/// <summary> /// Reads from the table holding all the fees charged for this month and generates a GL batch from it. /// Relates to gl2150.p /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="APeriodNumber"></param> /// <param name="APrintReport"></param> /// <param name="ADBTransaction"></param> /// <param name="AVerificationResult"></param> /// <returns></returns> private static bool GenerateAdminFeeBatch(int ALedgerNumber, int APeriodNumber, bool APrintReport, TDBTransaction ADBTransaction, ref TVerificationResultCollection AVerificationResult ) { bool IsSuccessful = false; bool CreatedSuccessfully = false; decimal TransactionAmount; string DrAccountCode; string DestCostCentreCode = string.Empty; string DestAccountCode = string.Empty; string FeeDescription = string.Empty; decimal DrFeeTotal = 0; bool DrCrIndicator = true; //Error handling string ErrorContext = String.Empty; string ErrorMessage = String.Empty; //Set default type as non-critical TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical; /* Make a temporary table to hold totals for gifts going to * each account. */ GLStewardshipCalculationTDSCreditFeeTotalTable CreditFeeTotalDT = new GLStewardshipCalculationTDSCreditFeeTotalTable(); //int x = CreditFeeTotalDT.Count; /* Retrieve info on the ledger. */ ALedgerTable AvailableLedgers = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ADBTransaction); ALedgerRow LedgerRow = (ALedgerRow)AvailableLedgers.Rows[0]; try { /* Check that we have not closed all periods for the year yet. * (Not at the provisional year end point) */ if (LedgerRow.ProvisionalYearEndFlag) { //Petra ErrorCode = GL0071 ErrorContext = Catalog.GetString("Generate Admin Fee Batch"); ErrorMessage = String.Format(Catalog.GetString( "Cannot progress as Ledger {0} is at the provisional year-end point"), ALedgerNumber); ErrorType = TResultSeverity.Resv_Critical; throw new System.InvalidOperationException(ErrorMessage); } /* 0003 Finds for ledger base currency format, for report currency format */ ACurrencyTable CurrencyInfo = ACurrencyAccess.LoadByPrimaryKey(LedgerRow.BaseCurrency, ADBTransaction); ACurrencyRow CurrencyRow = (ACurrencyRow)CurrencyInfo.Rows[0]; /* 0001 Extract number of decimal places */ string NumericFormat = CurrencyRow.DisplayFormat; int NumDecPlaces = THelperNumeric.CalcNumericFormatDecimalPlaces(NumericFormat); /* Create the journal to create the fee transactions in, if there are * fees to charge. * NOTE: if the date in the processed fee table is ? then that fee * hasn't been processed. */ AProcessedFeeTable ProcessedFeeDataTable = new AProcessedFeeTable(); string sqlStmt = String.Format("SELECT * FROM {0} WHERE {1} = ? AND {2} = ? AND {3} IS NULL AND {4} <> 0 ORDER BY {5}, {6}", AProcessedFeeTable.GetTableDBName(), AProcessedFeeTable.GetLedgerNumberDBName(), AProcessedFeeTable.GetPeriodNumberDBName(), AProcessedFeeTable.GetProcessedDateDBName(), AProcessedFeeTable.GetPeriodicAmountDBName(), AProcessedFeeTable.GetFeeCodeDBName(), AProcessedFeeTable.GetCostCentreCodeDBName() ); OdbcParameter[] parameters = new OdbcParameter[2]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("PeriodNumber", OdbcType.Int); parameters[1].Value = APeriodNumber; DBAccess.GDBAccessObj.SelectDT(ProcessedFeeDataTable, sqlStmt, ADBTransaction, parameters, -1, -1); if (ProcessedFeeDataTable.Count == 0) { if (TLogging.DebugLevel > 0) { TLogging.Log("No fees to charge were found"); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Admin Fee Batch"), String.Format(Catalog.GetString("No admin fees charged in period {0}."), APeriodNumber), TResultSeverity.Resv_Status)); } IsSuccessful = true; } else { //Post to Ledger - Ln 132 //****************4GL Transaction Starts Here******************** AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, ADBTransaction); AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0]; // Create a Batch. If no fees are to be charged, I'll delete this batch later. GLBatchTDS AdminFeeDS = TGLPosting.CreateABatch(ALedgerNumber, Catalog.GetString( "Admin Fees & Grants"), 0, AccountingPeriodRow.PeriodEndDate); ABatchRow BatchRow = AdminFeeDS.ABatch[0]; AJournalRow JournalRow = AdminFeeDS.AJournal.NewRowTyped(); JournalRow.LedgerNumber = ALedgerNumber; JournalRow.BatchNumber = BatchRow.BatchNumber; JournalRow.JournalNumber = ++BatchRow.LastJournal; JournalRow.JournalDescription = BatchRow.BatchDescription; JournalRow.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL; JournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString(); JournalRow.TransactionCurrency = LedgerRow.BaseCurrency; JournalRow.ExchangeRateToBase = 1; JournalRow.DateEffective = AccountingPeriodRow.PeriodEndDate; JournalRow.JournalPeriod = APeriodNumber; AdminFeeDS.AJournal.Rows.Add(JournalRow); // // Generate the transactions // /* M009 Changed the following loop for Petra 2.1 design changes. a_processed_fee * now has a record for each gift detail so it is necessary to sum up all the * totals for each fee code/cost centre so that only one transaction is posted * for each */ int GLJournalNumber = JournalRow.JournalNumber; int GLTransactionNumber = 0; string CurrentFeeCode = string.Empty; string CostCentreCode = string.Empty; string CostCentreCodeDBName = AProcessedFeeTable.GetCostCentreCodeDBName(); for (int i = 0; i < ProcessedFeeDataTable.Count; i++) { AProcessedFeeRow pFR = (AProcessedFeeRow)ProcessedFeeDataTable.Rows[i]; if (CurrentFeeCode != pFR.FeeCode) { CurrentFeeCode = pFR.FeeCode; // Find first AFeesPayableTable FeesPayableTable = AFeesPayableAccess.LoadByPrimaryKey(ALedgerNumber, CurrentFeeCode, ADBTransaction); if (FeesPayableTable.Count > 0) //if null try receivables instead { AFeesPayableRow FeesPayableRow = (AFeesPayableRow)FeesPayableTable.Rows[0]; DrAccountCode = FeesPayableRow.DrAccountCode; DestCostCentreCode = FeesPayableRow.CostCentreCode; DestAccountCode = FeesPayableRow.AccountCode; FeeDescription = FeesPayableRow.FeeDescription; } else { AFeesReceivableTable FeesReceivableTable = AFeesReceivableAccess.LoadByPrimaryKey(ALedgerNumber, CurrentFeeCode, ADBTransaction); if (FeesReceivableTable.Count > 0) { AFeesReceivableRow FeesReceivableRow = (AFeesReceivableRow)FeesReceivableTable.Rows[0]; DrAccountCode = FeesReceivableRow.DrAccountCode; DestCostCentreCode = FeesReceivableRow.CostCentreCode; DestAccountCode = FeesReceivableRow.AccountCode; FeeDescription = FeesReceivableRow.FeeDescription; } else { //Petra error: X_0007 ErrorContext = Catalog.GetString("Generate Transactions"); ErrorMessage = String.Format(Catalog.GetString( "Unable to access information for Fee Code '{1}' in either the Fees Payable & Receivable Tables for Ledger {0}"), ALedgerNumber, CurrentFeeCode); ErrorType = TResultSeverity.Resv_Critical; throw new System.InvalidOperationException(ErrorMessage); } } DrFeeTotal = 0; //Get all the distinct CostCentres DataView CostCentreView = ProcessedFeeDataTable.DefaultView; CostCentreView.Sort = CostCentreCodeDBName; CostCentreView.RowFilter = string.Format("{0} = '{1}'", AProcessedFeeTable.GetFeeCodeDBName(), CurrentFeeCode); DataTable ProcessedFeeCostCentresTable = CostCentreView.ToTable(true, CostCentreCodeDBName); foreach (DataRow r in ProcessedFeeCostCentresTable.Rows) { CostCentreCode = r[0].ToString(); DataView view = ProcessedFeeDataTable.DefaultView; view.Sort = CostCentreCodeDBName; view.RowFilter = string.Format("{0} = '{1}' AND {2} = '{3}'", AProcessedFeeTable.GetFeeCodeDBName(), CurrentFeeCode, CostCentreCodeDBName, CostCentreCode); //ProcessedFeeDataTable2 = ProcessedFeeDataTable2.Clone(); DataTable ProcessedFeeDataTable2 = view.ToTable(); Int32 FeeCodeRowCount = ProcessedFeeDataTable2.Rows.Count; for (int j = 0; j < FeeCodeRowCount; j++) { DataRow pFR2 = ProcessedFeeDataTable2.Rows[j]; DrFeeTotal = DrFeeTotal + Math.Round(Convert.ToDecimal( pFR2[AProcessedFeeTable.GetPeriodicAmountDBName()]), NumDecPlaces); //pFR2.PeriodicAmount; //ROUND(pFR2.PeriodicAmount, // lv_dp) if (j == (FeeCodeRowCount - 1)) //implies last of the CostCentre rows for this feecode { if (DrFeeTotal != 0) { if (DrFeeTotal < 0) { DrCrIndicator = false; //Credit DrFeeTotal = -DrFeeTotal; } else { DrCrIndicator = true; //Debit //lv_dr_fee_total remains unchanged } /* * Generate the transaction to deduct the fee amount from the source cost centre. (Expense leg) */ //RUN gl1130o.p -> gl1130.i //Create a transaction if (!TGLPosting.CreateATransaction(AdminFeeDS, ALedgerNumber, BatchRow.BatchNumber, GLJournalNumber, "Fee: " + FeeDescription + " (" + CurrentFeeCode + ")", DrAccountCode, CostCentreCode, DrFeeTotal, AccountingPeriodRow.PeriodEndDate, DrCrIndicator, "AG", true, DrFeeTotal, out GLTransactionNumber)) { ErrorContext = Catalog.GetString("Generating the Admin Fee batch"); ErrorMessage = String.Format(Catalog.GetString( "Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."), ALedgerNumber, BatchRow.BatchNumber, GLJournalNumber); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } DrFeeTotal = 0; } } } } } /* Mark each fee entry as processed. */ pFR.ProcessedDate = DateTime.Today.Date; pFR.Timestamp = (DateTime.Today.TimeOfDay.Hours * 3600 + DateTime.Today.TimeOfDay.Minutes * 60 + DateTime.Today.TimeOfDay.Seconds); /* Add the charges on this account to the fee total, * creating an entry if necessary. (This is for the income total) */ GLStewardshipCalculationTDSCreditFeeTotalRow CreditFeeTotalRow = (GLStewardshipCalculationTDSCreditFeeTotalRow) CreditFeeTotalDT.Rows.Find(new object[] { DestCostCentreCode, DestAccountCode }); if (CreditFeeTotalRow != null) { CreditFeeTotalRow.TransactionAmount += Math.Round(pFR.PeriodicAmount, NumDecPlaces); } else { CreditFeeTotalRow = CreditFeeTotalDT.NewRowTyped(); CreditFeeTotalRow.CostCentreCode = DestCostCentreCode; CreditFeeTotalRow.AccountCode = DestAccountCode; CreditFeeTotalRow.TransactionAmount = Math.Round(pFR.PeriodicAmount, NumDecPlaces); CreditFeeTotalDT.Rows.Add(CreditFeeTotalRow); } } /* Generate the transaction to credit the fee amounts to * the destination accounts. (Income leg) */ for (int k = 0; k < CreditFeeTotalDT.Count; k++) { GLStewardshipCalculationTDSCreditFeeTotalRow cFT = (GLStewardshipCalculationTDSCreditFeeTotalRow) CreditFeeTotalDT.Rows[k]; if (cFT.TransactionAmount < 0) { /* The case of a negative gift total should be very rare. * It would only happen if, for instance, the was only * a reversal but no new gifts for a certain ledger. */ DrCrIndicator = true; //Debit TransactionAmount = -cFT.TransactionAmount; } else { DrCrIndicator = false; //Credit TransactionAmount = cFT.TransactionAmount; } /* 0002 - Ok for it to be 0 as just a correction */ if (cFT.TransactionAmount != 0) { GLTransactionNumber = 0; CreatedSuccessfully = TGLPosting.CreateATransaction(AdminFeeDS, ALedgerNumber, BatchRow.BatchNumber, JournalRow.JournalNumber, "Collected admin charges", cFT.AccountCode, cFT.CostCentreCode, TransactionAmount, AccountingPeriodRow.PeriodEndDate, DrCrIndicator, "AG", true, TransactionAmount, out GLTransactionNumber); } } TVerificationResultCollection Verification = null; /* check that something has been posted - we know this if the IsSuccessful flag is still false */ if (!CreatedSuccessfully) { IsSuccessful = true; AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Admin Fee Batch"), String.Format(Catalog.GetString("No admin fees charged in period ({0})."), APeriodNumber), TResultSeverity.Resv_Status)); // An empty GL Batch now exists, which I need to delete. // TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection(); TGLPosting.DeleteGLBatch( ALedgerNumber, BatchRow.BatchNumber, out BatchCancelResult); AVerificationResult.AddCollection(BatchCancelResult); } else { //Post the batch just created GLBatchTDSAccess.SubmitChanges(AdminFeeDS); IsSuccessful = TGLPosting.PostGLBatch(ALedgerNumber, BatchRow.BatchNumber, out Verification); if (IsSuccessful) { AProcessedFeeAccess.SubmitChanges(ProcessedFeeDataTable, ADBTransaction); } } if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verification)) { //Petra error: GL0067 ErrorContext = Catalog.GetString("Posting Admin Fee Batch"); ErrorMessage = String.Format(Catalog.GetString("The posting of the admin fee batch failed.")); ErrorType = TResultSeverity.Resv_Noncritical; throw new System.InvalidOperationException(ErrorMessage); } //End of Transaction block in 4GL /* Print the Admin Fee Calculations report, if requested */ if (APrintReport && IsSuccessful) { //TODO } } } catch (InvalidOperationException ex) { AVerificationResult.Add(new TVerificationResult(ErrorContext, ex.Message, ErrorType)); IsSuccessful = false; } catch (Exception ex) { ErrorContext = Catalog.GetString("Generate Admin Fee Batch"); ErrorMessage = String.Format(Catalog.GetString("Error while generating admin fee batch for Ledger {0}:" + Environment.NewLine + Environment.NewLine + ex.ToString()), ALedgerNumber ); ErrorType = TResultSeverity.Resv_Critical; AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType)); IsSuccessful = false; } return IsSuccessful; }
public static bool PostAPDocuments(Int32 ALedgerNumber, List <Int32>AAPDocumentIds, DateTime APostingDate, Boolean Reversal, out TVerificationResultCollection AVerificationResult) { bool PostingWorkedOk = true; ABatchRow batch; TVerificationResultCollection ResultsCollection = new TVerificationResultCollection(); TDBTransaction HighLevelTransaction = null; Boolean WillCommit = true; Boolean MustBeApproved; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref HighLevelTransaction, ref WillCommit, delegate { AccountsPayableTDS MainDS = LoadDocumentsAndCheck(ALedgerNumber, AAPDocumentIds, APostingDate, Reversal, out MustBeApproved, out ResultsCollection); if (!TVerificationHelper.IsNullOrOnlyNonCritical(ResultsCollection)) { PostingWorkedOk = false; return; // This is returning from the AutoTransaction, not from the whole method. } GLBatchTDS GLDataset = CreateGLBatchAndTransactionsForPosting(ALedgerNumber, APostingDate, Reversal, ref MainDS); batch = GLDataset.ABatch[0]; // save the batch if (TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDataset, out ResultsCollection) != TSubmitChangesResult.scrOK) { PostingWorkedOk = false; } // post the batch if (PostingWorkedOk) { PostingWorkedOk = TGLPosting.PostGLBatch(ALedgerNumber, batch.BatchNumber, out ResultsCollection); } if (!PostingWorkedOk) { TVerificationResultCollection MoreResults; TGLPosting.DeleteGLBatch( ALedgerNumber, GLDataset.ABatch[0].BatchNumber, out MoreResults); ResultsCollection.AddCollection(MoreResults); return; } // GL posting is OK - change status of AP documents and save to database foreach (AApDocumentRow row in MainDS.AApDocument.Rows) { if (Reversal) { row.DocumentStatus = MustBeApproved ? MFinanceConstants.AP_DOCUMENT_APPROVED : MFinanceConstants.AP_DOCUMENT_OPEN; } else { row.DocumentStatus = MFinanceConstants.AP_DOCUMENT_POSTED; } } try { AApDocumentAccess.SubmitChanges(MainDS.AApDocument, HighLevelTransaction); } catch (Exception Exc) { // Now I've got GL entries, but "unposted" AP documents! TLogging.Log("An Exception occured during the Posting of an AP Document:" + Environment.NewLine + Exc.ToString()); ResultsCollection.Add(new TVerificationResult(Catalog.GetString("Post AP Document"), Catalog.GetString("NOTE THAT A GL ENTRY MAY HAVE BEEN CREATED.") + Environment.NewLine + Exc.Message, TResultSeverity.Resv_Critical)); PostingWorkedOk = false; return; } }); AVerificationResult = ResultsCollection; // The System.Action defined in the delegate below cannot directly access // "out" parameters, so this intermediate variable was used. return PostingWorkedOk; }
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; }
private bool SubmitChangesPersonnelData(ref PartnerEditTDS AInspectDS, TDBTransaction ASubmitChangesTransaction, out TVerificationResultCollection AVerificationResult) { TVerificationResultCollection SingleVerificationResultCollection; AVerificationResult = null; // TLogging.LogAtLevel(7, "TPartnerEditUIConnector.SubmitChangesPersonnelData: Instance hash is " + this.GetHashCode().ToString()); bool AllSubmissionsOK = true; if (AInspectDS != null) { AVerificationResult = new TVerificationResultCollection(); #region Individual Data (Personnel Tab) IndividualDataTDS TempDS = new IndividualDataTDS(); TempDS.Merge(AInspectDS); TSubmitChangesResult IndividualDataResult; // can remove tables PPerson, PDataLabelValuePartner and PDataLabelValueApplication here // as this is part of both PartnerEditTDS and IndividualDataTDS and // so the relevant data was already saved when PartnerEditTDS was saved TempDS.RemoveTable(PPersonTable.GetTableName()); TempDS.RemoveTable(PDataLabelValuePartnerTable.GetTableName()); TempDS.RemoveTable(PDataLabelValueApplicationTable.GetTableName()); TempDS.InitVars(); IndividualDataResult = TIndividualDataWebConnector.SubmitChangesServerSide(ref TempDS, ref AInspectDS, ASubmitChangesTransaction, out SingleVerificationResultCollection); if ((IndividualDataResult != TSubmitChangesResult.scrOK) && (IndividualDataResult != TSubmitChangesResult.scrNothingToBeSaved)) { AllSubmissionsOK = false; AVerificationResult.AddCollection(SingleVerificationResultCollection); } #endregion // Note: Locations and PartnerLocations are done sepearately in SubmitChangesAddresses! if (AllSubmissionsOK == false) { // TLogging.LogAtLevel(9, Messages.BuildMessageFromVerificationResult( // "TPartnerEditUIConnector.SubmitChangesPersonnelData AVerificationResult: ", AVerificationResult)); } } else { TLogging.LogAtLevel(8, "TPartnerEditUIConnector.SubmitChangesPersonnelData AInspectDS = null!"); AllSubmissionsOK = false; } return AllSubmissionsOK; }
public static GiftBatchTDS PrepareGiftBatchForPosting(Int32 ALedgerNumber, Int32 ABatchNumber, ref TDBTransaction ATransaction, out TVerificationResultCollection AVerifications) { #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 ChangesToCommit = false; GiftBatchTDS MainDS = LoadAGiftBatchSingle(ALedgerNumber, ABatchNumber, ref ATransaction); string LedgerBaseCurrency = MainDS.ALedger[0].BaseCurrency; string LedgerIntlCurrency = MainDS.ALedger[0].IntlCurrency; AVerifications = new TVerificationResultCollection(); //Check Batch status if (MainDS.AGiftBatch[0].BatchStatus != MFinanceConstants.BATCH_UNPOSTED) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Cannot post batch ({0}, {1}) with status: {2}", ALedgerNumber, ABatchNumber, MainDS.AGiftBatch[0].BatchStatus), TResultSeverity.Resv_Critical)); return null; } //Load all other related data for the batch and commit any changes MainDS.Merge(LoadAGiftBatchAndRelatedData(ALedgerNumber, ABatchNumber, ATransaction, out ChangesToCommit, true)); if (ChangesToCommit) { GiftBatchTDSAccess.SubmitChanges(MainDS); } AGiftBatchRow GiftBatchRow = MainDS.AGiftBatch[0]; string BatchTransactionCurrency = GiftBatchRow.CurrencyCode; // check that the Gift Batch BatchPeriod matches the date effective DateTime GLEffectiveDate = GiftBatchRow.GlEffectiveDate; DateTime StartOfMonth = new DateTime(GLEffectiveDate.Year, GLEffectiveDate.Month, 1); int DateEffectivePeriod, DateEffectiveYear; TFinancialYear.IsValidPostingPeriod(GiftBatchRow.LedgerNumber, GiftBatchRow.GlEffectiveDate, out DateEffectivePeriod, out DateEffectiveYear, ATransaction); decimal IntlToBaseExchRate = TExchangeRateTools.GetCorporateExchangeRate(LedgerBaseCurrency, LedgerIntlCurrency, StartOfMonth, GLEffectiveDate); //Check Batch period if (GiftBatchRow.BatchPeriod != DateEffectivePeriod) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Invalid gift batch period {0} for date {1}", GiftBatchRow.BatchPeriod, GLEffectiveDate), TResultSeverity.Resv_Critical)); return null; } //Check international exchange rate else if (IntlToBaseExchRate == 0) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("No Corporate Exchange rate exists for the month: {0:MMMM yyyy}!"), GLEffectiveDate), TResultSeverity.Resv_Critical)); return null; } //Check Hash total else if ((GiftBatchRow.HashTotal != 0) && (GiftBatchRow.BatchTotal != GiftBatchRow.HashTotal)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("The gift batch total ({0}) does not equal the hash total ({1}).", StringHelper.FormatUsingCurrencyCode(GiftBatchRow.BatchTotal, GiftBatchRow.CurrencyCode), StringHelper.FormatUsingCurrencyCode(GiftBatchRow.HashTotal, GiftBatchRow.CurrencyCode)), TResultSeverity.Resv_Critical)); return null; } //Check validity at the gift detail level foreach (GiftBatchTDSAGiftDetailRow giftDetail in MainDS.AGiftDetail.Rows) { // find motivation detail row AMotivationDetailRow motivationRow = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find(new object[] { ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode }); //do not allow posting gifts with no donor if (giftDetail.DonorKey == 0) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("Donor Key needed in gift {0}"), giftDetail.GiftTransactionNumber), TResultSeverity.Resv_Critical)); return null; } //check for valid motivation detail code else if (motivationRow == null) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Invalid motivation detail {0}/{1} in gift {2}", giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode, giftDetail.GiftTransactionNumber), TResultSeverity.Resv_Critical)); return null; } // data is only updated if the gift amount is positive if (giftDetail.GiftTransactionAmount >= 0) { // The recipient ledger number must not be 0 if the motivation group is 'GIFT' if ((giftDetail.IsRecipientLedgerNumberNull() || (giftDetail.RecipientLedgerNumber == 0)) && (giftDetail.MotivationGroupCode == MFinanceConstants.MOTIVATION_GROUP_GIFT)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("No valid Gift Destination exists for the recipient {0} ({1}) of gift {2}."), giftDetail.RecipientDescription, giftDetail.RecipientKey.ToString("0000000000"), giftDetail.GiftTransactionNumber) + "\n\n" + Catalog.GetString( "A Gift Destination will need to be assigned to this Partner before this gift can be posted with the Motivation Group 'GIFT'."), TResultSeverity.Resv_Critical)); return null; } //Check for missing cost centre code else if (giftDetail.IsCostCentreCodeNull() || (giftDetail.CostCentreCode == string.Empty)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("No valid Cost Centre Code exists for the recipient {0} ({1}) of gift {2}."), giftDetail.RecipientDescription, giftDetail.RecipientKey.ToString("0000000000"), giftDetail.GiftTransactionNumber) + "\n\n" + Catalog.GetString( "A Gift Destination will need to be assigned to this Partner."), TResultSeverity.Resv_Critical)); return null; } } // set column giftdetail.AccountCode motivation giftDetail.AccountCode = motivationRow.AccountCode; // validate exchange rate to base if (GiftBatchRow.ExchangeRateToBase == 0) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("Exchange rate to base currency is 0 in Batch {0}!"), ABatchNumber), TResultSeverity.Resv_Critical)); return null; } else if ((GiftBatchRow.CurrencyCode != LedgerBaseCurrency) && !IsDailyExchangeRateIsStillValid(GiftBatchRow.CurrencyCode, LedgerBaseCurrency, GiftBatchRow.GlEffectiveDate, GiftBatchRow.ExchangeRateToBase, ATransaction)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("Exchange rate to base currency is invalid in Batch {0}!"), ABatchNumber), TResultSeverity.Resv_Critical)); return null; } //Calculate GiftAmount giftDetail.GiftAmount = giftDetail.GiftTransactionAmount / GiftBatchRow.ExchangeRateToBase; if (BatchTransactionCurrency != LedgerIntlCurrency) { giftDetail.GiftAmountIntl = giftDetail.GiftAmount / IntlToBaseExchRate; } else { giftDetail.GiftAmountIntl = giftDetail.GiftTransactionAmount; } // for calculation of admin fees LoadAdminFeeTablesForGiftDetail(MainDS, giftDetail, ATransaction); // get all motivation detail fees for this gift foreach (AMotivationDetailFeeRow motivationFeeRow in MainDS.AMotivationDetailFee.Rows) { // If the charge flag is not set, still process fees for GIF and ICT but do not process other fees. if (giftDetail.ChargeFlag || (motivationFeeRow.FeeCode == MFinanceConstants.ADMIN_FEE_GIF) || (motivationFeeRow.FeeCode == MFinanceConstants.ADMIN_FEE_ICT)) { TVerificationResultCollection Verifications2; decimal FeeAmount = CalculateAdminFee(MainDS, ALedgerNumber, motivationFeeRow.FeeCode, giftDetail.GiftAmount, out Verifications2); if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verifications2)) { AVerifications.AddCollection(Verifications2); return null; } if (FeeAmount != 0) { AddToFeeTotals(MainDS, giftDetail, motivationFeeRow.FeeCode, FeeAmount, GiftBatchRow.BatchPeriod); } } } } return MainDS; }
/// <summary> /// Main Entry point. The parameters are the same as in /// Ict.Petra.Server.MFinance.GL.WebConnectors.TPeriodMonthEnd /// </summary> /// <param name="AInfoMode"></param> /// <param name="AVRCollection"></param> /// <returns>false if it went OK</returns> public bool RunMonthEnd(bool AInfoMode, out TVerificationResultCollection AVRCollection) { FInfoMode = AInfoMode; FverificationResults = new TVerificationResultCollection(); AVRCollection = FverificationResults; if (FledgerInfo.ProvisionalYearEndFlag) { // we want to run a month end, but the provisional year end flag has been set TVerificationResult tvt = new TVerificationResult(Catalog.GetString("Year End is required!"), Catalog.GetString("In this situation you cannot run a month end routine"), "", TPeriodEndErrorAndStatusCodes.PEEC_03.ToString(), TResultSeverity.Resv_Critical); FverificationResults.Add(tvt); FHasCriticalErrors = true; return true; } if (AInfoMode) { AAccountingPeriodTable PeriodTbl = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadUncommitted, ref Transaction, delegate { PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(FledgerInfo.LedgerNumber, FledgerInfo.CurrentPeriod, Transaction); }); if (PeriodTbl.Rows.Count > 0) { FverificationResults.Add( new TVerificationResult( Catalog.GetString("Month End"), String.Format(Catalog.GetString("Current period is {0} - {1}"), PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()), TResultSeverity.Resv_Status)); } } RunPeriodEndCheck(new RunMonthEndChecks(FledgerInfo), FverificationResults); if (!AInfoMode) { TVerificationResultCollection IchVerificationReults; if (!StewardshipCalculationDelegate(FledgerInfo.LedgerNumber, FledgerInfo.CurrentPeriod, out IchVerificationReults)) { FHasCriticalErrors = true; } // Merge VerificationResults: FverificationResults.AddCollection(IchVerificationReults); } // RunPeriodEndSequence(new RunMonthlyAdminFees(), "Example"); if (!FInfoMode) { if (!FHasCriticalErrors) { SetNextPeriod(); // refresh cached ledger table, so that the client will know the current period TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing( TCacheableFinanceTablesEnum.LedgerDetails.ToString()); } } // // The 4GL code throws out these reports: // // Admin fee calculations report. // ICH stewardship report. // "Trial Balance" with account details. // HOSA for each foreign cost centre (ledger/fund). // Income Statement/Profit & Loss // Current Accounts Payable if interfaced. M025 // AFO report. // Executive Summary Report. // return FHasCriticalErrors; }
public static bool PostAPPayments( ref AccountsPayableTDS AMainDS, DateTime APostingDate, out TVerificationResultCollection AVerificationResult) { AccountsPayableTDS MainDS = AMainDS; TVerificationResultCollection VerificationResult = new TVerificationResultCollection(); AVerificationResult = VerificationResult; if ((MainDS.AApPayment.Rows.Count < 1) || (MainDS.AApDocumentPayment.Rows.Count < 1)) { AVerificationResult = new TVerificationResultCollection(); AVerificationResult.Add(new TVerificationResult("Post Payment", String.Format("Nothing to do - Payments has {0} rows, Documents has {1} rows.", MainDS.AApPayment.Rows.Count, MainDS.AApDocumentPayment.Rows.Count), TResultSeverity.Resv_Noncritical)); return false; } TDBTransaction transaction = null; Boolean SubmissionOK = false; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref transaction, ref SubmissionOK, delegate { foreach (AccountsPayableTDSAApDocumentPaymentRow row in MainDS.AApDocumentPayment.Rows) { AccountsPayableTDSAApDocumentRow documentRow = (AccountsPayableTDSAApDocumentRow)MainDS.AApDocument.Rows.Find(row.ApDocumentId); if (documentRow != null) { MainDS.AApDocument.Rows.Remove(documentRow); } documentRow = (AccountsPayableTDSAApDocumentRow) AApDocumentAccess.LoadByPrimaryKey(MainDS, row.ApDocumentId, transaction); SetOutstandingAmount(documentRow, documentRow.LedgerNumber, MainDS.AApDocumentPayment); // // If the amount paid is negative, this is a refund.. if (row.Amount < 0) { if (row.Amount <= documentRow.OutstandingAmount) { documentRow.DocumentStatus = MFinanceConstants.AP_DOCUMENT_PAID; } else { documentRow.DocumentStatus = MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID; } } else { if ((row.Amount >= documentRow.OutstandingAmount) || (documentRow.OutstandingAmount == 0.0m)) { documentRow.DocumentStatus = MFinanceConstants.AP_DOCUMENT_PAID; } else { documentRow.DocumentStatus = MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID; } } } // Get max payment number for this ledger // PROBLEM: what if two payments are happening at the same time? do we need locking? // see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=50 object maxPaymentCanBeNull = DBAccess.GDBAccessObj.ExecuteScalar( "SELECT MAX(PUB_a_ap_payment.a_payment_number_i) FROM PUB_a_ap_payment WHERE PUB_a_ap_payment.a_ledger_number_i = " + MainDS.AApPayment[0].LedgerNumber.ToString(), transaction); Int32 maxPaymentNumberInLedger = (maxPaymentCanBeNull == System.DBNull.Value ? 0 : Convert.ToInt32(maxPaymentCanBeNull)); foreach (AccountsPayableTDSAApPaymentRow paymentRow in MainDS.AApPayment.Rows) { paymentRow.PaymentDate = APostingDate; paymentRow.Amount = 0.0M; Int32 NewPaymentNumber = maxPaymentNumberInLedger + (-1 * paymentRow.PaymentNumber); foreach (AccountsPayableTDSAApDocumentPaymentRow docPaymentRow in MainDS.AApDocumentPayment.Rows) { if (docPaymentRow.PaymentNumber == paymentRow.PaymentNumber) { paymentRow.Amount += docPaymentRow.Amount; docPaymentRow.PaymentNumber = NewPaymentNumber; } } paymentRow.PaymentNumber = NewPaymentNumber; } // create GL batch GLBatchTDS GLDataset = CreateGLBatchAndTransactionsForPaying(MainDS.AApPayment[0].LedgerNumber, APostingDate, ref MainDS); ABatchRow batch = GLDataset.ABatch[0]; // save the batch Boolean PostingWorkedOk = (TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDataset, out VerificationResult) == TSubmitChangesResult.scrOK); if (PostingWorkedOk) { // post the batch PostingWorkedOk = TGLPosting.PostGLBatch(MainDS.AApPayment[0].LedgerNumber, batch.BatchNumber, out VerificationResult); } if (!PostingWorkedOk) { TVerificationResultCollection MoreResults; TGLPosting.DeleteGLBatch( MainDS.AApPayment[0].LedgerNumber, batch.BatchNumber, out MoreResults); VerificationResult.AddCollection(MoreResults); return; // return from delegate } // store ApPayment and ApDocumentPayment to database AApPaymentAccess.SubmitChanges(MainDS.AApPayment, transaction); AApDocumentPaymentAccess.SubmitChanges(MainDS.AApDocumentPayment, transaction); // save changed status of AP documents to database AApDocumentAccess.SubmitChanges(MainDS.AApDocument, transaction); SubmissionOK = true; }); // Get NewOrExisting Auto Transaction return SubmissionOK; } // Post AP Payments
private static GiftBatchTDS PrepareGiftBatchForPosting(Int32 ALedgerNumber, Int32 ABatchNumber, ref TDBTransaction ATransaction, out TVerificationResultCollection AVerifications) { GiftBatchTDS MainDS = LoadAGiftBatchSingle(ALedgerNumber, ABatchNumber, ref ATransaction); string LedgerBaseCurrency = MainDS.ALedger[0].BaseCurrency; string LedgerIntlCurrency = MainDS.ALedger[0].IntlCurrency; AVerifications = new TVerificationResultCollection(); if ((MainDS.AGiftBatch == null) || (MainDS.AGiftBatch.Rows.Count < 1)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Unable to Load GiftBatchData ({0}, {1})", ALedgerNumber, ABatchNumber), TResultSeverity.Resv_Critical)); return null; } else if (MainDS.AGiftBatch[0].BatchStatus != MFinanceConstants.BATCH_UNPOSTED) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Cannot post batch ({0}, {1}) with status: {2}", ALedgerNumber, ABatchNumber, MainDS.AGiftBatch[0].BatchStatus), TResultSeverity.Resv_Critical)); return null; } //Load all other related data for the batch and commit any changes bool ChangesToCommit = false; MainDS.Merge(LoadAGiftBatchAndRelatedData(ALedgerNumber, ABatchNumber, ATransaction, out ChangesToCommit, true)); if (ChangesToCommit) { GiftBatchTDSAccess.SubmitChanges(MainDS); } AGiftBatchRow GiftBatchRow = MainDS.AGiftBatch[0]; string BatchTransactionCurrency = GiftBatchRow.CurrencyCode; // for calculation of admin fees LoadAdminFeeTablesForGiftBatch(MainDS, ALedgerNumber, ABatchNumber, ATransaction); // check that the Gift Batch BatchPeriod matches the date effective DateTime GLEffectiveDate = GiftBatchRow.GlEffectiveDate; DateTime StartOfMonth = new DateTime(GLEffectiveDate.Year, GLEffectiveDate.Month, 1); int DateEffectivePeriod, DateEffectiveYear; TFinancialYear.IsValidPostingPeriod(GiftBatchRow.LedgerNumber, GiftBatchRow.GlEffectiveDate, out DateEffectivePeriod, out DateEffectiveYear, ATransaction); decimal IntlToBaseExchRate = TExchangeRateTools.GetCorporateExchangeRate(LedgerBaseCurrency, LedgerIntlCurrency, StartOfMonth, GLEffectiveDate); if (GiftBatchRow.BatchPeriod != DateEffectivePeriod) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Invalid gift batch period {0} for date {1}", GiftBatchRow.BatchPeriod, GLEffectiveDate), TResultSeverity.Resv_Critical)); return null; } else if (IntlToBaseExchRate == 0) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("No Corporate Exchange rate exists for the month: {0:MMMM yyyy}!"), GLEffectiveDate), TResultSeverity.Resv_Critical)); return null; } else if ((GiftBatchRow.HashTotal != 0) && (GiftBatchRow.BatchTotal != GiftBatchRow.HashTotal)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("The gift batch total ({0}) does not equal the hash total ({1}).", StringHelper.FormatUsingCurrencyCode(GiftBatchRow.BatchTotal, GiftBatchRow.CurrencyCode), StringHelper.FormatUsingCurrencyCode(GiftBatchRow.HashTotal, GiftBatchRow.CurrencyCode)), TResultSeverity.Resv_Critical)); return null; } //Check validity at the gift detail level foreach (GiftBatchTDSAGiftDetailRow giftDetail in MainDS.AGiftDetail.Rows) { // do not allow posting gifts with no donor if (giftDetail.DonorKey == 0) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("Donor Key needed in gift {0}"), giftDetail.GiftTransactionNumber), TResultSeverity.Resv_Critical)); return null; } // find motivation detail AMotivationDetailRow motivationRow = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find(new object[] { ALedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode }); if (motivationRow == null) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format("Invalid motivation detail {0}/{1} in gift {2}", giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode, giftDetail.GiftTransactionNumber), TResultSeverity.Resv_Critical)); return null; } // data is only updated if the gift amount is positive if (giftDetail.GiftTransactionAmount >= 0) { // The recipient ledger number must not be 0 if the motivation group is 'GIFT' if ((giftDetail.IsRecipientLedgerNumberNull() || (giftDetail.RecipientLedgerNumber == 0)) && (giftDetail.MotivationGroupCode == MFinanceConstants.MOTIVATION_GROUP_GIFT)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("No valid Gift Destination exists for the recipient {0} ({1}) of gift {2}."), giftDetail.RecipientDescription, giftDetail.RecipientKey, giftDetail.GiftTransactionNumber) + "\n\n" + Catalog.GetString( "A Gift Destination will need to be assigned to this Partner before this gift can be posted with the Motivation Group 'GIFT'."), TResultSeverity.Resv_Critical)); return null; } if (giftDetail.IsCostCentreCodeNull() || (giftDetail.CostCentreCode == string.Empty)) { AVerifications.Add( new TVerificationResult( "Posting Gift Batch", String.Format(Catalog.GetString("No valid Cost Centre Code exists for the recipient {0} ({1}) of gift {2}."), giftDetail.RecipientDescription, giftDetail.RecipientKey, giftDetail.GiftTransactionNumber) + "\n\n" + Catalog.GetString( "A Gift Destination will need to be assigned to this Partner."), TResultSeverity.Resv_Critical)); return null; } } // set column giftdetail.AccountCode motivation giftDetail.AccountCode = motivationRow.AccountCode; giftDetail.GiftAmount = giftDetail.GiftTransactionAmount / GiftBatchRow.ExchangeRateToBase; if (BatchTransactionCurrency != LedgerIntlCurrency) { giftDetail.GiftAmountIntl = giftDetail.GiftAmount / IntlToBaseExchRate; } else { giftDetail.GiftAmountIntl = giftDetail.GiftTransactionAmount; } // get all motivation detail fees for this gift foreach (AMotivationDetailFeeRow motivationFeeRow in MainDS.AMotivationDetailFee.Rows) { if ((motivationFeeRow.MotivationDetailCode == motivationRow.MotivationDetailCode) && (motivationFeeRow.MotivationGroupCode == motivationRow.MotivationGroupCode)) { TVerificationResultCollection Verifications2; decimal FeeAmount = CalculateAdminFee(MainDS, ALedgerNumber, motivationFeeRow.FeeCode, giftDetail.GiftAmount, out Verifications2); if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verifications2)) { AVerifications.AddCollection(Verifications2); return null; } AddToFeeTotals(MainDS, giftDetail, motivationFeeRow.FeeCode, FeeAmount, GiftBatchRow.BatchPeriod); } } } return MainDS; }