F4941

-- ============================================================
-- Table  : F4941 One row per routing step of a shipment - where the carrier, mode, load assignment, scheduled vs. actual ship/delivery timestamps, and billable vs. payable freight totals live. The workhorse table for carrier performance and freight cost analysis.
-- Purpose: Column-selected read of F4941 — auto-generated from field metadata
-- Grain  : One row per RSSHPN + RSRSSN
-- 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.RSSHPN AS "Shipment number - the system-assigned key that groups order lines for transport; the same value lands on F4211.SDSHPN.",
  f.RSRSSN / POWER(10, 1) AS "Routing step number within the shipment.",  -- implied decimals: 1 (verify in F9210)
  f.RSMOT AS "Mode of transport - truckload, LTL, parcel, rail, and so on.",
  f.RSCARS AS "Carrier's address book number; join to F0101.ABAN8 for the name and F4906 for carrier attributes.",
  f.RSROUT AS "Route code assigned during routing.",
  f.RSRTN AS "Route number assigned by route selection.",
  f.RSDLNO AS "Delivery number, used when freight is charged at the delivery level.",
  f.RSFRSC AS "Freight rate schedule used to rate this record.",
  TRIM(f.RSVMCU) AS "Trip depot business unit - first half of the load key; join to F4960.TMVMCU.",
  f.RSLDNM AS "Load number - second half of the load key; join to F4960/F4961.",
  f.RSTRPL AS "Load leg number within the load.",
  f.RSSTSQ AS "Stop sequence position on the load.",
  f.RSORGN AS "Origin address book number the goods ship from.",
  f.RSANCC AS "Destination address book number.",
  f.RSWGTS / 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.RSWTUM AS "Weight unit of measure.",
  f.RSSCVL AS "Scheduled volume in the volume UoM.",
  f.RSVLUM AS "Volume unit of measure.",
  f.RSNPCS AS "Number of pieces.",
  f.RSNCTR AS "Number of shipping containers.",
  f.RSDSTN AS "Distance in the distance UoM.",
  f.RSUMD1 AS "Distance unit of measure (miles/kilometers).",
  f.RSAEXP / POWER(10, 2) AS "Extended sales value of the goods being shipped; 2 implied decimals.",  -- implied decimals: 2 (verify in F9210)
  f.RSCRCD AS "Transaction (from) currency code.",
  f.RSECST / POWER(10, 2) AS "Extended cost of the goods being shipped; 2 implied decimals.",  -- implied decimals: 2 (verify in F9210)
  CASE WHEN f.RSPPDJ IS NULL OR f.RSPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RSPPDJ AS INT) DIV 1000, 1, 1), CAST(f.RSPPDJ AS INT) % 1000 - 1) END AS "Promised ship date.",  -- CYYDDD Julian → DATE
  CASE WHEN f.RSRSDJ IS NULL OR f.RSRSDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RSRSDJ AS INT) DIV 1000, 1, 1), CAST(f.RSRSDJ AS INT) % 1000 - 1) END AS "Promised delivery date.",  -- CYYDDD Julian → DATE
  CASE WHEN f.RSLDDT IS NULL OR f.RSLDDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RSLDDT AS INT) DIV 1000, 1, 1), CAST(f.RSLDDT AS INT) % 1000 - 1) END AS "Load confirm date - when the load was confirmed at the depot.",  -- CYYDDD Julian → DATE
  CASE WHEN f.RSADDJ IS NULL OR f.RSADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RSADDJ AS INT) DIV 1000, 1, 1), CAST(f.RSADDJ AS INT) % 1000 - 1) END AS "Actual ship date - the anchor for on-time-shipment analysis.",  -- CYYDDD Julian → DATE
  f.RSADTM AS "Actual ship time (HHMMSS).",
  CASE WHEN f.RSDLDT IS NULL OR f.RSDLDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RSDLDT AS INT) DIV 1000, 1, 1), CAST(f.RSDLDT AS INT) % 1000 - 1) END AS "Actual delivery date.",  -- CYYDDD Julian → DATE
  f.RSDLTM AS "Actual delivery time (HHMMSS).",
  f.RSRRTR AS "Routing status - whether a carrier/route has been assigned yet.",
  f.RSRATR AS "Rating status - whether freight has been rated yet.",
  f.RSFRTD AS "Y/N - billable (customer) freight has been calculated for this step.",
  f.RSFRTV AS "Y/N - payable (carrier) freight has been calculated for this step.",
  f.RSFRVC / POWER(10, 4) AS "Payable (carrier) freight amount in domestic currency; 4 implied decimals.",  -- implied decimals: 4 (verify in F9210)
  f.RSCRCP AS "Currency code of the payable (A/P) freight amounts.",
  f.RSFRCC / POWER(10, 4) AS "Billable (customer) freight amount in domestic currency; 4 implied decimals.",  -- implied decimals: 4 (verify in F9210)
  f.RSCRDC AS "Billing (to) currency code."
FROM <catalog>.<schema_data>.f4941 f
WHERE
  f.RSSHPN = <SHPN>
ORDER BY f.RSSHPN;