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’

  );

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s