1 PACKAGE BODY igs_session_validity AS
2 /* $Header: IGSSS10B.pls 120.10 2006/08/24 07:33:01 bdeviset ship $ */
3
4 g_icx_session_timeout NUMBER := NVL(fnd_profile.value('ICX_SESSION_TIMEOUT'), 0);
5
6 FUNCTION validate_first_connect (p_first_connect DATE, p_limit_time NUMBER)
7 /*
8 || Change History :
9 || Who When What
10 ||
11 || ckasu 23-Aug-2004 Modified If Condition inorder as a part of
12 || bug 3855996.
13 */
14
15 RETURN BOOLEAN IS
16 BEGIN
17
18 -- If the diffence between Current Login Time and First connect time
19 -- is greater than Limit than the session is invalid
20
21 IF ( ((SYSDATE - p_first_connect)*24*60*60) > p_limit_time*60*60 ) THEN
22 RETURN FALSE;
23 ELSE
24 RETURN TRUE;
25 END IF;
26
27 END validate_first_connect;
28
29 FUNCTION validate_last_connect ( p_last_connect DATE)
30 /*
31 || Change History :
32 || Who When What
33 ||
34 || ckasu 23-Aug-2004 Modified If Condition inorder as a part of
35 || bug 3855996.
36 */
37
38 RETURN BOOLEAN IS
39 BEGIN
40
41 -- If the diffence between Current Login Time and Last connect time
42 -- is greater than icx_session_timeout than the session is invalid
43
44 IF ( ((SYSDATE - p_last_connect)*24*60*60) > g_icx_session_timeout*60 ) THEN
45 RETURN FALSE;
46 ELSE
47 RETURN TRUE;
48 END IF;
49
50 END validate_last_connect;
51
52 /**
53 This function checks the validity of an ICX_SESSION based on
54 1. Record not found in the ICX_SESSION
55 2. Disabled_flag
56 3. ICX_SESSION_TIMEOUT, last_connect
57 4. first_connect, limit_time
58 */
59 FUNCTION is_valid_session ( p_session_id NUMBER)
60 RETURN BOOLEAN IS
61 CURSOR session_cur IS
62 SELECT
63 first_connect,
64 last_connect,
65 limit_time,
66 disabled_flag
67 FROM
68 icx_sessions
69 WHERE
70 session_id = p_session_id;
71 session_rec session_cur%ROWTYPE;
72 BEGIN
73 OPEN session_cur;
74 FETCH session_cur INTO session_rec;
75 IF session_cur%NOTFOUND THEN
76 CLOSE session_cur;
77 RETURN FALSE;
78 END IF;
79 CLOSE session_cur;
80 IF session_rec.disabled_flag = 'Y' THEN
81 RETURN FALSE;
82 END IF;
83 IF g_icx_session_timeout = 0 THEN
84 RETURN validate_first_connect ( session_rec.first_connect, session_rec.limit_time);
85 ELSE
86 RETURN validate_last_connect ( session_rec.last_connect);
87 END IF;
88 END is_valid_session;
89
90 /**
91 This procedure cleans all the enrollment worksheet records that are added in
92 the previous sessions that are no longer valid
93 */
94 PROCEDURE clean_enroll_wrksht IS
95 /*
96 || Change History :
97 || Who When What
98 ||
99 || ckasu 23-Aug-2004 Added the If condition inorder to retain
100 || the Units added to Enrollment cart when
101 || the profile value is set 'N' as a part of
102 || bug 3847480.
103 || sgurusam 07-Jun-2005 EN317 Enhnacement
104 || svanukur 20-sep-2005 Added logic to rollback deletion of unit in case of exception, EN317 build
105 || bdeviset 24-mar-2006 Modified so that the profile purge cart is used only for admin as
106 || per bug# 5083862
107 || bdeviset 08-jn-2006 Modified such that in all the cases planning sheet error records/swap records are deleted.
108 For student irrespective of the profile cart units are also purged but for admin it is
109 done based on the profile. bug# 5306874
110 */
111
112 PRAGMA AUTONOMOUS_TRANSACTION;
113 /**
114 Cursor to select all the records which are added through the self service page
115 and are still in the enrollment worksheet
116 */
117
118 CURSOR all_sessions_cur IS
119 SELECT
120 DISTINCT session_id
121 FROM
122 igs_en_su_attempt_all
123 WHERE
124 cart = 'S';
125
126 /**
127 Cursor to select the enrollment worksheet records which are added in a session, not valid
128 anymore
129 */
130
131 CURSOR unit_attempt_del_cur (c_session_id NUMBER) IS
132 SELECT
133 rowid row_id, person_id
134 FROM
135 igs_en_su_attempt_all
136 WHERE
137 session_id = c_session_id AND
138 cart = 'S'
139 ORDER BY sup_unit_cd ASC;
140
141 l_ispurgeenabled VARCHAR2(3);
142
143 /**
144 Cursor to select the enrollment worksheet records which are added in a session by a student, not valid
145 anymore
146 */
147
148 CURSOR std_unit_attempt_del_cur (c_session_id NUMBER) IS
149 SELECT
150 rowid row_id, person_id
151 FROM
152 igs_en_su_attempt_all
153 WHERE
154 session_id = c_session_id AND
155 cart = 'S' AND
156 ss_source_ind <> 'A'
157 ORDER BY sup_unit_cd ASC;
158
159 /**
160 Cursor to select all the records which are added through the self service page
161 and are still in the enrollment worksheet except for the records added by admin
162 */
163 CURSOR all_stud_sessions_cur IS
164 SELECT
165 DISTINCT session_id
166 FROM
167 igs_en_su_attempt_all
168 WHERE cart = 'S'
169 AND ss_source_ind <> 'A';
170
171 /**
172 Cursor to select all the distinct session ids of cart, created from planned sheet and swap page.
173 */
174 CURSOR c_stud_cart_ses IS
175 SELECT DISTINCT session_id
176 FROM igs_en_su_attempt
177 WHERE cart = 'S'
178 AND ss_source_ind IN ('P','S');
179
180 /**
181 Cursor to select details of the cart, created from planned sheet and swap page.
182 */
183 CURSOR c_stud_cart_row (p_session_id igs_en_su_attempt.session_id%TYPE) IS
184 SELECT a.rowid, a.person_id,a.course_cd, a.uoo_id, a.ss_source_ind
185 FROM igs_en_su_attempt a
186 WHERE a.cart = 'S'
187 AND a.ss_source_ind IN ('P','S')
188 AND a.session_id = p_session_id
189 ORDER BY a.sup_unit_cd asc;
190
191 /**
192 Cursor to select rowid of error carts for deleting.
193 */
194 CURSOR c_plan_error( p_session_id igs_en_plan_units.session_id%TYPE,
195 p_person_id igs_en_plan_units.person_id%TYPE,
196 p_course_cd igs_en_plan_units.course_cd%TYPE,
197 p_uoo_id igs_en_plan_units.uoo_id%TYPE ) IS
198 SELECT ROWID
199 FROM igs_en_plan_units
200 WHERE person_id = p_person_id
201 AND course_cd = p_course_cd
202 AND uoo_id = p_uoo_id
203 AND cart_error_flag = 'Y'
204 AND session_id = p_session_id;
205
206 /**
207 Cursor to select value of term_cal_type and term_ci_sequence_number.
208 */
209 CURSOR c_cal_type( p_person_id igs_en_plan_units.person_id%TYPE,
210 p_course_cd igs_en_plan_units.course_cd%TYPE,
211 p_uoo_id igs_en_plan_units.uoo_id%TYPE ) IS
212 SELECT term_cal_type, term_ci_sequence_number
213 FROM igs_en_plan_units
214 WHERE person_id = p_person_id
215 AND course_cd = p_course_cd
216 AND uoo_id = p_uoo_id;
217
218 /*
219 curosor to select warnings of invalid sessions
220 */
221 CURSOR c_stud_warn_row(p_session_id IGS_EN_STD_WARNINGS.SESSION_ID%type) is
222 SELECT rowid
223 from IGS_EN_STD_WARNINGS
224 where SESSION_ID = p_session_id;
225
226 -- added by ckasu as a part of bug#4673919
227
228 CURSOR all_plan_session IS
229 SELECT DISTINCT session_id
230 FROM igs_en_plan_units
231 WHERE cart_error_flag= 'Y';
232
233 CURSOR c_get_all_plan_error_units(p_session_id IGS_EN_PLAN_UNITS.SESSION_ID%TYPE) IS
234 SELECT ROWID
235 FROM igs_en_plan_units
236 WHERE session_id = p_session_id
237 AND cart_error_flag = 'Y';
238
239 l_purge_admin_cart BOOLEAN;
240
241 BEGIN
242
243 -- delete unit attempts taken by swap, plan and student for invalida sessions
244
245 FOR c_stud_cart_ses_rec IN c_stud_cart_ses LOOP
246 -- if a unit attempt created by planning sheet is being deleted then the
247 -- planning sheet should be re-instated if it was already submitted.
248
249 IF NOT is_valid_session (c_stud_cart_ses_rec.session_id) THEN
250
251 FOR c_stud_warn_rec in c_stud_warn_row(c_stud_cart_ses_rec.session_id) LOOP
252
253 BEGIN
254 SAVEPOINT SP_WARN_REC;
255
256 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid =>c_stud_warn_rec.rowid );
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 ROLLBACK TO SP_WARN_REC;
261 NULL; --suppress the exception if any, since we are only trying to clear the warnings
262 END;
263
264 END LOOP; -- c_stud_cart_ses_rec
265
266
267
268 FOR c_stud_cart_rec IN c_stud_cart_row (c_stud_cart_ses_rec.session_id) LOOP
269 --create a savepoint here and rollback in case of errors, since an error in this package
270 --will prevent user from even logging in
271
272 BEGIN
273 SAVEPOINT SP_CART_REC;
274
275 -- Delete the cart record
276 igs_en_su_attempt_pkg.delete_row (c_stud_cart_rec.rowid);
277
278 IF c_stud_cart_rec.ss_source_ind = 'P' THEN
279 -- If there exists a term record with planning sheet marked as
280 -- submited for this unit attempt, then update that term record to PLAN.
281
282 --This loop is executed only once
283 FOR c_cal_type_rec IN c_cal_type (c_stud_cart_rec.person_id,
284 c_stud_cart_rec.course_cd,
285 c_stud_cart_rec.uoo_id)
286 LOOP
287
288 UPDATE igs_en_spa_terms spa SET spa.plan_sht_status = 'PLAN'
289 WHERE spa.plan_sht_status = 'SUB_PLAN'
290 AND spa.person_id = c_stud_cart_rec.person_id
291 AND spa.program_cd = c_stud_cart_rec.course_cd
292 AND spa.term_cal_type = c_cal_type_rec.term_cal_type
293 AND spa.term_sequence_number = c_cal_type_rec.term_ci_sequence_number
294 AND EXISTS ( SELECT pl.uoo_id FROM igs_en_plan_units pl WHERE pl.person_id=spa.person_id AND pl.course_cd = spa.program_cd AND
295 pl.term_cal_type = spa.term_cal_type AND pl.term_ci_sequence_number = spa.term_sequence_number AND pl.uoo_id =c_stud_cart_rec.uoo_id);
296
297 -- After updating exit the loop.
298 EXIT;
299 END LOOP;
300
301 -- delete planning sheet cart error records for the cart being deleted above
302 FOR c_plan_error_rec IN c_plan_error (c_stud_cart_ses_rec.session_id,
303 c_stud_cart_rec.person_id,
304 c_stud_cart_rec.course_cd,
305 c_stud_cart_rec.uoo_id)
306 LOOP
307 igs_en_plan_units_pkg.delete_row(c_plan_error_rec.rowid) ;
308 END LOOP;
309
310 END IF;
311 EXCEPTION
312 WHEN OTHERS THEN
313 ROLLBACK TO SP_CART_REC;
314 NULL; --suppress the exception if any, since we are only trying to clear the cart
315 END;
316 END LOOP; -- c_stud_cart_rec
317
318 END IF; -- isValidSession
319
320 END LOOP; -- c_stud_cart_ses_rec
321
322 l_purge_admin_cart := FALSE;
323 l_ispurgeenabled := FND_PROFILE.VALUE('IGS_PURGE_ENROLLMENT_CART');
324
325 IF l_ispurgeenabled = 'Y' THEN
326 l_purge_admin_cart := TRUE;
327 END IF;
328
329 -- planning sheet error records cant figured out whether they
330 -- are created by admin or student.So purging them.
331
332 -- code added by ckasu as a part of bug#4673919
333
334 FOR all_plan_session_rec IN all_plan_session LOOP
335
336 IF NOT is_valid_session ( all_plan_session_rec.session_id) THEN
337
338 FOR c_get_all_plan_error_units_rec IN c_get_all_plan_error_units(all_plan_session_rec.session_id) LOOP
339
340 BEGIN
341
342 SAVEPOINT SP_PLAN_ERROR_REC;
343 igs_en_plan_units_pkg.delete_row(c_get_all_plan_error_units_rec.rowid);
344
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 ROLLBACK TO SP_PLAN_ERROR_REC;
349 NULL; --suppress the exception if any, since we are only trying to clear the warnings
350 END;
351
352 END LOOP; -- end of FOR c_get_all_plan_error_units_rec IN c_get_all_plan_error_units LOOP
353
354 FOR c_stud_warn_rec in c_stud_warn_row(all_plan_session_rec.session_id) LOOP
355
356 BEGIN
357
358 SAVEPOINT SP_WARN_REC;
359
360 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid =>c_stud_warn_rec.rowid );
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 ROLLBACK TO SP_WARN_REC;
365 NULL; --suppress the exception if any, since we are only trying to clear the warnings
366 END;
367
368 END LOOP; -- end of c_stud_warn_rec in c_stud_warn_row(c_stud_cart_ses_rec.session_id) LOOP
369
370
371 END IF; -- end of IF NOT is_valid_session ( all_plan_session_rec.session_id) THEN
372
373 END LOOP; -- end of FOR all_plan_session_rec IN all_plan_session LOOP
374
375 -- end of code added by ckasu as a part of bug#4673919
376
377 IF l_purge_admin_cart THEN
378 -- if the profile is set delete all the inactive cart records
379
380 FOR all_sessions_rec IN all_sessions_cur LOOP
381
382 IF NOT is_valid_session ( all_sessions_rec.session_id) THEN
383
384 FOR c_stud_warn_rec in c_stud_warn_row(all_sessions_rec.session_id) LOOP
385
386 BEGIN
387 SAVEPOINT SP_WARN_REC;
388
389 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid =>c_stud_warn_rec.rowid );
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 ROLLBACK TO SP_WARN_REC;
394 NULL; --suppress the exception if any, since we are only trying to clear the warnings
395 END;
396
397 END LOOP; -- end of FOR c_stud_warn_rec in c_stud_warn_row(all_sessions_rec.session_id) LOOP
398
399 FOR unit_attempt_del_rec IN unit_attempt_del_cur ( all_sessions_rec.session_id) LOOP
400 BEGIN
401 SAVEPOINT SP_SESSION_VAL_SUA_DEL;
402 igs_en_su_attempt_pkg.delete_row ( unit_attempt_del_rec.row_id);
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 ROLLBACK TO SP_SESSION_VAL_SUA_DEL;
407 NULL; --suppress the exception if any, since we are only trying to clear the cart
408 END;
409 END LOOP; -- end of FOR unit_attempt_del_rec IN unit_attempt_del_cur ( all_sessions_rec.session_id) LOOP
410
411 END IF; -- end of IF NOT is_valid_session ( all_sessions_rec.session_id) THEN
412
413 END LOOP; -- end of FOR all_sessions_rec IN all_sessions_cur LOOP
414
415 ELSE
416 -- if the profile is not set delete all the inactive cart records created by student
417
418 FOR all_stud_sessions_rec IN all_stud_sessions_cur LOOP
419
420 IF NOT is_valid_session ( all_stud_sessions_rec.session_id) THEN
421
422 FOR c_stud_warn_rec in c_stud_warn_row(all_stud_sessions_rec.session_id) LOOP
423
424 BEGIN
425 SAVEPOINT SP_WARN_REC;
426
427 IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid =>c_stud_warn_rec.rowid );
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 ROLLBACK TO SP_WARN_REC;
432 NULL; --suppress the exception if any, since we are only trying to clear the warnings
433 END;
434
435 END LOOP; -- end of FOR c_stud_warn_rec in c_stud_warn_row(all_sessions_rec.session_id) LOOP
436
437 FOR std_unit_attempt_del_rec IN std_unit_attempt_del_cur ( all_stud_sessions_rec.session_id) LOOP
438 BEGIN
439 SAVEPOINT SP_SESSION_VAL_SUA_DEL;
440 igs_en_su_attempt_pkg.delete_row ( std_unit_attempt_del_rec.row_id);
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 ROLLBACK TO SP_SESSION_VAL_SUA_DEL;
445 NULL; --suppress the exception if any, since we are only trying to clear the cart
446 END;
447 END LOOP; -- end of FOR std_unit_attempt_del_rec IN unit_attempt_del_cur ( all_sessions_rec.session_id) LOOP
448
449 END IF; -- end of IF NOT is_valid_session ( all_sessions_rec.session_id) THEN
450
451 END LOOP; -- end of FOR all_sessions_rec IN all_sessions_cur LOOP
452
453 END IF;
454
455
456 -- this has been added to commit this transaction
457 -- if it not committed here then a lock is aquired by the
458 -- IgsApplication module which is the root AM on the table
459 -- and from there on, no transaction can be performed on this table
460 -- hence runing this procedure in an autonomous transaction.
461 -- commiting the same
462 --amuthu 3-APR-2002
463 COMMIT;
464 END clean_enroll_wrksht;
465 END igs_session_validity;
[Home] [Help]