Search Results balance_range_id




Overview
Explore in AI Chat

Overview

APPS.CE_INTEREST_RATES_V is a reporting and integration view in the Oracle E-Business Suite Cash Management (CE) module, exposing interest rate definitions maintained against interest schedules and balance ranges. It presents a flattened, denormalized representation of interest rate data so that external consumers—interfaces, concurrent programs, and custom reports—can retrieve effective-dated rate information without traversing the underlying normalized structures directly.

The view is relevant to ETRM (E-Business Tax and Treasury-adjacent) reporting where interest calculations for cash pools, bank accounts, and internal borrowing arrangements must be surfaced. It is defined with owner APPS and is intended for read-only consumption; the presence of fnd_global.user_id and trunc(sysdate) expressions indicates audit stamping consistent with standard EBS view conventions. The suffix "_V" signals a versioned/denormalized view rather than a base table.

Underlying Base Objects

The view is defined over three documented base objects:

The view joins CE_INTEREST_BAL_RANGES to CE_INTEREST_RATES on balance range and effective date, returning a DISTINCT result set that collapses duplicate schedule/range/date combinations. Because it is defined with SELECT DISTINCT and several scalar subqueries, it is not directly updatable.

Key Columns

  • INTEREST_SCHEDULE_ID — identifies the parent interest schedule; the principal join key for downstream reporting.
  • EFFECTIVE_DATE — the date on which a given rate becomes active; used for time-sliced reporting.
  • INTEREST_RATE — the rate value resolved per balance range and effective date.
  • OBJECT_VERSION_NUMBER — optimistic locking/versioning token carried from CE_INTEREST_RATES.
  • Audit columns — created/updated timestamps and user IDs sourced via FND_GLOBAL and SYSDATE.

Several columns in the SELECT list are populated with to_number(null) or literal markers such as 'OLD', indicating placeholder or legacy compatibility slots rather than live data. The multiple scalar subqueries resolve the first, second, third, and fourth balance ranges respectively, effectively pivoting up to four range tiers per schedule into discrete columns.

Common Use Cases and Queries

Typical usage includes reconciling effective interest rates by schedule, feeding treasury dashboards, and validating rate tiering before interest calculation runs.

  • Retrieve all rates for a schedule:

SELECT interest_schedule_id, effective_date, interest_rate
FROM apps.ce_interest_rates_v
WHERE interest_schedule_id = :p_schedule_id
ORDER BY effective_date;

  • Find the current effective rate as of today:

SELECT interest_schedule_id, interest_rate
FROM apps.ce_interest_rates_v
WHERE effective_date = (SELECT MAX(effective_date)
  FROM apps.ce_interest_rates_v v2
  WHERE v2.interest_schedule_id = ce_interest_rates_v.interest_schedule_id);

Because the view uses SELECT DISTINCT and correlated subqueries over CE_INTEREST_BAL_RANGES, performance may degrade with large volumes of schedules and ranges; restricting by INTEREST_SCHEDULE_ID or EFFECTIVE_DATE is advisable. Users requiring balance-range detail beyond the pivoted tiers should query CE_INTEREST_RATES and CE_INTEREST_BAL_RANGES directly.