-- EXEC MIG_ITEM_SEQ_INFO_Sp
CREATE OR ALTER PROCEDURE MIG_ITEM_SEQ_INFO_Sp
AS
BEGIN
	DELETE FROM MIG_ITEM_SEQ_INFO

	INSERT INTO [MIG_ITEM_SEQ_INFO]
	(
		 [ZZ_ID]
		,[ITEM_KEY]
		,[REVISION]
		,[PREV_REVISION]
		,[STATUS]
		,[ZZ_SYSTIME]
        ,[I_SEQUENCE]
        ,[IS_LATEST]
	)
	SELECT 
		ITM.ZZ_ID, 
		ITM.ITEM_KEY,
		ITM.REVISION,
		ITM.PREV_REVISION,
		ITM.STATUS,
		ITM.ZZ_SYSTIME,
		ROW_NUMBER() OVER 
			(
				PARTITION BY UPPER(ITEM_KEY) 
				ORDER BY 
				--CASE 
				--	WHEN STATUS = 7 THEN 1
				--	WHEN STATUS = 8 THEN 1
				--	WHEN STATUS = 0 THEN 2
				--	ELSE 3
				--END ASC,
				--ZZ_SYSTIME ASC,
				ZZ_ID
			) 
			AS SequenceNumber, 
		'0'
	FROM ITEM ITM
	WHERE ITM.OBJECT_TYPE = 0

	UPDATE T1
	SET 
		T1.IS_LATEST = 1
	FROM dbo.MIG_ITEM_SEQ_INFO AS T1
	INNER JOIN 
	(
		SELECT T.ITEM_KEY, MAX(I_SEQUENCE) AS "MAX_I_SEQUENCE"
		FROM MIG_ITEM_SEQ_INFO T
		GROUP BY T.ITEM_KEY
	) T2
	  ON T2.ITEM_KEY = T1.ITEM_KEY
	 AND T2.MAX_I_SEQUENCE = T1.I_SEQUENCE
END
