
Create View [dbo].[MIG_ROUTE_CHART_VIEW] As
SELECT DISTINCT X.ROUTE_ID,X.INSTR_ID, X.PREV_INSTR_ID, X.NEXT_INSTR_ID, X.STEP_ID,X.LINE_TYPE, CASE WHEN  RS.STEP_TYPE = 4 THEN 'Begin' 
               WHEN RS.STEP_TYPE = 6 THEN 'End' 
                WHEN RS.STEP_TYPE = 5 THEN
                CASE 
                         WHEN RTS.TOOL_KEY = 'WkFl_Fork' THEN 'Fork'
                         WHEN RTS.TOOL_KEY = 'WkFl_WaitUntil' THEN 'Join' 
                         ELSE RTS.TOOL_KEY
               END
               WHEN RS.STEP_TYPE = 2 THEN 'System' 
               WHEN RS.STEP_TYPE = 1 THEN
               CASE
                        WHEN RUS.GRP_ACTIVITY_FLAG = 1 THEN 'Group'
                        ELSE 'User' 
                        END
                        ELSE NULL
                  END STEP_TYPE,
                  RS.LAYOUT_X ,
                  RS.LAYOUT_Y ,
                  RUS.USER_NAME, RUS.GROUP_NAME,
                  0  CurrentStep,
                  ' ' Escalation,
                  COALESCE(RTS.SKIP_STEP , RUS.SKIP_STEP , RSS.SKIP_STEP , TRN.SKIP_STEP , 0) SKIP_STEP
                 FROM  ROUTE_LINE X
                 LEFT JOIN ROUTE_STEP RS ON RS.ROUTE_ID = X.ROUTE_ID AND RS.STEP_ID = X.STEP_ID
                 LEFT JOIN RT_TOOL_STP RTS ON RTS.ROUTE_ID = X.ROUTE_ID AND RTS.STEP_ID = X.STEP_ID
                 LEFT JOIN RT_USR_STP RUS ON RUS.ROUTE_ID = X.ROUTE_ID AND RUS.STEP_ID = X.STEP_ID
                 LEFT JOIN RT_SYS_STP RSS ON RSS.ROUTE_ID = X.ROUTE_ID AND RSS.STEP_ID = X.STEP_ID
                 LEFT JOIN RT_TRNS_STP TRN ON TRN.ROUTE_ID = X.ROUTE_ID AND TRN.STEP_ID = X.STEP_ID
                  WHERE
				   X.INSTR_ID<> X.NEXT_INSTR_ID 
				   



