select
y.operating_unit,
y.supplier_name,
y.supplier_number,
y.status,
y.gl_account,
xxen_util.segment_description(y.gl_account,'SEGMENT2',y.chart_of_accounts_id) gl_account_desciption,
max(y.trx_type) trx_type,
xxen_util.segment_description(max(y.trx_type),'SEGMENT2',y.chart_of_accounts_id) tt_description,
sum(y.amount_0_30) amount_0_30,
sum(y.amount_31_60) amount_31_60,
sum(y.amount_61_90) amount_61_90,
sum(y.amount_91_120) amount_91_120,
sum(y.amount_121_150) amount_121_150,
sum(y.amount_151_180) amount_151_180,
sum(y.amount_181_365) amount_181_365,
sum(y.amount_366_540) amount_366_540,
sum(y.amount_541) amount_541,
sum(y.amount_not_due) amount_not_due,
sum(y.amount) amount_total
from
(
select
x.*,
case when x.due_date between :p_date-30 and :p_date then x.amount end amount_0_30,
case when x.due_date between :p_date-60 and :p_date-31 then x.amount end amount_31_60,
case when x.due_date between :p_date-90 and :p_date-61 then x.amount end amount_61_90,
case when x.due_date between :p_date-120 and :p_date-91 then x.amount end amount_91_120,
case when x.due_date between :p_date-150 and :p_date-121 then x.amount end amount_121_150,
case when x.due_date between :p_date-180 and :p_date-151 then x.amount end amount_151_180,
case when x.due_date between :p_date-365 and :p_date-181 then x.amount end amount_181_365,
case when x.due_date between :p_date-540 and :p_date-366 then x.amount end amount_366_540,
case when x.due_date <=:p_date-541 then x.amount end amount_541,
case when x.max_due_date>:p_date then x.amount end amount_not_due
from
(
select
aps.vendor_name supplier_name,
aps.segment1 supplier_number,
aia.invoice_num,
apsa.due_date,
max(apsa.due_date) over (partition by apsa.invoice_id) max_due_date,
nvl((select sum(aida.amount) from ap_invoice_distributions_all aida where aia.invoice_id=aida.invoice_id and aida.accounting_event_id in (select xah.event_id from xla_ae_headers xah where xah.gl_transfer_status_code='Y')),0)*nvl(aia.exchange_rate,1)- --invoice_amount_posted
nvl((select sum(aipa.amount) from ap_invoice_payments_all aipa where aia.invoice_id=aipa.invoice_id and aipa.accounting_event_id in (select xah.event_id from xla_ae_headers xah where xah.gl_transfer_status_code='Y')),0)*nvl(aia.exchange_rate,1) amount, --paid_amount_posted,
case when assa.hold_all_payments_flag='Y' or aps.hold_all_payments_flag='Y' or exists (select null from ap_holds_all aha where aia.invoice_id=aha.invoice_id and aha.release_lookup_code is null) then 'H' else 'O' end as status,
gcc.segment2 gl_account,
(
select distinct
max(x.segment2) keep (dense_rank last order by x.amount, x.segment2) over (partition by x.invoice_id) segment2
from
(
select distinct
aida.invoice_id,
gcc.segment2,
sum(aida.amount) over (partition by aida.invoice_id, gcc.segment2) amount
from
ap_invoice_distributions_all aida,
gl_code_combinations gcc
where
aida.line_type_lookup_code='ITEM' and
aida.dist_code_combination_id=gcc.code_combination_id
) x
where
aia.invoice_id=x.invoice_id
) trx_type,
hou.name operating_unit,
gcc.chart_of_accounts_id,
hou.set_of_books_id
from
ap_invoices_all aia,
ap_payment_schedules_all apsa,
ap_suppliers aps,
ap_supplier_sites_all assa,
gl_code_combinations gcc,
hr_operating_units hou
where
1=1 and
aia.invoice_id=apsa.invoice_id and
decode(aia.vendor_id,-222,null,aia.vendor_id)=aps.vendor_id(+) and
decode(aia.vendor_id,-222,null,aia.vendor_site_id)=assa.vendor_site_id(+) and
aia.accts_pay_code_combination_id=gcc.code_combination_id and
aia.org_id=hou.organization_id and
(
aia.gl_date<=:p_date or
exists (select null from ap_invoice_lines_all aila where aia.invoice_id=aila.invoice_id and aila.accounting_date<=:p_date) or
exists (select null from ap_invoice_distributions_all aida where aia.invoice_id=aida.invoice_id and aida.accounting_date<=:p_date)
)
) x
) y
group by
y.operating_unit,
y.supplier_name,
y.supplier_number,
y.status,
y.gl_account,
y.gl_account,
y.chart_of_accounts_id
order by
y.operating_unit,
amount_total desc |
