Results for “iby_payments_v”

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

Overview

IBY_PAYMENTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the IBY (Payments) product family. It presents a consolidated, denormalized projection of payment transaction summary data joined to the tangible (instrument) records that underpin each payment. The view is documented as VALID in ETRM metadata for both EBS 12.1.1 and 12.2.2. Its principal design characteristic is the derivation of a scheduling mode indicator: a DECODE on the SETTLEDATE column returns the literal value 'ONLINE' when SETTLEDATE is null, and 'OFFLINE' otherwise. This single expression makes the view the canonical reference point for distinguishing online (not yet settled) from offline (settled) payment transactions in IBY-based reporting and integration flows.

Underlying Base Objects

The view is defined over two documented base objects, both exposed to APPS through synonyms: IBY_TRXN_SUMMARIES_ALL and IBY_TANGIBLE. The FROM clause references IBY_TRXN_SUMMARIES_ALL (aliased PTS) and IBY_TANGIBLE (aliased PT), joined on PTS.MTANGIBLEID = PT.MTANGIBLEID. The transaction summary side supplies payment-request attributes, while the tangible side supplies the instrument-level detail such as amount and currency. Because IBY_TRXN_SUMMARIES_ALL retains multiple request rows per transaction, the view applies a correlated subquery filter that restricts results to the row bearing the maximum REQDATE for each TRANSACTIONID. The filter further constrains REQTYPE to the values 'ORAPMTREQ' and 'ORAPMTCREDIT', which correspond to Oracle payment requests and payment credits. The practical effect is that IBY_PAYMENTS_V returns one most-recent record per transaction.

Key Columns

The view exposes seventeen columns. TRANSACTIONID, PAYEEID, ECAPPID, PAYERID, PAYERINSTRID, INSTRTYPE, and ORG_ID identify the transaction, its parties, the payment instrument type, and the operating unit context. SCHEDDATE carries the transaction's settle date, sourced from PTS.SETTLEDATE. SCHEDMODE is the derived 'ONLINE'/'OFFLINE' indicator described above. NLSLANG holds the National Language Support language setting for the payment. PAYMENTMETHODNAME identifies the payment method, such as check or electronic funds transfer. TANGIBLEID, AMOUNT, CURRENCYNAMECODE, ACCTNO, REFINFO, and MEMO carry the instrument-level payment detail, including the monetary value, currency, account reference, and free-text memo.

Common Use Cases and Queries

The view is typically used to report outstanding versus settled payments and to feed downstream reconciliation or treasury extracts. Because SCHEDMODE is computed rather than stored, a query filtering on the 'ONLINE' value directly serves the common requirement of identifying unsettled payments. A representative query is:

• SELECT transactionid, payeeid, paymentmethodname, amount, currencynamecode, scheddate, schedmode FROM apps.iby_payments_v WHERE schedmode = 'ONLINE' AND org_id = :p_org_id;
• SELECT schedmode, paymentmethodname, COUNT(*), SUM(amount) FROM apps.iby_payments_v GROUP BY schedmode, paymentmethodname;
• SELECT transactionid, tangibleid, acctno, refinfo, memo FROM apps.iby_payments_v WHERE transactionid = :p_transaction_id;

Typical scenarios include treasury dashboards showing online payments awaiting settlement, reconciliation reports matching transaction summaries to tangible instruments, and integration extracts that supply payment details to external banking or ERP systems. Filtering on ORG_ID preserves multi-org security, and joining TRANSACTIONID or TANGIBLEID back to IBY base tables supports drill-down reporting.