Search Results msc_st_planners




Overview
Explore in AI Chat

Overview

MSC_ST_PLANNERS is a staging table in the MSC schema, owned by Oracle Advanced Supply Chain Planning (ASCP). It is the interface buffer used by the data collection program to validate and process planner records before they are propagated to the base planning table MSC_PLANNERS. In the Oracle EBS 12.1.1 and 12.2.2 data model, ASCP collection routines extract planner assignments from source instances, load them into this staging table, apply validation and transformation rules, and then publish the accepted rows into the production planner table. Because collection is a multi-pass, restartable process, the staging table preserves an isolated, auditable copy of every inbound record until it successfully clears validation.

From a heuristic Data Vault modeling perspective, this object is best classified as a standalone satellite. It carries descriptive planner attributes plus type‑1 and type‑2 style tracking columns (audit fields, disable dates, refresh and delete flags), but the metadata documents no foreign key to a parent MSC hub or link other than the mined reference to PSB_EMPLOYEES. Rather than a normalized hub, it is a transient satellite that stages attribute values for a business key (PLANNER_CODE) that is stable across the planning instance.

Key Information Stored

The documented schema exposes 45 columns. The most important for identification and processing are:

No surrogate primary key or unique index is documented in the supplied metadata; PLANNER_CODE combined with ORGANIZATION_ID and SR_INSTANCE_ID is the practical business key used for de‑duplication and upsert logic.

Common Use Cases and Queries

Typical usage centers on collection diagnostics, error investigation, and reconciliation between source and planning instances. Common patterns include:

  • Identifying rows that failed validation during the last run: select planner_code, message_id, error_text from msc_st_planners where process_flag = 'ERROR' (flag semantics per implementation).
  • Auditing which concurrent request loaded a given planner: filter by request_id or program_update_date to tie staging rows to FND_CONCURRENT_REQUESTS.
  • Reconciling staged data against the published MSC_PLANNERS rows using planner_code, organization_id, and sr_instance_id as the join key.
  • Reporting active planners by extracting current_employee_flag = 'Y' and joining to psb_employees on employee_id.

Collection jobs also use refresh_id and batch_id to segregate incremental load batches and support restartability.

Related Objects

  • MSC_PLANNERS — the production target table populated from staging after successful validation.
  • PSB_EMPLOYEES — referenced via MSC_ST_PLANNERS.EMPLOYEE_ID, providing employee identity and status.
  • MSC_ST_PLANNER_ORGS / organization staging counterparts — companion staging entities for organization context.
  • FND_CONCURRENT_REQUESTS — joined on request_id to trace the collecting program.
  • FND_APPLICATION and FND_CONCURRENT_PROGRAMS — resolved via program_application_id and program_id.
  • MSC_SYSTEM_ITEMS and other MSC_ST_* staging tables — share the same collection framework and process_flag/error_text conventions.
  • The ASCP collection concurrent programs (e.g., planner collection manager) that read and write this staging table.