Search Results role_plan_id

Overview

The CN_ROLE_PLANS_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module, specifically in versions 12.1.1 and 12.2.2. As defined in the ETRM metadata, its primary function is to manage the assignment of compensation plans to compensation roles. This table serves as a critical junction, linking the definition of a compensation role with the specific incentive plans that individuals holding that role are eligible for. Its existence is fundamental to the administration of sales compensation, ensuring that the correct plans are associated with the appropriate organizational roles, thereby governing commission calculations and payout eligibility.

Key Information Stored

The table's structure centers on the relationship between a role and a plan. The primary key, ROLE_PLAN_ID, uniquely identifies each assignment record. The most critical foreign key column is COMP_PLAN_ID, which references CN_COMP_PLANS_ALL. This link ties the assignment to the specific compensation plan's definition, rules, and formulas. While the provided metadata does not list all columns, a typical implementation would also include identifiers for the compensation role (likely a foreign key to a roles table), effective start and end dates for the assignment, and context columns for multi-organization (Multi-Org) architecture, as indicated by the "_ALL" suffix. The ROLE_PLAN_ID is also referenced as a foreign key by the CN_SRP_PLAN_ASSIGNS_ALL table, which manages individual salesperson plan assignments.

Common Use Cases and Queries

This table is central to configuration and reporting tasks. Administrators query it to audit which compensation plans are available to specific roles or to validate setup before a compensation cycle. A common reporting use case involves listing all active plan assignments for a set of roles to support compensation planning. A typical SQL pattern would join CN_ROLE_PLANS_ALL with CN_COMP_PLANS_ALL to get plan details.

• Sample Query: To find all compensation plans assigned to a specific role, one might use: SELECT cp.name, rpa.* FROM cn_role_plans_all rpa, cn_comp_plans_all cp WHERE rpa.comp_plan_id = cp.comp_plan_id AND rpa.role_id = :role_id AND SYSDATE BETWEEN rpa.start_date_active AND NVL(rpa.end_date_active, SYSDATE);
• Data in this table is typically maintained via the Oracle Incentive Compensation administrator forms or related APIs, not via direct SQL manipulation.

Related Objects

As per the documented foreign keys, CN_ROLE_PLANS_ALL has integral relationships with other key Incentive Compensation tables.

• CN_COMP_PLANS_ALL: This is the master table for compensation plan definitions. The COMP_PLAN_ID in CN_ROLE_PLANS_ALL derives its valid values from this table.
• CN_SRP_PLAN_ASSIGNS_ALL: This table manages the assignment of specific plans to individual salespeople (SRPs). It references ROLE_PLAN_ID from CN_ROLE_PLANS_ALL, establishing that an individual's plan assignment is based on a valid role-to-plan assignment.
• While not explicitly listed, CN_ROLE_PLANS_ALL would also relate to the table storing compensation role definitions (e.g., CN_ROLES or similar) via a ROLE_ID column.