1 package body biv_sr_details_pkg as
2 /* $Header: bivcsrdb.pls 120.3 2006/02/14 23:21:59 ngmishra noship $ */
3 -- get data for reopen
4 function get_last_run_time return date is
5 l_dt date;
6 begin
7 select max(last_update_date) into l_dt
8 from biv_sr_summary;
9 return nvl(l_dt,sysdate - 3650);
10 exception
11 when others then
12 return sysdate - 365*10;
13 end;
14 procedure clear_temp_tables (errbuf out nocopy varchar2,
15 retcode out nocopy number) is
16 begin
17 delete from biv_tmp_bin;
18 delete from biv_tmp_rt1;
19 delete from biv_tmp_rt2;
20 delete from biv_tmp_hs1;
21 delete from biv_tmp_hs2;
22 delete from biv_debug;
23 commit;
24 end;
25 -----------------------------------------
26 procedure get_data (errbuf out nocopy varchar2,
27 retcode out nocopy number) is
28 cursor c_reopen_sr is
29 select au1.incident_id,min(au1.last_update_date)
30 from cs_incidents_audit_b au1,
31 cs_incident_statuses_b stat1,
32 cs_incident_statuses_b stat2
33 where au1.change_incident_status_flag = 'Y'
34 and au1.old_incident_status_id = stat1.incident_status_id
35 and au1.incident_status_id = stat2.incident_status_id
36 and nvl(stat1.close_flag,'N') = 'Y'
37 and nvl(stat2.close_flag,'N') <> 'Y'
38 group by au1.incident_id;
39 l_incident_id cs_incidents_all_b.incident_id % type;
40 l_incident_date date;
41 l_reopen_date date;
42 l_reclose_date date;
43 l_resp_time number;
44 l_last_prog_run date;
45 l_curr_time date := sysdate;
46
47 l_user_id number := fnd_global.user_id;
48 l_login_id number := fnd_global.login_id;
49 cursor c_sreqs is
50 select incident_id, incident_date
51 from cs_incidents_all_b
52 where last_update_date >= l_last_prog_run;
53 begin
54 l_last_prog_run := get_last_run_time;
55 --dbms_output.put_line('Last time program was run on ' ||
56 -- to_char(l_last_prog_run,'dd-mon-yyyy hh24:mi:ss'));
57 insert into biv_sr_summary(incident_id,
58 arrival_time,
59 last_update_date,
60 creation_date,
61 last_updated_by,
62 created_by,
63 last_update_login
64 )
65 select incident_id,
66 to_number(to_char(trunc(incident_date,'HH24'),'HH24')) +
67 decode(sign(to_number(to_char(trunc(incident_date,'MI'),'MI'))-30),
68 1,.5,0),
69 l_curr_time,
70 l_curr_time,
71 l_user_id,
72 l_user_id,
73 l_login_id
74 from cs_incidents_all_b sr
75 where not exists ( select 1 from biv_sr_summary sm
76 where sr.incident_id = sm.incident_id)
77 ;
78 --dbms_output.put_line('Number of New incidents inserted in Summary Table :'||
79 -- to_char(sql%rowcount));
80 open c_reopen_sr;
81 loop
82 fetch c_reopen_sr into l_incident_id, l_reopen_date;
83 if c_reopen_sr % notfound then exit; end if;
84 get_reclose_date(l_incident_id, l_reopen_date, l_reclose_date);
85 update_reopen_reclose_date(l_incident_id,l_reopen_date,l_reclose_date);
86 end loop;
87 close c_reopen_sr;
88 --
89 -- update response time
90 open c_sreqs;
91 loop
92 fetch c_sreqs into l_incident_id, l_incident_date;
93 if c_sreqs % notfound then exit; end if;
94 l_resp_time := get_response_time(l_incident_id, l_incident_date);
95 if (l_resp_time is not null) then
96 update biv_sr_summary
97 set response_time = l_resp_time
98 where incident_id = l_incident_id;
99 end if;
100 end loop;
101 --dbms_output.put_line('No of Incidents update in Summary Table:'||
102 -- to_char(c_sreqs%rowcount));
103 close c_sreqs;
104 -- End of response time update
105 --
106 update_escalation_level;
107 --get_group_levels(errbuf, retcode);
108 clear_temp_tables(errbuf, retcode);
109 end;
110 ------------------- End of Get Data ------------------
111 procedure get_reclose_date(p_incident_id number,
112 p_reopen_date date,
113 x_reclose_date in out nocopy date) as
114 begin
115 select min(au2.last_update_date)
116 into x_reclose_date
117 from cs_incidents_audit_b au2,
118 cs_incident_statuses_b stat3,
119 cs_incident_statuses_b stat4
120 where au2.incident_id = p_incident_id
121 and au2.last_update_date > p_reopen_date
122 and au2.change_incident_status_flag = 'Y'
123 and au2.old_incident_status_id = stat3.incident_status_id
124 and au2.incident_status_id = stat4.incident_status_id
125 and nvl(stat3.close_flag ,'N') <> 'Y'
126 and nvl(stat4.close_flag ,'N') = 'Y';
127 exception
128 when no_data_found then
129 x_reclose_date := null;
130 end;
131 procedure update_reopen_reclose_date(p_incident_id number,
132 p_reopen_date date,
133 p_reclose_date date) as
134 begin
135 update biv_sr_summary
136 set reopen_date = p_reopen_date,
137 reclose_date = p_reclose_date
138 where incident_id = p_incident_id;
139 exception
140 when others then null;
141 end;
142 procedure update_escalation_level as
143 cursor c_escalation is
144 select r.object_id, t.escalation_level, owner_id, r.creation_date
145 from jtf_task_references_b r,
146 jtf_tasks_b t
147 where r.object_type_code = 'SR'
148 and r.reference_code = 'ESC'
149 and r.task_id = t.task_id
150 and t.task_type_id = 22;
151 l_incident_id cs_incidents_all_b.incident_id % type;
152 l_esc_level jtf_tasks_b.escalation_level % type;
153 l_owner_type_code jtf_tasks_b.owner_type_code % type;
154 l_owner_id jtf_tasks_b.owner_id % type;
155 l_dt jtf_task_references_b.creation_date % type;
156
157 begin
158 open c_escalation;
159 loop
160 fetch c_escalation into l_incident_id, l_esc_level,
161 l_owner_id, l_dt;
162 if c_escalation % notfound then exit; end if;
163 update biv_sr_summary
164 set escalation_level = l_esc_level,
165 esc_owner_id = l_owner_id,
166 escalation_date = l_dt
167 where incident_id = l_incident_id;
168 end loop;
169 close c_escalation;
170 end;
171 procedure get_group_levels (errbuf out nocopy varchar2,
172 retcode out nocopy number ) is
173 /********
174 This cursor will give you all the groups which are at top level
175 ***************************/
176 /*****Hints added and "not exists" replaced with "not in" as part of appsperf bug fix (bug#5029442)********/
177 cursor c_parent_groups is
178 select/*+index_ffs(grp_out jtf_rs_grp_relations_n1) index_ffs(usg JTF_RS_GROUP_USAGES_U2)*/ distinct related_group_id
179 from jtf_rs_grp_relations grp_out,
180 jtf_rs_group_usages usg
181 where relation_type = 'PARENT_GROUP'
182 and grp_out.related_group_id = usg.group_id
183 and usg.usage in ( 'METRICS', 'SUPPORT')
184 and grp_out.related_group_id
185 not in
186 (select/*+index_ffs(grp_in jtf_rs_grp_relations_n1)*/ grp_in.group_id
187 from jtf_rs_grp_relations grp_in
188 );
189
190 /**End of appsperf fix for bug#5029442**/
191
192 l_group_id jtf_rs_groups_b.group_id % type;
193 begin
194 delete from biv_resource_groups;
195 open c_parent_groups;
196 loop
197 begin
198 fetch c_parent_groups into l_group_id;
199 if c_parent_groups%notfound then exit; end if;
200 insert into biv_resource_groups ( group_id, group_level)
201 values ( l_group_id, 1);
202 /** Now top level group has been inserted. The query below will insert
203 all the groups at lower hierarchy levels
204 ****************/
205 -- dbms_output.put_line('Parent Group Id:'|| to_char(l_group_id));
206 insert into biv_resource_groups ( group_id, group_level)
207 select group_id, level+1
208 from jtf_rs_grp_relations
209 where relation_type = 'PARENT_GROUP'
210 start with related_group_id = l_group_id
211 connect by prior group_id = related_group_id;
212
213 exception
214 when others then
215 null;
216 -- dbms_output.put_line('Error for Parent Group Id:'||
217 -- to_char(l_group_id));
218 -- dbms_output.put_line('Error Text:'|| sqlerrm);
219 end;
220 end loop;
221 update biv_resource_groups a
222 set usage = (select usage from jtf_rs_group_usages b
223 where a.group_id = b.group_id
224 and usage in ('METRICS', 'SUPPORT')
225 and rownum = 1);
226 close c_parent_groups;
227 commit;
228 insert into biv_resource_groups ( group_id, group_level, usage)
229 select a.group_id, 1, b.usage
230 from jtf_rs_groups_b a, jtf_rs_group_usages b
231 where a.group_id = b.group_id
232 and b.usage in ('SUPPORT', 'METRICS')
233 and not exists ( select 1
234 from biv_resource_groups r
235 where r.group_id = a.group_id
236 or r.group_id = a.group_id
237 );
238
239 delete from biv_resource_groups
240 where nvl(usage,'XX') not in ('SUPPORT', 'METRICS');
241
242 end;
243 function get_response_time(p_incident_id number,
244 p_incident_date date) return number as
245 l_update_date date;
246 begin
247 select min(last_update_date)
248 into l_update_date
249 from cs_incidents_audit_b
250 where incident_id = p_incident_id;
251
252 if l_update_date is null then return null;
253 else return(l_update_date-p_incident_date);
254 end if;
255 exception
256 when others then return null;
257 end;
258 end;
[Home] [Help]