F3460

-- ============================================================
-- Table  : F3460 One row per forecast quantity/amount — keyed by item, branch, forecast type, requested date, year, and customer — holding the demand forecasts (both generated and adjusted) that feed MRP/DRP.
-- Purpose: Column-selected read of F3460 — auto-generated from field metadata
-- Grain  : One row per MFITM + MFMCU + MFDRQJ + MFAN8 + MFTYPF + MFYR
-- 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.MFITM AS "Short item number the forecast is for.",
  TRIM(f.MFMCU) AS "Branch/plant the forecast applies to.",
  CASE WHEN f.MFDRQJ IS NULL OR f.MFDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.MFDRQJ AS INT) DIV 1000, 1, 1), CAST(f.MFDRQJ AS INT) % 1000 - 1) END AS "Forecast (requested) date bucket, stored as Julian CYYDDD.",  -- CYYDDD Julian → DATE
  f.MFAN8 AS "Customer address number for customer-level forecasts; zero on item/branch-level rows.",
  f.MFTYPF AS "Forecast type distinguishing generated statistical forecasts (e.g. BF) from manual/adjusted ones (e.g. AA).",
  f.MFYR AS "Two-digit calendar year of the forecast bucket; part of the unique key.",
  f.MFLITM AS "25-character second item number for the same item.",
  f.MFAITM AS "Third item number for the same item.",
  f.MFUORG AS "Original/transaction quantity captured with the forecast; implied decimals vary by setup.",
  f.MFAEXP / POWER(10, 2) AS "Extended price associated with the forecast quantity.",  -- implied decimals: 2 (verify in F9210)
  f.MFFAM / POWER(10, 2) AS "Forecast amount in currency for the bucket.",  -- implied decimals: 2 (verify in F9210)
  f.MFFQT AS "Forecast quantity for the bucket — the number MRP consumes; implied decimals vary by setup.",
  f.MFDCTO AS "Order type code attached to the forecast record.",
  f.MFBPFC AS "Bypass-forcing flag — excludes this row when lower-level forecast changes are forced up a hierarchy.",
  f.MFRVIS AS "Marks the forecast row as manually revised, protecting it from regeneration."
FROM <catalog>.<schema_data>.f3460 f
WHERE
  f.MFITM = <ITM>
  -- AND f.MFMCU = '<MCU>'
  -- AND f.MFAN8 = <AN8>
  -- AND f.MFDRQJ >= <DRQJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.MFDRQJ <= <DRQJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.MFITM;