MSC_SUPPLIES

-- ============================================================
-- Table  : MSC_SUPPLIES — Every supply row in a plan — existing purchase orders, jobs, on-hand, and in-transit alongside the planner's recommended planned orders; the supply side of ASCP plan output
-- Purpose: Column-selected read of MSC_SUPPLIES — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + PLAN_ID + SR_INSTANCE_ID + TRANSACTION_ID
-- Caution: Plan-scoped and mixed-content: always filter one PLAN_ID (and SR_INSTANCE_ID in multi-instance shops) and an ORDER_TYPE before counting recommendations — the row ids repeat across plans
-- 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 — every query filters one",
  t.SR_INSTANCE_ID AS "The collected source instance — part of the key",
  t.TRANSACTION_ID AS "The supply row id — plan-scoped, repeats across plans; NOT an EBS transaction id",
  t.ORGANIZATION_ID AS "Planning-instance copy of the org id",
  t.INVENTORY_ITEM_ID AS "Planning-instance copy of the item id — join through the planning item master, not straight to EBS",
  t.ORDER_TYPE AS "What kind of supply the row is — planned order, PO, job, on-hand…; numeric, undecoded (circulating decodes conflict)",
  t.NEW_SCHEDULE_DATE AS "The supply's due date — the analysis date",
  t.NEW_ORDER_PLACEMENT_DATE AS "The supply's start date",
  t.NEW_ORDER_QUANTITY AS "The supply quantity",
  t.OLD_SCHEDULE_DATE AS "The pre-reschedule date — reschedule analysis pairs it with the new one",
  t.OLD_ORDER_QUANTITY AS "The pre-reschedule quantity",
  t.DISPOSITION_ID AS "Reference to the source of the supply",
  t.DISPOSITION_STATUS_TYPE AS "1 = not cancelled, 2 = cancelled (per the data dictionary's own comment)",
  t.FIRM_PLANNED_TYPE AS "Whether the supply is firmed against replanning",
  t.RESCHEDULE_DAYS AS "How far the plan moved the supply",
  t.IMPLEMENTED_QUANTITY AS "How much of a recommendation was released"
FROM <catalog>.<schema>.MSC_SUPPLIES 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.TRANSACTION_ID = <TRANSACTION_ID>
  -- AND t.NEW_SCHEDULE_DATE >= DATE '<DATE_FROM>'
  -- AND t.NEW_SCHEDULE_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;