1 PACKAGE BODY igs_en_gen_017 AS
2 /* $Header: IGSEN96B.pls 120.3 2005/07/12 07:44:57 appldev ship $ */
3
4 /*************************************************************
5 Created By :
6 Date Created By :
7 Purpose :
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified add_to_cart_waitlist.
12 (reverse chronological order - newest change first)
13 ***************************************************************/
14
15 PROCEDURE add_to_cart_waitlist (
16 p_person_number IN VARCHAR2,
17 p_career IN VARCHAR2,
18 p_program_code IN VARCHAR2,
19 p_term_alt_code IN VARCHAR2,
20 p_call_number IN NUMBER,
21 p_audit_ind IN VARCHAR2,
22 p_waitlist_ind IN OUT NOCOPY VARCHAR2,
23 p_action IN VARCHAR2,
24 p_error_message OUT NOCOPY VARCHAR2,
25 p_ret_status OUT NOCOPY VARCHAR2) AS
26 /*
27 || Created By : Nishikant
28 || Created On : 23JAN2003
29 || Purpose : This procedure is called from the add_to_cart API and waitlist API.
30 || When called from the waitlist API it wailists a student.
31 || When called from add_to_cart API its checks for seat availability for the student.
32 || In case the seat is available then it adds the unit section to the student cart.
33 || In case the seat is not available but student can waitlist then it returns
34 || this information without adding to cart.
35 || In case student cannot waitlist then it returns the error message out.
36 ||
37 || Known limitations, enhancements or remarks :
38 || Change History :
39 || Who When What
40 || sgurusam 05-Jul-2005 Pass the new parameter p_calling_obj='JOB' in the calls to igs_ss_en_wrappers.insert_into_enr_worksheet
41 || Pass the new parameter p_calling_obj= 'JOB' in the calls to igs_ss_en_wrappers.drop_selected_units
42 || sommukhe 27-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row
43 || to include new parameter abort_flag.
44 || sarakshi 18-Sep-2003 Enh#3052452.Modified the call to igs_ps_unit_ofr_opt_pkg.update_row
45 || to include new parameter sup_uoo_id,relation_type,default_enroll_flag
46 || rvivekan 3-Aug-2003 Added new parameters to ofr_enrollment_or_waitlist |
47 as a part of Bulk Unit Upload Bug#3049009
48 || vvutukur 05-aug-2003 Enh#3045069.PSP Enh Build. Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to
49 || include new parameter not_multiple_section_flag.
50 || svanukur 16-jun-2003 implemented the check for unit section status of 'NOT_OFFERED'
51 || as part of validation impact CR ENCR034.
52 || (reverse chronological order - newest change first)
53 */
54 l_person_id igs_pe_person_base_v.person_id%TYPE;
55 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
56 l_cal_type igs_ca_inst.cal_type%TYPE;
57 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
58 l_primary_code igs_ps_ver.course_cd%TYPE;
59 l_primary_version igs_ps_ver.version_number%TYPE;
60 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
61 l_us_status igs_en_su_attempt.unit_attempt_status%TYPE;
62 l_ret_status VARCHAR2(6):='TRUE';
63 l_waitlist_ind VARCHAR2(1);
64 l_message_count NUMBER;
65 l_message_data VARCHAR2(2000) := NULL;
66 l_message_data_out VARCHAR2(2000) := NULL;
67 l_next_step VARCHAR2(1);
68
69 CURSOR c_chk_us_ivr IS
70 SELECT ivrs_available_ind, unit_section_status, auditable_ind
71 FROM igs_ps_unit_ofr_opt
72 WHERE uoo_id = l_uoo_id;
73
74 l_ivrs_available_ind igs_ps_unit_ofr_opt.ivrs_available_ind%TYPE;
75 l_unit_section_status igs_ps_unit_ofr_opt.unit_section_status%TYPE;
76 l_auditable_ind igs_ps_unit_ofr_opt.auditable_ind%TYPE;
77
78 CURSOR c_unit_ofr_opt IS
79 SELECT uoo.*
80 FROM igs_ps_unit_ofr_opt uoo
81 WHERE uoo_id = l_uoo_id;
82
83 l_unit_ofr_opt c_unit_ofr_opt%ROWTYPE;
84
85 BEGIN
86 p_ret_status := l_ret_status;
87
88 --Validate the input parameters and if valid, also fetch the internal calculated
89 --values. Pass the Validation level as With Call Number.
90 igs_en_gen_017.enrp_validate_input_parameters(
91 p_person_number => p_person_number,
92 p_career => p_career,
93 p_program_code => p_program_code,
94 p_term_alt_code => p_term_alt_code,
95 p_call_number => p_call_number,
96 p_validation_level => 'WITHCALLNUM',
97 p_person_id => l_person_id,
98 p_person_type => l_person_type,
99 p_cal_type => l_cal_type,
100 p_ci_sequence_number => l_ci_sequence_number,
101 p_primary_code => l_primary_code,
102 p_primary_version => l_primary_version,
103 p_uoo_id => l_uoo_id,
104 p_error_message => l_message_data,
105 p_ret_status => l_ret_status );
106
107 --If there is any invalid parameter then log it and return with error status
108 IF l_ret_status = 'FALSE' THEN
109 igs_en_gen_017.enrp_msg_string_to_list (
110 p_message_string => l_message_data,
111 p_delimiter => ';',
112 p_init_msg_list => FND_API.G_FALSE,
113 x_message_count => l_message_count,
114 x_message_data => l_message_data_out);
115 p_error_message := l_message_data_out;
116 p_ret_status := 'FALSE';
117 RETURN;
118 END IF;
119
120 --If p_action is "WLIST" and the waitlist indicator parameter is 'N' then update the
121 --inquired but not waitlisted counter (INQ_NOT_WLST column) of the unit section
122 --with increasing by one.
123 IF p_action = 'WLIST' AND p_waitlist_ind = 'N' THEN
124
125 OPEN c_unit_ofr_opt;
126 FETCH c_unit_ofr_opt INTO l_unit_ofr_opt;
127 CLOSE c_unit_ofr_opt;
128
129 igs_ps_unit_ofr_opt_pkg.update_row (
130 x_rowid => l_unit_ofr_opt.row_id,
131 x_unit_cd => l_unit_ofr_opt.unit_cd,
132 x_version_number => l_unit_ofr_opt.version_number,
133 x_cal_type => l_unit_ofr_opt.cal_type,
134 x_ci_sequence_number => l_unit_ofr_opt.ci_sequence_number,
135 x_location_cd => l_unit_ofr_opt.location_cd,
136 x_unit_class => l_unit_ofr_opt.unit_class,
137 x_uoo_id => l_unit_ofr_opt.uoo_id,
138 x_ivrs_available_ind => l_unit_ofr_opt.ivrs_available_ind,
139 x_call_number => l_unit_ofr_opt.call_number,
140 x_unit_section_status => l_unit_ofr_opt.unit_section_status,
141 x_unit_section_start_date => l_unit_ofr_opt.unit_section_start_date,
142 x_unit_section_end_date => l_unit_ofr_opt.unit_section_end_date,
143 x_enrollment_actual => l_unit_ofr_opt.enrollment_actual,
144 x_waitlist_actual => l_unit_ofr_opt.waitlist_actual,
145 x_offered_ind => l_unit_ofr_opt.offered_ind,
146 x_state_financial_aid => l_unit_ofr_opt.state_financial_aid,
147 x_grading_schema_prcdnce_ind => l_unit_ofr_opt.grading_schema_prcdnce_ind,
148 x_federal_financial_aid => l_unit_ofr_opt.federal_financial_aid,
149 x_unit_quota => l_unit_ofr_opt.unit_quota,
150 x_unit_quota_reserved_places => l_unit_ofr_opt.unit_quota_reserved_places,
151 x_institutional_financial_aid => l_unit_ofr_opt.institutional_financial_aid,
152 x_unit_contact => l_unit_ofr_opt.unit_contact,
153 x_grading_schema_cd => l_unit_ofr_opt.grading_schema_cd,
154 x_gs_version_number => l_unit_ofr_opt.gs_version_number,
155 x_owner_org_unit_cd => l_unit_ofr_opt.owner_org_unit_cd,
156 x_attendance_required_ind => l_unit_ofr_opt.attendance_required_ind,
157 x_reserved_seating_allowed => l_unit_ofr_opt.reserved_seating_allowed,
158 x_special_permission_ind => l_unit_ofr_opt.special_permission_ind,
159 x_ss_display_ind => l_unit_ofr_opt.ss_display_ind,
160 x_mode => 'R',
161 x_ss_enrol_ind => l_unit_ofr_opt.ss_enrol_ind,
162 x_dir_enrollment => l_unit_ofr_opt.dir_enrollment,
163 x_enr_from_wlst => l_unit_ofr_opt.enr_from_wlst,
164 x_inq_not_wlst => NVL(l_unit_ofr_opt.inq_not_wlst,0) + 1, --increased by one
165 x_rev_account_cd => l_unit_ofr_opt.rev_account_cd,
166 x_anon_unit_grading_ind => l_unit_ofr_opt.anon_unit_grading_ind,
167 x_anon_assess_grading_ind => l_unit_ofr_opt.anon_assess_grading_ind,
168 x_non_std_usec_ind => l_unit_ofr_opt.non_std_usec_ind,
169 x_auditable_ind => l_unit_ofr_opt.auditable_ind,
170 x_audit_permission_ind => l_unit_ofr_opt.audit_permission_ind,
171 x_not_multiple_section_flag => l_unit_ofr_opt.not_multiple_section_flag ,
172 x_sup_uoo_id => l_unit_ofr_opt.sup_uoo_id ,
173 x_relation_type => l_unit_ofr_opt.relation_type ,
174 x_default_enroll_flag => l_unit_ofr_opt.default_enroll_flag,
175 x_abort_flag => l_unit_ofr_opt.abort_flag
176 );
177 RETURN;
178
179 END IF;
180
181 OPEN c_chk_us_ivr;
182 FETCH c_chk_us_ivr INTO l_ivrs_available_ind,l_unit_section_status,l_auditable_ind;
183 CLOSE c_chk_us_ivr;
184
185 --If the unit Section is not for IVR Response Enrollment then log error message
186 IF l_ivrs_available_ind <> 'Y' THEN
187 l_message_data := 'IGS_EN_US_NOT_OFR_IVR';
188 p_ret_status := 'FALSE';
189 END IF;
190
191 --If the unit Section is of PLANNED OR CANCELLED status then log error message
192 IF l_unit_section_status IN ('PLANNED','CANCELLED','NOT_OFFERED') THEN
193 IF l_message_data IS NULL THEN
194 l_message_data := 'IGS_SS_EN_INVLD_UNIT_STATUS';
195 ELSE
196 l_message_data := l_message_data||';'||'IGS_SS_EN_INVLD_UNIT_STATUS';
197 END IF;
198 p_ret_status := 'FALSE';
199 END IF;
200
201 --If the Auditable parameter passed is yes, But the Auditable indicator of the
202 --unit section is no then log Error message
203 IF p_audit_ind = 'Y' AND l_auditable_ind <> 'Y' THEN
204 IF l_message_data IS NULL THEN
205 l_message_data := 'IGS_EN_CANNOT_AUDIT';
206 ELSE
207 l_message_data := l_message_data||';'||'IGS_EN_CANNOT_AUDIT';
208 END IF;
209 p_ret_status := 'FALSE';
210 END IF;
211
212 --If one or more of the above three validation has been failed then log all the
213 --error messages and Return.
214 IF p_ret_status = 'FALSE' THEN
215 igs_en_gen_017.enrp_msg_string_to_list (
216 p_message_string => l_message_data,
217 p_delimiter => ';',
218 p_init_msg_list => FND_API.G_FALSE,
219 x_message_count => l_message_count,
220 x_message_data => l_message_data_out);
221 RETURN;
222 END IF;
223
224 --Call the below procedure for the Person Step validations
225 l_message_data := NULL;
226 igs_en_ivr_pub.evaluate_person_steps(
227 p_api_version => 1.0,
228 p_init_msg_list => FND_API.G_FALSE,
229 p_commit => FND_API.G_FALSE,
230 p_person_number => p_person_number,
231 p_career => p_career,
232 p_program_code => p_program_code,
233 p_term_alt_code => p_term_alt_code,
234 x_return_status => l_ret_status,
235 x_msg_count => l_message_count,
236 x_msg_data => l_message_data );
237
238 --If the Person Step Validation has been returned with Error then
239 --Log error message and Return.
240 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
241 p_ret_status := 'FALSE';
242 RETURN;
243 END IF;
244
245 --Get the Unit Section Status and Waitlist Allowed indicator of the Unit Section Applied for
246 igs_en_gen_015.get_usec_status (
247 p_uoo_id => l_uoo_id,
248 p_person_id => l_person_id,
249 p_unit_section_status => l_us_status,
250 p_waitlist_ind => l_waitlist_ind,
251 p_load_cal_type => l_cal_type,
252 p_load_ci_sequence_number => l_ci_sequence_number,
253 p_course_cd => p_program_code);
254
255 --If the waitlist indicator is 'Y' then Student can waitlist into the unit section
256 IF l_waitlist_ind = 'Y' THEN
257 --If Action parameter is CART then log an error message saying the unit section is full.
258 --You can be waitlisted into it and Return.
259 IF p_action = 'CART' THEN
260 p_ret_status := 'FALSE';
261 p_waitlist_ind := 'Y';
262 l_next_step := 'N';
263 igs_en_gen_017.enrp_msg_string_to_list (
264 p_message_string => 'IGS_EN_IVR_USEC_WLST_INFO',
265 p_delimiter => ';',
266 p_init_msg_list => FND_API.G_FALSE,
267 x_message_count => l_message_count,
268 x_message_data => l_message_data_out);
269 p_ret_status := 'FALSE';
270 p_error_message := l_message_data_out;
271
272 --If Action parameter is WLIST then Proceed to the further validations
273 ELSIF p_action = 'WLIST' THEN
274 l_next_step := 'Y';
275 END IF;
276
277 --If the Waitlist indicator is 'N' then Student can enroll into the unit section.
278 --Proceed to the further validations
279 ELSIF l_waitlist_ind = 'N' THEN
280 l_next_step := 'Y';
281 --If the Waitlist indicator is NULL then Student can neither Enroll nor waitlist.
282 --Log proper error message and Return.
283 ELSIF l_waitlist_ind IS NULL THEN
284 p_ret_status := 'FALSE';
285 p_waitlist_ind := 'N';
286 l_next_step := 'N';
287 igs_en_gen_017.enrp_msg_string_to_list (
288 p_message_string => 'IGS_EN_SS_CANNOT_WAITLIST',
289 p_delimiter => ';',
290 p_init_msg_list => FND_API.G_FALSE,
291 x_message_count => l_message_count,
292 x_message_data => l_message_data_out);
293 p_ret_status := 'FALSE';
294 p_error_message := l_message_data_out;
295 END IF;
296
297 --In case of any succes case above do the below validation
298 IF l_next_step = 'Y' THEN
299
300 --Perform the unit steps validation and add the unit section to the cart in unconfirmed status.
301 --In case of action WLIST, Waitlist the student into the unit section.
302 igs_ss_en_wrappers.insert_into_enr_worksheet (
303 p_person_number => p_person_number,
304 p_course_cd => l_primary_code,
305 p_uoo_id => l_uoo_id,
306 p_waitlist_ind => l_waitlist_ind,
307 p_session_id => NULL,
308 p_return_status => l_ret_status,
309 p_message => l_message_data,
310 p_cal_type => l_cal_type,
311 p_ci_sequence_number => l_ci_sequence_number,
312 p_audit_requested => p_audit_ind,
313 p_enr_method => NULL,
314 p_override_cp => NULL,
315 p_subtitle => NULL,
316 p_gradsch_cd => NULL,
317 p_gs_version_num => NULL,
318 p_calling_obj => 'JOB');
319
320 --If the return status is D (means DENY) then Log error message and Return
321 IF l_ret_status = 'D' THEN
322 igs_en_gen_017.enrp_msg_string_to_list (
323 p_message_string => l_message_data,
324 p_delimiter => ';',
325 p_init_msg_list => FND_API.G_FALSE,
326 x_message_count => l_message_count,
327 x_message_data => l_message_data_out);
328 p_ret_status := 'FALSE';
329 END IF;
330 END IF;
331 END add_to_cart_waitlist;
332
333 PROCEDURE drop_section(
334 p_person_number IN VARCHAR2,
335 p_career IN VARCHAR2,
336 p_program_code IN VARCHAR2,
337 p_term_alt_code IN VARCHAR2,
338 p_call_number IN NUMBER ,
339 p_action IN VARCHAR2,
340 p_drop_reason IN VARCHAR2,
341 p_adm_status IN VARCHAR2,
342 p_error_message OUT NOCOPY VARCHAR2,
343 p_return_stat OUT NOCOPY VARCHAR2) IS
344 /*
345 || Created By : Nalin Kumar
346 || Created On : 16-Jan-2003
347 || Purpose : To drop all the sections of students for the career/program and term.
348 || Known limitations, enhancements or remarks :
349 || Change History :
350 || Who When What
351 */
352 l_person_id igs_pe_person.person_id%TYPE;
353 l_person_type igs_pe_person_types.person_type_code%TYPE;
354 l_cal_type igs_ca_inst.cal_type%TYPE;
355 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
356 l_program_cd igs_en_su_attempt_all.course_cd%TYPE;
357 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
358 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
359 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
360 l_messaage_count NUMBER(15);
361 l_error_message VARCHAR2(1000);
362 l_return_stat VARCHAR2(10);
363 l_api_name CONSTANT VARCHAR2(30) := 'drop_section';
364 l_api_version CONSTANT NUMBER := 1.0;
365 l_failed_uoo_ids VARCHAR2(1000);
366 l_failed_unit_cds VARCHAR2(1000);
367
368 l_return_status VARCHAR2(10) := NULL;
369 l_message_count NUMBER := NULL;
370 l_message_data VARCHAR2(1000);
371
372 CURSOR cur_uoo_id (cp_person_id igs_pe_person.person_id%TYPE,
373 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
374 cp_cal_type igs_ca_inst.cal_type%TYPE,
375 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE)IS
376 SELECT uoo_id
377 FROM igs_en_su_attempt
378 WHERE person_id = cp_person_id
379 AND unit_attempt_status IN ('INVALID','WAITLIST','ENROLLED')
380 AND course_cd = cp_course_cd
381 AND (cal_type , ci_sequence_number) IN (SELECT teach_cal_type,teach_ci_sequence_number
382 FROM igs_ca_load_to_teach_v
383 WHERE load_cal_type = cp_cal_type AND
384 load_ci_sequence_number = cp_ci_sequence_number);
385 l_rec_uoo_id cur_uoo_id%ROWTYPE;
386 l_uoo_ids VARCHAR2(1000);
387
388 BEGIN
389
390 --Initialize the parameters
391 p_error_message := NULL;
392 p_return_stat := 'TRUE';
393
394 IF p_action = 'ONE' THEN
395 igs_en_gen_017.enrp_validate_input_parameters(
396 p_person_number => p_person_number ,
397 p_career => p_career ,
398 p_program_code => p_program_code ,
399 p_term_alt_code => p_term_alt_code ,
400 p_call_number => p_call_number ,
401 p_validation_level => 'WITHCALLNUM' ,
402 p_person_id => l_person_id ,
403 p_person_type => l_person_type ,
404 p_cal_type => l_cal_type ,
405 p_ci_sequence_number => l_ci_sequence_number,
406 p_primary_code => l_primary_code ,
407 p_primary_version => l_primary_version ,
408 p_uoo_id => l_uoo_id ,
409 p_error_message => l_error_message ,
410 p_ret_status => l_return_stat
411 );
412 ELSIF p_action = 'ALL' THEN
413 igs_en_gen_017.enrp_validate_input_parameters(
414 p_person_number => p_person_number ,
415 p_career => p_career ,
416 p_program_code => p_program_code ,
417 p_term_alt_code => p_term_alt_code ,
418 p_call_number => p_call_number ,
419 p_validation_level => 'NOCALLNUM' ,
420 p_person_id => l_person_id ,
421 p_person_type => l_person_type ,
422 p_cal_type => l_cal_type ,
423 p_ci_sequence_number => l_ci_sequence_number,
424 p_primary_code => l_primary_code ,
425 p_primary_version => l_primary_version ,
426 p_uoo_id => l_uoo_id ,
427 p_error_message => l_error_message ,
428 p_ret_status => l_return_stat
429 );
430 END IF;
431 p_return_stat := l_return_stat;
432 p_error_message := l_error_message;
433 IF p_return_stat = 'FALSE' THEN
434 igs_en_gen_017.enrp_msg_string_to_list(
435 p_message_string => l_error_message,
436 p_init_msg_list => FND_API.G_FALSE,
437 x_message_count => l_messaage_count,
438 x_message_data => l_message_data
439 );
440 RETURN;
441 END IF;
442 --
443 --If the control reaches till here then perform the Person Validate Step by calling igs_en_ivr_pub.evaluate_person_steps.
444 --
445 igs_en_ivr_pub.evaluate_person_steps(
446 p_api_version => 1.0,
447 p_init_msg_list => FND_API.G_FALSE,
448 p_commit => FND_API.G_FALSE,
449 p_person_number => p_person_number,
450 p_career => p_career,
451 p_program_code => p_program_code,
452 p_term_alt_code => p_term_alt_code,
453 x_return_status => l_return_status,
454 x_msg_count => l_message_count,
455 x_msg_data => l_message_data);
456
457 IF l_return_status = FND_API.G_FALSE THEN
458 RETURN;
459 END IF;
460
461 --
462 --If the action is 'ALL' then Fetch the students sections for selected term and career/program.
463 --
464 IF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') = 'Y' THEN
465 l_program_cd := l_primary_code;
466 ELSE
467 l_program_cd := p_program_code;
468 END IF;
469 l_uoo_ids := NULL;
470
471 IF p_action = 'ONE' THEN
472 l_uoo_ids := l_uoo_id;
473 ELSIF p_action = 'ALL' THEN
474 FOR l_rec_uoo_id IN cur_uoo_id(l_person_id, l_program_cd, l_cal_type, l_ci_sequence_number) LOOP
475 IF l_uoo_ids IS NULL THEN
476 l_uoo_ids := l_rec_uoo_id.uoo_id;
477 ELSE
478 l_uoo_ids := l_uoo_ids||','||l_rec_uoo_id.uoo_id;
479 END IF;
480 END LOOP;
481 END IF;
482
483 BEGIN
484 --Call the program unit to drop the unit sections
485 igs_ss_en_wrappers.drop_selected_units(
486 p_uoo_ids => l_uoo_ids ,
487 p_person_id => l_person_id ,
488 p_person_type => l_person_type ,
489 p_load_cal_type => l_cal_type ,
490 p_load_sequence_number => l_ci_sequence_number,
491 p_program_cd => l_program_cd ,
492 p_program_version => l_primary_version ,
493 p_dcnt_reason_cd => p_drop_reason ,
494 p_admin_unit_status => p_adm_status ,
495 p_effective_date => SYSDATE ,
496 p_failed_uoo_ids => l_failed_uoo_ids ,
497 p_failed_unit_cds => l_failed_unit_cds ,
498 p_return_status => l_return_status ,
499 p_message => l_error_message ,
500 p_ovrrd_min_cp_chk => NULL,
501 p_ovrrd_crq_chk => NULL,
502 p_ovrrd_prq_chk => NULL,
503 p_ovrrd_att_typ_chk => NULL
504 );
505
506 EXCEPTION
507 -- Added the exception part to handle the excption occured in igs_ss_enr_details.drop_selected_units procedure.
508 WHEN OTHERS THEN
509 IF l_message_data IS NOT NULL THEN
510 IF p_error_message IS NOT NULL THEN
511 p_error_message := p_error_message||';'||l_message_data;
512 ELSE
513 p_error_message := l_message_data;
514 END IF;
515 END IF;
516 p_return_stat := 'FALSE';
517 RETURN;
518 END;
519
520 l_message_data:= NULL;
521 igs_en_gen_017.enrp_msg_string_to_list(
522 p_message_string => l_error_message,
523 p_init_msg_list => FND_API.G_FALSE,
524 x_message_count => l_message_count,
525 x_message_data => l_message_data);
526
527 IF l_message_data IS NOT NULL THEN
528 IF p_error_message IS NOT NULL THEN
529 p_error_message := p_error_message||';'||l_message_data;
530 ELSE
531 p_error_message := l_message_data;
532 END IF;
533 END IF;
534 p_return_stat := 'TRUE';
535 END drop_section;
536
537 PROCEDURE enrp_get_default_term(
538 p_term_alt_code OUT NOCOPY VARCHAR2,
539 p_error_message OUT NOCOPY VARCHAR2,
540 p_ret_status OUT NOCOPY VARCHAR2 )AS
541 /*
542 || Created By : Nishikant
543 || Created On : 15JAN2003
544 || Purpose : This Procedure gets the default Term.
545 || If no default term found then it will return error message.
546 ||
547 || Known limitations, enhancements or remarks :
548 || Change History :
549 || Who When What
550 || (reverse chronological order - newest change first)
551 */
552
553 --Get the 'load effective date alias' in 'Configure Enrollment Calendar' set up form.
554 --For that alias find the active term calendar instance for which the sysdate is between
555 --the start date and end date of the instance and the absolute value of the alias is less than
556 --or equal to sysdate. The instance with most recent absolute value will be considered as defaulted.
557 CURSOR c_cur_alt_code IS
558 SELECT currterm.alternate_code
559 FROM (SELECT ci.alternate_code
560 FROM igs_ca_inst ci,
561 igs_ca_type ct,
562 igs_ca_stat cs,
563 igs_ca_da_inst dai
564 WHERE ci.cal_type = ct.cal_type
565 AND ct.s_cal_cat = 'LOAD'
566 AND ci.cal_status = cs.cal_status
567 AND cs.s_cal_status = 'ACTIVE'
568 AND SYSDATE BETWEEN ci.start_dt AND ci.end_dt
569 AND dai.cal_type = ci.cal_type
570 AND dai.ci_sequence_number = ci.sequence_number
571 AND dai.dt_alias = (SELECT load_effect_dt_alias
572 FROM igs_en_cal_conf)
573 AND dai.absolute_val <= SYSDATE
574 ORDER BY dai.absolute_val DESC) currterm
575 WHERE ROWNUM < 2;
576
577 BEGIN
578 --Initialize the parameters
579 p_error_message := NULL;
580 p_ret_status := 'TRUE';
581
582 OPEN c_cur_alt_code;
583 FETCH c_cur_alt_code INTO p_term_alt_code;
584
585 IF c_cur_alt_code%NOTFOUND THEN
586 --If the default Term Calendar is not found then set error message.
587 p_error_message := 'IGS_EN_NO_DEFAULT_TERM';
588 p_ret_status := 'FALSE';
589 END IF;
590 CLOSE c_cur_alt_code;
591
592 END enrp_get_default_term;
593
594 PROCEDURE enrp_get_enr_method(
595 p_enr_method_type OUT NOCOPY VARCHAR2,
596 P_error_message OUT NOCOPY VARCHAR2,
597 p_ret_status OUT NOCOPY VARCHAR2) IS
598 /*
599 || Created By : Nalin Kumar
600 || Created On : 16-Jan-2003
601 || Purpose : To return the method type based on the enrolling source.
602 || This procedure would return the Enrollment Method depending on the Passed
603 || Parameter indicating the enrolling source. This parameter would currently
604 || have the values SS for Self Service /IVR or the actual enrolment method
605 || stored with at student attempt level Based on this, the method type setup
606 || would be fetched and returned or if method is passed then it would be
607 || validated to be a valid method. The setup is done in IGSEN015 form. If no
608 || method type is found then error is returned. The procedure can be expanded
609 || later to other methods of enrollment as and when the requirement arises.
610 || 'SS' and 'IVR' are codes used internally hence not seeded.
611 || Note: After this modification at no place the IGS_EN_METHOD_TYPE should be
612 || used directly to get the enrollment method type. This procedure should be invoked.
613 ||
614 || Known limitations, enhancements or remarks :
615 || Change History :
616 || Who When What
617 || rvivekan 11-7-2003 changed g_invoke_source NJOB to JOB as a part of bug 3036949
618 || kkillams 09-06-2003 Modified the validation to return default bulk job enrollment method
619 || if g_invoke_source set to 'JOB' w.r.t bug 3829270
620 */
621 CURSOR cur_ss IS
622 SELECT enr_method_type
623 FROM igs_en_method_type
624 WHERE self_service = 'Y'
625 AND closed_ind = 'N';
626 l_rec_ss cur_ss%ROWTYPE;
627
628 CURSOR cur_ivr IS
629 SELECT enr_method_type
630 FROM igs_en_method_type
631 WHERE ivr_display_ind = 'Y'
632 AND closed_ind = 'N';
633 l_rec_ivr cur_ivr%ROWTYPE;
634
635 CURSOR cur_bulkjob IS
636 SELECT enr_method_type
637 FROM igs_en_method_type
638 WHERE bulk_job_ind = 'Y'
639 AND closed_ind = 'N';
640 l_rec_bulk cur_bulkjob%ROWTYPE;
641
642 BEGIN
643 --
644 --Initialize the parameters.
645 --
646 p_enr_method_type := NULL;
647 p_error_message := NULL;
648 p_ret_status := 'TRUE';
649
650 --
651 --Get the Enrollment Method.
652 --
653 IF igs_en_gen_017.g_invoke_source = 'SS' THEN
654 OPEN cur_ss;
655 FETCH cur_ss INTO l_rec_ss;
656 IF cur_ss%FOUND THEN
657 p_enr_method_type := l_rec_ss.enr_method_type;
658 END IF;
659 CLOSE cur_ss;
660 ELSIF igs_en_gen_017.g_invoke_source = 'IVR' THEN
661 OPEN cur_ivr;
662 FETCH cur_ivr INTO l_rec_ivr;
663 IF cur_ivr%FOUND THEN
664 p_enr_method_type := l_rec_ivr.enr_method_type;
665 END IF;
666 CLOSE cur_ivr;
667 ELSIF igs_en_gen_017.g_invoke_source = 'JOB' THEN
668 OPEN cur_bulkjob;
669 FETCH cur_bulkjob INTO l_rec_bulk;
670 IF cur_bulkjob%FOUND THEN
671 p_enr_method_type := l_rec_bulk.enr_method_type;
672 END IF;
673 CLOSE cur_bulkjob;
674 END IF;
675
676 --
677 -- If no Enrollment Method found then return 'FALSE' along with the proper error message.
678 --
679 IF p_enr_method_type IS NULL THEN
680 p_error_message := 'IGS_SS_EN_NOENR_METHOD';
681 p_ret_status := 'FALSE';
682 END IF;
683 END enrp_get_enr_method;
684
685 FUNCTION enrp_get_invoke_source RETURN VARCHAR2 AS
686 /******************************************************************************************
687 || Created By : smanglm
688 || Created On : 2003/01/22
689 || Purpose : This funtion will return value of global package variable g_invoke_source
690 || Change History :
691 || Who When What
692 || (reverse chronological order - newest change first)
693 ******************************************************************************************/
694 BEGIN
695 RETURN igs_en_gen_017.g_invoke_source;
696 END enrp_get_invoke_source;
697
698 PROCEDURE enrp_get_term_ivr_list(
699 p_term_tbl OUT NOCOPY igs_en_ivr_pub.term_tbl_type,
700 p_error_message OUT NOCOPY VARCHAR2 ,
701 p_ret_status OUT NOCOPY VARCHAR2 ) AS
702 /*
703 || Created By : Nishikant
704 || Created On : 15JAN2003
705 || Purpose : This Procedure gets a list of valid term calendars for IVR in PL/SQL Table.
706 || These calendars should be ACTIVE LOAD calendars in system where the IVR indicator is 'Y'.
707 ||
708 || Known limitations, enhancements or remarks :
709 || Change History :
710 || Who When What
711 || (reverse chronological order - newest change first)
712 */
713
714 CURSOR c_alt_code IS
715 SELECT ci.alternate_code
716 FROM igs_ca_inst ci,
717 igs_ca_type ca,
718 igs_ca_stat cs
719 WHERE ci.cal_type =ca.cal_type
720 AND ci.cal_status = cs.cal_status
721 AND cs.s_cal_status = 'ACTIVE'
722 AND ca.s_cal_cat = 'LOAD'
723 AND ci.ivr_display_ind = 'Y';
724 counter NUMBER := 0;
725
726 BEGIN
727 --Initialize the parameters
728 p_error_message := NULL;
729 p_ret_status := 'TRUE';
730
731 --Get all the Active term calendars for IVR
732 FOR l_alt_code IN c_alt_code LOOP
733 counter := counter + 1;
734 p_term_tbl(counter).p_term_alt_code := l_alt_code.alternate_code;
735 END LOOP;
736
737 --If not a single term has found then set error message
738 IF counter = 0 THEN
739 p_error_message := 'IGS_EN_NO_TERM_FOR_IVR';
740 p_ret_status := 'FALSE';
741 END IF;
742
743 END enrp_get_term_ivr_list;
744
745 PROCEDURE enrp_msg_string_to_list
746 (
747 p_message_string IN VARCHAR2,
748 p_delimiter IN VARCHAR2,
749 p_init_msg_list IN VARCHAR2,
750 x_message_count OUT NOCOPY NUMBER,
751 x_message_data OUT NOCOPY VARCHAR2
752 )
753 /******************************************************************************************
754 || Created By : smanglm
755 || Created On : 2003/01/15
756 || Purpose : This procedure will extract the message name and set them in message list
757 || Change History :
758 || Who When What
759 || (reverse chronological order - newest change first)
760 ******************************************************************************************/
761
762 IS
763 -- local variables
764 l_start_pos NUMBER :=0;
765 l_end_pos NUMBER :=0;
766 l_message_string VARCHAR2(4000);
767 l_msg VARCHAR2(30);
768
769 BEGIN
770 /*
771 Initialize message stack
772 */
773 IF FND_API.to_Boolean(p_init_msg_list) THEN
774 FND_MSG_PUB.initialize;
775 END IF;
776
777 /*
778 If input string is null then do not proceed and return
779 */
780 IF p_message_string IS NULL THEN
781 RETURN;
782 END IF;
783 l_message_string := p_message_string;
784 /*
785 remove delimiter from start messaage string
786 */
787 IF substr(l_message_string,1,1) = p_delimiter THEN
788 l_message_string := substr (l_message_string,2);
789 END IF;
790 /*
791 add p_delimiter at the end, if missing
792 */
793 IF substr(l_message_string,-1) <> p_delimiter THEN
794 l_message_string := l_message_string||p_delimiter;
795 END IF;
796 /*
797 loop through the string and keep adding to message stack
798 */
799 FOR i IN 1..length(l_message_string)
800 LOOP
801 IF substr(l_message_string,i,1) = p_delimiter THEN
802 l_end_pos := i;
803 l_msg := substr(l_message_string,l_start_pos+1,l_end_pos-l_start_pos-1);
804 IF INSTR(l_msg, '*') <> 0 THEN
805 FND_MESSAGE.SET_NAME('IGS',substr(l_msg,1, (INSTR(l_msg, '*') - 1 )));
806 FND_MESSAGE.SET_TOKEN('UNIT_CD', substr(l_msg, (INSTR(l_msg, '*') + 1 )));
807 FND_MSG_PUB.ADD;
808 ELSE
809 FND_MESSAGE.SET_NAME('IGS',l_msg);
810 FND_MSG_PUB.ADD;
811 END IF;
812 ELSE
813 l_start_pos:= l_end_pos;
814 END IF;
815 /*
816 check for exit criteria
817 */
818 IF l_end_pos = length(l_message_string) THEN
819 EXIT;
820 END IF;
821 END LOOP;
822 /*
823 now get the message data and count
824 Please note that if count > 1, data will null
825 Calling procedure should loop through the message
826 stack to retrieve all messages
827 */
828 FND_MSG_PUB.Count_And_Get(
829 p_count => x_message_count,
830 p_data => x_message_data);
831
832 END enrp_msg_string_to_list;
833
834
835 PROCEDURE enrp_validate_call_number(
836 p_term_alt_code IN VARCHAR2,
837 p_call_number IN NUMBER ,
838 p_uoo_id OUT NOCOPY NUMBER ,
839 p_cal_type OUT NOCOPY VARCHAR2,
840 p_ci_sequence_number OUT NOCOPY NUMBER ,
841 p_error_message OUT NOCOPY VARCHAR2,
842 p_ret_status OUT NOCOPY VARCHAR2) AS
843 /*
844 || Created By : Nishikant
845 || Created On : 15JAN2003
846 || Purpose : The procedure validates the term is valid and the call number corresponds
847 || to a valid unit section for that term.
848 || As call number is unique within the term it takes the term calendar also and
849 || return the valid section details else return the error message.
850 ||
851 || Known limitations, enhancements or remarks :
852 || Change History :
853 || Who When What
854 || (reverse chronological order - newest change first)
855 */
856
857 l_cal_type igs_ca_inst.cal_type%TYPE;
858 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
859
860 CURSOR c_uoo_id IS
861 SELECT 'X'
862 FROM igs_ps_unit_ofr_opt
863 WHERE call_number = p_call_number;
864 l_dummy VARCHAR2(1);
865
866 CURSOR c_chk_call_num_term (cp_cal_type igs_ca_inst.cal_type%TYPE,
867 cp_ci_seq_num igs_ca_inst.sequence_number%TYPE) IS
868 SELECT uoo_id
869 FROM igs_ps_unit_ofr_opt
870 WHERE call_number = p_call_number
871 AND ( cal_type, ci_sequence_number ) IN
872 ( SELECT teach_cal_type, teach_ci_sequence_number
873 FROM igs_ca_load_to_teach_v
874 WHERE load_cal_type = cp_cal_type
875 AND load_ci_sequence_number = cp_ci_seq_num );
876
877 BEGIN
878 --Initialize the parameters
879 p_error_message := NULL;
880 p_ret_status := 'TRUE';
881
882 --Call the procedure enrp_validate_term_alt_code to validate the paramter p_term_alt_code.
883 igs_en_gen_017.enrp_validate_term_alt_code(
884 p_term_alt_code => p_term_alt_code,
885 p_cal_type => l_cal_type,
886 p_ci_sequence_number => l_ci_sequence_number,
887 p_error_message => p_error_message,
888 p_ret_status => p_ret_status);
889
890 IF p_ret_status = 'TRUE' THEN
891 --Set the Term Calendar details
892 p_cal_type := l_cal_type;
893 p_ci_sequence_number := l_ci_sequence_number;
894
895 --Check whether the provided call number is valid or not
896 OPEN c_uoo_id;
897 FETCH c_uoo_id INTO l_dummy;
898 IF c_uoo_id%FOUND THEN
899 --Check if the provided call number falls under term calendar passed
900 --And get the uoo_id for the provided Call Number parameter
901 OPEN c_chk_call_num_term( l_cal_type, l_ci_sequence_number);
902 FETCH c_chk_call_num_term INTO p_uoo_id;
903 IF c_chk_call_num_term%NOTFOUND THEN
904 --If the Call number does not fall in the term calendar provided then set the message parameter
905 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_MISMATCH_CALL_TERM');
906 FND_MSG_PUB.ADD;
907 p_error_message := 'IGS_EN_MISMATCH_CALL_TERM';
908 END IF;
909 CLOSE c_chk_call_num_term;
910
911 ELSE
912 --If uoo_id could not found then set the error message for invalid call number
913 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_INVALID_CALL_NUMBER');
914 FND_MESSAGE.SET_TOKEN('CALLNUM',p_call_number);
915 FND_MSG_PUB.ADD;
916 p_error_message := 'IGS_EN_INVALID_CALL_NUMBER';
917
918 END IF;
919 CLOSE c_uoo_id;
920 END IF;
921
922 IF p_error_message IS NOT NULL THEN
923 p_ret_status := 'FALSE';
924 END IF;
925
926 END enrp_validate_call_number;
927
928 PROCEDURE enrp_validate_input_parameters
929 (
930 p_person_number IN VARCHAR2,
931 p_career IN VARCHAR2,
932 p_program_code IN VARCHAR2,
933 p_term_alt_code IN VARCHAR2,
934 p_call_number IN NUMBER,
935 p_validation_level IN VARCHAR2,
936 p_person_id OUT NOCOPY NUMBER,
937 p_person_type OUT NOCOPY VARCHAR2,
938 p_cal_type OUT NOCOPY VARCHAR2,
939 p_ci_sequence_number OUT NOCOPY NUMBER,
940 p_primary_code OUT NOCOPY VARCHAR2,
941 p_primary_version OUT NOCOPY NUMBER,
942 p_uoo_id OUT NOCOPY NUMBER,
943 p_error_message OUT NOCOPY VARCHAR2,
944 p_ret_status OUT NOCOPY VARCHAR2
945 )
946 /******************************************************************************************
947 || Created By : smanglm
948 || Created On : 2003/01/15
949 || Purpose : This procedure will accept the input parameters and verify that they are
950 || valid OSS data. If not valid data then it would return the error message.
951 || Change History :
952 || Who When What
953 || (reverse chronological order - newest change first)
954 ******************************************************************************************/
955
956 IS
957 -- local variables
958 l_person_id igs_pe_person.person_id%TYPE;
959 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
960 l_error_message VARCHAR2(2000);
961 l_ret_status VARCHAR2(10);
962 l_primary_program_code igs_ps_ver.course_cd%TYPE;
963 l_primary_program_version igs_ps_ver.version_number%TYPE;
964 l_cal_type igs_ca_inst.cal_type%TYPE;
965 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
966 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
967 l_return_status VARCHAR2(10);
968 l_message_count NUMBER;
969 l_message_data VARCHAR2(2000);
970
971
972 BEGIN
973 /*
974 set the package variable igs_en_gen_017.g_invoke_source to IVR
975 */
976 igs_en_gen_017.g_invoke_source := 'IVR';
977 /*
978 Set the out parameter p_error_message as null and p_ret_status as 'TRUE'
979 */
980 p_error_message := NULL;
981 p_ret_status := 'TRUE';
982 /*
983 Validate the person number parameter
984 */
985 igs_en_gen_017.enrp_validate_student
986 (
987 p_person_number => p_person_number,
988 p_person_id => l_person_id ,
989 p_person_type => l_person_type ,
990 p_error_message => l_error_message,
991 p_ret_status => l_ret_status
992 );
993 IF l_ret_status = 'FALSE' THEN
994 p_ret_status := l_ret_status;
995 -- return as no other validation would be successful
996 -- messages are not added to the parameter since they are already
997 -- added to the stack
998 RETURN;
999 END IF;
1000 /*
1001 assign OUT parameters
1002 */
1003 p_person_id := l_person_id;
1004 p_person_type := l_person_type;
1005 /*
1006 got the person number and hence continue with other validation
1007 */
1008 /*
1009 Validate the career and program
1010 */
1011 igs_en_ivr_pub.validate_career_program
1012 (
1013 p_api_version => 1.0,
1014 p_init_msg_list => FND_API.G_FALSE,
1015 p_commit => FND_API.G_FALSE,
1016 p_person_number => p_person_number,
1017 p_career => p_career,
1018 p_program_code => p_program_code,
1019 x_primary_code => l_primary_program_code,
1020 x_primary_version=> l_primary_program_version,
1021 x_return_status => l_return_status,
1022 x_msg_count => l_message_count,
1023 x_msg_data => l_message_data
1024 );
1025 IF l_message_data IS NOT NULL THEN
1026 p_ret_status := 'FALSE';
1027 -- messages are not added to the parameter since they are already
1028 -- added to the stack
1029 END IF;
1030 /*
1031 continue with other validation so that at one go all
1032 exceptions are caught.
1033 */
1034 DECLARE
1035 l_error_message_in VARCHAR2(2000);
1036 l_ret_status_in VARCHAR2(10);
1037
1038 BEGIN
1039 IF p_validation_level = 'NOCALLNUM' THEN
1040 igs_en_gen_017.enrp_validate_term_alt_code
1041 (
1042 p_term_alt_code => p_term_alt_code,
1043 p_cal_type => l_cal_type,
1044 p_ci_sequence_number=> l_ci_sequence_number,
1045 p_error_message => l_error_message_in,
1046 p_ret_status => l_ret_status_in
1047 );
1048 IF l_error_message_in IS NOT NULL OR l_ret_status_in = 'FALSE' THEN
1049 p_ret_status := 'FALSE';
1050 -- message already in the stack
1051 END IF; -- l_error_message_in IS NOT NULL OR l_ret_status_in = 'FALSE'
1052 ELSIF p_validation_level = 'WITHCALLNUM' THEN
1053 igs_en_gen_017.enrp_validate_call_number
1054 (
1055 p_term_alt_code => p_term_alt_code,
1056 p_call_number => p_call_number,
1057 p_uoo_id => l_uoo_id,
1058 p_cal_type => l_cal_type ,
1059 p_ci_sequence_number => l_ci_sequence_number ,
1060 p_error_message => l_error_message_in,
1061 p_ret_status => l_ret_status_in
1062 );
1063 IF l_error_message_in IS NOT NULL OR l_ret_status_in = 'FALSE' THEN
1064 -- messages already added to the stack
1065 p_ret_status := 'FALSE';
1066 END IF;
1067 END IF; -- p_validation_level = 'NOCALLNUM'
1068 /*
1069 assign OUT parameters
1070 */
1071 p_cal_type := l_cal_type;
1072 p_ci_sequence_number := l_ci_sequence_number;
1073 p_primary_code := l_primary_program_code;
1074 p_primary_version := l_primary_program_version;
1075 p_uoo_id := l_uoo_id;
1076 END;
1077 END enrp_validate_input_parameters;
1078
1079 PROCEDURE enrp_validate_student
1080 (
1081 p_person_number IN VARCHAR2,
1082 p_person_id OUT NOCOPY NUMBER,
1083 p_person_type OUT NOCOPY VARCHAR2,
1084 p_error_message OUT NOCOPY VARCHAR2,
1085 p_ret_status OUT NOCOPY VARCHAR2
1086 )
1087 /******************************************************************************************
1088 || Created By : smanglm
1089 || Created On : 2003/01/15
1090 || Purpose : The procedure is to validate that the student passed by 3rd party s/w is
1091 || valid person in system or not. If valid and has an fnd user then return
1092 || the person id and the user defined person type for system type of 'STUDENT'.
1093 || Change History :
1094 || Who When What
1095 || (reverse chronological order - newest change first)
1096 || bdeviset 14-APR-2005 changed cursor c_vld_fnd_user for bug # 4303661
1097 ******************************************************************************************/
1098
1099 IS
1100 /*
1101 cursor to validate if person number is valid in the system
1102 */
1103 CURSOR c_vld_person (cp_person_number igs_pe_person_base_v.person_number%TYPE) IS
1104 SELECT person_id
1105 FROM igs_pe_person_base_v
1106 WHERE person_number = cp_person_number;
1107 l_vld_person_id igs_pe_person_base_v.person_id%TYPE;
1108
1109 /*
1110 cursor to validate that person is valid apps user in system
1111 */
1112 CURSOR c_vld_fnd_user (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1113 SELECT user_id
1114 FROM fnd_user
1115 WHERE person_party_id = cp_person_id;
1116 l_vld_fnd_user_id fnd_user.user_id%TYPE;
1117
1118 /*
1119 cursor to validate that the person number is a valid 'STUDENT' in the system
1120 */
1121 CURSOR c_vld_student (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1122 SELECT person_type_code
1123 FROM igs_pe_typ_instances
1124 WHERE person_id = cp_person_id
1125 AND ( end_date IS NULL OR end_date > SYSDATE )
1126 AND person_type_code IN (
1127 SELECT person_type_code
1128 FROM igs_pe_person_types
1129 WHERE system_type = 'STUDENT'
1130 AND closed_ind = 'N'
1131 );
1132 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
1133
1134 /*
1135 cursor to fetch the RESP_ID and RESP_APPL_ID against the user_id
1136 */
1137 CURSOR c_fnd_user_dtls (cp_user_id fnd_user_resp_groups.user_id%TYPE) IS
1138 SELECT responsibility_id,
1139 responsibility_application_id,
1140 security_group_id
1141 FROM fnd_user_resp_groups
1142 WHERE user_id = cp_user_id
1143 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
1144 rec_fnd_user_dtls c_fnd_user_dtls%ROWTYPE;
1145
1146 BEGIN
1147 /*
1148 Set the out parameter p_error_message as null and p_ret_status as 'TRUE'
1149 */
1150 p_error_message := NULL;
1151 p_ret_status := 'TRUE';
1152 /*
1153 If input parmeter is null return with error message 'IGS_GE_INVALID_PERSON_NUMBER'
1154 */
1155 IF p_person_number IS NULL THEN
1156 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
1157 FND_MSG_PUB.ADD;
1158 p_error_message := 'IGS_GE_INVALID_PERSON_NUMBER';
1159 p_ret_status := 'FALSE';
1160 RETURN;
1161 END IF;
1162 /*
1163 Validate if person number is valid in the system
1164 */
1165 OPEN c_vld_person (p_person_number);
1166 FETCH c_vld_person INTO l_vld_person_id;
1167 IF c_vld_person%NOTFOUND THEN
1168 CLOSE c_vld_person;
1169 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
1170 FND_MSG_PUB.ADD;
1171 p_error_message := 'IGS_GE_INVALID_PERSON_NUMBER';
1172 p_ret_status := 'FALSE';
1173 RETURN;
1174 END IF;
1175 CLOSE c_vld_person;
1176 /*
1177 validate that person is valid apps user in system
1178 */
1179 OPEN c_vld_fnd_user (l_vld_person_id);
1180 FETCH c_vld_fnd_user INTO l_vld_fnd_user_id;
1181 IF c_vld_fnd_user%NOTFOUND THEN
1182 CLOSE c_vld_fnd_user;
1183 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PERSON_NOT_FND_USER');
1184 FND_MSG_PUB.ADD;
1185 p_error_message := 'IGS_EN_PERSON_NOT_FND_USER';
1186 p_ret_status := 'FALSE';
1187 RETURN;
1188 END IF;
1189 CLOSE c_vld_fnd_user;
1190 /*
1191 Validate that the person number is a valid 'STUDENT' in the system
1192 */
1193 OPEN c_vld_student (l_vld_person_id);
1194 FETCH c_vld_student INTO l_person_type;
1195 IF c_vld_student%NOTFOUND THEN
1196 CLOSE c_vld_student;
1197 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PERSON_NOT_STUDENT');
1198 FND_MSG_PUB.ADD;
1199 p_error_message := 'IGS_EN_PERSON_NOT_STUDENT';
1200 p_ret_status := 'FALSE';
1201 RETURN;
1202 END IF;
1203 CLOSE c_vld_student;
1204 /*
1205 fetch the RESP_ID and RESP_APPL_ID against the user_id
1206 */
1207 OPEN c_fnd_user_dtls (l_vld_fnd_user_id);
1208 FETCH c_fnd_user_dtls INTO rec_fnd_user_dtls;
1209 IF c_fnd_user_dtls%NOTFOUND THEN
1210 CLOSE c_fnd_user_dtls;
1211 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PERSON_NO_RESP');
1212 FND_MSG_PUB.ADD;
1213 p_error_message := 'IGS_EN_PERSON_NO_RESP';
1214 p_ret_status := 'FALSE';
1215 RETURN;
1216 END IF;
1217 CLOSE c_fnd_user_dtls;
1218
1219 /*
1220 set the apps context
1221 */
1222 fnd_global.apps_initialize
1223 (
1224 user_id => l_vld_fnd_user_id,
1225 resp_id => rec_fnd_user_dtls.responsibility_id,
1226 resp_appl_id => rec_fnd_user_dtls.responsibility_application_id,
1227 security_group_id => rec_fnd_user_dtls.security_group_id
1228 );
1229
1230 /*
1231 assign obtained values to OUT parameters
1232 */
1233 p_person_id := l_vld_person_id;
1234 p_person_type := l_person_type;
1235
1236 END enrp_validate_student;
1237
1238 PROCEDURE enrp_validate_term_alt_code(
1239 p_term_alt_code IN VARCHAR2,
1240 p_cal_type OUT NOCOPY VARCHAR2,
1241 p_ci_sequence_number OUT NOCOPY NUMBER ,
1242 p_error_message OUT NOCOPY VARCHAR2,
1243 p_ret_status OUT NOCOPY VARCHAR2) AS
1244 /*
1245 || Created By : Nishikant
1246 || Created On : 15JAN2003
1247 || Purpose : This procedure validates that the term passed is valid or not.
1248 || If valid then return with null error message and Cal Type and
1249 || Sequence Number required for further processing.
1250 || Else return with error message.
1251 ||
1252 || Known limitations, enhancements or remarks :
1253 || Change History :
1254 || Who When What
1255 || (reverse chronological order - newest change first)
1256 */
1257
1258 CURSOR c_alt_code IS
1259 SELECT ci.cal_type,
1260 ci.sequence_number
1261 FROM igs_ca_inst ci,
1262 igs_ca_type ca,
1263 igs_ca_stat cs
1264 WHERE ci.alternate_code = p_term_alt_code
1265 AND ci.cal_type = ca.cal_type
1266 AND ci.cal_status = cs.cal_status
1267 AND cs.s_cal_status = 'ACTIVE'
1268 AND ca.s_cal_cat = 'LOAD'
1269 AND ci.ivr_display_ind = 'Y';
1270
1271 BEGIN
1272 --Initialize the parameters
1273 p_error_message := NULL;
1274 p_ret_status := 'TRUE';
1275
1276 --If the parameter Alternate Code is NULL then set the error message.
1277 IF p_term_alt_code IS NULL THEN
1278 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_NO_TERM_DTLS');
1279 FND_MSG_PUB.ADD;
1280 p_error_message := 'IGS_EN_NO_TERM_DTLS';
1281 p_ret_status := 'FALSE';
1282 END IF;
1283
1284 --If the alternate code parameter is NOT NULL then validate the alternate code
1285 --and get the cal_type and Sequence Number for the Alternate code
1286 IF p_ret_status = 'TRUE' THEN
1287 OPEN c_alt_code;
1288 FETCH c_alt_code INTO p_cal_type, p_ci_sequence_number;
1289
1290 --If the alternate code is not valid , then set the error message.
1291 IF c_alt_code%NOTFOUND THEN
1292 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_INVALID_TERM');
1293 FND_MESSAGE.SET_TOKEN('ALTERNATE_CODE', p_term_alt_code);
1294 FND_MSG_PUB.ADD;
1295 p_error_message := 'IGS_EN_INVALID_TERM';
1296 p_ret_status := 'FALSE';
1297 END IF;
1298 CLOSE c_alt_code;
1299 END IF;
1300
1301 END enrp_validate_term_alt_code;
1302
1303
1304 END igs_en_gen_017;
[Home] [Help]