Results for “msc_plan_org_status”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MSC_PLAN_ORG_STATUS is a table in the MSC schema belonging to the Advanced Supply Chain Planning (ASCP) module of Oracle E-Business Suite, documented as VALID across 12.1.1 and 12.2.2. Its stated purpose is to maintain the history of statuses of collaborating organizations participating in a plan. In a distributed planning deployment, multiple source instances and organizations collaborate against a single plan, and each participant progresses through lifecycle states as data is collected, planned, and published. MSC_PLAN_ORG_STATUS records those transitions over time rather than holding only a current flag, which makes it the historical audit trail for organization-level plan execution. From a Data Vault modeling perspective, the FK structure suggests a satellite-leaning classification: the table appears to attach descriptive, time-stamped attributes to a parent planning relationship rather than acting as an independent hub or a pure many-to-many link.
Key Information Stored
The documented physical schema contains six columns in the MSC schema: PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, STATUS, STATUS_DATE, and NUMBER1. The composite of PLAN_ID, ORGANIZATION_ID, and SR_INSTANCE_ID forms the business-key candidate that identifies which collaborating organization on which source instance, within which plan, a given status row describes. These three columns also constitute the foreign key relationship pointing to MSC_PLAN_ORGANIZATIONS, confirming that the parent planning organization record must exist before any status history is written.
- PLAN_ID — identifies the ASCP plan to which the status record belongs; primary join key to the plan definition.
- ORGANIZATION_ID — the inventory organization acting as a collaborating participant in the plan.
- SR_INSTANCE_ID — the source (transactional) instance from which the organization's data originates; essential in multi-instance or hub-and-spoke deployments.
- STATUS — the status value assigned to the organization for the plan at the point in time recorded.
- STATUS_DATE — the effective timestamp of the status change, enabling historical sequencing and trend analysis.
- NUMBER1 — a generic numeric attribute reserved for supplementary status-related values.
The metadata does not document a separate surrogate primary key column; the identifying key is the business composite of the three foreign key columns.
Common Use Cases and Queries
Typical uses include diagnosing why an organization did not contribute to a plan run, reporting the current state of each collaborating organization, and auditing the sequence of status transitions for troubleshooting or SLA measurement. A common pattern retrieves the latest status per organization using an analytic function:
SELECT plan_id, organization_id, sr_instance_id, status, status_date FROM (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY plan_id, organization_id, sr_instance_id ORDER BY status_date DESC) rn FROM msc_plan_org_status s WHERE plan_id = :plan_id) WHERE rn = 1;- Historical trend query: group by status and STATUS_DATE to count organizations per state across a planning cycle.
- Exception query: join to MSC_PLAN_ORGANIZATIONS to list organizations whose latest status indicates a failure or incomplete state.
These queries support plan monitoring dashboards, collection diagnostics, and post-run reconciliation reports.
Related Objects
The documented foreign key links PLAN_ID, ORGANIZATION_ID, and SR_INSTANCE_ID to MSC_PLAN_ORGANIZATIONS, making that table the primary parent and the most significant related object. Additional objects that logically participate in this area include:
- MSC_PLAN_ORGANIZATIONS — parent definition of organizations collaborating in a plan; direct FK target.
- MSC_PLANS — plan header supplying plan-level context for PLAN_ID.
- MSC_PLAN_INSTANCES — instance-level planning configuration aligned with SR_INSTANCE_ID.
- MSC_ORGANIZATIONS — organization master data referenced by ORGANIZATION_ID.
- MSC_SYSTEM_ITEMS and MSC_PLAN_SCHEDULES — planning data scoped to the same plan and organization combinations.
Because MSC_PLAN_ORG_STATUS is satellite-leaning, most reporting joins fan out from MSC_PLAN_ORGANIZATIONS to this table to enrich the parent relationship with time-stamped status attributes.
-
Maintains the history of statuses of collaborating organizations in a plan.
-
Maintains the history of statuses of collaborating organizations in a plan.
-
Lookup Type: MSC_OTHER_TABLE 12.2.2
MSC_OTHER_TABLE
-
Lookup Type: MSC_OTHER_TABLE 12.1.1
MSC_OTHER_TABLE
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 FND Design Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.2.2 DBA Data 12.2.2
-
12.2.2 DBA Data 12.2.2