Wednesday, December 29, 2010
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'
);
);
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
3 Responses to “How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)”
April 8, 2013 at 12:21 AM
Good
August 9, 2013 at 2:04 PM
Very Helpful, thank you!
Do you know if it's possible for a payment to have data on AP_INVOICE_PAYMENTS_ALL but not on IBY_PAYMENTS_ALL?
September 30, 2013 at 11:09 PM
Hey man.. came across your blog again.. hope you doing good :)
Post a Comment