Search Results update_existing_expression

Overview

CN_PLANCOPY_UTIL_PVT is a private PL/SQL utility package in the Oracle EBS Advanced Pricing module (Oracle Trade Management / Channel Revenue Management, "CN" schema prefix). It serves as the internal workhorse for plan copy and import/export operations within CN_COMP_PLANS. Its principal business function is to manipulate, transform, and validate the large object (LOB) payloads that carry pricing plan structures — formulas, calculation expressions, edges, rate schedules, and quota definitions — between environments or between plan versions. The package is declared AUTHID CURRENT_USER, meaning its DML and DDL execute under the privileges of the calling schema rather than the package owner, which is characteristic of an internal EBS utility invoked from a public API wrapper.

The header comment (version 120.3.12020000.2, last modified 2012/08/27) indicates the package was patched during the 12.1.3/12.2 development cycle. The presence of the internal note regarding a "BT issue" fixed on 10 March 2012 confirms that the package evolved to address defects in plan copy behavior rather than serving as a purely static utility.

Key Procedures and Functions

• GET_UNIQUE_NAME_FOR_COMPONENT — Generates a unique component name using caller-supplied prefix and suffix values. This prevents naming collisions when plan components (formulas, expressions, edges) are duplicated into a target plan or organization.
• CONVERT_BLOB_TO_CLOB — Converts a BLOB payload into a CLOB. This is required because plan copy export/import artifacts are stored as binary LOBs but downstream parsing and XML processing require character data.
• CONVERT_CLOB_TO_XMLTYPE — Promotes a CLOB to an XMLTYPE value, enabling XML-based manipulation or XQuery processing of the copied plan definition.
• CONVERT_BLOB_TO_XMLTYPE — A convenience combination of the two preceding conversions, taking a raw binary export/import payload directly to XMLTYPE.
• UPDATE_EXISTING_EXPRESSION — Added specifically to correct a defect in existing calculation expressions during plan copy. It updates expression records — including parent linkage as reflected in the truncated parameter list — to keep the expression hierarchy consistent after copy or merge.
• CHECK_NAME_LENGTH — Validates that a generated or supplied component name does not exceed the column length limits imposed by the CN pricing tables, raising the appropriate API error when it does.

The three conversion procedures follow the standard EBS API signature convention (p_api_version, p_init_msg_list, p_commit, p_validation_level, and the x_return_status / x_msg_count / x_msg_data output triple), and key off CN_COPY_REQUESTS_ALL.EXP_IMP_REQUEST_ID to identify the request being processed. UPDATE_EXISTING_EXPRESSION was introduced by developer Naren to resolve a reported bug.

Tables Accessed

The package touches a wide slice of the Advanced Pricing data model. CN_COPY_REQUESTS_ALL supplies the export/import request identifier that drives every conversion and update routine. CN_COMP_PLANS_ALL is the parent plan table. Expression and formula structures are handled through CN_CALC_SQL_EXPS, CN_CALC_SQL_EXPS_ALL, CN_CALC_FORMULAS, and CN_CALC_FORMULAS_ALL, with CN_CALC_EDGES and CN_CALC_EDGES_S holding the dependency graph that must remain internally consistent after a copy. Rate and quota constructs are represented by CN_QUOTAS_ALL, CN_RATE_DIMENSIONS_ALL, and CN_RATE_SCHEDULES_ALL, while CN_SCENARIOS_ALL anchors the pricing scenario context. DBMS_LOB supplies the LobLocator/Substr primitives used by the conversion routines, DBMS_RANDOM underpins unique-name generation, and DUAL is used for scalar lookups.

Usage Notes

CN_PLANCOPY_UTIL_PVT is a private (PVT) package and is not intended for direct customer invocation. It is called principally from the public plan copy API layer and from concurrent programs that perform pricing plan export and import, and ETRM metadata records five other packages referencing it. Because procedure signatures carry FND_API style defaults, callers typically pass FND_API.G_TRUE for initialization and commit. Customizations should never modify this package in place; extensions should be built on the public package layer, and any patch or upgrade applied to the 12.1.1 or 12.2.2 file system should be treated as authoritative, since the conversion and expression-update logic is tightly bound to the shape of the LOB payload written by the matching release.