F3111

-- ============================================================
-- Table  : F3111 Work order parts list — one row per component required by a work order, with required vs. issued quantities, scrap, commitments, and the operation each part feeds
-- Purpose: Column-selected read of F3111 — auto-generated from field metadata
-- Grain  : One row per WMUKID
-- 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.WMUKID AS "Internal unique key — the table's single-column primary key; carries no business meaning",
  f.WMDOCO AS "Work order number — join to F4801",
  f.WMDCTO AS "Work order type",
  f.WMSFXO AS "Order suffix — completes the document trio with DOCO/DCTO",
  f.WMTBM AS "Bill of material type the parts list was copied from",
  f.WMFORQ AS "Fixed or variable quantity — whether the required quantity scales with order size",
  f.WMITC AS "Issue type code — manual issue, backflush, floor stock, etc.; determines how TRQT gets populated",
  f.WMCOBY AS "Co-/by-product/intermediate flag — distinguishes consumed components from produced outputs on process orders",
  f.WMCPNT / POWER(10, 1) AS "Component line number on the parts list; 1 implied decimal",  -- implied decimals: 1 (verify in F9210)
  f.WMOPSQ / POWER(10, 2) AS "Operation sequence the component is consumed at — links the part to an F3112 routing step; 2 implied decimals (op 10 stored as 1000)",  -- implied decimals: 2 (verify in F9210)
  f.WMSCRP / POWER(10, 2) AS "Scrap percent built into the required quantity; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WMLOVD AS "Leadtime offset days — shifts the component's requested date relative to the order start",
  f.WMCPIT AS "Component short item number — join to F4101/F4102",
  f.WMCPIL AS "Component second item number (customer-facing)",
  TRIM(f.WMCMCU) AS "Component branch/plant the part is issued from — right-justified 12-character",
  f.WMLOCN AS "Warehouse location the component is committed against",
  f.WMLOTN AS "Component lot/serial number — one side of lot genealogy (consumed lot feeding the produced lot on F4801)",
  CASE WHEN f.WMTRDJ IS NULL OR f.WMTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WMTRDJ AS INT) DIV 1000, 1, 1), CAST(f.WMTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.WMDRQJ IS NULL OR f.WMDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WMDRQJ AS INT) DIV 1000, 1, 1), CAST(f.WMDRQJ AS INT) % 1000 - 1) END AS "Date the component is required (Julian CYYDDD) — start date plus leadtime offset",  -- CYYDDD Julian → DATE
  f.WMUORG AS "Required quantity — how much of the component the order needs",
  f.WMTRQT AS "Quantity issued to the order so far — UORG minus TRQT is the open requirement; erpref labels it 'Quantity Available' but issue programs write issued quantity here",
  f.WMSOCN AS "Component quantity canceled or scrapped",
  f.WMSOBK AS "Component quantity backordered/held — shortage signal for material availability dashboards",
  f.WMQNTA AS "Quantity committed against inventory (hard or soft per COMM flag)",
  f.WMUM AS "Unit of measure for the component quantities",
  f.WMEA / POWER(10, 2) AS "Estimated cost of the component requirement; 2 implied decimals",  -- implied decimals: 2 (verify in F9210)
  f.WMMTST AS "Material status of the line (e.g. available, shortage, issued) — shop-floor material readiness code",
  TRIM(f.WMMCU) AS "Business unit charged for the component — right-justified 12-character",
  CASE WHEN f.WMSTRX IS NULL OR f.WMSTRX = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WMSTRX AS INT) DIV 1000, 1, 1), CAST(f.WMSTRX AS INT) % 1000 - 1) END AS "Completion date carried from the order (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  f.WMCOMM AS "Commitment flag — hard or soft commitment of the component quantity",
  f.WMVEND AS "Supplier address number — set on supplier-provided or outside-processed components",
  CASE WHEN f.WMGLD IS NULL OR f.WMGLD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.WMGLD AS INT) DIV 1000, 1, 1), CAST(f.WMGLD AS INT) % 1000 - 1) END AS "G/L date of the last inventory issue against the line (Julian CYYDDD)"  -- CYYDDD Julian → DATE
FROM <catalog>.<schema_data>.f3111 f
WHERE
  f.WMTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.WMTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.WMUKID;