F4981

-- ============================================================
-- Table  : F4981 One row per freight charge written by freight update (R4981) - the permanent audit trail that ties every billable and payable charge to the G/L document (voucher or invoice) it created. The system-of-record table for freight spend analytics.
-- Purpose: Column-selected read of F4981 — auto-generated from field metadata
-- Grain  : One row per FHUK01
-- 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.FHUK01 AS "Internal unique key - the physical primary key of this table.",
  f.FHSHPN AS "Shipment number - the system-assigned key that groups order lines for transport; the same value lands on F4211.SDSHPN.",
  f.FHRSSN / POWER(10, 1) AS "Routing step number within the shipment.",  -- implied decimals: 1 (verify in F9210)
  TRIM(f.FHVMCU) AS "Trip depot business unit - first half of the load key; join to F4960.TMVMCU.",
  f.FHLDNM AS "Load number - second half of the load key; join to F4960/F4961.",
  f.FHDLNO AS "Delivery number, used when freight is charged at the delivery level.",
  f.FHNMFC AS "National Motor Freight Classification used for LTL rating.",
  f.FHORGN AS "Origin address book number the goods ship from.",
  TRIM(f.FHNMCU) AS "Shipment depot - the origin depot business unit.",
  f.FHBLPB AS "Charge direction: billable to the customer or payable to the carrier - always filter or group on this.",
  f.FHMOT AS "Mode of transport - truckload, LTL, parcel, rail, and so on.",
  f.FHCARS AS "Carrier's address book number; join to F0101.ABAN8 for the name and F4906 for carrier attributes.",
  f.FHCAMD AS "Auto-pay flag - Y means freight update creates the carrier voucher automatically without a matched invoice.",
  f.FHWGTS / 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.FHWTUM AS "Weight unit of measure.",
  f.FHSCVL AS "Scheduled volume in the volume UoM.",
  f.FHVLUM AS "Volume unit of measure.",
  f.FHFRSC AS "Freight rate schedule used to rate this record.",
  f.FHRTNM AS "Rate name that produced this charge.",
  f.FHRTGB AS "Rate basis the charge was computed on (weight, volume, pieces...).",
  f.FHRTDQ / POWER(10, 4) AS "Quantity the rate was applied against; 4 implied decimals.",  -- implied decimals: 4 (verify in F9210)
  f.FHUOM AS "Unit of measure of the transaction quantity.",
  f.FHCGC1 AS "Charge code classifying the freight charge (line haul, fuel surcharge...).",
  f.FHAG / POWER(10, 2) AS "Gross charge amount; 2 implied decimals.",  -- implied decimals: 2 (verify in F9210)
  f.FHNAMT / POWER(10, 2) AS "Net charge amount after discounts; 2 implied decimals - the number to aggregate for freight spend.",  -- implied decimals: 2 (verify in F9210)
  f.FHCRDC AS "Billing (to) currency code.",
  f.FHCRCD AS "Transaction (from) currency code.",
  f.FHDOCO AS "Order number of the related order line - joins F4211.SDDOCO (or F4311 for inbound).",
  f.FHDCTO AS "Order type of the related order line.",
  f.FHKCOO AS "Order key company of the related order line.",
  f.FHLNID / POWER(10, 3) AS "Order line number; 3 implied decimals - matches F4211.SDLNID as stored.",  -- implied decimals: 3 (verify in F9210)
  f.FHDOC AS "Document number of the voucher or invoice freight update created; joins F0411/F0911.",
  f.FHDCT AS "Document type of the created voucher/invoice.",
  f.FHKCO AS "Document company of the created voucher/invoice.",
  CASE WHEN f.FHDGJ IS NULL OR f.FHDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.FHDGJ AS INT) DIV 1000, 1, 1), CAST(f.FHDGJ AS INT) % 1000 - 1) END AS "G/L date of the freight document.",  -- CYYDDD Julian → DATE
  f.FHVINV AS "Supplier's invoice number used for freight match.",
  f.FHSHAN AS "Ship-to address book number; join to F0101.",
  f.FHFRTH AS "Freight handling code - the freight terms (who arranges and pays).",
  CASE WHEN f.FHADDJ IS NULL OR f.FHADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.FHADDJ AS INT) DIV 1000, 1, 1), CAST(f.FHADDJ AS INT) % 1000 - 1) END AS "Actual ship date - the anchor for on-time-shipment analysis.",  -- CYYDDD Julian → DATE
  f.FHFRSN AS "Reason code for a manual freight adjustment."
FROM <catalog>.<schema_data>.f4981 f
WHERE
  f.FHDGJ >= <DGJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.FHDGJ <= <DGJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.FHUK01;