F4801

-- ============================================================
-- Table  : F4801 Work order header — one row per work order carrying the item being made, branch, quantities ordered/completed/scrapped, status, and the requested/start/completion dates that drive shop floor and maintenance analytics
-- Purpose: Column-selected read of F4801 — auto-generated from field metadata
-- Grain  : One row per WADOCO
-- 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.WADOCO AS "Work order number — the single primary key; join target for F3111 parts list, F3112 routing, and F4801T tag rows",
  f.WADCTO AS "Order type — WO for manufacturing work orders; filter on this plus TYPS to separate manufacturing from maintenance and service orders",
  f.WASFXO AS "Order suffix — part of the standard JDE document trio (DOCO/DCTO/SFXO); usually blank or 000 on work orders",
  f.WARCTO AS "Related order type — order type of the sales order or parent document this work order was generated for",
  f.WARORN AS "Related order number — links a make-to-order WO back to its sales order; stored as an 8-character string, not numeric",
  f.WALNID / POWER(10, 3) AS "Related order line number — pairs with RORN/RCTO to point at the exact sales order line; 3 implied decimals (line 1.000 stored as 1000)",  -- implied decimals: 3 (verify in F9210)
  f.WAPARS AS "Parent work order number — non-blank on child orders in multi-level or ETO work order structures; stored as a string",
  f.WATYPS AS "Work order type — one-character classifier separating manufacturing, equipment/maintenance, and service work orders",
  f.WAPRTS AS "Work order priority code",
  f.WADL01 AS "Work order description — free text, handy as a label in dashboards",
  f.WACO AS "Company the work order belongs to",
  TRIM(f.WAMCU) AS "Charge-to business unit — right-justified 12-character; TRIM before joining or grouping",
  TRIM(f.WAMMCU) AS "Branch/plant where the order is produced — the usual plant dimension for WO analytics; right-justified 12-character",
  f.WASRST AS "Current work order status — the lifecycle field (created, released, on shop floor, complete, closed); status ranges are install-specific",
  f.WAAN8 AS "Address book number tied to the order — customer on make-to-order work, blank on many make-to-stock orders",
  f.WAANP AS "Assigned-to address number — typically the planner or lead responsible for the order",
  CASE WHEN f.WADPL IS NULL OR f.WADPL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WADPL AS INT) DIV 1000, 1, 1), CAST(f.WADPL AS INT) % 1000 - 1) END AS "Planned completion date (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.WATRDJ IS NULL OR f.WATRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WATRDJ AS INT) DIV 1000, 1, 1), CAST(f.WATRDJ AS INT) % 1000 - 1) END AS "Order date — when the work order was entered (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.WASTRT IS NULL OR f.WASTRT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WASTRT AS INT) DIV 1000, 1, 1), CAST(f.WASTRT AS INT) % 1000 - 1) END AS "Planned start date (Julian CYYDDD) — backscheduled from the requested date using routing leadtimes",  -- CYYDDD Julian → DATE
  CASE WHEN f.WADRQJ IS NULL OR f.WADRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WADRQJ AS INT) DIV 1000, 1, 1), CAST(f.WADRQJ AS INT) % 1000 - 1) END AS "Requested date — when the finished quantity is needed (Julian CYYDDD); the anchor for on-time completion metrics",  -- CYYDDD Julian → DATE
  CASE WHEN f.WASTRX IS NULL OR f.WASTRX = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WASTRX AS INT) DIV 1000, 1, 1), CAST(f.WASTRX AS INT) % 1000 - 1) END AS "Actual completion date (Julian CYYDDD) — populated at full completion; compare to DRQJ for schedule attainment",  -- CYYDDD Julian → DATE
  f.WAWR01 AS "Work order category code 01 — commonly used as the phase code; one of ten site-defined WO category codes",
  f.WAAMTO / POWER(10, 2) AS "Estimated total cost of the order; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WAHRSO / POWER(10, 2) AS "Estimated hours for the order; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WAAMTA / POWER(10, 2) AS "Actual total cost charged to the order; 2 implied decimals — compare to AMTO for cost variance",  -- implied decimals: 2 (verify in F9210)
  f.WAHRSA / POWER(10, 2) AS "Actual hours charged to the order; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WAITM AS "Short item number of the parent item being produced — join to F4101/F4102",
  f.WALITM AS "Second (customer-facing) item number of the item being produced",
  f.WAUORG AS "Quantity ordered — how much the work order is supposed to produce",
  f.WASOBK AS "Quantity backordered or held against the order",
  f.WASOCN AS "Quantity canceled or scrapped on the order — pairs with SOQS for yield analysis",
  f.WASOQS AS "Quantity completed to stock — updated by work order completions; UORG minus SOQS minus SOCN gives the open quantity",
  f.WAQTYT AS "Cumulative quantity completed/shipped to date — used mainly in rate/repetitive schedules",
  f.WAUOM AS "Unit of measure the order quantities are expressed in",
  f.WATBM AS "Bill of material type used when the parts list was attached (e.g. M for manufacturing)",
  f.WATRT AS "Routing type used when the routing was attached",
  f.WALOTN AS "Lot/serial number produced by the order — key for lot genealogy in regulated industries",
  f.WANTST AS "Next expected status in the order's workflow",
  f.WAAMLC / POWER(10, 2) AS "Estimated labor cost; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WAAMMC / POWER(10, 2) AS "Estimated material cost; 2 implied decimals"  -- implied decimals: 2 (verify in F9210)
  -- … plus 2 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f4801 f
WHERE
  f.WADOCO = <DOCO>
  -- AND f.WATRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.WATRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.WADOCO;