Oracle Applications :Drill down from GL to AR Query
Below query is useful for drilling down from GL to AR invoices
SSELECT gir.je_header_id,
gir.je_line_num,
xte.source_id_int_1 Invoice_id,
rhead.trx_number Invoice_num,
rhead.trx_date Invoice_date,
xah.EVENT_TYPE_CODE,
xal.Accounting_class_code,
xal.ENTERED_DR,
gir.REFERENCE_9,
gir.REFERENCE_10,
xal.ENTERED_CR,
xah.ENTITY_ID,
gir.REFERENCE_5,
xah.EVENT_ID,
gir.REFERENCE_6,
xah.AE_HEADER_ID,
gir.REFERENCE_7
FROM GL_JE_HEADERS gjh,
gl_je_lines gjl,
GL_IMPORT_REFERENCES gir,
XLA_AE_LINES xal,
XLA_AE_HEADERS xah,
XLA.XLA_TRANSACTION_ENTITIES xte,
ra_customer_Trx_all rhead
WHERE rhead.customer_Trx_id = xte.source_id_int_1
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.ledger_id IN (ledger_id)
AND xah.application_id = 222
AND xah.entity_id = xte.entity_id
AND xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = (:customer_trx_id)
AND xte.ledger_id IN (ledger_id)
ORDER BY gir.je_line_num
Pass your Ledger id ,customer trx_id as parameters
