P3003

-- ============================================================
-- Program: P3003 Enter/Change Routing — the screen where manufacturing engineering defines the standard routing for an item: operations, work centers, and labor/machine time standards.
-- Purpose: Enter/Change Routing — the screen where manufacturing engineering defines the standard routing for an item: operations, work centers, and labor/machine time standards. — auto-generated boilerplate from program-table-map
-- Grain  : F3003 × F30006, F4102 — 1:N joins yield one row per detail line
-- Tables : F3003, F30006, F4102
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  h.IRTRT AS "Routing type — M is the standard manufacturing routing; alternates cover rework, rate schedules, and outside operations.",
  h.IRKIT AS "Short item number of the parent item this routing builds.",
  TRIM(h.IRMMCU) AS "Branch/plant that owns this routing.",
  h.IRLINE AS "Production line or cell identifier, used in repetitive/lean manufacturing; blank in discrete routings.",
  h.IROPSQ / POWER(10, 2) AS "Operation sequence number ordering the steps of the routing; matches the OPSQ on F3002 component lines and F3102 cost rows.",  -- implied decimals: 2 (verify in F9210)
  CASE WHEN h.IREFFF IS NULL OR h.IREFFF = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.IREFFF AS INT) DIV 1000, 1, 1), CAST(h.IREFFF AS INT) % 1000 - 1) END AS "Date the operation becomes effective, stored as Julian CYYDDD; part of the unique key.",  -- CYYDDD Julian → DATE
  h.IRBQTY AS "Batch quantity this routing version applies to; part of the key so one item can carry batch-size-specific routings — implied decimals vary by setup.",
  h.IROPSC AS "Operation type code (e.g. standard vs. outside operation); part of the unique key.",
  h.IRDSC1 AS "Free-text description of the operation step.",
  TRIM(f.IWMCU) AS "Work center identifier — itself a business unit in the F0006 sense.",
  ib.IBITM AS "Short item number - the internal numeric item key",
  TRIM(ib.IBMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)"
FROM <catalog>.<schema_data>.f3003 h
LEFT JOIN <catalog>.<schema_data>.f30006 f
  ON f.IWMMCU = h.IRMMCU
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
ORDER BY h.IRTRT;