Results for “attain_schedule_id”

50+ results




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

Overview

CN_ATTAIN_SCHEDULES is a table owned by the CN schema within the Oracle Incentive Compensation module. Its documented purpose is to store the names of attainment schedules that are assigned to compensation contracts. An attainment schedule defines the measurement construct against which a participant's performance is evaluated — for example, the revenue thresholds or quota percentages that determine payout credit — so this table acts as the master registry of valid attainment definitions available to the compensation engine.

The ETRM metadata classifies this object, using a heuristic derived from its foreign-key topology, as hub-leaning in Data Vault terms. This is a modeling suggestion rather than a physical attribute: the table holds a stable, uniquely identified business entity (the attainment schedule) that is referenced by multiple dependent tables, which is the characteristic profile of a hub. Its own outgoing reference to FND_SECURITY_GROUPS is a lightweight attribute rather than a genuine parent relationship, reinforcing the hub interpretation.

Key Information Stored

The table is documented with 25 physical columns. The most significant by role are:

  • ATTAIN_SCHEDULE_ID — the surrogate primary key, enforced by CN_ATTAIN_SCHEDULES_PK. It is also the single documented unique index (CN_ATTAIN_SCHEDULES_U1), confirming it as the sole business-key candidate in the object.
  • NAME — the descriptive name of the attainment schedule, which is the value surfaced to compensation administrators when selecting a schedule on a contract.
  • SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, used for multi-tenant or organizational data segregation under Oracle's security group model.
  • OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Application Object Library to detect concurrent updates.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns, allowing customers to extend the table with site-specific attributes without schema modification.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — the standard audit columns captured by the WHO columns framework.

Constitutionally, this is a narrow definition table: it carries identity, naming, security, auditing, and flexfield extensibility, while the actual attainment tier logic resides in dependent tables such as CN_ATTAIN_TIERS.

Common Use Cases and Queries

Typical usage centres on resolving schedule IDs to human-readable names, validating assignments, and auditing contract configuration.

  • Listing all active attainment schedules for a picklist or LOV: SELECT attain_schedule_id, name FROM cn.cn_attain_schedules ORDER BY name;
  • Joining schedules to their tiers for a full attainment definition: SELECT s.name, t.* FROM cn.cn_attain_schedules s, cn.cn_attain_tiers t WHERE s.attain_schedule_id = t.attain_schedule_id;
  • Identifying schedules not referenced by any role model (orphan or obsolete definitions) via an outer join to CN_ROLE_MODELS_ALL.
  • Security-scoped reporting using SECURITY_GROUP_ID to restrict output to a requesting operating unit.
  • Audit reconciliation of creation and update metadata against concurrent-program activity.

Related Objects

CN_ATTAIN_SCHEDULES_PK is referenced by several dependent tables that consume the attainment schedule identifier:

  • CN_ATTAIN_TIERS — joins on ATTAIN_SCHEDULE_ID and holds the tier breakpoints belonging to each schedule.
  • CN_ROLE_DETAILS — joins on ATTAIN_SCHEDULE_ID, linking a compensation role to its assigned attainment schedule.
  • CN_ROLE_MODELS_ALL — joins on ATTAIN_SCHEDULE_ID, attaching the schedule to a role model definition.
  • CN_SRP_PAYOUT_DTLS_ALL — joins on ATTAIN_SCHEDULE_ID, recording the schedule used when a payout detail was calculated.
  • CN_SRP_TOTAL_COMPS_ALL — joins on ATTAIN_SCHEDULE_ID, capturing the schedule associated with total compensation results.
  • FND_SECURITY_GROUPS — the parent of this table's own SECURITY_GROUP_ID foreign key.