Link between ap_invoices_all and xla tables

 

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 ,

ia.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