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, TO_CHAR(ai.invoice_date, 'DD-MON-YYYY') doc_date, ai.payment_status_flag payment_status_flag, TO_CHAR(ai.gl_date, 'DD-MON-YYYY') 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, fnd_date.date_to_chardate(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, TO_CHAR(ac.check_date, 'DD-MON-YYYY') doc_date, NULL payment_status_flag, TO_CHAR(aip.accounting_date, 'DD-MON-YYYY') 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, 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, null Document_Date, null Document_Status, 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, null Document_Date, null Document_Status, 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(null) period_start_date, to_number(null) period_num, q_supplier.vendor_id, q_supplier.vendor_site_id, q_supplier.organization_id, q_supplier.balance_brought_forward bbf from q_supplier union all -- dummy operating unit summary record select distinct 'Operating Unit Summary' Record_Type, q_supplier.vendor_name Vendor_name, q_supplier.vendor_number Vendor_Number, q_supplier.vendor_name_alt Vendor_Alternative_Name, null Vendor_Tax_Registration, q_supplier.organization_name Operating_Unit, null Vendor_Site_Code, null Vendor_Site_Alternative_Code, null vendor_site_address, null Period_Name, null Document_Type, null Document_Number, null Document_Date, null Document_Status, null GL_Date, null Description, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 3 sort_order, to_date(null) period_start_date, to_number(null) period_num, q_supplier.vendor_id, to_number(null) vendor_site_id, q_supplier.organization_id, to_number(null) bbf from q_supplier where :p_reporting_level != '3000' -- don't show OU Summary when run by OU union all -- dummy supplier summary record select distinct 'Supplier Summary' Record_Type, q_supplier.vendor_name Vendor_name, q_supplier.vendor_number Vendor_Number, q_supplier.vendor_name_alt Vendor_Alternative_Name, null Vendor_Tax_Registration, null Operating_Unit, null Vendor_Site_Code, null Vendor_Site_Alternative_Code, null vendor_site_address, null Period_Name, null Document_Type, null Document_Number, null Document_Date, null Document_Status, null GL_Date, null Description, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 4 sort_order, to_date(null) period_start_date, to_number(null) period_num, q_supplier.vendor_id, to_number(null) vendor_site_id, to_number(null) organization_id, to_number(null) bbf from q_supplier union all -- dummy reporting entity summary record select distinct '&reporting_entity_col_name Summary' Record_Type, null Vendor_name, null Vendor_Number, null Vendor_Alternative_Name, null Vendor_Tax_Registration, null Operating_Unit, null Vendor_Site_Code, null Vendor_Site_Alternative_Code, null vendor_site_address, null Period_Name, null Document_Type, null Document_Number, null Document_Date, null Document_Status, null GL_Date, null Description, to_number(null) Debit, to_number(null) Credit, to_number(null) Original_Amount, null Currency_Code, 5 sort_order, to_date(null) period_start_date, to_number(null) period_num, to_number(null) vendor_id, to_number(null) vendor_site_id, to_number(null) organization_id, to_number(null) bbf from dual union all -- transactions select 'Transaction' 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, 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.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 GL_Date, q_main.description Description, case q_main.transaction_type when 'I' then to_number(null) else q_main.accounted_amount end Debit, case q_main.transaction_type when 'I' then q_main.accounted_amount else to_number(null) end Credit, q_main.entered_amount Original_Amount, q_main.currency_code 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 ) 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 y.vendor_id is null or nvl(y.supplier_closing_balance,0) != 0) ) z order by z.seq) q where ( (:p_summary_only = 'N' and q.record_type = 'Transaction') or (:p_summary_level = 'Period' and q.record_type not in ('Transaction')) or (:p_summary_level = 'Supplier Site' and q.record_type not in ('Period Summary','Transaction')) or (:p_summary_level = 'Operating Unit' and q.record_type not in ('Supplier Site Summary','Period Summary','Transaction')) or (:p_summary_level = 'Supplier' and q.record_type = 'Supplier Summary') or (:p_reporting_level = '3000' and q.record_type = 'Operating_Unit Summary') or (:p_reporting_level = '1000' and q.record_type = 'Ledger Summary') ) order by q.seq |