907 lines
34 KiB
MySQL
907 lines
34 KiB
MySQL
|
--------------------------------------------------------
|
||
|
|
-- DDL for Package Body PKG_WORKFLOW_COORDINATOR
|
||
|
|
--------------------------------------------------------
|
||
|
|
|
||
|
|
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "ENVX_OPER"."PKG_WORKFLOW_COORDINATOR" AS
|
||
|
|
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
/*---------------------------- FUNCTIONS ----------------------------------*/
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
FUNCTION chg_prof_h (
|
||
|
|
pi_id_sys_profile IN NUMBER,
|
||
|
|
pi_id_sup_sys_profile IN NUMBER,
|
||
|
|
pi_id_role IN NUMBER
|
||
|
|
) RETURN NUMBER AS
|
||
|
|
v_aux_profile NUMBER := 0;
|
||
|
|
BEGIN
|
||
|
|
SELECT UNIQUE 0
|
||
|
|
INTO v_aux_profile
|
||
|
|
FROM (
|
||
|
|
SELECT sp.id_sys_profile
|
||
|
|
FROM system_profile sp
|
||
|
|
WHERE sp.id_role = pi_id_role START WITH
|
||
|
|
sp.id_sys_profile = pi_id_sys_profile
|
||
|
|
CONNECT BY
|
||
|
|
PRIOR sp.id_sys_profile = sp.id_sup_sys_profile
|
||
|
|
) v
|
||
|
|
WHERE v.id_sys_profile = pi_id_sup_sys_profile;
|
||
|
|
|
||
|
|
RETURN 0;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.chg_prof_h - %s', sqlerrm);
|
||
|
|
RETURN 1;
|
||
|
|
END;
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
FUNCTION chg_env_dir (
|
||
|
|
pi_id_enviroment IN NUMBER,
|
||
|
|
pi_id_parent_enviroment IN NUMBER,
|
||
|
|
pi_id_enviroment_type IN NUMBER
|
||
|
|
) RETURN NUMBER AS
|
||
|
|
v_aux_enviroment NUMBER := 0;
|
||
|
|
BEGIN
|
||
|
|
SELECT UNIQUE 0
|
||
|
|
INTO v_aux_enviroment
|
||
|
|
FROM (
|
||
|
|
SELECT e.id_enviroment
|
||
|
|
FROM enviroment e
|
||
|
|
WHERE e.id_enviroment = pi_id_enviroment START WITH
|
||
|
|
e.id_enviroment = pi_id_enviroment
|
||
|
|
CONNECT BY
|
||
|
|
PRIOR e.id_enviroment = e.id_parent_enviroment
|
||
|
|
) v
|
||
|
|
WHERE v.id_enviroment = pi_id_parent_enviroment;
|
||
|
|
|
||
|
|
RETURN 0;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.chg_env_dir - %s', sqlerrm);
|
||
|
|
RETURN 1;
|
||
|
|
END;
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
FUNCTION next_step_flow (
|
||
|
|
pi_id_action IN NUMBER,
|
||
|
|
pi_id_step_flow IN NUMBER
|
||
|
|
) RETURN NUMBER AS
|
||
|
|
v_result INTEGER := 0;
|
||
|
|
v_order INTEGER := 0;
|
||
|
|
v_id_req_flow INTEGER := 0;
|
||
|
|
v_id_action_category INTEGER := 0;
|
||
|
|
v_step_deploy INTEGER := 0;
|
||
|
|
BEGIN
|
||
|
|
---------------------------------------------
|
||
|
|
SELECT step_order,
|
||
|
|
id_req_flow,
|
||
|
|
(
|
||
|
|
SELECT id_action_cat
|
||
|
|
FROM request_action_flow ra
|
||
|
|
WHERE ra.id_action = pi_id_action
|
||
|
|
AND ra.id_step_flow = rf.id_step_flow
|
||
|
|
) AS id_action_cat,
|
||
|
|
rf.deploy_enable
|
||
|
|
INTO v_order,
|
||
|
|
v_id_req_flow,
|
||
|
|
v_id_action_category,
|
||
|
|
v_step_deploy
|
||
|
|
FROM request_step_workflow rf
|
||
|
|
WHERE rf.id_step_flow = pi_id_step_flow;
|
||
|
|
|
||
|
|
IF v_order = 1 AND nvl(v_id_action_category, 0) = 0 THEN
|
||
|
|
v_id_action_category := 1;
|
||
|
|
END IF;
|
||
|
|
---------------------------------------------
|
||
|
|
CASE v_id_action_category
|
||
|
|
|
||
|
|
/*- NEXT STEP ---*/
|
||
|
|
WHEN 1 THEN
|
||
|
|
SELECT MIN(id_step_flow)
|
||
|
|
INTO v_result
|
||
|
|
FROM request_step_workflow
|
||
|
|
WHERE id_req_flow = v_id_req_flow
|
||
|
|
AND step_order > v_order
|
||
|
|
AND step_status = 1;
|
||
|
|
|
||
|
|
/*- PREVIUS STEP ---*/
|
||
|
|
WHEN 2 THEN
|
||
|
|
SELECT MAX(id_step_flow)
|
||
|
|
INTO v_result
|
||
|
|
FROM request_step_workflow
|
||
|
|
WHERE id_req_flow = v_id_req_flow
|
||
|
|
AND step_order < v_order
|
||
|
|
AND step_status = 1;
|
||
|
|
|
||
|
|
/*- GO TO STEP ---*/
|
||
|
|
WHEN 3 THEN
|
||
|
|
SELECT id_step_go
|
||
|
|
INTO v_result
|
||
|
|
FROM request_action_flow ra
|
||
|
|
WHERE ra.id_action = pi_id_action
|
||
|
|
AND ra.id_step_flow = pi_id_step_flow;
|
||
|
|
|
||
|
|
/*- GO TO BEGIN ---*/
|
||
|
|
WHEN 4 THEN
|
||
|
|
SELECT MIN(id_step_flow)
|
||
|
|
INTO v_result
|
||
|
|
FROM request_step_workflow
|
||
|
|
WHERE id_req_flow = v_id_req_flow
|
||
|
|
AND step_status = 1;
|
||
|
|
/*- PAUSE --- */
|
||
|
|
WHEN 5 THEN
|
||
|
|
v_result := pi_id_step_flow;
|
||
|
|
|
||
|
|
/*- ABORT ---*/
|
||
|
|
WHEN 6 THEN
|
||
|
|
v_result := -1;
|
||
|
|
|
||
|
|
/*- END ---*/
|
||
|
|
WHEN 7 THEN
|
||
|
|
v_result := 0;
|
||
|
|
|
||
|
|
/*- ERROR ---*/
|
||
|
|
ELSE
|
||
|
|
IF v_step_deploy = 1 THEN
|
||
|
|
--- si es un paso de despliegue, finaliza el workflow
|
||
|
|
v_result := 0;
|
||
|
|
ELSE
|
||
|
|
v_result := -2;
|
||
|
|
END IF;
|
||
|
|
END CASE;
|
||
|
|
---------------------------------------------
|
||
|
|
apex_debug.info('pkg_workflow_coordinator.next_step_flow - v_result: %s', v_result);
|
||
|
|
RETURN v_result;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.next_step_flow - %s', sqlerrm);
|
||
|
|
END;
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
FUNCTION next_step_category_b (
|
||
|
|
pi_id_action_category IN NUMBER
|
||
|
|
) RETURN BOOLEAN AS
|
||
|
|
v_result INTEGER := 0;
|
||
|
|
v_order INTEGER := 0;
|
||
|
|
v_id_req_flow INTEGER := 0;
|
||
|
|
BEGIN
|
||
|
|
---------------------------------------------
|
||
|
|
CASE pi_id_action_category
|
||
|
|
/*- NEXT STEP ---*/
|
||
|
|
WHEN 1 THEN
|
||
|
|
RETURN true;
|
||
|
|
/*- PREVIUS STEP ---*/
|
||
|
|
WHEN 2 THEN
|
||
|
|
RETURN true;
|
||
|
|
/*- GO TO STEP --- */
|
||
|
|
WHEN 3 THEN
|
||
|
|
RETURN true;
|
||
|
|
/*- GO TO BEGIN --- */
|
||
|
|
WHEN 4 THEN
|
||
|
|
RETURN true;
|
||
|
|
/*- PAUSE --- */
|
||
|
|
WHEN 5 THEN
|
||
|
|
RETURN true;
|
||
|
|
/*- ABORT ---*/
|
||
|
|
WHEN 6 THEN
|
||
|
|
RETURN false;
|
||
|
|
/*- END ---*/
|
||
|
|
WHEN 7 THEN
|
||
|
|
RETURN false;
|
||
|
|
/*- ERROR ---*/
|
||
|
|
ELSE
|
||
|
|
RETURN false;
|
||
|
|
END CASE;
|
||
|
|
---------------------------------------------
|
||
|
|
END;
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
/*--------------------------- PROCEDURES ----------------------------------*/
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
PROCEDURE change_step_order (
|
||
|
|
pi_id_step_flow IN NUMBER,
|
||
|
|
pi_new_position IN VARCHAR2,
|
||
|
|
po_new_order OUT NUMBER,
|
||
|
|
po_warning OUT NOCOPY VARCHAR2
|
||
|
|
) IS
|
||
|
|
v_id_req_flow INTEGER := 0;
|
||
|
|
v_actual_order INTEGER := 0;
|
||
|
|
v_auxid_step_flow INTEGER := 0;
|
||
|
|
v_max_order INTEGER := 0;
|
||
|
|
BEGIN
|
||
|
|
---------------------------------------------
|
||
|
|
SELECT r.id_req_flow,
|
||
|
|
r.step_order
|
||
|
|
INTO v_id_req_flow,
|
||
|
|
v_actual_order
|
||
|
|
FROM tmp_request_step_workflow r
|
||
|
|
WHERE r.id_step_flow = pi_id_step_flow;
|
||
|
|
|
||
|
|
---------------------------------------------
|
||
|
|
IF upper(v_actual_order) = 'UP' THEN
|
||
|
|
---------------------------------------------
|
||
|
|
IF v_actual_order = 1 THEN
|
||
|
|
po_warning := 'Ya se encuentra en la primera posicion';
|
||
|
|
ELSE
|
||
|
|
po_new_order := v_actual_order + 1;
|
||
|
|
/*
|
||
|
|
--- sube una posicion ---
|
||
|
|
UPDATE tmp_request_step_workflow rsf
|
||
|
|
SET
|
||
|
|
rsf.step_order = v_actual_order
|
||
|
|
WHERE rsf.id_req_flow = v_id_req_flow
|
||
|
|
AND rsf.step_order = v_actual_order - 1;
|
||
|
|
---------------------------------------------
|
||
|
|
UPDATE tmp_request_step_workflow rsf
|
||
|
|
SET
|
||
|
|
rsf.step_order = v_actual_order - 1
|
||
|
|
WHERE rsf.id_step_flow = pi_id_step_flow;
|
||
|
|
---------------------------------------------*/
|
||
|
|
END IF;
|
||
|
|
---------------------------------------------
|
||
|
|
ELSIF upper(pi_new_position) = 'DOWN' THEN
|
||
|
|
--- baja una posicion ---
|
||
|
|
SELECT MAX(r.step_order)
|
||
|
|
INTO v_max_order
|
||
|
|
FROM tmp_request_step_workflow r
|
||
|
|
WHERE r.id_step_flow = pi_id_step_flow;
|
||
|
|
|
||
|
|
po_new_order := v_actual_order - 1;
|
||
|
|
/*IF v_actual_order = v_max_order THEN
|
||
|
|
po_warning := 'Ya se encuentra en la ultima posicion';
|
||
|
|
ELSE
|
||
|
|
---------------------------------------------
|
||
|
|
UPDATE tmp_request_step_workflow rsf
|
||
|
|
SET
|
||
|
|
rsf.step_order = v_actual_order
|
||
|
|
WHERE rsf.id_req_flow = v_id_req_flow
|
||
|
|
AND rsf.step_order = v_actual_order + 1;
|
||
|
|
---------------------------------------------
|
||
|
|
UPDATE tmp_request_step_workflow rsf
|
||
|
|
SET
|
||
|
|
rsf.step_order = v_actual_order + 1
|
||
|
|
WHERE rsf.id_step_flow = pi_id_step_flow;
|
||
|
|
---------------------------------------------
|
||
|
|
END IF;*/
|
||
|
|
ELSE
|
||
|
|
NULL;
|
||
|
|
END IF;
|
||
|
|
---------------------------------------------
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.change_step_order - %s', sqlerrm);
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
PROCEDURE change_status_flow (
|
||
|
|
pi_id_req_flow IN NUMBER,
|
||
|
|
pi_new_status_flow IN NUMBER,
|
||
|
|
po_result OUT NUMBER
|
||
|
|
) AS
|
||
|
|
v_id_deployment_type NUMBER := 0;
|
||
|
|
v_id_enviroment_type NUMBER := 0;
|
||
|
|
BEGIN
|
||
|
|
--- permite que solo un flujo este activo por tipo de ambiente y tipo de despliegue ---
|
||
|
|
IF pi_new_status_flow = 1 THEN
|
||
|
|
-------------------------------------------------
|
||
|
|
SELECT wfl.id_deployment_type,
|
||
|
|
wfl.id_enviroment_type
|
||
|
|
INTO v_id_deployment_type,
|
||
|
|
v_id_enviroment_type
|
||
|
|
FROM request_workflow wfl
|
||
|
|
WHERE wfl.id_req_flow = pi_id_req_flow;
|
||
|
|
-------------------------------------------------
|
||
|
|
UPDATE request_workflow
|
||
|
|
SET request_flow_status = 0
|
||
|
|
WHERE id_deployment_type = v_id_deployment_type
|
||
|
|
AND id_enviroment_type = v_id_enviroment_type
|
||
|
|
AND id_req_flow != pi_id_req_flow;
|
||
|
|
-------------------------------------------------
|
||
|
|
COMMIT;
|
||
|
|
po_result := 1;
|
||
|
|
ELSE
|
||
|
|
NULL;
|
||
|
|
END IF;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.change_status_flow - %s', sqlerrm);
|
||
|
|
po_result := -1;
|
||
|
|
END;
|
||
|
|
-----------------------------------------------------------------------*/
|
||
|
|
PROCEDURE change_status_step (
|
||
|
|
pi_id_step_flow IN NUMBER,
|
||
|
|
pi_new_status_flow IN NUMBER,
|
||
|
|
po_result OUT NUMBER
|
||
|
|
) AS
|
||
|
|
BEGIN
|
||
|
|
IF pi_new_status_flow = 1 THEN
|
||
|
|
NULL;
|
||
|
|
ELSE
|
||
|
|
--- si se desactiva un paso, se desactivan todas sus acciones asociadas ---
|
||
|
|
UPDATE request_action_flow
|
||
|
|
SET action_status = 0
|
||
|
|
WHERE id_step_go = pi_id_step_flow;
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.change_status_step - %s', sqlerrm);
|
||
|
|
END;
|
||
|
|
-----------------------------------------------------------------------*/
|
||
|
|
PROCEDURE change_status_act (
|
||
|
|
pi_id_action IN NUMBER,
|
||
|
|
pi_new_status_flow IN NUMBER
|
||
|
|
) AS
|
||
|
|
BEGIN
|
||
|
|
NULL;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.change_status_act - %s', sqlerrm);
|
||
|
|
END;
|
||
|
|
-----------------------------------------------------------------------*/
|
||
|
|
PROCEDURE manage_enviroment_group (
|
||
|
|
pi_id_preset_group 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;
|
||
|
|
BEGIN
|
||
|
|
SELECT e.deploy_enable,
|
||
|
|
e.enviroment_status AS env_enable,
|
||
|
|
e.enviroment_name
|
||
|
|
|| ' ('
|
||
|
|
|| e.enviroment_alias
|
||
|
|
|| ') ',
|
||
|
|
nvl((
|
||
|
|
SELECT 1
|
||
|
|
FROM preset_env_group_members m
|
||
|
|
WHERE m.id_enviroment = e.id_enviroment
|
||
|
|
AND m.id_preset_group = pi_id_preset_group
|
||
|
|
), 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_deploy_enable = 1 AND v_enviroment_status = 1 AND v_enviroment_set = 0 THEN
|
||
|
|
BEGIN
|
||
|
|
INSERT INTO tmp_members_group (
|
||
|
|
id_preset_group,
|
||
|
|
id_enviroment
|
||
|
|
) VALUES (
|
||
|
|
pi_id_preset_group,
|
||
|
|
pi_id_enviroment
|
||
|
|
);
|
||
|
|
---
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 1,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
---
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
DELETE tmp_members_group
|
||
|
|
WHERE id_enviroment = pi_id_enviroment
|
||
|
|
AND id_preset_group = pi_id_preset_group;
|
||
|
|
---
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 2,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
|
||
|
|
END;
|
||
|
|
|
||
|
|
ELSIF v_deploy_enable = 0 THEN
|
||
|
|
---
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 3,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
ELSIF v_enviroment_status = 0 THEN
|
||
|
|
---
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 4,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
ELSIF v_enviroment_set = 1 THEN
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 7,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
ELSE
|
||
|
|
---
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 5,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
---
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.manage_enviroment_group - %s', sqlerrm);
|
||
|
|
po_warning := v_enviroment_alias
|
||
|
|
|| pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => 5,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 1
|
||
|
|
);
|
||
|
|
|
||
|
|
END;
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
PROCEDURE exec_action_workflow (
|
||
|
|
pi_id_request IN NUMBER,
|
||
|
|
pi_id_action IN NUMBER,
|
||
|
|
pi_user_name IN VARCHAR2,
|
||
|
|
pi_id_next_user IN NUMBER,
|
||
|
|
pi_observations IN VARCHAR2,
|
||
|
|
po_warning OUT NOCOPY VARCHAR2
|
||
|
|
) IS
|
||
|
|
|
||
|
|
--- PRAGMA autonomous_transaction;
|
||
|
|
v_actual_step NUMBER := 0;
|
||
|
|
v_actual_seq NUMBER := 0;
|
||
|
|
r_ref request_exec_flow%rowtype;
|
||
|
|
r_reff request_exec_flow%rowtype;
|
||
|
|
v_action_name VARCHAR2(100);
|
||
|
|
v_action_category NUMBER := 0;
|
||
|
|
v_next_step NUMBER := 0;
|
||
|
|
v_action_status VARCHAR2(100);
|
||
|
|
v_id_role NUMBER := 0;
|
||
|
|
v_id_sys_profile NUMBER := 0;
|
||
|
|
v_id_user NUMBER := 0;
|
||
|
|
v_n_id_role NUMBER := 0;
|
||
|
|
v_n_id_sys_profile NUMBER := 0;
|
||
|
|
v_nn_id_sys_profile NUMBER := 0;
|
||
|
|
v_deploy_step NUMBER := 0;
|
||
|
|
v_notification CLOB;
|
||
|
|
v_id_user_siguiente NUMBER(38, 0);
|
||
|
|
v_subject VARCHAR2(4000);
|
||
|
|
po_response_code VARCHAR2(4000);
|
||
|
|
po_error_message VARCHAR2(4000);
|
||
|
|
v_message_type NUMBER := 0;
|
||
|
|
v_enviroment_type NUMBER := 0;
|
||
|
|
v_deployment_type NUMBER := 0;
|
||
|
|
v_action_cat NUMBER := 0;
|
||
|
|
log_program_name VARCHAR2(100) := 'pkg_workflow_coordinator.exec_action_workflow';
|
||
|
|
BEGIN
|
||
|
|
---
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4,
|
||
|
|
--- DEBUG
|
||
|
|
pi_log_message => 'INICIO - '
|
||
|
|
|| log_program_name
|
||
|
|
|| ': '
|
||
|
|
|| sysdate
|
||
|
|
|| ' ID_REQUEST: '
|
||
|
|
|| pi_id_request
|
||
|
|
|| ' ID_ACTION: '
|
||
|
|
|| pi_id_action
|
||
|
|
|| ' ID_USER: '
|
||
|
|
|| pi_user_name
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
|
||
|
|
--- Verifica que solo un usuario pueda ejecutar el paso del flujo ---
|
||
|
|
BEGIN
|
||
|
|
----------------------------------------------------------------
|
||
|
|
--- Busca el ultimo paso ejecutado en el flujo ---
|
||
|
|
----------------------------------------------------------------
|
||
|
|
SELECT exec.*
|
||
|
|
INTO r_ref
|
||
|
|
FROM request_exec_flow exec
|
||
|
|
WHERE exec.id_request = pi_id_request
|
||
|
|
AND exec.status_result IS NULL;
|
||
|
|
|
||
|
|
v_actual_seq := r_ref.id_seq_step;
|
||
|
|
---
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
SELECT nvl(MAX(id_seq_step), 1)
|
||
|
|
INTO v_actual_seq
|
||
|
|
FROM request_exec_flow exec
|
||
|
|
WHERE exec.id_request = pi_id_request
|
||
|
|
AND exec.id_seq_step = 1;
|
||
|
|
|
||
|
|
END;
|
||
|
|
|
||
|
|
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4,
|
||
|
|
--- DEBUG
|
||
|
|
pi_log_message => 'valor de v_actual_seq: '
|
||
|
|
|| v_actual_seq
|
||
|
|
);
|
||
|
|
---
|
||
|
|
--- Describe el paso y accion ejecutado ---
|
||
|
|
SELECT action_name,
|
||
|
|
id_action_cat,
|
||
|
|
id_step_flow,
|
||
|
|
id_next_step,
|
||
|
|
status_result,
|
||
|
|
deploy_enable,
|
||
|
|
id_role,
|
||
|
|
id_sys_profile,
|
||
|
|
id_user
|
||
|
|
INTO v_action_name,
|
||
|
|
v_action_category,
|
||
|
|
v_actual_step,
|
||
|
|
v_next_step,
|
||
|
|
v_action_status,
|
||
|
|
v_deploy_step,
|
||
|
|
v_id_role,
|
||
|
|
v_id_sys_profile,
|
||
|
|
v_id_user
|
||
|
|
FROM request_flow_action
|
||
|
|
WHERE id_action = pi_id_action
|
||
|
|
AND user_name = pi_user_name
|
||
|
|
AND id_request = pi_id_request;
|
||
|
|
|
||
|
|
-------------------------------
|
||
|
|
|
||
|
|
----------------------------------------
|
||
|
|
IF v_actual_seq != 1 THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4,
|
||
|
|
--- DEBUG
|
||
|
|
pi_log_message => 'SIGUIENTE SEQ - '
|
||
|
|
|| log_program_name
|
||
|
|
|| ': '
|
||
|
|
|| sysdate
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
---- No:
|
||
|
|
-- actualiza el estado de ejecucion
|
||
|
|
-- sin estado de ejecucion
|
||
|
|
UPDATE request_exec_flow e
|
||
|
|
SET e.id_user = v_id_user,
|
||
|
|
e.user_name = pi_user_name,
|
||
|
|
e.id_action = pi_id_action,
|
||
|
|
e.action_name = v_action_name,
|
||
|
|
e.id_action_cat = v_action_category,
|
||
|
|
e.status_result = v_action_status,
|
||
|
|
e.last_step_date = sysdate,
|
||
|
|
e.observations = pi_observations
|
||
|
|
WHERE e.id_request = pi_id_request
|
||
|
|
AND e.id_step_flow = v_actual_step
|
||
|
|
AND e.status_result IS NULL;
|
||
|
|
|
||
|
|
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4,
|
||
|
|
--- DEBUG
|
||
|
|
pi_log_message => 'PRIMERA SEQ - '
|
||
|
|
|| log_program_name
|
||
|
|
|| ': '
|
||
|
|
|| sysdate
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
---- Si:
|
||
|
|
-- crear el primer paso en la ejecucion
|
||
|
|
-- asigna el estado de ejecucion
|
||
|
|
---
|
||
|
|
INSERT INTO request_exec_flow (
|
||
|
|
id_request,
|
||
|
|
id_seq_step,
|
||
|
|
action_name,
|
||
|
|
id_action_cat,
|
||
|
|
status_result,
|
||
|
|
id_step_flow,
|
||
|
|
id_role,
|
||
|
|
id_sys_profile,
|
||
|
|
id_user,
|
||
|
|
id_action,
|
||
|
|
observations,
|
||
|
|
step_date,
|
||
|
|
update_data,
|
||
|
|
delete_data,
|
||
|
|
last_step_date,
|
||
|
|
user_name
|
||
|
|
) VALUES (
|
||
|
|
pi_id_request,
|
||
|
|
v_actual_seq,
|
||
|
|
v_action_name,
|
||
|
|
v_action_category,
|
||
|
|
v_action_status,
|
||
|
|
v_actual_step,
|
||
|
|
v_id_role,
|
||
|
|
v_id_sys_profile,
|
||
|
|
v_id_user,
|
||
|
|
pi_id_action,
|
||
|
|
pi_observations,
|
||
|
|
sysdate,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
sysdate,
|
||
|
|
pi_user_name
|
||
|
|
);
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
---
|
||
|
|
UPDATE deployment_request r
|
||
|
|
SET r.id_last_seq_step = v_actual_seq,
|
||
|
|
r.request_status = v_action_status
|
||
|
|
WHERE r.id_request = pi_id_request;
|
||
|
|
--- Si no es el Paso de Despliegue genera un nuevo registro de control
|
||
|
|
|
||
|
|
IF v_deploy_step != 1 OR v_action_category NOT IN (
|
||
|
|
6, 7
|
||
|
|
) THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => 'ACCION REGULAR - '
|
||
|
|
|| log_program_name
|
||
|
|
|| ': '
|
||
|
|
|| sysdate
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
-- Busqueda del proximo perfil a realizar acciones ---
|
||
|
|
SELECT step.id_sys_profile,
|
||
|
|
(
|
||
|
|
SELECT p.id_role
|
||
|
|
FROM system_profile p
|
||
|
|
WHERE p.id_sys_profile = step.id_sys_profile
|
||
|
|
) AS id_role
|
||
|
|
INTO v_n_id_sys_profile,
|
||
|
|
v_n_id_role
|
||
|
|
FROM request_step_workflow step,
|
||
|
|
request_workflow flow
|
||
|
|
WHERE flow.id_req_flow = step.id_req_flow
|
||
|
|
AND step.id_step_flow = v_next_step;
|
||
|
|
-- Busqueda del proximo perfil a realizar acciones ---
|
||
|
|
----------------------------------------------------------------
|
||
|
|
|
||
|
|
|
||
|
|
--- insertar el siguiente paso a ejecutar
|
||
|
|
----------------------------------------------------------------
|
||
|
|
v_actual_seq := v_actual_seq + 1;
|
||
|
|
INSERT INTO request_exec_flow (
|
||
|
|
id_request,
|
||
|
|
id_seq_step,
|
||
|
|
action_name,
|
||
|
|
id_action_cat,
|
||
|
|
status_result,
|
||
|
|
id_step_flow,
|
||
|
|
id_role,
|
||
|
|
id_sys_profile,
|
||
|
|
id_user,
|
||
|
|
id_action,
|
||
|
|
observations,
|
||
|
|
step_date,
|
||
|
|
update_data,
|
||
|
|
delete_data,
|
||
|
|
last_step_date,
|
||
|
|
user_name
|
||
|
|
) VALUES (
|
||
|
|
pi_id_request,
|
||
|
|
v_actual_seq,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
v_next_step,
|
||
|
|
v_n_id_role,
|
||
|
|
v_n_id_sys_profile,
|
||
|
|
pi_id_next_user,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
sysdate,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
COMMIT;
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
---Obtiene la el id de la categoria ejecutada
|
||
|
|
SELECT id_action_cat
|
||
|
|
INTO v_action_cat
|
||
|
|
FROM request_exec_flow
|
||
|
|
WHERE
|
||
|
|
id_request = pi_id_request
|
||
|
|
AND id_seq_step = (
|
||
|
|
SELECT
|
||
|
|
MAX(exec.id_seq_step)
|
||
|
|
FROM
|
||
|
|
request_exec_flow exec
|
||
|
|
WHERE
|
||
|
|
exec.id_request = pi_id_request
|
||
|
|
AND exec.status_result IS NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CASE
|
||
|
|
|
||
|
|
when v_action_cat in(1,3) then
|
||
|
|
---Si la accion elegida implica un paso hacia adelante en el proceso
|
||
|
|
---envia notificacion al id_sys_profile del paso siguiente.
|
||
|
|
|
||
|
|
pkg_standard_executors.prc_send_action_message (
|
||
|
|
pi_id_action => pi_id_action,
|
||
|
|
pi_id_request => pi_id_request,
|
||
|
|
pi_observation => pi_observations,
|
||
|
|
pi_request_status => v_action_status,
|
||
|
|
pi_request_flow_message =>'REQUEST_WORKFLOW', --/ REQUEST_DEPLOYMENT
|
||
|
|
pi_id_sys_profile => v_n_id_sys_profile,
|
||
|
|
pi_id_next_user => pi_id_next_user
|
||
|
|
) ;
|
||
|
|
|
||
|
|
WHEN v_action_cat in(2,4,5) THEN
|
||
|
|
---Si la accion elegida implica un paso hacia atras en el proceso
|
||
|
|
---envia notificacion a todos los intervinientes activos de dicha solicitud
|
||
|
|
|
||
|
|
FOR c_user IN (
|
||
|
|
SELECT DISTINCT ref.id_user
|
||
|
|
FROM request_exec_flow ref
|
||
|
|
INNER JOIN user_roles ur
|
||
|
|
ON ref.id_user = ur.id_user
|
||
|
|
WHERE ref.id_request = pi_id_request
|
||
|
|
AND ref.status_result IS NOT NULL
|
||
|
|
AND ur.user_status = 1
|
||
|
|
UNION
|
||
|
|
SELECT ur.id_user
|
||
|
|
FROM user_roles ur
|
||
|
|
WHERE UPPER(ur.user_name) = (SELECT UPPER(dr.user_create)
|
||
|
|
FROM deployment_request dr
|
||
|
|
WHERE dr.id_request = pi_id_request)
|
||
|
|
) LOOP
|
||
|
|
|
||
|
|
pkg_standard_executors.prc_send_action_message (
|
||
|
|
pi_id_action => pi_id_action,
|
||
|
|
pi_id_request => pi_id_request,
|
||
|
|
pi_observation => pi_observations,
|
||
|
|
pi_request_status => v_action_status,
|
||
|
|
pi_request_flow_message =>'REQUEST_WORKFLOW', --/ REQUEST_DEPLOYMENT
|
||
|
|
pi_id_sys_profile => null,
|
||
|
|
pi_id_next_user => c_user.id_user
|
||
|
|
) ;
|
||
|
|
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
ELSE
|
||
|
|
---Para cualquier otro action_category
|
||
|
|
---Si se indica id_user se envia al mismo
|
||
|
|
-- Sino al id_sys_profile del paso siguiente
|
||
|
|
pkg_standard_executors.prc_send_action_message (
|
||
|
|
pi_id_action => pi_id_action,
|
||
|
|
pi_id_request => pi_id_request,
|
||
|
|
pi_observation => pi_observations,
|
||
|
|
pi_request_status => v_action_status,
|
||
|
|
pi_request_flow_message =>'REQUEST_WORKFLOW', --/ REQUEST_DEPLOYMENT
|
||
|
|
pi_id_sys_profile => v_n_id_sys_profile,
|
||
|
|
pi_id_next_user => pi_id_next_user
|
||
|
|
) ;
|
||
|
|
END CASE;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
ELSIF v_next_step = 0 THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4,
|
||
|
|
--- DEBUG
|
||
|
|
pi_log_message => 'ACCION DESPLIEGUE - '
|
||
|
|
|| log_program_name
|
||
|
|
|| ': '
|
||
|
|
|| sysdate
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
--- Coloca la solicitud en la cola para despliegue
|
||
|
|
pkg_deploy_coordinator.manage_dep_queue(pi_id_request => pi_id_request);
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
--Busca el id_sys_profile del siguiente paso cuando la solicitud se encuentra para despliegue
|
||
|
|
|
||
|
|
SELECT UNIQUE
|
||
|
|
id_sys_profile
|
||
|
|
INTO v_nn_id_sys_profile
|
||
|
|
FROM
|
||
|
|
deployment_pattern dp,
|
||
|
|
deployment_req_object dro
|
||
|
|
WHERE
|
||
|
|
dp.id_pattern = dro.id_pattern
|
||
|
|
AND dro.id_request = pi_id_request;
|
||
|
|
|
||
|
|
---
|
||
|
|
----------------------------------------------------------------
|
||
|
|
--Ejecuta procedimeinto para configuracion y contenido del mensaje
|
||
|
|
|
||
|
|
|
||
|
|
pkg_standard_executors.prc_send_action_message (
|
||
|
|
pi_id_action => pi_id_action,
|
||
|
|
pi_id_request => pi_id_request,
|
||
|
|
pi_observation => pi_observations,
|
||
|
|
pi_request_status => v_action_status,
|
||
|
|
pi_request_flow_message =>'REQUEST_WORKFLOW', --/ REQUEST_DEPLOYMENT
|
||
|
|
pi_id_sys_profile => v_nn_id_sys_profile,
|
||
|
|
pi_id_next_user => null
|
||
|
|
) ;
|
||
|
|
|
||
|
|
|
||
|
|
---ejecuta procedimeinto para configuracion y contenido del mensaje
|
||
|
|
------------------------------------------------------------------
|
||
|
|
|
||
|
|
ELSE
|
||
|
|
--- termina el proceso aprobatorio, pero no hay paso de despliegue en el
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
--ejecuta procedimeinto para configuracion y contenido del mensaje
|
||
|
|
|
||
|
|
|
||
|
|
FOR c_user IN (
|
||
|
|
SELECT DISTINCT ref.id_user
|
||
|
|
FROM request_exec_flow ref
|
||
|
|
INNER JOIN user_roles ur
|
||
|
|
on ref.id_user = ur.id_user
|
||
|
|
WHERE ref.id_request = pi_id_request
|
||
|
|
AND ref.status_result IS NOT NULL
|
||
|
|
AND ur.user_status =1
|
||
|
|
) LOOP
|
||
|
|
|
||
|
|
pkg_standard_executors.prc_send_action_message(
|
||
|
|
pi_id_action => pi_id_action,
|
||
|
|
pi_id_request => pi_id_request,
|
||
|
|
pi_observation => pi_observations,
|
||
|
|
pi_request_status => v_action_status,
|
||
|
|
pi_request_flow_message => 'REQUEST_WORKFLOW', --'REQUEST_WORKFLOW' / REQUEST_DEPLOYMENT
|
||
|
|
pi_id_sys_profile => null,
|
||
|
|
pi_id_next_user => c_user.id_user
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
---
|
||
|
|
apex_debug.error('pkg_workflow_coordinator.exec_action_workflow - %s', sqlerrm);
|
||
|
|
po_warning := pkg_sicd_commons.f_system_message(
|
||
|
|
pi_id_message => - 1,
|
||
|
|
pi_message_class => 'WFC',
|
||
|
|
pi_return_type => 2
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2,
|
||
|
|
--- ERROR
|
||
|
|
pi_log_message => 'WFC ERROR - '
|
||
|
|
|| log_program_name
|
||
|
|
|| ': '
|
||
|
|
|| sysdate
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
END;
|
||
|
|
/*-------------------------------------------------------------------------*/
|
||
|
|
END pkg_workflow_coordinator;
|
||
|
|
|
||
|
|
/
|