UDT
Table Name: FREIGHT
Description: Freight Tracking
Object Type: No Object with Auto-Increment


Add UDFs to UDT Freight
1. SalesOrder - Sales Order - Numeric ** Same as ORDR.DocNum
2. TrackNo - Tracking Number - Alpha(30) **Same as ORDR.TrackNo
3. ShipCharge - Shipping Charges - Amount - Default 0 - Mandatory
4. Updated - Updated - Alpha(1) - Valid Values (Y-Yes/N-No) - Default N - Mandatory


Add UDFs to Marketing Document Header
1. SalesOrder - Sales Order - Numeric ** Same as ORDR.DocNum
This will be used to make it easier to associate the delivery directly with the originating order
2. TrackNo - Tracking Number - Alpha(254)
Will be used on Delivery documents to output tracking numbers
3. ShipCharge - Shipping Charges - Amount - Default 0 - Mandatory
Will be used on Delivery documents to output shipping charges


PTN to Auto Update Sales Order Header

-- BSC: ORDR: Update sales order field for tracking numbers

IF :object_type = '17' AND (:transaction_type = 'A') THEN

	UPDATE T0
	SET T0."U_SalesOrder" = T0."DocNum"
	FROM ORDR T0
	WHERE T0."DocEntry" = :list_of_cols_val_tab_del;
 
END IF;



PTN to Auto Pull FREIGHT Tracking Data into Deliveries

-- BSC: ODLN: Update tracking numbers and shipping charges, mark UDT as updated

IF :object_type = '15' AND (:transaction_type = 'A' OR :transaction_type = 'U') THEN

	UPDATE T0
	SET T0."U_TrackNo" = X."TrackNo", T0."U_ShipCharge" = X."ShipCharge"
	FROM ODLN T0
	INNER JOIN (
		SELECT
			 D0."U_SalesOrder"
			,STRING_AGG(D0."U_TrackNo",','ORDER BY D0."U_TrackNo") AS "TrackNo"
			,SUM(D0."U_ShipCharge") AS "ShipCharge"
		FROM "@FREIGHT" D0
		WHERE D0."U_Updated" = 'N'
		GROUP BY D0."U_SalesOrder"
	) X ON T0."U_SalesOrder" = X."U_SalesOrder"
	WHERE T0."DocEntry" = :list_of_cols_val_tab_del;
 
	UPDATE T0
	SET T0."U_Updated" = 'Y'
	FROM "@FREIGHT" T0
	INNER JOIN ODLN T1 ON T0."U_SalesOrder" = T1."U_SalesOrder"
	WHERE T0."U_Updated" = 'N'
	AND T1."DocEntry" = :list_of_cols_val_tab_del; 
 
END IF;


Process Update to Existing Orders

UPDATE T0
SET T0."U_SalesOrder" = T0."DocNum"
FROM ORDR T0
WHERE T0."DocStatus" = 'O'