Results for “cn_plan_texts_u1”

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

Overview

CN.CN_PLAN_TEXTS is a transactional table in the Oracle E-Business Suite Compensation (CN) schema. It stores customized textual information associated with compensation contracts for each sales role. Specifically, users can personalize contract titles, club qualification criteria, terms and conditions clauses, and approver information for plan documents. This provides flexibility for organizations that need to surface role-specific or plan-specific language in their compensation plans.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, consistent with a transactional data object that undergoes frequent DML. Its FND Design Data registration confirms it is a shipped, seeded application table. The Data Vault classification heuristic identifies this object as a link, suggesting it may be modeled as a relationship (link) table that bridges a sales role to plan text content — reflected by its foreign keys to CN_ROLE_DETAILS, CN_QUOTA_CATEGORIES, CN_ROLE_MODELS_ALL, and FND_SECURITY_GROUPS.

Key Information Stored

The table's structure combines identity, business context, payload, and descriptive flexfield columns:

• PLAN_TEXT_ID — NUMBER(15), the primary key and the unique business-key candidate (unique index CN_PLAN_TEXTS_U1). This surrogate identifier uniquely identifies each plan text record.
• ROLE_ID — NUMBER(15), the sales role identifier linking the text to a specific role. Indexed via CN_PLAN_TEXTS_N1 (nonunique).
• ROLE_MODEL_ID — NUMBER(15), foreign key to CN_ROLE_MODELS_ALL, tying the plan text to a role model.
• QUOTA_CATEGORY_ID — NUMBER(15), foreign key to CN_QUOTA_CATEGORIES, associating the text with a quota category.
• SEQUENCE_ID — NUMBER(15), controlling ordering of text records within a role/plan.
• TEXT_TYPE — VARCHAR2(30), categorizing the plan text type (e.g., title, terms, approver information).
• TEXT — VARCHAR2(2000), the primary plan text content.
• TEXT2 — VARCHAR2(2000), a second text content field, useful for supplementary or overflow text.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — VARCHAR2(150 each), a descriptive flexfield structure and its segments for customer-extensible data.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enabling multi-organization/security partitioning.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, and OBJECT_VERSION_NUMBER support auditing and optimistic locking.

Common Use Cases and Queries

Typical scenarios involve retrieving or maintaining the customized text displayed on compensation plan documents for specific roles and quota categories. A common query joins to the role details to resolve readable role names:

• Retrieve plan texts for a role: SELECT PLAN_TEXT_ID, TEXT_TYPE, TEXT FROM CN_PLAN_TEXTS WHERE ROLE_ID = :role_id ORDER BY SEQUENCE_ID;
• Join to role details: SELECT p.TEXT_TYPE, p.TEXT FROM CN_PLAN_TEXTS p, CN_ROLE_DETAILS r WHERE p.ROLE_ID = r.ROLE_ID AND r.ROLE_ID = :role_id;
• Filter by plan text type: SELECT * FROM CN_PLAN_TEXTS WHERE TEXT_TYPE = :type;
• Security-scoped reporting using SECURITY_GROUP_ID for multi-organization access control.
• Descriptive flexfield extraction via ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 for customer-specific reporting.

Because TEXT and TEXT2 are each up to 2000 characters, reports should truncate or handle long text carefully. Index CN_PLAN_TEXTS_N1 on ROLE_ID optimizes role-based lookups.

Related Objects

The following objects are most significant, based on documented foreign key relationships:

• CN_ROLE_DETAILS — via CN_PLAN_TEXTS.ROLE_ID; provides the sales role context.
• CN_QUOTA_CATEGORIES — via CN_PLAN_TEXTS.QUOTA_CATEGORY_ID; associates quota categories.
• CN_ROLE_MODELS_ALL — via CN_PLAN_TEXTS.ROLE_MODEL_ID; defines the role model.
• FND_SECURITY_GROUPS — via CN_PLAN_TEXTS.SECURITY_GROUP_ID; governs security partitioning.
• CN_PLAN_TEXTS_PK — the primary key constraint on PLAN_TEXT_ID.
• CN_PLAN_TEXTS_U1 — the unique index underpinning the primary key and the searched identifier term.

Together these objects form the relational backbone for maintaining role-level compensation plan text in Oracle EBS 12.1.1 and 12.2.2.