Results for “cn_role_plan_maps”

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

Overview

CN_ROLE_PLAN_MAPS is a transaction table in the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. The table stores the mapping between a sales role and a compensation plan, effectively defining which compensation plans apply to which sales roles within a given operating context. This mapping is fundamental to the Incentive Compensation engine, because plan assignment determines how credit, quota, and earnings calculations are attributed to individual participants.

The table is owned by the CN schema and is classified as VALID in the ETRM repository. Its primary key is CN_ROLE_PLAN_MAPS_PK, defined on the ROLE_PLAN_MAP_ID column, with a corresponding unique index CN_ROLE_PLAN_MAPS_U1 on the same column serving as the business-key candidate. The documented physical schema contains 29 columns. Under the heuristic Data Vault classification mined from the foreign-key structure, this object leans toward a satellite pattern: it records the effective-dated attributes of a relationship between a role and a plan, rather than acting as a pure hub or link. This classification should be treated as a modeling suggestion only, not as a binding design constraint.

Key Information Stored

The table's most operationally significant columns are:

• ROLE_PLAN_MAP_ID — surrogate primary key, uniquely identifying each role-to-plan mapping record.
• ROLE_ID — foreign key to CN_ROLE_DETAILS, identifying the sales role being mapped.
• ROLE_MODEL_ID — foreign key to CN_ROLE_MODELS_ALL, tying the mapping to a specific role model version or definition.
• COMP_PLAN_NAME — the compensation plan name to which the role is mapped.
• START_DATE and END_DATE — the effective-dating window during which the mapping is active.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org or data-security partitioning.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle descriptive flexfield (DFF) columns for capturing client-specific mapping attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — the standard WHO audit columns.
• OBJECT_VERSION_NUMBER — used for optimistic locking during concurrent updates.

The distinction between the surrogate key (ROLE_PLAN_MAP_ID) and the business-key candidate (also ROLE_PLAN_MAP_ID via CN_ROLE_PLAN_MAPS_U1) is important: in this table the two coincide, meaning no separate natural composite key is documented. This simplifies single-row lookups but means business uniqueness on ROLE_ID plus COMP_PLAN_NAME plus date range must be enforced at the application layer.

Common Use Cases and Queries

The table is typically queried to answer questions such as which compensation plans are assigned to a given role, when a mapping became effective or expired, and how role-model changes affect plan coverage. A representative query joins the table to CN_ROLE_DETAILS and CN_ROLE_MODELS_ALL:

• SELECT r.Role_Plan_Map_Id, r.Role_Id, r.Comp_Plan_Name, r.Start_Date, r.End_Date FROM Cn_Role_Plan_Maps r WHERE r.Role_Id = :role_id AND Sysdate BETWEEN r.Start_Date AND Nvl(r.End_Date, Sysdate);
• Historical reporting: select mappings where START_DATE falls within a period to audit plan assignment changes over time.
• Multi-org filtering: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to restrict results to the correct operating unit or business group.
• Data-extract and integration loads: the DFF attribute columns are commonly used by downstream reporting or commission calculation feeds.

Related Objects

The following objects are directly referenced by or depend on CN_ROLE_PLAN_MAPS, based on documented foreign-key relationships:

• CN_ROLE_DETAILS — joined on ROLE_ID; the parent of the role definition.
• CN_ROLE_MODELS_ALL — joined on ROLE_MODEL_ID; defines the role model version.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; enforces data-security and multi-org access.
• CN_ROLE_PLAN_MAPS_PK (primary key) and CN_ROLE_PLAN_MAPS_U1 (unique index) — control row uniqueness and lookup performance.

Because the object is documented as satellite-leaning, transaction-level incentive processing routines in the CN module read this table to resolve the correct plan for each role before crediting and calculation routines execute.