R12 - Query For AP 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 = 166014).
Reconciliation Tables:
CE_STATEMENT_RECONCILS_ALL
CE_STATEMENT_LINES
SELECT DISTINCT cel.*
FROM CE_STATEMENT_HEADERS ceh,
CE_STATEMENT_LINES cel,
CE_STATEMENT_RECONCILS_ALL csr,
AP_CHECKS_ALL ac
AP_INVOICE_PAYMENTS_ALL aip,
WHERE cel.statement_header_id = ceh.statement_header_id
AND aip.check_id = ac.check_id
AND ac.bank_account_id = ceh.bank_account_id
AND aip.invoice_id = '166014'
AND TO_CHAR (ac.check_number) = cel.bank_trx_number
AND ac.check_id = aip.check_id
AND csr.statement_line_id = cel.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 = '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'
);
