[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(last_update_date) into l_dt
from biv_sr_summary;
delete from biv_tmp_bin;
delete from biv_tmp_rt1;
delete from biv_tmp_rt2;
delete from biv_tmp_hs1;
delete from biv_tmp_hs2;
delete from biv_debug;
select au1.incident_id,min(au1.last_update_date)
from cs_incidents_audit_b au1,
cs_incident_statuses_b stat1,
cs_incident_statuses_b stat2
where au1.change_incident_status_flag = 'Y'
and au1.old_incident_status_id = stat1.incident_status_id
and au1.incident_status_id = stat2.incident_status_id
and nvl(stat1.close_flag,'N') = 'Y'
and nvl(stat2.close_flag,'N') <> 'Y'
group by au1.incident_id;
select incident_id, incident_date
from cs_incidents_all_b
where last_update_date >= l_last_prog_run;
insert into biv_sr_summary(incident_id,
arrival_time,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login
)
select incident_id,
to_number(to_char(trunc(incident_date,'HH24'),'HH24')) +
decode(sign(to_number(to_char(trunc(incident_date,'MI'),'MI'))-30),
1,.5,0),
l_curr_time,
l_curr_time,
l_user_id,
l_user_id,
l_login_id
from cs_incidents_all_b sr
where not exists ( select 1 from biv_sr_summary sm
where sr.incident_id = sm.incident_id)
;
update_reopen_reclose_date(l_incident_id,l_reopen_date,l_reclose_date);
update biv_sr_summary
set response_time = l_resp_time
where incident_id = l_incident_id;
update_escalation_level;
select min(au2.last_update_date)
into x_reclose_date
from cs_incidents_audit_b au2,
cs_incident_statuses_b stat3,
cs_incident_statuses_b stat4
where au2.incident_id = p_incident_id
and au2.last_update_date > p_reopen_date
and au2.change_incident_status_flag = 'Y'
and au2.old_incident_status_id = stat3.incident_status_id
and au2.incident_status_id = stat4.incident_status_id
and nvl(stat3.close_flag ,'N') <> 'Y'
and nvl(stat4.close_flag ,'N') = 'Y';
procedure update_reopen_reclose_date(p_incident_id number,
p_reopen_date date,
p_reclose_date date) as
begin
update biv_sr_summary
set reopen_date = p_reopen_date,
reclose_date = p_reclose_date
where incident_id = p_incident_id;
procedure update_escalation_level as
cursor c_escalation is
select r.object_id, t.escalation_level, owner_id, r.creation_date
from jtf_task_references_b r,
jtf_tasks_b t
where r.object_type_code = 'SR'
and r.reference_code = 'ESC'
and r.task_id = t.task_id
and t.task_type_id = 22;
update biv_sr_summary
set escalation_level = l_esc_level,
esc_owner_id = l_owner_id,
escalation_date = l_dt
where incident_id = l_incident_id;
select/*+index_ffs(grp_out jtf_rs_grp_relations_n1) index_ffs(usg JTF_RS_GROUP_USAGES_U2)*/ distinct related_group_id
from jtf_rs_grp_relations grp_out,
jtf_rs_group_usages usg
where relation_type = 'PARENT_GROUP'
and grp_out.related_group_id = usg.group_id
and usg.usage in ( 'METRICS', 'SUPPORT')
and grp_out.related_group_id
not in
(select/*+index_ffs(grp_in jtf_rs_grp_relations_n1)*/ grp_in.group_id
from jtf_rs_grp_relations grp_in
);
delete from biv_resource_groups;
insert into biv_resource_groups ( group_id, group_level)
values ( l_group_id, 1);
/** Now top level group has been inserted. The query below will insert
all the groups at lower hierarchy levels
****************/
-- dbms_output.put_line('Parent Group Id:'|| to_char(l_group_id));
insert into biv_resource_groups ( group_id, group_level)
select group_id, level+1
from jtf_rs_grp_relations
where relation_type = 'PARENT_GROUP'
start with related_group_id = l_group_id
connect by prior group_id = related_group_id;
update biv_resource_groups a
set usage = (select usage from jtf_rs_group_usages b
where a.group_id = b.group_id
and usage in ('METRICS', 'SUPPORT')
and rownum = 1);
insert into biv_resource_groups ( group_id, group_level, usage)
select a.group_id, 1, b.usage
from jtf_rs_groups_b a, jtf_rs_group_usages b
where a.group_id = b.group_id
and b.usage in ('SUPPORT', 'METRICS')
and not exists ( select 1
from biv_resource_groups r
where r.group_id = a.group_id
or r.group_id = a.group_id
);
delete from biv_resource_groups
where nvl(usage,'XX') not in ('SUPPORT', 'METRICS');
l_update_date date;
select min(last_update_date)
into l_update_date
from cs_incidents_audit_b
where incident_id = p_incident_id;
if l_update_date is null then return null;
else return(l_update_date-p_incident_date);