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 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
  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 '05' THEN 'Quotation' WHEN '10' THEN 'Preliminary' WHEN '22' THEN 'Left to allocate' WHEN '23' THEN 'Partially allocated' WHEN '24' THEN 'Partly to allocate, partly on printed picking list' WHEN '26' THEN 'Partly to allocate, partly delivered' WHEN '27' THEN 'Partly to allocate, partly invoiced' WHEN '33' THEN 'Allocated' WHEN '34' THEN 'Allocated, partly on printed picking list' WHEN '36' THEN 'Allocated, partly delivered' WHEN '37' THEN 'Allocated, partly invoiced' WHEN '44' THEN 'Picking list printed' WHEN '46' THEN 'Picking list printed, partly delivered' WHEN '47' THEN 'Picking list printed, partly invoiced' WHEN '66' THEN 'Delivered' WHEN '67' THEN 'Delivered, partly invoiced' WHEN '77' THEN 'Invoiced' WHEN '99' THEN 'Completed without delivery' ELSE ol.OBORST END AS "Line status — two independent numerals, lowest and furthest degree of processing, so mixed pairs like 46 mean partly picked, partly delivered",  -- status: ORST_LINE (all 18 curated values)
  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
  ol.OBORQA AS "Ordered quantity in the alternate (order) unit of measure — the basic-unit figure sits in ORQT alongside it",
  ol.OBSAAM AS "Line sales amount — divided by the ordered quantity it yields the unit base price"
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;