-- EXEC [MIG_CREATE_ENTRIES_IN_TEXT_FIELDS_QUERIES_Sp]
CREATE OR ALTER PROCEDURE [MIG_CREATE_ENTRIES_IN_TEXT_FIELDS_QUERIES_Sp]
AS BEGIN
	-- The purpose of this stored procedure is to create the entries for table [MIG_TEXT_FIELDS_QUERIES] table.

	--CREATE TABLE [dbo].[MIG_TEXT_FIELDS_QUERIES](
	--	[ID] [int] IDENTITY(0,1) NOT NULL,
	--	[TARGET_TABLE] [varchar](255) NOT NULL,
	--	[TARGET_FIELD] [varchar](255) NOT NULL,
	--	[SQL_QUERY] [nvarchar](max) NOT NULL
	--) ON [PRIMARY]
	--GO

	DELETE FROM MIG_TEXT_FIELDS_QUERIES

	--EO.IMPACT_ANALYSIS ==> pdwfl100.impa
	INSERT INTO MIG_TEXT_FIELDS_QUERIES(TARGET_TABLE, TARGET_FIELD, SQL_QUERY)
	VALUES
	(
		'pdcad100',
		'extd',
		'SELECT ''pdcad100.extd-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", EXTRA_DATA as "FILE_CONTENT", TEMPLATE AS "eoid" ' +
		'FROM MAPPING_RULES ' +
		'WHERE LEN(MAPPING_RULES.EXTRA_DATA) > 0 ORDER BY MAPPING_RULES.TEMPLATE'
	),
	(
		'pdpdm111',
		'text',
		'SELECT ''pdpdm111.text-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", TEXT as "FILE_CONTENT", ITEM_KEY AS "ikey" , ROUTE_ID AS "rout" ' +
		'FROM ITEM_ROUTING ' +
		'WHERE LEN(ITEM_ROUTING.TEXT) > 0 ORDER BY ITEM_ROUTING.ITEM_KEY, ITEM_ROUTING.ROUTE_ID'
	) ,
	(
		'pdwfl114',
		'cmnt',
		'SELECT ''pdwfl114.cmnt-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", TEXT_COMMENT as "FILE_CONTENT", COMMENT_ID AS "cmid" ' +
		'FROM EO_EXT_CMNT ' +
		'WHERE LEN(EO_EXT_CMNT.TEXT_COMMENT) > 0 ORDER BY EO_EXT_CMNT.COMMENT_ID'
	),
	(
		'pdwfl100',
		'impa',
		'SELECT ''pdwfl100.impa-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", IMPACT_ANALYSIS as "FILE_CONTENT", EO_ID AS "eoid" ' +
		'FROM EO ' +
		'WHERE LEN(EO.IMPACT_ANALYSIS) > 0 ORDER BY EO.EO_ID'
	),
	(
		'pdwfl100',
		'rsch',
		'SELECT ''pdwfl100.rsch-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", REASON_FOR_CHANGE as "FILE_CONTENT", EO_ID AS "eoid" ' +
		'FROM EO ' +
		'WHERE LEN(EO.REASON_FOR_CHANGE) > 0 ORDER BY EO.EO_ID'
	),
	(
		'pdwfl100',
		'rmrk',
		'SELECT ''pdwfl100.rmrk-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME",  REMARKS as "FILE_CONTENT", EO_ID AS "eoid" ' +
		'FROM EO ' +
		'WHERE LEN(EO.REMARKS) > 0 ORDER BY EO.EO_ID'
	),
	(
		'pdwfl126',
		'msgt',
		'SELECT ''pdwfl126.msgt-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", MESSAGE as "FILE_CONTENT", EO_ID AS "eoid", ROUTE_ID AS "rout" , STEP_ID AS "step" , EO_USER_ROLE AS "urol" , USER_NAME AS "user" ' +
		'FROM EO_DUE_EVENTS ' +
		'WHERE LEN(EO_DUE_EVENTS.MESSAGE) > 0 ORDER BY EO_DUE_EVENTS.EO_ID, EO_DUE_EVENTS.ROUTE_ID, EO_DUE_EVENTS.STEP_ID, EO_DUE_EVENTS.EO_USER_ROLE, EO_DUE_EVENTS.USER_NAME'
	),
	(
		'pdwfl133',
		'mess',
		'SELECT ''pdwfl133.mess-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", MESS as "FILE_CONTENT", EO_ID AS "eoid", ROUTE_ID AS "rout" , STEP_ID AS "step" ' +
		'FROM MIG_EO_DISTRIBUTION_PRM ' +
		'WHERE LEN(MIG_EO_DISTRIBUTION_PRM.MESS) > 0 ORDER BY MIG_EO_DISTRIBUTION_PRM.EO_ID, MIG_EO_DISTRIBUTION_PRM.ROUTE_ID, MIG_EO_DISTRIBUTION_PRM.STEP_ID'
	),
	(
		'pdwfl207',
		'msgt',
		'SELECT ''pdwfl207.msgt-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", MESSAGE as "FILE_CONTENT", ROUTE_ID AS "rout" , STEP_ID AS "step" , EO_USER_ROLE AS "urol" , USER_NAME AS "user" ' +
		'FROM RT_DUE_EVENTS ' +
		'WHERE LEN(RT_DUE_EVENTS.MESSAGE) > 0 ORDER BY RT_DUE_EVENTS.ROUTE_ID, RT_DUE_EVENTS.STEP_ID, RT_DUE_EVENTS.EO_USER_ROLE, RT_DUE_EVENTS.USER_NAME'
	),
	(
		'pdwfl233',
		'mess',
		'SELECT ''pdwfl233.mess-'' + CAST(ZZ_ID AS NVARCHAR(MAX)) AS "FILE_NAME", MESS as "FILE_CONTENT", ROUTE_ID AS "rout" , STEP_ID AS "step" ' +
		'FROM MIG_ROUTE_DISTRIBUTION_PRM ' +
		'WHERE LEN(MIG_ROUTE_DISTRIBUTION_PRM.MESS) > 0 ORDER BY MIG_ROUTE_DISTRIBUTION_PRM.ROUTE_ID, MIG_ROUTE_DISTRIBUTION_PRM.STEP_ID'
	)
	
END

-- exec MIG_CREATE_ENTRIES_IN_TEXT_FIELDS_QUERIES_Sp
-- SELECT * FROM MIG_TEXT_FIELDS_QUERIES

--Domain			Domain Description	Package		Module	Table	Field	Table Description								Field Description		Data Migration Required
--pdcad.tpda		TemplateData		pd			cad		101		tpda	Mapping Templates								TemplateData			??
--pdtext			Text				pd			adm		502		rxml	Category History								Request XML				No
--pdtext			Text				pd			adm		558		impa	Business Process Defaults						Impact Analysis			No
--pdtext			Text				pd			adm		558		rmrk	Business Process Defaults						Remarks					No
--pdtext			Text				pd			adm		558		rsch	Business Process Defaults						Reason					No
--pdtext			Text				pd			adm		558		text	Business Process Defaults						Text/Long Description	No
--pdtext			Text				pd			adm		858		exdt	DCA - Mapping Rules								Extra Data				No
--pdtext			Text				pd			cad		100		extd	Mapping Rules									Mapping Extra Data		Yes
--pdtext			Text				pd			int		001		rmks	PLM Data Migration Field Mapping				Remarks					No
--pdtext			Text				pd			pdm		111		text	Item Routings									Text					Yes
--pdtext			Text				pd			wfl		010		txta	Tasks											Text					No
--pdtext			Text				pd			wfl		013		txta	Resource Requirements by Activity				Text					No
--pdtext			Text				pd			wfl		100		impa	Business Process								Impact Analysis			Yes
--pdtext			Text				pd			wfl		100		rmrk	Business Process								Remarks					Yes
--pdtext			Text				pd			wfl		100		rsch	Business Process								Reason					Yes
--pdtext			Text				pd			wfl		100		text	Business Process								Text/Long Description	No
--pdtext			Text				pd			wfl		114		cmnt	EO Comment										Text Comment			Yes
--pdtext			Text				pd			wfl		126		msgt	EO Route Due Events								Message					Yes
--pdtext			Text				pd			wfl		133		mess	EO Distribution Step Parameters					Message					Yes
--pdtext			Text				pd			wfl		143		txta	Resource Requirements by Business Process Step	Text					No
--pdtext			Text				pd			wfl		200		text	Route											Text					No
--pdtext			Text				pd			wfl		207		msgt	Route Due Events								Message					Yes
--pdtext			Text				pd			wfl		233		mess	Distribution Step Parameters					Message					Yes
--pdtext			Text				pd			wfl		243		txta	Resource Requirements by Workflow Step			Text					No

