Search Results bis_levels




Overview
Explore in AI Chat

Overview

BIS_LEVELS is a reference table in the BIS (Applications BIS) product schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It lists all the terms that can be used to define a level of partitioning along a particular dimension. For example, "month," "quarter," and "year" are all levels at which records can be grouped within the time dimension. The table therefore acts as a metadata registry that drives how dimensional data is grouped, summarized, and drilled into across applications that rely on the BIS dimensional framework.

From a Data Vault modeling perspective, the mined FK structure classifies BIS_LEVELS as a standalone table. This is a heuristic suggestion: because its only outgoing foreign key references a parent level table (MSD_LEVELS) without being the target of other dependent tables within the same FK graph, it behaves less like a true hub or link and more like a self-contained reference or satellite-style lookup. Modelers should treat this classification as a starting point rather than a definitive role.

Key Information Stored

The table is documented with 22 columns. The most functionally significant are listed below.

The distinction between the surrogate key (LEVEL_ID) and the two business-key candidates (LEVEL_ID via BIS_LEVELS_U1 and SHORT_NAME via BIS_LEVELS_U2) is important for ETL design: SHORT_NAME typically serves human-facing lookups, while LEVEL_ID anchors referential integrity.

Common Use Cases and Queries

Typical scenarios include listing available levels for a dimension, resolving drill-down targets, and joining level metadata to transactional fact data.

  • Enumerate active levels for a dimension: SELECT LEVEL_ID, SHORT_NAME FROM BIS.BIS_LEVELS WHERE DIMENSION_ID = :dim_id AND ENABLED = 'Y';
  • Resolve a master level definition: join BIS_LEVELS to MSD_LEVELS on LEVEL_ID to obtain master attributes.
  • Identify drill-to-form behavior: SELECT SHORT_NAME, DRILL_TO_FORM_FUNCTION FROM BIS.BIS_LEVELS WHERE DRILL_TO_FORM_FUNCTION IS NOT NULL;
  • Reporting on level configuration: query DEFAULT_SEARCH, LONG_LOV, and HIDE_IN_DESIGN to audit list-of-values and design-time exposure.

Related Objects

  • MSD_LEVELS — Referenced by the foreign key BIS_LEVELS.LEVEL_ID → MSD_LEVELS.LEVEL_ID; the primary parent table.
  • BIS_LEVELS_U1 and BIS_LEVELS_U2 — Unique indexes enforcing the surrogate and business keys.
  • Views named in LEVEL_VALUES_VIEW_NAME and VIEW_OBJECT_NAME — Dynamically referenced value sources.
  • APIs named in DEFAULT_VALUES_API — Programmatic interfaces for retrieving level values.