1 PACKAGE BODY IGS_EN_VAL_SUA AS
2 /* $Header: IGSEN68B.pls 120.19 2006/06/05 10:12:58 smaddali ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 29-AUG-2001 Bug No. 1956374 .The Function genp_val_sdtt_sess removed
7 --smadathi 29-AUG-2001 Bug No. 1956374 .The Function genp_val_staff_prsn removed
8 --nalkumar 04-May-2002 Modified the enrp_val_sua_delete procedure as per the Bug# 2356997.
9 --Nishikant 13-may-2002 Bug#2364216. A small modification in the function enrp_val_sua_enr_dt got.
10 --prraj 16-May-2002 Changed condition that checks for credit points not syncing with
11 -- credit point min/max/increment in FUNCTION enrp_val_sua_ovrd_cp as part of (Bug# 2375757)
12 --Sudhir 23-MAY-2002 Changed the message from IGS_EN_ADMIN_UNITST_NOTVALID to IGS_SS_EN_INVLD_ADMIN_UNITST
13 -- and the req. logic for procedure enrp_val_discont_aus.Also Added out NOCOPY parameter for
14 -- procedure enrp_val_discont_aus.
15 --svenkata 20-Nov-2002 Added a new parameter p_legacy to selectively carry out NOCOPY validations for legacy.
16 -- The following routines have been modified : enrp_val_sua_uoo , enrp_val_sua_enr_dt,
17 -- enrp_val_sua_advstnd,resp_val_sua_cnfrm,enrp_val_sua_discont,enrp_val_discont_aus
18 -- amuthu 20-JAn-2003 Added the no_assessment_ind column to the function enrp_val_sua_ovrd_cp
19 -- if the value of this column is 'Y' and the Acheivable CP is zero then
20 -- do not validate the acheivalbe CP.
21 -- amuthu 04-FEB-2003 Modified the function enrp_get_sua_ausg to consider only audit grades
22 -- for audit units and only non-audit grades for non-audit unit attempts
23 -- sarakshi 24-Feb-2003 Enh#2797116,modified cursor c_coo in function's enrp_val_coo_loc and enrp_val_coo_mode
24 -- to add delete_flag check in the where clause
25 -- myoganat 23-MAY-2003 Modified the cursor C_SUA_UV in procedure ENRP_VAL_COO_CROSS
26 -- as part of Bug #2855870
27 -- svenkata 3-Jun-2003 The function ENRP_VAL_COO_CROSS has been removed. The same functionality has been implemented as
28 -- cross-element restrictions of Validations. Bug# 2829272
29 --svanukur 26-jun-2003 checking if discontinued date is set for dropped unit attempt status , then the validations
30 -- for discotinued unit attempts return true from functions enrp_val_discont_aus and
31 -- enrp_val_sua_discont as part of bug 2898213.
32 -- ptandon 04-Jul-2003 Modified the function enrp_val_discont_aus to return list of valid administrative unit statuses
33 -- for discontinuation which was initially returning NULL as part of Bug# 3036433
34 -- amuthu 07-JUL-2003 Added logic to check if the program attempt status is not Unconfirm/discontin
35 -- when the unit attempt status is enrolled or invalid.
36 -- amuthu 04-AUG-2003 Bypassed the discontinuation validation for a dropped unit attempt in enrp_val_sua_discont
37 -- rvivekan 09-sep-2003 Modified the behaviour of repeatable_ind column in igs_ps_unit_ver table. PSP integration build #3052433
38 --svanukur 18-oct-2003 created procedures enr_sub_units and drop_sub_units as part of placements build 3052438
39 --rvivekan 17-nov-2003 Bug3264064. Changed the datatype of variables holding the concatenated administrative unit status list
40 -- to varchar(2000) in enrp_val_discont_aus
41 --ptandon 29-Dec-2003 Removed the exception handling sections of enrp_val_sua_cnfrm, enrp_val_sua_insert,
42 -- enrp_val_sua_intrmt, resp_val_sua_cnfrm, enrp_val_sua_excld, enrp_val_sua_advstnd,
43 -- enrp_val_coo_loc, enrp_val_coo_mode, enrp_val_sua_enr_dt, enrp_val_sua_ci and enrp_val_sua_dupl
44 -- so that the correct error message is displayed instead of the unhandled exception message.
45 -- Bug# 3328268.
46 --smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_unit_class to select active (not closed) unit classes.
47 -- rnirwani 13-Sep-2004 changed cursor c_sci_details (ENRP_VAL_SUA_INTRMT) to not consider logically deleted records and
48 -- also to avoid un-approved intermission records. Bug# 3885804
49 -- ckasu 17-Nov-2004 modfied the ENRP_VAL_SUA_CNFRM_P procedure inorder to consider enrollment Category setup
50 -- for checking the Forced location, attendance mode as apart of Program
51 -- Transfer Build#4000939
52 -- amuthu 26-NOV-2004 modified logic in two methods to allow the insertion of completed and dicontinued unit attempts
53 -- amuthu 03-DEC-2004 On enrolling user was getting invalid cursor, fixed the issue
54 -- ckasu 21-Dec-2004 modified enrp_val_sua_update procedure inorder to Transfer Unit outcomes in ABA Transfer as a part
55 -- of bug# 4080883
56 -- sgurusam 17-Jun-2005 Modified to pass aditional parameter p_calling_obj = 'JOB' in the calls to
57 -- igs_ss_en_wrappers.insert_into_enr_worksheet, igs_en_elgbl_unit.eval_unit_forced_location,
58 -- and igs_en_elgbl_unit.eval_unit_forced_mode.
59 -- bdeviset 24-Nov_2005 Added proc validate_mus for bug#4676023
60 -- ckasu 28-NOV-2005 modified v_message_name <> NULL to v_message_name IS NOT NULL in enrp_val_sua_dupl Function
61 -- as a part of bug #4666102
62 -- smaddali 10-apr-06 Modified ENRP_VAL_SUA_INTRMT for bug#5091858 BUILD EN324
63 --ckasu 02-May-2006 Modified as a part of bug#5191592
64 -------------------------------------------------------------------------------------------
65 -- To validate the confirmation of a research unit attempt.
66 FUNCTION RESP_VAL_SUA_CNFRM(
67 p_person_id IN NUMBER ,
68 p_course_cd IN VARCHAR2 ,
69 p_unit_cd IN VARCHAR2 ,
70 p_version_number IN NUMBER ,
71 p_cal_type IN VARCHAR2 ,
72 p_ci_sequence_number IN NUMBER ,
73 p_message_name OUT NOCOPY VARCHAR2 ,
74 p_legacy IN VARCHAR2 )
75 RETURN boolean AS
76
77 BEGIN -- resp_val_sua_cnfrm
78 -- Validate a research student unit attempt being added to a research student
79 --(note: this routine is checking confirmation point ?
80 -- there is another routine to check commit point processing),
81 -- checking for :
82 -- * That the student has a candidature record
83 -- * That the student has supervisors to cover the entire duration
84 -- of the teaching period
85 DECLARE
86 v_rsup_record_not_found BOOLEAN;
87 v_teach_days NUMBER;
88 v_teach_end_dt DATE;
89 v_teach_start_dt DATE;
90 v_check_dt DATE;
91 CURSOR c_uv IS
92 SELECT 'x'
93 FROM IGS_PS_UNIT_VER uv
94 WHERE uv.unit_cd = p_unit_cd AND
95 uv.version_number = p_version_number AND
96 uv.research_unit_ind <> 'N';
97 v_uv_exists VARCHAR2(1);
98 CURSOR c_ca IS
99 SELECT ca.sequence_number
100 FROM IGS_RE_CANDIDATURE ca
101 WHERE ca.person_id = p_person_id AND
102 (ca.sca_course_cd IS NOT NULL AND
103 ca.sca_course_cd = p_course_cd);
104 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
105 CURSOR c_rsup(
106 cp_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE) IS
107 SELECT rsup.start_dt
108 FROM IGS_RE_SPRVSR rsup
109 WHERE rsup.ca_person_id = p_person_id AND
110 rsup.ca_sequence_number = cp_ca_sequence_number AND
111 rsup.start_dt <= v_teach_end_dt AND
112 (rsup.end_dt IS NULL OR
113 rsup.end_dt >= v_teach_start_dt)
114 ORDER BY rsup.start_dt ASC;
115
116 --tray
117 CURSOR c_com_dt(p_person_id IGS_EN_STDNT_PS_ATT_ALL.Person_id%TYPE,
118 p_course_cd IGS_EN_STDNT_PS_ATT_ALL.Course_cd%TYPE)IS
119 SELECT commencement_dt from igs_en_stdnt_ps_att_all
120 WHERE person_id = p_person_id
121 AND course_cd = p_course_cd;
122 v_commencement_dt igs_en_stdnt_ps_att_all.commencement_dt%TYPE;
123
124 FUNCTION respl_check_percentage(
125 pl_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE,
126 pl_check_dt IGS_RE_SPRVSR.start_dt%TYPE)
127 RETURN BOOLEAN AS
128
129 BEGIN
130 -- This is a local function to check the total percentages of
131 -- funding and supervision for the research_supervior
132 DECLARE
133 vl_total_supervision NUMBER;
134 vl_total_funding NUMBER;
135 CURSOR c_rsup_chk_pct IS
136 SELECT SUM(NVL(rsup.supervision_percentage, 0)),
137 SUM(NVL(rsup.funding_percentage, 0))
138 FROM IGS_RE_SPRVSR rsup
139 WHERE rsup.ca_person_id = p_person_id AND
140 rsup.ca_sequence_number = pl_ca_sequence_number AND
141 rsup.start_dt <= pl_check_dt AND
142 (rsup.end_dt IS NULL OR
143 rsup.end_dt >= pl_check_dt);
144 CURSOR c_rsup_per_type IS
145 SELECT IGS_EN_GEN_003.Get_Staff_Ind( rsup.person_id) person_type
146 FROM IGS_RE_SPRVSR rsup
147 WHERE rsup.ca_person_id = p_person_id AND
148 rsup.ca_sequence_number = pl_ca_sequence_number AND
149 rsup.start_dt <= pl_check_dt AND
150 (rsup.end_dt IS NULL OR
151 rsup.end_dt >= pl_check_dt);
152 l_person_type c_rsup_per_type%ROWTYPE ;
153 BEGIN
154 OPEN c_rsup_chk_pct;
155 FETCH c_rsup_chk_pct INTO vl_total_supervision,
156 vl_total_funding;
157 IF c_rsup_chk_pct%NOTFOUND OR
158 vl_total_supervision < 100 THEN
159 CLOSE c_rsup_chk_pct;
160 p_message_name := 'IGS_RE_CAND_DOES_NOT_HAVE_SUP';
161 RETURN FALSE;
162 END IF;
163 IF NVL(vl_total_funding,0) < 100 THEN
164 FOR l_person_type IN c_rsup_per_type LOOP
165 IF l_person_type.person_type = 'Y' THEN
166 p_message_name := 'IGS_RE_CAND_DOES_NOT_HAVE_SUP';
167 RETURN FALSE;
168 END IF;
169 END LOOP;
170 END IF;
171 CLOSE c_rsup_chk_pct;
172 RETURN TRUE;
173 EXCEPTION
174 WHEN OTHERS THEN
175 IF c_rsup_chk_pct%ISOPEN THEN
176 CLOSE c_rsup_chk_pct;
177 END IF;
178 RAISE;
179 END;
180 END respl_check_percentage;
181 BEGIN
182 -- Set the default message number
183 p_message_name := null;
184 OPEN c_uv;
185 FETCH c_uv INTO v_uv_exists;
186 IF c_uv%NOTFOUND THEN
187 CLOSE c_uv;
188 -- invalid parameters
189 RETURN TRUE;
190 END IF;
191 CLOSE c_uv;
192 --1. Check that student has a candidature matching the course attempt.
193 OPEN c_ca;
194 FETCH c_ca INTO v_ca_sequence_number;
195 IF c_ca%NOTFOUND THEN
196
197 CLOSE c_ca;
198 p_message_name := 'IGS_RE_MUST_HAVE_RES_CANDIDAT';
199 IF p_legacy <> 'Y' THEN
200 RETURN FALSE;
201 ELSE
202 Fnd_Message.Set_Name('IGS', p_message_name );
203 FND_MSG_PUB.ADD;
204 END IF;
205 ELSE
206 CLOSE c_ca;
207 END IF;
208
209 IF p_cal_type IS NULL OR p_ci_sequence_number IS NULL THEN
210 -- Remaining validations don't apply without calendar details.
211 RETURN TRUE;
212 END IF;
213 v_teach_days := IGS_RE_GEN_002.RESP_GET_TEACH_DAYS(
214 p_cal_type,
215 p_ci_sequence_number,
216 v_teach_start_dt,
217 v_teach_end_dt );
218 IF ( v_teach_days = 0 AND p_legacy <> 'Y' )THEN
219 p_message_name := 'IGS_RE_TEACH_PER_NOT_SETUP';
220 RETURN TRUE; -- Warning Only
221 END IF;
222
223
224 --2. Check that student has 100% supervision to cover the teaching period.
225 OPEN c_com_dt (p_person_id,p_course_cd);
226 FETCH c_com_dt INTO v_commencement_dt;
227 v_rsup_record_not_found := TRUE;
228 FOR v_rsup_rec IN c_rsup(
229 v_ca_sequence_number) LOOP
230 --IF first record
231 IF c_rsup%ROWCOUNT = 1 THEN
232 v_rsup_record_not_found := FALSE;
233
234 IF c_com_dt%NOTFOUND THEN
235 IF p_legacy <> 'Y' THEN
236 p_message_name := 'IGS_RE_COM_DT_UNAVAIL';
237 CLOSE c_com_dt;
238 RETURN FALSE;
239 END IF;
240 ELSE
241 CLOSE c_com_dt;
242 IF v_commencement_dt BETWEEN v_teach_start_dt AND v_teach_end_dt THEN
243 IF v_commencement_dt < v_rsup_rec.start_dt THEN
244 p_message_name := 'IGS_RE_COM_DT_LESS_SUP_ST_DT';
245 IF p_legacy <> 'Y' THEN
246 RETURN FALSE;
247 ELSE
248 Fnd_Message.Set_Name('IGS', p_message_name );
249 FND_MSG_PUB.ADD;
250 END IF ;
251 END IF;
252 ELSE
253 IF v_rsup_rec.start_dt > v_teach_start_dt THEN
254 p_message_name := 'IGS_RE_CAND_DOES_NOT_HAVE_SUP';
255 IF p_legacy <> 'Y' THEN
256 RETURN FALSE;
257 ELSE
258 Fnd_Message.Set_Name('IGS', p_message_name );
259 FND_MSG_PUB.ADD;
260 END IF ;
261 ELSE
262 v_check_dt := v_rsup_rec.start_dt;
263 --Execute <Check Percentages>
264 IF respl_check_percentage(
265 v_ca_sequence_number,
266 v_check_dt) = FALSE THEN
267 IF p_legacy <> 'Y' THEN
268 RETURN FALSE;
269 ELSE
270 Fnd_Message.Set_Name('IGS', p_message_name );
271 FND_MSG_PUB.ADD;
272 END IF ;
273 END IF;
274 END IF;
275
276 END IF;
277
278 END IF;
279 ELSE --if multiple records
280 v_check_dt := v_rsup_rec.start_dt;
281 --Execute <Check Percentages>
282 IF respl_check_percentage(
283 v_ca_sequence_number,
284 v_check_dt) = FALSE THEN
285 IF p_legacy <> 'Y' THEN
286 RETURN FALSE;
287 ELSE
288 Fnd_Message.Set_Name('IGS', p_message_name );
289 FND_MSG_PUB.ADD;
290 END IF ;
291 END IF;
292 END IF;
293 END LOOP;
294 IF v_rsup_record_not_found THEN
295 --No supervisors found - error
296 p_message_name := 'IGS_RE_CAND_DOES_NOT_HAVE_SUP';
297 IF p_legacy <> 'Y' THEN
298 RETURN FALSE;
299 ELSE
300 Fnd_Message.Set_Name('IGS', p_message_name );
301 FND_MSG_PUB.ADD;
302 END IF ;
303 END IF;
304 v_check_dt := v_teach_end_dt;
305 --Execute <Check Percentages>
306 IF respl_check_percentage(
307 v_ca_sequence_number,
308 v_check_dt) = FALSE THEN
309 IF p_legacy <> 'Y' THEN
310 RETURN FALSE;
311 ELSE
312 Fnd_Message.Set_Name('IGS', p_message_name );
313 FND_MSG_PUB.ADD;
314 END IF ;
315 END IF;
316 RETURN TRUE;
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF c_uv%ISOPEN THEN
320 CLOSE c_uv;
321 END IF;
322 IF c_ca%ISOPEN THEN
323 CLOSE c_ca;
324 END IF;
325 RAISE;
326 END;
327 END RESP_VAL_SUA_CNFRM ;
328
329 --
330 -- To validate all research units in an academic period
331 FUNCTION RESP_VAL_SUA_ALL(
332 p_person_id IN NUMBER ,
333 p_course_cd IN VARCHAR2 ,
334 p_acad_cal_type IN VARCHAR2 ,
335 p_acad_ci_sequence_number IN NUMBER ,
336 p_message_name OUT NOCOPY varchar2 )
337 RETURN boolean AS
338 -------------------------------------------------------------------------------------------
339 -- resp_val_sua_all
340 -- Validate research unit attempts for a research student course attempt,
341 -- checking for:
342 -- * That a student doesn't have multiple research units enrolled in a
343 -- single teaching period.
344 --Change History:
345 --Who When What
346 --kkillams 28-04-2003 Modified the c_sua cursor where clause due to change in the pk of
347 -- student unit attempt w.r.t. bug number 2829262
348 --stutta 06-Mar-2006 Modified cursor c_sua and added c_acad for perf bug #5025850
349 -------------------------------------------------------------------------------------------
350 BEGIN
351 DECLARE
352 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
353 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
354 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
355 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
356 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
357 cst_load CONSTANT VARCHAR2(10) := 'LOAD';
358
359 CURSOR c_ca IS
360 SELECT 'x'
361 FROM IGS_RE_CANDIDATURE ca
362 WHERE ca.person_id = p_person_id AND
363 (ca.sca_course_cd IS NULL OR
364 ca.sca_course_cd = p_course_cd);
365 v_ca_exists VARCHAR2(1);
366
367
368 CURSOR c_acad IS
369 SELECT DISTINCT cir.sup_cal_type acad_cal_type, cir.sup_ci_sequence_number acad_ci_sequence_number
370 FROM igs_ca_inst_rel cir, igs_en_su_attempt_all sua
371 WHERE sua.cal_type = cir.sub_cal_type
372 AND sua.ci_sequence_number = cir.sub_ci_sequence_number
373 AND sua.person_id = p_person_id
374 AND sua.course_cd = p_course_cd
375 AND cir.sup_cal_type = NVL(p_acad_cal_type,cir.sup_cal_type)
376 AND cir.sup_ci_sequence_number = NVL(p_acad_ci_sequence_number,cir.sup_ci_sequence_number);
377
378
379 CURSOR c_sua(cp_acad_cal_type igs_ca_inst.cal_type%TYPE,cp_acad_seq_num igs_ca_inst.sequence_number%TYPE) IS
380 SELECT
381 sua2.cal_type sua_cal_type,
382 sua2.ci_sequence_number sua_ci_sequence_number,
383 sua2.discontinued_dt,
384 sua2.administrative_unit_status,
385 sua2.unit_attempt_status,
386 sua2.no_assessment_ind,
387 loadcal.cal_type ci_cal_type,
388 loadcal.sequence_number ci_sequence_number
389 FROM
390 IGS_EN_SU_ATTEMPT_all sua1,
391 IGS_EN_SU_ATTEMPT_all sua2 ,
392 IGS_PS_UNIT_VER_ALL uv1,
393 IGS_PS_UNIT_VER_ALL uv2,
394 IGS_CA_INST_ALL loadcal,
395 IGS_CA_INST_REL acadterm,
396 IGS_CA_TYPE cat2,
397 IGS_CA_STAT cs2 ,
398 IGS_ST_DFT_LOAD_APPO l2t,
399 IGS_ST_DFT_LOAD_APPO l2tsua2
400
401 WHERE sua1.person_id = p_person_id
402 AND sua1.course_cd = p_course_cd
403 AND sua1.unit_attempt_status = cst_enrolled
404 AND sua2.uoo_id <> sua1.uoo_id
405 AND sua1.person_id = sua2.person_id
406 AND sua1.course_cd = sua2.course_cd
407 AND sua2.unit_attempt_status IN (cst_enrolled,cst_completed,cst_discontin)
408 AND uv1.unit_cd = sua1.unit_cd
409 AND uv1.version_number = sua1.version_number
410 AND uv2.unit_cd = sua2.unit_cd
411 AND uv2.version_number =sua2.version_number
412 AND uv1.research_unit_ind = 'Y'
413 AND uv2.research_unit_ind = 'Y'
414 AND EXISTS (SELECT 'x' FROM IGS_CA_INST_REL acadteach
415 WHERE acadteach.sup_cal_type = cp_acad_cal_type
416 AND acadteach.sup_ci_sequence_number = cp_acad_seq_num
417 AND acadteach.sub_cal_type = sua1.cal_type
418 AND acadteach.sub_ci_sequence_number = sua1.ci_sequence_number)
419 AND EXISTS (SELECT 'x' FROM IGS_CA_INST_REL acadteach
420 WHERE acadteach.sup_cal_type = cp_acad_cal_type
421 AND acadteach.sup_ci_sequence_number = cp_acad_seq_num
422 AND acadteach.sub_cal_type = sua2.cal_type
423 AND acadteach.sub_ci_sequence_number = sua2.ci_sequence_number)
424 AND acadterm.sup_cal_type = cp_acad_cal_type
425 AND acadterm.sup_ci_sequence_number = cp_acad_seq_num
426 AND loadcal.cal_type = acadterm.sub_cal_type
427 AND loadcal.sequence_number = acadterm.sub_ci_sequence_number
428 AND cat2.cal_type = loadcal.cal_type
429 AND cat2.s_cal_cat = cst_load
430 -- Check they are active
431 AND cs2.cal_status = loadcal.cal_status
432 AND cs2.s_cal_status = cst_active
433 AND l2t.cal_type = loadcal.cal_type
434 AND l2t.ci_sequence_number = loadcal.sequence_number
435 AND l2t.teach_cal_type = sua1.cal_type
436 AND l2tsua2.cal_type = l2t.cal_type
437 AND l2tsua2.ci_sequence_number = l2t.ci_sequence_number
438 AND l2tsua2.teach_cal_type = sua2.cal_type;
439
440
441
442
443 v_return_false BOOLEAN := FALSE;
444 BEGIN
445 -- Set the default message number
446 p_message_name := null;
447 -- 1. Check if the person is a candidate.
448 OPEN c_ca;
449 FETCH c_ca INTO v_ca_exists;
450 IF c_ca%NOTFOUND THEN
451 CLOSE c_ca;
452 -- Not a research student - not applicable.
453 RETURN TRUE;
454 END IF;
455 CLOSE c_ca;
456 -- 2. Select all enrolled research units (in the academic year if specified)
457 -- Determine the load calendar to which the teaching calendar contributes.
458 -- Find any other research unit attempts which are
459 -- incurring load within the same load calendar.
460
461 FOR rec_acad IN c_acad LOOP
462 FOR v_sua_rec IN c_sua(rec_acad.acad_cal_type, rec_acad.acad_ci_sequence_number) LOOP
463 IF IGS_EN_PRC_LOAD.ENRP_GET_LOAD_INCUR(
464 v_sua_rec.sua_cal_type,
465 v_sua_rec.sua_ci_sequence_number,
466 v_sua_rec.discontinued_dt,
467 v_sua_rec.administrative_unit_status,
468 v_sua_rec.unit_attempt_status,
469 v_sua_rec.no_assessment_ind,
470 v_sua_rec.ci_cal_type,
471 v_sua_rec.ci_sequence_number,
472 -- anilk, Audit special fee build
473 NULL, -- for p_uoo_id
474 'N') = 'Y' THEN
475 v_return_false := TRUE;
476 EXIT;
477 END IF;
478 END LOOP;
479 END LOOP;
480 IF v_return_false THEN
481 p_message_name := 'IGS_RE_CAND_ENROL_IN_SING_RES';
482 RETURN FALSE;
483 END IF;
484 -- Return the default value
485 RETURN TRUE;
486 EXCEPTION
487 WHEN OTHERS THEN
488 IF c_sua%ISOPEN THEN
489 CLOSE c_sua;
490 END IF;
491 RAISE;
492 END;
493 EXCEPTION
494 WHEN OTHERS THEN
495 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
496 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.resp_val_sua_all');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499 END resp_val_sua_all;
500 --
501 -- To validate for student unit attempt being excluded
502 FUNCTION enrp_val_sua_excld(
503 p_person_id IN NUMBER ,
504 p_course_cd IN VARCHAR2 ,
505 p_unit_cd IN VARCHAR2 ,
506 p_cal_type IN VARCHAR2 ,
507 p_ci_sequence_number IN NUMBER ,
508 p_message_name OUT NOCOPY varchar2 )
509 RETURN BOOLEAN AS
510
511 BEGIN -- enrp_val_sua_excld
512 -- This module validates if a student unit attempt is excluded from
513 -- enrolment / re-enrolment because of person, course, course group
514 -- or course unit exclusions that are effective on the census date
515 -- of the teaching period of the unit attempt.
516 DECLARE
517 v_message_name varchar2(30);
518 v_ret BOOLEAN;
519 CURSOR c_sua IS
520 SELECT daiv.alias_val,
521 ci.start_dt,
522 ci.end_dt
523 FROM IGS_CA_DA_INST_V daiv,
524 IGS_CA_INST ci,
525 IGS_GE_S_GEN_CAL_CON sgcc
526 WHERE daiv.cal_type = p_cal_type AND
527 daiv.ci_sequence_number = p_ci_sequence_number AND
528 daiv.dt_alias = sgcc.census_dt_alias AND
529 sgcc.s_control_num = 1 AND
530 daiv.cal_type = ci.cal_type AND
531 daiv.ci_sequence_number = ci.sequence_number;
532 BEGIN
533 p_message_name := null;
534 -- Validate parameters passed.
535 IF p_person_id IS NULL OR
536 p_course_cd IS NULL OR
537 p_unit_cd IS NULL OR
538 p_cal_type IS NULL OR
539 p_ci_sequence_number IS NULL THEN
540 p_message_name := 'IGS_EN_PARAM_ROUTINE_SPECIFY';
541 RETURN FALSE;
542 END IF;
543 -- Validate records
544 FOR v_sua_rec IN c_sua LOOP
545 -- Only validate if census date is between ci.start_dt and ci.end_dt.
546 IF (v_sua_rec.alias_val >= v_sua_rec.start_dt) AND
547 (v_sua_rec.alias_val <= v_sua_rec.end_dt) THEN
548 -- Validate against person, course and course group exclusions.
549 IF IGS_EN_VAL_ENCMB.enrp_val_excld_crs (
550 p_person_id,
551 p_course_cd,
552 v_sua_rec.alias_val,
553 v_message_name) = FALSE THEN
554 p_message_name := v_message_name;
555 v_ret := FALSE;
556 EXIT;
557 END IF;
558 -- Validate against course and unit exclusions.
559 IF IGS_EN_VAL_ENCMB.enrp_val_excld_unit (
560 p_person_id,
561 p_course_cd,
562 p_unit_cd,
563 v_sua_rec.alias_val,
564 v_message_name) = FALSE THEN
565 v_ret := FALSE;
566 p_message_name := v_message_name;
567 EXIT;
568 END IF;
569 END IF;
570 END LOOP;
571 IF v_ret = FALSE THEN
572 RETURN FALSE;
573 END IF;
574 RETURN TRUE;
575 EXCEPTION
576 WHEN OTHERS THEN
577 IF (c_sua%ISOPEN) THEN
578 CLOSE c_sua;
579 END IF;
580 RAISE;
581 END;
582 END enrp_val_sua_excld;
583 --
584 -- To validate update of SUA.
585 FUNCTION enrp_val_sua_update(
586 p_person_id IN NUMBER ,
587 p_course_cd IN VARCHAR2 ,
588 p_unit_cd IN VARCHAR2 ,
589 p_cal_type IN VARCHAR2 ,
590 p_ci_sequence_number IN NUMBER ,
591 p_unit_attempt_status IN VARCHAR2 ,
592 p_new_version_number IN NUMBER ,
593 p_new_location_cd IN VARCHAR2 ,
594 p_new_unit_class IN VARCHAR2 ,
595 p_new_enrolled_dt IN DATE ,
596 p_new_discontinued_dt IN DATE ,
597 p_new_admin_unit_status IN VARCHAR2 ,
598 p_new_rule_waived_dt IN DATE ,
599 p_new_rule_waived_person_id IN NUMBER ,
600 p_new_no_assessment_ind IN VARCHAR2 ,
601 p_new_sup_unit_cd IN VARCHAR2 ,
602 p_new_sup_version_number IN NUMBER ,
603 p_new_exam_location_cd IN VARCHAR2 ,
604 p_old_version_number IN NUMBER ,
605 p_old_location_cd IN VARCHAR2 ,
606 p_old_unit_class IN VARCHAR2 ,
607 p_old_enrolled_dt IN DATE ,
608 p_old_discontinued_dt IN DATE ,
609 p_old_admin_unit_status IN VARCHAR2 ,
610 p_old_rule_waived_dt IN DATE ,
611 p_old_rule_waived_person_id IN NUMBER ,
612 p_old_no_assessment_ind IN VARCHAR2 ,
613 p_old_sup_unit_cd IN VARCHAR2 ,
614 p_old_sup_version_number IN NUMBER ,
615 p_old_exam_location_cd IN VARCHAR2 ,
616 p_message_name OUT NOCOPY VARCHAR2,
617 p_uoo_id IN NUMBER)
618 RETURN BOOLEAN AS
619 -------------------------------------------------------------------------------------------
620 --Change History:
621 --Who When What
622 --kkillams 28-04-2003 Added new parameter p_uoo_id to the function
623 -- Modified the c_sut cursor where clause due to change in pk
624 -- of student unit attempt table w.r.t. bug number 2829262
625 -- ckasu 21-Dec-2004 modified procedure inorder to as a part of bug# 4080883
626 -- ctyagi 29-Sept-2005 modified cursor c_sut for bug# 4524765
627 -------------------------------------------------------------------------------------------
628 BEGIN -- enrp_val_sua_update
629 -- This modules validates the update of IGS_EN_SU_ATTEMPT in relation to
630 -- unit_attempt_status.
631 DECLARE
632 CURSOR c_sut IS
633 SELECT 'X'
634 FROM IGS_PS_STDNT_UNT_TRN sut1
635 WHERE
636 sut1.person_id = p_person_id AND
637 sut1.transfer_course_cd = p_course_cd AND
638 sut1.uoo_id = p_uoo_id and
639 sut1.transfer_dt = ( SELECT max(sut2.transfer_dt)
640 FROM IGS_PS_STDNT_UNT_TRN sut2
641 where sut2.person_id = sut1.person_id
642 and sut2.transfer_course_cd = sut1.transfer_course_cd
643 and sut2.uoo_id = sut1.uoo_id)
644 and sut1.transfer_dt > (SELECT NVL(max(sut3.transfer_dt),(sut1.transfer_dt-1))
645 FROM IGS_PS_STDNT_UNT_TRN sut3
646 where sut3.person_id = sut1.person_id
647 and sut3.course_cd = sut1.transfer_course_cd
648 and sut3.uoo_id = sut1.uoo_id);
649 CURSOR c_sca IS
650 SELECT sca.course_attempt_status
651 FROM IGS_EN_STDNT_PS_ATT sca
652 WHERE sca.person_id = p_person_id AND
653 sca.course_cd = p_course_cd;
654 CURSOR c_old_sua_attr IS
655 SELECT sua.unit_attempt_status
656 FROM igs_en_su_attempt sua
657 WHERE sua.person_id = p_person_id AND
658 sua.course_cd = p_course_cd AND
659 sua.uoo_id = p_uoo_id;
660 l_old_unit_status igs_en_su_attempt.unit_attempt_status%TYPE;
661
662 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
663 v_sut_found VARCHAR2(1) := NULL;
664 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
665 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
666 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
667 cst_lapsed CONSTANT VARCHAR2(7) := 'LAPSED';
668 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
669 cst_invalid CONSTANT VARCHAR2(10) := 'INVALID';
670 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
671 cst_dropped CONSTANT VARCHAR2(10) := 'DROPPED';
672 BEGIN
673 -- Set p_message_name
674 p_message_name := null;
675 OPEN c_sca;
676 FETCH c_sca INTO v_course_attempt_status;
677 IF (c_sca%NOTFOUND) THEN
678 CLOSE c_sca;
679 RETURN TRUE;
680 ELSE
681 CLOSE c_sca;
682 IF v_course_attempt_status = cst_lapsed THEN
683 p_message_name := 'IGS_EN_SUA_NOTUPD_PRGATT_LAPS';
684 RETURN FALSE;
685 ELSIF v_course_attempt_status = cst_discontin THEN
686 IF p_unit_attempt_status = cst_unconfirm THEN
687 p_message_name := 'IGS_EN_UNCONFIRM_SUA';
688 RETURN FALSE;
689 ELSIF p_unit_attempt_status = cst_invalid THEN
690 p_message_name := 'IGS_EN_INVALID_SUA_NOTUPD';
691 RETURN FALSE;
692 END IF;
693 END IF;
694
695 IF p_unit_attempt_status IN (cst_enrolled,cst_invalid) THEN
696 IF v_course_attempt_status = cst_unconfirm THEN
697 p_message_name := 'IGS_EN_SUA_NOTCONFIRM_SPA';
698 RETURN FALSE;
699 ELSIF v_course_attempt_status = cst_discontin THEN
700 p_message_name := 'IGS_EN_SUA_NOT_ENROL';
701 RETURN FALSE;
702 END IF;
703 END IF;
704
705 END IF;
706
707 -- get the old unit attempt status. This will be available because this procedure is called from before_dml
708 -- code added by ckasu as a part of bug# 4080883
709 OPEN c_old_sua_attr ;
710 FETCH c_old_sua_attr INTO l_old_unit_status;
711 CLOSE c_old_sua_attr;
712
713 IF p_unit_attempt_status = cst_duplicate AND l_old_unit_status <> cst_dropped THEN
714 p_message_name := 'IGS_EN_SUPL_SUA_NOTUPD';
715 RETURN FALSE;
716 END IF;
717
718
719 IF p_unit_attempt_status = cst_completed AND l_old_unit_status NOT IN (cst_dropped,cst_completed) THEN
720 -- Check that completed unit is not a duplicate
721 OPEN c_sut;
722 FETCH c_sut INTO v_sut_found;
723 IF (c_sut%FOUND) THEN
724 CLOSE c_sut;
725 p_message_name := 'IGS_EN_COMPL_UA_NOTUPD';
726 RETURN FALSE;
727 END IF;
728 CLOSE c_sut;
729 END IF;
730
731 IF p_unit_attempt_status = cst_completed AND l_old_unit_status <> cst_dropped THEN
732 IF p_old_version_number <> p_new_version_number OR
733 p_old_location_cd <> p_new_location_cd OR
734 p_old_unit_class <> p_new_unit_class OR
735 TRUNC(p_old_enrolled_dt) <> TRUNC(p_new_enrolled_dt) OR
736 p_old_admin_unit_status <> p_new_admin_unit_status OR
737 TRUNC(p_old_discontinued_dt) <> TRUNC(p_new_discontinued_dt) OR
738 p_old_rule_waived_dt <> p_new_rule_waived_dt OR
739 p_old_rule_waived_person_id <> p_new_rule_waived_person_id OR
740 p_old_no_assessment_ind <> p_new_no_assessment_ind OR
741 p_old_sup_unit_cd <> p_new_sup_unit_cd OR
742 p_old_sup_version_number <> p_new_sup_version_number OR
743 p_old_exam_location_cd <> p_new_exam_location_cd THEN
744 p_message_name := 'IGS_EN_COMPL_SUA_NOTUPD';
745 RETURN FALSE;
746 END IF;
747 END IF;
748 IF p_unit_attempt_status = cst_discontin AND l_old_unit_status <> cst_dropped THEN
749 OPEN c_sut;
750 FETCH c_sut INTO v_sut_found;
751 IF (c_sut%FOUND) THEN
752 CLOSE c_sut;
753 p_message_name := 'IGS_EN_DISCONT_UA_NOTUPD_DUPL';
754 RETURN FALSE;
755 END IF;
756 CLOSE c_sut;
757 IF p_old_version_number <> p_new_version_number OR
758 p_old_location_cd <> p_new_location_cd OR
759 p_old_unit_class <> p_new_unit_class OR
760 TRUNC(p_old_enrolled_dt) <> TRUNC(p_new_enrolled_dt) OR
761 p_old_no_assessment_ind <> p_new_no_assessment_ind OR
762 p_old_sup_unit_cd <> p_new_sup_unit_cd OR
763 p_old_sup_version_number <> p_new_sup_version_number OR
764 p_old_exam_location_cd <> p_new_exam_location_cd THEN
765 p_message_name := 'IGS_EN_DISCONT_DET_SUA';
766 RETURN FALSE;
767 END IF;
768 END IF;
769 RETURN TRUE;
770 EXCEPTION
771 WHEN OTHERS THEN
772 IF (c_sut%ISOPEN) THEN
773 CLOSE c_sut;
774 END IF;
775 IF (c_sca%ISOPEN) THEN
776 CLOSE c_sca;
777 END IF;
778 RAISE;
779 END;
780 EXCEPTION
781 WHEN OTHERS THEN
782 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
783 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_update');
784 IGS_GE_MSG_STACK.ADD;
785 App_Exception.Raise_Exception;
786 END enrp_val_sua_update;
787 --
788 -- To validate SUA override credit reason
789 FUNCTION enrp_val_sua_cp_rsn(
790 p_override_enrolled_cp IN NUMBER ,
791 p_override_achievable_cp IN NUMBER ,
792 p_override_credit_reason IN VARCHAR2 ,
793 p_message_name OUT NOCOPY varchar2 )
794 RETURN BOOLEAN AS
795
796 BEGIN -- enrp_val_sua_cp_rsn
797 -- This module validates that IGS_EN_SU_ATTEMPT.override_credit_reason
798 -- only exists if one of IGS_EN_STDNT_PS_ATT.override_enrolled_cp or
799 -- IGS_EN_STDNT_PS_ATT.override_achievalble_cp exists.
800 BEGIN
801 p_message_name := null;
802 IF (p_override_credit_reason IS NOT NULL) AND
803 (p_override_enrolled_cp IS NULL) AND
804 (p_override_achievable_cp IS NULL) THEN
805 p_message_name := 'IGS_EN_OVERRIDE_CRD_REASON';
806 RETURN FALSE;
807 END IF;
808 RETURN TRUE;
809 END;
810 EXCEPTION
811 WHEN OTHERS THEN
812 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
813 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_cp_rsn');
814 IGS_GE_MSG_STACK.ADD;
815 App_Exception.Raise_Exception;
816 END enrp_val_sua_cp_rsn;
817 --
818 -- Routine to clear records saved in a PL/SQL RECORD from a prior commit.
819 PROCEDURE enrp_clear_sua_exist
820 AS
821 BEGIN
822 -- initialise
823 gt_sua_exists_table := gt_sua_exists_empty_table;
824 gv_sua_exists_table_index := 1;
825 END enrp_clear_sua_exist;
826
827 --
828 -- To validate enrolled date of SUA.
829 FUNCTION enrp_val_sua_ci(
830 p_person_id IN NUMBER ,
831 p_course_cd IN VARCHAR2 ,
832 p_cal_type IN VARCHAR2 ,
833 p_ci_sequence_number IN NUMBER ,
834 p_unit_attempt_status IN VARCHAR2 ,
835 p_commencement_dt IN DATE ,
836 p_form_trigger_ind IN VARCHAR2 ,
837 p_message_name OUT NOCOPY varchar2 )
838 RETURN BOOLEAN AS
839
840 BEGIN -- enrp_val_sua_ci
841 -- This module validates that the teaching period is valid for the
842 -- IGS_EN_SU_ATTEMPT.
843 -- * Teaching period must start after the commencement date of the
844 -- IGS_EN_STDNT_PS_ATT.
845 DECLARE
846 CURSOR c_ci IS
847 SELECT ci.end_dt
848 FROM IGS_CA_INST ci
849 WHERE ci.cal_type = p_cal_type AND
850 ci.sequence_number = p_ci_sequence_number;
851 v_ci_rec c_ci%ROWTYPE;
852 CURSOR c_sca IS
853 SELECT sca.commencement_dt
854 FROM IGS_EN_STDNT_PS_ATT sca
855 WHERE sca.person_id = p_person_id AND
856 sca.course_cd = p_course_cd;
857 v_sca_rec c_sca%ROWTYPE;
858 cst_duplicate CONSTANT VARCHAR2(9) := 'DUPLICATE';
859 cst_discontin CONSTANT VARCHAR2(9) := 'DISCONTIN';
860 cst_completed CONSTANT VARCHAR2(9) := 'COMPLETED';
861 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
862 BEGIN
863 -- Set the default message number
864 p_message_name := null;
865 IF p_unit_attempt_status NOT IN (cst_duplicate,cst_discontin,cst_completed) THEN
866 IF p_form_trigger_ind = 'F' THEN
867 IF p_commencement_dt IS NULL THEN
868 RETURN TRUE;
869 ELSE
870 v_commencement_dt := p_commencement_dt;
871 END IF;
872 ELSE
873 -- We need to get the commencement date from
874 -- the student course attempt
875 OPEN c_sca;
876 FETCH c_sca INTO v_sca_rec;
877 IF c_sca%NOTFOUND THEN
878 -- This should not occur, return from function
879 CLOSE c_sca;
880 RETURN TRUE;
881 END IF;
882 CLOSE c_sca;
883 IF v_sca_rec.commencement_dt IS NULL THEN
884 RETURN TRUE;
885 ELSE
886 v_commencement_dt := v_sca_rec.commencement_dt;
887 END IF;
888 END IF;
889 -- Determine end date of calendar instance
890 -- (student unit attempt teaching period)
891 OPEN c_ci;
892 FETCH c_ci INTO v_ci_rec;
893 IF c_ci%NOTFOUND THEN
894 -- This should not occur, return from function
895 CLOSE c_ci;
896 RETURN TRUE;
897 END IF;
898 CLOSE c_ci;
899 -- Check that end date of teaching period is
900 -- not less than the commencement date
901 IF v_ci_rec.end_dt < v_commencement_dt THEN
902 p_message_name := 'IGS_EN_TEACHPRD_UA_NOT_PRIOR';
903 RETURN FALSE;
904 END IF;
905 END IF;
906 RETURN TRUE;
907 EXCEPTION
908 WHEN OTHERS THEN
909 IF c_sca%ISOPEN THEN
910 CLOSE c_sca;
911 END IF;
912 IF c_ci%ISOPEN THEN
913 CLOSE c_ci;
914 END IF;
915 RAISE;
916 END;
917 END enrp_val_sua_ci;
918 --
919 -- To validate SUA alternative title.
920 FUNCTION enrp_val_sua_alt_ttl(
921 p_unit_cd IN VARCHAR2 ,
922 p_version_number IN NUMBER ,
923 p_alternative_title IN VARCHAR2 ,
924 p_message_name OUT NOCOPY varchar2 )
925 RETURN BOOLEAN AS
926
927 BEGIN -- enrp_val_sua_alt_ttl
928 -- validate the student unit attempt alternative title.
929 DECLARE
930 v_title_override_ind IGS_PS_UNIT_VER.title_override_ind%TYPE;
931 CURSOR c_uv IS
932 SELECT title_override_ind
933 FROM IGS_PS_UNIT_VER uv
934 WHERE uv.unit_cd = p_unit_cd AND
935 uv.version_number = p_version_number;
936 BEGIN
937 p_message_name := null;
938 IF p_alternative_title IS NULL THEN
939 RETURN TRUE;
940 END IF;
941 OPEN c_uv;
942 FETCH c_uv INTO v_title_override_ind;
943 IF (c_uv%FOUND) THEN
944 IF (v_title_override_ind = 'N') THEN
945 CLOSE c_uv;
946 p_message_name := 'IGS_EN_ALT_TITLE_NOTPERMITTED';
947 RETURN FALSE;
948 END IF;
949 END IF;
950 CLOSE c_uv;
951 RETURN TRUE ;
952 EXCEPTION
953 WHEN OTHERS THEN
954 IF(c_uv%ISOPEN) THEN
955 CLOSE c_uv;
956 END IF;
957 RAISE;
958 END;
959 EXCEPTION
960 WHEN OTHERS THEN
961 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
962 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_alt_ttl');
963 IGS_GE_MSG_STACK.ADD;
964 App_Exception.Raise_Exception;
965 END enrp_val_sua_alt_ttl;
966 --
967 -- Routine to clear records saved in a PL/SQL RECORD from a prior commit.
968 PROCEDURE enrp_clear_sua_dupl
969 AS
970 BEGIN
971 -- initialise
972 gt_sua_duplicate_table := gt_sua_duplicate_empty_table;
973 gv_sua_duplicate_table_index := 1;
974 END enrp_clear_sua_dupl;
975
976 --
977 -- Validate whether unit attempt can be pre-enrolled
978 FUNCTION enrp_val_sua_pre(
979 p_person_id IN NUMBER ,
980 p_course_cd IN VARCHAR2 ,
981 p_unit_cd IN VARCHAR2 ,
982 p_log_creation_dt IN DATE ,
983 p_warn_level OUT NOCOPY VARCHAR2 ,
984 p_message_name OUT NOCOPY varchar2 )
985 RETURN boolean AS
986
987 BEGIN -- enrp_val_sua_pre
988 -- To check for advanced standing or encumbrances which would prevent
989 -- the unit attempt being added as part of a pre-enrolment of units.
990 DECLARE
991 cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
992 cst_minor CONSTANT VARCHAR2(10) := 'MINOR';
993 cst_granted CONSTANT VARCHAR2(10) := 'GRANTED';
994 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
995 cst_credit CONSTANT VARCHAR2(10) := 'CREDIT';
996 cst_preclusion CONSTANT VARCHAR2(10) := 'PRECLUSION';
997 CURSOR c_adv IS
998 SELECT 'x'
999 FROM IGS_AV_STND_UNIT asu
1000 WHERE asu.person_id = p_person_id AND
1001 asu.as_course_cd = p_course_cd AND
1002 asu.unit_cd = p_unit_cd AND
1003 ((asu.s_adv_stnd_recognition_type = cst_credit AND
1004 igs_av_val_asu.granted_adv_standing(p_person_id,p_course_cd,NULL,
1005 p_unit_cd,NULL,'BOTH',NULL) ='TRUE')
1006 OR
1007 (asu.s_adv_stnd_granting_status IN (cst_approved,cst_granted) AND
1008 asu.s_adv_stnd_recognition_type = cst_preclusion));
1009 v_adv_exists VARCHAR2(1);
1010 v_message_name varchar2(30);
1011 BEGIN
1012 -- Set the default message number
1013 p_message_name := null;
1014 -- Check for advanced standing which is either Approved or Granted,
1015 -- and which is either a 100% credit or a preclusion from the nominated unit.
1016 OPEN c_adv;
1017 FETCH c_adv INTO v_adv_exists;
1018 IF c_adv%FOUND THEN
1019 CLOSE c_adv;
1020 IF p_log_creation_dt IS NOT NULL THEN
1021 -- Write to the exception log
1022 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1023 cst_pre_enrol,
1024 p_log_creation_dt,
1025 cst_minor || ',' ||
1026 TO_CHAR(p_person_id) || ',' ||
1027 p_course_cd,
1028 'IGS_EN_STUD_INELG_ADV_STANDIN',
1029 p_unit_cd);
1030 END IF;
1031 p_warn_level := cst_minor;
1032 p_message_name := 'IGS_EN_STUD_INELG_ADV_STANDIN';
1033 RETURN FALSE;
1034 ELSE
1035 CLOSE c_adv;
1036 END IF;
1037 -- Check for an encumbrance on the unit which would prevent it
1038 -- being enrolled. This checks for a current encumbrance, irrespective
1039 -- of whether it may be lifted sometime during the academic year.
1040 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_unit(
1041 p_person_id,
1042 p_course_cd,
1043 p_unit_cd,
1044 SYSDATE,
1045 v_message_name) THEN
1046 IF p_log_creation_dt IS NOT NULL THEN
1047 -- Write to the exception log
1048 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1049 cst_pre_enrol,
1050 p_log_creation_dt,
1051 cst_minor || ',' ||
1052 TO_CHAR(p_person_id) || ',' ||
1053 p_course_cd,
1054 'IGS_EN_STUD_INELG_UNIT_EXCLUS',
1055 p_unit_cd);
1056 END IF;
1057 p_warn_level := cst_minor;
1058 p_message_name := 'IGS_EN_STUD_INELG_UNIT_EXCLUS';
1059 RETURN FALSE;
1060 END IF;
1061 -- Return the default value
1062 RETURN TRUE;
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 IF c_adv%ISOPEN THEN
1066 CLOSE c_adv;
1067 END IF;
1068 RAISE;
1069 END;
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1073 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_pre');
1074 IGS_GE_MSG_STACK.ADD;
1075 App_Exception.Raise_Exception;
1076 END enrp_val_sua_pre;
1077 --
1078
1079 --
1080 -- To validate SUA advanced standing unit.
1081 FUNCTION enrp_val_sua_advstnd(
1082 p_person_id IN NUMBER ,
1083 p_course_cd IN VARCHAR2 ,
1084 p_crs_version_number IN NUMBER ,
1085 p_unit_cd IN VARCHAR2 ,
1086 p_un_version_number IN NUMBER ,
1087 p_message_name OUT NOCOPY VARCHAR2 ,
1088 p_legacy IN VARCHAR2)
1089 -------------------------------------------------------------------------------------------
1090 --Change History:
1091 --Who When What
1092 --kkillams 10-JUL-2003 Returning error message only if repeatable_ind is 'N' w.r.t. 3036367
1093 --rvivekan 09-sep-2003 Modified the behaviour of repeatable_ind column in igs_ps_unit_ver table. PSP integration build #3052433
1094 --rvivekan 24-SEP-2006 Removed p_legacy check for the granted advanced standing validation Bug#3132543
1095 -------------------------------------------------------------------------------------------
1096 RETURN BOOLEAN AS
1097 BEGIN
1098 DECLARE
1099 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
1100 cst_granted CONSTANT VARCHAR2(10) := 'GRANTED';
1101 cst_credit CONSTANT VARCHAR2(10) := 'CREDIT';
1102 cst_preclusion CONSTANT VARCHAR2(10) := 'PRECLUSION';
1103 v_other_detail VARCHAR2(255);
1104 v_total_exmptn_approved IGS_AV_ADV_STANDING.total_exmptn_approved%TYPE;
1105 v_total_exmptn_granted IGS_AV_ADV_STANDING.total_exmptn_granted%TYPE;
1106 v_total_exmptn_perc_grntd IGS_AV_ADV_STANDING.total_exmptn_perc_grntd%TYPE;
1107 v_message_name varchar2(30);
1108 v_crs_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1109 v_repeatable_ind IGS_PS_UNIT_VER.repeatable_ind%TYPE;
1110 CURSOR c_sca(
1111 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1112 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1113 SELECT sca.version_number
1114 FROM IGS_EN_STDNT_PS_ATT sca
1115 WHERE sca.person_id = cp_person_id AND
1116 sca.course_cd = cp_course_cd;
1117 CURSOR c_adv_stnd_unit_details (
1118 cp_person_id IGS_AV_STND_UNIT.person_id%TYPE,
1119 cp_course_cd IGS_AV_STND_UNIT.as_course_cd%TYPE,
1120 cp_crs_vers_num IGS_AV_STND_UNIT.as_version_number%TYPE,
1121 cp_unit_cd IGS_AV_STND_UNIT.unit_cd%TYPE,
1122 cp_un_vers_num IGS_AV_STND_UNIT.version_number%TYPE) IS
1123 SELECT unit_cd,version_number,s_adv_stnd_recognition_type
1124 FROM IGS_AV_STND_UNIT asu
1125 WHERE asu.person_id = cp_person_id AND
1126 asu.as_course_cd = cp_course_cd AND
1127 asu.as_version_number = cp_crs_vers_num AND
1128 asu.unit_cd = cp_unit_cd AND
1129 asu.version_number = cp_un_vers_num AND
1130 asu.s_adv_stnd_recognition_type IN (cst_credit,
1131 cst_preclusion) AND
1132 asu.s_adv_stnd_granting_status IN (cst_approved, cst_granted)
1133 GROUP BY unit_cd,version_number,s_adv_stnd_recognition_type;
1134 CURSOR c_unit_version(
1135 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
1136 cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
1137 SELECT repeatable_ind
1138 FROM IGS_PS_UNIT_VER uv
1139 WHERE uv.unit_cd = cp_unit_cd AND
1140 uv.version_number = cp_version_number;
1141 l_credits NUMBER;
1142 l_s_adv_atnd_granting_status igs_av_stnd_unit_all.s_adv_stnd_granting_status%TYPE;
1143 BEGIN
1144 -- This function validates a IGS_EN_SU_ATTEMPT in
1145 -- relation to advanced standing units.
1146 p_message_name := null;
1147 -- validate the input parameters
1148 IF (p_person_id IS NULL OR
1149 p_course_cd IS NULL OR
1150 p_unit_cd IS NULL OR
1151 p_un_version_number IS NULL) THEN
1152 p_message_name := 'IGS_EN_NOTVALIDATE_ADVSTD';
1153 RETURN FALSE;
1154 END IF;
1155 -- get course version number if it isn't passed
1156 IF p_crs_version_number IS NULL THEN
1157 OPEN c_sca(
1158 p_person_id,
1159 p_course_cd);
1160 FETCH c_sca INTO v_crs_version_number;
1161 IF c_sca%NOTFOUND THEN
1162 CLOSE c_sca;
1163 p_message_name := 'IGS_EN_NOTVALIDATE_ADVSTD';
1164 RETURN FALSE;
1165 ELSE
1166 CLOSE c_sca;
1167 END IF;
1168 ELSE
1169 v_crs_version_number := p_crs_version_number;
1170 END IF;
1171 -- get unit version repeatable indicator
1172 v_repeatable_ind := 'N';
1173 OPEN c_unit_version(
1174 p_unit_cd,
1175 p_un_version_number);
1176 FETCH c_unit_version INTO v_repeatable_ind;
1177 CLOSE c_unit_version;
1178 -- check for the existace of IGS_AV_STND_UNIT
1179 FOR v_adv_stnd IN c_adv_stnd_unit_details(
1180 p_person_id,
1181 p_course_cd,
1182 v_crs_version_number,
1183 p_unit_cd,
1184 p_un_version_number) LOOP
1185 -- Changed after academic records maitenance dld
1186 IF v_Adv_stnd.s_adv_stnd_recognition_type = cst_credit THEN
1187 IF NOT igs_av_val_asu.adv_Credit_pts(p_person_id,p_course_cd,v_crs_version_number,
1188 v_adv_stnd.unit_cd,v_adv_stnd.version_number,'BOTH',NULL,
1189 l_credits,l_s_adv_atnd_granting_status,p_message_name) THEN
1190
1191 -- unit will still need to be studied if credit is less than 100
1192 p_message_name := null;
1193 RETURN TRUE;
1194 ELSE
1195 IF (l_s_adv_atnd_granting_status= cst_granted) THEN
1196 -- unit doens't need to be attempted by
1197 -- student because advanced standing has been
1198 -- granted
1199 IF v_repeatable_ind <> 'X' THEN
1200 -- Warning only
1201 p_message_name := 'IGS_AV_STUD_GRANTED_ADV';
1202 RETURN TRUE;
1203 ELSE
1204 p_message_name := 'IGS_EN_STUD_GRANTED_ADVSTD';
1205 RETURN FALSE;
1206 END IF;
1207 ELSIF (l_s_adv_atnd_granting_status <> cst_granted AND p_legacy <> 'Y' ) THEN
1208 -- check that course version advanced
1209 -- standing limits are not exceeded by
1210 -- approved and granted advanced standing
1211 IF (IGS_AV_VAL_ASU.advp_val_as_totals (
1212 p_person_id,
1213 p_course_cd,
1214 v_crs_version_number,
1215 TRUE, -- include approved advanced standing
1216 '', -- IGS_AV_STND_UNIT.unit_cd
1217 '', -- IGS_AV_STND_UNIT.version_number
1218 '', -- IGS_AV_STND_UNIT.s_adv_stnd_granting_status
1219 '', -- IGS_AV_STND_UNIT_LVL.unit_LEVEL
1220 '', -- IGS_AV_STND_UNIT_LVL.exemption_institution_cd
1221 '', -- IGS_AV_STND_UNIT_LVL.s_adv_stnd_granting_status
1222 v_total_exmptn_approved,
1223 v_total_exmptn_granted,
1224 v_total_exmptn_perc_grntd,
1225 v_message_name) = FALSE) THEN
1226 -- check for invalid parameters error
1227 IF (v_message_name <> 'IGS_AV_INSUFFICIENT_INFO_VER') THEN
1228 -- warn that approved advanced standing exists
1229 p_message_name := 'IGS_EN_STUD_APPROVED_ADVSTD';
1230 RETURN TRUE;
1231 END IF;
1232 ELSE
1233 -- unit doesn't need to be attempted by student
1234 -- because approved advanced standing exists and is
1235 -- liekly to be granted with nightly process
1236 IF v_repeatable_ind <> 'X' THEN
1237 -- Warning only
1238 p_message_name := 'IGS_EN_STUD_APPROVED_ADVSTD';
1239 RETURN TRUE;
1240 ELSE
1241 p_message_name := 'IGS_EN_STUD_APPR_ADVSTD';
1242 RETURN FALSE;
1243 END IF;
1244 END IF;
1245
1246 END IF;
1247 END IF;
1248 END IF;
1249 END LOOP;
1250 -- return the default message number and type
1251 p_message_name := null;
1252 RETURN TRUE;
1253 END;
1254 END enrp_val_sua_advstnd;
1255 --
1256 -- To validate the insertion of an sua against any intermissions
1257 FUNCTION ENRP_VAL_SUA_INTRMT(
1258 p_person_id IN NUMBER ,
1259 p_course_cd IN VARCHAR2 ,
1260 p_cal_type IN VARCHAR2 ,
1261 p_ci_sequence_number IN NUMBER ,
1262 p_message_name OUT NOCOPY varchar2 )
1263 RETURN boolean AS
1264 BEGIN
1265 DECLARE
1266 NO_SGCC_RECORDS_FOUND EXCEPTION;
1267 v_census_dt_alias IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE;
1268 v_start_dt IGS_EN_STDNT_PS_INTM.start_dt%TYPE;
1269 v_end_dt IGS_EN_STDNT_PS_INTM.end_dt%TYPE;
1270 v_rec_found BOOLEAN := FALSE;
1271
1272 CURSOR c_census_dt IS
1273 SELECT sgcc.census_dt_alias
1274 FROM IGS_GE_S_GEN_CAL_CON sgcc
1275 WHERE sgcc.s_control_num = 1;
1276 CURSOR c_sci_details
1277 (cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1278 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE) IS
1279 SELECT sci.start_dt,
1280 sci.end_dt,
1281 sci.cond_return_flag , sci.logical_delete_date
1282 FROM IGS_EN_STDNT_PS_INTM sci,
1283 IGS_EN_INTM_TYPES eit
1284 WHERE sci.person_id = cp_person_id AND
1285 sci.course_cd = cp_course_cd AND
1286 sci.approved = eit.appr_reqd_ind AND
1287 eit.intermission_type = sci.intermission_type AND
1288 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY');
1289
1290 CURSOR c_daiv_details
1291 (cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1292 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1293 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1294 cp_ci_seq_num IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1295 cp_cond_ret_ind IGS_EN_STDNT_PS_INTM.cond_return_flag%TYPE,
1296 cp_census_dt IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE,
1297 cp_start_dt IGS_EN_STDNT_PS_INTM.start_dt%TYPE,
1298 cp_end_dt IGS_EN_STDNT_PS_INTM.end_dt%TYPE,
1299 cp_logical_del_dt IGS_EN_STDNT_PS_INTM.logical_delete_date%TYPE) IS
1300 SELECT daiv.dt_alias
1301 FROM IGS_CA_DA_INST_V daiv
1302 WHERE daiv.cal_type = cp_cal_type AND
1303 daiv.ci_sequence_number = cp_ci_seq_num AND
1304 daiv.dt_alias = cp_census_dt AND
1305 daiv.alias_val >= cp_start_dt AND
1306 (daiv.alias_val <= cp_end_dt OR
1307 (daiv.alias_val > cp_end_dt AND
1308 cp_cond_ret_ind = 'Y' AND
1309 EXISTS ( SELECT 'x'
1310 FROM IGS_EN_SPI_RCONDS
1311 WHERE person_id =p_person_id
1312 AND course_cd =p_course_cd
1313 AND start_dt =cp_start_dt
1314 AND logical_delete_date =cp_logical_del_dt
1315 AND status_code IN('FAILED','PENDING')
1316 )
1317 )
1318 );
1319
1320 BEGIN
1321 -- this module validates that the teaching perion of
1322 -- UA being added is permitted according to any
1323 -- intermission details which exist.
1324 -- The validation will fail if the census date (or any
1325 -- of the census dates if there are multiple) fall
1326 -- within the intermission person.
1327 -- note : this is not inclusive of the end date.
1328 -- set the default message number
1329 p_message_name := null;
1330 -- select the census date alias from the general
1331 -- calendar confiration table
1332 OPEN c_census_dt;
1333 FETCH c_census_dt INTO v_census_dt_alias;
1334 -- raise an exception if no IGS_GE_S_GEN_CAL_CON
1335 -- records are found
1336 IF (c_census_dt%NOTFOUND) THEN
1337 CLOSE c_census_dt;
1338 RAISE NO_SGCC_RECORDS_FOUND;
1339 END IF;
1340 CLOSE c_census_dt;
1341 -- select the student course intermission records
1342 -- for the relevant course
1343 FOR v_sci_details IN c_sci_details(
1344 p_person_id,
1345 p_course_cd) LOOP
1346 -- looping through dt_alias_INST_V records
1347 FOR v_daiv_details IN c_daiv_details(
1348 p_person_id,
1349 p_course_cd,
1350 p_cal_type,
1351 p_ci_sequence_number,
1352 v_sci_details.cond_return_flag,
1353 v_census_dt_alias,
1354 v_sci_details.start_dt,
1355 v_sci_details.end_dt,
1356 v_sci_details.logical_delete_date) LOOP
1357 -- set that a record was found
1358 v_rec_found := TRUE;
1359 END LOOP;
1360 END LOOP;
1361 -- checking if record(s) were found
1362 -- if so, set the message number and return
1363 -- FALSE
1364 IF (v_rec_found = TRUE) THEN
1365 p_message_name := 'IGS_EN_CANT_ADD_UNT_ATMPTS';
1366 RETURN FALSE;
1367 END IF;
1368 -- the default return type
1369 RETURN TRUE;
1370 EXCEPTION
1371 WHEN NO_SGCC_RECORDS_FOUND THEN
1372 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
1373
1374 IGS_GE_MSG_STACK.ADD;
1375 App_Exception.Raise_Exception;
1376 END;
1377 END enrp_val_sua_intrmt;
1378
1379 -- Routine to clear records saved in a PL/SQL RECORD from a prior commit.
1380 PROCEDURE enrp_clear_sua_disc
1381 AS
1382 BEGIN
1383 -- initialise
1384 gt_sua_discont_table := gt_sua_discont_empty_table;
1385 gv_sua_discont_table_index := 1;
1386 END enrp_clear_sua_disc;
1387
1388 --
1389 -- Validate the discontinued administrative unit status.
1390 FUNCTION enrp_val_discont_aus(
1391 p_administrative_unit_status IN VARCHAR2 ,
1392 p_discontinued_dt IN DATE ,
1393 p_cal_type IN VARCHAR2 ,
1394 p_ci_sequence_number IN NUMBER ,
1395 p_message_name OUT NOCOPY varchar2,
1396 p_uoo_id IN NUMBER ,
1397 p_message_token OUT NOCOPY VARCHAR2 ,
1398 p_legacy IN VARCHAR2)
1399 RETURN BOOLEAN AS
1400
1401 BEGIN
1402 DECLARE
1403 v_other_detail VARCHAR2(255);
1404 v_closed_ind IGS_AD_ADM_UNIT_STAT.closed_ind%TYPE;
1405 v_unit_attempt_status IGS_AD_ADM_UNIT_STAT.unit_attempt_status%TYPE;
1406 v_alias_val DATE;
1407 v_admin_unit_status_str VARCHAR2(2000);
1408 v_first_char NUMBER;
1409 v_grading_schema_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE;
1410 v_version_number IGS_AS_GRD_SCHEMA.version_number%TYPE;
1411 v_message_name varchar2(30);
1412 v_string_found BOOLEAN := FALSE;
1413 v_current_string IGS_AD_ADM_UNIT_STAT.administrative_unit_status%TYPE;
1414 v_administrative_unit_status VARCHAR2(2000);
1415 v_admin_entered IGS_AD_ADM_UNIT_STAT.administrative_unit_status%TYPE;
1416 CURSOR c_aus IS
1417 SELECT closed_ind,
1418 unit_attempt_status
1419 FROM IGS_AD_ADM_UNIT_STAT
1420 WHERE administrative_unit_status = p_administrative_unit_status;
1421 BEGIN
1422 -- This module validates the
1423 -- IGS_EN_SU_ATTEMPT.administrative_unit_status
1424 p_message_name := null;
1425 -- Validate no administrative unit status
1426 OPEN c_aus;
1427 FETCH c_aus INTO v_closed_ind, v_unit_attempt_status;
1428 -- check if a record has been found
1429 IF (c_aus%NOTFOUND) AND p_legacy <> 'Y' THEN
1430 CLOSE c_aus;
1431 RETURN TRUE;
1432 END IF;
1433 CLOSE c_aus;
1434
1435 IF p_administrative_unit_status IS NULL THEN
1436 IF v_unit_attempt_status = 'DROPPED' THEN
1437 RETURN TRUE;
1438 END IF;
1439 IF p_discontinued_dt IS NULL THEN
1440 -- return with no errors
1441 RETURN TRUE;
1442 ELSE
1443 -- return the message number if discontinued date exists
1444 p_message_name := 'IGS_EN_ADMIN_UNIT_ST_SPECIFY';
1445 IF p_legacy <> 'Y' THEN
1446 RETURN FALSE;
1447 ELSE
1448 Fnd_Message.Set_Name('IGS', p_message_name );
1449 FND_MSG_PUB.ADD;
1450 END IF;
1451 END IF;
1452 END IF;
1453
1454 IF (v_closed_ind = 'Y' AND p_legacy <> 'Y' ) THEN
1455 -- return the message number if the closed_ind = 'Y'
1456 p_message_name := 'IGS_EN_ADM_UNT_STAT_CLOSED';
1457 RETURN FALSE;
1458 END IF;
1459
1460 IF (v_unit_attempt_status <> 'DISCONTIN' AND p_legacy <> 'Y' ) THEN
1461 -- must be for DISCONTIN unit attempt status
1462 p_message_name := 'IGS_EN_SPECIFY_ADM_UNT_STATUS';
1463 RETURN FALSE;
1464 END IF;
1465
1466 -- validate that if the discontinued date is not set,
1467 -- then the related administrative status is not set
1468 IF (p_discontinued_dt IS NULL) AND
1469 (v_unit_attempt_status = 'DISCONTIN') THEN
1470 p_message_name := 'IGS_EN_DISCONT_ADM_UNIT_ST';
1471 IF p_legacy <> 'Y' THEN
1472 RETURN FALSE;
1473 ELSE
1474 Fnd_Message.Set_Name('IGS', p_message_name );
1475 FND_MSG_PUB.ADD;
1476 END IF;
1477 END IF;
1478
1479 -- Validate that administrative unit status applies at discontinuation
1480 -- as determined by unit discontinuation date criteria
1481 --Modified as a part of Enrollment Process build bug no:1832130
1482 -- Sarakshi , 27-07-2001, uoo_id is passed to IGS_EN_GEN_008.ENRP_GET_UDDC_AUS
1483 IF p_legacy <> 'Y' THEN
1484 v_administrative_unit_status := IGS_EN_GEN_008.ENRP_GET_UDDC_AUS(
1485 p_discontinued_dt,
1486 p_cal_type,
1487 p_ci_sequence_number,
1488 v_admin_unit_status_str,
1489 v_alias_val,
1490 p_uoo_id);
1491
1492 IF v_admin_unit_status_str IS NULL THEN
1493 p_message_name := 'IGS_EN_ADMIN_UNITST_NOTVALID';
1494 RETURN FALSE;
1495 ELSE
1496 -- set the parameter passed in to a variable
1497 v_admin_entered := p_administrative_unit_status;
1498 IF (v_administrative_unit_status IS NULL) OR
1499 ((v_administrative_unit_status IS NOT NULL) AND
1500 (v_admin_entered <> v_administrative_unit_status)) THEN
1501 -- Administrative unit status is not equal to the defaults
1502 -- so check against string returned which contains list of
1503 -- valid administrative unit status delimited by ',' eg
1504 -- EARLY WDRW, LATE WDRW,
1505 -- set the current position in the string to 1
1506 v_first_char := 1;
1507 v_administrative_unit_status := NULL;
1508 LOOP
1509 -- exit when the end of the string is reached
1510 EXIT WHEN v_first_char >= LENGTH(v_admin_unit_status_str);
1511 -- put 10 characters at a a time into a string for comparison
1512 v_current_string := (SUBSTR(v_admin_unit_status_str, v_first_char, 10));
1513 -- don't do anything if the string is null
1514 IF (v_current_string IS NULL) THEN
1515 EXIT;
1516 ELSE
1517 -- if the parameter string entered is part of string
1518 -- passed in, then exit - it's been found
1519 IF (RPAD(v_admin_entered,10,' ') = RPAD(v_current_string,10,' ')) THEN
1520 v_string_found := TRUE;
1521 EXIT;
1522 ELSE
1523 -- continue seaching the next 11 characters
1524 -- along in the string passed in (as we have
1525 -- to account for the fact that the string
1526 -- is delimited by ','
1527 IF v_administrative_unit_status IS NULL THEN
1528 v_administrative_unit_status := RTRIM(RPAD(v_current_string,10,' '));
1529 ELSE
1530 v_administrative_unit_status := v_administrative_unit_status||','||RTRIM(RPAD(v_current_string,10,' '));
1531 END IF;
1532 v_first_char := v_first_char + 11;
1533 END IF;
1534 END IF;
1535 END LOOP;
1536 -- return an error if the parameter string entered
1537 -- wasn't part of the string passed in
1538 IF (v_string_found = FALSE) THEN
1539 --p_message_name := 'IGS_EN_ADMIN_UNITST_NOTVALID';
1540 p_message_name := 'IGS_SS_EN_INVLD_ADMIN_UNITST';
1541 p_message_token := v_administrative_unit_status;
1542 RETURN FALSE;
1543 END IF;
1544 END IF;
1545 END IF; --end for v_admin_unit_status_str IS NULL
1546 -- Both the administrative unit status and administrative unit status (out parameter) string are both null return false.
1547 IF v_administrative_unit_status IS NULL AND v_admin_unit_status_str IS NULL THEN
1548 p_message_name := 'IGS_SS_EN_INVLD_ADMIN_UNITST';
1549 p_message_token := v_administrative_unit_status;
1550 RETURN FALSE;
1551 END IF;
1552 END IF;
1553
1554 -- validate administrative unit status grade
1555 IF (IGS_EN_VAL_SUA.enrp_get_sua_gs(
1556 p_discontinued_dt,
1557 p_administrative_unit_status,
1558 v_grading_schema_cd,
1559 v_version_number,
1560 v_message_name) = FALSE) THEN
1561 p_message_name := v_message_name;
1562 IF p_legacy <> 'Y' THEN
1563 RETURN FALSE;
1564 ELSE
1565 Fnd_Message.Set_Name('IGS', p_message_name );
1566 FND_MSG_PUB.ADD;
1567 END IF;
1568 END IF;
1569 RETURN TRUE;
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1573 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_discont_aus');
1574 IGS_GE_MSG_STACK.ADD;
1575 App_Exception.Raise_Exception;
1576 END;
1577 END enrp_val_discont_aus;
1578 --
1579 -- Get SUA grading schema.
1580 FUNCTION enrp_get_sua_gs(
1581 p_effective_dt IN DATE ,
1582 p_administrative_unit_status IN VARCHAR2 ,
1583 p_grading_schema_cd OUT NOCOPY VARCHAR2 ,
1584 p_version_number OUT NOCOPY NUMBER ,
1585 p_message_name OUT NOCOPY varchar2 )
1586 RETURN BOOLEAN AS
1587
1588 BEGIN
1589 DECLARE
1590 TYPE t_schema_dtls IS RECORD (
1591 grading_schema_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE,
1592 version_number IGS_AS_GRD_SCHEMA.version_number%TYPE );
1593 CURSOR c_get_schema_dtls IS
1594 SELECT DISTINCT gs.grading_schema_cd,
1595 gs.version_number
1596 FROM IGS_AS_GRD_SCHEMA gs,
1597 IGS_AS_GRD_SCH_GRADE gsg,
1598 IGS_AD_ADM_UT_STA_GD ausg
1599 WHERE gs.start_dt <= TRUNC(p_effective_dt) AND
1600 (gs.end_dt IS NULL OR
1601 gs.end_dt >= TRUNC(p_effective_dt)) AND
1602 gsg.grading_schema_cd = gs.grading_schema_cd AND
1603 gsg.version_number = gs.version_number AND
1604 ausg.grade = gsg.grade AND
1605 ausg.administrative_unit_status = p_administrative_unit_status;
1606 v_grading_schema_dtls t_schema_dtls;
1607 CURSOR c_aus IS
1608 SELECT unit_attempt_status
1609 FROM IGS_AD_ADM_UNIT_STAT
1610 WHERE administrative_unit_status = p_administrative_unit_status;
1611 l_status VARCHAR2(10);
1612 BEGIN
1613 --- Set the default message number
1614 p_message_name := null;
1615 OPEN c_aus;
1616 FETCH c_aus INTO l_status;
1617 close c_aus;
1618
1619 --- Select the effective grading schema.
1620 OPEN c_get_schema_dtls;
1621 FETCH c_get_schema_dtls INTO v_grading_schema_dtls;
1622 --- No match
1623 IF c_get_schema_dtls%NOTFOUND THEN
1624 IF l_status <> 'DROPPED' THEN
1625 p_grading_schema_cd := NULL;
1626 p_version_number := NULL;
1627 p_message_name := 'IGS_EN_CHK_ADM_UNT_STATUS';
1628 CLOSE c_get_schema_dtls;
1629 RETURN FALSE;
1630 END IF;
1631 END IF;
1632 --- One or more matches were found
1633 --- then return first one found
1634 p_grading_schema_cd := v_grading_schema_dtls.grading_schema_cd;
1635 p_version_number := v_grading_schema_dtls.version_number;
1636 p_message_name := null;
1637 CLOSE c_get_schema_dtls;
1638 RETURN TRUE;
1639 END;
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1643 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_get_sua_gs');
1644 IGS_GE_MSG_STACK.ADD;
1645 App_Exception.Raise_Exception;
1646 END enrp_get_sua_gs;
1647 --
1648 -- Get SUA administrative unit status grade.
1649 FUNCTION enrp_get_sua_ausg(
1650 p_administrative_unit_status IN VARCHAR2 ,
1651 p_person_id IN NUMBER ,
1652 p_course_cd IN VARCHAR2 ,
1653 p_unit_cd IN VARCHAR2 ,
1654 p_cal_type IN VARCHAR2 ,
1655 p_ci_sequence_number IN NUMBER ,
1656 p_effective_dt IN DATE ,
1657 p_grading_schema_cd OUT NOCOPY VARCHAR2 ,
1658 p_version_number OUT NOCOPY NUMBER ,
1659 p_grade OUT NOCOPY VARCHAR2 ,
1660 p_message_name OUT NOCOPY VARCHAR2,
1661 p_uoo_id IN NUMBER)
1662 RETURN BOOLEAN AS
1663 -------------------------------------------------------------------------------------------
1664 --Change History:
1665 --Who When What
1666 --kkillams 28-04-2003 Added new parameter p_uoo_id to the function
1667 -- Modified the c_no_ass_ind and c_sua_ausg cursors where clause due
1668 -- to change in pk of student unit attempt table
1669 -- w.r.t. bug number 2829262
1670 -------------------------------------------------------------------------------------------
1671 BEGIN
1672 DECLARE
1673
1674 v_ausg_count NUMBER;
1675 v_dummy VARCHAR2 (1);
1676 l_no_assessment_ind IGS_EN_SU_ATTEMPT.NO_ASSESSMENT_IND%TYPE;
1677
1678 CURSOR c_no_ass_ind IS
1679 SELECT no_assessment_ind
1680 FROM igs_en_su_attempt
1681 WHERE person_id = p_person_id
1682 AND course_cd = p_course_cd
1683 AND uoo_id = p_uoo_id;
1684
1685 CURSOR c_ausg_exists
1686 (cp_admin_unit_status
1687 IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE) IS
1688 SELECT 'X'
1689 FROM IGS_AD_ADM_UT_STA_GD ausg
1690 WHERE ausg.administrative_unit_status = cp_admin_unit_status;
1691
1692 CURSOR c_ausg_count
1693 (cp_admin_unit_status IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE,
1694 cp_effective_dt IGS_AS_GRD_SCHEMA.start_dt%TYPE,
1695 cp_no_assessment_ind IGS_EN_SU_ATTEMPT.NO_ASSESSMENT_IND%TYPE) IS
1696 SELECT COUNT(*)
1697 FROM igs_ad_adm_ut_sta_gd ausg,
1698 igs_as_grd_schema gs,
1699 igs_as_grd_sch_grade gsg
1700 WHERE ausg.administrative_unit_status = cp_admin_unit_status AND
1701 gs.grading_schema_cd = ausg.grading_schema_cd AND
1702 gs.version_number = ausg.version_number AND
1703 TRUNC(gs.start_dt) <= TRUNC(cp_effective_dt) AND
1704 TRUNC(NVL(gs.end_dt, cp_effective_dt)) >= TRUNC(cp_effective_dt) AND
1705 ausg.grading_schema_cd = gsg.grading_schema_cd AND
1706 ausg.version_number = gsg.version_number AND
1707 ausg.GRADE = gsg.grade AND
1708 (
1709 ( gsg.s_result_type = 'AUDIT' AND cp_no_assessment_ind ='Y')
1710 OR
1711 ( gsg.s_result_type <> 'AUDIT' AND cp_no_assessment_ind <> 'Y')
1712 );
1713
1714 CURSOR c_ausg
1715 (cp_admin_unit_status IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE,
1716 cp_effective_dt IGS_AS_GRD_SCHEMA.start_dt%TYPE,
1717 cp_no_assessment_ind IGS_EN_SU_ATTEMPT.NO_ASSESSMENT_IND%TYPE) IS
1718 SELECT ausg.grading_schema_cd,
1719 ausg.version_number,
1720 ausg.grade
1721 FROM igs_ad_adm_ut_sta_gd ausg,
1722 igs_as_grd_schema gs,
1723 igs_as_grd_sch_grade gsg
1724 WHERE ausg.administrative_unit_status = cp_admin_unit_status AND
1725 gs.grading_schema_cd = ausg.grading_schema_cd AND
1726 gs.version_number = ausg.version_number AND
1727 gs.start_dt <= TRUNC(cp_effective_dt) AND
1728 NVL(gs.end_dt, TRUNC(cp_effective_dt)) >= TRUNC(cp_effective_dt) AND
1729 ausg.grading_schema_cd = gsg.grading_schema_cd AND
1730 ausg.version_number = gsg.version_number AND
1731 ausg.GRADE = gsg.grade AND
1732 (
1733 ( gsg.s_result_type = 'AUDIT' AND cp_no_assessment_ind ='Y')
1734 OR
1735 ( gsg.s_result_type <> 'AUDIT' AND cp_no_assessment_ind <> 'Y')
1736 );
1737
1738 CURSOR c_sua_ausg
1739 (cp_admin_unit_status
1740 IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE,
1741 cp_effective_dt IGS_AS_GRD_SCHEMA.start_dt%TYPE) IS
1742 SELECT ausg.grading_schema_cd,
1743 ausg.version_number,
1744 ausg.grade
1745 FROM IGS_EN_SU_ATTEMPT sua,
1746 IGS_PS_UNIT_OFR_OPT uoo,
1747 IGS_AD_ADM_UT_STA_GD ausg,
1748 IGS_AS_GRD_SCHEMA gs,
1749 igs_as_grd_sch_grade gsg
1750 WHERE sua.person_id = p_person_id AND
1751 sua.course_cd = p_course_cd AND
1752 sua.uoo_id = p_uoo_id AND
1753 sua.unit_cd = uoo.unit_cd AND
1754 sua.version_number = uoo.version_number AND
1755 sua.cal_type = uoo.cal_type AND
1756 sua.ci_sequence_number = uoo.ci_sequence_number AND
1757 sua.location_cd = uoo.location_cd AND
1758 sua.unit_class = uoo.unit_class AND
1759 uoo.grading_schema_cd = ausg.grading_schema_cd AND
1760 ausg.administrative_unit_status = cp_admin_unit_status AND
1761 gs.grading_schema_cd = ausg.grading_schema_cd AND
1762 gs.version_number = ausg.version_number AND
1763 gs.start_dt <= cp_effective_dt AND
1764 NVL(gs.end_dt, cp_effective_dt) >= cp_effective_dt AND
1765 ausg.grading_schema_cd = gsg.grading_schema_cd AND
1766 ausg.version_number = gsg.version_number AND
1767 ausg.GRADE = gsg.grade AND
1768 (
1769 ( gsg.s_result_type = 'AUDIT' AND sua.no_assessment_ind ='Y')
1770 OR
1771 ( gsg.s_result_type <> 'AUDIT' AND sua.no_assessment_ind <> 'Y')
1772 );
1773 BEGIN
1774 -- This module gets the administrative unit status
1775 -- grading schema grade.
1776 -- If only one current grading schema grade mapped to the
1777 -- administrative_unit_status then use this grade which can be
1778 -- apply to all students. If multiple mappings exist, then determine
1779 -- the appropriate grade for the student's unit.
1780 -- Initialise out NOCOPY parameters.
1781 p_grading_schema_cd := NULL;
1782 p_version_number := NULL;
1783 p_grade := NULL;
1784 p_message_name := null;
1785
1786 OPEN c_no_ass_ind;
1787 FETCH c_no_ass_ind INTO l_no_assessment_ind;
1788 CLOSE c_no_ass_ind;
1789
1790 -- Determine how many current grades are mapped against the administrative
1791 -- unit status.
1792 OPEN c_ausg_count(
1793 p_administrative_unit_status,
1794 p_effective_dt,
1795 l_no_assessment_ind);
1796 FETCH c_ausg_count INTO v_ausg_count;
1797 IF c_ausg_count%NOTFOUND THEN
1798 v_ausg_count := 0;
1799 END IF;
1800 CLOSE c_ausg_count;
1801 IF v_ausg_count = 1 THEN
1802 OPEN c_ausg(p_administrative_unit_status,
1803 p_effective_dt,
1804 l_no_assessment_ind);
1805 FETCH c_ausg INTO p_grading_schema_cd,
1806 p_version_number,
1807 p_grade;
1808 IF c_ausg%FOUND THEN
1809 -- Return the retrieved values.
1810 CLOSE c_ausg;
1811 p_message_name := null;
1812 RETURN TRUE;
1813 END IF;
1814 CLOSE c_ausg;
1815 ELSIF v_ausg_count > 1 THEN
1816 OPEN c_sua_ausg(p_administrative_unit_status,
1817 p_effective_dt);
1818 FETCH c_sua_ausg INTO p_grading_schema_cd,
1819 p_version_number,
1820 p_grade;
1821 IF c_sua_ausg%FOUND THEN
1822 -- Return the retrieved values.
1823 CLOSE c_sua_ausg;
1824 p_message_name := null;
1825 RETURN TRUE;
1826 END IF;
1827 CLOSE c_sua_ausg;
1828 END IF;
1829
1830 -- If processing reaches here, check if any grades exist for the
1831 -- administrative unit status. It is valid as some do not have associated
1832 -- grades (eg. When discontinuing early and no student unit attempt
1833 -- outcome is record because the student unit attempt is deleted).
1834 OPEN c_ausg_exists(p_administrative_unit_status);
1835 FETCH c_ausg_exists INTO v_dummy;
1836 IF c_ausg_exists%FOUND THEN
1837 -- Error as the grading schema is not available
1838 -- for the effective date
1839 CLOSE c_ausg_exists;
1840 p_grading_schema_cd := NULL;
1841 p_version_number := NULL;
1842 p_grade := NULL;
1843 p_message_name := 'IGS_EN_CANT_DETR_UNIT_STATUS';
1844 RETURN FALSE;
1845 ELSE
1846 -- no records were found, so set the grade
1847 -- to NULL (this is acceptable, as some
1848 -- administrative unit statuses don't have
1849 -- associated grades)
1850 CLOSE c_ausg_exists;
1851 p_grading_schema_cd := NULL;
1852 p_version_number := NULL;
1853 p_grade := NULL;
1854 p_message_name := null;
1855 RETURN TRUE;
1856 END IF;
1857 EXCEPTION
1858 WHEN OTHERS THEN
1859 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1860 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_get_sua_ausg');
1861 IGS_GE_MSG_STACK.ADD;
1862 App_Exception.Raise_Exception;
1863 END;
1864 END enrp_get_sua_ausg;
1865 --
1866 -- To validate the discontinuation date
1867 FUNCTION enrp_val_sua_discont(
1868 p_person_id IN NUMBER ,
1869 p_course_cd IN VARCHAR2 ,
1870 p_unit_cd IN VARCHAR2 ,
1871 p_version_number IN NUMBER ,
1872 p_ci_start_dt IN DATE ,
1873 p_enrolled_dt IN DATE ,
1874 p_administrative_unit_status IN VARCHAR2 ,
1875 p_unit_attempt_status IN VARCHAR2 ,
1876 p_discontinued_dt IN DATE ,
1877 p_message_name OUT NOCOPY VARCHAR2 ,
1878 p_legacy IN VARCHAR2)
1879 RETURN BOOLEAN AS
1880 BEGIN
1881 DECLARE
1882
1883 v_unit_attempt_status IGS_AD_ADM_UNIT_STAT.unit_attempt_status%TYPE;
1884 v_s_unit_status IGS_AD_ADM_UNIT_STAT.unit_attempt_status%TYPE;
1885 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
1886 CURSOR c_uas IS
1887 SELECT unit_attempt_status
1888 FROM IGS_AD_ADM_UNIT_STAT
1889 WHERE administrative_unit_status = p_administrative_unit_status;
1890 CURSOR c_uvus IS
1891 SELECT us.s_unit_status
1892 FROM IGS_PS_UNIT_VER uv,
1893 IGS_PS_UNIT_STAT us
1894 WHERE uv.unit_cd = p_unit_cd AND
1895 uv.version_number = p_version_number AND
1896 us.unit_status = uv.unit_status;
1897 CURSOR c_cas IS
1898 SELECT course_attempt_status
1899 FROM IGS_EN_STDNT_PS_ATT
1900 WHERE person_id = p_person_id AND
1901 course_cd = p_course_cd;
1902 BEGIN
1903 -- This module validates the discontinued_dt from
1904 -- the IGS_EN_SU_ATTEMPT
1905 p_message_name := null;
1906
1907 -- This validation should not be done for dropped unit attempts
1908 -- which are being enrolled again. The administrative status
1909 -- will be null only for a dropped unit attempt. If the unit
1910 -- attempt was discontinued then the administrative unit status
1911 -- cannot be null.
1912 IF p_administrative_unit_status IS NULL AND p_legacy <> 'Y' THEN
1913 RETURN TRUE;
1914 END IF;
1915
1916 IF (p_discontinued_dt IS NOT NULL) THEN
1917 -- validate that the discontinued_dt is
1918 -- less than or equal to today's date
1919 IF (TRUNC(p_discontinued_dt) > TRUNC(SYSDATE)) THEN
1920 p_message_name := 'IGS_EN_SUA_DISCONT_FUTUREDT';
1921 IF p_legacy <> 'Y' THEN
1922 RETURN FALSE;
1923 ELSE
1924 Fnd_Message.Set_Name('IGS', p_message_name );
1925 FND_MSG_PUB.ADD;
1926 END IF;
1927 END IF;
1928 -- validate that the discontinued_dt is
1929 -- greater than or equal to the enrolled date
1930 IF (p_enrolled_dt IS NOT NULL AND
1931 (TRUNC(p_discontinued_dt) < TRUNC(p_enrolled_dt))) THEN
1932 p_message_name := 'IGS_EN_DISCONT_DT_GE_ENRDT';
1933 IF p_legacy <> 'Y' THEN
1934 RETURN FALSE;
1935 ELSE
1936 Fnd_Message.Set_Name('IGS', p_message_name );
1937 FND_MSG_PUB.ADD;
1938 END IF;
1939 END IF;
1940 END IF;
1941 -- validate that if the discontinued date is set,
1942 -- then the unit attempt status must be enrolled
1943 IF (p_discontinued_dt IS NOT NULL AND
1944 p_unit_attempt_status NOT IN ('ENROLLED', 'DISCONTIN' , 'DUPLICATE', 'DROPPED') AND
1945 p_legacy <> 'Y' ) THEN
1946 p_message_name := 'IGS_EN_ENROL_SUA_DISCONT';
1947 RETURN FALSE;
1948 END IF;
1949 -- validate that if the discontinued date is not set,
1950 -- then the related administrative status is not set
1951 IF (p_discontinued_dt IS NULL AND
1952 p_administrative_unit_status IS NOT NULL AND p_legacy <> 'Y' ) THEN
1953 OPEN c_uas;
1954 FETCH c_uas INTO v_unit_attempt_status;
1955 CLOSE c_uas;
1956 IF (v_unit_attempt_status = 'DISCONTIN') THEN
1957 p_message_name := 'IGS_EN_DISCONT_ADM_UNIT_ST';
1958 RETURN FALSE;
1959 END IF;
1960 END IF;
1961 IF (p_discontinued_dt IS NULL and p_legacy <> 'Y') THEN
1962 -- validate that if the discontinued date is not set,
1963 -- then the unit version is active
1964 OPEN c_uvus;
1965 FETCH c_uvus INTO v_s_unit_status;
1966 CLOSE c_uvus;
1967 IF (v_s_unit_status <> 'ACTIVE') THEN
1968 p_message_name := 'IGS_EN_UNITVERSION_INACTIVE';
1969 RETURN FALSE;
1970 END IF;
1971 -- validate that the course attempt status
1972 -- is enrolled, inactive or completed
1973 OPEN c_cas;
1974 FETCH c_cas INTO v_course_attempt_status;
1975 CLOSE c_cas;
1976 IF (v_course_attempt_status NOT IN ('ENROLLED', 'INACTIVE', 'COMPLETED')) THEN
1977 p_message_name := 'IGS_EN_SUA_NOT_DISCONT';
1978 RETURN FALSE;
1979 END IF;
1980 END IF;
1981 RETURN TRUE;
1982 EXCEPTION
1983 WHEN OTHERS THEN
1984 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1985 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_discont');
1986 IGS_GE_MSG_STACK.ADD;
1987 App_Exception.Raise_Exception;
1988 END;
1989 END enrp_val_sua_discont;
1990 --
1991 -- To validate enrolled date of SUA.
1992 FUNCTION enrp_val_sua_enr_dt(
1993 p_person_id IN NUMBER ,
1994 p_course_cd IN VARCHAR2 ,
1995 p_enrolled_dt IN DATE ,
1996 p_unit_attempt_status IN VARCHAR2 ,
1997 p_ci_end_dt IN DATE ,
1998 p_commencement_dt IN DATE ,
1999 p_message_name OUT NOCOPY VARCHAR2 ,
2000 p_legacy IN VARCHAR2)
2001 RETURN BOOLEAN AS
2002 -------------------------------------------------------------------------------------------
2003 --Change History:
2004 --Who When What
2005 --amuthu 02-APR-2002 defined the constant cst_waitlisted.
2006 -- the validation was presently being bypassed for unconfirmed units
2007 -- alone. Now waitlist status is also being added to it. bug 2335455
2008 --Nishikant 13-may-2002 Bug#2364216 Dropped case is also removed in the condition while checking for the Enrolled date
2009 -- is null or not. If the status is DROPPED then no need to check the Enrolled date is null or not.
2010 -- bdeviset 13-JUL-2005 if the status is PLANNED then no need to check the Enrolled date is null or not.
2011 -- for bug# 4377985 BUILD FOR EN317 SELF SERVICE ENHANCEMENTS
2012 -------------------------------------------------------------------------------------------
2013
2014 BEGIN
2015 DECLARE
2016 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
2017 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
2018 cst_waitlisted CONSTANT VARCHAR2(10) := 'WAITLISTED';
2019 cst_dropped CONSTANT VARCHAR2(10) := 'DROPPED'; -- Added by Nishikant - bug#2364216
2020 cst_planned CONSTANT VARCHAR2(10) := 'PLANNED';
2021 v_sca_details IGS_EN_STDNT_PS_ATT%ROWTYPE;
2022
2023 CURSOR c_sca_details (
2024 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
2025 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE) IS
2026 SELECT *
2027 FROM IGS_EN_STDNT_PS_ATT
2028 WHERE person_id = cp_person_id AND
2029 course_cd = cp_course_cd;
2030 BEGIN
2031 -- Validate that the IGS_EN_SU_ATTEMPT.enrolled_dt must be set
2032 -- for all unit attempt statuses with the exception of UNCONFIRM
2033 -- Validate that IGS_EN_SU_ATTEMPT.enrolled_dt >=
2034 -- IGS_EN_STDNT_PS_ATT.commencement_dt.
2035 -- Validate that the student unit attempt enrolled date must
2036 -- be set for all unit attempt statuses, with the exception
2037 -- of UNCONFIRM.
2038 -- amuthu 02-APR-2002, Now adding the WAITLISED status also
2039 -- to the exceptions, since for a waitlist status also the
2040 -- the enrolled date will not be set. see bug 2335455
2041
2042 -- Nishikant - Bug#bug#2364216 -- Dropped case is also removed in the condition
2043 -- If the status is DROPPED then no need to check the Enrolled date is null or not.
2044 -- if the status is PLANNED then no need to check the Enrolled date is null or not
2045 IF (p_enrolled_dt IS NULL
2046 AND p_unit_attempt_status <> cst_unconfirm
2047 AND p_unit_attempt_status <> cst_waitlisted
2048 AND p_unit_attempt_status <> cst_dropped
2049 AND p_unit_attempt_status <> cst_planned
2050 AND p_legacy <> 'Y' ) THEN
2051 p_message_name := 'IGS_GE_MANDATORY_FLD';
2052 RETURN FALSE;
2053 END IF;
2054 IF (p_enrolled_dt IS NOT NULL) THEN
2055 OPEN c_sca_details (p_person_id,
2056 p_course_cd);
2057 FETCH c_sca_details INTO v_sca_details;
2058 -- check if a record was found
2059 IF (c_sca_details%NOTFOUND) THEN
2060 p_message_name := null;
2061 CLOSE c_sca_details;
2062 RETURN TRUE;
2063 ELSE
2064 CLOSE c_sca_details;
2065 -- check the status of the student_confirmed_ind
2066 IF (v_sca_details.student_confirmed_ind = 'N') THEN
2067 p_message_name := 'IGS_EN_SUA_NOTENR_SPA';
2068 RETURN FALSE;
2069 ELSE
2070 -- check that not enrolling when course attempt is discontinued
2071 IF (v_sca_details.course_attempt_status = cst_discontin) THEN
2072 IF p_unit_attempt_status = cst_unconfirm AND p_legacy <> 'Y' THEN
2073 p_message_name := 'IGS_EN_SUA_NOT_ENROL';
2074 RETURN FALSE;
2075 END IF;
2076 END IF;
2077 -- Do not perform the following validation
2078 -- for now
2079 -- if p_commencement_dt is null, then
2080 -- retrieve the value from the database
2081 --IF (p_commencement_dt IS NULL) THEN
2082 -- IF (v_sca_details.commencement_dt > p_enrolled_dt) THEN
2083 -- p_message_name := 'IGS_EN_ENRDT_GE_SPA_COMMDT';
2084 -- RETURN FALSE;
2085 -- END IF;
2086 --ELSE
2087 -- IF (p_commencement_dt > p_enrolled_dt) THEN
2088 -- p_message_name := 'IGS_EN_ENRDT_GE_SPA_COMMDT';
2089 -- RETURN FALSE;
2090 -- END IF;
2091 --END IF;
2092 END IF;
2093 END IF;
2094 END IF;
2095 /* comment for bug 2344075 as per ray's suggestion
2096 IF (p_enrolled_dt IS NOT NULL AND p_enrolled_dt > p_ci_end_dt) THEN
2097 p_message_name := 'IGS_EN_ENRDT_LE_UOO_TEACHPRD';
2098 RETURN FALSE;
2099 END IF;
2100 */
2101 -- set the default message number and return type
2102 p_message_name := null;
2103 RETURN TRUE;
2104 END;
2105 END enrp_val_sua_enr_dt;
2106 --
2107 -- To validate SCA sub-units.
2108
2109 --
2110 -- To validate deletion of the student unit attempt
2111 FUNCTION enrp_val_sua_delete(
2112 p_person_id IN NUMBER ,
2113 p_course_cd IN VARCHAR2 ,
2114 p_unit_cd IN VARCHAR2 ,
2115 p_form_trigger_ind IN VARCHAR2 ,
2116 p_unit_attempt_status IN VARCHAR2 ,
2117 p_cal_type IN VARCHAR2 ,
2118 p_ci_sequence_number IN NUMBER ,
2119 p_discontinued_dt IN DATE ,
2120 p_effective_dt IN DATE ,
2121 p_message_name OUT NOCOPY VARCHAR2,
2122 p_uoo_id IN NUMBER)
2123 -------------------------------------------------------------------------------------------
2124 --Change History:
2125 --Who When What
2126 --kkillams 28-04-2003 Removed the cur_uoo_IGS_EN_SU_ATTEMPT cursor and it's references
2127 -- and modified the c_sut cursor where clause w.r.t. bug number 2829262
2128 -------------------------------------------------------------------------------------------
2129 RETURN BOOLEAN AS
2130 gv_other_details VARCHAR2(255);
2131 BEGIN -- enrp_val_sua_delete
2132 -- Validate the deletion of a IGS_EN_SU_ATTEMPT
2133 DECLARE
2134 cst_duplicate IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'DUPLICATE';
2135 cst_discontinued IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'DISCONTIN';
2136 cst_completed IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'COMPLETED';
2137 cst_unconfirmed IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'UNCONFIRM';
2138 cst_enrolled IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'ENROLLED';
2139 cst_invalid IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'INVALID';
2140 v_del_alwd_dt DATE := NULL;
2141 CURSOR c_sca IS
2142 SELECT course_rqrmnt_complete_ind
2143 FROM IGS_EN_STDNT_PS_ATT
2144 WHERE person_id = p_person_id AND
2145 course_cd = p_course_cd;
2146 v_course_rqrmnt_complete_ind
2147 IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind%TYPE := NULL;
2148 CURSOR c_sut IS
2149 SELECT 'x'
2150 FROM IGS_PS_STDNT_UNT_TRN
2151 WHERE person_id = p_person_id AND
2152 transfer_course_cd = p_course_cd AND
2153 uoo_id = p_uoo_id;
2154 v_sut_found VARCHAR2(1) := NULL;
2155 BEGIN
2156 -- Set the default message number
2157 p_message_name := null;
2158 -- Unconfirmed unit attempts can be deleted at any stage.
2159 IF p_unit_attempt_status = cst_unconfirmed OR
2160 p_unit_attempt_status = cst_invalid THEN
2161 RETURN TRUE;
2162 END IF;
2163 -- Duplicate unit attempts cannot be deleted when the
2164 -- course requirements are completed
2165 IF p_unit_attempt_status = cst_duplicate THEN
2166 OPEN c_sca;
2167 FETCH c_sca INTO v_course_rqrmnt_complete_ind;
2168 IF (c_sca%FOUND) THEN
2169 CLOSE c_sca;
2170 IF (v_course_rqrmnt_complete_ind = 'Y') THEN
2171 p_message_name := 'IGS_EN_DUPL_SUA_NOTDEL';
2172 RETURN FALSE;
2173 END IF;
2174 ELSE
2175 CLOSE c_sca;
2176 END IF;
2177 -- Validate that the duplicate student unit attempt is
2178 -- not a duplicate in another course.
2179 OPEN c_sut;
2180 FETCH c_sut INTO v_sut_found;
2181 IF (c_sut%FOUND) THEN
2182 CLOSE c_sut;
2183 p_message_name := 'IGS_EN_DUPL_STUD_UNIT_ATTEMPT';
2184 RETURN FALSE;
2185 END IF;
2186 CLOSE c_sut;
2187 END IF;
2188 -- Completed or discontinued unit attempts cannot be deleted
2189 IF p_unit_attempt_status = cst_completed OR
2190 p_unit_attempt_status = cst_discontinued THEN
2191 p_message_name := 'IGS_EN_CANT_DEL_DISCONT_ATMPT';
2192 RETURN FALSE;
2193 END IF;
2194
2195 -- Validate that delete is allowed as per unit
2196 -- unit discontinuation date criteria
2197 -- This validation cannot be performed in the trigger
2198 -- to cater before backdating of discontinuation date
2199 -- resulting in a delete (the discontinuation date is
2200 -- not available in delete trigger).
2201 IF p_unit_attempt_status = cst_enrolled THEN
2202 IF p_form_trigger_ind = 'F' THEN
2203 IF p_discontinued_dt IS NOT NULL THEN
2204 v_del_alwd_dt := p_discontinued_dt;
2205 ELSE
2206 v_del_alwd_dt := p_effective_dt;
2207 END IF;
2208 --Modified as a part of Enrollment Process build bug no:1832130
2209 -- Sarakshi , 27-07-2001,one cursor is opened to fetch the uoo_id corresponding
2210 -- to the pk of igs_en_su_attempt and passed to IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD
2211 IF IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD(
2212 p_cal_type,
2213 p_ci_sequence_number,
2214 v_del_alwd_dt,
2215 p_uoo_id) = 'N' THEN
2216 p_message_name := 'IGS_EN_CANT_DEL_STUD_UNIT';
2217 RETURN FALSE;
2218 END IF;
2219 END IF;
2220 END IF;
2221 --- Validate that for enrolled student_unit_attempts, the
2222 --- record can only be deleted in the record enrolments time frame.
2223 IF p_unit_attempt_status = cst_enrolled THEN
2224 --Modified as a part of Enrollment Process build bug no:1832130
2225 -- Sarakshi , 27-07-2001,one cursor is opened to fetch the uoo_id corresponding
2226 -- to the pk of igs_en_su_attempt and passed to igs_en_gen_008.enrp_get_var_window
2227
2228 -- Modified the Next IF logic as per the Bug# 2356997. Made the
2229 -- call to the igs_en_gen_008.enrp_get_var_window instead of IGS_EN_GEN_004.ENRP_GET_REC_WINDOW
2230 IF igs_en_gen_008.enrp_get_var_window(
2231 p_cal_type,
2232 p_ci_sequence_number,
2233 p_effective_dt,
2234 p_uoo_id) = FALSE THEN
2235 p_message_name := 'IGS_EN_CANT_DEL_ENRL_STUD_UNT';
2236 RETURN FALSE;
2237 END IF;
2238 END IF;
2239 --- Return the default return value
2240 RETURN TRUE;
2241 EXCEPTION
2242 WHEN OTHERS THEN
2243 IF (c_sca%ISOPEN) THEN
2244 CLOSE c_sca;
2245 END IF;
2246 IF (c_sut%ISOPEN) THEN
2247 CLOSE c_sut;
2248 END IF;
2249 RAISE;
2250 END;
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2254 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_delete');
2255 IGS_GE_MSG_STACK.ADD;
2256 App_Exception.Raise_Exception;
2257 END enrp_val_sua_delete;
2258 --
2259 -- To validate insert of SUA.
2260 FUNCTION enrp_val_sua_insert(
2261 p_person_id IN NUMBER ,
2262 p_course_cd IN VARCHAR2 ,
2263 p_unit_attempt_status IN VARCHAR2 ,
2264 p_message_name OUT NOCOPY varchar2 )
2265 RETURN BOOLEAN AS
2266 gv_other_details VARCHAR2(255);
2267 BEGIN
2268 DECLARE
2269 cst_discontin CONSTANT
2270 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE:= 'DISCONTIN';
2271 cst_lapsed CONSTANT
2272 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'LAPSED';
2273 cst_unconfirm CONSTANT
2274 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'UNCONFIRM';
2275 cst_completed CONSTANT
2276 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
2277 cst_duplicate CONSTANT
2278 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'DUPLICATE';
2279 CURSOR c_sca IS
2280 SELECT sca.course_attempt_status
2281 FROM IGS_EN_STDNT_PS_ATT sca
2282 WHERE sca.person_id = p_person_id AND
2283 sca.course_cd = p_course_cd;
2284 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
2285 BEGIN
2286 --- Set the default message number
2287 p_message_name := null;
2288 --- Get the course attempt status
2289 OPEN c_sca;
2290 FETCH c_sca INTO v_course_attempt_status;
2291 IF c_sca%NOTFOUND THEN
2292 CLOSE c_sca;
2293 RETURN TRUE;
2294 END IF;
2295 CLOSE c_sca;
2296 -- Validate against status of IGS_EN_STDNT_PS_ATT
2297 IF (v_course_attempt_status = cst_discontin) THEN
2298 IF (p_unit_attempt_status <> cst_duplicate) THEN
2299 p_message_name := 'IGS_EN_CANT_INS_STUD_UNT_ATMP';
2300 RETURN FALSE;
2301 END IF;
2302 END IF;
2303 IF (v_course_attempt_status = cst_lapsed) THEN
2304 p_message_name := 'IGS_EN_CANT_INS_STUD_UNT_ATMP';
2305 RETURN FALSE;
2306 END IF;
2307 IF (v_course_attempt_status = cst_unconfirm) THEN
2308 IF (p_unit_attempt_status <> cst_unconfirm) THEN
2309 p_message_name := 'IGS_EN_UNCONF_SUA_INSERTED';
2310 RETURN FALSE;
2311 END IF;
2312 END IF;
2313 IF (v_course_attempt_status = cst_completed) THEN
2314 IF (p_unit_attempt_status = cst_duplicate) THEN
2315 p_message_name := 'IGS_EN_DUPL_SUA_NOTINS';
2316 RETURN FALSE;
2317 END IF;
2318 END IF;
2319 -- Return the default value
2320 RETURN TRUE;
2321 EXCEPTION
2322 WHEN OTHERS THEN
2323 IF (c_sca%ISOPEN) THEN
2324 CLOSE c_sca;
2325 END IF;
2326 RAISE;
2327 END;
2328 END enrp_val_sua_insert;
2329 --
2330 -- Validate the confirmation of a student unit attempt.
2331 FUNCTION ENRP_VAL_SUA_CNFRM(
2332 p_person_id IN NUMBER ,
2333 p_course_cd IN VARCHAR2 ,
2334 p_unit_cd IN VARCHAR2 ,
2335 p_uv_version_number NUMBER ,
2336 p_cal_type IN VARCHAR2 ,
2337 p_ci_sequence_number IN NUMBER ,
2338 p_ci_end_dt IN DATE ,
2339 p_location_cd IN VARCHAR2 ,
2340 p_unit_class IN VARCHAR2 ,
2341 p_enrolled_dt IN DATE ,
2342 p_fail_type OUT NOCOPY VARCHAR2 ,
2343 p_message_name OUT NOCOPY varchar2 )
2344 RETURN BOOLEAN AS
2345 -------------------------------------------------------------------------------------------
2346 --Change History:
2347 --Who When What
2348 --kkillams 28-04-2003 Impacted object, due to change in the signature of igs_en_val_sua.enrp_val_sua_dupl
2349 -- of the function w.r.t. bug number 2829262
2350 -------------------------------------------------------------------------------------------
2351
2352 BEGIN -- enrp_val_sua_cnfrm
2353 -- Perform all validations associated with the confirmation of a unit
2354 -- attempt for a student. This module is a grouping of existing
2355 -- validation modules.
2356 -- Performs the following modules:
2357 -- Call IGS_EN_VAL_SUA.enrp_val_sua_insert;
2358 -- determine if the student is of the correct status to have
2359 -- a unit attempt added.
2360 -- Call IGS_EN_VAL_ENCMB.enrp_val_excld_unit;
2361 -- determine if the student is currently excluded from the unit.
2362 -- Call IGS_EN_VAL_SUA.enrp_val_sua_advstnd;
2363 -- determine if the student has already satisfied the unit
2364 -- through advanced standing.
2365 -- Call IGS_EN_VAL_SUA.enrp_val_sua_intrmt;
2366 -- determine if the attempt overlaps an existing period of
2367 -- intermission.
2368 -- Call IGS_EN_VAL_SUA.enrp_val_coo_loc;
2369 -- determine if the attempt is in line with students forced
2370 -- location (if applicable).
2371 -- Call IGS_EN_VAL_SUA.enrp_val_coo_mode;
2372 -- determine if the attemt is in line with students forced
2373 -- mode (if applicable).
2374 -- Call IGS_EN_VAL_SUA.enrp_val_sua_enr_dt;
2375 -- validate the enrolled date.
2376 -- Call IGS_EN_VAL_SUA.enrp_val_sua_ci;
2377 -- validate that the teaching period of the unit is not prior to
2378 -- the commencement date of the student course attempt.
2379 -- Call IGS_EN_VAL_SUA.enrp_val_sua_dupl;
2380 -- determine if the student is already enrolled concurrently in the
2381 -- unit or has completed the unit with a pass or incomplete result type.
2382 -- Call IGS_EN_VAL_SUA.resp_val_sua_cnfrm;
2383 -- validate if attempting to confirm a research unit attempt.
2384 -- The current set of fail types are:
2385 -- course The course isn?t in a correct state. ie.
2386 -- Discontinued or intermitted for the teaching period.
2387 -- ENCUMB Excluded from the unit by either course/unit or person encumbrances
2388 -- ADVSTAND Already granted in advanced standing
2389 -- CROSS Breaches a cross-element restriction
2390 -- ENROLDT Enrolment date invalid
2391 -- TEACHING Teaching Period invalid
2392 -- DUPLICATE Already enrolled or completed unit attempt
2393 DECLARE
2394 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
2395 cst_course CONSTANT VARCHAR2(10) := 'course';
2396 cst_encumb CONSTANT VARCHAR2(10) := 'ENCUMB';
2397 cst_advstand CONSTANT VARCHAR2(10) := 'ADVSTAND';
2398 cst_cross CONSTANT VARCHAR2(10) := 'CROSS';
2399 cst_enroldt CONSTANT VARCHAR2(10) := 'ENROLDT';
2400 cst_teaching CONSTANT VARCHAR2(10) := 'TEACHING';
2401 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
2402 CURSOR c_sca IS
2403 SELECT sca.version_number,
2404 sca.coo_id,
2405 sca.commencement_dt
2406 FROM IGS_EN_STDNT_PS_ATT sca
2407 WHERE person_id = p_person_id AND
2408 course_cd = p_course_cd;
2409 CURSOR c_sua IS
2410 SELECT uoo_id
2411 FROM igs_ps_unit_ofr_opt
2412 WHERE unit_cd = p_unit_cd
2413 AND version_number = p_uv_version_number
2414 AND cal_type = p_cal_type
2415 AND ci_sequence_number = p_ci_sequence_number
2416 AND location_cd = p_location_cd
2417 AND unit_class = p_unit_class;
2418
2419 l_uoo_id igs_en_su_attempt.uoo_id%TYPE;
2420 v_sca_rec c_sca%ROWTYPE;
2421 v_return_val BOOLEAN := FALSE;
2422 v_message_name varchar2(30);
2423 v_duplicate_course_cd VARCHAR2(6);
2424 BEGIN
2425 -- Set the := message number
2426 p_message_name := null;
2427 p_fail_type := NULL;
2428 OPEN c_sua;
2429 FETCH c_sua INTO l_uoo_id;
2430 CLOSE c_sua;
2431 -- Determine if the student is of the correct status to have a unit attempt
2432 -- added.
2433 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_insert(
2434 p_person_id,
2435 p_course_cd,
2436 cst_enrolled,
2437 v_message_name) THEN
2438 p_fail_type := cst_course;
2439 p_message_name := v_message_name;
2440 RETURN FALSE;
2441 END IF;
2442 IF v_message_name <> NULL THEN
2443 p_fail_type := cst_course;
2444 p_message_name := v_message_name;
2445 END IF;
2446 -- Determine if the attempt overlaps an existing period of intermission.
2447 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_intrmt(
2448 p_person_id,
2449 p_course_cd,
2450 p_cal_type,
2451 p_ci_sequence_number,
2452 v_message_name) THEN
2453 p_fail_type := cst_course;
2454 p_message_name := v_message_name;
2455 RETURN FALSE;
2456 END IF;
2457 IF v_message_name <> NULL THEN
2458 p_fail_type := cst_course;
2459 p_message_name := v_message_name;
2460 END IF;
2461 -- Validate research unit attempt
2462 IF NOT IGS_EN_VAL_SUA.resp_val_sua_cnfrm(
2463 p_person_id,
2464 p_course_cd,
2465 p_unit_cd,
2466 p_uv_version_number,
2467 p_cal_type,
2468 p_ci_sequence_number,
2469 v_message_name ,
2470 'N' ) THEN
2471 p_fail_type := cst_course;
2472 p_message_name := v_message_name;
2473 RETURN FALSE;
2474 END IF;
2475 IF v_message_name <> NULL THEN
2476 p_fail_type := cst_course;
2477 p_message_name := v_message_name;
2478 END IF;
2479 -- Determine if the student is currently excluded from the unit
2480 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_excld(
2481 p_person_id,
2482 p_course_cd,
2483 p_unit_cd,
2484 p_cal_type,
2485 p_ci_sequence_number,
2486 v_message_name) THEN
2487 p_fail_type := cst_encumb;
2488 p_message_name := v_message_name;
2489 RETURN FALSE;
2490 END IF;
2491 IF v_message_name <> NULL THEN
2492 p_fail_type := cst_encumb;
2493 p_message_name := v_message_name;
2494 END IF;
2495 -- Fetch student course attempt details
2496 OPEN c_sca;
2497 FETCH c_sca INTO v_sca_rec;
2498 CLOSE c_sca;
2499 -- Determine if the student has already satisfied the unit through advanced
2500 -- standing.
2501 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_advstnd(
2502 p_person_id,
2503 p_course_cd,
2504 v_sca_rec.version_number,
2505 p_unit_cd,
2506 p_uv_version_number,
2507 v_message_name ,
2508 'N' ) THEN
2509 p_fail_type := cst_advstand;
2510 p_message_name := v_message_name;
2511 RETURN FALSE;
2512 END IF;
2513 IF v_message_name <> NULL THEN
2514 p_fail_type := cst_advstand;
2515 p_message_name := v_message_name;
2516 END IF;
2517 -- Determine if the attempt is in line with students
2518 -- forced location (if applicable).
2519 IF NOT IGS_EN_VAL_SUA.enrp_val_coo_loc(
2520 v_sca_rec.coo_id,
2521 p_location_cd,
2522 v_message_name) THEN
2523 p_fail_type := cst_cross;
2524 p_message_name := v_message_name;
2525 RETURN FALSE;
2526 END IF;
2527 IF v_message_name <> NULL THEN
2528 p_fail_type := cst_cross;
2529 p_message_name := v_message_name;
2530 END IF;
2531 -- Determine if the attempt is in line with students forced mode (if
2532 -- applicable).
2533 IF NOT IGS_EN_VAL_SUA.enrp_val_coo_mode(
2534 v_sca_rec.coo_id,
2535 p_unit_class,
2536 v_message_name) THEN
2537 p_fail_type := cst_cross;
2538 p_message_name := v_message_name;
2539 RETURN FALSE;
2540 END IF;
2541 IF v_message_name <> NULL THEN
2542 p_fail_type := cst_cross;
2543 p_message_name := v_message_name;
2544 END IF;
2545 -- Validate the enrolled date.
2546 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_enr_dt(
2547 p_person_id,
2548 p_course_cd,
2549 p_enrolled_dt,
2550 cst_enrolled,
2551 p_ci_end_dt,
2552 v_sca_rec.commencement_dt,
2553 v_message_name ,
2554 'N' ) THEN
2555 p_fail_type := cst_enroldt;
2556 p_message_name := v_message_name;
2557 RETURN FALSE;
2558 END IF;
2559 IF v_message_name <> NULL THEN
2560 p_fail_type := cst_enroldt;
2561 p_message_name := v_message_name;
2562 END IF;
2563 -- Determine if the student unit attempt has a teaching period
2564 -- which is prior to the commencement date of the student course attempt
2565 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_ci(
2566 p_person_id,
2567 p_course_cd,
2568 p_cal_type,
2569 p_ci_sequence_number,
2570 'ENROLLED',
2571 v_sca_rec.commencement_dt,
2572 'F', -- commencement date is known
2573 v_message_name) THEN
2574 p_fail_type := cst_teaching;
2575 p_message_name := v_message_name;
2576 RETURN FALSE;
2577 END IF;
2578 IF v_message_name <> NULL THEN
2579 p_fail_type := cst_teaching;
2580 p_message_name := v_message_name;
2581 END IF;
2582 -- Determine if the student unit attempt already exists as
2583 -- enrolled or completed with pass or incomplete result
2584 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_dupl(
2585 p_person_id,
2586 p_course_cd,
2587 p_unit_cd,
2588 p_uv_version_number,
2589 p_cal_type,
2590 p_ci_sequence_number,
2591 cst_enrolled, -- unit_attempt_status when confirming
2592 v_duplicate_course_cd,
2593 v_message_name,
2594 l_uoo_id) THEN
2595 p_fail_type := cst_duplicate;
2596 p_message_name := v_message_name;
2597 RETURN FALSE;
2598 END IF;
2599 IF v_message_name <> NULL THEN
2600 p_fail_type := cst_duplicate;
2601 p_message_name := v_message_name;
2602 END IF;
2603 -- Return the default value
2604 RETURN TRUE;
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 IF c_sca%ISOPEN THEN
2608 CLOSE c_sca;
2609 END IF;
2610 RAISE;
2611 END;
2612 END enrp_val_sua_cnfrm;
2613 --
2614 -- Validate the course against a posted change to student unit attempt.
2615 FUNCTION ENRP_VAL_SUA_CNFRM_P(
2616 p_person_id IN NUMBER ,
2617 p_course_cd IN VARCHAR2,
2618 p_course_version IN NUMBER,
2619 p_coo_id IN NUMBER ,
2620 p_cal_type IN VARCHAR2 ,
2621 p_ci_sequence_number IN NUMBER ,
2622 p_uoo_id IN NUMBER,
2623 p_fail_type OUT NOCOPY VARCHAR2 ,
2624 p_message_name OUT NOCOPY varchar2 ,
2625 p_message_name2 OUT NOCOPY varchar2 )
2626 RETURN BOOLEAN AS
2627 -------------------------------------------------------------------------------------------
2628 --Change History:
2629 --Who When What
2630 --svanukur 04-dec-2003 as part of the holds bug the procedure that checks student ecumbrances
2631 -- IGS_EN_VAL_ENCMB.enrp_val_enr_encmb is being modified to consider only term calendar.
2632 -- Hence modifying the call to this procedure to pass the load calendars under the
2633 -- superior acad calendar in reference.
2634 --ckasu 17-Nov-2004 modfied the procedure inorder to consider enrollment Category setup
2635 -- for checking the Forced location, attendance mode as apart of Program
2636 -- Transfer Build#4000939
2637 --amuthu 18-May-2006 Removed the holds validation call from here. The logic for the same has been
2638 -- moved to IGS_EN_TRANSFER_APIS.check_for_holds.
2639 -------------------------------------------------------------------------------------------
2640
2641 BEGIN -- enrp_val_sua_cnfrm_p
2642 -- Perform all post-commit (or post) validations to a given student unit
2643 -- attempt. This module is a grouping of existing validation modules.
2644 -- Performs the following modules:
2645 -- Call IGS_EN_VAL_SUA.resp_val_sua_all to check any research unit related
2646 -- issues.
2647 -- Call IGS_EN_VAL_ENCMB.enrp_val_enr_encmb to check that the student hasn't
2648 -- breached any encumbrance restrictions (eg. max cp).
2649 -- Call IGS_EN_VAL_SCA.enrp_val_coo_att to ensure that the student is in line
2650 -- with their forced attendance mode.
2651 -- -- Call enrp_val_unit_rule.{rulp_val_coreq,rulp_val_incomp,rulp_val_prereq}
2652 -- to check all unit rules - these unit attempts should be rejected
2653 -- outright, and not set to Invalid.
2654 -- as part of the holds bug the procedure that checks student ecumbrances IGS_EN_VAL_ENCMB.enrp_val_enr_encmb
2655 -- is being modified to consider only term calendar. Hence modifying the call to this procedure
2656 -- to pass the load calendars under the superior calendars in reference.
2657 DECLARE
2658 cst_research CONSTANT VARCHAR2(10) := 'RESEARCH';
2659 cst_cross CONSTANT VARCHAR2(10) := 'CROSS';
2660 cst_superior CONSTANT VARCHAR2(10) := 'SUPERIOR';
2661 v_message_name varchar2(30);
2662
2663 CURSOR c_get_teach_cal_dtls(cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
2664 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
2665 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
2666 SELECT cal_type,ci_sequence_number
2667 FROM IGS_EN_SU_ATTEMPT
2668 WHERE person_id = cp_person_id AND
2669 course_cd = cp_course_cd AND
2670 uoo_id = cp_uoo_id;
2671
2672 CURSOR c_get_load_cal_dtls(cp_teach_cal_type IGS_CA_INST.cal_type%TYPE,
2673 cp_teach_cal_seq_num IGS_CA_INST.sequence_number%TYPE) IS
2674 SELECT load_cal_type,load_ci_sequence_number
2675 FROM IGS_CA_TEACH_TO_LOAD_V
2676 WHERE teach_cal_type = cp_teach_cal_type AND
2677 teach_ci_sequence_number = cp_teach_cal_seq_num ;
2678
2679
2680 l_person_type igs_pe_person_types.person_type_code%TYPE;
2681 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
2682 l_enr_category VARCHAR2(20);
2683 l_enr_comm_type VARCHAR2(2000);
2684 l_enrolment_cat IGS_AS_SC_ATMPT_ENR.enrolment_cat%TYPE;
2685 l_enr_cal_type IGS_AS_SC_ATMPT_ENR.cal_type%TYPE;
2686 l_enr_cal_seq_num IGS_AS_SC_ATMPT_ENR.ci_sequence_number%TYPE;
2687 l_enr_method_type IGS_EN_METHOD_TYPE.enr_method_type%TYPE;
2688 l_dummy VARCHAR2(255);
2689 l_notification_flag IGS_EN_CPD_EXT.notification_flag%TYPE;
2690 l_teach_cal_type IGS_CA_INST.cal_type%TYPE;
2691 l_teach_cal_seq_num IGS_CA_INST.sequence_number%TYPE;
2692 l_load_cal_type IGS_CA_INST.cal_type%TYPE;
2693 l_load_cal_seq_num IGS_CA_INST.sequence_number%TYPE;
2694 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2695 l_message_name1 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2696 l_message_name2 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2697 l_return_status VARCHAR2(10);
2698 l_status BOOLEAN;
2699
2700 BEGIN
2701 p_fail_type := NULL;
2702 p_message_name := null;
2703 p_message_name2 := null;
2704 -- Check research unit related issues
2705 IF IGS_EN_VAL_SUA.resp_val_sua_all(
2706 p_person_id,
2707 p_course_cd,
2708 p_cal_type,
2709 p_ci_sequence_number,
2710 v_message_name) = FALSE THEN
2711 p_fail_type := cst_research;
2712 p_message_name := v_message_name;
2713 RETURN FALSE;
2714 ELSE
2715 IF v_message_name <> NULL THEN
2716 p_fail_type := cst_research;
2717 p_message_name := v_message_name;
2718 END IF;
2719 END IF;
2720 -- Determine if the student is of the correct status to have a unit attempt
2721 -- added.
2722
2723
2724 -- code added by ckasu as a part of Program transfer build bug#4000939
2725
2726 l_person_type := IGS_EN_GEN_008.enrp_get_person_type(NULL);
2727
2728 --get enrolment method type
2729
2730 igs_en_gen_017.enrp_get_enr_method(p_enr_method_type => l_enr_method_type,
2731 p_error_message => l_message_name,
2732 p_ret_status => l_return_status);
2733
2734 -- getting enrolment category , commencement type
2735
2736 l_enr_category := IGS_EN_GEN_003.enrp_get_enr_cat( p_person_id => p_person_id,
2737 p_course_cd => p_course_cd,
2738 p_cal_type => p_cal_type,
2739 p_ci_sequence_number => p_ci_sequence_number ,
2740 p_session_enrolment_cat =>NULL,
2741 p_enrol_cal_type => l_enr_cal_type ,
2742 p_enrol_ci_sequence_number => l_enr_cal_seq_num,
2743 p_commencement_type => l_enr_comm_type,
2744 p_enr_categories => l_dummy );
2745
2746 l_notification_flag := igs_ss_enr_details.get_notification(
2747 p_person_type => l_person_type,
2748 p_enrollment_category => l_enr_category,
2749 p_comm_type => l_enr_comm_type,
2750 p_enr_method_type => l_enr_method_type,
2751 p_step_group_type => 'UNIT',
2752 p_step_type => 'FLOC_CHK',
2753 p_person_id => p_person_id,
2754 p_message => l_message_name);
2755
2756 OPEN c_get_teach_cal_dtls(p_person_id,p_course_cd,p_uoo_id);
2757 FETCH c_get_teach_cal_dtls INTO l_teach_cal_type,l_teach_cal_seq_num;
2758 IF c_get_teach_cal_dtls%FOUND THEN
2759 CLOSE c_get_teach_cal_dtls;
2760 OPEN c_get_load_cal_dtls(l_teach_cal_type,l_teach_cal_seq_num);
2761 FETCH c_get_load_cal_dtls INTO l_load_cal_type,l_load_cal_seq_num;
2762 CLOSE c_get_load_cal_dtls;
2763 ELSE
2764 CLOSE c_get_teach_cal_dtls;
2765 END IF; -- end of c_get_teach_cal_dtls%FOUND IF THEN
2766
2767 IF l_notification_flag IS NOT NULL THEN
2768
2769 l_status := IGS_EN_ELGBL_UNIT.eval_unit_forced_location(p_person_id,
2770 l_load_cal_type,
2771 l_load_cal_seq_num,
2772 p_uoo_id,
2773 p_course_cd,
2774 p_course_version,
2775 l_message_name1,
2776 l_notification_flag,
2777 'JOB' -- parameter for calling_obj column
2778 );
2779 IF l_notification_flag = 'DENY' AND l_message_name1 IS NOT NULL THEN
2780 p_message_name := l_message_name1;
2781 RETURN FALSE;
2782 END IF;
2783
2784 END IF;
2785
2786 l_notification_flag := igs_ss_enr_details.get_notification(
2787 p_person_type => l_person_type,
2788 p_enrollment_category => l_enr_category,
2789 p_comm_type => l_enr_comm_type,
2790 p_enr_method_type => l_enr_method_type,
2791 p_step_group_type => 'UNIT',
2792 p_step_type => 'FATD_MODE',
2793 p_person_id => p_person_id,
2794 p_message => l_message_name);
2795
2796
2797
2798 IF l_notification_flag IS NOT NULL THEN
2799
2800 l_status := IGS_EN_ELGBL_UNIT.eval_unit_forced_mode (p_person_id,
2801 l_load_cal_type,
2802 l_load_cal_seq_num,
2803 p_uoo_id,
2804 p_course_cd,
2805 p_course_version,
2806 l_message_name2,
2807 l_notification_flag,
2808 'JOB' -- parameter for calling_obj column
2809 );
2810 IF l_notification_flag = 'DENY' AND l_message_name2 IS NOT NULL THEN
2811 p_message_name := l_message_name2;
2812 RETURN FALSE;
2813 END IF;
2814
2815 END IF;
2816
2817
2818 -- end of code added by ckasu as a part of Program transfer build bug#4000939
2819
2820 RETURN TRUE;
2821
2822 END;
2823 EXCEPTION
2824 WHEN OTHERS THEN
2825 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2826 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_cnfrm_p');
2827 IGS_GE_MSG_STACK.ADD;
2828 App_Exception.Raise_Exception;
2829 END enrp_val_sua_cnfrm_p;
2830 --
2831 -- To validate SUA override credit point values
2832 -- New parameter p_uoo_id is added w.r.t. bug num: 2375757 by kkillams
2833 FUNCTION enrp_val_sua_ovrd_cp(
2834 p_unit_cd IN VARCHAR2 ,
2835 p_version_number IN NUMBER ,
2836 p_override_enrolled_cp IN NUMBER ,
2837 p_override_achievable_cp IN NUMBER ,
2838 p_override_eftsu IN NUMBER ,
2839 p_message_name OUT NOCOPY varchar2,
2840 p_uoo_id IN NUMBER,
2841 p_no_assessment_ind IN VARCHAR2)
2842 RETURN BOOLEAN AS
2843
2844 BEGIN -- enrp_val_sua_ovrd_cp
2845 -- Validate the override credit point and EFTSU values against the
2846 -- constraints set in the IGS_PS_UNIT_VER table.
2847 DECLARE
2848 v_points_override_ind IGS_PS_UNIT_VER.points_override_ind%TYPE;
2849 v_points_min IGS_PS_UNIT_VER.points_min%TYPE;
2850 v_points_max IGS_PS_UNIT_VER.points_max%TYPE;
2851 v_points_increment IGS_PS_UNIT_VER.points_increment%TYPE;
2852 CURSOR c_uv IS
2853 SELECT uv.points_override_ind,
2854 uv.points_min,
2855 uv.points_max,
2856 uv.points_increment
2857 FROM IGS_PS_UNIT_VER uv
2858 WHERE unit_cd = p_unit_cd AND
2859 version_number = p_version_number;
2860 --New cursor is added w.r.t. bug 2375757 by kkillams
2861 --To get the details override credit points at unit section level
2862 CURSOR c_usv IS
2863 SELECT usv.minimum_credit_points,
2864 usv.maximum_credit_points,
2865 usv.variable_increment
2866 FROM IGS_PS_USEC_CPS usv
2867 WHERE usv.uoo_id = p_uoo_id;
2868
2869 BEGIN
2870 p_message_name := NULL;
2871 -- If none of the override values are set then there is no validation to occur.
2872 IF p_override_enrolled_cp IS NULL AND
2873 p_override_achievable_cp IS NULL AND
2874 p_override_eftsu IS NULL THEN
2875 RETURN TRUE;
2876 END IF;
2877 -- Select details from unit version.
2878 OPEN c_uv;
2879 FETCH c_uv INTO v_points_override_ind,
2880 v_points_min,
2881 v_points_max,
2882 v_points_increment;
2883 IF (c_uv%NOTFOUND) THEN
2884 CLOSE c_uv;
2885 RETURN TRUE;
2886 END IF;
2887 CLOSE c_uv;
2888 -- If override points not allowed return error.
2889 IF v_points_override_ind = 'N' THEN
2890 p_message_name := 'IGS_EN_OVERRIDE_EFTSU_VALUES';
2891 RETURN FALSE;
2892 ELSE
2893 --If min and max credit points defined at unit section level
2894 --than override the values w.r.t bug no# 2375757 by kkillams
2895 IF p_uoo_id IS NOT NULL THEN
2896 OPEN c_usv;
2897 FETCH c_usv INTO v_points_min,
2898 v_points_max,
2899 v_points_increment;
2900 CLOSE c_usv;
2901 END IF;
2902 END IF;
2903
2904 -- If override cp is set and not in accordance with unit version ranges.
2905 IF p_override_enrolled_cp IS NOT NULL THEN
2906 IF p_override_enrolled_cp < v_points_min OR
2907 p_override_enrolled_cp > v_points_max OR
2908 ( MOD(p_override_enrolled_cp, v_points_increment) <> MOD(v_points_min, v_points_increment) ) THEN
2909 p_message_name := 'IGS_EN_OVERRIDE_ENR_CREDITPNT';
2910 RETURN FALSE;
2911 END IF;
2912 END IF;
2913 -- If override achievable cp is set and not in accordance with unit version
2914 -- ranges.
2915 IF p_override_achievable_cp IS NOT NULL THEN
2916 -- added as part of ENCR026 if the unit is an audit unit and the achievable CP is zero
2917 -- then should the acheivable CP should not be validated.
2918 IF NOT (NVL(p_no_assessment_ind,'N') = 'Y' AND p_override_achievable_cp = 0) THEN
2919 IF p_override_achievable_cp < v_points_min OR
2920 p_override_achievable_cp > v_points_max OR
2921 MOD (p_override_achievable_cp, v_points_increment) <> 0 THEN
2922 p_message_name := 'IGS_EN_OVERRIDE_ACHCRD_POINT';
2923 RETURN FALSE;
2924 END IF;
2925 END IF;
2926 END IF;
2927 RETURN TRUE;
2928 EXCEPTION
2929 WHEN OTHERS THEN
2930 IF (c_uv%ISOPEN) THEN
2931 CLOSE c_uv;
2932 END IF;
2933 RAISE;
2934 END;
2935 EXCEPTION
2936 WHEN OTHERS THEN
2937 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2938 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp');
2939 IGS_GE_MSG_STACK.ADD;
2940 App_Exception.Raise_Exception;
2941 END enrp_val_sua_ovrd_cp;
2942 --
2943 -- To validate SUA rule waived date.
2944 FUNCTION enrp_val_sua_rule_wv(
2945 p_rule_waived_dt IN DATE ,
2946 p_enrolled_dt IN DATE ,
2947 p_rule_waived_person_id IN OUT NOCOPY NUMBER ,
2948 p_message_name OUT NOCOPY varchar2 )
2949 RETURN BOOLEAN AS
2950
2951
2952 BEGIN
2953 BEGIN
2954 --- Set the default message number
2955 p_message_name := null;
2956
2957
2958 --- Check that rule waived date is greater than the enrolled date if it exists
2959 IF p_enrolled_dt IS NOT NULL AND p_rule_waived_dt < TRUNC(p_enrolled_dt) THEN
2960 p_message_name := 'IGS_EN_RULE_WAV_DT_GE_ENRL_DT';
2961 RETURN FALSE;
2962 END IF;
2963 --- Return the default value
2964 RETURN TRUE;
2965 END;
2966 EXCEPTION
2967 WHEN OTHERS THEN
2968 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2969 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_rule_wv');
2970 IGS_GE_MSG_STACK.ADD;
2971 App_Exception.Raise_Exception;
2972 END enrp_val_sua_rule_wv;
2973
2974 --
2975 -- To validate SUA unit offering option.
2976 FUNCTION enrp_val_sua_uoo(
2977 p_unit_cd IN VARCHAR2 ,
2978 p_version_number IN NUMBER ,
2979 p_cal_type IN VARCHAR2 ,
2980 p_ci_sequence_number IN NUMBER ,
2981 p_location_cd IN VARCHAR2 ,
2982 p_unit_class IN VARCHAR2 ,
2983 p_message_name OUT NOCOPY VARCHAR2 ,
2984 p_legacy IN VARCHAR2)
2985 RETURN BOOLEAN AS
2986 gv_other_details VARCHAR2(255);
2987 BEGIN
2988 DECLARE
2989 CURSOR c_get_uv_status IS
2990 SELECT IGS_PS_UNIT_STAT.s_unit_status
2991 FROM IGS_PS_UNIT_VER,
2992 IGS_PS_UNIT_STAT
2993 WHERE IGS_PS_UNIT_VER.unit_cd = p_unit_cd AND
2994 IGS_PS_UNIT_VER.version_number = p_version_number AND
2995 IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;
2996 CURSOR c_val_uoo IS
2997 SELECT offered_ind
2998 FROM IGS_PS_UNIT_OFR_OPT
2999 WHERE unit_cd = p_unit_cd AND
3000 version_number = p_version_number AND
3001 cal_type = p_cal_type AND
3002 ci_sequence_number = p_ci_sequence_number AND
3003 location_cd = p_location_cd AND
3004 unit_class = p_unit_class;
3005 cst_no IGS_PS_UNIT_OFR_OPT.offered_ind%TYPE := 'N';
3006 cst_active IGS_PS_UNIT_STAT.s_unit_status%TYPE := 'ACTIVE';
3007 cst_inactive IGS_PS_UNIT_STAT.s_unit_status%TYPE := 'INACTIVE';
3008 v_uv_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
3009 v_offered_ind IGS_PS_UNIT_OFR_OPT.offered_ind%TYPE;
3010 BEGIN
3011 --- Set the default message number
3012 p_message_name := null;
3013 --- Validate that the unit version is ACTIVE.
3014 OPEN c_get_uv_status;
3015 FETCH c_get_uv_status INTO v_uv_status;
3016 IF c_get_uv_status%NOTFOUND THEN
3017 IF p_legacy <> 'Y' THEN
3018 CLOSE c_get_uv_status;
3019 RETURN TRUE;
3020 END IF ;
3021 END IF;
3022 CLOSE c_get_uv_status;
3023 IF v_uv_status <> cst_active THEN
3024 IF p_legacy <> 'Y' THEN
3025 p_message_name := 'IGS_EN_UNITVERSION_INACTIVE';
3026 RETURN FALSE;
3027 ELSIF v_uv_status <> cst_inactive AND p_legacy = 'Y' THEN
3028 p_message_name := 'IGS_EN_UNITVERSION_INACTIVE';
3029 Fnd_Message.Set_name('IGS','IGS_EN_UNITVERSION_INACTIVE');
3030 FND_MSG_PUB.ADD;
3031 END IF ;
3032 END IF;
3033 --- Validate that the unit offering option is offered.
3034 OPEN c_val_uoo;
3035 FETCH c_val_uoo INTO v_offered_ind;
3036 IF c_val_uoo%NOTFOUND THEN
3037 CLOSE c_val_uoo;
3038 RETURN TRUE;
3039 END IF;
3040 CLOSE c_val_uoo;
3041 IF v_offered_ind = cst_no THEN
3042 p_message_name := 'IGS_EN_STUD_UNT_OFF_NOT_AVALA';
3043 IF p_legacy <> 'Y' THEN
3044 RETURN FALSE;
3045 ELSE
3046 Fnd_Message.Set_name('IGS',p_message_name );
3047 FND_MSG_PUB.ADD;
3048 END IF;
3049 END IF;
3050 --- Set the default return value
3051 RETURN TRUE;
3052 END;
3053 EXCEPTION
3054 WHEN OTHERS THEN
3055 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3056 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_uoo');
3057 IGS_GE_MSG_STACK.ADD;
3058 App_Exception.Raise_Exception;
3059 END enrp_val_sua_uoo;
3060 --
3061 -- To validate sca location code against coo restriction
3062 FUNCTION ENRP_VAL_COO_LOC(
3063 p_coo_id IN NUMBER ,
3064 p_unit_location_cd IN VARCHAR2 ,
3065 p_message_name OUT NOCOPY varchar2 )
3066 RETURN boolean AS
3067 BEGIN
3068 DECLARE
3069 v_coo_rec_found BOOLEAN;
3070 v_unit_mode IGS_AS_UNIT_CLASS.unit_mode%TYPE;
3071 v_s_unit_mode IGS_AS_UNIT_MODE.s_unit_mode%TYPE;
3072 v_govt_attend_mode IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
3073 v_other_detail VARCHAR(255);
3074 CURSOR c_coo(
3075 cp_coo_id IGS_EN_STDNT_PS_ATT.coo_id%TYPE) IS
3076 SELECT *
3077 FROM IGS_PS_OFR_OPT
3078 WHERE IGS_PS_OFR_OPT.coo_id = cp_coo_id
3079 AND IGS_PS_OFR_OPT.delete_flag = 'N';
3080 BEGIN
3081 -- This module validates the nominated unit location code against
3082 -- course_offering_option location code for the enrolled course
3083 p_message_name := null;
3084 v_coo_rec_found := FALSE;
3085 FOR v_coo_rec IN c_coo(p_coo_id) LOOP
3086 v_coo_rec_found := TRUE;
3087 IF v_coo_rec.forced_location_ind = 'Y' THEN
3088 IF (p_unit_location_cd<> v_coo_rec.location_cd) THEN
3089 p_message_name := 'IGS_EN_UNT_LOC_CONFLICTS';
3090 RETURN FALSE;
3091 END IF;
3092 END IF;
3093 END LOOP;
3094 IF(NOT v_coo_rec_found) THEN
3095 RETURN TRUE;
3096 END IF;
3097 RETURN TRUE;
3098 END;
3099 END enrp_val_coo_loc;
3100 --
3101 -- To validate the sca att mode against coo restriction
3102 FUNCTION ENRP_VAL_COO_MODE(
3103 p_coo_id IN NUMBER ,
3104 p_unit_class IN VARCHAR2 ,
3105 p_message_name OUT NOCOPY varchar2 )
3106 RETURN boolean AS
3107 BEGIN
3108 DECLARE
3109 v_coo_rec_found BOOLEAN;
3110 v_unit_mode IGS_AS_UNIT_CLASS.unit_mode%TYPE;
3111 v_s_unit_mode IGS_AS_UNIT_MODE.s_unit_mode%TYPE;
3112 v_govt_attend_mode IGS_EN_ATD_MODE.govt_attendance_mode%TYPE;
3113 v_other_detail VARCHAR(255);
3114 CURSOR c_coo(
3115 cp_coo_id IGS_EN_STDNT_PS_ATT.coo_id%TYPE) IS
3116 SELECT *
3117 FROM IGS_PS_OFR_OPT
3118 WHERE IGS_PS_OFR_OPT.coo_id = cp_coo_id
3119 AND IGS_PS_OFR_OPT.delete_flag = 'N';
3120 CURSOR c_unit_class(
3121 cp_unit_class IGS_EN_SU_ATTEMPT.unit_class%TYPE) IS
3122 SELECT unit_mode
3123 FROM IGS_AS_UNIT_CLASS
3124 WHERE IGS_AS_UNIT_CLASS.unit_class = cp_unit_class AND IGS_AS_UNIT_CLASS.closed_ind = 'N';
3125 CURSOR c_unit_mode(
3126 cp_unit_mode IGS_AS_UNIT_CLASS.unit_mode%TYPE) IS
3127 SELECT s_unit_mode
3128 FROM IGS_AS_UNIT_MODE
3129 WHERE IGS_AS_UNIT_MODE.unit_mode = cp_unit_mode;
3130 CURSOR c_attend_mode(
3131 cp_attend_mode IGS_EN_ATD_MODE.attendance_mode%TYPE) IS
3132 SELECT govt_attendance_mode
3133 FROM IGS_EN_ATD_MODE
3134 WHERE IGS_EN_ATD_MODE.attendance_mode = cp_attend_mode;
3135 BEGIN
3136 -- This module validates the nominated unit class against
3137 -- course_offering_option attandance mode for the enrolled course
3138 p_message_name := null;
3139 v_coo_rec_found := FALSE;
3140 FOR v_coo_rec IN c_coo(p_coo_id) LOOP
3141 v_coo_rec_found := TRUE;
3142 IF v_coo_rec.forced_att_mode_ind = 'Y' THEN
3143 OPEN c_unit_class(
3144 p_unit_class);
3145 FETCH c_unit_class INTO v_unit_mode;
3146 CLOSE c_unit_class;
3147 OPEN c_unit_mode(
3148 v_unit_mode);
3149 FETCH c_unit_mode INTO v_s_unit_mode;
3150 CLOSE c_unit_mode;
3151 OPEN c_attend_mode(
3152 v_coo_rec.attendance_mode);
3153 FETCH c_attend_mode INTO v_govt_attend_mode;
3154 CLOSE c_attend_mode;
3155 IF v_s_unit_mode = 'ON' THEN
3156 IF (v_govt_attend_mode <> '1' AND
3157 v_govt_attend_mode <> '3') THEN
3158 p_message_name := 'IGS_EN_UNIT_CD_CONFLICTS';
3159 RETURN FALSE;
3160 END IF;
3161 ELSIF v_s_unit_mode = 'OFF' THEN
3162 IF (v_govt_attend_mode <> '2' AND
3163 v_govt_attend_mode <> '3') THEN
3164 p_message_name := 'IGS_EN_UNIT_CD_CONFLICTS';
3165 RETURN FALSE;
3166 END IF;
3167 END IF;
3168 END IF;
3169 END LOOP;
3170 IF(NOT v_coo_rec_found) THEN
3171 RETURN TRUE;
3172 END IF;
3173 RETURN TRUE;
3174 END;
3175 END enrp_val_coo_mode;
3176 --
3177
3178 --
3179 -- To validate for student unit attempt being duplicated
3180 FUNCTION enrp_val_sua_dupl(
3181 p_person_id IN NUMBER ,
3182 p_course_cd IN VARCHAR2 ,
3183 p_unit_cd IN VARCHAR2 ,
3184 p_uv_version_number IN NUMBER ,
3185 p_cal_type IN VARCHAR2 ,
3186 p_ci_sequence_number IN NUMBER ,
3187 p_unit_attempt_status IN VARCHAR2 ,
3188 p_duplicate_course_cd OUT NOCOPY VARCHAR2 ,
3189 p_message_name OUT NOCOPY VARCHAR2,
3190 p_uoo_id IN NUMBER)
3191 RETURN BOOLEAN AS
3192 -------------------------------------------------------------------------------------------
3193 -- enrp_val_sua_dupl
3194 -- This module validates that enrolled student unit attempt:
3195 -- * does not already exist for the student in any of their course attempts,
3196 -- is enrolled and being studied concurrently. note: Allow for duplicate if
3197 -- student_course_transfer between the two course attempts.
3198 -- This module warns if:
3199 -- * the unit attempt has already been completed in any of the course
3200 -- attempts of the student with a s_result_type of ?PASS? or ?INCOMPLETE?.
3201 --Change History:
3202 --Who When What
3203 --kkillams 28-04-2003 Added new parameter p_uoo_id to the function
3204 -- Modified the c_sua cursor where clause due
3205 -- to change in pk of student unit attempt table
3206 -- w.r.t. bug number 2829262
3207 -- rvivekan 09-sep-2003 Modified the behaviour of repeatable_ind column
3208 -- in igs_ps_unit_ver table. PSP integration build #3052433
3209 -- ckasu 28-NOV-2005 modified v_message_name <> NULL to v_message_name IS NOT NULL
3210 -- as a partof bug #4666102
3211 -------------------------------------------------------------------------------------------
3212
3213 BEGIN
3214 DECLARE
3215 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
3216 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3217 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
3218 cst_pass CONSTANT VARCHAR2(10) := 'PASS';
3219 cst_incomp CONSTANT VARCHAR2(10) := 'INCOMP';
3220 CURSOR c_daiv (
3221 cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
3222 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
3223 SELECT UNIQUE(daiv.alias_val) alias_val
3224 FROM IGS_CA_DA_INST_V daiv,
3225 IGS_GE_S_GEN_CAL_CON sgcc
3226 WHERE daiv.cal_type = cp_cal_type AND
3227 daiv.ci_sequence_number = cp_ci_sequence_number AND
3228 daiv.dt_alias = sgcc.census_dt_alias AND
3229 sgcc.s_control_num = 1;
3230 CURSOR c_sua(cp_location_cd IGS_EN_SU_ATTEMPT.LOCATION_CD%TYPE,
3231 cp_unit_class IGS_EN_SU_ATTEMPT.UNIT_CLASS%TYPE)IS
3232 SELECT sua.course_cd,
3233 sua.cal_type,
3234 sua.ci_sequence_number,
3235 sua.unit_attempt_status,
3236 sua.uoo_id
3237 FROM IGS_EN_SU_ATTEMPT sua
3238 WHERE sua.person_id = p_person_id AND
3239 sua.unit_cd = p_unit_cd AND
3240 sua.version_number = p_uv_version_number AND
3241 sua.location_cd = cp_location_cd AND
3242 sua.unit_class = cp_unit_class AND
3243 (sua.course_cd <> p_course_cd OR
3244 sua.cal_type <> p_cal_type OR
3245 sua.ci_sequence_number <> p_ci_sequence_number) AND
3246 sua.unit_attempt_status IN (cst_enrolled,
3247 cst_completed,
3248 cst_discontin);
3249 CURSOR c_sua_d IS
3250 SELECT sua.location_cd,
3251 sua.unit_class
3252 FROM IGS_EN_SU_ATTEMPT sua
3253 WHERE sua.person_id = p_person_id AND
3254 sua.course_cd = p_course_cd AND
3255 sua.uoo_id = p_uoo_id;
3256 CURSOR c_sct (
3257 cp_course_cd IGS_PS_STDNT_TRN.transfer_course_cd%TYPE) IS
3258 SELECT 'x'
3259 FROM IGS_PS_STDNT_TRN sct
3260 WHERE sct.person_id = p_person_id AND
3261 sct.course_cd = p_course_cd AND
3262 sct.transfer_course_cd = cp_course_cd;
3263 CURSOR c_uv IS
3264 SELECT uv.repeatable_ind
3265 FROM IGS_PS_UNIT_VER uv
3266 WHERE uv.unit_cd = p_unit_cd AND
3267 uv.version_number = p_uv_version_number;
3268 v_sct_exists VARCHAR2(1);
3269 TYPE r_alias_val_record_type IS RECORD(
3270 alias_val IGS_CA_DA_INST_V.alias_val%TYPE);
3271 r_alias_val_record r_alias_val_record_type;
3272 TYPE t_alias_val_type IS TABLE OF r_alias_val_record%TYPE
3273 INDEX BY BINARY_INTEGER;
3274 v_alias_val_table t_alias_val_type;
3275 v_alias_val_index BINARY_INTEGER := 0;
3276 v_index BINARY_INTEGER := 0;
3277 v_av_found BOOLEAN := FALSE;
3278 v_message_name VARCHAR2(30);
3279 rec_sua_d c_sua_d%ROWTYPE;
3280 v_s_result_type IGS_LOOKUPS_VIEW.lookup_code%TYPE := NULL;
3281 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
3282 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
3283 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
3284 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
3285 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
3286 v_origin_course_cd IGS_AS_SU_STMPTOUT.course_cd%TYPE;
3287 v_repeatable_ind IGS_PS_UNIT_VER.repeatable_ind%TYPE;
3288 v_duplicate_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
3289 BEGIN
3290 -- Set the default message number and duplicate course code
3291 p_message_name := null;
3292 p_duplicate_course_cd := NULL;
3293 IF p_unit_attempt_status = cst_enrolled THEN
3294 -- Determine if the unit version is not repeatable
3295 OPEN c_uv;
3296 FETCH c_uv INTO v_repeatable_ind;
3297 IF (c_uv%NOTFOUND) THEN
3298 CLOSE c_uv;
3299 RETURN TRUE;
3300 END IF;
3301 CLOSE c_uv;
3302 -- Determine census date(s) in unit teaching period
3303 FOR v_daiv_rec IN c_daiv(
3304 p_cal_type,
3305 p_ci_sequence_number) LOOP
3306 -- add a new record to the exceptions table
3307 v_alias_val_index := v_alias_val_index + 1;
3308 v_alias_val_table(v_alias_val_index).alias_val := v_daiv_rec.alias_val;
3309 END LOOP; -- v_daiv_rec IN c_daiv
3310
3311 OPEN c_sua_d;
3312 FETCH c_sua_d INTO rec_sua_d;
3313 CLOSE c_sua_d;
3314 FOR v_sua_rec IN c_sua(rec_sua_d.location_cd,
3315 rec_sua_d.unit_class)
3316 LOOP
3317 IF v_sua_rec.unit_attempt_status = cst_enrolled THEN
3318 -- Determine if another enrolled attempt exists
3319 -- for this unit across all course attempts for the student
3320 IF v_sua_rec.cal_type = p_cal_type AND
3321 v_sua_rec.ci_sequence_number = p_ci_sequence_number THEN
3322 -- Check that this isn't a result of a course Transfer,
3323 -- and is therefore valid
3324 OPEN c_sct(
3325 v_sua_rec.course_cd);
3326 FETCH c_sct INTO v_sct_exists;
3327 IF c_sct%NOTFOUND THEN
3328 -- unit is currently being studied against another course
3329 CLOSE c_sct;
3330 v_duplicate_course_cd := v_sua_rec.course_cd;
3331 v_message_name := 'IGS_EN_UNITVER_CURR_ATTEMPTED';
3332 EXIT;
3333 END IF;
3334 CLOSE c_sct;
3335 -- Continue processing
3336 ELSE
3337 -- Determine if the matched unit is being studied concurrently
3338 -- Determine census date(s) in matched teaching period
3339 -- and check if same date value exists for the matched unit
3340 FOR v_daiv_rec IN c_daiv(
3341 v_sua_rec.cal_type, v_sua_rec.ci_sequence_number) LOOP
3342 v_index := 0;
3343 v_av_found := FALSE;
3344 WHILE v_index < v_alias_val_index AND
3345 NOT v_av_found LOOP
3346 v_index := v_index + 1;
3347 IF v_alias_val_table(v_index).alias_val =
3348 v_daiv_rec.alias_val THEN
3349 v_av_found := TRUE;
3350 END IF;
3351 END LOOP;
3352 IF v_av_found THEN
3353 v_duplicate_course_cd := v_sua_rec.course_cd;
3354 v_message_name := 'IGS_EN_UNITVER_CURR_ATTEMPTED';
3355 EXIT;
3356 END IF;
3357 END LOOP; -- v_daiv_rec IN c_daiv2
3358 END IF;
3359 END IF;
3360 IF v_message_name IS NOT NULL THEN
3361 EXIT;
3362 END IF;
3363 IF v_sua_rec.unit_attempt_status IN (cst_completed,
3364 cst_discontin) THEN
3365 IF v_repeatable_ind = 'X' THEN
3366 OPEN c_sct(v_sua_rec.course_cd);
3367 FETCH c_sct INTO v_sct_exists;
3368 IF c_sct%NOTFOUND THEN
3369 CLOSE c_sct;
3370 -- Continue processing
3371 -- Warn if the unit version is not repeatable
3372 -- and the unit has already been completed with
3373 -- a result type of pass or incomplete
3374 v_s_result_type := IGS_AS_GEN_003.ASSP_GET_SUA_OUTCOME(p_person_id,
3375 v_sua_rec.course_cd,
3376 p_unit_cd,
3377 v_sua_rec.cal_type,
3378 v_sua_rec.ci_sequence_number,
3379 v_sua_rec.unit_attempt_status,
3380 'N', -- finalised indicator
3381 v_outcome_dt,
3382 v_grading_schema_cd,
3383 v_gs_version_number,
3384 v_grade,
3385 v_mark,
3386 v_origin_course_cd,
3387 v_sua_rec.uoo_id,
3388 --added by LKAKI---
3389 'N');
3390 IF v_s_result_type = cst_pass THEN
3391 v_duplicate_course_cd := v_sua_rec.course_cd;
3392 v_message_name := 'IGS_EN_UNITVER_STUD_PASSED';
3393 EXIT;
3394 ELSIF v_s_result_type = cst_incomp THEN
3395 v_duplicate_course_cd := v_sua_rec.course_cd;
3396 v_message_name := 'IGS_EN_UNITVER_INCOMPL_RESULT';
3397 EXIT;
3398 END IF;
3399 ELSE
3400 CLOSE c_sct;
3401 END IF;
3402
3403
3404 END IF;
3405 END IF;
3406 END LOOP; -- v_sua_rec IN c_sua
3407 END IF;
3408 IF v_message_name IS NOT NULL THEN
3409 p_duplicate_course_cd := v_duplicate_course_cd;
3410 p_message_name := v_message_name;
3411 IF v_message_name IN ('IGS_EN_UNITVER_INCOMPL_RESULT',
3412 'IGS_EN_UNITVER_STUD_PASSED') THEN
3413 RETURN TRUE;
3414 END IF;
3415 RETURN FALSE;
3416 END IF;
3417 RETURN TRUE;
3418 EXCEPTION
3419 WHEN OTHERS THEN
3420 IF c_uv%ISOPEN THEN
3421 CLOSE c_uv;
3422 END IF;
3423 IF c_daiv%ISOPEN THEN
3424 CLOSE c_daiv;
3425 END IF;
3426 IF c_sua%ISOPEN THEN
3427 CLOSE c_sua;
3428 END IF;
3429 IF c_sct%ISOPEN THEN
3430 CLOSE c_sct;
3431 END IF;
3432 RAISE;
3433 END;
3434 END enrp_val_sua_dupl;
3435
3436 PROCEDURE enr_sub_units(
3437 p_person_id IN NUMBER ,
3438 p_course_cd IN VARCHAR2 ,
3439 p_uoo_id IN NUMBER,
3440 p_waitlist_flag IN VARCHAR2,
3441 p_load_cal_type IN VARCHAR2,
3442 p_load_seq_num IN NUMBER,
3443 p_enrollment_date IN DATE ,
3444 p_enrollment_method IN VARCHAR2,
3445 p_enr_uoo_ids IN VARCHAR2,
3446 p_uoo_ids OUT NOCOPY VARCHAR2,
3447 p_waitlist_uoo_ids OUT NOCOPY VARCHAR2,
3448 p_failed_uoo_ids OUT NOCOPY VARCHAR2) AS
3449 -------------------------------------------------------------------------------------------
3450 --Created by : Satya Vanukuri, Oracle IDC
3451 --Date created: 13-oct-2003
3452 -- Purpose : Created as part of placements build .
3453 --procedure enrolls subordinate unit sections that are marked as default enroll
3454 --if the student is attempting superior unit section
3455 --if a subordinate units section is explicitly selected by the user along with the superior units
3456 --then no other sub units are enrolled
3457 -------------------------------------------------------------------------------------------
3458 CURSOR cur_sup is
3459 SELECT 1 FROM IGS_PS_UNIT_OFR_OPT
3460 WHERE uoo_id = p_uoo_id
3461 AND relation_type = 'SUPERIOR' ;
3462
3463 l_check_sup NUMBER(1);
3464
3465 TYPE sub_ref_cur IS REF CURSOR;
3466 cur_sub sub_ref_cur;
3467 cur_sub1 sub_ref_cur;
3468 sub_stmt VARCHAR2(1000);
3469
3470 sub_stmt1 VARCHAR2(1000);
3471
3472 CURSOR get_sub_usecs IS
3473 SELECT * FROM IGS_PS_UNIT_OFR_OPT
3474 WHERE sup_uoo_id = p_uoo_id AND
3475 default_enroll_flag = 'Y' AND
3476 relation_type = 'SUBORDINATE';
3477
3478 CURSOR cur_person_number IS
3479 SELECT party_number from hz_parties
3480 WHERE party_id = p_person_id;
3481
3482 l_sub_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
3483 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
3484 l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE;
3485 l_waitlist_ind VARCHAR2(1);
3486 l_return_status VARCHAR2(1);
3487 l_err_message VARCHAR2(1000);
3488 l_message_name VARCHAR2(1000);
3489 l_person_number IGS_PE_PERSON.person_number%TYPE;
3490 l_enroll BOOLEAN;
3491 BEGIN
3492 sub_stmt := 'SELECT uoo_id FROM IGS_PS_UNIT_OFR_OPT WHERE sup_uoo_id = :p_uoo_id AND
3493 relation_type = ''SUBORDINATE'' AND uoo_id in ('||p_enr_uoo_ids||')';
3494
3495 sub_stmt1 := 'SELECT uoo_id FROM IGS_PS_UNIT_OFR_OPT WHERE sup_uoo_id = :p_uoo_id AND
3496 relation_type = ''SUBORDINATE'' AND uoo_id = :1';
3497
3498 p_uoo_ids := NULL;
3499 p_waitlist_uoo_ids := NULL;
3500 p_failed_uoo_ids := NULL;
3501 l_check_sup := NULL;
3502
3503
3504 --Check whether context unit section is a superior unit attempt
3505 OPEN cur_sup;
3506 FETCH cur_sup INTO l_check_sup;
3507 CLOSE cur_sup;
3508
3509
3510 IF l_check_sup IS NULL THEN
3511 RETURN;
3512
3513 ELSE
3514 IF p_enr_uoo_ids IS NOT NULL THEN
3515 --then check whether anyone of uoo_ids in the list p_enr_uoo_ids is subordinate to the context uoo_id
3516 IF(INSTR(p_enr_uoo_ids,',',1) = 0) THEN
3517 l_uoo_id := TO_NUMBER(p_enr_uoo_ids);
3518 OPEN cur_sub1 for sub_stmt1 using p_uoo_id,l_uoo_id;
3519 FETCH cur_sub1 INTO l_sub_id;
3520 CLOSE cur_sub1;
3521 ELSE
3522
3523 OPEN cur_sub FOR sub_stmt using p_uoo_id;
3524 FETCH cur_sub INTO l_sub_id;
3525 CLOSE cur_sub;
3526 END IF;
3527
3528 IF l_sub_id IS NOT NULL THEN
3529 RETURN;
3530 END IF;
3531 END IF;
3532 END IF;
3533 --none of the sub units have been chosen, hence select all the sub units that have default enroll set
3534
3535
3536 FOR sub_usecs_rec IN get_sub_usecs LOOP
3537 l_enroll := TRUE;
3538 l_message_name := NULL;
3539 --call api to validate enrollment window
3540 IF NOT IGS_EN_GEN_004.ENRP_GET_REC_WINDOW(
3541 sub_usecs_rec.cal_type,
3542 sub_usecs_rec.ci_sequence_number,
3543 nvl(p_enrollment_date,SYSDATE),
3544 sub_usecs_rec.uoo_id,
3545 l_message_name) THEN
3546
3547 IF p_failed_uoo_ids IS NOT NULL THEN
3548 p_failed_uoo_ids := p_failed_uoo_ids ||','||sub_usecs_rec.uoo_id;
3549 ELSE
3550 p_failed_uoo_ids := sub_usecs_rec.uoo_id;
3551 END IF;
3552
3553
3554 ELSE
3555 --call api to validate variation window
3556 IF NOT IGS_EN_GEN_008.ENRP_GET_VAR_WINDOW(
3557 sub_usecs_rec.cal_type,
3558 sub_usecs_rec.ci_sequence_number,
3559 nvl(p_enrollment_date,SYSDATE),
3560 sub_usecs_rec.uoo_id) THEN
3561
3562 IF p_failed_uoo_ids IS NOT NULL THEN
3563 p_failed_uoo_ids := p_failed_uoo_ids ||','||sub_usecs_rec.uoo_id;
3564 ELSE
3565 p_failed_uoo_ids := sub_usecs_rec.uoo_id;
3566 END IF;
3567
3568 ELSE
3569 l_unit_section_status := NULL;
3570 l_waitlist_ind := NULL;
3571 --check seat availibility for sub unit
3572 igs_en_gen_015.get_usec_status(
3573 p_uoo_id => sub_usecs_rec.uoo_id,
3574 p_person_id => p_person_id,
3575 p_unit_section_status => l_unit_section_status,
3576 p_waitlist_ind => l_waitlist_ind,
3577 p_load_cal_type => p_load_cal_type,
3578 p_load_ci_sequence_number => p_load_seq_num,
3579 p_course_cd => p_course_cd) ;
3580
3581 IF l_waitlist_ind IS NULL THEN
3582 IF p_failed_uoo_ids IS NOT NULL THEN
3583 p_failed_uoo_ids := p_failed_uoo_ids ||','||sub_usecs_rec.uoo_id;
3584 ELSE
3585 p_failed_uoo_ids := sub_usecs_rec.uoo_id;
3586 END IF;
3587
3588 ELSE
3589 IF l_waitlist_ind = 'Y' AND p_waitlist_flag = 'Y' THEN
3590 l_enroll := FALSE;
3591 IF p_failed_uoo_ids IS NOT NULL THEN
3592 p_failed_uoo_ids := p_failed_uoo_ids ||','||sub_usecs_rec.uoo_id;
3593 ELSE
3594 p_failed_uoo_ids := sub_usecs_rec.uoo_id;
3595 END IF;
3596 END IF;
3597 IF l_enroll THEN
3598 l_return_status := NULL;
3599 l_err_message := NULL;
3600
3601 OPEN cur_person_number;
3602 FETCH cur_person_number INTO l_person_number;
3603 CLOSE cur_person_number;
3604 SAVEPOINT enrwksht;
3605 --create unconfirm/waitlist sub unit attmepts
3606 BEGIN
3607 igs_ss_en_wrappers.insert_into_enr_worksheet(
3608 p_person_number =>l_person_number,
3609 p_course_cd => p_course_cd,
3610 p_uoo_id => sub_usecs_rec.uoo_id,
3611 p_waitlist_ind => l_waitlist_ind,
3612 p_session_id => NULL,
3613 p_return_status => l_return_status,
3614 p_message => l_err_message,
3615 p_cal_type => p_load_cal_type,
3616 p_ci_sequence_number => p_load_seq_num,
3617 p_audit_requested => 'N',
3618 p_enr_method => p_enrollment_method,
3619 p_override_cp => null,
3620 p_subtitle => null,
3621 p_gradsch_cd => null,
3622 p_gs_version_num => null,
3623 p_calling_obj =>'JOB'
3624 );
3625 EXCEPTION WHEN OTHERS THEN
3626 l_return_status := 'D';
3627 END;
3628
3629
3630 IF l_return_status <> 'D' THEN --implies success
3631
3632 IF l_waitlist_ind = 'Y' THEN --implies unit was waitlisted.
3633
3634 IF p_waitlist_uoo_ids IS NOT NULL THEN
3635 p_waitlist_uoo_ids := p_waitlist_uoo_ids ||','||sub_usecs_rec.uoo_id;
3636 ELSE
3637 p_waitlist_uoo_ids := sub_usecs_rec.uoo_id;
3638 END IF;
3639 ELSIF l_waitlist_ind = 'N' THEN --implies unit was preenrolled
3640
3641 IF p_uoo_ids IS NOT NULL THEN
3642 p_uoo_ids := p_uoo_ids||','||sub_usecs_rec.uoo_id;
3643 ELSE
3644 p_uoo_ids := sub_usecs_rec.uoo_id;
3645 END IF;
3646 END IF;
3647 ELSE --implies unit was not preenrolled or waitlisted.
3648 ROLLBACK to enrwksht;
3649 IF p_failed_uoo_ids IS NOT NULL THEN
3650 p_failed_uoo_ids := p_failed_uoo_ids ||','||sub_usecs_rec.uoo_id;
3651 ELSE
3652 p_failed_uoo_ids := sub_usecs_rec.uoo_id;
3653 END IF;
3654 END IF;
3655 END IF; -- l_enroll
3656 END IF; --l_waitlst_ind NULL
3657
3658 END IF; --IGS_EN_GEN_008.ENRP_GET_VAR_WINDOW
3659 END IF; --IF IGS_EN_GEN_004.ENRP_GET_REC_WINDOW
3660 END LOOP;
3661 EXCEPTION
3662 WHEN OTHERS THEN
3663 IF cur_sup%ISOPEN THEN
3664 CLOSE cur_sup;
3665 END IF;
3666 IF cur_sub%ISOPEN THEN
3667 CLOSE cur_sub;
3668 END IF;
3669 IF cur_sub1%ISOPEN THEN
3670 CLOSE cur_sub1;
3671 END IF;
3672 IF get_sub_usecs%ISOPEN THEN
3673 CLOSE get_sub_usecs;
3674 END IF;
3675 IF cur_person_number%ISOPEN THEN
3676 CLOSE cur_person_number;
3677 END IF;
3678 RAISE;
3679
3680
3681 END enr_sub_units;
3682
3683 PROCEDURE drop_sub_units(
3684 p_person_id IN NUMBER,
3685 p_course_cd IN VARCHAR2,
3686 p_uoo_id IN NUMBER,
3687 p_load_cal_type IN VARCHAR2,
3688 p_load_seq_num IN NUMBER,
3689 p_acad_cal_type IN VARCHAR2,
3690 p_acad_seq_num IN NUMBER,
3691 p_enrollment_method IN VARCHAR2,
3692 p_confirmed_ind IN VARCHAR2,
3693 p_person_type IN VARCHAR2,
3694 p_effective_date IN DATE,
3695 p_course_ver_num IN NUMBER,
3696 p_dcnt_reason_cd IN VARCHAR2,
3697 p_admin_unit_status IN VARCHAR2,
3698 p_uoo_ids OUT NOCOPY VARCHAR2,
3699 p_error_message OUT NOCOPY VARCHAR2) As
3700
3701 -------------------------------------------------------------------------------------------
3702 --Created by : Satya Vanukuri, Oracle IDC
3703 --Date created: 13-oct-2003
3704 -- Purpose : Created as part of placements build .
3705 -- procedure drops subordinate unit sections if the student is dropping superior unit section
3706 -- who when what
3707 -- ckasu 25-APR-2006 Modfied as a part of bug#5191592.
3708 -------------------------------------------------------------------------------------------
3709 CURSOR cur_sup is
3710 SELECT 1 FROM IGS_PS_UNIT_OFR_OPT
3711 WHERE uoo_id = p_uoo_id
3712 AND relation_type = 'SUPERIOR' ;
3713
3714 l_check_sup NUMBER(1);
3715
3716 CURSOR cur_sub_sua IS
3717 SELECT sua.uoo_id sub_uoo_id
3718 FROM IGS_EN_SU_ATTEMPT sua, IGS_PS_UNIT_OFR_OPT uoo
3719 WHERE sua.person_id = p_person_id AND
3720 sua.course_cd = p_course_cd AND
3721 sua.uoo_id = uoo.uoo_id AND
3722 uoo.sup_uoo_id = p_uoo_id AND
3723 uoo.relation_type = 'SUBORDINATE' ;
3724
3725 CURSOR cur_coo_id is
3726 SELECT sca.coo_id
3727 FROM igs_en_stdnt_ps_att sca
3728 WHERE sca.person_id = p_person_id
3729 AND sca.course_cd = p_course_cd;
3730
3731 l_coo_id igs_en_stdnt_ps_att.coo_id%TYPE;
3732
3733 l_sub_sua IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE;
3734 l_ovrrd_chk VARCHAR2(1);
3735 l_ovrrd_drop VARCHAR2(1);
3736 l_enrolment_cat IGS_PS_TYPE.enrolment_cat%TYPE;
3737 l_en_cal_type igs_ca_inst.cal_type%TYPE;
3738 l_en_ci_seq_num igs_ca_inst.sequence_number%TYPE;
3739 l_commencement_type VARCHAR2(10);
3740 l_dummy VARCHAR2(255);
3741 l_total_credit_points NUMBER;
3742 l_att_type VARCHAR2(100);
3743 l_attendance_types VARCHAR2(2000);
3744 l_message_name VARCHAR2(2000);
3745 l_eftsu_total NUMBER;
3746 l_ret_status BOOLEAN;
3747 l_err_message VARCHAR2(2000);
3748 BEGIN
3749 OPEN cur_sup;
3750 FETCH cur_sup INTO l_check_sup;
3751 CLOSE cur_sup;
3752
3753 IF l_check_sup IS NULL THEN
3754 RETURN;
3755 END IF;
3756
3757 IF p_confirmed_ind = 'Y' THEN
3758 l_ovrrd_chk := 'Y';
3759 l_ovrrd_drop := 'N';
3760
3761 ELSIF p_confirmed_ind = 'N' THEN
3762 l_ovrrd_chk := 'N';
3763 l_ovrrd_drop := 'N';
3764
3765 ELSE
3766 l_ovrrd_chk := 'N';
3767 l_ovrrd_drop := 'Y';
3768 END IF;
3769
3770 OPEN cur_sub_sua ;
3771 LOOP
3772 FETCH cur_sub_sua INTO l_sub_sua;
3773 EXIT WHEN cur_sub_sua%NOTFOUND;
3774
3775 -- Determine the Enrollment method , Enrollment Commencement type.
3776 l_dummy := NULL;
3777 l_enrolment_cat:=IGS_EN_GEN_003.Enrp_Get_Enr_Cat(p_person_id =>p_person_id,
3778 p_course_cd =>p_course_cd,
3779 p_cal_type =>p_acad_cal_type,
3780 p_ci_sequence_number =>p_acad_seq_num,
3781 p_session_enrolment_cat =>NULL,
3782 p_enrol_cal_type =>l_en_cal_type,
3783 p_enrol_ci_sequence_number =>l_en_ci_seq_num,
3784 p_commencement_type =>l_commencement_type,
3785 p_enr_categories =>l_dummy);
3786
3787 -- A call to igs_en_prc_load.enrp_clc_eftsu_total
3788 -- The Total enrolled CP of the student has to be determined before the unit is dropped(l_total_credit_points) .
3789 -- The unit is then dropped , and eval_min_cp is called with the value of l_total_enrolled_cp.
3790 -- The value of l_total_enrolled_cp is essential to determine if the Min Credit Points is already reached
3791 -- by the student before that Unit is dropped.
3792 l_eftsu_total := igs_en_prc_load.enrp_clc_eftsu_total(p_person_id => p_person_id,
3793 p_course_cd => p_course_cd,
3794 p_acad_cal_type => p_acad_cal_type,
3795 p_acad_sequence_number => p_acad_seq_num,
3796 p_load_cal_type => p_load_cal_type,
3797 p_load_sequence_number => p_load_seq_num,
3798 p_truncate_ind => 'N',
3799 p_include_research_ind => 'Y' ,
3800 p_key_course_cd => NULL ,
3801 p_key_version_number => NULL ,
3802 p_credit_points => l_total_credit_points );
3803
3804 -- Check if the Forced Attendance Type has already been reached for the Student before transferring .
3805 OPEN cur_coo_id;
3806 FETCH cur_coo_id INTO l_coo_Id;
3807 CLOSE cur_coo_id;
3808 l_message_name :=NULL;
3809
3810 IF igs_en_val_sca.enrp_val_coo_att(p_person_id => p_person_id,
3811 p_coo_id => l_coo_id,
3812 p_cal_type => p_acad_cal_type,
3813 p_ci_sequence_number => p_acad_seq_num,
3814 p_message_name => l_message_name,
3815 p_attendance_types => l_attendance_types,
3816 p_load_or_teach_cal_type => p_load_cal_type,
3817 p_load_or_teach_seq_number => p_load_seq_num) THEN
3818 -- Assign values to the parameter p_deny_warn_att based on if Attendance Type has not been already reached or not.
3819 l_att_type := 'AttTypReached' ;
3820
3821 ELSE
3822 l_att_type := 'AttTypNotReached' ;
3823
3824 END IF ;
3825
3826 igs_ss_en_wrappers.blk_drop_units(
3827 p_uoo_id => l_sub_sua,
3828 p_person_id => p_person_id,
3829 p_person_type => p_person_type,
3830 p_load_cal_type => p_load_cal_type,
3831 p_load_sequence_number => p_load_seq_num,
3832 p_acad_cal_type => p_acad_cal_type,
3833 p_acad_sequence_number => p_acad_seq_num,
3834 p_program_cd => p_course_cd,
3835 p_program_version => p_course_ver_num,
3836 p_dcnt_reason_cd => p_dcnt_reason_cd ,
3837 p_admin_unit_status => p_admin_unit_status,
3838 p_effective_date => p_effective_date,
3839 p_enrolment_cat => l_enrolment_Cat,
3840 p_comm_type => l_commencement_type,
3841 p_enr_meth_type => p_enrollment_method,
3842 p_total_credit_points => l_total_credit_points,
3843 p_force_att_type => l_att_type,
3844 p_val_ovrrd_chk => l_ovrrd_chk,
3845 p_ovrrd_drop => l_ovrrd_drop,
3846 p_return_status =>l_ret_status,
3847 p_message =>l_err_message,
3848 P_sub_unit =>'Y' );
3849
3850 IF NOT l_ret_status THEN
3851 p_error_message := l_err_message;
3852 RETURN;
3853
3854 ELSE
3855
3856 IF p_confirmed_ind IS NOT NULL THEN
3857 IF p_uoo_ids IS NOT NULL THEN
3858 p_uoo_ids := p_uoo_ids||','||l_sub_sua;
3859 ELSE
3860 p_uoo_ids := l_sub_sua;
3861 END IF;
3862 END IF;
3863 END IF;
3864
3865 END LOOP;
3866 EXCEPTION
3867 WHEN OTHERS THEN
3868
3869 IF cur_coo_id%ISOPEN THEN
3870 CLOSE cur_coo_id;
3871 END IF;
3872 IF cur_Sup%ISOPEN THEN
3873 CLOSE cur_sup;
3874 END IF;
3875 RAISE;
3876
3877 END drop_sub_units;
3878
3879 PROCEDURE validate_mus( p_person_id IN NUMBER,
3880 p_course_cd IN VARCHAR2,
3881 p_uoo_id IN NUMBER
3882 ) AS
3883
3884
3885 CURSOR c_same_section(cp_unit_cd VARCHAR2,cp_version_number NUMBER) IS
3886 SELECT same_teaching_period
3887 FROM igs_ps_unit_ver uv
3888 WHERE unit_cd = cp_unit_cd AND
3889 uv.version_number =cp_version_number;
3890
3891 CURSOR c_mus_allowed (cp_person_id NUMBER, cp_course_cd VARCHAR2, cp_unit_cd VARCHAR2,
3892 cp_cal_type VARCHAR2, cp_ci_sequence_number NUMBER, cp_uoo_id NUMBER) IS
3893 SELECT 'x'
3894 FROM igs_en_su_attempt
3895 WHERE person_id=cp_person_id AND
3896 course_cd=cp_course_cd AND
3897 unit_cd=cp_unit_cd AND
3898 cal_type=cp_cal_type AND
3899 ci_sequence_number=cp_ci_sequence_number AND
3900 unit_attempt_status NOT IN ('DROPPED','DISCONTIN') AND
3901 uoo_id<> cp_uoo_id;
3902
3903 CURSOR c_usec_exclude_mus_flag (cp_uoo_id NUMBER) IS
3904 SELECT unit_cd,version_number,cal_type,ci_sequence_number,not_multiple_section_flag
3905 FROM igs_ps_unit_ofr_opt
3906 WHERE uoo_id=cp_uoo_id;
3907
3908 CURSOR c_mus_participate (cp_not_multiple_section_flag igs_ps_unit_ofr_opt.not_multiple_section_flag%TYPE,
3909 cp_person_id NUMBER, cp_course_cd VARCHAR2, cp_unit_cd VARCHAR2,
3910 cp_cal_type VARCHAR2, cp_ci_sequence_number NUMBER, cp_uoo_id NUMBER) IS
3911 SELECT 'x'
3912 FROM igs_en_su_attempt sua,
3913 igs_ps_unit_ofr_opt opt
3914 WHERE sua.person_id=cp_person_id AND
3915 sua.course_cd=cp_course_cd AND
3916 sua.unit_cd=cp_unit_cd AND
3917 sua.cal_type=cp_cal_type AND
3918 sua.ci_sequence_number = cp_ci_sequence_number AND
3919 unit_attempt_status NOT IN ('DROPPED','DISCONTIN') AND
3920 sua.uoo_id<> cp_uoo_id AND
3921 sua.uoo_id=opt.uoo_id AND
3922 opt.not_multiple_section_flag=cp_not_multiple_section_flag;
3923
3924 l_unit_cd igs_en_su_attempt.unit_cd%TYPE;
3925 l_unit_ver igs_en_su_attempt.version_number%TYPE;
3926 l_cal_type igs_en_su_attempt.cal_type%TYPE;
3927 l_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE;
3928 l_same_teaching_period igs_ps_unit_ver.same_teaching_period%TYPE;
3929 l_usec_exclude_mus_flag igs_ps_unit_ofr_opt.not_multiple_section_flag%TYPE;
3930 l_notused VARCHAR2(1);
3931
3932
3933
3934 BEGIN
3935
3936 /*checking for multiple versions of same unit section, if exists raise an exception*/
3937 --processing for same_teaching_period at unit section level added as a part of Repeat and Reeenrollment build
3938 OPEN c_usec_exclude_mus_flag (p_uoo_id);
3939 FETCH c_usec_exclude_mus_flag INTO l_unit_cd,l_unit_ver,l_cal_type,l_ci_sequence_number,l_usec_exclude_mus_flag;
3940 CLOSE c_usec_exclude_mus_flag;
3941
3942 OPEN c_same_section (l_unit_cd, l_unit_ver);
3943 FETCH c_same_section INTO l_same_teaching_period;
3944 CLOSE c_same_section;
3945
3946 IF NVL(l_same_teaching_period,'N')='N' OR NVL(l_usec_exclude_mus_flag,'Y')='Y' THEN
3947 --unit does not allow MUS..if any other attempts exist..raise error
3948 OPEN c_mus_allowed (p_person_id,p_course_cd,l_unit_cd,l_cal_type,l_ci_sequence_number, p_uoo_id);
3949 FETCH c_mus_allowed INTO l_notused;
3950 IF c_mus_allowed%FOUND THEN
3951 CLOSE c_mus_allowed;
3952 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MUS_NOT_ALLOWED');
3953 IGS_GE_MSG_STACK.ADD;
3954 APP_EXCEPTION.RAISE_EXCEPTION;
3955 END IF;
3956 CLOSE c_mus_allowed;
3957 ELSE
3958 --unit allows MUS..IF unit section allows MUS..check if any existing attempts do not allow MUS
3959 --if exits, raise error
3960 OPEN c_mus_participate ('Y',p_person_id,p_course_cd,l_unit_cd,l_cal_type,l_ci_sequence_number, p_uoo_id);
3961 FETCH c_mus_participate INTO l_notused;
3962 IF c_mus_participate%FOUND THEN
3963 CLOSE c_mus_participate;
3964 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MUS_NOT_ALLOWED');
3965 IGS_GE_MSG_STACK.ADD;
3966 APP_EXCEPTION.RAISE_EXCEPTION;
3967 END IF;
3968 CLOSE c_mus_participate;
3969 END IF; -- IF NVL(l_same_teaching_period,'N')='N'
3970
3971 EXCEPTION
3972
3973 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
3974 RAISE;
3975
3976 WHEN OTHERS THEN
3977 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3978 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.validate_mus');
3979 IGS_GE_MSG_STACK.ADD;
3980 RAISE;
3981
3982 END validate_mus;
3983
3984 END IGS_EN_VAL_SUA;
[Home] [Help]