Loading

Wednesday, December 29, 2010

How Payable Invoices related Reconciliation and Currencies Data is stored in Oracle Apps R12? (Cash Management and General Ledger)

                                                                                                        

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:
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'

0 Responses to “How Payable Invoices related Reconciliation and Currencies Data is stored in Oracle Apps R12? (Cash Management and General Ledger)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.