Data Query combining AP, GL

Use the reference columns i.e REFERENCE_1 to REFERENCE_10 of gl_je_lines table to join the AP tables with the GL tables like the below query.



SELECT gjl.reference_1,

gjl.reference_2,

gjl.reference_4,

gjl.reference_5,

gjl.reference_10,

aia.doc_sequence_value,

aia.invoice_type_lookup_code,

aia.description invoice_description,

ada.prepay_distribution_id,

gjl.description,

aca.doc_sequence_value payment_doc_seq_number,

ada.invoice_id,

aia.doc_sequence_value

FROM gl.gl_je_headers gjh,

gl.gl_je_lines gjl,

ap.ap_checks_all aca,

ap.ap_invoices_all aia,

ap.ap_invoice_distributions_all ada

WHERE gjl.je_header_id = gjh.je_header_id

AND aia.invoice_id(+) = gjl.reference_2

AND ada.invoice_id(+) = gjl.reference_2

AND ada.invoice_id(+) = gjl.reference_3

AND aca.check_id(+) = gjl.reference_3;