[Home] [Help]
PACKAGE BODY: APPS.IGS_SS_EN_WRAPPERS
Source
1 PACKAGE BODY igs_ss_en_wrappers AS
2 /* $Header: IGSSS09B.pls 120.31 2006/08/24 07:33:57 bdeviset noship $ */
3 --package variables
4 pkg_coreq_failed_uooids VARCHAR2(2000);
5 pkg_prereq_failed_uooids VARCHAR2(2000);
6 PROCEDURE validate_prog_pro(
7 p_person_id igs_en_su_attempt.person_id%TYPE,
8 p_cal_type igs_ca_inst.cal_type%TYPE,
9 p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
10 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
11 p_course_cd igs_en_su_attempt.course_cd%TYPE,
12 p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
13 p_message_name OUT NOCOPY VARCHAR2,
14 p_deny_warn OUT NOCOPY VARCHAR2,
15 p_return_status OUT NOCOPY VARCHAR2
16 ) AS
17
18 l_message_name VARCHAR2(2000);
19 l_deny_warn VARCHAR2(2000);
20 BEGIN NULL;
21 IF igs_en_enroll_wlst.validate_prog
22 (
23 p_person_id => p_person_id,
24 p_cal_type => p_cal_type,
25 p_ci_sequence_number => p_ci_sequence_number,
26 p_uoo_id => p_uoo_id,
27 p_course_cd => p_course_cd,
28 p_enr_method_type => p_enr_method_type,
29 p_message_name => l_message_name,
30 p_deny_warn => l_deny_warn
31 ) THEN
32 p_return_status := 'TRUE';
33 ELSE
34 p_return_status := 'FALSE';
35 END IF;
36 p_deny_warn := l_deny_warn;
37 p_message_name := l_message_name;
38 END validate_prog_pro;
39
40 -----------------------------------------------------------------------------------
41 --Created by : prgoyal ( Oracle IDC)
42 --Date created: 08-OCT-2001
43 --
44 --Purpose: This is a wrapper procedure to evaluate the person steps.
45 -- It is will be called fom SS screens. the enrollment category, method and
46 -- commencement type would be calcualted and the function to evaluate
47 -- person step would be called.
48 --
49 --Known limitations/enhancements and/or remarks:
50 --
51 --Change History:
52 --Who When What
53 ------------------------------------------------------------------------------------
54 PROCEDURE enrp_ss_val_person_step
55 (
56 p_person_id IN NUMBER,
57 p_person_type IN VARCHAR2,
58 p_load_cal_type IN VARCHAR2,
59 p_load_ci_sequence_number IN NUMBER,
60 p_program_cd IN VARCHAR2,
61 p_program_version IN NUMBER,
62 p_message_name OUT NOCOPY VARCHAR2,
63 p_deny_warn OUT NOCOPY VARCHAR2,
64 p_step_eval_result OUT NOCOPY VARCHAR2,
65 p_calling_obj IN VARCHAR2,
66 p_create_warning IN VARCHAR2,
67 p_ss_session_id IN NUMBER
68 )
69 AS
70 /* HISTORY
71 WHO WHEN WHAT
72 ayedubat 07-JUN-2002 The function call,Igs_En_Gen_015.get_academic_cal is replaced with
73 Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd to get the academic calendar of the
74 given load calendar rather than current academic calendar for the bug fix:2381603
75 */
76
77 l_commencement_type VARCHAR2(10);
78 l_enr_method IGS_EN_METHOD_TYPE.enr_method_type%TYPE;
79 l_enrollment_category igs_en_cat_prc_dtl.enrolment_cat%TYPE;
80 l_step_eval_result BOOLEAN;
81 l_message VARCHAR2(2000);
82 l_deny_warn VARCHAR2(10);
83 l_enrol_cal_type igs_ca_type.cal_type%TYPE;
84 l_enrol_sequence_number igs_ca_inst_all.sequence_number%TYPE;
85 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
86 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
87 lv_message fnd_new_messages.message_name%TYPE;
88 l_acad_start_dt IGS_CA_INST.start_dt%TYPE;
89 l_acad_end_dt IGS_CA_INST.end_dt%TYPE;
90 l_alternate_code IGS_CA_INST.alternate_code%TYPE;
91 l_return_status VARCHAR2(10);
92 l_dummy VARCHAR2(200);
93
94
95 BEGIN
96
97 igs_en_add_units_api.g_ss_session_id := p_ss_session_id;
98 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
99 igs_en_gen_017.enrp_get_enr_method(
100 p_enr_method_type => l_enr_method,
101 p_error_message => l_message,
102 p_ret_status => l_return_status);
103
104 IF l_return_status='FALSE' THEN
105 p_message_name := 'IGS_SS_EN_NOENR_METHOD' ;
106 p_step_eval_result := 'FALSE';
107 p_deny_warn := 'DENY';
108 ELSE
109
110 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
111 p_cal_type => p_load_cal_type,
112 p_ci_sequence_number => p_load_ci_sequence_number,
113 p_acad_cal_type => l_acad_cal_type,
114 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
115 p_acad_ci_start_dt => l_acad_start_dt,
116 p_acad_ci_end_dt => l_acad_end_dt,
117 p_message_name => lv_message );
118
119 IF lv_message IS NOT NULL THEN
120 p_message_name := lv_message;
121 p_deny_warn := 'DENY';
122 p_step_eval_result := 'FALSE';
123 igs_en_add_units_api.g_ss_session_id := NULL;
124 RETURN;
125 END IF;
126
127 /* get the enrollment category and commencement type */
128 l_enrollment_category := igs_en_gen_003.enrp_get_enr_cat
129 ( p_person_id => p_person_id,
130 p_course_cd => p_program_cd,
131 p_cal_type => l_acad_cal_type,
132 p_ci_sequence_number => l_acad_ci_sequence_number,
133 p_session_enrolment_cat =>NULL,
134 p_enrol_cal_type => l_enrol_cal_type ,
135 p_enrol_ci_sequence_number => l_enrol_sequence_number,
136 p_commencement_type => l_commencement_type,
137 p_enr_categories => l_dummy );
138
139 IF l_commencement_type = 'BOTH' THEN
140 /* if both is returned we have to treat it as all */
141 l_commencement_type := 'ALL';
142 END IF;
143 IF igs_en_elgbl_person.eval_person_steps
144 (
145 p_person_id => p_person_id ,
146 p_person_type => p_person_type,
147 p_load_calendar_type => p_load_cal_type ,
148 p_load_cal_sequence_number => p_load_ci_sequence_number,
149 p_program_cd => p_program_cd,
150 p_program_version => p_program_version,
151 p_enrollment_category => l_enrollment_category,
152 p_comm_type => l_commencement_type,
153 p_enrl_method => l_enr_method,
154 p_message => l_message,
155 p_deny_warn => l_deny_warn,
156 p_calling_obj => p_calling_obj,
157 p_create_warning => p_create_warning) THEN
158 /* the step evaluation has succeded*/
159 p_message_name := l_message;
160 p_deny_warn := l_deny_warn;
161 p_step_eval_result := 'TRUE';
162 ELSE
163 /* the step evaluation has failed */
164 p_message_name := l_message;
165 p_deny_warn := l_deny_warn;
166 p_step_eval_result := 'FALSE';
167 END IF;
168 END IF;
169
170 igs_en_add_units_api.g_ss_session_id := NULL;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 igs_en_add_units_api.g_ss_session_id := NULL;
175 RAISE;
176
177 END enrp_ss_val_person_step;
178
179
180 PROCEDURE validate_unit_steps(
181 p_person_id IN igs_en_su_attempt.person_id%TYPE,
182 p_cal_type IN igs_ca_inst.cal_type%TYPE,
183 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
184 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
185 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
186 p_return_status OUT NOCOPY VARCHAR2,
187 p_message_name OUT NOCOPY VARCHAR2,
188 p_deny_warn OUT NOCOPY VARCHAR2
189 ) AS
190 ------------------------------------------------------------------------------------
191 --Created by : brajendr ( Oracle IDC)
192 --Date created: 08-OCT-2001
193 --
194 --Purpose:
195 --
196 --Known limitations/enhancements and/or remarks:
197 --
198 --Change History:
199 --Who When What
200 -------------------------------------------------------------------------------------
201 l_enr_method IGS_EN_METHOD_TYPE.enr_method_type%TYPE;
202 l_message VARCHAR2(2000);
203 l_return_status VARCHAR2(10);
204
205 BEGIN
206 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
207 igs_en_gen_017.enrp_get_enr_method(
208 p_enr_method_type => l_enr_method,
209 p_error_message => l_message,
210 p_ret_status => l_return_status);
211 IF igs_en_enroll_wlst.validate_unit_steps ( p_person_id,
212 p_cal_type,
213 p_ci_sequence_number,
214 p_uoo_id,
215 p_course_cd,
216 l_enr_method,
217 p_message_name,
218 p_deny_warn,
219 'JOB') THEN
220
221
222 p_return_status := 'TRUE';
223 ELSE
224 p_return_status := 'FALSE';
225 END IF;
226 END validate_unit_steps;
227
228 PROCEDURE get_person_type_by_rank(
229 p_person_id IN NUMBER,
230 p_person_type OUT NOCOPY VARCHAR2
231 ) AS
232 ------------------------------------------------------------------------------------
233 --Created by : brajendr ( Oracle IDC)
234 --Date created: 30-OCT-2001
235 --
236 --Purpose:
237 --
238 --Known limitations/enhancements and/or remarks:
239 --
240 --Change History:
241 --Who When What
242 -- kamohan 01-NOV-2001 Modified to get the person_type for the admin person
243 -- type only
244 -------------------------------------------------------------------------------------
245 CURSOR cur_get_person_type_by_rank( p_person_id NUMBER) IS
246 SELECT person_type_code
247 FROM igs_pe_person_types
248 WHERE rank = ( SELECT MIN(rank)
249 FROM igs_pe_person_types
250 WHERE person_type_code IN ( SELECT person_type_code
251 FROM igs_pe_typ_instances_all
252 WHERE person_id = p_person_id
253 )
254 AND system_type = 'SS_ENROLL_STAFF'
255 AND closed_ind = 'N'
256 );
257 BEGIN
258 p_person_type := NULL;
259 OPEN cur_get_person_type_by_rank(p_person_id);
260 FETCH cur_get_person_type_by_rank INTO p_person_type;
261 CLOSE cur_get_person_type_by_rank;
262 EXCEPTION
263 WHEN OTHERS THEN
264 p_person_type := NULL;
265 END get_person_type_by_rank;
266
267 PROCEDURE call_fee_ass (
268 p_person_id IN NUMBER,
269 p_cal_type IN VARCHAR2,
270 p_sequence_number IN NUMBER,
271 p_course_cd IN VARCHAR2,
272 p_unit_cd IN VARCHAR2,
273 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
274 ) IS
275
276 l_row_id VARCHAR2(25);
277 l_return_val NUMBER;
278 CURSOR c1 IS
279 SELECT
280 igs_pe_std_todo_ref_rf_num_s.NEXTVAL
281 FROM
282 dual;
283 l_reference_number NUMBER;
284 CURSOR c2 ( c_return_val NUMBER) IS
285 SELECT
286 person_id
287 FROM
288 igs_pe_std_todo_ref
289 WHERE
290 person_id = p_person_id AND
291 s_student_todo_type = 'FEE_RECALC' AND
292 sequence_number = c_return_val AND
293 cal_type = p_cal_type AND
294 ci_sequence_number = p_sequence_number AND
295 logical_delete_dt IS NULL; -- This condition is newly added by Nishikant - 20JUN2002 - bug#2420442.
296 c2_rec NUMBER;
297 BEGIN
298 l_return_val := IGS_GE_GEN_003.genp_ins_stdnt_todo( p_person_id, 'FEE_RECALC', SYSDATE, 'Y');
299 OPEN c2( l_return_val);
300 FETCH c2 INTO c2_rec;
301 IF c2%NOTFOUND THEN
302 OPEN c1;
303 FETCH c1 INTO l_reference_number;
304 igs_pe_std_todo_ref_pkg.insert_row (
305 X_ROWID => l_row_id,
306 X_PERSON_ID => p_person_id,
307 X_S_STUDENT_TODO_TYPE => 'FEE_RECALC',
308 X_SEQUENCE_NUMBER => l_return_val,
309 X_REFERENCE_NUMBER => l_reference_number,
310 X_CAL_TYPE => p_cal_type,
311 X_CI_SEQUENCE_NUMBER => p_sequence_number,
312 X_COURSE_CD => p_course_cd,
313 X_UNIT_CD => p_unit_cd,
314 X_OTHER_REFERENCE => NULL,
315 X_LOGICAL_DELETE_DT => NULL,
316 X_MODE => 'R',
317 X_UOO_ID => p_uoo_id
318 );
319 END IF;
320 CLOSE c2;
321 END call_fee_ass;
322
323
324 PROCEDURE enroll_cart_unit(
325 p_person_id IN NUMBER,
326 p_uoo_id IN NUMBER,
327 p_unit_cd IN VARCHAR2,
328 p_version_number IN NUMBER,
329 p_course_cd IN VARCHAR2,
330 p_unit_attempt_status IN VARCHAR2,
331 p_enrolled_dt IN DATE
332 ) AS
333 ------------------------------------------------------------------------------------
334 --Created by : kamohan ( Oracle IDC)
335 --Date created: 10-NOV-2001
336 --
337 --Purpose:
338 --
339 --Known limitations/enhancements and/or remarks:
340 --
341 --Change History:
342 --Who When What
343 --stutta 11-FEB-2004 Added new parameter p_enrolled_dt. Replace the code to
344 -- populate enrolled date with SYSDATE always to consider the
345 -- value passed in.
346 --mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in TBH call of IGS_EN_SU_ATTEMPT
347 -- for Bug 2554109 MINI Waitlist Build for Jan 03 Release
348 -- kamohan 10-NOV-2001 Added this procedure for the Enrollment Worksheet
349 --
350 -------------------------------------------------------------------------------------
351 CURSOR cart_cur IS
352 SELECT sua.*
353 FROM
354 igs_en_su_attempt sua
355 WHERE
356 person_id = p_person_id AND
357 course_cd = p_course_cd AND
358 unit_cd = p_unit_cd AND
359 version_number = p_version_number AND
360 uoo_id = p_uoo_id;
361 cart_rec cart_cur%ROWTYPE;
362 l_enrolled_dt DATE;
363 BEGIN
364 OPEN cart_cur;
365 FETCH cart_cur INTO cart_rec;
366 CLOSE cart_cur;
367 IF p_unit_attempt_status = 'ENROLLED' THEN
368 l_enrolled_dt := NVL(p_enrolled_dt,SYSDATE);
369 ELSE
370 l_enrolled_dt := cart_rec.enrolled_dt;
371 END IF;
372 igs_en_sua_api.update_unit_attempt(
373 X_ROWID => cart_rec.ROW_ID,
374 X_PERSON_ID => cart_rec.PERSON_ID,
375 X_COURSE_CD => cart_rec.COURSE_CD,
376 X_UNIT_CD => cart_rec.UNIT_CD,
377 X_CAL_TYPE => cart_rec.CAL_TYPE,
378 X_CI_SEQUENCE_NUMBER => cart_rec.CI_SEQUENCE_NUMBER,
379 X_VERSION_NUMBER => cart_rec.VERSION_NUMBER,
380 X_LOCATION_CD => cart_rec.LOCATION_CD,
381 X_UNIT_CLASS => cart_rec.UNIT_CLASS,
382 X_CI_START_DT => cart_rec.CI_START_DT,
383 X_CI_END_DT => cart_rec.CI_END_DT,
384 X_UOO_ID => cart_rec.UOO_ID,
385 X_ENROLLED_DT => l_enrolled_dt,
386 X_UNIT_ATTEMPT_STATUS => p_unit_attempt_status,
387 X_ADMINISTRATIVE_UNIT_STATUS => cart_rec.ADMINISTRATIVE_UNIT_STATUS,
388 X_DISCONTINUED_DT => cart_rec.DISCONTINUED_DT,
389 X_RULE_WAIVED_DT => cart_rec.RULE_WAIVED_DT,
390 X_RULE_WAIVED_PERSON_ID => cart_rec.RULE_WAIVED_PERSON_ID,
391 X_NO_ASSESSMENT_IND => cart_rec.NO_ASSESSMENT_IND,
392 X_SUP_UNIT_CD => cart_rec.SUP_UNIT_CD,
393 X_SUP_VERSION_NUMBER => cart_rec.SUP_VERSION_NUMBER,
394 X_EXAM_LOCATION_CD => cart_rec.EXAM_LOCATION_CD,
395 X_ALTERNATIVE_TITLE => cart_rec.ALTERNATIVE_TITLE,
396 X_OVERRIDE_ENROLLED_CP => cart_rec.OVERRIDE_ENROLLED_CP,
397 X_OVERRIDE_EFTSU => cart_rec.OVERRIDE_EFTSU,
398 X_OVERRIDE_ACHIEVABLE_CP => cart_rec.OVERRIDE_ACHIEVABLE_CP,
399 X_OVERRIDE_OUTCOME_DUE_DT => cart_rec.OVERRIDE_OUTCOME_DUE_DT,
400 X_OVERRIDE_CREDIT_REASON => cart_rec.OVERRIDE_CREDIT_REASON,
401 X_ADMINISTRATIVE_PRIORITY => cart_rec.ADMINISTRATIVE_PRIORITY,
402 X_WAITLIST_DT => cart_rec.WAITLIST_DT,
403 X_DCNT_REASON_CD => cart_rec.DCNT_REASON_CD,
404 X_MODE => 'R',
405 X_GS_VERSION_NUMBER => cart_rec.GS_VERSION_NUMBER,
406 X_ENR_METHOD_TYPE => cart_rec.ENR_METHOD_TYPE,
407 X_FAILED_UNIT_RULE => cart_rec.FAILED_UNIT_RULE,
408 X_CART => 'N',
409 X_RSV_SEAT_EXT_ID => cart_rec.RSV_SEAT_EXT_ID,
410 X_ORG_UNIT_CD => cart_rec.ORG_UNIT_CD,
411 X_GRADING_SCHEMA_CODE => cart_rec.GRADING_SCHEMA_CODE,
412 X_SUBTITLE => cart_rec.SUBTITLE,
413 X_SESSION_ID => cart_rec.SESSION_ID,
414 X_DEG_AUD_DETAIL_ID => cart_rec.DEG_AUD_DETAIL_ID,
415 X_STUDENT_CAREER_TRANSCRIPT => cart_rec.STUDENT_CAREER_TRANSCRIPT,
416 X_STUDENT_CAREER_STATISTICS => cart_rec.STUDENT_CAREER_STATISTICS,
417 X_ATTRIBUTE_CATEGORY => cart_rec.ATTRIBUTE_CATEGORY,
418 X_ATTRIBUTE1 => cart_rec.ATTRIBUTE1,
419 X_ATTRIBUTE2 => cart_rec.ATTRIBUTE2,
420 X_ATTRIBUTE3 => cart_rec.ATTRIBUTE3,
421 X_ATTRIBUTE4 => cart_rec.ATTRIBUTE4,
422 X_ATTRIBUTE5 => cart_rec.ATTRIBUTE5,
423 X_ATTRIBUTE6 => cart_rec.ATTRIBUTE6,
424 X_ATTRIBUTE7 => cart_rec.ATTRIBUTE7,
425 X_ATTRIBUTE8 => cart_rec.ATTRIBUTE8,
426 X_ATTRIBUTE9 => cart_rec.ATTRIBUTE9,
427 X_ATTRIBUTE10 => cart_rec.ATTRIBUTE10,
428 X_ATTRIBUTE11 => cart_rec.ATTRIBUTE11,
429 X_ATTRIBUTE12 => cart_rec.ATTRIBUTE12,
430 X_ATTRIBUTE13 => cart_rec.ATTRIBUTE13,
431 X_ATTRIBUTE14 => cart_rec.ATTRIBUTE14,
432 X_ATTRIBUTE15 => cart_rec.ATTRIBUTE15,
433 X_ATTRIBUTE16 => cart_rec.ATTRIBUTE16,
434 X_ATTRIBUTE17 => cart_rec.ATTRIBUTE17,
435 X_ATTRIBUTE18 => cart_rec.ATTRIBUTE18,
436 X_ATTRIBUTE19 => cart_rec.ATTRIBUTE19,
437 X_ATTRIBUTE20 => cart_rec.ATTRIBUTE20,
438 X_WAITLIST_MANUAL_IND => cart_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
439 X_WLST_PRIORITY_WEIGHT_NUM => cart_rec.wlst_priority_weight_num,
440 X_WLST_PREFERENCE_WEIGHT_NUM => cart_rec.wlst_preference_weight_num,
441 X_CORE_INDICATOR_CODE => cart_rec.core_indicator_code
442 );
443 END enroll_cart_unit;
444 PROCEDURE Validate_enroll_validate (
445 p_person_id IN igs_en_su_attempt.person_id%TYPE,
446 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
447 p_load_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
448 p_uoo_ids IN VARCHAR2,
449 p_program_cd IN igs_en_su_attempt.course_cd%TYPE,
450 p_message_name OUT NOCOPY VARCHAR2,
451 p_deny_warn OUT NOCOPY VARCHAR2,
452 p_return_status OUT NOCOPY VARCHAR2,
453 p_enr_method IN igs_en_cat_prc_dtl.enr_method_type%TYPE,
454 p_enrolled_dt IN DATE) AS
455 ------------------------------------------------------------------------------------
456 --Created by : amuthu
457 --Date created: 28-May-2002
458 --
459 --Purpose:
460 --Known limitations/enhancements and/or remarks:
461 --
462 --Change History:
463 --Who When What
464 --amuthu 28-Mar-2003 The return_type out parameter from ENRP_VAL_ENR_ENCMB
465 -- was returned with a value only for required units
466 -- validation and this was being used in setting the
467 -- return_status and hence only if the return type was 'E'
468 -- the status was set to Deny. now modified the code to
469 -- checking the return value and messages to set the return status
470 --amuthu 03-Jun-2002 The Program step validations were returning status as
471 -- FALSE even when a Warning was to be shown.
472 -- Hence 'RETURN' is being done only when the status
473 -- FALSE and the messages type is DENY
474 -- At the end if the messages is not null then type is
475 -- being set as WARN
476 --svanukur 04-dec-2003 Passing the load calendar details to the procedure
477 -- IGS_EN_VAL_ENCMB.enrp_val_enr_encmb as part of holds bug 3227399
478 --stutta 11-Feb-2004 Added new parameter p_enrolled_dt, passing the same to
479 -- enrol_cart_unit
480 -------------------------------------------------------------------------------------
481
482 l_drop_uoo_ids VARCHAR2(255);
483 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
484 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
485 l_unit_version igs_ps_unit_ver.version_number%TYPE;
486 lv_message_name VARCHAR2(2000);
487 lv_message_name2 VARCHAR2(2000);
488 lv_return_type VARCHAR2(2);
489 l_enr_method IGS_EN_METHOD_TYPE.enr_method_type%TYPE;
490 l_message VARCHAR2(2000);
491 l_return_status VARCHAR2(10);
492
493 CURSOR c_uv (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
494 SELECT unit_cd, version_number
495 FROM igs_ps_unit_ofr_opt
496 WHERE uoo_id = cp_uoo_id;
497
498
499
500 BEGIN
501 /********************************************************************************************
502 what are we trying to do in this procedure.
503 1. Loop thru all uoo_id's and Check for the Max CP ,if it suceeds then enroll the unit section
504 2. For a second time loop thru all uoo_id's and check for Min CP.
505 3. For a third time loop thru all uoo_id's and validate the other program
506 steps other than Min and Max cp. And if it succeeds then call the call_fee_ass (SFCR019)
507
508 Why are we doing it like this?
509 The validation written for Max CP first calculates the CP for all the Enrolled
510 units under the context program. Then added the CP of the current unit section
511 being enrolled and checks if the maximum has been reached. hence we check for the
512 Max CP first and then enroll the unit.
513
514 Why are we checking for Min CP seperately, this needs to be done because all the units
515 have already been enrolled, so if we pass the Credit point value as either null and dont
516 pass the parameter ifself internally the procedure calculates the CP, hence we have
517 to explicitly call the eval_min_cp with zero credit points as parameter.
518
519 Now after all this has been done we need to call the validate_program_step to
520 evaluate the other program steps
521 And also validates the holds, if the person have to enroll some required units, etc
522 ********************************************************************************************/
523
524
525 IF p_enr_method IS NULL THEN
526 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
527 igs_en_gen_017.enrp_get_enr_method(
528 p_enr_method_type => l_enr_method,
529 p_error_message => l_message,
530 p_ret_status => l_return_status);
531 IF l_return_status = 'FALSE' THEN
532 p_message_name := 'IGS_SS_EN_NOENR_METHOD' ;
533 p_return_status := 'FALSE';
534 p_deny_warn := 'DENY';
535 END IF;
536 ELSE
537 l_enr_method := p_enr_method;
538 END IF;
539
540 l_drop_uoo_ids := p_uoo_ids;
541
542 WHILE l_drop_uoo_ids IS NOT NULL LOOP
543
544 IF(instr(l_drop_uoo_ids,',',1) = 0) THEN
545 l_uoo_id := TO_NUMBER(l_drop_uoo_ids);
546 ELSE
547 l_uoo_id := TO_NUMBER(substr(l_drop_uoo_ids,0,instr(l_drop_uoo_ids,',',1)-1)) ;
548 END IF;
549
550
551 -- check for Max cp by passing the null value to credit points
552 --so that the default calculation are done for the CP of this unit section
553 igs_en_enroll_wlst.ss_eval_min_or_max_cp(p_person_id => p_person_id,
554 p_load_cal_type => p_load_cal_type,
555 p_load_ci_sequence_number => p_load_ci_sequence_number,
556 p_uoo_id => l_uoo_id,
557 p_program_cd => p_program_cd,
558 p_step_type => 'FMAX_CRDT',
559 p_credit_points => NULL, -- deliberately passing null, this value will be internally calculated
560 p_message_name => lv_message_name,
561 p_deny_warn => p_deny_warn,
562 p_return_status => p_return_status,
563 p_enr_method => l_enr_method);
564
565 IF lv_message_name IS NOT NULL AND instr(NVL(p_message_name,' '),lv_message_name) = 0 THEN
566 IF p_message_name IS NOT NULL THEN
567 p_message_name := p_message_name || ';' ||lv_message_name;
568 ELSE
569 p_message_name := lv_message_name;
570 END IF;
571 END IF;
572
573 IF p_return_status = 'FALSE' AND p_deny_warn = 'DENY' THEN
574 RETURN;
575 END IF;
576
577 OPEN c_uv(l_uoo_id);
578 FETCH c_uv INTO l_unit_cd, l_unit_version;
579 CLOSE c_uv;
580
581 -- call the procedure to enroll the unconfirmed or invalid unit
582 IGS_SS_EN_WRAPPERS.enroll_cart_unit (
583 P_PERSON_ID => p_person_id ,
584 P_COURSE_CD => p_program_cd ,
585 P_UNIT_CD => l_unit_Cd,
586 P_VERSION_NUMBER => l_unit_version,
587 P_UOO_ID => l_uoo_id,
588 P_UNIT_ATTEMPT_STATUS => 'ENROLLED',
589 P_ENROLLED_DT => NVL(p_enrolled_dt,SYSDATE));
590
591 IF(instr(l_drop_uoo_ids,',',1) = 0) THEN
592 l_drop_uoo_ids := NULL;
593 ELSE
594 l_drop_uoo_ids := substr(l_drop_uoo_ids,instr(l_drop_uoo_ids,',',1)+1);
595 END IF;
596
597 END LOOP;
598
599
600 l_drop_uoo_ids := p_uoo_ids;
601
602 WHILE l_drop_uoo_ids IS NOT NULL LOOP
603 l_uoo_id := null;
604 IF(instr(l_drop_uoo_ids,',',1) = 0) THEN
605 l_uoo_id := TO_NUMBER(l_drop_uoo_ids);
606 ELSE
607 l_uoo_id := TO_NUMBER(substr(l_drop_uoo_ids,0,instr(l_drop_uoo_ids,',',1)-1)) ;
608 END IF;
609
610 lv_message_name := null;
611
612 -- call the procedure to evaluate the Min CP by passing ZERO to the
613 -- credit points parameter
614 igs_en_enroll_wlst.ss_eval_min_or_max_cp(p_person_id => p_person_id,
615 p_load_cal_type => p_load_cal_type,
616 p_load_ci_sequence_number => p_load_ci_sequence_number,
617 p_uoo_id => l_uoo_id,
618 p_program_cd => p_program_cd,
619 p_step_type => 'FMIN_CRDT',
620 p_credit_points => 0.0, -- deliberately passing the value zero since the cp has already been enrolled
621 p_message_name => lv_message_name,
622 p_deny_warn => p_deny_warn,
623 p_return_status => p_return_status,
624 p_enr_method => l_enr_method);
625
626 IF lv_message_name IS NOT NULL AND instr(NVL(p_message_name,' '),lv_message_name) = 0 THEN
627 IF p_message_name IS NOT NULL THEN
628 p_message_name := p_message_name || ';' ||lv_message_name;
629 ELSE
630 p_message_name := lv_message_name;
631 END IF;
632 END IF;
633
634
635 IF p_return_status = 'FALSE' AND p_deny_warn = 'DENY' THEN
636 RETURN;
637 END IF;
638
639
640 IF(instr(l_drop_uoo_ids,',',1) = 0) THEN
641 l_drop_uoo_ids := NULL;
642 ELSE
643 l_drop_uoo_ids := substr(l_drop_uoo_ids,instr(l_drop_uoo_ids,',',1)+1);
644 END IF;
645
646 END LOOP;
647
648 -- Added the following code as part of the bug 2385096, pmarada
649 -- Validating the holds
650 l_drop_uoo_ids := p_uoo_ids;
651 WHILE l_drop_uoo_ids IS NOT NULL LOOP
652 IF(INSTR(l_drop_uoo_ids,',',1) = 0) THEN
653 l_uoo_id := TO_NUMBER(l_drop_uoo_ids);
654 ELSE
655 l_uoo_id := TO_NUMBER(SUBSTR(l_drop_uoo_ids,0,INSTR(l_drop_uoo_ids,',',1)-1)) ;
656 END IF;
657 lv_message_name := NULL;
658 lv_message_name2 :=NULL;
659
660
661 IF IGS_EN_VAL_ENCMB.enrp_val_enr_encmb(p_person_id,
662 p_program_cd ,
663 p_load_cal_type,
664 p_load_ci_sequence_number,
665 lv_message_name,
666 lv_message_name2,
667 lv_return_type,
668 NULL -- default value, it will be calculated internally based on the census date
669 ) THEN
670 p_return_status := 'TRUE';
671 ELSE
672 p_return_status := 'FALSE';
673 END IF;
674
675 IF lv_message_name IS NOT NULL AND INSTR(NVL(p_message_name,' '),lv_message_name) = 0 THEN
676 IF p_message_name IS NOT NULL THEN
677 p_message_name := p_message_name || ';' || lv_message_name;
678 ELSE
679 p_message_name := lv_message_name;
680 END IF;
681 END IF;
682 IF lv_message_name2 IS NOT NULL AND INSTR(NVL(p_message_name,' '),lv_message_name2) = 0 THEN
683 IF p_message_name IS NOT NULL THEN
684 p_message_name := p_message_name || ';' || lv_message_name2;
685 ELSE
686 p_message_name := lv_message_name2;
687 END IF;
688 END IF;
689
690 IF p_return_status = 'FALSE' AND (lv_message_name IS NOT NULL OR lv_message_name2 IS NOT NULL ) THEN
691 p_deny_warn := 'DENY';
692 RETURN;
693 END IF;
694
695 IF(INSTR(l_drop_uoo_ids,',',1) = 0) THEN
696 l_drop_uoo_ids := NULL;
697 ELSE
698 l_drop_uoo_ids := SUBSTR(l_drop_uoo_ids,INSTR(l_drop_uoo_ids,',',1)+1);
699 END IF;
700 END LOOP ;
701 -- End of the code added by pmarada, bug 2385096.
702
703 l_drop_uoo_ids := p_uoo_ids;
704 WHILE l_drop_uoo_ids IS NOT NULL LOOP
705
706 IF(instr(l_drop_uoo_ids,',',1) = 0) THEN
707 l_uoo_id := TO_NUMBER(l_drop_uoo_ids);
708 ELSE
709 l_uoo_id := TO_NUMBER(substr(l_drop_uoo_ids,0,instr(l_drop_uoo_ids,',',1)-1)) ;
710 END IF;
711
712 lv_message_name := null;
713
714 -- call the procedure to evaluate all program steps except Min and Max CP
715 IF igs_en_enroll_wlst.validate_prog (
716 p_person_id => p_person_id,
717 p_cal_type => p_load_cal_type,
718 p_ci_sequence_number => p_load_ci_sequence_number,
719 p_uoo_id => l_uoo_id,
720 p_course_cd => p_program_cd,
721 p_enr_method_type => l_enr_method,
722 p_message_name => lv_message_name,
723 p_deny_warn => p_deny_warn
724 ) THEN
725 p_return_status := 'TRUE';
726 ELSE
727 p_return_status := 'FALSE';
728 END IF;
729
730 IF lv_message_name IS NOT NULL AND instr(NVL(p_message_name,' '),lv_message_name) = 0 THEN
731 IF p_message_name IS NOT NULL THEN
732 p_message_name := p_message_name || ';' ||lv_message_name;
733 ELSE
734 p_message_name := lv_message_name;
735 END IF;
736 END IF;
737
738
739 IF p_return_status = 'FALSE' AND p_deny_warn = 'DENY' THEN
740 RETURN;
741 END IF;
742
743 OPEN c_uv(l_uoo_id);
744 FETCH c_uv INTO l_unit_cd, l_unit_version;
745 CLOSE c_uv;
746
747 -- call the procedure to insert a student todo record for fee recalculation (SFCR019)
748 IGS_SS_EN_WRAPPERS.call_fee_ass (
749 p_person_id => p_person_id,
750 p_cal_type => p_load_cal_type,
751 p_sequence_number => p_load_ci_sequence_number,
752 p_course_cd => p_program_cd,
753 p_unit_cd => l_unit_Cd,
754 p_uoo_id => l_uoo_id);
755
756 IF(instr(l_drop_uoo_ids,',',1) = 0) THEN
757 l_drop_uoo_ids := NULL;
758 ELSE
759 l_drop_uoo_ids := substr(l_drop_uoo_ids,instr(l_drop_uoo_ids,',',1)+1);
760 END IF;
761
762 END LOOP;
763
764 -- if the code has reached this point then ther either warnings only or everything went through
765 IF p_message_name IS NULL THEN
766 p_deny_warn := NULL;
767 ELSE
768 p_deny_warn := 'WARN';
769 END IF;
770
771 p_return_status := 'TRUE';
772
773 RETURN ;
774
775 END Validate_enroll_validate;
776
777 PROCEDURE get_cart_details
778 ( p_person_id IN NUMBER,
779 p_program_cd IN VARCHAR2,
780 p_load_cal_type IN VARCHAR2,
781 p_load_ci_seq_num IN NUMBER,
782 p_total_units_cart OUT NOCOPY NUMBER,
783 p_total_cp_cart OUT NOCOPY NUMBER
784 ) AS
785 /*------------------------------------------------------------------------------------
786 --Created by : Kamal ( Oracle IDC)
787 --Date created: 13-Jan-2003
788 --
789 -- Purpose: To get the total number of units in Cart and the total CP of the units in
790 -- cart, for the given student in a Term.
791 -- The criteria is inline with the VO 'EnrWrkshtVO.xml', which is used in
792 -- Enrollment Cart Page
793 --
794 --Change History:
795 --Who When What
796 --sarakshi 27-Jun-2003 Enh#2930935,modified the cursor cur_cart_credit_pts such that it pick up
797 -- usec level enroled crdit points if exist else unit level
798 --myoganat 16-Jun-2003 Removed the reference to the profile IGS_EN_INCL_AUDIT_CP
799 -- in Cursor CUR_CART_CREDIT_PTS as part of Bug #2855870 (ENCR032 Build)
800 -- rvangala 11-Dec-2003 Bug #3112107, replaced IN clause in queries of cursors cur_units_cart
801 -- and cur_cart_credit_pts with direct join
802 -------------------------------------------------------------------------------------*/
803 CURSOR cur_units_cart(cp_person_id NUMBER,cp_course_cd VARCHAR2,cp_load_cal_type VARCHAR2,cp_load_ci_seq_num NUMBER ) IS
804 SELECT count(*) UNITS_IN_CART
805 FROM
806 IGS_EN_SU_ATTEMPT SUA,
807 IGS_EN_USEC_STAT_DSP_V ENUSECSTAT,
808 IGS_PS_UNIT_OFR_OPT UOO
809 WHERE sua.person_id = cp_person_id
810 AND sua.course_cd = cp_course_cd
811 AND sua.uoo_id = uoo.uoo_id
812 AND enusecstat.unit_section_status = uoo.unit_section_status
813 AND enusecstat.displayed = 'Y'
814 AND sua.unit_attempt_status IN ( 'UNCONFIRM','INVALID' )
815 AND 0< (SELECT 1 FROM igs_ca_load_to_teach_v vt
816 WHERE vt.load_cal_type =cp_load_cal_type
817 AND vt.load_ci_sequence_number=cp_load_ci_seq_num
818 AND sua.cal_type = vt.teach_cal_type
819 AND sua.ci_sequence_number = vt.teach_ci_sequence_number) ;
820
821 -- get the total credit points in the cart
822 CURSOR cur_cart_credit_pts(cp_person_id NUMBER,cp_course_cd VARCHAR2,cp_load_cal_type VARCHAR2,cp_load_ci_seq_num NUMBER ) IS
823 SELECT SUM(NVL(sua.override_enrolled_cp,NVL(cps.enrolled_credit_points,
824 uv.enrolled_credit_points))) total_credit_points
825 FROM igs_en_su_attempt sua,
826 igs_en_usec_stat_dsp_v enusecstat,
827 igs_ps_unit_ofr_opt uoo,
828 igs_ps_usec_cps cps,
829 igs_ps_unit_ver uv
830 WHERE sua.person_id = cp_person_id AND
831 sua.course_cd = cp_course_cd AND
832 sua.uoo_id = uoo.uoo_id AND
833 uoo.uoo_id = cps.uoo_id(+) AND
834 enusecstat.unit_section_status = uoo.unit_section_status AND
835 enusecstat.displayed = 'Y' AND
836 sua.unit_attempt_status IN ('UNCONFIRM','INVALID') AND
837 sua.no_assessment_ind = 'N' AND
838 0< (SELECT 1 FROM igs_ca_load_to_teach_v vt
839 WHERE vt.load_cal_type=cp_load_cal_type
840 AND vt.load_ci_sequence_number=cp_load_ci_seq_num
841 AND sua.cal_type=vt.teach_cal_type
842 AND sua.ci_sequence_number=vt.teach_ci_sequence_number)
843 AND uv.unit_cd= sua.unit_cd
844 AND uv.version_number = sua.version_number;
845
846
847 BEGIN
848 -- initialize the values to zero
849 p_total_units_cart := 0;
850 p_total_cp_cart := 0;
851 -- fetch the units in cart and the total credit points of the units in cart
852 OPEN cur_units_cart(p_person_id,p_program_cd,p_load_cal_type,p_load_ci_seq_num);
853 FETCH cur_units_cart INTO p_total_units_cart;
854 CLOSE cur_units_cart;
855 -- fetch the units in cart and the total credit points of the units in cart
856 OPEN cur_cart_credit_pts(p_person_id,p_program_cd,p_load_cal_type,p_load_ci_seq_num);
857 FETCH cur_cart_credit_pts INTO p_total_cp_cart;
858 CLOSE cur_cart_credit_pts;
859
860 END get_cart_details;
861
862 PROCEDURE insert_into_enr_worksheet(
863 p_person_number IN VARCHAR2,
864 p_course_cd IN VARCHAR2,
865 p_uoo_id IN NUMBER,
866 p_waitlist_ind IN VARCHAR2,
867 p_session_id IN NUMBER,
868 p_return_status OUT NOCOPY VARCHAR2,
869 p_message OUT NOCOPY VARCHAR2,
870 p_cal_type IN VARCHAR2,
871 p_ci_sequence_number IN NUMBER,
872 p_audit_requested IN VARCHAR2,
873 p_enr_method IN igs_en_cat_prc_dtl.enr_method_type%TYPE,
874 p_override_cp IN NUMBER,
875 p_subtitle IN VARCHAR2,
876 p_gradsch_cd IN VARCHAR2,
877 p_gs_version_num IN NUMBER,
878 p_core_indicator_code IN VARCHAR2,
879 p_calling_obj IN VARCHAR2) AS
880
881
882
883 l_enr_method_type igs_en_method_type.enr_method_type%TYPE;
884 l_deny_warn VARCHAR2(10);
885 l_message VARCHAR2(2000);
886 l_return_status BOOLEAN;
887
888
889 BEGIN
890
891
892 IF p_enr_method IS NULL THEN
893 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
894 igs_en_gen_017.enrp_get_enr_method(
895 p_enr_method_type => l_enr_method_type,
896 p_error_message => p_message,
897 p_ret_status => p_return_status);
898 IF p_return_status = 'FALSE' THEN
899 p_message := 'IGS_SS_EN_NOENR_METHOD';
900 fnd_message.set_name('IGS','IGS_SS_EN_NOENR_METHOD');
901 fnd_msg_pub.add;
902 p_return_status := fnd_api.g_ret_sts_error;
903 END IF;
904 ELSE
905 l_enr_method_type:= p_enr_method;
906 END IF;
907
908
909 l_return_status := igs_en_ofr_wlst_opt.ofr_enrollment_or_waitlist (
910 p_uoo_id => p_uoo_id,
911 p_waitlist_ind => p_waitlist_ind,
912 p_person_number => p_person_number,
913 p_course_cd => p_course_cd,
914 p_enr_method_type => l_enr_method_type,
915 p_session_id => p_session_id,
916 p_deny_or_warn => l_deny_warn,
917 p_message => p_message,
918 p_cal_type => p_cal_type, -- load calendar
919 p_ci_sequence_number => p_ci_sequence_number, -- load calendar
920 p_audit_requested => p_audit_requested,
921 p_override_cp =>p_override_cp ,
922 p_subtitle =>p_subtitle ,
923 p_gradsch_cd =>p_gradsch_cd ,
924 p_gs_version_num=>p_gs_version_num ,
925 p_core_indicator_code=>p_core_indicator_code ,
926 p_calling_obj => p_calling_obj
927 ); -- ptandon, Prevent Dropping Core Units build
928
929
930 IF l_return_status and l_deny_warn is null THEN
931 p_return_status := 'S';
932 ELSE
933 IF l_deny_warn = 'WARN' THEN
934 p_return_status := 'W';
935 ELSE
936 p_return_status := 'D';
937 END IF;
938 END IF;
939 END insert_into_enr_worksheet;
940
941 PROCEDURE drop_selected_units (
942 p_uoo_ids IN VARCHAR2,
943 p_person_id IN NUMBER,
944 p_person_type IN VARCHAR2,
945 p_load_cal_type IN VARCHAR2,
946 p_load_sequence_number IN NUMBER,
947 p_program_cd IN VARCHAR2,
948 p_program_version IN NUMBER ,
949 p_dcnt_reason_cd IN VARCHAR2 ,
950 p_admin_unit_status IN VARCHAR2 ,
951 p_effective_date IN DATE ,
952 p_failed_uoo_ids OUT NOCOPY VARCHAR2,
953 p_failed_unit_cds OUT NOCOPY VARCHAR2,
954 p_return_status OUT NOCOPY VARCHAR2,
955 p_message OUT NOCOPY VARCHAR2,
956 p_ovrrd_min_cp_chk IN VARCHAR2 ,
957 p_ovrrd_crq_chk IN VARCHAR2 , --msrinivi , added new param 2-may-2002
958 p_ovrrd_prq_chk IN VARCHAR2 , --msrinivi added new param 2-may-2002
959 p_ovrrd_att_typ_chk IN VARCHAR2
960 ) AS
961 /* History
962 WHO WHEN WHAT
963 smanglm 03-02-2003 call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
964 svenkata 28-Jan-03 Modified the manner in which the string of unit codes that failed the co-req / pre-req valdns are created.
965 svenkata 7-Jan-03 Incorporated the logic for 'When first Reach Attendance Type'. The routine enrp_val_coo_att is being called to get the
966 Att Typ before updating the CP.The routine eval_unit_forced_type is then called called to evaluate with the fetched value-Bug#2737263
967 svenkata 20-Dec-02 Added a new parameter p_ovrrd_att_type for attendance Type validation. Incorporated Att Type
968 validation when dropping a Unit section.Bug# 2686793
969 Nishikant 01-NOV-2002 SEVIS Build Bug#2641905. parameters p_person_id and p_message added in the calls
970 get_notification.
971 svenkata 21-oct-02 Bug 2616692 - The call to the fucntion eval_min_cp has been modified to add
972 4 new parameters .
973 ayedubat 3-JUL-2002 1.Changed the dynamic sql creation of the cursor,c_ref_cur_inst to conside the 'WAITLISTED' unit attempt status
974 2.Added a validation to check the existence of records in the table,lData before
975 looping through the records while doing the prereq validations for the bug fix:2443876
976 svanukur 04-dec-2003 Passing the load calendar details to the procedure
977 IGS_EN_VAL_ENCMB.enrp_val_enr_encmb as part of holds bug 3227399
978 ptandon 16-Feb-2004 Added Exception handling section and handled the exception NO_AUSL_RECORD_FOUND to
979 return a meaningful error message. Bug# 3418087.
980 stutta 16-Nov-2004 Validate coreq/prereq rules for all sua before drop operation so as to suppress the rules
981 which failed even before the drop. Bug # 3926541
982 */
983
984
985 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
986 l_deny_warn_min_cp VARCHAR2(10) := NULL ;
987 l_deny_warn_coreq VARCHAR2(10) := NULL ;
988 l_deny_warn_prereq VARCHAR2(10) := NULL ;
989 l_deny_warn_att_type VARCHAR2(100) := NULL ;
990
991 l_message VARCHAR2(2000);
992 l_min_cp_message VARCHAR2(2000);
993 l_att_type_message VARCHAR2(2000) := NULL ;
994
995 l_min_cp_failed BOOLEAN := FALSE;
996 l_att_typ_failed BOOLEAN := FALSE;
997 l_coreq_failed BOOLEAN := FALSE;
998 l_prereq_failed BOOLEAN := FALSE;
999
1000 l_all_units_for_drop BOOLEAN := FALSE;
1001 l_failed_unit_codes VARCHAR2(2000);
1002 l_failed_uoo_ids VARCHAR2(2000);
1003 l_coreq_failed_units VARCHAR2(2000);
1004 l_prereq_failed_units VARCHAR2(2000);
1005 l_drop_uoo_ids VARCHAR2(2000);
1006
1007 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
1008 l_unit_version_number igs_ps_unit_ver.VERSION_NUMBER%TYPE;
1009
1010 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
1011 l_enr_cal_type VARCHAR2(20);
1012 l_enr_ci_seq NUMBER(20);
1013 l_enr_cat VARCHAR2(20);
1014 l_enr_comm VARCHAR2(2000);
1015
1016 l_acad_cal_type igs_ca_inst.cal_type%type;
1017 l_acad_ci_sequence_number igs_ca_inst.sequence_number%type;
1018 l_acad_start_dt igs_ca_inst.start_dt%type;
1019 l_acad_end_dt igs_ca_inst.end_dt%type;
1020 l_alternate_code igs_ca_inst.alternate_code%type;
1021 l_acad_message varchar2(100);
1022
1023 -- Added as part of Enrollment Eligibility and validations
1024 l_eftsu_total igs_en_su_attempt.override_eftsu%type;
1025 l_total_credit_points igs_en_su_attempt.override_enrolled_cp%TYPE ;
1026 l_credit_points igs_en_su_attempt.override_enrolled_cp%TYPE := 0;
1027 l_dummy VARCHAR2(200);
1028 l_return_status VARCHAR2(10);
1029
1030 l_sub_uoo_ids VARCHAR2(2000);
1031 l_nonsub_uoo_ids VARCHAR2(2000);
1032 l_sub_unit VARCHAR2(1);
1033 l_chk_sub VARCHAR2(1);
1034 l_sub_drop_uoo_ids VARCHAR2(2000);
1035 L_DROP_ALLUOO_IDS VARCHAR2(2000);
1036
1037 NO_AUSL_RECORD_FOUND EXCEPTION;
1038 pragma exception_init(NO_AUSL_RECORD_FOUND,-20010);
1039
1040 CURSOR cur_uoo_id (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1041 SELECT unit_cd, version_number
1042 FROM igs_ps_unit_ofr_opt
1043 WHERE uoo_id = p_uoo_id;
1044
1045
1046 CURSOR c_dcnt_rsn IS
1047 SELECT DISCONTINUATION_REASON_CD
1048 FROM igs_en_dcnt_reasoncd
1049 WHERE NVL(closed_ind,'N') ='N'
1050 AND dflt_ind ='Y'
1051 AND dcnt_unit_ind ='Y';
1052
1053
1054 lv_message_name VARCHAR2(2000);
1055 lv_message_name2 VARCHAR2(100);
1056 lv_return_type VARCHAR2(2);
1057 l_req_unit_failed BOOLEAN;
1058
1059 l_dcnt_reason_cd igs_en_dcnt_reasoncd.discontinuation_reason_cd%TYPE;
1060
1061 -- Cursor to get the coo_id of the student.
1062 CURSOR cur_coo_id IS
1063 SELECT coo_id coo_id
1064 FROM igs_en_stdnt_ps_att
1065 WHERE person_id = p_person_id
1066 AND course_cd = p_program_cd ;
1067
1068 l_attendance_type_reach BOOLEAN := TRUE;
1069 l_cur_coo_id cur_coo_id%ROWTYPE;
1070 l_attendance_types VARCHAR2(100); -- As returned from the function igs_en_val_sca.enrp_val_coo_att
1071
1072 TYPE c_ref_cursor IS REF CURSOR;
1073 c_ref_cur_inst c_ref_cursor;
1074 c_ref_only_dup c_ref_cursor;
1075 c_chk_sub c_ref_cursor;
1076 v_ref_cur_rec igs_en_su_attempt%ROWTYPE;
1077
1078 TYPE tDataBuff IS
1079 TABLE OF igs_en_su_attempt%ROWTYPE
1080 INDEX BY BINARY_INTEGER;
1081
1082 lData tDataBuff;
1083 lDataEmpty tDataBuff;
1084 t1_idx BINARY_INTEGER := 0;
1085 l_prereq_failed_uoo_ids VARCHAR2(2000);
1086 l_coreq_failed_uoo_ids VARCHAR2(2000);
1087 l_person_id_found igs_en_su_attempt_all.person_id%TYPE;
1088 BEGIN
1089 lData := lDataEmpty; --Initializing the array
1090
1091 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
1092 igs_en_gen_017.enrp_get_enr_method(
1093 p_enr_method_type => l_enr_meth_type,
1094 p_error_message => l_message,
1095 p_ret_status => l_return_status);
1096
1097 IF p_dcnt_reason_cd IS NULL THEN
1098 OPEN c_dcnt_rsn;
1099 FETCH c_dcnt_rsn INTO l_dcnt_reason_cd;
1100 CLOSE c_dcnt_rsn;
1101 END IF;
1102
1103 -- added below logic to get the Academic Calendar which is used by method enrp_get_enr_cat
1104 --
1105 -- get the academic calendar of the given Load Calendar
1106 --
1107 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
1108 p_cal_type => p_load_cal_type,
1109 p_ci_sequence_number => p_load_sequence_number,
1110 p_acad_cal_type => l_acad_cal_type,
1111 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
1112 p_acad_ci_start_dt => l_acad_start_dt,
1113 p_acad_ci_end_dt => l_acad_end_dt,
1114 p_message_name => l_acad_message );
1115
1116 IF l_acad_message IS NOT NULL THEN
1117 p_message := l_acad_message;
1118 p_return_status := 'FALSE';
1119 RETURN;
1120 END IF;
1121
1122
1123 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat(
1124 p_person_id,
1125 p_program_cd,
1126 l_acad_cal_type,
1127 l_acad_ci_sequence_number,
1128 NULL,
1129 l_enr_cal_type,
1130 l_enr_ci_seq,
1131 l_enr_comm,
1132 l_dummy);
1133
1134 IF l_enr_comm = 'BOTH' THEN
1135 l_enr_comm :='ALL';
1136 END IF;
1137
1138 l_message:= NULL;
1139 l_deny_warn_min_cp := igs_ss_enr_details.get_notification(
1140 p_person_type => p_person_type,
1141 p_enrollment_category => l_enr_cat,
1142 p_comm_type => l_enr_comm,
1143 p_enr_method_type => l_enr_meth_type,
1144 p_step_group_type => 'PROGRAM',
1145 p_step_type => 'FMIN_CRDT',
1146 p_person_id => p_person_id,
1147 p_message => l_message
1148 ) ;
1149 IF l_message IS NOT NULL THEN
1150 p_message := l_message;
1151 p_return_status := 'FALSE';
1152 RETURN;
1153 END IF;
1154
1155 l_deny_warn_att_type := igs_ss_enr_details.get_notification(
1156 p_person_type => p_person_type,
1157 p_enrollment_category => l_enr_cat,
1158 p_comm_type => l_enr_comm,
1159 p_enr_method_type => l_enr_meth_type,
1160 p_step_group_type => 'PROGRAM',
1161 p_step_type => 'FATD_TYPE',
1162 p_person_id => p_person_id,
1163 p_message => l_message
1164 ) ;
1165 IF l_message IS NOT NULL THEN
1166 p_message := l_message;
1167 p_return_status := 'FALSE';
1168 RETURN;
1169 END IF;
1170
1171 IF l_deny_warn_att_type IS NOT NULL THEN
1172 OPEN cur_coo_id;
1173 FETCH cur_coo_id INTO l_cur_coo_id;
1174 CLOSE cur_coo_id;
1175
1176 -- Check if the Forced Attendance Type has already been reached for the Student before transferring .
1177 l_attendance_type_reach := igs_en_val_sca.enrp_val_coo_att(
1178 p_person_id => p_person_id,
1179 p_coo_id => l_cur_coo_id.coo_id,
1180 p_cal_type => l_acad_cal_type,
1181 p_ci_sequence_number => l_acad_ci_sequence_number,
1182 p_message_name => l_message,
1183 p_attendance_types => l_attendance_types,
1184 p_load_or_teach_cal_type => p_load_cal_type,
1185 p_load_or_teach_seq_number => p_load_sequence_number);
1186
1187 -- Assign values to the parameter p_deny_warn_att based on if Attendance Type has not been already reached or not.
1188 IF l_attendance_type_reach THEN
1189 l_deny_warn_att_type := 'AttTypReached' ;
1190 ELSE
1191 l_deny_warn_att_type := 'AttTypNotReached' ;
1192 END IF ;
1193
1194 END IF ;
1195
1196 l_deny_warn_coreq := igs_ss_enr_details.get_notification(
1197 p_person_type => p_person_type,
1198 p_enrollment_category => l_enr_cat,
1199 p_comm_type => l_enr_comm,
1200 p_enr_method_type => l_enr_meth_type,
1201 p_step_group_type => 'UNIT',
1202 p_step_type => 'COREQ',
1203 p_person_id => p_person_id,
1204 p_message => l_message
1205 ) ;
1206 IF l_message IS NOT NULL THEN
1207 p_message := l_message;
1208 p_return_status := 'FALSE';
1209 RETURN;
1210 END IF;
1211
1212 l_deny_warn_prereq := igs_ss_enr_details.get_notification(
1213 p_person_type => p_person_type,
1214 p_enrollment_category => l_enr_cat,
1215 p_comm_type => l_enr_comm,
1216 p_enr_method_type => l_enr_meth_type,
1217 p_step_group_type => 'UNIT',
1218 p_step_type => 'PREREQ',
1219 p_person_id => p_person_id,
1220 p_message => l_message
1221 ) ;
1222 IF l_message IS NOT NULL THEN
1223 p_message := l_message;
1224 p_return_status := 'FALSE';
1225 RETURN;
1226 END IF;
1227
1228
1229 --Decode the concatenated string and check that all units are not selected,
1230 --in which case, just delete all units w/o checking cp or coreq requirements.
1231 l_drop_uoo_ids := p_uoo_ids;
1232
1233 OPEN c_ref_cur_inst FOR '
1234 SELECT DISTINCT u.person_id
1235 FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V
1236 WHERE person_id =:1
1237 AND course_cd = :2
1238 AND unit_attempt_status IN (''ENROLLED'',''INVALID'',''WAITLISTED'')
1239 AND cal_type = teach_cal_type
1240 AND ci_sequence_number = teach_ci_sequence_number
1241 AND load_cal_type = :3
1242 AND load_ci_sequence_number =:4
1243 AND uoo_id NOT IN('||l_drop_uoo_ids||')'
1244 USING p_person_id, p_program_cd, p_load_cal_type, p_load_sequence_number ;
1245
1246 FETCH c_ref_cur_inst INTO l_person_id_found ;
1247 IF c_ref_cur_inst%NOTFOUND THEN
1248 l_all_units_for_drop := TRUE;
1249 END IF;
1250 ClOSE c_ref_cur_inst;
1251
1252 IF NOT l_all_units_for_drop THEN
1253 -- Even if only duplicate unit attempt are selected for drop then do
1254 -- not perform any validations. So that we do not get any min cp or
1255 -- attendance type validation fialures.
1256 OPEN c_ref_only_dup FOR '
1257 SELECT U.*
1258 FROM IGS_EN_SU_ATTEMPT U
1259 WHERE person_id =:1 AND
1260 unit_attempt_status <> ''DUPLICATE'' AND
1261 (cal_type,ci_sequence_number) IN
1262 (SELECT teach_cal_type,teach_ci_sequence_number
1263 FROM igs_ca_load_to_teach_v
1264 WHERE load_cal_type = :2
1265 AND load_ci_sequence_number =:3 )
1266 AND uoo_id IN('||l_drop_uoo_ids||')'
1267 USING p_person_id, p_load_cal_type, p_load_sequence_number;
1268 FETCH c_ref_only_dup INTO v_ref_cur_rec ;
1269 IF c_ref_only_dup%NOTFOUND THEN
1270 l_all_units_for_drop := TRUE;
1271 END IF;
1272 CLOSE c_ref_only_dup;
1273 END IF;
1274
1275 --call procedure to reorder the unts such that subordinate units are processed first.
1276 -- the list of units are reordered and returned in l_drop_alluoo_ids.
1277
1278 enrp_chk_del_sub_units (
1279 p_person_id => p_person_id ,
1280 p_course_cd => p_program_cd,
1281 p_load_cal_type => p_load_cal_type,
1282 p_load_ci_seq_num => p_load_sequence_number,
1283 p_selected_uoo_ids => p_uoo_ids,
1284 p_ret_all_uoo_ids => l_drop_alluoo_ids,
1285 p_ret_sub_uoo_ids => l_sub_drop_uoo_ids,
1286 p_ret_nonsub_uoo_ids => l_nonsub_uoo_ids,
1287 p_delete_flag => 'N'
1288 );
1289
1290 IF l_all_units_for_drop THEN
1291 WHILE l_drop_alluoo_ids IS NOT NULL LOOP
1292 l_credit_points := 0;
1293 --extract the uoo_id
1294 IF(instr(l_drop_alluoo_ids,',',1) = 0) THEN
1295 l_uoo_id :=TO_NUMBER(l_drop_alluoo_ids);
1296 ELSE
1297
1298 l_uoo_id := TO_NUMBER(substr(l_drop_alluoo_ids,0,instr(l_drop_alluoo_ids,',',1)-1)) ;
1299 END IF;
1300
1301 --param indication if unit is sub or not
1302 l_sub_unit := 'N';
1303 --cursor to
1304 IF l_sub_drop_uoo_ids IS NOT NULL THEN
1305
1306 OPEN c_chk_sub FOR 'Select ''X'' from igs_en_su_attempt sua where sua.uoo_id in ('||l_sub_drop_uoo_ids||') AND sua.person_id =
1307 '||p_person_id|| 'AND sua.course_cd = '''|| p_program_cd|| '''AND exists (SELECT ''X'' from igs_ps_unit_ofr_opt uoo
1308 WHERE uoo.sup_uoo_id IN (' ||l_drop_alluoo_ids||') AND uoo.relation_type = ''SUBORDINATE'' AND uoo.uoo_id = sua.uoo_id)';
1309
1310 FETCH c_chk_sub INTO l_chk_sub;
1311 IF c_chk_sub%FOUND THEN
1312 l_sub_unit := 'Y';
1313 END IF;
1314 CLOSE c_chk_sub;
1315 END IF;
1316
1317 --IF l_all_units_for_drop THEN
1318 --Call enrp_dropall_units
1319 igs_en_gen_004.enrp_dropall_unit(
1320 p_person_id => p_person_id,
1321 p_cal_type => p_load_cal_type,
1322 p_ci_sequence_number => p_load_sequence_number,
1323 p_dcnt_reason_cd => NVL(p_dcnt_reason_cd,l_dcnt_reason_cd),
1324 p_admin_unit_sta => p_admin_unit_status,
1325 p_effective_date => p_effective_date,
1326 p_program_cd => p_program_cd,
1327 p_uoo_id => l_uoo_id,
1328 p_sub_unit => l_sub_unit);
1329 IF(instr(l_drop_alluoo_ids,',',1) = 0) THEN
1330 l_drop_alluoo_ids := NULL;
1331 ELSE
1332 l_drop_alluoo_ids := substr(l_drop_alluoo_ids,instr(l_drop_alluoo_ids,',',1)+1);
1333 END IF;
1334 END LOOP;
1335 ELSE
1336
1337 OPEN c_ref_cur_inst FOR ' SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id
1338 = :1 AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND
1339 uoo_id NOT IN('||p_uoo_ids||')'
1340 USING p_person_id;
1341
1342 LOOP
1343
1344 FETCH c_ref_cur_inst INTO v_ref_cur_rec ;
1345
1346 EXIT WHEN c_ref_cur_inst%NOTFOUND;
1347
1348 IF p_ovrrd_crq_chk = 'N' AND l_deny_warn_coreq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_coreq( --Do a coreq check only if l_deny_warn_coreq is defined
1349 p_person_id => p_person_id,
1350 p_load_cal_type => p_load_cal_type,
1351 p_load_sequence_number => p_load_sequence_number,
1352 p_uoo_id => v_ref_cur_rec.uoo_id,
1353 p_course_cd => p_program_cd,
1354 p_course_version => p_program_version,
1355 p_message => l_message,
1356 p_deny_warn => l_deny_warn_coreq,
1357 p_calling_obj => 'JOB') THEN
1358
1359 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
1360 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
1361 l_coreq_failed_units := l_coreq_failed_units || ',' || v_ref_cur_rec.unit_cd;
1362 l_coreq_failed_uoo_ids:= l_coreq_failed_uoo_ids ||','|| TO_CHAR(v_ref_cur_rec.uoo_id);
1363
1364 ELSE
1365 -- Since the coreq rule has passed, store these passed uoo_ids
1366 -- into a temp table so that the prereq check can be run for these uoo_ids
1367 -- instead of opening the cursor again
1368
1369 lData(t1_idx).uoo_id := v_ref_cur_rec.uoo_id;
1370 lData(t1_idx).unit_cd := v_ref_cur_rec.unit_cd;
1371 t1_idx :=t1_idx+1;
1372
1373 END IF;
1374 END LOOP;
1375 ClOSE c_ref_cur_inst;
1376
1377 -- Instead of opening the cursor again, we are storing the passed uoo_ids
1378 -- values into a Table
1379 -- Check whether records exist in the temparary table,lDATA.
1380 IF lData.COUNT > 0 THEN
1381 FOR i IN lData.FIRST .. lData.LAST LOOP
1382 IF p_ovrrd_prq_chk = 'N' AND l_deny_warn_prereq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_prereq( --Do a coreq check only if l_deny_warn_prereq is defined
1383 p_person_id => p_person_id,
1384 p_load_cal_type => p_load_cal_type,
1385 p_load_sequence_number => p_load_sequence_number,
1386 p_uoo_id => lData(i).uoo_id,
1387 p_course_cd => p_program_cd,
1388 p_course_version => p_program_version,
1389 p_message => l_message,
1390 p_deny_warn => l_deny_warn_prereq,
1391 p_calling_obj => 'JOB') THEN
1392
1393 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
1394 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
1395 l_prereq_failed_units := l_prereq_failed_units || ',' || lData(i).unit_cd;
1396 l_prereq_failed_uoo_ids := l_prereq_failed_uoo_ids ||','|| TO_CHAR(lData(i).uoo_id);
1397
1398 END IF;
1399 END LOOP;
1400
1401 END IF;
1402 t1_idx := 0;
1403
1404 --Only some of the units have been selected for drop. so, loop thru them and drop them
1405
1406
1407 --check if the unit being deleted is a subordiante unit before calling drop_all_units.
1408 --initialize l_sub_unit to 'N'
1409 --check if the unit attmepted is in the list of sub units being dropped, then check if the
1410 --superior unit of that unit is in the list of the units being dropped, if yes set the l_sub_unit to 'Y'.
1411 WHILE l_drop_alluoo_ids IS NOT NULL LOOP
1412 l_credit_points := 0;
1413 --extract the uoo_id
1414 IF(instr(l_drop_alluoo_ids,',',1) = 0) THEN
1415 l_uoo_id :=TO_NUMBER(l_drop_alluoo_ids);
1416 ELSE
1417
1418 l_uoo_id := TO_NUMBER(substr(l_drop_alluoo_ids,0,instr(l_drop_alluoo_ids,',',1)-1)) ;
1419 END IF;
1420
1421 --param indication if unit is sub or not
1422 l_sub_unit := 'N';
1423 --cursor to
1424 IF l_sub_drop_uoo_ids IS NOT NULL THEN
1425
1426 OPEN c_chk_sub FOR 'Select ''X'' from igs_en_su_attempt sua where sua.uoo_id in ('||l_sub_drop_uoo_ids||') AND sua.person_id =
1427 '||p_person_id|| 'AND sua.course_cd = '''|| p_program_cd|| '''AND exists (SELECT ''X'' from igs_ps_unit_ofr_opt uoo
1428 WHERE uoo.sup_uoo_id IN (' ||l_drop_alluoo_ids||') AND uoo.relation_type = ''SUBORDINATE'' AND uoo.uoo_id = sua.uoo_id)';
1429
1430 FETCH c_chk_sub INTO l_chk_sub;
1431 IF c_chk_sub%FOUND THEN
1432 l_sub_unit := 'Y';
1433 END IF;
1434 CLOSE c_chk_sub;
1435 END IF;
1436 -- If min cp validation fails, error out
1437 IF p_ovrrd_min_cp_chk = 'Y' OR NOT l_min_cp_failed THEN
1438 OPEN cur_uoo_id(l_uoo_id);
1439 FETCH cur_uoo_id INTO l_unit_cd,l_unit_version_number;
1440 CLOSE cur_uoo_id ;
1441
1442 IF l_deny_warn_min_cp IS NOT NULL THEN -- Min CP validation step is defined
1443
1444 -- A call to igs_en_prc_load.enrp_clc_eftsu_total as part of- Enrollment Eligibility and validations .
1445 -- The Total enrolled CP of the student has to be determined before the unit is dropped(l_total_credit_points) .
1446 -- The unit is then dropped , and eval_min_cp is called with the value of l_total_enrolled_cp.
1447 -- The value of l_total_enrolled_cp is essential to determine if the Min Credit Points is already reached
1448 -- by the student before that Unit is dropped.
1449
1450 l_eftsu_total := igs_en_prc_load.enrp_clc_eftsu_total(
1451 p_person_id => p_person_id,
1452 p_course_cd => p_program_cd ,
1453 p_acad_cal_type => l_acad_cal_type,
1454 p_acad_sequence_number => l_acad_ci_sequence_number,
1455 p_load_cal_type => p_load_cal_type,
1456 p_load_sequence_number => p_load_sequence_number,
1457 p_truncate_ind => 'N',
1458 p_include_research_ind => 'Y' ,
1459 p_key_course_cd => NULL ,
1460 p_key_version_number => NULL ,
1461 p_credit_points => l_total_credit_points );
1462
1463
1464 --Call enrp_dropall_units
1465 -- Dropping the unit before Min CP check, moved to here as part of bug 2401891
1466 --pass the p_sub_unit parameter to indicate if the unit being dropped is a subordinate(placements build)
1467 igs_en_gen_004.enrp_dropall_unit(
1468 p_person_id => p_person_id,
1469 p_cal_type => p_load_cal_type,
1470 p_ci_sequence_number => p_load_sequence_number,
1471 p_dcnt_reason_cd => NVL(p_dcnt_reason_cd,l_dcnt_reason_cd),
1472 p_admin_unit_sta => p_admin_unit_status,
1473 p_effective_date => p_effective_date,
1474 p_program_cd => p_program_cd,
1475 p_uoo_id => l_uoo_id,
1476 p_sub_unit => l_sub_unit);
1477
1478 IF p_ovrrd_min_cp_chk = 'Y' OR igs_en_elgbl_program.eval_min_cp(
1479 p_person_id => p_person_id,
1480 p_load_calendar_type => p_load_cal_type,
1481 p_load_cal_sequence_number => p_load_sequence_number,
1482 p_uoo_id => l_uoo_id,
1483 p_program_cd => p_program_cd,
1484 p_program_version => p_program_version,
1485 p_message => l_min_cp_message,
1486 p_deny_warn => l_deny_warn_min_cp,
1487 p_credit_points => l_credit_points ,
1488 p_enrollment_category => l_enr_cat,
1489 p_comm_type => l_enr_comm,
1490 p_method_type => l_enr_meth_type,
1491 p_min_credit_point => l_total_credit_points,
1492 p_calling_obj => 'JOB') THEN
1493
1494 -- validate the holds when droping an unit, pmarada, 2385096
1495
1496 IF NOT IGS_EN_VAL_ENCMB.enrp_val_enr_encmb(p_person_id,
1497 p_program_cd ,
1498 p_load_cal_type,
1499 p_load_sequence_number,
1500 lv_message_name,
1501 lv_message_name2,
1502 lv_return_type,
1503 NULL -- default value, it will be calculated internally based on the census date
1504 ) THEN
1505 l_req_unit_failed := TRUE;
1506 EXIT;
1507 END IF;
1508 -- End of the code added by pmarada, 2385096
1509
1510 ELSE
1511 l_min_cp_failed := TRUE;
1512 END IF; -- ens of p_ovrrd_min_cp_chk
1513
1514 ELSE
1515
1516 --Call enrp_dropall_units
1517 igs_en_gen_004.enrp_dropall_unit(
1518 p_person_id => p_person_id,
1519 p_cal_type => p_load_cal_type,
1520 p_ci_sequence_number => p_load_sequence_number,
1521 p_dcnt_reason_cd => NVL(p_dcnt_reason_cd,l_dcnt_reason_cd),
1522 p_admin_unit_sta => p_admin_unit_status,
1523 p_effective_date => p_effective_date,
1524 p_program_cd => p_program_cd,
1525 p_uoo_id => l_uoo_id,
1526 p_sub_unit => l_sub_unit);
1527
1528 -- validate the holds when droping an unit, pmarada, 2385096
1529
1530 IF NOT IGS_EN_VAL_ENCMB.enrp_val_enr_encmb(p_person_id,
1531 p_program_cd ,
1532 p_load_cal_type,
1533 p_load_sequence_number,
1534 lv_message_name,
1535 lv_message_name2,
1536 lv_return_type,
1537 NULL -- default value, it will be calculated internally based on the census date
1538 ) THEN
1539 l_req_unit_failed := TRUE;
1540 EXIT;
1541
1542 END IF;
1543 -- End of the code added by pmarada, 2385096
1544 END IF;
1545 END IF;
1546
1547 IF p_ovrrd_min_cp_chk = 'Y' OR NOT l_min_cp_failed THEN
1548 IF p_ovrrd_att_typ_chk = 'Y' OR NOT l_att_typ_failed THEN
1549
1550 -- If the Att Type validation step has been setup , continue validating.
1551 IF l_deny_warn_att_type IS NOT NULL THEN
1552 IF NOT (igs_en_elgbl_program.eval_unit_forced_type( p_person_id => p_person_id,
1553 p_load_calendar_type => p_load_cal_type,
1554 p_load_cal_sequence_number => p_load_sequence_number,
1555 p_uoo_id => l_uoo_id ,
1556 p_course_cd => p_program_cd,
1557 p_course_version => p_program_version,
1558 p_message => l_att_type_message,
1559 p_deny_warn => l_deny_warn_att_type ,
1560 p_enrollment_category => l_enr_cat,
1561 p_comm_type => l_enr_comm,
1562 p_method_type => l_enr_meth_type,
1563 p_calling_obj => 'JOB') ) THEN
1564
1565 l_att_typ_failed := TRUE;
1566 END IF ;
1567
1568 END IF ;
1569
1570 END IF; -- p_ovrrd_att_typ_chk = 'Y' OR NOT l_att_typ_failed
1571 END IF; -- p_ovrrd_min_cp_chk = 'Y' OR NOT l_min_cp_failed
1572
1573 IF(instr(l_drop_alluoo_ids,',',1) = 0) THEN
1574 l_drop_alluoo_ids := NULL;
1575 ELSE
1576 l_drop_alluoo_ids := substr(l_drop_alluoo_ids,instr(l_drop_alluoo_ids,',',1)+1);
1577 END IF;
1578
1579 END LOOP; -- End of while loop dropping the selected units
1580 --IF NOT l_all_units_for_drop THEN
1581 IF p_ovrrd_min_cp_chk = 'Y' OR NOT l_min_cp_failed THEN
1582
1583 -- check if any prereq/coreq rules are failing before the drop so that these rule failures
1584 -- can be ignored later.
1585 OPEN c_ref_cur_inst FOR ' SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id
1586 = :1 AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND
1587 uoo_id NOT IN('||p_uoo_ids||')'
1588 USING p_person_id;
1589
1590 LOOP
1591
1592 FETCH c_ref_cur_inst INTO v_ref_cur_rec ;
1593
1594 EXIT WHEN c_ref_cur_inst%NOTFOUND;
1595
1596 IF p_ovrrd_crq_chk = 'N' AND l_deny_warn_coreq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_coreq( --Do a coreq check only if l_deny_warn_coreq is defined
1597 p_person_id => p_person_id,
1598 p_load_cal_type => p_load_cal_type,
1599 p_load_sequence_number => p_load_sequence_number,
1600 p_uoo_id => v_ref_cur_rec.uoo_id,
1601 p_course_cd => p_program_cd,
1602 p_course_version => p_program_version,
1603 p_message => l_message,
1604 p_deny_warn => l_deny_warn_coreq,
1605 p_calling_obj => 'JOB') THEN
1606
1607 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
1608 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
1609 -- if the unit has failed the coreq rule before drop operation, dont consider the rule to have
1610 -- failed due to the drop.
1611 IF (l_coreq_failed_uoo_ids IS NULL OR INSTR(l_coreq_failed_uoo_ids,TO_CHAR(v_ref_cur_rec.uoo_id)) = 0) THEN
1612 IF (l_failed_unit_codes IS NULL OR INSTR(l_failed_unit_codes,v_ref_cur_rec.unit_cd ) = 0) THEN
1613 l_failed_unit_codes := l_failed_unit_codes || ',' || v_ref_cur_rec.unit_cd;
1614 END IF;
1615 l_failed_uoo_ids := l_failed_uoo_ids ||','|| TO_CHAR(v_ref_cur_rec.uoo_id);
1616 l_coreq_failed := TRUE;
1617 END IF ;
1618
1619
1620 ELSE
1621 -- Since the coreq rule has passed, store these passed uoo_ids
1622 -- into a temp table so that the prereq check can be run for these uoo_ids
1623 -- instead of opening the cursor again
1624
1625 lData(t1_idx).uoo_id := v_ref_cur_rec.uoo_id;
1626 lData(t1_idx).unit_cd := v_ref_cur_rec.unit_cd;
1627 t1_idx :=t1_idx+1;
1628
1629 END IF;
1630 END LOOP;
1631 ClOSE c_ref_cur_inst;
1632
1633 -- Instead of opening the cursor again, we are storing the passed uoo_ids
1634 -- values into a Table so that a prereq check can now be run since
1635 -- the min_cp and coreq checks have passed
1636 IF (p_ovrrd_min_cp_chk = 'Y' OR NOT l_min_cp_failed) AND NOT l_coreq_failed THEN
1637 -- Run prereq check since coreq has passed
1638 -- Check whether records exist in the temparary table,lDATA.
1639 IF lData.COUNT > 0 THEN
1640 FOR i IN lData.FIRST .. lData.LAST LOOP
1641 IF p_ovrrd_prq_chk = 'N' AND l_deny_warn_prereq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_prereq( --Do a coreq check only if l_deny_warn_prereq is defined
1642 p_person_id => p_person_id,
1643 p_load_cal_type => p_load_cal_type,
1644 p_load_sequence_number => p_load_sequence_number,
1645 p_uoo_id => lData(i).uoo_id,
1646 p_course_cd => p_program_cd,
1647 p_course_version => p_program_version,
1648 p_message => l_message,
1649 p_deny_warn => l_deny_warn_prereq,
1650 p_calling_obj => 'JOB') THEN
1651
1652 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
1653 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
1654 -- if the unit has failed the prereq rule before drop operation, dont consider the rule to have
1655 -- failed due to the drop.
1656 IF (l_prereq_failed_uoo_ids IS NULL OR INSTR(l_prereq_failed_uoo_ids,TO_CHAR(lData(i).uoo_id) ) = 0) THEN
1657 IF (l_failed_unit_codes IS NULL OR INSTR(l_failed_unit_codes,lData(i).unit_cd ) = 0) THEN
1658 l_failed_unit_codes := l_failed_unit_codes || ',' || lData(i).unit_cd;
1659 END IF;
1660 l_failed_uoo_ids := l_failed_uoo_ids ||','|| TO_CHAR(lData(i).uoo_id);
1661 l_prereq_failed := TRUE;
1662 END IF ;
1663
1664 END IF;
1665 END LOOP;
1666 END IF;
1667
1668 END IF;
1669 END IF;
1670
1671 --Format the returned fields for redundant commas etc.
1672 IF INSTR(l_failed_unit_codes,',') = 1 THEN
1673 l_failed_unit_codes := SUBSTR(l_failed_unit_codes,2);
1674 END IF;
1675
1676 IF INSTR(l_failed_uoo_ids,',') = 1 THEN
1677 l_failed_uoo_ids := SUBSTR(l_failed_uoo_ids,2);
1678 END IF;
1679
1680 IF INSTR(l_failed_unit_codes,',') = length(l_failed_unit_codes) THEN
1681 l_failed_unit_codes := substr(l_failed_unit_codes,0,length(l_failed_unit_codes)-1) ;
1682 END IF;
1683
1684 IF INSTR(l_failed_uoo_ids,',') = length(l_failed_uoo_ids) THEN
1685 l_failed_uoo_ids := substr(l_failed_uoo_ids,0,length(l_failed_uoo_ids)-1) ;
1686 END IF;
1687
1688 END IF; -- end of l_all_units_for_drop comparison
1689
1690 --Return the status and error msg to calling procedure
1691 IF l_coreq_failed THEN
1692
1693 p_return_status := 'FALSE';
1694 IF l_deny_warn_coreq = 'WARN' THEN
1695 p_message := 'IGS_SS_EN_CRQ_DRP_WARN';
1696 ELSE
1697 p_message := 'IGS_SS_EN_CRQ_DRP_DENY';
1698 p_failed_uoo_ids := l_failed_uoo_ids;
1699 END IF;
1700 p_failed_unit_cds := l_failed_unit_codes;
1701
1702
1703 ELSIF p_ovrrd_min_cp_chk = 'N' AND l_min_cp_failed THEN
1704 p_return_status := 'FALSE';
1705 -- set the appropriate message name based on the message returned from eval_min_cp call.
1706 -- if WARN message is returned
1707
1708 IF l_min_cp_message = 'IGS_SS_WARN_MIN_CP_REACHED' THEN
1709 p_message := 'IGS_SS_EN_MIN_CP_WARN';
1710 ELSE
1711 p_message := 'IGS_SS_EN_MIN_CP_DENY';
1712 END IF;
1713 ELSIF p_ovrrd_att_typ_chk = 'N' AND l_att_typ_failed THEN
1714 p_return_status := 'FALSE';
1715
1716 -- set the appropriate message name based on the message returned from call.
1717 -- if WARN message is returned
1718 IF l_att_type_message = 'IGS_SS_WARN_ATTYPE_CHK' THEN
1719 p_message := 'IGS_SS_EN_ATT_TYP_WARN';
1720 ELSE
1721 p_message := 'IGS_SS_EN_ATT_TYP_DENY';
1722 END IF;
1723
1724 ELSIF l_prereq_failed THEN
1725 p_return_status := 'FALSE';
1726
1727 IF l_deny_warn_prereq = 'WARN' THEN
1728 p_message := 'IGS_SS_EN_PRQ_DRP_WARN';
1729 ELSE
1730 p_message := 'IGS_SS_EN_PRQ_DRP_DENY';
1731 p_failed_uoo_ids := l_failed_uoo_ids ;
1732 END IF;
1733
1734 p_failed_unit_cds := l_failed_unit_codes;
1735
1736 ELSIF l_req_unit_failed THEN -- for required units message handing, pmarada
1737 IF lv_message_name2 = 'IGS_EN_PRSN_NOTENR_REQUIRE' THEN
1738 lv_message_name2 := 'IGS_EN_REQ_UNIT_CANNOT_DROP';
1739 END IF;
1740 IF lv_message_name = 'IGS_EN_PRSN_NOTENR_REQUIRE' THEN
1741 lv_message_name := 'IGS_EN_REQ_UNIT_CANNOT_DROP';
1742 END IF;
1743 p_return_status := 'FALSE';
1744 IF lv_message_name IS NOT NULL
1745 AND INSTR(NVL(p_message,' '),lv_message_name) = 0
1746 AND lv_return_type ='E' THEN
1747 p_message := lv_message_name;
1748 END IF;
1749 IF lv_message_name2 IS NOT NULL
1750 AND INSTR(NVL(p_message,' '),lv_message_name2) = 0
1751 AND lv_return_type ='E' THEN
1752 p_message := lv_message_name2;
1753 END IF;
1754 ELSE
1755 p_return_status := 'TRUE';
1756 END IF;
1757
1758 EXCEPTION
1759 WHEN NO_AUSL_RECORD_FOUND THEN
1760 p_message := 'IGS_EN_AUS_NOT_DEFINED';
1761 p_return_status := 'FALSE';
1762 RETURN;
1763 END drop_selected_units;
1764
1765 --BEGIN DROP ALL UNITS CODE
1766 -- Added the following two parameters p_reason, p_source_of_drop
1767 -- as part of Drop/ Transfer Workflow Notification DLD.
1768 -- pradhakr; 30-Sep-2002; Bug# 2599925.
1769 PROCEDURE drop_all_workflow (
1770 p_uoo_ids IN VARCHAR2,
1771 p_person_id IN NUMBER,
1772 p_load_cal_type IN VARCHAR2,
1773 p_load_sequence_number IN NUMBER,
1774 p_program_cd IN VARCHAR2,
1775 p_return_status OUT NOCOPY VARCHAR2,
1776 p_drop_date IN DATE DEFAULT NULL,
1777 p_old_cp IN NUMBER DEFAULT NULL,
1778 p_new_cp IN NUMBER DEFAULT NULL
1779 ) AS
1780 ------------------------------------------------------------------------------
1781 --Created by :
1782 --Date created:
1783 --
1784 -- Purpose:
1785
1786 -- Known limitations/enhancements and/or remarks:
1787 --
1788 -- Change History:
1789 -- Who When What
1790 -- kkillams 13-OCT-2003 Three new paramters are added to the procedure w.r.t bug#3160856
1791 ------------------------------------------------------------------------------
1792
1793 CURSOR cur_seq_val
1794 IS
1795 SELECT igs_en_status_mail_req_s.nextval seq_val
1796 FROM DUAL;
1797
1798 CURSOR cur_user_name IS SELECT user_name FROM fnd_user WHERE user_id = fnd_global.user_id;
1799
1800 CURSOR cur_cal_desc IS SELECT description FROM igs_ca_inst WHERE cal_type = p_load_cal_type
1801 AND sequence_number = p_load_sequence_number;
1802
1803 l_cur_seq_val cur_seq_val%ROWTYPE;
1804 l_wf_parameter_list_t WF_PARAMETER_LIST_T := wf_parameter_list_t();
1805 l_wf_installed fnd_lookups.lookup_code%TYPE;
1806 l_wf_role VARCHAR2(100);
1807 l_cal_desc IGS_CA_INST.DESCRIPTION%TYPE;
1808
1809 BEGIN
1810 -- get the profile value that is set for checking if workflow is installed
1811 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
1812
1813 -- if workflow is installed then carry on with the raising an event
1814 IF (RTRIM(l_wf_installed) ='Y') THEN
1815 -- get the next value of the sequence
1816 OPEN cur_seq_val;
1817 FETCH cur_seq_val INTO l_cur_seq_val;
1818 CLOSE cur_seq_val;
1819
1820 --Get the login user name.
1821 OPEN cur_user_name;
1822 FETCH cur_user_name INTO l_wf_role;
1823 CLOSE cur_user_name;
1824
1825 --Get load calendar descriptin.
1826 OPEN cur_cal_desc;
1827 FETCH cur_cal_desc INTO l_cal_desc;
1828 CLOSE cur_cal_desc;
1829 -- set the event parameters
1830 wf_event.addparametertolist(P_NAME => 'STUDENT_ID' ,P_VALUE => p_person_id ,P_PARAMETERLIST => l_wf_parameter_list_t);
1831 wf_event.addparametertolist(P_NAME => 'UOO_IDS' ,P_VALUE => p_uoo_ids ,P_PARAMETERLIST => l_wf_parameter_list_t);
1832 wf_event.addparametertolist(P_NAME => 'CAL_TYPE' ,P_VALUE => p_load_cal_type ,P_PARAMETERLIST => l_wf_parameter_list_t);
1833 wf_event.addparametertolist(P_NAME => 'CAL_SEQUENCE_NUMBER' ,P_VALUE => p_load_sequence_number ,P_PARAMETERLIST => l_wf_parameter_list_t);
1834 wf_event.addparametertolist(P_NAME => 'PROGRAM_CD' ,P_VALUE => p_program_cd ,P_PARAMETERLIST => l_wf_parameter_list_t);
1835 wf_event.addparametertolist(P_NAME => 'DROP_DATE' ,P_VALUE => p_drop_date ,P_PARAMETERLIST => l_wf_parameter_list_t);
1836 wf_event.addparametertolist(P_NAME => 'OLD_CP' ,P_VALUE => NVL(p_old_cp,0) ,P_PARAMETERLIST => l_wf_parameter_list_t);
1837 wf_event.addparametertolist(P_NAME => 'NEW_CP' ,P_VALUE => NVL(p_new_cp,0) ,P_PARAMETERLIST => l_wf_parameter_list_t);
1838 wf_event.addparametertolist(P_NAME => 'P_WF_ROLE' ,P_VALUE => l_wf_role ,P_PARAMETERLIST => l_wf_parameter_list_t);
1839 wf_event.addparametertolist(P_NAME => 'CAL_DESC' ,P_VALUE => l_cal_desc ,P_PARAMETERLIST => l_wf_parameter_list_t);
1840 wf_event.addparametertolist(P_NAME => 'REASON' ,P_VALUE => igs_en_su_attempt_pkg.pkg_reason ,P_PARAMETERLIST => l_wf_parameter_list_t);
1841 wf_event.addparametertolist(P_NAME => 'SOURCE_OF_DROP' ,P_VALUE => igs_en_su_attempt_pkg.pkg_source_of_drop ,P_PARAMETERLIST => l_wf_parameter_list_t);
1842
1843 -- raise the event
1844 WF_EVENT.RAISE(p_event_name=>'oracle.apps.igs.en.dropnotification',
1845 p_event_key =>'oracle.apps.igs.en.dropnotification'||l_cur_seq_val.seq_val,
1846 p_event_data=>NULL,
1847 p_parameters=>l_wf_parameter_list_t);
1848 END IF;
1849 p_return_status := 'TRUE';
1850 END drop_all_workflow;
1851
1852
1853 PROCEDURE transfer_workflow (
1854 p_source_uoo_ids IN VARCHAR2,
1855 p_dest_uoo_ids IN VARCHAR2,
1856 p_person_id IN NUMBER,
1857 p_load_cal_type IN VARCHAR2,
1858 p_load_sequence_number IN NUMBER,
1859 p_program_cd IN VARCHAR2,
1860 p_unit_attempt_status IN VARCHAR2,
1861 p_reason IN VARCHAR2,
1862 p_return_status OUT NOCOPY VARCHAR2,
1863 p_message OUT NOCOPY VARCHAR2
1864 ) AS
1865 ------------------------------------------------------------------------------------
1866 --Created by : pradhakr
1867 --Date created: 30-Sep-2002
1868 --
1869 -- Purpose: Whenever there is a transfer of students from one unit section to another
1870 -- this procedure is called which raises a business event.
1871 -- Added as part of Drop / Transfer Workflow Notification DLD.
1872 -- Bug# 2599925.
1873 -- Known limitations/enhancements and/or remarks:
1874 --
1875 -- Change History:
1876 -- Who When What
1877 --
1878 ------------------------------------------------------------------------------
1879
1880 CURSOR cur_seq_val
1881 IS
1882 SELECT igs_en_wf_trans_notif_s.nextval seq_val
1883 FROM DUAL;
1884
1885 l_cur_seq_val cur_seq_val%ROWTYPE;
1886 l_wf_parameter_list_t WF_PARAMETER_LIST_T:=wf_parameter_list_t();
1887 l_wf_installed fnd_lookups.lookup_code%TYPE;
1888 BEGIN
1889 -- get the profile value that is set for checking if workflow is installed
1890 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
1891
1892 -- if workflow is installed then carry on with the raising an event
1893 IF (RTRIM(l_wf_installed) = 'Y' ) THEN
1894
1895 OPEN cur_seq_val;
1896 FETCH cur_seq_val INTO l_cur_seq_val;
1897 CLOSE cur_seq_val;
1898
1899 -- set the event parameters
1900 wf_event.addparametertolist(p_Name=>'SOURCE_UOO_IDS' ,p_Value=>p_source_uoo_ids ,p_Parameterlist =>l_wf_parameter_list_t);
1901 wf_event.addparametertolist(p_Name=>'DEST_UOO_IDS' ,p_Value=>p_dest_uoo_ids ,p_Parameterlist =>l_wf_parameter_list_t);
1902 wf_event.addparametertolist(p_Name=>'STUDENT_ID' ,p_Value=>p_person_id ,p_Parameterlist =>l_wf_parameter_list_t);
1903 wf_event.addparametertolist(p_Name=>'CAL_TYPE' ,p_Value=>p_load_cal_type ,p_Parameterlist =>l_wf_parameter_list_t);
1904 wf_event.addparametertolist(p_Name=>'CAL_SEQUENCE_NUMBER' ,p_Value=>p_load_sequence_number ,p_Parameterlist =>l_wf_parameter_list_t);
1905 wf_event.addparametertolist(p_Name=>'PROGRAM_CD' ,p_Value=>p_program_cd ,p_Parameterlist =>l_wf_parameter_list_t);
1906 wf_event.addparametertolist(p_Name=>'UNIT_ATTEMPT_STATUS' ,p_Value=>p_unit_attempt_status ,p_Parameterlist =>l_wf_parameter_list_t);
1907 wf_event.addparametertolist(p_Name=>'REASON' ,p_Value=>p_reason ,p_Parameterlist =>l_wf_parameter_list_t);
1908
1909 -- raise the event
1910 WF_EVENT.RAISE(p_event_name=>'oracle.apps.igs.en.transfernotification',
1911 p_event_key =>'oracle.apps.igs.en.transfernotification'||l_cur_seq_val.seq_val,
1912 p_event_data=>NULL,
1913 p_parameters=>l_wf_parameter_list_t);
1914 END IF;
1915 p_return_status := 'TRUE';
1916 END transfer_workflow;
1917
1918 FUNCTION enr_val_grad_usec
1919 (
1920 p_uoo_ids IN VARCHAR2,
1921 p_grading_schema_code IN VARCHAR2,
1922 p_gs_version_number IN NUMBER
1923 ) RETURN BOOLEAN
1924 IS
1925
1926 ------------------------------------------------------------------------------------
1927 --Created by : pradhakr
1928 --Date created: 30-Sep-2002
1929 --
1930 -- Purpose: Procedure to check whether Grading Schema exists in the Unit Section Level /
1931 -- Unit level. Added as part of Drop / Transfer Workflow Notification DLD.
1932 -- Bug# 2599925.
1933 -- Known limitations/enhancements and/or remarks:
1934 --
1935 -- Change History:
1936 -- Who When What
1937 --
1938 ------------------------------------------------------------------------------------
1939
1940 -- Cursor to check the existance of grading schema in Unit Section level.
1941 CURSOR c_grad_schema IS
1942 SELECT grading_schema_code, grd_schm_version_number
1943 FROM igs_ps_usec_grd_schm
1944 WHERE uoo_id = p_uoo_ids;
1945
1946 -- Cursor to check the existance of grading schema in Unit level.
1947 CURSOR c_grad_schema_cd(l_unit_cd VARCHAR2, l_unit_version NUMBER) IS
1948 SELECT grading_schema_code, grd_schm_version_number
1949 FROM igs_ps_unit_grd_schm
1950 WHERE unit_code = l_unit_cd
1951 AND unit_version_number = l_unit_version ;
1952
1953 CURSOR c_unit_cd IS
1954 SELECT unit_cd, version_number
1955 FROM igs_ps_unit_ofr_opt
1956 WHERE uoo_id = p_uoo_ids ;
1957
1958 l_unit_cd c_unit_cd%ROWTYPE;
1959 l_grad_schema c_grad_schema%ROWTYPE;
1960 l_grad_schema_cd c_grad_schema_cd%ROWTYPE;
1961
1962
1963 BEGIN
1964
1965 OPEN c_grad_schema;
1966 FETCH c_grad_schema INTO l_grad_schema;
1967
1968 -- Check whether the grading schema exists in Unit Section level. If exists,
1969 -- return True.
1970 IF c_grad_schema%FOUND THEN
1971 LOOP
1972 IF (p_grading_schema_code = l_grad_schema.grading_schema_code AND
1973 p_gs_version_number = l_grad_schema.grd_schm_version_number) THEN
1974 CLOSE c_grad_schema;
1975 RETURN TRUE;
1976 ELSE
1977 FETCH c_grad_schema INTO l_grad_schema;
1978 EXIT WHEN c_grad_schema%NOTFOUND;
1979 END IF;
1980 END LOOP;
1981 CLOSE c_grad_schema;
1982 RETURN FALSE;
1983 ELSE
1984 CLOSE c_grad_schema;
1985 -- Get the unit code and version number for the passed uoo_id.
1986 OPEN c_unit_cd;
1987 FETCH c_unit_cd INTO l_unit_cd;
1988 CLOSE c_unit_cd;
1989
1990 OPEN c_grad_schema_cd (l_unit_cd.unit_cd, l_unit_cd.version_number);
1991 FETCH c_grad_schema_cd INTO l_grad_schema_cd;
1992
1993 -- Check whether the grading schema exists in Unit level.
1994 IF c_grad_schema_cd%FOUND THEN
1995 LOOP
1996 IF (p_grading_schema_code = l_grad_schema_cd.grading_schema_code AND
1997 p_gs_version_number = l_grad_schema_cd.grd_schm_version_number) THEN
1998 CLOSE c_grad_schema_cd;
1999 RETURN TRUE;
2000 ELSE
2001 FETCH c_grad_schema_cd INTO l_grad_schema_cd;
2002 EXIT WHEN c_grad_schema_cd%NOTFOUND;
2003 END IF;
2004 END LOOP;
2005 CLOSE c_grad_schema_cd;
2006 RETURN FALSE;
2007 ELSE
2008 CLOSE c_grad_schema_cd;
2009 RETURN FALSE;
2010 END IF;
2011 RETURN FALSE;
2012 END IF;
2013
2014 END enr_val_grad_usec;
2015
2016 --Call from SS to valdiate if min or max cp has been breached
2017 PROCEDURE validate_upd_cp(
2018 x_person_id IN NUMBER,
2019 x_person_type IN VARCHAR2,
2020 x_load_cal_type IN VARCHAR2,
2021 x_load_sequence_number IN NUMBER,
2022 x_uoo_id IN NUMBER,
2023 x_program_cd IN VARCHAR2,
2024 x_program_version IN NUMBER,
2025 x_override_enrolled_cp IN NUMBER,
2026 x_message OUT NOCOPY VARCHAR2,
2027 x_return_status OUT NOCOPY VARCHAR2
2028 ) AS
2029 -- Change History
2030 -- Who When What
2031 -- rvivekan 18-jun-2003 Reenrollment and repeat build #2881363 added
2032 -- reenroll step to validate_upd_cp
2033 -- smanglm 03-02-2003 call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
2034 -- amuthu 20-JAN-2003 moved the savepoint to the begining of the procedure
2035 -- svenkata 7-Jan-03 Incorporated the logic for 'When first Reach Attendance Type'. The routine enrp_val_coo_att is being called to get the
2036 -- Att Typ before updating the CP.The CP is updated and the routine eval_unit_forced_type is called to evaluate. A call to
2037 -- SUA Update row is made intentinally 'cos the post validation cannot be carried out in the SS screen from where this routine is called.Bug#2737263
2038 -- svenkata 20-Dec-02 Incorporated Att Type validation when updating the Credit Points for a Student Unit section.Bug# 2686793
2039 --Nishikant 01-NOV-2002 SEVIS Build Bug#2641905. parameters p_person_id and p_message added in the calls
2040 -- get_notification.
2041 --svenkata 21-oct-02 Bug 2616692 - The call to the fucntion eval_min_cp has been modified to add
2042 -- 4 new parameters .
2043 --kkillams 27-Mar-03 Modified cur_su Cursor, replaced * with relevant columns w.r.t. bug 2749648
2044 --svenkata 6-Jun-2003 Added new validations for checking Cross element Restrictions. The validations Cross location, cross faculty amd cross mode are introduced
2045 -- as part of deny/ warn build. Bug 2829272.Modifications have been made in such a way that only DENY OR only WARN messages will be returned.
2046 --myoganat 16-Jun-2003 Removed the reference to profile IGS_EN_INCL_AUDIT_CP.
2047 --sarakshi 27-Jun-2003 Enh#2930935,added p_uoo_id parameter to IGS_EN_PRC_LOAD.ENRP_CLC_SUA_LOAD
2048 --vkarthik 22-Jul-2004 Added three dummy variables l_audit_cp, l_billing_cp, l_enrolled_cp for all the calls to
2049 -- igs_en_prc_load.enrp_clc_sua_load towards EN308 Billable credit points build Enh#3782329
2050
2051 CURSOR cur_su IS
2052 SELECT unit_cd,
2053 version_number,
2054 cal_type,
2055 ci_sequence_number,
2056 discontinued_dt,
2057 administrative_unit_status ,
2058 unit_attempt_status,
2059 no_assessment_ind
2060 FROM igs_en_su_attempt
2061 WHERE person_id = x_person_id AND
2062 course_cd = x_program_cd AND
2063 uoo_id = x_uoo_id;
2064
2065 -- Cursor to get the coo_id of the student.
2066 CURSOR cur_coo_id IS
2067 SELECT coo_id coo_id
2068 FROM igs_en_stdnt_ps_att
2069 WHERE person_id = x_person_id
2070 AND course_cd = x_program_cd;
2071
2072 -- Cursor to get the Unit Attempt Details .
2073 CURSOR get_sua_dtls IS
2074 SELECT sua.rowid , sua.*
2075 FROM igs_en_su_attempt_all sua
2076 WHERE person_id = x_person_id AND
2077 course_cd = x_program_cd AND
2078 uoo_id = x_uoo_id FOR UPDATE NOWAIT;
2079
2080 -- Cursor to get the assessment indicator value.
2081 CURSOR c_assessment IS
2082 SELECT no_assessment_ind
2083 FROM igs_en_su_attempt
2084 WHERE person_id = x_person_id
2085 AND course_cd = x_program_cd
2086 AND uoo_id = x_uoo_id;
2087
2088 l_sua_dtls_rec get_sua_dtls%ROWTYPE;
2089 l_attendance_type_reach BOOLEAN := TRUE;
2090 l_cur_coo_id cur_coo_id%ROWTYPE;
2091 l_attendance_types VARCHAR2(100); -- As returned from the function igs_en_val_sca.enrp_val_coo_att
2092
2093 l_deny_warn_min_cp VARCHAR2(10) DEFAULT NULL;
2094 l_deny_warn_max_cp VARCHAR2(10) DEFAULT NULL;
2095 l_deny_warn_cross_loc VARCHAR2(10) DEFAULT NULL;
2096 l_deny_warn_cross_fac VARCHAR2(10) DEFAULT NULL;
2097 l_deny_warn_cross_mod VARCHAR2(10) DEFAULT NULL;
2098 l_deny_warn_att_type VARCHAR2(100) DEFAULT NULL;
2099 l_deny_warn_reenroll VARCHAR2(100) DEFAULT NULL;
2100
2101 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
2102 l_enr_cal_type VARCHAR2(20);
2103 l_enr_ci_seq NUMBER(20);
2104 l_enr_cat VARCHAR2(20);
2105 l_enr_comm VARCHAR2(2000);
2106
2107 l_calc_cp igs_en_su_attempt.override_enrolled_cp%TYPE;
2108 l_calc_min_cp igs_en_su_attempt.override_enrolled_cp%TYPE;
2109 l_eftsu_total igs_en_su_attempt.override_enrolled_cp%TYPE;
2110
2111 l_su_rec cur_su%ROWTYPE;
2112 l_total_credit_points igs_en_su_attempt.override_enrolled_cp%TYPE := NULL ;
2113
2114 l_enr_incurred_cp NUMBER DEFAULT 0;
2115 l_over_incurred_cp NUMBER DEFAULT 0;
2116 l_current_cp NUMBER DEFAULT 0;
2117 l_dummy NUMBER;
2118 l_acad_cal_type igs_ca_inst.cal_type%type;
2119 l_acad_ci_sequence_number igs_ca_inst.sequence_number%type;
2120 l_acad_start_dt igs_ca_inst.start_dt%type;
2121 l_acad_end_dt igs_ca_inst.end_dt%type;
2122 l_alternate_code igs_ca_inst.alternate_code%type;
2123 l_acad_message varchar2(100);
2124 l_message VARCHAR2(1200);
2125 l_return_status VARCHAR2(10);
2126 l_dummy1 VARCHAR2(200);
2127 --dummy variables to pick up audit, billing, enrolled credit points
2128 --due to signature change by EN308 Billing credit hours Bug 3782329
2129 l_audit_cp IGS_PS_USEC_CPS.billing_credit_points%TYPE;
2130 l_billing_cp IGS_PS_USEC_CPS.billing_hrs%TYPE;
2131 l_enrolled_cp IGS_PS_UNIT_VER.enrolled_credit_points%TYPE;
2132
2133 BEGIN
2134
2135 SAVEPOINT upd_sua_cp;
2136
2137
2138 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
2139 igs_en_gen_017.enrp_get_enr_method(
2140 p_enr_method_type => l_enr_meth_type,
2141 p_error_message => l_message,
2142 p_ret_status => l_return_status);
2143
2144 OPEN cur_su;
2145 FETCH cur_su INTO l_su_rec;
2146 CLOSE cur_su ;
2147
2148 -- added below logic to get the Academic Calendar which is used by method enrp_get_enr_cat
2149 --
2150 -- get the academic calendar of the given Load Calendar
2151 --
2152 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
2153 p_cal_type => x_load_cal_type,
2154 p_ci_sequence_number => x_load_sequence_number,
2155 p_acad_cal_type => l_acad_cal_type,
2156 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
2157 p_acad_ci_start_dt => l_acad_start_dt,
2158 p_acad_ci_end_dt => l_acad_end_dt,
2159 p_message_name => l_acad_message );
2160
2161 IF l_acad_message IS NOT NULL THEN
2162 x_message := l_acad_message;
2163 x_return_status := 'DENY';
2164 END IF;
2165
2166 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat(
2167 x_person_id, x_program_cd,
2168 l_acad_cal_type, l_acad_ci_sequence_number,
2169 NULL, l_enr_cal_type,
2170 l_enr_ci_seq, l_enr_comm,
2171 l_dummy1);
2172
2173 IF l_enr_comm = 'BOTH' THEN
2174 l_enr_comm :='ALL';
2175 END IF;
2176 --
2177 -- Below code is added as part of Bug 2401891
2178 -- Checking Load Incurred as user can re-instate a discontinued unit and change CP in one transaction
2179 --
2180 -- Getting the current cp and passing this against parameter p_override_enrolled_cp to get Incurred CP.
2181 -- (As the current CP can be from igs_ps_unit_ver or from igs_en_su_attempt)
2182 l_current_cp := igs_ss_enr_details.get_credit_points(x_person_id,x_uoo_id,l_su_rec.unit_cd,l_su_rec.version_number,x_program_cd);
2183 IF Igs_En_Prc_Load.ENRP_GET_LOAD_INCUR(
2184 l_su_rec.cal_type,
2185 l_su_rec.ci_sequence_number,
2186 l_su_rec.discontinued_dt,
2187 l_su_rec.administrative_unit_status ,
2188 l_su_rec.unit_attempt_status,
2189 l_su_rec.no_assessment_ind,
2190 x_load_cal_type,
2191 x_load_sequence_number,
2192 -- anilk, Audit special fee build
2193 NULL, -- for p_uoo_id
2194 'N') = 'Y' THEN
2195 -- calculate CP incurred in the given Load calendar for Enrolled credit points.
2196
2197 l_enr_incurred_cp := Igs_En_Prc_Load.enrp_clc_sua_load(
2198 p_unit_cd => l_su_rec.unit_cd,
2199 p_version_number => l_su_rec.version_number,
2200 p_cal_type => l_su_rec.cal_type,
2201 p_ci_sequence_number => l_su_rec.ci_sequence_number,
2202 p_load_cal_type => x_load_cal_type,
2203 p_load_ci_sequence_number => x_load_sequence_number,
2204 p_override_enrolled_cp => l_current_cp,
2205 p_override_eftsu => NULL,
2206 p_return_eftsu => l_dummy1,
2207 p_uoo_id => x_uoo_id,
2208 -- anilk, Audit special fee build
2209 p_include_as_audit => 'N',
2210 p_audit_cp => l_audit_cp,
2211 p_billing_cp => l_billing_cp,
2212 p_enrolled_cp => l_enrolled_cp);
2213
2214 END IF;
2215 --
2216 -- calculate CP incurred in the given Load calendar for Override Enrolled credit points.
2217 l_over_incurred_cp := Igs_En_Prc_Load.enrp_clc_sua_load(
2218 p_unit_cd => l_su_rec.unit_cd,
2219 p_version_number => l_su_rec.version_number,
2220 p_cal_type => l_su_rec.cal_type,
2221 p_ci_sequence_number => l_su_rec.ci_sequence_number,
2222 p_load_cal_type => x_load_cal_type,
2223 p_load_ci_sequence_number => x_load_sequence_number,
2224 p_override_enrolled_cp => x_override_enrolled_cp,
2225 p_override_eftsu => NULL,
2226 p_return_eftsu => l_dummy1,
2227 p_uoo_id =>x_uoo_id,
2228 -- anilk, Audit special fee build
2229 p_include_as_audit => 'N',
2230 p_audit_cp => l_audit_cp,
2231 p_billing_cp => l_billing_cp,
2232 p_enrolled_cp => l_enrolled_cp);
2233 --
2234 -- Summing the incurred override CP and negative value of incurred current CP
2235 -- which is passed to Min and Max CP validations.
2236 l_calc_cp := l_over_incurred_cp - l_enr_incurred_cp;
2237
2238 l_message := NULL;
2239 l_deny_warn_max_cp := igs_ss_enr_details.get_notification(
2240 p_person_type => x_person_type,
2241 p_enrollment_category => l_enr_cat,
2242 p_comm_type => l_enr_comm,
2243 p_enr_method_type => l_enr_meth_type,
2244 p_step_group_type => 'PROGRAM',
2245 p_step_type => 'FMAX_CRDT',
2246 p_person_id => x_person_id,
2247 p_message => l_message
2248 ) ;
2249 IF l_message IS NOT NULL THEN
2250 x_message := l_message;
2251 x_return_status := 'DENY';
2252 RETURN;
2253 END IF;
2254 --------------------------------------------------------------------------------------------------------------------------------------------
2255 l_deny_warn_reenroll := igs_ss_enr_details.get_notification(
2256 p_person_type => x_person_type,
2257 p_enrollment_category => l_enr_cat,
2258 p_comm_type => l_enr_comm,
2259 p_enr_method_type => l_enr_meth_type,
2260 p_step_group_type => 'UNIT',
2261 p_step_type => 'REENROLL',
2262 p_person_id => x_person_id,
2263 p_message => l_message
2264 ) ;
2265 IF l_message IS NOT NULL THEN
2266 x_message := l_message;
2267 x_return_status := 'DENY';
2268 RETURN;
2269 END IF;
2270
2271 l_deny_warn_min_cp := igs_ss_enr_details.get_notification(
2272 p_person_type => x_person_type,
2273 p_enrollment_category => l_enr_cat,
2274 p_comm_type => l_enr_comm,
2275 p_enr_method_type => l_enr_meth_type,
2276 p_step_group_type => 'PROGRAM',
2277 p_step_type => 'FMIN_CRDT',
2278 p_person_id => x_person_id,
2279 p_message => l_message
2280 ) ;
2281 IF l_message IS NOT NULL THEN
2282 x_message := l_message;
2283 x_return_status := 'DENY';
2284 RETURN;
2285 END IF;
2286
2287 l_deny_warn_att_type := igs_ss_enr_details.get_notification(
2288 p_person_type => x_person_type,
2289 p_enrollment_category => l_enr_cat,
2290 p_comm_type => l_enr_comm,
2291 p_enr_method_type => l_enr_meth_type,
2292 p_step_group_type => 'PROGRAM',
2293 p_step_type => 'FATD_TYPE',
2294 p_person_id => x_person_id,
2295 p_message => l_message
2296 ) ;
2297
2298 IF l_message IS NOT NULL THEN
2299 x_message := l_message;
2300 x_return_status := 'DENY';
2301 RETURN;
2302 END IF;
2303
2304 l_deny_warn_cross_loc := igs_ss_enr_details.get_notification(
2305 p_person_type => x_person_type,
2306 p_enrollment_category => l_enr_cat,
2307 p_comm_type => l_enr_comm,
2308 p_enr_method_type => l_enr_meth_type,
2309 p_step_group_type => 'PROGRAM',
2310 p_step_type => 'CROSS_LOC',
2311 p_person_id => x_person_id,
2312 p_message => l_message
2313 ) ;
2314
2315 IF l_message IS NOT NULL THEN
2316 x_message := l_message;
2317 x_return_status := 'DENY';
2318 RETURN;
2319 END IF;
2320
2321 l_deny_warn_cross_mod := igs_ss_enr_details.get_notification(
2322 p_person_type => x_person_type,
2323 p_enrollment_category => l_enr_cat,
2324 p_comm_type => l_enr_comm,
2325 p_enr_method_type => l_enr_meth_type,
2326 p_step_group_type => 'PROGRAM',
2327 p_step_type => 'CROSS_MOD',
2328 p_person_id => x_person_id,
2329 p_message => l_message
2330 ) ;
2331
2332 IF l_message IS NOT NULL THEN
2333 x_message := l_message;
2334 x_return_status := 'DENY';
2335 RETURN;
2336 END IF;
2337
2338 l_deny_warn_cross_fac := igs_ss_enr_details.get_notification(
2339 p_person_type => x_person_type,
2340 p_enrollment_category => l_enr_cat,
2341 p_comm_type => l_enr_comm,
2342 p_enr_method_type => l_enr_meth_type,
2343 p_step_group_type => 'PROGRAM',
2344 p_step_type => 'CROSS_FAC',
2345 p_person_id => x_person_id,
2346 p_message => l_message
2347 ) ;
2348
2349 IF l_message IS NOT NULL THEN
2350 x_message := l_message;
2351 x_return_status := 'DENY';
2352 RETURN;
2353 END IF;
2354
2355 -- A call to igs_en_prc_load.enrp_clc_eftsu_total as part of- Enrollment Eligibility and validations .The Total enrolled CP
2356 -- of the student has to be determined before the credit points of the unit is changed.The Credit Points of the unit is then
2357 -- changed, and eval_min_cp is called with the value l_total_credit_points passed to the parameter l_total_enrolled_cp.The
2358 -- value of l_total_enrolled_cp is essential to determine if the Min Credit Points is already reached by the student before
2359 -- the Credit points are changed.
2360
2361 IF l_deny_warn_min_cp ='DENY' THEN
2362
2363 l_eftsu_total := igs_en_prc_load.enrp_clc_eftsu_total(
2364 p_person_id => x_person_id,
2365 p_course_cd => x_program_cd ,
2366 p_acad_cal_type => l_acad_cal_type,
2367 p_acad_sequence_number => l_acad_ci_sequence_number,
2368 p_load_cal_type => x_load_cal_type,
2369 p_load_sequence_number => x_load_sequence_number,
2370 p_truncate_ind => 'N',
2371 p_include_research_ind => 'Y' ,
2372 p_key_course_cd => NULL ,
2373 p_key_version_number => NULL ,
2374 p_credit_points => l_total_credit_points );
2375
2376 -- The value of l_calc_min_cp is assigned the value of l_calc_cp as the parameter
2377 -- p_credit_points is an IN OUT parameter , that will modify the value in l_calc_cp.
2378 l_calc_min_cp := l_calc_cp ;
2379 END IF;
2380 x_return_status:='WARN';
2381
2382 -- The call to the fucntion eval_min_cp has been modified to add 4 new parameters .
2383 IF l_deny_warn_min_cp ='DENY' AND NOT igs_en_elgbl_program.eval_min_cp(
2384 p_person_id => x_person_id,
2385 p_load_calendar_type => x_load_cal_type,
2386 p_load_cal_sequence_number => x_load_sequence_number,
2387 p_uoo_id => x_uoo_id,
2388 p_program_cd => x_program_cd,
2389 p_program_version => x_program_version,
2390 p_message => l_message,
2391 p_deny_warn => l_deny_warn_min_cp,
2392 p_credit_points => l_calc_min_cp ,
2393 p_enrollment_category => l_enr_cat,
2394 p_comm_type => l_enr_comm,
2395 p_method_type => l_enr_meth_type,
2396 p_min_credit_point => l_total_credit_points,
2397 p_calling_obj => 'SCH_UPD' ) THEN
2398
2399 x_message := l_message ;
2400 x_return_status := 'DENY';
2401 RETURN;
2402
2403 END IF ;
2404
2405 IF l_deny_warn_max_cp ='DENY' AND NOT igs_en_elgbl_program.eval_max_cp (
2406 p_person_id => x_person_id,
2407 p_load_calendar_type => x_load_cal_type,
2408 p_load_cal_sequence_number => x_load_sequence_number,
2409 p_uoo_id => x_uoo_id,
2410 p_program_cd => x_program_cd,
2411 p_program_version => x_program_version,
2412 p_message => l_message,
2413 p_deny_warn => l_deny_warn_max_cp,
2414 p_upd_cp => l_calc_cp,
2415 p_calling_obj => 'SCH_UPD') THEN
2416
2417 x_return_status := 'DENY';
2418 x_message := l_message ;
2419 RETURN;
2420
2421 END IF ;
2422
2423 IF l_deny_warn_reenroll ='DENY' AND NOT igs_en_elgbl_unit.eval_unit_reenroll (
2424 p_person_id => x_person_id,
2425 p_load_cal_type => x_load_cal_type,
2426 p_load_cal_seq_number => x_load_sequence_number,
2427 p_uoo_id => x_uoo_id,
2428 p_program_cd => x_program_cd,
2429 p_program_version => x_program_version,
2430 p_message => l_message,
2431 p_deny_warn => l_deny_warn_reenroll,
2432 p_upd_cp => x_override_enrolled_cp-l_current_cp,
2433 p_val_level => 'CREDIT_POINT',
2434 p_calling_obj => 'SCH_UPD' ) THEN
2435
2436 x_return_status := 'DENY';
2437 x_message := l_message ;
2438 RETURN;
2439 END IF ;
2440
2441 IF l_deny_warn_att_type ='DENY' THEN
2442
2443 OPEN cur_coo_id;
2444 FETCH cur_coo_id INTO l_cur_coo_id;
2445 CLOSE cur_coo_id;
2446
2447 -- Check if the Forced Attendance Type has already been reached for the Student before transferring .
2448 l_attendance_type_reach := igs_en_val_sca.enrp_val_coo_att(p_person_id => x_person_id,
2449 p_coo_id => l_cur_coo_id.coo_id,
2450 p_cal_type => l_acad_cal_type,
2451 p_ci_sequence_number => l_acad_ci_sequence_number,
2452 p_message_name => l_message,
2453 p_attendance_types => l_attendance_types,
2454 p_load_or_teach_cal_type => x_load_cal_type,
2455 p_load_or_teach_seq_number => x_load_sequence_number);
2456
2457
2458 -- Assign values to the parameter p_deny_warn_att based on if Attendance Type has not been already reached or not.
2459 IF l_attendance_type_reach THEN
2460 l_deny_warn_att_type := 'AttTypReached' ;
2461 ELSE
2462 l_deny_warn_att_type := 'AttTypNotReached' ;
2463 END IF ;
2464
2465 BEGIN
2466 OPEN get_sua_dtls;
2467 FETCH get_sua_dtls INTO l_sua_dtls_rec;
2468 CLOSE get_sua_dtls;
2469
2470 Igs_En_Su_Attempt_Pkg.update_row
2471 (x_rowid => l_sua_dtls_rec.ROWID,
2472 x_person_id =>l_sua_dtls_rec.person_id,
2473 x_course_cd =>l_sua_dtls_rec.course_cd,
2474 x_unit_cd =>l_sua_dtls_rec.unit_cd,
2475 x_cal_type =>l_sua_dtls_rec.cal_type,
2476 x_ci_sequence_number =>l_sua_dtls_rec.ci_sequence_number,
2477 x_version_number =>l_sua_dtls_rec.version_number,
2478 x_location_cd =>l_sua_dtls_rec.location_cd,
2479 x_unit_class =>l_sua_dtls_rec.unit_class,
2480 x_ci_start_dt =>l_sua_dtls_rec.ci_start_dt,
2481 x_ci_end_dt =>l_sua_dtls_rec.ci_end_dt,
2482 x_uoo_id =>l_sua_dtls_rec.uoo_id,
2483 x_enrolled_dt =>l_sua_dtls_rec.enrolled_dt,
2484 x_unit_attempt_status => l_sua_dtls_rec.unit_attempt_status,
2485 x_administrative_unit_status =>l_sua_dtls_rec.administrative_unit_status,
2486 x_discontinued_dt =>l_sua_dtls_rec.discontinued_dt,
2487 x_dcnt_reason_cd =>l_sua_dtls_rec.dcnt_reason_cd ,
2488 x_rule_waived_dt =>l_sua_dtls_rec.rule_waived_dt,
2489 x_rule_waived_person_id =>l_sua_dtls_rec.rule_waived_person_id,
2490 x_no_assessment_ind =>l_sua_dtls_rec.no_assessment_ind,
2491 x_sup_unit_cd =>l_sua_dtls_rec.sup_unit_cd,
2492 x_sup_version_number =>l_sua_dtls_rec.sup_version_number,
2493 x_exam_location_cd =>l_sua_dtls_rec.exam_location_cd,
2494 x_alternative_title =>l_sua_dtls_rec.alternative_title,
2495 x_override_enrolled_cp =>x_override_enrolled_cp ,
2496 x_override_eftsu =>l_sua_dtls_rec.override_eftsu,
2497 x_override_achievable_cp =>l_sua_dtls_rec.override_achievable_cp,
2498 x_override_outcome_due_dt =>l_sua_dtls_rec.override_outcome_due_dt,
2499 x_override_credit_reason =>l_sua_dtls_rec.override_credit_reason,
2500 x_administrative_priority =>l_sua_dtls_rec.administrative_priority,
2501 x_waitlist_dt =>l_sua_dtls_rec.waitlist_dt,
2502 x_gs_version_number => l_sua_dtls_rec.gs_version_number,
2503 x_enr_method_type => l_sua_dtls_rec.enr_method_type,
2504 x_failed_unit_rule => l_sua_dtls_rec.failed_unit_rule,
2505 x_cart => l_sua_dtls_rec.cart,
2506 x_rsv_seat_ext_id => l_sua_dtls_rec.rsv_seat_ext_id,
2507 x_mode =>'R',
2508 x_org_unit_cd => l_sua_dtls_rec.org_unit_cd,
2509 x_session_id => l_sua_dtls_rec.session_id,
2510 x_grading_schema_code => l_sua_dtls_rec.grading_schema_code,
2511 x_deg_aud_detail_id => l_sua_dtls_rec.deg_aud_detail_id,
2512 x_student_career_transcript => l_sua_dtls_rec.student_career_transcript,
2513 x_student_career_statistics => l_sua_dtls_rec.student_career_statistics,
2514 x_subtitle => l_sua_dtls_rec.subtitle,
2515 x_waitlist_manual_ind => l_sua_dtls_rec.waitlist_manual_ind,
2516 x_attribute_category => l_sua_dtls_rec.attribute_category,
2517 x_attribute1 => l_sua_dtls_rec.attribute1,
2518 x_attribute2 => l_sua_dtls_rec.attribute2,
2519 x_attribute3 => l_sua_dtls_rec.attribute3,
2520 x_attribute4 => l_sua_dtls_rec.attribute4,
2521 x_attribute5 => l_sua_dtls_rec.attribute5,
2522 x_attribute6 => l_sua_dtls_rec.attribute6,
2523 x_attribute7 => l_sua_dtls_rec.attribute7,
2524 x_attribute8 => l_sua_dtls_rec.attribute8,
2525 x_attribute9 => l_sua_dtls_rec.attribute9,
2526 x_attribute10 => l_sua_dtls_rec.attribute10,
2527 x_attribute11 => l_sua_dtls_rec.attribute11,
2528 x_attribute12 => l_sua_dtls_rec.attribute12,
2529 x_attribute13 => l_sua_dtls_rec.attribute13,
2530 x_attribute14 => l_sua_dtls_rec.attribute14,
2531 x_attribute15 => l_sua_dtls_rec.attribute15,
2532 x_attribute16 => l_sua_dtls_rec.attribute16,
2533 x_attribute17 => l_sua_dtls_rec.attribute17,
2534 x_attribute18 => l_sua_dtls_rec.attribute18,
2535 x_attribute19 => l_sua_dtls_rec.attribute19,
2536 x_attribute20 => l_sua_dtls_rec.attribute20,
2537 X_WLST_PRIORITY_WEIGHT_NUM => l_sua_dtls_rec.wlst_priority_weight_num,
2538 X_WLST_PREFERENCE_WEIGHT_NUM=> l_sua_dtls_rec.wlst_preference_weight_num,
2539 X_CORE_INDICATOR_CODE => l_sua_dtls_rec.core_indicator_code,
2540 X_UPD_AUDIT_FLAG => l_sua_dtls_rec.upd_audit_flag,
2541 X_SS_SOURCE_IND => l_sua_dtls_rec.ss_source_ind
2542 );
2543 EXCEPTION
2544 WHEN OTHERS THEN
2545 ROLLBACK TO upd_sua_cp ;
2546 END ;
2547
2548 IF NOT igs_en_elgbl_program.eval_unit_forced_type(
2549 p_person_id => x_person_id,
2550 p_load_calendar_type => x_load_cal_type,
2551 p_load_cal_sequence_number => x_load_sequence_number,
2552 p_uoo_id => x_uoo_id,
2553 p_course_cd => x_program_cd,
2554 p_course_version => x_program_version,
2555 p_message => l_message,
2556 p_deny_warn => l_deny_warn_att_type ,
2557 p_enrollment_category => l_enr_cat,
2558 p_comm_type => l_enr_comm,
2559 p_method_type => l_enr_meth_type,
2560 p_calling_obj => 'SCH_UPD' ) THEN
2561
2562 x_return_status := 'DENY';
2563 x_message := l_message ;
2564 RETURN;
2565 END IF ;
2566 END IF ;
2567
2568
2569 IF l_deny_warn_cross_fac ='DENY' AND NOT igs_en_elgbl_program.eval_cross_validation (
2570 p_person_id => x_person_id,
2571 p_load_cal_type => x_load_cal_type,
2572 p_load_ci_sequence_number => x_load_sequence_number,
2573 p_uoo_id => x_uoo_id,
2574 p_course_cd => x_program_cd,
2575 p_program_version => x_program_version,
2576 p_message => l_message,
2577 p_deny_warn => l_deny_warn_cross_fac,
2578 p_upd_cp => l_total_credit_points ,
2579 p_eligibility_step_type => 'CROSS_FAC',
2580 p_calling_obj => 'SCH_UPD' ) THEN
2581
2582 x_return_status := 'DENY';
2583 x_message := l_message ;
2584 RETURN;
2585 END IF ;
2586
2587
2588 IF l_deny_warn_cross_mod ='DENY' AND NOT igs_en_elgbl_program.eval_cross_validation (
2589 p_person_id => x_person_id,
2590 p_load_cal_type => x_load_cal_type,
2591 p_load_ci_sequence_number => x_load_sequence_number,
2592 p_uoo_id => x_uoo_id,
2593 p_course_cd => x_program_cd,
2594 p_program_version => x_program_version,
2595 p_message => l_message,
2596 p_deny_warn => l_deny_warn_cross_mod,
2597 p_upd_cp => l_total_credit_points ,
2598 p_eligibility_step_type => 'CROSS_MOD',
2599 p_calling_obj => 'SCH_UPD' ) THEN
2600
2601 x_return_status := 'DENY';
2602 x_message := l_message ;
2603 RETURN;
2604 END IF ;
2605
2606 IF l_deny_warn_cross_loc ='DENY' AND NOT igs_en_elgbl_program.eval_cross_validation (
2607 p_person_id => x_person_id,
2608 p_load_cal_type => x_load_cal_type,
2609 p_load_ci_sequence_number => x_load_sequence_number,
2610 p_uoo_id => x_uoo_id,
2611 p_course_cd => x_program_cd,
2612 p_program_version => x_program_version,
2613 p_message => l_message,
2614 p_deny_warn => l_deny_warn_cross_loc,
2615 p_upd_cp => l_total_credit_points ,
2616 p_eligibility_step_type => 'CROSS_LOC' ,
2617 p_calling_obj => 'SCH_UPD') THEN
2618
2619 x_return_status := 'DENY';
2620 x_message := l_message ;
2621 RETURN;
2622 END IF ;
2623
2624 IF x_message IS NOT NULL THEN
2625 x_return_status := 'DENY' ;
2626 ELSE
2627 x_return_status := 'WARN';
2628 END IF;
2629
2630 ROLLBACK TO upd_sua_cp ;
2631 RETURN;
2632 END validate_upd_cp;
2633
2634 PROCEDURE blk_drop_units(
2635 p_uoo_id IN NUMBER,
2636 p_person_id IN NUMBER,
2637 p_person_type IN VARCHAR2,
2638 p_load_cal_type IN VARCHAR2,
2639 p_load_sequence_number IN NUMBER,
2640 p_acad_cal_type IN VARCHAR2,
2641 p_acad_sequence_number IN NUMBER,
2642 p_program_cd IN VARCHAR2,
2643 p_program_version IN NUMBER ,
2644 p_dcnt_reason_cd IN VARCHAR2,
2645 p_admin_unit_status IN VARCHAR2,
2646 p_effective_date IN DATE ,
2647 p_enrolment_cat IN VARCHAR2,
2648 p_comm_type IN VARCHAR2,
2649 p_enr_meth_type IN VARCHAR2,
2650 p_total_credit_points IN NUMBER,
2651 p_force_att_type IN VARCHAR2,
2652 p_val_ovrrd_chk IN VARCHAR2,
2653 p_ovrrd_drop IN VARCHAR2,
2654 p_return_status OUT NOCOPY BOOLEAN,
2655 p_message OUT NOCOPY VARCHAR2,
2656 p_sub_unit IN VARCHAR2
2657 )AS
2658 ------------------------------------------------------------------
2659 --
2660 --Known limitations/enhancements and/or remarks:
2661 --
2662 --Change History:
2663 --Who When What
2664 --svanukur 04-dec-2003 passing the load calendar details to the procedure enrp_val_enr_encmb
2665 -- instead of the academic calendar details as part of bug 3227399
2666 -- stutta 21-Feb-2006 Modified ref cursor c_ref_cur_inst for perf bug #5054297
2667 -------------------------------------------------------------------
2668
2669
2670 l_deny_warn_min_cp VARCHAR2(100) := NULL ;
2671 l_deny_warn_coreq VARCHAR2(100) := NULL ;
2672 l_deny_warn_prereq VARCHAR2(100) := NULL ;
2673 l_deny_warn_att_type VARCHAR2(100) := NULL ;
2674 l_message VARCHAR2(2000);
2675 l_min_cp_message VARCHAR2(2000);
2676 l_att_type_message VARCHAR2(2000) := NULL ;
2677 l_coreq_failed BOOLEAN := FALSE;
2678 l_prereq_failed BOOLEAN := FALSE;
2679 l_all_units_for_drop BOOLEAN := FALSE;
2680 l_enr_cal_type VARCHAR2(20);
2681 l_enr_ci_seq NUMBER(20);
2682 l_enr_comm VARCHAR2(2000);
2683 l_return_status VARCHAR2(10);
2684 -- Added as part of Enrollment Eligibility and validations
2685 l_eftsu_total igs_en_su_attempt.override_eftsu%type;
2686 l_total_credit_points NUMBER;
2687 l_credit_points igs_en_su_attempt.override_enrolled_cp%TYPE := 0;
2688 l_unit_version_number igs_ps_unit_ver.VERSION_NUMBER%TYPE;
2689 l_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
2690 l_acad_start_dt igs_ca_inst.start_dt%type;
2691 l_acad_end_dt igs_ca_inst.end_dt%type;
2692 l_alternate_code igs_ca_inst.alternate_code%type;
2693
2694
2695 CURSOR cur_uoo_id (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2696 SELECT version_number
2697 FROM igs_ps_unit_ofr_opt
2698 WHERE uoo_id = p_uoo_id;
2699
2700
2701 CURSOR c_dcnt_rsn IS
2702 SELECT DISCONTINUATION_REASON_CD
2703 FROM igs_en_dcnt_reasoncd
2704 WHERE NVL(closed_ind,'N') ='N'
2705 AND dflt_ind ='Y'
2706 AND dcnt_unit_ind ='Y';
2707
2708
2709 lv_message_name VARCHAR2(2000);
2710 lv_message_name2 VARCHAR2(100);
2711 lv_return_type VARCHAR2(2);
2712 l_req_unit_failed BOOLEAN;
2713
2714 l_dcnt_reason_cd igs_en_dcnt_reasoncd.discontinuation_reason_cd%TYPE;
2715
2716 -- Cursor to get the coo_id of the student.
2717 CURSOR cur_coo_id IS
2718 SELECT coo_id coo_id
2719 FROM igs_en_stdnt_ps_att
2720 WHERE person_id = p_person_id
2721 AND course_cd = p_program_cd ;
2722
2723 l_attendance_type_reach BOOLEAN := TRUE;
2724 l_cur_coo_id cur_coo_id%ROWTYPE;
2725 l_attendance_types VARCHAR2(100); -- As returned from the function igs_en_val_sca.enrp_val_coo_att
2726 l_core_indicator_code igs_en_su_attempt.core_indicator_code%TYPE;
2727 l_deny_warn VARCHAR2(10);
2728
2729 --
2730 -- Cursor to find the Core Indicator associated with a Unit Attempt - ptandon, Enh Bug# 3052432
2731 --
2732 CURSOR cur_get_core_ind(cp_person_id igs_en_su_attempt.person_id%TYPE,
2733 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
2734 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
2735 IS
2736 SELECT core_indicator_code
2737 FROM igs_en_su_attempt
2738 WHERE person_id = cp_person_id
2739 AND course_cd = cp_course_cd
2740 AND uoo_id = cp_uoo_id;
2741
2742 --
2743 -- Cursor to find the Unit Code - ptandon, Enh Bug# 3052432
2744 --
2745 CURSOR cur_unit_cd (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2746 SELECT unit_cd
2747 FROM igs_ps_unit_ofr_opt
2748 WHERE uoo_id = p_uoo_id;
2749
2750 TYPE c_ref_cursor IS REF CURSOR;
2751 c_ref_cur_inst c_ref_cursor;
2752 v_ref_cur_rec igs_en_su_attempt%ROWTYPE;
2753 l_person_id_found igs_en_su_attempt_all.person_id%TYPE;
2754 TYPE tDataBuff IS
2755 TABLE OF igs_en_su_attempt%ROWTYPE
2756 INDEX BY BINARY_INTEGER;
2757
2758 lData tDataBuff;
2759 lDataEmpty tDataBuff;
2760 t1_idx BINARY_INTEGER := 0;
2761
2762
2763 BEGIN --blk_drop_units
2764 OPEN cur_unit_cd(p_uoo_id);
2765 FETCH cur_unit_cd INTO l_unit_cd;
2766 CLOSE cur_unit_cd;
2767 -- Get the value of core indicator for unit attempt
2768 OPEN cur_get_core_ind(p_person_id,p_program_cd,p_uoo_id);
2769 FETCH cur_get_core_ind INTO l_core_indicator_code;
2770 CLOSE cur_get_core_ind;
2771
2772 -- Get the value of core indicator for unit attempt
2773 IF l_core_indicator_code = 'CORE' THEN
2774 -- If the unit attempt being dropped is Core check whether user can
2775 -- drop/discontinue core unit attempt. If Yes then only proceed
2776 -- otherwise log an error and return from the procedure.
2777 IF igs_en_gen_015.eval_core_unit_drop
2778 (
2779 p_person_id,
2780 p_program_cd,
2781 p_uoo_id,
2782 'DROP_CORE',
2783 p_load_cal_type,
2784 p_load_sequence_number,
2785 l_deny_warn,
2786 p_enr_meth_type
2787 ) = 'FALSE'
2788 THEN
2789 -- Get the Unit Code
2790
2791
2792 IF l_deny_warn = 'DENY' THEN
2793 -- Log the error and return
2794 p_message:='IGS_EN_CORE_DROP_DENY*'||l_unit_cd;
2795 p_return_status := FALSE;
2796 RETURN;
2797 ELSE
2798 p_message:='IGS_EN_CORE_DROP_WARN*'||l_unit_cd;
2799 END IF;
2800 END IF;
2801 END IF;
2802
2803 lData := lDataEmpty; --Initializing the array
2804 l_enr_comm := p_comm_type;
2805
2806 IF p_dcnt_reason_cd IS NULL THEN
2807 OPEN c_dcnt_rsn;
2808 FETCH c_dcnt_rsn INTO l_dcnt_reason_cd;
2809 CLOSE c_dcnt_rsn;
2810 END IF;
2811 IF l_enr_comm = 'BOTH' THEN
2812 l_enr_comm :='ALL';
2813 END IF;
2814 IF p_val_ovrrd_chk ='N' THEN
2815 l_message:= NULL;
2816 l_deny_warn_min_cp:= igs_ss_enr_details.get_notification(p_person_type => p_person_type,
2817 p_enrollment_category => p_enrolment_cat,
2818 p_comm_type => l_enr_comm,
2819 p_enr_method_type => p_enr_meth_type,
2820 p_step_group_type => 'PROGRAM',
2821 p_step_type => 'FMIN_CRDT',
2822 p_person_id => p_person_id,
2823 p_message => l_message) ;
2824 IF l_message IS NOT NULL THEN
2825 p_message := l_message;
2826 p_return_status := FALSE;
2827 RETURN;
2828 END IF;
2829 END IF;
2830 IF p_val_ovrrd_chk ='N' THEN
2831 l_message:= NULL;
2832 l_deny_warn_att_type := igs_ss_enr_details.get_notification(p_person_type => p_person_type,
2833 p_enrollment_category => p_enrolment_cat,
2834 p_comm_type => l_enr_comm,
2835 p_enr_method_type => p_enr_meth_type,
2836 p_step_group_type => 'PROGRAM',
2837 p_step_type => 'FATD_TYPE',
2838 p_person_id => p_person_id,
2839 p_message => l_message) ;
2840 IF l_message IS NOT NULL THEN
2841 p_message := l_message;
2842 p_return_status := FALSE;
2843 RETURN;
2844 END IF;
2845 l_deny_warn_att_type :=p_force_att_type;
2846 END IF;
2847 IF p_val_ovrrd_chk = 'N' THEN
2848 l_message:= NULL;
2849 l_deny_warn_coreq := igs_ss_enr_details.get_notification( p_person_type => p_person_type,
2850 p_enrollment_category => p_enrolment_cat,
2851 p_comm_type => l_enr_comm,
2852 p_enr_method_type => p_enr_meth_type,
2853 p_step_group_type => 'UNIT',
2854 p_step_type => 'COREQ',
2855 p_person_id => p_person_id,
2856 p_message => l_message) ;
2857 IF l_message IS NOT NULL THEN
2858 p_message := l_message;
2859 p_return_status := FALSE;
2860 RETURN;
2861 END IF;
2862 END IF;
2863 IF p_val_ovrrd_chk ='N' THEN
2864 l_message:= NULL;
2865 l_deny_warn_prereq := igs_ss_enr_details.get_notification( p_person_type => p_person_type,
2866 p_enrollment_category => p_enrolment_cat,
2867 p_comm_type => l_enr_comm,
2868 p_enr_method_type => p_enr_meth_type,
2869 p_step_group_type => 'UNIT',
2870 p_step_type => 'PREREQ',
2871 p_person_id => p_person_id,
2872 p_message => l_message ) ;
2873 IF l_message IS NOT NULL THEN
2874 p_message := l_message;
2875 p_return_status := FALSE;
2876 RETURN;
2877 END IF;
2878 END IF;
2879 --Decode the concatenated string and check that all units are not selected,
2880 --in which case, just delete all units w/o checking cp or coreq requirements.
2881
2882 OPEN c_ref_cur_inst FOR 'SELECT DISTINCT U.PERSON_ID FROM IGS_EN_SU_ATTEMPT U, igs_ca_load_to_teach_v WHERE person_id =:1 AND course_cd = :2 '||
2883 'AND unit_attempt_status IN (''ENROLLED'',''INVALID'',''WAITLISTED'') '||
2884 'AND cal_type = teach_cal_type AND ci_sequence_number= teach_ci_sequence_number '||
2885 'AND load_cal_type = :3 AND load_ci_sequence_number =:4 '||
2886 'AND uoo_id <> :5 '
2887 USING p_person_id, p_program_cd, p_load_cal_type, p_load_sequence_number,p_uoo_id ;
2888 FETCH c_ref_cur_inst INTO l_person_id_found;
2889 IF c_ref_cur_inst%NOTFOUND THEN
2890 l_all_units_for_drop := TRUE;
2891 END IF;
2892 ClOSE c_ref_cur_inst;
2893 IF l_all_units_for_drop AND p_ovrrd_drop ='N' THEN
2894 --Call enrp_dropall_units
2895 igs_en_gen_004.enrp_dropall_unit( p_person_id => p_person_id,
2896 p_cal_type => p_load_cal_type,
2897 p_ci_sequence_number => p_load_sequence_number,
2898 p_dcnt_reason_cd => NVL(p_dcnt_reason_cd,l_dcnt_reason_cd),
2899 p_admin_unit_sta => p_admin_unit_status,
2900 p_effective_date => p_effective_date,
2901 p_program_cd => p_program_cd,
2902 p_uoo_id => NULL,
2903 p_sub_unit => p_sub_unit);
2904
2905 ELSE
2906
2907 --Only some of the units have been selected for drop. so, loop thru them and drop them
2908 l_credit_points := 0;
2909 OPEN cur_uoo_id(p_uoo_id);
2910 FETCH cur_uoo_id INTO l_unit_version_number;
2911 CLOSE cur_uoo_id ;
2912 IF p_ovrrd_drop = 'N' THEN
2913 -- Check for rule failure prior to dropping the unit, so that such failures can be ignored.
2914 -- The list of failed uoo_ids is stored in package variables pkg_coreq_failed_uooids, pkg_prereq_failed_uooids
2915 OPEN c_ref_cur_inst FOR 'SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id = :1 '||
2916 'AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND ' ||
2917 ' uoo_id <> :2'
2918 USING p_person_id, p_uoo_id;
2919
2920
2921 LOOP
2922
2923 FETCH c_ref_cur_inst INTO v_ref_cur_rec ;
2924
2925 EXIT WHEN c_ref_cur_inst%NOTFOUND;
2926
2927 IF NOT IGS_EN_ELGBL_UNIT.eval_coreq( --Do a coreq check only if l_deny_warn_coreq is defined
2928 p_person_id => p_person_id,
2929 p_load_cal_type => p_load_cal_type,
2930 p_load_sequence_number => p_load_sequence_number,
2931 p_uoo_id => v_ref_cur_rec.uoo_id,
2932 p_course_cd => p_program_cd,
2933 p_course_version => p_program_version,
2934 p_message => l_message,
2935 p_deny_warn => l_deny_warn_coreq,
2936 p_calling_obj => 'JOB') THEN
2937
2938 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
2939 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
2940 pkg_coreq_failed_uooids:= pkg_coreq_failed_uooids ||','|| TO_CHAR(v_ref_cur_rec.uoo_id);
2941
2942
2943
2944 ELSE
2945 -- Since the coreq rule has passed, store these passed uoo_ids
2946 -- into a temp table so that the prereq check can be run for these uoo_ids
2947 -- instead of opening the cursor again
2948
2949 lData(t1_idx).uoo_id := v_ref_cur_rec.uoo_id;
2950 lData(t1_idx).unit_cd := v_ref_cur_rec.unit_cd;
2951 t1_idx :=t1_idx+1;
2952
2953 END IF;
2954 END LOOP;
2955 ClOSE c_ref_cur_inst;
2956
2957 -- Instead of opening the cursor again, we are storing the passed uoo_ids
2958 -- values into a Table
2959 -- Check whether records exist in the temparary table,lDATA.
2960 IF lData.COUNT > 0 THEN
2961 FOR i IN lData.FIRST .. lData.LAST LOOP
2962 IF NOT IGS_EN_ELGBL_UNIT.eval_prereq( --Do a coreq check only if l_deny_warn_prereq is defined
2963 p_person_id => p_person_id,
2964 p_load_cal_type => p_load_cal_type,
2965 p_load_sequence_number => p_load_sequence_number,
2966 p_uoo_id => lData(i).uoo_id,
2967 p_course_cd => p_program_cd,
2968 p_course_version => p_program_version,
2969 p_message => l_message,
2970 p_deny_warn => l_deny_warn_prereq,
2971 p_calling_obj => 'JOB') THEN
2972
2973 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
2974 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
2975 pkg_prereq_failed_uooids := pkg_prereq_failed_uooids ||','|| TO_CHAR(lData(i).uoo_id);
2976
2977
2978 END IF;
2979 END LOOP;
2980 END IF;
2981 t1_idx := 0;
2982 END IF;
2983
2984 IF l_deny_warn_min_cp IS NOT NULL THEN -- Min CP validation step is defined
2985
2986 -- A call to igs_en_prc_load.enrp_clc_eftsu_total as part of- Enrollment Eligibility and validations .
2987 -- The Total enrolled CP of the student has to be determined before the unit is dropped(l_total_credit_points) .
2988 -- The unit is then dropped , and eval_min_cp is called with the value of l_total_enrolled_cp.
2989 -- The value of l_total_enrolled_cp is essential to determine if the Min Credit Points is already reached
2990 -- by the student before that Unit is dropped.
2991 IF p_ovrrd_drop = 'N' THEN
2992 --Call enrp_dropall_units
2993 -- Dropping the unit before Min CP check, moved to here as part of bug 2401891
2994 igs_en_gen_004.enrp_dropall_unit(p_person_id => p_person_id,
2995 p_cal_type => p_load_cal_type,
2996 p_ci_sequence_number => p_load_sequence_number,
2997 p_dcnt_reason_cd => NVL(p_dcnt_reason_cd,l_dcnt_reason_cd),
2998 p_admin_unit_sta => p_admin_unit_status,
2999 p_effective_date => p_effective_date,
3000 p_program_cd => p_program_cd,
3001 p_uoo_id => p_uoo_id,
3002 p_sub_unit =>p_sub_unit);
3003 END IF;
3004 l_total_credit_points := p_total_credit_points;
3005 IF igs_en_elgbl_program.eval_min_cp(p_person_id => p_person_id,
3006 p_load_calendar_type => p_load_cal_type,
3007 p_load_cal_sequence_number => p_load_sequence_number,
3008 p_uoo_id => p_uoo_id,
3009 p_program_cd => p_program_cd,
3010 p_program_version => p_program_version,
3011 p_message => l_min_cp_message,
3012 p_deny_warn => l_deny_warn_min_cp,
3013 p_credit_points => l_credit_points ,
3014 p_enrollment_category => p_enrolment_cat,
3015 p_comm_type => l_enr_comm,
3016 p_method_type => p_enr_meth_type,
3017 p_min_credit_point => l_total_credit_points,
3018 p_calling_obj => 'JOB') THEN
3019 -- validate the holds when droping an unit, pmarada, 2385096
3020
3021 IF NOT IGS_EN_VAL_ENCMB.enrp_val_enr_encmb( p_person_id,
3022 p_program_cd ,
3023 p_load_cal_type,
3024 p_load_sequence_number,
3025 lv_message_name,
3026 lv_message_name2,
3027 lv_return_type,
3028 NULL -- default value, it will be calculated internally based on the census date
3029 ) THEN
3030 IF lv_message_name2 = 'IGS_EN_PRSN_NOTENR_REQUIRE' THEN
3031 lv_message_name2 := 'IGS_EN_REQ_UNIT_CANNOT_DROP';
3032 END IF;
3033 IF lv_message_name = 'IGS_EN_PRSN_NOTENR_REQUIRE' THEN
3034 lv_message_name := 'IGS_EN_REQ_UNIT_CANNOT_DROP';
3035 END IF;
3036 IF lv_message_name IS NOT NULL AND INSTR(NVL(p_message,' '),lv_message_name) = 0 THEN
3037 p_message := p_message||lv_message_name||';';
3038 END IF;
3039 IF lv_message_name2 IS NOT NULL AND INSTR(NVL(p_message,' '),lv_message_name2) = 0 THEN
3040 p_message := p_message||lv_message_name2||';';
3041 END IF;
3042 p_return_status := FALSE;
3043 RETURN;
3044 END IF;
3045 ELSE
3046 IF l_min_cp_message ='IGS_SS_DENY_MIN_CP_REACHED' THEN
3047 p_return_status := FALSE;
3048 p_message := p_message||l_min_cp_message||';';
3049 RETURN;
3050 ELSE
3051 p_message := p_message||l_min_cp_message||';';
3052 END IF;
3053 END IF; --igs_en_elgbl_program.eval_min_cp
3054 ELSE
3055 IF p_ovrrd_drop ='N' THEN
3056 --Call enrp_dropall_units
3057 igs_en_gen_004.enrp_dropall_unit(
3058 p_person_id => p_person_id,
3059 p_cal_type => p_load_cal_type,
3060 p_ci_sequence_number => p_load_sequence_number,
3061 p_dcnt_reason_cd => NVL(p_dcnt_reason_cd,l_dcnt_reason_cd),
3062 p_admin_unit_sta => p_admin_unit_status,
3063 p_effective_date => p_effective_date,
3064 p_program_cd => p_program_cd,
3065 p_uoo_id => p_uoo_id,
3066 p_sub_unit =>p_sub_unit
3067 );
3068 END IF;
3069 -- validate the holds when droping an unit, pmarada, 2385096
3070
3071 IF p_ovrrd_drop ='N' THEN
3072 IF NOT IGS_EN_VAL_ENCMB.enrp_val_enr_encmb(p_person_id,
3073 p_program_cd ,
3074 p_load_cal_type,
3075 p_load_sequence_number,
3076 lv_message_name,
3077 lv_message_name2,
3078 lv_return_type,
3079 NULL -- default value, it will be calculated internally based on the census date
3080 ) THEN
3081 IF lv_message_name2 = 'IGS_EN_PRSN_NOTENR_REQUIRE' THEN
3082 lv_message_name2 := 'IGS_EN_REQ_UNIT_CANNOT_DROP';
3083 END IF;
3084 IF lv_message_name = 'IGS_EN_PRSN_NOTENR_REQUIRE' THEN
3085 lv_message_name := 'IGS_EN_REQ_UNIT_CANNOT_DROP';
3086 END IF;
3087 IF lv_message_name IS NOT NULL AND INSTR(NVL(p_message,' '),lv_message_name) = 0 THEN
3088 p_message := p_message||lv_message_name||';';
3089 END IF;
3090 IF lv_message_name2 IS NOT NULL AND INSTR(NVL(p_message,' '),lv_message_name2) = 0 THEN
3091 p_message := p_message||lv_message_name2||';';
3092 END IF;
3093 p_return_status := FALSE;
3094 RETURN;
3095 END IF;
3096 END IF;
3097 END IF; --l_deny_warn_min_cp
3098 -- If the Att Type validation step has been setup , continue validating.
3099 IF l_deny_warn_att_type IS NOT NULL THEN
3100 IF NOT (igs_en_elgbl_program.eval_unit_forced_type(p_person_id => p_person_id,
3101 p_load_calendar_type => p_load_cal_type,
3102 p_load_cal_sequence_number => p_load_sequence_number,
3103 p_uoo_id => p_uoo_id ,
3104 p_course_cd => p_program_cd,
3105 p_course_version => p_program_version,
3106 p_message => l_att_type_message,
3107 p_deny_warn => l_deny_warn_att_type ,
3108 p_enrollment_category => p_enrolment_cat,
3109 p_comm_type => l_enr_comm,
3110 p_method_type => p_enr_meth_type,
3111 p_calling_obj => 'JOB') ) THEN
3112 IF l_att_type_message ='IGS_SS_DENY_ATTYPE_CHK' THEN
3113 p_return_status := FALSE;
3114 p_message := p_message||l_att_type_message||';';
3115 RETURN;
3116 ELSE
3117 p_message := p_message||l_att_type_message||';';
3118 END IF;
3119 END IF ;
3120 END IF; --l_deny_warn_att_type IS NOT NULL
3121 -- Only if min_cp validation passed, need to check for coreq validation
3122 l_message:= NULL;
3123 OPEN c_ref_cur_inst FOR 'SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id = :1 '||
3124 'AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND ' ||
3125 ' uoo_id <> :2'
3126 USING p_person_id, p_uoo_id;
3127 LOOP
3128 FETCH c_ref_cur_inst INTO v_ref_cur_rec ;
3129 EXIT WHEN c_ref_cur_inst%NOTFOUND;
3130 IF l_deny_warn_coreq IS NOT NULL THEN
3131 IF NOT igs_en_elgbl_unit.eval_coreq( --Do a coreq check only if l_deny_warn_coreq is defined
3132 p_person_id => p_person_id,
3133 p_load_cal_type => p_load_cal_type,
3134 p_load_sequence_number => p_load_sequence_number,
3135 p_uoo_id => v_ref_cur_rec.uoo_id,
3136 p_course_cd => p_program_cd,
3137 p_course_version => p_program_version,
3138 p_message => l_message,
3139 p_deny_warn => l_deny_warn_coreq,
3140 p_calling_obj => 'JOB') THEN
3141 -- if the rule has not failed before the drop, only then consider coreq rule to have failed.
3142 IF pkg_coreq_failed_uooids IS NULL OR INSTR(pkg_coreq_failed_uooids,v_ref_cur_rec.uoo_id ) = 0 THEN
3143 l_coreq_failed := TRUE;
3144 END IF;
3145
3146 ELSE
3147 -- Since the coreq rule has passed, store these passed uoo_ids
3148 -- into a temp table so that the prereq check can be run for these uoo_ids
3149 -- instead of opening the cursor again
3150 lData(t1_idx).uoo_id :=v_ref_cur_rec.uoo_id;
3151 lData(t1_idx).unit_cd :=v_ref_cur_rec.unit_cd;
3152 t1_idx :=t1_idx+1;
3153 END IF;
3154
3155 END IF;
3156 END LOOP;
3157 ClOSE c_ref_cur_inst;
3158 IF l_coreq_failed THEN
3159 IF l_deny_warn_coreq ='DENY' THEN
3160 p_return_status := FALSE;
3161 p_message := p_message||'IGS_EN_COREQ_DENY*'||l_unit_cd||';';
3162 RETURN;
3163 ELSE
3164 p_message := p_message||'IGS_EN_COREQ_WARN*'||l_unit_cd||';';
3165 END IF;
3166 END IF; --l_coreq_failed
3167
3168 -- Run prereq check since coreq has passed
3169 -- Check whether records exist in the temparary table,lDATA.
3170 l_message := NULL;
3171 IF lData.COUNT > 0 AND l_deny_warn_prereq IS NOT NULL THEN
3172 FOR i IN lData.FIRST .. lData.LAST LOOP
3173 IF NOT IGS_EN_ELGBL_UNIT.eval_prereq( --Do a coreq check only if l_deny_warn_prereq is defined
3174 p_person_id => p_person_id,
3175 p_load_cal_type => p_load_cal_type,
3176 p_load_sequence_number => p_load_sequence_number,
3177 p_uoo_id => lData(i).uoo_id,
3178 p_course_cd => p_program_cd,
3179 p_course_version => p_program_version,
3180 p_message => l_message,
3181 p_deny_warn => l_deny_warn_prereq,
3182 p_calling_obj => 'JOB') THEN
3183 -- If the Unit code has already been concatenated,it should not be repeated again. This condition will arise when the student is
3184 -- enrolled in the same unit in 2 diff programs. Pre-req and Co-req validate across Programs.
3185 -- if the rule has not failed before the drop, only then consider coreq rule to have failed.
3186 IF pkg_prereq_failed_uooids IS NULL OR INSTR(pkg_prereq_failed_uooids,TO_CHAR(lData(i).uoo_id) ) = 0 THEN
3187 l_prereq_failed := TRUE;
3188 END IF;
3189
3190 END IF;
3191 END LOOP;
3192 END IF; --lData.COUNT > 0
3193 IF l_prereq_failed THEN
3194 IF l_deny_warn_prereq ='DENY' THEN
3195 p_return_status := FALSE;
3196 p_message := p_message||'IGS_EN_PREREQ_DENY*'||l_unit_cd||';';
3197 RETURN;
3198 ELSE
3199
3200 p_message := p_message||'IGS_EN_PREREQ_WARN*'||l_unit_cd||';';
3201 END IF;
3202 END IF; --l_prereq_failed
3203 END IF; -- end of l_all_units_for_drop comparison
3204 p_return_status := TRUE;
3205 END blk_drop_units;
3206
3207 PROCEDURE enrp_switch_core_section(
3208 p_person_id IN NUMBER,
3209 p_program_cd IN VARCHAR2,
3210 p_source_uoo_id IN NUMBER,
3211 p_dest_uoo_id IN NUMBER,
3212 p_session_id IN NUMBER,
3213 p_cal_type IN VARCHAR2,
3214 p_ci_sequence_number IN NUMBER,
3215 p_audit_requested IN VARCHAR2,
3216 p_core_indicator_code IN VARCHAR2,
3217 p_waitlist_ind IN VARCHAR2,
3218 p_return_status OUT NOCOPY VARCHAR2,
3219 p_message_name OUT NOCOPY VARCHAR2)
3220
3221 ------------------------------------------------------------------
3222 --Created by : Parul Tandon, Oracle IDC
3223 --Date created: 01-OCT-2003
3224 --
3225 --Purpose: This procedure is to switch the core unit sections selected,
3226 -- i.e. drop the source unit attempt and then add the destination unit section to cart.
3227 --
3228 --Known limitations/enhancements and/or remarks:
3229 --
3230 --Change History:
3231 --Who When What
3232 -------------------------------------------------------------------
3233
3234 AS
3235
3236 --
3237 -- Cursor to find the Rowid associated with a Unit Attempt
3238 --
3239 CURSOR cur_get_source_rowid(cp_person_id igs_en_su_attempt.person_id%TYPE,
3240 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
3241 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
3242 IS
3243 SELECT rowid
3244 FROM igs_en_su_attempt
3245 WHERE person_id = cp_person_id
3246 AND course_cd = cp_course_cd
3247 AND uoo_id = cp_uoo_id;
3248
3249 --
3250 -- Cursor to find the Person Number associated with a Person Id
3251 --
3252 CURSOR cur_get_person_num(cp_person_id igs_pe_person.person_id%TYPE)
3253 IS
3254 SELECT party_number
3255 FROM hz_parties
3256 WHERE party_id = cp_person_id;
3257
3258
3259
3260 l_source_rowid VARCHAR2(20);
3261 l_person_number igs_pe_person.person_number%TYPE;
3262 l_enr_method igs_en_cat_prc_step.enr_method_type%TYPE;
3263 l_message VARCHAR2(100);
3264 l_return_status VARCHAR2(10);
3265
3266 BEGIN
3267 -- Get the Rowid of Source Unit Attempt
3268 OPEN cur_get_source_rowid(p_person_id,p_program_cd,p_source_uoo_id);
3269 FETCH cur_get_source_rowid INTO l_source_rowid;
3270 CLOSE cur_get_source_rowid;
3271
3272 -- Delete the source unit attempt
3273 IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW(l_source_rowid);
3274
3275 -- Get the Person Number
3276 OPEN cur_get_person_num(p_person_id);
3277 FETCH cur_get_person_num INTO l_person_number;
3278 CLOSE cur_get_person_num;
3279
3280
3281 --- Get the enrollment method
3282 igs_en_gen_017.enrp_get_enr_method(l_enr_method,l_message,l_return_status);
3283
3284 -- Add Destination Unit Section to Cart
3285 insert_into_enr_worksheet(
3286 p_person_number => l_person_number,
3287 p_course_cd => p_program_cd,
3288 p_uoo_id => p_dest_uoo_id,
3289 p_waitlist_ind => p_waitlist_ind,
3290 p_session_id => p_session_id,
3291 p_return_status => p_return_status,
3292 p_message => p_message_name,
3293 p_cal_type => p_cal_type,
3294 p_ci_sequence_number => p_ci_sequence_number,
3295 p_audit_requested => p_audit_requested,
3296 p_enr_method => l_enr_method,
3297 p_core_indicator_code => p_core_indicator_code,
3298 p_calling_obj => 'JOB');
3299
3300 END enrp_switch_core_section;
3301
3302 --
3303 PROCEDURE drop_notif_variable(
3304 p_reason IN VARCHAR2,
3305 p_source_of_drop IN VARCHAR2) AS
3306 /*
3307 || Created By : kkillams
3308 || Created On : 27-SEP-2002
3309 || Purpose : This procedure is created for self service application to set the
3310 || student unit attempt package variables
3311 || Known limitations, enhancements or remarks :
3312 || Change History :
3313 || Who When What
3314 */
3315 BEGIN
3316 igs_en_su_attempt_pkg.pkg_reason := NULL;
3317 igs_en_su_attempt_pkg.pkg_source_of_drop :=NULL;
3318 igs_en_su_attempt_pkg.pkg_reason := p_reason;
3319 igs_en_su_attempt_pkg.pkg_source_of_drop := p_source_of_drop;
3320 NULL;
3321 END drop_notif_variable;
3322
3323 PROCEDURE ENRP_CHK_DEL_SUB_UNITS(
3324 p_person_id IN NUMBER,
3325 p_course_cd IN VARCHAR2,
3326 p_load_cal_type IN VARCHAR2,
3327 p_load_ci_seq_num IN NUMBER,
3328 p_selected_uoo_ids IN VARCHAR2,
3329 p_ret_all_uoo_ids OUT NOCOPY VARCHAR2,
3330 p_ret_sub_uoo_ids OUT NOCOPY VARCHAR2,
3331 p_ret_nonsub_uoo_ids OUT NOCOPY VARCHAR2,
3332 p_delete_flag IN VARCHAR2 DEFAULT 'N'
3333 ) AS
3334
3335 ------------------------------------------------------------------
3336 --Created by : Satya Vanukuri, Oracle IDC
3337 --Date created: 10-OCT-2003
3338 --
3339 --Purpose: This procedure reorders the units selected for drop such that the subordinate
3340 -- units are processed before the superior units
3341 --
3342 --Known limitations/enhancements and/or remarks:
3343 --
3344 --Change History:
3345 --Who When What
3346 -------------------------------------------------------------------
3347
3348 TYPE t_uoo_ref_cur IS REF CURSOR;
3349 c_sup_uoo_cur_inst t_uoo_ref_cur;
3350 c_sub_sua_cur_inst t_uoo_ref_cur;
3351
3352 v_select_stmt VARCHAR2(2000);
3353 v_sup_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE;
3354 v_sup_unit_cd IGS_PS_UNIT_OFR_OPT.UNIT_CD%TYPE;
3355
3356 v_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE;
3357 v_row_id VARCHAR2(25);
3358
3359
3360 CURSOR c_plan_status IS
3361 SELECT plan_sht_status
3362 FROM igs_en_spa_terms
3363 WHERE person_id = p_person_id
3364 AND program_cd = p_course_cd
3365 AND term_cal_type = p_load_cal_type
3366 AND term_sequence_number = p_load_ci_seq_num
3367 AND plan_sht_status NOT IN ('SKIP' ,'SUB_CART' , 'NONE');
3368
3369 l_plan_sht_status igs_en_spa_terms.plan_sht_status%TYPE;
3370
3371 PROCEDURE L_UPD_DEL_SUA(
3372 p_person_id IN NUMBER,
3373 p_course_cd IN VARCHAR2,
3374 p_uoo_id IN NUMBER) AS
3375 --local procedure to update the unit attempt to drop
3376 --fetch the unit attempts for the student and program in context
3377 CURSOR c_sua_rec IS
3378 SELECT sua.rowid,sua.* from igs_en_su_attempt sua
3379 WHERE person_id = p_person_id
3380 AND course_cd = p_course_cd
3381 AND uoo_id = p_uoo_id;
3382
3383
3384 BEGIN
3385
3386 FOR v_sua_rec IN c_sua_rec LOOP
3387 --phisically delelte if sua status is "unconfirm"
3388 IF v_sua_rec.unit_attempt_status = 'UNCONFIRM' THEN
3389 igs_en_su_attempt_pkg.delete_row(v_sua_rec.row_id);
3390
3391 --update to dropped if status is invalid
3392
3393 ELSIF v_sua_rec.unit_attempt_status = 'INVALID' THEN
3394
3395 IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT(
3396 X_ROWID => V_SUA_REC.ROW_ID,
3397 X_PERSON_ID => V_SUA_REC.PERSON_ID,
3398 X_COURSE_CD =>V_SUA_REC.COURSE_CD ,
3399 X_UNIT_CD =>V_SUA_REC.UNIT_CD,
3400 X_CAL_TYPE =>V_SUA_REC.CAL_TYPE,
3401 X_CI_SEQUENCE_NUMBER => V_SUA_REC.CI_SEQUENCE_NUMBER,
3402 X_VERSION_NUMBER =>V_SUA_REC.VERSION_NUMBER ,
3403 X_LOCATION_CD =>V_SUA_REC.LOCATION_CD,
3404 X_UNIT_CLASS =>V_SUA_REC.UNIT_CLASS,
3405 X_CI_START_DT =>V_SUA_REC.CI_START_DT,
3406 X_CI_END_DT => V_SUA_REC.CI_END_DT,
3407 X_UOO_ID =>V_SUA_REC.UOO_ID,
3408 X_ENROLLED_DT =>V_SUA_REC.ENROLLED_DT ,
3409 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
3410 X_ADMINISTRATIVE_UNIT_STATUS =>V_SUA_REC.ADMINISTRATIVE_UNIT_STATUS,
3411 X_DISCONTINUED_DT =>V_SUA_REC.DISCONTINUED_DT,
3412 X_RULE_WAIVED_DT =>V_SUA_REC.RULE_WAIVED_DT,
3413 X_RULE_WAIVED_PERSON_ID =>V_SUA_REC.RULE_WAIVED_PERSON_ID,
3414 X_NO_ASSESSMENT_IND =>V_SUA_REC.NO_ASSESSMENT_IND,
3415 X_SUP_UNIT_CD =>V_SUA_REC.SUP_UNIT_CD,
3416 X_SUP_VERSION_NUMBER =>V_SUA_REC.SUP_VERSION_NUMBER,
3417 X_EXAM_LOCATION_CD =>V_SUA_REC.EXAM_LOCATION_CD,
3418 X_ALTERNATIVE_TITLE =>V_SUA_REC.ALTERNATIVE_TITLE,
3419 X_OVERRIDE_ENROLLED_CP =>V_SUA_REC.OVERRIDE_ENROLLED_CP,
3420 X_OVERRIDE_EFTSU =>V_SUA_REC.OVERRIDE_EFTSU,
3421 X_OVERRIDE_ACHIEVABLE_CP =>V_SUA_REC.OVERRIDE_ACHIEVABLE_CP,
3422 X_OVERRIDE_OUTCOME_DUE_DT =>V_SUA_REC.OVERRIDE_OUTCOME_DUE_DT,
3423 X_OVERRIDE_CREDIT_REASON =>V_SUA_REC.OVERRIDE_CREDIT_REASON,
3424 X_ADMINISTRATIVE_PRIORITY =>V_SUA_REC.ADMINISTRATIVE_PRIORITY ,
3425 X_WAITLIST_DT =>V_SUA_REC.WAITLIST_DT,
3426 X_DCNT_REASON_CD =>V_SUA_REC.DCNT_REASON_CD,
3427 X_MODE =>'R',
3428 X_GS_VERSION_NUMBER =>V_SUA_REC.GS_VERSION_NUMBER,
3429 X_ENR_METHOD_TYPE =>V_SUA_REC.ENR_METHOD_TYPE,
3430 X_FAILED_UNIT_RULE =>V_SUA_REC.FAILED_UNIT_RULE,
3431 X_CART => V_SUA_REC.CART ,
3432 X_RSV_SEAT_EXT_ID =>V_SUA_REC.RSV_SEAT_EXT_ID ,
3433 X_ORG_UNIT_CD =>V_SUA_REC.ORG_UNIT_CD ,
3434 X_GRADING_SCHEMA_CODE =>V_SUA_REC.GRADING_SCHEMA_CODE,
3435 X_subtitle =>V_SUA_REC.subtitle,
3436 x_session_id =>V_SUA_REC.session_id ,
3437 X_deg_aud_detail_id =>V_SUA_REC.deg_aud_detail_id,
3438 x_student_career_transcript =>V_SUA_REC.student_career_transcript,
3439 x_student_career_statistics =>V_SUA_REC.student_career_statistics,
3440 X_WAITLIST_MANUAL_IND =>V_SUA_REC.WAITLIST_MANUAL_IND ,
3441 X_ATTRIBUTE_CATEGORY =>V_SUA_REC.ATTRIBUTE_CATEGORY,
3442 X_ATTRIBUTE1 =>V_SUA_REC.ATTRIBUTE1,
3443 X_ATTRIBUTE2 =>V_SUA_REC.ATTRIBUTE2,
3444 X_ATTRIBUTE3 =>V_SUA_REC.ATTRIBUTE3,
3445 X_ATTRIBUTE4 =>V_SUA_REC.ATTRIBUTE4,
3446 X_ATTRIBUTE5 =>V_SUA_REC.ATTRIBUTE5,
3447 X_ATTRIBUTE6 =>V_SUA_REC.ATTRIBUTE6,
3448 X_ATTRIBUTE7 =>V_SUA_REC.ATTRIBUTE7,
3449 X_ATTRIBUTE8 =>V_SUA_REC.ATTRIBUTE8,
3450 X_ATTRIBUTE9 =>V_SUA_REC.ATTRIBUTE9,
3451 X_ATTRIBUTE10 =>V_SUA_REC.ATTRIBUTE10,
3452 X_ATTRIBUTE11 =>V_SUA_REC.ATTRIBUTE11,
3453 X_ATTRIBUTE12 =>V_SUA_REC.ATTRIBUTE12,
3454 X_ATTRIBUTE13 =>V_SUA_REC.ATTRIBUTE13,
3455 X_ATTRIBUTE14 =>V_SUA_REC.ATTRIBUTE14,
3456 X_ATTRIBUTE15 =>V_SUA_REC.ATTRIBUTE15,
3457 X_ATTRIBUTE16 =>V_SUA_REC.ATTRIBUTE16,
3458 X_ATTRIBUTE17 =>V_SUA_REC.ATTRIBUTE17,
3459 X_ATTRIBUTE18 =>V_SUA_REC.ATTRIBUTE18,
3460 X_ATTRIBUTE19 =>V_SUA_REC.ATTRIBUTE19,
3461 X_ATTRIBUTE20 =>V_SUA_REC.ATTRIBUTE20,
3462 X_WLST_PRIORITY_WEIGHT_NUM =>V_SUA_REC.WLST_PRIORITY_WEIGHT_NUM,
3463 X_WLST_PREFERENCE_WEIGHT_NUM =>V_SUA_REC.WLST_PREFERENCE_WEIGHT_NUM
3464
3465 );
3466
3467
3468 ELSE
3469 FND_MESSAGE.SET_NAME('IGS','IGS_EN_BAD_SUB');
3470 IGS_GE_MSG_STACK.ADD;
3471 APP_EXCEPTION.RAISE_EXCEPTION;
3472 END IF;
3473 END LOOP;
3474 END;
3475
3476
3477 PROCEDURE delete_plansheet_subunit(
3478 P_PERSON_ID IN NUMBER,
3479 P_COURSE_CD IN VARCHAR2,
3480 P_UOOID IN NUMBER
3481 ) AS
3482
3483 CURSOR cur_plan_unit IS
3484 SELECT pl.rowid, pl.term_cal_type, pl.term_ci_sequence_number,pl.uoo_id
3485 FROM igs_en_plan_units pl
3486 WHERE pl.person_id = p_person_id
3487 AND pl.course_cd = p_course_cd
3488 AND EXISTS ( SELECT 'X'
3489 FROM IGS_PS_UNIT_OFR_OPT UOO
3490 WHERE UOO.SUP_UOO_ID = P_UOOID
3491 AND UOO.RELATION_TYPE = 'SUBORDINATE'
3492 AND UOO.UOO_ID = pl.UOO_ID)
3493
3494 ORDER BY pl.SUP_Uoo_id;
3495
3496 cur_plan_unit_rec cur_plan_unit%ROWTYPE;
3497
3498 CURSOR cur_permission_unit(cp_uoo_id IN NUMBER) IS
3499 SELECT spl.spl_perm_request_id
3500 FROM igs_en_spl_perm spl
3501 WHERE spl.student_person_id= p_person_id
3502 AND spl.uoo_id = cp_uoo_id
3503 AND spl.transaction_type <> 'WITHDRAWN';
3504
3505 cur_permission_unit_rec cur_permission_unit%ROWTYPE;
3506 l_enc_message_name VARCHAR2(2000);
3507 l_app_short_name VARCHAR2(10);
3508 l_msg_index NUMBER;
3509
3510 BEGIN
3511 FOR cur_plan_unit_rec IN cur_plan_unit LOOP
3512 --call the TBH for the planning sheet to delete the unit
3513 --by passing the rowid
3514 igs_en_plan_units_pkg.delete_row (x_rowid => cur_plan_unit_rec.rowid);
3515
3516 --call drop_permission_unit to drop the permission unit
3517
3518 FOR cur_permission_unit_rec IN cur_permission_unit(cur_plan_unit_rec.uoo_id) LOOP
3519 igs_ss_en_wrappers.remove_permission_unit(cur_permission_unit_rec.spl_perm_request_id,cur_plan_unit_rec.term_cal_type,cur_plan_unit_rec.term_ci_sequence_number, p_course_cd);
3520 END LOOP;
3521 END LOOP;
3522 END ;
3523
3524
3525
3526 BEGIN
3527
3528 p_ret_all_uoo_ids := NULL;
3529 p_ret_sub_uoo_ids := NULL;
3530 p_ret_nonsub_uoo_ids := NULL;
3531
3532 --dynamic sql to select all the units that need to be dropped for the student in context
3533 --the records are ordered by the superior unit code so that the
3534 --subordinate units are returned first since a superior unit will have a null value for SUP_UNIT_CD.
3535
3536 v_select_stmt := ' SELECT SUA.UOO_ID, SUA.SUP_UNIT_CD '
3537 || ' FROM IGS_EN_SU_ATTEMPT SUA '
3538 || ' WHERE SUA.PERSON_ID = :1 '
3539 || ' AND SUA.COURSE_CD = :2 '
3540 || ' AND SUA.UNIT_ATTEMPT_STATUS NOT IN (''DROPPED'',''DISCONTIN'',''COMPLETED'') '
3541 || ' AND (( SUA.UOO_ID IN (' || P_SELECTED_UOO_IDS || ') )'
3542 || ' OR ( EXISTS ( SELECT ''X'' '
3543 || ' FROM IGS_PS_UNIT_OFR_OPT UOO'
3544 || ' WHERE UOO.SUP_UOO_ID IN (' || P_SELECTED_UOO_IDS || ')'
3545 || ' AND UOO.RELATION_TYPE = ''SUBORDINATE'' '
3546 || ' AND UOO.UOO_ID = SUA.UOO_ID ) ) ) '
3547 || ' ORDER BY SUA.SUP_UNIT_CD ';
3548
3549 IF P_SELECTED_UOO_IDS IS NULL THEN
3550 RETURN;
3551 END IF;
3552
3553 OPEN c_sub_sua_cur_inst FOR v_select_stmt USING p_person_id, p_course_cd;
3554
3555 LOOP
3556 v_sup_unit_cd := NULL;
3557 v_uoo_id := NULL;
3558 FETCH c_sub_sua_cur_inst INTO v_uoo_id, v_sup_unit_cd;
3559 EXIT WHEN c_sub_sua_cur_inst%NOTFOUND;
3560
3561 --concatenate the uoo_ids to string that contains all the uooids with superiors first.
3562 IF p_ret_all_uoo_ids IS NULL THEN
3563 p_ret_all_uoo_ids := TO_CHAR(v_uoo_id);
3564 ELSE
3565 p_ret_all_uoo_ids := p_ret_all_uoo_ids || ',' || TO_CHAR(v_uoo_id);
3566 END IF;
3567
3568 --append all subordinate units ; v_sup_unit_cd NOT NULL implies unit is subordinate
3569 IF v_sup_unit_cd IS NOT NULL THEN
3570 IF p_ret_sub_uoo_ids IS NULL THEN
3571 p_ret_sub_uoo_ids := TO_CHAR(V_UOO_ID);
3572 ELSE
3573 p_ret_sub_uoo_ids := p_ret_sub_uoo_ids || ',' || TO_CHAR(v_uoo_id);
3574 END IF;
3575 ELSE
3576 --append nonsubordinate units
3577 IF p_ret_nonsub_uoo_ids IS NULL THEN
3578 p_ret_nonsub_uoo_ids := TO_CHAR(v_uoo_id);
3579 ELSE
3580 p_ret_nonsub_uoo_ids := p_ret_nonsub_uoo_ids || ',' || TO_CHAR(v_uoo_id);
3581 END IF;
3582 END IF;
3583
3584 --if delete_flag is set, then delete/drop the unit attmept
3585 IF NVL(p_delete_flag,'N') = 'Y' THEN
3586
3587 L_UPD_DEL_SUA(
3588 p_person_id,
3589 p_course_cd,
3590 v_uoo_id);
3591
3592 OPEN c_plan_status;
3593 FETCH c_plan_status INTO l_plan_sht_status;
3594 IF c_plan_status%FOUND THEN
3595 delete_plansheet_subunit(
3596 p_person_id ,
3597 p_course_cd ,
3598 v_uoo_id ) ;
3599 END IF;
3600 CLOSE c_plan_status;
3601 END IF;
3602
3603 END LOOP;
3604 CLOSE c_sub_sua_cur_inst;
3605
3606 END enrp_chk_del_sub_units;
3607
3608 --To Get the UNIT status
3609
3610 FUNCTION get_unit_int_status(
3611 x_person_id IN NUMBER,
3612 x_person_type IN VARCHAR2,
3613 x_load_cal_type IN VARCHAR2,
3614 x_load_sequence_number IN NUMBER,
3615 x_program_cd IN VARCHAR2,
3616 x_message OUT NOCOPY VARCHAR2,
3617 x_return_status OUT NOCOPY VARCHAR2
3618 ) RETURN VARCHAR2
3619 AS
3620
3621
3622 l_deny_warn_val VARCHAR2(10) := NULL ;
3623 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
3624 l_enr_cal_type VARCHAR2(20);
3625 l_enr_ci_seq NUMBER(20);
3626 l_enr_cat VARCHAR2(20);
3627 l_enr_comm VARCHAR2(2000);
3628
3629 l_dummy NUMBER;
3630 l_acad_cal_type igs_ca_inst.cal_type%type;
3631 l_acad_ci_sequence_number igs_ca_inst.sequence_number%type;
3632 l_acad_start_dt igs_ca_inst.start_dt%type;
3633 l_acad_end_dt igs_ca_inst.end_dt%type;
3634 l_alternate_code igs_ca_inst.alternate_code%type;
3635 l_acad_message varchar2(100);
3636 l_message VARCHAR2(1200);
3637 l_no_assessment_ind igs_en_su_attempt.no_assessment_ind%TYPE;
3638 l_return_status VARCHAR2(10);
3639 l_dummy1 VARCHAR2(200);
3640
3641 BEGIN
3642
3643 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
3644
3645 igs_en_gen_017.enrp_get_enr_method(
3646 p_enr_method_type => l_enr_meth_type,
3647 p_error_message => l_message,
3648 p_ret_status => l_return_status);
3649
3650 -- added below logic to get the Academic Calendar which is used by method enrp_get_enr_cat
3651 --
3652 -- get the academic calendar of the given Load Calendar
3653 --
3654 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
3655 p_cal_type => x_load_cal_type,
3656 p_ci_sequence_number => x_load_sequence_number,
3657 p_acad_cal_type => l_acad_cal_type,
3658 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
3659 p_acad_ci_start_dt => l_acad_start_dt,
3660 p_acad_ci_end_dt => l_acad_end_dt,
3661 p_message_name => l_acad_message );
3662
3663 IF l_acad_message IS NOT NULL THEN
3664 x_message := l_acad_message;
3665 x_return_status := 'FALSE';
3666 END IF;
3667
3668 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat(
3669 x_person_id, x_program_cd,
3670 l_acad_cal_type, l_acad_ci_sequence_number,
3671 NULL, l_enr_cal_type,
3672 l_enr_ci_seq, l_enr_comm,
3673 l_dummy1);
3674
3675 IF l_enr_comm = 'BOTH' THEN
3676 l_enr_comm :='ALL';
3677 END IF;
3678
3679
3680 l_message := NULL;
3681
3682 l_deny_warn_val := igs_ss_enr_details.get_notification(
3683 p_person_type => x_person_type,
3684 p_enrollment_category => l_enr_cat,
3685 p_comm_type => l_enr_comm,
3686 p_enr_method_type => l_enr_meth_type,
3687 p_step_group_type => 'PROGRAM',
3688 p_step_type => 'TIME_CFTP',
3689 p_person_id => x_person_id,
3690 p_message => l_message
3691 ) ;
3692 IF l_message IS NOT NULL THEN
3693 x_message := l_message;
3694 x_return_status := 'FALSE';
3695 END IF;
3696 --------------------------------------------------------------------------------------------------------------------------------------------
3697
3698 RETURN l_deny_warn_val;
3699
3700 END get_unit_int_status;
3701
3702 --procedure to update the terms SPA planning sheet status for a student
3703 PROCEDURE update_spa_plan_sts( p_n_person_id IN NUMBER,
3704 p_c_program_cd IN VARCHAR2,
3705 p_c_cal_type IN VARCHAR2,
3706 p_n_seq_num IN NUMBER,
3707 p_c_plan_sts IN VARCHAR2) IS
3708
3709 ------------------------------------------------------------------
3710 --Created by : Somasekar, Oracle IDC
3711 --Date created: 17-May-2005
3712 --
3713 --Purpose: procedure to update the terms SPA
3714 -- planning sheet status for a student.
3715 --
3716 --Known limitations/enhancements and/or remarks:
3717 --
3718 --Change History:
3719 --Who When What
3720 -------------------------------------------------------------------
3721
3722 CURSOR c_spa_terms (cp_n_person_id IN NUMBER,
3723 cp_c_program_cd IN VARCHAR2,
3724 cp_c_cal_type IN VARCHAR2,
3725 cp_n_seq_num IN NUMBER) IS
3726 SELECT spa.ROWID ROW_ID, spa.*
3727 FROM igs_en_spa_terms spa
3728 WHERE person_id = cp_n_person_id
3729 AND program_cd = cp_c_program_cd
3730 AND term_cal_type = cp_c_cal_type
3731 AND term_sequence_number = cp_n_seq_num;
3732
3733 BEGIN
3734 FOR rec_spa_terms IN c_spa_terms(p_n_person_id, p_c_program_cd, p_c_cal_type, p_n_seq_num)
3735 LOOP
3736 igs_en_spa_terms_pkg.update_row(
3737 x_rowid =>rec_spa_terms.row_id,
3738 x_term_record_id =>rec_spa_terms.term_record_id,
3739 x_person_id =>rec_spa_terms.person_id,
3740 x_program_cd =>rec_spa_terms.program_cd,
3741 x_program_version =>rec_spa_terms.program_version,
3742 x_acad_cal_type =>rec_spa_terms.acad_cal_type,
3743 x_term_cal_type =>rec_spa_terms.term_cal_type,
3744 x_term_sequence_number =>rec_spa_terms.term_sequence_number,
3745 x_key_program_flag =>rec_spa_terms.key_program_flag,
3746 x_location_cd =>rec_spa_terms.location_cd,
3747 x_attendance_mode =>rec_spa_terms.attendance_mode,
3748 x_attendance_type =>rec_spa_terms.attendance_type,
3749 x_fee_cat =>rec_spa_terms.fee_cat,
3750 x_coo_id =>rec_spa_terms.coo_id,
3751 x_class_standing_id =>rec_spa_terms.class_standing_id,
3752 x_attribute_category =>rec_spa_terms.attribute_category,
3753 x_attribute1 =>rec_spa_terms.attribute1,
3754 x_attribute2 =>rec_spa_terms.attribute2,
3755 x_attribute3 =>rec_spa_terms.attribute3,
3756 x_attribute4 =>rec_spa_terms.attribute4,
3757 x_attribute5 =>rec_spa_terms.attribute5,
3758 x_attribute6 =>rec_spa_terms.attribute6,
3759 x_attribute7 =>rec_spa_terms.attribute7,
3760 x_attribute8 =>rec_spa_terms.attribute8,
3761 x_attribute9 =>rec_spa_terms.attribute9,
3762 x_attribute10 =>rec_spa_terms.attribute10,
3763 x_attribute11 =>rec_spa_terms.attribute11,
3764 x_attribute12 =>rec_spa_terms.attribute12,
3765 x_attribute13 =>rec_spa_terms.attribute13,
3766 x_attribute14 =>rec_spa_terms.attribute14,
3767 x_attribute15 =>rec_spa_terms.attribute15,
3768 x_attribute16 =>rec_spa_terms.attribute16,
3769 x_attribute17 =>rec_spa_terms.attribute17,
3770 x_attribute18 =>rec_spa_terms.attribute18,
3771 x_attribute19 =>rec_spa_terms.attribute19,
3772 x_attribute20 =>rec_spa_terms.attribute20,
3773 x_mode =>'R',
3774 x_plan_sht_status =>p_c_plan_sts);
3775 END LOOP;
3776 END update_spa_plan_sts;
3777
3778 PROCEDURE update_grading_schema(
3779 p_person_id IN NUMBER,
3780 p_uoo_id IN NUMBER,
3781 p_course_cd IN VARCHAR2,
3782 p_grading_schema IN VARCHAR2,
3783 p_gs_version IN NUMBER,
3784 p_message OUT NOCOPY VARCHAR2,
3785 p_return_status OUT NOCOPY VARCHAR2
3786 ) AS
3787 ------------------------------------------------------------------
3788 --Created by : Vijay Rajagopal, Oracle IDC
3789 --Date created: 8-JUN-2005
3790 --
3791 --Purpose: This procedure updates the override values of grading schema of an unit attempt
3792 --
3793 --Known limitations/enhancements and/or remarks:
3794 --
3795 --Change History:
3796 --Who When What
3797 -------------------------------------------------------------------
3798 Cursor cur_grading(cp_person_id NUMBER,
3799 cp_course_cd VARCHAR2,
3800 cp_uoo_id NUMBER) IS
3801 SELECT su.ROWID , su.*
3802 FROM IGS_EN_SU_ATTEMPT_ALL su
3803 WHERE person_id = cp_person_id
3804 AND course_cd = cp_course_cd
3805 AND uoo_id = cp_uoo_id;
3806
3807 l_rowid VARCHAR2(25);
3808 l_data cur_grading%ROWTYPE;
3809 l_enc_message_name VARCHAR2(2000);
3810 l_app_short_name VARCHAR2(10);
3811 l_msg_index NUMBER;
3812 BEGIN
3813 -- select unit attempt and call update row using IGS_EN_SU_ATTEMPT_PKG
3814 OPEN cur_grading(p_person_id,p_course_cd,p_uoo_id);
3815 FETCH cur_grading INTO l_data;
3816 IF cur_grading%FOUND THEN
3817 igs_en_su_attempt_pkg.update_row (
3818 x_rowid => l_data.rowid,
3819 x_person_id => l_data.person_id,
3820 x_course_cd => l_data.course_cd,
3821 x_unit_cd => l_data.unit_cd,
3822 x_cal_type => l_data.cal_type,
3823 x_ci_sequence_number => l_data.ci_sequence_number,
3824 x_version_number => l_data.version_number,
3825 x_location_cd => l_data.location_cd,
3826 x_unit_class => l_data.unit_class,
3827 x_ci_start_dt => l_data.ci_start_dt,
3828 x_ci_end_dt => l_data.ci_end_dt,
3829 x_uoo_id => l_data.uoo_id,
3830 x_enrolled_dt => l_data.enrolled_dt,
3831 x_unit_attempt_status => l_data.unit_attempt_status,
3832 x_administrative_unit_status => l_data.administrative_unit_status,
3833 x_discontinued_dt => l_data.discontinued_dt,
3834 x_rule_waived_dt => l_data.rule_waived_dt,
3835 x_rule_waived_person_id => l_data.rule_waived_person_id,
3836 x_no_assessment_ind => l_data.no_assessment_ind,
3837 x_sup_unit_cd => l_data.sup_unit_cd,
3838 x_sup_version_number => l_data.sup_version_number,
3839 x_exam_location_cd => l_data.exam_location_cd,
3840 x_alternative_title => l_data.alternative_title,
3841 x_override_enrolled_cp => l_data.override_enrolled_cp,
3842 x_override_eftsu => l_data.override_eftsu ,
3843 x_override_achievable_cp => l_data.override_achievable_cp,
3844 x_override_outcome_due_dt => l_data.override_outcome_due_dt,
3845 x_override_credit_reason => l_data.override_credit_reason,
3846 x_administrative_priority => l_data.administrative_priority,
3847 x_waitlist_dt => l_data.waitlist_dt,
3848 x_dcnt_reason_cd => l_data.dcnt_reason_cd,
3849 x_mode => 'R',
3850 x_gs_version_number => p_gs_version,
3851 x_enr_method_type => l_data.enr_method_type,
3852 x_failed_unit_rule => l_data.failed_unit_rule,
3853 x_cart => l_data.cart,
3854 x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
3855 x_org_unit_cd => l_data.org_unit_cd,
3856 x_grading_schema_code => p_grading_schema,
3857 x_subtitle => l_data.subtitle,
3858 x_session_id => l_data.session_id,
3859 x_deg_aud_detail_id => l_data.deg_aud_detail_id,
3860 x_student_career_transcript => l_data.student_career_transcript,
3861 x_student_career_statistics => l_data.student_career_statistics,
3862 x_waitlist_manual_ind => l_data.waitlist_manual_ind,
3863 x_attribute_category => l_data.attribute_category,
3864 x_attribute1 => l_data.attribute1,
3865 x_attribute2 => l_data.attribute2,
3866 x_attribute3 => l_data.attribute3,
3867 x_attribute4 => l_data.attribute4,
3868 x_attribute5 => l_data.attribute5,
3869 x_attribute6 => l_data.attribute6,
3870 x_attribute7 => l_data.attribute7,
3871 x_attribute8 => l_data.attribute8,
3872 x_attribute9 => l_data.attribute9,
3873 x_attribute10 => l_data.attribute10,
3874 x_attribute11 => l_data.attribute11,
3875 x_attribute12 => l_data.attribute12,
3876 x_attribute13 => l_data.attribute13,
3877 x_attribute14 => l_data.attribute14,
3878 x_attribute15 => l_data.attribute15,
3879 x_attribute16 => l_data.attribute16,
3880 x_attribute17 => l_data.attribute17,
3881 x_attribute18 => l_data.attribute18,
3882 x_attribute19 => l_data.attribute19,
3883 x_attribute20 => l_data.attribute20,
3884 x_wlst_priority_weight_num => l_data.wlst_priority_weight_num,
3885 x_wlst_preference_weight_num => l_data.wlst_preference_weight_num,
3886 x_core_indicator_code => l_data.core_indicator_code ,
3887 x_upd_audit_flag => l_data.upd_audit_flag,
3888 x_ss_source_ind => l_data.ss_source_ind
3889 );
3890 END IF;
3891 CLOSE cur_grading;
3892 EXCEPTION
3893 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
3894 IF cur_grading%ISOPEN THEN
3895 CLOSE cur_grading;
3896 END IF;
3897 --set the p_message out parameter
3898 igs_ge_msg_stack.get(-1, 'T', l_enc_message_name, l_msg_index);
3899 fnd_message.parse_encoded(l_enc_message_name,l_app_short_name,p_message);
3900 p_return_status := 'DENY';
3901 WHEN OTHERS THEN
3902 p_message :='IGS_GE_UNHANDLED_EXCEPTION';
3903 p_return_status := 'DENY';
3904 END update_grading_schema;
3905
3906 PROCEDURE update_credit_points(
3907 p_person_id IN NUMBER,
3908 p_person_type IN VARCHAR2,
3909 p_load_cal_type IN VARCHAR2,
3910 p_load_sequence_number IN NUMBER,
3911 p_uoo_id IN NUMBER,
3912 p_course_cd IN VARCHAR2,
3913 p_course_version IN NUMBER,
3914 p_override_enrolled_cp IN NUMBER,
3915 p_message OUT NOCOPY VARCHAR2,
3916 p_return_status OUT NOCOPY VARCHAR2
3917 ) AS
3918 ------------------------------------------------------------------
3919 --Created by : Vijay Rajagopal, Oracle IDC
3920 --Date created: 9-JUN-2005
3921 --
3922 --Purpose: This procedure updates the override credit points of a unit attempt.
3923 --
3924 --Known limitations/enhancements and/or remarks:
3925 --
3926 --Change History:
3927 --Who When What
3928 -------------------------------------------------------------------
3929 CURSOR cur_credit(cp_person_id NUMBER,
3930 cp_course_cd VARCHAR2,
3931 cp_uoo_id NUMBER) IS
3932 SELECT su.ROWID, su.*
3933 FROM IGS_EN_SU_ATTEMPT_ALL su
3934 WHERE person_id = cp_person_id
3935 AND course_cd = cp_course_cd
3936 AND uoo_id = cp_uoo_id;
3937 l_rowid VARCHAR2(25);
3938 l_data cur_credit%ROWTYPE;
3939 l_enc_message_name VARCHAR2(2000);
3940 l_app_short_name VARCHAR2(10);
3941 l_msg_index NUMBER;
3942 BEGIN
3943 -- validate updated credit points
3944 validate_upd_cp(
3945 x_person_id => p_person_id,
3946 x_person_type => p_person_type,
3947 x_load_cal_type => p_load_cal_type,
3948 x_load_sequence_number => p_load_sequence_number,
3949 x_uoo_id => p_uoo_id,
3950 x_program_cd => p_course_cd,
3951 x_program_version => p_course_version,
3952 x_override_enrolled_cp => p_override_enrolled_cp,
3953 x_message => p_message,
3954 x_return_status => p_return_status
3955 );
3956 IF p_message IS NOT NULL THEN
3957 p_return_status := 'DENY';
3958 RETURN;
3959 END IF;
3960
3961 -- get the unit attempt information
3962 OPEN cur_credit(p_person_id,p_course_cd,p_uoo_id);
3963 FETCH cur_credit INTO l_data;
3964
3965 IF cur_credit%FOUND THEN
3966 igs_en_su_attempt_pkg.update_row (
3967 x_rowid => l_data.rowid,
3968 x_person_id => l_data.person_id,
3969 x_course_cd => l_data.course_cd,
3970 x_unit_cd => l_data.unit_cd,
3971 x_cal_type => l_data.cal_type,
3972 x_ci_sequence_number => l_data.ci_sequence_number,
3973 x_version_number => l_data.version_number,
3974 x_location_cd => l_data.location_cd,
3975 x_unit_class => l_data.unit_class,
3976 x_ci_start_dt => l_data.ci_start_dt,
3977 x_ci_end_dt => l_data.ci_end_dt,
3978 x_uoo_id => l_data.uoo_id,
3979 x_enrolled_dt => l_data.enrolled_dt,
3980 x_unit_attempt_status => l_data.unit_attempt_status,
3981 x_administrative_unit_status => l_data.administrative_unit_status,
3982 x_discontinued_dt => l_data.discontinued_dt,
3983 x_rule_waived_dt => l_data.rule_waived_dt,
3984 x_rule_waived_person_id => l_data.rule_waived_person_id,
3985 x_no_assessment_ind => l_data.no_assessment_ind,
3986 x_sup_unit_cd => l_data.sup_unit_cd,
3987 x_sup_version_number => l_data.sup_version_number,
3988 x_exam_location_cd => l_data.exam_location_cd,
3989 x_alternative_title => l_data.alternative_title,
3990 x_override_enrolled_cp => p_override_enrolled_cp,
3991 x_override_eftsu => l_data.override_eftsu ,
3992 x_override_achievable_cp => l_data.override_achievable_cp,
3993 x_override_outcome_due_dt => l_data.override_outcome_due_dt,
3994 x_override_credit_reason => l_data.override_credit_reason,
3995 x_administrative_priority => l_data.administrative_priority,
3996 x_waitlist_dt => l_data.waitlist_dt,
3997 x_dcnt_reason_cd => l_data.dcnt_reason_cd,
3998 x_mode => 'R',
3999 x_gs_version_number => l_data.gs_version_number,
4000 x_enr_method_type => l_data.enr_method_type,
4001 x_failed_unit_rule => l_data.failed_unit_rule,
4002 x_cart => l_data.cart,
4003 x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
4004 x_org_unit_cd => l_data.org_unit_cd,
4005 x_grading_schema_code => l_data.grading_schema_code,
4006 x_subtitle => l_data.subtitle,
4007 x_session_id => l_data.session_id,
4008 x_deg_aud_detail_id => l_data.deg_aud_detail_id,
4009 x_student_career_transcript => l_data.student_career_transcript,
4010 x_student_career_statistics => l_data.student_career_statistics,
4011 x_waitlist_manual_ind => l_data.waitlist_manual_ind,
4012 x_attribute_category => l_data.attribute_category,
4013 x_attribute1 => l_data.attribute1,
4014 x_attribute2 => l_data.attribute2,
4015 x_attribute3 => l_data.attribute3,
4016 x_attribute4 => l_data.attribute4,
4017 x_attribute5 => l_data.attribute5,
4018 x_attribute6 => l_data.attribute6,
4019 x_attribute7 => l_data.attribute7,
4020 x_attribute8 => l_data.attribute8,
4021 x_attribute9 => l_data.attribute9,
4022 x_attribute10 => l_data.attribute10,
4023 x_attribute11 => l_data.attribute11,
4024 x_attribute12 => l_data.attribute12,
4025 x_attribute13 => l_data.attribute13,
4026 x_attribute14 => l_data.attribute14,
4027 x_attribute15 => l_data.attribute15,
4028 x_attribute16 => l_data.attribute16,
4029 x_attribute17 => l_data.attribute17,
4030 x_attribute18 => l_data.attribute18,
4031 x_attribute19 => l_data.attribute19,
4032 x_attribute20 => l_data.attribute20,
4033 x_wlst_priority_weight_num => l_data.wlst_priority_weight_num,
4034 x_wlst_preference_weight_num => l_data.wlst_preference_weight_num,
4035 x_core_indicator_code => l_data.core_indicator_code ,
4036 x_upd_audit_flag => l_data.upd_audit_flag,
4037 x_ss_source_ind => l_data.ss_source_ind
4038 );
4039 END IF;
4040 CLOSE cur_credit;
4041
4042 EXCEPTION
4043 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4044 IF cur_credit%ISOPEN THEN
4045 CLOSE cur_credit;
4046 END IF;
4047 --set the p_message out parameter
4048 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4049 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,p_message);
4050 p_return_status := 'DENY';
4051 WHEN OTHERS THEN
4052 p_message :='IGS_GE_UNHANDLED_EXCEPTION';
4053 p_return_status := 'DENY';
4054 END update_credit_points;
4055
4056 PROCEDURE update_audit(
4057 p_person_id IN NUMBER,
4058 p_load_cal_type IN VARCHAR2,
4059 p_load_sequence_number IN NUMBER,
4060 p_uoo_id IN NUMBER,
4061 p_course_cd IN VARCHAR2,
4062 p_no_assessment_ind IN VARCHAR2,
4063 p_override_cp IN NUMBER,
4064 p_message OUT NOCOPY VARCHAR2,
4065 p_return_status OUT NOCOPY VARCHAR2
4066 ) AS
4067 ------------------------------------------------------------------
4068 --Created by : Vijay Rajagopal, Oracle IDC
4069 --Date created: 9-JUN-2005
4070 --
4071 --Purpose: This procedure updates the no_assessment_indicator of a unit attempt
4072 --
4073 --Known limitations/enhancements and/or remarks:
4074 --
4075 --Change History:
4076 --Who When What
4077 -------------------------------------------------------------------
4078 CURSOR c_usec_audit_lim IS
4079 SELECT NVL (usec.max_auditors_allowed, NVL(uv.max_auditors_allowed,999999) )
4080 FROM igs_ps_usec_lim_wlst usec,
4081 igs_ps_unit_ver uv,
4082 igs_ps_unit_ofr_opt uoo
4083 WHERE uoo.unit_cd = uv.unit_cd
4084 AND uoo.version_number = uv.version_number
4085 AND uoo.uoo_id = usec.uoo_id (+)
4086 AND uoo.uoo_id = p_uoo_id;
4087
4088 CURSOR c_audit_sua IS
4089 SELECT COUNT(*)
4090 FROM igs_en_su_attempt
4091 WHERE uoo_id=p_uoo_id
4092 AND no_assessment_ind = 'Y'
4093 AND unit_attempt_status IN ('ENROLLED', 'COMPLETED','INVALID','UNCONFIRM' ) ;
4094
4095
4096 CURSOR cur_per_typ IS
4097 SELECT person_type_code
4098 FROM igs_pe_person_types
4099 WHERE system_type = 'OTHER';
4100
4101
4102 CURSOR c_approv_perm IS
4103 SELECT approval_status
4104 FROM Igs_En_Spl_Perm
4105 WHERE student_person_id = p_person_id
4106 AND uoo_id = p_uoo_id
4107 AND request_type = 'AUDIT_PERM'
4108 AND transaction_type <> 'WITHDRAWN' ;
4109
4110 CURSOR cur_chk_au_allowed IS
4111 SELECT NVL(auditable_ind, 'N'), unit_cd, unit_class, NVL(audit_permission_ind, 'N')
4112 FROM igs_ps_unit_ofr_opt
4113 WHERE uoo_id = p_uoo_id;
4114
4115 CURSOR cur_step_def_var(cp_enr_cat igs_en_cpd_ext.enrolment_cat%TYPE,
4116 cp_enr_method igs_en_cpd_ext.enr_method_type%TYPE,
4117 cp_comm_type igs_en_cpd_ext.s_student_comm_type%TYPE,
4118 cp_step_type igs_en_cpd_ext.s_enrolment_step_type%TYPE) IS
4119 SELECT eru.stud_audit_lim
4120 FROM igs_en_cpd_ext eru, igs_lookups_view lkv
4121 WHERE eru.s_enrolment_step_type =lkv.lookup_code
4122 AND lkv.lookup_type = 'ENROLMENT_STEP_TYPE_EXT'
4123 AND lkv.step_group_type = 'UNIT'
4124 AND eru.enrolment_cat = cp_enr_cat
4125 AND eru.enr_method_type = cp_enr_method
4126 AND ( eru.s_student_comm_type = cp_comm_type OR eru.s_student_comm_type = 'ALL')
4127 AND s_enrolment_step_type = cp_step_type
4128 ORDER BY eru.step_order_num;
4129
4130 CURSOR c_crs_ver IS
4131 SELECT version_number
4132 FROM igs_en_stdnt_ps_att
4133 WHERE person_id=p_person_id
4134 AND course_cd= p_course_cd;
4135
4136 CURSOR cur_grading(cp_person_id NUMBER,
4137 cp_course_cd VARCHAR2,
4138 cp_uoo_id NUMBER) IS
4139 SELECT su.ROWID , su.*
4140 FROM IGS_EN_SU_ATTEMPT_ALL su
4141 WHERE person_id = cp_person_id
4142 AND course_cd = cp_course_cd
4143 AND uoo_id = cp_uoo_id;
4144
4145 l_enr_method_type igs_en_cpd_ext.enr_method_type%TYPE;
4146 l_cur_per_typ cur_per_typ%ROWTYPE;
4147 lv_person_type igs_pe_person_types.person_type_code%TYPE;
4148 l_alternate_code igs_ca_inst.alternate_code%TYPE;
4149 l_notification_flag igs_en_cpd_ext.notification_flag%TYPE;
4150 l_message fnd_new_messages.message_name%TYPE;
4151 l_acad_cal_type igs_ca_inst_rel.sub_cal_type%TYPE;
4152 l_approval_status igs_en_spl_perm.approval_status%TYPE;
4153 l_audit_allowed igs_ps_unit_ofr_opt.auditable_ind%TYPE;
4154 l_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
4155 l_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE;
4156 l_au_perm_req igs_ps_unit_ofr_opt.audit_permission_ind%TYPE;
4157 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
4158 l_enrol_sequence_number igs_ca_inst.sequence_number%TYPE;
4159 l_enrol_cal_type igs_ca_inst.cal_type%TYPE;
4160 l_usec_audit_lim igs_ps_usec_lim_wlst.max_auditors_allowed%TYPE;
4161 l_commencement_type igs_en_cpd_ext.s_student_comm_type%TYPE;
4162 l_step_override_limit igs_en_elgb_ovr_step.step_override_limit%TYPE;
4163 l_stud_audit_lim igs_en_cpd_ext.stud_audit_lim%TYPE;
4164 l_crs_ver igs_en_stdnt_ps_att.version_number%TYPE;
4165 l_data cur_grading%ROWTYPE;
4166 l_enrollment_category VARCHAR2(200);
4167 l_audit_sua NUMBER;
4168 l_acad_start_dt DATE;
4169 l_acad_end_dt DATE;
4170 l_categories VARCHAR2(255);
4171 l_enc_message_name VARCHAR2(2000);
4172 l_app_short_name VARCHAR2(10);
4173 l_msg_index NUMBER;
4174 l_override_eftsu igs_en_su_attempt.override_eftsu%type;
4175 l_override_enrolled_cp igs_en_su_attempt.override_enrolled_cp%TYPE;
4176 l_override_credit_reason igs_en_su_attempt.override_outcome_due_dt%TYPE;
4177 l_override_cp igs_en_su_attempt.override_enrolled_cp%TYPE;
4178
4179 BEGIN
4180 IF p_no_assessment_ind = 'N' THEN
4181 l_override_cp := NULL ;
4182 ELSE
4183 l_override_cp := p_override_cp;
4184 END IF;
4185
4186 /*** To get person type ***/
4187 OPEN cur_per_typ;
4188 FETCH cur_per_typ into l_cur_per_typ;
4189 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(p_course_cd),l_cur_per_typ.person_type_code);
4190 CLOSE cur_per_typ;
4191 /*** To get person type ***/
4192
4193 -- if student is auditing this unit attempt then check the following
4194 igs_en_gen_017.enrp_get_enr_method(
4195 p_enr_method_type => l_enr_method_type,
4196 p_error_message => p_message,
4197 p_ret_status => p_return_status);
4198 IF p_return_status = 'FALSE' THEN
4199 p_message := 'IGS_SS_EN_NOENR_METHOD';
4200 p_return_status := 'DENY' ;
4201 RETURN;
4202 END IF;
4203
4204 l_alternate_code := igs_en_gen_002.enrp_get_acad_alt_cd(
4205 p_cal_type => p_load_cal_type,
4206 p_ci_sequence_number => p_load_sequence_number,
4207 p_acad_cal_type => l_acad_cal_type,
4208 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
4209 p_acad_ci_start_dt => l_acad_start_dt,
4210 p_acad_ci_end_dt => l_acad_end_dt,
4211 p_message_name => l_message );
4212
4213 IF l_message IS NOT NULL THEN
4214 p_message:= l_message;
4215 p_return_status := 'DENY';
4216 RETURN;
4217 END IF;
4218 -- get the enrollment category and commencement type
4219 l_enrollment_category := igs_en_gen_003.enrp_get_enr_cat(
4220 p_person_id => p_person_id,
4221 p_course_cd => p_course_cd,
4222 p_cal_type => l_acad_cal_type,
4223 p_ci_sequence_number => l_acad_ci_sequence_number,
4224 p_session_enrolment_cat =>NULL,
4225 p_enrol_cal_type => l_enrol_cal_type ,
4226 p_enrol_ci_sequence_number => l_enrol_sequence_number,
4227 p_commencement_type => l_commencement_type,
4228 p_enr_categories => l_categories );
4229
4230
4231 IF p_no_assessment_ind = 'Y' THEN
4232 -- check if audit permission step is setup
4233
4234 l_notification_flag := igs_ss_enr_details.get_notification(
4235 p_person_type => lv_person_type,
4236 p_enrollment_category => l_enrollment_category,
4237 p_comm_type => l_commencement_type,
4238 p_enr_method_type => l_enr_method_type,
4239 p_step_group_type => 'UNIT',
4240 p_step_type => 'AUDIT_PERM',
4241 p_person_id => p_person_id,
4242 p_message => l_message);
4243 IF l_message IS NOT NULL THEN
4244 p_return_status := 'DENY';
4245 p_message := l_message;
4246 RETURN;
4247 END IF;
4248 -- Check whether the Audit is allowed in the given unit section
4249 -- If audit is not allowed return error message.
4250 OPEN cur_chk_au_allowed;
4251 FETCH cur_chk_au_allowed INTO l_audit_allowed,l_unit_cd, l_unit_class,l_au_perm_req;
4252 CLOSE cur_chk_au_allowed;
4253 IF l_audit_allowed = 'N' THEN
4254 p_return_status := 'DENY';
4255 p_message := 'IGS_EN_CANNOT_AUDIT';
4256 RETURN;
4257 END IF;
4258 -- Check whether Special Permission step has setup and not been overridden.
4259
4260 -- Check whether Audit Permission step has setup and not been overridden.
4261 IF l_notification_flag ='DENY' AND
4262 NOT Igs_En_Gen_015.validation_step_is_overridden (
4263 'AUDIT_PERM',
4264 p_load_cal_type,
4265 p_load_sequence_number ,
4266 p_person_id ,
4267 p_uoo_id ,
4268 l_step_override_limit
4269 ) AND l_au_perm_req = 'Y' THEN
4270 -- get the audit permission request
4271 OPEN c_approv_perm;
4272 FETCH c_approv_perm INTO l_approval_status ;
4273 IF c_approv_perm%FOUND THEN
4274 CLOSE c_approv_perm;
4275 -- if the audit permission has not been approved then show error
4276 IF l_approval_status <> 'A' THEN
4277 p_return_status := 'DENY';
4278 p_message := 'IGS_EN_DENY_AUDIT_PERM';
4279 RETURN;
4280 END IF;
4281 ELSE
4282 -- if student doesnt have any permission requests then take him to request audit permission page
4283 p_return_status := 'N';
4284 CLOSE c_approv_perm;
4285 RETURN;
4286 END IF;
4287
4288 END IF;
4289
4290 --call igs_en_elgbl_unit. eval_student_audit_limit to validate audit limit for student
4291 l_notification_flag := igs_ss_enr_details.get_notification(
4292 p_person_type => lv_person_type,
4293 p_enrollment_category => l_enrollment_category,
4294 p_comm_type => l_commencement_type,
4295 p_enr_method_type => l_enr_method_type,
4296 p_step_group_type => 'UNIT',
4297 p_step_type => 'AUDIT_LIM',
4298 p_person_id => p_person_id,
4299 p_message => l_message);
4300
4301
4302 IF l_message IS NOT NULL THEN
4303 p_return_status := 'DENY';
4304 p_message := l_message;
4305 RETURN;
4306 END IF;
4307
4308 -- get the audit limit setup at the step level
4309 OPEN cur_step_def_var(l_enrollment_category,l_enr_method_type,l_commencement_type,'AUDIT_LIM');
4310 FETCH cur_step_def_var INTO l_stud_audit_lim;
4311 CLOSE cur_step_def_var;
4312 -- if audit limit for student failed then show error
4313 IF l_notification_flag='DENY' AND
4314 NOT igs_en_elgbl_unit.eval_student_audit_limit (
4315 p_person_id => p_person_id,
4316 p_load_cal_type => p_load_cal_type,
4317 p_load_sequence_number => p_load_sequence_number,
4318 p_uoo_id => p_uoo_id,
4319 p_course_cd => p_course_cd,
4320 p_course_version => NULL,
4321 p_message => l_message,
4322 p_deny_warn => l_notification_flag,
4323 p_stud_audit_lim => l_stud_audit_lim,
4324 p_calling_obj => 'SCH_UPD'
4325 ) THEN
4326 p_return_status := 'DENY';
4327 p_message := l_message;
4328 RETURN;
4329 END IF;
4330
4331 -- check if the unit section audit limit is satisfied or not
4332 OPEN c_usec_audit_lim;
4333 FETCH c_usec_audit_lim INTO l_usec_audit_lim;
4334 CLOSE c_usec_audit_lim;
4335 OPEN c_audit_sua;
4336 FETCH c_audit_sua INTO l_audit_sua;
4337 CLOSE c_audit_sua;
4338 IF l_audit_sua >= l_usec_audit_lim THEN
4339 p_message := 'IGS_EN_AUDIT_UPD_DENY';
4340 p_return_status := 'DENY' ;
4341 RETURN;
4342 END IF;
4343 END IF; -- end of p_no_assessment_ind='Y'
4344
4345
4346 SAVEPOINT upd_audit_cp ;
4347
4348 OPEN c_crs_ver;
4349 FETCH c_crs_ver INTO l_crs_ver;
4350 CLOSE c_crs_ver;
4351 -- select unit attempt and call update row using IGS_EN_SU_ATTEMPT_PKG
4352 OPEN cur_grading(p_person_id,p_course_cd,p_uoo_id);
4353 FETCH cur_grading INTO l_data;
4354 IF cur_grading%FOUND THEN
4355
4356 IF p_no_assessment_ind = 'N' THEN
4357 l_override_enrolled_cp := NULL;
4358 l_override_eftsu := NULL;
4359 l_override_credit_reason := NULL;
4360 ELSE
4361 l_override_enrolled_cp := l_data.override_enrolled_cp;
4362 l_override_eftsu := l_data.override_eftsu ;
4363 l_override_credit_reason := l_data.override_credit_reason ;
4364 END IF;
4365
4366 igs_en_su_attempt_pkg.update_row (
4367 x_rowid => l_data.rowid,
4368 x_person_id => l_data.person_id,
4369 x_course_cd => l_data.course_cd,
4370 x_unit_cd => l_data.unit_cd,
4371 x_cal_type => l_data.cal_type,
4372 x_ci_sequence_number => l_data.ci_sequence_number,
4373 x_version_number => l_data.version_number,
4374 x_location_cd => l_data.location_cd,
4375 x_unit_class => l_data.unit_class,
4376 x_ci_start_dt => l_data.ci_start_dt,
4377 x_ci_end_dt => l_data.ci_end_dt,
4378 x_uoo_id => l_data.uoo_id,
4379 x_enrolled_dt => l_data.enrolled_dt,
4380 x_unit_attempt_status => l_data.unit_attempt_status,
4381 x_administrative_unit_status => l_data.administrative_unit_status,
4382 x_discontinued_dt => l_data.discontinued_dt,
4383 x_rule_waived_dt => l_data.rule_waived_dt,
4384 x_rule_waived_person_id => l_data.rule_waived_person_id,
4385 x_no_assessment_ind => p_no_assessment_ind,
4386 x_sup_unit_cd => l_data.sup_unit_cd,
4387 x_sup_version_number => l_data.sup_version_number,
4388 x_exam_location_cd => l_data.exam_location_cd,
4389 x_alternative_title => l_data.alternative_title,
4390 x_override_enrolled_cp => l_override_enrolled_cp,
4391 x_override_eftsu => l_override_eftsu ,
4392 x_override_achievable_cp => NULL,
4393 x_override_outcome_due_dt => l_data.override_outcome_due_dt,
4394 x_override_credit_reason => l_override_credit_reason,
4395 x_administrative_priority => l_data.administrative_priority,
4396 x_waitlist_dt => l_data.waitlist_dt,
4397 x_dcnt_reason_cd => l_data.dcnt_reason_cd,
4398 x_mode => 'R',
4399 x_gs_version_number => l_data.gs_version_number,
4400 x_enr_method_type => l_data.enr_method_type,
4401 x_failed_unit_rule => l_data.failed_unit_rule,
4402 x_cart => l_data.cart,
4403 x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
4404 x_org_unit_cd => l_data.org_unit_cd,
4405 x_grading_schema_code => l_data.grading_schema_code,
4406 x_subtitle => l_data.subtitle,
4407 x_session_id => l_data.session_id,
4408 x_deg_aud_detail_id => l_data.deg_aud_detail_id,
4409 x_student_career_transcript => l_data.student_career_transcript,
4410 x_student_career_statistics => l_data.student_career_statistics,
4411 x_waitlist_manual_ind => l_data.waitlist_manual_ind,
4412 x_attribute_category => l_data.attribute_category,
4413 x_attribute1 => l_data.attribute1,
4414 x_attribute2 => l_data.attribute2,
4415 x_attribute3 => l_data.attribute3,
4416 x_attribute4 => l_data.attribute4,
4417 x_attribute5 => l_data.attribute5,
4418 x_attribute6 => l_data.attribute6,
4419 x_attribute7 => l_data.attribute7,
4420 x_attribute8 => l_data.attribute8,
4421 x_attribute9 => l_data.attribute9,
4422 x_attribute10 => l_data.attribute10,
4423 x_attribute11 => l_data.attribute11,
4424 x_attribute12 => l_data.attribute12,
4425 x_attribute13 => l_data.attribute13,
4426 x_attribute14 => l_data.attribute14,
4427 x_attribute15 => l_data.attribute15,
4428 x_attribute16 => l_data.attribute16,
4429 x_attribute17 => l_data.attribute17,
4430 x_attribute18 => l_data.attribute18,
4431 x_attribute19 => l_data.attribute19,
4432 x_attribute20 => l_data.attribute20,
4433 x_wlst_priority_weight_num => l_data.wlst_priority_weight_num,
4434 x_wlst_preference_weight_num => l_data.wlst_preference_weight_num,
4435 x_core_indicator_code => l_data.core_indicator_code ,
4436 x_upd_audit_flag => 'N',
4437 x_ss_source_ind => l_data.ss_source_ind
4438 );
4439 END IF;
4440 CLOSE cur_grading;
4441
4442 -- perform validations for update of audit and credit points
4443 validate_upd_cp(
4444 x_person_id => p_person_id,
4445 x_person_type => lv_person_type,
4446 x_load_cal_type => p_load_cal_type,
4447 x_load_sequence_number => p_load_sequence_number,
4448 x_uoo_id => p_uoo_id,
4449 x_program_cd => p_course_cd,
4450 x_program_version => l_crs_ver,
4451 x_override_enrolled_cp => l_override_cp,
4452 x_message => p_message,
4453 x_return_status => p_return_status
4454 );
4455
4456 IF p_message IS NOT NULL THEN
4457 p_return_status :='DENY';
4458 ROLLBACK TO upd_audit_cp ;
4459 ELSE
4460 p_return_status := 'WARN';
4461 END IF;
4462 RETURN;
4463 EXCEPTION
4464 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4465 --set the p_message out parameter
4466 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4467 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,p_message);
4468 p_return_status := 'DENY';
4469 WHEN OTHERS THEN
4470 p_message :='IGS_GE_UNHANDLED_EXCEPTION';
4471 p_return_status := 'DENY';
4472
4473
4474 END update_audit;
4475
4476 PROCEDURE remove_permission_unit(p_request_id IN NUMBER,
4477 p_load_cal IN VARCHAR2,
4478 p_load_seq_num IN NUMBER,
4479 p_course_cd IN VARCHAR2) AS
4480 ------------------------------------------------------------------
4481 --Created by : Vijay Rajagopal, Oracle IDC
4482 --Date created: 11-JUN-2005
4483 --
4484 --Purpose: This procedure drops a permission unit attempt
4485 --
4486 --Known limitations/enhancements and/or remarks:
4487 --
4488 --Change History:
4489 --Who When What
4490 -------------------------------------------------------------------
4491
4492 CURSOR c_perm(cp_request_id NUMBER) IS
4493 SELECT spl.ROWID,spl.*
4494 FROM igs_en_spl_perm spl
4495 WHERE spl.spl_perm_request_id=cp_request_id;
4496 l_data c_perm%ROWTYPE;
4497
4498 BEGIN
4499 OPEN c_perm(p_request_id);
4500 FETCH c_perm INTO l_data;
4501 IF c_perm%FOUND THEN
4502 igs_en_spl_perm_pkg.update_row(
4503 x_rowid => l_data.rowid,
4504 x_spl_perm_request_id => l_data.spl_perm_request_id,
4505 x_student_person_id => l_data.student_person_id,
4506 x_uoo_id => l_data.uoo_id,
4507 x_date_submission => l_data.date_submission,
4508 x_audit_the_course => l_data.audit_the_course,
4509 x_instructor_person_id => l_data.instructor_person_id,
4510 x_approval_status => 'W',
4511 x_reason_for_request => l_data.reason_for_request,
4512 x_instructor_more_info => l_data.instructor_more_info,
4513 x_instructor_deny_info => l_data.instructor_deny_info,
4514 x_student_more_info => l_data.student_more_info,
4515 x_transaction_type => 'WITHDRAWN',
4516 x_request_type => l_data.request_type,
4517 x_mode => 'R'
4518 );
4519
4520 -- raise business event to notify instructor
4521 igs_en_workflow.raise_withdraw_perm_evt(
4522 p_n_uoo_id => l_data.UOO_ID,
4523 p_c_load_cal => p_load_cal,
4524 p_n_load_seq_num => p_load_seq_num,
4525 p_n_person_id => l_data.student_person_id,
4526 p_c_course_cd => p_course_cd,
4527 p_c_approval_type => l_data.request_type
4528 );
4529
4530 END IF;
4531 CLOSE c_perm;
4532
4533 END remove_permission_unit;
4534
4535
4536 PROCEDURE update_core_indicator(
4537 p_person_id IN NUMBER,
4538 p_uoo_id IN NUMBER,
4539 p_program_cd IN VARCHAR2,
4540 p_core_indicator IN VARCHAR2,
4541 p_message OUT NOCOPY VARCHAR2
4542 ) AS
4543 ------------------------------------------------------------------
4544 --Created by : Siva Gurusamy, Oracle IDC
4545 --Date created: 12-Aug-05
4546 --
4547 --Purpose:
4548 -- This is a new function to update the core indicator code for a unit section
4549 --Known limitations/enhancements and/or remarks:
4550 --
4551 --Change History:
4552 --Who When What
4553 --sgurusam 12-Aug-05 Created
4554 ------------1-------------------------------------------------------
4555
4556 --cursor to fetch the rowid and other attributes from the student unit attempt for the passed in information
4557 CURSOR cur_core_ind(p_person_id NUMBER,
4558 p_course_cd VARCHAR2,
4559 p_uoo_id NUMBER) IS
4560 SELECT su.ROWID, su.*
4561 FROM IGS_EN_SU_ATTEMPT_ALL su
4562 WHERE person_id = p_person_id
4563 AND course_cd = p_course_cd
4564 AND uoo_id = p_uoo_id;
4565
4566 l_rowid VARCHAR2(25);
4567 l_data cur_core_ind%ROWTYPE;
4568 l_core_indicator igs_en_su_attempt_all.core_indicator_code%TYPE;
4569 l_enc_message_name VARCHAR2(2000);
4570 l_app_short_name VARCHAR2(10);
4571 l_msg_index NUMBER;
4572 BEGIN
4573 SAVEPOINT update_core_indicator;
4574
4575 l_core_indicator := p_core_indicator;
4576
4577 -- select unit attempt and call update row using IGS_EN_SU_ATTEMPT_PKG
4578 OPEN cur_core_ind(p_person_id, p_program_cd, p_uoo_id);
4579 FETCH cur_core_ind INTO l_data;
4580 CLOSE cur_core_ind;
4581
4582 -- IF length(l_core_indicator)=0 THEN
4583 -- l_core_indicator := p_core_indicator;
4584 -- END IF;
4585
4586 -- make call to update_row of IGS_EN_SU_ATTEMPT_PKG to update core indicator code value l_core_indicator
4587 Igs_En_Su_Attempt_Pkg.update_row(
4588 x_rowid =>l_data.ROWID,
4589 x_person_id =>l_data.person_id,
4590 x_course_cd =>l_data.course_cd,
4591 x_unit_cd =>l_data.unit_cd,
4592 x_cal_type =>l_data.cal_type,
4593 x_ci_sequence_number =>l_data.ci_sequence_number,
4594 x_version_number =>l_data.version_number,
4595 x_location_cd =>l_data.location_cd,
4596 x_unit_class =>l_data.unit_class,
4597 x_ci_start_dt =>l_data.ci_start_dt,
4598 x_ci_end_dt =>l_data.ci_end_dt,
4599 x_uoo_id =>l_data.uoo_id,
4600 x_enrolled_dt =>l_data.enrolled_dt,
4601 x_unit_attempt_status => l_data.unit_attempt_status,
4602 x_administrative_unit_status => l_data.administrative_unit_status,
4603 x_discontinued_dt =>l_data.discontinued_dt,
4604 x_dcnt_reason_cd =>l_data.dcnt_reason_cd ,
4605 x_rule_waived_dt =>l_data.rule_waived_dt,
4606 x_rule_waived_person_id =>l_data.rule_waived_person_id,
4607 x_no_assessment_ind =>l_data.no_assessment_ind,
4608 x_sup_unit_cd =>l_data.sup_unit_cd,
4609 x_sup_version_number =>l_data.sup_version_number,
4610 x_exam_location_cd =>l_data.exam_location_cd,
4611 x_alternative_title =>l_data.alternative_title,
4612 x_override_enrolled_cp =>l_data.override_enrolled_cp ,
4613 x_override_eftsu =>l_data.override_eftsu,
4614 x_override_achievable_cp =>l_data.override_achievable_cp,
4615 x_override_outcome_due_dt =>l_data.override_outcome_due_dt,
4616 x_override_credit_reason =>l_data.override_credit_reason,
4617 x_administrative_priority =>l_data.administrative_priority,
4618 x_waitlist_dt =>l_data.waitlist_dt,
4619 x_gs_version_number => l_data.gs_version_number,
4620 x_enr_method_type => l_data.enr_method_type,
4621 x_failed_unit_rule => l_data.failed_unit_rule,
4622 x_cart => l_data.cart,
4623 x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
4624 x_mode => 'R',
4625 x_org_unit_cd => l_data.org_unit_cd,
4626 x_session_id => l_data.session_id,
4627 x_grading_schema_code => l_data.grading_schema_code,
4628 x_deg_aud_detail_id => l_data.deg_aud_detail_id,
4629 x_student_career_transcript => l_data.student_career_transcript,
4630 x_student_career_statistics => l_data.student_career_statistics,
4631 x_subtitle => l_data.subtitle,
4632 x_waitlist_manual_ind => l_data.waitlist_manual_ind,
4633 x_attribute_category => l_data.attribute_category,
4634 x_attribute1 => l_data.attribute1,
4635 x_attribute2 => l_data.attribute2,
4636 x_attribute3 => l_data.attribute3,
4637 x_attribute4 => l_data.attribute4,
4638 x_attribute5 => l_data.attribute5,
4639 x_attribute6 => l_data.attribute6,
4640 x_attribute7 => l_data.attribute7,
4641 x_attribute8 => l_data.attribute8,
4642 x_attribute9 => l_data.attribute9,
4643 x_attribute10 => l_data.attribute10,
4644 x_attribute11 => l_data.attribute11,
4645 x_attribute12 => l_data.attribute12,
4646 x_attribute13 => l_data.attribute13,
4647 x_attribute14 => l_data.attribute14,
4648 x_attribute15 => l_data.attribute15,
4649 x_attribute16 => l_data.attribute16,
4650 x_attribute17 => l_data.attribute17,
4651 x_attribute18 => l_data.attribute18,
4652 x_attribute19 => l_data.attribute19,
4653 x_attribute20 => l_data.attribute20,
4654 X_WLST_PRIORITY_WEIGHT_NUM => l_data.wlst_priority_weight_num,
4655 X_WLST_PREFERENCE_WEIGHT_NUM=> l_data.wlst_preference_weight_num,
4656 X_CORE_INDICATOR_CODE => l_core_indicator,
4657 X_UPD_AUDIT_FLAG => l_data.upd_audit_flag,
4658 X_SS_SOURCE_IND => l_data.ss_source_ind
4659 );
4660 EXCEPTION
4661 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
4662 ROLLBACK TO update_core_indicator;
4663 --set the p_message out parameter
4664 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
4665 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,p_message);
4666 WHEN OTHERS THEN
4667 ROLLBACK TO update_core_indicator;
4668 p_message :='IGS_GE_UNHANDLED_EXCEPTION';
4669 END update_core_indicator;
4670
4671
4672 PROCEDURE check_en_security( p_person_id IN NUMBER,
4673 p_course_cd IN VARCHAR2,
4674 p_uoo_id IN NUMBER,
4675 p_table IN VARCHAR2,
4676 p_mode IN VARCHAR2,
4677 p_select_allowed OUT NOCOPY VARCHAR2,
4678 p_update_allowed OUT NOCOPY VARCHAR2,
4679 p_message OUT NOCOPY VARCHAR2
4680 )
4681 ------------------------------------------------------------------
4682 --Created by : Siva Gurusamy, Oracle IDC
4683 --Date created: 12-Aug-05
4684 --
4685 --Purpose:
4686 -- This is a new function to check the securty levels for the schedule page
4687 -- Implementation
4688 -- 1. Checks if logged in user has select/update access to the Student's program attempt object and update access to the Student's unit attempt object
4689 -- 2. If p_mode is B, then checks if logged in user has select/update access to the Student's program attempt object
4690 -- 3. If value of Out parameter p_select_allowed is 'Y', the user has select access to the student's program attempt object
4691 -- 4. If value of Out parameter p_update_allowed is 'Y', the user has update access to the student's program/unit attempt object
4692 --Known limitations/enhancements and/or remarks:
4693 --
4694 --Change History:
4695 --Who When What
4696 --sgurusam 12-Aug-05 Created
4697 -------------------------------------------------------------------
4698
4699 AS
4700 --cursor to get the rowid for the program attempt of the student
4701 CURSOR cur_program(p_person_id NUMBER, p_course_cd VARCHAR2) IS
4702 SELECT spat.ROWID
4703 FROM igs_en_stdnt_ps_att_all spat
4704 WHERE spat.person_id = p_person_id
4705 AND spat.course_cd = p_course_cd;
4706
4707 --cursor to get the rowid for the unit attempt of the student
4708 CURSOR cur_unit(p_person_id NUMBER, p_course_cd VARCHAR2, p_uoo_id IN NUMBER) IS
4709 SELECT sua.ROWID
4710 FROM igs_en_su_attempt_all sua
4711 WHERE sua.person_id = p_person_id
4712 AND sua.course_cd = p_course_cd
4713 AND sua.uoo_id = p_uoo_id;
4714
4715 l_program_rowid VARCHAR2(25);
4716 l_unit_rowid VARCHAR2(25);
4717 l_msg_data VARCHAR2(4000);
4718 l_sec_out BOOLEAN;
4719 BEGIN
4720
4721 l_sec_out := FALSE;
4722 p_select_allowed := 'N';
4723 p_update_allowed := 'N';
4724 l_program_rowid := NULL;
4725
4726 -- get the program attempt rowid
4727 IF p_table ='IGS_EN_STDNT_PS_ATT_ALL' THEN
4728 --get the rowid for program attempt
4729 OPEN cur_program(p_person_id,p_course_cd);
4730 FETCH cur_program INTO l_program_rowid;
4731 CLOSE cur_program;
4732 END IF;
4733
4734 --if program attempt exists, check if logged in user has update access to the student's program attempt object
4735 IF (l_program_rowid IS NOT NULL) THEN
4736 IF p_mode = 'B' THEN
4737 -- check for query permission on the table
4738 l_sec_out := igs_sc_gen_001.check_sel_upd_del_security (
4739 p_tab_name => 'IGS_EN_STDNT_PS_ATT_SV',
4740 p_rowid => l_program_rowid,
4741 p_action => 'S',
4742 p_msg_data => l_msg_data);
4743
4744 IF NOT l_sec_out THEN
4745 p_message := 'IGS_SC_NO_ACCESS_PRIV';
4746 IF l_msg_data IS NOT NULL THEN
4747 p_message := 'IGS_SC_PRE_CHECK_EXCEP*'||l_msg_data||'*IGS_EN_STDNT_PS_ATT_SV*S';
4748 END IF;
4749 RETURN;
4750 END IF;
4751
4752 IF l_sec_out THEN
4753 p_select_allowed := 'Y';
4754 END IF;
4755
4756 l_msg_data := NULL;
4757
4758 -- check for update permission on the table
4759 l_sec_out := Igs_sc_gen_001.check_sel_upd_del_security(
4760 p_tab_name => p_table,
4761 p_rowid => l_program_rowid,
4762 p_action => 'U',
4763 p_msg_data => l_msg_data);
4764
4765 IF NOT l_sec_out THEN
4766 p_message := 'IGS_SC_NO_ACCESS_PRIV';
4767 IF l_msg_data IS NOT NULL THEN
4768 p_message := 'IGS_SC_PRE_CHECK_EXCEP*'||l_msg_data||'*'||p_table||'*U';
4769 END IF;
4770 RETURN;
4771 END IF;
4772
4773 IF l_sec_out THEN
4774 p_update_allowed := 'Y';
4775 END IF;
4776
4777 ELSE
4778 -- check for mode permission on the table
4779 l_sec_out := Igs_sc_gen_001.check_sel_upd_del_security(
4780 p_tab_name => p_table,
4781 p_rowid => l_program_rowid,
4782 p_action => p_mode,
4783 p_msg_data => l_msg_data);
4784
4785 IF NOT l_sec_out THEN
4786 p_message := 'IGS_SC_NO_ACCESS_PRIV';
4787 IF l_msg_data IS NOT NULL THEN
4788 p_message := 'IGS_SC_PRE_CHECK_EXCEP*'||l_msg_data||'*'||p_table||'*'||p_mode;
4789 END IF;
4790 RETURN;
4791 END IF;
4792
4793 IF l_sec_out THEN
4794 IF p_mode = 'S' THEN
4795 p_select_allowed := 'Y';
4796 ELSE
4797 p_update_allowed := 'Y';
4798 END IF;
4799 END IF;
4800
4801 END IF;
4802 RETURN;
4803 END IF;
4804
4805 l_unit_rowid := NULL;
4806
4807 -- get the unit attempt rowid
4808 IF p_uoo_id IS NOT NULL AND p_table = 'IGS_EN_SU_ATTEMPT_ALL' THEN
4809 -- get the row id for unit attempt
4810 OPEN cur_unit(p_person_id,p_course_cd,p_uoo_id);
4811 FETCH cur_unit INTO l_unit_rowid;
4812 CLOSE cur_unit;
4813 END IF;
4814
4815 --if program attempt exists, check if logged in user has update access to the student's program attempt object
4816 --Checking for unit Updates
4817 IF (l_unit_rowid IS NOT NULL) THEN
4818 -- check for update permission on the table student unit attempt
4819 l_sec_out := Igs_sc_gen_001.check_sel_upd_del_security(
4820 p_tab_name => 'IGS_EN_SU_ATTEMPT_ALL',
4821 p_rowid => l_unit_rowid,
4822 p_action => 'U',
4823 p_msg_data => l_msg_data);
4824
4825 IF NOT l_sec_out THEN
4826 p_message := 'IGS_SC_NO_ACCESS_PRIV';
4827 IF l_msg_data IS NOT NULL THEN
4828 p_message := 'IGS_SC_PRE_CHECK_EXCEP*'||l_msg_data||'*IGS_EN_SU_ATTEMPT_ALL*U';
4829 END IF;
4830 RETURN;
4831 END IF;
4832
4833 IF l_sec_out THEN
4834 p_update_allowed := 'Y';
4835 END IF;
4836 END IF;
4837
4838 --set the out parameter to specify if update of program attempt object is allowed or not
4839 END check_en_security;
4840
4841 PROCEDURE update_audit_flag(p_person_id IN NUMBER,
4842 p_course_cd IN VARCHAR2,
4843 p_uoo_id IN NUMBER,
4844 p_upd_audit_flag IN VARCHAR2)
4845 AS
4846 CURSOR c_sua (p_person_id IN NUMBER,
4847 p_course_cd IN VARCHAR2,
4848 p_uoo_id IN NUMBER)
4849 IS
4850 SELECT *
4851 FROM igs_en_su_attempt
4852 WHERE person_id = p_person_id
4853 AND course_cd = p_course_cd
4854 AND uoo_id = p_uoo_id;
4855
4856 l_sua_rec c_sua%ROWTYPE;
4857 BEGIN
4858
4859 OPEN c_sua(p_person_id, p_course_cd, p_uoo_id);
4860 FETCH c_sua INTO l_sua_rec;
4861 IF c_sua%FOUND THEN
4862 IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW( -- calling the TBH
4863 X_ROWID => l_sua_rec.ROW_ID,
4864 X_PERSON_ID => l_sua_rec.PERSON_ID,
4865 X_COURSE_CD => l_sua_rec.COURSE_CD ,
4866 X_UNIT_CD => l_sua_rec.UNIT_CD,
4867 X_CAL_TYPE => l_sua_rec.CAL_TYPE,
4868 X_CI_SEQUENCE_NUMBER => l_sua_rec.CI_SEQUENCE_NUMBER ,
4869 X_VERSION_NUMBER => l_sua_rec.VERSION_NUMBER ,
4870 X_LOCATION_CD => l_sua_rec.LOCATION_CD,
4871 X_UNIT_CLASS => l_sua_rec.UNIT_CLASS ,
4872 X_CI_START_DT => l_sua_rec.CI_START_DT,
4873 X_CI_END_DT => l_sua_rec.CI_END_DT,
4874 X_UOO_ID => l_sua_rec.UOO_ID ,
4875 X_ENROLLED_DT => l_sua_rec.ENROLLED_DT,
4876 X_UNIT_ATTEMPT_STATUS => l_sua_rec.UNIT_ATTEMPT_STATUS,
4877 X_ADMINISTRATIVE_UNIT_STATUS => l_sua_rec.administrative_unit_status,
4878 X_ADMINISTRATIVE_PRIORITY => l_sua_rec.administrative_PRIORITY,
4879 X_DISCONTINUED_DT => l_sua_rec.discontinued_dt,
4880 X_DCNT_REASON_CD => l_sua_rec.DCNT_REASON_CD,
4881 X_RULE_WAIVED_DT => l_sua_rec.RULE_WAIVED_DT ,
4882 X_RULE_WAIVED_PERSON_ID => l_sua_rec.RULE_WAIVED_PERSON_ID ,
4883 X_NO_ASSESSMENT_IND => l_sua_rec.NO_ASSESSMENT_IND,
4884 X_SUP_UNIT_CD => l_sua_rec.SUP_UNIT_CD ,
4885 X_SUP_VERSION_NUMBER => l_sua_rec.SUP_VERSION_NUMBER,
4886 X_EXAM_LOCATION_CD => l_sua_rec.EXAM_LOCATION_CD,
4887 X_ALTERNATIVE_TITLE => l_sua_rec.ALTERNATIVE_TITLE,
4888 X_OVERRIDE_ENROLLED_CP => l_sua_rec.OVERRIDE_ENROLLED_CP,
4889 X_OVERRIDE_EFTSU => l_sua_rec.OVERRIDE_EFTSU ,
4890 X_OVERRIDE_ACHIEVABLE_CP => l_sua_rec.OVERRIDE_ACHIEVABLE_CP,
4891 X_OVERRIDE_OUTCOME_DUE_DT => l_sua_rec.OVERRIDE_OUTCOME_DUE_DT,
4892 X_OVERRIDE_CREDIT_REASON => l_sua_rec.OVERRIDE_CREDIT_REASON,
4893 X_WAITLIST_DT => l_sua_rec.waitlist_dt,
4894 X_MODE => 'R',
4895 X_GS_VERSION_NUMBER => l_sua_rec.gs_version_number,
4896 X_ENR_METHOD_TYPE => l_sua_rec.enr_method_type,
4897 X_FAILED_UNIT_RULE => l_sua_rec.FAILED_UNIT_RULE,
4898 X_CART => l_sua_rec.CART,
4899 X_RSV_SEAT_EXT_ID => l_sua_rec.RSV_SEAT_EXT_ID ,
4900 X_ORG_UNIT_CD => l_sua_rec.org_unit_cd ,
4901 X_SESSION_ID => l_sua_rec.session_id,
4902 X_GRADING_SCHEMA_CODE => l_sua_rec.grading_schema_code,
4903 X_DEG_AUD_DETAIL_ID => l_sua_rec.deg_aud_detail_id,
4904 X_SUBTITLE => l_sua_rec.subtitle,
4905 X_STUDENT_CAREER_TRANSCRIPT => l_sua_rec.student_career_transcript,
4906 X_STUDENT_CAREER_STATISTICS => l_sua_rec.student_career_statistics,
4907 X_ATTRIBUTE_CATEGORY => l_sua_rec.attribute_category,
4908 X_ATTRIBUTE1 => l_sua_rec.attribute1,
4909 X_ATTRIBUTE2 => l_sua_rec.attribute2,
4910 X_ATTRIBUTE3 => l_sua_rec.attribute3,
4911 X_ATTRIBUTE4 => l_sua_rec.attribute4,
4912 X_ATTRIBUTE5 => l_sua_rec.attribute5,
4913 X_ATTRIBUTE6 => l_sua_rec.attribute6,
4914 X_ATTRIBUTE7 => l_sua_rec.attribute7,
4915 X_ATTRIBUTE8 => l_sua_rec.attribute8,
4916 X_ATTRIBUTE9 => l_sua_rec.attribute9,
4917 X_ATTRIBUTE10 => l_sua_rec.attribute10,
4918 X_ATTRIBUTE11 => l_sua_rec.attribute11,
4919 X_ATTRIBUTE12 => l_sua_rec.attribute12,
4920 X_ATTRIBUTE13 => l_sua_rec.attribute13,
4921 X_ATTRIBUTE14 => l_sua_rec.attribute14,
4922 X_ATTRIBUTE15 => l_sua_rec.attribute15,
4923 X_ATTRIBUTE16 => l_sua_rec.attribute16,
4924 X_ATTRIBUTE17 => l_sua_rec.attribute17,
4925 X_ATTRIBUTE18 => l_sua_rec.attribute18,
4926 X_ATTRIBUTE19 => l_sua_rec.attribute19,
4927 X_ATTRIBUTE20 => l_sua_rec.attribute20,
4928 X_WAITLIST_MANUAL_IND => l_sua_rec.waitlist_manual_ind ,
4929 X_WLST_PRIORITY_WEIGHT_NUM => l_sua_rec.wlst_priority_weight_num,
4930 X_WLST_PREFERENCE_WEIGHT_NUM => l_sua_rec.wlst_preference_weight_num,
4931 X_CORE_INDICATOR_CODE => l_sua_rec.core_indicator_code,
4932 X_UPD_AUDIT_FLAG => p_upd_audit_flag, -- updating audit flag
4933 X_SS_SOURCE_IND => l_sua_rec.ss_source_ind
4934 );
4935 END IF;
4936 CLOSE c_sua;
4937
4938 END update_audit_flag;
4939
4940 FUNCTION check_perm_exists(p_person_id IN NUMBER,
4941 p_uoo_id IN NUMBER,
4942 p_request_type IN VARCHAR2) return varchar2 as
4943
4944
4945 CURSOR cur_perm_exists is
4946 SELECT 'X' FROM igs_en_spl_perm perm
4947 WHERE perm.uoo_id = p_uoo_id
4948 AND perm.student_person_id = p_person_id
4949 AND perm.approval_status <> 'A'
4950 AND perm.request_type <> p_request_type;
4951
4952 l_perm_exists varchar2(1);
4953 begin
4954 l_perm_exists := null;
4955
4956 OPEN cur_perm_exists;
4957 FETCH cur_perm_exists into l_perm_exists;
4958 CLOSE cur_perm_exists;
4959
4960 IF l_perm_exists is NOT NULL THEN
4961 RETURN 'Y';
4962 ELSE
4963 RETURN 'N';
4964 END IF;
4965 END check_perm_exists;
4966
4967 FUNCTION check_sua_exists(p_person_id IN NUMBER,
4968 p_uoo_id IN NUMBER,
4969 p_course_cd IN VARCHAR2) return varchar2 as
4970 CURSOR cur_sua_exists is
4971 SELECT 'X' FROM igs_en_su_attempt sua
4972 WHERE sua.person_id = p_person_id
4973 AND sua.course_Cd = p_course_cd
4974 AND sua.uoo_id = p_uoo_id;
4975
4976 l_sua_exists varchar2(1);
4977 begin
4978 l_sua_exists := null;
4979
4980 OPEN cur_sua_exists;
4981 FETCH cur_sua_exists into l_sua_exists;
4982 CLOSE cur_sua_exists;
4983
4984 IF l_sua_exists is NOT NULL THEN
4985 RETURN 'Y';
4986 ELSE
4987 RETURN 'N';
4988 END IF;
4989 END check_sua_exists;
4990
4991
4992 PROCEDURE chk_cart_units(p_person_id IN NUMBER,
4993 p_course_cd IN VARCHAR2,
4994 p_load_cal_type IN VARCHAR2,
4995 p_load_sequence_number IN NUMBER,
4996 p_cart_exists OUT NOCOPY VARCHAR2
4997 ) AS
4998
4999 ------------------------------------------------------------------
5000 --Created by : Basanth Devisetty, Oracle IDC
5001 --Date created: 12-Feb-06
5002 --
5003 --Purpose: To check if any unconfirm unit attempts exists in the cart
5004 --
5005 --Change History:
5006 --Who When What
5007 --bdeviset 09-MAR-06 Created for Bug# 5072814
5008 -------------------------------------------------------------------
5009
5010 CURSOR c_chk_cart_units IS
5011 SELECT 'x'
5012 FROM igs_en_su_attempt sua,
5013 igs_ca_teach_to_load_v ltt
5014 WHERE sua.person_id = p_person_id
5015 AND sua.course_cd = p_course_cd
5016 AND sua.unit_attempt_status = 'UNCONFIRM'
5017 AND NVL(sua.SS_SOURCE_IND,'N') <> 'S'
5018 AND ltt.load_cal_type = p_load_cal_type
5019 AND ltt.load_ci_sequence_number = p_load_sequence_number
5020 AND ltt.teach_cal_type = sua.cal_type
5021 AND ltt.teach_ci_sequence_number = sua.ci_sequence_number;
5022
5023
5024 l_dummy VARCHAR2(1);
5025 BEGIN
5026 p_cart_exists := 'FALSE';
5027 OPEN c_chk_cart_units;
5028 FETCH c_chk_cart_units INTO l_dummy;
5029 IF c_chk_cart_units%FOUND THEN
5030 p_cart_exists := 'TRUE';
5031 END IF;
5032 CLOSE c_chk_cart_units;
5033
5034 END chk_cart_units;
5035
5036 END igs_ss_en_wrappers;