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

SELECT          /*+ Index(xdl XLA_DISTRIBUTION_LINKS_N3) */
       DISTINCT gjh.doc_sequence_value gl_vou_num, ac.check_id, xal.source_id,
                aipa.invoice_payment_id, gjl.effective_date accounting_date,
                xal.accounted_dr, xal.accounted_cr, aps.segment1 vendor_code,
                aps.vendor_name vendor_name, aipa.invoice_id, aia.invoice_num,
                aia.invoice_date, aia.invoice_currency_code cur,
                aia.invoice_amount inv_amt_frc,
                aia.exchange_rate inv_exc_rate,
                aia.exchange_date inv_exc_date,
                aia.doc_sequence_value ap_vou_num, aipa.amount amt_paid,
                aipa.exchange_rate paid_exc_rate,
                aipa.exchange_date paid_exc_date, ac.check_number
               FROM gl_je_headers gjh,
                gl_je_lines gjl,
                xla_ae_headers xah,
                xla_ae_lines xal,
                xla.xla_transaction_entities xte,
                ap.ap_checks_all ac,
                gl_code_combinations glcc,
                ap_invoice_payments_all aipa,
                ap_invoices_all aia,
                ap_suppliers aps,
                ap_invoice_lines_all aila,
                ap_invoice_distributions_all aida,
                gl_import_references gir,
                xla_distribution_links xdl,
                ap_payment_hist_dists aphd
          WHERE 1 = 1
            AND gjh.je_header_id = gjl.je_header_id
            AND gjh.ledger_id = ‘1001’
            AND gjh.je_source = ‘Payables’
            AND gjh.je_category = ‘Payments’
            AND gjl.status = ‘P’
            AND xal.accounting_class_code = ‘LOSS’
            AND xdl.accounting_line_code = ‘AP_LOSS_PMT’
            AND gjl.effective_date BETWEEN :p_from_date AND :p_to_date –GL Line Effective Dates
            AND aipa.accounting_event_id = xah.event_id
            AND NVL (xte.source_id_int_1, (-99)) = TO_CHAR (ac.check_id)
            AND xal.code_combination_id = glcc.code_combination_id
            AND xal.ae_header_id = xah.ae_header_id
            AND xte.application_id = xah.application_id
            AND xte.application_id = 200
            AND xte.ledger_id = 1001
            AND xte.entity_id = xah.entity_id
            AND ac.check_id = aipa.check_id
            AND aipa.invoice_id = aia.invoice_id
            AND aia.vendor_id = aps.vendor_id
            AND 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 glcc.segment3 = ‘123456789’
          –AND aida.distribution_line_number = 1

            AND gir.gl_sl_link_id = xal.gl_sl_link_id
            AND gir.gl_sl_link_table = xal.gl_sl_link_table
            AND gir.je_header_id = gjl.je_header_id
            AND gir.je_line_num = gjl.je_line_num
            AND aipa.invoice_payment_id = aphd.invoice_payment_id
            AND xdl.source_distribution_type = ‘AP_PMT_DIST’
            AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
            AND xdl.ae_header_id = xal.ae_header_id
            AND xdl.ae_line_num = xal.ae_line_num
            AND xdl.application_id = xal.application_id
  • October 10, 2014 | 36 views