create or replace PROCEDURE PRC_ASDK_MODIFICAR_REL (
   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_query                 VARCHAR2 (4000);
   v_query_filter          VARCHAR2 (4000);
   v_field                 VARCHAR2 (70);
   v_relacion              NUMBER (10, 0);
   v_id_item               NUMBER (10, 0);
   v_value                 INTEGER                        := NULL;
   v_value2                INTEGER                        := NULL;
   v_value3                INTEGER                        := NULL;
   v_value4                INTEGER                        := NULL;
   v_value5                INTEGER                        := NULL;
   v_value6                INTEGER                        := NULL;
   v_value7                INTEGER                        := NULL;
   v_cur_param_id          NUMBER (10, 0);
   v_cur_value_param       VARCHAR2 (8000);
   v_record_type           NUMBER (10, 0);
   v_item_id               NUMBER (10, 0);
   v_table                 VARCHAR2 (70);
   v_key                   VARCHAR2 (70);
   v_table2                VARCHAR2 (70);
   v_key2                  VARCHAR2 (70);
   v_valueb                VARCHAR2 (500);
   v_codigo                VARCHAR2 (8000);
   v_mensaje               VARCHAR2 (8000);
   v_profile_id            NUMBER;
   v_count_case_open       NUMBER;
   v_codcaso               NUMBER;
   v_child_direction       NUMBER;
   v_field_direction       VARCHAR (70);
   v_state_is_closed		   NUMBER;
   v_tasks_pending			NUMBER;
   v_comment_failed		   VARCHAR2(255);
   v_lang					   NUMBER;
   v_rule_id				   NUMBER;
   cur_result              pk_asc_objects.asc_return_data;
   cur_result_filter       pk_asc_objects.asc_return_data;

   TYPE type_cur IS REF CURSOR;

   cur_related             type_cur;
   v_data_set              pk_asc_objects.asc_return_data;
   v_commentary            CLOB;
   v_value9                INTEGER                        := NULL;
   v_comment_case          CLOB;
   v_comment_case_nohtml   CLOB;
   v_page_size             NUMBER;
   v_project_id            NUMBER;
   v_count                 NUMBER;
   v_cis_used              NUMBER;
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_record_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;

   FOR ind_cur_param_values IN cur_param_values
   LOOP
      v_cur_param_id := ind_cur_param_values.param_id;
      v_cur_value_param := ind_cur_param_values.param_value;

      IF v_cur_param_id = 1
      THEN                                              -- Tabla a Modificar
         v_relacion := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 2
      THEN                                              -- Codigo de estado
         v_value := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 3
      THEN                                              -- Codigo de Prioridad
         v_value2 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 4
      THEN                                              -- Codigo de Impacto
         v_value3 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 5
      THEN                                              -- Codigo de Urgencia
         v_value4 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 6
      THEN                                              -- Codigo de Grupo de especialistas
         v_value5 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 7
      THEN                                              -- Codigo de Usuario responsable
         v_value6 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 8
      THEN                                              -- Codigo de Razon
         v_value7 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      ELSIF v_cur_param_id = 9
      THEN                                              -- Utiliza Comentario Caso
         v_value9 := TO_NUMBER (TRIM (ind_cur_param_values.param_value));
      END IF;
   END LOOP;

   BEGIN
      SELECT source_table, source_key, source_value, child_direction,
             field_direction
        INTO v_table2, v_key2, v_valueb, v_child_direction,
             v_field_direction
        FROM asdk_rules_tables_relations
       WHERE table_id = v_record_type AND table2_id = v_relacion;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;

   --Averigua el comentario del caso dado el tipo
   IF v_value9 IS NOT NULL
   THEN
      IF (v_value9 = 1)
      THEN
    /*==============================*/
    /*          INCIDENTES          */
    /*==============================*/
         IF (v_record_type = 1)
         THEN
            BEGIN
               SELECT inci_commentary, inci_id_by_project
                 INTO v_commentary, v_codcaso
                 FROM asdk_incident
                WHERE inci_id = i_codcaso;

               v_commentary :=
                     CHR (13)
                  || ' INCIDENT No '
                  || TO_CHAR (v_codcaso)
                  || '  '
                  || NVL (v_commentary, '');
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
            END;
         END IF;

    /*==============================*/
    /*          PROBLEMAS           */
    /*==============================*/
         IF (v_record_type = 2)
         THEN
            BEGIN
               SELECT prob_commentary, prob_id_by_project
                 INTO v_commentary, v_codcaso
                 FROM asdk_problem
                WHERE prob_id = i_codcaso;

               v_commentary :=
                     CHR (13)
                  || ' PROBLEM No '
                  || TO_CHAR (v_codcaso)
                  || '  '
                  || NVL (v_commentary, '');
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
            END;
         END IF;

    /*==============================*/
    /*          CAMBIOS             */
    /*==============================*/
         IF (v_record_type = 3)
         THEN
            BEGIN
               SELECT chan_commentary, chan_id_by_project
                 INTO v_commentary, v_codcaso
                 FROM asdk_change
                WHERE chan_id = i_codcaso;

               v_commentary :=
                     CHR (13)
                  || ' CHANGE No '
                  || TO_CHAR (v_codcaso)
                  || '  '
                  || NVL (v_commentary, '');
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
            END;
         END IF;

    /*==============================*/
    /* LLAMADA DE SERVICIO          */
    /*==============================*/
         IF (v_record_type = 15)
         THEN
            BEGIN
               SELECT serv_commentary, serv_id_by_project
                 INTO v_commentary, v_codcaso
                 FROM asdk_service_call
                WHERE serv_id = i_codcaso;

               v_commentary :=
                     CHR (13)
                  || ' SERVICE CALL No '
                  || TO_CHAR (v_codcaso)
                  || '  '
                  || NVL (v_commentary, '');
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
            END;
         END IF;
      END IF;
   END IF;

   v_query :=
         'SELECT '
      || v_valueb
      || ' FROM '
      || v_table2
      || ' WHERE '
      || v_key2
      || ' = '
      || i_codcaso;

   IF (v_child_direction IS NOT NULL)
   THEN
      v_query :=
         v_query || ' AND ' || v_field_direction || ' = '
         || v_child_direction;
   END IF;

   v_query_filter :=
         'SELECT '
      || v_valueb
      || ' FROM '
      || v_table2
      || ' JOIN ASDK_CONCURRENCY B ON '
      || v_table2
      || '.'
      || v_valueb
      || ' = B.FL_STR_PRIMARY_KEY '
      || ' WHERE '
      || v_key2
      || ' = '
      || i_codcaso;

   --VERIFICA QUE EL CASO RELACIONADO NO SE ENCUENTRE ABIERTO
   BEGIN
      OPEN cur_result_filter FOR v_query_filter;

      IF cur_result_filter%ROWCOUNT = 0
      THEN
         BEGIN
            OPEN cur_result FOR v_query;

            LOOP
               FETCH cur_result
                INTO v_id_item;

               EXIT WHEN cur_result%NOTFOUND;

               IF v_relacion = 1
               THEN
                  BEGIN
                     SELECT NVL (inci_commentary, ''), inci_fl_int_project_id
                       INTO v_comment_case, v_project_id
                       FROM asdk_incident
                      WHERE inci_id = v_id_item;

                     v_comment_case := v_comment_case || ' ' || v_commentary;

                     --Validar informacion de especialista
                     IF v_value6 IS NULL
                     THEN
                        BEGIN
                           --Verifica el codigo de grupo
                           IF v_value5 IS NOT NULL
                           THEN
                              BEGIN
                                 --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_relacion
                                                               );
                                 END IF;
                              END;
                           END IF;
                        END;
                     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
                                                 );
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

                  BEGIN
                     v_comment_case_nohtml := remove_html (v_comment_case);
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

						-- 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_id_item, 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;

						PRC_ASDK_INCIDENT (
							i_operation                      => 'U',
							io_inci_id                       => v_id_item,
							i_inci_user_id_modifier          => 1,
							i_inci_status_id                 => v_value,
							i_inci_reason_id                 => v_value7,
							i_inci_priority_id               => v_value2,
							i_inci_impact_id                 => v_value3,
							i_inci_urgency_id                => v_value4,
							i_inci_responsible_id            => v_value6,
							i_inci_responsible_group_id      => v_value5,
							i_inci_commentary                => v_comment_case,
							i_inci_commentary_nohtml         => v_comment_case_nohtml,
							o_page_size                      => v_page_size,
							cur_out                          => v_data_set,
							o_err_cod                        => v_codigo,
							o_err_msg                        => v_mensaje
						);

						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;

							  -- Escribe en el historico que fall¿
							PRC_ASDK_HIST_NOTES (
								i_operation => 'I',
								i_option => NULL,
								i_item_id => v_id_item,
								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
							);

                  END IF;

               END IF;

               IF v_relacion = 2
               THEN
                  BEGIN
                     SELECT NVL (prob_commentary, ''), prob_fl_int_project_id
                       INTO v_comment_case, v_project_id
                       FROM asdk_problem
                      WHERE prob_id = v_id_item;

                     v_comment_case := v_comment_case || ' ' || v_commentary;

                     --Validar informacion de especialista
                     IF v_value6 IS NULL
                     THEN
                        BEGIN
                           --Verifica el codigo de grupo
                           IF v_value5 IS NOT NULL
                           THEN
                              BEGIN
                                 --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_relacion
                                                               );
                                 END IF;
                              END;
                           END IF;
                        END;
                     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
                                                 );
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

                  BEGIN
                     v_comment_case_nohtml := remove_html (v_comment_case);
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

				  		-- 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_id_item, 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;

						PRC_ASDK_PROBLEM (
							i_operation                      => 'U',
							io_prob_id                       => v_id_item,
							i_prob_user_id_modifier          => 1,
							i_prob_status_id                 => v_value,
							i_prob_reason_id                 => v_value7,
							i_prob_priority_id               => v_value2,
							i_prob_impact_id                 => v_value3,
							i_prob_urgency_id                => v_value4,
							i_prob_responsible_id            => v_value6,
							i_prob_responsible_group_id      => v_value5,
							i_prob_commentary                => v_comment_case,
							i_prob_commentary_nohtml         => v_comment_case_nohtml,
							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;

							  -- Escribe en el historico que fall¿
							PRC_ASDK_HIST_NOTES (
								i_operation => 'I',
								i_option => NULL,
								i_item_id => v_id_item,
								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
							);

						END IF;

               END IF;

               IF v_relacion = 3
               THEN
                  BEGIN
                     SELECT NVL (chan_commentary, ''), chan_fl_int_project_id
                       INTO v_comment_case, v_project_id
                       FROM asdk_change
                      WHERE chan_id = v_id_item;

                     v_comment_case := v_comment_case || ' ' || v_commentary;

                     --Validar informacion de especialista
                     IF v_value6 IS NULL
                     THEN
                        BEGIN
                           --Verifica el codigo de grupo
                           IF v_value5 IS NOT NULL
                           THEN
                              BEGIN
                                 --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_relacion
                                                               );
                                 END IF;
                              END;
                           END IF;
                        END;
                     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
                                                 );
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

                  BEGIN
                     v_comment_case_nohtml := remove_html (v_comment_case);
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

						-- 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_id_item, 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;

						PRC_ASDK_CHANGE (
							i_operation                      => 'U',
							io_chan_id                       => v_id_item,
							i_chan_user_id_modifier          => 1,
							i_chan_status_id                 => v_value,
							i_chan_reason_id                 => v_value7,
							i_chan_priority_id               => v_value2,
							i_chan_impact_id                 => v_value3,
							i_chan_urgency_id                => v_value4,
							i_chan_responsible_id            => v_value6,
							i_chan_responsible_group_id      => v_value5,
							i_chan_commentary                => v_comment_case,
							i_chan_commentary_nohtml         => v_comment_case_nohtml,
							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;

							-- Escribe en el historico que fall¿
							PRC_ASDK_HIST_NOTES (
								i_operation => 'I',
								i_option => NULL,
								i_item_id => v_id_item,
								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
							);

                  END IF;

               END IF;

               IF v_relacion = 15
               THEN
                  BEGIN
                     SELECT NVL (serv_commentary, ''), serv_fl_int_project_id
                       INTO v_comment_case, v_project_id
                       FROM asdk_service_call
                      WHERE serv_id = v_id_item;

                     v_comment_case := v_comment_case || ' ' || v_commentary;

                     --Validar informacion de especialista
                     IF v_value6 IS NULL
                     THEN
                        BEGIN
                           --Verifica el codigo de grupo
                           IF v_value5 IS NOT NULL
                           THEN
                              BEGIN
                                 --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;
                           END IF;
                        END;
                     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
                                                 );
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;

                  BEGIN
                     v_comment_case_nohtml := remove_html (v_comment_case);
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;
                  
                  -- 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_id_item, 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;

                  PRC_ASDK_SERVICE_CALL (
							i_operation                      => 'U',
                     io_serv_id                       => v_id_item,
                     i_serv_user_id_modifier          => 1,
                     i_serv_status_id                 => v_value,
                     i_serv_reason_id                 => v_value7,
                     i_serv_priority_id               => v_value2,
                     i_serv_impact_id                 => v_value3,
                     i_serv_urgency_id                => v_value4,
                     i_serv_responsible_id            => v_value6,
                     i_serv_responsible_group_id      => v_value5,
                     i_serv_commentary                => v_comment_case,
                     i_serv_commentary_nohtml         => v_comment_case_nohtml,
                     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;

                    -- Escribe en el historico que fall¿
                    PRC_ASDK_HIST_NOTES
                      (i_operation => 'I',
                      i_option => NULL,
                      i_item_id => v_id_item,
                      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);
                  END IF;

               END IF;

               IF v_relacion = 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;
                  prc_configuration_item (i_operation          => 'U',
                                          i_option             => 'A',
                                          i_ci_id              => v_id_item,
                                          i_actual_status      => v_value,
                                          i_impact             => v_value2,
                                          i_incharge_id        => v_value3,
                                          i_profile_id         => v_profile_id,
                                          i_flag_history_ci    => 1,
                                          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 IF;
            END LOOP;

            CLOSE cur_result;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;

         --ELIMINA EL CASO DE LA TABLA DE MODIFICAR RELACIONADOS
         DELETE FROM asdk_rules_modify_items
               WHERE fl_int_case_id = i_codcaso
                 AND fl_int_action_id = i_action_id;
      END IF;
   END;
END prc_asdk_modificar_rel;