--DROP TABLE MIG_EO_DIST_STP_PARAMS
IF OBJECT_ID(N'dbo.MIG_EO_DIST_STP_PARAMS', N'U') IS NULL
	BEGIN	
		CREATE TABLE [dbo].[MIG_EO_DIST_STP_PARAMS](
			[EO_ID] [varchar](30) NOT NULL,
			[ROUTE_ID] [varchar](30) NOT NULL,
			[STEP_ID] [varchar](15) NOT NULL,
			[TO_USER] [varchar](255) NOT NULL,
			[TO_GROUP] [varchar](255) NOT NULL,
			[MAIL_TO] [varchar](255) NOT NULL,
			[SUBJECT] [varchar](255) NOT NULL,
			[MESSAGE] [varchar](max) NOT NULL,
		 CONSTRAINT PK_MIG_EO_DIST_STP_PARAMS PRIMARY KEY (EO_ID, ROUTE_ID, STEP_ID)
		) ON [PRIMARY]
	END
GO

-- insert data to table


;With CTE_CONCAT_MESSAGES AS (
	SELECT	EO_ID,
			ROUTE_ID, 
			TOOL_KEY, 
			STEP_ID, 
			'5' as PRM_IDX,
			REPLACE(CONCAT_PRM_VALUE, '|', '') as CONCAT_PRM_VALUE
	FROM ( 
		   SELECT	OTBL.EO_ID, 
					OTBL.ROUTE_ID, 
					OTBL.TOOL_KEY, 
					OTBL.STEP_ID, 
					CONCAT_PRM_VALUE = STUFF(
						(SELECT  (CASE WHEN ISNULL(ITBL.PRM_VALUE,'') <> '' THEN ';' ELSE '|' END) + ITBL.PRM_VALUE
						 FROM EO_RT_TOOL_PRM ITBL
						 WHERE	ITBL.ROUTE_ID = OTBL.ROUTE_ID    AND
								ITBL.TOOL_KEY = OTBL.TOOL_KEY    AND
								TOOL_KEY = 'WkFl_Distribute'	 AND PRM_IDX IN (5,6,7,8,9)
						 FOR XML PATH ('')
						), 1, 1, '')
			FROM EO_RT_TOOL_PRM OTBL
			WHERE OTBL.TOOL_KEY = 'WkFl_Distribute'
		) AS TBL
)

--select * from CTE_CONCAT_MESSAGES
INSERT into [MIG_EO_DIST_STP_PARAMS]

SELECT	TP.EO_ID,
		TP.ROUTE_ID,
		TP.STEP_ID,
		MAX(case when TP.PRM_IDX = 1 then TP.PRM_VALUE ELSE '' END)  as TO_USER,
		MAX(case when TP.PRM_IDX = 2 then TP.PRM_VALUE ELSE '' END)  as TO_GROUP,
		MAX(case when TP.PRM_IDX = 3 then TP.PRM_VALUE ELSE '' END)  as [MAIL_TO],
		MAX(case when TP.PRM_IDX = 4 then TP.PRM_VALUE ELSE '' END)  as [SUBJECT],
		MAX(case when TP_MSG.PRM_IDX = 5 then TP_MSG.CONCAT_PRM_VALUE ELSE '' END)  as [MESSAGE]
FROM EO_RT_TOOL_PRM TP
INNER JOIN CTE_CONCAT_MESSAGES TP_MSG
	ON	TP.EO_ID = TP_MSG.EO_ID AND
		TP.ROUTE_ID = TP_MSG.ROUTE_ID AND
		TP.STEP_ID = TP_MSG.STEP_ID AND
		TP.TOOL_KEY = TP_MSG.TOOL_KEY
LEFT JOIN NINTRA_TOOL NT
		ON	TP.TOOL_KEY = NT.TOOL_KEY  AND
			TP.PRM_IDX = NT.PRM_IDX
WHERE TP.TOOL_KEY = 'WkFl_Distribute'
GROUP BY TP.EO_ID, TP.ROUTE_ID, TP.STEP_ID, TP.TOOL_KEY

GO

