R12 - Query For AP Invoices Reconciliation

In this post, we will find the tables involved in storing the Reconciliation and Currency Data related to the   Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

Reconciliation - Tables Involved

    CE_STATEMENT_RECONCILS_ALL
    CE_STATEMENT_HEADERS
    CE_STATEMENT_LINES

Query:

/* Formatted on 10/7/2013 9:14:34 AM (QP5 v5.114.809.3010) */
SELECT   DISTINCT cel.*
  FROM   CE_STATEMENT_LINES cel,
         CE_STATEMENT_HEADERS ceh,
         CE_STATEMENT_RECONCILS_ALL csr,
         AP_INVOICE_PAYMENTS_ALL aip,
         AP_CHECKS_ALL ac
 WHERE       cel.statement_header_id = ceh.statement_header_id
         AND ac.check_id = aip.check_id
         AND aip.invoice_id = '166014'
         AND aip.check_id = ac.check_id
         AND ac.bank_account_id = ceh.bank_account_id
         AND TO_CHAR (ac.check_number) = cel.bank_trx_number
         AND csr.statement_line_id = cel.statement_line_id;
Currencies - Tables Involved

    FND_CURRENCIES
    AP_INVOICES_ALL
    AP_SYSTEM_PARAMETERS_ALL

Query:

/* Formatted on 10/7/2013 9:14:03 AM (QP5 v5.114.809.3010) */

SELECT   *
  FROM   FND_CURRENCIES
 WHERE   currency_code IN (SELECT   a.invoice_currency_code
                             FROM   AP_INVOICES_ALL a
                            WHERE   a.invoice_id = '166014'
                           UNION
                           SELECT   b.payment_currency_code
                             FROM   AP_INVOICES_ALL b
                            WHERE   b.invoice_id = '166014'
                           UNION
                           SELECT   c.base_currency_code
                             FROM   AP_SYSTEM_PARAMETERS_ALL c
                            WHERE   NVL (c.org_id, -99) = '911');