Search Results msc_atp_peg_temp_n1

Overview

MSC.MSC_ATP_PEG_TEMP is a global temporary table in the MSC (Advanced Planning and Scheduling) schema of Oracle E-Business Suite. It is used by the available-to-promise (ATP) and pegging engine to stage transient allocations between demand and supply records during planning and scheduling runs. The table is defined with a data duration of SYS$TRANSACTION, meaning rows persist only for the duration of the transaction that inserts them; the inserting session alone can see its own rows, and other sessions are isolated from this data. This design supports concurrent ATP and scheduling computations across multiple plans and organizations without contention.

From a Data Vault modeling perspective, this object is classified heuristically as a standalone structure. That classification suggests it functions neither as a persistent hub, link, or satellite in the conventional sense, but rather as a transient work area whose lifecycle is bounded by the seeding transaction. Persistent history for the same relationships is maintained elsewhere in the MSC schema, notably through MRP_FULL_PEGGING.

Key Information Stored

The table holds 43 documented columns. The most significant are grouped as follows:

• Planning context: PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, and INVENTORY_ITEM_ID identify the plan, source instance, organization, and planned item for each pegging row.
• Demand identity: DEMAND_ID, DEMAND_QUANTITY, DEMAND_CLASS, SALES_ORDER_LINE_ID, DEMAND_SOURCE_TYPE, and END_DEMAND_ID describe the originating demand, including sales order linkage and terminal demand record.
• Supply identity: SUPPLY_ID, SUPPLY_QUANTITY, and ALLOCATED_QUANTITY describe the supply record and the quantity applied against the demand.
• Pegging chain: PEGGING_ID, PREV_PEGGING_ID, and END_PEGGING_ID link each row into the pegging chain, joining to MRP_FULL_PEGGING.
• Resource relief: RESOURCE_ID, DEPARTMENT_ID, RESOURCE_HOURS, DAILY_RESOURCE_HOURS, RELIEF_TYPE, RELIEF_QUANTITY, and DAILY_RELIEF_QTY capture capacity relief implications of the allocation.
• Timing: TRANSACTION_DATE, START_DATE, END_DATE, ORIGINAL_DATE, and the ATF_DATE column, which records the available-to-fulfill date — the field of interest in the user's search. ATF_DATE represents the date on which the supply is confirmed as available to fulfill the associated demand after pegging and relief are applied.
• Audit and party: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE provide standard audit columns; CUSTOMER_ID and CUSTOMER_SITE_ID associate demand with the ordering customer and site, while PRODUCT_FAMILY_ID and ORIGINAL_ITEM_ID support family-level and original-item reporting.

The table has no documented primary key column in the ETRM metadata, and the sole index, MSC_ATP_PEG_TEMP_N1, is non-unique. This absence of a surrogate key is consistent with a temporary staging table. The index covers PLAN_ID, RELIEF_TYPE, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, REFERENCE_ITEM_ID, END_DEMAND_ID, DEMAND_ID, SUPPLY_ID, RESOURCE_ID, and RESOURCE_ID, supporting the primary access paths for pegging and relief queries.

Common Use Cases and Queries

Typical usage involves diagnosing ATP results during a plan run, verifying pegging between a demand and its supply, and reporting available-to-fulfill dates. A representative query to retrieve pegging rows for an item within a plan is:

SELECT DEMAND_ID, SUPPLY_ID, PEGGING_ID, ATF_DATE, DEMAND_QUANTITY, ALLOCATED_QUANTITY
FROM   MSC.MSC_ATP_PEG_TEMP
WHERE  PLAN_ID = :plan_id
AND    ORGANIZATION_ID = :org_id
AND    INVENTORY_ITEM_ID = :item_id;

Because the table is temporary with SYS$TRANSACTION duration, such queries return data only within the session that populated the table. Reporting use cases therefore typically involve extracting results within the originating concurrent program, or materializing the desired columns into a permanent table before the transaction ends. Comparisons of DEMAND_QUANTITY, ALLOCATED_QUANTITY, CONSUMED_QTY, and OVERCONSUMPTION_QTY help identify over- or under-allocated demands, while ATF_DATE ordering supports earliest-availability analyses.

Related Objects

• MRP_FULL_PEGGING — joined via PEGGING_ID to resolve the full pegging chain for each temporary row.
• AHL_PRD_DISPOSITIONS_B — referenced through DISPOSITION_ID to identify the disposition applied to the demand or supply.
• BOM_DEPARTMENTS — referenced through DEPARTMENT_ID to resolve the department associated with resource relief.
• MSC.MSC_ATP_PEG_TEMP_N1 — the non-unique index supporting plan, item, demand, supply, and resource lookups.
• Persistent planning tables in the MSC schema, such as MSC_ATP_PEGGED_ORDERS and the standard ATP views, provide the durable counterparts used for reporting beyond the transaction boundary.

Collectively, these objects place MSC_ATP_PEG_TEMP within the broader MSC pegging and ATP framework, serving as the transient working store from which permanent pegging and availability results are derived.