Results for “msc_plan_organizations_u1”

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

Overview

MSC.MSC_PLAN_ORGANIZATIONS is a transactional configuration table in the Oracle Advanced Planning and Scheduling (APS) and Supply Chain Hub schemas. It stores the parameters that govern how each inventory organization participates in a given plan, and it records the companies that collaborate on a shared supply or demand plan. In Oracle EBS 12.1.1 and 12.2.2, this table forms the organizational scope of every APS plan: a plan cannot net, schedule, or publish supply and demand for an organization unless a corresponding row exists here with the appropriate flags enabled. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its unique index resides in APPS_TS_TX_IDX.

From a Data Vault modeling perspective, the mined classification is hub-leaning. This suggests treating the combination of PLAN_ID, ORGANIZATION_ID, and SR_INSTANCE_ID as a durable business key hub, with the remaining flag, calendar, and horizon columns modeled as satellite attributes that change over time.

Key Information Stored

The table contains 56 documented columns. The physical primary key is MSC_PLAN_ORGANIZATIONS_PK (SR_INSTANCE_ID, PLAN_ID, ORGANIZATION_ID). The business-key candidate is the unique index MSC_PLAN_ORGANIZATIONS_U1 (PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID), which enforces the same combination at the logical level. There is no single surrogate sequence column; the composite key itself acts as the identifier.

The most significant columns include:

• PLAN_ID — the plan identifier, foreign key to MSC_PLANS.
• ORGANIZATION_ID — the organization identifier, foreign key to MSC_PARAMETERS.
• SR_INSTANCE_ID — the source instance identifier for the org, supporting multi-instance supply chain hub deployments.
• ORGANIZATION_CODE and ORGANIZATION_DESCRIPTION — denormalized display attributes for reporting.
• NET_WIP, NET_RESERVATIONS, NET_PURCHASING, NET_ON_HAND, and PLAN_SAFETY_STOCK — netting control flags that determine which supply sources are consumed during the plan run.
• PLAN_LEVEL — the planning granularity assigned to the organization.
• CALENDAR_CODE, FORECAST_CALENDAR, and BILL_OF_RESOURCES — the calendar and resource structures used by the plan.
• SIMULATION_SET — identifies the simulation context for what-if analysis.
• FROZEN_HORIZON_DAYS, FIRM_HORIZON_DAYS, and the CURR_ equivalents — scheduling horizons applied during planning.
• COLLAB_ROLE and CONTACT — collaboration metadata for supply chain hub partners.
• INCLUDE_SALESORDER and INCLUDE_PRODUCTION_SCHEDULE — flags controlling demand and schedule inclusion.
• Standard Who and Concurrent Who columns (LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID), plus the ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 descriptive flexfield segments.

Common Use Cases and Queries

Typical scenarios include verifying which organizations are enrolled in a plan, auditing netting flags before a plan run, and reporting collaboration roles across the supply chain hub. A common query lists organizations in a given plan:

• SELECT organization_id, organization_code, plan_level FROM msc.msc_plan_organizations WHERE plan_id = :plan_id AND sr_instance_id = :instance_id;
• SELECT plan_id, organization_id FROM msc.msc_plan_organizations WHERE net_wip = 1 AND net_purchasing = 1;
• Join to MSC_PLANS to correlate organization scope with plan names and run dates.
• Join to MSC_PARAMETERS on ORGANIZATION_ID to bring back MRP and planning parameters for the same organizations.

Because the table is interface-loaded by the APS collections and plan definition concurrent programs, reporting solutions should filter on LAST_UPDATE_DATE or PROGRAM_UPDATE_DATE to detect recently changed enrollments.

Related Objects

The relationship data identifies this table as central to the APS schema. Parent references are MSC_PARAMETERS (via ORGANIZATION_ID) and MSC_PLANS (via PLAN_ID). Child tables that reference MSC_PLAN_ORGANIZATIONS on PLAN_ID include:

• MSC_BILL_OF_RESOURCES — resource definitions scoped by plan and organization.
• MSC_DEPARTMENT_RESOURCES — departmental resource capacity.
• MSC_PLAN_BUCKETS — time-bucket definitions used for aggregation.
• MSC_PLAN_ORG_KPIS — key performance indicators per plan organization.
• MSC_PLAN_ORG_STATUS — plan run status per organization.
• MSC_PLAN_SCHEDULES — scheduling data for the plan.
• MSC_PROJECTS — project-level planning records.
• MSC_SUB_INVENTORIES — subinventory-level supply and demand.
• MSC_SYSTEM_ITEMS — item master within the plan context.

Together these dependencies establish MSC_PLAN_ORGANIZATIONS as the organizational anchor for plan-level reporting and integration across APS and the Supply Chain Hub.