R31410

-- ============================================================
-- Program: R31410 Order Processing — the batch job that attaches the parts list and routing to work orders, commits inventory, and prints shop floor paperwork in one pass.
-- Purpose: Order Processing — the batch job that attaches the parts list and routing to work orders, commits inventory, and prints shop floor paperwork in one pass. — auto-generated boilerplate from program-table-map
-- Grain  : F4801 × F3111, F3112, F3002, F3003, F41021 — 1:N joins yield one row per detail line
-- Tables : F4801, F3111, F3112, F3002, F3003, F41021
-- 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.WADOCO AS "Work order number — the single primary key; join target for F3111 parts list, F3112 routing, and F4801T tag rows",
  h.WADL01 AS "Work order description — free text, handy as a label in dashboards",
  f.WMUKID AS "Internal unique key — the table's single-column primary key; carries no business meaning",
  TRIM(f3.WLMCU) AS "Work center business unit performing the operation — part of the composite primary key; right-justified 12-character",
  f3.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)
  f3.WLOPSC AS "Operation type code — part of the primary key; distinguishes duplicate sequence numbers",
  f30.IXTBM AS "Bill type — M is the standard manufacturing bill; other UDC values cover rework, spare parts, and planning bills.",
  f30.IXKIT AS "Short item number of the parent being built — the top of this BOM line.",
  TRIM(f30.IXMMCU) AS "Branch/plant that owns this bill — BOMs are branch-specific in JDE.",
  f30.IXSBNT / POWER(10, 2) AS "Sequence that orders substitute items for a component; 0 for the primary component line.",  -- implied decimals: 2 (verify in F9210)
  f30.IXBQTY AS "Batch quantity of the parent that the quantity-per is stated against; part of the key, so one item can carry separate bills per batch size — implied decimals vary by setup.",
  f30.IXCOBY AS "Classifies the line as a component, co-product, by-product, or intermediate.",
  f30.IXCPNB / POWER(10, 2) AS "System-assigned BOM component line number that uniquifies lines within a parent/branch/type/batch key.",  -- implied decimals: 2 (verify in F9210)
  f300.IRTRT AS "Routing type — M is the standard manufacturing routing; alternates cover rework, rate schedules, and outside operations.",
  f300.IRKIT AS "Short item number of the parent item this routing builds.",
  TRIM(f300.IRMMCU) AS "Branch/plant that owns this routing.",
  f300.IRLINE AS "Production line or cell identifier, used in repetitive/lean manufacturing; blank in discrete routings.",
  f300.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 f300.IREFFF IS NULL OR f300.IREFFF = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f300.IREFFF AS INT) DIV 1000, 1, 1), CAST(f300.IREFFF AS INT) % 1000 - 1) END AS "Date the operation becomes effective, stored as Julian CYYDDD; part of the unique key.",  -- CYYDDD Julian → DATE
  f300.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.",
  f300.IROPSC AS "Operation type code (e.g. standard vs. outside operation); part of the unique key.",
  loc.LIITM AS "Short item number - the internal numeric item key",
  TRIM(loc.LIMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  loc.LILOCN AS "Storage location within the branch plant",
  loc.LILOTN AS "Lot or serial number"
FROM <catalog>.<schema_data>.f4801 h
LEFT JOIN <catalog>.<schema_data>.f3111 f
  ON f.WMDOCO = h.WADOCO
LEFT JOIN <catalog>.<schema_data>.f3112 f3
  ON f3.WLDOCO = h.WADOCO
LEFT JOIN <catalog>.<schema_data>.f3002 f30
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f3003 f300
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f41021 loc
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
WHERE
  h.WADOCO = <DOCO>
ORDER BY h.WADOCO;