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

GL Manual Journal Voucher Query

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

    Creation Date : 04 October, 2016

    Purpose       : GL Journal Voucher

                    Report Query

    Module        : General Ledger

    Parameters    : From and To Voucher No

    Created By    : Muhammad Waqas Khan

   

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

SELECT H.PERIOD_NAME,

       H.DESCRIPTION,

       H.DEFAULT_EFFECTIVE_DATE,

       H.JE_HEADER_ID,

       L.JE_LINE_NUM,

       H.CURRENCY_CODE,

       H.DOC_SEQUENCE_VALUE JV_NO,

       H.LEDGER_ID,

       C.CONCATENATED_SEGMENTS,

       L.DESCRIPTION,

       APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(C.CHART_OF_ACCOUNTS_ID,

                                                  4,

                                                  C.SEGMENT4) ACCOUNT_DESC,

       L.ENTERED_DR,

       L.ENTERED_CR

  FROM GL_JE_HEADERS H, GL_JE_LINES L, GL_CODE_COMBINATIONS_KFV C

 WHERE H.JE_HEADER_ID = L.JE_HEADER_ID

      -- AND H.JE_SOURCE = 'Manual'

   AND H.DOC_SEQUENCE_VALUE IS NOT NULL

   AND NVL(H.DOC_SEQUENCE_VALUE, -1) BETWEEN

       NVL(:FROM_VOUCHER, NVL(H.DOC_SEQUENCE_VALUE, -1)) AND

       NVL(:TO_VOUCHER, NVL(H.DOC_SEQUENCE_VALUE, -1))

   AND L.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID

   AND H.LEDGER_ID = :P_LEDGER_ID

 ORDER BY H.DOC_SEQUENCE_VALUE, H.JE_HEADER_ID, L.JE_LINE_NUM

AP Prepayment Report Query

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

    Creation Date : 04 October, 2016

    Purpose       : AP Prepayment Report

    Module        : Payables

    Parameters    : Supplier and Organization

    Created By    : Muhammad Waqas Khan

   

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

SELECT VENDOR_NAME,

       ORG_ID,

       VOUCHER,

       C_INVOICE_NUM,

       CURR,

       INVOICE_DATE,

       GL_DATE,

       DESCRIP,

       (INVOICE_AMOUNT * EXCHANGE_RATE) INVOICE_AMOUNT,

       (ABS(PREPAY_AMOUNT) * EXCHANGE_RATE) PREPAY_AMOUNT,

       (REMAINING_AMOUNT * EXCHANGE_RATE) REMAINING_AMOUNT

  FROM (SELECT UPPER(C_VENDOR_NAME) VENDOR_NAME,

               ORG_ID,

               DESCRIP,

               DOC_SEQUENCE_VALUE,

               C_INVOICE_NUM,

               INVOICE_DATE,

               INVOICE_AMOUNT,

               PREPAY_AMOUNT,

               INVOICE_AMOUNT + PREPAY_AMOUNT REMAINING_AMOUNT,

               EXCHANGE_RATE,

               GL_DATE,

               CURR,

               (SELECT ACA.DOC_SEQUENCE_VALUE

                  FROM AP_INVOICE_PAYMENTS_ALL AIPA, AP_CHECKS_ALL ACA

                 WHERE AIPA.INVOICE_ID = X.INVOICE_ID

                   AND ACA.STATUS_LOOKUP_CODE = 'RECONCILED'

                   AND ACA.CHECK_ID = AIPA.CHECK_ID) VOUCHER

          FROM (SELECT DISTINCT PV.VENDOR_NAME AS C_VENDOR_NAME,

                                INV.DOC_SEQUENCE_VALUE,

                                INV.DESCRIPTION DESCRIP,

                                INV.ORG_ID,

                                INV.INVOICE_ID,

                                INV.INVOICE_NUM AS C_INVOICE_NUM,

                                INV.INVOICE_DATE,

                                NVL(INV.EXCHANGE_RATE, 1) EXCHANGE_RATE,

                                INV.INVOICE_CURRENCY_CODE CURR,

                                INV.INVOICE_CURRENCY_CODE AS C_CURRENCY_CODE,

                                INV.GL_DATE,

                                NVL(INV.INVOICE_AMOUNT, 0) AS INVOICE_AMOUNT,

                                (SELECT SUM(AMOUNT)

                                   FROM AP_INVOICE_DISTRIBUTIONS_ALL IDA

                                  WHERE LINE_TYPE_LOOKUP_CODE = 'PREPAY'

                                    AND AID.INVOICE_DISTRIBUTION_ID =

                                        IDA.PREPAY_DISTRIBUTION_ID) AS PREPAY_AMOUNT

                  FROM PO_VENDORS                   PV,

                       AP_INVOICES_ALL              INV,

                       AP_INVOICE_DISTRIBUTIONS_ALL AID,

                       AP_INVOICE_PREPAYS_ALL       AIPP

                 WHERE PV.VENDOR_ID = INV.VENDOR_ID

                   AND INV.INVOICE_ID = AID.INVOICE_ID

                   AND AIPP.INVOICE_ID(+) = INV.INVOICE_ID

                   AND PV.VENDOR_ID = NVL(:P_VENDOR_ID, PV.VENDOR_ID)

                   AND AID.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'

                   AND INV.ORG_ID = :P_ORG_ID --234

                --AND INV.INVOICE_NUM = 'PTC2014/380'

                ) X

         WHERE INVOICE_AMOUNT - PREPAY_AMOUNT IS NOT NULL)

 WHERE REMAINING_AMOUNT > 0

 ORDER BY 1, 6;