AR Aging - 7 Buckets - By Salesperson/Agent

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,<