Search Results upgrade_geo_level_codes




Overview
Explore in AI Chat

Overview

MSD_DEM_UPDATE_LEVEL_CODES is an APPS-owned PL/SQL package in Oracle E-Business Suite that belongs to the Oracle Demand Planning / Advanced Supply Chain Planning (ASCP) family of modules (the MSD and MRP schema prefixes). The package's name and its documented procedures indicate that its business function is the maintenance and migration of hierarchy level code identifiers used by the Demand Planning and supply chain planning data model. Planning applications organize dimension members — such as sites, organizations, customers, products, and geographic regions — into leveled hierarchies. Each hierarchy level is represented by a code value stored in planning tables, and those codes must be kept consistent when the underlying source system changes, when data is refreshed from source instances, or when the application is upgraded between releases.

The package is classified in the ETRM repository as an API of type OTHER. It exposes a small, focused set of entry points rather than being a general-purpose utility library, and it is referenced by two other packages in the ETRM metadata, indicating that it sits in the middle of a dependency chain rather than at the top level of the call stack.

Key Procedures and Functions

The ETRM documentation records three procedures or functions for this package:

  • UPDATE_CODE — The only procedure for which the ETRM excerpt includes a full signature. It accepts an instance identifier, a level identifier, a destination table name, a destination column name, and a source column name, together with the standard concurrent-program errbuf and retcode OUT parameters. Its purpose is to copy or refresh the level code value from a source column into a designated destination column of a designated planning table, scoped by the given instance and hierarchy level. Because it takes table and column names as parameters, it is a metadata-driven routine used to roll level codes forward across multiple planning tables without hard-coding each table.
  • CONVERT_SITE_CODE — Converts site-level code values between representations, typically as part of reconciling site identifiers across instances or between the source transaction system and the planning schema.
  • UPGRADE_GEO_LEVEL_CODES — Upgrades geographic level codes during an application upgrade, ensuring that geography hierarchy definitions conform to the code structure expected by the target release.

The published header notes the file revision as msddemupdlvs.pls 120.0.12000000.2, indicating that the package body is delivered as part of the shipped APPS code tree.

Tables Accessed

The ETRM metadata lists only DBMS_OUTPUT among the objects referenced through APPS synonyms. This is significant: rather than owning a fixed set of planning tables, the package manipulates tables whose names are supplied at runtime through the p_dest_table_name and p_dest_column_name parameters of UPDATE_CODE. The package therefore performs dynamic SQL against planning hierarchy and level tables in the MSD/MRP schema. DBMS_OUTPUT is used purely for diagnostic trace messages emitted during execution, which is consistent with a maintenance routine intended to be run under supervision.

Usage Notes

Because UPDATE_CODE follows the standard errbuf/retcode concurrent program convention, the package is most commonly invoked from a concurrent program or from a request set executed by a system administrator, rather than interactively from a form. Typical invocation scenarios include post-upgrade migration steps, hierarchy consistency fix-ups after a data refresh or collection run, and corrective scripts supplied by Oracle Support when level codes in the planning schema become misaligned with the source instance.

The presence of runtime-supplied table and column names means callers must supply valid object names; incorrect values will surface as dynamic SQL errors rather than compile-time failures. Because it is an internal implementation package and not a published public API, direct calls from customer-written custom code are not advisable. Customers requiring the behavior should use the supported concurrent program or patch that drives the package, and should apply such changes during a maintenance window, since level code updates affect hierarchy reporting and plan output.