Search Results cn_rate_tiers




Overview
Explore in AI Chat

Overview

CN_RATE_TIERS_PKG is a server-side PL/SQL package body owned by the APPS schema that encapsulates the business logic for maintaining commission rate table tiers in Oracle E-Business Suite. Commission rate schedules in Oracle Incentive Compensation are composed of rate tiers, each of which defines a commission amount and a sequence position against a parent rate schedule. The package provides a controlled, centralized API for inserting, locking, updating, and deleting these tier records while preserving data integrity across the related compensation plan structure.

The package header comment states its purpose explicitly: it "holds all server side packages used to process a commission rate table tier." A defining characteristic of the package is the side effect it enforces on dependent compensation plans. Whenever a tier is added, updated, or deleted, any compensation plan assigned to the affected rate schedule through a quota must be set to an INCOMPLETE status. This ensures that plan administrators revisit and re-validate plans whose underlying commission economics have changed.

Key Procedures and Functions

  • INSERT_ROW — Creates a new commission rate tier record. It accepts the rate tier identifier as an IN OUT NOCOPY parameter and generates a primary key from the CN_RATE_TIERS_S sequence when the supplied identifier is null. The procedure accepts the parent rate schedule, commission amount, sequence, and standard WHO audit columns, with creation and update metadata defaulted from SYSDATE and the FND_GLOBAL session context. Following the design note in the package body, INSERT_ROW invokes cn_comp_plans_pkg.set_status to mark dependent compensation plans as INCOMPLETE.
  • LOCK_ROW — Acquires a row-level lock on a rate tier to support the read-modify-write concurrency model used by the other procedures. This prevents concurrent modification of the same tier during an update or delete operation.
  • UPDATE_ROW — Modifies the attributes of an existing tier, such as its commission amount or rate sequence, and refreshes the audit columns. As with INSERT_ROW, the procedure triggers the compensation plan status update so that affected plans are invalidated when tier definitions change.
  • DELETE_ROW — Removes a tier from a rate schedule and applies the same invalidation logic to any compensation plans that reference the schedule through a quota.

Tables Accessed

  • CN_RATE_TIERS — The primary transactional table storing individual commission rate tier rows. INSERT_ROW, UPDATE_ROW, and DELETE_ROW maintain records here, and LOCK_ROW references the same table for concurrency control.
  • CN_RATE_TIERS_S — The sequence object used to generate unique RATE_TIER_ID values during insert processing.
  • DUAL — Used to fetch the next sequence value during primary key generation.

Referenced indirectly, CN_COMP_PLANS is accessed through the cn_comp_plans_pkg call rather than by direct SQL in this package body. This package is referenced by one other package in the documented metadata.

Usage Notes

In Oracle EBS 12.1.1 and 12.2.2, CN_RATE_TIERS_PKG is an internal API rather than a standalone user-facing program. It is invoked by the Oracle Incentive Compensation forms and by other administrative processes that manage rate schedules and plan assignments. The package reflects the R12 multi-org access control (MOAC) model: the R12 changes embedded in the source add an ORG_ID parameter to INSERT_ROW, so callers must supply the operating unit identifier as part of the insert payload.

Custom code interfacing with rate tiers should call these procedures rather than writing directly to CN_RATE_TIERS, because bypassing them would omit the compensation plan invalidation step and could leave plans in an inconsistent state. Because there is no package-level validation logic beyond the status update, callers are responsible for supplying valid rate schedule references and coherent sequence values. The package source header indicates a ship version of 120.2, confirming its availability throughout the ETRM releases documented for 12.1.1 and 12.2.2.