CREATE OR REPLACE PROCEDURE SAARANDA8.PRC_ASDK_CREATE_CASE (
	i_codcaso                IN           NUMBER,
	i_action_id              IN           NUMBER,
	i_timestamp              IN           TIMESTAMP DEFAULT NULL
) AS
   v_query                 VARCHAR2(4000);
   v_relacion              NUMBER;
   v_id_item               NUMBER;
   v_table_id              NUMBER;
   v_value                 VARCHAR2(4000);
   v_value2                VARCHAR2(4000);
   v_value3                VARCHAR2(4000);
   v_value4                VARCHAR2(4000);
   v_value5                LONG;
   v_value6                VARCHAR2(4000);
   v_cur_param_id          NUMBER;
   v_cur_value_param       VARCHAR2(8000);
   v_record_type           NUMBER;
   v_table2                VARCHAR2(70);
   v_key2                  VARCHAR2(70);
   v_valueb                VARCHAR2(500);
   v_codigo                VARCHAR2(8000);
   v_mensaje               VARCHAR2(8000);
   v_cur_result            PK_ASC_OBJECTS.asc_return_data;
   v_page_size             NUMBER;
   v_case_type_related		NUMBER;

   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;

BEGIN
   BEGIN    
      SELECT asdk_rules_tables_1.table_id,table_parent
      INTO   v_record_type,v_case_type_related      
      FROM   asdk_rules_actions_types
      JOIN   asdk_rules_actions_types_table ON (asdk_rules_actions_types.action_type = asdk_rules_actions_types_table.action_type)
      JOIN   asdk_rules_tables asdk_rules_tables_1 ON (asdk_rules_actions_types_table.table_id = asdk_rules_tables_1.table_id)
      JOIN   asdk_rules_actions ON (asdk_rules_actions_types.action_type = asdk_rules_actions.action_type)
      JOIN   asdk_rules ON (asdk_rules_actions.rule_id = asdk_rules.rule_id AND asdk_rules_actions_types_table.table_parent = asdk_rules.table_id)
      WHERE  (asdk_rules_actions.action_id = i_action_id) AND ROWNUM = 1;
      
      EXCEPTION WHEN NO_DATA_FOUND THEN
      NULL;
   END;

   FOR i IN cur_param_values LOOP   
      IF i.param_id = 1 THEN
         v_relacion := TO_NUMBER(TRIM(i.param_value));
      END IF;
      
      IF i.param_id = 2 THEN
         v_value := i.param_value;
      END IF;
      
      IF i.param_id = 3 THEN
         v_value2 := i.param_value;
      END IF;
      
      IF i.param_id = 4 THEN
         v_value3 := i.param_value;
      END IF;
      
      IF i.param_id = 5 THEN
         v_value4 := i.param_value;
      END IF;
      
      IF i.param_id = 6 THEN         
		 PRC_ASDK_TEXTO_CORREO(
         i_item_id     => i_codcaso,
         i_record_type => v_record_type,
         i_cur_cuerpo  => i.param_value,
         i_timestamp   => i_timestamp,
         o_salida      => v_value5);
      END IF;      

      IF i.param_id = 7 THEN
         v_value6 := i.param_value;
      END IF;
   END LOOP;

   IF v_relacion = 1 THEN
      PRC_ASDK_INCIDENT(
      i_operation               => 'I',
      io_inci_id                => v_id_item ,
      i_inci_author             => 1,
      i_inci_user_id_modifier   => 1,
      i_inci_fl_int_project_id  => TO_NUMBER(TRIM(v_value)),
      i_inci_category_id        => TO_NUMBER(TRIM(v_value2)),
      i_inci_service_id         => TO_NUMBER(TRIM(v_value3)),
      i_inci_urgency_id         => TO_NUMBER(TRIM(v_value4)),
      i_inci_description        => v_value5,
      i_inci_registry_type_id   => 1,
      i_inci_customer_id        => 1,
	  i_inci_associated_ci      => CASE WHEN v_case_type_related IN (37, 38) THEN i_codcaso END,
      o_page_size               => v_page_size,
      cur_out                   => v_cur_result,
      o_err_cod                 => v_codigo,
      o_err_msg                 => v_mensaje);
      
      IF v_case_type_related = 1 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'H',
         i_inci_parent_id => i_codcaso,
         i_inci_id => v_id_item,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         cur_out => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 2 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'B',
         i_inci_id => v_id_item,
         i_prob_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         i_direction => 1,
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 3 THEN
        PRC_ASDK_RELATION(
           i_operation => 'I',
           i_option => 'A',
           i_inci_id => v_id_item,
           i_chan_id => i_codcaso,
           i_user_id_modifier => 1,
           i_relation_cause_id => 3,	
           i_direction => 1,	
           cur_out  => v_cur_result,
           o_err_cod => v_codigo,
           o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 15 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'O',
         i_inci_id => v_id_item,
         i_serv_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,
         i_direction => 1,		
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
		ELSIF v_case_type_related IN(37, 38) THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'I',
         i_ci_id => i_codcaso,
         i_inci_id => v_id_item,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,	
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      END IF;
      
   END IF;   
      
   IF v_relacion = 2 THEN
      PRC_ASDK_PROBLEM(
      i_operation               => 'I',
      io_prob_id                => v_id_item ,
      i_prob_author             => 1,
      i_prob_fl_int_project_id  => TO_NUMBER(TRIM(v_value)),
      i_prob_category_id        => TO_NUMBER(TRIM(v_value2)),
      i_prob_service_id         => TO_NUMBER(TRIM(v_value3)),
      i_prob_urgency_id         => TO_NUMBER(TRIM(v_value4)),
      i_prob_description        => v_value5,
      i_prob_registry_type_id   => 1,
      i_prob_associated_ci      => CASE WHEN v_case_type_related IN (37, 38) THEN i_codcaso END,
      o_page_size               => v_page_size,
      cur_out                   => v_cur_result,
      o_err_cod                 => v_codigo,
      o_err_msg                 => v_mensaje);
      
      IF v_case_type_related = 1 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'B',
         i_prob_id => v_id_item,
         i_inci_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,	
         i_direction => 0,
         cur_out => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 2 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'F',
         i_prob_parent_id => v_id_item,
         i_prob_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 3 THEN
        PRC_ASDK_RELATION(
           i_operation => 'I',
           i_option => 'C',
           i_prob_id => v_id_item,
           i_chan_id => i_codcaso,
           i_user_id_modifier => 1,
           i_relation_cause_id => 3,	
           i_direction => 1,	
           cur_out  => v_cur_result,
           o_err_cod => v_codigo,
           o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 15 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'N',
         i_prob_id => v_id_item,
         i_serv_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,
         i_direction => 0,		
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
		ELSIF v_case_type_related IN(37, 38) THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'D',
         i_ci_id => i_codcaso,
         i_prob_id => v_id_item,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,	
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      END IF;
   END IF;

   IF v_relacion = 3 THEN
      PRC_ASDK_CHANGE(
      i_operation               => 'I',
      io_chan_id                => v_id_item ,
      i_chan_author             => 1,
      i_chan_fl_int_project_id  => TO_NUMBER(TRIM(v_value)),
      i_chan_category_id        => TO_NUMBER(TRIM(v_value2)),
      i_chan_service_id         => TO_NUMBER(TRIM(v_value3)),
      i_chan_urgency_id         => TO_NUMBER(TRIM(v_value4)),
      i_chan_description        => v_value5,
      i_chan_customer_id        => 1,
      i_chan_registry_type_id   => 1,
      i_chan_associated_ci      => CASE WHEN v_case_type_related IN (37, 38) THEN i_codcaso END,
      o_page_size               => v_page_size,
      cur_out                   => v_cur_result,
      o_err_cod                 => v_codigo,
      o_err_msg                 => v_mensaje);
      
      IF v_case_type_related = 1 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'A',
         i_chan_id => v_id_item,
         i_inci_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         i_direction => 0,
         cur_out => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 2 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'C',
         i_chan_id => v_id_item,
         i_prob_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         i_direction => 0,
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 3 THEN
        PRC_ASDK_RELATION(
           i_operation => 'I',
           i_option => 'G',
           i_chan_parent_id => i_codcaso,
           i_chan_id => v_id_item,
           i_user_id_modifier => 1,
           i_relation_cause_id => 3,	
           cur_out  => v_cur_result,
           o_err_cod => v_codigo,
           o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 15 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'L',
         i_chan_id => v_id_item,
         i_serv_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,
         i_direction => 0,		
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
		ELSIF v_case_type_related IN(37, 38) THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'E',
         i_ci_id => i_codcaso,
         i_chan_id => v_id_item,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,	
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      END IF;
   END IF;      

   IF v_relacion = 15 THEN
      PRC_ASDK_SERVICE_CALL(
      i_operation               => 'I',
      io_serv_id                => v_id_item ,
      i_serv_author             => 1,
      i_serv_fl_int_project_id  => TO_NUMBER(TRIM(v_value)),
      i_serv_category_id        => TO_NUMBER(TRIM(v_value2)),
      i_serv_service_id         => TO_NUMBER(TRIM(v_value3)),
      i_serv_urgency_id         => TO_NUMBER(TRIM(v_value4)),
      i_serv_description        => v_value5,
      i_serv_registry_type_id   => 1,
      i_serv_customer_id        => 1,
      i_serv_associated_ci      => CASE WHEN v_case_type_related IN (37, 38) THEN i_codcaso END,
      o_page_size               => v_page_size,
      cur_out                   => v_cur_result,
      o_err_cod                 => v_codigo,
      o_err_msg                 => v_mensaje);
      
      IF v_case_type_related = 1 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'O',
         i_serv_id => v_id_item,
         i_inci_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         i_direction => 0,
         cur_out => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 2 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'N',
         i_serv_id => v_id_item,
         i_prob_id => i_codcaso,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,		
         i_direction => 1,
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 3 THEN
        PRC_ASDK_RELATION(
           i_operation => 'I',
           i_option => 'L',
           i_serv_id => v_id_item,
           i_chan_id => i_codcaso,
           i_user_id_modifier => 1,
           i_relation_cause_id => 3,	
           i_direction => 1,	
           cur_out  => v_cur_result,
           o_err_cod => v_codigo,
           o_err_msg => v_mensaje);
      ELSIF v_case_type_related = 15 THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'M',
         i_serv_parent_id => i_codcaso,
         i_serv_id => v_id_item,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,	
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
		ELSIF v_case_type_related IN(37, 38) THEN
        PRC_ASDK_RELATION(
         i_operation => 'I',
         i_option => 'K',
         i_ci_id => i_codcaso,
         i_serv_id => v_id_item,
         i_user_id_modifier => 1,
         i_relation_cause_id => 3,	
         cur_out  => v_cur_result,
         o_err_cod => v_codigo,
         o_err_msg => v_mensaje);
      END IF;
   END IF;
   
   IF v_relacion = 22 THEN
      PRC_ASS_ARTICLE(
      i_operation            => 'I',
      i_option               => 'A',
      io_solution_id         => v_id_item,
      i_autor_id             => 1,
      i_project_id           => TO_NUMBER(TRIM(v_value)),           -- 2
      i_group_responsible_id => TO_NUMBER(TRIM(v_value2)),          -- 3
      i_solution_title       => TRIM(v_value3),                     -- 4
      i_solution_description => TRIM(v_value4),                     -- 5
      i_solution_content     => v_value5,                           -- 6
      i_responsible_id       => TO_NUMBER(TRIM(v_value6)),          -- 7
      i_class_id             => 0,
      i_arti_create_reason_id=> 1,
      i_solution_keyword     => TRIM(v_value3),
      cur_out                => v_cur_result,
      o_err_cod              => v_codigo,
      o_err_msg              => v_mensaje);
   END IF;
   
END PRC_ASDK_CREATE_CASE;
/