Search Results dest_table




Overview
Explore in AI Chat

Overview

MSD_DEM_UPDATE_LEVEL_CODES is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite. The MSD_DEM prefix identifies it as part of the Demand Planning (formerly Demantra) integration layer within the Oracle Advanced Planning product family, where "DEM" refers to Demand Management. The package provides utility routines that standardize and repair the level or member code values stored against demand history and sales data. In practice, source sales and shipment records arrive with descriptive level identifiers (for example, a site or geography name), while the analytical demand engine expects integer or otherwise normalized level codes. This package bridges that gap by rewriting destination columns in place so that downstream demand planning, forecasting, and aggregation processes operate on consistent identifiers.

The package is classified in the ETRM repository as API classification OTHER, indicating it is an internal implementation utility rather than a published, customer-facing API. It is referenced by two other packages, which suggests the level-code correction logic is shared across the Demand Planning integration code path rather than being invoked directly by end users. The header comment shows the file at version 120.3.12020000.3, dated October 1, 2012, consistent with the 12.1.1 and 12.2.2 code lines.

Key Procedures and Functions

  • UPDATE_CODE — The primary working procedure. It accepts an instance identifier, a level name, a destination table name, a destination column name, and a source column name. When the level is SITE, it retrieves a template SQL statement from the query utility repository, substitutes the DEST_TABLE, DEST_COLUMN, and SRC_COLUMN tokens with the caller-supplied object and column names, and then executes the resulting statement dynamically, binding the instance identifier as a bind variable. The procedure commits on success and returns a zero return code. On failure it captures the SQL error message into the error buffer, logs it, and returns a non-zero return code.
  • CONVERT_SITE_CODE — Converts the site level code representation between descriptive and integer formats based on a conversion type parameter. According to the embedded comment, a conversion type of 1 changes codes from the new format back to the old descriptive format, while a conversion type of 2 changes from the old descriptive format to the new format. This supports upgrade and migration scenarios where legacy sales data must be reconciled with the current coding convention.
  • UPGRADE_GEO_LEVEL_CODES — Performs the corresponding code normalization for geography-based levels, ensuring that geography member codes are aligned with the current representation required by the demand planning engine during an upgrade.

Tables Accessed

The documented metadata lists only DBMS_OUTPUT as a referenced object, accessed through an APPS synonym. The package uses DBMS_OUTPUT.PUT_LINE for diagnostic trace output and relies on the MSD_DEM_COMMON_UTILITIES and MSD_DEM_QUERY_UTILITIES packages for message logging and stored query retrieval. The actual sales and demand history tables are not referenced statically; they are passed in as parameters and addressed dynamically at runtime, which is why the destination table and column names are substituted into the query text rather than hard-coded. DBMS_OUTPUT is used for debug visibility only and does not affect the data changes performed.

Usage Notes

This package is invoked as part of the Demand Planning data preparation and upgrade flow, typically from concurrent programs or from the higher-level integration packages that call it. It is not intended for direct execution by end users. The dynamic SQL approach requires that the caller supply valid object and column names, since the procedure cannot validate them at compile time. Because UPDATE_CODE issues an explicit COMMIT, callers should be aware that any pending transaction in the session will also be committed. Error conditions are reported through the standard concurrent program error buffer and return code convention, making the procedure suitable for use inside concurrent program PL/SQL blocks where errbuf and retcode are the expected output parameters.