AR TO GL Tracking

SELECT *
FROM   ra_customer_trx_all rct
WHERE  rct.trx_number = Nvl(:P_TRX_NUM, '263')
=====================================================================================================================================================

SELECT *
FROM   ra_customer_trx_lines_all rctla
WHERE  rctla.customer_trx_id IN (SELECT rct.customer_trx_id
                                 FROM   ra_customer_trx_all rct
                                 WHERE  rct.trx_number = Nvl(:P_TRX_NUM, '263'))
=====================================================================================================================================================

SELECT *
FROM   ra_cust_trx_line_gl_dist_all rctlgda
WHERE  rctlgda.customer_trx_line_id IN (SELECT rctla.customer_trx_line_id
                                        FROM   ra_customer_trx_lines_all rctla
                                        WHERE  rctla.customer_trx_id IN
                                               (SELECT rct.customer_trx_id
                                                FROM   ra_customer_trx_all rct
                                                WHERE  rct.trx_number =
                                                       Nvl(:P_TRX_NUM, '263')))
=====================================================================================================================================================

SELECT *
FROM   xla_events xe
WHERE  xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all rctlgda
                       WHERE  rctlgda.customer_trx_line_id IN
                              (SELECT rctla.customer_trx_line_id
                               FROM   ra_customer_trx_lines_all rctla
                               WHERE  rctla.customer_trx_id IN
                                      (SELECT rct.customer_trx_id
                                       FROM   ra_customer_trx_all rct
                                       WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263'))))
=====================================================================================================================================================

SELECT *
FROM   xla_ae_headers xah
WHERE  xah.event_id IN (SELECT xe.event_id
                        FROM   xla_events xe
                        WHERE
       xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all
                              rctlgda
                       WHERE  rctlgda.customer_trx_line_id IN
       (SELECT rctla.customer_trx_line_id
        FROM   ra_customer_trx_lines_all rctla
        WHERE  rctla.customer_trx_id IN
               (SELECT rct.customer_trx_id
                FROM   ra_customer_trx_all rct
                WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263')))))
=====================================================================================================================================================

SELECT *
FROM   xla_ae_lines xal
WHERE  xal.ae_header_id IN (SELECT xah.ae_header_id
                            FROM   xla_ae_headers xah
                            WHERE  xah.event_id IN (SELECT xe.event_id
                                                    FROM   xla_events xe
                                                    WHERE
       xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all
                              rctlgda
                       WHERE  rctlgda.customer_trx_line_id
                              IN
       (SELECT rctla.customer_trx_line_id
        FROM   ra_customer_trx_lines_all rctla
        WHERE  rctla.customer_trx_id IN
               (SELECT rct.customer_trx_id
                FROM   ra_customer_trx_all rct
                WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263'))))))
=====================================================================================================================================================

SELECT *
FROM   xla_distribution_links xdl
WHERE  xdl.event_id IN (SELECT rctlgda.event_id
                        FROM   ra_cust_trx_line_gl_dist_all rctlgda
                        WHERE  rctlgda.customer_trx_line_id IN
                               (SELECT rctla.customer_trx_line_id
                                FROM   ra_customer_trx_lines_all rctla
                                WHERE  rctla.customer_trx_id IN
                                       (SELECT rct.customer_trx_id
                                        FROM   ra_customer_trx_all rct
                                        WHERE
                       rct.trx_number =
                       Nvl(:P_TRX_NUM, '263'))))
=====================================================================================================================================================

SELECT *
FROM   xla_transaction_entities xte
WHERE  xte.source_id_int_1 IN (SELECT rct.customer_trx_id
                               FROM   ra_customer_trx_all rct
                               WHERE  rct.trx_number = Nvl(:P_TRX_NUM, '263')
                                      AND xte.entity_code = 'TRANSACTIONS')
=====================================================================================================================================================

SELECT *
FROM   gl_import_references gir
WHERE  gir.gl_sl_link_id IN (SELECT xal.gl_sl_link_id
                             FROM   xla_ae_lines xal
                             WHERE  xal.ae_header_id IN (SELECT xah.ae_header_id
                                                         FROM
                                    xla_ae_headers xah
                                                         WHERE
                                    xah.event_id IN (SELECT xe.event_id
                                                     FROM   xla_events xe
                                                     WHERE
       xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all
                              rctlgda
                       WHERE  rctlgda.customer_trx_line_id
                              IN
       (SELECT rctla.customer_trx_line_id
        FROM   ra_customer_trx_lines_all rctla
        WHERE  rctla.customer_trx_id IN
               (SELECT rct.customer_trx_id
                FROM   ra_customer_trx_all rct
                WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263')))))))
=====================================================================================================================================================

SELECT *
FROM   gl_je_headers gjh
WHERE  gjh.je_header_id IN (SELECT gir.je_header_id
                            FROM   gl_import_references gir
                            WHERE  gir.gl_sl_link_id IN
                                   (SELECT xal.gl_sl_link_id
                                    FROM   xla_ae_lines xal
                                    WHERE  xal.ae_header_id IN
                                   (SELECT xah.ae_header_id
                                    FROM   xla_ae_headers xah
                                    WHERE
                                   xah.event_id IN (SELECT xe.event_id
                                                    FROM   xla_events xe
                                                    WHERE
       xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all
                              rctlgda
                       WHERE  rctlgda.customer_trx_line_id
                              IN
       (SELECT rctla.customer_trx_line_id
        FROM   ra_customer_trx_lines_all rctla
        WHERE  rctla.customer_trx_id IN
               (SELECT rct.customer_trx_id
                FROM   ra_customer_trx_all rct
                WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263'))))))))
=====================================================================================================================================================

SELECT *
FROM   gl_je_lines gjl
WHERE  gjl.je_header_id IN (SELECT gjh.je_header_id
                            FROM   gl_je_headers gjh
                            WHERE  gjh.je_header_id IN (SELECT gir.je_header_id
                                                        FROM
                                   gl_import_references gir
                                                        WHERE
                                   gir.gl_sl_link_id IN
                                   (SELECT xal.gl_sl_link_id
                                    FROM   xla_ae_lines xal
                                    WHERE  xal.ae_header_id IN
                                   (SELECT xah.ae_header_id
                                    FROM   xla_ae_headers xah
                                    WHERE
                                   xah.event_id IN (SELECT xe.event_id
                                                    FROM   xla_events xe
                                                    WHERE
       xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all
                              rctlgda
                       WHERE  rctlgda.customer_trx_line_id
                              IN
       (SELECT rctla.customer_trx_line_id
        FROM   ra_customer_trx_lines_all rctla
        WHERE  rctla.customer_trx_id IN
               (SELECT rct.customer_trx_id
                FROM   ra_customer_trx_all rct
                WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263')))))))))
=====================================================================================================================================================

SELECT *
FROM   gl_je_batches gjb
WHERE  gjb.je_batch_id IN (SELECT gir.je_batch_id
                           FROM   gl_import_references gir
                           WHERE  gir.gl_sl_link_id IN (SELECT xal.gl_sl_link_id
                                                        FROM   xla_ae_lines xal
                                                        WHERE
                                  xal.ae_header_id IN
                                  (SELECT xah.ae_header_id
                                   FROM
                                                       xla_ae_headers xah
                                                                WHERE
                                  xah.event_id IN (SELECT xe.event_id
                                                   FROM   xla_events xe
                                                   WHERE
       xe.event_id IN (SELECT rctlgda.event_id
                       FROM   ra_cust_trx_line_gl_dist_all
                              rctlgda
                       WHERE  rctlgda.customer_trx_line_id
                              IN
       (SELECT rctla.customer_trx_line_id
        FROM   ra_customer_trx_lines_all rctla
        WHERE  rctla.customer_trx_id IN
               (SELECT rct.customer_trx_id
                FROM   ra_customer_trx_all rct
                WHERE
                      rct.trx_number =
                      Nvl(:P_TRX_NUM, '263'))))))))
=====================================================================================================================================================