Search Results compile_hierarchy_all

Overview

The APPS.FND_FLEX_HIERARCHY_COMPILER package is a server-side PL/SQL utility within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments whose principal business function is the compilation of flex value hierarchies. Flexfields form the backbone of EBS data capture, and value sets frequently define parent-child hierarchies that must be materialized, validated, and cached so that validation of key flexfield and descriptive flexfield values can be performed efficiently at transaction time. The package derives its name and purpose directly from its central entry point, compile_hierarchy, which rebuilds the compiled representation of the hierarchy associated with a given flex value set based on the security and hierarchy rules configured by the enterprise.

The package header is owned by APPS and is classified as OTHER under the documented API classification scheme. Its $Header attribute references source file AFFFCHYS.pls, version 120.2.12010000.1, dated 25 July 2008, which indicates that the interface has been stable across the 11i and 12.x release families. Because hierarchies underpin both cross-validation and security rule enforcement, compiling them correctly is a precondition for accurate flexfield validation throughout the application.

Key Procedures and Functions

ETRM documentation identifies five declared program units, four of which are functionally distinct. No parameter lists are reproduced here beyond those documented.

• COMPILE_HIERARCHY — the primary entry point, appearing in two overloaded forms. It compiles the hierarchy for a single flex value set. The documentation states that the p_flex_value_set argument accepts either a numeric value set identifier or an alphanumeric value set name; the package interprets a numeric input as FLEX_VALUE_SET_ID and any other input as FLEX_VALUE_SET_NAME. An optional debug flag of Y or N controls diagnostic output. The procedure returns a result status of SUCCESS or FAILURE together with a descriptive message.
• COMPILE_HIERARCHY_ALL — a driver procedure, documented alongside the singular variant, that compiles hierarchies across multiple value sets in a single unit of work, enabling bulk recompilation after rule or value changes.
• REQUEST_LOCK — acquires a named lock through a procedure that returns a lock handle for the caller. This serializes compilation activity so that concurrent requests cannot corrupt hierarchy structures.
• RELEASE_LOCK — releases a previously acquired lock using the lock name and handle returned by REQUEST_LOCK, completing the concurrency control contract.

The availability of explicit locking primitives confirms the package is designed for coordinated, restartable processing rather than ad hoc single-threaded execution.

Tables Accessed

The compiled hierarchy is derived from and written back to several core flexfield metadata tables, all referenced through APPS synonyms.

• FND_FLEX_VALUE_SETS — the master definition of the value set being compiled, including its identifier and name as resolved by the overload.
• FND_FLEX_VALUE_HIERARCHIES, FND_FLEX_VALUE_HIER_ALL, and FND_FLEX_VALUE_NORM_HIERARCHY — the hierarchy definition, all-levels flattening, and normalized hierarchy structures that hold parent-child relationships.
• FND_FLEX_VALUE_RULES and FND_FLEX_VALUE_RULE_LINES — the rule definitions whose compiled results the compiler produces.
• FND_FLEX_SEC_RULE_ALLOWED_VALS — the mapping of allowed values for security rules, which the compiled hierarchy must honor.

The package also references the Oracle-supplied utilities DBMS_UTILITY, for general PL/SQL infrastructure, and WF_PARAMETER_LIST_T, the Workflow parameter list type used to package results and messages.

Usage Notes

This package is ordinarily not called directly by end users. It is invoked by the Flexfields Hierarchies and Security forms, by concurrent programs that rebuild hierarchy structures during configuration changes or patch application, and by custom PL/SQL that needs to force recompilation after inserting hierarchy or rule data. Because it is an OTHER-classified APPS package, Oracle does not guarantee interface stability; custom code should call it defensively and inspect the returned result and message rather than assuming success. Long-running compilations should be preceded by REQUEST_LOCK and followed by RELEASE_LOCK to prevent contention. Given the package is referenced by one other package, implementers should review dependent objects before modification, and should typically consolidate all hierarchy recompilation into COMPILE_HIERARCHY_ALL where several value sets are affected.