/// <SUMmary>
/// Create Product Line details
/// </SUMmary>
/// <param name="Parent">C_MasterForecastLine</param>
/// <param name="Order_ID">Sales Order</param>
/// <param name="OrderLine_ID">Sales Order line</param>
/// <param name="Opportunity_ID">Opportunity</param>
/// <param name="OppLine_ID">Opportunity Line</param>
/// <param name="Forecast">Team Forecast</param>
/// <param name="ForecastLine_ID">Forecast Line</param>
/// <param name="C_Period_ID">Period</param>
/// <param name="UOM_ID">UOM</param>
/// <param name="Product_ID">Product</param>
/// <param name="Quantity">Qunatity</param>
/// <param name="Price">Price</param>
/// <param name="M_AttributeSetInstance_ID">Attribute Set Instance</param>
/// <returns>Product line detail Object</returns>
private PO GenerateProductLineDetails(MMasterForecastLine Parent, int LineNo, int Order_ID, int OrderLine_ID, int Opportunity_ID, int OppLine_ID, int Forecast, int ForecastLine_ID, int C_Period_ID, int UOM_ID, int Product_ID, Decimal?Quantity, Decimal?Price, int M_AttributeSetInstance_ID)
{
//object of VA073_MasterForecastLineDetails
po = tbl.GetPO(mf.GetCtx(), 0, mf.Get_Trx());
//LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0) AS DefaultValue FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + Parent.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
po.SetAD_Client_ID(Parent.GetAD_Client_ID());
po.SetAD_Org_ID(Parent.GetAD_Org_ID());
po.Set_Value("LineNo", LineNo);
po.Set_Value("C_MasterForecastLine_ID", Parent.GetC_MasterForecastLine_ID());
po.Set_Value("C_Order_ID", Order_ID);
po.Set_Value("C_OrderLine_ID", OrderLine_ID);
po.Set_Value("C_Project_ID", Opportunity_ID);
po.Set_Value("C_ProjectLine_ID", OppLine_ID);
po.Set_Value("C_Forecast_ID", Forecast);
po.Set_Value("C_ForecastLine_ID", ForecastLine_ID);
po.Set_Value("C_Period_ID", C_Period_ID);
po.Set_Value("C_UOM_ID", UOM_ID);
po.Set_Value("M_Product_ID", Product_ID);
po.Set_Value("QtyEntered", Quantity);
po.Set_Value("Price", Price);
po.Set_Value("M_AttributeSetInstance_ID", M_AttributeSetInstance_ID);
return(po);
}
/// <SUMmary>
/// Sales Order Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int SalesOrderProducts()
{
sql = "SELECT ol.m_product_id,ol.QtyOrdered,M_AttributeSetInstance_ID,ol.C_UOM_ID," +
" ol.C_OrderLine_ID,o.C_Order_ID,(NVL(PriceEntered,0) * NVL(QtyEntered,0)) AS Price,o.C_Currency_ID FROM C_Order o " +
" INNER JOIN C_OrderLine ol ON o.C_Order_ID = ol.C_Order_ID " +
" INNER JOIN C_Doctype d ON o.c_DocTypeTarget_ID = d.C_Doctype_ID " +
" WHERE d.DocBaseType='" + MDocBaseType.DOCBASETYPE_SALESORDER + "' " +
" AND d.DocSubTypeSo NOT IN ('" + MDocType.DOCSUBTYPESO_BlanketOrder + "','" + MDocType.DOCSUBTYPESO_Proposal + "')" +
" AND o.IsSOTrx='Y' AND o.IsReturnTrx='N' AND o.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND o.DateOrdered 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 ol.QtyOrdered > ol.QtyDelivered " +
" AND ol.C_OrderLine_ID NOT IN (SELECT C_OrderLine_ID FROM va073_masterforecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(ol.M_Product_ID,0)>0 AND o.DocStatus IN('CO','CL') ";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_Order", true, true); // fully qualified - RO
dsOrder = new DataSet();
dsOrder = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOrder != null && dsOrder.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsOrder.Tables[0].Rows.Count; i++)
{
//create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOrder.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_Order_ID"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"]), 0, 0, 0, 0,
C_Period_ID, Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfDecimal(dsOrder.Tables[0].Rows[i]["QtyOrdered"]), ConvertedAmt,
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + "for OrderLine" + Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"])
+ vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + "for OrderLine" + Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"]));
}
}
else
{
Count++;
LineNo += 10;
//Update quantities AND price at Product line
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundSalesOrder"));
}
return(Count);
}
/// <SUMmary>
/// Team Forecast Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int TeamForecastProduct()
{
sql = @"SELECT fl.M_Product_ID,fl.M_AttributeSetInstance_ID,fl.qtyentered,fl.BaseQty,f.C_Forecast_ID,
C_ForecastLine_ID,f.C_Period_ID,fl.C_UOM_ID,NVL(pricestd,0) AS Price,f.C_Currency_ID
FROM C_Forecast f " +
" INNER JOIN C_Forecastline fl ON fl.c_forecast_id = f.c_forecast_id " +
" WHERE f.c_period_id = " + C_Period_ID + " AND f.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND f.isactive = 'Y' AND f.processed = 'Y'" +
" AND C_ForecastLine_ID NOT IN (SELECT C_ForecastLine_ID FROM VA073_MasterForecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(fl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(mf.GetCtx()).AddAccessSQL(sql, "C_Forecast", true, true); // fully qualified - RO
dsForecast = new DataSet();
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsForecast.Tables[0].Rows.Count; i++)
{
//Create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, 0, 0, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Forecast_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Period_ID"]), mfLine.GetC_UOM_ID(),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["qtyentered"]),
ConvertedAmt, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]) + " " + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]));
}
}
else
{
//Update quantities AND Price at Product line
Count++;
LineNo += 10;
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundForecast"));
}
return(Count);
}
/// <SUMmary>
/// Oppportunity Products
/// </SUMmary>
/// <returns>No of Lines created</returns>
private int OnlyOpportunityProducts()
{
if (!Env.IsModuleInstalled("VA073_"))
{
//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 ANDNULL 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 = " + GetRecord_ID() + ")";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, mf.Get_Trx());
while (idr.Read())
{
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "SELECT SUM(nvl(pl.plannedqty,0)) AS Quantity ,SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price, p.C_Currency_ID,pl.C_UOM_ID" +
" 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'"
+ " GROUP BY C_Currency_ID,pl.C_UOM_ID";
// 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));
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Conversion from BaseUOM to UOM on Project Line
totalQtyOpp = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(idr[0]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Quantity"]));
}
if (totalQtyOpp.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPriceOpp.Value, totalQtyOpp.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(idr[0]), 0, Util.GetValueOfDecimal(Decimal.Zero), totalQtyOpp, avgPrice);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
}
else
{
//VA073_ Module is Installed
sql = "SELECT pl.m_product_id, p.c_project_id,p.C_Currency_ID,pl.c_projectline_id, pl.plannedqty,pl.C_UOM_ID," +
"(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price,pl.M_AttributeSetInstance_ID " +
" FROM C_Project p " +
"INNER JOIN C_ProjectLine pl ON p.C_Project_ID = pl.C_Project_ID" +
" WHERE p.c_order_id IS NULL AND p.ref_order_id IS NULL AND c_period_id = " + C_Period_ID + " AND p.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND C_ProjectLine_ID NOT IN (SELECT C_ProjectLine_ID FROM va073_masterforecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(pl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_Project", true, true); // fully qualified - RO
dsOpp = new DataSet();
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsOpp.Tables[0].Rows.Count; i++)
{
//Create MasterForecastline
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Conversion from BaseUOM to UOM on Project Line
OppQty = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["plannedqty"]));
if (OppQty == null)
{
OppQty = Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["plannedqty"]);
}
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_Project_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]), 0, 0,
C_Period_ID, Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]),
OppQty, ConvertedAmt,
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ProjectLine " + Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]) + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ProjectLine " + Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]));
}
}
else
{
Count++;
LineNo += 10;
//Update quantities AND price at Product line
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundOpportunity"));
}
}
return(Count);
}