MSC_SYSTEM_ITEMS

-- ============================================================
-- Table  : MSC_SYSTEM_ITEMS — The planning copy of the item master at plan + instance + org + item grain — collected item attributes frozen per plan run, and the bridge column back to the source EBS item
-- Purpose: Column-selected read of MSC_SYSTEM_ITEMS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + PLAN_ID + SR_INSTANCE_ID + INVENTORY_ITEM_ID
-- Caution: INVENTORY_ITEM_ID here is the planning-side surrogate — the EBS item id is SR_INVENTORY_ITEM_ID; joining plan output straight to the EBS item master on the local id is wrong
-- 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 — item attributes are frozen per plan run",
  t.SR_INSTANCE_ID AS "The collected source instance — part of the key",
  t.ORGANIZATION_ID AS "Planning-instance copy of the org id — part of the key",
  t.INVENTORY_ITEM_ID AS "The planning-side item surrogate — part of the key; NOT the EBS item id",
  t.SR_INVENTORY_ITEM_ID AS "The item id in the source EBS instance — the ONLY sound bridge back to the item master",
  t.ITEM_NAME AS "The item name as collected",
  t.DESCRIPTION AS "Item description as collected",
  t.UOM_CODE AS "Primary UOM as collected",
  t.MRP_PLANNING_CODE AS "How the item is planned — MRP, MPS, DRP combinations; codes enumerated in the data dictionary's own comment",
  t.FIXED_LEAD_TIME AS "Fixed lead time as collected",
  t.VARIABLE_LEAD_TIME AS "Variable lead time",
  t.PREPROCESSING_LEAD_TIME AS "Preprocessing lead time",
  t.POSTPROCESSING_LEAD_TIME AS "Postprocessing lead time",
  t.PLANNING_TIME_FENCE_DATE AS "Planning time fence as of the plan run",
  t.DEMAND_TIME_FENCE_DATE AS "Demand time fence as of the plan run",
  t.STANDARD_COST AS "Standard cost as collected",
  t.SAFETY_STOCK_CODE AS "Safety stock method as collected",
  t.MINIMUM_ORDER_QUANTITY AS "Minimum order quantity constraint"
FROM <catalog>.<schema>.MSC_SYSTEM_ITEMS 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.SR_INSTANCE_ID = <SR_INSTANCE_ID>
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.PLAN_ID;