QUERY FOR PAYMENT VOUCHED DETAILS

/* Formatted on 26-Apr-12 5:51:27 PM (QP5 v5.115.810.9015) */
SELECT   ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
         ACA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
         ACA.CHECKRUN_NAME BATCH_NAME,
         ACA.BANK_ACCOUNT_NUM,
         FU.USER_NAME,
         ACA.CHECK_DATE GL_DATE,
         ACA.CHECK_ID,
         ACA.CHECK_ID CHECK_ID1,
         ACA.CHECK_NUMBER,
         ACA.VENDOR_ID,
         aca.vendor_site_id,
         NVL (
            (SELECT   aia.attribute2
               FROM   ap_invoice_payments_all aip, ap_invoices_all aia
              WHERE       aip.invoice_id = aia.invoice_id
                      AND aip.check_id = aca.check_id
                      AND ROWNUM = 1),
            HZP.PARTY_NAME
         )
            VENDOR_NAME,
         ACA.PARTY_ID,
         ACA.DOC_SEQUENCE_VALUE VOUCHER_NO,
         ACA.AMOUNT * NVL (ACA.EXCHANGE_RATE, 1) CREDIT_AMOUNT,
         GCC.SEGMENT2 COST_CENTER,
         GCC.SEGMENT3 || '.' || GCC.SEGMENT4 ACCOUNT_CODE,
         REPLACE (
            DECODE (GCC.SEGMENT4,
                    '00', A.DESCRIPTION,
                    A.DESCRIPTION || '.' || B.DESCRIPTION),
            'CLEARING A/C',
            NULL
         )
            DESRIPTION,
         NULL Status,
         1 Query
  FROM   AP_CHECKS_ALL ACA,
         FND_USER FU,
         CE_BANK_ACCT_USES_ALL CBAU,
         CE_BANK_ACCOUNTS CBA,
         GL_CODE_COMBINATIONS GCC,
         FND_FLEX_VALUES_VL A,
         FND_FLEX_VALUES_VL B,
         HZ_PARTIES HZP
 WHERE   ACA.ORG_ID = :P_ORG_ID AND HZP.PARTY_ID = ACA.PARTY_ID
         AND DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME) =
               NVL (
                  :P_BATCH_NAME,
                  DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME)
               )
         AND aca.doc_sequence_value BETWEEN NVL (:P_VOU_NUM,
                                                 aca.doc_sequence_value)
                                        AND  NVL (:P_VOU_TO,
                                                  aca.doc_sequence_value)
         /*AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) >=
                      NVL(:P_VOU_NUM,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))
                  AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) <=
                      NVL(:P_VOU_TO,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))*/
         AND ACA.CHECK_NUMBER >= NVL (:P_CHECK_NUMBER, ACA.CHECK_NUMBER)
         AND ACA.CHECK_NUMBER <= NVL (:P_CHECK_TO, ACA.CHECK_NUMBER)
         AND ACA.CE_BANK_ACCT_USE_ID =
               NVL (:P_BANK_ACCOUNT_ID, ACA.CE_BANK_ACCT_USE_ID)
         AND HZP.PARTY_NAME = NVL (:P_VENDOR_NAME, HZP.PARTY_NAME)
         AND FU.USER_ID = ACA.CREATED_BY
         AND CBAU.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND GCC.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID
         AND A.FLEX_VALUE = GCC.SEGMENT3
         AND B.FLEX_VALUE = GCC.SEGMENT4
         AND B.PARENT_FLEX_VALUE_LOW = A.FLEX_VALUE
         AND aca.created_by = NVL (:p_created_by, aca.created_by)
UNION ALL
SELECT   ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
         ACA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
         ACA.CHECKRUN_NAME BATCH_NAME,
         ACA.BANK_ACCOUNT_NUM,
         FU.USER_NAME,
         ACA.CHECK_DATE GL_DATE,
         ACA.CHECK_ID,
         ACA.CHECK_ID CHECK_ID1,
         ACA.CHECK_NUMBER,
         ACA.VENDOR_ID,
         aca.vendor_site_id,
         NVL (
            (SELECT   aia.attribute2
               FROM   ap_invoice_payments_all aip, ap_invoices_all aia
              WHERE       aip.invoice_id = aia.invoice_id
                      AND aip.check_id = aca.check_id
                      AND ROWNUM = 1),
            HZP.PARTY_NAME
         )
            VENDOR_NAME,
         ACA.PARTY_ID,
         ACA.DOC_SEQUENCE_VALUE VOUCHER_NO,
         ACA.AMOUNT * NVL (ACA.EXCHANGE_RATE, 1) * -1 CREDIT_AMOUNT,
         GCC.SEGMENT2 COST_CENTER,
         GCC.SEGMENT3 || '.' || GCC.SEGMENT4 ACCOUNT_CODE,
         REPLACE (
            DECODE (GCC.SEGMENT4,
                    '00', A.DESCRIPTION,
                    A.DESCRIPTION || '.' || B.DESCRIPTION),
            'CLEARING A/C',
            NULL
         )
            DESRIPTION,
         'VOIDED' status,
         2 Query
  FROM   AP_CHECKS_ALL ACA,
         FND_USER FU,
         CE_BANK_ACCT_USES_ALL CBAU,
         CE_BANK_ACCOUNTS CBA,
         GL_CODE_COMBINATIONS GCC,
         FND_FLEX_VALUES_VL A,
         FND_FLEX_VALUES_VL B,
         HZ_PARTIES HZP
 WHERE   ACA.ORG_ID = :P_ORG_ID AND HZP.PARTY_ID = ACA.PARTY_ID
         AND DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME) =
               NVL (
                  :P_BATCH_NAME,
                  DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME)
               )
         AND aca.doc_sequence_value BETWEEN NVL (:P_VOU_NUM,
                                                 aca.doc_sequence_value)
                                        AND  NVL (:P_VOU_TO,
                                                  aca.doc_sequence_value)
         /* AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) >=
                      NVL(:P_VOU_NUM,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))
                  AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) <=
                      NVL(:P_VOU_TO,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))*/
         AND ACA.CHECK_NUMBER >= NVL (:P_CHECK_NUMBER, ACA.CHECK_NUMBER)
         AND ACA.CHECK_NUMBER <= NVL (:P_CHECK_TO, ACA.CHECK_NUMBER)
         AND ACA.CE_BANK_ACCT_USE_ID =
               NVL (:P_BANK_ACCOUNT_ID, ACA.CE_BANK_ACCT_USE_ID)
         AND HZP.PARTY_NAME = NVL (:P_VENDOR_NAME, HZP.PARTY_NAME)
         AND FU.USER_ID = ACA.CREATED_BY
         AND CBAU.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND GCC.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID
         AND A.FLEX_VALUE = GCC.SEGMENT3
         AND B.FLEX_VALUE = GCC.SEGMENT4
         AND B.PARENT_FLEX_VALUE_LOW = A.FLEX_VALUE
         AND ACA.status_lookup_code = 'VOIDED'
         AND aca.created_by = NVL (:p_created_by, aca.created_by)
ORDER BY   14, 20


query :2

 SELECT   ABA.BATCH_NAME INVOICE_BATCH,
           PV.VENDOR_NAME,
           AIA.INVOICE_NUM,
           AIPA.AMOUNT,
           AIA.INVOICE_ID,
           AIA.ORG_ID,
           AIPA.CHECK_ID,
           ROWNUM S_NO_INV
    FROM   AP_INVOICES_ALL AIA,
           PO_VENDORS PV,
           AP_BATCHES_ALL ABA,
           AP_INVOICE_PAYMENTS_ALL AIPA
   WHERE       ABA.BATCH_ID = AIA.BATCH_ID
           AND AIA.VENDOR_ID = PV.VENDOR_ID
           AND AIA.INVOICE_ID = AIPA.INVOICE_ID
           AND AIA.ORG_ID = AIPA.ORG_ID
           AND AIA.ORG_ID = :P_ORG_ID
--&P_DYAMIC_QUERY
ORDER BY   PV.VENDOR_NAME