In this post you’ll see query which shows how to link between GL and AR transactions in Oracle ERP Cloud, this is a common requirements comes from clients side to show AR transactions number at GL journal entry level.
SELECT gjh.je_header_id
,rct.trx_number
,rct.ct_reference contract
FROM gl_je_headers gjh
join gl_je_lines gjl on (gjh.je_header_id = gjl.je_header_id)
join gl_import_references gir on (gir.je_header_id = gjl.je_header_id and gir.je_line_num = gjl.je_line_num)
join xla_ae_lines xal on (xal.gl_sl_link_id = gir.gl_sl_link_id and xal.GL_SL_LINK_TABLE = gir.GL_SL_LINK_TABLE)
join xla_ae_headers xah on (xah.APPLICATION_ID = xal.APPLICATION_ID and xah.AE_HEADER_ID = xal.AE_HEADER_ID)
join xla_events xe on (xe.event_id = xah.event_id)
join xla_transaction_entities xte on (xte.entity_id = xe.entity_id)
join ra_customer_trx_all rct on (rct.customer_trx_id = xte.SOURCE_ID_INT_1)
WHERE gjh.je_header_id = 181003