MRP_FORECAST_DATES

-- ============================================================
-- Table  : MRP_FORECAST_DATES — The forecast fact — one row per forecast entry per item, org, forecast name, and date, carrying both the entered quantity and the post-consumption remainder
-- Purpose: Column-selected read of MRP_FORECAST_DATES — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + TRANSACTION_ID
-- Caution: A single row spans MANY buckets when RATE_END_DATE is set — expand before summing; and CURRENT_FORECAST_QUANTITY is post-consumption, so summing ORIGINAL double-counts demand already consumed by sales orders
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.TRANSACTION_ID AS "Surrogate key of the forecast entry",
  t.INVENTORY_ITEM_ID AS "The forecast item",
  t.ORGANIZATION_ID AS "Inventory organization",
  t.FORECAST_DESIGNATOR AS "The forecast the entry belongs to — joins with the org",
  t.FORECAST_DATE AS "The bucket date — the analysis date",
  t.RATE_END_DATE AS "NULL for a single bucket — when set, the row SPANS buckets from the forecast date to here; expand before summing",
  t.BUCKET_TYPE AS "Daily, weekly, or periodic bucket",
  t.ORIGINAL_FORECAST_QUANTITY AS "The entered quantity, before consumption",
  t.CURRENT_FORECAST_QUANTITY AS "The post-consumption remainder — original minus current is what sales orders consumed",
  t.CONFIDENCE_PERCENTAGE AS "Confidence attached to the entry",
  t.ORIGINATION_TYPE AS "How the entry was created — manual, load, copy…; numeric, undecoded",
  t.SOURCE_FORECAST_DESIGNATOR AS "The forecast this entry was copied/loaded from",
  t.SOURCE_ORGANIZATION_ID AS "Source org of a copied entry",
  t.END_ITEM_ID AS "The planning-bill parent this entry was exploded from",
  t.FORECAST_RULE_ID AS "The forecast rule that generated statistical entries"
FROM <catalog>.<schema>.MRP_FORECAST_DATES t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.TRANSACTION_ID = <TRANSACTION_ID>
  -- AND t.FORECAST_DATE >= DATE '<DATE_FROM>'
  -- AND t.FORECAST_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.TRANSACTION_ID;