} // copyGL /// <summary> /// Copy Default /// </summary> /// <param name="targetAS">target</param> private void CopyDefault(MAcctSchema targetAS) { MAcctSchemaDefault source = MAcctSchemaDefault.Get(GetCtx(), _SourceAcctSchema_ID); MAcctSchemaDefault target = new MAcctSchemaDefault(GetCtx(), 0, Get_Trx()); target.SetC_AcctSchema_ID(_TargetAcctSchema_ID); target.SetC_AcctSchema_ID(_TargetAcctSchema_ID); //ArrayList<KeyNamePair> list = source.getAcctInfo(); List <KeyNamePair> list = source.GetAcctInfo(); for (int i = 0; i < list.Count; i++) { //KeyNamePair pp = list.get(i); KeyNamePair pp = list[i]; int sourceC_ValidCombination_ID = pp.GetKey(); String columnName = pp.GetName(); MAccount sourceAccount = MAccount.Get(GetCtx(), sourceC_ValidCombination_ID); MAccount targetAccount = CreateAccount(targetAS, sourceAccount); target.SetValue(columnName, Utility.Util.GetValueOfInt(targetAccount.GetC_ValidCombination_ID())); } if (!target.Save()) { throw new Exception("Could not Save Default"); } } // copyDefault
} // createGLJournal /// <summary> /// Create Balancing Entry /// </summary> /// <param name="asDefaultAccts">acct schema default accounts</param> /// <param name="journal">journal</param> /// <param name="drTotal">dr</param> /// <param name="crTotal">cr</param> /// <param name="AD_Org_ID">org</param> /// <param name="lineNo">lineno base line no</param> private void CreateBalancing(MAcctSchemaDefault asDefaultAccts, MJournal journal, Decimal drTotal, Decimal crTotal, int AD_Org_ID, int lineNo) { if (journal == null) { throw new ArgumentException("Jornal is null"); } // CR Entry = Gain if (Env.Signum(drTotal) != 0) { MJournalLine line = new MJournalLine(journal); line.SetLine(lineNo + 1); MAccount bas = MAccount.Get(GetCtx(), asDefaultAccts.GetUnrealizedGain_Acct()); MAccount acct = MAccount.Get(GetCtx(), asDefaultAccts.GetAD_Client_ID(), AD_Org_ID, asDefaultAccts.GetC_AcctSchema_ID(), bas.GetAccount_ID(), bas.GetC_SubAcct_ID(), bas.GetM_Product_ID(), bas.GetC_BPartner_ID(), bas.GetAD_OrgTrx_ID(), bas.GetC_LocFrom_ID(), bas.GetC_LocTo_ID(), bas.GetC_SalesRegion_ID(), bas.GetC_Project_ID(), bas.GetC_Campaign_ID(), bas.GetC_Activity_ID(), bas.GetUser1_ID(), bas.GetUser2_ID(), bas.GetUserElement1_ID(), bas.GetUserElement2_ID()); line.SetDescription(Msg.GetElement(GetCtx(), "UnrealizedGain_Acct")); line.SetC_ValidCombination_ID(acct.GetC_ValidCombination_ID()); line.SetAmtSourceCr(drTotal); line.SetAmtAcctCr(drTotal); line.Save(); } // DR Entry = Loss if (Env.Signum(crTotal) != 0) { MJournalLine line = new MJournalLine(journal); line.SetLine(lineNo + 2); MAccount bas = MAccount.Get(GetCtx(), asDefaultAccts.GetUnrealizedLoss_Acct()); MAccount acct = MAccount.Get(GetCtx(), asDefaultAccts.GetAD_Client_ID(), AD_Org_ID, asDefaultAccts.GetC_AcctSchema_ID(), bas.GetAccount_ID(), bas.GetC_SubAcct_ID(), bas.GetM_Product_ID(), bas.GetC_BPartner_ID(), bas.GetAD_OrgTrx_ID(), bas.GetC_LocFrom_ID(), bas.GetC_LocTo_ID(), bas.GetC_SalesRegion_ID(), bas.GetC_Project_ID(), bas.GetC_Campaign_ID(), bas.GetC_Activity_ID(), bas.GetUser1_ID(), bas.GetUser2_ID(), bas.GetUserElement1_ID(), bas.GetUserElement2_ID()); line.SetDescription(Msg.GetElement(GetCtx(), "UnrealizedLoss_Acct")); line.SetC_ValidCombination_ID(acct.GetC_ValidCombination_ID()); line.SetAmtSourceDr(crTotal); line.SetAmtAcctDr(crTotal); line.Save(); } } // createBalancing
} // 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
/// <summary> /// Create new Charge based on the parameters passed /// </summary> /// <param name="ctx"></param> /// <param name="m_C_AcctSchema_ID"></param> /// <param name="m_C_TaxCategory_ID"></param> /// <param name="name"></param> /// <param name="primaryC_ElementValue_ID"></param> /// <param name="expense"></param> /// <returns></returns> public int CreateCharge(Ctx ctx, int m_C_AcctSchema_ID, int m_C_TaxCategory_ID, String name, int primaryC_ElementValue_ID, Boolean expense) { MCharge charge = new MCharge(ctx, 0, null); charge.SetName(name); charge.SetC_TaxCategory_ID(m_C_TaxCategory_ID); if (!charge.Save()) { // log.Log(Level.SEVERE, name + " not created"); Msg = name + " not created"; ID = 0; return(0); } MAcctSchema m_acctSchema = null; // Get Primary AcctSchama if (m_acctSchema == null) { m_acctSchema = new MAcctSchema(ctx, m_C_AcctSchema_ID, null); } if (m_acctSchema == null || m_acctSchema.GetC_AcctSchema_ID() == 0) { ID = 0; return(0); } MAcctSchemaElement primary_ase = m_acctSchema.GetAcctSchemaElement(X_C_AcctSchema_Element.ELEMENTTYPE_Account); // Get All MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, charge.GetAD_Client_ID()); foreach (MAcctSchema ac in ass) { // Target Account MAccount defaultAcct = MAccount.GetDefault(ac, true); // optional null // Natural Account int C_ElementValue_ID = primaryC_ElementValue_ID; MAcctSchemaElement ase = ac.GetAcctSchemaElement(X_C_AcctSchema_Element.ELEMENTTYPE_Account); if (primary_ase.GetC_Element_ID() != ase.GetC_Element_ID()) { MAcctSchemaDefault defAccts = MAcctSchemaDefault.Get(ctx, ac.GetC_AcctSchema_ID()); int C_ValidCombination_ID = defAccts.GetCh_Expense_Acct(); if (!expense) { C_ValidCombination_ID = defAccts.GetCh_Revenue_Acct(); } MAccount chargeAcct = MAccount.Get(ctx, C_ValidCombination_ID); C_ElementValue_ID = chargeAcct.GetAccount_ID(); // Fallback if (C_ElementValue_ID == 0) { C_ElementValue_ID = defaultAcct.GetAccount_ID(); if (C_ElementValue_ID == 0) { C_ElementValue_ID = ase.GetC_ElementValue_ID(); } if (C_ElementValue_ID == 0) { // log.Log(Level.WARNING, "No Default ElementValue for " + ac); Msg = "No Default ElementValue for " + ac; continue; } } } MAccount acct = MAccount.Get(ctx, charge.GetAD_Client_ID(), charge.GetAD_Org_ID(), ac.GetC_AcctSchema_ID(), C_ElementValue_ID, defaultAcct.GetC_SubAcct_ID(), defaultAcct.GetM_Product_ID(), defaultAcct.GetC_BPartner_ID(), defaultAcct.GetAD_OrgTrx_ID(), defaultAcct.GetC_LocFrom_ID(), defaultAcct.GetC_LocTo_ID(), defaultAcct.GetC_SalesRegion_ID(), defaultAcct.GetC_Project_ID(), defaultAcct.GetC_Campaign_ID(), defaultAcct.GetC_Activity_ID(), defaultAcct.GetUser1_ID(), defaultAcct.GetUser2_ID(), defaultAcct.GetUserElement1_ID(), defaultAcct.GetUserElement2_ID()); if (acct == null) { //log.Log(Level.WARNING, "No Default Account for " + ac); Msg = "No Default Account for " + ac; continue; } // Update Accounts StringBuilder sql = new StringBuilder("UPDATE C_Charge_Acct "); sql.Append("SET CH_Expense_Acct=").Append(acct.GetC_ValidCombination_ID()); sql.Append(", CH_Revenue_Acct=").Append(acct.GetC_ValidCombination_ID()); sql.Append(" WHERE C_Charge_ID=").Append(charge.GetC_Charge_ID()); sql.Append(" AND C_AcctSchema_ID=").Append(ac.GetC_AcctSchema_ID()); // int no = VAdvantage.DataBase.DB.ExecuteQuery(sql.ToString(), null, null); if (no != 1) { //log.Log(Level.WARNING, "Update #" + no + "\n" + sql.ToString()); Msg = "Update #" + no + "\n" + sql.ToString(); } } ID = charge.GetC_Charge_ID(); return(charge.GetC_Charge_ID()); }
} // 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