How Payable Invoices related Reconciliation and Currencies Data is stored in Oracle Apps R12

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:

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:

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’

  );