/// <summary> /// Update/Create initial Cost Record. /// Check first for Purchase Price List, /// then Product Purchase Costs /// and then Price List /// </summary> /// <param name="as1">accounting schema</param> /// <param name="create">create record</param> /// <returns>costs</returns> private Decimal?UpdateCostsOld(MAcctSchema as1, bool create) { // Create Zero Record if (create) { StringBuilder sql = new StringBuilder("INSERT INTO M_Product_Costing " + "(M_Product_ID,C_AcctSchema_ID," + " AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," + " CurrentCostPrice,CostStandard,FutureCostPrice," + " CostStandardPOQty,CostStandardPOAmt,CostStandardCumQty,CostStandardCumAmt," + " CostAverage,CostAverageCumQty,CostAverageCumAmt," + " PriceLastPO,PriceLastInv, TotalInvQty,TotalInvAmt) " + "VALUES ("); sql.Append(_M_Product_ID).Append(",").Append(as1.GetC_AcctSchema_ID()).Append(",") .Append(as1.GetAD_Client_ID()).Append(",").Append(as1.GetAD_Org_ID()).Append(",") .Append("'Y',SysDate,0,SysDate,0, 0,0,0, 0,0,0,0, 0,0,0, 0,0, 0,0)"); int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, _trx); if (no == 1) { log.Fine("CostingCreated"); } } // Try to find non ZERO Price String costSource = "PriceList-PO"; Decimal?costs = GetPriceList(as1, true); if (costs == null || costs.Equals(Env.ZERO)) { costSource = "PO Cost"; costs = GetPOCost(as1); } if (costs == null || costs.Equals(Env.ZERO)) { costSource = "PriceList"; costs = GetPriceList(as1, false); } // if not found use $1 (to be able to do material transactions) if (costs == null || costs.Equals(Env.ZERO)) { costSource = "Not Found"; costs = 1;//new Decimal(1); } // update current costs StringBuilder sql1 = new StringBuilder("UPDATE M_Product_Costing "); sql1.Append("SET CurrentCostPrice=").Append(costs) .Append(" WHERE M_Product_ID=").Append(_M_Product_ID) .Append(" AND C_AcctSchema_ID=").Append(as1.GetC_AcctSchema_ID()); int no1 = DataBase.DB.ExecuteQuery(sql1.ToString(), null, _trx); if (no1 == 1) { log.Fine(costSource + " - " + costs); } return(costs); }
/// <summary> /// Get PO Cost from Purchase Info - and convert it to AcctSchema Currency /// </summary> /// <param name="as1">accounting schema</param> /// <returns>po cost</returns> private Decimal?GetPOCost(MAcctSchema as1) { String sql = "SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO " + "FROM M_Product_PO WHERE M_Product_ID=" + _M_Product_ID + "ORDER BY IsCurrentVendor DESC"; int C_Currency_ID = 0; Decimal? PriceList = null; Decimal? PricePO = null; Decimal? PriceLastPO = null; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); if (idr.Read()) { C_Currency_ID = Utility.Util.GetValueOfInt(idr[0]); //.getInt(1); PriceList = Utility.Util.GetValueOfDecimal(idr[1]); //.getBigDecimal(2); PricePO = Utility.Util.GetValueOfDecimal(idr[2]); //.getBigDecimal(3); PriceLastPO = Utility.Util.GetValueOfDecimal(idr[3]); //.getBigDecimal(4); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); idr = null; } log.Log(Level.SEVERE, sql, e); } // nothing found if (C_Currency_ID == 0) { return(null); } Decimal?cost = PriceLastPO; // best bet if (cost == null || cost.Equals(Env.ZERO)) { cost = PricePO; } if (cost == null || cost.Equals(Env.ZERO)) { cost = PriceList; } // Convert - standard precision!! - should be costing precision if (cost != null && !cost.Equals(Env.ZERO)) { cost = MConversionRate.Convert(as1.GetCtx(), Utility.Util.GetValueOfDecimal(cost), C_Currency_ID, as1.GetC_Currency_ID(), as1.GetAD_Client_ID(), as1.GetAD_Org_ID()); } return(cost); }