P3112

-- ============================================================
-- Program: P3112 Work Order Routing — the screen for revising a work order's routing steps: work centers, operation sequences, run hours, and outside operations.
-- Purpose: Work Order Routing — the screen for revising a work order's routing steps: work centers, operation sequences, run hours, and outside operations. — auto-generated boilerplate from program-table-map
-- Grain  : F3112 × F3003, F30006, F4801 — 1:N joins yield one row per detail line
-- Tables : F3112, F3003, F30006, F4801
-- 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.WLDOCO AS "Work order number — join to F4801; part of the composite primary key",
  TRIM(h.WLMCU) AS "Work center business unit performing the operation — part of the composite primary key; right-justified 12-character",
  h.WLOPSQ / POWER(10, 2) AS "Operation sequence number — orders the routing steps and is the join target for F3111 components; part of the primary key; 2 implied decimals (op 10 stored as 1000)",  -- implied decimals: 2 (verify in F9210)
  h.WLOPSC AS "Operation type code — part of the primary key; distinguishes duplicate sequence numbers",
  h.WLDSC1 AS "Operation description",
  f.IRTRT AS "Routing type — M is the standard manufacturing routing; alternates cover rework, rate schedules, and outside operations.",
  f.IRKIT AS "Short item number of the parent item this routing builds.",
  TRIM(f.IRMMCU) AS "Branch/plant that owns this routing.",
  f.IRLINE AS "Production line or cell identifier, used in repetitive/lean manufacturing; blank in discrete routings.",
  CASE WHEN f.IREFFF IS NULL OR f.IREFFF = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IREFFF AS INT) DIV 1000, 1, 1), CAST(f.IREFFF AS INT) % 1000 - 1) END AS "Date the operation becomes effective, stored as Julian CYYDDD; part of the unique key.",  -- CYYDDD Julian → DATE
  f.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.",
  TRIM(f3.IWMMCU) AS "Branch/plant the work center belongs to."
FROM <catalog>.<schema_data>.f3112 h
LEFT JOIN <catalog>.<schema_data>.f3003 f
  ON f.IROPSQ = h.WLOPSQ
 AND f.IROPSC = h.WLOPSC
LEFT JOIN <catalog>.<schema_data>.f30006 f3
  ON f3.IWMCU = h.WLMCU
LEFT JOIN <catalog>.<schema_data>.f4801 f4
  ON f4.WADOCO = h.WLDOCO
WHERE
  h.WLDOCO = <DOCO>
  AND h.WLMCU = '<MCU>'
ORDER BY h.WLDOCO;