Search Results get_date_range

Overview

CE_BAL_UTIL is a utility package owned by APPS in the Oracle Cash Management (CE) module. Its declaration header identifies it as AUTHID CURRENT_USER, meaning that all SQL statements inside the package execute with the privileges of the invoking user rather than with the definer's privileges. This is significant because the package is designed to be called from multiple contexts—concurrent programs, forms, and custom PL/SQL—where the caller's schema and security context must be respected.

The package supplies the computational primitives used to answer a specific business question: what is the balance of a cash pool on a given date? Cash pooling is used by treasury organizations to aggregate the balances of multiple sub-accounts under a single header account, so that interest, funding, and liquidity can be managed at the pool level rather than account by account. CE_BAL_UTIL provides the date enumeration, the balance aggregation, and the pool-level lookup that together make this possible. The ETRM metadata classifies it as a UTIL package, indicating it is not a business transaction API with validation and locking logic, but rather a reusable helper library.

Key Procedures and Functions

• GET_DATE_RANGE — A pipelined function that returns every day falling between a start date and an end date. It is declared as a pipelined table function over a date table type, so callers can consume the result with a SELECT ... FROM TABLE(...) construct. Its purpose is to generate a dense calendar of dates, which is required whenever a balance must be reported for periods with no underlying transaction activity, since sparse balance data would otherwise produce gaps in reporting.
• GET_BALANCE — A pipelined function that returns a balance table for a supplied date and a ref cursor of account identifiers. The ref cursor is defined to return rows shaped like CE_CASHPOOL_SUB_ACCTS, so the caller supplies the membership of the pool and the function returns the corresponding balances. Pipelining allows large result sets to be streamed rather than materialized, which is important when a pool contains many sub-accounts.
• GET_POOL_BALANCE — The function matching the user's search term. It accepts a cash pool identifier and a balance date and returns a single NUMBER, representing the aggregate balance of that pool as of the requested date. This is the highest-level entry point in the package and is the routine most commonly called by external code, since it encapsulates the sub-account resolution and aggregation performed by the other two functions.

Tables Accessed

Two tables are documented as accessed through APPS synonyms. CE_CASHPOOL_SUB_ACCTS stores the relationship between a cash pool and its member sub-accounts; it is the source of pool membership and drives the ref cursor consumed by GET_BALANCE. CE_BANK_ACCT_BALANCES stores bank account balance information by account and date, and provides the numeric balances that GET_POOL_BALANCE aggregates. Together they define the join path from a cash pool identifier to a summed balance figure.

Usage Notes

The package is referenced by at least one other package, confirming that it functions as a shared backend library rather than as an end-user-facing component. Typical invocation patterns include Cash Management concurrent programs that produce pool balance reports, custom treasury extracts, and inline SQL or PL/SQL that needs a point-in-time pool balance. Developers calling GET_POOL_BALANCE should note that it returns a scalar and is therefore suitable for direct assignment or comparison, whereas GET_BALANCE and GET_DATE_RANGE return pipelined collections and must be invoked in a table-function context. Because the package runs AUTHID CURRENT_USER, callers must themselves hold select privileges on the referenced tables and execute privileges on the package; these are normally granted to the Cash Management responsibility roles.