1 PACKAGE BODY igs_en_gen_005 AS
2 /* $Header: IGSEN05B.pls 120.1 2006/01/18 22:52:59 ctyagi noship $ */
3
4 /* Change History
5 who when what
6 smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_uoo2 to select active (not closed) unit classes.
7 */
8
9 FUNCTION enrp_get_fee_student(
10 p_person_id IN NUMBER ,
11 p_course_cd IN VARCHAR2 ,
12 p_hecs_payment_option IN VARCHAR2 )
13 RETURN NUMBER AS
14 BEGIN
15 DECLARE
16 cst_fee_paying_not CONSTANT NUMBER := 1;
17 cst_fee_paying_os CONSTANT NUMBER := 2;
18 cst_fee_paying_pg_course CONSTANT NUMBER := 3;
19 cst_fee_paying_non_os_ug CONSTANT NUMBER := 4;
20 cst_hecs_fee_paying_pg CONSTANT VARCHAR2(2) := '20';
21 cst_hecs_fee_paying_os CONSTANT VARCHAR2(2) := '22';
22 cst_hecs_os_student_charge CONSTANT VARCHAR2(2) := '23';
23 cst_hecs_fee_paying_os_spnsr CONSTANT VARCHAR2(2) := '24';
24 v_other_detail VARCHAR2(255);
25 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
26 v_govt_course_type IGS_PS_GOVT_TYPE.govt_course_type%TYPE;
27 v_output NUMBER;
28
29 CURSOR c_ghpo IS
30 SELECT govt_hecs_payment_option
31 FROM IGS_FI_HECS_PAY_OPTN
32 WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = p_hecs_payment_option;
33
34 CURSOR c_get_govt_crs_type IS
35 SELECT govt_course_type
36 FROM IGS_EN_STDNT_PS_ATT,
37 IGS_PS_VER,
38 IGS_PS_TYPE
39 WHERE IGS_EN_STDNT_PS_ATT.person_id = p_person_id AND
40 IGS_EN_STDNT_PS_ATT.course_cd = p_course_cd AND
41 IGS_PS_VER.course_cd = IGS_EN_STDNT_PS_ATT.course_cd AND
42 IGS_PS_VER.version_number = IGS_EN_STDNT_PS_ATT.version_number AND
43 IGS_PS_TYPE.course_type = IGS_PS_VER.course_type;
44 BEGIN
45 -- This module returns the govt. value (either 1/2/3/4) for FEE-STUDENT.
46 -- DEETYA element 349.
47 -- retrieving the govt. value for p_hecs_payment_option
48 OPEN c_ghpo;
49 FETCH c_ghpo INTO v_govt_hecs_payment_option;
50 CLOSE c_ghpo;
51 -- determine value for FEE-STUDENT
52 -- returning 2 (cst_fee_paying_os)
53 IF (NVL(v_govt_hecs_payment_option, 'NULL') = cst_hecs_fee_paying_os) OR
54 (NVL(v_govt_hecs_payment_option, 'NULL') = cst_hecs_os_student_charge) OR
55 (NVL(v_govt_hecs_payment_option, 'NULL') =
56 cst_hecs_fee_paying_os_spnsr) THEN
57 v_output := cst_fee_paying_os;
58 return v_output;
59 ELSIF (NVL(v_govt_hecs_payment_option, 'NULL') = cst_hecs_fee_paying_pg) THEN
60 OPEN c_get_govt_crs_type;
61 FETCH c_get_govt_crs_type INTO v_govt_course_type;
62 CLOSE c_get_govt_crs_type;
63 -- returning 4 (cst_fee_paying_non_os_ug)
64 IF (v_govt_course_type IS NOT NULL AND
65 v_govt_course_type BETWEEN 8 AND 10 OR
66 v_govt_course_type BETWEEN 20 AND 22) THEN
67 v_output := cst_fee_paying_non_os_ug;
68 return v_output;
69 ELSE
70 -- returning 3 (cst_fee_paying_pg_course)
71 v_output := cst_fee_paying_pg_course;
72 return v_output;
73 END IF;
74 ELSE -- returning 1 (cst_fee_paying_not)
75 v_output := cst_fee_paying_not;
76 return v_output;
77 END IF;
78 EXCEPTION
79 WHEN OTHERS THEN
80 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
81 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_005.enrp_get_fee_student');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception;
84 END;
85 END enrp_get_fee_student;
86
87
88 FUNCTION Enrp_Get_Pos_Elgbl(
89 p_acad_cal_type IN VARCHAR2 ,
90 p_acad_sequence_number IN NUMBER ,
91 p_person_id IN NUMBER ,
92 p_course_cd IN VARCHAR2 ,
93 p_version_number IN NUMBER ,
94 p_pos_sequence_number IN NUMBER ,
95 p_always_pre_enrol_ind IN VARCHAR2 ,
96 p_acad_period_num IN NUMBER ,
97 p_log_creation_dt IN DATE ,
98 p_warn_level OUT NOCOPY VARCHAR2 ,
99 p_message_name OUT NOCOPY VARCHAR2)
100 RETURN VARCHAR2 AS
101 -------------------------------------------------------------------------------------------
102 --Change History:
103 --Who When What
104 --ayedubat 16-MAY-2002 Changed the cursor,c_sua_cir to remove validation comparing the future academic periods
105 -- as part of the bug;2377045
106 --kkillams 24-04-2003 Modified the c_sua cursor w.r.t. bug number 2829262
107 -------------------------------------------------------------------------------------------
108 /* HISTORY
109 WHO WHEN WHAT
110
111 */
112 BEGIN -- enrp_get_pos_elgbl
113 -- Check whether a student IGS_PS_COURSE attempt is eligible to be pre-enrolled
114 -- using the pattern of study structure in a nominated academic period
115 -- A student is deemed to be ineligible to be pre-enrolled via a pattern
116 -- of study if:-
117 -- * They already have IGS_PS_UNIT attempts in the target academic period(s)
118 -- * They have been granted/approved IGS_PS_UNIT level advanced standing
119 -- * They haven't got any IGS_PS_UNIT requirements (applied via encumbrances)
120 -- which wouldn't be satisfied by the units in the pattern of study.
121 -- * They have not passed (or are currently enrolled in) units which
122 -- should have already been completed in accordance with the pattern
123 -- of study. These units must be taken in the prescribed teaching
124 -- calendars.
125 DECLARE
126 cst_true CONSTANT VARCHAR2(5) := 'TRUE';
127 cst_false CONSTANT VARCHAR2(5) := 'FALSE';
128 cst_minor CONSTANT VARCHAR2(5) := 'MINOR';
129 cst_pass CONSTANT VARCHAR2(5) := 'PASS';
130 cst_incomp CONSTANT VARCHAR2(6) := 'INCOMP';
131 cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
132 cst_granted CONSTANT VARCHAR2(10) := 'GRANTED';
133 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
134 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
135 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
136 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
137 v_dummy VARCHAR2(1);
138 v_alt_code IGS_CA_INST.alternate_code%TYPE;
139 v_acad_cal_type IGS_CA_INST.cal_type%TYPE;
140 v_acad_sequence_number IGS_CA_INST.sequence_number%TYPE;
141 v_acad_start_dt IGS_CA_INST.start_dt%TYPE;
142 v_acad_end_dt IGS_CA_INST.end_dt%TYPE;
143 v_return_flag BOOLEAN;
144 v_message_name VARCHAR2(30);
145
146 CURSOR c_sua_cir IS
147 SELECT sua.cal_type,
148 sua.ci_sequence_number,
149 ci.cal_type parent_cal_type,
150 ci.sequence_number parent_sequence_number
151 FROM IGS_CA_INST aci,
152 IGS_EN_SU_ATTEMPT sua,
153 IGS_CA_INST_REL cir,
154 IGS_CA_INST ci
155 WHERE
156 aci.cal_type = p_acad_cal_type AND
157 aci.sequence_number = p_acad_sequence_number AND
158 sua.person_id = p_person_id AND
159 sua.course_cd = p_course_cd AND
160 cir.sub_cal_type = sua.cal_type AND
161 cir.sub_ci_sequence_number = sua.ci_sequence_number AND
162 ci.cal_type = cir.sup_cal_type AND
163 ci.sequence_number = cir.sup_ci_sequence_number AND
164 (cir.sup_cal_type = p_acad_cal_type AND
165 cir.sup_ci_sequence_number = p_acad_sequence_number);
166
167 CURSOR c_asul IS
168 SELECT 'X'
169 FROM IGS_AV_STND_UNIT_LVL asul
170 WHERE asul.person_id = p_person_id AND
171 asul.as_course_cd = p_course_cd AND
172 asul.s_adv_stnd_granting_status IN (cst_granted,
173 cst_approved);
174 CURSOR c_pur_pee IS
175 SELECT 'X'
176 FROM IGS_PE_UNT_REQUIRMNT pur,
177 IGS_PE_PERSENC_EFFCT pee
178 WHERE pur.person_id = p_person_id AND
179 pur.pur_start_dt <= SYSDATE AND
180 NVL(pur.expiry_dt, igs_ge_date.igsdate('9999/01/01')) > SYSDATE AND
181 pee.person_id = pur.person_id AND
182 pee.encumbrance_type = pur.encumbrance_type AND
183 pee.pen_start_dt = pur.pen_start_dt AND
184 pee.s_encmb_effect_type = pur.s_encmb_effect_type AND
185 pee.pee_start_dt = pur.pee_start_dt AND
186 pee.sequence_number = pur.pee_sequence_number AND
187 (pee.course_cd IS NULL OR
188 pee.course_cd = p_course_cd) AND
189 NOT EXISTS (
190 SELECT 'X'
191 FROM IGS_PS_PAT_STUDY_UNT posu,
192 IGS_PS_PAT_STUDY_PRD posp
193 WHERE posu.course_cd = p_course_cd AND
194 posu.version_number = p_version_number AND
195 posu.cal_type = p_acad_cal_type AND
196 posu.pos_sequence_number = p_pos_sequence_number AND
197 NVL(posu.unit_cd, NULL) = pur.unit_cd AND
198 posp.course_cd = posu.course_cd AND
199 posp.version_number = posu.version_number AND
200 posp.cal_type = posu.cal_type AND
201 posp.pos_sequence_number = posu.pos_sequence_number AND
202 posp.sequence_number = posu.posp_sequence_number AND
203 posp.acad_period_num = p_acad_period_num);
204 CURSOR c_posu_posp IS
205 SELECT posu.unit_cd,
206 posp.teach_cal_type
207 FROM IGS_PS_PAT_STUDY_UNT posu,
208 IGS_PS_PAT_STUDY_PRD posp
209 WHERE posu.course_cd = p_course_cd AND
210 posu.version_number = p_version_number AND
211 posu.cal_type = p_acad_cal_type AND
212 posu.pos_sequence_number = p_pos_sequence_number AND
213 posu.unit_cd IS NOT NULL AND
214 posp.course_cd = posu.course_cd AND
215 posp.version_number = posu.version_number AND
216 posp.cal_type = posu.cal_type AND
217 posp.pos_sequence_number = posu.pos_sequence_number AND
218 posp.sequence_number = posu.posp_sequence_number AND
219 posp.acad_period_num < p_acad_period_num;
220 CURSOR c_sua (cp_posu_unit_cd IGS_PS_PAT_STUDY_UNT.unit_cd%TYPE,
221 cp_posp_teach_cal_type IGS_PS_PAT_STUDY_PRD.teach_cal_type%TYPE) IS
222 SELECT 'X'
223 FROM IGS_EN_SU_ATTEMPT sua
224 WHERE sua.person_id = p_person_id AND
225 sua.course_cd = p_course_cd AND
226 sua.unit_cd = cp_posu_unit_cd AND
227 sua.cal_type = cp_posp_teach_cal_type AND
228 (sua.unit_attempt_status = cst_enrolled OR
229 (sua.unit_attempt_status = cst_completed AND
230 EXISTS (
231 SELECT 'X'
232 FROM IGS_AS_SUAO_V suaov,
233 IGS_AS_GRD_SCH_GRADE gsg
234 WHERE suaov.person_id = sua.person_id AND
235 suaov.course_cd = sua.course_cd AND
236 suaov.uoo_id = sua.uoo_id AND
237 gsg.grading_schema_cd = suaov.grading_schema_cd AND
238 gsg.version_number = suaov.version_number AND
239 gsg.grade = suaov.grade AND
240 gsg.s_result_type IN (cst_pass,cst_incomp))));
241 CURSOR c_posp IS
242 SELECT teach_cal_type
243 FROM IGS_PS_PAT_STUDY_PRD posp
244 WHERE posp.course_cd = p_course_cd AND
245 posp.version_number = p_version_number AND
246 posp.cal_type = p_acad_cal_type AND
247 posp.pos_sequence_number = p_pos_sequence_number AND
248 posp.acad_period_num = p_acad_period_num;
249 CURSOR c_cir_tci (cp_teach_cal_type IGS_PS_PAT_STUDY_PRD.teach_cal_type%TYPE)
250 IS
251 SELECT tci.cal_type,
252 tci.sequence_number
253 FROM IGS_CA_INST_REL cir,
254 IGS_CA_INST tci,
255 IGS_CA_TYPE cat,
256 IGS_CA_STAT cs
257 WHERE cir.sup_cal_type = p_acad_cal_type AND
258 cir.sup_ci_sequence_number = p_acad_sequence_number AND
259 cir.sub_cal_type = cp_teach_cal_type AND
260 tci.cal_type = cir.sub_cal_type AND
261 tci.sequence_number = cir.sub_ci_sequence_number AND
262 cat.cal_type = tci.cal_type AND
263 cat.s_cal_cat = 'TEACHING' AND
264 cs.cal_status = tci.cal_status AND
265 cs.s_cal_status = cst_active
266 ORDER BY tci.start_dt DESC;
267 v_teach_cal_type IGS_CA_INST.cal_type%TYPE;
268 v_teach_sequence_number IGS_CA_INST.sequence_number%TYPE;
269 BEGIN
270 p_message_name := NULL;
271 v_return_flag := FALSE;
272 -- Check whether student has already been pre_enrolled into IGS_PS_UNIT attempts
273 -- within the academic year.
274 FOR v_sua_rec IN c_sua_cir LOOP
275 -- Check that the IGS_PS_UNIT attempt was actually started within the
276 -- academic year
277 v_alt_code := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD(v_sua_rec.cal_type,
278 v_sua_rec.ci_sequence_number,
279 v_acad_cal_type,
280 v_acad_sequence_number,
281 v_acad_start_dt,
282 v_acad_end_dt,
283 v_message_name);
284 IF v_acad_cal_type IS NOT NULL AND
285 v_acad_cal_type = v_sua_rec.parent_cal_type AND
286 v_acad_sequence_number = v_sua_rec.parent_sequence_number THEN
287 IF p_log_creation_dt IS NOT NULL THEN
288 -- If all warnings are logged then write the exception
289 IGS_GE_GEN_003.genp_ins_log_entry(cst_pre_enrol,
290 p_log_creation_dt,
291 cst_minor || ','
292 || p_person_id ||','
293 || p_course_cd,
294 'IGS_EN_STUD_INELG_PREENR',
295 NULL);
296 END IF;
297 v_return_flag := TRUE;
298 EXIT;
299 END IF;
300 END LOOP;
301 IF v_return_flag = TRUE THEN
302 p_warn_level := cst_minor;
303 p_message_name := 'IGS_EN_STUD_INELG_PREENR';
304 RETURN cst_false;
305 END IF;
306 -- Check for 'IGS_PS_UNIT level' advanced standing which is approved or granted.
307 -- Existence of this level of advanced standing will prevent the
308 -- pre-enrollment of the pattern of study occurring.
309 OPEN c_asul;
310 FETCH c_asul INTO v_dummy;
311 IF (c_asul%FOUND) THEN
312 IF p_log_creation_dt IS NOT NULL THEN
313 CLOSE c_asul;
314 -- If all warnings are logged then write the exception
315 IGS_GE_GEN_003.genp_ins_log_entry(cst_pre_enrol,
316 p_log_creation_dt,
317 cst_minor || ','
318 || p_person_id ||','
319 || p_course_cd,
320 'IGS_EN_STUD_INELG_UNIT_LVL',
321 NULL);
322 END IF;
323 p_warn_level := cst_minor;
324 p_message_name := 'IGS_EN_STUD_INELG_UNIT_LVL';
325 RETURN cst_false;
326 END IF;
327 CLOSE c_asul;
328 -- Check whether student as a period (s) of intermission overlapping
329 -- the target year(s) which would prevent the enrolment of units in
330 -- the POS periods.
331 FOR v_posp_rec IN c_posp
332 LOOP
333 OPEN c_cir_tci (v_posp_rec.teach_cal_type);
334 FETCH c_cir_tci INTO v_teach_cal_type, v_teach_sequence_number;
335 IF c_cir_tci%FOUND THEN
336 CLOSE c_cir_tci;
337 -- If the student has an intermission overlapping the period
338 -- then they ineligible for POS pre-enrolment.
339 IF IGS_EN_VAL_SUA.enrp_val_sua_intrmt(
340 p_person_id,
341 p_course_cd,
342 v_teach_cal_type,
343 v_teach_sequence_number,
344 v_message_name) = FALSE THEN
345 IF p_log_creation_dt IS NOT NULL THEN
346 IGS_GE_GEN_003.genp_ins_log_entry(cst_pre_enrol,
347 p_log_creation_dt,
348 cst_minor ||','
349 || p_person_id ||','
350 || p_course_cd,
351 'IGS_EN_STUD_INELG_POS_OVERLAP',
352 NULL);
353 END IF;
354 p_warn_level := cst_minor;
355 p_message_name := 'IGS_EN_STUD_INELG_POS_OVERLAP';
356 RETURN cst_false;
357 END IF;
358 ELSE
359 CLOSE c_cir_tci;
360 END IF;
361 END LOOP;
362 -- If the student has the required units (applied through encumbrances) and
363 -- one or more of the units are not within the set being pre-enrolled in
364 -- the upcoming academic year.
365 OPEN c_pur_pee;
366 FETCH c_pur_pee INTO v_dummy;
367 IF (c_pur_pee%FOUND) THEN
368 IF p_log_creation_dt IS NOT NULL THEN
369 CLOSE c_pur_pee;
370 -- If all warnings are logged then write the exception
371 IGS_GE_GEN_003.genp_ins_log_entry(cst_pre_enrol,
372 p_log_creation_dt,
373 cst_minor ||','
374 || p_person_id ||','
375 || p_course_cd,
376 'IGS_EN_STUD_INELG_ENCUMB',
377 NULL);
378 END IF;
379 p_warn_level := cst_minor;
380 p_message_name := 'IGS_EN_STUD_INELG_ENCUMB';
381 RETURN cst_false;
382 END IF;
383 CLOSE c_pur_pee;
384 -- Check that all of the units prior to the current year within the pattern
385 -- of study have been completed (and passed) or are currently enrolled in
386 -- the relevant teaching calendar types.
387 IF p_always_pre_enrol_ind = 'N' THEN
388 FOR v_posu_posp_rec IN c_posu_posp LOOP
389 -- Search for the IGS_PS_UNIT attempt within the academic year in the nominated
390 -- teaching calendar type. IGS_GE_NOTE: it is not checking whether it was studied
391 -- in the EXACT academic period number - this is not really necessary;
392 -- provided they've reached the current academic year having satisfied
393 -- the requirements of all units they are eligible.
394 OPEN c_sua(v_posu_posp_rec.unit_cd,
395 v_posu_posp_rec.teach_cal_type);
396 FETCH c_sua INTO v_dummy;
397 IF (c_sua%NOTFOUND) THEN
398 IF p_log_creation_dt IS NOT NULL THEN
399 CLOSE c_sua;
400 -- If all warnings are logged then write the exception
401 IGS_GE_GEN_003.genp_ins_log_entry(cst_pre_enrol,
402 p_log_creation_dt,
403 cst_minor || ','
404 || p_person_id ||','
405 || p_course_cd,
406 'IGS_EN_STUD_INELG_PROGRESSION',
407 v_posu_posp_rec.unit_cd);
408 END IF;
409 v_return_flag := TRUE;
410 EXIT;
411 END IF;
412 CLOSE c_sua;
413 END LOOP;
414 IF v_return_flag = TRUE THEN
415 p_warn_level := cst_minor;
416 p_message_name := 'IGS_EN_STUD_INELG_PROGRESSION';
417 RETURN cst_false;
418 END IF;
419 END IF;
420 RETURN cst_true;
421 EXCEPTION
422 WHEN OTHERS THEN
423 IF (c_sua_cir%ISOPEN) THEN
424 CLOSE c_sua_cir;
425 END IF;
426 IF (c_asul%ISOPEN) THEN
427 CLOSE c_asul;
428 END IF;
429 IF (c_pur_pee%ISOPEN) THEN
430 CLOSE c_pur_pee;
431 END IF;
432 IF (c_posu_posp%ISOPEN) THEN
433 CLOSE c_posu_posp;
434 END IF;
435 IF (c_sua%ISOPEN) THEN
436 CLOSE c_sua;
437 END IF;
438 RAISE;
439 END;
440 EXCEPTION
441 WHEN OTHERS THEN
442 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
443 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_005.enrp_get_pos_elgbl');
444 IGS_GE_MSG_STACK.ADD;
445 App_Exception.Raise_Exception;
446 END enrp_get_pos_elgbl;
447
448
449 FUNCTION Enrp_Get_Pre_Uoo(
450 p_unit_cd IN VARCHAR2 ,
451 p_cal_type IN VARCHAR2 ,
452 p_sequence_number IN NUMBER ,
453 p_location_cd IN VARCHAR2 ,
454 p_unit_class IN VARCHAR2 ,
455 p_unit_mode IN VARCHAR2 ,
456 p_crs_location_cd IN VARCHAR2 ,
457 p_uoo_id OUT NOCOPY NUMBER )
458 RETURN BOOLEAN AS
459
460 BEGIN -- enrp_get_pre_uoo
461 -- Routine to select the IGS_PS_UNIT offering option matching the specified
462 -- search criteria.
463 -- If the IGS_PS_UNIT IGS_AD_LOCATION/class parameters are specified then only an
464 -- exact match will be returned.
465 -- If IGS_PS_UNIT IGS_AD_LOCATION and/or class are null then the routine will
466 -- attempt to find a match, on the condition that:
467 -- 1. The IGS_AD_LOCATION code matches either the parameter or the enrolled IGS_PS_COURSE
468 -- IGS_AD_LOCATION code.
469 -- 2. The option class matches the parameter, or if not set any mode will do
470 -- (but a match with the IGS_PS_COURSE attendance mode will take priority)
471 DECLARE
472 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
473 CURSOR c_uoo IS
474 SELECT uoo_id
475 FROM IGS_PS_UNIT_OFR_OPT uoo,
476 IGS_PS_UNIT_VER uv,
477 IGS_PS_UNIT_STAT us
478 WHERE uoo.unit_cd = p_unit_cd AND
479 uoo.cal_type = p_cal_type AND
480 uoo.ci_sequence_number = p_sequence_number AND
481 uoo.location_cd = p_location_cd AND
482 uoo.unit_class = p_unit_class AND
483 uoo.offered_ind = 'Y' AND
484 uv.unit_cd = uoo.unit_cd AND
485 uv.version_number = uoo.version_number AND
486 uv.expiry_dt IS NULL AND
487 us.unit_status = uv.unit_status AND
488 us.s_unit_status = 'ACTIVE';
489 CURSOR c_uoo2 IS
490 SELECT uoo.uoo_id,
491 um.s_unit_mode
492 FROM IGS_PS_UNIT_OFR_OPT uoo,
493 IGS_PS_UNIT_VER uv,
494 IGS_PS_UNIT_STAT us,
495 IGS_AS_UNIT_CLASS uc,
496 IGS_AS_UNIT_MODE um
497 WHERE uoo.unit_cd = p_unit_cd AND
498 uoo.cal_type = p_cal_type AND
499 uoo.ci_sequence_number = p_sequence_number AND
500 uoo.location_cd = NVL(p_location_cd, p_crs_location_cd) AND
501 (p_unit_class IS NULL OR
502 uoo.unit_class = p_unit_class) AND
503 uoo.offered_ind = 'Y' and
504 uoo.unit_cd = uv.unit_cd AND
505 uoo.version_number = uv.version_number AND
506 uv.expiry_dt IS NULL and
507 us.unit_status = uv.unit_status AND
508 us.s_unit_status = 'ACTIVE' AND
509 uoo.unit_class = uc.unit_class AND
510 uc.closed_ind = 'N' AND
511 uc.unit_mode = um.unit_mode;
512 v_uoo_rec c_uoo%ROWTYPE;
513 v_full_match_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE DEFAULT NULL;
514 v_partial_match_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE DEFAULT NULL;
515 BEGIN
516 p_uoo_id := NULL;
517 -- If both the mode and class have been specified,
518 -- then search for a uoo matching.
519 -- If not found, then return NULL.
520 IF p_location_cd IS NOT NULL AND
521 p_unit_class IS NOT NULL THEN
522 OPEN c_uoo;
523 FETCH c_uoo INTO v_uoo_rec;
524 IF c_uoo%NOTFOUND THEN
525 CLOSE c_uoo;
526 RETURN FALSE;
527 END IF;
528 CLOSE c_uoo;
529 p_uoo_id := v_uoo_rec.uoo_id;
530 RETURN TRUE;
531 END IF;
532 -- Attempt to select the closest match from the IGS_PS_UNIT offering option table.
533 FOR v_uoo_rec IN c_uoo2 LOOP
534 -- If the class is set or the mode matches the IGS_PS_COURSE mode,
535 -- then it is considered an exact match.
536 IF p_unit_class IS NOT NULL THEN
537 v_full_match_uoo_id := v_uoo_rec.uoo_id;
538 EXIT;
539 ELSIF p_unit_mode = '%' OR
540 p_unit_mode = v_uoo_rec.s_unit_mode THEN
541 v_full_match_uoo_id := v_uoo_rec.uoo_id;
542 EXIT;
543 ELSE
544 v_partial_match_uoo_id := v_uoo_rec.uoo_id;
545 END IF;
546 END LOOP;
547 -- If set, use the full match UOO, otherwise use the partial
548 -- match (ie. mode differs).
549 IF v_full_match_uoo_id IS NOT NULL THEN
550 p_uoo_id := v_full_match_uoo_id;
551 RETURN TRUE;
552 ELSIF v_partial_match_uoo_id IS NOT NULL THEN
553 p_uoo_id := v_partial_match_uoo_id;
554 RETURN TRUE;
555 END IF;
556 p_uoo_id := NULL;
557 RETURN FALSE;
558 EXCEPTION
559 WHEN OTHERS THEN
560 IF c_uoo%ISOPEN THEN
561 CLOSE c_uoo;
562 END IF;
563 IF c_uoo2%ISOPEN THEN
564 CLOSE c_uoo2;
565 END IF;
566 RAISE;
567 END;
568 EXCEPTION
569 WHEN OTHERS THEN
570 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
571 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_005.enrp_get_pre_uoo');
572 IGS_GE_MSG_STACK.ADD;
573 App_Exception.Raise_Exception;
574 END enrp_get_pre_uoo;
575
576
577 FUNCTION Enrp_Get_Pos_Links(
578 p_location_cd IN VARCHAR2 ,
579 p_attendance_mode IN VARCHAR2 ,
580 p_attendance_type IN VARCHAR2 ,
581 p_unit_set_cd IN VARCHAR2 ,
582 p_adm_cal_type IN VARCHAR2 ,
583 p_admission_cat IN VARCHAR2 ,
584 p_pos_location_cd IN VARCHAR2 ,
585 p_pos_attendance_mode IN VARCHAR2 ,
586 p_pos_attendance_type IN VARCHAR2 ,
587 p_pos_unit_set_cd IN VARCHAR2 ,
588 p_pos_adm_cal_type IN VARCHAR2 ,
589 p_pos_admission_cat IN VARCHAR2 )
590 RETURN NUMBER AS
591
592 BEGIN -- enrp_get_pos_links
593 -- Totals the number of elements of the IGS_PS_PAT_OF_STUDY linkages which
594 -- match the parameter record. If the values in the linkages are NULL,
595 -- then they don't count as a match. The ain is to find the record which
596 -- has the most number of specific matches.
597 DECLARE
598 v_match_count NUMBER;
599 BEGIN
600 v_match_count := 0;
601 IF p_location_cd = NVL(p_pos_location_cd, 'NOMATCH') THEN
602 v_match_count := v_match_count + 1;
603 END IF;
604 IF p_attendance_mode = NVL(p_pos_attendance_mode, 'NOMATCH') THEN
605 v_match_count := v_match_count + 1;
606 END IF;
607 IF p_attendance_type = NVL(p_pos_attendance_type, 'NOMATCH') THEN
608 v_match_count := v_match_count + 1;
609 END IF;
610 IF NVL(p_unit_set_cd, 'NOVALUE') = NVL(p_pos_unit_set_cd, 'NOMATCH') THEN
611 v_match_count := v_match_count + 1;
612 END IF;
613 IF NVL(p_adm_cal_type, 'NOVALUE') = NVL(p_pos_adm_cal_type, 'NOMATCH') THEN
614 v_match_count := v_match_count + 1;
615 END IF;
616 IF NVL(p_admission_cat, 'NOVALUE') = NVL(p_pos_admission_cat, 'NOMATCH') THEN
617 v_match_count := v_match_count + 1;
618 END IF;
619 RETURN v_match_count;
620 END;
621
622 END enrp_get_pos_links;
623
624
625 FUNCTION Enrp_Get_First_Enr(
626 p_person_id IN NUMBER )
627 RETURN VARCHAR2 AS
628 gv_other_details VARCHAR2(255);
629 BEGIN
630 DECLARE
631 -- modified cursor for performance bug 3687265
632 CURSOR c_get_acad_alt_cd IS
633 SELECT SUBSTR(IGS_EN_GEN_014.enrs_get_acad_alt_cd(sua_v.cal_type,sua_v.ci_sequence_number),1,10)
634 FROM IGS_EN_SU_ATTEMPT sua_v,
635 IGS_CA_INST ci
636 WHERE sua_v.person_id = p_person_id AND
637 sua_v.enrolled_dt IS NOT NULL AND
638 sua_v.cal_type = ci.cal_type AND
639 sua_v.ci_sequence_number = ci.sequence_number
640 ORDER BY ci.start_dt,
641 ci.end_dt ;
642
643 v_acad_alt_cd IGS_CA_INST.alternate_code%TYPE;
644 BEGIN
645 --- Retrieve the student IGS_PS_UNIT attempt records for the IGS_PE_PERSON.
646 --- The order the records are returned will ensure the oldest IGS_PS_UNIT
647 --- attempt record for the IGS_PE_PERSON is returned first.
648 OPEN c_get_acad_alt_cd;
649 FETCH c_get_acad_alt_cd INTO v_acad_alt_cd;
650 --- Many records may be returned, but we only want the first record.
651 --- Return the result of the query, may be null if none were found.
652 IF c_get_acad_alt_cd%NOTFOUND THEN
653 CLOSE c_get_acad_alt_cd;
654 RETURN NULL;
655 ELSE
656 CLOSE c_get_acad_alt_cd;
657 RETURN v_acad_alt_cd;
658 END IF;
659 END;
660
661 END enrp_get_first_enr;
662
663
664 FUNCTION Enrp_Get_Frst_Enr_Yr(
665 p_person_id IN NUMBER )
666 RETURN DATE AS
667 BEGIN
668 -- This is a stub only and needs to be updated when the spec is complete.
669 RETURN NULL;
670 END enrp_get_frst_enr_yr;
671
672
673 FUNCTION Enrp_Get_Last_Enr(
674 p_person_id IN NUMBER )
675 RETURN VARCHAR2 AS
676 gv_other_details VARCHAR2(255);
677 BEGIN
678 DECLARE
679 --modified cursor for performance bug 3687150
680 CURSOR c_get_acad_alt_cd IS
681 SELECT SUBSTR(IGS_EN_GEN_014.enrs_get_acad_alt_cd(sua_v.cal_type,sua_v.ci_sequence_number),1,10)
682 FROM IGS_EN_SU_ATTEMPT sua_v,
683 IGS_CA_INST ci
684 WHERE sua_v.person_id = p_person_id AND
685 sua_v.enrolled_dt IS NOT NULL AND
686 sua_v.cal_type = ci.cal_type AND
687 sua_v.ci_sequence_number = ci.sequence_number
688 ORDER BY ci.start_dt desc,
689 ci.end_dt desc ;
690 v_acad_alt_cd IGS_CA_INST.alternate_code%TYPE;
691 BEGIN
692 --- Retrieve the student IGS_PS_UNIT attempt records for the IGS_PE_PERSON.
693 --- The order the records are returned will ensure the newest IGS_PS_UNIT
694 --- attempt record for the IGS_PE_PERSON is returned first.
695 OPEN c_get_acad_alt_cd;
696 FETCH c_get_acad_alt_cd INTO v_acad_alt_cd;
697 --- Many records may be returned, but we only want the first record.
698 --- Return the result of the query, may be null if none were found.
699 IF c_get_acad_alt_cd%NOTFOUND THEN
700 CLOSE c_get_acad_alt_cd;
701 RETURN NULL;
702 ELSE
703 CLOSE c_get_acad_alt_cd;
704 RETURN v_acad_alt_cd;
705 END IF;
706 END;
707
708 END enrp_get_last_enr;
709
710
711 FUNCTION Enrp_Get_Last_Enr_Yr(
712 p_person_id IN NUMBER )
713 RETURN DATE AS
714 BEGIN
715 -- This is a stub only and needs to be updated when the spec is complete.
716 RETURN NULL;
717 END enrp_get_last_enr_yr;
718
719 END IGS_EN_GEN_005;
[Home] [Help]