[Home] [Help]
PACKAGE BODY: APPS.CS_SR_LOG_DATA_TEMP_PVT
Source
1 PACKAGE BODY cs_sr_log_data_temp_pvt AS
2 /* $Header: csvlogb.pls 115.6 2002/11/30 11:50:51 pkesani noship $ */
3
4 procedure delete_log( p_session_id number
5 , p_incident_id number)
6 is
7
8 PRAGMA AUTONOMOUS_TRANSACTION;
9
10 begin
11 delete from cs_sr_log_data_temp
12 where session_id = p_session_id
13 and incident_id = p_incident_id;
14
15 commit;
16 exception
17 when NO_DATA_FOUND then
18 null;
19
20 end delete_log;
21
22 procedure insert_log( p_session_id number
23 , p_incident_id number
24 , p_log_text varchar2)
25 is
26
27 PRAGMA AUTONOMOUS_TRANSACTION;
28
29 begin
30
31 insert into cs_sr_log_data_temp
32 ( session_id,
33 incident_id,
34 log_text,
35 display_seq)
36 VALUES ( p_session_id,
37 p_incident_id,
38 p_log_text,
39 cs_sr_log_data_temp_pvt.display_seq);
40 commit;
41
42 end insert_log;
43
44 procedure get_log_report(p_incident_id in number,
45 x_session_id out NOCOPY number) is
46 i_return number := 0 ;
47 l_session_id number := 0;
48 begin
49
50 select userenv('SESSIONID') into l_session_id from dual;
51
52 cs_sr_log_data_temp_pvt.get_log_details(p_incident_id);
53
54 i_return := cs_sr_log_data_temp_pvt.sort_log_data(1, cs_sr_log_data_temp_pvt.i_total_records);
55
56 cs_sr_log_data_temp_pvt.insert_log_data(l_session_id, p_incident_id );
57
58 x_session_id := l_session_id;
59
60 exception
61 when OTHERS then
62 null;
63
64 end get_log_report;
65
66 procedure get_log_details(p_incident_id in number) is
67
68 cursor log_cursor is
69 select 'AUDIT' source_type,
70 audi.incident_id,
71 AUDI.incident_audit_id,
72 FU.USER_NAME last_updated_by,
73 AUDI.last_update_date last_update_date,
74 to_char(AUDI.old_incident_severity_id) severity_old,
75 to_char(AUDI.incident_severity_id) severity_new,
76 AUDI.CHANGE_INCIDENT_SEVERITY_FLAG incident_severity_flag,
77 to_char(AUDI.old_incident_type_id) type_old,
78 to_char(AUDI.incident_type_id) type_new,
79 AUDI.CHANGE_INCIDENT_TYPE_FLAG incident_type_flag,
80 to_char(AUDI.old_incident_status_id) status_old,
81 to_char(AUDI.incident_status_id) status_new,
82 AUDI.CHANGE_INCIDENT_STATUS_FLAG incident_status_flag,
83 to_char(AUDI.old_incident_urgency_id) urgency_old,
84 to_char(AUDI.incident_urgency_id) urgency_new,
85 AUDI.CHANGE_INCIDENT_URGENCY_FLAG incident_urgency_flag,
86 to_char(AUDI.old_incident_owner_id) owner_old,
87 to_char(AUDI.incident_owner_id) owner_new,
88 AUDI.CHANGE_INCIDENT_OWNER_FLAG incident_owner_flag,
89 to_char(AUDI.old_expected_resolution_date) date_old,
90 to_char(AUDI.expected_resolution_date) date_new,
91 AUDI.CHANGE_RESOLUTION_FLAG resolution_date_flag,
92 severity1.name old_severity_name ,
93 severity2.name new_severity_name ,
94 type1.name old_type_name ,
95 type2.name new_type_name ,
96 status1.name old_status_name ,
97 status2.name new_status_name ,
98 urgency1.name old_urgency_name ,
99 urgency2.name new_urgency_name ,
100 decode(audi.old_resource_type,'RS_EMPLOYEE',ext1.source_last_name||' '||ext1.source_first_name,'RS_TEAM',team1.team_name,'RS_GROUP',grp1.group_name) old_owner,
101 decode(audi.resource_type,'RS_EMPLOYEE',ext2.source_last_name||' '||ext2.source_first_name,'RS_TEAM',team2.team_name,'RS_GROUP',grp2.group_name) new_owner,
102 obj1.name old_resource_type ,
103 obj2.name resource_type ,
104 audi.change_resource_type_flag
105 from fnd_user FU,
106 cs_incidents_AUDIT_vl AUDI ,
107 cs_incident_severities_tl severity1 ,
108 cs_incident_severities_tl severity2 ,
109 cs_incident_types_tl type1 ,
110 cs_incident_types_tl type2 ,
111 cs_incident_statuses_tl status1 ,
112 cs_incident_statuses_tl status2 ,
113 cs_incident_urgencies_tl urgency1 ,
114 cs_incident_urgencies_tl urgency2 ,
115 jtf_rs_resource_extns ext1,
116 jtf_rs_teams_tl team1,
117 jtf_rs_groups_tl grp1,
118 jtf_rs_resource_extns ext2,
119 jtf_rs_teams_tl team2,
120 jtf_rs_groups_tl grp2,
121 jtf_objects_tl obj1 ,
122 jtf_objects_tl obj2
123 where audi.incident_id = p_incident_id
124 and FU.user_id = AUDI.last_updated_by
125 and severity1.incident_severity_id(+) = audi.old_incident_severity_id
126 and (severity1.language = userenv('LANG') or severity1.language is null)
127 and severity2.incident_severity_id(+) = audi.incident_severity_id
128 and (severity2.language = userenv('LANG') or severity2.language is null)
129 and type1.incident_type_id(+) = audi.old_incident_type_id
130 and (type1.language = userenv('LANG') or type1.language is null)
131 and type2.incident_type_id(+) = audi.incident_type_id
132 and (type2.language = userenv('LANG') or type2.language is null)
133 and status1.incident_status_id(+) = audi.old_incident_status_id
134 and (status1.language = userenv('LANG') or status1.language is null)
135 and status2.incident_status_id(+) = audi.incident_status_id
136 and (status2.language = userenv('LANG') or status2.language is null)
137 and urgency1.incident_urgency_id(+) = audi.old_incident_urgency_id
138 and (urgency1.language = userenv('LANG') or urgency1.language is null)
139 and urgency2.incident_urgency_id(+) = audi.incident_urgency_id
140 and (urgency2.language = userenv('LANG') or urgency2.language is null)
141 and ext1.resource_id(+) = audi.old_incident_owner_id
142 and team1.team_id(+) = audi.old_incident_owner_id
143 and (team1.language = userenv('LANG') or team1.language is null)
144 and grp1.group_id(+) = audi.old_incident_owner_id
145 and (grp1.language = userenv('LANG') or grp1.language is null)
146 and ext2.resource_id(+) = audi.incident_owner_id
147 and team2.team_id(+) = audi.incident_owner_id
148 and (team2.language = userenv('LANG') or team2.language is null)
149 and grp2.group_id(+) = audi.incident_owner_id
150 and (grp2.language = userenv('LANG') or grp2.language is null)
151 and obj1.object_code(+) = audi.old_resource_type
152 and (obj1.language = userenv('LANG') or obj1.language is null)
153 and obj2.object_code(+) = audi.resource_type
154 and (obj2.language = userenv('LANG') or obj2.language is null);
155
156 cursor task_cursor is select 'TASK' source_type,jtf.task_id,fnd.user_name,
157 jtf.last_update_date,jtf.description
158 from jtf_tasks_vl jtf, fnd_user fnd
159 where source_object_type_code='SR'
160 and source_object_id=p_incident_id
161 and jtf.last_updated_by = fnd.user_id;
162
163 cursor note_cursor is select 'NOTE' source_type,jtf.jtf_note_id,fnd.user_name,
164 jtf.last_update_date,jtf.notes
165 from jtf_notes_vl jtf, fnd_user fnd
166 where source_object_code='SR'
167 and source_object_id=p_incident_id
168 and jtf.last_updated_by = fnd.user_id;
169
170 cursor activity_cursor is select 'ACTIVITY' source_type,jtf.interaction_id,
171 act.short_description,fnd.user_name,jtf.last_update_date
172 from jtf_ih_activities jtf, jtf_ih_actions_tl act,
173 fnd_user fnd
174 where doc_ref = 'SR'
175 and doc_id = p_incident_id
176 and jtf.action_id = act.action_id
177 and act.language = userenv('LANG')
178 and jtf.last_updated_by = fnd.user_id;
179
180 cursor soln_cursor is
181 select 'SOLN' source_type, setv.set_id,
182 setv.name set_summary, sett.name set_type_name,
183 elev.name element_summary, elet.name element_type_name,
184 setl.last_update_date, fnd.user_name
185 from cs_kb_set_links setl, cs_kb_sets_vl setv,
186 cs_kb_set_types_tl sett, cs_kb_element_links elel,
187 cs_kb_elements_vl elev, cs_kb_element_types_tl elet,
188 fnd_user fnd
189 where setl.object_code ='SR'
190 and setl.other_id = p_incident_id
191 and setl.set_id=setv.set_id
192 and sett.set_type_id=setv.set_type_id(+)
193 and (sett.language = userenv('LANG') or sett.language is null)
194 and (elel.object_code='KB' or elel.object_code is null)
195 and elel.other_id(+)=setv.set_id
196 and elel.element_id=elev.element_id(+)
197 and elev.element_type_id = elet.element_type_id(+)
198 and (elet.language = userenv('LANG') or elet.language is null)
199 and setl.last_updated_by = fnd.user_id;
200
201 i_main_pointer integer;
202 i_other_pointer integer;
203 i_size_of_kb integer;
204 l_incident_id number;
205 l_formated_string varchar2(100);
206 old_set_id number;
207 length_of_string number;
208 gs_newline varchar2(1) := substr('
209
210 ',1,1);
211
212 begin
213
214 /* This function executes each of the SQL and stores the data in
215 the Main array and Other Array */
216 l_formated_string := null;
217 cs_sr_log_data_temp_pvt.i_total_records := 0;
218 cs_sr_log_data_temp_pvt.i_other_records := 0;
219
220 for i_ctn in log_cursor
221 loop
222
223 length_of_string := 0;
224
225 if i_ctn.incident_severity_flag = 'Y' then
226 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_severity'||': ',
227 i_ctn.old_severity_name,
228 i_ctn.new_severity_name,
229 i_ctn.incident_severity_flag,
230 i_ctn.last_update_date,
231 i_ctn.last_updated_by,
232 i_ctn.source_type);
233 end if;
234
235 if i_ctn.incident_type_flag = 'Y' then
236 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_type'||': ',
237 i_ctn.old_type_name,
238 i_ctn.new_type_name,
239 i_ctn.incident_type_flag,
240 i_ctn.last_update_date,
241 i_ctn.last_updated_by,
242 i_ctn.source_type);
243 end if;
244
245 if i_ctn.incident_status_flag = 'Y' then
246 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_status'||': ',
247 i_ctn.old_status_name,
248 i_ctn.new_status_name,
249 i_ctn.incident_status_flag,
250 i_ctn.last_update_date,
251 i_ctn.last_updated_by,
252 i_ctn.source_type);
253 end if;
254
255 if i_ctn.incident_urgency_flag = 'Y' then
256 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_urgency'||': ',
257 i_ctn.old_urgency_name,
258 i_ctn.new_urgency_name,
259 i_ctn.incident_urgency_flag,
260 i_ctn.last_update_date,
261 i_ctn.last_updated_by,
262 i_ctn.source_type);
263 end if;
264
265 if i_ctn.change_resource_type_flag = 'Y' then
266 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_owner_type'||': ',
267 i_ctn.old_resource_type,
268 i_ctn.resource_type,
269 i_ctn.change_resource_type_flag,
270 i_ctn.last_update_date,
271 i_ctn.last_updated_by,
272 i_ctn.source_type);
273 end if;
274
275 if i_ctn.incident_owner_flag = 'Y' then
276 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_owner'||': ',
277 i_ctn.old_owner,
278 i_ctn.new_owner,
279 i_ctn.incident_owner_flag,
280 i_ctn.last_update_date,
281 i_ctn.last_updated_by,
282 i_ctn.source_type);
283 end if;
284
285 if i_ctn.resolution_date_flag = 'Y' then
286 length_of_string := cs_sr_log_data_temp_pvt.format_data('parameter.log_date'||': ',
287 i_ctn.date_old,
288 i_ctn.date_new,
289 i_ctn.resolution_date_flag,
290 i_ctn.last_update_date,
291 i_ctn.last_updated_by,
292 i_ctn.source_type);
293 end if;
294
295 end loop; -- end of log_cursor loop
296
297 -- Task Cursor. Collecting all Task data into the arrays.
298 i_main_pointer := cs_sr_log_data_temp_pvt.i_total_records;
299 i_other_pointer := cs_sr_log_data_temp_pvt.i_other_records ;
300 for i_ctn in task_cursor
301 loop
302
303 i_main_pointer := i_main_pointer + 1;
304 cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
305 cs_sr_log_data_temp_pvt.main_log_pointer(i_main_pointer) := i_main_pointer;
306
307 cs_sr_log_data_temp_pvt.main_log_source(i_main_pointer) := i_ctn.source_type;
308 cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer) := i_ctn.task_id;
309
310 i_other_pointer := i_other_pointer + 1;
311 cs_sr_log_data_temp_pvt.other_log_source(i_other_pointer):= i_ctn.source_type;
312 cs_sr_log_data_temp_pvt.other_log_id(i_other_pointer) := i_ctn.task_id;
313 cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer) := '*** '||'parameter.log_tasks'||': '||i_ctn.user_name||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||gs_newline||i_ctn.description||gs_newline;
314 cs_sr_log_data_temp_pvt.main_log_page(i_main_pointer) := length(cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer));
315
316 end loop; -- end of task_cursor loop
317
318 cs_sr_log_data_temp_pvt.i_total_records := i_main_pointer ;
319 cs_sr_log_data_temp_pvt.i_other_records := i_other_pointer ;
320
321 -- Notes Cursor. Collecting all Notes data into the arrays.
322 i_main_pointer := cs_sr_log_data_temp_pvt.i_total_records;
323 i_other_pointer := cs_sr_log_data_temp_pvt.i_other_records ;
324
325 for i_ctn in note_cursor
326 loop
327
328 i_main_pointer := i_main_pointer + 1;
329 cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
330 cs_sr_log_data_temp_pvt.main_log_pointer(i_main_pointer) := i_main_pointer;
331
332 cs_sr_log_data_temp_pvt.main_log_source(i_main_pointer) := i_ctn.source_type;
333 cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer) := i_ctn.jtf_note_id;
334
335 i_other_pointer := i_other_pointer + 1;
336 cs_sr_log_data_temp_pvt.other_log_source(i_other_pointer):= i_ctn.source_type;
337 cs_sr_log_data_temp_pvt.other_log_id(i_other_pointer) := i_ctn.jtf_note_id;
338 cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer) := '*** '||'parameter.log_notes'||': '||i_ctn.user_name||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||gs_newline||i_ctn.notes||gs_newline;
339 cs_sr_log_data_temp_pvt.main_log_page(i_main_pointer) := length(cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer));
340
341 end loop; -- end of note_cursor loop
342
343 cs_sr_log_data_temp_pvt.i_total_records := i_main_pointer ;
344 cs_sr_log_data_temp_pvt.i_other_records := i_other_pointer ;
345
346 -- Activity Cursor. Collecting all Activity data into the arrays.
347 i_main_pointer := cs_sr_log_data_temp_pvt.i_total_records;
348
349 for i_ctn in activity_cursor
350 loop
351
352 i_main_pointer := i_main_pointer + 1;
353 cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
354 cs_sr_log_data_temp_pvt.main_log_pointer(i_main_pointer) := i_main_pointer;
355
356 cs_sr_log_data_temp_pvt.main_log_source(i_main_pointer) := i_ctn.source_type;
357 cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer) := '*** '||'parameter.log_interaction'||': '||i_ctn.interaction_id||' '||i_ctn.user_name;
358 cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer):= cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer)||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||' '||i_ctn.short_description||gs_newline;
359 cs_sr_log_data_temp_pvt.main_log_page(i_main_pointer) := length(cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer));
360
361 end loop; -- end of note_cursor loop
362
363 cs_sr_log_data_temp_pvt.i_total_records := i_main_pointer ;
364
365 -- All Data from Knowledge base is fetched in this Cursor
366
367 i_main_pointer := cs_sr_log_data_temp_pvt.i_total_records;
368 i_other_pointer := cs_sr_log_data_temp_pvt.i_other_records ;
369 old_set_id := 0;
370
371 for i_ctn in soln_cursor
372 loop
373
374 if old_set_id <> i_ctn.set_id then
375 i_size_of_kb := 0;
376 i_main_pointer := i_main_pointer + 1;
377 cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
378 cs_sr_log_data_temp_pvt.main_log_pointer(i_main_pointer) := i_main_pointer;
379
380 cs_sr_log_data_temp_pvt.main_log_source(i_main_pointer) := i_ctn.source_type||'-'||i_ctn.set_id;
381 cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer) := '*** '||'parameter.log_knowledge'||': '||i_ctn.user_name||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ');
382 cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer):= cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer)||i_ctn.set_id||gs_newline||i_ctn.set_type_name||gs_newline||i_ctn.set_summary||gs_newline;
383 i_size_of_kb := i_size_of_kb + length(cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer));
384 old_set_id:=i_ctn.set_id;
385 end if;
386 i_other_pointer := i_other_pointer + 1;
387 cs_sr_log_data_temp_pvt.other_log_source(i_other_pointer):= i_ctn.source_type||'-'||i_ctn.set_id;
388 cs_sr_log_data_temp_pvt.other_log_id(i_other_pointer) := i_ctn.set_id;
389 cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer) := ' '||i_ctn.element_type_name||' : '||i_ctn.element_summary||gs_newline;
390 i_size_of_kb := i_size_of_kb + length(cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer));
391 cs_sr_log_data_temp_pvt.main_log_page(i_main_pointer) := i_size_of_kb;
392
393 end loop;
394
395 cs_sr_log_data_temp_pvt.i_total_records := i_main_pointer ;
396 cs_sr_log_data_temp_pvt.i_other_records := i_other_pointer ;
397
398 end get_log_details;
399
400 function format_data(column_name in varchar2,
401 old_field_name in varchar2,
402 new_field_name in varchar2,
403 changed_flag in varchar2,
404 last_update_date in date,
405 last_updated_by in varchar2,
406 source_type in varchar2) return number is
407
408 l_formated_string varchar2(200);
409 gs_newline varchar2(1) := substr('
410
411 ',1,1);
412
413 i_sort_pointer integer;
414 i_counter integer;
415
416 begin
417
418 /* This function formats all Audit data */
419
420 i_sort_pointer := cs_sr_log_data_temp_pvt.i_total_records;
421 if changed_flag = 'Y' then
422 l_formated_string := '*** '||'parameter.log_audit'||': '||last_updated_by||' '||rpad(to_char(last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||' '||rpad(column_name,20,' ')||' '||old_field_name||' --> '||new_field_name;
423
424 l_formated_string := l_formated_string||gs_newline;
425 i_sort_pointer := i_sort_pointer + 1;
426 cs_sr_log_data_temp_pvt.main_log_date(i_sort_pointer) := last_update_date;
427 cs_sr_log_data_temp_pvt.main_log_pointer(i_sort_pointer) := i_sort_pointer;
428
429 cs_sr_log_data_temp_pvt.main_log_source(i_sort_pointer) := source_type;
430 cs_sr_log_data_temp_pvt.main_log_text(i_sort_pointer) := l_formated_string;
431 cs_sr_log_data_temp_pvt.main_log_page(i_sort_pointer) := length(l_formated_string);
432 else
433 l_formated_string := null;
434 end if;
435
436 cs_sr_log_data_temp_pvt.i_total_records := i_sort_pointer ;
437 return length(l_formated_string);
438
439 end format_data;
440
441 function sort_log_data(p_low in integer, p_high in integer) RETURN INTEGER is
442 l_mid_date date;
443 l_mid_source varchar2(10);
444 l_mid_log_text varchar2(200);
445
446 l_swap_date date;
447 l_swap_pointer integer;
448 l_swap_source varchar2(10);
449 l_swap_log_text varchar2(200);
450
451 i_start integer;
452 i_end integer;
453 i_mid integer;
454 i_return integer;
455 p_return integer;
456
457 begin
458 /* This procedure is an implementation of the Quick sort algorithm.
459 www.maths.lse.ac.uk/Courses/MA309/quicksort.html -
460 Some modification was done to the above Algorithm.
461 Basically all the data in the main Array is sorted.
462 */
463 p_return := 0;
464 if (p_low >= p_high) then
465 return p_return;
466 end if;
467
468 i_start := p_low-1;
469 i_end := p_high;
470
471 i_mid := i_end; -- Selecting an arbitary mid point
472 l_mid_date := cs_sr_log_data_temp_pvt.main_log_date(i_mid);
473
474 while i_start < i_end -- Main while loop
475 loop
476 loop
477 i_start := i_start + 1;
478 if (i_start > i_end) then
479 i_start := i_end;
480 exit;
481 end if;
482 if (cs_sr_log_data_temp_pvt.main_log_date(i_start) < l_mid_date) then
483 exit;
484 end if;
485 end loop;
486
487 loop
488 i_end := i_end - 1;
489 if ((i_end < i_start) or
490 (cs_sr_log_data_temp_pvt.main_log_date(i_end) > l_mid_date)) then
491 exit;
492 end if;
493 end loop;
494
495 if (i_start < i_end) then
496 --Swap data in i_start i_end
497 l_swap_date := cs_sr_log_data_temp_pvt.main_log_date(i_start);
498 l_swap_pointer := cs_sr_log_data_temp_pvt.main_log_pointer(i_start);
499
500 cs_sr_log_data_temp_pvt.main_log_date(i_start):= cs_sr_log_data_temp_pvt.main_log_date(i_end);
501 cs_sr_log_data_temp_pvt.main_log_pointer(i_start):= cs_sr_log_data_temp_pvt.main_log_pointer(i_end);
502
503 cs_sr_log_data_temp_pvt.main_log_date(i_end):= l_swap_date;
504 cs_sr_log_data_temp_pvt.main_log_pointer(i_end):= l_swap_pointer;
505
506 end if;
507 end loop; -- End of main while loop
508
509 l_swap_date := cs_sr_log_data_temp_pvt.main_log_date(i_start);
510 l_swap_pointer := cs_sr_log_data_temp_pvt.main_log_pointer(i_start);
511
512 cs_sr_log_data_temp_pvt.main_log_date(i_start):= cs_sr_log_data_temp_pvt.main_log_date(p_high);
513 cs_sr_log_data_temp_pvt.main_log_pointer(i_start):= cs_sr_log_data_temp_pvt.main_log_pointer(p_high);
514
515 cs_sr_log_data_temp_pvt.main_log_date(p_high):= l_swap_date;
516 cs_sr_log_data_temp_pvt.main_log_pointer(p_high):= l_swap_pointer;
517
518 i_return := cs_sr_log_data_temp_pvt.sort_log_data(p_low, i_start - 1);
519 i_return := cs_sr_log_data_temp_pvt.sort_log_data(i_start + 1, p_high);
520 return p_return;
521
522 end sort_log_data;
523
524 procedure insert_log_data(p_session_id NUMBER,
525 p_incident_id NUMBER) is
526
527 i_counter integer;
528 i_location integer:=1;
529 i_position integer;
530 l_source_id number;
531 l_set_id varchar2(30);
532 total_log_size number:=0;
533 start_location integer := 1;
534
535 begin
536
537 /*
538 This function is used to insert data from the memory arrays to
539 the field LOG_NOTES.LOG_DETAILS.
540 */
541 -- Delete the records first
542 --
543 cs_sr_log_data_temp_pvt.delete_log( p_session_id, p_incident_id);
544
545 cs_sr_log_data_temp_pvt.display_seq := 0;
546 total_log_size := 0;
547 for i_location in start_location..cs_sr_log_data_temp_pvt.i_total_records
548 loop
549 i_counter := cs_sr_log_data_temp_pvt.main_log_pointer(i_location);
550 cs_sr_log_data_temp_pvt.display_seq := cs_sr_log_data_temp_pvt.display_seq+1;
551
552 if cs_sr_log_data_temp_pvt.main_log_source(i_counter) ='AUDIT' then
553
554 cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
555 cs_sr_log_data_temp_pvt.main_log_text(i_counter));
556
557 elsif cs_sr_log_data_temp_pvt.main_log_source(i_counter) ='ACTIVITY' then
558
559 cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
560 cs_sr_log_data_temp_pvt.main_log_text(i_counter));
561
562 elsif cs_sr_log_data_temp_pvt.main_log_source(i_counter) ='TASK' then
563 l_source_id := to_number(rtrim(cs_sr_log_data_temp_pvt.main_log_text(i_counter)));
564 cs_sr_log_data_temp_pvt.get_data_location('TASK', l_source_id, i_position);
565
566 cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
567 cs_sr_log_data_temp_pvt.other_log_text(i_position));
568
569 elsif cs_sr_log_data_temp_pvt.main_log_source(i_counter) ='NOTE' then
570 l_source_id := to_number(rtrim(cs_sr_log_data_temp_pvt.main_log_text(i_counter)));
571 cs_sr_log_data_temp_pvt.get_data_location('NOTE', l_source_id, i_position);
572
573 cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
574 cs_sr_log_data_temp_pvt.other_log_text(i_position));
575
576 elsif substr(cs_sr_log_data_temp_pvt.main_log_source(i_counter),1,4) ='SOLN'
577 and cs_sr_log_data_temp_pvt.i_other_records > 0 then
578
579 cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
580 cs_sr_log_data_temp_pvt.main_log_text(i_counter));
581 l_set_id := substr(cs_sr_log_data_temp_pvt.main_log_source(i_counter),6,length(cs_sr_log_data_temp_pvt.main_log_source(i_counter)));
582 l_source_id := to_number(rtrim(l_set_id));
583 cs_sr_log_data_temp_pvt.get_data_location(cs_sr_log_data_temp_pvt.main_log_source(i_counter), l_source_id, i_position);
584
585 i_position := 1;
586 while (i_position <= cs_sr_log_data_temp_pvt.i_other_records)
587 loop
588 if (cs_sr_log_data_temp_pvt.other_log_source(i_position) = cs_sr_log_data_temp_pvt.main_log_source(i_counter)
589 and cs_sr_log_data_temp_pvt.other_log_id(i_position)=l_source_id) then
590
591 cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id, cs_sr_log_data_temp_pvt.other_log_text(i_position));
592 end if;
593 i_position := i_position + 1;
594 end loop;
595
596
597 end if; -- End of if for all Source Types.
598
599 end loop;
600
601 end insert_log_data;
602
603 procedure get_data_location(source_type in varchar2, source_id in number, position out NOCOPY integer) is
604 count number;
605 begin
606
607 /* Given parameters of source_type and source_id this function
608 searches the Other Array and returns the pointer of the data.
609 This function will not be used later as the pointer will be directly
610 stored */
611
612 if source_id is not null and source_type is not null then
613 if cs_sr_log_data_temp_pvt.i_other_records = 1 then
614 if cs_sr_log_data_temp_pvt.other_log_id(1)=source_id
615 and cs_sr_log_data_temp_pvt.other_log_source(1)=source_type then
616 position := 1;
617 end if;
618
619 else
620 for i_position in 1..cs_sr_log_data_temp_pvt.i_other_records
621 loop
622 if cs_sr_log_data_temp_pvt.other_log_id(i_position)=source_id
623 and cs_sr_log_data_temp_pvt.other_log_source(i_position)=source_type then
624 position := i_position;
625 exit;
626 end if;
627 end loop;
628 end if; -- End of if at i_other_records=1
629 end if;
630
631 end get_data_location;
632
633 procedure inc_display_seq is
634 ctn number;
635
636 begin
637 cs_sr_log_data_temp_pvt.display_seq := cs_sr_log_data_temp_pvt.display_seq+1;
638
639 end inc_display_seq;
640
641 END;