Search Results ce_interest_rates_v




Overview
Explore in AI Chat

Overview

CE_INTEREST_RATES_V is a Cash Management (CE) view owned by the APPS schema and shipped with Oracle E-Business Suite 12.1.1 and 12.2.2. It presents interest rate information defined against interest schedules and their associated balance ranges, denormalizing a schedule-level structure into a single row per schedule/effective-date combination. The view returns key identifiers from the underlying interest schedule (notably INTEREST_SCHEDULE_ID), the effective date of the rate, audit columns, and a set of pivoted interest rate columns that represent the rate applied to each successive balance range within the schedule.

Because the underlying ETRM implementation selects rates by positional balance range (first, second, third, fourth, and so on), CE_INTEREST_RATES_V is most useful for reporting and integration scenarios where a flattened, one-row-per-schedule view of tiered interest rates is required. It is a query-only object; no DML should be attempted against it. Its primary role is to support Cash Management interest computation reporting, external interfaces, and custom extensions that need to read interest schedule rates without navigating the normalized base tables.

Underlying Base Objects

The documented referenced base objects are:

The view is defined over these objects with a SELECT DISTINCT on CE_INTEREST_BAL_RANGES (aliased BR), joined conceptually to CE_INTEREST_RATES (aliased IR) on EFFECTIVE_DATE. For each schedule, correlated subqueries select the rate belonging to the lowest BALANCE_RANGE_ID (the minimum range) and then to the second, third, and fourth ranges in ascending BALANCE_RANGE_ID order, producing distinct columns for each tier. Two of the leading positional columns are emitted as TO_NUMBER(NULL) placeholders, reflecting columns retained for structural compatibility but not populated in this view.

Key Columns

  • INTEREST_SCHEDULE_ID — the interest schedule identifier; the principal join key and the column most often searched by users. It links the view to schedule definitions and to balance ranges.
  • EFFECTIVE_DATE — the date from which the associated rates take effect; the view can return multiple rows per schedule across effective dates.
  • Interest rate tier columns — scalar subqueries returning IR.INTEREST_RATE for the first (minimum), second, third, and fourth balance ranges in ascending BALANCE_RANGE_ID order. These expose the tiered rate structure in a flattened form.
  • Audit columns — the view emits TRUNC(SYSDATE) and NVL(FND_GLOBAL.USER_ID, -1) values, and an 'OLD' literal, along with OBJECT_VERSION_NUMBER from CE_INTEREST_RATES, providing creation/update context and optimistic locking support.

Common Use Cases and Queries

Typical uses include verifying which rates apply to a schedule, reconciling tiered rate definitions, and feeding external interest calculation or reporting processes. A frequent lookup is by INTEREST_SCHEDULE_ID:

  • Retrieve all rate rows for a given schedule: SELECT interest_schedule_id, effective_date FROM ce_interest_rates_v WHERE interest_schedule_id = :schedule_id ORDER BY effective_date;
  • List schedules effective as of a date for reporting: SELECT interest_schedule_id, effective_date FROM ce_interest_rates_v WHERE effective_date <= TRUNC(SYSDATE);
  • Join to CE_INTEREST_BAL_RANGES to correlate tier positions with explicit range boundaries where the flattened columns are insufficient.

Because the view relies on correlated subqueries and positional ROWNUM logic, queries against large rate sets can be expensive; filtering by INTEREST_SCHEDULE_ID or EFFECTIVE_DATE is recommended. Always treat the object as read-only and validate results against the base tables when precision is critical.