private void GenerateMasterForecast(int M_Product_ID, decimal?totalQtyTeam, Decimal?totalQtyOpp, decimal?avgPrice)
{
sql = "select c_uom_id from m_product where m_product_id = " + M_Product_ID;
MMasterForecastLine mfLine = new MMasterForecastLine(GetCtx(), 0, null);
mfLine.SetAD_Client_ID(mf.GetAD_Client_ID());
mfLine.SetAD_Org_ID(mf.GetAD_Org_ID());
mfLine.SetM_Product_ID(M_Product_ID);
mfLine.SetC_MasterForecast_ID(mf.GetC_MasterForecast_ID());
mfLine.SetForcastQty(totalQtyTeam);
mfLine.SetOppQty(totalQtyOpp);
Decimal?total = Decimal.Add(totalQtyOpp.Value, totalQtyTeam.Value);
mfLine.SetTotalQty(total);
mfLine.SetPrice(avgPrice);
mfLine.SetC_UOM_ID(Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)));
mfLine.SetProcessed(true);
Decimal?planRevenue = Decimal.Round(Decimal.Multiply(avgPrice.Value, total.Value), 2, MidpointRounding.AwayFromZero);
mfLine.SetPlannedRevenue(planRevenue);
if (!mfLine.Save())
{
log.SaveError("MasterForecastLineNotSaved", "MasterForecastLineNotSaved");
}
}
private void OnlyOpportunityProducts()
{
//sql = " SELECT distinct(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL"
// + " AND p.ref_order_id IS NULL AND pl.m_product_id NOT IN (SELECT DISTINCT(M_Product_ID) FROM c_forecastline fl "
// + " INNER JOIN c_forecast f ON (fl.c_forecast_id = f.c_forecast_id) WHERE f.c_period_id = " + C_Period_ID
// + " AND f.ad_client_id = " + GetCtx().GetAD_Client_ID() + " AND fl.isactive = 'Y')";
sql = " SELECT distinct(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL"
+ " AND p.ref_order_id IS NULL AND pl.m_product_id NOT IN (select m_product_id from c_masterforecastline where isactive = 'Y' and c_masterforecast_id = " + mFor.GetC_MasterForecast_ID() + ")";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
while (idr.Read())
{
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "select sum(nvl(pl.plannedqty,0)) FROM c_projectline pl inner join c_project p on (p.c_project_id = pl.c_project_id) "
+ " WHERE pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and p.c_order_id is null and p.ref_order_id is null and pl.isactive = 'Y'";
totalQtyOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
sql = " SELECT SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) FROM c_projectline pl inner join c_project p on (p.c_project_id = pl.c_project_id) "
+ " WHERE pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and p.c_order_id is null and p.ref_order_id is null and pl.isactive = 'Y'";
totalPriceOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
if (totalQtyOpp.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPriceOpp.Value, totalQtyOpp.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
GenerateMasterForecast(Util.GetValueOfInt(idr[0]), Util.GetValueOfDecimal(Decimal.Zero), totalQtyOpp, avgPrice);
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
}
/// <SUMmary>
/// Create Master Forecast
/// </SUMmary>
/// <param name="M_Product_ID">Product </param>
/// <param name="totalQtyTeam">Forecast Qty</param>
/// <param name="totalQtyOpp">Opportunity Qty</param>
/// <param name="avgPrice">Price</param>
/// <param name="totalQtySO">Total </param>
/// <returns>odject</returns>
private MMasterForecastLine GenerateMasterForecast(int M_Product_ID, int M_AttributeSetInstance, decimal?totalQtyTeam, Decimal?totalQtyOpp, decimal?avgPrice)
{
MMasterForecastLine mfLine = MMasterForecastLine.GetOrCreate(mf, M_Product_ID, M_AttributeSetInstance);
mfLine.SetC_MasterForecast_ID(mf.GetC_MasterForecast_ID());
mfLine.SetForcastQty(totalQtyTeam);
mfLine.SetOppQty(totalQtyOpp + mfLine.GetOppQty());
Decimal?total = Decimal.Add(totalQtyOpp.Value, totalQtyTeam.Value);
mfLine.SetTotalQty(total + mfLine.GetTotalQty());
mfLine.SetPrice(avgPrice + mfLine.GetPrice());
Decimal?planRevenue = Decimal.Round(Decimal.Multiply(mfLine.GetPrice(), mfLine.GetTotalQty()), StdPrecision, MidpointRounding.AwayFromZero);
mfLine.SetPlannedRevenue(planRevenue);
return(mfLine);
}
/// <SUMmary>
/// Create Master Forecast
/// </SUMmary>
/// <param name="M_Product_ID">Product </param>
/// <param name="totalQtyTeam">Forecast Qty</param>
/// <param name="totalQtyOpp">Opportunity Qty</param>
/// <param name="avgPrice">Price</param>
/// <param name="totalQtySO">Total </param>
/// <returns>object</returns>
private MMasterForecastLine GenerateMasterForecast(int M_Product_ID, int M_AttributeSetInstance, decimal?totalQtyTeam, Decimal?totalQtyOpp, decimal?avgPrice)
{
MMasterForecastLine mfLine = MMasterForecastLine.GetOrCreate(mf, M_Product_ID, M_AttributeSetInstance);
Decimal? qty = mfLine.GetOppQty();
mfLine.SetC_MasterForecast_ID(mf.GetC_MasterForecast_ID());
if (totalQtyOpp > 0 && totalQtyTeam > 0)
{
mfLine.SetForcastQty(totalQtyTeam + mfLine.GetForcastQty());
mfLine.SetOppQty(totalQtyOpp);
}
else
{
mfLine.SetForcastQty(totalQtyTeam);
mfLine.SetOppQty(totalQtyOpp + mfLine.GetOppQty());
}
//Decimal? totalqty = Decimal.Add(mfLine.GetForcastQty(), mfLine.GetOppQty());
mfLine.SetTotalQty(mfLine.GetForcastQty() + mfLine.GetOppQty());
//calculate average price for opportunit case only
if (totalQtyOpp > 0 && totalQtyTeam == 0)
{
avgPrice = ((mfLine.GetPrice() * qty) + (avgPrice * totalQtyOpp)) / mfLine.GetTotalQty();
avgPrice = Decimal.Round(avgPrice.Value, StdPrecision, MidpointRounding.AwayFromZero);
mfLine.SetPrice(avgPrice);
}
//Team Forecast case
else
{
mfLine.SetPrice(avgPrice);
}
Decimal?planRevenue = Decimal.Round(Decimal.Multiply(mfLine.GetPrice(), mfLine.GetTotalQty()), StdPrecision, MidpointRounding.AwayFromZero);
mfLine.SetPlannedRevenue(planRevenue);
return(mfLine);
}