ForecastSales

-- ============================================================
-- Table  : ForecastSales Demand (sales) forecast lines — one row per forecast model, item, dimension set, and period start, carrying forecast quantity, price, and amount; the demand input the master plan consumes
-- Purpose: Column-selected read of ForecastSales — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + ModelId + ItemId
-- Notes  : Auto-generated skeleton for Synapse Link / Fabric Link-landed F&O data (lowercase column names). Enums decoded inline where verified; datetimes stored in UTC; 1900-01-01 dates are sentinels mapped to NULL. System/audit columns omitted — see the quirks guide.
-- ============================================================
SELECT
  f.modelid AS "Forecast model the line belongs to — filter to the active model before aggregating, or overlapping scenarios double-count",
  f.itemid AS "Item the forecast is for",
  f.startdate AS "Start of the forecast period — the date the plan consumes the quantity on; the primary analysis date",
  CASE WHEN CAST(f.enddate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(f.enddate AS DATE) END AS "Optional end of the forecast period; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  f.salesqty AS "Forecast quantity in the sales unit",
  f.salesprice AS "Forecast unit price",
  f.amount AS "Forecast amount for the line in the line currency",
  f.currency AS "Currency of the forecast amount",
  f.inventdimid AS "Inventory-dimension combination the forecast is placed at (typically site/warehouse); join to InventDim to resolve",
  f.custaccountid AS "Customer the forecast is placed against, when forecasting by customer",
  f.custgroupid AS "Customer group the forecast is placed against, when forecasting by group",
  f.itemgroupid AS "Item group of the forecast line",
  f.salesunitid AS "Sales unit the forecast quantity is expressed in",
  f.active AS "Whether the forecast line is active and included in planning",  -- enum: decode f.active via GlobalOptionsetMetadata join — see quirks guide #enums
  f.defaultdimension AS "RecId of the line's financial-dimension value set in DimensionAttributeValueSet"
FROM <catalog>.<schema>.forecastsales f
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = f.inventdimid AND id.dataareaid = f.dataareaid
-- Resolve DimensionAttributeValueSet (via DefaultDimension): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.dimensionattributevalueset dim ON dim.recid = f.defaultdimension
WHERE
  f.dataareaid = '<company>'
  -- AND f.itemid = '<ItemId>'
  -- AND f.startdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND f.startdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY f.modelid;