Search Results get_sc_key

Overview

MSD_COMMON_UTILITIES is a shared infrastructure package in the Oracle EBS Advanced Planning / Demand Planning (MSD) schema. It is declared with AUTHID CURRENT_USER and is owned by APPS. Its primary role is to centralize the constants and helper routines required to move demand-planning data between fact tables, staging tables, and the dimensional structures that support multi-dimensional analysis. The package exposes a large set of VARCHAR2 constants bound to the physical fact and staging table names used across the MSD schema, including MSD_SHIPMENT_DATA, MSD_BOOKING_DATA, MSD_SALES_FORECAST, MSD_MFG_FORECAST, MSD_SALES_OPPORTUNITY_DATA, MSD_CURRENCY_CONVERSIONS, MSD_UOM_CONVERSIONS, MSD_LEVEL_VALUES, MSD_LEVEL_ASSOCIATIONS, MSD_ITEM_LIST_PRICE, MSD_TIME, MSD_PRICE_LIST, MSD_DP_SCENARIO_ENTRIES, and MSD_LOCAL_ID_SETUP, together with their parallel staging counterparts prefixed MSD_ST_. By publishing these names as package-level variables, dependent code avoids hard-coded literals and adapts consistently when table names change. The package is referenced by 64 other packages, confirming its role as a foundational utility layer rather than a business-facing API.

Key Procedures and Functions

The package documents 44 procedures and functions. Several resolve surrogate keys for dimensional members: GET_ITEM_KEY, GET_ORG_KEY, GET_LOC_KEY, GET_CUS_KEY, GET_SALESREP_KEY, GET_SC_KEY, and GET_DCS_KEY return keys for items, organizations, locations, customers, sales representatives, scenarios, and demand-consensus structures respectively. The level-centric helpers include GET_LEVEL_PK, GET_LEVEL_VALUE_PK, GET_LEVEL_NAME, GET_SR_LEVEL_PK, GET_PARENT_LEVEL_PK, and GET_CHILD_LEVEL_PK, which support traversal and identification of hierarchy levels and their parent/child relationships. GET_LEVEL_VALUE retrieves a level value for a given context. GET_DIMENSION_CODE — the object the user searched for — resolves the internal dimension code used to identify a dimension member; it is typically called before building level-value or fact-table inserts so that the correct dimensional identifier accompanies the staging record. Environment helpers include GET_INST_INFO, which returns instance information, and GET_DB_LINK, which supplies the database link used for cross-instance access. Conversion routines MSD_UOM_CONVERSION and MSD_UOM_CONVERT translate quantities between units of measure. IS_GLOBAL_SCENARIO reports whether a scenario is global in scope. As documented metadata does not disclose parameter lists, callers should validate signatures against the packaged specification in the target instance.

Tables Accessed

Documented table references, reached via APPS synonyms, include the supply-chain dictionary tables MSC_SYSTEM_ITEMS, MSC_TRADING_PARTNERS, MSC_ITEM_SUPPLIERS, MSC_ASL_AUTH_DETAILS, MSC_PLANS, MSC_SAFETY_STOCKS, MSC_UOM_CONVERSIONS, MSC_UOM_CLASS_CONVERSIONS, MSC_TP_ID_LID, and MSC_APPS_INSTANCES. From the MSD schema the package reads MSD_DEMAND_PLANS, MSD_DP_SCENARIO_OUTPUT_LEVELS, MSD_LEVELS, MSD_LEVEL_ASSOCIATIONS, and MSD_LEVEL_VALUES. These accesses support key resolution (GET_ITEM_KEY, GET_ORG_KEY, GET_CUS_KEY, GET_LEVEL_PK), UOM conversion (MSD_UOM_CONVERT), instance and database-link discovery (GET_INST_INFO, GET_DB_LINK), and scenario scoping (IS_GLOBAL_SCENARIO).

Usage Notes

MSD_COMMON_UTILITIES is invoked internally by MSD demand-planning packages, staging loaders, and dimensional build programs rather than by end-user forms directly. Typical calls occur during fact and staging table loads, when a dimension code or surrogate key must be resolved and attached to a record, during UOM normalization of forecast quantities, and during scenario validation. Custom extensions that integrate with MSD fact tables should call these helpers instead of duplicating key-lookup logic. Because the package is AUTHID CURRENT_USER, privileges and synonym resolution follow the invoking schema, so deployment must ensure the APPS synonyms for the referenced MSC and MSD tables are visible to the calling context. The version string "120.5 2010/09/09" indicates the package is common to the 12.1.1 and 12.2.2 code lines and has remained stable across those releases.