CR02

-- ============================================================
-- T-Code : CR02 Change Work Center
-- Purpose: Change work center attributes
-- Grain  : One row per work center / plant / valid-period
-- Tables : CRHD, CRTX, CRCA, KAKO, T001W
-- Notes  : CRTX provides the work-center description and must be filtered by SPRAS. CRCA carries capacity assignments (one row per work-center × capacity category); capacity category (KAPAR) lives on KAKO, joined via KAPID. Cost-center assignment lives in CRCO, not CRHD.
-- ============================================================
SELECT
  -- Keys
  wc.MANDT  AS "Client",
  wc.OBJID  AS "Internal Object ID",
  wc.ARBPL  AS "Work Center",
  wc.WERKS  AS "Plant",

  -- Descriptive text
  ct.KTEXT  AS "Work Center Description",

  -- Dates
  wc.BEGDA  AS "Valid From",
  wc.ENDDA  AS "Valid To",

  -- Status / indicators
  wc.LOANZ  AS "Number of People",
  wc.PLANV  AS "Planner Group",
  wc.VGWTS  AS "Standard-Value Key",
  wc.STAND  AS "Standard Value Key",

  -- Org fields
  pt.NAME1  AS "Plant Name",
  wc.VERWE  AS "Work Center Category",

  -- Master data enrichment
  wc.VERAN  AS "Person Responsible",
  ca.KAPID  AS "Capacity ID",
  k.KAPAR   AS "Capacity Category"
FROM <catalog>.<schema>.crhd wc
LEFT JOIN <catalog>.<schema>.crtx ct
  ON  ct.MANDT = wc.MANDT
  AND ct.OBJTY = wc.OBJTY
  AND ct.OBJID = wc.OBJID
  AND ct.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.crca ca
  ON  ca.MANDT = wc.MANDT
  AND ca.OBJTY = wc.OBJTY
  AND ca.OBJID = wc.OBJID
LEFT JOIN <catalog>.<schema>.kako k
  ON  k.MANDT = ca.MANDT
  AND k.KAPID = ca.KAPID
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = wc.MANDT
  AND pt.WERKS = wc.WERKS
WHERE
  wc.MANDT = '<MANDT>'
  AND wc.WERKS = '<WERKS>'
  -- AND wc.ARBPL = '<ARBPL>'
ORDER BY wc.WERKS, wc.ARBPL;