OIS100MI

-- ============================================================
-- Program: OIS100MI — Customer order API — creates, reads, and maintains customer orders programmatically
-- Purpose: Read the tables behind API program OIS100MI — auto-generated from program-table-map
-- Grain  : OOHEAD × OOLINE — 1:N joins yield one row per line
-- Tables : OOHEAD, OOLINE
-- 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
  oh.OACONO AS "Company",
  oh.OAORNO AS "Customer order number — the key order lines join on",
  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)
  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"
FROM <catalog>.<schema>.OOHEAD oh
LEFT JOIN <catalog>.<schema>.OOLINE ol
  ON ol.OBORNO = oh.OAORNO
 AND ol.OBCONO = oh.OACONO
WHERE
  oh.OACONO = <company>
ORDER BY oh.OAORNO;