AP Accounted Invoice Aging

Description
Categories: Enginatics
Repository: Github
Application: Payables
Report: Accounts Payable Accounted Invoice Aging Report

Description.
Report details Aging of outstanding amounts at a specified point in time for Accounted Invoices.

For scheduling the report to run periodically, use the 'as of relative period close' offset parameter. This is the relative period offset to the current period, so when the current period chan ... 
Application: Payables
Report: Accounts Payable Accounted Invoice Aging Report

Description.
Report details Aging of outstanding amounts at a specified point in time for Accounted Invoices.

For scheduling the report to run periodically, use the 'as of relative period close' offset parameter. This is the relative period offset to the current period, so when the current period changes, the period close as of date will also be automatically updated when the report is re-run.
   more
Run AP Accounted Invoice Aging and other Oracle EBS reports with Blitz Report™ on our demo environment
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,
  asu.segment1                vendor_number,
  asu.vendor_name             vendor_name,
  assa.vendor_site_code       vendor_site,
  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,
  aia.invoice_currency_code   invoice_currency,
  aia.invoice_amount          invoice_amount,
  nvl(aia.base_amount,aia.invoice_amount) invoice_base_amount,
  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
 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,
  hz_parties hp,
  hz_party_sites hps,
  gl_ledgers gl,
  fnd_currencies_vl fcv,
  hr_all_organization_units_vl haouv
 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
  aia.party_id=hp.party_id and
  aia.party_site_id=hps.party_site_id(+) and
  (   (asu.employee_id is null and hps.party_site_id is not null)
   or ( asu.employee_id is not null)
  ) and
  xtb.ledger_id=gl.ledger_id and
  gl.currency_code=fcv.currency_code and
  haouv.organization_id=aia.org_id and
  aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where not exists (select null from mo_glob_org_access_tmp mgoat))
)
--
-- Main Query Starts Here
--
select
 ap_inv.ledger_name,
 ap_inv.tb_name trial_balance_name,
 ap_inv.ledger_currency,
 gcck.concatenated_segments account,
 ap_inv.third_party_name,
 ap_inv.third_party_number,
 ap_inv.third_party_site_name,
 ap_inv.vendor_number,
 ap_inv.vendor_name,
 ap_inv.vendor_site,
 ap_inv.operating_unit,
 ap_inv.source_trx_type transaction_type,
 ap_inv.source_transaction_number transaction_number,
 ap_inv.invoice_document_number,
 ap_inv.invoice_date,
 ap_inv.invoice_gl_date,
 ap_inv.invoice_cancelled_date,
 ap_inv.invoice_source,
 ap_inv.invoice_type,
 ap_inv.invoice_description,
 ap_inv.invoice_status,
 ap_inv.invoice_payment_status,
 ap_inv.invoice_currency,
 case when ap_inv.payment_num = first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id order by ap_inv.payment_num) then ap_inv.invoice_amount end invoice_amount,
 case when ap_inv.payment_num = first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id order by ap_inv.payment_num) then ap_inv.invoice_base_amount end invoice_base_amount,
 case when ap_inv.payment_num = first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_orig_amount end transaction_original_amount,
 case when ap_inv.payment_num = first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_rem_amount end transaction_remaining_amount,
 ap_inv.payment_num,
 ap_inv.as_of_date,
 ap_inv.due_date,
 ap_inv.days_due,
 &aging_bucket_cols
 &reval_columns
 ap_inv.balancing_segment_value balancing_segment,
 gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_BALANCING',ap_inv.balancing_segment_value) balancing_segment_desc,
 ap_inv.natural_account_segment_value account_segment,
 gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_ACCOUNT',ap_inv.natural_account_segment_value) account_segment_desc,
 ap_inv.cost_center_segment_value cost_center_segment,
 gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'FA_COST_CTR',ap_inv.cost_center_segment_value) cost_center_segment_desc,
 ap_inv.invoice_id
from
 ap_inv ap_inv,
 gl_code_combinations_kfv gcck
where
 1=1 and
 gcck.code_combination_id (+) = ap_inv.code_combination_id and
 nvl(ap_inv.ps_amount_remaining,0) != 0
order by
 ap_inv.ledger_name,
 ap_inv.tb_name,
 gcck.concatenated_segments,
 ap_inv.third_party_name,
 ap_inv.third_party_number,
 ap_inv.invoice_gl_date,
 ap_inv.invoice_id,
 ap_inv.payment_num
Parameter Name SQL text Validation
Ledger
(ap_inv.ledger_name=:ledger or
ap_inv.ledger_id in (select glsa.ledger_id from gl_ledgers gl,gl_ledger_set_assignments glsa where glsa.ledger_set_id=gl.ledger_id and gl.name=:ledger)
)
LOV
Operating Unit
ap_inv.operating_unit=:operating_unit
LOV
Report Definition
ap_inv.tb_name=:report_definition
LOV
As of Date
 
Date
As of Relative Period Close
 
LOV
Aging Bucket Name
select
 'case when ceil(ap_inv.as_of_date-ap_inv.due_date) between ' || to_char(aap.days_start) || ' and ' || to_char(aap.days_to) || ' and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "' || aap.title || '",'
from
 (select
   aapl.period_sequence_num,
   aapl.days_start,
   aapl.days_to,
   substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,30) title
  from
   ap_aging_periods aap,
   ap_aging_period_lines aapl
  where
   aap.aging_period_id = aapl.aging_period_id and
   aap.period_name = :p_aging_bucket_name
  order by
   aapl.period_sequence_num
 ) aap
LOV
Third Party Name
(ap_inv.third_party_name=:p_third_party_name or ap_inv.vendor_name=:p_third_party_name)
LOV
Payables Account From
gcck.concatenated_segments>=:p_account_from
LOV
Payables Account To
gcck.concatenated_segments<=:p_account_to
LOV
Revaluation Currency
 
LOV
Revaluation Rate Type
 
LOV
Revaluation Date
 
Date
Download
Blitz Report™

Blitz Report™ provides multiple benefits: