/// <summary>
/// Get Labor Cost from Expense Report
/// </summary>
/// <param name="as1"></param>
/// <returns>Unit Labor Cost</returns>
private Decimal?GetLaborCost(MAcctSchema as1)
{
Decimal?retValue = null;
/** TODO Labor Cost */
return(retValue);
}
} // 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
/// <summary>
/// Load controls lookup etc.
/// </summary>
/// <param name="ctx"></param>
/// <param name="windowNo"></param>
/// <param name="C_AcctSchema_ID"></param>
/// <returns>class lookups</returns>
public AccountSchema AccountSchemaLoad(Ctx ctx, int windowNo, int C_AcctSchema_ID)
{
AccountSchema objSchema = new AccountSchema();
MAcctSchemaElement[] elements = null;
var _AcctSchema = new MAcctSchema(ctx, C_AcctSchema_ID, null);
ctx.GetCtx(windowNo).SetContext(windowNo, "C_AcctSchema_ID", C_AcctSchema_ID);
elements = _AcctSchema.GetAcctSchemaElements();
objSchema.IsHasAlies = _AcctSchema.IsHasAlias();
objSchema.Elements = new List <AccountingElements>();
for (int i = 0; i < elements.Length; i++)
{
AccountingElements obj = new AccountingElements();
MAcctSchemaElement ase = elements[i];
obj.Type = ase.GetElementType();
obj.IsMandatory = ase.IsMandatory();
obj.ID = ase.Get_ID();
obj.Name = ase.GetName();
obj.DefaultValue = ase.GetDefaultValue();
obj.SeqNo = ase.GetSeqNo();
obj.AD_Column_ID = ase.GetAD_Column_ID();
obj.IsHeavyData = Util.GetValueOfBool(ase.Get_Value("IsHeavyData"));
objSchema.Elements.Add(obj);
}
objSchema.Description = _AcctSchema.ToString();
return(objSchema);
}
public JsonResult PostImmediate(int AD_Client_ID, int AD_Table_ID, int Record_ID, bool force)
{
Ctx ctx = Session["ctx"] as Ctx;
string res = "";
try
{
string clientName = ctx.GetAD_Org_Name() + "_" + ctx.GetAD_User_Name();
string storedPath = Path.Combine(HostingEnvironment.ApplicationPhysicalPath, "");
storedPath += clientName;
VLogMgt.Initialize(true, storedPath);
MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, AD_Client_ID);
res = Doc.PostImmediate(ass, AD_Table_ID, Record_ID, force, null);
if (res == null || res.Trim().Length > 0)
{
res = "OK";
}
}
catch (Exception ex)
{
res += ex.Message;
}
return(Json(new { result = res }, JsonRequestBehavior.AllowGet));
}
} // 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>
/// Work
/// </summary>
protected override void DoWork()
{
_summary = new StringBuilder();
// Get Schemata
if (_modelLocal.GetC_AcctSchema_ID() == 0)
{
_ass = MAcctSchema.GetClientAcctSchema(GetCtx(), _modelLocal.GetAD_Client_ID());
}
else // only specific accounting schema
{
_ass = new MAcctSchema[] { new MAcctSchema(GetCtx(), _modelLocal.GetC_AcctSchema_ID(), null) };
}
//
PostSession();
MCost.Create(_clientLocal);
//
int no = _modelLocal.DeleteLog();
_summary.Append("Logs deleted=").Append(no);
//
MAcctProcessorLog pLog = new MAcctProcessorLog(_modelLocal, _summary.ToString());
pLog.SetReference("#" + Utility.Util.GetValueOfString(_runCount) // String.valueOf(p_runCount)
+ " - " + TimeUtil.FormatElapsed(_startWork)); //new DateTime(_startWork)));
pLog.Save();
}
/// <summary>
/// Create Facts (the accounting logic) for
/// PJI
/// <pre>
/// Issue
/// ProjectWIP DR
/// Inventory CR
/// </pre>
/// Project Account is either Asset or WIP depending on Project Type
/// </summary>
/// <param name="?"></param>
/// <returns>fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
SetC_Currency_ID(as1.GetC_Currency_ID());
MProject project = new MProject(GetCtx(), _issue.GetC_Project_ID(), null);
String ProjectCategory = project.GetProjectCategory();
MProduct product = MProduct.Get(GetCtx(), _issue.GetM_Product_ID());
// Line pointers
FactLine dr = null;
FactLine cr = null;
// Issue Cost
Decimal?cost = null;
if (_issue.GetM_InOutLine_ID() != 0)
{
cost = GetPOCost(as1);
}
else if (_issue.GetS_TimeExpenseLine_ID() != 0)
{
cost = GetLaborCost(as1);
}
if (cost == null) // standard Product Costs
{
cost = _line.GetProductCosts(as1, GetAD_Org_ID(), false);
}
// Project DR
int acctType = ACCTTYPE_ProjectWIP;
if (MProject.PROJECTCATEGORY_AssetProject.Equals(ProjectCategory))
{
acctType = ACCTTYPE_ProjectAsset;
}
dr = fact.CreateLine(_line,
GetAccount(acctType, as1), as1.GetC_Currency_ID(), cost, null);
dr.SetQty((Decimal?)Decimal.Negate(Utility.Util.GetValueOfDecimal(_line.GetQty())));
// Inventory CR
acctType = ProductCost.ACCTTYPE_P_Asset;
if (product.IsService())
{
acctType = ProductCost.ACCTTYPE_P_Expense;
}
cr = fact.CreateLine(_line,
_line.GetAccount(acctType, as1),
as1.GetC_Currency_ID(), null, cost);
cr.SetM_Locator_ID(_line.GetM_Locator_ID());
cr.SetLocationFromLocator(_line.GetM_Locator_ID(), true); // from Loc
//
List <Fact> facts = new List <Fact>();
facts.Add(fact);
return(facts);
}
/// <summary>
/// Create New Client level Costing Record
/// </summary>
/// <param name="product">product</param>
/// <param name="?">acct schema</param>
/// <returns>true if created</returns>
private bool CreateNew(MProduct product, MAcctSchema as1)
{
VAdvantage.Model.MCost cost = VAdvantage.Model.MCost.Get(product, 0, as1, 0, _ce.GetM_CostElement_ID());
if (cost.Is_New())
{
return(cost.Save());
}
return(false);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="document">pointer to document</param>
/// <param name="acctSchema">Account Schema to create accounts</param>
/// <param name="defaultPostingType">the default Posting type (actual,..) for this posting</param>
public Fact(Doc document, MAcctSchema acctSchema, String defaultPostingType)
{
log = VLogger.GetVLogger(this.GetType().FullName);
_doc = document;
_acctSchema = acctSchema;
_postingType = defaultPostingType;
//
log.Config(ToString());
}
/// <summary>
/// Post Immediate
/// @ejb.interface-method view-type="both"
/// </summary>
/// <param name="ctx">Client Context</param>
/// <param name="AD_Client_ID">Client ID of Document</param>
/// <param name="AD_Table_ID">Table ID of Document</param>
/// <param name="Record_ID">Record ID of this document</param>
/// <param name="force">force posting</param>
/// <param name="trxName">transaction</param>
/// <returns>null, if success or error message</returns>
public String PostImmediate(IDictionary <string, string> ctxDic, int AD_Client_ID, int AD_Table_ID, int Record_ID, bool force, Trx trxName)
{
//log.Info("[" + _no + "] Table=" + AD_Table_ID + ", Record=" + Record_ID);
//_postCount++;
Ctx ctx = new Ctx(ctxDic);
MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, AD_Client_ID);
return(Doc.PostImmediate(ass, AD_Table_ID, Record_ID, force, trxName));
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Product_Category_ID=" + _M_Product_Category_ID
+ ", Future=" + _SetFutureCostTo
+ ", Standard=" + _SetStandardCostTo
+ "; M_PriceList_Version_ID=" + _M_PriceList_Version_ID);
if (_SetFutureCostTo == null)
{
_SetFutureCostTo = "";
}
if (_SetStandardCostTo == null)
{
_SetStandardCostTo = "";
}
// Nothing to Do
if (_SetFutureCostTo.Length == 0 && _SetStandardCostTo.Length == 0)
{
return("-");
}
// PLV required
if (_M_PriceList_Version_ID == 0 &&
(_SetFutureCostTo.Equals(TO_PriceListLimit) || _SetStandardCostTo.Equals(TO_PriceListLimit)))
{
throw new Exception("@FillMandatory@ @M_PriceList_Version_ID@");
}
// Validate Source
if (!IsValid(_SetFutureCostTo))
{
throw new Exception("@NotFound@ @M_CostElement_ID@ (Future) " + _SetFutureCostTo);
}
if (!IsValid(_SetStandardCostTo))
{
throw new Exception("@NotFound@ @M_CostElement_ID@ (Standard) " + _SetStandardCostTo);
}
// Prepare
MClient client = MClient.Get(GetCtx());
_ce = MCostElement.GetMaterialCostElement(client, MAcctSchema.COSTINGMETHOD_StandardCosting);
if (_ce.Get_ID() == 0)
{
throw new Exception("@NotFound@ @M_CostElement_ID@ (StdCost)");
}
log.Config(_ce.ToString());
_ass = MAcctSchema.GetClientAcctSchema(GetCtx(), client.GetAD_Client_ID());
for (int i = 0; i < _ass.Length; i++)
{
CreateNew(_ass[i]);
}
Commit();
// Update Cost
int counter = Update();
return("#" + counter);
}
/// <summary>
/// Create New Standard Costs
/// </summary>
/// <param name="as1">accounting schema</param>
private void CreateNew(MAcctSchema as1)
{
if (!as1.GetCostingLevel().Equals(MAcctSchema.COSTINGLEVEL_Client))
{
String txt = "Costing Level prevents creating new Costing records for " + as1.GetName();
log.Warning(txt);
AddLog(0, null, null, txt);
return;
}
String sql = "SELECT * FROM M_Product p "
+ "WHERE NOT EXISTS (SELECT * FROM M_Cost c WHERE c.M_Product_ID=p.M_Product_ID"
+ " AND c.M_CostType_ID=" + as1.GetM_CostType_ID() + " AND c.C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND c.M_CostElement_ID=" + _ce.GetM_CostElement_ID()
+ " AND c.M_AttributeSetInstance_ID=0) "
+ "AND AD_Client_ID=" + as1.GetAD_Client_ID();
if (_M_Product_Category_ID != 0)
{
sql += " AND M_Product_Category_ID=" + _M_Product_Category_ID;
}
int counter = 0;
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
if (CreateNew(new MProduct(GetCtx(), dr, null), as1))
{
counter++;
}
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
finally
{
dt = null;
if (idr != null)
{
idr.Close();
}
}
log.Info("#" + counter);
AddLog(0, null, new Decimal(counter), "Created for " + as1.GetName());
}
/// <summary>
/// Create Facts (the accounting logic) for
/// GLJ.
/// (only for the accounting scheme, it was created)
/// <pre>
/// account DR CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
List <Fact> facts = new List <Fact>();
// Other Acct Schema
// need to Post GL Journal for Multiple Accounting Schema that's why commented this section
//if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID)
//{
// return facts;
//}
// create Fact Header
Fact fact = new Fact(this, as1, _PostingType);
// get conversion rate from Assigned accounting schema tab -
Decimal conversionRate = Util.GetValueOfDecimal(DB.ExecuteScalar(@"SELECT CurrencyRate FROM GL_AssignAcctSchema WHERE
C_AcctSchema_ID = " + as1.GetC_AcctSchema_ID() + " AND GL_Journal_ID = " + record_Id, null, null));
// GLJ
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_GLJOURNAL))
{
// account DR CR
for (int i = 0; i < _lines.Length; i++)
{
// need to Post GL Journal for Multiple Accounting Schema that's why commented this condition
//if (_lines[i].GetC_AcctSchema_ID() == as1.GetC_AcctSchema_ID())
//{
// set conversion rate on line, so that amount to be converted based on that multiply rate
if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID && _lines[i].GetC_Currency_ID() != as1.GetC_Currency_ID())
{
conversionRate = MConversionRate.GetRate(_lines[i].GetC_Currency_ID(), as1.GetC_Currency_ID(),
_lines[i].GetDateAcct(), _lines[i].GetC_ConversionType_ID(),
as1.GetAD_Client_ID(), _lines[i].GetAD_Org_ID());
_lines[i].SetConversionRate(conversionRate);
}
else if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID)
{
_lines[i].SetConversionRate(conversionRate);
}
fact.CreateLine(_lines[i],
_lines[i].GetAccount(),
_lines[i].GetC_Currency_ID(),
_lines[i].GetAmtSourceDr(),
_lines[i].GetAmtSourceCr());
//}
} // for all lines
}
else
{
_error = "DocumentType unknown: " + GetDocumentType();
log.Log(Level.SEVERE, _error);
fact = null;
}
//
facts.Add(fact);
return(facts);
}
/// <summary>
/// Get Charge Account
/// </summary>
/// <param name="as1">account schema</param>
/// <param name="amount">amount for expense(+)/revenue(-)</param>
/// <returns>Charge Account or null</returns>
public MAccount GetChargeAccount(MAcctSchema as1, Decimal?amount)
{
int C_Charge_ID = GetC_Charge_ID();
if (C_Charge_ID == 0)
{
return(null);
}
return(MCharge.GetAccount(C_Charge_ID, as1, amount.Value));
}
/// <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>
/// Get Total Product Costs
/// </summary>
/// <param name="as1"></param>
/// <param name="AD_Org_ID"></param>
/// <param name="zeroCostsOK">zero/no costs are OK</param>
/// <returns>costs</returns>
public Decimal GetProductCosts(MAcctSchema as1, int AD_Org_ID, bool zeroCostsOK)
{
ProductCost pc = GetProductCost();
int C_OrderLine_ID = GetC_OrderLine_ID();
String costingMethod = null;
Decimal? costs = pc.GetProductCosts(as1, AD_Org_ID, costingMethod, C_OrderLine_ID, zeroCostsOK);
if (costs != null)
{
return(costs.Value);
}
return(Env.ZERO);
}
public JsonResult PostByNewLogic(int AD_Client_ID)
{
Ctx ctx = Session["ctx"] as Ctx;
string res = "No";
MClientInfo info = MClientInfo.Get(ctx, AD_Client_ID);
MAcctSchema ass = new MAcctSchema(ctx, info.GetC_AcctSchema1_ID(), null);
if (ass.GetFRPT_LocAcct_ID() > 0)
{
res = "Yes";
}
return(Json(new { result = res }, JsonRequestBehavior.AllowGet));
}
/// <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>
/// Get PO Costs in Currency of AcctSchema
/// </summary>
/// <param name="as1"></param>
/// <returns>Unit PO Cost</returns>
private Decimal?GetPOCost(MAcctSchema as1)
{
Decimal?retValue = null;
// Uses PO Date
String sql = "SELECT currencyConvert(ol.PriceActual, o.C_Currency_ID, @param1, o.DateOrdered, o.C_ConversionType_ID, @param2, @param3) "
+ "FROM C_OrderLine ol"
+ " INNER JOIN M_InOutLine iol ON (iol.C_OrderLine_ID=ol.C_OrderLine_ID)"
+ " INNER JOIN C_Order o ON (o.C_Order_ID=ol.C_Order_ID) "
+ "WHERE iol.M_InOutLine_ID=@param4";
IDataReader idr = null;
try
{
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@param1", as1.GetC_Currency_ID());
param[1] = new SqlParameter("@param2", GetAD_Client_ID());
param[2] = new SqlParameter("@param3", GetAD_Org_ID());
param[3] = new SqlParameter("@param4", _issue.GetM_InOutLine_ID());
idr = DataBase.DB.ExecuteReader(sql, param, null);
if (idr.Read())
{
retValue = Utility.Util.GetValueOfDecimal(idr[0]);///.getBigDecimal(1);
log.Fine("POCost = " + retValue);
}
else
{
log.Warning("Not found for M_InOutLine_ID=" + _issue.GetM_InOutLine_ID());
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
log.Log(Level.SEVERE, sql, e);
}
return(retValue);
}
/**
* Update Balance of Client
* @param ctx context
* @param deleteFirst delete first
* @param dateFrom null for all or first date to delete/calculate
* @param trx transaction
* @param svrPrc optional server process
* @return Info
*/
public static String UpdateBalanceClient(Ctx ctx,
DateTime?dateFrom, Trx trx, int Fact_Accumulation_ID, SvrProcess svrPrc)
{
int AD_Client_ID = ctx.GetAD_Client_ID();
StringBuilder Info = new StringBuilder();
MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, AD_Client_ID);
foreach (MAcctSchema as1 in ass)
{
if (Info.Length > 0)
{
Info.Append(" - ");
}
String msg = UpdateBalance(ctx, as1.GetC_AcctSchema_ID(),
dateFrom, trx, Fact_Accumulation_ID, svrPrc);
Info.Append(as1.GetName()).Append(":").Append(msg);
}
return(Info.ToString());
}
/// <summary>
/// Line Account from Product (or Charge).
/// </summary>
/// <param name="AcctType"></param>
/// <param name="as1"></param>
/// <returns>Requested Product Account</returns>
public MAccount GetAccount(int AcctType, MAcctSchema as1)
{
// Charge Account
if (GetM_Product_ID() == 0 && GetC_Charge_ID() != 0)
{
Decimal amt = new Decimal(-1); // Revenue (-)
if (!_doc.IsSOTrx())
{
amt = new Decimal(+1); // Expense (+)
}
MAccount acct = GetChargeAccount(as1, amt);
if (acct != null)
{
return(acct);
}
}
// Product Account
return(GetProductCost().GetAccount(AcctType, as1));
}
/// <summary>
/// Update ProductPO PriceLastInv
/// </summary>
/// <param name="as1">accounting schema</param>
private void UpdateProductPO(MAcctSchema as1)
{
MClientInfo ci = MClientInfo.Get(GetCtx(), as1.GetAD_Client_ID());
if (ci.GetC_AcctSchema1_ID() != as1.GetC_AcctSchema_ID())
{
return;
}
StringBuilder sql = new StringBuilder(
"UPDATE M_Product_PO po "
+ "SET PriceLastInv = "
// select
+ "(SELECT currencyConvert(il.PriceActual,i.C_Currency_ID,po.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID) "
+ "FROM C_Invoice i, C_InvoiceLine il "
+ "WHERE i.C_Invoice_ID=il.C_Invoice_ID"
+ " AND po.M_Product_ID=il.M_Product_ID AND po.C_BPartner_ID=i.C_BPartner_ID");
//jz + " AND ROWNUM=1 AND i.C_Invoice_ID=").Append(get_ID()).Append(") ")
if (DataBase.DB.IsOracle()) //jz
{
sql.Append(" AND ROWNUM=1 ");
}
else
{
sql.Append(" AND i.UPDATED IN (SELECT MAX(i1.UPDATED) "
+ "FROM C_Invoice i1, C_InvoiceLine il1 "
+ "WHERE i1.C_Invoice_ID=il1.C_Invoice_ID"
+ " AND po.M_Product_ID=il1.M_Product_ID AND po.C_BPartner_ID=i1.C_BPartner_ID")
.Append(" AND i1.C_Invoice_ID=").Append(Get_ID()).Append(") ");
}
sql.Append(" AND i.C_Invoice_ID=").Append(Get_ID()).Append(") ")
// update
.Append("WHERE EXISTS (SELECT * "
+ "FROM C_Invoice i, C_InvoiceLine il "
+ "WHERE i.C_Invoice_ID=il.C_Invoice_ID"
+ " AND po.M_Product_ID=il.M_Product_ID AND po.C_BPartner_ID=i.C_BPartner_ID"
+ " AND i.C_Invoice_ID=").Append(Get_ID()).Append(")");
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, GetTrx());
log.Fine("Updated=" + no);
}
/// <summary>
/// Get Commitment Release.
/// Called from MatchInv for accrual and Allocation for Cash Based
/// </summary>
/// <param name="as1">accounting schema</param>
/// <param name="doc">doc</param>
/// <param name="Qty">qty invoiced/matched</param>
/// <param name="C_InvoiceLine_ID">line</param>
/// <param name="multiplier">1 for accrual</param>
/// <returns>Fact</returns>
public static Fact GetCommitmentRelease(MAcctSchema as1, Doc doc,
Decimal Qty, int C_InvoiceLine_ID, Decimal multiplier)
{
Fact fact = new Fact(doc, as1, Fact.POST_Commitment);
DocLine[] commitments = Doc_Order.GetCommitments(doc, Qty, C_InvoiceLine_ID);
Decimal total = Env.ZERO;
int C_Currency_ID = -1;
for (int i = 0; i < commitments.Length; i++)
{
DocLine line = commitments[i];
if (C_Currency_ID == -1)
{
C_Currency_ID = line.GetC_Currency_ID();
}
else if (C_Currency_ID != line.GetC_Currency_ID())
{
doc._error = "Different Currencies of Order Lines";
_log.Log(Level.SEVERE, doc._error);
return(null);
}
Decimal cost = Decimal.Multiply(line.GetAmtSource(), multiplier);
total = Decimal.Add(total, cost);
// Account
MAccount expense = line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1);
fact.CreateLine(line, expense, C_Currency_ID, null, cost);
}
// Offset
MAccount offset = doc.GetAccount(ACCTTYPE_CommitmentOffset, as1);
if (offset == null)
{
doc._error = "@NotFound@ @CommitmentOffset_Acct@";
_log.Log(Level.SEVERE, doc._error);
return(null);
}
fact.CreateLine(null, offset, C_Currency_ID, total, null);
return(fact);
}
/// <summary>
/// Update ProductPO PriceLastPO
/// </summary>
/// <param name="as1">accounting schema</param>
private void UpdateProductPO(MAcctSchema as1)
{
MClientInfo ci = MClientInfo.Get(GetCtx(), as1.GetAD_Client_ID());
if (ci.GetC_AcctSchema1_ID() != as1.GetC_AcctSchema_ID())
{
return;
}
StringBuilder sql = new StringBuilder(
"UPDATE M_Product_PO po "
+ "SET PriceLastPO = (SELECT currencyConvert(ol.PriceActual,ol.C_Currency_ID,po.C_Currency_ID,o.DateOrdered,o.C_ConversionType_ID,o.AD_Client_ID,o.AD_Org_ID) "
+ "FROM C_Order o, C_OrderLine ol "
+ "WHERE o.C_Order_ID=ol.C_Order_ID"
+ " AND po.M_Product_ID=ol.M_Product_ID AND po.C_BPartner_ID=o.C_BPartner_ID");
// AND ROWNUM=1 AND o.C_Order_ID=").Append(get_ID()).Append(") ")
if (DataBase.DB.IsOracle()) //jz
{
sql.Append(" AND ROWNUM=1 ");
}
else
{
sql.Append(" AND o.UPDATED IN (SELECT MAX(o1.UPDATED) "
+ "FROM C_Order o1, C_OrderLine ol1 "
+ "WHERE o1.C_Order_ID=ol1.C_Order_ID"
+ " AND po.M_Product_ID=ol1.M_Product_ID AND po.C_BPartner_ID=o1.C_BPartner_ID")
.Append(" AND o1.C_Order_ID=").Append(Get_ID()).Append(") ");
}
sql.Append(" AND o.C_Order_ID=").Append(Get_ID()).Append(") ")
.Append("WHERE EXISTS (SELECT * "
+ "FROM C_Order o, C_OrderLine ol "
+ "WHERE o.C_Order_ID=ol.C_Order_ID"
+ " AND po.M_Product_ID=ol.M_Product_ID AND po.C_BPartner_ID=o.C_BPartner_ID"
+ " AND o.C_Order_ID=").Append(Get_ID()).Append(")");
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, GetTrx());
log.Fine("Updated=" + no);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// GLJ.
/// (only for the accounting scheme, it was created)
/// <pre>
/// account DR CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
List <Fact> facts = new List <Fact>();
// Other Acct Schema
// need to Post GL Journal for Multiple Accounting Schema that's why commented this section
//if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID)
//{
// return facts;
//}
// create Fact Header
Fact fact = new Fact(this, as1, _PostingType);
// GLJ
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_GLJOURNAL))
{
// account DR CR
for (int i = 0; i < _lines.Length; i++)
{
// need to Post GL Journal for Multiple Accounting Schema that's why commented this condition
//if (_lines[i].GetC_AcctSchema_ID() == as1.GetC_AcctSchema_ID())
//{
fact.CreateLine(_lines[i],
_lines[i].GetAccount(),
GetC_Currency_ID(),
_lines[i].GetAmtSourceDr(),
_lines[i].GetAmtSourceCr());
//}
} // for all lines
}
else
{
_error = "DocumentType unknown: " + GetDocumentType();
log.Log(Level.SEVERE, _error);
fact = null;
}
//
facts.Add(fact);
return(facts);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// </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_INCOMETAX))
{
// Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross);
// Commitment
Fact fact = new Fact(this, as1, Fact.POST_Actual);
Decimal total = Env.ZERO;
Decimal amount = Env.ZERO;
for (int i = 0; i < _lines.Length; i++)
{
DocLine dline = _lines[i];
MIncomeTaxLines line = new MIncomeTaxLines(GetCtx(), dline.Get_ID(), null);
amount = Util.GetValueOfDecimal(line.GetIncomeTaxAmount());
if (amount != Env.ZERO)
{
total = Decimal.Add(total, amount);
}
// Account
MAccount expense = MAccount.Get(GetCtx(), line.GetC_IncomeTax_Acct());
fact.CreateLine(dline, expense, GetC_Currency_ID(), amount);
}
if (total != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT T_Due_Acct FROM C_Tax_Acct 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(), Decimal.Negate(total));
}
facts.Add(fact);
}
return(facts);
}
/// <summary>
/// Create Facts (the accounting logic) for POR.
/// <pre>
/// Reservation
/// Expense CR
/// Offset DR
/// </pre>
/// </summary>
/// <param name="as1"></param>
/// <returns>fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
List <Fact> facts = new List <Fact>();
Fact fact = new Fact(this, as1, Fact.POST_Reservation);
SetC_Currency_ID(as1.GetC_Currency_ID());
//
// Decimal grossAmt =
GetAmount(Doc.AMTTYPE_Gross);
// Commitment
if (as1.IsCreateReservation())
{
Decimal total = Env.ZERO;
for (int i = 0; i < _lines.Length; i++)
{
DocLine line = _lines[i];
Decimal cost = line.GetAmtSource();
total = Decimal.Add(total, cost);
// Account
MAccount expense = line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1);
//
fact.CreateLine(line, expense, as1.GetC_Currency_ID(), cost, null);
}
// Offset
MAccount offset = GetAccount(ACCTTYPE_CommitmentOffset, as1);
if (offset == null)
{
_error = "@NotFound@ @CommitmentOffset_Acct@";
log.Log(Level.SEVERE, _error);
return(null);
}
fact.CreateLine(null, offset, GetC_Currency_ID(), null, total);
facts.Add(fact);
}
return(facts);
}
/// <summary>
/// Get Account
/// </summary>
/// <param name="AcctType"></param>
/// <param name="as1"></param>
/// <returns>Account</returns>
public MAccount GetAccount(int AcctType, MAcctSchema as1)
{
if (AcctType < 0 || AcctType > 4)
{
return(null);
}
//
String sql = "SELECT T_Due_Acct, T_Liability_Acct, T_Credit_Acct, T_Receivables_Acct, T_Expense_Acct "
+ "FROM C_Tax_Acct WHERE C_Tax_ID=" + _C_Tax_ID + " AND C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID();
int validCombination_ID = 0;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
if (idr.Read())
{
validCombination_ID = Utility.Util.GetValueOfInt(idr[AcctType]);// .getInt(AcctType + 1); // 1..5
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
log.Log(Level.SEVERE, sql, e);
}
if (validCombination_ID == 0)
{
return(null);
}
return(MAccount.Get(as1.GetCtx(), validCombination_ID));
}
/// <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>
/// Load Invoice Line
/// </summary>
/// <param name="journal"></param>
/// <returns>DocLine Array</returns>
private DocLine[] LoadLines(MJournal journal)
{
MAcctSchema mSc = new MAcctSchema(GetCtx(), _C_AcctSchema_ID, null);
List <DocLine> list = new List <DocLine>();
MJournalLine[] lines = journal.GetLines(false);
record_Id = lines[0].GetGL_Journal_ID();
for (int i = 0; i < lines.Length; i++)
{
MJournalLine line = lines[i];
if (line.GetElementType() == null)
{
DocLine docLine = new DocLine(line, this);
// -- Source Amounts
docLine.SetAmount(line.GetAmtAcctDr(), line.GetAmtAcctCr());
// -- Converted Amounts
// no need to update converted amount here
//docLine.SetConvertedAmt(_C_AcctSchema_ID, line.GetAmtAcctDr(), line.GetAmtAcctCr());
// -- Account
MAccount account = line.GetAccount();
docLine.SetAccount(account);
// -- Quantity
docLine.SetQty(line.GetQty(), false);
// -- Date
docLine.SetDateAcct(journal.GetDateAcct());
// -- Organization of Line was set to Org of Account
list.Add(docLine);
}
else
{
string sql = "SELECT * FROM GL_LineDimension WHERE GL_JournalLine_ID=" + line.Get_ID();
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int m = 0; m < ds.Tables[0].Rows.Count; m++)
{
DataRow dr = ds.Tables[0].Rows[m];
X_GL_LineDimension lDim = new X_GL_LineDimension(GetCtx(), dr, null);
DocLine docLine = new DocLine(lDim, this);
// -- Source Amounts
decimal cRate = line.GetCurrencyRate();
if (cRate == 0)
{
cRate = 1;
}
decimal amtAcctCr = 0;
decimal amtAcctDr = 0;
//MAcctSchema mSc = new MAcctSchema(GetCtx(), _C_AcctSchema_ID, null);
if (line.GetAmtSourceDr() != 0)
{
amtAcctDr = lDim.GetAmount() * cRate;
docLine.SetAmount(amtAcctDr, 0);
amtAcctDr = Decimal.Round(amtAcctDr, mSc.GetStdPrecision());
}
else
{
amtAcctCr = lDim.GetAmount() * cRate;
docLine.SetAmount(0, amtAcctCr);
amtAcctCr = Decimal.Round(amtAcctCr, mSc.GetStdPrecision());
}
// -- Converted Amounts
// no need to update converted amount here
//docLine.SetConvertedAmt(_C_AcctSchema_ID, amtAcctDr, amtAcctCr);
// -- Account
MAccount account = line.GetAccount();
docLine.SetAccount(account);
// -- Quantity
docLine.SetQty(lDim.GetQty(), false);
// -- Date
docLine.SetDateAcct(journal.GetDateAcct());
// -- User Dimension
docLine = SetUserDimension(lDim, docLine);
// -- Organization of Line was set to Org of Account
list.Add(docLine);
}
}
}
}
// Return Array
int size = list.Count;
DocLine[] dls = new DocLine[size];
dls = list.ToArray();
return(dls);
}
