select
	SOH.STOFCY_0 [Site],
	SOH.SOHNUM_0 [SO#],
	SOH.CUSORDREF_0 [Shopify#],
	coalesce(SDH.SDHNUM_0,'') [Shipment#],
	case SOH.ORDSTA_0
		when 1 then 'Open'
		when 2 then 'Closed'
	end [Order status],
	SOQ.ITMREF_0 [X3 Order Item#],
	SOQ.QTYSTU_0 [X3 Order qty],
	[SL_ECOMM].lot [SL lot],
	SLF.AAACUMQTY_0 [X3 available stock for SL lot],
	[SL_ECOMM].[tracking]
from PROD.SORDER SOH
left join PROD.SDELIVERY SDH
	on SDH.SOHNUM_0 = SOH.SOHNUM_0
left join PROD.SORDERQ SOQ
	on SOQ.SOHNUM_0 = SOH.SOHNUM_0
left join [analytics].[dbo].[SL_ECOMM]
	on SOQ.ITMREF_0 = [SL_ECOMM].item
	and SOQ.STOFCY_0 = [SL_ECOMM].site
	and SOH.CUSORDREF_0 = [SL_ECOMM].order_id
left join PROD.STOLOTFCY SLF
	on [SL_ECOMM].site = SLF.STOFCY_0
	and [SL_ECOMM].item = SLF.ITMREF_0
	and [SL_ECOMM].lot = SLF.LOT_0
where SOH.SOHTYP_0 = 'WEB'
and SOH.STOFCY_0 in ('SSB','SMB')
and SOH.ORDSTA_0 <> 2
--and SLF.AAACUMQTY_0 is null
order by 2, 3, 6