Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA

SELECT   SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount,
         invoice_num
    FROM ap_invoices_all aia,
         ap_invoice_payments_all aipa,
         ap_checks_all aca,
         xla.xla_transaction_entities xte,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl.gl_import_references gir,
         gl_je_lines gjl,
         gl_je_headers gjh
   WHERE 1 = 1
     AND aia.invoice_id = aipa.invoice_id
     AND aipa.check_id = aca.check_id
     AND aca.check_id = NVL (“SOURCE_ID_INT_1”, (-99))
     AND xte.entity_code = ‘AP_PAYMENTS’
     AND xte.application_id = 200
     AND xte.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_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 aia.invoice_num = ‘INVNO4567’  –Invoice Num
GROUP BY aia.invoice_num, aia.invoice_amount
  • October 10, 2014 | 28 views