Search Results retrieve_period_and_sd_data

Overview

MSC_ATP_UTILS is a PL/SQL utility package in the APPS schema that supports the Available-to-Promise (ATP) processing infrastructure within Oracle E-Business Suite. Its header revision ($Header: MSCUATPS.pls 120.2) indicates the package has been stable since at least 2007, and it remains documented in ETRM for both release 12.1.1 and 12.2.2. The package serves as a bridge between the ATP engine supplied by MRP_ATP_PUB and the temporary staging structures used to persist scheduling, supply/demand, period, and pegging results across session boundaries — a requirement in multi-tier or distributed configurations where ATP results must be moved between databases or reused across calls.

The package is classified as OTHER (not a formal public API), meaning it is primarily intended for internal consumption by other Oracle Supply Chain modules. It is nevertheless referenced by eight other packages, confirming its role as shared infrastructure.

Key Procedures and Functions

The documented interface exposes twenty procedures and functions. Grouped by purpose:

• Temporary table I/O: PUT_INTO_TEMP_TABLE and GET_FROM_TEMP_TABLE move ATP record, supply/demand, period, and detail type structures into and out of a session-scoped staging area, with a mode and optional dblink parameter to support remote execution.
• ATP invocation: CALL_ATP_11 invokes the ATP engine (group id, session id, insert and partial flags) and returns a numeric status; it forms the core entry point used by callers.
• Scheduling data handling for "put_sch_data_result_mode": PUT_SCH_DATA_REQUEST_MODE and PUT_SCH_DATA_RESULT_MODE are the two modes paired with the user's search term. They handle insertion of scheduling information into MRP_ATP_SCHEDULE_TEMP, with the REQUEST_MODE (1) and RESULTS_MODE (2) constants declared at lines 7–8 controlling behaviour. PUT_SCHEDULING_DATA and TRANSFER_SCHEDULING_DATA perform the corresponding bulk write and movement operations.
• Supply/demand and period processing: PUT_SD_DATA, PUT_PERIOD_DATA, RETRIEVE_PERIOD_AND_SD_DATA, COPY_MRP_SD_RECS, and PROCESS_SUPPLY_DEMAND_DETAILS manage the detail-level tables that back an ATP enquiry.
• Pegging: PUT_PEGGING_DATA records pegging relationships produced by the ATP calculation.
• BOM support: PUT_INTO_BOM_TEMP_TABLE was added (per the inline comment) to insert BOM data into MSC_BOM_TEMP when ATP is invoked for CTO models from Order Management or Configurator.
• Schedule utilities: EXTEND_MAST and TRIM_MAST manipulate the mrp_atp_schedule_temp_typ record to grow or reduce schedule horizons.
• Transfer and maintenance: TRANSFER_MRP_ATP_DETAILS_TEMP, UPDATE_LINE_ITEM_PROPERTIES, and TRUNCATE_DEMAND manage staging content between sessions.
• TEST is a legacy diagnostic procedure retained only for compatibility.

Tables Accessed

• MRP_ATP_DETAILS_TEMP and MRP_ATP_SCHEDULE_TEMP — primary staging targets for ATP scheduling and detail output.
• MSC_BOM_TEMP, MSC_CTO_BOM, MSC_CTO_SOURCES — support CTO/configuration BOM data written during ATP calls from OM or Configurator.
• MSC_SYSTEM_ITEMS, MTL_SYSTEM_ITEMS — item validation and attribute lookups.
• MTL_DEMAND_INTERFACE and MTL_SUPPLY_DEMAND_TEMP — demand and supply bridge tables for ATP calculation input/output.
• OE_ORDER_LINES_ALL — order line context, referenced so ATP results can be tied back to their originating order lines.
• DBMS_SQL and PLITBLM are supplied package dependencies used for dynamic SQL and message token handling.

Usage Notes

MSC_ATP_UTILS is not typically invoked directly from a form. It is called by other supply chain packages — the eight known referencing packages — and by concurrent programs that materialise ATP results into temporary tables prior to forwarding them to Order Management. Custom extensions should follow the same pattern: call PUT_INTO_TEMP_TABLE in REQUEST_MODE, execute the ATP engine via CALL_ATP_11, then retrieve results using the RESULTS_MODE (or PUT_SCH_DATA_RESULT_MODE) path. Because the package writes to persistent temporary tables keyed by session identifier, callers must supply a unique x_session_id and clean up via TRUNCATE_DEMAND or TRANSFER_* when processing completes. Direct modification of the underlying tables is not supported; the API contract is defined by the declared procedures only.