F4215

-- ============================================================
-- Table  : F4215 One row per shipment - the anchor record Transportation Management creates when order lines are grouped for delivery; despite the F42xx name it belongs to system 49. Carries status, mode, geography, weight, value, and promised dates.
-- Purpose: Column-selected read of F4215 — auto-generated from field metadata
-- Grain  : One row per XHSHPN
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  f.XHSHPN AS "Shipment number - the system-assigned key that groups order lines for transport; the same value lands on F4211.SDSHPN.",
  f.XHSSTS AS "Shipment status - the main workflow filter (pending, confirmed, freight updated...).",
  f.XHWGTS / POWER(10, 4) AS "Shipment weight in the weight UoM; stored with 4 implied decimals - divide by 10,000.",  -- implied decimals: 4 (verify in F9210)
  f.XHWGTU AS "Weight unit of measure.",
  f.XHMOT AS "Mode of transport - truckload, LTL, parcel, rail, and so on.",
  f.XHROTE AS "Free-form routing description text.",
  TRIM(f.XHMCU) AS "Business unit that owns the shipment.",
  TRIM(f.XHNMCU) AS "Shipment depot - the origin depot business unit.",
  f.XHORGN AS "Origin address book number the goods ship from.",
  f.XHSRCO AS "Where the shipment originated - sales order, purchase order, or another source.",
  f.XHBPFG AS "Bulk or packed shipment flag.",
  f.XHAEXP / POWER(10, 2) AS "Extended sales value of the goods being shipped; 2 implied decimals.",  -- implied decimals: 2 (verify in F9210)
  f.XHECST / POWER(10, 2) AS "Extended cost of the goods being shipped; 2 implied decimals.",  -- implied decimals: 2 (verify in F9210)
  CASE WHEN f.XHDRQJ IS NULL OR f.XHDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.XHDRQJ AS INT) DIV 1000, 1, 1), CAST(f.XHDRQJ AS INT) % 1000 - 1) END AS "Requested delivery date from the order.",  -- CYYDDD Julian → DATE
  CASE WHEN f.XHRSDJ IS NULL OR f.XHRSDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.XHRSDJ AS INT) DIV 1000, 1, 1), CAST(f.XHRSDJ AS INT) % 1000 - 1) END AS "Promised delivery date.",  -- CYYDDD Julian → DATE
  f.XHRSDT AS "Promised delivery time (HHMMSS).",
  f.XHAN8 AS "Customer (sold-to) address book number.",
  f.XHSHAN AS "Ship-to address book number; join to F0101.",
  f.XHCTY1 AS "Ship-to city.",
  f.XHADDS AS "Ship-to state.",
  f.XHADDZ AS "Ship-to postal code.",
  f.XHCTR AS "Ship-to country.",
  f.XHZON AS "Delivery zone code.",
  f.XHCAR1 AS "First preferred carrier address number considered during routing.",
  f.XHCAR2 AS "Second preferred carrier address number.",
  f.XHCAR3 AS "Third preferred carrier address number.",
  f.XHFRTH AS "Freight handling code - the freight terms (who arranges and pays).",
  f.XHFRSC AS "Freight rate schedule used to rate this record.",
  f.XHDLLV AS "Level at which freight applies: delivery, line, or load.",
  f.XHRSLT AS "Route selection type used for this shipment.",
  f.XHDSTN AS "Distance in the distance UoM.",
  f.XHUMD1 AS "Distance unit of measure (miles/kilometers).",
  f.XHNRTS AS "Count of routing steps on the shipment.",
  f.XHCTYO AS "Origin city.",
  f.XHADSO AS "Origin state.",
  f.XHADZO AS "Origin postal code.",
  f.XHCTRO AS "Origin country."
FROM <catalog>.<schema_data>.f4215 f
WHERE
  f.XHSHPN = <SHPN>
  -- AND f.XHDRQJ >= <DRQJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.XHDRQJ <= <DRQJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.XHSHPN;