
CREATE OR ALTER PROCEDURE MIG_CORRECT_ROUTE_STEP_Sp
AS BEGIN 

DELETE FROM MIG_CORRECT_ROUTE_STEP

INSERT INTO MIG_CORRECT_ROUTE_STEP(ROUTE_ID, STEP_TYPE, STEP_ID, NEW_STEP_ID)

SELECT DISTINCT RS1.ROUTE_ID , RS1.STEP_TYPE, RS1.STEP_ID , '' NEW_STEP_ID
	--,(CASE WHEN RTS.TOOL_KEY IS NULL THEN NULL ELSE RTS.TOOL_KEY END) AS TOOL_KEY
FROM ROUTE_STEP  RS1 
LEFT JOIN  RT_TOOL_STP RTS 
ON	RTS.ROUTE_ID = RS1.ROUTE_ID AND 
	RTS.STEP_ID = RS1.STEP_ID 
LEFT JOIN  RT_USR_STP RUS
ON	RUS.ROUTE_ID = RS1.ROUTE_ID AND 
	RUS.STEP_ID = RS1.STEP_ID
LEFT JOIN  RT_TRNS_STP RTRS
ON	RTRS.ROUTE_ID = RS1.ROUTE_ID AND 
	RTRS.STEP_ID = RS1.STEP_ID
LEFT JOIN  RT_SYS_STP RSS
ON	RSS.ROUTE_ID = RS1.ROUTE_ID AND 
	RSS.STEP_ID = RS1.STEP_ID
JOIN ( SELECT ROUTE_ID, STEP_ID ,ROW_NUMBER() OVER(PARTITION BY ROUTE_ID,UPPER(STEP_ID) 
		ORDER BY ROUTE_ID) AS RT_DUPLICATE FROM ROUTE_STEP ) CASE_SENSETIVE_IDS
ON	RS1.ROUTE_ID = CASE_SENSETIVE_IDS.ROUTE_ID AND 
	UPPER(RS1.STEP_ID) = UPPER(CASE_SENSETIVE_IDS.STEP_ID)
WHERE	RT_DUPLICATE > 1 AND 
		(TOOL_KEY NOT IN ('WKFL_FORK', 'WKFL_WAITUNTIL', 'WKFL_INITEVENT') OR 
		TOOL_KEY IS NULL)
END