/// <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> /// 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); }
/// <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); }