Cacher is the code snippet organizer for pro developers

DECLARE @ALPHACODE  varchar(50)

set @ALPHACODE = (
select  CAST(CAST(mod.ModuleProperties AS NTEXT) AS xml).query('//IMS_SSCC_PLANT_CODE').value('(node()[1])', 'varchar(50)')
from modules mod
where ModuleCode = 'IWAMS_FW_BL'
)


SELECT 	
	plt.WOBuildID, 
	plt.WOBuildDFID,	
	plt.WOBuildSequenceNo, 	
	plt.WOBuildQuantity, 	
	plu.Code AS PLUCode,  	
	plu.Text1 AS PLUdescription,
	plu.Text3 AS DCP,
	ord.DeliveryDocketNo, 
	ord.EstDeliverDate, 
	ord.LocalOrderNo,
	Dept1.Code AS DepotCode, 
	Dept1.Text1 AS DepotDescription, 
	Dept1.Code + ' - ' + Dept1.Text1 AS DepotCodeDescription ,
	pltcount.TotalPallets,
	case when Dept1.flag1 = 1 then 'APPLY SSCC LABEL TO THIS PALLET' else '' end as [msg],
	CASE WHEN cust.Code = 1000 then 'Supplier Alpha Code : '+@ALPHACODE else '' end AS ALPHACODE,
	CASE WHEN cust.Code = 1000 then 'Wave Number: 3' else '' end AS WAVENUMBER,
	cust.Code  AS CustCode,
	cust.Text1 AS CustDescription
FROM 
	oms_wo_builder plt 
	LEFT OUTER JOIN dbo.datafiles_data plu 
		ON plt.WOBuildDFID = plu.DatafileDataID 
	LEFT JOIN oms_order_header ord 
		ON plt.WOType = ord.OrderType 
		AND plt.WONo = ord.OrderNo 
		AND ord.OrderVersion = 0 
	INNER JOIN (
				SELECT
					COUNT(DISTINCT WOBuildSequenceNo)AS TotalPallets,
					WOType AS WOType2,
					WONo AS WONo2
				FROM
					oms_wo_builder
				GROUP BY
					WOType,
					WONo
				) pltcount
		ON plt.WOType = pltcount.WOType2 
		AND plt.WONo = pltcount.WONo2
	LEFT OUTER JOIN dbo.vw_ce_datafiles_data Dept1 
		ON ord.DepotID = Dept1.DatafileDataID
    LEFT JOIN datafiles_data cust 
		ON cust.DatafileDataID = ord.TraderID

WHERE WOType='SO' AND WoNO IN ('SODR00103699')  AND WOBuildSequenceNo in (1) AND 1=1