Search Results resequence_dimension_levels

Overview

APPS.BIS_PMF_MIGRATION_PUB is a public PL/SQL package in the Oracle EBS Business Intelligence/Enterprise Performance Foundation (BIS) schema family. Its declared purpose, per the source header, is the Migration of PMF data — the Process Manufacturing/Performance Measurement Framework data set that underlies the BIS indicators, target levels, and target values model. The package header is dated 2002 and carries the RCS tag BISPMIGS.pls 115.7, indicating it is a legacy fusion-era component that has been carried forward into EBS 12.1.1 and 12.2.2 for backward compatibility with existing BIS installations.

The package operates with AUTHID CURRENT_USER, meaning its SQL executes under the privileges of the calling session rather than the package owner. This is significant in EBS because it allows the package to resolve the APPS synonyms for the BIS tables using the caller's schema context, while still enforcing that only appropriately privileged users (typically APPS or a custom user granted access to the BIS synonyms) can migrate data.

The package spec defines two PL/SQL record types that model the migration payload: target_level_rec, which carries a measure ID plus the target level ID and the org, time, and dimension 1–5 level IDs drawn from BIS_TARGET_LEVELS; and target_rec, which carries the corresponding measure, target level, target IDs and the org, time, and dimension 1–5 level values drawn from BIS_TARGET_VALUES. Together these record types describe the complete grain of a migrated performance measure target.

Key Procedures and Functions

The ETRM metadata documents four callable units in this package:

• MIGRATE_PERFORMANCE_MEASURES — The primary entry point, which performs the bulk movement of PMF performance-measure definitions into the BIS indicator/target model. It is the procedure a caller invokes to drive a migration run.
• RESEQUENCE_DIMENSION_LEVELS — Reassigns or renumbers dimension level identifiers so that dimension slots remain contiguous and correctly ordered after migration. This is necessary because PMF source data may use sparse or non-sequential dimension numbering that BIS_TARGET_LEVELS cannot represent directly.
• RESEQUENCE_TARGET_LEVEL_VALUES — Performs the analogous resequencing for the level values stored in BIS_TARGET_VALUES, keeping value rows aligned with the level rows processed by RESEQUENCE_DIMENSION_LEVELS.
• GET_DIMENSION_ID — A lookup function that resolves and returns a dimension identifier, typically used to map a PMF dimension reference to the corresponding BIS dimension ID in BIS_INDICATOR_DIMENSIONS or BIS_LEVELS. This is the unit most commonly referenced by custom migration code that needs to translate dimension keys before calling the migration procedures.

Parameter lists are not documented in the available metadata and should be confirmed against the deployed package specification in the target environment.

Tables Accessed

• BIS_INDICATORS — source/target of measure IDs; supplies INDICATOR_ID used as measure_id in both record types.
• BIS_TARGET_LEVELS — target table for org, time, and dimension 1–5 level IDs; the object resequenced by RESEQUENCE_DIMENSION_LEVELS.
• BIS_TARGET_VALUES — target table for the corresponding level values; the object resequenced by RESEQUENCE_TARGET_LEVEL_VALUES.
• BIS_INDICATOR_DIMENSIONS — dimension membership mapping consulted when resolving dimension IDs via GET_DIMENSION_ID.
• BIS_LEVELS — level definitions used to validate or look up level IDs during resequencing.
• PLITBLM — the standard EBS PL/SQL index-by table used for bulk operations, indicating the package uses FORALL/BULK COLLECT patterns for performance.

Usage Notes

BIS_PMF_MIGRATION_PUB is not referenced by any other documented package, so it is a leaf-level API intended for direct invocation rather than composition. In practice it is invoked from a one-time or incremental migration script, a SQL*Plus session run as APPS, or a custom concurrent program registered against MIGRATE_PERFORMANCE_MEASURES. Because the procedures manipulate BIS_TARGET_LEVELS and BIS_TARGET_VALUES in place, migration should be executed during a maintenance window after a backup, and resequencing procedures should be run after the main migration to preserve referential alignment between levels and values. Custom code requiring dimension ID resolution should call GET_DIMENSION_ID rather than querying the BIS tables directly, so that any future resequencing logic is honored.