private DocLine[] LoadLines(MProfitLoss pay) { List <DocLine> list = new List <DocLine>(); MProfitLossLines[] lines = pay.GetLines(false); //C_AcctSchema = Util.GetValueOfInt(DB.ExecuteScalar("SELECT c_acctschema1_id FROM AD_ClientInfo WHERE AD_Client_ID=" + GetAD_Client_ID())); for (int i = 0; i < lines.Length; i++) { MProfitLossLines line = lines[i]; DocLine docLine = new DocLine(line, this); docLine.SetAmount(line.GetC_ProfitAndLoss_ID() != 0 ? line.GetAccountDebit() : Math.Abs(line.GetAccountDebit()), line.GetC_ProfitAndLoss_ID() != 0 ? line.GetAccountCredit():Math.Abs(line.GetAccountCredit())); //docLine.SetConvertedAmt(line.GetC_AcctSchema_ID(), line.GetAccountDebit(), line.GetAccountCredit()); // set primary key value docLine.SetPrimaryKeyValue(line.GetC_ProfitLossLines_ID()); // set GL journal line table ID docLine.SetLineTable_ID(line.Get_Table_ID()); // list.Add(docLine); } // Return Array DocLine[] dls = new DocLine[list.Count]; dls = list.ToArray(); return(dls); }
/// <summary> /// Is used to create line against Income Account summary account -- /// </summary> /// <param name="Profit">class object of MProfitLoss </param> private void InsertProfitLossLine(MProfitLoss Profit) { // get consolidated profit amount agsint Organization String Sql = @"SELECT AD_Org_ID , (SUM(AccountDebit) - SUM(AccountCredit)) AS ProfitAmt FROM C_ProfitLossLines WHERE C_ProfitAndLoss_ID > 0 AND C_ProfitLoss_ID = " + Profit.GetC_ProfitLoss_ID() + @" GROUP BY AD_Org_ID"; DataSet dsProfit = DB.ExecuteDataset(Sql, null, Get_Trx()); if (dsProfit != null && dsProfit.Tables.Count > 0 && dsProfit.Tables[0].Rows.Count > 0) { // get max line no int lineNo = Convert.ToInt32(DB.ExecuteScalar(@"SELECT NVL(MAX(Line),0)+10 AS line FROM C_ProfitLossLines WHERE C_ProfitLoss_ID = " + Profit.GetC_ProfitLoss_ID(), null, Get_Trx())); // get Valid Combination against Income Summary Acct from accounting schema int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + Util.GetValueOfInt(Profit.Get_Value("C_AcctSchema_ID")))); // get account id MAccount acct = MAccount.Get(GetCtx(), validComID); for (int i = 0; i < dsProfit.Tables[0].Rows.Count; i++) { Decimal profitAmt = Util.GetValueOfDecimal(dsProfit.Tables[0].Rows[i]["ProfitAmt"]); // when difference of DR - CR is ZERO then not to create line if (profitAmt == 0) { continue; } MProfitLossLines profitLossLines = new MProfitLossLines(GetCtx(), 0, Get_Trx()); profitLossLines.SetAD_Client_ID(Profit.GetAD_Client_ID()); profitLossLines.SetAD_Org_ID(Util.GetValueOfInt(dsProfit.Tables[0].Rows[i]["AD_Org_ID"])); profitLossLines.SetC_ProfitLoss_ID(Profit.GetC_ProfitLoss_ID()); profitLossLines.SetLine(lineNo); profitLossLines.SetC_AcctSchema_ID(Util.GetValueOfInt(Profit.Get_Value("C_AcctSchema_ID"))); profitLossLines.SetPostingType(Util.GetValueOfString(Profit.Get_Value("PostingType"))); profitLossLines.SetAccount_ID(acct.GetAccount_ID()); profitLossLines.Set_Value("LedgerCode", acct.GetAccount().Get_Value("Value")); profitLossLines.Set_Value("LedgerName", acct.GetAccount().Get_Value("Name")); if (profitAmt > 0) { profitLossLines.SetAccountCredit(profitAmt); } else { profitLossLines.SetAccountDebit(profitAmt); } if (!profitLossLines.Save(Get_Trx())) { ValueNamePair pp = VLogger.RetrieveError(); log.Fine("Failed - Profit loss line not saved for income acct summary - " + (pp != null && !String.IsNullOrEmpty(pp.GetName()) ? pp.GetName() : "")); } else { lineNo += 10; } } } }
/// <summary> /// Create Facts (the accounting logic) for /// CMC. /// <pre> /// Expense /// CashExpense DR /// CashAsset CR ///Receipt /// CashAsset DR /// CashReceipt CR /// Charge /// Charge DR /// CashAsset CR /// Difference /// CashDifference DR /// CashAsset CR /// Invoice /// CashAsset DR /// CashTransfer CR /// Transfer /// BankInTransit DR /// CashAsset CR /// </pre> /// </summary> /// <param name="?"></param> /// <returns>Fact</returns> public override List <Fact> CreateFacts(MAcctSchema as1) { // create Fact Header List <Fact> facts = new List <Fact>(); if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_PROFITLOSS)) { // Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross); SetC_Currency_ID(GetCurrency(as1.GetC_AcctSchema_ID())); // Commitment Fact fact = new Fact(this, as1, Fact.POST_Actual); Decimal total = Env.ZERO, totalCredit = Env.ZERO, totalDebit = Env.ZERO; Decimal credit = Env.ZERO, debit = Env.ZERO; for (int i = 0; i < _lines.Length; i++) { DocLine dline = _lines[i]; MProfitLossLines line = new MProfitLossLines(GetCtx(), dline.Get_ID(), null); credit = Util.GetValueOfDecimal(dline.GetAmtAcctCr()); debit = Util.GetValueOfDecimal(dline.GetAmtAcctDr()); if (credit > 0) { totalCredit = Decimal.Add(totalCredit, credit); } if (debit > 0) { totalDebit = Decimal.Add(totalDebit, debit); } // Account MAccount expense = MAccount.Get(GetCtx(), GetAD_Client_ID(), GetAD_Org_ID(), line.GetC_AcctSchema_ID(), line.GetAccount_ID(), line.GetC_SubAcct_ID(), line.GetM_Product_ID(), line.GetC_BPartner_ID(), line.GetAD_OrgTrx_ID(), line.GetC_LocFrom_ID(), line.GetC_LocTo_ID(), line.GetC_SalesRegion_ID(), line.GetC_Project_ID(), line.GetC_Campaign_ID(), line.GetC_Activity_ID(), line.GetUser1_ID(), line.GetUser2_ID(), line.GetUserElement1_ID(), line.GetUserElement2_ID()); fact.CreateLine(dline, expense, GetCurrency(line.GetC_AcctSchema_ID()), debit, credit); } total = totalCredit - totalDebit; if (total != Env.ZERO) { int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID())); MAccount acct = MAccount.Get(GetCtx(), validComID); fact.CreateLine(null, acct, GetC_Currency_ID(), total); } //if (TotalCurrLoss != Env.ZERO) //{ // int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )")); // MAccount acct = MAccount.Get(GetCtx(), validComID); // TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); // fact.CreateLine(null, acct, // GetC_Currency_ID(), (TotalCurrLoss)); //} facts.Add(fact); } return(facts); }
/// <summary> /// Create Facts (the accounting logic) for /// CMC. /// <pre> /// Expense /// CashExpense DR /// CashAsset CR ///Receipt /// CashAsset DR /// CashReceipt CR /// Charge /// Charge DR /// CashAsset CR /// Difference /// CashDifference DR /// CashAsset CR /// Invoice /// CashAsset DR /// CashTransfer CR /// Transfer /// BankInTransit DR /// CashAsset CR /// </pre> /// </summary> /// <param name="?"></param> /// <returns>Fact</returns> public override List <Fact> CreateFacts(MAcctSchema as1) { // create Fact Header List <Fact> facts = new List <Fact>(); if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_PROFITLOSS)) { //Change By mohit // Get Assigned Accounting Schemas based on organization MProfitLossLines PLossline = new MProfitLossLines(GetCtx(), _lines[0].Get_ID(), null); MProfitLoss PLoss = new MProfitLoss(GetCtx(), PLossline.GetC_ProfitLoss_ID(), null); MAcctSchema HeaderAcctSchema = new MAcctSchema(GetCtx(), Util.GetValueOfInt(PLoss.Get_Value("C_AcctSchema_ID")), null); List <int> _ListAcctSch = new List <int>(); // Profit & Loss account shall be posted only in accounting schema selected on header (By Ashish - discussed with Mukesh sir) //_ListAcctSch = GetAcctSchemas(PLoss.GetAD_Org_ID()); _ListAcctSch.Add(HeaderAcctSchema.GetC_AcctSchema_ID()); if (_ListAcctSch.Count > 0) { int CurrencyType_ID = GetDefaultConversionType(GetAD_Client_ID(), GetAD_Org_ID()); for (int asch = 0; asch < _ListAcctSch.Count; asch++) { MAcctSchema AccountingSchema = new MAcctSchema(GetCtx(), _ListAcctSch[asch], null); // Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross); SetC_Currency_ID(GetCurrency(AccountingSchema.GetC_AcctSchema_ID())); // Commitment Fact fact = new Fact(this, AccountingSchema, Fact.POST_Actual); Decimal total = Env.ZERO, totalCredit = Env.ZERO, totalDebit = Env.ZERO; Decimal credit = Env.ZERO, debit = Env.ZERO; for (int i = 0; i < _lines.Length; i++) { DocLine dline = _lines[i]; MProfitLossLines line = new MProfitLossLines(GetCtx(), dline.Get_ID(), null); if (Util.GetValueOfInt(HeaderAcctSchema.GetC_Currency_ID()) == Util.GetValueOfInt(AccountingSchema.GetC_Currency_ID())) { credit = Util.GetValueOfDecimal(dline.GetAmtSourceCr()); debit = Util.GetValueOfDecimal(dline.GetAmtSourceDr()); } else { credit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceCr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(), PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID()); debit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceDr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(), PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID()); Util.GetValueOfDecimal(dline.GetAmtAcctDr()); } if (credit > 0) { totalCredit = Decimal.Add(totalCredit, credit); } if (debit > 0) { totalDebit = Decimal.Add(totalDebit, debit); } // Account MAccount expense = MAccount.Get(GetCtx(), GetAD_Client_ID(), GetAD_Org_ID(), AccountingSchema.GetC_AcctSchema_ID(), line.GetAccount_ID(), line.GetC_SubAcct_ID(), line.GetM_Product_ID(), line.GetC_BPartner_ID(), line.GetAD_OrgTrx_ID(), line.GetC_LocFrom_ID(), line.GetC_LocTo_ID(), line.GetC_SalesRegion_ID(), line.GetC_Project_ID(), line.GetC_Campaign_ID(), line.GetC_Activity_ID(), line.GetUser1_ID(), line.GetUser2_ID(), line.GetUserElement1_ID(), line.GetUserElement2_ID(), line.GetUserElement3_ID(), line.GetUserElement4_ID(), line.GetUserElement5_ID(), line.GetUserElement6_ID(), line.GetUserElement7_ID(), line.GetUserElement8_ID(), line.GetUserElement9_ID()); fact.CreateLine(dline, expense, GetCurrency(AccountingSchema.GetC_AcctSchema_ID()), debit, credit); } total = totalCredit - totalDebit; if (total != Env.ZERO) { int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + AccountingSchema.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID())); MAccount acct = MAccount.Get(GetCtx(), validComID); fact.CreateLine(null, acct, GetC_Currency_ID(), total); } //if (TotalCurrLoss != Env.ZERO) //{ // int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )")); // MAccount acct = MAccount.Get(GetCtx(), validComID); // TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); // fact.CreateLine(null, acct, // GetC_Currency_ID(), (TotalCurrLoss)); //} facts.Add(fact); } } } return(facts); }
protected override string DoIt() { DB.ExecuteQuery("DELETE FROM C_ProfitLossLines WHERE C_ProfitLoss_ID=" + GetRecord_ID()); prof = new MProfitLoss(GetCtx(), GetRecord_ID(), Get_TrxName()); stDate = Util.GetValueOfDateTime(DB.ExecuteScalar("select p.startdate from c_period p inner join c_year y on p.c_year_id=y.c_year_id where p.periodno='1' and p.c_year_id= " + prof.GetC_Year_ID() + " and y.ad_client_id= " + GetCtx().GetAD_Client_ID(), null, null)); eDate = Util.GetValueOfDateTime(DB.ExecuteScalar("select p.enddate from c_period p inner join c_year y on p.c_year_id=y.c_year_id where p.periodno='12' and p.c_year_id= " + prof.GetC_Year_ID() + " and y.ad_client_id= " + GetCtx().GetAD_Client_ID(), null, null)); qry = @"select ft.C_AcctSchema_ID,ft.PostingType,ft.AmtAcctDr,ft.AmtAcctCr,ft.Account_ID,ft.C_SubAcct_ID,ft.C_BPartner_ID,ft.M_Product_ID,ft.C_Project_ID,ft.C_SalesRegion_ID,ft.C_Campaign_ID,ft.AD_OrgTrx_ID,ft.C_LocFrom_ID,ft.C_LocTo_ID,ft.C_Activity_ID,ft.User1_ID,ft.User2_ID,ft.UserElement1_ID,ft.UserElement2_ID,ft.GL_Budget_ID,ft.C_ProjectPhase_ID,ft.C_ProjectTask_ID," + "ev.Value as LedgerCode,ev.Name as LedgerName from fact_acct_balance ft inner join c_elementvalue ev on ft.account_id=ev.c_elementvalue_id where ft.ad_client_id=" + GetAD_Client_ID() + " and ft.DateAcct >=" + GlobalVariable.TO_DATE(stDate, true) + " AND ft.DateAcct <= " + GlobalVariable.TO_DATE(eDate, true) + " AND (ev.accounttype='E' OR ev.accounttype='R') and ev.isintermediatecode='N'"; ds = DB.ExecuteDataset(qry, null, Get_TrxName()); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { pLine = new MProfitLossLines(GetCtx(), 0, Get_TrxName()); pLine.SetAD_Client_ID(GetAD_Client_ID()); pLine.SetAD_Org_ID(GetCtx().GetAD_Org_ID()); pLine.SetC_ProfitLoss_ID(GetRecord_ID()); pLine.SetC_ProfitAndLoss_ID(prof.GetC_ProfitAndLoss_ID()); pLine.SetC_AcctSchema_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_AcctSchema_ID"])); pLine.SetPostingType(Util.GetValueOfString(ds.Tables[0].Rows[i]["PostingType"])); pLine.SetLedgerCode(Util.GetValueOfString(ds.Tables[0].Rows[i]["LedgerCode"])); pLine.SetLedgerName(Util.GetValueOfString(ds.Tables[0].Rows[i]["LedgerName"])); if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["AmtAcctDr"]) > 0) { pLine.SetAccountCredit(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["AmtAcctDr"])); } if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["AmtAcctCr"]) > 0) { pLine.SetAccountDebit(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["AmtAcctCr"])); } pLine.SetC_SubAcct_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_SubAcct_ID"])); pLine.SetAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Account_ID"])); pLine.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"])); pLine.SetM_Product_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["M_Product_ID"])); pLine.SetC_Project_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Project_ID"])); pLine.SetC_SalesRegion_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_SalesRegion_ID"])); pLine.SetC_Campaign_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Campaign_ID"])); pLine.SetAD_OrgTrx_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_OrgTrx_ID"])); pLine.SetC_LocFrom_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_LocFrom_ID"])); pLine.SetC_LocTo_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_LocTo_ID"])); pLine.SetC_Activity_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Activity_ID"])); pLine.SetUser1_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["User1_ID"])); pLine.SetUser2_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["User2_ID"])); pLine.SetUserElement1_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["UserElement1_ID"])); pLine.SetUserElement2_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["UserElement2_ID"])); pLine.SetGL_Budget_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["GL_Budget_ID"])); pLine.SetC_ProjectPhase_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ProjectPhase_ID"])); pLine.SetC_ProjectTask_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ProjectTask_ID"])); int lineno = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(Line),0)+10 FROM C_ProfitLossLines WHERE C_ProfitLoss_ID=" + GetRecord_ID(), null, Get_TrxName())); pLine.SetLine(lineno); if (!pLine.Save()) { ds.Dispose(); return(Msg.GetMsg(GetCtx(), "ProfitLinesNotSaved")); } } expense = Util.GetValueOfDecimal(DB.ExecuteScalar("select SUM(ft.AmtAcctCr+ft.AmtAcctDr) from fact_acct_balance ft inner join c_elementvalue ev on ft.account_id=ev.c_elementvalue_id where ft.ad_client_id=" + GetAD_Client_ID() + " and ft.DateAcct >=" + GlobalVariable.TO_DATE(stDate, true) + " AND ft.DateAcct <= " + GlobalVariable.TO_DATE(eDate, true) + " AND ev.accounttype='E' and ev.isintermediatecode='N'")); revenue = Util.GetValueOfDecimal(DB.ExecuteScalar("select SUM(ft.AmtAcctCr+ft.AmtAcctDr) from fact_acct_balance ft inner join c_elementvalue ev on ft.account_id=ev.c_elementvalue_id where ft.ad_client_id=" + GetAD_Client_ID() + " and ft.DateAcct >=" + GlobalVariable.TO_DATE(stDate, true) + " AND ft.DateAcct <= " + GlobalVariable.TO_DATE(eDate, true) + " AND ev.accounttype='R' and ev.isintermediatecode='N'")); prof.SetProfitBeforeTax(revenue - expense); if (!prof.Save()) { ds.Dispose(); Rollback(); return(Msg.GetMsg(GetCtx(), "ProfitNotSaved")); } ds.Dispose(); return(Msg.GetMsg(GetCtx(), "LinesGenerated")); } } return(Msg.GetMsg(GetCtx(), "RecordNoFound")); }