MSC_PLANS

-- ============================================================
-- Table  : MSC_PLANS — The ASCP plan registry — one row per plan with its user-visible name, owning org, type, planning horizon, and run timestamps; every plan-output query starts by picking a row here
-- Purpose: Column-selected read of MSC_PLANS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + PLAN_ID
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.PLAN_ID AS "The plan id every plan-output row is scoped by",
  t.COMPILE_DESIGNATOR AS "The plan name users see",
  t.SR_INSTANCE_ID AS "The collected source instance",
  t.ORGANIZATION_ID AS "The org that owns the plan",
  t.PLAN_TYPE AS "MRP, DRP, or MPS plan — numeric, names documented, codes not",
  t.CURR_START_DATE AS "Start of the planning horizon",
  t.CURR_CUTOFF_DATE AS "End of the planning horizon",
  t.PLAN_START_DATE AS "When the plan run started",
  t.PLAN_COMPLETION_DATE AS "When the plan run completed — the recency check",
  t.DATA_START_DATE AS "Snapshot start",
  t.DATA_COMPLETION_DATE AS "Snapshot completion",
  t.CURR_SCHEDULE_DESIGNATOR AS "The schedule the plan ran against",
  t.DEMAND_TIME_FENCE_FLAG AS "Whether the demand time fence was applied"
FROM <catalog>.<schema>.MSC_PLANS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.PLAN_ID = <PLAN_ID>
  -- AND t.PLAN_COMPLETION_DATE >= DATE '<DATE_FROM>'
  -- AND t.PLAN_COMPLETION_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.PLAN_ID;