ENVX/oper
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
master
oper/DB/PACKAGE_BODIES/PKG_DEPLOY_COORDINATOR.sql
luquitas a269361962 Primer Commit
2023-06-08 11:55:59 -03:00

3574 lines
163 KiB
MySQL
Raw Permalink Blame History

--------------------------------------------------------
-- DDL for Package Body PKG_DEPLOY_COORDINATOR
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "ENVX_OPER"."PKG_DEPLOY_COORDINATOR" AS
/*-------------------------------------------------------------------------*/
/*---------------------------- FUNCTIONS ----------------------------------*/
/*-------------------------------------------------------------------------*/
/*--------------- FNC01-------------------*/
FUNCTION request_hgroup_name(
pi_id_request IN NUMBER,
pi_id_enviroment_type IN NUMBER,
pi_creation_date IN DATE
) RETURN VARCHAR2 AS
v_result VARCHAR2(100);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.request_hgroup_name';
BEGIN
v_result := lpad(pi_id_request, 5, 0)
|| '-'
--lpad(pi_id_enviroment_type, 000)
|| lpad(to_char(pi_creation_date, 'YYYYMMDD'), 8, 0);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'HGROUP: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
END;
/*--------------- FNC02-------------------*/
FUNCTION next_step_status(
pi_id_pattern IN NUMBER,
pi_id_pattern_step IN NUMBER,
pi_id_status IN NUMBER,
pi_result IN NUMBER
-- 0: exec_sequence / 1: id_pattern_step / 2: id_executor
) RETURN NUMBER AS
v_result NUMBER := 0;
v_actual_id_step NUMBER := 0;
v_category NUMBER := 0;
v_act_exec_cat NUMBER := 0;
v_act_executor NUMBER := 0;
v_actual_sequence NUMBER := 0;
---
v_new_id_step NUMBER := 0;
v_new_exec_cat NUMBER := 0;
v_new_executor NUMBER := 0;
v_new_sequence NUMBER := 0;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.next_step_status';
BEGIN
SELECT (
CASE pi_id_status
WHEN 0 THEN
-999
WHEN 1 THEN
nvl(ps.id_pstep_success, -999)
WHEN 2 THEN
nvl(ps.id_pstep_warning, -999)
WHEN (-1) THEN
nvl(ps.id_pstep_error, -999)
WHEN (-2) THEN
nvl(ps.id_pstep_abort, -999)
WHEN (-3) THEN
nvl(ps.id_pstep_timeout, -999)
ELSE
NULL
END
) AS resultado,
(
CASE pi_id_status
WHEN 0 THEN
0
WHEN 1 THEN
ps.id_act_cat_success
WHEN 2 THEN
ps.id_act_cat_warning
WHEN (-1) THEN
ps.id_act_cat_error
WHEN (-2) THEN
ps.id_act_cat_abort
WHEN (-3) THEN
ps.id_act_cat_timeout
ELSE
NULL
END
) AS category,
ps.id_exec_category,
ps.exec_sequence,
ps.id_executor
INTO v_actual_id_step,
v_category,
v_act_exec_cat,
v_actual_sequence,
v_act_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = pi_id_pattern_step
AND ps.id_pattern = pi_id_pattern;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_actual_id_step, v_category, v_act_exec_cat, v_actual_sequence, v_act_executor: '
|| v_actual_id_step
|| ', '
|| v_category
|| ', '
|| v_act_exec_cat
|| ', '
|| v_actual_sequence
|| ', '
|| v_act_executor);
------------------------------------------------------------------
IF v_actual_id_step = -999 THEN
CASE
WHEN v_category = 1 THEN
-- NEXT_STEP
---
v_new_sequence := v_actual_sequence + 1;
---
SELECT ps.id_pattern_step,
ps.id_exec_category,
ps.id_executor
INTO v_new_id_step,
v_new_exec_cat,
v_new_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern = pi_id_pattern
AND ps.exec_sequence = v_new_sequence;
WHEN v_category = 2 THEN
-- PREV_STEP
v_new_sequence := v_actual_sequence - 1;
---
SELECT ps.id_pattern_step,
ps.id_exec_category,
ps.id_executor
INTO v_new_id_step,
v_new_exec_cat,
v_new_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern = pi_id_pattern
AND ps.exec_sequence = v_new_sequence;
WHEN v_category = 3 THEN
-- GO_2_STEP
v_new_sequence := -999;
--- WTF -- DEBE TENER CONFIGURADO UN PASO OBLIGATORIAMENTE
v_new_id_step := -999;
WHEN v_category = 4 THEN
-- GO_2_BEGIN
v_new_sequence := 1;
---
SELECT ps.id_pattern_step,
ps.id_exec_category,
ps.id_executor
INTO v_new_id_step,
v_new_exec_cat,
v_new_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern = pi_id_pattern
AND ps.exec_sequence = v_new_sequence;
WHEN v_category = 5 THEN
-- PAUSE
v_new_id_step := v_actual_id_step;
v_new_sequence := v_actual_sequence;
v_new_executor := v_act_executor;
WHEN v_category = 6 THEN
-- ABORT
v_new_sequence := 0;
--- WTF -- FIN DE LA EJECUCION
v_new_id_step := 0;
v_new_executor := 0;
WHEN v_category = 7 THEN
-- FINISH
v_new_sequence := 0;
--- WTF -- FIN DE LA EJECUCION
v_new_id_step := 0;
v_new_executor := 0;
ELSE
-- WTF
NULL;
END CASE;
ELSE
v_new_id_step := v_actual_id_step;
v_new_sequence := v_actual_sequence;
v_new_executor := v_act_executor;
END IF;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_new_id_step, v_category, v_new_exec_cat, v_new_sequence, v_new_executor: '
|| v_new_id_step
|| ', '
|| v_category
|| ', '
|| v_new_exec_cat
|| ', '
|| v_new_sequence
|| ', '
|| v_new_executor);
------------------------------------------------------------------
---
IF nvl(pi_result, 0) = 0 THEN
RETURN v_new_id_step;
ELSIF nvl(pi_result, 0) = 1 THEN
RETURN v_new_sequence;
ELSE
RETURN v_act_executor;
END IF;
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.next_step_status - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - next_step_status: ' || sqlerrm);
------------------------------------------------------------------
RETURN -999;
END;
/*--------------- FNC03-------------------*/
FUNCTION step_status_result(
pi_pattern IN NUMBER,
pi_id_pattern_step IN NUMBER,
pi_id_status IN NUMBER
) RETURN VARCHAR2 AS
v_result VARCHAR2(50);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.step_status_result';
BEGIN
SELECT (
CASE pi_id_status
WHEN 0 THEN
'Procesando'
WHEN 1 THEN
ps.status_success
WHEN 2 THEN
ps.status_warning
WHEN (-1) THEN
ps.status_error
WHEN (-2) THEN
ps.status_abort
WHEN (-3) THEN
ps.status_timeout
ELSE
NULL
END
) AS functional_status
INTO v_result
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = pi_id_pattern_step
AND ps.id_pattern = pi_pattern;
---
apex_debug.info('pkg_deploy_coordinator.step_status_result - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.step_status_result - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - step_status_result: ' || sqlerrm);
------------------------------------------------------------------
RETURN 'ERROR-WTF-FNC03';
END;
/*--------------- FNC04-------------------*/
FUNCTION step_status_category(
pi_id_pattern IN NUMBER,
pi_id_pattern_step IN NUMBER,
pi_id_status IN NUMBER
) RETURN NUMBER AS
v_result NUMBER := 0;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.step_status_category';
BEGIN
SELECT (
CASE pi_id_status
WHEN 0 THEN
0
WHEN 1 THEN
ps.id_act_cat_success
WHEN 2 THEN
ps.id_act_cat_warning
WHEN (-1) THEN
ps.id_act_cat_error
WHEN (-2) THEN
ps.id_act_cat_abort
WHEN (-3) THEN
ps.id_act_cat_timeout
ELSE
NULL
END
) AS category
INTO v_result
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = pi_id_pattern_step
AND ps.id_pattern = pi_id_pattern;
---
apex_debug.info('pkg_deploy_coordinator.step_status_category - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.step_status_category - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - step_status_category: ' || sqlerrm);
------------------------------------------------------------------
RETURN -999;
END;
/*--------------- FNC05-------------------*/
FUNCTION step_status_retry(
pi_id_pattern IN NUMBER,
pi_id_pattern_step IN NUMBER,
pi_id_status IN NUMBER
) RETURN NUMBER AS
v_result NUMBER := 0;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.step_status_retry';
BEGIN
SELECT (
CASE pi_id_status
WHEN 0 THEN
0
WHEN 1 THEN
0
WHEN 2 THEN
0
WHEN (-1) THEN
ps.retries_error
WHEN (-2) THEN
0
WHEN (-3) THEN
ps.retries_timeout
ELSE
0
END
) AS retries
INTO v_result
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = pi_id_pattern_step
AND ps.id_pattern = pi_id_pattern;
---
apex_debug.info('pkg_deploy_coordinator.step_status_retry - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.step_status_retry - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - step_status_retry: ' || sqlerrm);
------------------------------------------------------------------
RETURN -999;
END;
/*--------------- FNC06-------------------*/
FUNCTION step_exec_category(
pi_id_pattern IN NUMBER,
pi_id_pattern_step IN NUMBER,
pi_result IN NUMBER
-- 0:exec_category_type / 1:id_exec_category
) RETURN NUMBER AS
v_result NUMBER := 0;
v_sqlerrm VARCHAR2(500);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.step_exec_category';
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES pi_result, pi_id_pattern, pi_id_pattern_step: '
|| pi_result
|| ', '
|| pi_id_pattern
|| ', '
|| pi_id_pattern_step);
------------------------------------------------------------------
SELECT decode(nvl(pi_result, 0), 0, ec.exec_category_type, ps.id_exec_category) AS category
INTO v_result
FROM deployment_pattern_step ps,
executor_category ec
WHERE ps.id_exec_category = ec.id_exec_category
AND ps.id_pattern_step = pi_id_pattern_step
AND ps.id_pattern = pi_id_pattern;
---
apex_debug.info('pkg_deploy_coordinator.step_exec_category - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.step_exec_category - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - step_exec_category: ' || sqlerrm);
------------------------------------------------------------------
RETURN -999;
--WTF
END;
/*--------------- FNC07-------------------*/
FUNCTION sch_job_name(
pi_id_schedule IN NUMBER,
pi_id_schedule_detail IN NUMBER,
pi_schedule_fequency IN NUMBER
) RETURN VARCHAR2 AS
v_result VARCHAR2(100);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.sch_job_name';
BEGIN
SELECT 'SC'
|| lpad(s.id_schedule, 2, 0)
|| lpad(pi_id_schedule_detail, 2, 0)
|| 'F'
|| lpad(pi_schedule_fequency, 2, 0)
|| 'DT'
|| lpad(id_deployment_type, 2, 0)
|| 'ET'
|| lpad(id_enviroment_type, 2, 0) AS job_name
INTO v_result
FROM deployment_schedule s
WHERE s.id_schedule = pi_id_schedule;
apex_debug.info('pkg_deploy_coordinator.sch_job_name - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
EXCEPTION
WHEN no_data_found THEN
apex_debug.error('pkg_deploy_coordinator.sch_job_name - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - sch_job_name: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*--------------- FNC08-------------------*/
FUNCTION schedule_fequency_name(
pi_schedule_fequency IN NUMBER,
pi_schedule_init_date IN DATE
) RETURN VARCHAR2 AS
v_result VARCHAR2(200);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.schedule_fequency_name';
BEGIN
CASE
WHEN pi_schedule_fequency = 1 THEN
v_result := 'DIARIAMENTE: HORA-' || to_char(pi_schedule_init_date, 'HH24:MI:SS');
WHEN pi_schedule_fequency = 2 THEN
v_result := 'SEMANALMENTE: '
|| trim(to_char(pi_schedule_init_date, 'DAY'))
|| ' | HORA-'
|| to_char(pi_schedule_init_date, 'HH24:MI:SS');
WHEN pi_schedule_fequency = 3 THEN
v_result := 'MENSUALMENTE: DIA-'
|| to_char(pi_schedule_init_date, 'DD')
|| ' | HORA-'
|| to_char(pi_schedule_init_date, 'HH24:MI:SS');
WHEN pi_schedule_fequency = 4 THEN
v_result := 'ANUALMENTE: DIA-'
|| to_char(pi_schedule_init_date, 'DD')
|| ' | MES-'
|| trim(to_char(pi_schedule_init_date, 'MONTH'))
|| ' | HORA-'
|| to_char(pi_schedule_init_date, 'HH24:MI:SS');
END CASE;
apex_debug.info('pkg_deploy_coordinator.schedule_fequency_name - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
EXCEPTION
WHEN no_data_found THEN
apex_debug.error('pkg_deploy_coordinator.schedule_fequency_name - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - schedule_fequency_name: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*--------------- FNC09-------------------*/
FUNCTION schedule_fequency_job(
pi_schedule_fequency IN NUMBER,
pi_schedule_init_date IN DATE
) RETURN VARCHAR2 AS
v_result VARCHAR2(200);
v_job_freq VARCHAR2(100);
v_day VARCHAR2(50);
v_hour VARCHAR2(50);
v_minute VARCHAR2(50);
v_second VARCHAR2(50);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.schedule_fequency_job';
BEGIN
CASE pi_schedule_fequency
WHEN 1 THEN
v_job_freq := 'FREQ=DAILY';
--- Establece la hora exacta de ejecucion en funcion de la fecha inicial
v_hour := ';byhour=' || to_char(pi_schedule_init_date, 'HH24');
v_minute := ';byminute=' || to_char(pi_schedule_init_date, 'MI');
v_second := ';bysecond=' || to_char(pi_schedule_init_date, 'SS');
v_result := v_job_freq
|| v_hour
|| v_minute
|| v_second;
WHEN 2 THEN
v_job_freq := 'FREQ=WEEKLY';
--- Determina el dia de la semana donde se ejecuta en funcion de la fecha inicial
CASE to_char(pi_schedule_init_date, 'D')
WHEN 1 THEN
v_day := ';BYDAY=MON';
WHEN 2 THEN
v_day := ';BYDAY=TUE';
WHEN 3 THEN
v_day := ';BYDAY=WED';
WHEN 4 THEN
v_day := ';BYDAY=THU';
WHEN 5 THEN
v_day := ';BYDAY=FRI';
WHEN 6 THEN
v_day := ';BYDAY=SAT';
ELSE
v_day := ';BYDAY=SUN';
END CASE;
--- Establece la hora exacta de ejecucion en funcion de la fecha inicial
v_hour := ';byhour=' || to_char(pi_schedule_init_date, 'HH24');
v_minute := ';byminute=' || to_char(pi_schedule_init_date, 'MI');
v_second := ';bysecond=' || to_char(pi_schedule_init_date, 'SS');
---
v_result := v_job_freq
|| v_day
|| v_hour
|| v_minute
|| v_second;
WHEN 3 THEN
v_job_freq := 'FREQ=MONTHLY';
---
v_result := v_job_freq;
WHEN 4 THEN
v_job_freq := 'FREQ=YEARLY';
v_result := v_job_freq;
ELSE
v_result := '';
END CASE;
apex_debug.info('pkg_deploy_coordinator.schedule_fequency_job - v_result: %s', v_result);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
EXCEPTION
WHEN no_data_found THEN
apex_debug.error('pkg_deploy_coordinator.schedule_fequency_job - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - schedule_fequency_job: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*--------------- FNC10-------------------*/
FUNCTION fnc_request_icon_deploy(
pi_id_status NUMBER
) RETURN VARCHAR2 AS
v_result VARCHAR2(100);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.fnc_request_icon_deploy';
BEGIN
SELECT (
CASE
WHEN pi_id_status = 0 THEN
'fa fa-rocket fam-blank fam-is-info'
--0
WHEN pi_id_status = 1 THEN
'fa fa-rocket fam-check fam-is-success'
--ps.id_act_cat_success
WHEN pi_id_status = 2 THEN
'fa fa-rocket fam-check fam-is-warning'
--ps.id_act_cat_warning
WHEN pi_id_status = (-1) THEN
'fa fa-rocket fam-x fam-is-danger'
--ps.id_act_cat_error
WHEN pi_id_status = (-2) THEN
'fa fa-ban'
--ps.id_act_cat_abort
WHEN pi_id_status = (-3) THEN
'fa fa-clock-o fam-x fam-is-danger'
--ps.id_act_cat_timeout
WHEN pi_id_status = (-4) THEN
'fa fa-rocket fam-x fam-is-danger'
-- -4 - Error interno, NO devuelto por Jenkins
ELSE
'fa fa-rocket fam-blank fam-is-info'
END
) AS category_action
INTO v_result
FROM dual;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*--------------- FNC11-------------------*/
FUNCTION fnc_request_status(
pi_id_control_deploy NUMBER
) RETURN VARCHAR2 AS
v_id_status NUMBER;
v_status VARCHAR2(100);
v_last_step BOOLEAN;
v_category NUMBER;
v_pattern NUMBER;
v_pattern_step NUMBER;
v_id_obj_hgroup VARCHAR(50);
v_max_group VARCHAR(50);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.fnc_request_status';
BEGIN
v_last_step := false;
SELECT ed.id_deployment_status,
ed.id_pattern,
ed.id_pattern_step,
ed.id_obj_hgroup,
(
SELECT MAX(h.id_req_hgroup)
FROM deployment_req_hgroup h
WHERE h.id_request = ed.id_request
) AS max_group
INTO v_id_status,
v_pattern,
v_pattern_step,
v_id_obj_hgroup,
v_max_group
FROM request_exec_deploy ed
WHERE ed.id_control_deploy = pi_id_control_deploy;
--analizo si es el ultimo paso en el patron
SELECT (
CASE
WHEN v_id_status = 0 THEN
0
WHEN v_id_status = 1 THEN
ps.id_act_cat_success
WHEN v_id_status = 2 THEN
ps.id_act_cat_warning
WHEN v_id_status = -1 THEN
ps.id_act_cat_error
WHEN v_id_status = -2 THEN
ps.id_act_cat_abort
WHEN v_id_status = -3 THEN
ps.id_act_cat_timeout
ELSE
NULL
END
) AS category_action
INTO v_category
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = v_pattern_step
AND ps.id_pattern = v_pattern;
--si es el ultimo paso en el patron, seteo la variable last_step
IF v_category IN (6, 7) AND (v_id_obj_hgroup = v_max_group) THEN
v_last_step := true;
END IF;
--analizo si es el ultimo paso de la solicitud o no y seteo el resultado
IF v_last_step = false THEN
--analizo segun el id, si es un estado correcto, con advertencias o con errores
IF v_id_status >= 0 THEN
v_status := 'En Proceso';
ELSIF v_id_status < 0 AND v_category IN (6, 7) THEN
v_status := 'Abortada';
ELSE
v_status := 'Con Errores';
END IF;
ELSE
--analizo segun el id, si es un estado correcto, con advertencias o con errores
IF v_id_status > 0 THEN
v_status := 'Finalizada';
ELSIF v_id_status < 0 THEN
v_status := 'Abortada';
END IF;
END IF;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_status: ' || v_status);
------------------------------------------------------------------
RETURN v_status;
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*--------------- FNC12-------------------*/
FUNCTION fnc_request_progress(
pi_id_request NUMBER
) RETURN VARCHAR2 AS
v_total_obj_deploy NUMBER;
v_total_obj NUMBER;
v_result VARCHAR2(100);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.fnc_request_progress';
BEGIN
SELECT (
SELECT COUNT(1)
FROM request_exec_deploy ed
WHERE id_request = t.id_request
AND id_deployment_status > 0
AND id_control_deploy = (
SELECT MAX(ec.id_control_deploy)
FROM request_exec_deploy ec
WHERE ec.id_dep_req_object = ed.id_dep_req_object
AND ec.id_request = ed.id_request
AND ec.env_order = ed.env_order
)
AND (
SELECT (
CASE
WHEN ed.id_deployment_status = 0 THEN
0
WHEN ed.id_deployment_status = 1 THEN
ps.id_act_cat_success
WHEN ed.id_deployment_status = 2 THEN
ps.id_act_cat_warning
WHEN ed.id_deployment_status = -1 THEN
ps.id_act_cat_error
WHEN ed.id_deployment_status = -2 THEN
ps.id_act_cat_abort
WHEN ed.id_deployment_status = -3 THEN
ps.id_act_cat_timeout
ELSE
NULL
END
) AS category_action
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = ed.id_pattern_step
AND ps.id_pattern = ed.id_pattern
) = 7
) AS obj,
t.q_object
INTO v_total_obj_deploy,
v_total_obj
FROM request_exec_tray t
WHERE t.id_request = pi_id_request;
v_result := v_total_obj_deploy
|| '/'
|| v_total_obj
|| ' Objetos ('
|| round(((v_total_obj_deploy / v_total_obj) * 100), 2)
|| '%)';
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*--------------- FNC13-------------------*/
FUNCTION fnc_icon_flag(
pi_id_request NUMBER,
pi_env_hgroup VARCHAR2
) RETURN VARCHAR2 AS
v_result VARCHAR2(100);
v_total_obj_hgroup NUMBER;
v_total_obj_hgroup_deploy NUMBER;
v_total_obj_processing NUMBER;
v_obj_error NUMBER;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.fnc_icon_flag';
BEGIN
SELECT (
SELECT COUNT(1)
FROM request_exec_deploy ed
WHERE id_request = t.id_request
AND id_deployment_status > 0
AND id_control_deploy = (
SELECT MAX(ec.id_control_deploy)
FROM request_exec_deploy ec
WHERE ec.id_dep_req_object = ed.id_dep_req_object
AND ec.id_request = ed.id_request
)
AND (
SELECT (
CASE
WHEN ed.id_deployment_status = 0 THEN
0
WHEN ed.id_deployment_status = 1 THEN
ps.id_act_cat_success
WHEN ed.id_deployment_status = 2 THEN
ps.id_act_cat_warning
WHEN ed.id_deployment_status = -1 THEN
ps.id_act_cat_error
WHEN ed.id_deployment_status = -2 THEN
ps.id_act_cat_abort
WHEN ed.id_deployment_status = -3 THEN
ps.id_act_cat_timeout
ELSE
NULL
END
) AS category_action
FROM deployment_pattern_step ps
WHERE ps.id_pattern_step = ed.id_pattern_step
AND ps.id_pattern = ed.id_pattern
) = 7
AND ed.id_env_hgroup = pi_env_hgroup
),
(
SELECT COUNT(1)
FROM request_exec_deploy ed
WHERE ed.id_request = t.id_request
AND ed.id_control_deploy = (
SELECT MAX(ec.id_control_deploy)
FROM request_exec_deploy ec
WHERE ec.id_dep_req_object = ed.id_dep_req_object
AND ec.id_request = ed.id_request
)
AND ed.id_env_hgroup = pi_env_hgroup
),
(
SELECT COUNT(1)
FROM request_exec_deploy ed
WHERE ed.id_request = pi_id_request
AND ed.id_control_deploy = (
SELECT MAX(ec.id_control_deploy)
FROM request_exec_deploy ec
WHERE ec.id_dep_req_object = ed.id_dep_req_object
AND ec.id_request = ed.id_request
)
AND ed.id_env_hgroup = pi_env_hgroup
AND id_deployment_status < 0
)
INTO v_total_obj_hgroup_deploy,
v_total_obj_processing,
v_obj_error
FROM request_exec_tray t
WHERE t.id_request = pi_id_request;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_total_obj_hgroup_deploy,v_total_obj_processing,v_obj_error: '
|| v_total_obj_hgroup_deploy
|| ','
|| v_total_obj_processing
|| ','
|| v_obj_error);
------------------------------------------------------------------
SELECT COUNT(1)
INTO v_total_obj_hgroup
FROM hierarchical_request_vew
WHERE group_id = pi_id_request
AND level_alias = 'O'
AND level_path_id LIKE 'R'
|| pi_id_request
|| ','
|| pi_env_hgroup
|| '%';
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_total_obj_hgroup: ' || v_total_obj_hgroup);
------------------------------------------------------------------
IF (v_total_obj_hgroup = v_total_obj_hgroup_deploy) THEN
v_result := 'fa fa-code fam-check fam-is-success';
ELSIF (v_total_obj_hgroup > v_total_obj_hgroup_deploy) AND (v_obj_error > 0) THEN
v_result := 'fa fa-code fam-x fam-is-danger';
ELSIF (v_total_obj_hgroup > v_total_obj_hgroup_deploy) AND (v_total_obj_hgroup_deploy > 0) THEN
v_result := 'fa fa-code fam-blank fam-is-info';
ELSIF (v_total_obj_hgroup > v_total_obj_hgroup_deploy) AND (v_total_obj_hgroup_deploy = 0) AND (v_total_obj_processing > 0) THEN
v_result := 'fa fa-code fam-blank fam-is-info';
ELSIF (v_total_obj_hgroup > v_total_obj_hgroup_deploy) AND (v_total_obj_hgroup = 0) THEN
v_result := 'fa fa-code';
ELSE
v_result := 'fa fa-code';
END IF;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'VARIABLES v_result: ' || v_result);
------------------------------------------------------------------
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR: ' || sqlerrm);
------------------------------------------------------------------
RETURN NULL;
END;
/*-------------------------------------------------------------------------*/
/*-------------------------------------------------------------------------*/
/*--------------------------- PROCEDURES ----------------------------------*/
/*-------------------------------------------------------------------------*/
/*--------------- DPCP1-------------------*/
PROCEDURE manage_enviroment_pattern(
pi_id_pattern IN NUMBER,
pi_id_enviroment IN NUMBER,
po_warning OUT NOCOPY VARCHAR2
) AS
v_deploy_enable NUMBER := 0;
v_enviroment_status NUMBER := 0;
v_enviroment_alias VARCHAR2(100);
v_enviroment_set NUMBER := 0;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.manage_enviroment_pattern';
BEGIN
SELECT e.deploy_enable,
e.enviroment_status AS env_enable,
e.enviroment_name
|| ' ('
|| e.enviroment_alias
|| ') ',
nvl((
SELECT 1
FROM deployment_pattern_env ep
WHERE ep.id_enviroment = e.id_enviroment
AND ep.id_pattern = pi_id_pattern
), 0)
INTO v_deploy_enable,
v_enviroment_status,
v_enviroment_alias,
v_enviroment_set
FROM enviroment e
WHERE e.id_enviroment = pi_id_enviroment;
IF v_enviroment_status = 1 AND v_enviroment_set = 0 THEN
BEGIN
INSERT INTO tmp_enviroment_pattern (
id_pattern,
id_enviroment
)
VALUES (
pi_id_pattern,
pi_id_enviroment
);
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 1, pi_message_class => 'DPCP1',
pi_return_type => 1);
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.manage_enviroment_pattern - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - tmp_enviroment_pattern: ' || sqlerrm);
------------------------------------------------------------------
DELETE tmp_enviroment_pattern
WHERE id_enviroment = pi_id_enviroment
AND id_pattern = pi_id_pattern;
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 2, pi_message_class => 'DPCP1',
pi_return_type => 1);
END;
ELSIF v_enviroment_status = 0 THEN
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 3, pi_message_class => 'DPCP1',
pi_return_type => 1);
ELSIF v_enviroment_set = 1 THEN
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 4, pi_message_class => 'DPCP1',
pi_return_type => 1);
ELSE
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 5, pi_message_class => 'DPCP1',
pi_return_type => 1);
END IF;
EXCEPTION
WHEN OTHERS THEN
---
apex_debug.error('pkg_deploy_coordinator.manage_enviroment_pattern - %s', sqlerrm);
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 5, pi_message_class => 'DPCP1',
pi_return_type => 1);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - manage_enviroment_pattern: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP2-------------------*/
PROCEDURE manage_enviroment_request(
pi_id_request IN NUMBER,
pi_id_req_hgroup IN VARCHAR2,
pi_id_deploying_tech IN NUMBER,
pi_id_enviroment IN NUMBER,
po_warning OUT NOCOPY VARCHAR2
) AS
v_order NUMBER := 0;
v_deploy_enable NUMBER := 0;
v_enviroment_status NUMBER := 0;
v_enviroment_alias VARCHAR2(100);
v_enviroment_set NUMBER := 0;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.manage_enviroment_request';
BEGIN
SELECT e.deploy_enable,
e.enviroment_status AS env_enable,
e.enviroment_name
|| ' ('
|| e.enviroment_alias
|| ') ',
nvl((SELECT COUNT(1) FROM enviroment ep WHERE ep.id_parent_enviroment = e.id_enviroment), 0) AS seted
INTO v_deploy_enable,
v_enviroment_status,
v_enviroment_alias,
v_enviroment_set
FROM enviroment e
WHERE e.id_enviroment = pi_id_enviroment;
IF v_enviroment_status = 1 AND v_enviroment_set = 0 THEN
BEGIN
---
SELECT nvl(MAX(enviroment_order), 0)
INTO v_order
FROM tmp_enviroments_request;
---
INSERT INTO tmp_enviroments_request (
id_dep_req_env,
id_enviroment,
id_deploying_tech,
id_request,
enviroment_order,
id_req_hgroup
)
VALUES (
v_order + 1,
pi_id_enviroment,
pi_id_deploying_tech,
pi_id_request,
v_order + 1,
pi_id_req_hgroup
);
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 1, pi_message_class => 'DPCP2',
pi_return_type => 1);
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.manage_enviroment_request - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - tmp_enviroments_request: ' || sqlerrm);
------------------------------------------------------------------
DELETE tmp_enviroments_request
WHERE id_enviroment = pi_id_enviroment
AND id_req_hgroup = pi_id_req_hgroup;
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 2, pi_message_class => 'DPCP2',
pi_return_type => 1);
END;
ELSIF v_enviroment_status = 0 THEN
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 3, pi_message_class => 'DPCP2',
pi_return_type => 1);
ELSIF v_enviroment_set > 0 THEN
po_warning := pkg_sicd_commons.f_system_message(pi_id_message => 4, pi_message_class => 'DPCP2',
pi_return_type => 1)
|| v_enviroment_alias;
ELSE
---
po_warning := v_enviroment_alias
|| pkg_sicd_commons.f_system_message(pi_id_message => 5, pi_message_class => 'DPCP2',
pi_return_type => 1);
END IF;
EXCEPTION
WHEN OTHERS THEN
---
apex_debug.error('pkg_deploy_coordinator.manage_enviroment_request - %s', sqlerrm);
po_warning := v_enviroment_alias
|| pi_id_enviroment
|| pkg_sicd_commons.f_system_message(pi_id_message => 5, pi_message_class => 'DPCP2',
pi_return_type => 1);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - manage_enviroment_request: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP3-------------------*/
PROCEDURE manage_object_request(
pi_id_request IN NUMBER,
pi_id_req_hgroup IN VARCHAR2,
pi_id_pattern IN NUMBER,
pi_object_path IN VARCHAR2,
pi_object_name IN VARCHAR2,
pi_object_version IN VARCHAR2,
pi_id_object_repo IN VARCHAR2,
po_warning OUT NOCOPY VARCHAR2
) AS
v_order NUMBER := 0;
v_deploy_enable NUMBER := 0;
v_object_status NUMBER := 0;
v_object_t_set NUMBER := 0;
v_object_ver_set VARCHAR2(10);
v_object_hgroup_name VARCHAR2(100);
v_err VARCHAR2(1000);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.manage_object_request';
BEGIN
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '00 - pi_id_request, pi_id_req_hgroup, pi_id_pattern, pi_object_path, pi_object_name, pi_object_version, pi_id_object_rep: '
|| pi_id_request
|| ', '
|| pi_id_req_hgroup
|| ', '
|| pi_id_pattern
|| ', '
|| pi_object_path
|| ', '
|| pi_object_name
|| ', '
|| pi_object_version
|| ', '
|| pi_id_object_repo);
------------------------------------------------------------------
SELECT o.object_version
INTO v_object_ver_set
FROM tmp_objects_request o
WHERE o.object_name = pi_object_name
AND o.object_source_path = pi_object_path;
v_object_t_set := 1;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '01 - pi_id_object_repo, v_object_ver_set: '
|| pi_id_object_repo
|| ', '
|| v_object_ver_set);
------------------------------------------------------------------
EXCEPTION
WHEN no_data_found THEN
-- apex_debug.error('pkg_deploy_coordinator.manage_object_request - %s', sqlerrm);
BEGIN
SELECT o.object_version,
hg.req_hgroup_name
INTO v_object_ver_set,
v_object_hgroup_name
FROM deployment_req_object o,
deployment_req_hgroup hg
WHERE o.id_req_hgroup = hg.id_req_hgroup
AND o.id_req_hgroup != pi_id_req_hgroup
AND o.object_name = pi_object_name
AND o.object_source_path = pi_object_path
AND o.id_request = pi_id_request;
v_object_t_set := 2;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '02 - pi_id_object_repo, v_object_ver_set, v_object_t_set: '
|| pi_id_object_repo
|| ', '
|| v_object_ver_set
|| ', '
|| v_object_hgroup_name
|| ', '
|| v_object_t_set);
------------------------------------------------------------------
EXCEPTION
WHEN no_data_found THEN
-- apex_debug.error('pkg_deploy_coordinator.manage_object_request - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - tmp_objects_request: ' || sqlerrm);
------------------------------------------------------------------
END;
END;
---
IF nvl(v_object_ver_set, 'NE') IN ('NE', pi_object_version) AND v_object_t_set = 1 THEN
---
-- apex_debug.error('pkg_deploy_coordinator.manage_object_request - %s', sqlerrm);
DELETE tmp_objects_request
WHERE id_object_repo = pi_id_object_repo
AND object_name = pi_object_name
AND id_req_hgroup = pi_id_req_hgroup
AND id_request = pi_id_request;
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '04 - pi_id_object_repo, pi_id_req_hgroup: '
|| pi_id_object_repo
|| ', '
|| pi_id_req_hgroup);
------------------------------------------------------------------
po_warning := pi_object_name || pkg_sicd_commons.f_system_message(pi_id_message => 2, pi_message_class => 'DPCP3', pi_return_type => 1);
ELSIF nvl(v_object_ver_set, 'NE') IN ('NE', pi_object_version) AND v_object_t_set = 0 THEN
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => 'EVAL - v_object_ver_set, pi_object_version, v_object_t_set: '
|| v_object_ver_set
|| ', '
|| pi_object_version
|| ', '
|| v_object_t_set);
------------------------------------------------------------------
---
SELECT nvl(MAX(o.object_order), 0)
INTO v_order
FROM tmp_objects_request o
WHERE id_req_hgroup = pi_id_req_hgroup
AND id_request = pi_id_request;
---
INSERT INTO tmp_objects_request (
id_dep_req_object,
id_request,
id_pattern,
id_req_hgroup,
object_order,
id_object_repo,
id_reqdet_instance,
object_dep_status,
object_version,
object_name,
object_source_path
)
VALUES (
v_order + 1,
pi_id_request,
pi_id_pattern,
pi_id_req_hgroup,
v_order + 1,
TRIM(pi_id_object_repo),
NULL,
NULL,
pi_object_version,
pi_object_name,
pi_object_path
);
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '03 - pi_id_object_repo, pi_object_version, pi_object_name, pi_object_path: '
|| pi_id_object_repo
|| ', '
|| pi_object_version
|| ', '
|| pi_object_name
|| ', '
|| pi_object_path);
------------------------------------------------------------------
po_warning := pi_object_name || pkg_sicd_commons.f_system_message(pi_id_message => 1, pi_message_class => 'DPCP3', pi_return_type => 1);
---
EXCEPTION
WHEN OTHERS THEN
---
-- apex_debug.error('pkg_deploy_coordinator.manage_object_request - %s', sqlerrm);
DELETE tmp_objects_request
WHERE id_object_repo = pi_id_object_repo
AND object_name = pi_object_name
AND id_req_hgroup = pi_id_req_hgroup
AND id_request = pi_id_request;
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '04 - pi_id_object_repo, pi_id_req_hgroup: '
|| pi_id_object_repo
|| ', '
|| pi_id_req_hgroup);
------------------------------------------------------------------
po_warning := pi_object_name || pkg_sicd_commons.f_system_message(pi_id_message => 2, pi_message_class => 'DPCP3', pi_return_type => 1);
END;
ELSIF nvl(v_object_ver_set, 'NE') NOT IN ('NE', pi_object_version) AND v_object_t_set = 1 THEN
UPDATE tmp_objects_request o
SET o.id_object_repo = pi_id_object_repo,
o.object_version = pi_object_version
WHERE o.object_name = pi_object_name
AND o.object_source_path = pi_object_path
AND id_request = pi_id_request;
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '05 - pi_id_object_repo, v_object_ver_set, v_object_t_set: '
|| pi_id_object_repo
|| ', '
|| v_object_ver_set
|| ', '
|| v_object_hgroup_name
|| ', '
|| v_object_t_set);
------------------------------------------------------------------
po_warning := pi_object_name || pkg_sicd_commons.f_system_message(pi_id_message => 3, pi_message_class => 'DPCP3', pi_return_type => 1);
ELSIF v_object_ver_set = pi_object_version AND v_object_t_set = 2 THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '06 - pi_id_object_repo, v_object_ver_set, v_object_t_set: '
|| pi_id_object_repo
|| ', '
|| v_object_ver_set
|| ', '
|| v_object_hgroup_name
|| ', '
|| v_object_t_set);
------------------------------------------------------------------
po_warning := pi_object_name
|| pkg_sicd_commons.f_system_message(pi_id_message => 4, pi_message_class => 'DPCP3',
pi_return_type => 1)
|| v_object_hgroup_name;
ELSIF v_object_ver_set != pi_object_version AND v_object_t_set = 2 THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '07 - pi_id_object_repo, v_object_ver_set, v_object_t_set: '
|| pi_id_object_repo
|| ', '
|| v_object_ver_set
|| ', '
|| v_object_hgroup_name
|| ', '
|| v_object_t_set);
------------------------------------------------------------------
po_warning := pi_object_name
|| pkg_sicd_commons.f_system_message(pi_id_message => 4, pi_message_class => 'DPCP3',
pi_return_type => 1)
|| '"'
|| v_object_hgroup_name
|| '"';
ELSE
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- WARNING
pi_log_message => '08 - pi_id_object_repo, v_object_ver_set, v_object_t_set: '
|| pi_id_object_repo
|| ', '
|| v_object_ver_set
|| ', '
|| v_object_hgroup_name
|| ', '
|| v_object_t_set);
------------------------------------------------------------------
po_warning := pi_object_name
|| pkg_sicd_commons.f_system_message(pi_id_message => 5, pi_message_class => 'DPCP3',
pi_return_type => 1)
|| '"'
|| v_object_hgroup_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS THEN
---
-- apex_debug.error('pkg_deploy_coordinator.manage_object_request - %s', sqlerrm);
/*po_warning := pi_object_name
|| pi_id_object_repo
|| pkg_sicd_commons.f_system_message(pi_id_message => 6, pi_message_class => 'DPCP3',
pi_return_type => 1);*/
po_warning := pi_object_name
|| ': '
|| pkg_sicd_commons.f_system_message(pi_id_message => 6, pi_message_class => 'DPCP3',
pi_return_type => 1);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - manage_object_request: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP4-------------------*/
PROCEDURE automatic_dep_control(
pi_id_request IN INTEGER,
pi_id_control_deploy IN INTEGER,
pi_id_status IN INTEGER,
pi_exec_message IN VARCHAR2,
pi_exec_date IN VARCHAR2,
po_warning OUT NOCOPY VARCHAR2
) IS
r_red request_exec_deploy%rowtype;
r_redx request_exec_deploy%rowtype;
v_retry_aux INTEGER := 0;
v_id_next_step INTEGER := 0;
v_seq_next_step INTEGER := 0;
v_exec_next_step INTEGER := 0;
v_sts_category INTEGER := 0;
v_exec_category INTEGER := 0;
v_exec_cat_type INTEGER := 0;
v_next_ctrl_deploy INTEGER := 0;
---
v_total_obj_og INTEGER := 0;
v_total_og_eg INTEGER := 0;
v_total_env_eg INTEGER := 0;
v_total_eg_req INTEGER := 0;
---
v_next_obj INTEGER := 0;
v_next_obj_hgroup INTEGER := 0;
v_next_env INTEGER := 0;
v_next_env_hgroup INTEGER := 0;
---
v_sqlerrm VARCHAR2(500);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.automatic_dep_control';
BEGIN
SELECT d.*
INTO r_red
FROM request_exec_deploy d
WHERE d.id_request = pi_id_request
AND d.id_control_deploy = pi_id_control_deploy;
v_sts_category := pkg_deploy_coordinator.step_status_category(pi_id_pattern => r_red.id_pattern,
pi_id_pattern_step => r_red.id_pattern_step,
pi_id_status => r_red.id_deployment_status);
-- Determina si la ejecucion se esta procesando
IF r_red.id_deployment_status = 0 THEN
NULL;
-- FALTA JOB DE TIMEOUT ---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'PROCESANDO EJECUCION pi_id_request, pi_id_control_deploy, pi_id_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_status);
------------------------------------------------------------------
ELSIF r_red.id_deployment_status < 0 THEN
-- Determina si la ultima ejecucion no fue exitosa
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'EJECUCION CON ERROR pi_id_request, pi_id_control_deploy, pi_id_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_status);
------------------------------------------------------------------
v_retry_aux := pkg_deploy_coordinator.step_status_retry(pi_id_pattern => r_red.id_pattern,
pi_id_pattern_step => r_red.id_pattern_step,
pi_id_status => r_red.id_deployment_status);
-- Determina si posee reintentos
IF v_retry_aux > 0 THEN
IF r_red.retry_numer < v_retry_aux THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'REINTENTO DE EJECUCION pi_id_request, retry_numer: '
|| pi_id_request
|| ', '
|| r_red.retry_numer);
------------------------------------------------------------------
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.retry_numer := r_red.retry_numer + 1;
IF r_red.retry_numer >= v_retry_aux THEN
r_red.deployment_status := 'Último Reintento';
END IF;
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'ULTIMO REINTENTO DE EJECUCION FALLIDO pi_id_request, retry_numer: '
|| pi_id_request
|| ', '
|| r_red.retry_numer);
------------------------------------------------------------------
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.retry_numer := 0;
r_red.id_deployment_status := -1;
r_red.id_dep_proc := NULL;
r_red.control_init_date := sysdate;
r_red.control_end_date := NULL;
r_red.deployment_status := 'Maximo de Reintentos';
r_red.observation := 'SE ALCANZO EL MAXIMO DE REINTENTOS FALLIDOS Y SE REQUIERE INTERVENCION MANUAL PARA CONTINUAR !!!';
-- inserta un nuevo registro de control para subsanacion manual
INSERT INTO request_exec_deploy VALUES r_red;
END IF;
ELSE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'EJECUCION SIN REINTENTOS pi_id_request, retry_numer: '
|| pi_id_request
|| ', '
|| r_red.retry_numer);
------------------------------------------------------------------
---
v_id_next_step := pkg_deploy_coordinator.next_step_status(pi_id_pattern => r_red.id_pattern,
pi_id_pattern_step => r_red.id_pattern_step,
pi_id_status => r_red.id_deployment_status,
pi_result => 0
-- 1: exec_sequence / 0: id_pattern_step,
);
IF v_id_next_step > 0 THEN
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.id_pattern_step := v_id_next_step;
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'EJECUTAR PASO pi_id_request, v_id_next_step: '
|| pi_id_request
|| ', '
|| v_id_next_step);
------------------------------------------------------------------
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
-- DETIENE EL PROCESO DE EJECUCION EN EL ULTIMO CONTROL
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.id_pattern_step := v_id_next_step;
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- AVISA AL PERFIL RESPONSABLE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- DEBUG
pi_log_message => '*** PAUSA DE EJECUCION pi_id_request, id_control_deploy: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy);
------------------------------------------------------------------
END IF;
END IF;
ELSE
--- Determina si la ejecucion fue exitosa
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'EJECUCION CORRECTA pi_id_request, pi_id_control_deploy, pi_id_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_status);
------------------------------------------------------------------
-- Se inicializa el numero de reintentos para el siguiente paso
r_red.retry_numer := 0;
----------------------------------------------------------------
-- Determinar si existen controles hijos
FOR ctrl IN (
SELECT MAX(id_control_deploy) ult_ctrl
FROM request_exec_deploy dc
WHERE dc.id_parent_ctrl_deploy = r_red.id_control_deploy
)
LOOP
-- Si existen controles hijos, buscar el ID mas alto y actualiza el registro actual
-- Obtiene el registro de control ejecutado para una solicitud
BEGIN
SELECT d.*
INTO r_red
FROM request_exec_deploy d
WHERE d.id_request = pi_id_request
AND d.id_control_deploy = ctrl.ult_ctrl;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'ULTIMO REGISTRO HIJO pi_id_request, id_control_deploy: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy);
------------------------------------------------------------------
EXCEPTION
WHEN no_data_found THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 4,
--- DEBUG
pi_log_message => 'ERROR - request_exec_deploy - SIN HIJOS: ' || sqlerrm);
------------------------------------------------------------------
END;
END LOOP;
----------------------------------------------------------------
----------------------------------------------------------------
-- Determina el Tipo de la Categoria de Ejecucion del paso actual
v_exec_cat_type := pkg_deploy_coordinator.step_exec_category(pi_id_pattern => r_red.id_pattern,
pi_id_pattern_step => r_red.id_pattern_step,
pi_result => 0
-- 0: exec_category_type / 1:id_exec_category
);
---
-- Categoría del Paso Actual es AUXILIAR
IF v_exec_cat_type = 3 THEN
-- Determina el Tipo de la Categoria de Ejecucion del paso anterior
SELECT pkg_deploy_coordinator.step_exec_category(pi_id_pattern => d.id_pattern,
pi_id_pattern_step => d.id_pattern_step,
pi_result => 0)
-- 0: exec_category_type / 1: id_exec_category
INTO v_exec_cat_type
FROM request_exec_deploy d
WHERE d.id_request = pi_id_request
AND d.id_control_deploy = (
SELECT MAX(d.id_control_deploy)
FROM request_exec_deploy d
WHERE d.id_request = pi_id_request
AND d.id_control_deploy < r_red.id_control_deploy
);
END IF;
----------------------------------------------------------------
CASE v_exec_cat_type
WHEN 1 THEN
-- Flujo regular de ejecucion
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 2,
--- WARNING
pi_log_message => 'SIGUIENTE EJCUCION REGULAR pi_id_request, pi_id_control_deploy, pi_id_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_status);
------------------------------------------------------------------
IF v_sts_category IN (1, 7) THEN
--- Siguiente paso en el orden de ejecucion
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 4,
--- DEBUG
pi_log_message => 'CATEGORIA PASO SIGUIENTE pi_id_request, pi_id_control_deploy, v_sts_category: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| v_sts_category);
------------------------------------------------------------------
-- Total de Objetos por Grupo de Objetos
SELECT MAX(ro.object_order)
INTO v_total_obj_og
FROM deployment_req_object ro
WHERE ro.id_req_hgroup = r_red.id_obj_hgroup
AND ro.id_request = r_red.id_request;
IF v_total_obj_og > r_red.obj_order THEN
-- Determino si NO es el ultimo objeto del Grupo
-- Busco el siguiente Objeto en el Grupo
-- Ajusta los datos para el nuevo registro de control
r_red.obj_order := r_red.obj_order + 1;
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
---
SELECT ro.id_dep_req_object,
ro.id_object_repo,
ro.object_name,
ro.object_version,
ro.object_source_path
INTO r_red.id_dep_req_object,
r_red.id_object_repo,
r_red.object_name,
r_red.object_version,
r_red.object_source_path
FROM deployment_req_object ro
WHERE ro.id_req_hgroup = r_red.id_obj_hgroup
AND ro.id_request = r_red.id_request
AND ro.object_order = r_red.obj_order;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL SIGUIENTE OBJETO pi_id_request, id_control_deploy, object_name: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy
|| ', '
|| r_red.object_name);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Solicito la Ejecucion del mismo paso del Patron
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
---
IF v_sts_category != 7 THEN
-- Determino si NO es el ultimo Paso del Patron
-- Busco el siguiente Paso Regular del Patron
SELECT ps.id_pattern_step,
ps.id_executor
INTO r_red.id_pattern_step,
r_red.id_executor
FROM deployment_pattern_step ps,
deployment_pattern_step psa
WHERE ps.exec_sequence = psa.exec_sequence + 1
AND ps.id_pattern = psa.id_pattern
AND psa.id_pattern = r_red.id_pattern
AND psa.id_pattern_step = r_red.id_pattern_step;
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.obj_order := 1;
-- Solicito la Ejecucion con el primer Objeto del Grupo
SELECT ro.id_dep_req_object,
ro.id_object_repo,
ro.object_name,
ro.object_version,
ro.object_source_path
INTO r_red.id_dep_req_object,
r_red.id_object_repo,
r_red.object_name,
r_red.object_version,
r_red.object_source_path
FROM deployment_req_object ro
WHERE ro.id_req_hgroup = r_red.id_obj_hgroup
AND ro.id_request = r_red.id_request
AND ro.object_order = 1;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL SIGUIENTE PASO DEL PATRON pi_id_request, id_control_deploy, id_pattern_step: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy
|| ', '
|| r_red.id_pattern_step);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
-- Total de Grupos de Objetos por Grupo de Ambientes
SELECT MAX(og.req_hgroup_order)
INTO v_total_og_eg
FROM deployment_req_hgroup og
WHERE og.id_parent_req_hgroup = r_red.id_env_hgroup
AND og.id_request = r_red.id_request;
IF v_total_og_eg > r_red.obj_hgroup_order THEN
-- Determino si NO es el ultimo Grupo de objetos
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.obj_hgroup_order := r_red.obj_hgroup_order + 1;
r_red.obj_order := 1;
-- Busco el siguiente Grupo de Objetos
SELECT og.id_req_hgroup,
og.id_req_hgroup_ref
INTO r_red.id_obj_hgroup,
r_red.id_pattern
FROM deployment_req_hgroup og
WHERE og.id_parent_req_hgroup = r_red.id_env_hgroup
AND og.id_request = r_red.id_request
AND og.req_hgroup_order = r_red.obj_hgroup_order;
-- Primer Paso del Patron
SELECT id_pattern_step,
id_executor
INTO r_red.id_pattern_step,
r_red.id_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern = r_red.id_pattern
AND ps.exec_sequence = 1;
-- Primer Objeto del Grupo
SELECT ro.id_dep_req_object,
ro.id_object_repo,
ro.object_name,
ro.object_version,
ro.object_source_path
INTO r_red.id_dep_req_object,
r_red.id_object_repo,
r_red.object_name,
r_red.object_version,
r_red.object_source_path
FROM deployment_req_object ro
WHERE ro.id_req_hgroup = r_red.id_obj_hgroup
AND ro.id_request = r_red.id_request
AND ro.object_order = 1;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL SIGUIENTE GRUPO DE OBJETOS pi_id_request, id_control_deploy, id_obj_hgroup: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy
|| ', '
|| r_red.id_obj_hgroup);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
-- Total de Ambientes por Grupo de Ambientes
SELECT MAX(re.enviroment_order)
INTO v_total_env_eg
FROM deployment_req_env re
WHERE re.id_req_hgroup = r_red.id_env_hgroup
AND re.id_request = r_red.id_request;
IF v_total_env_eg > r_red.env_order THEN
-- Determino si NO es el ultimo Ambiente del Grupo
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.env_order := r_red.env_order + 1;
r_red.obj_hgroup_order := 1;
r_red.obj_order := 1;
-- Busco el Siguiente Ambientes
SELECT re.id_enviroment,
(
SELECT id_parent_enviroment
FROM enviroment ep
WHERE ep.id_enviroment = re.id_enviroment
) AS id_parent_enviroment
INTO r_red.id_enviroment,
r_red.id_parent_enviroment
FROM deployment_req_env re
WHERE re.id_req_hgroup = r_red.id_env_hgroup
AND re.id_request = r_red.id_request
AND re.enviroment_order = r_red.env_order;
-- Primer Grupo de Objetos
SELECT og.id_req_hgroup,
og.id_req_hgroup_ref
INTO r_red.id_obj_hgroup,
r_red.id_pattern
FROM deployment_req_hgroup og
WHERE og.id_parent_req_hgroup = r_red.id_env_hgroup
AND og.id_request = r_red.id_request
AND og.req_hgroup_order = 1;
-- Primer Paso del Patron
SELECT id_pattern_step,
id_executor
INTO r_red.id_pattern_step,
r_red.id_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern = r_red.id_pattern
AND ps.exec_sequence = 1;
-- Primer Objeto del Grupo
SELECT ro.id_dep_req_object,
ro.id_object_repo,
ro.object_name,
ro.object_version,
ro.object_source_path
INTO r_red.id_dep_req_object,
r_red.id_object_repo,
r_red.object_name,
r_red.object_version,
r_red.object_source_path
FROM deployment_req_object ro
WHERE ro.id_req_hgroup = r_red.id_obj_hgroup
AND ro.id_request = r_red.id_request
AND ro.object_order = 1;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL SIGUIENTE AMBIENTE pi_id_request, id_control_deploy, id_enviroment: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy
|| ', '
|| r_red.id_enviroment);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
-- Total de Grupos de Ambientes por Solicitud
SELECT MAX(eg.req_hgroup_order)
INTO v_total_eg_req
FROM deployment_req_hgroup eg
WHERE eg.id_parent_req_hgroup = r_red.id_req_hgroup
AND eg.id_request = r_red.id_request;
IF v_total_eg_req > r_red.env_hgroup_order THEN
-- Determino si NO es el ultimo Grupo de Ambientes
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.env_hgroup_order := r_red.env_hgroup_order + 1;
r_red.env_order := 1;
r_red.obj_hgroup_order := 1;
r_red.obj_order := 1;
-- Busco el siguiente Grupo de Ambientes
SELECT id_req_hgroup
INTO r_red.id_env_hgroup
FROM deployment_req_hgroup eg
WHERE eg.id_parent_req_hgroup = r_red.id_req_hgroup
AND eg.id_request = r_red.id_request
AND eg.req_hgroup_order = r_red.env_hgroup_order;
-- Primer Ambiente del Grupo
SELECT re.id_enviroment,
(
SELECT id_parent_enviroment
FROM enviroment ep
WHERE ep.id_enviroment = re.id_enviroment
) AS id_parent_enviroment
INTO r_red.id_enviroment,
r_red.id_parent_enviroment
FROM deployment_req_env re
WHERE re.id_req_hgroup = r_red.id_env_hgroup
AND re.id_request = r_red.id_request
AND re.enviroment_order = 1;
-- Primer Grupo de Objetos
SELECT og.id_req_hgroup,
og.id_req_hgroup_ref
INTO r_red.id_obj_hgroup,
r_red.id_pattern
FROM deployment_req_hgroup og
WHERE og.id_parent_req_hgroup = r_red.id_env_hgroup
AND og.id_request = r_red.id_request
AND og.req_hgroup_order = 1;
-- Primer Paso del Patron
SELECT id_pattern_step,
id_executor
INTO r_red.id_pattern_step,
r_red.id_executor
FROM deployment_pattern_step ps
WHERE ps.id_pattern = r_red.id_pattern
AND ps.exec_sequence = 1;
-- Primer Objeto del Grupo
SELECT ro.id_dep_req_object,
ro.id_object_repo,
ro.object_name,
ro.object_version,
ro.object_source_path
INTO r_red.id_dep_req_object,
r_red.id_object_repo,
r_red.object_name,
r_red.object_version,
r_red.object_source_path
FROM deployment_req_object ro
WHERE ro.id_req_hgroup = r_red.id_obj_hgroup
AND ro.id_request = r_red.id_request
AND ro.object_order = 1;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL SIGUIENTE GRUPO DE AMBIENTES pi_id_request, id_control_deploy, id_env_hgroup: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy
|| ', '
|| r_red.id_env_hgroup);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
ELSE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'FIN DE LA SOLICITUD pi_id_request, id_control_deploy: '
|| pi_id_request
|| ', '
|| r_red.id_control_deploy);
------------------------------------------------------------------
next_dep_queue(
pi_id_request => pi_id_request,
pi_queue_alias => r_red.queue_alias
);
-- FIN DE LA SOLICITUD
END IF;
END IF;
END IF;
END IF;
END IF;
ELSE
-- Proximo paso configurado de ejecucion
v_id_next_step := pkg_deploy_coordinator.next_step_status(pi_id_pattern => r_red.id_pattern,
pi_id_pattern_step => r_red.id_pattern_step,
pi_id_status => r_red.id_deployment_status,
pi_result => 1
-- 0: exec_sequence / 1: id_pattern_step,
);
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.id_pattern_step := v_id_next_step;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL PASO* pi_id_request, pi_id_control_deploy, v_sts_category, v_id_next_step: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| v_sts_category
|| ', '
|| v_id_next_step);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
END IF;
WHEN 2 THEN
-- Flujo de Subsanacion de la Ejecucion
v_id_next_step := pkg_deploy_coordinator.next_step_status(pi_id_pattern => r_red.id_pattern,
pi_id_pattern_step => r_red.id_pattern_step,
pi_id_status => r_red.id_deployment_status,
pi_result => 1
-- 0: exec_sequence / 1: id_pattern_step
);
-- Ajusta los datos para el nuevo registro de control
v_next_ctrl_deploy := s_request_exec_dep_id.nextval;
r_red.id_control_deploy := v_next_ctrl_deploy;
r_red.id_pattern_step := v_id_next_step;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 3,
--- DEBUG
pi_log_message => 'IR AL PASO DE SUBSANACION pi_id_request, pi_id_control_deploy, v_sts_category, v_id_next_step: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| v_sts_category
|| ', '
|| v_id_next_step);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
next_dep_control(n_red => r_red);
-- Lanza la ejecucion automatica
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => r_red.id_control_deploy,
pi_id_executor => r_red.id_executor);
WHEN 3 THEN
-- Flujo Auxiliar
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 1,
--- INFO
pi_log_message => 'EJCUCION AUXILIAR pi_id_request, pi_id_control_deploy, pi_id_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_status);
------------------------------------------------------------------
ELSE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 2,
--- WARNING
pi_log_message => 'WTF-PROBLEMA pi_id_request, pi_id_control_deploy, pi_id_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_status);
------------------------------------------------------------------
END CASE;
END IF;
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.automatic_dep_control - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name,
pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - automatic_dep_control: ' || sqlerrm);
------------------------------------------------------------------
END automatic_dep_control;
/*--------------- DPCP5-------------------*/
PROCEDURE first_dep_control(
pi_id_request IN NUMBER,
pi_id_user IN NUMBER,
pi_queue_alias IN VARCHAR2,
n_red OUT NOCOPY request_exec_deploy%rowtype
) AS
v_scope_type INTEGER := 0;
v_id_scope INTEGER := 0;
r_redx request_exec_deploy%rowtype;
v_new_id_ctrl INTEGER := 0;
v_warning VARCHAR2(1000);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.first_dep_control';
BEGIN
v_new_id_ctrl := s_request_exec_dep_id.nextval;
--
SELECT eg.id_parent_req_hgroup AS id_req_hgroup,
re.id_request,
NULL AS id_deployment_status,
NULL AS deployment_status,
eg.req_hgroup_order AS env_hgroup_order,
0 AS env_hgroup_members,
eg.id_req_hgroup AS id_env_hgroup,
id_deploying_tech,
re.enviroment_order AS env_order,
(
SELECT e.id_enviroment
FROM enviroment e
WHERE e.id_enviroment = re.id_enviroment
) AS id_enviroment,
(
SELECT e.id_parent_enviroment
FROM enviroment e
WHERE e.id_enviroment = re.id_enviroment
) AS id_parent_enviroment,
og.req_hgroup_order AS obj_hgroup_order,
0 AS obj_hgroup_members,
og.id_req_hgroup AS id_obj_hgroup,
ps.id_pattern,
ps.id_pattern_step,
ro.object_order AS obj_order,
id_object_repo,
object_source_path,
object_name,
object_version,
v_new_id_ctrl AS id_control_deploy,
pi_id_user AS id_user_control,
id_executor,
NULL AS id_dep_proc,
0 AS retry_numer,
NULL AS control_init_date,
NULL AS control_end_date,
id_dep_req_object,
NULL AS id_parent_ctrl_deploy,
pi_queue_alias AS queue_alias,
NULL AS id_prc_queue,
NULL AS observation
-------------------
INTO n_red
FROM deployment_req_object ro,
deployment_pattern_step ps,
deployment_req_hgroup og,
deployment_req_env re,
deployment_req_hgroup eg
WHERE ro.id_req_hgroup = og.id_req_hgroup
AND og.id_parent_req_hgroup = eg.id_req_hgroup
AND re.id_req_hgroup = eg.id_req_hgroup
AND ps.id_pattern = og.id_req_hgroup_ref
AND eg.id_request = re.id_request
AND og.id_request = re.id_request
AND ro.id_request = re.id_request
AND re.id_request = re.id_request
AND re.id_request = pi_id_request
AND ro.object_order = (
SELECT MIN(ro1.object_order)
FROM deployment_req_object ro1
WHERE ro1.id_request = re.id_request
AND ro1.id_req_hgroup = og.id_req_hgroup
)
--------------------------------
AND og.req_hgroup_order = (
SELECT MIN(og1.req_hgroup_order)
FROM deployment_req_hgroup og1
WHERE og1.id_request = re.id_request
AND og1.id_parent_req_hgroup = eg.id_req_hgroup
)
--------------------------------
AND re.enviroment_order = (
SELECT MIN(re1.enviroment_order)
FROM deployment_req_env re1
WHERE re1.id_request = re.id_request
AND re1.id_req_hgroup = eg.id_req_hgroup
)
--------------------------------
AND eg.req_hgroup_order = 1/*(
SELECT MIN(eg1.req_hgroup_order)
FROM deployment_req_hgroup eg1
WHERE eg1.id_request = re.id_request
AND og.id_parent_req_hgroup = eg1.id_req_hgroup
)*/
--------------------------------
AND ps.exec_sequence = 1;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 1,
--- INFO
pi_log_message => 'PRIMER REGISTRO DE CONTROL pi_id_request, id_control_deploy: '
|| pi_id_request
|| ', '
|| v_new_id_ctrl);
------------------------------------------------------------------
-- inserta un nuevo registro de control con el numero de reintentos
INSERT INTO request_exec_deploy VALUES n_red;
log_collector(pi_program_name => log_program_name, pi_log_level => 1,
--- INFO
pi_log_message => 'Actualizacion first_dep_control: ' || pi_id_request);
-- Actualiza el estado de la Solicitud
UPDATE deployment_request r
SET r.request_status = 'En Proceso'
WHERE r.id_request = n_red.id_request;
SELECT ps.id_scope
INTO v_id_scope
FROM deployment_pattern_step ps
WHERE ps.id_pattern = n_red.id_pattern
AND ps.id_pattern_step = n_red.id_pattern_step;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'LUEGO DE CONSULTAR EL SCOPE - SCOPE: ' || v_id_scope);
------------------------------------------------------------------
-- Si afecta a multiples objetos
IF v_id_scope != 0 THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'DENTRO DEL IF DE SCOPE - n_red.id_request: '
|| n_red.id_request
|| ' - n_red.id_req_hgroup: '
|| n_red.id_req_hgroup
|| ' - n_red.id_object_repo: '
|| n_red.id_object_repo
|| ' - n_red.obj_order: '
|| n_red.obj_order);
------------------------------------------------------------------
FOR r_rdtx IN (
SELECT *
FROM deployment_req_object ro
WHERE ro.id_request = n_red.id_request
AND ro.id_req_hgroup LIKE n_red.id_req_hgroup || '%'
AND ro.id_object_repo = n_red.id_object_repo
AND ro.object_order > n_red.obj_order
ORDER BY ro.object_order
)
LOOP
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'DENTRO DEL LOOP DE HIJOS pi_id_request, r_rdtx.object_source_path, n_red.object_source_path: '
|| pi_id_request
|| ', '
|| r_rdtx.object_source_path
|| ', '
|| n_red.object_source_path);
------------------------------------------------------------------
IF r_rdtx.object_source_path = n_red.object_source_path THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'DENTRO DEL IF');
------------------------------------------------------------------
-- Inserta registros de control mientras los registros ordenados pertenezcan al mismo directorio
r_redx.id_req_hgroup := r_rdtx.id_req_hgroup;
r_redx.id_request := r_rdtx.id_request;
r_redx.id_user_control := pi_id_user;
r_redx.id_deployment_status := NULL;
r_redx.deployment_status := NULL;
r_redx.env_hgroup_order := n_red.env_hgroup_order;
r_redx.env_hgroup_members := n_red.env_hgroup_members;
r_redx.id_env_hgroup := n_red.id_env_hgroup;
r_redx.id_deploying_tech := n_red.id_deploying_tech;
r_redx.env_order := n_red.env_order;
r_redx.id_enviroment := n_red.id_enviroment;
r_redx.id_parent_enviroment := n_red.id_parent_enviroment;
r_redx.obj_hgroup_order := n_red.obj_hgroup_order;
r_redx.obj_hgroup_members := n_red.obj_hgroup_members;
r_redx.id_obj_hgroup := r_rdtx.id_req_hgroup;
r_redx.id_pattern := r_rdtx.id_pattern;
r_redx.id_pattern_step := n_red.id_pattern_step;
r_redx.obj_order := r_rdtx.object_order;
r_redx.id_object_repo := r_rdtx.id_object_repo;
r_redx.object_source_path := r_rdtx.object_source_path;
r_redx.object_name := r_rdtx.object_name;
r_redx.object_version := r_rdtx.object_version;
r_redx.id_control_deploy := s_request_exec_dep_id.nextval;
r_redx.id_executor := n_red.id_executor;
r_redx.retry_numer := n_red.retry_numer;
r_redx.control_init_date := sysdate;
r_redx.id_dep_req_object := r_rdtx.id_dep_req_object;
r_redx.id_parent_ctrl_deploy := n_red.id_control_deploy;
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'PRIMER GRUPO HIJO DE CONTROL pi_id_request, id_control_deploy: '
|| pi_id_request
|| ', '
|| r_redx.id_control_deploy);
------------------------------------------------------------------
-- inserta un nuevo registro de control
INSERT INTO request_exec_deploy VALUES r_redx;
ELSE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'DENTRO DEL ELSE');
------------------------------------------------------------------
n_red := r_redx;
EXIT;
END IF;
END LOOP;
END IF;
---
COMMIT;
---
-- Lanza la ejecucion del primer registro de Control
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'PRIMERA EJECUCION pi_id_request, id_control_deploy: '
|| pi_id_request
|| ', '
|| v_new_id_ctrl);
------------------------------------------------------------------
pkg_standard_executors.prc_dynamic_exec(pi_id_request => pi_id_request,
pi_id_control_deploy => v_new_id_ctrl,
--n_red.id_control_deploy,
pi_id_executor => n_red.id_executor);
--- INCLUIR AUDITORIA QUE CONTROLE QUE UN PROGRAMA HAYA INTENTADO ACTUALIZAR EL ESTATUS
--- Y EL REGISTRO YA SE ENCUENTRE CON UN ESTATUS PREVIO
--- PUEDE PASAR SOBRE TODO EN LOS TIMEOUT
---
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.first_dep_control - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - first_dep_control: ' || sqlerrm);
------------------------------------------------------------------
END first_dep_control;
/*--------------- DPCP6-------------------*/
PROCEDURE next_dep_control(
n_red IN OUT NOCOPY request_exec_deploy%rowtype
) AS
v_scope_type INTEGER := 0;
v_id_scope INTEGER := 0;
r_redx request_exec_deploy%rowtype;
v_sqlerrm VARCHAR2(500);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.next_dep_control';
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'Inicio next_dep_control: id_pattern, id_pattern_step : '
|| n_red.id_pattern
|| ', '
|| n_red.id_pattern_step);
------------------------------------------------------------------
SELECT ps.id_scope
INTO v_id_scope
FROM deployment_pattern_step ps
WHERE ps.id_pattern = n_red.id_pattern
AND ps.id_pattern_step = n_red.id_pattern_step;
-- inserta un nuevo registro de control con el numero de reintentos
n_red.id_deployment_status := NULL;
n_red.deployment_status := NULL;
n_red.id_prc_queue := NULL;
n_red.id_dep_proc := NULL;
n_red.control_init_date := sysdate;
n_red.control_end_date := NULL;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'SIGUIENTE REGISTRO DE CONTROL pi_id_request, id_control_deploy: '
|| n_red.id_request
|| ', '
|| n_red.id_control_deploy);
------------------------------------------------------------------
INSERT INTO request_exec_deploy VALUES n_red;
-- Si afecta a multiples objetos
IF v_id_scope != 0 THEN
FOR r_rdtx IN (
SELECT *
FROM deployment_req_object ro
WHERE ro.id_request = n_red.id_request
AND ro.id_req_hgroup LIKE n_red.id_req_hgroup || '%'
AND (ro.id_object_repo != n_red.id_object_repo
OR
ro.id_object_repo = n_red.id_object_repo
AND ro.object_name != n_red.object_name
)
AND ro.object_order > n_red.obj_order
ORDER BY ro.object_order
)
LOOP
IF r_rdtx.object_source_path = n_red.object_source_path THEN
-- Inserta registros de control mientras los registros ordenados pertenezcan al mismo directorio
r_redx.id_req_hgroup := n_red.id_req_hgroup;
r_redx.id_request := r_rdtx.id_request;
r_redx.id_user_control := n_red.id_user_control;
r_redx.id_deployment_status := NULL;
r_redx.deployment_status := NULL;
r_redx.env_hgroup_order := n_red.env_hgroup_order;
r_redx.env_hgroup_members := n_red.env_hgroup_members;
r_redx.id_env_hgroup := n_red.id_env_hgroup;
r_redx.id_deploying_tech := n_red.id_deploying_tech;
r_redx.env_order := n_red.env_order;
r_redx.id_enviroment := n_red.id_enviroment;
r_redx.id_parent_enviroment := n_red.id_parent_enviroment;
r_redx.obj_hgroup_order := n_red.obj_hgroup_order;
r_redx.obj_hgroup_members := n_red.obj_hgroup_members;
r_redx.id_obj_hgroup := r_rdtx.id_req_hgroup;
r_redx.id_pattern := r_rdtx.id_pattern;
r_redx.id_pattern_step := n_red.id_pattern_step;
r_redx.obj_order := r_rdtx.object_order;
r_redx.id_object_repo := r_rdtx.id_object_repo;
r_redx.object_source_path := r_rdtx.object_source_path;
r_redx.object_name := r_rdtx.object_name;
r_redx.object_version := r_rdtx.object_version;
r_redx.id_control_deploy := s_request_exec_dep_id.nextval;
r_redx.id_executor := n_red.id_executor;
r_redx.retry_numer := n_red.retry_numer;
r_redx.control_init_date := sysdate;
r_redx.id_dep_req_object := r_rdtx.id_dep_req_object;
r_redx.id_parent_ctrl_deploy := n_red.id_control_deploy;
---
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'SIGUIENTE REGISTRO DE CONTROL HIJO pi_id_request, id_control_deploy: '
|| r_redx.id_request
|| ', '
|| r_redx.id_control_deploy);
------------------------------------------------------------------
-- inserta un nuevo registro de control
INSERT INTO request_exec_deploy VALUES r_redx;
ELSE
n_red := r_redx;
EXIT;
END IF;
END LOOP;
END IF;
---
COMMIT;
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.next_dep_control - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - next_dep_control: ' || sqlerrm);
------------------------------------------------------------------
END next_dep_control;
/*--------------- DPCP7-------------------*/
PROCEDURE update_dep_control(
pi_id_request IN NUMBER,
pi_id_control_deploy IN NUMBER,
pi_id_prc_exec IN VARCHAR2,
pi_status IN NUMBER,
pi_exec_message IN VARCHAR2,
po_result OUT INTEGER
-- 0: Sin actualizaciones , 1: Con actualizaciones
) AS
v_parent_ctrl INTEGER := 0;
v_sqlerrm VARCHAR2(500);
v_request_status VARCHAR2(500);
v_id_action NUMBER;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.update_dep_control';
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- DEBUG
pi_log_message => 'ACTUALIZACION DEL CONTROL pi_id_request, id_control_deploy, pi_id_prc_exec, pi_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_prc_exec
|| ', '
|| pi_status);
------------------------------------------------------------------
-- Actualiza el registro de control devuelto por Jenkins
UPDATE request_exec_deploy ed
SET ed.id_deployment_status = pi_status,
ed.deployment_status = pkg_deploy_coordinator.step_status_result(ed.id_pattern, ed.id_pattern_step, pi_status),
ed.id_dep_proc = decode(pi_status, 0, pi_id_prc_exec, ed.id_dep_proc),
ed.control_init_date = (
CASE
WHEN pi_status = 0 THEN
sysdate
WHEN pi_status = -4 THEN
sysdate
ELSE
ed.control_init_date
END
),
ed.control_end_date = (
CASE
WHEN pi_status = 0 THEN
NULL
WHEN pi_status = -4 THEN
NULL
ELSE
sysdate
END
),
ed.observation = substr(utl_url.unescape(replace(pi_exec_message, '+', ' '), 'UTF-8'), 1, 4000)
WHERE ed.id_request = pi_id_request
-- AND nvl(ed.id_deployment_status, 0) = 0
AND ed.id_control_deploy = pi_id_control_deploy;
IF SQL%rowcount >= 1 THEN
po_result := 1;
COMMIT;
-- Actualiza los registros que posean el control como padre
UPDATE request_exec_deploy ed
SET ed.id_deployment_status = pi_status,
ed.deployment_status = pkg_deploy_coordinator.step_status_result(ed.id_pattern, ed.id_pattern_step, pi_status),
ed.id_dep_proc = decode(pi_status, 0, pi_id_prc_exec, ed.id_dep_proc),
ed.control_init_date = decode(pi_status, 0, sysdate, ed.control_init_date),
ed.control_end_date = decode(pi_status, 0, NULL, sysdate)
WHERE ed.id_request = pi_id_request
AND ed.id_parent_ctrl_deploy = pi_id_control_deploy;
-- Si encontro registros adicionales que actualizar
IF SQL%rowcount >= 1 THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- debug
pi_log_message => 'ACTUALIZACION DEL GRUPO CONTROL pi_id_request, id_parent_ctrl_deploy, pi_id_prc_exec, pi_status: '
|| pi_id_request
|| ', '
|| pi_id_control_deploy
|| ', '
|| pi_id_prc_exec
|| ', '
|| pi_status);
------------------------------------------------------------------
COMMIT;
END IF;
ELSE
po_result := 0;
END IF;
-- Actualiza el estado de la Solicitud
UPDATE deployment_request r
SET r.request_status = (fnc_request_status(pi_id_control_deploy))
WHERE r.id_request = pi_id_request;
COMMIT;
log_collector(pi_program_name => log_program_name,
pi_log_level => 4, --- DEBUG
pi_log_message => 'Actualización tabla deployment_request - antes de enviar el mail - Estado: ' || fnc_request_status(pi_id_control_deploy));
----Envia notificacion de mail de la actualizacion del estado de la solicitud
pkg_standard_executors.prc_send_deploy_status_message(
pi_id_request => pi_id_request,
pi_id_control_deploy => pi_id_control_deploy,
pi_observation => pi_exec_message,
pi_request_flow_message => 'REQUEST_DEPLOYMENT',--'REQUEST_WORKFLOW' / REQUEST_DEPLOYMENT */
pi_id_prc_exec => pi_id_prc_exec);
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.update_dep_control - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - update_dep_control: ' || sqlerrm);
------------------------------------------------------------------
END update_dep_control;
/*--------------- DPCP8-------------------*/
PROCEDURE execution_status(
pi_id_request IN NUMBER,
pi_environment_alias IN VARCHAR2,
pi_id_det_req IN NUMBER,
pi_id_prc_exec IN VARCHAR2,
pi_status IN NUMBER,
pi_exec_message IN VARCHAR2,
pi_exec_date IN VARCHAR2
) AS
v_warning VARCHAR2(1000);
v_result INTEGER := 0;
v_enviroment_alias VARCHAR2(100);
v_id_executor INTEGER := 0;
v_prg_executor VARCHAR2(100);
v_minutes_timeout INTEGER := 0;
v_seconds_timeout INTEGER := 0;
v_queue_timeout INTEGER := pkg_sicd_commons.get_parameter_value(param_name => 'JENKINS_QUEUE_ADD_TIMEOUT');
v_sqlerrm VARCHAR2(500);
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.execution_status';
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 1,
--- INFO
pi_log_message => 'REPORTE DE ESTADO pi_id_request, pi_id_det_req, pi_id_prc_exec, pi_status, pi_exec_message: '
|| pi_id_request
|| ', '
|| pi_id_det_req
|| ', '
|| pi_id_prc_exec
|| ', '
|| pi_status
|| ', '
|| pi_exec_message);
------------------------------------------------------------------
-- Recibe la comunicacion del Jenkins con el estado
update_dep_control(pi_id_request => pi_id_request,
pi_id_control_deploy => pi_id_det_req,
pi_id_prc_exec => pi_id_prc_exec,
pi_status => pi_status,
pi_exec_message => pi_exec_message,
po_result => v_result
-- 0: Sin actualizaciones , 1: Con actualizaciones
);
---
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'Salió de procedimiento update_dep_control - v_result: ' || v_result);
IF pi_status = 0 AND v_result = 1 THEN
-- Inicio de Procesamiento
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'JOB TIMEOUT DE COLA pi_id_request, pi_id_det_req, pi_id_prc_exec, pi_status: '
|| pi_id_request
|| ', '
|| pi_id_det_req
|| ', '
|| pi_id_prc_exec
|| ', '
|| pi_status);
------------------------------------------------------------------
SELECT e.enviroment_alias,
red.id_executor,
(
SELECT executor_program
FROM executor exe
WHERE exe.id_executor = red.id_executor
) AS executor_program,
ps.minutes_timeout,
ps.seconds_timeout
INTO v_enviroment_alias,
v_id_executor,
v_prg_executor,
v_minutes_timeout,
v_seconds_timeout
FROM request_exec_deploy red,
deployment_pattern_step ps,
enviroment e
WHERE ps.id_pattern = red.id_pattern
AND e.id_enviroment = red.id_enviroment
AND red.id_pattern_step = ps.id_pattern_step
AND e.enviroment_alias = pi_environment_alias
AND red.id_control_deploy = pi_id_det_req;
-- Se genera un JOB para el control de TIMEOUT configurado por para la cola de procesamiento
---***** HAY QUE CAMBIAR EL PROGRAMA, YA QUE ES UNO DESTINADO A LA NOTIFICACION ****---
/*sys.dbms_scheduler.create_job(
job_name => pi_id_det_req
|| '_'
|| v_prg_executor
|| '_PROCCESSING_TIMEOUT',
job_type => 'PLSQL_BLOCK',
start_date => sysdate +(v_minutes_timeout / 1440) +(v_seconds_timeout / 86400),
repeat_interval => NULL,
end_date => sysdate +(v_minutes_timeout / 1440) +((v_seconds_timeout + v_queue_timeout) / 86400),
auto_drop => true,
job_action => '
BEGIN
pkg_deploy_coordinator.execution_status(
pi_id_request => '
|| pi_id_request
|| ',
pi_environment_alias => '
|| v_enviroment_alias
|| ',
pi_id_det_req => '
|| pi_id_det_req
|| ',
pi_id_prc_exec => '
|| pi_id_prc_exec
|| ',
pi_status => - 3,
pi_exec_message => NULL,
pi_exec_date => sydate
);
END;',
comments => NULL
);*/
END IF;
---
IF v_result = 1 THEN
---
/* ------------------------------------------------------------------
log_collector(
pi_program_name => log_program_name,
pi_log_level => 3, --- DEBUG
pi_log_message => 'SIGUIENTE CICLO DE EJECUCION pi_id_request, pi_id_det_req, pi_id_prc_exec, pi_status: '
|| pi_id_request
|| ', '
|| pi_id_det_req
|| ', '
|| pi_id_prc_exec
|| ', '
|| pi_status
);
------------------------------------------------------------------*/
pkg_deploy_coordinator.automatic_dep_control(pi_id_request => pi_id_request,
pi_id_control_deploy => pi_id_det_req,
pi_id_status => pi_status,
pi_exec_message => NULL,
pi_exec_date => sysdate,
po_warning => v_warning);
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'Luego de llamar al procedimiento automatic_dep_control - ' || v_warning);
--- INCLUIR AUDITORIA QUE CONTROLE QUE UN PROGRAMA HAYA INTENTADO ACTUALIZAR EL ESTATUS
--- Y EL REGISTRO YA SE ENCUENTRE CON UN ESTATUS PREVIO
--- PUEDE PASAR SOBRE TODO EN LOS TIMEOUT
END IF;
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.execution_status - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - execution_status: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP9-------------------*/
PROCEDURE delete_references(
pi_id_request IN NUMBER,
pi_id_reference IN VARCHAR2,
po_warning OUT NOCOPY VARCHAR2
) AS
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.delete_references';
BEGIN
IF pi_id_reference IS NOT NULL THEN
DELETE FROM tmp_reference_list_add
WHERE id_reference = pi_id_reference
AND id_request = pi_id_request;
po_warning := pkg_sicd_commons.f_system_message(pi_id_message => 1, pi_message_class => 'DPCP9',
pi_return_type => 1);
END IF;
EXCEPTION
WHEN no_data_found THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP10-------------------*/
PROCEDURE manage_references(
pi_id_request IN NUMBER,
pi_id_reference IN VARCHAR2,
pi_id_reference_des IN VARCHAR2,
po_warning OUT NOCOPY VARCHAR2
) AS
v_reference NUMBER;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.manage_references';
BEGIN
IF pi_id_reference IS NOT NULL THEN
SELECT COUNT(1)
INTO v_reference
FROM tmp_reference_list_add t
WHERE t.id_request = pi_id_request
AND t.id_reference = pi_id_reference;
IF v_reference = 1 THEN
po_warning := pkg_sicd_commons.f_system_message(pi_id_message => 1, pi_message_class => 'DPCP10',
pi_return_type => 1);
ELSE
IF v_reference = 0 THEN
INSERT INTO tmp_reference_list_add (
id_reference,
id_request,
id_ref_source,
reference_description
)
VALUES (
pi_id_reference,
pi_id_request,
1,
pi_id_reference_des
);
po_warning := pkg_sicd_commons.f_system_message(pi_id_message => 2, pi_message_class => 'DPCP10',
pi_return_type => 1);
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.manage_references - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - manage_references: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP11-------------------*/
PROCEDURE manage_dep_queue(
pi_id_request IN NUMBER
) AS
v_start_date DATE;
v_id_queue INTEGER := 0;
v_id_schedule INTEGER := 0;
v_id_schedule_detail INTEGER := 0;
v_job_name VARCHAR2(100);
v_deploy_date DATE;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.manage_dep_queue';
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'INICIO - manage_dep_queue: ' || sqlerrm);
------------------------------------------------------------------
---
FOR sch IN (
SELECT aj.next_run_date,
sd.schedule_job_name,
s.id_schedule,
sd.id_schedule_detail
FROM all_scheduler_jobs aj,
deployment_schedule_detail sd,
deployment_schedule s,
deployment_request r
WHERE aj.job_name = sd.schedule_job_name
AND sd.id_schedule = s.id_schedule
AND s.id_deployment_type = r.id_deployment_type
AND s.id_enviroment_type = r.id_enviroment_type
AND r.id_request = pi_id_request
AND aj.next_run_date > sysdate
ORDER BY aj.start_date
) LOOP
v_start_date := sch.next_run_date;
v_job_name := sch.schedule_job_name;
v_id_schedule := sch.id_schedule;
v_id_schedule_detail := sch.id_schedule_detail;
EXIT;
END LOOP;
--------------------------------------------------
-- PROVISIONAL: EN ESPERA SE EJECUCION CON JOBS --
--------------------------------------------------
/* BEGIN
SELECT next_run_date, job_name
INTO v_start_date, v_job_name
FROM user_scheduler_jobs aj
WHERE next_run_date > sysdate
AND rownum = 1
ORDER BY next_run_date;
SELECT ID_SCHEDULE, ID_SCHEDULE_DETAIL
INTO v_id_schedule, v_id_schedule_detail
FROM DEPLOYMENT_SCHEDULE_DETAIL
WHERE SCHEDULE_JOB_NAME = v_job_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_start_date := sysdate;
v_job_name := 'J0000001';
v_id_schedule := 1;
v_id_schedule_detail := 1;
END;*/
--------------------------------------------------
-- PROVISIONAL: EN ESPERA SE EJECUCION CON JOBS --
--------------------------------------------------
v_id_queue := s_req_queue_id.nextval;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'INSERT - manage_dep_queue: ' || v_id_queue);
------------------------------------------------------------------
---
INSERT INTO deployment_request_queue (
id_queue,
id_request,
queue_date,
queue_order,
id_parent_queue,
id_schedule,
id_schedule_detail,
deploy_date,
queue_alias,
job_name
)
VALUES (
v_id_queue,
pi_id_request,
v_start_date,
nvl((
SELECT MAX(q.queue_order)
FROM deployment_request_queue q
WHERE q.id_schedule = v_id_schedule
AND q.id_schedule_detail = v_id_schedule_detail
AND q.status_queue = 0
AND q.queue_date >= to_date(sysdate,'DD/MM/YYYY')
),
0) + 1,
NULL,
v_id_schedule,
v_id_schedule_detail,
NULL,
to_char(v_start_date, 'YYYYMMDDHHMISS')
|| 'SC'
|| v_id_schedule_detail,
v_job_name
);
---
COMMIT;
-- END IF;
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 4,
--- DEBUG
pi_log_message => 'FIN - manage_dep_queue: ' || sqlerrm);
------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.manage_dep_queue - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - manage_dep_queue: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP12-------------------*/
PROCEDURE first_dep_queue(
pi_job_name IN VARCHAR2
) AS
r_red request_exec_deploy%rowtype;
r_drq deployment_request_queue%rowtype;
v_id_automatic_user NUMBER;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.first_dep_queue';
BEGIN
SELECT MIN(id_user)
INTO v_id_automatic_user
FROM USER_ROLES
WHERE ID_ROLE = '1';
-- Recorre todas las colas, seleccionando unicamente su primer registro
FOR r_drq IN (
SELECT q.*
FROM deployment_request_queue q
WHERE trunc(q.queue_date) = trunc(sysdate)
AND q.job_name = pi_job_name
AND q.queue_order = 1
AND q.status_queue = 0
)
LOOP
-- Incluye el primer registro de Control para cada cola e inicia el ciclo
pkg_deploy_coordinator.first_dep_control(pi_id_request => r_drq.id_request,
pi_id_user => v_id_automatic_user,
pi_queue_alias => r_drq.queue_alias,
n_red => r_red);
-- Actualiza el primer registro de la cola
UPDATE deployment_request_queue q
SET q.deploy_date = sysdate,
q.status_queue = 1
-- en proceso
WHERE q.id_queue = r_drq.id_queue;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.first_dep_queue - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - first_dep_queue: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP13-------------------*/
PROCEDURE next_dep_queue(
pi_id_request IN NUMBER DEFAULT 0,
pi_queue_alias IN VARCHAR2
) AS
r_red request_exec_deploy%rowtype;
r_drq deployment_request_queue%rowtype;
v_last_id_queue NUMBER := 0;
v_next_id_queue NUMBER := 0;
v_last_queue_order NUMBER := 0;
v_next_id_request NUMBER := 0;
v_id_automatic_user NUMBER;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.next_dep_queue';
BEGIN
SELECT MIN(id_user)
INTO v_id_automatic_user
FROM USER_ROLES
WHERE ID_ROLE = '1';
--- Busca el registro de la cola ejecutada
SELECT q.id_queue,
q.queue_order
INTO v_last_id_queue,
v_last_queue_order
FROM deployment_request_queue q
WHERE q.id_request = pi_id_request
AND q.queue_alias = pi_queue_alias;
-- Actualiza el estado de ejecucion
UPDATE deployment_request_queue q
SET q.status_queue = 2
-- terminado
WHERE q.id_queue = v_last_id_queue;
COMMIT;
--- busca la proxima ejecucion en secuencia
SELECT q.id_queue, q.id_request
INTO v_next_id_queue, v_next_id_request
FROM deployment_request_queue q
WHERE q.queue_order = v_last_queue_order +1
AND q.status_queue = 0
AND q.queue_alias = pi_queue_alias;
pkg_deploy_coordinator.first_dep_control(
pi_id_request => v_next_id_request,
pi_id_user => v_id_automatic_user,
pi_queue_alias => pi_queue_alias,
n_red => r_red
);
-- Actualiza el estado de ejecucion
UPDATE deployment_request_queue q
SET q.deploy_date = sysdate,
q.status_queue = 1
-- en proceso
WHERE q.id_queue = v_next_id_queue;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
apex_debug.error('pkg_deploy_coordinator.next_dep_queue - %s', sqlerrm);
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 2,
--- WARNING
pi_log_message => 'ERROR - next_dep_queue: ' || sqlerrm);
------------------------------------------------------------------
END;
/*--------------- DPCP14-------------------*/
PROCEDURE manage_dep_job(
pi_job_n_name IN VARCHAR2,
pi_job_o_name IN VARCHAR2,
pi_job_status IN NUMBER,
pi_freq IN NUMBER,
pi_start_date IN DATE
) AS
v_job_freq VARCHAR2(100);
v_end_date DATE := NULL;
v_auto_drop BOOLEAN := false;
v_day VARCHAR2(50);
v_hour VARCHAR2(50);
v_minute VARCHAR2(50);
v_second VARCHAR2(50);
v_boolean_sts BOOLEAN := false;
log_program_name VARCHAR2(100) := 'pkg_deploy_coordinator.manage_dep_job';
BEGIN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 1,
--- INFO
pi_log_message => 'INICIO ');
------------------------------------------------------------------
v_job_freq := pkg_deploy_coordinator.schedule_fequency_job(pi_schedule_fequency => pi_freq,
pi_schedule_init_date => pi_start_date);
---
IF pi_job_status = 1 THEN
v_boolean_sts := true;
ELSE
v_boolean_sts := false;
END IF;
/* -- Al crear el trabajo programado -- */
IF pi_job_o_name IS NULL THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- DEBUG
pi_log_message => 'NUEVO TRABAJO PROGRAMADO ' || pi_job_n_name);
------------------------------------------------------------------
/*
dbms_scheduler.create_job(job_name => pi_job_n_name, job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN pkg_deploy_coordinator.first_dep_queue(pi_job_name => '
|| pi_job_n_name
|| '); END;',
number_of_arguments => 0,
start_date => pi_start_date,
repeat_interval => v_job_freq,
end_date => v_end_date,
enabled => v_boolean_sts,
auto_drop => v_auto_drop,
comments => NULL);
*/
/* -- Al Cambiar la Frecuencia y Datos del Trabajo Programado -- */
ELSE
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 3,
--- DEBUG
pi_log_message => 'CAMBIO DEL TRABAJO PROGRAMADO '
|| pi_job_n_name
|| ' POR '
|| pi_job_o_name);
------------------------------------------------------------------
UPDATE deployment_request_queue drq
SET drq.job_name = pi_job_n_name,
drq.deploy_date = pi_start_date
WHERE drq.job_name = pi_job_o_name
AND drq.deploy_date >= sysdate;
BEGIN
NULL;--dbms_scheduler.drop_job(pi_job_o_name, false);
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 1,
--- INFO
pi_log_message => 'ERROR ELIMINADNO EL JOB '
|| sqlerrm
|| ' EN EL JOB '
|| pi_job_n_name);
------------------------------------------------------------------
END;
/*
dbms_scheduler.create_job(job_name => pi_job_n_name, job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN pkg_deploy_coordinator.first_dep_queue(pi_job_name => '
|| pi_job_n_name
|| '); END;',
number_of_arguments => 0,
start_date => pi_start_date,
repeat_interval => v_job_freq,
end_date => v_end_date,
enabled => v_boolean_sts,
auto_drop => v_auto_drop,
comments => NULL);
*/
END IF;
---
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
log_collector(pi_program_name => log_program_name, pi_log_level => 1,
--- INFO
pi_log_message => 'ERROR NO MENAJDO '
|| sqlerrm
|| ' EN EL JOB '
|| pi_job_n_name);
------------------------------------------------------------------
END manage_dep_job;
-----------------------------------------------------------------------
/*---------------DPCP15--------------------*/
PROCEDURE manage_req_object_order(
pi_id_dep_req_object IN NUMBER,
pi_id_req_hgroup IN VARCHAR2
) AS
v_aux NUMBER := 0;
BEGIN
DELETE FROM deployment_req_object
WHERE id_dep_req_object = pi_id_dep_req_object;
COMMIT;
FOR i IN (
SELECT *
FROM deployment_req_object
WHERE id_req_hgroup = pi_id_req_hgroup
--:P300_ID
ORDER BY object_order
)
LOOP
v_aux := v_aux + 1;
UPDATE deployment_req_object
SET object_order = v_aux
WHERE id_dep_req_object = i.id_dep_req_object;
END LOOP;
END;
-----------------------------------------------------------------------
/*---------------DPCP16--------------------*/
PROCEDURE manage_req_hgroup_order(
pi_id_request IN NUMBER,
pi_id_req_hgroup_level IN VARCHAR2,
pi_id_parent_req_hgroup IN VARCHAR2
) AS
i NUMBER := 0;
BEGIN
FOR hg_ord IN (
SELECT id_req_hgroup
FROM deployment_req_hgroup hg
WHERE hg.id_request = pi_id_request
AND hg.id_req_hgroup_level = pi_id_req_hgroup_level
AND hg.id_parent_req_hgroup = pi_id_parent_req_hgroup
ORDER BY req_hgroup_order
)
LOOP
i := i + 1;
UPDATE deployment_req_hgroup
SET req_hgroup_order = i
WHERE id_req_hgroup = hg_ord.id_req_hgroup;
END LOOP;
COMMIT;
END manage_req_hgroup_order;
/*---------------DPCP17--------------------*/
PROCEDURE p_genera_job(
pi_job_nombre IN VARCHAR2,
pi_job_accion IN VARCHAR2,
pi_job_tipo_frecuencia IN NUMBER,
pi_job_fecha_inicio IN TIMESTAMP,
po_estado OUT NUMBER
) AS
PRAGMA autonomous_transaction;
v_estado NUMBER;
v_job_frecuencia NUMBER := 1;
v_intervalo_repeticion VARCHAR2(500);
BEGIN
--------------------------------------------
-- Se calcula el Intervalo de Repeticion ---
CASE
WHEN pi_job_tipo_frecuencia = 1 THEN
v_intervalo_repeticion := 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;';
WHEN pi_job_tipo_frecuencia = 2 THEN
v_intervalo_repeticion := 'FREQ=DAILY;';
WHEN pi_job_tipo_frecuencia = 3 THEN
v_intervalo_repeticion := 'FREQ=WEEKLY;';
WHEN pi_job_tipo_frecuencia = 4 THEN
v_intervalo_repeticion := 'FREQ=DAILY;INTERVAL=30;';
WHEN pi_job_tipo_frecuencia = 5 THEN
v_intervalo_repeticion := 'FREQ=MONTHLY;bymonthday=1;';
WHEN pi_job_tipo_frecuencia = 6 THEN
v_intervalo_repeticion := 'FREQ=MONTHLY;bymonthday=-1;';
ELSE
v_intervalo_repeticion := '';
END CASE;
-- Se calcula el Intervalo de Repeticion ---
--------------------------------------------
----------------------------------------------
-- CREACION DEL JOB CON LOS DATOS INDICADOS --
dbms_scheduler.create_job(job_name => pi_job_nombre,
job_action => pi_job_accion,
start_date => pi_job_fecha_inicio,
job_type => 'plsql_block',
repeat_interval => v_intervalo_repeticion,
enabled => false);
-- CREACION DEL JOB CON LOS DATOS INDICADOS --
----------------------------------------------
dbms_scheduler.enable(pi_job_nombre);
END;
/*---------------DPCP18--------------------*/
PROCEDURE p_elimina_job(
pi_nombre_job IN OUT CLOB
) AS
PRAGMA autonomous_transaction;
BEGIN
-- Desactiva el JOB --
dbms_scheduler.disable(name => pi_nombre_job);
-- Elimina el JOB existente --
dbms_scheduler.drop_job(job_name => pi_nombre_job);
END;
-----------------------------------------------------------------------
END pkg_deploy_coordinator;
/
Reference in New Issue View Git Blame Copy Permalink