/// <summary> /// creates the rows for the whole current year in AGeneralLedgerMaster and AGeneralLedgerMasterPeriod for an Account/CostCentre combination /// </summary> /// <param name="AMainDS"></param> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACostCentreCode"></param> /// <returns>The new glm sequence, which is negative until SubmitChanges</returns> private static Int32 CreateGLMYear( ref GLPostingTDS AMainDS, Int32 ALedgerNumber, string AAccountCode, string ACostCentreCode) { ALedgerRow Ledger = AMainDS.ALedger[0]; AGeneralLedgerMasterRow GLMRow = AMainDS.AGeneralLedgerMaster.NewRowTyped(); // row.GlmSequence will be set by SubmitChanges GLMRow.GlmSequence = (AMainDS.AGeneralLedgerMaster.Rows.Count * -1) - 1; GLMRow.LedgerNumber = ALedgerNumber; GLMRow.Year = Ledger.CurrentFinancialYear; GLMRow.AccountCode = AAccountCode; GLMRow.CostCentreCode = ACostCentreCode; AMainDS.AGeneralLedgerMaster.Rows.Add(GLMRow); for (int PeriodCount = 1; PeriodCount < Ledger.NumberOfAccountingPeriods + Ledger.NumberFwdPostingPeriods + 1; PeriodCount++) { AGeneralLedgerMasterPeriodRow PeriodRow = AMainDS.AGeneralLedgerMasterPeriod.NewRowTyped(); PeriodRow.GlmSequence = GLMRow.GlmSequence; PeriodRow.PeriodNumber = PeriodCount; AMainDS.AGeneralLedgerMasterPeriod.Rows.Add(PeriodRow); } return GLMRow.GlmSequence; }
/// <summary> /// Write all changes to the database; on failure the whole transaction will be rolled back /// </summary> /// <param name="AMainDS"></param> private static void SubmitChanges(GLPostingTDS AMainDS) { TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SubmitChanges..."); GLPostingTDSAccess.SubmitChanges(AMainDS.GetChangesTyped(true)); TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: Finished..."); }
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber) { FBudgetTDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction); string sqlLoadBudgetForThisAndNextYear = string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)", ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("thisyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear; parameters.Add(param); param = new OdbcParameter("nextyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1; parameters.Add(param); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction, parameters.ToArray()); string sqlLoadBudgetPeriodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", ABudgetPeriodTable.GetTableDBName(), ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetPeriodForThisAndNextYear, FBudgetTDS.ABudgetPeriod.TableName, Transaction, parameters.ToArray()); // Accept row changes here so that the Client gets 'unmodified' rows FBudgetTDS.AcceptChanges(); GLPostingDS = new GLPostingTDS(); AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction); // get the glm sequences for this year and next year for (int i = 0; i <= 1; i++) { int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i; AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = Year; GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction)); } string sqlLoadGlmperiodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(GLPostingDS, sqlLoadGlmperiodForThisAndNextYear, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); GLPostingDS.AcceptChanges(); return(true); }
public static bool GenerateICHStewardshipBatch(int ALedgerNumber, int APeriodNumber, ref TVerificationResultCollection AVerificationResult) { string StandardCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); bool IsSuccessful = false; bool DrCrIndicator = true; bool IncomeDrCrIndicator; bool ExpenseDrCrIndicator; bool AccountDrCrIndicator; string IncomeAccounts = string.Empty; string ExpenseAccounts = string.Empty; //Error handling string ErrorContext = String.Empty; string ErrorMessage = String.Empty; //Set default type as non-critical TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical; bool NewTransaction = false; TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); //Generating the ICH batch... try { DateTime PeriodStartDate; DateTime PeriodEndDate; TFinancialYear.GetStartAndEndDateOfPeriod(ALedgerNumber, APeriodNumber, out PeriodStartDate, out PeriodEndDate, DBTransaction); String strPeriodStartDate = "#" + PeriodStartDate.ToString("yyyy-MM-dd") + "#"; String strPeriodEndDate = "#" + PeriodEndDate.ToString("yyyy-MM-dd") + "#"; AGiftBatchTable GiftBatchTable = new AGiftBatchTable(); String GiftQuery = "SELECT * FROM a_gift_batch WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = " + ALedgerNumber + " AND " + AGiftBatchTable.GetBatchStatusDBName() + " = '" + MFinanceConstants.BATCH_POSTED + "'" + " AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " >= " + strPeriodStartDate + " AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= " + strPeriodEndDate + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(); DBAccess.GDBAccessObj.SelectDT(GiftBatchTable, GiftQuery, DBTransaction); //Create a new batch. If it turns out I don't need one, I can delete it later. GLBatchTDS MainDS = TGLPosting.CreateABatch(ALedgerNumber, Catalog.GetString("ICH Stewardship"), 0, PeriodEndDate); ABatchRow NewBatchRow = MainDS.ABatch[0]; int GLBatchNumber = NewBatchRow.BatchNumber; //Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship GLPostingTDS PostingDS = new GLPostingTDS(); ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, DBTransaction); AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction); AIchStewardshipAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction); AAccountHierarchyAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction); ABatchTable BatchTable = new ABatchTable(); ABatchRow BatchTemplateRow = (ABatchRow)BatchTable.NewRowTyped(false); BatchTemplateRow.LedgerNumber = ALedgerNumber; BatchTemplateRow.BatchPeriod = APeriodNumber; StringCollection Operators0 = StringHelper.InitStrArr(new string[] { "=", "=" }); StringCollection OrderList0 = new StringCollection(); OrderList0.Add("ORDER BY"); OrderList0.Add(ABatchTable.GetBatchNumberDBName() + " DESC"); ABatchTable BatchesInAPeriod = ABatchAccess.LoadUsingTemplate(BatchTemplateRow, Operators0, null, DBTransaction, OrderList0, 0, 0); if (BatchesInAPeriod != null) { int BatchNumber = 0; for (int i = 0; i < BatchesInAPeriod.Count; i++) { ABatchRow batchRow = (ABatchRow)BatchesInAPeriod.Rows[i]; BatchNumber = batchRow.BatchNumber; AJournalAccess.LoadViaABatch(MainDS, ALedgerNumber, BatchNumber, DBTransaction); ATransactionAccess.LoadViaABatch(MainDS, ALedgerNumber, BatchNumber, DBTransaction); } } else { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("No Batches found to process in Ledger: {0}"), ALedgerNumber); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } ALedgerRow LedgerRow = (ALedgerRow)PostingDS.ALedger.Rows[0]; //Create a new journal in the Batch //Run gl1120o.p AJournalRow NewJournalRow = MainDS.AJournal.NewRowTyped(); NewJournalRow.LedgerNumber = ALedgerNumber; NewJournalRow.BatchNumber = GLBatchNumber; NewJournalRow.JournalNumber = ++NewBatchRow.LastJournal; NewJournalRow.JournalDescription = NewBatchRow.BatchDescription; NewJournalRow.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL; NewJournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString(); NewJournalRow.TransactionCurrency = LedgerRow.BaseCurrency; NewJournalRow.ExchangeRateToBase = 1; NewJournalRow.DateEffective = PeriodEndDate; NewJournalRow.JournalPeriod = APeriodNumber; MainDS.AJournal.Rows.Add(NewJournalRow); int GLJournalNumber = NewJournalRow.JournalNumber; int GLTransactionNumber = NewJournalRow.LastTransactionNumber + 1; // *************************** // Generate the transactions // *************************** AAccountRow AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.INCOME_HEADING }); //Process income accounts if (AccountRow != null) { IncomeDrCrIndicator = AccountRow.DebitCreditIndicator; } else { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("Income Account header: '{1}' not found in the accounts table for Ledger: {0}."), ALedgerNumber, MFinanceConstants.INCOME_HEADING); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } BuildChildAccountList(ALedgerNumber, AccountRow, DBTransaction, ref IncomeAccounts, ref AVerificationResult); //Process expense accounts AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.EXPENSE_HEADING }); if (AccountRow != null) { ExpenseDrCrIndicator = AccountRow.DebitCreditIndicator; } else { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("Expense Account header: '{1}' not found in the accounts table for Ledger: {0}."), ALedgerNumber, MFinanceConstants.EXPENSE_HEADING); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } BuildChildAccountList(ALedgerNumber, AccountRow, DBTransaction, ref ExpenseAccounts, ref AVerificationResult); //Process P&L accounts AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.PROFIT_AND_LOSS_HEADING }); if (AccountRow != null) { AccountDrCrIndicator = AccountRow.DebitCreditIndicator; } else { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("Profit & Loss Account header: '{1}' not found in the accounts table for Ledger: {0}."), ALedgerNumber, MFinanceConstants.PROFIT_AND_LOSS_HEADING); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } // find out the stewardship number - Ln 275 // Increment the Last ICH No. int ICHProcessing = ++LedgerRow.LastIchNumber; decimal ICHTotal = 0; bool PostICHBatch = false; ACostCentreRow CCTemplateRow = PostingDS.ACostCentre.NewRowTyped(false); CCTemplateRow.LedgerNumber = ALedgerNumber; CCTemplateRow.PostingCostCentreFlag = true; CCTemplateRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE; ACostCentreAccess.LoadUsingTemplate(PostingDS, CCTemplateRow, DBTransaction); //Iterate through the cost centres // string OrderBy = ACostCentreTable.GetCostCentreCodeDBName(); StringDictionary DestinationAccount = GetDestinationAccountCodes(ALedgerNumber, PostingDS.ACostCentre, DBTransaction); AIchStewardshipTable ICHStewardshipTable = new AIchStewardshipTable(); Boolean NonIchTransactionsIncluded = false; String JournalRowOrder = "a_journal_number_i"; String TransRowOrder = "a_batch_number_i,a_journal_number_i,a_transaction_number_i"; foreach (ACostCentreRow CostCentreRow in PostingDS.ACostCentre.Rows) { string CostCentre = CostCentreRow.CostCentreCode; //Initialise values for each Cost Centre decimal SettlementAmount = 0; decimal IncomeAmount = 0; decimal ExpenseAmount = 0; decimal XferAmount = 0; decimal IncomeAmountIntl = 0; decimal ExpenseAmountIntl = 0; decimal XferAmountIntl = 0; Boolean TransferFound = false; /* 0008 Go through all of the transactions. Ln:301 */ String WhereClause = "a_cost_centre_code_c = '" + CostCentreRow.CostCentreCode + "' AND a_transaction_status_l=true AND a_ich_number_i = 0"; DataRow[] FoundTransRows = MainDS.ATransaction.Select(WhereClause, TransRowOrder); foreach (DataRow UntypedTransRow in FoundTransRows) { ATransactionRow TransRow = (ATransactionRow)UntypedTransRow; DataRow[] FoundJournalRows = MainDS.AJournal.Select( "a_batch_number_i = " + TransRow.BatchNumber + " AND a_journal_number_i = " + TransRow.JournalNumber, JournalRowOrder); if (FoundJournalRows != null) { TransferFound = true; PostICHBatch = true; TransRow.IchNumber = ICHProcessing; if (TransRow.DebitCreditIndicator == AccountDrCrIndicator) { SettlementAmount -= TransRow.AmountInBaseCurrency; } else { SettlementAmount += TransRow.AmountInBaseCurrency; } //Process Income (ln:333) if (IncomeAccounts.Contains(TransRow.AccountCode)) { if (TransRow.DebitCreditIndicator == IncomeDrCrIndicator) { IncomeAmount += TransRow.AmountInBaseCurrency; IncomeAmountIntl += TransRow.AmountInIntlCurrency; } else { IncomeAmount -= TransRow.AmountInBaseCurrency; IncomeAmountIntl -= TransRow.AmountInIntlCurrency; } } //process expenses if (ExpenseAccounts.Contains(TransRow.AccountCode) && (TransRow.AccountCode != MFinanceConstants.DIRECT_XFER_ACCT) && (TransRow.AccountCode != MFinanceConstants.ICH_ACCT_SETTLEMENT)) { if (TransRow.DebitCreditIndicator = ExpenseDrCrIndicator) { ExpenseAmount += TransRow.AmountInBaseCurrency; ExpenseAmountIntl += TransRow.AmountInIntlCurrency; } else { ExpenseAmount -= TransRow.AmountInBaseCurrency; ExpenseAmountIntl -= TransRow.AmountInIntlCurrency; } } //Process Direct Transfers if (TransRow.AccountCode == MFinanceConstants.DIRECT_XFER_ACCT) { if (TransRow.DebitCreditIndicator == ExpenseDrCrIndicator) { XferAmount += TransRow.AmountInBaseCurrency; XferAmountIntl += TransRow.AmountInIntlCurrency; } else { XferAmount -= TransRow.AmountInBaseCurrency; XferAmountIntl -= TransRow.AmountInIntlCurrency; } } } } //end of foreach transaction /* now mark all the gifts as processed */ if (TransferFound) { AGiftDetailTable GiftDetailTable = new AGiftDetailTable(); AGiftDetailRow GiftDetailTemplateRow = (AGiftDetailRow)GiftDetailTable.NewRowTyped(false); GiftDetailTemplateRow.LedgerNumber = ALedgerNumber; GiftDetailTemplateRow.IchNumber = 0; GiftDetailTemplateRow.CostCentreCode = CostCentreRow.CostCentreCode; foreach (AGiftBatchRow GiftBatchRow in GiftBatchTable.Rows) { GiftDetailTemplateRow.BatchNumber = GiftBatchRow.BatchNumber; GiftDetailTable = AGiftDetailAccess.LoadUsingTemplate(GiftDetailTemplateRow, DBTransaction); foreach (AGiftDetailRow GiftDetailRow in GiftDetailTable.Rows) { GiftDetailRow.IchNumber = ICHProcessing; } } } // if TransferFound if ((SettlementAmount == 0) // If there's no activity in this CC, && (IncomeAmount == 0) // bail to the next one. && (ExpenseAmount == 0) && (XferAmount == 0)) { continue; } /* Balance the cost centre by entering an opposite transaction * to ICH settlement. Use positive amounts only. */ /* Increment or decrement the ICH total to be transferred after this loop. * NOTE - if this is a "non-ICH fund", I need to balance it separately, and I'll do that right here. */ DrCrIndicator = AccountRow.DebitCreditIndicator; if (DestinationAccount[CostCentreRow.CostCentreCode] == MFinanceConstants.ICH_ACCT_ICH) { if (DrCrIndicator == MFinanceConstants.IS_DEBIT) { ICHTotal += SettlementAmount; } else { ICHTotal -= SettlementAmount; } } DrCrIndicator = AccountDrCrIndicator; if (SettlementAmount < 0) { DrCrIndicator = !AccountDrCrIndicator; SettlementAmount = 0 - SettlementAmount; } if ((DestinationAccount[CostCentreRow.CostCentreCode] != MFinanceConstants.ICH_ACCT_ICH) && (SettlementAmount != 0)) { // I'm creating a transaction right here for this "non-ICH" CostCentre. // This potentially means that there will be multiple transactions to the "non-ICH" account, // whereas the ICH account has only a single transaction, but that's not big deal: if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber, Catalog.GetString("Non-ICH foreign fund Clearing"), DestinationAccount[CostCentreRow.CostCentreCode], StandardCostCentre, SettlementAmount, PeriodEndDate, !DrCrIndicator, Catalog.GetString("Non-ICH"), true, SettlementAmount, out GLTransactionNumber)) { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."), ALedgerNumber, GLBatchNumber, GLJournalNumber); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } NonIchTransactionsIncluded = true; } /* Generate the transaction to 'balance' the foreign fund - * in the ICH settlement account. */ //RUN gl1130o.p ("new":U, //Create a transaction if (SettlementAmount > 0) { if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber, Catalog.GetString("ICH Monthly Clearing"), MFinanceConstants.ICH_ACCT_SETTLEMENT, // DestinationAccount[CostCentreRow.CostCentreCode], CostCentreRow.CostCentreCode, SettlementAmount, PeriodEndDate, DrCrIndicator, Catalog.GetString("ICH Process"), true, SettlementAmount, out GLTransactionNumber)) { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."), ALedgerNumber, GLBatchNumber, GLJournalNumber); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } //Mark as processed ATransactionRow TransRow = (ATransactionRow)MainDS.ATransaction.Rows.Find(new object[] { ALedgerNumber, GLBatchNumber, GLJournalNumber, GLTransactionNumber }); TransRow.IchNumber = ICHProcessing; } /* Now create corresponding report row on stewardship table, * Only for Cost Centres that cleared to ICH */ if ((DestinationAccount[CostCentreRow.CostCentreCode] == MFinanceConstants.ICH_ACCT_ICH) && ((IncomeAmount != 0) || (ExpenseAmount != 0) || (XferAmount != 0))) { AIchStewardshipRow ICHStewardshipRow = ICHStewardshipTable.NewRowTyped(true); //MainDS.Tables.Add(IchStewardshipTable); ICHStewardshipRow.LedgerNumber = ALedgerNumber; ICHStewardshipRow.PeriodNumber = APeriodNumber; ICHStewardshipRow.IchNumber = ICHProcessing; // ICHStewardshipRow.DateProcessed = DateTime.Today; // This would be strictly correct, but the Stewardship Reporting looks for ICHStewardshipRow.DateProcessed = PeriodEndDate; // rows using a date filter. ICHStewardshipRow.CostCentreCode = CostCentreRow.CostCentreCode; ICHStewardshipRow.IncomeAmount = IncomeAmount; ICHStewardshipRow.ExpenseAmount = ExpenseAmount; ICHStewardshipRow.DirectXferAmount = XferAmount; ICHStewardshipRow.IncomeAmountIntl = IncomeAmountIntl; ICHStewardshipRow.ExpenseAmountIntl = ExpenseAmountIntl; ICHStewardshipRow.DirectXferAmountIntl = XferAmountIntl; ICHStewardshipTable.Rows.Add(ICHStewardshipRow); } } // for each cost centre /* Update the balance of the ICH account (like a bank account). * If the total is negative, it means the ICH batch has a * credit total so far. Thus, we now balance it with the opposite * transaction. */ if (ICHTotal < 0) { DrCrIndicator = MFinanceConstants.IS_DEBIT; ICHTotal = -ICHTotal; } else if (ICHTotal > 0) { DrCrIndicator = MFinanceConstants.IS_CREDIT; } /* 0006 - If the balance is 0 then this is ok * (eg last minute change of a gift from one field to another) */ if ((ICHTotal == 0) && !NonIchTransactionsIncluded) { AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Generating the ICH batch"), Catalog.GetString("No ICH batch was required."), TResultSeverity.Resv_Status)); // An empty GL Batch now exists, which I need to delete. // TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection(); TGLPosting.DeleteGLBatch( ALedgerNumber, GLBatchNumber, out BatchCancelResult); AVerificationResult.AddCollection(BatchCancelResult); IsSuccessful = true; } else { if (ICHTotal != 0) { //Create a transaction if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber, Catalog.GetString("ICH Monthly Clearing"), MFinanceConstants.ICH_ACCT_ICH, StandardCostCentre, ICHTotal, PeriodEndDate, DrCrIndicator, Catalog.GetString("ICH"), true, ICHTotal, out GLTransactionNumber)) { ErrorContext = Catalog.GetString("Generating the ICH batch"); ErrorMessage = String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."), ALedgerNumber, GLBatchNumber, GLJournalNumber); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } } //Post the batch if (PostICHBatch) { AIchStewardshipAccess.SubmitChanges(ICHStewardshipTable, DBTransaction); MainDS.ThrowAwayAfterSubmitChanges = true; // SubmitChanges will not return to me any changes made in MainDS. GLBatchTDSAccess.SubmitChanges(MainDS); ALedgerAccess.SubmitChanges(PostingDS.ALedger, DBTransaction); // LastIchNumber has changed. IsSuccessful = TGLPosting.PostGLBatch(ALedgerNumber, GLBatchNumber, out AVerificationResult); } else { AVerificationResult.Add(new TVerificationResult(ErrorContext, Catalog.GetString("No Stewardship batch is required."), TResultSeverity.Resv_Status)); // An empty GL Batch now exists, which I need to delete. // TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection(); TGLPosting.DeleteGLBatch( ALedgerNumber, GLBatchNumber, out BatchCancelResult); AVerificationResult.AddCollection(BatchCancelResult); } // else } // else } // try catch (ArgumentException Exc) { TLogging.Log("An ArgumentException occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString()); if (AVerificationResult == null) { AVerificationResult = new TVerificationResultCollection(); } AVerificationResult.Add(new TVerificationResult(ErrorContext, Exc.Message, ErrorType)); throw; } catch (InvalidOperationException Exc) { TLogging.Log( "An InvalidOperationException occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString()); if (AVerificationResult == null) { AVerificationResult = new TVerificationResultCollection(); } AVerificationResult.Add(new TVerificationResult(ErrorContext, Exc.Message, ErrorType)); throw; } catch (Exception Exc) { TLogging.Log("An Exception occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString()); ErrorContext = Catalog.GetString("Calculate Admin Fee"); ErrorMessage = String.Format(Catalog.GetString("Unknown error while generating the ICH batch for Ledger: {0} and Period: {1}" + Environment.NewLine + Environment.NewLine + Exc.ToString()), ALedgerNumber, APeriodNumber); ErrorType = TResultSeverity.Resv_Critical; if (AVerificationResult == null) { AVerificationResult = new TVerificationResultCollection(); } AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType)); throw; } finally { if (IsSuccessful && NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); } else if (!IsSuccessful && NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } return IsSuccessful; }
/// <summary> /// for each posting level, propagate the value upwards through both the account and the cost centre hierarchy in glm master; /// also propagate the value from the posting period through the following periods; /// </summary> private static bool SummarizeData( GLPostingTDS APostingDS, Int32 AFromPeriod, ref SortedList <string, TAmount>APostingLevel, ref SortedList <string, TAccountTreeElement>AAccountTree, ref SortedList <string, string>ACostCentreTree) { if (APostingDS.ALedger[0].ProvisionalYearEndFlag) { // If the year end close is running, then we are posting the year end // reallocations. These appear as part of the final period, but // should only be written to the forward periods. // In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i. AFromPeriod++; } DataView GLMMasterView = APostingDS.AGeneralLedgerMaster.DefaultView; GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName(); DataView GLMPeriodView = APostingDS.AGeneralLedgerMasterPeriod.DefaultView; GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName(); // Loop through the posting data collected earlier. Summarize it to a // temporary table, which is much faster than finding and updating records // in the glm tables multiple times. WriteData will write it to the real // tables in a single pass. foreach (string PostingLevelKey in APostingLevel.Keys) { string AccountCode = TAmount.GetAccountCode(PostingLevelKey); string CostCentreCode = TAmount.GetCostCentreCode(PostingLevelKey); TAmount PostingLevelElement = APostingLevel[PostingLevelKey]; // Combine the summarization trees for both the account and the cost centre. foreach (string AccountTreeKey in AAccountTree.Keys) { if (TAccountTreeElement.GetReportingAccountCode(AccountTreeKey) == AccountCode) { string AccountCodeToReportTo = TAccountTreeElement.GetAccountReportToCode(AccountTreeKey); TAccountTreeElement AccountTreeElement = AAccountTree[AccountTreeKey]; foreach (string CostCentreKey in ACostCentreTree.Keys) { if (CostCentreKey.StartsWith(CostCentreCode + ":")) { string CostCentreCodeToReportTo = CostCentreKey.Split(':')[1]; decimal SignBaseAmount = PostingLevelElement.baseAmount; decimal SignTransAmount = PostingLevelElement.transAmount; // Set the sign of the amounts according to the debit/credit indicator if (AccountTreeElement.Invert) { SignBaseAmount *= -1; SignTransAmount *= -1; } // Find the summary level, creating it if it does not already exist. int GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCodeToReportTo, CostCentreCodeToReportTo }); if (GLMMasterIndex == -1) { CreateGLMYear( ref APostingDS, APostingDS.ALedger[0].LedgerNumber, AccountCodeToReportTo, CostCentreCodeToReportTo); GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCodeToReportTo, CostCentreCodeToReportTo }); } AGeneralLedgerMasterRow GlmRow = (AGeneralLedgerMasterRow)GLMMasterView[GLMMasterIndex].Row; GlmRow.YtdActualBase += SignBaseAmount; if (AccountTreeElement.Foreign) { if (GlmRow.IsYtdActualForeignNull()) { GlmRow.YtdActualForeign = SignTransAmount; } else { GlmRow.YtdActualForeign += SignTransAmount; } } if (APostingDS.ALedger[0].ProvisionalYearEndFlag) { GlmRow.ClosingPeriodActualBase += SignBaseAmount; } // Add the period data from the posting level to the summary levels for (Int32 PeriodCount = AFromPeriod; PeriodCount <= APostingDS.ALedger[0].NumberOfAccountingPeriods + APostingDS.ALedger[0].NumberFwdPostingPeriods; PeriodCount++) { int GLMPeriodIndex = GLMPeriodView.Find(new object[] { GlmRow.GlmSequence, PeriodCount }); AGeneralLedgerMasterPeriodRow GlmPeriodRow; if (GLMPeriodIndex == -1) { GlmPeriodRow = APostingDS.AGeneralLedgerMasterPeriod.NewRowTyped(); GlmPeriodRow.GlmSequence = GlmRow.GlmSequence; GlmPeriodRow.PeriodNumber = PeriodCount; APostingDS.AGeneralLedgerMasterPeriod.Rows.Add(GlmPeriodRow); } else { GlmPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[GLMPeriodIndex].Row; } GlmPeriodRow.ActualBase += SignBaseAmount; if (AccountTreeElement.Foreign) { if (GlmPeriodRow.IsActualForeignNull()) { GlmPeriodRow.ActualForeign = SignTransAmount; } else { GlmPeriodRow.ActualForeign += SignTransAmount; } } } } } } } } return true; }
/// <summary> /// Load all GLM and GLMPeriod records for the batch period and the following periods, since that will avoid loading them one by one during submitchanges. /// this is called after ValidateBatchAndTransactions, because the BatchYear and BatchPeriod are validated and recalculated there /// </summary> private static void LoadGLMData(ref GLPostingTDS AGLPostingDS, Int32 ALedgerNumber, ABatchRow ABatchToPost) { TDBTransaction Transaction = null; GLPostingTDS GLPostingDS = AGLPostingDS; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { AGeneralLedgerMasterRow GLMTemplateRow = GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.Year = ABatchToPost.BatchYear; AGeneralLedgerMasterAccess.LoadUsingTemplate(GLPostingDS, GLMTemplateRow, Transaction); string query = "SELECT PUB_a_general_ledger_master_period.* " + "FROM PUB_a_general_ledger_master, PUB_a_general_ledger_master_period " + "WHERE PUB_a_general_ledger_master.a_ledger_number_i = ? " + "AND PUB_a_general_ledger_master.a_year_i = ? " + "AND PUB_a_general_ledger_master_period.a_glm_sequence_i = PUB_a_general_ledger_master.a_glm_sequence_i " + "AND PUB_a_general_ledger_master_period.a_period_number_i >= ?"; List <OdbcParameter>parameters = new List <OdbcParameter>(); OdbcParameter parameter = new OdbcParameter("ledgernumber", OdbcType.Int); parameter.Value = ALedgerNumber; parameters.Add(parameter); parameter = new OdbcParameter("year", OdbcType.Int); parameter.Value = ABatchToPost.BatchYear; parameters.Add(parameter); parameter = new OdbcParameter("period", OdbcType.Int); parameter.Value = ABatchToPost.BatchPeriod; parameters.Add(parameter); DBAccess.GDBAccessObj.Select(GLPostingDS, query, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); }
/// <summary> /// validate the attributes of the transactions /// some things are even modified, eg. batch period etc from date effective /// </summary> private static bool ValidateAnalysisAttributes(ref GLBatchTDS ADataSet, GLPostingTDS APostingDS, Int32 ALedgerNumber, Int32 ABatchNumber, out TVerificationResultCollection AVerifications) { AVerifications = new TVerificationResultCollection(); DataView TransactionsOfJournalView = new DataView(ADataSet.ATransaction); foreach (AJournalRow journal in ADataSet.AJournal.Rows) { if (journal.BatchNumber != ABatchNumber) { continue; } TransactionsOfJournalView.RowFilter = ATransactionTable.GetJournalNumberDBName() + " = " + journal.JournalNumber.ToString(); foreach (DataRowView transRowView in TransactionsOfJournalView) { ATransactionRow transRow = (ATransactionRow)transRowView.Row; // Check that all atransanalattrib records are there for all analattributes entries DataView ANView = APostingDS.AAnalysisAttribute.DefaultView; ANView.RowFilter = String.Format("{0} = '{1}' AND {2} = true", AAnalysisAttributeTable.GetAccountCodeDBName(), transRow.AccountCode, AAnalysisAttributeTable.GetActiveDBName()); int i = 0; while (i < ANView.Count) { AAnalysisAttributeRow attributeRow = (AAnalysisAttributeRow)ANView[i].Row; ATransAnalAttribRow aTransAttribRow = (ATransAnalAttribRow)ADataSet.ATransAnalAttrib.Rows.Find(new object[] { ALedgerNumber, ABatchNumber, transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode }); if (aTransAttribRow == null) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString( "Missing attributes record for journal #{0} transaction #{1} and TypeCode {2}"), transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode), TResultSeverity.Resv_Critical)); } else { String v = aTransAttribRow.AnalysisAttributeValue; if ((v == null) || (v.Length == 0)) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString("Analysis Type {0} is missing values in journal #{1}, transaction #{2}"), attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber), TResultSeverity.Resv_Critical)); } else { AFreeformAnalysisRow afaRow = (AFreeformAnalysisRow)APostingDS.AFreeformAnalysis.Rows.Find( new Object[] { ALedgerNumber, attributeRow.AnalysisTypeCode, v }); if (afaRow == null) { // this would cause a constraint error and is only possible in a development/sqlite environment AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString("Invalid values at journal #{0} transaction #{1} and TypeCode {2}"), transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode), TResultSeverity.Resv_Critical)); } else { if (!afaRow.Active) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString( "Value {0} not active at journal #{1} transaction #{2} and TypeCode {3}"), v, transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode), TResultSeverity.Resv_Critical)); } // if } // else } // else } // else i++; } // while i } // foreach transRowView } // foreach journal return TVerificationHelper.IsNullOrOnlyNonCritical(AVerifications); }
// // // April 2015, Tim Ingham: // // NOTE that the full summarization that includes all the summary levels has been discontinued, // since the Open Petra reports only use the posting levels and calculate the summaries on the fly. // This makes Ledger posting MUCH faster. // // The full SummarizeData method, and its supporting CalculateTrees method, is still present, // and we could return to it if it became necessary. private static void SummarizeInternal(Int32 ALedgerNumber, GLPostingTDS APostingDS, SortedList <string, TAmount>APostingLevel, Int32 AFromPeriod, bool ACalculatePostingTree) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments if (ACalculatePostingTree) { TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: CalculateTrees..."); // key is PostingAccount, the value TAccountTreeElement describes the parent account and other details of the relation SortedList <string, TAccountTreeElement>AccountTree; // key is the PostingCostCentre, the value is the parent Cost Centre SortedList <string, string>CostCentreTree; // this was in Petra 2.x; takes a lot of time, which the reports could do better // TODO: can we just calculate the cost centre tree, since that is needed for Balance Sheet, // but avoid calculating the whole account tree? CalculateTrees(ALedgerNumber, ref APostingLevel, out AccountTree, out CostCentreTree, APostingDS); TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeData..."); SummarizeData(APostingDS, AFromPeriod, ref APostingLevel, ref AccountTree, ref CostCentreTree); } else { TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeDataSimple..."); SummarizeDataSimple(ALedgerNumber, APostingDS, AFromPeriod, ref APostingLevel); } }
/// <summary> /// Only used for precalculating the new balances before the user actually posts the batch /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ABatchNumber"></param> /// <param name="ATransaction"></param> /// <param name="AVerifications"></param> /// <param name="APostingDS"></param> /// <param name="ABatchPeriod"></param> /// <returns></returns> public static bool TestPostGLBatch(Int32 ALedgerNumber, Int32 ABatchNumber, TDBTransaction ATransaction, out TVerificationResultCollection AVerifications, out GLPostingTDS APostingDS, ref Int32 ABatchPeriod) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (ABatchNumber <= 0) { throw new EFinanceSystemInvalidBatchNumberException(String.Format(Catalog.GetString( "Function:{0} - The Batch number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber, ABatchNumber); } else if (ATransaction == null) { throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( "Function:{0} - Database Transaction must not be NULL!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments bool RetVal = false; GLBatchTDS MainDS = new GLBatchTDS(); SortedList <string, TAmount>PostingLevel = new SortedList <string, TGLPosting.TAmount>(); try { APostingDS = PrepareGLBatchForPosting(out MainDS, ALedgerNumber, ABatchNumber, ref ATransaction, out AVerifications, PostingLevel, ref ABatchPeriod); if ((MainDS != null) && (APostingDS != null)) { SummarizeInternal(ALedgerNumber, APostingDS, PostingLevel, ABatchPeriod, false); RetVal = true; } } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return RetVal; }
/// <summary> /// For each posting level, propagate the value upwards through both the account and the cost centre hierarchy in glm master; /// also propagate the value from the posting period through the following periods; /// </summary> private static bool SummarizeData( GLPostingTDS APostingDS, Int32 AFromPeriod, ref SortedList <string, TAmount>APostingLevel, ref SortedList <string, TAccountTreeElement>AAccountTree, ref SortedList <string, string>ACostCentreTree) { #region Validate Arguments if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if ((APostingDS.ALedger == null) || (APostingDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data in the GL Posting dataset does not exist or could not be accessed!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments if (APostingDS.ALedger[0].ProvisionalYearEndFlag) { // If the year end close is running, then we are posting the year end // reallocations. These appear as part of the final period, but // should only be written to the forward periods. // In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i. AFromPeriod++; } DataView GLMMasterView = APostingDS.AGeneralLedgerMaster.DefaultView; GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName(); DataView GLMPeriodView = APostingDS.AGeneralLedgerMasterPeriod.DefaultView; GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName(); // Loop through the posting data collected earlier. foreach (string postingLevelKey in APostingLevel.Keys) { String[] keyParts = postingLevelKey.Split(':'); string accountCode = keyParts[0]; string costCentreCode = keyParts[1]; TAmount postingLevelElement = APostingLevel[postingLevelKey]; // Combine the summarization trees for both the account and the cost centre. foreach (string accountTreeKey in AAccountTree.Keys) { String[] accountKeyParts = accountTreeKey.Split(':'); if (accountKeyParts[0] == accountCode) { string accountCodeToReportTo = accountKeyParts[1]; TAccountTreeElement accountTreeElement = AAccountTree[accountTreeKey]; foreach (string costCentreKey in ACostCentreTree.Keys) { String[] cCKeyParts = costCentreKey.Split(':'); if (cCKeyParts[0] == costCentreCode) { string costCentreCodeToReportTo = cCKeyParts[1]; decimal signBaseAmount = postingLevelElement.BaseAmount; decimal signIntlAmount = postingLevelElement.IntlAmount; decimal signTransAmount = postingLevelElement.TransAmount; // Set the sign of the amounts according to the debit/credit indicator if (accountTreeElement.Invert) { signBaseAmount *= -1; signIntlAmount *= -1; signTransAmount *= -1; } // Find the summary level, creating it if it does not already exist. int gLMMasterIndex = GLMMasterView.Find(new object[] { accountCodeToReportTo, costCentreCodeToReportTo }); if (gLMMasterIndex == -1) { CreateGLMYear( ref APostingDS, APostingDS.ALedger[0].LedgerNumber, accountCodeToReportTo, costCentreCodeToReportTo); gLMMasterIndex = GLMMasterView.Find(new object[] { accountCodeToReportTo, costCentreCodeToReportTo }); } AGeneralLedgerMasterRow gLMRow = (AGeneralLedgerMasterRow)GLMMasterView[gLMMasterIndex].Row; #region Validate Data if (gLMRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - GLM row data does not exist or could not be accessed!"), Utilities.GetMethodName(true))); } #endregion Validate Data gLMRow.YtdActualBase += signBaseAmount; gLMRow.YtdActualIntl += signIntlAmount; if (accountTreeElement.Foreign) { if (gLMRow.IsYtdActualForeignNull()) { gLMRow.YtdActualForeign = signTransAmount; } else { gLMRow.YtdActualForeign += signTransAmount; } } if (APostingDS.ALedger[0].ProvisionalYearEndFlag) { gLMRow.ClosingPeriodActualBase += signBaseAmount; gLMRow.ClosingPeriodActualIntl += signIntlAmount; } // Add the data to forward periods, to the end of the GLMP list for (Int32 PeriodCount = AFromPeriod; PeriodCount <= APostingDS.ALedger[0].NumberOfAccountingPeriods + APostingDS.ALedger[0].NumberFwdPostingPeriods; PeriodCount++) { int gLMPeriodIndex = GLMPeriodView.Find(new object[] { gLMRow.GlmSequence, PeriodCount }); AGeneralLedgerMasterPeriodRow gLMPeriodRow; if (gLMPeriodIndex == -1) { gLMPeriodRow = APostingDS.AGeneralLedgerMasterPeriod.NewRowTyped(); gLMPeriodRow.GlmSequence = gLMRow.GlmSequence; gLMPeriodRow.PeriodNumber = PeriodCount; APostingDS.AGeneralLedgerMasterPeriod.Rows.Add(gLMPeriodRow); } else { gLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[gLMPeriodIndex].Row; } gLMPeriodRow.ActualBase += signBaseAmount; gLMPeriodRow.ActualIntl += signIntlAmount; if (accountTreeElement.Foreign) { if (gLMPeriodRow.IsActualForeignNull()) { gLMPeriodRow.ActualForeign = signTransAmount; } else { gLMPeriodRow.ActualForeign += signTransAmount; } } } } } } } } return true; }
/// <summary> /// on the posting level propagate the value from the posting period through the following periods; /// in this version of SummarizeData, there is no calculation of summary accounts/cost centres, since that can be done by the reports /// </summary> private static bool SummarizeDataSimple( Int32 ALedgerNumber, GLPostingTDS APostingDS, Int32 AFromPeriod, ref SortedList <string, TAmount>APostingLevel) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if ((APostingDS.ALedger == null) || (APostingDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data in the GL Posting dataset does not exist or could not be accessed!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments if (APostingDS.ALedger[0].ProvisionalYearEndFlag) { // If the year end close is running, then we are posting the year end // reallocations. These appear as part of the final period, but // should only be written to the forward periods. // In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i. AFromPeriod++; } DataView GLMMasterView = APostingDS.AGeneralLedgerMaster.DefaultView; GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName(); DataView GLMPeriodView = APostingDS.AGeneralLedgerMasterPeriod.DefaultView; GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName(); foreach (string postingLevelKey in APostingLevel.Keys) { String[] keyParts = postingLevelKey.Split(':'); string accountCode = keyParts[0]; string costCentreCode = keyParts[1]; TAmount postingLevelElement = APostingLevel[postingLevelKey]; // Find the posting level, creating it if it does not already exist. int gLMMasterIndex = GLMMasterView.Find(new object[] { accountCode, costCentreCode }); AGeneralLedgerMasterRow gLMRow; if (gLMMasterIndex == -1) { CreateGLMYear( ref APostingDS, ALedgerNumber, accountCode, costCentreCode); gLMMasterIndex = GLMMasterView.Find(new object[] { accountCode, costCentreCode }); } gLMRow = (AGeneralLedgerMasterRow)GLMMasterView[gLMMasterIndex].Row; gLMRow.YtdActualBase += postingLevelElement.BaseAmount; gLMRow.YtdActualIntl += postingLevelElement.IntlAmount; AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, accountCode }); #region Validate Data if (accountRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"), Utilities.GetMethodName(true), accountCode, ALedgerNumber)); } #endregion Validate Data if (accountRow.ForeignCurrencyFlag) { if (gLMRow.IsYtdActualForeignNull()) { gLMRow.YtdActualForeign = postingLevelElement.TransAmount; } else { gLMRow.YtdActualForeign += postingLevelElement.TransAmount; } } if (APostingDS.ALedger[0].ProvisionalYearEndFlag) { gLMRow.ClosingPeriodActualBase += postingLevelElement.BaseAmount; gLMRow.ClosingPeriodActualIntl += postingLevelElement.IntlAmount; } // Last use of GlmRow in this routine ... // propagate the data through the following periods for (Int32 periodCount = AFromPeriod; periodCount <= APostingDS.ALedger[0].NumberOfAccountingPeriods + APostingDS.ALedger[0].NumberFwdPostingPeriods; periodCount++) { int gLMPeriodIndex = GLMPeriodView.Find(new object[] { gLMRow.GlmSequence, periodCount }); AGeneralLedgerMasterPeriodRow gLMPeriodRow; if (gLMPeriodIndex == -1) { gLMPeriodRow = APostingDS.AGeneralLedgerMasterPeriod.NewRowTyped(); gLMPeriodRow.GlmSequence = gLMRow.GlmSequence; gLMPeriodRow.PeriodNumber = periodCount; } else { gLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[gLMPeriodIndex].Row; } gLMPeriodRow.ActualBase += postingLevelElement.BaseAmount; gLMPeriodRow.ActualIntl += postingLevelElement.IntlAmount; if (accountRow.ForeignCurrencyFlag) { if (gLMPeriodRow.IsActualForeignNull()) { gLMPeriodRow.ActualForeign = postingLevelElement.TransAmount; } else { gLMPeriodRow.ActualForeign += postingLevelElement.TransAmount; } } } } GLMMasterView.Sort = ""; GLMPeriodView.Sort = ""; return true; }
/// <summary> /// Calculate the summarization trees for each posting account and each /// posting cost centre. The result of the union of these trees, /// excluding the base posting/posting combination, is the set of /// accounts that receive the summary data. /// </summary> private static bool CalculateTrees( Int32 ALedgerNumber, ref SortedList <string, TAmount>APostingLevel, out SortedList <string, TAccountTreeElement>AAccountTree, out SortedList <string, string>ACostCentreTree, GLPostingTDS APostingDS) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments // get all accounts that each posting level account is directly or indirectly posting to AAccountTree = new SortedList <string, TAccountTreeElement>(); foreach (string postingLevelKey in APostingLevel.Keys) { string accountCode = TAmount.GetAccountCode(postingLevelKey); // only once for each account, even though there might be several entries for one account in APostingLevel because of different costcentres if (AAccountTree.ContainsKey(TAccountTreeElement.MakeKey(accountCode, accountCode))) { continue; } AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, accountCode }); #region Validate Data if (accountRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"), Utilities.GetMethodName(true), accountCode, ALedgerNumber)); } #endregion Validate Data bool DebitCreditIndicator = accountRow.DebitCreditIndicator; AAccountTree.Add(TAccountTreeElement.MakeKey(accountCode, accountCode), new TAccountTreeElement(false, accountRow.ForeignCurrencyFlag)); AAccountHierarchyDetailRow HierarchyDetail = (AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find( new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, accountCode }); while (HierarchyDetail != null) { accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, HierarchyDetail.AccountCodeToReportTo }); if (accountRow == null) { // current account is BAL SHT, and it reports nowhere (account with name = ledgernumber does not exist) break; } AAccountTree.Add(TAccountTreeElement.MakeKey(accountCode, HierarchyDetail.AccountCodeToReportTo), new TAccountTreeElement(DebitCreditIndicator != accountRow.DebitCreditIndicator, accountRow.ForeignCurrencyFlag)); HierarchyDetail = (AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find( new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, HierarchyDetail.AccountCodeToReportTo }); } } ACostCentreTree = new SortedList <string, string>(); foreach (string postingLevelKey in APostingLevel.Keys) { string costCentreCode = TAmount.GetCostCentreCode(postingLevelKey); // only once for each cost centre if (ACostCentreTree.ContainsKey(costCentreCode + ":" + costCentreCode)) { continue; } ACostCentreTree.Add(costCentreCode + ":" + costCentreCode, costCentreCode + ":" + costCentreCode); ACostCentreRow costCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, costCentreCode }); while (costCentre != null && !costCentre.IsCostCentreToReportToNull()) { ACostCentreTree.Add(costCentreCode + ":" + costCentre.CostCentreToReportTo, costCentreCode + ":" + costCentre.CostCentreToReportTo); costCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, costCentre.CostCentreToReportTo }); } } return true; }
/// <summary> /// mark each journal, each transaction as being posted; /// add sums for costcentre/account combinations /// </summary> /// <param name="AMainDS">can contain several batches and journals and transactions</param> /// <param name="APostingDS"></param> /// <param name="APostingLevel">the balance changes at the posting level</param> /// <param name="ABatchToPost">the batch to post</param> /// <returns>a list with the sums for each costcentre/account combination</returns> private static SortedList <string, TAmount>MarkAsPostedAndCollectData(GLBatchTDS AMainDS, GLPostingTDS APostingDS, SortedList <string, TAmount>APostingLevel, ABatchRow ABatchToPost) { #region Validate Arguments if (AMainDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"), Utilities.GetMethodName(true))); } else if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if (ABatchToPost == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Batch to post data row is null!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments DataView TransactionsDV = new DataView(AMainDS.ATransaction); TransactionsDV.Sort = ATransactionTable.GetJournalNumberDBName(); foreach (AJournalRow journal in AMainDS.AJournal.Rows) { if (journal.BatchNumber != ABatchToPost.BatchNumber) { continue; } foreach (DataRowView transactionview in TransactionsDV.FindRows(journal.JournalNumber)) { ATransactionRow transaction = (ATransactionRow)transactionview.Row; if (transaction.BatchNumber != ABatchToPost.BatchNumber) { continue; } transaction.TransactionStatus = true; // get the account that this transaction is writing to AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { transaction.LedgerNumber, transaction.AccountCode }); #region Validate Data if (accountRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"), Utilities.GetMethodName(true), transaction.AccountCode, transaction.LedgerNumber)); } #endregion Validate Data // Set the sign of the amounts according to the debit/credit indicator decimal SignBaseAmount = transaction.AmountInBaseCurrency; decimal SignIntlAmount = transaction.AmountInIntlCurrency; decimal SignTransAmount = transaction.TransactionAmount; if (accountRow.DebitCreditIndicator != transaction.DebitCreditIndicator) { SignBaseAmount *= -1.0M; SignIntlAmount *= -1.0M; SignTransAmount *= -1.0M; } // TODO: do we need to check for base currency corrections? // or do we get rid of these problems by not having international currency? string key = TAmount.MakeKey(transaction.AccountCode, transaction.CostCentreCode); if (!APostingLevel.ContainsKey(key)) { APostingLevel.Add(key, new TAmount()); } APostingLevel[key].BaseAmount += SignBaseAmount; APostingLevel[key].IntlAmount += SignIntlAmount; // Only foreign currency accounts store a value in the transaction currency, // if the transaction was actually in the foreign currency. if (accountRow.ForeignCurrencyFlag && (journal.TransactionCurrency == accountRow.ForeignCurrencyCode)) { APostingLevel[key].TransAmount += SignTransAmount; } } journal.JournalStatus = MFinanceConstants.BATCH_POSTED; } ABatchToPost.BatchStatus = MFinanceConstants.BATCH_POSTED; return APostingLevel; }
/// <summary> /// validate the attributes of the transactions /// some things are even modified, eg. batch period etc from date effective /// </summary> private static bool ValidateAnalysisAttributes(ref GLBatchTDS AGLBatchDS, GLPostingTDS APostingDS, Int32 ALedgerNumber, Int32 ABatchNumber, out TVerificationResultCollection AVerifications) { #region Validate Arguments if (AGLBatchDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"), Utilities.GetMethodName(true))); } else if (APostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (ABatchNumber <= 0) { throw new EFinanceSystemInvalidBatchNumberException(String.Format(Catalog.GetString( "Function:{0} - The Batch number in Ledger {1} must be greater than 0!"), Utilities.GetMethodName(true), ALedgerNumber), ALedgerNumber, ABatchNumber); } #endregion Validate Arguments bool CriticalError = false; AVerifications = new TVerificationResultCollection(); DataView TransactionsOfJournalView = new DataView(AGLBatchDS.ATransaction); foreach (AJournalRow journal in AGLBatchDS.AJournal.Rows) { if (journal.BatchNumber != ABatchNumber) { continue; } TransactionsOfJournalView.RowFilter = ATransactionTable.GetJournalNumberDBName() + " = " + journal.JournalNumber.ToString(); foreach (DataRowView transRowView in TransactionsOfJournalView) { ATransactionRow transRow = (ATransactionRow)transRowView.Row; // Check that all atransanalattrib records are there for all analattributes entries DataView ANView = APostingDS.AAnalysisAttribute.DefaultView; ANView.RowFilter = String.Format("{0} = '{1}' AND {2} = true", AAnalysisAttributeTable.GetAccountCodeDBName(), transRow.AccountCode, AAnalysisAttributeTable.GetActiveDBName()); int counter = 0; while (counter < ANView.Count) { AAnalysisAttributeRow attributeRow = (AAnalysisAttributeRow)ANView[counter].Row; ATransAnalAttribRow aTransAttribRow = (ATransAnalAttribRow)AGLBatchDS.ATransAnalAttrib.Rows.Find(new object[] { ALedgerNumber, ABatchNumber, transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode }); if (aTransAttribRow == null) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString( "Analysis Type {0} is missing values in journal {1}, transaction {2}"), attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber), TResultSeverity.Resv_Critical)); CriticalError = true; break; } else { String analAttrValue = aTransAttribRow.AnalysisAttributeValue; if ((analAttrValue == null) || (analAttrValue.Length == 0)) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString("Analysis Type {0} is missing values in journal {1}, transaction {2}"), attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber), TResultSeverity.Resv_Critical)); CriticalError = true; break; } else { AFreeformAnalysisRow afaRow = (AFreeformAnalysisRow)APostingDS.AFreeformAnalysis.Rows.Find( new Object[] { ALedgerNumber, attributeRow.AnalysisTypeCode, analAttrValue }); if (afaRow == null) { // this would cause a constraint error and is only possible in a development/sqlite environment AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString("Analysis Type {0} has invalid value in journal {1}, transaction {2}"), attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber), TResultSeverity.Resv_Critical)); CriticalError = true; break; } else { if (!afaRow.Active) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber), String.Format(Catalog.GetString( "Analysis Type {0} has inactive value: '{1}' in journal {2}, transaction {3}"), attributeRow.AnalysisTypeCode, analAttrValue, transRow.JournalNumber, transRow.TransactionNumber), TResultSeverity.Resv_Critical)); CriticalError = true; break; } // if } // else } // else } // else counter++; } // while i if (CriticalError) { break; } } // foreach transRowView if (CriticalError) { break; } } // foreach journal return TVerificationHelper.IsNullOrOnlyNonCritical(AVerifications); }
/// <summary> /// Only used for precalculating the new balances before the user actually posts the batch /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ABatchNumber"></param> /// <param name="ATransaction"></param> /// <param name="AVerifications"></param> /// <param name="APostingDS"></param> /// <param name="ABatchPeriod"></param> /// <returns></returns> public static bool TestPostGLBatch(Int32 ALedgerNumber, Int32 ABatchNumber, TDBTransaction ATransaction, out TVerificationResultCollection AVerifications, out GLPostingTDS APostingDS, ref Int32 ABatchPeriod) { GLBatchTDS MainDS = new GLBatchTDS(); SortedList <string, TAmount>PostingLevel = new SortedList <string, TGLPosting.TAmount>(); APostingDS = PrepareGLBatchForPosting(out MainDS, ALedgerNumber, ABatchNumber, ref ATransaction, out AVerifications, PostingLevel, ref ABatchPeriod); if ((MainDS != null) && (APostingDS != null)) { SummarizeInternal(ALedgerNumber, APostingDS, PostingLevel, ABatchPeriod, false); return true; } return false; }
/// <summary> /// load the tables that are needed for posting /// </summary> /// <param name="ALedgerNumber"></param> /// <returns></returns> private static GLPostingTDS LoadGLDataForPosting(Int32 ALedgerNumber) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } #endregion Validate Arguments GLPostingTDS PostingDS = new GLPostingTDS(); TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, Transaction); // load all accounts of ledger, because we need them later for the account hierarchy tree for summarisation AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); // TODO: use cached table? AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(PostingDS, ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, Transaction); // TODO: use cached table? ACostCentreAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); AAnalysisTypeAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); AFreeformAnalysisAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); AAnalysisAttributeAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); }); #region Validate Data //Only the following tables should not be empty when posting. if ((PostingDS.ALedger == null) || (PostingDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } else if ((PostingDS.AAccount == null) || (PostingDS.AAccount.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } else if ((PostingDS.ACostCentre == null) || (PostingDS.ACostCentre.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Cost Centre data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } else if ((PostingDS.AAccountHierarchyDetail == null) || (PostingDS.AAccountHierarchyDetail.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - AAccount Hierarchy Detail data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return PostingDS; }
/// <summary> /// load the tables that are needed for posting /// </summary> /// <param name="ALedgerNumber"></param> /// <returns></returns> private static GLPostingTDS LoadDataForPosting(Int32 ALedgerNumber) { GLPostingTDS PostingDS = new GLPostingTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, Transaction); // load all accounts of ledger, because we need them later for the account hierarchy tree for summarisation AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); // TODO: use cached table? AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(PostingDS, ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, Transaction); // TODO: use cached table? ACostCentreAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); AAnalysisTypeAccess.LoadAll(PostingDS, Transaction); AFreeformAnalysisAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); AAnalysisAttributeAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction); }); return PostingDS; }
/// <summary> /// Load all GLM and GLMPeriod records for the batch period and the following periods, since that will avoid loading them one by one during submitchanges. /// this is called after ValidateBatchAndTransactions, because the BatchYear and BatchPeriod are validated and recalculated there /// /// This should probably be changed, in the new, skinny summarization, only a few rows need to be accessed. /// </summary> private static void LoadGLMData(ref GLPostingTDS AGLPostingDS, Int32 ALedgerNumber, ABatchRow ABatchToPost) { #region Validate Arguments if (AGLPostingDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (ABatchToPost == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Batch to post data row is null!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments GLPostingTDS GLPostingDS = AGLPostingDS; TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { AGeneralLedgerMasterRow GLMTemplateRow = GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.Year = ABatchToPost.BatchYear; AGeneralLedgerMasterAccess.LoadUsingTemplate(GLPostingDS, GLMTemplateRow, Transaction); string query = "SELECT PUB_a_general_ledger_master_period.* " + "FROM PUB_a_general_ledger_master, PUB_a_general_ledger_master_period " + "WHERE PUB_a_general_ledger_master.a_ledger_number_i = ? " + "AND PUB_a_general_ledger_master.a_year_i = ? " + "AND PUB_a_general_ledger_master_period.a_glm_sequence_i = PUB_a_general_ledger_master.a_glm_sequence_i " + "AND PUB_a_general_ledger_master_period.a_period_number_i >= ?"; List <OdbcParameter>parameters = new List <OdbcParameter>(); OdbcParameter parameter = new OdbcParameter("ledgernumber", OdbcType.Int); parameter.Value = ALedgerNumber; parameters.Add(parameter); parameter = new OdbcParameter("year", OdbcType.Int); parameter.Value = ABatchToPost.BatchYear; parameters.Add(parameter); parameter = new OdbcParameter("period", OdbcType.Int); parameter.Value = ABatchToPost.BatchPeriod; parameters.Add(parameter); DBAccess.GDBAccessObj.Select(GLPostingDS, query, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } }
/// <summary> /// runs validations on batch, journals and transactions /// some things are even modified, eg. batch period etc from date effective /// </summary> private static bool ValidateBatchAndTransactions(ref GLBatchTDS AGLBatchDS, GLPostingTDS APostingDS, Int32 ALedgerNumber, ABatchRow ABatchToPost, out TVerificationResultCollection AVerifications) { AVerifications = new TVerificationResultCollection(); TVerificationResultCollection Verifications = AVerifications; if ((ABatchToPost.BatchStatus == MFinanceConstants.BATCH_CANCELLED) || (ABatchToPost.BatchStatus == MFinanceConstants.BATCH_POSTED)) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString("It has status {0}"), ABatchToPost.BatchStatus), TResultSeverity.Resv_Critical)); } // Calculate the base currency amounts for each transaction, using the exchange rate from the journals. // erm - this is done already? I don't want to do it here, since my journal may contain forex-reval elements. // Calculate the credit and debit totals GLRoutines.UpdateTotalsOfBatch(ref AGLBatchDS, ABatchToPost); if (ABatchToPost.BatchCreditTotal != ABatchToPost.BatchDebitTotal) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString("It does not balance: Debit is {0:N2}, Credit is {1:N2}"), ABatchToPost.BatchDebitTotal, ABatchToPost.BatchCreditTotal), TResultSeverity.Resv_Critical)); } else if ((ABatchToPost.BatchCreditTotal == 0) && ((AGLBatchDS.AJournal.Rows.Count == 0) || (AGLBatchDS.ATransaction.Rows.Count == 0))) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), Catalog.GetString("The batch has no monetary value. Please cancel it or add transactions."), TResultSeverity.Resv_Critical)); } else if ((ABatchToPost.BatchControlTotal != 0) && (ABatchToPost.BatchControlTotal != ABatchToPost.BatchCreditTotal)) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString("The control total {0:n2} does not fit the Credit/Debit Total {1:n2}."), ABatchToPost.BatchControlTotal, ABatchToPost.BatchCreditTotal), TResultSeverity.Resv_Critical)); } Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber; TDBTransaction Transaction = null; GLBatchTDS GLBatchDS = AGLBatchDS; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { if (!TFinancialYear.IsValidPostingPeriod(ABatchToPost.LedgerNumber, ABatchToPost.DateEffective, out DateEffectivePeriodNumber, out DateEffectiveYearNumber, Transaction)) { Verifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString("The Date Effective {0:d-MMM-yyyy} does not fit any open accounting period."), ABatchToPost.DateEffective), TResultSeverity.Resv_Critical)); } else { // just make sure that the correct BatchPeriod is used ABatchToPost.BatchPeriod = DateEffectivePeriodNumber; ABatchToPost.BatchYear = DateEffectiveYearNumber; } // check that all transactions are inside the same period as the GL date effective of the batch DateTime PostingPeriodStartDate, PostingPeriodEndDate; TFinancialYear.GetStartAndEndDateOfPeriod(ABatchToPost.LedgerNumber, DateEffectivePeriodNumber, out PostingPeriodStartDate, out PostingPeriodEndDate, Transaction); foreach (ATransactionRow transRow in GLBatchDS.ATransaction.Rows) { if ((transRow.BatchNumber == ABatchToPost.BatchNumber) && (transRow.TransactionDate < PostingPeriodStartDate) || (transRow.TransactionDate > PostingPeriodEndDate)) { Verifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format( "invalid transaction date for transaction {0} in Batch {1} Journal {2}: {3:d-MMM-yyyy} must be inside period {4} ({5:d-MMM-yyyy} till {6:d-MMM-yyyy})", transRow.TransactionNumber, transRow.BatchNumber, transRow.JournalNumber, transRow.TransactionDate, DateEffectivePeriodNumber, PostingPeriodStartDate, PostingPeriodEndDate), TResultSeverity.Resv_Critical)); } } }); AVerifications = Verifications; DataView TransactionsOfJournalView = new DataView(AGLBatchDS.ATransaction); foreach (AJournalRow journal in AGLBatchDS.AJournal.Rows) { journal.DateEffective = ABatchToPost.DateEffective; journal.JournalPeriod = ABatchToPost.BatchPeriod; if (journal.JournalCreditTotal != journal.JournalDebitTotal) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString("The journal {0} does not balance: Debit is {1:N2}, Credit is {2:N2}"), journal.JournalNumber, journal.JournalDebitTotal, journal.JournalCreditTotal), TResultSeverity.Resv_Critical)); } TransactionsOfJournalView.RowFilter = ATransactionTable.GetJournalNumberDBName() + " = " + journal.JournalNumber.ToString(); foreach (DataRowView TransactionViewRow in TransactionsOfJournalView) { ATransactionRow transaction = (ATransactionRow)TransactionViewRow.Row; // check that transactions on foreign currency accounts are using the correct currency // (fx reval transactions are an exception because they are posted in base currency) if (!((transaction.Reference == CommonAccountingTransactionTypesEnum.REVAL.ToString()) && (journal.TransactionTypeCode == CommonAccountingTransactionTypesEnum.REVAL.ToString()))) { // get the account that this transaction is writing to AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, transaction.AccountCode }); if (Account == null) { // should not get here throw new Exception("ValidateBatchAndTransactions: Cannot find account " + transaction.AccountCode); } if (Account.ForeignCurrencyFlag && (journal.TransactionCurrency != Account.ForeignCurrencyCode)) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString( "Transaction {0} in Journal {1} with currency {2} does not fit the foreign currency {3} of account {4}."), transaction.TransactionNumber, transaction.JournalNumber, journal.TransactionCurrency, Account.ForeignCurrencyCode, transaction.AccountCode), TResultSeverity.Resv_Critical)); } } if ((transaction.AmountInBaseCurrency == 0) && (transaction.TransactionAmount != 0)) { AVerifications.Add(new TVerificationResult( String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber), String.Format(Catalog.GetString("Transaction {0} in Journal {1} has invalid base transaction amount of 0."), transaction.TransactionNumber, transaction.JournalNumber), TResultSeverity.Resv_Critical)); } } } return TVerificationHelper.IsNullOrOnlyNonCritical(AVerifications); }
/// <summary> /// creates the rows for the whole current year in AGeneralLedgerMaster and AGeneralLedgerMasterPeriod for an Account/CostCentre combination /// </summary> /// <param name="AMainDS"></param> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACostCentreCode"></param> /// <returns>The new glm sequence, which is negative until SubmitChanges</returns> private static Int32 CreateGLMYear( ref GLPostingTDS AMainDS, Int32 ALedgerNumber, string AAccountCode, string ACostCentreCode) { #region Validate Arguments if (AMainDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The GL Posting dataset is null!"), Utilities.GetMethodName(true))); } else if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if ((AMainDS.ALedger == null) || (AMainDS.ALedger.Count == 0)) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Ledger table does not exist or is empty!"), Utilities.GetMethodName(true))); } else if (AAccountCode.Length == 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Account code is empty!"), Utilities.GetMethodName(true))); } else if (ACostCentreCode.Length == 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Cost Centre code is empty!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments int RetVal = 0; try { ALedgerRow LedgerRow = AMainDS.ALedger[0]; #region Validate Data if (LedgerRow == null) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data AGeneralLedgerMasterRow GLMRow = AMainDS.AGeneralLedgerMaster.NewRowTyped(); // row.GlmSequence will be set by SubmitChanges GLMRow.GlmSequence = (AMainDS.AGeneralLedgerMaster.Count * -1) - 1; GLMRow.LedgerNumber = ALedgerNumber; GLMRow.Year = LedgerRow.CurrentFinancialYear; GLMRow.AccountCode = AAccountCode; GLMRow.CostCentreCode = ACostCentreCode; AMainDS.AGeneralLedgerMaster.Rows.Add(GLMRow); for (int PeriodCount = 1; PeriodCount < LedgerRow.NumberOfAccountingPeriods + LedgerRow.NumberFwdPostingPeriods + 1; PeriodCount++) { AGeneralLedgerMasterPeriodRow PeriodRow = AMainDS.AGeneralLedgerMasterPeriod.NewRowTyped(); PeriodRow.GlmSequence = GLMRow.GlmSequence; PeriodRow.PeriodNumber = PeriodCount; AMainDS.AGeneralLedgerMasterPeriod.Rows.Add(PeriodRow); } RetVal = GLMRow.GlmSequence; } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return RetVal; }
/// <summary> /// mark each journal, each transaction as being posted; /// add sums for costcentre/account combinations /// </summary> /// <param name="MainDS">can contain several batches and journals and transactions</param> /// <param name="APostingDS"></param> /// <param name="APostingLevel">the balance changes at the posting level</param> /// <param name="ABatchToPost">the batch to post</param> /// <returns>a list with the sums for each costcentre/account combination</returns> private static SortedList <string, TAmount>MarkAsPostedAndCollectData(GLBatchTDS MainDS, GLPostingTDS APostingDS, SortedList <string, TAmount>APostingLevel, ABatchRow ABatchToPost) { DataView myView = new DataView(MainDS.ATransaction); myView.Sort = ATransactionTable.GetJournalNumberDBName(); foreach (AJournalRow journal in MainDS.AJournal.Rows) { if (journal.BatchNumber != ABatchToPost.BatchNumber) { continue; } foreach (DataRowView transactionview in myView.FindRows(journal.JournalNumber)) { ATransactionRow transaction = (ATransactionRow)transactionview.Row; if (transaction.BatchNumber != ABatchToPost.BatchNumber) { continue; } transaction.TransactionStatus = true; // get the account that this transaction is writing to AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { transaction.LedgerNumber, transaction.AccountCode }); // Set the sign of the amounts according to the debit/credit indicator decimal SignBaseAmount = transaction.AmountInBaseCurrency; decimal SignTransAmount = transaction.TransactionAmount; if (Account.DebitCreditIndicator != transaction.DebitCreditIndicator) { SignBaseAmount *= -1.0M; SignTransAmount *= -1.0M; } // TODO: do we need to check for base currency corrections? // or do we get rid of these problems by not having international currency? string key = TAmount.MakeKey(transaction.AccountCode, transaction.CostCentreCode); if (!APostingLevel.ContainsKey(key)) { APostingLevel.Add(key, new TAmount()); } APostingLevel[key].baseAmount += SignBaseAmount; // Only foreign currency accounts store a value in the transaction currency, // if the transaction was actually in the foreign currency. if (Account.ForeignCurrencyFlag && (journal.TransactionCurrency == Account.ForeignCurrencyCode)) { APostingLevel[key].transAmount += SignTransAmount; } } journal.JournalStatus = MFinanceConstants.BATCH_POSTED; } ABatchToPost.BatchStatus = MFinanceConstants.BATCH_POSTED; return APostingLevel; }
public static TSubmitChangesResult SaveGLBatchTDS(ref GLBatchTDS AInspectDS, out TVerificationResultCollection AVerificationResult) { AVerificationResult = new TVerificationResultCollection(); TVerificationResultCollection VerificationResult = AVerificationResult; // make sure that empty tables are removed. This can return NULL!! AInspectDS = AInspectDS.GetChangesTyped(true); if (AInspectDS == null) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Save GL Batch"), Catalog.GetString("No changes - nothing to do"), TResultSeverity.Resv_Info)); return TSubmitChangesResult.scrNothingToBeSaved; } bool AllValidationsOK = true; bool GLBatchTableInDataSet = (AInspectDS.ABatch != null && AInspectDS.ABatch.Count > 0); bool GLJournalTableInDataSet = (AInspectDS.AJournal != null && AInspectDS.AJournal.Count > 0); bool GLTransTableInDataSet = (AInspectDS.ATransaction != null && AInspectDS.ATransaction.Count > 0); bool GLTransAttrTableInDataSet = (AInspectDS.ATransAnalAttrib != null && AInspectDS.ATransAnalAttrib.Count > 0); bool RecurrGLBatchTableInDataSet = (AInspectDS.ARecurringBatch != null && AInspectDS.ARecurringBatch.Count > 0); bool RecurrGLJournalTableInDataSet = (AInspectDS.ARecurringJournal != null && AInspectDS.ARecurringJournal.Count > 0); bool RecurrGLTransTableInDataSet = (AInspectDS.ARecurringTransaction != null && AInspectDS.ARecurringTransaction.Count > 0); bool RecurrGLAttrTableInDataSet = (AInspectDS.ARecurringTransAnalAttrib != null && AInspectDS.ARecurringTransAnalAttrib.Count > 0); //Check if saving recurring tables if (RecurrGLBatchTableInDataSet || RecurrGLJournalTableInDataSet || RecurrGLTransTableInDataSet || RecurrGLAttrTableInDataSet) { if (GLBatchTableInDataSet || GLJournalTableInDataSet || GLTransTableInDataSet || GLTransAttrTableInDataSet) { throw new Exception(String.Format("Function:{0} - Recurring and normal GL data found in same changes batch!", Utilities.GetMethodName(true))); } return SaveRecurringGLBatchTDS(ref AInspectDS, ref AVerificationResult, RecurrGLBatchTableInDataSet, RecurrGLJournalTableInDataSet, RecurrGLTransTableInDataSet, RecurrGLAttrTableInDataSet); } else { if (!(GLBatchTableInDataSet || GLJournalTableInDataSet || GLTransTableInDataSet || GLTransAttrTableInDataSet)) { throw new Exception(String.Format("Function:{0} - No GL data changes to save!", Utilities.GetMethodName(true))); } } GLBatchTDS InspectDS = AInspectDS; List <Int32>ListAllGLBatchesToProcess = new List <int>(); Int32 LedgerNumber = -1; TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction, delegate { if (GLBatchTableInDataSet) { DataView AllBatchesToProcess = new DataView(InspectDS.ABatch); AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added; foreach (DataRowView drv in AllBatchesToProcess) { ABatchRow glbr = (ABatchRow)drv.Row; int batchNumber; if (glbr.RowState != DataRowState.Deleted) { LedgerNumber = glbr.LedgerNumber; batchNumber = glbr.BatchNumber; } else { LedgerNumber = (Int32)glbr[ABatchTable.ColumnLedgerNumberId, DataRowVersion.Original]; batchNumber = (Int32)glbr[ABatchTable.ColumnBatchNumberId, DataRowVersion.Original]; } if (!ListAllGLBatchesToProcess.Contains(batchNumber)) { ListAllGLBatchesToProcess.Add(batchNumber); } int periodNumber, yearNumber; if (TFinancialYear.IsValidPostingPeriod(LedgerNumber, glbr.DateEffective, out periodNumber, out yearNumber, Transaction)) { glbr.BatchYear = yearNumber; glbr.BatchPeriod = periodNumber; } } //TODO add validation as with gift //ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail); //ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail); if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult)) { AllValidationsOK = false; } } if (GLJournalTableInDataSet) { DataView AllBatchesToProcess = new DataView(InspectDS.AJournal); AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added; foreach (DataRowView drv in AllBatchesToProcess) { GLBatchTDSAJournalRow gljr = (GLBatchTDSAJournalRow)drv.Row; int batchNumber; if (gljr.RowState != DataRowState.Deleted) { if (LedgerNumber == -1) { LedgerNumber = gljr.LedgerNumber; } batchNumber = gljr.BatchNumber; } else { if (LedgerNumber == -1) { LedgerNumber = (Int32)gljr[AJournalTable.ColumnLedgerNumberId, DataRowVersion.Original]; } batchNumber = (Int32)gljr[AJournalTable.ColumnBatchNumberId, DataRowVersion.Original]; } if (!ListAllGLBatchesToProcess.Contains(batchNumber)) { ListAllGLBatchesToProcess.Add(batchNumber); } } //TODO add validation as with gift //ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail); //ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail); if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult)) { AllValidationsOK = false; } } if (GLTransTableInDataSet) { DataView AllBatchesToProcess = new DataView(InspectDS.ATransaction); AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added; GLPostingTDS accountsAndCostCentresDS = new GLPostingTDS(); foreach (DataRowView drv in AllBatchesToProcess) { ATransactionRow gltr = (ATransactionRow)drv.Row; int batchNumber; if (gltr.RowState != DataRowState.Deleted) { if (LedgerNumber == -1) { LedgerNumber = gltr.LedgerNumber; } batchNumber = gltr.BatchNumber; //Prepare to test for valid account and cost centre code if ((accountsAndCostCentresDS.AAccount == null) || (accountsAndCostCentresDS.AAccount.Count == 0)) { AAccountAccess.LoadViaALedger(accountsAndCostCentresDS, LedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(accountsAndCostCentresDS, LedgerNumber, Transaction); #region Validate Data if ((accountsAndCostCentresDS.AAccount == null) || (accountsAndCostCentresDS.AAccount.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), LedgerNumber)); } else if ((accountsAndCostCentresDS.ACostCentre == null) || (accountsAndCostCentresDS.ACostCentre.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Cost Centre data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), LedgerNumber)); } #endregion Validate Data } CheckTransactionAccountAndCostCentre(LedgerNumber, ref accountsAndCostCentresDS, ref gltr, ref VerificationResult); } else { if (LedgerNumber == -1) { LedgerNumber = (Int32)gltr[ATransactionTable.ColumnLedgerNumberId, DataRowVersion.Original]; } batchNumber = (Int32)gltr[ATransactionTable.ColumnBatchNumberId, DataRowVersion.Original]; } if (!ListAllGLBatchesToProcess.Contains(batchNumber)) { ListAllGLBatchesToProcess.Add(batchNumber); } } //TODO add validation as with gift //ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail); //ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail); if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult)) { AllValidationsOK = false; } } if (GLTransAttrTableInDataSet) { DataView AllBatchesToProcess = new DataView(InspectDS.ATransAnalAttrib); AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added; foreach (DataRowView drv in AllBatchesToProcess) { ATransAnalAttribRow glta = (ATransAnalAttribRow)drv.Row; int batchNumber; if (glta.RowState != DataRowState.Deleted) { if (LedgerNumber == -1) { LedgerNumber = glta.LedgerNumber; } batchNumber = glta.BatchNumber; } else { if (LedgerNumber == -1) { LedgerNumber = (Int32)glta[ATransAnalAttribTable.ColumnLedgerNumberId, DataRowVersion.Original]; } batchNumber = (Int32)glta[ATransAnalAttribTable.ColumnBatchNumberId, DataRowVersion.Original]; } if (!ListAllGLBatchesToProcess.Contains(batchNumber)) { ListAllGLBatchesToProcess.Add(batchNumber); } } //TODO add validation as with gift //ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail); //ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail); if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult)) { AllValidationsOK = false; } } // load previously stored batches and check for posted status if (ListAllGLBatchesToProcess.Count == 0) { VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"), Catalog.GetString("Cannot save an empty Batch!"), TResultSeverity.Resv_Critical)); } else { string listOfBatchNumbers = string.Empty; foreach (Int32 batchNumber in ListAllGLBatchesToProcess) { listOfBatchNumbers = StringHelper.AddCSV(listOfBatchNumbers, batchNumber.ToString()); } string SQLStatement = "SELECT * " + " FROM PUB_" + ABatchTable.GetTableDBName() + " WHERE " + ABatchTable.GetLedgerNumberDBName() + " = " + LedgerNumber.ToString() + " AND " + ABatchTable.GetBatchNumberDBName() + " IN (" + listOfBatchNumbers + ")"; GLBatchTDS BatchDS = new GLBatchTDS(); DBAccess.GDBAccessObj.Select(BatchDS, SQLStatement, BatchDS.ABatch.TableName, Transaction); foreach (ABatchRow batch in BatchDS.ABatch.Rows) { if ((batch.BatchStatus == MFinanceConstants.BATCH_POSTED) || (batch.BatchStatus == MFinanceConstants.BATCH_CANCELLED)) { VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"), String.Format(Catalog.GetString("Cannot modify Batch {0} because it is {1}"), batch.BatchNumber, batch.BatchStatus), TResultSeverity.Resv_Critical)); } } } }); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } if (AVerificationResult.Count > 0) { // Downgrade TScreenVerificationResults to TVerificationResults in order to allow // Serialisation (needed for .NET Remoting). TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult); } if (!TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult)) { return TSubmitChangesResult.scrError; } TSubmitChangesResult SubmissionResult; if (AllValidationsOK) { //Need to save changes before deleting any transactions GLBatchTDSAccess.SubmitChanges(AInspectDS); SubmissionResult = TSubmitChangesResult.scrOK; } else { SubmissionResult = TSubmitChangesResult.scrError; } return SubmissionResult; }
/// <summary> /// Calculate the summarization trees for each posting account and each /// posting cost centre. The result of the union of these trees, /// excluding the base posting/posting combination, is the set of /// accounts that receive the summary data. /// </summary> private static bool CalculateTrees( Int32 ALedgerNumber, ref SortedList <string, TAmount>APostingLevel, out SortedList <string, TAccountTreeElement>AAccountTree, out SortedList <string, string>ACostCentreTree, GLPostingTDS APostingDS) { // get all accounts that each posting level account is directly or indirectly posting to AAccountTree = new SortedList <string, TAccountTreeElement>(); foreach (string PostingLevelKey in APostingLevel.Keys) { string AccountCode = TAmount.GetAccountCode(PostingLevelKey); // only once for each account, even though there might be several entries for one account in APostingLevel because of different costcentres if (AAccountTree.ContainsKey(TAccountTreeElement.MakeKey(AccountCode, AccountCode))) { continue; } AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, AccountCode }); bool DebitCreditIndicator = Account.DebitCreditIndicator; AAccountTree.Add(TAccountTreeElement.MakeKey(AccountCode, AccountCode), new TAccountTreeElement(false, Account.ForeignCurrencyFlag)); AAccountHierarchyDetailRow HierarchyDetail = (AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find( new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, AccountCode }); while (HierarchyDetail != null) { Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, HierarchyDetail.AccountCodeToReportTo }); if (Account == null) { // current account is BAL SHT, and it reports nowhere (account with name = ledgernumber does not exist) break; } AAccountTree.Add(TAccountTreeElement.MakeKey(AccountCode, HierarchyDetail.AccountCodeToReportTo), new TAccountTreeElement(DebitCreditIndicator != Account.DebitCreditIndicator, Account.ForeignCurrencyFlag)); HierarchyDetail = (AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find( new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, HierarchyDetail.AccountCodeToReportTo }); } } ACostCentreTree = new SortedList <string, string>(); foreach (string PostingLevelKey in APostingLevel.Keys) { string CostCentreCode = TAmount.GetCostCentreCode(PostingLevelKey); // only once for each cost centre if (ACostCentreTree.ContainsKey(CostCentreCode + ":" + CostCentreCode)) { continue; } ACostCentreTree.Add(CostCentreCode + ":" + CostCentreCode, CostCentreCode + ":" + CostCentreCode); ACostCentreRow CostCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, CostCentreCode }); while (!CostCentre.IsCostCentreToReportToNull()) { ACostCentreTree.Add(CostCentreCode + ":" + CostCentre.CostCentreToReportTo, CostCentreCode + ":" + CostCentre.CostCentreToReportTo); CostCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, CostCentre.CostCentreToReportTo }); } } return true; }
private static void CheckTransactionAccountAndCostCentre(Int32 ALedgerNumber, ref GLPostingTDS AAccountsAndCostCentresDS, ref ATransactionRow ATransRow, ref TVerificationResultCollection AVerificationResult) { // check for valid accounts and cost centres if (AAccountsAndCostCentresDS.AAccount.Rows.Find(new object[] { ALedgerNumber, ATransRow.AccountCode }) == null) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Cannot save transaction"), String.Format(Catalog.GetString("Invalid account code {0} in batch {1}, journal {2}, transaction {3}"), ATransRow.AccountCode, ATransRow.BatchNumber, ATransRow.JournalNumber, ATransRow.TransactionNumber), TResultSeverity.Resv_Critical)); } if (AAccountsAndCostCentresDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, ATransRow.CostCentreCode }) == null) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Cannot save transaction"), String.Format(Catalog.GetString("Invalid cost centre code {0} in batch {1}, journal {2}, transaction {3}"), ATransRow.CostCentreCode, ATransRow.BatchNumber, ATransRow.JournalNumber, ATransRow.TransactionNumber), TResultSeverity.Resv_Critical)); } // AmountInBaseCurrency must be greater than 0. // Transaction amount can be 0 if ForexGain. if (ATransRow.AmountInBaseCurrency <= 0) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Cannot save transaction"), String.Format(Catalog.GetString("Invalid amount in batch {0}, journal {1}, transaction {2}. " + "Either the debit amount or the credit amount needs to be greater than 0."), ATransRow.BatchNumber, ATransRow.JournalNumber, ATransRow.TransactionNumber), TResultSeverity.Resv_Critical)); } }
/// <summary> /// on the posting level propagate the value from the posting period through the following periods; /// in this version of SummarizeData, there is no calculation of summary accounts/cost centres, since that can be done by the reports /// </summary> private static bool SummarizeDataSimple( Int32 ALedgerNumber, GLPostingTDS AMainDS, Int32 AFromPeriod, ref SortedList <string, TAmount>APostingLevel) { if (AMainDS.ALedger[0].ProvisionalYearEndFlag) { // If the year end close is running, then we are posting the year end // reallocations. These appear as part of the final period, but // should only be written to the forward periods. // In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i. AFromPeriod++; } DataView GLMMasterView = AMainDS.AGeneralLedgerMaster.DefaultView; GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName(); DataView GLMPeriodView = AMainDS.AGeneralLedgerMasterPeriod.DefaultView; GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName(); // Loop through the posting data collected earlier. Summarize it to a // temporary table, which is much faster than finding and updating records // in the glm tables multiple times. WriteData will write it to the real // tables in a single pass. foreach (string PostingLevelKey in APostingLevel.Keys) { string AccountCode = TAmount.GetAccountCode(PostingLevelKey); string CostCentreCode = TAmount.GetCostCentreCode(PostingLevelKey); TAmount PostingLevelElement = APostingLevel[PostingLevelKey]; // Find the posting level, creating it if it does not already exist. int GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCode, CostCentreCode }); AGeneralLedgerMasterRow GlmRow; if (GLMMasterIndex == -1) { CreateGLMYear( ref AMainDS, ALedgerNumber, AccountCode, CostCentreCode); GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCode, CostCentreCode }); } GlmRow = (AGeneralLedgerMasterRow)GLMMasterView[GLMMasterIndex].Row; GlmRow.YtdActualBase += PostingLevelElement.baseAmount; AAccountRow account = (AAccountRow)AMainDS.AAccount.Rows.Find(new object[] { ALedgerNumber, AccountCode }); if (account.ForeignCurrencyFlag) { if (GlmRow.IsYtdActualForeignNull()) { GlmRow.YtdActualForeign = PostingLevelElement.transAmount; } else { GlmRow.YtdActualForeign += PostingLevelElement.transAmount; } } if (AMainDS.ALedger[0].ProvisionalYearEndFlag) { GlmRow.ClosingPeriodActualBase += PostingLevelElement.baseAmount; } // Last use of GlmRow in this routine ... // propagate the data through the following periods for (Int32 PeriodCount = AFromPeriod; PeriodCount <= AMainDS.ALedger[0].NumberOfAccountingPeriods + AMainDS.ALedger[0].NumberFwdPostingPeriods; PeriodCount++) { int GLMPeriodIndex = GLMPeriodView.Find(new object[] { GlmRow.GlmSequence, PeriodCount }); AGeneralLedgerMasterPeriodRow GlmPeriodRow; if (GLMPeriodIndex == -1) { GlmPeriodRow = AMainDS.AGeneralLedgerMasterPeriod.NewRowTyped(); GlmPeriodRow.GlmSequence = GlmRow.GlmSequence; GlmPeriodRow.PeriodNumber = PeriodCount; } else { GlmPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[GLMPeriodIndex].Row; } GlmPeriodRow.ActualBase += PostingLevelElement.baseAmount; if (account.ForeignCurrencyFlag) { if (GlmPeriodRow.IsActualForeignNull()) { GlmPeriodRow.ActualForeign = PostingLevelElement.transAmount; } else { GlmPeriodRow.ActualForeign += PostingLevelElement.transAmount; } } } } GLMMasterView.Sort = ""; GLMPeriodView.Sort = ""; return true; }
private static void SummarizeInternal(Int32 ALedgerNumber, GLPostingTDS APostingDS, SortedList <string, TAmount>APostingLevel, Int32 AFromPeriod, bool ACalculatePostingTree) { // we need the tree, because of the cost centre tree, which is not calculated by the balance sheet and other reports. // for testing the balances, we don't need to calculate the whole tree if (ACalculatePostingTree) { TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: CalculateTrees..."); // key is PostingAccount, the value TAccountTreeElement describes the parent account and other details of the relation SortedList <string, TAccountTreeElement>AccountTree; // key is the PostingCostCentre, the value is the parent Cost Centre SortedList <string, string>CostCentreTree; // TODO Can anything of this be done in StoredProcedures? Only SQLite here? // this was in Petra 2.x; takes a lot of time, which the reports could do better // TODO: can we just calculate the cost centre tree, since that is needed for Balance Sheet, // but avoid calculating the whole account tree? CalculateTrees(ALedgerNumber, ref APostingLevel, out AccountTree, out CostCentreTree, APostingDS); TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeData..."); SummarizeData(APostingDS, AFromPeriod, ref APostingLevel, ref AccountTree, ref CostCentreTree); } else { TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeDataSimple..."); SummarizeDataSimple(ALedgerNumber, APostingDS, AFromPeriod, ref APostingLevel); } }
public static TSubmitChangesResult SaveGLBatchTDS(ref GLBatchTDS AInspectDS, out TVerificationResultCollection AVerificationResult) { AVerificationResult = new TVerificationResultCollection(); TVerificationResultCollection VerificationResult = AVerificationResult; // make sure that empty tables are removed AInspectDS = AInspectDS.GetChangesTyped(true); bool batchTableInDataSet = (AInspectDS.ABatch != null); bool journalTableInDataSet = (AInspectDS.AJournal != null); bool transTableInDataSet = (AInspectDS.ATransaction != null); bool attrTableInDataSet = (AInspectDS.ATransAnalAttrib != null); bool recurrBatchTableInDataSet = (AInspectDS.ARecurringBatch != null); bool recurrJournalTableInDataSet = (AInspectDS.ARecurringJournal != null); bool recurrTransTableInDataSet = (AInspectDS.ARecurringTransaction != null); //bool newTransaction = false; TDBTransaction Transaction = null; GLBatchTDS InspectDS = AInspectDS; // calculate debit and credit sums for journal and batch? but careful: we only have the changed parts! // no, we calculate the debit and credit sums before the posting, with GLRoutines.UpdateTotalsOfBatch // check added and modified and deleted rows: are they related to a posted or cancelled batch? we must not save adjusted posted batches! List <Int32>BatchNumbersInvolved = new List <int>(); Int32 LedgerNumber = -1; //Check if saving recurring tables if (recurrBatchTableInDataSet || recurrJournalTableInDataSet || recurrTransTableInDataSet) { if (batchTableInDataSet || journalTableInDataSet || transTableInDataSet || attrTableInDataSet) { throw new Exception( "SaveGLBatchTDS: need to call GetChangesTyped before saving, otherwise confusion about recurring or normal gl batch"); } return SaveRecurringGLBatchTDS(ref AInspectDS); } DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction, delegate { if (batchTableInDataSet) { LedgerNumber = ((ABatchRow)InspectDS.ABatch.Rows[0]).LedgerNumber; foreach (ABatchRow batch in InspectDS.ABatch.Rows) { if (batch.RowState != DataRowState.Added) { Int32 BatchNumber; try { BatchNumber = batch.BatchNumber; } catch (Exception) { // for deleted batches BatchNumber = (Int32)batch[ABatchTable.ColumnBatchNumberId, DataRowVersion.Original]; } if (!BatchNumbersInvolved.Contains(BatchNumber)) { BatchNumbersInvolved.Add(BatchNumber); } } int PeriodNumber, YearNr; if (TFinancialYear.IsValidPostingPeriod(LedgerNumber, batch.DateEffective, out PeriodNumber, out YearNr, Transaction)) { batch.BatchYear = YearNr; batch.BatchPeriod = PeriodNumber; } } } if (journalTableInDataSet) { if (LedgerNumber == -1) { LedgerNumber = ((AJournalRow)InspectDS.AJournal.Rows[0]).LedgerNumber; } foreach (GLBatchTDSAJournalRow journal in InspectDS.AJournal.Rows) { Int32 BatchNumber; try { BatchNumber = journal.BatchNumber; LedgerNumber = journal.LedgerNumber; } catch (Exception) { // for deleted journals BatchNumber = (Int32)journal[AJournalTable.ColumnBatchNumberId, DataRowVersion.Original]; LedgerNumber = (Int32)journal[AJournalTable.ColumnLedgerNumberId, DataRowVersion.Original]; } if (!BatchNumbersInvolved.Contains(BatchNumber)) { BatchNumbersInvolved.Add(BatchNumber); } } } if (transTableInDataSet) { if (LedgerNumber == -1) { LedgerNumber = ((ATransactionRow)InspectDS.ATransaction.Rows[0]).LedgerNumber; } GLPostingTDS TestAccountsAndCostCentres = new GLPostingTDS(); foreach (ATransactionRow transaction in InspectDS.ATransaction.Rows) { Int32 BatchNumber; try { BatchNumber = transaction.BatchNumber; LedgerNumber = transaction.LedgerNumber; if (TestAccountsAndCostCentres.AAccount.Count == 0) { AAccountAccess.LoadViaALedger(TestAccountsAndCostCentres, LedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(TestAccountsAndCostCentres, LedgerNumber, Transaction); } // TODO could check for active accounts and cost centres? // check for valid accounts and cost centres if (TestAccountsAndCostCentres.AAccount.Rows.Find(new object[] { LedgerNumber, transaction.AccountCode }) == null) { VerificationResult.Add(new TVerificationResult( Catalog.GetString("Cannot save transaction"), String.Format(Catalog.GetString("Invalid account code {0} in batch {1}, journal {2}, transaction {3}"), transaction.AccountCode, transaction.BatchNumber, transaction.JournalNumber, transaction.TransactionNumber), TResultSeverity.Resv_Critical)); } if (TestAccountsAndCostCentres.ACostCentre.Rows.Find(new object[] { LedgerNumber, transaction.CostCentreCode }) == null) { VerificationResult.Add(new TVerificationResult( Catalog.GetString("Cannot save transaction"), String.Format(Catalog.GetString("Invalid cost centre code {0} in batch {1}, journal {2}, transaction {3}"), transaction.CostCentreCode, transaction.BatchNumber, transaction.JournalNumber, transaction.TransactionNumber), TResultSeverity.Resv_Critical)); } // AmountInBaseCurrency must be greater than 0. // Transaction amount can be 0 if ForexGain. if (transaction.AmountInBaseCurrency <= 0) { VerificationResult.Add(new TVerificationResult( Catalog.GetString("Cannot save transaction"), String.Format(Catalog.GetString("Invalid amount in batch {0}, journal {1}, transaction {2}. " + "Either the debit amount or the credit amount needs to be greater than 0."), transaction.BatchNumber, transaction.JournalNumber, transaction.TransactionNumber), TResultSeverity.Resv_Critical)); } } catch (Exception) { // for deleted transactions BatchNumber = (Int32)transaction[ATransactionTable.ColumnBatchNumberId, DataRowVersion.Original]; LedgerNumber = (Int32)transaction[ATransactionTable.ColumnLedgerNumberId, DataRowVersion.Original]; } if (!BatchNumbersInvolved.Contains(BatchNumber)) { BatchNumbersInvolved.Add(BatchNumber); } } } if (attrTableInDataSet) { foreach (ATransAnalAttribRow transAnalAttrib in InspectDS.ATransAnalAttrib.Rows) { Int32 BatchNumber; if (transAnalAttrib.RowState != DataRowState.Deleted) { BatchNumber = transAnalAttrib.BatchNumber; if (!BatchNumbersInvolved.Contains(BatchNumber)) { BatchNumbersInvolved.Add(BatchNumber); } } } } // load previously stored batches and check for posted status if (BatchNumbersInvolved.Count == 0) { VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"), Catalog.GetString("Cannot save an empty Batch!"), TResultSeverity.Resv_Critical)); } else { string ListOfBatchNumbers = string.Empty; foreach (Int32 BatchNumber in BatchNumbersInvolved) { ListOfBatchNumbers = StringHelper.AddCSV(ListOfBatchNumbers, BatchNumber.ToString()); } string SQLStatement = "SELECT * " + " FROM PUB_" + ABatchTable.GetTableDBName() + " WHERE " + ABatchTable.GetLedgerNumberDBName() + " = " + LedgerNumber.ToString() + " AND " + ABatchTable.GetBatchNumberDBName() + " IN (" + ListOfBatchNumbers + ")"; GLBatchTDS BatchDS = new GLBatchTDS(); DBAccess.GDBAccessObj.Select(BatchDS, SQLStatement, BatchDS.ABatch.TableName, Transaction); foreach (ABatchRow batch in BatchDS.ABatch.Rows) { if ((batch.BatchStatus == MFinanceConstants.BATCH_POSTED) || (batch.BatchStatus == MFinanceConstants.BATCH_CANCELLED)) { VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"), String.Format(Catalog.GetString("Cannot modify Batch {0} because it is {1}"), batch.BatchNumber, batch.BatchStatus), TResultSeverity.Resv_Critical)); } } } }); AVerificationResult = VerificationResult; if (!TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult)) { return TSubmitChangesResult.scrError; } //Need to save changes before deleting any transactions GLBatchTDSAccess.SubmitChanges(AInspectDS); TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrOK; if ((transTableInDataSet) && (AInspectDS.ATransaction.Rows.Count > 0)) { //Accept deletion of Attributes to allow deletion of transactions if (attrTableInDataSet) { AInspectDS.ATransAnalAttrib.AcceptChanges(); } AInspectDS.ATransaction.AcceptChanges(); if (AInspectDS.ATransaction.Count > 0) { ATransactionRow tranR = (ATransactionRow)AInspectDS.ATransaction.Rows[0]; Int32 currentLedger = tranR.LedgerNumber; Int32 currentBatch = tranR.BatchNumber; Int32 currentJournal = tranR.JournalNumber; Int32 transToDelete = 0; try { //Check if any records have been marked for deletion DataRow[] foundTransactionForDeletion = AInspectDS.ATransaction.Select(String.Format("{0} = '{1}'", ATransactionTable.GetSubTypeDBName(), MFinanceConstants.MARKED_FOR_DELETION)); if (foundTransactionForDeletion.Length > 0) { ATransactionRow transRowClient = null; for (int i = 0; i < foundTransactionForDeletion.Length; i++) { transRowClient = (ATransactionRow)foundTransactionForDeletion[i]; transToDelete = transRowClient.TransactionNumber; TLogging.Log(String.Format("Transaction to Delete: {0} from Journal: {1} in Batch: {2}", transToDelete, currentJournal, currentBatch)); transRowClient.Delete(); } //Submit all changes GLBatchTDSAccess.SubmitChanges(AInspectDS); SubmissionResult = TSubmitChangesResult.scrOK; } } catch (Exception ex) { TLogging.Log("Saving DataSet: " + ex.Message); TLogging.Log(String.Format("Error trying to save transaction: {0} in Journal: {1}, Batch: {2}", transToDelete, currentJournal, currentBatch )); SubmissionResult = TSubmitChangesResult.scrError; } } } return SubmissionResult; }
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber) { FBudgetTDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction); string sqlLoadBudgetForThisAndNextYear = string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)", ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); List <OdbcParameter>parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("thisyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear; parameters.Add(param); param = new OdbcParameter("nextyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1; parameters.Add(param); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction, parameters.ToArray()); string sqlLoadBudgetPeriodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", ABudgetPeriodTable.GetTableDBName(), ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetPeriodForThisAndNextYear, FBudgetTDS.ABudgetPeriod.TableName, Transaction, parameters.ToArray()); // Accept row changes here so that the Client gets 'unmodified' rows FBudgetTDS.AcceptChanges(); GLPostingDS = new GLPostingTDS(); AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction); // get the glm sequences for this year and next year for (int i = 0; i <= 1; i++) { int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i; AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = Year; GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction)); } string sqlLoadGlmperiodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(GLPostingDS, sqlLoadGlmperiodForThisAndNextYear, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); GLPostingDS.AcceptChanges(); return true; }