Supplier Ledger

https://aporaclepayables.blogspot.com/2017/11/supplier-ledger.html

-- in case of APN Num using gl_date as base date in parameter as there is no clearing date present for APN Num
-- in case of APV Num using clearing_date as base date in parameter


SELECT   1 sno
,        gcc.segment1 company
,        supp.segment1 supplier_num
,        TRIM(UPPER(supp.vendor_name))||'  Supp ID: '||supp.segment1 supplier_name
,        'APN' "Type"
,        aia.invoice_num inv_number
,        aia.gl_date gl_date
,        aia.doc_sequence_value doc_value
 ,     --  NULL check_number
(SELECT 
 to_char(acax.doc_sequence_value) doc_value
    FROM
     ap_invoice_payments_all aipax
,     ap_checks_all acax

   WHERE aia.invoice_id = aipax.invoice_id
         AND aipax.reversal_flag = 'N'
     AND aipax.check_id = acax.check_id
     AND acax.cleared_date IS NOT NULL
) check_number
,        aia.description "Description"
,        aia.invoice_currency_code ccy
,        aia.exchange_rate exch_rate
,        NULL debit_entered_amount
,        NULL debit_func_amount
,        aia.invoice_amount credit_entered_amount
,        (aia.invoice_amount * nvl (aia.exchange_rate, 1)) credit_func_amount
,        NULL invoice_id
,        aia.vendor_id
,        aia.org_id
    FROM ap_invoices_all aia
,        ap_suppliers supp
,        gl_code_combinations gcc
   WHERE aia.vendor_id = supp.vendor_id
     AND aia.accts_pay_code_combination_id = gcc.code_combination_id
   --  AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
     AND aia.org_id = nvl (:p_org_id, aia.org_id)
     -- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
     AND supp.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, supp.vendor_name) AND nvl(:cf_to_vendor_dsp, supp.vendor_name)
     AND aia.invoice_currency_code BETWEEN nvl (:p_from_curr, aia.invoice_currency_code) AND nvl (:p_to_curr, aia.invoice_currency_code)
     AND aia.gl_date BETWEEN nvl (:p_from_date, aia.gl_date) AND nvl (:p_to_date, aia.gl_date)
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT   2 sno
,        gcc.segment1 company
,        supp.segment1 supplier_num
,        TRIM(UPPER(supp.vendor_name))||'  Supp ID: '||supp.segment1 supplier_name
,        'APV' "Type"
,        NULL inv_number
,        aca.cleared_date gl_date
,        aca.doc_sequence_value doc_value
,        to_char (aca.check_number) check_number
,        aca.description "Description"
,        aca.currency_code ccy
,        aca.exchange_rate exch_rate
,        aia.invoice_amount debit_entered_amount
,        ((aia.invoice_amount * nvl (aia.exchange_rate, 1))) debit_func_amount
,        NULL credit_entered_amount
,        NULL credit_func_amount
,        aia.invoice_id
,        aia.vendor_id
,        aia.org_id
    FROM ap_invoices_all aia
,        ap_invoice_payments_all aipa
,        ap_checks_all aca
,        ap_suppliers supp
,        gl_code_combinations gcc
   WHERE aia.invoice_id = aipa.invoice_id
     AND aipa.check_id = aca.check_id
     AND aia.vendor_id = supp.vendor_id
     AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
     AND aipa.reversal_flag = 'N'
     AND aca.cleared_date IS NOT NULL
  --   AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) =  'APPROVED'
---------- Parameters ---------------------------------------------
     AND aia.org_id = nvl (:p_org_id, aia.org_id)
     -- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
     AND supp.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, supp.vendor_name) AND nvl(:cf_to_vendor_dsp, supp.vendor_name)
     AND aia.invoice_currency_code BETWEEN nvl (:p_from_curr, aia.invoice_currency_code) AND nvl (:p_to_curr, aia.invoice_currency_code)
     AND aca.cleared_date BETWEEN nvl (:p_from_date, aca.cleared_date) AND nvl (:p_to_date, aca.cleared_date)
------------------------------------------------------------------
ORDER BY supplier_name, gl_date, "Type", doc_value