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

Supplier Ledger (Invoices, Payments and Prepayments)

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

    Creation Date : 01 October, 2016

    Purpose       : AP Supplier Ledger

    Module        : Payables

    Parameters    : Supplier

    Created By    : Muhammad Waqas Khan

   

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

select *

  from (SELECT 2 sno,

               LE.NAME LE_NAME,

               HAOU.NAME HAOU_NAME,

               AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

               DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE,

                      'STANDARD',

                      'Standard Invoice',

                      'DEBIT',

                      'Debit Memo',

                      'CREDIT',

                      'Credit Memo') INV_TYPE_DESC,

               AIA.INVOICE_NUM,

               0 cHECK_NUMBER,

               AIA.INVOICE_ID,

               ASA.VENDOR_ID SUPP_ID,

               ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

               ASSA.VENDOR_SITE_CODE SUPP_SITE,

               AIA.DOC_SEQUENCE_VALUE VOUCHER_NO,

               AIA.INVOICE_DATE INV_DATE,

               AIDA.DESCRIPTION INV_DESC,

               (case

                 when AIDA.AMOUNT < 0 then

                  abs(AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

               end) DR,

               (case

                 when AIDA.AMOUNT > 0 then

                  (AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

               end) CR,

               0 check_id,

               AIA.Doc_Sequence_Value inv_doc_seq_val,

               0 chk_doc_seq_val

          FROM AP_INVOICES_ALL              AIA,

               AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

               AP_SUPPLIERS                 ASA,

               AP_SUPPLIER_SITES_ALL        ASSA,

               XLE_ENTITY_PROFILES          LE,

               HR_ALL_ORGANIZATION_UNITS    HAOU,

               GL_LEDGER_LE_V               GL

       

         WHERE 1 = 1

           and AIA.INVOICE_ID = AIDA.INVOICE_ID

           AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

           AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

           AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

           AND ASA.VENDOR_ID = AIA.VENDOR_ID

           AND ASSA.VENDOR_ID = ASA.VENDOR_ID

           AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

           AND AIA.INVOICE_TYPE_LOOKUP_CODE in

               ('STANDARD', 'DEBIT', 'CREDIT')

           AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,

                                                   AIA.INVOICE_AMOUNT,

                                                   AIA.PAYMENT_STATUS_FLAG,

                                                   AIA.INVOICE_TYPE_LOOKUP_CODE)            =   'APPROVED'

           AND AIA.GL_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

           AND AIA.ORG_ID = &P_ORG_ID

           AND AIA.VENDOR_ID = &P_VENODR_TO

           and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

           AND AIA.Cancelled_Date Is Null

           and aida.line_type_lookup_code != 'PREPAY'

           and nvl(AIA.DOC_SEQUENCE_VALUE, -1) =

               nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1))

        --&ADD_WHERE

        UNION ALL

        SELECT 21 sno,

               LE.NAME LE_NAME,

               HAOU.NAME HAOU_NAME,

               AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

               DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE,

                      'STANDARD',

                      'Standard Invoice',

                      'DEBIT',

                      'Debit Memo',

                      'CREDIT',

                      'Credit Memo') INV_TYPE_DESC,

               AIA.INVOICE_NUM,

               0 cHECK_NUMBER,

               AIA.INVOICE_ID,

               ASA.VENDOR_ID SUPP_ID,

               ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

               ASSA.VENDOR_SITE_CODE SUPP_SITE,

               AIA.DOC_SEQUENCE_VALUE VOUCHER_NO,

               AIA.INVOICE_DATE INV_DATE,

               AIDA.DESCRIPTION INV_DESC,

               (case

                 when AIDA.AMOUNT < 0 then

                  abs(AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

               end) DR,

               (case

                 when AIDA.AMOUNT > 0 then

                  (AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

               end) CR,

               0 check_id,

               AIA.Doc_Sequence_Value inv_doc_seq_val,

               0 chk_doc_seq_val

          FROM AP_INVOICES_ALL              AIA,

               AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

               AP_SUPPLIERS                 ASA,

               AP_SUPPLIER_SITES_ALL        ASSA,

               XLE_ENTITY_PROFILES          LE,

               HR_ALL_ORGANIZATION_UNITS    HAOU,

               GL_LEDGER_LE_V               GL

       

         WHERE 1 = 1

           and AIA.INVOICE_ID = AIDA.INVOICE_ID

           AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

           AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

           AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

           AND ASA.VENDOR_ID = AIA.VENDOR_ID

           AND ASSA.VENDOR_ID = ASA.VENDOR_ID

           AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

           AND AIA.INVOICE_TYPE_LOOKUP_CODE in

               ('STANDARD', 'DEBIT', 'CREDIT')

         AND AIA.GL_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

           AND AIA.ORG_ID = &P_ORG_ID

           AND AIA.VENDOR_ID = &P_VENODR_TO

           and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

           AND AIA.Cancelled_Date Is Null

           and aida.line_type_lookup_code = 'PREPAY'

           AND AIDA.REVERSAL_FLAG = 'N'

           and nvl(AIA.DOC_SEQUENCE_VALUE, -1) =

               nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1))

       

        UNION ALL

        --For Payment--

       

        SELECT 3 sno,

               LE.NAME LE_NAME,

               HAOU.NAME,

               AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

               'Payment' INV_TYPE_DESC,

               AIA.INVOICE_NUM,

               ACA.CHECK_NUMBER,

               AIA.INVOICE_ID,

               ASA.VENDOR_ID,

               ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

               ASSA.VENDOR_SITE_CODE SUPP_SITE,

               ACA.doc_sequence_value VOUCHER_NO,

               ACA.CHECK_DATE PAY_DATE,

               ACA.DESCRIPTION INV_DESC,

               (CASE

                 WHEN AIPA.AMOUNT >= 0 THEN

                  SUM(AIPA.AMOUNT * NVL(ROUND(AIA.EXCHANGE_RATE, 7), 1))

                 ELSE

                  0

               END) DR,

               (CASE

                 WHEN AIPA.AMOUNT < 0 THEN

                  SUM(AIPA.AMOUNT * NVL(ROUND(AIA.EXCHANGE_RATE, 7), 1))

                 ELSE

                  0

               END) CR,

               ACA.check_id,

               AIA.Doc_Sequence_Value inv_doc_seq_val,

               ACA.Doc_Sequence_Value chk_doc_seq_val

          FROM AP_INVOICES_ALL           AIA,

               AP_INVOICE_PAYMENTS_ALL   AIPA,

               AP_CHECKS_ALL             ACA,

               AP_SUPPLIERS              ASA,

               AP_SUPPLIER_SITES_ALL     ASSA,

               XLE_ENTITY_PROFILES       LE,

               HR_ALL_ORGANIZATION_UNITS HAOU,

               GL_LEDGER_LE_V            GL

         WHERE 1 = 1

           AND AIPA.INVOICE_ID = AIA.INVOICE_ID

           AND AIPA.CHECK_ID = ACA.CHECK_ID

           AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

           AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

           AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

           AND ASA.VENDOR_ID = AIA.VENDOR_ID

           AND ASSA.VENDOR_ID = ASA.VENDOR_ID

           AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

             

           AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,

                                                   AIA.INVOICE_AMOUNT,

                                                   AIA.PAYMENT_STATUS_FLAG,

                                                   AIA.INVOICE_TYPE_LOOKUP_CODE)            = 'APPROVED'

           AND ACA.CHECK_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

           AND AIA.ORG_ID = &P_ORG_ID

           AND AIA.VENDOR_ID = &P_VENODR_TO

           and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

           AND ACA.STATUS_LOOKUP_CODE <> 'VOIDED'

           and nvl(AIA.DOC_SEQUENCE_VALUE, -1) =

               nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1))

         GROUP BY LE.NAME,

                  HAOU.NAME,

                  AIA.INVOICE_TYPE_LOOKUP_CODE,

                  'Payment',

                  AIA.INVOICE_ID,

                  CHECK_NUMBER,

                  ASA.VENDOR_ID,

                  ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME,

                  ASSA.VENDOR_SITE_CODE,

                  ACA.doc_sequence_value,

                  ACA.CHECK_DATE,

                  ACA.DESCRIPTION,

                  AIA.EXCHANGE_RATE,

                  AIPA.AMOUNT,

                  ACA.STATUS_LOOKUP_CODE,

                  ACA.check_id,

                  ACA.CHECK_NUMBER,

                  AIA.INVOICE_NUM,

                  AIA.Doc_Sequence_Value,

                  ACA.Doc_Sequence_Value

       

        UNION ALL

        --For Prepayment Invoice--

       

        SELECT 1 sno,

               LE.NAME LE_NAME,

               HAOU.NAME,

               AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

               'Prepayment Invoice' INV_TYPE_DESC,

               AIA.INVOICE_NUM,

               0 CHECK_NUMBER,

               AIA.INVOICE_ID,

               ASA.VENDOR_ID,

               ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

               ASSA.VENDOR_SITE_CODE SUPP_SITE,

               AIA.DOC_SEQUENCE_VALUE VOUCHER_NO,

               AIA.INVOICE_DATE INV_DATE,

               AIA.DESCRIPTION INV_DESC,

               0 CR,

               (AIA.INVOICE_AMOUNT * NVL(ROUND(AIA.EXCHANGE_RATE, 7), 1)) DR,

               0 check_id,

               AIA.Doc_Sequence_Value inv_doc_seq_val,

               0 chk_doc_seq_val

          FROM AP_INVOICES_ALL           AIA,

               AP_SUPPLIERS              ASA,

               AP_SUPPLIER_SITES_ALL     ASSA,

               XLE_ENTITY_PROFILES       LE,

               HR_ALL_ORGANIZATION_UNITS HAOU,

               GL_LEDGER_LE_V            GL

         WHERE 1 = 1

           AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

           AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

           AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

           AND ASA.VENDOR_ID = AIA.VENDOR_ID

           AND ASSA.VENDOR_ID = ASA.VENDOR_ID

           AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

           AND AIA.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'

           AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,

                                                   AIA.INVOICE_AMOUNT,

                                                   AIA.PAYMENT_STATUS_FLAG,

                                                   AIA.INVOICE_TYPE_LOOKUP_CODE)            in  ('AVAILABLE', 'FULL')

           AND AIA.GL_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

           AND AIA.ORG_ID = &P_ORG_ID

           And Aia.Cancelled_Date Is Null

           AND AIA.VENDOR_ID = &P_VENODR_TO

           and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

           and nvl(AIA.DOC_SEQUENCE_VALUE, -1) = nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1)))

 ORDER BY INV_DATE, inv_doc_seq_val, DR desc