Search Results manage_indexes




Overview
Explore in AI Chat

Overview

CN_SCA_UTL_PVT is a private utility package in the Oracle EBS Applications (APPS) schema, shipped as part of the Advanced Product Catalog / Supply Chain Application configuration rule engine components. The package name prefix "CN" identifies it as belonging to the Oracle Product Information Management / Configurator family of modules, and the "_PVT" suffix classifies it in the ETRM as an internal, non-public API. It is not intended for direct customer invocation; rather, it is called by other Rules Engine PL/SQL packages to supply shared helper logic.

The package was created by Rao Chenna on 23 June 2003 and carries a header version of 120.1 dated 15 September 2005. Its declared purpose, as stated in the source, is to hold "utilities" that are used by sibling packages within the Rules Engine. The specification is declared AUTHID CURRENT_USER, meaning that runtime privilege resolution occurs under the calling schema rather than under APPS, a design choice that supports the package's role as an internal helper.

Key Procedures and Functions

The ETRM documentation identifies two callable elements within the package specification:

  • GET_VALUSET_QUERY — A function accepting a value set identifier (l_valueset_id) and returning a VARCHAR2. Its role is to construct or retrieve the SQL query text associated with a given Oracle value set, allowing the Rules Engine to translate stored value set IDs into executable lookup queries at runtime.
  • MANAGE_INDEXES — A procedure taking a transaction source, an organization ID, and an output return status parameter. As the name implies, this routine performs index maintenance operations. Because the object was located via the search term "manage_indexes," this procedure is the primary entry point of interest. It manages the creation, alteration, or removal of indexes on rule engine tables based on the transaction source and organization context supplied by the caller.

No additional procedures or functions are documented beyond these two; the metadata lists a total of two documented callable elements.

Tables Accessed

The package reads and writes the following tables through APPS synonyms:

  • CN_SCA_CONDITIONS — Stores condition definitions for configuration rules; referenced to determine which objects require index support.
  • CN_SCA_RULE_ATTRIBUTES_ALL_B — Holds the base-table definitions for rule attributes across all organizations; central to determining indexing targets.
  • FND_APPLICATION — Provides application context metadata, used to resolve the owning application of rule objects.
  • FND_ORACLE_USERID — Supplies Oracle schema/user information, likely to determine the correct schema context for DDL operations.
  • FND_PRODUCT_INSTALLATIONS — Confirms product installation status, ensuring index management only proceeds where the relevant product is installed.
  • ALL_INDEXES — The data dictionary view queried to determine which indexes already exist before creation or removal, avoiding duplicate or conflicting DDL.

Usage Notes

Because CN_SCA_UTL_PVT is classified as PVT, it is invoked internally rather than from forms, concurrent programs, or public APIs. It is referenced by one other package per the ETRM metadata, which acts as its caller within the Rules Engine. The MANAGE_INDEXES procedure is most likely triggered during rule deployment, attribute regeneration, or organizational setup, when the engine must ensure that supporting indexes exist on the configuration tables for optimal query performance. The GET_VALUSET_QUERY function supports value set resolution during rule evaluation.

Customizations should not call this package directly; Oracle does not guarantee its specification across releases. In both EBS 12.1.1 and 12.2.2 the same compiled specification is deployed in APPS, and any modification requires the standard AD utilities (adpatch / adop) and invalidation of dependent packages.