UPDATE t
	SET InventoryList = SUBSTRING(tmp2.Products, 1, LEN(tmp2.products) - 1)
	FROM #temp AS t
	INNER JOIN (SELECT  List AS Products, t.BCKey
				FROM #temp AS t
				CROSS APPLY(SELECT CONVERT(NVARCHAR(30), i.productid) + '|' AS [TEXT()]
							FROM orders AS o WITH(NOLOCK) 
							INNER JOIN orderstatus AS os WITH(NOLOCK) ON o.statusid = os.statusid
							INNER JOIN orderdetails AS od WITH(NOLOCK) ON o.orderid = od.orderid
							INNER JOIN inventory AS I WITH(NOLOCK) ON od.inventoryid = i.inventoryid
							INNER JOIN inventorymisc AS im WITH(NOLOCK) ON i.inventoryid = im.inventoryid
							WHERE o.subjectid = t.bckey
								AND os.official = 1
								AND secondarysubjecttypeid = 0
								AND (im.DistributorSignupPack = 1
									OR im.distributorUpgradepack = 1
									OR gfRanktypeid IS NOT NULL
									)
								AND o.bonusdate BETWEEN @qualifyStart AND @qualifyEnd
							FOR XML PATH('')
							) AS DUMMY(List)
					WHERE list IS NOT NULL
					AND t.ID = @bonusrunid
				) AS TMP2 ON TMP2.BCKey = T.BCKey
	WHERE t.ID = @bonusrunid