/// <summary> /// Perrform Process. /// </summary> /// <returns>Message (clear text)</returns> protected override String DoIt() { log.Info("C_PaySelection_ID=" + _C_PaySelection_ID + ", PaymentRule=" + _PaymentRule); MPaySelection psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); if (psel.Get_ID() == 0) { throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); } if (psel.IsProcessed()) { throw new ArgumentException("@Processed@"); } // MPaySelectionLine[] lines = psel.GetLines(false); for (int i = 0; i < lines.Length; i++) { MPaySelectionLine line = lines[i]; if (!line.IsActive() || line.IsProcessed()) { continue; } CreateCheck(line); } // psel.SetProcessed(true); psel.Save(); return("@C_PaySelectionCheck_ID@ - #" + _list.Count); }
public Dictionary <string, object> GetScheduleData(Ctx ctx, string fields) { Dictionary <string, object> result = null; string[] paramValue = fields.Split(','); //Assign parameter value int C_Schedule_ID = Util.GetValueOfInt(paramValue[0].ToString()); int C_PaySelection_ID = Util.GetValueOfInt(paramValue[2].ToString()); MPaySelection paysel = new MPaySelection(ctx, C_PaySelection_ID, null); //End Assign parameter result = new Dictionary <string, object>(); string _sql = @"SELECT i.C_Invoice_ID, currencyConvert(ips.DUEAMT,ips.C_Currency_ID, " + paysel.GetC_Currency_ID() + @",TO_DATE('2018-12-21','YYYY-MM-DD'), i.C_ConversionType_ID,ips.AD_Client_ID,ips.AD_Org_ID) AS OpenAmt, I.PAYMENTRULE, ips.VA009_PAYMENTMETHOD_ID, i.IsSOTrx, CASE WHEN (TO_DATE('2018-12-21','YYYY-MM-DD') <= IPS.DISCOUNTDATE) THEN IPS.DISCOUNTAMT ELSE 0 END AS DISCOUNT1, CASE WHEN (TO_DATE('2018-12-21','YYYY-MM-DD') > IPS.DISCOUNTDATE AND TO_DATE('2018-12-21','YYYY-MM-DD') <= IPS.DISCOUNTDAYS2) THEN IPS.DISCOUNT2 ELSE 0 END AS DISCOUNT2 FROM C_INVOICEPAYSCHEDULE ips INNER JOIN C_INVOICE i on IPS.C_INVOICE_ID =I.C_INVOICE_ID WHERE ips.C_INVOICEPAYSCHEDULE_ID=" + C_Schedule_ID; DataSet ds = DB.ExecuteDataset(_sql); if (ds != null && ds.Tables[0].Rows.Count > 0) { result["C_Invoice_ID"] = Util.GetValueOfInt(ds.Tables[0].Rows[0]["C_Invoice_ID"]); result["OpenAmt"] = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["OpenAmt"]); result["PAYMENTRULE"] = Util.GetValueOfString(ds.Tables[0].Rows[0]["PAYMENTRULE"]); result["VA009_PAYMENTMETHOD_ID"] = Util.GetValueOfInt(ds.Tables[0].Rows[0]["VA009_PAYMENTMETHOD_ID"]); result["IsSOTrx"] = Util.GetValueOfString(ds.Tables[0].Rows[0]["IsSOTrx"]); result["DISCOUNT1"] = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["DISCOUNT1"]); result["DISCOUNT2"] = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["DISCOUNT2"]); } return(result); }
/// <summary> /// Perrform Process. /// </summary> /// <returns>Message </returns> protected override String DoIt() { int count = 0; log.Info("C_PaySelection_ID=" + _C_PaySelection_ID + ", OnlyDiscount=" + _OnlyDiscount + ", OnlyDue=" + _OnlyDue + ", IncludeInDispute=" + _IncludeInDispute + ", MatchRequirement=" + _MatchRequirementI + ", PaymentRule=" + _PaymentRule + ", C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID); MPaySelection psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); int C_CurrencyTo_ID = psel.GetC_Currency_ID(); if (psel.Get_ID() == 0) { throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); } if (psel.IsProcessed()) { throw new ArgumentException("@Processed@"); } // psel.getPayDate(); String sql = "SELECT C_Invoice_ID," // Open + " currencyConvert(invoiceOpen(i.C_Invoice_ID, 0)" + ",i.C_Currency_ID, " + C_CurrencyTo_ID + "," + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ", i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##1/2 Currency_To,PayDate // Discount + " currencyConvert(paymentTermDiscount(i.GrandTotal,i.C_Currency_ID,i.C_PaymentTerm_ID,i.DateInvoiced, " + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ")" // ##3 PayDate + ",i.C_Currency_ID," + C_CurrencyTo_ID + "," + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ",i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##4/5 Currency_To,PayDate + " PaymentRule, IsSOTrx " // 4..6 + "FROM C_Invoice i " + "WHERE"; if (_PaymentRule != null && _PaymentRule.Equals(X_C_Invoice.PAYMENTRULE_DirectDebit)) { sql += " IsSOTrx='Y'"; } else { sql += " IsSOTrx='N'"; } sql += " AND IsPaid='N' AND DocStatus IN ('CO','CL')" // ##6 + " AND AD_Client_ID=" + psel.GetAD_Client_ID() // ##7 // Existing Payments - Will reselect Invoice if prepared but not paid + " AND NOT EXISTS (SELECT * FROM C_PaySelectionLine psl " + "WHERE i.C_Invoice_ID=psl.C_Invoice_ID AND psl.IsActive='Y'" + " AND psl.C_PaySelectionCheck_ID IS NOT NULL)"; count = 7; // Disputed if (!_IncludeInDispute) { sql += " AND i.IsInDispute='N'"; } // PaymentRule (optional) if (_PaymentRule != null && _PaymentRule != " ") { sql += " AND PaymentRule=" + _PaymentRule; // ## count += 1; } // OnlyDiscount if (_OnlyDiscount) { if (_OnlyDue) { sql += " AND ("; } else { sql += " AND "; } sql += "paymentTermDiscount(invoiceOpen(C_Invoice_ID, 0), C_Currency_ID, C_PaymentTerm_ID, DateInvoiced, " + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ") > 0"; // ## count += 1; } // OnlyDue if (_OnlyDue) { if (_OnlyDiscount) { sql += " OR "; } else { sql += " AND "; } sql += "paymentTermDueDays(C_PaymentTerm_ID, DateInvoiced, " + DataBase.DB.TO_DATE(psel.GetPayDate(), true) + ") >= 0"; // ## count += 1; if (_OnlyDiscount) { sql += ")"; } } // Business Partner if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) { sql += " AND C_BPartner_ID=" + _C_BPartner_ID; // ## count += 1; } // Business Partner Group else if (_C_BP_Group_ID != 0 && _C_BP_Group_ID != -1) { sql += " AND EXISTS (SELECT * FROM C_BPartner bp " + "WHERE bp.C_BPartner_ID=i.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; // ## count += 1; } // PO Matching Requiremnent if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrder) || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) { sql += " AND i._MatchRequirementI NOT IN ('N','R')" + " AND EXISTS (SELECT * FROM C_InvoiceLine il " + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchPO m " + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; } // Receipt Matching Requiremnent if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_Receipt) || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) { sql += " AND i._MatchRequirementI NOT IN ('N','P')" + " AND EXISTS (SELECT * FROM C_InvoiceLine il " + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchInv m " + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; } // Document No else if (_DocumentNo_From != null && _DocumentNo_To != null) { sql += " AND i.DocumentNo BETWEEN " + DataBase.DB.TO_STRING(_DocumentNo_From) + " AND " + DataBase.DB.TO_STRING(_DocumentNo_To); } else if (_DocumentNo_From != null) { sql += " AND "; if (_DocumentNo_From.IndexOf('%') == -1) { sql += "i.DocumentNo >= " + DataBase.DB.TO_STRING(_DocumentNo_From); } else { sql += "i.DocumentNo LIKE " + DataBase.DB.TO_STRING(_DocumentNo_From); } } // int lines = 0; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { int C_Invoice_ID = Utility.Util.GetValueOfInt(idr[0]); // rs.getInt(1); Decimal PayAmt = Utility.Util.GetValueOfDecimal(idr[1]); //rs.getBigDecimal(2); if (C_Invoice_ID == 0 || Env.ZERO.CompareTo(PayAmt) == 0) { continue; } Decimal DiscountAmt = Utility.Util.GetValueOfDecimal(idr[2]); //rs.getBigDecimal(3); String PaymentRule = Utility.Util.GetValueOfString(idr[3]); //rs.getString(4); bool isSOTrx = "Y".Equals(Utility.Util.GetValueOfString(idr[4])); //rs.getString(5)); // lines++; MPaySelectionLine pselLine = new MPaySelectionLine(psel, lines * 10, PaymentRule); pselLine.SetInvoice(C_Invoice_ID, isSOTrx, PayAmt, Decimal.Subtract(PayAmt, DiscountAmt), DiscountAmt); if (!pselLine.Save()) { if (idr != null) { idr.Close(); idr = null; } return(GetRetrievedError(pselLine, "Cannot save MPaySelectionLine")); //throw new Exception("Cannot save MPaySelectionLine"); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return("@C_PaySelectionLine_ID@ - #" + lines); }
/// <summary> /// Generate PaySelection /// </summary> public string GeneratePaySelect(Ctx ctx, List <GridRecords> selectedRecords, Decimal?paymentAmt, String paymentRule, int C_BankAccount_ID, DateTime?payDate) { Trx trx = null; Trx p_trx = null; List <int> Invoice_ID = new List <int>(); List <Decimal?> openAmt = new List <Decimal?>(); List <Decimal?> payAmt = new List <Decimal?>(); int rowsSelected = 0; Decimal?totalAmt = 0; // BindingSource rowSource = vdgvPayment.ItemsSource as BindingSource; for (int i = 0; i < selectedRecords.Count; i++) { if ((Convert.ToBoolean(selectedRecords[i].SELECT))) { Decimal?amt = Util.GetValueOfDecimal(selectedRecords[i].PAYMENTAMOUNT); Invoice_ID.Add(Util.GetValueOfInt(selectedRecords[i].C_INVOICE_ID)); openAmt.Add(Util.GetValueOfDecimal(selectedRecords[i].AMOUNTDUE)); payAmt.Add(Util.GetValueOfDecimal(selectedRecords[i].PAYMENTAMOUNT)); rowsSelected++; } } if (rowsSelected == 0) { return(""); } //String paymentRule = Util.GetValueOfString(cmbPaymentRule.SelectedValue); //int C_BankAccount_ID = Util.GetValueOfInt(cmbBankAccount.SelectedValue); //// Create Header //DateTime? payDate = Util.GetValueOfDateTime(vdtpPayDate.SelectedDate); MPaySelection m_ps = new MPaySelection(ctx, 0, null); m_ps.SetName(Msg.GetMsg(ctx, "VPaySelect") + " - " + paymentRule + " - " + payDate.Value.Date); m_ps.SetPayDate(payDate); m_ps.SetC_BankAccount_ID(C_BankAccount_ID); m_ps.SetIsApproved(true); if (!m_ps.Save()) { //log.SaveError("SaveError", Msg.Translate(Envs.GetCtx(), "C_PaySelection_ID")); m_ps = null; return(""); } _C_PaySelection_ID = m_ps.GetC_PaySelection_ID(); string name = m_ps.GetName(); //string sqlTableID = "select ad_table_id from ad_table where tablename = 'C_PaySelection'"; //int AD_Table_ID = Util.GetValueOfInt(DB.ExecuteScalar(sqlTableID, null, null)); // log.Config(m_ps.ToString()); bool isSOTrx = false; if (X_C_Order.PAYMENTRULE_DirectDebit.Equals(paymentRule)) { isSOTrx = true; } int line = 0; Decimal?pAmt = Decimal.Zero; Decimal?oldpAmt = Decimal.Zero; for (int j = 0; j < Invoice_ID.Count; j++) { line = line + 10; if (Decimal.Add(pAmt.Value, Util.GetValueOfDecimal(payAmt[j])) > paymentAmt) { oldpAmt = Decimal.Subtract(paymentAmt.Value, pAmt.Value); } pAmt = Decimal.Add(pAmt.Value, Util.GetValueOfDecimal(payAmt[j])); // pAmt = Util.GetValueOfDecimal(payAmt[j]); MPaySelectionLine psl = new MPaySelectionLine(m_ps, line, paymentRule); //psl.SetInvoice(Util.GetValueOfInt(Invoice_ID[j]), isSOTrx, Util.GetValueOfDecimal(openAmt[j]), Util.GetValueOfDecimal(payAmt[j]), Decimal.Subtract(Util.GetValueOfDecimal(openAmt[j]), Util.GetValueOfDecimal(payAmt[j]))); if (paymentAmt >= pAmt) { psl.SetInvoice(Util.GetValueOfInt(Invoice_ID[j]), isSOTrx, Util.GetValueOfDecimal(openAmt[j]), Util.GetValueOfDecimal(payAmt[j]), Decimal.Zero); if (!psl.Save()) { // log.SaveError("PaymentSelectionLineNotSaved", "PaymentSelectionLineNotSaved"); return(""); } // log.Fine("C_Invoice_ID=" + Util.GetValueOfInt(Invoice_ID[j]) + ", PayAmt=" + Util.GetValueOfDecimal(payAmt[j])); } else { psl.SetInvoice(Util.GetValueOfInt(Invoice_ID[j]), isSOTrx, Util.GetValueOfDecimal(openAmt[j]), oldpAmt.Value, Decimal.Zero); if (!psl.Save()) { // log.SaveError("PaymentSelectionLineNotSaved", "PaymentSelectionLineNotSaved"); return(""); } // log.Fine("C_Invoice_ID=" + Util.GetValueOfInt(Invoice_ID[j]) + ", PayAmt=" + Util.GetValueOfDecimal(payAmt[j])); } } //if (false.Equals(((Message)sc).DialogResult)) //{ // Dispose(); // return; //} MPaySelection psel = new MPaySelection(ctx, _C_PaySelection_ID, null); if (psel.Get_ID() == 0) { throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); } if (psel.IsProcessed()) { throw new ArgumentException("@Processed@"); } // MPaySelectionLine[] lines = psel.GetLines(false); List <MPaySelectionCheck> _list = new List <MPaySelectionCheck>(); for (int i = 0; i < lines.Length; i++) { MPaySelectionLine payLine = lines[i]; if (!payLine.IsActive() || payLine.IsProcessed()) { continue; } CreateCheck(ctx, payLine, _list); } // psel.SetProcessed(true); psel.Save(); //string sql = "select ad_form_id from ad_form where classname = 'VAdvantage.Apps.AForms.VPayPrint'"; //int AD_Form_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); return("@C_PaySelectionCheck_ID@ - #" + _list.Count); //SetBusy(false); //Dispose(); //FormFrame ff = new FormFrame(); //ff.OpenForm(AD_Form_ID); } // generatePaySelect
/// <summary> /// Create Query /// </summary> /// <returns>String, Query</returns> public string CreateQuery() { int count = 0; log.Info("C_PaySelection_ID=" + _C_PaySelection_ID + ", OnlyDiscount=" + _OnlyDiscount + ", OnlyDue=" + _OnlyDue + ", IncludeInDispute=" + _IncludeInDispute + ", MatchRequirement=" + _MatchRequirementI + ", PaymentRule=" + _PaymentRule + ", C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID); psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); int C_CurrencyTo_ID = psel.GetC_Currency_ID(); if (psel.Get_ID() == 0) { throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); } if (psel.IsProcessed()) { throw new ArgumentException("@Processed@"); } StringBuilder sql = new StringBuilder("SELECT ips.C_INVOICEPAYSCHEDULE_ID, i.C_Invoice_ID," // Open Amount + " NVL(currencyConvert(ips.DUEAMT, ips.C_Currency_ID, " + C_CurrencyTo_ID + "," + DB.TO_DATE(psel.GetPayDate(), true) + ", i.C_ConversionType_ID, ips.AD_Client_ID, ips.AD_Org_ID),0) as OpenAmt," + " i.PaymentRule, i.IsSOTrx, CASE WHEN (" + DB.TO_DATE(psel.GetPayDate(), true) + " <= IPS.DISCOUNTDATE) THEN IPS.DISCOUNTAMT ELSE 0 END AS DISCOUNT1, CASE WHEN (" + DB.TO_DATE(psel.GetPayDate(), true) + " > IPS.DISCOUNTDATE AND " + DB.TO_DATE(psel.GetPayDate(), true) + " <= IPS.DISCOUNTDAYS2) THEN IPS.DISCOUNT2 ELSE 0 END AS DISCOUNT2 " + " FROM C_INVOICEPAYSCHEDULE ips INNER JOIN C_INVOICE i ON ips.C_INVOICE_ID=i.C_INVOICE_ID" + " WHERE ips.VA009_IsPaid='N' AND ips.IsHoldPayment='N' AND i.DocStatus IN ('CO','CL')" // ##6 + " AND ips.AD_Client_ID=" + psel.GetAD_Client_ID() // ##7 // Existing Payments - Will reselect Invoice if prepared but not paid + " AND IPS.C_INVOICEPAYSCHEDULE_ID NOT IN (SELECT C_INVOICEPAYSCHEDULE_ID FROM C_PAYSELECTIONLINE PSL WHERE PSL.C_INVOICEPAYSCHEDULE_ID = IPS.C_INVOICEPAYSCHEDULE_ID)" + " AND NOT EXISTS (SELECT * FROM C_PaySelectionLine psl WHERE ips.C_Invoice_ID=psl.C_Invoice_ID AND psl.IsActive='Y' AND psl.C_PaySelectionCheck_ID IS NOT NULL)"); // Disputed if (!_IncludeInDispute) { sql.Append(" AND i.IsInDispute='N'"); } // Payment Method if (VA009_PaymentMethod_ID > 0) { sql.Append(" AND ips.VA009_PaymentMethod_ID= " + VA009_PaymentMethod_ID); // ## count += 1; } // OnlyDue if (_OnlyDue) { sql.Append(" AND ips.DueDate <= " + DB.TO_DATE(psel.GetPayDate(), true)); count += 1; } if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) { sql.Append(" AND i.C_BPartner_ID=" + _C_BPartner_ID); // ## count += 1; } // Business Partner Group if (_C_BP_Group_ID != 0 && _C_BP_Group_ID != -1) { sql.Append(" AND EXISTS (SELECT * FROM C_BPartner bp " + "WHERE bp.C_BPartner_ID=i.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"); // ## count += 1; } // Document No if (_DocumentNo_From != null && _DocumentNo_To != null) { sql.Append(" AND i.DocumentNo BETWEEN " + DB.TO_STRING(_DocumentNo_From) + " AND " + DB.TO_STRING(_DocumentNo_To)); } else if (_DocumentNo_From != null) { sql.Append(" AND "); if (_DocumentNo_From.IndexOf('%') == -1) { sql.Append("i.DocumentNo >= " + DB.TO_STRING(_DocumentNo_From)); } else { sql.Append("i.DocumentNo LIKE " + DB.TO_STRING(_DocumentNo_From)); } } // Organization if (_AD_Org_ID != null && _AD_Org_ID != string.Empty) { int tableID = PO.Get_Table_ID("AD_Org"); sql.Append(@" AND ips.AD_Org_ID IN (SELECT AD_ORG.AD_ORG_ID FROM AD_TreeNode AD_TreeNode INNER JOIN AD_Tree AD_Tree ON AD_Tree.AD_Tree_ID =AD_TreeNode.AD_Tree_ID INNER JOIN AD_ORG AD_ORG ON AD_ORG.AD_ORG_ID = AD_TREENODE.NODE_ID WHERE AD_TreeNode.AD_Tree_ID IN (SELECT AD_Tree_ID FROM AD_Tree WHERE AD_Client_ID= " + GetCtx().GetAD_Client_ID() + @" AND AD_Table_ID=" + tableID + " AND IsActive ='Y' AND ISALLNODES='Y' AND ISDEFAULT='Y') AND (AD_ORG.AD_ORG_ID IN (" + _AD_Org_ID + ") OR AD_TREENODE.PARENT_ID IN (" + _AD_Org_ID + @")) AND AD_ORG.IsSummary ='N')"); } // Transaction Type if (VA009_Trx) { if (_isSOTrx) { sql.Append(" AND i.IsSOTrx='Y'"); } else { sql.Append(" AND i.IsSOTrx='N'"); } } return(sql.ToString()); }
/// <summary> /// Perform Process. /// </summary> /// <returns>Message </returns> protected override String DoIt() { //int count = 0; psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); #region Old Query //log.Info("C_PaySelection_ID=" + _C_PaySelection_ID // + ", OnlyDiscount=" + _OnlyDiscount + ", OnlyDue=" + _OnlyDue // + ", IncludeInDispute=" + _IncludeInDispute // + ", MatchRequirement=" + _MatchRequirementI // + ", PaymentRule=" + _PaymentRule // + ", C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID); //MPaySelection psel = new MPaySelection(GetCtx(), _C_PaySelection_ID, Get_TrxName()); //int C_CurrencyTo_ID = psel.GetC_Currency_ID(); //if (psel.Get_ID() == 0) //{ // throw new ArgumentException("Not found C_PaySelection_ID=" + _C_PaySelection_ID); //} //if (psel.IsProcessed()) //{ // throw new ArgumentException("@Processed@"); //} //// psel.getPayDate(); //String sql = "SELECT C_Invoice_ID," // // Open // + " currencyConvert(invoiceOpen(i.C_Invoice_ID, 0)" // + ",i.C_Currency_ID, " + C_CurrencyTo_ID + "," + DB.TO_DATE(psel.GetPayDate(), true) + ", i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##1/2 Currency_To,PayDate // // Discount // + " currencyConvert(paymentTermDiscount(i.GrandTotal,i.C_Currency_ID,i.C_PaymentTerm_ID,i.DateInvoiced, " + DB.TO_DATE(psel.GetPayDate(), true) + ")" // ##3 PayDate // + ",i.C_Currency_ID," + C_CurrencyTo_ID + "," + DB.TO_DATE(psel.GetPayDate(), true) + ",i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)," // ##4/5 Currency_To,PayDate // + " PaymentRule, IsSOTrx " // 4..6 // + "FROM C_Invoice i " // + "WHERE"; ////if (_PaymentRule != null && _PaymentRule.Equals(X_C_Invoice.PAYMENTRULE_DirectDebit)) ////{ //// sql += " IsSOTrx='Y'"; ////} ////else ////{ //// sql += " IsSOTrx='N'"; ////} //sql += " IsPaid='N' AND DocStatus IN ('CO','CL')" // ##6 // + " AND AD_Client_ID=" + psel.GetAD_Client_ID() // ##7 // // Existing Payments - Will reselect Invoice if prepared but not paid // + " AND NOT EXISTS (SELECT * FROM C_PaySelectionLine psl " // + "WHERE i.C_Invoice_ID=psl.C_Invoice_ID AND psl.IsActive='Y'" // + " AND psl.C_PaySelectionCheck_ID IS NOT NULL)"; //count = 7; //// Disputed //if (!_IncludeInDispute) //{ // sql += " AND i.IsInDispute='N'"; //} //// PaymentRule (optional) //if (_PaymentRule != null && _PaymentRule != " ") //{ // sql += " AND PaymentRule='" + _PaymentRule + "'"; // ## // count += 1; //} //// OnlyDiscount //if (_OnlyDiscount) //{ // if (_OnlyDue) // { // sql += " AND ("; // } // else // { // sql += " AND "; // } // sql += "paymentTermDiscount(invoiceOpen(C_Invoice_ID, 0), C_Currency_ID, C_PaymentTerm_ID, DateInvoiced, " + DB.TO_DATE(psel.GetPayDate(), true) + ") > 0"; // ## // count += 1; //} //// OnlyDue //if (_OnlyDue) //{ // if (_OnlyDiscount) // { // sql += " OR "; // } // else // { // sql += " AND "; // } // sql += "paymentTermDueDays(C_PaymentTerm_ID, DateInvoiced, " + DB.TO_DATE(psel.GetPayDate(), true) + ") >= 0"; // ## // count += 1; // if (_OnlyDiscount) // { // sql += ")"; // } //} //// Business Partner //if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) //{ // sql += " AND C_BPartner_ID=" + _C_BPartner_ID; // ## // count += 1; //} //// Business Partner Group //else if (_C_BP_Group_ID != 0 && _C_BP_Group_ID != -1) //{ // sql += " AND EXISTS (SELECT * FROM C_BPartner bp " // + "WHERE bp.C_BPartner_ID=i.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; // ## // count += 1; //} //// PO Matching Requiremnent //if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrder) // || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) //{ // sql += " AND i._MatchRequirementI NOT IN ('N','R')" // + " AND EXISTS (SELECT * FROM C_InvoiceLine il " // + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" // + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchPO m " // + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; //} //// Receipt Matching Requiremnent //if (_MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_Receipt) // || _MatchRequirementI.Equals(X_C_Invoice.MATCHREQUIREMENTI_PurchaseOrderAndReceipt)) //{ // sql += " AND i._MatchRequirementI NOT IN ('N','P')" // + " AND EXISTS (SELECT * FROM C_InvoiceLine il " // + "WHERE i.C_Invoice_ID=il.C_Invoice_ID" // + " AND QtyInvoiced IN (SELECT SUM(Qty) FROM M_MatchInv m " // + "WHERE il.C_InvoiceLine_ID=m.C_InvoiceLine_ID))"; //} //// Document No //else if (_DocumentNo_From != null && _DocumentNo_To != null) //{ // sql += " AND i.DocumentNo BETWEEN " // + DB.TO_STRING(_DocumentNo_From) + " AND " // + DB.TO_STRING(_DocumentNo_To); //} //else if (_DocumentNo_From != null) //{ // sql += " AND "; // if (_DocumentNo_From.IndexOf('%') == -1) // { // sql += "i.DocumentNo >= " // + DB.TO_STRING(_DocumentNo_From); // } // else // { // sql += "i.DocumentNo LIKE " // + DB.TO_STRING(_DocumentNo_From); // } //} #endregion String sql = CreateQuery(); // int lines = 0; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { int C_InvoicePaySchedule_ID = Util.GetValueOfInt(idr[0]); int C_Invoice_ID = Util.GetValueOfInt(idr[1]); // rs.getInt(1); Decimal PayAmt = Util.GetValueOfDecimal(idr[2]); //rs.getBigDecimal(2); if (C_Invoice_ID == 0 || Env.ZERO.CompareTo(PayAmt) == 0) { continue; } Decimal DiscountAmt = Util.GetValueOfDecimal(idr[5]); //rs.getBigDecimal(5); Decimal DiscountAmt2 = Util.GetValueOfDecimal(idr[6]); //rs.getBigDecimal(6); // OnlyDiscount if (_OnlyDiscount) { if (DiscountAmt == 0 && DiscountAmt2 == 0) { continue; //Skip this if discount is ZERO in the case of ONLY DISCOUNT } } if (DiscountAmt2 > 0) { DiscountAmt = DiscountAmt2; } String PaymentRule = Util.GetValueOfString(idr[3]); //rs.getString(3); bool isSOTrx = "Y".Equals(Util.GetValueOfString(idr[4])); //rs.getString(4)); // lines++; MPaySelectionLine pselLine = new MPaySelectionLine(psel, lines * 10, PaymentRule); pselLine.SetInvoice(C_Invoice_ID, isSOTrx, PayAmt, Decimal.Subtract(PayAmt, DiscountAmt), DiscountAmt); if (VA009_PaymentMethod_ID == 0) { MInvoicePaySchedule ips = new MInvoicePaySchedule(GetCtx(), C_InvoicePaySchedule_ID, Get_TrxName()); pselLine.Set_Value("VA009_PaymentMethod_ID", ips.GetVA009_PaymentMethod_ID()); _PaymentRule = (String)(DB.ExecuteScalar("SELECT VA009_PaymentBaseType FROM VA009_PaymentMethod WHERE IsActive='Y' AND VA009_PaymentMethod_ID=" + ips.GetVA009_PaymentMethod_ID())); pselLine.SetPaymentRule(_PaymentRule); } else { pselLine.Set_Value("VA009_PaymentMethod_ID", VA009_PaymentMethod_ID); pselLine.SetPaymentRule(_PaymentRule); } pselLine.SetC_InvoicePaySchedule_ID(C_InvoicePaySchedule_ID); // Set Invoice Pay Schedule if (!pselLine.Save()) { if (idr != null) { idr.Close(); idr = null; } //return GetReterivedError(pselLine, "Cannot save MPaySelectionLine"); throw new Exception("Cannot save MPaySelectionLine"); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return("@C_PaySelectionLine_ID@ - #" + lines); }