/// <summary>
/// Get PO Price from PriceList - and convert it to AcctSchema Currency
/// </summary>
/// <param name="as1">accounting schema</param>
/// <param name="onlyPOPriceList">use only PO price list</param>
/// <returns>po price</returns>
private Decimal?GetPriceList(MAcctSchema as1, bool onlyPOPriceList)
{
StringBuilder sql = new StringBuilder(
"SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit "
+ "FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp "
+ "WHERE pl.M_PriceList_ID = plv.M_PriceList_ID"
+ " AND plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID"
+ " AND pp.M_Product_ID=" + _M_Product_ID);
if (onlyPOPriceList)
{
sql.Append(" AND pl.IsSOPriceList='N'");
}
sql.Append(" ORDER BY pl.IsSOPriceList ASC, plv.ValidFrom DESC");
int C_Currency_ID = 0;
Decimal? PriceList = null;
Decimal? PriceStd = null;
Decimal? PriceLimit = null;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, null);
if (idr.Read())
{
C_Currency_ID = Utility.Util.GetValueOfInt(idr[0]); //.getInt(1);
PriceList = Utility.Util.GetValueOfDecimal(idr[1]); //.getBigDecimal(2);
PriceStd = Utility.Util.GetValueOfDecimal(idr[2]); //.getBigDecimal(3);
PriceLimit = Utility.Util.GetValueOfDecimal(idr[3]); //.getBigDecimal(4);
}
idr.Close();
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString(), e);
}
// nothing found
if (C_Currency_ID == 0)
{
return(null);
}
Decimal?price = PriceLimit; // best bet
if (price == null || price.Equals(Env.ZERO))
{
price = PriceStd;
}
if (price == null || price.Equals(Env.ZERO))
{
price = PriceList;
}
// Convert
if (price != null && !price.Equals(Env.ZERO))
{
price = MConversionRate.Convert(as1.GetCtx(), Utility.Util.GetValueOfDecimal(price), C_Currency_ID, as1.GetC_Currency_ID(),
as1.GetAD_Client_ID(), 0);
}
return(price);
}
/// <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);
}
