OE_ORDER_HEADERS_ALL

-- ============================================================
-- Table  : OE_ORDER_HEADERS_ALL — The sales order header — one row per order or quote, carrying the order number, type, customer references, currency, and the workflow status the whole order-to-cash cycle hangs off; operating-unit striped
-- Purpose: Column-selected read of OE_ORDER_HEADERS_ALL — auto-generated from field metadata
-- Grain  : One row per operating unit (ORG_ID) + HEADER_ID
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.HEADER_ID AS "Surrogate key of the order header — what lines, holds, adjustments, and shipping rows join on",
  t.ORG_ID AS "Operating unit the order belongs to — the MOAC stripe, not a warehouse",
  t.ORDER_NUMBER AS "The user-facing order number — unique only together with order type and version, never alone",
  t.ORDER_TYPE_ID AS "The order transaction type — workflow, defaults, and category come from it",
  t.VERSION_NUMBER AS "Document version — part of the real unique key with order number and type",
  t.ORDER_CATEGORY_CODE AS "Order, return, or mixed",
  t.FLOW_STATUS_CODE AS "Workflow-derived header status — a code, not a display name; header and line statuses diverge",  -- decode t.FLOW_STATUS_CODE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'FLOW_STATUS', LANGUAGE-filtered) — see quirks guide #lookups
  t.BOOKED_FLAG AS "Y once the order is booked",
  t.OPEN_FLAG AS "Y while the order is still open",
  t.CANCELLED_FLAG AS "Y when fully cancelled — the row persists",
  t.ORDERED_DATE AS "When the order was placed — the primary analysis date (nullable on quotes and drafts)",
  t.BOOKED_DATE AS "When the order was booked",
  t.SOLD_TO_ORG_ID AS "The customer ACCOUNT (a TCA id) — not an organization, despite the name",
  t.SHIP_TO_ORG_ID AS "The customer ship-to SITE USE (a TCA id) — not an organization either",
  t.SHIP_FROM_ORG_ID AS "Default ship-from warehouse — this one IS an org: an inventory organization",
  t.TRANSACTIONAL_CURR_CODE AS "The order currency",
  t.CUST_PO_NUMBER AS "The customer's own PO reference",
  t.ORDER_SOURCE_ID AS "Where the order came from — decodes through the order source master"
FROM <catalog>.<schema>.OE_ORDER_HEADERS_ALL t
WHERE
  t.ORG_ID = <operating_unit_id>  -- operating unit (MOAC does not filter extracts)
  -- AND t.HEADER_ID = <HEADER_ID>
  -- AND t.ORDERED_DATE >= DATE '<DATE_FROM>'
  -- AND t.ORDERED_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.HEADER_ID;