187 lines
5.9 KiB
SQL
187 lines
5.9 KiB
SQL
--------------------------------------------------------
|
|
-- DDL for Procedure ACTION_MESSAGE_CONF
|
|
--------------------------------------------------------
|
|
set define off;
|
|
|
|
CREATE OR REPLACE EDITIONABLE PROCEDURE "ENVX_OPER"."ACTION_MESSAGE_CONF" (
|
|
pi_id_action IN NUMBER,
|
|
pi_id_request IN NUMBER,
|
|
pi_observertion IN VARCHAR2,
|
|
pi_request_status IN VARCHAR2,
|
|
po_subject OUT VARCHAR2,
|
|
po_message OUT CLOB,
|
|
po_message_type OUT NUMBER
|
|
) IS
|
|
v_response_code VARCHAR2(4000);
|
|
v_error_message VARCHAR2(4000);
|
|
r_drh deployment_req_hgroup%rowtype;
|
|
v_step_flow NUMBER(38, 0);
|
|
log_program_name VARCHAR2(100);
|
|
r_ref action_message%rowtype;
|
|
v_err_msg VARCHAR2(2000);
|
|
v_err_code VARCHAR2(50);
|
|
BEGIN
|
|
log_program_name := 'Procedure action_message_conf';
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Inicio Procedimiento action_message_conf - '
|
|
|| log_program_name
|
|
|| ': '
|
|
|| sysdate
|
|
);
|
|
|
|
------------------------------------------
|
|
--busca el cuerpo,asunto,tipo de mensensaja,etc segun la accion realizada
|
|
BEGIN
|
|
SELECT am.*
|
|
INTO r_ref
|
|
FROM action_message am
|
|
WHERE am.id_action = pi_id_action;
|
|
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
v_err_code := sqlcode;
|
|
v_err_msg := substr(sqlerrm, 1, 1000);
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Error Procedimiento action_message_conf - '
|
|
|| v_err_msg
|
|
|| ': '
|
|
|| sysdate
|
|
);
|
|
|
|
END;
|
|
--
|
|
------------------------------------------
|
|
|
|
--busca el Nº completo de la request (REQ_HGROUP_NAME)
|
|
BEGIN
|
|
SELECT r_drh.*
|
|
INTO r_drh
|
|
FROM deployment_req_hgroup r_drh
|
|
WHERE r_drh.id_request = pi_id_request
|
|
AND r_drh.id_req_hgroup_level = 'R';
|
|
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
v_err_code := sqlcode;
|
|
v_err_msg := substr(sqlerrm, 1, 1000);
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Error Procedimiento action_message_conf - '
|
|
|| v_err_msg
|
|
|| ': '
|
|
|| sysdate
|
|
);
|
|
|
|
END;
|
|
--
|
|
------------------------------------------
|
|
|
|
--Crea y extrae el Cuerpo del mensage
|
|
|
|
IF pi_observertion IS NULL THEN
|
|
po_message := 'La solicitud Nº '
|
|
|| r_drh.req_hgroup_name
|
|
|| chr(10)
|
|
|| 'Accion Realizada: '
|
|
|| r_ref.action_message_body
|
|
|| chr(10)
|
|
|| 'Fecha: '
|
|
|| sysdate
|
|
|| chr(10)
|
|
|| 'Observacion: Sin observaciones'
|
|
|| chr(10)
|
|
|| 'Estado de la solicitud: '
|
|
|| pi_request_status;
|
|
ELSE
|
|
po_message := 'La solicitud Nº '
|
|
|| r_drh.req_hgroup_name
|
|
|| chr(10)
|
|
|| 'Accion Realizada: '
|
|
|| r_ref.action_message_body
|
|
|| chr(10)
|
|
|| 'Fecha: '
|
|
|| sysdate
|
|
|| chr(10)
|
|
|| 'Observacion: '
|
|
|| pi_observertion
|
|
|| chr(10)
|
|
|| 'Estado de la solicitud: '
|
|
|| pi_request_status;
|
|
END IF;
|
|
--
|
|
------------------------------------------
|
|
--Crea y extrae el Asunto del mensage a enviar
|
|
|
|
po_subject := r_ref.action_message_subject
|
|
|| r_drh.req_hgroup_name
|
|
|| ' - '
|
|
|| to_char(sysdate, 'DD/MM/YYYY - HH24:MI:SS');
|
|
|
|
-- extraigo el tipo de mensage
|
|
|
|
po_message_type := r_ref.action_message_type;
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Asunto del mensaje es : ' || po_subject
|
|
);
|
|
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Notificacion es : ' || po_message
|
|
);
|
|
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Fin Procedimiento action_message_conf - '
|
|
|| log_program_name
|
|
|| ': '
|
|
|| sysdate
|
|
);
|
|
|
|
EXCEPTION
|
|
WHEN no_data_found THEN
|
|
dbms_output.put_line('No datos');
|
|
v_err_code := sqlcode;
|
|
v_err_msg := substr(sqlerrm, 1, 1000);
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Error tipo:No data found - '
|
|
|| v_err_msg
|
|
|| ': '
|
|
|| sysdate
|
|
);
|
|
|
|
WHEN OTHERS THEN
|
|
dbms_output.put_line('Error');
|
|
v_err_code := sqlcode;
|
|
v_err_msg := substr(sqlerrm, 1, 1000);
|
|
log_collector(
|
|
pi_program_name => log_program_name,
|
|
pi_log_level => 4,
|
|
--- DEBUG
|
|
pi_log_message => 'Error tipo:when others- '
|
|
|| v_err_msg
|
|
|| ': '
|
|
|| sysdate
|
|
);
|
|
|
|
END;
|
|
|
|
/
|