Results for “srp_plan_assign_id”

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

Overview

CN_SRP_PERIOD_QUOTAS_ALL is a transactional table within the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, documented as Salesperson Plan Elements by Period. It stores the period-level quota and performance results assigned to individual salespersons under the plan and quota assignment structures defined in Incentive Compensation. Each row links a salesperson to a specific quota, plan assignment, and accounting period, accumulating target amounts and period-to-date, year-to-date, inception-to-date, and rollover measures used to drive commission calculation and crediting.

The table resides in the CN schema and contains 66 columns. It is one of the highest-volume operational tables in the Compensation engine, and it is routinely queried by period for reconciliation, analytics, and downstream payout processing. The _ALL suffix indicates that the table is partitioned by operating unit and governed by Multi-Org Access Control through ORG_ID, and a same-named extension table (CN_SRP_PERIOD_QUOTAS_EXT_ALL) exists for customer-defined attributes.

From a heuristic Data Vault perspective, the table is classified as satellite-leaning. The documented metadata lists a single-column surrogate primary key (SRP_PERIOD_QUOTA_ID), but the dominant foreign-key relationships point outward to dimension-like assigners (CN_SRP_PLAN_ASSIGNS_ALL, CN_SRP_QUOTA_ASSIGNS_ALL, CN_QUOTAS_ALL, CN_PERIOD_STATUSES_ALL), suggesting the natural design is more consistent with a link/satellite hybrid rather than a pure hub. This classification is a modeling suggestion rather than an ETRM-certified designation.

Key Information Stored

The most operationally significant columns are:

• SRP_PERIOD_QUOTA_ID — the surrogate primary key CN_SRP_PERIOD_QUOTAS_PK; unique per row, used as the join target from child tables.
• SRP_QUOTA_ASSIGN_ID and SRP_PLAN_ASSIGN_ID — foreign keys to CN_SRP_QUOTA_ASSIGNS_ALL and CN_SRP_PLAN_ASSIGNS_ALL; these tie the row to the salesperson-plan-quota structure. SRP_QUOTA_ASSIGN_ID plus PERIOD_ID forms business-key candidate CN_SRP_PERIOD_QUOTAS_UK1.
• SALESREP_ID, PERIOD_ID, QUOTA_ID — the salesperson, accounting period (FK to CN_PERIOD_STATUSES_ALL), and quota (FK to CN_QUOTAS_ALL). Collectively these define business-key candidate CN_SRP_PERIOD_QUOTAS_UK2, while SRP_PLAN_ASSIGN_ID + QUOTA_ID + PERIOD_ID define CN_SRP_PERIOD_QUOTAS_UK3 and unique index CN_SRP_PERIOD_QUOTAS_U3. Additionally, CN_SRP_PERIOD_QUOTAS_U1 covers (SRP_PERIOD_QUOTA_ID, ORG_ID) for multi-org partitioning.
• TARGET_AMOUNT and ITD_TARGET — the period target and inception-to-date target for the salesperson's quota.
• QUOTA_ACHIEVED_PTD / QUOTA_ACHIEVED_YTD — credited achievement through period-to-date and year-to-date.
• COMMISSION_PAYED_PTD, COMMISSION_PAYED_YTD, COMMISSION_PAYED_ITD — compensation paid in the respective windows.
• PERIOD_PAYMENT and ITD_PAYMENT — payments accrued for the period versus inception-to-date.
• TRX_AMOUNT_PTD, TRX_AMOUNT_YTD, TRANSACTION_AMOUNT_PTD, TRANSACTION_AMOUNT_ITD — credited transaction value in each window.
• QUANTITY_PTD / QUANTITY_YTD — credited unit quantities.
• ROLLOVER and TOTAL_ROLLOVER — amounts carried forward between periods.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, used for row-level data security filtering.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard WHO columns and optimistic locking.

The _F-suffixed columns (for example QUOTA_ACHIEVED_PTD_F) mirror their base columns and hold functional-currency converted values.

Common Use Cases and Queries

Typical reporting includes salesperson performance dashboards, period close reconciliation of quota achievement, and prior-period payout auditing. A representative query returning current-period achievement per salesperson joins this table to the period and plan assignment tables:

• Quota achievement by salesperson for a period:

  SELECT qr.SALESREP_ID, qr.QUOTA_ACHIEVED_PTD, qr.TARGET_AMOUNT, qr.QUOTA_ACHIEVED_YTD
  FROM   CN_SRP_PERIOD_QUOTAS_ALL qr
  WHERE  qr.ORG_ID = :org_id
  AND    qr.PERIOD_ID = :period_id;
  

• Commission reconciliation vs. plan assignment: join CN_SRP_PLAN_ASSIGNS_ALL on SRP_PLAN_ASSIGN_ID to compare actual commissions against plan-defined quotas.
• Rollover analysis: filter on ROLLOVER and TOTAL_ROLLOVER to identify quarantined or carried-forward quota amounts.
• ETL / analytics extraction: because the _F columns provide functional-currency values, they are preferable for consolidated multi-org reporting where ORG_ID filtering is applied through MOAC.

Queries should always include ORG_ID or an appropriate security predicate to respect MOAC and row-level security through SECURITY_GROUP_ID.

Related Objects

The table participates in the Incentive Compensation schema as a central fact between plan assignments, quota assignments, and quota definitions. The principal related objects are:

• CN_SRP_PLAN_ASSIGNS_ALL — joined on SRP_PLAN_ASSIGN_ID; defines the plan a salesperson is enrolled in.
• CN_SRP_QUOTA_ASSIGNS_ALL — joined on SRP_QUOTA_ASSIGN_ID; the assignment of a quota to a salesperson.
• CN_QUOTAS_ALL — joined on QUOTA_ID; the quota definition.
• CN_PERIOD_STATUSES_ALL — joined on PERIOD_ID; the accounting period and its open/closed status.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; controls row-level access.
• CN_SRP_PER_QUOTA_RC_ALL — child table referencing SRP_PERIOD_QUOTA_ID; holds per-quota rate/credit calculations.
• CN_SRP_PERIOD_QUOTAS_EXT_ALL — extension table keyed by the same SRP_PERIOD_QUOTA_ID, supporting customer-defined flex attributes.

Writers to this table are principally the Incentive Compensation concurrent processes that populate period quotas and update achievement (the Calculate Incentive Compensation, period-start rollover, and quota achievement feeds). Direct DML is not supported; the table should be treated as read-only for reporting and integration purposes, with changes driven through the standard CN concurrent program interfaces.