XLA tables in Oracle Fusion

XLA tables in Oracle Fusion

Hi friends, we are going to discuss about the XLA tables in Oracle Fusion. We will share the complete list of xla tables which helps to store the complete xla accounting data for all AP,AR,GL,FA modules. We have multiple xla tables in oracle fusion and in this post , i will try to explain each of these xla tables. I will also share the DB table joins between the xla tables which will help to link the xla tables in oracle fusion. We will also share the important sql queries too which will help to extract the xla data in oracle fusion. If i will compare with oracle apps r12 then there is no much difference in xla tables. XLA tables are completely same in Oracle apps r12 and in oracle fusion. XLA is the bridge between the subledger modules and with general ledger. All accounting data transfers from subledger modules (ap,ar,gl,fa) to gl module using this xla. Please find below the complete detail about XLA tables in Oracle Fusion.

Top 6 XLA tables in Oracle Fusion

Here below is the list of important xla tables in oracle fusion which helps to keep the xla accounting data in oracle fusion

1.XLA_AE_LINES

2.XLA_AE_HEADERS

3.XLA_EVENTS

4.XLA_TRANSACTION_ENTITIES

5.GL_IMPORT_REFERENCES

6.XLA_DISTRIBUTIONS_LINK

XLA tables in Oracle Fusion

XLA Tables Joins in Oracle Fusion

Here below is the xla tables join which helps to connect the xla tables in oracle fusion.

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)   

XLA_TRANSACTION_ENTITIES(ENTITY_ID)                           => XLA_EVENTS (ENTITY_ID)  

Detail XLA SQL query to extract the xla data in Oracle Fusion

Here below is the complete sql query which helps to extract the xla data in oracle fusion

select 

aia.INVOICE_NUM "AP Invoice Num" ,

aia.INVOICE_DATE "AP Invoice Date" ,

aia.INVOICE_AMOUNT ,

aia.INVOICE_CURRENCY_CODE ,

aia.GL_DATE ,

aia.PAYMENT_METHOD_CODE ,

xah.JE_CATEGORY_NAME "Journal Cateogry"

FROM ap.ap_invoices_all aia,

xla.xla_events xev,

xla.xla_ae_headers XAH,

xla.xla_ae_lines XAL,

GL_IMPORT_REFERENCES gir,

gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations gcc,

xla.xla_transaction_entities XTE

WHERE 1=1

AND XAH.je_category_name = 'Purchase Invoices'

AND XAH.gl_transfer_status_code = 'Y'

AND aia.INVOICE_ID = xte.source_id_int_1

AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID

AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID

AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID

AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM

AND xev.entity_id = xte.entity_id

AND xah.entity_id = xte.entity_id

AND xah.event_id = xev.event_id

AND XAH.ae_header_id = XAL.ae_header_id

AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID

AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

XLA tables in Oracle Fusion