--DROP TABLE MIG_EO_SEND_TO_PROD_STP_PARAMS

IF OBJECT_ID(N'dbo.MIG_EO_SEND_TO_PROD_STP_PARAMS', N'U') IS NULL
	BEGIN	
		CREATE TABLE [dbo].[MIG_EO_SEND_TO_PROD_STP_PARAMS](
			[EO_ID] [varchar](30) NOT NULL,
			[ROUTE_ID] [varchar](30) NOT NULL,
			[STEP_ID] [varchar](15) NOT NULL,
			[BAAN_COMP_PROJ] [varchar](255) NOT NULL,
			[NUM_LEVELS] [varchar](255) NOT NULL,
			[EFFE_DATE] [varchar](255) NOT NULL,
			[TRANS_ALTER] [varchar](255) NOT NULL,
			[EXTRACT_PURCHASE_BOM] [varchar](255) NOT NULL,
			[CLUST_WAREHOUSE] [varchar](255) NOT NULL,
			[GUID] [varchar](36) NOT NULL,
		 CONSTRAINT PK_MIG_EO_SEND_TO_PROD_STP_PARAMS PRIMARY KEY (EO_ID, ROUTE_ID, STEP_ID)
		) ON [PRIMARY]
	END
GO

INSERT INTO MIG_EO_SEND_TO_PROD_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 [BAAN_COMP_PROJ],
		MAX(CASE WHEN TP.PRM_IDX = 2 THEN TP.PRM_VALUE ELSE '' END) as [NUM_LEVELS],
		MAX(CASE WHEN TP.PRM_IDX = 3 THEN TP.PRM_VALUE ELSE '' END) as [EFFE_DATE],
		MAX(CASE WHEN TP.PRM_IDX = 4 THEN TP.PRM_VALUE ELSE '' END) as [TRANS_ALTER],
		MAX(CASE WHEN TP.PRM_IDX = 5 THEN TP.PRM_VALUE ELSE '' END) as [EXTRACT_PURCHASE_BOM],
		MAX(CASE WHEN TP.PRM_IDX = 6 THEN TP.PRM_VALUE ELSE '' END) as [CLUST_WAREHOUSE],
		NEWID() AS [GUID]
from EO_RT_TOOL_PRM TP -- WHERE  TP.TOOL_KEY= 'WkFl_ExtrToBAAN'
LEFT JOIN NINTRA_TOOL NT 
ON TP.TOOL_KEY = NT.TOOL_KEY 
	AND TP.PRM_IDX =  NT.PRM_IDX
WHERE  TP.TOOL_KEY= 'WkFl_ExtrToBAAN' 
GROUP BY TP.EO_ID,
		 TP.ROUTE_ID,
		 TP.STEP_ID



UNION

SELECT  TP.EO_ID,
		TP.ROUTE_ID,
		TP.STEP_ID,
		MAX(CASE WHEN TP.PRM_IDX = 1 THEN TP.PRM_VALUE ELSE '' END) as [BAAN_COMP_PROJ],
		MAX(CASE WHEN TP.PRM_IDX = 2 THEN TP.PRM_VALUE ELSE '' END) as [NUM_LEVELS],
		MAX(CASE WHEN TP.PRM_IDX = 3 THEN TP.PRM_VALUE ELSE '' END) as [EFFE_DATE],
		MAX(CASE WHEN TP.PRM_IDX = 4 THEN TP.PRM_VALUE ELSE '' END) as [TRANS_ALTER],
		MAX(CASE WHEN TP.PRM_IDX = 5 THEN TP.PRM_VALUE ELSE '' END) as [EXTRACT_PURCHASE_BOM],
		MAX(CASE WHEN TP.PRM_IDX = 8 THEN TP.PRM_VALUE ELSE '' END) as [CLUST_WAREHOUSE],
		NEWID() AS [GUID]	
FROM EO_RT_TOOL_PRM TP 
LEFT JOIN NINTRA_TOOL NT 
ON TP.TOOL_KEY = NT.TOOL_KEY AND
	TP.PRM_IDX = NT.PRM_IDX 
WHERE  TP.TOOL_KEY= 'WkFl_TOP_ToBAAN' 
		AND TP.PRM_IDX IN (1,2,3,4,5,8 ) -- Except MBC_ID , LOG_ID
GROUP BY TP.EO_ID,
		 TP.ROUTE_ID,
		 TP.STEP_ID
ORDER BY TP.ROUTE_ID
