Search Results cn_calc_sql_exps_all




Overview
Explore in AI Chat

Overview

CN_CALC_SQL_EXPS_ALL is a transactional configuration table in the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores the SQL expressions consumed by the Incentive Compensation calculation engine during the calculation process. Each row defines a reusable, named SQL expression—composed of a SELECT clause, a FROM clause, and an associated display expression—that the calculation engine resolves at runtime to derive computed values for participants, transactions, credits, and compensation results. Administrators and implementers maintain these definitions to avoid hard-coding business logic, allowing the calculation process to retrieve derived metrics such as attainment, quota ratios, or custom earning formulas.

From a Data Vault modeling perspective, the mined heuristic classification for this table is standalone. It neither behaves as a pure hub, link, nor satellite in the mined relationship graph; it is best treated as a reference or configuration entity that anchors expression definitions consumed downstream by calculation logic. Multi-organization support is handled through ORG_ID, and security partitioning is enforced through SECURITY_GROUP_ID.

Key Information Stored

The table is owned by the CN schema and contains 35 documented columns. The most significant are described below.

  • CALC_SQL_EXP_ID — the surrogate primary key. It is enforced by the unique index CN_CALC_SQL_EXPS_U1 and uniquely identifies each SQL expression definition.
  • NAME — the business identifier for the expression. Together with ORG_ID it forms the business-key candidate enforced by unique index CN_CALC_SQL_EXPS_U2.
  • ORG_ID — the operating unit/org context, used both for the business key and for multi-org data segregation.
  • DESCRIPTION — free-text description of the expression’s purpose.
  • STATUS — indicates whether the expression is active and available for use in calculation.
  • EXP_TYPE_CODE — classifies the expression type, governing how the engine interprets and applies it.
  • EXPRESSION_DISP — the display form of the expression shown to users.
  • SQL_SELECT — the SELECT portion of the underlying SQL.
  • SQL_FROM — the FROM portion of the underlying SQL.
  • PIPED_SQL_SELECT / PIPED_SQL_FROM — pipelined variants of the SELECT and FROM clauses used for set-based expression processing.
  • PIPED_EXPRESSION_DISP — the display form of the piped expression.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS controlling row-level access.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the AOL/BC4J framework.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Fifteen ATTRIBUTE (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15) columns provide the standard EBS descriptive-flexfield extension area. Six system-managed LOB indexes (SYS_IL0000107485C00007$$ through C00012$$) exist on the CLOB-backed expression columns.

Common Use Cases and Queries

Typical usage centers on auditing, cloning, and troubleshooting calculation logic.

  • Listing all active expressions for an operating unit:
    SELECT CALC_SQL_EXP_ID, NAME, EXP_TYPE_CODE, SQL_SELECT, SQL_FROM FROM CN.CN_CALC_SQL_EXPS_ALL WHERE ORG_ID = :p_org AND STATUS = 'A';
  • Locating an expression by business key: filter on NAME and ORG_ID, which map to the CN_CALC_SQL_EXPS_U2 unique index.
  • Reviewing piped versus non-piped definitions by comparing SQL_SELECT/SQL_FROM against PIPED_SQL_SELECT/PIPED_SQL_FROM and PIPED_EXPRESSION_DISP.
  • Detecting maintenance activity through LAST_UPDATED_BY and LAST_UPDATE_DATE for change-audit reporting.
  • Security-scoped reporting by joining SECURITY_GROUP_ID to FND_SECURITY_GROUPS.

Because expression text is stored in LOB columns, extraction queries should account for CLOB handling when exporting definitions for migration or documentation.

Related Objects

The mined foreign-key relationship identifies FND_SECURITY_GROUPS as a direct reference target, joined via SECURITY_GROUP_ID. Functionally, the following objects are most significant to its use:

  • FND_SECURITY_GROUPS — referenced by CN_CALC_SQL_EXPS_ALL.SECURITY_GROUP_ID for security partitioning.
  • CN_CALC_FORMULAS_ALL — calculation formulas that consume SQL expression definitions during computation.
  • CN_CALC_METHODS_ALL — calculation methods that orchestrate expressions within the calculation run.
  • CN_PLANS_ALL — plan definitions whose calculation processes reference these expressions.
  • CN_PARTICIPANTS_ALL — participants for whom computed expression results are produced.
  • CN_CALC_RSLTS_ALL — calculated results populated using the resolved SQL expressions.

These relationships reflect the functional dependency chain of the Incentive Compensation calculation engine, in which CN_CALC_SQL_EXPS_ALL serves as the centralized repository of SQL-driven expression logic.