Search Results cn_calc_formulas_all




Overview
Explore in AI Chat

Overview

The CN_CALC_FORMULAS_ALL table is a core data object within the Oracle E-Business Suite (EBS) Incentive Compensation (CN) module. It serves as the central repository for all calculation formulas defined in the system. These formulas are the programmable logic used to compute earnings, performance metrics, quotas, and other incentive-related values for compensation plans. The table's role is fundamental to the configuration and execution of the compensation engine, as every formula referenced by a performance measure, quota, or rate table assignment must have a corresponding definition stored in this table. Its "ALL" suffix indicates it is a multi-organization table, capable of storing data partitioned by operating unit.

Key Information Stored

While the provided metadata does not list specific columns beyond the primary key, the table's description and relationships imply it stores critical attributes for each formula. The primary identifier is the CALC_FORMULA_ID. A formula record would typically include a name, description, and the actual formula text or a reference to the executable logic. Given its foreign key relationship with CN_PERF_MEASURES_ALL via PERF_MEASURE_ID, the table also links formulas to specific performance measures. Other essential columns likely include control attributes such as ENABLED_FLAG, START_DATE, END_DATE, and CREATED_BY/CREATION_DATE for auditing. The structure supports complex, hierarchical formulas, as evidenced by its recursive relationship with CN_CALC_EDGES_ALL for parent-child formula dependencies.

Common Use Cases and Queries

This table is central to administrative and diagnostic activities within Incentive Compensation. A common use case is auditing all formulas associated with active compensation plans to validate business logic before a compensation cycle. System administrators may query this table to identify formulas that are referenced by multiple objects or to analyze formula hierarchies. A typical diagnostic query would join to performance measures to trace calculation results back to their source definition.

  • Sample Query (List Active Formulas with Associated Performance Measure):
    SELECT cf.calc_formula_id, cf.name, pm.name AS perf_measure_name
    FROM cn_calc_formulas_all cf, cn_perf_measures_all pm
    WHERE cf.perf_measure_id = pm.perf_measure_id
    AND cf.enabled_flag = 'Y'
    AND SYSDATE BETWEEN cf.start_date AND NVL(cf.end_date, SYSDATE);
  • Reporting Use Case: Generating a master list of all calculation formulas, their assignment to quotas or rate tables, and their effective dates for compliance and change management documentation.

Related Objects

The CN_CALC_FORMULAS_ALL table has extensive relationships, acting as a hub for incentive calculation definitions. The documented foreign keys are:

  • CN_PERF_MEASURES_ALL: Links a formula to a specific performance measure (PERF_MEASURE_ID).
  • CN_CALC_EDGES_ALL (PARENT_ID, CHILD_ID): Enables recursive, hierarchical formula structures where formulas are composed of other formulas.
  • CN_FORMULA_INPUTS_ALL: Stores the input parameters or variables required for a specific formula (CALC_FORMULA_ID).
  • CN_QUOTAS_ALL: Assigns a calculation formula to a quota definition (CALC_FORMULA_ID).
  • CN_ROLE_QUOTA_CATES: Associates a formula with role-based quota categories (CALC_FORMULA_ID).
  • CN_RT_FORMULA_ASGNS_ALL: Assigns a calculation formula to a rate table (CALC_FORMULA_ID).

These relationships underscore the table's critical position; any modification to a formula record can directly impact performance measures, quota calculations, and compensation rate determinations.