ODHEAD

-- ============================================================
-- Table  : ODHEAD — The invoiced-delivery header — one row per delivered and invoiced customer order delivery, carrying the invoice number and date, payer, and currency; the payment term is part of its key, so one delivery can settle under more than one term
-- Purpose: Column-selected read of ODHEAD — auto-generated from field metadata
-- Grain  : One row per company (CONO) + UAORNO + UADLIX + UATEPY
-- 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
  o.UACONO AS "Company",
  o.UAORNO AS "Customer order number",
  o.UADLIX AS "Delivery index the invoiced delivery corresponds to",
  o.UATEPY AS "Payment terms — part of the key, so one delivery can settle under more than one term",
  o.UAIVNO AS "Invoice number the delivery was billed on",
  CASE WHEN o.UAIVDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(o.UAIVDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Invoice date",  -- YYYYMMDD, 0 → NULL
  o.UACUNO AS "Customer the delivery was made to",
  o.UAPYNO AS "Payer — the customer billed, when different from the receiver",
  o.UACUCD AS "Invoice currency",
  o.UAWHLO AS "Warehouse the delivery shipped from"
FROM <catalog>.<schema>.ODHEAD o
WHERE
  o.UACONO = <company>
  -- AND o.UAORNO = '<ORNO>'
  -- AND o.UAIVDT >= <DATE_FROM>  -- yyyyMMdd numeric
  -- AND o.UAIVDT <= <DATE_TO>  -- yyyyMMdd numeric
ORDER BY o.UAORNO;