AR Aging - 7 Buckets - By Salesperson/Agent

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
Application: Receivables
Source: Aging - 7 Buckets - By Salesperson/Agent Report
Short Name: ARXAGRW
Package: XXEN_AR_ARXAGRW_PKG
select
 :p_company_name                     ledger
,:p_reporting_level_name             reporting_level
,:p_reporting_entity_name            reporting_entity
,x1.bal_segment_value                "&lp_bal_seg_p"
,x1.sort_field1                      salesperson
,x1.cust_name                        customer
,x1.cust_no                          customer_Number
&lp_invoice_cols_s
,nvl(sum(x1.amt_due_remaining),0)    outstanding_amount 
&lp_on_account_summary_cols
,sum(decode(x1.b0,1,x1.amt_due_remaining,null)) "&lp_b0_p"
,sum(decode(x1.b1,1,x1.amt_due_remaining,null)) "&lp_b1_p"
,sum(decode(x1.b2,1,x1.amt_due_remaining,null)) "&lp_b2_p"
,sum(decode(x1.b3,1,x1.amt_due_remaining,null)) "&lp_b3_p"
,sum(decode(x1.b4,1,x1.amt_due_remaining,null)) "&lp_b4_p"
,sum(decode(x1.b5,1,x1.amt_due_remaining,null)) "&lp_b5_p"
,sum(decode(x1.b6,1,x1.amt_due_remaining,null)) "&lp_b6_p"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b0,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b0_p %"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b1,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b1_p %"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b2,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b2_p %"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b3,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b3_p %"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b4,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b4_p %"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b5,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b5_p %"
,case nvl(sum(x1.amt_due_remaining),0)
 when 0 then to_number(null) else round(sum(decode(x1.b6,1,x1.amt_due_remaining,null)) / sum(x1.amt_due_remaining) * 100,2)
 end "&lp_b6_p %"
from
  (
    select 
     x.sort_field1
    ,x.sort_field2
    ,x.cust_name
    ,x.cust_no
    ,x.class
    ,x.cons_billing_number
    ,x.invnum
    ,x.due_date 
    ,x.days_past_due
    ,x.amount_adjusted
    ,x.amount_applied
    ,x.amount_credited
    ,x.gl_date
    ,x.data_converted
    ,x.ps_exchange_rate
    ,x.bal_segment_value
    ,x.invoice_type
    ,x.b0
    ,x.b1
    ,x.b2
    ,x.b3
    ,x.b4
    ,x.b5
    ,x.b6
    ,case when (:p_credit_option = 'SUMMARY' and x.class in ('PMT','CM','CLAIM'))
            or (:p_risk_option = 'SUMMARY' AND x.invoice_type = :p_risk_meaning)
     then to_number(null)
     else x.amt_due_remaining
     end amt_due_remaining
    ,case when :p_credit_option = 'SUMMARY' AND x.class = 'PMT'
     then x.amt_due_remaining
     else null
     end  on_account_amount_cash
    ,case when :p_credit_option = 'SUMMARY' AND x.class = 'CM'
     then x.amt_due_remaining
     else null
     end  on_account_amount_credit
    ,case when :p_risk_option = 'SUMMARY' AND x.invoice_type = :p_risk_meaning
     then x.amt_due_remaining
     else null
     end  on_account_amount_risk
    ,case when :p_credit_option = 'SUMMARY' AND x.class = 'CLAIM'
     then x.amt_due_remaining
     else null
     end  cust_amount_claim
    from
      (
        select 
           substrb(party.party_name,1,50) cust_name, 
           cust_acct.account_number cust_no,
           extns.resource_name sort_field1,
           arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2,
           nvl(sales.salesrep_id, -3) inv_tid,
           site.site_use_id contact_site_id,
           loc.state cust_state,
           loc.city cust_city,
           decode(decode(upper(RTRIM(RPAD(:p_in_format_option_low, 1))),'D','D',NULL),NULL,-1,acct_site.cust_acct_site_id) addr_id,
           nvl(cust_acct.cust_account_id,-999) cust_id,
           ps.payment_schedule_id payment_sched_id,
           ps.class class,
           ps.due_date  due_date,
           XXEN_AR_ARXAGRW_PKG.Comp_Amt_Due_RemainingFormula
           (ps.class,
            arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id),
            ps.payment_schedule_id,
            ps.amt_due_remaining,
            ps.amount_applied,
            ps.amount_credited,
            ps.amount_adjusted
           )  amt_due_remaining, 
           ps.trx_number invnum,
           ceil(:p_in_as_of_date_low - ps.due_date) days_past_due,
           ps.amount_adjusted amount_adjusted,
           ps.amount_applied amount_applied,
           ps.amount_credited amount_credited,
           ps.gl_date gl_date,
           decode(ps.invoice_currency_code, :p_functional_currency, NULL,
                  decode(ps.exchange_rate, NULL, '*', NULL)) data_converted,
           nvl(ps.exchange_rate, 1) ps_exchange_rate,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(0),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(0),XXEN_AR_ARXAGRW_PKG.bucket_days_to(0),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b0,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(1),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(1),XXEN_AR_ARXAGRW_PKG.bucket_days_to(1),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b1,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(2),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(2),XXEN_AR_ARXAGRW_PKG.bucket_days_to(2),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b2,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(3),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(3),XXEN_AR_ARXAGRW_PKG.bucket_days_to(3),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b3,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(4),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(4),XXEN_AR_ARXAGRW_PKG.bucket_days_to(4),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b4,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(5),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(5),XXEN_AR_ARXAGRW_PKG.bucket_days_to(5),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b5,
           arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(6),
                        dh.amount_in_dispute,ps.amount_adjusted_pending,
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(6),XXEN_AR_ARXAGRW_PKG.bucket_days_to(6),
                        ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b6,
          fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', c.CHART_OF_ACCOUNTS_ID, NULL, c.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')  bal_segment_value,
          &lp_query_show_bill  cons_billing_number,
          arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type 
        from hz_cust_accounts cust_acct,
             hz_parties party, 
             (select 
                a.customer_id, 
                a.customer_site_use_id , 
                a.customer_trx_id, 
                a.payment_schedule_id, 
                a.class , 
                sum(a.primary_salesrep_id) primary_salesrep_id, 
                a.due_date , 
                sum(a.amount_due_remaining) amt_due_remaining, 
                a.trx_number, 
                a.amount_adjusted, 
                a.amount_applied , 
                a.amount_credited , 
                a.amount_adjusted_pending, 
                a.gl_date , 
                a.cust_trx_type_id, 
                a.org_id, 
                a.invoice_currency_code, 
                a.exchange_rate, 
                sum(a.cons_inv_id) cons_inv_id 
              from 
                (select 
                   ps.customer_id, 
                   ps.customer_site_use_id , 
                   ps.customer_trx_id, 
                   ps.payment_schedule_id, 
                   ps.class , 
                   0 primary_salesrep_id, 
                   ps.due_date , 
                   nvl(sum ( decode( nvl2(:p_in_currency,'N','Y'), 'Y', 
                              nvl(adj.acctd_amount, 0), 
                              adj.amount ) 
                           ),0) * (-1)  amount_due_remaining, 
                   ps.trx_number, 
                   ps.amount_adjusted , 
                   ps.amount_applied , 
                   ps.amount_credited , 
                   ps.amount_adjusted_pending, 
                   ps.gl_date , 
                   ps.cust_trx_type_id, 
                   ps.org_id, 
                   ps.invoice_currency_code, 
                   nvl(ps.exchange_rate,1) exchange_rate, 
                   0 cons_inv_id 
                from  ar_payment_schedules ps, 
                      ar_adjustments adj 
                      &lp_customer_num_f
                where  ps.gl_date <= :p_in_as_of_date_low 
                and ps.customer_id > 0 
                and  ps.gl_date_closed  > :p_in_as_of_date_low 
                and  decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, 
                            upper(:p_in_currency)) = ps.invoice_currency_code 
                and  adj.payment_schedule_id = ps.payment_schedule_id 
                and  adj.status = 'A' 
                and  adj.gl_date > :p_in_as_of_date_low
                &lp_customer_num_low
                &lp_customer_num_high
                &lp_customer_num_w
                &lp_mo_ps_w
                &lp_mo_adj_w
         group by 
           ps.customer_id, 
              ps.customer_site_use_id , 
              ps.customer_trx_id, 
              ps.class , 
              ps.due_date, 
              ps.trx_number, 
              ps.amount_adjusted , 
              ps.amount_applied , 
              ps.amount_credited , 
              ps.amount_adjusted_pending, 
              ps.gl_date , 
              ps.cust_trx_type_id, 
              ps.org_id, 
              ps.invoice_currency_code, 
              nvl(ps.exchange_rate,1), 
              ps.payment_schedule_id 
        UNION ALL 
           select  ps.customer_id, 
              ps.customer_site_use_id , 
              ps.customer_trx_id, 
              ps.payment_schedule_id, 
              ps.class , 
              0 primary_salesrep_id, 
              ps.due_date  , 
              nvl(sum ( decode 
                           ( nvl2(:p_in_currency,'N','Y'), 'Y', 
        		     (decode(ps.class, 'CM', 
        				decode ( app.application_type, 'CM', 
        					 app.acctd_amount_applied_from, 
                                                 app.acctd_amount_applied_to 
        					), 
        				app.acctd_amount_applied_to)+ 
                               nvl(app.acctd_earned_discount_taken,0) + 
                               nvl(app.acctd_unearned_discount_taken,0)) 
        		     , 
                             ( app.amount_applied + 
                               nvl(app.earned_discount_taken,0) + 
                               nvl(app.unearned_discount_taken,0) ) 
        		   ) * 
        		   decode 
                           ( ps.class, 'CM', 
                              decode(app.application_type, 'CM', -1, 1), 1 ) 
                        ), 0) amount_due_remaining,  
              ps.trx_number , 
              ps.amount_adjusted, 
              ps.amount_applied , 
              ps.amount_credited , 
              ps.amount_adjusted_pending, 
              ps.gl_date gl_date, 
              ps.cust_trx_type_id, 
              ps.org_id, 
              ps.invoice_currency_code, 
              nvl(ps.exchange_rate, 1) exchange_rate, 
              0 cons_inv_id 
           from  ar_payment_schedules ps, 
                 ar_receivable_applications app
                 &lp_customer_num_f        
           where  ps.gl_date <= :p_in_as_of_date_low 
            and   ps.customer_id > 0 
            and   ps.gl_date_closed  > :p_in_as_of_date_low 
            and   decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, 
                      upper(:p_in_currency)) = ps.invoice_currency_code 
            and  (app.applied_payment_schedule_id = ps.payment_schedule_id 
        			OR 
        	  app.payment_schedule_id = ps.payment_schedule_id) 
            and   app.status IN ('APP', 'ACTIVITY') 
            and   nvl( app.confirmed_flag, 'Y' ) = 'Y' 
            and   app.gl_date > :p_in_as_of_date_low
            &lp_customer_num_low
            &lp_customer_num_high
            &lp_customer_num_w 
            &lp_mo_ps_w
            &lp_mo_app_w
          group by 
          ps.customer_id, 
              ps.customer_site_use_id , 
              ps.customer_trx_id, 
              ps.class , 
              ps.due_date, 
              ps.trx_number, 
              ps.amount_adjusted , 
              ps.amount_applied , 
              ps.amount_credited , 
              ps.amount_adjusted_pending, 
              ps.gl_date , 
              ps.cust_trx_type_id, 
              ps.org_id, 
              ps.invoice_currency_code, 
              nvl(ps.exchange_rate, 1), 
              ps.payment_schedule_id 
        UNION ALL 
           select  ps.customer_id, 
              ps.customer_site_use_id , 
              ps.customer_trx_id, 
              ps.payment_schedule_id, 
              ps.class class, 
              nvl(ct.primary_salesrep_id, -3) primary_salesrep_id, 
              ps.due_date  due_date, 
              decode( nvl2(:p_in_currency,'N','Y'), 'Y', 
                      ps.acctd_amount_due_remaining, 
                      ps.amount_due_remaining) amt_due_remaining, 
              ps.trx_number, 
              ps.amount_adjusted , 
              ps.amount_applied , 
              ps.amount_credited , 
              ps.amount_adjusted_pending, 
              ps.gl_date , 
              ps.cust_trx_type_id, 
              ps.org_id, 
              ps.invoice_currency_code, 
              nvl(ps.exchange_rate, 1) exchange_rate, 
              ps.cons_inv_id 
           from  ar_payment_schedules ps, 
                 ra_customer_trx ct
                 &lp_customer_num_f        
           where  ps.gl_date <= :p_in_as_of_date_low 
            and   ps.gl_date_closed  > :p_in_as_of_date_low 
            and   decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, 
                      upper(:p_in_currency)) = ps.invoice_currency_code 
            and  ps.customer_trx_id = ct.customer_trx_id
            and ps.class <> 'CB'
            &lp_customer_num_low
            &lp_customer_num_high
            &lp_customer_num_w 
            &lp_mo_ps_w 
            &lp_mo_ct_w 
           UNION ALL 
           select  ps.customer_id, 
              ps.customer_site_use_id , 
              ps.customer_trx_id, 
              ps.payment_schedule_id, 
              ps.class class, 
              ct.primary_salesrep_id primary_salesrep_id, 
              ps.due_date  due_date, 
              decode( nvl2(:p_in_currency,'N','Y'), 'Y', 
                      ps.acctd_amount_due_remaining, 
                      ps.amount_due_remaining) amt_due_remaining, 
              ps.trx_number, 
              ps.amount_adjusted , 
              ps.amount_applied , 
              ps.amount_credited , 
              ps.amount_adjusted_pending, 
              ps.gl_date , 
              ps.cust_trx_type_id, 
              ps.org_id, 
              ps.invoice_currency_code, 
              nvl(ps.exchange_rate, 1) exchange_rate, 
              ps.cons_inv_id 
           from  ar_payment_schedules ps, 
                 ra_customer_trx ct, 
                 ar_adjustments adj
                 &lp_customer_num_f        
           where  ps.gl_date <= :p_in_as_of_date_low 
            and   ps.gl_date_closed  > :p_in_as_of_date_low 
            and   decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, 
                      upper(:p_in_currency)) = ps.invoice_currency_code 
            and  ps.class = 'CB' 
            and  ps.customer_trx_id = adj.chargeback_customer_trx_id 
            and  adj.customer_trx_id = ct.customer_trx_id
            &lp_customer_num_low
            &lp_customer_num_high
            &lp_customer_num_w
            &lp_mo_ps_w
            &lp_mo_ct_w
            &lp_mo_adj_w
          ) a 
        group by a.customer_id, 
              a.customer_site_use_id , 
              a.customer_trx_id, 
              a.payment_schedule_id, 
              a.class , 
              a.due_date , 
              a.trx_number, 
              a.amount_adjusted, 
              a.amount_applied , 
              a.amount_credited , 
              a.amount_adjusted_pending, 
              a.gl_date , 
              a.cust_trx_type_id, 
              a.org_id, 
              a.invoice_currency_code, 
              a.exchange_rate
              ) ps,
              &lp_table_show_bill
              ra_salesreps_all sales , 
              jtf_rs_resource_extns_vl extns,
              hz_cust_site_uses site, 
              hz_cust_acct_sites acct_site, 
              hz_party_sites party_site, 
              hz_locations loc, 
              ra_cust_trx_line_gl_dist gld,
              ar_dispute_history dh, 
              gl_code_combinations c
           where 
                  --upper(RTRIM(RPAD(:p_in_summary_option_low,1)) ) = 'I' 
                  ps.customer_site_use_id = site.site_use_id 
            and   site.cust_acct_site_id = acct_site.cust_acct_site_id 
            and   acct_site.party_site_id = party_site.party_site_id 
            and   loc.location_id = party_site.location_id 
                            and   gld.account_class = 'REC' 
            and   gld.latest_rec_flag = 'Y'
            and gld.code_combination_id = c.code_combination_id
            and   ps.payment_schedule_id  =  dh. payment_schedule_id(+) 
            and   XXEN_AR_ARXAGRW_PKG.include_org_id(ps.org_id) = 'Y' 
            and  :p_in_as_of_date_low  >= nvl(dh.start_date(+), :p_in_as_of_date_low) 
            and  :p_in_as_of_date_low  <  nvl(dh.end_date(+), :p_in_as_of_date_low + 1) 
            and   cust_acct.party_id = party.party_id
        &lp_customer_name_low
        &lp_customer_name_high
        &lp_customer_num_low
        &lp_customer_num_high
        &lp_invoice_type_low
        &lp_invoice_type_high
        &lp_bal_seg_low
        &lp_bal_seg_high
        &lp_where_show_bill
        and ps.customer_id = cust_acct.cust_account_id 
                and ps.customer_trx_id = gld.customer_trx_id
               and nvl(ps.primary_salesrep_id,-3) = sales.salesrep_id
        and extns.resource_name between nvl(:p_in_salesrep_name_low,extns.resource_name)
                                    and nvl(:p_in_salesrep_name_high,extns.resource_name)
        and sales.resource_id = extns.resource_id
        and nvl(sales.org_id, ps.org_id) = ps.org_id 
        &lp_mo_gld_w
        &lp_mo_addr_w
        &lp_mo_sales_w
        UNION ALL
        select 
                   substrb(nvl(party.party_name,:p_short_unid_phrase),1,50) cust_name, 
                   cust_acct.account_number cust_no,
                   extns.resource_name,
                   initcap(:p_payment_meaning),
                   nvl(sales.salesrep_id,-3),          
                   site.site_use_id, 
                   loc.state cust_state, 
                   loc.city cust_state, 
                   decode(decode(upper(RTRIM(RPAD(:p_in_format_option_low, 1))),'D','D',NULL),NULL,-1,acct_site.cust_acct_site_id) addr_id, 
                   nvl(cust_acct.cust_account_id, -999) cust_id, 
                   ps.payment_schedule_id, 
                   app.class, 
                   ps.due_date, 
                   XXEN_AR_ARXAGRW_PKG.Comp_Amt_Due_RemainingFormula
                   (app.class,
                    initcap(:p_payment_meaning),
                    ps.payment_schedule_id,
                    decode(nvl2(:p_in_currency,'N','Y'), 'Y', nvl(-SUM(app.acctd_amount), 0), nvl(-SUM(app.amount), 0)), -- amount due remaining
                    ps.amount_applied,
                    ps.amount_credited,
                    ps.amount_adjusted
                   )  amt_due_remaining,  
                   ps.trx_number, 
                   ceil(:p_in_as_of_date_low - ps.due_date), 
                   ps.amount_adjusted, 
                   ps.amount_applied, 
                   ps.amount_credited, 
                   ps.gl_date, 
                   decode(ps.invoice_currency_code, :p_functional_currency, NULL, 
                        decode(ps.exchange_rate, NULL, '*', NULL)), 
                   nvl(ps.exchange_rate, 1),
             arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(0), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(0),XXEN_AR_ARXAGRW_PKG.bucket_days_to(0), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b0, 
              arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(1), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(1),XXEN_AR_ARXAGRW_PKG.bucket_days_to(1), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b1, 
              arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(2), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(2),XXEN_AR_ARXAGRW_PKG.bucket_days_to(2), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b2, 
              arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(3), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(3),XXEN_AR_ARXAGRW_PKG.bucket_days_to(3), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b3, 
              arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(4), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(4),XXEN_AR_ARXAGRW_PKG.bucket_days_to(4), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b4, 
              arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(5), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(5),XXEN_AR_ARXAGRW_PKG.bucket_days_to(5), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b5, 
              arpt_sql_func_util.bucket_function(XXEN_AR_ARXAGRW_PKG.bucket_line_type(6), 
                        ps.amount_in_dispute,ps.amount_adjusted_pending, 
                        XXEN_AR_ARXAGRW_PKG.bucket_days_from(6),XXEN_AR_ARXAGRW_PKG.bucket_days_to(6), 
                         ps.due_date,XXEN_AR_ARXAGRW_PKG.bucket_category,:p_in_as_of_date_low) b6,
              app.segment1 bal_segment_value,
              &lp_query_show_bill cons_billing_number,
             initcap(:p_payment_meaning)
                from  hz_cust_accounts cust_acct, 
                       hz_parties party, 
                       ar_payment_schedules ps,
        &lp_table_show_bill
                       ra_salesreps_all sales , 
                       jtf_rs_resource_extns_vl extns,
                       hz_cust_site_uses site, 
                       hz_cust_acct_sites acct_site, 
                       hz_party_sites party_site, 
                       hz_locations loc,
                 (Select
        	   fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', c.CHART_OF_ACCOUNTS_ID, NULL, c.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')  segment1,
        	   fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_accounting_flexfield', 'SQLGL', 'GL#', c.CHART_OF_ACCOUNTS_ID, NULL, c.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') segments,
        	   c.code_combination_id code_combination_id, 
                   ps.payment_schedule_id payment_schedule_id, 
                   decode(app.applied_payment_schedule_id,   -4,   'CLAIM',   ps.class) class, 
                   app.acctd_amount_applied_from acctd_amount, 
                   app.amount_applied amount, 
                   app.status status 
                   FROM ar_receivable_applications app, 
                   gl_code_combinations c, 
                   ar_payment_schedules ps 
                where    app.gl_date <= :p_in_as_of_date_low 
                   --and upper(RTRIM(RPAD(:p_in_summary_option_low,1)))  = 'I' 
                   and    XXEN_AR_ARXAGRW_PKG.include_org_id(ps.org_id) = 'Y' 
                   and    ps.cash_receipt_id = app.cash_receipt_id 
                   and    app.code_combination_id = c.code_combination_id 
                   and    app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC') 
                   and    nvl(app.confirmed_flag, 'Y') = 'Y'
                   and    ps.gl_date_closed  > :p_in_as_of_date_low 
                   and    ((app.reversal_gl_date is not null AND 
                            ps.gl_date <= :p_in_as_of_date_low) 
                           OR 
                           app.reversal_gl_date is null ) 
                   and    decode(upper(:p_in_currency), NULL, ps.invoice_currency_code, 
                          upper(:p_in_currency