
-- EXEC MIG_FOLDER_SEQ_INFO_Sp
CREATE OR ALTER PROCEDURE MIG_FOLDER_SEQ_INFO_Sp
AS
BEGIN
	DELETE FROM MIG_FOLDER_SEQ_INFO

	INSERT INTO [MIG_FOLDER_SEQ_INFO]
	(
		 [ZZ_ID]
		,[FOLDER_KEY]
		,[REVISION]
		,[PREV_REVISION]
		,[STATUS]
		,[ZZ_SYSTIME]
        ,[I_SEQUENCE]
        ,[IS_LATEST]
	)
	SELECT 
		FLD.ZZ_ID, 
		FLD.FOLDER_KEY,
		FLD.REVISION,
		FLD.PREV_REVISION,
		FLD.STATUS,
		FLD.ZZ_SYSTIME,
		ROW_NUMBER() OVER 
			(
				PARTITION BY UPPER(FOLDER_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 FOLDER FLD
	--WHERE FLD.OBJECT_TYPE = 0

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

