public void CheckCosting()
{
log.Info(ToString());
// Create Cost Type
if (GetM_CostType_ID() == 0)
{
MCostType ct = new MCostType(GetCtx(), 0, Get_TrxName());
ct.SetClientOrg(GetAD_Client_ID(), 0);
ct.SetName(GetName());
ct.Save();
SetM_CostType_ID(ct.GetM_CostType_ID());
}
// Create Cost Elements
MCostElement.GetMaterialCostElement(this, GetCostingMethod());
// Default Costing Level
if (GetCostingLevel() == null)
{
SetCostingLevel(COSTINGLEVEL_Client);
}
if (GetCostingMethod() == null)
{
SetCostingMethod(COSTINGMETHOD_StandardCosting);
}
if (GetGAAP() == null)
{
SetGAAP(GAAP_InternationalGAAP);
}
} // checkCosting
/// <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>
/// Check Costing Setup
/// </summary>
public void CheckCosting()
{
// Create Cost Elements
if (GetCostingMethod() != null && GetCostingMethod().Length > 0)
{
MCostElement.GetMaterialCostElement(this, GetCostingMethod());
}
}
/**
* Get Cost Element from Cache
* @param ctx context
* @param M_CostElement_ID id
* @return Cost Element
*/
public static MCostElement Get(Ctx ctx, int M_CostElement_ID)
{
int key = M_CostElement_ID;
MCostElement retValue = (MCostElement)s_cache[key];
if (retValue != null)
{
return(retValue);
}
retValue = new MCostElement(ctx, M_CostElement_ID, null);
if (retValue.Get_ID() != 0)
{
s_cache.Add(key, retValue);
}
return(retValue);
}
protected override bool BeforeSave(bool newRecord)
{
try
{
costElement = new MCostElement(GetCtx(), GetM_Ref_CostElement(), null);
costingMethod = costElement.GetCostingMethod();
if (string.IsNullOrEmpty(costingMethod))
{
if (Util.GetValueOfInt(Get_ValueOld("M_Ref_CostElement")) == GetM_Ref_CostElement())
{
return(true);
}
sql.Clear();
sql.Append(@"SELECT COUNT(*) FROM M_CostElementLine WHERE M_Ref_CostElement = " + GetM_Ref_CostElement()
+ " AND AD_Client_ID = " + GetAD_Client_ID() + " AND M_CostElement_ID = " + GetM_CostElement_ID() + " AND M_CostElementLine_ID != " + GetM_CostElementLine_ID());
countRecord = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null, null));
if (countRecord > 0)
{
log.SaveError("Error", Msg.GetMsg(GetCtx(), "CannotSaveDuplicateRecord"));
return(false);
}
}
else
{
if (Util.GetValueOfInt(Get_ValueOld("M_Ref_CostElement")) == GetM_Ref_CostElement())
{
return(true);
}
sql.Clear();
sql.Append(@"SELECT COUNT(*) FROM M_CostElementLine WHERE AD_Client_ID = " + GetAD_Client_ID()
+ " AND M_CostElement_ID = " + GetM_CostElement_ID()
+ " AND CAST(M_Ref_CostElement AS INTEGER) IN (SELECT M_CostElement_ID FROM M_CostElement WHERE IsActive = 'Y' AND "
+ " CostingMethod IN ('A' , 'F' , 'I' , 'L' , 'S' , 'i' , 'p')) AND M_CostElementLine_ID != " + GetM_CostElementLine_ID());
countRecord = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null, null));
if (countRecord > 0)
{
log.SaveError("Error", Msg.GetMsg(GetCtx(), "CannotSaveMultipleRecordofMaterial"));
return(false);
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql.ToString(), e);
}
return(true);
}
/**
* Get active Material Cost Element for client
* @param po parent
* @return cost element array
*/
public static MCostElement[] GetCostingMethods(PO po)
{
List <MCostElement> list = new List <MCostElement>();
String sql = "SELECT * FROM M_CostElement "
+ "WHERE AD_Client_ID=@Client_ID"
+ " AND IsActive='Y' AND CostElementType='M' AND CostingMethod IS NOT NULL";
DataTable dt = null;
//IDataReader idr = null;
try
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Client_ID", po.GetAD_Client_ID());
//idr = DataBase.DB.ExecuteReader(sql, param, po.Get_TrxName());
DataSet ds = DataBase.DB.ExecuteDataset(sql, param, po.Get_TrxName());
dt = new DataTable();
dt = ds.Tables[0];
//idr.Close();
foreach (DataRow dr in dt.Rows)
{
list.Add(new MCostElement(po.GetCtx(), dr, po.Get_TrxName()));
}
}
catch (Exception e)
{
//if (idr != null)
// {
// idr.Close();
//}
_log.Log(Level.SEVERE, sql, e);
}
finally
{
dt = null;
}
MCostElement[] retValue = new MCostElement[list.Count];
retValue = list.ToArray();
return(retValue);
}
/**
* Get first Material Cost Element
* @param ctx context
* @param CostingMethod costing method
* @return Cost Element or null
*/
public static MCostElement GetMaterialCostElement(Ctx ctx, String CostingMethod)
{
MCostElement retValue = null;
String sql = "SELECT * FROM M_CostElement WHERE AD_Client_ID=" + ctx.GetAD_Client_ID() + " AND CostingMethod=@CostingMethod ORDER BY AD_Org_ID";
DataTable dt = null;
IDataReader idr = null;
try
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@CostingMethod", CostingMethod);
idr = DataBase.DB.ExecuteReader(sql, param, null);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
retValue = new MCostElement(ctx, dr, null);
}
//if (dr.next())
// s_log.info("More then one Material Cost Element for CostingMethod=" + CostingMethod);
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally
{
dt = null;
}
return(retValue);
}
/**
* Get Material Cost Element or create it
* @param po parent
* @param CostingMethod method
* @return cost element
*/
public static MCostElement GetMaterialCostElement(PO po, String CostingMethod)
{
if (CostingMethod == null || CostingMethod.Length == 0)
{
_log.Severe("No CostingMethod");
return(null);
}
//
MCostElement retValue = null;
String sql = "SELECT * FROM M_CostElement WHERE AD_Client_ID=" + po.GetAD_Client_ID() + " AND CostingMethod=@costingMethod ORDER BY AD_Org_ID";
DataTable dt = null;
IDataReader idr = null;
try
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@costingMethod", CostingMethod);
idr = DataBase.DB.ExecuteReader(sql, param, po.Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
//bool n = dr.next(); //jz to fix DB2 resultSet closed problem
//if (n)
foreach (DataRow dr in dt.Rows)
{
retValue = new MCostElement(po.GetCtx(), dr, po.Get_TrxName());
}
//if (n && dr.next())
// s_log.warning("More then one Material Cost Element for CostingMethod=" + CostingMethod);
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally
{
dt = null;
}
if (retValue != null)
{
return(retValue);
}
// Create New
retValue = new MCostElement(po.GetCtx(), 0, po.Get_TrxName());
retValue.SetClientOrg(po.GetAD_Client_ID(), 0);
String name = MRefList.GetListName(po.GetCtx(), COSTINGMETHOD_AD_Reference_ID, CostingMethod);
if (name == null || name.Length == 0)
{
name = CostingMethod;
}
retValue.SetName(name);
retValue.SetCostElementType(COSTELEMENTTYPE_Material);
retValue.SetCostingMethod(CostingMethod);
retValue.Save();
return(retValue);
}
/// <summary>
/// Adjust Qty based on 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="Qty">quantity to be reduced</param>
/// <param name="trxName">transaction</param>
/// <returns>cost price reduced or null of error</returns>
public static Decimal?AdjustQty(MProduct product, int M_ASI_ID, MAcctSchema mas,
int Org_ID, MCostElement ce, Decimal Qty, Trx trxName)
{
if (Env.Signum(Qty) == 0)
{
return(Env.ZERO);
}
MCostQueue[] costQ = GetQueue(product, M_ASI_ID, mas, Org_ID, ce, trxName);
Decimal remainingQty = Qty;
for (int i = 0; i < costQ.Length; i++)
{
MCostQueue queue = costQ[i];
// Negative Qty i.e. add
if (Env.Signum(remainingQty) < 0)
{
Decimal oldQty = queue.GetCurrentQty();
Decimal newQty = Decimal.Subtract(oldQty, remainingQty);
queue.SetCurrentQty(newQty);
if (queue.Save())
{
_log.Fine("Qty=" + remainingQty
+ "(!), ASI=" + queue.GetM_AttributeSetInstance_ID()
+ " - " + oldQty + " -> " + newQty);
return(queue.GetCurrentCostPrice());
}
else
{
return(null);
}
}
// Positive queue
if (Env.Signum(queue.GetCurrentQty()) > 0)
{
Decimal reduction = remainingQty;
if (reduction.CompareTo(queue.GetCurrentQty()) > 0)
{
reduction = queue.GetCurrentQty();
}
Decimal oldQty = queue.GetCurrentQty();
Decimal newQty = Decimal.Subtract(oldQty, reduction);
queue.SetCurrentQty(newQty);
if (queue.Save())
{
_log.Fine("Qty=" + reduction
+ ", ASI=" + queue.GetM_AttributeSetInstance_ID()
+ " - " + oldQty + " -> " + newQty);
remainingQty = Decimal.Subtract(remainingQty, reduction);
}
else
{
return(null);
}
//
if (Env.Signum(remainingQty) == 0)
{
return(queue.GetCurrentCostPrice());
}
}
} // for queue
_log.Fine("RemainingQty=" + remainingQty);
return(null);
}
/// <summary>
/// Calculate Cost based on Qty based on 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="Qty">quantity to be reduced</param>
/// <param name="trxName">transaction</param>
/// <returns>cost for qty or null of error</returns>
public static Decimal?GetCosts(MProduct product, int M_ASI_ID, MAcctSchema mas,
int Org_ID, MCostElement ce, Decimal Qty, Trx trxName)
{
if (Env.Signum(Qty) == 0)
{
return(Env.ZERO);
}
MCostQueue[] costQ = GetQueue(product, M_ASI_ID,
mas, Org_ID, ce, trxName);
//
Decimal cost = Env.ZERO;
Decimal remainingQty = Qty;
Decimal?firstPrice = null;
Decimal?lastPrice = null;
//
for (int i = 0; i < costQ.Length; i++)
{
MCostQueue queue = costQ[i];
// Negative Qty i.e. add
if (Env.Signum(remainingQty) <= 0)
{
Decimal oldQty = queue.GetCurrentQty();
lastPrice = queue.GetCurrentCostPrice();
Decimal costBatch = Decimal.Multiply((Decimal)lastPrice, remainingQty);
cost = Decimal.Add(cost, costBatch);
_log.Config("ASI=" + queue.GetM_AttributeSetInstance_ID()
+ " - Cost=" + lastPrice + " * Qty=" + remainingQty + "(!) = " + costBatch);
return(cost);
}
// Positive queue
if (Env.Signum(queue.GetCurrentQty()) > 0)
{
Decimal reduction = remainingQty;
if (reduction.CompareTo(queue.GetCurrentQty()) > 0)
{
reduction = queue.GetCurrentQty();
}
Decimal oldQty = queue.GetCurrentQty();
lastPrice = queue.GetCurrentCostPrice();
Decimal costBatch = Decimal.Multiply((Decimal)lastPrice, reduction);
cost = Decimal.Add(cost, costBatch);
_log.Fine("ASI=" + queue.GetM_AttributeSetInstance_ID()
+ " - Cost=" + lastPrice + " * Qty=" + reduction + " = " + costBatch);
remainingQty = Decimal.Subtract(remainingQty, reduction);
// Done
if (Env.Signum(remainingQty) == 0)
{
_log.Config("Cost=" + cost);
return(cost);
}
if (firstPrice == null)
{
firstPrice = lastPrice;
}
}
} // for queue
if (lastPrice == null)
{
lastPrice = MCost.GetSeedCosts(product, M_ASI_ID, mas, Org_ID,
ce.GetCostingMethod(), 0);
if (lastPrice == null)
{
_log.Info("No Price found");
return(null);
}
_log.Info("No Cost Queue");
}
Decimal costBatch1 = Decimal.Multiply((Decimal)lastPrice, remainingQty);
_log.Fine("RemainingQty=" + remainingQty + " * LastPrice=" + lastPrice + " = " + costBatch1);
cost = Decimal.Add(cost, costBatch1);
_log.Config("Cost=" + cost);
return(cost);
}