AP Payment Voucher Query

/********************************************** 

    Creation Date : 30 September, 2016

    Purpose       : AP Payment Voucher Query

    Module        : Payable

    Parameters    : Cheque No, Payment Voucher No

                    and Payment Date

    Created By    : Muhammad Waqas Khan

   

*********************************************/

SELECT CHK.DOC_SEQUENCE_VALUE "PAYMENT VOUCHER",

       CHK.CURRENCY_CODE "PAYMENT CURRENCY",

       CHK.CHECK_DATE "PAYMENT DATE",

       CHECK_NUMBER "PAYMENT NO.",

       CHK.FUTURE_PAY_DUE_DATE "CHECK_DATE", --MATURITY DATE

       CHK.PAYMENT_METHOD_CODE "PAYMENT TYPE",

       BNK.BANK_NAME "BANK NAME",

       CHK.BANK_ACCOUNT_NUM "BANK ACCOUNT NO.",

       CHK.VENDOR_NAME "PAID TO",

       CHK.AMOUNT "AMOUNT",

       AIA.INVOICE_NUM "INVOICE NO.",

       AIA.INVOICE_DATE "INVOICE DATE",

       AIA.DESCRIPTION "INVOICE DESCRIPTION",

       AIA.INVOICE_AMOUNT "INVOICE AMOUNT",

       SUPP_BANK.BANK_NAME "PAID TO BANK NAME",

       SUPP_BANK.BANK_ACCOUNT_NUM "PAID TO BANK ACCOUNT NO.",

       CHK.EXCHANGE_RATE CONVERSION_RATE,

       TO_CHAR((NVL(CHK.EXCHANGE_RATE, 1) * CHK.AMOUNT), '9,999.99') FUNCTIONAL_AMOUNT

  FROM AP_INVOICE_PAYMENTS_ALL IPA,

       AP_CHECKS_ALL CHK,

       AP_INVOICES_ALL AIA,

       FND_USER U,

       CE_BANK_ACCOUNTS BNKACC,

       CE_BANKS_V BNK,

       XLE_ENTITY_PROFILES LE,

       (SELECT APS.VENDOR_ID,

               HOP_BANK.ORGANIZATION_NAME BANK_NAME,

               IEBA.BANK_ACCOUNT_NUM

          FROM HZ_PARTIES               HZP,

               AP_SUPPLIERS             APS,

               IBY_EXTERNAL_PAYEES_ALL  HEPA,

               IBY_PMT_INSTR_USES_ALL   IPIUA,

               IBY_EXT_BANK_ACCOUNTS    IEBA,

               HZ_PARTIES               HZP_BANK,

               HZ_ORGANIZATION_PROFILES HOP_BANK

         WHERE HZP.PARTY_ID = APS.PARTY_ID

           AND HZP.PARTY_ID = HEPA.PAYEE_PARTY_ID

           AND HEPA.EXT_PAYEE_ID = IPIUA.EXT_PMT_PARTY_ID(+)

           AND IPIUA.INSTRUMENT_ID = IEBA.EXT_BANK_ACCOUNT_ID(+)

           AND IEBA.BANK_ID = HZP_BANK.PARTY_ID(+)

           AND HOP_BANK.PARTY_ID(+) = HZP_BANK.PARTY_ID

           AND HEPA.SUPPLIER_SITE_ID IS NULL) SUPP_BANK

 WHERE LE.LEGAL_ENTITY_ID = CHK.LEGAL_ENTITY_ID

   AND CHK.CHECK_ID = IPA.CHECK_ID

   AND BNK.BANK_PARTY_ID = BNKACC.BANK_ID

   AND BNKACC.BANK_ACCOUNT_NAME = CHK.BANK_ACCOUNT_NAME

   AND BNKACC.BANK_ACCOUNT_NUM = CHK.BANK_ACCOUNT_NUM

   AND AIA.INVOICE_ID = IPA.INVOICE_ID

   AND AIA.VENDOR_ID = SUPP_BANK.VENDOR_ID(+)

   AND CHK.CREATED_BY = U.USER_ID(+)

   AND CHK.LAST_UPDATED_BY = U.USER_ID(+)

   AND CHK.STATUS_LOOKUP_CODE <> 'VOIDED'

   --AND CHK.CHECK_NUMBER = '329'

      ------ PARAMETERS ----------------

   AND CHK.CHECK_NUMBER = NVL(:P_CHAQUE_NUMBER, CHK.CHECK_NUMBER)

   AND NVL(CHK.DOC_SEQUENCE_VALUE, 0) BETWEEN

       NVL(:P_VOUCHER_NO_FROM, NVL(CHK.DOC_SEQUENCE_VALUE, 0)) AND

       NVL(:P_VOUCHER_NO_TO, NVL(CHK.DOC_SEQUENCE_VALUE, 0))

   AND IPA.ORG_ID = NVL(:P_ORG_ID, IPA.ORG_ID)

   AND CHK.CHECK_DATE BETWEEN NVL(:FROM_DATE, CHK.CHECK_DATE) AND

       NVL(:TO_DATE, CHK.CHECK_DATE)

 ORDER BY CHK.DOC_SEQUENCE_VALUE,

          CHK.CHECK_NUMBER,

          AIA.INVOICE_NUM,

          AIA.INVOICE_DATE