DOO_HEADERS_ALL

-- ============================================================
-- Table  : DOO_HEADERS_ALL — The sales order header — one row per orchestration order, carrying the order number, source-system references, sold-to customer, currency, and the status the whole order-to-cash cycle hangs off; business-unit striped
-- Purpose: Column-selected read of DOO_HEADERS_ALL — auto-generated from field metadata
-- Grain  : One row per business unit (ORG_ID) + HEADER_ID
-- Notes  : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. 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 orchestration order",
  t.ORDER_NUMBER AS "The sales order number used with fulfillment systems",
  t.ORG_ID AS "The business unit owning the order — Fusion's BU stripe, not the EBS operating unit",
  t.SOURCE_ORDER_SYSTEM AS "Which capture system the order came from",
  t.SOURCE_ORDER_ID AS "Order id in the originating capture system",
  t.SOURCE_ORDER_NUMBER AS "Order number in the originating capture system",
  t.SOLD_TO_PARTY_ID AS "Sold-to customer party (TCA)",
  t.CUSTOMER_PO_NUMBER AS "The customer's purchase order reference",
  t.STATUS_CODE AS "Orchestration order status code",
  t.OPEN_FLAG AS "Y while the order is not closed or canceled",
  t.CANCELED_FLAG AS "Y when the order is canceled — canceled orders keep their rows",
  t.ON_HOLD AS "Order not accepting further processing — note the name has no _FLAG suffix",
  t.SUBMITTED_FLAG AS "N = draft, Y = submitted — filter to Y for real orders",
  t.ORDER_TYPE_CODE AS "Type of order placed",
  t.TRANSACTIONAL_CURRENCY_CODE AS "Order header currency",
  t.ORDERED_DATE AS "When the items were ordered",
  t.REQUEST_SHIP_DATE AS "The customer's originally requested ship date (header default)",
  t.SUBMITTED_DATE AS "When the order was validated and submitted"
FROM <catalog>.<schema>.DOO_HEADERS_ALL t
WHERE
  t.ORG_ID = <business_unit_id>  -- business unit — Fusion _ALL means BU, not the EBS operating unit; see quirks guide #all-means-bu
  -- 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>'  -- the column incremental BICC extracts key on
ORDER BY t.HEADER_ID;