Link between xla and gl tables

Link between xla and gl tables

 

In this post , We will discuss about link between xla and gl tables. We all know that accounting from subledgers transfer to general ledger through xla /sl module. So to fetch the data between xla and gl tables we need to use the link between xla and gl tables.

 XLA Tables used in the link between xla and gl tables

GL_JE_BATCHES (je_batch_id)                                   => GL_JE_HEADERS (je_batch_id)

GL_JE_HEADERS (je_header_id)                                  => GL_JE_LINES (je_header_id)

GL_JE_LINES (je_header_id,  je_line_num)                      => GL_IMPORT_REFERENCES (je_header_id, je_line_num)

GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)

XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id) 

XLA_AE_HEADERS (application_id, event_id)                     => XLA_EVENTS (application_id, event_id)   

 

Sql query of Link between xla and gl tables

 

SELECT GLB.NAME ,

GLB.description ,

gjh.je_category,

gjh.je_source,

gjh.period_name ,

gjh.NAME ,

gjh.status ,

gjh.description ,

gjl.je_line_num

FROM gl_je_batches GLB,

gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations_kfv glcc,

gl_import_references gir,

xla_ae_lines xlal,

xla_ae_headers xlah,

xla_events xlae,

xla_transaction_entities xlate,
ra_customer_trx_all rct
WHERE GLB.je_batch_id = gjh.je_batch_id

AND gjh.je_header_id = gjl.je_header_id

AND xlal.code_combination_id = glcc.code_combination_id

AND gjl.je_header_id = gir.je_header_id

AND gjl.je_line_num = gir.je_line_num

AND gir.gl_sl_link_table = xlal.gl_sl_link_table

AND gir.gl_sl_link_id = xlal.gl_sl_link_id

AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id

AND xlae.entity_id = xlate.entity_id

AND xlae.application_id = xlate.application_id

AND gjh.je_source = 'Receivables'

AND rct.trx_number = xlate.transaction_number

AND xlate.transaction_number = :trx_number;

AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID

AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID

AND gjh.STATUS = 'P'

AND gjh.Actual_flag = '