Search Results cn_srp_plan_assigns_all

Overview

The CN_SRP_PLAN_ASSIGNS_ALL table is a core transactional entity within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module, specifically in versions 12.1.1 and 12.2.2. It serves as the master repository for salesperson plan assignments, formally linking individual sales representatives (resources) to specific compensation plans. This linkage is a fundamental prerequisite for the entire incentive calculation and payout process. The table's "ALL" suffix indicates it is a multi-organization enabled table, storing data across operating units, which is critical for global deployments. As the central assignment record, it acts as a parent to numerous downstream transactional tables that track quotas, periods, rates, and postings for each unique assignment.

Key Information Stored

While the provided metadata does not list all columns, the primary and foreign key relationships define its essential structure. The table's unique identifier is the SRP_PLAN_ASSIGN_ID, which is the primary key. Each record establishes a relationship between a SALESREP_ID (the salesperson) and a COMP_PLAN_ID (the compensation plan), forming a unique key constraint (CN_SRP_PLAN_ASSIGNS_UK1). This prevents duplicate assignments. A critical foreign key is ROLE_PLAN_ID, which links to the CN_ROLE_PLANS_ALL table, allowing assignments to be based on a salesperson's role. Typical columns in such a table would also include effective start and end dates for the assignment, creation and last update information, and the ORG_ID for multi-org partitioning.

Common Use Cases and Queries

This table is central to administrative and reporting activities. Common use cases include auditing which sales reps are assigned to specific plans, identifying reps without active plan assignments, and analyzing assignment history. A frequent reporting requirement is to list all active assignments with plan and salesperson details. A sample query pattern would join to the salesperson (JTF_RS_SALESREPS) and plan (CN_COMP_PLANS_TL) tables:

• SELECT sra.salesrep_id, sra.comp_plan_id, sr.resource_name, cpt.name plan_name FROM cn_srp_plan_assigns_all sra, jtf_rs_salesreps sr, cn_comp_plans_tl cpt WHERE sra.salesrep_id = sr.salesrep_id AND sra.comp_plan_id = cpt.comp_plan_id AND sysdate BETWEEN sra.start_date AND NVL(sra.end_date, sysdate+1);

Data in this table is primarily managed through the Oracle Incentive Compensation administrator interface (Compensation Manager), not via direct SQL manipulation.

Related Objects

The CN_SRP_PLAN_ASSIGNS_ALL table is a pivotal hub in the Incentive Compensation schema, with extensive foreign key relationships. It references two parent tables: CN_COMP_PLANS_ALL via COMP_PLAN_ID and CN_ROLE_PLANS_ALL via ROLE_PLAN_ID. More significantly, its primary key (SRP_PLAN_ASSIGN_ID) is referenced as a foreign key by seven key transactional child tables, demonstrating its central role:

• CN_POSTING_DETAILS_ALL: Links posted transactions to the specific plan assignment.
• CN_SRP_PERIODS_ALL: Stores the processing periods for the assignment.
• CN_SRP_PERIOD_QUOTAS_ALL & CN_SRP_PER_QUOTA_RC_ALL: Hold period-level quota targets and results.
• CN_SRP_QUOTA_ASSIGNS_ALL & CN_SRP_QUOTA_RULES_ALL: Manage quota assignment rules.
• CN_SRP_RATE_ASSIGNS_ALL: Stores the rate schedules applied to the assignment.

These relationships ensure all incentive calculations are intrinsically tied to a valid salesperson-plan assignment record.