It is linked with GL_JE_BATCHES by JE_BATCH_ID and linked with GL_JE_HEADERS, GL_JE_LINES by JE_HEADER_ID
This table is linked with XLA related table by reference column.
REFERENCE_5 is ENTITY_ID in XLA table.
REFERENCE_6 is ACCOUNTING_EVENT_ID in XLA table.
REFERENCE_7 is AE_HEADER_ID in XLA table.
The most important column in GL_IMPORT_REFERENCES is GL_SL_LINK_ID, you also can find this column in table XLA_AE_LINES
The link in AP & GL is table GL_IMPORT_REFERENCES, key column is GL_SL_LINK_ID.
SELECT aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjl.ENTERED_DR "Entered DR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
xah.JE_CATEGORY_NAME "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
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'
AND gjh.CURRENCY_CODE = 'USD'
AND aia.Invoice_id = &Invoice_Id;
Where &Invoice_Id can be identified from XLA_TRANSACTION_ENTITIES.source_id_int_1 in order to verify the link.
=========================================================================
AR->SLA->GL
=========================================================================
ACTION: COMPLETE TRANSACTION:
RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID
RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1
XLA_TRANSACTION_ENTITIES.ENTITY_ID = XLA_EVENTS.ENTITY_ID
ACTION: RUN CREATE ACCOUNTING:
RA_CUST_TRX_LINE_GL_DIST_ALL.CUST_TRX_LINE_GL_DIST_ID = XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1
XLA_AE_LINES.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID
XLA_AE_HEADERS.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID
ACTION: RUN TRANSFER TO GL:
XLA_AE_LINES.GL_SL_LINE_ID = GL_JE_LINES.GL_SL_LINK_ID
XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID
GL_IMPORT_REFERENCES.HE_HEADER_ID = GL_JE_HEADERS.JE_HEADER_ID
SELECT GLB.NAME batch_name,
GLB.description batch_description,
gjh.je_category,
gjh.je_source,
gjh.period_name je_period_name,
gjh.NAME journal_name,
gjh.status journal_status,
gjh.description je_description,
gjl.je_line_num line_number
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 = &p_trx_num;
