/// <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);
}