1 PACKAGE BODY IGS_EN_GEN_013 AS
2 /* $Header: IGSEN13B.pls 120.1 2005/09/23 03:10:32 appldev ship $ */
3
4 -- Modification By : jbegum
5 -- Modification : Removed the following 3 functions:
6 -- Enrp_Val_Sca_Fs , Enrp_Val_Sua_Excld , Enrp_Val_Sua_Pre
7 -- The functions Enrp_Val_Sca_Fs , Enrp_Val_Sua_Excld were not being called from anywhere .
8 -- Also identical functions were present in the packages of IGSEN61B.pls and IGSEN68B.pls .
9 -- Hence these two functions were removed
10 -- The function Enrp_Val_Sua_Pre was being called from the package in IGSEN09B.pls.But the
11 -- exact replica of it was found in IGSEN68B.pls.Hence it was removed and the call to
12 -- this function from IGSEN09B.pls was replaced with the call to its replica in IGSEN68B.pls
13
14 -- Modified For : Enhancement Bug #1832130
15 -- Modified By : jbegum
16 -- Modification : The function Enrp_Upd_Sci_Ua was modified as part of the Enrollments Process build.
17 -- In the call to the update row of the table IGS_EN_SU_ATTEMPT the following columns were added:
18 -- GS_VERSION_NUMBER , ENR_METHOD_TYPE , FAILED_UNIT_RULE , CART , RSV_SEAT_EXT_ID .
19 -- In the call to the functions IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD and IGS_EN_GEN_008.ENRP_GET_UDDC_AUS
20 -- parameter UOO_ID has been added.
21 -- Also when the function IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD returns 'Y' the existing code use to have
22 -- a call to the delete row of the table IGS_EN_SU_ATTEMPT.This has been replaced with a call to update row
23 -- of the table IGS_EN_SU_ATTEMPT with the unit_attempt_status updated to 'DROPPED'
24 --Added refernces to column ORG_UNIT_CD incall to IGS_EN_SU_ATTEMPT TBH call as a part of bug 1964697
25 --
26 --nalkumar 12-Oct-2001 Added CATALOG_CAL_TYPE and CATALOG_SEQ_NUM parameters to call IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW as per the
27 -- Career impact Build Bug# 2027984.
28 --kkillams 20-12-2001 Added attribute_category to attribute20 parameters are added to call IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW as per the
29 -- YOP-EN build Bug No: 2156956.
30 --svenkata 20-Dec-2001 Added columns student_career_transcript and Student_career_statistics as part of build Career
31 -- Impact Part2 . Bug #2158626
32 --svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added to table handler
33 -- procedure calls as part of CCR - ENCR022.
34 --Nishikant 29-jan-2002 Added the column session_id in the Tbh calls of IGS_EN_SU_ATTEMPT_PKG
35 -- as a part of the bug 2172380.
36 --Nishikant 15-may-2002 Condition in an IF clause in the function Enrp_Upd_Sci_Ua modified as part of the bug#2364216.
37 --Who When What
38 --mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in update row of IGS_EN_SU_ATTEMPT
39 -- for Bug 2554109 MINI Waitlist Build for Jan 03 Release
40 --amuthu 03-OCT-2002 Modified Enrp_Upd_Sci_Ua to invoke workflow when a unit is dropped or discontinued
41 -- To do this created a new procedure invoke_drop_workflow. This was done as part of
42 -- Drop Transfer Workflow Build. Bug number 2599925.
43 -- pradhakr 16-Dec-2002 Changed the call to the update_row of igs_en_su_attempt
44 -- table to igs_en_sua_api.update_unit_attempt. |
45 -- Changes wrt ENCR031 build. Bug#2643207 |
46 --svanukur 26-jun-2003 Passing discontinued date with a nvl substitution of sysdate in the call to the update_row api of
47 -- ig_en_su_attmept in case of a "dropped" unit attempt status as part of bug 2898213.
48 --rvivekan 3-SEP-2003 Waitlist Enhacements build # 3052426. 2 new columns added to
49 -- IGS_EN_SU_ATTEMPT_PKG procedures and consequently to IGS_EN_SUA_API procedures
50 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
51 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
52
53 PROCEDURE invoke_drop_workflow(p_uoo_ids IN VARCHAR2,
54 p_unit_cds IN VARCHAR2,
55 p_teach_cal_type IN VARCHAR2,
56 p_teach_ci_sequence_number IN NUMBER,
57 p_person_id IN NUMBER,
58 p_course_cd IN VARCHAR2,
59 p_message_name IN OUT NOCOPY VARCHAR2)
60 AS
61 CURSOR c_tl IS
62 SELECT load_cal_type, load_ci_sequence_number
63 FROM IGS_CA_TEACH_TO_LOAD_V
64 WHERE teach_cal_type = p_teach_cal_type
65 AND teach_ci_sequence_number = p_teach_ci_sequence_number
66 ORDER BY LOAD_START_DT ASC;
67
68 CURSOR c_reason IS
69 SELECT meaning
70 FROM IGS_LOOKUPS_VIEW
71 WHERE lookup_type = 'CRS_ATTEMPT_STATUS'
72 AND lookup_CODE = 'INTERMIT';
73
74 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
75 l_load_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
76 l_return_status VARCHAR2(10);
77 l_meaning IGS_LOOKUPS_VIEW.MEANING%TYPE;
78
79 BEGIN
80
81 OPEN c_tl;
82 FETCH c_tl INTO l_load_cal_type, l_load_ci_sequence_number;
83 CLOSE c_tl;
84
85 OPEN c_reason;
86 FETCH c_reason INTO l_meaning;
87 CLOSE c_reason;
88
89 FND_MESSAGE.SET_NAME('IGS','IGS_EN_REASON_DRP_UNIT');
90 FND_MESSAGE.SET_TOKEN('UNIT',p_unit_cds);
91 FND_MESSAGE.SET_TOKEN('REASON',l_meaning);
92 igs_ss_en_wrappers.drop_notif_variable(FND_MESSAGE.GET(),'PROGRAM_INTERMISSION' );
93
94 END invoke_drop_workflow;
95
96
97 FUNCTION Enrp_Upd_Sci_Ua(
98 p_person_id IN NUMBER ,
99 p_course_cd IN VARCHAR2 ,
100 p_start_dt IN DATE ,
101 p_end_dt IN DATE ,
102 p_message_name OUT NOCOPY VARCHAR2)
103 RETURN boolean AS
104 -------------------------------------------------------------------------------------------
105 -- Update the IGS_EN_SU_ATTEMPT records as appropriate for the
106 -- student_intermission detail which has been entered.
107 -- Where the intermission start date is before the census date AND
108 -- intermission end date is after the census date,
109 -- REVISION (23/07/97): AND where the intermission start
110 -- date is AFTER the census date and before the variation cutoff date
111 -- (and if the variation cutoff date is not set the end date of
112 -- the calendar instance) (END REVISION)
113 -- the process will attempt to discontinue/remove the IGS_PS_UNIT attempts.
114 -- Whether to discontinue or remove IGS_PS_UNIT attempts is dependent on the
115 -- "IGS_PS_UNIT discontinuation criteria" within the teaching period calendar;
116 -- if the intermission start date is prior to this date, the IGS_PS_UNIT attempts
117 -- are removed, otherwise they are discontinued.
118 -- If there are problems the routine will set the message number
119 -- and return FALSE.
120 --Change History:
121 --Who When What
122 --kkillams 28-04-2003 Modified cursor igs_en_su_attempt_cur due to change in pk of
123 -- student unit attempt w.r.t. bug number 2829262
124 --vangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
125 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
126 --bdeviset 13-SEP-2004 Added cursor c_sua_grade w.r.t bug no 3885804
127 -------------------------------------------------------------------------------------------
128 BEGIN
129 DECLARE
130 NO_S_GEN_CAL_CONF_REC_FOUND EXCEPTION;
131 v_message_name VARCHAR2(30);
132 v_administrative_unit_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
133 v_discontinuation_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE;
134 v_census_dt_alias IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE;
135 v_do_remove BOOLEAN:=FALSE;
136 -- Revision 23/07/97
137 e_vrn_cutoff_dt_alias_notfound EXCEPTION;
138 v_variation_cutoff_dt_alias IGS_EN_CAL_CONF.variation_cutoff_dt_alias%TYPE;
139 v_after_cutoff_warning BOOLEAN;
140 v_dummy_date DATE;
141 v_admin_unit_status_str VARCHAR2(2000);
142 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
143 v_max_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
144
145 CURSOR c_s_enr_cal_conf IS
146 SELECT variation_cutoff_dt_alias
147 FROM IGS_EN_CAL_CONF
148 WHERE s_control_num = 1;
149
150 CURSOR c_get_max_alias_val (
151 cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
152 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
153 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
154 SELECT
155 MAX(alias_val)
156 FROM
157 IGS_CA_DA_INST_V
158 WHERE cal_type = cp_cal_type AND
159 ci_sequence_number = cp_ci_sequence_number AND
160 dt_alias = cp_dt_alias AND
161 alias_val IS NOT NULL;
162
163 CURSOR c_s_gen_cal_conf IS
164 SELECT census_dt_alias
165 FROM IGS_GE_S_GEN_CAL_CON
166 WHERE s_control_num = 1;
167
168 CURSOR c_student_unit_attempt(
169 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
170 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE) IS
171 SELECT DISTINCT sua.cal_type,
172 sua.ci_sequence_number,
173 ci.end_dt
174 FROM
175 IGS_EN_SU_ATTEMPT sua,
176 IGS_CA_INST ci
177 WHERE
178 sua.person_id = cp_person_id AND
179 sua.course_cd = cp_course_cd AND
180 sua.unit_attempt_status IN ('UNCONFIRM', 'ENROLLED') AND
181 ci.cal_type = sua.cal_type AND
182 ci.sequence_number = sua.ci_sequence_number;
183
184 CURSOR c_student_unit_attempt_ci(
185 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
186 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
187 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
188 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
189 SELECT unit_cd,
190 version_number,
191 cal_type,
192 ci_sequence_number,
193 ci_start_dt,
194 ci_end_dt,
195 enrolled_dt,
196 uoo_id,
197 unit_attempt_status
198 FROM IGS_EN_SU_ATTEMPT
199 WHERE person_id = cp_person_id AND
200 course_cd = cp_course_cd AND
201 cal_type = cp_cal_type AND
202 ci_sequence_number = cp_ci_sequence_number AND
203 unit_attempt_status = 'ENROLLED';
204
205 CURSOR c_dai_v(cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
206 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
207 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
208 SELECT alias_val
209 FROM IGS_CA_DA_INST_V
210 WHERE
211 cal_type = cp_cal_type AND
212 ci_sequence_number = cp_ci_sequence_number AND
213 dt_alias = cp_dt_alias AND
214 alias_val IS NOT NULL
215 ORDER BY alias_val ASC;
216
217 -- cursor to determine of a unit attempt exists which has a finalized
218 -- or un-finalised mid-term grade or an un-finalized final grade.
219 CURSOR c_sua_grade IS
220 SELECT 'x' FROM IGS_AS_SU_STMPTOUT ou, igs_en_su_attempt sua
221 where sua . person_id = p_person_id
222 and sua .course_cd = p_course_cd
223 AND sua .unit_attempt_status = 'ENROLLED'
224 AND sua.person_id = ou.person_id
225 AND sua.course_cd = ou.course_cd
226 AND sua.uoo_id = ou.uoo_id;
227
228
229
230 v_other_detail VARCHAR(255);
231 v_unit_att_exists VARCHAR2(1);
232
233 BEGIN
234 p_message_name := null;
235 v_after_cutoff_warning := FALSE;
236
237 -- Get the census date alias
238 OPEN c_s_gen_cal_conf;
239 FETCH c_s_gen_cal_conf INTO v_census_dt_alias;
240 IF (c_s_gen_cal_conf%NOTFOUND) THEN
241 CLOSE c_s_gen_cal_conf;
242 RAISE NO_S_GEN_CAL_CONF_REC_FOUND;
243 END IF;
244
245 CLOSE c_s_gen_cal_conf;
246
247 -- Get the variation cutoff date alias
248 OPEN c_s_enr_cal_conf;
249 FETCH c_s_enr_cal_conf INTO v_variation_cutoff_dt_alias;
250 IF (c_s_enr_cal_conf%NOTFOUND) THEN
251 CLOSE c_s_enr_cal_conf;
252 RAISE e_vrn_cutoff_dt_alias_notfound;
253 END IF;
254 CLOSE c_s_enr_cal_conf;
255
256 --if any unit attempt exists which has a finalized or un-finalised mid-term grade
257 -- or an un-finalized final grade then cannot drop/discontinue.
258 OPEN c_sua_grade;
259 FETCH c_sua_grade INTO v_unit_att_exists;
260 IF c_sua_grade%FOUND THEN
261 CLOSE c_sua_grade;
262 P_message_name := 'IGS_EN_GRD_EXST_INTM';
263 Return false;
264 ELSE
265 CLOSE c_sua_grade;
266 END IF;
267
268
269 FOR v_sua_rec IN c_student_unit_attempt(p_person_id,
270 p_course_cd)
271 LOOP
272
273 v_do_remove := FALSE;
274 v_alias_val := NULL;
275
276 FOR v_daiv_rec IN c_dai_v(v_sua_rec.cal_type,
277 v_sua_rec.ci_sequence_number,
278 v_census_dt_alias)
279 LOOP
280 v_alias_val := v_daiv_rec.alias_val;
281 IF (v_alias_val BETWEEN p_start_dt AND p_end_dt) THEN
282 v_do_remove := TRUE;
283 EXIT;
284 END IF;
285 END LOOP;
286
287 IF (v_alias_val is not null AND
288 v_do_remove = FALSE AND
289 p_start_dt >= v_alias_val) THEN
290
291 IF (v_variation_cutoff_dt_alias IS NOT NULL) THEN
292
293 OPEN c_get_max_alias_val(v_sua_rec.cal_type,
294 v_sua_rec.ci_sequence_number,
295 v_variation_cutoff_dt_alias);
296 FETCH c_get_max_alias_val INTO v_max_alias_val;
300
297
298 IF (c_get_max_alias_val%FOUND AND
299 v_max_alias_val IS NOT NULL) THEN
301 IF (SYSDATE <= v_max_alias_val) THEN
302 IF (p_start_dt <= v_sua_rec.end_dt) THEN
303 v_do_remove := TRUE;
304 END IF;
305 ELSE
306 v_after_cutoff_warning := TRUE;
307 END IF;
308
309 ELSIF (p_start_dt <= v_sua_rec.end_dt) THEN
310
311 v_do_remove := TRUE;
312
313 END IF;
314
315 CLOSE c_get_max_alias_val;
316
317 ELSE
318
319 IF (p_start_dt <= v_sua_rec.end_dt) THEN
320 v_do_remove := TRUE;
321 END IF;
322
323 END IF;
324
325 END IF;
326 IF (v_do_remove = TRUE) THEN
327
328 FOR v_suaci_rec IN c_student_unit_attempt_ci(p_person_id,
329 p_course_cd,
330 v_sua_rec.cal_type,
331 v_sua_rec.ci_sequence_number)
332 LOOP
333
334 IF (p_start_dt < SYSDATE) THEN
335 v_discontinuation_dt := p_start_dt;
336 ELSE
337 v_discontinuation_dt := SYSDATE;
338 END IF;
339
340 IF (TRUNC(v_discontinuation_dt) < TRUNC(v_suaci_rec.enrolled_dt)) THEN
341 p_message_name := 'IGS_EN_ENRDT_EFFECT_UA';
342 RETURN FALSE;
343 END IF;
344
345 -- Added the AND clause in the below If condtion AND unit_attempt status is not WAITLISTED
346 -- Added by Nishikant - bug#2364216. If the unit attempt cannot be deleted and the unit attempt status is not WAITLISTED
347 -- then proceed to discontinue the student unit attempt. Otherwise Proceed to drop the unit attempt.
348 IF (IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD(v_suaci_rec.cal_type,
349 v_suaci_rec.ci_sequence_number,
350 v_discontinuation_dt,
351 v_suaci_rec.uoo_id)= 'N'
352 AND v_suaci_rec.unit_attempt_status <> 'WAITLISTED' ) THEN
353 v_administrative_unit_status := IGS_EN_GEN_008.ENRP_GET_UDDC_AUS(v_discontinuation_dt,
354 v_suaci_rec.cal_type,
355 v_suaci_rec.ci_sequence_number,
356 v_admin_unit_status_str,
357 v_dummy_date,
358 v_suaci_rec.uoo_id);
359
360 -- IGS_GE_NOTE: If there is more than one possible administrative IGS_PS_UNIT status
361 -- then the v_administrative_unit_status will be NULL and the possible
362 -- statuses will be concatenated in the v_admin_unit_status_str OUT NOCOPY
363 -- parameter. If this is the case the first possible status is used. This
364 -- may be revised in the future when full 'default' admin IGS_PS_UNIT statuses
365 -- come into effect.
366
367 IF (v_administrative_unit_status IS NULL) THEN
368 p_message_name := 'IGS_EN_CANT_DISCONTINUE';
369 RETURN FALSE;
370 END IF;
371
372 DECLARE
373 v_unit_cds VARCHAR2(4000);
374 v_uoo_ids VARCHAR2(4000);
375
376 CURSOR igs_en_su_attempt_cur IS
377 SELECT igs_en_su_attempt.*
378 FROM igs_en_su_attempt
379 WHERE person_id = p_person_id AND
380 course_cd = p_course_cd AND
381 uoo_id = v_suaci_rec.uoo_id;
382
383 BEGIN
384 v_uoo_ids := null;
385 v_unit_cds := null;
386
387
388 FOR IGS_EN_SU_ATTEMPT_rec IN IGS_EN_SU_ATTEMPT_cur LOOP
389 -- Call the API to update the student unit attempt. This API is a
390 -- wrapper to the update row of the TBH.
391 invoke_drop_workflow(
392 p_uoo_ids => v_uoo_ids,
393 p_unit_cds => v_unit_cds,
394 p_teach_cal_type => v_suaci_rec.cal_type,
395 p_teach_ci_sequence_number => v_suaci_rec.ci_sequence_number,
396 p_person_id => p_person_id,
397 p_course_cd => p_course_cd,
398 p_message_name => v_message_name
399 );
400 igs_en_sua_api.update_unit_attempt(
401 X_ROWID => igs_en_su_attempt_rec.row_id,
402 X_PERSON_ID => igs_en_su_attempt_rec.person_id,
403 X_COURSE_CD => igs_en_su_attempt_rec.course_cd,
404 X_UNIT_CD => igs_en_su_attempt_rec.unit_cd,
405 X_CAL_TYPE => igs_en_su_attempt_rec.cal_type,
406 X_CI_SEQUENCE_NUMBER => igs_en_su_attempt_rec.ci_sequence_number,
407 X_VERSION_NUMBER => igs_en_su_attempt_rec.version_number,
408 X_LOCATION_CD => igs_en_su_attempt_rec.location_cd,
409 X_UNIT_CLASS => igs_en_su_attempt_rec.unit_class,
413 X_ENROLLED_DT => igs_en_su_attempt_rec.enrolled_dt,
410 X_CI_START_DT => igs_en_su_attempt_rec.ci_start_dt,
411 X_CI_END_DT => igs_en_su_attempt_rec.ci_end_dt,
412 X_UOO_ID => igs_en_su_attempt_rec.uoo_id,
414 X_UNIT_ATTEMPT_STATUS => igs_en_su_attempt_rec.unit_attempt_status,
415 X_ADMINISTRATIVE_UNIT_STATUS => v_administrative_unit_status,
416 X_ADMINISTRATIVE_PRIORITY => igs_en_su_attempt_rec.administrative_PRIORITY,
417 X_DISCONTINUED_DT => v_discontinuation_dt,
418 X_DCNT_REASON_CD => NULL, -- unable to insert value in to this field
419 X_RULE_WAIVED_DT => igs_en_su_attempt_rec.rule_waived_dt,
420 X_RULE_WAIVED_PERSON_ID => igs_en_su_attempt_rec.rule_waived_person_id,
421 X_NO_ASSESSMENT_IND => igs_en_su_attempt_rec.no_assessment_ind,
422 X_SUP_UNIT_CD => igs_en_su_attempt_rec.sup_unit_cd,
423 X_SUP_VERSION_NUMBER => igs_en_su_attempt_rec.SUP_VERSION_NUMBER,
424 X_EXAM_LOCATION_CD => igs_en_su_attempt_rec.exam_location_cd,
425 X_ALTERNATIVE_TITLE => igs_en_su_attempt_rec.alternative_title,
426 X_OVERRIDE_ENROLLED_CP => igs_en_su_attempt_rec.OVERRIDE_ENROLLED_CP,
427 X_OVERRIDE_EFTSU => igs_en_su_attempt_rec.OVERRIDE_EFTSU,
428 X_OVERRIDE_ACHIEVABLE_CP => igs_en_su_attempt_rec.OVERRIDE_ACHIEVABLE_CP,
429 X_OVERRIDE_OUTCOME_DUE_DT => igs_en_su_attempt_rec.OVERRIDE_OUTCOME_DUE_DT,
430 X_OVERRIDE_CREDIT_REASON => igs_en_su_attempt_rec.OVERRIDE_CREDIT_REASON,
431 X_WAITLIST_DT => igs_en_su_attempt_rec.WAITLIST_DT,
432 X_MODE => 'R',
433 X_GS_VERSION_NUMBER => igs_en_su_attempt_rec.GS_VERSION_NUMBER,
434 X_ENR_METHOD_TYPE => igs_en_su_attempt_rec.ENR_METHOD_TYPE,
435 X_FAILED_UNIT_RULE => igs_en_su_attempt_rec.FAILED_UNIT_RULE,
436 X_CART => igs_en_su_attempt_rec.CART,
437 X_RSV_SEAT_EXT_ID => igs_en_su_attempt_rec.RSV_SEAT_EXT_ID,
438 X_ORG_UNIT_CD => igs_en_su_attempt_rec.ORG_UNIT_CD,
439 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
440 X_SESSION_ID => igs_en_su_attempt_rec.SESSION_ID,
441 X_GRADING_SCHEMA_CODE => igs_en_su_attempt_rec.GRADING_SCHEMA_CODE,
442 X_DEG_AUD_DETAIL_ID => igs_en_su_attempt_rec.DEG_AUD_DETAIL_ID,
443 X_SUBTITLE => igs_en_su_attempt_rec.subtitle,
444 X_STUDENT_CAREER_TRANSCRIPT => igs_en_su_attempt_rec.student_career_transcript,
445 X_STUDENT_CAREER_STATISTICS => igs_en_su_attempt_rec.student_career_statistics,
446 X_ATTRIBUTE_CATEGORY => igs_en_su_attempt_rec.attribute_category,
447 X_ATTRIBUTE1 => igs_en_su_attempt_rec.attribute1,
448 X_ATTRIBUTE2 => igs_en_su_attempt_rec.attribute2,
449 X_ATTRIBUTE3 => igs_en_su_attempt_rec.attribute3,
450 X_ATTRIBUTE4 => igs_en_su_attempt_rec.attribute4,
451 X_ATTRIBUTE5 => igs_en_su_attempt_rec.attribute5,
452 X_ATTRIBUTE6 => igs_en_su_attempt_rec.attribute6,
453 X_ATTRIBUTE7 => igs_en_su_attempt_rec.attribute7,
454 X_ATTRIBUTE8 => igs_en_su_attempt_rec.attribute8,
455 X_ATTRIBUTE9 => igs_en_su_attempt_rec.attribute9,
456 X_ATTRIBUTE10 => igs_en_su_attempt_rec.attribute10,
457 X_ATTRIBUTE11 => igs_en_su_attempt_rec.attribute11,
458 X_ATTRIBUTE12 => igs_en_su_attempt_rec.attribute12,
459 X_ATTRIBUTE13 => igs_en_su_attempt_rec.attribute13,
460 X_ATTRIBUTE14 => igs_en_su_attempt_rec.attribute14,
461 X_ATTRIBUTE15 => igs_en_su_attempt_rec.attribute15,
462 X_ATTRIBUTE16 => igs_en_su_attempt_rec.attribute16,
463 X_ATTRIBUTE17 => igs_en_su_attempt_rec.attribute17,
464 X_ATTRIBUTE18 => igs_en_su_attempt_rec.attribute18,
465 X_ATTRIBUTE19 => igs_en_su_attempt_rec.attribute19,
466 X_ATTRIBUTE20 => igs_en_su_attempt_rec.attribute20,
467 X_WAITLIST_MANUAL_IND => igs_en_su_attempt_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
468 X_WLST_PRIORITY_WEIGHT_NUM => igs_en_su_attempt_rec.wlst_priority_weight_num,
469 X_WLST_PREFERENCE_WEIGHT_NUM => igs_en_su_attempt_rec.wlst_preference_weight_num,
470 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
471 X_CORE_INDICATOR_CODE => igs_en_su_attempt_rec.core_indicator_code
472 );
473 IF v_unit_cds IS NULL THEN
474 v_unit_cds := v_suaci_rec.unit_Cd;
475 ELSE
476 v_unit_cds := v_unit_Cds || ',' || v_suaci_rec.unit_Cd;
477 END IF;
481 v_uoo_ids := to_char(IGS_EN_SU_ATTEMPT_rec.uoo_id);
478
479
480 IF v_uoo_ids IS NULL THEN
482 ELSE
483 v_uoo_ids := v_uoo_ids || ',' || to_char(IGS_EN_SU_ATTEMPT_rec.uoo_id);
484 END IF;
485 END LOOP;
486
487 END;
488
489
490 ELSE
491 -- Modified For : Enhancement Bug #1832130
492 -- Modified By : jbegum
493 -- When the function IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD returns 'Y' the existing code use to have
494 -- a call to the delete row of the table IGS_EN_SU_ATTEMPT.This has been replaced with a call to update row
495 -- of the table IGS_EN_SU_ATTEMPT with the unit_attempt_status updated to 'DROPPED'
496
497 DECLARE
498 v_unit_cds VARCHAR2(4000);
499 v_uoo_ids VARCHAR2(4000);
500 CURSOR igs_en_su_attempt_cur IS
501 SELECT igs_en_su_attempt.*
502 FROM igs_en_su_attempt
503 WHERE person_id = p_person_id AND
504 course_cd = p_course_cd AND
505 uoo_id = v_suaci_rec.uoo_id;
506
507 BEGIN
508
509 v_uoo_ids := null;
510 v_unit_cds := null;
511
512 FOR igs_en_su_attempt_rec IN igs_en_su_attempt_cur LOOP
513 -- Call the API to update the student unit attempt. This API is a
514 -- wrapper to the update row of the TBH.
515
516 invoke_drop_workflow(
517 p_uoo_ids => v_uoo_ids,
518 p_unit_cds => v_unit_cds,
519 p_teach_cal_type => v_suaci_rec.cal_type,
520 p_teach_ci_sequence_number => v_suaci_rec.ci_sequence_number,
521 p_person_id => p_person_id,
522 p_course_cd => p_course_cd,
523 p_message_name => v_message_name);
524
525 igs_en_sua_api.update_unit_attempt(
526 X_ROWID => igs_en_su_attempt_rec.row_id,
527 X_PERSON_ID => igs_en_su_attempt_rec.person_id,
528 X_COURSE_CD => igs_en_su_attempt_rec.course_cd,
529 X_UNIT_CD => igs_en_su_attempt_rec.unit_cd,
530 X_CAL_TYPE => igs_en_su_attempt_rec.cal_type,
531 X_CI_SEQUENCE_NUMBER => igs_en_su_attempt_rec.ci_sequence_number,
532 X_VERSION_NUMBER => igs_en_su_attempt_rec.version_number,
533 X_LOCATION_CD => igs_en_su_attempt_rec.location_cd,
534 X_UNIT_CLASS => igs_en_su_attempt_rec.unit_class,
535 X_CI_START_DT => igs_en_su_attempt_rec.ci_start_dt,
536 X_CI_END_DT => igs_en_su_attempt_rec.ci_end_dt,
537 X_UOO_ID => igs_en_su_attempt_rec.uoo_id,
538 X_ENROLLED_DT => igs_en_su_attempt_rec.enrolled_dt,
539 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
540 X_ADMINISTRATIVE_UNIT_STATUS => igs_en_su_attempt_rec.administrative_unit_status,
541 X_ADMINISTRATIVE_PRIORITY => igs_en_su_attempt_rec.administrative_priority,
542 X_DISCONTINUED_DT => nvl(igs_en_su_attempt_rec.discontinued_dt,trunc(sysdate)),
543 X_DCNT_REASON_CD => igs_en_su_attempt_rec.dcnt_reason_cd,
544 X_RULE_WAIVED_DT => igs_en_su_attempt_rec.rule_waived_dt,
545 X_RULE_WAIVED_PERSON_ID => igs_en_su_attempt_rec.rule_waived_person_id,
546 X_NO_ASSESSMENT_IND => igs_en_su_attempt_rec.no_assessment_ind,
547 X_SUP_UNIT_CD => igs_en_su_attempt_rec.sup_unit_cd,
548 X_SUP_VERSION_NUMBER => igs_en_su_attempt_rec.SUP_VERSION_NUMBER,
549 X_EXAM_LOCATION_CD => igs_en_su_attempt_rec.exam_location_cd,
550 X_ALTERNATIVE_TITLE => igs_en_su_attempt_rec.alternative_title,
551 X_OVERRIDE_ENROLLED_CP => igs_en_su_attempt_rec.OVERRIDE_ENROLLED_CP,
552 X_OVERRIDE_EFTSU => igs_en_su_attempt_rec.OVERRIDE_EFTSU,
553 X_OVERRIDE_ACHIEVABLE_CP => igs_en_su_attempt_rec.OVERRIDE_ACHIEVABLE_CP,
554 X_OVERRIDE_OUTCOME_DUE_DT => igs_en_su_attempt_rec.OVERRIDE_OUTCOME_DUE_DT,
555 X_OVERRIDE_CREDIT_REASON => igs_en_su_attempt_rec.OVERRIDE_CREDIT_REASON,
556 X_WAITLIST_DT => igs_en_su_attempt_rec.WAITLIST_DT,
557 X_MODE => 'R',
558 X_GS_VERSION_NUMBER => igs_en_su_attempt_rec.GS_VERSION_NUMBER,
559 X_ENR_METHOD_TYPE => igs_en_su_attempt_rec.ENR_METHOD_TYPE,
560 X_FAILED_UNIT_RULE => igs_en_su_attempt_rec.FAILED_UNIT_RULE,
561 X_CART => igs_en_su_attempt_rec.CART,
562 X_RSV_SEAT_EXT_ID => igs_en_su_attempt_rec.RSV_SEAT_EXT_ID,
563 X_ORG_UNIT_CD => igs_en_su_attempt_rec.ORG_UNIT_CD,
564 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
565 X_SESSION_ID => igs_en_su_attempt_rec.SESSION_ID,
569 X_STUDENT_CAREER_TRANSCRIPT => igs_en_su_attempt_rec.student_career_transcript,
566 X_GRADING_SCHEMA_CODE => igs_en_su_attempt_rec.GRADING_SCHEMA_CODE,
567 X_DEG_AUD_DETAIL_ID => igs_en_su_attempt_rec.DEG_AUD_DETAIL_ID,
568 X_SUBTITLE => igs_en_su_attempt_rec.subtitle,
570 X_STUDENT_CAREER_STATISTICS => igs_en_su_attempt_rec.student_career_statistics,
571 X_ATTRIBUTE_CATEGORY => igs_en_su_attempt_rec.attribute_category,
572 X_ATTRIBUTE1 => igs_en_su_attempt_rec.attribute1,
573 X_ATTRIBUTE2 => igs_en_su_attempt_rec.attribute2,
574 X_ATTRIBUTE3 => igs_en_su_attempt_rec.attribute3,
575 X_ATTRIBUTE4 => igs_en_su_attempt_rec.attribute4,
576 X_ATTRIBUTE5 => igs_en_su_attempt_rec.attribute5,
577 X_ATTRIBUTE6 => igs_en_su_attempt_rec.attribute6,
578 X_ATTRIBUTE7 => igs_en_su_attempt_rec.attribute7,
579 X_ATTRIBUTE8 => igs_en_su_attempt_rec.attribute8,
580 X_ATTRIBUTE9 => igs_en_su_attempt_rec.attribute9,
581 X_ATTRIBUTE10 => igs_en_su_attempt_rec.attribute10,
582 X_ATTRIBUTE11 => igs_en_su_attempt_rec.attribute11,
583 X_ATTRIBUTE12 => igs_en_su_attempt_rec.attribute12,
584 X_ATTRIBUTE13 => igs_en_su_attempt_rec.attribute13,
585 X_ATTRIBUTE14 => igs_en_su_attempt_rec.attribute14,
586 X_ATTRIBUTE15 => igs_en_su_attempt_rec.attribute15,
587 X_ATTRIBUTE16 => igs_en_su_attempt_rec.attribute16,
588 X_ATTRIBUTE17 => igs_en_su_attempt_rec.attribute17,
589 X_ATTRIBUTE18 => igs_en_su_attempt_rec.attribute18,
590 X_ATTRIBUTE19 => igs_en_su_attempt_rec.attribute19,
591 X_ATTRIBUTE20 => igs_en_su_attempt_rec.attribute20,
592 X_WAITLIST_MANUAL_IND => igs_en_su_attempt_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
593 X_WLST_PRIORITY_WEIGHT_NUM => igs_en_su_attempt_rec.wlst_priority_weight_num,
594 X_WLST_PREFERENCE_WEIGHT_NUM => igs_en_su_attempt_rec.wlst_preference_weight_num,
595 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
596 X_CORE_INDICATOR_CODE => igs_en_su_attempt_rec.core_indicator_code);
597
598 IF v_unit_cds IS NULL THEN
599 v_unit_cds := v_suaci_rec.unit_Cd;
600 ELSE
601 v_unit_cds := v_unit_Cds || ',' || v_suaci_rec.unit_Cd;
602 END IF;
603
604
605 IF v_uoo_ids IS NULL THEN
606 v_uoo_ids := to_char(IGS_EN_SU_ATTEMPT_rec.uoo_id);
607 ELSE
608 v_uoo_ids := v_uoo_ids || ',' || to_char(IGS_EN_SU_ATTEMPT_rec.uoo_id);
609 END IF;
610
611
612 END LOOP;
613 END;
614
615 END IF;/* For the IGS_EN_GEN_008.ENRP_GET_UA_DEL_ALWD IF condition */
616
617 END LOOP;
618
619 END IF; /* End If for IF (v_do_remove = TRUE) */
620
621 END LOOP;
622
623
624 IF v_after_cutoff_warning = TRUE THEN
625 p_message_name := 'IGS_EN_MORE_UA_BEEN_ROLLED';
626 END IF;
627 RETURN TRUE;
628
629 EXCEPTION
630
631 WHEN NO_S_GEN_CAL_CONF_REC_FOUND THEN
632 Fnd_Message.Set_name('FND','FORM_RECORD_DELETED');
633 IGS_GE_MSG_STACK.ADD;
634 App_Exception.Raise_Exception;
635
636 WHEN e_vrn_cutoff_dt_alias_notfound THEN
637 Fnd_Message.Set_name('FND','FORM_RECORD_DELETED');
638 IGS_GE_MSG_STACK.ADD;
639 App_Exception.Raise_Exception;
640
641 WHEN OTHERS THEN
642 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
643 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_013.enrp_upd_sci_ua');
644 IGS_GE_MSG_STACK.ADD;
645 App_Exception.Raise_Exception;
646 END;
647 END enrp_upd_sci_ua;
648
649 Function Enrp_Upd_Susa_End_Dt(
650 p_person_id IN NUMBER ,
651 p_course_cd IN VARCHAR2 ,
652 p_unit_set_cd IN VARCHAR2 ,
653 p_sequence_number IN NUMBER ,
654 p_end_dt IN DATE ,
655 p_voluntary_end_ind IN VARCHAR2 := 'N',
656 p_authorised_person_id IN NUMBER ,
657 p_authorised_on IN DATE ,
658 p_message_name OUT NOCOPY VARCHAR2)
659 RETURN BOOLEAN AS
660
661 resource_busy_exception EXCEPTION;
662 PRAGMA EXCEPTION_INIT(resource_busy_exception, -54);
663 BEGIN -- enrp_upd_susa_end_dt
664 -- This module is called when a IGS_AS_SU_SETATMPT is ended (end_dt set).
665 -- This module will check if the IGS_PS_UNIT set has any child IGS_PS_UNIT sets and attempt
666 -- to set the end_dt and associated authorisation details for all children.
667 DECLARE
668 v_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE;
669 v_us_version_number IGS_AS_SU_SETATMPT.us_version_number%TYPE;
670 v_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE;
671 v_authorised_person_id IGS_AS_SU_SETATMPT.authorised_person_id%TYPE;
672 v_authorised_on IGS_AS_SU_SETATMPT.authorised_on%TYPE;
676 v_parent_unit_set_cd IGS_AS_SU_SETATMPT.parent_unit_set_cd%TYPE;
673 v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
674 v_end_dt IGS_AS_SU_SETATMPT.end_dt%TYPE;
675 v_rqrmnts_complete_dt IGS_AS_SU_SETATMPT.rqrmnts_complete_dt%TYPE;
677 v_parent_sequence_number IGS_AS_SU_SETATMPT.parent_sequence_number%TYPE;
678 v_student_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
679 v_primary_set_ind IGS_AS_SU_SETATMPT.primary_set_ind%TYPE;
680 v_voluntary_end_ind IGS_AS_SU_SETATMPT.voluntary_end_ind%TYPE;
681 v_override_title IGS_AS_SU_SETATMPT.override_title%TYPE;
682 v_rqrmnts_complete_ind IGS_AS_SU_SETATMPT.rqrmnts_complete_ind%TYPE;
683 v_s_completed_source_type IGS_AS_SU_SETATMPT.s_completed_source_type%TYPE;
684 v_catalog_cal_type IGS_AS_SU_SETATMPT.catalog_cal_type%TYPE;
685 v_catalog_seq_num IGS_AS_SU_SETATMPT.catalog_seq_num%TYPE;
686 --kkillams attribute_category to attribute20 variables are added w.r.t to YOP-EN bug id :2156956
687 v_attribute_category IGS_AS_SU_SETATMPT.attribute_category%TYPE;
688 v_attribute1 IGS_AS_SU_SETATMPT.attribute1%TYPE;
689 v_attribute2 IGS_AS_SU_SETATMPT.attribute2%TYPE;
690 v_attribute3 IGS_AS_SU_SETATMPT.attribute3%TYPE;
691 v_attribute4 IGS_AS_SU_SETATMPT.attribute4%TYPE;
692 v_attribute5 IGS_AS_SU_SETATMPT.attribute5%TYPE;
693 v_attribute6 IGS_AS_SU_SETATMPT.attribute6%TYPE;
694 v_attribute7 IGS_AS_SU_SETATMPT.attribute7%TYPE;
695 v_attribute8 IGS_AS_SU_SETATMPT.attribute8%TYPE;
696 v_attribute9 IGS_AS_SU_SETATMPT.attribute9%TYPE;
697 v_attribute10 IGS_AS_SU_SETATMPT.attribute10%TYPE;
698 v_attribute11 IGS_AS_SU_SETATMPT.attribute11%TYPE;
699 v_attribute12 IGS_AS_SU_SETATMPT.attribute12%TYPE;
700 v_attribute13 IGS_AS_SU_SETATMPT.attribute13%TYPE;
701 v_attribute14 IGS_AS_SU_SETATMPT.attribute14%TYPE;
702 v_attribute15 IGS_AS_SU_SETATMPT.attribute15%TYPE;
703 v_attribute16 IGS_AS_SU_SETATMPT.attribute16%TYPE;
704 v_attribute17 IGS_AS_SU_SETATMPT.attribute17%TYPE;
705 v_attribute18 IGS_AS_SU_SETATMPT.attribute18%TYPE;
706 v_attribute19 IGS_AS_SU_SETATMPT.attribute19%TYPE;
707 v_attribute20 IGS_AS_SU_SETATMPT.attribute20%TYPE;
708 v_message_name VARCHAR2(30);
709 v_message_text VARCHAR2(2000);
710 v_rowid ROWID;
711
712 CURSOR c_susa IS
713 SELECT susa.ROWID,
714 susa.unit_set_cd,
715 susa.us_version_number,
716 susa.sequence_number,
717 susa.authorised_person_id,
718 susa.authorised_on,
719 susa.selection_dt,
720 susa.end_dt,
721 susa.rqrmnts_complete_dt,
722 susa.parent_unit_set_cd,
723 susa.parent_sequence_number,
724 susa.student_confirmed_ind,
725 susa.primary_set_ind,
726 susa.voluntary_end_ind,
727 susa.override_title,
728 susa.rqrmnts_complete_ind,
729 susa.s_completed_source_type,
730 susa.catalog_cal_type,
731 susa.catalog_seq_num,
732 susa.attribute_category, --kkillams columns attribute_category to attribute20 added to cursor w.r.t to YOP-EN bug id :2156956
733 susa.attribute1,
734 susa.attribute2,
735 susa.attribute3,
736 susa.attribute4,
737 susa.attribute5,
738 susa.attribute6,
739 susa.attribute7,
740 susa.attribute8,
741 susa.attribute9,
742 susa.attribute10,
743 susa.attribute11,
744 susa.attribute12,
745 susa.attribute13,
746 susa.attribute14,
747 susa.attribute15,
748 susa.attribute16,
749 susa.attribute17,
750 susa.attribute18,
751 susa.attribute19,
752 susa.attribute20
753 FROM IGS_AS_SU_SETATMPT susa
754 START WITH susa.person_id = p_person_id AND
755 susa.course_cd = p_course_cd AND
756 susa.parent_unit_set_cd = p_unit_set_cd AND
757 susa.parent_sequence_number = p_sequence_number
758 CONNECT BY
759 PRIOR susa.person_id = susa.person_id AND
760 PRIOR susa.course_cd = susa.course_cd AND
761 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
762 PRIOR susa.sequence_number = susa.parent_sequence_number
763 FOR UPDATE OF end_dt,
764 voluntary_end_ind,
765 authorised_person_id,
766 authorised_on NOWAIT;
767 L_ROWID VARCHAR2(25);
768 BEGIN
769 -- Set the default message number
770 p_message_name := null;
771
772 OPEN c_susa;
773 FETCH c_susa INTO v_rowid,
774 v_unit_set_cd,
775 v_us_version_number,
776 v_sequence_number,
777 v_authorised_person_id,
778 v_authorised_on,
779 v_selection_dt,
780 v_end_dt,
781 v_rqrmnts_complete_dt,
782 v_parent_unit_set_cd,
783 v_parent_sequence_number,
784 v_student_confirmed_ind,
788 v_rqrmnts_complete_ind,
785 v_primary_set_ind,
786 v_voluntary_end_ind,
787 v_override_title,
789 v_s_completed_source_type,
790 v_catalog_cal_type,
791 v_catalog_seq_num,
792 v_attribute_category, --kkillams variables attribute_category to attribute20 are added w.r.t to YOP-EN bug id :2156956
793 v_attribute1,
794 v_attribute2,
795 v_attribute3,
796 v_attribute4,
797 v_attribute5,
798 v_attribute6,
799 v_attribute7,
800 v_attribute8,
801 v_attribute9,
802 v_attribute10,
803 v_attribute11,
804 v_attribute12,
805 v_attribute13,
806 v_attribute14,
807 v_attribute15,
808 v_attribute16,
809 v_attribute17,
810 v_attribute18,
811 v_attribute19,
812 v_attribute20;
813
814 LOOP
815 EXIT WHEN (c_susa%NOTFOUND);
816 -- For each descendant record found, validate and then end the IGS_PS_UNIT set.
817 IF (v_end_dt IS NULL AND
818 v_rqrmnts_complete_dt IS NULL) THEN
819 -- Determine if authorised IGS_PE_PERSON id required to be updated (That is, if
820 -- authorise parameter fields null then do not alter fields.)
821 IF (p_authorised_person_id IS NOT NULL OR
822 p_authorised_on IS NOT NULL) THEN
823 v_authorised_person_id := p_authorised_person_id;
824 v_authorised_on := p_authorised_on;
825 END IF;
826 -- Validate that able to update the record.
827 IF IGS_EN_VAL_SUSA.enrp_val_susa(
828 p_person_id,
829 p_course_cd,
830 v_unit_set_cd,
831 v_sequence_number,
832 v_us_version_number,
833 v_selection_dt,
834 v_student_confirmed_ind,
835 p_end_dt,
836 v_parent_unit_set_cd,
837 v_parent_sequence_number,
838 v_primary_set_ind,
839 p_voluntary_end_ind,
840 v_authorised_person_id,
841 v_authorised_on,
842 v_override_title,
843 v_rqrmnts_complete_ind,
844 v_rqrmnts_complete_dt,
845 v_s_completed_source_type,
846 'UPDATE',
847 v_message_name,
848 v_message_text) = FALSE THEN
849 -- IGS_GE_NOTE: The IGS_RU_RULE check will only be called when confirming the IGS_PS_UNIT set.
850 -- Hence no need to check and handle if v_message_text returned.
851 CLOSE c_susa;
852 p_message_name := v_message_name;
853 RETURN FALSE;
854 END IF;
855 -- Disable triggers for the session as this is a cascading update and as
856 -- such creates problems accessing the PL/SQL table when called in the
857 -- after statement trigger.
858
859 -- Inserts a record into the s_disable_table_trigger
860 -- database table.
861 IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
862 X_ROWID => L_ROWID ,
863 X_TABLE_NAME =>'IGS_AS_SU_SETATMPT',
864 X_SESSION_ID => userenv('SESSIONID'),
865 x_mode => 'R'
866 );
867
868 -- If valid, then end the IGS_PS_UNIT set attempt.
869
870 -- Added CATALOG_CAL_TYPE and CATALOG_SEQ_NUM parameters
871 -- to call IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW as per the
872 -- Career impact Build Bug# 2027984.
873
874 IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW(
875 X_ROWID => V_ROWID,
876 X_PERSON_ID => P_PERSON_ID,
877 X_COURSE_CD => P_COURSE_CD,
878 X_UNIT_SET_CD => V_UNIT_SET_CD,
879 X_SEQUENCE_NUMBER => V_SEQUENCE_NUMBER,
880 X_US_VERSION_NUMBER => V_US_VERSION_NUMBER,
881 X_SELECTION_DT => V_SELECTION_DT,
882 X_STUDENT_CONFIRMED_IND => V_STUDENT_CONFIRMED_IND,
883 X_END_DT => P_END_DT,
884 X_PARENT_UNIT_SET_CD => V_PARENT_UNIT_SET_CD,
885 X_PARENT_SEQUENCE_NUMBER => V_PARENT_SEQUENCE_NUMBER,
886 X_PRIMARY_SET_IND => V_PRIMARY_SET_IND,
887 X_VOLUNTARY_END_IND => P_VOLUNTARY_END_IND,
888 X_AUTHORISED_PERSON_ID => V_AUTHORISED_PERSON_ID,
889 X_AUTHORISED_ON => V_AUTHORISED_ON,
890 X_OVERRIDE_TITLE => V_OVERRIDE_TITLE,
891 X_RQRMNTS_COMPLETE_IND => V_RQRMNTS_COMPLETE_IND,
892 X_RQRMNTS_COMPLETE_DT => V_RQRMNTS_COMPLETE_DT,
893 X_S_COMPLETED_SOURCE_TYPE => V_S_COMPLETED_SOURCE_TYPE,
894 X_CATALOG_CAL_TYPE => V_CATALOG_CAL_TYPE,
898 X_ATTRIBUTE2 => V_ATTRIBUTE2,
895 X_CATALOG_SEQ_NUM => V_CATALOG_SEQ_NUM,
896 X_ATTRIBUTE_CATEGORY => V_ATTRIBUTE_CATEGORY, --kkillams attribute_category to attribute20 parameters are added to
897 X_ATTRIBUTE1 => V_ATTRIBUTE1, --IGS_AS_SU_SETATMPT_PKG.update_row call w.r.t to YOP-EN bug id :2156956
899 X_ATTRIBUTE3 => V_ATTRIBUTE3,
900 X_ATTRIBUTE4 => V_ATTRIBUTE4,
901 X_ATTRIBUTE5 => V_ATTRIBUTE5,
902 X_ATTRIBUTE6 => V_ATTRIBUTE6,
903 X_ATTRIBUTE7 => V_ATTRIBUTE7,
904 X_ATTRIBUTE8 => V_ATTRIBUTE8,
905 X_ATTRIBUTE9 => V_ATTRIBUTE9,
906 X_ATTRIBUTE10 => V_ATTRIBUTE10,
907 X_ATTRIBUTE11 => V_ATTRIBUTE11,
908 X_ATTRIBUTE12 => V_ATTRIBUTE12,
909 X_ATTRIBUTE13 => V_ATTRIBUTE13,
910 X_ATTRIBUTE14 => V_ATTRIBUTE14,
911 X_ATTRIBUTE15 => V_ATTRIBUTE15,
912 X_ATTRIBUTE16 => V_ATTRIBUTE16,
913 X_ATTRIBUTE17 => V_ATTRIBUTE17,
914 X_ATTRIBUTE18 => V_ATTRIBUTE18,
915 X_ATTRIBUTE19 => V_ATTRIBUTE19,
916 X_ATTRIBUTE20 => V_ATTRIBUTE20,
917 X_MODE => 'R');
918
919
920
921 -- Re-enable triggers for the session.
922 IGS_GE_MNT_SDTT.genp_del_sdtt('IGS_AS_SU_SETATMPT');
923 END IF;
924 FETCH c_susa INTO v_rowid,
925 v_unit_set_cd,
926 v_us_version_number,
927 v_sequence_number,
928 v_authorised_person_id,
929 v_authorised_on,
930 v_selection_dt,
931 v_end_dt,
932 v_rqrmnts_complete_dt,
933 v_parent_unit_set_cd,
934 v_parent_sequence_number,
935 v_student_confirmed_ind,
936 v_primary_set_ind,
937 v_voluntary_end_ind,
938 v_override_title,
939 v_rqrmnts_complete_ind,
940 v_s_completed_source_type,
941 v_catalog_cal_type,
942 v_catalog_seq_num,
943 v_attribute_category, --kkillams variables attribute_category to attribute20 are added w.r.t to YOP-EN bug id :2156956
944 v_attribute1,
945 v_attribute2,
946 v_attribute3,
947 v_attribute4,
948 v_attribute5,
949 v_attribute6,
950 v_attribute7,
951 v_attribute8,
952 v_attribute9,
953 v_attribute10,
954 v_attribute11,
955 v_attribute12,
956 v_attribute13,
957 v_attribute14,
958 v_attribute15,
959 v_attribute16,
960 v_attribute17,
961 v_attribute18,
962 v_attribute19,
963 v_attribute20;
964 END LOOP;
965 CLOSE c_susa;
966 -- If processing successful then
967 RETURN TRUE;
968 EXCEPTION
969 -- If an exception is raised indicating a lock on the current record,
970 -- return false and an error.
971 WHEN resource_busy_exception THEN
972 IF c_susa%ISOPEN THEN
973 CLOSE c_susa;
974 END IF;
975 p_message_name := 'IGS_EN_DESCENDANT_UNIT_SET';
976 RETURN FALSE;
977 WHEN OTHERS THEN
978 IF c_susa%ISOPEN THEN
979 CLOSE c_susa;
980 END IF;
981 RAISE;
982 END;
983 EXCEPTION
984 WHEN OTHERS THEN
985 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
986 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_013.enrp_upd_susa_end_dt');
987 IGS_GE_MSG_STACK.ADD;
988 App_Exception.Raise_Exception;
989 END enrp_upd_susa_end_dt;
990
991 Function Enrp_Upd_Susa_Sci(
992 p_person_id IN NUMBER ,
993 p_course_cd IN VARCHAR2 ,
994 p_unit_set_cd IN VARCHAR2 ,
995 p_sequence_number IN NUMBER ,
996 p_student_confirmed_ind IN VARCHAR2 := 'N',
997 p_message_name OUT NOCOPY VARCHAR2)
998 RETURN BOOLEAN AS
999
1000 resource_busy_exception EXCEPTION;
1001 PRAGMA EXCEPTION_INIT(resource_busy_exception, -54);
1002 BEGIN -- enrp_upd_susa_sci
1003 -- This module is called when the student_confimed_ind is unset.
1004 -- This module will check if the IGS_PS_UNIT set has any child IGS_PS_UNIT sets and
1008 v_susa_rec_found BOOLEAN := FALSE;
1005 -- attempt to unset the the student_confirmed_ind for all children.
1006 DECLARE
1007 v_enrp_val_susa_sci BOOLEAN := TRUE;
1009 v_message_name VARCHAR2(30);
1010 v_message_text VARCHAR2 (2000);
1011 v_rowid ROWID;
1012 CURSOR c_susa IS
1013 SELECT susa.ROWID,
1014 susa.unit_set_cd,
1015 susa.us_version_number,
1016 susa.sequence_number,
1017 susa.authorised_person_id,
1018 susa.authorised_on,
1019 susa.selection_dt,
1020 susa.end_dt,
1021 susa.rqrmnts_complete_dt,
1022 susa.parent_unit_set_cd,
1023 susa.parent_sequence_number,
1024 susa.student_confirmed_ind,
1025 susa.primary_set_ind,
1026 susa.voluntary_end_ind,
1027 susa.override_title,
1028 susa.rqrmnts_complete_ind,
1029 susa.s_completed_source_type,
1030 susa.catalog_cal_type,
1031 susa.catalog_seq_num,
1032 susa.attribute_category, --kkillams columns attribute_category to attribute20 added to cursor w.r.t to YOP-EN bug id :2156956
1033 susa.attribute1,
1034 susa.attribute2,
1035 susa.attribute3,
1036 susa.attribute4,
1037 susa.attribute5,
1038 susa.attribute6,
1039 susa.attribute7,
1040 susa.attribute8,
1041 susa.attribute9,
1042 susa.attribute10,
1043 susa.attribute11,
1044 susa.attribute12,
1045 susa.attribute13,
1046 susa.attribute14,
1047 susa.attribute15,
1048 susa.attribute16,
1049 susa.attribute17,
1050 susa.attribute18,
1051 susa.attribute19,
1052 susa.attribute20
1053 FROM IGS_AS_SU_SETATMPT susa
1054 START WITH susa.person_id = p_person_id AND
1055 susa.course_cd = p_course_cd AND
1056 susa.parent_unit_set_cd = p_unit_set_cd AND
1057 susa.parent_sequence_number = p_sequence_number
1058 CONNECT BY
1059 PRIOR susa.person_id = susa.person_id AND
1060 PRIOR susa.course_cd = susa.course_cd AND
1061 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
1062 PRIOR susa.sequence_number = susa.parent_sequence_number
1063 FOR UPDATE OF student_confirmed_ind,
1064 selection_dt NOWAIT;
1065 L_ROWID VARCHAR2(25);
1066 BEGIN
1067 -- Set the default message number
1068 p_message_name := null;
1069 -- If student confirmed indicator is NULL or 'Y' then
1070 -- not concerned with updating children
1071 IF p_student_confirmed_ind = 'Y' OR
1072 p_student_confirmed_ind IS NULL THEN
1073 p_message_name := null;
1074 RETURN TRUE;
1075 END IF;
1076 -- Process all descendants of the IGS_PS_UNIT set and attempt
1077 -- to unset the student confirmed indicator.
1078 -- For each descendant record found, unset the student confirmed indicator.
1079 FOR v_susa_rec IN c_susa LOOP
1080 v_susa_rec_found := TRUE;
1081 IF v_susa_rec.student_confirmed_ind = 'Y' THEN
1082 -- Validate that able to update the record.
1083 IF IGS_EN_VAL_SUSA.enrp_val_susa(
1084 p_person_id,
1085 p_course_cd,
1086 v_susa_rec.unit_set_cd,
1087 v_susa_rec.sequence_number,
1088 v_susa_rec.us_version_number,
1089 NULL, -- selection_dt
1090 'N', -- student_confirmed_ind
1091 v_susa_rec.end_dt,
1092 v_susa_rec.parent_unit_set_cd,
1093 v_susa_rec.parent_sequence_number,
1094 v_susa_rec.primary_set_ind,
1095 v_susa_rec.voluntary_end_ind,
1096 v_susa_rec.authorised_person_id,
1097 v_susa_rec.authorised_on,
1098 v_susa_rec.override_title,
1099 v_susa_rec.rqrmnts_complete_ind,
1100 v_susa_rec.rqrmnts_complete_dt,
1101 v_susa_rec.s_completed_source_type,
1102 'UPDATE',
1103 v_message_name,
1104 v_message_text) = FALSE THEN
1105 -- IGS_GE_NOTE: The IGS_RU_RULE check will only be called when confirming the IGS_PS_UNIT set.
1106 -- Hence no need to check and handle if v_message_text returned.
1107 p_message_name := v_message_name;
1108 v_enrp_val_susa_sci := FALSE;
1109 EXIT;
1110 END IF;
1111 -- Disable triggers for the session as this is a cascading update and as
1112 -- such creates problems accessing the PL/SQL table when called in the
1113 -- after statement trigger.
1114
1115 -- Inserts a record into the s_disable_table_trigger
1116 -- database table.
1117 IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
1118 X_ROWID => L_ROWID ,
1119 X_TABLE_NAME =>'IGS_AS_SU_SETATMPT',
1120 X_SESSION_ID => userenv('SESSIONID'),
1121 x_mode => 'R'
1122 );
1123
1124 -- If valid, then unset the student confirmed indicator.
1125
1126 -- Added CATALOG_CAL_TYPE and CATALOG_SEQ_NUM parameters
1130 IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW(X_ROWID => v_susa_rec.rowid,
1127 -- to call IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW as per the
1128 -- Career impact Build Bug# 2027984.
1129
1131 X_PERSON_ID => P_PERSON_ID,
1132 X_COURSE_CD => P_COURSE_CD,
1133 X_UNIT_SET_CD => v_susa_rec.unit_set_cd,
1134 X_SEQUENCE_NUMBER => v_susa_rec.sequence_number,
1135 X_US_VERSION_NUMBER => v_susa_rec.us_version_number,
1136 X_SELECTION_DT => NULL,
1137 X_STUDENT_CONFIRMED_IND => 'N',
1138 X_END_DT => v_susa_rec.end_dt,
1139 X_PARENT_UNIT_SET_CD => v_susa_rec.parent_unit_set_cd,
1140 X_PARENT_SEQUENCE_NUMBER => v_susa_rec.parent_sequence_number,
1141 X_PRIMARY_SET_IND => v_susa_rec.primary_set_ind,
1142 X_VOLUNTARY_END_IND => v_susa_rec.voluntary_end_ind,
1143 X_AUTHORISED_PERSON_ID => v_susa_rec.authorised_person_id,
1144 X_AUTHORISED_ON => v_susa_rec.authorised_on,
1145 X_OVERRIDE_TITLE => v_susa_rec.override_title,
1146 X_RQRMNTS_COMPLETE_IND => v_susa_rec.rqrmnts_complete_ind,
1147 X_RQRMNTS_COMPLETE_DT => v_susa_rec.rqrmnts_complete_dt,
1148 X_S_COMPLETED_SOURCE_TYPE => v_susa_rec.s_completed_source_type,
1149 X_CATALOG_CAL_TYPE => v_susa_rec.catalog_cal_type,
1150 X_CATALOG_SEQ_NUM => v_susa_rec.catalog_seq_num,
1151 X_ATTRIBUTE_CATEGORY => v_susa_rec.attribute_category, --kkillams attribute_category to attribute20 parameters are added to
1152 X_ATTRIBUTE1 => v_susa_rec.attribute1, --IGS_AS_SU_SETATMPT_PKG.update_row call w.r.t to YOP-EN bug id :2156956
1153 X_ATTRIBUTE2 => v_susa_rec.attribute2,
1154 X_ATTRIBUTE3 => v_susa_rec.attribute3,
1155 X_ATTRIBUTE4 => v_susa_rec.attribute4,
1156 X_ATTRIBUTE5 => v_susa_rec.attribute5,
1157 X_ATTRIBUTE6 => v_susa_rec.attribute6,
1158 X_ATTRIBUTE7 => v_susa_rec.attribute7,
1159 X_ATTRIBUTE8 => v_susa_rec.attribute8,
1160 X_ATTRIBUTE9 => v_susa_rec.attribute9,
1161 X_ATTRIBUTE10 => v_susa_rec.attribute10,
1162 X_ATTRIBUTE11 => v_susa_rec.attribute11,
1163 X_ATTRIBUTE12 => v_susa_rec.attribute12,
1164 X_ATTRIBUTE13 => v_susa_rec.attribute13,
1165 X_ATTRIBUTE14 => v_susa_rec.attribute14,
1166 X_ATTRIBUTE15 => v_susa_rec.attribute15,
1167 X_ATTRIBUTE16 => v_susa_rec.attribute16,
1168 X_ATTRIBUTE17 => v_susa_rec.attribute17,
1169 X_ATTRIBUTE18 => v_susa_rec.attribute18,
1170 X_ATTRIBUTE19 => v_susa_rec.attribute19,
1171 X_ATTRIBUTE20 => v_susa_rec.attribute20,
1172 X_MODE => 'R'
1173 );
1174
1175
1176 -- Re-enable triggers for the session.
1177 IGS_GE_MNT_SDTT.genp_del_sdtt('IGS_AS_SU_SETATMPT');
1178 END IF;
1179 END LOOP;
1180 IF (v_susa_rec_found = TRUE AND
1181 v_enrp_val_susa_sci = FALSE) THEN
1182 RETURN FALSE;
1183 END IF;
1184 RETURN TRUE;
1185 EXCEPTION
1186 -- If an exception raise indicating a lock on the current record,
1187 -- return false and errror message. Declare an exception
1188 WHEN resource_busy_exception THEN
1189 IF c_susa%ISOPEN THEN
1190 CLOSE c_susa;
1191 END IF;
1192 p_message_name := 'IGS_EN_DESCENDANT_UNITSET_LOC';
1193 RETURN FALSE;
1194 WHEN OTHERS THEN
1195 RAISE;
1196 END;
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1200 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_013.enrp_upd_susa_sci');
1201 IGS_GE_MSG_STACK.ADD;
1202 App_Exception.Raise_Exception;
1203 END enrp_upd_susa_sci;
1204
1205 END IGS_EN_GEN_013;
[Home] [Help]