Query to Link Shipments, AP Invoice, GL and SLA Tables: R12 AP GL RCV XLA

SELECT gjh.doc_sequence_value vch_num, gjl.effective_date, xal.currency_code,
       xal.currency_conversion_rate con_rate, xal.entered_dr entered_dr,
       xal.entered_cr entered_cr, xal.accounted_dr amount_dr,
       xal.accounted_cr amount_cr, xal.description, aia.invoice_num,
       aia.SOURCE, rsh.receipt_num
  FROM ap_invoices_all aia,
       ap_invoice_lines_all aila,
       ap_invoice_distributions_all aida,
       xla.xla_transaction_entities xte,
       xla_ae_headers xah,
       xla_ae_lines xal,
       gl_import_references gir,
       gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations glcc,
       rcv_transactions rt,
       rcv_shipment_headers rsh
 WHERE aia.invoice_id = aila.invoice_id(+)
   AND aila.invoice_id = aida.invoice_id(+)
   AND aila.line_number = aida.invoice_line_number
   AND aila.line_number = 1
   AND aida.invoice_line_number = 1
   AND aia.doc_sequence_value = ‘123456’ –Invoice Doc Seq Value
   AND NVL (xte.source_id_int_1, (-99)) = aia.invoice_id
   AND xte.entity_code = ‘AP_INVOICES’
   AND xte.application_id = 200
   AND xte.ledger_id = 1001
   AND xte.entity_id = xah.entity_id
   AND xah.ae_header_id(+) = xal.ae_header_id
   AND glcc.code_combination_id = gjl.code_combination_id
   AND xal.gl_sl_link_id(+) = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjl.je_header_id = gjh.je_header_id
   AND glcc.segment3 = NVL (:p_acc, glcc.segment3) –GL CC Segment Value   AND gjl.effective_date BETWEEN :p_from_date AND :p_to_date –GL Lines Effective Date   AND gjh.actual_flag = ‘A’
   AND gjh.je_source = ‘Payables’
   AND rt.transaction_id(+) = aida.rcv_transaction_id
   AND rt.shipment_header_id = rsh.shipment_header_id(+)
  • October 10, 2014 | 23 views