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_HEADERS
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'
);
