R3483

-- ============================================================
-- Program: R3483 Master Planning Schedule - Multiple Plant — the batch job that runs multifacility requirements planning, generating transfer-order messages and time series across a network of branch/plants.
-- Purpose: Master Planning Schedule - Multiple Plant — the batch job that runs multifacility requirements planning, generating transfer-order messages and time series across a network of branch/plants. — auto-generated boilerplate from program-table-map
-- Grain  : F3411 × F3413, F3412, F3460, F4102, F4211 — 1:N joins yield one row per detail line
-- Tables : F3411, F3413, F3412, F3460, F4102, F4211
-- 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.MMUKID AS "Internal unique key — single-column primary key with no business meaning",
  h.MMDSC1 AS "Message text shown to the planner",
  f.MRITM AS "Short item number — part of the primary key",
  TRIM(f.MRMCU) AS "Branch/plant — part of the primary key; right-justified 12-character",
  f.MRQT AS "Quantity type — the row dimension of the time series (forecast, gross requirements, planned orders, projected available, ATP, ...); part of the primary key",
  CASE WHEN f.MRSTRT IS NULL OR f.MRSTRT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MRSTRT AS INT) DIV 1000, 1, 1), CAST(f.MRSTRT AS INT) % 1000 - 1) END AS "Bucket start date (Julian CYYDDD) — part of the primary key",  -- CYYDDD Julian → DATE
  f34.MFITM AS "Short item number the forecast is for.",
  TRIM(f34.MFMCU) AS "Branch/plant the forecast applies to.",
  CASE WHEN f34.MFDRQJ IS NULL OR f34.MFDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f34.MFDRQJ AS INT) DIV 1000, 1, 1), CAST(f34.MFDRQJ AS INT) % 1000 - 1) END AS "Forecast (requested) date bucket, stored as Julian CYYDDD.",  -- CYYDDD Julian → DATE
  f34.MFAN8 AS "Customer address number for customer-level forecasts; zero on item/branch-level rows.",
  f34.MFTYPF AS "Forecast type distinguishing generated statistical forecasts (e.g. BF) from manual/adjusted ones (e.g. AA).",
  f34.MFYR AS "Two-digit calendar year of the forecast bucket; part of the unique key.",
  ib.IBITM AS "Short item number - the internal numeric item key",
  TRIM(ib.IBMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  d.SDKCOO AS "Order key company - disambiguates order numbers across companies",
  d.SDDOCO AS "Order number",
  d.SDDCTO AS "Order type code (SO, ST, CO, ...)",
  d.SDLNID / POWER(10, 3) AS "Order line number, stored x1000 (3 implied decimals)"  -- implied decimals: 3 (verify in F9210)
FROM <catalog>.<schema_data>.f3411 h
LEFT JOIN <catalog>.<schema_data>.f3413 f
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f3412 f3
  ON f3.MWUKID = h.MMUKID
LEFT JOIN <catalog>.<schema_data>.f3460 f34
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f4211 d
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
ORDER BY h.MMUKID;