F3112

-- ============================================================
-- Table  : F3112 Work order routing — one row per operation on a work order, carrying the work center, standard vs. actual machine/labor/setup hours, and quantities completed or scrapped at each step
-- Purpose: Column-selected read of F3112 — auto-generated from field metadata
-- Grain  : One row per WLDOCO + WLMCU + WLOPSQ + WLOPSC
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. 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
  f.WLDOCO AS "Work order number — join to F4801; part of the composite primary key",
  TRIM(f.WLMCU) AS "Work center business unit performing the operation — part of the composite primary key; right-justified 12-character",
  f.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)
  f.WLOPSC AS "Operation type code — part of the primary key; distinguishes duplicate sequence numbers",
  f.WLDCTO AS "Work order type",
  f.WLSFXO AS "Order suffix",
  f.WLTRT AS "Routing type the operations were copied from",
  f.WLKIT AS "Short item number of the parent item being produced",
  TRIM(f.WLMMCU) AS "Branch/plant of the work order — right-justified 12-character",
  f.WLLINE AS "Production line or cell for the operation",
  f.WLDSC1 AS "Operation description",
  f.WLOPST AS "Operation status — tracks progress step by step through the routing",
  f.WLTIMB AS "Time basis code — the per-unit basis (per 1, per 1,000, etc.) that standard hours are expressed in; critical when computing expected hours",
  f.WLLAMC AS "Labor-or-machine flag for the operation's primary resource",
  f.WLAN8 AS "Address number — supplier on outside-processing operations",
  CASE WHEN f.WLTRDJ IS NULL OR f.WLTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WLTRDJ AS INT) DIV 1000, 1, 1), CAST(f.WLTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.WLDRQJ IS NULL OR f.WLDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WLDRQJ AS INT) DIV 1000, 1, 1), CAST(f.WLDRQJ AS INT) % 1000 - 1) END AS "Requested date for the operation (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.WLSTRT IS NULL OR f.WLSTRT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WLSTRT AS INT) DIV 1000, 1, 1), CAST(f.WLSTRT AS INT) % 1000 - 1) END AS "Scheduled start date of the operation (Julian CYYDDD) — the basis for work center load by day",  -- CYYDDD Julian → DATE
  CASE WHEN f.WLSTRX IS NULL OR f.WLSTRX = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WLSTRX AS INT) DIV 1000, 1, 1), CAST(f.WLSTRX AS INT) % 1000 - 1) END AS "Scheduled completion date of the operation (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  f.WLNXOP / POWER(10, 2) AS "Next operation sequence; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WLSETC / POWER(10, 1) AS "Crew size at the operation; 1 implied decimal",  -- implied decimals: 1 (verify in F9210)
  f.WLMOVD / POWER(10, 2) AS "Standard move hours to the next operation; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WLQUED / POWER(10, 2) AS "Standard queue hours before the operation; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WLRUNM / POWER(10, 2) AS "Standard machine run hours; 2 implied decimals — interpret with the time basis code",  -- implied decimals: 2 (verify in F9210)
  f.WLRUNL / POWER(10, 2) AS "Standard labor run hours; 2 implied decimals — interpret with the time basis code",  -- implied decimals: 2 (verify in F9210)
  f.WLSETL / POWER(10, 2) AS "Standard setup labor hours; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WLMACA / POWER(10, 2) AS "Actual machine hours reported; 2 implied decimals — vs. RUNM for machine efficiency",  -- implied decimals: 2 (verify in F9210)
  f.WLLABA / POWER(10, 2) AS "Actual labor run hours reported; 2 implied decimals — vs. RUNL for labor efficiency",  -- implied decimals: 2 (verify in F9210)
  f.WLSETA / POWER(10, 2) AS "Actual setup labor hours reported; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WLUORG AS "Order quantity scheduled through the operation",
  f.WLSOCN AS "Quantity scrapped at the operation — sums to first-pass-yield analysis by work center",
  f.WLSOQS AS "Quantity completed through the operation",
  f.WLQMTO AS "Quantity currently sitting at the operation (WIP at the step)",
  f.WLUOM AS "Unit of measure for operation quantities",
  f.WLVEND AS "Supplier address number for outside-processing operations",
  TRIM(f.WLWMCU) AS "Branch/plant the work center belongs to — right-justified 12-character"
FROM <catalog>.<schema_data>.f3112 f
WHERE
  f.WLDOCO = <DOCO>
  -- AND f.WLMCU = '<MCU>'
  -- AND f.WLTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.WLTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.WLDOCO;