How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)

In this post, we will find the tables involved in storing the Payment 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.

AP_TERMS
SELECT *
FROM AP_TERMS
WHERE term_id IN
( SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '166014'
);

AP_TERMS_LINES
SELECT *
FROM AP_TERMS_LINES
WHERE term_id IN
( SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '166014'
);

AP_PAYMENT_SCHEDULES_ALL
SELECT
amount_remaining,
batch_id,
due_date,
gross_amount,
hold_flag,
invoice_id,
payment_num,
SUBSTR(payment_status_flag,1,1) payment_status_flag,
org_id
FROM
AP_PAYMENT_SCHEDULES_ALL
WHERE
invoice_id = '166014';

AP_INVOICE_PAYMENTS_ALL
SELECT
check_id,
SUBSTR(invoice_payment_id,1,15) invoice_payment_id,
amount,
payment_base_amount,
invoice_base_amount,
accounting_date,
period_name,
posted_flag,
accounting_event_id,
invoice_id,
org_id
FROM
AP_INVOICE_PAYMENTS_ALL
WHERE
invoice_id = '166014'
ORDER BY check_id ASC;

AP_PAYMENT_DISTRIBUTIONS_ALL
SELECT tab.*
FROM AP_INVOICE_PAYMENTS_ALL aip,
AP_PAYMENT_DISTRIBUTIONS_ALL tab
WHERE aip.invoice_payment_id = tab.invoice_payment_id
AND aip.invoice_id = '166014';

AP_CHECKS_ALL
SELECT
check_id,
check_number,
vendor_site_code,
amount,
base_amount,
checkrun_id,
checkrun_name,
check_date,
SUBSTR(status_lookup_code,1,15) status_lookup_code,
void_date,
org_id
FROM
AP_CHECKS_ALL
WHERE check_id IN
( SELECT DISTINCT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
);

AP_PAYMENT_HISTORY_ALL
SELECT
payment_history_id,
check_id,
accounting_date,
SUBSTR(transaction_type,1,20) transaction_type,
posted_flag,
SUBSTR(accounting_event_id,1,10) accounting_event_id,
rev_pmt_hist_id,
org_id
FROM
AP_PAYMENT_HISTORY_ALL
WHERE check_id IN
(SELECT DISTINCT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
)
ORDER BY payment_history_id ASC;

AP_PAYMENT_HIST_DISTS
SELECT aphd.*
FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
AP_PAYMENT_HIST_DISTS aphd,
AP_PAYMENT_HISTORY_ALL aph
WHERE aid.invoice_id = '166014'
AND aid.invoice_distribution_id = aphd.invoice_distribution_id
AND aph.payment_history_id = aphd.payment_history_id;


AP_RECON_DISTRIBUTIONS_ALL
SELECT *
FROM AP_RECON_DISTRIBUTIONS_ALL
WHERE check_id IN
( SELECT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
);

AP_DOCUMENTS_PAYABLE
SELECT
pay_proc_trxn_type_code,
calling_app_doc_unique_ref1 check_id,
calling_app_doc_unique_ref2 invoice_id,
calling_app_doc_unique_ref4 invoice_payment_id,
calling_app_doc_ref_number invoice_number,
payment_function,
payment_date,
document_date,
document_type,
payment_currency_code,
payment_amount,
payment_method_code
FROM
AP_DOCUMENTS_PAYABLE
WHERE calling_app_id = 200 -- Application id for Payables
AND calling_app_doc_unique_ref2 = '166014';

IBY_DOCS_PAYABLE_ALL
SELECT *
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014';

IBY_PAYMENTS_ALL
SELECT *
FROM IBY_PAYMENTS_ALL
WHERE payment_id IN
(SELECT payment_id
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014'
);

IBY_PAY_INSTRUCTIONS_ALL
SELECT *
FROM IBY_PAY_INSTRUCTIONS_ALL
WHERE payment_instruction_id IN
(SELECT payment_instruction_id
FROM IBY_PAYMENTS_ALL
WHERE payment_id IN
(SELECT payment_id
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014'
);
);

AP PAYMENT TABLES TO XLA TABLES LINK

XLA_EVENTS
SELECT
DISTINCT xe.*
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xe.application_id
AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
ORDER BY
xe.entity_id,
xe.event_number;

XLA_AE_HEADERS
SELECT DISTINCT xeh.*
FROM xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xeh.event_id,
xeh.ae_header_id ASC;

XLA_AE_LINES
SELECT DISTINCT xel.*,
fnd_flex_ext.get_segs('SQLGL','GL#', '50577' ,xel.code_combination_id) "Account"
FROM xla_ae_lines xel,
xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xel.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xel.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xel.ae_header_id,
xel.ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl.*
FROM xla_distribution_links xdl,
xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xdl.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xdl.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id