-- EXEC MIG_OBJ_CATEG_INST_Sp
CREATE OR ALTER PROCEDURE MIG_OBJ_CATEG_INST_Sp 
AS
BEGIN
	Declare @TEMP_CAT_TREE TABLE
	(
		CATEGORY_ID varchar(16),
		PARENT_CATEGORIES varchar(max) 
	)

	Declare @TEMP_OBJ_CATEG_INST TABLE
	(
		 ZZ_ID INT, 
		 --OBJECT_TYPE SMALLINT,
		 --OBJECT_ID VARCHAR(40), 
		 CATEGORY_ID VARCHAR(16), 
		 ATTR_NAME VARCHAR(80), 
		 ATTR_STRING_VALUE VARCHAR(255),
		 ATTR_NUMERIC_VALUE FLOAT
	)

	INSERT INTO @TEMP_CAT_TREE
	SELECT CATEGORY_ID, dbo.[MIG_GET_PARENT_CATEGORIES_Fn](CATEGORY_ID) AS PARENT_CATEGORIES 
	FROM CATEGORY_TREE

	--SELECT * FROM @TEMP_CAT_TREE

	INSERT INTO @TEMP_OBJ_CATEG_INST
	SELECT DISTINCT OBCI.ZZ_ID, 
					--OBCI.OBJECT_TYPE,
					--OBCI.OBJECT_ID, 
					OBCI.CATEGORY_ID, 
					OBCI.ATTR_NAME,
					FORMAT(CONVERT(DATE,ATTR_STRING_VALUE), 'MM-dd-yyyy') AS  ATTR_STRING_VALUE,
					DATEDIFF(day, '0001/01/01', FORMAT(CONVERT(DATE,ATTR_STRING_VALUE), 'yyyy/MM/dd') ) + 1 AS ATTR_NUMERIC_VALUE  --> Year/Month/Date
	FROM  @TEMP_CAT_TREE PRCT 
	JOIN CATEGORY_ATTRS ATTR
		-- Consider all the parent Categories of selected Category. bcz child Category attr can be derived from parent category attributes
		ON ATTR.CATEGORY_ID IN ( SELECT [value] FROM [MIG_PLM_STRING_SPLIT_Fn](PRCT.PARENT_CATEGORIES, ',') ) 
	JOIN OBJ_CATEG_INST OBCI 
		ON OBCI.ATTR_NAME = ATTR.ATTR_NAME AND PRCT.CATEGORY_ID = OBCI.CATEGORY_ID  -- Category must be in Category Tree
	WHERE ATTR.ATTR_TYPE = 7 -- attr type date

	--SELECT * FROM @TEMP_OBJ_CATEG_INST
	DELETE FROM MIG_OBJ_CATEG_INST

	INSERT INTO MIG_OBJ_CATEG_INST
	SELECT OBCI.[ZZ_ID],
		   OBCI.[OBJECT_TYPE],
		   OBCI.[OBJECT_ID],
		   OBCI.[OBJECT_REV],
		   OBCI.[CATEGORY_ID],
		   OBCI.[ATTR_NAME],
		   ISNULL(TOBC.[ATTR_STRING_VALUE] ,OBCI.[ATTR_STRING_VALUE]) AS [ATTR_STRING_VALUE],
		   ISNULL(TOBC.[ATTR_NUMERIC_VALUE],OBCI.[ATTR_NUMERIC_VALUE]) AS [ATTR_NUMERIC_VALUE],
		   OBCI.[UPDATED_ON]
	FROM OBJ_CATEG_INST OBCI 
	LEFT JOIN @TEMP_OBJ_CATEG_INST TOBC
		ON	OBCI.ZZ_ID = TOBC.ZZ_ID AND 
			OBCI.CATEGORY_ID = TOBC.CATEGORY_ID AND 
			OBCI.ATTR_NAME = TOBC.ATTR_NAME  

END	
