Search Results perf_measure_id

Overview

The CN_PERF_MEASURES_ALL table is a core data repository within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module, specifically for versions 12.1.1 and 12.2.2. It serves as the master definition table for all performance measures used in calculating commissions, bonuses, and other incentive payouts. A performance measure is a quantifiable metric, such as revenue, quantity sold, or margin, against which a salesperson's or compensation recipient's performance is evaluated. The table's primary role is to store the foundational metadata for these measures, which are subsequently referenced throughout the complex incentive calculation engine. Its multi-org enabled structure, indicated by the "_ALL" suffix, allows the definition of performance measures to be shared or partitioned across multiple operating units.

Key Information Stored

While the provided metadata does not list specific columns, the table's primary key is documented as PERF_MEASURE_ID, a unique system-generated identifier for each performance measure. Based on its central role, the table typically stores descriptive and control attributes for a measure. Common columns would include the measure's NAME and DESCRIPTION, its DATA_TYPE (e.g., currency, quantity), and flags controlling its status and usage (e.g., ACTIVE_FLAG). It would also contain columns for the associated INCENTIVE_TYPE and links to the source of the transactional data, such as the OBJECT_VERSION_NUMBER for optimistic locking, CREATION_DATE, and CREATED_BY. The ORG_ID column is critical for multi-org partitioning.

Common Use Cases and Queries

This table is central to administrative setup and diagnostic reporting within Incentive Compensation. Common use cases include generating a catalog of all active performance measures for plan design, auditing changes to measure definitions, and troubleshooting calculation issues by tracing a measure back to its source. A typical administrative query would retrieve all measures for a specific operating unit:

• SELECT PERF_MEASURE_ID, NAME, DESCRIPTION FROM CN_PERF_MEASURES_ALL WHERE ORG_ID = :p_org_id AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE) ORDER BY NAME;

For integration or data validation, a common pattern is to join this table to transaction source views to verify mapped measures exist:

• SELECT DISTINCT cpma.NAME FROM MY_SOURCE_TRX mst, CN_PERF_MEASURES_ALL cpma WHERE mst.PERF_MEASURE_CODE = cpma.NAME AND cpma.PERF_MEASURE_ID = :p_measure_id;

Related Objects

The CN_PERF_MEASURES_ALL table is a pivotal hub in the Incentive Compensation data model, with several key objects referencing it via foreign key constraints, as documented in the ETRM metadata.

• CN_CALC_FORMULAS_ALL: References PERF_MEASURE_ID. This is a direct relationship where calculation formulas are defined for a specific performance measure.
• CN_CALC_EDGES_ALL: References PERF_MEASURE_ID twice, via PARENT_ID and CHILD_ID. This table manages the hierarchical relationships between performance measures, enabling complex, multi-level roll-up calculations (e.g., a "Regional Revenue" measure being the sum of individual "Territory Revenue" measures).

These relationships underscore the table's critical function: a measure defined in CN_PERF_MEASURES_ALL can have associated calculation formulas and can participate in a calculation hierarchy, forming the backbone of the incentive plan logic.