Search Results convert_clob_to_xmltype

Overview

CN_PLANCOPY_UTIL_PVT is a private (PVT) PL/SQL package body owned by APPS that belongs to the Oracle Enterprise Trade Management (ETRM) product family, specifically the Oracle Channel Revenue Management / Trade Management Plans module. It provides the internal utility layer that supports the copying and duplication of trade planning components — plans, plan elements, rate tables, rate dimensions, expressions, formulas, and scenarios. The leading token "CN" in the package name corresponds to the ETRM schema prefix, while "PLANCOPY_UTIL" denotes its role as a plan-copy utility and "PVT" identifies it as a private API not intended for direct third-party invocation.

The package encapsulates the low-level mechanics required when a user duplicates an existing plan or its dependent objects: it generates collision-free names, validates name lengths against column constraints, converts large-object data types, and updates expression records that are re-used during a copy. Because trade plans in ETRM carry names that must be unique within an organization and constrained by column widths, the utility centralizes the naming and length rules so that the same logic is applied across every copied component type.

Key Procedures and Functions

• GET_UNIQUE_NAME_FOR_COMPONENT — Builds a unique, length-compliant name for the component being copied. It derives a random numeric suffix via DBMS_RANDOM, concatenates it with a localized copy message or the "CN_PLANS" string retrieved through FND_MESSAGE, and truncates the source name to fit the target column. The procedure is type-aware, honoring distinct column widths for plan elements and rate tables (80 characters), plans, rate dimensions, expressions and formulas (30 characters), and scenarios (80 characters).
• CONVERT_BLOB_TO_CLOB — Converts character data stored as a BLOB into a CLOB, a conversion required when copied expression or SQL text is moved between columns with differing LOB storage types.
• CONVERT_CLOB_TO_XMLTYPE — Materializes CLOB content into an XMLTYPE instance, used where copied component metadata is persisted or compared as XML.
• CONVERT_BLOB_TO_XMLTYPE — Converts BLOB content directly into XMLTYPE, supporting components whose original storage was binary.
• UPDATE_EXISTING_EXPRESSION — Updates an existing expression record as part of the copy flow, allowing a plan copy operation to reuse and re-point expression definitions rather than duplicating them.
• CHECK_NAME_LENGTH — Validates that a proposed component name fits within the length constraints of the destination column, guarding against truncation or ORA-12899 errors during insertion.

Tables Accessed

The package operates predominantly on the ETRM plan and calculation tables accessed through APPS synonyms. The _ALL tables — CN_COPY_REQUESTS_ALL, CN_COMP_PLANS_ALL, CN_QUOTAS_ALL, CN_RATE_DIMENSIONS_ALL, CN_RATE_SCHEDULES_ALL, CN_SCENARIOS_ALL, CN_CALC_FORMULAS_ALL, CN_CALC_SQL_EXPS_ALL — are the multi-org enabled manufacturing base tables holding plans, quotas, rate structures, scenarios, formulas, and SQL expressions. The non-_ALL tables CN_CALC_EDGES, CN_CALC_EDGES_S, CN_CALC_FORMULAS, and CN_CALC_SQL_EXPS represent the underlying and translated seed-level records. CN_COPY_REQUESTS_ALL is of particular significance as it tracks the copy request that drives this utility. Utility packages DBMS_LOB, DBMS_RANDOM, and the DUAL pseudo-table are referenced for LOB manipulation, random-name generation, and scalar selects, respectively.

Usage Notes

CN_PLANCOPY_UTIL_PVT is a private package and is not exposed as a public API; it is referenced by five other ETRM packages within the plan copy framework. It is typically invoked indirectly — from the ETRM Plan Copy concurrent program and from the Trade Management Plans forms when a user performs a "Copy Plan" action. Developers extending ETRM plan duplication should call the public-facing wrapper packages rather than this private utility, since the utility's signature and behavior are subject to change without notice. In custom integrations, the name-generation logic should be replicated only if the standard copy flow cannot be reused, and callers must preserve the maximum name lengths enforced by CHECK_NAME_LENGTH to avoid insert failures.