CA02

-- ============================================================
-- T-Code : CA02 Change Routing
-- Purpose: Change an existing routing
-- Grain  : One row per routing operation (PLKO header + PLPO operation)
-- Tables : MAPL, PLKO, PLAS, PLPO, MAKT, CRHD, T001W
-- Notes  : MAPL is the plant-level routing assignment (material → PLNNR group + PLNAL alternative). PLKO has no PLNKN column, so operations are reached via the PLAS bridge (PLKO → PLAS on PLNTY/PLNNR/PLNAL, then PLAS → PLPO on PLNTY/PLNNR/PLNKN). 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.VERWE   AS "Task List Usage",
  l.ANZMA   AS "Number of Employees"
FROM <catalog>.<schema>.mapl a
INNER JOIN <catalog>.<schema>.plko h
  ON  h.MANDT = a.MANDT
  AND h.PLNTY = a.PLNTY
  AND h.PLNNR = a.PLNNR
  AND h.PLNAL = a.PLNAL
INNER JOIN <catalog>.<schema>.plas br
  ON  br.MANDT = h.MANDT
  AND br.PLNTY = h.PLNTY
  AND br.PLNNR = h.PLNNR
  AND br.PLNAL = h.PLNAL
INNER JOIN <catalog>.<schema>.plpo l
  ON  l.MANDT = br.MANDT
  AND l.PLNTY = br.PLNTY
  AND l.PLNNR = br.PLNNR
  AND l.PLNKN = br.PLNKN
LEFT JOIN <catalog>.<schema>.crhd wc
  ON  wc.MANDT = l.MANDT
  AND wc.OBJID = l.ARBID
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = a.MANDT
  AND mt.MATNR = a.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = h.MANDT
  AND pt.WERKS = h.WERKS
WHERE
  a.MANDT = '<MANDT>'
  AND a.MATNR = '<MATNR>'
  AND h.WERKS = '<WERKS>'
  AND h.LOEKZ = ''
ORDER BY a.MATNR, a.PLNNR, a.PLNAL, l.VORNR;