Query to Link PO, AP, GL, ZX, CE, RCV & XLA

   SELECT glimp.je_header_id je_header_id, glimp.je_line_num je_line_num,

          xal.accounted_cr accounted_cr, xal.accounted_dr accounted_dr,

          xal.entered_cr entered_cr, xal.entered_dr entered_dr,

          xah.accounting_date accounting_date,

          xal.accounting_class_code acct_line_type,

          xal.code_combination_id code_combination_id,

             gcc.segment1

          || '.'

          || gcc.segment3

          || '.'

          || gcc.segment2

          || '.'

          || gcc.segment4

          || '.'

          || gcc.segment5 account_combination,

          gcc.segment1 company_code, gcc.segment3 center_code,

          gcc.segment2 account_code, gcc.segment4 subaccount,       

          gcc.segment5 cost_center_type,

          xal.description comments,

          xah.gl_transfer_status_code gl_transfer_status,        

          xal.party_id vendor_id,                    

          v.segment1 vendor_number,

          v.vendor_name vendor_name, xal.party_site_id vendor_site_id,

          vs.vendor_site_code vendor_site_name,

          c.check_id check_id,

          c.check_number check_number,

          ae.event_number accounting_event_number,

          ae.event_type_code accounting_event_type,

          xal.ae_line_num accounting_line_number,

          xah.je_category_name je_category,

          jc.user_je_category_name user_je_category_name,

          c.currency_code trx_hdr_currency,

          xal.last_update_date last_update_date,

          xal.last_updated_by last_updated_by,

          xal.creation_date creation_date, xal.created_by created_by,

          xal.last_update_login last_update_login, xal.request_id request_id,

          c.bank_account_name bank_account_name            

          i.invoice_num invoice_number,                                     

          i.invoice_currency_code invoice_currency,

          d.distribution_line_number invoice_line_number,

          d.line_type_lookup_code invoice_line_type,

          ph.segment1 po_order_number, pr.release_num po_order_release_num,

          rsh.receipt_num rcv_receipt_num

     FROM xla_ae_headers xah,             

          xla_ae_lines xal,                

          gl_import_references glimp,

          gl_je_headers glh,               

          gl_je_lines gll,                 

          gl_code_combinations gcc,

          gl_daily_conversion_types glct,

          gl_je_categories jc,

          ap_checks_all c,

          ap_invoices_all i,

          ap_invoice_payments_all p,

          ap_invoice_distributions_all d,

          ap_invoice_lines_all l,

          ap_accounting_events_all ae,

          po_vendor_sites_all vs,

          po_vendors v,

          po_distributions_all pd,

          po_headers_all ph,

          po_releases_all pr,

          ce_statement_lines sl,

          ce_statement_headers sh,

          rcv_transactions rct,

          rcv_shipment_headers rsh,

          ce_statement_reconciliations csr,

          zx_lines_summary_v zls,

          zx_rates_vl zr,

          fnd_document_sequences fd,

          fnd_document_sequences fd2,

          fnd_flex_values_vl ffvv1,

          ap_lookup_codes l1

    WHERE xal.application_id = xah.application_id

      AND xah.ae_header_id = xal.ae_header_id

      AND glimp.je_header_id = glh.je_header_id

      AND glimp.je_line_num = gll.je_line_num

      AND glh.je_header_id = gll.je_header_id

      AND glimp.gl_sl_link_id = xal.gl_sl_link_id

      AND gcc.code_combination_id = xal.code_combination_id

      AND xal.currency_conversion_type = glct.conversion_type(+)

      AND jc.je_category_name = xah.je_category_name

      AND d.invoice_id = i.invoice_id

      AND i.invoice_id = p.invoice_id

      AND c.check_id = ae.source_id

      AND c.check_id = p.check_id

     AND ae.accounting_event_id = xah.event_id

      AND xal.party_id = v.vendor_id

      AND xal.party_site_id = vs.vendor_site_id

      AND ph.po_header_id(+) = pd.po_header_id

      AND pd.po_distribution_id(+) = d.po_distribution_id

      AND pr.po_release_id(+) = pd.po_release_id

      AND sl.statement_header_id = sh.statement_header_id(+)

      AND csr.statement_line_id = sl.statement_line_id(+)

      AND rsh.shipment_header_id(+) = rct.shipment_header_id

      AND d.rcv_transaction_id = rct.transaction_id(+)

      and l.invoice_id=i.invoice_id

      and l.line_number=1

      AND csr.reference_type(+) = 'PAYMENT'

      AND csr.current_record_flag(+) = 'Y'

      AND gll.je_header_id(+) = csr.je_header_id

      AND c.check_id = csr.reference_id(+)

      AND i.invoice_id = zls.trx_id(+)

      AND zls.tax_rate_id = zr.tax_rate_id(+)

      AND c.doc_sequence_id = fd.doc_sequence_id(+)

      AND sh.doc_sequence_id = fd2.doc_sequence_id(+)

      AND ae.source_table = 'AP_CHECKS'   

      AND l1.lookup_code = c.payment_method_lookup_code

      AND l1.lookup_type = 'PAYMENT METHOD'

      AND DECODE (xal.source_table,

                  'AP_INVOICE_PAYMENTS', xal.source_id,

                  NULL

                 ) = p.invoice_payment_id

      and l.line_number=d.invoice_line_number  

      

      --Dhiraj Jha