[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_PLAN_UTILS
Source
1 PACKAGE BODY IGS_EN_PLAN_UTILS AS
2 /* $Header: IGSEN94B.pls 120.12 2006/08/24 10:20:04 bdeviset noship $ */
3
4 --Procedure to check whether the student has core override for a section.
5 FUNCTION check_core_override( p_person_id IN NUMBER,
6 p_course_cd IN VARCHAR2,
7 p_uoo_id IN NUMBER,
8 p_term_cal IN VARCHAR2,
9 p_term_seq_num IN NUMBER) RETURN BOOLEAN AS
10 l_deny_warn VARCHAR2(10); --DENY / WARN Flag
11 BEGIN
12 -- Call the procedure to evaluate core unit drop
13 IF igs_en_gen_015.eval_core_unit_drop (p_person_id => p_person_id,
14 p_course_cd => p_course_cd,
15 p_uoo_id => p_uoo_id,
16 p_step_type => 'DROP_CORE',
17 p_term_cal => p_term_cal,
18 p_term_sequence_number => p_term_seq_num,
19 p_deny_warn => l_deny_warn,
20 p_enr_method => null) = 'TRUE' THEN
21 RETURN TRUE;
22 ELSE
23 RETURN FALSE;
24 END IF;
25 END check_core_override;
26
27 PROCEDURE add_units_to_plan(p_person_id IN NUMBER,
28 p_course_cd IN VARCHAR2,
29 p_load_cal_type IN VARCHAR2,
30 p_load_sequence_number IN NUMBER,
31 p_uoo_ids IN VARCHAR2,
32 p_return_status OUT NOCOPY VARCHAR2,
33 p_message_name OUT NOCOPY VARCHAR2,
34 p_ss_session_id IN NUMBER) AS
35
36 CURSOR c_get_unitSection(cp_uooid IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
37 SELECT unit_cd || '/' || unit_class
38 FROM IGS_PS_UNIT_OFR_OPT
39 WHERE uoo_id = cp_uooid;
40
41 l_strtpoint NUMBER;
42 l_endpoint NUMBER;
43 l_cindex NUMBER;
44 l_pre_cindex NUMBER;
45 l_nth_occurence NUMBER;
46
47 l_uooid_audit_sep_index NUMBER;
48 l_audit_gscd_sep_index NUMBER;
49 l_gscd_gsver_sep_index NUMBER;
50 l_gsver_credits_sep_index NUMBER;
51 l_credits_poo_index NUMBER;
52
53 l_unitcd VARCHAR2(200);
54 l_uooid NUMBER;
55 l_audit VARCHAR2(1);
56 l_grading_code VARCHAR2(100);
57 l_grading_version NUMBER;
58 l_credits NUMBER;
59 l_unit_dtls_to_be_added VARCHAR2(3000);
60 l_uooid_audit_gscd_gsver_crdts VARCHAR2(3000);
61
62 l_sup_uooid NUMBER;
63
64 l_enc_message_name VARCHAR2(2000);
65 l_app_short_name VARCHAR2(10);
66 l_message_name VARCHAR2(100);
67 l_mesg_txt VARCHAR2(4000);
68 l_msg_index NUMBER;
69 l_row_id VARCHAR2(1000);
70 cst_error CONSTANT VARCHAR2(5) := 'E';
71
72
73
74 BEGIN
75
76 igs_en_add_units_api.g_ss_session_id := p_ss_session_id;
77
78 l_strtpoint := 0;
79 l_pre_cindex := 0;
80 l_nth_occurence := 1;
81 l_unit_dtls_to_be_added := p_uoo_ids;
82 l_cindex := INSTR(l_unit_dtls_to_be_added,';',1,l_nth_occurence);
83
84 WHILE (l_cindex <> 0 ) LOOP
85
86
87 l_strtpoint := l_pre_cindex + 1;
88 l_endpoint := l_cindex - l_strtpoint;
89 l_pre_cindex := l_cindex;
90 l_uooid_audit_gscd_gsver_crdts := substr(l_unit_dtls_to_be_added,l_strtpoint,l_endpoint);
91
92 l_uooid_audit_sep_index := INSTR(l_uooid_audit_gscd_gsver_crdts,',',1);
93 l_audit_gscd_sep_index := INSTR(l_uooid_audit_gscd_gsver_crdts,',',1,2);
94 l_gscd_gsver_sep_index := INSTR(l_uooid_audit_gscd_gsver_crdts,',',1,3);
95 l_gsver_credits_sep_index := INSTR(l_uooid_audit_gscd_gsver_crdts,',',1,4);
96 l_credits_poo_index := INSTR(l_uooid_audit_gscd_gsver_crdts,',',1,5);
97
98 l_uooid := TO_NUMBER(SUBSTR(l_uooid_audit_gscd_gsver_crdts,1,l_uooid_audit_sep_index - 1));
99 l_audit := SUBSTR(l_uooid_audit_gscd_gsver_crdts,l_uooid_audit_sep_index+1,l_audit_gscd_sep_index -(l_uooid_audit_sep_index+1));
100 l_grading_code := SUBSTR(l_uooid_audit_gscd_gsver_crdts,l_audit_gscd_sep_index + 1,l_gscd_gsver_sep_index - (l_audit_gscd_sep_index+1));
101 l_grading_version := TO_NUMBER(SUBSTR(l_uooid_audit_gscd_gsver_crdts,l_gscd_gsver_sep_index + 1,l_gsver_credits_sep_index - (l_gscd_gsver_sep_index+1)));
102 IF l_credits_poo_index = 0 THEN
103 l_credits := TO_NUMBER(SUBSTR(l_uooid_audit_gscd_gsver_crdts,l_gsver_credits_sep_index + 1));
104 ELSE
105 l_credits := TO_NUMBER(SUBSTR(l_uooid_audit_gscd_gsver_crdts,l_gsver_credits_sep_index + 1,l_credits_poo_index - (l_gsver_credits_sep_index+1)));
106 END IF;
107
108 OPEN c_get_unitSection(l_uooid);
109 FETCH c_get_unitSection INTO l_unitcd;
110 CLOSE c_get_unitSection;
111
112 BEGIN
113 IGS_EN_PLAN_UNITS_PKG.INSERT_ROW(
114 x_rowid => l_row_id,
115 x_person_id => p_person_id,
116 x_course_cd => p_course_cd,
117 x_uoo_id => l_uooid,
118 x_term_cal_type => p_load_cal_type,
119 x_term_ci_sequence_number => p_load_sequence_number,
120 x_no_assessment_ind => l_audit,
121 x_sup_uoo_id => NULL,
122 x_override_enrolled_cp => l_credits,
123 x_grading_schema_code => l_grading_code,
124 x_gs_version_number => l_grading_version,
125 x_core_indicator_code => NULL,
126 x_alternative_title => NULL,
127 x_cart_error_flag => 'N',
128 x_session_id => igs_en_add_units_api.g_ss_session_id,
129 x_mode => 'R');
130 EXCEPTION
131 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
132 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
133 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
134 IF l_message_name = 'IGS_GE_RECORD_ALREADY_EXISTS' THEN
135 IF p_message_name IS NOT NULL THEN
136 p_message_name := p_message_name || ';';
137 END IF;
138 p_message_name := p_message_name || 'IGS_EN_PLAN_RECORD_EXISTS'||'*'||l_unitcd;
139 p_return_status := 'FALSE';
140 ELSE
141 RAISE;
142 END IF;
143 END;
144
145 l_nth_occurence := l_nth_occurence + 1;
146 l_cindex := INSTR(l_unit_dtls_to_be_added,';',1,l_nth_occurence);
147
148 END LOOP;
149
150 igs_en_add_units_api.g_ss_session_id := NULL;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 igs_en_add_units_api.g_ss_session_id := NULL;
155 p_message_name := 'IGS_GE_UNHANDLED_EXP';
156 p_return_status := 'FALSE';
157 RAISE;
158 END add_units_to_plan;
159
160 PROCEDURE update_spa_terms_plan_sht_flag(
161 P_PERSON_ID IN NUMBER,
162 P_COURSE_CD IN VARCHAR2,
163 P_TERM_CAL_TYPE IN VARCHAR2,
164 P_TERM_SEQUENCE_NUMBER IN NUMBER,
165 P_PLAN_SHT_FLAG IN VARCHAR2
166 ) AS
167 ------------------------------------------------------------------
168 --Created by : ctyagi, Oracle IDC
169 --Date created: 18-JULY-2005
170 --
171 --Purpose: update plan_sht_status column of igs_en_spa_terms.
172 --
173 --Known limitations/enhancements and/or remarks:
174 --
175 --Change History:
176 --Who When What
177 -------------------------------------------------------------------
178
179 CURSOR select_rec IS
180 SELECT spt.rowid,spt.*
181 FROM igs_en_spa_terms spt
182 WHERE PERSON_ID=P_PERSON_ID
183 AND PROGRAM_CD=P_COURSE_CD
184 AND TERM_CAL_TYPE=P_TERM_CAL_TYPE
185 AND TERM_SEQUENCE_NUMBER=P_TERM_SEQUENCE_NUMBER;
186
187 old_references select_rec%ROWTYPE;
188
189
190
191
192 l_message_name VARCHAR2(2000);
193 l_plan_sht_status igs_en_spa_terms.plan_sht_status%TYPE;
194
195 BEGIN
196 OPEN select_rec ;
197 FETCH select_rec INTO old_references;
198
199 IF select_rec%NOTFOUND THEN
200 IF P_PLAN_SHT_FLAG = 'SKIP' THEN
201
202 IF p_person_id IS NOT NULL THEN
203 -- Call the API to Create/Update the term record.
204 igs_en_spa_terms_api.create_update_term_rec(p_person_id => P_PERSON_ID,
205 p_program_cd => P_COURSE_CD,
206 p_term_cal_type => P_TERM_CAL_TYPE,
207 p_term_sequence_number => P_TERM_SEQUENCE_NUMBER,
208 p_plan_sht_status => 'PLAN',
209 p_ripple_frwrd => FALSE,
210 p_message_name => l_message_name,
211 p_update_rec => TRUE);
212 END IF;
213
214 END IF; -- if condition of Plan sheet flag as 'SKIP'
215
216 END IF ; -- if condition of terms record exists.
217 ClOSE select_rec;
218
219 OPEN select_rec ;
220 FETCH select_rec INTO old_references;
221 IF select_rec%FOUND THEN
222 igs_en_spa_terms_pkg.update_row(x_rowid => old_references.rowid ,
223 x_term_record_id => old_references.term_record_id,
224 x_person_id => old_references.person_id,
225 x_program_cd => old_references.program_cd,
226 x_program_version => old_references.program_version,
227 x_acad_cal_type => old_references.acad_cal_type,
228 x_term_cal_type => old_references.term_cal_type ,
229 x_term_sequence_number => old_references.term_sequence_number,
230 x_key_program_flag => old_references.key_program_flag,
231 x_location_cd => old_references.location_cd,
232 x_attendance_mode => old_references.attendance_mode,
233 x_attendance_type => old_references.attendance_type,
234 x_fee_cat => old_references.fee_cat,
235 x_coo_id => old_references.coo_id,
236 x_class_standing_id => old_references.class_standing_id,
237 x_attribute_category => old_references.attribute_category,
238 x_attribute1 => old_references.attribute1,
239 x_attribute2 => old_references.attribute2 ,
240 x_attribute3 => old_references.attribute3,
241 x_attribute4 => old_references.attribute4,
242 x_attribute5 => old_references.attribute5,
243 x_attribute6 => old_references.attribute6,
244 x_attribute7 => old_references.attribute7,
245 x_attribute8 => old_references.attribute8,
246 x_attribute9 => old_references.attribute9,
247 x_attribute10 => old_references.attribute10,
248 x_attribute11 => old_references.attribute11,
249 x_attribute12 => old_references.attribute12,
250 x_attribute13 => old_references.attribute13,
251 x_attribute14 => old_references.attribute14 ,
252 x_attribute15 => old_references.attribute15,
253 x_attribute16 => old_references.attribute16,
254 x_attribute17 => old_references.attribute17,
255 x_attribute18 => old_references.attribute18,
256 x_attribute19 => old_references.attribute19,
257 x_attribute20 => old_references.attribute20,
258 x_mode => 'R',
259 x_plan_sht_status => P_PLAN_SHT_FLAG
260 );
261 END IF;
262 ClOSE select_rec;
263
264 END update_spa_terms_plan_sht_flag;
265
266
267 PROCEDURE update_plansheet_unitdetails(
268 P_PERSON_ID IN NUMBER,
269 P_COURSE_CD IN VARCHAR2,
270 P_UOOID IN NUMBER,
271 P_CARTFLAG IN VARCHAR2,
272 P_SOURCEFLAG IN VARCHAR2,
273 P_FIELDNAME IN VARCHAR2,
274 P_auditVAL IN VARCHAR2,
275 P_creditVAL IN NUMBER,
276 P_gradingVAL IN VARCHAR2
277 ) AS
278
279 ------------------------------------------------------------------
280 --Created by : ctyagi, Oracle IDC
281 --Date created: 18-JULY-2005
282 --
283 --Purpose: update plannig sheet unitdetails .
284 --
285 --Known limitations/enhancements and/or remarks:
286 --
287 --Change History:
288 --Who When What
289 -------------------------------------------------------------------
290
291 cursor select_sua is
292 select sua.rowid,sua.* from IGS_EN_SU_ATTEMPT sua where
293 PERSON_ID=P_PERSON_ID AND
294 COURSE_CD=P_COURSE_CD AND
295 UOO_ID=P_UOOID ;
296
297 cursor select_plan is
298 select plan.rowid,plan.* from igs_en_plan_units plan where
299 PERSON_ID=P_PERSON_ID AND
300 COURSE_CD=P_COURSE_CD AND
301 UOO_ID=P_UOOID AND
302 CART_ERROR_FLAG=P_CARTFLAG ;
303
304 old_refsua select_sua%ROWTYPE;
305 old_refplan select_plan%ROWTYPE;
306
307 l_gradingcode IGS_EN_SU_ATTEMPT.GRADING_SCHEMA_CODE%TYPE;
308 l_gradingver IGS_EN_SU_ATTEMPT.GS_VERSION_NUMBER%TYPE;
309 l_enrolled_cp IGS_EN_SU_ATTEMPT.OVERRIDE_ENROLLED_CP%TYPE;
310 l_no_assessment_ind IGS_EN_SU_ATTEMPT.NO_ASSESSMENT_IND%TYPE;
311
312 -- Internal Procedure to update the Planning Sheet record.
313 PROCEDURE update_plan( p_grd_schm_cd IN VARCHAR2,
314 p_gs_ver IN NUMBER,
315 p_audit IN VARCHAR2,
316 p_credit IN NUMBER,
317 p_old_refplan select_plan%ROWTYPE) IS
318 BEGIN
319 igs_en_plan_units_pkg.update_row(
320 x_rowid => p_old_refplan.rowid,
321 x_person_id => p_old_refplan.person_id ,
322 x_course_cd => p_old_refplan.course_cd,
323 x_uoo_id => p_old_refplan.uoo_id,
324 x_term_cal_type => p_old_refplan.term_cal_type,
325 x_term_ci_sequence_number => p_old_refplan.term_ci_sequence_number,
326 x_no_assessment_ind => p_audit,
327 x_sup_uoo_id => p_old_refplan.sup_uoo_id,
328 x_override_enrolled_cp => p_credit,
329 x_grading_schema_code => p_grd_schm_cd,
330 x_gs_version_number => p_gs_ver,
331 x_core_indicator_code => p_old_refplan.core_indicator_code,
332 x_alternative_title => p_old_refplan.alternative_title,
333 x_cart_error_flag => p_old_refplan.cart_error_flag,
334 x_session_id => p_old_refplan.session_id,
335 x_mode => 'R'
336 );
337 END update_plan;
338
339 -- Internal procedure to update the sua record.
340 PROCEDURE update_sua ( p_grd_schm_cd IN VARCHAR2,
341 p_gs_ver IN NUMBER,
342 p_audit IN VARCHAR2,
343 p_credit IN NUMBER,
344 p_old_refsua select_sua%ROWTYPE) IS
345 BEGIN
346 igs_en_su_attempt_pkg.UPDATE_ROW(
347 X_ROWID => p_old_refsua.rowid,
348 X_PERSON_ID => p_old_refsua.PERSON_ID,
349 X_COURSE_CD => p_old_refsua.COURSE_CD,
350 X_UNIT_CD => p_old_refsua.UNIT_CD,
351 X_CAL_TYPE => p_old_refsua.CAL_TYPE,
352 X_CI_SEQUENCE_NUMBER => p_old_refsua.CI_SEQUENCE_NUMBER,
353 X_VERSION_NUMBER => p_old_refsua.VERSION_NUMBER,
354 X_LOCATION_CD => p_old_refsua.LOCATION_CD,
355 X_UNIT_CLASS => p_old_refsua.UNIT_CLASS,
356 X_CI_START_DT => p_old_refsua.CI_START_DT,
357 X_CI_END_DT => p_old_refsua.CI_END_DT,
358 X_UOO_ID => p_old_refsua.UOO_ID,
359 X_ENROLLED_DT => p_old_refsua.ENROLLED_DT,
360 X_UNIT_ATTEMPT_STATUS => p_old_refsua.UNIT_ATTEMPT_STATUS,
361 X_ADMINISTRATIVE_UNIT_STATUS => p_old_refsua.ADMINISTRATIVE_UNIT_STATUS,
362 X_DISCONTINUED_DT => p_old_refsua.DISCONTINUED_DT,
363 X_RULE_WAIVED_DT => p_old_refsua.RULE_WAIVED_DT,
364 X_RULE_WAIVED_PERSON_ID => p_old_refsua.RULE_WAIVED_PERSON_ID,
365 X_NO_ASSESSMENT_IND => p_audit,
366 X_SUP_UNIT_CD => p_old_refsua.SUP_UNIT_CD,
367 X_SUP_VERSION_NUMBER => p_old_refsua.SUP_VERSION_NUMBER,
368 X_EXAM_LOCATION_CD => p_old_refsua.EXAM_LOCATION_CD,
369 X_ALTERNATIVE_TITLE => p_old_refsua.ALTERNATIVE_TITLE,
370 X_OVERRIDE_ENROLLED_CP => p_credit,
371 X_OVERRIDE_EFTSU => p_old_refsua.OVERRIDE_EFTSU,
372 X_OVERRIDE_ACHIEVABLE_CP => p_old_refsua.OVERRIDE_ACHIEVABLE_CP,
373 X_OVERRIDE_OUTCOME_DUE_DT => p_old_refsua.OVERRIDE_OUTCOME_DUE_DT,
374 X_OVERRIDE_CREDIT_REASON => p_old_refsua.OVERRIDE_CREDIT_REASON,
375 X_ADMINISTRATIVE_PRIORITY => p_old_refsua.ADMINISTRATIVE_PRIORITY,
376 X_WAITLIST_DT => p_old_refsua.WAITLIST_DT,
377 x_dcnt_reason_cd => p_old_refsua.dcnt_reason_cd,
378 X_MODE => 'R',
379 X_GS_VERSION_NUMBER => p_gs_ver,
380 X_ENR_METHOD_TYPE => p_old_refsua.ENR_METHOD_TYPE,
381 X_FAILED_UNIT_RULE => p_old_refsua.FAILED_UNIT_RULE,
382 X_CART => p_old_refsua.CART,
383 X_RSV_SEAT_EXT_ID => p_old_refsua.RSV_SEAT_EXT_ID,
384 X_ORG_UNIT_CD => p_old_refsua.ORG_UNIT_CD,
385 X_GRADING_SCHEMA_CODE => p_grd_schm_cd,
386 x_subtitle => p_old_refsua.subtitle,
387 x_session_id => p_old_refsua.session_id,
388 x_deg_aud_detail_id => p_old_refsua.deg_aud_detail_id,
389 x_student_career_transcript => p_old_refsua.student_career_transcript,
390 x_student_career_statistics => p_old_refsua.student_career_statistics,
391 x_waitlist_manual_ind => p_old_refsua.waitlist_manual_ind,
392 X_ATTRIBUTE_CATEGORY => p_old_refsua.ATTRIBUTE_CATEGORY,
393 X_ATTRIBUTE1 => p_old_refsua.ATTRIBUTE1,
394 X_ATTRIBUTE2 => p_old_refsua.ATTRIBUTE2,
395 X_ATTRIBUTE3 => p_old_refsua.ATTRIBUTE3,
396 X_ATTRIBUTE4 => p_old_refsua.ATTRIBUTE4,
397 X_ATTRIBUTE5 => p_old_refsua.ATTRIBUTE5,
398 X_ATTRIBUTE6 => p_old_refsua.ATTRIBUTE6,
399 X_ATTRIBUTE7 => p_old_refsua.ATTRIBUTE7,
400 X_ATTRIBUTE8 => p_old_refsua.ATTRIBUTE8,
401 X_ATTRIBUTE9 => p_old_refsua.ATTRIBUTE9,
402 X_ATTRIBUTE10 => p_old_refsua.ATTRIBUTE10,
403 X_ATTRIBUTE11 => p_old_refsua.ATTRIBUTE11,
404 X_ATTRIBUTE12 => p_old_refsua.ATTRIBUTE12,
405 X_ATTRIBUTE13 => p_old_refsua.ATTRIBUTE13,
406 X_ATTRIBUTE14 => p_old_refsua.ATTRIBUTE14,
407 X_ATTRIBUTE15 => p_old_refsua.ATTRIBUTE15,
408 X_ATTRIBUTE16 => p_old_refsua.ATTRIBUTE16,
409 X_ATTRIBUTE17 => p_old_refsua.ATTRIBUTE17,
410 X_ATTRIBUTE18 => p_old_refsua.ATTRIBUTE18,
411 X_ATTRIBUTE19 => p_old_refsua.ATTRIBUTE19,
412 x_ATTRIBUTE20 => p_old_refsua.ATTRIBUTE20,
413 X_WLST_PRIORITY_WEIGHT_NUM => p_old_refsua.WLST_PRIORITY_WEIGHT_NUM,
414 X_WLST_PREFERENCE_WEIGHT_NUM => p_old_refsua.WLST_PREFERENCE_WEIGHT_NUM,
415 X_CORE_INDICATOR_CODE => p_old_refsua.CORE_INDICATOR_CODE,
416 X_UPD_AUDIT_FLAG => p_old_refsua.UPD_AUDIT_FLAG,
417 X_SS_SOURCE_IND => p_old_refsua.SS_SOURCE_IND
418 );
419 END update_sua;
420
421 BEGIN
422
423 -- if the SUA record is getting updated.
424 IF P_SOURCEFLAG = 'SUA' THEN
425
426 OPEN select_sua;
427 FETCH select_sua INTO old_refsua;
428 IF select_sua%FOUND THEN
429 IF P_FIELDNAME = 'GRADING' THEN
430 l_gradingcode:=substr(P_gradingVAL, 0,instr(P_gradingVAL, ',')-1);
431 l_gradingver:=to_number(substr(P_gradingVAL,instr(P_gradingVAL, ',')+1,length(P_gradingVAL)));
432 l_enrolled_cp := old_refsua.override_enrolled_cp;
433 l_no_assessment_ind := old_refsua.no_assessment_ind;
434 ELSIF P_FIELDNAME = 'AUDIT' THEN
435 l_gradingcode := old_refsua.grading_schema_code;
436 l_gradingver := old_refsua.gs_version_number;
437 l_enrolled_cp := P_creditVAL;
438 l_no_assessment_ind := P_auditVAL;
439 ELSIF P_FIELDNAME = 'VARIABLECREDIT' THEN
440 l_gradingcode := old_refsua.grading_schema_code;
441 l_gradingver := old_refsua.gs_version_number;
442 l_enrolled_cp := P_creditVAL;
443 l_no_assessment_ind := old_refsua.no_assessment_ind;
444 END IF;
445
446 update_sua ( p_grd_schm_cd => l_gradingcode,
447 p_gs_ver => l_gradingver,
448 p_audit => l_no_assessment_ind,
449 p_credit => l_enrolled_cp,
450 p_old_refsua =>old_refsua);
451
452 END IF; -- Sua Exists
453 CLOSE select_sua;
454
455 ELSIF P_SOURCEFLAG ='PLAN' THEN -- if the planning sheet record is getting updated.
456
457 OPEN select_plan;
458 FETCH select_plan INTO old_refplan;
459 IF select_plan%found THEN
460 IF P_FIELDNAME = 'GRADING' THEN
461 l_gradingcode:=substr(P_gradingVAL, 0,instr(P_gradingVAL, ',')-1);
462 l_gradingver:=to_number(substr(P_gradingVAL,instr(P_gradingVAL, ',')+1,length(P_gradingVAL)));
463 l_enrolled_cp := old_refplan.override_enrolled_cp;
464 l_no_assessment_ind := old_refplan.no_assessment_ind;
465 ELSIF P_FIELDNAME = 'AUDIT' THEN
466 l_gradingcode := old_refplan.grading_schema_code;
467 l_gradingver := old_refplan.gs_version_number;
468 l_enrolled_cp := P_creditVAL;
469 l_no_assessment_ind := P_auditVAL;
470 ELSIF P_FIELDNAME = 'VARIABLECREDIT' THEN
471 l_gradingcode := old_refplan.grading_schema_code;
472 l_gradingver := old_refplan.gs_version_number;
473 l_enrolled_cp := P_creditVAL;
474 l_no_assessment_ind := old_refplan.no_assessment_ind;
475 END IF;
476 update_plan( p_grd_schm_cd => l_gradingcode,
477 p_gs_ver => l_gradingver,
478 p_audit => l_no_assessment_ind,
479 p_credit => l_enrolled_cp,
480 p_old_refplan =>old_refplan);
481
482 END IF; -- if plan exists
483 CLOSE select_plan;
484
485 END IF;
486
487 END update_plansheet_unitdetails;
488
489
490 PROCEDURE delete_plansheet_unit(
491 p_person_id IN NUMBER,
492 p_course_cd IN VARCHAR2,
493 p_uooid IN NUMBER,
494 p_cartflag IN VARCHAR2,
495 p_return_status OUT NOCOPY VARCHAR2,
496 p_message_name OUT NOCOPY VARCHAR2
497 ) AS
498 PRAGMA AUTONOMOUS_TRANSACTION;
499 ------------------------------------------------------------------
500 --Created by : ctyagi, Oracle IDC
501 --Date created: 18-JULY-2005
502 --
503 --Purpose: delete record from igs_en_plan_units .
504 --
505 --Known limitations/enhancements and/or remarks:
506 --
507 --Change History:
508 --Who When What
509 -------------------------------------------------------------------
510 CURSOR cur_plan_unit IS
511 SELECT pl.rowid, pl.term_cal_type, pl.term_ci_sequence_number,pl.uoo_id, pl.core_indicator_code, pl.sup_uoo_id
512 FROM igs_en_plan_units pl
513 WHERE pl.person_id = p_person_id
514 AND pl.course_cd = p_course_cd
515 AND pl.cart_error_flag = P_CARTFLAG
516 AND (pl.uoo_id = P_UOOID
517 OR ( EXISTS ( SELECT 'X'
518 FROM IGS_PS_UNIT_OFR_OPT UOO
519 WHERE UOO.SUP_UOO_ID = P_UOOID
520 AND UOO.RELATION_TYPE = 'SUBORDINATE'
521 AND UOO.UOO_ID = pl.UOO_ID)
522 )
523 )
524 ORDER BY pl.SUP_Uoo_id;
525
526 cur_plan_unit_rec cur_plan_unit%ROWTYPE;
527
528 CURSOR c_usec_dtls(cp_n_uoo_id IN NUMBER) IS
529 SELECT unit_cd || '/' || unit_class UnitSection
530 FROM igs_ps_unit_ofr_opt_all
531 WHERE uoo_id = cp_n_uoo_id;
532
533 CURSOR cur_permission_unit(cp_uoo_id IN NUMBER) IS
534 SELECT spl.spl_perm_request_id
535 FROM igs_en_spl_perm spl
536 WHERE spl.student_person_id= p_person_id
537 AND spl.uoo_id = cp_uoo_id
538 AND spl.transaction_type <> 'WITHDRAWN';
539
540 cur_permission_unit_rec cur_permission_unit%ROWTYPE;
541 l_enc_message_name VARCHAR2(2000);
542 l_app_short_name VARCHAR2(10);
543 l_msg_index NUMBER;
544 rec_usec_dtls c_usec_dtls%ROWTYPE;
545
546 BEGIN
547 -- Loop through the records.
548 FOR cur_plan_unit_rec IN cur_plan_unit LOOP
549 -- imp bmerugu this if is always false as core indicator flag for planning sheet is not being populated, core indicator column added for the future use.
550 -- this if block code never executes.
551 -- if the unit section is core unit.
552 IF cur_plan_unit_rec.core_indicator_code = 'CORE' THEN
553 -- if the student does not have override to drop then.
554 IF NOT check_core_override( p_person_id => p_person_id,
555 p_course_cd => p_course_cd,
556 p_uoo_id => cur_plan_unit_rec.uoo_id,
557 p_term_cal => cur_plan_unit_rec.term_cal_type,
558 p_term_seq_num => cur_plan_unit_rec.term_ci_sequence_number ) THEN
559 -- if the unit section is normal or superior unit section then.
560 IF cur_plan_unit_rec.sup_uoo_id IS NULL THEN
561 fnd_message.set_name('IGS','IGS_EN_SS_SWP_DEL_CORE_FAIL');
562 OPEN c_usec_dtls (cur_plan_unit_rec.uoo_id);
563 FETCH c_usec_dtls INTO rec_usec_dtls;
564 CLOSE c_usec_dtls;
565 fnd_message.set_token('UNIT_CD',rec_usec_dtls.UnitSection);
566 ELSE
567 -- if the unit section is sub ordinate unit section, it is also core and student does not have override to drop the core unit.
568 fnd_message.set_name('IGS','IGS_EN_SS_SWP_SUB_CORE_FAIL');
569
570 OPEN c_usec_dtls (cur_plan_unit_rec.sup_uoo_id);
571 FETCH c_usec_dtls INTO rec_usec_dtls;
572 CLOSE c_usec_dtls;
573 fnd_message.set_token('SUP_UNIT_CD',rec_usec_dtls.UnitSection);
574
575 OPEN c_usec_dtls (cur_plan_unit_rec.uoo_id);
576 FETCH c_usec_dtls INTO rec_usec_dtls;
577 CLOSE c_usec_dtls;
578 fnd_message.set_token('SUB_UNIT_CD',rec_usec_dtls.UnitSection);
579
580 END IF;
581
582 p_message_name := fnd_message.get;
583 p_return_status := 'FALSE';
584 EXIT;
585 END IF;
586 END IF;
587
588 --call the TBH for the planning sheet to delete the unit
589 --by passing the rowid
590 igs_en_plan_units_pkg.delete_row (x_rowid => cur_plan_unit_rec.rowid);
591
592 --call drop_permission_unit to drop the permission unit
593
594 FOR cur_permission_unit_rec IN cur_permission_unit(cur_plan_unit_rec.uoo_id) LOOP
595 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);
596 END LOOP;
597 fnd_message.set_name('IGS','IGS_EN_SS_SWP_DEL_SUCCESS');
598
599 END LOOP;
600 IF p_return_status = 'FALSE' THEN
601 ROLLBACK;
602 ELSE
603
604 p_return_status := 'TRUE'; -- Successfully dropped the unit section/s
605 p_message_name:= fnd_message.get;
606 COMMIT;
607 END IF;
608
609 EXCEPTION
610 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
611 ROLLBACK;
612 IF cur_plan_unit%ISOPEN THEN
613 CLOSE cur_plan_unit;
614 END IF;
615 --set the p_message out parameter
616 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
617 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,p_message_name);
618 p_return_status := 'DENY';
619 WHEN OTHERS THEN
620 ROLLBACK;
621 p_message_name :='IGS_GE_UNHANDLED_EXCEPTION';
622 p_return_status := 'DENY';
623
624 END delete_plansheet_unit;
625
626 PROCEDURE delete_sua_from_plan(
627 p_person_id IN NUMBER,
628 p_course_cd IN VARCHAR2,
629 p_uoo_id IN NUMBER,
630 p_tch_cal IN VARCHAR2,
631 p_tch_seq IN NUMBER,
632 p_term_cal IN VARCHAR2,
633 p_term_seq_num IN NUMBER,
634 p_core IN VARCHAR2,
635 p_return_status OUT NOCOPY VARCHAR2,
636 p_message_name OUT NOCOPY VARCHAR2
637 )AS
638 ------------------------------------------------------------------
639 --Created by : ctyagi, Oracle IDC
640 --Date created: 18-JULY-2005
641 --
642 --Purpose: delete sua record from planning sheet.
643 --
644 --Known limitations/enhancements and/or remarks:
645 --
646 --Change History:
647 --Who When What
648 -------------------------------------------------------------------
649
650 CURSOR c_multiple_load(cp_cal_type IN VARCHAR2,
651 cp_seq_num IN NUMBER ) IS
652 SELECT COUNT(*)
653 FROM igs_ca_teach_to_load_v
654 WHERE teach_cal_type = cp_cal_type
655 AND teach_ci_sequence_number = cp_seq_num
656 AND rownum < 3;
657 l_deny_warn igs_en_cpd_ext.notification_flag%TYPE;
658 l_counter NUMBER;
659 l_ret_all_uoo_ids VARCHAR2(100);
660 l_ret_sub_uoo_ids VARCHAR2(100);
661 l_ret_nonsub_uoo_ids VARCHAR2(100);
662
663
664 FUNCTION eval_plan_core ( p_person_id IN NUMBER,
665 p_course_cd IN VARCHAR2,
666 p_uoo_id IN NUMBER,
667 p_term_cal IN VARCHAR2,
668 p_term_seq_num IN NUMBER,
669 p_return_status OUT NOCOPY VARCHAR2,
670 p_message_name OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
671
672 l_person_type igs_pe_person_types.person_type_code%TYPE;
673 l_enrollment_category igs_en_cat_prc_step.enrolment_cat%TYPE;
674 l_comm_type igs_en_cat_prc_step.s_student_comm_type%TYPE;
675 l_enr_method_type igs_en_cat_prc_step.enr_method_type%TYPE;
676 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
677 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
678 l_step_override_limit NUMBER;
679 l_message VARCHAR2(100);
680 l_ret_status VARCHAR2(10);
681 l_en_cal_type igs_ca_inst.cal_type%TYPE;
682 l_en_ci_seq_num igs_ca_inst.sequence_number%TYPE;
683 l_dummy VARCHAR2(200);
684
685 BEGIN
686 IF NVL(fnd_profile.value('IGS_EN_CORE_VAL'),'N') = 'N' THEN
687 RETURN 'TRUE';
688 END IF;
689
690 -- Get the person type
691 l_person_type := igs_en_gen_008.enrp_get_person_type(p_course_cd);
692
693 -- Get the superior academic calendar instance
694 igs_en_gen_015.get_academic_cal
695 (
696 p_person_id,
697 p_course_cd,
698 l_acad_cal_type,
699 l_acad_ci_sequence_number,
700 p_message_name,
701 SYSDATE
702 );
703
704 -- Get the enrollment category and commencement type
705 l_enrollment_category:=igs_en_gen_003.enrp_get_enr_cat(
706 p_person_id,
707 p_course_cd,
708 l_acad_cal_type,
709 l_acad_ci_sequence_number,
710 NULL,
711 l_en_cal_type,
712 l_en_ci_seq_num,
713 l_comm_type,
714 l_dummy);
715
716 --- Get the enrollment method
717 igs_en_gen_017.enrp_get_enr_method(l_enr_method_type,p_message_name,l_ret_status);
718
719 -- Get the value of Deny/Warn Flag for unit step 'DROP_CORE'
720 l_deny_warn := igs_ss_enr_details.get_notification(
721 p_person_type => l_person_type,
722 p_enrollment_category => l_enrollment_category,
723 p_comm_type => l_comm_type,
724 p_enr_method_type => l_enr_method_type,
725 p_step_group_type => 'UNIT',
726 p_step_type => 'DROP_CORE',
727 p_person_id => p_person_id,
728 p_message => p_message_name
729 ) ;
730
731 -- If the unit step is not defined return TRUE
732 IF l_deny_warn IS NULL OR l_deny_warn ='W' OR l_deny_warn = 'WARN' THEN
733 RETURN 'TRUE';
734 END IF;
735
736 -- If the unit is not a Core Unit, return TRUE. If the unit is a
737 -- core unit and the unit step DROP_CORE is overridden for the
738 -- student in context, return TRUE else return FALSE.
739 IF igs_en_gen_015.validation_step_is_overridden
740 (
741 'DROP_CORE',
742 p_term_cal,
743 p_term_seq_num,
744 p_person_id,
745 p_uoo_id,
746 l_step_override_limit
747 )
748 THEN
749 RETURN 'TRUE';
750 ELSE
751 RETURN 'FALSE';
752 END IF;
753
754 END eval_plan_core;
755
756 BEGIN
757
758 OPEN c_multiple_load(p_tch_cal,p_tch_seq);
759 FETCH c_multiple_load INTO l_counter;
760 CLOSE c_multiple_load;
761 IF NVL(l_counter,0) >1 THEN
762 p_return_status := 'FALSE';
763 p_message_name :='IGS_EN_CANT_DROP_PLS';
764 RETURN;
765 END IF;
766
767 IF p_core = 'CORE' THEN
768 IF eval_plan_core ( p_person_id => p_person_id,
769 p_course_cd => p_course_cd,
770 p_uoo_id => p_uoo_id,
771 p_term_cal => p_term_cal,
772 p_term_seq_num => p_term_seq_num,
773 p_return_status => p_return_status,
774 p_message_name => p_message_name) = 'FALSE' THEN
775 p_return_status := 'FALSE';
776 p_message_name := 'IGS_EN_SS_SWP_DEL_CORE_FAIL';
777 RETURN;
778 END IF;
779 END IF;
780
781 igs_ss_en_wrappers.enrp_chk_del_sub_units(p_person_id => p_person_id ,
782 p_course_cd => p_course_cd,
783 p_load_cal_type => p_term_cal,
784 p_load_ci_seq_num => p_term_seq_num,
785 p_selected_uoo_ids => p_uoo_id,
786 p_ret_all_uoo_ids => l_ret_all_uoo_ids,
787 p_ret_sub_uoo_ids => l_ret_sub_uoo_ids,
788 p_ret_nonsub_uoo_ids => l_ret_nonsub_uoo_ids,
789 p_delete_flag => 'Y') ;
790 p_return_status := 'TRUE';
791 p_message_name := NULL;
792
793 END delete_sua_from_plan;
794
795
796 PROCEDURE is_core_replaced(p_n_person_id IN NUMBER,
797 p_c_program_code IN VARCHAR2,
798 p_n_program_ver IN NUMBER,
799 p_c_load_cal IN VARCHAR2,
800 p_n_load_seq_num IN NUMBER,
801 p_c_core_uoo_ids IN VARCHAR2,
802 p_ss_session_id IN NUMBER)
803 ------------------------------------------------------------------
804 --Created by : vijrajag
805 --Date created: 04-July-2005
806 --
807 --Purpose: Checks whether all the core unit section getting dropped has equivalent replacement core unit sections
808 --
809 --Known limitations/enhancements and/or remarks:
810 --
811 --Change History:
812 --Who When What
813 --vijrajag 28-Oct-2005 Should create a Deny warning only if the DROP_CORE step fails
814 -------------------------------------------------------------------
815 IS
816 l_uoo_ids varchar2(2000);
817 l_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
818 l_found varchar2(1);
819 l_pos number;
820 l_row_id ROWID;
821 l_warning_id igs_en_std_warnings.warning_id%TYPE;
822 l_deny_warn VARCHAR2(20);
823 CURSOR c_unit_sec_rep(cp_uoo_id IN NUMBER) IS
824 SELECT 'X' FROM igs_en_su_attempt_all sua, igs_ps_unit_ofr_opt_all uoo
825 WHERE uoo.uoo_id = cp_uoo_id
826 AND sua.person_id = p_n_person_id
827 AND sua.course_cd = p_c_program_code
828 AND sua.core_indicator_code = 'CORE'
829 AND sua.ss_source_ind = 'S'
830 AND sua.unit_cd = uoo.unit_cd
831 AND sua.cal_type = uoo.cal_type
832 AND sua.ci_sequence_number = uoo.ci_sequence_number
833 AND sua.uoo_id <> uoo.uoo_id
834 AND sua.unit_attempt_status = 'UNCONFIRM';
835 BEGIN
836
837 igs_en_add_units_api.g_ss_session_id := p_ss_session_id;
838
839 l_uoo_ids := p_c_core_uoo_ids;
840 WHILE l_uoo_ids is NOT NULL LOOP
841 l_pos := instr(l_uoo_ids,',',1);
842 if l_pos=0 then
843 l_uoo_id := l_uoo_ids;
844 l_uoo_ids := NULL;
845 else
846 l_uoo_id := substr(l_uoo_ids,0,l_pos-1);
847 l_uoo_ids := substr(l_uoo_ids,l_pos+1,length(l_uoo_ids));
848 end if;
849 l_deny_warn := NULL;
850 -- If the CORE STEP validation fails, only then create a warning
851 IF igs_en_gen_015.eval_core_unit_drop
852 (
853 p_n_person_id,
854 p_c_program_code,
855 l_uoo_id,
856 'DROP_CORE',
857 p_c_load_cal,
858 p_n_load_seq_num,
859 l_deny_warn,
860 null
861 ) = 'FALSE'
862 THEN
863 -- Get the Unit Code
864 IF l_deny_warn = 'DENY' THEN
865
866 OPEN c_unit_sec_rep(l_uoo_id);
867 FETCH c_unit_sec_rep INTO l_found;
868 IF c_unit_sec_rep%NOTFOUND THEN
869 igs_en_drop_units_api.create_ss_warning (
870 P_PERSON_ID =>p_n_person_id,
871 P_COURSE_CD =>p_c_program_code,
872 P_TERM_CAL_TYPE =>p_c_load_cal,
873 P_TERM_CI_SEQUENCE_NUMBER =>p_n_load_seq_num,
874 P_UOO_ID =>l_uoo_id,
875 P_MESSAGE_FOR =>igs_ss_enr_details.get_core_disp_unit(p_n_person_id,p_c_program_code,l_uoo_id),
876 P_MESSAGE_ICON =>'D',
877 P_MESSAGE_NAME =>'IGS_SS_DENY_CORE_SWAP',
878 P_MESSAGE_RULE_TEXT => NULL,
879 P_MESSAGE_TOKENS => NULL,
880 P_MESSAGE_ACTION => NULL,
881 P_DESTINATION => NULL,
882 P_PARAMETERS => NULL,
883 P_STEP_TYPE => 'UNIT'
884 );
885 END IF;
886 CLOSE c_unit_sec_rep;
887 END IF;
888 END IF;
889 END LOOP;
890
891 igs_en_add_units_api.g_ss_session_id := NULL;
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 igs_en_add_units_api.g_ss_session_id := NULL;
896 RAISE;
897
898 END is_core_replaced;
899
900 PROCEDURE swap_drop (
901 p_uoo_ids IN VARCHAR2,
902 p_person_id IN NUMBER,
903 p_person_type IN VARCHAR2,
904 p_load_cal_type IN VARCHAR2,
905 p_load_sequence_number IN NUMBER,
906 p_program_cd IN VARCHAR2,
907 p_program_version IN NUMBER ,
908 p_message OUT NOCOPY VARCHAR2,
909 p_ret_status OUT NOCOPY VARCHAR2,
910 p_ss_session_id IN NUMBER)
911 ------------------------------------------------------------------
912 --Created by : Vijay Rajagopal, Oracle IDC
913 --Date created: 27-JUN-2005
914 --
915 --Purpose: Drop the unit section selected as a part of drop
916 --
917 --Known limitations/enhancements and/or remarks:
918 --
919 --Change History:
920 --Who When What
921 --bdeviset 28-MAR-2006 Modified procedure for Bug# 5070774
922 -------------------------------------------------------------------
923 IS
924
925 -- Get the discontinuation reason code.
926 CURSOR c_drop_reason IS
927 SELECT DISCONTINUATION_REASON_CD
928 FROM igs_en_dcnt_reasoncd
929 WHERE S_DISCONTINUATION_REASON_TYPE = 'SWAP'
930 AND SYS_DFLT_IND = 'Y'
931 AND ROWNUM < 2 ;
932
933
934 -- Get the SUA Details
935 CURSOR c_sua (cp_n_person_id IN NUMBER, cp_c_course_cd IN VARCHAR2, cp_n_uoo_id IN NUMBER) IS
936 SELECT *
937 FROM IGS_EN_SU_ATTEMPT sua
938 WHERE sua.person_id = cp_n_person_id
939 AND sua.course_cd = cp_c_course_cd
940 AND sua.uoo_id = cp_n_uoo_id;
941
942 TYPE c_ref_cursor IS REF CURSOR;
943 c_ref_cur_coreq_prereq c_ref_cursor;
944 l_c_reason_cd igs_en_dcnt_reasoncd.discontinuation_reason_cd%TYPE;
945 l_uoo_ids varchar2(32000);
946 pos number;
947 l_cur_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE; -- current uoo_id
948 l_start_index NUMBER;
949 l_end_index NUMBER;
950 -- l_sub VARCHAR2(1); -- indicate whether the current unit section is subordinate or not.
951
952 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
953 l_alternate_code igs_ca_inst.alternate_code%TYPE;
954 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
955 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
956 l_acad_start_dt DATE;
957 l_acad_end_dt DATE;
958
959 l_enr_cat igs_ps_type.enrolment_cat%TYPE;
960 l_enr_cal_type IGS_CA_INST.cal_type%TYPE;
961 l_enr_ci_seq IGS_CA_INST.sequence_number%TYPE;
962 l_enr_categories VARCHAR2(255);
963 l_enr_comm VARCHAR2(1000);
964
965 l_deny_warn_min_cp VARCHAR2(10);
966 l_deny_warn_att_type VARCHAR2(30);
967 l_deny_warn_coreq VARCHAR2(10);
968 l_deny_warn_prereq VARCHAR2(10);
969 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
970
971 l_message VARCHAR2(100);
972 l_return_status VARCHAR2(5);
973 l_rule VARCHAR2(32000);
974 l_icon VARCHAR2(1);
975 l_display_rule VARCHAR2(1);
976 l_coreq_failed_uoo_ids VARCHAR2(1000);
977 l_prereq_failed_uoo_ids VARCHAR2(1000);
978 l_unit_cd igs_en_su_attempt.unit_cd%TYPE;
979 l_uoo_id igs_en_su_attempt.uoo_id%TYPE;
980 l_unit_class igs_en_su_attempt.unit_class%TYPE;
981
982 BEGIN
983
984 -- reset the global variable
985 igs_en_add_units_api.g_swap_failed_uooids := NULL;
986 igs_en_add_units_api.g_ss_session_id := p_ss_session_id;
987
988
989 -- Get the enrollment method type by calling the procedure igs_en_gen_017.enrp_get_enr_method
990 igs_en_gen_017.enrp_get_enr_method(p_enr_method_type => l_enr_meth_type,
991 p_error_message => l_message,
992 p_ret_status => l_return_status);
993
994 IF l_return_status = 'FALSE' OR l_message IS NOT NULL THEN
995 p_message := l_message;
996 p_ret_status := 'E';
997 igs_en_add_units_api.g_ss_session_id := NULL;
998 RETURN;
999 END IF ;
1000
1001 -- Get the academic calendar by calling the procedure Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd
1002 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
1003 p_cal_type => p_load_cal_type,
1004 p_ci_sequence_number => p_load_sequence_number,
1005 p_acad_cal_type => l_acad_cal_type,
1006 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
1007 p_acad_ci_start_dt => l_acad_start_dt,
1008 p_acad_ci_end_dt => l_acad_end_dt,
1009 p_message_name => l_message );
1010
1011 IF l_message IS NOT NULL THEN
1012 p_message := l_message;
1013 p_ret_status := 'E';
1014 igs_en_add_units_api.g_ss_session_id := NULL;
1015 RETURN;
1016 END IF;
1017
1018 -- Derive enrollment category by calling the procedure igs_en_gen_003.enrp_get_enr_cat
1019 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat(
1020 p_person_id => p_person_id,
1021 p_course_cd => p_program_cd,
1022 p_cal_type => l_acad_cal_type ,
1023 p_ci_sequence_number => l_acad_ci_sequence_number,
1024 p_session_enrolment_cat => NULL,
1025 p_enrol_cal_type => l_enr_cal_type,
1026 p_enrol_ci_sequence_number => l_enr_ci_seq,
1027 p_commencement_type => l_enr_comm,
1028 p_enr_categories => l_enr_categories
1029 );
1030
1031
1032 IF l_enr_comm = 'BOTH' THEN
1033 l_enr_comm :='ALL';
1034 END IF;
1035
1036 -- Get the Deny Warn flag for Co-req
1037 l_deny_warn_coreq := igs_ss_enr_details.get_notification(
1038 p_person_type => l_person_type,
1039 p_enrollment_category => l_enr_cat,
1040 p_comm_type => l_enr_comm,
1041 p_enr_method_type => l_enr_meth_type,
1042 p_step_group_type => 'UNIT',
1043 p_step_type => 'COREQ',
1044 p_person_id => p_person_id,
1045 p_message => l_message
1046 ) ;
1047 IF l_message IS NOT NULL THEN
1048 p_message := l_message;
1049 p_ret_status := 'E';
1050 igs_en_add_units_api.g_ss_session_id := NULL;
1051 RETURN;
1052 END IF;
1053
1054 -- Get the Deny Warn for pre-req
1055 l_deny_warn_prereq := igs_ss_enr_details.get_notification(
1056 p_person_type => l_person_type,
1057 p_enrollment_category => l_enr_cat,
1058 p_comm_type => l_enr_comm,
1059 p_enr_method_type => l_enr_meth_type,
1060 p_step_group_type => 'UNIT',
1061 p_step_type => 'PREREQ',
1062 p_person_id => p_person_id,
1063 p_message => l_message
1064 ) ;
1065 IF l_message IS NOT NULL THEN
1066 p_message := l_message;
1067 p_ret_status := 'E';
1068 igs_en_add_units_api.g_ss_session_id := NULL;
1069 RETURN;
1070 END IF;
1071
1072 -- Validate prereq and coreq before dropping the units to be swapped
1073 OPEN c_ref_cur_coreq_prereq FOR
1074 'SELECT U.unit_cd, U.unit_class, U.uoo_id
1075 FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V V
1076 WHERE U.person_id = :1
1077 AND U.course_cd = :2
1078 AND U.unit_attempt_status IN (''ENROLLED'',''INVALID'')
1079 AND U.uoo_id NOT IN('||p_uoo_ids||')
1080 AND U.cal_type = V.teach_cal_type
1081 AND U.ci_sequence_number = V.teach_ci_sequence_number
1082 AND V.load_cal_type = '''||p_load_cal_type||'''
1083 AND V.load_ci_sequence_number = '||p_load_sequence_number
1084 USING p_person_id,p_program_cd;
1085
1086 LOOP
1087
1088 FETCH c_ref_cur_coreq_prereq INTO l_unit_cd,l_unit_class,l_uoo_id;
1089 EXIT WHEN c_ref_cur_coreq_prereq%NOTFOUND;
1090
1091 l_message := NULL;
1092 -- Evaluate Co-req
1093 IF l_deny_warn_coreq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_coreq(
1094 p_person_id => p_person_id,
1095 p_load_cal_type => p_load_cal_type,
1096 p_load_sequence_number => p_load_sequence_number,
1097 p_uoo_id => l_uoo_id,
1098 p_course_cd => p_program_cd,
1099 p_course_version => p_program_version,
1100 p_message => l_message,
1101 p_deny_warn => l_deny_warn_coreq,
1102 p_calling_obj => 'DROP') THEN
1103
1104 IF l_coreq_failed_uoo_ids IS NOT NULL THEN
1105 l_coreq_failed_uoo_ids := l_coreq_failed_uoo_ids ||','|| TO_CHAR(l_uoo_id);
1106 ELSE
1107 l_coreq_failed_uoo_ids := TO_CHAR(l_uoo_id);
1108 END IF;
1109
1110 END IF;
1111
1112 l_message := NULL;
1113 -- Evaluate Prereq
1114 IF l_deny_warn_prereq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_prereq(
1115 p_person_id => p_person_id,
1116 p_load_cal_type => p_load_cal_type,
1117 p_load_sequence_number => p_load_sequence_number,
1118 p_uoo_id => l_uoo_id,
1119 p_course_cd => p_program_cd,
1120 p_course_version => p_program_version,
1121 p_message => l_message,
1122 p_deny_warn => l_deny_warn_prereq,
1123 p_calling_obj => 'DROP') THEN
1124
1125 IF l_prereq_failed_uoo_ids IS NOT NULL THEN
1126 l_prereq_failed_uoo_ids := l_prereq_failed_uoo_ids||','||TO_CHAR(l_uoo_id);
1127 ELSE
1128 l_prereq_failed_uoo_ids := TO_CHAR(l_uoo_id);
1129 END IF;
1130
1131 END IF;
1132 END LOOP;
1133
1134
1135 l_uoo_ids := p_uoo_ids;
1136
1137
1138 -- Get the swap drop reason
1139 OPEN c_drop_reason;
1140 FETCH c_drop_reason INTO l_c_reason_cd;
1141 CLOSE c_drop_reason;
1142
1143 IF l_uoo_ids IS NOT NULL THEN
1144
1145 -- Set the calling package as 'SWAP' so that check parent existence in igs_en_su_attempt_pkg will not be called.
1146 -- This is to overcome the locking issue encountered during Swap.
1147 -- (i.e) when the user is dropping few sua in the swap to be replaced, the sua is getting updated as 'DROPPED' and
1148 -- it locks all the parent records like SPA terms, sca...., in the swap to be added page when the user tries to
1149 -- add few sua, sua records are created in autonomous transaction where the check_parent_existences of TBH is being
1150 -- called as the parents are already locked in another transaction while dropping, SUA record creation in autonomous
1151 -- transaction fails.
1152
1153 igs_en_su_attempt_pkg.pkg_source_of_drop := 'SWAP';
1154
1155 while l_uoo_ids is not null loop
1156 pos := instr(l_uoo_ids,',',1);
1157 if pos=0 then
1158 l_cur_uoo_id := l_uoo_ids;
1159 l_uoo_ids := null;
1160 else
1161 l_cur_uoo_id := substr(l_uoo_ids,0,pos-1);
1162 l_uoo_ids := substr(l_uoo_ids,pos+1,length(l_uoo_ids));
1163 end if;
1164 -- for the current sua record.
1165 FOR rec_sua IN c_sua(p_person_id,p_program_cd,l_cur_uoo_id)
1166 LOOP
1167
1168 -- Drop the sua with reason code as swap reason code.
1169 IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW (
1170 X_ROWID => rec_sua.row_id,
1171 X_PERSON_ID => rec_sua.person_id,
1172 X_COURSE_CD => rec_sua.course_cd,
1173 X_UNIT_CD => rec_sua.unit_cd,
1174 X_CAL_TYPE => rec_sua.cal_type,
1175 X_CI_SEQUENCE_NUMBER => rec_sua.ci_sequence_number,
1176 X_VERSION_NUMBER => rec_sua.version_number,
1177 X_LOCATION_CD => rec_sua.location_cd,
1178 X_UNIT_CLASS => rec_sua.unit_class,
1179 X_CI_START_DT => rec_sua.ci_start_dt,
1180 X_CI_END_DT => rec_sua.ci_end_dt,
1181 X_UOO_ID => rec_sua.uoo_id,
1182 X_ENROLLED_DT => rec_sua.enrolled_dt,
1183 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
1184 X_ADMINISTRATIVE_UNIT_STATUS => NULL,
1185 X_DISCONTINUED_DT => SYSDATE,
1186 X_RULE_WAIVED_DT => rec_sua.rule_waived_dt,
1187 X_RULE_WAIVED_PERSON_ID => rec_sua.rule_waived_person_id,
1188 X_NO_ASSESSMENT_IND => rec_sua.no_assessment_ind,
1189 X_SUP_UNIT_CD => rec_sua.sup_unit_cd,
1190 X_SUP_VERSION_NUMBER => rec_sua.sup_version_number,
1191 X_EXAM_LOCATION_CD => rec_sua.exam_location_cd,
1192 X_ALTERNATIVE_TITLE => rec_sua.alternative_title,
1193 X_OVERRIDE_ENROLLED_CP => rec_sua.override_enrolled_cp,
1194 X_OVERRIDE_EFTSU => rec_sua.override_eftsu,
1195 X_OVERRIDE_ACHIEVABLE_CP => rec_sua.override_achievable_cp,
1196 X_OVERRIDE_OUTCOME_DUE_DT => rec_sua.override_outcome_due_dt,
1197 X_OVERRIDE_CREDIT_REASON => rec_sua.override_credit_reason,
1198 X_ADMINISTRATIVE_PRIORITY => rec_sua.administrative_priority,
1199 X_WAITLIST_DT => rec_sua.waitlist_dt,
1200 X_DCNT_REASON_CD => l_c_reason_cd,
1201 X_MODE => 'R',
1202 X_GS_VERSION_NUMBER => rec_sua.gs_version_number,
1203 X_ENR_METHOD_TYPE => rec_sua.enr_method_type,
1204 X_FAILED_UNIT_RULE => rec_sua.failed_unit_rule,
1205 X_CART => rec_sua.cart,
1206 X_RSV_SEAT_EXT_ID => rec_sua.rsv_seat_ext_id,
1207 X_ORG_UNIT_CD => rec_sua.org_unit_cd,
1208 X_GRADING_SCHEMA_CODE => rec_sua.grading_schema_code,
1209 X_subtitle => rec_sua.subtitle,
1210 x_session_id => rec_sua.session_id,
1211 X_deg_aud_detail_id => rec_sua.deg_aud_detail_id,
1212 x_student_career_transcript => rec_sua.student_career_transcript,
1213 x_student_career_statistics => rec_sua.student_career_statistics,
1214 X_WAITLIST_MANUAL_IND => rec_sua.waitlist_manual_ind,
1215 X_ATTRIBUTE_CATEGORY => rec_sua.attribute_category,
1216 X_ATTRIBUTE1 => rec_sua.attribute1,
1217 X_ATTRIBUTE2 => rec_sua.attribute2,
1218 X_ATTRIBUTE3 => rec_sua.attribute3,
1219 X_ATTRIBUTE4 => rec_sua.attribute4,
1220 X_ATTRIBUTE5 => rec_sua.attribute5,
1221 X_ATTRIBUTE6 => rec_sua.attribute6,
1222 X_ATTRIBUTE7 => rec_sua.attribute7,
1223 X_ATTRIBUTE8 => rec_sua.attribute8,
1224 X_ATTRIBUTE9 => rec_sua.attribute9,
1225 X_ATTRIBUTE10 => rec_sua.attribute10,
1226 X_ATTRIBUTE11 => rec_sua.attribute11,
1227 X_ATTRIBUTE12 => rec_sua.attribute12,
1228 X_ATTRIBUTE13 => rec_sua.attribute13,
1229 X_ATTRIBUTE14 => rec_sua.attribute14,
1230 X_ATTRIBUTE15 => rec_sua.attribute15,
1231 X_ATTRIBUTE16 => rec_sua.attribute16,
1232 X_ATTRIBUTE17 => rec_sua.attribute17,
1233 X_ATTRIBUTE18 => rec_sua.attribute18,
1234 X_ATTRIBUTE19 => rec_sua.attribute19,
1235 X_ATTRIBUTE20 => rec_sua.attribute20,
1236 X_WLST_PRIORITY_WEIGHT_NUM => rec_sua.wlst_priority_weight_num,
1237 X_WLST_PREFERENCE_WEIGHT_NUM => rec_sua.wlst_preference_weight_num,
1238 X_CORE_INDICATOR_CODE => rec_sua.core_indicator_code
1239 );
1240
1241
1242 IF rec_sua.core_indicator_code = 'CORE' THEN
1243 igs_en_drop_units_api.create_ss_warning (
1244 P_PERSON_ID => p_person_id,
1245 P_COURSE_CD => p_program_cd,
1246 P_TERM_CAL_TYPE => p_load_cal_type,
1247 P_TERM_CI_SEQUENCE_NUMBER => p_load_sequence_number,
1248 P_UOO_ID => rec_sua.uoo_id,
1249 P_MESSAGE_FOR => rec_sua.unit_cd || '/' || rec_sua.unit_class,
1250 P_MESSAGE_ICON => 'I',
1251 P_MESSAGE_NAME => 'IGS_SS_INFO_CORE_SWP',
1252 P_MESSAGE_RULE_TEXT => null,
1253 P_MESSAGE_TOKENS => null,
1254 P_MESSAGE_ACTION => null,
1255 P_DESTINATION => null,
1256 P_PARAMETERS => null,
1257 P_STEP_TYPE => 'UNIT'
1258 );
1259 END IF;
1260
1261 IGS_SS_EN_WRAPPERS.call_fee_ass (
1262 p_person_id => p_person_id,
1263 p_cal_type => p_load_cal_type, -- load
1264 p_sequence_number => p_load_sequence_number, -- load
1265 p_course_cd => p_program_cd,
1266 p_unit_cd => rec_sua.unit_cd,
1267 p_uoo_id => rec_sua.uoo_id
1268 );
1269
1270 END LOOP; -- FOR loop
1271
1272 END LOOP; -- While loop
1273 END IF;
1274
1275 -- Unsetting the package variable after dropping SUA.
1276 igs_en_su_attempt_pkg.pkg_source_of_drop := NULL;
1277
1278
1279 l_display_rule := NVL(fnd_profile.value('IGS_EN_CART_RULE_DISPLAY'),'N');
1280
1281 OPEN c_ref_cur_coreq_prereq FOR
1282 'SELECT U.unit_cd, U.unit_class, U.uoo_id
1283 FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V V
1284 WHERE U.person_id = :1
1285 AND U.course_cd = :2
1286 AND U.unit_attempt_status IN (''ENROLLED'',''INVALID'')
1287 AND U.uoo_id NOT IN('||p_uoo_ids||')
1288 AND U.cal_type = V.teach_cal_type
1289 AND U.ci_sequence_number = V.teach_ci_sequence_number
1290 AND V.load_cal_type = '''||p_load_cal_type||'''
1291 AND V.load_ci_sequence_number = '||p_load_sequence_number
1292 USING p_person_id,p_program_cd;
1293
1294 LOOP
1295
1296 FETCH c_ref_cur_coreq_prereq INTO l_unit_cd,l_unit_class,l_uoo_id;
1297 EXIT WHEN c_ref_cur_coreq_prereq%NOTFOUND;
1298
1299 -- Evaluate Co-req
1300 IF l_deny_warn_coreq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_coreq(
1301 p_person_id => p_person_id,
1302 p_load_cal_type => p_load_cal_type,
1303 p_load_sequence_number => p_load_sequence_number,
1304 p_uoo_id => l_uoo_id,
1305 p_course_cd => p_program_cd,
1306 p_course_version => p_program_version,
1307 p_message => l_message,
1308 p_deny_warn => l_deny_warn_coreq,
1309 p_calling_obj => 'DROP') THEN
1310
1311 l_message := null;
1312 l_rule := null;
1313 l_icon := null;
1314
1315 -- check if the unit failed the rule because of dropping the unit if so log a warning record
1316 IF (l_coreq_failed_uoo_ids IS NULL OR INSTR(','||l_coreq_failed_uoo_ids||',' , ','||l_uoo_id||',') = 0) THEN
1317
1318 -- get the error message and icon to be displayed.
1319 IF l_deny_warn_coreq = 'DENY' THEN
1320 l_message := 'IGS_SS_DENY_COREQ_SWP';
1321 l_icon := 'D';
1322 ELSE
1323 l_message := 'IGS_SS_WARN_COREQ_SWP';
1324 l_icon := 'W';
1325 END IF;
1326
1327 IF igs_en_add_units_api.g_swap_failed_uooids IS NULL THEN
1328 igs_en_add_units_api.g_swap_failed_uooids := TO_CHAR(l_uoo_id);
1329 ELSE
1330 igs_en_add_units_api.g_swap_failed_uooids := igs_en_add_units_api.g_swap_failed_uooids||','||TO_CHAR(l_uoo_id);
1331 END IF;
1332
1333
1334 -- if rule text needs to be displayed, get it.
1335 IF l_display_rule = 'Y' THEN
1336 l_rule := igs_ss_enr_details.get_rule_text('COREQ', l_uoo_id);
1337 END IF;
1338
1339 -- Create the warnings record for those unit section which are failing co-requisite rule
1340 igs_en_drop_units_api.create_ss_warning (
1341 P_PERSON_ID => p_person_id,
1342 P_COURSE_CD => p_program_cd,
1343 P_TERM_CAL_TYPE => p_load_cal_type,
1344 P_TERM_CI_SEQUENCE_NUMBER => p_load_sequence_number,
1345 P_UOO_ID => l_uoo_id,
1346 P_MESSAGE_FOR => l_unit_cd|| '/' ||l_unit_class,
1347 P_MESSAGE_ICON => l_icon,
1348 P_MESSAGE_NAME => l_message,
1349 P_MESSAGE_RULE_TEXT => l_rule,
1350 P_MESSAGE_TOKENS => null,
1351 P_MESSAGE_ACTION => null,
1352 P_DESTINATION => null,
1353 P_PARAMETERS => null,
1354 P_STEP_TYPE => 'UNIT'
1355 );
1356
1357 END IF;
1358
1359 END IF;
1360
1361 -- Evaluate Pre-req
1362 IF l_deny_warn_prereq IS NOT NULL AND NOT IGS_EN_ELGBL_UNIT.eval_prereq(
1363 p_person_id => p_person_id,
1364 p_load_cal_type => p_load_cal_type,
1365 p_load_sequence_number => p_load_sequence_number,
1366 p_uoo_id => l_uoo_id,
1367 p_course_cd => p_program_cd,
1368 p_course_version => p_program_version,
1369 p_message => l_message,
1370 p_deny_warn => l_deny_warn_prereq,
1371 p_calling_obj => 'DROP') THEN
1372
1373 l_message := null;
1374 l_rule := null;
1375 l_icon := null;
1376
1377 -- check if the unit failed the rule because of dropping the unit if so log a warning record
1378 IF (l_prereq_failed_uoo_ids IS NULL OR INSTR(','||l_prereq_failed_uoo_ids||',' , ','||l_uoo_id||',') = 0) THEN
1379
1380 -- get the error message and icon to be displayed.
1381 IF l_deny_warn_prereq = 'DENY' THEN
1382 l_message := 'IGS_SS_DENY_PREREQ_SWP';
1383 l_icon := 'D';
1384 ELSE
1385 l_message := 'IGS_SS_WARN_PREREQ_SWP';
1386 l_icon := 'W';
1387 END IF;
1388
1389 IF igs_en_add_units_api.g_swap_failed_uooids IS NULL THEN
1390 igs_en_add_units_api.g_swap_failed_uooids := TO_CHAR(l_uoo_id);
1391 ELSE
1392 igs_en_add_units_api.g_swap_failed_uooids := igs_en_add_units_api.g_swap_failed_uooids||','||TO_CHAR(l_uoo_id);
1393 END IF;
1394
1395 -- if rule text needs to be displayed, get it.
1396 IF l_display_rule = 'Y' THEN
1397 l_rule := igs_ss_enr_details.get_rule_text('PREREQ', l_uoo_id);
1398 END IF;
1399
1400 -- Create the warnings record for those unit section which are failing co-requisite rule
1401 igs_en_drop_units_api.create_ss_warning (
1402 P_PERSON_ID => p_person_id,
1403 P_COURSE_CD => p_program_cd,
1404 P_TERM_CAL_TYPE => p_load_cal_type,
1405 P_TERM_CI_SEQUENCE_NUMBER => p_load_sequence_number,
1406 P_UOO_ID => l_uoo_id,
1407 P_MESSAGE_FOR => l_unit_cd||'/'||l_unit_class,
1408 P_MESSAGE_ICON => l_icon,
1409 P_MESSAGE_NAME => l_message,
1410 P_MESSAGE_RULE_TEXT => l_rule,
1411 P_MESSAGE_TOKENS => null,
1412 P_MESSAGE_ACTION => null,
1413 P_DESTINATION => null,
1414 P_PARAMETERS => null,
1415 P_STEP_TYPE => 'UNIT'
1416 );
1417 END IF;
1418
1419 END IF;
1420
1421 END LOOP;
1422 igs_en_add_units_api.g_ss_session_id := NULL;
1423
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 igs_en_add_units_api.g_ss_session_id := NULL;
1427 RAISE;
1428
1429 END swap_drop;
1430 -- Procedure to delete the uncofirmed SUA created in the swap cart.
1431 -- p_ret_status returns TRUE or FALSE based on whether the record has been successfully deleted or not
1432 -- p_msg returns sucess/failure message to be displayed in SS page.
1433
1434 PROCEDURE swap_delete ( p_person_id IN NUMBER,
1435 p_course_cd IN VARCHAR2,
1436 p_course_version IN NUMBER,
1437 p_usec_dtls IN VARCHAR2,
1438 p_uoo_id IN NUMBER,
1439 p_term_cal IN VARCHAR2,
1440 p_term_seq_num IN NUMBER,
1441 p_core IN VARCHAR2,
1442 p_rel_type IN VARCHAR2,
1443 p_ret_status OUT NOCOPY VARCHAR2,
1444 p_msg OUT NOCOPY VARCHAR2) AS
1445 PRAGMA AUTONOMOUS_TRANSACTION;
1446
1447
1448 -- Cursor to get the subordinate unit attempts for a superior unit attempt.
1449 CURSOR c_sub_sua (cp_person_id IN NUMBER, cp_course_cd IN VARCHAR2, cp_uoo_id IN NUMBER) IS
1450 SELECT sua.unit_cd || '/' || sua.unit_class AS UNIT_SECTION,
1451 sua.uoo_id,
1452 sua.core_indicator_code AS CORE
1453 FROM igs_en_su_attempt_all sua,
1454 igs_ps_unit_ofr_opt_all uoo
1455 WHERE sua.person_id = cp_person_id
1456 AND sua.course_cd = cp_course_cd
1457 AND sua.uoo_id = uoo.uoo_id
1458 AND uoo.sup_uoo_id = cp_uoo_id;
1459
1460 l_sup_us BOOLEAN; -- indicates whether the unit section is superior or not
1461 l_sub_found BOOLEAN; -- indicates whether the superior unit section has subordinate or not.
1462 l_sub_units VARCHAR2(500); -- Stores the list of subordinate unit section getting dropped.
1463 l_msg VARCHAR2(30);
1464 l_denywarn igs_en_cpd_ext.notification_flag%TYPE;
1465
1466
1467 -- Procedure to call the delete row for SUA
1468 PROCEDURE sua_delete (p_person_id IN NUMBER,
1469 p_course_cd IN VARCHAR2,
1470 p_uoo_id IN NUMBER) IS
1471
1472 -- Cursor to select the row id of SUA.
1473 CURSOR c_del (cp_person_id IN NUMBER,
1474 cp_course_cd IN VARCHAR2,
1475 cp_uoo_id IN NUMBER) IS
1476 SELECT sua.row_id AS ROW_ID
1477 FROM igs_en_su_attempt sua
1478 WHERE sua.person_id = cp_person_id
1479 AND sua.course_cd = cp_course_cd
1480 AND sua.uoo_id = cp_uoo_id;
1481
1482 BEGIN
1483 FOR rec_del IN c_del(p_person_id, p_course_cd, p_uoo_id) LOOP
1484 igs_en_su_attempt_pkg.delete_row ( X_ROWID => rec_del.row_id,
1485 X_MODE => 'R');
1486 END LOOP;
1487 END sua_delete;
1488
1489 BEGIN -- Main procedure begins
1490
1491 -- Call the sua_delete procedure to delete
1492 sua_delete (p_person_id => p_person_id,
1493 p_course_cd => p_course_cd,
1494 p_uoo_id => p_uoo_id);
1495
1496 -- if the unit section dropped was superior then its subordinates as well as needs to be dropped.
1497 IF p_rel_type = 'SUPERIOR' THEN
1498 l_sup_us := TRUE;
1499 FOR rec_sub_sua IN c_sub_sua(p_person_id, p_course_cd, p_uoo_id) LOOP
1500 l_sub_units := l_sub_units || ',' || rec_sub_sua.unit_section; -- Store the list of subordinate unit sections.
1501
1502 -- Delete the subordinate unit section.
1503 sua_delete (p_person_id => p_person_id,
1504 p_course_cd => p_course_cd,
1505 p_uoo_id => rec_sub_sua.uoo_id);
1506 END LOOP;
1507
1508 IF p_ret_status = 'FALSE' THEN
1509 ROLLBACK;
1510 RETURN;
1511 END IF;
1512
1513
1514 fnd_message.set_name ('IGS','IGS_EN_SS_SWP_SUB_CORE_SUCCESS');
1515 fnd_message.set_token ('SUP_UNIT_CD',p_usec_dtls);
1516 IF length(l_sub_units) >0 THEN
1517 fnd_message.set_token ('SUB_UNIT_CD', substr(l_sub_units,2));
1518 ELSE
1519 fnd_message.set_token ('SUB_UNIT_CD', igs_ss_enr_details.get_none_desc);
1520 END IF;
1521
1522 ELSE
1523
1524 fnd_message.set_name('IGS','IGS_EN_SS_SWP_DEL_SUCCESS');
1525 fnd_message.set_token ('UNIT_CD',p_usec_dtls);
1526
1527 END IF;
1528
1529 p_ret_status := 'TRUE'; -- Successfully dropped the unit section/s
1530 p_msg:= fnd_message.get; -- Contains the success/failure message to be displayed in swap to be added page.
1531 COMMIT;
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 ROLLBACK;
1536 p_ret_status := 'FALSE';
1537 p_msg := sqlerrm;
1538 END swap_delete;
1539
1540 -- This procedure releases the sheet as the unit sections are dropped as a part of swap
1541
1542 PROCEDURE swap_submit (person_id IN NUMBER,
1543 program_cd IN VARCHAR2,
1544 p_uoo_ids IN VARCHAR2) AS
1545 CURSOR cur_sua(cp_person_id IN NUMBER,cp_program_cd IN VARCHAR2,cp_uoo_id IN NUMBER) IS
1546 SELECT *
1547 FROM IGS_EN_SU_ATTEMPT_ALL
1548 WHERE person_id = cp_person_id
1549 AND course_cd = cp_program_cd
1550 AND uoo_Id = cp_uoo_Id;
1551
1552 CURSOR cur_igs_ps_rsv_ext (cp_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE) IS
1553 SELECT rsv.ROWID row_id, rsv.*
1554 FROM igs_ps_rsv_ext rsv
1555 WHERE rsv_ext_id = cp_rsv_ext_id FOR UPDATE;
1556
1557
1558 old_rec igs_en_su_attempt_all%rowTYPE;
1559 new_rec igs_en_su_attempt_all%ROWTYPE;
1560 l_uoo_ids varchar2(30);
1561 l_cur_uoo_id varchar2(30);
1562 pos number;
1563 v_message_name VARCHAR2(30);
1564
1565 BEGIN
1566 l_uoo_ids := p_uoo_ids;
1567 WHILE l_uoo_ids is not null LOOP
1568 pos := instr(l_uoo_ids,',',1);
1569 IF pos=0 THEN
1570 l_cur_uoo_id := l_uoo_ids;
1571 l_uoo_ids := null;
1572 ELSE
1573 l_cur_uoo_id := substr(l_uoo_ids,0,pos-1);
1574 l_uoo_ids := substr(l_uoo_ids,pos+1,length(l_uoo_ids));
1575 END IF;
1576 OPEN cur_sua(person_id, program_cd, l_cur_uoo_id);
1577 FETCH cur_sua INTO old_rec;
1578 CLOSE cur_sua;
1579 new_rec := old_rec;
1580 old_rec.unit_attempt_status := 'ENROLLED';
1581
1582 -- if reserve seating is existing then need to decrement the actual seat enrolled
1583 -- which was not done during swap drop.
1584
1585 IF new_rec.rsv_seat_ext_id IS NOT NULL THEN
1586 FOR rec_igs_ps_rsv_ext IN cur_igs_ps_rsv_ext(new_rec.rsv_seat_ext_id) LOOP
1587 IF ((rec_igs_ps_rsv_ext.actual_seat_enrolled -1) >= 0) THEN
1588 igs_ps_rsv_ext_pkg.update_row( x_rowid => rec_igs_ps_rsv_ext.row_id,
1589 x_rsv_ext_id => rec_igs_ps_rsv_ext.rsv_ext_id,
1590 x_uoo_id => rec_igs_ps_rsv_ext.uoo_id,
1591 x_priority_id => rec_igs_ps_rsv_ext.priority_id,
1592 x_preference_id => rec_igs_ps_rsv_ext.preference_id,
1593 x_rsv_level => rec_igs_ps_rsv_ext.rsv_level,
1594 x_actual_seat_enrolled => rec_igs_ps_rsv_ext.actual_seat_enrolled -1,
1595 x_mode => 'R'
1596 );
1597 END IF;
1598 END LOOP;
1599 END IF;
1600
1601
1602 IF IGS_EN_GEN_012.ENRP_UPD_SCA_STATUS(
1603 new_rec.person_id,
1604 new_rec.course_cd,
1605 v_message_name) = FALSE THEN
1606 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1607 IGS_GE_MSG_STACK.ADD;
1608 APP_EXCEPTION.RAISE_EXCEPTION;
1609 END IF;
1610
1611 IGS_EN_GEN_003.UPD_MAT_MRADM_CAT_TERMS(new_rec.person_id,
1612 new_rec.course_cd,
1613 new_rec.unit_attempt_status,
1614 new_rec.cal_type,
1615 new_rec.ci_sequence_number
1616 ) ;
1617
1618 igs_en_sua_api.upd_enrollment_counts( 'DELETE',
1619 old_rec,
1620 new_rec);
1621 END LOOP;
1622 END swap_submit;
1623
1624
1625 PROCEDURE release_swap_cart(p_n_person_id IN NUMBER,
1626 p_c_program_code IN VARCHAR2,
1627 p_c_load_cal IN VARCHAR2,
1628 p_n_load_seq_num IN NUMBER)
1629 ------------------------------------------------------------------
1630 --Created by : vijrajag
1631 --Date created: 04-July-2005
1632 --
1633 --Purpose: Procedure to release enrollment seats grabbed during swap
1634 --
1635 --Known limitations/enhancements and/or remarks:
1636 --
1637 --Change History:
1638 --Who When What
1639 -------------------------------------------------------------------
1640 IS
1641 PRAGMA AUTONOMOUS_TRANSACTION;
1642 -- cursor to pick up the student attempts created as a part of swap.
1643 CURSOR c_sua (cp_n_person_id IN NUMBER,
1644 cp_c_program_code IN VARCHAR2,
1645 cp_c_load_cal IN VARCHAR2,
1646 cp_n_seq_num IN NUMBER) IS
1647 SELECT sua.ROWID ROW_ID,
1648 sua.uoo_id
1649 FROM igs_en_su_attempt_all sua,
1650 igs_ca_teach_to_load_v rel
1651 WHERE sua.person_id = cp_n_person_id
1652 AND sua.course_cd = cp_c_program_code
1653 AND sua.cal_type = rel.teach_cal_type
1654 AND sua.ci_sequence_number = rel.teach_ci_sequence_number
1655 AND rel.load_cal_type = cp_c_load_cal
1656 AND rel.load_ci_sequence_number = cp_n_seq_num
1657 AND sua.unit_attempt_status = 'UNCONFIRM'
1658 AND sua.SS_SOURCE_IND = 'S';
1659
1660
1661
1662 -- Cursor to select the warning records created as a part of swap.
1663 CURSOR c_warn (cp_n_person_id IN NUMBER,
1664 cp_c_program_code IN VARCHAR2,
1665 cp_c_load_cal IN VARCHAR2,
1666 cp_n_seq_num IN NUMBER) IS
1667 SELECT warn.ROWID ROW_ID
1668 FROM igs_en_std_warnings warn
1669 WHERE warn.person_id = cp_n_person_id
1670 AND warn.course_cd = cp_c_program_code
1671 AND warn.term_cal_type = cp_c_load_cal
1672 AND warn.term_ci_sequence_number = cp_n_seq_num;
1673
1674 BEGIN
1675
1676 FOR rec_warn in c_warn(p_n_person_id,p_c_program_code,p_c_load_cal,p_n_load_seq_num) LOOP
1677 IGS_EN_STD_WARNINGS_pkg.delete_row(x_rowid =>rec_warn.row_id);
1678 END LOOP;
1679
1680 FOR rec_sua IN c_sua(p_n_person_id,p_c_program_code,p_c_load_cal,p_n_load_seq_num) LOOP
1681
1682 igs_en_su_attempt_pkg.delete_row(X_ROWID => rec_sua.row_id,
1683 x_mode => 'R');
1684 END LOOP;
1685
1686 COMMIT;
1687
1688 END release_swap_cart;
1689
1690 FUNCTION is_credit_updatable(
1691 p_person_id IN NUMBER,
1692 p_course_cd IN NUMBER,
1693 p_uoo_id IN NUMBER,
1694 p_cal_type IN VARCHAR2,
1695 p_ci_sequence_number IN NUMBER
1696 ) RETURN CHAR AS
1697 /******************************************************************
1698 Created By : Somasekar, IDC
1699 Date Created By : 04-Oct-2001
1700 Purpose : This func determines if credit points can be updated
1701 Known limitations,
1702 enhancements,
1703 remarks :
1704 Change History
1705 Who When What
1706 ******************************************************************/
1707 l_dummy VARCHAR2(100);
1708 -- cursor for getting the Unit_cd and Version Number for the Uoo_id passed
1709 CURSOR cur_ps_unit_ofr
1710 IS
1711 SELECT unit_cd, version_number,cal_type,ci_sequence_number
1712 FROM igs_ps_unit_ofr_opt
1713 WHERE uoo_id = p_uoo_id;
1714
1715 -- Check if the unit is set up for variable cp
1716 CURSOR cur_chk_cp_chg_val (p_unit_cd igs_ps_unit_ver_v.unit_cd%TYPE,p_unit_ver_num igs_ps_unit_ver_v.version_number%TYPE)
1717 IS SELECT points_override_ind
1718 FROM igs_ps_unit_ver_v
1719 WHERE unit_cd = p_unit_cd
1720 AND version_number = p_unit_ver_num;
1721
1722 --Check if there exist any user level deadline
1723 CURSOR cur_pe_usr_arg( cp_person_type IN igs_pe_person_types.person_type_code%TYPE,
1724 cp_cal_type igs_ca_inst_all.cal_type%TYPE, cp_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
1725 IS
1726 SELECT dai.alias_val alias_val
1727 FROM igs_ca_da_inst_v dai,igs_pe_usr_arg_all pua
1728 WHERE pua.person_type = cp_person_type
1729 AND dai.dt_alias = pua.grad_sch_dt_alias
1730 AND dai.cal_type = cp_cal_type
1731 AND dai.ci_sequence_number = cp_ci_sequence_number
1732 ORDER BY 1;
1733
1734 --Check if deadline has passed for cp change at usec level
1735 CURSOR cur_en_nstd_usec
1736 IS
1737 SELECT enr_dl_date alias_val
1738 FROM igs_en_nstd_usec_dl
1739 WHERE function_name = 'GRADING_SCHEMA'
1740 AND uoo_id = p_uoo_id
1741 ORDER BY 1;
1742
1743 --Check if deadline has passed for cp change at institution level
1744 CURSOR cur_en_cal_conf(cp_cal_type igs_ca_inst_all.cal_type%TYPE, cp_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
1745 IS
1746 SELECT dai.alias_val alias_val
1747 FROM igs_ca_da_inst_v dai, igs_en_cal_conf ecc
1748 WHERE dai.cal_type = cp_cal_type
1749 AND dai.ci_sequence_number = cp_ci_sequence_number
1750 AND dai.dt_alias = ecc.grading_schema_dt_alias
1751 AND ecc.s_control_num =1
1752 ORDER BY 1;
1753
1754 -- Cursor to get the System Type corresponding to the Person Type Code
1755 -- Added as per the bug# 2364461.
1756 CURSOR cur_sys_per_typ(cp_person_type VARCHAR2) IS
1757 SELECT system_type
1758 FROM igs_pe_person_types
1759 WHERE person_type_code = cp_person_type;
1760 l_cur_sys_per_typ cur_sys_per_typ%ROWTYPE;
1761
1762 -- Cursor to check for audit attempts
1763 -- By selecting no_assessment_ind column corresponding
1764 -- to the Person Id, Unit Offering Options Id, Calendar Type
1765 -- and Calendar Instance
1766 CURSOR cur_no_assessment_ind
1767 IS
1768 SELECT no_assessment_ind
1769 FROM igs_en_plan_units
1770 WHERE person_id = p_person_id
1771 AND course_cd = p_course_cd
1772 AND uoo_id = p_uoo_id
1773 AND cart_error_flag='N';
1774 l_no_assessment_ind VARCHAR2(1);
1775
1776 --Row type variables
1777 l_cur_pe_usr_arg cur_pe_usr_arg%ROWTYPE;
1778 l_cur_en_nstd_usec cur_en_nstd_usec%ROWTYPE;
1779 l_cur_en_cal_conf cur_en_cal_conf%ROWTYPE;
1780 l_cur_chk_cp_chg_val cur_chk_cp_chg_val%ROWTYPE;
1781 l_cur_ps_unit_ofr cur_ps_unit_ofr%ROWTYPE;
1782
1783 -- Variables
1784 l_v_person_type igs_pe_person_types.person_type_code%TYPE;
1785 l_cp_out NUMBER;
1786
1787 BEGIN
1788
1789 -- Check for audit attempt
1790 OPEN cur_no_assessment_ind;
1791 FETCH cur_no_assessment_ind INTO l_no_assessment_ind;
1792 CLOSE cur_no_assessment_ind;
1793 -- Incase of an audit attempt the enrolled CP should not be
1794 -- updateable by the student, hence return 'N'
1795 IF l_no_assessment_ind = 'Y' THEN
1796 RETURN 'N';
1797 END IF;
1798
1799 --Get the person logged in frmo session
1800 l_v_person_type := igs_en_gen_008.enrp_get_person_type(p_course_cd=>NULL);
1801
1802 -- According to ENCR012, check that approved cp are not defined for this student
1803 -- Added as per the bug# 2364461.
1804 -- Start of new code.
1805 OPEN cur_sys_per_typ(l_v_person_type);
1806 FETCH cur_sys_per_typ INTO l_cur_sys_per_typ;
1807 CLOSE cur_sys_per_typ;
1808 -- End of new code.
1809 -- For Bug 2398133,removed the assignment
1810 -- l_v_person_type := l_cur_sys_per_typ.system_type
1811 -- as it was overwritting the person type of the logged in user.
1812 --
1813 IF l_cur_sys_per_typ.system_type = 'STUDENT' THEN
1814 IF fnd_profile.value('IGS_EN_UPDATE_CP_GS')='Y' THEN
1815 RETURN 'N';
1816 END IF;
1817
1818 IF igs_en_gen_015.validation_step_is_overridden(
1819 p_eligibility_step_type => 'VAR_CREDIT_APPROVAL',
1820 p_load_cal_type => p_cal_type,
1821 p_load_cal_seq_number => p_ci_sequence_number,
1822 p_person_id => p_person_id,
1823 p_uoo_id => p_uoo_id,
1824 p_step_override_limit => l_cp_out) THEN
1825 RETURN 'N';
1826 END IF;
1827 END IF;
1828
1829
1830 -- Get the Unit, version_number for the UOO_ID passed into the function
1831 OPEN cur_ps_unit_ofr;
1832 FETCH cur_ps_unit_ofr INTO l_cur_ps_unit_ofr;
1833 CLOSE cur_ps_unit_ofr;
1834
1835 -- check that the unit is set up as allowing points override in PSP
1836 OPEN cur_chk_cp_chg_val(l_cur_ps_unit_ofr.unit_cd,l_cur_ps_unit_ofr.version_number);
1837 FETCH cur_chk_cp_chg_val INTO l_cur_chk_cp_chg_val;
1838 CLOSE cur_chk_cp_chg_val;
1839
1840 IF l_cur_chk_cp_chg_val.points_override_ind = 'Y' THEN
1841 --This above condition means that override cp is allowed
1842 --So check for deadlines at user,unit section level and institution level
1843
1844
1845
1846 --If person type exists, check that any user level deadlines are not passed
1847 IF l_v_person_type IS NOT NULL THEN
1848 -- check if any date_aliases are defined for the person_type
1849 -- if found then validate them
1850 -- else validate dates at Unit Section Level
1851 OPEN cur_pe_usr_arg(l_v_person_type,l_cur_ps_unit_ofr.cal_type, l_cur_ps_unit_ofr.ci_sequence_number);
1852 FETCH cur_pe_usr_arg INTO l_cur_pe_usr_arg;
1853 IF cur_pe_usr_arg%FOUND THEN
1854 CLOSE cur_pe_usr_arg;
1855 OPEN cur_pe_usr_arg(l_v_person_type,l_cur_ps_unit_ofr.cal_type, l_cur_ps_unit_ofr.ci_sequence_number);
1856 LOOP
1857 EXIT WHEN cur_pe_usr_arg%NOTFOUND;
1858 FETCH cur_pe_usr_arg INTO l_cur_pe_usr_arg;
1859 IF ( TRUNC(l_cur_pe_usr_arg.alias_val) < TRUNC(SYSDATE) ) THEN
1860 RETURN 'N';
1861 END IF;
1862 END LOOP;
1863 CLOSE cur_pe_usr_arg;
1864 RETURN 'Y';
1865 ELSE
1866 CLOSE cur_pe_usr_arg;
1867 END IF;
1868 END IF;
1869
1870 --Check if unit section level deadline has not passed
1871 OPEN cur_en_nstd_usec;
1872 FETCH cur_en_nstd_usec INTO l_cur_en_nstd_usec;
1873 IF cur_en_nstd_usec%FOUND THEN
1874 CLOSE cur_en_nstd_usec;
1875 OPEN cur_en_nstd_usec;
1876 LOOP
1877 EXIT WHEN cur_en_nstd_usec%NOTFOUND;
1878 FETCH cur_en_nstd_usec INTO l_cur_en_nstd_usec;
1879 IF ( TRUNC(l_cur_en_nstd_usec.alias_val) < TRUNC(SYSDATE) ) THEN
1880 RETURN 'N';
1881 END IF;
1882 END LOOP;
1883 CLOSE cur_en_nstd_usec;
1884 RETURN 'Y';
1885 ELSE
1886 CLOSE cur_en_nstd_usec;
1887 END IF;
1888
1889 --Check if institution level deadline has not passed
1890 OPEN cur_en_cal_conf(l_cur_ps_unit_ofr.cal_type, l_cur_ps_unit_ofr.ci_sequence_number);
1891 FETCH cur_en_cal_conf INTO l_cur_en_cal_conf;
1892 IF cur_en_cal_conf%FOUND THEN
1893 CLOSE cur_en_cal_conf;
1894 OPEN cur_en_cal_conf(l_cur_ps_unit_ofr.cal_type, l_cur_ps_unit_ofr.ci_sequence_number);
1895 LOOP
1896 EXIT WHEN cur_en_cal_conf%NOTFOUND;
1897 FETCH cur_en_cal_conf INTO l_cur_en_cal_conf;
1898 IF ( TRUNC(l_cur_en_cal_conf.alias_val) < TRUNC(SYSDATE) ) THEN
1899 RETURN 'N';
1900 END IF;
1901 END LOOP;
1902 CLOSE cur_en_cal_conf;
1903 RETURN 'Y';
1904 ELSE
1905 CLOSE cur_en_cal_conf;
1906 RETURN 'Y';
1907 END IF;
1908
1909 ELSE
1910 RETURN 'N';
1911 END IF;
1912
1913 END is_credit_updatable;
1914
1915 PROCEDURE swap_update(
1916 p_person_id IN NUMBER,
1917 p_course_cd IN VARCHAR2,
1918 p_uooid IN NUMBER,
1919 p_fieldname IN VARCHAR2,
1920 p_auditval IN VARCHAR2,
1921 p_creditval IN NUMBER,
1922 p_gradingval IN VARCHAR2,
1923 X_ROWID IN VARCHAR2,
1924 X_UNIT_CD IN VARCHAR2,
1925 X_CAL_TYPE IN VARCHAR2,
1926 X_CI_SEQUENCE_NUMBER IN NUMBER,
1927 X_VERSION_NUMBER IN NUMBER,
1928 X_LOCATION_CD IN VARCHAR2,
1929 X_UNIT_CLASS IN VARCHAR2,
1930 X_CI_START_DT IN DATE,
1931 X_CI_END_DT IN DATE,
1932 X_ENROLLED_DT IN DATE,
1933 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
1934 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
1935 X_DISCONTINUED_DT IN DATE,
1936 X_RULE_WAIVED_DT IN DATE,
1937 X_RULE_WAIVED_PERSON_ID IN NUMBER,
1938 X_NO_ASSESSMENT_IND IN VARCHAR2,
1939 X_SUP_UNIT_CD IN VARCHAR2,
1940 X_SUP_VERSION_NUMBER IN NUMBER,
1941 X_EXAM_LOCATION_CD IN VARCHAR2,
1942 X_ALTERNATIVE_TITLE IN VARCHAR2,
1943 X_OVERRIDE_ENROLLED_CP IN NUMBER,
1944 X_OVERRIDE_EFTSU IN NUMBER,
1945 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
1946 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
1947 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
1948 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
1949 X_WAITLIST_DT IN DATE,
1950 X_DCNT_REASON_CD IN VARCHAR2,
1951 X_GS_VERSION_NUMBER IN NUMBER,
1952 X_ENR_METHOD_TYPE IN VARCHAR2,
1953 X_FAILED_UNIT_RULE IN VARCHAR2,
1954 X_CART IN VARCHAR2,
1955 X_RSV_SEAT_EXT_ID IN NUMBER,
1956 X_ORG_UNIT_CD IN VARCHAR2,
1957 X_GRADING_SCHEMA_CODE IN VARCHAR2,
1958 X_subtitle IN VARCHAR2,
1959 x_session_id IN NUMBER,
1960 X_deg_aud_detail_id IN NUMBER,
1961 x_student_career_transcript IN VARCHAR2,
1962 x_student_career_statistics IN VARCHAR2,
1963 x_waitlist_manual_ind IN VARCHAR2,
1964 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
1965 X_ATTRIBUTE1 IN VARCHAR2,
1966 X_ATTRIBUTE2 IN VARCHAR2,
1967 X_ATTRIBUTE3 IN VARCHAR2,
1968 X_ATTRIBUTE4 IN VARCHAR2,
1969 X_ATTRIBUTE5 IN VARCHAR2,
1970 X_ATTRIBUTE6 IN VARCHAR2,
1971 X_ATTRIBUTE7 IN VARCHAR2,
1972 X_ATTRIBUTE8 IN VARCHAR2,
1973 X_ATTRIBUTE9 IN VARCHAR2,
1974 X_ATTRIBUTE10 IN VARCHAR2,
1975 X_ATTRIBUTE11 IN VARCHAR2,
1976 X_ATTRIBUTE12 IN VARCHAR2,
1977 X_ATTRIBUTE13 IN VARCHAR2,
1978 X_ATTRIBUTE14 IN VARCHAR2,
1979 X_ATTRIBUTE15 IN VARCHAR2,
1980 X_ATTRIBUTE16 IN VARCHAR2,
1981 X_ATTRIBUTE17 IN VARCHAR2,
1982 X_ATTRIBUTE18 IN VARCHAR2,
1983 X_ATTRIBUTE19 IN VARCHAR2,
1984 x_ATTRIBUTE20 IN VARCHAR2,
1985 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER,
1986 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER,
1987 X_CORE_INDICATOR_CODE IN VARCHAR2,
1988 X_UPD_AUDIT_FLAG IN VARCHAR2,
1989 X_SS_SOURCE_IND IN VARCHAR2)
1990 ------------------------------------------------------------------
1991 --Created by : vijrajag
1992 --Date created: 04-July-2005
1993 --
1994 --Purpose: autonomous wrapper over update_plansheet_utnidetails
1995 --
1996 --Known limitations/enhancements and/or remarks:
1997 --
1998 --Change History:
1999 --Who When What
2000 -------------------------------------------------------------------
2001 IS
2002 PRAGMA AUTONOMOUS_TRANSACTION;
2003 BEGIN
2004 igs_en_su_attempt_pkg.lock_row(
2005 X_ROWID => X_ROWID,
2006 X_PERSON_ID => p_person_id,
2007 X_COURSE_CD => p_course_cd,
2008 X_UNIT_CD => X_UNIT_CD,
2009 X_CAL_TYPE => X_CAL_TYPE,
2010 X_CI_SEQUENCE_NUMBER => X_CI_SEQUENCE_NUMBER,
2011 X_VERSION_NUMBER => X_VERSION_NUMBER,
2012 X_LOCATION_CD => X_LOCATION_CD,
2013 X_UNIT_CLASS => X_UNIT_CLASS,
2014 X_CI_START_DT => X_CI_START_DT,
2015 X_CI_END_DT => X_CI_END_DT,
2016 X_UOO_ID => p_uooid,
2017 X_ENROLLED_DT => X_ENROLLED_DT,
2018 X_UNIT_ATTEMPT_STATUS => X_UNIT_ATTEMPT_STATUS,
2019 X_ADMINISTRATIVE_UNIT_STATUS => X_ADMINISTRATIVE_UNIT_STATUS,
2020 X_DISCONTINUED_DT => X_DISCONTINUED_DT,
2021 X_RULE_WAIVED_DT => X_RULE_WAIVED_DT,
2022 X_RULE_WAIVED_PERSON_ID => X_RULE_WAIVED_PERSON_ID,
2023 X_NO_ASSESSMENT_IND => X_NO_ASSESSMENT_IND,
2024 X_SUP_UNIT_CD => X_SUP_UNIT_CD,
2025 X_SUP_VERSION_NUMBER => X_SUP_VERSION_NUMBER,
2026 X_EXAM_LOCATION_CD => X_EXAM_LOCATION_CD,
2027 X_ALTERNATIVE_TITLE => X_ALTERNATIVE_TITLE,
2028 X_OVERRIDE_ENROLLED_CP => X_OVERRIDE_ENROLLED_CP,
2029 X_OVERRIDE_EFTSU => X_OVERRIDE_EFTSU,
2030 X_OVERRIDE_ACHIEVABLE_CP => X_OVERRIDE_ACHIEVABLE_CP,
2031 X_OVERRIDE_OUTCOME_DUE_DT => X_OVERRIDE_OUTCOME_DUE_DT,
2032 X_OVERRIDE_CREDIT_REASON => X_OVERRIDE_CREDIT_REASON,
2033 X_ADMINISTRATIVE_PRIORITY => X_ADMINISTRATIVE_PRIORITY,
2034 X_WAITLIST_DT => X_WAITLIST_DT,
2035 X_DCNT_REASON_CD => X_DCNT_REASON_CD,
2036 X_GS_VERSION_NUMBER => X_GS_VERSION_NUMBER,
2037 X_ENR_METHOD_TYPE => X_ENR_METHOD_TYPE,
2038 X_FAILED_UNIT_RULE => X_FAILED_UNIT_RULE,
2039 X_CART => X_CART,
2040 X_RSV_SEAT_EXT_ID => X_RSV_SEAT_EXT_ID,
2041 X_ORG_UNIT_CD => X_ORG_UNIT_CD,
2042 X_GRADING_SCHEMA_CODE => X_GRADING_SCHEMA_CODE,
2043 X_subtitle => X_subtitle,
2044 x_session_id => x_session_id,
2045 X_deg_aud_detail_id => X_deg_aud_detail_id,
2046 x_student_career_transcript => x_student_career_transcript,
2047 x_student_career_statistics => x_student_career_statistics,
2048 x_waitlist_manual_ind => x_waitlist_manual_ind,
2049 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
2050 X_ATTRIBUTE1 => X_ATTRIBUTE1,
2051 X_ATTRIBUTE2 => X_ATTRIBUTE2,
2052 X_ATTRIBUTE3 => X_ATTRIBUTE3,
2053 X_ATTRIBUTE4 => X_ATTRIBUTE4,
2054 X_ATTRIBUTE5 => X_ATTRIBUTE5,
2055 X_ATTRIBUTE6 => X_ATTRIBUTE6,
2056 X_ATTRIBUTE7 => X_ATTRIBUTE7,
2057 X_ATTRIBUTE8 => X_ATTRIBUTE8,
2058 X_ATTRIBUTE9 => X_ATTRIBUTE9,
2059 X_ATTRIBUTE10 => X_ATTRIBUTE10,
2060 X_ATTRIBUTE11 => X_ATTRIBUTE11,
2061 X_ATTRIBUTE12 => X_ATTRIBUTE12,
2062 X_ATTRIBUTE13 => X_ATTRIBUTE13,
2063 X_ATTRIBUTE14 => X_ATTRIBUTE14,
2064 X_ATTRIBUTE15 => X_ATTRIBUTE15,
2065 X_ATTRIBUTE16 => X_ATTRIBUTE16,
2066 X_ATTRIBUTE17 => X_ATTRIBUTE17,
2067 X_ATTRIBUTE18 => X_ATTRIBUTE18,
2068 X_ATTRIBUTE19 => X_ATTRIBUTE19,
2069 x_ATTRIBUTE20 => x_ATTRIBUTE20,
2070 X_WLST_PRIORITY_WEIGHT_NUM => X_WLST_PRIORITY_WEIGHT_NUM,
2071 X_WLST_PREFERENCE_WEIGHT_NUM => X_WLST_PREFERENCE_WEIGHT_NUM,
2072 X_CORE_INDICATOR_CODE => X_CORE_INDICATOR_CODE,
2073 X_UPD_AUDIT_FLAG => X_UPD_AUDIT_FLAG,
2074 X_SS_SOURCE_IND => X_SS_SOURCE_IND);
2075
2076 update_plansheet_unitdetails(
2077 p_person_id => p_person_id,
2078 p_course_cd => p_course_cd,
2079 p_uooid => p_uooid,
2080 p_cartflag => NULL,
2081 p_sourceflag => 'SUA',
2082 p_fieldname => p_fieldname,
2083 p_auditval => p_auditval,
2084 p_creditval => p_creditval,
2085 p_gradingval => p_gradingval);
2086 COMMIT;
2087 EXCEPTION
2088 WHEN OTHERS THEN
2089 ROLLBACK;
2090 RAISE;
2091 END swap_update;
2092
2093 PROCEDURE plan_update(
2094 p_person_id IN NUMBER,
2095 p_course_cd IN VARCHAR2,
2096 p_uoo_id IN NUMBER,
2097 p_fieldname IN VARCHAR2,
2098 p_auditval IN VARCHAR2,
2099 p_creditval IN NUMBER,
2100 p_gradingval IN VARCHAR2,
2101 p_row_id IN VARCHAR2,
2102 p_term_cal_type IN VARCHAR2,
2103 p_term_ci_sequence_number IN NUMBER,
2104 p_no_assessment_ind IN VARCHAR2,
2105 p_sup_uoo_id IN NUMBER,
2106 p_override_enrolled_cp IN NUMBER,
2107 p_grading_schema_code IN VARCHAR2,
2108 p_gs_version_number IN NUMBER,
2109 p_core_indicator_code IN VARCHAR2,
2110 p_alternative_title IN VARCHAR2,
2111 p_cart_error_flag IN VARCHAR2,
2112 p_session_id IN NUMBER
2113 )
2114 ------------------------------------------------------------------
2115 --Created byp : Chanchal
2116 --Date creatped: 30-Jul-2005
2117 --
2118 --Purpose: autonomous wrapper over update_plansheet_utnidetails to
2119 -- update planning sheet record.
2120 --Known limitations/enhancements and/or remarks:
2121 --
2122 --Change History:
2123 --Who When What
2124 -------------------------------------------------------------------
2125 IS
2126 PRAGMA AUTONOMOUS_TRANSACTION;
2127 BEGIN
2128 igs_en_plan_units_pkg.lock_row (
2129 x_rowid => p_row_id,
2130 x_person_id => p_person_id,
2131 x_course_cd => p_course_cd,
2132 x_uoo_id => p_uoo_id,
2133 x_term_cal_type => p_term_cal_type,
2134 x_term_ci_sequence_number => p_term_ci_sequence_number,
2135 x_no_assessment_ind => p_no_assessment_ind,
2136 x_sup_uoo_id => p_sup_uoo_id,
2137 x_override_enrolled_cp => p_override_enrolled_cp,
2138 x_grading_schema_code => p_grading_schema_code,
2139 x_gs_version_number => p_gs_version_number,
2140 x_core_indicator_code => p_core_indicator_code,
2141 x_alternative_title => p_alternative_title,
2142 x_cart_error_flag => p_cart_error_flag,
2143 x_session_id => p_session_id
2144 );
2145 update_plansheet_unitdetails(
2146 p_person_id => p_person_id,
2147 p_course_cd => p_course_cd,
2148 p_uooid => p_uoo_id,
2149 p_cartflag => p_cart_error_flag,
2150 p_sourceflag => 'PLAN',
2151 p_fieldname => p_fieldname,
2152 p_auditval => p_auditval,
2153 p_creditval => p_creditval,
2154 p_gradingval => p_gradingval);
2155 COMMIT;
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 ROLLBACK;
2159 RAISE;
2160 END plan_update;
2161
2162
2163 -- This procedure releases the seats as the unit sections are dropped as a part of DROP page
2164 -- smaddali 8-dec-2005 added new procedure to update spa status,
2165 -- matriculation , seat counts and reserved seat counts for DROP : bug#4864437
2166 PROCEDURE drop_submit (person_id IN NUMBER,
2167 program_cd IN VARCHAR2,
2168 p_uoo_ids IN VARCHAR2) AS
2169 CURSOR cur_sua(cp_person_id IN NUMBER,cp_program_cd IN VARCHAR2,cp_uoo_id IN NUMBER) IS
2170 SELECT *
2171 FROM IGS_EN_SU_ATTEMPT_ALL
2172 WHERE person_id = cp_person_id
2173 AND course_cd = cp_program_cd
2174 AND uoo_Id = cp_uoo_Id;
2175
2176 CURSOR cur_igs_ps_rsv_ext (cp_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE) IS
2177 SELECT rsv.ROWID row_id, rsv.*
2178 FROM igs_ps_rsv_ext rsv
2179 WHERE rsv_ext_id = cp_rsv_ext_id FOR UPDATE;
2180
2181 l_cur_igs_ps_rsv_ext cur_igs_ps_rsv_ext%ROWTYPE;
2182 l_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE;
2183 old_rec igs_en_su_attempt_all%rowTYPE;
2184 new_rec igs_en_su_attempt_all%ROWTYPE;
2185 l_uoo_ids varchar2(30);
2186 l_cur_uoo_id varchar2(30);
2187 pos number;
2188 v_message_name VARCHAR2(30);
2189
2190 PROCEDURE get_old_sua (p_person_id IN NUMBER,p_program_cd IN VARCHAR2,p_uoo_id IN NUMBER,
2191 p_sua_rec OUT NOCOPY igs_en_su_attempt_all%ROWTYPE ) AS
2192 PRAGMA AUTONOMOUS_TRANSACTION;
2193
2194 CURSOR c_old_sua IS
2195 SELECT *
2196 FROM IGS_EN_SU_ATTEMPT_ALL
2197 WHERE person_id = p_person_id
2198 AND course_cd = p_program_cd
2199 AND uoo_Id = p_uoo_Id;
2200 c_old_sua_rec c_old_sua%ROWTYPE;
2201
2202 BEGIN
2203 OPEN c_old_sua;
2204 FETCH c_old_sua INTO p_sua_rec;
2205 CLOSE c_old_sua;
2206 ROLLBACK;
2207 RETURN ;
2208
2209 END;
2210
2211 BEGIN
2212 l_uoo_ids := p_uoo_ids;
2213 WHILE l_uoo_ids is not null LOOP
2214 pos := instr(l_uoo_ids,',',1);
2215 IF pos=0 THEN
2216 l_cur_uoo_id := l_uoo_ids;
2217 l_uoo_ids := null;
2218 ELSE
2219 l_cur_uoo_id := substr(l_uoo_ids,0,pos-1);
2220 l_uoo_ids := substr(l_uoo_ids,pos+1,length(l_uoo_ids));
2221 END IF;
2222
2223 -- fetch the old sua record, since this transaction is not commited,
2224 -- get it from an autonomous transaction
2225 old_rec := NULL;
2226 new_rec := NULL;
2227 get_old_sua(person_id, program_cd, l_cur_uoo_id,old_rec) ;
2228
2229 -- fetch the new sua record
2230 OPEN cur_sua(person_id, program_cd, l_cur_uoo_id);
2231 FETCH cur_sua INTO new_rec;
2232 CLOSE cur_sua;
2233
2234 -- if reserve seating is existing then need to decrement the actual seat enrolled
2235 -- which was not done during swap drop.
2236 -- checking if the unit attempt status has been changed from ENROLLED/INVALID to DROPPED/DISCONTIN
2237 IF (old_rec.unit_attempt_status IN ('ENROLLED','INVALID')
2238 AND (new_rec.unit_attempt_status IN ('DROPPED','DISCONTIN'))) THEN
2239 IF ( old_rec.rsv_seat_ext_id = new_rec.rsv_seat_ext_id ) THEN
2240 l_rsv_ext_id := old_rec.rsv_seat_ext_id;
2241 ELSE
2242 l_rsv_ext_id := new_rec.rsv_seat_ext_id;
2243 END IF;
2244
2245 OPEN cur_igs_ps_rsv_ext(l_rsv_ext_id);
2246 FETCH cur_igs_ps_rsv_ext INTO l_cur_igs_ps_rsv_ext;
2247 CLOSE cur_igs_ps_rsv_ext;
2248
2249
2250 -- If the unit attempt status is changed then the actual seats enrolled column has to be decreased by one
2251 IF ((l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1) >= 0) THEN
2252 igs_ps_rsv_ext_pkg.update_row( x_rowid => l_cur_igs_ps_rsv_ext.row_id,
2253 x_rsv_ext_id => l_cur_igs_ps_rsv_ext.rsv_ext_id,
2254 x_uoo_id => l_cur_igs_ps_rsv_ext.uoo_id,
2255 x_priority_id => l_cur_igs_ps_rsv_ext.priority_id,
2256 x_preference_id => l_cur_igs_ps_rsv_ext.preference_id,
2257 x_rsv_level => l_cur_igs_ps_rsv_ext.rsv_level,
2258 x_actual_seat_enrolled => l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1,
2259 x_mode => 'R'
2260 );
2261 END IF;
2262 END IF;
2263
2264
2265 IF IGS_EN_GEN_012.ENRP_UPD_SCA_STATUS(
2266 new_rec.person_id,
2267 new_rec.course_cd,
2268 v_message_name) = FALSE THEN
2269 FND_MESSAGE.SET_NAME('IGS',v_message_name);
2270 IGS_GE_MSG_STACK.ADD;
2271 APP_EXCEPTION.RAISE_EXCEPTION;
2272 END IF;
2273
2274 IGS_EN_GEN_003.UPD_MAT_MRADM_CAT_TERMS(new_rec.person_id,
2275 new_rec.course_cd,
2276 new_rec.unit_attempt_status,
2277 new_rec.cal_type,
2278 new_rec.ci_sequence_number
2279 ) ;
2280
2281 igs_en_sua_api.upd_enrollment_counts( 'UPDATE',
2282 old_rec,
2283 new_rec);
2284 END LOOP;
2285
2286 END drop_submit;
2287
2288
2289 FUNCTION get_sua_fin_mark(p_person_id IN igs_en_su_attempt_all.person_id%TYPE,
2290 p_course_cd IN igs_en_su_attempt_all.course_Cd%TYPE,
2291 p_uoo_id IN igs_en_su_attempt_all.uoo_id%TYPE)
2292 RETURN NUMBER IS
2293
2294 CURSOR c_suao IS
2295 SELECT susv.mark
2296 FROM igs_as_su_stmptout susv
2297 WHERE
2298 susv.PERSON_ID = p_person_id AND
2299 susv.course_cd =p_course_cd AND
2300 susv.uoo_id = p_uoo_id AND
2301 susv.finalised_outcome_ind = 'Y' and
2302 susv.grading_period_cd <> 'MIDTERM'
2303 ORDER BY susv.outcome_dt DESC ;
2304
2305 l_mark igs_as_su_stmptout.mark%TYPE;
2306
2307 BEGIN
2308 OPEN c_suao;
2309 FETCH c_suao INTO l_mark;
2310 CLOSE c_suao;
2311 RETURN l_mark;
2312 END get_sua_fin_mark;
2313
2314 FUNCTION get_sua_fin_grade(p_person_id IN igs_en_su_attempt_all.person_id%TYPE,
2315 p_course_cd IN igs_en_su_attempt_all.course_Cd%TYPE,
2316 p_uoo_id IN igs_en_su_attempt_all.uoo_id%TYPE)
2317 RETURN VARCHAR2 IS
2318
2319 CURSOR c_suao IS
2320 SELECT susv.grade
2321 FROM igs_as_su_stmptout susv
2322 WHERE
2323 susv.PERSON_ID = p_person_id AND
2324 susv.course_cd =p_course_cd AND
2325 susv.uoo_id = p_uoo_id AND
2326 susv.finalised_outcome_ind = 'Y' and
2327 susv.grading_period_cd <> 'MIDTERM'
2328 ORDER BY susv.outcome_dt DESC ;
2329
2330 l_grade igs_as_su_stmptout.grade%TYPE;
2331
2332 BEGIN
2333 OPEN c_suao;
2334 FETCH c_suao INTO l_grade;
2335 CLOSE c_suao;
2336 RETURN l_grade;
2337
2338 END get_sua_fin_grade;
2339
2340 END IGS_EN_PLAN_UTILS;