Results for “cn_srp_total_comps_all”

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

Overview

The CN_SRP_TOTAL_COMPS_ALL table is a core configuration and results object within the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, available in both the 12.1.1 and 12.2.2 releases. It stores the total compensation calculated for both fixed and quota-based components of a given compensation model, resolved for a specific attainment tier. In practical terms, this table answers the question: "For a given sales role participating in a given model, and having reached a specific attainment tier, what is the total payable compensation?" This makes it a foundational reference for quota attainment processing, commission calculation, and downstream crediting logic in Incentive Compensation.

From a Data Vault modeling perspective, the metadata classifies this table heuristically as standalone. No parent hub or link dependency is asserted by the mined FK structure. A Data Vault practitioner may therefore model CN_SRP_TOTAL_COMPS_ALL as a satellite attached to the business key formed by SRP_ROLE_ID and ATTAIN_TIER_ID, with the remaining descriptive and measure columns (TOTAL_COMP, PERCENT, and the ATTRIBUTE1–15 flex fields) held as satellite payload. This classification is a suggestion only; the transactional and configuration semantics of the table should drive the final modeling decision.

Key Information Stored

The table carries 32 documented columns in the ETRM 12.2.2 physical schema. The most significant are summarized below.

• SRP_TOTAL_COMP_ID — Surrogate primary key, enforced by the CN_SRP_TOTAL_COMPS_PK constraint. It uniquely identifies each compensation total record.
• SRP_ROLE_ID and ATTAIN_TIER_ID — Together these form the business-key candidate captured by the CN_SRP_TOTAL_COMPS_U2 unique index. They identify which sales role and which attainment tier the total applies to.
• ROLE_MODEL_ID — References CN_ROLE_MODELS_ALL, linking the total to a specific role's participation in a compensation model.
• ROLE_ID — The underlying role identifier associated with the calculation context.
• ATTAIN_SCHEDULE_ID — References CN_ATTAIN_SCHEDULES, tying the record to the attainment schedule that governs tier evaluation.
• PERCENT — The percentage rate applied for the tier (for example, a commission rate or payout percentage).
• TOTAL_COMP — The total compensation amount computed for the fixed and quota-based components.
• ORG_ID — The operating unit, supporting multi-org data segregation.
• SECURITY_GROUP_ID — References FND_SECURITY_GROUPS, controlling row-level access to the compensation data.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the OA Framework for concurrent update protection.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield columns enabling client-specific extension without schema change.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking record creation and modification.

Common Use Cases and Queries

Typical usage centers on retrieving the compensation amount a role is entitled to at a given attainment tier. A representative query joins the table to the role model and tier definitions:

• Tier payout lookup: SELECT t.SRP_ROLE_ID, t.ATTAIN_TIER_ID, t.PERCENT, t.TOTAL_COMP FROM CN_SRP_TOTAL_COMPS_ALL t WHERE t.ORG_ID = :org_id AND t.SRP_ROLE_ID = :role_id.
• Model-level compensation summary: Aggregate TOTAL_COMP grouped by ROLE_MODEL_ID to compare fixed versus quota-based components across a model.
• Attainment schedule audit: Filter by ATTAIN_SCHEDULE_ID to verify that tier percentages align with the active schedule.
• Security-scoped reporting: Restrict results using SECURITY_GROUP_ID to enforce row-level access for a reporting user.
• Concurrent processing reference: Incentive Compensation concurrent programs read this table during calculation to resolve totals by tier.

Reports commonly expose PERCENT and TOTAL_COMP side by side so compensation analysts can validate that computed amounts reflect the intended tier rate.

Related Objects

The following objects are most significant to CN_SRP_TOTAL_COMPS_ALL, based on documented foreign-key relationships:

• CN_SRP_ROLE_DTLS_ALL — joined via SRP_ROLE_ID; provides the role detail context for each total.
• CN_ROLE_MODELS_ALL — joined via ROLE_MODEL_ID; defines the model in which the role participates.
• CN_ATTAIN_TIERS — joined via ATTAIN_TIER_ID; supplies the tier definition that drives PERCENT and TOTAL_COMP.
• CN_ATTAIN_SCHEDULES — joined via ATTAIN_SCHEDULE_ID; provides the schedule governing tier evaluation.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; enforces data security at query time.

These parent objects should be joined whenever descriptive context such as tier names, model names, or role attributes is required in reporting. Because the table is classified as standalone, no child tables declare a direct FK back to it; dependent logic is typically driven through Incentive Compensation concurrent programs and the CN APIs that consume the calculated totals.