Search Results msc_atp_summary_so_u1

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC.MSC_ATP_SUMMARY_SO is a planning table owned by the MSC schema in Oracle E-Business Suite, populated and consumed by the Advanced Supply Chain Planning (ASCP) and Global Order Promising (GOP) modules. Its name reflects its function: it holds an ATP (Available-to-Promise) summary of sales order demand. Each row aggregates open sales order demand for a given plan, source instance, organization, inventory item, demand date, and demand class, allowing the planning engine to evaluate scheduled ship dates against supply and to publish planning results back to the transactional order management system.

From a Data Vault modeling perspective, the mined metadata suggests this object behaves as a satellite: the business key (SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, SD_DATE, DEMAND_CLASS) is stable and descriptive, while the quantity measure (SD_QTY) and administrative columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY) change over planning cycles. It is standalone in the dependency graph — it does not reference any database object and is referenced only by its APPS synonym, MSC_ATP_SUMMARY_SO — so it is best treated as a denormalized fact-style append/update target within the ASCP schema rather than a normative dimension.

The table is relevant across EBS 12.1.1 and 12.2.2. The 12.2.2 documented physical schema records 17 columns in the MSC owner, with a U1 unique index and a composite primary key that adds PLAN_ID to the business key.

Key Information Stored

The most significant columns and their roles are:

• PLAN_ID — identifies the ASCP plan that owns the summary row; part of the composite primary key.
• SR_INSTANCE_ID — source system instance; distinguishes data originating from different source instances.
• ORGANIZATION_ID — inventory organization (warehouse) context for the demand.
• INVENTORY_ITEM_ID — the planned item.
• DEMAND_CLASS — demand classification (e.g., forecast class) keyed into the unique index.
• CUSTOMER_CLASS, CUSTOMER_ID, SHIP_TO_SITE_ID — customer segmentation and ship-to context for the summarized demand.
• SD_DATE — the scheduled ship date; the time dimension of the ATP summary.
• SD_QTY — aggregated scheduled ship quantity for that combination.
• TASK_ID, PROJECT_ID — project/task references for project-based or MTO demand.
• PLANNING_GROUP — grouping attribute used during planning runs.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard EBS audit columns.

The documented surrogate primary key is MSC_ATP_SUMMARY_SO (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, SD_DATE, DEMAND_CLASS). The business-key candidate MSC_ATP_SUMMARY_SO_U1 is unique on (SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, SD_DATE, DEMAND_CLASS) — the same key minus PLAN_ID. This distinction allows plan-ranged comparison while preserving a flat lookup key.

Common Use Cases and Queries

Typical usage includes reconciling planning quantities against booked sales orders, load validation, and diagnosing why a plan produced unexpected promised dates. Basic extraction uses the documented query text:

• SELECT SD_DATE, SUM(SD_QTY) FROM MSC.MSC_ATP_SUMMARY_SO WHERE PLAN_ID = :p AND ORGANIZATION_ID = :org GROUP BY SD_DATE; — daily ATP demand profile.
• SELECT * FROM MSC.MSC_ATP_SUMMARY_SO WHERE INVENTORY_ITEM_ID = :item AND CUSTOMER_ID = :cust; — demand detail by item and customer.
• SELECT DEMAND_CLASS, SUM(SD_QTY) FROM MSC.MSC_ATP_SUMMARY_SO WHERE SR_INSTANCE_ID = :inst AND SD_DATE BETWEEN :from AND :to GROUP BY DEMAND_CLASS; — demand-class breakdown for a source instance.
• DELETE FROM MSC.MSC_ATP_SUMMARY_SO WHERE PLAN_ID = :p; — plan cleanup; typically performed by planner worker programs, not manually.

Because the table is a planning staging/summary object, direct DML by end users should be avoided; rows are maintained through ASCP/GOP engine processes and refreshed on each plan run.

Related Objects

Documented relationships are minimal: MSC_ATP_SUMMARY_SO does not reference any database object, and it is referenced only by the APPS synonym MSC_ATP_SUMMARY_SO. In practice, the table participates logically with:

• APPS.MSC_ATP_SUMMARY_SO — the application-layer synonym defined over MSC.MSC_ATP_SUMMARY_SO.
• MSC.MSC_ATP_RULES / ATP rule tables — demand-class and scheduling rules used when interpreting rows.
• MSC.MSC_SYSTEM_ITEMS / MTL_SYSTEM_ITEMS — item master joined on INVENTORY_ITEM_ID.
• MSC.MSC_PLANS — joined on PLAN_ID to resolve plan names and status.
• OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL — transactional sales orders the summary is derived from and feeds back into via scheduling.
• HZ_CUST_ACCOUNTS / HZ_CUST_SITE_USES_ALL — customer and ship-to resolution via CUSTOMER_ID and SHIP_TO_SITE_ID.
• MSC.MSC_ATP_SUMMARY — the sibling supply-side/full ATP summary structure.

Joins are anchored on SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, and SD_DATE. All object names, columns, types, and the U1 unique index cited above are drawn from the ETRM 12.2.2 documentation; the same definitions apply under 12.1.1 suppression of later-table changes.