/// <summary>
/// is used to save data in case of Full move container / full qty move
/// </summary>
/// <param name="movementId">movement header refernce</param>
/// <param name="fromLocatorId">From Locator - from where we have to move product</param>
/// <param name="fromContainerId">From Container - from which container, we have to move product</param>
/// <param name="toLocatorId">To Locator - where we are moving product</param>
/// <param name="toContainerId">To container - in which container we are moving product</param>
/// <param name="lineNo"></param>
/// <param name="isMoveFullContainerQty">Is Container also move with Product</param>
/// <param name="trx">Self created Trx</param>
/// <returns>Message : lines inserted or not</returns>
public String SaveMoveLinewithFullContainer(int movementId, int fromLocatorId, int fromContainerId, int toLocatorId, int toContainerId, int lineNo, bool isMoveFullContainerQty, Trx trx)
{
MMovement movement = new MMovement(_ctx, movementId, trx);
string childContainerId = null;
StringBuilder error = new StringBuilder();
// Get Path upto selected container
string sql = @"SELECT sys_connect_by_path(m_productcontainer_id,'->') tree
FROM m_productcontainer
WHERE m_productcontainer_id = " + fromContainerId + @"
START WITH ref_m_container_id IS NULL CONNECT BY prior m_productcontainer_id = ref_m_container_id
ORDER BY tree ";
string pathContainer = Util.GetValueOfString(DB.ExecuteScalar(sql, null, trx));
// child records with Parent Id
if (!isMoveFullContainerQty)
{
sql = @"SELECT tree, m_productcontainer_id FROM
(SELECT sys_connect_by_path(m_productcontainer_id,'->') tree , m_productcontainer_id
FROM m_productcontainer
START WITH ref_m_container_id IS NULL
CONNECT BY prior m_productcontainer_id = ref_m_container_id
ORDER BY tree
)
WHERE tree LIKE ('" + pathContainer + "%') ";
DataSet ds = DB.ExecuteDataset(sql, null, trx);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (String.IsNullOrEmpty(childContainerId))
{
childContainerId = Util.GetValueOfString(ds.Tables[0].Rows[i]["m_productcontainer_id"]);
}
else
{
childContainerId += "," + Util.GetValueOfString(ds.Tables[0].Rows[i]["m_productcontainer_id"]);
}
}
}
ds.Dispose();
}
else
{
childContainerId = fromContainerId.ToString();
}
// check is same container already moved to another container
// Ex :: p1 -> c1 and p1 -> c2
// OR also check -- is any other container is moving into exist target container
// Ex :: p1 -> c1 and p2 -> p1
if (!isMoveFullContainerQty)
{
if (!IsContainerMoved(movementId, pathContainer, childContainerId, toContainerId, trx))
{
return(Msg.GetMsg(_ctx, "VIS_AlreadyMoved"));
}
}
// not to move Parent container to its child container
if (toContainerId > 0 && childContainerId.Contains(toContainerId.ToString()))
{
//Parent cant be Move to its own child
return(Msg.GetMsg(_ctx, "VIS_cantMoveParentTochild"));
}
// Get All records of Parent Container and child container
sql = @"SELECT * FROM (
SELECT p.M_PRODUCT_ID, p.NAME, p.C_UOM_ID, u.Name AS UomName, t.M_ATTRIBUTESETINSTANCE_ID, t.M_ProductContainer_ID,
SUM(t.ContainerCurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) AS ContainerCurrentQty
FROM M_Transaction t
INNER JOIN M_Product p ON p.M_Product_ID = t.M_Product_ID
INNER JOIN C_UOM u ON u.C_UOM_ID = p.C_UOM_ID
WHERE t.IsActive = 'Y' AND NVL(t.M_ProductContainer_ID, 0) IN ( " + childContainerId +
@" ) AND t.MovementDate <=" + GlobalVariable.TO_DATE(movement.GetMovementDate(), true) + @"
AND t.M_Locator_ID = " + fromLocatorId + @"
AND t.AD_Client_ID = " + movement.GetAD_Client_ID() + @"
GROUP BY p.M_PRODUCT_ID, p.NAME, p.C_UOM_ID, u.Name, t.M_ATTRIBUTESETINSTANCE_ID, t.M_ProductContainer_ID
) t WHERE ContainerCurrentQty <> 0 ";
DataSet dsRecords = DB.ExecuteDataset(sql, null, trx);
if (dsRecords != null && dsRecords.Tables.Count > 0 && dsRecords.Tables[0].Rows.Count > 0)
{
int movementlineId = 0;
MMovementLine moveline = null;
MProduct product = null;
for (int i = 0; i < dsRecords.Tables[0].Rows.Count; i++)
{
movementlineId = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(M_MovementLine_ID, 0) AS M_Movement_ID FROM M_MovementLine WHERE
M_Movement_ID = " + Util.GetValueOfInt(movementId) +
@" AND M_Product_ID = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]) +
@" AND NVL(M_AttributeSetInstance_ID, 0) = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]) +
@" AND M_Locator_ID = " + Util.GetValueOfInt(fromLocatorId) +
@" AND NVL(M_ProductContainer_ID, 0) = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]) +
@" AND M_LocatorTo_ID = " + Util.GetValueOfInt(toLocatorId) +
@" AND NVL(Ref_M_ProductContainerTo_ID, 0) = " + toContainerId +
@" AND AD_Org_ID = " + movement.GetAD_Org_ID()));
if (movementlineId > 0)
{
moveline = new MMovementLine(_ctx, movementlineId, trx);
}
else
{
moveline = new MMovementLine(_ctx, 0, trx);
}
if (movementlineId == 0)
{
#region Create new record of movement line
lineNo += 10;
moveline.SetAD_Client_ID(movement.GetAD_Client_ID());
moveline.SetAD_Org_ID(movement.GetAD_Org_ID());
moveline.SetM_Movement_ID(movement.GetM_Movement_ID());
moveline.SetLine(lineNo);
moveline.SetM_Product_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]));
moveline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
moveline.SetC_UOM_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["C_UOM_ID"]));
moveline.SetM_Locator_ID(fromLocatorId);
moveline.SetM_LocatorTo_ID(toLocatorId);
moveline.SetM_ProductContainer_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]));
moveline.SetRef_M_ProductContainerTo_ID(toContainerId);
moveline.SetQtyEntered(Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"]));
moveline.SetMovementQty(Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"]));
moveline.SetMoveFullContainer(isMoveFullContainerQty ? false : true);
// when move full container, then only need to update IsParentMove and Target container (which represent - to which container we are moving)
// and set true value on those line which container are moving, not on its child container
if (!isMoveFullContainerQty)
{
moveline.SetIsParentMove(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]) == fromContainerId ? true : false);
moveline.SetTargetContainer_ID(fromContainerId);
}
#endregion
}
else
{
#region Update record of movement line
moveline.SetQtyEntered(Decimal.Add(moveline.GetQtyEntered(), Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"])));
moveline.SetMovementQty(Decimal.Add(moveline.GetMovementQty(), Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"])));
moveline.SetMoveFullContainer(true);
#endregion
}
if (!moveline.Save(trx))
{
#region Save error catch and rollback
product = MProduct.Get(_ctx, Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]));
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
_log.SaveError("Movement line not inserted through Move Container Form : ", pp.GetName());
if (String.IsNullOrEmpty(error.ToString()))
{
error.Append(Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
else
{
error.Append(" , " + Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
}
trx.Rollback();
#endregion
}
else
{
trx.Commit();
}
}
}
else
{
return(Msg.GetMsg(_ctx, "VIS_ContainerhaveNoRecord"));
}
return(String.IsNullOrEmpty(error.ToString()) ? "VIS_SuccessFullyInserted" : error.ToString());
}
/// <SUMmary>
/// Team Forecast Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int TeamForecastProduct()
{
sql = @"SELECT fl.M_Product_ID,fl.M_AttributeSetInstance_ID,fl.qtyentered,fl.BaseQty,f.C_Forecast_ID,
C_ForecastLine_ID,f.C_Period_ID,fl.C_UOM_ID,NVL(pricestd,0) AS Price,f.C_Currency_ID
FROM C_Forecast f " +
" INNER JOIN C_Forecastline fl ON fl.c_forecast_id = f.c_forecast_id " +
" WHERE f.c_period_id = " + C_Period_ID + " AND f.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND f.isactive = 'Y' AND f.processed = 'Y'" +
" AND C_ForecastLine_ID NOT IN (SELECT C_ForecastLine_ID FROM VA073_MasterForecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(fl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(mf.GetCtx()).AddAccessSQL(sql, "C_Forecast", true, true); // fully qualified - RO
dsForecast = new DataSet();
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsForecast.Tables[0].Rows.Count; i++)
{
//Create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, 0, 0, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Forecast_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Period_ID"]), mfLine.GetC_UOM_ID(),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["qtyentered"]),
ConvertedAmt, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]) + " " + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]));
}
}
else
{
//Update quantities AND Price at Product line
Count++;
LineNo += 10;
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundForecast"));
}
return(Count);
}
/// <summary>
/// Create Invoice.
/// </summary>
/// <returns>document no</returns>
protected override String DoIt()
{
//log.info("M_InOut_ID=" + _M_InOut_ID
// + ", M_PriceList_ID=" + _M_PriceList_ID
// + ", InvoiceDocumentNo=" + _InvoiceDocumentNo);
// Message Display Wrong Done Vikas and Assigned by Gurinder
int count = Util.GetValueOfInt(DB.ExecuteScalar(" SELECT Count(*) FROM M_Inout WHERE ISSOTRX='Y' AND M_Inout_ID=" + GetRecord_ID()));
MInOut ship = null;
if (count > 0)
{
if (_M_InOut_ID == 0)
{
throw new ArgumentException("No Shipment");
}
//
ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx());
if (ship.Get_ID() == 0)
{
throw new ArgumentException("Shipment not found");
}
if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus()))
{
// JID_0750: done by Bharat on 05 Feb 2019 if Customer Return document and status is not complete it should give message "Customer Return Not Completed".
if (ship.IsReturnTrx())
{
throw new ArgumentException("Customer Return Not Completed");
}
else
{
throw new ArgumentException("Shipment Not Completed");
}
}
}
else
{
if (_M_InOut_ID == 0)
{
throw new ArgumentException("No Material Receipt");
}
//
ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx());
if (ship.Get_ID() == 0)
{
throw new ArgumentException("Material Receipt not found");
}
if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus()))
{
// JID_0750: done by Bharat on 05 Feb 2019 if Return to vendor document and status is not complete it should give message "Return To Vendor Not Completed".
if (ship.IsReturnTrx())
{
throw new ArgumentException("Return To Vendor Not Completed");
}
else
{
throw new ArgumentException("Material Receipt Not Completed");
}
}
}
//***********************END*****************************//
MInvoice invoice = new MInvoice(ship, null);
//-------------Column Added by Anuj----------------------
//int _CountVA009 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA009_' AND IsActive = 'Y'"));
int _CountVA009 = Env.IsModuleInstalled("VA009_") ? 1 : 0;
// Code by Mohit Amortization process
//int _CountVA038 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA038_' AND IsActive = 'Y'"));
int _CountVA038 = Env.IsModuleInstalled("VA038_") ? 1 : 0;
// End Amortization Code
if (_CountVA009 > 0)
{
int _PaymentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select VA009_PaymentMethod_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID()));
if (_PaymentMethod_ID > 0)
{
invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID);
}
}
//-------------Column Added by Anuj----------------------
// added by Amit 26-may-2016
//int _CountVA026 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA026_' AND IsActive = 'Y'"));
int _CountVA026 = Env.IsModuleInstalled("VA026_") ? 1 : 0;
if (_CountVA026 > 0)
{
MOrder order = new MOrder(GetCtx(), ship.GetC_Order_ID(), Get_Trx());
if (order != null && order.GetC_Order_ID() > 0)
{
invoice.SetVA026_LCDetail_ID(order.GetVA026_LCDetail_ID());
}
}
//end
if (ship.IsReturnTrx())
{
if (!ship.IsSOTrx())
{
// Purchase Return
// set target document from documnet type window -- based on documnet type available on material receipt / return to vendor
if (invoice.GetC_DocTypeTarget_ID() == 0)
{
int C_DocTypeTarget_ID = DB.GetSQLValue(null,
"SELECT C_DocTypeInvoice_ID FROM C_DocType WHERE C_DocType_ID=@param1",
ship.GetC_DocType_ID());
if (C_DocTypeTarget_ID >= 0)
{
invoice.SetC_DocTypeTarget_ID(C_DocTypeTarget_ID);
}
else
{
invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO);
}
}
invoice.SetIsReturnTrx(ship.IsReturnTrx());
invoice.SetIsSOTrx(ship.IsSOTrx());
}
else
{
// Sales Return
if (ship.GetC_Order_ID() >= 0)
{
MOrder order = new MOrder(GetCtx(), ship.GetC_Order_ID(), Get_Trx());
MDocType dt = MDocType.Get(GetCtx(), order.GetC_DocType_ID());
if (dt.GetC_DocTypeInvoice_ID() != 0)
{
invoice.SetC_DocTypeTarget_ID(dt.GetC_DocTypeInvoice_ID(), true);
}
else
{
invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO);
}
}
else
{
invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO);
}
}
}
if (_M_PriceList_ID != 0)
{
invoice.SetM_PriceList_ID(_M_PriceList_ID);
}
if (_InvoiceDocumentNo != null && _InvoiceDocumentNo.Length > 0)
{
invoice.SetDocumentNo(_InvoiceDocumentNo);
}
// Added by Bharat on 30 Jan 2018 to set Inco Term from Order
if (invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0)
{
invoice.SetC_IncoTerm_ID(ship.GetC_IncoTerm_ID());
}
//To get Payment Rule and set the Payment method
if (invoice.GetPaymentRule() != "")
{
invoice.SetPaymentMethod(invoice.GetPaymentRule());
}
if (!invoice.Save())
{
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
//return GetReterivedError(invoice, "Cannot save Invoice");
}
MInOutLine[] shipLines = ship.GetLines(false);
DateTime? AmortStartDate = null;
DateTime? AmortEndDate = null;
count = 0;
DataSet ds = null;
for (int i = 0; i < shipLines.Length; i++)
{
MInOutLine sLine = shipLines[i];
// Changes done by Bharat on 06 July 2017 restrict to create invoice if Invoice already created against that for same quantity
string sql = @"SELECT ml.QtyEntered - SUM(COALESCE(li.QtyEntered,0)) as QtyEntered, ml.MovementQty-SUM(COALESCE(li.QtyInvoiced,0)) as QtyInvoiced
FROM M_InOutLine ml INNER JOIN C_InvoiceLine li ON li.M_InOutLine_ID = ml.M_InOutLine_ID INNER JOIN C_Invoice ci ON ci.C_Invoice_ID = li.C_Invoice_ID
WHERE ci.DocStatus NOT IN ('VO', 'RE') AND ml.M_InOutLine_ID =" + sLine.GetM_InOutLine_ID() + " GROUP BY ml.MovementQty, ml.QtyEntered";
ds = DB.ExecuteDataset(sql, null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
decimal qtyEntered = Util.GetValueOfDecimal(ds.Tables[0].Rows[0][0]);
decimal qtyInvoiced = Util.GetValueOfDecimal(ds.Tables[0].Rows[0][1]);
if (qtyEntered <= 0)
{
ds.Dispose();
log.Info("Invoice Line already exist for Receipt Line ID - " + sLine.GetM_InOutLine_ID());
continue;
}
else
{
MInvoiceLine line = new MInvoiceLine(invoice);
line.SetShipLine(sLine);
//line.SetQtyEntered(sLine.GetQtyEntered());
//line.SetQtyInvoiced(sLine.GetMovementQty());
line.SetQtyEntered(qtyEntered);
line.SetQtyInvoiced(qtyInvoiced);
// Change By Mohit Amortization process -------------
if (_CountVA038 > 0)
{
if (line.GetM_Product_ID() > 0)
{
MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName());
if (Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")) > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")));
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")));
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
if (line.GetC_Charge_ID() > 0)
{
MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName());
if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
}
// End Change Amortization process--------------
if (!line.Save())
{
//return GetReterivedError(line, "Cannot save Invoice Line");
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
count++;
}
ds.Dispose();
}
else
{
MInvoiceLine line = new MInvoiceLine(invoice);
line.SetShipLine(sLine);
line.SetQtyEntered(sLine.GetQtyEntered());
line.SetQtyInvoiced(sLine.GetMovementQty());
line.Set_ValueNoCheck("IsDropShip", sLine.Get_Value("IsDropShip")); //Arpit Rai 20-Sept-2017
// Change By Mohit Amortization process -------------
if (_CountVA038 > 0)
{
if (line.GetM_Product_ID() > 0)
{
MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName());
if (Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")) > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")));
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")));
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
if (line.GetC_Charge_ID() > 0)
{
MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName());
if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
}
// End Change Amortization process--------------
if (!line.Save())
{
//return GetReterivedError(line, "Cannot save Invoice Line");
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
count++;
}
}
#region [Enhancement for Charges Distribution/Generation by Sukhwinder on 13 December 2017]
if (_GenerateCharges && count > 0)
{
StringBuilder OrderSql = new StringBuilder();
OrderSql.Append(" SELECT CO.C_ORDER_ID, "
+ " SUM(ML.QTYENTERED) AS MRLINEQTY, "
+ " SUM(OL.QTYENTERED) AS ORDERQTY "
+ " FROM M_INOUTLINE ML "
+ " INNER JOIN C_ORDERLINE OL "
+ " ON OL.C_ORDERLINE_ID = ML.C_ORDERLINE_ID "
+ " INNER JOIN C_ORDER CO "
+ " ON CO.C_ORDER_ID = OL.C_ORDER_ID "
+ " WHERE ML.M_INOUT_ID = " + _M_InOut_ID
+ " AND (OL.C_CHARGE_ID IS NULL "
+ " OR OL.C_CHARGE_ID = 0) "
+ " GROUP BY CO.C_ORDER_ID ");
DataSet OrderDS = DB.ExecuteDataset(OrderSql.ToString(), null, Get_Trx());
if (OrderDS != null && OrderDS.Tables[0].Rows.Count > 0)
{
StringBuilder ChargesSql = new StringBuilder();
for (int index = 0; index < OrderDS.Tables[0].Rows.Count; index++)
{
ds = null;
ChargesSql.Clear();
ChargesSql.Append(" SELECT C_CHARGE_ID, "
+ " C_ORDERLINE_ID, "
+ " C_ORDER_ID, "
+ " C_CURRENCY_ID, "
+ " PRICEENTERED, "
+ " PRICEACTUAL, "
+ " LINENETAMT, "
+ " QTYENTERED, "
+ " C_UOM_ID, "
+ " C_Tax_ID, "
+ " IsDropShip "
+ " FROM C_ORDERLINE "
+ " WHERE C_ORDER_ID IN "
+ " ( " + Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["C_ORDER_ID"])
+ " ) "
+ " AND C_CHARGE_ID IS NOT NULL "
+ " AND C_CHARGE_ID > 0 ");
ds = DB.ExecuteDataset(ChargesSql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MInvoiceLine line = new MInvoiceLine(invoice);
line.SetQty(1);
line.SetQtyEntered(1);
line.SetQtyInvoiced(1);
line.SetOrderLine(new MOrderLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ORDERLINE_ID"]), Get_Trx()));
line.SetC_Charge_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_CHARGE_ID"]));
line.SetC_UOM_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_UOM_ID"]));
line.SetC_Tax_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_TAX_ID"]));
decimal SumOfQty = 0;
if (Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]) == 0)
{
SumOfQty = 1;
}
else
{
SumOfQty = Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]);
}
decimal amt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["LINENETAMT"]) / SumOfQty) * Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["MRLINEQTY"]);
line.SetPrice(Decimal.Round(amt, 3));
line.SetPriceActual(Decimal.Round(amt, 3));
line.SetPriceEntered(Decimal.Round(amt, 3));
line.SetLineNetAmt(Decimal.Round(amt, 3));
line.Set_ValueNoCheck("IsDropShip", Util.GetValueOfString(ds.Tables[0].Rows[i]["ISDROPSHIP"]));
if (_CountVA038 > 0)
{
if (line.GetC_Charge_ID() > 0)
{
MCharge charge = new MCharge(GetCtx(), line.GetC_Charge_ID(), Get_TrxName());
if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
}
if (!line.Save())
{
//return GetReterivedError(line, "Cannot save Invoice Line");
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
}
}
}
}
}
#endregion
if (count > 0)
{
return(invoice.GetDocumentNo());
}
else
{
Get_Trx().Rollback();
return(Msg.GetMsg(GetCtx(), "InvoiceExist"));
}
}
protected override String DoIt()
{
//int C_Contract_ID = 0;
String Sql = "SELECT C_OrderLine_ID FROM C_OrderLine WHERE C_Order_ID=" + orderID + " AND CreateServiceContract='N' AND IsContract='Y' AND IsActive='Y'";
dr = DB.ExecuteReader(Sql);
try
{
VAdvantage.Model.X_C_Order order = new VAdvantage.Model.X_C_Order(GetCtx(), orderID, Get_TrxName());
string DocStatus = order.GetDocStatus();
if (DocStatus != "CO")
{
if (dr != null)
{
dr.Close();
dr = null;
}
return(Msg.GetMsg(GetCtx(), "FirstCompleteOrder"));
}
while (dr.Read())
{
VAdvantage.Model.X_C_OrderLine line = new VAdvantage.Model.X_C_OrderLine(GetCtx(), Util.GetValueOfInt(dr[0]), Get_TrxName());
if (line.IsContract() && line.GetC_Contract_ID() == 0)
{
VAdvantage.Model.X_C_Contract contact = new VAdvantage.Model.X_C_Contract(GetCtx(), 0, Get_TrxName());
int M_PriceList_ID = Util.GetValueOfInt(order.GetM_PriceList_ID());
//Neha---Commented code because object created but not used in further class---04 Sep,2018
//VAdvantage.Model.MProductPricing pp = new VAdvantage.Model.MProductPricing(GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID(),
// line.GetM_Product_ID(), order.GetC_BPartner_ID(), line.GetQtyOrdered(), true);
//pp.SetM_PriceList_ID(M_PriceList_ID);
//VAdvantage.Model.MProduct prd = new VAdvantage.Model.MProduct(GetCtx(), line.GetM_Product_ID(), null);
//pp.SetC_UOM_ID(prd.GetC_UOM_ID());
string sql = "SELECT pl.IsTaxIncluded,pl.EnforcePriceLimit,pl.C_Currency_ID,c.StdPrecision,"
+ "plv.M_PriceList_Version_ID,plv.ValidFrom "
+ "FROM M_PriceList pl,C_Currency c,M_PriceList_Version plv "
+ "WHERE pl.C_Currency_ID=c.C_Currency_ID"
+ " AND pl.M_PriceList_ID=plv.M_PriceList_ID"
+ " AND pl.M_PriceList_ID=" + M_PriceList_ID // 1
+ "ORDER BY plv.ValidFrom DESC";
//int M_PriceList_Version_ID = 0;
int C_Currency_ID = 0;
DataSet ds = DB.ExecuteDataset(sql, null, Get_TrxName());
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr1 = ds.Tables[0].Rows[i];
// Tax Included
// bool isTaxIncluded = Util.GetValueOfBool(ds.Tables[0].Rows[i]["IsTaxIncluded"]);
// Price Limit Enforce
// bool isTaxIncluded = Util.GetValueOfBool(ds.Tables[0].Rows[i]["IsTaxIncluded"]);
// Currency
// int ii = Util.GetValueOfInt(dr[2].ToString());
C_Currency_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Currency_ID"]);
// int prislst = Util.GetValueOfInt(dr[4].ToString());
// PriceList Version
//M_PriceList_Version_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["M_PriceList_Version_ID"]);
}
//int M_PriceList_Version_ID = GetCtx().GetContextAsInt(WindowNo, "M_PriceList_Version_ID");
//pp.SetM_PriceList_Version_ID(M_PriceList_Version_ID);
//Neha---Set Tenant,Organization from Sales Order---11 Sep,2018
contact.SetAD_Client_ID(order.GetAD_Client_ID());
contact.SetAD_Org_ID(order.GetAD_Org_ID());
//---------------------End ------------------
contact.SetDescription(order.GetDescription());
contact.SetC_Order_ID(order.GetC_Order_ID());
contact.SetC_OrderLine_ID(line.GetC_OrderLine_ID());
contact.SetStartDate(line.GetStartDate());
contact.SetBillStartDate(line.GetStartDate());
contact.SetEndDate(line.GetEndDate());
contact.SetC_BPartner_ID(order.GetC_BPartner_ID());
contact.SetBill_Location_ID(order.GetBill_Location_ID());
contact.SetBill_User_ID(order.GetBill_User_ID());
contact.SetSalesRep_ID(order.GetSalesRep_ID());
contact.SetC_Currency_ID(line.GetC_Currency_ID());
contact.SetC_ConversionType_ID(order.GetC_ConversionType_ID());
contact.SetC_PaymentTerm_ID(order.GetC_PaymentTerm_ID());
contact.SetM_PriceList_ID(order.GetM_PriceList_ID());
contact.SetC_Frequency_ID(line.GetC_Frequency_ID());
//Neha--Set List Price,Price,Unit Price,Discount from Order Line--4 Sep,2018
//contact.SetPriceList(pp.GetPriceList());
//contact.SetPriceActual(pp.GetPriceStd());
//contact.SetPriceEntered(pp.GetPriceStd());
contact.SetPriceList(line.GetPriceList());
contact.SetPriceActual(line.GetPriceActual());
contact.SetPriceEntered(line.GetPriceEntered());
contact.SetQtyEntered(line.GetQtyPerCycle());
//Decimal discount = Decimal.Round(Decimal.Divide(Decimal.Multiply(Decimal.Subtract(pp.GetPriceList(), pp.GetPriceStd()), new Decimal(100)), pp.GetPriceList()), 2);
contact.SetDiscount(line.GetDiscount());
//------------------End----------------------------
//contact.SetGrandTotal(
// invoice Count Start
//DateTime SDate = (DateTime)(line.GetStartDate());
//DateTime Edate = (DateTime)(line.GetEndDate());
//int frequency = Util.GetValueOfInt(line.GetC_Frequency_ID());
//string PSql = "Select NoOfDays from C_Frequency where C_Frequency_ID=" + frequency;
//int days = Util.GetValueOfInt(DB.ExecuteScalar(PSql, null, null));
//int totaldays = (Edate - SDate).Days;
//int count = totaldays / days;
contact.SetTotalInvoice(line.GetNoofCycle());
//invoice Count end
contact.SetC_Project_ID(order.GetC_Project_ID());
// contact.SetPriceList(line.GetPriceList());
//contact.SetPriceActual(line.GetPriceActual());
contact.SetC_UOM_ID(line.GetC_UOM_ID());
contact.SetM_Product_ID(line.GetM_Product_ID());
// Added by Vivek on 21/11/2017 asigned by Pradeep
contact.SetM_AttributeSetInstance_ID(line.GetM_AttributeSetInstance_ID());
// contact.SetPriceEntered(line.GetPriceEntered());
//contact.SetQtyEntered(line.GetQtyEntered());
// contact.SetDiscount(line.GetDiscount());
contact.SetC_Tax_ID(line.GetC_Tax_ID());
contact.SetC_Campaign_ID(order.GetC_Campaign_ID());
//Neha---Calculate TaxAmt,GrandTotal,Line Amount on the basis of Actual Price(Sales Order Line)--04 Sep,2018
sql = "SELECT Rate FROM C_Tax WHERE C_Tax_ID = " + line.GetC_Tax_ID();
Decimal?rate = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_TrxName()));
//Decimal? amt = Decimal.Multiply(pp.GetPriceStd(), (Decimal.Divide(rate.Value, 100)));
Decimal?amt = Decimal.Multiply(line.GetPriceActual(), (Decimal.Divide(rate.Value, 100)));
amt = Decimal.Round(amt.Value, 2, MidpointRounding.AwayFromZero);
Decimal?taxAmt = Decimal.Multiply(amt.Value, line.GetQtyPerCycle());
contact.SetTaxAmt(taxAmt);
//contact.SetGrandTotal(Decimal.Add(Decimal.Multiply(line.GetQtyPerCycle(), pp.GetPriceStd()), taxAmt.Value));
contact.SetGrandTotal(Decimal.Add(Decimal.Multiply(line.GetQtyPerCycle(), line.GetPriceActual()), taxAmt.Value));
//contact.SetLineNetAmt(Decimal.Multiply(line.GetQtyPerCycle(), pp.GetPriceStd()));
contact.SetLineNetAmt(Decimal.Multiply(line.GetQtyPerCycle(), line.GetPriceActual()));
//-------------------------End--------------------------
contact.SetDocStatus("DR");
contact.SetRenewContract("N");
if (!contact.Save())
{
//Neha----If Service Contract not saved then will show the exception---17 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Service Contract. " + pp.GetName());
}
throw new ArgumentException("Cannot save Service Contract");
}
else
{
if (!line.Save())
{
//Neha----If Order Line not saved then will show the exception---17 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Order Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Order Line");
}
//Neha---Set CreateServiceContract,Order Line ID on Order Line tab---17 Sep,2018
String _qry = "UPDATE C_ORDERLINE SET C_CONTRACT_ID=" + contact.GetC_Contract_ID() + " ,CreateServiceContract='Y' WHERE C_ORDERLINE_ID=" + line.GetC_OrderLine_ID();
DB.ExecuteScalar(_qry, null, Get_TrxName());
}
//C_Contract_ID = contact.GetC_Contract_ID();
}
}
dr.Close();
order.SetCreateServiceContract("Y");
if (!order.Save())
{
}
return(Msg.GetMsg(GetCtx(), "ServiceContractGenerationDone"));
}
catch (Exception ex)
{
if (dr != null)
{
dr.Close();
dr = null;
}
return(Msg.GetMsg(GetCtx(), ex.Message));
}
// return "";
}
/// <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);
}
//Thread worker = null;
/// <summary>
/// Start Workflow and Wait for completion.
/// </summary>
/// <param name="pi">process info with Record_ID record for the workflow</param>
/// <returns>process</returns>
public MWFProcess StartWait(ProcessInfo pi)
{
const int SLEEP = 500; // 1/2 sec
const int MAXLOOPS = 160; // 50;// 30; // 15 sec
//
MWFProcess process = Start(pi);
if (process == null)
{
return(null);
}
//Causes the currently executing thread object to temporarily pause
//and allow other threads to execute.
//Thread.yield();
Thread.Sleep(0);
StateEngine state = process.GetState();
//worker = new Thread(new ThreadStart(process.Run));
//worker.Start();
int loops = 0;
while (!state.IsClosed() && !state.IsSuspended() && !state.IsBackground())
{
if (loops > MAXLOOPS)
{
// MessageBox.Show("Timeout after sec " + ((SLEEP * MAXLOOPS) / 1000));
pi.SetSummary(Msg.GetMsg(GetCtx(), "ProcessRunning", true));
pi.SetIsTimeout(true);
return(process);
}
try
{
Thread.Sleep(SLEEP);
loops++;
}
catch (Exception e)
{
log.Log(Level.SEVERE, "Interrupted", e);
pi.SetSummary("Interrupted");
return(process);
}
//Thread.yield();
Thread.Sleep(0);
state = process.GetState();
}
String summary = process.GetProcessMsg();
// Change to get the Error Message and Display the Message
ValueNamePair vp = VLogger.RetrieveAdvDocNoError();
if (vp != null)
{
summary = vp.GetValue();
}
// Change to get the Error Message and Display the Message
if (summary == null || summary.Trim().Length == 0)
{
summary = state.ToString();
}
pi.SetSummary(summary, state.IsTerminated() || state.IsAborted());
log.Fine(summary);
return(process);
}
// Method added by mohit to get reports as byteArray. 19 April 2019
/// <summary>
/// Method to get byte array of report.
/// </summary>
/// <param name="ctx">Current Context.</param>
/// <param name="AD_Process_ID">Process ID.</param>
/// <param name="Name">Name of process.</param>
/// <param name="AD_Table_ID">Table id for fetching report.</param>
/// <param name="Record_ID">Record id.</param>
/// <param name="WindowNo">Window Number if process fired from window.</param>
/// <param name="recIDs">String of record id's/</param>
/// <param name="fileType">Report type.</param>
/// <param name="report">Out parameter to get byte array.</param>
/// <returns>Returns Process Info list and report byte array.</returns>
public Dictionary <string, object> GetReport(Ctx ctx, int AD_Process_ID, string Name, int AD_Table_ID, int Record_ID, int WindowNo, string recIDs, string fileType, out byte[] report, out string rptFilePath)
{
Dictionary <string, object> d = null;
report = null;
rptFilePath = null;
// Create PInstance
MPInstance instance = null;
try
{
instance = new MPInstance(ctx, AD_Process_ID, Record_ID);
}
catch (Exception e)
{
VLogger.Get().SaveError("GetReport-Instance Save", e.Message);
return(d);
}
if (!instance.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
VLogger.Get().SaveError("GetReport-Instance Save", vp.Name ?? vp.GetValue());
}
else
{
VLogger.Get().SaveError("GetReport-Instance Save", "PInstanceNotSaved");
}
return(d);
}
// Culture.
string lang = ctx.GetAD_Language().Replace("_", "-");
System.Globalization.CultureInfo original = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(lang);
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(lang);
// Process info
ProcessInfo pi = new ProcessInfo(Name, AD_Process_ID, AD_Table_ID, Record_ID, recIDs);
pi.SetFileType(fileType);
TryPrintFromDocType(pi);
try
{
pi.SetAD_User_ID(ctx.GetAD_User_ID());
pi.SetAD_Client_ID(ctx.GetAD_Client_ID());
pi.SetAD_PInstance_ID(instance.GetAD_PInstance_ID());
ProcessCtl ctl = new ProcessCtl();
d = new Dictionary <string, object>();
d = ctl.Process(pi, ctx, out report, out rptFilePath);
ctl.ReportString = null;
}
catch (Exception e)
{
VLogger.Get().SaveError("GetReport", e.Message);
report = null;
}
return(d);
}
/// <summary>
/// Is used to do a reverse entry of "Production Record" into the system
/// </summary>
/// <returns>message successfuly created or not</returns>
protected override string DoIt()
{
if (M_Production_ID > 0)
{
//Copy Production Header
ViennaAdvantage.Model.X_M_Production production = new ViennaAdvantage.Model.X_M_Production(GetCtx(), M_Production_ID, Get_Trx());
string cnt = "Select count(*) from m_production pro where pro.movementdate >" + GlobalVariable.TO_DATE(production.GetMovementDate(), true) + " and pro.processed='Y' ";
int trncnt = Util.GetValueOfInt(DB.ExecuteScalar(cnt));
if (trncnt > 0)
{
production.SetGOM01_IsRecordAvail(true);
production.Save(Get_TrxName());
return(Msg.GetMsg(GetCtx(), "Please Check some transaction already availble in future"));
}
else
{
production.SetGOM01_IsRecordAvail(false);
production.Save(Get_TrxName());
}
//check production is Reversed or not, if Reversed then not to do anything
if (production.IsReversed())
{
return(Msg.GetMsg(GetCtx(), "AlreadyReversed"));
}
//Get data from Production Plan
dsProductionPlan = DB.ExecuteDataset(@"SELECT AD_CLIENT_ID , AD_ORG_ID , DESCRIPTION , LINE , M_LOCATOR_ID ,
M_PRODUCT_ID , M_PRODUCTIONPLAN_ID , M_PRODUCTION_ID , PROCESSED , PRODUCTIONQTY M_WAREHOUSE_ID FROM M_ProductionPlan
WHERE IsActive = 'Y' AND M_PRODUCTION_ID = " + M_Production_ID, null, Get_Trx());
//get data from production Line
dsProductionLine = DB.ExecuteDataset(@"SELECT AD_CLIENT_ID , AD_ORG_ID , DESCRIPTION , LINE , M_ATTRIBUTESETINSTANCE_ID , M_LOCATOR_ID ,
M_PRODUCT_ID , M_PRODUCTIONLINE_ID, M_PRODUCTIONPLAN_ID , M_PRODUCTION_ID , PROCESSED , MOVEMENTQTY ,
C_UOM_ID , PLANNEDQTY , M_WAREHOUSE_ID FROM M_ProductionLine
WHERE IsActive = 'Y' AND M_PRODUCTION_ID = " + M_Production_ID, null, Get_Trx());
// Create New record of Production Header with Reverse Entry
X_M_Production productionTo = new X_M_Production(production.GetCtx(), 0, production.Get_Trx());
try
{
productionTo.Set_TrxName(production.Get_Trx());
PO.CopyValues(production, productionTo, production.GetAD_Client_ID(), production.GetAD_Org_ID());
productionTo.SetName("{->" + productionTo.GetName() + ")");
if (production.Get_ColumnIndex("DocumentNo") > 0)
{
productionTo.Set_Value("DocumentNo", ("{->" + productionTo.Get_Value("DocumentNo") + ")"));
}
productionTo.SetMovementDate(production.GetMovementDate()); //SI_0662 : not to create reverse record in current date, it should be created with the same date.
productionTo.SetProcessed(false);
if (!productionTo.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production reverse entry");
}
else
{
#region create new record of Production Plan
if (dsProductionPlan != null && dsProductionPlan.Tables.Count > 0 && dsProductionPlan.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsProductionPlan.Tables[0].Rows.Count; i++)
{
//Original Line
fromProdPlan = new X_M_ProductionPlan(GetCtx(), Util.GetValueOfInt(dsProductionPlan.Tables[0].Rows[i]["M_PRODUCTIONPLAN_ID"]), Get_Trx());
// Create New record of Production Plan with Reverse Entry
toProdPlan = new X_M_ProductionPlan(production.GetCtx(), 0, production.Get_Trx());
try
{
toProdPlan.Set_TrxName(production.Get_Trx());
PO.CopyValues(fromProdPlan, toProdPlan, fromProdPlan.GetAD_Client_ID(), fromProdPlan.GetAD_Org_ID());
toProdPlan.SetProductionQty(Decimal.Negate(toProdPlan.GetProductionQty()));
toProdPlan.SetM_Production_ID(productionTo.GetM_Production_ID());
toProdPlan.SetProcessed(false);
if (!toProdPlan.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production Plan reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production Plan reverse entry");
}
else
{
#region check record exist on production line
if (dsProductionLine != null && dsProductionLine.Tables.Count > 0 && dsProductionLine.Tables[0].Rows.Count > 0)
{
//check record exist on production line against production plan
drProductionLine = dsProductionLine.Tables[0].Select("M_ProductionPlan_ID = " + fromProdPlan.GetM_ProductionPlan_ID());
if (drProductionLine.Length > 0)
{
for (int j = 0; j < drProductionLine.Length; j++)
{
//Original Line
fromProdline = new X_M_ProductionLine(GetCtx(), Util.GetValueOfInt(drProductionLine[j]["M_PRODUCTIONLINE_ID"]), Get_Trx());
// Create New record of Production line with Reverse Entry
toProdline = new X_M_ProductionLine(production.GetCtx(), 0, production.Get_Trx());
try
{
toProdline.Set_TrxName(production.Get_Trx());
PO.CopyValues(fromProdline, toProdline, fromProdPlan.GetAD_Client_ID(), fromProdPlan.GetAD_Org_ID());
toProdline.SetMovementQty(Decimal.Negate(toProdline.GetMovementQty()));
toProdline.SetPlannedQty(Decimal.Negate(toProdline.GetPlannedQty()));
toProdline.SetM_Production_ID(productionTo.GetM_Production_ID());
toProdline.SetM_ProductionPlan_ID(toProdPlan.GetM_ProductionPlan_ID());
toProdline.SetReversalDoc_ID(fromProdline.GetM_ProductionLine_ID()); //maintain refernce of Orignal record on reversed record
toProdline.SetProcessed(false);
// if (!CheckQtyAvailablity(GetCtx(), toProdline.GetM_Warehouse_ID(), toProdline.GetM_Locator_ID(), toProdline.GetM_ProductContainer_ID(), toProdline.GetM_Product_ID(), toProdline.GetM_AttributeSetInstance_ID(), toProdline.GetMovementQty(), Get_Trx()))
if (!CheckQtyAvailablity(GetCtx(), toProdline.GetM_Warehouse_ID(), toProdline.GetM_Locator_ID(), 0, toProdline.GetM_Product_ID(), toProdline.GetM_AttributeSetInstance_ID(), toProdline.GetMovementQty(), Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
if (!string.IsNullOrEmpty(pp.GetName()))
{
throw new Exception("Could not create Production line reverse entry, " + pp.GetName());
}
else
{
throw new Exception("Could not create Production line reverse entry");
}
}
if (!toProdline.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production Line reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production line reverse entry");
}
else
{
// Create New record of Production line Policy (Material Policy) with Reverse Entry
sql.Clear();
sql.Append(@"INSERT INTO M_ProductionLineMA
( AD_CLIENT_ID, AD_ORG_ID , CREATED , CREATEDBY , ISACTIVE , UPDATED , UPDATEDBY ,
M_PRODUCTIONLINE_ID , M_ATTRIBUTESETINSTANCE_ID , MMPOLICYDATE , M_PRODUCTCONTAINER_ID, MOVEMENTQTY )
(SELECT AD_CLIENT_ID, AD_ORG_ID , sysdate , CREATEDBY , ISACTIVE , sysdate , UPDATEDBY ,
" + toProdline.GetM_ProductionLine_ID() + @" , M_ATTRIBUTESETINSTANCE_ID , MMPOLICYDATE , M_PRODUCTCONTAINER_ID, -1 * MOVEMENTQTY
FROM M_ProductionLineMA WHERE M_ProductionLine_ID = " + fromProdline.GetM_ProductionLine_ID() + @" ) ");
int no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
_log.Info("No of records saved on Meterial Policy against Production line ID : " + toProdline.GetM_ProductionLine_ID() + " are : " + no);
}
}
catch (Exception ex)
{
_log.Info("Error Occured during Production Reverse " + ex.ToString());
if (dsProductionLine != null)
{
dsProductionLine.Dispose();
}
if (dsProductionPlan != null)
{
dsProductionPlan.Dispose();
}
return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result));
}
}
}
}
#endregion
}
}
catch (Exception ex)
{
_log.Info("Error Occured during Production Reverse " + ex.ToString());
if (dsProductionLine != null)
{
dsProductionLine.Dispose();
}
if (dsProductionPlan != null)
{
dsProductionPlan.Dispose();
}
return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result));
}
}
}
#endregion
result = productionTo.GetName();
}
//set Reversed as True
productionTo.SetIsReversed(true);
if (!productionTo.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production reverse entry");
}
//Set reversed as true, Reverse Refernce on Orignal Document
production.SetIsReversed(true);
production.SetM_Ref_Production(productionTo.GetM_Production_ID());
if (!production.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production reverse entry");
}
}
catch (Exception ex)
{
_log.Info("Error Occured during Production Reverse " + ex.ToString());
if (dsProductionLine != null)
{
dsProductionLine.Dispose();
}
if (dsProductionPlan != null)
{
dsProductionPlan.Dispose();
}
return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result));
}
}
return(Msg.GetMsg(GetCtx(), "DocumentReversedSuccessfully" + result));
}
/// <summary>
/// Create Invoice Line from Order Line
/// </summary>
/// <param name="order">order</param>
/// <param name="orderLine">line</param>
/// <param name="qtyInvoiced">qty</param>
/// <param name="qtyEntered">qty</param>
private void CreateLine(MOrder order, MOrderLine orderLine,
Decimal qtyInvoiced, Decimal qtyEntered)
{
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();
// Get Payment method from Business partner
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()));
// 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
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 (o). " + pp.GetName());
}
throw new Exception("Could not create Invoice (o)");
}
}
//
MInvoiceLine line = new MInvoiceLine(_invoice);
line.SetOrderLine(orderLine);
line.SetQtyInvoiced(qtyInvoiced);
// if drop ship line true
line.SetIsDropShip(orderLine.IsDropShip());
log.Info("Qty Invoiced" + line.GetQtyInvoiced());
line.SetQtyEntered(qtyEntered);
line.SetLine(_line + orderLine.GetLine());
if (!line.Save())
{
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Could not create Invoice Line (o). " + pp.GetName());
}
throw new Exception("Could not create Invoice Line (o)");
}
log.Fine(line.ToString());
}
/// <summary>
/// Create default columns for Master Data Version Table
/// e.g. Processed, Processing, IsApproved etc.
/// </summary>
/// <param name="Ver_AD_Table_ID"></param>
/// <returns></returns>
private string CreateDefaultVerCols(int Ver_AD_Table_ID)
{
DataSet dstblCols = DB.ExecuteDataset("SELECT ColumnName FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, null);
for (int i = 0; i < listDefVerCols.Count; i++)
{
bool hasCol = false;
if (dstblCols != null && dstblCols.Tables[0].Rows.Count > 0)
{
DataRow[] dr = dstblCols.Tables[0].Select("ColumnName = '" + listDefVerCols[i] + "'");
if (dr != null && dr.Length > 0)
{
hasCol = true;
}
}
if (hasCol)
{
continue;
}
MColumn colVer = new MColumn(GetCtx(), 0, _trx);
colVer.SetExport_ID(null);
colVer.SetAD_Table_ID(Ver_AD_Table_ID);
colVer.SetColumnName(listDefVerCols[i]);
colVer.SetAD_Element_ID(_listDefVerElements[i]);
colVer.SetAD_Reference_ID(listDefVerRef[i]);
//if (listDefVerCols[i] == "VersionValidFrom")
// colVer.SetIsParent(true);
if (listDefVerRef[i] == 10)
{
colVer.SetFieldLength(10);
}
if (listDefVerRef[i] == 14)
{
colVer.SetFieldLength(2000);
}
if (listDefVerRef[i] == 13)
{
colVer.SetIsKey(true);
colVer.SetIsMandatory(true);
colVer.SetIsMandatoryUI(true);
}
if (!colVer.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating Version Column " + listDefVerCols[i];
}
log.Log(Level.SEVERE, "Version Column not created :: " + listDefVerCols[i] + " :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionColNotCreated"));
}
else
{
oldVerCol = colVer.GetAD_Column_ID();
}
}
return("");
}
protected override string DoIt()
{
try
{
_log.Info("Foreign Cost Calculation start at : " + DateTime.Now);
// Calculate Foreign Cost for Average Invoice
sql = @"SELECT i.c_invoice_id , il.c_invoiceline_id
FROM c_invoice i INNER JOIN c_invoiceline il ON i.c_invoice_id = il.c_invoice_id
WHERE il.isactive = 'Y' AND il.isfuturecostcalculated = 'N' AND i.isfuturecostcalculated = 'N'
AND docstatus IN ('CO' , 'CL') AND i.issotrx = 'N' AND i.isreturntrx = 'N' AND NVL(il.m_inoutline_ID , 0) <> 0
ORDER BY i.c_invoice_id ASC";
ds = DB.ExecuteDataset(sql, null, null);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
_log.Info("Foreign Cost Calculation : Average Invoice Record : " + ds.Tables[0].Rows.Count);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
try
{
invoice = new MInvoice(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]), Get_Trx());
invoiceLine = new MInvoiceLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoiceline_id"]), Get_Trx());
if (!MCostForeignCurrency.InsertForeignCostAverageInvoice(GetCtx(), invoice, invoiceLine, Get_Trx()))
{
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for calcualting Av. Invoice Foreign Cost for this record ID = " + invoice.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Value is " + pp.GetValue());
continue;
}
else
{
if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(*) FROM C_InvoiceLine WHERE IsFutureCostCalculated = 'N' AND C_Invoice_ID = " + invoice.GetC_Invoice_ID(), null, Get_Trx())) <= 0)
{
int no = Util.GetValueOfInt(DB.ExecuteQuery("UPDATE C_Invoice Set IsFutureCostCalculated = 'Y' WHERE C_Invoice_ID = " + invoice.GetC_Invoice_ID(), null, Get_Trx()));
}
Get_Trx().Commit();
}
}
catch (Exception ex1) { }
}
}
// Calculate Foriegn Cost for Average PO
sql = @"SELECT i.m_inout_id , il.m_inoutline_id , il.c_orderline_id
FROM m_inout i INNER JOIN m_inoutline il ON i.m_inout_id = il.m_inout_id
WHERE il.isactive = 'Y' AND il.isfuturecostcalculated = 'N' AND i.isfuturecostcalculated = 'N'
AND docstatus IN ('CO' , 'CL') AND i.issotrx = 'N' AND i.isreturntrx = 'N' AND NVL(il.c_orderline_ID , 0) <> 0
ORDER BY i.m_inout_id ASC";
ds = DB.ExecuteDataset(sql, null, null);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
_log.Info("Foreign Cost Calculation : Average PO Record : " + ds.Tables[0].Rows.Count);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
try
{
orderLine = new MOrderLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_orderline_id"]), Get_Trx());
order = new MOrder(GetCtx(), orderLine.GetC_Order_ID(), Get_Trx());
inoutLine = new MInOutLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["m_inoutline_id"]), Get_Trx());
if (!MCostForeignCurrency.InsertForeignCostAveragePO(GetCtx(), order, orderLine, inoutLine, Get_Trx()))
{
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for calcualting Av. PO Foreign Cost for this record ID = " + inoutLine.GetM_InOut_ID() +
" Error Name is " + pp.GetName() + " And Error Value is " + pp.GetValue());
continue;
}
else
{
if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(*) FROM M_InoutLine WHERE IsFutureCostCalculated = 'N' AND M_InOut_ID = " + inoutLine.GetM_InOut_ID(), null, Get_Trx())) <= 0)
{
int no = Util.GetValueOfInt(DB.ExecuteQuery("UPDATE M_Inout Set IsFutureCostCalculated = 'Y' WHERE M_Inout_ID = " + inoutLine.GetM_InOut_ID(), null, Get_Trx()));
}
Get_Trx().Commit();
}
}
catch (Exception ex2) { }
}
}
}
catch (Exception ex)
{
}
_log.Info("Foreign Cost Calculation End : " + DateTime.Now);
return(Msg.GetMsg(GetCtx(), "SuccessFullyCompleted"));
}
/// <summary>
/// Insert data in Version table against multikey Master table
/// </summary>
/// <param name="baseTbl"></param>
/// <param name="keyCols"></param>
/// <param name="tblVer"></param>
/// <returns></returns>
private string InsertMKVersionData(MTable baseTbl, string[] keyCols, MTable tblVer)
{
string retMsg = "";
// Get data from Master table
DataSet dsRecs = DB.ExecuteDataset("SELECT * FROM " + baseTbl.GetTableName(), null, _trx);
// check if there are any records in master table
if (dsRecs != null && dsRecs.Tables[0].Rows.Count > 0)
{
// loop through all records and insert in Version table
for (int i = 0; i < dsRecs.Tables[0].Rows.Count; i++)
{
// get where Clause for Master table against multiple key columns
GetMKWhereClause(dsRecs.Tables[0].Rows[i], keyCols);
if (MKWhereClause.Length > 0)
{
int count = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(" + tblVer.GetTableName() + "_ID) FROM " + tblVer.GetTableName() + " WHERE " + MKWhereClause.ToString(), null, _trx));
if (count > 0)
{
continue;
}
}
// create PO object of source table (Master table)
PO sPO = baseTbl.GetPO(GetCtx(), dsRecs.Tables[0].Rows[i], _trx);
// create PO object of destination table (Version table)
PO dPO = tblVer.GetPO(GetCtx(), 0, _trx);
sPO.CopyTo(dPO);
dPO.SetAD_Client_ID(sPO.GetAD_Client_ID());
dPO.SetAD_Org_ID(sPO.GetAD_Org_ID());
dPO.Set_Value("RecordVersion", "1");
dPO.Set_ValueNoCheck("VersionValidFrom", sPO.Get_Value("Created"));
dPO.Set_ValueNoCheck("IsVersionApproved", true);
dPO.Set_ValueNoCheck("Processed", true);
dPO.Set_ValueNoCheck("ProcessedVersion", true);
for (int j = 0; j < keyCols.Length; j++)
{
dPO.Set_ValueNoCheck(keyCols[j], sPO.Get_Value(keyCols[j]));
}
dPO.Set_Value("Export_ID", null);
if (!dPO.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in saving data in Version table";
}
return(retMsg);
}
}
}
return(retMsg);
}
/// <summary>
/// Get System Elements for Default Columns
/// </summary>
/// <param name="VerTblName">Table Name</param>
public string GetSystemElements(string VerTblName)
{
// check if count in list is equal to default version columns
if (_listDefVerElements.Count == listDefVerCols.Count)
{
return("");
}
// Clear values from list
_listDefVerElements.Clear();
// check if Primary key column is present in Columns list, if not present then add Primary Key column
if (!listDefVerCols.Contains(VerTblName + "_ID"))
{
listDefVerCols.Add(VerTblName + "_ID");
}
// Create comma separated string of all default version columns
string DefSysEle = string.Join(",", listDefVerCols
.Select(x => string.Format("'{0}'", x)));
// Get System Elements and Column Names for all Version table columns
DataSet dsDefVerCols = DB.ExecuteDataset("SELECT AD_Element_ID, ColumnName FROM AD_Element WHERE ColumnName IN (" + DefSysEle + ")", null, _trx);
if (dsDefVerCols != null && dsDefVerCols.Tables[0].Rows.Count > 0)
{
// loop through all columns of version table to get System Elements
// if not found then create new
for (int i = 0; i < listDefVerCols.Count; i++)
{
DataRow[] drSysEle = dsDefVerCols.Tables[0].Select("ColumnName='" + listDefVerCols[i] + "'");
if (drSysEle.Length > 0)
{
if (!_listDefVerElements.Contains(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"])))
{
_listDefVerElements.Add(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"]));
}
if (listDefVerCols[i] == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
else
{
M_Element ele = new M_Element(GetCtx(), 0, _trx);
ele.SetAD_Client_ID(0);
ele.SetAD_Org_ID(0);
ele.SetName(listDefVerCols[i]);
ele.SetColumnName(listDefVerCols[i]);
ele.SetPrintName(listDefVerCols[i]);
if (!ele.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating System Element";
}
log.Log(Level.SEVERE, error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "ElementNotSaved"));
}
else
{
_listDefVerElements.Add(ele.GetAD_Element_ID());
if (ele.GetColumnName() == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
}
}
}
return("");
}
/// <summary>
/// Create version table and window based on the parent table \
/// where Maintain Version field is marked as true
/// </summary>
/// <returns> Message (String) </returns>
public string CreateVersionInfo(int AD_Column_ID, int AD_Table_ID, Trx trx)
{
_trx = trx;
_AD_Table_ID = AD_Table_ID;
_AD_Column_ID = AD_Column_ID;
bool hasMainVerCol = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + _AD_Table_ID + " AND IsActive ='Y' AND IsMaintainVersions = 'Y'", null, _trx)) > 0;
if (!hasMainVerCol)
{
hasMainVerCol = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + _AD_Table_ID, null, _trx)) == "Y";
}
// check whether there are any columns in the table
// marked as "Maintain Versions", then proceed else return
if (hasMainVerCol)
{
MTable tbl = new MTable(GetCtx(), _AD_Table_ID, _trx);
string VerTblName = tbl.GetTableName() + "_Ver";
// Create/Get System Elements for Version Table Columns
string retMsg = GetSystemElements(VerTblName);
if (retMsg != "")
{
return(retMsg);
}
int Ver_AD_Table_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName = '" + VerTblName + "'", null, _trx));
// check whether version table is already present in system
// if not present then create table
MTable tblVer = null;
if (Ver_AD_Table_ID <= 0)
{
string tableName = tbl.GetTableName();
// create new Version table for parent table
tblVer = new MTable(GetCtx(), 0, _trx);
tbl.CopyTo(tblVer);
tblVer.SetTableName(tableName + "_Ver");
tblVer.SetName(tableName + " Ver");
tblVer.Set_Value("Export_ID", null);
tblVer.Set_Value("AD_Window_ID", null);
tblVer.SetIsDeleteable(false);
tblVer.SetDescription("Table for maintaining versions of " + tableName);
tblVer.SetHelp("Table for maintaining versions of " + tableName);
//tblVer.SetAD_Window_ID(Ver_AD_Window_ID);
if (!tblVer.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating Version Table";
}
log.Log(Level.SEVERE, "Version table not created :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionTblNotCreated"));
}
else
{
Ver_AD_Table_ID = tblVer.GetAD_Table_ID();
// Create Default Version Columns
retMsg = CreateDefaultVerCols(Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
}
}
else
{
tblVer = new MTable(GetCtx(), Ver_AD_Table_ID, _trx);
// Create Default Version Columns
retMsg = CreateDefaultVerCols(Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
}
int VerTableColID = 0;
// if Version table successfully created, then check columns, if not found then create new
if (Ver_AD_Table_ID > 0)
{
// Get all columns from Version Table
int[] ColIDs = MColumn.GetAllIDs("AD_Column", "AD_Table_ID = " + _AD_Table_ID, _trx);
bool hasCols = false;
DataSet dsDestCols = DB.ExecuteDataset("SELECT ColumnName, AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, _trx);
if (dsDestCols != null && dsDestCols.Tables[0].Rows.Count > 0)
{
hasCols = true;
}
// loop through all columns
foreach (int columnID in ColIDs)
{
bool createNew = true;
// object of Column from source table (Master Table)
MColumn sCol = new MColumn(GetCtx(), columnID, _trx);
// check if source column is not Virtual Column, proceed in that case only
if (!sCol.IsVirtualColumn())
{
DataRow[] dr = null;
if (hasCols)
{
dr = dsDestCols.Tables[0].Select("ColumnName = '" + sCol.GetColumnName() + "'");
if (dr.Length > 0)
{
createNew = false;
}
}
// Version Column object
MColumn colVer = null;
int AD_Col_ID = 0;
// if column not present in Version table then create new
if (createNew)
{
colVer = new MColumn(GetCtx(), AD_Col_ID, _trx);
}
// if column already present and user pressed sync button on same column of Master table
// then create object of existing column (in case of change in any column fields)
else if (!createNew && (_AD_Column_ID == columnID))
{
AD_Col_ID = Util.GetValueOfInt(dr[0]["AD_Column_ID"]);
colVer = new MColumn(GetCtx(), Util.GetValueOfInt(dr[0]["AD_Column_ID"]), _trx);
}
if (colVer != null)
{
sCol.CopyTo(colVer);
if (AD_Col_ID > 0)
{
colVer.SetAD_Column_ID(AD_Col_ID);
}
colVer.SetExport_ID(null);
colVer.SetAD_Table_ID(Ver_AD_Table_ID);
// set key column to false
colVer.SetIsKey(false);
// check if source column is key column
// then set Restrict Constraint and set Reference as Table Direct
if (sCol.IsKey())
{
colVer.SetConstraintType("R");
colVer.SetAD_Reference_ID(19);
}
//if (sCol.IsKey())
// colVer.SetIsParent(true);
//else
colVer.SetIsParent(false);
colVer.SetIsMaintainVersions(false);
colVer.SetIsMandatory(false);
colVer.SetIsMandatoryUI(false);
if (!colVer.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Version Column not created";
}
log.Log(Level.SEVERE, "Version Column not created :: " + sCol.GetColumnName() + " :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionColNotCreated"));
}
else
{
VerTableColID = colVer.GetAD_Column_ID();
}
}
}
}
// Get one column to sync table in database from Version Table
if (VerTableColID <= 0)
{
VerTableColID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, _trx));
}
// Get newly Created Column
if (oldVerCol > 0)
{
VerTableColID = oldVerCol;
}
// Sync Version table in database
bool success = true;
retMsg = SyncVersionTable(tblVer, VerTableColID, out success);
// if any error and there is message in return then return and rollback transaction
if (!success && retMsg != "")
{
log.Log(Level.SEVERE, "Column not sync :: " + retMsg);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNotSync"));
}
else
{
// if table has single key
if (tbl.IsSingleKey())
{
// get column names from parent table
string colNameStrings = GetColumnNameString(tbl.GetAD_Table_ID());
// get default columns string from Version Table columns list
string defColString = GetDefaultColString(colNameStrings);
// Insert data in version table from Master table
InsertVersionData(colNameStrings, defColString, tblVer.GetTableName());
}
// Cases where single key is not present in Master table
else
{
// Insert data in version table against Master Table
retMsg = InsertMKVersionData(tbl, tbl.GetKeyColumns(), tblVer);
if (retMsg != "")
{
log.Log(Level.SEVERE, "Data not Inserted :: " + retMsg);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "DataInsertionErrorMultikey"));
}
}
}
}
}
return(Msg.GetMsg(GetCtx(), "ProcessCompletedSuccessfully"));
}
/// <summary>
/// Generate Quotation
/// </summary>
/// <returns>Process Message</returns>
protected override string DoIt()
{
// Int32 value = 0;
string msg = "";
ValueNamePair vp = null;
MBPartner bp = null;
MOrderLine ol = null;
log.Info("C_Project_ID=" + _C_Project_ID);
if (_C_Project_ID == 0)
{
throw new ArgumentException("C_Project_ID == 0");
}
MProject fromProject = new MProject(GetCtx(), _C_Project_ID, Get_TrxName());
if (fromProject.GetGenerate_Quotation() == null)
{
throw new ArgumentException("No Generate Quotation found on Project.");
}
if (fromProject.GetGenerate_Quotation().Trim() == "Y")
{
throw new ArgumentException("Sales Quotation already generated");
}
// if Business Partner or Prospect is not selected then gives error
if (fromProject.GetC_BPartner_ID() == 0 && fromProject.GetC_BPartnerSR_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "SelectBP/Prospect"));
}
//JID_1200: if Business Partner/Prospect Location is not selected then gives error
if (fromProject.GetC_BPartner_Location_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "SelectBPLocation"));
}
MOrder order = new MOrder(GetCtx(), 0, Get_TrxName());
order.SetAD_Client_ID(fromProject.GetAD_Client_ID());
order.SetAD_Org_ID(fromProject.GetAD_Org_ID());
C_Bpartner_id = fromProject.GetC_BPartner_ID();
C_Bpartner_Location_id = fromProject.GetC_BPartner_Location_ID();
C_BPartnerSR_ID = fromProject.GetC_BPartnerSR_ID();
MBPartnerLocation bpartnerloc = new MBPartnerLocation(GetCtx(), C_Bpartner_Location_id, Get_TrxName());
//String currentdate = DateTime.Now.ToString();
String sqlprjln = "SELECT COUNT(C_ProjectLine_ID) FROM C_ProjectLine WHERE C_Project_ID=" + _C_Project_ID;
C_ProjectLine_ID = Util.GetValueOfInt(DB.ExecuteScalar(sqlprjln, null, Get_TrxName()));
if (C_ProjectLine_ID != 0)
{
order.SetDateOrdered(DateTime.Now.ToLocalTime());
order.SetDatePromised(DateTime.Now.ToLocalTime());
if (C_Bpartner_id != 0)
{
order.SetC_BPartner_ID(fromProject.GetC_BPartner_ID());
if (bpartnerloc.IsShipTo() == true)
{
order.SetC_BPartner_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetAD_User_ID(fromProject.GetAD_User_ID());
}
if (bpartnerloc.IsBillTo() == true)
{
order.SetBill_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetBill_User_ID(fromProject.GetAD_User_ID());
}
}
if (C_BPartnerSR_ID != 0)
{
order.SetC_BPartner_ID(fromProject.GetC_BPartnerSR_ID());
if (bpartnerloc.IsShipTo() == true)
{
order.SetC_BPartner_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetAD_User_ID(fromProject.GetAD_User_ID());
}
if (bpartnerloc.IsBillTo() == true)
{
order.SetBill_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetBill_User_ID(fromProject.GetAD_User_ID());
}
}
String sql = "SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType = 'SOO' AND DocSubTypeSO = 'ON' AND IsReturnTrx = 'N' AND IsActive = 'Y' AND AD_Client_ID = "
+ GetCtx().GetAD_Client_ID() + " AND AD_Org_ID IN (0, " + GetAD_Org_ID() + ") ORDER BY AD_Org_ID DESC";
int Doctype_id = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName()));
int MPriceList_id = fromProject.GetM_PriceList_ID();
order.SetM_PriceList_ID(MPriceList_id);
order.SetC_Project_ID(GetRecord_ID());
if (fromProject.GetSalesRep_ID() > 0)
{
order.SetSalesRep_ID(fromProject.GetSalesRep_ID());
}
order.SetC_Currency_ID(fromProject.GetC_Currency_ID());
if (C_Bpartner_id != 0)
{
bp = new MBPartner(GetCtx(), C_Bpartner_id, Get_TrxName());
if (bp.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0)
{
bp.SetC_Campaign_ID(fromProject.GetC_Campaign_ID());
}
//bp.SetAD_Client_ID(fromProject.GetAD_Client_ID());
//bp.SetAD_Org_ID(fromProject.GetAD_Org_ID());
if (bp.GetC_PaymentTerm_ID() != 0)
{
order.SetPaymentMethod(bp.GetPaymentRule());
order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID());
}
if (!bp.Save())
{
log.SaveError("BPartnerNotSaved", "");
return(Msg.GetMsg(GetCtx(), "BPartnerNotSaved"));
}
}
else
{
bp = new MBPartner(GetCtx(), C_BPartnerSR_ID, Get_TrxName());
if (bp.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0)
{
bp.SetC_Campaign_ID(fromProject.GetC_Campaign_ID());
}
//bp.SetAD_Client_ID(fromProject.GetAD_Client_ID());
//bp.SetAD_Org_ID(fromProject.GetAD_Org_ID());
if (bp.GetC_PaymentTerm_ID() != 0)
{
order.SetPaymentMethod(bp.GetPaymentRule());
order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID());
}
if (!bp.Save())
{
log.SaveError("BPartnerNotSaved", "");
return(Msg.GetMsg(GetCtx(), "BPartnerNotSaved"));
}
}
order.SetFreightCostRule("I");
if (order.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0)
{
order.SetC_Campaign_ID(fromProject.GetC_Campaign_ID());
}
order.SetDocStatus("IP");
order.SetC_DocType_ID(Doctype_id);
order.SetC_DocTypeTarget_ID(Doctype_id);
order.SetIsSOTrx(true);
order.Set_Value("IsSalesQuotation", true);
if (!order.Save())
{
Get_TrxName().Rollback();
vp = VLogger.RetrieveError();
if (vp != null)
{
msg = vp.GetName();
}
else
{
msg = Msg.GetMsg(GetCtx(), "QuotationNotSaved");
}
log.SaveError("QuotationNotSaved", "");
return(msg);
}
//Order Lines
int count = 0;
MProjectLine[] lines = fromProject.GetLines();
for (int i = 0; i < lines.Length; i++)
{
ol = new MOrderLine(order);
ol.SetLine(lines[i].GetLine());
ol.SetDescription(lines[i].GetDescription());
ol.SetM_Product_ID(lines[i].GetM_Product_ID(), true);
ol.SetQtyEntered(lines[i].GetPlannedQty());
ol.SetQtyOrdered(lines[i].GetPlannedQty());
ol.SetPriceEntered(lines[i].GetPlannedPrice());
ol.SetPriceActual(lines[i].GetPlannedPrice());
ol.SetPriceList(lines[i].GetPriceList());
if (ol.Save())
{
count++;
}
else
{
Get_TrxName().Rollback();
vp = VLogger.RetrieveError();
if (vp != null)
{
msg = vp.GetName();
}
else
{
msg = Msg.GetMsg(GetCtx(), "QuoteLineNotSaved");
}
log.SaveError("QuoteLineNotSaved", "");
return(msg);
}
}
fromProject.SetRef_Order_ID(order.GetC_Order_ID());
fromProject.SetGenerate_Quotation("Y");
if (!fromProject.Save())
{
Get_TrxName().Rollback();
log.SaveError("ProjectNotSaved", "");
return(Msg.GetMsg(GetCtx(), "ProjectNotSaved"));
}
msg = Msg.GetMsg(GetCtx(), "QuotationGenerated");
}
else
{
msg = Msg.GetMsg(GetCtx(), "NoLines");
}
return(msg);
}
/// <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());
}
/// <summary>
/// Generate Contract Invoice
/// </summary>
/// <param name="contSchedule"></param>
private void GenerateInvoice(VAdvantage.Model.X_C_ContractSchedule contSchedule)
{
if (contSchedule.IsActive())
{
int res = 0;
// sql = "select noofdays from c_frequency where c_frequency_id = " + cont.GetC_Frequency_ID();
// Decimal? days = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
Decimal?price = null;
if (!cont.IsCancel())
{
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
}
else
{
sql.Clear();
sql.Append("UPDATE C_Contract SET RenewalType = NULL WHERE C_Contract_ID = " + cont.GetC_Contract_ID());
int res2 = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()));
if (contSchedule.GetEndDate() <= cont.GetCancellationDate())
{
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
}
else
{
sql.Clear();
sql.Append("SELECT MAX(C_ContractSchedule_ID) FROM C_ContractSchedule WHERE NVL(C_INVOICE_ID,0) > 0 AND C_Contract_ID = " + cont.GetC_Contract_ID());
int c_contractschedule_id = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null, Get_TrxName()));
if (c_contractschedule_id != 0)
{
string date = cont.GetCancellationDate().Value.ToString("dd-MMM-yyyy");
sql.Clear();
sql.Append("SELECT DaysBetween('" + date + "', EndDate) FROM C_ContractSchedule WHERE C_ContractSchedule_ID = " + c_contractschedule_id);
Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql.ToString(), null, Get_TrxName()));
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
sql.Clear();
sql.Append("UPDATE C_ContractSchedule SET IsActive = 'N' WHERE EndDate > '" + date + "' AND C_Contract_ID = " + cont.GetC_Contract_ID());
res = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()));
}
else
{
sql.Clear();
sql.Append("SELECT DaysBetween(CancellationDate, StartDate) FROM C_Contract WHERE C_Contract_ID = " + cont.GetC_Contract_ID());
Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql.ToString(), null, Get_TrxName()));
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
if (diffDays > 0)
{
sql.Clear();
sql.Append("UPDATE C_ContractSchedule SET IsActive = 'N' WHERE C_Contract_ID = " + cont.GetC_Contract_ID());
res = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()));
}
else
{
sql.Clear();
sql.Append("UPDATE C_ContractSchedule SET IsActive = 'N' WHERE C_Contract_ID = " + cont.GetC_Contract_ID());
res = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()));
}
}
}
}
price = Decimal.Round(price.Value, 2, MidpointRounding.AwayFromZero);
inv = new VAdvantage.Model.MInvoice(GetCtx(), 0, Get_TrxName());
inv.SetAD_Client_ID(cont.GetAD_Client_ID());
inv.SetAD_Org_ID(cont.GetAD_Org_ID());
inv.SetC_BPartner_ID(cont.GetC_BPartner_ID());
if (Util.GetValueOfInt(cont.GetC_Order_ID()) != 0)
{
inv.SetC_Order_ID(cont.GetC_Order_ID());
}
// JID_0872: System has To pick the Payment Method defined with the Business Partner against whom the Invoice is getting generated.
if (Env.IsModuleInstalled("VA009_"))
{
// Done by Rakesh Kumar on 01/Apr/2021
// When ContractType is Accounts Receivable
if (bp.GetVA009_PaymentMethod_ID() > 0 && (cont.GetContractType().Equals(X_C_Contract.CONTRACTTYPE_AccountsReceivable)))
{
inv.SetVA009_PaymentMethod_ID(bp.GetVA009_PaymentMethod_ID());
inv.SetIsSOTrx(true);
}
else if (bp.GetVA009_PO_PaymentMethod_ID() > 0 && (cont.GetContractType().Equals(X_C_Contract.CONTRACTTYPE_AccountsPayable)))
{
// When ContractType is Accounts Payable
inv.SetVA009_PaymentMethod_ID(bp.GetVA009_PO_PaymentMethod_ID());
inv.SetIsSOTrx(false);
}
else
{
throw new ArgumentException(Msg.GetMsg(GetCtx(), "VIS_PaymentMethodNotDefined") + " : " + bp.GetName());
}
}
inv.SetC_DocType_ID(_C_DocType_ID);
inv.SetC_DocTypeTarget_ID(_C_DocType_ID);
inv.SetC_BPartner_Location_ID(cont.GetBill_Location_ID());
inv.SetC_Currency_ID(cont.GetC_Currency_ID());
// JID_1536_3
inv.SetC_ConversionType_ID(cont.GetC_ConversionType_ID());
inv.SetC_PaymentTerm_ID(cont.GetC_PaymentTerm_ID());
inv.SetC_Campaign_ID(cont.GetC_Campaign_ID());
inv.SetM_PriceList_ID(cont.GetM_PriceList_ID());
inv.SetSalesRep_ID(cont.GetSalesRep_ID());
inv.SetC_Contract_ID(cont.GetC_Contract_ID());
// Done by rakesh kumar on 31/Mar/2021
// When invoice created from service contract set ServiceContract always true
inv.SetServiceContract(true);
if (!inv.Save())
{
//Neha----If Invoice not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
else
{
VAdvantage.Model.MInvoiceLine invLine = new VAdvantage.Model.MInvoiceLine(GetCtx(), 0, Get_TrxName());
invLine.SetAD_Client_ID(inv.GetAD_Client_ID());
invLine.SetAD_Org_ID(inv.GetAD_Org_ID());
invLine.SetC_Campaign_ID(inv.GetC_Campaign_ID());
invLine.SetC_Invoice_ID(inv.GetC_Invoice_ID());
invLine.SetC_UOM_ID(cont.GetC_UOM_ID());
invLine.SetM_Product_ID(cont.GetM_Product_ID());
// Added by Vivek on 21/11/2017 asigned by Pradeep
invLine.SetM_AttributeSetInstance_ID(cont.GetM_AttributeSetInstance_ID());
if (Util.GetValueOfInt(cont.GetC_OrderLine_ID()) != 0)
{
invLine.SetC_OrderLine_ID(cont.GetC_OrderLine_ID());
}
invLine.SetC_Tax_ID(cont.GetC_Tax_ID());
invLine.SetQty(cont.GetQtyEntered());
invLine.SetQtyEntered(cont.GetQtyEntered());
// invLine.SetQtyInvoiced(1);
// invLine.SetPrice(price.Value);
invLine.SetPriceActual(cont.GetPriceEntered());
invLine.SetPriceEntered(cont.GetPriceEntered());
// invLine.SetPriceLimit(price);
invLine.SetPriceList(cont.GetPriceEntered());
if (!invLine.Save())
{
//Neha----If Invoice Line not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
}
string comp = inv.CompleteIt();
//Neha---If Invoice Completed then Set Document Action, Document Status and Contract on Invoice Header---11 Sep,2018
if (comp == "CO")
{
inv.SetDocAction("CL");
inv.SetDocStatus("CO");
//Neha---Set C_Contract_ID on C_invoice table using MClass object--11 Sep,2018
inv.SetC_Contract_ID(cont.GetC_Contract_ID());
if (!inv.Save())
{
//Neha----If Invoice not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
}
else
{
//Neha----If Invoice not completed then will show the exception---11 Sep,2018
if (inv.GetProcessMsg() != null)
{
throw new ArgumentException("Cannot complete the Invoice. " + inv.GetProcessMsg());
}
throw new ArgumentException("Cannot complete the Invoice");
}
#region Commented Code
//Neha---Set C_Contract_ID on C_invoice table using MClass object--11 Sep,2018
//sql = "UPDATE c_invoice SET C_Contract_ID = " + cont.GetC_Contract_ID() + " WHERE c_invoice_id = " + inv.GetC_Invoice_ID();
//res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
//Neha---taxAmt not used in this class----11 Sep,2018
//sql = "SELECT SUM(taxamt) FROM c_invoicetax WHERE c_invoice_id = " + inv.GetC_Invoice_ID();
//Decimal? taxAmt = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
# endregion
sql.Clear();
sql.Append("UPDATE C_ContractSchedule SET C_Invoice_ID = " + inv.GetC_Invoice_ID() + ", Processed = 'Y' WHERE C_ContractSchedule_ID = " + contSchedule.GetC_ContractSchedule_ID());
res = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()));
//Neha---Append Document No. in sb----12 Sep,2018
sb.Append(inv.GetDocumentNo() + ", ");
//_count++;
}
} // prepare
/// <summary>
/// Generate Sales Order
/// </summary>
/// <returns>Process Message</returns>
protected override String DoIt()
{
Int32 value = 0;
string msg = "";
ValueNamePair vp = null;
MBPartner bp = null;
MOrderLine ol = null;
log.Info("C_Project_ID=" + _C_Project_ID);
if (_C_Project_ID == 0)
{
throw new ArgumentException("C_Project_ID == 0");
}
MProject fromProject = new MProject(GetCtx(), _C_Project_ID, Get_TrxName());
if (fromProject.GetGenerate_Order().Trim() == "Y")
{
throw new ArgumentException("Sales Order already generated");
}
// if Business Partner or Prospect is not selected then gives error
if (fromProject.GetC_BPartner_ID() == 0 && fromProject.GetC_BPartnerSR_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "SelectBP/Prospect"));
}
// if Business Partner/Prospect Location is not selected then gives error
if (fromProject.GetC_BPartner_Location_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "SelectBPLocation"));
}
MOrder order = new MOrder(GetCtx(), 0, Get_TrxName());
order.SetAD_Client_ID(fromProject.GetAD_Client_ID());
order.SetAD_Org_ID(fromProject.GetAD_Org_ID());
C_Bpartner_id = fromProject.GetC_BPartner_ID();
C_Bpartner_Location_id = fromProject.GetC_BPartner_Location_ID();
C_BPartnerSR_ID = fromProject.GetC_BPartnerSR_ID();
MBPartnerLocation bpartnerloc = new MBPartnerLocation(GetCtx(), C_Bpartner_Location_id, Get_TrxName());
//String currentdate = DateTime.Now.ToString();
String sqlprjln = "SELECT COUNT(C_ProjectLine_ID) FROM C_ProjectLine WHERE C_Project_ID=" + _C_Project_ID;
C_ProjectLine_ID = Util.GetValueOfInt(DB.ExecuteScalar(sqlprjln, null, Get_TrxName()));
if (C_ProjectLine_ID != 0)
{
order.SetDateOrdered(DateTime.Now.ToLocalTime());
order.SetDatePromised(DateTime.Now.ToLocalTime());
if (C_Bpartner_id != 0)
{
order.SetC_BPartner_ID(fromProject.GetC_BPartner_ID());
if (bpartnerloc.IsShipTo() == true)
{
order.SetC_BPartner_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetAD_User_ID(fromProject.GetAD_User_ID());
}
if (bpartnerloc.IsBillTo() == true)
{
order.SetBill_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetBill_User_ID(fromProject.GetAD_User_ID());
}
}
if (C_BPartnerSR_ID != 0)
{
String sqlcust = "UPDATE C_BPartner SET IsCustomer='Y', IsProspect='N' WHERE C_BPartner_ID=" + C_BPartnerSR_ID;
value = DB.ExecuteQuery(sqlcust, null, Get_TrxName());
if (value == -1)
{
return(Msg.GetMsg(GetCtx(), "BPartnerNotSaved"));
}
order.SetC_BPartner_ID(fromProject.GetC_BPartnerSR_ID());
if (bpartnerloc.IsShipTo() == true)
{
order.SetC_BPartner_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetAD_User_ID(fromProject.GetAD_User_ID());
}
if (bpartnerloc.IsBillTo() == true)
{
order.SetBill_Location_ID(fromProject.GetC_BPartner_Location_ID());
order.SetBill_User_ID(fromProject.GetAD_User_ID());
}
}
String sql = "SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType = 'SOO' AND DocSubTypeSO = 'SO' AND IsReturnTrx = 'N' AND IsActive = 'Y' AND AD_Client_ID = "
+ GetAD_Client_ID() + " AND AD_Org_ID IN (0, " + GetAD_Org_ID() + ") ORDER BY AD_Org_ID DESC";
int Doctype_id = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName()));
order.SetM_PriceList_ID(fromProject.GetM_PriceList_ID());
order.SetC_Project_ID(GetRecord_ID());
if (fromProject.GetSalesRep_ID() > 0)
{
order.SetSalesRep_ID(fromProject.GetSalesRep_ID());
}
order.SetC_Currency_ID(fromProject.GetC_Currency_ID());
if (C_Bpartner_id != 0)
{
bp = new MBPartner(GetCtx(), C_Bpartner_id, Get_TrxName());
if (bp.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0)
{
bp.SetC_Campaign_ID(fromProject.GetC_Campaign_ID());
}
//bp.SetAD_Client_ID(fromProject.GetAD_Client_ID());
//bp.SetAD_Org_ID(fromProject.GetAD_Org_ID());
if (bp.GetC_PaymentTerm_ID() != 0)
{
order.SetPaymentMethod(bp.GetPaymentRule());
order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID());
order.SetVA009_PaymentMethod_ID(bp.GetVA009_PaymentMethod_ID());
}
if (!bp.Save())
{
log.SaveError("BPartnerNotSaved", "");
return(Msg.GetMsg(GetCtx(), "BPartnerNotSaved"));
}
}
else
{
bp = new MBPartner(GetCtx(), C_BPartnerSR_ID, Get_TrxName());
if (bp.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0)
{
bp.SetC_Campaign_ID(fromProject.GetC_Campaign_ID());
}
//bp.SetAD_Client_ID(fromProject.GetAD_Client_ID());
//bp.SetAD_Org_ID(fromProject.GetAD_Org_ID());
if (bp.GetC_PaymentTerm_ID() != 0)
{
order.SetPaymentMethod(bp.GetPaymentRule());
order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID());
order.SetVA009_PaymentMethod_ID(bp.GetVA009_PaymentMethod_ID());
}
if (!bp.Save())
{
log.SaveError("BPartnerNotSaved", "");
return(Msg.GetMsg(GetCtx(), "BPartnerNotSaved"));
}
}
order.SetFreightCostRule("I");
if (order.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0)
{
order.SetC_Campaign_ID(fromProject.GetC_Campaign_ID());
}
order.SetDocStatus("IP");
order.SetC_DocType_ID(Doctype_id);
order.SetIsSOTrx(true);
order.SetC_DocTypeTarget_ID(Doctype_id);
order.SetPriorityRule("5");
order.SetFreightCostRule("I");
//Set VA077 values on header level
if (Env.IsModuleInstalled("VA077_"))
{
//Get the org count of legal entity org
sql = @"SELECT Count(AD_Org_ID) FROM AD_Org WHERE IsActive='Y'
AND (IsProfitCenter ='Y' OR IsCostCenter ='Y') AND
AD_Client_Id=" + fromProject.GetAD_Client_ID() + @" AND LegalEntityOrg = " + fromProject.GetAD_Org_ID();
int result = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (result > 0)
{
order.SetVA077_IsLegalEntity(true);
}
order.SetVA077_SalesCoWorker(fromProject.GetVA077_SalesCoWorker());
order.SetVA077_SalesCoWorkerPer(fromProject.GetVA077_SalesCoWorkerPer());
order.Set_Value("VA077_TotalMarginAmt", fromProject.Get_Value("VA077_TotalMarginAmt"));
order.Set_Value("VA077_TotalPurchaseAmt", fromProject.Get_Value("VA077_TotalPurchaseAmt"));
order.Set_Value("VA077_TotalSalesAmt", fromProject.Get_Value("VA077_TotalSalesAmt"));
order.Set_Value("VA077_MarginPercent", fromProject.Get_Value("VA077_MarginPercent"));
order.Set_Value("VA077_OrderRef", fromProject.GetPOReference());
}
if (!order.Save())
{
Get_TrxName().Rollback();
vp = VLogger.RetrieveError();
if (vp != null)
{
msg = vp.GetName();
}
else
{
msg = Msg.GetMsg(GetCtx(), "SaleOrderNotSaved");
}
log.SaveError("SaleOrderNotSaved", "");
return(msg);
}
//Order Lines
int count = 0;
MProjectLine[] lines = fromProject.GetLines();
for (int i = 0; i < lines.Length; i++)
{
ol = new MOrderLine(order);
ol.SetLine(lines[i].GetLine());
ol.SetDescription(lines[i].GetDescription());
ol.SetM_Product_ID(lines[i].GetM_Product_ID(), true);
ol.SetQtyEntered(lines[i].GetPlannedQty());
ol.SetQtyOrdered(lines[i].GetPlannedQty());
ol.SetPriceEntered(lines[i].GetPlannedPrice());
ol.SetPriceActual(lines[i].GetPlannedPrice());
ol.SetPriceList(lines[i].GetPriceList());
// Set Attribute and UOM from Opportunity Lines
if (lines[i].Get_ColumnIndex("M_AttributeSetInstance_ID") >= 0)
{
ol.SetM_AttributeSetInstance_ID(lines[i].GetM_AttributeSetInstance_ID());
}
if (lines[i].Get_ColumnIndex("C_UOM_ID") >= 0)
{
ol.SetC_UOM_ID(Util.GetValueOfInt(lines[i].Get_Value("C_UOM_ID")));
}
//Set VA077 values on line level
if (Env.IsModuleInstalled("VA077_"))
{
ol.Set_Value("VA077_MarginPercent", lines[i].Get_Value("VA077_MarginPercent"));
ol.Set_Value("VA077_MarginAmt", lines[i].Get_Value("VA077_MarginAmt"));
ol.Set_Value("VA077_PurchasePrice", lines[i].Get_Value("VA077_PurchasePrice"));
}
if (ol.Save())
{
count++;
}
else
{
Get_TrxName().Rollback();
vp = VLogger.RetrieveError();
if (vp != null)
{
msg = vp.GetName();
}
else
{
msg = Msg.GetMsg(GetCtx(), "OrderLineNotSaved");
}
log.SaveError("OrderLineNotSaved", "");
return(msg);
}
}
fromProject.SetC_Order_ID(order.GetC_Order_ID());
fromProject.SetC_BPartner_ID(fromProject.GetC_BPartnerSR_ID());
fromProject.SetC_BPartnerSR_ID(0);
fromProject.SetGenerate_Order("Y");
if (!fromProject.Save())
{
Get_TrxName().Rollback();
log.SaveError("ProjectNotSaved", "");
return(Msg.GetMsg(GetCtx(), "ProjectNotSaved"));
}
return(Msg.GetMsg(GetCtx(), "OrderGenerationDone"));
}
else
{
msg = Msg.GetMsg(GetCtx(), "NoLines");
}
return(msg);
}
protected override string DoIt()
{
try
{
sql.Clear();
sql.Append("SELECT * FROM M_InOut WHERE IsActive = 'Y' AND DocStatus IN ('DR') ORDER BY movementdate");
dsInOut = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
#region complete Order Record
sql.Clear();
sql.Append("SELECT * FROM C_Order WHERE IsActive = 'Y' AND DocStatus IN ('DR') ORDER BY dateacct");
dsRecord = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
dataRow = dsRecord.Tables[0].Select("DocStatus = 'DR' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
MOrder order = null;
for (int i = 0; i < dataRow.Length; i++)
{
try
{
order = new MOrder(GetCtx(), Util.GetValueOfInt(dataRow[i]["C_Order_ID"]), Get_Trx());
order.CompleteIt();
if (order.GetDocAction() == "CL")
{
order.SetDocStatus("CO");
order.SetDocAction("CL");
if (!order.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + order.GetC_Order_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Order not completed for this Record ID = " + order.GetC_Order_ID());
}
}
catch { }
}
}
#endregion
#region complete Invoice Record
sql.Clear();
sql.Append("SELECT * FROM C_Invoice WHERE IsActive = 'Y' AND DocStatus IN ('DR') ORDER BY dateacct");
dsRecord = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
dataRow = dsRecord.Tables[0].Select("DocStatus = 'DR' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
MInvoice invoice = null;
for (int i = 0; i < dataRow.Length; i++)
{
try
{
invoice = new MInvoice(GetCtx(), Util.GetValueOfInt(dataRow[i]["C_Invoice_ID"]), Get_Trx());
invoice.CompleteIt();
if (invoice.GetDocAction() == "CL")
{
invoice.SetDocStatus("CO");
invoice.SetDocAction("CL");
if (!invoice.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Invoice Record ID = " + invoice.GetC_Invoice_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Invoice not completed for this Record ID = " + invoice.GetC_Invoice_ID());
}
}
catch { }
}
}
#endregion
#region complete material receipt
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'N' AND IsReturnTrx = 'N' AND DocStatus = 'DR' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetM_InOut_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Material Receipt not completed for this Record ID = " + inout.GetM_InOut_ID());
}
}
catch { }
}
}
#endregion
#region complete Movement Record
sql.Clear();
sql.Append("SELECT * FROM M_Movement WHERE IsActive = 'Y' AND DocStatus IN ('DR') ORDER BY movementdate");
dsRecord = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
dataRow = dsRecord.Tables[0].Select("DocStatus = 'DR' ", "movementdate");
if (dataRow != null && dataRow.Length > 0)
{
MMovement movement = null;
for (int i = 0; i < dataRow.Length; i++)
{
try
{
movement = new MMovement(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_Movement_ID"]), Get_Trx());
movement.CompleteIt();
if (movement.GetDocAction() == "CL")
{
movement.SetDocStatus("CO");
movement.SetDocAction("CL");
if (!movement.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Invoice Record ID = " + movement.GetM_Movement_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Movement not completed for this Record ID = " + movement.GetM_Movement_ID());
}
}
catch { }
}
}
#endregion
#region complete shipment
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'Y' AND IsReturnTrx = 'N' AND DocStatus = 'DR' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetM_InOut_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Shipment not completed for this Record ID = " + inout.GetM_InOut_ID());
}
}
catch { }
}
}
#endregion
#region complete Customer Return
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'Y' AND IsReturnTrx = 'Y' AND DocStatus = 'DR' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetM_InOut_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Customer return not completed for this Record ID = " + inout.GetM_InOut_ID());
}
}
catch { }
}
}
#endregion
#region complete Return to Vendor
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'N' AND IsReturnTrx = 'Y' AND DocStatus = 'DR' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetM_InOut_ID() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
_log.Info("Return to Vendor not completed for this Record ID = " + inout.GetM_InOut_ID());
}
}
catch { }
}
}
#endregion
}
catch (Exception ex)
{
_log.Info("Error Occured during completion of record by using ImportedDataCompletion Process - " + ex.ToString());
return(Msg.GetMsg(GetCtx(), "NotCompleted"));
}
return(Msg.GetMsg(GetCtx(), "SucessfullyCompleted"));
}
/**
* Before Save
* @param newRecord new
* @return true
*/
protected override bool BeforeSave(bool newRecord)
{
if (Is_ValueChanged("DueAmt"))
{
log.Fine("beforeSave");
SetIsValid(false);
}
oldDueAmt = Util.GetValueOfDecimal(Get_ValueOld("DueAmt"));
if (Env.IsModuleInstalled("VA009_"))
{
// get invoice currency for rounding
MCurrency currency = MCurrency.Get(GetCtx(), GetC_Currency_ID());
SetDueAmt(Decimal.Round(GetDueAmt(), currency.GetStdPrecision()));
SetVA009_PaidAmntInvce(Decimal.Round(GetVA009_PaidAmntInvce(), currency.GetStdPrecision()));
// when invoice schedule have payment reference then need to check payment mode on payment window & update here
if (GetC_Payment_ID() > 0)
{
#region for payment
MPayment payment = new MPayment(GetCtx(), GetC_Payment_ID(), Get_Trx());
SetVA009_PaymentMethod_ID(payment.GetVA009_PaymentMethod_ID());
// get payment method detail -- update to here
DataSet dsPaymentMethod = DB.ExecuteDataset(@"SELECT VA009_PaymentMode, VA009_PaymentType, VA009_PaymentTrigger FROM VA009_PaymentMethod
WHERE VA009_PaymentMethod_ID = " + payment.GetVA009_PaymentMethod_ID(), null, Get_Trx());
if (dsPaymentMethod != null && dsPaymentMethod.Tables.Count > 0 && dsPaymentMethod.Tables[0].Rows.Count > 0)
{
if (!String.IsNullOrEmpty(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentMode"])))
{
SetVA009_PaymentMode(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentMode"]));
}
if (!String.IsNullOrEmpty(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentType"])))
{
SetVA009_PaymentType(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentType"]));
}
SetVA009_PaymentTrigger(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentTrigger"]));
}
#endregion
}
else if (GetC_CashLine_ID() > 0)
{
#region For Cash
// when invoice schedule have cashline reference then need to check
// payment mode of "Cash" type having currency is null on "Payment Method" window & update here
DataSet dsPaymentMethod = (DB.ExecuteDataset(@"SELECT VA009_PaymentMethod_ID, VA009_PaymentMode, VA009_PaymentType, VA009_PaymentTrigger
FROM VA009_PaymentMethod WHERE IsActive = 'Y'
AND AD_Client_ID = " + GetAD_Client_ID() + @" AND VA009_PaymentBaseType = 'B' AND NVL(C_Currency_ID , 0) = 0", null, Get_Trx()));
if (dsPaymentMethod != null && dsPaymentMethod.Tables.Count > 0 && dsPaymentMethod.Tables[0].Rows.Count > 0)
{
SetVA009_PaymentMethod_ID(Util.GetValueOfInt(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentMethod_ID"]));
if (!String.IsNullOrEmpty(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentMode"])))
{
SetVA009_PaymentMode(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentMode"]));
}
if (!String.IsNullOrEmpty(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentType"])))
{
SetVA009_PaymentType(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentType"]));
}
SetVA009_PaymentTrigger(Util.GetValueOfString(dsPaymentMethod.Tables[0].Rows[0]["VA009_PaymentTrigger"]));
}
else
{
#region when we not found record of "Cash" then we will create a new rcord on Payment Method for Cash
string sql = @"SELECT AD_TABLE_ID FROM AD_TABLE WHERE tablename LIKE 'VA009_PaymentMethod' AND IsActive = 'Y'";
int tableId = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
MTable tbl = new MTable(GetCtx(), tableId, Get_Trx());
PO po = tbl.GetPO(GetCtx(), 0, Get_Trx());
po.SetAD_Client_ID(GetAD_Client_ID());
po.SetAD_Org_ID(0); // Recod will be created in (*) Organization
po.Set_Value("Value", "By Cash");
po.Set_Value("VA009_Name", "By Cash");
po.Set_Value("IsActive", true);
po.Set_Value("VA009_PaymentBaseType", "B");
po.Set_Value("VA009_PaymentRule", "M");
po.Set_Value("VA009_PaymentMode", "C");
po.Set_Value("VA009_PaymentType", "S");
po.Set_Value("VA009_PaymentTrigger", "S");
po.Set_Value("C_Currency_ID", null);
po.Set_Value("VA009_InitiatePay", false);
if (!po.Save(Get_Trx()))
{
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error Occured when try to save record on Payment Method for Cash. Error Type : " + pp.GetValue());
}
else
{
SetVA009_PaymentMethod_ID(Util.GetValueOfInt(po.Get_Value("VA009_PaymentMethod_ID")));
SetVA009_PaymentMode("C");
SetVA009_PaymentType("S");
SetVA009_PaymentTrigger("S");
}
#endregion
}
#endregion
}
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="contSchedule"></param>
private void GenerateInvoice(VAdvantage.Model.X_C_ContractSchedule contSchedule)
{
if (contSchedule.IsActive())
{
int res = 0;
sql = "SELECT MIN(C_DOCTYPE_ID) FROM C_DocType"
+ @" WHERE DOCBASETYPE='ARI'
AND ISACTIVE ='Y'";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_DocType", true, true);
int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName()));
// sql = "select noofdays from c_frequency where c_frequency_id = " + cont.GetC_Frequency_ID();
// Decimal? days = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
Decimal?price = null;
if (!cont.IsCancel())
{
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
}
else
{
sql = "UPDATE C_Contract SET RenewalType = null WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
int res2 = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
if (contSchedule.GetEndDate() <= cont.GetCancellationDate())
{
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
}
else
{
sql = "SELECT MAX(C_ContractSchedule_ID) FROM C_ContractSchedule WHERE NVL(C_INVOICE_ID,0) > 0 AND C_Contract_ID = " + cont.GetC_Contract_ID();
int c_contractschedule_id = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName()));
if (c_contractschedule_id != 0)
{
string date = cont.GetCancellationDate().Value.ToString("dd-MMM-yyyy");
// int contsch = Util.GetValueOfInt(contSchedule.GetC_ContractSchedule_ID()) - 1;
sql = "SELECT daysbetween('" + date + "', EndDate) FROM C_ContractSchedule WHERE C_ContractSchedule_ID= " + c_contractschedule_id;
Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_TrxName()));
// price = Decimal.Multiply(cont.GetPriceEntered(), diffDays.Value);
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
sql = "UPDATE C_ContractSchedule SET IsActive = 'N' WHERE EndDate > '" + date + "' AND C_Contract_ID = " + cont.GetC_Contract_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
}
else
{
sql = "SELECT daysbetween(CancellationDate, StartDate) FROM C_Contract WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_TrxName()));
//price = Decimal.Multiply(Decimal.Divide(cont.GetPriceEntered(), days.Value), diffDays.Value);
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
if (diffDays > 0)
{
sql = "UPDATE C_ContractSchedule SET IsActive = 'N' WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
}
else
{
sql = "UPDATE C_ContractSchedule SET IsActive = 'N' WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
}
// sql = "update c_contractschedule set isactive = 'N' where enddate > '" + System.DateTime.Now + "' and c_contract_id = " + cont.GetC_Contract_ID();
}
}
}
price = Decimal.Round(price.Value, 2, MidpointRounding.AwayFromZero);
inv = new VAdvantage.Model.MInvoice(GetCtx(), 0, Get_TrxName());
inv.SetAD_Client_ID(cont.GetAD_Client_ID());
inv.SetAD_Org_ID(cont.GetAD_Org_ID());
inv.SetC_BPartner_ID(cont.GetC_BPartner_ID());
if (Util.GetValueOfInt(cont.GetC_Order_ID()) != 0)
{
inv.SetC_Order_ID(cont.GetC_Order_ID());
}
inv.SetC_DocType_ID(C_DocType_ID);
inv.SetC_DocTypeTarget_ID(C_DocType_ID);
inv.SetC_BPartner_Location_ID(cont.GetBill_Location_ID());
inv.SetC_Currency_ID(cont.GetC_Currency_ID());
inv.SetC_PaymentTerm_ID(cont.GetC_PaymentTerm_ID());
inv.SetC_Campaign_ID(cont.GetC_Campaign_ID());
inv.SetIsSOTrx(true);
inv.SetM_PriceList_ID(cont.GetM_PriceList_ID());
inv.SetSalesRep_ID(cont.GetSalesRep_ID());
inv.SetC_Contract_ID(cont.GetC_Contract_ID());
if (!inv.Save())
{
//Neha----If Invoice not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
else
{
VAdvantage.Model.MInvoiceLine invLine = new VAdvantage.Model.MInvoiceLine(GetCtx(), 0, Get_TrxName());
invLine.SetAD_Client_ID(inv.GetAD_Client_ID());
invLine.SetAD_Org_ID(inv.GetAD_Org_ID());
invLine.SetC_Campaign_ID(inv.GetC_Campaign_ID());
invLine.SetC_Invoice_ID(inv.GetC_Invoice_ID());
invLine.SetC_UOM_ID(cont.GetC_UOM_ID());
invLine.SetM_Product_ID(cont.GetM_Product_ID());
// Added by Vivek on 21/11/2017 asigned by Pradeep
invLine.SetM_AttributeSetInstance_ID(cont.GetM_AttributeSetInstance_ID());
if (Util.GetValueOfInt(cont.GetC_OrderLine_ID()) != 0)
{
invLine.SetC_OrderLine_ID(cont.GetC_OrderLine_ID());
}
invLine.SetC_Tax_ID(cont.GetC_Tax_ID());
invLine.SetQty(cont.GetQtyEntered());
invLine.SetQtyEntered(cont.GetQtyEntered());
// invLine.SetQtyInvoiced(1);
// invLine.SetPrice(price.Value);
invLine.SetPriceActual(cont.GetPriceEntered());
invLine.SetPriceEntered(cont.GetPriceEntered());
// invLine.SetPriceLimit(price);
invLine.SetPriceList(cont.GetPriceEntered());
if (!invLine.Save())
{
//Neha----If Invoice Line not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
}
string comp = inv.CompleteIt();
//Neha---If Invoice Completed then Set Document Action, Document Status and Contract on Invoice Header---11 Sep,2018
if (comp == "CO")
{
inv.SetDocAction("CL");
inv.SetDocStatus("CO");
//Neha---Set C_Contract_ID on C_invoice table using MClass object--11 Sep,2018
inv.SetC_Contract_ID(cont.GetC_Contract_ID());
if (!inv.Save())
{
//Neha----If Invoice not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
}
else
{
//Neha----If Invoice not completed then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot complete the Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot complete the Invoice");
}
#region Commented Code
//Neha---Set C_Contract_ID on C_invoice table using MClass object--11 Sep,2018
//sql = "UPDATE c_invoice SET C_Contract_ID = " + cont.GetC_Contract_ID() + " WHERE c_invoice_id = " + inv.GetC_Invoice_ID();
//res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
//Neha---taxAmt not used in this class----11 Sep,2018
//sql = "SELECT SUM(taxamt) FROM c_invoicetax WHERE c_invoice_id = " + inv.GetC_Invoice_ID();
//Decimal? taxAmt = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
# endregion
sql = "UPDATE C_ContractSchedule SET C_Invoice_ID = " + inv.GetC_Invoice_ID() + ", Processed = 'Y' WHERE C_ContractSchedule_ID = " + contSchedule.GetC_ContractSchedule_ID();
// sql = "update c_contractschedule set c_invoice_id = " + inv.GetC_Invoice_ID() + ", processed = 'Y', TotalAmt = " + inv.GetTotalLines() + ", taxamt = " + taxAmt + ", grandtotal = " + inv.GetGrandTotal() + " where c_contractschedule_id = " + contSchedule.GetC_ContractSchedule_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
//Neha---Append Document No. in sb----12 Sep,2018
sb.Append(inv.GetDocumentNo() + ", ");
//_count++;
}
/// <summary>
/// Function to update data in Master Table based on the versions saved
/// </summary>
/// <param name="dsRec">All Records which need to be updated</param>
/// <param name="TableName">Version Table Name</param>
/// <param name="VerTableID">Version Table ID</param>
/// <returns>True/False based on the Updation of data in parent table</returns>
private bool UpdateRecords(DataSet dsRec, string TableName, int VerTableID)
{
// Get Master table name from Version table
string BaseTblName = TableName;
if (TableName.EndsWith("_Ver"))
{
BaseTblName = BaseTblName.Substring(0, TableName.Length - 4);
}
// Master Table ID from Table name
int BaseTableID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName = '" + BaseTblName + "'"));
// Get Column information of Master Table
DataSet dsDBColNames = DB.ExecuteDataset("SELECT ColumnName, AD_Reference_ID, IsUpdateable, IsAlwaysUpdateable FROM AD_Column WHERE AD_Table_ID = " + BaseTableID);
if (dsDBColNames != null && dsDBColNames.Tables[0].Rows.Count > 0)
{
log.Info("Processing for " + TableName + " :: ");
StringBuilder sqlSB = new StringBuilder("");
bool recordProcessed = false;
// create object of Master Table
MTable tbl = new MTable(GetCtx(), BaseTableID, null);
// create object of Version table
MTable tblVer = new MTable(GetCtx(), VerTableID, null);
// check whether master table has Single key
bool isSingleKey = tbl.IsSingleKey();
// List of records which were not processed by process in case of any error
List <string> keys = new List <string>();
StringBuilder sbKey = new StringBuilder("");
// Loop through the records which need to be updated on Master Table
for (int i = 0; i < dsRec.Tables[0].Rows.Count; i++)
{
DataRow dr = dsRec.Tables[0].Rows[i];
sbKey.Clear();
PO poDest = null;
PO poSource = tblVer.GetPO(GetCtx(), dr, null);
// if table has single key
if (isSingleKey)
{
// Create object of PO Class from TableName and Record ID
poDest = MTable.GetPO(GetCtx(), BaseTblName, Util.GetValueOfInt(dr[BaseTblName + "_ID"]), null);
sbKey.Append(Util.GetValueOfInt(dr[BaseTblName + "_ID"]));
}
else
{
// Create object of PO Class from combination of key columns
string[] keyCols = tbl.GetKeyColumns();
StringBuilder whereCond = new StringBuilder("");
for (int w = 0; w < keyCols.Length; w++)
{
if (w == 0)
{
if (keyCols[w] != null)
{
whereCond.Append(keyCols[w] + " = " + poSource.Get_Value(keyCols[w]));
}
else
{
whereCond.Append(" NVL(" + keyCols[w] + ",0) = 0");
}
}
else
{
if (keyCols[w] != null)
{
whereCond.Append(" AND " + keyCols[w] + " = " + poSource.Get_Value(keyCols[w]));
}
else
{
whereCond.Append(" AND NVL(" + keyCols[w] + ",0) = 0");
}
}
}
poDest = tbl.GetPO(GetCtx(), whereCond.ToString(), null);
sbKey.Append(whereCond);
}
// check if there is any error in processing record, then continue and do not process next versions
if (keys.Contains(sbKey.ToString()))
{
continue;
}
// Check whether Master Table contains "Processing" Column
if (poDest.Get_ColumnIndex("Processing") >= 0)
{
// if "Processing" column found then return, do not update in Master Table,
// because transaction might be in approval process
if (Util.GetValueOfString(poSource.Get_Value("Processing")) == "Y")
{
keys.Add(sbKey.ToString());
continue;
}
}
// Check whether Master Table contains "Processed" Column
if (poDest.Get_ColumnIndex("Processed") >= 0)
{
if (Util.GetValueOfString(poSource.Get_Value("Processed")) == "Y")
{
recordProcessed = true;
}
}
// set client and Organization ID from Version table to Master
// as copy PO set these ID's as 0
poDest.SetAD_Client_ID(poSource.GetAD_Client_ID());
poDest.SetAD_Org_ID(poSource.GetAD_Org_ID());
StringBuilder sbColName = new StringBuilder("");
// Loop through all the columns in Master Table
for (int j = 0; j < dsDBColNames.Tables[0].Rows.Count; j++)
{
sbColName.Clear();
// Get Name of Column
sbColName.Append(dsDBColNames.Tables[0].Rows[j]["ColumnName"]);
// check if column exist in Default columns list, in that case do not update and continue to next column
if (defcolNames.Contains(sbColName.ToString()))
{
continue;
}
// No need to update Primary key column, continue to next column
if (sbColName.ToString().Equals(BaseTblName + "_ID"))
{
continue;
}
// if column is of "Yes-No" type i.e. Reference ID is 20 (Fixed) then set True/False
if (Util.GetValueOfInt(dsDBColNames.Tables[0].Rows[j]["AD_Reference_ID"]) == 20)
{
Object val = false;
if (poSource.Get_Value(sbColName.ToString()) != null)
{
val = poSource.Get_Value(sbColName.ToString());
}
poDest.Set_Value(sbColName.ToString(), val);
}
else
{
poDest.Set_ValueNoCheck(sbColName.ToString(), poSource.Get_Value(sbColName.ToString()));
}
// Check if Master record is Processed and Always Updatable is false then check whether any value updated in such column
// if value updated then return false, can't change data in Processed record if it's not Always Updatable
if (recordProcessed && Util.GetValueOfString(dsDBColNames.Tables[0].Rows[j]["IsAlwaysUpdateable"]) == "N")
{
bool upd = poDest.Is_ValueChanged(sbColName.ToString());
if (upd)
{
msg.Append("Can't update " + sbColName.ToString() + " in " + TableName);
log.SaveError("ERROR", "Can not update processed record for column ==>> " + sbColName.ToString());
// Add record to the list of unprocessed records
keys.Add(sbKey.ToString());
continue;
}
}
}
// Save Master Record
if (!poDest.Save())
{
// Add record to the list of unprocessed records
keys.Add(sbKey.ToString());
// Check for Errors
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in updating Version";
}
msg.Append("Save error in " + TableName + " ==>> " + error);
log.SaveError("ERROR", error);
sqlSB.Clear();
sqlSB.Append("UPDATE " + TableName + " SET VersionLog = '" + error + "' WHERE " + TableName + "_ID = " + dr[TableName + "_ID"]);
int count = DB.ExecuteQuery(sqlSB.ToString(), null, null);
continue;
}
else
{
// Update Version table, Set "ProcessedVersion" to "Y", so that it don't consider when process runs next time
sqlSB.Clear();
// Update against record id in case of Single key, and update Key column in version table as well in case of new record
if (isSingleKey)
{
sqlSB.Append("UPDATE " + TableName + " SET ProcessedVersion = 'Y', " + BaseTblName + "_ID = " + poDest.Get_ID() + " WHERE " + TableName + "_ID = " + dr[TableName + "_ID"]);
}
// else
else
{
sqlSB.Append("UPDATE " + TableName + " SET ProcessedVersion = 'Y' WHERE " + TableName + "_ID = " + dr[TableName + "_ID"]);
}
int count = DB.ExecuteQuery(sqlSB.ToString(), null, null);
if (count <= 0)
{
log.Info(TableName + " not updated ==>> " + sqlSB.ToString());
}
}
}
}
return(true);
}
/// <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),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 p.c_order_id IS NULL"
// + " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
+ " 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() + ") AND p.AD_CLient_ID= " + mf.GetAD_Client_ID() +
" AND 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 + ") " +
" GROUP BY C_Currency_ID,pl.C_UOM_ID,pl.m_product_id";
//IDataReader idr = null;
try
{
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)
{
for (int i = 0; i < dsOpp.Tables[0].Rows.Count; i++)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp = 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());
//Conversion from BaseUOM to UOM on Project Line
totalQtyOpp = 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]["Quantity"]));
if (totalQtyOpp == null)
{
totalQtyOpp = Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["Quantity"]);
}
if (totalQtyOpp.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPriceOpp.Value, totalQtyOpp.Value);
avgPrice = Decimal.Round(avgPrice.Value, StdPrecision, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]), 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 (Exception e)
{
log.Log(Level.SEVERE, e.Message);
}
}
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 Invoice.
/// </summary>
/// <returns>document no</returns>
protected override String DoIt()
{
StringBuilder invDocumentNo = new StringBuilder();
int count = Util.GetValueOfInt(DB.ExecuteScalar(" SELECT Count(M_Inout_ID) FROM M_Inout WHERE ISSOTRX='Y' AND M_Inout_ID=" + GetRecord_ID()));
MInOut ship = null;
bool isAllownonItem = Util.GetValueOfString(GetCtx().GetContext("$AllowNonItem")).Equals("Y");
if (count > 0)
{
if (_M_InOut_ID == 0)
{
throw new ArgumentException("No Shipment");
}
//
ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx());
if (ship.Get_ID() == 0)
{
throw new ArgumentException("Shipment not found");
}
if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus()) && !MInOut.DOCSTATUS_Closed.Equals(ship.GetDocStatus()))
{
// JID_0750: done by Bharat on 05 Feb 2019 if Customer Return document and status is not complete it should give message "Customer Return Not Completed".
if (ship.IsReturnTrx())
{
throw new ArgumentException(Msg.GetMsg(GetCtx(), "CustomerReturnNotCompleted"));
}
else
{
throw new ArgumentException(Msg.GetMsg(GetCtx(), "ShipmentNotCompleted"));
}
}
}
else
{
if (_M_InOut_ID == 0)
{
throw new ArgumentException("No Material Receipt");
}
//
ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx());
if (ship.Get_ID() == 0)
{
throw new ArgumentException("Material Receipt not found");
}
if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus()) && !MInOut.DOCSTATUS_Closed.Equals(ship.GetDocStatus()))
{
// JID_0750: done by Bharat on 05 Feb 2019 if Return to vendor document and status is not complete it should give message "Return To Vendor Not Completed".
if (ship.IsReturnTrx())
{
throw new ArgumentException(Msg.GetMsg(GetCtx(), "VendorReturnNotCompleted"));
}
else
{
throw new ArgumentException(Msg.GetMsg(GetCtx(), "MRNotCompleted"));
}
}
}
// When record contain more than single order and order having different Payment term or Price List then not to generate invoices
// JID_0976 - For conversion Type
if (ship.GetC_Order_ID() > 0 && Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(DISTINCT c_order.m_pricelist_id) + count(distinct c_order.c_paymentterm_id) + count(distinct COALESCE( c_order.C_ConversionType_ID , " + MConversionType.GetDefault(GetAD_Client_ID()) + @")) as recordcount
FROM m_inoutline INNER JOIN c_orderline ON m_inoutline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_order.c_order_id = c_orderline.c_order_id
WHERE m_inoutline.m_inout_id = " + _M_InOut_ID + @" GROUP BY m_inoutline.m_inout_id ", null, Get_Trx())) > 3)
{
if (ship.IsSOTrx())
{
//Different Payment Terms, Price list found against the selected orders, use "Generate Invoice" process to create multiple invoices.
throw new ArgumentException(Msg.GetMsg(GetCtx(), "VIS_SoDifferentPayAndPrice"));
}
else
{
//Different Payment Terms, Price list found against the selected orders
throw new ArgumentException(Msg.GetMsg(GetCtx(), "VIS_DifferentPayAndPrice"));
}
}
// Create Invoice Header
MInvoice invoice = new MInvoice(ship, null);
//Payment Management
int _CountVA009 = Env.IsModuleInstalled("VA009_") ? 1 : 0;
// Amortization
int _CountVA038 = Env.IsModuleInstalled("VA038_") ? 1 : 0;
if (_CountVA009 > 0)
{
int _PaymentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select VA009_PaymentMethod_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID(), null, Get_Trx()));
// 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
int bpPamentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT " + (ship.IsSOTrx() ? " VA009_PaymentMethod_ID " : " VA009_PO_PaymentMethod_ID ") +
@" FROM C_BPartner WHERE C_BPartner_ID = " + ship.GetC_BPartner_ID(), null, Get_Trx()));
if (bpPamentMethod_ID != 0)
{
_PaymentMethod_ID = bpPamentMethod_ID;
}
if (_PaymentMethod_ID > 0)
{
invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID);
}
}
// Letter Of Credit
int _CountVA026 = Env.IsModuleInstalled("VA026_") ? 1 : 0;
if (_CountVA026 > 0)
{
int VA026_LCDetail_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select VA026_LCDetail_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID(), null, Get_Trx()));
if (VA026_LCDetail_ID > 0)
{
invoice.SetVA026_LCDetail_ID(VA026_LCDetail_ID);
}
}
//end
if (ship.IsReturnTrx())
{
if (!ship.IsSOTrx())
{
// Purchase Return
// set target document from documnet type window -- based on documnet type available on material receipt / return to vendor
// JID_0779: Create AP Credit memo if we run the Generate TO process from Returm to Vendor window.
//if (invoice.GetC_DocTypeTarget_ID() == 0)
//{
if (_C_DocType_ID > 0)
{
invoice.SetC_DocTypeTarget_ID(_C_DocType_ID);
}
else
{
int C_DocTypeTarget_ID = DB.GetSQLValue(null, "SELECT C_DocTypeInvoice_ID FROM C_DocType WHERE C_DocType_ID=@param1", ship.GetC_DocType_ID());
if (C_DocTypeTarget_ID > 0)
{
invoice.SetC_DocTypeTarget_ID(C_DocTypeTarget_ID);
}
else
{
invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO);
}
}
invoice.SetIsReturnTrx(ship.IsReturnTrx());
invoice.SetIsSOTrx(ship.IsSOTrx());
}
else
{
// Sales Return
if (_C_DocType_ID > 0)
{
invoice.SetC_DocTypeTarget_ID(_C_DocType_ID);
}
else
{
if (ship.GetC_Order_ID() >= 0)
{
int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select C_DocType_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID(), null, Get_Trx()));
MDocType dt = MDocType.Get(GetCtx(), C_DocType_ID);
if (dt.GetC_DocTypeInvoice_ID() != 0)
{
invoice.SetC_DocTypeTarget_ID(dt.GetC_DocTypeInvoice_ID(), true);
}
else
{
invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO);
}
}
else
{
invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO);
}
}
}
}
if (_M_PriceList_ID != 0)
{
invoice.SetM_PriceList_ID(_M_PriceList_ID);
}
//Set InvoiceDocumentNo to InvoiceReference
if (_InvoiceDocumentNo != null && _InvoiceDocumentNo.Length > 0)
{
invoice.Set_Value("InvoiceReference", _InvoiceDocumentNo);
}
//Set TargetDoctype
if (_C_DocType_ID > 0 && !ship.IsReturnTrx())
{
invoice.Set_Value("C_DocTypeTarget_ID", _C_DocType_ID);
}
// Added by Bharat on 30 Jan 2018 to set Inco Term from Order
if (invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0)
{
invoice.SetC_IncoTerm_ID(ship.GetC_IncoTerm_ID());
}
//To get Payment Rule and set the Payment method
if (invoice.GetPaymentRule() != "")
{
invoice.SetPaymentMethod(invoice.GetPaymentRule());
}
if (!invoice.Save())
{
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
MInOutLine[] shipLines = ship.GetLines(false);
DateTime? AmortStartDate = null;
DateTime? AmortEndDate = null;
count = 0;
DataSet ds = null;
DataSet dsDoc = null;
for (int i = 0; i < shipLines.Length; i++)
{
MInOutLine sLine = shipLines[i];
// Changes done by Bharat on 06 July 2017 restrict to create invoice if Invoice already created against that for same quantity
string sql = @"SELECT ml.QtyEntered - SUM(COALESCE(li.QtyEntered,0)) as QtyEntered, ml.MovementQty- SUM(COALESCE(li.QtyInvoiced, 0)) as QtyInvoiced" +
" FROM M_InOutLine ml INNER JOIN C_InvoiceLine li ON li.M_InOutLine_ID = ml.M_InOutLine_ID INNER JOIN C_Invoice ci ON ci.C_Invoice_ID = li.C_Invoice_ID " +
" WHERE ci.DocStatus NOT IN ('VO', 'RE') AND ml.M_InOutLine_ID =" + sLine.GetM_InOutLine_ID() + " GROUP BY ml.MovementQty, ml.QtyEntered";
ds = DB.ExecuteDataset(sql, null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
decimal qtyEntered = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["QtyEntered"]);
decimal qtyInvoiced = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["QtyInvoiced"]);
if (qtyEntered <= 0)
{
// Getting document number Count if Invoice already generated for Material Receipt
string StrSql = "SELECT ci.DocumentNo,li.M_InOutLine_ID FROM C_InvoiceLine li INNER JOIN C_Invoice ci ON ci.C_Invoice_ID = li.C_Invoice_ID " +
" WHERE ci.DocStatus NOT IN ('VO', 'RE') AND li.M_InOutLine_ID = " + sLine.GetM_InOutLine_ID();
dsDoc = DB.ExecuteDataset(StrSql, null, Get_Trx());
if (dsDoc != null && dsDoc.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < dsDoc.Tables[0].Rows.Count; j++)
{
// JID_1358: Need to show document number in message if Invoice already generated for Material Receipt
string no = invDocumentNo.ToString();
if (invDocumentNo.Length > 0 && no != Util.GetValueOfString(dsDoc.Tables[0].Rows[j]["DocumentNo"]))
{
invDocumentNo.Append(", " + Util.GetValueOfString(dsDoc.Tables[0].Rows[j]["DocumentNo"]));
}
else
{
invDocumentNo.Clear();
invDocumentNo.Append(Util.GetValueOfString(dsDoc.Tables[0].Rows[j]["DocumentNo"]));
}
ds.Dispose();
log.Info("Invoice Line already exist for Receipt Line ID - " + sLine.GetM_InOutLine_ID());
continue;
}
dsDoc.Dispose();
}
}
else
{
MInvoiceLine line = new MInvoiceLine(invoice);
line.SetShipLine(sLine);
line.SetQtyEntered(qtyEntered);
line.SetQtyInvoiced(qtyInvoiced);
// Change By Mohit Amortization process -------------
if (_CountVA038 > 0)
{
if (line.GetM_Product_ID() > 0)
{
//MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName());
int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID
FROM M_Product WHERE M_Product_ID = " + sLine.GetM_Product_ID(), null, Get_Trx()));
if (VA038_AmortizationTemplate_ID > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID);
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID);
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
if (line.GetC_Charge_ID() > 0)
{
//MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName());
int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID
FROM C_Charge WHERE C_Charge_ID = " + sLine.GetC_Charge_ID(), null, Get_Trx()));
if (VA038_AmortizationTemplate_ID > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID);
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID);
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
}
// End Change Amortization process--------------
if (!line.Save())
{
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
count++;
}
ds.Dispose();
}
else
{
MInvoiceLine line = new MInvoiceLine(invoice);
// JID_1850 Avoid the duplicate charge line
if (sLine.GetC_Charge_ID() > 0 && (!isAllownonItem || _GenerateCharges))
{
continue;
}
line.SetShipLine(sLine);
line.SetQtyEntered(sLine.GetQtyEntered());
line.SetQtyInvoiced(sLine.GetMovementQty());
line.Set_ValueNoCheck("IsDropShip", sLine.Get_Value("IsDropShip")); //Arpit Rai 20-Sept-2017
// Change By Mohit Amortization process -------------
if (_CountVA038 > 0)
{
if (line.GetM_Product_ID() > 0)
{
//MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName());
int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID
FROM M_Product WHERE M_Product_ID = " + sLine.GetM_Product_ID(), null, Get_Trx()));
if (VA038_AmortizationTemplate_ID > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID);
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID);
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
if (line.GetC_Charge_ID() > 0)
{
//MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName());
int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID
FROM C_Charge WHERE C_Charge_ID = " + sLine.GetC_Charge_ID(), null, Get_Trx()));
if (VA038_AmortizationTemplate_ID > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID);
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID);
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
}
// End Change Amortization process--------------
if (!line.Save())
{
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
count++;
}
}
#region [Enhancement for Charges Distribution/Generation by Sukhwinder on 13 December 2017]
if (_GenerateCharges && count > 0)
{
StringBuilder OrderSql = new StringBuilder();
OrderSql.Append(" SELECT CO.C_ORDER_ID, "
+ " SUM(ML.QTYENTERED) AS MRLINEQTY, "
+ " SUM(OL.QTYENTERED) AS ORDERQTY "
+ " FROM M_INOUTLINE ML "
+ " INNER JOIN C_ORDERLINE OL "
+ " ON OL.C_ORDERLINE_ID = ML.C_ORDERLINE_ID "
+ " INNER JOIN C_ORDER CO "
+ " ON CO.C_ORDER_ID = OL.C_ORDER_ID "
+ " WHERE ML.M_INOUT_ID = " + _M_InOut_ID
+ " AND (OL.C_CHARGE_ID IS NULL "
+ " OR OL.C_CHARGE_ID = 0) "
+ " GROUP BY CO.C_ORDER_ID ");
DataSet OrderDS = DB.ExecuteDataset(OrderSql.ToString(), null, Get_Trx());
if (OrderDS != null && OrderDS.Tables[0].Rows.Count > 0)
{
StringBuilder ChargesSql = new StringBuilder();
for (int index = 0; index < OrderDS.Tables[0].Rows.Count; index++)
{
ds = null;
ChargesSql.Clear();
ChargesSql.Append(" SELECT C_CHARGE_ID, "
+ " C_ORDERLINE_ID, "
+ " C_ORDER_ID, "
+ " C_CURRENCY_ID, "
+ " PRICEENTERED, "
+ " PRICEACTUAL, "
+ " LINENETAMT, "
+ " QTYENTERED, "
+ " C_UOM_ID, "
+ " C_Tax_ID, "
+ " IsDropShip "
+ " FROM C_ORDERLINE "
+ " WHERE C_ORDER_ID IN "
+ " ( " + Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["C_ORDER_ID"])
+ " ) "
+ " AND C_CHARGE_ID IS NOT NULL "
+ " AND C_CHARGE_ID > 0 ");
ds = DB.ExecuteDataset(ChargesSql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MInvoiceLine line = new MInvoiceLine(invoice);
line.SetQty(1);
line.SetQtyEntered(1);
line.SetQtyInvoiced(1);
line.SetOrderLine(new MOrderLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ORDERLINE_ID"]), Get_Trx()));
line.SetC_Charge_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_CHARGE_ID"]));
line.SetC_UOM_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_UOM_ID"]));
line.SetC_Tax_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_TAX_ID"]));
decimal SumOfQty = 0;
if (Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]) == 0)
{
SumOfQty = 1;
}
else
{
SumOfQty = Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]);
}
decimal amt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["LINENETAMT"]) / SumOfQty) * Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["MRLINEQTY"]);
line.SetPrice(Decimal.Round(amt, 3));
line.SetPriceActual(Decimal.Round(amt, 3));
line.SetPriceEntered(Decimal.Round(amt, 3));
line.SetLineNetAmt(Decimal.Round(amt, 3));
line.Set_ValueNoCheck("IsDropShip", Util.GetValueOfString(ds.Tables[0].Rows[i]["ISDROPSHIP"]));
if (_CountVA038 > 0)
{
if (line.GetC_Charge_ID() > 0)
{
MCharge charge = new MCharge(GetCtx(), line.GetC_Charge_ID(), Get_TrxName());
if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0)
{
line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")));
AmortStartDate = null;
AmortEndDate = null;
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A")
{
AmortStartDate = invoice.GetDateAcct();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T")
{
AmortStartDate = invoice.GetDateInvoiced();
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M")
{
AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y")
{
AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"]));
}
line.Set_Value("FROMDATE", AmortStartDate);
line.Set_Value("EndDate", AmortEndDate);
if (amrtDS != null)
{
amrtDS.Dispose();
}
}
}
}
if (!line.Save())
{
//SI_0708 - message was not upto the mark
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
}
}
}
}
}
#endregion
if (count > 0)
{
return(invoice.GetDocumentNo());
}
else
{
//Get_Trx().Rollback();
throw new ArgumentException(Msg.GetMsg(GetCtx(), "InvoiceExist") + ": " + invDocumentNo.ToString());
}
}
/// <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'";
//DataSet idr = null;
sql = "SELECT DISTINCT(M_Product_ID), 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 (fl.c_forecast_id = f.c_forecast_id) WHERE f.c_period_id = " + C_Period_ID +
" AND f.ad_client_id = " + mf.GetAD_Client_ID() + " AND f.isactive = 'Y' AND f.processed = 'Y' " +
"Group BY M_product_ID,f.C_Currency_ID";
try
{
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
Decimal?totalQtyTeam = 0;
Decimal?totalPriceTeam = 0;
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
for (int i = 0; i < dsForecast.Tables[0].Rows.Count; i++)
{
//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(_ds[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[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());
totalQtyTeam = Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["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(dsForecast.Tables[0].Rows[i]["M_Product_ID"]) +
" 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();
totalQtyOpp = 0;
totalPriceOpp = 0;
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
for (int k = 0; k < dsOpp.Tables[0].Rows.Count; k++)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp += MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[k]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[k]["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
decimal?ConvertedQty = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[k]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[k]["Quantity"]));
if (ConvertedQty == null)
{
totalQtyOpp += Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[k]["Quantity"]);
}
else
{
totalQtyOpp += ConvertedQty;
}
}
}
}
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, StdPrecision, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), 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 (Exception e)
{
log.Log(Level.SEVERE, e.Message);
}
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>
/// Create PO From SO
/// </summary>
/// <param name="so">sales order</param>
/// <returns>number of POs created</returns>
private int CreatePOFromSO(MOrder so)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlErrorMessage = new StringBuilder();
sqlErrorMessage.Clear();
string _Dropship = "";
log.Info(so.ToString());
MOrderLine[] soLines = so.GetLines(true, null);
if (soLines == null || soLines.Length == 0)
{
log.Warning("No Lines - " + so);
return(0);
}
//
int counter = 0;
// Order Lines with a Product which has a current vendor
sql.Append(@"SELECT DISTINCT po.C_BPartner_ID, po.M_Product_ID ,ol.Isdropship, po.PriceList , po.PricePO , po.C_Currency_ID
FROM M_Product_PO po
INNER JOIN M_Product prd ON po.M_Product_ID=prd.M_Product_ID
INNER JOIN C_OrderLine ol ON (po.M_Product_ID=ol.M_Product_ID "); // changes done by bharat on 26 June 2018 If purchased Checkbox is false on Finished Good Product, System should not generate Purchase Order.
sqlErrorMessage.Append(@"SELECT DISTINCT po.C_BPartner_ID, bp.name AS BPName, ol.M_Product_ID , p.Name, ol.Isdropship, po.C_Currency_ID, bp.PO_PaymentTerm_ID, bp.PO_PriceList_ID
FROM C_OrderLine ol INNER JOIN m_product p ON (p.M_Product_ID =ol.M_Product_ID)
LEFT JOIN M_Product_PO po ON (po.M_Product_ID=ol.M_Product_ID AND po.isactive = 'Y' AND po.IsCurrentVendor = 'Y' )
LEFT JOIN c_bpartner bp ON (bp.c_bpartner_id = po.c_bpartner_id ");
// Added by Vivek on 20/09/2017 Assigned By Pradeep for drop shipment
// if drop ship parameter is true then get all records true drop ship lines
if (_IsDropShip == "Y")
{
sql.Append(@"AND Ol.Isdropship='Y' ");
sqlErrorMessage.Append(@"AND Ol.Isdropship='Y' ");
}
// if drop ship parameter is false then get all records false drop ship lines
else if (_IsDropShip == "N")
{
sql.Append(@"AND Ol.Isdropship='N' ");
sqlErrorMessage.Append(@"AND Ol.Isdropship='N' ");
}
// changes don eby Bharat on 26 June 2018 to handle If purchased Checkbox is false on Finished Good Product, System should not generate Purchase Order.
sql.Append(@") WHERE ol.C_Order_ID=" + so.GetC_Order_ID() + @" AND po.IsCurrentVendor='Y' AND prd.IsPurchased='Y'");
sqlErrorMessage.Append(@") WHERE ol.C_Order_ID=" + so.GetC_Order_ID());
if (_Vendor_ID > 0)
{
sql.Append(@" AND po.C_BPartner_ID = " + _Vendor_ID);
sqlErrorMessage.Append(@" AND po.C_BPartner_ID = " + _Vendor_ID);
}
sql.Append(@" ORDER BY po.c_bpartner_id,ol.Isdropship ");
sqlErrorMessage.Append(@" ORDER BY po.c_bpartner_id,ol.Isdropship ");
// get error or setting message
GetErrorOrSetting(sqlErrorMessage.ToString(), Get_TrxName());
IDataReader idr = null;
MOrder po = null;
ConsolidatePO consolidatePO = null;
//ConsolidatePOLine consolidatePOLine = null;
try
{
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
// New Order
int C_BPartner_ID = Utility.Util.GetValueOfInt(idr[0]);//.getInt(1);
// Code Commented by Vivek Kumar on 20/09/2017 Assigned By Pradeep for drop shipment
//if (po == null || po.GetBill_BPartner_ID() != C_BPartner_ID)
//{
// po = CreatePOForVendor(Utility.Util.GetValueOfInt(idr[0]), so);
// AddLog(0, null, null, po.GetDocumentNo());
// counter++;
//
// check ANY PO created with same Business Partnet and Drop Shipment
if (_IsConsolidatedPO && listConsolidatePO.Count > 0)
{
ConsolidatePO poRecord;
if (listConsolidatePO.Exists(x => (x.C_BPartner_ID == C_BPartner_ID) && (x.IsDropShip == Utility.Util.GetValueOfString(idr[2]))))
{
poRecord = listConsolidatePO.Find(x => (x.C_BPartner_ID == C_BPartner_ID) && (x.IsDropShip == Utility.Util.GetValueOfString(idr[2])));
if (poRecord != null)
{
po = new MOrder(GetCtx(), poRecord.C_Order_ID, Get_Trx());
_Dropship = po.IsDropShip() ? "Y" : "N";
}
}
}
// Drop Shipment fucntionality added by Vivek on 20/09/2017 Assigned By Pradeep
if (po == null || po.GetBill_BPartner_ID() != C_BPartner_ID || _Dropship != Utility.Util.GetValueOfString(idr[2]))
{
po = CreatePOForVendor(Utility.Util.GetValueOfInt(idr[0]), so, Utility.Util.GetValueOfString(idr[2]));
if (po == null)
{
return(counter);
}
// AddLog(0, null, null, po.GetDocumentNo());
counter++;
// maintain list
if (po != null && po.GetC_Order_ID() > 0)
{
consolidatePO = new ConsolidatePO();
consolidatePO.C_Order_ID = po.GetC_Order_ID();
consolidatePO.C_BPartner_ID = C_BPartner_ID;
consolidatePO.IsDropShip = Utility.Util.GetValueOfString(idr[2]);
listConsolidatePO.Add(consolidatePO);
}
}
_Dropship = Utility.Util.GetValueOfString(idr[2]);
// Line
int M_Product_ID = Utility.Util.GetValueOfInt(idr[1]);//.getInt(2);
for (int i = 0; i < soLines.Length; i++)
{
// When Drop ship parameter is yes but SO line does not contains any drop shipment product
if (_IsDropShip == "Y" && Util.GetValueOfBool(soLines[i].IsDropShip()) == false)
{
continue;
}
// When Drop ship parameter is NO but SO line contains drop shipment product then it also does not generate any
else if (_IsDropShip == "N" && Util.GetValueOfBool(soLines[i].IsDropShip()) == true)
{
continue;
}
//When Drop ship parameter is yes and SO line also contains drop shipment product
else
{
String _Drop = "N";
if (Util.GetValueOfBool(soLines[i].IsDropShip()))
{
_Drop = "Y";
}
if (soLines[i].GetM_Product_ID() == M_Product_ID && _Drop == _Dropship)
{
MOrderLine poLine = new MOrderLine(po);
poLine.SetRef_OrderLine_ID(soLines[i].GetC_OrderLine_ID());
poLine.SetM_Product_ID(soLines[i].GetM_Product_ID());
poLine.SetM_AttributeSetInstance_ID(soLines[i].GetM_AttributeSetInstance_ID());
poLine.SetC_UOM_ID(soLines[i].GetC_UOM_ID());
poLine.SetQtyEntered(soLines[i].GetQtyEntered());
poLine.SetQtyOrdered(soLines[i].GetQtyOrdered());
poLine.SetDescription(soLines[i].GetDescription());
poLine.SetDatePromised(soLines[i].GetDatePromised());
poLine.SetIsDropShip(soLines[i].IsDropShip());
poLine.SetPrice();
// Set value in Property From Process to check on Before Save.
poLine.SetFromProcess(true);
if (!poLine.Save())
{
ValueNamePair pp = VLogger.RetrieveError();
log.Info("CreatePOfromSO : Not Saved. Error Value : " + pp.GetValue() + " , Error Name : " + pp.GetName());
}
//else
//{
// if (poLine != null && poLine.GetC_OrderLine_ID() > 0)
// {
// consolidatePOLine = new ConsolidatePOLine();
// consolidatePOLine.C_Order_ID = poLine.GetC_Order_ID();
// consolidatePOLine.C_OrderLine_ID = poLine.GetC_OrderLine_ID();
// consolidatePOLine.M_Product_ID = poLine.GetM_Product_ID();
// consolidatePOLine.M_AttributeSetInstance_ID = poLine.GetM_AttributeSetInstance_ID();
// consolidatePOLine.C_UOM_ID = poLine.GetC_UOM_ID();
// consolidatePOLine.IsDropShip = soLines[i].IsDropShip() ? "Y" : "N";
// listConsolidatePOLine.Add(consolidatePOLine);
// }
//}
}
}
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Set Reference to PO
if (po != null)
{
so.SetRef_Order_ID(po.GetC_Order_ID());
so.Save();
}
return(counter);
}
/// <summary>
/// Create version fields against version tab
/// </summary>
/// <param name="tab"> Object of MTab </param>
/// <param name="Ver_AD_Tab_ID"> Tab ID of Version window </param>
/// <param name="Ver_AD_Table_ID"> Table ID of Version </param>
/// <returns>string (Message)</returns>
private string CreateVerFields(MTab tab, int Ver_AD_Tab_ID, int Ver_AD_Table_ID)
{
// Get all fields from Master Tab
int[] fields = MTable.GetAllIDs("AD_Field", "AD_Tab_ID = " + tab.GetAD_Tab_ID(), Get_TrxName());
bool hasOrigCols = false;
bool hasVerFields = false;
bool hasVerCols = false;
// Get columns from Master table
DataSet origColDS = DB.ExecuteDataset("SELECT AD_Column_ID, Name,ColumnSql, ColumnName FROM AD_Column WHERE AD_Table_ID = " + tab.GetAD_Table_ID(), null, Get_TrxName());
if (origColDS != null && origColDS.Tables[0].Rows.Count > 0)
{
hasOrigCols = true;
}
// Get fields from Version Tab
DataSet verFieldDS = DB.ExecuteDataset("SELECT f.AD_Column_ID, f.Name, f.AD_Field_ID, (SELECT c.AD_Element_ID FROM AD_Column c WHERE c.AD_Column_ID = f.AD_Column_ID) AS AD_Element_ID FROM AD_Field f WHERE f.AD_Tab_ID = " + Ver_AD_Tab_ID, null, Get_TrxName());
if (verFieldDS != null && verFieldDS.Tables[0].Rows.Count > 0)
{
hasVerFields = true;
}
// Get Columns from Version Table
DataSet verColumnsDS = DB.ExecuteDataset("SELECT AD_Column_ID, Name, ColumnName FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, Get_TrxName());
if (verColumnsDS != null && verColumnsDS.Tables[0].Rows.Count > 0)
{
hasVerCols = true;
}
StringBuilder sbColName = new StringBuilder("");
foreach (int fld in fields)
{
bool createNew = true;
MField origFld = new MField(GetCtx(), fld, Get_TrxName());
// check if Field already exist for Version Tab else create new
if (hasVerFields)
{
DataRow[] drFld = verFieldDS.Tables[0].Select("Name = '" + origFld.GetName() + "'");
if (drFld.Length > 0)
{
createNew = false;
}
}
// if Field do not exist on Version tab
if (createNew)
{
sbColName.Clear();
int VerColID = 0;
// Get column Info from Column ID of Master Table
DataRow[] drOrigColName = origColDS.Tables[0].Select("AD_Column_ID = " + origFld.GetAD_Column_ID());
if (drOrigColName.Length > 0)
{
if (Util.GetValueOfString(drOrigColName[0]["ColumnSQL"]).Trim() != "")
{
continue;
}
sbColName.Append(Util.GetValueOfString(drOrigColName[0]["ColumnName"]));
// check whether Column exist in Version table with column name of Master Table
// if column not found return with Message
DataRow[] drVerCol = verColumnsDS.Tables[0].Select("ColumnName = '" + sbColName.ToString() + "'");
if (drVerCol.Length > 0)
{
VerColID = Util.GetValueOfInt(drVerCol[0]["AD_Column_ID"]);
}
else
{
log.Log(Level.SEVERE, "Version Column Not Found :: " + sbColName.ToString());
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNameNotFound") + " :: " + sbColName.ToString());
}
}
else
{
log.Log(Level.SEVERE, "Column ID not found in Original table :: " + origFld.GetAD_Column_ID());
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNameNotFound") + " :: " + origFld.GetAD_Column_ID());
}
// Only create Version Field if Version Column found
if (VerColID > 0)
{
// check if field is already created with column
// else skip creating field
int fldID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Field_ID FROM AD_Field WHERE AD_Column_ID = " + VerColID + " AND AD_Tab_ID = " + Ver_AD_Tab_ID, null, Get_TrxName()));
if (fldID <= 0)
{
// Create Field for Column, copy field from Master tables Field tab on Version Field against Version Tab
MField verFld = new MField(GetCtx(), 0, Get_TrxName());
origFld.CopyTo(verFld);
verFld.SetAD_Tab_ID(Ver_AD_Tab_ID);
verFld.SetAD_Column_ID(VerColID);
verFld.SetExport_ID(null);
verFld.SetIsReadOnly(true);
if (!verFld.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating Version Field";
}
log.Log(Level.SEVERE, "Version Field not saved :: " + verFld.GetName() + " :: " + error);
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "FieldNotSaved") + " :: " + verFld.GetName());
}
}
}
}
}
// Fill Dataset again from Version Field
verFieldDS = DB.ExecuteDataset("SELECT f.AD_Column_ID, f.Name, f.AD_Field_ID, (SELECT c.AD_Element_ID FROM AD_Column c WHERE c.AD_Column_ID = f.AD_Column_ID) AS AD_Element_ID FROM AD_Field f WHERE f.AD_Tab_ID = " + Ver_AD_Tab_ID, null, Get_TrxName());
// Create Default Fields against default Columns for Version tab
string retMsg = CreateDefaultFields(Ver_AD_Tab_ID, verFieldDS, hasVerFields, verColumnsDS, Ver_AD_Table_ID);
if (retMsg != "")
{
Get_TrxName().Rollback();
return(retMsg);
}
return("");
}
public string DoIt(int recordID, Ctx ct, Trx trx, int CurrencyType_ID)
{
//Check Bank Response
// sql.Append(@"SELECT count(bd.VA009_BankResponse) FROM va009_batchlinedetails bd INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bd.va009_batchlines_id
// WHERE bl.va009_batch_id=" + recordID + " AND bd.VA009_BankResponse='IP' AND bd.AD_Client_ID = " + ct.GetAD_Client_ID() + " Group by bd.VA009_BankResponse ");
// countresponse = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null,trx));
sql.Clear();
sql.Append(@"SELECT b.c_bankaccount_id, bl.c_bpartner_id, bld.c_currency_id, bld.c_invoice_id, bld.dueamt, bld.VA009_ConvertedAmt, bld.discountamt, bld.va009_batchlinedetails_ID , bl.va009_batchlines_id ,
bld.discountdate, inv.issotrx, inv.isreturntrx, bld.c_invoicepayschedule_id, bld.ad_org_id, bld.ad_client_id , doc.DocBaseType , bld.va009_paymentmethod_id , bl.VA009_DueAmount
FROM va009_batchlinedetails bld INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bld.va009_batchlines_id
INNER JOIN va009_batch b ON b.va009_batch_id =bl.va009_batch_id INNER JOIN c_invoice inv ON inv.c_invoice_id = bld.c_invoice_id
INNER JOIN C_DocType doc ON doc.C_Doctype_ID = inv.C_Doctype_ID
WHERE NVL(bl.c_payment_id , 0) = 0 AND NVL(bld.c_payment_id , 0) = 0 AND NVL(bld.C_AllocationHdr_ID , 0) = 0 AND b.va009_batch_id =" + recordID);
if (IsBankresponse == "Y" && countresponse == 0)
{
sql.Append(" AND bld.va009_bankresponse='RE' ORDER BY bl.c_bpartner_id ASC ");
}
else if (IsBankresponse == "N")
{
sql.Append(" ORDER BY bld.va009_batchlines_id , bl.c_bpartner_id ASC ");
}
//else if (IsBankresponse == "Y" && countresponse > 0)
// return Msg.GetMsg(ct, "VA009_AllResponseNotAvailable");
DataSet ds = DB.ExecuteDataset(sql.ToString(), null, trx);
MVA009Batch _batch = new MVA009Batch(ct, recordID, trx);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
#region Consolidate = true
if (_batch.IsVA009_Consolidate() == true)
{
int c_currency_id = 0; int Bpartner_ID = 0; int C_Payment_ID = 0, batchline_id = 0, allocationHeader = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
#region Create View Allocation Header and line when the Due Amount on Batch line = 0
if (c_currency_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) &&
Bpartner_ID == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]) &&
batchline_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]) &&
Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_DueAmount"]) == 0)
{
MAllocationLine alloclne = new MAllocationLine(ct, 0, trx);
alloclne.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
alloclne.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
alloclne.SetC_AllocationHdr_ID(allocationHeader);
alloclne.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
alloclne.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
alloclne.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
alloclne.SetDateTrx(System.DateTime.Now.ToLocalTime());
alloclne.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
alloclne.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
if (!alloclne.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
// set Allocation ID on Batch Line Details
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_AllocationHdr_ID(allocationHeader);
batchLineDetails.Save();
}
}
else if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_DueAmount"]) == 0)
{
MAllocationHdr allocHdr = new MAllocationHdr(ct, 0, trx);
allocHdr.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
allocHdr.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
allocHdr.SetDateAcct(System.DateTime.Now.ToLocalTime());
allocHdr.SetDateTrx(System.DateTime.Now.ToLocalTime());
allocHdr.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
allocHdr.SetDocStatus("DR");
allocHdr.SetDocAction("CO");
if (!allocHdr.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
if (!viewAllocationId.Contains(allocHdr.GetC_AllocationHdr_ID()))
{
viewAllocationId.Add(allocHdr.GetC_AllocationHdr_ID());
}
MAllocationLine alloclne = new MAllocationLine(ct, 0, trx);
alloclne.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
alloclne.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
alloclne.SetC_AllocationHdr_ID(allocHdr.GetC_AllocationHdr_ID());
alloclne.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
alloclne.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
alloclne.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
alloclne.SetDateTrx(System.DateTime.Now.ToLocalTime());
alloclne.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
alloclne.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
if (!alloclne.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
c_currency_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]);
Bpartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]);
batchline_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]);
allocationHeader = allocHdr.GetC_AllocationHdr_ID();
allocationDocumentNo += allocHdr.GetDocumentNo() + " , ";
// set Allocation ID on Batch Line
//batchLines = new MVA009BatchLines(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]), trx.trx);
//batchLineDetails.SetC_AllocationHdr_ID(allocationHeader);
//batchLines.Save();
// set Allocation ID on Batch Line Details
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_AllocationHdr_ID(allocationHeader);
batchLineDetails.Save();
}
}
}
#endregion
#region Create a new entry of payment Allocate against same payment and the condition
else if (c_currency_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) &&
Bpartner_ID == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]) &&
batchline_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]))
{
MPaymentAllocate PayAlocate = new MPaymentAllocate(ct, 0, trx);
PayAlocate.SetC_Payment_ID(C_Payment_ID);
PayAlocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
//if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
//{
// PayAlocate.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
// PayAlocate.SetAmount(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
// PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
//}
//else
//{
PayAlocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
PayAlocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]));
//}
PayAlocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
PayAlocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
PayAlocate.SetWriteOffAmt(0);
PayAlocate.SetOverUnderAmt(0);
if (!PayAlocate.Save())
{
msg = Msg.GetMsg(ct, "VA009_PymentAllocateNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_Payment_ID(C_Payment_ID);
batchLineDetails.Save();
}
}
#endregion
#region Create a new payment
else
{
MPayment _pay = new MPayment(ct, 0, trx);
int C_Doctype_ID = GetDocumnetType(Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]), ct);
_pay.SetC_DocType_ID(C_Doctype_ID);
_pay.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
_pay.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
_pay.SetDateAcct(System.DateTime.Now);
_pay.SetDateTrx(System.DateTime.Now);
_pay.SetC_BankAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bankaccount_id"]));
_pay.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
_pay.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
_pay.SetC_ConversionType_ID(CurrencyType_ID);
_pay.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
tenderType = Util.GetValueOfString(DB.ExecuteScalar(@"select VA009_PAYMENTBASETYPE from VA009_PAYMENTMETHOD where VA009_PAYMENTMETHOD_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])));
if (tenderType == "K") // Credit Card
{
_pay.SetTenderType("C");
}
else if (tenderType == "D") // Direct Debit
{
_pay.SetTenderType("D");
}
else if (tenderType == "S") // Check
{
_pay.SetTenderType("K");
}
else if (tenderType == "T") // Direct Deposit
{
_pay.SetTenderType("A");
}
else
{
_pay.SetTenderType("A");
}
if (!_pay.Save())
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
if (!payment.Contains(_pay.GetC_Payment_ID()))
{
payment.Add(_pay.GetC_Payment_ID());
}
c_currency_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]);
Bpartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]);
C_Payment_ID = _pay.GetC_Payment_ID();
batchline_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]);
paymentDocumentNo += _pay.GetDocumentNo() + " , ";
MPaymentAllocate PayAlocate = new MPaymentAllocate(ct, 0, trx);
PayAlocate.SetC_Payment_ID(C_Payment_ID);
PayAlocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
//if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
//{
// PayAlocate.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
// PayAlocate.SetAmount(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
// PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
//}
//else
//{
PayAlocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
PayAlocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]));
//}
PayAlocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
PayAlocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
PayAlocate.SetWriteOffAmt(0);
PayAlocate.SetOverUnderAmt(0);
if (!PayAlocate.Save())
{
msg = Msg.GetMsg(ct, "VA009_PymentAllocateNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_Payment_ID(_pay.GetC_Payment_ID());
batchLineDetails.Save();
batchLines = new MVA009BatchLines(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]), trx);
batchLines.SetC_Payment_ID(_pay.GetC_Payment_ID());
batchLines.Save();
}
}
}
#endregion
}
// Complete the Consolidate Records of payment
for (int i = 0; i < payment.Count(); i++)
{
MPayment completePayment = new MPayment(ct, payment[i], trx);
if (completePayment.CompleteIt() == "CO")
{
completePayment.SetDocStatus("CO");
completePayment.SetDocAction("CL");
completePayment.Save();
}
}
// Complete the Consolidate Records of View allocation
for (int i = 0; i < viewAllocationId.Count(); i++)
{
MAllocationHdr completeAllocation = new MAllocationHdr(ct, viewAllocationId[i], trx);
if (completeAllocation.CompleteIt() == "CO")
{
completeAllocation.SetDocStatus("CO");
completeAllocation.SetDocAction("CL");
completeAllocation.Save();
}
}
}
#endregion
#region Consolidate = false
else if (_batch.IsVA009_Consolidate() == false)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MPayment _pay = new MPayment(ct, 0, trx);
int C_Doctype_ID = GetDocumnetType(Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]), ct);
_pay.SetC_DocType_ID(C_Doctype_ID);
_pay.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
_pay.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
_pay.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
_pay.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
_pay.SetDateAcct(System.DateTime.Now);
_pay.SetDateTrx(System.DateTime.Now);
_pay.SetC_ConversionType_ID(CurrencyType_ID);
_pay.SetC_BankAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bankaccount_id"]));
_pay.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
//if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
//{
// _pay.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
// _pay.SetPayAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
//}
//else
//{
_pay.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
//_pay.SetPayAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]));
_pay.SetPayAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
//}
_pay.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
_pay.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
tenderType = Util.GetValueOfString(DB.ExecuteScalar(@"select VA009_PAYMENTBASETYPE from VA009_PAYMENTMETHOD where VA009_PAYMENTMETHOD_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])));
if (tenderType == "K") // Credit Card
{
_pay.SetTenderType("C");
}
else if (tenderType == "D") // Direct Debit
{
_pay.SetTenderType("D");
}
else if (tenderType == "S") // Check
{
_pay.SetTenderType("K");
}
else if (tenderType == "T") // Direct Deposit
{
_pay.SetTenderType("A");
}
else
{
_pay.SetTenderType("A");
}
if (!_pay.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
paymentDocumentNo += _pay.GetDocumentNo() + " , ";
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_Payment_ID(_pay.GetC_Payment_ID());
batchLineDetails.Save(trx);
if (_pay.CompleteIt() == "CO")
{
_pay.SetDocStatus("CO");
_pay.SetDocAction("CL");
_pay.Save(trx);
}
}
}
}
#endregion
}
else
{
return(msg = Msg.GetMsg(ct, "VA009_LinesNotAvailable"));
}
if (paymentDocumentNo != "" || allocationDocumentNo != "")
{
SaveRecordPaymentBachLog(_batch.GetAD_Client_ID(), _batch.GetAD_Org_ID(), recordID, paymentDocumentNo, allocationDocumentNo, ct, trx);
}
return(msg);
}
protected override string DoIt()
{
MOrder obj = new MOrder(GetCtx(), GetRecord_ID(), Get_Trx());
// get Precision for rounding
MCurrency currency = new MCurrency(GetCtx(), obj.GetC_Currency_ID(), Get_Trx());
precision = currency.GetStdPrecision();
MOrderLine[] lines = obj.GetLines();
if (_IsCLearDiscount == "N")
{
if (_DiscountAmt == 0 && _DiscountPercent == 0)
{
return(Msg.GetMsg(GetCtx(), "PlsSelAtlstOneField"));
}
if (_DiscountAmt != 0 && _DiscountPercent != 0)
{
return(Msg.GetMsg(GetCtx(), "PlsSelOneField"));
}
// get amount on which we have to apply discount
subTotal = obj.GetTotalLines();
// when we are giving discount in terms of amount, then we have to calculate discount in term of percentage
discountPercentageOnTotalAmount = GetDiscountPercentageOnTotal(subTotal, _DiscountAmt, precision);
for (int i = 0; i < lines.Length; i++)
{
MOrderLine ln = lines[i];
// this value represent discount on line net amount
discountAmountOnTotal = GetDiscountAmountOnTotal(ln.GetLineNetAmt(), discountPercentageOnTotalAmount != 0 ? discountPercentageOnTotalAmount : _DiscountPercent);
// this value represent discount on unit price of 1 qty
discountAmountOnTotal = Decimal.Round(Decimal.Divide(discountAmountOnTotal, ln.GetQtyEntered()), precision);
if (discountPercentageOnTotalAmount != 0 && _DiscountAmt != 0)
{
if (i != lines.Length - 1)
{
// reduce discounted amount from total discount
_DiscountAmt -= discountAmountOnTotal;
}
else if (i == lines.Length - 1)
{
// when last iteration, set remaning amount
discountAmountOnTotal = _DiscountAmt;
}
}
ln.SetAmountAfterApplyDiscount(Decimal.Add(ln.GetAmountAfterApplyDiscount(), discountAmountOnTotal));
ln.SetPriceActual(Decimal.Round(Decimal.Subtract(ln.GetPriceActual(), discountAmountOnTotal), precision));
ln.SetPriceEntered(Decimal.Round(Decimal.Subtract(ln.GetPriceEntered(), discountAmountOnTotal), precision));
if (!ln.Save(Get_TrxName()))
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("ApplyDiscountOnOrder : Not Saved. Error Value : " + pp.GetValue() + " , Error Name : " + pp.GetName());
throw new Exception(Msg.GetMsg(GetCtx(), "DiscNotApplied"));
}
}
return(Msg.GetMsg(GetCtx(), "DiscAppliedSuccess"));
}
else
{
for (int i = 0; i < lines.Length; i++)
{
MOrderLine ln = lines[i];
ln.SetPriceEntered(Decimal.Add(ln.GetPriceEntered(), ln.GetAmountAfterApplyDiscount()));
ln.SetPriceActual(Decimal.Add(ln.GetPriceActual(), ln.GetAmountAfterApplyDiscount()));
ln.SetAmountAfterApplyDiscount(0);
if (!ln.Save())
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("ApplyDiscountOnOrder : Not Saved. Error Value : " + pp.GetValue() + " , Error Name : " + pp.GetName());
throw new Exception(Msg.GetMsg(GetCtx(), "DiscNotCleared"));
}
}
return(Msg.GetMsg(GetCtx(), "DiscClearedSuccessfully"));
}
}
/// <summary>
/// Is used to save data on movememt line
/// </summary>
/// <param name="mData"></param>
/// <returns></returns>
public String SaveMovementLine(List <Dictionary <string, string> > mData)
{
StringBuilder error = new StringBuilder();
bool isMoveFullContainer = false;
bool isMoveFullContainerQty = false;
Trx trx = Trx.GetTrx("Movement");
if (mData.Count > 0)
{
isMoveFullContainer = Util.GetValueOfBool(mData[0]["IsFullMoveContainer"]);
//to delete all the movement lines where MoveFullContainer is False
if (isMoveFullContainer)
{
DB.ExecuteQuery("DELETE FROM M_MovementLine WHERE M_Movement_ID = " + Util.GetValueOfInt(mData[0]["M_Movement_ID"]) + " AND MoveFullContainer= 'N' ", null, null);
}
// Lines not inserted, as movement line already has a full move container line.
if (!isMoveFullContainer)
{
if (Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(MoveFullContainer) FROM M_MovementLine WHERE MoveFullContainer= 'Y' AND M_Movement_ID = "
+ Util.GetValueOfInt(mData[0]["M_Movement_ID"]), null, trx)) > 0)
{
trx.Close();
return(Msg.GetMsg(_ctx, "VIS_LinehaveFullContainer"));
}
}
// Get Line No
int lineNo = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(MAX(Line),0) AS DefaultValue FROM M_MovementLine WHERE M_Movement_ID="
+ Util.GetValueOfInt(mData[0]["M_Movement_ID"]), null, trx));
isMoveFullContainerQty = Util.GetValueOfBool(mData[0]["IsfullContainerQtyWise"]);
if (!isMoveFullContainer && !isMoveFullContainerQty)
{
goto moveFullContainer;
}
else if (isMoveFullContainer || isMoveFullContainerQty)
{
error.Clear();
error.Append(SaveMoveLinewithFullContainer(Util.GetValueOfInt(mData[0]["M_Movement_ID"]),
Util.GetValueOfInt(mData[0]["FromLocator"]),
Util.GetValueOfInt(mData[0]["FromContainer"]),
Util.GetValueOfInt(mData[0]["ToLocator"]),
Util.GetValueOfInt(mData[0]["ToContainer"]),
lineNo, isMoveFullContainerQty,
trx));
trx.Close();
return(error.ToString());
}
moveFullContainer:
MMovementLine moveline = null;
MProduct product = null;
int moveId = 0;
for (int i = 0; i < mData.Count; i++)
{
#region Quantity Only
MMovement move = new MMovement(_ctx, Util.GetValueOfInt(mData[i]["M_Movement_ID"]), null);
moveId = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(M_MovementLine_ID, 0) AS M_Movement_ID FROM M_MovementLine WHERE
M_Movement_ID = " + Util.GetValueOfInt(mData[i]["M_Movement_ID"]) +
@" AND M_Product_ID = " + Util.GetValueOfInt(mData[i]["M_Product_ID"]) +
@" AND NVL(M_AttributeSetInstance_ID, 0) = " + Util.GetValueOfInt(mData[i]["M_AttributeSetInstance_ID"]) +
@" AND M_Locator_ID = " + Util.GetValueOfInt(mData[i]["FromLocator"]) +
@" AND NVL(M_ProductContainer_ID, 0) = " + Util.GetValueOfInt(mData[i]["FromContainer"]) +
@" AND M_LocatorTo_ID = " + Util.GetValueOfInt(mData[i]["ToLocator"]) +
@" AND NVL(Ref_M_ProductContainerTo_ID, 0) = " + Util.GetValueOfInt(mData[i]["ToContainer"]) +
@" AND AD_Org_ID = " + move.GetAD_Org_ID()));
if (moveId > 0)
{
moveline = new MMovementLine(_ctx, moveId, trx);
}
else
{
moveline = new MMovementLine(_ctx, 0, trx);
}
if (moveId == 0)
{
#region Create new record of movement line
lineNo += 10;
moveline.SetAD_Client_ID(move.GetAD_Client_ID());
moveline.SetAD_Org_ID(move.GetAD_Org_ID());
moveline.SetM_Movement_ID(move.GetM_Movement_ID());
moveline.SetLine(lineNo);
moveline.SetM_Product_ID(Util.GetValueOfInt(mData[i]["M_Product_ID"]));
moveline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(mData[i]["M_AttributeSetInstance_ID"]));
moveline.SetC_UOM_ID(Util.GetValueOfInt(mData[i]["C_UOM_ID"]));
moveline.SetM_Locator_ID(Util.GetValueOfInt(mData[i]["FromLocator"]));
moveline.SetM_LocatorTo_ID(Util.GetValueOfInt(mData[i]["ToLocator"]));
moveline.SetM_ProductContainer_ID(Util.GetValueOfInt(mData[i]["FromContainer"]));
moveline.SetRef_M_ProductContainerTo_ID(Util.GetValueOfInt(mData[i]["ToContainer"]));
moveline.SetQtyEntered(Util.GetValueOfDecimal(mData[i]["MoveQty"]));
moveline.SetMovementQty(Util.GetValueOfDecimal(mData[i]["MoveQty"]));
moveline.SetMoveFullContainer(Util.GetValueOfBool(mData[i]["IsFullMoveContainer"]));
#endregion
}
else
{
#region Update record of movement line
moveline.SetQtyEntered(Decimal.Add(moveline.GetQtyEntered(), Util.GetValueOfDecimal(mData[i]["MoveQty"])));
moveline.SetMovementQty(Decimal.Add(moveline.GetMovementQty(), Util.GetValueOfDecimal(mData[i]["MoveQty"])));
moveline.SetMoveFullContainer(Util.GetValueOfBool(mData[i]["IsFullMoveContainer"]));
#endregion
}
if (!moveline.Save(trx))
{
#region Save error catch and rollback
product = MProduct.Get(_ctx, Util.GetValueOfInt(mData[i]["M_Product_ID"]));
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
_log.SaveError("Movement line not inserted through Move Container Form : ", pp.GetName());
if (String.IsNullOrEmpty(error.ToString()))
{
error.Append(Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
else
{
error.Append(" , " + Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
}
trx.Rollback();
#endregion
}
else
{
trx.Commit();
}
#endregion
}
}
trx.Close();
return(String.IsNullOrEmpty(error.ToString()) ? "VIS_SuccessFullyInserted" : error.ToString());
}
