

	/*=========================================================================================================
	Procedure		: [usp_Coremark_SelectivityStacking_Migration]
	Author			: Pavan K
	Created date	: 10 Nov 2017
	Description		: use this sp in get selectivity stacking for all selective coremarks while running migration tool
	===========================================================================================================
	 EXEC [usp_Coremark_SelectivityStacking_Migration] 2360
	=========================================================================================================*/
	CREATE PROCEDURE [dbo].[usp_Coremark_SelectivityStacking_Migration] 
			   @FacilityIDs VARCHAR(MAX)
	AS
	BEGIN

		DECLARE @SystemID	INT
		DECLARE @CoremarkID	INT
		DECLARE	@Output		VARCHAR(MAX)
		DECLARE	@OPBY		VARCHAR(MAX)


		DECLARE @EvenOdd		 TABLE	( Even varchar(1), Odd varchar(1))
		DECLARE @A3SystemValues	 TABLE	( Cuts varchar(1))
		DECLARE @A4SystemValues	 TABLE	( Cuts varchar(1))
	
		CREATE TABLE #Coremarks			(ID INT IDENTITY(1,1), CoremarkID INT, KeymarkID INT, KeyCut VARCHAR(7), KeymarkName VARCHAR(20), ParentID INT, Stacking VARCHAR(MAX), OPBY VARCHAR(MAX), SystemID INT)
		CREATE TABLE #MKSystems			( SystemID INT, GM_KeyMarkID INT, KeyCut VARCHAR(7), SystemType VARCHAR(50), PinSize VARCHAR(5) )

		CREATE TABLE #TempPhantomKeysTable	( ID int,Code varchar(7))
		CREATE TABLE #TempCoremarkSelTable	( IsStandardSelectivity bit, KeyMarkID int, KeyMarkName varchar(50), KLevel INT, KeyCut varchar(7), COL1 varchar(1),COL2 varchar(1),COL3 varchar(1),COL4 varchar(1),COL5 varchar(1),COL6 varchar(1),COL7 varchar(1)) 
		CREATE TABLE #TempTable				( COL1 varchar(1),COL2 varchar(1),COL3 varchar(1),COL4 varchar(1),COL5 varchar(1),COL6 varchar(1),COL7 varchar(1))
		CREATE TABLE #TempSELTable			( ID int, COL1 varchar(1),COL2 varchar(1),COL3 varchar(1),COL4 varchar(1),COL5 varchar(1),COL6 varchar(1),COL7 varchar(1))

	
		INSERT INTO @EvenOdd				(Even,Odd)	VALUES (0,1),(2,3),(4,5),(6,7),(8,9)
		INSERT INTO @A3SystemValues			(Cuts)		VALUES (0),(1),(2),(3),(4),(5),(6)
		INSERT INTO @A4SystemValues			(Cuts)		VALUES (0),(1),(2),(3),(4),(5)


		DECLARE @systemType		VARCHAR(25)
		DECLARE @pinSize		INT
		DECLARE @keymark		VARCHAR(7)
		DECLARE @gm				VARCHAR(7)
		DECLARE @gmKeyMarkID	INT
		DECLARE @gmCutOPLevel	VARCHAR(7)
		DECLARE @keymarkName	VARCHAR(15)	
		DECLARE @ParentID		INT
		DECLARE @RID			INT = 1;

		DECLARE	 @KeyMark1 VARCHAR(1) ,@KeyMark2 VARCHAR(1) ,@KeyMark3 VARCHAR(1) ,@KeyMark4 VARCHAR(1) ,@KeyMark5 VARCHAR(1) ,@KeyMark6 VARCHAR(1) ,@KeyMark7 VARCHAR(1)
		DECLARE	 @GM1 VARCHAR(1) ,@GM2 VARCHAR(1) ,@GM3 VARCHAR(1) ,@GM4 VARCHAR(1) ,@GM5 VARCHAR(1) ,@GM6 VARCHAR(1) ,@GM7 VARCHAR(1)
	
		INSERT INTO #MKSystems (SystemID, GM_KeyMarkID, SystemType, PinSize)
		SELECT
			 MA.SystemID
			,GM_KeyMarkID
			,ST.SystemType
			,PS.PinSize
		FROM		MasterKeySystem	MA
		LEFT JOIN	SystemType		ST ON MA.SystemTypeID = ST.SystemTypeID
		LEFT JOIN	PinSize			PS ON MA.PinsizeID	= PS.PinSizeID
		WHERE	MA.FacilityID	IN (SELECT Value FROM dbo.udf_Table_SplitString(',',@FacilityIDs))
		
	
		--SELECT 1,GETDATE()

		EXEC [dbo].[usp_Connection] 1

		UPDATE MS
		SET		MS.KeyCut = CONVERT(VARCHAR(7),DECRYPTBYKEY(KM.KeyCut))
		FROM	KeyMark		KM
		JOIN	#MKSystems	MS ON KM.SystemID = MS.SystemID	AND KM.KeymarkID = MS.GM_KeyMarkID

		INSERT INTO #Coremarks ( CoremarkID)--, KeyCut, KeymarkName, ParentID )
		SELECT	DISTINCT CS.CoreMarkID--, CONVERT(VARCHAR(7),DECRYPTBYKEY(KM.KeyCut)), KM.KeyMarkName, KM.ParentID
		FROM	CoreMark_Sel	CS
		JOIN	KeyMark			KM ON CS.KeyMarkID = KM.KeyMarkID
		WHERE	KM.FacilityID	IN (SELECT Value FROM dbo.udf_Table_SplitString(',',@FacilityIDs))

		UPDATE	TCM
		SET		TCM.KeymarkID = OCM.KeyMarkID
		FROM	#Coremarks	TCM
		JOIN	CoreMark	OCM ON TCM.CoremarkID = OCM.CoreMarkID

		UPDATE	CM
		SET
			 CM.KeyCut		= CONVERT(VARCHAR(7),DECRYPTBYKEY(KM.KeyCut))
			,CM.KeymarkName	= KM.KeyMarkName
			,CM.ParentID	= KM.ParentID
			,CM.SystemID	= KM.SystemID
		FROM	#Coremarks	CM
		JOIN	KeyMark		KM ON CM.KeyMarkID = KM.KeyMarkID
	
		--SELECT 2,GETDATE()
		--SELECT * FROM #Coremarks

		DECLARE @Loop INT
		SELECT @Loop = MAX(ID) FROM #Coremarks

		WHILE @Loop >= 1
		BEGIN

			SELECT
				 @CoremarkID	= CoremarkID
				,@keymark		= KeyCut
				,@keymarkName	= KeymarkName
				,@ParentID		= ParentID
				,@SystemID		= SystemID
			FROM	#Coremarks
			WHERE	ID = @Loop

			SELECT
				 @gmKeyMarkID	= GM_KeyMarkID
				,@systemType	= SystemType
				,@pinSize		= PinSize
				,@gm			= keycut
			FROM	#MKSystems	MA
			WHERE	MA.SystemID	= @SystemID
		

			IF(@gm IS NULL OR @gm = '')
			BEGIN	
				SELECT @gm = CONVERT(VARCHAR(7),DECRYPTBYKEY(keycut))
				FROM	KeyMark
				WHERE	KeyMarkID = @ParentID
	
				SET @gmCutOPLevel = ''
			END

			INSERT INTO #TempPhantomKeysTable	(ID,Code)	VALUES (0,@keymark)
		
			INSERT INTO #TempCoremarkSelTable
				( IsStandardSelectivity, KeyMarkID, KeyCut, KeyMarkName, KLevel )
			SELECT
				 c.IsStandardSelectivity
				,c.KeyMarkID
				,CONVERT(VARCHAR(7),DECRYPTBYKEY(k.keycut))
				,k.KeyMarkName
				,k.KeyMarkLevelID
			FROM	CoreMark_Sel	c
			JOIN	KeyMark			k ON c.KeyMarkID=k.KeyMarkID
			WHERE	c.coremarkID	= @CoremarkID
		
			UPDATE	TCSL
			SET		TCSL.KeyMarkName	= KL.KeyMarkLevel+'''' COLLATE DATABASE_DEFAULT  + TCSL.KeyMarkName
			FROM	#TempCoremarkSelTable	TCSL
			JOIN	KeyMarkLevel			KL	 ON TCSL.KLevel = KL.KeyMarkLevelID AND TCSL.KLevel IN (6,7,8,9,10,11,13)
				
		
			UPDATE #TempCoremarkSelTable
			SET	
				 COL1 = SUBSTRING(KeyCut,1,1)
				,COL2 = SUBSTRING(KeyCut,2,1)
				,COL3 = SUBSTRING(KeyCut,3,1)
				,COL4 = SUBSTRING(KeyCut,4,1)
				,COL5 = SUBSTRING(KeyCut,5,1)
				,COL6 = SUBSTRING(KeyCut,6,1)
				,COL7 = SUBSTRING(KeyCut,7,1)

			SELECT	
				 @KeyMark1	= SUBSTRING(@keymark,1,1)
				,@KeyMark2	= SUBSTRING(@keymark,2,1)
				,@KeyMark3	= SUBSTRING(@keymark,3,1)
				,@KeyMark4	= SUBSTRING(@keymark,4,1)
				,@KeyMark5	= SUBSTRING(@keymark,5,1)
				,@KeyMark6	= SUBSTRING(@keymark,6,1)
				,@KeyMark7	= SUBSTRING(@keymark,7,1)
				,@GM1		= SUBSTRING(@gm,1,1)
				,@GM2		= SUBSTRING(@gm,2,1)
				,@GM3		= SUBSTRING(@gm,3,1)
				,@GM4		= SUBSTRING(@gm,4,1)
				,@GM5		= SUBSTRING(@gm,5,1)
				,@GM6		= SUBSTRING(@gm,6,1)
				,@GM7		= SUBSTRING(@gm,7,1)

		
			INSERT INTO #TempTable (COL1,COL2,COL3,COL4,COL5,COL6,COL7) 
			SELECT 
				 CASE	WHEN TCSL.COL1 = @KeyMark1 OR TCSL.COL1 = @GM1  THEN (CASE WHEN TCSL.COL1 = @GM1 AND IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE TCSL.COL1 END
				,CASE	WHEN TCSL.COL2 = @KeyMark2 OR TCSL.COL2 = @GM2	THEN (CASE WHEN TCSL.COL2 = @GM2 AND IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE TCSL.COL2 END
				,CASE	WHEN TCSL.COL3 = @KeyMark3 OR TCSL.COL3 = @GM3	THEN (CASE WHEN TCSL.COL3 = @GM3 AND IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE TCSL.COL3 END
				,CASE	WHEN TCSL.COL4 = @KeyMark4 OR TCSL.COL4 = @GM4	THEN (CASE WHEN TCSL.COL4 = @GM4 AND IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE TCSL.COL4 END
				,CASE	WHEN TCSL.COL5 = @KeyMark5 OR TCSL.COL5 = @GM5	THEN (CASE WHEN TCSL.COL5 = @GM5 AND IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE TCSL.COL5 END
				,CASE	WHEN @pinSize<>5	AND  (TCSL.COL6 = @KeyMark6 OR TCSL.COL6 = @GM6)	THEN (CASE WHEN TCSL.COL6 = @GM6 AND  IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE (CASE WHEN @pinSize<>5 THEN TCSL.COL6 ELSE 'X' END) END
				,CASE	WHEN @pinSize=7		AND  (TCSL.COL7 = @KeyMark7 OR TCSL.COL7 = @GM7)	THEN (CASE WHEN TCSL.COL7 = @GM7 AND  IsStandardSelectivity=0 THEN 'Y' ELSE 'X' END) ELSE (CASE WHEN @pinSize=7 THEN TCSL.COL7 ELSE 'X' END) END
			FROM #TempCoremarkSelTable TCSL
		
			IF EXISTS (SELECT 1 FROM #TempTable WHERE COL1 = 'Y')
			BEGIN		
		
				IF @systemType = 'A2'
				BEGIN
					IF ((@GM1%2)=0)			
						INSERT #TempTable
						SELECT	EVEN,'X','X','X','X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL1 AND COL1 <> 'X'
						WHERE	EO.Even <> @GM1
						AND		EO.Even <> @KeyMark1
						AND		TT.COL1 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	Odd,'X','X','X','X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL1 AND COL1 <>'X'
						WHERE	EO.Odd <> @GM1
						AND		EO.Odd <> @KeyMark1
						AND		TT.COL1 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	Cuts,'X','X','X','X','X','X'
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL1 AND COL1 <> 'X'
					WHERE	EO.Cuts <> @GM1
					AND		EO.Cuts <> @KeyMark1
					AND		TT.COL1 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	Cuts,'X','X','X','X','X','X'
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL1 AND COL1 <> 'X'
					WHERE	EO.Cuts <> @GM1
					AND		EO.Cuts <> @KeyMark1
					AND		TT.COL1 IS NULL
		
				END
		
				UPDATE #TempTable SET COL1='X' where COL1='Y'

			END

			IF EXISTS (SELECT 1 from #TempTable where COL2='Y')
			BEGIN
	
				IF @systemType = 'A2'
				BEGIN
					IF ((@GM2%2)=0)							
						INSERT #TempTable
						SELECT	'X',EVEN,'X','X','X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL2 AND COL2 <> 'X'
						WHERE	EO.Even <> @GM2
						AND		EO.Even <> @KeyMark2
						AND		TT.COL2 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	'X',Odd,'X','X','X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL2 AND COL2 <>'X'
						WHERE	EO.Odd <> @GM2
						AND		EO.Odd <> @KeyMark2
						AND		TT.COL2 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	'X',Cuts,'X','X','X','X','X'
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL2 AND COL2 <> 'X'
					WHERE	EO.Cuts <> @GM2
					AND		EO.Cuts <> @KeyMark2
					AND		TT.COL2 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	'X',Cuts,'X','X','X','X','X'
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL2 AND COL2 <> 'X'
					WHERE	EO.Cuts <> @GM2
					AND		EO.Cuts <> @KeyMark2
					AND		TT.COL2 IS NULL
		
				END

				UPDATE #TempTable SET COL2='X' where COL2='Y'
			END

			IF EXISTS (SELECT 1 from #TempTable where COL3='Y')
			BEGIN
	
				IF @systemType = 'A2'
				BEGIN
					IF ((@GM3%2)=0)							
						INSERT #TempTable
						SELECT	'X','X',EVEN,'X','X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL3 AND COL3 <> 'X'
						WHERE	EO.Even <> @GM3
						AND		EO.Even <> @KeyMark3
						AND		TT.COL3 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	'X','X',Odd,'X','X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL3 AND COL3 <>'X'
						WHERE	EO.Odd <> @GM3
						AND		EO.Odd <> @KeyMark3
						AND		TT.COL3 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	'X','X',Cuts,'X','X','X','X'
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL3 AND COL3 <> 'X'
					WHERE	EO.Cuts <> @GM3
					AND		EO.Cuts <> @KeyMark3
					AND		TT.COL3 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	'X','X',Cuts,'X','X','X','X'
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL3 AND COL3 <> 'X'
					WHERE	EO.Cuts <> @GM3
					AND		EO.Cuts <> @KeyMark3
					AND		TT.COL3 IS NULL
		
				END

				UPDATE #TempTable SET COL3='X' where COL3='Y'

			END

			IF EXISTS (SELECT 1 from #TempTable where COL4='Y')
			BEGIN
	
				IF @systemType = 'A2'
				BEGIN
					IF ((@GM4%2)=0)							
						INSERT #TempTable
						SELECT	'X','X','X',EVEN,'X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL4 AND COL4 <> 'X'
						WHERE	EO.Even <> @GM4
						AND		EO.Even <> @KeyMark4
						AND		TT.COL4 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	'X','X','X',Odd,'X','X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL4 AND COL4 <>'X'
						WHERE	EO.Odd <> @GM4
						AND		EO.Odd <> @KeyMark4
						AND		TT.COL4 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X',Cuts,'X','X','X'
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL4 AND COL4 <> 'X'
					WHERE	EO.Cuts <> @GM4
					AND		EO.Cuts <> @KeyMark4
					AND		TT.COL4 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X',Cuts,'X','X','X'
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL4 AND COL4 <> 'X'
					WHERE	EO.Cuts <> @GM4
					AND		EO.Cuts <> @KeyMark4
					AND		TT.COL4 IS NULL
		
				END

				UPDATE #TempTable SET COL4='X' where COL4='Y'

			END

			IF EXISTS (SELECT 1 from #TempTable where COL5='Y')
			BEGIN
	
				IF @systemType = 'A2'
				BEGIN
					IF ((@GM5%2)=0)							
						INSERT #TempTable
						SELECT	'X','X','X','X',EVEN,'X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL5 AND COL5 <> 'X'
						WHERE	EO.Even <> @GM5
						AND		EO.Even <> @KeyMark5
						AND		TT.COL5 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	'X','X','X','X',Odd,'X','X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL5 AND COL5 <>'X'
						WHERE	EO.Odd <> @GM5
						AND		EO.Odd <> @KeyMark5
						AND		TT.COL5 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X','X',Cuts,'X','X'
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL5 AND COL5 <> 'X'
					WHERE	EO.Cuts <> @GM5
					AND		EO.Cuts <> @KeyMark5
					AND		TT.COL5 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X','X',Cuts,'X','X'
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL5 AND COL5 <> 'X'
					WHERE	EO.Cuts <> @GM5
					AND		EO.Cuts <> @KeyMark5
					AND		TT.COL5 IS NULL
		
				END

				UPDATE #TempTable SET COL5='X' where COL5='Y'

			END

			IF EXISTS (SELECT 1 from #TempTable where COL6='Y')
			BEGIN

				IF @systemType = 'A2'
				BEGIN
					IF ((@GM6%2)=0)							
						INSERT #TempTable
						SELECT	'X','X','X','X','X',EVEN,'X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL6 AND COL6 <> 'X'
						WHERE	EO.Even <> @GM6
						AND		EO.Even <> @KeyMark6
						AND		TT.COL6 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	'X','X','X','X','X',Odd,'X'
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL6 AND COL6 <>'X'
						WHERE	EO.Odd <> @GM6
						AND		EO.Odd <> @KeyMark6
						AND		TT.COL6 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X','X','X',Cuts,'X'
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL6 AND COL6 <> 'X'
					WHERE	EO.Cuts <> @GM6
					AND		EO.Cuts <> @KeyMark6
					AND		TT.COL6 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X','X','X',Cuts,'X'
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL6 AND COL6 <> 'X'
					WHERE	EO.Cuts <> @GM6
					AND		EO.Cuts <> @KeyMark6
					AND		TT.COL6 IS NULL
		
				END

				UPDATE #TempTable SET COL6='X' where COL6='Y'

			END

			IF EXISTS (SELECT 1 from #TempTable where COL7='Y')
			BEGIN

				IF @systemType = 'A2'
				BEGIN
					IF ((@GM7%2)=0)							
						INSERT #TempTable
						SELECT	'X','X','X','X','X','X',EVEN
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Even = TT.COL7 AND COL7 <> 'X'
						WHERE	EO.Even <> @GM7
						AND		EO.Even <> @KeyMark7
						AND		TT.COL7 IS NULL

					ELSE
						INSERT #TempTable
						SELECT	'X','X','X','X','X','X',Odd
						FROM		@EvenOdd	EO
						LEFT JOIN	#TempTable	TT ON EO.Odd = TT.COL7 AND COL7 <>'X'
						WHERE	EO.Odd <> @GM7
						AND		EO.Odd <> @KeyMark7
						AND		TT.COL7 IS NULL
			 
				END
				ELSE IF @systemType = 'A3'
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X','X','X','X',Cuts
					FROM		@A3SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL7 AND COL7 <> 'X'
					WHERE	EO.Cuts <> @GM7
					AND		EO.Cuts <> @KeyMark7
					AND		TT.COL7 IS NULL
		 
				END
				ELSE
				BEGIN

					INSERT #TempTable
					SELECT	'X','X','X','X','X','X',Cuts
					FROM		@A4SystemValues	EO
					LEFT JOIN	#TempTable		TT ON EO.Cuts = TT.COL7 AND COL7 <> 'X'
					WHERE	EO.Cuts <> @GM7
					AND		EO.Cuts <> @KeyMark7
					AND		TT.COL7 IS NULL
		
				END

				UPDATE #TempTable SET COL7='X' where COL7='Y'

			END

			INSERT #TempSELTable
			VALUES	 (1,'X','X','X','X','X','X','X'),(2,'X','X','X','X','X','X','X'),(3,'X','X','X','X','X','X','X'),(4,'X','X','X','X','X','X','X')
					,(5,'X','X','X','X','X','X','X'),(6,'X','X','X','X','X','X','X'),(7,'X','X','X','X','X','X','X'),(8,'X','X','X','X','X','X','X')
	
	
			UPDATE	TS
			SET		TS.COL1	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL1) ID,COL1 AS Val FROM #TempTable WHERE COL1 <> 'X') RN ON TS.ID = RN.ID

			UPDATE	TS
			SET		TS.COL2	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL2) ID,COL2 AS Val FROM #TempTable WHERE COL2 <> 'X') RN ON TS.ID = RN.ID

			UPDATE	TS
			SET		TS.COL3	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL3) ID,COL3 AS Val FROM #TempTable WHERE COL3 <> 'X') RN ON TS.ID = RN.ID

			UPDATE	TS
			SET		TS.COL4	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL4) ID,COL4 AS Val FROM #TempTable WHERE COL4 <> 'X') RN ON TS.ID = RN.ID

			UPDATE	TS
			SET		TS.COL5	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL5) ID,COL5 AS Val FROM #TempTable WHERE COL5 <> 'X') RN ON TS.ID = RN.ID

			UPDATE	TS
			SET		TS.COL6	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL6) ID,COL6 AS Val FROM #TempTable WHERE COL6 <> 'X') RN ON TS.ID = RN.ID

			UPDATE	TS
			SET		TS.COL7	= RN.Val
			FROM	#TempSELTable	TS
			JOIN	(SELECT DENSE_RANK()OVER(ORDER BY COL7) ID,COL7 AS Val FROM #TempTable WHERE COL7 <> 'X') RN ON TS.ID = RN.ID
	
			DELETE FROM #TempSELTable WHERE COL1+COL2+COL3+COL4+COL5+COL6+COL7 = 'XXXXXXX'
				
			SELECT @Output=STUFF((SELECT ','+COL1+COL2+COL3+COL4+COL5+COL6+COL7 FROM #TempSELTable ORDER BY ID DESC FOR XML PATH('')),1,1,'')+','+@keymark
			SELECT	@OPBY = STUFF((SELECT '; '+KeyMarkName FROM	#TempCoremarkSelTable ORDER BY dbo.udf_scalar_GetAlphanumericSortValue(KeyMarkName,1) FOR XML PATH('')),1,2,'')		



			UPDATE #Coremarks SET Stacking = @Output, OPBY = @OPBY WHERE ID = @Loop
			--UPDATE Coremark SET Stacking = @Output WHERE CoremarkID = @CoremarkID
		

			DELETE FROM #TempCoremarkSelTable
			DELETE FROM #TempTable
			DELETE FROM #TempSELTable
			DELETE FROM #TempPhantomKeysTable

			SET @Output = NULL
			SET @OPBY = NULL
			SET @Loop = @Loop - 1

			SELECT
				 @gmKeyMarkID	= NULL
				,@systemType	= NULL
				,@pinSize		= NULL
				,@gm			= NULL
				,@SystemID		= NULL
				,@KeyMark1		= NULL
				,@KeyMark2		= NULL
				,@KeyMark3		= NULL
				,@KeyMark4		= NULL
				,@KeyMark5		= NULL
				,@KeyMark6		= NULL
				,@KeyMark7		= NULL
				,@GM1			= NULL
				,@GM2			= NULL
				,@GM3			= NULL
				,@GM4			= NULL
				,@GM5			= NULL
				,@GM6			= NULL
				,@GM7			= NULL
		

		END	
	
		UPDATE	OCM
		SET		OCM.Stacking = TCM.Stacking, OCM.OP_By = TCM.OPBY
		FROM	Coremark	OCM
		JOIN	#Coremarks	TCM ON OCM.CoremarkID = TCM.CoremarkID

		--SELECT * FROM #Coremarks
			
		DROP TABLE #TempCoremarkSelTable
		DROP TABLE #TempTable
		DROP TABLE #TempSELTable
		DROP TABLE #TempPhantomKeysTable
		DROP TABLE #Coremarks
		DROP TABLE #MKSystems

		EXEC [dbo].[usp_Connection] 0

	END;
	