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 × F4101, F4102 — 1:N joins yield one row per detail line
-- Tables : F3460, 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.
-- Omitted: F42119 is cataloged for this program, but no verified join condition connects it to F3460, so it is left out of this skeleton. Add the join yourself using the key fields on each table's page.
-- ============================================================
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."
FROM <catalog>.<schema_data>.f3460 h
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;