/* This query returns all currently inactive items in the order guide for a given store and vendor */

DECLARE @EntityName varchar(30) = '%Castle Rock%'
DECLARE @VendorName varchar(30) = '%CFA Inc. Warehouse%'

	declare @EntityID bigint = (select top 1 EntityId from tbEntity where Entity like @EntityName)
	declare @VendorID bigint = (select top 1 v.VendorId from tbVendor v 
								inner join tbVendorEntity ve on ve.EntityID = @entityId and ve.VendorID = v.VendorID 
								where v.VendorName like @VendorName)
	declare @EntityTime as datetime = GetDate()
	declare @UserhasOverridePermission as bit = 0


	SELECT * FROM (

		Select
			tbItemGroup.ItemGroup,   
			tbVendorEntityItem.VendorCode,  
			tbVendorEntityItem.VendorDescription,  
			tbVendorEntityItem.VendorID,  
			tbVendorEntityItem.EntityID,  
			tbVendorEntityItem.ItemID,  
			tbVendorEntityItem.DiscontinueDate,  
			tbVendorEntityItem.DiscontinueDateOverrideEnabled,  
			tbVendorEntityItem.DiscontinueDateOverridden,  
			tbVendorEntityItem.LastPurchaseDate, 
			tbEntityItem.InUse,  
			isnull(tbEntityItem.NewItemForCountLocation,0) as NewITem,  
			tVI.NewVendorItemComment,
			case when tbEntityItem.IntroductionDate < @EntityTime   
				  and (tbEntityItem.DiscontinueDate is null or @EntityTime < tbEntityItem.DiscontinueDate)  
			then 1 else 0 end as EntityItemStatus, 
			 
			dbo.fn_IsActiveInDateRange(tbVendorEntityItem.EntityId, 
									tbVendorEntityItem.IntroductionDate,
									tbVendorEntityItem.DiscontinueDate,
									tbEntityItem.InUse,
									@EntityTime,
									tbVendorEntityItem.DiscontinueDateOverrideEnabled,
									tbVendorEntityItem.DiscontinueDateOverridden) as VendorEntityItemStatus  
		  from tbVendorEntityItem with(nolock)  
		  join tbEntityItem with(nolock) on tbVendorEntityItem.EntityID = tbEntityItem.EntityID   
										and tbVendorEntityItem.ItemID = tbEntityItem.ItemID   
		  join tbItem with(nolock) on tbVendorEntityItem.ItemID = tbItem.ItemID   
		  join tbItemGroup with(nolock) on tbItem.GroupID = tbItemGroup.ItemGroupID  
		  join tbVendor with (nolock) on tbVendorEntityItem.VendorID = tbVendor.VendorID
		  join (SELECT DISTINCT VendorID, ItemID, Coalesce(NewVendorItemComment, '') as NewVendorItemComment FROM tbVendorItem with (nolock)) tVI ON tVI.VendorID = @VendorId and tVI.ItemID = tbItem.ItemID  
		 where tbVendorEntityItem.EntityID = @EntityID  
		   and tbVendorEntityItem.VendorID = @VendorID  
		   and tbVendor.LocalVendor = 0  
		   and ((tbVendorEntityItem.IntroductionDate IS NOT NULL AND tbVendorEntityItem.IntroductionDate <= @EntityTime) And (tbVendorEntityItem.DiscontinueDate IS NULL OR tbVendorEntityItem.DiscontinueDate > @EntityTime)
		   OR (tbVendorEntityItem.DiscontinueDateOverrideEnabled = 1 AND @UserhasOverridePermission = 1))   
		  
	  ) OrderGuideItemSubQuery WHERE VendorEntityItemStatus = 0
	  ORDER BY NewITem DESC, VendorDescription  
	  