[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ALR_EMAIL_TABLE.delete;
select count(*)
into count_var
from alr_actual_responses
where open_closed = 'O';
select application_id, alert_id, response_set_id, open_closed
into alr_msg_dtls_tbl(1).app_id,
alr_msg_dtls_tbl(1).alert_id,
alr_msg_dtls_tbl(1).response_set_id,
alr_msg_dtls_tbl(1).open_closed
from alr_actual_responses
where message_handle=msg_handle and
node_handle= node_handle;
select variable_number, name, data_type, default_value,
NVL(detail_max_len,0)
from alr_response_variables
where application_id = p_app_id and
alert_id=p_alert_id and
response_set_id=p_response_set_id;
select response_id, type, response_text, response_name
from alr_valid_responses
where application_id = p_app_id and
alert_id=p_alert_id and
response_set_id=p_response_set_id;
select r.response_id, r.action_id, a.name, NVL(a.action_type,'R'),
a.body, a.concurrent_program_id,
DECODE(a.list_id, NULL, a.to_recipients, d.to_recipients),
DECODE(a.list_id, NULL, a.cc_recipients, d.cc_recipients),
DECODE(a.list_id, NULL, a.bcc_recipients, d.bcc_recipients),
DECODE(a.list_id, NULL, a.print_recipients, d.print_recipients),
DECODE(a.list_id, NULL, a.printer, d.printer),
a.subject, a.reply_to, a.column_wrap_flag,
a.maximum_summary_message_width, a.action_level_type,
' ', a.file_name, a.argument_string, a.program_application_id,
a.list_application_id, a.response_set_id, a.follow_up_after_days,
NVL(a.version_number,0)
from alr_actions a, alr_distribution_lists d,
alr_response_actions r
where r.application_id = p_app_id and
r.alert_id = p_alert_id and
r.response_set_id = p_response_set_id and
r.response_id = p_response_id and
r.application_id = a.application_id(+) and
r.action_id = a.action_id(+) and
r.enabled_flag= 'Y' and
NVL(r.end_date_active,SYSDATE)>=SYSDATE and
r.enabled_flag = a.enabled_flag(+) and
NVL(d.end_date_active(+),SYSDATE+1) >= SYSDATE and
a.application_id = d.application_id(+) and
a.list_id = d.list_id(+)
order by r.sequence;
select variable_name, value, data_type, detail_max_len
from alr_response_variable_values
where message_handle = p_msg_handle and
node_handle = p_node_handle;
select maintain_history_days into
maintain_history_days from
alr_alerts where
application_id=alr_msg_dtls_tbl(1).app_id and
alert_id = alr_msg_dtls_tbl(1).alert_id;
select action_id into p_action_id from alr_action_history
where message_handle=msg_handle and
node_handle=node_handle;
select max(version_number) into p_version_num from
alr_actions where action_id=p_action_id;
select to_recipients, cc_recipients, bcc_recipients,
reply_to, subject
into p_to_recip, p_cc_recip, p_bcc_recip,
p_reply_to, p_subject
from alr_actions
where action_id = p_action_id and
version_number = p_version_num;
select alr_response_messages_s.nextval into seq from dual;
insert into alr_response_messages(message_handle, node_handle,
application_id, alert_id, response_set_id, response_type,
response_id, response_message_id, date_processed,
to_recipients, cc_recipients, bcc_recipients, sent_from,
reply_to, subject, body, received)
values(msg_handle, node_handle, alr_msg_dtls_tbl(1).app_id,
alr_msg_dtls_tbl(1).alert_id,
alr_msg_dtls_tbl(1).response_set_id,
'V',
p_resp_id, seq,
SYSDATE,
p_to_recip,
p_cc_recip,
p_bcc_recip,
l_from,
p_reply_to,
p_subject,
p_response_body,
received);
insert into alr_response_variable_values(application_id, alert_id,
response_set_id, message_handle, node_handle, variable_name,
value, data_type, detail_max_len)
select alr_msg_dtls_tbl(1).app_id,
alr_msg_dtls_tbl(1).alert_id,
alr_msg_dtls_tbl(1).response_set_id,
msg_handle,
node_handle,
alr_init_resp_tbl(i).name,
alr_init_resp_tbl(i).default_value,
alr_init_resp_tbl(i).data_type,
alr_init_resp_tbl(i).max_len
from dual where not exists
(select null from alr_response_variable_values
where message_handle=msg_handle and
node_handle=node_handle and
variable_name=alr_init_resp_tbl(i).name);
insert into alr_response_variable_values(application_id, alert_id,
response_set_id, message_handle, node_handle, variable_name,
value, data_type, detail_max_len)
select alr_msg_dtls_tbl(1).app_id,
alr_msg_dtls_tbl(1).alert_id,
alr_msg_dtls_tbl(1).response_set_id,
msg_handle,
node_handle,
variable_name,
value,
alr_init_resp_tbl(t_counter).data_type,
alr_init_resp_tbl(t_counter).max_len
from dual where not exists
(select null from alr_response_variable_values
where message_handle=msg_handle and
node_handle=node_handle and
variable_name=variable_name);
insert into alr_response_action_history(message_handle,
node_handle, response_message_id, oracle_id, sequence, application_id,
alert_id, action_id, version_number, success_flag)
values (msg_handle, node_handle, response_msg_id, oracle_id,
seq, alr_msg_dtls_tbl(1).app_id,
alr_msg_dtls_tbl(1).alert_id,
alr_get_resp_act_tbl(1).action_id, version_num,
success_flag);
update alr_actual_responses
set response_id=alr_init_valid_resp_tbl(1).resp_id,
open_closed= open_closed, action_set_pass_fail=action_set_pass_fail
where message_handle = msg_handle and node_handle=node_handle;