AP Accounted Invoice Aging

with xtb as
  (select /*+ parallel(xtb) leading(xtb) NO_MERGE */
    xtb.definition_code tb_code,
    xtdv.name tb_name,
    nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
    xtb.code_combination_id,
    xtb.source_application_id,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_unrounded_cr,0) - nvl(xtb.entered_unrounded_dr,0))) entered_unrounded_orig_amount,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0)   - nvl(xtb.entered_rounded_dr,0)))   entered_rounded_orig_amount,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_unrounded_cr,0)   - nvl(xtb.acctd_unrounded_dr,0)))   acctd_unrounded_orig_amount,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0)     - nvl(xtb.acctd_rounded_dr,0)) )    acctd_rounded_orig_amount,
    sum (nvl(xtb.entered_unrounded_cr,0)) - sum(nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
    sum (nvl(xtb.entered_rounded_cr,0))   - sum(nvl(xtb.entered_rounded_dr,0))   entered_rounded_rem_amount,
    sum (nvl(xtb.acctd_unrounded_cr,0))   - sum(nvl(xtb.acctd_unrounded_dr,0))   acctd_unrounded_rem_amount,
    sum (nvl(xtb.acctd_rounded_cr,0))     - sum(nvl(xtb.acctd_rounded_dr,0))     acctd_rounded_rem_amount,
    case when sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0)   - nvl(xtb.entered_rounded_dr,0))) = 0
    then 1
    else sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0)     - nvl(xtb.acctd_rounded_dr,0)) ) /
         sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0)   - nvl(xtb.entered_rounded_dr,0)))
    end conversion_rate,
    xtb.ledger_id,
    xtb.party_id,
    xtb.balancing_segment_value,
    xtb.natural_account_segment_value,
    xtb.cost_center_segment_value,
    xtb.intercompany_segment_value,
    xtb.management_segment_value,
    dte.as_of_date
   from
    xla_trial_balances xtb,
    xla_tb_definitions_vl xtdv,
    (select
      gl.ledger_id,
      :p_as_of_date as_of_date
     from
      gl_ledgers gl
     where
         :p_as_of_date is not null
     and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or
           (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and
            exists
             (select 1
              from
               gl_access_sets gas,
               gl_access_set_assignments asa
              where
               gas.access_set_id=asa.access_set_id and
               asa.ledger_id=gl.ledger_id and
               ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or
                 gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1')
               )
             )
           )
         )
     union
     select
      x.ledger_id,
      x.end_date as_of_date
     from
      (select
       1-rank() over (partition by gl.name order by ((glp.period_year * 100000) + glp.period_num) desc) relative_period,
       gl.ledger_id,
       glp.end_date
       from
        gl_ledgers gl
       ,gl_periods glp
       ,gl_periods glpc
       where
           :p_as_of_date is null
       and :p_relative_period is not null
       and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or
             (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and
              exists
               (select 1
                from
                 gl_access_sets gas,
                 gl_access_set_assignments asa
                where
                 gas.access_set_id=asa.access_set_id and
                 asa.ledger_id=gl.ledger_id and
                 ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or
                   gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1')
                 )
               )
             )
           )
       and gl.period_set_name=glp.period_set_name
       and gl.accounted_period_type=glp.period_type
       and glp.adjustment_period_flag='N'
       and gl.period_set_name=glpc.period_set_name
       and gl.accounted_period_type=glpc.period_type
       and glpc.adjustment_period_flag='N'
       and trunc(sysdate) between glpc.start_date and glpc.end_date
       and glp.start_date <= glpc.start_date
      ) x
     where
         x.relative_period = to_number(:p_relative_period)
    ) dte
   where
    xtb.ledger_id = dte.ledger_id and
    xtb.definition_code = xtdv.definition_code and
    xtb.source_application_id=200 and
    xtb.gl_date between to_date('01/01/1950','DD/MM/YYYY') and dte.as_of_date and
    xtdv.enabled_flag='Y'
   group by
    xtb.definition_code,
    xtdv.name,
    nvl(xtb.applied_to_entity_id,xtb.source_entity_id),
    xtb.code_combination_id ,
    xtb.source_application_id,
    xtb.ledger_id,
    xtb.party_id,
    xtb.balancing_segment_value,
    xtb.natural_account_segment_value,
    xtb.cost_center_segment_value,
    xtb.intercompany_segment_value,
    xtb.management_segment_value,
    dte.as_of_date
   having
    sum(nvl(xtb.acctd_rounded_cr,0)) <> sum (nvl(xtb.acctd_rounded_dr,0))
  ),
ap_inv as
(select
  xtb.as_of_date as_of_date,
  xtb.tb_code,
  xtb.tb_name,
  xtb.ledger_id,
  gl.name ledger_name,
  gl.short_name ledger_short_name,
  gl.currency_code ledger_currency,
  haouv.name operating_unit,
  xtb.source_application_id,
  xtb.entity_id source_entity_id,
  xte.entity_code source_entity_code,
  xte.security_id_int_1 org_id,
  xte.transaction_number source_transaction_number,
  xetv.name source_trx_type,
  xtb.code_combination_id,
  xtb.entered_unrounded_orig_amount,
  xtb.entered_rounded_orig_amount,
  xtb.acctd_unrounded_orig_amount,
  xtb.acctd_rounded_orig_amount,
  xtb.entered_unrounded_rem_amount,
  xtb.entered_rounded_rem_amount,
  xtb.acctd_unrounded_rem_amount,
  xtb.acctd_rounded_rem_amount,
  xtb.balancing_segment_value,
  xtb.natural_account_segment_value,
  xtb.cost_center_segment_value,
  xtb.intercompany_segment_value,
  xtb.management_segment_value,
  xtb.entity_id applied_to_entity_id,
  xtb.party_id third_party_number,
  --
  hp.party_name               third_party_name,
  hps.party_site_name         third_party_site_name,
  ftv2.territory_short_name   third_party_site_country,
  asu.segment1                vendor_number,
  asu.vendor_name             vendor_name,
  assa.vendor_site_code       vendor_site,
  ftv1.territory_short_name   vendor_site_country,
  aia.invoice_num             invoice_number,
  aia.doc_sequence_value      invoice_document_number,
  aia.invoice_date            invoice_date,
  aia.gl_date                 invoice_gl_date,
  aia.cancelled_date          invoice_cancelled_date,
  aia.source                  invoice_source,
  xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200)
                              invoice_type,
  aia.description             invoice_description,
  xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id)
                              invoice_status,
  xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200)
                              invoice_payment_status,
  xxen_util.meaning(aia.pay_group_lookup_code,'PAY GROUP',201) pay_group,
  (select ipmv.payment_method_name from iby_payment_methods_vl ipmv where ipmv.payment_method_code = aia.payment_method_code) payment_method,
  aia.dispute_reason          dispute_reason,
  apsa.iby_hold_reason        payment_hold_reason,
  aia.invoice_currency_code   invoice_currency, 
  aia.invoice_amount          invoice_amount,
  nvl(aia.base_amount,aia.invoice_amount) invoice_base_amount,
  case when aia.invoice_currency_code != gl.currency_code then aia.exchange_rate end invoice_exchange_rate,
  case when aia.invoice_currency_code != gl.currency_code then (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type = aia.exchange_rate_type) end invoice_exchange_rate_type,
  case when aia.invoice_currency_code != gl.currency_code then aia.exchange_date end invoice_exchange_rate_date,
  aia.invoice_id,
  apsa.payment_num,
  apsa.due_date,
  ceil(xtb.as_of_date-apsa.due_date) days_due,
  case
  when xtb.acctd_rounded_rem_amount = 0 then 0
  when count(apsa.payment_num) over (partition by aia.invoice_id) = 1 then xtb.acctd_rounded_rem_amount
  -- multiple payment schedules, but invoice is cancelled or over paid, then allocate to the first payment schedule only
  when aia.invoice_amount = 0 or sign(xtb.acctd_rounded_rem_amount) != sign(aia.invoice_amount)
  then case when apsa.payment_num = first_value(apsa.payment_num) over (partition by aia.invoice_id order by apsa.payment_num)
       then xtb.acctd_rounded_rem_amount else 0
       end
  -- multiple paymement schedules. Consume amount remaining from latest payment schedule to first
  when nvl(sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),
           abs(xtb.acctd_rounded_rem_amount)) <= abs(xtb.acctd_rounded_rem_amount)
  then case
       when apsa.payment_num = first_value(apsa.payment_num) over (partition by aia.invoice_id order by apsa.payment_num) -- 1st payment schedule - consume all remaining amount
       then sign(xtb.acctd_rounded_rem_amount) * (nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.acctd_rounded_rem_amount)))
       when nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding)
               ,abs(xtb.acctd_rounded_rem_amount)) >= abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))
       then sign(xtb.acctd_rounded_rem_amount) * abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))
       else sign(xtb.acctd_rounded_rem_amount) * (nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.acctd_rounded_rem_amount)))
       end
  else 0 -- all remaining amount already consumed
  end ps_amount_remaining,
  decode(gl.currency_code,:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where gl.currency_code=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,
  --
  cbbv.bank_name               remit_to_bank_name,
  cbbv.bank_number             remit_to_bank_number,
  cbbv.bank_branch_name        remit_to_branch_name,
  cbbv.branch_number           remit_to_branch_number,
  cbbv.country                 remit_to_branch_country,
  ieba.masked_bank_account_num remit_to_account_num
 from
  xtb xtb,
  xla_transaction_entities xte,
  xla_entity_types_vl xetv,
  ap_invoices_all aia,
  ap_payment_schedules_all apsa,
  ap_suppliers asu,
  ap_supplier_sites_all assa,
  fnd_territories_vl ftv1,
  hz_parties hp,
  hz_party_sites hps,
  hz_locations hl,
  fnd_territories_vl ftv2,
  gl_ledgers gl,
  fnd_currencies_vl fcv,
  hr_all_organization_units_vl haouv,
  iby_ext_bank_accounts ieba,
  ce_bank_branches_v cbbv
 where
  xtb.entity_id=xte.entity_id and
  xtb.source_application_id=xte.application_id and
  xte.entity_code='AP_INVOICES' and
  xetv.entity_code=xte.entity_code and
  xetv.application_id=xte.application_id and
  aia.invoice_id=nvl(xte.source_id_int_1,-99) and
  apsa.invoice_id=nvl(xte.source_id_int_1,-99) and
  aia.vendor_id=asu.vendor_id(+) and
  aia.vendor_site_id=assa.vendor_site_id(+) and
  assa.country=ftv1.territory_code(+) and
  aia.party_id=hp.party_id and