1 PACKAGE BODY igs_en_gen_008 AS
2 /* $Header: IGSEN08B.pls 120.6 2006/02/22 02:22:14 ckasu ship $ */
3 /*-------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 amuthu 23-Sep-02 Modified the cursor c_sca in batch pre enrollment procedure
7 as per the Core Vs Optional EN TD.
8
9 vchappid 04-Jul-01 this function was considering the date aliases defined at the institutional
10 level only. Now it is changed to consider date aliases/dates at person type,
11 unit section level, institutional level in the same hierarchy
12 nalkumar 04-May-2002 Modified the enrp_get_var_window procedure as per the Bug# 2356997.
13 prraj 07-May-2002 Truncated the date values to remove timestamp before date
14 comparisons as part of (Bug# 2355143)
15 nalkumar 14-May-2002 Modified the 'enrp_val_chg_cp' function as per the bug# 2364461.
16 Nishikant 07OCT2002 UK Enhancement build. Bug#2580731. Five new parameters p_start_day, p_start_month,
17 p_end_day, p_end_month, p_selection_date added in the procedure enrp_ins_btch_prenrl.
18 Nishikant 16DEC2002 ENCR030(UK Enh) - Bug#2708430. One more parameter p_completion_date added in the procedure
19 enrp_ins_btch_prenrl.
20 prraj 11-Dec-2003 Replaced reference to view IGS_EN_NSTD_USEC_DL_V
21 with base table IGS_EN_NSTD_USEC_DL Bug# 2750716
22 knaraset 05-Mar-2003 Modified the date comparison in function enrp_get_ua_del_alwd,
23 such that it returns N, when the first unit discontinuation date is
24 less than or equal to the given effective date. Bug 2833794
25 myoganat 23-May-2003 Created cursor cur_no_assesment_ind in ENRP_VAL_CHG_CP to
26 check for audit attempt - Bug #2855870
27 svenkata 6-Jun-03 Modified the routine enrp_get_var_window to check for Variation cutoff override at
28 Person Type level. Bug 2829272.
29 amuthu 04-JUN-2003 added new parameter p_progress_status to enrp_ins_btch_prenrl as part of bug 2829265
30 Also added the same parameter in the call to IGS_EN_GEN_10.enrp_ins_sret/snew_prenrl
31 kkillams 16-06-2003 Three new parameters are added to the enrp_ins_btch_prenrl procedure as part of bug 2829270
32 amuthu 03-JUL-2003 Added logic to filter Advance and repeating students before call the call to
33 -- enrp_ins_snew_prenrl and enrp_ins_sret_prenrl instead of checking it within
34 -- the above mentioned procedures.
35 rvivekan 29-JUL-2003 Modified several message_name variables from varchar2(30) to varchar2(2000) as
36 a part of bug#3045405
37 vkarthik 21-Jan-2004 Removed recursive search from the function enrp_get_within_ci for checking if
38 the passed calendars are related anywhere in the hierarchy and replaced it with
39 direct search as part of Bug 3083153
40
41 snambaka 14-Mar-2005 Truncated the Effective Dates passed to the Procedure enrp_get_var_window
42 to ensure the standard validation between the Dates with and without time stamps.
43 Bug :3930440
44 ckasu 20-Feb-2006 modified cursor c_sca of enrp_ins_btch_prenrl procedure as a part of bug#5049068
45
46 -------------------------------------------------------------------------------------------*/
47
48 FUNCTION enrp_get_pr_outcome(
49 p_person_id IN NUMBER,
50 p_course_cd IN VARCHAR2
51 ) RETURN VARCHAR2 ;
52
53 FUNCTION enrp_get_ua_del_alwd( p_cal_type IN VARCHAR2 ,
54 p_ci_sequence_number IN NUMBER ,
55 p_effective_dt IN DATE,
56 p_uoo_id IN NUMBER
57 ) RETURN VARCHAR2 AS
58 /******************************************************************
59 Created By :
60 Date Created By :
61 Purpose : This function is used to determine whether deletion of unit attempts
62 is allowed within the nominated teaching calendar instance as at the
63 nominated date
64 Known limitations,
65 enhancements,
66 remarks :
67 Change History
68 Who When What
69
70 ******************************************************************/
71
72
73 -- cursor for getting the date aliases that are defined
74 -- at the person type level for the logged on person
75 --modified cursor for bug 3696257
76 CURSOR cur_pe_usr_adisc (cp_person_type igs_pe_person_types.person_type_code%TYPE)
77 IS
78 SELECT nvl(daiv.absolute_val,
79 IGS_CA_GEN_001.calp_set_alias_value(daiv.absolute_val,
80 IGS_CA_GEN_002.cals_clc_dt_from_dai(daiv.ci_sequence_number,
81 daiv.CAL_TYPE,
82 daiv.DT_ALIAS, daiv.sequence_number) ) ) alias_val
83 FROM igs_pe_usr_adisc_all pua,
84 igs_ca_da_inst daiv
85 WHERE daiv.cal_type = p_cal_type
86 AND daiv.ci_sequence_number = p_ci_sequence_number
87 AND daiv.dt_alias = pua.disc_dt_alias
88 AND pua.person_type = cp_person_type
89 ORDER BY 1;
90
91 -- cursor for getting the dates that are defined
92 -- at the unit level for the logged on person and for the uoo_id passed
93 CURSOR cur_usec_disc_dl
94 IS
95 SELECT usec_disc_dl_date alias_val
96 FROM igs_en_usec_disc_dl
97 WHERE uoo_id = p_uoo_id
98 ORDER BY usec_disc_dl_date, administrative_unit_status;
99
100 -- cursor for getting the date aliases that are defined
101 -- at the institutional level for the logged on person
102 CURSOR cur_unit_disc_crt
103 IS
104 SELECT daiv.alias_val
105 FROM igs_ps_unit_disc_crt uddc,
106 igs_ca_da_inst_v daiv
107 WHERE uddc.delete_ind = 'N'
108 AND daiv.cal_type = p_cal_type
109 AND daiv.ci_sequence_number = p_ci_sequence_number
110 AND daiv.dt_alias = uddc.unit_discont_dt_alias
111 ORDER BY daiv.alias_val;
112
113 -- Rowtype variables
114 l_cur_pe_usr_adisc cur_pe_usr_adisc%ROWTYPE;
115 l_cur_usec_disc_dl cur_usec_disc_dl%ROWTYPE;
116 l_cur_unit_disc_crt cur_unit_disc_crt%ROWTYPE;
117
118 l_v_person_type igs_pe_person_types.person_type_code%TYPE;
119
120 -- constant return variables
121 cst_yes CONSTANT VARCHAR2(1) := 'Y';
122 cst_no CONSTANT VARCHAR2(1) := 'N';
123
124 BEGIN
125 -- Get the person type of the logged on user by calling the function
126 l_v_person_type := igs_en_gen_008.enrp_get_person_type(p_course_cd =>NULL);
127
128 -- If the logged on user is a self service type then a person type will be returned
129 -- If the user logged is a back-office user the validations defined at Unit level has to be validated
130 IF l_v_person_type IS NOT NULL THEN
131
132 -- Check for the Person Type there exists date aliases at the Person Level
133 -- If exists then do the validations else validations at Unit Level has to be validated
134 OPEN cur_pe_usr_adisc(l_v_person_type);
135 FETCH cur_pe_usr_adisc INTO l_cur_pe_usr_adisc;
136 IF cur_pe_usr_adisc%FOUND THEN
137
138 -- If the records are found then close the cursor and reopen the cursor
139 -- for the person_type in a loop to process all the fetched records
140 CLOSE cur_pe_usr_adisc;
141 OPEN cur_pe_usr_adisc(l_v_person_type);
142 LOOP
143 FETCH cur_pe_usr_adisc INTO l_cur_pe_usr_adisc;
144 EXIT WHEN cur_pe_usr_adisc%NOTFOUND;
145
146 -- If the alias_val is less than the date passed into the function
147 -- then then function has to return 'N'
148 IF (l_cur_pe_usr_adisc.alias_val <= TRUNC(p_effective_dt)) THEN
149 CLOSE cur_pe_usr_adisc;
150 RETURN cst_no;
151 END IF;
152 END LOOP;
153 CLOSE cur_pe_usr_adisc;
154
155 -- If the alias_val is not less than the date passed into the function
156 -- then then function has to return 'Y'
157 RETURN cst_yes;
158 ELSE
159
160 -- Closing the cursor after checking that no date aliases are defined at person level
161 CLOSE cur_pe_usr_adisc;
162 END IF;
163 END IF;
164
165 -- Check for the Uoo_id passed there exists dead line dates at the Unit Level
166 -- If exists then do the validations else validations at Institutional Level has to be validated
167 OPEN cur_usec_disc_dl;
168 FETCH cur_usec_disc_dl INTO l_cur_usec_disc_dl;
169 IF cur_usec_disc_dl%FOUND THEN
170
171 -- If the records are found then close the cursor and reopen the cursor
172 -- for the Uoo_id in a loop to process all the fetched records
173 CLOSE cur_usec_disc_dl;
174 OPEN cur_usec_disc_dl;
175 LOOP
176 FETCH cur_usec_disc_dl INTO l_cur_usec_disc_dl;
177 EXIT WHEN cur_usec_disc_dl%NOTFOUND;
178
179 -- If the alias_val is less than the date passed into the function
180 -- then then function has to return 'N'
181 IF (l_cur_usec_disc_dl.alias_val <= TRUNC(p_effective_dt)) THEN
182 CLOSE cur_usec_disc_dl;
183 RETURN cst_no;
184 END IF;
185 END LOOP;
186 CLOSE cur_usec_disc_dl;
187
188 -- If the alias_val is not less than the date passed into the function
189 -- then then function has to return 'Y'
190 RETURN cst_yes;
191 ELSE
192 CLOSE cur_usec_disc_dl;
193 END IF;
194
195 -- Check for the Logged on user there exists date aliases at the Institutional Level
196 -- If exists then do the validations else the function should return 'Y'
197 OPEN cur_unit_disc_crt;
198 FETCH cur_unit_disc_crt INTO l_cur_unit_disc_crt;
199 IF cur_unit_disc_crt%FOUND THEN
200
201 -- If the records are found then close the cursor and reopen the cursor
202 -- for the parameters in a loop to process all the fetched records
203 CLOSE cur_unit_disc_crt;
204 OPEN cur_unit_disc_crt;
205 LOOP
206 FETCH cur_unit_disc_crt INTO l_cur_unit_disc_crt;
207 EXIT WHEN cur_unit_disc_crt%NOTFOUND;
208 IF (l_cur_unit_disc_crt.alias_val <= TRUNC(p_effective_dt)) THEN
209 CLOSE cur_unit_disc_crt;
210 RETURN cst_no;
211 END IF;
212 END LOOP;
213 CLOSE cur_unit_disc_crt;
214 RETURN cst_yes;
215 ELSE
216 CLOSE cur_unit_disc_crt;
217 -- If no dead line dates are defined at any level then the function should return 'Y'
218 RETURN cst_yes;
219 END IF;
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
224 fnd_message.set_token('NAME','IGS_EN_GEN_008.enrp_get_ua_del_alwd');
225 IGS_GE_MSG_STACK.ADD;
226 APP_EXCEPTION.RAISE_EXCEPTION;
227 END enrp_get_ua_del_alwd;
228
229
230 FUNCTION enrp_get_ua_rty( p_person_id IN NUMBER ,
231 p_course_cd IN VARCHAR2 ,
232 p_unit_cd IN VARCHAR2 ,
233 p_cal_type IN VARCHAR2 ,
234 p_ci_sequence_number IN NUMBER,
235 p_uoo_id IN NUMBER
236 ) RETURN VARCHAR2 AS
237 -------------------------------------------------------------------------------------------
238 --Who When What
239 --kkillams 25-04-2003 New paramater p_uoo_id is added to the function.
240 -- w.r.t. bug number 2829262
241 -------------------------------------------------------------------------------------------
242 BEGIN
243 DECLARE
244 v_grade igs_as_su_stmptout.grade%TYPE;
245 v_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE;
246 v_version_number igs_as_su_stmptout.version_number%TYPE;
247 v_s_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
248
249 CURSOR c_suao (
250 cp_person_id igs_en_su_attempt.person_id%TYPE,
251 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
252 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)IS
253 SELECT suao.grade,
254 suao.grading_schema_cd,
255 suao.version_number,
256 suao.finalised_outcome_ind
257 FROM igs_as_su_stmptout suao
258 WHERE suao.person_id = cp_person_id AND
259 suao.course_cd = cp_course_cd AND
260 suao.uoo_id = cp_uoo_id
261 ORDER BY suao.outcome_dt;
262
263 CURSOR c_gsg (
264 cp_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE,
265 cp_version_number igs_as_su_stmptout.version_number%TYPE,
266 cp_grade igs_as_su_stmptout.grade%TYPE) IS
267 SELECT gsg.s_result_type
268 FROM igs_as_grd_sch_grade gsg
269 WHERE gsg.grading_schema_cd = cp_grading_schema_cd AND
270 gsg.version_number = cp_version_number AND
271 gsg.grade = cp_grade;
272 BEGIN
273 -- This function gets the result type of a student IGS_PS_UNIT attempt.
274 -- The routine will determine the latest finalized grade for the UA and
275 -- return it's result type. The valid return values are those in the
276 -- s_result_type table.
277 -- 1. Select the latest finalised grade from the IGS_AS_SU_STMPTOUT
278 -- table.
279 FOR v_suao_row IN c_suao( p_person_id,
280 p_course_cd,
281 p_uoo_id) LOOP
282 IF ((v_suao_row.finalised_outcome_ind = 'Y') OR
283 (v_suao_row.grade IS NULL))THEN
284 v_grade := v_suao_row.grade;
285 v_grading_schema_cd := v_suao_row.grading_schema_cd;
286 v_version_number := v_suao_row.version_number;
287 END IF;
288 END LOOP;
289 IF (v_grade IS NULL) THEN
290 RETURN NULL;
291 END IF;
292 -- 2. Get the result type from the grading schema
293 OPEN c_gsg(
294 v_grading_schema_cd,
295 v_version_number,
296 v_grade);
297 FETCH c_gsg INTO v_s_result_type;
298 CLOSE c_gsg;
299 RETURN v_s_result_type;
300 END;
301 EXCEPTION
302 WHEN OTHERS THEN
303 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
304 fnd_message.set_token('NAME','IGS_EN_GEN_008.enrp_get_ua_rty');
305 IGS_GE_MSG_STACK.ADD;
306 app_exception.raise_exception;
307 END enrp_get_ua_rty;
308
309
310 FUNCTION enrp_get_uddc_aus( p_discontinued_dt IN DATE,
311 p_cal_type IN VARCHAR2,
312 p_ci_sequence_number IN NUMBER,
313 p_admin_unit_status_str OUT NOCOPY VARCHAR2,
314 p_alias_val OUT NOCOPY DATE,
315 p_uoo_id IN NUMBER
316 ) RETURN VARCHAR2 AS
317 /******************************************************************
318 Created By :
319 Date Created By :
320 Purpose : This function is used to determine the recent administrative unit status
321 that applies to the the student Unit attempt discontinued date
322 Known limitations,
323 enhancements,
324 remarks :
325 Change History
326 Who When What
327 amuthu 12-Jun-02 Modified the code to return the only value that was being
328 passed out NOCOPY using the out NOCOPY parameter and passing null to the out NOCOPY parameter.
329 vchappid 04-Jul-01 this function was considering the date aliases defined at the institutional
330 level only. Now it is changed to consider date aliases/dates at person type,
331 unit section level, institutional level in the same hierarchy
332 kkillams 14-Apr-03 Removed the cur_per_unit_disc cursor and related logic w.r.t. bug the 2893263
333 rvivekan 17-nov-2003 Bug3264064. Changed the datatype of variable holding the concatenated
334 administrative unit status list to l_v_admin_unit_status_str varchar(2000)
335
336 ******************************************************************/
337
338 -- cursor for getting the dates that are defined
339 -- at the unit level for the logged on person and for the uoo_id passed
340 CURSOR cur_unit_usec_disc
341 IS
342 SELECT usec_disc_dl_date alias_val,administrative_unit_status
343 FROM igs_en_usec_disc_dl
344 WHERE uoo_id = p_uoo_id
345 ORDER BY usec_disc_dl_date;
346
347 -- cursor for getting the date aliases that are defined
348 -- at the institutional level for the logged on person
349 CURSOR cur_inst_date_aliases
350 IS
351 SELECT igs_ca_gen_001.calp_set_alias_value(dai.absolute_val,
352 igs_ca_gen_002.cals_clc_dt_from_dai(dai.ci_sequence_number,
353 dai.cal_type,
354 dai.dt_alias,
355 dai.sequence_number
356 )
357 ) alias_val,
358 uddc.administrative_unit_status,
359 uddc.dflt_ind
360 FROM igs_ca_da_inst dai,
361 igs_ps_unit_disc_crt uddc
362 WHERE dai.cal_type = p_cal_type AND
363 dai.ci_sequence_number = p_ci_sequence_number AND
364 dai.dt_alias = uddc.unit_discont_dt_alias
365 ORDER BY 1;
366
367 -- Rowtype Variables
368 l_cur_unit_usec_disc cur_unit_usec_disc%ROWTYPE;
369 l_cur_inst_date_aliases cur_inst_date_aliases%ROWTYPE;
370 l_v_alias_val igs_ca_da_inst_v.alias_val%TYPE;
371 l_v_administrative_unit_status igs_ps_unit_disc_crt.administrative_unit_status%TYPE;
372 l_v_return_value igs_ps_unit_disc_crt.administrative_unit_status%TYPE;
373 l_v_admin_unit_status_str VARCHAR2(2000);
374 l_b_dflt_admin_unit_found BOOLEAN;
375 l_n_aus_count NUMBER(2);
376 BEGIN
377 -- Check for the Uoo_id there exists date aliases defined at the Unit Level
378 -- If exists then do the validations else validations at Institutional Level has to be validated
379 OPEN cur_unit_usec_disc;
380 FETCH cur_unit_usec_disc INTO l_cur_unit_usec_disc;
381 IF cur_unit_usec_disc%FOUND THEN
382 -- If the records are found then close the cursor and reopen the cursor
383 -- for the Uoo_id in a loop to process all the fetched records
384 CLOSE cur_unit_usec_disc;
385 OPEN cur_unit_usec_disc;
386 LOOP
387 FETCH cur_unit_usec_disc INTO l_cur_unit_usec_disc;
388 EXIT WHEN cur_unit_usec_disc%NOTFOUND;
389 -- checking if this is the first time the loop is entered
390 IF (l_v_alias_val IS NULL) THEN
391 -- if it is the first time then all the alias_val is NULL
392 -- if the discontinuation date is less than the alias value then NULL is returned
393 IF (TRUNC(p_discontinued_dt) < l_cur_unit_usec_disc.alias_val) THEN
394 p_alias_val := NULL;
395 p_admin_unit_status_str := NULL;
396 l_v_return_value := NULL;
397 RETURN l_v_return_value;
398 ELSE
399 l_v_alias_val := l_cur_unit_usec_disc.alias_val;
400 l_v_administrative_unit_status := l_cur_unit_usec_disc.administrative_unit_status;
401 -- Checking if the current administrative status is null
402 -- if it is NULL then ';' is not appended
403 IF l_cur_unit_usec_disc.administrative_unit_status IS NOT NULL THEN
404 l_v_admin_unit_status_str := RPAD(l_cur_unit_usec_disc.administrative_unit_status,10,' ')||',';
405 ELSE
406 l_v_admin_unit_status_str := RPAD(l_cur_unit_usec_disc.administrative_unit_status,10,' ');
407 END IF;
408 END IF;
409 ELSE -- l_v_alias_val is not NULL
410 IF (TRUNC(p_discontinued_dt) >= l_v_alias_val and TRUNC(p_discontinued_dt) < l_cur_unit_usec_disc.alias_val) THEN
411 p_alias_val := l_v_alias_val;
412 IF l_v_admin_unit_status_str IS NOT NULL
413 AND l_v_administrative_unit_status IS NULL
414 AND length(TRIM(l_v_admin_unit_status_str)) = 11 THEN
415 p_admin_unit_status_str := l_v_admin_unit_status_str;
416 l_v_return_value := TRIM(SUBSTR(TRIM(l_v_admin_unit_status_str),1,10));
417 ELSE
418 p_admin_unit_status_str := l_v_admin_unit_status_str;
419 l_v_return_value := l_v_administrative_unit_status;
420 END IF;
421 RETURN l_v_return_value;
422 ELSE
423 IF l_cur_unit_usec_disc.alias_val = l_v_alias_val THEN
424 -- If the date aliases are having the same absolute value then the administrative status
425 -- is concatenated and the value is returned else the recent date alias is returned
426 IF l_cur_unit_usec_disc.administrative_unit_status IS NOT NULL THEN
427 l_v_admin_unit_status_str := l_v_admin_unit_status_str||RPAD(l_cur_unit_usec_disc.administrative_unit_status,10,' ')||',';
428 ELSE
429 l_v_admin_unit_status_str := l_v_admin_unit_status_str||RPAD(l_cur_unit_usec_disc.administrative_unit_status,10,' ');
430 END IF;
431 ELSE
432 IF l_cur_unit_usec_disc.administrative_unit_status IS NOT NULL THEN
433 l_v_admin_unit_status_str := RPAD(l_cur_unit_usec_disc.administrative_unit_status,10,' ')||',';
434 ELSE
435 l_v_admin_unit_status_str := RPAD(l_cur_unit_usec_disc.administrative_unit_status,10,' ');
436 END IF;
437 l_b_dflt_admin_unit_found := FALSE;
438 END IF;
439 -- If the more recent date alias is found then the OUT NOCOPY parameter value is set to the recent one
440 IF l_b_dflt_admin_unit_found = FALSE THEN
441 l_b_dflt_admin_unit_found := TRUE;
442 l_v_administrative_unit_status := l_cur_unit_usec_disc.administrative_unit_status;
443 ELSE
444 l_v_administrative_unit_status := NULL;
445 END IF;
446 l_v_alias_val := l_cur_unit_usec_disc.alias_val;
447 END IF;
448 END IF;
449 END LOOP;
450 CLOSE cur_unit_usec_disc;
451 -- when none of the date aliases are not violating the validations
452 -- then the recent values are returned from the function
453 p_alias_val := l_v_alias_val;
454 IF l_v_admin_unit_status_str IS NOT NULL
455 AND l_v_administrative_unit_status IS NULL
456 AND length(TRIM(l_v_admin_unit_status_str)) = 11 THEN
457 p_admin_unit_status_str := l_v_admin_unit_status_str;
458 l_v_return_value := TRIM(SUBSTR(TRIM(l_v_admin_unit_status_str),1,10));
459 ELSE
460 p_admin_unit_status_str := l_v_admin_unit_status_str;
461 l_v_return_value := l_v_administrative_unit_status;
462 END IF;
463 RETURN l_v_return_value;
464 ELSE
465 CLOSE cur_unit_usec_disc;
466 END IF;
467 -- selecting the IGS_PS_UNIT discontinuation date aliases
468 -- in the student IGS_PS_UNIT attempt teaching period
469 OPEN cur_inst_date_aliases;
470 LOOP
471 FETCH cur_inst_date_aliases INTO l_cur_inst_date_aliases;
472 EXIT WHEN cur_inst_date_aliases%NOTFOUND;
473 -- searching for the administrative IGS_PS_UNIT status
474 -- applicable to the discontinued date
475 IF (l_v_alias_val IS NULL) THEN
476 IF (TRUNC(p_discontinued_dt) < l_cur_inst_date_aliases.alias_val) THEN
477 p_alias_val := NULL;
478 p_admin_unit_status_str := NULL;
479 l_v_return_value := NULL;
480 RETURN l_v_return_value;
481 ELSE
482 l_v_alias_val := l_cur_inst_date_aliases.alias_val;
483 IF l_cur_inst_date_aliases.dflt_ind = 'Y' THEN
484 l_v_administrative_unit_status := l_cur_inst_date_aliases.administrative_unit_status;
485 ELSE
486 l_v_administrative_unit_status := NULL;
487 END IF;
488 IF l_cur_inst_date_aliases.administrative_unit_status IS NOT NULL THEN
489 l_v_admin_unit_status_str := RPAD(l_cur_inst_date_aliases.administrative_unit_status,10,' ')||',';
490 ELSE
491 l_v_admin_unit_status_str := RPAD(l_cur_inst_date_aliases.administrative_unit_status,10,' ');
492 END IF;
493 END IF;
494 ELSE -- l_v_alias_val is not NULL
495 IF (TRUNC(p_discontinued_dt) >= l_v_alias_val and TRUNC(p_discontinued_dt) < l_cur_inst_date_aliases.alias_val) THEN
496 p_alias_val := l_v_alias_val;
497 IF l_v_admin_unit_status_str IS NOT NULL
498 AND l_v_administrative_unit_status IS NULL
499 AND length(TRIM(l_v_admin_unit_status_str)) = 11 THEN
500 p_admin_unit_status_str := l_v_admin_unit_status_str;
501 l_v_return_value := TRIM(SUBSTR(TRIM(l_v_admin_unit_status_str),1,10));
502 ELSE
503 p_admin_unit_status_str := l_v_admin_unit_status_str;
504 l_v_return_value := l_v_administrative_unit_status;
505 END IF;
506 RETURN l_v_return_value;
507 ELSE
508 IF l_cur_inst_date_aliases.alias_val = l_v_alias_val THEN
509 IF l_cur_inst_date_aliases.administrative_unit_status IS NOT NULL THEN
510 l_v_admin_unit_status_str := l_v_admin_unit_status_str||RPAD(l_cur_inst_date_aliases.administrative_unit_status,10,' ')||',';
511 ELSE
512 l_v_admin_unit_status_str := l_v_admin_unit_status_str||RPAD(l_cur_inst_date_aliases.administrative_unit_status,10,' ');
513 END IF;
514 ELSE
515 IF l_cur_inst_date_aliases.administrative_unit_status IS NOT NULL THEN
516 l_v_admin_unit_status_str := RPAD(l_cur_inst_date_aliases.administrative_unit_status,10,' ')||',';
517 ELSE
518 l_v_admin_unit_status_str := RPAD(l_cur_inst_date_aliases.administrative_unit_status,10,' ');
519 END IF;
520 l_b_dflt_admin_unit_found := FALSE;
521 END IF;
522 IF l_cur_inst_date_aliases.dflt_ind = 'Y' THEN
523 IF l_b_dflt_admin_unit_found = FALSE THEN
524 l_b_dflt_admin_unit_found := TRUE;
525 l_v_administrative_unit_status := l_cur_inst_date_aliases.administrative_unit_status;
526 ELSE
527 l_v_administrative_unit_status := NULL;
528 END IF;
529 ELSE
530 IF l_b_dflt_admin_unit_found = FALSE THEN
531 l_v_administrative_unit_status := NULL;
532 END IF;
533 END IF;
534 l_v_alias_val := l_cur_inst_date_aliases.alias_val;
535 END IF;
536 END IF;
537 END LOOP;
538
539 -- return the unit_administrative_status
540 p_alias_val := l_v_alias_val;
541 IF l_v_admin_unit_status_str IS NOT NULL
542 AND l_v_administrative_unit_status IS NULL
543 AND length(TRIM(l_v_admin_unit_status_str)) = 11 THEN
544 p_admin_unit_status_str := l_v_admin_unit_status_str;
545 l_v_return_value := TRIM(SUBSTR(TRIM(l_v_admin_unit_status_str),1,10));
546 ELSE
547 p_admin_unit_status_str := l_v_admin_unit_status_str;
548 l_v_return_value := l_v_administrative_unit_status;
549 END IF;
550 RETURN l_v_return_value;
551
552 END enrp_get_uddc_aus;
553
554
555 FUNCTION enrp_get_ug_pg_crs( p_course_cd IN VARCHAR2 ,
556 p_version_number NUMBER
557 ) RETURN VARCHAR2 AS
558 BEGIN
559 DECLARE
560 v_govt_course_type IGS_PS_TYPE.govt_course_type%TYPE ;
561 CURSOR c_crv_ct IS
562 SELECT ct.govt_course_type
563 FROM igs_ps_ver crv,
564 igs_ps_type ct
565 WHERE crv.course_cd = p_course_cd AND
566 crv.version_number = p_version_number AND
567 crv.course_type = ct.course_type;
568 BEGIN
569
570 v_govt_course_type := NULL;
571
572 -- Validate the input parameter.
573 IF p_course_cd IS NULL OR
574 p_version_number IS NULL THEN
575 RETURN NULL;
576 END IF;
577 -- Retrieve the government IGS_PS_COURSE type for the IGS_PS_COURSE code.
578 OPEN c_crv_ct;
579 FETCH c_crv_ct INTO v_govt_course_type;
580 CLOSE c_crv_ct;
581 -- Determine if the IGS_PS_COURSE is an undergraduate or a postgraduate IGS_PS_COURSE.
582 IF v_govt_course_type IN (8, 9, 10, 13, 20, 21, 22, 30, 40, 41) THEN
583 RETURN 'UG';
584 ELSIF v_govt_course_type IN (1, 2, 3, 4, 5, 6, 7, 11, 12, 42) THEN
585 RETURN 'PG';
586 END IF;
587 RETURN NULL;
588 EXCEPTION
589 WHEN OTHERS THEN
590 IF c_crv_ct%ISOPEN THEN
591 CLOSE c_crv_ct;
592 END IF;
593 RAISE;
594 END;
595 EXCEPTION
596 WHEN OTHERS THEN
597 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
598 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_008.enrp_get_ug_pg_crs');
599 IGS_GE_MSG_STACK.ADD;
600 App_Exception.Raise_Exception;
601 END enrp_get_ug_pg_crs;
602
603 FUNCTION enrp_get_us_title(
604 p_unit_set_cd IN VARCHAR2 ,
605 p_us_version_number IN NUMBER ,
606 p_course_cd IN VARCHAR2 ,
607 p_version_number IN NUMBER ,
608 p_cal_type IN VARCHAR2 ,
609 p_sequence_number IN NUMBER ,
610 p_person_id IN NUMBER )
611 RETURN VARCHAR2 AS
612 BEGIN -- enrp_get_us_title
613 -- This routine will get the IGS_EN_UNIT_SET title from either the
614 -- IGS_AS_SU_SETATMPT, IGS_PS_OFR_UNIT_SET or IGS_EN_UNIT_SET table,
615 -- taking the lowest level which is set.
616 DECLARE
617 v_override_title igs_as_su_setatmpt.override_title%TYPE;
618 v_cous_override_title igs_ps_ofr_unit_set.override_title%TYPE;
619 v_us_title igs_en_unit_set.title%TYPE;
620 CURSOR c_susa IS
621 SELECT susa.override_title
622 FROM igs_as_su_setatmpt susa
623 WHERE susa.person_id = p_person_id AND
624 susa.course_cd = p_course_cd AND
625 susa.unit_set_cd = p_unit_set_cd AND
626 susa.sequence_number = p_sequence_number;
627 CURSOR c_us_cous IS
628 SELECT cous.override_title,
629 us.title
630 FROM igs_en_unit_set us,
631 igs_ps_ofr_unit_set cous
632 WHERE us.unit_set_cd = p_unit_set_cd AND
633 us.version_number = p_us_version_number AND
634 cous.unit_set_cd(+) = us.unit_set_cd AND
635 cous.us_version_number(+) = us.version_number AND
636 cous.course_cd(+) = p_course_cd AND
637 cous.crv_version_number(+) = p_version_number AND
638 cous.cal_type(+) = p_cal_type;
639 BEGIN
640 -- If the IGS_PE_PERSON details are set then query for a IGS_PE_PERSON based title
641 IF (p_person_id IS NOT NULL AND
642 p_sequence_number IS NOT NULL) THEN
643 OPEN c_susa;
644 FETCH c_susa INTO v_override_title;
645 IF (c_susa%FOUND AND
646 v_override_title IS NOT NULL) THEN
647 CLOSE c_susa;
648 RETURN v_override_title;
649 END IF;
650 CLOSE c_susa;
651 END IF;
652 -- Query the titles from IGS_PS_UNIT set and IGS_PS_COURSE offering IGS_PS_UNIT set
653 OPEN c_us_cous;
654 FETCH c_us_cous INTO v_cous_override_title,
655 v_us_title;
656 IF (c_us_cous%NOTFOUND) THEN
657 CLOSE c_us_cous;
658 RETURN NULL;
659 ELSE
660 CLOSE c_us_cous;
661 IF (v_cous_override_title IS NOT NULL) THEN
662 RETURN v_cous_override_title;
663 ELSE
664 RETURN v_us_title;
665 END IF;
666 END IF;
667 EXCEPTION
668 WHEN OTHERS THEN
669 IF (c_susa%ISOPEN) THEN
670 CLOSE c_susa;
671 END IF;
672 IF (c_us_cous%ISOPEN) THEN
673 CLOSE c_us_cous;
674 END IF;
675 END;
676
677 END enrp_get_us_title;
678
679
680 FUNCTION enrp_get_var_window( p_cal_type IN VARCHAR2,
681 p_ci_sequence_number IN NUMBER,
682 p_effective_dt IN DATE,
683 p_uoo_id IN NUMBER) RETURN BOOLEAN AS
684 /******************************************************************
685 Created By :
686 Date Created By :
687 Purpose : This function is used to determine if the effective date of an insert, update or
688 delete of a student unit attempts is within the enrollments variation window
689 Known limitations,
690 enhancements,
691 remarks :
692 Change History
693 Who When What
694 vchappid 04-Jul-01 this function was considering the date aliases defined at the institutional
695 level only. Now it is changed to consider date aliases/dates at unit section level,
696 institutional level in the same hierarchy
697 kkillams 27-Feb-03 Modified cur_dai_v Cursor, replaced * with alias_val w.r.t. bug 2749648
698 svenkata 6-Jun-03 Added new validation to check if the Variation cutt off has been overriden at the Person Type level.Bug 2829272
699 snambaka 14-Mar-05 Truncated the p_effective_dt and l_cur_en_nstd_usec_dl.enr_dl_date
700 to ensure date validation without timestamp to avaoid inconsistency.
701 ******************************************************************/
702 -- Determine if the effective date of an insert, update or delete
703 -- of a student IGS_PS_UNIT attempt is within the enrollments variation window.
704 -- This is determined by an enrollments variation cutoff date within the
705 -- teaching period calendar instance of the student IGS_PS_UNIT attempt.
706
707 -- cursor for getting the dates that are defined
708 -- at the unit level for the logged on person and for the uoo_id passed
709 CURSOR cur_en_nstd_usec_dl
710 IS
711 SELECT enr_dl_date
712 FROM igs_en_nstd_usec_dl
713 WHERE uoo_id = p_uoo_id
714 AND function_name='VARIATION_CUTOFF'
715 ORDER BY enr_dl_date;
716
717 -- cursor for getting the variation cut off date aliases that are defined
718 -- at the institutional level
719 CURSOR cur_s_enr_cal_conf
720 IS
721 SELECT variation_cutoff_dt_alias
722 FROM IGS_EN_CAL_CONF
723 WHERE s_control_num = 1;
724
725 -- cursor for getting the date aliases that are defined
726 -- at the institutional level for the logged on person and for the cal type, sequence number passed
727 CURSOR cur_dai_v( cp_dt_alias igs_en_cal_conf.variation_cutoff_dt_alias%TYPE)
728 IS
729 SELECT alias_val
730 FROM igs_ca_da_inst_v
731 WHERE cal_type = p_cal_type
732 AND ci_sequence_number = p_ci_sequence_number
733 AND dt_alias = cp_dt_alias
734 ORDER BY alias_val DESC;
735
736 -- Cursor to check if an override has been setup for the 'Variation Cutoff' Step at the Person Type level.
737 CURSOR c_get_ovr (p_person_type igs_pe_person.party_type%TYPE ) IS
738 SELECT 'x'
739 FROM IGS_PE_USR_AVAL
740 WHERE person_type = p_person_type AND
741 validation = 'OVR_VAR_CUT_OFF' AND
742 override_ind = 'Y' ;
743
744 -- ROWTYPE Variables
745 l_cur_en_nstd_usec_dl cur_en_nstd_usec_dl%ROWTYPE;
746 l_cur_s_enr_cal_conf cur_s_enr_cal_conf%ROWTYPE;
747 l_cur_dai_v cur_dai_v%ROWTYPE;
748 l_person_type igs_pe_person.party_type%TYPE := NULL ;
749 l_dummy VARCHAR2(10);
750 l_effective_dt l_cur_dai_v.alias_val%TYPE ;
751 BEGIN
752 --
753 -- Check if an override exists for Variation Cut off Window at the Person Type level. First , get the Person Type in context.
754 l_person_type := enrp_get_person_type ( P_COURSE_CD => NULL );
755 l_effective_dt := trunc(p_effective_dt);
756
757 IF l_person_type IS NOT NULL THEN
758 OPEN c_get_ovr( l_person_type );
759 FETCH c_get_ovr INTO l_dummy ;
760 --
761 -- If an override exists at the Person Type level , return TRUE.
762 IF c_get_ovr%FOUND THEN
763 CLOSE c_get_ovr;
764 RETURN TRUE;
765 END IF ;
766 CLOSE c_get_ovr;
767 END IF ;
768
769 -- Added next IF condition as per the Bug# 2356997.
770 IF fnd_function.test('IGSENVAR') = TRUE THEN
771 RETURN TRUE;
772 ELSE
773 -- Check if the deadline dates are defined at the Unit level
774 OPEN cur_en_nstd_usec_dl;
775 FETCH cur_en_nstd_usec_dl INTO l_cur_en_nstd_usec_dl;
776 -- If the dates are defined then the cursor is closed and reopened to loop through all the records
777 IF cur_en_nstd_usec_dl%FOUND THEN
778 CLOSE cur_en_nstd_usec_dl;
779 OPEN cur_en_nstd_usec_dl;
780 LOOP
781 FETCH cur_en_nstd_usec_dl INTO l_cur_en_nstd_usec_dl;
782 EXIT WHEN cur_en_nstd_usec_dl%NOTFOUND;
783 -- If the effective deadline date passed into the function is greater than
784 -- the deadline alias value at the Unit Level then the function returns FALSE
785 IF (l_effective_dt > trunc(l_cur_en_nstd_usec_dl.enr_dl_date)) THEN
786 RETURN FALSE;
787 END IF;
788 END LOOP;
789 CLOSE cur_en_nstd_usec_dl;
790 -- If the effective deadline date passed into the function is not greater than
791 -- the deadline alias value at the Unit Level then the function returns TRUE
792 RETURN TRUE;
793 ELSE
794 -- If the date aliases are not defined at the Unit Level then
795 -- close the cursor and then do next level validations
796 CLOSE cur_en_nstd_usec_dl;
797 END IF;
798 -- This module gets whether it is possible as at the effective date to
799 --vary IGS_PS_UNIT attempts in the nominated teaching period cal instance
800 OPEN cur_s_enr_cal_conf;
801 FETCH cur_s_enr_cal_conf INTO l_cur_s_enr_cal_conf;
802 IF (cur_s_enr_cal_conf%NOTFOUND) THEN
803 CLOSE cur_s_enr_cal_conf;
804 RETURN TRUE;
805 END IF;
806 CLOSE cur_s_enr_cal_conf;
807 IF(l_cur_s_enr_cal_conf.variation_cutoff_dt_alias IS NULL) THEN
808 RETURN TRUE;
809 END IF;
810 OPEN cur_dai_v(l_cur_s_enr_cal_conf.variation_cutoff_dt_alias);
811 FETCH cur_dai_v INTO l_cur_dai_v;
812 IF cur_dai_v%NOTFOUND THEN
813 CLOSE cur_dai_v;
814 RETURN TRUE;
815 END IF;
816 CLOSE cur_dai_v;
817 IF (l_effective_dt > trunc(l_cur_dai_v.alias_val)) THEN
818 RETURN FALSE;
819 END IF;
820 RETURN TRUE;
821 END IF;
822 EXCEPTION
823 WHEN OTHERS THEN
824 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
825 fnd_message.set_token('NAME','IGS_EN_GEN_008.enrp_get_var_window');
826 IGS_GE_MSG_STACK.ADD;
827 APP_EXCEPTION.RAISE_EXCEPTION;
828 END enrp_get_var_window;
829
830 FUNCTION enrp_get_within_ci(
831 p_sup_cal_type IN VARCHAR2 ,
832 p_sup_sequence_number IN NUMBER ,
833 p_sub_cal_type IN VARCHAR2 ,
834 p_sub_sequence_number IN NUMBER ,
835 p_direct_match_ind IN boolean )
836 RETURN BOOLEAN AS
837 /* --Change History:
838 Who When What
839 vkarthik 21-Jan-2004 Removed recursive search from the function for checking if
840 the passed calendars are related anywhere in the hierarchy */
841 CURSOR c_cir (
842 cp_sup_cal_ty igs_ca_inst_rel.sup_cal_type%TYPE,
843 cp_sup_ci_seq_num igs_ca_inst_rel.sup_ci_sequence_number%TYPE,
844 cp_sub_cal_ty igs_ca_inst_rel.sub_cal_type%TYPE,
845 cp_sub_ci_seq_num igs_ca_inst_rel.sub_ci_sequence_number%TYPE) IS
846 SELECT sup_cal_type,
847 sup_ci_sequence_number
848 FROM igs_ca_inst_rel
849 WHERE
850 sup_cal_type = cp_sup_cal_ty AND
851 sup_ci_sequence_number = cp_sup_ci_seq_num AND
852 sub_cal_type = cp_sub_cal_ty AND
853 sub_ci_sequence_number = cp_sub_ci_seq_num;
854 l_ret_status BOOLEAN := FALSE;
855 vc_cir c_cir%ROWTYPE;
856 BEGIN
857 -- This module determines whether the nominated subordinate
858 -- calendar is within the nominated superior calendar.
859 OPEN c_cir(p_sup_cal_type, p_sup_sequence_number,
860 p_sub_cal_type, p_sub_sequence_number);
861 FETCH c_cir INTO vc_cir;
862 IF c_cir%FOUND THEN
863 l_ret_status := TRUE;
864 END IF;
865 CLOSE c_cir;
866 RETURN l_ret_status;
867 END enrp_get_within_ci;
868
869 FUNCTION get_commence_date_range(
870 p_start_day IN NUMBER,
871 p_start_month IN NUMBER,
872 p_end_day IN NUMBER,
873 p_end_month IN NUMBER,
874 p_commencement_dt IN DATE)
875 RETURN VARCHAR2 AS
876 /* HISTORY
877 WHO WHEN WHAT
878 Nishikant 07OCT2002 This local function Created in the UK Enhancement Build. Enh Bug#2580731.
879 It returns TRUE if the Commencement date falls between the Start_day/Start_month AND
880 End_day/End_month. Else it returns FALSE. This is being used in the cursor c_sca of the
881 Procedure enrp_ins_btch_prenrl. */
882
883 l_year NUMBER;
884 l_start_day NUMBER;
885 l_start_month NUMBER;
886 l_end_day NUMBER;
887 l_end_month NUMBER;
888
889 PROCEDURE leap_chk_start_date(
890 p_l_start_day IN OUT NOCOPY NUMBER,
891 p_l_start_month IN OUT NOCOPY NUMBER,
892 p_l_year IN NUMBER
893 ) AS
894 BEGIN
895 --If the Start day and Month is 29thFeb then check the year is a LEAP year or not.
896 --If its a LEAP year then fine. If not then make the Start day and Month to 28th of February.
897 IF SUBSTR(LAST_DAY(TO_DATE('28'||'-'||'02'||'-'||TO_CHAR(p_l_year),'DD-MM-YYYY')),1,2) = '28' THEN
898 p_l_start_day := 28;
899 END IF;
900 END leap_chk_start_date;
901
902 PROCEDURE leap_chk_end_date(
903 p_l_end_day IN OUT NOCOPY NUMBER,
904 p_l_end_month IN OUT NOCOPY NUMBER,
905 p_l_year IN NUMBER
906 ) AS
907 BEGIN
908 --If the End day and Month is 29thFeb then check the year is a LEAP year or not.
909 --If its a LEAP year then fine. If not then make the End day and Month to 1st of March.
910 IF SUBSTR(LAST_DAY(TO_DATE('28'||'-'||'02'||'-'||TO_CHAR(p_l_year),'DD-MM-YYYY')),1,2) = '28' THEN
911 p_l_end_day := 1;
912 p_l_end_month := 3;
913 END IF;
914 END leap_chk_end_date;
915
916 BEGIN
917 --If any of the parameter p_start_day, p_end_day, p_start_month and p_end_month IS NULL then return TRUE
918 IF p_start_day IS NULL OR p_start_month IS NULL OR
919 p_end_day IS NULL OR p_end_month IS NULL THEN
920 RETURN 'TRUE';
921 END IF;
922
923 -- The year part of the Start and End date is based on the year part of the p_commencement_date parameter
924 l_year := TO_NUMBER(TO_CHAR(p_commencement_dt, 'YYYY'));
925 l_start_day := p_start_day;
926 l_start_month := p_start_month;
927 l_end_day := p_end_day;
928 l_end_month := p_end_month;
929
930 -- If Min Program Attempt Start Month < Max Program Attempt Start Month
931 -- OR ( Min Program Attempt Start Month = Max Program Attempt Start Month AND
932 -- Min Program Attempt Start Day < Max Program Attempt Start Day )
933 -- (E.g.: Start Date = 10-Feb and End Date = 20-Feb , the range spans across 10 Days of the same Year only )
934 -- And if the commencement date falls between the range then return TRUE
935 IF ( p_start_month < p_end_month ) OR
936 ( p_start_month = p_end_month AND p_start_day <= p_end_day ) THEN
937
938 IF l_start_day = 29 AND l_start_month = 02 THEN
939 leap_chk_start_date(l_start_day, l_start_month, l_year);
940 END IF;
941 IF l_end_day = 29 AND l_end_month = 02 THEN
942 leap_chk_end_date(l_end_day, l_end_month, l_year);
943 END IF;
944
945 IF p_commencement_dt BETWEEN TO_DATE(l_start_day||'-'||p_start_month||'-'||TO_CHAR(l_year),'DD-MM-YYYY')
946 AND TO_DATE(l_end_day||'-'||l_end_month||'-'||TO_CHAR(l_year),'DD-MM-YYYY') THEN
947 RETURN 'TRUE';
948 END IF;
949
950 -- If Min Program Attempt Start Month > Max Program Attempt Start Month
951 -- OR ( Min Program Attempt Start Month = Max Program Attempt Start Month AND
952 -- Min Program Attempt Start Day > Max Program Attempt Start Day )
953 -- (E.g.: Start Date = 20-Feb and End Date = 10-Feb , the range spans across YEARS )
954 -- Here arises two scenarios.
955 ELSIF ( p_start_month > p_end_month ) OR
956 ( p_start_month = p_end_month AND p_start_day > p_end_day ) THEN
957
958 IF l_start_day = 29 AND l_start_month = 02 THEN
959 leap_chk_start_date(l_start_day, l_start_month, l_year-1);
960 END IF;
961 IF l_end_day = 29 AND l_end_month = 02 THEN
962 leap_chk_end_date(l_end_day, l_end_month, l_year);
963 END IF;
964
965 -- First Scenario
966 -- Check whether the commencement date lies between the range of the Start Day/Month with the year of the commencement date Minus 1
967 -- and the End Day/Month with the year of the commencement date. If yes then return TRUE.
968 IF p_commencement_dt BETWEEN TO_DATE(l_start_day||'-'||l_start_month||'-'||TO_CHAR(l_year - 1),'DD-MM-YYYY')
969 AND TO_DATE(l_end_day||'-'||l_end_month||'-'||TO_CHAR(l_year),'DD-MM-YYYY') THEN
970 RETURN 'TRUE';
971 END IF;
972
973 --Setting all the local variables again, in case value of these might have been changed
974 l_start_day := p_start_day;
975 l_start_month := p_start_month;
976 l_end_day := p_end_day;
977 l_end_month := p_end_month;
978
979 IF l_start_day = 29 AND l_start_month = 02 THEN
980 leap_chk_start_date(l_start_day, l_start_month, l_year);
981 END IF;
982 IF l_end_day = 29 AND l_end_month = 02 THEN
983 leap_chk_end_date(l_end_day, l_end_month, l_year+1);
984 END IF;
985
986 -- Second Scenario
987 -- Check whether the commencement date lies between the range of the Start Day/Month with the year of the commencement date
988 -- and the End Day/Month with the year of the commencement date plus 1. If yes then return TRUE.
989 IF p_commencement_dt BETWEEN TO_DATE(l_start_day||'-'||l_start_month||'-'||TO_CHAR(l_year),'DD-MM-YYYY')
990 AND TO_DATE(l_end_day||'-'||l_end_month||'-'||TO_CHAR(l_year + 1),'DD-MM-YYYY') THEN
991 RETURN 'TRUE';
992 END IF;
993 END IF;
994
995 --If fails above then return FALSE
996 RETURN 'FALSE';
997 END get_commence_date_range;
998
999 PROCEDURE enrp_ins_btch_prenrl(
1000 p_course_cd IN VARCHAR2 ,
1001 p_acad_cal_type IN VARCHAR2 ,
1002 p_acad_sequence_number IN NUMBER ,
1003 p_course_type IN VARCHAR2 ,
1004 p_responsible_org_unit_cd IN VARCHAR2 ,
1005 p_location_cd IN VARCHAR2 ,
1006 p_attendance_type IN VARCHAR2 ,
1007 p_attendance_mode IN VARCHAR2 ,
1008 p_student_comm_type IN VARCHAR2 ,
1009 p_person_group_id IN NUMBER ,
1010 p_dflt_enrolment_cat IN VARCHAR2 ,
1011 p_units_indicator IN VARCHAR2 ,
1012 p_override_enr_form_due_dt IN DATE ,
1013 p_override_enr_pckg_prod_dt IN DATE ,
1014 p_enr_cal_type IN VARCHAR2 ,
1015 p_enr_sequence_number IN NUMBER ,
1016 p_last_enrolment_cat IN VARCHAR2 ,
1017 p_admission_cat IN VARCHAR2 ,
1018 p_adm_cal_type IN VARCHAR2 ,
1019 p_adm_sequence_number IN NUMBER ,
1020 p_dflt_confirmed_ind IN VARCHAR2 ,
1021 p_unit1_unit_cd IN VARCHAR2 ,
1022 p_unit1_cal_type IN VARCHAR2 ,
1023 p_unit1_location_cd IN VARCHAR2 ,
1024 p_unit1_unit_class IN VARCHAR2 ,
1025 p_unit2_unit_cd IN VARCHAR2 ,
1026 p_unit2_cal_type IN VARCHAR ,
1027 p_unit2_location_cd IN VARCHAR2 ,
1028 p_unit2_unit_class IN VARCHAR2 ,
1029 p_unit3_unit_cd IN VARCHAR2 ,
1030 p_unit3_cal_type IN VARCHAR2 ,
1031 p_unit3_location_cd IN VARCHAR2 ,
1032 p_unit3_unit_class IN VARCHAR2 ,
1033 p_unit4_unit_cd IN VARCHAR2 ,
1034 p_unit4_cal_type IN VARCHAR2 ,
1035 p_unit4_location_cd IN VARCHAR2 ,
1036 p_unit4_unit_class IN VARCHAR2 ,
1037 p_unit5_unit_cd IN VARCHAR2 ,
1038 p_unit5_cal_type IN VARCHAR2 ,
1039 p_unit5_location_cd IN VARCHAR2 ,
1040 p_unit5_unit_class IN VARCHAR2 ,
1041 p_unit6_unit_cd IN VARCHAR2 ,
1042 p_unit6_cal_type IN VARCHAR2 ,
1043 p_unit6_location_cd IN VARCHAR2 ,
1044 p_unit6_unit_class IN VARCHAR2 ,
1045 p_unit7_unit_cd IN VARCHAR2 ,
1046 p_unit7_cal_type IN VARCHAR2 ,
1047 p_unit7_location_cd IN VARCHAR2 ,
1048 p_unit7_unit_class IN VARCHAR2 ,
1049 p_unit8_unit_cd IN VARCHAR2 ,
1050 p_unit8_cal_type IN VARCHAR2 ,
1051 p_unit8_location_cd IN VARCHAR2 ,
1052 p_unit8_unit_class IN VARCHAR2 ,
1053 p_unit9_unit_cd IN VARCHAR2 , --cloumns are added w.r.t. YOP-EN build by kkillams from p_unit9_unit_cd to p_unit_set_cd2
1054 p_unit9_cal_type IN VARCHAR2 ,
1055 p_unit9_location_cd IN VARCHAR2 ,
1056 p_unit9_unit_class IN VARCHAR2 ,
1057 p_unit10_unit_cd IN VARCHAR2 ,
1058 p_unit10_cal_type IN VARCHAR2 ,
1059 p_unit10_location_cd IN VARCHAR2 ,
1060 p_unit10_unit_class IN VARCHAR2 ,
1061 p_unit11_unit_cd IN VARCHAR2 ,
1062 p_unit11_cal_type IN VARCHAR2 ,
1063 p_unit11_location_cd IN VARCHAR2 ,
1064 p_unit11_unit_class IN VARCHAR2 ,
1065 p_unit12_unit_cd IN VARCHAR2 ,
1066 p_unit12_cal_type IN VARCHAR2 ,
1067 p_unit12_location_cd IN VARCHAR2 ,
1068 p_unit12_unit_class IN VARCHAR2 ,
1069 p_unit_set_cd1 IN VARCHAR2 ,
1070 p_unit_set_cd2 IN VARCHAR2 ,
1071 -- The Below five parameters are added as part of the Enh bug#2580731
1072 p_start_day IN NUMBER,
1073 p_start_month IN NUMBER,
1074 p_end_day IN NUMBER,
1075 p_end_month IN NUMBER,
1076 p_selection_date IN DATE,
1077 --Below parameter added as part of ENCR030(UK Enh) - Bug#2708430
1078 p_completion_date IN DATE ,
1079 p_log_creation_dt OUT NOCOPY DATE,
1080 p_progress_stat IN VARCHAR2,
1081 p_dflt_enr_method IN VARCHAR2,
1082 p_load_cal_type IN VARCHAR2,
1083 p_load_ci_seq_num IN NUMBER)
1084 AS
1085 /* HISTORY
1086 WHO WHEN WHAT
1087 ayedubat 25-MAY-2002 Changed the cursor,c_acaiv to replace the view,IGS_AD_PS_APPL_INST_APLINST_V
1088 with the base table,IGS_AD_PS_APPL_INST and also replaced the function calls
1089 Igs_En_Gen_002.enrp_get_acai_offer and Igs_En_Gen_014.ENRS_GET_WITHIN_CI as aprt of the bug fix: 2384449
1090 Nishikant 11JUN2002 Bug#2392277. The cursor c_sca modified to add a condition to check whether any of the unit code parameter is provided or not.
1091 If provided then the pre enrollment process will consider all the unit code(s) provided for the persons
1092 evenif they have already enrolled into any unit in the provided enrollment period.
1093 Nishikant 04OCT2002 UK Enhancement build. Bug#2580731. Five new parameters p_start_day, p_start_month, p_end_day, p_end_month, p_selection_date
1094 added to the procedure. Also the cursor c_sca modified to call local Function get_commence_date_range.
1095 Nishikant 16DEC2002 ENCR030(UK Enh), Bug#2708430. One more parameter p_completion date added to the signature.
1096 ptandon 23-JUN-2003 The Cursor c_sca was modified to replace call to the IGS_EN_GEN_014.ENRS_GET_WITHIN_CI routine with
1097 direct joins to the calendar instance tables. Bug# 3004806.
1098 svanukur 02-jul-2003 The cursors c_sca and c_acaiv were modified to include only the active members of a groupid
1099 as per bug# 3030782
1100 rvivekan 29-JUL-2003 Modified several message_name variables from varchar2(30) to varchar2(2000) as
1101 a part of bug#3045405
1102 ckasu 20-Feb-2006 modified cursor c_sca as a part of performance bug#5049068
1103
1104 */
1105 BEGIN -- enrp_ins_btch_prenrl
1106 -- This routine will pre-enrol a group of students in
1107 -- ?batch? mode as specified by the parameters passed to it.
1108 -- The routine will process both new and returning students,
1109 -- with the logic being the following:
1110 -- New Students:
1111 -- * Loop through IGS_AD_PS_APPL_INST_APLINST_V records matching the academic period
1112 -- and the specified IGS_PS_COURSE offering option parameters (eg. IGS_PS_COURSE code,
1113 -- IGS_AD_LOCATION, etc).
1114 -- * Call the ENRP_INS_SNEW_PRENRL routine to perform the pre-enrolment on each
1115 -- student.
1116 -- Returning Students:
1117 -- * Loop through IGS_EN_STDNT_PS_ATT records with a status of ENROLLED,
1118 -- INACTIVE, INTERMIT or UNCONFIRM matching the specified IGS_PS_COURSE offering
1119 -- option parameters.
1120 -- * Call the ENRP_INS_SRET_PRENRL routine to perform the pre-enrolment on
1121 -- each student.
1122 -- The output from the processing is logged to the IGS_GE_S_LOG table, which produces
1123 -- an- exception report.
1124 -- Following is a description of the parameters:
1125 -- p_course_cd; the IGS_PS_COURSE on which to match students. Can be %.
1126 -- p_acad_cal_type, p_acad_sequence_number; the academic calendar type the
1127 -- pre-enrolment is for.
1128 -- p_location_cd, p_attendance_type, p_attendance_mode; the elements of the
1129 -- student's enrolled IGS_PS_COURSE offering option on which to match. All of these
1130 -- can be %
1131 -- p_student_comm_type; the commencement type of students to process, being
1132 -- ?NEW? or ?RETURN?. No ALL option is permitted.
1133 -- p_person_id_group (optional); a IGS_PE_PERSON id group from which to limit the
1134 -- students processed. This will allow the pre-enrolments to be confined to
1135 -- any nominated group of specific students.
1136 -- p_dflt_enrolment_cat (optional); indicates the default enrolment cat. This
1137 -- value will only be used if the student has not had one specified (via the
1138 -- admission category) through admissions, or if (for re-enrolling) there
1139 -- wasn?t a category from a previous enrolment period.
1140 -- p_enrol_cal_type, p_enrol_sequence_number; the target enrolment period for
1141 -- all of the students pre-enrolled. Applies to only returning students. New
1142 -- students are always derived from their admission period.
1143 -- p_units_indicator; indicates whether to pre-enrol IGS_PS_UNIT attempts.
1144 -- p_dflt_fee_cat (optional); the fee category to assign to new students being
1145 -- pre-enrolled. Only applies to new students. Any fee category assigned to
1146 -- the student through admissions will take precedence.
1147 -- p_dflt_correspondence_cat (optional); the correspondence category to assign
1148 -- to new students being pre-enrolled. Only applies to new students.
1149 -- p_dflt_confirmed_ind; indicates whether to default the created IGS_PS_COURSE/IGS_PS_UNIT
1150 -- attempts to confirmed or not (defaults to N). For returning students it
1151 -- only applies to the IGS_PS_UNIT attempts as the IGS_PS_COURSE attempt will have
1152 -- already been confirmed.
1153 -- p_override_enr_form_due_dt (optional); the enrolment form due date which
1154 -- will be entered as the override against the students pre-enrolment
1155 -- detail. This will override the date alias stored against the enrolment
1156 -- period calendar instance.
1157 -- p_override_enr_pckg_prod_dt (optional); the enrolment package production
1158 -- date which will be entered as the override against the students
1159 -- pre-enrolment detail. This will override the date alias stored against
1160 -- the enrolment period calendar.
1161 -- p_unit1_unit_cd, p_unit1_cal_type, p_unit1_location_cd,
1162 -- p_unit1_unit_class (1-8)
1163 -- (optional): represent the IGS_PS_UNIT attempts in which to enrol all students being
1164 -- pre-enrolled. The version number is not specified, as it will assume the
1165 -- current ACTIVE and non-expired version. The calendar instance will be
1166 -- determined from the academic calendar instance in which the
1167 -- pre-enrolment is occurring. IGS_GE_NOTE: In the first instance, this will only
1168 -- be possible for NEW students.
1169 DECLARE
1170 cst_enrolled CONSTANT VARCHAR(10) := 'ENROLLED';
1171 cst_inactive CONSTANT VARCHAR(10) := 'INACTIVE';
1172 cst_intermit CONSTANT VARCHAR(10) := 'INTERMIT';
1173 cst_active CONSTANT VARCHAR(10) := 'ACTIVE';
1174 cst_success CONSTANT VARCHAR(10) := 'SUCCESS';
1175 cst_error CONSTANT VARCHAR2(5) := 'ERROR';
1176 cst_return CONSTANT VARCHAR(10) := 'RETURN';
1177 cst_exception CONSTANT VARCHAR(10) := 'EXCEPTION';
1178 cst_pre_enrol CONSTANT VARCHAR(10) := 'PRE-ENROL';
1179 cst_new CONSTANT VARCHAR(10) := 'NEW';
1180
1181 CURSOR c_sca IS
1182 SELECT sca.person_id,
1183 sca.course_cd
1184 FROM IGS_EN_STDNT_PS_ATT sca,
1185 IGS_PS_VER crv,
1186 IGS_PS_STAT cs
1187 WHERE sca.course_cd LIKE p_course_cd AND
1188 sca.cal_type = p_acad_cal_type AND
1189 sca.location_cd LIKE p_location_cd AND
1190 sca.attendance_type LIKE p_attendance_type AND
1191 sca.attendance_mode LIKE p_attendance_mode AND
1192 sca.course_attempt_status IN (
1193 cst_enrolled,
1194 cst_inactive,
1195 cst_intermit) AND
1196 (p_person_group_id IS NULL OR
1197 EXISTS (SELECT 'x'
1198 FROM IGS_PE_PRSID_GRP_MEM gm
1199 WHERE gm.group_id = p_person_group_id AND
1200 gm.person_id = sca.person_id AND
1201 (gm.end_date IS NULL OR gm.end_date >= trunc(sysdate))AND
1202 (gm.start_date IS NULL OR gm.start_date <= trunc(sysdate)))) AND
1203 crv.course_cd = sca.course_cd AND
1204 crv.version_number = sca.version_number AND
1205 crv.course_status = cs.course_status AND
1206 cs.s_course_status = cst_active AND
1207 crv.course_type like p_course_type AND
1208 (crv.responsible_org_unit_cd LIKE p_responsible_org_unit_cd OR
1209 EXISTS (SELECT 'x'
1210 FROM IGS_OR_INST_ORG_BASE_V ou,
1211 IGS_OR_STATUS os
1212 WHERE ou.PARTY_NUMBER LIKE p_responsible_org_unit_cd AND
1213 ou.org_status = os.org_status AND
1214 ou.inst_org_ind = 'O' AND
1215 os.s_org_status = cst_active AND
1216 IGS_OR_GEN_001.ORGP_GET_WITHIN_OU(
1217 ou.PARTY_NUMBER,
1218 ou.start_dt,
1219 crv.responsible_org_unit_cd,
1220 crv.responsible_ou_start_dt,
1221 'N') = 'Y')) AND
1222 -- IGS_GE_NOTE: this section of the query deals with determining if the student has
1223 -- already been pre-enrolled and whether they may require a IGS_PS_UNIT pre-enrolment
1224 (
1225 NOT EXISTS (
1226 SELECT person_id
1227 FROM IGS_AS_SC_ATMPT_ENR
1228 WHERE person_id = sca.person_id AND
1229 course_cd = sca.course_cd AND
1230 cal_type = p_enr_cal_type AND
1231 ci_sequence_number = p_enr_sequence_number) OR
1232 -- The bellow condition is added by Nishikant - bug#2392277 - 11JUN2002.
1233 -- Its checking whether any of the unit code parameter is provided or not.
1234 ( p_unit1_unit_cd is not null OR
1235 p_unit2_unit_cd is not null OR
1236 p_unit3_unit_cd is not null OR
1237 p_unit4_unit_cd is not null OR
1238 p_unit5_unit_cd is not null OR
1239 p_unit6_unit_cd is not null OR
1240 p_unit7_unit_cd is not null OR
1241 p_unit8_unit_cd is not null OR
1242 p_unit9_unit_cd is not null OR
1243 p_unit10_unit_cd is not null OR
1244 p_unit11_unit_cd is not null OR
1245 p_unit12_unit_cd is not null ) OR
1246 ( ( p_units_indicator = 'Y' OR p_units_indicator = 'CORE_ONLY') AND
1247 EXISTS (
1248 SELECT course_cd
1249 FROM IGS_PS_PAT_OF_STUDY pos
1250 WHERE course_cd = sca.course_cd AND
1251 version_number = sca.version_number) AND
1252 NOT EXISTS (
1253 SELECT person_id
1254 FROM IGS_EN_SU_ATTEMPT sua,
1255 IGS_CA_INST_REL cr
1256 WHERE sua.person_id = sca.person_id AND
1257 sua.course_cd = sca.course_cd AND
1258 sua.cal_type = cr.sub_cal_type AND
1259 sua.ci_sequence_number = cr.sub_ci_sequence_number AND
1260 cr.sup_cal_type = p_acad_cal_type AND
1261 cr.sup_ci_sequence_number= p_acad_sequence_number)
1262 ))
1263 -- The Below Function call added as part of the UK Enhancement. Enh bug#2580731 - 04OCT2002
1264 AND get_commence_date_range(
1265 p_start_day,
1266 p_start_month,
1267 p_end_day,
1268 p_end_month,
1269 sca.commencement_dt) = 'TRUE';
1270
1271 CURSOR c_scae (
1272 cp_person_id IGS_AS_SC_ATMPT_ENR.person_id%TYPE,
1273 cp_course_cd IGS_AS_SC_ATMPT_ENR.course_cd%TYPE,
1274 cp_start_dt IGS_CA_INST.start_dt%TYPE) IS
1275 SELECT scae.enrolment_cat
1276 FROM IGS_AS_SC_ATMPT_ENR scae,
1277 IGS_CA_INST ci
1278 WHERE scae.person_id = cp_person_id AND
1279 scae.course_cd = cp_course_cd AND
1280 ci.cal_type = scae.cal_type AND
1281 ci.sequence_number = scae.ci_sequence_number AND
1282 ci.start_dt <= cp_start_dt;
1283 v_scae_rec c_scae%ROWTYPE;
1284
1285 -- Cursor to fetch the list of students eligible to pre-enrollment
1286 CURSOR c_acaiv IS
1287 SELECT acai.person_id,
1288 acai.course_cd,
1289 acai.admission_appl_number,
1290 acai.nominated_course_cd,
1291 acai.sequence_number
1292 FROM IGS_AD_PS_APPL_INST acai,
1293 IGS_AD_APPL aa,
1294 IGS_PS_VER crv,
1295 IGS_PS_STAT cs
1296 WHERE
1297 acai.course_cd LIKE p_course_cd AND
1298 acai.location_cd LIKE p_location_cd AND
1299 acai.attendance_mode LIKE p_attendance_mode AND
1300 acai.attendance_type LIKE p_attendance_type AND
1301 aa.person_id = acai.person_id AND
1302 aa.admission_appl_number = acai.admission_appl_number AND
1303 aa.admission_cat LIKE p_admission_cat AND
1304 aa.acad_cal_type = p_acad_cal_type AND
1305 aa.acad_ci_sequence_number = p_acad_sequence_number AND
1306 (( p_adm_cal_type IS NULL AND
1307 p_adm_sequence_number IS NULL) OR
1308 ( NVL(acai.adm_cal_type,aa.adm_cal_type) = p_adm_cal_type AND
1309 NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) = p_adm_sequence_number) ) AND
1310 EXISTS( SELECT 'X'
1311 FROM IGS_AD_OU_STAT os,
1312 IGS_AD_OFR_RESP_STAT rs
1313 WHERE os.adm_outcome_status = acai.adm_outcome_status AND
1314 rs.adm_offer_resp_status = acai.adm_offer_resp_status AND
1315 IGS_AD_GEN_008.Admp_Get_Saos(acai.adm_outcome_status) IN ('OFFER','COND-OFFER') AND
1316 IGS_AD_GEN_008.Admp_Get_Saors(acai.adm_offer_resp_status) NOT IN ('LAPSED','REJECTED')) AND
1317 (p_person_group_id is null OR
1318 EXISTS (SELECT 'x'
1319 FROM IGS_PE_PRSID_GRP_MEM gm
1320 WHERE gm.group_id = p_person_group_id AND
1321 gm.person_id = acai.person_id AND
1322 (gm.end_date IS NULL OR gm.end_date >= trunc(sysdate))AND
1323 (gm.start_date IS NULL OR gm.start_date <= trunc(sysdate)) )) AND
1324 crv.course_cd = acai.course_cd AND
1325 crv.version_number = acai.crv_version_number AND
1326 crv.course_status = cs.course_status AND
1327 cs.s_course_status = cst_active AND
1328 crv.course_type LIKE p_course_type AND
1329 (crv.responsible_org_unit_cd LIKE p_responsible_org_unit_cd OR
1330 EXISTS (SELECT 'x'
1331 FROM IGS_OR_UNIT ou,
1332 IGS_OR_STATUS os
1333 WHERE ou.org_unit_cd LIKE p_responsible_org_unit_cd AND
1334 ou.org_status = os.org_status AND
1335 os.s_org_status = cst_active AND
1336 IGS_OR_GEN_001.ORGP_GET_WITHIN_OU(
1337 ou.org_unit_cd,
1338 ou.start_dt,
1339 crv.responsible_org_unit_cd,
1340 crv.responsible_ou_start_dt,'N') = 'Y' ) ) AND
1341 -- IGS_GE_NOTE: this section of the query deals with determining if the student has
1342 -- already been pre-enrolled and whether they may require a IGS_PS_UNIT pre-enrolment
1343 (NOT EXISTS (
1344 SELECT person_id
1345 FROM IGS_EN_STDNT_PS_ATT
1346 WHERE person_id = acai.person_id AND
1347 course_cd = acai.course_cd AND
1348 adm_admission_appl_number = acai.admission_appl_number AND
1349 adm_nominated_course_cd = acai.nominated_course_cd AND
1350 adm_sequence_number = acai.sequence_number) OR
1351 (p_unit1_unit_cd is not null OR
1352 p_unit2_unit_cd is not null OR
1353 p_unit3_unit_cd is not null OR
1354 p_unit4_unit_cd is not null OR
1355 p_unit5_unit_cd is not null OR
1356 p_unit6_unit_cd is not null OR
1357 p_unit7_unit_cd is not null OR
1358 p_unit8_unit_cd is not null OR
1359 p_unit9_unit_cd is not null OR
1360 p_unit10_unit_cd is not null OR
1361 p_unit11_unit_cd is not null OR
1362 p_unit12_unit_cd is not null) OR
1363 ( ( p_units_indicator = 'Y' OR p_units_indicator = 'CORE_ONLY') AND
1364 EXISTS (
1365 SELECT course_cd
1366 FROM IGS_PS_PAT_OF_STUDY pos
1367 WHERE course_cd = acai.course_cd AND
1368 version_number = acai.crv_version_number ) AND
1369 NOT EXISTS (
1370 SELECT person_id
1371 FROM IGS_EN_SU_ATTEMPT sua,
1372 IGS_CA_INST_REL cr
1373 WHERE sua.person_id = acai.person_id AND
1374 sua.course_cd = acai.course_cd AND
1375 sua.cal_type = cr.sub_cal_type AND
1376 sua.ci_sequence_number = cr.sub_ci_sequence_number AND
1377 cr.sup_cal_type = p_acad_cal_type AND
1378 cr.SUP_CI_SEQUENCE_NUMBER= p_acad_sequence_number) ))
1379 ORDER BY
1380 acai.person_id,
1381 acai.course_cd,
1382 acai.offer_dt DESC;
1383
1384 CURSOR c_eci IS
1385 SELECT start_dt
1386 FROM IGS_CA_INST eci
1387 WHERE cal_type = p_enr_cal_type AND
1388 sequence_number = p_enr_sequence_number;
1389 --new cursor is by kkillams added w.r.t. to YOP-EN build bug id :2156956
1390 CURSOR c_yop_us_st(cp_person_id NUMBER,
1391 cp_course_cd VARCHAR2,
1392 cp_unit_set_cd VARCHAR2)IS
1393 SELECT sca.course_cd,susa.unit_set_cd
1394 FROM IGS_AS_SU_SETATMPT susa,
1395 IGS_EN_STDNT_PS_ATT sca
1396 WHERE sca.person_id =cp_person_id
1397 AND sca.course_cd =cp_course_cd
1398 AND susa.person_id =sca.person_id
1399 AND susa.course_cd =sca.course_cd
1400 AND susa.unit_set_cd=cp_unit_set_cd
1401 AND susa.selection_dt IS NOT NULL
1402 AND susa.rqrmnts_complete_dt IS NULL
1403 AND susa.end_dt IS NULL;
1404 r_yop_us_st c_yop_us_st%ROWTYPE;
1405 CURSOR c_load_cal(p_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1406 p_acad_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
1407 SELECT rel.sub_cal_type, rel.sub_ci_sequence_number FROM igs_ca_inst_rel rel,
1408 igs_ca_inst ci,
1409 igs_ca_type cal
1410 WHERE rel.sup_cal_type = p_acad_cal_type
1411 AND rel.sup_ci_sequence_number = p_acad_seq_num
1412 AND rel.sub_cal_type = ci.cal_type
1413 AND rel.sub_ci_sequence_number = ci.sequence_number
1414 AND rel.sub_cal_type = cal.cal_type
1415 AND cal.s_cal_cat = 'LOAD'
1416 AND cal.closed_ind = 'N'
1417 ORDER BY ci.start_dt;
1418
1419 v_eci_rec c_eci%ROWTYPE;
1420 v_person_id IGS_AD_PS_APPL_INST.person_id%TYPE;
1421 v_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE;
1422 v_log_creation_dt DATE ;
1423 v_log_error_ind VARCHAR2(1) := 'N';
1424 v_warn_level VARCHAR2(10) := NULL;
1425 v_message_name Varchar2(2000) := NULL;
1426 successful_total NUMBER := 0;
1427 exception_total NUMBER := 0;
1428 v_output_message VARCHAR(255) := NULL;
1429 l_us_count NUMBER := 0;
1430 l_load_cal_type igs_ca_inst.cal_type%TYPE;
1431 l_load_seq_num igs_ca_inst.sequence_number%TYPE;
1432 l_enr_method igs_en_method_type.enr_method_type%TYPE;
1433 l_return_status VARCHAR2(10);
1434 l_message VARCHAR2(100);
1435 l_mesg_txt VARCHAR2(4000);
1436 vl_process BOOLEAN;
1437 v_prog_outcome igs_pr_ou_type.s_progression_outcome_type%TYPE;
1438 l_enc_message_name VARCHAR2(2000);
1439 l_app_short_name VARCHAR2(10);
1440 l_message_name VARCHAR2(100);
1441 l_msg_index NUMBER;
1442 BEGIN
1443
1444 v_person_id := NULL ;
1445 v_course_cd := NULL ;
1446 v_log_creation_dt := NULL ;
1447
1448 -- Initialise the log for reporting of IGS_GE_EXCEPTIONS
1449 IGS_GE_GEN_003.genp_ins_log (cst_pre_enrol,
1450 p_course_cd || ',' ||
1451 p_acad_cal_type || ',' ||
1452 TO_CHAR(p_acad_sequence_number) || ',' ||
1453 p_course_type || ',' ||
1454 p_responsible_org_unit_cd || ',' ||
1455 TO_CHAR(p_person_group_id) || ',' ||
1456 p_location_cd || ',' ||
1457 p_attendance_mode || ',' ||
1458 p_attendance_type || ',' ||
1459 p_student_comm_type || ',' ||
1460 p_dflt_enrolment_cat || ',' ||
1461 p_units_indicator || ',' ||
1462 igs_ge_date.igschar(p_override_enr_form_due_dt) || ',' ||
1463 igs_ge_date.igscharDT(p_override_enr_pckg_prod_dt) || ',' ||
1464 p_enr_cal_type || ',' ||
1465 TO_CHAR(p_enr_sequence_number) || ',' ||
1466 p_last_enrolment_cat || ',' ||
1467 p_admission_cat || ',' ||
1468 p_adm_cal_type || ',' ||
1469 TO_CHAR(p_adm_sequence_number) || ',' ||
1470 p_dflt_confirmed_ind || ',' ||
1471 NVL(p_unit1_unit_cd,'')||'/'||
1472 NVL(p_unit1_cal_type,'')||'/'||
1473 NVL(p_unit1_location_cd,'')||'/'||
1474 NVL(p_unit1_unit_class,'')||','||
1475 NVL(p_unit2_unit_cd,'')||'/'||
1476 NVL(p_unit2_cal_type,'')||'/'||
1477 NVL(p_unit2_location_cd,'')||'/'||
1478 NVL(p_unit2_unit_class,'')||','||
1479 NVL(p_unit3_unit_cd,'')||'/'||
1480 NVL(p_unit3_cal_type,'')||'/'||
1481 NVL(p_unit3_location_cd,'')||'/'||
1482 NVL(p_unit3_unit_class,'')||','||
1483 NVL(p_unit4_unit_cd,'')||'/'||
1484 NVL(p_unit4_cal_type,'')||'/'||
1485 NVL(p_unit4_location_cd,'')||'/'||
1486 NVL(p_unit4_unit_class,'')||','||
1487 NVL(p_unit5_unit_cd,'')||'/'||
1488 NVL(p_unit5_cal_type,'')||'/'||
1489 NVL(p_unit5_location_cd,'')||'/'||
1490 NVL(p_unit5_unit_class,'')||','||
1491 NVL(p_unit6_unit_cd,'')||'/'||
1492 NVL(p_unit6_cal_type,'')||'/'||
1493 NVL(p_unit6_location_cd,'')||'/'||
1494 NVL(p_unit6_unit_class,'')||','||
1495 NVL(p_unit7_unit_cd,'')||'/'||
1496 NVL(p_unit7_cal_type,'')||'/'||
1497 NVL(p_unit7_location_cd,'')||'/'||
1498 NVL(p_unit7_unit_class,'')||','||
1499 NVL(p_unit8_unit_cd,'')||'/'||
1500 NVL(p_unit8_cal_type,'')||'/'||
1501 NVL(p_unit8_location_cd,'')||'/'||
1502 NVL(p_unit8_unit_class,'')||'/'||
1503 NVL(p_unit9_unit_cd,'')||'/'||
1504 NVL(p_unit9_cal_type,'')||'/'||
1505 NVL(p_unit9_location_cd,'')||'/'||
1506 NVL(p_unit9_unit_class,'')||'/'||
1507 NVL(p_unit10_unit_cd,'')||'/'||
1508 NVL(p_unit10_cal_type,'')||'/'||
1509 NVL(p_unit10_location_cd,'')||'/'||
1510 NVL(p_unit10_unit_class,'')||'/'||
1511 NVL(p_unit11_unit_cd,'')||'/'||
1512 NVL(p_unit11_cal_type,'')||'/'||
1513 NVL(p_unit11_location_cd,'')||'/'||
1514 NVL(p_unit11_unit_class,'')||'/'||
1515 NVL(p_unit12_unit_cd,'')||'/'||
1516 NVL(p_unit12_cal_type,'')||'/'||
1517 NVL(p_unit12_location_cd,'')||'/'||
1518 NVL(p_unit12_unit_class,'')||'/'||
1519 NVL(p_unit_set_cd1,'')||'/'||
1520 NVL(p_unit_set_cd2,'')||'/'||
1521 -- The Below five parameters are added as part of the UK Enhancement - bug#2580731 - 04OCT2002
1522 NVL(p_start_day,'')||'/'||
1523 NVL(p_start_month,'')||'/'||
1524 NVL(p_end_day,'')||'/'||
1525 NVL(p_end_month,'')||'/'||
1526 NVL(p_selection_date,'')||'/'||
1527 -- The Below parameter completion_date added as part of ENCR030(UK Enh) - Bug#2708430 - 16DEC2002
1528 NVL(p_completion_date,'')||'/'||
1529 p_dflt_enr_method||'/'||
1530 p_load_cal_type||'/'||
1531 TO_CHAR(p_load_ci_seq_num),
1532 v_log_creation_dt
1533 );
1534 p_log_creation_dt := v_log_creation_dt;
1535 IF p_load_cal_type IS NULL THEN
1536 OPEN c_load_cal(p_acad_cal_type,p_acad_sequence_number);
1537 FETCH c_load_cal INTO l_load_cal_type, l_load_seq_num;
1538 IF c_load_cal%NOTFOUND THEN
1539 CLOSE c_load_cal;
1540 Fnd_Message.Set_name('IGS','IGS_EN_CN_FIND_TERM_CAL');
1541 IGS_GE_MSG_STACK.ADD;
1542 App_Exception.Raise_Exception;
1543 END IF;
1544 CLOSE c_load_cal;
1545 ELSE
1546 l_load_cal_type := p_load_cal_type;
1547 l_load_seq_num := p_load_ci_seq_num;
1548 END IF;
1549 l_enr_method:=p_dflt_enr_method;
1550 IF p_student_comm_type = cst_new THEN
1551 -- Select all of the students who have been granted offers in the
1552 -- relevant academic period matching the supplied parameters.
1553 FOR v_acaiv_rec IN c_acaiv LOOP
1554 BEGIN
1555 -- Only process the latest offer in the IGS_PS_COURSE for the person_id
1556 IF (v_person_id IS NULL AND
1557 v_course_cd IS NULL) OR
1558 NOT (v_acaiv_rec.person_id = v_person_id AND
1559 v_acaiv_rec.course_cd = v_course_cd) THEN
1560 v_person_id := v_acaiv_rec.person_id;
1561 v_course_cd := v_acaiv_rec.course_cd;
1562 v_message_name := null;
1563 vl_process := FALSE;
1564 --logicall code is adding w.r.t. to YOP-EN build bug id :2156956 by kkillams
1565 --checking of passing parameter unitset are active or not.
1566 l_us_count :=0;
1567 --Checking the whether pre-enrollment profile value is set Y or not.
1568 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
1569 IF p_unit_set_cd1 is NULL THEN
1570 l_us_count:= l_us_count + 1;
1571 ELSE
1572 OPEN c_yop_us_st(v_acaiv_rec.person_id,v_acaiv_rec.course_cd,p_unit_set_cd1);
1573 FETCH c_yop_us_st INTO r_yop_us_st;
1574 IF c_yop_us_st%FOUND THEN
1575 l_us_count:= l_us_count + 1;
1576 END IF;
1577 CLOSE c_yop_us_st;
1578 END IF;
1579 IF p_unit_set_cd2 is NULL THEN
1580 l_us_count:= l_us_count + 1;
1581 ELSIF l_us_count =1 THEN
1582 OPEN c_yop_us_st(v_acaiv_rec.person_id,v_acaiv_rec.course_cd,p_unit_set_cd2);
1583 FETCH c_yop_us_st INTO r_yop_us_st;
1584 IF c_yop_us_st%FOUND THEN
1585 l_us_count:= l_us_count + 1;
1586 END IF;
1587 CLOSE c_yop_us_st;
1588 END IF;
1589 ELSE
1590 l_us_count:=2;
1591 END IF;
1592
1593 IF l_us_count = 2 THEN
1594 vl_process := TRUE;
1595 END IF;
1596
1597 IF vl_process THEN
1598 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
1599 v_prog_outcome := enrp_get_pr_outcome(v_acaiv_rec.person_id,v_acaiv_rec.course_cd);
1600 IF v_prog_outcome IS NULL THEN
1601 vl_process := FALSE;
1602 ELSIF v_prog_outcome = 'ADVANCE' AND NVL(p_progress_stat,'ADVANCE') IN ('ADVANCE','BOTH') THEN
1603 vl_process := TRUE;
1604 ELSIF v_prog_outcome = 'REPEATYR' AND NVL(p_progress_stat,'ADVANCE') IN ('REPEATYR','BOTH') THEN
1605 vl_process := TRUE;
1606 ELSIF v_prog_outcome = 'NEW' AND NVL(p_progress_stat,'ADVANCE') IN ('ADVANCE','BOTH') THEN
1607 vl_process := TRUE;
1608 ELSE
1609 vl_process := FALSE;
1610 END IF;
1611 END IF;
1612 END IF;
1613
1614 IF vl_process THEN
1615 -- Call the pre-enrolment routine for the single IGS_PE_PERSON
1616 IF IGS_EN_GEN_010.ENRP_INS_SNEW_PRENRL(
1617 v_acaiv_rec.person_id,
1618 v_acaiv_rec.course_cd,
1619 p_dflt_enrolment_cat,
1620 p_acad_cal_type,
1621 p_acad_sequence_number,
1622 p_units_indicator,
1623 p_dflt_confirmed_ind,
1624 p_override_enr_form_due_dt,
1625 p_override_enr_pckg_prod_dt,
1626 'Y', -- Check eligibility
1627 v_acaiv_rec.admission_appl_number,
1628 v_acaiv_rec.nominated_course_cd,
1629 v_acaiv_rec.sequence_number,
1630 p_unit1_unit_cd,
1631 p_unit1_cal_type,
1632 p_unit1_location_cd,
1633 p_unit1_unit_class,
1634 p_unit2_unit_cd,
1635 p_unit2_cal_type,
1636 p_unit2_location_cd,
1637 p_unit2_unit_class,
1638 p_unit3_unit_cd,
1639 p_unit3_cal_type,
1640 p_unit3_location_cd,
1641 p_unit3_unit_class,
1642 p_unit4_unit_cd,
1643 p_unit4_cal_type,
1644 p_unit4_location_cd,
1645 p_unit4_unit_class,
1646 p_unit5_unit_cd,
1647 p_unit5_cal_type,
1648 p_unit5_location_cd,
1649 p_unit5_unit_class,
1650 p_unit6_unit_cd,
1651 p_unit6_cal_type,
1652 p_unit6_location_cd,
1653 p_unit6_unit_class,
1654 p_unit7_unit_cd,
1655 p_unit7_cal_type,
1656 p_unit7_location_cd,
1657 p_unit7_unit_class,
1658 p_unit8_unit_cd,
1659 p_unit8_cal_type,
1660 p_unit8_location_cd,
1661 p_unit8_unit_class,
1662 v_log_creation_dt,
1663 v_warn_level,
1664 v_message_name,
1665 p_unit9_unit_cd,
1666 p_unit9_cal_type,
1667 p_unit9_location_cd,
1668 p_unit9_unit_class,
1669 p_unit10_unit_cd,
1670 p_unit10_cal_type,
1671 p_unit10_location_cd,
1672 p_unit10_unit_class,
1673 p_unit11_unit_cd,
1674 p_unit11_cal_type,
1675 p_unit11_location_cd,
1676 p_unit11_unit_class,
1677 p_unit12_unit_cd,
1678 p_unit12_cal_type,
1679 p_unit12_location_cd,
1680 p_unit12_unit_class,
1681 p_unit_set_cd1,
1682 p_unit_set_cd2,
1683 p_progress_stat,
1684 l_enr_method,
1685 l_load_cal_type,
1686 l_load_seq_num
1687 ) THEN
1688 -- Log entry indicating successful pre-enrolment.
1689 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1690 cst_pre_enrol,
1691 v_log_creation_dt ,
1692 cst_success || ',' ||
1693 v_acaiv_rec.person_id || ',' ||
1694 v_acaiv_rec.course_cd,
1695 'IGS_EN_SUCCESSFULLY_PRE_ENR',
1696 NULL);
1697 successful_total := successful_total + 1;
1698 ELSE
1699 exception_total := exception_total + 1;
1700 END IF;
1701 END IF; --Unit Set condition end if, l_us_count =2
1702 END IF;
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 IF v_log_creation_dt IS NOT NULL THEN
1706 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
1707 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
1708 IF l_message_name <> 'IGS_GE_UNHANDLED_EXP' THEN
1709 IF l_message_name IS NOT NULL THEN
1710 -- If the log creation date is set then log the HECS error
1711 -- This is if the pre-enrolment is being performed in batch.
1712 FND_MESSAGE.SET_NAME(l_app_short_name,l_message_name);
1713 l_mesg_txt := FND_MESSAGE.GET;
1714 igs_ge_gen_003.genp_ins_log_entry(p_s_log_type => cst_pre_enrol,
1715 p_creation_dt => v_log_creation_dt,
1716 p_key => cst_error||','||TO_CHAR(v_acaiv_rec.person_id)||','||v_acaiv_rec.course_cd,
1717 p_s_message_name => l_message_name,
1718 p_text => l_mesg_txt);
1719 END IF;
1720 ELSE
1721 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1722 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_008.enrp_ins_btch_prenrl');
1723 l_mesg_txt := fnd_message.get;
1724 igs_ge_gen_003.genp_ins_log_entry(p_s_log_type => cst_pre_enrol,
1725 p_creation_dt => v_log_creation_dt,
1726 p_key => cst_error||','||TO_CHAR(v_acaiv_rec.person_id)||','||v_acaiv_rec.course_cd,
1727 p_s_message_name => 'IGS_GE_UNHANDLED_EXP',
1728 p_text => l_mesg_txt);
1729 END IF;
1730 l_message_name := NULL;
1731 ELSE -- v_log_creation_dt is null
1732 RAISE;
1733 END IF;
1734 END;
1735 END LOOP;
1736 ELSIF p_student_comm_type = cst_return THEN
1737
1738 -- It is assumed that p_enr_cal_type and
1739 -- p_enr_sequence_number are not null and are
1740 -- correct.
1741 OPEN c_eci;
1742 FETCH c_eci INTO v_eci_rec;
1743 CLOSE c_eci;
1744 FOR v_sca_rec IN c_sca LOOP
1745 BEGIN
1746
1747 -- If the last enrolment category parameter has been set then
1748 -- check that the enrolment category last used for the IGS_PS_COURSE
1749 -- attempt matches the parameter
1750 IF p_last_enrolment_cat IS NOT NULL THEN
1751 OPEN c_scae(
1752 v_sca_rec.person_id,
1753 v_sca_rec.course_cd,
1754 v_eci_rec.start_dt);
1755 FETCH c_scae INTO v_scae_rec;
1756 IF c_scae%FOUND THEN
1757 CLOSE c_scae;
1758 IF v_scae_rec.enrolment_cat = p_last_enrolment_cat THEN
1759 v_message_name := null;
1760 vl_process := FALSE;
1761 --logicall code is adding w.r.t. to YOP-EN build bug id :2156956 by kkillams
1762 --checking of passing unitset are active or not.
1763 l_us_count:=0;
1764
1765 --Checking the whether pre-enrollment profile value is set Y or not.
1766 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
1767 IF p_unit_set_cd1 is NULL THEN
1768 l_us_count:= l_us_count + 1;
1769 ELSE
1770 OPEN c_yop_us_st(v_sca_rec.person_id,v_sca_rec.course_cd,p_unit_set_cd1);
1771 FETCH c_yop_us_st INTO r_yop_us_st;
1772 IF c_yop_us_st%FOUND THEN
1773 l_us_count:= l_us_count + 1;
1774 END IF;
1775 CLOSE c_yop_us_st;
1776 END IF;
1777 IF p_unit_set_cd2 is NULL THEN
1778 l_us_count:= l_us_count + 1;
1779 ELSIF l_us_count =1 THEN
1780 OPEN c_yop_us_st(v_sca_rec.person_id,v_sca_rec.course_cd,p_unit_set_cd2);
1781 FETCH c_yop_us_st INTO r_yop_us_st;
1782 IF c_yop_us_st%FOUND THEN
1783 l_us_count:= l_us_count + 1;
1784 END IF;
1785 CLOSE c_yop_us_st;
1786 END IF;
1787 ELSE
1788 l_us_count:=2;
1789 END IF;
1790
1791 IF l_us_count = 2 THEN
1792 vl_process := TRUE;
1793 END IF;
1794
1795 IF vl_process THEN
1796 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
1797 v_prog_outcome := enrp_get_pr_outcome(v_sca_rec.person_id,v_sca_rec.course_cd);
1798 IF v_prog_outcome IS NULL OR v_prog_outcome = 'NEW' THEN
1799 vl_process := FALSE;
1800 ELSIF v_prog_outcome = 'ADVANCE' AND NVL(p_progress_stat,'ADVANCE') IN ('ADVANCE','BOTH') THEN
1801 vl_process := TRUE;
1802 ELSIF v_prog_outcome = 'REPEATYR' AND NVL(p_progress_stat,'ADVANCE') IN ('REPEATYR','BOTH') THEN
1803 vl_process := TRUE;
1804 ELSE
1805 vl_process := FALSE;
1806 END IF;
1807 END IF;
1808 END IF;
1809
1810
1811
1812 IF vl_process THEN
1813 IF NOT IGS_EN_GEN_010.enrp_ins_sret_prenrl(
1814 v_sca_rec.person_id,
1815 v_sca_rec.course_cd,
1816 p_dflt_enrolment_cat,
1817 p_acad_cal_type,
1818 p_acad_sequence_number,
1819 p_enr_cal_type,
1820 p_enr_sequence_number,
1821 p_units_indicator,
1822 p_override_enr_form_due_dt,
1823 p_override_enr_pckg_prod_dt,
1824 v_log_creation_dt,
1825 v_warn_level,
1826 v_message_name,
1827 p_unit1_unit_cd,
1828 p_unit1_cal_type,
1829 p_unit1_location_cd,
1830 p_unit1_unit_class,
1831 p_unit2_unit_cd,
1832 p_unit2_cal_type,
1833 p_unit2_location_cd,
1834 p_unit2_unit_class,
1835 p_unit3_unit_cd,
1836 p_unit3_cal_type,
1837 p_unit3_location_cd,
1838 p_unit3_unit_class,
1839 p_unit4_unit_cd,
1840 p_unit4_cal_type,
1841 p_unit4_location_cd,
1842 p_unit4_unit_class,
1843 p_unit5_unit_cd,
1844 p_unit5_cal_type,
1845 p_unit5_location_cd,
1846 p_unit5_unit_class,
1847 p_unit6_unit_cd,
1848 p_unit6_cal_type,
1849 p_unit6_location_cd,
1850 p_unit6_unit_class,
1851 p_unit7_unit_cd,
1852 p_unit7_cal_type,
1853 p_unit7_location_cd,
1854 p_unit7_unit_class,
1855 p_unit8_unit_cd,
1856 p_unit8_cal_type,
1857 p_unit8_location_cd,
1858 p_unit8_unit_class,
1859 p_unit9_unit_cd,
1860 p_unit9_cal_type,
1861 p_unit9_location_cd,
1862 p_unit9_unit_class,
1863 p_unit10_unit_cd,
1864 p_unit10_cal_type,
1865 p_unit10_location_cd,
1866 p_unit10_unit_class,
1867 p_unit11_unit_cd,
1868 p_unit11_cal_type,
1869 p_unit11_location_cd,
1870 p_unit11_unit_class,
1871 p_unit12_unit_cd,
1872 p_unit12_cal_type,
1873 p_unit12_location_cd,
1874 p_unit12_unit_class,
1875 p_unit_set_cd1,
1876 p_unit_set_cd2,
1877 p_selection_date, --Added as part of the UK Enh Buid- Bug#2580731
1878 p_completion_date, --Added as part of ENCR030(UK Enh) - Bug#2708430 - 16DEC2002
1879 p_progress_stat,
1880 l_enr_method,
1881 l_load_cal_type,
1882 l_load_seq_num
1883 ) THEN
1884 exception_total := exception_total + 1;
1885 ELSE
1886 -- Log entry indicating successful pre-enrolment.
1887 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1888 cst_pre_enrol,
1889 v_log_creation_dt ,
1890 cst_success || ',' ||
1891 v_sca_rec.person_id || ',' ||
1892 v_sca_rec.course_cd,
1893 'IGS_EN_SUCCESSFULLY_PRE_ENR',
1894 NULL);
1895 successful_total := successful_total + 1;
1896 END IF;
1897 END IF; --UNIT SET's condition(IGS_PS_PRENRL_YEAR_IND ='Y' , l_count_us_st =2
1898 END IF; -- v_scae_rec.enrolment_cat = p_last_enrolment_cat
1899 ELSE -- c_scae%NOTFOUND
1900 CLOSE c_scae;
1901 END IF; -- c_scae
1902 ELSE
1903 vl_process := FALSE;
1904 --logicall code is adding w.r.t. to YOP-EN build bug id :2156956 by kkillams
1905 --checking of passing unitset are active or not.
1906 l_us_count:=0;
1907 --Checking the whether pre-enrollment profile value is set Y or not.
1908 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
1909 IF p_unit_set_cd1 is NULL THEN
1910 l_us_count:= l_us_count + 1;
1911 ELSE
1912 OPEN c_yop_us_st(v_sca_rec.person_id,v_sca_rec.course_cd,p_unit_set_cd1);
1913 FETCH c_yop_us_st INTO r_yop_us_st;
1914 IF c_yop_us_st%FOUND THEN
1915 l_us_count:= l_us_count + 1;
1916 END IF;
1917 CLOSE c_yop_us_st;
1918 END IF;
1919 IF p_unit_set_cd2 is NULL THEN
1920 l_us_count:= l_us_count + 1;
1921 ELSIF l_us_count =1 THEN
1922 OPEN c_yop_us_st(v_sca_rec.person_id,v_sca_rec.course_cd,p_unit_set_cd2);
1923 FETCH c_yop_us_st INTO r_yop_us_st;
1924 IF c_yop_us_st%FOUND THEN
1925 l_us_count:= l_us_count + 1;
1926 END IF;
1927 CLOSE c_yop_us_st;
1928 END IF;
1929 ELSE
1930 l_us_count:=2;
1931 END IF;
1932
1933 IF l_us_count = 2 THEN
1934 vl_process := TRUE;
1935 END IF;
1936
1937 IF vl_process THEN
1938 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
1939 v_prog_outcome := enrp_get_pr_outcome(v_sca_rec.person_id,v_sca_rec.course_cd);
1940 IF v_prog_outcome IS NULL OR v_prog_outcome = 'NEW' THEN
1941 vl_process := FALSE;
1942 ELSIF v_prog_outcome = 'ADVANCE' AND NVL(p_progress_stat,'ADVANCE') IN ('ADVANCE','BOTH') THEN
1943 vl_process := TRUE;
1944 ELSIF v_prog_outcome = 'REPEATYR' AND NVL(p_progress_stat,'ADVANCE') IN ('REPEATYR','BOTH') THEN
1945 vl_process := TRUE;
1946 ELSE
1947 vl_process := FALSE;
1948 END IF;
1949 END IF;
1950 END IF;
1951
1952 IF vl_process THEN
1953 v_message_name := null;
1954 IF NOT IGS_EN_GEN_010.enrp_ins_sret_prenrl(
1955 v_sca_rec.person_id,
1956 v_sca_rec.course_cd,
1957 p_dflt_enrolment_cat,
1958 p_acad_cal_type,
1959 p_acad_sequence_number,
1960 p_enr_cal_type,
1961 p_enr_sequence_number,
1962 p_units_indicator,
1963 p_override_enr_form_due_dt,
1964 p_override_enr_pckg_prod_dt,
1965 v_log_creation_dt,
1966 v_warn_level,
1967 v_message_name,
1968 p_unit1_unit_cd,
1969 p_unit1_cal_type,
1970 p_unit1_location_cd,
1971 p_unit1_unit_class,
1972 p_unit2_unit_cd,
1973 p_unit2_cal_type,
1974 p_unit2_location_cd,
1975 p_unit2_unit_class,
1976 p_unit3_unit_cd,
1977 p_unit3_cal_type,
1978 p_unit3_location_cd,
1979 p_unit3_unit_class,
1980 p_unit4_unit_cd,
1981 p_unit4_cal_type,
1982 p_unit4_location_cd,
1983 p_unit4_unit_class,
1984 p_unit5_unit_cd,
1985 p_unit5_cal_type,
1986 p_unit5_location_cd,
1987 p_unit5_unit_class,
1988 p_unit6_unit_cd,
1989 p_unit6_cal_type,
1990 p_unit6_location_cd,
1991 p_unit6_unit_class,
1992 p_unit7_unit_cd,
1993 p_unit7_cal_type,
1994 p_unit7_location_cd,
1995 p_unit7_unit_class,
1996 p_unit8_unit_cd,
1997 p_unit8_cal_type,
1998 p_unit8_location_cd,
1999 p_unit8_unit_class,
2000 p_unit9_unit_cd,
2001 p_unit9_cal_type,
2002 p_unit9_location_cd,
2003 p_unit9_unit_class,
2004 p_unit10_unit_cd,
2005 p_unit10_cal_type,
2006 p_unit10_location_cd,
2007 p_unit10_unit_class,
2008 p_unit11_unit_cd,
2009 p_unit11_cal_type,
2010 p_unit11_location_cd,
2011 p_unit11_unit_class,
2012 p_unit12_unit_cd,
2013 p_unit12_cal_type,
2014 p_unit12_location_cd,
2015 p_unit12_unit_class,
2016 p_unit_set_cd1,
2017 p_unit_set_cd2,
2018 p_selection_date, --Added as part of the UK Enh Buid- Bug#2580731
2019 p_completion_date, --Added as part of ENCR030(UK Enh) - Bug#2708430 - 18DEC2002
2020 p_progress_stat,
2021 l_enr_method,
2022 l_load_cal_type,
2023 l_load_seq_num
2024 ) THEN
2025 exception_total := exception_total + 1;
2026 ELSE
2027 -- Log entry indicating successful pre-enrolment.
2028 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2029 cst_pre_enrol,
2030 v_log_creation_dt ,
2031 cst_success || ',' ||
2032 v_sca_rec.person_id || ',' ||
2033 v_sca_rec.course_cd,
2034 'IGS_EN_SUCCESSFULLY_PRE_ENR',
2035 NULL);
2036 successful_total := successful_total + 1;
2037 END IF;
2038 END IF;
2039 END IF; -- p_last_enrolment_cat IS NOT NULL
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042 IF v_log_creation_dt IS NOT NULL THEN
2043 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
2044 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
2045 IF l_message_name <> 'IGS_GE_UNHANDLED_EXP' THEN
2046 IF l_message_name IS NOT NULL THEN
2047 -- If the log creation date is set then log the HECS error
2048 -- This is if the pre-enrolment is being performed in batch.
2049 FND_MESSAGE.SET_NAME(l_app_short_name,l_message_name);
2050 l_mesg_txt := FND_MESSAGE.GET;
2051 igs_ge_gen_003.genp_ins_log_entry(p_s_log_type => cst_pre_enrol,
2052 p_creation_dt => v_log_creation_dt,
2053 p_key => cst_error||','||TO_CHAR(v_sca_rec.person_id)||','||v_sca_rec.course_cd,
2054 p_s_message_name => l_message_name,
2055 p_text => l_mesg_txt);
2056 END IF;
2057 ELSE
2058 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2059 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_008.enrp_ins_btch_prenrl');
2060 l_mesg_txt := fnd_message.get;
2061 igs_ge_gen_003.genp_ins_log_entry(p_s_log_type => cst_pre_enrol,
2062 p_creation_dt => v_log_creation_dt,
2063 p_key => cst_error||','||TO_CHAR(v_sca_rec.person_id)||','||v_sca_rec.course_cd,
2064 p_s_message_name => 'IGS_GE_UNHANDLED_EXP',
2065 p_text => l_mesg_txt);
2066 END IF;
2067 l_message_name := NULL;
2068 ELSE -- v_log_creation_dt is null
2069 RAISE;
2070 END If;
2071
2072 END;
2073
2074 END LOOP; -- v_sca_rec IN c_sca
2075 END IF; -- p_student_comm_type
2076 -- COMMIT; this commit is not needed
2077 RETURN;
2078 EXCEPTION
2079 WHEN OTHERS THEN
2080 IF c_scae%ISOPEN THEN
2081 CLOSE c_scae;
2082 END IF;
2083 IF c_eci%ISOPEN THEN
2084 CLOSE c_eci;
2085 END IF;
2086 IF c_sca%ISOPEN THEN
2087 CLOSE c_sca;
2088 END IF;
2089 IF c_acaiv%ISOPEN THEN
2090 CLOSE c_acaiv;
2091 END IF;
2092 RAISE;
2093 END;
2094 EXCEPTION
2095 WHEN OTHERS THEN
2096 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
2097 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_008.enrp_ins_btch_prenrl');
2098 IGS_GE_MSG_STACK.ADD;
2099 App_Exception.Raise_Exception;
2100 END enrp_ins_btch_prenrl;
2101
2102 FUNCTION enrp_get_person_type(p_course_cd IN VARCHAR2) RETURN VARCHAR2 AS
2103 /******************************************************************
2104 Created By : Vinay Chappidi
2105 Date Created By : 04-Jul-2001
2106 Purpose : This function returns the person type of the user logged into the application
2107 returns TRUE for a Self Service User and FLASE for back office user
2108 Known limitations,
2109 enhancements,
2110 remarks :
2111 Change History
2112 Who When What
2113 kkillams 22-04-22 Cursor cur_hz_parties changed to Cur_person_id modified w.r.t. 2249114
2114 knaraset 07-May-02 Bug 2335276,Modified the logic of getting the person type, to consider RoleType
2115 information also, To make in sync with self service session utilities.
2116 kkillams 11-Apr-03 Modified the cur_fun_name cursor definition as part of the performance bug 2893267 fix.
2117 bdeviset 14-APR-2005 changed cursor cur_person_id for bug # 4303661
2118 ******************************************************************/
2119
2120 -- cursor for getting the person_id of the user who has logged into the application
2121 --Cursor cur_hz_parties is replaced with new cursor cur_person_id w.r.t. 2249114
2122 --cur_hz_parties select statement is SELECT party_id person_id FROM igs_pe_hz_parties
2123 --WHERE oracle_username = cp_user_name
2124 CURSOR cur_person_id( cp_user_name IN fnd_user.user_name%TYPE)
2125 IS
2126 SELECT person_party_id person_id
2127 FROM fnd_user
2128 WHERE user_name = cp_user_name;
2129 -- cursor for getting the person type code of the user who has logged into the application
2130 -- minimum rank for the person id is returned
2131 -- cursor is modified for performance bug 3713057
2132 CURSOR cur_person_type( cp_person_id igs_pe_typ_instances.person_id%TYPE)
2133 IS
2134 SELECT pti.person_type_code
2135 FROM igs_pe_typ_instances pti,
2136 igs_pe_person_types pt
2137 WHERE pti.person_id = cp_person_id AND
2138 pti.person_type_code = pt.person_type_code AND
2139 pt.system_type = 'SS_ENROLL_STAFF' AND
2140 TRUNC(SYSDATE) BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE)
2141 order by rank asc;
2142
2143 -- cursor for getting the person type code of the user who has logged into the application
2144 -- depending on the course code passed into the function
2145 -- cursor is modified for performance bug 3696901
2146 CURSOR cur_typ_instances( cp_person_id igs_pe_hz_parties.party_id%TYPE) IS
2147 SELECT person_type_code
2148 FROM igs_pe_typ_instances_all
2149 WHERE person_id = cp_person_id AND
2150 course_cd = NVL(p_course_cd,course_cd) AND
2151 end_date IS NULL AND
2152 person_type_code IN ( SELECT person_type_code
2153 FROM igs_pe_person_types
2154 WHERE system_type = 'STUDENT');
2155 --
2156 -- Cursor to select the Function name of home page attached to the given responsibility.
2157 --
2158 CURSOR cur_fun_name IS
2159 SELECT fun.function_name
2160 FROM fnd_form_functions fun
2161 WHERE fun.type = 'JSP' AND
2162 fun.function_name IN ('IGS_SS_ADMIN_HOME','IGS_SS_STUDENT_HOME') AND
2163 fun.FUNCTION_ID IN (SELECT menu.FUNCTION_ID
2164 FROM fnd_menu_entries menu
2165 CONNECT BY menu.menu_id = PRIOR menu.SUB_MENU_ID
2166 START WITH menu.menu_id = (SELECT resp.MENU_ID
2167 FROM fnd_responsibility resp
2168 WHERE responsibility_id = fnd_global.RESP_ID
2169 AND resp.application_id = 8405));
2170
2171
2172 l_cur_person_id cur_person_id%ROWTYPE;
2173 l_cur_person_type cur_person_type%ROWTYPE;
2174 l_cur_typ_instances cur_typ_instances%ROWTYPE;
2175 l_fun_name_rec cur_fun_name%ROWTYPE;
2176 -- Variables
2177 l_v_logged_username fnd_user.user_name%TYPE;
2178 BEGIN
2179 -- get the username of the person who has logged into the application
2180 l_v_logged_username := fnd_global.user_name();
2181
2182 -- if the username is not found then return NULL
2183 IF l_v_logged_username IS NULL THEN
2184 RETURN NULL;
2185 ELSE
2186 -- get the person id for the user who has logged into the application by passing the username
2187 OPEN cur_person_id(l_v_logged_username);
2188 FETCH cur_person_id INTO l_cur_person_id;
2189 CLOSE cur_person_id;
2190
2191 -- when person id is not found for the person logged into the application then the function
2192 -- returns NULL expecting that the user looged is a backoffice user
2193 IF l_cur_person_id.person_id IS NULL THEN
2194 RETURN NULL;
2195 ELSE
2196 -- Below coded is modified as per Bug 2335276.
2197 -- when person id is found for the person logged into the application
2198 OPEN cur_fun_name;
2199 FETCH cur_fun_name INTO l_fun_name_rec;
2200 CLOSE cur_fun_name;
2201 IF l_fun_name_rec.function_name = 'IGS_SS_ADMIN_HOME' THEN
2202 -- If logged in user has access to Admin Home Page, means he/she is a Admin.
2203 -- get the person type code of the logged in user based on Rank
2204 OPEN cur_person_type(l_cur_person_id.person_id);
2205 FETCH cur_person_type INTO l_cur_person_type;
2206 CLOSE cur_person_type;
2207 RETURN l_cur_person_type.person_type_code;
2208 ELSIF l_fun_name_rec.function_name = 'IGS_SS_STUDENT_HOME' THEN
2209 -- If logged in user has access to Student Home Page, means he/she is a Student.
2210 -- get the person type code corresponding to the SyatemType STUDENT,
2211 OPEN cur_typ_instances(l_cur_person_id.person_id);
2212 FETCH cur_typ_instances INTO l_cur_typ_instances;
2213 CLOSE cur_typ_instances;
2214 RETURN l_cur_typ_instances.person_type_code;
2215 ELSE
2216 -- If the user got access to Other than Admin/Student Home Pages
2217 OPEN cur_person_type(l_cur_person_id.person_id);
2218 FETCH cur_person_type INTO l_cur_person_type;
2219 CLOSE cur_person_type;
2220 RETURN l_cur_person_type.person_type_code;
2221 END IF; -- fnd_function.test
2222 END IF; -- person_id is NULL
2223 END IF; -- Logged in User Name is NULL
2224 END enrp_get_person_type;
2225
2226 FUNCTION enrp_val_chg_grd_sch ( p_uoo_id IN NUMBER,
2227 p_cal_type IN VARCHAR2,
2228 p_ci_sequence_number IN NUMBER,
2229 p_message_name OUT NOCOPY VARCHAR2
2230 ) RETURN BOOLEAN AS
2231 /******************************************************************
2232 Created By : Vinay Chappidi
2233 Date Created By : 04-Jul-2001
2234 Purpose : This function is used to determine whether the current date is after the Grading Schema
2235 change deadline date or not and also whether more than one grading schema exist or not.
2236 If only one grading schema avilable or date is after the deadline date then the function
2237 return FALSE otherwise returns TRUE
2238 Known limitations,
2239 enhancements,
2240 remarks :
2241 Change History
2242 Who When What
2243 knaraset 21-May-2002 Bug 2357310, modified the logic to return false when only one grading schema defined at unit section
2244 level, so that the item will be displayed as Read-Only in self service page.
2245 kkillams 27-02-2003 Modified cur_ps_unit_ofr cursor, * replaced with unit_cd and version_number w.r.t. bug 2749648
2246 ******************************************************************/
2247
2248
2249 -- cursor for getting the count of Grading Schemas for the Uoo_id passed at the Unit Level
2250 CURSOR cur_usec_grd_schm
2251 IS
2252 SELECT COUNT(1) num_grade_schemas
2253 FROM igs_ps_usec_grd_schm
2254 WHERE uoo_id = p_uoo_id;
2255
2256 -- cursor for getting the Unit_cd and Version Number for the Uoo_id passed
2257 CURSOR cur_ps_unit_ofr
2258 IS
2259 SELECT unit_cd, version_number
2260 FROM igs_ps_unit_ofr_opt
2261 WHERE uoo_id = p_uoo_id;
2262
2263 -- cursor for getting the count of Grading Schema's for the Uoo_id passed at the Unit Section Level
2264 CURSOR cur_unit_grd_schm( cp_unit_code igs_ps_unit_grd_schm.unit_code%TYPE,
2265 cp_version_number igs_ps_unit_grd_schm.unit_version_number%TYPE
2266 )
2267 IS
2268 SELECT COUNT(1) num_grade_schemas
2269 FROM igs_ps_unit_grd_schm
2270 WHERE unit_code = cp_unit_code
2271 AND unit_version_number = cp_version_number;
2272
2273 -- Cursor for getting different grading schema's defined for the logged on person type
2274 -- Modiified cursor for performance bug 3696153
2275 CURSOR cur_pe_usr_arg( cp_person_type IN igs_pe_person_types.person_type_code%TYPE)
2276 IS
2277 SELECT nvl(dai.absolute_val,
2278 IGS_CA_GEN_001.calp_set_alias_value(dai.absolute_val,
2279 IGS_CA_GEN_002.cals_clc_dt_from_dai(dai.ci_sequence_number, dai.CAL_TYPE,
2280 dai.DT_ALIAS, dai.sequence_number) ) ) alias_val
2281 FROM IGS_PE_USR_ARG_ALL pua,
2282 IGS_CA_DA_INST dai
2283 WHERE pua.person_type = cp_person_type
2284 AND dai.dt_alias = pua.grad_sch_dt_alias
2285 AND dai.cal_type = p_cal_type
2286 AND dai.ci_sequence_number = p_ci_sequence_number
2287 ORDER BY 1;
2288
2289
2290
2291 -- Cursor for getting different grading schemas defined at the Unit Level
2292 CURSOR cur_en_nstd_usec
2293 IS
2294 SELECT enr_dl_date alias_val
2295 FROM igs_en_nstd_usec_dl
2296 WHERE function_name = 'GRADING_SCHEMA'
2297 AND uoo_id = p_uoo_id
2298 ORDER BY 1;
2299
2300 -- Cursor for getting different grading schemas defined at the Institutional Level
2301 CURSOR cur_en_cal_conf
2302 IS
2303 SELECT dai.alias_val alias_val
2304 FROM igs_ca_da_inst_v dai, igs_en_cal_conf ecc
2305 WHERE dai.cal_type = p_cal_type
2306 AND dai.ci_sequence_number = p_ci_sequence_number
2307 AND dai.dt_alias = ecc.grading_schema_dt_alias
2308 AND ecc.s_control_num =1
2309 ORDER BY 1;
2310
2311 -- ROWTYPE Variables for Cursors
2312 l_cur_usec_grd_schm cur_usec_grd_schm%ROWTYPE;
2313 l_cur_ps_unit_ofr cur_ps_unit_ofr%ROWTYPE;
2314 l_cur_unit_grd_schm cur_unit_grd_schm%ROWTYPE;
2315 l_cur_pe_usr_arg cur_pe_usr_arg%ROWTYPE;
2316 l_cur_en_nstd_usec cur_en_nstd_usec%ROWTYPE;
2317 l_cur_en_cal_conf cur_en_cal_conf%ROWTYPE;
2318
2319 -- Variables
2320 l_v_person_type igs_pe_person_types.person_type_code%TYPE;
2321 l_b_grade_schema BOOLEAN ;
2322
2323 BEGIN
2324
2325
2326 -- Initializing the local variable to FALSE
2327 -- If more then one grading schemas are defined for the Unit Or at the Unit Section
2328 -- level then this variable is set to TRUE
2329 l_b_grade_schema :=FALSE;
2330
2331 -- Check if more than one grading schemas are defined at Unit Level
2332 -- if there are more than one grading schemas then the variable l_b_grade_schema is set to TRUE
2333 -- else grading schemas defined at Unit section level have to be fetched
2334 OPEN cur_usec_grd_schm;
2335 FETCH cur_usec_grd_schm INTO l_cur_usec_grd_schm;
2336 CLOSE cur_usec_grd_schm;
2337 IF (l_cur_usec_grd_schm.num_grade_schemas > 1) THEN
2338 l_b_grade_schema := TRUE;
2339 ELSIF (l_cur_usec_grd_schm.num_grade_schemas = 0) THEN
2340 -- get the Unit, version_number for the UOO_ID passed into the function
2341 OPEN cur_ps_unit_ofr;
2342 FETCH cur_ps_unit_ofr INTO l_cur_ps_unit_ofr;
2343 CLOSE cur_ps_unit_ofr;
2344
2345 -- If the Unit code and version number are not fetched then the local variable
2346 -- l_b_grade_schema is set to FALSE else set to TRUE
2347 IF (l_cur_ps_unit_ofr.unit_cd IS NOT NULL AND l_cur_ps_unit_ofr.version_number IS NOT NULL) THEN
2348 OPEN cur_unit_grd_schm( l_cur_ps_unit_ofr.unit_cd, l_cur_ps_unit_ofr.version_number);
2349 FETCH cur_unit_grd_schm INTO l_cur_unit_grd_schm;
2350 CLOSE cur_unit_grd_schm;
2351 IF (l_cur_unit_grd_schm.num_grade_schemas > 1) THEN
2352 l_b_grade_schema := TRUE;
2353 END IF;
2354 END IF;
2355 END IF;
2356
2357 -- If the local variable is set to TRUE then start validating the date aliases
2358 -- at person level
2359 IF l_b_grade_schema THEN
2360 -- get the person_type of the logged on user
2361 l_v_person_type := igs_en_gen_008.enrp_get_person_type( p_course_cd => NULL);
2362
2363 -- If there is a person_type defined then validate date aliases at person type level
2364 -- else validate dates at the unit section level
2365 IF l_v_person_type IS NOT NULL THEN
2366
2367 -- check if any date_aliases are defined for the person_type
2368 -- if found then validate them
2369 -- else validate dates at Unit Section Level
2370 OPEN cur_pe_usr_arg(l_v_person_type);
2371 FETCH cur_pe_usr_arg INTO l_cur_pe_usr_arg;
2372 IF cur_pe_usr_arg%FOUND THEN
2373 CLOSE cur_pe_usr_arg;
2374
2375 OPEN cur_pe_usr_arg(l_v_person_type);
2376 LOOP
2377 EXIT WHEN cur_pe_usr_arg%NOTFOUND;
2378 FETCH cur_pe_usr_arg INTO l_cur_pe_usr_arg;
2379 IF ( TRUNC(l_cur_pe_usr_arg.alias_val) < TRUNC(SYSDATE) ) THEN
2380 p_message_name := 'IGS_EN_GRAD_DL_PASS';
2381 RETURN FALSE;
2382 END IF;
2383 END LOOP;
2384 CLOSE cur_pe_usr_arg;
2385 RETURN TRUE;
2386 ELSE
2387 CLOSE cur_pe_usr_arg;
2388 END IF;
2389 END IF;
2390
2391 -- check if any dates are defined for the unit_section level
2392 -- if found then validate them
2393 -- else validate date aliases at institutional Level
2394 OPEN cur_en_nstd_usec;
2395 FETCH cur_en_nstd_usec INTO l_cur_en_nstd_usec;
2396 IF cur_en_nstd_usec%FOUND THEN
2397 CLOSE cur_en_nstd_usec;
2398
2399 OPEN cur_en_nstd_usec;
2400 LOOP
2401 EXIT WHEN cur_en_nstd_usec%NOTFOUND;
2402 FETCH cur_en_nstd_usec INTO l_cur_en_nstd_usec;
2403 IF ( TRUNC(l_cur_en_nstd_usec.alias_val) < TRUNC(SYSDATE) ) THEN
2404 p_message_name := 'IGS_EN_GRAD_DL_PASS';
2405 RETURN FALSE;
2406 END IF;
2407 END LOOP;
2408 CLOSE cur_en_nstd_usec;
2409 RETURN TRUE;
2410 ELSE
2411 CLOSE cur_en_nstd_usec;
2412 END IF;
2413
2414 -- check if any date_aliases are defined at the institutional level
2415 -- if found then validate them
2416 -- else the function should return FALSE by setting p_message_name to 'IGS_EN_GRAD_DL_PASS'
2417 OPEN cur_en_cal_conf;
2418 FETCH cur_en_cal_conf INTO l_cur_en_cal_conf;
2419 IF cur_en_cal_conf%FOUND THEN
2420 CLOSE cur_en_cal_conf;
2421
2422 OPEN cur_en_cal_conf;
2423 LOOP
2424 EXIT WHEN cur_en_cal_conf%NOTFOUND;
2425 FETCH cur_en_cal_conf INTO l_cur_en_cal_conf;
2426 IF ( TRUNC(l_cur_en_cal_conf.alias_val) < TRUNC(SYSDATE) ) THEN
2427 p_message_name := 'IGS_EN_GRAD_DL_PASS';
2428 RETURN FALSE;
2429 END IF;
2430 END LOOP;
2431 CLOSE cur_en_cal_conf;
2432 RETURN TRUE;
2433 ELSE
2434 CLOSE cur_en_cal_conf;
2435 RETURN TRUE;
2436 END IF;
2437 ELSE
2438 p_message_name := 'IGS_EN_GRAD_DL_PASS';
2439 RETURN FALSE;
2440 END IF;
2441 END enrp_val_chg_grd_sch;
2442
2443
2444 FUNCTION enrp_val_chg_grd_sch_wrapper ( p_uoo_id IN NUMBER,
2445 p_cal_type IN VARCHAR2,
2446 p_ci_sequence_number IN NUMBER
2447 ) RETURN CHAR AS
2448 /******************************************************************
2449 Created By : Manu Srinivasan
2450 Date Created By : 04-Oct-2001
2451 Purpose : This is wrapper on the function enrp_val_chg_grd_sch since it has to be used in a view definition and the function has out NOCOPY parameters. This function internall calls this function and has no additional functions
2452 Known limitations,
2453 enhancements,
2454 remarks :
2455 Change History
2456 Who When What
2457 ******************************************************************/
2458 l_dummy VARCHAR2(100);
2459 BEGIN
2460
2461 IF( enrp_val_chg_grd_sch ( p_uoo_id,
2462 p_cal_type,
2463 p_ci_sequence_number,
2464 l_dummy)) THEN
2465 RETURN 'Y';
2466
2467 ELSE RETURN 'N';
2468 END IF;
2469
2470 END enrp_val_chg_grd_sch_wrapper;
2471
2472 FUNCTION enrp_val_chg_cp (
2473 p_person_id IN NUMBER,
2474 p_uoo_id IN NUMBER,
2475 p_cal_type IN VARCHAR2,
2476 p_ci_sequence_number IN NUMBER
2477 ) RETURN CHAR AS
2478 /******************************************************************
2479 Created By : Manu Srinivasan
2480 Date Created By : 04-Oct-2001
2481 Purpose : This func determines if credit points can be updated
2482 Known limitations,
2483 enhancements,
2484 remarks :
2485 Change History
2486 Who When What
2487 kkillams 27-02-2003 Modified cur_ps_unit_ofr cursor, * replaced with unit_cd and version_number w.r.t. bug 2749648
2488 myoganat 23-05-2003 Created cursor cur_no_assesment_ind to check for
2489 audit attempts #2855870
2490 ******************************************************************/
2491 l_dummy VARCHAR2(100);
2492 -- cursor for getting the Unit_cd and Version Number for the Uoo_id passed
2493 CURSOR cur_ps_unit_ofr
2494 IS
2495 SELECT unit_cd, version_number
2496 FROM igs_ps_unit_ofr_opt
2497 WHERE uoo_id = p_uoo_id;
2498
2499 -- Check if the unit is set up for variable cp
2500 CURSOR cur_chk_cp_chg_val (p_unit_cd igs_ps_unit_ver_v.unit_cd%TYPE,p_unit_ver_num igs_ps_unit_ver_v.version_number%TYPE)
2501 IS SELECT points_override_ind
2502 FROM igs_ps_unit_ver_v
2503 WHERE unit_cd = p_unit_cd
2504 AND version_number = p_unit_ver_num;
2505
2506 --Check if there exist any user level deadline
2507 --Modified the cursor for performance bug 3696257
2508 CURSOR cur_pe_usr_arg( cp_person_type IN igs_pe_person_types.person_type_code%TYPE)
2509 IS
2510 SELECT nvl(dai.absolute_val,
2511 IGS_CA_GEN_001.calp_set_alias_value(dai.absolute_val,
2512 IGS_CA_GEN_002.cals_clc_dt_from_dai(dai.ci_sequence_number, dai.CAL_TYPE,
2513 dai.DT_ALIAS, dai.sequence_number) ) ) alias_val
2514 FROM igs_ca_da_inst dai,igs_pe_usr_arg_all pua
2515 WHERE pua.person_type = cp_person_type
2516 AND dai.dt_alias = pua.grad_sch_dt_alias
2517 AND dai.cal_type = p_cal_type
2518 AND dai.ci_sequence_number = p_ci_sequence_number
2519 ORDER BY 1;
2520
2521
2522 --Check if deadline has passed for cp change at usec level
2523 CURSOR cur_en_nstd_usec
2524 IS
2525 SELECT enr_dl_date alias_val
2526 FROM igs_en_nstd_usec_dl
2527 WHERE function_name = 'GRADING_SCHEMA'
2528 AND uoo_id = p_uoo_id
2529 ORDER BY 1;
2530
2531 --Check if deadline has passed for cp change at institution level
2532 CURSOR cur_en_cal_conf
2533 IS
2534 SELECT dai.alias_val alias_val
2535 FROM igs_ca_da_inst_v dai, igs_en_cal_conf ecc
2536 WHERE dai.cal_type = p_cal_type
2537 AND dai.ci_sequence_number = p_ci_sequence_number
2538 AND dai.dt_alias = ecc.grading_schema_dt_alias
2539 AND ecc.s_control_num =1
2540 ORDER BY 1;
2541
2542 -- Cursor to get the System Type corresponding to the Person Type Code
2543 -- Added as per the bug# 2364461.
2544 CURSOR cur_sys_per_typ(cp_person_type VARCHAR2) IS
2545 SELECT system_type
2546 FROM igs_pe_person_types
2547 WHERE person_type_code = cp_person_type;
2548 l_cur_sys_per_typ cur_sys_per_typ%ROWTYPE;
2549
2550 -- Cursor to check for audit attempts
2551 -- By selecting no_assessment_ind column corresponding
2552 -- to the Person Id, Unit Offering Options Id, Calendar Type
2553 -- and Calendar Instance
2554 CURSOR cur_no_assessment_ind
2555 IS
2556 SELECT no_assessment_ind
2557 FROM igs_en_su_attempt
2558 WHERE person_id = p_person_id
2559 AND uoo_id = p_uoo_id
2560 AND cal_type = p_cal_type
2561 AND ci_sequence_number = p_ci_sequence_number;
2562 l_no_assessment_ind VARCHAR2(1);
2563
2564 --Row type variables
2565 l_cur_pe_usr_arg cur_pe_usr_arg%ROWTYPE;
2566 l_cur_en_nstd_usec cur_en_nstd_usec%ROWTYPE;
2567 l_cur_en_cal_conf cur_en_cal_conf%ROWTYPE;
2568 l_cur_chk_cp_chg_val cur_chk_cp_chg_val%ROWTYPE;
2569 l_cur_ps_unit_ofr cur_ps_unit_ofr%ROWTYPE;
2570
2571 -- Variables
2572 l_v_person_type igs_pe_person_types.person_type_code%TYPE;
2573 l_cp_out NUMBER;
2574
2575 BEGIN
2576
2577 -- Check for audit attempt
2578 OPEN cur_no_assessment_ind;
2579 FETCH cur_no_assessment_ind INTO l_no_assessment_ind;
2580 CLOSE cur_no_assessment_ind;
2581 -- Incase of an audit attempt the enrolled CP should not be
2582 -- updateable by the student, hence return 'N'
2583 IF l_no_assessment_ind = 'Y' THEN
2584 RETURN 'N';
2585 END IF;
2586
2587 --Get the person logged in frmo session
2588 l_v_person_type := igs_en_gen_008.enrp_get_person_type(p_course_cd=>NULL);
2589
2590 -- According to ENCR012, check that approved cp are not defined for this student
2591 -- Added as per the bug# 2364461.
2592 -- Start of new code.
2593 OPEN cur_sys_per_typ(l_v_person_type);
2594 FETCH cur_sys_per_typ INTO l_cur_sys_per_typ;
2595 CLOSE cur_sys_per_typ;
2596 -- End of new code.
2597 -- For Bug 2398133,removed the assignment
2598 -- l_v_person_type := l_cur_sys_per_typ.system_type
2599 -- as it was overwritting the person type of the logged in user.
2600 --
2601 IF l_cur_sys_per_typ.system_type = 'STUDENT' THEN
2602 IF fnd_profile.value('IGS_EN_UPDATE_CP_GS')='Y' THEN
2603 RETURN 'N';
2604 END IF;
2605
2606 IF igs_en_gen_015.validation_step_is_overridden(
2607 p_eligibility_step_type => 'VAR_CREDIT_APPROVAL',
2608 p_load_cal_type => p_cal_type,
2609 p_load_cal_seq_number => p_ci_sequence_number,
2610 p_person_id => p_person_id,
2611 p_uoo_id => p_uoo_id,
2612 p_step_override_limit => l_cp_out) THEN
2613 RETURN 'N';
2614 END IF;
2615 END IF;
2616
2617
2618 -- Get the Unit, version_number for the UOO_ID passed into the function
2619 OPEN cur_ps_unit_ofr;
2620 FETCH cur_ps_unit_ofr INTO l_cur_ps_unit_ofr;
2621 CLOSE cur_ps_unit_ofr;
2622
2623 -- check that the unit is set up as allowing points override in PSP
2624 OPEN cur_chk_cp_chg_val(l_cur_ps_unit_ofr.unit_cd,l_cur_ps_unit_ofr.version_number);
2625 FETCH cur_chk_cp_chg_val INTO l_cur_chk_cp_chg_val;
2626 CLOSE cur_chk_cp_chg_val;
2627
2628 IF l_cur_chk_cp_chg_val.points_override_ind = 'Y' THEN
2629 --This above condition means that override cp is allowed
2630 --So check for deadlines at user,unit section level and institution level
2631
2632
2633
2634 --If person type exists, check that any user level deadlines are not passed
2635 IF l_v_person_type IS NOT NULL THEN
2636 -- check if any date_aliases are defined for the person_type
2637 -- if found then validate them
2638 -- else validate dates at Unit Section Level
2639 OPEN cur_pe_usr_arg(l_v_person_type);
2640 FETCH cur_pe_usr_arg INTO l_cur_pe_usr_arg;
2641 IF cur_pe_usr_arg%FOUND THEN
2642 CLOSE cur_pe_usr_arg;
2643 OPEN cur_pe_usr_arg(l_v_person_type);
2644 LOOP
2645 EXIT WHEN cur_pe_usr_arg%NOTFOUND;
2646 FETCH cur_pe_usr_arg INTO l_cur_pe_usr_arg;
2647 IF ( TRUNC(l_cur_pe_usr_arg.alias_val) < TRUNC(SYSDATE) ) THEN
2648 RETURN 'N';
2649 END IF;
2650 END LOOP;
2651 CLOSE cur_pe_usr_arg;
2652 RETURN 'Y';
2653 ELSE
2654 CLOSE cur_pe_usr_arg;
2655 END IF;
2656 END IF;
2657
2658 --Check if unit section level deadline has not passed
2659 OPEN cur_en_nstd_usec;
2660 FETCH cur_en_nstd_usec INTO l_cur_en_nstd_usec;
2661 IF cur_en_nstd_usec%FOUND THEN
2662 CLOSE cur_en_nstd_usec;
2663 OPEN cur_en_nstd_usec;
2664 LOOP
2665 EXIT WHEN cur_en_nstd_usec%NOTFOUND;
2666 FETCH cur_en_nstd_usec INTO l_cur_en_nstd_usec;
2667 IF ( TRUNC(l_cur_en_nstd_usec.alias_val) < TRUNC(SYSDATE) ) THEN
2668 RETURN 'N';
2669 END IF;
2670 END LOOP;
2671 CLOSE cur_en_nstd_usec;
2672 RETURN 'Y';
2673 ELSE
2674 CLOSE cur_en_nstd_usec;
2675 END IF;
2676
2677 --Check if institution level deadline has not passed
2678 OPEN cur_en_cal_conf;
2679 FETCH cur_en_cal_conf INTO l_cur_en_cal_conf;
2680 IF cur_en_cal_conf%FOUND THEN
2681 CLOSE cur_en_cal_conf;
2682 OPEN cur_en_cal_conf;
2683 LOOP
2684 EXIT WHEN cur_en_cal_conf%NOTFOUND;
2685 FETCH cur_en_cal_conf INTO l_cur_en_cal_conf;
2686 IF ( TRUNC(l_cur_en_cal_conf.alias_val) < TRUNC(SYSDATE) ) THEN
2687 RETURN 'N';
2688 END IF;
2689 END LOOP;
2690 CLOSE cur_en_cal_conf;
2691 RETURN 'Y';
2692 ELSE
2693 CLOSE cur_en_cal_conf;
2694 RETURN 'Y';
2695 END IF;
2696
2697 ELSE
2698 RETURN 'N';
2699 END IF;
2700
2701 END enrp_val_chg_cp;
2702
2703 FUNCTION enrp_get_dflt_sdrt(
2704 p_s_discont_reason_type IN VARCHAR2 )
2705 RETURN VARCHAR2 IS
2706
2707 /******************************************************************
2708 Created By : Prajeesh Chandran
2709 Date Created By : 14-May-2002
2710 Purpose :This function Returns the Default discontinuation reason code if exists
2711 Known limitations,
2712 enhancements,
2713 remarks :
2714 Change History
2715 Who When What
2716 ******************************************************************/
2717 -- enrp_get_dflt_sdt
2718 -- Get the default discontinuation_reason_cd for a nominated
2719 -- s_discontinuation_reason_type, based on the sys_dflt_ind field.
2720 v_discontinuation_reason_cd IGS_EN_DCNT_REASONCD.discontinuation_reason_cd%TYPE;
2721 CURSOR c_dr IS
2722 SELECT discontinuation_reason_cd
2723 FROM IGS_EN_DCNT_REASONCD dr
2724 WHERE dr.s_discontinuation_reason_type = p_s_discont_reason_type AND
2725 dr.sys_dflt_ind = 'Y' AND
2726 dr.closed_ind = 'N';
2727
2728 BEGIN
2729 OPEN c_dr;
2730 FETCH c_dr INTO v_discontinuation_reason_cd;
2731 IF c_dr%NOTFOUND THEN
2732 CLOSE c_dr;
2733 RETURN NULL;
2734 END IF;
2735 CLOSE c_dr;
2736 RETURN v_discontinuation_reason_cd;
2737 EXCEPTION
2738 WHEN OTHERS THEN
2739 IF c_dr%ISOPEN THEN
2740 CLOSE c_dr;
2741 END IF;
2742 RETURN NULL;
2743
2744 END enrp_get_dflt_sdrt;
2745
2746
2747 FUNCTION enrp_get_pr_outcome(
2748 p_person_id IN NUMBER,
2749 p_course_cd IN VARCHAR2
2750 ) RETURN VARCHAR2 AS
2751
2752
2753 -- get the currently active unit set for the person course attempt
2754 CURSOR c_active_us IS
2755 SELECT susa.selection_dt
2756 FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
2757 WHERE susa.person_id = p_person_id AND
2758 susa.course_cd = p_course_cd AND
2759 susa.selection_dt IS NOT NULL AND
2760 susa.end_dt IS NULL AND
2761 susa.rqrmnts_complete_dt IS NULL AND
2762 susa.unit_set_cd = us.unit_set_cd AND
2763 us.unit_set_cat = usc.unit_set_cat AND
2764 usc.s_unit_set_cat = 'PRENRL_YR' ;
2765
2766
2767 -- find the last active unit set for the person program
2768 CURSOR c_last_us IS
2769 SELECT susa.selection_dt
2770 FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
2771 WHERE susa.person_id = p_person_id AND
2772 susa.course_cd = p_course_cd AND
2773 susa.rqrmnts_complete_dt IS NOT NULL AND
2774 susa.unit_set_cd = us.unit_set_cd AND
2775 us.unit_set_cat = usc.unit_set_cat AND
2776 usc.s_unit_set_cat = 'PRENRL_YR'
2777 ORDER BY susa.rqrmnts_complete_dt desc ;
2778
2779 -- checks the eligibility of the student to be moved to the next year of program (unit set)
2780 -- by checking if there is any outcome preventing the progress of the student program attempt
2781 CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
2782 SELECT pou.decision_dt, pout.s_progression_outcome_type
2783 FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
2784 WHERE pou.person_id = p_person_id AND
2785 pou.course_cd = p_course_cd AND
2786 pou.decision_status = 'APPROVED' AND
2787 pou.decision_dt IS NOT NULL AND
2788 pou.decision_dt > cp_select_dt AND
2789 pou.progression_outcome_type = pout.progression_outcome_type
2790 ORDER BY pou.decision_dt desc ;
2791 c_prog_outcome_rec c_prog_outcome%ROWTYPE;
2792
2793 v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
2794 cst_advance CONSTANT VARCHAR2(30) := 'ADVANCE';
2795 cst_repeatyr CONSTANT VARCHAR2(30) := 'REPEATYR';
2796
2797 BEGIN
2798
2799 v_selection_dt := NULL;
2800 -- If there is a currently active year of program then make it completed
2801 --and pre-enrol in the next year of program , if it exists
2802 OPEN c_active_us ;
2803 FETCH c_active_us INTO v_selection_dt;
2804 IF c_active_us%FOUND THEN
2805 CLOSE c_active_us ;
2806 NULL;
2807 ELSE -- c_active_us
2808 CLOSE c_active_us ;
2809 OPEN c_last_us ;
2810 FETCH c_last_us INTO v_selection_dt ;
2811 IF c_last_us%NOTFOUND THEN
2812 CLOSE c_last_us ;
2813 RETURN 'NEW';
2814 END IF;
2815 CLOSE c_last_us ;
2816 END IF; -- c_active_us%FOUND
2817
2818 -- check if there is any progression outcome preventing this student
2819 -- from completing this unit set attempt and going into the next year of program
2820 OPEN c_prog_outcome(v_selection_dt) ;
2821 FETCH c_prog_outcome INTO c_prog_outcome_rec ;
2822
2823 IF c_prog_outcome%NOTFOUND THEN
2824 CLOSE c_prog_outcome;
2825 RETURN cst_advance;
2826 ELSIF c_prog_outcome_rec.s_progression_outcome_type = cst_advance OR
2827 c_prog_outcome_rec.s_progression_outcome_type = cst_repeatyr THEN
2828 CLOSE c_prog_outcome;
2829 RETURN c_prog_outcome_rec.s_progression_outcome_type;
2830 ELSE
2831 CLOSE c_prog_outcome;
2832 RETURN NULL;
2833 END IF;
2834
2835 IF c_prog_outcome%ISOPEN THEN
2836 CLOSE c_prog_outcome;
2837 END IF;
2838
2839 RETURN cst_advance;
2840
2841 END enrp_get_pr_outcome;
2842
2843 END igs_en_gen_008;
[Home] [Help]