ALTER PROCEDURE [dbo].[PRC_ASDK_TEXTO_CORREO] (
	@i_item_id				INT,
	@i_record_type			INT,
	@i_template				TEXT,
	@i_timestamp			DATETIME = NULL,
	@i_requester			INT = 1,
	@o_replaced				TEXT OUTPUT
) AS

BEGIN

	SET NOCOUNT ON;

	DECLARE 
		@v_replace			NVARCHAR(MAX),
		@v_parameters		NVARCHAR(512),
		@v_table				SYSNAME,
		@v_table_id			INT,
		@v_field				SYSNAME,
		@v_key				SYSNAME,
		@v_token				SYSNAME,
		@v_date_start		DATETIME,
		@v_date_finish		DATETIME,
		@v_log_time			CHAR(1);
	
	SET @v_date_start = GETDATE(); 
	SET @v_parameters = '@i_item_id INT, @i_timestamp DATETIME, @v_replace TEXT OUTPUT';
	SET @v_log_time = 'N';
			
	BEGIN TRY
		
		SET @v_table_id =
			CASE
				WHEN @i_record_type IN(1, 8) THEN 1
				WHEN @i_record_type IN(2, 9) THEN 2
				WHEN @i_record_type IN(3, 10) THEN 3
				WHEN @i_record_type IN(15, 17) THEN 4
				WHEN @i_record_type IN(13, 14) THEN 6
				WHEN @i_record_type IN(42) THEN 10
			END;

		-- Duplicate quotation marks
		SET @v_replace = REPLACE(CONVERT(NVARCHAR(MAX), @i_template), N'''', N'''''');
		
		-- Tags replacement for advanced additional fields
		SELECT
			@v_replace = REPLACE (
				CONVERT(NVARCHAR(MAX), @v_replace),
				'/ADV_FIELD_' + CONVERT(VARCHAR(11), fc.fl_int_id_field)  + '/',
				dbo.FUN_AFW_FIELD_VALUE_STRING(fc.fl_int_field_type, fd.fl_str_field_value, fd.fl_date_field_value, fd.fl_int_field_value)
			)
		FROM AFW_ADD_FIELDS_CONFIG fc
		LEFT JOIN AFW_ADD_FIELDS_DATA fd ON fd.fl_int_id_field = fc.fl_int_id_field
			AND fl_int_id_caso = @i_item_id
		WHERE fc.fl_int_registry_type = @v_table_id;

		-- Sentence for tags replacement
		SELECT @v_table = ASDK_RULES_TABLES.source_table,
			@v_key = ASDK_RULES_TABLES.source_key,
			@v_replace =
				REPLACE (
					CONVERT(NVARCHAR(MAX), @v_replace),
					'/' + ASDK_RULES_FIELDS.field_name + '/',
					''') + ISNULL(CONVERT(' +
						CASE WHEN (
							SELECT syscolumns.xtype
							FROM sys.syscolumns
							INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
							WHERE	sysobjects.name = ASDK_RULES_TABLES.source_table
							AND syscolumns.name = ASDK_RULES_FIELDS.field_name
						) IN (35, 167) THEN 'VARCHAR(MAX)' ELSE 'VARCHAR(8000)' END
						+ ',' + ASDK_RULES_FIELDS.field_name + '),'''') + CONVERT(VARCHAR(MAX), '''
				)
		FROM ASDK_RULES_FIELDS
		JOIN ASDK_RULES_TABLES ON ASDK_RULES_FIELDS.table_id = ASDK_RULES_TABLES.table_id
		WHERE	ASDK_RULES_FIELDS.table_id = @i_record_type;

		SET @v_replace	=
			'SELECT @v_replace = CONVERT(VARCHAR(MAX), '''
			+ @v_replace + ''') FROM '
			+ CASE WHEN @i_requester <> 1 AND @i_record_type IN (8, 9, 10, 17) THEN @v_table + '_TIME ' ELSE @v_table END
			+ ' WITH (NOLOCK) '
			+ ' WHERE ' + @v_key + ' = @i_item_id' +
			CASE WHEN @i_requester = 1 AND (@i_record_type IN (1, 2, 3, 14, 15) OR ISNULL(@i_timestamp, 0) <> 0) THEN ' AND time_stamp = @i_timestamp' ELSE '' END;

		EXECUTE sp_executesql @v_replace, @v_parameters, @i_item_id, @i_timestamp, @v_replace = @o_replaced OUTPUT;

		-- Gets execution time
		SET @v_date_finish = GETDATE();

		IF @v_log_time = 'Y'
		BEGIN

			INSERT INTO AFW_SQL_TIMES (
				sqlt_database_object,
				sqlt_operation,
				sqlt_option,
				sqlt_started,
				sqlt_finished,
				sqlt_elapsed_cs,
				sqlt_sentence
			) VALUES (
				'PRC_ASDK_TEXTO_CORREO',
				'NONE',
				'NONE',
				@v_date_start,
				@v_date_finish,
				DATEDIFF(MS, @v_date_start, @v_date_finish),
				'TEXTO_CORREO'
			);

		END;
		
	END TRY
	BEGIN CATCH
		
		EXECUTE EXCEPTION_HANDLER
			@i_operation = 'PRC_ASDK_TEXTO_CORREO';

	END CATCH;

END;