Results for “logical_group_reference”

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

Overview

CE_AVAILABLE_BATCHES_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Cash Management (CE) product family. Its documented purpose is to expose the set of bank statement batches that are available for reconciliation within Oracle Cash Management. The view serves as the presentation layer through which the reconciliation user interface, concurrent programs, and external integrations retrieve candidate batches that can be matched against bank statement lines.

Because reconciliation involves both manually entered and automatically matched transactions, CE_AVAILABLE_BATCHES_V consolidates batch information from multiple batch views into a single, uniform result set. The view is defined as a UNION ALL of two principal queries drawn from CE_200_BATCHES_V, with a filter governed by the CE_AUTO_BANK_MATCH package (specifically the GET_200 function) and a supporting reference to CE_200_GROUPS_V. A parallel branch of the union references CE_222_BATCHES_V, reflecting the coexistence of legacy and later batch structures. The final SELECT list appends CE_BANK_ACCT_USE_ID, ORG_ID, and two NULL columns to align column positions across the union branches.

Underlying Base Objects

The documented base objects underlying CE_AVAILABLE_BATCHES_V are:

• CE_200_BATCHES_V (VIEW) — provides the primary batch row set for the first union branch.
• CE_200_GROUPS_V (VIEW) — a related grouping view referenced in the batch data model.
• CE_222_BATCHES_V (VIEW) — supplies the second union branch, accommodating the 12.2.x batch structure.
• CE_AUTO_BANK_MATCH (PACKAGE) — supplies the GET_200 selector function used in the WHERE clause and the GET_INVERSE_RATE function used within the SELECT list.
• MO_GLOBAL (PACKAGE) — the Multi-Org access control package, consistent with the ORG_ID column exposed by the view.

The view therefore does not read base tables directly; it is a composite over other Cash Management views, and its behavior is tightly coupled to the logic embedded in the CE_AUTO_BANK_MATCH package.

Key Columns

CE_AVAILABLE_BATCHES_V returns the following significant columns:

• ROW_ID, BATCH_ID, BATCH_NUMBER, TRX_NUMBER — batch identification and transaction numbering.
• TRX_TYPE, TYPE_MEANING, TRX_CURRENCY_TYPE — transaction classification attributes.
• TRX_DATE, GL_DATE — transaction and general ledger dates.
• CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, ACTUAL_AMOUNT_CLEARED, CLEARED_AMOUNT — monetary amounts in transaction and bank account currency.
• EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE_DATE, EXCHANGE_RATE — currency conversion attributes.
• BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, BANK_CURRENCY_CODE, CE_BANK_ACCT_USE_ID — bank and bank account identification.
• BANK_CHARGES, BANK_ERRORS — reconciliation adjustments.
• MULTI_SELECT, COUNT_CLEARED, REMITTANCE_NUMBER, APPLICATION_ID, ORG_ID — control and organizational attributes.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard WHO audit columns.

Notably, the EXCHANGE_RATE column is not passed through unchanged. It is wrapped in a DECODE expression driven by CE_AUTO_BANK_MATCH.GET_INVERSE_RATE: when the flag is 'N', the rate is returned as stored; when 'Y', a zero or NULL rate yields NULL, otherwise the reciprocal (1/EXCHANGE_RATE) is returned.

Common Use Cases and Queries

This view is most frequently queried to list reconciliation-eligible batches for a bank account, to audit exchange rate handling on foreign-currency batches, and to feed custom reconciliation reports. A representative query filtering by bank account and inspecting rate handling follows:

• SELECT batch_id, trx_number, currency_code, amount, exchange_rate_type, exchange_rate, exchange_rate_date FROM ce_available_batches_v WHERE bank_account_id = :p_bank_account_id AND org_id = :p_org_id;
• SELECT trx_number, currency_code, exchange_rate, 1/exchange_rate AS inverse_rate FROM ce_available_batches_v WHERE exchange_rate IS NOT NULL AND currency_code <> :p_bank_currency;

Because the view is a UNION ALL over batch views, queries should generally filter on BANK_ACCOUNT_ID, ORG_ID, or TRX_DATE to limit the scanned row set. Users should also be aware that any modification to CE_AUTO_BANK_MATCH logic will directly affect the rows and rates returned by this view.