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
  • October 14, 2015 | 17 views