CO01

-- ============================================================
-- T-Code : CO01 Create Production Order
-- Purpose: Create a production order with header, item, and operations
-- Grain  : One row per production order with first-line item and operation
-- Tables : AFKO, AFPO, AUFK, AFVC, MAKT, MARA, T001W
-- Notes  : AFVC joins to AFKO via AUFPL (routing number), NOT AUFNR — otherwise you get a Cartesian product on operations. Status evaluation requires JEST + TJ02T lookup (see JEST trick at end).
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.AUFNR   AS "Order Number",
  l.POSNR   AS "Order Item",

  -- Descriptive text
  ord.KTEXT AS "Short Text",
  mt.MAKTX  AS "Material Description",

  -- Quantities + UOM (paired)
  h.GAMNG   AS "Total Order Quantity",
  h.GMEIN   AS "Order Unit of Measure",
  l.PSMNG   AS "Item Quantity",
  l.WEMNG   AS "Delivered Quantity",
  (l.PSMNG - l.WEMNG) AS "Open Quantity",
  l.PAMNG   AS "Scrap Quantity",

  -- Dates
  h.GSTRP   AS "Basic Start Date",
  h.GLTRP   AS "Basic Finish Date",
  h.GSTRS   AS "Scheduled Start",
  h.GLTRS   AS "Scheduled Finish",
  h.FTRMI   AS "Actual Release Date",
  ord.ERDAT AS "Created On",

  -- Status / indicators
  ord.OBJNR AS "Object Number (JEST/TJ02T)",
  ord.USER0 AS "User Status",

  -- Org fields
  l.DWERK   AS "Plant",
  pt.NAME1  AS "Plant Name",
  ord.BUKRS AS "Company Code",
  h.FEVOR   AS "Production Scheduler",

  -- Master data enrichment
  ord.AUART AS "Order Type",
  h.PLNBEZ  AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  h.DISPO   AS "MRP Controller",
  h.PLNNR   AS "Routing Group",
  h.PLNAL   AS "Group Counter"
FROM <catalog>.<schema>.afko h
INNER JOIN <catalog>.<schema>.afpo l
  ON  l.MANDT = h.MANDT
  AND l.AUFNR = h.AUFNR
INNER JOIN <catalog>.<schema>.aufk ord
  ON  ord.MANDT = h.MANDT
  AND ord.AUFNR = h.AUFNR
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.DWERK
WHERE
  h.MANDT = '<MANDT>'
  AND ord.ERDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND l.DWERK = '<WERKS>'
  -- AND h.AUFNR = '<AUFNR>'
ORDER BY h.GLTRP, h.AUFNR;