OE_ORDER_LINES_ALL

-- ============================================================
-- Table  : OE_ORDER_LINES_ALL — Sales order lines at their most granular workflow unit — shipment splits, model options, included items, and configuration items each get their own row, individually statused and quantity-tracked through booking, shipping, and invoicing
-- Purpose: Column-selected read of OE_ORDER_LINES_ALL — auto-generated from field metadata
-- Grain  : One row per operating unit (ORG_ID) + LINE_ID
-- Caution: Quantities are running counters and lines split as they ship — cancelled lines persist with ORDERED_QUANTITY driven to 0, and model/option/config structures add rows; filter ITEM_TYPE_CODE and sum knowingly
-- 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.LINE_ID AS "Surrogate key of the order line — what shipping, reservations, and invoicing join on",
  t.HEADER_ID AS "The parent order header",
  t.ORG_ID AS "Operating unit — striped on lines as well as headers",
  t.LINE_NUMBER AS "Display line number — rows multiply under it as lines split",
  t.LINE_TYPE_ID AS "The line transaction type",
  t.LINE_CATEGORY_CODE AS "Order vs return line",
  t.ITEM_TYPE_CODE AS "Row kind in configured products — standard, model, option, included, config; filter it or double-count",
  t.INVENTORY_ITEM_ID AS "The item sold — joins the item master together with the ship-from org",
  t.SHIP_FROM_ORG_ID AS "The fulfilling warehouse — an inventory org, the org half of the item join (see quirks guide #two-orgs)",
  t.ORDERED_QUANTITY AS "Current ordered quantity — already reduced by cancellations",
  t.SHIPPED_QUANTITY AS "Quantity ship-confirmed",
  t.CANCELLED_QUANTITY AS "Quantity cancelled off the line — what ORDERED_QUANTITY no longer includes",
  t.FULFILLED_QUANTITY AS "Quantity fulfilled — what drives invoicing in fulfillment sets",
  t.ORDER_QUANTITY_UOM AS "UOM of the line quantities",
  t.UNIT_SELLING_PRICE AS "Net unit price after adjustments",
  t.REQUEST_DATE AS "Customer-requested date — the demand-side analysis date",
  t.SCHEDULE_SHIP_DATE AS "Scheduled ship date from scheduling",
  t.ACTUAL_SHIPMENT_DATE AS "Actual ship date — the OTIF numerator",
  t.FLOW_STATUS_CODE AS "Workflow-derived line status — a different lookup type than the header's",  -- decode t.FLOW_STATUS_CODE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'LINE_FLOW_STATUS', LANGUAGE-filtered) — see quirks guide #lookups
  t.OPEN_FLAG AS "Y while the line is still open"
FROM <catalog>.<schema>.OE_ORDER_LINES_ALL t
WHERE
  t.ORG_ID = <operating_unit_id>  -- operating unit (MOAC does not filter extracts)
  -- AND t.LINE_ID = <LINE_ID>
  -- AND t.REQUEST_DATE >= DATE '<DATE_FROM>'
  -- AND t.REQUEST_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.LINE_ID;