/**
* Parent Constructor (old)
* @param product parent
* @param C_AcctSchema_ID accounting schema
*/
public MProductCosting(MProduct product, int C_AcctSchema_ID)
: base(product.GetCtx(), 0, product.Get_TrxName())
{
SetClientOrg(product);
SetM_Product_ID(product.GetM_Product_ID());
SetC_AcctSchema_ID(C_AcctSchema_ID);
}
/// <summary>
/// Get BOM Lines for Product. Default to Current Active, Master BOM
/// BOM Lines are Ordered By Ascending Order of Product Names.
/// </summary>
/// <param name="product">product</param>
/// <param name="bomType">bomtype</param>
/// <param name="bomUse">bomuse</param>
/// <param name="isAscending">true if ascending, false if descending</param>
/// <returns>array of BOMs</returns>
/// <writer>raghu</writer>
/// <date>08-march-2011</date>
public static MBOMProduct[] GetBOMLinesOrderByProductName(MProduct product, String bomType, String bomUse, Boolean isAscending)
{
// return lines for Current Active, Master BOM
String sql = "SELECT M_BOM_ID FROM M_BOM WHERE M_Product_ID= " + product.GetM_Product_ID() +
"AND BOMType ='" + bomType + "' AND BOMUse ='" + bomUse + "' AND IsActive = 'Y'";
Trx trx = product.Get_Trx();
int bomID = 0;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, trx);
if (idr.Read())
{
bomID = Util.GetValueOfInt(idr[0]);
}
idr.Close();
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
finally
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
return(GetBOMLinesOrderByProductName(MBOM.Get(product.GetCtx(), bomID), isAscending));
}
/// <summary>
/// Get Cost Queue Records in Lifo/Fifo order
/// </summary>
/// <param name="product">product</param>
/// <param name="M_ASI_ID">costing level ASI</param>
/// <param name="mas">accounting schema</param>
/// <param name="Org_ID">costing level org</param>
/// <param name="ce">Cost Element</param>
/// <param name="trxName">transaction</param>
/// <returns>cost queue or null</returns>
public static MCostQueue[] GetQueue(MProduct product, int M_ASI_ID, MAcctSchema mas,
int Org_ID, MCostElement ce, Trx trxName)
{
List <MCostQueue> list = new List <MCostQueue>();
String sql = "SELECT * FROM M_CostQueue "
+ "WHERE AD_Client_ID=@client AND AD_Org_ID=@org"
+ " AND M_Product_ID=@prod"
+ " AND M_CostType_ID=@ct AND C_AcctSchema_ID=@accs"
+ " AND M_CostElement_ID=@ce";
if (M_ASI_ID != 0)
{
sql += " AND M_AttributeSetInstance_ID=@asi";
}
sql += " AND CurrentQty<>0 "
+ "ORDER BY M_AttributeSetInstance_ID ";
if (!ce.IsFifo())
{
sql += "DESC";
}
try
{
SqlParameter[] param = null;
if (M_ASI_ID != 0)
{
param = new SqlParameter[7];
}
else
{
param = new SqlParameter[6];
}
param[0] = new SqlParameter("@client", product.GetAD_Client_ID());
param[1] = new SqlParameter("@org", Org_ID);
param[2] = new SqlParameter("@prod", product.GetM_Product_ID());
param[3] = new SqlParameter("@ct", mas.GetM_CostType_ID());
param[4] = new SqlParameter("@accs", mas.GetC_AcctSchema_ID());
param[5] = new SqlParameter("@ce", ce.GetM_CostElement_ID());
if (M_ASI_ID != 0)
{
param[6] = new SqlParameter("@asi", M_ASI_ID);
}
DataSet ds = DataBase.DB.ExecuteDataset(sql, param, trxName);
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(new MCostQueue(product.GetCtx(), dr, trxName));
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
MCostQueue[] costQ = new MCostQueue[list.Count];
costQ = list.ToArray();
return(costQ);
}
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="parent"></param>
public MUOMConversion(MProduct parent)
: this(parent.GetCtx(), 0, parent.Get_TrxName())
{
SetClientOrg(parent);
SetC_UOM_ID(parent.GetC_UOM_ID());
SetM_Product_ID(parent.GetM_Product_ID());
SetC_UOM_To_ID(parent.GetC_UOM_ID());
SetMultiplyRate(Env.ONE);
SetDivideRate(Env.ONE);
}
public MCostForeignCurrency(int AD_Org_ID, MProduct product, int M_AttributeSetInstance_ID,
int M_CostElement_ID, int C_BPartner_ID, int C_Currency_ID)
: this(product.GetCtx(), 0, product.Get_TrxName())
{
SetClientOrg(product.GetAD_Client_ID(), AD_Org_ID);
SetM_Product_ID(product.GetM_Product_ID());
SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
SetM_CostElement_ID(M_CostElement_ID);
SetC_BPartner_ID(C_BPartner_ID);
SetC_Currency_ID(C_Currency_ID);
}
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="product">product</param>
/// <param name="M_AttributeSetInstance_ID">Attribute Set Instance</param>
/// <param name="mas">Acct Schema</param>
/// <param name="AD_Org_ID">org</param>
/// <param name="M_CostElement_ID">cost element</param>
/// <param name="trxName">transaction</param>
public MCostQueue(MProduct product, int M_AttributeSetInstance_ID,
MAcctSchema mas, int AD_Org_ID, int M_CostElement_ID, Trx trxName)
: this(product.GetCtx(), 0, trxName)
{
SetClientOrg(product.GetAD_Client_ID(), AD_Org_ID);
SetC_AcctSchema_ID(mas.GetC_AcctSchema_ID());
SetM_CostType_ID(mas.GetM_CostType_ID());
SetM_Product_ID(product.GetM_Product_ID());
SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
SetM_CostElement_ID(M_CostElement_ID);
}
/// <summary>
/// Is used to check costing method belongs to PO costing method like (Average PO, Weighted Average PO or Last PO)
/// Firts we check costing method on Product category, if not found then we will check on Primary Accounting Schema
/// </summary>
/// <param name="ctx">current context</param>
/// <param name="AD_Client_ID">Client reference</param>
/// <param name="M_Product_ID">Product whom costing method is to be determine</param>
/// <param name="trxName">Transaction</param>
/// <returns>True/False</returns>
public static bool IsPOCostingmethod(Ctx ctx, int AD_Client_ID, int M_Product_ID, Trx trxName)
{
MProductCategory pc = null;
bool isPOcostingMethod = false;
string costingMethod = null;
MClient client = MClient.Get(ctx, AD_Client_ID);
MProduct product = MProduct.Get(ctx, M_Product_ID);
if (product != null)
{
pc = MProductCategory.Get(product.GetCtx(), product.GetM_Product_Category_ID());
if (pc != null)
{
// check costing method from product category
costingMethod = pc.GetCostingMethod();
if (costingMethod == "C")
{
costingMethod = Util.GetValueOfString(DB.ExecuteScalar(@"SELECT costingmethod FROM M_CostElement WHERE M_CostElement_ID IN
(SELECT CAST(M_Ref_CostElement AS INTEGER) FROM M_CostElementLine WHERE M_CostElement_ID=" + pc.GetM_CostElement_ID() + @" )
AND CostingMethod IS NOT NULL", null, trxName));
}
}
if (String.IsNullOrEmpty(costingMethod))
{
// check costing method against primary accounting schema
MClientInfo clientInfo = MClientInfo.Get(ctx, AD_Client_ID);
MAcctSchema actSchema = MAcctSchema.Get(ctx, clientInfo.GetC_AcctSchema1_ID());
if (actSchema != null)
{
costingMethod = actSchema.GetCostingMethod();
if (costingMethod == "C")
{
costingMethod = Util.GetValueOfString(DB.ExecuteScalar(@"SELECT costingmethod FROM M_CostElement WHERE M_CostElement_ID IN
(SELECT CAST(M_Ref_CostElement AS INTEGER) FROM M_CostElementLine WHERE M_CostElement_ID=" + actSchema.GetM_CostElement_ID() + @" )
AND CostingMethod IS NOT NULL", null, trxName));
}
}
}
}
if (costingMethod.Equals(COSTINGMETHOD_WeightedAveragePO) ||
costingMethod.Equals(COSTINGMETHOD_AveragePO) ||
costingMethod.Equals(COSTINGMETHOD_LastPOPrice))
{
isPOcostingMethod = true;
}
else
{
isPOcostingMethod = false;
}
return(isPOcostingMethod);
}
public static MCostForeignCurrency Get(MProduct product, int M_AttributeSetInstance_ID, int AD_Org_ID, int M_CostElement_ID,
int C_BPartner_ID, int C_Currency_ID)
{
MCostForeignCurrency foreignCurrency = null;
String sql = "SELECT * "
+ "FROM M_Cost_ForeignCurrency c "
+ "WHERE AD_Client_ID=" + product.GetAD_Client_ID() + " AND AD_Org_ID=" + AD_Org_ID
+ " AND M_Product_ID=" + product.GetM_Product_ID()
+ " AND NVL(M_AttributeSetInstance_ID , 0) =" + M_AttributeSetInstance_ID
+ " AND M_CostElement_ID=" + M_CostElement_ID
+ " AND C_BPartner_ID = " + C_BPartner_ID
+ " AND C_Currency_ID = " + C_Currency_ID;
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, product.Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
foreignCurrency = new MCostForeignCurrency(product.GetCtx(), dr, product.Get_TrxName());
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally { dt = null; }
// New
if (foreignCurrency == null)
{
foreignCurrency = new MCostForeignCurrency(AD_Org_ID, product, M_AttributeSetInstance_ID,
M_CostElement_ID, C_BPartner_ID, C_Currency_ID);
}
return(foreignCurrency);
}
/// <summary>
/// Get/Create Cost Queue Record.
/// CostingLevel is not validated
/// </summary>
/// <param name="product">product</param>
/// <param name="M_AttributeSetInstance_ID">real asi</param>
/// <param name="mas">accounting schema</param>
/// <param name="AD_Org_ID">real org</param>
/// <param name="M_CostElement_ID">element</param>
/// <param name="trxName">transaction</param>
/// <returns>cost queue or null</returns>
public static MCostQueue Get(MProduct product, int M_AttributeSetInstance_ID,
MAcctSchema mas, int AD_Org_ID, int M_CostElement_ID, Trx trxName)
{
MCostQueue costQ = null;
String sql = "SELECT * FROM M_CostQueue "
+ "WHERE AD_Client_ID=@client AND AD_Org_ID=@org"
+ " AND M_Product_ID=@pro"
+ " AND M_AttributeSetInstance_ID=@asi"
+ " AND M_CostType_ID=@ct AND C_AcctSchema_ID=@accs"
+ " AND M_CostElement_ID=@ce";
try
{
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@client", product.GetAD_Client_ID());
param[1] = new SqlParameter("@org", AD_Org_ID);
param[2] = new SqlParameter("@pro", product.GetM_Product_ID());
param[3] = new SqlParameter("@asi", M_AttributeSetInstance_ID);
param[4] = new SqlParameter("@ct", mas.GetM_CostType_ID());
param[5] = new SqlParameter("@accs", mas.GetC_AcctSchema_ID());
param[6] = new SqlParameter("@ce", M_CostElement_ID);
DataSet ds = DataBase.DB.ExecuteDataset(sql, param);
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
costQ = new MCostQueue(product.GetCtx(), dr, trxName);
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
// New
if (costQ == null)
{
costQ = new MCostQueue(product, M_AttributeSetInstance_ID,
mas, AD_Org_ID, M_CostElement_ID, trxName);
}
return(costQ);
}
/**
* Get array of active Locators for Product and warehouse ordered by priority
* @param product product
* @param M_Warehouse_ID wh
* @return product locators
*/
public static MLocator[] GetLocators(MProduct product, int M_Warehouse_ID)
{
List <MLocator> list = new List <MLocator>();
String sql = "SELECT * FROM M_Locator l "
+ "WHERE l.IsActive='Y'"
+ " AND (M_Locator_ID IN (SELECT M_Locator_ID FROM M_Product WHERE M_Product_ID=" + product.GetM_Product_ID() + ")"
+ " OR M_Locator_ID IN (SELECT M_Locator_ID FROM M_ProductLocator WHERE M_Product_ID=" + product.GetM_Product_ID() + " AND IsActive='Y'))"
+ " AND M_Warehouse_ID=" + M_Warehouse_ID
+ "ORDER BY PriorityNo DESC";
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, product.Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
list.Add(new MLocator(product.GetCtx(), dr, product.Get_TrxName()));
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally {
if (idr != null)
{
idr.Close();
}
dt = null;
}
MLocator[] retValue = new MLocator[list.Count];
retValue = list.ToArray();
return(retValue);
}
public static bool InsertForeignCostMatchOrder(Ctx ctx, MOrderLine orderLine, decimal matchQty, int ASI, Trx trx)
{
int acctSchema_ID = 0;
int M_CostElement_ID = 0;
int AD_Org_ID = 0;
int M_ASI_ID = 0;
MProduct product = null;
MAcctSchema acctSchema = null;
MCostForeignCurrency foreignCost = null;
dynamic pc = null;
String cl = null;
MOrder order = null;
try
{
order = new MOrder(ctx, orderLine.GetC_Order_ID(), trx);
if (!order.IsSOTrx() && !order.IsReturnTrx())
{
acctSchema_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT asch.c_acctschema_id FROM c_acctschema asch INNER JOIN ad_clientinfo ci
ON ci.c_acctschema1_id = asch.c_acctschema_id WHERE ci.ad_client_id = " + order.GetAD_Client_ID()));
acctSchema = new MAcctSchema(ctx, acctSchema_ID, trx);
if (acctSchema.GetC_Currency_ID() != order.GetC_Currency_ID())
{
// Get Costing Element of Av. PO
M_CostElement_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT M_CostElement_ID FROM M_CostElement WHERE AD_Client_ID = "
+ order.GetAD_Client_ID() + " AND IsActive = 'Y' AND CostingMethod = 'A'"));
product = new MProduct(ctx, orderLine.GetM_Product_ID(), trx);
if (product != null && product.GetProductType() == "I" && product.GetM_Product_ID() > 0) // for Item Type product
{
pc = MProductCategory.Get(product.GetCtx(), product.GetM_Product_Category_ID());
// Get Costing Level
if (pc != null)
{
cl = pc.GetCostingLevel();
}
if (cl == null)
{
cl = acctSchema.GetCostingLevel();
}
if (cl == "C" || cl == "B")
{
AD_Org_ID = 0;
}
else
{
AD_Org_ID = order.GetAD_Org_ID();
}
if (cl != "B")
{
M_ASI_ID = 0;
}
else
{
M_ASI_ID = ASI;
}
foreignCost = MCostForeignCurrency.Get(product, M_ASI_ID, AD_Org_ID, M_CostElement_ID, order.GetC_BPartner_ID(), order.GetC_Currency_ID());
foreignCost.SetC_Order_ID(order.GetC_Order_ID());
foreignCost.SetCumulatedQty(Decimal.Add(foreignCost.GetCumulatedQty(), matchQty));
foreignCost.SetCumulatedAmt(Decimal.Add(foreignCost.GetCumulatedAmt(), Decimal.Multiply(orderLine.GetPriceActual(), matchQty)));
if (foreignCost.GetCumulatedQty() != 0)
{
foreignCost.SetCostPerUnit(Decimal.Round(Decimal.Divide(foreignCost.GetCumulatedAmt(), foreignCost.GetCumulatedQty()), acctSchema.GetCostingPrecision()));
}
else
{
foreignCost.SetCostPerUnit(0);
}
if (!foreignCost.Save(trx))
{
ValueNamePair pp = VLogger.RetrieveError();
_log.Severe("Error occured during updating M_Cost_ForeignCurrency. Error name : " + pp.GetName() +
" AND Error Value : " + pp.GetValue() + " , For Invoice line : " + orderLine.GetC_OrderLine_ID() +
" , AND Ad_Client_ID : " + orderLine.GetAD_Client_ID());
return(false);
}
}
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, "", ex);
return(false);
}
return(true);
}
public static bool InsertForeignCostAverageInvoice(Ctx ctx, MInvoice invoice, MInvoiceLine invoiceLine, Trx trx)
{
int acctSchema_ID = 0;
int M_CostElement_ID = 0;
int AD_Org_ID = 0;
int M_ASI_ID = 0;
MProduct product = null;
MAcctSchema acctSchema = null;
MCostForeignCurrency foreignCost = null;
dynamic pc = null;
String cl = null;
try
{
// if cost is calculated then not to calculate again
if (invoiceLine.IsFutureCostCalculated())
{
return(true);
}
acctSchema_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT asch.c_acctschema_id FROM c_acctschema asch INNER JOIN ad_clientinfo ci
ON ci.c_acctschema1_id = asch.c_acctschema_id WHERE ci.ad_client_id = " + invoice.GetAD_Client_ID()));
acctSchema = new MAcctSchema(ctx, acctSchema_ID, trx);
if (acctSchema.GetC_Currency_ID() != invoice.GetC_Currency_ID())
{
// Get Costing Element of Av. Invoice
M_CostElement_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT M_CostElement_ID FROM M_CostElement WHERE AD_Client_ID = "
+ invoice.GetAD_Client_ID() + " AND IsActive = 'Y' AND CostingMethod = 'I'"));
product = new MProduct(ctx, invoiceLine.GetM_Product_ID(), trx);
if (product != null && product.GetProductType() == "I" && product.GetM_Product_ID() > 0) // for Item Type product
{
pc = MProductCategory.Get(product.GetCtx(), product.GetM_Product_Category_ID());
// Get Costing Level
if (pc != null)
{
cl = pc.GetCostingLevel();
}
if (cl == null)
{
cl = acctSchema.GetCostingLevel();
}
if (cl == "C" || cl == "B")
{
AD_Org_ID = 0;
}
else
{
AD_Org_ID = invoice.GetAD_Org_ID();
}
if (cl != "B")
{
M_ASI_ID = 0;
}
else
{
M_ASI_ID = invoiceLine.GetM_AttributeSetInstance_ID();
}
foreignCost = MCostForeignCurrency.Get(product, M_ASI_ID, AD_Org_ID, M_CostElement_ID, invoice.GetC_BPartner_ID(), invoice.GetC_Currency_ID());
foreignCost.SetC_Invoice_ID(invoice.GetC_Invoice_ID());
foreignCost.SetCumulatedQty(Decimal.Add(foreignCost.GetCumulatedQty(), invoiceLine.GetQtyInvoiced()));
foreignCost.SetCumulatedAmt(Decimal.Add(foreignCost.GetCumulatedAmt(), invoiceLine.GetLineNetAmt()));
if (foreignCost.GetCumulatedQty() != 0)
{
foreignCost.SetCostPerUnit(Decimal.Round(Decimal.Divide(foreignCost.GetCumulatedAmt(), foreignCost.GetCumulatedQty()), acctSchema.GetCostingPrecision()));
}
else
{
foreignCost.SetCostPerUnit(0);
}
if (!foreignCost.Save(trx))
{
ValueNamePair pp = VLogger.RetrieveError();
_log.Severe("Error occured during updating M_Cost_ForeignCurrency. Error name : " + pp.GetName() +
" AND Error Value : " + pp.GetValue() + " , For Invoice line : " + invoiceLine.GetC_InvoiceLine_ID() +
" , AND Ad_Client_ID : " + invoiceLine.GetAD_Client_ID());
return(false);
}
else
{
invoiceLine.SetIsFutureCostCalculated(true);
if (!invoiceLine.Save(trx))
{
ValueNamePair pp = VLogger.RetrieveError();
_log.Severe("Error occured during updating Is Foreign Cost On C_invoice. Error name : " + pp.GetName() +
" AND Error Value : " + pp.GetValue() + " , For Invoice line : " + invoiceLine.GetC_InvoiceLine_ID() +
" , AND Ad_Client_ID : " + invoiceLine.GetAD_Client_ID());
return(false);
}
}
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, "", ex);
return(false);
}
return(true);
}
/**
* Get BOM Lines for Product
* @param product product
* @return array of BOMs
*/
public static MProductBOM[] GetBOMLines(MProduct product)
{
return(GetBOMLines(product.GetCtx(), product.GetM_Product_ID(), product.Get_TrxName()));
}
