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