R12 Query For Invoices Reconciliation and Currencies Data is stored in Oracle AP Tables

Here we find the tables involved in storing the Reconciliation and Currency Data related to the Payable INVOICE (Invoice_id = 1234567).

Reconciliation Tables:

    CE_STATEMENT_RECONCILS_ALL

    CE_STATEMENT_HEADERS

    CE_STATEMENT_LINES

SELECT DISTINCT CSL.*

  FROM CE_STATEMENT_HEADERS CSH,

       CE_STATEMENT_LINES CSL,

       CE_STATEMENT_RECONCILS_ALL CSR,

       AP_CHECKS_ALL ac,

       AP_INVOICE_PAYMENTS_ALL aip

 WHERE     CSL.statement_header_id = CSH.statement_header_id

       AND aip.check_id = ac.check_id

       AND ac.bank_account_id = CSH.bank_account_id

       AND aip.invoice_id = '1234567'

       AND TO_CHAR (ac.check_number) = CSL.bank_trx_number

       AND ac.check_id = aip.check_id

       AND csr.statement_line_id = CSL.statement_line_id;

Currencies Tables:

      FND_CURRENCIES

      AP_SYSTEM_PARAMETERS_ALL

      AP_INVOICES_ALL

    

SELECT *

  FROM FND_CURRENCIES

 WHERE currency_code IN (SELECT a.invoice_currency_code

                           FROM AP_INVOICES_ALL a

                          WHERE a.invoice_id = '1234567'

                         UNION

                         SELECT b.payment_currency_code

                           FROM AP_INVOICES_ALL b

                          WHERE b.invoice_id = '1234567'

                         UNION

                         SELECT c.base_currency_code

                           FROM AP_SYSTEM_PARAMETERS_ALL c

                          WHERE NVL (c.org_id, -99) = '982');