Monday 20 July 2020

Creditor Ledger sql query in Oracle Fusion

Creditor Ledger sql query in Oracle Fusion

Hi Friends , we are going to discuss about the Creditor Ledger sql query in Oracle Fusion. Creditor ledger is also called the Supplier ledger in oracle fusion. Creditor ledger helps to find the supplier outstanding till date. In Creditor ledger , we can track each and every transaction related to supplier like Invoices , Payments, Refunds, Credit Memo , Debit Memo created in oracle fusion for the supplier. This sql query helps to develop the custom creditor ledger BIP report in Oracle Fusion. Using this creditor ledger query , we can extract the detail level transaction for the supplier in oracle fusion. We uses creditor ledger in oracle fusion to get the current outstanding for the supplier. Please find below the detail about Creditor Ledger sql query in Oracle Fusion.

Creditor Ledger sql query in Oracle Fusion
Creditor Ledger sql query in Oracle Fusion

5 Important table in Creditor Ledger sql query

1.ap_invoice_distributions_all 
2.ap_invoices_all
3.poz_supplier_sites_v
4.gl_code_combinations 
5.hz_parties

Detail Level of Creditor Ledger sql query in Oracle Fusion

Here below is the detail sql query to extract the creditor's ledger in Oracle Fusion.

SELECT ROWNUM, xx.sno, xx.vendor_id, xx.vendor_type, xx.inv_type,
       xx.invoice_num, xx.invoice_date, xx.description, xx.payment_number,
       xx.gl_date, xx.orderby, xx.dr_amount, xx.cr_amount,
       xx.vendor_site_code, xx.doc_type, xx.balance,
       SUM (xx.balance) OVER (ORDER BY xx.sno,
        ROWNUM,
        xx.gl_date,
        xx.vendor_id,
        xx.vendor_site_code,
        xx.invoice_num,
        xx.doc_type) running_bal,
       xx.vendor_num, xx.inv_status, xx.liability
  FROM (SELECT   2 sno, vttbl.vendor_id,
                 (SELECT DISTINCT vendor_type_lookup_code
                             FROM poz_suppliers_v ps
                            WHERE ps.vendor_id = vttbl.vendor_id) vendor_type,
                 vttbl.inv_type, vttbl.invoice_num,
                 TO_CHAR (TRUNC (vttbl.invoice_date),
                          'DD-MON-YYYY'
                         ) invoice_date,
                 vttbl.description, vttbl.payment_number,
                 TO_CHAR (TRUNC (vttbl.gl_date), 'DD-MON-YYYY') gl_date,
                 vttbl.dr_amount, vttbl.cr_amount, vttbl.vendor_site_code,
                 vttbl.doc_type,
                 NVL (vttbl.dr_amount, 0) - NVL (vttbl.cr_amount, 0) balance,
                 (SELECT DISTINCT segment1
                             FROM poz_suppliers_v ps
                            WHERE ps.vendor_id = vttbl.vendor_id) vendor_num,
                 TO_CHAR (TRUNC (vttbl.gl_date), 'YYYYMMDD') orderby,
                 vttbl.inv_status ,vttbl.liability
            FROM ( SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description, NULL payment_number,
                         apinv.gl_date,
                         CASE
                            WHEN apinv.cancelled_date IS NULL
                               THEN   apinv.invoice_amount
                                    * CASE
                                         WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                            THEN -1
                                         ELSE 0
                                      END
                            ELSE   apinv.cancelled_amount
                                 * CASE
                                      WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                         THEN -1
                                      ELSE 0
                                   END
                         END dr_amount,
                         CASE
                            WHEN apinv.cancelled_date IS NULL
                               THEN   apinv.invoice_amount
                                    * CASE
                                         WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                            THEN 0
                                         ELSE 1
                                      END
                            ELSE   apinv.cancelled_amount
                                 * CASE
                                      WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                         THEN 0
                                      ELSE 1
                                   END
                         END cr_amount,
                         supsites.vendor_site_code, 'Invoice' doc_type,
                         1 disorder,  
                         decode(ap_invoices_pkg.get_approval_status  (apinv.invoice_id,
                             apinv.invoice_amount,
                             apinv.payment_status_flag,
                             apinv.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status, gcc.segment4 liability
                    FROM ap_invoices_all apinv, poz_supplier_sites_v supsites,gl_code_combinations gcc
                   WHERE TO_CHAR (TRUNC (apinv.gl_date), 'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (apinv.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (apinv.gl_date)
                                        )
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                     AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE),VENDOR_SITE_CODE))
                    and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id 
union all 
SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description, NULL payment_number,
                         apinv.gl_date,
                         CASE
                            WHEN apinv.cancelled_date IS NULL
                               THEN   apinv.invoice_amount
                                    * CASE
                                         WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                            THEN 0
                                         ELSE 1
                                      END
                            ELSE   apinv.cancelled_amount
                                 * CASE
                                      WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                         THEN 0
                                      ELSE 1
                                   END
                         END dr_amount,
                         CASE
                            WHEN apinv.cancelled_date IS NULL
                               THEN   apinv.invoice_amount
                                    * CASE
                                         WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                            THEN -1
                                         ELSE 0
                                      END
                            ELSE   apinv.cancelled_amount
                                 * CASE
                                      WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                         THEN -1
                                      ELSE 0
                                   END
                         END cr_amount,
                         supsites.vendor_site_code, 'Invoice' doc_type,
                         8 disorder,  
                         decode(ap_invoices_pkg.get_approval_status  (apinv.invoice_id,
                             apinv.invoice_amount,
                             apinv.payment_status_flag,
                             apinv.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status, gcc.segment4 liability
                    FROM ap_invoices_all apinv, poz_supplier_sites_v supsites,gl_code_combinations gcc
                   WHERE TO_CHAR (TRUNC (apinv.gl_date), 'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (apinv.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (apinv.gl_date)
                                        )
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     AND apinv.invoice_type_lookup_code='PREPAYMENT'
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                   AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                    and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id                                                                      
                  UNION ALL
                  SELECT apinv.vendor_id,  decode(vtpymt.PAYMENT_TYPE_FLAG,'R','Refund','Payment') inv_type, apinv.invoice_num,-- APINV.INVOICE_ID,
                         apinv.invoice_date, apinv.description,
                         TO_CHAR (vtpymt.payment_number) payment_number,
                         vtpymt.gl_date,
                         CASE
                            WHEN vtpymt.amount > 0
                               THEN vtpymt.amount
                            ELSE 0
                         END dr_amount,
                         CASE
                            WHEN vtpymt.amount < 0
                               THEN vtpymt.amount * -1
                            ELSE 0
                         END cr_amount,
                         supsites.vendor_site_code, decode(vtpymt.PAYMENT_TYPE_FLAG,'R','Refund','Payment') doc_type,
                         2 disorder, vtpymt.status_lookup_code inv_status,gcc.segment4 liability
                    FROM ap_invoices_all apinv,gl_code_combinations gcc,
                         poz_supplier_sites_v supsites,
                         (SELECT apchk.check_number payment_number,
                                 apchk.status_lookup_code,apchk.PAYMENT_TYPE_FLAG,
                                 vt_apinvpymt.invoice_id, vt_apinvpymt.amount,
                                 vt_apinvpymt.gl_date
                            FROM ap_checks_all apchk,
                                 (SELECT   check_id, invoice_id,
                                           MAX (accounting_date) gl_date,
                                           SUM (amount) amount
                                      FROM ap_invoice_payments_all apinvpymt
                                     WHERE reversal_inv_pmt_id IS NULL
                                  GROUP BY check_id, invoice_id) vt_apinvpymt
                           WHERE apchk.check_id = vt_apinvpymt.check_id                           
                             AND apchk.vendor_id =
                                    (SELECT ps.vendor_id
                                       FROM poz_suppliers ps, hz_parties hp
                                      WHERE ps.party_id = hp.party_id
                                        AND hp.party_name = :p_vendor_name)
                                           AND apchk.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                             AND TO_CHAR (TRUNC (vt_apinvpymt.gl_date),
                                          'YYYY-MM-DD'
                                         )
                                    BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (vt_apinvpymt.gl_date)
                                                )
                                        AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (vt_apinvpymt.gl_date)
                                                )) vtpymt
                   WHERE apinv.invoice_id = vtpymt.invoice_id
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                  UNION ALL
                  SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description, apinv2.invoice_num prepay_number,
                         vtprepay_apply.gl_date,
                         vtprepay_apply.amount dr_amount, 0 cr_amount,
                         supsites.vendor_site_code, 'PrePay Apply' doc_type,
                         3 disorder,
                          decode(ap_invoices_pkg.get_approval_status  (apinv2.invoice_id,
                             apinv2.invoice_amount,
                             apinv2.payment_status_flag,
                             apinv2.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM (SELECT apinv_dist.invoice_id,
                                 apinv_dist.accounting_date gl_date,
                                 apinv_dist.amount * -1 amount,
                                 apinv_dist.prepay_distribution_id
                            FROM ap_invoice_distributions_all apinv_dist
                           WHERE apinv_dist.line_type_lookup_code = 'PREPAY'
                             AND apinv_dist.parent_reversal_id IS NULL) vtprepay_apply,
                         ap_invoices_all apinv,
                         poz_supplier_sites_v supsites,
                         ap_invoice_distributions_all apinv_dist,
                         ap_invoices_all apinv2,gl_code_combinations gcc
                   WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                     AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                     AND apinv_dist.invoice_id = apinv2.invoice_id
                     AND supsites.vendor_site_id = apinv.vendor_site_id
                     AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                  'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                   AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                    and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id 
                   --and  gcc.segment4=nvl(:P_liability,gcc.segment4)                              
                  UNION ALL
                  SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description, apinv2.invoice_num prepay_number,
                         vtprepay_apply.gl_date, 0 dr_amount,
                         vtprepay_apply.amount cr_amount,
                         supsites.vendor_site_code, 'PrePay UnApply' doc_type,
                         4 disorder --,DECODE(apinv.cancelled_date,null,NULL,'Cancelled')
                         ,  decode(ap_invoices_pkg.get_approval_status  (apinv2.invoice_id,
                             apinv2.invoice_amount,
                             apinv2.payment_status_flag,
                             apinv2.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM (SELECT apinv_dist.invoice_id,
                                 apinv_dist.accounting_date gl_date,
                                 apinv_dist.amount,
                                 apinv_dist.prepay_distribution_id
                            FROM ap_invoice_distributions_all apinv_dist
                           WHERE apinv_dist.line_type_lookup_code = 'PREPAY'
                             AND apinv_dist.parent_reversal_id IS NOT NULL) vtprepay_apply,
                         ap_invoices_all apinv,
                         poz_supplier_sites_v supsites,
                         ap_invoice_distributions_all apinv_dist,
                         ap_invoices_all apinv2,gl_code_combinations gcc
                   WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                     AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                     AND apinv_dist.invoice_id = apinv2.invoice_id
                     AND supsites.vendor_site_id = apinv.vendor_site_id
                     AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                  'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                   AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                     and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id 
UNION ALL
                  SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         --apinv.invoice_num
                         apinv2.invoice_num, apinv.invoice_date,
                         apinv.description, null prepay_number,
                         vtprepay_apply.gl_date,
                         0 dr_amount, vtprepay_apply.amount cr_amount,
                         supsites.vendor_site_code, 'PrePay Apply' doc_type,
                         9 disorder,
                          decode(ap_invoices_pkg.get_approval_status  (apinv2.invoice_id,
                             apinv2.invoice_amount,
                             apinv2.payment_status_flag,
                             apinv2.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM (SELECT apinv_dist.invoice_id,
                                 apinv_dist.accounting_date gl_date,
                                 apinv_dist.amount * -1 amount,
                                 apinv_dist.prepay_distribution_id
                            FROM ap_invoice_distributions_all apinv_dist
                           WHERE apinv_dist.line_type_lookup_code = 'PREPAY'
                             AND apinv_dist.parent_reversal_id IS NULL) vtprepay_apply,
                         ap_invoices_all apinv,
                         poz_supplier_sites_v supsites,
                         ap_invoice_distributions_all apinv_dist,
                         ap_invoices_all apinv2,gl_code_combinations gcc
                   WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                     AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                     AND apinv_dist.invoice_id = apinv2.invoice_id
                     AND supsites.vendor_site_id = apinv.vendor_site_id
                     AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                  'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                   AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                    and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id 
                 --  and  gcc.segment4=nvl(:P_liability,gcc.segment4)                              
                  UNION ALL
                  SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                        -- apinv.invoice_num, 
                        apinv2.invoice_num, apinv.invoice_date,
                         apinv.description, null prepay_number,
                         vtprepay_apply.gl_date, vtprepay_apply.amount dr_amount,
                         0 cr_amount,
                         supsites.vendor_site_code, 'PrePay UnApply' doc_type,
                         10 disorder --,DECODE(apinv.cancelled_date,null,NULL,'Cancelled')
                         ,  decode(ap_invoices_pkg.get_approval_status  (apinv2.invoice_id,
                             apinv2.invoice_amount,
                             apinv2.payment_status_flag,
                             apinv2.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM (SELECT apinv_dist.invoice_id,
                                 apinv_dist.accounting_date gl_date,
                                 apinv_dist.amount,
                                 apinv_dist.prepay_distribution_id
                            FROM ap_invoice_distributions_all apinv_dist
                           WHERE apinv_dist.line_type_lookup_code = 'PREPAY'
                             AND apinv_dist.parent_reversal_id IS NOT NULL) vtprepay_apply,
                         ap_invoices_all apinv,
                         poz_supplier_sites_v supsites,
                         ap_invoice_distributions_all apinv_dist,
                         ap_invoices_all apinv2,gl_code_combinations gcc
                   WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                     AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                     AND apinv_dist.invoice_id = apinv2.invoice_id
                     AND supsites.vendor_site_id = apinv.vendor_site_id
                     AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                  'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (vtprepay_apply.gl_date)
                                        )
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                   AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                     and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id                                                 
                  UNION ALL
                  SELECT apinv.vendor_id,
                         'Payment' inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description,
                         TO_CHAR (vtpymt.payment_number) payment_number,
                         vtpymt.gl_date,
                         CASE
                            WHEN vtpymt.amount > 0
                               THEN vtpymt.amount
                            ELSE 0
                         END dr_amount,
                         CASE
                            WHEN vtpymt.amount < 0
                               THEN vtpymt.amount * -1
                            ELSE 0
                         END cr_amount,
                         supsites.vendor_site_code,
                         'Payment Cancelled' doc_type, 5 disorder,
                         -- ,DECODE(apinv.cancelled_date,null,NULL,'Cancelled') inv_status
                         vtpymt.status_lookup_code inv_status,gcc.segment4 liability
                    FROM ap_invoices_all apinv, gl_code_combinations gcc,
                         poz_supplier_sites_v supsites,
                         (SELECT apchk.check_number payment_number,
                                 apchk.status_lookup_code,
                                 vt_apinvpymt.invoice_id, vt_apinvpymt.amount,
                                 vt_apinvpymt.gl_date
                            FROM ap_checks_all apchk,
                                 (SELECT   check_id, invoice_id,
                                           MAX (accounting_date) gl_date,
                                           SUM (amount) amount
                                      FROM ap_invoice_payments_all apinvpymt
                                     WHERE reversal_inv_pmt_id IS NOT NULL
                                  GROUP BY check_id, invoice_id) vt_apinvpymt
                           WHERE apchk.check_id = vt_apinvpymt.check_id
                             AND apchk.vendor_id =
                                    (SELECT ps.vendor_id
                                       FROM poz_suppliers ps, hz_parties hp
                                      WHERE ps.party_id = hp.party_id
                                        AND hp.party_name = :p_vendor_name)
                                           AND apchk.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                             AND TO_CHAR (TRUNC (vt_apinvpymt.gl_date),
                                          'YYYY-MM-DD'
                                         )
                                    BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (vt_apinvpymt.gl_date)
                                                )
                                        AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (vt_apinvpymt.gl_date)
                                                )) vtpymt
                   WHERE apinv.invoice_id = vtpymt.invoice_id
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                  UNION ALL
                  SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description, NULL payment_number,
                         CASE
                            WHEN apinv_cancel.gl_date IS NOT NULL
                               THEN apinv_cancel.gl_date
                            ELSE apinv.cancelled_date
                         END gl_date,
                         (  apinv.cancelled_amount
                          * CASE
                               WHEN apinv.invoice_type_lookup_code NOT IN
                                                          ('CREDIT', 'DEBIT')
                                  THEN 1
                               ELSE 0
                            END
                         ) dr_amount,
                         (  apinv.cancelled_amount
                          * CASE
                               WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                  THEN -1
                               ELSE 0
                            END
                         ) cr_amount,
                         supsites.vendor_site_code,
                         'Invoice Cancelled' doc_type, 6 disorder
                                ,  decode(ap_invoices_pkg.get_approval_status  (apinv.invoice_id,
                             apinv.invoice_amount,
                             apinv.payment_status_flag,
                             apinv.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM ap_invoices_all apinv,
                         (SELECT   invoice_id, MAX (accounting_date) gl_date
                              FROM ap_invoice_distributions_all
                             WHERE cancellation_flag = 'Y'
                          GROUP BY invoice_id) apinv_cancel,
                         poz_supplier_sites_v supsites,gl_code_combinations gcc
                   WHERE apinv.invoice_id = apinv_cancel.invoice_id(+)
                     AND 1 =
                            CASE
                               WHEN apinv_cancel.gl_date IS NOT NULL
                               AND TO_CHAR (TRUNC (apinv_cancel.gl_date),
                                            'YYYY-MM-DD'
                                           )
                                      BETWEEN NVL
                                                (TO_CHAR (TRUNC (:p_from_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (apinv_cancel.gl_date)
                                                )
                                          AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                            'YYYY-MM-DD'
                                                           ),
                                                   TRUNC (apinv_cancel.gl_date)
                                                  )
                                  THEN 1
                               WHEN apinv_cancel.gl_date IS NULL
                               AND TO_CHAR (TRUNC (apinv.cancelled_date),
                                            'YYYY-MM-DD'
                                           )
                                      BETWEEN NVL
                                                (TO_CHAR (TRUNC (:p_from_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (apinv.cancelled_date)
                                                )
                                          AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                            'YYYY-MM-DD'
                                                           ),
                                                   TRUNC (apinv.cancelled_date)
                                                  )
                                  THEN 1
                               ELSE 0
                            END
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     AND apinv.cancelled_date IS NOT NULL
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                    and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id  
UNION ALL
   SELECT apinv.vendor_id,
                         apinv.invoice_type_lookup_code inv_type,
                         apinv.invoice_num, apinv.invoice_date,
                         apinv.description, NULL payment_number,
                         CASE
                            WHEN apinv_cancel.gl_date IS NOT NULL
                               THEN apinv_cancel.gl_date
                            ELSE apinv.cancelled_date
                         END gl_date,
                        (  apinv.cancelled_amount
                          * CASE
                               WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                  THEN -1
                               ELSE 0
                            END
                         )dr_amount,
                          (  apinv.cancelled_amount
                          * CASE
                               WHEN apinv.invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT')
                                  THEN 1
                               ELSE 0
                            END
                         )  cr_amount,
                         supsites.vendor_site_code,
                         'Invoice Cancelled' doc_type, 11 disorder
                                ,  decode(ap_invoices_pkg.get_approval_status  (apinv.invoice_id,
                             apinv.invoice_amount,
                             apinv.payment_status_flag,
                             apinv.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM ap_invoices_all apinv,
                         (SELECT   invoice_id, MAX (accounting_date) gl_date
                              FROM ap_invoice_distributions_all
                             WHERE cancellation_flag = 'Y'
                          GROUP BY invoice_id) apinv_cancel,
                         poz_supplier_sites_v supsites,gl_code_combinations gcc
                   WHERE apinv.invoice_id = apinv_cancel.invoice_id(+)
                     AND 1 =
                            CASE
                               WHEN apinv_cancel.gl_date IS NOT NULL
                               AND TO_CHAR (TRUNC (apinv_cancel.gl_date),
                                            'YYYY-MM-DD'
                                           )
                                      BETWEEN NVL
                                                (TO_CHAR (TRUNC (:p_from_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (apinv_cancel.gl_date)
                                                )
                                          AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                            'YYYY-MM-DD'
                                                           ),
                                                   TRUNC (apinv_cancel.gl_date)
                                                  )
                                  THEN 1
                               WHEN apinv_cancel.gl_date IS NULL
                               AND TO_CHAR (TRUNC (apinv.cancelled_date),
                                            'YYYY-MM-DD'
                                           )
                                      BETWEEN NVL
                                                (TO_CHAR (TRUNC (:p_from_date),
                                                          'YYYY-MM-DD'
                                                         ),
                                                 TRUNC (apinv.cancelled_date)
                                                )
                                          AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                            'YYYY-MM-DD'
                                                           ),
                                                   TRUNC (apinv.cancelled_date)
                                                  )
                                  THEN 1
                               ELSE 0
                            END
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     AND apinv.cancelled_date IS NOT NULL
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                 AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                    and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                    AND apinv.invoice_type_lookup_code='PREPAYMENT'                                                 
                  UNION ALL
                  SELECT apinv.vendor_id, 'TDS' inv_type, apinv.invoice_num,
                         apinv.invoice_date, apinv.description,
                         NULL payment_number, apinv.gl_date, 
                         NVL
                            ((SELECT DECODE (SIGN (SUM (ail.amount)),
                                             -1, NVL (SUM (ABS (ail.amount)),
                                                      0
                                                     )
                                            ) amt
                                FROM ap_invoice_lines_all ail
                               WHERE ail.invoice_id = apinv.invoice_id
                                 AND ail.line_type_lookup_code = 'AWT'
                                 AND ail.tax_regime_code in ( 'TDS_REGIME','TDS_GLOBA')),
                             0
                            ) dr_amount,
                         NVL
                            ((SELECT DECODE (SIGN (SUM (ail.amount)),
                                             1, NVL (SUM (ABS (ail.amount)),
                                                     0)
                                            ) amt
                                FROM ap_invoice_lines_all ail
                               WHERE ail.invoice_id = apinv.invoice_id
                                 AND ail.line_type_lookup_code = 'AWT'
                                 AND ail.tax_regime_code in ( 'TDS_REGIME','TDS_GLOBA')),
                             0
                            ) cr_amount,
                         supsites.vendor_site_code, 'TDS' doc_type,
                         7 disorder,
                             decode(ap_invoices_pkg.get_approval_status  (apinv.invoice_id,
                             apinv.invoice_amount,
                             apinv.payment_status_flag,
                             apinv.invoice_type_lookup_code  ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL  ) inv_status ,gcc.segment4 liability
                    FROM ap_invoices_all apinv, poz_supplier_sites_v supsites,gl_code_combinations gcc
                   WHERE TO_CHAR (TRUNC (apinv.gl_date), 'YYYY-MM-DD')
                            BETWEEN NVL (TO_CHAR (TRUNC (:p_from_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (apinv.gl_date)
                                        )
                                AND NVL (TO_CHAR (TRUNC (:p_to_date),
                                                  'YYYY-MM-DD'
                                                 ),
                                         TRUNC (apinv.gl_date)
                                        )
                     AND apinv.vendor_site_id = supsites.vendor_site_id
                     AND apinv.vendor_id =
                            (SELECT ps.vendor_id
                               FROM poz_suppliers ps, hz_parties hp
                              WHERE ps.party_id = hp.party_id
                                AND hp.party_name = :p_vendor_name)
                                 AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                     and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                      ) vttbl 
           WHERE NVL (vttbl.dr_amount, 0) - NVL (vttbl.cr_amount, 0) <> 0
        UNION ALL
/* ****         opening           **** */ 
        SELECT   1 sno, opnbal.vendor_id,
                 (SELECT DISTINCT vendor_type_lookup_code
                             FROM poz_suppliers_v ps
                            WHERE ps.vendor_id = opnbal.vendor_id)
                                                                  vendor_type,
                 'OPENING' inv_type, NULL invoice_num, NULL invoice_date,
                 NULL description, NULL payment_number, NULL gl_date,
                 (NVL (opnbal.dr_amt, 0) - NVL (opnbal.cr_amt, 0)
                 ) opn_amount, NULL cr_amount, NULL vendor_site_code,
                 NULL doc_type,
                 (NVL (opnbal.dr_amt, 0) - NVL (opnbal.cr_amt, 0)
                 ) opn_balance, NULL vendor_num, NULL orderby,
                 NULL inv_status, null liability
            FROM (SELECT   vttbl.vendor_id,
                           SUM (NVL (vttbl.dr_amount, 0)) dr_amt,
                           SUM (NVL (vttbl.cr_amount, 0)) cr_amt
                      FROM (SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num, NULL payment_number,
                                   apinv.gl_date,
                                   CASE
                                      WHEN apinv.cancelled_date IS NULL
                                         THEN   apinv.invoice_amount
                                              * CASE
                                                   WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                      THEN -1
                                                   ELSE 0
                                                END
                                      ELSE   apinv.cancelled_amount
                                           * CASE
                                                WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                   THEN -1
                                                ELSE 0
                                             END
                                   END dr_amount,
                                   CASE
                                      WHEN apinv.cancelled_date IS NULL
                                         THEN   apinv.invoice_amount
                                              * CASE
                                                   WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                      THEN 0
                                                   ELSE 1
                                                END
                                      ELSE   apinv.cancelled_amount
                                           * CASE
                                                WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                   THEN 0
                                                ELSE 1
                                             END
                                   END cr_amount,
                                   supsites.vendor_site_code,
                                   'Invoice' doc_type, 1 disorder
                              FROM ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,gl_code_combinations gcc
                             WHERE TO_CHAR (TRUNC (apinv.gl_date),
                                            'YYYY-MM-DD'
                                           ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),
                                                    'YYYY-MM-DD'
                                                   ),
                                           TRUNC (apinv.gl_date)
                                          )
                               AND apinv.vendor_site_id =
                                                       supsites.vendor_site_id                          
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                     and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                    -- and  gcc.segment4=nvl(:P_liability,gcc.segment4)
UNION ALL
SELECT apinv.vendor_id, apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num, NULL payment_number,
                                   apinv.gl_date,
                                   CASE
                                      WHEN apinv.cancelled_date IS NULL
                                         THEN   apinv.invoice_amount
                                              * CASE
                                                   WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                      THEN 0
                                                   ELSE 1
                                                END
                                      ELSE   apinv.cancelled_amount
                                           * CASE
                                                WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                   THEN 0
                                                ELSE 1
                                             END
                                   END dr_amount,
                                    CASE
                                      WHEN apinv.cancelled_date IS NULL
                                         THEN   apinv.invoice_amount
                                              * CASE
                                                   WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                      THEN -1
                                                   ELSE 0
                                                END
                                      ELSE   apinv.cancelled_amount
                                           * CASE
                                                WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                                   THEN -1
                                                ELSE 0
                                             END
                                   END cr_amount,
                                   supsites.vendor_site_code,
                                   'Invoice' doc_type, 8 disorder
                              FROM ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,gl_code_combinations gcc
                             WHERE TO_CHAR (TRUNC (apinv.gl_date),  'YYYY-MM-DD' ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),  'YYYY-MM-DD' ), TRUNC (apinv.gl_date) )
                               AND apinv.vendor_site_id =  supsites.vendor_site_id
                               AND  apinv.invoice_type_lookup_code='PREPAYMENT'
                               AND apinv.vendor_id = (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                     and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id                  
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   TO_CHAR
                                        (vtpymt.payment_number)
                                                               payment_number,
                                   vtpymt.gl_date,
                                   CASE
                                      WHEN vtpymt.amount > 0
                                         THEN vtpymt.amount
                                      ELSE 0
                                   END dr_amount,
                                   CASE
                                      WHEN vtpymt.amount < 0
                                         THEN vtpymt.amount * -1
                                      ELSE 0
                                   END cr_amount,
                                   supsites.vendor_site_code,
                                   'Payment' doc_type, 2 disorder
                              FROM ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   (SELECT apchk.check_number payment_number,
                                           vt_apinvpymt.invoice_id,
                                           vt_apinvpymt.amount,
                                           vt_apinvpymt.gl_date
                                      FROM ap_checks_all apchk,
                                           (SELECT   check_id, invoice_id,
                                                     MAX
                                                        (accounting_date
                                                        ) gl_date,
                                                     SUM (amount) amount
                                                FROM ap_invoice_payments_all apinvpymt
                                               WHERE reversal_inv_pmt_id IS NULL
                                            GROUP BY check_id, invoice_id) vt_apinvpymt
                                     WHERE apchk.check_id =
                                                         vt_apinvpymt.check_id
                                       AND apchk.vendor_id =
                                              (SELECT ps.vendor_id
                                                 FROM poz_suppliers ps,
                                                      hz_parties hp
                                                WHERE ps.party_id =
                                                                   hp.party_id
                                                  AND hp.party_name =
                                                                :p_vendor_name)
                                                                 AND apchk.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                                       AND TO_CHAR
                                                 (TRUNC (vt_apinvpymt.gl_date),
                                                  'YYYY-MM-DD'
                                                 ) <
                                              NVL
                                                 (TO_CHAR (TRUNC (:p_from_date),
                                                           'YYYY-MM-DD'
                                                          ),
                                                  TRUNC (vt_apinvpymt.gl_date)
                                                 )) vtpymt,gl_code_combinations gcc
                             WHERE apinv.invoice_id = vtpymt.invoice_id
                               AND apinv.vendor_site_id =   supsites.vendor_site_id
                        and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id                                                       
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   apinv2.invoice_num prepay_number,
                                   vtprepay_apply.gl_date,
                                   vtprepay_apply.amount dr_amount,
                                   0 cr_amount, supsites.vendor_site_code,
                                   'PrePay Apply' doc_type, 3 disorder
                              FROM (SELECT apinv_dist.invoice_id,
                                           apinv_dist.accounting_date gl_date,
                                           apinv_dist.amount * -1 amount,
                                           apinv_dist.prepay_distribution_id
                                      FROM ap_invoice_distributions_all apinv_dist
                                     WHERE apinv_dist.line_type_lookup_code =
                                                                      'PREPAY'
                                       AND apinv_dist.parent_reversal_id IS NULL) vtprepay_apply,
                                   ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   ap_invoice_distributions_all apinv_dist,
                                   ap_invoices_all apinv2,gl_code_combinations gcc
                             WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                        --     AND apinv.ORG_ID = NVL((SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS WHERE NAME = :P_ORG_ID),apinv.ORG_ID)
 -- AND apinv.ORG_ID IN (SELECT DISTINCT ORG_ID FROM FUN_USER_ROLE_DATA_ASGNMNTS WHERE USER_GUID = FND_GLOBAL.USER_GUID)
                               AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                               AND apinv_dist.invoice_id = apinv2.invoice_id
                               AND supsites.vendor_site_id = apinv.vendor_site_id
                               AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                            'YYYY-MM-DD'
                                           ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),
                                                    'YYYY-MM-DD'
                                                   ),
                                           TRUNC (vtprepay_apply.gl_date)
                                          )
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                     --and  gcc.segment4=nvl(:P_liability,gcc.segment4)                                          
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   apinv2.invoice_num prepay_number,
                                   vtprepay_apply.gl_date, 0 dr_amount,
                                   vtprepay_apply.amount cr_amount,
                                   supsites.vendor_site_code,
                                   'PrePay UnApply' doc_type, 4 disorder
                              FROM (SELECT apinv_dist.invoice_id,
                                           apinv_dist.accounting_date gl_date,
                                           apinv_dist.amount,
                                           apinv_dist.prepay_distribution_id
                                      FROM ap_invoice_distributions_all apinv_dist
                                     WHERE apinv_dist.line_type_lookup_code =
                                                                      'PREPAY'
                                       AND apinv_dist.parent_reversal_id IS NOT NULL) vtprepay_apply,
                                   ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   ap_invoice_distributions_all apinv_dist,
                                   ap_invoices_all apinv2,gl_code_combinations gcc
                             WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                               AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                               AND apinv_dist.invoice_id = apinv2.invoice_id
                               AND supsites.vendor_site_id =  apinv.vendor_site_id
                               AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                            'YYYY-MM-DD'
                                           ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),
                                                    'YYYY-MM-DD'
                                                   ),
                                           TRUNC (vtprepay_apply.gl_date)
                                          )
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
UNION ALL                     
 SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   apinv2.invoice_num prepay_number,
                                   vtprepay_apply.gl_date,
                                   0 dr_amount,
                                   vtprepay_apply.amount cr_amount, supsites.vendor_site_code,
                                   'PrePay Apply' doc_type, 9 disorder
                              FROM (SELECT apinv_dist.invoice_id,
                                           apinv_dist.accounting_date gl_date,
                                           apinv_dist.amount * -1 amount,
                                           apinv_dist.prepay_distribution_id
                                      FROM ap_invoice_distributions_all apinv_dist
                                     WHERE apinv_dist.line_type_lookup_code =
                                                                      'PREPAY'
                                       AND apinv_dist.parent_reversal_id IS NULL) vtprepay_apply,
                                   ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   ap_invoice_distributions_all apinv_dist,
                                   ap_invoices_all apinv2,gl_code_combinations gcc
                             WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                               AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                               AND apinv_dist.invoice_id = apinv2.invoice_id
                               AND supsites.vendor_site_id = apinv.vendor_site_id
                               AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                            'YYYY-MM-DD'
                                           ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),
                                                    'YYYY-MM-DD'
                                                   ),
                                           TRUNC (vtprepay_apply.gl_date)
                                          )
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id                                      
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   apinv2.invoice_num prepay_number,
                                   vtprepay_apply.gl_date, vtprepay_apply.amount dr_amount,
                                   0 cr_amount,
                                   supsites.vendor_site_code,
                                   'PrePay UnApply' doc_type, 10 disorder
                              FROM (SELECT apinv_dist.invoice_id,
                                           apinv_dist.accounting_date gl_date,
                                           apinv_dist.amount,
                                           apinv_dist.prepay_distribution_id
                                      FROM ap_invoice_distributions_all apinv_dist
                                     WHERE apinv_dist.line_type_lookup_code =
                                                                      'PREPAY'
                                       AND apinv_dist.parent_reversal_id IS NOT NULL) vtprepay_apply,
                                   ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   ap_invoice_distributions_all apinv_dist,
                                   ap_invoices_all apinv2,gl_code_combinations gcc
                             WHERE vtprepay_apply.invoice_id = apinv.invoice_id
                               AND vtprepay_apply.prepay_distribution_id =
                                            apinv_dist.invoice_distribution_id
                               AND apinv_dist.invoice_id = apinv2.invoice_id
                               AND supsites.vendor_site_id =  apinv.vendor_site_id
                               AND TO_CHAR (TRUNC (vtprepay_apply.gl_date),
                                            'YYYY-MM-DD'
                                           ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),
                                                    'YYYY-MM-DD'
                                                   ),
                                           TRUNC (vtprepay_apply.gl_date)
                                          )
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                              and apinv2.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                             -- and  gcc.segment4=nvl(:P_liability,gcc.segment4)                                                                
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   TO_CHAR
                                        (vtpymt.payment_number)
                                                               payment_number,
                                   vtpymt.gl_date,
                                   CASE
                                      WHEN vtpymt.amount > 0
                                         THEN vtpymt.amount
                                      ELSE 0
                                   END dr_amount,
                                   CASE
                                      WHEN vtpymt.amount < 0
                                         THEN vtpymt.amount * -1
                                      ELSE 0
                                   END cr_amount,
                                   supsites.vendor_site_code,
                                   'Payment Cancelled' doc_type, 5 disorder
                              FROM ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   (SELECT apchk.check_number payment_number,
                                           vt_apinvpymt.invoice_id,
                                           vt_apinvpymt.amount,
                                           vt_apinvpymt.gl_date
                                      FROM ap_checks_all apchk,
                                           (SELECT   check_id, invoice_id,
                                                     MAX
                                                        (accounting_date
                                                        ) gl_date,
                                                     SUM (amount) amount
                                                FROM ap_invoice_payments_all apinvpymt
                                               WHERE reversal_inv_pmt_id IS NOT NULL
                                            GROUP BY check_id, invoice_id) vt_apinvpymt
                                     WHERE apchk.check_id =
                                                         vt_apinvpymt.check_id
                                       AND apchk.vendor_id =
                                              (SELECT ps.vendor_id
                                                 FROM poz_suppliers ps,
                                                      hz_parties hp
                                                WHERE ps.party_id =
                                                                   hp.party_id
                                                  AND hp.party_name =
                                                                :p_vendor_name)
                                                                 AND apchk.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                                       AND TO_CHAR
                                                 (TRUNC (vt_apinvpymt.gl_date),
                                                  'YYYY-MM-DD'
                                                 ) <
                                              NVL
                                                 (TO_CHAR (TRUNC (:p_from_date),
                                                           'YYYY-MM-DD'
                                                          ),
                                                  TRUNC (vt_apinvpymt.gl_date)
                                                 )) vtpymt,gl_code_combinations gcc
                             WHERE apinv.invoice_id = vtpymt.invoice_id
                               AND apinv.vendor_site_id = supsites.vendor_site_id
                               and apinv.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num,
                                   TO_CHAR (vtpymt.payment_number)  payment_number,
                                   vtpymt.gl_date,
                                    CASE
                                      WHEN vtpymt.amount < 0
                                         THEN vtpymt.amount * -1
                                      ELSE 0
                                   END dr_amount,
                                   CASE
                                      WHEN vtpymt.amount > 0
                                         THEN vtpymt.amount
                                      ELSE 0
                                   END cr_amount,
                                   supsites.vendor_site_code,
                                   'Payment Cancelled' doc_type, 11 disorder
                              FROM ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,
                                   (SELECT apchk.check_number payment_number,
                                           vt_apinvpymt.invoice_id,
                                           vt_apinvpymt.amount,
                                           vt_apinvpymt.gl_date
                                      FROM ap_checks_all apchk,
                                           (SELECT   check_id, invoice_id,
                                                     MAX (accounting_date  ) gl_date,
                                                     SUM (amount) amount
                                                FROM ap_invoice_payments_all apinvpymt
                                               WHERE reversal_inv_pmt_id IS NOT NULL
                                            GROUP BY check_id, invoice_id) vt_apinvpymt
                                     WHERE apchk.check_id = vt_apinvpymt.check_id
                                       AND apchk.vendor_id =  (SELECT ps.vendor_id
                                                 FROM poz_suppliers ps,
                                                      hz_parties hp
                                                WHERE ps.party_id =  hp.party_id
                                                  AND hp.party_name = :p_vendor_name)
                                                   AND apchk.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                                       AND TO_CHAR  (TRUNC (vt_apinvpymt.gl_date),
                                                  'YYYY-MM-DD'  ) <
                                              NVL (TO_CHAR (TRUNC (:p_from_date), 'YYYY-MM-DD'),
                                                  TRUNC (vt_apinvpymt.gl_date)
                                                 )) vtpymt,gl_code_combinations gcc
                             WHERE apinv.invoice_id = vtpymt.invoice_id
                        --     AND apinv.ORG_ID = NVL((SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS WHERE NAME = :P_ORG_ID),apinv.ORG_ID)
 -- AND apinv.ORG_ID IN (SELECT DISTINCT ORG_ID FROM FUN_USER_ROLE_DATA_ASGNMNTS WHERE USER_GUID = FND_GLOBAL.USER_GUID)
                               AND apinv.vendor_site_id = supsites.vendor_site_id
                               and apinv.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id
                             --  and gcc.segment4 = nvl(:P_liability,gcc.segment4) 
                               AND apinv.invoice_type_lookup_code='PREPAYMENT'                                                         
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num, NULL payment_number,
                                   CASE
                                      WHEN apinv_cancel.gl_date IS NOT NULL
                                         THEN apinv_cancel.gl_date
                                      ELSE apinv.cancelled_date
                                   END gl_date,
                                   (  apinv.cancelled_amount
                                    * CASE
                                         WHEN apinv.invoice_type_lookup_code NOT IN
                                                          ('CREDIT', 'DEBIT')
                                            THEN 1
                                         ELSE 0
                                      END
                                   ) dr_amount,
                                   (  apinv.cancelled_amount
                                    * CASE
                                         WHEN apinv.invoice_type_lookup_code IN
                                                          ('CREDIT', 'DEBIT')
                                            THEN -1
                                         ELSE 0
                                      END
                                   ) cr_amount,
                                   supsites.vendor_site_code,
                                   'Invoice Cancelled' doc_type, 6 disorder
                              FROM ap_invoices_all apinv,
                                   (SELECT   invoice_id,
                                             MAX (accounting_date) gl_date
                                        FROM ap_invoice_distributions_all
                                       WHERE cancellation_flag = 'Y'
                                    GROUP BY invoice_id) apinv_cancel,
                                   poz_supplier_sites_v supsites,gl_code_combinations gcc
                             WHERE apinv.invoice_id = apinv_cancel.invoice_id(+)
                               AND 1 =
                                      CASE
                                         WHEN apinv_cancel.gl_date IS NOT NULL
                                         AND TO_CHAR
                                                 (TRUNC (apinv_cancel.gl_date),
                                                  'YYYY-MM-DD'
                                                 ) <
                                                NVL
                                                   (TO_CHAR
                                                          (TRUNC (:p_from_date),
                                                           'YYYY-MM-DD'
                                                          ),
                                                    TRUNC
                                                         (apinv_cancel.gl_date)
                                                   )
                                            THEN 1
                                         WHEN apinv_cancel.gl_date IS NULL
                                         AND TO_CHAR
                                                 (TRUNC (apinv.cancelled_date),
                                                  'YYYY-MM-DD'
                                                 ) <
                                                NVL
                                                   (TO_CHAR
                                                          (TRUNC (:p_from_date),
                                                           'YYYY-MM-DD'
                                                          ),
                                                    TRUNC
                                                         (apinv.cancelled_date)
                                                   )
                                            THEN 1
                                         ELSE 0
                                      END
                               AND apinv.vendor_site_id =  supsites.vendor_site_id
                               AND apinv.cancelled_date IS NOT NULL
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id
                  --   and  gcc.segment4=nvl(:P_liability,gcc.segment4)                                          
                            UNION ALL
                            SELECT apinv.vendor_id,
                                   apinv.invoice_type_lookup_code inv_type,
                                   apinv.invoice_num, NULL payment_number,
                                   apinv.gl_date,
                                   NVL
                                      ((SELECT DECODE
                                                  (SIGN (SUM (ail.amount)),
                                                   -1, NVL
                                                        (SUM (ABS (ail.amount)),
                                                         0
                                                        )
                                                  ) amt
                                          FROM ap_invoice_lines_all ail
                                         WHERE ail.invoice_id =
                                                              apinv.invoice_id
                                           AND ail.line_type_lookup_code =  'AWT'
                                           AND ail.tax_regime_code in ( 'TDS_REGIME','TDS_GLOBA')),
                                       0
                                      ) dr_amount,
                                   NVL
                                      ((SELECT DECODE
                                                  (SIGN (SUM (ail.amount)),
                                                   1, NVL
                                                        (SUM (ABS (ail.amount)),
                                                         0
                                                        )
                                                  ) amt
                                          FROM ap_invoice_lines_all ail
                                         WHERE ail.invoice_id =
                                                              apinv.invoice_id
                                           AND ail.line_type_lookup_code = 'AWT'
                                           AND ail.tax_regime_code in ( 'TDS_REGIME','TDS_GLOBA')),
                                       0
                                      ) cr_amount,
                                   supsites.vendor_site_code, 'AWT' doc_type,
                                   7 disorder
                              FROM ap_invoices_all apinv,
                                   poz_supplier_sites_v supsites,gl_code_combinations gcc
                             WHERE TO_CHAR (TRUNC (apinv.gl_date),
                                            'YYYY-MM-DD'
                                           ) <
                                      NVL (TO_CHAR (TRUNC (:p_from_date),
                                                    'YYYY-MM-DD'
                                                   ),
                                           TRUNC (apinv.gl_date)
                                          ) 
                               AND apinv.vendor_site_id =  supsites.vendor_site_id
                               AND apinv.vendor_id =
                                      (SELECT ps.vendor_id
                                         FROM poz_suppliers ps, hz_parties hp
                                        WHERE ps.party_id = hp.party_id
                                          AND hp.party_name = :p_vendor_name)
                                           AND apinv.vendor_site_id in (SELECT VENDOR_SITE_ID FROM POZ_SUPPLIER_SITES_V
WHERE VENDOR_SITE_CODE=nvl(:P_VENDOR_SITE_CODE,VENDOR_SITE_CODE))
                     and apinv.ACCTS_PAY_CODE_COMBINATION_ID=gcc.code_combination_id                                         
                                          ) vttbl
                  GROUP BY vttbl.vendor_id) opnbal
        ORDER BY sno,
                 orderby ASC,
                 doc_type     
                         ) xx

Creditor Ledger sql query in Oracle Fusion
Creditor Ledger sql query in Oracle Fusion

at 06:39
Labels:

1 comments:

Anonymous said...

Hi
Thanks for this beautiful query....
I tried this query but observed that PrePayment Invoices are appearing in 2 lines..one for Debit and other for Credit.

Regards
Shukla

19 January 2021 at 06:09

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *