AR Aging and Current Open AR

select
 x1.ledger                           ledger,
 x1.operating_unit                   operating_unit,
 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.cust_name                        customer,
 x1.cust_no                          customer_number,
 x1.cust_type                        customer_type,
 x1.cust_country                     customer_country,
 &lp_invoice_cols_s
 x1.invoice_currency_code            trx_currency,
 x1.revaluation_from_currency        amounts_currency,
 nvl(sum(x1.amt_due_original),0)     original_amount,
 nvl(sum(x1.amt_due_remaining),0)    outstanding_amount,
 nvl(sum(case when x1.days_past_due > 0 then x1.amt_due_remaining end),0) overdue_amount,
&lp_on_acc_summ_cols
 min(case when x1.gl_date <= :p_in_as_of_date_low then 'Aged Trx' else 'Future Trx' end) trx_aging_status,
 min(case when x1.gl_date <= :p_in_as_of_date_low then :p_age_basis else null end) Aging_Basis,
&lp_aging_amount_cols
&lp_aging_pct_cols
-- Current Open AR
 nvl(sum(x1.acctd_amount_due_remaining),0) current_open_ar_actd_curr,
 nvl(sum(x1.amount_due_remaining),0) current_open_ar_trx_curr,
&lp_current_open_ar_cols
 max(case when x1.status = 'OP' then 'Open' else 'Closed' end) current_ar_status,
-- Revaluation Columns
&lp_reval_columns
 --
 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.ledger,
   (select haou.name from hr_all_organization_units haou where haou.organization_id = x.org_id) operating_unit,
   x.salesperson,
   x.cust_name,
   x.cust_no,
   x.cust_type,
   x.cust_country,
   x.class,
   x.cons_billing_number,
   x.invnum,
   x.invoice_currency_code,
   x.trx_date,
   x.due_date,
   x.days_past_trx,
   x.days_past_due,
   x.current_days_past_trx,
   x.current_days_past_due,
   x.amt_due_original,
   x.amount_adjusted,
   x.amount_applied,
   x.amount_credited,
   x.amount_due_remaining, --xxecl
   x.acctd_amount_due_remaining, --xxecl
   x.gl_date,
   x.data_converted,
   x.ps_exchange_rate,
   x.code_combination_id,
   x.chart_of_accounts_id,
   x.invoice_type,
   x.invoice_terms,
   x.status,
   --
&lp_bucket_cols1
   --
   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,
   nvl(:p_in_currency,x.functional_currency) revaluation_from_currency,
   decode(nvl(:p_in_currency,x.functional_currency),:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where nvl(:p_in_currency,x.functional_currency)=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate,
   xxen_util.meaning(:p_reporting_level,'FND_MO_REPORTING_LEVEL',0) reporting_level,
   case :p_reporting_level when '1000' then x.ledger when '3000' then (select haou.name from hr_all_organization_units haou where haou.organization_id = x.org_id) end reporting_entity
  from
   (--start x
    --X-Q1 - Transactions
    select
     substrb(party.party_name,1,50) cust_name,
     cust_acct.account_number cust_no,
     xxen_util.meaning(cust_acct.customer_type,'CUSTOMER_TYPE',222) cust_type,
     nvl(sales.name,jrrev.resource_name) salesperson,
     nvl(sales.salesrep_id, -3) inv_tid,
     site.site_use_id contact_site_id,
     loc.state cust_state,
     loc.city cust_city,
     (select ftv.territory_short_name from fnd_territories_vl ftv where ftv.territory_code = loc.country) cust_country,
     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.trx_date,
     ps.due_date,
     decode(nvl2(:p_in_currency,'N','Y'), 'Y', ps.amount_due_original * nvl(ps.exchange_rate, 1), ps.amount_due_original) amt_due_original,
     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.trx_date) days_past_trx,
     ceil(:p_in_as_of_date_low - ps.due_date) days_past_due,
     ceil(trunc(sysdate) - ps.trx_date) current_days_past_trx,
     ceil(trunc(sysdate) - ps.due_date) current_days_past_due,
     ps.amount_adjusted amount_adjusted,
     ps.amount_applied amount_applied,
     ps.amount_credited amount_credited,
     ps.amount_due_remaining, --xxecl
     ps.acctd_amount_due_remaining, --xxecl
     ps.gl_date gl_date,
     decode(ps.invoice_currency_code, gsob.currency_code, NULL, decode(ps.exchange_rate, null, '*', null)) data_converted,
     nvl(ps.exchange_rate, 1) ps_exchange_rate,
     (select rtv.name from ra_terms_vl rtv where rtv.term_id = ps.term_id) invoice_terms,
     --
&lp_bucket_cols2
     --
     c.code_combination_id,
     c.chart_of_accounts_id,
     ci.cons_billing_number  cons_billing_number,
     arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type,
     ps.invoice_currency_code,
     gsob.currency_code functional_currency,
     gsob.name ledger,
     ps.org_id,
     ps.status
    from
     hz_cust_accounts cust_acct,
     hz_parties party,
     (-- Start ps payment_schedules Query
      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.trx_date,
       a.due_date,
       sum(a.amt_due_remaining) amt_due_remaining,
       a.trx_number,
       a.amount_due_original,
       a.amount_adjusted,
       a.amount_applied ,
       a.amount_credited ,
       a.amount_adjusted_pending,
       a.amount_due_remaining, --xxecl
	      a.acctd_amount_due_remaining, --xxecl
       a.gl_date ,
       a.cust_trx_type_id,
       a.org_id,
       a.invoice_currency_code,
       a.exchange_rate,
       a.term_id,
       a.status,
       sum(a.cons_inv_id) cons_inv_id
      from
       (--Start a
        --A-Q1 - Adjustments after the As of Date
        select
         ps.customer_id,
         ps.customer_site_use_id,
         ps.customer_trx_id,
         ps.payment_schedule_id,
         ps.class,
         0 primary_salesrep_id,
         ps.trx_date,
         ps.due_date,
         nvl(sum(decode(nvl2(:p_in_currency,'N','Y'), 'Y', nvl(adj.acctd_amount, 0), adj.amount)),0) * (-1)  amt_due_remaining,
         ps.trx_number,
         ps.amount_due_original,
         ps.amount_adjusted,
         ps.amount_applied,
         ps.amount_credited,
         ps.amount_adjusted_pending,
         ps.amount_due_remaining, --xxecl
         ps.acctd_amount_due_remaining, --xxecl
         ps.gl_date,
         ps.cust_trx_type_id,
         ps.org_id,
         ps.invoice_currency_code,
         nvl(ps.exchange_rate,1) exchange_rate,
         ps.term_id,
         ps.status,
         0 cons_inv_id
        from
         ar_payment_schedules ps,
         ar_adjustments adj
        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
        and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y'
        and adj.org_id = ps.org_id
       group by
        ps.customer_id,
        ps.customer_site_use_id,
        ps.customer_trx_id,
        ps.class,
        ps.trx_date,
        ps.due_date,
        ps.trx_number,
        ps.amount_due_original,
        ps.amount_adjusted,
        ps.amount_applied,
        ps.amount_credited,
        ps.amount_adjusted_pending,
        ps.amount_due_remaining, --xxecl
        ps.acctd_amount_due_remaining, --xxecl
        ps.gl_date,
        ps.cust_trx_type_id,
        ps.org_id,
        ps.invoice_currency_code,
        nvl(ps.exchange_rate,1),
        ps.term_id,
        ps.status,
        ps.payment_schedule_id
       union all
       --A-Q2 - Receivable Applications after the As of Date
       select
        ps.customer_id,
        ps.customer_site_use_id,
        ps.customer_trx_id,
        ps.payment_schedule_id,
        ps.class,
        0 primary_salesrep_id,
        ps.trx_date,
        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
           ) amt_due_remaining,
        ps.trx_number,
        ps.amount_due_original,
        ps.amount_adjusted,
        ps.amount_applied ,
        ps.amount_credited,
        ps.amount_adjusted_pending,
        ps.amount_due_remaining, --xxecl
        ps.acctd_amount_due_remaining, --xxecl
        ps.gl_date gl_date,
        ps.cust_trx_type_id,
        ps.org_id,
        ps.invoice_currency_code,
        nvl(ps.exchange_rate, 1) exchange_rate,
        ps.term_id,
        ps.status,
        0 cons_inv_id
       from
        ar_payment_schedules ps,
        ar_receivable_applications app
       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
       and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y'
       and app.org_id = ps.org_id
       group by
        ps.customer_id,
        ps.customer_site_use_id,
        ps.customer_trx_id,
        ps.class,
        ps.trx_date,
        ps.due_date,
        ps.trx_number,
        ps.amount_due_original,
        ps.amount_adjusted,
        ps.amount_applied,
        ps.amount_credited,
        ps.amount_adjusted_pending,
        ps.amount_due_remaining, --xxecl
        ps.acctd_amount_due_remaining, --xxecl
        ps.gl_date,
        ps.cust_trx_type_id,
        ps.org_id,
        ps.invoice_currency_code,
        nvl(ps.exchange_rate, 1),
        ps.term_id,
        ps.status,
        ps.payment_schedule_id
       union all
       --A-Q3 - Transactions Open as at the As Of Date excluding class 'CB' Charge Back
       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.trx_date,
        ps.due_date,
        case when ps.gl_date <= :p_in_as_of_date_low -- we don't age transactions after the as of date if including the open ar balances
        then decode( nvl2(:p_in_currency,'N','Y'), 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining)
        else null
        end amt_due_remaining, 
        ps.trx_number,
        ps.amount_due_original,
        ps.amount_adjusted,
        ps.amount_applied,
        ps.amount_credited,
        ps.amount_adjusted_pending,
        ps.amount_due_remaining, --xxecl
        ps.acctd_amount_due_remaining, --xxecl
        ps.gl_date,
        ps.cust_trx_type_id,
        ps.org_id,
        ps.invoice_currency_code,
        nvl(ps.exchange_rate, 1) exchange_rate,
        ps.term_id,
        ps.status,
        ps.cons_inv_id
       from
        ar_payment_schedules ps,
        ra_customer_trx ct
       where
           (   (ps.gl_date <= :p_in_as_of_date_low and ps.gl_date_closed > :p_in_as_of_date_low)
            or (:p_show_open_ar = 'Y' and ps.gl_date > :p_in_as_of_date_low and ps.status = 'OP')
           )
       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'
       and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y'
       and ct.org_id = ps.org_id
       union all
       --A-Q4 - CB Charge Back Transactions Open as at the As Of Date
       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.trx_date,
        ps.due_date,
        case when ps.gl_date <= :p_in_as_of_date_low -- we don't age transactions after the as of date if including the open ar balances
        then decode( nvl2(:p_in_currency,'N','Y'), 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining)
        else null
        end amt_due_remaining, 
        ps.trx_number,
        ps.amount_due_original,
        ps.amount_adjusted,
        ps.amount_applied,
        ps.amount_credited,
        ps.amount_adjusted_pending,
        ps.amount_due_remaining, --xxecl
        ps.acctd_amount_due_remaining, --xxecl
        ps.gl_date,
        ps.cust_trx_type_id,
        ps.org_id,
        ps.invoice_currency_code,
        nvl(ps.exchange_rate, 1) exchange_rate,
        ps.term_id,
        ps.status,
        ps.cons_inv_id
       from
        ar_payment_schedules ps,
        ra_customer_trx ct,
        ar_adjustments adj
       where
           (   (ps.gl_date <= :p_in_as_of_date_low and ps.gl_date_closed > :p_in_as_of_date_low)
            or (:p_show_open_ar = 'Y' and ps.gl_date > :p_in_as_of_date_low and ps.status = 'OP')
           )
       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
       and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y'
       and ct.org_id = ps.org_id
       and adj.org_id = ps.org_id
      ) a
     group by
      a.customer_id,
      a.customer_site_use_id,
      a.customer_trx_id,
      a.payment_schedule_id,
      a.class,
      a.trx_date,
      a.due_date,
      a.trx_number,
      a.amount_due_original,
      a.amount_adjusted,
      a.amount_applied,
      a.amount_credited,
      a.amount_adjusted_pending,
      a.amount_due_remaining, --xxecl
      a.acctd_amount_due_remaining, --xxecl
      a.gl_date,
      a.cust_trx_type_id,
      a.org_id,
      a.invoice_currency_code,
      a.exchange_rate,
      a.term_id,
      a.status
     ) ps,
     ar_cons_inv ci,
     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,
     ra_customer_trx rct,
     gl_sets_of_books gsob
    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)
              )
        )
    and rct.customer_trx_id = ps.customer_trx_id
    and gsob.set_of_books_id = rct.set_of_books_id
    &lp_invoice_type_low
    &lp_invoice_type_high
    &lp_bal_seg_low
    &lp_bal_seg_high
    &lp_acc_seg_low
    &lp_acc_seg_high
    and ps.cons_inv_id = ci.cons_inv_id(+)
    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 xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y'
    and gld.org_id = ps.org_id
    and site.org_id = ps.org_id
    and 2=2
    union all
    -- X-Q2 - On Account, Unapplied, Unidentified, Other
    select
     substrb(nvl(party.party_name,:p_short_unid_phrase),1,50) cust_name,
     cust_acct.account_number cust_no,
     xxen_util.meaning