Search Results sys_il0000107485c00007

Overview

CN.CN_CALC_SQL_EXPS_ALL is a transaction data table in the Oracle E-Business Suite Channel Revenue Management (CN) schema. It stores the SQL expressions consumed by the calculation engine that drives channel rebate, accrual, and settlement processing. Each row represents a reusable expression definition composed of discrete SELECT and FROM fragments, allowing the calculation process to assemble and execute dynamic SQL at runtime rather than relying on hard-coded logic. The table therefore acts as a metadata repository for expression-driven computation, an architectural pattern common to EBS products that require configurable business rules.

From a Data Vault modeling perspective, the table is classified as standalone (heuristic, mined from foreign key structure). No parent hub dependency governs its identity beyond the operational ORG_ID context, although a documented foreign key to FND_SECURITY_GROUPS via SECURITY_GROUP_ID provides multi-tenant security isolation consistent with the 12.1.1 and 12.2.2 releases. The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, the standard profile for transactional data objects in the EBS data model.

Key Information Stored

The surrogate primary key is CALC_SQL_EXP_ID, enforced by the unique index CN_CALC_SQL_EXPS_U1 and identified in the user search term cn_calc_sql_exps_u1. A second business-key candidate, CN_CALC_SQL_EXPS_U2, enforces uniqueness across NAME and ORG_ID, meaning expression names must be unique within an operating unit. Both indexes are stored in APPS_TS_TX_IDX.

• ORG_ID — organization identifier, part of the business key.
• CALC_SQL_EXP_ID — surrogate primary key.
• NAME and DESCRIPTION — business-facing identifier and description of the expression.
• STATUS — valid or invalid flag controlling whether the expression is usable.
• EXP_TYPE_CODE — classifies the expression type.
• EXPRESSION_DISP — displayable CLOB rendering of the expression content.
• SQL_SELECT and SQL_FROM — the executable SELECT and FROM clauses.
• PIPED_SQL_SELECT, PIPED_SQL_FROM, and PIPED_EXPRESSION_DISP — delimited variants used by the calculation engine for processing.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — descriptive flexfield segments for extensibility.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit and optimistic locking columns.

The six SYS_IL LOB indexes (SYS_IL0000107485C00007$$ through SYS_IL0000107485C00012$$) support the CLOB columns and reside in APPS_TS_TX_DATA.

Common Use Cases and Queries

Developers and functional analysts query this table to inspect, validate, or debug calculation expressions. A typical lookup retrieves the expression definition by name within an organization:

• SELECT calc_sql_exp_id, name, status, exp_type_code, expression_disp FROM cn_calc_sql_exps_all WHERE name = :name AND org_id = :org_id.
• Filtering on STATUS = 'VALID' isolates expressions eligible for the calculation process.
• Joining SECURITY_GROUP_ID to FND_SECURITY_GROUPS confirms tenant eligibility for a given responsibility.
• Reporting by EXP_TYPE_CODE profiles the mix of expression types configured across operating units.

Because SQL_SELECT, SQL_FROM, and their PIPED variants are CLOB columns, queries in SQL*Plus require SET LONG or DBMS_LOB.SUBSTR to view content beyond the default buffer.

Related Objects

• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID, the only documented foreign key.
• CN_CALC_SQL_EXPS_U1 / U2 — unique indexes enforcing primary and business keys.
• FND_DESCR_FLEX_COL_USAGE and related DFF metadata — define the ATTRIBUTE_CATEGORY flexfield structure.
• APPS_TS_TX_DATA / APPS_TS_TX_IDX — tablespaces holding table, LOB, and index segments.

Consumers of this table sit primarily within the CN calculation engine, which reads valid expressions, assembles the SELECT and FROM fragments, and executes the resulting SQL during rebate and accrual processing.