create or replace PROCEDURE PRC_ASDK_MODIFICAR_ITEM (
	i_codcaso		IN NUMBER DEFAULT NULL,
	i_action_id	IN NUMBER DEFAULT NULL,
  i_timestamp IN TIMESTAMP DEFAULT NULL
) AS

CURSOR cur_param_values IS
SELECT ASDK_RULES_PARAM.param_id,
	ASDK_RULES_PARAM.param_value
FROM ASDK_RULES_PARAM
JOIN ASDK_RULES_ACTIONS ON ASDK_RULES_PARAM.action_id = ASDK_RULES_ACTIONS.action_id
WHERE ASDK_RULES_ACTIONS.action_id = i_action_id
ORDER BY ASDK_RULES_PARAM.param_id DESC;

v_value						INTEGER;
v_value2						INTEGER;
v_value3						INTEGER;
v_value4						INTEGER;
v_value5						INTEGER;
v_value6						INTEGER;
v_value7						INTEGER;
v_value8						INTEGER;
v_value9						INTEGER;
v_cur_param_id				NUMBER (10, 0);
v_row_type					NUMBER (10, 0);
v_codcaso					NUMBER;
v_profile_id				NUMBER;
v_status_id					NUMBER;
v_responsible_group_id	NUMBER;
v_responsible_id			NUMBER;
v_reason_id					NUMBER;
v_page_size					NUMBER;
v_cis_used					NUMBER;
v_final_status				NUMBER;
v_cur_value_param			VARCHAR2 (8000);
v_codigo						VARCHAR2 (8000);
v_mensaje					VARCHAR2 (8000);
v_query						VARCHAR2 (4000);
v_field						VARCHAR2 (70);
v_table						VARCHAR2 (70);
v_key							VARCHAR2 (70);

--Variables para almacenar el valor de campos utilizados al calcular los tiempos SLA
v_slaservice				NUMBER;
v_service					NUMBER;
v_customer_id				NUMBER;
v_ci_id						NUMBER;
v_vendor_id					NUMBER;
v_project_id				NUMBER;
v_count						NUMBER;
v_state_is_closed			NUMBER;
v_tasks_pending			NUMBER;
v_comment_failed			VARCHAR2 (255);
v_comment_id				NUMBER;
v_lang						NUMBER;
v_rule_id					NUMBER;
v_data_set					PK_ASC_OBJECTS.asc_return_data;
ex_error_executing		EXCEPTION;

BEGIN

	SELECT fl_int_rule_id
	INTO v_rule_id
	FROM ASDK_RULES_MODIFY_ITEMS
	WHERE fl_int_case_id = i_codcaso
	AND fl_int_action_id = i_action_id
  AND fl_exec_timestamp = i_timestamp;

	v_codcaso := i_codcaso;

	BEGIN

		SELECT ASDK_RULES.table_id
		INTO v_row_type
		FROM ASDK_RULES_ACTIONS
		JOIN ASDK_RULES ON ASDK_RULES_ACTIONS.rule_id = ASDK_RULES.rule_id
		WHERE ASDK_RULES_ACTIONS.action_id = i_action_id;

	EXCEPTION WHEN NO_DATA_FOUND THEN
		NULL;

	END;

	v_value := NULL;
	v_value2 := NULL;
	v_value3 := NULL;
	v_value4 := NULL;
	v_value5 := NULL;
	v_value6 := NULL;
	v_value7 := NULL;
	v_value8 := NULL;
	v_value9 := NULL;

	-- Se recorren los parametros de la regla -- Se utilizan para campos que pueden ser modificados mediante la regla
	FOR ind_cur_param_values IN cur_param_values LOOP

		IF ind_cur_param_values.param_id = 1 THEN -- Se arma la variable correspondiente al Asunto del Correo
			v_value := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 2 THEN -- Se arma la variable para el cuerpo del mensaje
			v_value2 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 3 THEN -- Se arma la variable para el cuerpo del mensaje
			v_value3 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 4 THEN -- Se arma la variable para el cuerpo del mensaje
			v_value4 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 5 THEN -- Se arma la variable para el cuerpo del mensaje
			v_value5 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 6 THEN -- Se arma la variable para el cuerpo del mensaje
			v_value6 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 7 THEN -- Se arma la variable para el cuerpo del mensaje
			v_value7 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 8 THEN -- Servicio
			v_value8 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		ELSIF ind_cur_param_values.param_id = 9 THEN -- Sla's
			v_value9 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));

		END IF;

	END LOOP;

	IF v_row_type = 1 THEN

		-- Captura el servicio, sla,estado, responsable, compa?ia, ci y cliente del caso
		SELECT inci_service_sla_id,
			inci_service_id,
			inci_status_id,
			inci_responsible_group_id,
			inci_responsible_id,
			inci_customer_id,
			inci_associated_ci,
			inci_vendor_id,
			inci_reason_id,
			inci_fl_int_project_id,
			inci_final_status
		INTO v_slaservice,
			v_service,
			v_status_id,
			v_responsible_group_id,
			v_responsible_id,
			v_customer_id,
			v_ci_id, v_vendor_id,
			v_reason_id,
			v_project_id,
			v_final_status
		FROM ASDK_INCIDENT
		WHERE inci_id = i_codcaso;

		IF (v_value8 IS NOT NULL) THEN
			IF ((v_value8 = v_service) AND (v_value9 <> v_slaservice)) THEN
				v_value8 := v_service;

			END IF;

		ELSE
			v_value8 := v_service;
			v_value9 := v_slaservice;

		END IF;

		--Estado
		IF v_value IS NULL THEN
			v_value := v_status_id;

		END IF;

		--Razon
		IF v_value7 IS NULL THEN
			v_value7 := v_reason_id;

		END IF;

		--Grupo
		IF v_value5 IS NULL THEN
			v_value5 := v_responsible_group_id;

		END IF;

		--especialista
		IF v_value6 IS NULL THEN
			v_value6 := v_responsible_id;

			--VERIFICAR SI EN EL GRUPO ESTA EL MISMO RESPONSABLE
			SELECT COUNT (1)
			INTO v_count
			FROM GROUP_USERS
			WHERE grp_id = v_value5
			AND codusuario = v_value6;

			IF v_count = 0 THEN
				v_value6 := FUN_ASDK_GET_RESPONSABLE(v_value5, v_project_id, v_row_type);

			END IF;

		END IF;

		--Asignar especialista si existe un periodo activo de inactividad
		v_value6 := FUN_AFW_ROUT_RESPONSIBLE(v_value5, v_value6, 1, v_project_id);

		-- Verifica si el estado es cerrado
		v_state_is_closed := NVL (FUN_ASDK_STATUS_CHILD(v_value), 0);

		--Verifica que no haya tareas pendientes
		v_tasks_pending := NVL (FUN_ASDK_ACTIVE_TASK (v_codcaso, 1), 0);
		v_value := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value END;
		v_value7 := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value7 END;

		BEGIN

			IF v_final_status = 1 THEN

				PRC_ASDK_INCIDENT (
					i_operation							=> 'U',
					io_inci_id							=> v_codcaso,
					i_inci_user_id_modifier			=> 1,
					i_inci_status_id					=> v_value,
					i_inci_priority_id				=> v_value2,
					i_inci_impact_id					=> v_value3,
					i_inci_urgency_id					=> v_value4,
					i_inci_responsible_group_id	=> v_value5,
					i_inci_responsible_id			=> v_value6,
					i_inci_reason_id					=> v_value7,
					i_inci_service_id					=> v_value8,
					i_inci_service_sla_id			=> v_value9,
					i_inci_customer_id				=> v_customer_id,
					i_inci_associated_ci				=> v_ci_id,
					i_inci_vendor_id					=> v_vendor_id,
					o_page_size							=> v_page_size,
					o_err_cod							=> v_codigo,
					o_err_msg							=> v_mensaje,
					cur_out								=> v_data_set
				);

				IF v_state_is_closed = 0 AND v_tasks_pending > 0 THEN

					SELECT CAST(SetValue AS INT)
					INTO v_lang
					FROM SETINGENERAL
					WHERE SetId = 2113;

					SELECT msg_text
					INTO v_comment_failed
					FROM ASC_MESSAGES
					WHERE msg_id = 299
					AND msg_lan_id = v_lang;

					v_comment_failed := REPLACE (v_comment_failed, '%d', TO_CHAR (v_rule_id));
					v_comment_failed := REPLACE (v_comment_failed, '%s', '{1}<br>{2}');

					FOR cur IN (
						SELECT
							'{' || TO_CHAR (ROW_NUMBER () OVER (ORDER BY field_name)) || '}' pattern,
							field_name
						FROM ASDK_RULES_FIELDS_TRANSLATOR
						WHERE field_id IN (1, 1735)
						AND language = CASE v_lang WHEN 1 THEN 'ESP' WHEN 2 THEN 'ING' WHEN 3 THEN 'POR' END
					) LOOP

						v_comment_failed := REPLACE (v_comment_failed, cur.pattern, cur.field_name);

					END LOOP;

					BEGIN

						SELECT hino_id,
							v_comment_failed || '<br><br>' || REPLACE(msg_text, '%s', NVL(times, 1) + 1)
						INTO v_comment_id,
							v_comment_failed
						FROM (
							SELECT hino_id,
								SUBSTR (
									times_text,
									INSTR(times_text, '(') + 1,
									ABS(INSTR(times_text, ')') - INSTR(times_text, '(') - 1)
								) times,
								msg_text
							FROM (
								SELECT hino_id,
									SUBSTR (
									hino_description,
										CASE WHEN REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) = 0
											THEN NULL ELSE REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) END,
										LENGTH(msg_text)
									) times_text,
									msg_text
								FROM ASDK_HIST_NOTES
								JOIN ASC_MESSAGES ON msg_id = 300
								AND msg_lan_id = v_lang
								WHERE hino_item_id = i_codcaso
								AND hino_description LIKE v_comment_failed || '%'
							) note
						) times;

					EXCEPTION WHEN NO_DATA_FOUND THEN
						NULL;

					END;

					IF v_comment_id IS NULL THEN

						-- Escribe en el historico que fallo
						PRC_ASDK_HIST_NOTES (
							i_operation						=> 'I',
							i_option							=> NULL,
							i_item_id						=> v_codcaso,
							i_hino_row_type				=> 1,
							i_hino_author					=> 1,
							i_hino_description			=> v_comment_failed,
							i_hino_description_nohtml	=> v_comment_failed,
							i_hino_visible					=> '0',
							i_language						=> NULL,
							cur_out							=> v_data_set
						);

					ELSE

						UPDATE ASDK_HIST_NOTES
						SET hino_description = v_comment_failed,
						hino_created = SYSDATE
						WHERE hino_id = v_comment_id;

					END IF;

					DELETE FROM ASDK_RULES_EXECUTED
					WHERE idcaso = v_codcaso
					AND idregla = (
						SELECT rule_id
						FROM ASDK_RULES_ACTIONS
						WHERE action_id = i_action_id
					);

				END IF;

			END IF;

		EXCEPTION WHEN OTHERS THEN
			RAISE ex_error_executing;

		END;

	END IF;

	IF v_row_type = 2 THEN

		-- Captura el servicio, sla,estado, responsable y ci del caso
		SELECT prob_service_id,
			prob_service_sla_id,
			prob_status_id,
			prob_responsible_group_id,
			prob_responsible_id,
			prob_associated_ci,
			prob_reason_id,
			prob_fl_int_project_id,
			prob_final_status
		INTO v_service,
			v_slaservice,
			v_status_id,
			v_responsible_group_id,
			v_responsible_id,
			v_ci_id,
			v_reason_id,
			v_project_id,
			v_final_status
		FROM ASDK_PROBLEM
		WHERE prob_id = i_codcaso;

		IF (v_value8 IS NOT NULL) THEN
			IF ((v_value8 = v_service) AND (v_value9 <> v_slaservice)) THEN
				v_value8 := v_service;

			END IF;

		ELSE
			v_value8 := v_service;
			v_value9 := v_slaservice;

		END IF;

		--Estado
		IF v_value IS NULL THEN
			v_value := v_status_id;

		END IF;

		--Razon
		IF v_value7 IS NULL THEN
			v_value7 := v_reason_id;

		END IF;

		--Grupo
		IF v_value5 IS NULL THEN
			v_value5 := v_responsible_group_id;

		END IF;

		--especialista
		IF v_value6 IS NULL THEN

			v_value6 := v_responsible_id;

			--VERIFICAR SI EN EL GRUPO ESTA EL MISMO RESPONSABLE
			SELECT COUNT (1)
			INTO v_count
			FROM GROUP_USERS
			WHERE grp_id = v_value5
			AND codusuario = v_value6;

			IF (v_count = 0) THEN
				v_value6 := FUN_ASDK_GET_RESPONSABLE(v_value5, v_project_id, v_row_type);

			END IF;

		END IF;

		--Asignar especialista si existe un periodo activo de inactividad
		v_value6 := FUN_AFW_ROUT_RESPONSIBLE (v_value5, v_value6, 2, v_project_id);

		-- Verifica si el estado es cerrado
		v_state_is_closed := NVL (FUN_ASDK_STATUS_CHILD (v_value), 0);

		--Verifica que no haya tareas pendientes
		v_tasks_pending := NVL (FUN_ASDK_ACTIVE_TASK (v_codcaso, 2), 0);
		v_value := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value END;
		v_value7 := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value7 END;

		BEGIN

			IF v_final_status = 1 THEN

				PRC_ASDK_PROBLEM (
					i_operation							=> 'U',
					io_prob_id							=> v_codcaso,
					i_prob_user_id_modifier			=> 1,
					i_prob_status_id					=> v_value,
					i_prob_priority_id				=> v_value2,
					i_prob_impact_id					=> v_value3,
					i_prob_urgency_id					=> v_value4,
					i_prob_responsible_group_id	=> v_value5,
					i_prob_responsible_id			=> v_value6,
					i_prob_reason_id					=> v_value7,
					i_prob_service_id					=> v_value8,
					i_prob_service_sla_id			=> v_value9,
					i_prob_associated_ci				=> v_ci_id,
					o_page_size							=> v_page_size,
					o_err_cod							=> v_codigo,
					o_err_msg							=> v_mensaje,
					cur_out								=> v_data_set
				);

				IF v_state_is_closed = 0 AND v_tasks_pending > 0 THEN

					SELECT CAST(SetValue AS INT)
					INTO v_lang
					FROM SETINGENERAL
					WHERE SetId = 2113;

					SELECT msg_text
					INTO v_comment_failed
					FROM ASC_MESSAGES
					WHERE msg_id = 299
					AND msg_lan_id = v_lang;

					v_comment_failed := REPLACE (v_comment_failed, '%d', TO_CHAR (v_rule_id));
					v_comment_failed := REPLACE (v_comment_failed, '%s', '{1}<br>{2}');

					FOR cur IN (
						SELECT
							'{' || TO_CHAR (ROW_NUMBER () OVER (ORDER BY field_name)) || '}' pattern,
							field_name
						FROM ASDK_RULES_FIELDS_TRANSLATOR
						WHERE field_id IN (1, 1735)
						AND language = CASE v_lang WHEN 1 THEN 'ESP' WHEN 2 THEN 'ING' WHEN 3 THEN 'POR' END
					) LOOP

						v_comment_failed := REPLACE (v_comment_failed, cur.pattern, cur.field_name);

					END LOOP;

					BEGIN

						SELECT hino_id,
							v_comment_failed || '<br><br>' || REPLACE(msg_text, '%s', NVL(times, 1) + 1)
						INTO v_comment_id,
							v_comment_failed
						FROM (
							SELECT hino_id,
								SUBSTR (
									times_text,
									INSTR(times_text, '(') + 1,
									ABS(INSTR(times_text, ')') - INSTR(times_text, '(') - 1)
								) times,
								msg_text
							FROM (
								SELECT hino_id,
									SUBSTR (
										hino_description,
										CASE WHEN REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) = 0
											THEN NULL ELSE REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) END,
										LENGTH(msg_text)
									) times_text,
									msg_text
								FROM ASDK_HIST_NOTES
								JOIN ASC_MESSAGES ON msg_id = 300
								AND msg_lan_id = v_lang
								WHERE hino_item_id = i_codcaso
								AND hino_description LIKE v_comment_failed || '%'
							) note
						) times;

					EXCEPTION WHEN NO_DATA_FOUND THEN
						NULL;

					END;

					IF v_comment_id IS NULL THEN

						-- Escribe en el historico que fallo
						PRC_ASDK_HIST_NOTES (
							i_operation						=> 'I',
							i_option							=> NULL,
							i_item_id						=> v_codcaso,
							i_hino_row_type				=> 2,
							i_hino_author					=> 1,
							i_hino_description			=> v_comment_failed,
							i_hino_description_nohtml	=> v_comment_failed,
							i_hino_visible					=> '0',
							i_language						=> NULL,
							cur_out							=> v_data_set
						);

					ELSE

						UPDATE ASDK_HIST_NOTES
						SET hino_description = v_comment_failed,
						hino_created = SYSDATE
						WHERE hino_id = v_comment_id;

					END IF;

					DELETE FROM ASDK_RULES_EXECUTED
					WHERE idcaso = v_codcaso
					AND idregla = (
						SELECT rule_id
						FROM ASDK_RULES_ACTIONS
						WHERE action_id = i_action_id
					);

				END IF;

			END IF;

		EXCEPTION WHEN OTHERS THEN
			RAISE ex_error_executing;

		END;

	END IF;

	IF v_row_type = 3 THEN

		-- Captura el servicio, sla,estado, responsable, compa?ia, ci y cliente del caso
		SELECT chan_service_sla_id,
			chan_service_id,
			chan_status_id,
			chan_responsible_group_id,
			chan_responsible_id,
			chan_customer_id,
			chan_associated_ci,
			chan_vendor_id,
			chan_reason_id,
			chan_fl_int_project_id,
			chan_final_status
		INTO v_slaservice,
			v_service,
			v_status_id,
			v_responsible_group_id,
			v_responsible_id,
			v_customer_id,
			v_ci_id,
			v_vendor_id,
			v_reason_id,
			v_project_id, 
			v_final_status
		FROM ASDK_CHANGE
		WHERE chan_id = i_codcaso;

		IF (v_value8 IS NOT NULL) THEN
			IF ((v_value8 = v_service) AND (v_value9 <> v_slaservice)) THEN
				v_value8 := v_service;

			END IF;

		ELSE
			v_value8 := v_service;
			v_value9 := v_slaservice;

		END IF;

		--Estado
		IF v_value IS NULL THEN
			v_value := v_status_id;

		END IF;

		--Razon
		IF v_value7 IS NULL THEN
			v_value7 := v_reason_id;

		END IF;

		--Grupo
		IF v_value5 IS NULL THEN
			v_value5 := v_responsible_group_id;

		END IF;

		--especialista
		IF v_value6 IS NULL THEN

			v_value6 := v_responsible_id;

			--VERIFICAR SI EN EL GRUPO ESTA EL MISMO RESPONSABLE
			SELECT COUNT (1)
			INTO v_count
			FROM GROUP_USERS
			WHERE grp_id = v_value5
			AND codusuario = v_value6;

			IF (v_count = 0) THEN
				v_value6 := FUN_ASDK_GET_RESPONSABLE(v_value5, v_project_id, v_row_type);

			END IF;

		END IF;

		--Asignar especialista si existe un periodo activo de inactividad
		v_value6 := FUN_AFW_ROUT_RESPONSIBLE (v_value5, v_value6, 3, v_project_id);

		-- Verifica si el estado es cerrado
		v_state_is_closed := NVL (FUN_ASDK_STATUS_CHILD (v_value), 0);

		--Verifica que no haya tareas pendientes
		v_tasks_pending := NVL (FUN_ASDK_ACTIVE_TASK (v_codcaso, 3), 0);
		v_value := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value END;
		v_value7 := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value7 END;

		BEGIN

			IF v_final_status = 1 THEN

				PRC_ASDK_CHANGE (
					i_operation							=> 'U',
					io_chan_id							=> v_codcaso,
					i_chan_user_id_modifier			=> 1,
					i_chan_status_id					=> v_value,
					i_chan_priority_id				=> v_value2,
					i_chan_impact_id					=> v_value3,
					i_chan_urgency_id					=> v_value4,
					i_chan_responsible_group_id	=> v_value5,
					i_chan_responsible_id			=> v_value6,
					i_chan_reason_id					=> v_value7,
					i_chan_service_id					=> v_value8,
					i_chan_service_sla_id			=> v_value9,
					i_chan_customer_id				=> v_customer_id,
					i_chan_associated_ci				=> v_ci_id,
					i_chan_vendor_id					=> v_vendor_id,
					o_page_size							=> v_page_size,
					o_err_cod							=> v_codigo,
					o_err_msg							=> v_mensaje,
					cur_out								=> v_data_set
				);

				IF v_state_is_closed = 0 AND v_tasks_pending > 0 THEN

					SELECT CAST(SetValue AS INT)
					INTO v_lang
					FROM SETINGENERAL
					WHERE SetId = 2113;

					SELECT msg_text
					INTO v_comment_failed
					FROM ASC_MESSAGES
					WHERE msg_id = 299
					AND msg_lan_id = v_lang;

					v_comment_failed := REPLACE (v_comment_failed, '%d', TO_CHAR (v_rule_id));
					v_comment_failed := REPLACE (v_comment_failed, '%s', '{1}<br>{2}');

					FOR cur IN (
						SELECT
							'{' || TO_CHAR (ROW_NUMBER () OVER (ORDER BY field_name)) || '}' pattern,
							field_name
						FROM ASDK_RULES_FIELDS_TRANSLATOR
						WHERE field_id IN (1, 1735)
						AND language = CASE v_lang WHEN 1 THEN 'ESP' WHEN 2 THEN 'ING' WHEN 3 THEN 'POR' END
					) LOOP

						v_comment_failed := REPLACE (v_comment_failed, cur.pattern, cur.field_name);

					END LOOP;

					BEGIN

						SELECT hino_id,
							v_comment_failed || '<br><br>' || REPLACE(msg_text, '%s', NVL(times, 1) + 1)
						INTO v_comment_id,
							v_comment_failed
						FROM (
							SELECT hino_id,
								SUBSTR (
									times_text,
									INSTR(times_text, '(') + 1,
									ABS(INSTR(times_text, ')') - INSTR(times_text, '(') - 1)
								) times,
								msg_text
							FROM (
								SELECT hino_id,
									SUBSTR (
										hino_description,
										CASE WHEN REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) = 0
											THEN NULL ELSE REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) END,
										LENGTH(msg_text)
									) times_text,
									msg_text
								FROM ASDK_HIST_NOTES
								JOIN ASC_MESSAGES ON msg_id = 300
								AND msg_lan_id = v_lang
								WHERE hino_item_id = i_codcaso
								AND hino_description LIKE v_comment_failed || '%'
							) note
						) times;

					EXCEPTION WHEN NO_DATA_FOUND THEN
						NULL;

					END;

					IF v_comment_id IS NULL THEN

						-- Escribe en el historico que fallo
						PRC_ASDK_HIST_NOTES (
							i_operation						=> 'I',
							i_option							=> NULL,
							i_item_id						=> v_codcaso,
							i_hino_row_type				=> 3,
							i_hino_author					=> 1,
							i_hino_description			=> v_comment_failed,
							i_hino_description_nohtml	=> v_comment_failed,
							i_hino_visible					=> '0',
							i_language						=> NULL,
							cur_out							=> v_data_set
						);

					ELSE

						UPDATE ASDK_HIST_NOTES
						SET hino_description = v_comment_failed,
						hino_created = SYSDATE
						WHERE hino_id = v_comment_id;

					END IF;

					DELETE FROM ASDK_RULES_EXECUTED
					WHERE idcaso = v_codcaso
					AND idregla = (
						SELECT rule_id
						FROM ASDK_RULES_ACTIONS
						WHERE action_id = i_action_id
					);

				END IF;

			END IF;

		EXCEPTION WHEN OTHERS THEN
			RAISE ex_error_executing;

		END;

	END IF;

	IF v_row_type = 15 THEN

		-- Captura el servicio, sla,estado, responsable, compa?ia, ci y cliente del caso
		SELECT serv_service_sla_id,
			serv_service_id,
			serv_status_id,
			serv_responsible_group_id,
			serv_responsible_id,
			serv_customer_id,
			serv_associated_ci,
			serv_vendor_id,
			serv_reason_id,
			serv_fl_int_project_id,
			serv_final_status
		INTO v_slaservice,
			v_service,
			v_status_id,
			v_responsible_group_id,
			v_responsible_id,
			v_customer_id,
			v_ci_id,
			v_vendor_id,
			v_reason_id,
			v_project_id,
			v_final_status
		FROM ASDK_SERVICE_CALL
		WHERE serv_id = i_codcaso;

		IF (v_value8 IS NOT NULL) THEN
			IF ((v_value8 = v_service) AND (v_value9 <> v_slaservice)) THEN
				v_value8 := v_service;

			END IF;

		ELSE
			v_value8 := v_service;
			v_value9 := v_slaservice;

		END IF;

		--Estado
		IF v_value IS NULL THEN
			v_value := v_status_id;

		END IF;

		--Razon
		IF v_value7 IS NULL THEN
			v_value7 := v_reason_id;

		END IF;

		--Grupo
		IF v_value5 IS NULL THEN
			v_value5 := v_responsible_group_id;

		END IF;

		--especialista
		IF v_value6 IS NULL THEN

			v_value6 := v_responsible_id;

			--VERIFICAR SI EN EL GRUPO ESTA EL MISMO RESPONSABLE
			SELECT COUNT(1)
			INTO v_count
			FROM GROUP_USERS
			WHERE grp_id = v_value5
			AND codusuario = v_value6;

			IF (v_count = 0) THEN
				v_value6 := FUN_ASDK_GET_RESPONSABLE(v_value5, v_project_id, 4);

			END IF;

		END IF;

		--Asignar especialista si existe un periodo activo de inactividad
		v_value6 := FUN_AFW_ROUT_RESPONSIBLE(v_value5, v_value6, 4, v_project_id);

		-- Verifica si el estado es cerrado
		v_state_is_closed := NVL(FUN_ASDK_STATUS_CHILD(v_value), 0);

		--Verifica que no haya tareas pendientes
		v_tasks_pending := NVL (FUN_ASDK_ACTIVE_TASK (v_codcaso, 4), 0);
		v_value := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value END;
		v_value7 := CASE WHEN v_state_is_closed = 0 AND v_tasks_pending > 0 THEN NULL ELSE v_value7 END;

		BEGIN

			IF v_final_status = 1 THEN

				PRC_ASDK_SERVICE_CALL (
					i_operation							=> 'U',
					io_serv_id							=> v_codcaso,
					i_serv_user_id_modifier			=> 1,
					i_serv_status_id					=> v_value,
					i_serv_priority_id				=> v_value2,
					i_serv_impact_id					=> v_value3,
					i_serv_urgency_id					=> v_value4,
					i_serv_responsible_group_id	=> v_value5,
					i_serv_responsible_id			=> v_value6,
					i_serv_reason_id					=> v_value7,
					i_serv_service_id					=> v_value8,
					i_serv_service_sla_id			=> v_value9,
					i_serv_customer_id				=> v_customer_id,
					i_serv_associated_ci				=> v_ci_id,
					i_serv_vendor_id					=> v_vendor_id,
					o_page_size							=> v_page_size,
					o_err_cod							=> v_codigo,
					o_err_msg							=> v_mensaje,
					cur_out								=> v_data_set
				);

				IF v_state_is_closed = 0 AND v_tasks_pending > 0 THEN

					SELECT CAST(SetValue AS INT)
					INTO v_lang
					FROM SETINGENERAL
					WHERE SetId = 2113;

					SELECT msg_text
					INTO v_comment_failed
					FROM ASC_MESSAGES
					WHERE msg_id = 299
					AND msg_lan_id = v_lang;

					v_comment_failed := REPLACE (v_comment_failed, '%d', TO_CHAR (v_rule_id));
					v_comment_failed := REPLACE (v_comment_failed, '%s', '{1}<br>{2}');

					FOR cur IN (
						SELECT
							'{' || TO_CHAR (ROW_NUMBER () OVER (ORDER BY field_name)) || '}' pattern,
							field_name
						FROM ASDK_RULES_FIELDS_TRANSLATOR
						WHERE field_id IN (1, 1735)
						AND language = CASE v_lang WHEN 1 THEN 'ESP' WHEN 2 THEN 'ING' WHEN 3 THEN 'POR' END
					) LOOP

						v_comment_failed := REPLACE (v_comment_failed, cur.pattern, cur.field_name);

					END LOOP;

					BEGIN

						SELECT hino_id,
							v_comment_failed || '<br><br>' || REPLACE(msg_text, '%s', NVL(times, 1) + 1)
						INTO v_comment_id,
							v_comment_failed
						FROM (
							SELECT hino_id,
								SUBSTR (
									times_text,
									INSTR(times_text, '(') + 1,
									ABS(INSTR(times_text, ')') - INSTR(times_text, '(') - 1)
								) times,
								msg_text
							FROM (
								SELECT hino_id,
									SUBSTR (
										hino_description,
										CASE WHEN REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) = 0
											THEN NULL ELSE REGEXP_INSTR(hino_description, REPLACE(msg_text, '%s', '[(]+[0-9][)]')) END,
										LENGTH(msg_text)
									) times_text,
									msg_text
								FROM ASDK_HIST_NOTES
								JOIN ASC_MESSAGES ON msg_id = 300
								AND msg_lan_id = v_lang
								WHERE hino_item_id = i_codcaso
								AND hino_description LIKE v_comment_failed || '%'
							) note
						) times;

					EXCEPTION WHEN NO_DATA_FOUND THEN
						NULL;

					END;

					IF v_comment_id IS NULL THEN

						-- Escribe en el historico que fallo
						PRC_ASDK_HIST_NOTES (
							i_operation						=> 'I',
							i_option							=> NULL,
							i_item_id						=> v_codcaso,
							i_hino_row_type				=> 4,
							i_hino_author					=> 1,
							i_hino_description			=> v_comment_failed,
							i_hino_description_nohtml	=> v_comment_failed,
							i_hino_visible					=> '0',
							i_language						=> NULL,
							cur_out							=> v_data_set
						);

					ELSE

						UPDATE ASDK_HIST_NOTES
						SET hino_description = v_comment_failed,
							hino_created = SYSDATE
						WHERE hino_id = v_comment_id;

					END IF;

					DELETE FROM ASDK_RULES_EXECUTED
					WHERE idcaso = v_codcaso
					AND idregla = (
						SELECT rule_id
						FROM ASDK_RULES_ACTIONS
						WHERE action_id = i_action_id
					);

				END IF;
			END IF;

		EXCEPTION WHEN OTHERS THEN
			RAISE ex_error_executing;

		END;

	END IF;

	IF v_row_type = 22 THEN

		v_value := CASE v_value WHEN 0 THEN NULL ELSE v_value END;
		v_value5 := CASE v_value5 WHEN 0 THEN NULL ELSE v_value5 END;
		v_value6 := CASE v_value6 WHEN 0 THEN NULL ELSE v_value6 END;
		v_value7 := CASE v_value7 WHEN 0 THEN NULL ELSE v_value7 END;

		SELECT prar_project_id
		INTO v_project_id
		FROM ASS_ARTICLE
		JOIN ASS_PROJECT_ARTICLE ON ASS_ARTICLE.fl_int_solution_id = ASS_PROJECT_ARTICLE.prar_solution_id
		WHERE ASS_PROJECT_ARTICLE.prar_solution_id = v_codcaso;

		--Asignar especialista si existe un periodo activo de inactividad
		v_value3 := FUN_AFW_ROUT_RESPONSIBLE(v_value5, v_value6, 7, v_project_id);

		BEGIN

			PRC_ASS_ARTICLE (
				i_operation					=> 'U',
				i_option						=> 'A',
				io_solution_id				=> v_codcaso,
				i_solution_state			=> v_value,
				i_group_responsible_id	=> v_value5,
				i_responsible_id			=> v_value6,
				i_arti_reason_id			=> v_value7,
				i_arti_user_id_modifier	=> 1,
				o_err_cod					=> v_codigo,
				o_err_msg					=> v_mensaje,
				cur_out						=> v_data_set
			);

		END;

	END IF;

	IF v_row_type = 37 THEN

		v_value := CASE v_value WHEN 0 THEN NULL ELSE v_value END;
		v_value2 := CASE v_value2 WHEN 0 THEN NULL ELSE v_value2 END;
		v_value3 := CASE v_value3 WHEN 0 THEN NULL ELSE v_value3 END;
		v_profile_id := NULL;

		BEGIN

			PRC_CONFIGURATION_ITEM (
				i_operation			=> 'U',
				i_option				=> 'A',
				i_ci_id				=> v_codcaso,
				i_actual_status	=> v_value,
				i_impact				=> v_value2,
				i_incharge_id		=> v_value3,
				i_profile_id		=> v_profile_id,
				o_page_size			=> v_page_size,
				o_err_cod			=> v_codigo,
				o_err_msg			=> v_mensaje,
				o_cis_used			=> v_cis_used,
				cur_out				=> v_data_set
			);

		END;

	END IF;

	-- ELIMINA EL CASO DE LA TABLA MODIFY_ITEMS DE LAS REGLAS
	DELETE FROM ASDK_RULES_MODIFY_ITEMS
	WHERE fl_int_case_id = i_codcaso
	AND fl_int_action_id = i_action_id;

EXCEPTION WHEN ex_error_executing
	THEN RAISE;

WHEN OTHERS THEN
	v_codigo := SQLERRM;
	v_mensaje := SQLCODE;

END PRC_ASDK_MODIFICAR_ITEM;