/// <summary>
/// Get Invoices
/// </summary>
/// <param name="ctx">Context</param>
/// <param name="displays">Display Columns</param>
/// <param name="cBPartnerId">Business Partner</param>
/// <param name="isReturnTrxs">Return Transaction</param>
/// <param name="IsDrop">Drop Shipemnt</param>
/// <returns>List<VCreateFromGetCOrder>, List of Invoices</returns>
public List <VCreateFromGetCOrder> GetInvoicesVCreate(Ctx ctx, string displays, int cBPartnerId, bool isReturnTrxs, bool IsDrop)
{
List <VCreateFromGetCOrder> obj = new List <VCreateFromGetCOrder>();
string sql = "SELECT i.C_Invoice_ID," + displays + " AS displays FROM C_Invoice i INNER JOIN C_DocType d ON (i.C_DocType_ID = d.C_DocType_ID) "
// New column added to fill invoice which drop ship is true
+ "WHERE i.C_BPartner_ID=" + cBPartnerId + " AND i.IsSOTrx='N' AND i.IsDropShip='" + (IsDrop ? "Y" : "N") + "' "
+ "AND d.IsReturnTrx='" + (isReturnTrxs ? "Y" : "N") + "' AND i.DocStatus IN ('CL','CO') "
//Invoice vendor record created with Document Type having checkbox 'Treat As Discount' is true will not show on 'Create line From' on window : Return to Vendor.
+ " AND i.TreatAsDiscount = 'N' "
+ " AND i.C_Invoice_ID IN "
+ "(SELECT C_Invoice_ID FROM (SELECT il.C_Invoice_ID,il.C_InvoiceLine_ID,il.QtyInvoiced,mi.Qty FROM C_InvoiceLine il "
+ " LEFT OUTER JOIN M_MatchInv mi ON (il.C_InvoiceLine_ID=mi.C_InvoiceLine_ID) "
+ " INNER JOIN M_Product Mp On Mp.M_Product_Id = Il.M_Product_Id "
+ " WHERE Mp.ProductType = 'I' AND (il.QtyInvoiced <> nvl(mi.Qty,0) AND mi.C_InvoiceLine_ID IS NOT NULL And Mp.Iscostadjustmentonlost = 'N') "
+ " OR (NVL(Mi.Qty,0) = 0 AND Mi.C_Invoiceline_Id IS NOT NULL AND Mp.Iscostadjustmentonlost = 'Y') "
+ " OR mi.C_InvoiceLine_ID IS NULL ) GROUP BY C_Invoice_ID,C_InvoiceLine_ID,QtyInvoiced "
+ " HAVING QtyInvoiced > SUM(nvl(Qty,0))) ORDER BY i.DateInvoiced, i.DocumentNo";
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
VCreateFromGetCOrder objc = new VCreateFromGetCOrder();
objc.key = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"]);
objc.value = Util.GetValueOfString(ds.Tables[0].Rows[i]["displays"]);
obj.Add(objc);
}
}
return(obj);
}
/// <summary>
//// get orders
/// </summary>
/// <param name="ctx">Context</param>
/// <param name="display">Display Columns</param>
/// <param name="column">Column Name</param>
/// <param name="C_BPartner_ID">Business Partner</param>
/// <param name="isReturnTrx">Return Transaction</param>
/// <param name="OrgId">Organization</param>
/// <param name="DropShip">Drop Shipment</param>
/// <param name="IsSOTrx">Sales Transaction</param>
/// <param name="forInvoices">For Invoice</param>
/// <returns>List<VCreateFromGetCOrder>, List of Orders</returns>
public List <VCreateFromGetCOrder> VCreateGetOrders(Ctx ctx, string display, string column, int C_BPartner_ID, bool isReturnTrx, int OrgId, bool DropShip, bool IsSOTrx, bool forInvoices)
{
List <VCreateFromGetCOrder> obj = new List <VCreateFromGetCOrder>();
var dis = display;
MClient tenant = MClient.Get(ctx);
//Added O.ISSALESQUOTATION='N' in where condition(Sales quotation will not display in Order dropdown)
StringBuilder sql = new StringBuilder("SELECT o.C_Order_ID," + display + " AS displays FROM C_Order o WHERE o.C_BPartner_ID=" + C_BPartner_ID + " AND o.IsSOTrx ='" + (IsSOTrx ? "Y" : "N")
+ "' AND O.IsBlanketTrx = 'N' AND O.ISSALESQUOTATION='N' AND o.DocStatus IN ('CL','CO') ");
if (OrgId > 0)
{
sql.Append("AND o.AD_Org_ID = " + OrgId);
}
// when create lines fom open from M_Inout then pick records from match po having m_inoutline is not null
// when create lines fom open from M_Invoiceline then pick records from match po having m_invoiceline is not null
sql.Append("AND o.IsReturnTrx='" + (isReturnTrx ? "Y" : "N") + "' AND o.IsDropShip='" + (DropShip ? "Y" : "N") + "' AND o.C_Order_ID IN "
+ @"(SELECT C_Order_ID FROM (SELECT ol.C_Order_ID,ol.C_OrderLine_ID,ol.QtyOrdered,
(SELECT SUM(m.qty) FROM m_matchPO m WHERE ol.C_OrderLine_ID=m.C_OrderLine_ID AND NVL(" + column + @", 0) != 0 AND m.ISACTIVE = 'Y' ) AS Qty,
(SELECT SUM(IL.QtyInvoiced) FROM C_INVOICELINE IL INNER JOIN C_Invoice I ON I.C_INVOICE_ID = IL.C_INVOICE_ID
WHERE il.ISACTIVE = 'Y' AND I.DOCSTATUS NOT IN ('VO','RE') AND OL.C_ORDERLINE_ID =IL.C_ORDERLINE_ID) AS QtyInvoiced FROM C_OrderLine ol ");
// Get Orders based on the setting taken on Tenant to allow non item Product
if (!forInvoices && tenant.Get_ColumnIndex("IsAllowNonItem") > 0 && !tenant.IsAllowNonItem())
{
sql.Append("INNER JOIN M_Product p ON ol.M_Product_ID = p.M_Product_ID AND p.ProductType = 'I'");
}
sql.Append(") GROUP BY C_Order_ID,C_OrderLine_ID,QtyOrdered "
+ "HAVING QtyOrdered > SUM(nvl(Qty,0)) AND QtyOrdered > SUM(NVL(QtyInvoiced,0))) ORDER BY o.DateOrdered, o.DocumentNo");
DataSet ds = DB.ExecuteDataset(sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
VCreateFromGetCOrder objc = new VCreateFromGetCOrder();
objc.key = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Order_ID"]);
objc.value = Util.GetValueOfString(ds.Tables[0].Rows[i]["displays"]);
obj.Add(objc);
}
}
return(obj);
}
/// <summary>
/// Get Shipment data
/// </summary>
/// <param name="ctx"></param>
/// <param name="displays"></param>
/// <param name="CBPartnerIDs"></param>
/// <returns></returns>
public List <VCreateFromGetCOrder> GetShipments(Ctx ctx, string displays, int CBPartnerIDs, bool IsDrop, bool IsSOTrx)
{
List <VCreateFromGetCOrder> obj = new List <VCreateFromGetCOrder>();
string sql = "SELECT s.M_InOut_ID," + displays + " AS dis FROM M_InOut s "
+ "WHERE s.C_BPartner_ID=" + CBPartnerIDs + " AND s.IsSOTrx='" + (IsSOTrx ? "Y" : "N") + "' AND s.DocStatus IN ('CL','CO')"
// New column added to fill invoice which drop ship is true
+ " AND s.IsDropShip='" + (IsDrop ? "Y" : "N") + "' AND s.M_InOut_ID IN "
// Changes done by Bharat on 06 July 2017 restrict to create invoice if Invoice already created against that for same quantity
+ "(SELECT M_InOut_ID FROM (SELECT sl.M_InOut_ID, sl.M_InOutLine_ID, sl.MovementQty, mi.QtyInvoiced FROM M_InOutLine sl "
+ "LEFT OUTER JOIN (SELECT il.QtyInvoiced, il.M_InOutLine_ID FROM C_InvoiceLine il INNER JOIN C_Invoice I ON I.C_INVOICE_ID = il.C_INVOICE_ID "
+ "WHERE i.DocStatus NOT IN ('VO','RE')) mi ON sl.M_InOutLine_ID=mi.M_InOutLine_ID) "
+ "GROUP BY M_InOut_ID, M_InOutLine_ID, MovementQty HAVING MovementQty > SUM(NVL(QtyInvoiced,0))) ORDER BY s.MovementDate, s.DocumentNo";
//+ "(SELECT M_InOut_ID FROM (SELECT sl.M_InOut_ID,sl.M_InOutLine_ID,sl.MovementQty,mi.Qty,IL.QtyInvoiced FROM M_InOutLine sl "
//+ "LEFT OUTER JOIN M_MatchInv mi ON (sl.M_InOutLine_ID=mi.M_InOutLine_ID) "
//+ " LEFT OUTER JOIN C_INVOICELINE IL ON (sl.C_ORDERLINE_ID =IL.C_ORDERLINE_ID)"
//+ " LEFT OUTER JOIN C_Invoice I ON I.C_INVOICE_ID =IL.C_INVOICE_ID "
//+ " AND I.DOCSTATUS NOT IN ('VO','RE') "
//+ " WHERE (sl.MovementQty <> nvl(mi.Qty,0) OR SL.MovementQty <> NVL(IL.QtyInvoiced,0)"
//+ "AND mi.M_InOutLine_ID IS NOT NULL) OR mi.M_InOutLine_ID IS NULL ) GROUP BY M_InOut_ID,M_InOutLine_ID,MovementQty "
//+ "HAVING MovementQty > SUM(nvl(Qty,0)) OR MovementQty > SUM(NVL(QtyInvoiced,0)) ) ORDER BY s.MovementDate";
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
VCreateFromGetCOrder objc = new VCreateFromGetCOrder();
objc.key = Util.GetValueOfInt(ds.Tables[0].Rows[i]["M_InOut_ID"]);
objc.value = Util.GetValueOfString(ds.Tables[0].Rows[i]["dis"]);
obj.Add(objc);
}
}
return(obj);
}
