Select
		itn
		, Scale_Weight
		, Customer_Tracking_Number
		, CASE --Tracking Number
			When Tracking_Number IS NOT NULL Then
				Tracking_Number
			When Tracking_Number IS NULL Then
				(SELECT do.order_id, d.pro_nbr 
				FROM Server.DB.dbo.delivery_arc d 
				JOIN Server.DB.dbo.deliveryorders_arc do ON d.delivery_nbr = do.delivery_nbr 
				WHERE do.order_id = @Criteria) --'005436624'
		  END 						
		, Ship_Date
		, Carrier_Name
		, Shipment_Number
		, Package_Charge
	From BES_PackageTrackingDetails
	WHERE shipment_number = @Criteria
	Order By itn

Select a.itn
		, a.Scale_Weight
		, a.Customer_Tracking_Number
		, coalesce(a.Tracking_Number,d.pro_nrb)
		, a.Ship_Date
		, a.Carrier_Name
		, a.Shipment_Number
		, a.Package_Charge
	From BES_PackageTrackingDetails a
    inner join Server.DB.dbo.deliveryorders_arc do on a.shipment_number = do.order_id
    inner join Server.DB.dbo.delivery_arc d on d.delivery_nbr = do.delivery_nbr 
	WHERE a.shipment_number = @Criteria
	Order By itn