WSH_DELIVERY_DETAILS

-- ============================================================
-- Table  : WSH_DELIVERY_DETAILS — The atomic shipping line — one row per shippable unit of demand from order lines (or WIP/PO sources), plus container rows, tracking requested/picked/shipped quantities through pick release and ship confirm
-- Purpose: Column-selected read of WSH_DELIVERY_DETAILS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + DELIVERY_DETAIL_ID
-- Caution: Container rows (CONTAINER_FLAG = 'Y') are packaging, not demand — filter them out of quantity aggregates; one order line commonly fans out to several rows through splits and backorders
-- 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.DELIVERY_DETAIL_ID AS "Surrogate key of the delivery line / container row",
  t.ORG_ID AS "Operating unit — the only WSH table carrying the MOAC stripe; push OU filters down to here",
  t.ORGANIZATION_ID AS "Ship-from inventory org (warehouse) — the org half of the item join",
  t.SOURCE_CODE AS "Which system fed the row — 'OE' for sales orders, or WIP/PO sources",
  t.SOURCE_HEADER_ID AS "The source document header — the sales order header for OE rows",
  t.SOURCE_LINE_ID AS "The source document line — the sales order line for OE rows",
  t.RELEASED_STATUS AS "One-character pick-release status — Oracle documents the statuses by name only, so the codes ship undecoded here",
  t.INVENTORY_ITEM_ID AS "The item being shipped",
  t.REQUESTED_QUANTITY AS "Quantity requested to ship on this row",
  t.REQUESTED_QUANTITY_UOM AS "UOM of the requested quantity",
  t.PICKED_QUANTITY AS "Quantity picked",
  t.SHIPPED_QUANTITY AS "Quantity ship-confirmed — SUM by source line, one order line fans out across rows",
  t.CANCELLED_QUANTITY AS "Quantity cancelled",
  t.CUSTOMER_ID AS "The customer account (TCA id) the row ships to",
  t.DATE_REQUESTED AS "Customer-requested date carried from the source line",
  t.DATE_SCHEDULED AS "Scheduled date — the backlog analysis date; actual ship time lives on the trip stop",
  t.SHIP_METHOD_CODE AS "Ship method for the row",
  t.CONTAINER_FLAG AS "Y for container (LPN) rows — packaging, not demand; filter from quantity aggregates",
  t.SUBINVENTORY AS "Sourcing subinventory for the pick",
  t.LOT_NUMBER AS "Lot allocated to the row, for lot-controlled items"
FROM <catalog>.<schema>.WSH_DELIVERY_DETAILS t
WHERE
  t.ORG_ID = <operating_unit_id>  -- operating unit (MOAC does not filter extracts)
  AND t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.DELIVERY_DETAIL_ID = <DELIVERY_DETAIL_ID>
  -- AND t.DATE_SCHEDULED >= DATE '<DATE_FROM>'
  -- AND t.DATE_SCHEDULED <= 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.DELIVERY_DETAIL_ID;