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