F3003

-- ============================================================
-- Table  : F3003 One row per operation step in an item's routing at a branch/plant, effective-dated, carrying the work center, time standards, and yield percentages that drive scheduling and labor costing.
-- Purpose: Column-selected read of F3003 — auto-generated from field metadata
-- Grain  : One row per IRTRT + IRKIT + IRMMCU + IRLINE + IROPSQ + IREFFF + IRBQTY + IROPSC
-- 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.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.",
  f.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 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.",
  f.IROPSC AS "Operation type code (e.g. standard vs. outside operation); part of the unique key.",
  f.IRKITL AS "Parent item's 25-character second item number.",
  f.IRDSC1 AS "Free-text description of the operation step.",
  TRIM(f.IRMCU) AS "Work center where the operation is performed — joins to F30006.",
  CASE WHEN f.IREFFT IS NULL OR f.IREFFT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IREFFT AS INT) DIV 1000, 1, 1), CAST(f.IREFFT AS INT) % 1000 - 1) END AS "Date the operation expires, stored as Julian CYYDDD.",  -- CYYDDD Julian → DATE
  f.IRBFPF AS "Pay point code controlling whether material/labor is backflushed at this operation.",
  f.IRLAMC AS "Flags the operation as labor-based or machine-based for capacity and costing.",
  f.IRTIMB AS "Time basis code stating what quantity the run hours are expressed per (per unit, per 1000, etc.) — required to convert standards into hours.",
  f.IRUOM AS "Unit of measure the batch quantity was entered in.",
  f.IRRUNM / POWER(10, 2) AS "Standard machine run hours per time basis quantity.",  -- implied decimals: 2 (verify in F9210)
  f.IRRUNL / POWER(10, 2) AS "Standard labor run hours per time basis quantity.",  -- implied decimals: 2 (verify in F9210)
  f.IRSETL / POWER(10, 2) AS "Standard setup labor hours for the operation, incurred once per order.",  -- implied decimals: 2 (verify in F9210)
  f.IRSETC / POWER(10, 1) AS "Crew size — number of people working the operation simultaneously; multiplies labor hours.",  -- implied decimals: 1 (verify in F9210)
  f.IRMOVD / POWER(10, 2) AS "Standard move hours to transfer work to the next operation.",  -- implied decimals: 2 (verify in F9210)
  f.IRQUED / POWER(10, 2) AS "Standard queue hours waiting at the work center before the operation starts.",  -- implied decimals: 2 (verify in F9210)
  f.IRNXOP / POWER(10, 2) AS "Sequence number of the next operation, supporting non-linear routings.",  -- implied decimals: 2 (verify in F9210)
  f.IROPYP / POWER(10, 2) AS "Planned yield percent of this single operation.",  -- implied decimals: 2 (verify in F9210)
  f.IRCPYP / POWER(10, 2) AS "Cumulative planned yield percent from this operation through the end of the routing.",  -- implied decimals: 2 (verify in F9210)
  f.IRVEND AS "Supplier address number for outside (subcontracted) operations.",
  f.IRPOY AS "Flag indicating the operation generates a purchase order (outside processing).",
  f.IRCOST AS "Cost component (e.g. B1 labor, B2 setup) this operation's cost rolls into — same code set as F3102/F30026.",
  f.IRRREV AS "Revision level of the routing this operation belongs to.",
  TRIM(f.IRWMCU) AS "Branch/plant of the work center when it differs from the routing branch."
FROM <catalog>.<schema_data>.f3003 f
ORDER BY f.IRTRT;