} // setC_Currency_ID /// <summary> /// Set Rate /// </summary> private void SetRate() { // Source info int C_Currency_ID = GetC_Currency_ID(); int C_ConversionType_ID = GetC_ConversionType_ID(); if (C_Currency_ID == 0 || C_ConversionType_ID == 0) { return; } DateTime?DateAcct = GetDateAcct(); if (DateAcct == null) { DateAcct = DateTime.Now;// new Timestamp(System.currentTimeMillis()); } // int C_AcctSchema_ID = GetC_AcctSchema_ID(); MAcctSchema a = MAcctSchema.Get(GetCtx(), C_AcctSchema_ID); int AD_Client_ID = GetAD_Client_ID(); int AD_Org_ID = GetAD_Org_ID(); Decimal?CurrencyRate = (Decimal?)MConversionRate.GetRate(C_Currency_ID, a.GetC_Currency_ID(), DateAcct, C_ConversionType_ID, AD_Client_ID, AD_Org_ID); log.Fine("rate = " + CurrencyRate); //if (CurrencyRate.Value == null) //{ // CurrencyRate = Env.ZERO; //} SetCurrencyRate(CurrencyRate.Value); } // setRate
/// <summary> /// GetAcctSchema Detail /// </summary> /// <param name="ctx"></param> /// <param name="fields"></param> /// <returns></returns> public Dictionary <String, String> GetAcctSchema(Ctx ctx, string fields) { string[] paramValue = fields.Split(','); int C_AcctSchema_ID; Dictionary <String, String> retDic = new Dictionary <string, string>(); //Assign parameter value C_AcctSchema_ID = Util.GetValueOfInt(paramValue[0].ToString()); //End Assign parameter value MAcctSchema aas = MAcctSchema.Get(ctx, C_AcctSchema_ID); retDic["StdPrecision"] = aas.GetStdPrecision().ToString(); retDic["C_Currency_ID"] = aas.GetC_Currency_ID().ToString(); return(retDic); }
/// <summary> /// Process /// </summary> /// <returns>message</returns> protected override String DoIt() { log.Info("C_AcctSchema_ID=" + _C_AcctSchema_ID); if (_C_AcctSchema_ID == 0) { throw new Exception("C_AcctSchema_ID=0"); } MAcctSchema as1 = MAcctSchema.Get(GetCtx(), _C_AcctSchema_ID); if (as1.Get_ID() == 0) { throw new Exception("Not Found - C_AcctSchema_ID=" + _C_AcctSchema_ID); } // Update String sql = "UPDATE M_Product_Acct pa " + "SET (P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct," + " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct," + " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct " // Added **************** Lokesh Chauhan **************** + " ,P_Resource_Absorption_Acct , P_MaterialOverhd_Acct " // Added **************** + " )=" + " (SELECT P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct," + " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct," + " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct" // Added **************** Lokesh Chauhan **************** + " ,P_Resource_Absorption_Acct , P_MaterialOverhd_Acct " // Added **************** + " FROM M_Product_Category_Acct pca" + " WHERE pca.M_Product_Category_ID=" + _M_Product_Category_ID + " AND pca.C_AcctSchema_ID=" + _C_AcctSchema_ID + "), Updated=SysDate, UpdatedBy=0 " + "WHERE pa.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM M_Product p " + "WHERE p.M_Product_ID=pa.M_Product_ID" + " AND p.M_Product_Category_ID=" + _M_Product_Category_ID + ")"; int updated = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); AddLog(0, null, new Decimal(updated), "@Updated@"); // Insert new Products sql = "INSERT INTO M_Product_Acct " + "(M_Product_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct," + " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct," + " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct " //Added + " ,P_Resource_Absorption_Acct, P_MaterialOverhd_Acct " + ") " + "SELECT p.M_Product_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct," + " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct," + " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct " + " ,acct.P_Resource_Absorption_Acct, acct.P_MaterialOverhd_Acct" + " FROM M_Product p" + " INNER JOIN M_Product_Category_Acct acct ON (acct.M_Product_Category_ID=p.M_Product_Category_ID)" + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID // # + " AND p.M_Product_Category_ID=" + _M_Product_Category_ID // # + " AND NOT EXISTS (SELECT * FROM M_Product_Acct pa " + "WHERE pa.M_Product_ID=p.M_Product_ID" + " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; int created = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); AddLog(0, null, new Decimal(created), "@Created@"); return("@Created@=" + created + ", @Updated@=" + updated); }
/// <summary> /// Get Costs /// </summary> /// <param name="cost">Cost</param> /// <param name="to">where to get costs from </param> /// <returns>costs (could be 0) or null if not found</returns> private Decimal?GetCosts(VAdvantage.Model.MCost cost, String to) { Decimal?retValue = null; // Average Invoice if (to.Equals(TO_AverageInvoice)) { MCostElement ce = GetCostElement(TO_AverageInvoice); if (ce == null) { throw new Exception("CostElement not found: " + TO_AverageInvoice); } VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Average Invoice History else if (to.Equals(TO_AverageInvoiceHistory)) { MCostElement ce = GetCostElement(TO_AverageInvoice); if (ce == null) { throw new Exception("CostElement not found: " + TO_AverageInvoice); } VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetHistoryAverage(); } } // Average PO else if (to.Equals(TO_AveragePO)) { MCostElement ce = GetCostElement(TO_AveragePO); if (ce == null) { throw new Exception("CostElement not found: " + TO_AveragePO); } VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Average PO History else if (to.Equals(TO_AveragePOHistory)) { MCostElement ce = GetCostElement(TO_AveragePO); if (ce == null) { throw new Exception("CostElement not found: " + TO_AveragePO); } VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetHistoryAverage(); } } // FiFo else if (to.Equals(TO_FiFo)) { MCostElement ce = GetCostElement(TO_FiFo); if (ce == null) { throw new Exception("CostElement not found: " + TO_FiFo); } VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Future Std Costs else if (to.Equals(TO_FutureStandardCost)) { retValue = cost.GetFutureCostPrice(); } // Last Inv Price else if (to.Equals(TO_LastInvoicePrice)) { MCostElement ce = GetCostElement(TO_LastInvoicePrice); if (ce != null) { VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } if (retValue == null) { MProduct product = MProduct.Get(GetCtx(), cost.GetM_Product_ID()); MAcctSchema as1 = MAcctSchema.Get(GetCtx(), cost.GetC_AcctSchema_ID()); retValue = VAdvantage.Model.MCost.GetLastInvoicePrice(product, cost.GetM_AttributeSetInstance_ID(), cost.GetAD_Org_ID(), as1.GetC_Currency_ID()); } } // Last PO Price else if (to.Equals(TO_LastPOPrice)) { MCostElement ce = GetCostElement(TO_LastPOPrice); if (ce != null) { if (BTR002_IsPickLastPO == "N") { VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } } if (retValue == null) { MProduct product = MProduct.Get(GetCtx(), cost.GetM_Product_ID()); MAcctSchema as1 = MAcctSchema.Get(GetCtx(), cost.GetC_AcctSchema_ID()); retValue = VAdvantage.Model.MCost.GetLastPOPrice(product, cost.GetM_AttributeSetInstance_ID(), cost.GetAD_Org_ID(), as1.GetC_Currency_ID()); } } // FiFo else if (to.Equals(TO_LiFo)) { MCostElement ce = GetCostElement(TO_LiFo); if (ce == null) { throw new Exception("CostElement not found: " + TO_LiFo); } VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Old Std Costs else if (to.Equals(TO_OldStandardCost)) { retValue = GetOldCurrentCostPrice(cost); } // Price List else if (to.Equals(TO_PriceListLimit)) { retValue = GetPrice(cost); } // Standard Costs else if (to.Equals(TO_StandardCost)) { retValue = cost.GetCurrentCostPrice(); } return(retValue); }
} // doIt /// <summary> /// Create GL Journal /// </summary> /// <returns>document info</returns> private String CreateGLJournal() { List <X_T_InvoiceGL> list = new List <X_T_InvoiceGL>(); String sql = "SELECT * FROM T_InvoiceGL " + "WHERE AD_PInstance_ID=" + GetAD_PInstance_ID() + " ORDER BY AD_Org_ID"; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { list.Add(new X_T_InvoiceGL(GetCtx(), idr, Get_TrxName())); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } if (list.Count == 0) { return(" - No Records found"); } // MAcctSchema aas = MAcctSchema.Get(GetCtx(), _C_AcctSchema_ID); MAcctSchemaDefault asDefaultAccts = MAcctSchemaDefault.Get(GetCtx(), _C_AcctSchema_ID); MGLCategory cat = MGLCategory.GetDefaultSystem(GetCtx()); if (cat == null) { MDocType docType = MDocType.Get(GetCtx(), _C_DocTypeReval_ID); cat = MGLCategory.Get(GetCtx(), docType.GetGL_Category_ID()); } // MJournalBatch batch = new MJournalBatch(GetCtx(), 0, Get_TrxName()); batch.SetDescription(GetName()); batch.SetC_DocType_ID(_C_DocTypeReval_ID); batch.SetDateDoc(DateTime.Now);// new Timestamp(System.currentTimeMillis())); batch.SetDateAcct(_DateReval); batch.SetC_Currency_ID(aas.GetC_Currency_ID()); if (!batch.Save()) { return(GetRetrievedError(batch, "Could not create Batch")); //return " - Could not create Batch"; } // MJournal journal = null; Decimal? drTotal = Env.ZERO; Decimal? crTotal = Env.ZERO; int AD_Org_ID = 0; for (int i = 0; i < list.Count; i++) { X_T_InvoiceGL gl = list[i];//.get(i); if (Env.Signum(gl.GetAmtRevalDrDiff()) == 0 && Env.Signum(gl.GetAmtRevalCrDiff()) == 0) { continue; } MInvoice invoice = new MInvoice(GetCtx(), gl.GetC_Invoice_ID(), null); if (invoice.GetC_Currency_ID() == aas.GetC_Currency_ID()) { continue; } // if (journal == null) { journal = new MJournal(batch); journal.SetC_AcctSchema_ID(aas.GetC_AcctSchema_ID()); journal.SetC_Currency_ID(aas.GetC_Currency_ID()); journal.SetC_ConversionType_ID(_C_ConversionTypeReval_ID); MOrg org = MOrg.Get(GetCtx(), gl.GetAD_Org_ID()); journal.SetDescription(GetName() + " - " + org.GetName()); journal.SetGL_Category_ID(cat.GetGL_Category_ID()); if (!journal.Save()) { return(GetRetrievedError(journal, "Could not create Journal")); //return " - Could not create Journal"; } } // MJournalLine line = new MJournalLine(journal); line.SetLine((i + 1) * 10); line.SetDescription(invoice.GetSummary()); // MFactAcct fa = new MFactAcct(GetCtx(), gl.GetFact_Acct_ID(), null); line.SetC_ValidCombination_ID(MAccount.Get(fa)); Decimal?dr = gl.GetAmtRevalDrDiff(); Decimal?cr = gl.GetAmtRevalCrDiff(); drTotal = Decimal.Add(drTotal.Value, dr.Value); crTotal = Decimal.Add(crTotal.Value, cr.Value); line.SetAmtSourceDr(dr.Value); line.SetAmtAcctDr(dr.Value); line.SetAmtSourceCr(cr.Value); line.SetAmtAcctCr(cr.Value); line.Save(); // if (AD_Org_ID == 0) // invoice org id { AD_Org_ID = gl.GetAD_Org_ID(); } // Change in Org if (AD_Org_ID != gl.GetAD_Org_ID()) { CreateBalancing(asDefaultAccts, journal, drTotal.Value, crTotal.Value, AD_Org_ID, (i + 1) * 10); // AD_Org_ID = gl.GetAD_Org_ID(); drTotal = Env.ZERO; crTotal = Env.ZERO; journal = null; } } CreateBalancing(asDefaultAccts, journal, drTotal.Value, crTotal.Value, AD_Org_ID, (list.Count + 1) * 10); return(" - " + batch.GetDocumentNo() + " #" + list.Count); } // createGLJournal
} // prepare /// <summary> /// Process /// </summary> /// <returns>Info</returns> protected override String DoIt() { log.Info("SourceAcctSchema_ID=" + _SourceAcctSchema_ID + ", TargetAcctSchema_ID=" + _TargetAcctSchema_ID); if (_SourceAcctSchema_ID == 0 || _TargetAcctSchema_ID == 0) { throw new Exception("ID=0"); } if (_SourceAcctSchema_ID == _TargetAcctSchema_ID) { throw new Exception("Account Schema must be different"); } MAcctSchema source = MAcctSchema.Get(GetCtx(), _SourceAcctSchema_ID, null); if (source.Get_ID() == 0) { throw new Exception("@NotFound@ Source @C_AcctSchema_ID@=" + _SourceAcctSchema_ID); } MAcctSchema target = new MAcctSchema(GetCtx(), _TargetAcctSchema_ID, Get_Trx()); if (target.Get_ID() == 0) { throw new Exception("@NotFound@ Target @C_AcctSchema_ID@=" + _TargetAcctSchema_ID); } // // MAcctSchemaElement[] sourceElements = source.getAcctSchemaElements(); MAcctSchemaElement[] targetElements = target.GetAcctSchemaElements(); if (targetElements.Length == 0) { throw new Exception("@NotFound@ Target C_AcctSchema_Element"); } // Accounting Element must be the same MAcctSchemaElement sourceAcctElement = source.GetAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_Account); if (sourceAcctElement == null) { throw new Exception("NotFound Source AC C_AcctSchema_Element"); } MAcctSchemaElement targetAcctElement = target.GetAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_Account); if (targetAcctElement == null) { throw new Exception("NotFound Target AC C_AcctSchema_Element"); } if (sourceAcctElement.GetC_Element_ID() != targetAcctElement.GetC_Element_ID()) { throw new Exception("@C_Element_ID@ different"); } if (MAcctSchemaGL.Get(GetCtx(), _TargetAcctSchema_ID) == null) { CopyGL(target); } if (MAcctSchemaDefault.Get(GetCtx(), _TargetAcctSchema_ID) == null) { CopyDefault(target); } return("@OK@"); } // doIt
} // prepare /// <summary> /// Process /// </summary> /// <returns></returns> protected override String DoIt() { log.Info("C_AcctSchema_ID=" + _C_AcctSchema_ID + ", CopyOverwriteAcct=" + _CopyOverwriteAcct); if (_C_AcctSchema_ID == 0) { throw new Exception("C_AcctSchema_ID=0"); } MAcctSchema a = MAcctSchema.Get(GetCtx(), _C_AcctSchema_ID); if (a.Get_ID() == 0) { throw new Exception("Not Found - C_AcctSchema_ID=" + _C_AcctSchema_ID); } MAcctSchemaDefault acct = MAcctSchemaDefault.Get(GetCtx(), _C_AcctSchema_ID); if (acct == null || acct.Get_ID() == 0) { throw new Exception("Default Not Found - C_AcctSchema_ID=" + _C_AcctSchema_ID); } String sql = null; int updated = 0; int created = 0; int updatedTotal = 0; int createdTotal = 0; // Update existing Product Category if (_CopyOverwriteAcct) { sql = "UPDATE M_Product_Category_Acct pa " + "SET P_Revenue_Acct=" + acct.GetP_Revenue_Acct() + ", P_Expense_Acct=" + acct.GetP_Expense_Acct() + ", P_CostAdjustment_Acct=" + acct.GetP_CostAdjustment_Acct() + ", P_InventoryClearing_Acct=" + acct.GetP_InventoryClearing_Acct() + ", P_Asset_Acct=" + acct.GetP_Asset_Acct() + ", P_COGS_Acct=" + acct.GetP_COGS_Acct() + ", P_PurchasePriceVariance_Acct=" + acct.GetP_PurchasePriceVariance_Acct() + ", P_InvoicePriceVariance_Acct=" + acct.GetP_InvoicePriceVariance_Acct() + ", P_TradeDiscountRec_Acct=" + acct.GetP_TradeDiscountRec_Acct() + ", P_TradeDiscountGrant_Acct=" + acct.GetP_TradeDiscountGrant_Acct() // Added **************** Lokesh Chauhan **************** + ", P_Resource_Absorption_Acct = " + acct.GetP_Resource_Absorption_Acct() + ", P_MaterialOverhd_Acct = " + acct.GetP_MaterialOverhd_Acct() // **************** + ", Updated=SysDate, UpdatedBy=0 " + "WHERE pa.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM M_Product_Category p " + "WHERE p.M_Product_Category_ID=pa.M_Product_Category_ID)"; //updated = DataBase.executeUpdate(sql, Get_Trx()); updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @M_Product_Category_ID@"); updatedTotal += updated; } // Insert new Product Category sql = "INSERT INTO M_Product_Category_Acct " + "(M_Product_Category_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct," + " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct," + " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct " // Added **************** Lokesh Chauhan **************** + " ,P_Resource_Absorption_Acct, P_MaterialOverhd_Acct " // **************** + " ) " + "SELECT p.M_Product_Category_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct," + " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct," + " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct " // Added **************** Lokesh Chauhan **************** + " ,acct.P_Resource_Absorption_Acct, acct.P_MaterialOverhd_Acct " // Added **************** + " FROM M_Product_Category p" + " INNER JOIN C_AcctSchema_Default acct ON (p.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM M_Product_Category_Acct pa " + "WHERE pa.M_Product_Category_ID=p.M_Product_Category_ID" + " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @M_Product_Category_ID@"); createdTotal += created; if (!_CopyOverwriteAcct) // Insert new Products { sql = "INSERT INTO M_Product_Acct " + "(M_Product_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct," + " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct," + " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct,P_Resource_Absorption_Acct, P_MaterialOverhd_Acct) " + "SELECT p.M_Product_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct," + " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct," + " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct, acct.P_Resource_Absorption_Acct, acct.P_MaterialOverhd_Acct " + "FROM M_Product p" + " INNER JOIN M_Product_Category_Acct acct ON (acct.M_Product_Category_ID=p.M_Product_Category_ID)" + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND p.M_Product_Category_ID=acct.M_Product_Category_ID" + " AND NOT EXISTS (SELECT * FROM M_Product_Acct pa " + "WHERE pa.M_Product_ID=p.M_Product_ID" + " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @M_Product_ID@"); createdTotal += created; } // Update Business Partner Group if (_CopyOverwriteAcct) { sql = "UPDATE C_BP_Group_Acct a " + "SET C_Receivable_Acct=" + acct.GetC_Receivable_Acct() + ", C_Receivable_Services_Acct=" + acct.GetC_Receivable_Services_Acct() + ", C_Prepayment_Acct=" + acct.GetC_Prepayment_Acct() + ", V_Liability_Acct=" + acct.GetV_Liability_Acct() + ", V_Liability_Services_Acct=" + acct.GetV_Liability_Services_Acct() + ", V_Prepayment_Acct=" + acct.GetV_Prepayment_Acct() + ", PayDiscount_Exp_Acct=" + acct.GetPayDiscount_Exp_Acct() + ", PayDiscount_Rev_Acct=" + acct.GetPayDiscount_Rev_Acct() + ", WriteOff_Acct=" + acct.GetWriteOff_Acct() + ", NotInvoicedReceipts_Acct=" + acct.GetNotInvoicedReceipts_Acct() + ", UnEarnedRevenue_Acct=" + acct.GetUnEarnedRevenue_Acct() + ", NotInvoicedRevenue_Acct=" + acct.GetNotInvoicedRevenue_Acct() + ", NotInvoicedReceivables_Acct=" + acct.GetNotInvoicedReceivables_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BP_Group_Acct x " + "WHERE x.C_BP_Group_ID=a.C_BP_Group_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_BP_Group_ID@"); updatedTotal += updated; } // Insert Business Partner Group sql = "INSERT INTO C_BP_Group_Acct " + "(C_BP_Group_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " C_Receivable_Acct, C_Receivable_Services_Acct, C_PrePayment_Acct," + " V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct," + " PayDiscount_Exp_Acct, PayDiscount_Rev_Acct, WriteOff_Acct," + " NotInvoicedReceipts_Acct, UnEarnedRevenue_Acct," + " NotInvoicedRevenue_Acct, NotInvoicedReceivables_Acct) " + "SELECT x.C_BP_Group_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.C_Receivable_Acct, acct.C_Receivable_Services_Acct, acct.C_PrePayment_Acct," + " acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct," + " acct.PayDiscount_Exp_Acct, acct.PayDiscount_Rev_Acct, acct.WriteOff_Acct," + " acct.NotInvoicedReceipts_Acct, acct.UnEarnedRevenue_Acct," + " acct.NotInvoicedRevenue_Acct, acct.NotInvoicedReceivables_Acct " + "FROM C_BP_Group x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_BP_Group_Acct a " + "WHERE a.C_BP_Group_ID=x.C_BP_Group_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_BP_Group_ID@"); createdTotal += created; // Update Business Partner - Employee if (_CopyOverwriteAcct) { sql = "UPDATE C_BP_Employee_Acct a " + "SET E_Expense_Acct=" + acct.GetE_Expense_Acct() + ", E_Prepayment_Acct=" + acct.GetE_Prepayment_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BP_Employee_Acct x " + "WHERE x.C_BPartner_ID=a.C_BPartner_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_BPartner_ID@ @IsEmployee@"); updatedTotal += updated; } // Insert new Business Partner - Employee sql = "INSERT INTO C_BP_Employee_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " E_Expense_Acct, E_Prepayment_Acct) " + "SELECT x.C_BPartner_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.E_Expense_Acct, acct.E_Prepayment_Acct " + "FROM C_BPartner x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_BP_Employee_Acct a " + "WHERE a.C_BPartner_ID=x.C_BPartner_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_BPartner_ID@ @IsEmployee@"); createdTotal += created; // if (!_CopyOverwriteAcct) { sql = "INSERT INTO C_BP_Customer_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " C_Receivable_Acct, C_Receivable_Services_Acct, C_PrePayment_Acct) " + "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.C_Receivable_Acct, acct.C_Receivable_Services_Acct, acct.C_PrePayment_Acct " + "FROM C_BPartner p" + " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)" + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID // # + " AND p.C_BP_Group_ID=acct.C_BP_Group_ID" + " AND NOT EXISTS (SELECT * FROM C_BP_Customer_Acct ca " + "WHERE ca.C_BPartner_ID=p.C_BPartner_ID" + " AND ca.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_BPartner_ID@ @IsCustomer@"); createdTotal += created; // sql = "INSERT INTO C_BP_Vendor_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct) " + "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct " + "FROM C_BPartner p" + " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)" + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID // # + " AND p.C_BP_Group_ID=acct.C_BP_Group_ID" + " AND NOT EXISTS (SELECT * FROM C_BP_Vendor_Acct va " + "WHERE va.C_BPartner_ID=p.C_BPartner_ID AND va.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_BPartner_ID@ @IsVendor@"); createdTotal += created; } // Update Warehouse if (_CopyOverwriteAcct) { sql = "UPDATE M_Warehouse_Acct a " + "SET W_Inventory_Acct=" + acct.GetW_Inventory_Acct() + ", W_Differences_Acct=" + acct.GetW_Differences_Acct() + ", W_Revaluation_Acct=" + acct.GetW_Revaluation_Acct() + ", W_InvActualAdjust_Acct=" + acct.GetW_InvActualAdjust_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM M_Warehouse_Acct x " + "WHERE x.M_Warehouse_ID=a.M_Warehouse_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @M_Warehouse_ID@"); updatedTotal += updated; } // Insert new Warehouse sql = "INSERT INTO M_Warehouse_Acct " + "(M_Warehouse_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " W_Inventory_Acct, W_Differences_Acct, W_Revaluation_Acct, W_InvActualAdjust_Acct) " + "SELECT x.M_Warehouse_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.W_Inventory_Acct, acct.W_Differences_Acct, acct.W_Revaluation_Acct, acct.W_InvActualAdjust_Acct " + "FROM M_Warehouse x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM M_Warehouse_Acct a " + "WHERE a.M_Warehouse_ID=x.M_Warehouse_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @M_Warehouse_ID@"); createdTotal += created; // Update Project if (_CopyOverwriteAcct) { sql = "UPDATE C_Project_Acct a " + "SET PJ_Asset_Acct=" + acct.GetPJ_Asset_Acct() + ", PJ_WIP_Acct=" + acct.GetPJ_Asset_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Project_Acct x " + "WHERE x.C_Project_ID=a.C_Project_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_Project_ID@"); updatedTotal += updated; } // Insert new Projects sql = "INSERT INTO C_Project_Acct " + "(C_Project_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " PJ_Asset_Acct, PJ_WIP_Acct) " + "SELECT x.C_Project_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.PJ_Asset_Acct, acct.PJ_WIP_Acct " + "FROM C_Project x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Project_Acct a " + "WHERE a.C_Project_ID=x.C_Project_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_Project_ID@"); createdTotal += created; // Update Tax if (_CopyOverwriteAcct) { sql = "UPDATE C_Tax_Acct a " + "SET T_Due_Acct=" + acct.GetT_Due_Acct() + ", T_Liability_Acct=" + acct.GetT_Liability_Acct() + ", T_Credit_Acct=" + acct.GetT_Credit_Acct() + ", T_Receivables_Acct=" + acct.GetT_Receivables_Acct() + ", T_Expense_Acct=" + acct.GetT_Expense_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Tax_Acct x " + "WHERE x.C_Tax_ID=a.C_Tax_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_Tax_ID@"); updatedTotal += updated; } // Insert new Tax sql = "INSERT INTO C_Tax_Acct " + "(C_Tax_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " T_Due_Acct, T_Liability_Acct, T_Credit_Acct, T_Receivables_Acct, T_Expense_Acct) " + "SELECT x.C_Tax_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.T_Due_Acct, acct.T_Liability_Acct, acct.T_Credit_Acct, acct.T_Receivables_Acct, acct.T_Expense_Acct " + "FROM C_Tax x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Tax_Acct a " + "WHERE a.C_Tax_ID=x.C_Tax_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_Tax_ID@"); createdTotal += created; // Update BankAccount if (_CopyOverwriteAcct) { sql = "UPDATE C_BankAccount_Acct a " + "SET B_InTransit_Acct=" + acct.GetB_InTransit_Acct() + ", B_Asset_Acct=" + acct.GetB_Asset_Acct() + ", B_Expense_Acct=" + acct.GetB_Expense_Acct() + ", B_InterestRev_Acct=" + acct.GetB_InterestRev_Acct() + ", B_InterestExp_Acct=" + acct.GetB_InterestExp_Acct() + ", B_Unidentified_Acct=" + acct.GetB_Unidentified_Acct() + ", B_UnallocatedCash_Acct=" + acct.GetB_UnallocatedCash_Acct() + ", B_PaymentSelect_Acct=" + acct.GetB_PaymentSelect_Acct() + ", B_SettlementGain_Acct=" + acct.GetB_SettlementGain_Acct() + ", B_SettlementLoss_Acct=" + acct.GetB_SettlementLoss_Acct() + ", B_RevaluationGain_Acct=" + acct.GetB_RevaluationGain_Acct() + ", B_RevaluationLoss_Acct=" + acct.GetB_RevaluationLoss_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BankAccount_Acct x " + "WHERE x.C_BankAccount_ID=a.C_BankAccount_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_BankAccount_ID@"); updatedTotal += updated; } // Insert new BankAccount sql = "INSERT INTO C_BankAccount_Acct " + "(C_BankAccount_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " B_InTransit_Acct, B_Asset_Acct, B_Expense_Acct, B_InterestRev_Acct, B_InterestExp_Acct," + " B_Unidentified_Acct, B_UnallocatedCash_Acct, B_PaymentSelect_Acct," + " B_SettlementGain_Acct, B_SettlementLoss_Acct," + " B_RevaluationGain_Acct, B_RevaluationLoss_Acct) " + "SELECT x.C_BankAccount_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.B_InTransit_Acct, acct.B_Asset_Acct, acct.B_Expense_Acct, acct.B_InterestRev_Acct, acct.B_InterestExp_Acct," + " acct.B_Unidentified_Acct, acct.B_UnallocatedCash_Acct, acct.B_PaymentSelect_Acct," + " acct.B_SettlementGain_Acct, acct.B_SettlementLoss_Acct," + " acct.B_RevaluationGain_Acct, acct.B_RevaluationLoss_Acct " + "FROM C_BankAccount x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_BankAccount_Acct a " + "WHERE a.C_BankAccount_ID=x.C_BankAccount_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_BankAccount_ID@"); createdTotal += created; // Update Withholding if (_CopyOverwriteAcct) { sql = "UPDATE C_Withholding_Acct a " + "SET Withholding_Acct=" + acct.GetWithholding_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Withholding_Acct x " + "WHERE x.C_Withholding_ID=a.C_Withholding_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_Withholding_ID@"); updatedTotal += updated; } // Insert new Withholding sql = "INSERT INTO C_Withholding_Acct " + "(C_Withholding_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " Withholding_Acct) " + "SELECT x.C_Withholding_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.Withholding_Acct " + "FROM C_Withholding x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Withholding_Acct a " + "WHERE a.C_Withholding_ID=x.C_Withholding_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_Withholding_ID@"); createdTotal += created; // Update Charge if (_CopyOverwriteAcct) { sql = "UPDATE C_Charge_Acct a " + "SET Ch_Expense_Acct=" + acct.GetCh_Expense_Acct() + ", Ch_Revenue_Acct=" + acct.GetCh_Revenue_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Charge_Acct x " + "WHERE x.C_Charge_ID=a.C_Charge_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_Charge_ID@"); updatedTotal += updated; } // Insert new Charge sql = "INSERT INTO C_Charge_Acct " + "(C_Charge_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " Ch_Expense_Acct, Ch_Revenue_Acct) " + "SELECT x.C_Charge_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.Ch_Expense_Acct, acct.Ch_Revenue_Acct " + "FROM C_Charge x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Charge_Acct a " + "WHERE a.C_Charge_ID=x.C_Charge_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_Charge_ID@"); createdTotal += created; // Update Cashbook if (_CopyOverwriteAcct) { sql = "UPDATE C_Cashbook_Acct a " + "SET CB_Asset_Acct=" + acct.GetCB_Asset_Acct() + ", CB_Differences_Acct=" + acct.GetCB_Differences_Acct() + ", CB_CashTransfer_Acct=" + acct.GetCB_CashTransfer_Acct() + ", CB_Expense_Acct=" + acct.GetCB_Expense_Acct() + ", CB_Receipt_Acct=" + acct.GetCB_Receipt_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Cashbook_Acct x " + "WHERE x.C_Cashbook_ID=a.C_Cashbook_ID)"; updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(updated), "@Updated@ @C_Cashbook_ID@"); updatedTotal += updated; } // Insert new Cashbook sql = "INSERT INTO C_Cashbook_Acct " + "(C_Cashbook_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " CB_Asset_Acct, CB_Differences_Acct, CB_CashTransfer_Acct," + " CB_Expense_Acct, CB_Receipt_Acct) " + "SELECT x.C_Cashbook_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.CB_Asset_Acct, acct.CB_Differences_Acct, acct.CB_CashTransfer_Acct," + " acct.CB_Expense_Acct, acct.CB_Receipt_Acct " + "FROM C_Cashbook x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Cashbook_Acct a " + "WHERE a.C_Cashbook_ID=x.C_Cashbook_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(created), "@Created@ @C_Cashbook_ID@"); createdTotal += created; return("@Created@=" + createdTotal + ", @Updated@=" + updatedTotal); } // doIt
} // prepare /// <summary> /// Process /// </summary> /// <returns>message</returns> protected override String DoIt() { log.Info("C_AcctSchema_ID=" + _C_AcctSchema_ID); if (_C_AcctSchema_ID == 0) { throw new SystemException("C_AcctSchema_ID=0"); } MAcctSchema ass = MAcctSchema.Get(GetCtx(), _C_AcctSchema_ID); if (ass.Get_ID() == 0) { throw new SystemException("Not Found - C_AcctSchema_ID=" + _C_AcctSchema_ID); } // String sql = null; int _updated = 0; int _created = 0; int _updatedTotal = 0; int _createdTotal = 0; // Update existing Customers sql = "UPDATE C_BP_Customer_Acct ca " + "SET (C_Receivable_Acct,C_Receivable_Services_Acct,C_PrePayment_Acct)=" + " (SELECT C_Receivable_Acct,C_Receivable_Services_Acct,C_PrePayment_Acct " + " FROM C_BP_Group_Acct" + " WHERE C_BP_Group_ID=" + _C_BP_Group_ID + " AND C_AcctSchema_ID=" + _C_AcctSchema_ID + "), Updated=SysDate, UpdatedBy=0 " + "WHERE ca.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BPartner p " + "WHERE p.C_BPartner_ID=ca.C_BPartner_ID" + " AND p.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; _updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(_updated), "@Updated@ @C_BPartner_ID@ @IsCustomer@"); _updatedTotal += _updated; // Insert new Customer sql = "INSERT INTO C_BP_Customer_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " C_Receivable_Acct, C_Receivable_Services_Acct, C_PrePayment_Acct) " + "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.C_Receivable_Acct, acct.C_Receivable_Services_Acct, acct.C_PrePayment_Acct " + "FROM C_BPartner p" + " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)" + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID // # + " AND p.C_BP_Group_ID=" + _C_BP_Group_ID + " AND NOT EXISTS (SELECT * FROM C_BP_Customer_Acct ca " + "WHERE ca.C_BPartner_ID=p.C_BPartner_ID" + " AND ca.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; _created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(_created), "@Created@ @C_BPartner_ID@ @IsCustomer@"); _createdTotal += _created; // Update existing Vendors sql = "UPDATE C_BP_Vendor_Acct va " + "SET (V_Liability_Acct,V_Liability_Services_Acct,V_PrePayment_Acct)=" + " (SELECT V_Liability_Acct,V_Liability_Services_Acct,V_PrePayment_Acct " + " FROM C_BP_Group_Acct" + " WHERE C_BP_Group_ID=" + _C_BP_Group_ID + " AND C_AcctSchema_ID=" + _C_AcctSchema_ID + "), Updated=SysDate, UpdatedBy=0 " + "WHERE va.C_AcctSchema_ID=" + _C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BPartner p " + "WHERE p.C_BPartner_ID=va.C_BPartner_ID" + " AND p.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; _updated = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(_updated), "@Updated@ @C_BPartner_ID@ @IsVendor@"); _updatedTotal += _updated; // Insert new Vendors sql = "INSERT INTO C_BP_Vendor_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct) " + "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct " + "FROM C_BPartner p" + " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)" + "WHERE acct.C_AcctSchema_ID=" + _C_AcctSchema_ID // # + " AND p.C_BP_Group_ID=" + _C_BP_Group_ID + " AND NOT EXISTS (SELECT * FROM C_BP_Vendor_Acct va " + "WHERE va.C_BPartner_ID=p.C_BPartner_ID AND va.C_AcctSchema_ID=acct.C_AcctSchema_ID)"; _created = DataBase.DB.ExecuteQuery(sql, null, Get_Trx()); AddLog(0, null, Utility.Util.GetValueOfDecimal(_created), "@Created@ @C_BPartner_ID@ @IsVendor@"); _createdTotal += _created; return("@Created@=" + _createdTotal + ", @Updated@=" + _updatedTotal); } // doIt