with
q_main as
(select
'I' transaction_type,
asup.segment1 vendor_number,
asup.vendor_name,
asup.vendor_name_alt,
ass.vendor_site_code,
ass.vendor_site_code_alt,
ass.address_line1 ||' '|| ass.address_line2 ||' '|| ass.address_line3 ||' '|| ass.city ||' '|| ass.state ||' '|| ass.zip vendor_site_address,
ass.vat_registration_num,
hro.name organization_name,
alc.displayed_field lookup_value,
ai.invoice_type_lookup_code lookup_code,
ai.invoice_num doc_number,
to_char(ai.doc_sequence_value) voucher_number,
ai.invoice_date doc_date,
ai.payment_status_flag,
ai.gl_date,
ai.invoice_currency_code currency_code,
ai.invoice_amount entered_amount,
nvl(ai.base_amount,round(ai.invoice_amount * nvl(ai.exchange_rate,1),2)) accounted_amount,
to_number(null) paid_amount,
to_number(null) inv_disc_taken,
to_number(null) inv_paid_amount,
to_number(null) paid_exchange_rate,
nvl(ai.exchange_rate,1) inv_exchange_rate,
ai.description description,
gp.period_name gp_period_name,
gp.period_num gp_period_num,
gp.start_date gp_start_date,
ap_invoices_pkg.get_posting_status (ai.invoice_id) posting_status,
ap_invoices_pkg.get_wfapproval_status(ai.invoice_id,ai.org_id) approval_status,
ap_tp_stmt_pkg.invoice_validate_status(ai.invoice_id) validate_status,
ai.cancelled_date cancel_date,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = ai.accts_pay_code_combination_id) liability_account,
ai.vendor_id,
ai.vendor_site_id,
hro.organization_id,
'I' || ai.invoice_id id,
ai.invoice_id gst_trx_id
from
ap_invoices ai,
ap_suppliers asup,
ap_supplier_sites_all ass,
hr_operating_units hro,
gl_periods gp,
gl_ledgers gled,
ap_lookup_codes alc
where
ai.vendor_id = asup.vendor_id
and asup.enabled_flag = 'Y'
and ai.vendor_site_id = ass.vendor_site_id
and ass.org_id = hro.organization_id
and gled.period_set_name = gp.period_set_name
and gled.accounted_period_type = gp.period_type
and gp.adjustment_period_flag ='N'
and gled.ledger_id = ai.set_of_books_id
and ai.invoice_type_lookup_code = alc.lookup_code
and ai.invoice_type_lookup_code <> 'PREPAYMENT'
and alc.lookup_type = 'INVOICE TYPE'
and ai.gl_date between gp.start_date and gp.end_date
and ai.gl_date between :p_from_gl_date and :p_to_gl_date
and ap_invoices_pkg.get_posting_status(ai.invoice_id) = decode(:p_accounted,'ACCOUNTED','Y' ,'UNACCOUNTED','N' ,ap_invoices_pkg.get_posting_status(ai.invoice_id))
and 1=1
and 3=3
&gc_reporting_entity
&gc_unapproved_trx
&gc_currency
&gc_validate_inv
&gc_org_id
union all
select
decode(ai.invoice_type_lookup_code,'PREPAYMENT','A','P') transaction_type,
asup.segment1 vendor_number,
asup.vendor_name,
asup.vendor_name_alt,
ass.vendor_site_code,
ass.vendor_site_code_alt,
ass.address_line1 ||' '|| ass.address_line2 ||' '|| ass.address_line3 ||' '|| ass.city ||' '|| ass.state ||' '|| ass.zip vendor_site_address,
ass.vat_registration_num,
hro.name organization_name,
alc.displayed_field lookup_value,
ac.payment_method_lookup_code lookup_code,
to_char(ac.check_number)||'/'||ai.invoice_num doc_number,
to_char(ac.doc_sequence_value) voucher_number,
ac.check_date doc_date,
null payment_status_flag,
aip.accounting_date gl_date,
ac.currency_code,
aip.amount entered_amount,
nvl(aip.payment_base_amount,round(aip.amount * nvl(aip.exchange_rate,1),2)) accounted_amount,
--
-- for Gain/Loss Calculation
aip.amount paid_amount,
aip.discount_taken inv_disc_taken,
aip.amount + nvl(aip.discount_taken,0) inv_paid_amount,
nvl(aip.exchange_rate,1) paid_exchange_rate,
nvl(case
when nvl(aip.exchange_rate,1) = 1
then 1
when ai.invoice_type_lookup_code='PREPAYMENT'
then (select
nvl(ai2.exchange_rate,1)
from
ap_prepay_history aph2,
ap_invoices ai2
where
aph2.prepay_invoice_id = ai.invoice_id and
aph2.invoice_id = ai2.invoice_id and
ai2.invoice_currency_code = ai2.payment_currency_code and
ai2.payment_currency_code = ac.currency_code and
rownum <= 1
)
else null
end,
case
when ai.invoice_currency_code = ai.payment_currency_code
and ai.payment_currency_code = ac.currency_code
then nvl(ai.exchange_rate,1)
else nvl(aip.exchange_rate,1)
end
) inv_exchange_rate,
ac.description,
gp.period_name gp_period_name,
gp.period_num gp_period_num,
gp.start_date gp_start_date,
aip.accrual_posted_flag posting_status,
alc1.displayed_field approval_status,
null validate_status,
to_date(null) cancel_date,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = ai.accts_pay_code_combination_id) liability_account,
ac.vendor_id,
ac.vendor_site_id,
hro.organization_id,
'P' || aip.invoice_payment_id id,
ai.invoice_id gst_trx_id
from
ap_invoices ai,
ap_invoice_payments aip,
ap_checks ac,
ap_suppliers asup,
ap_supplier_sites_all ass,
hr_operating_units hro,
gl_periods gp,
gl_ledgers gled,
ap_lookup_codes alc,
ap_lookup_codes alc1
where
aip.check_id = ac.check_id
and aip.invoice_id = ai.invoice_id
and ac.vendor_id = asup.vendor_id
and asup.enabled_flag = 'Y'
and ac.vendor_site_id = ass.vendor_site_id
and ass.org_id = hro.organization_id
and gled.period_set_name = gp.period_set_name
and gled.accounted_period_type = gp.period_type
and gp.adjustment_period_flag ='N'
and gled.ledger_id = aip.set_of_books_id
and ac.payment_type_flag = alc.lookup_code
and alc.lookup_type = 'PAYMENT TYPE'
and alc1.lookup_type = 'CHECK STATE'
and ac.status_lookup_code = alc1.lookup_code
and aip.accounting_date between gp.start_date and gp.end_date
and aip.accounting_date between :p_from_gl_date and :p_to_gl_date
and 2=2
and 3=3
&gc_reporting_entity
&gc_pmt_accounted
&gc_pmt_org_id
&gc_pmt_currency
),
q_supplier as
(select
asup.segment1 vendor_number,
asup.vendor_name,
asup.vendor_name_alt,
asup.vendor_id,
ass.vendor_site_id,
ass.vendor_site_code,
ass.vendor_site_code_alt,
ass.address_line1 ||' '|| ass.address_line2 ||' '|| ass.address_line3 ||' '|| ass.city ||' '|| ass.state ||' '|| ass.zip vendor_site_address,
ass.vat_registration_num,
hro.name organization_name,
hro.organization_id,
ap_tp_stmt_pkg.balance_brought_forward(ass.vendor_id,ass.vendor_site_id,ass.org_id) balance_brought_forward,
row_number() over (partition by asup.vendor_id order by ass.vendor_site_code) site_count
from
ap_suppliers asup,
ap_supplier_sites_all ass,
hr_operating_units hro
where
asup.vendor_id = ass.vendor_id
and ass.org_id = hro.organization_id
and asup.enabled_flag = 'Y'
and 3=3
&gc_reporting_entity
)
--
-- main query start here
--
select
:p_reporting_entity_name &reporting_entity_col_name,
q.vendor_name supplier_name,
q.vendor_number supplier_number,
q.vendor_alternative_name supplier_alt_name,
q.vendor_tax_registration supplier_tax_registration,
q.vendor_site_code supplier_site_code,
q.vendor_site_alternative_code supplier_site_alt_code,
q.vendor_site_address supplier_site_address,
&lp_operating_unit_column
q.period_name,
replace(q.record_type,'_',' ') record_type
&lp_report_level_columns
&il_reg_columns
&il_tax_outer
from
(select
z.*,
case when z.record_type like '%Summary' then 0 else null end balance_brought_forward_debit,
case when z.record_type like '%Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id) else null end balance_brought_forward_credit,
case z.record_type
when 'Supplier Site Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_id)
else null
end net_debit,
case z.record_type
when 'Supplier Site Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_id)
else null
end net_credit,
case z.record_type
when 'Supplier Site Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id order by z.seq rows between unbounded preceding and current row)
when 'Operating Unit Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_id,z.organization_id order by z.seq rows between unbounded preceding and current row)
when 'Supplier Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_id order by z.seq rows between unbounded preceding and current row)
else null
end cumulative_debit,
case z.record_type
when 'Supplier Site Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_id,z.organization_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_id)
else null
end cumulative_credit,
case z.record_type
when 'Supplier Site Summary' then sum(nvl(z.currency_gain_loss,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then sum(nvl(z.currency_gain_loss,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then sum(nvl(z.currency_gain_loss,0)) over (partition by z.vendor_id)
else null
end net_currency_gain_loss,
case z.record_type
when 'Supplier Site Summary' then sum(nvl(z.discount_taken,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then sum(nvl(z.discount_taken,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then sum(nvl(z.discount_taken,0)) over (partition by z.vendor_id)
else null
end net_discount_taken,
case z.record_type
when 'Transaction' then
sum(nvl(z.bbf,0)) over (partition by z.vendor_id,
case when :p_summary_level in ('Supplier Site','Operating Unit') then z.organization_id end,
case when :p_summary_level='Supplier Site' then z.vendor_site_id end) +
nvl(sum(nvl(z.credit,0)-nvl(z.debit,0)) over (partition by z.vendor_id,
case when :p_summary_level in ('Supplier Site','Operating Unit') then z.organization_id end,
case when :p_summary_level='Supplier Site' then z.vendor_site_id end
order by z.seq rows between unbounded preceding and 1 preceding),0)
when 'Supplier Site Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_id)
end opening_balance,
case z.record_type
when 'Transaction' then
sum(nvl(z.bbf,0)) over (partition by z.vendor_id,
case when :p_summary_level in ('Supplier Site','Operating Unit') then z.organization_id end,
case when :p_summary_level='Supplier Site' then z.vendor_site_id end) +
sum(nvl(z.credit,0)-nvl(z.debit,0)) over (partition by z.vendor_id,
case when :p_summary_level in ('Supplier Site','Operating Unit') then z.organization_id end,
case when :p_summary_level='Supplier Site' then z.vendor_site_id end
order by z.seq rows between unbounded preceding and current row)
when 'Supplier Site Summary' then
sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id) +
sum(nvl(z.credit,0)-nvl(z.debit,0)) over (partition by z.vendor_id,z.organization_id,z.vendor_site_id)
when 'Operating Unit Summary' then
sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id) +
sum(nvl(z.credit,0)-nvl(z.debit,0)) over (partition by z.vendor_id,z.organization_id)
when 'Supplier Summary' then
sum(nvl(z.bbf,0)) over (partition by z.vendor_id) +
sum(nvl(z.credit,0)-nvl(z.debit,0)) over (partition by z.vendor_id)
end closing_balance
from
(select
rownum seq,
y.*
from
(select
x.*,
sum(nvl(x.bbf,0)) over (partition by x.vendor_id) +
sum(nvl(x.credit,0)) over (partition by x.vendor_id) -
sum(nvl(x.debit,0)) over (partition by x.vendor_id) supplier_closing_balance
from
(-- summary record
select
nvl(:p_summary_level,'Supplier') || ' Summary' record_type,
q_supplier.vendor_name vendor_name,
q_supplier.vendor_number vendor_number,
q_supplier.vendor_name_alt vendor_alternative_name,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vat_registration_num else null end vendor_tax_registration,
case when nvl(:p_summary_level,'Supplier') in ('Supplier Site','Operating Unit') then q_supplier.organization_name else null end operating_unit,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_code else null end vendor_site_code,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_code_alt else null end vendor_site_alternative_code,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_address else null end vendor_site_address,
null period_name,
null document_type,
null document_number,
null voucher_number,
to_date(null) document_date,
null document_status,
to_date(null) gl_date,
null description,
to_number(null) debit,
to_number(null) credit,
to_number(null) currency_gain_loss,
to_number(null) discount_taken,
--
to_number(null) original_amount,
null currency_code,
null liability_account,
1 sort_order,
to_date(null) period_start_date,
to_number(null) period_num,
q_supplier.vendor_id,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_id else null end vendor_site_id,
case when nvl(:p_summary_level,'Supplier') in ('Supplier Site','Operating Unit') then q_supplier.organization_id else null end organization_id,
sum(q_supplier.balance_brought_forward) bbf,
to_number(null) gst_trx_id
from
q_supplier
where
:p_report_level in ('BOTH','SUMMARY')
group by
q_supplier.vendor_name,
q_supplier.vendor_number,
q_supplier.vendor_name_alt,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vat_registration_num else null end,
case when nvl(:p_summary_level,'Supplier') in ('Supplier Site','Operating Unit') then q_supplier.organization_name else null end,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_code else null end,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_code_alt else null end,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_address else null end,
q_supplier.vendor_id,
case when nvl(:p_summary_level,'Supplier') = 'Supplier Site' then q_supplier.vendor_site_id else null end,
case when nvl(:p_summary_level,'Supplier') in ('Supplier Site','Operating Unit') then q_supplier.organization_id else null end
union all
-- transaction records
select
'Transaction' record_type,
q_main.vendor_name vendor_name,
q_main.vendor_number vendor_number,
q_main.vendor_name_alt vendor_alternative_name,
q_main.vat_registration_num vendor_tax_registration,
q_main.organization_name operating_unit,
q_main.vendor_site_code vendor_site_code,
q_main.vendor_site_code_alt vendor_site_alternative_code,
q_main.vendor_site_address vendor_site_address,
q_main.gp_period_name period_name,
case q_main.transaction_type
when 'A' then 'Prepayment Application'
else q_main.lookup_value
end document_type,
q_main.doc_number document_number,
q_main.voucher_number,
q_main.doc_date document_date,
nvl2(q_main.cancel_date,'Cancelled/ ',null) || case q_main.validate_status
when 'Y' then 'Validated/ '
when 'N' then 'Un-Validated/ '
else null
end ||case q_main.posting_status
when 'Y' then 'Accounted'
when 'N' then 'Not Accounted'
else null
end ||case q_main.lookup_code
when 'PA' then '/ Applied'
else null
end document_status,
q_main.gl_date,
q_main.description,
--
case q_main.transaction_type
when 'I' then to_number(null)
else round(q_main.inv_paid_amount * q_main.inv_exchange_rate,2)
end debit,
case q_main.transaction_type
when 'I' then q_main.accounted_amount
else to_number(null)
end credit,
case when q_main.transaction_type != 'I'
and q_main.inv_exchange_rate != q_main.paid_exchange_rate
and round(q_main.paid_amount * q_main.inv_exchange_rate,2) - round(q_main.paid_amount * q_main.paid_exchange_rate,2) != 0
then round(q_main.paid_amount * q_main.inv_exchange_rate,2) - round(q_main.paid_amount * q_main.paid_exchange_rate,2)
else null
end currency_gain_loss,
decode(nvl(q_main.inv_disc_taken,0),0,to_number(null),round(q_main.inv_paid_amount * q_main.inv_exchange_rate,2) - round(q_main.paid_amount * q_main.inv_exchange_rate,2)) discount_taken,
--
q_main.entered_amount original_amount,
q_main.currency_code,
q_main.liability_account,
-1 sort_order,
q_main.gp_start_date period_start_date,
q_main.gp_period_num period_num,
q_main.vendor_id,
q_main.vendor_site_id,
q_main.organization_id,
to_number(null) bbf,
q_main.gst_trx_id
from
q_main
) x
order by
x.vendor_name,
x.vendor_number,
x.operating_unit,
x.vendor_site_code,
nvl(x.period_start_date,to_date('31/12/4712','DD/MM/YYYY')),
nvl(x.period_num,99),
nvl(x.period_name,'####'),
x.sort_order,
x.gl_date,
x.document_date
) y
where
(:p_incl_zero_bal_sup = 'Y' or nvl(y.supplier_closing_balance,0) != 0)
) z
order by
z.seq
) q
&il_from
where
( (:p_report_level = 'BOTH')
or (:p_report_level = 'TRANSACTION' and q.record_type = 'Transaction')
or (:p_report_level = 'SUMMARY' and q.record_type like '%Summary')
)
&il_where
order by
q.seq |
