MPLINE

-- ============================================================
-- Table  : MPLINE — The purchase order line — one row per ordered item with quantity, price, planned delivery date, and status; the core buy-side transaction, joined to its header by PUNO
-- Purpose: Column-selected read of MPLINE — auto-generated from field metadata
-- Grain  : One row per company (CONO) + IBPUNO + IBPNLI + IBPNLS
-- 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
  pl.IBCONO AS "Company",
  pl.IBPUNO AS "Purchase order number",
  pl.IBPNLI AS "Purchase order line number",
  pl.IBPNLS AS "Line subnumber — subdivides a line across deliveries",
  pl.IBITNO AS "Item ordered",
  pl.IBPUST AS "Line status — the two-char ladder the line walks from entry through confirmation, receipt, and invoice matching",  -- status: decode pl.IBPUST against your configuration — see quirks guide #statuses
  pl.IBORQA AS "Ordered quantity in the purchase unit",
  pl.IBPUPR AS "Purchase price per price unit on the line",
  CASE WHEN pl.IBDWDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(pl.IBDWDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Planned delivery date for the line",  -- YYYYMMDD, 0 → NULL
  pl.IBWHLO AS "Receiving warehouse for the line"
FROM <catalog>.<schema>.MPLINE pl
WHERE
  pl.IBCONO = <company>
  -- AND pl.IBPUNO = '<PUNO>'
  -- AND pl.IBDWDT >= <DATE_FROM>  -- yyyyMMdd numeric
  -- AND pl.IBDWDT <= <DATE_TO>  -- yyyyMMdd numeric
ORDER BY pl.IBPUNO;