
DECLARE @StoreA	  VARCHAR(15) = '01580' 
DECLARE @StoreB	  VARCHAR(15) = '03995' 
DECLARE @StoreC	  VARCHAR(15) = '00011' 
DECLARE @StoreD	  VARCHAR(15) = '04027' 
DECLARE @StoreE	  VARCHAR(15) = '00048'
DECLARE @Zone1	  BIGINT	  = 723
DECLARE @Zone2	  BIGINT	  = 873
DECLARE @ItemCode VARCHAR(15) = '101297'
DECLARE @VendorId1 BIGINT	  =10
DECLARE @VendorId2 BIGINT	  = 11 

DECLARE @EntityId1 BIGINT = (select top 1 EntityId from tbEntity where StoreNumber = @StoreA)
DECLARE @EntityId2 BIGINT = (select top 1 EntityId from tbEntity where StoreNumber = @StoreB)
DECLARE @EntityId3 BIGINT = (select top 1 EntityId from tbEntity where StoreNumber = @StoreC)
DECLARE @EntityId4 BIGINT = (select top 1 EntityId from tbEntity where StoreNumber = @StoreD)
DECLARE @EntityId5 BIGINT = (select top 1 EntityId from tbEntity where StoreNumber = @StoreE)

--List zones this store is in
SELECT '' as [Zones That Include Store:], z.ZoneId, z.Zone, t.ZoneType, ez.EntityId, E.StoreNumber, E.Entity
FROM tbEntityZone ez with (nolock) 
JOIN tbEntity e with (nolock) on e.EntityId = ez.EntityId
JOIN tbZone z with (nolock) on ez.zoneid = z.zoneid
JOIN tbZoneType t with (nolock) on t.zonetypeid = z.zonetypeid
WHERE z.ZoneId in (@Zone1, @Zone2) and ez.EntityID in (@EntityId1, @EntityId2, @EntityId3, @EntityId4, @EntityId5)
	AND	t.ZoneType like 'Location%'

-- List the vendors for this item for this store
SELECT '' as [Vendor Item From Vendors these Stores Use:], v.VendorName, v.VendorId, vi.IntroductionDate, vi.DiscontinueDate, vi.ItemId,vendoritemid, e.Entity
FROM tbVendor v with (nolock) 
JOIN tbVendorEntity ve with (nolock) on v.VendorId = ve.VendorId and ve.EntityId in (@EntityId1, @EntityId2, @EntityId3, @EntityId4, @EntityId5)
JOIN tbVendorItem vi with (nolock) on vi.VendorId = v.VendorId and vi.VendorCode = @ItemCode
JOIN tbEntity e with (nolock) on e.EntityId = ve.EntityId
WHERE v.VendorId in (@VendorId1, @VendorId2)

-- List the zones rules that apply to this item AND this store
SELECT '' as [Zone Rules For This Item, For Zones This Store Is In:], z.Zone, r.VendorCode, r.RuleType, ActiveDate, ExpirationDate, Deleted, e.EntityId, E.StoreNumber, E.Entity
FROM dbo.tbVendorItemZoneRule r with (nolock) 
JOIN tbZone z on z.ZoneId = r.ZoneId
JOIN tbEntityZone ez on ez.ZoneId = z.ZoneId and ez.EntityId in (@EntityId1, @EntityId2, @EntityId3, @EntityId4, @EntityId5)
JOIN tbEntity e with (nolock) on e.EntityId = ez.EntityId
WHERE vendorcode = @ItemCode and Deleted = 0
order by Entity

-- List the store rules that apply to this item
SELECT '' as [Store Rules For This Item:],  r.VendorCode, r.RuleType, ActiveDate, ExpirationDate, Deleted, e.EntityId, E.StoreNumber, E.Entity
FROM dbo.tbVendorItemEntityRule r with (nolock) 
JOIN tbEntity e with (nolock) on e.EntityId = r.EntityId
WHERE vendorcode = @ItemCode and e.EntityId in (@EntityId1, @EntityId2, @EntityId3, @EntityId4, @EntityId5)

-- The Vendor Entity Item
SELECT '' as [The Vendor Entity Item:], v.VendorName, v.VendorId, VendorDescription, VendorCode, IntroductionDate, DiscontinueDate, e.EntityId, E.StoreNumber, E.Entity, vei.vendorentityitemid
FROM tbVendorEntityItem vei with (nolock) 
JOIN tbEntity e with (nolock) on e.EntityId = vei.EntityId
JOIN tbVendor v with (nolock) on vei.VendorID = v.VendorID
WHERE vei.EntityId in (@EntityId1, @EntityId2, @EntityId3, @EntityId4, @EntityId5)
-- AND vei.VendorId in (@VendorId1, @VendorId2)
 and VendorCode = @ItemCode
 order by Entity, VendorName

