Results for “iby_batches_all_v”

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

Overview

IBY_BATCHES_ALL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, residing in the IBY (Payments) product module. In ETRM documentation it is catalogued with a status of VALID and is described simply as a view "used to work with batch transactions." Functionally, it presents a denormalized, read-oriented projection of payment batch data drawn from the underlying IBY_BATCHES_ALL table, enriched with decoded state values, payee names, and payment system (BEP) names. The view is not a transactional entity in its own right; it exists to simplify query, reporting, and integration against batch header information without requiring the caller to perform the joins or lookup decoding manually.

Because the view exposes ORG_ID, it participates in Oracle's multi-org security model, and any query against it is subject to the operating unit context of the session. This makes it suitable for operational reporting, reconciliation extracts, and lightweight integration surfaces in both 12.1.1 and 12.2.2 environments.

Underlying Base Objects

The view text documented in ETRM shows that IBY_BATCHES_ALL_V is defined over four base objects, all referenced through APPS synonyms: IBY_BATCHES_ALL, IBY_BEPINFO, IBY_PAYEE, and FND_LOOKUP_VALUES. The primary driving table is IBY_BATCHES_ALL (aliased BAT), which supplies the batch identifier, status, amounts, and operating unit. The remaining three are joined as outer joins using the (+) operator, meaning batch rows are preserved even when related payee, BEP, or lookup records are absent.

The join conditions link BAT.BEPID to BEP.BEPID, BAT.PAYEEID to PYEE.PAYEEID, and BAT.BATCHSTATEID to the lookup code in FND_LOOKUP_VALUES where LOOKUP_TYPE equals 'IBY_BATCH_STATE'. A LANGUAGE filter restricts lookup translation to the session language via USERENV('LANG').

Key Columns

• MERCHBATCHID — the batch identifier, sourced as BAT.BATCHID and aliased to MERCHBATCHID.
• BATCHSTATUS — the internal status value of the batch record.
• BATCHSTATE — the decoded, user-facing meaning obtained from FND_LOOKUP_VALUES for lookup type IBY_BATCH_STATE.
• PAYEENAME — the payee name resolved from IBY_PAYEE.
• BEPNAME — the name of the associated payment system from IBY_BEPINFO.
• BATCHCLOSEDATE — the date on which the batch was closed.
• VPSBATCHID — the batch identifier as assigned by the payment system.
• NUMTRXNS — the count of transactions contained in the batch.
• BATCHTOTAL, BATCHSALES, BATCHCREDIT — aggregate monetary amounts for the batch.
• PAYMENTMETHODNAME — the payment method associated with the batch.
• ORG_ID — the operating unit identifier, enforcing multi-org partitioning.

Common Use Cases and Queries

Typical uses include reconciliation of batch totals, monitoring of open or closed batches, and extraction of batch data for downstream reporting. The decoded BATCHSTATE column is especially valuable because it removes the need to join FND_LOOKUP_VALUES in the calling query.

A representative query listing batches for the current operating unit:

• SELECT merchbatchid, payeename, bepname, batchstate, batchtotal, numtrxns, batchclosedate FROM iby_batches_all_v WHERE org_id = :p_org_id ORDER BY batchclosedate DESC;

Filtering to closed batches over a date range:

• SELECT merchbatchid, vpsbatchid, batchstate, batchtotal FROM iby_batches_all_v WHERE batchclosedate BETWEEN :p_start AND :p_end AND batchstate = 'Closed';

Aggregating transaction counts and totals by payment system:

• SELECT bepname, COUNT(*) batch_count, SUM(numtrxns) total_trxns, SUM(batchtotal) total_amount FROM iby_batches_all_v GROUP BY bepname;

Consultants should note that the view is read-only and that direct updates must target IBY_BATCHES_ALL. Because outer joins are used, columns such as PAYEENAME or BEPNAME may return NULL when the related record is missing, and callers should handle these cases in reporting logic.