SELECT a.iSO_id,
 	(SELECT TOP 1 cItemCode 
		FROM ACC_Item_Codes zz 	
		WHERE   zz.cGroup_id = e.cGroup_id AND 	
			RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) +
 		    RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7) +
 		    RIGHT('0000000' + CAST(a.iUPC_Case AS VARCHAR(7)), 7) = zz.cItemCodeKey_id_MIC) AS cItemCode_MIC,
  	(SELECT TOP 1 cItemCode
		FROM ACC_Item_Codes zzz 
		WHERE   zzz.cGroup_id = e.cGroup_id AND 
		    RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) +
 		    RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7) = zzz.cItemCodeKey_id_MI) AS cItemCode_MI,
  	(SELECT TOP 1 cItemcode
		FROM ACC_Item_Codes zzzz 
		WHERE   zzzz.cGroup_id = e.cGroup_id AND 
		    RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) +
 		    RIGHT('0000000' + CAST(a.iUPC_Case AS VARCHAR(7)), 7) = zzzz.cItemCodeKey_id_MC) AS cItemCode_MC
FROM ACC_SO_Dtl a 	
	LEFT JOIN ACC_SO b 	
		ON a.iSO_id = b.iSO_id 	
	LEFT JOIN ACC_Division e 	
		ON b.cTo_id = e.cDivision_id 	
WHERE  a.iSO_id = 38618