CA01

-- ============================================================
-- T-Code : CA01 Create Routing
-- Purpose: Create a routing with header + operations
-- Grain  : One row per routing operation (PLKO header + PLPO operation)
-- Tables : MAPL, PLKO, PLPO, MAKT, CRHD, T001W
-- Notes  : MAPL is the plant-level routing assignment (material → PLNNR group + PLNAL alternative). PLPO carries the operation sequence; CRHD resolves ARBID into a readable work-center code.
-- ============================================================
SELECT
  -- Keys
  a.MANDT   AS "Client",
  a.MATNR   AS "Material",
  a.PLNNR   AS "Routing Group",
  a.PLNAL   AS "Group Counter",
  h.PLNTY   AS "Task List Type",
  l.VORNR   AS "Operation Number",

  -- Descriptive text
  mt.MAKTX  AS "Material Description",
  h.KTEXT   AS "Routing Description",
  l.LTXA1   AS "Operation Short Text",

  -- Quantities + UOM (paired)
  l.BMSCH   AS "Base Quantity",
  l.MEINH   AS "Unit of Measure",
  l.VGW01   AS "Standard Value 1",
  l.VGE01   AS "Std Value Unit 1",
  l.VGW02   AS "Standard Value 2",
  l.VGE02   AS "Std Value Unit 2",

  -- Dates
  h.DATUV   AS "Valid From",
  h.ANDAT   AS "Created On",
  h.AEDAT   AS "Last Changed On",

  -- Status / indicators
  h.LOEKZ   AS "Header Deletion Indicator",
  l.LOEKZ   AS "Operation Deletion Indicator",
  h.STATU   AS "Status",
  l.STEUS   AS "Control Key",

  -- Org fields
  h.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  wc.ARBPL  AS "Work Center",

  -- Master data enrichment
  h.PLNME   AS "Routing Unit of Measure",
  h.PLNAW   AS "Application",
  l.ANZMA   AS "Number of Employees"
FROM mapl a
INNER JOIN plko h
  ON  h.MANDT = a.MANDT
  AND h.PLNTY = a.PLNTY
  AND h.PLNNR = a.PLNNR
  AND h.PLNAL = a.PLNAL
INNER JOIN plpo l
  ON  l.MANDT = h.MANDT
  AND l.PLNTY = h.PLNTY
  AND l.PLNNR = h.PLNNR
  AND l.PLNKN = h.PLNKN
LEFT JOIN crhd wc
  ON  wc.MANDT = l.MANDT
  AND wc.OBJID = l.ARBID
LEFT JOIN makt mt
  ON  mt.MANDT = a.MANDT
  AND mt.MATNR = a.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN t001w pt
  ON  pt.MANDT = h.MANDT
  AND pt.WERKS = h.WERKS
WHERE
  a.MANDT = '100'
  AND a.MATNR = '<MATNR>'
  AND h.WERKS = '<WERKS>'
  AND h.LOEKZ = ''
ORDER BY a.MATNR, a.PLNNR, a.PLNAL, l.VORNR;