1 PACKAGE BODY IGS_EN_FUTURE_DT_TRANS AS
2 /* $Header: IGSEN83B.pls 120.8 2005/12/08 07:36:14 appldev noship $ */
3
4 g_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 e_resource_busy EXCEPTION;
6 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
7
8 ---------------------------------------------------------------------------------
9
10 -- PRIVATE FUNCTIONS
11 ---------------------------------------------------------------------------------
12
13 FUNCTION is_career_model_enabled RETURN BOOLEAN AS
14 -------------------------------------------------------------------------------------------
15 --Created by : Chandrasekhar Kasu, Oracle IDC
16 --Date created: 20-Nov-2004
17 -- Purpose : returns True when Career model is enabled else False.
18 --Change History:
19 --Who When What
20
21 -------------------------------------------------------------------------------------------
22 BEGIN
23
24 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
25 RETURN TRUE;
26 ELSE
27 RETURN FALSE;
28 END IF;
29
30 END is_career_model_enabled;
31
32 FUNCTION is_tranfer_across_careers(
33 p_src_program_cd IN VARCHAR2,
34 p_src_progam_ver IN NUMBER,
35 p_dest_program_cd IN VARCHAR2,
36 p_dest_prog_ver IN NUMBER,
37 p_src_career_type OUT NOCOPY VARCHAR2
38 ) RETURN BOOLEAN AS
39
40 -------------------------------------------------------------------------------------------
41 --Created by : Chandrasekhar Kasu, Oracle IDC
42 --Date created: 20-Nov-2004
43 -- Purpose : This function returns when Transfer is across careers and
44 -- false when transfer is with in the careers
45 --Change History:
46 --Who When What
47
48 -------------------------------------------------------------------------------------------
49
50 CURSOR c_get_career_type(c_program_cd VARCHAR2,c_program_ver NUMBER) IS
51 SELECT course_type
52 FROM IGS_PS_VER
53 WHERE course_cd = c_program_cd AND
54 version_number = c_program_ver;
55 l_src_prgm_career IGS_PS_VER.COURSE_TYPE%TYPE;
56 l_dest_prgm_career IGS_PS_VER.COURSE_TYPE%TYPE;
57
58 BEGIN
59
60 OPEN c_get_career_type(p_src_program_cd,p_src_progam_ver);
61 FETCH c_get_career_type INTO l_src_prgm_career;
62 CLOSE c_get_career_type;
63 OPEN c_get_career_type(p_dest_program_cd,p_dest_prog_ver);
64 FETCH c_get_career_type INTO l_dest_prgm_career;
65 CLOSE c_get_career_type;
66 IF (l_src_prgm_career <> l_dest_prgm_career) THEN
67 p_src_career_type := l_src_prgm_career;
68 RETURN TRUE;
69 ELSE
70 RETURN FALSE;
71 END IF;
72
73 END is_tranfer_across_careers;
74
75
76 PROCEDURE log_err_messages(
77 p_msg_count IN NUMBER,
78 p_msg_data IN VARCHAR2
79 ) AS
80 -------------------------------------------------------------------------------------------
81 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
82 -- purpose : this methos concatenates al the warning and error messages delimited by '<br>'
83 -- that were recieved during program transfer.
84 --Change History:
85 --Who When What
86
87 --------------------------------------------------------------------------------------------
88
89
90 l_msg_count NUMBER(4);
91 l_msg_data VARCHAR2(4000);
92 l_enc_msg VARCHAR2(2000);
93 l_msg_index NUMBER(4);
94 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
95 l_warn_and_err_msg VARCHAR2(5000);
96
97 BEGIN
98
99 l_msg_count := p_msg_count;
100 l_msg_data := p_msg_data;
101 l_warn_and_err_msg := null;
102
103 IF l_msg_count =1 THEN
104 FND_MESSAGE.SET_ENCODED(l_msg_data);
105 l_msg_text := FND_MESSAGE.GET;
106 FND_FILE.PUT_LINE (FND_FILE.LOG, l_msg_text);
107
108 ELSIF l_msg_count > 1 THEN
109 FOR l_index IN 1..NVL(l_msg_count,0)
110 LOOP
111 FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST,
112 FND_API.G_TRUE,
113 l_enc_msg,
114 l_msg_index);
115 FND_MESSAGE.SET_ENCODED(l_enc_msg);
116 l_msg_text := FND_MESSAGE.GET;
117 FND_FILE.PUT_LINE (FND_FILE.LOG, l_msg_text);
118 FND_MSG_PUB.DELETE_MSG(l_msg_index);
119
120 END LOOP;
121 END IF;
122
123 END log_err_messages;
124
125
126 PROCEDURE del_gua(
127 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
128 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
129 IS
130 BEGIN -- del_gua
131 -- Delete IGS_GR_GRADUAND_PKG records
132 DECLARE
133
134 CURSOR c_gua IS
135 SELECT create_dt
136 FROM IGS_GR_GRADUAND gua
137 WHERE gua.person_id = p_person_id
138 AND gua.course_cd = p_course_cd;
139
140 CURSOR c_gua_del (
141 cp_create_dt IGS_GR_GRADUAND.create_dt%TYPE) IS
142 SELECT rowid
143 FROM IGS_GR_GRADUAND gua
144 WHERE gua.person_id = p_person_id
145 AND gua.create_dt = cp_create_dt
146 FOR UPDATE OF gua.LAST_UPDATE_DATE NOWAIT ;
147
148
149 CURSOR c_gach (
150 cp_create_dt IGS_GR_AWD_CRMN_HIST.create_dt%TYPE) IS
151 SELECT gach.gach_id
152 FROM IGS_GR_AWD_CRMN_HIST gach
153 WHERE gach.person_id = p_person_id
154 AND gach.create_dt = cp_create_dt;
155
156 CURSOR c_gach_del (
157 cp_gach_id IGS_GR_AWD_CRMN_HIST.gach_id%TYPE) IS
158 SELECT rowid
159 FROM IGS_GR_AWD_CRMN_HIST gach
160 WHERE gach.gach_id = cp_gach_id
161 FOR UPDATE OF gach.LAST_UPDATE_DATE NOWAIT ;
162
163 CURSOR c_gac (
164 cp_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE) IS
165 SELECT gac.gac_id
166 FROM IGS_GR_AWD_CRMN gac
167 WHERE gac.person_id = p_person_id
168 AND gac.create_dt = cp_create_dt;
169
170 CURSOR c_gac_del (
171 cp_gac_id IGS_GR_AWD_CRMN.gac_id%TYPE) IS
172 SELECT rowid
173 FROM IGS_GR_AWD_CRMN gac
174 WHERE gac.gac_id = cp_gac_id
175 FOR UPDATE OF gac.LAST_UPDATE_DATE NOWAIT ;
176
177 v_gua_del_exists c_gua_del%ROWTYPE;
178 l_entity_name VARCHAR2(30);
179
180 BEGIN
181
182 FOR v_gua_rec IN c_gua LOOP
183 FOR v_gach_rec IN c_gach (v_gua_rec.create_dt) LOOP
184 BEGIN
185 -- Delete unconfirmed IGS_GR_AWD_CRMN_HIST records
186 FOR c_gach_del_rec in c_gach_del(v_gach_rec.gach_id)
187 LOOP
188
189 IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW( X_ROWID => c_gach_del_rec.ROWID );
190
191 END LOOP;
192
193 EXCEPTION
194 WHEN e_resource_busy THEN
195 IF c_gach_del%ISOPEN THEN
196 CLOSE c_gach_del;
197 END IF;
198 l_entity_name := 'IGS_GR_AWD_CRMN_HIST';
199 EXIT;
200 END;
201 END LOOP;
202 IF l_entity_name IS NOT NULL THEN
203 EXIT;
204 END IF;
205
206 FOR v_gca_rec IN c_gac(v_gua_rec.create_dt) LOOP
207 BEGIN
208 -- Delete unconfirmed IGS_GR_AWD_CRMN records
209 FOR c_gac_del_rec IN c_gac_del(
210 v_gca_rec.gac_id) LOOP
211 IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
212 X_ROWID => c_gac_del_rec.ROWID );
213 END LOOP;
214 EXCEPTION
215 WHEN e_resource_busy THEN
216 IF c_gac_del%ISOPEN THEN
217 CLOSE c_gac_del;
218 END IF;
219 l_entity_name := 'IGS_GR_AWD_CRMN';
220 EXIT;
221 END;
222 END LOOP;
223
224 IF l_entity_name IS NOT NULL THEN
225 EXIT;
226 END IF;
227
228 -- Delete unconfirmed IGS_GR_GRADUAND records
229 FOR v_gua_del_exists IN c_gua_del(v_gua_rec.create_dt) LOOP
230 IGS_GR_GRADUAND_PKG.DELETE_ROW(
231 X_ROWID => v_gua_del_exists.rowid );
232 END LOOP;
233
234 END LOOP;
235
236 EXCEPTION
237 WHEN e_resource_busy THEN
238 IF c_gua%ISOPEN THEN
239 CLOSE c_gua;
240 END IF;
241 IF c_gua_del%ISOPEN THEN
242 CLOSE c_gua_del;
243 END IF;
244 IF c_gach%ISOPEN THEN
245 CLOSE c_gach;
246 END IF;
247 IF c_gach_del%ISOPEN THEN
248 CLOSE c_gach_del;
249 END IF;
250 IF c_gac%ISOPEN THEN
251 CLOSE c_gac;
252 END IF;
253 IF c_gac_del%ISOPEN THEN
254 CLOSE c_gac_del;
255 END IF;
256 WHEN OTHERS THEN
257 IF c_gua%ISOPEN THEN
258 CLOSE c_gua;
259 END IF;
260 IF c_gua_del%ISOPEN THEN
261 CLOSE c_gua_del;
262 END IF;
263 IF c_gach%ISOPEN THEN
264 CLOSE c_gach;
265 END IF;
266 IF c_gach_del%ISOPEN THEN
267 CLOSE c_gach_del;
268 END IF;
269 IF c_gac%ISOPEN THEN
270 CLOSE c_gac;
271 END IF;
272 IF c_gac_del%ISOPEN THEN
273 CLOSE c_gac_del;
274 END IF;
275
276 END;
277 END del_gua;
278
279
280 PROCEDURE del_esaa(
281 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
282 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
283 IS
284 BEGIN -- del_esaa
285 -- Delete IGS_EN_SPA_AWD_AIM records
286 DECLARE
287
288 CURSOR c_esaa IS
289 SELECT award_cd
290 FROM IGS_EN_SPA_AWD_AIM esaa
291 WHERE esaa.person_id = p_person_id
292 AND esaa.course_cd = p_course_cd;
293
294 CURSOR c_esaa_del (
295 cp_award_cd IGS_EN_SPA_AWD_AIM.award_cd%TYPE) IS
296 SELECT rowid
297 FROM IGS_EN_SPA_AWD_AIM esaa
298 WHERE esaa.person_id = p_person_id
299 AND esaa.course_cd = p_course_cd
300 AND esaa.award_cd = cp_award_cd
301 FOR UPDATE OF esaa.LAST_UPDATE_DATE NOWAIT ;
302
303 v_esaa_del_exists c_esaa_del%ROWTYPE;
304
305 BEGIN
306
307 FOR v_esaa_rec IN c_esaa LOOP
308 -- Delete IGS_EN_SPA_AWD_AIM records
309 FOR v_esaa_del_exists IN c_esaa_del(v_esaa_rec.award_cd) LOOP
310
311 IGS_EN_SPA_AWD_AIM_PKG.DELETE_ROW(X_ROWID => v_esaa_del_exists.rowid );
312
313 END LOOP;
314
315 END LOOP;
316
317 EXCEPTION
318 WHEN e_resource_busy THEN
319 IF c_esaa%ISOPEN THEN
320 CLOSE c_esaa;
321 END IF;
322 IF c_esaa_del%ISOPEN THEN
323 CLOSE c_esaa_del;
324 END IF;
325 WHEN OTHERS THEN
326 IF c_esaa%ISOPEN THEN
327 CLOSE c_esaa;
328 END IF;
329 IF c_esaa_del%ISOPEN THEN
330 CLOSE c_esaa_del;
331 END IF;
332
333 END;
334 END del_esaa;
335
336 PROCEDURE del_gsa(
337 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
338 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
339 IS
340 BEGIN -- del_gsa
341 -- (1) Delete IGS_GR_SPECIAL_AWARD records
342 DECLARE
343 CURSOR c_gsa IS
344 SELECT award_cd,award_dt
345 FROM IGS_GR_SPECIAL_AWARD gsa
346 WHERE gsa.person_id = p_person_id
347 AND gsa.course_cd = p_course_cd;
348
349 CURSOR c_gsa_del (
350 cp_award_cd IGS_GR_SPECIAL_AWARD.award_cd%TYPE,
351 cp_award_dt IGS_GR_SPECIAL_AWARD.award_dt%TYPE) IS
352 SELECT rowid
353 FROM IGS_GR_SPECIAL_AWARD gsa
354 WHERE gsa.person_id = p_person_id
355 AND gsa.course_cd = p_course_cd
356 AND gsa.award_cd = cp_award_cd
357 AND gsa.award_dt = cp_award_dt
358 FOR UPDATE OF gsa.LAST_UPDATE_DATE NOWAIT ;
359
360 v_gsa_del_exists c_gsa_del%ROWTYPE;
361
362 BEGIN
363
364 FOR v_gsa_rec IN c_gsa LOOP
365 FOR v_gsa_del_exists IN c_gsa_del(v_gsa_rec.award_cd,
366 v_gsa_rec.award_dt ) LOOP
367
368 IGS_GR_SPECIAL_AWARD_PKG.DELETE_ROW(X_ROWID => v_gsa_del_exists.rowid );
369
370 END LOOP;
371
372 END LOOP;
373
374 EXCEPTION
375 WHEN e_resource_busy THEN
376 IF c_gsa%ISOPEN THEN
377 CLOSE c_gsa;
378 END IF;
379 IF c_gsa_del%ISOPEN THEN
380 CLOSE c_gsa_del;
381 END IF;
382 WHEN OTHERS THEN
383 IF c_gsa%ISOPEN THEN
384 CLOSE c_gsa;
385 END IF;
386 IF c_gsa_del%ISOPEN THEN
387 CLOSE c_gsa_del;
388 END IF;
389
390 END;
391 END del_gsa;
392
393 PROCEDURE del_hssc(
394 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
395 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
396 IS
397 BEGIN -- del_hssc
398 -- Delete IGS_HE_ST_SPA_CC records
399 DECLARE
400
401 CURSOR c_hssc IS
402 SELECT he_spa_cc_id
403 FROM IGS_HE_ST_SPA_CC hssc
404 WHERE hssc.person_id = p_person_id
405 AND hssc.course_cd = p_course_cd;
406
407 CURSOR c_hssc_del (cp_he_spa_cc_id IGS_HE_ST_SPA_CC.he_spa_cc_id%TYPE) IS
408 SELECT rowid
409 FROM IGS_HE_ST_SPA_CC hssc
410 WHERE hssc.he_spa_cc_id = cp_he_spa_cc_id
411 FOR UPDATE OF hssc.LAST_UPDATE_DATE NOWAIT ;
412
413 v_hssc_del_exists c_hssc_del%ROWTYPE;
414
415 BEGIN
416 FOR v_hssc_rec IN c_hssc LOOP
417 -- Delete IGS_HE_ST_SPA_CC records
418 FOR v_hssc_del_exists IN c_hssc_del(v_hssc_rec.he_spa_cc_id) LOOP
419
420 IGS_HE_ST_SPA_CC_PKG.DELETE_ROW(X_ROWID => v_hssc_del_exists.ROWID );
421
422 END LOOP;
423
424 END LOOP;
425
426 EXCEPTION
427 WHEN e_resource_busy THEN
428 IF c_hssc%ISOPEN THEN
429 CLOSE c_hssc;
430 END IF;
431 IF c_hssc_del%ISOPEN THEN
432 CLOSE c_hssc_del;
433 END IF;
434 WHEN OTHERS THEN
435 IF c_hssc%ISOPEN THEN
436 CLOSE c_hssc;
437 END IF;
438 IF c_hssc_del%ISOPEN THEN
439 CLOSE c_hssc_del;
440 END IF;
441
442 END;
443 END del_hssc;
444
445 PROCEDURE del_hssa(
446 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
447 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
448 IS
449 BEGIN -- del_hssa
450 -- Delete IGS_HE_ST_SPA records
451 DECLARE
452 CURSOR c_hssa IS
453 SELECT hesa_st_spa_id,person_id,course_cd
454 FROM IGS_HE_ST_SPA hssa
455 WHERE hssa.person_id = p_person_id
456 AND hssa.course_cd = p_course_cd;
457
458 CURSOR c_hssa_del (
459 cp_hesa_st_spa_id IGS_HE_ST_SPA.hesa_st_spa_id%TYPE) IS
460 SELECT rowid
461 FROM IGS_HE_ST_SPA hssa
462 WHERE hssa.hesa_st_spa_id = cp_hesa_st_spa_id
463 FOR UPDATE OF hssa.LAST_UPDATE_DATE NOWAIT ;
464
465
466 CURSOR c_hssua (
467 cp_person_id IGS_HE_ST_SPA_UT.person_id%TYPE,
468 cp_course_cd IGS_HE_ST_SPA.course_cd%TYPE) IS
469 SELECT hesa_st_spau_id
470 FROM IGS_HE_ST_SPA_UT hssua
471 WHERE hssua.person_id = cp_person_id
472 AND hssua.course_cd = cp_course_cd ;
473
474 CURSOR c_hssua_del (
475 cp_hesa_st_spau_id IGS_HE_ST_SPA_UT.hesa_st_spau_id%TYPE) IS
476 SELECT rowid
477 FROM IGS_HE_ST_SPA_UT hssua
478 WHERE hssua.hesa_st_spau_id = cp_hesa_st_spau_id
479 FOR UPDATE OF hssua.LAST_UPDATE_DATE NOWAIT ;
480
481 v_hssa_del_exists c_hssa_del%ROWTYPE;
482 l_entity_name VARCHAR2(30);
483
484
485 BEGIN
486
487 FOR v_hssa_rec IN c_hssa LOOP
488 FOR v_hssua_rec IN c_hssua (v_hssa_rec.person_id,
489 v_hssa_rec.course_cd ) LOOP
490 BEGIN
491 -- Delete unconfirmed IGS_HE_ST_SPA_UT records
492 FOR v_hssua_del_rec in c_hssua_del(v_hssua_rec.hesa_st_spau_id)
493
494 LOOP
495 IGS_HE_ST_SPA_UT_ALL_PKG.DELETE_ROW(
496 X_ROWID => v_hssua_del_rec.ROWID );
497 END LOOP;
498 EXCEPTION
499 WHEN e_resource_busy THEN
500 IF c_hssua_del%ISOPEN THEN
501 CLOSE c_hssua_del;
502 END IF;
503 l_entity_name := 'IGS_HE_ST_SPA_UT_ALL';
504 EXIT;
505 END;
506 END LOOP;
507 IF l_entity_name IS NOT NULL THEN
508 EXIT;
509 END IF;
510 -- Delete IGS_HE_ST_SPA records
511 FOR v_hssa_del_exists IN c_hssa_del(v_hssa_rec.hesa_st_spa_id) LOOP
512
513 IGS_HE_ST_SPA_ALL_PKG.DELETE_ROW(
514 X_ROWID => v_hssa_del_exists.rowid );
515 END LOOP;
516 END LOOP;
517
518 EXCEPTION
519 WHEN e_resource_busy THEN
520 IF c_hssa%ISOPEN THEN
521 CLOSE c_hssa;
522 END IF;
523 IF c_hssa_del%ISOPEN THEN
524 CLOSE c_hssa_del;
525 END IF;
526 IF c_hssua%ISOPEN THEN
527 CLOSE c_hssua;
528 END IF;
529 IF c_hssua_del%ISOPEN THEN
530 CLOSE c_hssua_del;
531 END IF;
532 WHEN OTHERS THEN
533 IF c_hssa%ISOPEN THEN
534 CLOSE c_hssa;
535 END IF;
536 IF c_hssa_del%ISOPEN THEN
537 CLOSE c_hssa_del;
538 END IF;
539 IF c_hssua%ISOPEN THEN
540 CLOSE c_hssua;
541 END IF;
542 IF c_hssua_del%ISOPEN THEN
543 CLOSE c_hssua_del;
544 END IF;
545
546 END;
547 END del_hssa;
548
549 ------------------------------------------------------------------------------------------------------------
550 PROCEDURE del_pr_rule_appl(
551 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
552 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
553 IS
554 BEGIN -- del_pr_rule_appl
555 -- Delete IGS_PR_RU_APPL records
556 DECLARE
557 CURSOR c_pra IS
558 SELECT progression_rule_cat,sequence_number
559 FROM IGS_PR_RU_APPL pra
560 WHERE pra.sca_person_id = p_person_id
561 AND pra.sca_course_cd = p_course_cd;
562
563 CURSOR c_pra_del (
564 cp_progression_rule_cat IGS_PR_RU_APPL.progression_rule_cat%TYPE,
565 cp_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE) IS
566 SELECT rowid,pra.*
567 FROM IGS_PR_RU_APPL pra
568 WHERE pra.progression_rule_cat = cp_progression_rule_cat
569 AND pra.sequence_number = cp_sequence_number
570 FOR UPDATE OF pra.LAST_UPDATE_DATE NOWAIT ;
571
572 v_pra_upd_exists c_pra_del%ROWTYPE;
573
574 BEGIN
575
576 FOR v_pra_rec IN c_pra LOOP
577 -- Delete IGS_PR_RU_APPL records
578 FOR v_pra_upd_exists IN c_pra_del(v_pra_rec.progression_rule_cat,
579 v_pra_rec.sequence_number ) LOOP
580 -- DELETE THE RECORD
581 IGS_PR_RU_APPL_PKG.DELETE_ROW (X_ROWID => v_pra_upd_exists.rowid) ;
582
583 END LOOP;
584
585 END LOOP;
586
587 EXCEPTION
588 WHEN e_resource_busy THEN
589 IF c_pra%ISOPEN THEN
590 CLOSE c_pra;
591 END IF;
592 IF c_pra_del%ISOPEN THEN
593 CLOSE c_pra_del;
594 END IF;
595 WHEN OTHERS THEN
596 IF c_pra%ISOPEN THEN
597 CLOSE c_pra;
598 END IF;
599 IF c_pra_del%ISOPEN THEN
600 CLOSE c_pra_del;
601 END IF;
602
603 END;
604
605 END del_pr_rule_appl;
606
607
608 PROCEDURE del_psaa(
609 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
610 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
611 IS
612 BEGIN -- del_psaa
613 -- Delete IGS_PS_STDNT_APV_ALT records
614 DECLARE
615
616 CURSOR c_psaa IS
617 SELECT exit_course_cd,exit_version_number
618 FROM IGS_PS_STDNT_APV_ALT psaa
619 WHERE psaa.person_id = p_person_id
620 AND psaa.course_cd = p_course_cd;
621
622 CURSOR c_psaa_del (
623 cp_exit_course_cd IGS_PS_STDNT_APV_ALT.exit_course_cd%TYPE,
624 cp_exit_version_number IGS_PS_STDNT_APV_ALT.exit_version_number%TYPE) IS
625 SELECT rowid
626 FROM IGS_PS_STDNT_APV_ALT psaa
627 WHERE psaa.person_id = p_person_id
628 AND psaa.course_cd = p_course_cd
629 AND psaa.exit_course_cd = cp_exit_course_cd
630 AND psaa.exit_version_number = cp_exit_version_number
631 FOR UPDATE OF psaa.LAST_UPDATE_DATE NOWAIT ;
632
633 v_psaa_del_exists c_psaa_del%ROWTYPE;
634
635 BEGIN
636
637 FOR v_psaa_rec IN c_psaa LOOP
638 -- Delete IGS_PS_STDNT_APV_ALT records
639 FOR v_psaa_del_exists IN c_psaa_del(v_psaa_rec.exit_course_cd,
640 v_psaa_rec.exit_version_number ) LOOP
641
642 IGS_PS_STDNT_APV_ALT_PKG.DELETE_ROW(X_ROWID => v_psaa_del_exists.rowid);
643
644 END LOOP;
645 END LOOP;
646
647 EXCEPTION
648 WHEN e_resource_busy THEN
649 IF c_psaa%ISOPEN THEN
650 CLOSE c_psaa;
651 END IF;
652 IF c_psaa_del%ISOPEN THEN
653 CLOSE c_psaa_del;
654 END IF;
655 WHEN OTHERS THEN
656 IF c_psaa%ISOPEN THEN
657 CLOSE c_psaa;
658 END IF;
659 IF c_psaa_del%ISOPEN THEN
660 CLOSE c_psaa_del;
661 END IF;
662
663 END;
664
665 END del_psaa;
666
667
668 PROCEDURE del_susa(
669 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
670 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
671 IS
672 BEGIN -- del_susa
673 -- (2) Delete IGS_AS_SU_SETATMPT records
674 DECLARE
675 CURSOR c_susa IS
676 SELECT susa.unit_set_cd,susa.sequence_number
677 FROM IGS_AS_SU_SETATMPT susa
678 WHERE susa.person_id = p_person_id
679 AND susa.course_cd = p_course_cd
680 START WITH
681 susa.person_id = p_person_id AND
682 susa.course_cd = p_course_cd AND
683 susa.parent_unit_set_cd IS NULL
684 CONNECT BY
685 PRIOR susa.person_id = p_person_id AND
686 PRIOR susa.course_cd = p_course_cd AND
687 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
688 PRIOR susa.sequence_number = susa.parent_sequence_number
689 ORDER BY LEVEL DESC;
690
691 CURSOR c_susa_del (
692 cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
693 cp_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
694 SELECT ROWID, susa.*
695 FROM IGS_AS_SU_SETATMPT susa
696 WHERE susa.person_id = p_person_id
697 AND susa.course_cd = p_course_cd
698 AND susa.unit_set_cd = cp_unit_set_cd
699 AND susa.sequence_number = cp_sequence_number
700 FOR UPDATE OF
701 susa.LAST_UPDATE_DATE NOWAIT;
702
703 CURSOR c_hes ( cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
704 cp_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
705 SELECT hesa_en_susa_id
706 FROM IGS_HE_EN_SUSA hes
707 WHERE hes.person_id = p_person_id
708 AND hes.course_cd = p_course_cd
709 AND hes.unit_set_cd = cp_unit_set_cd
710 AND hes.sequence_number = cp_sequence_number;
711
712 CURSOR c_hes_del (
713 cp_hesa_en_susa_id IGS_HE_EN_SUSA.hesa_en_susa_id%TYPE) IS
714 SELECT rowid
715 FROM IGS_HE_EN_SUSA hes
716 WHERE hes.hesa_en_susa_id = cp_hesa_en_susa_id
717 FOR UPDATE OF hes.LAST_UPDATE_DATE NOWAIT ;
718
719 CURSOR c_hesc ( cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
720 cp_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
721 SELECT he_susa_cc_id
722 FROM IGS_HE_EN_SUSA_CC hesc
723 WHERE hesc.person_id = p_person_id
724 AND hesc.course_cd = p_course_cd
725 AND hesc.unit_set_cd = cp_unit_set_cd
726 AND hesc.sequence_number = cp_sequence_number;
727
728 CURSOR c_hesc_del (
729 cp_he_susa_cc_id IGS_HE_EN_SUSA_CC.he_susa_cc_id%TYPE) IS
730 SELECT rowid
731 FROM IGS_HE_EN_SUSA_CC hesc
732 WHERE hesc.he_susa_cc_id = cp_he_susa_cc_id
733 FOR UPDATE OF hesc.LAST_UPDATE_DATE NOWAIT ;
734
735 v_susa_del_exists c_susa_del%ROWTYPE;
736 L_ROWID VARCHAR2(25);
737 v_error_flag BOOLEAN DEFAULT FALSE;
738 l_entity_name VARCHAR2(30);
739
740 BEGIN
741 v_error_flag := FALSE;
742 -- Prevent admission application validation in database trigger
743 -- Inserts a record into the s_disable_table_trigger
744 -- database table.
745 IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
746 X_ROWID => L_ROWID ,
747 X_TABLE_NAME =>'ADMP_DEL_SCA_UNCONF',
748 X_SESSION_ID => userenv('SESSIONID'),
749 x_mode => 'R'
750 );
751 FOR v_susa_rec IN c_susa LOOP
752 FOR v_hes_rec IN c_hes (v_susa_rec.unit_set_cd, v_susa_rec.sequence_number )
753 LOOP
754 BEGIN
755 -- Delete unconfirmed IGS_HE_EN_SUSA records
756 FOR v_hes_del_rec in c_hes_del(v_hes_rec.hesa_en_susa_id) LOOP
757
758 IGS_HE_EN_SUSA_PKG.DELETE_ROW( X_ROWID => v_hes_del_rec.ROWID );
759
760 END LOOP;
761 EXCEPTION
762 WHEN e_resource_busy THEN
763 IF c_hes_del%ISOPEN THEN
764 CLOSE c_hes_del;
765 END IF;
766 l_entity_name := 'IGS_HE_EN_SUSA';
767 EXIT;
768 END;
769 END LOOP;
770
771 FOR v_hesc_rec IN c_hesc (v_susa_rec.unit_set_cd, v_susa_rec.sequence_number )
772 LOOP
773
774 BEGIN
775 -- Delete unconfirmed IGS_HE_EN_SUSA_CC records
776 FOR v_hesc_del_rec in c_hesc_del(v_hesc_rec.he_susa_cc_id) LOOP
777
778 IGS_HE_EN_SUSA_CC_PKG.DELETE_ROW( X_ROWID => v_hesc_del_rec.ROWID );
779
780 END LOOP;
781 EXCEPTION
782 WHEN e_resource_busy THEN
783 IF c_hesc_del%ISOPEN THEN
784 CLOSE c_hesc_del;
785 END IF;
786 l_entity_name := 'IGS_HE_EN_SUSA_CC';
787 EXIT;
788 END;
789 END LOOP;
790 IF l_entity_name IS NOT NULL THEN
791 EXIT;
792 END IF;
793 -- Delete unconfirmed IGS_AS_SU_SETATMPT
794 FOR v_susa_del_exists IN c_susa_del(
795 v_susa_rec.unit_set_cd,
796 v_susa_rec.sequence_number) LOOP
797
798 IGS_AS_SU_SETATMPT_PKG.DELETE_ROW ( X_ROWID => V_SUSA_DEL_EXISTS.ROWID );
799
800 END LOOP;
801
802 END LOOP;
803 IF v_error_flag THEN
804 -- Must reset database trigger validation if been turned off
805 IGS_GE_MNT_SDTT.genp_del_sdtt('ADMP_DEL_SCA_UNCONF');
806
807 END IF;
808 -- Must reset database trigger validation if been turned off
809 IGS_GE_MNT_SDTT.genp_del_sdtt('ADMP_DEL_SCA_UNCONF');
810
811 EXCEPTION
812 WHEN e_resource_busy THEN
813 IF c_susa%ISOPEN THEN
814 CLOSE c_susa;
815 END IF;
816 IF c_susa_del%ISOPEN THEN
817 CLOSE c_susa_del;
818 END IF;
819 IF c_hes%ISOPEN THEN
820 CLOSE c_hes;
821 END IF;
822 IF c_hes_del%ISOPEN THEN
823 CLOSE c_hes_del;
824 END IF;
825 IF c_hesc%ISOPEN THEN
826 CLOSE c_hesc;
827 END IF;
828 IF c_hesc_del%ISOPEN THEN
829 CLOSE c_hesc_del;
830 END IF;
831 -- Must reset database trigger validation if been turned off
832 IGS_GE_MNT_SDTT.genp_del_sdtt('ADMP_DEL_SCA_UNCONF');
833 WHEN OTHERS THEN
834 IF c_susa%ISOPEN THEN
835 CLOSE c_susa;
836 END IF;
837 IF c_susa_del%ISOPEN THEN
838 CLOSE c_susa_del;
839 END IF;
840 IF c_hes%ISOPEN THEN
841 CLOSE c_hes;
842 END IF;
843 IF c_hes_del%ISOPEN THEN
844 CLOSE c_hes_del;
845 END IF;
846 IF c_hesc%ISOPEN THEN
847 CLOSE c_hesc;
848 END IF;
849 IF c_hesc_del%ISOPEN THEN
850 CLOSE c_hesc_del;
851 END IF;
852 -- Must reset database trigger validation if been turned off
853 IGS_GE_MNT_SDTT.genp_del_sdtt('ADMP_DEL_SCA_UNCONF');
854
855 END;
856 END del_susa;
857
858 PROCEDURE del_scho(
859 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
860 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
861 IS
862 BEGIN -- del_scho
863 -- (3) Delete IGS_EN_STDNTPSHECSOP records
864 DECLARE
865 CURSOR c_scho IS
866 SELECT scho.start_dt
867 FROM IGS_EN_STDNTPSHECSOP scho
868 WHERE scho.person_id = p_person_id
869 AND scho.course_cd = p_course_cd;
870
871 CURSOR c_scho_del (
872 cp_start_dt IGS_EN_STDNTPSHECSOP.start_dt%TYPE) IS
873 SELECT ROWID, scho.*
874 FROM IGS_EN_STDNTPSHECSOP scho
875 WHERE scho.person_id = p_person_id
876 AND scho.course_cd = p_course_cd
877 AND scho.start_dt = cp_start_dt
878 FOR UPDATE OF
879 scho.LAST_UPDATE_DATE NOWAIT;
880
881 v_scho_del_exists c_scho_del%ROWTYPE;
882
883 BEGIN
884
885 FOR v_scho_rec IN c_scho LOOP
886 -- Delete unconfirmed IGS_EN_STDNTPSHECSOP
887 FOR v_scho_del_exists IN c_scho_del(v_scho_rec.start_dt) LOOP
888
889 IGS_EN_STDNTPSHECSOP_PKG.DELETE_ROW ( X_ROWID => V_SCHO_DEL_EXISTS.ROWID );
890
891 END LOOP;
892
893 END LOOP;
894
895 EXCEPTION
896 WHEN e_resource_busy THEN
897 IF c_scho%ISOPEN THEN
898 CLOSE c_scho;
899 END IF;
900 IF c_scho_del%ISOPEN THEN
901 CLOSE c_scho_del;
902 END IF;
903
904 WHEN OTHERS THEN
905 IF c_scho%ISOPEN THEN
906 CLOSE c_scho;
907 END IF;
908 IF c_scho_del%ISOPEN THEN
909 CLOSE c_scho_del;
910 END IF;
911
912 END;
913
914 END del_scho;
915
916 PROCEDURE del_scae(
917 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
918 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
919 IS
920
921 BEGIN -- del_scae
922 -- (4) Delete IGS_AS_SC_ATMPT_ENR scae
923 DECLARE
924 CURSOR c_scae IS
925 SELECT ROWID, scae.*
926 FROM IGS_AS_SC_ATMPT_ENR scae
927 WHERE scae.person_id = p_person_id
928 AND scae.course_cd = p_course_cd
929 FOR UPDATE OF scae.LAST_UPDATE_DATE NOWAIT;
930
931 BEGIN
932 FOR v_scae_rec IN c_scae LOOP
933
934 IGS_AS_SC_ATMPT_ENR_PKG.DELETE_ROW(v_scae_rec.rowid);
935
936 END LOOP;
937
938 EXCEPTION
939 WHEN e_resource_busy THEN
940 IF c_scae%ISOPEN THEN
941 CLOSE c_scae;
942 END IF;
943
944 WHEN OTHERS THEN
945 IF c_scae%ISOPEN THEN
946 CLOSE c_scae;
947 END IF;
948
949 END;
950
951 END del_scae;
952
953 PROCEDURE del_scan(
954 p_person_id IGS_AS_SC_ATMPT_NOTE.person_id%TYPE,
955 p_course_cd IGS_AS_SC_ATMPT_NOTE.course_cd%TYPE)
956 IS
957 BEGIN -- del_scan
958 -- Delete student IGS_PS_COURSE attempt notes (5)
959 DECLARE
960 CURSOR c_scan IS
961 SELECT ROWID, scan.*
962 FROM IGS_AS_SC_ATMPT_NOTE scan
963 WHERE scan.person_id = p_person_id
964 AND scan.course_cd = p_course_cd
965 FOR UPDATE OF scan.reference_number NOWAIT;
966
967
968 BEGIN
969 FOR v_scan_rec IN c_scan LOOP
970 -- Call RI check routine for the IGS_AS_SC_ATMPT_NOTE table
971 IGS_AS_SC_ATMPT_NOTE_PKG.DELETE_ROW(v_scan_rec.rowid);
972
973 END LOOP;
974
975 EXCEPTION
976 WHEN e_resource_busy THEN
977 IF c_scan%ISOPEN THEN
978 CLOSE c_scan;
979 END IF;
980 WHEN OTHERS THEN
981 IF c_scan%ISOPEN THEN
982 CLOSE c_scan;
983 END IF;
984
985 END;
986
987 END del_scan;
988
989 PROCEDURE upd_del_re_candidature(
990 p_person_id IGS_AV_ADV_STANDING.person_id%TYPE,
991 p_course_cd IGS_AV_ADV_STANDING.course_cd%TYPE,
992 p_adm_admission_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE,
993 p_adm_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE,
994 p_adm_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE)
995 IS
996 BEGIN -- upd_re_candidature
997 -- Process IGS_RE_CANDIDATURE
998 DECLARE
999 CURSOR c_ca IS
1000 SELECT rowid, ca.*
1001 FROM IGS_RE_CANDIDATURE ca
1002 WHERE ca.person_id = p_person_id
1003 AND ca.sca_course_cd = p_course_cd
1004 FOR UPDATE OF ca.sca_course_cd NOWAIT;
1005
1006 BEGIN
1007 FOR v_ca_rec IN c_ca LOOP
1008
1009 IF (V_CA_REC.ACAI_ADMISSION_APPL_NUMBER IS NULL AND
1010 V_CA_REC.ACAI_NOMINATED_COURSE_CD IS NULL AND
1011 V_CA_REC.ACAI_SEQUENCE_NUMBER IS NULL) THEN
1012
1013 IGS_RE_CANDIDATURE_PKG.DELETE_ROW(X_ROWID => V_CA_REC.ROWID);
1014 ELSE
1015
1016 IGS_RE_CANDIDATURE_PKG.UPDATE_ROW(
1017 X_ROWID => V_CA_REC.ROWID,
1018 X_PERSON_ID => V_CA_REC.PERSON_ID,
1019 X_SEQUENCE_NUMBER => V_CA_REC.SEQUENCE_NUMBER,
1020 X_SCA_COURSE_CD => NULL,
1021 X_ACAI_ADMISSION_APPL_NUMBER => V_CA_REC.ACAI_ADMISSION_APPL_NUMBER,
1022 X_ACAI_NOMINATED_COURSE_CD => V_CA_REC.ACAI_NOMINATED_COURSE_CD,
1023 X_ACAI_SEQUENCE_NUMBER => V_CA_REC.ACAI_SEQUENCE_NUMBER,
1024 X_ATTENDANCE_PERCENTAGE => V_CA_REC.ATTENDANCE_PERCENTAGE,
1025 X_GOVT_TYPE_OF_ACTIVITY_CD => V_CA_REC.GOVT_TYPE_OF_ACTIVITY_CD,
1026 X_MAX_SUBMISSION_DT => V_CA_REC.MAX_SUBMISSION_DT,
1027 X_MIN_SUBMISSION_DT => V_CA_REC.MIN_SUBMISSION_DT,
1028 X_RESEARCH_TOPIC => V_CA_REC.RESEARCH_TOPIC,
1029 X_INDUSTRY_LINKS => V_CA_REC.INDUSTRY_LINKS
1030 );
1031 END IF;
1032 END LOOP;
1033
1034 EXCEPTION
1035 WHEN e_resource_busy THEN
1036 IF c_ca%ISOPEN THEN
1037 CLOSE c_ca;
1038 END IF;
1039 WHEN OTHERS THEN
1040 IF c_ca%ISOPEN THEN
1041 CLOSE c_ca;
1042 END IF;
1043
1044 END;
1045
1046 END upd_del_re_candidature;
1047
1048 PROCEDURE del_av(
1049 p_person_id IGS_AV_ADV_STANDING.person_id%TYPE,
1050 p_course_cd IGS_AV_ADV_STANDING.course_cd%TYPE,
1051 p_version_num IGS_AV_ADV_STANDING.version_number%TYPE)
1052 IS
1053 BEGIN -- del_av
1054 -- Delete IGS_AV_ADV_STANDING records
1055 DECLARE
1056 CURSOR c_av IS
1057 SELECT ROWID
1058 FROM IGS_AV_ADV_STANDING av
1059 WHERE av.person_id = p_person_id
1060 AND av.course_cd = p_course_cd
1061 AND av.version_number = p_version_num;
1062
1063 c_av_rec c_av%ROWTYPE;
1064
1065 BEGIN
1066
1067 FOR c_av_rec IN c_av LOOP
1068 -- Delete IGS_AV_ADV_STANDING records
1069 IGS_AV_ADV_STANDING_PKG.DELETE_ROW( c_av_rec.ROWID);
1070
1071 END LOOP;
1072 -- Return the default value
1073
1074 EXCEPTION
1075 WHEN e_resource_busy THEN
1076 IF c_av%ISOPEN THEN
1077 CLOSE c_av;
1078 END IF;
1079 WHEN OTHERS THEN
1080 IF c_av%ISOPEN THEN
1081 CLOSE c_av;
1082 END IF;
1083
1084 END;
1085
1086 END del_av;
1087
1088 -- To delete IGS_FI_FEE_AS_RT table records
1089 PROCEDURE del_fi_fee(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1090 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1091 IS
1092
1093 CURSOR c_del_fi_fee IS
1094 SELECT rowid
1095 FROM IGS_FI_FEE_AS_RT fi_fee
1096 WHERE fi_fee.person_id = p_person_id
1097 AND fi_fee.course_cd = p_course_cd;
1098
1099 v_del_fi_fee c_del_fi_fee%ROWTYPE;
1100
1101 BEGIN
1102 FOR v_del_fi_fee IN c_del_fi_fee LOOP
1103
1104 IGS_FI_FEE_AS_RT_PKG.DELETE_ROW(v_del_fi_fee.rowid);
1105
1106 END LOOP;
1107
1108 EXCEPTION
1109 WHEN e_resource_busy THEN
1110 IF c_del_fi_fee%ISOPEN THEN
1111 CLOSE c_del_fi_fee;
1112 END IF;
1113
1114 WHEN OTHERS THEN
1115 IF c_del_fi_fee%ISOPEN THEN
1116 CLOSE c_del_fi_fee;
1117 END IF;
1118
1119 END del_fi_fee;
1120
1121
1122 -- To delete intermission records
1123 PROCEDURE del_ps_intm(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1124 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1125 IS
1126
1127 CURSOR c_del_ps_intm IS
1128 SELECT rowid
1129 FROM IGS_EN_STDNT_PS_INTM intm
1130 WHERE intm.person_id = p_person_id
1131 AND intm.course_cd = p_course_cd;
1132
1133 v_del_ps_intm c_del_ps_intm%ROWTYPE;
1134
1135 BEGIN
1136
1137 FOR v_del_ps_intm IN c_del_ps_intm LOOP
1138 IGS_EN_STDNT_PS_INTM_PKG.DELETE_ROW(v_del_ps_intm.rowid);
1139 END LOOP;
1140
1141 EXCEPTION
1142 WHEN e_resource_busy THEN
1143 IF c_del_ps_intm%ISOPEN THEN
1144 CLOSE c_del_ps_intm;
1145 END IF;
1146
1147 WHEN OTHERS THEN
1148 IF c_del_ps_intm%ISOPEN THEN
1149 CLOSE c_del_ps_intm;
1150 END IF;
1151
1152 END del_ps_intm;
1153
1154 PROCEDURE del_ps_trnsf(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1155 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1156 p_term_cal_type IGS_CA_INST.cal_type%TYPE,
1157 p_term_sequence_number IGS_CA_INST.sequence_number%TYPE)
1158 IS
1159
1160 CURSOR c_del_ps_trnsf IS
1161 SELECT rowid
1162 FROM IGS_PS_STDNT_TRN trnsf
1163 WHERE trnsf.person_id = p_person_id
1164 AND trnsf.course_cd = p_course_cd
1165 AND trnsf.effective_term_cal_type = p_term_cal_type
1166 AND trnsf.effective_term_sequence_num = p_term_sequence_number
1167 AND trnsf.status_flag = 'C';
1168
1169 v_del_ps_trnsf c_del_ps_trnsf%ROWTYPE;
1170
1171 BEGIN
1172
1173 FOR v_del_ps_trnsf IN c_del_ps_trnsf LOOP
1174
1175 IGS_PS_STDNT_TRN_PKG.DELETE_ROW(v_del_ps_trnsf.rowid);
1176
1177 END LOOP;
1178
1179 EXCEPTION
1180 WHEN e_resource_busy THEN
1181 IF c_del_ps_trnsf%ISOPEN THEN
1182 CLOSE c_del_ps_trnsf;
1183 END IF;
1184
1185 WHEN OTHERS THEN
1186
1187 IF c_del_ps_trnsf%ISOPEN THEN
1188 CLOSE c_del_ps_trnsf;
1189 END IF;
1190
1191 END del_ps_trnsf;
1192
1193
1194
1195 PROCEDURE del_pr_cohinst_rank(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1196 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1197 IS
1198
1199 CURSOR c_del_pr_cohinst_rank IS
1200 SELECT rowid
1201 FROM igs_pr_cohinst_rank pr_cohinst
1202 WHERE pr_cohinst.person_id = p_person_id
1203 AND pr_cohinst.course_cd = p_course_cd;
1204
1205 v_del_pr_cohinst c_del_pr_cohinst_rank%ROWTYPE;
1206
1207 BEGIN
1208 FOR v_del_pr_cohinst IN c_del_pr_cohinst_rank LOOP
1209
1210 igs_pr_cohinst_rank_pkg.DELETE_ROW(v_del_pr_cohinst.rowid);
1211
1212 END LOOP;
1213 EXCEPTION
1214 WHEN e_resource_busy THEN
1215 IF c_del_pr_cohinst_rank%ISOPEN THEN
1216 CLOSE c_del_pr_cohinst_rank;
1217 END IF;
1218 WHEN OTHERS THEN
1219 IF c_del_pr_cohinst_rank%ISOPEN THEN
1220 CLOSE c_del_pr_cohinst_rank;
1221 END IF;
1222
1223 END del_pr_cohinst_rank;
1224
1225
1226 PROCEDURE del_as_anon_id_ps(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1227 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
1228 IS
1229
1230 CURSOR c_del_as_anon_id_ps IS
1231 SELECT rowid
1232 FROM igs_as_anon_id_ps as_anon_id_ps
1233 WHERE as_anon_id_ps.person_id = p_person_id
1234 AND as_anon_id_ps.course_cd = p_course_cd;
1235
1236 v_del_as_anon_id_ps c_del_as_anon_id_ps%ROWTYPE;
1237
1238 BEGIN
1239
1240 FOR v_del_as_anon_id_ps IN c_del_as_anon_id_ps LOOP
1241 igs_as_anon_id_ps_pkg.DELETE_ROW(v_del_as_anon_id_ps.rowid);
1242 END LOOP;
1243
1244 EXCEPTION
1245 WHEN e_resource_busy THEN
1246 IF c_del_as_anon_id_ps%ISOPEN THEN
1247 CLOSE c_del_as_anon_id_ps;
1248 END IF;
1249
1250 WHEN OTHERS THEN
1251 IF c_del_as_anon_id_ps%ISOPEN THEN
1252 CLOSE c_del_as_anon_id_ps;
1253 END IF;
1254
1255 END del_as_anon_id_ps;
1256
1257 -- procedure to delete all the unit attempt reference codes
1258 PROCEDURE del_suar(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1259 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1260 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1261 IS
1262
1263 CURSOR cur_sua_ref_cds(cp_person_id NUMBER,
1264 cp_course_cd VARCHAR2,
1265 cp_uoo_id NUMBER) IS
1266 Select suar.rowid
1267 From IGS_AS_SUA_REF_CDS suar
1268 Where suar.person_id = cp_person_id
1269 And suar.course_cd = cp_course_cd
1270 And suar.uoo_id = cp_uoo_id;
1271
1272 BEGIN
1273 FOR v_cur_sua_ref_cds IN cur_sua_ref_cds(p_person_id,p_course_cd,p_uoo_id) LOOP
1274 igs_as_sua_ref_cds_pkg.delete_row(v_cur_sua_ref_cds.rowid);
1275 END LOOP;
1276
1277 EXCEPTION
1278 WHEN e_resource_busy THEN
1279 IF cur_sua_ref_cds%ISOPEN THEN
1280 CLOSE cur_sua_ref_cds;
1281 END IF;
1282 WHEN OTHERS THEN
1283 IF cur_sua_ref_cds%ISOPEN THEN
1284 CLOSE cur_sua_ref_cds;
1285 END IF;
1286
1287 END del_suar;
1288
1289
1290 PROCEDURE del_as_anon_id_us(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1291 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1292 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1293 IS
1294
1295 CURSOR c_del_as_anon_id_us IS
1296 SELECT rowid
1297 FROM igs_as_anon_id_us as_anon_id_us
1298 WHERE as_anon_id_us.person_id = p_person_id
1299 AND as_anon_id_us.course_cd = p_course_cd
1300 AND as_anon_id_us.uoo_id = p_uoo_id;
1301 v_del_as_anon_id_us c_del_as_anon_id_us%ROWTYPE;
1302
1303 BEGIN
1304
1305 FOR v_del_as_anon_id_us IN c_del_as_anon_id_us LOOP
1306 igs_as_anon_id_us_pkg.DELETE_ROW(v_del_as_anon_id_us.rowid);
1307 END LOOP;
1308
1309 EXCEPTION
1310 WHEN e_resource_busy THEN
1311 IF c_del_as_anon_id_us%ISOPEN THEN
1312 CLOSE c_del_as_anon_id_us;
1313 END IF;
1314 WHEN OTHERS THEN
1315 IF c_del_as_anon_id_us%ISOPEN THEN
1316 CLOSE c_del_as_anon_id_us;
1317 END IF;
1318
1319 END del_as_anon_id_us;
1320
1321
1322 FUNCTION del_sua(
1323 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1324 p_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1325 p_term_cal_type IGS_CA_INST.cal_type%TYPE,
1326 p_term_sequence_number IGS_CA_INST.sequence_number%TYPE
1327 )
1328 RETURN BOOLEAN
1329 IS
1330 BEGIN -- del_sua
1331 -- (1) Delete IGS_EN_SU_ATTEMPT records
1332 DECLARE
1333 CURSOR c_sua IS
1334 SELECT uoo_id
1335 FROM IGS_EN_SU_ATTEMPT sua
1336 WHERE sua.person_id = p_person_id
1337 AND sua.course_cd = p_dest_course_cd;
1338
1339 CURSOR c_sua_del (
1340 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
1341 SELECT rowid
1342 FROM IGS_EN_SU_ATTEMPT sua
1343 WHERE sua.person_id = p_person_id
1344 AND sua.course_cd = p_dest_course_cd
1345 AND sua.uoo_id = cp_uoo_id
1346 FOR UPDATE OF sua.LAST_UPDATE_DATE NOWAIT;
1347
1348 v_sua_del_exists c_sua_del%ROWTYPE;
1349
1350 BEGIN
1351
1352 FOR v_sua_rec IN c_sua LOOP
1353
1354 FOR v_sua_del_exists IN c_sua_del(v_sua_rec.uoo_id) LOOP
1355
1356 -- Delete the unit attempt only if it is in the effective and future terms
1357 IF IGS_EN_GEN_010.unit_effect_or_future_term(
1358 p_person_id => p_person_id,
1359 p_dest_course_cd => p_dest_course_cd,
1360 p_uoo_id => v_sua_rec.uoo_id,
1361 p_term_cal_type => p_term_cal_type ,
1362 p_term_seq_num => p_term_sequence_number) THEN
1363
1364 del_suar(p_person_id, p_dest_course_Cd, v_sua_rec.uoo_id);
1365
1366 del_as_anon_id_us( p_person_id,p_dest_course_cd,v_sua_rec.uoo_id);
1367
1368 IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW( X_ROWID => v_sua_del_exists.rowid );
1369
1370 END IF;
1371
1372 END LOOP;
1373
1374 END LOOP;
1375
1376 RETURN TRUE;
1377 EXCEPTION
1378 WHEN e_resource_busy THEN
1379 IF c_sua%ISOPEN THEN
1380 CLOSE c_sua;
1381 END IF;
1382 IF c_sua_del%ISOPEN THEN
1383 CLOSE c_sua_del;
1384 END IF;
1385
1386 RETURN FALSE;
1387
1388 WHEN OTHERS THEN
1389 IF c_sua%ISOPEN THEN
1390 CLOSE c_sua;
1391 END IF;
1392 IF c_sua_del%ISOPEN THEN
1393 CLOSE c_sua_del;
1394 END IF;
1395 RETURN FALSE;
1396 END;
1397 END del_sua;
1398
1399
1400 PROCEDURE upd_sua(
1401 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1402 p_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1403 p_term_cal_type IGS_CA_INST.cal_type%TYPE,
1404 p_term_sequence_number IGS_CA_INST.sequence_number%TYPE)
1405 IS
1406 BEGIN -- upd_sua
1407 -- (1) Update IGS_EN_SU_ATTEMPT records
1408 DECLARE
1409
1410 cst_dropped CONSTANT VARCHAR2(10) := 'DROPPED';
1411
1412 CURSOR c_sua IS
1413 SELECT uoo_id
1414 FROM IGS_EN_SU_ATTEMPT sua
1415 WHERE sua.person_id = p_person_id
1416 AND sua.course_cd = p_dest_course_cd;
1417
1418 CURSOR c_sua_upd (
1419 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
1420 SELECT rowid,sua.*
1421 FROM IGS_EN_SU_ATTEMPT sua
1422 WHERE sua.person_id = p_person_id
1423 AND sua.course_cd = p_dest_course_cd
1424 AND sua.uoo_id = cp_uoo_id
1425 AND sua.unit_attempt_status <> cst_dropped
1426 FOR UPDATE OF sua.LAST_UPDATE_DATE NOWAIT;
1427
1428 v_sua_del_exists c_sua_upd%ROWTYPE;
1429
1430 BEGIN
1431
1432 FOR v_sua_rec IN c_sua LOOP
1433
1434 FOR v_sua_upd_exists IN c_sua_upd(v_sua_rec.uoo_id) LOOP
1435
1436 -- Update the unit attempt only if it is in the effective and future terms
1437 IF IGS_EN_GEN_010.unit_effect_or_future_term(
1438 p_person_id => p_person_id,
1439 p_dest_course_cd => p_dest_course_cd,
1440 p_uoo_id => v_sua_rec.uoo_id,
1441 p_term_cal_type => p_term_cal_type ,
1442 p_term_seq_num => p_term_sequence_number) THEN
1443
1444 IGS_EN_SUA_API.update_unit_attempt(
1445 X_ROWID => v_sua_upd_exists.ROWID,
1446 X_PERSON_ID => v_sua_upd_exists.PERSON_ID,
1447 X_COURSE_CD => v_sua_upd_exists.COURSE_CD ,
1448 X_UNIT_CD => v_sua_upd_exists.UNIT_CD,
1449 X_CAL_TYPE => v_sua_upd_exists.CAL_TYPE,
1450 X_CI_SEQUENCE_NUMBER => v_sua_upd_exists.CI_SEQUENCE_NUMBER ,
1451 X_VERSION_NUMBER => v_sua_upd_exists.VERSION_NUMBER ,
1452 X_LOCATION_CD => v_sua_upd_exists.LOCATION_CD,
1453 X_UNIT_CLASS => v_sua_upd_exists.UNIT_CLASS ,
1454 X_CI_START_DT => v_sua_upd_exists.CI_START_DT,
1455 X_CI_END_DT => v_sua_upd_exists.CI_END_DT,
1456 X_UOO_ID => v_sua_upd_exists.UOO_ID ,
1457 X_ENROLLED_DT => v_sua_upd_exists.ENROLLED_DT,
1458 X_UNIT_ATTEMPT_STATUS => cst_dropped, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
1459 X_ADMINISTRATIVE_UNIT_STATUS => v_sua_upd_exists.administrative_unit_status,
1460 X_ADMINISTRATIVE_PRIORITY => v_sua_upd_exists.administrative_PRIORITY,
1461 X_DISCONTINUED_DT => nvl(v_sua_upd_exists.discontinued_dt,trunc(SYSDATE)),
1462 X_DCNT_REASON_CD => v_sua_upd_exists.DCNT_REASON_CD,
1463 X_RULE_WAIVED_DT => v_sua_upd_exists.RULE_WAIVED_DT ,
1464 X_RULE_WAIVED_PERSON_ID => v_sua_upd_exists.RULE_WAIVED_PERSON_ID ,
1465 X_NO_ASSESSMENT_IND => v_sua_upd_exists.NO_ASSESSMENT_IND,
1466 X_SUP_UNIT_CD => v_sua_upd_exists.SUP_UNIT_CD ,
1467 X_SUP_VERSION_NUMBER => v_sua_upd_exists.SUP_VERSION_NUMBER,
1468 X_EXAM_LOCATION_CD => v_sua_upd_exists.EXAM_LOCATION_CD,
1469 X_ALTERNATIVE_TITLE => v_sua_upd_exists.ALTERNATIVE_TITLE,
1470 X_OVERRIDE_ENROLLED_CP => v_sua_upd_exists.OVERRIDE_ENROLLED_CP,
1471 X_OVERRIDE_EFTSU => v_sua_upd_exists.OVERRIDE_EFTSU ,
1472 X_OVERRIDE_ACHIEVABLE_CP => v_sua_upd_exists.OVERRIDE_ACHIEVABLE_CP,
1473 X_OVERRIDE_OUTCOME_DUE_DT => v_sua_upd_exists.OVERRIDE_OUTCOME_DUE_DT,
1474 X_OVERRIDE_CREDIT_REASON => v_sua_upd_exists.OVERRIDE_CREDIT_REASON,
1475 X_WAITLIST_DT => v_sua_upd_exists.waitlist_dt,
1476 X_MODE => 'R',
1477 X_GS_VERSION_NUMBER => v_sua_upd_exists.gs_version_number,
1478 X_ENR_METHOD_TYPE => v_sua_upd_exists.enr_method_type,
1479 X_FAILED_UNIT_RULE => v_sua_upd_exists.FAILED_UNIT_RULE,
1480 X_CART => v_sua_upd_exists.CART,
1481 X_RSV_SEAT_EXT_ID => v_sua_upd_exists.RSV_SEAT_EXT_ID ,
1482 X_ORG_UNIT_CD => v_sua_upd_exists.org_unit_cd ,
1483 X_SESSION_ID => v_sua_upd_exists.session_id,
1484 X_GRADING_SCHEMA_CODE => v_sua_upd_exists.grading_schema_code,
1485 X_DEG_AUD_DETAIL_ID => v_sua_upd_exists.deg_aud_detail_id,
1486 X_SUBTITLE => v_sua_upd_exists.subtitle,
1487 X_STUDENT_CAREER_TRANSCRIPT => v_sua_upd_exists.student_career_transcript,
1488 X_STUDENT_CAREER_STATISTICS => v_sua_upd_exists.student_career_statistics,
1489 X_ATTRIBUTE_CATEGORY => v_sua_upd_exists.attribute_category,
1490 X_ATTRIBUTE1 => v_sua_upd_exists.attribute1,
1491 X_ATTRIBUTE2 => v_sua_upd_exists.attribute2,
1492 X_ATTRIBUTE3 => v_sua_upd_exists.attribute3,
1493 X_ATTRIBUTE4 => v_sua_upd_exists.attribute4,
1494 X_ATTRIBUTE5 => v_sua_upd_exists.attribute5,
1495 X_ATTRIBUTE6 => v_sua_upd_exists.attribute6,
1496 X_ATTRIBUTE7 => v_sua_upd_exists.attribute7,
1497 X_ATTRIBUTE8 => v_sua_upd_exists.attribute8,
1498 X_ATTRIBUTE9 => v_sua_upd_exists.attribute9,
1499 X_ATTRIBUTE10 => v_sua_upd_exists.attribute10,
1500 X_ATTRIBUTE11 => v_sua_upd_exists.attribute11,
1501 X_ATTRIBUTE12 => v_sua_upd_exists.attribute12,
1502 X_ATTRIBUTE13 => v_sua_upd_exists.attribute13,
1503 X_ATTRIBUTE14 => v_sua_upd_exists.attribute14,
1504 X_ATTRIBUTE15 => v_sua_upd_exists.attribute15,
1505 X_ATTRIBUTE16 => v_sua_upd_exists.attribute16,
1506 X_ATTRIBUTE17 => v_sua_upd_exists.attribute17,
1507 X_ATTRIBUTE18 => v_sua_upd_exists.attribute18,
1508 X_ATTRIBUTE19 => v_sua_upd_exists.attribute19,
1509 X_ATTRIBUTE20 => v_sua_upd_exists.attribute20,
1510 X_WAITLIST_MANUAL_IND => v_sua_upd_exists.waitlist_manual_ind,
1511 X_WLST_PRIORITY_WEIGHT_NUM => v_sua_upd_exists.wlst_priority_weight_num,
1512 X_WLST_PREFERENCE_WEIGHT_NUM => v_sua_upd_exists.wlst_preference_weight_num,
1513 X_CORE_INDICATOR_CODE => v_sua_upd_exists.core_indicator_code
1514 );
1515
1516 END IF;
1517
1518 END LOOP;
1519 END LOOP;
1520
1521 EXCEPTION
1522 WHEN e_resource_busy THEN
1523 IF c_sua%ISOPEN THEN
1524 CLOSE c_sua;
1525 END IF;
1526 IF c_sua_upd%ISOPEN THEN
1527 CLOSE c_sua_upd;
1528 END IF;
1529
1530 WHEN OTHERS THEN
1531 IF c_sua%ISOPEN THEN
1532 CLOSE c_sua;
1533 END IF;
1534 IF c_sua_upd%ISOPEN THEN
1535 CLOSE c_sua_upd;
1536 END IF;
1537
1538 END;
1539 END upd_sua;
1540
1541 PROCEDURE del_as_stmptout(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1542 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1543 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1544 IS
1545
1546 CURSOR c_del_as_stmptout(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1547 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1548 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1549 IS
1550 SELECT rowid
1551 FROM igs_as_su_stmptout_all stmptout
1552 WHERE stmptout.person_id = cp_person_id
1553 AND stmptout.course_cd = cp_course_cd
1554 AND stmptout.uoo_id = cp_uoo_id;
1555 v_del_as_stmptout c_del_as_stmptout%ROWTYPE;
1556
1557 BEGIN
1558
1559 FOR v_del_as_stmptout IN c_del_as_stmptout(p_person_id,p_course_cd,p_uoo_id) LOOP
1560 IGS_AS_SU_STMPTOUT_PKG.DELETE_ROW(v_del_as_stmptout.rowid);
1561 END LOOP;
1562
1563 EXCEPTION
1564 WHEN e_resource_busy THEN
1565 IF c_del_as_stmptout%ISOPEN THEN
1566 CLOSE c_del_as_stmptout;
1567 END IF;
1568 WHEN OTHERS THEN
1569 IF c_del_as_stmptout%ISOPEN THEN
1570 CLOSE c_del_as_stmptout;
1571 END IF;
1572
1573 END del_as_stmptout;
1574
1575 PROCEDURE del_ps_stdnt_unt_trn(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1576 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1577 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1578 IS
1579
1580 CURSOR c_del_ps_stdnt_unt_trn(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1581 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1582 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1583 IS
1584 SELECT rowid
1585 FROM IGS_PS_STDNT_UNT_TRN unttrn
1586 WHERE unttrn.person_id = cp_person_id
1587 AND unttrn.course_cd = cp_course_cd
1588 AND unttrn.uoo_id = cp_uoo_id;
1589 v_del_ps_stdnt_unt_trn c_del_ps_stdnt_unt_trn%ROWTYPE;
1590
1591 BEGIN
1592
1593 FOR v_del_ps_stdnt_unt_trn IN c_del_ps_stdnt_unt_trn(p_person_id,p_course_cd,p_uoo_id) LOOP
1594 IGS_PS_STDNT_UNT_TRN_PKG.DELETE_ROW(v_del_ps_stdnt_unt_trn.rowid);
1595 END LOOP;
1596
1597 EXCEPTION
1598 WHEN e_resource_busy THEN
1599 IF c_del_ps_stdnt_unt_trn%ISOPEN THEN
1600 CLOSE c_del_ps_stdnt_unt_trn;
1601 END IF;
1602 WHEN OTHERS THEN
1603 IF c_del_ps_stdnt_unt_trn%ISOPEN THEN
1604 CLOSE c_del_ps_stdnt_unt_trn;
1605 END IF;
1606
1607 END del_ps_stdnt_unt_trn;
1608
1609 PROCEDURE del_as_sua_ses_atts(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1610 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1611 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1612 IS
1613
1614 CURSOR c_del_as_sua_ses_atts(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1615 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1616 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1617 IS
1618 SELECT rowid
1619 FROM igs_as_sua_ses_atts sua_ses_atts
1620 WHERE sua_ses_atts.person_id = cp_person_id
1621 AND sua_ses_atts.course_cd = cp_course_cd
1622 AND sua_ses_atts.uoo_id = cp_uoo_id;
1623 v_del_as_sua_ses_atts c_del_as_sua_ses_atts%ROWTYPE;
1624 v_RETURN_STATUS VARCHAR2(255) ;
1625 v_MSG_DATA VARCHAR2(4000);
1626 v_MSG_COUNT NUMBER ;
1627 BEGIN
1628
1629 FOR v_del_as_sua_ses_atts IN c_del_as_sua_ses_atts(p_person_id,p_course_cd,p_uoo_id) LOOP
1630 IGS_AS_SUA_SES_ATTS_pkg.DELETE_ROW(v_del_as_sua_ses_atts.rowid,
1631 'R' ,
1632 v_RETURN_STATUS,
1633 v_MSG_DATA,
1634 v_MSG_COUNT
1635 );
1636
1637 END LOOP;
1638
1639
1640
1641 EXCEPTION
1642 WHEN e_resource_busy THEN
1643 IF c_del_as_sua_ses_atts%ISOPEN THEN
1644 CLOSE c_del_as_sua_ses_atts;
1645 END IF;
1646 WHEN OTHERS THEN
1647 IF c_del_as_sua_ses_atts%ISOPEN THEN
1648 CLOSE c_del_as_sua_ses_atts;
1649 END IF;
1650
1651 END del_as_sua_ses_atts;
1652
1653 PROCEDURE del_as_msht_su_atmpt(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1654 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1655 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1656 IS
1657
1658 CURSOR c_del_as_msht_su_atmpt(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1659 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1660 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1661 IS
1662 SELECT rowid
1663 FROM IGS_AS_MSHT_SU_ATMPT as_msht_su_atmpt
1664 WHERE as_msht_su_atmpt.person_id = cp_person_id
1665 AND as_msht_su_atmpt.course_cd = cp_course_cd
1666 AND as_msht_su_atmpt.uoo_id = cp_uoo_id;
1667 v_del_as_msht_su_atmpt c_del_as_msht_su_atmpt%ROWTYPE;
1668
1669 BEGIN
1670
1671 FOR v_del_as_msht_su_atmpt IN c_del_as_msht_su_atmpt(p_person_id,p_course_cd,p_uoo_id) LOOP
1672 IGS_AS_MSHT_SU_ATMPT_PKG.DELETE_ROW(v_del_as_msht_su_atmpt.rowid);
1673
1674 END LOOP;
1675
1676 EXCEPTION
1677 WHEN e_resource_busy THEN
1678 IF c_del_as_msht_su_atmpt%ISOPEN THEN
1679 CLOSE c_del_as_msht_su_atmpt;
1680 END IF;
1681 WHEN OTHERS THEN
1682 IF c_del_as_msht_su_atmpt%ISOPEN THEN
1683 CLOSE c_del_as_msht_su_atmpt;
1684 END IF;
1685
1686 END del_as_msht_su_atmpt;
1687
1688
1689
1690
1691 ---- END OF PRIVATE FUNCTIONS
1692
1693 /*----------------------------------------------------------------------------
1694 || Created By : bdeviset
1695 || Created On : 18-NOV-2004
1696 || Purpose : Processing future dated transfer records in cleanup/delete mode.
1697 || In cleanup mode deletes most of the child records for destination program
1698 || and unconfirms it.It also updates the term records against destination
1699 || program with source program details.
1700 ||
1701 || Known limitations, enhancements or remarks :
1702 || Change History :
1703 || Who When What
1704 || stutta 30-Dec-2004 Creating/deleting/updating term records while
1705 || cleanup and resetting globals.
1706 || somasekar 13-apr-2005 bug# 4179106 modified to set the future date
1707 || transfer Cancelles status to 'C'
1708 ------------------------------------------------------------------------------*/
1709
1710 PROCEDURE cleanup_dest_program(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1711 p_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1712 p_term_cal_type IGS_CA_INST.cal_type%TYPE,
1713 p_term_sequence_number IGS_CA_INST.sequence_number%TYPE,
1714 p_mode VARCHAR2)
1715
1716 IS
1717 Type term_rec IS RECORD
1718 (
1719 term_cal_type igs_en_spa_terms.term_cal_type%TYPE,
1720 term_seq_num igs_en_spa_terms.term_sequence_number%TYPE,
1721 key_program_flag igs_en_spa_terms.key_program_flag%TYPE
1722
1723 );
1724 CURSOR c_spa_clnup(cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
1725 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE,
1726 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1727 cp_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1728 SELECT rowid,sct.*
1729 FROM IGS_PS_STDNT_TRN sct
1730 WHERE sct.effective_term_cal_type = cp_term_cal_type
1731 AND sct.effective_term_sequence_num = cp_term_sequence_number
1732 AND sct.person_id = cp_person_id
1733 AND sct.course_cd = cp_dest_course_cd
1734 AND ( sct.status_flag = 'U' and p_mode IN ('CLEANUP')
1735 OR sct.status_flag = 'C' and p_mode IN ('DELETE'));
1736
1737 -- cursor for fetching the source program details
1738 CURSOR c_sca (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1739 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1740 SELECT sca.rowid,sca.*
1741 FROM IGS_EN_STDNT_PS_ATT sca
1742 WHERE sca.person_id = cp_person_id
1743 AND sca.course_cd = cp_course_cd;
1744
1745 -- cursor for fetching the student unit attempt details having status other than dropped or uncofirmed
1746 CURSOR c_sua (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1747 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1748 SELECT 'X'
1749 FROM IGS_EN_SU_ATTEMPT sua
1750 WHERE sua.person_id = cp_person_id
1751 AND sua.course_cd = cp_course_cd
1752 AND sua.unit_attempt_status NOT IN ('DROPPED','UNCONFIRM');
1753
1754 -- cursor for fetching the student unit attempt details having status as dropped
1755 CURSOR c_sua_drop (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1756 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1757 SELECT 'X'
1758 FROM IGS_EN_SU_ATTEMPT sua
1759 WHERE sua.person_id = cp_person_id
1760 AND sua.course_cd = cp_course_cd
1761 AND sua.unit_attempt_status = 'DROPPED';
1762
1763 -- cursor to get the future term records of the destination program
1764 CURSOR c_spat (cp_person_id IGS_EN_SPA_TERMS.PERSON_ID%TYPE,
1765 cp_program_cd IGS_EN_SPA_TERMS.PROGRAM_CD%TYPE,
1766 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1767 cp_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
1768 SELECT spat.rowid, spat.key_program_flag, spat.term_cal_type,
1769 spat.term_sequence_number
1770 FROM IGS_EN_SPA_TERMS spat, IGS_CA_INST ci1, IGS_CA_INST ci2
1771 WHERE spat.person_id = cp_person_id
1772 AND spat.program_cd = cp_program_cd
1773 AND spat.term_cal_type = ci2.cal_type
1774 AND spat.term_sequence_number = ci2.sequence_number
1775 AND ci1.cal_type = cp_cal_type
1776 AND ci1.sequence_number = cp_sequence_number
1777 AND ci1.start_dt <= ci2.start_dt
1778 ORDER BY ci2.start_dt;
1779
1780 -- cursor to get the any past term records of the source program
1781 CURSOR c_spat_src (cp_person_id IGS_EN_SPA_TERMS.PERSON_ID%TYPE,
1782 cp_program_cd IGS_EN_SPA_TERMS.PROGRAM_CD%TYPE,
1783 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1784 cp_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
1785 SELECT spat.key_program_flag
1786 FROM IGS_EN_SPA_TERMS spat, IGS_CA_INST ci1, IGS_CA_INST ci2
1787 WHERE spat.person_id = cp_person_id
1788 AND spat.program_cd = cp_program_cd
1789 AND spat.term_cal_type = ci2.cal_type
1790 AND spat.term_sequence_number = ci2.sequence_number
1791 AND ci1.cal_type = cp_cal_type
1792 AND ci1.sequence_number = cp_sequence_number
1793 AND ci1.start_dt > ci2.start_dt
1794 ORDER BY ci2.start_dt DESC;
1795
1796 CURSOR c_term_acad_rel(cp_acad IGS_CA_INST.CAL_TYPE%TYPE, cp_term IGS_CA_INST.CAL_TYPE%TYPE,
1797 cp_term_seq IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
1798 SELECT 'x'
1799 FROM igs_ca_inst_rel
1800 WHERE sup_cal_type = cp_acad
1801 AND sub_cal_type = cp_term
1802 AND sub_ci_sequence_number = cp_term_seq;
1803
1804
1805 -- Get the details of
1806 CURSOR c_src_eff_term(cp_src_course_cd IGS_PS_VER.COURSE_CD%TYPE) IS
1807 SELECT term_cal_type, term_sequence_number
1808 FROM igs_en_spa_terms spat, igs_ca_inst ca1, igs_ca_inst ca2
1809 WHERE ca1.cal_type = p_term_cal_type
1810 AND ca1.sequence_number = p_term_sequence_number
1811 AND spat.person_id = p_person_id
1812 AND spat.program_cd = cp_src_course_cd
1813 AND ca2.cal_type = spat.term_cal_type
1814 AND ca2.sequence_number = spat.term_sequence_number
1815 AND ca2.start_dt >= ca1.start_dt
1816 ORDER BY ca2.start_dt ASC;
1817
1818
1819 l_acad_cal IGS_EN_SPA_TERMS.ACAD_CAL_TYPE%TYPE;
1820 l_dummy VARCHAR2(1);
1821 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1822 v_del BOOLEAN;
1823 v_sca_src_rec c_sca%ROWTYPE;
1824 v_sca_dest_rec c_sca%ROWTYPE;
1825 v_spa_clnup_rec c_spa_clnup%ROWTYPE;
1826 v_spa_del_rec c_spa_clnup%ROWTYPE;
1827 v_rec_exists VARCHAR2(1);
1828 v_message_name VARCHAR2(30);
1829 v_src_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
1830 l_src_career_type IGS_PS_VER.COURSE_TYPE%TYPE;
1831 l_status_date IGS_PS_STDNT_TRN.status_date%TYPE;
1832 l_trans_status IGS_PS_STDNT_TRN.status_flag%TYPE;
1833 l_tran_across_careers BOOLEAN;
1834 l_trans_within_careers BOOLEAN;
1835 l_stdnt_conf_ind IGS_EN_STDNT_PS_ATT.STUDENT_CONFIRMED_IND%TYPE;
1836 l_comm_date IGS_EN_STDNT_PS_ATT.COMMENCEMENT_DT%TYPE;
1837 l_course_att_status IGS_EN_STDNT_PS_ATT.COURSE_ATTEMPT_STATUS%TYPE;
1838 v_src_career_type igs_ps_ver.course_type%TYPE;
1839 v_spat_rec c_spat_src%ROWTYPE;
1840 TYPE terms_tab IS TABLE OF term_rec INDEX BY BINARY_INTEGER;
1841 terms terms_tab;
1842 terms_ind NUMBER;
1843 v_ripple BOOLEAN;
1844 l_key_program_flag IGS_EN_SPA_TERMS.key_program_flag%TYPE;
1845 l_term_cal_type IGS_EN_SPA_TERMS.TERM_CAL_TYPE%TYPE;
1846 l_term_seq_num IGS_EN_SPA_TERMS.TERM_SEQUENCE_NUMBER%TYPE;
1847 BEGIN -----------------Begining of cleanup/delete
1848
1849 SAVEPOINT sp_unable_to_clnup_del;
1850
1851 OPEN c_spa_clnup(p_term_cal_type,
1852 p_term_sequence_number,
1853 p_person_id,
1854 p_dest_course_cd
1855 );
1856 FETCH c_spa_clnup INTO v_spa_clnup_rec;
1857
1858 IF c_spa_clnup%NOTFOUND THEN
1859 CLOSE c_spa_clnup;
1860 RETURN;
1861 END IF;
1862
1863 CLOSE c_spa_clnup;
1864
1865 OPEN c_sca(p_person_id,p_dest_course_cd);
1866 FETCH c_sca INTO v_sca_dest_rec;
1867 CLOSE c_sca;
1868
1869 v_src_course_cd := v_spa_clnup_rec.transfer_course_cd;
1870
1871 OPEN c_sca(p_person_id,v_src_course_cd);
1872 FETCH c_sca INTO v_sca_src_rec;
1873 CLOSE c_sca;
1874
1875
1876 IF p_mode = 'CLEANUP' THEN
1877
1878 -- Since the cleanup mode is done in a different way in intra career transfer
1879 -- check if the transfer is within careers
1880 IF is_career_model_enabled THEN
1881
1882 IF is_tranfer_across_careers(
1883 v_sca_dest_rec.course_cd,
1884 v_sca_dest_rec.version_number,
1885 v_sca_src_rec.course_cd,
1886 v_sca_src_rec.version_number,
1887 v_src_career_type
1888 ) = FALSE THEN
1889
1890 l_trans_within_careers := TRUE;
1891
1892 END IF;
1893
1894 END IF;
1895
1896
1897 l_status_date := SYSDATE;
1898 l_trans_status := 'C';
1899 l_stdnt_conf_ind := v_sca_dest_rec.student_confirmed_ind;
1900 l_comm_date := v_sca_dest_rec.commencement_dt;
1901 l_course_att_status := v_sca_dest_rec.course_attempt_status;
1902
1903 -- If it is within career
1904 IF l_trans_within_careers THEN
1905
1906 -- Delete unit attempts existing in the effective and future terms against destination program.
1907 IF NOT del_sua(
1908 p_person_id,
1909 p_dest_course_cd,
1910 p_term_cal_type,
1911 p_term_sequence_number
1912 ) THEN
1913
1914 ----IF not able to delete then update unit attempt status as 'DROPPED'
1915 upd_sua(
1916 p_person_id,
1917 p_dest_course_cd,
1918 p_term_cal_type,
1919 p_term_sequence_number
1920 );
1921
1922 END IF;
1923
1924 OPEN c_sua ( p_person_id,p_dest_course_cd);
1925 FETCH c_sua INTO v_rec_exists;
1926
1927 -- if there are no unit attempts with status other than dropped/unconfirmed
1928 -- then delete child records and set the status of destination program
1929 -- as unconfirmed
1930 IF c_sua%NOTFOUND THEN
1931
1932 -- setting the commencement date,student confirmed indicator
1933 -- as course attempt status is unconfirmed.
1934 l_stdnt_conf_ind := 'N';
1935 l_comm_date := NULL;
1936 l_course_att_status := cst_unconfirm;
1937
1938 --- Delete all the child records of the destination program
1939
1940 del_gua( p_person_id,p_dest_course_cd);
1941
1942 del_esaa( p_person_id,p_dest_course_cd);
1943
1944 del_gsa( p_person_id,p_dest_course_cd);
1945
1946 del_hssc( p_person_id,p_dest_course_cd);
1947
1948 del_hssa( p_person_id,p_dest_course_cd);
1949
1950 del_pr_rule_appl( p_person_id, p_dest_course_cd);
1951
1952 del_psaa( p_person_id,p_dest_course_cd);
1953
1954 del_scho( p_person_id,p_dest_course_cd);
1955
1956 del_scae( p_person_id,p_dest_course_cd);
1957
1958 del_scan( p_person_id,p_dest_course_cd);
1959
1960 del_fi_fee( p_person_id,p_dest_course_cd);
1961
1962 del_ps_intm( p_person_id,p_dest_course_cd);
1963
1964 del_pr_cohinst_rank( p_person_id,p_dest_course_cd);
1965
1966 del_as_anon_id_ps( p_person_id,p_dest_course_cd);
1967
1968 END IF; -- End of IF c_sua%NOTFOUND
1969 CLOSE c_sua;
1970
1971 END IF; -- End of IF l_trans_within_careers
1972
1973
1974 -- Update the transfer record set status_flag = 'C' and status_date = SYSDATE
1975 IGS_PS_STDNT_TRN_PKG.update_row(
1976 X_ROWID => v_spa_clnup_rec.rowid,
1977 X_PERSON_ID => v_spa_clnup_rec.person_id,
1978 X_COURSE_CD => v_spa_clnup_rec.course_cd,
1979 X_TRANSFER_COURSE_CD => v_spa_clnup_rec.transfer_course_cd,
1980 X_TRANSFER_DT => v_spa_clnup_rec.transfer_dt,
1981 X_COMMENTS => v_spa_clnup_rec.comments,
1982 X_APPROVED_DATE => v_spa_clnup_rec.approved_date,
1983 X_EFFECTIVE_TERM_CAL_TYPE => v_spa_clnup_rec.effective_term_cal_type,
1984 X_EFFECTIVE_TERM_SEQUENCE_NUM => v_spa_clnup_rec.effective_term_sequence_num,
1985 X_DISCONTINUE_SOURCE_FLAG => v_spa_clnup_rec.discontinue_source_flag,
1986 X_UOOIDS_TO_TRANSFER => v_spa_clnup_rec.uooids_to_transfer,
1987 X_SUSA_TO_TRANSFER => v_spa_clnup_rec.susa_to_transfer,
1988 X_TRANSFER_ADV_STAND_FLAG => v_spa_clnup_rec.transfer_adv_stand_flag,
1989 X_STATUS_DATE => l_status_date,
1990 X_STATUS_FLAG => l_trans_status
1991 );
1992
1993 -- Start of deletion and updation/creation of term records
1994 IF is_career_model_enabled THEN
1995 --
1996 l_tran_across_careers := is_tranfer_across_careers(
1997 v_sca_dest_rec.course_cd,
1998 v_sca_dest_rec.version_number,
1999 v_sca_src_rec.course_cd,
2000 v_sca_src_rec.version_number,
2001 v_src_career_type
2002 );
2003 ELSE
2004 -- If in program mode, consider it as across career transfer
2005 l_tran_across_careers := TRUE;
2006 END IF;
2007
2008 IF NOT l_tran_across_careers OR (l_tran_across_careers AND v_sca_src_rec.key_program = 'Y') THEN
2009 -- if within career or across career with source key.
2010 terms_ind := 0;
2011 -- Loop to delete destination term records in the terms in which future dated transfer was
2012 -- effective. Delete of term records is only valid for within career transfers.
2013 FOR v_spat_recd IN c_spat(p_person_id, p_dest_course_cd, p_term_cal_type,p_term_sequence_number)
2014 LOOP
2015
2016 terms(terms_ind).term_cal_type :=v_spat_recd.term_cal_type;
2017 terms(terms_ind).term_seq_num :=v_spat_recd.term_sequence_number;
2018 terms(terms_ind).key_program_flag := v_spat_recd.key_program_flag;
2019 -- Delete the term records against the destination program
2020 IF NOT l_tran_across_careers THEN
2021 IGS_EN_SPA_TERMS_PKG.DELETE_ROW(v_spat_recd.rowid);
2022 END IF;
2023 terms_ind := terms_ind + 1;
2024 END LOOP;
2025 v_ripple := TRUE;
2026 FOR i IN terms.FIRST.. terms.LAST LOOP
2027 -- create term records against source program
2028
2029 l_acad_cal := igs_en_spa_terms_api.get_spat_acad_cal_type(p_person_id,
2030 v_src_course_cd,
2031 terms(i).term_cal_type,
2032 terms(i).term_seq_num);
2033 OPEN c_term_acad_rel(l_acad_cal,terms(i).term_cal_type,terms(i).term_seq_num);
2034 FETCH c_term_acad_rel INTO l_dummy;
2035
2036 OPEN c_spat_src(p_person_id, v_src_course_cd, terms(i).term_cal_type,terms(i).term_seq_num);
2037 FETCH c_spat_src INTO v_spat_rec;
2038
2039
2040 IF v_spat_rec.key_program_flag = 'Y' AND v_ripple THEN
2041 -- if Source was key and this is the first time entering into loop send key as changing to terms api
2042 l_key_program_flag := 'Y';
2043 IF c_term_acad_rel%NOTFOUND THEN
2044
2045 OPEN c_src_eff_term(v_src_course_cd);
2046 FETCH c_src_eff_term INTO l_term_cal_type, l_term_seq_num;
2047 IF (c_src_eff_term%FOUND) THEN
2048 igs_en_spa_terms_api.CREATE_UPDATE_TERM_REC(
2049 P_PERSON_ID => p_person_id,
2050 P_PROGRAM_CD => v_src_course_cd,
2051 P_TERM_CAL_TYPE => l_term_cal_type,
2052 P_TERM_SEQUENCE_NUMBER => l_term_seq_num,
2053 P_KEY_PROGRAM_FLAG => l_key_program_flag, -- using key flag from terms table
2054 p_ripple_frwrd => v_ripple,
2055 p_message_name => v_message_name,
2056 p_update_rec => TRUE
2057 );
2058 END IF;
2059
2060 END IF;
2061
2062 ELSE
2063 l_key_program_flag := FND_API.G_MISS_CHAR;
2064 END IF;
2065
2066
2067 IF (c_term_acad_rel%FOUND)THEN
2068
2069
2070 -- Terms api called as key changed if source is key and with ripple forward as TRUE only for the first time in loop
2071 igs_en_spa_terms_api.CREATE_UPDATE_TERM_REC(
2072 P_PERSON_ID => p_person_id,
2073 P_PROGRAM_CD => v_src_course_cd,
2074 P_TERM_CAL_TYPE => terms(i).term_cal_type,
2075 P_TERM_SEQUENCE_NUMBER => terms(i).term_seq_num,
2076 P_KEY_PROGRAM_FLAG => l_key_program_flag, -- using key flag from terms table
2077 p_ripple_frwrd => v_ripple,
2078 p_message_name => v_message_name,
2079 p_update_rec => TRUE
2080 );
2081 END IF;
2082 CLOSE c_term_acad_rel;
2083
2084 v_ripple := FALSE;
2085 -- create_update_term_rec should be called with p_ripple_frwrd as TRUE only for the first
2086 -- run. The first record selected by the cursor c_spat is latest term for which the term
2087 -- record has to be created. Ripple forward has to be passed as TRUE, inorder to ripple
2088 -- the changes for any future terms that are existing for the source.
2089 CLOSE c_spat_src;
2090
2091 END LOOP;
2092
2093 END IF;
2094 -- End of deletion and updation/creation of term records
2095
2096
2097
2098 -- update the destination program attempt with the values of l_stdnt_conf_ind,l_comm_date,l_course_att_status
2099 -- and set the future dated transfer flag to 'N'
2100 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
2101 X_ROWID => v_sca_dest_rec.ROWID,
2102 X_PERSON_ID => v_sca_dest_rec.PERSON_ID,
2103 X_COURSE_CD => v_sca_dest_rec.COURSE_CD,
2104 X_VERSION_NUMBER => v_sca_dest_rec.VERSION_NUMBER,
2105 X_CAL_TYPE => v_sca_dest_rec.CAL_TYPE,
2106 X_LOCATION_CD => v_sca_dest_rec.LOCATION_CD,
2107 X_ATTENDANCE_MODE => v_sca_dest_rec.ATTENDANCE_MODE,
2108 X_ATTENDANCE_TYPE => v_sca_dest_rec.ATTENDANCE_TYPE,
2109 X_COO_ID => v_sca_dest_rec.COO_ID,
2110 X_STUDENT_CONFIRMED_IND => l_stdnt_conf_ind,
2111 X_COMMENCEMENT_DT => l_comm_date,
2112 X_COURSE_ATTEMPT_STATUS => l_course_att_status,
2113 X_PROGRESSION_STATUS => v_sca_dest_rec.PROGRESSION_STATUS,
2114 X_DERIVED_ATT_TYPE => v_sca_dest_rec.DERIVED_ATT_TYPE,
2115 X_DERIVED_ATT_MODE => v_sca_dest_rec.DERIVED_ATT_MODE,
2116 X_PROVISIONAL_IND => v_sca_dest_rec.PROVISIONAL_IND,
2117 X_DISCONTINUED_DT => v_sca_dest_rec.DISCONTINUED_DT,
2118 X_DISCONTINUATION_REASON_CD => v_sca_dest_rec.DISCONTINUATION_REASON_CD,
2119 X_LAPSED_DT => v_sca_dest_rec.LAPSED_DT,
2120 X_FUNDING_SOURCE => v_sca_dest_rec.FUNDING_SOURCE,
2121 X_EXAM_LOCATION_CD => v_sca_dest_rec.EXAM_LOCATION_CD,
2122 X_DERIVED_COMPLETION_YR => v_sca_dest_rec.DERIVED_COMPLETION_YR,
2123 X_DERIVED_COMPLETION_PERD => v_sca_dest_rec.DERIVED_COMPLETION_PERD,
2124 X_NOMINATED_COMPLETION_YR => v_sca_dest_rec.NOMINATED_COMPLETION_YR,
2125 X_NOMINATED_COMPLETION_PERD => v_sca_dest_rec.NOMINATED_COMPLETION_PERD,
2126 X_RULE_CHECK_IND => v_sca_dest_rec.RULE_CHECK_IND,
2127 X_WAIVE_OPTION_CHECK_IND => v_sca_dest_rec.WAIVE_OPTION_CHECK_IND,
2128 X_LAST_RULE_CHECK_DT => v_sca_dest_rec.LAST_RULE_CHECK_DT,
2129 X_PUBLISH_OUTCOMES_IND => v_sca_dest_rec.PUBLISH_OUTCOMES_IND,
2130 X_COURSE_RQRMNT_COMPLETE_IND => v_sca_dest_rec.COURSE_RQRMNT_COMPLETE_IND,
2131 X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_dest_rec.COURSE_RQRMNTS_COMPLETE_DT,
2132 X_S_COMPLETED_SOURCE_TYPE => v_sca_dest_rec.S_COMPLETED_SOURCE_TYPE,
2133 X_OVERRIDE_TIME_LIMITATION => v_sca_dest_rec.OVERRIDE_TIME_LIMITATION,
2134 X_ADVANCED_STANDING_IND => v_sca_dest_rec.ADVANCED_STANDING_IND,
2135 X_FEE_CAT => v_sca_dest_rec.FEE_CAT,
2136 X_CORRESPONDENCE_CAT => v_sca_dest_rec.CORRESPONDENCE_CAT,
2137 X_SELF_HELP_GROUP_IND => v_sca_dest_rec.SELF_HELP_GROUP_IND,
2138 X_LOGICAL_DELETE_DT => v_sca_dest_rec.LOGICAL_DELETE_DT,
2139 X_ADM_ADMISSION_APPL_NUMBER => v_sca_dest_rec.ADM_ADMISSION_APPL_NUMBER,
2140 X_ADM_NOMINATED_COURSE_CD => v_sca_dest_rec.ADM_NOMINATED_COURSE_CD,
2141 X_ADM_SEQUENCE_NUMBER => v_sca_dest_rec.ADM_SEQUENCE_NUMBER,
2142 X_LAST_DATE_OF_ATTENDANCE => v_sca_dest_rec.LAST_DATE_OF_ATTENDANCE,
2143 X_DROPPED_BY => v_sca_dest_rec.DROPPED_BY,
2144 X_IGS_PR_CLASS_STD_ID => v_sca_dest_rec.IGS_PR_CLASS_STD_ID ,
2145 X_PRIMARY_PROGRAM_TYPE => v_sca_dest_rec.primary_program_type,
2146 X_PRIMARY_PROG_TYPE_SOURCE => v_sca_dest_rec.PRIMARY_PROG_TYPE_SOURCE,
2147 X_CATALOG_CAL_TYPE => v_sca_dest_rec.CATALOG_CAL_TYPE,
2148 X_CATALOG_SEQ_NUM => v_sca_dest_rec.CATALOG_SEQ_NUM,
2149 X_KEY_PROGRAM => v_sca_dest_rec.key_program,
2150 X_MANUAL_OVR_CMPL_DT_IND => v_sca_dest_rec.MANUAL_OVR_CMPL_DT_IND,
2151 X_OVERRIDE_CMPL_DT => v_sca_dest_rec.OVERRIDE_CMPL_DT,
2152 X_MODE => 'R' ,
2153 X_ATTRIBUTE_CATEGORY => v_sca_dest_rec.ATTRIBUTE_CATEGORY,
2154 X_FUTURE_DATED_TRANS_FLAG => 'C',
2155 X_ATTRIBUTE1 => v_sca_dest_rec.ATTRIBUTE1,
2156 X_ATTRIBUTE2 => v_sca_dest_rec.ATTRIBUTE2,
2157 X_ATTRIBUTE3 => v_sca_dest_rec.ATTRIBUTE3,
2158 X_ATTRIBUTE4 => v_sca_dest_rec.ATTRIBUTE4,
2159 X_ATTRIBUTE5 => v_sca_dest_rec.ATTRIBUTE5,
2160 X_ATTRIBUTE6 => v_sca_dest_rec.ATTRIBUTE6,
2161 X_ATTRIBUTE7 => v_sca_dest_rec.ATTRIBUTE7,
2162 X_ATTRIBUTE8 => v_sca_dest_rec.ATTRIBUTE8,
2163 X_ATTRIBUTE9 => v_sca_dest_rec.ATTRIBUTE9,
2164 X_ATTRIBUTE10 => v_sca_dest_rec.ATTRIBUTE10,
2165 X_ATTRIBUTE11 => v_sca_dest_rec.ATTRIBUTE11,
2166 X_ATTRIBUTE12 => v_sca_dest_rec.ATTRIBUTE12,
2167 X_ATTRIBUTE13 => v_sca_dest_rec.ATTRIBUTE13,
2168 X_ATTRIBUTE14 => v_sca_dest_rec.ATTRIBUTE14,
2169 X_ATTRIBUTE15 => v_sca_dest_rec.ATTRIBUTE15,
2170 X_ATTRIBUTE16 => v_sca_dest_rec.ATTRIBUTE16,
2171 X_ATTRIBUTE17 => v_sca_dest_rec.ATTRIBUTE17,
2172 X_ATTRIBUTE18 => v_sca_dest_rec.ATTRIBUTE18,
2173 X_ATTRIBUTE19 => v_sca_dest_rec.ATTRIBUTE19,
2174 X_ATTRIBUTE20 => v_sca_dest_rec.ATTRIBUTE20);
2175
2176 END IF; -- END OF CLean Up
2177
2178 IF p_mode = 'DELETE' THEN
2179
2180 -- Delete transfer record
2181 del_ps_trnsf( p_person_id,
2182 p_dest_course_cd,
2183 p_term_cal_type,
2184 p_term_sequence_number);
2185
2186 -- if the destination program is unconfirmed then only
2187 -- delete other child records and if no dropped unit
2188 -- attempts exists then drop the program attempt.
2189 IF v_sca_dest_rec.course_attempt_status = cst_unconfirm THEN
2190
2191 -- Delete unit set attempts
2192 del_susa( p_person_id,p_dest_course_cd);
2193
2194 -- update/delete research candidature details
2195 upd_del_re_candidature(
2196 p_person_id,
2197 p_dest_course_cd,
2198 v_sca_dest_rec.adm_admission_appl_number,
2199 v_sca_dest_rec.adm_nominated_course_cd,
2200 v_sca_dest_rec.adm_sequence_number
2201 );
2202
2203 -- Delete advanced standing details
2204 del_av(
2205 p_person_id,
2206 p_dest_course_cd,
2207 v_sca_dest_rec.version_number
2208 );
2209
2210 OPEN c_sua_drop ( p_person_id,p_dest_course_cd);
2211 FETCH c_sua_drop INTO v_rec_exists;
2212
2213 --- If dropped unit attempts doesnot exists then delete program attempt.
2214 IF (c_sua_drop%NOTFOUND) THEN
2215
2216 IGS_EN_STDNT_PS_ATT_PKG.DELETE_ROW(v_sca_dest_rec.rowid);
2217
2218 END IF;
2219
2220 CLOSE c_sua_drop;
2221
2222 END IF; --End of IF v_sca_dest_rec.course_attempt_status = cst_unconfirm
2223
2224 END IF; -- End of Delete mode
2225
2226 EXCEPTION
2227 WHEN OTHERS THEN
2228
2229 IF c_spa_clnup%ISOPEN THEN
2230 CLOSE c_spa_clnup;
2231 END IF;
2232 IF c_sca%ISOPEN THEN
2233 CLOSE c_sca;
2234 END IF;
2235 IF c_sua_drop%ISOPEN THEN
2236 CLOSE c_sua_drop;
2237 END IF;
2238 ROLLBACK TO sp_unable_to_clnup_del;
2239 App_Exception.Raise_Exception;
2240
2241 END cleanup_dest_program;
2242
2243
2244 /*----------------------------------------------------------------------------
2245 || Created By : bdeviset
2246 || Created On : 18-NOV-2004
2247 || Purpose : Processing future dated transfer records in process/cleanup/delete mode.
2248 || In cleanup/delete modes it calls cleanup_dest_program.
2249 || In process mode it discontinues the source program including the units
2250 || and unsets the future dated transfer flag for destination program
2251 ||
2252 || Known limitations, enhancements or remarks :
2253 || Change History :
2254 || Who When What
2255 ||
2256 ------------------------------------------------------------------------------*/
2257
2258 PROCEDURE process_fut_dt_trans(
2259 errbuf OUT NOCOPY VARCHAR2,
2260 retcode OUT NOCOPY NUMBER,
2261 p_term_cal_comb IN VARCHAR2,
2262 p_mode IN VARCHAR2,
2263 p_ignore_warnings IN VARCHAR2,
2264 p_drop_enrolled IN VARCHAR2
2265 )
2266 IS
2267
2268 -- Cursor to fetch the records to process
2269 CURSOR c_spa_clnup(cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
2270 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
2271 SELECT sct.*
2272 FROM IGS_PS_STDNT_TRN sct
2273 WHERE effective_term_cal_type = cp_term_cal_type
2274 AND effective_term_sequence_num = cp_term_sequence_number
2275 AND ( sct.status_flag = 'U' and p_mode IN ('PROCESS','CLEANUP')
2276 OR sct.status_flag = 'C' and p_mode IN ('DELETE'));
2277
2278 -- cursor to fetch student program details
2279 CURSOR c_sca (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2280 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
2281 SELECT sca.rowid,sca.*
2282 FROM IGS_EN_STDNT_PS_ATT sca
2283 WHERE sca.person_id = cp_person_id
2284 AND sca.course_cd = cp_course_cd;
2285
2286 -- To fetch the date aliases set up in calendar configuration table
2287 CURSOR c_enr_cal_conf IS
2288 SELECT BEGIN_TRANS_DT_ALIAS,CLEAN_TRANS_DT_ALIAS
2289 FROM IGS_EN_CAL_CONF
2290 WHERE s_control_num = 1;
2291
2292 -- cursor to get the clean up date alias value
2293 CURSOR c_cln_up_dt_alias_val(cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
2294 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE,
2295 cp_cleanup_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE ) IS
2296 SELECT alias_val
2297 FROM igs_ca_da_inst_v
2298 WHERE cal_type = cp_term_cal_type
2299 AND ci_sequence_number = cp_term_sequence_number
2300 AND dt_alias = cp_cleanup_dt_alias;
2301
2302 -- cursor to get the begin program transfer details
2303 CURSOR c_begin_pt_dt_alias_val(cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
2304 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE,
2305 cp_begin_pt_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
2306 SELECT alias_val
2307 FROM igs_ca_da_inst_v
2308 WHERE cal_type = cp_term_cal_type
2309 AND ci_sequence_number = cp_term_sequence_number
2310 AND dt_alias = cp_begin_pt_dt_alias;
2311
2312 -- cursor to get the person number
2313 CURSOR c_person_num(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE) IS
2314 SELECT party_number
2315 FROM HZ_PARTIES
2316 WHERE party_id = cp_person_id;
2317
2318 -- cursor to get the load calendar end date
2319 CURSOR c_load_cal_end_dt(cp_term_cal IGS_CA_INST.cal_type%TYPE,
2320 cp_term_seq_num IGS_CA_INST.sequence_number%TYPE) IS
2321 SELECT end_dt
2322 FROM igs_ca_inst
2323 WHERE cal_type = cp_term_cal
2324 AND sequence_number = cp_term_seq_num;
2325
2326
2327 -- Cursor to know if the destination program is primary in any of the prev terms
2328 -- whose start date is greater than transfer date
2329 CURSOR c_prim_in_prev_terms(cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2330 cp_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
2331 cp_cur_term_cal IGS_CA_INST.cal_type%TYPE,
2332 cp_cur_term_seq_num IGS_CA_INST.sequence_number%TYPE,
2333 cp_acad_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
2334 cp_transfer_dt IGS_PS_STDNT_TRN.transfer_dt%TYPE ) IS
2335 SELECT 'x'
2336 FROM igs_ca_inst ci2,
2337 igs_ca_inst_rel cir,
2338 igs_ca_type ct,
2339 igs_ca_inst ci1,
2340 igs_ca_stat cs
2341 WHERE
2342 ci2.cal_type = cir.sub_cal_type AND
2343 ci2.sequence_number = cir.sub_ci_sequence_number AND
2344 cir.sup_cal_type = cp_acad_cal_type AND
2345 ci2.cal_type = ct.cal_type AND
2346 ct.s_cal_cat = 'LOAD' AND
2347 cs.cal_status = ci1.cal_status AND
2348 cs.s_cal_status = 'ACTIVE' AND
2349 ci1.cal_type = cp_cur_term_cal AND
2350 ci1.sequence_number = cp_cur_term_seq_num AND
2351 ci2.start_dt < ci1.start_dt AND
2352 ci2.start_dt > cp_transfer_dt
2353 AND EXISTS (SELECT 'x' from igs_en_spa_terms spat
2354 WHERE spat.person_id = cp_person_id
2355 AND spat.program_cd = cp_dest_course_cd
2356 AND spat.term_cal_type = ci2.cal_type
2357 AND spat.term_sequence_number = ci2.sequence_number)
2358 ORDER BY ci2.start_dt DESC;
2359
2360
2361 -- Cursor to get uoo details to be transferred
2362 CURSOR c_uoo (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2363 cp_src_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
2364 cp_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
2365 SELECT uoo_id,core_indicator_code,unit_attempt_status
2366 FROM IGS_EN_SU_ATTEMPT sua
2367 WHERE person_id = cp_person_id
2368 AND course_cd = cp_src_course_cd
2369 AND not exists (SELECT 'x'
2370 FROM igs_en_su_attempt sua2
2371 WHERE person_id = cp_person_id
2372 AND course_cd= cp_dest_course_cd
2373 AND unit_attempt_status <> 'DROPPED'
2374 AND sua.uoo_id = sua2.uoo_id)
2375 AND unit_Attempt_status not in ('DROPPED','UNCONFIRM');
2376
2377
2378 CURSOR c_sct(cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
2379 cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE,
2380 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2381 cp_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE
2382 ) IS
2383 SELECT 'X'
2384 FROM IGS_PS_STDNT_TRN sct
2385 WHERE effective_term_cal_type = cp_term_cal_type
2386 AND effective_term_sequence_num = cp_term_sequence_number
2387 AND sct.person_id = cp_person_id
2388 AND sct.course_cd = cp_dest_course_cd
2389 AND sct.status_flag = 'C';
2390
2391
2392
2393
2394 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
2395 cst_prog_trans CONSTANT VARCHAR2(20) := 'PROGRAM_TRANSFER';
2396 v_begin_pt_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE;
2397 v_begin_pt_dt_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
2398 v_cleanup_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE;
2399 v_cleanup_dt_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
2400 is_prim_in_prev_term VARCHAR2(1);
2401 v_sca_src_rec c_sca%ROWTYPE;
2402 v_sca_dest_rec c_sca%ROWTYPE;
2403 l_term_cal_type IGS_CA_DA_INST.cal_type%TYPE;
2404 l_term_sequence_number IGS_CA_DA_INST.sequence_number%TYPE;
2405 l_end_dt IGS_CA_INST.end_dt%TYPE;
2406 v_del BOOLEAN;
2407 v_person_num HZ_PARTIES.party_number%TYPE;
2408 l_acad_cal_seq_num IGS_CA_DA_INST.sequence_number%TYPE;
2409 l_acad_cal_type IGS_CA_DA_INST.cal_type%TYPE;
2410 l_message_name VARCHAR2(30);
2411 l_show_warning VARCHAR2(1);
2412 l_rec_exists BOOLEAN;
2413 l_tran_across_careers BOOLEAN;
2414 l_src_career_type IGS_PS_VER.COURSE_TYPE%TYPE;
2415 l_new_dest_key_prgm_flag IGS_EN_STDNT_PS_ATT.KEY_PROGRAM%TYPE;
2416 l_msg VARCHAR2(4000);
2417 l_unit_sets_having_errors VARCHAR2(4000);
2418 l_uoo_ids_having_errors VARCHAR2(4000);
2419 l_return_status VARCHAR2(1);
2420 l_msg_count NUMBER;
2421 l_msg_data VARCHAR2(6000);
2422 l_process_mode VARCHAR2(10);
2423 v_rec_exists VARCHAR2(1);
2424
2425
2426 BEGIN
2427 retcode := 0;
2428 l_msg := NULL;
2429 l_acad_cal_seq_num := NULL;
2430 l_term_cal_type := RTRIM(SUBSTR(P_TERM_CAL_COMB,101,10));
2431 l_term_sequence_number := TO_NUMBER(RTRIM(SUBSTR(P_TERM_CAL_COMB,112,6)));
2432 l_rec_exists := FALSE;
2433
2434 IF p_ignore_warnings = 'Y' THEN
2435
2436 l_show_warning := 'N';
2437
2438 ELSE
2439
2440 l_show_warning := 'Y';
2441
2442 END IF;
2443
2444 IF p_drop_enrolled = 'Y' THEN
2445
2446 l_process_mode := 'DROP';
2447
2448 ELSE
2449
2450 l_process_mode := NULL;
2451
2452 END IF;
2453
2454 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2455 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2456 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Term Calendar :'||l_term_cal_type||', Term Calendar Sequence Number :'||l_term_sequence_number||', Ignore warnings :'||p_ignore_warnings||', Drop Enrolled :'||p_drop_enrolled);
2457 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2458
2459 OPEN c_enr_cal_conf;
2460 FETCH c_enr_cal_conf INTO v_begin_pt_dt_alias,v_cleanup_dt_alias;
2461
2462 IF c_enr_cal_conf%FOUND THEN
2463
2464 CLOSE c_enr_cal_conf;
2465 IF p_mode = 'PROCESS' AND v_begin_pt_dt_alias IS NULL THEN
2466
2467 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_BEG_PT_DAV_LT_SYSDATE');
2468 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2469 RETURN;
2470
2471 END IF;
2472
2473 IF p_mode = 'CLEANUP' AND v_cleanup_dt_alias IS NULL THEN
2474
2475 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_CLNUP_DAV_GE_SYSDATE');
2476 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2477 RETURN;
2478
2479 END IF;
2480
2481 ELSE
2482
2483 CLOSE c_enr_cal_conf;
2484 RETURN;
2485
2486 END IF;
2487
2488 IF p_mode = 'CLEANUP' THEN
2489 -- If cleanup date alias is not set then OR
2490 -- if cleanup date alias is set and is greater than or equal to sysdate then return
2491 OPEN c_cln_up_dt_alias_val(l_term_cal_type,l_term_sequence_number,v_cleanup_dt_alias);
2492 FETCH c_cln_up_dt_alias_val INTO v_cleanup_dt_alias_val;
2493
2494 IF c_cln_up_dt_alias_val%NOTFOUND OR
2495 (c_cln_up_dt_alias_val%FOUND AND (v_cleanup_dt_alias_val >= SYSDATE)) THEN
2496
2497 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_CLNUP_DAV_GE_SYSDATE');
2498 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2499
2500 CLOSE c_cln_up_dt_alias_val;
2501 RETURN;
2502
2503 ELSE
2504
2505 CLOSE c_cln_up_dt_alias_val;
2506
2507 END IF;
2508
2509 END IF;
2510
2511
2512 -- Get the load calendar end date
2513 IF p_mode = 'PROCESS' THEN
2514
2515 OPEN c_load_cal_end_dt(l_term_cal_type,l_term_sequence_number);
2516 FETCH c_load_cal_end_dt INTO l_end_dt;
2517 CLOSE c_load_cal_end_dt;
2518
2519 -- If begin program transfer date alias is found and
2520 -- ( sysdate is not between begin program date alias and loadcalendar end date ) return
2521 -- or if it is not found then exit
2522 OPEN c_begin_pt_dt_alias_val(l_term_cal_type,l_term_sequence_number,v_begin_pt_dt_alias);
2523 FETCH c_begin_pt_dt_alias_val INTO v_begin_pt_dt_alias_val;
2524
2525 IF c_begin_pt_dt_alias_val%NOTFOUND OR
2526 (c_begin_pt_dt_alias_val%FOUND AND (SYSDATE NOT BETWEEN v_begin_pt_dt_alias_val AND l_end_dt) ) THEN
2527
2528 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_BEG_PT_DAV_LT_SYSDATE');
2529 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2530
2531 CLOSE c_begin_pt_dt_alias_val;
2532 RETURN;
2533
2534 END IF;
2535
2536 CLOSE c_begin_pt_dt_alias_val;
2537
2538 END IF;
2539
2540 FOR v_clnup_rec IN c_spa_clnup(l_term_cal_type,l_term_sequence_number) LOOP
2541
2542 BEGIN
2543 l_rec_exists := TRUE;
2544
2545 OPEN c_person_num(v_clnup_rec.person_id);
2546 FETCH c_person_num INTO v_person_num;
2547 CLOSE c_person_num;
2548
2549 FND_FILE.PUT_LINE (FND_FILE.LOG, '-----------------------------------------------------------------------------');
2550 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Person :'||v_person_num||' Source Course :'||v_clnup_rec.transfer_course_cd||' Destination Course :'||v_clnup_rec.course_cd);
2551 FND_FILE.PUT_LINE (FND_FILE.LOG, '-----------------------------------------------------------------------------');
2552 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2553
2554 IF p_mode IN ('CLEANUP','DELETE') THEN
2555
2556 BEGIN
2557 -- Call the cleanup destination program with p_mode as cleanup/delete
2558 IGS_EN_FUTURE_DT_TRANS.cleanup_dest_program(
2559 p_person_id => v_clnup_rec.person_id,
2560 p_dest_course_cd => v_clnup_rec.course_cd,
2561 p_term_cal_type => l_term_cal_type,
2562 p_term_sequence_number => l_term_sequence_number,
2563 p_mode => p_mode
2564 );
2565
2566 OPEN c_sct(l_term_cal_type,
2567 l_term_sequence_number,
2568 v_clnup_rec.person_id,
2569 v_clnup_rec.course_cd);
2570 FETCH c_sct INTO v_rec_exists;
2571
2572 IF p_mode = 'DELETE' THEN
2573
2574 -- If no transfer record is found then log the delete successful message
2575 IF c_sct%NOTFOUND THEN
2576
2577 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_DEL_DEST_PROG');
2578 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2579
2580 ELSE
2581 -- If destination program attempt record is found log the cant delete message
2582 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_CANT_DEL_DEST_PROG');
2583 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2584
2585 END IF;
2586
2587 END IF;
2588 CLOSE c_sct;
2589
2590
2591 OPEN c_sca (v_clnup_rec.person_id,v_clnup_rec.course_cd);
2592 FETCH c_sca INTO v_sca_dest_rec;
2593 -- If the mode is cleanup and future dated transfer flag of
2594 -- of destination program attempt is set to 'N'
2595 -- then log success message
2596 IF p_mode = 'CLEANUP' AND c_sca%FOUND
2597 AND v_sca_dest_rec.future_dated_trans_flag = 'C' THEN
2598
2599 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_UNCONF_DEST_PROG');
2600 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2601
2602 END IF;
2603
2604 CLOSE c_sca;
2605
2606
2607 EXCEPTION
2608 WHEN OTHERS THEN
2609
2610 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2611
2612 IF p_mode = 'CLEANUP' THEN
2613
2614 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_CANT_UNCONF_DEST_PROG');
2615 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2616
2617 END IF;
2618
2619
2620 END;
2621
2622 -- Process Mode
2623 ELSE
2624
2625 OPEN c_sca (v_clnup_rec.person_id,v_clnup_rec.transfer_course_cd);
2626 FETCH c_sca INTO v_sca_dest_rec;
2627 CLOSE c_sca;
2628
2629 --- If destination program attempt is intermitted/lapsed/discontinued/unconfirme
2630 --- then dont allow the transfer
2631 IF v_sca_dest_rec.course_attempt_status = 'INTERMITTED' THEN
2632
2633 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_TRN_DEST_INTM');
2634 FND_MESSAGE.SET_TOKEN('SOURCE_PROGRAM', v_clnup_rec.transfer_course_cd);
2635 FND_MESSAGE.SET_TOKEN('DEST_PROGRAM', v_clnup_rec.course_cd);
2636 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2637
2638 ELSIF v_sca_dest_rec.course_attempt_status = 'LAPSED' THEN
2639
2640 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_TRN_DEST_LAPSE');
2641 FND_MESSAGE.SET_TOKEN('SOURCE_PROGRAM', v_clnup_rec.transfer_course_cd);
2642 FND_MESSAGE.SET_TOKEN('DEST_PROGRAM', v_clnup_rec.course_cd);
2643 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2644
2645 ELSIF v_sca_dest_rec.course_attempt_status = 'DISCONTIN' THEN
2646
2647 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_TRN_DEST_DISCON');
2648 FND_MESSAGE.SET_TOKEN('SOURCE_PROGRAM', v_clnup_rec.transfer_course_cd);
2649 FND_MESSAGE.SET_TOKEN('DEST_PROGRAM', v_clnup_rec.course_cd);
2650 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2651
2652 ELSIF v_sca_dest_rec.course_attempt_status = 'UNCONFIRM' THEN
2653
2654 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_TRN_DEST_UNCONF');
2655 FND_MESSAGE.SET_TOKEN('SOURCE_PROGRAM', v_clnup_rec.transfer_course_cd);
2656 FND_MESSAGE.SET_TOKEN('DEST_PROGRAM', v_clnup_rec.course_cd);
2657 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2658
2659
2660 ELSE
2661
2662 OPEN c_sca (v_clnup_rec.person_id,v_clnup_rec.transfer_course_cd);
2663 FETCH c_sca INTO v_sca_src_rec;
2664 CLOSE c_sca;
2665
2666 IF is_career_model_enabled THEN
2667
2668 l_tran_across_careers := is_tranfer_across_careers(
2669 v_sca_src_rec.course_cd,
2670 v_sca_src_rec.version_number,
2671 v_sca_dest_rec.COURSE_CD,
2672 v_sca_dest_rec.version_number,
2673 l_src_career_type
2674 );
2675 -- if the transfer is across careers and destination program is secoondary
2676 -- then dont allow the transfer
2677 IF l_tran_across_careers AND v_sca_dest_rec.primary_program_type = 'SECONDARY' THEN
2678
2679 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_TRN_SEC_DEST_INTER_CAR');
2680 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2681 App_Exception.Raise_Exception;
2682
2683 END IF;
2684 -- If the transfer is within careers
2685 IF NOT l_tran_across_careers THEN
2686
2687 -- Check If destination program is primary in the previous term
2688 -- if so dont transfer
2689 OPEN c_prim_in_prev_terms(v_clnup_rec.person_id,
2690 v_clnup_rec.course_cd,
2691 l_term_cal_type,
2692 l_term_sequence_number,
2693 v_sca_dest_rec.cal_type,
2694 v_clnup_rec.transfer_dt
2695 );
2696 FETCH c_prim_in_prev_terms INTO is_prim_in_prev_term;
2697
2698 IF (c_prim_in_prev_terms%FOUND) THEN
2699
2700 CLOSE c_prim_in_prev_terms;
2701
2702 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PRIMARY_IN_PRIOR_TERM');
2703 FND_MESSAGE.SET_TOKEN('DEST_PROGRAM', v_clnup_rec.course_cd);
2704 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2705 EXIT;
2706
2707 END IF;
2708 CLOSE c_prim_in_prev_terms;
2709
2710 END IF; -- if condition to check whether the transfer is within careers
2711
2712 END IF; -- if condition to check if career model is enabled
2713
2714
2715 -- Get academic calendar details
2716 IGS_EN_GEN_015.get_academic_cal(
2717 p_person_id => v_sca_src_rec.person_id,
2718 p_course_cd => v_sca_src_rec.course_cd,
2719 p_effective_dt => SYSDATE,
2720 p_acad_cal_type => l_acad_cal_type, -- OUT parameter
2721 p_acad_ci_sequence_number => l_acad_cal_seq_num, -- OUT parameter
2722 p_message => l_message_name
2723 );
2724 IF l_message_name IS NOT NULL -- AND l_acad_cal_seq_num IS NULL
2725 THEN
2726
2727 FND_FILE.PUT_LINE(FND_FILE.LOG,l_message_name);
2728
2729 ELSE
2730 -- Call the transfer api to carry out the transfer
2731 -- set the research details flag to 'N' as it has already been transferred
2732 -- when the future dated transfer is created
2733 IGS_EN_TRANSFER_APIS.program_transfer_api(
2734 p_person_id => v_clnup_rec.person_id,
2735 p_source_program_cd => v_sca_src_rec.course_cd,
2736 p_source_prog_ver => v_sca_src_rec.version_number,
2737 p_term_cal_type => l_term_cal_type,
2738 p_term_seq_num => l_term_sequence_number,
2739 p_acad_cal_type => l_acad_cal_type,
2740 p_acad_seq_num => l_acad_cal_seq_num,
2741 p_trans_approval_dt => v_clnup_rec.APPROVED_DATE,
2742 p_trans_actual_dt => v_clnup_rec.transfer_dt,
2743 -- Transfer date should be passed. This parameter is used while unit transfer records are created.
2744 p_dest_program_cd => v_clnup_rec.course_cd,
2745 p_dest_prog_ver => v_sca_dest_rec.version_number,
2746 p_dest_coo_id => v_sca_dest_rec.coo_id,
2747 p_uoo_ids_to_transfer => v_clnup_rec.UOOIDS_TO_TRANSFER,
2748 p_uoo_ids_not_selected => NULL,
2749 p_uoo_ids_having_errors => l_uoo_ids_having_errors,
2750 p_unit_sets_to_transfer => v_clnup_rec.SUSA_TO_TRANSFER,
2751 p_unit_sets_not_selected => NULL,
2752 p_unit_sets_having_errors => l_uoo_ids_having_errors,
2753 p_transfer_av => v_clnup_rec.TRANSFER_ADV_STAND_FLAG,
2754 p_transfer_re => 'N',
2755 p_discontinue_source => v_clnup_rec.DISCONTINUE_SOURCE_FLAG,
2756 p_show_warning => l_show_warning,
2757 p_call_from => 'PROCESS',
2758 p_process_mode => l_process_mode,
2759 p_return_status => l_return_status,
2760 p_msg_data => l_msg_data,
2761 p_msg_count => l_msg_count
2762 );
2763
2764
2765 log_err_messages(l_msg_count, l_msg_data);
2766
2767 IF l_return_status = 'U' THEN
2768
2769 retcode := 2;
2770
2771 ELSIF l_return_status = 'E' THEN
2772
2773 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_FUT_DT_TRANSF_FAIL');
2774 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2775
2776 ELSIF l_return_status = 'S' AND l_show_warning = 'Y' AND l_msg_count > 0 THEN
2777
2778 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_FUT_DT_TRANSF_WARN');
2779 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2780
2781 ELSE
2782
2783 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_FUT_DT_TRANSF');
2784 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2785
2786 END IF;
2787
2788 END IF; -- if condition for academic calendar details
2789
2790 END IF; -- If the dest prog is not intermitted/disc/lapsed
2791
2792 END IF; -- If condition for cleanup/delete process modes
2793
2794 EXCEPTION
2795 WHEN OTHERS THEN
2796
2797 IF p_mode = 'PROCESS' THEN
2798
2799 l_msg := FND_MESSAGE.GET;
2800
2801 IF l_msg IS NULL THEN
2802 l_msg := sqlerrm;
2803 END IF;
2804
2805 FND_FILE.PUT_LINE (FND_FILE.LOG, l_msg);
2806 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_FUT_DT_TRANSF_FAIL');
2807 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2808
2809 END IF;
2810
2811 END;
2812
2813 END LOOP;
2814
2815 --- No Data is found
2816 IF NOT l_rec_exists THEN
2817
2818 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_NO_DATA_FOUND');
2819 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
2820
2821 END IF;
2822
2823 EXCEPTION
2824
2825 WHEN OTHERS THEN
2826 retcode:=2;
2827 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2828
2829 END process_fut_dt_trans;
2830
2831 /*----------------------------------------------------------------------------
2832 || Created By : ctyagi
2833 || Created On : 30-AUG-2005
2834 || Purpose : Deleting unconfirm unit as impact of Re-Open
2835 || Admission Appication
2836 ||
2837 || Known limitations, enhancements or remarks :
2838 || Change History :
2839 || Who When What
2840 ||
2841 ------------------------------------------------------------------------------*/
2842
2843 FUNCTION del_sua_for_reopen(
2844 p_person_id IN IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2845 p_course_cd IN IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
2846 p_uoo_id IN IGS_EN_SU_ATTEMPT.uoo_id%TYPE
2847 )
2848 RETURN BOOLEAN
2849 AS
2850 CURSOR c_sua_del (
2851 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2852 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
2853 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
2854 ) IS
2855 SELECT rowid
2856 FROM IGS_EN_SU_ATTEMPT sua
2857 WHERE sua.person_id = cp_person_id
2858 AND sua.course_cd = cp_course_cd
2859 AND sua.uoo_id = cp_uoo_id
2860 FOR UPDATE OF sua.LAST_UPDATE_DATE NOWAIT;
2861
2862 v_sua_del_exists c_sua_del%ROWTYPE;
2863
2864 BEGIN
2865
2866
2867
2868 FOR v_sua_del_exists IN c_sua_del(p_person_id,p_course_cd,p_uoo_id) LOOP
2869
2870
2871
2872 del_suar(p_person_id, p_course_cd, p_uoo_id);
2873
2874 del_as_msht_su_atmpt( p_person_id,p_course_cd,p_uoo_id);
2875
2876 del_as_stmptout( p_person_id,p_course_cd,p_uoo_id);
2877
2878 del_ps_stdnt_unt_trn( p_person_id,p_course_cd,p_uoo_id);
2879
2880 del_as_anon_id_us( p_person_id,p_course_cd,p_uoo_id);
2881
2882 del_as_sua_ses_atts( p_person_id,p_course_cd,p_uoo_id);
2883
2884
2885 IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW( X_ROWID => v_sua_del_exists.rowid );
2886
2887
2888
2889 END LOOP;
2890 RETURN TRUE;
2891 EXCEPTION
2892 WHEN OTHERS THEN
2893 IF c_sua_del%ISOPEN THEN
2894 CLOSE c_sua_del;
2895 END IF;
2896 RETURN FALSE;
2897
2898 END del_sua_for_reopen;
2899
2900
2901
2902 END IGS_EN_FUTURE_DT_TRANS;
[Home] [Help]