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

/* Formatted on 5/18/2017 4:37:29 PM (QP5 v5.114.809.3010) */
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

/* Formatted on 5/18/2017 4:38:04 PM (QP5 v5.114.809.3010) */
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

/* Formatted on 5/18/2017 4:38:33 PM (QP5 v5.114.809.3010) */
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