1 PACKAGE BODY IGS_EN_GEN_004 AS
2 /* $Header: IGSEN04B.pls 120.2 2006/08/09 06:56:55 amuthu noship $ */
3
4 --Changes History:
5 -- knaraset 14-May-2003 Modified call to call_fee_ass to add uoo_id, as part of MUS build bug 2829262
6 -- prraj 10-Jan-2003 Replaced reference to view IGS_EN_NSTD_USEC_DL_V
7 -- with base table IGS_EN_NSTD_USEC_DL Bug# 2750716
8 -- prraj 06-Jan-2003 Changed message for record cutoff in function Enrp_Get_Rec_Window
9 -- as part of Bug# 2730025
10 -- pradhakr 16-Dec-2002 Changed the call to the update_row of igs_en_su_attempt
11 -- table to igs_en_sua_api.update_unit_attempt.
12 -- Changes wrt ENCR031 build. Bug#2643207
13 --ayedubat 02-JUL-2002 Changed the procedure, Enrp_Dropall_Unit for the bug Fix:2423605
14 --ayedubat 30-MAY-2002 Changed the function: Enrp_Get_Rec_Window for the bug fix:2337161
15 --amuthu 10-May-2002 Commented the exception section in
16 -- Enrp_Dropall_Unit, since the errors
17 -- were not getting propagated to the SS screen
18
19 --Updated by Sudhir.
20 --Update Date: 28-Feb-2002.
21 --Added a new parameter p_admin_unit_sta to the procedure enrp_dropall_unit and the logic for processing it.
22
23 --Added refernces to column ORG_UNIT_CD incall to IGS_EN_SU_ATTEMPT TBH call as a part of bug 1964697
24 --Aiyer 10-Oct-2001 Added the column grading schema code in all Tbh calls of IGS_EN_SU_ATTEMPT_PKG as a part of the bug 2037897.
25 --svenkata 20-Dec-2001 Added the columns student_career_transcript,student_career_statistics in all Tbh calls of
26 -- IGS_EN_SU_ATTEMPT_PKG as a part of the bug # 2158626
27 --vvutukur 7-Jan-2002 Added primary_program_type,key_program as part of cursor c_sca_hist, for SFCR007 Build, bug 2162747
28 --svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added to table handler procedure calls as part of
29 -- CCR - ENCR022.
30 --Nishikant 30-jan-2002 Added the column session_id in the Tbh calls of IGS_EN_SU_ATTEMPT_PKG
31 -- as a part of the bug 2172380.
32 --Nishikant 15-may-2002 Condition in an IF clause in the function Enrp_Dropall_Unit modified as part of the bug#2364216.
33 --sudhir 23-MAY-2002 Raise exception if multiple admin unit status found.
34 --amuthu 26-DEC-2002 when dropping a wailtisted unit added code to create a TODO rec
35 --svanukur 26-jun-2003 Passing discontinued date with a nvl substitution of sysdate in the call to the update_row api of
36 -- ig_en_su_attmept in case of a "dropped" unit attempt status as part of bug 2898213.
37 --rvivekan 3-SEP-2003 Waitlist Enhacements build # 3052426. 2 new columns added to
38 -- IGS_EN_SU_ATTEMPT_PKG procedures and consequently to IGS_EN_SUA_API procedures
39 --rvangala 07-OCT-03 Passing core_indicator_code to IGS_EN_SUA-API.UPDATE_UNIT_ATTEMPT added as part of Prevent Dropping
40 -- Core Units. Enh Bug# 3052432
41 -- gmaheswa 13-Nov-2003 Bug 3227107 address changes . stubbed Enrp_get_pa_gap.
42 -- amuthu 14-JUL-2004 Allowing the drop of duplicate unit attempt as
43 -- part of IGS.M bug 3765628/ IGS.L.#R bug 3703889
44 -- Modified the Cursor in enrp_dropall_unit to allow
45 -- the dropping of duplicate unit attempts and add a
46 -- call to delete row for duplicate unit attempts.
47 -- gmaheswa 25-Jan-05 Bug 3882788 - Added START_DT <> END_DT OR END_DT IS NULL condition inorder to ignore deleted person identifiers.
48 -- amuthu 09-Aug-2006 Modififed Enrp_Dropall_Unit.
49 -------------------------------------------------------------------------------------------------------------------------------------------
50
51 FUNCTION Enrp_Get_Pa_Gap(
52 p_person_id IN NUMBER ,
53 p_start_dt IN DATE ,
54 p_end_dt IN DATE )
55 RETURN VARCHAR2 AS
56 GV_OTHER_DETAIL VARCHAR2(250);
57
58 BEGIN
59 RETURN NULL;
60 END enrp_get_pa_gap;
61
62 FUNCTION Enrp_Get_Pei_Dt(p_person_id IN NUMBER )
63 RETURN DATE AS
64
65 BEGIN -- enrp_get_pei_dt
66 -- This module finds the date of the latest image
67 -- for a IGS_PE_PERSON from the IGS_PE_PERSON image table.
68 DECLARE
69 v_image_dt IGS_PE_PERSON_IMAGE.image_dt%TYPE;
70 CURSOR c_pei IS
71 SELECT pei.image_dt
72 FROM IGS_PE_PERSON_IMAGE pei
73 WHERE pei.person_id = p_person_id AND
74 pei.PERSON_IMAGE IS NOT NULL
75 ORDER BY pei.image_dt DESC;
76 BEGIN
77 OPEN c_pei;
78 FETCH c_pei INTO v_image_dt;
79 IF (c_pei%NOTFOUND) THEN
80 CLOSE c_pei;
81 RETURN NULL;
82 END IF;
83 CLOSE c_pei;
84 RETURN v_image_dt;
85 EXCEPTION
86 WHEN OTHERS THEN
87 IF (c_pei%ISOPEN) THEN
88 CLOSE c_pei;
89 END IF;
90 RAISE;
91 END;
92 EXCEPTION
93 WHEN OTHERS THEN
94 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
95 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.enrp_get_pei_dt');
96 IGS_GE_MSG_STACK.ADD;
97 APP_EXCEPTION.RAISE_EXCEPTION;
98 END enrp_get_pei_dt;
99
100
101 PROCEDURE Enrp_Get_Pe_Exists(
102 p_person_id IN NUMBER ,
103 p_effective_dt IN DATE ,
104 p_check_alternate IN BOOLEAN ,
105 p_check_address IN BOOLEAN ,
106 p_check_disability IN BOOLEAN ,
107 p_check_visa IN BOOLEAN ,
108 p_check_finance IN BOOLEAN ,
109 p_check_notes IN BOOLEAN ,
110 p_check_statistics IN BOOLEAN ,
111 p_check_alias IN BOOLEAN ,
112 p_alternate_exists OUT NOCOPY BOOLEAN ,
113 p_address_exists OUT NOCOPY BOOLEAN ,
114 p_disability_exists OUT NOCOPY BOOLEAN ,
115 p_visa_exists OUT NOCOPY BOOLEAN ,
116 p_finance_exists OUT NOCOPY BOOLEAN ,
117 p_notes_exists OUT NOCOPY BOOLEAN ,
118 p_statistics_exists OUT NOCOPY BOOLEAN ,
119 p_alias_exists OUT NOCOPY BOOLEAN )
120 AS
121
122 BEGIN -- enrp_get_pe_exists
123 -- return output parameters indicating whether
124 -- or not data exists on IGS_PE_PERSON detail tables
125 -- for the specific IGS_PE_PERSON ID.
126 DECLARE
127 v_record_exists VARCHAR2(1);
128 CURSOR c_api IS
129 SELECT 'x'
130 FROM IGS_PE_ALT_PERS_ID api
131 WHERE pe_person_id = p_person_id AND
132 (api.start_dt IS NULL OR
133 api.start_dt <= p_effective_dt) AND
134 (api.end_dt IS NULL OR
135 api.end_dt >= p_effective_dt) AND
136 (api.start_dt <> api.end_dt OR
137 api.end_dt IS NULL);
138 CURSOR c_pa IS
139 SELECT 'x'
140 FROM IGS_PE_ADDR_V
141 WHERE person_id = p_person_id;
142 CURSOR c_pd IS
143 SELECT 'x'
144 FROM IGS_PE_PERS_DISABLTY
145 WHERE person_id = p_person_id;
146 CURSOR c_iv IS
147 SELECT 'x'
148 FROM IGS_PE_VISA iv
149 WHERE iv.person_id = p_person_id AND
150 (iv.visa_expiry_date IS NULL OR
151 iv.visa_expiry_date > p_effective_dt);
152 CURSOR c_pn IS
153 SELECT 'x'
154 FROM IGS_PE_PERS_NOTE pn
155 WHERE pn.person_id = p_person_id;
156 --modified the cursor for the performance bug 3693713
157 --this cursor is used to check statistics record which is created with person record
158 CURSOR c_ps IS
159 SELECT 'x'
160 FROM HZ_PARTIES ps
161 WHERE ps.party_id = p_person_id ;
162
163 CURSOR c_pal IS
164 SELECT 'x'
165 FROM IGS_PE_PERSON_ALIAS pal
166 WHERE pal.person_id = p_person_id AND
167 (pal.start_dt IS NULL OR
168 pal.start_dt <= p_effective_dt) AND
169 (pal.end_dt IS NULL OR
170 pal.end_dt >= p_effective_dt);
171 BEGIN
172 -- initialise output parameters
173 p_alternate_exists := FALSE;
174 p_address_exists := FALSE;
175 p_disability_exists := FALSE;
176 p_visa_exists := FALSE;
177 p_finance_exists := FALSE;
178 p_notes_exists := FALSE;
179 p_statistics_exists := FALSE;
180 p_alias_exists := FALSE;
181 IF p_check_alternate = TRUE THEN
182 -- check for the exsistence of an alternate IGS_PE_PERSON ID record
183 OPEN c_api;
184 FETCH c_api INTO v_record_exists;
185 IF (c_api%FOUND) THEN
186 p_alternate_exists := TRUE;
187 END IF;
188 CLOSE c_api;
189 END IF;
190 IF p_check_address = TRUE THEN
191 -- check for the exsistence of an address record(correspondence)
192 OPEN c_pa;
193 FETCH c_pa INTO v_record_exists;
194 IF (c_pa%FOUND) THEN
195 p_address_exists := TRUE;
196 END IF;
197 CLOSE c_pa;
198 END IF;
199 IF p_check_disability = TRUE THEN
200 -- check for the exsistence of a IGS_PE_PERSON disability record
201 OPEN c_pd;
202 FETCH c_pd INTO v_record_exists;
203 IF (c_pd%FOUND) THEN
204 p_disability_exists := TRUE;
205 END IF;
206 CLOSE c_pd;
207 END IF;
208 IF p_check_visa = TRUE THEN
209 -- check for the exsistence of a international visa record
210 OPEN c_iv;
211 FETCH c_iv INTO v_record_exists;
212 IF (c_iv%FOUND) THEN
213 p_visa_exists := TRUE;
214 END IF;
215 CLOSE c_iv;
216 END IF;
217 IF p_check_finance = TRUE THEN
218 -- check for the exsistence of a IGS_PE_PERSON finance record
219 -- table does no exist yet
220 NULL;
221 END IF;
222 IF p_check_notes = TRUE THEN
223 -- check for the exsistence of a IGS_PE_PERSON notes record
224 OPEN c_pn;
225 FETCH c_pn INTO v_record_exists;
226 IF (c_pn%FOUND) THEN
227 p_notes_exists := TRUE;
228 END IF;
229 CLOSE c_pn;
230 END IF;
231 IF p_check_statistics = TRUE THEN
232 -- check for the exsistence of a IGS_PE_PERSON statistics record
233 OPEN c_ps;
234 FETCH c_ps INTO v_record_exists;
235 IF (c_ps%FOUND) THEN
236 p_statistics_exists := TRUE;
237 END IF;
238 CLOSE c_ps;
239 END IF;
240 IF p_check_alias = TRUE THEN
241 -- check for the exsistence of a IGS_PE_PERSON alias record
242 OPEN c_pal;
243 FETCH c_pal INTO v_record_exists;
244 IF (c_pal%FOUND) THEN
245 p_alias_exists := TRUE;
246 END IF;
247 CLOSE c_pal;
248 END IF;
249 RETURN;
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF (c_api%ISOPEN) THEN
253 CLOSE c_api;
254 END IF;
255 IF (c_pa%ISOPEN) THEN
256 CLOSE c_pa;
257 END IF;
258 IF (c_pd%ISOPEN) THEN
259 CLOSE c_pd;
260 END IF;
261 IF (c_iv%ISOPEN) THEN
262 CLOSE c_iv;
263 END IF;
264 IF (c_pn%ISOPEN) THEN
265 CLOSE c_pn;
266 END IF;
267 IF (c_ps%ISOPEN) THEN
268 CLOSE c_ps;
269 END IF;
270 IF (c_pal%ISOPEN) THEN
271 CLOSE c_api;
272 END IF;
273 END;
274 EXCEPTION
275 WHEN OTHERS THEN
276 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
277 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.enrp_get_pe_exists');
278 IGS_GE_MSG_STACK.ADD;
279 APP_EXCEPTION.RAISE_EXCEPTION;
280 END enrp_get_pe_exists;
281
282
283 FUNCTION Enrp_Get_Rule_Cutoff(
284 p_cal_type IN VARCHAR2 ,
285 p_ci_sequence_number IN NUMBER ,
286 p_date_type IN VARCHAR2 )
287 RETURN DATE AS
288
289 BEGIN -- enrp_get_rule_cutoff
290 -- Get one of the IGS_RU_RULE cutoff dates from the nominated teaching calendar.
291 -- The date type parameter indicates whether to get the enrolled or invalid
292 -- cut off dates.
293 DECLARE
294 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
295 CURSOR c_alias_val IS
296 SELECT daiv.alias_val
297 FROM IGS_EN_CAL_CONF secc,
298 IGS_CA_DA_INST_V daiv
299 WHERE secc.s_control_num = 1 AND
300 daiv.dt_alias = DECODE(p_date_type,
301 'ENROLLED',NVL(secc.enrolled_rule_cutoff_dt_alias, 'NULL'),
302 'INVALID', NVL(secc.invalid_rule_cutoff_dt_alias,'NULL'),
303 'NULL' ) AND
304 daiv.cal_type = p_cal_type AND
305 daiv.ci_sequence_number = p_ci_sequence_number;
306 BEGIN
307 -- Validate IGS_PS_UNIT version
308 OPEN c_alias_val;
309 FETCH c_alias_val INTO v_alias_val;
310 -- * Get the earliest date alias instance value within the
311 -- specified teaching calendar. If no records found (in either
312 -- secc or daiv) or the invalid_rule_cutoff_dt_alias is null
313 -- then NULL will be returned.
314 IF (c_alias_val%NOTFOUND) THEN
315 CLOSE c_alias_val;
316 RETURN NULL;
317 ELSE
318 CLOSE c_alias_val;
319 RETURN v_alias_val;
320 END IF;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 IF c_alias_val%ISOPEN THEN
325 CLOSE c_alias_val;
326 END IF;
327 RAISE;
328 END;
329 /*
330 EXCEPTION
331 WHEN OTHERS THEN
332 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
333 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.enrp_get_rule_cutoff');
334 IGS_GE_MSG_STACK.ADD;
335 APP_EXCEPTION.RAISE_EXCEPTION;
336 */
337 END enrp_get_rule_cutoff;
338
339 FUNCTION Enrp_Get_Scah_Col(
340 p_column_name IN VARCHAR2 ,
341 p_person_id IN IGS_AS_SC_ATTEMPT_H_ALL.person_id%TYPE ,
342 p_course_cd IN IGS_AS_SC_ATTEMPT_H_ALL.course_cd%TYPE ,
343 p_hist_end_dt IN IGS_AS_SC_ATTEMPT_H_ALL.hist_end_dt%TYPE )
344 RETURN VARCHAR2 AS
345 gv_other_detail VARCHAR2(255);
346 BEGIN
347 DECLARE
348 CURSOR c_sca_hist (cp_column_name user_tab_columns.column_name%TYPE,
349 cp_person_id IGS_AS_SC_ATTEMPT_H.person_id%TYPE,
350 cp_course_cd IGS_AS_SC_ATTEMPT_H.course_cd%TYPE,
351 cp_hist_end_dt IGS_AS_SC_ATTEMPT_H.hist_end_dt%TYPE) IS
352 SELECT DECODE(cp_column_name, 'VERSION_NUMBER', TO_CHAR(scah.version_number),
353 'cal_type', scah.cal_type,
354 'LOCATION_CD', scah.location_cd,
355 'attendance_mode', scah.attendance_mode,
356 'attendance_type', scah.attendance_type,
357 'STUDENT_CONFIRMED_IND', scah.student_confirmed_ind,
358 'COMMENCEMENT_DT', igs_ge_date.igscharDT(scah.commencement_dt),
359 'COURSE_ATTEMPT_STATUS', scah.course_attempt_status,
360 'DERIVED_ATT_TYPE', scah.derived_att_type,
361 'DERIVED_ATT_MODE', scah.derived_att_mode,
362 'PROVISIONAL_IND', scah.provisional_ind,
363 'DISCONTINUED_DT', igs_ge_date.igschar(scah.discontinued_dt),
364 'DISCONTINUATION_REASON_CD', scah.DISCONTINUATION_REASON_CD,
365 'FUNDING_SOURCE', scah.FUNDING_SOURCE,
366 'EXAM_LOCATION_CD', scah.exam_location_cd,
367 'DERIVED_COMPLETION_YR', TO_CHAR(scah.derived_completion_yr),
368 'DERIVED_COMPLETION_PERD', scah.derived_completion_perd,
369 'NOMINATED_COMPLETION_YR', TO_CHAR(scah.nominated_completion_yr),
370 'NOMINATED_COMPLETION_PERD', scah.nominated_completion_perd,
371 'RULE_CHECK_IND', scah.rule_check_ind,
372 'WAIVE_OPTION_CHECK_IND', scah.waive_option_check_ind,
373 'LAST_RULE_CHECK_DT', igs_ge_date.igschar(scah.last_rule_check_dt),
374 'PUBLISH_OUTCOMES_IND', scah.publish_outcomes_ind,
375 'COURSE_RQRMNT_COMPLETE_IND', scah.course_rqrmnt_complete_ind,
376 'OVERRIDE_TIME_LIMITATION', TO_CHAR(scah.override_time_limitation),
377 'ADVANCED_STANDING_IND', scah.advanced_standing_ind,
378 'FEE_CAT', scah.FEE_CAT,
379 'IGS_CO_CAT', scah.CORRESPONDENCE_CAT,
380 'SELF_HELP_GROUP_IND', scah.self_help_group_ind,
381 'PRIMARY_PROGRAM_TYPE', primary_program_type, --Bug 2162747 by vvutukur
382 'KEY_PROGRAM', key_program) --Bug 2162747 by vvutukur
383 FROM IGS_AS_SC_ATTEMPT_H scah
384 WHERE scah.person_id = cp_person_id AND
385 scah.course_cd = cp_course_cd AND
386 scah.hist_start_dt >= cp_hist_end_dt
387 ORDER BY
388 scah.hist_start_dt ASC;
389 v_column_value VARCHAR2(2000);
390 BEGIN
391 OPEN c_sca_hist(p_column_name,
392 p_person_id,
393 p_course_cd,
394 p_hist_end_dt);
395 LOOP
396 FETCH c_sca_hist INTO v_column_value;
397 IF (c_sca_hist%NOTFOUND) THEN
398 CLOSE c_sca_hist;
399 RETURN NULL;
400 END IF;
401 IF NVL(v_column_value,'NULL') <> 'NULL' THEN
402 CLOSE c_sca_hist;
403 RETURN v_column_value;
404 END IF;
405 END LOOP;
406 CLOSE c_sca_hist;
407 RETURN NULL;
408 END;
409 EXCEPTION
410 WHEN OTHERS THEN
411 gv_other_detail := 'Parm: p_column_name - ' || p_column_name
412 || ' p_person_id - ' || TO_CHAR(p_person_id)
413 || ' p_course_cd - ' || p_course_cd
414 || ' p_hist_end_dt - ' || igs_ge_date.igschar(p_hist_end_dt);
415
416 RAISE;
417 END enrp_get_scah_col;
418
419 FUNCTION Enrp_Get_Scae_Due(
420 p_person_id IN NUMBER ,
421 p_course_cd IN VARCHAR2 ,
422 p_cal_type IN VARCHAR2 ,
423 p_ci_sequence_number IN NUMBER ,
424 p_passing_due_date_ind IN VARCHAR2 ,
425 p_enr_form_due_dt IN DATE )
426 RETURN DATE AS
427
428 BEGIN -- enrp_get_scae_due
429 -- Get the enrolment form due date for a nominated student IGS_PS_COURSE attempt
430 -- enrolment record. The logic is,
431 -- If a student has the IGS_AS_SC_ATMPT_ENR.enr_form_due_dt set, then this
432 -- is used. Else, it is search for in the enrolment period matching the
433 -- IGS_EN_CAL_CONF. enr_form_due_dt_alias (the latest date is selected)
434 DECLARE
435 v_enr_form_due_dt IGS_AS_SC_ATMPT_ENR.enr_form_due_dt%TYPE;
436 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
437 CURSOR c_scae IS
438 SELECT scae.enr_form_due_dt
439 FROM IGS_AS_SC_ATMPT_ENR scae
440 WHERE scae.person_id = p_person_id AND
441 scae.course_cd = p_course_cd AND
442 scae.cal_type = p_cal_type AND
443 scae.ci_sequence_number = p_ci_sequence_number AND
444 scae.enr_form_due_dt IS NOT NULL;
445
446 CURSOR c_latest_alias_val IS
447 SELECT IGS_CA_GEN_001.calp_set_alias_value(
448 daiv.absolute_val,
449 IGS_CA_GEN_002.cals_clc_dt_from_dai(
450 daiv.ci_sequence_number,
451 daiv.CAL_TYPE,
452 daiv.DT_ALIAS,
453 daiv.sequence_number) ) alias_val
454 FROM IGS_EN_CAL_CONF secc,
455 IGS_CA_DA_INST daiv
456 WHERE secc.s_control_num = 1 AND
457 daiv.cal_type = p_cal_type AND
458 daiv.ci_sequence_number = p_ci_sequence_number AND
459 daiv.dt_alias = secc.enr_form_due_dt_alias AND
460 IGS_CA_GEN_001.calp_set_alias_value(
461 daiv.absolute_val,
462 IGS_CA_GEN_002.cals_clc_dt_from_dai(
463 daiv.ci_sequence_number,
464 daiv.CAL_TYPE,
465 daiv.DT_ALIAS,
466 daiv.sequence_number) ) IS NOT NULL
467 ORDER BY 1 DESC; -- gives latest date first
468
469 BEGIN
470 -- If the parameter enrolment form due date is passed
471 IF p_passing_due_date_ind = 'Y' AND
472 p_enr_form_due_dt IS NOT NULL THEN
473 RETURN p_enr_form_due_dt;
474 END IF;
475 -- If the date was not passed, then query the scae record for the date.
476 -- The scae record should not have a null enr_form_due_dt.
477 IF p_passing_due_date_ind = 'N' THEN
478 OPEN c_scae;
479 FETCH c_scae INTO v_enr_form_due_dt;
480 IF (c_scae%FOUND) THEN
481 CLOSE c_scae;
482 RETURN v_enr_form_due_dt;
483 END IF;
484 CLOSE c_scae;
485 END IF;
486 -- Query the latest IGS_CA_DA_INST_V from the enrolment calendar instance
487 OPEN c_latest_alias_val;
488 FETCH c_latest_alias_val INTO v_alias_val;
489 IF (c_latest_alias_val%FOUND) THEN
490 CLOSE c_latest_alias_val;
491 RETURN v_alias_val;
492 END IF;
493 CLOSE c_latest_alias_val;
494 RETURN NULL;
495 EXCEPTION
496 WHEN OTHERS THEN
497 IF (c_scae%ISOPEN) THEN
498 CLOSE c_scae;
499 END IF;
500 IF (c_latest_alias_val%ISOPEN) THEN
501 CLOSE c_latest_alias_val;
502 END IF;
503 RAISE;
504 END;
505 /*
506 EXCEPTION
507 WHEN OTHERS THEN
508 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
509 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.enrp_get_scae_due');
510 IGS_GE_MSG_STACK.ADD;
511 APP_EXCEPTION.RAISE_EXCEPTION;
512 */
513 END enrp_get_scae_due;
514
515 -- Following function modified as part of the Enrollments Process build - Bug #1832130
516 -- Created By : jbegum
517 -- This function will determine whether it is possible at the effective date to record new unit attempts in the nominated teaching period calendar instance
518 -- Validation is done at 3 levels ie person_type level , unit_section level and institutional level
519
520 -- Function modified by Nishikant - 21MAR2002 - Bug#2274500
521 -- Function was not returning TRUE if no date alias instances found at Person level or Unit section level or institution level, It was returning FALSE.
522
523 FUNCTION Enrp_Get_Rec_Window(
524 p_cal_type IN VARCHAR2 ,
525 p_ci_sequence_number IN NUMBER,
526 p_effective_date IN DATE,
527 p_uoo_id IN NUMBER ,
528 p_message_name OUT NOCOPY VARCHAR2)
529 RETURN BOOLEAN AS
530 /*******************************************************************************************************************************************************
531 Created By :Syam
532 Date Created By :
533 Purpose :-- Enrp_Get_Rec_Window
534 -- Function will determine whether it is possible at the effective date to record new unit attempts in the nominated teaching period calendar
535 -- instance at 3 levels ie person_type level , unit_section level and institutional level.
536 -- If the effective date lies between the absolute values of Record open and Record cutoff date aliases defined at person level the function
537 -- returns true else returns false .
538 -- If no date aliases defined at person level then check at unit section level
539 -- If the effective date lies between the absolute values of Record open date alias defined at institutional level and Record cutoff date alias
540 -- defined at unit section level the function returns true else returns false .
541 -- If no Record cutoff date alias defined at unit section level then check at institutional level
542 -- If the effective date lies between the absolute values of Record open and Record cutoff date aliases defined at institutional level the function
543 -- returns true else returns false .
544 -- If no date aliases defined at any level then fuction returns true
545 Change History
546 Who When What
547 ayedubat 30-MAY-2002 Added a new parameter,p_message_name for the bug fix:2337161
548 kkillams 23-12-2002 Modified Function, Current function is not returning FALSE when both Record Cutoff date alias and Record Open date alias are past dates
549 and Record Cutoff date alias value is less than Record Open date alias value for Institution setup , w.r.t. bug 2660310
550 **********************************************************************************************************************************************************/
551 BEGIN
552 DECLARE
553 l_person_type IGS_PE_USR_ARG.person_type%TYPE;
554 l_record_open_dt_alias IGS_EN_CAL_CONF.record_open_dt_alias%TYPE;
555 l_record_cutoff_dt_alias IGS_EN_CAL_CONF.record_cutoff_dt_alias%TYPE;
556 l_daiv_rec_found BOOLEAN;
557 l_rec_open_dt_pass BOOLEAN;
558 l_open_dt IGS_CA_DA_INST_V.alias_val%TYPE;
559 l_rec_cutt_off_dt IGS_CA_DA_INST_V.alias_val%TYPE;
560 l_effective_date DATE;
561
562 --modified cursor for performance bug 3696424
563 CURSOR c_recdt_alias_per_lvl( cp_person_type IGS_PE_USR_ARG.person_type%TYPE ) IS
564 SELECT record_open_dt_alias,
565 record_cutoff_dt_alias
566 FROM IGS_PE_USR_ARG_ALL
567 WHERE person_type = cp_person_type;
568
569 --Cursor is to get the all open date alias values for a calendar instance in ascending order.
570 CURSOR c_alias_val_op_dt(cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
571 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
572 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
573 SELECT alias_val FROM IGS_CA_DA_INST_V
574 WHERE cal_type = cp_cal_type
575 AND ci_sequence_number = cp_ci_sequence_number
576 AND dt_alias = cp_dt_alias
577 AND alias_val IS NOT NULL
578 ORDER BY alias_val ASC;
579
580 --Cursor is to get the all Record Cutt-off alias values for a calendar instance in descending order.
581 CURSOR c_alias_val_rec_dt(cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
582 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
583 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
584 SELECT alias_val FROM IGS_CA_DA_INST_V
585 WHERE cal_type = cp_cal_type
586 AND ci_sequence_number = cp_ci_sequence_number
587 AND dt_alias = cp_dt_alias
588 AND alias_val IS NOT NULL
589 ORDER BY alias_val DESC;
590
591 -- Cursor to check the Non-Standard Unit Section
592 CURSOR cur_non_std_usec_ind(p_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
593 SELECT non_std_usec_ind FROM IGS_PS_UNIT_OFR_OPT
594 WHERE uoo_id = p_uoo_id;
595
596 CURSOR c_recdt_alias_usec_lvl1( cp_uoo_id IGS_EN_NSTD_USEC_DL.uoo_id%TYPE ) IS
597 SELECT enr_dl_date
598 FROM IGS_EN_NSTD_USEC_DL
599 WHERE function_name = 'RECORD_CUTOFF'
600 AND uoo_id = cp_uoo_id;
601
602 CURSOR c_recdt_alias_usec_lvl2 IS
603 SELECT record_open_dt_alias
604 FROM IGS_EN_CAL_CONF
605 WHERE s_control_num = 1;
606
607 CURSOR c_recdt_alias_inst_lvl IS
608 SELECT record_open_dt_alias,
609 record_cutoff_dt_alias
610 FROM IGS_EN_CAL_CONF
611 WHERE s_control_num = 1;
612
613 l_non_std_usec_ind IGS_PS_UNIT_OFR_OPT.non_std_usec_ind%TYPE;
614
615 BEGIN
616
617 -- initialize the message parameter with NULL
618 p_message_name := NULL;
619 l_effective_date := TRUNC(p_effective_date);
620 -- Validation at person_type level
621 l_person_type := IGS_EN_GEN_008.enrp_get_person_type;
622
623 IF l_person_type IS NOT NULL THEN
624
625 OPEN c_recdt_alias_per_lvl(l_person_type);
626 FETCH c_recdt_alias_per_lvl INTO l_record_open_dt_alias,
627 l_record_cutoff_dt_alias;
628 -- If no date aliases defined at person_type level go to unit_section level
629 IF (c_recdt_alias_per_lvl%FOUND) THEN
630 CLOSE c_recdt_alias_per_lvl;
631 -- If both date alias values are null at person_type level go to unit_section level
632 IF (l_record_open_dt_alias IS NOT NULL OR l_record_cutoff_dt_alias IS NOT NULL) THEN
633 l_open_dt := NULL;
634 l_rec_cutt_off_dt := NULL;
635 IF l_record_open_dt_alias IS NOT NULL THEN
636 OPEN c_alias_val_op_dt (p_cal_type,
637 p_ci_sequence_number,
638 l_record_open_dt_alias);
639 FETCH c_alias_val_op_dt INTO l_open_dt;
640 CLOSE c_alias_val_op_dt;
641 END IF; -- l_record_open_dt_alias IS NOT NULL
642
643 IF l_record_cutoff_dt_alias IS NOT NULL THEN
644 OPEN c_alias_val_rec_dt(p_cal_type,
645 p_ci_sequence_number,
646 l_record_cutoff_dt_alias);
647 FETCH c_alias_val_rec_dt INTO l_rec_cutt_off_dt;
648 CLOSE c_alias_val_rec_dt;
649 END IF; --l_record_cutoff_dt_alias IS NOT NULL
650
651 --Return true if open date is defined and effective date is greater than open date and
652 --record cut-off date is defined and effective date is less than or equal to the record
653 --cut-off date else return false along error message.
654 IF (l_open_dt IS NULL OR l_open_dt <= l_effective_date) AND
655 (l_rec_cutt_off_dt IS NULL OR l_effective_date <= l_rec_cutt_off_dt) THEN
656 RETURN TRUE;
657 ELSE
658 p_message_name := 'IGS_EN_SUA_NOTENR_OUTS_REC_PT';
659 RETURN FALSE;
660 END IF;
661 END IF; -- For the IF checking whether l_record_open_dt_alias IS NOT NULL OR l_record_cutoff_dt_alias IS NOT NULL
662 END IF; -- For the IF checking whether c_recdt_alias_per_lvl%FOUND
663 END IF; -- For the IF checking whether l_person_type IS NOT NULL
664
665 -- Validation at unit_section level
666
667 l_record_open_dt_alias := NULL;
668 l_record_cutoff_dt_alias := NULL;
669 l_rec_cutt_off_dt := NULL;
670 l_open_dt := NULL;
671
672 OPEN c_recdt_alias_usec_lvl1(p_uoo_id);
673 OPEN c_recdt_alias_usec_lvl2;
674 -- record cutoff date is being fetched from Unit Section level and record open date alias
675 -- is being fetched from Institution level
676 FETCH c_recdt_alias_usec_lvl1 INTO l_rec_cutt_off_dt;
677 FETCH c_recdt_alias_usec_lvl2 INTO l_record_open_dt_alias;
678 IF c_recdt_alias_usec_lvl1%FOUND THEN
679 CLOSE c_recdt_alias_usec_lvl1;
680 CLOSE c_recdt_alias_usec_lvl2;
681 IF l_record_open_dt_alias IS NOT NULL THEN
682 OPEN c_alias_val_op_dt (p_cal_type,
683 p_ci_sequence_number,
684 l_record_open_dt_alias);
685 FETCH c_alias_val_op_dt INTO l_open_dt;
686 CLOSE c_alias_val_op_dt;
687 END IF; -- l_record_open_dt_alias IS NOT NULL
688 IF (l_open_dt IS NULL OR l_open_dt <= l_effective_date) AND
689 (l_rec_cutt_off_dt IS NULL OR l_effective_date <= l_rec_cutt_off_dt) THEN
690 RETURN TRUE;
691 ELSE
692 OPEN cur_non_std_usec_ind(p_uoo_id);
693 FETCH cur_non_std_usec_ind INTO l_non_std_usec_ind;
694 CLOSE cur_non_std_usec_ind;
695
696 -- Check weather the Unit Section is Standard or Non Standard as assign the message accordingly.
697 IF l_non_std_usec_ind = 'Y' THEN
698 p_message_name := 'IGS_EN_SUA_NOTENR_OUTS_REC_NSU';
699 ELSE
700 p_message_name := 'IGS_EN_SUA_NOTENR_OUTS_REC_SU';
701 END IF;
702 RETURN FALSE;
703 END IF;
704 ELSE
705 CLOSE c_recdt_alias_usec_lvl1;
706 CLOSE c_recdt_alias_usec_lvl2;
707 END IF;
708
709 -- Validation at Institutional level
710
711 l_record_open_dt_alias := NULL;
712 l_record_cutoff_dt_alias := NULL;
713 OPEN c_recdt_alias_inst_lvl;
714 FETCH c_recdt_alias_inst_lvl INTO l_record_open_dt_alias,
715 l_record_cutoff_dt_alias;
716
717 -- If no dates defined at any level then the function returns true
718 IF (c_recdt_alias_inst_lvl%NOTFOUND) THEN
719 CLOSE c_recdt_alias_inst_lvl;
720 RETURN TRUE;
721 END IF;
722 -- If date aliases defined at institution level
723 CLOSE c_recdt_alias_inst_lvl;
724
725 -- If both date alias values are NULL then return TRUE in ELSE part
726 IF (l_record_cutoff_dt_alias IS NOT NULL OR l_record_open_dt_alias IS NOT NULL) THEN
727
728 l_daiv_rec_found := FALSE;
729 -- This variable helps for checking when cut-off date alias or cut off date alias instances are not defined
730 l_rec_open_dt_pass := FALSE;
731
732 l_open_dt := NULL;
733 l_rec_cutt_off_dt := NULL;
734 IF l_record_open_dt_alias IS NOT NULL THEN
735 OPEN c_alias_val_op_dt (p_cal_type,
736 p_ci_sequence_number,
737 l_record_open_dt_alias);
738 FETCH c_alias_val_op_dt INTO l_open_dt;
739 CLOSE c_alias_val_op_dt;
740 END IF; -- l_record_open_dt_alias IS NOT NULL
741
742 IF l_record_cutoff_dt_alias IS NOT NULL THEN
743 OPEN c_alias_val_rec_dt(p_cal_type,
744 p_ci_sequence_number,
745 l_record_cutoff_dt_alias);
746 FETCH c_alias_val_rec_dt INTO l_rec_cutt_off_dt;
747 CLOSE c_alias_val_rec_dt;
748 END IF; --l_record_cutoff_dt_alias IS NOT NULL
749
750 --Return true if open date is defined and effective date is greater than open date and
751 --record cut-off date is defined and effective date is less than or equal to the record
752 --cut-off date else return false along error message.
753 IF (l_open_dt IS NULL OR l_open_dt <= l_effective_date) AND
754 (l_rec_cutt_off_dt IS NULL OR l_effective_date <= l_rec_cutt_off_dt) THEN
755 RETURN TRUE;
756 ELSE
757 p_message_name := 'IGS_EN_SUA_NOTENR_RECENR_WIN';
758 RETURN FALSE;
759 END IF;
760 END IF;
761 -- If both date alises are not defined at any level then the function returns true
762 RETURN TRUE;
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
767 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.enrp_get_rec_window');
768 IGS_GE_MSG_STACK.ADD;
769 APP_EXCEPTION.RAISE_EXCEPTION;
770
771 END;
772
773 END enrp_get_rec_window;
774
775 FUNCTION Enrp_Get_Perd_Num(
776 p_person_id IN NUMBER ,
777 p_course_cd IN VARCHAR2 ,
778 p_acad_cal_type IN VARCHAR2 ,
779 p_acad_sequence_number IN NUMBER ,
780 p_acad_start_dt IN DATE )
781 RETURN NUMBER AS
782
783 BEGIN -- enrp_get_perd_num
784 -- Get the academic period number of the students enrolment. This is done by
785 -- looping through the academic periods within which the student has studied
786 -- IGS_PS_UNIT attempts.
787 DECLARE
788 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
789 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
790 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
791 v_record_count NUMBER;
792 CURSOR c_sua_cir_ci_cat_cs IS
793 SELECT DISTINCT cir.sup_cal_type,
794 cir.sup_ci_sequence_number
795 FROM IGS_EN_SU_ATTEMPT sua,
796 IGS_CA_INST_REL cir,
797 IGS_CA_INST ci,
798 IGS_CA_TYPE cat,
799 IGS_CA_STAT cs
800 WHERE sua.person_id = p_person_id AND
801 sua.course_cd = p_course_cd AND
802 sua.unit_attempt_status <> cst_unconfirm AND
803 cir.sub_cal_type = sua.cal_type AND
804 cir.sub_ci_sequence_number = sua.ci_sequence_number AND
805 ci.cal_type = cir.sup_cal_type AND
806 ci.sequence_number = cir.sup_ci_sequence_number AND
807 cat.cal_type = ci.cal_type AND
808 cat.S_CAL_CAT = cst_academic AND
809 cs.CAL_STATUS = ci.CAL_STATUS AND
810 ci.start_dt < p_acad_start_dt;
811 BEGIN
812 v_record_count := 0;
813 FOR v_sua_cir_ci_cat_cs_rec IN c_sua_cir_ci_cat_cs LOOP
814 v_record_count := v_record_count+1;
815 END LOOP;
816 RETURN v_record_count+1;
817 EXCEPTION
818 WHEN OTHERS THEN
819 IF (c_sua_cir_ci_cat_cs%ISOPEN) THEN
820 CLOSE c_sua_cir_ci_cat_cs;
821 END IF;
822 RAISE;
823 END;
824 EXCEPTION
825 WHEN OTHERS THEN
826 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
827 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.enrp_get_perd_num');
828 IGS_GE_MSG_STACK.ADD;
829 APP_EXCEPTION.RAISE_EXCEPTION;
830 END enrp_get_perd_num;
831
832 -- Following procedure added as part of the Enrollments Process build - Bug #1832130
833 -- Created By : jbegum
834 -- This procedure when invoked will discontinue/drop all unit attempts of a student within a given term calendar
835
836 PROCEDURE Enrp_Dropall_Unit(
837 p_person_id IN NUMBER ,
838 p_cal_type IN VARCHAR2 ,
839 p_ci_sequence_number IN NUMBER ,
840 p_dcnt_reason_cd IN VARCHAR2 ,
841 p_admin_unit_sta IN VARCHAR2 ,
842 p_effective_date IN DATE ,
843 p_program_cd IN VARCHAR2,
844 p_uoo_id IN NUMBER,
845 p_sub_unit IN VARCHAR2
846 )
847
848 AS
849
850 BEGIN -- Enrp_Dropall_Unit
851 -- Update all the unit attempt records of a student in the table IGS_EN_SU_ATTEMPT with
852 -- unit_attempt_status as 'DROPPED' or 'DISCONTIN'
853 /* HISTORY
854 WHO WHEN WHAT
855 mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in update row of IGS_EN_SU_ATTEMPT
856 for Bug 2554109 MINI Waitlist Build for Jan 03 Release
857 ayedubat 02-JUL-2002 Added a new validation to check the Variation Window Cutoff Date
858 for the bug Fix:2423605
859 ayedubat 26-JUN-2002 Changed the width of the variable,l_adm_unit_status_ret to VARCHAR2(255)
860 and also removed the exception handler for the bug fix:2423605
861 rvangala 07-OCT-03 Passing core_indicator_code to IGS_EN_SUA-API.UPDATE_UNIT_ATTEMPT added as part of Prevent Dropping
862 Core Units. Enh Bug# 3052432
863 amuthu 9-Aug-2006 If the default drop reason cannot be determined then
864 -- stopping the further processing and showing a newly added message*/
865
866 DECLARE
867
868 l_unit_attempt VARCHAR2(1);
869 l_adm_unit_status_ret VARCHAR2(255);
870 l_adm_unit_status VARCHAR2(2000);
871 l_alias_val DATE;
872 l_first_char NUMBER;
873 l_current_string VARCHAR2(10);
874 l_val VARCHAR2(1);
875 --modified cursor for performance bug 3693713
876 CURSOR c_unit_attempt IS
877 SELECT U.*
878 FROM IGS_EN_SU_ATTEMPT U
879 WHERE person_id = p_person_id
880 AND ((unit_attempt_status IN ('ENROLLED','INVALID','WAITLISTED'))
881 OR (unit_attempt_status = 'DUPLICATE' AND P_UOO_ID IS NOT NULL))
882 AND course_cd = p_program_cd AND ((p_uoo_id IS NULL) OR (uoo_id = p_uoo_id));
883
884 -- Added new cursor for performance bug 3693713
885 CURSOR c_is_unit_exists(p_load_cal_type IGS_CA_INST.cal_type%TYPE,
886 p_load_ci_seq_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
887 p_tch_cal_type IGS_CA_INST.cal_type%TYPE,
888 p_tch_ci_seq_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
889 SELECT 'x'
890 FROM igs_ca_load_to_teach_v
891 WHERE load_cal_type = p_load_cal_type
892 AND load_ci_sequence_number = p_load_ci_seq_number
893 AND teach_cal_type = p_tch_cal_type
894 AND teach_ci_sequence_number = p_tch_ci_seq_number;
895
896 BEGIN
897
898 -- check if all the parameters are specified ie they are not null
899 IF p_dcnt_reason_cd IS NULL THEN
900 Fnd_Message.Set_Name('IGS' , 'IGS_EN_DFLT_DCNT_RSN_NOT_SETUP');
901 IGS_GE_MSG_STACK.ADD;
902 App_Exception.Raise_Exception;
903 END IF;
904
905 IF p_person_id IS NULL OR
906 p_cal_type IS NULL OR
907 p_ci_sequence_number IS NULL THEN
908
909 Fnd_Message.Set_Name('IGS' , 'IGS_GE_INSUFFICIENT_PARAMETER');
910 IGS_GE_MSG_STACK.ADD;
911 App_Exception.Raise_Exception;
912
913 END IF;
914
915 -- Checking whether dropping of the 'unit attempt BY a student ' is allowed within the nominated teaching calendar instance
916 -- at the nominated effective date
917
918 FOR c_unit_attempt_rec IN c_unit_attempt LOOP
919 OPEN c_is_unit_exists(p_cal_type,
920 p_ci_sequence_number,
921 c_unit_attempt_rec.cal_type,
922 c_unit_attempt_rec.ci_sequence_number);
923 FETCH c_is_unit_exists INTO l_val;
924
925 IF c_is_unit_exists%FOUND THEN
926
927 IF p_sub_unit = 'Y' THEN
928 l_unit_attempt := 'Y';
929 ELSE
930 l_unit_attempt := IGS_EN_GEN_008.Enrp_Get_Ua_Del_Alwd(P_CAL_TYPE => c_unit_attempt_rec.cal_type,
931 P_CI_SEQUENCE_NUMBER => c_unit_attempt_rec.ci_sequence_number,
932 P_EFFECTIVE_DT => p_effective_date,
933 P_UOO_ID => c_unit_attempt_rec.uoo_id );
934
935 END IF;
936 -- Validate the variation window dead limits if the Unit Attempt Status is not 'WAITLISTED'
937 IF c_unit_attempt_rec.unit_attempt_status NOT IN ('WAITLISTED','DUPLICATE') THEN
938
939 -- If the Dropped/Discontinued Date is not with in the Variation window boundary, Raise the Error
940 -- Otherwise Continue the Processing
941 IF NOT igs_en_gen_008.enrp_get_var_window(
942 c_unit_attempt_rec.cal_type,
943 c_unit_attempt_rec.ci_sequence_number,
944 p_effective_date,
945 c_unit_attempt_rec.uoo_id ) THEN
946
947 Fnd_Message.Set_Name('IGS','IGS_EN_SUA_NOTENR_DISCONT');
948 IGS_GE_MSG_STACK.ADD;
949 App_Exception.Raise_Exception;
950
951 END IF;
952 END IF;
953
954 -- if it is a duplicate unit attempt then delete the unit attempt instead of dropping it.
955
956 IF c_unit_attempt_rec.unit_attempt_status = 'DUPLICATE' THEN
957 IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW(X_ROWID => c_unit_attempt_rec.row_id);
958 RETURN;
959 END If;
960
961 -- If dropping of the 'unit attempt BY a student ' is allowed within the nominated teaching calendar instance at the nominated
962 -- effective date then update the unit_attempt_status to 'DROPPED'
963
964 -- Added the OR clause in the below If condtion OR unit_attempt status is WAITLISTED
965 -- Added by Nishikant - bug#2364216. If the status is WAITLISTED then no need to check whether the unit attempt can be deleted
966 IF (l_unit_attempt = 'Y' OR c_unit_attempt_rec.unit_attempt_status = 'WAITLISTED') THEN
967
968 -- Call the API to update the student unit attempt. This API is a
969 -- wrapper to the update row of the TBH.
970 igs_en_sua_api.update_unit_attempt (
971 X_ROWID => c_unit_attempt_rec.row_id,
972 X_PERSON_ID => c_unit_attempt_rec.person_id,
973 X_COURSE_CD => c_unit_attempt_rec.course_cd,
974 X_UNIT_CD => c_unit_attempt_rec.unit_cd,
975 X_CAL_TYPE => c_unit_attempt_rec.cal_type,
976 X_CI_SEQUENCE_NUMBER => c_unit_attempt_rec.ci_sequence_number,
977 X_VERSION_NUMBER => c_unit_attempt_rec.version_number,
978 X_LOCATION_CD => c_unit_attempt_rec.location_cd,
979 X_UNIT_CLASS => c_unit_attempt_rec.unit_class,
980 X_CI_START_DT => c_unit_attempt_rec.ci_start_dt,
981 X_CI_END_DT => c_unit_attempt_rec.ci_end_dt,
982 X_UOO_ID => c_unit_attempt_rec.uoo_id,
983 X_ENROLLED_DT => c_unit_attempt_rec.enrolled_dt,
984 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
985 X_ADMINISTRATIVE_UNIT_STATUS => NULL,
986 X_DISCONTINUED_DT => nvl(p_effective_date,trunc(SYSDATE)),
987 X_RULE_WAIVED_DT =>c_unit_attempt_rec.rule_waived_dt,
988 X_RULE_WAIVED_PERSON_ID =>c_unit_attempt_rec.rule_waived_person_id,
989 X_NO_ASSESSMENT_IND => c_unit_attempt_rec.no_assessment_ind,
990 X_SUP_UNIT_CD => c_unit_attempt_rec.sup_unit_cd,
991 X_SUP_VERSION_NUMBER => c_unit_attempt_rec.sup_version_number,
992 X_EXAM_LOCATION_CD => c_unit_attempt_rec.exam_location_cd,
993 X_ALTERNATIVE_TITLE => c_unit_attempt_rec.alternative_title,
994 X_OVERRIDE_ENROLLED_CP => c_unit_attempt_rec.override_enrolled_cp,
995 X_OVERRIDE_EFTSU => c_unit_attempt_rec.override_eftsu,
996 X_OVERRIDE_ACHIEVABLE_CP => c_unit_attempt_rec.override_achievable_cp,
997 X_OVERRIDE_OUTCOME_DUE_DT => c_unit_attempt_rec.override_outcome_due_dt,
998 X_OVERRIDE_CREDIT_REASON => c_unit_attempt_rec.override_credit_reason,
999 X_ADMINISTRATIVE_PRIORITY => c_unit_attempt_rec.administrative_priority,
1000 X_WAITLIST_DT => c_unit_attempt_rec.waitlist_dt,
1001 X_DCNT_REASON_CD => p_dcnt_reason_cd,
1002 X_MODE => 'R',
1003 X_GS_VERSION_NUMBER => c_unit_attempt_rec.gs_version_number,
1004 X_ENR_METHOD_TYPE => c_unit_attempt_rec.enr_method_type,
1005 X_FAILED_UNIT_RULE => c_unit_attempt_rec.failed_unit_rule,
1006 X_CART => c_unit_attempt_rec.cart,
1007 X_RSV_SEAT_EXT_ID => c_unit_attempt_rec.rsv_seat_ext_id,
1008 X_ORG_UNIT_CD => c_unit_attempt_rec.org_unit_cd,
1009 -- Added the column grading schema code as a part of the bug 2037897. - aiyer
1010 X_GRADING_SCHEMA_CODE => c_unit_attempt_rec.grading_schema_code,
1011 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1012 X_SESSION_ID => c_unit_attempt_rec.session_id,
1013 --Added the column deg_aud_detail_id as part of Degree Audit Interface build. Bug# 2033208 - pradhakr
1014 X_DEG_AUD_DETAIL_ID => c_unit_attempt_rec.deg_aud_detail_id,
1015 X_SUBTITLE => c_unit_attempt_rec.subtitle,
1016 --Added the columns student_career_transcript,student_career_statistics as part of Career Impact DLD
1017 -- part 2 . Bug # svenkata
1018 X_STUDENT_CAREER_TRANSCRIPT => c_unit_attempt_rec.student_career_transcript,
1019 X_STUDENT_CAREER_STATISTICS => c_unit_attempt_rec.student_career_statistics,
1020 X_ATTRIBUTE_CATEGORY => c_unit_attempt_rec.attribute_category,
1021 X_ATTRIBUTE1 => c_unit_attempt_rec.attribute1,
1022 X_ATTRIBUTE2 => c_unit_attempt_rec.attribute2,
1023 X_ATTRIBUTE3 => c_unit_attempt_rec.attribute3,
1024 X_ATTRIBUTE4 => c_unit_attempt_rec.attribute4,
1025 X_ATTRIBUTE5 => c_unit_attempt_rec.attribute5,
1026 X_ATTRIBUTE6 => c_unit_attempt_rec.attribute6,
1027 X_ATTRIBUTE7 => c_unit_attempt_rec.attribute7,
1028 X_ATTRIBUTE8 => c_unit_attempt_rec.attribute8,
1029 X_ATTRIBUTE9 => c_unit_attempt_rec.attribute9,
1030 X_ATTRIBUTE10 => c_unit_attempt_rec.attribute10,
1031 X_ATTRIBUTE11 => c_unit_attempt_rec.attribute11,
1032 X_ATTRIBUTE12 => c_unit_attempt_rec.attribute12,
1033 X_ATTRIBUTE13 => c_unit_attempt_rec.attribute13,
1034 X_ATTRIBUTE14 => c_unit_attempt_rec.attribute14,
1035 X_ATTRIBUTE15 => c_unit_attempt_rec.attribute15,
1036 X_ATTRIBUTE16 => c_unit_attempt_rec.attribute16,
1037 X_ATTRIBUTE17 => c_unit_attempt_rec.attribute17,
1038 X_ATTRIBUTE18 => c_unit_attempt_rec.attribute18,
1039 X_ATTRIBUTE19 => c_unit_attempt_rec.attribute19,
1040 X_ATTRIBUTE20 => c_unit_attempt_rec.attribute20,
1041 X_WAITLIST_MANUAL_IND => c_unit_attempt_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109.,
1042 X_WLST_PRIORITY_WEIGHT_NUM => c_unit_attempt_rec.wlst_priority_weight_num,
1043 X_WLST_PREFERENCE_WEIGHT_NUM=> c_unit_attempt_rec.wlst_preference_weight_num,
1044 -- CORE_INDICATOR_CODE --added by rvangala 07-OCT-2003. Enh Bug# 3052432
1045 x_CORE_INDICATOR_CODE => c_unit_attempt_rec.core_indicator_code
1046 );
1047
1048 -- since a waitlisetd unit could have contributed to the fee we need
1049 -- to create a TODO record to recalculate the fee when a waitlisted unit
1050 -- is dropped. The unit would contribute towards the CP or fee based on the
1051 -- profile IGS_EN_INCL_WLST_CP
1052 IF c_unit_attempt_rec.unit_attempt_status = 'WAITLISTED' THEN
1053 IGS_SS_EN_WRAPPERS.call_fee_ass (
1054 p_person_id => p_person_id,
1055 p_cal_type => p_cal_type, -- load
1056 p_sequence_number => p_ci_sequence_number, -- load
1057 p_course_cd => c_unit_attempt_rec.course_cd,
1058 p_unit_cd => c_unit_attempt_rec.unit_cd,
1059 p_uoo_id => c_unit_attempt_rec.uoo_id
1060 );
1061 END IF;
1062
1063 ELSE
1064
1065 -- If dropping of the 'unit attempt BY a student ' is NOT allowed within the nominated teaching calendar instance at the nominated
1066 -- effective date then update the unit_attempt_status to 'DISCONTIN' also get the administrative unit
1067
1068 IF p_admin_unit_sta is NOT NULL THEN
1069 l_adm_unit_status_ret := p_admin_unit_sta;
1070 ELSE
1071 l_adm_unit_status_ret :=IGS_EN_GEN_008.Enrp_Get_Uddc_Aus (
1072 P_DISCONTINUED_DT => p_effective_date,
1073 P_CAL_TYPE => c_unit_attempt_rec.cal_type,
1074 P_CI_SEQUENCE_NUMBER => c_unit_attempt_rec.ci_sequence_number,
1075 P_ADMIN_UNIT_STATUS_STR => l_adm_unit_status,
1076 P_ALIAS_VAL => l_alias_val,
1077 P_UOO_ID => c_unit_attempt_rec.uoo_id );
1078 IF l_adm_unit_status_ret IS NULL THEN
1079 --l_adm_unit_status_ret := SUBSTR(l_adm_unit_status,1,10);sudhir
1080 l_adm_unit_status_ret := NULL;
1081 l_first_char := 1;
1082 LOOP
1083 -- exit when the end of the string is reached
1084 EXIT WHEN l_first_char >= LENGTH(l_adm_unit_status);
1085 -- put 10 characters at a a time into a string for comparison
1086 l_current_string := (SUBSTR(l_adm_unit_status, l_first_char, 10));
1087 -- don't do anything if the string is null
1088 IF (l_current_string IS NULL) THEN
1089 EXIT;
1090 ELSE
1091 IF l_adm_unit_status_ret IS NULL THEN
1092 l_adm_unit_status_ret := RTRIM(RPAD(l_current_string,10,' '));
1093 ELSE
1094 l_adm_unit_status_ret := l_adm_unit_status_ret||','||RTRIM(RPAD(l_current_string,10,' '));
1095 END IF;
1096 l_first_char := l_first_char + 11;
1097 END IF;
1098 END LOOP;
1099 Fnd_Message.Set_Name('IGS','IGS_SS_EN_MANY_ADMIN_UNSTA');
1100 FND_MESSAGE.SET_TOKEN('LIST',l_adm_unit_status_ret);
1101 IGS_GE_MSG_STACK.ADD;
1102 App_Exception.Raise_Exception;
1103 END IF;
1104 END IF;
1105
1106 -- Call the API to update the student unit attempt. This API is a
1107 -- wrapper to the update row of the TBH.
1108 igs_en_sua_api.update_unit_attempt(
1109 X_ROWID => c_unit_attempt_rec.row_id,
1110 X_PERSON_ID => c_unit_attempt_rec.person_id,
1111 X_COURSE_CD => c_unit_attempt_rec.course_cd,
1112 X_UNIT_CD => c_unit_attempt_rec.unit_cd,
1113 X_CAL_TYPE => c_unit_attempt_rec.cal_type,
1114 X_CI_SEQUENCE_NUMBER => c_unit_attempt_rec.ci_sequence_number,
1115 X_VERSION_NUMBER => c_unit_attempt_rec.version_number,
1116 X_LOCATION_CD => c_unit_attempt_rec.location_cd,
1117 X_UNIT_CLASS => c_unit_attempt_rec.unit_class,
1118 X_CI_START_DT => c_unit_attempt_rec.ci_start_dt,
1119 X_CI_END_DT => c_unit_attempt_rec.ci_end_dt,
1120 X_UOO_ID => c_unit_attempt_rec.uoo_id,
1121 X_ENROLLED_DT => c_unit_attempt_rec.enrolled_dt,
1122 X_UNIT_ATTEMPT_STATUS => 'DISCONTIN',
1123 X_ADMINISTRATIVE_UNIT_STATUS => l_adm_unit_status_ret,
1124 X_DISCONTINUED_DT => p_effective_date,
1125 X_RULE_WAIVED_DT =>c_unit_attempt_rec.rule_waived_dt,
1126 X_RULE_WAIVED_PERSON_ID =>c_unit_attempt_rec.rule_waived_person_id,
1127 X_NO_ASSESSMENT_IND => c_unit_attempt_rec.no_assessment_ind,
1128 X_SUP_UNIT_CD => c_unit_attempt_rec.sup_unit_cd,
1129 X_SUP_VERSION_NUMBER => c_unit_attempt_rec.sup_version_number,
1130 X_EXAM_LOCATION_CD => c_unit_attempt_rec.exam_location_cd,
1131 X_ALTERNATIVE_TITLE => c_unit_attempt_rec.alternative_title,
1132 X_OVERRIDE_ENROLLED_CP => c_unit_attempt_rec.override_enrolled_cp,
1133 X_OVERRIDE_EFTSU => c_unit_attempt_rec.override_eftsu,
1134 X_OVERRIDE_ACHIEVABLE_CP => c_unit_attempt_rec.override_achievable_cp,
1135 X_OVERRIDE_OUTCOME_DUE_DT => c_unit_attempt_rec.override_outcome_due_dt,
1136 X_OVERRIDE_CREDIT_REASON => c_unit_attempt_rec.override_credit_reason,
1137 X_ADMINISTRATIVE_PRIORITY => c_unit_attempt_rec.administrative_priority,
1138 X_WAITLIST_DT => c_unit_attempt_rec.waitlist_dt,
1139 X_DCNT_REASON_CD => p_dcnt_reason_cd,
1140 X_MODE => 'R',
1141 X_GS_VERSION_NUMBER => c_unit_attempt_rec.gs_version_number,
1142 X_ENR_METHOD_TYPE => c_unit_attempt_rec.enr_method_type,
1143 X_FAILED_UNIT_RULE => c_unit_attempt_rec.failed_unit_rule,
1144 X_CART => c_unit_attempt_rec.cart,
1145 X_RSV_SEAT_EXT_ID => c_unit_attempt_rec.rsv_seat_ext_id,
1146 X_ORG_UNIT_CD => c_unit_attempt_rec.org_unit_cd,
1147 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1148 X_SESSION_ID => c_unit_attempt_rec.session_id,
1149 -- Added the column grading schema code as a part of the bug 2037897. - aiyer
1150 X_GRADING_SCHEMA_CODE => c_unit_attempt_rec.grading_schema_code,
1151 --Added the column deg_aud_detail_id as part of Degree Audit Interface build. Bug# 2033208 - pradhakr
1152 X_DEG_AUD_DETAIL_ID => c_unit_attempt_rec.deg_aud_detail_id,
1153 X_SUBTITLE => c_unit_attempt_rec.subtitle,
1154 --Added the columns student_career_transcript,student_career_statistics as part of Career Impact DLD
1155 -- part 2 . Bug # svenkata
1156 X_STUDENT_CAREER_TRANSCRIPT => c_unit_attempt_rec.student_career_transcript,
1157 X_STUDENT_CAREER_STATISTICS => c_unit_attempt_rec.student_career_statistics,
1158 X_ATTRIBUTE_CATEGORY => c_unit_attempt_rec.attribute_category,
1159 X_ATTRIBUTE1 => c_unit_attempt_rec.attribute1,
1160 X_ATTRIBUTE2 => c_unit_attempt_rec.attribute2,
1161 X_ATTRIBUTE3 => c_unit_attempt_rec.attribute3,
1162 X_ATTRIBUTE4 => c_unit_attempt_rec.attribute4,
1163 X_ATTRIBUTE5 => c_unit_attempt_rec.attribute5,
1164 X_ATTRIBUTE6 => c_unit_attempt_rec.attribute6,
1165 X_ATTRIBUTE7 => c_unit_attempt_rec.attribute7,
1166 X_ATTRIBUTE8 => c_unit_attempt_rec.attribute8,
1167 X_ATTRIBUTE9 => c_unit_attempt_rec.attribute9,
1168 X_ATTRIBUTE10 => c_unit_attempt_rec.attribute10,
1169 X_ATTRIBUTE11 => c_unit_attempt_rec.attribute11,
1170 X_ATTRIBUTE12 => c_unit_attempt_rec.attribute12,
1171 X_ATTRIBUTE13 => c_unit_attempt_rec.attribute13,
1172 X_ATTRIBUTE14 => c_unit_attempt_rec.attribute14,
1173 X_ATTRIBUTE15 => c_unit_attempt_rec.attribute15,
1174 X_ATTRIBUTE16 => c_unit_attempt_rec.attribute16,
1175 X_ATTRIBUTE17 => c_unit_attempt_rec.attribute17,
1176 X_ATTRIBUTE18 => c_unit_attempt_rec.attribute18,
1177 X_ATTRIBUTE19 => c_unit_attempt_rec.attribute19,
1178 X_ATTRIBUTE20 => c_unit_attempt_rec.attribute20,
1179 X_WAITLIST_MANUAL_IND => c_unit_attempt_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109.
1180 X_WLST_PRIORITY_WEIGHT_NUM => c_unit_attempt_rec.wlst_priority_weight_num,
1181 X_WLST_PREFERENCE_WEIGHT_NUM=> c_unit_attempt_rec.wlst_preference_weight_num,
1182 -- CORE_INDICATOR_CODE --added by rvangala 07-OCT-2003. Enh Bug# 3052432
1183 X_CORE_INDICATOR_CODE => c_unit_attempt_rec.core_indicator_code
1184 );
1185 END IF;
1186
1187 IGS_SS_EN_WRAPPERS.call_fee_ass (
1188 p_person_id => p_person_id,
1189 p_cal_type => p_cal_type, -- load
1190 p_sequence_number => p_ci_sequence_number, -- load
1191 p_course_cd => c_unit_attempt_rec.course_cd,
1192 p_unit_cd => c_unit_attempt_rec.unit_cd,
1193 p_uoo_id => c_unit_attempt_rec.uoo_id
1194 );
1195
1196 END IF; --END of c_is_unit_exists%FOUND
1197 CLOSE c_is_unit_exists;
1198
1199 END LOOP;
1200
1201 END;
1202
1203 /* Removing the exception since the real error message is not getting propagated to the SS Drop Screens
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1207 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_004.Enrp_Dropall_Unit');
1208 IGS_GE_MSG_STACK.ADD;
1209 APP_EXCEPTION.RAISE_EXCEPTION;
1210 */
1211
1212 END enrp_dropall_unit;
1213
1214 END IGS_EN_GEN_004;
[Home] [Help]