AP Supplier Statement
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Payables
Source: Supplier Statement
Short Name: APTPSTMT
DB package: AP_TP_STMT_PKG
Source: Supplier Statement
Short Name: APTPSTMT
DB package: AP_TP_STMT_PKG
with q_supplier as (select asup.segment1 vendor_number, asup.vendor_name vendor_name, asup.vendor_name_alt vendor_name_alt, asup.vendor_id vendor_id, ass.vendor_site_id vendor_site_id, ass.vendor_site_code vendor_site_code, ass.vendor_site_code_alt 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 vat_registration_num, hro.name organization_name, hro.organization_id organization_id, ap_tp_stmt_pkg.balance_brought_forward(ass.vendor_id,ass.vendor_site_id,ass.org_id) balance_brought_forward 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' &gc_reporting_entity &gc_supplier_name &gc_vend_type &gc_pay_group ), q_main as (select 'I' transaction_type, alc.displayed_field lookup_value, ai.invoice_type_lookup_code lookup_code, ai.invoice_num doc_number, ai.invoice_date doc_date, ai.payment_status_flag, ai.gl_date, ai.invoice_currency_code currency_code, ai.invoice_amount * nvl(ai.exchange_rate,1) accounted_amount, ai.invoice_amount entered_amount, 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, ai.vendor_id, ai.vendor_site_id, 'I' || ai.invoice_id id from ap_invoices ai, gl_periods gp, gl_ledgers gled, ap_lookup_codes alc where 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 &gc_unapproved_trx &gc_currency &gc_validate_inv &gc_org_id union all select 'P' transaction_type, alc.displayed_field lookup_value, ac.payment_method_lookup_code lookup_code, to_char(ac.check_number)||'/'||ai.invoice_num doc_number, ac.check_date doc_date, null payment_status_flag, aip.accounting_date gl_date, ac.currency_code currency_code, aip.amount * nvl(aip.exchange_rate,1) accounted_amount, aip.amount entered_amount, ac.description 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, ac.vendor_id, ac.vendor_site_id, 'P' || aip.invoice_payment_id id from ap_invoices ai, ap_invoice_payments aip, ap_checks ac, 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 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 &gc_pmt_accounted &gc_pmt_org_id &gc_pmt_currency ) -- -- 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, q.balance_bought_forward_debit, q.balance_bought_forward_credit, q.net_debit, q.net_credit, q.cumulative_debit, q.cumulative_credit, q.balance_bought_forward_credit - q.balance_bought_forward_debit balance_bought_forward_amount, q.net_credit - q.net_debit net_amount, q.cumulative_credit - q.cumulative_debit cumualtive_amount &document_columns from (select z.*, case z.record_type when 'Period Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row) - sum(nvl(z.debit,0)) over (partition by z.vendor_site_id,z.period_name) when 'Supplier Site Summary' then 0 when 'Operating Unit Summary' then 0 when 'Supplier Summary' then 0 when '&reporting_entity_col_name Summary' then 0 else null end balance_bought_forward_debit, case z.record_type when 'Period Summary' then sum(nvl(z.credit,0)) over (partition by 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_site_id) - sum(nvl(z.credit,0)) over (partition by z.vendor_site_id,z.period_name) when 'Supplier Site Summary' then sum(nvl(z.bbf,0)) over (partition by 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) when '&reporting_entity_col_name Summary' then sum(nvl(z.bbf,0)) over () else null end balance_bought_forward_credit, case z.record_type when 'Period Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_site_id,z.period_name) when 'Supplier Site Summary' then sum(nvl(z.debit,0)) over (partition by 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) when '&reporting_entity_col_name Summary' then sum(nvl(z.debit,0)) over () else null end net_debit, case z.record_type when 'Period Summary' then sum(nvl(z.credit,0)) over (partition by z.vendor_site_id,z.period_name) when 'Supplier Site Summary' then sum(nvl(z.credit,0)) over (partition by 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) when '&reporting_entity_col_name Summary' then sum(nvl(z.credit,0)) over () else null end net_credit, case z.record_type when 'Period Summary' then sum(nvl(z.debit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row) when 'Supplier Site Summary' then sum(nvl(z.debit,0)) over (partition by 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) when '&reporting_entity_col_name Summary' then sum(nvl(z.debit,0)) over () else null end cumulative_debit, case z.record_type when 'Period Summary' then sum(nvl(z.credit,0)) over (partition by 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_site_id) when 'Supplier Site Summary' then sum(nvl(z.credit,0)) over (partition by 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_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) when '&reporting_entity_col_name Summary' then sum(nvl(z.credit,0)) over () + sum(nvl(z.bbf,0)) over () else null end cumulative_credit 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 (-- dummy period summary record select distinct 'Period Summary' record_type, q_supplier.vendor_name vendor_name, q_supplier.vendor_number vendor_number, q_supplier.vendor_name_alt vendor_alternative_name, q_supplier.vat_registration_num vendor_tax_registration, q_supplier.organization_name operating_unit, q_supplier.vendor_site_code vendor_site_code, q_supplier.vendor_site_code_alt vendor_site_alternative_code, q_supplier.vendor_site_address vendor_site_address, q_main.gp_period_name period_name, null document_type, null document_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) original_amount, null currency_code, 1 sort_order, q_main.gp_start_date period_start_date, q_main.gp_period_num period_num, q_supplier.vendor_id, q_supplier.vendor_site_id, q_supplier.organization_id, to_number(null) bbf from q_supplier, q_main where q_main.vendor_id = q_supplier.vendor_id and q_main.vendor_site_id = q_supplier.vendor_site_id union all -- dummy supplier site summary record select distinct 'Supplier Site Summary' record_type, q_supplier.vendor_name vendor_name, q_supplier.vendor_number vendor_number, q_supplier.vendor_name_alt vendor_alternative_name, q_supplier.vat_registration_num vendor_tax_registration, q_supplier.organization_name operating_unit, q_supplier.vendor_site_code vendor_site_code, q_supplier.vendor_site_code_alt vendor_site_alternative_code, q_supplier.vendor_site_address vendor_site_address, null period_name, null document_type, null document_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) original_amount, null currency_code, 2 sort_order, to_date(nu |