Query to Link PO,AP,GL,TAX RATE & XLA

SELECT r.je_header_id je_header_id, r.je_line_num je_line_num,

       200 application_id, aeh.ledger_id set_of_books_id, 'INV' trx_class,

       l8.displayed_field trx_class_name, l1.displayed_field trx_type_name,

       i.invoice_type_lookup_code trx_type_c, i.invoice_num invoice_number,

       i.invoice_num trx_number_displayed, ael.description comments,

       fd.NAME doc_sequence_name, ael.accounted_cr accounted_cr,

       ael.accounted_dr accounted_dr, aeh.accounting_date accounting_date,

       ael.accounting_class_code acct_line_type,

       l3.meaning acct_line_type_name, aeh.ae_header_id aeh_id,

       'APID' ael_table, ael.code_combination_id code_combination_id,

          glcc.segment1|| '.'|| glcc.segment3|| '.' || glcc.segment2

       || '.'|| glcc.segment4 || '.'|| glcc.segment5 account_code,

       glcc.segment1 company_code, glcc.segment3 center_code,

       glcc.segment2 account_code, glcc.segment4 subaccount,

       glcc.segment5 cost_center_type, ael.currency_code currency_code,

       ael.currency_conversion_date currency_conversion_date,

       ael.currency_conversion_rate currency_conversion_rate,

       ael.currency_conversion_type currency_conversion_type,

       glct.user_conversion_type currency_user_conversion_type,

       ael.entered_cr entered_cr, ael.entered_dr entered_dr,

       aeh.gl_transfer_status_code gl_transfer_status,

       l4.displayed_field gl_transfer_status_name,

       l9.displayed_field transfer_status_detail_name,

       ael.source_id source_id, ael.source_table source_table,

       zr.tax_rate_name tax_code, 'SUPPLIER' vendor_type,

       ael.party_id vendor_id, v.segment1 vendor_number,

       v.vendor_name vendor_name, ael.party_site_id vendor_site_id,

       vs.vendor_site_code vendor_site_code, i.invoice_date invoice_date,

       i.invoice_id invoice_id, 'API' trx_hdr_table,

       aeh.je_category_name je_category,

       jc.user_je_category_name user_je_category_name,

       ael.last_update_date last_update_date,

       ael.last_updated_by last_updated_by, ael.creation_date creation_date,

       ael.created_by created_by, ael.last_update_login last_update_login,

       ael.request_id request_id,

       ael.program_application_id program_application_id,

       ael.program_id program_id, ael.program_update_date program_update_date,

       d.distribution_line_number trx_line_number,

       d.line_type_lookup_code trx_line_type,

       l2.displayed_field trx_line_type_name,

       ae.event_number accounting_event_number,

       ae.event_type_code accounting_event_type,

       l5.displayed_field accounting_event_type_name,

       i.invoice_currency_code invoice_currency, ph.segment1 po_order_number,

       pr.release_num po_order_release_num, rsh.receipt_num rcv_receipt_num,

       d.distribution_line_number distribution_line_number,

       d.amount distribution_amount, d.description dist_description,

       p.payment_num payment_number, p.reversal_flag payment_reversal_flag,

       c.check_number check_number, c.check_date check_date,

       ap_invoices_pkg.get_posting_status (i.invoice_id) posting_flag,

       i.pay_group_lookup_code pay_group, l13.displayed_field payment_status,

       fu_cre.user_name created_by_user_name,

       fu_upd.user_name updated_by_user_name, aeh.period_name period_anme,

       i.SOURCE invoice_source

  FROM ap_invoices_all i,

       ap_invoice_distributions_all d,

       ap_invoice_payments_all p,

       ap_checks_all c,

       ap_accounting_events_all ae,

       xla_ae_lines ael,

       xla_ae_headers aeh,

       gl_je_headers h,

       gl_je_lines jl,

       gl_import_references r,

       gl_je_categories jc,

       gl_daily_conversion_types glct,

       gl_code_combinations glcc,

       po_releases_all pr,

       po_headers_all ph,

       po_distributions_all pd,

       po_vendor_sites_all vs,

       po_vendors v,

       rcv_transactions rct,

       rcv_shipment_headers rsh,

       zx_lines_summary_v zls,

       zx_rates_vl zr,

       fnd_document_sequences fd,

       ce_statement_lines sl,

       ce_statement_headers sh,

       ce_statement_reconciliations csr,

       ap_lookup_codes l1,

       fnd_user fu_upd,

       fnd_user fu_cre

 WHERE i.invoice_id = d.invoice_id

   AND i.invoice_id = p.invoice_id

   AND c.check_id = p.check_id

   AND c.check_id = csr.reference_id

   AND i.invoice_id = ae.source_id

   AND ae.source_table = 'AP_INVOICES'

   AND aeh.ae_header_id = ael.ae_header_id

   AND aeh.application_id = ael.application_id

   AND ae.accounting_event_id = aeh.event_id

   AND jl.je_header_id = h.je_header_id

   AND r.je_header_id = jl.je_header_id

   AND r.je_line_num = jl.je_line_num

   AND ael.gl_sl_link_id = r.gl_sl_link_id

   AND jc.je_category_name = aeh.je_category_name

   AND ael.currency_conversion_type = glct.conversion_type(+)

   AND ael.code_combination_id = glcc.code_combination_id

   AND pr.po_release_id(+) = pd.po_release_id

   AND ph.po_header_id(+) = pd.po_header_id

   AND pd.po_distribution_id(+) = d.po_distribution_id

   AND ael.party_site_id = vs.vendor_site_id

   AND ael.party_id = v.vendor_id

   AND rsh.shipment_header_id(+) = rct.shipment_header_id

   AND d.rcv_transaction_id = rct.transaction_id(+)

   AND i.invoice_id = zls.trx_id(+)

   AND zls.tax_rate_id = zr.tax_rate_id(+)

   AND sh.doc_sequence_id = fd.doc_sequence_id(+)

   AND sl.statement_header_id = sh.statement_header_id(+)

   AND csr.statement_line_id = sl.statement_line_id(+)

   AND jl.je_header_id = csr.je_header_id(+)

   AND l1.lookup_code = i.invoice_type_lookup_code

   AND l1.lookup_type = 'INVOICE TYPE'

   AND i.created_by = fu_cre.user_id

   AND i.last_updated_by = fu_upd.user_id

      --Pradipta Behera