/// <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 Invoices to Run
/// </summary>
/// <returns>no of invoices</returns>
private int AddInvoices()
{
int size = 5;
int count = 0;
String sql = "SELECT i.C_Invoice_ID, i.C_Currency_ID,"
+ " i.GrandTotal*i.MultiplierAP,"
+ " invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID)*MultiplierAP,"
+ " COALESCE(daysBetween(@param1,ips.DueDate),paymentTermDueDays(i.C_PaymentTerm_ID,i.DateInvoiced,@param2))," // ##1/2
+ " i.IsInDispute, i.C_BPartner_ID "
+ "FROM C_Invoice_v i "
+ " LEFT OUTER JOIN C_InvoicePaySchedule ips ON (i.C_InvoicePaySchedule_ID=ips.C_InvoicePaySchedule_ID) "
+ "WHERE i.IsPaid='N' AND i.AD_Client_ID=@param3" // ##3
+ " AND i.DocStatus IN ('CO','CL')"
// Invoice Collection Status Collection Agency, Uncollectable, Legal will not been dunned any longer as per Def. YS + KP 12/02/06
+ " AND (NOT i.InvoiceCollectionType IN ('" + X_C_Invoice.INVOICECOLLECTIONTYPE_CollectionAgency + "', "
+ "'" + X_C_Invoice.INVOICECOLLECTIONTYPE_LegalProcedure + "', '" + X_C_Invoice.INVOICECOLLECTIONTYPE_Uncollectable + "')"
+ " OR InvoiceCollectionType IS NULL)"
// Do not show future docs...
+ " AND DateInvoiced<=@param4" // ##4
// Only BP(Group) with Dunning defined
+ " AND EXISTS (SELECT * FROM C_DunningLevel dl "
+ "WHERE dl.C_DunningLevel_ID=@param5" // // ##5
+ " 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))";
// for specific Business Partner
if (_C_BPartner_ID != 0)
{
size = size + 1;
sql += " AND i.C_BPartner_ID=@param6"; // ##6
}
// or a specific group
else if (_C_BP_Group_ID != 0)
{
size = size + 1;
sql += " AND EXISTS (SELECT * FROM C_BPartner bp "
+ "WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=@param6)"; // ##6
}
// Only Sales Trx
if (_OnlySOTrx)
{
sql += " AND i.IsSOTrx='Y'";
}
// Only single currency
if (!_IsAllCurrencies)
{
sql += " 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)
{
String sqlAppend = "";
for (int i = 0; i < previousLevels.Length; i++)
{
sqlAppend += " 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')";
}
sql += sqlAppend;
}
}
// 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"; // ##1 ##2
Decimal DaysAfterDue = _run.GetLevel().GetDaysAfterDue();
int DaysBetweenDunning = _run.GetLevel().GetDaysBetweenDunning();
IDataReader idr = null;
IDataReader idr1 = null;
try
{
SqlParameter[] param = new SqlParameter[size];
param[0] = new SqlParameter("@param1", _run.GetDunningDate());
param[1] = new SqlParameter("@param2", _run.GetDunningDate());
param[2] = new SqlParameter("@param3", _run.GetAD_Client_ID());
param[3] = new SqlParameter("@param4", _run.GetDunningDate());
param[4] = new SqlParameter("@param5", _run.GetC_DunningLevel_ID());
if (_C_BPartner_ID != 0)
{
//pstmt.setInt(6, _C_BPartner_ID);
param[5] = new SqlParameter("@param6", _C_BPartner_ID);
}
else if (_C_BP_Group_ID != 0)
{
//pstmt.setInt(6, _C_BP_Group_ID);
param[5] = new SqlParameter("@param6", _C_BP_Group_ID);
}
idr = DataBase.DB.ExecuteReader(sql, param, Get_TrxName());
//
while (idr.Read())
{
int C_Invoice_ID = Utility.Util.GetValueOfInt(idr[0]);
int C_Currency_ID = Utility.Util.GetValueOfInt(idr[1]);
Decimal GrandTotal = Utility.Util.GetValueOfDecimal(idr[2]);
Decimal Open = Utility.Util.GetValueOfDecimal(idr[3]);
int DaysDue = Utility.Util.GetValueOfInt(idr[4]);
bool IsInDispute = "Y".Equals(Utility.Util.GetValueOfString(idr[5]));
int C_BPartner_ID = Utility.Util.GetValueOfInt(idr[6]);
//
// Check for Dispute
if (!_IncludeInDispute && IsInDispute)
{
continue;
}
// Check the day again based on rulesets
if (DaysDue < Utility.Util.GetValueOfInt(DaysAfterDue) && !_level.IsShowAllDue())
{
continue;
}
// Check for an open amount
if (Env.ZERO.CompareTo(Open) == 0)
{
continue;
}
//
int timesDunned = 0;
int daysAfterLast = 0;
//2nd record set
SqlParameter[] param1 = new SqlParameter[2];
param1[0] = new SqlParameter("@C_DunningRun_ID", _run.Get_ID());
param1[1] = new SqlParameter("@C_Invoice_ID", C_Invoice_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 < DaysBetweenDunning && !_level.IsShowAllDue() && !_level.IsShowNotDue())
{
continue;
}
// We don't want to show non due documents
if (DaysDue < 0 && !_level.IsShowNotDue())
{
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;
}
//
CreateInvoiceLine(C_Invoice_ID, C_Currency_ID, GrandTotal, Open,
DaysDue, IsInDispute, C_BPartner_ID,
timesDunned, daysAfterLast);
count++;
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
if (idr1 != null)
{
idr1.Close();
}
log.Log(Level.SEVERE, "addInvoices", e);
}
return(count);
}