F3411

-- ============================================================
-- Table  : F3411 MRP action messages — one row per recommendation the planning run generates (order, expedite, defer, cancel, increase, decrease) for an item/branch; planners work this queue after each run
-- Purpose: Column-selected read of F3411 — auto-generated from field metadata
-- Grain  : One row per MMUKID
-- 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.MMUKID AS "Internal unique key — single-column primary key with no business meaning",
  f.MMITM AS "Short item number the message applies to",
  TRIM(f.MMMCU) AS "Branch/plant the message was generated for — right-justified 12-character",
  TRIM(f.MMMMCU) AS "Supplying/demand branch on transfer-type messages — right-justified 12-character",
  f.MMMSGT AS "Message type — the recommended action (order, expedite, defer, cancel, increase, decrease); the main slicer for message analytics",
  f.MMMSGA AS "Message action/control flag — whether the message has been processed, held, or cleared",
  f.MMHCLD AS "Hold code on the message",
  f.MMKCOO AS "Order company of the referenced order",
  f.MMDOCO AS "Order number the message refers to — the WO or PO to expedite/defer/cancel; blank on new-order messages until processed",
  f.MMDCTO AS "Order type of the referenced order",
  f.MMLNID / POWER(10, 3) AS "Line number of the referenced order line; 3 implied decimals",  -- implied decimals: 3 (verify in F9210)
  f.MMDSC1 AS "Message text shown to the planner",
  f.MMTRQT AS "Quantity the message recommends ordering or changing",
  f.MMVEND AS "Suggested supplier for purchase-type messages",
  CASE WHEN f.MMDRQJ IS NULL OR f.MMDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MMDRQJ AS INT) DIV 1000, 1, 1), CAST(f.MMDRQJ AS INT) % 1000 - 1) END AS "Requested/need date of the recommendation (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.MMSTRT IS NULL OR f.MMSTRT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MMSTRT AS INT) DIV 1000, 1, 1), CAST(f.MMSTRT AS INT) % 1000 - 1) END AS "Start date of the current order the message refers to (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.MMRSTJ IS NULL OR f.MMRSTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MMRSTJ AS INT) DIV 1000, 1, 1), CAST(f.MMRSTJ AS INT) % 1000 - 1) END AS "Recommended start date (Julian CYYDDD) — what the plan wants the order moved to",  -- CYYDDD Julian → DATE
  CASE WHEN f.MMRRQJ IS NULL OR f.MMRRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MMRRQJ AS INT) DIV 1000, 1, 1), CAST(f.MMRRQJ AS INT) % 1000 - 1) END AS "Recommended completion date (Julian CYYDDD) — difference vs. the order's current date quantifies expedite/defer magnitude",  -- CYYDDD Julian → DATE
  CASE WHEN f.MMREDJ IS NULL OR f.MMREDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MMREDJ AS INT) DIV 1000, 1, 1), CAST(f.MMREDJ AS INT) % 1000 - 1) END AS "Recommended effective date (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  CASE WHEN f.MMOEDJ IS NULL OR f.MMOEDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MMOEDJ AS INT) DIV 1000, 1, 1), CAST(f.MMOEDJ AS INT) % 1000 - 1) END AS "Original effective date of the order being changed (Julian CYYDDD)",  -- CYYDDD Julian → DATE
  f.MMLINE AS "Production line/cell for rate-based messages",
  f.MMLOTN AS "Lot/serial number when the message is lot-specific",
  f.MMPNS AS "Production number short — ties the message to production-number-controlled demand",
  f.MMPMPN AS "Production number (long form)"
FROM <catalog>.<schema_data>.f3411 f
WHERE
  f.MMDRQJ >= <DRQJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.MMDRQJ <= <DRQJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.MMUKID;