Search Results msc_ws_aps_data_services




Overview
Explore in AI Chat

Overview

MSC_WS_APS_DATA_SERVICES is an PL/SQL package owned by APPS that serves as the web-service data access layer for Oracle Advanced Planning and Scheduling (APS) within the Oracle E-Business Suite. Its principal business function is to move planning data between external or intermediate staging structures and the core Advanced Supply Chain Planning (ASCP) repository tables that live in the MSC schema. Specifically, the package brokers the upload and download of demand forecasts, safety stock levels, and planned supply, translating caller-supplied planning context — user, responsibility, security group, language, and scenario — into rows stored in the planning tables used by the MSC engine.

The package header documents a deliberate scheme of "output levels" that constrain how planning data is aggregated or disaggregated. Output levels fall into four categories: Item, Organization, Customer, and Demand Class. Item is mandatory and admits either Item or Product Family. Organization, Customer, and Demand Class are optional, and only one member of each category may be specified. The Customer category alone allows four distinct members: Customer Ship To Site, Customer, Customer Zone, and Zone. This design ensures uploaded or downloaded planning data lands at a legal planning level.

Key Procedures and Functions

The package exposes five documented procedures and functions, each mapped to a specific APS data movement task:

  • UPLOAD_FORECAST — Inserts or updates demand forecast rows into the denormalized scenario entries table (msd_dp_scn_entries_denorm), driven by caller-supplied scenario, output levels, and a forecast list. A PurgeAllFlag of Y causes all existing rows for the reserved owner 5555555 and the target scenario to be deleted before any insert or update.
  • DOWNLOAD_FORECAST — Retrieves forecast data from the planning repository for the requested scenario and output levels, supplying it back to the requesting APS or external consumer.
  • UPLOAD_SAFETY_STOCKS — Writes safety stock quantities into the planning safety stock structures for the designated items, organizations, and scenario context.
  • DOWNLOAD_SAFETY_STOCKS — Reads the currently stored safety stock values for use by downstream planning, reporting, or integration consumers.
  • UPLOAD_PLANNED_SUPPLY — Loads planned supply records into the planning supply tables, allowing external supply plans to be incorporated into the ASCP planning cycle.

Tables Accessed

The package operates across a broad slice of the MSC planning schema through APPS synonyms. MSC_DEMAND_CLASSES, MSC_PLAN_ORGANIZATIONS, MSC_ITEMS, MSC_SYSTEM_ITEMS, and MSC_PLAN_BUCKETS supply the dimensions and calendar context used to validate and stamp output levels. MSC_SAFETY_STOCKS and MSC_SUPPLIES, together with the MSC_SUPPLIES_S interface table, are the primary read/write targets for safety stock and planned supply movement. MSC_PLANS, MSC_APPS_INSTANCES, MSC_REGIONS, MSC_PROJECTS, MSC_PROJECT_TASKS, and MSC_TP_ID_LID provide scenario, instance, and hierarchical reference data. FND_LOOKUP_VALUES supplies valid lookup codes, including those governing output-level validation.

Usage Notes

MSC_WS_APS_DATA_SERVICES is invoked by the APS web services layer rather than being called directly from Oracle Forms. External planning applications, partner integrations, and the Advanced Planning integration flows invoke its procedures during forecast loading and safety stock synchronization. The header identifies responsibility, application, and security group parameters, confirming the package honors Oracle EBS function security on every call. Because the package depends on AUTHID CURRENT_USER and performs direct DML against MSC planning tables, custom code should invoke it only through supported service entry points and should honor the mandatory Item output level and the single-member rule for optional categories.