Payables Transfer to GL, SQL query to drilldown AP Invoice, Payment , Journal details using GL_IMPORT_REFERENCES.GL_SL_LINK_ID

Following query retrives data

Parameters:

 P_FROM_ACCTG_DATE -- Format (DD-MON-RRRR) -- Required

 P_TO_ACCTG_DATE -- Format (DD-MON-RRRR) -- Required

 P_BANK_ACCOUNT_NAME -- Optional

 P_SET_OF_BOOKS_ID -- Required

 P_CODE_COMBINATION_ID -- Optional

 P_ORG_ID -- Required

SELECT
/* + leading(AAE), index(AAE, AP_ACCOUNTING_EVENTS_N3) */
       NVL (jb.NAME, RPAD (' ', 100, ' ')) c_batch_name_ba, --journal entry batch
       NVL (jh.NAME, RPAD (' ', 100, ' ')) c_header_name_ba, --journal entry header
       ou.NAME c_operating_unit_ba, ac.bank_account_name c_bank_account_ba, -- bank account name
       ac.currency_code c_pmt_currency_ba,             --payment currency code
       bc.currency_code c_bank_currency,  -- Bank Account currency code
       DECODE (acs.NAME, NULL, alc1.displayed_field,acs.NAME) c_payment_document_ba,    --payment document
       alc.displayed_field c_nls_disb_type_ba,             --disbursement type
       ai.invoice_num c_invoice_number,                      -- Invoice Number
       ac.check_number c_payment_number_ba,  --payment/check number
       ac.check_date c_payment_date_ba,      --payment date
       ac.amount c_check_amount_ba,          --payment amount
       ac.vendor_name c_supplier_name_ba,    --supplier name
       ael.entered_dr c_entered_dr_ba,       --entered dr, display net debit only
       ael.entered_cr c_entered_cr_ba,       --entered cr
       ael.accounted_dr c_accounted_dr_ba,   --accounted dr, display net debit only
       ael.accounted_cr c_accounted_cr_ba,   --accounted cr
       ael.ae_line_id c_ae_line_id,
       ael.gl_sl_link_id c_gl_sl_link_id
  FROM hr_operating_units ou,
       ap_checks_all ac,
       ap_check_stocks_all acs,
       gl_je_batches jb,
       gl_import_references ir,
       gl_je_headers jh,
       gl_code_combinations cc,
       ap_accounting_events_all aae,
       ap_ae_headers_all aeh,
       ap_ae_lines_all ael,
       ap_lookup_codes alc,
       ap_lookup_codes alc1,
       ap_system_parameters_all asp,
       ap_bank_accounts_all bc,-- This can be replaced with
       ap_invoices_all ai,
       ap_invoice_payments_all aip
 WHERE aae.accounting_event_id = aeh.accounting_event_id
   AND aeh.ae_header_id = ael.ae_header_id
   AND ael.code_combination_id = cc.code_combination_id
   AND aae.source_table = 'AP_CHECKS'
   AND aeh.gl_transfer_flag = 'Y'
   AND jb.je_batch_id = ir.je_batch_id
   AND jh.je_header_id = ir.je_header_id
   AND ir.gl_sl_link_table = 'APECL'
   AND bc.bank_account_id = ac.bank_account_id
   AND aip.check_id = ac.check_id
   AND aip.invoice_id = ai.invoice_id
   AND aip.org_id = ai.org_id
   AND aip.org_id = ac.org_id
   AND ir.gl_sl_link_id = ael.gl_sl_link_id
   AND aae.source_id = ac.check_id
   AND ou.organization_id = ael.org_id
   AND ac.currency_code = bc.currency_code
   AND acs.check_stock_id(+) = ac.check_stock_id
   AND aae.accounting_date BETWEEN :p_from_acctg_date AND :p_to_acctg_date
   AND asp.set_of_books_id = aeh.set_of_books_id
   AND aeh.set_of_books_id = :p_set_of_books_id
   AND (   (    (:p_bank_account_name IS NOT NULL)
            AND (ac.bank_account_name = :p_bank_account_name)
           )
        OR (    (:p_bank_account_name IS NULL)
            AND (ac.bank_account_name = ac.bank_account_name)
           )
       )
   AND (   (    (:p_code_combination_id IS NOT NULL)
            AND (ael.code_combination_id = :p_code_combination_id)
            AND (   (    (NVL (asp.future_dated_pmt_liab_relief, 'ISSUE') =
                                                                       'ISSUE'
                         )
                     AND (ael.ae_line_type_code = 'LIABILITY')
                    )
                 OR (    (NVL (asp.future_dated_pmt_liab_relief, 'ISSUE') <>
                                                                       'ISSUE'
                         )
                     AND (ael.ae_line_type_code IN
                                              ('LIABILITY', 'FUTURE PAYMENT')
                         )
                    )
                )
           )
        OR (    (:p_code_combination_id IS NULL)
            AND (ael.ae_line_type_code = 'LIABILITY')
            AND (NVL (asp.future_dated_pmt_liab_relief, 'ISSUE') = 'ISSUE')
           )
        OR (    (:p_code_combination_id IS NULL)
            AND (NVL (asp.future_dated_pmt_liab_relief, 'ISSUE') <> 'ISSUE')
            AND (ael.ae_line_type_code IN ('LIABILITY', 'FUTURE PAYMENT'))
           )
       )
   AND alc.lookup_type(+) = 'DISBURSEMENT TYPE'
   AND alc.lookup_code(+) = acs.disbursement_type_lookup_code
   AND alc1.lookup_type = 'PAYMENT TYPE'
   AND alc1.lookup_code = 'R'
   AND ac.org_id = :p_org_id;