Search Results get_dim_parent_child

Overview

APPS.BSC_AW_MD_API is a PL/SQL package in the Oracle E-Business Suite Balanced Scorecard (BSC) module that manages the metadata definitions and lifecycle of OLAP objects — dimensions, levels, calendars, and KPIs — for the Oracle OLAP Analytic Workspace (AW). In Oracle EBS 12.1.1 and 12.2.2, the Balanced Scorecard application relies on analytic workspaces as its physical data storage layer; this package exposes a programmatic API through which callers create, query, mark for recreation, and drop the OLAP representations of scorecard metadata.

The package is classified as an API and is owned by APPS. It maintains session-level cached collections of OLAP object and object-relation descriptors, populated from its internal work tables, to avoid repeated catalog lookups during large metadata build operations. Runtime diagnostics are controlled through a debug flag and a statement buffer used to externalize generated DDL.

Key Procedures and Functions

• IS_DIM_PRESENT — A function that tests whether a given dimension already exists in the OLAP metadata catalog, returning a Boolean result.
• GET_KPI_FOR_DIM — Returns, via an output collection, the list of KPIs associated with a supplied dimension name.
• MARK_KPI_RECREATE — Flags a KPI so that its OLAP definition is rebuilt on the next refresh cycle rather than assuming the stored version is current.
• GET_DIM_OLAP_OBJECTS — Retrieves the OLAP objects belonging to a dimension, optionally filtered by object type, into an output collection.
• DROP_DIM — Removes the OLAP definition of a named dimension.
• GET_CCDIM_FOR_LEVELS — Given a dimension record, returns the list of conformed dimensions applicable to the dimension's levels.
• CREATE_DIM_OBJECTS — The central build procedure: accepts a dimension record (BSC_AW_ADAPTER_DIM.dimension_r) and creates the corresponding OLAP objects — dimensions, hierarchies, and levels — in the analytic workspace.
• GET_LEVEL_POSITION — Returns the ordinal position of a dimension level, used to preserve level ordering.
• DROP_KPI — Removes the OLAP definition of a KPI.
• GET_KPI_OLAP_OBJECTS — Returns the OLAP objects that implement a given KPI.
• DELETE_CALENDAR / CREATE_CALENDAR — Remove and create OLAP calendar definitions used for time-based scorecard comparisons.
• Additional documented members include GET_DIM_FOR_LEVEL, GET_DIM_PARENT_CHILD, GET_BSC_OLAP_OBJECT, GET_BSC_OLAP_OBJECT_RELATION, GET_DIM_FOR_KPI, CREATE_KPI, GET_DIM_PROPERTIES, and GET_DIM_SET_CALENDAR, which together provide the object lookup, parent/child traversal, KPI creation, property retrieval, and calendar assignment operations required to assemble a complete OLAP metadata model.

Tables Accessed

• BSC_OLAP_OBJECT — Stores the persisted OLAP object definitions and is queried and maintained whenever dimensions, levels, KPIs, or calendars are created, read, or dropped.
• BSC_OLAP_OBJECT_RELATION — Captures parent/child and other structural relationships between OLAP objects; used by the relation cache and by the parent/child traversal routines.
• DBMS_SQL — The built-in package used for dynamic SQL generation of OLAP DDL and for the varchar2_table structures returned by several getter procedures.
• PLITBLM — An internal APPS synonym used in support of PL/SQL collection processing.

Usage Notes

BSC_AW_MD_API is invoked as a backend metadata engine rather than from a user-facing form. The standard call pattern is: confirm a dimension does not yet exist with IS_DIM_PRESENT, then call CREATE_DIM_OBJECTS with a populated dimension record, subsequently creating KPIs and calendars through CREATE_KPI and CREATE_CALENDAR. When scorecard metadata changes, MARK_KPI_RECREATE schedules dependent KPIs for rebuild, and DROP_DIM or DROP_KPI clears obsolete definitions.

The package is referenced by thirteen other packages, so changes to its signatures must be treated as interface changes. Because it executes OLAP DDL against the analytic workspace, calls should be issued with the APPS schema and appropriate OLAP privileges, and typically originate from concurrent programs, metadata refresh APIs, or the Balanced Scorecard setup flows rather than from ad hoc custom code. Developers enabling diagnostics should set the module-level debug flag and inspect the statement buffer, which retains the last generated statement and is useful when troubleshooting creation or drop failures.