Results for “commission_rate”

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

Overview

CN_RATE_TIERS_ALL is the compensation rate tier table within the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual tier rows that make up a compensation rate schedule, defining the bands, thresholds, and corresponding payout values that drive incentive calculation for participants. Each row represents a single tier within a rate schedule identified by RATE_SCHEDULE_ID, and the table is owned by the CN schema.

From a dimensional modeling perspective, the mined foreign-key structure suggests a hub-leaning classification. Because the table is the referenced side of multiple downstream foreign keys and carries its own generated surrogate key (RATE_TIER_ID), it functions as a central definitional entity that other transactional and assignment tables point to, rather than as a pure dependent satellite or a junction linking two hubs.

Key Information Stored

The table contains 18 documented columns. The most functionally significant are:

• RATE_TIER_ID — surrogate primary key (constraint CN_RATE_TIERS_PK) uniquely identifying each tier row. Also part of unique index CN_RATE_TIERS_U1 (RATE_TIER_ID, ORG_ID).
• RATE_SCHEDULE_ID — foreign key to CN_RATE_SCHEDULES_ALL; identifies the parent rate schedule to which the tier belongs. Appears in unique indexes CN_RATE_TIERS_UK1 and CN_RATE_TIERS_UK2.
• ORG_ID — operating unit / organization identifier supporting multi-org partitioning.
• MINIMUM_PERCENT and MAXIMUM_PERCENT — percentage-based boundaries defining the tier band.
• MINIMUM_AMOUNT and MAXIMUM_AMOUNT — amount-based boundaries for tiers expressed in currency or quantity terms.
• COMMISSION_RATE — the rate applied when a transaction falls within the tier.
• COMMISSION_AMOUNT — a fixed payout amount for the tier, used in flat-rate schedules.
• SEQUENCE_NUMBER and RATE_SEQUENCE — ordering attributes that determine tier evaluation or application sequence.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS supporting row-level security.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the ORM layer.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

The business-key candidates are captured in the unique indexes: CN_RATE_TIERS_U1 (RATE_TIER_ID, ORG_ID), and the two unique constraints CN_RATE_TIERS_UK1 and CN_RATE_TIERS_UK2, both anchored on RATE_SCHEDULE_ID.

Common Use Cases and Queries

Typical usage centers on retrieving all tiers for a given schedule, identifying the tier matching a transaction value, and auditing payout configurations.

• List tiers for a schedule ordered for evaluation:

  SELECT rate_tier_id, minimum_percent, maximum_percent,
         commission_rate, commission_amount, sequence_number
  FROM   cn_rate_tiers_all
  WHERE  rate_schedule_id = :p_schedule_id
  AND    org_id = :p_org_id
  ORDER BY sequence_number;

• Locate the applicable tier for a given attainment value using boundary comparisons.
• Join to CN_COMMISSION_LINES_ALL on RATE_TIER_ID to report which tier produced a calculated commission.
• Reconcile rate schedule definitions across operating units using ORG_ID filters.

Related Objects

The table participates in a well-defined FK web:

• CN_RATE_SCHEDULES_ALL — parent schedule; joined via CN_RATE_TIERS_ALL.RATE_SCHEDULE_ID.
• CN_COMMISSION_LINES_ALL — commission detail lines referencing RATE_TIER_ID.
• CN_ROLE_QUOTA_RATES — role-to-quota rate assignments referencing RATE_TIER_ID.
• CN_SCH_DIM_TIERS_ALL — dimension tier definitions referencing RATE_TIER_ID.
• CN_SRP_RATE_ASSIGNS_ALL — salesrep rate assignments referencing RATE_TIER_ID.
• CN_SRP_QUOTA_RATES_ALL — salesrep quota rates referencing RATE_TIER_ID.
• CN_POSTING_DETAILS_ALL — posting detail records referencing RATE_TIER_ID.
• CN_PAYMENT_TRANSACTIONS_ALL — payment transactions referencing RATE_TIER_ID.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for row-level security.

These relationships confirm CN_RATE_TIERS_ALL as a foundational definitional table upon which commission calculation, assignment, posting, and payment processing all depend.