In R12 all GL posted transactions are sits in xla_ae_headers, xla_ae_lines, xla_transaction_entities.
The below Query will give All AP Invoices posted to GL in R12
SELECT DISTINCT
AID.AMOUNT AMOUNT,
ASP.SEGMENT1 SupplierNo,
ASP.VENDOR_NAME SupplierName,
AI.INVOICE_NUM Invoice
FROM ap_invoices_all ai,
ap_suppliers asp,
ap_supplier_sites_all ass,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl_code_combinations gcc,
xla.xla_transaction_entities xte,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ai.invoice_id = ail.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ai.invoice_id = aid.invoice_id
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.entity_id = xah.entity_id
AND xah.ae_header_id = xal.ae_header_id
AND aid.dist_code_combination_id = gcc.code_combination_id
AND gcc.code_combination_id = xal.code_combination_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND ai.vendor_id(+) = asp.vendor_id
AND asp.vendor_id = ass.vendor_id
AND ass.vendor_site_id = ai.vendor_site_id
AND aid.amount != 0
and xal.gl_sl_link_id = gir.gl_sl_link_id
and gir.je_header_id = gjl.je_header_id
and gir.je_line_num = gjl.je_line_num
and gir.je_header_id = gjh.je_header_id
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
There is something missing in the query
ReplyDeleteHi Shiva,
DeleteThanks for the feedback.Please let me know what is missing and what expecting.I will update accordingly.
Anil
Query has performance issue...Pls check below points
Delete1. Don't use distinct in the query
2. Think no need of join GL Tables