/// <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> /// 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); }
} // doIt /// <summary> /// Process Expense Line /// </summary> /// <param name="te">header</param> /// <param name="tel">line</param> /// <param name="bp">bp</param> private void ProcessLine(MTimeExpense te, MTimeExpenseLine tel, MBPartner bp) { if (_order == null) { log.Info("New Order for " + bp + ", Project=" + tel.GetC_Project_ID()); _order = new MOrder(GetCtx(), 0, Get_TrxName()); _order.SetAD_Org_ID(tel.GetAD_Org_ID()); _order.SetC_DocTypeTarget_ID(MOrder.DocSubTypeSO_Standard); // _order.SetBPartner(bp); if (_order.GetC_BPartner_Location_ID() == 0) { log.Log(Level.SEVERE, "No BP Location: " + bp); AddLog(0, te.GetDateReport(), null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName()); _order = null; return; } _order.SetM_Warehouse_ID(te.GetM_Warehouse_ID()); if (tel.GetC_Activity_ID() != 0) { _order.SetC_Activity_ID(tel.GetC_Activity_ID()); } if (tel.GetC_Campaign_ID() != 0) { _order.SetC_Campaign_ID(tel.GetC_Campaign_ID()); } if (tel.GetC_Project_ID() != 0) { _order.SetC_Project_ID(tel.GetC_Project_ID()); // Optionally Overwrite BP Price list from Project MProject project = new MProject(GetCtx(), tel.GetC_Project_ID(), Get_TrxName()); if (project.GetM_PriceList_ID() != 0) { _order.SetM_PriceList_ID(project.GetM_PriceList_ID()); } } _order.SetSalesRep_ID(te.GetDoc_User_ID()); // if (!_order.Save()) { throw new Exception("Cannot save Order"); } } else { // Update Header info if (tel.GetC_Activity_ID() != 0 && tel.GetC_Activity_ID() != _order.GetC_Activity_ID()) { _order.SetC_Activity_ID(tel.GetC_Activity_ID()); } if (tel.GetC_Campaign_ID() != 0 && tel.GetC_Campaign_ID() != _order.GetC_Campaign_ID()) { _order.SetC_Campaign_ID(tel.GetC_Campaign_ID()); } if (!_order.Save()) { new Exception("Cannot save Order"); } } // OrderLine MOrderLine ol = new MOrderLine(_order); // if (tel.GetM_Product_ID() != 0) { ol.SetM_Product_ID(tel.GetM_Product_ID(), tel.GetC_UOM_ID()); } if (tel.GetS_ResourceAssignment_ID() != 0) { ol.SetS_ResourceAssignment_ID(tel.GetS_ResourceAssignment_ID()); } ol.SetQty(tel.GetQtyInvoiced()); // ol.SetDescription(tel.GetDescription()); // ol.SetC_Project_ID(tel.GetC_Project_ID()); ol.SetC_ProjectPhase_ID(tel.GetC_ProjectPhase_ID()); ol.SetC_ProjectTask_ID(tel.GetC_ProjectTask_ID()); ol.SetC_Activity_ID(tel.GetC_Activity_ID()); ol.SetC_Campaign_ID(tel.GetC_Campaign_ID()); // Decimal price = tel.GetPriceInvoiced(); // if (price.CompareTo(Env.ZERO) != 0) { if (tel.GetC_Currency_ID() != _order.GetC_Currency_ID()) { price = MConversionRate.Convert(GetCtx(), price, tel.GetC_Currency_ID(), _order.GetC_Currency_ID(), _order.GetAD_Client_ID(), _order.GetAD_Org_ID()); } ol.SetPrice(price); } else { ol.SetPrice(); } if (tel.GetC_UOM_ID() != 0 && ol.GetC_UOM_ID() == 0) { ol.SetC_UOM_ID(tel.GetC_UOM_ID()); } ol.SetTax(); if (!ol.Save()) { throw new Exception("Cannot save Order Line"); } // Update TimeExpense Line tel.SetC_OrderLine_ID(ol.GetC_OrderLine_ID()); if (tel.Save()) { log.Fine("Updated " + tel + " with C_OrderLine_ID"); } else { log.Log(Level.SEVERE, "Not Updated " + tel + " with C_OrderLine_ID"); } } // processLine
/// <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); }
/// <summary> /// Create Facts (the accounting logic) for /// CMC. /// <pre> /// Expense /// CashExpense DR /// CashAsset CR ///Receipt /// CashAsset DR /// CashReceipt CR /// Charge /// Charge DR /// CashAsset CR /// Difference /// CashDifference DR /// CashAsset CR /// Invoice /// CashAsset DR /// CashTransfer CR /// Transfer /// BankInTransit DR /// CashAsset CR /// </pre> /// </summary> /// <param name="?"></param> /// <returns>Fact</returns> public override List <Fact> CreateFacts(MAcctSchema as1) { // Need to have CashBook if (GetC_CashBook_ID() == 0) { _error = "C_CashBook_ID not set"; log.Log(Level.SEVERE, _error); return(null); } // create Fact Header Fact fact = new Fact(this, as1, Fact.POST_Actual); // Header posting amt as1 Invoices and Transfer could be differenet currency // CashAsset Total Decimal assetAmt = Env.ZERO; Decimal TotalCurrLoss = Env.ZERO; Decimal TotalCurrGain = Env.ZERO; int headerCashCurrency = 0; int childCashCurrency = 0; int headerCashOrg = 0; int childCashOrg = 0; // Lines for (int i = 0; i < _lines.Length; i++) { DocLine_Cash line = (DocLine_Cash)_lines[i]; String CashType = line.GetCashType(); if (CashType.Equals(DocLine_Cash.CASHTYPE_EXPENSE)) { // amount is negative // CashExpense DR // CashAsset CR fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashExpense, as1), GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null); // fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), // p_vo.C_Currency_ID, null, line.GetAmount().negate()); assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())); CreateFactLine(fact, line, as1, line.GetAmount()); } else if (CashType.Equals(DocLine_Cash.CASHTYPE_RECEIPT)) { // amount is positive // CashAsset DR // CashReceipt CR // fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), // p_vo.C_Currency_ID, line.GetAmount(), null); assetAmt = Decimal.Add(assetAmt, line.GetAmount()); fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashReceipt, as1), GetC_Currency_ID(), null, line.GetAmount()); CreateFactLine(fact, line, as1, line.GetAmount()); } else if (CashType.Equals(DocLine_Cash.CASHTYPE_CHARGE)) { // amount is negative // Charge DR // CashAsset CR fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()), GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); //fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()), // GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null); // fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), // p_vo.C_Currency_ID, null, line.GetAmount().negate()); assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())); CreateFactLine(fact, line, as1, line.GetAmount()); } else if (CashType.Equals(DocLine_Cash.CASHTYPE_DIFFERENCE)) { // amount is pos/neg // CashDifference DR // CashAsset CR fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashDifference, as1), GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); // fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), // p_vo.C_Currency_ID, line.GetAmount()); assetAmt = Decimal.Add(assetAmt, line.GetAmount()); CreateFactLine(fact, line, as1, line.GetAmount()); } else if (CashType.Equals(DocLine_Cash.CASHTYPE_INVOICE)) { // amount is pos/neg // CashAsset DR dr -- Invoice is in Invoice Currency ! // CashTransfer cr CR if (line.GetC_Currency_ID() == GetC_Currency_ID()) { assetAmt = Decimal.Add(assetAmt, line.GetAmount()); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), line.GetC_Currency_ID(), line.GetAmount()); } fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); CreateFactLine(fact, line, as1, line.GetAmount()); } else if (CashType.Equals(DocLine_Cash.CASHTYPE_TRANSFER)) { // amount is pos/neg // BankInTransit DR dr -- Transfer is in Bank Account Currency // CashAsset dr CR int temp = GetC_BankAccount_ID(); SetC_BankAccount_ID(line.GetC_BankAccount_ID()); fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_BankInTransit, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); SetC_BankAccount_ID(temp); if (line.GetC_Currency_ID() == GetC_Currency_ID()) { assetAmt = Decimal.Add(assetAmt, line.GetAmount()); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), line.GetC_Currency_ID(), line.GetAmount()); } CreateFactLine(fact, line, as1, line.GetAmount()); } // Change to Apply Posting Logic against BusinessPartner else if (CashType.Equals(DocLine_Cash.CASHTYPE_BUSINESSPARTNER)) { // amount is pos/neg // CashAsset DR dr -- Invoice is in Invoice Currency ! // CashTransfer cr CR MBPartner bPartner = new MBPartner(Env.GetCtx(), line.GetC_BPartner_ID(), null); if (bPartner != null) { if (bPartner.IsEmployee()) { if (line.GetC_Currency_ID() == GetC_Currency_ID()) { assetAmt = Decimal.Add(assetAmt, line.GetAmount()); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_E_Prepayment, as1, line.GetC_BPartner_ID()), line.GetC_Currency_ID(), line.GetAmount()); } fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_E_Prepayment, as1, line.GetC_BPartner_ID()), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); } else if (bPartner.IsVendor()) { if (line.GetC_Currency_ID() == GetC_Currency_ID()) { assetAmt = Decimal.Add(assetAmt, line.GetAmount()); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_V_Prepayment, as1, line.GetC_BPartner_ID()), line.GetC_Currency_ID(), line.GetAmount()); } fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_V_Prepayment, as1, line.GetC_BPartner_ID()), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); } else if (bPartner.IsCustomer()) { if (line.GetC_Currency_ID() == GetC_Currency_ID()) { assetAmt = Decimal.Add(assetAmt, line.GetAmount()); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_C_Prepayment, as1, line.GetC_BPartner_ID()), line.GetC_Currency_ID(), line.GetAmount()); } fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_C_Prepayment, as1, line.GetC_BPartner_ID()), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); } CreateFactLine(fact, line, as1, line.GetAmount()); } else { if (line.GetC_Currency_ID() == GetC_Currency_ID()) { assetAmt = Decimal.Add(assetAmt, line.GetAmount()); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), line.GetC_Currency_ID(), line.GetAmount()); } fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); CreateFactLine(fact, line, as1, line.GetAmount()); } } //Code Added to CashBookTransfer else if (CashType.Equals(DocLine_Cash.CASHTYPE_CASHBOOKTRANSFER)) { //// amount is negative //// Charge DR //// CashAsset CR //fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()), // GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null); //// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), //// p_vo.C_Currency_ID, null, line.GetAmount().negate()); //assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())); int temp = GetC_CashBook_ID(); SetC_CashBook_ID(line.Get_C_CashBook_ID()); if (BeginningBalance > 0) { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); SetC_CashBook_ID(temp); //if (line.GetC_Currency_ID() == (new MCashBook(Env.GetCtx(), GetC_CashBook_ID(), null)).GetC_Currency_ID())// GetC_Currency_ID()) //{ //if (BeginningBalance > assetAmt) //if (BeginningBalance >= Math.Abs(line.GetAmount())) //if (assetAmt <= Math.Abs(line.GetAmount())) //{ assetAmt = (Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()))); //} //else //{ // assetAmt = (Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()))); //} //} //else //{ // fact.CreateLine(line, // GetAccount(Doc.ACCTTYPE_CashAsset, as1), // line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); //} } else { //fact.CreateLine(line, // GetAccount(Doc.ACCTTYPE_CashTransfer, as1), // line.GetC_Currency_ID(), decimal.Negate(line.GetAmount())); fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), (new MCashBook(Env.GetCtx(), HeaderCasbookID, null)).GetC_Currency_ID(), decimal.Negate(line.GetAmount())); SetC_CashBook_ID(temp); //if (line.GetC_Currency_ID() == (new MCashBook(Env.GetCtx(), GetC_CashBook_ID(), null)).GetC_Currency_ID())// GetC_Currency_ID()) //{ assetAmt = (Decimal.Add(assetAmt, line.GetAmount())); //} //else //{ // fact.CreateLine(line, // GetAccount(Doc.ACCTTYPE_CashAsset, as1), // line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); //} } CreateFactLine(fact, line, as1, line.GetAmount()); } else if (CashType.Equals(DocLine_Cash.CASHTYPE_CASHRECIEVEDFROM)) { int temp = GetC_CashBook_ID(); //SetC_CashBook_ID(line.Get_C_CashBook_ID()); //fact.CreateLine(line, // GetAccount(Doc.ACCTTYPE_CashTransfer, as1), // line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); //SetC_CashBook_ID(temp); //if (line.GetC_Currency_ID() == GetC_Currency_ID()) //{ //assetAmt = (Decimal.Add(assetAmt, line.GetAmount())); //} //else //{ // fact.CreateLine(line, // GetAccount(Doc.ACCTTYPE_CashAsset, as1), // line.GetC_Currency_ID(), line.GetAmount()); //} headerCashCurrency = (new MCashBook(Env.GetCtx(), HeaderCasbookID, null).GetC_Currency_ID()); childCashCurrency = (new MCashBook(Env.GetCtx(), line.Get_C_CashBook_ID(), null).GetC_Currency_ID()); headerCashOrg = (new MCashBook(Env.GetCtx(), HeaderCasbookID, null).GetAD_Org_ID()); childCashOrg = (new MCashBook(Env.GetCtx(), line.Get_C_CashBook_ID(), null).GetAD_Org_ID()); //else //{ if (headerCashCurrency != childCashCurrency) { Decimal transferdAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT Amount FROM C_CashLine WHERE C_CashLine_ID =" + line.Get_C_CashLine_Ref_ID())); Decimal recievedAmt = MConversionRate.Convert(Env.GetCtx(), line.GetAmount(), headerCashCurrency, childCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); Decimal res = Decimal.Subtract(recievedAmt, Math.Abs(transferdAmt)); if (res > 0) { TotalCurrGain = Decimal.Add(TotalCurrGain, res); } else if (res < 0) { TotalCurrLoss = Decimal.Add(TotalCurrLoss, Decimal.Negate(res)); } SetC_CashBook_ID(line.Get_C_CashBook_ID()); int OrgID = line.GetAD_Org_ID(); if (headerCashOrg != childCashOrg) { OrgID = childCashOrg; } //else //{ transferdAmt = MConversionRate.Convert(Env.GetCtx(), transferdAmt, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); //} fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), headerCashCurrency, transferdAmt, OrgID); SetC_CashBook_ID(temp); } else { SetC_CashBook_ID(line.Get_C_CashBook_ID()); if (headerCashOrg != childCashOrg) { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), childCashOrg); } else { fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashTransfer, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); } SetC_CashBook_ID(temp); } //if (headerCashOrg != childCashOrg) //{ // DataSet ds = DB.ExecuteDataset("SELECT INTERCOMPANYDUETO_ACCT,INTERCOMPANYDUEFROM_ACCT FROM C_AcctSchema_GL WHERE AD_Client_ID=" + GetAD_Client_ID()); // int dueFrom = 0; // int dueTo = 0; // if (ds != null && ds.Tables[0].Rows.Count > 0) // { // dueTo = Util.GetValueOfInt(ds.Tables[0].Rows[0]["INTERCOMPANYDUETO_ACCT"]); // dueFrom = Util.GetValueOfInt(ds.Tables[0].Rows[0]["INTERCOMPANYDUEFROM_ACCT"]); // if (dueFrom > 0 && dueTo > 0) // { // fact.CreateLine(line, MAccount.Get(Env.GetCtx(), dueFrom),line.GetC_Currency_ID(), line.GetAmount()); // fact.CreateLine(line, MAccount.Get(Env.GetCtx(), dueTo), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount())); // } // } //} //} assetAmt = (Decimal.Add(assetAmt, line.GetAmount())); CreateFactLine(fact, line, as1, line.GetAmount()); } } // lines if (TotalCurrGain != Env.ZERO) { int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='80540' AND AD_Client_ID = " + GetAD_Client_ID() + " )")); MAccount acct = MAccount.Get(Env.GetCtx(), validComID); TotalCurrGain = MConversionRate.Convert(Env.GetCtx(), TotalCurrGain, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); fact.CreateLine(null, acct, GetC_Currency_ID(), Decimal.Negate(TotalCurrGain)); } if (TotalCurrLoss != Env.ZERO) { int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )")); MAccount acct = MAccount.Get(Env.GetCtx(), validComID); TotalCurrLoss = MConversionRate.Convert(Env.GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); fact.CreateLine(null, acct, GetC_Currency_ID(), (TotalCurrLoss)); } // //// Cash Asset //fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1), // GetC_Currency_ID(), assetAmt); // Cash Asset //fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_CashAsset, as1), // GetC_Currency_ID(), assetAmt); List <Fact> facts = new List <Fact>(); facts.Add(fact); return(facts); }
/// <summary> /// Create Facts (the accounting logic) for /// CMC. /// <pre> /// Expense /// CashExpense DR /// CashAsset CR ///Receipt /// CashAsset DR /// CashReceipt CR /// Charge /// Charge DR /// CashAsset CR /// Difference /// CashDifference DR /// CashAsset CR /// Invoice /// CashAsset DR /// CashTransfer CR /// Transfer /// BankInTransit DR /// CashAsset CR /// </pre> /// </summary> /// <param name="?"></param> /// <returns>Fact</returns> public override List <Fact> CreateFacts(MAcctSchema as1) { // create Fact Header List <Fact> facts = new List <Fact>(); if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_PROFITLOSS)) { //Change By mohit // Get Assigned Accounting Schemas based on organization MProfitLossLines PLossline = new MProfitLossLines(GetCtx(), _lines[0].Get_ID(), null); MProfitLoss PLoss = new MProfitLoss(GetCtx(), PLossline.GetC_ProfitLoss_ID(), null); MAcctSchema HeaderAcctSchema = new MAcctSchema(GetCtx(), Util.GetValueOfInt(PLoss.Get_Value("C_AcctSchema_ID")), null); List <int> _ListAcctSch = new List <int>(); // Profit & Loss account shall be posted only in accounting schema selected on header (By Ashish - discussed with Mukesh sir) //_ListAcctSch = GetAcctSchemas(PLoss.GetAD_Org_ID()); _ListAcctSch.Add(HeaderAcctSchema.GetC_AcctSchema_ID()); if (_ListAcctSch.Count > 0) { int CurrencyType_ID = GetDefaultConversionType(GetAD_Client_ID(), GetAD_Org_ID()); for (int asch = 0; asch < _ListAcctSch.Count; asch++) { MAcctSchema AccountingSchema = new MAcctSchema(GetCtx(), _ListAcctSch[asch], null); // Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross); SetC_Currency_ID(GetCurrency(AccountingSchema.GetC_AcctSchema_ID())); // Commitment Fact fact = new Fact(this, AccountingSchema, Fact.POST_Actual); Decimal total = Env.ZERO, totalCredit = Env.ZERO, totalDebit = Env.ZERO; Decimal credit = Env.ZERO, debit = Env.ZERO; for (int i = 0; i < _lines.Length; i++) { DocLine dline = _lines[i]; MProfitLossLines line = new MProfitLossLines(GetCtx(), dline.Get_ID(), null); if (Util.GetValueOfInt(HeaderAcctSchema.GetC_Currency_ID()) == Util.GetValueOfInt(AccountingSchema.GetC_Currency_ID())) { credit = Util.GetValueOfDecimal(dline.GetAmtSourceCr()); debit = Util.GetValueOfDecimal(dline.GetAmtSourceDr()); } else { credit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceCr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(), PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID()); debit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceDr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(), PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID()); Util.GetValueOfDecimal(dline.GetAmtAcctDr()); } if (credit > 0) { totalCredit = Decimal.Add(totalCredit, credit); } if (debit > 0) { totalDebit = Decimal.Add(totalDebit, debit); } // Account MAccount expense = MAccount.Get(GetCtx(), GetAD_Client_ID(), GetAD_Org_ID(), AccountingSchema.GetC_AcctSchema_ID(), line.GetAccount_ID(), line.GetC_SubAcct_ID(), line.GetM_Product_ID(), line.GetC_BPartner_ID(), line.GetAD_OrgTrx_ID(), line.GetC_LocFrom_ID(), line.GetC_LocTo_ID(), line.GetC_SalesRegion_ID(), line.GetC_Project_ID(), line.GetC_Campaign_ID(), line.GetC_Activity_ID(), line.GetUser1_ID(), line.GetUser2_ID(), line.GetUserElement1_ID(), line.GetUserElement2_ID(), line.GetUserElement3_ID(), line.GetUserElement4_ID(), line.GetUserElement5_ID(), line.GetUserElement6_ID(), line.GetUserElement7_ID(), line.GetUserElement8_ID(), line.GetUserElement9_ID()); fact.CreateLine(dline, expense, GetCurrency(AccountingSchema.GetC_AcctSchema_ID()), debit, credit); } total = totalCredit - totalDebit; if (total != Env.ZERO) { int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + AccountingSchema.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID())); MAccount acct = MAccount.Get(GetCtx(), validComID); fact.CreateLine(null, acct, GetC_Currency_ID(), total); } //if (TotalCurrLoss != Env.ZERO) //{ // int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )")); // MAccount acct = MAccount.Get(GetCtx(), validComID); // TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID()); // fact.CreateLine(null, acct, // GetC_Currency_ID(), (TotalCurrLoss)); //} facts.Add(fact); } } } return(facts); }
/// <summary> /// Create Invoice Line from Shipment /// </summary> /// <param name="order">order</param> /// <param name="ship">shipment header</param> /// <param name="sLine">shipment line</param> private void CreateLine(MOrder order, MInOut ship, MInOutLine sLine) { if (_invoice == null) { _invoice = new MInvoice(order, 0, _DateInvoiced); int _CountVA009 = Env.IsModuleInstalled("VA009_") ? 1 : 0; if (_CountVA009 > 0) { int _PaymentMethod_ID = order.GetVA009_PaymentMethod_ID(); // during consolidation, payment method need to set that is defined on selected business partner. // If not defined on BP then it will set from order // during sale cycle -- VA009_PaymentMethod_ID // during purchase cycle -- VA009_PO_PaymentMethod_ID int bpPamentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT " + (order.IsSOTrx() ? " VA009_PaymentMethod_ID " : " VA009_PO_PaymentMethod_ID ") + @" FROM C_BPartner WHERE C_BPartner_ID = " + order.GetC_BPartner_ID(), null, Get_Trx())); if (_ConsolidateDocument && bpPamentMethod_ID != 0) { _PaymentMethod_ID = bpPamentMethod_ID; } if (_PaymentMethod_ID > 0) { _invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID); } } int _CountVA026 = Env.IsModuleInstalled("VA026_") ? 1 : 0; if (_CountVA026 > 0) { _invoice.SetVA026_LCDetail_ID(order.GetVA026_LCDetail_ID()); } // Added by Bharat on 29 Jan 2018 to set Inco Term from Order if (_invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0) { _invoice.SetC_IncoTerm_ID(order.GetC_IncoTerm_ID()); } if (!_invoice.Save()) { ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Could not create Invoice (s). " + pp.GetName()); } throw new Exception("Could not create Invoice (s)"); } } #region Comment Create Shipment Comment Line // Create Shipment Comment Line //if (_ship == null // || _ship.GetM_InOut_ID() != ship.GetM_InOut_ID()) //{ // MDocType dt = MDocType.Get(GetCtx(), ship.GetC_DocType_ID()); // if (_bp == null || _bp.GetC_BPartner_ID() != ship.GetC_BPartner_ID()) // { // _bp = new MBPartner(GetCtx(), ship.GetC_BPartner_ID(), Get_TrxName()); // } // // Reference: Delivery: 12345 - 12.12.12 // MClient client = MClient.Get(GetCtx(), order.GetAD_Client_ID()); // String AD_Language = client.GetAD_Language(); // if (client.IsMultiLingualDocument() && _bp.GetAD_Language() != null) // { // AD_Language = _bp.GetAD_Language(); // } // if (AD_Language == null) // { // // MessageBox.Show("Set base Language"); // //AD_Language = Language.getBaseAD_Language(); // } // //java.text.SimpleDateFormat format = DisplayType.getDateFormat // // (DisplayType.Date, Language.getLanguage(AD_Language)); // //String reference = dt.GetPrintName(_bp.GetAD_Language()) // // + ": " + ship.GetDocumentNo() // // + " - " + format.format(ship.GetMovementDate()); // String reference = dt.GetPrintName(_bp.GetAD_Language()) // + ": " + ship.GetDocumentNo() // + " - " + ship.GetMovementDate(); // _ship = ship; // // // MInvoiceLine line = new MInvoiceLine(_invoice); // line.SetIsDescription(true); // line.SetDescription(reference); // line.SetLine(_line + sLine.GetLine() - 2); // if (!line.Save()) // { // throw new Exception("Could not create Invoice Comment Line (sh)"); // } // // Optional Ship Address if not Bill Address // if (order.GetBill_Location_ID() != ship.GetC_BPartner_Location_ID()) // { // MLocation addr = MLocation.GetBPLocation(GetCtx(), ship.GetC_BPartner_Location_ID(), null); // line = new MInvoiceLine(_invoice); // line.SetIsDescription(true); // line.SetDescription(addr.ToString()); // line.SetLine(_line + sLine.GetLine() - 1); // if (!line.Save()) // { // throw new Exception("Could not create Invoice Comment Line 2 (sh)"); // } // } //} #endregion // MInvoiceLine line1 = new MInvoiceLine(_invoice); line1.SetShipLine(sLine); line1.SetQtyEntered(sLine.GetQtyEntered()); line1.SetQtyInvoiced(sLine.GetMovementQty()); line1.SetLine(_line + sLine.GetLine()); line1.SetM_AttributeSetInstance_ID(sLine.GetM_AttributeSetInstance_ID()); if (sLine.GetA_Asset_ID() > 0) { line1.SetA_Asset_ID(sLine.GetA_Asset_ID()); if (line1.Get_ColumnIndex("VAFAM_AssetCost") > 0) { int PAcctSchema_ID = 0; int pCurrency_ID = 0; MAcctSchema as1 = MClient.Get(GetCtx(), GetAD_Client_ID()).GetAcctSchema(); if (as1 != null) { PAcctSchema_ID = as1.GetC_AcctSchema_ID(); pCurrency_ID = as1.GetC_Currency_ID(); } decimal LineNetAmt = Decimal.Multiply(line1.GetPriceActual(), line1.GetQtyEntered()); decimal AssetCost = GetAssetCost(sLine.GetA_Asset_ID(), sLine.GetM_Product_ID(), PAcctSchema_ID); AssetCost = Decimal.Multiply(AssetCost, line1.GetQtyEntered()); if (LineNetAmt > 0) { LineNetAmt = MConversionRate.Convert(GetCtx(), LineNetAmt, _invoice.GetC_Currency_ID(), pCurrency_ID, _invoice.GetAD_Client_ID(), _invoice.GetAD_Org_ID()); } decimal Diff = LineNetAmt - AssetCost; line1.Set_Value("VAFAM_AssetCost", AssetCost); line1.Set_Value("VAFAM_Difference", Diff); } } if (!line1.Save()) { ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Could not create Invoice Line (s). " + pp.GetName()); } throw new Exception("Could not create Invoice Line (s)"); } // Link sLine.SetIsInvoiced(true); if (!sLine.Save()) { ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Could not update Shipment Line. " + pp.GetName()); } throw new Exception("Could not update Shipment Line"); } log.Fine(line1.ToString()); }
/************************************************************************** * Calculate/Set Tax Base Amt from Invoice Lines * @return true if tax calculated */ public bool CalculateTaxFromLines() { Decimal?taxBaseAmt = Env.ZERO; Decimal taxAmt = Env.ZERO; // bool documentLevel = GetTax().IsDocumentLevel(); MTax tax = GetTax(); // String sql = "SELECT il.LineNetAmt, COALESCE(il.TaxAmt,0), i.IsSOTrx , i.C_Currency_ID , i.DateAcct , i.C_ConversionType_ID " + "FROM C_InvoiceLine il" + " INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID) " + "WHERE il.C_Invoice_ID=" + GetC_Invoice_ID() + " AND il.C_Tax_ID=" + GetC_Tax_ID(); IDataReader idr = null; int c_Currency_ID = 0; DateTime? dateAcct = null; int c_ConversionType_ID = 0; try { idr = DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { //Get References from invoiice header c_Currency_ID = Util.GetValueOfInt(idr[3]); dateAcct = Util.GetValueOfDateTime(idr[4]); c_ConversionType_ID = Util.GetValueOfInt(idr[5]); // BaseAmt Decimal baseAmt = Util.GetValueOfDecimal(idr[0]); taxBaseAmt = Decimal.Add((Decimal)taxBaseAmt, baseAmt); // TaxAmt Decimal amt = Util.GetValueOfDecimal(idr[1]); //if (amt == null) // amt = Env.ZERO; bool isSOTrx = "Y".Equals(idr[2].ToString()); // if (documentLevel || Env.Signum(baseAmt) == 0) { amt = Env.ZERO; } else if (Env.Signum(amt) != 0 && !isSOTrx) // manually entered { ; } else // calculate line tax { amt = tax.CalculateTax(baseAmt, IsTaxIncluded(), GetPrecision()); } // taxAmt = Decimal.Add(taxAmt, amt); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "setTaxBaseAmt", e); taxBaseAmt = null; } if (taxBaseAmt == null) { return(false); } // Calculate Tax if (documentLevel || Env.Signum(taxAmt) == 0) { taxAmt = tax.CalculateTax((Decimal)taxBaseAmt, IsTaxIncluded(), GetPrecision()); } SetTaxAmt(taxAmt); // set Tax Amount in base currency if (Get_ColumnIndex("TaxBaseCurrencyAmt") >= 0) { decimal taxAmtBaseCurrency = GetTaxAmt(); int primaryAcctSchemaCurrency = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_Currency_ID FROM C_AcctSchema WHERE C_AcctSchema_ID = (SELECT c_acctschema1_id FROM ad_clientinfo WHERE ad_client_id = " + GetAD_Client_ID() + ")", null, Get_Trx())); if (c_Currency_ID != primaryAcctSchemaCurrency) { taxAmtBaseCurrency = MConversionRate.Convert(GetCtx(), GetTaxAmt(), primaryAcctSchemaCurrency, c_Currency_ID, dateAcct, c_ConversionType_ID, GetAD_Client_ID(), GetAD_Org_ID()); } SetTaxBaseCurrencyAmt(taxAmtBaseCurrency); } // Set Base if (IsTaxIncluded()) { SetTaxBaseAmt(Decimal.Subtract((Decimal)taxBaseAmt, taxAmt)); } else { SetTaxBaseAmt((Decimal)taxBaseAmt); } return(true); }
} // doIt /// <summary> /// Process Expense Line /// </summary> /// <param name="te">header</param> /// <param name="tel">line</param> /// <param name="bp">bp</param> private void ProcessLine(MTimeExpense te, MTimeExpenseLine tel, MBPartner bp) { if (_order == null) { log.Info("New Order for " + bp + ", Project=" + tel.GetC_Project_ID()); _order = new MOrder(GetCtx(), 0, Get_TrxName()); _order.SetAD_Org_ID(tel.GetAD_Org_ID()); _order.SetC_DocTypeTarget_ID(MOrder.DocSubTypeSO_Standard); // _order.SetBPartner(bp); if (_order.GetC_BPartner_Location_ID() == 0) { log.Log(Level.SEVERE, "No BP Location: " + bp); AddLog(0, te.GetDateReport(), null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName()); _order = null; return; } _order.SetM_Warehouse_ID(te.GetM_Warehouse_ID()); //Bhupendra: Add payment term // to check for if payment term is null if (bp.GetC_PaymentTerm_ID() == 0) { // set the default payment method as check _order.SetC_PaymentTerm_ID(GetPaymentTerm()); } else { _order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID()); } // Bhupendra: added a cond to check for payment method if null // Added by mohit - to set payment method and sales rep id. if (bp.GetVA009_PaymentMethod_ID() == 0) { // set the default payment method as check _order.SetVA009_PaymentMethod_ID(GetPaymentMethod()); } else { _order.SetVA009_PaymentMethod_ID(bp.GetVA009_PaymentMethod_ID()); } _order.SetSalesRep_ID(te.GetDoc_User_ID()); ////Added By Arpit asked by Surya Sir..................29-12-2015 //_order.SetSalesRep_ID(GetCtx().GetAD_User_ID()); //End if (tel.GetC_Activity_ID() != 0) { _order.SetC_Activity_ID(tel.GetC_Activity_ID()); } if (tel.GetC_Campaign_ID() != 0) { _order.SetC_Campaign_ID(tel.GetC_Campaign_ID()); } if (tel.GetC_Project_ID() != 0) { _order.SetC_Project_ID(tel.GetC_Project_ID()); // Optionally Overwrite BP Price list from Project MProject project = new MProject(GetCtx(), tel.GetC_Project_ID(), Get_TrxName()); if (project.GetM_PriceList_ID() != 0) { _order.SetM_PriceList_ID(project.GetM_PriceList_ID()); } } _order.SetSalesRep_ID(te.GetDoc_User_ID()); // if (!_order.Save()) { throw new Exception("Cannot save Order"); } } else { // Update Header info if (tel.GetC_Activity_ID() != 0 && tel.GetC_Activity_ID() != _order.GetC_Activity_ID()) { _order.SetC_Activity_ID(tel.GetC_Activity_ID()); } if (tel.GetC_Campaign_ID() != 0 && tel.GetC_Campaign_ID() != _order.GetC_Campaign_ID()) { _order.SetC_Campaign_ID(tel.GetC_Campaign_ID()); } if (!_order.Save()) { new Exception("Cannot save Order"); } } // OrderLine MOrderLine ol = new MOrderLine(_order); // if (tel.GetM_Product_ID() != 0) { ol.SetM_Product_ID(tel.GetM_Product_ID(), tel.GetC_UOM_ID()); } if (tel.GetS_ResourceAssignment_ID() != 0) { ol.SetS_ResourceAssignment_ID(tel.GetS_ResourceAssignment_ID()); } // Set charge ID if (tel.GetC_Charge_ID() != 0) { ol.SetC_Charge_ID(tel.GetC_Charge_ID()); ol.SetPriceActual(tel.GetExpenseAmt()); ol.SetQty(tel.GetQty()); } ol.SetQty(tel.GetQtyInvoiced()); // ol.SetDescription(tel.GetDescription()); // ol.SetC_Project_ID(tel.GetC_Project_ID()); ol.SetC_ProjectPhase_ID(tel.GetC_ProjectPhase_ID()); ol.SetC_ProjectTask_ID(tel.GetC_ProjectTask_ID()); ol.SetC_Activity_ID(tel.GetC_Activity_ID()); ol.SetC_Campaign_ID(tel.GetC_Campaign_ID()); // Decimal price = tel.GetPriceInvoiced(); // if (price.CompareTo(Env.ZERO) != 0) { if (tel.GetC_Currency_ID() != _order.GetC_Currency_ID()) { price = MConversionRate.Convert(GetCtx(), price, tel.GetC_Currency_ID(), _order.GetC_Currency_ID(), _order.GetAD_Client_ID(), _order.GetAD_Org_ID()); } ol.SetPrice(price); // added by Bhupendra to set the entered price ol.SetPriceEntered(price); } else { ol.SetPrice(); } if (tel.GetC_UOM_ID() != 0 && ol.GetC_UOM_ID() == 0) { ol.SetC_UOM_ID(tel.GetC_UOM_ID()); } ol.SetTax(); if (!ol.Save()) { throw new Exception("Cannot save Order Line"); } // Update TimeExpense Line tel.SetC_OrderLine_ID(ol.GetC_OrderLine_ID()); if (tel.Save()) { log.Fine("Updated " + tel + " with C_OrderLine_ID"); } else { log.Log(Level.SEVERE, "Not Updated " + tel + " with C_OrderLine_ID"); } } // processLine
/// <summary> /// Convert to Accounted Currency /// </summary> /// <returns>true if converted</returns> public bool Convert() { // Document has no currency if (GetC_Currency_ID() == Doc.NO_CURRENCY) { SetC_Currency_ID(_acctSchema.GetC_Currency_ID()); } if (_acctSchema.GetC_Currency_ID() == GetC_Currency_ID()) { SetAmtAcctDr(GetAmtSourceDr()); SetAmtAcctCr(GetAmtSourceCr()); return(true); } // Get Conversion Type from Line or Header int C_ConversionType_ID = 0; int AD_Org_ID = 0; if (_docLine != null) // get from line { C_ConversionType_ID = _docLine.GetC_ConversionType_ID(); AD_Org_ID = _docLine.GetAD_Org_ID(); } if (C_ConversionType_ID == 0) // get from header { if (_doc == null) { log.Severe("No Document VO"); return(false); } C_ConversionType_ID = _doc.GetC_ConversionType_ID(); if (AD_Org_ID == 0) { AD_Org_ID = _doc.GetAD_Org_ID(); } } DateTime?convDate = GetDateAcct(); // For sourceforge bug 1718381: Use transaction date instead of // accounting date for currency conversion when the document is Bank // Statement. Ideally this should apply to all "reconciliation" // accounting entries, but doing just Bank Statement for now to avoid // breaking other things. if (_doc is Doc_Bank) { convDate = GetDateTrx(); } SetAmtAcctDr(MConversionRate.Convert(GetCtx(), GetAmtSourceDr(), GetC_Currency_ID(), _acctSchema.GetC_Currency_ID(), convDate, C_ConversionType_ID, _doc.GetAD_Client_ID(), AD_Org_ID)); //if (GetAmtAcctDr() == null) //{ // return false; //} SetAmtAcctCr(MConversionRate.Convert(GetCtx(), GetAmtSourceCr(), GetC_Currency_ID(), _acctSchema.GetC_Currency_ID(), convDate, C_ConversionType_ID, _doc.GetAD_Client_ID(), AD_Org_ID)); return(true); }
/// <summary> /// Create Facts (the accounting logic) for /// MMS, MMR. /// <pre> /// Shipment /// CoGS (RevOrg) DR /// Inventory CR /// Shipment of Project Issue /// CoGS DR /// Project CR /// Receipt /// Inventory DR /// NotInvoicedReceipt CR /// </pre> /// </summary> /// <param name="as1">accounting schema</param> /// <returns>Fact</returns> public override List <Fact> CreateFacts(MAcctSchema as1) { // create Fact Header Fact fact = new Fact(this, as1, Fact.POST_Actual); SetC_Currency_ID(as1.GetC_Currency_ID()); // Line pointers FactLine dr = null; FactLine cr = null; // *** Sales - Shipment if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_MATERIALDELIVERY)) { for (int i = 0; i < _lines.Length; i++) { DocLine line = _lines[i]; MInOutLine sLine = new MInOutLine(GetCtx(), line.Get_ID(), null); Decimal costs = 0; if (sLine.GetA_Asset_ID() > 0) { costs = Util.GetValueOfDecimal(DB.ExecuteScalar(@"SELECT cost.CUrrentcostPrice FROM m_cost cost INNER JOIN A_Asset ass ON(ass.a_asset_ID=cost.a_asset_ID) INNER JOIN M_InOutLine IOL ON(IOL.A_Asset_ID =ass.A_Asset_ID) WHERE IOL.M_InOutLine_ID=" + sLine.GetM_InOutLine_ID() + @" ORDER By cost.created desc")); // Change if Cost not found against Asset then get Product Cost if (Env.Signum(costs) == 0) // zero costs OK { costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), true); } } else { costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), true); } if (Env.Signum(costs) == 0) // zero costs OK { MProduct product = line.GetProduct(); if (product.IsStocked()) { _error = "No Costs for " + line.GetProduct().GetName(); log.Log(Level.WARNING, _error); return(null); } else // ignore service { continue; } } if (!IsReturnTrx()) { // CoGS DR dr = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Cogs, as1), as1.GetC_Currency_ID(), costs, null); if (dr == null) { _error = "FactLine DR not created: " + line; log.Log(Level.WARNING, _error); return(null); } dr.SetM_Locator_ID(line.GetM_Locator_ID()); dr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc dr.SetAD_Org_ID(line.GetOrder_Org_ID()); // Revenue X-Org dr.SetQty(Decimal.Negate(line.GetQty().Value)); // Inventory CR cr = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Asset, as1), as1.GetC_Currency_ID(), null, costs); if (cr == null) { _error = "FactLine CR not created: " + line; log.Log(Level.WARNING, _error); return(null); } cr.SetM_Locator_ID(line.GetM_Locator_ID()); cr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc } else // Reverse accounting entries for returns { // CoGS CR cr = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Cogs, as1), as1.GetC_Currency_ID(), null, costs); if (cr == null) { _error = "FactLine CR not created: " + line; log.Log(Level.WARNING, _error); return(null); } cr.SetM_Locator_ID(line.GetM_Locator_ID()); cr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc cr.SetAD_Org_ID(line.GetOrder_Org_ID()); // Revenue X-Org cr.SetQty(Decimal.Negate(line.GetQty().Value)); // Inventory DR dr = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Asset, as1), as1.GetC_Currency_ID(), costs, null); if (dr == null) { _error = "FactLine DR not created: " + line; log.Log(Level.WARNING, _error); return(null); } dr.SetM_Locator_ID(line.GetM_Locator_ID()); dr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc } // if (line.GetM_Product_ID() != 0) { if (!IsPosted()) { MCostDetail.CreateShipment(as1, line.GetAD_Org_ID(), line.GetM_Product_ID(), line.GetM_AttributeSetInstance_ID(), line.Get_ID(), 0, costs, IsReturnTrx() ? Decimal.Negate(line.GetQty().Value) : line.GetQty().Value, line.GetDescription(), true, GetTrx(), GetRectifyingProcess()); } } } // for all lines if (!IsPosted()) { UpdateProductInfo(as1.GetC_AcctSchema_ID()); // only for SO! } } // Shipment // *** Purchasing - Receipt else if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_MATERIALRECEIPT)) { for (int i = 0; i < _lines.Length; i++) { Decimal costs = 0; DocLine line = _lines[i]; MProduct product = line.GetProduct(); /***********************************************************/ //05,Sep,2011 //Special Check to restic Price varience posting in case of //AvarageInvoice Selected on product Category.Then Neglact the AverageInvoice Cost MProductCategoryAcct pca = MProductCategoryAcct.Get(product.GetCtx(), product.GetM_Product_Category_ID(), as1.GetC_AcctSchema_ID(), null); try { if ((pca.GetCostingMethod() == "I" || as1.GetCostingMethod() == "I") && as1.IsNotPostPOVariance() && line.GetC_OrderLine_ID() > 0) { MOrderLine oLine = new MOrderLine(product.GetCtx(), line.GetC_OrderLine_ID(), null); MOrder order = new MOrder(product.GetCtx(), oLine.GetC_Order_ID(), null); Decimal convertedCost = MConversionRate.Convert(product.GetCtx(), oLine.GetPriceEntered(), order.GetC_Currency_ID(), as1.GetC_Currency_ID(), line.GetDateAcct(), order.GetC_ConversionType_ID(), oLine.GetAD_Client_ID(), line.GetAD_Org_ID()); costs = Decimal.Multiply(convertedCost, oLine.GetQtyEntered()); } else { costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), false); // non-zero costs } } catch (Exception ex) { log.SaveError("AccountSchemaColumnError", ex); costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), false); // non-zero costs } /***********************************************************/ //Decimal costs = costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), false); // non-zero costs if (Env.Signum(costs) == 0) { _error = "Resubmit - No Costs for " + product.GetName(); log.Log(Level.WARNING, _error); return(null); } // Inventory/Asset DR MAccount assets = line.GetAccount(ProductCost.ACCTTYPE_P_Asset, as1); if (product.IsService()) { assets = line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1); } if (!IsReturnTrx()) { // Inventory/Asset DR dr = fact.CreateLine(line, assets, as1.GetC_Currency_ID(), costs, null); if (dr == null) { _error = "DR not created: " + line; log.Log(Level.WARNING, _error); return(null); } dr.SetM_Locator_ID(line.GetM_Locator_ID()); dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc dr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc // NotInvoicedReceipt CR cr = fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_NotInvoicedReceipts, as1), as1.GetC_Currency_ID(), null, costs); if (cr == null) { _error = "CR not created: " + line; log.Log(Level.WARNING, _error); return(null); } cr.SetM_Locator_ID(line.GetM_Locator_ID()); cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc cr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc cr.SetQty(Decimal.Negate(line.GetQty().Value)); } else // reverse accounting entries for returns { // Inventory/Asset CR cr = fact.CreateLine(line, assets, as1.GetC_Currency_ID(), null, costs); if (cr == null) { _error = "CR not created: " + line; log.Log(Level.WARNING, _error); return(null); } cr.SetM_Locator_ID(line.GetM_Locator_ID()); cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc cr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc // NotInvoicedReceipt DR dr = fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_NotInvoicedReceipts, as1), as1.GetC_Currency_ID(), costs, null); if (dr == null) { _error = "DR not created: " + line; log.Log(Level.WARNING, _error); return(null); } dr.SetM_Locator_ID(line.GetM_Locator_ID()); dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc dr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc dr.SetQty(Decimal.Negate(line.GetQty().Value)); } } } // Receipt else { _error = "DocumentType unknown: " + GetDocumentType(); log.Log(Level.SEVERE, _error); return(null); } // List <Fact> facts = new List <Fact>(); facts.Add(fact); return(facts); }