Results for “ref_amount”

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

Overview

IBY_PAY_PAYMENTS_ALL is the transaction-level payments table in the Oracle Payments (IBY) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores payment information at the individual payment transaction level, as submitted by the payable programs that originate disbursements, such as Payables, Payroll, and other source applications integrated with Oracle Payments. Each row represents one payment transaction, carrying the beneficiary details, banking instructions, monetary amounts, and processing status needed to format, approve, and transmit a payment instruction to a payment system or financial institution.

From a Data Vault modeling perspective, the documented foreign key structure suggests this object is satellite-leaning. It holds descriptive payment attributes anchored to a transaction identity, while its relationship to IBY_PAY_BATCHES_ALL via BATCH_ID positions it as a detail entity within the payment batch hierarchy.

Key Information Stored

The table carries 77 documented columns in the IBY schema. The most significant include:

• PMT_TRXN_ID — The surrogate primary key, uniquely identifying each payment transaction through the unique index IBY_PAY_PAYMENTS_ALL_PK. This is the definitive business-key candidate for the table.
• BATCH_ID — Foreign key to IBY_PAY_BATCHES_ALL, linking the transaction to its parent payment batch.
• PAYER_ID and ECAPP_ID — Identify the paying party and the source application context.
• VALUE_AMOUNT, REF_AMOUNT, VALUE_DATE — The payment amount, reference amount, and effective value date used for accounting and settlement.
• VAT_AMOUNT and WHT_AMOUNT — Tax and withholding components of the payment.
• BNF_NAME, BNF_ID, BNF_ADDRESS_LINE1–3, BNF_ADDR_CITY/STATE/ZIP/COUNTRY — Beneficiary identity and mailing address details.
• BNF_BANK_ACCT_NUMBER, BNF_BANK_ACCT_IBAN_NUMBER, BNF_BANK_SWIFT_CODE, BNF_BANK_NAME, BNF_BANK_BRANCH_NUM — Beneficiary bank account and routing information used to execute the payment.
• PMT_STATUS and PMT_APPROVAL_STATUS — Track processing state and approval state of the transaction.
• ORG_ID — Operating unit identifier supporting multi-org reporting.
• ERROR_LOCATION, BEP_CODE, BEP_MESSAGE — Error diagnostics from payment processing or bank communication.

Standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) are also present.

Common Use Cases and Queries

IBY_PAY_PAYMENTS_ALL is central to payment reconciliation, bank file generation reporting, and payment status tracking. A typical query retrieves all transactions for a batch:

• SELECT p.PMT_TRXN_ID, p.BNF_NAME, p.VALUE_AMOUNT, p.PMT_STATUS FROM IBY_PAY_PAYMENTS_ALL p WHERE p.BATCH_ID = :batch_id;
• Filtering by PMT_STATUS to identify failed or unprocessed payments and inspecting BEP_MESSAGE for the cause.
• Joining to IBY_PAY_INVOICEPMTS on PMT_TRXN_ID to reconcile payment transactions to their source invoices or documents.
• Aggregating VALUE_AMOUNT by ORG_ID and VALUE_DATE for treasury and cash-flow reporting.
• Extracting beneficiary bank details (BNF_BANK_ACCT_IBAN_NUMBER, BNF_BANK_SWIFT_CODE) for payment instruction audits and compliance checks.

Related Objects

• IBY_PAY_BATCHES_ALL — Parent batch table; joined via IBY_PAY_PAYMENTS_ALL.BATCH_ID = IBY_PAY_BATCHES_ALL.BATCH_ID.
• IBY_PAY_INVOICEPMTS — Child table linking payment transactions to source invoice payments; joined via IBY_PAY_INVOICEPMTS.PMT_TRXN_ID = IBY_PAY_PAYMENTS_ALL.PMT_TRXN_ID.
• AP (Payables) payment tables and IBY payment formatting programs that consume this data to produce payment files.
• IBY payment APIs and concurrent programs that read and update PMT_STATUS and PMT_APPROVAL_STATUS during approval and transmission.