OIS101

-- ============================================================
-- Program: OIS101 — Customer order line entry — the line-level screen reached from OIS100, where the OOLINE rows under an OOHEAD header are entered and changed
-- Purpose: Read the tables behind program OIS101 — auto-generated from program-table-map
-- Grain  : OOLINE × OOHEAD — 1:N joins yield one row per line
-- Tables : OOLINE, OOHEAD
-- 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; company-partitioned tables are joined on CONO to prevent cross-company fan-out. 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",
  CASE oh.OAORSL WHEN '00' THEN 'Entry in progress (no order lines)' WHEN '05' THEN 'Quotation' WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (released)' 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 '90' THEN 'Deleted (no open order lines)' WHEN '99' THEN 'Completed without delivery' ELSE oh.OAORSL END AS "Lowest line status on the order — with the highest status, brackets where the order's lines stand",  -- status: ORST_HEAD (all 21 curated values)
  CASE oh.OAORST WHEN '00' THEN 'Entry in progress (no order lines)' WHEN '05' THEN 'Quotation' WHEN '10' THEN 'Preliminary' WHEN '20' THEN 'Final (released)' 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 '90' THEN 'Deleted (no open order lines)' WHEN '99' THEN 'Completed without delivery' ELSE oh.OAORST END AS "Highest line status on the order"  -- status: ORST_HEAD (all 21 curated values)
FROM <catalog>.<schema>.OOLINE ol
LEFT JOIN <catalog>.<schema>.OOHEAD oh
  ON oh.OAORNO = ol.OBORNO
 AND oh.OACONO = ol.OBCONO
WHERE
  ol.OBCONO = <company>
ORDER BY ol.OBORNO;