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