XLA to GL Link

XLA to GL Table Links

---

Subledger is a subset of general ledger which contain accounting information but do not have any chart of accounts. It is linked from source table and move accounting details to GL after final accounting. 

Subledger Tables

---

XLA_TRANSACTION_ENTITIES

XLA_EVENTS

XLA_AE_HEADERS

XLA_AE_LINES

General Ledger tables

---

GL_IMPORT_REFERENCES

GL_JE_HEADERS

GL_JE_LINES

GL_JE_BATCHES

select * from  xla_transaction_entities where transaction_number =250619 -- For entity_id

select * from  xla_events where event_id = 373021620  --entity_id = 211817231

select * from  xla_ae_headers  where event_id = 373021620  --event_id from xla_events

select * from  xla_ae_lines where ae_header_id = 465629469  --ae_header_id from xla_ae_headers

select * from  gl_import_references where gl_sl_link_id in (1100155677)  --gl_sl_link_id from xla_ae_lines

select * from  gl_je_headers where je_header_id =808500 -- je_header_id from gl_import_references

select * from  gl_je_batches where je_batch_id = 811239

Query

SELECT xte.source_id_int_1 ,

       xte.transaction_number,

   gjh.name journal_name,

   NVL(gjl.accounted_dr,0) accounted_dr,

   NVL(gjl.accounted_cr,0) accounted_cr,

   gjb.name journal_batch,

   gjl.je_line_num,

   gcc.segment1||'.'||

   gcc.segment2||'.'||

   gcc.segment3||'.'||

   gcc.segment4||'.'||

   gcc.segment5||'.'||

   gcc.segment6 account_code

  FROM xla_transaction_entities xte,

       xla_events xe,

   xla_ae_headers xah,

   xla_ae_lines xal,

   gl_import_references gir,

   gl_je_headers gjh,

   gl_je_lines gjl,

   gl_je_batches gjb,

   gl_code_combinations gcc

 WHERE xte.entity_id = xe.entity_id

   AND xte.application_id = xe.application_id

   AND xte.entity_code = 'TRANSACTIONS'

   AND xte.application_id = 222  --Receivables

   AND xe.entity_id = xah.entity_id

   AND xe.event_id = xah.event_id

   AND xah.ae_header_id = xal.ae_header_id

   AND xal.gl_sl_link_id = gir.gl_sl_link_id

   AND gir.je_header_id = gjh.je_header_id

   AND gjh.je_header_id = gjl.je_header_id

   AND gir.je_line_num = gjl.je_line_num

   AND gjh.je_batch_id = gjb.je_batch_id

   AND xah.application_id = 222  --Receivables

   AND xah.application_id = xal.application_id

   AND gjl.code_combination_id = gcc.code_combination_id;