CREATE OR ALTER PROCEDURE MIG_ANZ_PRE_CHECKS_ALL_Sp
AS 
BEGIN
	DECLARE @COUNTER INT
	DECLARE @COUNT_RECORDS INT
	DECLARE @STATUS INT
	DECLARE @MESSAGE NVARCHAR(999)
	
	SET @COUNTER = 0
	SET @STATUS = 0
	SET @MESSAGE = NULL

	--------------------------------------------------------------------------------------------
	DELETE FROM MIG_ANZ_PRECHECKS

	--------------------------------------------------------------------------------------------
	-- MIG_MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_DOC_Sp --
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM DOC D
	JOIN
	(
	   SELECT DOC_KEY
	   FROM DOC
	   WHERE PREV_REVISION = ''
	   GROUP BY DOC_KEY HAVING COUNT(*) > 1
	) T
	ON T.DOC_KEY = D.DOC_KEY
	WHERE D.PREV_REVISION = ''

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_DOC', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'PREV_REVISION is empty for more than one revisions of the Document objects.' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_FOLDER_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM FOLDER D
	JOIN
	(
	   SELECT FOLDER_KEY
	   FROM FOLDER
	   WHERE PREV_REVISION = ''
	   GROUP BY FOLDER_KEY HAVING COUNT(*) > 1
	) T
	ON T.FOLDER_KEY = D.FOLDER_KEY
	WHERE D.PREV_REVISION = ''

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_FOLDER', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'PREV_REVISION is empty for more than one revisions of the Folder objects.' ELSE '' 
		END
	)
		
	--------------------------------------------------------------------------------------------
	-- MIG_MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_ITEM_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM ITEM D
	JOIN
	(
	   SELECT ITEM_KEY
	   FROM ITEM
	   WHERE PREV_REVISION = ''
	   GROUP BY ITEM_KEY HAVING COUNT(*) > 1
	) T
	ON T.ITEM_KEY = D.ITEM_KEY
	WHERE D.PREV_REVISION = ''

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'MULTIPLE_REVISIONS_WITH_PREV_REVISION_EMPTY_ITEM', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'PREV_REVISION is empty for more than one revisions of the Item objects.' ELSE '' 
		END
	)
		
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_BOM_LINES_LEGAL_CHARACTERS_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM ITEM_PS_DEF 
	WHERE SUBSTRING(FIND_NO, 1, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')
	OR SUBSTRING(FIND_NO, 2, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')
	OR SUBSTRING(FIND_NO, 3, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')
	OR SUBSTRING(FIND_NO, 4, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_BOM_LINES_LEGAL_CHARACTERS', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'BOM Lines contain illegal character (legal characters are 0, 1, 2... 8, 9).' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_DOC_Sp
	SELECT @COUNT_RECORDS = 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

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_DOC', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Order of revisions for Document objects do not match as per ZZ_ID and ZZ_SYSTIME.' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_FOLDER_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM
	(
		SELECT 
			A.ZZ_ID AS ZZ_ID_A, 
			A.FOLDER_KEY AS FOLDER_KEY_A, 
			A.REVISION AS REVISION_A, 
			ROW_NUMBER() OVER ( PARTITION BY UPPER(A.FOLDER_KEY) ORDER BY A.ZZ_ID ) AS ROW_COUNT_A
		FROM FOLDER A
	) T1
	JOIN
	(
		SELECT 
			B.ZZ_ID AS ZZ_ID_B, 
			B.FOLDER_KEY AS FOLDER_KEY_B, 
			B.REVISION AS REVISION_B, 
			ROW_NUMBER() OVER ( PARTITION BY UPPER(B.FOLDER_KEY) ORDER BY B.ZZ_SYSTIME ) AS ROW_COUNT_B
		FROM FOLDER B
	) T2
	ON
	T2.FOLDER_KEY_B = T1.FOLDER_KEY_A AND T2.REVISION_B = T1.REVISION_A
	WHERE T1.ROW_COUNT_A <> T2.ROW_COUNT_B

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_FOLDER', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Order of revisions for Folder objects do not match as per ZZ_ID and ZZ_SYSTIME.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_ITEM_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM
	(
		SELECT 
			A.ZZ_ID AS ZZ_ID_A, 
			A.ITEM_KEY AS ITEM_KEY_A, 
			A.REVISION AS REVISION_A, 
			ROW_NUMBER() OVER ( PARTITION BY UPPER(A.ITEM_KEY) ORDER BY A.ZZ_ID ) AS ROW_COUNT_A
		FROM ITEM A
	) T1
	JOIN
	(
		SELECT 
			B.ZZ_ID AS ZZ_ID_B, 
			B.ITEM_KEY AS ITEM_KEY_B, 
			B.REVISION AS REVISION_B, 
			ROW_NUMBER() OVER ( PARTITION BY UPPER(B.ITEM_KEY) ORDER BY B.ZZ_SYSTIME ) AS ROW_COUNT_B
		FROM ITEM B
	) T2
	ON
	T2.ITEM_KEY_B = T1.ITEM_KEY_A AND T2.REVISION_B = T1.REVISION_A
	WHERE T1.ROW_COUNT_A <> T2.ROW_COUNT_B

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_REVISIONS_ORDER_BY_ZZ_ID_AND_BY_ZZ_SYSTIME_ITEM', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Order of revisions for Item objects do not match as per ZZ_ID and ZZ_SYSTIME.' ELSE '' 
		END
	)
		
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_DOC_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
    FROM
    (
           SELECT 
                  ZZ_ID,
                  DOC_KEY_FROM, 
                  REVISION_FROM, 
                  DOC_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(DOC_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_DOC 
    ) T1
    JOIN
    (
           SELECT 
                  ZZ_ID,
                  DOC_KEY_FROM, 
                  REVISION_FROM, 
                  DOC_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(DOC_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_DOC 
    ) T2
    ON  T2.DOC_KEY_FROM = T1.DOC_KEY_FROM 
    AND T2.REVISION_FROM = T1.REVISION_FROM
    AND T2.DOC_KEY_TO = T1.DOC_KEY_TO 
    AND T2.REVISION_TO = T1.REVISION_TO
    AND T2.CHECK_TYPE = T1.CHECK_TYPE
    AND T2.CHECKED_DATE = T1.CHECKED_DATE
    AND T2.CHECKED_TIME = T1.CHECKED_TIME
    AND T2.ZZ_ID <> T1.ZZ_ID
    AND T2.ROW_COUNT = (T1.ROW_COUNT + 1)
    AND T2.UPDATED_ON = T1.UPDATED_ON
    AND T2.ZZ_ID > T1.ZZ_ID

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_DOC', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Consecutive entries in CHECK_DOC tables are similar and differing only in milliseconds (in terms of precision).' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_FOLDER_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
    FROM
    (
           SELECT 
                  ZZ_ID,
                  FOLDER_KEY_FROM, 
                  REVISION_FROM, 
                  FOLDER_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(FOLDER_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_FOLDER 
    ) T1
    JOIN
    (
           SELECT 
                  ZZ_ID,
                  FOLDER_KEY_FROM, 
                  REVISION_FROM, 
                  FOLDER_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(FOLDER_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_FOLDER 
    ) T2
    ON  T2.FOLDER_KEY_FROM = T1.FOLDER_KEY_FROM 
    AND T2.REVISION_FROM = T1.REVISION_FROM
    AND T2.FOLDER_KEY_TO = T1.FOLDER_KEY_TO 
    AND T2.REVISION_TO = T1.REVISION_TO
    AND T2.CHECK_TYPE = T1.CHECK_TYPE
    AND T2.CHECKED_DATE = T1.CHECKED_DATE
    AND T2.CHECKED_TIME = T1.CHECKED_TIME
    AND T2.ZZ_ID <> T1.ZZ_ID
    AND T2.ROW_COUNT = (T1.ROW_COUNT + 1)
    AND T2.UPDATED_ON = T1.UPDATED_ON
    AND T2.ZZ_ID > T1.ZZ_ID

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_FOLDER', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Consecutive entries in CHECK_FOLDER tables are similar and differing only in milliseconds (in terms of precision).' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_ITEM_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
    FROM
    (
           SELECT 
                  ZZ_ID,
                  ITEM_KEY_FROM, 
                  REVISION_FROM, 
                  ITEM_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(ITEM_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_ITEM 
    ) T1
    JOIN
    (
           SELECT 
                  ZZ_ID,
                  ITEM_KEY_FROM, 
                  REVISION_FROM, 
                  ITEM_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(ITEM_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_ITEM 
    ) T2
    ON  T2.ITEM_KEY_FROM = T1.ITEM_KEY_FROM 
    AND T2.REVISION_FROM = T1.REVISION_FROM
    AND T2.ITEM_KEY_TO = T1.ITEM_KEY_TO 
    AND T2.REVISION_TO = T1.REVISION_TO
    AND T2.CHECK_TYPE = T1.CHECK_TYPE
    AND T2.CHECKED_DATE = T1.CHECKED_DATE
    AND T2.CHECKED_TIME = T1.CHECKED_TIME
    AND T2.ZZ_ID <> T1.ZZ_ID
    AND T2.ROW_COUNT = (T1.ROW_COUNT + 1)
    AND T2.UPDATED_ON = T1.UPDATED_ON
    AND T2.ZZ_ID > T1.ZZ_ID

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_ITEM', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Consecutive entries in CHECK_ITEM tables are similar and differing only in milliseconds (in terms of precision).' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_SITE_ENTRIES_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	SITE

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_VAULTS_COUNT', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'The number of vaults is greater than 20 (Max allowed).' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_ITEM_UNDER_TOP_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	ITEM
	WHERE	STATUS = 10

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_ITEM_UNDER_TOP', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Items with Status "UNDER_TOP" are detected.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_MAPPING_TEMPLATE_LENGTH_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM MAPPING_RULES 
	WHERE LEN(TEMPLATE) > 50

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_MAPPING_TEMPLATE_LENGTH', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Mapping Template Name exceeds 50 characters.' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_MATERIAL_ENTRIES_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	ITEM
	WHERE	OBJECT_TYPE = 74

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_MATERIAL_ENTRIES', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Material entries are detected.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_THUMBNAIL_TYPES_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	FILE_IMAGES 
	WHERE	UPPER(THUMBNAIL_TYPE) NOT IN ('JPEG', 'PNG')

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_THUMBNAIL_TYPES', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Thumbnails found with types other than JPEG and PNG.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_FILE_NAME_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	DATASET
	WHERE	LEN(DSET_NAME) > 250

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_FILE_NAME', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'File Name(s) with Length greater than 250 are detected.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_FILE_TYPE_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	DATASET 
	WHERE OBJECT_TYPE NOT IN ('12', '14') 

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_FILE_TYPE', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'File records found with type other than 12 (File) and 14 (EO Comment).' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_SEC_RULES_OBJECTS_Sp
	SELECT	@COUNT_RECORDS = COUNT(*)
	FROM	SEC_RULES
	LEFT JOIN
	(
		SELECT	'ITEM'		AS 'OBJ_ID',	''				AS 'LINK_ID'		--pdpdm100
		UNION
		SELECT	'DOC'		AS 'OBJ_ID',	''				AS 'LINK_ID'		--pdpdm200
		UNION
		SELECT	'FOLDER'	AS 'OBJ_ID',	''				AS 'LINK_ID'		--pdpdm300
	) T 
	 ON T.OBJ_ID = SEC_RULES.OBJ_ID
	AND T.LINK_ID = SEC_RULES.LINK_ID
	WHERE T.OBJ_ID IS NULL

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_SEC_RULES_OBJECTS', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Security Rules found that are not supported in target application.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_WAREHOUSE_NAME_Sp
	SELECT @COUNT_RECORDS = COUNT(*) 
	FROM ZZ_UDCDTYPE 
	WHERE TYPE_NAME = 'UNCH_WAREHOUSE' 
	  AND LEN(TYPE_CODE) > 6

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_WAREHOUSE_NAME', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Warehouse Name with length > 6 detected. Correct them.' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_MAPPING_RULES_OBJ_ID_Sp
	SELECT @COUNT_RECORDS = COUNT(*)
	FROM MAPPING_RULES
	WHERE PDM_OBJ_ID NOT IN 
	(
		--'CUSTOMER',
		'DOC',
		'DOC_CLASSIFICATION',
		'DOC_VAULT_HISTORY',
		'EO',
		'FILE',
		'ITEM',
		'ITEM_CLASSIFICATION',
		'ITEM_VAULT_HISTORY',
		--'MANUFACTURERS',
		'PROJECT' --,
		--'SUB_CONTRACTOR',
		--'SUPPLIER'
	)
	AND PDM_OBJ_ID NOT LIKE 'EO_HISTORY %'

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_MAPPING_RULES_OBJ_ID', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Mapping Rules found for objects found that are not supported.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_DUPLICATE_DOC_TYPE_SHORT_DESC_Sp
	SELECT @COUNT_RECORDS = COUNT(SHORT_DESCR)
	FROM ZZ_UDCDTYPE
	WHERE TYPE_NAME LIKE '%DOC_TYPE%'
	GROUP BY SHORT_DESCR 
	HAVING COUNT(SHORT_DESCR) > 1

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_DUPLICATE_DOC_TYPE_SHORT_DESC', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'DOCUMENT_TYPE (DOC_TYPE) have duplicate entries.' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_CATEGORY_ATTR_TYPE_Sp
	SELECT @COUNT_RECORDS = COUNT(*) 
	FROM CATEGORY_ATTRS
	WHERE ATTR_TYPE = 	(	SELECT VALUE_ID 
							FROM ZZ_UDCDTYPE 
							WHERE TYPE_NAME = 'UNCH_CATEGORY_ATTR_TYPE' 
							AND SHORT_DESCR = 'CLASS_REF_TYPE'
						)

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_CATEGORY_ATTR_TYPE', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Category Attributes of type CLASS_REF_TYPE not found.' ELSE '' 
		END
	)

	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_DUPL_CATEGORY_ATTRIBUTES_Sp
	DECLARE @Parent VARCHAR(50)
	DECLARE @CATEGORY_ID NVARCHAR(32)
	DECLARE @ITR INT = 0
	DECLARE @TOT_CNT INT

	SELECT @TOT_CNT = MAX(ZZ_ID) FROM CATEGORY_TREE

	WHILE @ITR < @TOT_CNT
	BEGIN
		SELECT @CATEGORY_ID = CATEGORY_ID FROM CATEGORY_TREE WHERE ZZ_ID = @ITR

		SELECT @Parent=PARENT_ID 
		FROM CATEGORY_TREE 
		WHERE CATEGORY_ID=@CATEGORY_ID  
	
		SELECT CATEGORY_ID INTO #tempTbl FROM CATEGORY_TREE WHERE PARENT_ID IS NULL

		INSERT INTO #tempTbl  
		SELECT CATEGORY_ID     
		FROM CATEGORY_TREE     
		WHERE CATEGORY_ID = @CATEGORY_ID  

		INSERT INTO #tempTbl  
		SELECT PARENT_ID     
		FROM CATEGORY_TREE     
		WHERE CATEGORY_ID = @CATEGORY_ID  
		
		WHILE (@Parent <> '')  
		BEGIN  
			INSERT INTO #tempTbl  
			SELECT PARENT_ID     
			FROM CATEGORY_TREE     
			WHERE CATEGORY_ID = @Parent  
  
			SELECT @Parent= PARENT_ID FROM CATEGORY_TREE WHERE CATEGORY_ID = @Parent  
		END  

		SELECT @COUNT_RECORDS = COUNT(*)
		FROM CATEGORY_ATTRS
		WHERE CATEGORY_ID IN (SELECT CATEGORY_ID FROM #tempTbl)
		GROUP BY ATTR_NAME
		HAVING COUNT(ATTR_NAME) > 1

		DROP TABLE #tempTbl
		
		IF @COUNT_RECORDS > 0
			BEGIN
				BREAK
			END
		SET @ITR = @ITR + 1
	END

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_DUPL_CATEGORY_ATTRIBUTES', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Duplicate Category Attribute Names are detected.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------
	-- MIG_VALIDATE_PARENT_CATEGORY_Sp
	SELECT @COUNT_RECORDS = COUNT(CATEGORY_ID) 
	FROM CATEGORY_TREE 
	WHERE CATEGORY_ID = '000001' 

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_ITEM_PARENT_CATEGORY', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'CategoryID 000001 "Item Classification" not found.' ELSE '' 
		END
	)

	SELECT @COUNT_RECORDS = COUNT(CATEGORY_ID) 
	FROM CATEGORY_TREE 
	WHERE CATEGORY_ID = '000002' 

	SET @COUNTER = @COUNTER + 1
	INSERT INTO MIG_ANZ_PRECHECKS
	(
		[ID],
		[CHECK], 
		[DETAILS], 
		[STATUS], 
		[MESSAGE]
	)
	VALUES
	(
		@COUNTER,
		'VALIDATE_DOC_PARENT_CATEGORY', 
		'', 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'Failed' ELSE 'Passed' 
		END, 
		CASE 
			WHEN @COUNT_RECORDS > 0 THEN 'CategoryID 000002 "Document Classification" not found.' ELSE '' 
		END
	)
	
	--------------------------------------------------------------------------------------------

END

-- exec MIG_ANZ_PRE_CHECKS_ALL_Sp
-- SELECT * FROM MIG_ANZ_PRECHECKS ORDER BY ID
