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
