CREATE OR ALTER PROCEDURE [MIG_ANZ_METADATA_ANALYSIS_Sp] @REPORT_TYPE nvarchar(255)
AS BEGIN

	IF @REPORT_TYPE = 'LIST_TYPE_MISMATCHES_STATISTICS'
		-- Statistics on list types with mismatches (differs)
		SELECT 
			T.[TYPE_NAME],
			SUM(	
					CASE 
						WHEN T.STD_VALUE_ID IS NOT NULL 
							 AND T.CUS_VALUE_ID IS NOT NULL THEN 
							1 
						ELSE 
							0 
						END
				) AS "LIST_VALUES_MISMATCH",
			SUM(CASE WHEN T.STD_VALUE_ID IS NULL THEN 1 ELSE 0 END) AS "NEWLY_ADDED_LIST_VALUES", 
			SUM(CASE WHEN T.CUS_VALUE_ID IS NULL THEN 1 ELSE 0 END) AS "MISSING_LIST_VALUES",
			dbo.MIG_ANZ_GET_FIELDS_BY_UDTP_TYPE_NAME_Fn('ZZ_CLASS_ATTR', T.[TYPE_NAME]) AS "ZZ_CLASS_ATTR_ATTR_NAME",
			dbo.MIG_ANZ_GET_FIELDS_BY_UDTP_TYPE_NAME_Fn('OBJ_DEF_ATTR', T.[TYPE_NAME]) AS "OBJ_DEF_ATTR_ATTR_NAME"
		FROM
		(
			SELECT 
				STD.[TYPE_NAME], 
				STD_VALUES.VALUE_ID AS "STD_VALUE_ID", 
				STD_VALUES.[DESCRIPTION] AS "STD_VALUE_DESCRIPTION", 
				CUS_VALUES.VALUE_ID AS "CUS_VALUE_ID", 
				CUS_VALUES.[DESCRIPTION] AS "CUS_VALUE_DESCRIPTION"
			FROM MIG_ANZ_SOURCE_LIST AS STD
			JOIN MIG_ANZ_SOURCE_LIST AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[TYPE_NAME] = STD.[TYPE_NAME]
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS STD_VALUES
			  ON STD_VALUES.TYPE = 'Standard'
			 AND STD_VALUES.TYPE_NAME = STD.TYPE_NAME
			LEFT JOIN MIG_ANZ_SOURCE_LIST_VALUES AS CUS_VALUES
			  ON CUS_VALUES.TYPE = 'Custom'
			 AND CUS_VALUES.TYPE_NAME = CUS.TYPE_NAME
			 AND CUS_VALUES.VALUE_ID = STD_VALUES.VALUE_ID
			WHERE CUS_VALUES.VALUE_ID IS NULL 
	
			UNION
	
			SELECT 
				STD.[TYPE_NAME], 
				STD_VALUES.VALUE_ID AS "STD_VALUE_ID", 
				STD_VALUES.[DESCRIPTION] AS "STD_VALUE_DESCRIPTION", 
				CUS_VALUES.VALUE_ID AS "CUS_VALUE_ID", 
				CUS_VALUES.[DESCRIPTION] AS "CUS_VALUE_DESCRIPTION"
			FROM MIG_ANZ_SOURCE_LIST AS CUS
			JOIN MIG_ANZ_SOURCE_LIST AS STD
			  ON CUS.TYPE = 'Standard'
			 AND STD.TYPE = 'Custom'
			 AND STD.[TYPE_NAME] = CUS.[TYPE_NAME]
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS CUS_VALUES
			  ON CUS_VALUES.TYPE = 'Custom'
			 AND CUS_VALUES.TYPE_NAME = CUS.TYPE_NAME
			LEFT JOIN MIG_ANZ_SOURCE_LIST_VALUES AS STD_VALUES
			  ON STD_VALUES.TYPE = 'Standard'
			 AND STD_VALUES.TYPE_NAME = STD.TYPE_NAME
			 AND STD_VALUES.VALUE_ID = CUS_VALUES.VALUE_ID
			WHERE STD_VALUES.VALUE_ID IS NULL 
	
			UNION
	
			SELECT 
				STD.[TYPE_NAME], 
				STD_VALUES.VALUE_ID AS "STD_VALUE_ID", 
				STD_VALUES.[DESCRIPTION] AS "STD_VALUE_DESCRIPTION", 
				CUS_VALUES.VALUE_ID AS "CUS_VALUE_ID", 
				CUS_VALUES.[DESCRIPTION] AS "CUS_VALUE_DESCRIPTION"
			FROM MIG_ANZ_SOURCE_LIST AS STD
			JOIN MIG_ANZ_SOURCE_LIST AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[TYPE_NAME] = STD.[TYPE_NAME]
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS STD_VALUES
			  ON STD_VALUES.TYPE = 'Standard'
			 AND STD_VALUES.TYPE_NAME = STD.TYPE_NAME
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS CUS_VALUES
			  ON CUS_VALUES.TYPE = 'Custom'
			 AND CUS_VALUES.TYPE_NAME = CUS.TYPE_NAME
			 AND CUS_VALUES.VALUE_ID = STD_VALUES.VALUE_ID
			WHERE CUS_VALUES.DESCRIPTION != STD_VALUES.DESCRIPTION
		) T
		GROUP BY T.TYPE_NAME
		ORDER BY T.TYPE_NAME
	ELSE IF @REPORT_TYPE = 'LIST_TYPE_MISMATCHES_DETAILS'
		-- List types with mismatches (differs) along with list values information
		SELECT 
			T.[TYPE_NAME],
			T.STD_VALUE_ID,
			T.STD_VALUE_DESCRIPTION,
			T.CUS_VALUE_ID,
			T.CUS_VALUE_DESCRIPTION
		FROM
		(
			SELECT 
				STD.[TYPE_NAME], 
				STD_VALUES.VALUE_ID AS "STD_VALUE_ID", 
				STD_VALUES.[DESCRIPTION] AS "STD_VALUE_DESCRIPTION", 
				CUS_VALUES.VALUE_ID AS "CUS_VALUE_ID", 
				CUS_VALUES.[DESCRIPTION] AS "CUS_VALUE_DESCRIPTION"
			FROM MIG_ANZ_SOURCE_LIST AS STD
			JOIN MIG_ANZ_SOURCE_LIST AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[TYPE_NAME] = STD.[TYPE_NAME]
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS STD_VALUES
			  ON STD_VALUES.TYPE = 'Standard'
			 AND STD_VALUES.TYPE_NAME = STD.TYPE_NAME
			LEFT JOIN MIG_ANZ_SOURCE_LIST_VALUES AS CUS_VALUES
			  ON CUS_VALUES.TYPE = 'Custom'
			 AND CUS_VALUES.TYPE_NAME = CUS.TYPE_NAME
			 AND CUS_VALUES.VALUE_ID = STD_VALUES.VALUE_ID
			WHERE CUS_VALUES.VALUE_ID IS NULL 
	
			UNION
	
			SELECT 
				STD.[TYPE_NAME], 
				STD_VALUES.VALUE_ID AS "STD_VALUE_ID", 
				STD_VALUES.[DESCRIPTION] AS "STD_VALUE_DESCRIPTION", 
				CUS_VALUES.VALUE_ID AS "CUS_VALUE_ID", 
				CUS_VALUES.[DESCRIPTION] AS "CUS_VALUE_DESCRIPTION"
			FROM MIG_ANZ_SOURCE_LIST AS CUS
			JOIN MIG_ANZ_SOURCE_LIST AS STD
			  ON CUS.TYPE = 'Standard'
			 AND STD.TYPE = 'Custom'
			 AND STD.[TYPE_NAME] = CUS.[TYPE_NAME]
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS CUS_VALUES
			  ON CUS_VALUES.TYPE = 'Custom'
			 AND CUS_VALUES.TYPE_NAME = CUS.TYPE_NAME
			LEFT JOIN MIG_ANZ_SOURCE_LIST_VALUES AS STD_VALUES
			  ON STD_VALUES.TYPE = 'Standard'
			 AND STD_VALUES.TYPE_NAME = STD.TYPE_NAME
			 AND STD_VALUES.VALUE_ID = CUS_VALUES.VALUE_ID
			WHERE STD_VALUES.VALUE_ID IS NULL 
	
			UNION
	
			SELECT 
				STD.[TYPE_NAME], 
				STD_VALUES.VALUE_ID AS "STD_VALUE_ID", 
				STD_VALUES.[DESCRIPTION] AS "STD_VALUE_DESCRIPTION", 
				CUS_VALUES.VALUE_ID AS "CUS_VALUE_ID", 
				CUS_VALUES.[DESCRIPTION] AS "CUS_VALUE_DESCRIPTION"
			FROM MIG_ANZ_SOURCE_LIST AS STD
			JOIN MIG_ANZ_SOURCE_LIST AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[TYPE_NAME] = STD.[TYPE_NAME]
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS STD_VALUES
			  ON STD_VALUES.TYPE = 'Standard'
			 AND STD_VALUES.TYPE_NAME = STD.TYPE_NAME
			JOIN MIG_ANZ_SOURCE_LIST_VALUES AS CUS_VALUES
			  ON CUS_VALUES.TYPE = 'Custom'
			 AND CUS_VALUES.TYPE_NAME = CUS.TYPE_NAME
			 AND CUS_VALUES.VALUE_ID = STD_VALUES.VALUE_ID
			WHERE CUS_VALUES.DESCRIPTION != STD_VALUES.DESCRIPTION
		) T
		ORDER BY T.TYPE_NAME, T.STD_VALUE_ID, T.CUS_VALUE_ID
	ELSE IF @REPORT_TYPE = 'LIST_TYPES_MISSING'
		-- Missing Standard List Types
		SELECT 
			STD.[TYPE], 
			STD.[TYPE_NAME], 
			dbo.MIG_ANZ_GET_FIELDS_BY_UDTP_TYPE_NAME_Fn('ZZ_CLASS_ATTR', STD.[TYPE_NAME]) AS "ZZ_CLASS_ATTR_ATTR_NAME",
			dbo.MIG_ANZ_GET_FIELDS_BY_UDTP_TYPE_NAME_Fn('OBJ_DEF_ATTR', STD.[TYPE_NAME]) AS "OBJ_DEF_ATTR_ATTR_NAME"
		FROM MIG_ANZ_SOURCE_LIST AS STD
		LEFT JOIN MIG_ANZ_SOURCE_LIST AS CUS
		 ON CUS.[TYPE_NAME] = STD.[TYPE_NAME]
		 AND CUS.[TYPE] = 'Custom'
		WHERE CUS.[TYPE_NAME] IS NULL
		 AND STD.[TYPE] = 'Standard'
	ELSE IF @REPORT_TYPE = 'LIST_TYPES_NEWLY_ADDED'
		-- Newly Added Customized List Types
		SELECT 
			CUS.[TYPE], 
			CUS.[TYPE_NAME], 
			dbo.MIG_ANZ_GET_FIELDS_BY_UDTP_TYPE_NAME_Fn('ZZ_CLASS_ATTR', CUS.[TYPE_NAME]) AS "ZZ_CLASS_ATTR_ATTR_NAME",
			dbo.MIG_ANZ_GET_FIELDS_BY_UDTP_TYPE_NAME_Fn('OBJ_DEF_ATTR', CUS.[TYPE_NAME]) AS "OBJ_DEF_ATTR_ATTR_NAME"
		FROM MIG_ANZ_SOURCE_LIST AS CUS
		LEFT JOIN MIG_ANZ_SOURCE_LIST AS STD
		 ON STD.[TYPE_NAME] = CUS.[TYPE_NAME]
		 AND STD.[TYPE] = 'Standard'
		WHERE STD.[TYPE_NAME] IS NULL
		 AND CUS.[TYPE] = 'Custom'
	ELSE IF @REPORT_TYPE = 'TABLES_MISMATCHES_STATISTICS'
		-- Statistics on tables with mismatches (differs)
		SELECT 
			T.[NAME],
			SUM(	
					CASE 
						WHEN T.STD_FIELD_NAME IS NOT NULL 
							 AND T.CUS_FIELD_NAME IS NOT NULL THEN 
							1 
						ELSE 
							0 
						END
				) AS "FIELDS_MISMATCH",
			SUM(CASE WHEN T.STD_FIELD_NAME IS NULL THEN 1 ELSE 0 END) AS "NEWLY_ADDED_FIELD", 
			SUM(CASE WHEN T.CUS_FIELD_NAME IS NULL THEN 1 ELSE 0 END) AS "MISSING_FIELDS"
		FROM
		(
			SELECT 
				STD.[NAME], 
				STD_FIELDS.[NAME] AS "STD_FIELD_NAME",
				CUS_FIELDS.[NAME] AS "CUS_FIELD_NAME"
			FROM MIG_ANZ_SOURCE_TABLE AS STD
			JOIN MIG_ANZ_SOURCE_TABLE AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[NAME] = STD.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS STD_FIELDS
			  ON STD_FIELDS.TYPE = 'Standard'
			 AND STD_FIELDS.TABLE_NAME = STD.[NAME]
			LEFT JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS CUS_FIELDS
			  ON CUS_FIELDS.TYPE = 'Custom'
			 AND CUS_FIELDS.TABLE_NAME = CUS.[NAME]
			 AND CUS_FIELDS.[NAME] = STD_FIELDS.[NAME]
			WHERE CUS_FIELDS.[NAME] IS NULL 
	
			UNION
	
			SELECT 
				STD.[NAME], 
				STD_FIELDS.[NAME] AS "STD_FIELD_NAME",
				CUS_FIELDS.[NAME] AS "CUS_FIELD_NAME"
			FROM MIG_ANZ_SOURCE_TABLE AS CUS
			JOIN MIG_ANZ_SOURCE_TABLE AS STD
			  ON CUS.TYPE = 'Standard'
			 AND STD.TYPE = 'Custom'
			 AND STD.[NAME] = CUS.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS CUS_FIELDS
			  ON CUS_FIELDS.TYPE = 'Custom'
			 AND CUS_FIELDS.TABLE_NAME = CUS.[NAME]
			LEFT JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS STD_FIELDS
			  ON STD_FIELDS.TYPE = 'Standard'
			 AND STD_FIELDS.TABLE_NAME = STD.[NAME]
			 AND STD_FIELDS.[NAME] = CUS_FIELDS.[NAME]
			WHERE STD_FIELDS.[NAME] IS NULL 
	
			UNION
	
			SELECT 
				STD.[NAME], 
				STD_FIELDS.[NAME] AS "STD_FIELD_NAME",
				CUS_FIELDS.[NAME] AS "CUS_FIELD_NAME"
			FROM MIG_ANZ_SOURCE_TABLE AS STD
			JOIN MIG_ANZ_SOURCE_TABLE AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[NAME] = STD.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS STD_FIELDS
			  ON STD_FIELDS.TYPE = 'Standard'
			 AND STD_FIELDS.TABLE_NAME = STD.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS CUS_FIELDS
			  ON CUS_FIELDS.TYPE = 'Custom'
			 AND CUS_FIELDS.TABLE_NAME = CUS.[NAME]
			 AND CUS_FIELDS.[NAME] = STD_FIELDS.[NAME]
			WHERE CUS_FIELDS.DATA_TYPE != STD_FIELDS.DATA_TYPE 
				OR CUS_FIELDS.ATTR_TYPE != STD_FIELDS.ATTR_TYPE
				OR CUS_FIELDS.CHAR_MAX_LENGTH != STD_FIELDS.CHAR_MAX_LENGTH
		) T
		GROUP BY T.[NAME]
		ORDER BY T.[NAME]
	ELSE IF @REPORT_TYPE = 'TABLES_MISMATCHES_DETAILS'
		-- Tables with mismatches (differs) along with fields information
		SELECT 
			T.[NAME],
			T.STD_FIELD_NAME, 
			T.CUS_FIELD_NAME,
			T.STD_ATTR_TYPE,
			T.STD_CHAR_MAX_LENGTH,
			T.CUS_ATTR_TYPE,
			T.CUS_CHAR_MAX_LENGTH
		FROM
		(
			SELECT 
				STD.[NAME], 
				STD_FIELDS.[NAME] AS "STD_FIELD_NAME",
				STD_FIELDS.[ATTR_TYPE] AS "STD_ATTR_TYPE",
				STD_FIELDS.[CHAR_MAX_LENGTH] AS "STD_CHAR_MAX_LENGTH",
				CUS_FIELDS.[NAME] AS "CUS_FIELD_NAME",
				CUS_FIELDS.[ATTR_TYPE] AS "CUS_ATTR_TYPE",
				CUS_FIELDS.[CHAR_MAX_LENGTH] AS "CUS_CHAR_MAX_LENGTH"
			FROM MIG_ANZ_SOURCE_TABLE AS STD
			JOIN MIG_ANZ_SOURCE_TABLE AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[NAME] = STD.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS STD_FIELDS
			  ON STD_FIELDS.TYPE = 'Standard'
			 AND STD_FIELDS.TABLE_NAME = STD.[NAME]
			LEFT JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS CUS_FIELDS
			  ON CUS_FIELDS.TYPE = 'Custom'
			 AND CUS_FIELDS.TABLE_NAME = CUS.[NAME]
			 AND CUS_FIELDS.[NAME] = STD_FIELDS.[NAME]
			WHERE CUS_FIELDS.[NAME] IS NULL 
	
			UNION
	
			SELECT 
				STD.[NAME], 
				STD_FIELDS.[NAME] AS "STD_FIELD_NAME",
				STD_FIELDS.[ATTR_TYPE] AS "STD_ATTR_TYPE",
				STD_FIELDS.[CHAR_MAX_LENGTH] AS "STD_CHAR_MAX_LENGTH",
				CUS_FIELDS.[NAME] AS "CUS_FIELD_NAME",
				CUS_FIELDS.[ATTR_TYPE] AS "CUS_ATTR_TYPE",
				CUS_FIELDS.[CHAR_MAX_LENGTH] AS "CUS_CHAR_MAX_LENGTH"
			FROM MIG_ANZ_SOURCE_TABLE AS CUS
			JOIN MIG_ANZ_SOURCE_TABLE AS STD
			  ON CUS.TYPE = 'Standard'
			 AND STD.TYPE = 'Custom'
			 AND STD.[NAME] = CUS.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS CUS_FIELDS
			  ON CUS_FIELDS.TYPE = 'Custom'
			 AND CUS_FIELDS.TABLE_NAME = CUS.[NAME]
			LEFT JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS STD_FIELDS
			  ON STD_FIELDS.TYPE = 'Standard'
			 AND STD_FIELDS.TABLE_NAME = STD.[NAME]
			 AND STD_FIELDS.[NAME] = CUS_FIELDS.[NAME]
			WHERE STD_FIELDS.[NAME] IS NULL 
	
			UNION
	
			SELECT 
				STD.[NAME], 
				STD_FIELDS.[NAME] AS "STD_FIELD_NAME",
				STD_FIELDS.[ATTR_TYPE] AS "STD_ATTR_TYPE",
				STD_FIELDS.[CHAR_MAX_LENGTH] AS "STD_CHAR_MAX_LENGTH",
				CUS_FIELDS.[NAME] AS "CUS_FIELD_NAME",
				CUS_FIELDS.[ATTR_TYPE] AS "CUS_ATTR_TYPE",
				CUS_FIELDS.[CHAR_MAX_LENGTH] AS "CUS_CHAR_MAX_LENGTH"
			FROM MIG_ANZ_SOURCE_TABLE AS STD
			JOIN MIG_ANZ_SOURCE_TABLE AS CUS
			  ON STD.TYPE = 'Standard'
			 AND CUS.TYPE = 'Custom'
			 AND CUS.[NAME] = STD.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS STD_FIELDS
			  ON STD_FIELDS.TYPE = 'Standard'
			 AND STD_FIELDS.TABLE_NAME = STD.[NAME]
			JOIN MIG_ANZ_SOURCE_TABLE_FIELD AS CUS_FIELDS
			  ON CUS_FIELDS.TYPE = 'Custom'
			 AND CUS_FIELDS.TABLE_NAME = CUS.[NAME]
			 AND CUS_FIELDS.[NAME] = STD_FIELDS.[NAME]
			WHERE CUS_FIELDS.DATA_TYPE != STD_FIELDS.DATA_TYPE 
				OR CUS_FIELDS.ATTR_TYPE != STD_FIELDS.ATTR_TYPE
				OR CUS_FIELDS.CHAR_MAX_LENGTH != STD_FIELDS.CHAR_MAX_LENGTH
		) T
		ORDER BY T.[NAME], T.STD_FIELD_NAME, T.CUS_FIELD_NAME
	ELSE IF @REPORT_TYPE = 'TABLES_MISSING'
		-- Missing Standard Tables
		SELECT STD.[TYPE], STD.[NAME]
		FROM MIG_ANZ_SOURCE_TABLE AS STD
		LEFT JOIN MIG_ANZ_SOURCE_TABLE AS CUS
		 ON CUS.[NAME] = STD.[NAME]
		 AND CUS.[TYPE] = 'Custom'
		WHERE CUS.[NAME] IS NULL
		 AND STD.[TYPE] = 'Standard'
		ORDER BY STD.[TYPE], STD.[NAME]
	ELSE IF @REPORT_TYPE = 'TABLES_NEWLY_ADDED'
		-- Newly Added Tables
		SELECT CUS.[TYPE], CUS.[NAME]
		FROM MIG_ANZ_SOURCE_TABLE AS CUS
		LEFT JOIN MIG_ANZ_SOURCE_TABLE AS STD
		 ON STD.[NAME] = CUS.[NAME]
		 AND STD.[TYPE] = 'Standard'
		WHERE STD.[NAME] IS NULL
		 AND CUS.[TYPE] = 'Custom'
		ORDER BY CUS.[TYPE], CUS.[NAME]

END

-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'LIST_TYPE_MISMATCHES_STATISTICS'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'LIST_TYPE_MISMATCHES_DETAILS'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'LIST_TYPES_MISSING'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'LIST_TYPES_NEWLY_ADDED'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'TABLES_MISMATCHES_STATISTICS'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'TABLES_MISMATCHES_DETAILS'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'TABLES_MISSING'
-- exec MIG_ANZ_METADATA_ANALYSIS_Sp 'TABLES_NEWLY_ADDED'
