OOLINE

-- ============================================================
-- Table  : OOLINE — The customer order line — one row per ordered item with quantities across the ordered/delivered/invoiced flow, price, warehouse, and planned delivery date; the core sell-side transaction, joined to its header by ORNO
-- Purpose: Column-selected read of OOLINE — auto-generated from field metadata
-- Grain  : One row per company (CONO) + OBORNO + OBPONR + OBPOSX
-- 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
  ol.OBCONO AS "Company",
  ol.OBORNO AS "Customer order number",
  ol.OBPONR AS "Order line number",
  ol.OBPOSX AS "Line suffix — subdivides a line when it is split (e.g. partial deliveries)",
  CASE ol.OBORST WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (entered)' WHEN '22' THEN 'Not allocated' WHEN '23' THEN 'Partially allocated' WHEN '33' THEN 'Allocated' WHEN '44' THEN 'Picking list printed' WHEN '66' THEN 'Delivered' WHEN '77' THEN 'Invoiced' ELSE ol.OBORST END AS "Line status — the two-char ladder the line walks from entry through allocation, delivery, and invoicing",  -- status: ORST
  ol.OBITNO AS "Item ordered",
  ol.OBORQT AS "Ordered quantity in the basic unit",
  ol.OBDLQT AS "Delivered quantity so far",
  ol.OBIVQT AS "Invoiced quantity so far",
  ol.OBSAPR AS "Sales price per price unit on the line",
  ol.OBWHLO AS "Warehouse the line ships from",
  CASE WHEN ol.OBDWDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(ol.OBDWDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Planned delivery date for the line"  -- YYYYMMDD, 0 → NULL
FROM <catalog>.<schema>.OOLINE ol
WHERE
  ol.OBCONO = <company>
  -- AND ol.OBORNO = '<ORNO>'
  -- AND ol.OBDWDT >= <DATE_FROM>  -- yyyyMMdd numeric
  -- AND ol.OBDWDT <= <DATE_TO>  -- yyyyMMdd numeric
ORDER BY ol.OBORNO;