AP Supplier Closing Balance
Description
Run
AP Supplier Closing Balance and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
As Of Date |
|
Date | |
Supplier |
|
LOV | |
Supplier Number |
|
LOV |