USE [Aranda]
GO
/****** Object:  StoredProcedure [dbo].[PRC_ASDK_SERVICE_CALL]    Script Date: 22/07/2020 12:22:42 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PRC_ASDK_SERVICE_CALL] (
	@i_operation							CHAR(1),
	@i_option								CHAR(2)			= NULL,
	@io_serv_id								INT				OUTPUT,
	@i_serv_id_by_project				INT				= NULL,
	@i_serv_category_id					INT				= NULL,
	@i_serv_codreceptor					INT				= NULL,
	@i_serv_customer_id					INT				= NULL,
	@i_serv_priority_id					INT				= NULL,
	@i_serv_status_id						INT				= NULL,
	@i_serv_responsible_id				INT				= NULL,
	@i_serv_registry_type_id			INT				= NULL,
	@i_serv_author							INT				= NULL,
	@i_serv_user_id_modifier			INT				= NULL,
	@i_serv_responsible_group_id		INT				= NULL,
	@i_serv_fl_int_project_id			INT				= NULL,
	@i_serv_vendor_id						INT				= NULL,
	@i_serv_associated_ci				INT				= NULL,
	@i_serv_impact_id						INT				= NULL,
	@i_serv_urgency_id					INT				= NULL,
	@i_serv_reason_id						INT				= NULL,
	@i_serv_service_id					INT				= NULL,
	@i_serv_service_sla_id				INT				= NULL,
	@i_serv_provider_uc_id				INT				= NULL,
	@i_LowerBound							INT				= 1,
	@i_HigerBound							INT				= 20,
	@i_serv_routing_type					INT				= 0,-- Tipo de Enrutamiento: 0=Enrutamiento,1=Reasignacion (Manual o programada)
	@i_solution_id							INT				= NULL,
	@i_is_description_html				INT				= 1,
	@i_serv_estimated_cost				NUMERIC(17,2)	= NULL,
	@i_serv_real_cost						NUMERIC(17,2)	= NULL,
	@i_date_ini								DATETIME			= NULL,
	@i_interface_id						VARCHAR(100)	= NULL,
	@i_serv_subject						VARCHAR(255)	= NULL,
	@fl_str_query							VARCHAR(4000)	= NULL,
	@i_where_clause						VARCHAR(MAX)	= NULL,
	@i_order_clause						VARCHAR(MAX)	= NULL,
	@i_serv_commentary					TEXT				= NULL,
	@i_serv_description_nohtml			TEXT				= NULL,
	@i_serv_commentary_nohtml			TEXT				= NULL,
	@i_serv_description					TEXT				= NULL,
	@i_aux_cte								VARCHAR(MAX)	= NULL,
	@i_aux_join								VARCHAR(MAX)	= NULL,
	@i_transaction_id						VARCHAR(36)		= NULL,
	@o_page_size							INT				OUTPUT,
	@o_err_cod								INT				OUTPUT,
	@o_err_msg								VARCHAR(255)	OUTPUT
) AS

DECLARE
	@v_serv_id								INT,
	@v_serv_involved_users				INT,
	@v_serv_status_id						INT,
	@v_serv_statustmp_id					INT,
	@v_serv_involved_companys			INT,
	@v_serv_involved_cis					INT,
	@v_current_ci_id						INT,
	@v_serv_attention_time				INT,
	@v_serv_solution_time				INT,
	@v_calendar_id							INT,
	@v_calendarbefore_id					INT,
	@v_serv_responsible_group_id		INT,
	@v_serv_service_sla_id				INT,
	@v_serv_service_sla_id_current	INT,
	@v_serv_responsible_id				INT,
	@v_serv_customer_id					INT,
	@v_serv_vendor_id						INT,
	@v_serv_impact_id						INT,
	@v_serv_category_id					INT,
	@v_serv_service_id					INT,
	@v_serv_current_time					INT,
	@v_serv_attention_real_time		INT,
	@v_serv_solution_real_time			INT,
	@v_serv_urgency_id					INT,
	@v_serv_priority_id					INT,
	@v_serv_registry_type_id			INT,
	@v_serv_reason_id						INT,
	@v_serv_associated_ci				INT,
	@v_serv_procedure_id					INT,
	@v_serv_involved_user				INT,
	@v_serv_involved_ci					INT,
	@v_serv_involved_company			INT,
	@v_autor									INT,
	@v_serv_provider_uc_id				INT,
	@v_serv_status_type					INT,
	@v_serv_final_status					INT,
	@v_new_status_type					INT,
	@v_real_status_time					INT,
	@v_serv_fl_int_project_id			INT,	
	@v_serv_attention_esti_date		DATETIME,
	@v_serv_attention_real_date		DATETIME,
	@v_serv_opened_date					DATETIME,
	@v_serv_colsed_date					DATETIME,
	@v_serv_expired_date					DATETIME,
	@v_serv_solution_real_date			DATETIME,
	@v_serv_sdb_last_shot				DATETIME,
	@v_serv_estimated_cost				NUMERIC(17,2),
	@v_serv_real_cost						NUMERIC(17,2),
	@v_select								NVARCHAR(MAX),
	@v_base_sentence						NVARCHAR(MAX),
	@v_sentence								NVARCHAR(4000),
	@v_serv_description					VARCHAR(MAX),
	@v_serv_commentary					VARCHAR(MAX),
	@v_serv_description_nohtml			VARCHAR(MAX),
	@v_serv_commentary_nohtml			VARCHAR(MAX),
	@v_from_clause							VARCHAR(MAX),
	@v_where_clause						VARCHAR(MAX),
	@v_actual_time							INT,
	@v_status_change						DATETIME,
	@v_value									INT,
	@v_serv_confidential					INT,

	-- Manejo de errores
	@v_exists								INT,
	@v_err_cod								INT,
	@v_serv_current_progress			INT,
	@v_err_msg								VARCHAR(255)
 
-- Variables usadas para el modelo de paginacion
DECLARE
	@v_rowcount								INT,
	@v_start									INT,
	@v_last									INT,
	@v_pagesize								INT,
	@v_full_query							VARCHAR(MAX),
	@v_parameters							NVARCHAR(500),
	@v_final_sentence						NVARCHAR(MAX)
 
-- Variables usadas para el registro de log de tiempos de ejecucion y errores
DECLARE
	@v_sql_cod								INT,
	@v_log_time								CHAR(1),
	@v_start_time							BIGINT,
	@v_elapsed_time_cs					BIGINT,
	@v_started_ts							DATETIME,
	@v_finished_ts							DATETIME,
	@v_sql_msg								VARCHAR(4000)

BEGIN

	BEGIN TRY;

	SET NOCOUNT OFF;
	SET @v_serv_id = 0;
	SET @v_serv_involved_users = 0;
	SET @v_serv_involved_companys = 0;
	SET @v_serv_involved_cis = 0;
	SET @v_current_ci_id = 0;
	SET @v_rowcount = 20;
	SET @v_start = 0;
	SET @v_last = 0;
	SET @v_started_ts = GETDATE();
	SET @v_start_time = CAST(CAST(GETDATE() AS BINARY(8)) AS BIGINT);
	SET @v_log_time = 'Y';
	SET @v_parameters = N'@v_count INT OUTPUT';

	/*=========================================*/
	/* OPERACION DE CREACION DEL REQUERIMIENTO */
	/*=========================================*/

	IF @i_operation = 'I'
	BEGIN TRY
		SELECT @io_serv_id = serv_id
		FROM ASDK_SERVICE_CALL
		WHERE transaction_id = @i_transaction_id;

		IF ISNULL (@io_serv_id, 0) = 0
		BEGIN
			SET @v_serv_opened_date = GETDATE();

			-- INCIALIZAR PARAMETROS --
			IF @i_serv_category_id IS NULL
			BEGIN

				SELECT @v_serv_category_id = setvalue
				FROM ASDK_PROJECT_SETTINGS
				WHERE setid = 19
				AND fl_int_project_id = @i_serv_fl_int_project_id;

				IF @@ROWCOUNT = 0
					RAISERROR('Category not found', 11, 1);

			END
			ELSE
				SET @v_serv_category_id = @i_serv_category_id;

			IF @i_serv_service_id IS NULL
			BEGIN

				SELECT @v_serv_service_id = setvalue
				FROM	ASDK_PROJECT_SETTINGS
				WHERE	setid = 20
				AND fl_int_project_id = @i_serv_fl_int_project_id;

				IF @@ROWCOUNT = 0
					RAISERROR('Service not found', 11, 1);

			END
			ELSE
				SET @v_serv_service_id = @i_serv_service_id;

			IF @i_serv_responsible_group_id IS NULL
			BEGIN

				SELECT @v_serv_responsible_group_id = grp_id
				FROM ASDK_SERVICE
				WHERE	fl_int_service_id = @v_serv_service_id;

				IF @@ROWCOUNT = 0
					RAISERROR('Default Group not found. Set up a group of specialists to service', 11, 1);

			END
			ELSE
				SET @v_serv_responsible_group_id = @i_serv_responsible_group_id;

			IF @i_serv_service_sla_id IS NULL
			BEGIN

				SELECT @v_serv_service_sla_id = sla_id
				FROM	ASDK_SERVICE
				WHERE	fl_int_service_id = @v_serv_service_id;

			END
			ELSE
				SET @v_serv_service_sla_id = @i_serv_service_sla_id;

			IF @i_serv_impact_id IS NULL
			BEGIN

				SELECT @v_serv_impact_id = fl_int_impact_id
				FROM ASDK_SERVICE
				WHERE fl_int_service_id = @v_serv_service_id;

			END
			ELSE
				SET @v_serv_impact_id = @i_serv_impact_id;

			IF @i_serv_responsible_id IS NULL
			BEGIN

				EXEC [dbo].[PRC_ASDK_GET_RESPONSABLE]
					@i_group_id		   = @v_serv_responsible_group_id,
					@i_project_id	   = @i_serv_fl_int_project_id,
					@i_case_type	   = 4,
					@o_responsible_id = @v_serv_responsible_id OUT;

				IF @v_serv_responsible_id IS NULL
					RAISERROR('Specialist not found', 11, 1);

			END
			ELSE
			BEGIN
				SET @v_serv_responsible_id = @i_serv_responsible_id
			END 
 
			IF @i_serv_provider_uc_id IS NULL
				SET @v_serv_provider_uc_id = NULL;

			ELSE
				SET @v_serv_provider_uc_id = @i_serv_provider_uc_id;

			BEGIN

				SELECT @v_calendar_id = ASDK_SERVICE.fl_int_calendar_id
				FROM ASDK_SLA
				INNER JOIN ASDK_SERVICE ON ASDK_SLA.fl_int_service_id = ASDK_SERVICE.fl_int_service_id
				WHERE	ASDK_SLA.sla_id = @v_serv_service_sla_id;

				IF @@ROWCOUNT = 0
					RAISERROR('Calendar not found', 11, 1);

			END;

			IF @i_serv_vendor_id IS NULL
			BEGIN
				SELECT TOP 1 @v_serv_vendor_id = asdk_user_vendor.vendor_id
				FROM ASDK_USER_VENDOR
				JOIN AFW_PROJECT_VENDOR ON AFW_PROJECT_VENDOR.vendor_id = ASDK_USER_VENDOR.vendor_id
					AND project_id = @i_serv_fl_int_project_id
				WHERE ASDK_USER_VENDOR.user_id = @i_serv_customer_id
				AND AFW_PROJECT_VENDOR.project_visible = 1;

			END
			ELSE
				SET @v_serv_vendor_id = @i_serv_vendor_id;

			-- FIN PARAMETROS --
			-- CALCULO DE FECHAS ESTIMADAS --
			BEGIN TRY

				EXEC PRC_ASDK_SLA_TIMES
					@i_sla_id = @v_serv_service_sla_id,
					@i_user_id = @i_serv_customer_id,
					@i_comp_id = @v_serv_vendor_id,
					@i_stage_id = NULL,
					@i_row_type = 4,
					@i_ci_id = @i_serv_associated_ci,
					@o_attention = @v_serv_attention_time OUTPUT,
					@o_solution = @v_serv_solution_time OUTPUT,
					@o_err_cod = @v_err_cod OUTPUT,
					@o_err_msg = @v_err_msg OUTPUT;

			END TRY
			BEGIN CATCH

				SET @v_err_msg = 'Error executing times: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

			END CATCH

			SET @v_serv_attention_esti_date = dbo.FUN_ASDK_FINAL_DATE(@v_calendar_id, @v_serv_attention_time, @v_serv_opened_date);
			SET @v_serv_expired_date = dbo.FUN_ASDK_FINAL_DATE(@v_calendar_id, @v_serv_solution_time, @v_serv_attention_esti_date);
 
			-- ITEMS ASOCIADOS AL SERVICIO --
			SELECT @v_serv_involved_users = COUNT(1)
			FROM ASDK_SERVICE_USER 
			WHERE seus_service_id = @v_serv_service_id;

			SELECT @v_serv_involved_cis = COUNT(1)
			FROM ASDK_SERVICE_CI
			WHERE fl_int_service_id = @v_serv_service_id;

			SELECT @v_serv_involved_companys = COUNT(1)
			FROM ASDK_SERVICE_COMPANY
			WHERE seco_service_id = @v_serv_service_id

			-- OBTIENE EL ESTADO INICIAL DEL FLUJO DE ESTADOS --
			IF @i_serv_status_id IS NULL
			BEGIN

				SELECT DISTINCT TOP 1 @v_serv_status_id = afw_status.stat_id
				FROM AFW_STATUS_TRANSITION,
					AFW_STATUS,
					ASDK_STATUS_BEHAVIOR
				WHERE AFW_STATUS.stat_id = ASDK_STATUS_BEHAVIOR.stat_id
				AND AFW_STATUS.stat_id = sttr_code_ini 
				AND sttr_category = 4 -- 4: serv
				AND AFW_STATUS.stat_project = @i_serv_fl_int_project_id
				AND sttr_code_ini NOT IN (
					SELECT sttr_code_fin
					FROM AFW_STATUS_TRANSITION
					WHERE sttr_category = 4 -- 3: Chan
				);

				IF @@ROWCOUNT = 0
					RAISERROR('Status not found', 11, 1);

			END
			ELSE
			BEGIN
			SET @v_serv_status_id = @i_serv_status_id
			END

			SET @v_serv_status_type = dbo.FUN_ASDK_STATUS_BEHAVIOR('A', @v_serv_status_id, NULL);
			SET @v_serv_priority_id = @i_serv_priority_id;
			SET @v_serv_urgency_id = @i_serv_urgency_id;

			IF @v_serv_priority_id IS NULL AND (@v_serv_impact_id IS NOT NULL AND @i_serv_urgency_id IS NOT NULL)
			BEGIN

				SELECT @v_serv_priority_id = mapr_prio_id
				FROM ASDK_MATRIX_PRIORITY
				WHERE mapr_impa_id = @v_serv_impact_id
				AND mapr_urge_id = @i_serv_urgency_id;

			END;

			IF @i_serv_urgency_id IS NULL
			BEGIN

				SELECT @v_serv_urgency_id = MIN(urge_id)
				FROM ASDK_URGENCY
				WHERE urge_id > 0;

			END;

			IF @i_serv_priority_id IS NULL
			BEGIN

				SELECT @v_serv_priority_id = MIN(prio_id)
				FROM ASDK_PRIORITY
				WHERE prio_id > 0;

			END;

			IF @v_serv_impact_id IS NULL
			BEGIN

				SELECT @v_serv_impact_id = MIN(impa_id)
				FROM ASDK_IMPACT
				WHERE impa_id > 0;

			END;

			SELECT
				@v_serv_confidential =
					CASE WHEN dbo.FUN_TRYCASTINT (setvalue) = @i_serv_customer_id
						AND ser_is_confidential = 1
					THEN 1 ELSE 0 END
			FROM ASDK_SERVICE
			LEFT JOIN SETINGENERAL ON SetId = 2210
			WHERE fl_int_service_id = @v_serv_service_id;

			SET @v_serv_description_nohtml = ISNULL(@i_serv_description_nohtml, dbo.REMOVE_HTML(ISNULL(@i_serv_description, '')));
			SET @v_serv_commentary_nohtml = ISNULL(@i_serv_commentary_nohtml, dbo.REMOVE_HTML(ISNULL(@i_serv_commentary, '')));

			-- ID POR PROYECTO --
			BEGIN TRY

				EXECUTE SP_GET_NEXTID
					@pi_case_type = 4,
					@pi_project_id = @i_serv_fl_int_project_id,
					@pio_case_id = @io_serv_id OUTPUT,-- VALOR USADO PARA EL CONSECUTIVO UNICO DE LA TABLA
					@pio_case_sec = @i_serv_id_by_project OUTPUT; -- VALOR USADO PARA LA NUMERACION UNICA POR PROYECTO

			END TRY
			BEGIN CATCH

				SET @v_err_msg = 'Error executing procedure - ' + ERROR_MESSAGE();
				RAISERROR(@v_err_msg, 11, 1);

			END CATCH;
	 
 
			-- REGISTRO DEL ITEM --
			BEGIN TRY
				SET @v_serv_final_status =
					CASE WHEN
							dbo.FUN_ASDK_STATUS_BEHAVIOR('I', @v_serv_status_id, NULL) = 1
							AND dbo.FUN_ASDK_STATUS_BEHAVIOR('J', @v_serv_status_id, NULL) = 0
						THEN 1
						ELSE 0
					END;
			
				INSERT INTO ASDK_SERVICE_CALL (
					serv_id_by_project,
					serv_id,serv_category_id,
					serv_codreceptor,
					serv_customer_id,
					serv_priority_id,
					serv_status_id,
					serv_opened_date,
					serv_description,
					serv_responsible_id,
					serv_expired_date,
					serv_registry_type_id,
					serv_commentary,
					serv_responsible_group_id,
					serv_attention_time,
					serv_solution_time,
					serv_attention_esti_date,
					serv_author,
					serv_user_id_modifier,
					serv_fl_int_project_id,
					serv_vendor_id,
					serv_estimated_cost,
					serv_real_cost,
					serv_reason_id,
					serv_impact_id,
					serv_urgency_id,
					serv_service_id,
					serv_service_sla_id,
					serv_involved_user,
					serv_involved_ci,
					serv_involved_company,
					serv_associated_ci,
					serv_final_status,
					serv_interface_id,
					serv_description_nohtml,
					serv_current_time,
					serv_current_progress,
					serv_provider_uc_id,
					serv_status_type,
					serv_commentary_nohtml,
					serv_subject,
					serv_confidential,
					transaction_id
				) VALUES (
					@i_serv_id_by_project,
					@io_serv_id,
					@v_serv_category_id,
					@v_serv_responsible_id,
					@i_serv_customer_id,
					@v_serv_priority_id,
					@v_serv_status_id,
					@v_serv_opened_date,
					@i_serv_description,
					@v_serv_responsible_id,
					@v_serv_expired_date,
					@i_serv_registry_type_id,
					@i_serv_commentary,
					@v_serv_responsible_group_id,
					@v_serv_attention_time,
					@v_serv_solution_time,
					@v_serv_attention_esti_date,
					@i_serv_author,
					@i_serv_author,
					@i_serv_fl_int_project_id,
					@v_serv_vendor_id,
					@i_serv_estimated_cost,
					@i_serv_real_cost,
					@i_serv_reason_id,
					@v_serv_impact_id,
					@v_serv_urgency_id,
					@v_serv_service_id,
					@v_serv_service_sla_id,
					@v_serv_involved_users,
					@v_serv_involved_cis,
					@v_serv_involved_companys,
					@i_serv_associated_ci,
					@v_serv_final_status,
					@i_interface_id,
					@v_serv_description_nohtml,
					0,
					0,
					@v_serv_provider_uc_id,
					@v_serv_status_type,
					@v_serv_commentary_nohtml,
					@i_serv_subject,
					@v_serv_confidential,
					@i_transaction_id
				);

				EXEC PRC_ASDK_RULE_CONTEXT
					4,
					@io_serv_id;

				INSERT INTO ASDK_TIMES_DATA (tida_case_id,
					tida_case_type,
					tida_calendar_id,
					tida_opened_date,
					tida_sdb_last_shot,
					tida_current_time,
					tida_status_type
				)
				VALUES (
					@io_serv_id,
					4,
					@v_calendar_id,
					@v_serv_opened_date,
					@v_serv_sdb_last_shot,
					0,
					@v_serv_status_type
				);

				EXEC [dbo].PRC_ASDK_PROJECT_CONTRACT
					@i_operation = 'U',
					@i_option = 'B',
					@i_type_case = 4,
					@io_id = NULL,
					@i_case_id = @io_serv_id,
					@i_value = 1,
					@o_err_cod = @o_err_cod,
					@o_err_msg = @o_err_msg;

			END TRY
			BEGIN CATCH

				SELECT @o_err_cod = -1,
					@v_err_msg = 'Error executing procedure - ' + ERROR_MESSAGE();

				RAISERROR(@v_err_msg, 11, 1);

			END CATCH;
					
			-- ACTUALIZACION DE DATOS EN TABLA DE DIAS REGLAS
			BEGIN TRY

				EXECUTE [dbo].[PRC_ASDK_MODIFY_DATES_CASE]
					@i_case_id = @io_serv_id,
					@i_case_type = 4,
					@i_type_status_id = @v_serv_final_status,
					@i_project_id = @i_serv_fl_int_project_id,
					@i_date_registry = @v_serv_opened_date,
					@i_calendar_id = @v_calendar_id;
			
			END TRY
			BEGIN CATCH

				SET @v_err_msg = 'Error executing relation procedure: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

			END CATCH;
		 
			IF @i_serv_user_id_modifier IS NULL
				SET @v_autor = @i_serv_author;

			ELSE
				SET @v_autor = @i_serv_user_id_modifier;
		
			BEGIN TRY

				EXECUTE PRC_ASDK_HIST_RECEPTOR
					@i_item_id = @io_serv_id,
					@i_row_type = 4,
					@i_author = @v_autor,
					@i_new_user = @v_serv_responsible_id,
					@i_new_group = @v_serv_responsible_group_id,
					@i_category_id = @i_serv_category_id,
					@i_priority_id = @i_serv_priority_id,
					@i_impact_id = @i_serv_impact_id,
					@i_urgency_id = @i_serv_urgency_id,
					@i_status_id = @i_serv_status_id,
					@i_reason_id = @i_serv_reason_id,
					@i_service_id = @i_serv_service_id,
					@i_sla_id = @v_serv_service_sla_id,
					@i_is_new = 1,
					@i_commentary = NULL,
					@i_responsible_user = @v_serv_responsible_id,
					@o_err_cod = @v_err_cod OUTPUT,
					@o_err_msg = @v_err_msg OUTPUT;

			END TRY
			BEGIN CATCH

				SET @v_err_msg = 'Error executing historic procedure: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

			END CATCH;
 
			IF @i_serv_associated_ci IS NOT NULL AND @i_serv_associated_ci <> 0
			BEGIN

				BEGIN TRY

					EXECUTE dbo.PRC_ASDK_RELATION
					@i_operation = 'I',
					@i_option = 'K',
					@i_serv_id = @io_serv_id,
					@i_ci_id = @i_serv_associated_ci,
					@i_user_id_modifier = @i_serv_author,
					@i_relation_cause_id = 5,-- Associated CI
					@o_err_cod = @o_err_cod OUTPUT,
					@o_err_msg = @o_err_msg OUTPUT;

				END TRY
				BEGIN CATCH

					SET @v_err_msg = 'Error executing relation procedure: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

				END CATCH

			END;

		END;

	END TRY
	BEGIN CATCH

		SET @v_err_msg = ERROR_MESSAGE();
		RAISERROR(@v_err_msg,11 , 2);

	END CATCH;

	IF @i_operation = 'R'
	BEGIN

		UPDATE ASDK_SERVICE_CALL
		SET serv_customer_id = @i_serv_customer_id,
			serv_vendor_id = @i_serv_vendor_id
		WHERE serv_id = @io_serv_id;

	END;

	/*=============================================*/
	/* OPERACION DE MODIFICACION DEL REQUERIMIENTO */
	/*=============================================*/
	IF @i_operation = 'U'
	BEGIN TRY

		IF @i_serv_responsible_group_id IS NULL
		BEGIN

			SELECT @v_serv_responsible_group_id = grp_id
			FROM ASDK_SERVICE
			WHERE fl_int_service_id = @v_serv_service_id;

		END
		ELSE
			SET @v_serv_responsible_group_id = @i_serv_responsible_group_id;

		IF @i_serv_responsible_id IS NULL
			EXEC [dbo].[PRC_ASDK_GET_RESPONSABLE]
				@i_group_id		   = @v_serv_responsible_group_id,
				@i_project_id	   = @i_serv_fl_int_project_id,
				@i_case_type	   = 4,
				@o_responsible_id = @v_serv_responsible_id OUT;

		ELSE
			SET @v_serv_responsible_id = @i_serv_responsible_id;
 
		IF @v_serv_responsible_id IS NULL
			SET @v_serv_responsible_group_id = NULL;
	
		IF @i_serv_provider_uc_id IS NULL
			SET @v_serv_provider_uc_id = NULL;

		ELSE
			SET @v_serv_provider_uc_id = @i_serv_provider_uc_id;

		-- HISTORICO DE LAS ACCIONES --
		IF @i_serv_routing_type = 0 OR @i_serv_routing_type IS NULL
		BEGIN

			-- LAS REASIGNACIONES INVOCAN DIRECTAMENTE LOS PROCEDIMIENTOS DE HISTORICO
			BEGIN TRY

				EXECUTE PRC_ASDK_HIST_RECEPTOR
					@i_item_id = @io_serv_id,
					@i_row_type = 4,
					@i_author = @i_serv_user_id_modifier,
					@i_new_user = @v_serv_responsible_id,
					@i_new_group = @v_serv_responsible_group_id,
					@i_category_id = @i_serv_category_id,
					@i_priority_id = @i_serv_priority_id,
					@i_impact_id = @i_serv_impact_id,
					@i_urgency_id = @i_serv_urgency_id,
					@i_status_id = @i_serv_status_id,
					@i_reason_id = @i_serv_reason_id,
					@i_service_id = @i_serv_service_id,
					@i_sla_id = @i_serv_service_sla_id,
					@i_commentary = @i_serv_commentary,
					@o_err_cod = @v_err_cod OUTPUT,
					@o_err_msg = @v_err_msg OUTPUT;

			END TRY
			BEGIN CATCH

				SET @v_err_msg = 'Error executing historic procedure: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

			END CATCH;

		END;
 
		SELECT @v_current_ci_id = serv_associated_ci
		FROM ASDK_SERVICE_CALL
		WHERE serv_id = @io_serv_id

		-- RELACIONES DEL ITEM --
		BEGIN TRY
		IF @i_serv_associated_ci IS NOT NULL AND @i_serv_associated_ci <> 0
		BEGIN

			-- ELIMINA LA RELACION ACTUAL
			EXECUTE dbo.PRC_ASDK_RELATION
				@i_operation = 'D',
				@i_option = 'K',
				@i_serv_id = @io_serv_id,
				@i_ci_id = @v_current_ci_id,
				@o_err_cod = @o_err_cod OUTPUT,
				@o_err_msg = @o_err_msg OUTPUT;

			-- INSERTA LA NUEVA RELACION
			EXECUTE dbo.PRC_ASDK_RELATION
				@i_operation = 'I',
				@i_option = 'K',
				@i_serv_id = @io_serv_id,
				@i_ci_id = @i_serv_associated_ci,
				@i_user_id_modifier = @i_serv_user_id_modifier,
				@i_relation_cause_id = 5,-- Associated CI
				@o_err_cod = @o_err_cod OUTPUT,
				@o_err_msg = @o_err_msg OUTPUT;

		END
		ELSE
		IF (@i_serv_associated_ci IS NULL OR @i_serv_associated_ci = 0) AND LEN(@v_current_ci_id) > 0
		BEGIN

			-- ELIMINA LA RELACION ACTUAL
			EXECUTE dbo.PRC_ASDK_RELATION
				@i_operation = 'D',
				@i_option = 'K',
				@i_serv_id = @io_serv_id,
				@i_ci_id = @v_current_ci_id,
				@o_err_cod = @o_err_cod OUTPUT,
				@o_err_msg = @o_err_msg OUTPUT;

		END
		END TRY
		BEGIN CATCH

			SET @v_err_msg = 'Error executing relation procedure: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

		END CATCH;
 
		-- ITEMS ASOCIADOS AL SERVICIO --
		SELECT @v_serv_involved_users = COUNT(1)
		FROM ASDK_SERVICE_USER 
		WHERE seus_service_id = @i_serv_service_id;

		SELECT @v_serv_involved_cis = COUNT(1)
		FROM ASDK_SERVICE_CI
		WHERE fl_int_service_id = @i_serv_service_id;

		SELECT @v_serv_involved_companys = COUNT(1)
		FROM ASDK_SERVICE_COMPANY
		WHERE seco_service_id = @i_serv_service_id;

		BEGIN

			-- OBTIENE LA FECHA DE ATECION QUE TIENE LA LLAMADA DE SERVICIO ACTUALMENTE
			SELECT @v_serv_vendor_id = serv_vendor_id,
				@v_serv_customer_id = serv_customer_id,
				@v_serv_solution_real_date = serv_solution_real_date,
				@v_serv_attention_real_date = serv_attention_real_date,
				@v_serv_status_id = ISNULL(@i_serv_status_id,serv_status_id),
				@v_serv_statustmp_id = serv_status_id,
				@v_serv_attention_time = serv_attention_time,
				@v_serv_solution_time = serv_solution_time,
				@v_serv_attention_esti_date = serv_attention_esti_date,
				@v_serv_expired_date = serv_expired_date,
				@v_serv_attention_real_time = serv_attention_real_time,
				@v_serv_solution_real_time	= serv_solution_real_time,
				@v_serv_category_id = ISNULL(@i_serv_category_id, serv_category_id),
				@v_serv_impact_id = ISNULL(ISNULL(@i_serv_impact_id, serv_impact_id), -1),
				@v_serv_urgency_id = ISNULL(ISNULL(@i_serv_urgency_id, serv_urgency_id), -1),
				@v_serv_priority_id = ISNULL(ISNULL(@i_serv_priority_id, serv_priority_id), -1),
				@v_serv_opened_date = serv_opened_date,
				@v_serv_description = ISNULL(@i_serv_description,serv_description),
				@v_serv_responsible_id =
					CASE WHEN [dbo].[FUN_ASDK_STATUS_BEHAVIOR]('B', ISNULL(@i_serv_status_id, serv_status_id), 4) = 0
						AND ISNULL(@i_serv_routing_type, 0) = 0 THEN serv_responsible_id
						ELSE ISNULL(@v_serv_responsible_id, serv_responsible_id) END,
				@v_serv_registry_type_id = ISNULL(@i_serv_registry_type_id,serv_registry_type_id),
				@v_serv_commentary = ISNULL(@i_serv_commentary,serv_commentary),
				@v_serv_responsible_group_id =
					CASE WHEN [dbo].[FUN_ASDK_STATUS_BEHAVIOR]('B', ISNULL(@i_serv_status_id, serv_status_id), 4) = 0
						THEN serv_responsible_group_id ELSE ISNULL(@v_serv_responsible_group_id, serv_responsible_group_id) END,
				@v_serv_estimated_cost = ISNULL(@i_serv_estimated_cost, serv_estimated_cost),
				@v_serv_real_cost = ISNULL(@i_serv_real_cost, serv_real_cost),
				@v_serv_reason_id = ISNULL(@i_serv_reason_id, serv_reason_id),
				@v_serv_associated_ci = @i_serv_associated_ci,
				@v_serv_service_id = ISNULL(@i_serv_service_id, serv_service_id),
				@v_serv_service_sla_id = ISNULL(@i_serv_service_sla_id, serv_service_sla_id),
				@v_serv_service_sla_id_current = serv_service_sla_id,
				@v_serv_involved_user = serv_involved_user,
				@v_serv_involved_ci = serv_involved_ci,
				@v_serv_involved_company = serv_involved_company,
				@v_serv_procedure_id = serv_procedure_id,
				@v_serv_description_nohtml	= ISNULL(@i_serv_description_nohtml, serv_description_nohtml),
				@v_serv_commentary_nohtml = ISNULL(@i_serv_commentary_nohtml, serv_commentary_nohtml),
				@v_serv_status_type = serv_status_type,
				@v_serv_sdb_last_shot = COALESCE(tida_sdb_last_shot, serv_sdb_last_shot, serv_opened_date),
				@v_serv_current_time = COALESCE(tida_current_time, serv_current_time * 60, 0),
				@v_serv_current_progress = serv_current_progress,
				@v_serv_fl_int_project_id = serv_fl_int_project_id,
				@v_real_status_time = ISNULL(serv_status_time * 60, 0) + ISNULL(tida_actual_time, 0)
			FROM ASDK_SERVICE_CALL
			LEFT JOIN ASDK_TIMES_DATA ON ASDK_TIMES_DATA.tida_case_id = ASDK_SERVICE_CALL.serv_id
			WHERE serv_id = @io_serv_id
			
			IF @@ROWCOUNT = 0
			BEGIN
				RAISERROR('Item not found',11,1);
			END;

		END;
		
		BEGIN

			SELECT @v_calendar_id = fl_int_calendar_id
			FROM ASDK_SERVICE
			WHERE	fl_int_service_id = @v_serv_service_id;

			IF @@ROWCOUNT = 0
				RAISERROR('Calendar not found', 11, 1);

		END;

		IF ISNULL(@i_serv_status_id, @v_serv_status_id) <> @v_serv_status_id
			SET @v_actual_time = 0;

		-- CALCULO DE TIEMPOS --
		IF @v_serv_status_type = 0 AND dbo.FUN_ASDK_STATUS_BEHAVIOR('A',@i_serv_status_id,NULL) = 1
		BEGIN

			SET @v_serv_sdb_last_shot = GETDATE();

			UPDATE ASDK_TIMES_DATA SET
				tida_status_type = 1,
				tida_actual_time = @v_actual_time,
				tida_sdb_last_shot = @v_serv_sdb_last_shot
			WHERE tida_case_id = @io_serv_id;

		END
		ELSE IF @v_serv_status_type = 1 AND dbo.FUN_ASDK_STATUS_BEHAVIOR('A',@i_serv_status_id,NULL) = 0
		BEGIN

			SET @v_serv_current_time = ISNULL(@v_serv_current_time,0) + dbo.FUN_ASDK_TOTAL_TIME(@v_calendar_id,ISNULL(@v_serv_sdb_last_shot,@v_serv_opened_date),GETDATE());
			SET @v_serv_sdb_last_shot = GETDATE();

			UPDATE ASDK_TIMES_DATA SET
				tida_current_time = @v_serv_current_time,
				tida_sdb_last_shot = @v_serv_sdb_last_shot,
				tida_actual_time = @v_actual_time,
				tida_status_type = 0
			WHERE tida_case_id = @io_serv_id;

		END
		ELSE
		BEGIN

			UPDATE ASDK_TIMES_DATA SET
				tida_status_type = @v_serv_status_type,
				tida_actual_time = ISNULL(@v_actual_time, tida_actual_time)
			WHERE tida_case_id = @io_serv_id;

		END;

		SET @v_serv_status_type = dbo.FUN_ASDK_STATUS_BEHAVIOR('A',@i_serv_status_id, NULL);

		SET @v_serv_final_status =
			CASE WHEN
					dbo.FUN_ASDK_STATUS_BEHAVIOR('I', @v_serv_status_id, NULL) = 1
					AND dbo.FUN_ASDK_STATUS_BEHAVIOR('J', @v_serv_status_id, NULL) = 0
				THEN 1
				ELSE 0
			END;

		IF @v_serv_attention_real_date IS NULL AND @v_serv_statustmp_id <> @i_serv_status_id
		BEGIN

			SET @v_serv_attention_real_date = GETDATE();
			SET @v_serv_attention_real_time = @v_serv_current_time / 60;

		END

		IF @v_serv_final_status = 0 AND @v_serv_colsed_date IS NULL AND @i_serv_status_id IS NOT NULL
		BEGIN 
			SET @v_serv_colsed_date = GETDATE();

		END;

		IF dbo.FUN_ASDK_STATUS_BEHAVIOR('E', @i_serv_status_id, 1) = 1 AND @v_serv_solution_real_date IS NULL
		BEGIN

			SET @v_serv_solution_real_date = GETDATE();
			SET @v_serv_solution_real_time = @v_serv_current_time / 60;

		END;

		IF (@i_serv_service_sla_id IS NOT NULL AND @i_serv_service_sla_id <> @v_serv_service_sla_id_current) OR (@i_serv_customer_id IS NOT NULL AND @i_serv_customer_id <> @v_serv_customer_id) OR ( @i_serv_vendor_id IS NOT NULL AND @i_serv_vendor_id <> @v_serv_vendor_id)
		BEGIN

			BEGIN

				SELECT	@v_calendar_id = ASDK_SERVICE.fl_int_calendar_id
				FROM	ASDK_SLA 
				INNER JOIN ASDK_SERVICE ON ASDK_SLA.FL_INT_SERVICE_ID = ASDK_SERVICE.FL_INT_SERVICE_ID
				WHERE	(ASDK_SLA.SLA_ID = @i_serv_service_sla_id);

				IF @@ROWCOUNT = 0
					RAISERROR('Calendar not found', 11, 1);

			END;

			BEGIN TRY

				EXEC PRC_ASDK_SLA_TIMES
					@i_sla_id = @i_serv_service_sla_id,
					@i_user_id = @i_serv_customer_id,
					@i_comp_id = @i_serv_vendor_id,
					@i_stage_id = NULL,
					@i_row_type = 4,
					@i_ci_id = @i_serv_associated_ci,
					@o_attention = @v_serv_attention_time OUTPUT,
					@o_solution = @v_serv_solution_time OUTPUT,
					@o_err_cod = @v_err_cod OUTPUT,
					@o_err_msg = @v_err_msg OUTPUT;

			END TRY
			BEGIN CATCH

				SET @v_err_msg = 'Error executing times procedure: ' + @v_err_msg + ' Details: ' + ERROR_MESSAGE();

			END CATCH;

			SET @v_serv_attention_esti_date = dbo.FUN_ASDK_FINAL_DATE(@v_calendar_id, @v_serv_attention_time, @v_serv_opened_date);
			SET @v_serv_expired_date = dbo.FUN_ASDK_FINAL_DATE(@v_calendar_id, @v_serv_solution_time, @v_serv_attention_esti_date);

			SELECT @v_calendarbefore_id = ASDK_SERVICE.fl_int_calendar_id
			FROM ASDK_SLA
			INNER JOIN ASDK_SERVICE ON ASDK_SLA.fl_int_service_id = ASDK_SERVICE.fl_int_service_id
			WHERE	ASDK_SLA.sla_id = @v_serv_service_sla_id_current;
 
			IF @v_calendarbefore_id <> @v_calendar_id
			BEGIN

				SET @v_serv_sdb_last_shot = GETDATE();
				SET @v_serv_current_time = dbo.FUN_ASDK_REAL_TIME(@io_serv_id, @v_serv_opened_date, @v_calendar_id, @v_serv_sdb_last_shot, @v_serv_status_type, ISNULL(@i_serv_status_id, @v_serv_status_id));
				
				SELECT @v_status_change = MAX(achi_created)
				FROM ASDK_ACTION_HIST
				JOIN ASDK_HIST_MODIFY ON himo_action_hist_id = achi_id
				WHERE achi_item_id = @io_serv_id
				AND himo_field_modified = 'STATUS';
				
				SET @v_real_status_time = dbo.FUN_ASDK_TOTAL_TIME(@v_calendar_id, ISNULL(@v_status_change, @v_serv_opened_date), @v_serv_sdb_last_shot);

				UPDATE ASDK_TIMES_DATA SET
					tida_current_time = @v_serv_current_time,
					tida_sdb_last_shot = @v_serv_sdb_last_shot,
					tida_actual_time = @v_real_status_time % 60,
					tida_calendar_id = @v_calendar_id
				WHERE tida_case_id = @io_serv_id;

			END;

		END;

		SET @v_serv_current_progress = CASE ISNULL(@v_serv_attention_time, 0) + ISNULL(@v_serv_solution_time,0) WHEN 0 THEN 100
			ELSE @v_serv_current_time / 60 * 100 / (ISNULL(@v_serv_attention_time,0) + ISNULL(@v_serv_solution_time,0)) END;

		IF @i_serv_customer_id IS NULL
			SET @i_serv_customer_id = @v_serv_customer_id;

		IF @i_serv_vendor_id IS NULL
			SET @i_serv_vendor_id = @v_serv_vendor_id;

		BEGIN TRY

			-- ACTUALIZACION DEL ITEM --
			UPDATE ASDK_SERVICE_CALL
			SET serv_category_id = @v_serv_category_id,
				serv_priority_id = @v_serv_priority_id,
				serv_description = @v_serv_description,
				serv_responsible_id = @v_serv_responsible_id,
				serv_customer_id = @i_serv_customer_id,
				serv_status_id = @v_serv_status_id,
				serv_colsed_date = @v_serv_colsed_date,
				serv_commentary = @v_serv_commentary,
				serv_expired_date = @v_serv_expired_date,
				serv_solution_real_date = @v_serv_solution_real_date,
				serv_responsible_group_id = @v_serv_responsible_group_id,
				serv_attention_esti_date = @v_serv_attention_esti_date,
				serv_registry_type_id = @v_serv_registry_type_id,
				serv_attention_real_date = @v_serv_attention_real_date,
				serv_user_id_modifier = @i_serv_user_id_modifier,
				serv_vendor_id = @i_serv_vendor_id,
				serv_attention_time = @v_serv_attention_time,
				serv_solution_time = @v_serv_solution_time,
				serv_estimated_cost = @v_serv_estimated_cost,
				serv_real_cost = @v_serv_real_cost,
				serv_reason_id = @v_serv_reason_id,
				serv_impact_id = @v_serv_impact_id,
				serv_urgency_id = @v_serv_urgency_id,
				serv_service_id = @v_serv_service_id,
				serv_service_sla_id = @v_serv_service_sla_id,
				serv_involved_user = @v_serv_involved_users,
				serv_involved_ci = @v_serv_involved_cis,
				serv_involved_company = @v_serv_involved_companys,
				serv_associated_ci = @v_serv_associated_ci,
				serv_solution_real_time = @v_serv_solution_real_time,
				serv_attention_real_time = @v_serv_attention_real_time,
				serv_procedure_id = @v_serv_procedure_id,
				serv_final_status = @v_serv_final_status,
				serv_interface_id = ISNULL(@i_interface_id,serv_interface_id),
				serv_description_nohtml = @v_serv_description_nohtml,
				serv_commentary_nohtml = @v_serv_commentary_nohtml,
				serv_provider_uc_id = ISNULL(@v_serv_provider_uc_id,serv_provider_uc_id),
				serv_status_type = @v_serv_status_type,
				serv_sdb_last_shot = @v_serv_sdb_last_shot,
				serv_current_time = @v_serv_current_time / 60,
				serv_current_progress = @v_serv_current_progress,
				serv_status_time = @v_real_status_time / 60,
				serv_subject = ISNULL(@i_serv_subject, serv_subject)
			WHERE serv_id = @io_serv_id;

			EXEC PRC_ASDK_RULE_CONTEXT
				4,
				@io_serv_id;

			IF (@v_serv_final_status = 0)
				DELETE FROM ASDK_TIMES_DATA
				WHERE tida_case_id = @io_serv_id;			
			
			SET @v_value =
				CASE
					WHEN dbo.FUN_ASDK_STATUS_BEHAVIOR('J', @i_serv_status_id, 4) = 1 THEN 2
					WHEN dbo.FUN_ASDK_STATUS_BEHAVIOR('E', @i_serv_status_id, 1) = 1
						OR @v_serv_final_status = 0 THEN 1
				END;

			IF @v_value IS NOT NULL
			BEGIN

				EXEC [dbo].PRC_ASDK_PROJECT_CONTRACT
					@i_operation = 'U',
					@i_option = 'B',
					@i_type_case = 4,
					@io_id = NULL,
					@i_case_id = @io_serv_id,
					@i_value = @v_value,
					@o_err_cod = @o_err_cod,
					@o_err_msg = @o_err_msg;

			END;

		END TRY
		BEGIN CATCH

			SELECT @o_err_cod = -1,
				@v_err_msg = 'Error updating item - ' + ERROR_MESSAGE();

			RAISERROR(@v_err_msg, 11, 1);

		END CATCH;
				
		BEGIN TRY

			-- ACTUALIZACION DE DATOS EN TABLA DE DIAS REGLAS
			EXECUTE [dbo].[PRC_ASDK_MODIFY_DATES_CASE] 
				@i_case_id = @io_serv_id,
				@i_case_type = 4,
				@i_type_status_id = @v_serv_final_status,
				@i_project_id = @v_serv_fl_int_project_id,
				@i_date_registry = @v_serv_opened_date,
				@i_date_attention = @v_serv_attention_real_date,
				@i_date_solution = @v_serv_solution_real_date,
				@i_calendar_id = @v_calendar_id;

		END TRY
		BEGIN CATCH

			SET @v_err_msg = 'Error executing procedure - ' + ERROR_MESSAGE();

		END CATCH
		
		IF dbo.FUN_ASDK_STATUS_BEHAVIOR('F', @i_serv_status_id, 1) = 1
		BEGIN

			EXECUTE dbo.PRC_ASDK_VOTING
				@i_operation = 'A',
				@i_voti_item_id = @io_serv_id,
				@i_item_type = 4,
				@i_voti_status_id	= @i_serv_status_id,
				@i_voti_project_id = @v_serv_fl_int_project_id,
				@o_err_cod = @v_err_cod OUTPUT,
				@o_err_msg = @v_err_msg OUTPUT;

		END;
		
	END TRY
	BEGIN CATCH

		SET @v_err_msg = ERROR_MESSAGE();
		RAISERROR(@v_err_msg, 11, 2);

	END CATCH;

	/*=========================================*/ 
	/* OPERACION DE CONSULTA DEL REQUERIMIENTO */
	/*=========================================*/
	IF @i_operation = 'S'
	BEGIN

		IF @i_option = 'A'
		BEGIN
			SET @v_select =
				'SELECT a.serv_id id,
					a.serv_cost,
					a.serv_id_by_project id_by_project,
					a.serv_category_id category_id,
					b.ctg_caption category_desc,
					b.fl_str_hierarchy category_hierarchy,
					a.serv_codreceptor codreceptor,
					l.uname codreceptor_uname,
					l.username codreceptor_username,
					a.serv_impact_id impact_id,
					d.impa_description impact_desc,
					a.serv_urgency_id urgency_id,
					e.urge_description urgency_desc,
					a.serv_priority_id priority_id,
					f.prio_description priority_desc,
					CASE WHEN v.solved = 1 THEN 1 ELSE 0 END solved,
					CASE WHEN v.canceled = 1 THEN 1 ELSE 0 END canceled,
					a.serv_status_id status_id,
					g.stat_name status_desc,
					a.serv_opened_date opened_date,
					a.serv_colsed_date colsed_date,
					a.serv_responsible_id responsible_id,
					h.uname responsible_uname,
					h.username responsible_username,
					a.serv_expired_date expired_date,
					a.serv_solution_real_date solution_real_date,
					a.serv_attention_esti_date attention_esti_date,
					a.serv_attention_real_date attention_real_date,
					a.serv_attention_time attention_time,
					a.serv_solution_time solution_time,
					a.serv_attention_real_time attention_real_time,
					a.serv_solution_real_time solution_real_time,
					a.serv_registry_type_id registry_type_id,
					j.ret_description registry_type_desc,
					a.serv_responsible_group_id responsible_group_id,
					k.grp_name responsible_group_desc,
					a.serv_author author_id,
					c.uname author_uname,
					c.username author_username,
					a.serv_user_id_modifier user_modifier_id,
					i.uname user_modifier_uname,
					i.username user_modifier_username,
					a.serv_fl_int_project_id project_id,
					n.fl_str_project_name project_desc,
					a.serv_estimated_cost estimated_cost,
					a.serv_real_cost real_cost,
					a.serv_reason_id reason_id,
					a.serv_provider_uc_id,
					o.reas_description reason_desc,
					a.serv_associated_ci associated_ci,
					t.fl_str_ci_name associatedDec,
					a.serv_service_id service_id,
					u.name service_desc,
					a.serv_service_sla_id service_sla_id,
					r.name sla_desc,
					a.serv_procedure_id serv_procedure_id,
					g.stat_background_color background_color,
					g.stat_foreground_color foreground_color,
					g.stat_background_color_rgb background_color_rgb,
					g.stat_foreground_color_rgb foreground_color_rgb,
					a.serv_current_time currenttime,
					a.serv_current_progress current_progress,
					a.serv_sdb_last_shot sdb_last_shot,
					a.serv_customer_id customer_id,
					m.uname customer_uname,
					m.username customer_username,
					a.serv_vendor_id vendor_id,
					s.company vendor_desc,
					aa.nomdepto departamento,
					ab.description piso,
					ac.description ciudad,
					ad.description pais,
					ae.description sede,
					af.description edificio,
					ag.description company,
					ah.description numberdep,
					ai.cha_description cargo,
					ROUND(a.serv_effort / 60, 2) effort,
					a.serv_interface_id interface_id,
					ISNULL(p.state,0) state,
					a.serv_final_status,
					w.surv_case_id surv_case_id,
					m.coddepto,
					m.floorid,
					m.cityid,
					m.countryid,
					m.siteid,
					m.buildingid,
					a.serv_involved_ci involved_ci,
					a.serv_involved_user involved_user,
					m.cha_id,
					m.companyid,
					m.numberdepid,
					a.serv_commentary_nohtml commentary_nohtml,
					n.fl_int_id project_id,
					a.serv_subject subject';

			SET @v_from_clause = '
				FROM ASDK_SERVICE_CALL a
				LEFT JOIN ASDK_CATEGORY b ON (a.serv_category_id = b.ctg_index)
				LEFT JOIN USUARIOS c ON (a.serv_author = c.codusuario)
				LEFT JOIN ASDK_IMPACT d ON (a.serv_impact_id = d.impa_id)
				LEFT JOIN ASDK_URGENCY e ON (a.serv_urgency_id = e.urge_id)
				LEFT JOIN ASDK_PRIORITY f ON (a.serv_priority_id = f.prio_id)
				LEFT JOIN AFW_STATUS g ON (a.serv_status_id = g.stat_id)
				LEFT JOIN USUARIOS h ON (a.serv_responsible_id = h.codusuario)
				LEFT JOIN USUARIOS i ON (a.serv_user_id_modifier = i.codusuario)
				LEFT JOIN REGISTRY_TYPE j WITH(NOEXPAND) ON (a.serv_registry_type_id = j.ret_id)
				LEFT JOIN GROUPHD k ON (a.serv_responsible_group_id = k.grp_id)
				LEFT JOIN USUARIOS l ON (a.serv_codreceptor = l.codusuario)
				LEFT JOIN USUARIOS m ON (a.serv_customer_id = m.codusuario)
				LEFT JOIN ASDK_PROJECTS n ON (a.serv_fl_int_project_id = n.fl_int_id)
				LEFT JOIN ASDK_REASON o ON (a.serv_reason_id = o.reas_id)
				LEFT JOIN STATE p WITH(NOEXPAND) ON (p.sttr_code_ini = a.serv_status_id)
				LEFT JOIN ASDK_SLA r ON (a.serv_service_sla_id = r.sla_id)
				LEFT JOIN VENDOR s ON (a.serv_vendor_id = s.vendorid)
				LEFT JOIN CI_CONFIGURATION_ITEM t ON (t.fl_int_ci_id = serv_associated_ci)
				LEFT JOIN ASDK_SERVICE u ON (u.fl_int_service_id = a.serv_service_id)
				LEFT JOIN ASDK_STATUS_BEHAVIOR v ON (v.stat_id = a.serv_status_id)
					LEFT JOIN DEPARTAMENTOS aa WITH(NOEXPAND) ON (m.coddepto = aa.coddepto)
					LEFT JOIN FLOOR ab WITH(NOEXPAND) ON (m.floorid = ab.floorid)
					LEFT JOIN CITY ac WITH(NOEXPAND) ON (m.cityid = ac.cityid)
					LEFT JOIN COUNTRY ad WITH(NOEXPAND) ON (m.countryid = ad.countryid)
					LEFT JOIN SITE ae WITH(NOEXPAND) ON (m.siteid = ae.siteid)
					LEFT JOIN BUILDING af WITH(NOEXPAND) ON (m.buildingid = af.buildingid)
					LEFT JOIN COMPANY ag WITH(NOEXPAND) ON (m.companyid = ag.companyid)
					LEFT JOIN NUMBERDEP ah WITH(NOEXPAND) ON (m.numberdepid = ah.numberdepid)
					LEFT JOIN CHARGES ai WITH(NOEXPAND) ON (m.cha_id = ai.cha_id)
				LEFT JOIN SURV_CASES w WITH(NOEXPAND) ON (a.serv_id = w.surv_case_id)';

			EXECUTE PRC_AFW_RUN_QUERY_MDT
				@o_page_size OUTPUT,
				@i_LowerBound,
				@i_HigerBound,
				@v_from_clause,
				@i_where_clause,
				@i_order_clause,
				@v_select,
				'PRC_ASDK_SERVICE_CALL',
				@i_operation,
				@i_option,
				36,
				@i_aux_cte,
				@i_aux_join;

		END;

		IF @i_option = 'B'
		BEGIN
			
			UPDATE ASDK_SERVICE_CALL SET
				serv_commentary = CASE WHEN serv_commentary LIKE '<DIV></DIV>' THEN '' ELSE serv_commentary END
			WHERE serv_id = @io_serv_id
			AND serv_commentary LIKE '<DIV></DIV>';

			SELECT serv_id id,
				a.serv_cost,
				a.serv_id_by_project id_by_project,
				a.serv_category_id category_id,
				b.ctg_caption category_desc,
				b.fl_str_hierarchy category_hierarchy,
				a.serv_codreceptor codreceptor,
				l.uname as codreceptor_uname,
				l.username as codreceptor_username,
				serv_impact_id impact_id,
				d.impa_description impact_desc,
				a.serv_urgency_id urgency_id,
				e.urge_description urgency_desc,
				serv_priority_id priority_id,
				f.prio_description priority_desc,
				CASE WHEN v.solved = 1 THEN 1 ELSE 0 END solved,
				CASE WHEN v.canceled = 1 THEN 1 ELSE 0 END canceled,
				a.serv_status_id status_id,
				g.stat_name status_desc,
				a.serv_opened_date opened_date,
				a.serv_colsed_date colsed_date,
				a.serv_description description,
				a.serv_responsible_id responsible_id,
				h.uname as responsible_uname,
				h.username as responsible_username,
				a.serv_expired_date expired_date,
				a.serv_solution_real_date solution_real_date,
				a.serv_attention_esti_date attention_esti_date,
				a.serv_attention_real_date attention_real_date,
				a.serv_attention_time attention_time,
				a.serv_solution_time solution_time,
				a.serv_registry_type_id registry_type_id,
				j.ret_description registry_type_desc,
				a.serv_commentary commentary,
				a.serv_provider_uc_id,
				a.serv_responsible_group_id responsible_group_id,
				k.grp_name responsible_group_desc,
				a.serv_author author_id,
				c.uname as author_uname,
				c.username as author_username,
				a.serv_user_id_modifier user_modifier_id,
				i.uname as user_modifier_uname,
				i.username as user_modifier_username,
				serv_fl_int_project_id project_id,
				n.fl_str_project_name project_desc,
				a.serv_estimated_cost estimated_cost,
				a.serv_real_cost real_cost,
				a.serv_reason_id reason_id,
				o.reas_description reason_desc,
				serv_associated_ci associated_ci,
				t.fl_str_ci_name associatedDec,
				a.serv_service_id service_id,
				u.name service_desc,
				a.serv_service_sla_id service_sla_id,
				r.name sla_desc,
				a.serv_procedure_id serv_procedure_id,
				g.stat_background_color background_color,
				g.stat_foreground_color foreground_color,
				g.stat_background_color_rgb background_color_rgb,
				g.stat_foreground_color_rgb foreground_color_rgb,
				a.serv_current_time currenttime,
				a.serv_current_progress current_progress,
				a.serv_sdb_last_shot sdb_last_shot,
				a.serv_customer_id customer_id,
				m.uname as customer_uname,
				m.username as customer_username,
				a.serv_vendor_id vendor_id,
				s.company as vendor_desc,
				aa.nomdepto departamento,
				ab.description piso,
				ac.description ciudad,
				ad.description pais,
				ae.description sede,
				af.description edificio,
				u.name servicio,
				ROUND(a.serv_effort / 60, 2) effort,
				a.serv_interface_id interface_id,
				ISNULL(p.state,0) state,
				a.serv_commentary_nohtml commentary_nohtml,
				a.serv_description_nohtml description_nohtml,
				x.plap_emaildate,
				a.serv_final_status,
				a.serv_subject subject
			FROM ASDK_SERVICE_CALL a
			LEFT JOIN ASDK_CATEGORY b ON (a.serv_category_id = b.ctg_index)
			LEFT JOIN USUARIOS c ON (a.serv_author = c.codusuario)
			LEFT JOIN ASDK_IMPACT d ON (a.serv_impact_id = d.impa_id)
			LEFT JOIN ASDK_URGENCY e ON (a.serv_urgency_id = e.urge_id)
			LEFT JOIN ASDK_PRIORITY f ON (a.serv_priority_id = f.prio_id)
			LEFT JOIN AFW_STATUS g ON (a.serv_status_id = g.stat_id)
			LEFT JOIN USUARIOS h ON (a.serv_responsible_id = h.codusuario)
			LEFT JOIN USUARIOS i ON (a.serv_user_id_modifier = i.codusuario)
			LEFT JOIN REGISTRY_TYPE j WITH(NOEXPAND) ON (a.serv_registry_type_id = j.ret_id)
			LEFT JOIN GROUPHD k ON (a.serv_responsible_group_id = k.grp_id)
			LEFT JOIN USUARIOS l ON (a.serv_codreceptor = l.codusuario)
			LEFT JOIN USUARIOS m ON (a.serv_customer_id = m.codusuario)
			LEFT JOIN ASDK_PROJECTS n ON (a.serv_fl_int_project_id = n.fl_int_id)
			LEFT JOIN ASDK_REASON o ON (a.serv_reason_id = o.reas_id)
			LEFT JOIN STATE p WITH(NOEXPAND) ON (p.sttr_code_ini = a.serv_status_id)
			LEFT JOIN ASDK_SLA r ON (a.serv_service_sla_id = r.sla_id)
			LEFT JOIN VENDOR s ON (a.serv_vendor_id = s.vendorid)
			LEFT JOIN CI_CONFIGURATION_ITEM t ON (t.fl_int_ci_id = serv_associated_ci)
			LEFT JOIN ASDK_SERVICE u ON (u.fl_int_service_id = a.serv_service_id)
			LEFT JOIN ASDK_STATUS_BEHAVIOR v ON (v.stat_id = a.serv_status_id)
			LEFT JOIN DEPARTAMENTOS aa WITH(NOEXPAND) ON (m.coddepto = aa.coddepto)
			LEFT JOIN FLOOR ab WITH(NOEXPAND) ON (m.floorid = ab.floorid)
			LEFT JOIN CITY ac WITH(NOEXPAND) ON (m.cityid = ac.cityid)
			LEFT JOIN COUNTRY ad WITH(NOEXPAND) ON (m.countryid = ad.countryid)
			LEFT JOIN SITE ae WITH(NOEXPAND) ON (m.siteid = ae.siteid)
			LEFT JOIN BUILDING af WITH(NOEXPAND) ON (m.buildingid = af.buildingid)
			LEFT JOIN COMPANY ag WITH(NOEXPAND) ON (m.companyid = ag.companyid)
			LEFT JOIN NUMBERDEP ah WITH(NOEXPAND) ON (m.numberdepid = ah.numberdepid)
			LEFT JOIN CHARGES ai WITH(NOEXPAND) ON (m.cha_id = ai.cha_id)
			LEFT JOIN SURV_CASES w WITH(NOEXPAND) ON (a.serv_id = w.surv_case_id)
			LEFT JOIN ACC_MAIL_SOURCE_LOG x ON (x.plap_id_asdk = a.serv_id)
			WHERE a.serv_id = @io_serv_id;

			-- Registra log de tiempos de ejecucion
			IF @v_log_time = 'Y'
			BEGIN

				SET @v_elapsed_time_cs = CAST(CAST(GETDATE() AS BINARY(8)) AS BIGINT) - @v_start_time;

				INSERT INTO AFW_SQL_TIMES (
					sqlt_database_object,
					sqlt_operation,
					sqlt_option,
					sqlt_started,
					sqlt_finished,
					sqlt_elapsed_cs,
					sqlt_sentence
				) VALUES (
					'PRC_ASDK_SERVICE_CALL',
					@i_operation,
					@i_option,
					@v_started_ts,
					GETDATE(),
					@v_elapsed_time_cs,
					'STATIC'
				);

			END;

		END;

		IF @i_option = 'C'
		BEGIN
		
			SELECT 
				  CASE @i_is_description_html
					  WHEN 1 THEN serv_description
					  ELSE serv_description_nohtml
				   END
					  description
			  FROM asdk_service_call a
			 WHERE a.serv_id = @io_serv_id;

			-- Registra log de tiempos de ejecucion
			IF @v_log_time = 'Y'
			BEGIN

				SET @v_elapsed_time_cs = CAST(CAST(GETDATE() AS BINARY(8)) AS BIGINT) - @v_start_time;

				INSERT INTO AFW_SQL_TIMES (
					sqlt_database_object,
					sqlt_operation,
					sqlt_option,
					sqlt_started,
					sqlt_finished,
					sqlt_elapsed_cs,
					sqlt_sentence
				) VALUES (
					'PRC_ASDK_SERVICE_CALL',
					@i_operation,
					@i_option,
					@v_started_ts,
					GETDATE(),
					@v_elapsed_time_cs,
					'STATIC-DESC'
				);

			END;

		END;

		/*================================*/
		/* CONSULTA SI HAY TAREAS ACTIVAS */
		/*================================*/
		IF @i_option = 'D'
		BEGIN

			SELECT dbo.FUN_ASDK_ACTIVE_TASK(@io_serv_id, 4, DEFAULT);

		END;

		IF @i_option = 'E'
		BEGIN

			SET @v_base_sentence =
				'SELECT @v_count = count(1)' + CHAR(10) +
				'FROM ASDK_SERVICE_CALL a' + CHAR(10) +
				'LEFT JOIN ASDK_CATEGORY b ON (a.serv_category_id = b.ctg_index)' + CHAR(10) +
				'LEFT JOIN USUARIOS c ON (a.serv_author = c.codusuario)' + CHAR(10) +
				'LEFT JOIN ASDK_IMPACT d ON (a.serv_impact_id = d.impa_id)' + CHAR(10) +
				'LEFT JOIN ASDK_URGENCY e ON (a.serv_urgency_id = e.urge_id)' + CHAR(10) +
				'LEFT JOIN ASDK_PRIORITY f ON (a.serv_priority_id = f.prio_id)' + CHAR(10) +
				'LEFT JOIN AFW_STATUS g ON (a.serv_status_id = g.stat_id)' + CHAR(10) +
				'LEFT JOIN USUARIOS h ON (a.serv_responsible_id = h.codusuario)' + CHAR(10) +
				'LEFT JOIN USUARIOS i ON (a.serv_user_id_modifier = i.codusuario)' + CHAR(10) +
				'LEFT JOIN REGISTRY_TYPE j WITH(NOEXPAND) ON (a.serv_registry_type_id = j.ret_id)' + CHAR(10) +
				'LEFT JOIN GROUPHD k ON (a.serv_responsible_group_id = k.grp_id)' + CHAR(10) +
				'LEFT JOIN USUARIOS l ON (a.serv_codreceptor = l.codusuario)' + CHAR(10) +
				'LEFT JOIN USUARIOS m ON (a.serv_customer_id = m.codusuario)' + CHAR(10) +
				'LEFT JOIN ASDK_PROJECTS n ON (a.serv_fl_int_project_id = n.fl_int_id)' + CHAR(10) +
				'LEFT JOIN ASDK_REASON o ON (a.serv_reason_id = o.reas_id)' + CHAR(10) +
				'LEFT JOIN STATE p WITH(NOEXPAND) ON (p.sttr_code_ini = a.serv_status_id)' + CHAR(10) +
				'LEFT JOIN ASDK_SLA r ON (a.serv_service_sla_id = r.sla_id)' + CHAR(10) +
				'LEFT JOIN VENDOR s ON (a.serv_vendor_id = s.vendorid)' + CHAR(10) +
				'LEFT JOIN CI_CONFIGURATION_ITEM t ON (t.fl_int_ci_id = serv_associated_ci)' + CHAR(10) +
				'LEFT JOIN ASDK_SERVICE u ON (u.fl_int_service_id = a.serv_service_id)' + CHAR(10) +
				'LEFT JOIN ASDK_STATUS_BEHAVIOR v ON (v.stat_id = a.serv_status_id)' + CHAR(10) +
				'LEFT JOIN DEPARTAMENTOS aa WITH(NOEXPAND) ON (m.coddepto = aa.coddepto)' + CHAR(10) +
				'LEFT JOIN FLOOR ab WITH(NOEXPAND) ON (m.floorid = ab.floorid)' + CHAR(10) +
				'LEFT JOIN CITY ac WITH(NOEXPAND) ON (m.cityid = ac.cityid)' + CHAR(10) +
				'LEFT JOIN COUNTRY ad WITH(NOEXPAND) ON (m.countryid = ad.countryid)' + CHAR(10) +
				'LEFT JOIN SITE ae WITH(NOEXPAND) ON (m.siteid = ae.siteid)' + CHAR(10) +
				'LEFT JOIN BUILDING af WITH(NOEXPAND) ON (m.buildingid = af.buildingid)' + CHAR(10) +
				'LEFT JOIN COMPANY ag WITH(NOEXPAND) ON (m.companyid = ag.companyid)' + CHAR(10) +
				'LEFT JOIN NUMBERDEP ah WITH(NOEXPAND) ON (m.numberdepid = ah.numberdepid)' + CHAR(10) +
				'LEFT JOIN CHARGES ai WITH(NOEXPAND) ON (m.cha_id = ai.cha_id)' + CHAR(10) +
				'LEFT JOIN SURV_CASES w WITH(NOEXPAND) ON (a.serv_id = w.surv_case_id)' + CHAR(10) +									
				' WHERE {WHERE_CLAUSE} ' + CHAR(10);
				
			-- Remplaza los alias por el valor correspondiente
			SET @v_where_clause = dbo.FUN_AFW_FIELDS_PARSER(36,@i_where_clause,'WHERE');

			-- Remplaza los tokens por las clausulas Where y Order correspondientes 
			SET @v_base_sentence = REPLACE(@v_base_sentence,'{WHERE_CLAUSE}',@v_where_clause);
	
			-- Ejecuta la sentencia para insertar los registros en la variable de tipo Table
			BEGIN TRY

				EXECUTE sp_executesql @v_base_sentence,@v_parameters,@v_count = @o_page_size OUTPUT;

			END TRY
			BEGIN CATCH

				SELECT @v_sql_msg = ERROR_MESSAGE(),@v_sql_cod = ERROR_NUMBER();

				INSERT INTO AFW_SQL_ERRORS (
					sqle_database_object,
					sqle_operation,
					sqle_option,
					sqle_started,
					sqle_sentence,
					sqle_code,
					sqle_msg
				) VALUES (
					'PRC_ASDK_SERVICE_CALL',
					@i_operation,
					@i_option,
					@v_started_ts,
					@v_base_sentence,
					@v_sql_cod,
					@v_sql_msg
				);

			END CATCH;

		END; 

		/*================================================*/
		/* CONSULTA EL ID POR PROYECTO DE UN SERVICE CALL */
		/*================================================*/
		IF @i_option = 'K'
		BEGIN

			SELECT serv_id_by_project IdByProject
			FROM ASDK_SERVICE_CALL a WITH(NOLOCK)
			WHERE a.serv_id = @io_serv_id;

		END;
		
		/*==================================================*/
		/* CONSULTA EL COMENTARIO DE UN SERVICE CALL */
		/*==================================================*/
		IF @i_option = 'L'
		BEGIN

			SELECT serv_commentary commentary
			FROM asdk_service_call a WITH (NOLOCK)
			WHERE a.serv_id = @io_serv_id;

		END;

	END;

	IF LEN(@v_err_msg) > 0
		RAISERROR(@v_err_msg, 11, 3);

	END TRY
	BEGIN CATCH

		EXEC EXCEPTION_HANDLER
			@i_procedure_operation = @i_operation,
			@i_procedure_option = @i_option,
			@i_error_procedure = 'PRC_ASDK_SERVICE_CALL',
			@i_error_message = @v_err_msg,
			@o_return_number = @o_err_cod,
			@o_return_message = @o_err_msg;

	END CATCH;

END;