--EXEC MIG_VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_DOC_Sp '', ''
CREATE OR ALTER PROCEDURE MIG_VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_DOC_Sp
(
	@STATUS INT = 0 OUT,
	@MESSAGE NVARCHAR(999) = NULL OUT
)
AS 
BEGIN
	DECLARE @CNT_DOCS INT
	
	SET @STATUS = 0
	SET @MESSAGE = NULL

	SELECT @CNT_DOCS = COUNT(*)
	FROM
	(
		SELECT 
			A.ZZ_ID AS ZZ_ID_A, 
			A.DOC_KEY AS DOC_KEY_A, 
			A.REVISION AS REVISION_A, 
			ROW_NUMBER() OVER ( PARTITION BY UPPER(A.DOC_KEY) ORDER BY A.ZZ_ID ) AS ROW_COUNT_A
		FROM DOC A
	) T1
	JOIN
	(
		SELECT 
			B.ZZ_ID AS ZZ_ID_B, 
			B.DOC_KEY AS DOC_KEY_B, 
			B.REVISION AS REVISION_B, 
			ROW_NUMBER() OVER ( PARTITION BY UPPER(B.DOC_KEY) ORDER BY B.ZZ_SYSTIME ) AS ROW_COUNT_B
		FROM DOC B
	) T2
	ON
	T2.DOC_KEY_B = T1.DOC_KEY_A AND T2.REVISION_B = T1.REVISION_A
	WHERE T1.ROW_COUNT_A <> T2.ROW_COUNT_B

	IF @CNT_DOCS > 0
		BEGIN
			SET @STATUS = 1
			IF @MESSAGE IS NULL
				SET @MESSAGE = ''

			SET @MESSAGE = @MESSAGE + 'Order of REVISIONS for DOCS do not match as per ZZ_ID and ZZ_SYSTIME. '
		END

	SELECT @STATUS, @MESSAGE
END

