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
Run AR Aging - 7 Buckets - By Salesperson/Agent and other Oracle EBS reports with Blitz Report™ on our demo environment
select
 :p_company_name                     ledger,
 :p_reporting_level_name             reporting_level,
 :p_reporting_entity_name            reporting_entity,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') "&lp_bal_seg_p",
 fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_acc_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') "&lp_acc_seg_p",
 fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_accounting_flexfield', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account_segments,
 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 %",
 fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION') "&lp_bal_seg_p Desc",
 fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_acc_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') "&lp_acc_seg_p Desc",
 fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_accounting_flexfield', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') gl_account_segments_desc
from
 ( --start x1
  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.code_combination_id,
   x.chart_of_accounts_id,
   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
   ( --start x
    select
     substrb(party.party_name,1,50) cust_name,
     cust_acct.account_number cust_no,
     nvl(sales.name,jrrev.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,
     c.code_combination_id,
     c.chart_of_accounts_id,
     &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 jrrev,
     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 ps.customer_id = cust_acct.cust_account_id
    and cust_acct.party_id = party.party_id
    and ps.customer_trx_id = gld.customer_trx_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 :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 (   dh.dispute_history_id is null
         or dh.dispute_history_id =
              (select max(dh2.dispute_history_id)
               from   ar_dispute_history dh2
               where  dh2.payment_schedule_id = ps.payment_schedule_id
               and    :p_in_as_of_date_low  >= nvl(dh2.start_date(+), :p_in_as_of_date_low)
               and    :p_in_as_of_date_low  <  nvl(dh2.end_date(+), :p_in_as_of_date_low + 1)
              )
        )
    &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 nvl(ps.primary_salesrep_id,-3) = sales.salesrep_id
    and sales.org_id = ps.org_id
    and jrrev.resource_id = sales.resource_id
    and nvl(sales.name,jrrev.resource_name) between nvl(:p_in_salesrep_name_low,nvl(sales.name,jrrev.resource_name))
                                                and nvl(:p_in_salesrep_name_high,nvl(sales.name,jrrev.resource_name))
    and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y'
    &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,
     nvl(sales.name,jrrev.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.code_combination_id,
     app.chart_of_accounts_id,
     &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 jrrev,
     hz_cust_site_uses site,
     hz_cust_acct_sites acct_site,
     hz_party_sites party_site,
     hz_locations loc,
     (
      select
       c.code_combination_id,
       c.chart_of_accounts_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)) = ps.invoice_currency_code
      and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
      &lp_bal_seg_low
      &lp_bal_seg_high
      &lp_mo_ps_w
      &lp_mo_app_w
     ) app
    where
        ps.payment_schedule_id = app.payment_schedule_id
    and ps.customer_id = cust_acct.cust_account_id(+)
    and cust_acct.party_id = party.party_id(+)
    and 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
    &lp_customer_name_low
    &lp_customer_name_high
    &lp_customer_num_low
    &lp_customer_num_high
    &lp_where_show_bill
    and sales.salesrep_id = -3
    and sales.org_id  = ps.org_id
    and jrrev.resource_id = sales.resource_id
    and nvl(sales.name,jrrev.resource_name) between nvl(:p_in_salesrep_name_low,nvl(sales.name,jrrev.resource_name))
                                                and nvl(:p_in_salesrep_name_high,nvl(sales.name,jrrev.resource_name))
    &lp_mo_sales_w
    &lp_mo_addr_w
    group by
     party.party_name,
     cust_acct.account_number,
     site.site_use_id,
     nvl(sales.name,jrrev.resource_name),
     nvl(sales.salesrep_id,-3),
     loc.state,
     loc.city,
     acct_site.cust_acct_site_id,
     cust_acct.cust_account_id,
     ps.payment_schedule_id,
     ps.due_date,
     ps.trx_number,
     ps.amount_adjusted,
     ps.amount_applied,
     ps.amount_credited,
     ps.gl_date,
     ps.amount_in_dispute,
     ps.amount_adjusted_pending,
     ps.invoice_currency_code,
     ps.exchange_rate,
     app.class,
     app.code_combination_id,
     app.chart_of_accounts_id,
     decode( app.status, 'UNID', 'UNID','OTHER ACC','OTHER ACC','UNAPP'),
     &lp_query_show_bill ,
     initcap(:p_payment_meaning)
    union all
    select
     substrb(nvl(party.party_name, :p_short_unid_phrase),1,50) cust_name,
     cust_acct.account_number cust_no,
     nvl(sales.name,jrrev.resource_name),
     initcap(:p_risk_meaning),
     nvl(sales.salesrep_id,-3),
     site.site_use_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,
     initcap(:p_risk_meaning),
     ps.due_date,
     xxen_ar_arxagrw_pkg.comp_amt_due_remainingformula
      ( initcap(:p_risk_meaning),
        initcap(:p_risk_meaning),
        ps.payment_schedule_id,
        decode( nvl2(:p_in_currency,'N','Y'), 'Y', crh.acctd_amount, crh.amount), -- 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,
     crh.gl_date,
     decode(ps.invoice_currency_code, :p_functional_currency, NULL, decode(crh.exchange_rate, NULL, '*', NULL)),
     nvl(crh.exchange_rate, 1),
     arpt_sql_func_util.bucket_function
      ( xxen_ar_arxagrw_pkg.bucket_line_type(0),
        0,
        0,
        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),
        0,
        0,
        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),
        0,
        0,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),
        0,
        0,
        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),
        0,
        0,
        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),
        0,
        0,
        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),
        0,
        0,
        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,
     c.code_combination_id,
     c.chart_of_accounts_id,
     &lp_query_show_bill  cons_billing_number,
     initcap(:p_risk_meaning)
    from
     hz_cust_accounts cust_acct,
     hz_parties party,
     ar_payment_schedules ps,
     &lp_table_show_bill
     hz_cust_site_uses site,
     hz_cust_acct_sites acct_site,
     hz_party_sites party_site,
     hz_locations loc,
     ar_cash_receipts cr,
     ar_cash_receipt_history crh,
     gl_code_combinations c,
     ra_salesreps_all sales,
     jtf_rs_resource_extns_vl jrrev
    where
        crh.gl_date <= :p_in_as_of_date_low
    --and upper(RTRIM(RPAD(:p_in_summary_option_low,1)))  = 'I'
    and upper(:p_risk_option) != 'NONE'
    and ps.customer_id = cust_acct.cust_account_id(+)
    and cust_acct.party_id = party.party_id(+)
    and ps.cash_receipt_id = cr.cash_receipt_id
    and XXEN_AR_ARXAGRW_PKG.include_org_id(ps.org_id) = 'Y'
    and cr.cash_receipt_id = crh.cash_receipt_id
    and crh.account_code_combination_id = c.code_combination_id
    and 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 decode(upper(:p_in_currency), NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code
    and (crh.current_record_flag = 'Y' or crh.reversal_gl_date > :p_in_as_of_date_low )
    and crh.status not in (decode(crh.factor_flag,'Y','RISK_ELIMINATED','N','CLEARED'),'REVERSED')
    and not exists (select 'x'
                    from ar_receivable_applications ra
                    where ra.cash_receipt_id = cr.cash_receipt_id
                    and ra.status = 'ACTIVITY'
                    and applied_payment_schedule_id = -2)
    and cr.cash_receipt_id not in
                     (select ps.reversed_cash_receipt_id
                      from ar_payment_schedules ps
                      where ps.reversed_cash_receipt_id=cr.cash_receipt_id
                      and ps.class='DM'
                      and ps.gl_date<= (:p_in_as_of_date_low))
    &lp_customer_name_low
    &lp_customer_name_high
    &lp_customer_num_low
    &lp_customer_num_high
    &lp_bal_seg_low
    &lp_bal_seg_high
    &lp_where_show_bill
    and sales.salesrep_id = -3
    and sales.org_id = ps.org_id
    and jrrev.resource_id = sales.resource_id
    and nvl(sales.name,jrrev.resource_name) between nvl(:p_in_salesrep_name_low,nvl(sales.name,jrrev.resource_name))
                                                and nvl(:p_in_salesrep_name_high,nvl(sales.name,jrrev.resource_name))
    &lp_mo_sales_w
    &lp_mo_ps_w
    &lp_mo_crh_w
    &lp_mo_cr_w
    &lp_mo_addr_w
    union all
    select
     substrb(party.party_name,1,50) cust_name,
     cust_acct.account_number cust_no,
     nvl(sales.name,jrrev.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.sta
Parameter Name SQL text Validation
Reporting Level
 
LOV Oracle
Reporting Context
 
LOV Oracle
Report Summary
 
LOV Oracle
As Of Date
 
Date
Aging Bucket Name
 
LOV Oracle
Show On Account
 
LOV
Salesrep Name Low
 
LOV
Salesrep Name High
 
LOV
Customer Name Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Number low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Balance Due Low
 
Number
Balance Due High
 
Number
Invoice Type Low
 
LOV Oracle
Invoice Type High
 
LOV Oracle
Entered Currency
 
LOV Oracle
Company Segment Low
 
LOV
Company Segment High
 
LOV
Show Receipts At Risk
 
LOV Oracle