1 PACKAGE BODY IGS_ST_GEN_001 AS
2 /* $Header: IGSST01B.pls 120.0 2005/06/01 13:36:13 appldev noship $ */
3 -- smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_caw in the procedure stapl_check_same_award.
4 -- svenkata 25-02-02 Removed the procedure Stap_Del_Ess as part of CCR
5 -- ENCR024 .Bug # 2239050
6 -- smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_count_unit_mode and c_chk_unit_mode to select active (not closed) unit classes.
7
8 Function Stap_Get_Att_Mode(
9 p_submission_yr IN NUMBER ,
10 p_submission_number IN NUMBER ,
11 p_person_id IN NUMBER ,
12 p_course_cd IN VARCHAR2 )
13 RETURN VARCHAR2 AS
14 ---------------------------------------------------------------------------------------------------------
15 --Change History:
16 --Who When What
17 --kkillams 28-04-2003 Modified the c_count_unit_mode and c_chk_unit_mode cursors where clause
18 -- w.r.t. bug number 2829262
19 ---------------------------------------------------------------------------------------------------------
20 e_attend_mode_error EXCEPTION;
21 gv_other_details VARCHAR2(255);
22 gv_exception_msg VARCHAR2(100);
23 BEGIN
24 DECLARE
25 cst_on CONSTANT IGS_AS_UNIT_MODE.s_unit_mode%TYPE := 'ON';
26 cst_off CONSTANT IGS_AS_UNIT_MODE.s_unit_mode%TYPE := 'OFF';
27 cst_composite CONSTANT IGS_AS_UNIT_MODE.s_unit_mode%TYPE := 'COMPOSITE';
28 v_unit_mode_count NUMBER;
29 v_unit_mode IGS_AS_UNIT_MODE.s_unit_mode%TYPE;
30 v_attend_mode IGS_EN_ATD_MODE.attendance_mode%TYPE;
31 CURSOR c_count_unit_mode IS
32 SELECT COUNT(UNIQUE um.s_unit_mode)
33 FROM IGS_ST_GVT_STDNTLOAD gslo,
34 IGS_EN_ST_SNAPSHOT ess,
35 IGS_AS_UNIT_CLASS ucl,
36 IGS_AS_UNIT_MODE um,
37 IGS_ST_GVT_SPSHT_CTL gsc
38 WHERE gslo.submission_yr = p_submission_yr AND
39 gslo.submission_number = p_submission_number AND
40 gslo.person_id = p_person_id AND
41 gslo.course_cd = p_course_cd AND
42 ess.snapshot_dt_time = gsc.ess_snapshot_dt_time AND
43 ess.person_id = gslo.person_id AND
44 ess.course_cd = gslo.course_cd AND
45 ess.unit_cd = gslo.unit_cd AND
46 ess.sua_cal_type = gslo.sua_cal_type AND
47 ess.sua_ci_sequence_number = gslo.sua_ci_sequence_number AND
48 ess.sua_location_cd = gslo.sua_location_cd AND
49 ess.unit_class = gslo.unit_class AND
50 ess.tr_org_unit_cd = gslo.tr_org_unit_cd AND
51 ess.tr_ou_start_dt = gslo.tr_ou_start_dt AND
52 ess.discipline_group_cd = gslo.discipline_group_cd AND
53 ess.govt_discipline_group_cd = gslo.govt_discipline_group_cd AND
54 ucl.unit_class = ess.unit_class AND
55 ucl.closed_ind = 'N' AND
56 um.unit_mode = ucl.unit_mode AND
57 gsc.submission_yr = gslo.submission_yr AND
58 gsc.submission_number = gslo.submission_number;
59 CURSOR c_chk_unit_mode IS
60 SELECT UNIQUE um.s_unit_mode
61 FROM IGS_ST_GVT_STDNTLOAD gslo,
62 IGS_EN_ST_SNAPSHOT ess,
63 IGS_AS_UNIT_CLASS ucl,
64 IGS_AS_UNIT_MODE um,
65 IGS_ST_GVT_SPSHT_CTL gsc
66 WHERE gslo.submission_yr = p_submission_yr AND
67 gslo.submission_number = p_submission_number AND
68 gslo.person_id = p_person_id AND
69 gslo.course_cd = p_course_cd AND
70 ess.snapshot_dt_time = gsc.ess_snapshot_dt_time AND
71 ess.person_id = gslo.person_id AND
72 ess.course_cd = gslo.course_cd AND
73 ess.unit_cd = gslo.unit_cd AND
74 ess.sua_cal_type = gslo.sua_cal_type AND
75 ess.sua_ci_sequence_number = gslo.sua_ci_sequence_number AND
76 ess.sua_location_cd = gslo.sua_location_cd AND
77 ess.unit_class = gslo.unit_class AND
78 ess.tr_org_unit_cd = gslo.tr_org_unit_cd AND
79 ess.tr_ou_start_dt = gslo.tr_ou_start_dt AND
80 ess.discipline_group_cd = gslo.discipline_group_cd AND
81 ess.govt_discipline_group_cd = gslo.govt_discipline_group_cd AND
82 ucl.unit_class = ess.unit_class AND
83 ucl.closed_ind = 'N' AND
84 um.unit_mode = ucl.unit_mode AND
85 gsc.submission_yr = gslo.submission_yr AND
86 gsc.submission_number = gslo.submission_number;
87 CURSOR c_get_attend_mode (
88 cp_govt_attend_mode IGS_EN_ATD_MODE.govt_attendance_mode%TYPE ) IS
89 SELECT attendance_mode
90 FROM IGS_EN_ATD_MODE
91 WHERE govt_attendance_mode = cp_govt_attend_mode
92 ORDER
93 BY attendance_mode;
94 BEGIN
95 -- Determine the number of different attendance modes.
96 OPEN c_count_unit_mode;
97 FETCH c_count_unit_mode INTO v_unit_mode_count;
98 CLOSE c_count_unit_mode;
99 --- Determine the attendance mode for the course.
100 IF v_unit_mode_count = 1 THEN
101 OPEN c_chk_unit_mode;
102 FETCH c_chk_unit_mode INTO v_unit_mode;
103 CLOSE c_chk_unit_mode;
104 IF v_unit_mode = cst_on THEN
105 -- Attendance mode is internal.
106 OPEN c_get_attend_mode( 1 );
107 FETCH c_get_attend_mode INTO v_attend_mode;
108 IF c_get_attend_mode%FOUND THEN
109 CLOSE c_get_attend_mode;
110 RETURN v_attend_mode;
111 ELSE
112 gv_exception_msg :=
113 'Cannot determine the Attendance Mode value (Internal).';
114 CLOSE c_get_attend_mode;
115 RAISE e_attend_mode_error;
116 END IF;
117 ELSIF v_unit_mode = cst_off THEN
118 -- Attendance mode is external.
119 OPEN c_get_attend_mode( 2 );
120 FETCH c_get_attend_mode INTO v_attend_mode;
121 IF c_get_attend_mode%FOUND THEN
122 CLOSE c_get_attend_mode;
123 RETURN v_attend_mode;
124 ELSE
125 gv_exception_msg :=
126 'Cannot determine the Attendance Mode value (External).';
127 CLOSE c_get_attend_mode;
128 RAISE e_attend_mode_error;
129 END IF;
130 ELSIF v_unit_mode = cst_composite THEN
131 -- Attendance mode is multi-modal.
132 OPEN c_get_attend_mode( 3 );
133 FETCH c_get_attend_mode INTO v_attend_mode;
134 IF c_get_attend_mode%FOUND THEN
135 CLOSE c_get_attend_mode;
136 RETURN v_attend_mode;
137 ELSE
138 gv_exception_msg :=
139 'Cannot determine the Attendance Mode value (Multi-modal).';
140 CLOSE c_get_attend_mode;
141 RAISE e_attend_mode_error;
142 END IF;
143 END IF;
144 ELSIF v_unit_mode_count > 1 THEN
145 -- Attendance mode is multi-modal.
146 OPEN c_get_attend_mode( 3 );
147 FETCH c_get_attend_mode INTO v_attend_mode;
148 IF c_get_attend_mode%FOUND THEN
149 CLOSE c_get_attend_mode;
150 RETURN v_attend_mode;
151 ELSE
152 gv_exception_msg :=
153 'Cannot determine the Attendance Mode value (Multi-modal).';
154 CLOSE c_get_attend_mode;
155 RAISE e_attend_mode_error;
156 END IF;
157 ELSE
158 -- no records selected
159 gv_exception_msg :=
160 'Cannot determine the Attendance Mode value for the course.';
161 RAISE e_attend_mode_error;
162 END IF;
163 RETURN NULL;
164 END;
165 EXCEPTION
166 WHEN e_attend_mode_error THEN
167 Null;
168 WHEN OTHERS THEN
169 Null;
170 END stap_get_att_mode;
171
172 Function Stap_Get_Comm_Stdnt(
173 p_person_id IN NUMBER ,
174 p_course_cd IN VARCHAR2 ,
175 p_version_number IN NUMBER ,
176 p_commencement_dt IN OUT NOCOPY DATE ,
177 p_collection_yr IN NUMBER )
178 RETURN VARCHAR2 AS
179 gv_other_detail VARCHAR2(255);
180 BEGIN
181 DECLARE
182 E_NO_SCA_RECORD_FOUND EXCEPTION;
183 E_COMM_DT_NULL EXCEPTION;
184 v_gse_record_found BOOLEAN DEFAULT FALSE;
185 v_prev_sca_rec_found BOOLEAN DEFAULT FALSE;
186 v_exclusion_level VARCHAR2(15);
187 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
188 v_curr_generic_course_ind IGS_PS_VER.generic_course_ind%TYPE;
189 v_curr_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
190 v_curr_responsible_org_unit_cd IGS_PS_VER.responsible_org_unit_cd%TYPE;
191 v_curr_course_level NUMBER;
192 v_prev_generic_course_ind IGS_PS_VER.generic_course_ind%TYPE;
193 v_prev_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE;
194 v_prev_responsible_org_unit_cd IGS_PS_VER.responsible_org_unit_cd%TYPE;
195 v_prev_course_level NUMBER;
196 v_prev_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
197 v_prev_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
198 v_prev_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
199 v_march_string VARCHAR2(10);
200 v_march_dt DATE;
201 v_april_string VARCHAR2(10);
202 v_april_dt DATE;
203 CURSOR c_sca IS
204 SELECT sca.commencement_dt
205 FROM IGS_EN_STDNT_PS_ATT sca
206 WHERE sca.person_id = p_person_id AND
207 sca.course_cd = p_course_cd;
208 CURSOR c_crv_cty(
209 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
210 cp_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
211 SELECT crv.generic_course_ind,
212 cty.govt_course_type,
213 crv.responsible_org_unit_cd,
214 IGS_ST_GEN_002.stap_get_course_lvl(
215 cp_course_cd,
216 cp_version_number,
217 cty.govt_course_type)
218 FROM IGS_PS_VER crv,
219 IGS_PS_TYPE cty
220 WHERE crv.course_cd = cp_course_cd AND
221 crv.version_number = cp_version_number AND
222 cty.course_type = crv.course_type;
223 CURSOR c_prev_sca(
224 cp_april_dt DATE) IS
225 SELECT sca.course_cd,
226 sca.version_number,
227 sca.commencement_dt
228 FROM IGS_EN_STDNT_PS_ATT sca
229 WHERE sca.person_id = p_person_id AND
230 sca.course_cd <> p_course_cd AND
231 sca.commencement_dt < cp_april_dt
232 ORDER BY sca.commencement_dt DESC;
233 FUNCTION stapl_retrieve_curr_prior_gse(
234 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
235 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
236 p_collection_yr NUMBER)
237 RETURN BOOLEAN IS
238 BEGIN
239 DECLARE
240 CURSOR c_gse IS
241 SELECT 'x'
242 FROM IGS_ST_GOVT_STDNT_EN gse
243 WHERE gse.person_id = p_person_id AND
244 gse.course_cd = p_course_cd AND
245 gse.submission_yr = p_collection_yr - 1;
246 v_cge_exists VARCHAR2(1);
247 BEGIN
248 -- This module checks if there is matching record for the current
249 -- student_course_attempt in the prior years' Student Enrolment File.
250 OPEN c_gse;
251 FETCH c_gse INTO v_cge_exists;
252 IF c_gse%FOUND THEN
253 CLOSE c_gse;
254 RETURN TRUE;
255 END IF;
256 CLOSE c_gse;
257 RETURN FALSE;
258 EXCEPTION
259 WHEN OTHERS THEN
260 IF c_gse%ISOPEN THEN
261 CLOSE c_gse;
262 END IF;
263 App_Exception.Raise_Exception;
264 END;
265 EXCEPTION
266 WHEN OTHERS THEN
267 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
268 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_retrieve_curr_prior_gse');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END stapl_retrieve_curr_prior_gse;
272
273 FUNCTION stapl_check_curr_prior_gse(
274 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
275 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
276 p_collection_yr NUMBER)
277 RETURN BOOLEAN
278 AS
279 BEGIN
280 DECLARE
281 v_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE;
282 v_continue BOOLEAN;
283 CURSOR c_api_pit(
284 cp_person_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE) IS
285 SELECT api.api_person_id
286 FROM IGS_PE_ALT_PERS_ID api,
287 IGS_PE_PERSON_ID_TYP pit
288 WHERE api.pe_person_id = cp_person_id AND
289 pit.person_id_type = api.person_id_type AND
290 pit.s_person_id_type = 'OBSOLETE';
291 BEGIN
292 -- This module checks if there is matching record for the current
293 -- student_course_attempt in the prior years' Student Enrolment File.
294 IF NOT stapl_retrieve_curr_prior_gse(
295 p_person_id,
296 p_course_cd,
297 p_collection_yr) THEN
298 v_person_id := p_person_id;
299 FOR v_api_pit_rec IN c_api_pit(
300 v_person_id) LOOP
301 BEGIN
302 v_continue := TRUE;
303 v_person_id := TO_NUMBER(v_api_pit_rec.api_person_id);
304 EXCEPTION
305 WHEN VALUE_ERROR THEN
306 v_continue := FALSE;
307 END;
308 IF v_continue AND
309 stapl_check_curr_prior_gse(
310 v_person_id,
311 p_course_cd,
312 p_collection_yr) THEN
313 RETURN TRUE;
314 END IF;
315 END LOOP;
316 RETURN FALSE;
317 ELSE
318 RETURN TRUE;
319 END IF;
320 EXCEPTION
321 WHEN OTHERS THEN
322 IF c_api_pit%ISOPEN THEN
323 CLOSE c_api_pit;
324 END IF;
325 App_Exception.Raise_Exception;
326 END;
327 EXCEPTION
328 WHEN OTHERS THEN
329 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
330 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_curr_prior_gse');
331 IGS_GE_MSG_STACK.ADD;
332 App_Exception.Raise_Exception;
333 END stapl_check_curr_prior_gse;
334
335 FUNCTION stapl_retrieve_prev_prior_gse(
336 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
337 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
338 p_collection_yr NUMBER)
339 RETURN BOOLEAN AS
340 BEGIN
341 DECLARE
342 CURSOR c_gse IS
343 SELECT 'x'
344 FROM IGS_ST_GOVT_STDNT_EN gse
345 WHERE gse.person_id = p_person_id AND
346 gse.course_cd = p_course_cd AND
347 gse.submission_yr < p_collection_yr;
348 v_cge_exists VARCHAR2(1);
349 BEGIN
350 -- This module checks if there is matching record for the previous
351 -- student_course_attempt in the prior years' Student Enrolment File.
352 OPEN c_gse;
353 FETCH c_gse INTO v_cge_exists;
354 IF c_gse%FOUND THEN
355 CLOSE c_gse;
356 RETURN TRUE;
357 END IF;
358 CLOSE c_gse;
359 RETURN FALSE;
360 EXCEPTION
361 WHEN OTHERS THEN
362 IF c_gse%ISOPEN THEN
363 CLOSE c_gse;
364 END IF;
365 App_Exception.Raise_Exception;
366 END;
367 EXCEPTION
368 WHEN OTHERS THEN
369 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
370 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_retrieve_prev_prior_gse');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END stapl_retrieve_prev_prior_gse;
374
375 FUNCTION stapl_check_prev_prior_gse(
376 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
377 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
378 p_collection_yr NUMBER)
379 RETURN BOOLEAN AS
380 BEGIN
381 DECLARE
382 v_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE;
383 v_continue BOOLEAN;
384 CURSOR c_api_pit(
385 cp_person_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE) IS
386 SELECT api.api_person_id
387 FROM IGS_PE_ALT_PERS_ID api,
388 IGS_PE_PERSON_ID_TYP pit
389 WHERE api.pe_person_id = cp_person_id AND
390 pit.person_id_type = api.person_id_type AND
391 pit.s_person_id_type = 'OBSOLETE';
392 BEGIN
393 -- This module checks if there is matching record for the previous
394 -- IGS_EN_STDNT_PS_ATT in the prior years' Student Enrolment File.
395 IF NOT stapl_retrieve_prev_prior_gse(
396 p_person_id,
397 p_course_cd,
398 p_collection_yr) THEN
399 v_person_id := p_person_id;
400 FOR v_api_pit_rec IN c_api_pit(
401 v_person_id) LOOP
402 BEGIN
403 v_continue := TRUE;
404 v_person_id := TO_NUMBER(v_api_pit_rec.api_person_id);
405 EXCEPTION
406 WHEN VALUE_ERROR THEN
407 v_continue := FALSE;
408 END;
409 IF v_continue AND
410 stapl_check_curr_prior_gse(
411 v_person_id,
412 p_course_cd,
413 p_collection_yr) THEN
414 RETURN TRUE;
415 END IF;
416 END LOOP;
417 RETURN FALSE;
418 ELSE
419 RETURN TRUE;
420 END IF;
421 EXCEPTION
422 WHEN OTHERS THEN
423 IF c_api_pit%ISOPEN THEN
424 CLOSE c_api_pit;
425 END IF;
426 App_Exception.Raise_Exception;
427 END;
428 EXCEPTION
429 WHEN OTHERS THEN
430 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
431 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_prev_prior_gse');
432 IGS_GE_MSG_STACK.ADD;
433 App_Exception.Raise_Exception;
434 END stapl_check_prev_prior_gse;
435
436 FUNCTION stapl_check_prev_crs_enrolled(
437 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
438 p_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE)
439 RETURN BOOLEAN AS
440 BEGIN
441 DECLARE
442 CURSOR c_sua IS
443 SELECT 'x'
444 FROM IGS_EN_SU_ATTEMPT sua
445 WHERE sua.person_id = p_person_id AND
446 sua.course_cd = p_course_cd AND
447 sua.unit_attempt_status IN (
448 'ENROLLED',
449 'COMPLETED');
450 v_sua_exists VARCHAR2(1);
451 BEGIN
452 -- This module checks if previous course attempt was enrolled.
453 OPEN c_sua;
454 FETCH c_sua INTO v_sua_exists;
455 IF c_sua%FOUND THEN
456 CLOSE c_sua;
457 RETURN TRUE;
458 END IF;
459 CLOSE c_sua;
460 RETURN FALSE;
461 EXCEPTION
462 WHEN OTHERS THEN
463 IF c_sua%ISOPEN THEN
464 CLOSE c_sua;
465 END IF;
466 App_Exception.Raise_Exception;
467 END;
468 EXCEPTION
469 WHEN OTHERS THEN
470 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
471 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_prev_crs_enrolled');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END stapl_check_prev_crs_enrolled;
475 FUNCTION stapl_check_same_award(
476 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
477 p_version_nunber IGS_EN_STDNT_PS_ATT.version_number%TYPE,
478 p_prev_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
479 p_prev_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE)
480 RETURN BOOLEAN AS
481 /***************************************************************
482 Created By :
483 Date Created By :
484 Purpose :
485 Known Limitations,Enhancements or Remarks:
486 Change History :
487 Who When What
488 smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_caw to select open program awards only.
489 ***************************************************************/
490 BEGIN
491 DECLARE
492 CURSOR c_caw IS
493 SELECT 'x'
494 FROM IGS_PS_AWARD caw
495 WHERE caw.course_cd = p_course_cd AND
496 caw.version_number = p_version_number AND
497 caw.closed_ind = 'N' AND
498 EXISTS (SELECT 'x'
499 FROM IGS_PS_AWARD caw1
500 WHERE caw1.course_cd = p_prev_course_cd AND
501 caw1.version_number = p_prev_version_number AND
502 caw1.award_cd <> caw.award_cd AND
503 caw1.closed_ind = 'N');
504 v_caw_exists VARCHAR2(1);
505 BEGIN
506 -- This module checks if the award(s) for the current course attempt and the
507 -- previous course attempt match.
508 OPEN c_caw;
509 FETCH c_caw INTO v_caw_exists;
510 IF c_caw%FOUND THEN
511 CLOSE c_caw;
512 RETURN FALSE;
513 END IF;
514 CLOSE c_caw;
515 RETURN TRUE;
516 EXCEPTION
517 WHEN OTHERS THEN
518 IF c_caw%ISOPEN THEN
519 CLOSE c_caw;
520 END IF;
521 App_Exception.Raise_Exception;
522 END;
523 EXCEPTION
524 WHEN OTHERS THEN
525 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
526 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_same_award');
527 IGS_GE_MSG_STACK.ADD;
528 App_Exception.Raise_Exception;
529 END stapl_check_same_award;
530 FUNCTION stapl_check_same_equiv(
531 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
532 p_version_nunber IGS_EN_STDNT_PS_ATT.version_number%TYPE,
533 p_prev_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
534 p_prev_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE)
535 RETURN BOOLEAN AS
536 BEGIN
537 DECLARE
538 v_course_equivalance_cd IGS_PS_GRP_MBR.course_group_cd%TYPE;
539 v_course_group_cd IGS_PS_GRP_MBR.course_group_cd%TYPE;
540 CURSOR c_cgm IS
541 SELECT 'X'
542 FROM IGS_PS_GRP_MBR cgm1
543 WHERE cgm1.course_cd = p_prev_course_cd AND
544 cgm1.version_number = p_prev_version_number AND
545 EXISTS (
546 SELECT 'X'
547 FROM IGS_PS_GRP_MBR cgm,
548 IGS_PS_GRP cgr,
549 IGS_PS_GRP_TYPE cgt
550 WHERE cgm.course_cd = p_course_cd AND
551 cgm.version_number = p_version_number AND
552 cgr.course_group_cd = cgm.course_group_cd AND
553 cgt.course_group_type = cgr.course_group_type AND
554 cgt.s_course_group_type = 'EQUIV' AND
555 cgm.course_group_cd = cgm1.course_group_cd);
556 v_cgm_exists VARCHAR2(1);
557 BEGIN
558 -- This module checks if the current course attempt and previous course
559 -- attempt are members of the same course equivalence group.
560 -- Retrieve all course equivalence groups the current course belongs to.
561 OPEN c_cgm;
562 FETCH c_cgm INTO v_cgm_exists;
563 IF c_cgm%FOUND THEN
564 CLOSE c_cgm;
565 RETURN TRUE;
566 END IF;
567 CLOSE c_cgm;
568 RETURN FALSE;
569 EXCEPTION
570 WHEN OTHERS THEN
571 IF c_cgm%ISOPEN THEN
572 CLOSE c_cgm;
573 END IF;
574 App_Exception.Raise_Exception;
575 END;
576 EXCEPTION
577 WHEN OTHERS THEN
578 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
579 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_same_equiv');
580 IGS_GE_MSG_STACK.ADD;
581 App_Exception.Raise_Exception;
582 END stapl_check_same_equiv;
583 FUNCTION stapl_check_combined(
584 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
585 p_version_nunber IGS_EN_STDNT_PS_ATT.version_number%TYPE)
586 RETURN BOOLEAN AS
587 BEGIN
588 DECLARE
589 v_cgm_cgr_cgt_exists VARCHAR2(1);
590 CURSOR c_cgm_cgr_cgt IS
591 SELECT 'X'
592 FROM IGS_PS_GRP_MBR cgm,
593 IGS_PS_GRP cgr,
594 IGS_PS_GRP_TYPE cgt
595 WHERE cgm.course_cd = p_course_cd AND
596 cgm.version_number = p_version_number AND
597 cgr.course_group_cd = cgm.course_group_cd AND
598 cgt.course_group_type = cgr.course_group_type AND
599 cgt.s_course_group_type = 'COMBINED';
600 BEGIN
601 -- This module checks if the course attempt is member of a combined
602 -- course group.
603 OPEN c_cgm_cgr_cgt;
604 FETCH c_cgm_cgr_cgt INTO v_cgm_cgr_cgt_exists;
605 IF c_cgm_cgr_cgt%FOUND THEN
606 CLOSE c_cgm_cgr_cgt;
607 RETURN TRUE;
608 END IF;
609 CLOSE c_cgm_cgr_cgt;
610 RETURN FALSE;
611 EXCEPTION
612 WHEN OTHERS THEN
613 IF c_cgm_cgr_cgt%ISOPEN THEN
614 CLOSE c_cgm_cgr_cgt;
615 END IF;
616 App_Exception.Raise_Exception;
617 END;
618 EXCEPTION
619 WHEN OTHERS THEN
620 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
621 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_combined');
622 IGS_GE_MSG_STACK.ADD;
623 App_Exception.Raise_Exception;
624 END stapl_check_combined;
625
626 FUNCTION stapl_check_comp_req(
627 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
628 p_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE)
629 RETURN BOOLEAN AS
630 -------------------------------------------------------------------------------------------
631 --Change History:
632 --Who When What
633 --kkillams 28-04-2003 Modified the c_sua cursor w.r.t. bug number 2829262
634 -------------------------------------------------------------------------------------------
635 BEGIN
636 DECLARE
637 v_grading_schema_cd VARCHAR2(10);
638 v_grade VARCHAR2(10);
639 v_gs_version_number NUMBER;
640 CURSOR c_sua IS
641 SELECT sua.course_cd,
642 sua.unit_cd,
643 sua.cal_type,
644 sua.ci_sequence_number,
645 sua.unit_attempt_status,
646 sua.uoo_id
647 FROM IGS_EN_SU_ATTEMPT sua
648 WHERE sua.person_id = p_person_id AND
649 sua.course_cd = p_course_cd AND
650 sua.unit_attempt_status = 'COMPLETED';
651 BEGIN
652 -- This module checks if the student has completed part of the requirements
653 -- of the previous course.
654 FOR v_sua_rec IN c_sua LOOP
655 IF IGS_AS_GEN_003.assp_get_sua_grade(
656 p_person_id,
657 v_sua_rec.course_cd,
658 v_sua_rec.unit_cd,
659 v_sua_rec.cal_type,
660 v_sua_rec.ci_sequence_number,
661 v_sua_rec.unit_attempt_status,
662 'Y', -- (Finalised indicator)
663 v_grading_schema_cd, -- (Output parameter; not used)
664 v_gs_version_number, -- (Output parameter; not used)
665 v_grade,
666 v_sua_rec.uoo_id) -- (Output parameter; not used)
667 = 'PASS' THEN
668 RETURN TRUE;
669 END IF;
670 END LOOP;
671 RETURN FALSE;
672 EXCEPTION
673 WHEN OTHERS THEN
674 IF c_sua%ISOPEN THEN
675 CLOSE c_sua;
676 END IF;
677 App_Exception.Raise_Exception;
678 END;
679 EXCEPTION
680 WHEN OTHERS THEN
681 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
682 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_comp_req');
683 IGS_GE_MSG_STACK.ADD;
684 App_Exception.Raise_Exception;
685 END stapl_check_comp_req;
686
687 FUNCTION stapl_check_course_transfer(
688 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
689 p_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
690 p_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE,
691 p_transfer_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
692 p_trnsfr_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE)
693 RETURN BOOLEAN
694 AS
695 BEGIN
696 DECLARE
697 v_exists VARCHAR2(1);
698 v_cgm_exists VARCHAR2(1);
699 cst_govt_ret CONSTANT VARCHAR2(10) := 'GOVT-RET';
700 CURSOR c_sct IS
701 SELECT 'X'
702 FROM IGS_PS_STDNT_TRN sct
703 WHERE sct.person_id = p_person_id AND
704 sct.course_cd = p_course_cd AND
705 sct.transfer_course_cd = p_transfer_course_cd;
706 CURSOR c_cgm IS
707 SELECT 'x'
708 FROM IGS_PS_GRP_MBR cgm1
709 WHERE cgm1.course_cd = p_course_cd AND
710 cgm1.version_number = p_version_number AND
711 EXISTS (
712 SELECT 'x'
713 FROM IGS_PS_GRP_MBR cgm2,
714 IGS_PS_GRP cgp,
715 IGS_PS_GRP_TYPE cgt
716 WHERE cgm2.course_cd = p_transfer_course_cd AND
717 cgm2.version_number = p_trnsfr_version_number AND
718 cgm2.course_group_cd = cgm1.course_group_cd AND
719 cgm2.course_group_cd = cgp.course_group_cd AND
720 cgp.course_group_type = cgt.course_group_type AND
721 cgt.s_course_group_type = cst_govt_ret);
722 BEGIN
723 -- This function checks if the student has transferred from a previous course
724 -- attempt to the current course attempt.
725 /**************************************
726 This check has been removed as it is thought
727 the IGS_PS_STDNT_TRN is not needed for the
728 student to still be considered continuing.
729 OPEN c_sct;
730 FETCH c_sct INTO v_exists;
731 IF c_sct%NOTFOUND THEN
732 CLOSE c_sct;
733 RETURN FALSE;
734 END IF;
735 CLOSE c_sct;
736 ***************************************/
737 OPEN c_cgm;
738 FETCH c_cgm INTO v_cgm_exists;
739 IF c_cgm%NOTFOUND THEN
740 CLOSE c_cgm;
741 RETURN FALSE;
742 END IF;
743 CLOSE c_cgm;
744 RETURN TRUE;
745 EXCEPTION
746 WHEN OTHERS THEN
747 IF c_sct%ISOPEN THEN
748 CLOSE c_sct;
749 END IF;
750 IF c_cgm%ISOPEN THEN
751 CLOSE c_cgm;
752 END IF;
753 App_Exception.Raise_Exception;
754 END;
755 EXCEPTION
756 WHEN OTHERS THEN
757 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
758 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_check_course_transfer');
759 IGS_GE_MSG_STACK.ADD;
760 App_Exception.Raise_Exception;
761 END stapl_check_course_transfer;
762 FUNCTION stapl_get_comm_dt(
763 p_prev_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
764 p_prev_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE,
765 p_prev_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE)
766 RETURN DATE
767 AS
768 BEGIN
769 DECLARE
770 v_comm_dt IGS_ST_GOVT_STDNT_EN.commencement_dt%TYPE;
771 CURSOR c_gse IS
772 SELECT gse.commencement_dt
773 FROM IGS_ST_GOVT_STDNT_EN gse
774 WHERE gse.submission_yr < p_collection_yr AND
775 gse.submission_number = 1 AND -- enrolment file is only ever submission 1
776 gse.person_id = p_person_id AND
777 gse.course_cd = p_prev_course_cd AND
778 gse.version_number = p_prev_version_number
779 ORDER BY gse.submission_yr DESC;
780 BEGIN
781 OPEN c_gse;
782 FETCH c_gse INTO v_comm_dt;
783 IF c_gse%NOTFOUND THEN
784 v_comm_dt := p_prev_commencement_dt;
785 END IF;
786 CLOSE c_gse;
787 RETURN v_comm_dt;
788 EXCEPTION
789 WHEN OTHERS THEN
790 IF c_gse%ISOPEN THEN
791 CLOSE c_gse;
792 END IF;
793 App_Exception.Raise_Exception;
794 END;
795 EXCEPTION
796 WHEN OTHERS THEN
797 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
798 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stapl_get_comm_dt');
799 IGS_GE_MSG_STACK.ADD;
800 App_Exception.Raise_Exception;
801 END stapl_get_comm_dt;
802 BEGIN -- main
803 -- Routine to determine the commencing student indicator for a student's
804 -- course attempt. The logic has been determined by the DEETYA Element
805 -- Definition of a commencing student (DEETYA element 922) and the Glossary
806 -- Definition. The logic will need to be enhanced when the course Transfer
807 -- process is developed in Enrolments Phase 1 / Priority 2.
808 -- Determine the student course attempt commencement date.
809 IF (p_commencement_dt IS NULL) THEN
810 OPEN c_sca;
811 FETCH c_sca INTO v_commencement_dt;
812 IF c_sca%NOTFOUND THEN
813 CLOSE c_sca;
814 RAISE E_NO_SCA_RECORD_FOUND;
815 END IF;
816 CLOSE c_sca;
817 IF (v_commencement_dt IS NULL) THEN
818 RAISE E_COMM_DT_NULL;
819 END IF;
820 ELSE
821 v_commencement_dt := p_commencement_dt;
822 END IF;
823 -- check if there is a matching record for the current sutdent course attempt
824 -- in the prior years Student Enrolment File
825 IF stapl_check_curr_prior_gse(
826 p_person_id,
827 p_course_cd,
828 p_collection_yr) THEN
829 v_gse_record_found := TRUE;
830 ELSE
831 v_gse_record_found := FALSE;
832 END IF;
833 v_march_string := TO_CHAR(p_collection_yr - 1)||'03/31';
834 v_march_dt := IGS_GE_DATE.igsdate(v_march_string);
835 v_april_string := TO_CHAR(p_collection_yr - 1)||'04/01';
836 v_april_dt := IGS_GE_DATE.igsdate(v_april_string);
837 -- Check for a commencing student
838 IF(v_commencement_dt > v_march_dt AND
839 v_gse_record_found = FALSE) THEN
840 -- Retrieve course version details for the current course attempt.
841 OPEN c_crv_cty(
842 p_course_cd,
843 p_version_number);
844 FETCH c_crv_cty INTO v_curr_generic_course_ind,
845 v_curr_govt_course_type,
846 v_curr_responsible_org_unit_cd,
847 v_curr_course_level;
848 CLOSE c_crv_cty;
849 -- Retrieve previous course attemptsfor the student.
850 FOR v_prev_sca_rec IN c_prev_sca(
851 v_april_dt) LOOP
852 v_prev_sca_rec_found := TRUE;
853 v_prev_course_cd := v_prev_sca_rec.course_cd;
854 v_prev_version_number := v_prev_sca_rec.version_number;
855 v_prev_commencement_dt := v_prev_sca_rec.commencement_dt;
856 IF((IGS_ST_GEN_003.stap_get_rptbl_govt(
857 p_person_id,
858 v_prev_course_cd,
859 v_prev_version_number,
860 NULL, -- Input Parameter: Unit Code
861 NULL, -- Input Parameter: Unit Version Number
862 NULL, -- Input Parameter: Teaching Calendar Type
863 NULL, -- Input Parameter: Teaching Calendar Sequence Number
864 NULL, -- Input Parameter: Teaching Responsibility - OU code
865 NULL, -- Input Parameter: Teaching Responsibility - OU start date
866 NULL, -- Input Parameter: EFTSU
867 NULL, -- Input Parameter: Effective Date
868 v_exclusion_level,
869 NULL) = 'N') AND
870 (v_exclusion_level IN ('COURSE','PERSON-COURSE'))) THEN
871 NULL; -- Don't do anything
872 ELSE
873 -- Retrieve course version details for the previous course attempt.
874 OPEN c_crv_cty(
875 v_prev_course_cd,
876 v_prev_version_number);
877 FETCH c_crv_cty INTO v_prev_generic_course_ind,
878 v_prev_govt_course_type,
879 v_prev_responsible_org_unit_cd,
880 v_prev_course_level;
881 CLOSE c_crv_cty;
882 -- Process Exceptions.
883 -- Check for a non-commencing student.
884 -- students who are starting a specialised program of studies after
885 -- completing, at the institution or an antecedent institution, a common
886 -- initial year or years of a general program.
887 IF(v_curr_generic_course_ind = 'N' AND
888 v_prev_generic_course_ind = 'Y') THEN
889 -- Check if the previous student course attempt has been reported
890 -- before
891 IF stapl_check_prev_prior_gse(
892 p_person_id,
893 v_prev_course_cd,
894 p_collection_yr) THEN
895 v_gse_record_found := TRUE;
896 ELSE
897 v_gse_record_found := FALSE;
898 END IF;
899 IF (v_prev_commencement_dt < v_april_dt OR
900 v_gse_record_found = TRUE) THEN
901 -- Check if the student has transferred from the previous course
902 -- attempt to the current course attempt.
903 IF stapl_check_course_transfer(
904 p_person_id,
905 p_course_cd,
906 p_version_number,
907 v_prev_course_cd,
908 v_prev_version_number) THEN
909 -- derive commencement date from previous course
910 p_commencement_dt := stapl_get_comm_dt(
911 v_prev_course_cd,
912 v_prev_version_number,
913 v_prev_commencement_dt);
914 RETURN 'N';
915 END IF;
916 END IF;
917 END IF;
918 -- students who, having completed an initial year of study at the
919 -- institution or an antecedent institution then exercise a standard
920 -- option of continuing their studies but at a lower level (i.e. their
921 -- studies would then lead to an award at a level lower than that which
922 -- pertains to the program of studies undertaken in the first year).
923 -- Check if the previous course attempt was enrolled.
924 IF stapl_check_prev_crs_enrolled(
925 p_person_id,
926 v_prev_course_cd) THEN
927 IF v_curr_course_level < v_prev_course_level THEN
928 -- Check if the student has transferred from the previous course
929 -- attempt to the current course attempt.
930 IF stapl_check_course_transfer(
931 p_person_id,
932 p_course_cd,
933 p_version_number,
934 v_prev_course_cd,
935 v_prev_version_number) THEN
936 -- Derive commencement date from previous course
937 p_commencement_dt := stapl_get_comm_dt(
938 v_prev_course_cd,
939 v_prev_version_number,
940 v_prev_commencement_dt);
941 RETURN 'N';
942 END IF;
943 END IF;
944 END IF;
945 -- students who are admitted to or transfer to a bachelor's honours course
946 -- having previously been enrolled, at the institution or an antecedent
947 -- institution, in the related bachelor's pass course.
948 IF (v_curr_govt_course_type = 9 AND
949 v_prev_govt_course_type = 10) THEN
950 -- Check if the previous course attempt was enrolled.
951 IF stapl_check_prev_crs_enrolled(
952 p_person_id,
953 v_prev_course_cd) THEN
954 -- Check if the previous course is an articulate course for the
955 -- current course.
956 IF IGS_RU_VAL_CRS_RULE.rulp_val_crs_artcltn(
957 p_course_cd,
958 p_version_number,
959 v_prev_course_cd,
960 v_prev_version_number) THEN
961 -- Derive commencement date from previous course
962 p_commencement_dt := stapl_get_comm_dt(
963 v_prev_course_cd,
964 v_prev_version_number,
965 v_prev_commencement_dt);
966 RETURN 'N';
967 END IF;
968 END IF;
969 END IF;
970 -- students who are admitted to or transfer to a master's honours course
971 -- having previously been enrolled, at the institution or an antecedent
972 -- institution , in the related master's pass course.
973 IF(v_curr_govt_course_type = 4 AND
974 v_prev_govt_course_type = 4) THEN
975 -- Check if the previous course attempt was enrolled.
976 IF stapl_check_prev_crs_enrolled(
977 p_person_id,
978 v_prev_course_cd) THEN
979 -- Check if the previous course is an articulate course for the
980 -- current course.
981 IF IGS_RU_VAL_CRS_RULE.rulp_val_crs_artcltn(
982 p_course_cd,
983 p_version_number,
984 v_prev_course_cd,
985 v_prev_version_number) THEN
986 -- Derive commencement date from previous course
987 p_commencement_dt := stapl_get_comm_dt(
988 v_prev_course_cd,
989 v_prev_version_number,
990 v_prev_commencement_dt);
991 RETURN 'N';
992 END IF;
993 END IF;
994 END IF;
995 -- students who transfer within the institution or an antecedent institution
996 -- from a course in one academic organisational unit to a course in another
997 -- academic organisational unit, where the courses lead to the same award.
998 IF (v_curr_responsible_org_unit_cd <> v_prev_responsible_org_unit_cd) THEN
999 IF stapl_check_same_award(
1000 p_course_cd,
1001 p_version_number,
1002 v_prev_course_cd,
1003 v_prev_version_number) THEN
1004 -- Check if the student has transferred from the previous course
1005 -- attempt to the current course attempt.
1006 IF stapl_check_course_transfer(
1007 p_person_id,
1008 p_course_cd,
1009 p_version_number,
1010 v_prev_course_cd,
1011 v_prev_version_number) THEN
1012 -- Derive commencement date from previous course
1013 p_commencement_dt := stapl_get_comm_dt(
1014 v_prev_course_cd,
1015 v_prev_version_number,
1016 v_prev_commencement_dt);
1017 RETURN 'N';
1018 END IF;
1019 END IF;
1020 END IF;
1021 -- students who are enrolled in a course at the institution or an antecedent
1022 -- institution which is upgraded in level or renamed.
1023 -- Check if the current course attempt and previous course attempt are
1024 -- members of the same course equivalence group.
1025 IF stapl_check_same_equiv(
1026 p_course_cd,
1027 p_version_number,
1028 v_prev_course_cd,
1029 v_prev_version_number) THEN
1030 -- Derive commencement date from previous course
1031 p_commencement_dt := stapl_get_comm_dt(
1032 v_prev_course_cd,
1033 v_prev_version_number,
1034 v_prev_commencement_dt);
1035 RETURN 'N';
1036 END IF;
1037 -- students who have completed part of the requirements of a combined course
1038 -- at the
1039 -- institution and then change their enrolment to one of the components of
1040 -- that
1041 -- combined course.
1042 -- Check if the previous course attempt is a member of a combined course
1043 -- group
1044 -- and the current course attempt is NOT a member of a combined course
1045 -- group.
1046 IF stapl_check_combined(
1047 v_prev_course_cd,
1048 v_prev_version_number) AND NOT
1049 stapl_check_combined(
1050 p_course_cd,
1051 p_version_number) THEN
1052 -- Check if the student has completed part of the requirements of the
1053 -- previous course.
1054 IF stapl_check_comp_req(
1055 p_person_id,
1056 v_prev_course_cd) THEN
1057 -- Check if the student has transferred from the previous course
1058 -- attempt to the current course attempt.
1059 IF stapl_check_course_transfer(
1060 p_person_id,
1061 p_course_cd,
1062 p_version_number,
1063 v_prev_course_cd,
1064 v_prev_version_number) THEN
1065 -- Derive commencement date from previous course
1066 p_commencement_dt := stapl_get_comm_dt(
1067 v_prev_course_cd,
1068 v_prev_version_number,
1069 v_prev_commencement_dt);
1070 RETURN 'N';
1071 END IF;
1072 END IF;
1073 END IF;
1074 -- students who have completed part of the requirements of a unitary course
1075 -- at the
1076 -- institution and then change their enrolment to a related combined course
1077 -- which leads
1078 -- to an award or awards that subsume the award applicable to the unitary
1079 -- course.
1080 -- Check if the previous course attempt is NOT a member of a combined course
1081 -- group
1082 -- and the current course attempt is a member of a combined course group.
1083 IF NOT stapl_check_combined(
1084 v_prev_course_cd,
1085 v_prev_version_number) AND
1086 stapl_check_combined(
1087 p_course_cd,
1088 p_version_number) THEN
1089 -- Check if the student has completed part of the requirements of the
1090 -- previous course.
1091 IF stapl_check_comp_req(
1092 p_person_id,
1093 v_prev_course_cd) THEN
1094 -- Check if the student has transferred from the previous course
1095 -- attempt to the current course attempt.
1096 IF stapl_check_course_transfer(
1097 p_person_id,
1098 p_course_cd,
1099 p_version_number,
1100 v_prev_course_cd,
1101 v_prev_version_number) THEN
1102 -- Derive commencement date from previous course
1103 p_commencement_dt := stapl_get_comm_dt(
1104 v_prev_course_cd,
1105 v_prev_version_number,
1106 v_prev_commencement_dt);
1107 RETURN 'N';
1108 END IF;
1109 END IF;
1110 END IF;
1111 END IF;
1112 END LOOP;
1113 -- no previous course attempts exist for the student or
1114 -- no IGS_GE_EXCEPTIONS - student is commencing
1115 p_commencement_dt := v_commencement_dt;
1116 RETURN 'Y';
1117 END IF;
1118 -- Check for a non-commencing student.
1119 IF(v_commencement_dt < v_april_dt OR
1120 v_gse_record_found = TRUE) THEN
1121 p_commencement_dt := v_commencement_dt;
1122 RETURN 'N';
1123 END IF;
1124 EXCEPTION
1125 WHEN E_NO_SCA_RECORD_FOUND THEN
1126 FND_MESSAGE.SET_NAME('IGS','IGS_ST_COMM_DT_NOT_DETER');
1127 IGS_GE_MSG_STACK.ADD;
1128 APP_EXCEPTION.RAISE_EXCEPTION;
1129 WHEN E_COMM_DT_NULL THEN
1130 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1131 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.Stap_Get_Comm_Stdnt');
1132 IGS_GE_MSG_STACK.ADD;
1133 APP_EXCEPTION.RAISE_EXCEPTION;
1134 WHEN OTHERS THEN
1135 IF c_sca%ISOPEN THEN
1136 CLOSE c_sca;
1137 END IF;
1138 IF c_crv_cty%ISOPEN THEN
1139 CLOSE c_crv_cty;
1140 END IF;
1141 IF c_prev_sca%ISOPEN THEN
1142 CLOSE c_prev_sca;
1143 END IF;
1144 APP_EXCEPTION.RAISE_EXCEPTION;
1145 END;
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1149 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_GEN_001.stap_get_comm_stdnt');
1150 IGS_GE_MSG_STACK.ADD;
1151 App_Exception.Raise_Exception;
1152 END stap_get_comm_stdnt;
1153
1154 END IGS_ST_GEN_001;
[Home] [Help]