F4960

-- ============================================================
-- Table  : F4960 One row per load (trip) - a planned vehicle departure that groups shipments, keyed by trip depot plus load number, with carrier, vehicle, load status, and distance.
-- Purpose: Column-selected read of F4960 — auto-generated from field metadata
-- Grain  : One row per TMVMCU + TMLDNM
-- 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
  TRIM(f.TMVMCU) AS "Trip depot business unit - first half of the load key.",
  f.TMLDNM AS "Load number - second half of the load key; join to F4960/F4961.",
  TRIM(f.TMNMCU) AS "Shipment depot - the origin depot business unit.",
  f.TMORGN AS "Origin address book number the goods ship from.",
  TRIM(f.TMMCUX) AS "Destination business unit.",
  f.TMANCC AS "Destination address book number.",
  f.TMCTY1 AS "Destination city.",
  f.TMADDS AS "Destination state.",
  f.TMADDZ AS "Destination postal code.",
  f.TMCTR AS "Destination country.",
  f.TMZON AS "Destination zone code.",
  f.TMLDTY AS "Load type.",
  f.TMLDLS AS "Load status - the workflow filter for loads (pending, approved, confirmed...).",
  CASE WHEN f.TMLOAD IS NULL OR f.TMLOAD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.TMLOAD AS INT) DIV 1000, 1, 1), CAST(f.TMLOAD AS INT) % 1000 - 1) END AS "Scheduled load date.",  -- CYYDDD Julian → DATE
  f.TMSHFT AS "Shift code for the scheduled departure.",
  f.TMTMLS AS "Scheduled loading time (HHMMSS).",
  CASE WHEN f.TMPPDJ IS NULL OR f.TMPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.TMPPDJ AS INT) DIV 1000, 1, 1), CAST(f.TMPPDJ AS INT) % 1000 - 1) END AS "Promised ship date.",  -- CYYDDD Julian → DATE
  CASE WHEN f.TMADDJ IS NULL OR f.TMADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.TMADDJ AS INT) DIV 1000, 1, 1), CAST(f.TMADDJ AS INT) % 1000 - 1) END AS "Actual ship date - the anchor for on-time-shipment analysis.",  -- CYYDDD Julian → DATE
  f.TMMOT AS "Mode of transport - truckload, LTL, parcel, rail, and so on.",
  f.TMVTYP AS "Vehicle type planned for the load.",
  f.TMPVEH AS "Primary vehicle ID.",
  f.TMRLNO AS "Vehicle registration / license plate number.",
  f.TMCARS AS "Carrier's address book number; join to F0101.ABAN8 for the name and F4906 for carrier attributes.",
  f.TMROUT AS "Route code assigned during routing.",
  f.TMRTN AS "Route number assigned by route selection.",
  f.TMFRSC AS "Freight rate schedule used to rate this record.",
  f.TMDSGP AS "Dispatch group - groups compatible products for transport.",
  f.TMDATY AS "Dispatch type.",
  f.TMDSCD AS "Disposition code.",
  f.TMDSTN AS "Distance in the distance UoM.",
  f.TMUMD1 AS "Distance unit of measure (miles/kilometers).",
  f.TMODST AS "Out-of-route distance beyond the standard route.",
  f.TMNSTP AS "Number of stops on the load.",
  f.TMRRTR AS "Routing status - whether a carrier/route has been assigned yet.",
  f.TMRATR AS "Rating status - whether freight has been rated yet.",
  f.TMFRVC / POWER(10, 4) AS "Payable (carrier) freight amount in domestic currency; 4 implied decimals.",  -- implied decimals: 4 (verify in F9210)
  f.TMCRCP AS "Currency code of the payable (A/P) freight amounts."
FROM <catalog>.<schema_data>.f4960 f
ORDER BY f.TMVMCU;