
--Run this to set up all the data
/* EXEC [Testing].[MxOrderGuide_SingleVendorScenerios] */

-- Run this to see the items and rules created by the setup. 
-- The rules will still need to be applied manually, until then the vendor entity items won't exist.
DECLARE @ItemSearchName VARCHAR(30) = '1001-V%'

SELECT 
	i.ItemCode,
	i.[Description],
	e.Entity,
	v.VendorName,
	'Zone' as RuleLevel,
	vr.VendorItemZoneRuleId as RuleId,
	vr.RuleType,
	vr.ActiveDate,
	vr.ExpirationDate,
	vei.IntroductionDate,
	vei.DiscontinueDate
FROM tbItem i
INNER JOIN tbEntityItem ei ON ei.ItemId = i.ItemId
INNER JOIN tbEntity e ON e.EntityId = ei.EntityId
INNER JOIN tbVendorItem vi ON vi.ItemId = i.ItemId
INNER JOIN tbVendor v ON v.VendorId = vi.VendorId
LEFT JOIN tbVendorItemZoneRule vr ON vr.VendorCode = i.ItemCode
LEFT JOIN tbVendorEntityItem vei ON vei.VendorId = v.VendorId AND vei.EntityId = e.EntityId AND vei.ItemId = i.ItemId
WHERE i.ItemCode like @ItemSearchName

UNION

SELECT 
	i.ItemCode,
	i.[Description],
	e.Entity,
	v.VendorName,
	'Store' as RuleLevel,
	er.VendorItemEntityRuleId as RuleId,
	er.RuleType,
	er.ActiveDate,
	er.ExpirationDate,
	vei.IntroductionDate,
	vei.DiscontinueDate
FROM tbItem i
INNER JOIN tbEntityItem ei ON ei.ItemId = i.ItemId
INNER JOIN tbEntity e ON e.EntityId = ei.EntityId
INNER JOIN tbVendorItem vi ON vi.ItemId = i.ItemId
INNER JOIN tbVendor v ON v.VendorId = vi.VendorId
INNER JOIN tbVendorItemEntityRule er ON er.VendorCode = i.ItemCode
LEFT JOIN tbVendorEntityItem vei ON vei.VendorId = v.VendorId AND vei.EntityId = e.EntityId AND vei.ItemId = i.ItemId
WHERE i.ItemCode like @ItemSearchName

ORDER BY [Description], RuleLevel

-- Run this after the test is done to clean up the data created by the test.
/* EXEC [Testing].[MxOrderGuide_TestTeardown] */
