/// <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>
/// 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>
/// 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 Landed Cost accounting & Cost lines
/// </summary>
/// <param name="as1">accounting schema</param>
/// <param name="fact">fact</param>
/// <param name="line">document line</param>
/// <param name="dr">DR entry (normal api)</param>
/// <returns>true if landed costs were created</returns>
private bool LandedCost(MAcctSchema as1, Fact fact, DocLine line, bool dr)
{
int C_InvoiceLine_ID = line.Get_ID();
MLandedCostAllocation[] lcas = MLandedCostAllocation.GetOfInvoiceLine(
GetCtx(), C_InvoiceLine_ID, GetTrx());
if (lcas.Length == 0)
{
return(false);
}
// Delete Old
String sql = "DELETE FROM M_CostDetail WHERE C_InvoiceLine_ID=" + C_InvoiceLine_ID;
int no = DataBase.DB.ExecuteQuery(sql, null, GetTrx());
if (no != 0)
{
log.Config("CostDetail Deleted #" + no);
}
// Calculate Total Base
double totalBase = 0;
for (int i = 0; i < lcas.Length; i++)
{
totalBase += Convert.ToDouble(lcas[i].GetBase());//.doubleValue();
}
// Create New
MInvoiceLine il = new MInvoiceLine(GetCtx(), C_InvoiceLine_ID, GetTrx());
for (int i = 0; i < lcas.Length; i++)
{
MLandedCostAllocation lca = lcas[i];
if (Env.Signum(lca.GetBase()) == 0)
{
continue;
}
double percent = totalBase / Convert.ToDouble(lca.GetBase());
String desc = il.GetDescription();
if (desc == null)
{
desc = percent + "%";
}
else
{
desc += " - " + percent + "%";
}
if (line.GetDescription() != null)
{
desc += " - " + line.GetDescription();
}
// Accounting
ProductCost pc = new ProductCost(GetCtx(),
lca.GetM_Product_ID(), lca.GetM_AttributeSetInstance_ID(), GetTrx());
Decimal?drAmt = null;
Decimal?crAmt = null;
if (dr)
{
drAmt = lca.GetAmt();
}
else
{
crAmt = lca.GetAmt();
}
FactLine fl = fact.CreateLine(line, pc.GetAccount(ProductCost.ACCTTYPE_P_CostAdjustment, as1),
GetC_Currency_ID(), drAmt, crAmt);
fl.SetDescription(desc);
// Cost Detail - Convert to AcctCurrency
Decimal allocationAmt = lca.GetAmt();
if (GetC_Currency_ID() != as1.GetC_Currency_ID())
{
allocationAmt = MConversionRate.Convert(GetCtx(), allocationAmt,
GetC_Currency_ID(), as1.GetC_Currency_ID(),
GetDateAcct(), GetC_ConversionType_ID(),
GetAD_Client_ID(), GetAD_Org_ID());
}
if (Env.Scale(allocationAmt) > as1.GetCostingPrecision())
{
allocationAmt = Decimal.Round(allocationAmt, as1.GetCostingPrecision(), MidpointRounding.AwayFromZero);
}
if (!dr)
{
allocationAmt = Decimal.Negate(allocationAmt);
}
if (!IsPosted())
{
MCostDetail cd = new MCostDetail(as1, lca.GetAD_Org_ID(),
lca.GetM_Product_ID(), lca.GetM_AttributeSetInstance_ID(),
lca.GetM_CostElement_ID(),
allocationAmt, lca.GetQty(), // Qty
desc, GetTrx());
cd.SetC_InvoiceLine_ID(C_InvoiceLine_ID);
bool ok = cd.Save();
if (ok && !cd.IsProcessed())
{
MClient client = MClient.Get(as1.GetCtx(), as1.GetAD_Client_ID());
if (client.IsCostImmediate())
{
cd.Process();
}
}
}
}
log.Config("Created #" + lcas.Length);
return(true);
}
} // copyDefault
/// <summary>
/// Create Account
/// </summary>
/// <param name="targetAS">target AS</param>
/// <param name="sourceAcct">source account</param>
/// <returns>target account</returns>
private MAccount CreateAccount(MAcctSchema targetAS, MAccount sourceAcct)
{
int AD_Client_ID = targetAS.GetAD_Client_ID();
int C_AcctSchema_ID = targetAS.GetC_AcctSchema_ID();
//
int AD_Org_ID = 0;
int Account_ID = 0;
int C_SubAcct_ID = 0;
int M_Product_ID = 0;
int C_BPartner_ID = 0;
int AD_OrgTrx_ID = 0;
int C_LocFrom_ID = 0;
int C_LocTo_ID = 0;
int C_SalesRegion_ID = 0;
int C_Project_ID = 0;
int C_Campaign_ID = 0;
int C_Activity_ID = 0;
int User1_ID = 0;
int User2_ID = 0;
int UserElement1_ID = 0;
int UserElement2_ID = 0;
//
// Active Elements
MAcctSchemaElement[] elements = targetAS.GetAcctSchemaElements();
for (int i = 0; i < elements.Length; i++)
{
MAcctSchemaElement ase = elements[i];
String elementType = ase.GetElementType();
//
if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Organization))
{
AD_Org_ID = sourceAcct.GetAD_Org_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Account))
{
Account_ID = sourceAcct.GetAccount_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_SubAccount))
{
C_SubAcct_ID = sourceAcct.GetC_SubAcct_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_BPartner))
{
C_BPartner_ID = sourceAcct.GetC_BPartner_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Product))
{
M_Product_ID = sourceAcct.GetM_Product_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Activity))
{
C_Activity_ID = sourceAcct.GetC_Activity_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_LocationFrom))
{
C_LocFrom_ID = sourceAcct.GetC_LocFrom_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_LocationTo))
{
C_LocTo_ID = sourceAcct.GetC_LocTo_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Campaign))
{
C_Campaign_ID = sourceAcct.GetC_Campaign_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_OrgTrx))
{
AD_OrgTrx_ID = sourceAcct.GetAD_OrgTrx_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Project))
{
C_Project_ID = sourceAcct.GetC_Project_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_SalesRegion))
{
C_SalesRegion_ID = sourceAcct.GetC_SalesRegion_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserList1))
{
User1_ID = sourceAcct.GetUser1_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserList2))
{
User2_ID = sourceAcct.GetUser2_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserElement1))
{
UserElement1_ID = sourceAcct.GetUserElement1_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserElement2))
{
UserElement2_ID = sourceAcct.GetUserElement2_ID();
}
// No UserElement
}
//
return(MAccount.Get(GetCtx(), AD_Client_ID, AD_Org_ID,
C_AcctSchema_ID, Account_ID, C_SubAcct_ID,
M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID,
C_Project_ID, C_Campaign_ID, C_Activity_ID,
User1_ID, User2_ID, UserElement1_ID, UserElement2_ID));
} // createAccount