Oracle Fusion Payments Tables with Sample Queries

Oracle Fusion Payments is the centralized payment engine behind Oracle Financials. It processes disbursements and receipts across modules like Payables, Receivables, and Expenses. This post will help provide the underlying tables can help technical and functional consultants can use for the reports and troubleshoot paymenting issues, and support customizations.

🔍 Key Oracle Fusion Payments Tables

Here are the most commonly used tables in Oracle Fusion Payments:

Table Name	Description
IBY_PAY_SERVICE_REQUESTS	Stores payment service request details for each disbursement
IBY_DOCS_PAYABLE_ALL	Contains invoice or refund records selected for payment
IBY_PAY_INSTRUCTIONS_ALL	Payment instructions sent for formatting and transmission
IBY_PAYMENTS_ALL	Details of actual payments created by the system
IBY_PAYMENT_DETAILS	Payment breakdowns linked to instructions
IBY_PMT_INSTR_USES_ALL	Payment instruments like bank accounts or credit cards
IBY_EXTERNAL_PAYEES_ALL	Supplier or employee payee information
CE_PAYMENT_TRANSACTIONS	Cash Management view of payment transactions
AP_INVOICES_ALL	Source invoices from Oracle Payables

🧩 How These Tables Are Related

The relationships between these tables are essential for linking invoice to payment:

• Invoices from AP_INVOICES_ALL become documents payable in IBY_DOCS_PAYABLE_ALL.
• Each document is tied to a payment service request in IBY_PAY_SERVICE_REQUESTS.
• Requests are grouped into payment instructions via IBY_PAY_INSTRUCTIONS_ALL.
• Payments are recorded in IBY_PAYMENTS_ALL and detailed in IBY_PAYMENT_DETAILS.

🧪 Sample SQL Queries

1. Invoices Selected for Payment

SELECT dp.payment_service_request_id,
       dp.invoice_id,
       ai.invoice_num,
       ai.invoice_amount,
       dp.payment_status
FROM iby_docs_payable_all dp
JOIN ap_invoices_all ai
  ON dp.invoice_id = ai.invoice_id
WHERE dp.payment_status = 'SELECTED';

2. Payment Instruction Details

SELECT pi.payment_instruction_id,
       pi.status,
       pi.payment_profile_id,
       pi.creation_date,
       COUNT(pd.payment_id) AS total_payments
FROM iby_pay_instructions_all pi
LEFT JOIN iby_payment_details pd
  ON pi.payment_instruction_id = pd.payment_instruction_id
GROUP BY pi.payment_instruction_id, pi.status, pi.payment_profile_id, pi.creation_date;

3. Payment Summary by Supplier

SELECT epa.supplier_name,
       SUM(p.amount) AS total_paid,
       COUNT(p.payment_id) AS payments_made
FROM iby_payments_all p
JOIN iby_external_payees_all epa
  ON p.payee_party_id = epa.payee_party_id
GROUP BY epa.supplier_name
ORDER BY total_paid DESC;

4. Trace Invoice to Payment

SELECT ai.invoice_num,
       ai.invoice_amount,
       dp.payment_service_request_id,
       psr.calling_app_doc_ref_number,
       pi.payment_instruction_id,
       p.payment_id,
       p.amount,
       p.status
FROM ap_invoices_all ai
JOIN iby_docs_payable_all dp
  ON ai.invoice_id = dp.invoice_id
JOIN iby_pay_service_requests psr
  ON dp.payment_service_request_id = psr.payment_service_request_id
JOIN iby_pay_instructions_all pi
  ON psr.payment_instruction_id = pi.payment_instruction_id
JOIN iby_payments_all p
  ON pi.payment_instruction_id = p.payment_instruction_id
WHERE ai.invoice_num = 'INV12345';

📌 Tips for Working with Payments Tables

• Check IBY_PAYMENTS_ALL.status for values like ISSUED, VOIDED, or CANCELLED
• Use CE_PAYMENT_TRANSACTIONS to track reconciled bank transactions
• Always apply ORG_ID or LEDGER_ID in multi-org setups

📚 Conclusion

Understanding the Oracle Fusion Payments data model enables you to extract insights, troubleshoot effectively, and support automation. These queries are just a starting point. You can extend them for BI Publisher, OTBI, or external reporting integrations.