
-- EXEC MIG_DOC_SEQ_INFO_Sp
CREATE OR ALTER PROCEDURE MIG_DOC_SEQ_INFO_Sp
AS
BEGIN
	DELETE FROM MIG_DOC_SEQ_INFO

	INSERT INTO [MIG_DOC_SEQ_INFO]
	(
		 [ZZ_ID]
		,[DOC_KEY]
		,[REVISION]
		,[PREV_REVISION]
		,[STATUS]
		,[ZZ_SYSTIME]
        ,[I_SEQUENCE]
        ,[IS_LATEST]
	)
	SELECT 
		D.ZZ_ID, 
		D.DOC_KEY,
		D.REVISION,
		D.PREV_REVISION,
		D.STATUS,
		D.ZZ_SYSTIME,
		ROW_NUMBER() OVER 
			(
				PARTITION BY UPPER(DOC_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 DOC D
	WHERE D.OBJECT_TYPE = 8

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