XLA Linking Between Oracle Modules and General Ledger (GL)

XLA Linking Between Oracle Modules and General Ledger (GL)

 

In this post , i will share the XML linking between all the oracle modules to GL. We all know that all the Data flows from Different modules to General Ledger through XLA. XLA is the only link between GL and other sub-ledger modules.

 

XLA linking Between AR and GL

 

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 = 'A'

 

 

 

XLA linking Between AP and GL

 

SELECT aia.INVOICE_ID ,

aia.INVOICE_NUM ,

aia.INVOICE_DATE ,

aia.INVOICE_AMOUNT ,

aia.INVOICE_CURRENCY_CODE ,

aia.PAYMENT_CURRENCY_CODE ,

aia.GL_DATE ,

xah.PERIOD_NAME ,

aia.PAYMENT_METHOD_CODE ,

xah.JE_CATEGORY_NAME

FROM ap.ap_invoices_all aia,

xla.xla_transaction_entities XTE,

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
WHERE aia.INVOICE_ID = xte.source_id_int_1

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 XAH.je_category_name = 'Purchase Invoices'

AND XAH.gl_transfer_status_code = 'Y'

AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID

AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

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

 

 

XLA LINKING BETWEEN FA AND GL

 

SELECT *

FROM

GL_JE_HEADERS GJH,

GL_JE_LINES GJL,

GL_IMPORT_REFERENCES GIR,

XLA_AE_LINES XAL,

XLA_AE_HEADERS XAH,

FA_TRANSACTION_HEADERS FTH,

FA_ADJUSTMENTS FAA
 
WHERE

GJH.JE_HEADER_ID=GJL.JE_HEADER_ID

AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID

AND GJL.JE_LINE_ID=GIR.JE_LINE_ID

AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID

AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID

AND XAH.EVENT_ID=FTH.EVENT_ID

AND FTH.TRANSACTION_HEADER_ID=FAA.TRANSACTION_HEADER_ID