R34650

-- ============================================================
-- Program: R34650 Forecast Generation — the batch job that builds statistical demand forecasts from sales history, testing up to twelve forecasting methods and keeping the best fit per item.
-- Purpose: Forecast Generation — the batch job that builds statistical demand forecasts from sales history, testing up to twelve forecasting methods and keeping the best fit per item. — auto-generated boilerplate from program-table-map
-- Grain  : F3460 × F42119, F4101, F4102 — 1:N joins yield one row per detail line
-- Tables : F3460, F42119, F4101, F4102
-- 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.MFITM AS "Short item number the forecast is for.",
  TRIM(h.MFMCU) AS "Branch/plant the forecast applies to.",
  CASE WHEN h.MFDRQJ IS NULL OR h.MFDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.MFDRQJ AS INT) DIV 1000, 1, 1), CAST(h.MFDRQJ AS INT) % 1000 - 1) END AS "Forecast (requested) date bucket, stored as Julian CYYDDD.",  -- CYYDDD Julian → DATE
  h.MFAN8 AS "Customer address number for customer-level forecasts; zero on item/branch-level rows.",
  h.MFTYPF AS "Forecast type distinguishing generated statistical forecasts (e.g. BF) from manual/adjusted ones (e.g. AA).",
  h.MFYR AS "Two-digit calendar year of the forecast bucket; part of the unique key.",
  dh.SDKCOO AS "Order key company - disambiguates order numbers across companies",
  dh.SDDOCO AS "Order number",
  dh.SDDCTO AS "Order type code (SO, ST, CO, ...)",
  dh.SDLNID / POWER(10, 3) AS "Order line number, stored x1000 (3 implied decimals)"  -- implied decimals: 3 (verify in F9210)
FROM <catalog>.<schema_data>.f3460 h
LEFT JOIN <catalog>.<schema_data>.f42119 dh
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f4101 im
  ON im.IMITM = h.MFITM
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON ib.IBITM = h.MFITM
 AND ib.IBMCU = h.MFMCU
WHERE
  h.MFITM = <ITM>
  AND h.MFMCU = '<MCU>'
  AND h.MFAN8 = <AN8>
ORDER BY h.MFITM;