Search Results execute_remote_query

Overview

The APPS.MSD_DEM_SR_UTIL package is a utility package within the Oracle EBS Mobile Supply Chain / Demand Planning integration layer. Its name and contents indicate that it belongs to the MSD (Mobile Supply Chain/Demand) product family, and the source header references version 120.2.12000000.3 with a 2007 revision tied to Bug 3509147. The package serves as a centralized helper library that supplies source-instance configuration values, master organization identifiers, unit-of-measure conversion logic, and service-related attribute lookups to other packages in the MSD_DEM family. It is classified as a UTIL package, meaning it exposes no public business-entity API of its own; instead, it provides reusable primitives that higher-level collection and synchronization processes call.

A significant portion of the package retrieves values from source profiles (such as MSD_DEM_CATEGORY_SET_NAME, MSD_DEM_CONVERSION_TYPE, and MSD_DEM_CUSTOMER_ATTRIBUTE) so that downstream programs operate with consistent, centrally maintained configuration rather than hard-coded values. This makes the package the single point of reference for environment-specific settings across the Demand/collection subsystem.

Key Procedures and Functions

The documented interface contains roughly 28 procedures and functions. Core entries include:

• EXECUTE_REMOTE_QUERY — executes a SQL query string passed in from a remote database, supporting distributed data retrieval in multi-instance EBS deployments.
• SET_CUSTOMER_ATTRIBUTE — associated with Bug 3509147; sets the MSD_CUSTOMER_ATTRIBUTE profile to NONE when a collection runs for the first time.
• GET_CATEGORY_SET_ID — returns the value of source profile MSD_DEM_CATEGORY_SET_NAME as an internal category set identifier.
• GET_CONVERSION_TYPE — returns the source profile MSD_DEM_CONVERSION_TYPE used for UOM conversion.
• GET_MASTER_ORGANIZATION — returns the ID of the master organization in the source instance; this is the function the user searched for.
• GET_CUSTOMER_ATTRIBUTE — returns the source profile MSD_DEM_CUSTOMER_ATTRIBUTE value.
• GET_NULL_PK / GET_NULL_CODE / GET_NULL_DESC / GET_DEFAULT_CODE — provide standardized null or default return values, common defensive-utility helpers.
• UOM_CONV — performs unit-of-measure conversion for a given UOM code and item.
• IS_ITEM_OPTIONAL_FOR_LVL, IS_PRODUCT_FAMILY_FORECASTABLE, CONFIG_ITEM_EXISTS — evaluate item-level business rules for optional components, forecastability of product families, and configuration item existence by header, organization, and ATO line.
• CONVERT_GLOBAL_AMT — converts monetary amounts to a global (functional) currency equivalent.
• GET_ZONE_ATTR, GET_SR_ZONE_DESC, GET_SR_ZONE_PK — resolve zone attributes, descriptions, and primary keys for service request zoning.
• IS_TXN_DEPOT_REPAIR — determines whether a transaction represents a depot repair scenario.
• GET_SERVICE_REQ_ORG_ID — returns the organization identifier associated with a service request context.

Tables Accessed

The package references the following tables through APPS synonyms: MTL_SYSTEM_ITEMS and MTL_PARAMETERS (item master and inventory organization parameters, used by the master-organization and item-rule functions); MTL_UOM_CONVERSIONS (UOM_CONV); MSD_DEM_APP_INSTANCE_ORGS and MSD_DEM_SETUP_PARAMETERS (source-instance configuration and setup); FND_PRODUCT_GROUPS and FND_DESCR_FLEX_COLUMN_USAGES (product group and descriptive flexfield column metadata); OE_ORDER_LINES_ALL (order-line context for configuration and conversion logic); CSD_REPAIR_JOB_XREF and WSH_REGION_LOCATIONS, WSH_ZONE_REGIONS (depot repair linkage and zone/region resolution for service requests).

Usage Notes

MSD_DEM_SR_UTIL is invoked internally by at least seven other packages in the MSD_DEM collection and synchronization stack rather than by end users directly. Its functions are typically called from concurrent collection programs and integration routines that push or pull data between source and destination EBS instances, and from service-request processing logic that needs zone and depot-repair resolution. GET_MASTER_ORGANIZATION is commonly referenced in metadata scripts and custom code that must resolve the source instance's master organization when mapping inventory items across instances. Because the package is a utility and not a public API, direct customization is discouraged; callers should rely on the documented function signatures, and profile-driven configuration should be maintained through the MSD_DEM setup rather than code changes.