SQL Insert with Union All

My attached code works fine with one annoying exception

The data is not going into the table in the same order that my union all's are written.

No matter what I do...it is inserted with an ascii sort on the afsSourceDetail column

So...
Instead of it going in like this...which I absolutely need
P01-REG
P01-FED
P01-St
P01-CITY


It goes in like this.
P01-CITY
P01-FED
P01-REG
P01-ST

Any suggestions?

ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )  
AS 
--Get afsSource for return
Begin
	Declare @afsSource nvarchar(20)
	Set @afsSource = (Select afsSource from proc_cfa.dbo.P_Availableforsale where afsid = @afsid)
End

--Add taxes to Available for sale
Begin 
	--Get Summary Data  
   ;WITH taxesCTE(P_Fed, P_St, P_City, P_R1, afsAmount, afsSource, afsSourceDetail, afsDate, afsStatus, afsTransaction) 
   --from 
	AS (  Select round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2) [P_Fed],   
				 round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2) [P_St],  
				 round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2) [P_City],                 
				x.afsAmount	- (round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2))
							- (round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2)) 
							- (round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2)) [P_R1],  
				 x.afsAmount,  
				 x.afsSource,  
				 x.afsSourceDetail,  
				 x.afsDate,  
				 x.afsStatus,  
				 x.afsTransaction  
		  From proc_lt.dbo.p_deals p  
				Join	(Select top 1 SUBSTRING(afsSource,4,4) DealID, * 
						from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
						order by afsSource) x  
				on p.DealID = x.DealID  
	   ) 
   
   --use CTE Data and Insert all rows with one insert/ where each insert afsamount > 0 
	INSERT INTO PROC_CFA.dbo.P_AvailableForSale  
						   (afsSource  
						   ,afsSourceDetail  
						   ,afsDate  
						   ,afsAmount  
						   ,afsStatus  
						   ,afsTransaction) 
	SELECT afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction  
	FROM taxesCTE  where P_R1 > 0
	UNION
	SELECT afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction  
	FROM taxesCTE where P_Fed > 0 
	UNION
	SELECT afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction  
	FROM taxesCTE  where P_St > 0  
	UNION 
	SELECT afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction  
	FROM taxesCTE where P_City > 0 
    --Remove original afsid   
	Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid  
 
End 

Select @afsSource as afsSource

Select all Open in new window