CREATE TABLE dbo.PurchaseOrderDefaultWorkflows (
	PurchaseOrderDefaultWorkflowID	INT		NOT NULL IDENTITY,
	Step				INT		NOT NULL CHECK(Step > 0),
	LogonUser			VARCHAR(255)	NOT NULL CHECK(LEN(LogonUser) > 0),
	CostCenterID			INT		NULL,
	CategoryID			INT		NULL,
	POTypeID			INT		NULL,
	AttachedAR			BIT		NOT NULL DEFAULT 0,
	TotalLimit			MONEY		NOT NULL DEFAULT 0.0000,
	TotalLimitCompare		VARCHAR(2)	NOT NULL DEFAULT '<=',
)
 
CREATE PROCEDURE GeneratePOWorkflow @po INT AS
SET NOCOUNT ON
 
	DECLARE @ar 		BIT
	DECLARE @cc 		INT
	DECLARE @pt 		INT
	DECLARE @t 		MONEY
	DECLARE @cat		INT
	DECLARE @mcat		INT
	DECLARE @categories	TABLE (catid INT)
	DECLARE @workflow	TABLE (step INT, logonuser VARCHAR(255))
 
	-- Get the basic information
	SELECT	@ar = CASE WHEN ARID IS NULL THEN 0 ELSE 1 END,
		@cc = CostCenterID,
		@pt = TypeID
	  FROM	PurchaseOrders
	 WHERE	ID = @po
 
	-- If an AR is entered don't require cost center
	IF @ar = 1 SET @cc = NULL
 
	-- Get total of items
	SELECT	@t = SUM(Quantity * UnitPrice)
	  FROM	PurchaseOrderItems
	 WHERE	PurchaseOrderID = @po
 
	-- Get all distinct categories from items
	INSERT INTO @categories
		SELECT	CategoryID
		  FROM	PurchaseOrderItems poi
			INNER JOIN CatalogItems ci ON poi.CatalogItemID = ci.CatalogItemID
		 WHERE	poi.PurchaseOrderID = @po
 
	-- For each category find the default workflow and insert
	WHILE (SELECT COUNT(*) FROM @categories) > 0
		BEGIN
			-- Select a category
			SELECT	@cat = catid
			  FROM	@categories

			-- Find master category
			SELECT	@mcat = dbo.fn_MasterCatalogCategory(@cat)
 
			-- This is where I need to add some good logic, this function
			-- Returns 1 or 0 depending on if it can find a workflow for the set
			-- See below for continuation...
			SELECT	dbo.fn_HasDefaultPurchaseOrderWorkflow(@cc, @mcat, @pt, @ar, '<=', @t)
 
			-- Delete from categories list
			DELETE FROM @categories WHERE catid = @cat
		END

CREATE FUNCTION dbo.fn_HasDefaultPurchaseOrderWorkflow (
	@CostCenterID 	INT, 
	@CategoryID	INT,
	@TypeID		INT,
	@AttachedAR	BIT,
	@LimitCompare	VARCHAR(2),
	@Limit		MONEY
) RETURNS BIT AS
	BEGIN
		DECLARE @rc BIT

		IF EXISTS(SELECT	PurchaseOrderDefaultWorkflowID
			    FROM	PurchaseOrderDefaultWorkflows
			   WHERE	(CostCenterID = @CostCenterID 
			      OR	(@CostCenterID IS NULL 
			     AND	CostCenterID IS NULL))
			     AND	(CategoryID = @CategoryID 
			      OR	(@CategoryID IS NULL 
			     AND	CategoryID IS NULL))
			     AND	(POTypeID = @TypeID 
			      OR	(@TypeID IS NULL 
			     AND	POTypeID IS NULL))
			     AND	(AttachedAR = @AttachedAR)
			     AND	(TotalLimit = @Limit)
			     AND	(TotalLimitCompare = @LimitCompare))
			SET @rc = 1
		ELSE
			SET @rc = 0

		RETURN @rc
	END

	INSERT INTO @wf (total, compare)
		SELECT	DISTINCT
			TotalLimit,
			TotalLimitCompare
		  FROM	PurchaseOrderDefaultWorkflows
	         WHERE	(CostCenterID = @cc 
		    OR	(@cc IS NULL 
		   AND	CostCenterID IS NULL))
		   AND	(CategoryID = @mcat
		    OR	(@mcat IS NULL
		   AND	CategoryID IS NULL))
		   AND	(POTypeID = @pt 
		    OR	(@pt IS NULL
		   AND	POTypeID IS NULL))
		   AND	(AttachedAR = @ar)

FindWorkflow3:
	SELECT	@total = total,
		@compare = compare
	  FROM	@wf
	 WHERE	ABS(total - @t) = (SELECT MIN(ABS(total - @t)) FROM @wf)
	   AND	total <> 0

	SELECT	@use = CASE @compare
			WHEN '<=' THEN CASE WHEN @t <= @total THEN 1 ELSE 0 END
			WHEN '>=' THEN CASE WHEN @t >= @total THEN 1 ELSE 0 END
			WHEN '<' THEN CASE WHEN @t < @total THEN 1 ELSE 0 END
			WHEN '>' THEN CASE WHEN @t > @total THEN 1 ELSE 0 END
			WHEN '=' THEN CASE WHEN @t = @total THEN 1 ELSE 0 END
			WHEN '!=' THEN CASE WHEN @t <> @total THEN 1 ELSE 0 END
		END

	IF (@use = 1)
		BEGIN
			INSERT INTO @workflow
				SELECT	Step,
					LogonUser
				  FROM	dbo.fn_DefaultPurchaseOrderWorkflow(@cc, @mcat, @pt, @ar, @compare, @total)
		END
	ELSE
		BEGIN
			DELETE FROM @wf WHERE total = @total AND compare = @compare
			IF (SELECT COUNT(*) FROM @wf) > 0
				BEGIN
					GOTO FindWorkflow3
				END
		END