Link between GL and AR through XLA

The main link to bind information together is the GL_SL_LINK_ID. This field exists in GL_JE_LINES, GL_IMPORT_REFERENCES and XLA_AE_LINES tables.

Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction.

Query 1: For complete Transaction
SELECT *
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE
WHERE
RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_ID = XE.ENTITY_ID

Query 2: After Create accounting Run
SELECT *
FROM
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_DISTRIBUTION_LINKS XDL,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH
WHERE
RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID

Query 3 : After Run Transfer to GL
SELECT *
FROM
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH
WHERE
XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID