OOHEAD

-- ============================================================
-- Table  : OOHEAD — The customer order header — one row per order, carrying the customer, order type, dates, and currency, plus the pair of status fields (lowest and highest line status) that summarize where the order's lines stand
-- Purpose: Column-selected read of OOHEAD — auto-generated from field metadata
-- Grain  : One row per company (CONO) + OAORNO
-- Notes  : Auto-generated skeleton for Infor Data Lake-landed M3 data. Dates are numeric YYYYMMDD (0 = none, mapped to NULL); status ladders decoded inline where verified. Audit columns (RGDT/RGTM/LMDT/CHNO/CHID) omitted — see the quirks guide.
-- ============================================================
SELECT
  oh.OACONO AS "Company",
  oh.OAORNO AS "Customer order number — the key order lines join on",
  CASE oh.OAORSL WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (entered)' WHEN '22' THEN 'Not allocated' WHEN '23' THEN 'Partially allocated' WHEN '33' THEN 'Allocated' WHEN '44' THEN 'Picking list printed' WHEN '66' THEN 'Delivered' WHEN '77' THEN 'Invoiced' ELSE oh.OAORSL END AS "Lowest line status on the order — with the highest status, brackets where the order's lines stand",  -- status: ORST
  CASE oh.OAORST WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (entered)' WHEN '22' THEN 'Not allocated' WHEN '23' THEN 'Partially allocated' WHEN '33' THEN 'Allocated' WHEN '44' THEN 'Picking list printed' WHEN '66' THEN 'Delivered' WHEN '77' THEN 'Invoiced' ELSE oh.OAORST END AS "Highest line status on the order",  -- status: ORST
  oh.OAORTP AS "Customer order type — the code that drives the order's process flow",
  oh.OACUNO AS "Customer the order belongs to",
  oh.OACUOR AS "Customer's own order reference",
  CASE WHEN oh.OAORDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(oh.OAORDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Order date",  -- YYYYMMDD, 0 → NULL
  CASE WHEN oh.OARLDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(oh.OARLDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Requested delivery date at the header level",  -- YYYYMMDD, 0 → NULL
  oh.OACUCD AS "Order currency",
  oh.OAFACI AS "Facility the order is processed in",
  oh.OAWHLO AS "Default warehouse for the order's lines"
FROM <catalog>.<schema>.OOHEAD oh
WHERE
  oh.OACONO = <company>
  -- AND oh.OAORNO = '<ORNO>'
  -- AND oh.OAORDT >= <DATE_FROM>  -- yyyyMMdd numeric
  -- AND oh.OAORDT <= <DATE_TO>  -- yyyyMMdd numeric
ORDER BY oh.OAORNO;