WSH_NEW_DELIVERIES

-- ============================================================
-- Table  : WSH_NEW_DELIVERIES — The delivery (shipment) header — a named grouping of shipment lines shipped together, carrying the ship-confirm and actual ship/delivery dates, carrier, ship method, waybill, and weight/volume rollups
-- 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 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.DELIVERY_ID AS "Surrogate key of the delivery (shipment)",
  t.DELIVERY_NAME AS "User-visible shipment name",
  t.ORGANIZATION_ID AS "Ship-from inventory organization",
  t.STATUS_CODE AS "Shipment status — code values are not enumerated in the dictionary, so they ship undecoded",
  t.DELIVERY_TYPE AS "Standard vs consolidated shipment",
  t.SOURCE_HEADER_ID AS "Header id of the order lines tied to this delivery",
  t.CONFIRM_DATE AS "When the shipment was ship-confirmed",
  t.INITIAL_PICKUP_DATE AS "First pickup date of the shipment",
  t.ULTIMATE_DROPOFF_DATE AS "Final dropoff date",
  t.ACTUAL_SHIP_DATE AS "Actual ship date",
  t.ACTUAL_DELIVERY_DATE AS "When delivered to the recipient",
  t.CARRIER_ID AS "Freight carrier — trip-level attributes live here in Fusion; there is no trip table",
  t.SHIP_METHOD_CODE AS "Shipping method code",
  t.SERVICE_LEVEL AS "Service level component of the ship method",
  t.MODE_OF_TRANSPORT AS "Transport mode component of the ship method",
  t.WAYBILL AS "Waybill / tracking number",
  t.GROSS_WEIGHT AS "Shipment gross weight rollup",
  t.SHIP_TO_PARTY_ID AS "Ship-to customer party (TCA)"
FROM <catalog>.<schema>.WSH_NEW_DELIVERIES t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the business unit — see quirks guide #item-org-striping
  -- 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>'  -- the column incremental BICC extracts key on
ORDER BY t.DELIVERY_ID;