
 CREATE  VIEW [dbo].[MIG_EO_ROUTE_CHART_VIEW]  
AS  
SELECT DISTINCT X.EO_ID, X.ROUTE_ID,X.INSTR_ID, X.PREV_INSTR_ID, X.NEXT_INSTR_ID, X.STEP_ID,X.LINE_TYPE,
		 CASE WHEN  ERS.STEP_TYPE = 4 THEN 'Begin' 
               WHEN ERS.STEP_TYPE = 6 THEN 'End' 
                WHEN ERS.STEP_TYPE = 5 THEN
                CASE 
                         WHEN ERTS.TOOL_KEY = 'WkFl_Fork' THEN 'Fork'
                         WHEN ERTS.TOOL_KEY = 'WkFl_WaitUntil' THEN 'Join' 
                         ELSE ERTS.TOOL_KEY
               END
               WHEN ERS.STEP_TYPE = 2 THEN 'System' 
               WHEN ERS.STEP_TYPE = 1 THEN
               CASE
                        WHEN ERUS.GRP_ACTIVITY_FLAG = 1 THEN 'Group'
                        ELSE 'User' 
                        END
                        ELSE NULL
                  END STEP_TYPE,
                  ERS.LAYOUT_X ,
                  ERS.LAYOUT_Y ,
                  ERUS.USER_NAME, ERUS.GROUP_NAME,
				CASE WHEN ERCS.ZZ_ID IS NULL THEN 0 ELSE 1 END CurrentStep,  
                  COALESCE(ERTS.SKIP_STEP , ERUS.SKIP_STEP , ERSS.SKIP_STEP , ETRN.SKIP_STEP , 0) SKIP_STEP

 FROM  EO_ROUTE_LINE X WITH(NOLOCK)  
 LEFT JOIN EO_ROUTE_STEP ERS WITH(NOLOCK)  
 ON ERS.EO_ID = X.EO_ID AND ERS.STEP_ID = X.STEP_ID   
 LEFT JOIN  EO_RT_TOOL_STP ERTS WITH(NOLOCK)   
 ON ERTS.EO_ID = X.EO_ID AND ERTS.STEP_ID = X.STEP_ID   
 LEFT JOIN EO_RT_USR_STP ERUS  WITH(NOLOCK)  
 ON  ERUS.EO_ID = X.EO_ID  AND ERUS.STEP_ID = X.STEP_ID   
 LEFT JOIN  EO_RT_SYST_STP ERSS WITH(NOLOCK) 
  ON ERSS.EO_ID = X.EO_ID AND ERSS.STEP_ID = X.STEP_ID   
 LEFT JOIN  EO_RT_TRNS_STP ETRN WITH(NOLOCK)   
 ON  ETRN.EO_ID = X.EO_ID  AND ETRN.STEP_ID = X.STEP_ID   
 LEFT JOIN EO_RT_CUR_STP ERCS  WITH(NOLOCK)
 ON ERCS.EO_ID = X.EO_ID  AND ERCS.STEP_ID = X.STEP_ID   
 WHERE    
 --X.EOID = 'BP-P1-0647'  AND   
 X.INSTR_ID <> X.NEXT_INSTR_ID;  
