--DROP TABLE MIG_ROUTE_DIST_STP_PARAMS
IF OBJECT_ID(N'dbo.MIG_ROUTE_DIST_STP_PARAMS', N'U') IS NULL
	BEGIN	
		CREATE TABLE [dbo].[MIG_ROUTE_DIST_STP_PARAMS](
			[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_ROUTE_DIST_STP_PARAMS PRIMARY KEY (ROUTE_ID, STEP_ID)
		) ON [PRIMARY]
	END
GO

-- insert data to table


--SELECT * FROM (
;With CTE_CONCAT_MESSAGES AS (
	SELECT	ROUTE_ID, 
			TOOL_KEY, 
			STEP_ID, 
			'5' as PRM_IDX,
			REPLACE(CONCAT_PRM_VALUE, '|', '') as CONCAT_PRM_VALUE
	FROM ( 
		   SELECT	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 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 RT_TOOL_PRM OTBL
			WHERE OTBL.TOOL_KEY = 'WkFl_Distribute'
		) AS TBL
)

--select * from CTE_CONCAT_MESSAGES
INSERT into [MIG_ROUTE_DIST_STP_PARAMS]

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

GO
