[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_OFR_WLST_OPT
Source
1 PACKAGE BODY igs_en_ofr_wlst_opt as
2 /* $Header: IGSEN75B.pls 120.2 2006/01/16 22:42:18 smaddali ship $ */
3
4
5 EN_SUA_REC_TYPE IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
6
7 FUNCTION ofr_enrollment_or_waitlist ( p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
8 p_session_id IN igs_en_su_attempt.session_id%TYPE,
9 p_waitlist_ind IN VARCHAR2,
10 p_person_number IN igs_pe_person.person_number%TYPE,
11 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
12 p_enr_method_type IN igs_en_su_attempt.enr_method_type%TYPE,
13 p_deny_or_warn OUT NOCOPY VARCHAR2,
14 p_message OUT NOCOPY VARCHAR2,
15 p_cal_type IN igs_ca_inst.cal_type%TYPE,
16 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
17 p_audit_requested IN VARCHAR2,
18 p_override_cp IN NUMBER ,
19 p_subtitle IN VARCHAR2 ,
20 p_gradsch_cd IN VARCHAR2 ,
21 p_gs_version_num IN NUMBER,
22 p_core_indicator_code IN VARCHAR2,
23 p_calling_obj IN VARCHAR2
24 ) RETURN BOOLEAN IS
25 ------------------------------------------------------------------
26 --Created by : Sanil Madathil, Oracle IDC
27 --Date created: 15-JUL-2001
28 --
29 --Purpose: This procedure validates the unit section selected by the student and place them in the
30 -- cart or move them into waitlist .
31 --
32 --
33 --
34 --Known limitations/enhancements and/or remarks:
35 --
36 --Change History:
37 --Who When What
38 --myoganat 26-May-2003 Removed the code setting the override achievable CP as zero
39 -- in case of an audit attempt - (Bug# 2855870)
40 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to
41 -- IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW and IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW
42 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
43 -------------------------------------------------------------------
44
45 l_cst_unconfirm CONSTANT igs_ps_unit_ofr_opt.unit_section_status%TYPE DEFAULT 'UNCONFIRM';
46 l_cst_waitlisted CONSTANT igs_ps_unit_ofr_opt.unit_section_status%TYPE DEFAULT 'WAITLISTED';
47
48
49 -- cursor which fetches unit offering option details based on the id passed as a parameter to it
50 CURSOR c_igs_ps_unit_ofr_opt(cp_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
51 SELECT unit_cd , version_number , uoo.cal_type ,
52 uoo.ci_sequence_number , location_cd , unit_class , start_dt ,
53 end_dt , uoo.row_id , uoo.ivrs_available_ind ,
54 uoo.call_number , uoo.unit_section_status , uoo.unit_section_start_date ,
55 uoo.unit_section_end_date , uoo.enrollment_actual , uoo.waitlist_actual ,
56 uoo.offered_ind , uoo.state_financial_aid , uoo.grading_schema_prcdnce_ind ,
57 uoo.federal_financial_aid , uoo.unit_quota , uoo.unit_quota_reserved_places ,
58 uoo.institutional_financial_aid , uoo.unit_contact , uoo.grading_schema_cd ,
59 uoo.gs_version_number , uoo.owner_org_unit_cd , uoo.attendance_required_ind ,
60 uoo.reserved_seating_allowed , uoo.special_permission_ind , uoo.ss_display_ind ,
61 uoo.ss_enrol_ind , uoo.dir_enrollment , uoo.enr_from_wlst ,
62 uoo.inq_not_wlst , uoo.anon_unit_grading_ind , uoo.anon_assess_grading_ind ,
63 uoo.rev_account_cd , uoo.non_std_usec_ind , uoo.auditable_ind ,
64 uoo.audit_permission_ind
65 FROM igs_ps_unit_ofr_opt uoo, igs_ca_inst ci
66 WHERE uoo.cal_type = ci.cal_type
67 AND uoo.ci_sequence_number = ci.sequence_number
68 AND uoo_id = cp_uoo_id ;
69
70 -- local rowtype variable for above cursor
71 l_c_igs_ps_unit_ofr_opt c_igs_ps_unit_ofr_opt%ROWTYPE ;
72
73 -- cursor which picks up person id corresponding to person number passed as parameter
74 CURSOR c_igs_pe_person(cp_person_number igs_pe_person.person_number%TYPE) IS
75 SELECT party_id
76 FROM hz_parties
77 WHERE party_number = cp_person_number ;
78
79 -- local rowtype variable for above cursor
80 l_c_igs_pe_person c_igs_pe_person%ROWTYPE ;
81 -- local variables
82 l_rowid igs_en_su_attempt.row_id%TYPE ;
83 l_person_id igs_pe_person.person_id%TYPE ;
84 l_result BOOLEAN DEFAULT FALSE ;
85 l_deny_or_warn_flag VARCHAR2(1000) ;
86 l_ret_flag BOOLEAN DEFAULT FALSE ;
87 l_rsv_seat_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE ;
88 l_message VARCHAR2(32767) DEFAULT NULL ;
89 l_message_name VARCHAR2(32767) DEFAULT NULL ;
90 l_app_short_name VARCHAR2(1000) DEFAULT 'IGS' ;
91
92 -- cursor which picks up all the details from reserved seat utilisation table
93 CURSOR c_igs_ps_rsv_ext(cp_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE) IS
94 SELECT rowid , rsv_ext_id , uoo_id , priority_id ,
95 preference_id , rsv_level , actual_seat_enrolled , created_by ,
96 creation_date , last_updated_by , last_update_date , last_update_login
97 FROM igs_ps_rsv_ext
98 WHERE rsv_ext_id = cp_rsv_ext_id ;
99
100 -- local rowtype variable for above cursor
101 l_c_igs_ps_rsv_ext c_igs_ps_rsv_ext%ROWTYPE ;
102
103 CURSOR c_igs_en_su_attempt(cp_rowid VARCHAR2) IS
104 SELECT su.*
105 FROM igs_en_su_attempt_all su
106 WHERE su.rowid = cp_rowid ;
107
108 l_c_igs_en_su_attempt c_igs_en_su_attempt%ROWTYPE ;
109 l_override_achievable_cp igs_en_su_attempt.override_achievable_cp%TYPE;
110 old_references EN_SUA_REC_TYPE%TYPE;
111 new_references EN_SUA_REC_TYPE%TYPE;
112 l_ss_src_ind igs_en_su_attempt.ss_source_ind%TYPE;
113
114 -- cursor to get person type
115 CURSOR cur_per_typ IS
116 SELECT person_type_code
117 FROM igs_pe_person_types
118 WHERE system_type = 'OTHER';
119 l_cur_per_typ cur_per_typ%ROWTYPE;
120 lv_person_type igs_pe_person_types.person_type_code%TYPE;
121
122 -- cursor tp get system person type
123 CURSOR cur_sys_pers_type(cp_person_type_code VARCHAR2) IS
124 SELECT system_type
125 FROM igs_pe_person_types
126 WHERE person_type_code = cp_person_type_code;
127
128 l_sys_per_type igs_pe_person_types.system_type%TYPE;
129
130 BEGIN
131 IGS_GE_MSG_STACK.INITIALIZE;
132 --fetch the person id corresponding to person number passed as parameter
133 OPEN c_igs_pe_person( cp_person_number => p_person_number) ;
134 FETCH c_igs_pe_person INTO l_c_igs_pe_person ;
135 --person number passed as parameter does not exist
136 IF c_igs_pe_person%NOTFOUND THEN
137 CLOSE c_igs_pe_person ;
138 p_message := 'IGS_PE_PERS_NOT_EXIST' ;
139 RETURN (FALSE) ;
140 END IF;
141 l_person_id := l_c_igs_pe_person.party_id ;
142 CLOSE c_igs_pe_person ;
143
144 -- fetch the uoo id details
145 OPEN c_igs_ps_unit_ofr_opt( p_uoo_id) ;
146 FETCH c_igs_ps_unit_ofr_opt INTO l_c_igs_ps_unit_ofr_opt ;
147 IF c_igs_ps_unit_ofr_opt%NOTFOUND THEN
148 CLOSE c_igs_ps_unit_ofr_opt ;
149 p_message := 'IGS_EN_UOO_NOT_EXIST' ;
150 RETURN (FALSE) ;
151 END IF;
152 CLOSE c_igs_ps_unit_ofr_opt ;
153
154 OPEN cur_per_typ;
155 FETCH cur_per_typ INTO l_cur_per_typ;
156 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(p_course_cd),l_cur_per_typ.person_type_code);
157 CLOSE cur_per_typ;
158
159 OPEN cur_sys_pers_type(lv_person_type);
160 FETCH cur_sys_pers_type INTO l_sys_per_type;
161 CLOSE cur_sys_pers_type;
162
163 -- set the source indicator flag
164 IF p_calling_obj = 'JOB' THEN
165
166 l_ss_src_ind := 'A';
167
168 ELSIF p_calling_obj IN ('PLAN','SUBMITPLAN') THEN
169
170 l_ss_src_ind := 'P';
171
172 ELSIF p_calling_obj IN ('SWAP','SUBMITSWAP') THEN
173
174 l_ss_src_ind := 'S';
175
176 ELSIF p_calling_obj IN ('CART','SUBMITCART','SCHEDULE','ENROLPEND') THEN
177
178 IF l_sys_per_type = 'STUDENT' THEN
179 l_ss_src_ind := 'N';
180 ELSE
181 l_ss_src_ind := 'A';
182 END IF;
183
184 END IF;
185
186 FOR l_c_igs_ps_unit_ofr_opt IN c_igs_ps_unit_ofr_opt(cp_uoo_id => p_uoo_id)
187 LOOP
188 -- creates a record in the table igs_en_su_attempt with unit_attempt_status = unconfirm
189 igs_en_su_attempt_pkg.insert_row (
190 X_ROWID => l_rowid ,
191 X_PERSON_ID => l_person_id ,
192 X_COURSE_CD => p_course_cd ,
193 X_UNIT_CD => l_c_igs_ps_unit_ofr_opt.unit_cd ,
194 X_CAL_TYPE => l_c_igs_ps_unit_ofr_opt.cal_type ,
195 X_CI_SEQUENCE_NUMBER => l_c_igs_ps_unit_ofr_opt.ci_sequence_number ,
196 X_VERSION_NUMBER => l_c_igs_ps_unit_ofr_opt.version_number ,
197 X_LOCATION_CD => l_c_igs_ps_unit_ofr_opt.location_cd ,
198 X_UNIT_CLASS => l_c_igs_ps_unit_ofr_opt.unit_class ,
199 X_CI_START_DT => l_c_igs_ps_unit_ofr_opt.start_dt ,
200 X_CI_END_DT => l_c_igs_ps_unit_ofr_opt.end_dt ,
201 X_UOO_ID => p_uoo_id ,
202 X_ENROLLED_DT => NULL ,
203 X_UNIT_ATTEMPT_STATUS => l_cst_unconfirm ,
204 X_ADMINISTRATIVE_UNIT_STATUS => NULL ,
205 X_DISCONTINUED_DT => NULL ,
206 X_RULE_WAIVED_DT => NULL ,
207 X_RULE_WAIVED_PERSON_ID => NULL ,
208 X_NO_ASSESSMENT_IND => NVL(p_audit_requested,'N') , -- value passed to indicate that audit is requeted or not
209 X_SUP_UNIT_CD => NULL ,
210 X_SUP_VERSION_NUMBER => NULL ,
211 X_EXAM_LOCATION_CD => NULL ,
212 X_ALTERNATIVE_TITLE => p_subtitle ,
213 X_OVERRIDE_ENROLLED_CP => p_override_cp ,
214 X_OVERRIDE_EFTSU => NULL ,
215 X_OVERRIDE_ACHIEVABLE_CP => l_override_achievable_cp , -- selective values passed based on whether audit is requeted or not
216 X_OVERRIDE_OUTCOME_DUE_DT => NULL ,
217 X_OVERRIDE_CREDIT_REASON => NULL ,
218 X_ADMINISTRATIVE_PRIORITY => NULL ,
219 X_WAITLIST_DT => NULL ,
220 X_DCNT_REASON_CD => NULL ,
221 X_MODE => 'R' ,
222 X_ORG_ID => TO_NUMBER(FND_PROFILE.VALUE('ORG_ID')) ,
223 X_GS_VERSION_NUMBER => p_gs_version_num ,
224 X_ENR_METHOD_TYPE => p_enr_method_type ,
225 X_FAILED_UNIT_RULE => NULL ,
226 X_CART => NULL ,
227 X_RSV_SEAT_EXT_ID => NULL ,
228 X_ORG_UNIT_CD => NULL ,
229 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
230 X_SESSION_ID => p_session_id,
231 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
232 X_GRADING_SCHEMA_CODE => p_gradsch_cd ,
233 --Added the column Deg_Aud_Detail_Id as part of Degree Audit Interface build. (Bug# 2033208) - pradhakr
234 X_DEG_AUD_DETAIL_ID => NULL,
235 X_STUDENT_CAREER_TRANSCRIPT => NULL ,
236 X_STUDENT_CAREER_STATISTICS => NULL,
237 X_ATTRIBUTE_CATEGORY => NULL,
238 X_ATTRIBUTE1 => NULL,
239 X_ATTRIBUTE2 => NULL,
240 X_ATTRIBUTE3 => NULL,
241 X_ATTRIBUTE4 => NULL,
242 X_ATTRIBUTE5 => NULL,
243 X_ATTRIBUTE6 => NULL,
244 X_ATTRIBUTE7 => NULL,
245 X_ATTRIBUTE8 => NULL,
246 X_ATTRIBUTE9 => NULL,
247 X_ATTRIBUTE10 => NULL,
248 X_ATTRIBUTE11 => NULL,
249 X_ATTRIBUTE12 => NULL,
250 X_ATTRIBUTE13 => NULL,
251 X_ATTRIBUTE14 => NULL,
252 X_ATTRIBUTE15 => NULL,
253 X_ATTRIBUTE16 => NULL,
254 X_ATTRIBUTE17 => NULL,
255 X_ATTRIBUTE18 => NULL,
256 X_ATTRIBUTE19 => NULL,
257 X_ATTRIBUTE20 => NULL,
258 X_WAITLIST_MANUAL_IND => 'N', --Added by mesriniv for Bug 2554109 Mini Waitlist Build.,
259 X_WLST_PRIORITY_WEIGHT_NUM => NULL,
260 X_WLST_PREFERENCE_WEIGHT_NUM => NULL,
261 X_CORE_INDICATOR_CODE => p_core_indicator_code,
262 X_UPD_AUDIT_FLAG => 'N',
263 X_SS_SOURCE_IND => l_ss_src_ind
264
265 );
266 -- validate_combined_unit is called here
270 p_unit_cd => l_c_igs_ps_unit_ofr_opt.unit_cd ,
267 l_result := igs_en_enroll_wlst.validate_combined_unit
268 (
269 p_person_id => l_person_id ,
271 p_version_number => l_c_igs_ps_unit_ofr_opt.version_number ,
272 p_cal_type => p_cal_type , -- load calendar
273 p_ci_sequence_number => p_ci_sequence_number , -- load calendar
274 p_location_cd => l_c_igs_ps_unit_ofr_opt.location_cd ,
275 p_unit_class => l_c_igs_ps_unit_ofr_opt.unit_class ,
276 p_uoo_id => p_uoo_id ,
277 p_course_cd => p_course_cd ,
278 p_enr_method_type => p_enr_method_type ,
279 p_message_name => p_message ,
280 p_deny_warn => l_deny_or_warn_flag,
281 p_calling_obj => p_calling_obj
282 ) ;
283 p_deny_or_warn := l_deny_or_warn_flag;
284 -- If all the validations are successful and waitlist indicator = 'N'
285 OPEN c_igs_en_su_attempt(l_rowid);
286 FETCH c_igs_en_su_attempt INTO l_c_igs_en_su_attempt ;
287 CLOSE c_igs_en_su_attempt ;
288
289 old_references := l_c_igs_en_su_attempt ;
290
291 IF (l_result AND p_waitlist_ind = 'N') THEN
292
293 -- update sua record with cart = 'y'
294 igs_en_su_attempt_pkg.update_row (
295 X_ROWID => l_rowid ,
296 X_PERSON_ID => l_c_igs_en_su_attempt.person_id ,
297 X_COURSE_CD => l_c_igs_en_su_attempt.course_cd ,
298 X_UNIT_CD => l_c_igs_en_su_attempt.unit_cd ,
299 X_CAL_TYPE => l_c_igs_en_su_attempt.cal_type ,
300 X_CI_SEQUENCE_NUMBER => l_c_igs_en_su_attempt.ci_sequence_number ,
301 X_VERSION_NUMBER => l_c_igs_en_su_attempt.version_number ,
302 X_LOCATION_CD => l_c_igs_en_su_attempt.location_cd ,
303 X_UNIT_CLASS => l_c_igs_en_su_attempt.unit_class ,
304 X_CI_START_DT => l_c_igs_en_su_attempt.ci_start_dt ,
305 X_CI_END_DT => l_c_igs_en_su_attempt.ci_end_dt ,
306 X_UOO_ID => l_c_igs_en_su_attempt.uoo_id ,
307 X_ENROLLED_DT => l_c_igs_en_su_attempt.enrolled_dt ,
308 X_UNIT_ATTEMPT_STATUS => l_c_igs_en_su_attempt.unit_attempt_status ,
309 X_ADMINISTRATIVE_UNIT_STATUS => l_c_igs_en_su_attempt.administrative_unit_status ,
310 X_DISCONTINUED_DT => l_c_igs_en_su_attempt.discontinued_dt ,
311 X_RULE_WAIVED_DT => l_c_igs_en_su_attempt.rule_waived_dt ,
312 X_RULE_WAIVED_PERSON_ID => l_c_igs_en_su_attempt.rule_waived_person_id ,
313 X_NO_ASSESSMENT_IND => l_c_igs_en_su_attempt.no_assessment_ind ,
314 X_SUP_UNIT_CD => l_c_igs_en_su_attempt.sup_unit_cd ,
315 X_SUP_VERSION_NUMBER => l_c_igs_en_su_attempt.sup_version_number ,
316 X_EXAM_LOCATION_CD => l_c_igs_en_su_attempt.exam_location_cd ,
317 X_ALTERNATIVE_TITLE => l_c_igs_en_su_attempt.alternative_title ,
318 X_OVERRIDE_ENROLLED_CP => l_c_igs_en_su_attempt.override_enrolled_cp ,
319 X_OVERRIDE_EFTSU => l_c_igs_en_su_attempt.override_eftsu ,
320 X_OVERRIDE_ACHIEVABLE_CP => l_c_igs_en_su_attempt.override_achievable_cp ,
321 X_OVERRIDE_OUTCOME_DUE_DT => l_c_igs_en_su_attempt.override_outcome_due_dt ,
322 X_OVERRIDE_CREDIT_REASON => l_c_igs_en_su_attempt.override_credit_reason ,
323 X_ADMINISTRATIVE_PRIORITY => l_c_igs_en_su_attempt.administrative_priority ,
327 X_GS_VERSION_NUMBER => l_c_igs_en_su_attempt.gs_version_number ,
324 X_WAITLIST_DT => l_c_igs_en_su_attempt.waitlist_dt ,
325 X_DCNT_REASON_CD => l_c_igs_en_su_attempt.dcnt_reason_cd ,
326 X_MODE => 'R' ,
328 X_ENR_METHOD_TYPE => l_c_igs_en_su_attempt.enr_method_type ,
329 X_FAILED_UNIT_RULE => l_c_igs_en_su_attempt.failed_unit_rule ,
330 X_CART => substr(igs_en_gen_017.enrp_get_invoke_source,1,1),
331 X_RSV_SEAT_EXT_ID => l_c_igs_en_su_attempt.rsv_seat_ext_id ,
332 X_ORG_UNIT_CD => l_c_igs_en_su_attempt.org_unit_cd ,
333 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
334 X_SESSION_ID => l_c_igs_en_su_attempt.session_id,
335 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
336 X_GRADING_SCHEMA_CODE => l_c_igs_en_su_attempt.grading_schema_code ,
337 --Added the column Deg_Aud_Detail_Id as part of Degree Audit Interface build. (Bug# 2033208)- pradhakr
338 X_DEG_AUD_DETAIL_ID => l_c_igs_en_su_attempt.deg_aud_detail_id ,
339 X_STUDENT_CAREER_TRANSCRIPT => l_c_igs_en_su_attempt.student_career_transcript,
340 X_STUDENT_CAREER_STATISTICS => l_c_igs_en_su_attempt.student_career_statistics,
341 X_ATTRIBUTE_CATEGORY => l_c_igs_en_su_attempt.attribute_category,
342 X_ATTRIBUTE1 => l_c_igs_en_su_attempt.attribute1,
343 X_ATTRIBUTE2 => l_c_igs_en_su_attempt.attribute2,
344 X_ATTRIBUTE3 => l_c_igs_en_su_attempt.attribute3,
345 X_ATTRIBUTE4 => l_c_igs_en_su_attempt.attribute4,
346 X_ATTRIBUTE5 => l_c_igs_en_su_attempt.attribute5,
347 X_ATTRIBUTE6 => l_c_igs_en_su_attempt.attribute6,
348 X_ATTRIBUTE7 => l_c_igs_en_su_attempt.attribute7,
349 X_ATTRIBUTE8 => l_c_igs_en_su_attempt.attribute8,
350 X_ATTRIBUTE9 => l_c_igs_en_su_attempt.attribute9,
351 X_ATTRIBUTE10 => l_c_igs_en_su_attempt.attribute10,
352 X_ATTRIBUTE11 => l_c_igs_en_su_attempt.attribute11,
353 X_ATTRIBUTE12 => l_c_igs_en_su_attempt.attribute12,
354 X_ATTRIBUTE13 => l_c_igs_en_su_attempt.attribute13,
355 X_ATTRIBUTE14 => l_c_igs_en_su_attempt.attribute14,
356 X_ATTRIBUTE15 => l_c_igs_en_su_attempt.attribute15,
357 X_ATTRIBUTE16 => l_c_igs_en_su_attempt.attribute16,
358 X_ATTRIBUTE17 => l_c_igs_en_su_attempt.attribute17,
359 X_ATTRIBUTE18 => l_c_igs_en_su_attempt.attribute18,
360 X_ATTRIBUTE19 => l_c_igs_en_su_attempt.attribute19,
361 X_ATTRIBUTE20 => l_c_igs_en_su_attempt.attribute20,
362 X_WAITLIST_MANUAL_IND => l_c_igs_en_su_attempt.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.,
363 X_WLST_PRIORITY_WEIGHT_NUM => l_c_igs_en_su_attempt.wlst_priority_weight_num,
364 X_WLST_PREFERENCE_WEIGHT_NUM => l_c_igs_en_su_attempt.wlst_preference_weight_num,
365 X_CORE_INDICATOR_CODE => l_c_igs_en_su_attempt.core_indicator_code,
366 X_UPD_AUDIT_FLAG => l_c_igs_en_su_attempt.upd_audit_flag,
367 X_SS_SOURCE_IND => l_c_igs_en_su_attempt.ss_source_ind
368 ) ;
369 l_ret_flag := TRUE ;
370
371 OPEN c_igs_en_su_attempt(l_rowid);
372 FETCH c_igs_en_su_attempt INTO new_references;
373 CLOSE c_igs_en_su_attempt ;
374
375 igs_en_sua_api.upd_enrollment_counts('UPDATE',
376 old_references,
377 new_references);
378
379
380 -- if validations are successful and waitlist indicator = 'Y' or validations fail and deny_warn_flag = 'warn' and waitlist indicator = 'Y'
381 ELSIF ((l_result AND p_waitlist_ind = 'Y') OR (NOT l_result AND l_deny_or_warn_flag = 'WARN' AND p_waitlist_ind = 'Y' )) THEN
385 X_ROWID => l_rowid ,
382 -- update sua record with cart = 'n' and unit_attempt_status = 'waitlisted'
383
384 igs_en_su_attempt_pkg.update_row (
386 X_PERSON_ID => l_c_igs_en_su_attempt.person_id ,
387 X_COURSE_CD => l_c_igs_en_su_attempt.course_cd ,
388 X_UNIT_CD => l_c_igs_en_su_attempt.unit_cd ,
389 X_CAL_TYPE => l_c_igs_en_su_attempt.cal_type ,
390 X_CI_SEQUENCE_NUMBER => l_c_igs_en_su_attempt.ci_sequence_number ,
391 X_VERSION_NUMBER => l_c_igs_en_su_attempt.version_number ,
392 X_LOCATION_CD => l_c_igs_en_su_attempt.location_cd ,
393 X_UNIT_CLASS => l_c_igs_en_su_attempt.unit_class ,
394 X_CI_START_DT => l_c_igs_en_su_attempt.ci_start_dt ,
395 X_CI_END_DT => l_c_igs_en_su_attempt.ci_end_dt ,
396 X_UOO_ID => l_c_igs_en_su_attempt.uoo_id ,
397 X_ENROLLED_DT => l_c_igs_en_su_attempt.enrolled_dt ,
398 X_UNIT_ATTEMPT_STATUS => l_cst_waitlisted ,
399 X_ADMINISTRATIVE_UNIT_STATUS => l_c_igs_en_su_attempt.administrative_unit_status ,
400 X_DISCONTINUED_DT => l_c_igs_en_su_attempt.discontinued_dt ,
401 X_RULE_WAIVED_DT => l_c_igs_en_su_attempt.rule_waived_dt ,
402 X_RULE_WAIVED_PERSON_ID => l_c_igs_en_su_attempt.rule_waived_person_id ,
403 X_NO_ASSESSMENT_IND => l_c_igs_en_su_attempt.no_assessment_ind ,
404 X_SUP_UNIT_CD => l_c_igs_en_su_attempt.sup_unit_cd ,
405 X_SUP_VERSION_NUMBER => l_c_igs_en_su_attempt.sup_version_number ,
406 X_EXAM_LOCATION_CD => l_c_igs_en_su_attempt.exam_location_cd ,
407 X_ALTERNATIVE_TITLE => l_c_igs_en_su_attempt.alternative_title ,
408 X_OVERRIDE_ENROLLED_CP => l_c_igs_en_su_attempt.override_enrolled_cp ,
409 X_OVERRIDE_EFTSU => l_c_igs_en_su_attempt.override_eftsu ,
410 X_OVERRIDE_ACHIEVABLE_CP => l_c_igs_en_su_attempt.override_achievable_cp ,
411 X_OVERRIDE_OUTCOME_DUE_DT => l_c_igs_en_su_attempt.override_outcome_due_dt ,
412 X_OVERRIDE_CREDIT_REASON => l_c_igs_en_su_attempt.override_credit_reason ,
413 X_ADMINISTRATIVE_PRIORITY => l_c_igs_en_su_attempt.administrative_priority ,
414 X_WAITLIST_DT => SYSDATE, --l_c_igs_en_su_attempt.waitlist_dt , -- modification done as per the Bug# 2335455
415 X_DCNT_REASON_CD => l_c_igs_en_su_attempt.dcnt_reason_cd ,
416 X_MODE => 'R' ,
417 X_GS_VERSION_NUMBER => l_c_igs_en_su_attempt.gs_version_number ,
418 X_ENR_METHOD_TYPE => l_c_igs_en_su_attempt.enr_method_type ,
419 X_FAILED_UNIT_RULE => l_c_igs_en_su_attempt.failed_unit_rule ,
420 X_CART => 'N' ,
421 X_RSV_SEAT_EXT_ID => l_c_igs_en_su_attempt.rsv_seat_ext_id ,
422 X_ORG_UNIT_CD => l_c_igs_en_su_attempt.org_unit_cd ,
423 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
424 X_SESSION_ID => l_c_igs_en_su_attempt.session_id,
425 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
426 X_GRADING_SCHEMA_CODE => l_c_igs_en_su_attempt.grading_schema_code ,
427 --Added the column Deg_Aud_Detail_Id as part of Degree Audit Interface build. (Bug# 2033208)- pradhakr
428 X_DEG_AUD_DETAIL_ID => l_c_igs_en_su_attempt.deg_aud_detail_id,
429 X_STUDENT_CAREER_TRANSCRIPT => l_c_igs_en_su_attempt.student_career_transcript,
433 X_ATTRIBUTE2 => l_c_igs_en_su_attempt.attribute2,
430 X_STUDENT_CAREER_STATISTICS => l_c_igs_en_su_attempt.student_career_statistics,
431 X_ATTRIBUTE_CATEGORY => l_c_igs_en_su_attempt.attribute_category,
432 X_ATTRIBUTE1 => l_c_igs_en_su_attempt.attribute1,
434 X_ATTRIBUTE3 => l_c_igs_en_su_attempt.attribute3,
435 X_ATTRIBUTE4 => l_c_igs_en_su_attempt.attribute4,
436 X_ATTRIBUTE5 => l_c_igs_en_su_attempt.attribute5,
437 X_ATTRIBUTE6 => l_c_igs_en_su_attempt.attribute6,
438 X_ATTRIBUTE7 => l_c_igs_en_su_attempt.attribute7,
439 X_ATTRIBUTE8 => l_c_igs_en_su_attempt.attribute8,
440 X_ATTRIBUTE9 => l_c_igs_en_su_attempt.attribute9,
441 X_ATTRIBUTE10 => l_c_igs_en_su_attempt.attribute10,
442 X_ATTRIBUTE11 => l_c_igs_en_su_attempt.attribute11,
443 X_ATTRIBUTE12 => l_c_igs_en_su_attempt.attribute12,
444 X_ATTRIBUTE13 => l_c_igs_en_su_attempt.attribute13,
445 X_ATTRIBUTE14 => l_c_igs_en_su_attempt.attribute14,
446 X_ATTRIBUTE15 => l_c_igs_en_su_attempt.attribute15,
447 X_ATTRIBUTE16 => l_c_igs_en_su_attempt.attribute16,
448 X_ATTRIBUTE17 => l_c_igs_en_su_attempt.attribute17,
449 X_ATTRIBUTE18 => l_c_igs_en_su_attempt.attribute18,
450 X_ATTRIBUTE19 => l_c_igs_en_su_attempt.attribute19,
451 X_ATTRIBUTE20 => l_c_igs_en_su_attempt.attribute20,
452 X_WAITLIST_MANUAL_IND => l_c_igs_en_su_attempt.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
453 X_WLST_PRIORITY_WEIGHT_NUM => l_c_igs_en_su_attempt.wlst_priority_weight_num,
454 X_WLST_PREFERENCE_WEIGHT_NUM => l_c_igs_en_su_attempt.wlst_preference_weight_num,
455 X_CORE_INDICATOR_CODE => l_c_igs_en_su_attempt.core_indicator_code,
456 X_UPD_AUDIT_FLAG => l_c_igs_en_su_attempt.upd_audit_flag,
457 X_SS_SOURCE_IND => l_c_igs_en_su_attempt.ss_source_ind
458 ) ;
459 --increase the waitlist actual by 1
460 /* the waitlist actual will be incremented through the TBH of IGS_EN_SU_ATTEMPT
461 this has been done to centralize the updation of all the statistics and status related columns */
462
463 p_deny_or_warn := l_deny_or_warn_flag;
464 l_ret_flag := TRUE;
465
466 -- since a waitlisetd unit could contribute to the fee we need
467 -- to create a TODO record to recalculate the fee when a waitlisted unit
468 -- is added. The unit would contribute towards the CP or fee based on the
469 -- profile IGS_EN_INCL_WLST_CP
470 IGS_SS_EN_WRAPPERS.call_fee_ass (
471 p_person_id => l_person_id,
472 p_cal_type => p_cal_type, -- load
473 p_sequence_number => p_ci_sequence_number, -- load
474 p_course_cd => l_c_igs_en_su_attempt.course_cd,
475 p_unit_cd => l_c_igs_en_su_attempt.unit_cd,
476 p_uoo_id => l_c_igs_en_su_attempt.uoo_id
477 );
478
479
480 OPEN c_igs_en_su_attempt(l_rowid);
481 FETCH c_igs_en_su_attempt INTO new_references;
482 CLOSE c_igs_en_su_attempt ;
483
484 igs_en_sua_api.upd_enrollment_counts( 'UPDATE',
485 old_references,
486 new_references);
487
488
489 ELSIF ( NOT l_result AND l_deny_or_warn_flag = 'DENY' ) THEN
490 -- update igs_ps_rsv_ext only when l_c_igs_en_su_attempt.rsv_seat_ext_id is not null
491 IF l_c_igs_en_su_attempt.rsv_seat_ext_id IS NOT NULL THEN
492 OPEN c_igs_ps_rsv_ext(cp_rsv_ext_id => l_c_igs_en_su_attempt.rsv_seat_ext_id ) ;
493 FETCH c_igs_ps_rsv_ext INTO l_c_igs_ps_rsv_ext ;
494 igs_ps_rsv_ext_pkg.update_row(
495 x_rowid => l_c_igs_ps_rsv_ext.rowid ,
496 x_rsv_ext_id => l_c_igs_ps_rsv_ext.rsv_ext_id ,
497 x_uoo_id => l_c_igs_ps_rsv_ext.uoo_id ,
498 x_priority_id => l_c_igs_ps_rsv_ext.priority_id ,
499 x_preference_id => l_c_igs_ps_rsv_ext.preference_id ,
500 x_rsv_level => l_c_igs_ps_rsv_ext.rsv_level ,
501 x_actual_seat_enrolled => NVL(l_c_igs_ps_rsv_ext.actual_seat_enrolled,0) - 1 ,
502 x_mode => 'R'
503 );
504 CLOSE c_igs_ps_rsv_ext ;
505 END IF;
506
507 p_deny_or_warn := l_deny_or_warn_flag;
508 l_ret_flag := FALSE ;
509 END IF;
510
511 END LOOP ;
512 RETURN (l_ret_flag) ;
513 END ofr_enrollment_or_waitlist ;
514
515
516 END igs_en_ofr_wlst_opt ;