CREATE OR ALTER PROCEDURE MIG_MAPPING_TEMPLATES_Sp 
AS 
BEGIN
	TRUNCATE TABLE MIG_MAPPING_TEMPLATES
	INSERT INTO MIG_MAPPING_TEMPLATES (SRC_TEMPLATE, TRG_TEMPLATE, TRG_APPLICATION)
	SELECT DISTINCT TEMPLATE, 
			SUBSTRING(TEMPLATE, CHARINDEX('_', TEMPLATE)+1, LEN(TEMPLATE)) AS TRG_TEMPLATE,
			-- pdcad.apln refer to this domain in PD package
			CASE 
				 WHEN SUBSTRING(TEMPLATE, 1, LEN('UG_') ) = 'UG_' THEN 5
				 WHEN SUBSTRING(TEMPLATE, 1, LEN('SOLIDWORKS_') ) = 'SOLIDWORKS_' THEN 10
				 WHEN SUBSTRING(TEMPLATE, 1, LEN('PROE_') ) = 'PROE_' THEN 15
				 WHEN SUBSTRING(TEMPLATE, 1, LEN('MS_') ) = 'MS_' THEN 20
				 WHEN SUBSTRING(TEMPLATE, 1, LEN('SOLIDEDGE_') ) = 'SOLIDEDGE_' THEN 25
				 WHEN SUBSTRING(TEMPLATE, 1, LEN('INVENTOR_') ) = 'INVENTOR_' THEN 30
			END AS [TRG_APPLICATION]
	FROM MIG_MAPPING_RULES

END
