WSH_NEW_DELIVERIES

-- ============================================================
-- Table  : WSH_NEW_DELIVERIES — The delivery header — a named grouping of delivery details shipped together from one location toward one final drop-off, carrying the ship-confirm date, carrier, waybill, and weights
-- Purpose: Column-selected read of WSH_NEW_DELIVERIES — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + DELIVERY_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.DELIVERY_ID AS "Surrogate key of the delivery",
  t.NAME AS "The delivery name users see — the delivery number",
  t.ORGANIZATION_ID AS "Ship-from inventory org",
  t.STATUS_CODE AS "Two-character delivery status ('OP' open…) — statuses documented by name only, codes ship undecoded",
  t.PLANNED_FLAG AS "Y once the delivery is planned",
  t.DELIVERY_TYPE AS "Standard vs consolidated delivery",
  t.INITIAL_PICKUP_DATE AS "First pickup date of the delivery",
  t.ULTIMATE_DROPOFF_DATE AS "Final drop-off date",
  t.ULTIMATE_DROPOFF_LOCATION_ID AS "Final destination location",
  t.CUSTOMER_ID AS "The customer account (TCA id)",
  t.SHIP_METHOD_CODE AS "Ship method",
  t.CARRIER_ID AS "Freight carrier",
  t.CONFIRM_DATE AS "Ship-confirm date — the natural shipped-date for delivery-grain analytics",
  t.GROSS_WEIGHT AS "Gross weight of the delivery",
  t.WEIGHT_UOM_CODE AS "Weight UOM",
  t.WAYBILL AS "Waybill number — often the tracking number"
FROM <catalog>.<schema>.WSH_NEW_DELIVERIES t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.DELIVERY_ID = <DELIVERY_ID>
  -- AND t.CONFIRM_DATE >= DATE '<DATE_FROM>'
  -- AND t.CONFIRM_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.DELIVERY_ID;