
CREATE OR ALTER PROCEDURE MIG_CORRECT_EO_ROUTE_STEP_Sp
AS BEGIN 

DELETE FROM MIG_CORRECT_EO_ROUTE_STEP

INSERT INTO MIG_CORRECT_EO_ROUTE_STEP(EO_ID, ROUTE_ID, STEP_TYPE, STEP_ID, NEW_STEP_ID)

SELECT DISTINCT ERS1.EO_ID, ERS1.ROUTE_ID , ERS1.STEP_TYPE, ERS1.STEP_ID , '' NEW_STEP_ID
	--,(CASE WHEN ERTS.TOOL_KEY IS NULL THEN NULL ELSE ERTS.TOOL_KEY END) AS TOOL_KEY
FROM EO_ROUTE_STEP  ERS1 
LEFT JOIN  EO_RT_TOOL_STP ERTS 
ON	ERTS.EO_ID = ERS1.EO_ID AND 
	ERTS.ROUTE_ID = ERS1.ROUTE_ID AND 
	ERTS.STEP_ID = ERS1.STEP_ID 
LEFT JOIN  EO_RT_USR_STP ERUS
ON	ERUS.EO_ID = ERS1.EO_ID AND 
	ERUS.ROUTE_ID = ERS1.ROUTE_ID AND 
	ERUS.STEP_ID = ERS1.STEP_ID
LEFT JOIN  EO_RT_TRNS_STP ERTRS
ON	ERTRS.EO_ID = ERS1.EO_ID AND 
	ERTRS.ROUTE_ID = ERS1.ROUTE_ID AND 
	ERTRS.STEP_ID = ERS1.STEP_ID
LEFT JOIN  EO_RT_SYST_STP ERSS
ON	ERSS.EO_ID = ERS1.EO_ID AND 
	ERSS.ROUTE_ID = ERS1.ROUTE_ID AND 
	ERSS.STEP_ID = ERS1.STEP_ID
JOIN ( SELECT EO_ID, ROUTE_ID, STEP_ID ,ROW_NUMBER() OVER(PARTITION BY EO_ID,ROUTE_ID,UPPER(STEP_ID) 
		ORDER BY EO_ID, ROUTE_ID) AS EO_Duplicate 
		FROM EO_ROUTE_STEP ) CASE_SENSETIVE_IDS
ON	ERS1.EO_ID = CASE_SENSETIVE_IDS.EO_ID AND 
	ERS1.ROUTE_ID = CASE_SENSETIVE_IDS.ROUTE_ID AND 
	UPPER(ERS1.STEP_ID) = UPPER(CASE_SENSETIVE_IDS.STEP_ID)
WHERE	EO_Duplicate > 1 AND 
		(TOOL_KEY NOT IN ('WkFl_Fork', 'WkFl_WaitUntil', 'WkFl_InitEvent') OR 
		TOOL_KEY IS NULL)

END 