/// <summary> /// Dunning Level Consequences /// </summary> /// <param name="level"></param> /// <param name="entry"></param> private void DunningLevelConsequences(MDunningLevel level, MDunningRunEntry entry) { // Update Business Partner based on Level if (level.IsSetCreditStop() || level.IsSetPaymentTerm()) { MBPartner thisBPartner = new MBPartner(GetCtx(), entry.GetC_BPartner_ID(), Get_TrxName()); if (level.IsSetCreditStop()) { thisBPartner.SetSOCreditStatus(X_C_BPartner.SOCREDITSTATUS_CreditStop); } if (level.IsSetPaymentTerm() && level.GetC_PaymentTerm_ID() != 0) { thisBPartner.SetC_PaymentTerm_ID(level.GetC_PaymentTerm_ID()); } thisBPartner.Save(); } // Update Invoices if not Statement (Statement is hardcoded -9999 see also MDunningLevel) if (!level.GetDaysAfterDue().Equals(new Decimal(-9999)) && level.GetInvoiceCollectionType() != null) { MDunningRunLine[] lines = entry.GetLines(); for (int i = 0; i < lines.Length; i++) { MDunningRunLine line = lines[i]; if (line.GetC_Invoice_ID() != 0 && line.IsActive()) { MInvoice invoice = new MInvoice(GetCtx(), line.GetC_Invoice_ID(), Get_TrxName()); invoice.SetInvoiceCollectionType(level.GetInvoiceCollectionType()); invoice.Save(); } } } }
/// <summary> /// Add Invoices to Run tab of dunning run window. /// </summary> /// <returns>No of invoices that are created.</returns> private int AddInvoices() { //int size = 3; int count = 0; SqlParameter[] param = null; // Changes for Invoice schedule if valid true if (!_level.IsShowNotDue()) { param = new SqlParameter[3]; } else { param = new SqlParameter[1]; } sql.Append("SELECT I.C_INVOICE_ID, I.C_CURRENCY_ID, I.GRANDTOTAL, INVOICEOPEN(I.C_INVOICE_ID,Ips.C_INVOICEPAYSCHEDULE_ID), " + " DAYSBETWEEN(@param1,IPS.DUEDATE)," + " i.IsInDispute, i.C_BPartner_ID, " + " CASE " + " WHEN i.issotrx ='Y' AND i.isreturntrx='N'" // Invoice Customer + " THEN ips.DueAmt" + " WHEN i.issotrx ='Y' AND i.isreturntrx='Y'" // AR Credit Memo + " THEN ips.DueAmt * -1" + " WHEN i.issotrx ='N' AND i.isreturntrx='N'" // Invoice Vendor + " THEN ips.DueAmt * -1" + " ELSE ips.DueAmt" // AP Credit Memo + " END AS DueAmt," + " ips.C_INVOICEPAYSCHEDULE_ID" + " FROM C_Invoice i " + " LEFT JOIN C_INVOICEPAYSCHEDULE ips " + " ON ips.c_invoice_id =i.c_invoice_id " + " WHERE ips.VA009_IsPaid ='N' " + " AND i.AD_Client_ID = " + _run.GetAD_Client_ID() + " AND i.AD_Org_ID = " + _run.GetAD_Org_ID() + " AND i.DocStatus IN ('CO','CL') " + " AND (NOT i.InvoiceCollectionType IN ('" + X_C_Invoice.INVOICECOLLECTIONTYPE_CollectionAgency + "', " + "'" + X_C_Invoice.INVOICECOLLECTIONTYPE_LegalProcedure + "', '" + X_C_Invoice.INVOICECOLLECTIONTYPE_Uncollectable + "')" + " OR InvoiceCollectionType IS NULL) "); if (_SalesRep_ID > 0) { sql.Append(" AND i.SalesRep_ID= " + _SalesRep_ID); } if (!_level.IsShowNotDue()) { sql.Append(" AND IPS.DUEDATE <=@param2 " + " AND DATEINVOICED <=@param3 "); } sql.Append(" AND IPS.ISVALID='Y' " + " AND i.ispayschedulevalid='Y' " + " AND EXISTS " + " (SELECT * " + " FROM C_DunningLevel dl " + " WHERE dl.C_DunningLevel_ID= " + _run.GetC_DunningLevel_ID() + " AND dl.C_Dunning_ID IN " + " (SELECT COALESCE(bp.C_Dunning_ID, bpg.C_Dunning_ID) " + " FROM C_BPartner bp " + " INNER JOIN C_BP_Group bpg " + " ON (bp.C_BP_Group_ID =bpg.C_BP_Group_ID) " + " WHERE i.C_BPartner_ID=bp.C_BPartner_ID " + " ) " + " )"); if (!_level.IsShowAllDue()) { param[0] = new SqlParameter("@param1", _run.GetDunningDate()); if (!_level.IsShowNotDue()) { param[1] = new SqlParameter("@param2", _run.GetDunningDate().Value.AddDays(Util.GetValueOfInt(-1 * _level.GetDaysAfterDue()))); param[2] = new SqlParameter("@param3", _run.GetDunningDate().Value.AddDays(Util.GetValueOfInt(-1 * _level.GetDaysAfterDue()))); } } else { param[0] = new SqlParameter("@param1", _run.GetDunningDate()); if (!_level.IsShowNotDue()) { param[1] = new SqlParameter("@param2", _run.GetDunningDate()); param[2] = new SqlParameter("@param3", _run.GetDunningDate()); } } // for specific Business Partner if (_C_BPartner_ID != 0) { //size = size + 1; sql.Append(" AND i.C_BPartner_ID=" + _C_BPartner_ID); // ##6 } // or a specific group if (_C_BP_Group_ID != 0) { //size = size + 1; sql.Append(" AND EXISTS (SELECT * FROM C_BPartner bp " + "WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"); // ##; } // Only Sales Trx if (_OnlySOTrx) { sql.Append(" AND i.IsSOTrx='Y'"); } // Only single currency if (!_IsAllCurrencies) { sql.Append(" AND i.C_Currency_ID=" + _C_Currency_ID); } // log.info(sql); String sql2 = null; // if sequentially we must check for other levels with smaller days for // which this invoice is not yet included! if (_level.GetParent().IsCreateLevelsSequentially()) { // Build a list of all topmost Dunning Levels MDunningLevel[] previousLevels = _level.GetPreviousLevels(); if (previousLevels != null && previousLevels.Length > 0) { for (int i = 0; i < previousLevels.Length; i++) { sql.Append(" AND i.C_Invoice_ID IN (SELECT C_Invoice_ID FROM C_DunningRunLine WHERE " + "C_DunningRunEntry_ID IN (SELECT C_DunningRunEntry_ID FROM C_DunningRunEntry WHERE " + "C_DunningRun_ID IN (SELECT C_DunningRun_ID FROM C_DunningRun WHERE " + "C_DunningLevel_ID=" + previousLevels[i].Get_ID() + ")) AND Processed<>'N')"); } } } // ensure that we do only dunn what's not yet dunned, so we lookup the max of last Dunn Date which was processed sql2 = "SELECT COUNT(*), COALESCE(DAYSBETWEEN(MAX(dr2.DunningDate), MAX(dr.DunningDate)),0)" + "FROM C_DunningRun dr2, C_DunningRun dr" + " INNER JOIN C_DunningRunEntry dre ON (dr.C_DunningRun_ID=dre.C_DunningRun_ID)" + " INNER JOIN C_DunningRunLine drl ON (dre.C_DunningRunEntry_ID=drl.C_DunningRunEntry_ID) " + "WHERE drl.Processed='Y' AND dr2.C_DunningRun_ID=@C_DunningRun_ID AND drl.C_Invoice_ID=@C_Invoice_ID AND C_InvoicePaySchedule_ID=@C_InvoicePaySchedule_ID AND dr.C_DunningRun_ID!=@C_DunningRun_ID"; // ##1 ##2 Decimal DaysAfterDue = _run.GetLevel().GetDaysAfterDue(); int DaysBetweenDunning = _run.GetLevel().GetDaysBetweenDunning(); IDataReader idr = null; IDataReader idr1 = null; try { idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName()); sql.Clear(); // while (idr.Read()) { C_Invoice_ID = 0; C_Currency_ID = 0; GrandTotal = 0; Open = 0; DaysDue = 0; IsInDispute = false; C_BPartner_ID = 0; PaySchedule_ID = 0; C_Invoice_ID = Utility.Util.GetValueOfInt(idr[0]); C_Currency_ID = Utility.Util.GetValueOfInt(idr[1]); GrandTotal = Utility.Util.GetValueOfDecimal(idr[2]); Open = Utility.Util.GetValueOfDecimal(idr[7]); DaysDue = Utility.Util.GetValueOfInt(idr[4]); IsInDispute = "Y".Equals(Utility.Util.GetValueOfString(idr[5])); C_BPartner_ID = Utility.Util.GetValueOfInt(idr[6]); PaySchedule_ID = Utility.Util.GetValueOfInt(idr[8]); // // Check for Dispute if (!_IncludeInDispute && IsInDispute) { continue; } // Check for an open amount if (Env.ZERO.CompareTo(Open) == 0) { continue; } // timesDunned = 0; daysAfterLast = 0; //2nd record set SqlParameter[] param1 = new SqlParameter[3]; param1[0] = new SqlParameter("@C_DunningRun_ID", _run.Get_ID()); param1[1] = new SqlParameter("@C_Invoice_ID", C_Invoice_ID); param1[2] = new SqlParameter("@C_InvoicePaySchedule_ID", PaySchedule_ID); idr1 = DataBase.DB.ExecuteReader(sql2, param1, Get_TrxName()); // SubQuery if (idr1.Read()) { timesDunned = Utility.Util.GetValueOfInt(idr1[0]); daysAfterLast = Utility.Util.GetValueOfInt(idr1[1]); } idr1.Close(); // SubQuery // Ensure that Daysbetween Dunning is enforced // Ensure Not ShowAllDue and Not ShowNotDue is selected // PROBLEM: If you have ShowAll activated then DaysBetweenDunning is not working, because we don't know whether // there is something which we really must Dunn. //if (DaysBetweenDunning != 0 && daysAfterLast != 0 && daysAfterLast < DaysBetweenDunning && !_level.IsShowAllDue() && !_level.IsShowNotDue()) if (DaysBetweenDunning != 0 && daysAfterLast != 0 && daysAfterLast < DaysBetweenDunning) { continue; } // We will minus the timesDunned if this is the DaysBetweenDunning is not fullfilled. // Remember in checkup later we must reset them! // See also checkDunningEntry() if (daysAfterLast < DaysBetweenDunning) { timesDunned = timesDunned * -1; } // if (CreateInvoiceLine(C_Invoice_ID, C_Currency_ID, GrandTotal, Open, DaysDue, IsInDispute, C_BPartner_ID, timesDunned, daysAfterLast, PaySchedule_ID)) { count++; } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } if (idr1 != null) { idr1.Close(); } log.Log(Level.SEVERE, "addInvoices", e); } return(count); }
/// <summary> /// Add Payments to Run /// </summary> /// <returns>no of payments</returns> private int AddPayments() { String sql = "SELECT C_Payment_ID, C_Currency_ID, PayAmt," + " paymentAvailable(C_Payment_ID), C_BPartner_ID " + "FROM C_Payment_v p " + "WHERE AD_Client_ID=" + GetAD_Client_ID() // ##1 + " AND IsAllocated='N' AND C_BPartner_ID IS NOT NULL" + " AND C_Charge_ID IS NULL" + " AND DocStatus IN ('CO','CL')" // Only BP with Dunning defined + " AND EXISTS (SELECT * FROM C_BPartner bp " + "WHERE p.C_BPartner_ID=bp.C_BPartner_ID" + " AND bp.C_Dunning_ID=(SELECT C_Dunning_ID FROM C_DunningLevel WHERE C_DunningLevel_ID=" + _run.GetC_DunningLevel_ID() + "))"; // ##2 if (_C_BPartner_ID != 0) { sql += " AND C_BPartner_ID=" + _C_BPartner_ID; // ##3 } else if (_C_BP_Group_ID != 0) { sql += " AND EXISTS (SELECT * FROM C_BPartner bp " + "WHERE p.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"; // ##3 } // If it is not a statement we will add lines only if InvoiceLines exists, // because we do not want to dunn for money we owe the customer! if (!_level.GetDaysAfterDue().Equals(new Decimal(-9999))) { sql += " AND C_BPartner_ID IN (SELECT C_BPartner_ID FROM C_DunningRunEntry WHERE C_DunningRun_ID=" + _run.Get_ID() + ")"; } // show only receipts / if only Sales if (_OnlySOTrx) { sql += " AND IsReceipt='Y'"; } int count = 0; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, Get_TrxName()); while (idr.Read()) { int C_Payment_ID = Utility.Util.GetValueOfInt(idr[0]); int C_Currency_ID = Utility.Util.GetValueOfInt(idr[1]); Decimal PayAmt = Decimal.Negate(Utility.Util.GetValueOfDecimal(idr[2])); //.getBigDecimal(3).negate(); Decimal openAmt = Decimal.Negate(Utility.Util.GetValueOfDecimal(idr[3])); // rs.getBigDecimal(4).negate(); int C_BPartner_ID = Utility.Util.GetValueOfInt(idr[4]); //.getInt(5); // checkup the amount if (Env.ZERO.CompareTo(openAmt) == 0) { continue; } // CreatePaymentLine(C_Payment_ID, C_Currency_ID, PayAmt, openAmt, C_BPartner_ID); count++; } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return(count); }