RO Partywise Account Ledger Report

Below Query will fetch Vendor Wise RO Account Ledger details

SELECT   *
    FROM (SELECT   asp.vendor_id, aia.gl_date accounting_date,
                      (SELECT DECODE (alc.displayed_field,
                                      'Standard', 'Purchase ',
                                      'Credit Memo', 'Credit Note',
                                      'Debit Memo', 'Debit Note',
                                      alc.displayed_field
                                     )
                         FROM ap_lookup_codes alc
                        WHERE 1 = 1
                          AND alc.lookup_type(+) = 'INVOICE TYPE'
                          AND alc.lookup_code(+) =
                                                  aia.invoice_type_lookup_code)
                   || DECODE (aia.voucher_num,
                              NULL, NULL,
                              '.' || aia.voucher_num
                             ) particulars,
                   'Invoices' voucher_type1,
                   ABS (  SUM (aia.invoice_amount)
                        + (SELECT NVL (SUM (aia1.invoice_amount), 0)
                             FROM ap_invoices_all aia1
                            WHERE 1=1--aia1.attribute1 = TO_CHAR (aia.invoice_id)
                              AND aia1.invoice_num like aia.invoice_num||'%TDS%CM%'
                              AND aia1.invoice_type_lookup_code = 'CREDIT')
                       ) credit_m,
                   ABS (SUM (0)) debit_m, aia.voucher_num voucher_number,
                   aia.invoice_num, :p_show_opening_bal,
                   aia.invoice_id invoice_id_1, aia.invoice_date invoice_date
              FROM apps.ap_invoices_all aia, apps.ap_suppliers asp
             WHERE 1 = 1
               AND aia.vendor_id = asp.vendor_id
               AND aia.invoice_type_lookup_code NOT IN
                                                     ('CREDIT', 'PREPAYMENT')
               AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
               AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
               AND EXISTS (
                      SELECT '1'
                        FROM ap_supplier_sites_all assa
                       WHERE assa.vendor_id = aia.vendor_id
                         AND assa.vendor_site_id = aia.vendor_site_id
                         AND NVL (assa.attribute3, '~') =
                                NVL (:p_supplier_type,
                                     NVL (assa.attribute3, '~')
                                    ))
               AND NVL (aia.pay_group_lookup_code, 'X') =
                      NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), 'X')
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
          GROUP BY asp.vendor_id,
                   aia.gl_date,
                   aia.voucher_num,
                   aia.invoice_num,
                   aia.voucher_num,
                   aia.invoice_type_lookup_code,
                   aia.invoice_id,
                   aia.invoice_date
          UNION ALL
          SELECT   asp.vendor_id, aia.gl_date accounting_date,
                      (SELECT DECODE (alc.displayed_field,
                                      'Standard', 'Purchase ',
                                      'Credit Memo', 'Credit Note',
                                      'Debit Memo', 'Debit Note',
                                      alc.displayed_field
                                     )
                         FROM ap_lookup_codes alc
                        WHERE 1 = 1
                          AND alc.lookup_type(+) = 'INVOICE TYPE'
                          AND alc.lookup_code(+) =
                                                  aia.invoice_type_lookup_code)
                   || DECODE (aia.voucher_num,
                              NULL, NULL,
                              '.' || aia.voucher_num
                             ) particulars,
                   'Invoices' voucher_type1, ABS (SUM (0)) credit_m,
                   ABS (  SUM (aia.invoice_amount)
                        + (SELECT NVL (SUM (aia1.invoice_amount), 0)
                             FROM ap_invoices_all aia1
                            WHERE 1=1--aia1.attribute1 = TO_CHAR (aia.invoice_id)
                              AND aia1.invoice_num like aia.invoice_num||'%TDS%CM%'
                              AND aia1.invoice_type_lookup_code = 'CREDIT')
                       ) debit_m,
                   aia.voucher_num voucher_number, aia.invoice_num,
                   :p_show_opening_bal, aia.invoice_id invoice_id_1,
                   aia.invoice_date invoice_date
              FROM apps.ap_invoices_all aia, apps.ap_suppliers asp
             WHERE 1 = 1
               AND aia.vendor_id = asp.vendor_id
               AND aia.invoice_type_lookup_code = 'CREDIT'
               -- AND aia.attribute2 = 'PURCHASE_RETURN' -- kamalakar 14th jan
               AND aia.attribute2 IN ('PURCHASE_RETURN', 'DEBIT_NOTE')
               AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
               AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
               AND EXISTS (
                      SELECT '1'
                        FROM ap_supplier_sites_all assa
                       WHERE assa.vendor_id = aia.vendor_id
                         AND assa.vendor_site_id = aia.vendor_site_id
                         AND NVL (assa.attribute3, '~') =
                                NVL (:p_supplier_type,
                                     NVL (assa.attribute3, '~')
                                    ))
               AND NVL (aia.pay_group_lookup_code, 'X') =
                      NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), 'X')
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
          GROUP BY asp.vendor_id,
                   aia.gl_date,
                   aia.voucher_num,
                   aia.invoice_num,
                   aia.voucher_num,
                   aia.invoice_type_lookup_code,
                   aia.invoice_id,
                   aia.invoice_date
          UNION ALL
          -- new union 10th july 2015

          /*SELECT   asp.vendor_id,
                          (SELECT accounting_date fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID)  accounting_date,
                          (SELECT PAYMENT_METHOD_CODE||'.'||CHECK_NUMBER
                             FROM AP_CHECKS_all aca ,AP_INVOICE_PAYMENTS_ALL aipa
                            WHERE aca.check_id = aipa.check_id
                              AND invoice_id = aia.invoice_id)  Particulars,
                          'Payments' Voucher_Type1,
                          (SELECT ABS(AMOUNT) fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID) credit_m,
                          ABS (SUM (0))   Debit_m,
                          aia.voucher_num voucher_number,
                           NULL invoice_num,
                          :P_SHOW_OPENING_BAL,
                          aia.invoice_id invoice_id_1,
                          aia.invoice_date invoice_date
                   FROM   apps.ap_invoices_all aia, apps.ap_suppliers asp
                  WHERE       1 = 1
                          AND aia.vendor_id = asp.vendor_id
                          AND aia.invoice_type_lookup_code = 'CREDIT'
                          AND AIA.ATTRIBUTE1 IS NULL
                       --and asp.vendor_name = 'ANMOL JEWELLERS.AJ'
                      --AND AIA.INVOICE_NUM ='OCT-2014-15 INV NO BRROSH00001 TO BRROSH00162'
                          -- AND aia.attribute2 = 'PURCHASE_RETURN' -- kamalakar 14th jan
                          AND aia.attribute2 IN ('PURCHASE_RETURN', 'DEBIT_NOTE')
                          AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
                          AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
                          AND EXISTS
                                (SELECT   '1'
                                   FROM   ap_supplier_sites_all assa
                                  WHERE   assa.vendor_id = aia.vendor_id
                                          AND assa.vendor_site_id =
                                                aia.vendor_site_id
                                          AND NVL (assa.attribute3, '~') =
                                                NVL (:p_supplier_type,
                                                     NVL (assa.attribute3, '~')))
                          AND NVL (aia.pay_group_lookup_code, 'X') =
                                NVL (NVL (:p_pay_group, aia.pay_group_lookup_code),
                                     'X')
                          AND EXISTS
                                (SELECT   '1'
                                   FROM   (SELECT   DISTINCT aida.invoice_id
                                             FROM   ap_invoice_distributions_all aida,
                                                    gl_code_combinations gcc
                                            WHERE   aida.line_type_lookup_code =
                                                       'ITEM'
                                                    AND aida.dist_code_combination_id =
                                                          gcc.code_combination_id
                                                    AND gcc.segment1 =
                                                          NVL (:p_showroom,
                                                               gcc.segment1)
                                           UNION
                                           SELECT   DISTINCT ail.invoice_id
                                             FROM   ap_invoice_lines_all ail,
                                                    gl_code_combinations gcc
                                            WHERE   ail.line_type_lookup_code =
                                                       'ITEM'
                                                    AND ail.default_dist_ccid =
                                                          gcc.code_combination_id
                                                    AND gcc.segment1 =
                                                          NVL (:p_showroom,
                                                               gcc.segment1)) x
                                  WHERE   x.invoice_id = aia.invoice_id)
                          AND EXISTS
                                (SELECT   '1'
                                   FROM   (SELECT   DISTINCT aida.invoice_id
                                             FROM   ap_invoice_distributions_all aida,
                                                    gl_code_combinations gcc
                                            WHERE   aida.line_type_lookup_code =
                                                       'ITEM'
                                                    AND aida.dist_code_combination_id =
                                                          gcc.code_combination_id
                                                    AND gcc.segment4 =
                                                          NVL (:p_service_line,
                                                               gcc.segment4)
                                           UNION
                                           SELECT   DISTINCT ail.invoice_id
                                             FROM   ap_invoice_lines_all ail,
                                                    gl_code_combinations gcc
                                            WHERE   ail.line_type_lookup_code =
                                                       'ITEM'
                                                    AND ail.default_dist_ccid =
                                                          gcc.code_combination_id
                                                    AND gcc.segment4 =
                                                          NVL (:p_service_line,
                                                               gcc.segment4)) x
                                  WHERE   x.invoice_id = aia.invoice_id)
               GROUP BY   asp.vendor_id,
                          aia.gl_date,
                          aia.voucher_num,
                          aia.invoice_num,
                          aia.voucher_num,
                          AIA.invoice_type_lookup_code,
                          aia.invoice_id,
                          aia.invoice_date*/
          SELECT   asp.vendor_id,
                                 --(SELECT accounting_date fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID)  accounting_date,
                                 p.accounting_date,
                   (SELECT    payment_method_code
                           || '.'
                           || check_number
                      FROM ap_checks_all aca, ap_invoice_payments_all aipa
                     WHERE aca.check_id = aipa.check_id
                       AND invoice_id = aia.invoice_id
                       AND ROWNUM < 2                         -- added 6th oct
                                     ) particulars,
                   'Payments' voucher_type1,
                                            --(SELECT ABS(AMOUNT) fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID) credit_m,
                                            ABS (SUM (p.amount)) credit_m,
                   ABS (SUM (0)) debit_m, aia.voucher_num voucher_number,
                   NULL invoice_num, :p_show_opening_bal,
                   aia.invoice_id invoice_id_1, aia.invoice_date invoice_date
              FROM apps.ap_invoices_all aia,
                   apps.ap_suppliers asp,
                   ap_invoice_payments_all p
             WHERE 1 = 1
               AND aia.vendor_id = asp.vendor_id
               AND aia.invoice_type_lookup_code = 'CREDIT'
               AND aia.attribute1 IS NULL
               AND aia.invoice_id = p.invoice_id
                --and asp.vendor_name = 'ANMOL JEWELLERS.AJ'
               --AND AIA.INVOICE_NUM ='OCT-2014-15 INV NO BRROSH00001 TO BRROSH00162'
                   -- AND aia.attribute2 = 'PURCHASE_RETURN' -- kamalakar 14th jan
               AND aia.attribute2 IN ('PURCHASE_RETURN', 'DEBIT_NOTE')
               AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
               AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
               AND EXISTS (
                      SELECT '1'
                        FROM ap_supplier_sites_all assa
                       WHERE assa.vendor_id = aia.vendor_id
                         AND assa.vendor_site_id = aia.vendor_site_id
                         AND NVL (assa.attribute3, '~') =
                                NVL (:p_supplier_type,
                                     NVL (assa.attribute3, '~')
                                    ))
               AND NVL (aia.pay_group_lookup_code, 'X') =
                      NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), 'X')
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
          GROUP BY asp.vendor_id,
                   p.accounting_date,
                   aia.voucher_num,
                   aia.invoice_num,
                   aia.voucher_num,
                   aia.invoice_type_lookup_code,
                   aia.invoice_id,
                   aia.invoice_date
          --- end
          UNION ALL
          SELECT   asp.vendor_id, aip.accounting_date,
                      ac.payment_method_code
                   || DECODE (ac.check_number,
                              NULL, NULL,
                              '.' || ac.check_number
                             ) particulars,
                   'Payments' voucher_type1, 0 credit_m,
                   ABS (  SUM (aip.amount)
                        + (SELECT NVL (SUM (aipa1.amount), 0)
                             FROM ap_invoices_all aia1,
                                  ap_invoice_payments_all aipa1,
                                  ap_checks_all aca1
                            WHERE 1=1--aia1.attribute1 = TO_CHAR (aia.invoice_id)
                              AND aia1.invoice_num like aia.invoice_num||'%TDS%CM%'
                              AND aia1.invoice_type_lookup_code = 'CREDIT'
                              AND aia1.invoice_id = aipa1.invoice_id
                              AND aipa1.check_id = aca1.check_id
                              AND (   aca1.void_date IS NULL
                                   OR aca1.void_date > :p_to_date
                                  ))
                       ) debit_m,
                   TO_CHAR (ac.check_number) voucher_number, NULL,
                   :p_show_opening_bal, NULL,
                   aip.accounting_date invoice_date
              FROM apps.ap_invoices_all aia,
                   apps.ap_suppliers asp,
                   apps.ap_invoice_payments_all aip,
                   apps.ap_checks_all ac,
                   apps.iby_payments_all ipa,
                   apps.ce_bank_branches_v cbb,
                   apps.ce_bank_accounts cba,
                   apps.ce_bank_acct_uses_all cbau
             WHERE 1 = 1
               AND aia.invoice_id = aip.invoice_id
               AND aia.vendor_id = asp.vendor_id
               AND aip.check_id = ac.check_id
               AND ac.payment_id = ipa.payment_id
               AND aia.invoice_type_lookup_code <> 'CREDIT'
               AND (ipa.void_date IS NULL OR ipa.void_date > :p_to_date)
               AND ac.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
               AND cbau.bank_account_id = cba.bank_account_id(+)
               AND cbb.branch_party_id(+) = cba.bank_branch_id
               AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
               AND aip.accounting_date BETWEEN :p_from_date AND :p_to_date
               AND EXISTS (
                      SELECT '1'
                        FROM ap_supplier_sites_all assa
                       WHERE assa.vendor_id = aia.vendor_id
                         AND assa.vendor_site_id = aia.vendor_site_id
                         AND NVL (assa.attribute3, '~') =
                                NVL (:p_supplier_type,
                                     NVL (assa.attribute3, '~')
                                    ))
               AND NVL (aia.pay_group_lookup_code, 'X') =
                      NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), 'X')
               AND    ac.payment_method_code
                   || DECODE (ac.check_number,
                              NULL, NULL,
                              '.' || ac.check_number
                             ) <> 'Prepayment'
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
          GROUP BY asp.vendor_id,
                   aip.accounting_date,
                   ac.check_number,
                   ac.payment_method_code,
                   aia.invoice_id,
   aia.invoice_num
          UNION ALL
          SELECT   asp.vendor_id, aip.accounting_date,
                      ac.payment_method_code
                   || DECODE (ac.check_number,
                              NULL, NULL,
                              '.' || ac.check_number
                             ) particulars,
                   'Refund' voucher_type1, ABS (SUM (aip.amount)) credit_m,
                   0 debit_m, aia.voucher_num voucher_number, aia.invoice_num,
                   :p_show_opening_bal, aia.invoice_id invoice_id_1,
                   aia.invoice_date invoice_date
              FROM apps.ap_invoices_all aia,
                   apps.ap_suppliers asp,
                   apps.ap_invoice_payments_all aip,
                   apps.ap_checks_all ac,
                   apps.ap_invoices_all ai2,
                   apps.ce_bank_branches_v cbb,
                   apps.ce_bank_accounts cba,
                   apps.ce_bank_acct_uses_all cbau
             WHERE 1 = 1
               AND aia.invoice_id = aip.invoice_id
               AND aia.vendor_id = asp.vendor_id
               AND aip.check_id = ac.check_id
               AND ac.payment_type_flag = 'R'
               AND aip.other_invoice_id = ai2.invoice_id(+)
               AND ac.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
               AND cbau.bank_account_id = cba.bank_account_id(+)
               AND cbb.branch_party_id(+) = cba.bank_branch_id
               AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
               AND aip.accounting_date BETWEEN :p_from_date AND :p_to_date
               AND    ac.payment_method_code
                   || DECODE (ac.check_number,
                              NULL, NULL,
                              '.' || ac.check_number
                             ) <> 'Prepayment'
               --&LP_SHOWROOM
               AND EXISTS (
                      SELECT '1'
                        FROM ap_supplier_sites_all assa
                       WHERE assa.vendor_id = aia.vendor_id
                         AND assa.vendor_site_id = aia.vendor_site_id
                         AND NVL (assa.attribute3, '~') =
                                NVL (:p_supplier_type,
                                     NVL (assa.attribute3, '~')
                                    ))
               AND NVL (aia.pay_group_lookup_code, 'X') =
                      NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), 'X')
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment1 =
                                                 NVL (:p_showroom,
                                                      gcc.segment1
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
               AND EXISTS (
                      SELECT '1'
                        FROM (SELECT DISTINCT aida.invoice_id
                                         FROM ap_invoice_distributions_all aida,
                                              gl_code_combinations gcc
                                        WHERE aida.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND aida.dist_code_combination_id =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )
                              UNION
                              SELECT DISTINCT ail.invoice_id
                                         FROM ap_invoice_lines_all ail,
                                              gl_code_combinations gcc
                                        WHERE ail.line_type_lookup_code
in('ITEM','MISCELLANEOUS')
                                          AND ail.default_dist_ccid =
                                                       gcc.code_combination_id
                                          AND gcc.segment4 =
                                                 NVL (:p_service_line,
                                                      gcc.segment4
                                                     )) x
                       WHERE x.invoice_id = aia.invoice_id)
          GROUP BY asp.vendor_id,
                   aip.accounting_date,
                      ac.payment_method_code
                   || DECODE (ac.check_number,
                              NULL, NULL,
                              '.' || ac.check_number
                             ),
                   'Refund',
                   aip.amount,
                   0,
                   aia.voucher_num,
                   aia.invoice_num,
                   aia.invoice_id,
                   aia.invoice_date)
ORDER BY invoice_date, invoice_num