Results for “iby_payments_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IBY_PAYMENTS_ALL is the core transactional table in the Oracle Payments (IBY) module of Oracle E-Business Suite, holding one row for every payment instruction generated by the Build Payments program. A payment may take the form of a single printed check or an electronic funds transfer executed between a first-party payer (the operating unit or legal entity) and a third-party payee. In release 12.1.1 and 12.2.2 the table is owned by the IBY schema and is considered a central object in the funds disbursement lifecycle, serving both Accounts Payable and Receivables-originated disbursement flows.

The table is 235 columns wide, which reflects its role as a denormalized "payment header" that carries payer, payee, bank account, remittance, and formatting attributes in a single row. Based on the foreign-key topology — many outward references to configuration and master tables, plus several tables that reference PAYMENT_ID — the Data Vault classification is satellite-leaning. In a dimensional or Data Vault style model, IBY_PAYMENTS_ALL is best understood as a satellite of the payer/payment-instruction hub, carrying descriptive and status attributes rather than acting as a pure hub itself.

Key Information Stored

The surrogate primary key is PAYMENT_ID, enforced by IBY_PAYMENTS_ALL_PK and uniquely indexed by IBY_PAYMENTS_ALL_U1. The most business-significant columns include:

• PAYMENT_METHOD_CODE – references IBY_PAYMENT_METHODS_B; identifies check, EFT, wire, etc.
• PAYMENT_STATUS and PAYMENTS_COMPLETE_FLAG – operational state of the payment.
• PAYMENT_AMOUNT, PAYMENT_CURRENCY_CODE, PAYMENT_DATE, ANTICIPATED_VALUE_DATE – monetary and timing attributes.
• PAYMENT_PROFILE_ID – links to IBY_ACCT_PMT_PROFILES_B, the payment process profile.
• PAYMENT_SERVICE_REQUEST_ID, PAYMENT_INSTRUCTION_ID – tie the payment back to the originating build request and instruction set.
• INTERNAL_BANK_ACCOUNT_ID / EXTERNAL_BANK_ACCOUNT_ID – reference CE_BANK_ACCOUNTS for payer and payee banks.
• EXT_PAYEE_ID – references IBY_EXTERNAL_PAYEES_ALL.
• LEGAL_ENTITY_ID – references FV_LEGAL_ENTITIES.
• VOID_DATE, VOID_REASON, VOIDED_BY – void/stop-payment audit trail along with the STOP_* columns.
• ORG_ID, SOURCE_PRODUCT – operating unit and originating product (AP, AR, etc.).
• PAYMENT_REFERENCE_NUMBER, PAPER_DOCUMENT_NUMBER – human-readable document identifiers.

Common Use Cases and Queries

Typical reporting and reconciliation scenarios include tracing payments by supplier, verifying voided or stopped payments, and reconciling disbursements to bank statements. A representative query joins the payment to its payee and profile:

SELECT p.payment_id, p.payment_date, p.payment_amount,
       p.payment_currency_code, p.payment_status,
       e.payee_name, m.payment_method_name
FROM   iby_payments_all p,
       iby_external_payees_all e,
       iby_payment_methods_b m
WHERE  p.ext_payee_id = e.ext_payee_id
AND    p.payment_method_code = m.payment_method_code
AND    p.org_id = :org_id
AND    p.payment_date BETWEEN :from_date AND :to_date;

Other common patterns include listing voided payments (WHERE void_date IS NOT NULL), aggregating payment amounts by profile (GROUP BY payment_profile_id), and reconciling issued payments to AP_HISTORY_CHECKS_ALL when check stock is replenished.

Related Objects

• AP_HISTORY_CHECKS_ALL.PAYMENT_ID → IBY_PAYMENTS_ALL.PAYMENT_ID – links historical check records to payments.
• IBY_DOCS_PAYABLE_ALL.PAYMENT_ID and FORMATTING_PAYMENT_ID → PAYMENT_ID – associates individual documents payable with their payment.
• IBY_DOCUMENTS_PAYABLE_H.PAYMENT_ID → PAYMENT_ID – historical archive of document-to-payment associations.
• IBY_PAY_INSTRUCTIONS_ALL – referenced via PAYMENT_INSTRUCTION_ID.
• IBY_PAYMENT_METHODS_B, IBY_ACCT_PMT_PROFILES_B, IBY_EXTERNAL_PAYEES_ALL, IBY_PAYMENT_REASONS_B, IBY_DELIVERY_CHANNELS_B – configuration/lookup dependencies.
• CE_BANK_ACCOUNTS and FV_LEGAL_ENTITIES – bank and legal-entity master references.