Search Results get_periodicity_for_table

Overview

APPS.BSC_DBGEN_BSC_READER is a PL/SQL package in the Oracle E-Business Suite database layer that supports the Balanced Scorecard (BSC) Designer component of Oracle Enterprise Performance Foundation / ETRM (Enterprise Territory and Resource Management lineage). The package provides read-only access to the metadata repository that describes the analytical structures deployed for a Balanced Scorecard implementation — including facts, measures, dimensions, levels, periodicities, calendars, and calculation definitions.

As the name implies, BSC_DBGEN_BSC_READER is a runtime query interface over the BSC-DB generation metadata. It allows the Designer framework to interrogate the shape of a deployed data model — for example, to determine which measures belong to a fact, which dimensions map to a level, or which periodicities apply to a given table — so that downstream generators, validators, or reporting components can produce consistent SQL and business object definitions. The package status is VALID and it is owned by the APPS schema, making its procedures accessible to other EBS code that resolves objects through APPS synonyms.

Key Procedures and Functions

The ETRM documentation records 38 procedures and functions in this package, with the following notable entries:

• IS_PARENT_1N / IS_PARENT_MN — Predicate functions that test whether a given level or entity is a parent in a one-to-many or many-to-many hierarchical relationship.
• GET_FACTS_TO_PROCESS — Returns the set of fact definitions eligible for processing by the Designer engine.
• GET_MEASURES_FOR_FACT — Retrieves the measures associated with a specified fact.
• GET_PERIODICITIES_FOR_FACT — Lists the periodicities (time granularities) valid for a fact.
• GET_DIMENSIONS_FOR_FACT — Provides the dimensions linked to a fact.
• GET_CHILDREN_FOR_LEVEL / GET_PARENTS_FOR_LEVEL — Navigate level hierarchies, returning child or parent levels for a given level.
• GET_LEVEL_INFO — Supplies descriptive metadata about a level.
• GET_DIM_SETS_FOR_FACT — Returns the dimension sets associated with a fact.
• GET_S_VIEWS — Retrieves view metadata used in the generated star or snowflake schemas.
• GET_LEVELS_FOR_TABLE — Identifies levels materialized in a specified table.
• GET_B_TABLE_MEASURES_FOR_FACT — Returns measures stored in base (B) tables for a fact.
• GET_PERIODICITY_FOR_TABLE — Determines the periodicity associated with a table.
• GET_DB_CALENDAR_COLUMN — Resolves the calendar column to be used for a database object.
• GET_ZERO_CODE_LEVELS — Identifies levels configured with a zero code designation.
• GET_BASE_TABLES_FOR_DIM_SET — Returns the base tables contributing to a dimension set.
• GET_FACTS_FOR_LEVELS — Lists facts reachable through a set of levels.
• GET_FILTER_FOR_DIM_LEVEL — Retrieves filter expressions applied to a dimension level.
• GET_CURRENT_PERIOD_FOR_FACT — Returns the current period applicable to a fact.

Because parameter lists are not documented in the ETRM excerpt, callers should inspect the package specification in the database for exact signatures.

Tables Accessed

The package reads from a series of BSC metadata tables resolved through APPS synonyms, including:

• BSC_KPI_DATA_TABLES, BSC_DB_TABLES, BSC_DB_TABLES_COLS, BSC_DB_TABLES_RELS — physical table and column metadata for the generated data model.
• BSC_KPI_DIM_LEVELS_B, BSC_SYS_DIM_LEVELS_B, BSC_SYS_DIM_LEVEL_RELS — dimension and level definitions and their hierarchical relationships.
• BSC_KPI_PERIODICITIES, BSC_SYS_CALENDARS_B — periodicity and calendar metadata.
• BSC_SYS_MEASURES, BSC_DB_MEASURE_COLS_TL — measure definitions and translated column labels.
• BSC_DB_CALCULATIONS, BSC_KPI_PROPERTIES, BSC_INTERNAL_COLUMN_S — calculation and internal column metadata.
• DBMS_SQL — the Oracle-supplied dynamic SQL package used to build and execute ad hoc queries at runtime.

All operations are read-oriented; the package does not modify core BSC metadata.

Usage Notes

BSC_DBGEN_BSC_READER is referenced by the companion package BSC_DBGEN_METADATA_READER and by itself (internal recursion), and it depends on BSC_DBGEN_STD_METADATA plus the DBMS_SQL package. It is typically invoked from the Balanced Scorecard Designer setup and generation flows, from concurrent programs that validate or build the analytical data model, and from custom PL/SQL that needs to interrogate deployed BSC metadata. Because it uses DBMS_SQL for dynamic generation, session privileges must permit execution of dynamic SQL against the referenced BSC tables. Developers extending BSC functionality should call this package rather than querying the underlying metadata tables directly, ensuring consistency with Designer semantics.