MPHEAD

-- ============================================================
-- Table  : MPHEAD — The purchase order header — one row per order, carrying the supplier, order date, currency, receiving warehouse, and the lowest/highest line status pair mirroring the customer-order pattern
-- Purpose: Column-selected read of MPHEAD — auto-generated from field metadata
-- Grain  : One row per company (CONO) + IAPUNO
-- 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
  ph.IACONO AS "Company",
  ph.IAPUNO AS "Purchase order number — the key order lines join on",
  ph.IAPUSL AS "Lowest line status on the order",  -- status: decode ph.IAPUSL against your configuration — see quirks guide #statuses
  ph.IAPUST AS "Highest line status on the order",  -- status: decode ph.IAPUST against your configuration — see quirks guide #statuses
  ph.IASUNO AS "Supplier the order is placed with",
  CASE WHEN ph.IAPUDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(ph.IAPUDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Purchase order date",  -- YYYYMMDD, 0 → NULL
  ph.IACUCD AS "Order currency",
  ph.IAWHLO AS "Receiving warehouse for the order"
FROM <catalog>.<schema>.MPHEAD ph
WHERE
  ph.IACONO = <company>
  -- AND ph.IAPUNO = '<PUNO>'
  -- AND ph.IAPUDT >= <DATE_FROM>  -- yyyyMMdd numeric
  -- AND ph.IAPUDT <= <DATE_TO>  -- yyyyMMdd numeric
ORDER BY ph.IAPUNO;