GL XLA Drill Down

Oracle Applications R12: GL XLA Drill Down


Below are the queries used for Drill Down between GL(General Leder) and SLA(Sub ledger Accounting) in R12
1.select * from ap_invoices_all where invoice_num = ;
--invoice_id 158962
 Now Query for entity Id

2. select * from xla.xla_transaction_entities where source_id_int_1 = 158962
 --entity_id 603052
 Next get the events

3. select * from xla_events where entity_id=603052
 --event_id 658617 658618

4. select * from xla_ae_headers where event_id in (658617, 658618);
 --ae_header_id 1758986 1758987 1758988 1758989 1758990 1758991

5. select gl_sl_link_id from xla_ae_lines where ae_header_id in (1758986, 1758987, 1758988, 1758989, 1758990, 1758991);
  --gl_sl_link_id is obtained

6.select * from gl_import_references where gl_sl_link_id in (select gl_sl_link_id from xla_ae_lines where ae_header_id in (1758986, 1758987, 1758988, 1758989, 1758990, 1758991))
and gl_sl_link_table ='XLAJEL';

7.select * from gl_je_batches where je_batch_id in
 (select je_batch_id from gl_import_references where gl_sl_link_id in (select gl_sl_link_id from xla_ae_lines where ae_header_id in (1758986, 1758987, 1758988, 1758989, 1758990, 1758991))
and gl_sl_link_table ='XLAJEL');

8.select * from gl_je_headers where je_header_id in
  (select je_header_id from gl_import_references where gl_sl_link_id in (select gl_sl_link_id from xla_ae_lines where ae_header_id in (1758986, 1758987, 1758988, 1758989, 1758990, 1758991))
and gl_sl_link_table ='XLAJEL');

9.select * from gl_je_lines where je_header_id in
  (select je_header_id from gl_import_references where gl_sl_link_id in (select gl_sl_link_id from xla_ae_lines where ae_header_id in (1758986, 1758987, 1758988, 1758989, 1758990, 1758991))
and gl_sl_link_table ='XLAJEL');

Pls note :
steps 8 and 9 do not work after the subledger transaction posting to the GL is complete, because gl_je_lines.gl_sl_link_id and gl_sl_link_table are set to null

Reference
http://toracleapps.blogspot.com