MPLINE

-- ============================================================
-- Table  : MPLINE — The purchase order line — one row per ordered item with quantity, price, planned delivery date, and the lowest/highest line-status pair; 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 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
  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",
  CASE pl.IBPUSL WHEN '12' THEN 'Awaiting authorization' WHEN '15' THEN 'Entered — ready for printout' WHEN '20' THEN 'Document printed' WHEN '50' THEN 'Goods received' WHEN '60' THEN 'Quality inspection partially performed' WHEN '64' THEN 'Rejected at inspection — handling not yet decided' WHEN '65' THEN 'Quality inspection completed' WHEN '69' THEN 'Rejected at inspection — handled through the claim routine' WHEN '70' THEN 'Put-away partially completed' WHEN '75' THEN 'Put-away complete' ELSE pl.IBPUSL END AS "Lowest line status — the least-processed point any part of the line's quantity still sits at; the line is fully received only when this end reaches the receipt rungs",  -- status: PUST_LINE (all 10 curated values)
  CASE pl.IBPUST WHEN '12' THEN 'Awaiting authorization' WHEN '15' THEN 'Entered — ready for printout' WHEN '20' THEN 'Document printed' WHEN '50' THEN 'Goods received' WHEN '60' THEN 'Quality inspection partially performed' WHEN '64' THEN 'Rejected at inspection — handling not yet decided' WHEN '65' THEN 'Quality inspection completed' WHEN '69' THEN 'Rejected at inspection — handled through the claim routine' WHEN '70' THEN 'Put-away partially completed' WHEN '75' THEN 'Put-away complete' ELSE pl.IBPUST END AS "Highest line status — the furthest point any part of the line's quantity has reached; on a partially received line this reads ahead of the quantity still outstanding",  -- status: PUST_LINE (all 10 curated values)
  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;