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