Exemple #1
0
 /// <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);
        }
Exemple #3
0
        /// <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);
        }