Search Results clean_up

Overview

GL_FLATTEN_SEG_VAL_HIERARCHIES is an Oracle General Ledger server-side PL/SQL package owned by the APPS schema. Its business purpose is to build and maintain the flattened representations of the Oracle Flexfield value hierarchy used by the Accounting Flexfield (key flexfield) segment values. Oracle EBS stores hierarchy relationships between parent and child flexfield values in normalized form, but performance-sensitive operations — most notably the FSG (Financial Statement Generator) report engine and hierarchy-aware value queries — require a pre-flattened, denormalized view of every ancestor/descendant relationship. This package is the entry point that transforms the source hierarchy data into that flattened form.

Specifically, the package maintains two staging/maintenance tables: GL_SEG_VAL_NORM_HIERARCHY and GL_SEG_VAL_HIERARCHIES. The former holds the normalized hierarchy, and the latter holds the flattened (expanded) hierarchy in which each qualifying relationship between a parent value and all of its descendants is materialized as an individual row. The package header carries the AUTHID CURRENT_USER directive, so it executes with the privileges of the invoking user rather than as definer, and it references the standard $Header signature associated with the 12.1.1/12.2.2 code line (120.4.12010000.1).

Key Procedures and Functions

• FLATTEN_SEG_VAL_HIER — The documented entry point for the package. Its stated purpose is maintaining the GL_SEG_VAL_NORM_HIERARCHY and GL_SEG_VAL_HIERARCHIES tables. It accepts an OUT NOCOPY BOOLEAN flag (IS_SEG_HIER_CHANGED) that signals to the caller whether the segment hierarchy has changed, and returns a BOOLEAN status.
• FIX_NORM_TABLE — Maintains the GL_SEG_VAL_NORM_HIERARCHY table. It returns a BOOLEAN and exposes an OUT NOCOPY BOOLEAN (IS_NORM_TABLE_CHANGED) reporting whether the normalized table was modified.
• FIX_FLATTENED_TABLE — Maintains the GL_SEG_VAL_HIERARCHIES table, returning BOOLEAN with an OUT NOCOPY BOOLEAN (IS_FLATTENED_TAB_CHANGED) that reports whether the flattened table was modified.
• CLEAN_UP — Brings all records in both GL_SEG_VAL_NORM_HIERARCHY and GL_SEG_VAL_HIERARCHIES to their final, consistent state. This is the routine normally associated with removing stale, orphaned, or superseded hierarchy rows left behind after incremental updates, so that both tables remain in agreement.

The three functional routines correspond to the three phases of hierarchy processing: normalize, flatten, and reconcile/clean. The parameter lists are not enumerated here, as the metadata documents only their names and purposes.

Tables Accessed

• FND_FLEX_VALUES — The flexfield values definition table, the ultimate source of segment values participating in the hierarchy.
• FND_FLEX_VALUE_NORM_HIERARCHY — The Oracle-defined parent/child relationship table from which the hierarchy is derived.
• FND_ID_FLEX_SEGMENTS — Identifies the key flexfield segment structure being processed.
• GL_SEG_VAL_NORM_HIERARCHY — The package's working normalized hierarchy table; read and written by FIX_NORM_TABLE.
• GL_SEG_VAL_HIERARCHIES — The package's flattened hierarchy table; read and written by FIX_FLATTENED_TABLE.
• DUAL — Used for scalar evaluation and control logic within the PL/SQL bodies.

Usage Notes

GL_FLATTEN_SEG_VAL_HIERARCHIES is not typically called interactively from a form field. It is invoked internally by Oracle General Ledger hierarchy maintenance processing — for example, when a value hierarchy is modified and needs to be re-flattened — and is referenced by one other documented package. In custom or extension code, the standard pattern is to call FLATTEN_SEG_VAL_HIER as the single entry point and inspect the IS_SEG_HIER_CHANGED OUT parameter to determine whether downstream processing (such as FSG compilation) needs to be re-triggered. Direct calls to FIX_NORM_TABLE, FIX_FLATTENED_TABLE, or CLEAN_UP are appropriate only during controlled reconciliation or troubleshooting, because they mutate the two maintenance tables directly. Because the package is defined AUTHID CURRENT_USER, the invoking session must hold sufficient privileges on the underlying FND and GL tables; deployment should therefore be left to the standard Oracle patch or concurrent-process invocation path rather than ad hoc execution.