Here we find
which tables are involved in storing the Payment Data to
the Payable INVOICE (Invoice_id = 124567).
TABLES
INVOLVED:
AP_TERMS
AP_TERMS_LINES
AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
AP_CHECKS_ALL
AP_PAYMENT_HISTORY_ALL
AP_PAYMENT_HIST_DISTS
AP_RECON_DISTRIBUTIONS_ALL
AP_DOCUMENTS_PAYABLE
IBY_DOCS_PAYABLE_ALL
IBY_PAYMENTS_ALL
IBY_PAY_INSTRUCTIONS_ALL
AP_TERMS
SELECT *
FROM AP_TERMS
WHERE term_id IN
(SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '124567'
);
AP_TERMS_LINES
SELECT *
FROM AP_TERMS_LINES
WHERE term_id IN
(SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '124567'
);
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 = '124567';
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 = '124567'
ORDER BY check_id;
AP_PAYMENT_DISTRIBUTIONS_ALL
SELECT tab.*
FROM AP_PAYMENT_DISTRIBUTIONS_ALL
tab,
AP_INVOICE_PAYMENTS_ALL
aip
WHERE aip.invoice_payment_id = tab.invoice_payment_id
AND
aip.invoice_id = '124567';
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 = '124567'
);
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 = '124567'
)
ORDER BY payment_history_id;
AP_PAYMENT_HIST_DISTS
SELECT aphd.*
FROM
AP_PAYMENT_HIST_DISTS aphd,
AP_INVOICE_DISTRIBUTIONS_ALL
aid,
AP_PAYMENT_HISTORY_ALL
aph
WHERE aid.invoice_id = '124567'
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 = '124567'
);
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
AND calling_app_doc_unique_ref2 = '124567';
IBY_DOCS_PAYABLE_ALL
SELECT *
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '124567';
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 = '124567'
);
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 = '124567'
);
);
No comments:
Post a Comment