1 PACKAGE BODY IGS_EN_GEN_006 AS
2 /* $Header: IGSEN06B.pls 120.4 2006/04/13 01:51:56 smaddali ship $ */
3
4 -------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 --prchandr 08-Jan-01 Enh Bug No: 2174101, As the Part of Change in IGSEN18B
8 -- Passing NULL as parameters to ENRP_CLC_SUA_EFTSU
9 -- ENRP_CLC_EFTSU_TOTAL for Key course cd and version number
10 --pradhakr 15-Jan-03 Modified the call to the function enrp_get_load_incur to add
11 -- a parameter no_assessment_ind. Changes wrt Bug# 2743459.
12 --smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_sua_um and c_suaeh to select active (not closed) unit classes.
13 -- rnirwani 13-Sep-2004 changed cursor c_sci (Enrp_Get_Sca_Elgbl) to not consider logically deleted records and
14 -- also to avoid un-approved intermission records. Bug# 3885804
15 -- ctyagi 20-feb-2005 Removed the function Enrp_Get_Sca_Hist_Am. Bug# 3712531
16 -- smaddali 10-apr-06 Added new column for bug#5091858 BUILD EN324
17 -------------------------------------------------------------------------------------------
18
19 Function Enrp_Get_Sca_Acad(
20 p_person_id IN NUMBER ,
21 p_course_cd IN VARCHAR2 ,
22 p_cal_type IN VARCHAR2 ,
23 p_ci_sequence_number OUT NOCOPY NUMBER ,
24 p_enrolment_cat OUT NOCOPY VARCHAR2 ,
25 p_message_name OUT NOCOPY VARCHAR2)
26 RETURN BOOLEAN AS
27
28 BEGIN -- enrp_get_sca_acad
29 -- Determine the academic calendar type and sequence number for the
30 -- IGS_PS_COURSE offering option calendar type.
31 -- This is required for validation purposes during a IGS_PS_COURSE transfer.
32 DECLARE
33 v_cal_type IGS_CA_INST.cal_type%TYPE;
34 v_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
35 v_acad_cal_type IGS_CA_INST.cal_type%TYPE;
36 v_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
37 v_acad_ci_start_dt IGS_CA_INST.start_dt%TYPE;
38 v_acad_ci_end_dt IGS_CA_INST.end_dt%TYPE;
39 v_message_name VARCHAR2(30);
40 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
41 v_enrolment_cat IGS_AS_SC_ATMPT_ENR.enrolment_cat%TYPE;
42 CURSOR c_scae_ci IS
43 SELECT scae.cal_type,
44 scae.ci_sequence_number,
45 scae.enrolment_cat
46 FROM IGS_AS_SC_ATMPT_ENR scae,
47 IGS_CA_INST ci
48 WHERE scae.person_id = p_person_id AND
49 scae.course_cd = p_course_cd AND
50 scae.cal_type = ci.cal_type AND
51 scae.ci_sequence_number = ci.sequence_number
52 ORDER BY ci.start_dt DESC;
53 BEGIN
54 p_message_name := null;
55 -- Check parameters
56 IF p_person_id IS NULL OR
57 p_course_cd IS NULL OR
58 p_cal_type IS NULL THEN
59 RETURN TRUE;
60 END IF;
61 -- Get the enrolment period from the latest student IGS_PS_COURSE
62 -- attempt enrolment period.
63 OPEN c_scae_ci;
64 FETCH c_scae_ci INTO v_cal_type,
65 v_ci_sequence_number,
66 v_enrolment_cat;
67 IF (c_scae_ci%NOTFOUND) THEN
68 CLOSE c_scae_ci;
69 p_ci_sequence_number := NULL;
70 p_enrolment_cat := NULL;
71 p_message_name := 'IGS_EN_NO_SPA_ENR_EXISTS';
72 RETURN FALSE;
73 END IF;
74 CLOSE c_scae_ci;
75 p_enrolment_cat := v_enrolment_cat;
76 -- Check if a link exists for the teaching period to the
77 -- academic calendar of the new IGS_PS_COURSE offering option.
78 v_alternate_code := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD (
79 v_cal_type,
80 v_ci_sequence_number,
81 v_acad_cal_type,
82 v_acad_ci_sequence_number,
83 v_acad_ci_start_dt,
84 v_acad_ci_end_dt,
85 v_message_name);
86 IF v_acad_cal_type <> p_cal_type THEN
87 p_message_name := 'IGS_EN_NOLINK_EXISTS_STUDENR';
88 RETURN FALSE;
89 END IF;
90 -- complete execution
91 IF v_message_name IS NOT NULL THEN
92 p_ci_sequence_number := v_ci_sequence_number;
93 ELSE
94 p_ci_sequence_number := v_acad_ci_sequence_number;
95 END IF;
96 RETURN TRUE;
97 EXCEPTION
98 WHEN OTHERS THEN
99 IF (c_scae_ci%ISOPEN) THEN
100 CLOSE c_scae_ci;
101 END IF;
102 RAISE;
103 END;
104 EXCEPTION
105 WHEN OTHERS THEN
106 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
107 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_006.enrp_get_sca_acad');
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END enrp_get_sca_acad;
111
112
113 Function Enrp_Get_Sca_Am(
114 p_person_id IN NUMBER ,
115 p_course_cd IN VARCHAR2 ,
116 p_load_cal_type IN VARCHAR2 ,
117 p_load_sequence_number IN NUMBER )
118 RETURN VARCHAR2 AS
119
120 BEGIN --enrp_get_sca_am
121 --This module gets the attendance mode for a nominated
122 --student IGS_PS_COURSE attempt within a load calendar instance.
123 --This routine checks the 'incurred' status of student IGS_PS_UNIT
124 --attempts prior to including them in the calculations.
125 --If the student is not enrolled in any applicable units
126 --the routine will return NULL.
127 DECLARE
128 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
129 cst_on CONSTANT VARCHAR2(2) := 'ON';
130 cst_off CONSTANT VARCHAR2(3) := 'OFF';
131 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
132 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
133 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
134 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
135 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
136 v_on_campus BOOLEAN DEFAULT FALSE;
137 v_off_campus BOOLEAN DEFAULT FALSE;
138 v_retval VARCHAR2(10) DEFAULT NULL;
139 CURSOR c_ci1 IS
140 SELECT ci1.cal_type,
141 ci1.sequence_number
142 FROM IGS_CA_INST ci1,
143 IGS_CA_TYPE cat,
144 IGS_CA_STAT cs
145 WHERE cat.cal_type = ci1.cal_type AND
146 cat.s_cal_cat = cst_academic AND
147 ci1.cal_status = cs.cal_status AND
148 cs.s_cal_status = cst_active AND
149 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
150 ci1.cal_type,
151 ci1.sequence_number,
152 p_load_cal_type,
153 p_load_sequence_number,
154 'Y') = 'Y';
155 CURSOR c_sua_um (
156 cp_ci_cal_type IGS_CA_INST.cal_type%TYPE,
157 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
158 SELECT um.s_unit_mode
159 FROM IGS_EN_SU_ATTEMPT sua,
160 IGS_AS_UNIT_CLASS ucl,
161 IGS_AS_UNIT_MODE um
162 WHERE sua.person_id = p_person_id AND
163 sua.course_cd = p_course_cd AND
164 sua.unit_attempt_status IN (
165 cst_enrolled,
166 cst_completed,
167 cst_discontin) AND
168 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
169 cp_ci_cal_type,
170 cp_ci_sequence_number,
171 sua.cal_type,
172 sua.ci_sequence_number,
173 'Y') = 'Y' AND
174 IGS_EN_PRC_LOAD.enrp_get_load_incur(
175 sua.cal_type,
176 sua.ci_sequence_number,
177 sua.discontinued_dt,
178 sua.administrative_unit_status,
179 sua.unit_attempt_status,
180 sua.no_assessment_ind,
181 p_load_cal_type,
182 p_load_sequence_number,
183 NULL,
184 -- anilk, Audit special fee build
185 'N') = 'Y' AND
186 ucl.unit_class = sua.unit_class AND
187 ucl.closed_ind = 'N' AND
188 um.unit_mode = ucl.unit_mode;
189 BEGIN
190 FOR v_ci1_rec IN c_ci1 LOOP
191 FOR v_sua_um_rec IN c_sua_um(
192 v_ci1_rec.cal_type,
193 v_ci1_rec.sequence_number) LOOP
194 --Set flags depending on the mode of the IGS_PS_UNIT attempt
195 IF v_sua_um_rec.s_unit_mode = cst_on THEN
196 v_on_campus := TRUE;
197 ELSIF v_sua_um_rec.s_unit_mode = cst_off THEN
198 v_off_campus := TRUE;
199 ELSIF v_sua_um_rec.s_unit_mode = cst_composite THEN
200 v_on_campus := TRUE;
201 v_off_campus := TRUE;
202 END IF;
203 --If the student is multi modal there is no need to continue
204 IF v_on_campus AND
205 v_off_campus THEN
206 EXIT;
207 END IF;
208 END LOOP; -- v_sua_um_rec
209 IF v_on_campus AND
210 v_off_campus THEN
211 EXIT;
212 END IF;
213 END LOOP; -- v_ci1_rec
214 IF v_on_campus AND
215 v_off_campus THEN
216 v_retval := cst_composite;
217 ELSIF v_on_campus THEN
218 v_retval := cst_on;
219 ELSIF v_off_campus THEN
220 v_retval := cst_off;
221 END IF;
222 RETURN v_retval;
223 EXCEPTION
224 WHEN OTHERS THEN
225 IF c_ci1%ISOPEN THEN
226 CLOSE c_ci1;
227 END IF;
228 IF c_sua_um%ISOPEN THEN
229 CLOSE c_sua_um;
230 END IF;
231 RAISE;
232 END;
233 EXCEPTION
234 WHEN OTHERS THEN
235 IF SQLCODE <>-20001 THEN
236 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
237 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_006.enrp_get_sca_am');
238 IGS_GE_MSG_STACK.ADD;
239 App_Exception.Raise_Exception(NULL,NULL,FND_MESSAGE.GET);
240 ELSE
241 RAISE;
242 END IF;
243 END enrp_get_sca_am;
244
245
246 Function Enrp_Get_Sca_Att(
247 p_person_id IN NUMBER ,
248 p_course_cd IN VARCHAR2 ,
249 p_effective_dt IN DATE )
250 RETURN VARCHAR2 AS
251 BEGIN
252 DECLARE
253 NO_SECC_RECORD_FOUND EXCEPTION;
254 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
255 cst_load CONSTANT VARCHAR2(10) := 'LOAD';
256 v_daiv_rec_found BOOLEAN;
257 v_cal_type IGS_EN_STDNT_PS_ATT.cal_type%TYPE;
258 v_load_effect_dt_alias IGS_EN_CAL_CONF.load_effect_dt_alias%TYPE;
259 v_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
260 v_period_load IGS_EN_ATD_TYPE_LOAD.lower_enr_load_range%TYPE;
261 v_period_credit_points NUMBER;
262 v_current_load_cal_type IGS_CA_INST.cal_type%TYPE;
263 v_current_load_sequence_number IGS_CA_INST.sequence_number%TYPE;
264 v_current_acad_cal_type IGS_CA_INST.cal_type%TYPE;
265 v_current_acad_sequence_number IGS_CA_INST.sequence_number%TYPE;
266 CURSOR c_stu_crs_atmpt(
267 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
268 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)IS
269 SELECT SCA.cal_type
270 FROM IGS_EN_STDNT_PS_ATT SCA
271 WHERE SCA.person_id = cp_person_id AND
272 SCA.course_cd = cp_course_cd;
273 CURSOR c_s_enr_cal_conf IS
274 SELECT SECC.load_effect_dt_alias
275 FROM IGS_EN_CAL_CONF SECC
276 WHERE SECC.s_control_num = 1;
277 CURSOR c_cal_instance(
278 cp_cal_type IGS_CA_INST.cal_type%TYPE,
279 cp_effective_dt IGS_CA_INST.start_dt%TYPE)IS
280 SELECT CI.cal_type,
281 CI.sequence_number
282 FROM IGS_CA_INST CI,
283 IGS_CA_STAT CS
284 WHERE CI.cal_type = cp_cal_type AND
285 CI.start_dt <= cp_effective_dt AND
286 CI.end_dt >= cp_effective_dt AND
287 CS.cal_status = CI.cal_status AND
288 CS.s_cal_status = cst_active
289 ORDER BY CI.start_dt desc;
290 CURSOR c_cal_type_instance(
291 cp_cal_type IGS_CA_INST.cal_type%TYPE,
292 cp_sequence_number IGS_CA_INST.sequence_number%TYPE)IS
293 SELECT CI.cal_type,
294 CI.sequence_number,
295 CI.start_dt,
296 CI.end_dt
297 FROM IGS_CA_TYPE CT,
298 IGS_CA_INST CI,
299 IGS_CA_STAT CS
300 WHERE CT.closed_ind = 'N' AND
301 CS.s_cal_status = cst_active AND
302 CI.cal_status = CS.cal_status AND
303 CT.s_cal_cat = cst_load AND
304 CI.cal_type = CT.cal_type AND
305 (IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(cp_cal_type,
306 cp_sequence_number,
307 CI.cal_type,
308 CI.sequence_number,
309 'N') = 'Y')
310 ORDER BY CI.start_dt asc;
311 CURSOR c_dai_v(
312 cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
313 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
314 cp_load_effect_dt_alias IGS_EN_CAL_CONF.load_effect_dt_alias%TYPE) IS
315 SELECT DAIV.alias_val
316 FROM IGS_CA_DA_INST_V DAIV
317 WHERE DAIV.cal_type = cp_cal_type AND
318 DAIV.ci_sequence_number = cp_ci_sequence_number AND
319 DAIV.dt_alias = cp_load_effect_dt_alias;
320 v_other_detail VARCHAR(255);
321 BEGIN
322 -- Get the current attendance type for a student IGS_PS_COURSE attempt as at the
323 -- effective date. Typically the effective date will be the current date.
324 -- The attendance type is derived based on load calendar instances, using
325 -- the 'load effective' date alias as the reference point for determining
326 -- which calendar is the current load calendar.
327 -- Load the student IGS_PS_COURSE attempt details.
328 OPEN c_stu_crs_atmpt(
329 p_person_id,
330 p_course_cd);
331 FETCH c_stu_crs_atmpt INTO v_cal_type;
332 IF(c_stu_crs_atmpt%NOTFOUND) THEN
333 CLOSE c_stu_crs_atmpt;
334 RETURN NULL;
335 END IF;
336 CLOSE c_stu_crs_atmpt;
337 -- Determine the 'current' load calendar instance based on the 'load effective'
338 -- date alias from the enrolment calendar configuration. If this date alias
339 -- can't be located then the latest calendar instance where start_dt/end_dt
340 -- encompass the effective dt is deemed current.
341 OPEN c_s_enr_cal_conf;
342 FETCH c_s_enr_cal_conf INTO v_load_effect_dt_alias;
343 IF (c_s_enr_cal_conf%NOTFOUND) THEN
344 CLOSE c_s_enr_cal_conf;
345 RAISE NO_SECC_RECORD_FOUND;
346 END IF;
347 CLOSE c_s_enr_cal_conf;
348 v_current_load_cal_type := NULL;
349 v_current_load_sequence_number := NULL;
350 v_current_acad_cal_type := NULL;
351 v_current_acad_sequence_number := NULL;
352 FOR v_cal_instance_rec IN c_cal_instance(
353 v_cal_type,
354 p_effective_dt)
355 LOOP
356 FOR v_cal_type_instance_rec IN c_cal_type_instance(
357 v_cal_instance_rec.cal_type,
358 v_cal_instance_rec.sequence_number)
359 LOOP
360 -- Attempt to find 'load effective' dt alias against the
361 -- calendar instance.
362 v_daiv_rec_found := FALSE;
363 FOR v_daiv_rec IN c_dai_v(
364 v_cal_type_instance_rec.cal_type,
365 v_cal_type_instance_rec.sequence_number,
366 v_load_effect_dt_alias)
367 LOOP
368 v_daiv_rec_found := TRUE;
369 IF(p_effective_dt >= v_daiv_rec.alias_val) THEN
370 v_current_load_cal_type := v_cal_type_instance_rec.cal_type;
371 v_current_load_sequence_number := v_cal_type_instance_rec.sequence_number;
372 v_current_acad_cal_type := v_cal_instance_rec.cal_type;
373 v_current_acad_sequence_number := v_cal_instance_rec.sequence_number;
374 END IF;
375 END LOOP;
376 IF(v_daiv_rec_found = FALSE) THEN
377 IF(p_effective_dt >= v_cal_type_instance_rec.start_dt AND
378 p_effective_dt <= v_cal_type_instance_rec.end_dt) THEN
379 v_current_load_cal_type := v_cal_type_instance_rec.cal_type;
380 v_current_load_sequence_number := v_cal_type_instance_rec.sequence_number;
381 v_current_acad_cal_type := v_cal_instance_rec.cal_type;
382 v_current_acad_sequence_number := v_cal_instance_rec.sequence_number;
383 END IF;
384 END IF;
385 END LOOP;
386 IF(v_current_load_cal_type IS NOT NULL) THEN
387 EXIT;
388 END IF;
389 END LOOP;
390 IF(v_current_load_cal_type IS NULL) THEN
391 RETURN NULL;
392 END IF;
393 -- Call ENRP_CLC_LOAD_TOTAL routine to get the load incurred within the
394 -- current load period
395 v_period_load := IGS_EN_PRC_LOAD.ENRP_CLC_EFTSU_TOTAL(
396 p_person_id,
397 p_course_cd,
398 v_current_acad_cal_type,
399 v_current_acad_sequence_number,
400 v_current_load_cal_type,
401 v_current_load_sequence_number,
402 'Y',
403 'Y',
404 NULL,
405 NULL,
406 v_period_credit_points);
407 -- Call routine to determine the attendance type for the calculated load
408 -- figure within the current load calendar
409 v_attendance_type := IGS_EN_PRC_LOAD.ENRP_GET_LOAD_ATT(
410 v_current_load_cal_type,
411 v_period_load);
412 RETURN v_attendance_type;
413 EXCEPTION
414 WHEN NO_SECC_RECORD_FOUND THEN
415 Fnd_Message.Set_name('FND','FORM_RECORD_DELETED');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 WHEN OTHERS THEN
419 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
420 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_006.enrp_get_sca_att');
421 IGS_GE_MSG_STACK.ADD;
422 App_Exception.Raise_Exception;
423 END;
424 END enrp_get_sca_att;
425
426
427 Function Enrp_Get_Sca_Comm(
428 p_person_id IN NUMBER ,
429 p_course_cd IN VARCHAR2 ,
430 p_student_confirmed_ind IN VARCHAR2 DEFAULT 'N',
431 p_effective_date IN DATE )
432 RETURN boolean AS
433 BEGIN
434 DECLARE
435 v_commence_cutoff_dt_alias IGS_EN_CAL_CONF.commence_cutoff_dt_alias%TYPE;
436 v_cal_type IGS_CA_INST.cal_type%TYPE;
437 v_sequence_number IGS_CA_INST.sequence_number%TYPE;
438 v_sua_ci_rec_found BOOLEAN;
439 v_dai_rec_found BOOLEAN;
440 CURSOR c_s_enr_cal_conf IS
441 SELECT commence_cutoff_dt_alias
442 FROM IGS_EN_CAL_CONF
443 WHERE s_control_num = 1;
444 CURSOR c_sua_ci(
445 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
446 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE) IS
447 SELECT IGS_EN_SU_ATTEMPT.cal_type,
448 IGS_EN_SU_ATTEMPT.ci_sequence_number,
449 IGS_CA_INST.start_dt
450 FROM IGS_EN_SU_ATTEMPT,
451 IGS_CA_INST
452 WHERE IGS_EN_SU_ATTEMPT.person_id = cp_person_id AND
453 IGS_EN_SU_ATTEMPT.course_cd = cp_course_cd AND
454 IGS_EN_SU_ATTEMPT.cal_type = IGS_CA_INST.cal_type AND
455 IGS_EN_SU_ATTEMPT.ci_sequence_number = IGS_CA_INST.sequence_number
456 ORDER BY IGS_CA_INST.start_dt;
457
458 CURSOR c_dai_v(cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
459 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
460 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
461 SELECT IGS_CA_GEN_001.calp_set_alias_value
462 (
463 absolute_val,
464 IGS_CA_GEN_002.cals_clc_dt_from_dai
465 (
466 ci_sequence_number,
467 CAL_TYPE,
468 DT_ALIAS,
469 sequence_number
470 )
471 ) alias_val
472 FROM IGS_CA_DA_INST
473 WHERE cal_type = cp_cal_type AND
474 ci_sequence_number = cp_ci_sequence_number AND
475 dt_alias = cp_dt_alias
476 ORDER BY alias_val;
477 v_other_detail VARCHAR(255);
478 BEGIN
479 -- This module gets whether the student is considered commencing in their
480 -- IGS_PS_COURSE for the purposes of enrolment. IGS_GE_NOTE: There may be other
481 -- commencing calculations in the system which apply for other purposes. Eg.
482 -- Statistics sub-system has a much more complicated definition of a
483 -- commencing student. This calculation will derive whether as at a
484 -- nominated effective date the student should still be considered
485 -- commencing within the nominated IGS_PS_COURSE attempt. A student is considered a
486 -- commencing student until a given date alias within their first teaching
487 -- period has been reached
488 v_sua_ci_rec_found := FALSE;
489 v_dai_rec_found := FALSE;
490 -- if IGS_PS_COURSE isn't confirmed, student is considered commencing.
491 IF p_student_confirmed_ind = 'N' THEN
492 RETURN TRUE;
493 END IF;
494 OPEN c_s_enr_cal_conf;
495 FETCH c_s_enr_cal_conf INTO v_commence_cutoff_dt_alias;
496 IF(c_s_enr_cal_conf%NOTFOUND) THEN
497 CLOSE c_s_enr_cal_conf;
498 RAISE NO_DATA_FOUND;
499 END IF;
500 CLOSE c_s_enr_cal_conf;
501 FOR v_sua_ci_rec IN c_sua_ci(
502 p_person_id,
503 p_course_cd)
504 LOOP
505 v_sua_ci_rec_found := TRUE;
506 v_cal_type := v_sua_ci_rec.cal_type;
507 v_sequence_number := v_sua_ci_rec.ci_sequence_number;
508 EXIT;
509 END LOOP;
510 IF(v_sua_ci_rec_found = FALSE) THEN
511 RETURN TRUE;
512 END IF;
513 FOR v_dai_rec IN c_dai_v(
514 v_cal_type,
515 v_sequence_number,
516 v_commence_cutoff_dt_alias)
517 LOOP
518 v_dai_rec_found := TRUE;
519 IF(p_effective_date <= v_dai_rec.alias_val) THEN
520 RETURN TRUE;
521 ELSE
522 RETURN FALSE;
523 END IF;
524 END LOOP;
525 IF(v_dai_rec_found = FALSE) THEN
526 RETURN FALSE;
527 END IF;
528 /*
529 EXCEPTION
530
531 WHEN OTHERS THEN
532 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
533 App_Exception.Raise_Exception;
534 */
535 END;
536 END enrp_get_sca_comm;
537
538
539 Function Enrp_Get_Sca_Elgbl(
540 p_person_id IN NUMBER ,
541 p_course_cd IN VARCHAR2 ,
542 p_student_comm_type IN VARCHAR2 ,
543 p_acad_cal_type IN VARCHAR2 ,
544 p_acad_ci_sequence_number IN NUMBER ,
545 p_dflt_confirmed_course_ind IN VARCHAR2 DEFAULT 'N',
546 p_message_name OUT NOCOPY VARCHAR2)
547 RETURN boolean AS
548 /*
549 || Created By :
550 || Created On :
551 || Purpose : This procedure process the Application
552 || Known limitations, enhancements or remarks :
553 || Change History :
554 || Who When What
555 || pkpatel 09-SEP-2001 Bug no.1960126 :For Academic Record Maintenance
556 || Modified the defination of Cursor 'c_sci' to include
557 || the logic for INtermission Type Approval
558 || pradhakr 29-Jan-2003 Added a message IGS_EN_CAL_CONF_NOT_SET.
559 || Changes wrt bug# 2675905
560 || (reverse chronological order - newest change first)
561 */
562 gv_other_detail VARCHAR2(255);
563 gv_extra_detail VARCHAR2(255) DEFAULT NULL;
564 BEGIN
565 -- Validate whether the nominated IGS_PE_PERSON is eligible to enrol in the nominated
566 -- IGS_PS_COURSE in an nominated academic period. This routine performs the same
567 -- logic for both new and returning students, due to the requirements of
568 -- re-admission and IGS_PS_COURSE transfer which blur the strict lines between the
569 -- two. The following checks are performed:
570 -- * The deceased_ind for the IGS_PE_PERSON is not set
571 -- * Student has no exclusions/encumbrances preventing them from enrolling.
572 -- The student must be excluded from all teaching periods linked to the
573 -- academic year to be ineligible.
574 -- * Student has an offer in the IGS_PS_COURSE within the nominated academic period,
575 -- or an existing student IGS_PS_COURSE attempt record which is of a status which
576 -- is ongoing (ie. ENROLLED, COMPLETED or INACTIVE).
577 -- * Student has a conditional offer that is satisfactory or waived, or
578 -- pending and it is not a requirement for it to be satisfied on
579 -- confirmation.
580 -- * Student has research IGS_RE_CANDIDATURE details if the IGS_PS_COURSE attempt is
581 -- defined as a research IGS_PS_COURSE.
582 -- * A IGS_EN_STDNT_PS_ATT record exists with a course_attempt_status of
583 -- INTERMIT and student_intermission record exists with an end_dt within the
584 -- academic period.
585 --
586 -- The routine will return TRUE if the student is eligible to enrol/re-enrol,
587 -- and FALSE if not. The message number will be set in the case that they are
588 -- ineligible and will contain the message number of the reason
589 -- for ineligibility.
590
591 DECLARE
592 cst_teaching CONSTANT VARCHAR2(8) := 'TEACHING';
593 cst_new_student CONSTANT VARCHAR2(3) := 'NEW';
594 cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
595 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
596 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
597 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
598 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
599 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
600
601 v_deceased_ind IGS_PE_PERSON.deceased_ind%TYPE;
602 v_instance_start_dt IGS_CA_INST.start_dt%TYPE;
603 v_instance_end_dt IGS_CA_INST.end_dt%TYPE;
604 v_intrmsn_start_dt IGS_EN_STDNT_PS_INTM.start_dt%TYPE;
605 v_intrmsn_end_dt IGS_EN_STDNT_PS_INTM.end_dt%TYPE;
606 v_census_dt_alias IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE;
607 v_course_status
608 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE DEFAULT NULL;
609 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
610
611 v_valid_enrolment BOOLEAN;
612 v_daiv_rec_found BOOLEAN;
613 v_valid_pre_sysdate BOOLEAN;
614 v_valid_post_sysdate BOOLEAN;
615 v_excluded BOOLEAN;
616
617 v_message_name VARCHAR2(30);
618
619 v_acaiv_offer_dt IGS_AD_PS_APPL_INST_APLINST_V.offer_dt%TYPE DEFAULT NULL;
620 v_adm_cndtnl_offer_status IGS_AD_PS_APPL_INST_APLINST_V.ADM_CNDTNL_OFFER_STATUS%TYPE;
621 v_cndtnl_off_must_be_stsfd_ind IGS_AD_PS_APPL_INST_APLINST_V.cndtnl_offer_must_be_stsfd_ind%TYPE;
622 v_discontinued_dt IGS_EN_STDNT_PS_ATT.discontinued_dt%TYPE;
623 v_lapsed_dt IGS_EN_STDNT_PS_ATT.lapsed_dt%TYPE;
624 v_cop_offered_ind IGS_PS_OFR_PAT.offered_ind%TYPE;
625 v_s_adm_cndtnl_offer_status IGS_LOOKUPS_view.lookup_code%TYPE DEFAULT NULL;
626
627 --modified cursor for performance bug 4968380
628 CURSOR c_person IS
629 SELECT DECODE(Pbv.DATE_OF_DEATH,NULL,NVL(PE.DECEASED_IND,'N'),'Y') DECEASED_IND
630 FROM IGS_PE_HZ_PARTIES pe,
631 IGS_PE_PERSON_BASE_V pbv
632 WHERE pe.party_id = p_person_id AND
633 pbv.person_id = pe.party_id;
634
635 CURSOR c_ci IS
636 SELECT ci.start_dt,
637 ci.end_dt
638 FROM IGS_CA_INST ci
639 WHERE ci.cal_type = p_acad_cal_type AND
640 ci.sequence_number = p_acad_ci_sequence_number;
641
642 CURSOR c_s_gen_cal_conf IS
643 SELECT sgcc.census_dt_alias
644 FROM IGS_GE_S_GEN_CAL_CON sgcc
645 WHERE sgcc.s_control_num = 1;
646
647 CURSOR c_ct_ci IS
648 SELECT ci.cal_type,
649 ci.sequence_number
650 FROM IGS_CA_INST_REL cir,
651 IGS_CA_TYPE cat,
652 IGS_CA_INST ci,
653 IGS_CA_STAT cs
654 WHERE cir.sup_cal_type = p_acad_cal_type AND
655 cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
656 cat.cal_type = cir.sub_cal_type AND
657 cat.closed_ind = 'N' AND
658 cat.s_cal_cat = cst_teaching AND
659 ci.cal_type = cir.sub_cal_type AND
660 ci.sequence_number = cir.sub_ci_sequence_number AND
661 ci.cal_type = cat.cal_type AND
662 ci.cal_status = cs.cal_status AND
663 cs.s_cal_status = cst_active;
664 CURSOR c_daiv(
665 cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
666 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
667 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
668
669 SELECT IGS_CA_GEN_001.calp_set_alias_value
670 (
671 absolute_val,
672 IGS_CA_GEN_002.cals_clc_dt_from_dai
673 (
674 ci_sequence_number,
675 CAL_TYPE,
676 DT_ALIAS,
677 sequence_number
678 )
679 ) alias_val
680 FROM IGS_CA_DA_INST dai
681 WHERE dai.cal_type = cp_cal_type AND
682 dai.ci_sequence_number = cp_ci_sequence_number AND
683 dai.dt_alias = cp_dt_alias;
684
685 CURSOR c_sca IS
686 SELECT sca.course_attempt_status,
687 sca.discontinued_dt,
688 sca.lapsed_dt,
689 sca.version_number
690 FROM IGS_EN_STDNT_PS_ATT sca
691 WHERE sca.person_id = p_person_id AND
692 sca.course_cd = p_course_cd;
693
694 CURSOR c_cop(
695 cp_coo_id IGS_PS_OFR_PAT.coo_id%TYPE,
696 cp_ci_sequence_number IGS_PS_OFR_PAT.ci_sequence_number%TYPE) IS
697
698 SELECT cop.offered_ind
699 FROM IGS_PS_OFR_PAT cop
700 WHERE cop.coo_id = cp_coo_id AND
701 cop.ci_sequence_number = cp_ci_sequence_number;
702
703 CURSOR c_sci IS
704 SELECT sci.end_dt
705 FROM IGS_EN_STDNT_PS_INTM sci,
706 IGS_EN_INTM_TYPES eit
707 WHERE sci.person_id = p_person_id AND
708 sci.course_cd = p_course_cd AND
709 eit.intermission_type(+) = sci.intermission_type AND
710 ((eit.appr_reqd_ind = 'Y' AND sci.approved = 'Y') OR (eit.appr_reqd_ind = 'N'))
711 AND sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
712 ORDER BY sci.start_dt;
713 -- replaced as pragma solution - view replaced by view query.
714 CURSOR c_acaiv IS
715 SELECT acaiv.offer_dt,
716 acaiv.ADM_CNDTNL_OFFER_STATUS,
717 acaiv.cndtnl_offer_must_be_stsfd_ind
718 FROM
719 (
720 SELECT
721 acai.person_id PERSON_ID,
722 aa.acad_cal_type ACAD_CAL_TYPE,
723 DECODE(acai.adm_cal_type, NULL, aa.acad_ci_sequence_number,
724 IGS_CA_GEN_001.calp_get_sup_inst (
725 aa.acad_cal_type,
726 acai.adm_cal_type,
727 acai.adm_ci_sequence_number))ACAD_CI_SEQUENCE_NUMBER,
728 acai.course_cd COURSE_CD,
729 acai.adm_outcome_status ADM_OUTCOME_STATUS,
730 acai.offer_dt OFFER_DT,
731 acai.adm_cndtnl_offer_status ADM_CNDTNL_OFFER_STATUS,
732 acai.cndtnl_offer_must_be_stsfd_ind CNDTNL_OFFER_MUST_BE_STSFD_IND,
733 acai.adm_offer_resp_status ADM_OFFER_RESP_STATUS
734 FROM
735 IGS_AD_PS_APPL_INST acai,
736 IGS_AD_APPL aa,
737 IGS_CA_INST ci,
738 IGS_AD_PS_APPL aca,
739 IGS_PS_VER crv
740 WHERE
741 aa.person_id = acai.person_id AND
742 aa.admission_appl_number = acai.admission_appl_number AND
743 ci.cal_type (+) = acai.deferred_adm_cal_type AND
744 ci.sequence_number (+) = acai.deferred_adm_ci_sequence_num AND
745 aca.person_id = acai.person_id AND
746 aca.admission_appl_number = acai.admission_appl_number AND
747 aca.nominated_course_cd = acai.nominated_course_cd AND
748 crv.course_cd = acai.course_cd AND
749 crv.version_number = acai.crv_version_number
750 ) acaiv
751
752 WHERE acaiv.person_id = p_person_id AND
753 acaiv.course_cd = p_course_cd AND
754 IGS_EN_GEN_002.enrp_get_acai_offer(acaiv.ADM_OUTCOME_STATUS,
755 acaiv.ADM_OFFER_RESP_STATUS) = 'Y' AND
756 acaiv.acad_cal_type = p_acad_cal_type AND
757 acaiv.acad_ci_sequence_number = p_acad_ci_sequence_number
758 ORDER BY acaiv.offer_dt DESC; -- use latest offer date
759
760 BEGIN
761 p_message_name := null;
762
763 -- Check that student is still alive.
764 OPEN c_person;
765 FETCH c_person INTO v_deceased_ind;
766 IF c_person%NOTFOUND THEN
767 CLOSE c_person;
768 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
769 RETURN FALSE;
770 END IF;
771 CLOSE c_person;
772
773 IF v_deceased_ind = 'Y' THEN
774 p_message_name := 'IGS_EN_STUD_INELIGIB_TO_ENROL';
775 RETURN FALSE;
776 END IF;
777
778 -- Select the start and end date for the nominated calendar
779 -- instance.
780 OPEN c_ci;
781 FETCH c_ci INTO v_instance_start_dt,
782 v_instance_end_dt;
783
784 IF c_ci%NOTFOUND THEN
785 CLOSE c_ci;
786 gv_extra_detail := ' -no IGS_CA_INST record was found';
787 p_message_name := 'IGS_EN_CAL_CONF_NOT_SET';
788 RETURN FALSE;
789 END IF;
790 CLOSE c_ci;
791
792 -- Check that the student is not encumbered for every teaching period
793 -- in the academic period.
794 OPEN c_s_gen_cal_conf;
795 FETCH c_s_gen_cal_conf INTO v_census_dt_alias;
796 IF c_s_gen_cal_conf%NOTFOUND THEN
797 CLOSE c_s_gen_cal_conf;
798 gv_extra_detail := ' -no IGS_GE_S_GEN_CAL_CON record was found';
799 p_message_name := 'IGS_EN_CAL_CONF_NOT_SET';
800 RETURN FALSE;
801 END IF;
802 CLOSE c_s_gen_cal_conf;
803
804 v_valid_enrolment := FALSE;
805 v_valid_pre_sysdate := FALSE;
806 v_valid_post_sysdate := FALSE;
807 v_excluded := FALSE;
808
809 FOR v_cal_type_instance_rec IN c_ct_ci LOOP
810
811 v_daiv_rec_found := FALSE;
812
813 FOR v_daiv_rec IN c_daiv(
814 v_cal_type_instance_rec.cal_type,
815 v_cal_type_instance_rec.sequence_number,
816 v_census_dt_alias) LOOP
817
818 v_daiv_rec_found := TRUE;
819
820 IF v_daiv_rec.alias_val BETWEEN v_instance_start_dt AND
821 v_instance_end_dt THEN
822
823 IF IGS_EN_VAL_ENCMB.enrp_val_excld_crs(
824 p_person_id,
825 p_course_cd,
826 v_daiv_rec.alias_val,
827 p_message_name) THEN
828
829 IF v_daiv_rec.alias_val >= SYSDATE THEN
830 v_valid_post_sysdate := TRUE;
831 ELSE
832 v_valid_pre_sysdate := TRUE;
833 END IF;
834 ELSE
835
836 v_excluded := TRUE;
837
838 END IF; -- IGS_EN_VAL_ENCMB.enrp_val_excld_crs
839 END IF;-- v_daiv_rec.alias_val
840 END LOOP; -- c_dai_v
841
842 IF NOT v_daiv_rec_found OR
843 NOT v_excluded OR
844 v_valid_post_sysdate THEN
845
846 v_valid_enrolment := TRUE;
847 EXIT;
848 END IF;
849
850 END LOOP; -- c_cal_type_instance
851
852 -- If the student is excluded from all teaching periods in the
853 -- academic period then ineligible.
854 IF NOT v_valid_enrolment THEN
855 p_message_name := 'IGS_EN_STUD_INELIBIBLE';
856 RETURN FALSE;
857 END IF;
858
859 -- Attempt to select existing student IGS_PS_COURSE attempt details.
860 OPEN c_sca;
861 FETCH c_sca INTO v_course_status,
862 v_discontinued_dt,
863 v_lapsed_dt,
864 v_sca_version_number;
865 CLOSE c_sca;
866
867 IF v_course_status = cst_unconfirm THEN
868 -- Validate confirmation of research IGS_PS_COURSE attempt
869 IF NOT IGS_EN_VAL_SCA.enrp_val_res_elgbl(
870 p_person_id,
871 p_course_cd,
872 v_sca_version_number,
873 p_message_name) THEN
874
875 RETURN FALSE;
876 END IF;
877 END IF;
878
879 IF v_course_status IS NULL OR
880 v_course_status = cst_unconfirm THEN
881
882 OPEN c_acaiv;
883 FETCH c_acaiv INTO
884 v_acaiv_offer_dt,
885 v_adm_cndtnl_offer_status,
886 v_cndtnl_off_must_be_stsfd_ind;
887
888 IF c_acaiv%NOTFOUND THEN
889 CLOSE c_acaiv;
890 p_message_name := 'IGS_EN_STUD_NOT_HAVE_CURR_AFF';
891 RETURN FALSE;
892 ELSE
893 CLOSE c_acaiv;
894
895 IF p_dflt_confirmed_course_ind = 'Y' THEN
896 -- Validate conditional offer
897 IF NOT IGS_EN_VAL_SCA.enrp_val_acai_cndtnl (
898 v_adm_cndtnl_offer_status,
899 v_cndtnl_off_must_be_stsfd_ind,
900 v_s_adm_cndtnl_offer_status,
901 p_message_name) THEN
902
903 RETURN FALSE;
904 END IF;
905 END IF;
906 END IF; -- c_acaiv%NOTFOUND
907 END IF; -- v_course_status = cst_unconfirm
908
909 -- Only load the latest offer date for current offers in the IGS_PS_COURSE
910 -- if IGS_PS_COURSE attempt status is 'DISCONTIN', 'LAPSED' or 'DELETED'
911 IF v_course_status IN (
912 cst_discontin,
913 cst_lapsed,
914 cst_deleted) THEN
915
916 OPEN c_acaiv;
917 FETCH c_acaiv INTO
918 v_acaiv_offer_dt,
919 v_adm_cndtnl_offer_status,
920 v_cndtnl_off_must_be_stsfd_ind;
921
922 IF c_acaiv%FOUND THEN
923 CLOSE c_acaiv;
924
925 -- Validate conditional offer
926 IF p_dflt_confirmed_course_ind = 'Y' AND
927 NOT IGS_EN_VAL_SCA.enrp_val_acai_cndtnl(
928 v_adm_cndtnl_offer_status,
929 v_cndtnl_off_must_be_stsfd_ind,
930 v_s_adm_cndtnl_offer_status,
931 p_message_name) THEN
932 RETURN FALSE;
933 END IF;
934 ELSE
935 CLOSE c_acaiv;
936 END IF;
937 END IF;
938
939 -- If IGS_PS_COURSE attempt is DISCONTIN then the admissions offer must have been
940 -- made after the discontinuation date.
941 IF v_course_status = cst_discontin THEN
942
943 IF v_acaiv_offer_dt IS NULL OR
944 v_acaiv_offer_dt <= v_discontinued_dt THEN
945 p_message_name := 'IGS_EN_STUD_INELIG_TO_RE_ENR';
946 RETURN FALSE;
947 END IF;
948
949 -- If IGS_PS_COURSE attempt is lapsed then the admissions offer must have been
950 -- made after the lapsed was placed.
951 ELSIF v_course_status = cst_lapsed THEN
952
953 IF v_acaiv_offer_dt IS NULL THEN
954 p_message_name := 'IGS_EN_INELIGBLE_DUE_TO_LAPSE';
955 RETURN FALSE;
956 ELSE
957 IF v_acaiv_offer_dt <= v_lapsed_dt THEN
958 p_message_name := 'IGS_EN_INELIGBLE_DUE_TO_LAPSE';
959 RETURN FALSE;
960 END IF;
961 END IF;
962
963 -- If the IGS_PS_COURSE attempt is DELETED then any current admissions offer will
964 -- permit enrolment.
965 ELSIF v_course_status = cst_deleted THEN
966
967 IF v_acaiv_offer_dt IS NULL THEN
968 p_message_name := 'IGS_EN_STUD_INELIGIBLE_RE_ENR';
969 RETURN FALSE;
970 END IF;
971
972 -- If IGS_PS_COURSE attempt status is INTERMIT then only eligible if returning within
973 -- the academic period.
974 ELSIF v_course_status = cst_intermit THEN
975 OPEN c_sci;
976 FETCH c_sci INTO v_intrmsn_end_dt;
977
978 IF c_sci%FOUND THEN
979 IF v_intrmsn_end_dt IS NULL OR
980 v_intrmsn_end_dt > v_instance_end_dt THEN
981
982 CLOSE c_sci;
983 p_message_name := 'IGS_EN_INTERM_DOES_NOT_END';
984 RETURN FALSE;
985 END IF;
986 END IF;
987 CLOSE c_sci;
988 END IF;
989
990 RETURN TRUE;
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 IF c_acaiv%ISOPEN THEN
995 CLOSE c_acaiv;
996 END IF;
997
998 IF c_sci%ISOPEN THEN
999 CLOSE c_sci;
1000 END IF;
1001
1002 IF c_ci%ISOPEN THEN
1003 CLOSE c_ci;
1004 END IF;
1005
1006 IF c_ct_ci%ISOPEN THEN
1007 CLOSE c_ct_ci;
1008 END IF;
1009
1010 IF c_sca%ISOPEN THEN
1011 CLOSE c_sca;
1012 END IF;
1013
1014 IF c_daiv%ISOPEN THEN
1015 CLOSE c_daiv;
1016 END IF;
1017
1018 IF c_person%ISOPEN THEN
1019 CLOSE c_person;
1020 END IF;
1021
1022 IF c_s_gen_cal_conf%ISOPEN THEN
1023 CLOSE c_s_gen_cal_conf;
1024 END IF;
1025
1026 RAISE;
1027 END;
1028 /*
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1032 App_Exception.Raise_Exception;
1033 */
1034 END enrp_get_sca_elgbl;
1035
1036
1037
1038
1039 Function Enrp_Get_Sca_Latt(
1040 p_person_id IN NUMBER ,
1041 p_course_cd IN VARCHAR2 ,
1042 p_load_cal_type IN VARCHAR2 ,
1043 p_load_sequence_number IN NUMBER )
1044 RETURN VARCHAR2 AS
1045
1046 BEGIN
1047 DECLARE
1048 v_dummy VARCHAR2(10);
1049 v_acad_cal_type VARCHAR2(10);
1050 v_acad_sequence_number NUMBER;
1051 v_acad_ci_start_dt DATE;
1052 v_acad_ci_end_dt DATE;
1053 v_message_name VARCHAR2(30);
1054 v_period_load NUMBER;
1055 v_period_credit_points NUMBER;
1056 v_attendance_type VARCHAR2(2);
1057 BEGIN
1058 -- Get the current attendance type for a student IGS_PS_COURSE attempt within a
1059 -- nominated load calendar instance.
1060 -- 1. Determine the academic calendar instance that the load calendar
1061 -- instance is within.
1062 v_dummy := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD(
1063 p_load_cal_type,
1064 p_load_sequence_number,
1065 v_acad_cal_type,
1066 v_acad_sequence_number,
1067 v_acad_ci_start_dt,
1068 v_acad_ci_end_dt,
1069 v_message_name);
1070 IF (v_acad_cal_type IS NULL) THEN
1071 RETURN NULL;
1072 END IF;
1073 -- 2. Call enrp_clc_load_total routine to get the load incurred within
1074 -- the current load period.
1075 v_period_load := IGS_EN_PRC_LOAD.enrp_clc_eftsu_total(
1076 p_person_id,
1077 p_course_cd,
1078 v_acad_cal_type,
1079 v_acad_sequence_number,
1080 p_load_cal_type,
1081 p_load_sequence_number,
1082 'Y',
1083 'Y',
1084 NULL,
1085 NULL,
1086 v_period_credit_points);
1087 -- 3. Call routine to determine the attendance type for the calculated
1088 -- load figure within the current load calendar.
1089 v_attendance_type := IGS_EN_PRC_LOAD.enrp_get_load_att(
1090 p_load_cal_type,
1091 v_period_load);
1092 RETURN v_attendance_type;
1093 END;
1094 /*
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1098 App_Exception.Raise_Exception;
1099 */
1100 END enrp_get_sca_latt;
1101
1102
1103 Function Enrp_Get_Sca_Perd(
1104 p_person_id IN NUMBER ,
1105 p_course_cd IN VARCHAR2 )
1106 RETURN VARCHAR2 AS
1107 gv_other_detail VARCHAR2(255);
1108 BEGIN
1109 DECLARE
1110 v_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
1111 v_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
1112 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
1113 v_academic_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
1114 v_academic_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
1115 v_academic_ci_start_dt IGS_EN_SU_ATTEMPT.ci_start_dt%TYPE;
1116 v_academic_ci_end_dt IGS_EN_SU_ATTEMPT.ci_end_dt%TYPE;
1117 v_message_name VARCHAR2(30);
1118 CURSOR c_sua_ci (
1119 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1120 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE)IS
1121 SELECT sua.cal_type,
1122 sua.ci_sequence_number,
1123 ci.alternate_code
1124 FROM IGS_EN_SU_ATTEMPT sua,
1125 IGS_CA_INST ci
1126 WHERE sua.person_id = cp_person_id AND
1127 sua.course_cd = cp_course_cd AND
1128 sua.cal_type = ci.cal_type AND
1129 sua.ci_sequence_number = ci.sequence_number AND
1130 (sua.unit_attempt_status = 'ENROLLED' OR
1131 sua.unit_attempt_status = 'COMPLETED')
1132 ORDER BY ci.start_dt;
1133 BEGIN
1134 -- Get the commencement period of a student IGS_PS_COURSE attempt. This is the first
1135 -- teaching period with a ENROLLED or COMPLETED student IGS_PS_UNIT attempt.
1136 -- The routine is expected to be mostly used by reporting as the result
1137 -- is a concatenated string of <teaching alt code>/<academic alternate code>.
1138 -- IGS_GE_NOTE: may need to be expanded to include IGS_PS_UNIT attempts which discontinued
1139 -- late once assessments is on board.
1140 -- 1. Find earliest student IGS_PS_UNIT attempt record matching the criteria.
1141 OPEN c_sua_ci(
1142 p_person_id,
1143 p_course_cd);
1144 FETCH c_sua_ci INTO v_cal_type,
1145 v_ci_sequence_number,
1146 v_alternate_code;
1147 IF (c_sua_ci%NOTFOUND) THEN
1148 CLOSE c_sua_ci;
1149 RETURN NULL;
1150 END IF;
1151 CLOSE c_sua_ci;
1152 v_academic_cal_type := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD(
1153 v_cal_type,
1154 v_ci_sequence_number,
1155 v_academic_cal_type,
1156 v_academic_ci_sequence_number,
1157 v_academic_ci_start_dt,
1158 v_academic_ci_end_dt,
1159 v_message_name);
1160 RETURN (v_alternate_code || ',' || v_academic_cal_type);
1161 END;
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1165 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_006.enrp_get_sca_perd');
1166 IGS_GE_MSG_STACK.ADD;
1167 App_Exception.Raise_Exception;
1168 END enrp_get_sca_perd;
1169
1170
1171 Function Enrp_Get_Sca_Status(
1172 p_person_id IN NUMBER ,
1173 p_course_cd IN VARCHAR2 ,
1174 p_course_attempt_status IN VARCHAR2 ,
1175 p_student_confirmed_ind IN VARCHAR2 DEFAULT 'N',
1176 p_discontinued_dt IN DATE ,
1177 p_lapsed_dt IN DATE ,
1178 p_course_rqrmnt_complete_ind IN VARCHAR2 DEFAULT 'N',
1179 p_logical_delete_dt IN DATE )
1180 RETURN VARCHAR2 AS
1181 /*
1182 || Created By :
1183 || Created On :
1184 || Purpose : This procedure process the Application
1185 || Known limitations, enhancements or remarks :
1186 || Change History :
1187 || Who When What
1188 || pkpatel 09-SEP-2001 Bug no.1960126 :For Academic Record Maintenance
1189 || Modified the defination of Cursor 'c_sci' to include
1190 || the logic for INtermission Type Approval
1191 || (reverse chronological order - newest change first)
1192 -- rnirwani 13-Sep-2004 changed cursor c_intmsn_details to not consider logically deleted records and
1193 -- also to avoid un-approved intermission records. Bug# 3885804
1194 -- smaddali 10-mar-06 Modified cursor c_sci for build EN324 - bug#5091858
1195 */
1196 BEGIN -- enrp_get_sca_status
1197 -- Get the IGS_PS_COURSE attempt status of a nominated student IGS_PS_COURSE attempt.
1198 -- This routine checks attributes of the students enrolment to ascertain
1199 -- what their enrolled student IGS_PS_COURSE attempt status should be.
1200 DECLARE
1201 cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
1202 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1203 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1204 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1205 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1206 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
1207 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1208 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
1209 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
1210 v_student_confirmed_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
1211 v_discontinued_dt IGS_EN_STDNT_PS_ATT.discontinued_dt%TYPE;
1212 v_lapsed_dt IGS_EN_STDNT_PS_ATT.lapsed_dt%TYPE;
1213 v_course_rqrmnt_complete_ind IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind%TYPE;
1214 v_logical_delete_dt IGS_EN_STDNT_PS_ATT.logical_delete_dt%TYPE;
1215 v_cal_type IGS_CA_INST.cal_type%TYPE;
1216 v_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
1217 v_enr_form_due_dt IGS_AS_SC_ATMPT_ENR.enr_form_due_dt%TYPE;
1218 v_exists_flag VARCHAR2(1);
1219 CURSOR c_sca IS
1220 SELECT sca.course_attempt_status,
1221 sca.student_confirmed_ind,
1222 sca.discontinued_dt,
1223 sca.lapsed_dt,
1224 sca.course_rqrmnt_complete_ind,
1225 sca.logical_delete_dt
1226 FROM IGS_EN_STDNT_PS_ATT sca
1227 WHERE sca.person_id = p_person_id AND
1228 sca.course_cd = p_course_cd;
1229 CURSOR c_sua IS
1230 SELECT 'x'
1231 FROM sys.dual
1232 WHERE EXISTS (
1233 SELECT 'x'
1234 FROM IGS_EN_SU_ATTEMPT sua
1235 WHERE sua.person_id = p_person_id AND
1236 sua.course_cd = p_course_cd AND
1237 sua.unit_attempt_status = cst_enrolled AND
1238 sua.ci_start_dt <= SYSDATE);
1239
1240 -- smaddali Modified cursor c_sci for build EN324 - bug#5091858
1241 CURSOR c_sci IS
1242 SELECT 'X'
1243 FROM IGS_EN_STDNT_PS_INTM sci,
1244 IGS_EN_INTM_TYPES eit
1245 WHERE sci.person_id = p_person_id AND
1246 sci.course_cd = p_course_cd AND
1247 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
1248 eit.intermission_type = sci.intermission_type AND
1249 ((eit.appr_reqd_ind = 'Y' AND sci.approved = 'Y') OR
1250 (eit.appr_reqd_ind = 'N')) AND
1251 sci.start_dt <= trunc(SYSDATE) AND
1252 ( sci.end_dt >= trunc(SYSDATE) OR
1253 ( sci.end_dt < trunc(SYSDATE) AND
1254 sci.cond_return_flag = 'Y' AND
1255 EXISTS (select 'x' from igs_en_spi_rconds rc
1256 where sci.person_id = rc.person_id
1257 and sci.course_cd = rc.course_cd
1258 and sci.start_dt = rc.start_dt
1259 and sci.logical_delete_date = rc.logical_delete_date
1260 and status_code IN ('FAILED','PENDING')
1261 )
1262 )
1263 );
1264 CURSOR c_scae IS
1265 SELECT ci.cal_type,
1266 scae.ci_sequence_number,
1267 scae.enr_form_due_dt
1268 FROM IGS_AS_SC_ATMPT_ENR scae,
1269 IGS_CA_INST ci
1270 WHERE scae.person_id = p_person_id AND
1271 scae.course_cd = p_course_cd AND
1272 ci.cal_type = scae.cal_type AND
1273 ci.sequence_number = scae.ci_sequence_number
1274 ORDER BY ci.end_dt DESC;
1275 CURSOR c_secc (
1276 cp_cal_type IGS_CA_INST.cal_type%TYPE,
1277 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE ) IS
1278 SELECT 'x'
1279 FROM sys.dual
1280 WHERE EXISTS (
1281 SELECT 'x'
1282 FROM IGS_EN_CAL_CONF secc,
1283 IGS_CA_DA_INST_V daiv
1284 WHERE secc.s_control_num = 1 AND
1285 secc.enr_form_due_dt_alias IS NOT NULL AND
1286 daiv.cal_type = cp_cal_type AND
1287 daiv.ci_sequence_number = cp_ci_sequence_number AND
1288 daiv.dt_alias = secc.enr_form_due_dt_alias AND
1289 daiv.alias_val >= SYSDATE);
1290 BEGIN
1291 -- If the values have not been passed in, load them.
1292 IF p_course_attempt_status IS NULL THEN
1293 OPEN c_sca;
1294 FETCH c_sca INTO v_course_attempt_status,
1295 v_student_confirmed_ind,
1296 v_discontinued_dt,
1297 v_lapsed_dt,
1298 v_course_rqrmnt_complete_ind,
1299 v_logical_delete_dt;
1300 IF (c_sca%NOTFOUND) THEN
1301 CLOSE c_sca;
1302 RETURN NULL;
1303 END IF;
1304 CLOSE c_sca;
1305 ELSE
1306 -- Use parameters instead of selected student IGS_PS_COURSE attempt
1307 -- information to set v_ values.
1308 v_course_attempt_status := p_course_attempt_status;
1309 v_student_confirmed_ind := p_student_confirmed_ind;
1310 v_discontinued_dt := p_discontinued_dt;
1311 v_lapsed_dt := p_lapsed_dt;
1312 v_course_rqrmnt_complete_ind := p_course_rqrmnt_complete_ind;
1313 v_logical_delete_dt := p_logical_delete_dt;
1314 END IF;
1315 -- If logical delete dt is not null then return deleted
1316 IF v_logical_delete_dt IS NOT NULL THEN
1317 RETURN cst_deleted;
1318 END IF;
1319 -- If IGS_PS_COURSE attempt is unconfirmed then return unconfirm
1320 IF v_student_confirmed_ind = 'N' THEN
1321 RETURN cst_unconfirm;
1322 END IF;
1323 -- If there is a current discontinuation date then return discontin
1324 IF v_discontinued_dt IS NOT NULL AND
1325 v_discontinued_dt <= SYSDATE THEN
1326 RETURN cst_discontin;
1327 END IF;
1328 -- If there is a current student IGS_PS_COURSE lapse then return lapsed
1329 IF (v_lapsed_dt IS NOT NULL) THEN
1330 OPEN c_sua;
1331 FETCH c_sua INTO v_exists_flag;
1332 IF (c_sua%NOTFOUND) THEN
1333 CLOSE c_sua;
1334 RETURN cst_lapsed;
1335 END IF;
1336 CLOSE c_sua;
1337 END IF;
1338 -- If there is a current intermission then return intermit
1339 OPEN c_sci;
1340 FETCH c_sci INTO v_exists_flag;
1341 IF (c_sci%FOUND) THEN
1342 CLOSE c_sci;
1343 RETURN cst_intermit;
1344 END IF;
1345 CLOSE c_sci;
1346 -- If there are any enrolled IGS_PS_UNIT attempts within the IGS_PS_COURSE then return
1347 -- enrolled
1348 OPEN c_sua;
1349 FETCH c_sua INTO v_exists_flag;
1350 IF (c_sua%FOUND) THEN
1351 CLOSE c_sua;
1352 RETURN cst_enrolled;
1353 END IF;
1354 CLOSE c_sua;
1355 -- If the IGS_PS_COURSE requirements are complete then return completed
1356 IF v_course_rqrmnt_complete_ind = 'Y' THEN
1357 RETURN cst_completed;
1358 END IF;
1359 -- If the student has not yet reached their enrolment form due date
1360 -- then return enrolled, else they are inactive. This checks both the student
1361 -- IGS_PS_COURSE attempt enrolment due date (which is an override) and the values
1362 -- in the pre-enrolled enrolment periods.
1363 OPEN c_scae;
1364 FETCH c_scae INTO v_cal_type,
1365 v_ci_sequence_number,
1366 v_enr_form_due_dt;
1367 IF (c_scae%NOTFOUND) THEN
1368 CLOSE c_scae;
1369 RETURN cst_inactive;
1370 END IF;
1371 CLOSE c_scae;
1372 -- If records found, using the last record (ie. The latest record)
1373 IF v_enr_form_due_dt IS NOT NULL THEN
1374 IF v_enr_form_due_dt > SYSDATE THEN
1375 -- commented for bug 1510921
1376 -- RETURN cst_enrolled;
1377 RETURN cst_inactive;
1378 ELSE
1379 RETURN cst_inactive;
1380 END IF;
1381 ELSE
1382 -- IGS_GE_NOTE: This query is designed to return no records if the
1383 -- secc.enr_form_due_dt_alias is set to NULL, which means the functionality
1384 -- is not enabled.
1385 OPEN c_secc( v_cal_type, v_ci_sequence_number );
1386 FETCH c_secc INTO v_exists_flag;
1387 IF (c_secc%NOTFOUND) THEN
1388 CLOSE c_secc;
1389 RETURN cst_inactive;
1390 ELSE
1391 CLOSE c_secc;
1392 -- commented for bug 1510921
1393 --RETURN cst_enrolled;
1394 RETURN cst_inactive;
1395 END IF;
1396 END IF;
1397
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 IF (c_sca%ISOPEN) THEN
1401 CLOSE c_sca;
1402 END IF;
1403 IF (c_sua%ISOPEN) THEN
1404 CLOSE c_sua;
1405 END IF;
1406 IF (c_sci%ISOPEN) THEN
1407 CLOSE c_sci;
1408 END IF;
1409 IF (c_scae%ISOPEN) THEN
1410 CLOSE c_scae;
1411 END IF;
1412 IF (c_secc%ISOPEN) THEN
1413 CLOSE c_secc;
1414 END IF;
1415 END;
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1419 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_006.enrp_get_sca_status');
1420 IGS_GE_MSG_STACK.ADD;
1421 App_Exception.Raise_Exception;
1422 END enrp_get_sca_status;
1423
1424
1425 Function Enrp_Get_Sca_Trnsfr(
1426 p_person_id IN NUMBER ,
1427 p_course_cd IN VARCHAR2 ,
1428 p_message_name OUT NOCOPY VARCHAR2)
1429 RETURN BOOLEAN AS
1430
1431 BEGIN -- enrp_get_sca_trnsfr
1432 -- This module determines if the student IGS_PS_COURSE attenmpt has been transferred
1433 --This is determined by the existence of one or nore student IGS_PS_COURSE transfer
1434 -- details where the latest is a transfer 'from' the IGS_PS_COURSE attempt
1435 DECLARE
1436 v_trnsfr_crs_cd IGS_PS_STDNT_TRN.transfer_course_cd%TYPE;
1437 CURSOR c_sct IS
1438 SELECT sct.transfer_course_cd
1439 FROM IGS_PS_STDNT_TRN sct
1440 WHERE sct.person_id = p_person_id AND
1441 (sct.course_cd = p_course_cd OR
1442 sct.transfer_course_cd = p_course_cd)
1443 ORDER BY transfer_dt desc;
1444 BEGIN
1445 p_message_name := null;
1446 -- Determine that if student IGS_PS_COURSE transfer details exist, then the last
1447 -- was a transfer from the IGS_PS_COURSE attempt
1448 OPEN c_sct;
1449 FETCH c_sct INTO v_trnsfr_crs_cd;
1450 IF (c_sct%NOTFOUND) THEN
1451 CLOSE c_sct;
1452 RETURN FALSE;
1453 END IF;
1454 CLOSE c_sct;
1455 IF (v_trnsfr_crs_cd <> p_course_cd) THEN
1456 RETURN FALSE;
1457 END IF;
1458 RETURN TRUE;
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461 IF (c_sct%ISOPEN) THEN
1462 CLOSE c_sct;
1463 END IF;
1464 RAISE;
1465 END;
1466 /*
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1470 App_Exception.Raise_Exception;
1471 */
1472 END enrp_get_sca_trnsfr;
1473
1474 END IGS_EN_GEN_006;
[Home] [Help]