AP_INVOICES_ALL
SELECT
ai.invoice_id,
SUBSTR(ai.invoice_num,1,25) invoice_num,
SUBSTR(aps.vendor_name,1,25) vendor_name,
ai.invoice_date,
ai.invoice_amount,
ai.base_amount,
SUBSTR(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code,
SUBSTR(ai.invoice_currency_code,1,3) invoice_currency_code,
SUBSTR(ai.payment_currency_code,1,3) payment_currency_code,
ai.legal_entity_id,
ai.org_id
FROM
AP_INVOICES_ALL ai,
AP_SUPPLIERS aps,
AP_SUPPLIER_SITES_ALL avs
WHERE ai.invoice_id = '166014'
AND ai.vendor_id = aps.vendor_id(+)
AND ai.vendor_site_id = avs.vendor_site_id(+)
ORDER BY ai.invoice_id ASC;
AP_INVOICE_LINES_ALL
SELECT
line_number,
line_type_lookup_code,
line_source,
accounting_date,
period_name,
deferred_acctg_flag,
org_id
FROM
AP_INVOICE_LINES_ALL
WHERE invoice_id = '166014'
AP_INVOICE_DISTRIBUTIONS_ALL
SELECT
invoice_id,
invoice_line_number,
SUBSTR(distribution_line_number,1,8) distribution_line_number,
SUBSTR(line_type_lookup_code,1,9) line_type_lookup_code,
accounting_date,
period_name,
amount,
base_amount,
posted_flag,
match_status_flag,
encumbered_flag,
SUBSTR(dist_code_combination_id,1,15) dist_code_combination_id,
SUBSTR(accounting_event_id,1,15) accounting_event_id,
SUBSTR(bc_event_id,1,15) bc_event_id,
SUBSTR(invoice_distribution_id,1,15) invoice_distribution_id,
SUBSTR(parent_reversal_id,1,15) parent_reversal_id,
SUBSTR(po_distribution_id,1,15) po_distribution_id,
org_id
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_id = '166014'
ORDER BY invoice_distribution_id,
invoice_line_number,
distribution_line_number ASC;
AP_HOLDS_ALL
SELECT
held_by,
hold_date,
hold_lookup_code,
SUBSTR(hold_reason,1,25) hold_reason,
invoice_id,
release_lookup_code,
SUBSTR(release_reason,1,25) release_reason,
status_flag,
org_id
FROM
AP_HOLDS_ALL
WHERE
invoice_id = '166014';
AP_HOLD_CODES
SELECT *
FROM AP_HOLD_CODES
WHERE hold_lookup_code IN
( SELECT hold_lookup_code
FROM AP_HOLDS_ALL
WHERE invoice_id = '166014'
);
AP_INV_APRVL_HIST_ALL
SELECT *
FROM AP_INV_APRVL_HIST_ALL
WHERE invoice_id = '166014'
ORDER BY 1;
AP_INVOICE_RELATIONSHIPS
SELECT *
FROM AP_INVOICE_RELATIONSHIPS
WHERE original_invoice_id = '166014'
OR related_invoice_id = '166014';
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'
);
);
Reconciliation - Tables Involved
CE_STATEMENT_RECONCILS_ALL
CE_STATEMENT_HEADERS
CE_STATEMENT_LINES
Query:
SELECT DISTINCT cel.*
FROM CE_STATEMENT_LINES cel,
CE_STATEMENT_HEADERS ceh,
CE_STATEMENT_RECONCILS_ALL csr,
AP_INVOICE_PAYMENTS_ALL aip,
AP_CHECKS_ALL ac
WHERE cel.statement_header_id = ceh.statement_header_id
AND ac.check_id = aip.check_id
AND aip.invoice_id = '166014'
AND aip.check_id = ac.check_id
AND ac.bank_account_id = ceh.bank_account_id
AND TO_CHAR(ac.check_number) = cel.bank_trx_number
AND csr.statement_line_id = cel.statement_line_id;
Currencies - Tables Involved
FND_CURRENCIES
AP_INVOICES_ALL
AP_SYSTEM_PARAMETERS_ALL
Query:
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'
);
No comments:
Post a Comment