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_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');
 -Happy Drilling
http://www.thotasuman.com/

2 comments:

  1. 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

    ReplyDelete
  2. Great Drilling, Really I like it

    ReplyDelete