[Home] [Help]
PACKAGE BODY: APPS.IEU_SH_CON_PVT
Source
1 PACKAGE BODY IEU_SH_CON_PVT AS
2 /* $Header: IEUVSHCB.pls 120.1 2005/10/28 14:36:43 parghosh noship $ */
3
4 PROCEDURE IEU_SH_END_IDLE_TRANS(ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY VARCHAR2, p_agent_name IN VARCHAR2, p_appl_name in VARCHAR2, p_timeout in NUMBER) IS
5
6 l_media_active VARCHAR2(1);
7 l_sh_active_flag VARCHAR2(1);
8 l_update_time_format VARCHAR2(32);
9 l_media_not_active VARCHAR2(1);
10 l_end_state_code VARCHAR2(32);
11 l_force_close_flag VARCHAR2(1);
12
13 --Select all users who have more than one session open by UWQ
14
15 cursor l_users(l_application_id in NUMBER) is
16 SELECT resource_id, count(resource_id) count, max(last_update_date) last_update
17 FROM ieu_sh_sessions where active_flag = l_sh_active_flag
18 and application_id = l_application_id
19 GROUP BY resource_id HAVING count(resource_id) > 1;
20
21 -- SELECT resource_id
22 --FROM JTF_RS_RESOURCE_EXTNS j, FND_USER f
23 --where j.user_id = f.user_id;
24
25 --Select all the sessions corresponding to a particular resource_id
26 --which are open by UWQ
27
28 cursor l_ses_resource(l_resource_id in NUMBER, l_application_id in NUMBER) is
29 SELECT session_id, last_update_date
30 FROM ieu_sh_sessions
31 WHERE resource_id = l_resource_id
32 AND active_flag = l_sh_active_flag
33 AND application_id = l_application_id;
34
35 -- Select the session id if the sessions are open by UWQ
36
37 cursor l_ses_cur(l_application_id in NUMBER) is
38 SELECT session_id, resource_id
39 FROM ieu_sh_sessions ses
40 WHERE end_date_time is NULL
41 AND active_flag = l_sh_active_flag
42 AND application_id = l_application_id;
43
44 -- Select all activity id for all open sessions
45
46 cursor l_act_cur1(l_ses_id IN NUMBER) is
47 SELECT activity_id, media_id, activity_type_code, session_id, BEGIN_DATE_TIME
48 FROM ieu_sh_activities
49 WHERE end_date_time is NULL
50 AND active_flag = l_sh_active_flag
51 AND session_id = l_ses_id;
52
53 -- select the activity id for all activities opened by UWQ whose sessions have been closed,
54
55 cursor l_act_cur2(l_application_id in NUMBER) is
56 SELECT activity_id, session_id, media_id, BEGIN_DATE_TIME
57 FROM ieu_sh_activities
58 WHERE end_date_time is NULL
59 AND active_flag = l_sh_active_flag
60 AND session_id in (select session_id from ieu_sh_sessions
61 where application_id = l_application_id
62 and end_date_time is not null
63 and active_flag is null)
64 ;
65
66 l_applCursor t_cursor;
67 l_applId NUMBER;
68
69 l_act_id NUMBER;
70 l_end_date_time DATE;
71 l_last_update_date DATE;
72 l_last_update_time NUMBER;
73 --l_count_ses NUMBER;
74 --l_last_ses_update DATE;
75 l_ses_timeout NUMBER;
76 l_agent_resource_id NUMBER;
77
78 l_count_lc_segs NUMBER;
79 l_count_lc_segs_active NUMBER;
80 l_cal_end_date_time DATE;
81 --l_max_end_date_time DATE;
82 --l_max_deliver_date_time DATE;
83 --l_max_begin_date_time DATE;
84 l_cycle_end_date_time DATE;
85
86 l_total_act NUMBER;
87 l_terminated_act NUMBER;
88
89 l_act_resource_id NUMBER;
90 l_act_end_date DATE;
91
92 --l_cursor_index NUMBER := 2000;
93 BEGIN
94
95 l_media_active := 'Y';
96 l_sh_active_flag := 'T';
97 l_update_time_format := 'hh24';
98 l_media_not_active := 'N';
99 l_end_state_code := 'END';
100 l_force_close_flag := 'Y';
101
102 l_ses_timeout := p_timeout;
103 l_total_act := 0;
104 l_terminated_act := 0;
105
106
107 /*
108 IF (FND_PROFILE.VALUE('IEU_UWQ_SESSION_TIMEOUT') is null)
109 THEN
110 l_ses_timeout:=3;
111 ELSE
112 l_ses_timeout:=TO_NUMBER(FND_PROFILE.VALUE('IEU_UWQ_SESSION_TIMEOUT'))/60;
113
114 END IF;
115 */
116
117 -- Main Cleanup Program
118 IEU_SH_OPEN_CURSOR(l_applCursor, p_appl_name);
119
120 loop
121 FETCH l_applCursor INTO l_applId;
122 EXIT WHEN l_applCursor%NOTFOUND;
123
124 --INSERT into P_TEMP (cnt, msg) VALUES (l_cursor_index, l_applId);
125 --l_cursor_index := l_cursor_index + 1;
126
127 for ses_cur_rec in l_ses_cur(l_applId)
128 loop
129
130 select max(trunc(last_update_date)), max(to_number(to_char(last_update_date, l_update_time_format)))
131 into l_last_update_date, l_last_update_time
132 from ieu_sh_activities act
133 where act.session_id = ses_cur_rec.session_id;
134
135 IF ( ( (trunc(sysdate) > l_last_update_date)
136 and
137 (trunc(sysdate) - l_last_update_date > 0.00000)
138 and
139 (trunc(sysdate) - l_last_update_date < 1.00000)
140 and
141 ( (23 - l_last_update_time + to_number(to_char(sysdate, l_update_time_format)) >= l_ses_timeout )
142 )
143 or
144 (trunc(sysdate) - l_last_update_date > 1.00000)
145 or
146 ((trunc(sysdate) = l_last_update_date )
147 and
148 ((to_number(to_char(sysdate, l_update_time_format)) - l_ses_timeout) >= l_last_update_time )
149 )
150 )
151 )
152 THEN
153
154 for act_cur_rec_1 in l_act_cur1(ses_cur_rec.session_id)
155 loop
156
157 -- dbms_output.put_line('Activity Id : '||act_cur_rec_1.activity_id||' Session Id : '||ses_cur_rec.session_id);
158
159 select count(*), max(end_date_time)
160 into l_count_lc_segs, l_cal_end_date_time
161 FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE
162 media_id = act_cur_rec_1.media_id AND
163 resource_id = ses_cur_rec.resource_id AND
164 ACTIVE = l_media_not_active;
165
166 select count(*)
167 into l_count_lc_segs_active
168 FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE
169 media_id = act_cur_rec_1.media_id AND
170 resource_id = ses_cur_rec.resource_id AND
171 ACTIVE = l_media_active;
172
173 l_total_act := l_total_act + 1;
174
175 IF (l_count_lc_segs > 0)
176 THEN
177
178 update IEU_SH_ACTIVITIES set
179 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
180 END_DATE_TIME = l_cal_end_date_time,
181 ACTIVE_FLAG = NULL,
182 STATE_CODE = l_end_state_code,
183 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
184 WHERE ACTIVITY_ID = act_cur_rec_1.activity_id;
185
186 l_terminated_act := l_terminated_act + 1;
187
188 ELSE
189
190 IF (l_count_lc_segs_active > 0)
191
192 THEN
193 -- do nothing, skip the record
194 exit;
195
196 -- for activities with no MEDIA ITEM IDs
197
198 ELSE
199
200 -- hack for media cycle issues
201
202 IF (act_cur_rec_1.activity_type_code = 'MEDIA_CYCLE')
203 THEN
204
205 BEGIN
206 select decode ( greatest (max(end_date_time), max(deliver_date_time), max(begin_date_time) ), null,
207 act_cur_rec_1.begin_date_time, greatest (max(end_date_time), max(deliver_date_time), max(begin_date_time) ) )
208 into l_cycle_end_date_time
209 from IEU_SH_ACTIVITIES where
210 parent_cycle_id = act_cur_rec_1.activity_id;
211 EXCEPTION
212 WHEN OTHERS THEN
213 NULL;
214 END;
215
216 IF (l_cycle_end_date_time is NULL)
217 THEN
218 l_cycle_end_date_time := act_cur_rec_1.BEGIN_DATE_TIME;
219 END IF;
220
221 ELSE
222
223 l_cycle_end_date_time := act_cur_rec_1.BEGIN_DATE_TIME;
224
225 END IF;
226
227 update IEU_SH_ACTIVITIES set
228 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
229 END_DATE_TIME = l_cycle_end_date_time,
230 ACTIVE_FLAG = NULL,
231 STATE_CODE = l_end_state_code,
232 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
233 WHERE ACTIVITY_ID = act_cur_rec_1.activity_id;
234
235 l_terminated_act := l_terminated_act + 1;
236
237
238 END IF;
239 END IF;
240
241 end loop;
242
243 IF (l_terminated_act = l_total_act)
244 THEN
245
246 BEGIN
247 select max(end_date_time)
248 into l_end_date_time
249 from ieu_sh_activities
250 where session_id = ses_cur_rec.session_id;
251 EXCEPTION
252 WHEN OTHERS THEN
253 NULL;
254 END;
255
256 if (l_end_date_time is null) then
257 select begin_date_time
258 into l_end_date_time
259 from ieu_sh_sessions
260 where session_id = ses_cur_rec.session_id;
261 end if;
262
263 -- dbms_output.put_line(' end_date_time : '||l_end_date_time);
264
265 update IEU_SH_SESSIONS set
266 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
267 END_DATE_TIME = l_end_date_time,
268 ACTIVE_FLAG = NULL,
269 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
270 WHERE SESSION_ID = ses_cur_rec.session_id;
271
272 END IF;
273
274 ELSIF ( (l_last_update_date is null) and (l_last_update_time is null) )
275 THEN
276
277 -- dbms_output.put_line('Session id for sessions with no activities: '||ses_cur_rec.session_id);
278
279 select trunc(last_update_date), to_number(to_char(last_update_date, l_update_time_format))
280 into l_last_update_date, l_last_update_time
281 from ieu_sh_sessions ses
282 where ses.session_id = ses_cur_rec.session_id;
283
284 IF ( ( (trunc(sysdate) > l_last_update_date)
285 and
286 (trunc(sysdate) - l_last_update_date > 0.00000)
287 and
288 (trunc(sysdate) - l_last_update_date < 1.00000)
289 and
290 ( (23 - l_last_update_time + to_number(to_char(sysdate, l_update_time_format))
291 >= l_ses_timeout )
292 )
293 or
294 ( trunc(sysdate) - l_last_update_date > 1.00000 )
295 or
296 ((trunc(sysdate) = l_last_update_date )
297 and
298 ((to_number(to_char(sysdate, l_update_time_format)) - l_ses_timeout) >= l_last_update_time )
299 )
300 )
301 )
302 THEN
303
304 update IEU_SH_SESSIONS set
305 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
306 END_DATE_TIME = BEGIN_DATE_TIME,
307 ACTIVE_FLAG = NULL,
308 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
309 WHERE SESSION_ID = ses_cur_rec.session_id;
310
311 END IF;
312
313 END IF;
314
315 end loop;
316
317 end loop;
318
319 close l_applCursor;
320
321 -- Moved the cleanup of activities with no active sessions to the end
322
323 -- Kill all sesssions and activities pertaining to a particular user_name
324 -- if it is supplied as a parameter
325
326 IF (p_agent_name is not null)
327 THEN
328
329 DECLARE
330
331 l_upper_agent VARCHAR2(100);
332
333 BEGIN
334 l_upper_agent := upper(p_agent_name);
335
336
337 select resource_id into l_agent_resource_id
338 FROM JTF_RS_RESOURCE_EXTNS
339 where user_name=l_upper_agent;
340
341
342 EXCEPTION
343
344 when NO_DATA_FOUND THEN
348
345 NULL;
346
347 END;
349
350 IF (l_agent_resource_id is not NULL)
351 THEN
352
353 IEU_SH_OPEN_CURSOR(l_applCursor, p_appl_name);
354 loop
355 FETCH l_applCursor INTO l_applId;
356 EXIT WHEN l_applCursor%NOTFOUND;
357
358 for cur_ses in l_ses_resource(l_agent_resource_id, l_applId)
359 loop
360
361 /*
362 update IEU_SH_SESSIONS set
363 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
364 END_DATE_TIME = BEGIN_DATE_TIME,
365 ACTIVE_FLAG = NULL,
366 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
367 WHERE SESSION_ID = cur_ses.session_id;
368 */
369
370
371
372 for cur_act in l_act_cur1(cur_ses.session_id)
373 loop
374
375 l_count_lc_segs := 0;
376
377 -- Should delete this block in future, use l_agent_resource_id straight way
378 select RESOURCE_ID into
379 l_act_resource_id from
380 IEU_SH_SESSIONS where
381 SESSION_ID = cur_act.session_id;
382
383 select count(*), max(end_date_time)
384 into l_count_lc_segs, l_cal_end_date_time
385 FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE
386 media_id = cur_act.media_id AND
387 resource_id = l_act_resource_id AND
388 ACTIVE = l_media_not_active;
389
390 IF (l_count_lc_segs > 0 )
391 THEN
392 l_act_end_date := l_cal_end_date_time;
393 ELSE
394 l_act_end_date := cur_act.BEGIN_DATE_TIME;
395 END IF;
396
397 update IEU_SH_ACTIVITIES set
398 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
399 END_DATE_TIME = l_act_end_date,
400 ACTIVE_FLAG = NULL,
401 STATE_CODE = l_end_state_code,
402 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
403 WHERE ACTIVITY_ID = cur_act.activity_id;
404
405 end loop;
406
407 BEGIN
408 select max(end_date_time)
409 into l_end_date_time
410 from ieu_sh_activities
411 where session_id = cur_ses.session_id;
412 EXCEPTION
413 WHEN OTHERS THEN
414 NULL;
415 END;
416
417 if (l_end_date_time is null) then
418 select begin_date_time
419 into l_end_date_time
420 from ieu_sh_sessions
421 where session_id = cur_ses.session_id;
422 end if;
423
424 -- dbms_output.put_line(' end_date_time : '||l_end_date_time);
425
426 update IEU_SH_SESSIONS set
427 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
428 END_DATE_TIME = l_end_date_time,
429 ACTIVE_FLAG = NULL,
430 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
431 WHERE SESSION_ID = cur_ses.session_id;
432
433 end loop;
434
435 end loop;
436
437 close l_applCursor;
438
439 END IF;
440
441 END IF;
442
443 -- Delete duplicate sessions corresponding to a resource_id
444 IEU_SH_OPEN_CURSOR(l_applCursor, p_appl_name);
445
446 loop
447 FETCH l_applCursor INTO l_applId;
448 EXIT WHEN l_applCursor%NOTFOUND;
449
450 --INSERT into P_TEMP (cnt, msg) VALUES (l_cursor_index, l_applId);
451 --l_cursor_index := l_cursor_index + 1;
452
453 for cur_user in l_users(l_applId)
454 loop
455
456 for cur_ses in l_ses_resource(cur_user.resource_id, l_applId)
457 loop
458 IF (cur_ses.last_update_date < cur_user.last_update)
459 THEN
460
461 -- New introduction 09/03/03 not updating session table with last_update_time anymore
462 /*
463 update IEU_SH_SESSIONS set
464 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
465 END_DATE_TIME = cur_user.last_update,
466 ACTIVE_FLAG = NULL,
467 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
468 WHERE SESSION_ID = cur_ses.session_id;
469 */
470
471
472 -- New introduction 09/03/03 updating activity set as well
473
474 for cur_act in l_act_cur1(cur_ses.session_id)
475 loop
476
477 l_count_lc_segs := 0;
478
479 -- Should delete this block in future, use cur_user.resource_id straight way
480 select RESOURCE_ID into
481 l_act_resource_id from
482 IEU_SH_SESSIONS where
483 SESSION_ID = cur_act.session_id;
484
485 select count(*), max(end_date_time)
486 into l_count_lc_segs, l_cal_end_date_time
487 FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE
488 media_id = cur_act.media_id AND
489 resource_id = l_act_resource_id AND
490 ACTIVE = l_media_not_active;
491
492 IF (l_count_lc_segs > 0 )
493 THEN
494 l_act_end_date := l_cal_end_date_time;
495 ELSE
496 l_act_end_date := cur_act.BEGIN_DATE_TIME;
497 END IF;
498
499 update IEU_SH_ACTIVITIES set
500 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
501 END_DATE_TIME = l_act_end_date,
502 ACTIVE_FLAG = NULL,
503 STATE_CODE = l_end_state_code,
504 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
505 WHERE ACTIVITY_ID = cur_act.activity_id;
506
507 end loop;
508
509 BEGIN
510 select max(end_date_time)
511 into l_end_date_time
512 from ieu_sh_activities
513 where session_id = cur_ses.session_id;
514 EXCEPTION
515 WHEN OTHERS THEN
516 NULL;
517 END;
518
519 if (l_end_date_time is null) then
520 select begin_date_time
521 into l_end_date_time
522 from ieu_sh_sessions
523 where session_id = cur_ses.session_id;
524 end if;
525
526 -- dbms_output.put_line(' end_date_time : '||l_end_date_time);
527
528 update IEU_SH_SESSIONS set
529 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
530 END_DATE_TIME = l_end_date_time,
531 ACTIVE_FLAG = NULL,
532 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
533 WHERE SESSION_ID = cur_ses.session_id;
534
535 END IF;
536 end loop;
537
538 end loop;
539
540 end loop;
541
542 close l_applCursor;
543
544 -- Gracefully end all activities with no open sessions
545 IEU_SH_OPEN_CURSOR(l_applCursor, p_appl_name);
546
547 loop
548 FETCH l_applCursor INTO l_applId;
549 EXIT WHEN l_applCursor%NOTFOUND;
550
551 for act_cur_rec_2 in l_act_cur2(l_applId)
552 loop
553
554 -- dbms_output.put_line('Activity Id for activities with no sessions : '|| act_cur_rec_2.activity_id);
555 l_count_lc_segs := 0;
556
557 select RESOURCE_ID into
558 l_act_resource_id from
559 IEU_SH_SESSIONS where
560 SESSION_ID = act_cur_rec_2.session_id;
561
562 select count(*), max(end_date_time)
563 into l_count_lc_segs, l_cal_end_date_time
564 FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE
565 media_id = act_cur_rec_2.media_id AND
566 resource_id = l_act_resource_id AND
567 ACTIVE = l_media_not_active;
568
569 IF (l_count_lc_segs > 0 )
570 THEN
571 l_act_end_date := l_cal_end_date_time;
572 ELSE
573 l_act_end_date := act_cur_rec_2.BEGIN_DATE_TIME;
574 END IF;
575
576 update IEU_SH_ACTIVITIES set
577 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
578 END_DATE_TIME = l_act_end_date,
579 ACTIVE_FLAG = NULL,
580 STATE_CODE = l_end_state_code,
581 FORCE_CLOSED_BY_UWQ_FLAG = l_force_close_flag
582 WHERE ACTIVITY_ID = act_cur_rec_2.activity_id;
583
584 end loop;
585
586 end loop;
587
588 close l_applCursor;
589
590 EXCEPTION
591 WHEN OTHERS THEN
592 errbuf := sqlerrm;
593 retcode := sqlcode;
594
595 END IEU_SH_END_IDLE_TRANS;
596
597 PROCEDURE IEU_SH_OPEN_CURSOR(l_applCursor IN OUT NOCOPY t_cursor, p_appl_name IN NUMBER) IS
598 l_app_names VARCHAR2(32);
599 l_app_uwq VARCHAR2(32);
600 l_app_emc VARCHAR2(32);
601 BEGIN
602
603 l_app_names := 'IEU_SH_APPL_NAMES';
604 l_app_uwq := 'IEU_SH_APPL_UWQ';
605 l_app_emc := 'IEU_SH_APPL_EMC';
606
607 IF (p_appl_name = '1')
608 THEN
609 OPEN l_applCursor FOR
610 SELECT to_number(attribute2) APPL_ID
611 FROM FND_LOOKUP_VALUES
612 WHERE LOOKUP_TYPE = l_app_names and
613 LOOKUP_CODE = l_app_uwq;
614 ELSIF (p_appl_name = '2')
615 THEN
616 OPEN l_applCursor FOR
617 SELECT to_number(attribute2) APPL_ID
618 FROM FND_LOOKUP_VALUES
619 WHERE LOOKUP_TYPE = l_app_names and
620 LOOKUP_CODE = l_app_emc;
621 ELSIF (p_appl_name = '3')
622 THEN
623 OPEN l_applCursor FOR
624 SELECT to_number(attribute2) APPL_ID
625 FROM FND_LOOKUP_VALUES
626 WHERE LOOKUP_TYPE = l_app_names;
627 ELSE
628 RAISE_APPLICATION_ERROR (-20000,
629 'Input must be "1" or "2" or "3"');
630 END IF;
631 END IEU_SH_OPEN_CURSOR;
632
633 END IEU_SH_CON_PVT;
634