Search Results msc_plan_org_status




Overview
Explore in AI Chat

Overview

The MSC_PLAN_ORG_STATUS table is a core data object within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module. Its primary function is to maintain a historical log of the operational statuses for collaborating organizations within a specific supply chain plan. This table is essential for tracking the lifecycle of an organization's participation in a planning run, capturing state changes such as when an organization is added, activated, in the process of being planned, or completed. This historical record provides critical auditability and context for understanding the sequence of events and potential issues during the complex ASCP engine execution.

Key Information Stored

The table's structure is centered around identifying a specific plan, organization, and its status over time. While the full column list is not detailed in the provided metadata, its foreign key relationships define its critical composite key and context. The table uniquely identifies records using the combination of PLAN_ID, ORGANIZATION_ID, and SR_INSTANCE_ID (which identifies the specific instance in a multi-organization setup). Core columns typically include a STATUS column to store the numeric or textual code representing the organization's state (e.g., 'Not Planned', 'Planning', 'Complete'), a STATUS_DATE or LAST_UPDATE_DATE to timestamp the change, and likely a SEQUENCE_NUMBER to order status transitions chronologically for a given plan-organization combination. This enables users to reconstruct the exact status timeline for any entity in a plan.

Common Use Cases and Queries

This table is primarily used for diagnostic reporting and process monitoring. A common use case is investigating a plan run that failed or was delayed; analysts can query this table to see which organizations were in a 'Planning' or 'Error' status at a specific time. Another key use is verifying successful plan completion by confirming all relevant organizations reached a 'Complete' status. A typical diagnostic query would join to MSC_PLAN_ORGANIZATIONS to get organization names.

  • Sample Query (Check Status History for a Plan):
    SELECT org.organization_code, pos.status, pos.status_date
    FROM msc_plan_org_status pos,
    msc_plan_organizations org
    WHERE pos.plan_id = 1000
    AND pos.plan_id = org.plan_id
    AND pos.organization_id = org.organization_id
    AND pos.sr_instance_id = org.sr_instance_id
    ORDER BY org.organization_code, pos.status_date;
  • Monitoring Current Plan Run: Filtering for the latest status per organization in an active plan to create a real-time dashboard of planning progress.

Related Objects

The most direct and critical relationship for MSC_PLAN_ORG_STATUS, as confirmed by the provided metadata, is with the MSC_PLAN_ORGANIZATIONS table. It holds a foreign key constraint linking its PLAN_ID, ORGANIZATION_ID, and SR_INSTANCE_ID columns to the corresponding primary key columns in MSC_PLAN_ORGANIZATIONS. This ensures referential integrity, meaning a status record cannot exist for an organization not defined for the plan. This table is also intrinsically linked to the central MSC_PLANS table (via PLAN_ID) and is populated and managed by the ASCP engine's internal processes. While not listed, it may be referenced by various MSC_* planning snapshot and transaction tables for status validation.