/// <summary> /// /// </summary> /// <param name="mf"></param> /// <param name="opp"></param> private void CreateMasterForecast(bool opp) { mFor = new X_C_MasterForecast(GetCtx(), 0, null); mFor.SetAD_Client_ID(mf.GetAD_Client_ID()); mFor.SetAD_Org_ID(mf.GetAD_Org_ID()); mFor.SetC_Period_ID(C_Period_ID); mFor.SetCurrentVersion(true); if (opp) { mFor.SetIsIncludeOpp(true); } else { mFor.SetIsIncludeOpp(false); } string name = mf.GetName().Substring(0, mf.GetName().IndexOf('_') + 1); string date = System.DateTime.Now.ToString("dd-MMM-yyyy"); if (name == "") { mFor.SetName(mf.GetName() + "_" + date); } else { mFor.SetName(name + date); } if (!mFor.Save()) { log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved"); } }
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; X_C_MasterForecastLine mfLine = new X_C_MasterForecastLine(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 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.ad_client_id = " + GetAD_Client_ID() + " and 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, 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' and p.ad_client_id = " + mf.GetAD_Client_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' and p.ad_client_id = " + mf.GetAD_Client_ID(); 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; } } }
protected override string DoIt() { mf = new X_C_MasterForecast(GetCtx(), GetRecord_ID(), null); C_Period_ID = mf.GetC_Period_ID(); // sql = "delete from c_masterforecastline where c_masterforecast_id = " + mf.GetC_MasterForecast_ID(); // int count = DB.ExecuteQuery(sql, null, null); if (C_Period_ID != 0) { sql = "select count(*) from c_masterforecastline where c_masterforecast_id = " + GetRecord_ID(); int count = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); if (count > 0) { sql = "update c_masterforecastline set Processed = 'Y' where c_masterforecast_id = " + GetRecord_ID(); int res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); sql = "update c_masterforecast set Processed = 'Y' where c_masterforecast_id = " + GetRecord_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); msg = Msg.GetMsg(GetCtx(), "RecordsProcessed"); return(msg); } sql = "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 f.isactive = 'Y' and f.processed = 'Y'"; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, null); while (idr.Read()) { Decimal?totalQtyTeam = 0; Decimal?totalPriceTeam = 0; Decimal?totalQtyOpp = 0; Decimal?totalPriceOpp = 0; sql = "select SUM(nvl(qtyentered,0)) from c_forecastline where m_product_id = " + Util.GetValueOfInt(idr[0]) + " and Processed = 'Y' and isactive = 'Y' and ad_client_id = " + mf.GetAD_Client_ID(); //sql = "select SUM(nvl(fl.qtyentered,0)) from c_forecastline fl inner join c_forecast f on (f.c_forecast_id = fl.c_forecast_id) where f.ad_client_id = " + mf.GetAD_Client_ID() + " and fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and fl.Processed = 'Y' and fl.isactive = 'Y' and f.c_period_id = " + C_Period_ID; totalQtyTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); sql = "select SUM(nvl(pricestd,0)) from c_forecastline where m_product_id = " + Util.GetValueOfInt(idr[0]) + " and Processed = 'Y' and isactive = 'Y' and ad_client_id = " + mf.GetAD_Client_ID(); // sql = "select SUM(nvl(fl.pricestd,0)) from c_forecastline fl inner join c_forecast f on (f.c_forecast_id = fl.c_forecast_id) where f.ad_client_id = " + mf.GetAD_Client_ID() + " and fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and fl.Processed = 'Y' and fl.isactive = 'Y' and f.c_period_id = " + C_Period_ID; totalPriceTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); if (mf.IsIncludeOpp()) { 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' and p.ad_client_id = " + mf.GetAD_Client_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' and p.ad_client_id = " + mf.GetAD_Client_ID(); totalPriceOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); } Decimal?totalPrice = Decimal.Add(totalPriceTeam.Value, totalPriceOpp.Value); Decimal?totalQty = Decimal.Add(totalQtyTeam.Value, totalQtyOpp.Value); if (totalQty.Value > 0) { Decimal?avgPrice = Decimal.Divide(totalPrice.Value, totalQty.Value); avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero); GenerateMasterForecast(Util.GetValueOfInt(idr[0]), totalQtyTeam, totalQtyOpp, avgPrice); } } if (idr != null) { idr.Close(); idr = null; } } catch { if (idr != null) { idr.Close(); idr = null; } } if (mf.IsIncludeOpp()) { OnlyOpportunityProducts(); } mf.SetCurrentVersion(true); mf.SetProcessed(true); if (!mf.Save()) { log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved"); } msg = Msg.GetMsg(GetCtx(), "ProcessCompleted"); } return(msg); }
/// <SUMmary> /// Consolidate Data FROM sales order , Team Forecast, Opportunity /// </SUMmary> /// <returns>Info</returns> protected override string DoIt() { mf = new X_C_MasterForecast(GetCtx(), GetRecord_ID(), Get_Trx()); if (Util.GetValueOfInt(mf.Get_Value("M_PriceList_ID")) == 0) { return(Msg.GetMsg(mf.GetCtx(), "CreatelinesManually")); } C_Period_ID = mf.GetC_Period_ID(); Currency = Util.GetValueOfInt(mf.Get_Value("C_Currency_ID")); StdPrecision = Util.GetValueOfInt(DB.ExecuteScalar("SELECT StdPrecision FROM C_Currency WHERE C_Currency_ID=" + Currency, null, null)); //Get Table_Id to create PO Object // sql = @"SELECT AD_TABLE_ID FROM AD_TABLE WHERE tablename LIKE 'VA073_MasterForecastLineDetail' AND IsActive = 'Y'"; // tableId = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); // tbl = new MTable(GetCtx(), tableId, null); tbl = MTable.Get(GetCtx(), "VA073_MasterForecastLineDetail"); // sql = "delete FROM c_masterforecastline WHERE c_masterforecast_id = " + mf.GetC_MasterForecast_ID(); // int count = DB.ExecuteQuery(sql, null, null); if (C_Period_ID != 0) { sql = "SELECT COUNT(C_MasterForecastLine_ID) FROM c_masterforecastline WHERE c_masterforecast_id = " + GetRecord_ID(); int count = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); if (count > 0) { sql = "UPDATE c_masterforecastline set Processed = 'Y' WHERE c_masterforecast_id = " + GetRecord_ID(); int res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); sql = "UPDATE c_masterforecast set Processed = 'Y' WHERE c_masterforecast_id = " + GetRecord_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); msg = Msg.GetMsg(GetCtx(), "RecordsProcessed"); return(msg); } if (!Env.IsModuleInstalled("VA073_")) { sql = "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 f.isactive = 'Y' AND f.processed = 'Y'"; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, mf.Get_Trx()); while (idr.Read()) { Decimal?totalQtyTeam = 0; Decimal?totalPriceTeam = 0; Decimal?totalQtyOpp = 0; Decimal?totalPriceOpp = 0; sql = "SELECT SUM(nvl(qtyentered,0)) AS Quantity,SUM(nvl(pricestd,0)) AS Price,f.C_Currency_ID FROM c_forecastline fl" + " INNER JOIN C_Forecast f ON f.C_Forecast_ID = fl.C_Forecast_ID " + " WHERE fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND f.Processed = 'Y' AND f.isactive = 'Y'" + " GROUP BY f.C_Currency_ID"; //totalQtyTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); //// sql = "SELECT SUM(nvl(qtyentered,0) * nvl(pricestd,0)) FROM c_forecastline WHERE m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND Processed = 'Y'"; //sql = "SELECT SUM(nvl(pricestd,0)) FROM c_forecastline WHERE m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND Processed = 'Y' AND isactive = 'Y'"; //totalPriceTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx())); dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx()); if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0) { totalPriceTeam = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[0]["Price"]), Util.GetValueOfInt(dsForecast.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()); totalQtyTeam = Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[0]["Quantity"]); } if (mf.IsIncludeOpp()) { 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, Get_Trx())); //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' AND p.ad_client_id = " + mf.GetAD_Client_ID(); 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"])); } } Decimal?totalPrice = Decimal.Add(totalPriceTeam.Value, totalPriceOpp.Value); Decimal?totalQty = Decimal.Add(totalQtyTeam.Value, totalQtyOpp.Value); if (totalQty.Value > 0) { Decimal?avgPrice = Decimal.Divide(totalPrice.Value, totalQty.Value); avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero); mfLine = GenerateMasterForecast(Util.GetValueOfInt(idr[0]), 0, totalQtyTeam, 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; } } if (mf.IsIncludeOpp()) { OnlyOpportunityProducts(); } mf.SetCurrentVersion(true); mf.SetProcessed(true); if (!mf.Save()) { log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved"); return(GetRetrievedError(mf, "MasterForecastNotSaved")); } msg = Msg.GetMsg(GetCtx(), "ProcessCompleted"); } else { //VA073 module installed -- Consolidate FROM Sales order , opportunity , Team Forecast TeamForecastProduct(); if (mf.IsIncludeOpp()) { OnlyOpportunityProducts(); } if (Util.GetValueOfBool(mf.Get_Value("VA073_IsIncludeOpenSO"))) { SalesOrderProducts(); } if (Count == 0) { mf.Get_Trx().Rollback(); } else { //UPDATE Master forecast Line Set processed to true sql = "UPDATE C_MasterForecastLine SET Processed='Y' WHERE C_MasterForecast_ID=" + GetRecord_ID(); DB.ExecuteQuery(sql, null, mf.Get_Trx()); //UPDATE Master forecast Set processed to true sql = "UPDATE C_MasterForecast SET Processed='Y' WHERE C_MasterForecast_ID=" + GetRecord_ID(); DB.ExecuteQuery(sql, null, mf.Get_Trx()); } msg = Msg.GetMsg(mf.GetCtx(), "ProductLinesDetailCreated") + Count; } } return(msg); }
/// <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); }