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 a prefixed physical M3 schema or a landing schema normalized to the prefixed names in this catalog. Raw Data Lake property names vary with the published object: map them through Data Catalog before running this SQL. Dates are numeric YYYYMMDD (0 = none, mapped to NULL); all curated status values are decoded inline. 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 '00' THEN 'Entry in progress (no order lines)' WHEN '05' THEN 'Quotation' WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (released)' WHEN '22' THEN 'Left to allocate' WHEN '23' THEN 'Partially allocated' WHEN '24' THEN 'Partly to allocate, partly on printed picking list' WHEN '26' THEN 'Partly to allocate, partly delivered' WHEN '27' THEN 'Partly to allocate, partly invoiced' WHEN '33' THEN 'Allocated' WHEN '34' THEN 'Allocated, partly on printed picking list' WHEN '36' THEN 'Allocated, partly delivered' WHEN '37' THEN 'Allocated, partly invoiced' WHEN '44' THEN 'Picking list printed' WHEN '46' THEN 'Picking list printed, partly delivered' WHEN '47' THEN 'Picking list printed, partly invoiced' WHEN '66' THEN 'Delivered' WHEN '67' THEN 'Delivered, partly invoiced' WHEN '77' THEN 'Invoiced' WHEN '90' THEN 'Deleted (no open order lines)' WHEN '99' THEN 'Completed without delivery' ELSE oh.OAORSL END AS "Lowest line status on the order — with the highest status, brackets where the order's lines stand",  -- status: ORST_HEAD (all 21 curated values)
  CASE oh.OAORST WHEN '00' THEN 'Entry in progress (no order lines)' WHEN '05' THEN 'Quotation' WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (released)' WHEN '22' THEN 'Left to allocate' WHEN '23' THEN 'Partially allocated' WHEN '24' THEN 'Partly to allocate, partly on printed picking list' WHEN '26' THEN 'Partly to allocate, partly delivered' WHEN '27' THEN 'Partly to allocate, partly invoiced' WHEN '33' THEN 'Allocated' WHEN '34' THEN 'Allocated, partly on printed picking list' WHEN '36' THEN 'Allocated, partly delivered' WHEN '37' THEN 'Allocated, partly invoiced' WHEN '44' THEN 'Picking list printed' WHEN '46' THEN 'Picking list printed, partly delivered' WHEN '47' THEN 'Picking list printed, partly invoiced' WHEN '66' THEN 'Delivered' WHEN '67' THEN 'Delivered, partly invoiced' WHEN '77' THEN 'Invoiced' WHEN '90' THEN 'Deleted (no open order lines)' WHEN '99' THEN 'Completed without delivery' ELSE oh.OAORST END AS "Highest line status on the order",  -- status: ORST_HEAD (all 21 curated values)
  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",
  oh.OATEDL AS "Delivery terms — the Incoterms-style code the order ships under, inherited from the customer",
  oh.OAMODL AS "Delivery method for the order — how the goods travel",
  oh.OAINRC AS "Invoice recipient — the bill-to party when it differs from the orderer"
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;