[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_IVR_PUB
Source
1 PACKAGE BODY igs_en_ivr_pub AS
2 /* $Header: IGSEN95B.pls 120.5 2006/08/24 07:31:03 bdeviset ship $ */
3 /*
4 ||==============================================================================||
5 || Created By : Nalin Kumar ||
6 || Created On : 16-Jan-2003 ||
7 || Purpose : Created this object as per IVR Build. Bug# 2745985 ||
8 || Known limitations, enhancements or remarks : ||
9 || Change History : ||
10 || Who When What ||
11 || vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified update_enroll_stats
12 || ctyagi 22-sept-2005 Added p_enroll_from_waitlsit_flag as a part of bug ||
13 4580204
14 ||ctyagi 26-sept-2005 Removed p_enroll_from_waitlsit_flag as a part of bug ||
15 4580204
16 || (reverse chronological order - newest change first) ||
17 ||==============================================================================||
18 */
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_EN_IVR_PUB';
20
21 PROCEDURE add_to_cart (
22 p_api_version IN NUMBER,
23 p_init_msg_list IN VARCHAR2,
24 p_commit IN VARCHAR2,
25 p_person_number IN VARCHAR2,
26 p_career IN VARCHAR2,
27 p_program_code IN VARCHAR2,
28 p_term_alt_code IN VARCHAR2,
29 p_call_number IN NUMBER,
30 p_audit_ind IN VARCHAR2,
31 p_waitlist_ind OUT NOCOPY VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2) AS
35 /*
36 || Created By : Nishikant
37 || Created On : 20JAN2003
38 || Purpose : The procedure will add a section to the students cart.
39 || It will accept the call number of section to added .
40 || It will check if seats are available in the section. If seats are not available it will return error status.
41 || Also check if waitlist is allowed and depending on that the return indicator for waitlist is to be set.
42 || The IVR would have to check with the student for waitlisting.
43 || If seats are available and section can be enrolled via IVR then unit steps validation would be performed.
44 || If they are successful then the section will be added to cart with 'UNCONFIRM' status and CART as 'I'.
45 || As first step it perform person step validations also.
46 ||
47 || Known limitations, enhancements or remarks :
48 || Change History :
49 || Who When What
50 || kkillams 11-Jul-2003 Call to rollback is added to the procedure if igs_en_gen_017.add_to_cart_waitlist
51 || sets the out p_ret_status parameter to false. Bug : 3036949
52 || (reverse chronological order - newest change first)
53 */
54 l_api_name CONSTANT VARCHAR2(30) := 'ADDTOCART';
55 l_ret_status VARCHAR2(6);
56 l_message_count NUMBER;
57 l_message_data VARCHAR2(2000);
58
59 BEGIN
60
61 -- Standard Start of API savepoint
62 SAVEPOINT ADDTOCART_PUB;
63
64 -- Standard call to check for call compatibility.
65 IF NOT FND_API.COMPATIBLE_API_CALL (1.0,
66 p_api_version,
67 l_api_name,
68 g_pkg_name ) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
74 FND_MSG_PUB.INITIALIZE;
75 END IF;
76
77 -- Initialize API return status to success
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 --Invoke the procedure add_to_cart_waitlist by passing the relevant parameters.
81 --Parameter p_action should be 'CART' .
82 --The invoke source parameter should have 'IVR' as the input.
83 igs_en_gen_017.add_to_cart_waitlist (
84 p_person_number => p_person_number,
85 p_career => p_career,
86 p_program_code => p_program_code,
87 p_term_alt_code => p_term_alt_code,
88 p_call_number => p_call_number,
89 p_audit_ind => p_audit_ind,
90 p_waitlist_ind => p_waitlist_ind,
91 p_action => 'CART',
92 p_error_message => l_message_data,
93 p_ret_status => l_ret_status);
94
95 --If return status is FALSE above then log error message and RETURN.
96 IF l_ret_status = 'FALSE' THEN
97 ROLLBACK TO ADDTOCART_PUB;
98 igs_en_gen_017.enrp_msg_string_to_list (
99 p_message_string => l_message_data,
100 p_delimiter => ';',
101 p_init_msg_list => FND_API.G_FALSE,
102 x_message_count => l_message_count,
103 x_message_data => l_message_data);
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 x_msg_data := l_message_data;
106 x_msg_count := l_message_count;
107 ELSE
108 IF FND_API.TO_BOOLEAN( p_commit ) THEN
109 COMMIT WORK;
110 END IF;
111 END IF;
112
113 EXCEPTION
114 WHEN FND_API.G_EXC_ERROR THEN
115 ROLLBACK TO ADDTOCART_PUB;
116 x_return_status := FND_API.G_RET_STS_ERROR ;
117 FND_MSG_PUB.COUNT_AND_GET (
118 p_count => x_msg_count,
119 p_data => x_msg_data );
120
121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
122 ROLLBACK TO ADDTOCART_PUB;
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
124 FND_MSG_PUB.COUNT_AND_GET (
125 p_count => x_msg_count,
126 p_data => x_msg_data );
127
128 WHEN OTHERS THEN
129 ROLLBACK TO ADDTOCART_PUB;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
132 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
133 FND_MSG_PUB.ADD;
134 FND_MSG_PUB.COUNT_AND_GET (
135 p_count => x_msg_count,
136 p_data => x_msg_data );
137 END add_to_cart;
138
139 PROCEDURE enroll_cart(
140 p_api_version IN NUMBER,
141 p_init_msg_list IN VARCHAR2,
142 p_commit IN VARCHAR2,
143 p_person_number IN VARCHAR2,
144 p_career IN VARCHAR2,
145 p_program_code IN VARCHAR2,
146 p_term_alt_code IN VARCHAR2,
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_msg_count OUT NOCOPY NUMBER,
149 x_msg_data OUT NOCOPY VARCHAR2) AS
150 /*
151 || Created By : Nishikant
152 || Created On : 23JAN2003
153 || Purpose : The procedure will enroll all the sections in the cart
154 || for students selected program/career and term.
155 ||
156 ||
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || stutta 11-Feb-2004 Passing new parameter p_enrolled_dt as SYSDATE in
161 || call to validate_enroll_validate.
162 || bdeviset 23-Aug-2006 Passed extra param p_ss_session_id for enrp_ss_val_person_step added as part of bug# 5306874
163 || (reverse chronological order - newest change first)
164 */
165 l_api_name VARCHAR2(30) := 'ENROLL_CART';
166
167 l_person_id igs_pe_person_base_v.person_id%TYPE;
168 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
169 l_cal_type igs_ca_inst.cal_type%TYPE;
170 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
171 l_primary_code igs_ps_ver.course_cd%TYPE;
172 l_primary_version igs_ps_ver.version_number%TYPE;
173 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
174 l_error_message VARCHAR2(2000);
175 l_ret_status VARCHAR2(6);
176 l_message_count NUMBER;
177 l_message_data VARCHAR2(2000) := NULL;
178 l_deny_warn igs_en_cpd_ext.notification_flag%TYPE;
179 l_dummy_bool BOOLEAN;
180 l_step_eval_result VARCHAR2(6);
181 l_enr_method_type igs_en_method_type.enr_method_type%TYPE;
182 l_conc_uoo_id VARCHAR2(2000);
183
184 CURSOR c_us_in_cart IS
185 SELECT sua.uoo_id,
186 sua.enr_method_type,
187 sua.cart
188 FROM igs_en_su_attempt sua
189 WHERE sua.person_id = l_person_id
190 AND sua.course_cd = l_primary_code
191 AND (sua.cal_type, sua.ci_sequence_number) IN (SELECT teach_cal_type,teach_ci_sequence_number
192 FROM igs_ca_load_to_teach_v
193 WHERE load_cal_type = l_cal_type
194 AND load_ci_sequence_number = l_ci_sequence_number)
195 AND sua.unit_attempt_status IN ('INVALID','UNCONFIRM') ;
196
197 BEGIN
198
199 SAVEPOINT ENROL_CART_PUB;
200
201 -- Standard call to check for call compatibility.
202 IF NOT FND_API.COMPATIBLE_API_CALL (1.0,
203 p_api_version,
204 l_api_name,
205 g_pkg_name ) THEN
206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
207 END IF;
208
209 -- Initialize message list if p_init_msg_list is set to TRUE.
210 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
211 FND_MSG_PUB.INITIALIZE;
212 END IF;
213
214 -- Initialize API return status to success
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 --Validate the input parameters and if valid, also fetch the internal calculated
218 --values. Pass the Validation level as No Call Number.
219 igs_en_gen_017.enrp_validate_input_parameters(
220 p_person_number => p_person_number,
221 p_career => p_career,
222 p_program_code => p_program_code,
223 p_term_alt_code => p_term_alt_code,
224 p_call_number => NULL,
225 p_validation_level => 'NOCALLNUM',
226 p_person_id => l_person_id,
227 p_person_type => l_person_type,
228 p_cal_type => l_cal_type,
229 p_ci_sequence_number => l_ci_sequence_number,
230 p_primary_code => l_primary_code,
231 p_primary_version => l_primary_version,
232 p_uoo_id => l_uoo_id,
233 p_error_message => l_message_data,
234 p_ret_status => l_ret_status );
235
236 --If there is any invalid parameter then log it and return with error status
237 IF l_ret_status = 'FALSE' THEN
238 igs_en_gen_017.enrp_msg_string_to_list (
239 p_message_string => l_message_data,
240 p_delimiter => ';',
241 p_init_msg_list => FND_API.G_FALSE,
242 x_message_count => x_msg_count,
243 x_message_data => x_msg_data);
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 RETURN;
246 END IF;
247
248 l_ret_status := NULL;
249 l_error_message := NULL;
250 --If the parameters are valid, then call the below procedure to evaluate Person Steps.
251 igs_ss_en_wrappers.enrp_ss_val_person_step(
252 p_person_id => l_person_id,
253 p_person_type => l_person_type,
254 p_load_cal_type => l_cal_type,
255 p_load_ci_sequence_number => l_ci_sequence_number,
256 p_program_cd => l_primary_code,
257 p_program_version => l_primary_version,
258 p_message_name => l_error_message,
259 p_deny_warn => l_deny_warn,
260 p_step_eval_result => l_step_eval_result,
261 p_calling_obj => 'JOB',
262 p_create_warning => 'N',
263 p_ss_session_id => NULL );
264
265 --If step evaluation result is FALSE then log the error message and return with error status
266 IF l_step_eval_result = 'FALSE' THEN
267 igs_en_gen_017.enrp_msg_string_to_list (
268 p_message_string => l_error_message,
269 p_delimiter => ';',
270 p_init_msg_list => FND_API.G_FALSE,
271 x_message_count => l_message_count,
272 x_message_data => l_message_data);
273 x_return_status := FND_API.G_RET_STS_ERROR;
274 x_msg_data := l_message_data;
275 x_msg_count := l_message_count;
276 RETURN;
277 END IF;
278
279 l_ret_status := NULL;
280 l_error_message := NULL;
281 --Fetch the enrollment method
282 igs_en_gen_017.enrp_get_enr_method(
283 p_enr_method_type => l_enr_method_type,
284 p_error_message => l_error_message,
285 p_ret_status => l_ret_status);
286
287 --If error occured during fetching then log it end Return with error status
288 IF l_ret_status = 'FALSE' THEN
289 igs_en_gen_017.enrp_msg_string_to_list (
290 p_message_string => l_error_message,
291 p_delimiter => ';',
292 p_init_msg_list => FND_API.G_FALSE,
293 x_message_count => l_message_count,
294 x_message_data => l_message_data);
295 x_return_status := FND_API.G_RET_STS_ERROR;
296 x_msg_data := l_message_data;
297 x_msg_count := l_message_count;
298 RETURN;
299 END IF;
300
301 --Loop through all the unit sections in the cart
302 FOR l_us_in_cart IN c_us_in_cart LOOP
303 --For each Unit Section check the Cart is null or not.
304 --If the cart is null then this indicates that the unit was added by Batch Pre Enrollment
305 --and the unit step validations are to be performed
306 IF l_us_in_cart.cart IS NULL THEN
307 l_deny_warn := NULL;
308 l_dummy_bool:= igs_en_enroll_wlst.validate_unit_steps (
309 p_person_id => l_person_id,
310 p_cal_type => l_cal_type,
311 p_ci_sequence_number => l_ci_sequence_number,
312 p_uoo_id => l_us_in_cart.uoo_id,
313 p_course_cd => l_primary_code,
314 p_enr_method_type => l_enr_method_type,
315 p_message_name => l_error_message,
316 p_deny_warn => l_deny_warn,
317 p_calling_obj => 'JOB'
318 );
319 --If the unit step validation returns Deny then log the error message and return with Error status
320 IF l_deny_warn = 'DENY' THEN
321 igs_en_gen_017.enrp_msg_string_to_list (
322 p_message_string => l_error_message,
323 p_delimiter => ';',
324 p_init_msg_list => FND_API.G_FALSE,
325 x_message_count => l_message_count,
326 x_message_data => l_message_data);
327
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 x_msg_data := l_message_data;
330 x_msg_count := l_message_count;
331 RETURN;
332 ELSE
333 --If the unit step validation not returns Deny then concatenate the uoo_id to the local_variable l_conc_uoo_id
334 IF l_conc_uoo_id IS NULL THEN
335 l_conc_uoo_id := l_us_in_cart.uoo_id;
336 ELSE
337 l_conc_uoo_id := l_conc_uoo_id ||','|| l_us_in_cart.uoo_id;
338 END IF;
339 END IF;
340 ELSE
341 --If the cart is having any value then concatenate the uoo_id to the local variable l_conc_uoo_id
342 IF l_conc_uoo_id IS NULL THEN
343 l_conc_uoo_id := l_us_in_cart.uoo_id;
344 ELSE
345 l_conc_uoo_id := l_conc_uoo_id ||','|| l_us_in_cart.uoo_id;
346 END IF;
347 END IF;
348 END LOOP;
349
350 --Perform the program steps validation and change the status of unit section in the cart to Enrolled status.
351 --Pass the concatenated uoo_ids to the procedure, so that it will enroll all those unit sections.
352 igs_ss_en_wrappers.validate_enroll_validate (
353 p_person_id => l_person_id,
354 p_load_cal_type => l_cal_type,
355 p_load_ci_sequence_number => l_ci_sequence_number,
356 p_uoo_ids => l_conc_uoo_id,
357 p_program_cd => l_primary_code,
358 p_message_name => l_message_data,
359 p_deny_warn => l_deny_warn,
360 p_return_status => l_ret_status,
361 p_enr_method => l_enr_method_type,
362 p_enrolled_dt => SYSDATE);
363
364 --If return status is Deny then log the error message and retrun with error status
365 IF l_ret_status = 'DENY' THEN
366 igs_en_gen_017.enrp_msg_string_to_list (
367 p_message_string => l_message_data,
368 p_delimiter => ';',
369 p_init_msg_list => FND_API.G_FALSE,
370 x_message_count => l_message_count,
371 x_message_data => l_message_data);
372
373 x_return_status := FND_API.G_RET_STS_ERROR;
374 x_msg_data := l_message_data;
375 x_msg_count := l_message_count;
376 ELSE
377 IF p_commit = FND_API.G_TRUE THEN
378 COMMIT WORK;
379 END IF;
380 END IF;
381
382 EXCEPTION
383 WHEN FND_API.G_EXC_ERROR THEN
384 ROLLBACK TO ENROL_CART_PUB;
385 x_return_status := FND_API.G_RET_STS_ERROR ;
386 FND_MSG_PUB.COUNT_AND_GET (
387 p_count => x_msg_count,
388 p_data => x_msg_data );
389
390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
391 ROLLBACK TO ENROL_CART_PUB;
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
393 FND_MSG_PUB.COUNT_AND_GET (
394 p_count => x_msg_count,
395 p_data => x_msg_data );
396
397 WHEN OTHERS THEN
398 ROLLBACK TO ENROL_CART_PUB;
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
401 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
402 FND_MSG_PUB.ADD;
403 FND_MSG_PUB.COUNT_AND_GET (
404 p_count => x_msg_count,
405 p_data => x_msg_data );
406 END enroll_cart;
407
408 PROCEDURE clean_up_cart(
409 p_api_version IN NUMBER ,
410 p_init_msg_list IN VARCHAR2,
411 p_commit IN VARCHAR2,
412 p_person_number IN VARCHAR2,
413 p_career IN VARCHAR2,
414 p_program_code IN VARCHAR2,
415 p_term_alt_code IN VARCHAR2,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER ,
418 x_msg_data OUT NOCOPY VARCHAR2) IS
419 /*
420 || Created By : Nalin Kumar
421 || Created On : 16-Jan-2003
422 || Purpose : To clean up the cart.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 */
427 l_api_name CONSTANT VARCHAR2(30) := 'clean_up_cart';
428 l_api_version CONSTANT NUMBER := 1.0;
429 l_messaage_count NUMBER(15);
430 l_message_data VARCHAR2(1000);
431 l_error_message VARCHAR2(1000);
432 l_return_status VARCHAR2(10);
433
434 l_person_id igs_pe_person.person_id%TYPE;
435 l_person_type igs_pe_person_types.person_type_code%TYPE;
436 l_cal_type igs_ca_inst.cal_type%TYPE;
437 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
438 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
439 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
440 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
441
442 -- Cursor to fetch the row_id, to clean up the cart.
443 CURSOR cur_sua (cp_person_id igs_pe_person.person_id%TYPE,
444 cp_program_cd igs_en_su_attempt.course_cd%TYPE,
445 cp_version_number igs_en_su_attempt.version_number%TYPE)IS
446 SELECT sua.row_id
447 FROM igs_en_su_attempt sua
448 WHERE unit_attempt_status = 'UNCONFIRM' AND
449 cart = 'I' AND
450 person_id = cp_person_id AND
451 course_cd = cp_program_cd AND
452 version_number = cp_version_number;
453 BEGIN
454 --Standard start of API savepoint
455 SAVEPOINT clean_up_cart;
456
457 --Standard call to check for call compatibility.
458 IF NOT FND_API.compatible_api_call(l_api_version,
459 p_api_version,
460 l_api_name,
461 G_PKG_NAME) THEN
462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
463 END IF;
464
465 --Initialize message list if p_init_msg_list is set to TRUE.
466 IF FND_API.to_Boolean(p_init_msg_list) THEN
467 FND_MSG_PUB.initialize;
468 END IF;
469
470 --Initialize API return status to success.
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 l_return_status := 'TRUE';
473
474 --
475 -- Main code logic begins
476 --
477
478 -- Validate the input parameters.
479 igs_en_gen_017.enrp_validate_input_parameters(
480 p_person_number => p_person_number ,
481 p_career => p_career ,
482 p_program_code => p_program_code ,
483 p_term_alt_code => p_term_alt_code ,
484 p_call_number => NULL ,
485 p_validation_level => 'NOCALLNUM' ,
486 p_person_id => l_person_id ,
487 p_person_type => l_person_type ,
488 p_cal_type => l_cal_type ,
489 p_ci_sequence_number => l_ci_sequence_number,
490 p_primary_code => l_primary_code ,
491 p_primary_version => l_primary_version ,
492 p_uoo_id => l_uoo_id ,
493 p_error_message => l_error_message ,
494 p_ret_status => l_return_status);
495
496 IF l_return_status = 'FALSE' THEN
497 -- Add to msg stack
498 igs_en_gen_017.enrp_msg_string_to_list(
499 p_message_string => l_error_message,
500 p_delimiter => ';',
501 p_init_msg_list => FND_API.G_FALSE,
502 x_message_count => l_messaage_count,
503 x_message_data => l_message_data);
504
505 x_return_status := FND_API.G_RET_STS_ERROR;
506 x_msg_count := l_messaage_count;
507 x_msg_data := l_message_data;
508 RETURN;
509 END IF;
510
511 -- Loop through the records and call the delete row to drop the cart.
512 FOR rec_sua IN cur_sua(l_person_id, l_primary_code, l_primary_version) LOOP
513 igs_en_su_attempt_pkg.delete_row(rec_sua.row_id);
514 END LOOP;
515
516 --Standard check of p_commit.
517 IF FND_API.to_Boolean(p_commit) THEN
518 commit;
519 END IF;
520
521 --Standard call to get message count and if count is 1, get message info.
522 FND_MSG_PUB.Count_And_Get(
523 p_count => l_messaage_count,
524 p_data => l_message_data);
525
526 EXCEPTION
527 WHEN FND_API.G_EXC_ERROR THEN
528 ROLLBACK TO clean_up_cart;
529 x_return_status := FND_API.G_RET_STS_ERROR ;
530 FND_MSG_PUB.COUNT_AND_GET (
531 p_count => x_msg_count,
532 p_data => l_message_data );
533
534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
535 ROLLBACK TO clean_up_cart;
536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537 FND_MSG_PUB.COUNT_AND_GET (
538 p_count => x_msg_count,
539 p_data => l_message_data );
540
541 WHEN OTHERS THEN
542 ROLLBACK TO clean_up_cart;
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
544 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
545 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
546 END IF;
547 FND_MSG_PUB.COUNT_AND_GET (
548 p_count => x_msg_count,
549 p_data => l_message_data );
550 END clean_up_cart;
551
552 PROCEDURE drop_all_section(
553 p_api_version IN NUMBER ,
554 p_init_msg_list IN VARCHAR2,
555 p_commit IN VARCHAR2,
556 p_person_number IN VARCHAR2,
557 p_career IN VARCHAR2,
558 p_program_code IN VARCHAR2,
559 p_term_alt_code IN VARCHAR2,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER ,
562 x_msg_data OUT NOCOPY VARCHAR2 ) IS
563 /*
564 || Created By : Nalin Kumar
565 || Created On : 16-Jan-2003
566 || Purpose : Drop all the sections of students for the career/program and term.
567 || Known limitations, enhancements or remarks :
568 || Change History :
569 || Who When What
570 */
571 l_api_name CONSTANT VARCHAR2(30) := 'drop_all_section';
572 l_api_version CONSTANT NUMBER := 1.0;
573
574 l_cnst_all CONSTANT VARCHAR2(5) := 'ALL';
575 l_messaage_count NUMBER(15);
576 l_message_data VARCHAR2(1000);
577 l_return_status VARCHAR2(10);
578 l_error_message VARCHAR2(1000);
579 BEGIN
580 --Standard start of API savepoint
581 SAVEPOINT drop_all_section;
582
583 --Standard call to check for call compatibility.
584 IF NOT FND_API.Compatible_API_Call(l_api_version,
585 p_api_version,
586 l_api_name,
587 G_PKG_NAME) THEN
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590
591 --Initialize message list if p_init_msg_list is set to TRUE.
592 IF FND_API.to_Boolean(p_init_msg_list) THEN
593 FND_MSG_PUB.initialize;
594 END IF;
595
596 --Initialize API return status to success.
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598 l_return_status := 'TRUE';
599
600 -- main code logic begins
601 igs_en_gen_017.drop_section(
602 p_person_number => p_person_number,
603 p_career => p_career ,
604 p_program_code => p_program_code ,
605 p_term_alt_code => p_term_alt_code,
606 p_call_number => NULL , --This should be null because we want to drop all units.
607 p_action => l_cnst_all ,
608 p_drop_reason => NULL ,
609 p_adm_status => NULL ,
610 p_error_message => l_error_message,
611 p_return_stat => l_return_status);
612
613 IF l_return_status = 'FALSE' THEN
614 -- add to msg stack
615 igs_en_gen_017.enrp_msg_string_to_list(
616 p_message_string => l_error_message,
617 p_init_msg_list => FND_API.G_FALSE,
618 x_message_count => l_messaage_count,
619 x_message_data => l_message_data);
620
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 x_msg_count := l_messaage_count;
623 x_msg_data := l_message_data;
624 END IF;
625
626 --Standard check of p_commit.
627 IF FND_API.to_Boolean(p_commit) THEN
628 commit;
629 END IF;
630
631 --Standard call to get message count and if count is 1, get message info.
632 FND_MSG_PUB.Count_And_Get(
633 p_count => l_messaage_count,
634 p_data => l_message_data);
635
636 EXCEPTION
637 WHEN FND_API.G_EXC_ERROR THEN
638 ROLLBACK TO drop_all_section;
639 x_return_status := FND_API.G_RET_STS_ERROR ;
640 FND_MSG_PUB.COUNT_AND_GET (
641 p_count => x_msg_count,
642 p_data => l_message_data );
643
644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
645 ROLLBACK TO drop_all_section;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
647 FND_MSG_PUB.COUNT_AND_GET (
648 p_count => x_msg_count,
649 p_data => l_message_data );
650
651 WHEN OTHERS THEN
652 ROLLBACK TO drop_all_section;
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
654 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
655 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
656 END IF;
657 FND_MSG_PUB.COUNT_AND_GET (
658 p_count => x_msg_count,
659 p_data => l_message_data );
660 END drop_all_section;
661
662 PROCEDURE drop_section_by_call_number(
663 p_api_version IN NUMBER ,
664 p_init_msg_list IN VARCHAR2,
665 p_commit IN VARCHAR2,
666 p_person_number IN VARCHAR2,
667 p_career IN VARCHAR2,
668 p_program_code IN VARCHAR2,
669 p_term_alt_code IN VARCHAR2,
670 p_call_number IN NUMBER ,
671 p_drop_reason IN VARCHAR2,
672 p_adm_status IN VARCHAR2,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER ,
675 x_msg_data OUT NOCOPY VARCHAR2) IS
676 /*
677 || Created By : Nalin Kumar
678 || Created On : 16-Jan-2003
679 || Purpose : The procedure will drop the section indicated by the student for the selected program/career and term.
680 || Known limitations, enhancements or remarks :
681 || Change History :
682 || Who When What
683 */
684 l_api_name CONSTANT VARCHAR2(30) := 'drop_section_by_call_number';
685 l_api_version CONSTANT NUMBER := 1.0;
686
687 l_cnst_one CONSTANT VARCHAR2(5) := 'ONE';
688 l_messaage_count NUMBER(15);
689 l_message_data VARCHAR2(1000);
690 l_return_status VARCHAR2(10);
691 l_error_message VARCHAR2(1000);
692 BEGIN
693 --Standard start of API savepoint
694 SAVEPOINT drop_section_by_call_number;
695
696 --Standard call to check for call compatibility.
697 IF NOT FND_API.Compatible_API_Call(l_api_version,
698 p_api_version,
699 l_api_name,
700 G_PKG_NAME) THEN
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703
704 --Initialize message list if p_init_msg_list is set to TRUE.
705 IF FND_API.to_Boolean(p_init_msg_list) THEN
706 FND_MSG_PUB.initialize;
707 END IF;
708
709 --Initialize API return status to success.
710 x_return_status := FND_API.G_RET_STS_SUCCESS;
711 l_return_status := 'TRUE';
712
713 -- main code logic begins
714 igs_en_gen_017.drop_section(
715 p_person_number => p_person_number,
716 p_career => p_career ,
717 p_program_code => p_program_code ,
718 p_term_alt_code => p_term_alt_code,
719 p_call_number => p_call_number ,
720 p_action => l_cnst_one ,
721 p_drop_reason => p_drop_reason ,
722 p_adm_status => p_adm_status ,
723 p_error_message => l_error_message,
724 p_return_stat => l_return_status);
725
726 IF l_return_status = 'FALSE' THEN
727 -- add to msg stack
728 igs_en_gen_017.enrp_msg_string_to_list(
729 p_message_string => l_error_message,
730 p_init_msg_list => FND_API.G_FALSE,
731 x_message_count => l_messaage_count,
732 x_message_data => l_message_data
733 );
734 x_msg_count := l_messaage_count;
735 x_msg_data := l_message_data;
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 END IF;
738
739 --Standard check of p_commit.
740 IF FND_API.to_Boolean(p_commit) THEN
741 commit;
742 END IF;
743
744 --Standard call to get message count and if count is 1, get message info.
745 FND_MSG_PUB.Count_And_Get(
746 p_count => l_messaage_count,
747 p_data => l_message_data);
748
749 EXCEPTION
750 WHEN FND_API.G_EXC_ERROR THEN
751 ROLLBACK TO drop_section_by_call_number;
752 x_return_status := FND_API.G_RET_STS_ERROR ;
753 FND_MSG_PUB.COUNT_AND_GET (
754 p_count => x_msg_count,
755 p_data => l_message_data );
756
757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
758 ROLLBACK TO drop_section_by_call_number;
759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
760 FND_MSG_PUB.COUNT_AND_GET (
761 p_count => x_msg_count,
762 p_data => l_message_data );
763
764 WHEN OTHERS THEN
765 ROLLBACK TO drop_section_by_call_number;
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
767 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
768 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
769 END IF;
770 FND_MSG_PUB.COUNT_AND_GET (
771 p_count => x_msg_count,
772 p_data => l_message_data );
773 END drop_section_by_call_number;
774
775 PROCEDURE evaluate_person_steps(
776 p_api_version IN NUMBER,
777 p_init_msg_list IN VARCHAR2,
778 p_commit IN VARCHAR2,
779 p_person_number IN VARCHAR2,
780 p_career IN VARCHAR2,
781 p_program_code IN VARCHAR2,
782 p_term_alt_code IN VARCHAR2,
783 x_return_status OUT NOCOPY VARCHAR2,
784 x_msg_count OUT NOCOPY NUMBER,
785 x_msg_data OUT NOCOPY VARCHAR2) AS
786
787 /*
788 || Created By : Nishikant
789 || Created On : 20JAN2003
790 || Purpose : The procedure will evaluate the person steps setup in the system for the
791 || student to perform enrollment related activities.
792 || This API is provided to give IVR an option that they can validate these
793 || at start of Enrollment and if failing then do not allow the student to
794 || proceed further with his enrollment activities/functions.
795 ||
796 || Known limitations, enhancements or remarks :
797 || Change History :
798 || Who When What
799 || (reverse chronological order - newest change first)
800 */
801
802 l_api_name CONSTANT VARCHAR2(30) := 'PERSON_STEPS';
803
804 l_person_id igs_pe_person_base_v.person_id%TYPE;
805 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
806 l_cal_type igs_ca_inst.cal_type%TYPE;
807 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
808 l_primary_code igs_ps_ver.course_cd%TYPE;
809 l_primary_version igs_ps_ver.version_number%TYPE;
810 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
811 l_message_data VARCHAR2(2000);
812 l_ret_status VARCHAR2(6);
813 l_deny_warn igs_en_cpd_ext_v.notification_flag%TYPE;
814 l_step_eval_result VARCHAR2(6);
815
816 BEGIN
817 -- Standard call to check for call compatibility.
818 IF NOT FND_API.COMPATIBLE_API_CALL (1.0,
819 p_api_version,
820 l_api_name,
821 g_pkg_name ) THEN
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824
825 -- Initialize message list if p_init_msg_list is set to TRUE.
826 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
827 FND_MSG_PUB.INITIALIZE;
828 END IF;
829
830 -- Initialize API return status to success
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832
833 --Validate the input parameters and if valid would also fetch the internal calculated
834 --values. Validation level here is No Call Number.
835 igs_en_gen_017.enrp_validate_input_parameters(
836 p_person_number => p_person_number,
837 p_career => p_career,
838 p_program_code => p_program_code,
839 p_term_alt_code => p_term_alt_code,
840 p_call_number => NULL,
841 p_validation_level => 'NOCALLNUM',
842 p_person_id => l_person_id,
843 p_person_type => l_person_type,
844 p_cal_type => l_cal_type,
845 p_ci_sequence_number => l_ci_sequence_number,
846 p_primary_code => l_primary_code,
847 p_primary_version => l_primary_version,
848 p_uoo_id => l_uoo_id,
849 p_error_message => l_message_data,
850 p_ret_status => l_ret_status );
851
852 --If there is any invalid parameter then log the error message and return with error status
853 IF l_ret_status = 'FALSE' THEN
854 igs_en_gen_017.enrp_msg_string_to_list (
855 p_message_string => l_message_data,
856 p_delimiter => ';',
857 p_init_msg_list => FND_API.G_FALSE,
858 x_message_count => x_msg_count,
859 x_message_data => x_msg_data);
860 x_return_status := FND_API.G_RET_STS_ERROR;
861 ELSE
862 --If the parameters are valid, then call the below procedure to evaluate Person Steps.
863 igs_ss_en_wrappers.enrp_ss_val_person_step(
864 p_person_id => l_person_id,
865 p_person_type => l_person_type,
866 p_load_cal_type => l_cal_type,
867 p_load_ci_sequence_number => l_ci_sequence_number,
868 p_program_cd => l_primary_code,
869 p_program_version => l_primary_version,
870 p_message_name => l_message_data,
871 p_deny_warn => l_deny_warn,
872 p_step_eval_result => l_step_eval_result,
873 p_calling_obj => 'JOB',
874 p_create_warning => 'N',
875 p_ss_session_id => NULL);
876
877 --If step evaluation result is FALSE then log the error message and return with error status
878 IF l_step_eval_result = 'FALSE' THEN
879 igs_en_gen_017.enrp_msg_string_to_list (
880 p_message_string => l_message_data,
881 p_delimiter => ';',
882 p_init_msg_list => FND_API.G_FALSE,
883 x_message_count => x_msg_count,
884 x_message_data => x_msg_data);
885 x_return_status := FND_API.G_RET_STS_ERROR;
886
887 END IF;
888 END IF;
889 IF FND_API.TO_BOOLEAN( p_commit ) THEN
890 COMMIT WORK;
891 END IF;
892
893 EXCEPTION
894 WHEN FND_API.G_EXC_ERROR THEN
895 x_return_status := FND_API.G_RET_STS_ERROR ;
896 FND_MSG_PUB.COUNT_AND_GET (
897 p_count => x_msg_count,
898 p_data => x_msg_data );
899
900 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
902 FND_MSG_PUB.COUNT_AND_GET (
903 p_count => x_msg_count,
904 p_data => x_msg_data );
905
906 WHEN OTHERS THEN
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
909 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
910 FND_MSG_PUB.ADD;
911 FND_MSG_PUB.COUNT_AND_GET (
912 p_count => x_msg_count,
913 p_data => x_msg_data );
914 END evaluate_person_steps;
915
916 PROCEDURE list_schedule(
917 p_api_version IN NUMBER ,
918 p_init_msg_list IN VARCHAR2,
919 p_commit IN VARCHAR2,
920 p_person_number IN VARCHAR2,
921 p_career IN VARCHAR2,
922 p_program_code IN VARCHAR2,
923 p_term_alt_code IN VARCHAR2,
924 x_schedule_tbl OUT NOCOPY schedule_tbl_type,
925 x_return_status OUT NOCOPY VARCHAR2,
926 x_msg_count OUT NOCOPY NUMBER ,
927 x_msg_data OUT NOCOPY VARCHAR2) IS
928 /*
929 || Created By : Nalin Kumar
930 || Created On : 16-Jan-2003
931 || Purpose : Returns the student Schedule Details
932 || Known limitations, enhancements or remarks :
933 || Change History :
934 || Who When What
935 || kkillams 29-04-2003 Modified the cur_schedule_type cursor due to change in the pk of
936 || Student unit attempt w.r.t. bug number 2829262
937 */
938 l_api_name CONSTANT VARCHAR2(30) := 'list_schedule';
939 l_api_version CONSTANT NUMBER := 1.0;
940 l_messaage_count NUMBER(15);
941 l_message_data VARCHAR2(1000);
942 l_error_message VARCHAR2(1000);
943 l_return_status VARCHAR2(10);
944
945 l_person_id igs_pe_person.person_id%TYPE;
946 l_person_type igs_pe_person_types.person_type_code%TYPE;
947 l_cal_type igs_ca_inst.cal_type%TYPE;
948 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
949 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
950 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
951 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
952 l_rec_count NUMBER(15);
953 l_status_not_shown VARCHAR2(100);
954
955 CURSOR cur_schedule_type (
956 cp_person_id igs_pe_person.person_id%TYPE,
957 cp_program_cd igs_en_su_attempt.course_cd%TYPE,
958 cp_load_cal_type igs_ca_inst.cal_type%TYPE,
959 cp_load_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
960 cp_unit_status_not_shown VARCHAR2)IS
961 SELECT
962 sua.unit_cd unit_code ,
963 sua1.unit_class unit_class ,
964 sua.version_number unit_version ,
965 ci.alternate_code teach_alternate_code ,
966 sua.call_number call_number ,
967 SUBSTR(sua.gradingschema,1,10) grading_schema ,
968 sua.creditpoints credit_points ,
969 sua.unit_attempt_status unit_attempt_status ,
970 sua.uas_meaning uas_meaning ,
971 sua.uoo_id uoo_id ,
972 sua.administrative_priority administrative_priority
973 FROM
974 igs_ss_en_sua_dtls_v sua,
975 igs_en_su_attempt sua1,
976 igs_ca_inst ci
977 WHERE
978 ci.cal_type = sua.cal_type AND
979 ci.sequence_number = sua.ci_sequence_number AND
980 sua.person_id = cp_person_id AND
981 sua.course_cd = cp_program_cd AND
982 (sua.cal_type ,sua.ci_sequence_number) IN
983 (SELECT teach_cal_type,teach_ci_sequence_number
984 FROM igs_ca_load_to_teach_v
985 WHERE load_cal_type = cp_load_cal_type AND
986 load_ci_sequence_number = cp_load_ci_sequence_number) AND
987 sua.unit_attempt_status NOT IN (cp_unit_status_not_shown) AND
988 sua1.person_id = sua.person_id AND
989 sua1.course_cd = sua.course_cd AND
990 sua1.uoo_id = sua.uoo_id;
991 BEGIN
992 --Standard start of API savepoint
993 SAVEPOINT list_schedule;
994
995 --Standard call to check for call compatibility.
996 IF NOT FND_API.Compatible_API_Call(l_api_version,
997 p_api_version,
998 l_api_name,
999 G_PKG_NAME) THEN
1000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001 END IF;
1002
1003 --Initialize message list if p_init_msg_list is set to TRUE.
1004 IF FND_API.to_Boolean(p_init_msg_list) THEN
1005 FND_MSG_PUB.initialize;
1006 END IF;
1007
1008 --Initialize API return status to success.
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010 l_return_status := 'TRUE';
1011 l_rec_count := 0;
1012
1013 --
1014 -- Main code logic begins
1015 --
1016
1017 -- Validate the input parameters.
1018 igs_en_gen_017.enrp_validate_input_parameters(
1019 p_person_number => p_person_number ,
1020 p_career => p_career ,
1021 p_program_code => p_program_code ,
1022 p_term_alt_code => p_term_alt_code ,
1023 p_call_number => NULL ,
1024 p_validation_level => 'NOCALLNUM' ,
1025 p_person_id => l_person_id ,
1026 p_person_type => l_person_type ,
1027 p_cal_type => l_cal_type ,
1028 p_ci_sequence_number => l_ci_sequence_number,
1029 p_primary_code => l_primary_code ,
1030 p_primary_version => l_primary_version ,
1031 p_uoo_id => l_uoo_id ,
1032 p_error_message => l_error_message ,
1033 p_ret_status => l_return_status);
1034
1035 IF l_return_status = 'FALSE' THEN
1036 -- Add to msg stack
1037 igs_en_gen_017.enrp_msg_string_to_list(
1038 p_message_string => l_error_message,
1039 p_init_msg_list => FND_API.G_FALSE,
1040 x_message_count => l_messaage_count,
1041 x_message_data => l_message_data
1042 );
1043 x_return_status := FND_API.G_RET_STS_ERROR;
1044 x_msg_count := l_messaage_count;
1045 x_msg_data := l_message_data;
1046 RETURN;
1047 END IF;
1048
1049 IF NVL(FND_PROFILE.VALUE('IGS_EN_DISP_DUNIT_STUD'), 'N') = 'Y' THEN
1050 l_status_not_shown := '''UNCONFIRM''';
1051 ELSE
1052 l_status_not_shown := '''UNCONFIRM'', ''DROPPED''';
1053 END IF;
1054
1055 FOR rec_schedule_type IN cur_schedule_type(l_person_id, l_primary_code, l_cal_type, l_ci_sequence_number, l_status_not_shown) LOOP
1056 l_rec_count := l_rec_count + 1;
1057 x_schedule_tbl(l_rec_count).p_unit_code := rec_schedule_type.unit_code ;
1058 x_schedule_tbl(l_rec_count).p_unit_class := rec_schedule_type.unit_class ;
1059 x_schedule_tbl(l_rec_count).p_unit_version := rec_schedule_type.unit_version ;
1060 x_schedule_tbl(l_rec_count).p_teach_alternate_code := rec_schedule_type.teach_alternate_code ;
1061 x_schedule_tbl(l_rec_count).p_call_Number := rec_schedule_type.call_Number ;
1062 x_schedule_tbl(l_rec_count).p_grading_Schema := rec_schedule_type.grading_Schema ;
1063 x_schedule_tbl(l_rec_count).p_credit_points := rec_schedule_type.credit_points ;
1064 x_schedule_tbl(l_rec_count).p_unit_attempt_status := rec_schedule_type.unit_attempt_status ;
1065 x_schedule_tbl(l_rec_count).p_uas_meaning := rec_schedule_type.uas_meaning ;
1066 x_schedule_tbl(l_rec_count).p_uoo_id := rec_schedule_type.uoo_id ;
1067 x_schedule_tbl(l_rec_count).p_administrative_priority := rec_schedule_type.administrative_priority;
1068 END LOOP;
1069
1070 --If no records found then returm IGS_EN_NO_SECTION error message with ERROR status.
1071 IF l_rec_count = 0 THEN
1072 -- Add to msg stack
1073 igs_en_gen_017.enrp_msg_string_to_list(
1074 p_message_string => 'IGS_EN_NO_SECTION',
1075 p_init_msg_list => FND_API.G_FALSE,
1076 x_message_count => l_messaage_count,
1077 x_message_data => l_message_data);
1078
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 x_msg_count := l_messaage_count;
1081 x_msg_data := l_message_data;
1082 END IF;
1083
1084 --Standard check of p_commit.
1085 IF FND_API.to_Boolean(p_commit) THEN
1086 commit;
1087 END IF;
1088
1089 --Standard call to get message count and if count is 1, get message info.
1090 FND_MSG_PUB.Count_And_Get(
1091 p_count => l_messaage_count,
1092 p_data => l_message_data);
1093
1094 EXCEPTION
1095 WHEN FND_API.G_EXC_ERROR THEN
1096 ROLLBACK TO list_schedule;
1097 x_return_status := FND_API.G_RET_STS_ERROR ;
1098 FND_MSG_PUB.COUNT_AND_GET (
1099 p_count => x_msg_count,
1100 p_data => x_msg_data );
1101
1102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1103 ROLLBACK TO list_schedule;
1104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1105 FND_MSG_PUB.COUNT_AND_GET (
1106 p_count => x_msg_count,
1107 p_data => x_msg_data );
1108
1109 WHEN OTHERS THEN
1110 ROLLBACK TO list_schedule;
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1112 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1113 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
1114 END IF;
1115 FND_MSG_PUB.COUNT_AND_GET (
1116 p_count => x_msg_count,
1117 p_data => x_msg_data );
1118 END list_schedule;
1119
1120 PROCEDURE list_section_in_cart(
1121 p_api_version IN NUMBER ,
1122 p_init_msg_list IN VARCHAR2,
1123 p_commit IN VARCHAR2,
1124 p_person_number IN VARCHAR2,
1125 p_career IN VARCHAR2,
1126 p_program_code IN VARCHAR2,
1127 p_term_alt_code IN VARCHAR2,
1128 x_call_number_tbl OUT NOCOPY call_number_tbl_type,
1129 x_return_status OUT NOCOPY VARCHAR2,
1130 x_msg_count OUT NOCOPY NUMBER ,
1131 x_msg_data OUT NOCOPY VARCHAR2) IS
1132 /*
1133 || Created By : Nalin Kumar
1134 || Created On : 16-Jan-2003
1135 || Purpose : Returns the cart contents of student
1136 || Known limitations, enhancements or remarks :
1137 || Change History :
1138 || Who When What
1139 */
1140 l_api_name CONSTANT VARCHAR2(30) := 'list_section_in_cart';
1141 l_api_version CONSTANT NUMBER := 1.0;
1142 l_messaage_count NUMBER(15);
1143 l_message_data VARCHAR2(1000);
1144 l_error_message VARCHAR2(1000);
1145 l_return_status VARCHAR2(10);
1146
1147 l_person_id igs_pe_person.person_id%TYPE;
1148 l_person_type igs_pe_person_types.person_type_code%TYPE;
1149 l_cal_type igs_ca_inst.cal_type%TYPE;
1150 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1151 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
1152 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
1153 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
1154 l_rec_count NUMBER(15);
1155
1156 CURSOR cur_call_num (
1157 cp_person_id igs_pe_person.person_id%TYPE,
1158 cp_program_cd igs_en_su_attempt.course_cd%TYPE,
1159 cp_load_cal_type igs_ca_inst.cal_type%TYPE,
1160 cp_load_ci_sequence_number igs_ca_inst.sequence_number%TYPE)IS
1161 SELECT sua.call_number
1162 FROM igs_ss_en_sua_dtls_v sua,
1163 igs_ca_inst ci
1164 WHERE
1165 ci.cal_type = sua.cal_type AND
1166 ci.sequence_number = sua.ci_sequence_number AND
1167 sua.person_id = cp_person_id AND
1168 sua.course_cd = cp_program_cd AND
1169 (sua.cal_type ,sua.ci_sequence_number) IN
1170 (SELECT teach_cal_type,teach_ci_sequence_number
1171 FROM igs_ca_load_to_teach_v
1172 WHERE load_cal_type = cp_load_cal_type AND
1173 load_ci_sequence_number = cp_load_ci_sequence_number) AND
1174 sua.unit_attempt_status IN ('INVALID','UNCONFIRM');
1175 BEGIN
1176 --Standard start of API savepoint
1177 SAVEPOINT list_section_in_cart;
1178
1179 --Standard call to check for call compatibility.
1180 IF NOT FND_API.compatible_api_call(l_api_version,
1181 p_api_version,
1182 l_api_name,
1183 G_PKG_NAME) THEN
1184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1185 END IF;
1186
1187 --Initialize message list if p_init_msg_list is set to TRUE.
1188 IF FND_API.to_Boolean(p_init_msg_list) THEN
1189 FND_MSG_PUB.initialize;
1190 END IF;
1191
1192 --Initialize API return status to success.
1193 x_return_status := FND_API.G_RET_STS_SUCCESS;
1194 l_return_status := 'TRUE';
1195 l_rec_count := 0;
1196
1197 --
1198 -- Main code logic begins
1199 --
1200
1201 -- Validate the input parameters.
1202 igs_en_gen_017.enrp_validate_input_parameters(
1203 p_person_number => p_person_number ,
1204 p_career => p_career ,
1205 p_program_code => p_program_code ,
1206 p_term_alt_code => p_term_alt_code ,
1207 p_call_number => NULL ,
1208 p_validation_level => 'NOCALLNUM' ,
1209 p_person_id => l_person_id ,
1210 p_person_type => l_person_type ,
1211 p_cal_type => l_cal_type ,
1212 p_ci_sequence_number => l_ci_sequence_number,
1213 p_primary_code => l_primary_code ,
1214 p_primary_version => l_primary_version ,
1215 p_uoo_id => l_uoo_id ,
1216 p_error_message => l_error_message ,
1217 p_ret_status => l_return_status);
1218
1219 IF l_return_status = 'FALSE' THEN
1220 -- Add to msg stack
1221 igs_en_gen_017.enrp_msg_string_to_list(
1222 p_message_string => l_error_message,
1223 p_delimiter => ';',
1224 p_init_msg_list => FND_API.G_FALSE,
1225 x_message_count => l_messaage_count,
1226 x_message_data => l_message_data);
1227
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 x_msg_count := l_messaage_count;
1230 x_msg_data := l_message_data;
1231 RETURN;
1232 END IF;
1233
1234 FOR rec_call_num IN cur_call_num(l_person_id, l_primary_code, l_cal_type, l_ci_sequence_number) LOOP
1235 l_rec_count := l_rec_count + 1;
1236 x_call_number_tbl(l_rec_count).p_call_number := rec_call_num.call_number;
1237 END LOOP;
1238
1239 --If no records found then returm IGS_EN_NO_SECTION error message with ERROR status.
1240 IF l_rec_count = 0 THEN
1241 -- Add to msg stack
1242 igs_en_gen_017.enrp_msg_string_to_list(
1243 p_message_string => 'IGS_EN_NO_SECTION',
1244 p_init_msg_list => FND_API.G_FALSE,
1245 x_message_count => l_messaage_count,
1246 x_message_data => l_message_data);
1247
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 x_msg_count := l_messaage_count;
1250 x_msg_data := l_message_data;
1251 END IF;
1252
1253 --Standard check of p_commit.
1254 IF FND_API.to_Boolean(p_commit) THEN
1255 commit;
1256 END IF;
1257
1258 --Standard call to get message count and if count is 1, get message info.
1259 FND_MSG_PUB.Count_And_Get(
1260 p_count => l_messaage_count,
1261 p_data => l_message_data);
1262
1263 EXCEPTION
1264 WHEN FND_API.G_EXC_ERROR THEN
1265 ROLLBACK TO list_section_in_cart;
1266 x_return_status := FND_API.G_RET_STS_ERROR ;
1267 FND_MSG_PUB.COUNT_AND_GET (
1268 p_count => x_msg_count,
1269 p_data => x_msg_data );
1270
1271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1272 ROLLBACK TO list_section_in_cart;
1273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1274 FND_MSG_PUB.COUNT_AND_GET (
1275 p_count => x_msg_count,
1276 p_data => x_msg_data );
1277
1278 WHEN OTHERS THEN
1279 ROLLBACK TO list_section_in_cart;
1280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1281 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1282 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
1283 END IF;
1284 FND_MSG_PUB.COUNT_AND_GET (
1285 p_count => x_msg_count,
1286 p_data => x_msg_data );
1287 END list_section_in_cart;
1288
1289 PROCEDURE remove_from_cart(
1290 p_api_version IN NUMBER ,
1291 p_init_msg_list IN VARCHAR2,
1292 p_commit IN VARCHAR2,
1293 p_person_number IN VARCHAR2,
1294 p_career IN VARCHAR2,
1295 p_program_code IN VARCHAR2,
1296 p_term_alt_code IN VARCHAR2,
1297 p_call_number IN NUMBER ,
1298 x_return_status OUT NOCOPY VARCHAR2,
1299 x_msg_count OUT NOCOPY NUMBER ,
1300 x_msg_data OUT NOCOPY VARCHAR2) IS
1301 /*
1302 || Created By : Nalin Kumar
1303 || Created On : 16-Jan-2003
1304 || Purpose : Returns the cart contents of student
1305 || Known limitations, enhancements or remarks :
1306 || Change History :
1307 || Who When What
1308 */
1309 l_api_name CONSTANT VARCHAR2(30) := 'remove_from_cart';
1310 l_api_version CONSTANT NUMBER := 1.0;
1311 l_messaage_count NUMBER(15);
1312 l_message_data VARCHAR2(1000);
1313 l_error_message VARCHAR2(1000);
1314 l_return_status VARCHAR2(10);
1315
1316 l_person_id igs_pe_person.person_id%TYPE;
1317 l_person_type igs_pe_person_types.person_type_code%TYPE;
1318 l_cal_type igs_ca_inst.cal_type%TYPE;
1319 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1320 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
1321 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
1322 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
1323 l_rec_count NUMBER(15);
1324
1325 CURSOR cur_cart(
1326 cp_person_id igs_pe_person.person_id%TYPE,
1327 cp_course_cd igs_en_su_attempt_all.course_cd%TYPE,
1328 cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE)IS
1329 SELECT sua.rowid
1330 FROM igs_en_su_attempt_all sua
1331 WHERE person_id = cp_person_id AND
1332 course_cd = cp_course_cd AND
1333 uoo_id = cp_uoo_id;
1334 l_rec_cart cur_cart%ROWTYPE;
1335 BEGIN
1336 --Standard start of API savepoint
1337 SAVEPOINT remove_from_cart;
1338
1339 --Standard call to check for call compatibility.
1340 IF NOT FND_API.compatible_api_call(l_api_version,
1341 p_api_version,
1342 l_api_name,
1343 G_PKG_NAME) THEN
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346
1347 --Initialize message list if p_init_msg_list is set to TRUE.
1348 IF FND_API.to_Boolean(p_init_msg_list) THEN
1349 FND_MSG_PUB.initialize;
1350 END IF;
1351
1352 --Initialize API return status to success.
1353 x_return_status := FND_API.G_RET_STS_SUCCESS;
1354 l_return_status := 'TRUE';
1355 l_rec_count := 0;
1356
1357 --
1358 -- Main code logic begins
1359 --
1360
1361 -- Validate the input parameters.
1362 igs_en_gen_017.enrp_validate_input_parameters(
1363 p_person_number => p_person_number ,
1364 p_career => p_career ,
1365 p_program_code => p_program_code ,
1366 p_term_alt_code => p_term_alt_code ,
1367 p_call_number => p_call_number ,
1368 p_validation_level => 'WITHCALLNUM' ,
1369 p_person_id => l_person_id ,
1370 p_person_type => l_person_type ,
1371 p_cal_type => l_cal_type ,
1372 p_ci_sequence_number => l_ci_sequence_number,
1373 p_primary_code => l_primary_code ,
1374 p_primary_version => l_primary_version ,
1375 p_uoo_id => l_uoo_id ,
1376 p_error_message => l_error_message ,
1377 p_ret_status => l_return_status);
1378
1379 IF l_return_status = 'FALSE' THEN
1380 -- Add to msg stack
1381 igs_en_gen_017.enrp_msg_string_to_list(
1382 p_message_string => l_error_message,
1383 p_delimiter => ';',
1384 p_init_msg_list => FND_API.G_FALSE,
1385 x_message_count => l_messaage_count,
1386 x_message_data => l_message_data);
1387
1388 x_return_status := FND_API.G_RET_STS_ERROR;
1389 x_msg_count := l_messaage_count;
1390 x_msg_data := l_message_data;
1391 RETURN;
1392 END IF;
1393
1394 --
1395 -- To remove from the cart we need to call the igs_en_su_attempt.delete_row
1396 --
1397 OPEN cur_cart(l_person_id, l_primary_code, l_uoo_id);
1398 FETCH cur_cart INTO l_rec_cart;
1399 IF cur_cart%FOUND THEN
1400 igs_en_su_attempt_pkg.delete_row(x_rowid => l_rec_cart.rowid);
1401 END IF;
1402 CLOSE cur_cart;
1403
1404 --Standard check of p_commit.
1405 IF FND_API.to_Boolean(p_commit) THEN
1406 commit;
1407 END IF;
1408
1409 --Standard call to get message count and if count is 1, get message info.
1410 FND_MSG_PUB.Count_And_Get(
1411 p_count => l_messaage_count,
1412 p_data => l_message_data);
1413
1414 EXCEPTION
1415 WHEN FND_API.G_EXC_ERROR THEN
1416 ROLLBACK TO remove_from_cart;
1417 x_return_status := FND_API.G_RET_STS_ERROR ;
1418 FND_MSG_PUB.COUNT_AND_GET (
1419 p_count => x_msg_count,
1420 p_data => x_msg_data );
1421
1422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1423 ROLLBACK TO remove_from_cart;
1424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1425 FND_MSG_PUB.COUNT_AND_GET (
1426 p_count => x_msg_count,
1427 p_data => x_msg_data );
1428
1429 WHEN OTHERS THEN
1430 ROLLBACK TO remove_from_cart;
1431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1432 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1433 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
1434 END IF;
1435 FND_MSG_PUB.COUNT_AND_GET (
1436 p_count => x_msg_count,
1437 p_data => x_msg_data );
1438 END remove_from_cart;
1439
1440 PROCEDURE update_enroll_stats(
1441 p_api_version IN NUMBER ,
1442 p_init_msg_list IN VARCHAR2,
1443 p_commit IN VARCHAR2,
1444 p_person_number IN VARCHAR2,
1445 p_career IN VARCHAR2,
1446 p_program_code IN VARCHAR2,
1447 p_term_alt_code IN VARCHAR2,
1448 p_call_number IN NUMBER ,
1449 x_return_status OUT NOCOPY VARCHAR2,
1450 x_msg_count OUT NOCOPY NUMBER ,
1451 x_msg_data OUT NOCOPY VARCHAR2) IS
1452 /*
1453 || Created By : Nalin Kumar
1454 || Created On : 16-Jan-2003
1455 || Purpose : To Update the statistic in enrollment.
1456 || Known limitations, enhancements or remarks :
1457 || Change History :
1458 || Who When What
1459 || sommukhe 27-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row
1460 || to include new parameter abort_flag.
1461 || sarakshi 18-Sep-2003 Enh#3052452.Modified the call to igs_ps_unit_ofr_opt_pkg.update_row
1462 || to include new parameter sup_uoo_id,relation_type,default_enroll_flag
1463 || vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the call to
1464 || igs_ps_unit_ofr_opt_pkg.update_row to include
1465 || new parameter not_multiple_section_flag.
1466 */
1467 l_api_name CONSTANT VARCHAR2(30) := 'update_enroll_stats';
1468 l_api_version CONSTANT NUMBER := 1.0;
1469 l_messaage_count NUMBER(15);
1470 l_message_data VARCHAR2(1000);
1471 l_error_message VARCHAR2(1000);
1472 l_return_status VARCHAR2(10);
1473
1474 l_person_id igs_pe_person.person_id%TYPE;
1475 l_person_type igs_pe_person_types.person_type_code%TYPE;
1476 l_cal_type igs_ca_inst.cal_type%TYPE;
1477 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1478 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
1479 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
1480 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
1481
1482 CURSOR cur_enroll_stat(cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE)IS
1483 SELECT puo.*
1484 FROM igs_ps_unit_ofr_opt puo
1485 WHERE puo.uoo_id = cp_uoo_id;
1486 l_rec_enroll_stat cur_enroll_stat%ROWTYPE;
1487 BEGIN
1488 --Standard start of API savepoint
1489 SAVEPOINT update_enroll_stats;
1490
1491 --Standard call to check for call compatibility.
1492 IF NOT FND_API.compatible_api_call(l_api_version,
1493 p_api_version,
1494 l_api_name,
1495 G_PKG_NAME) THEN
1496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1497 END IF;
1498
1499 --Initialize message list if p_init_msg_list is set to TRUE.
1500 IF FND_API.to_Boolean(p_init_msg_list) THEN
1501 FND_MSG_PUB.initialize;
1502 END IF;
1503
1504 --Initialize API return status to success.
1505 x_return_status := FND_API.G_RET_STS_SUCCESS;
1506 l_return_status := 'TRUE';
1507
1508 --
1509 -- Main code logic begins
1510 --
1511
1512 -- Validate the input parameters.
1513 igs_en_gen_017.enrp_validate_input_parameters(
1514 p_person_number => p_person_number ,
1515 p_career => p_career ,
1516 p_program_code => p_program_code ,
1517 p_term_alt_code => p_term_alt_code ,
1518 p_call_number => p_call_number ,
1519 p_validation_level => 'WITHCALLNUM' ,
1520 p_person_id => l_person_id ,
1521 p_person_type => l_person_type ,
1522 p_cal_type => l_cal_type ,
1523 p_ci_sequence_number => l_ci_sequence_number,
1524 p_primary_code => l_primary_code ,
1525 p_primary_version => l_primary_version ,
1526 p_uoo_id => l_uoo_id ,
1527 p_error_message => l_error_message ,
1528 p_ret_status => l_return_status);
1529
1530 IF l_return_status = 'FALSE' THEN
1531 -- Add to msg stack
1532 igs_en_gen_017.enrp_msg_string_to_list(
1533 p_message_string => l_error_message,
1534 p_delimiter => ';',
1535 p_init_msg_list => FND_API.G_FALSE,
1536 x_message_count => l_messaage_count,
1537 x_message_data => l_message_data);
1538
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 x_msg_count := l_messaage_count;
1541 x_msg_data := l_message_data;
1542 RETURN;
1543 END IF;
1544
1545 --
1546 -- Fetch the record from IGS_PS_UNIT_OFR_OPT for the fetched uoo_id to update INQ_NOT_WLST coulmn.
1547 --
1548 OPEN cur_enroll_stat(l_uoo_id);
1549 FETCH cur_enroll_stat INTO l_rec_enroll_stat;
1550 IF cur_enroll_stat%FOUND THEN
1551 BEGIN
1552 igs_ps_unit_ofr_opt_pkg.update_row(
1553 X_ROWID => l_rec_enroll_stat.row_id ,
1554 X_UNIT_CD => l_rec_enroll_stat.unit_cd ,
1555 X_VERSION_NUMBER => l_rec_enroll_stat.version_number ,
1556 X_CAL_TYPE => l_rec_enroll_stat.cal_type ,
1557 X_CI_SEQUENCE_NUMBER => l_rec_enroll_stat.ci_sequence_number ,
1558 X_LOCATION_CD => l_rec_enroll_stat.location_cd ,
1559 X_UNIT_CLASS => l_rec_enroll_stat.unit_class ,
1560 X_UOO_ID => l_rec_enroll_stat.uoo_id ,
1561 X_IVRS_AVAILABLE_IND => l_rec_enroll_stat.ivrs_available_ind ,
1562 X_CALL_NUMBER => l_rec_enroll_stat.call_number ,
1563 X_UNIT_SECTION_STATUS => l_rec_enroll_stat.unit_section_status ,
1564 X_UNIT_SECTION_START_DATE => l_rec_enroll_stat.unit_section_start_date ,
1565 X_UNIT_SECTION_END_DATE => l_rec_enroll_stat.unit_section_end_date ,
1566 X_ENROLLMENT_ACTUAL => l_rec_enroll_stat.enrollment_actual ,
1567 X_WAITLIST_ACTUAL => l_rec_enroll_stat.waitlist_actual ,
1568 X_OFFERED_IND => l_rec_enroll_stat.offered_ind ,
1569 X_STATE_FINANCIAL_AID => l_rec_enroll_stat.state_financial_aid ,
1570 X_GRADING_SCHEMA_PRCDNCE_IND => l_rec_enroll_stat.grading_schema_prcdnce_ind ,
1571 X_FEDERAL_FINANCIAL_AID => l_rec_enroll_stat.federal_financial_aid ,
1572 X_UNIT_QUOTA => l_rec_enroll_stat.unit_quota ,
1573 X_UNIT_QUOTA_RESERVED_PLACES => l_rec_enroll_stat.unit_quota_reserved_places ,
1574 X_INSTITUTIONAL_FINANCIAL_AID => l_rec_enroll_stat.institutional_financial_aid,
1575 X_UNIT_CONTACT => l_rec_enroll_stat.unit_contact ,
1576 X_GRADING_SCHEMA_CD => l_rec_enroll_stat.grading_schema_cd ,
1577 X_GS_VERSION_NUMBER => l_rec_enroll_stat.gs_version_number ,
1578 X_OWNER_ORG_UNIT_CD => l_rec_enroll_stat.owner_org_unit_cd ,
1579 X_ATTENDANCE_REQUIRED_IND => l_rec_enroll_stat.attendance_required_ind ,
1580 X_RESERVED_SEATING_ALLOWED => l_rec_enroll_stat.reserved_seating_allowed ,
1581 X_SPECIAL_PERMISSION_IND => l_rec_enroll_stat.special_permission_ind ,
1582 X_SS_DISPLAY_IND => l_rec_enroll_stat.ss_display_ind ,
1583 X_SS_ENROL_IND => l_rec_enroll_stat.ss_enrol_ind ,
1584 X_DIR_ENROLLMENT => l_rec_enroll_stat.dir_enrollment ,
1585 X_ENR_FROM_WLST => l_rec_enroll_stat.enr_from_wlst ,
1586 X_INQ_NOT_WLST => NVL(l_rec_enroll_stat.inq_not_wlst,0) + 1 ,
1587 X_REV_ACCOUNT_CD => l_rec_enroll_stat.rev_account_cd ,
1588 X_ANON_UNIT_GRADING_IND => l_rec_enroll_stat.anon_unit_grading_ind ,
1589 X_ANON_ASSESS_GRADING_IND => l_rec_enroll_stat.anon_assess_grading_ind ,
1590 X_NON_STD_USEC_IND => l_rec_enroll_stat.non_std_usec_ind ,
1591 X_AUDITABLE_IND => l_rec_enroll_stat.auditable_ind ,
1592 X_AUDIT_PERMISSION_IND => l_rec_enroll_stat.audit_permission_ind ,
1593 x_not_multiple_section_flag => l_rec_enroll_stat.not_multiple_section_flag ,
1594 x_sup_uoo_id => l_rec_enroll_stat.sup_uoo_id ,
1595 x_relation_type => l_rec_enroll_stat.relation_type ,
1596 x_default_enroll_flag => l_rec_enroll_stat.default_enroll_flag ,
1597 x_abort_flag => l_rec_enroll_stat.abort_flag
1598 );
1599 END;
1600 END IF;
1601 CLOSE cur_enroll_stat;
1602
1603 --Standard check of p_commit.
1604 IF FND_API.to_Boolean(p_commit) THEN
1605 commit;
1606 END IF;
1607
1608 --Standard call to get message count and if count is 1, get message info.
1609 FND_MSG_PUB.Count_And_Get(
1610 p_count => l_messaage_count,
1611 p_data => l_message_data);
1612
1613 EXCEPTION
1614 WHEN FND_API.G_EXC_ERROR THEN
1615 ROLLBACK TO update_enroll_stats;
1616 x_return_status := FND_API.G_RET_STS_ERROR ;
1617 FND_MSG_PUB.COUNT_AND_GET (
1618 p_count => x_msg_count,
1619 p_data => x_msg_data );
1620
1621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1622 ROLLBACK TO update_enroll_stats;
1623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1624 FND_MSG_PUB.COUNT_AND_GET (
1625 p_count => x_msg_count,
1626 p_data => x_msg_data );
1627
1628 WHEN OTHERS THEN
1629 ROLLBACK TO update_enroll_stats;
1630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1631 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1632 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
1633 END IF;
1634 FND_MSG_PUB.COUNT_AND_GET (
1635 p_count => x_msg_count,
1636 p_data => x_msg_data );
1637 END update_enroll_stats;
1638
1639 PROCEDURE validate_career_program(
1640 p_api_version IN NUMBER ,
1641 p_init_msg_list IN VARCHAR2,
1642 p_commit IN VARCHAR2,
1643 p_person_number IN VARCHAR2,
1644 p_career IN VARCHAR2,
1645 p_program_code IN VARCHAR2,
1646 x_primary_code OUT NOCOPY VARCHAR2,
1647 x_primary_version OUT NOCOPY NUMBER ,
1648 x_return_status OUT NOCOPY VARCHAR2,
1649 x_msg_count OUT NOCOPY NUMBER ,
1650 x_msg_data OUT NOCOPY VARCHAR2) IS
1651 /*
1652 || Created By : Nalin Kumar
1653 || Created On : 16-Jan-2003
1654 || Purpose : Validate that the career is valid for the student.
1655 || This procedure will validate the input career/program details.
1656 || It accepts the person and career/program details and verifies if
1657 || the career is active for student. (Active means that the program
1658 || attempt status with 'ENROLLED' or 'INACTIVE' status exists). If
1659 || valid then the procedure will return with success status and the
1660 || primary program code and version (for career mode) else will
1661 || return with error.
1662 || Known limitations, enhancements or remarks :
1663 || Change History :
1664 || Who When What
1665 || stutta 27-NOV-2003 Changed the call to igs_ss_enr_details.enrp_get_prgm_for_career
1666 || by passing in two new parameters.
1667 */
1668 l_api_name CONSTANT VARCHAR2(30) := 'validate_career_program';
1669 l_api_version CONSTANT NUMBER := 1.0;
1670 l_messaage_count NUMBER(15);
1671 l_message_data VARCHAR2(1000);
1672 l_error_message VARCHAR2(1000);
1673 l_return_status VARCHAR2(1000);
1674
1675 l_person_id igs_pe_person.person_id%TYPE;
1676 l_person_type igs_pe_person_types.person_type_code%TYPE;
1677 l_primary_code igs_en_su_attempt_all.course_cd%TYPE;
1678 l_primary_version igs_en_su_attempt_all.version_number%TYPE;
1679 l_programlist VARCHAR2(1000);
1680
1681 CURSOR cur_get_ver(
1682 cp_person_id igs_pe_person.person_id%TYPE,
1683 cp_course_cd igs_en_su_attempt_all.course_cd%TYPE) IS
1684 SELECT version_number
1685 FROM igs_en_stdnt_ps_att
1686 WHERE person_id = cp_person_id AND
1687 course_cd = cp_course_cd;
1688 l_rec_ver cur_get_ver%ROWTYPE;
1689
1690 BEGIN
1691 --Standard start of API savepoint
1692 SAVEPOINT validate_career_program;
1693
1694 --Standard call to check for call compatibility.
1695 IF NOT FND_API.compatible_api_call(l_api_version,
1696 p_api_version,
1697 l_api_name,
1698 G_PKG_NAME) THEN
1699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1700 END IF;
1701
1702 --Initialize message list if p_init_msg_list is set to TRUE.
1703 IF FND_API.to_Boolean(p_init_msg_list) THEN
1704 FND_MSG_PUB.initialize;
1705 END IF;
1706
1707 --Initialize API return status to success.
1708 x_return_status := FND_API.G_RET_STS_SUCCESS;
1709 l_return_status := 'TRUE';
1710
1711 --
1712 -- Main code logic begins
1713 --
1714 -- Validate Person Number
1715 igs_en_gen_017.enrp_validate_student(
1716 p_person_number => p_person_number,
1717 p_person_id => l_person_id ,
1718 p_person_type => l_person_type ,
1719 p_error_message => l_error_message,
1720 p_ret_status => l_return_status);
1721 IF l_return_status = 'FALSE' THEN
1722 -- add to msg stack
1723 igs_en_gen_017.enrp_msg_string_to_list(
1724 p_message_string => l_error_message,
1725 p_init_msg_list => FND_API.G_FALSE,
1726 x_message_count => l_messaage_count,
1727 x_message_data => l_message_data);
1728
1729 x_return_status := FND_API.G_RET_STS_ERROR;
1730 x_msg_count := l_messaage_count;
1731 x_msg_data := l_message_data;
1732 RETURN;
1733 END IF;
1734
1735 -- Check the career model, if it is enabled then get the primary program...
1736 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
1737 IF p_career IS NULL THEN
1738 igs_en_gen_017.enrp_msg_string_to_list(
1739 p_message_string => 'IGS_EN_NO_CAREER',
1740 p_init_msg_list => FND_API.G_FALSE,
1741 x_message_count => l_messaage_count,
1742 x_message_data => l_message_data);
1743
1744 x_return_status := FND_API.G_RET_STS_ERROR;
1745 x_msg_count := l_messaage_count;
1746 x_msg_data := l_message_data;
1747 RETURN;
1748 END IF;
1749
1750 -- Get the primary program and if not found then error out...
1751 igs_ss_enr_details.enrp_get_prgm_for_career(
1752 p_person_id => l_person_id ,
1753 p_carrer => p_career ,
1754 p_primary_program => l_primary_code ,
1755 p_primary_program_version => l_primary_version,
1756 p_programlist => l_programlist,
1757 p_term_cal_type => NULL,
1758 p_term_sequence_number => NULL);
1759 IF l_primary_code IS NULL THEN
1760 igs_en_gen_017.enrp_msg_string_to_list(
1761 p_message_string => 'IGS_SS_EN_NO_CAREER',
1762 p_init_msg_list => FND_API.G_FALSE,
1763 x_message_count => l_messaage_count,
1764 x_message_data => l_message_data);
1765
1766 x_return_status := FND_API.G_RET_STS_ERROR;
1767 x_msg_count := l_messaage_count;
1768 x_msg_data := l_message_data;
1769 RETURN;
1770 END IF;
1771 x_primary_code := l_primary_code;
1772 x_primary_version := l_primary_version;
1773 ELSE
1774 IF p_program_code IS NULL THEN
1775 igs_en_gen_017.enrp_msg_string_to_list(
1776 p_message_string => 'IGS_EN_NO_PROGRAM',
1777 p_init_msg_list => FND_API.G_FALSE,
1778 x_message_count => l_messaage_count,
1779 x_message_data => l_message_data);
1780
1781 x_return_status := FND_API.G_RET_STS_ERROR;
1782 x_msg_count := l_messaage_count;
1783 x_msg_data := l_message_data;
1784 RETURN;
1785 END IF;
1786
1787 -- Validate that the input person number and program correspond to a valid program attempt.
1788 OPEN cur_get_ver(l_person_id, p_program_code);
1789 FETCH cur_get_ver INTO l_rec_ver;
1790 IF cur_get_ver%NOTFOUND THEN
1791 igs_en_gen_017.enrp_msg_string_to_list(
1792 p_message_string => 'IGS_EN_NO_ACTIVE_PROGRAM',
1793 p_init_msg_list => FND_API.G_FALSE,
1794 x_message_count => l_messaage_count,
1795 x_message_data => l_message_data);
1796
1797 x_return_status := FND_API.G_RET_STS_ERROR;
1798 x_msg_count := l_messaage_count;
1799 x_msg_data := l_message_data;
1800 RETURN;
1801 ELSE
1802 x_primary_code := p_program_code;
1803 x_primary_version := l_rec_ver.version_number;
1804 END IF;
1805 CLOSE cur_get_ver;
1806 x_return_status := FND_API.G_RET_STS_SUCCESS;
1807 x_msg_count := NULL;
1808 x_msg_data := NULL;
1809 RETURN;
1810 END IF;
1811
1812 --Standard check of p_commit.
1813 IF FND_API.to_Boolean(p_commit) THEN
1814 commit;
1815 END IF;
1816
1817 --Standard call to get message count and if count is 1, get message info.
1818 FND_MSG_PUB.Count_And_Get(
1819 p_count => l_messaage_count,
1820 p_data => l_message_data);
1821
1822 EXCEPTION
1823 WHEN FND_API.G_EXC_ERROR THEN
1824 ROLLBACK TO validate_career_program;
1825 x_return_status := FND_API.G_RET_STS_ERROR ;
1826 FND_MSG_PUB.COUNT_AND_GET (
1827 p_count => x_msg_count,
1828 p_data => x_msg_data );
1829
1830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1831 ROLLBACK TO validate_career_program;
1832 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1833 FND_MSG_PUB.COUNT_AND_GET (
1834 p_count => x_msg_count,
1835 p_data => x_msg_data );
1836
1837 WHEN OTHERS THEN
1838 ROLLBACK TO validate_career_program;
1839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1840 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1841 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,l_api_name );
1842 END IF;
1843 FND_MSG_PUB.COUNT_AND_GET (
1844 p_count => x_msg_count,
1845 p_data => x_msg_data );
1846 END validate_career_program;
1847
1848 PROCEDURE validate_person_details(
1849 p_api_version IN NUMBER,
1850 p_init_msg_list IN VARCHAR2 ,
1851 p_commit IN VARCHAR2 ,
1852 p_person_number IN VARCHAR2 ,
1853 x_default_term_alt_code OUT NOCOPY VARCHAR2,
1854 x_career_tbl OUT NOCOPY career_tbl_type,
1855 x_term_tbl OUT NOCOPY term_tbl_type,
1856 x_multiple_career_program OUT NOCOPY VARCHAR2,
1857 x_return_status OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY NUMBER,
1859 x_msg_data OUT NOCOPY VARCHAR2
1860 )
1861 /******************************************************************************************
1862 || Created By : smanglm
1863 || Created On : 2003/01/15
1864 || Purpose : The procedure will validate the input person number.
1865 || (It will verify that we have an fnd user for input person, person is a valid
1866 || student). If not valid it will return error message and return status as error.
1867 || If valid then it will also return the default term, term list in pl/sql table,
1868 || program_career_list in pl/sql table and indicator whether single or multiple
1869 || career/program. These return values will help the 3rd party (IVR) in proceeding
1870 || further with getting the term and career/program selected by student with
1871 || minimum OSS interaction.
1872 || This procedure will also validate the basic assumption that term is setup for
1873 || IVR and student has at least one active career/program to perform enrollment
1874 || activities. If any of these were not valid it would return with error message.
1875 || This is an API published /available to 3rd party
1876 || Change History :
1877 || Who When What
1878 || (reverse chronological order - newest change first)
1879 ******************************************************************************************/
1880 IS
1881 l_api_name CONSTANT VARCHAR2(30) := 'validate_person_details';
1882 l_api_version CONSTANT NUMBER := 1.0;
1883
1884 -- local variables
1885 l_person_id igs_pe_person.person_id%TYPE;
1886 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
1887 l_error_message VARCHAR2(2000):= NULL;
1888 l_ret_status VARCHAR2(10) := 'TRUE';
1889
1890 l_message_count NUMBER;
1891 l_message_data VARCHAR2(4000);
1892
1893 l_rec_count NUMBER:=0;
1894
1895 /*
1896 cursor to get career mode details
1897 */
1898 CURSOR c_career_mode (lc_person_id igs_pe_person.person_id%TYPE) IS
1899 SELECT course_type,
1900 course_cd,
1901 version_number
1902 FROM igs_en_sca_v
1903 WHERE primary_program_type ='PRIMARY'
1904 AND Course_attempt_status IN ('ENROLLED','INACTIVE')
1905 AND person_id = lc_person_id;
1906
1907 /*
1908 cursor to get program mode details
1909 */
1910 CURSOR c_program_mode (lc_person_id igs_pe_person.person_id%TYPE) IS
1911 SELECT course_type,
1912 course_cd,
1913 version_number
1914 FROM igs_en_sca_v
1915 WHERE course_attempt_status IN ('ENROLLED','INACTIVE')
1916 AND person_id = lc_person_id;
1917
1918 BEGIN -- main begin
1919
1920 --Standard start of API savepoint
1921 SAVEPOINT validate_person_details;
1922
1923 --Standard call to check for call compatibility.
1924 IF NOT FND_API.Compatible_API_Call(
1925 l_api_version,
1926 p_api_version,
1927 l_api_name,
1928 G_PKG_NAME)
1929 THEN
1930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1931 END IF;
1932
1933 --Initialize message list if p_init_msg_list is set to TRUE.
1934 IF FND_API.to_Boolean(p_init_msg_list) THEN
1935 FND_MSG_PUB.initialize;
1936 END IF;
1937
1938 --Initialize API return status to success.
1939 x_return_status := FND_API.G_RET_STS_SUCCESS;
1940
1941
1942 -- main code logic begins
1943 /*
1944 validate person_number
1945 */
1946 igs_en_gen_017.enrp_validate_student
1947 (
1948 p_person_number => p_person_number,
1949 p_person_id => l_person_id ,
1950 p_person_type => l_person_type ,
1951 p_error_message => l_error_message,
1952 p_ret_status => l_ret_status
1953 );
1954 IF l_ret_status = 'FALSE' THEN
1955 -- add to msg stack
1956 igs_en_gen_017.enrp_msg_string_to_list
1957 (
1958 p_message_string => l_error_message,
1959 p_init_msg_list => FND_API.G_FALSE,
1960 x_message_count => l_message_count,
1961 x_message_data => l_message_data
1962 );
1963 x_return_status := FND_API.G_RET_STS_ERROR;
1964 END IF;
1965
1966 /*
1967 determine default term
1968 */
1969 l_error_message := NULL;
1970 l_ret_status := 'TRUE';
1971 igs_en_gen_017.enrp_get_default_term(
1972 p_term_alt_code => x_default_term_alt_code,
1973 p_error_message => l_error_message,
1974 p_ret_status => l_ret_status);
1975 /*
1976 no error loggin as x_default_term_alt_code is optional parameter
1977 */
1978 l_error_message := NULL;
1979 l_ret_status := 'TRUE';
1980 /*
1981 get list of term calendars
1982 */
1983 igs_en_gen_017.enrp_get_term_ivr_list
1984 (
1985 p_term_tbl => x_term_tbl,
1986 p_error_message => l_error_message,
1987 p_ret_status => l_ret_status
1988 );
1989 IF l_ret_status = 'FALSE' THEN
1990 -- add to msg stack
1991 igs_en_gen_017.enrp_msg_string_to_list
1992 (
1993 p_message_string => l_error_message,
1994 p_init_msg_list => FND_API.G_FALSE,
1995 x_message_count => l_message_count,
1996 x_message_data => l_message_data
1997 );
1998 x_return_status := FND_API.G_RET_STS_ERROR;
1999 END IF;
2000 /*
2001 Determine if single or multiple program for the student
2002 and set the career_program pl/sql table
2003 */
2004 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
2005 -- career
2006 FOR rec_career_mode IN c_career_mode(l_person_id)
2007 LOOP
2008 l_rec_count := l_rec_count + 1;
2009 x_career_tbl(l_rec_count).p_career := rec_career_mode.course_type;
2010 x_career_tbl(l_rec_count).p_program_code := rec_career_mode.course_cd;
2011 x_career_tbl(l_rec_count).p_version_number := rec_career_mode.version_number;
2012 END LOOP;
2013 ELSE
2014 FOR rec_program_mode IN c_program_mode(l_person_id)
2015 LOOP
2016 l_rec_count := l_rec_count + 1;
2017 x_career_tbl(l_rec_count).p_career := rec_program_mode.course_type;
2018 x_career_tbl(l_rec_count).p_program_code := rec_program_mode.course_cd;
2019 x_career_tbl(l_rec_count).p_version_number := rec_program_mode.version_number;
2020 END LOOP;
2021 END IF;
2022 IF l_rec_count = 0 THEN
2023 igs_en_gen_017.enrp_msg_string_to_list
2024 (
2025 p_message_string => 'IGS_SS_EN_NO_CONF_PRG',
2026 p_init_msg_list => FND_API.G_FALSE,
2027 x_message_count => l_message_count,
2028 x_message_data => l_message_data
2029 );
2030 x_return_status := FND_API.G_RET_STS_ERROR;
2031 ELSIF l_rec_count = 1 THEN
2032 x_multiple_career_program := 'N';
2033 ELSIF l_rec_count >= 2 THEN
2034 x_multiple_career_program := 'Y';
2035 END IF;
2036
2037
2038
2039 --Standard check of p_commit.
2040 IF FND_API.to_Boolean(p_commit) THEN
2041 commit;
2042 END IF;
2043
2044 --Standard call to get message count and if count is 1, get message info.
2045 FND_MSG_PUB.Count_And_Get(
2046 p_count => x_msg_count,
2047 p_data => x_msg_data);
2048
2049 EXCEPTION
2050 WHEN FND_API.G_EXC_ERROR THEN
2051 ROLLBACK TO validate_person_details;
2052 x_return_status := FND_API.G_RET_STS_ERROR;
2053 FND_MSG_PUB.Count_And_Get(
2054 p_count => x_msg_count,
2055 p_data => x_msg_data);
2056
2057 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2058 ROLLBACK TO validate_person_details;
2059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2060 FND_MSG_PUB.Count_And_Get(
2061 p_count => x_msg_count,
2062 p_data => x_msg_data);
2063 WHEN OTHERS THEN
2064 ROLLBACK TO validate_person_details;
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
2067 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2068 FND_MSG_PUB.ADD;
2069 FND_MSG_PUB.Count_And_Get(
2070 p_count => x_msg_count,
2071 p_data => x_msg_data);
2072
2073 END validate_person_details;
2074
2075 PROCEDURE validate_term(
2076 p_api_version IN NUMBER,
2077 p_init_msg_list IN VARCHAR2,
2078 p_commit IN VARCHAR2,
2079 p_term_alt_code IN VARCHAR2,
2080 x_return_status OUT NOCOPY VARCHAR2,
2081 x_msg_count OUT NOCOPY NUMBER ,
2082 x_msg_data OUT NOCOPY VARCHAR2) AS
2083 /*
2084 || Created By : Nishikant
2085 || Created On : 15JAN2003
2086 || Purpose : The procedure is public procedure to validate that the term
2087 || passed by 3rd party s/w is valid term in system or not.
2088 || If its a valid term calendar then return with null error message
2089 || Else return with error message.
2090 ||
2091 || Known limitations, enhancements or remarks :
2092 || Change History :
2093 || Who When What
2094 || (reverse chronological order - newest change first)
2095 */
2096
2097 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_TERM';
2098 l_api_version CONSTANT NUMBER := 1.0;
2099 l_cal_type igs_ca_inst.cal_type%TYPE;
2100 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
2101 l_message fnd_new_messages.message_name%TYPE;
2102 l_ret_status VARCHAR2(6);
2103 l_message_count NUMBER;
2104 l_message_data VARCHAR2(2000);
2105
2106 BEGIN
2107
2108 -- Standard call to check for call compatibility.
2109 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version,
2110 p_api_version,
2111 l_api_name,
2112 g_pkg_name ) THEN
2113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2114 END IF;
2115
2116 -- Initialize message list if p_init_msg_list is set to TRUE.
2117 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
2118 FND_MSG_PUB.INITIALIZE;
2119 END IF;
2120
2121 -- Initialize API return status to success
2122 x_return_status := FND_API.G_RET_STS_SUCCESS;
2123
2124 --Validate the p_term_alt_code parameter by calling the procedure enrp_validate_term_alt_code.
2125 --This procedure will validate the parameter and also return the cal_type and seq_number (which is of no use here.)
2126 igs_en_gen_017.enrp_validate_term_alt_code(
2127 p_term_alt_code => p_term_alt_code,
2128 p_cal_type => l_cal_type,
2129 p_ci_sequence_number => l_ci_sequence_number,
2130 p_error_message => l_message,
2131 p_ret_status => l_ret_status);
2132
2133 IF l_ret_status = 'FALSE' THEN
2134 -- If the above function returns the l_ret_status parameter as FALSE then
2135 -- Call the function enrp_msg_string_to_list to set the error message. And set the return status to Error.
2136 igs_en_gen_017.enrp_msg_string_to_list (
2137 p_message_string => l_message,
2138 p_delimiter => ';',
2139 p_init_msg_list => FND_API.G_FALSE,
2140 x_message_count => l_message_count,
2141 x_message_data => l_message_data);
2142
2143 --Set the Message Count and Message Data parameters to the out parameter of the above procedure call;
2144 x_msg_count := l_message_count;
2145 x_msg_data := l_message_data;
2146 x_return_status := FND_API.G_RET_STS_ERROR;
2147 END IF;
2148 IF FND_API.TO_BOOLEAN( p_commit ) THEN
2149 COMMIT WORK;
2150 END IF;
2151
2152 EXCEPTION
2153 WHEN FND_API.G_EXC_ERROR THEN
2154 x_return_status := FND_API.G_RET_STS_ERROR ;
2155 FND_MSG_PUB.COUNT_AND_GET (
2156 p_count => x_msg_count,
2157 p_data => x_msg_data );
2158
2159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2161 FND_MSG_PUB.COUNT_AND_GET (
2162 p_count => x_msg_count,
2163 p_data => x_msg_data );
2164
2165 WHEN OTHERS THEN
2166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2167 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
2168 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2169 FND_MSG_PUB.ADD;
2170 FND_MSG_PUB.COUNT_AND_GET (
2171 p_count => x_msg_count,
2172 p_data => x_msg_data );
2173 END validate_term;
2174
2175 PROCEDURE waitlist(
2176 p_api_version IN NUMBER,
2177 p_init_msg_list IN VARCHAR2,
2178 p_commit IN VARCHAR2,
2179 p_person_number IN VARCHAR2,
2180 p_career IN VARCHAR2,
2181 p_program_code IN VARCHAR2,
2182 p_term_alt_code IN VARCHAR2,
2183 p_call_number IN NUMBER,
2184 p_audit_ind IN VARCHAR2,
2185 p_waitlist IN VARCHAR2,
2186 x_return_status OUT NOCOPY VARCHAR2,
2187 x_msg_count OUT NOCOPY NUMBER,
2188 x_msg_data OUT NOCOPY VARCHAR2 ) AS
2189 /*
2190 || Created By : Nishikant
2191 || Created On : 23JAN2003
2192 || Purpose : The procedure will waitlist the student in the section.
2193 || It will accept the call number of section to added
2194 || (along with other context parameters).
2195 ||
2196 || Known limitations, enhancements or remarks :
2197 || Change History :
2198 || Who When What
2199 || kkillams 11-Jul-2003 Call to rollback is added to the procedure if igs_en_gen_017.add_to_cart_waitlist
2200 || sets the out p_ret_status parameter to false. Bug : 3036949
2201 || (reverse chronological order - newest change first)
2202 */
2203 l_api_name CONSTANT VARCHAR2(30) := 'WAITLIST';
2204 l_ret_status VARCHAR2(6) := NULL;
2205 l_message_data VARCHAR2(2000);
2206 l_waitlist VARCHAR2(1);
2207
2208 BEGIN
2209
2210 -- Standard Start of API savepoint
2211 SAVEPOINT WAITLIST_PUB;
2212
2213 -- Standard call to check for call compatibility.
2214 IF NOT FND_API.COMPATIBLE_API_CALL (1.0,
2215 p_api_version,
2216 l_api_name,
2217 g_pkg_name ) THEN
2218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2219 END IF;
2220
2221 -- Initialize message list if p_init_msg_list is set to TRUE.
2222 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
2223 FND_MSG_PUB.INITIALIZE;
2224 END IF;
2225
2226 -- Initialize API return status to success
2227 x_return_status := FND_API.G_RET_STS_SUCCESS;
2228 l_waitlist := p_waitlist;
2229
2230 --Invoke the procedure add_to_cart_waitlist by passing the relevant parameters.
2231 --Parameter p_action should be 'WLIST' in this case and the parameter p_waitlist_ind
2232 --is an IN/OUT variable hance an out value can be expected, which can be ignored.
2233 --The invoke source parameter should have 'IVR' as the input.
2234 igs_en_gen_017.add_to_cart_waitlist (
2235 p_person_number => p_person_number,
2236 p_career => p_career,
2237 p_program_code => p_program_code,
2238 p_term_alt_code => p_term_alt_code,
2239 p_call_number => p_call_number,
2240 p_audit_ind => p_audit_ind,
2241 p_waitlist_ind => l_waitlist,
2242 p_action => 'WLIST',
2243 p_error_message => l_message_data,
2244 p_ret_status => l_ret_status);
2245
2246 --If return status is FALSE above then log error message and RETURN.
2247 IF l_ret_status = 'FALSE' THEN
2248 ROLLBACK TO WAITLIST_PUB;
2249 x_return_status := FND_API.G_RET_STS_ERROR ;
2250 igs_en_gen_017.enrp_msg_string_to_list (
2251 p_message_string => l_message_data,
2252 p_delimiter => ';',
2253 p_init_msg_list => FND_API.G_FALSE,
2254 x_message_count => x_msg_count,
2255 x_message_data => x_msg_data);
2256 x_return_status := FND_API.G_RET_STS_ERROR ;
2257 ELSE
2258 IF FND_API.TO_BOOLEAN( p_commit ) THEN
2259 COMMIT WORK;
2260 END IF;
2261 END IF;
2262
2263 EXCEPTION
2264 WHEN FND_API.G_EXC_ERROR THEN
2265 ROLLBACK TO WAITLIST_PUB;
2266 x_return_status := FND_API.G_RET_STS_ERROR ;
2267 FND_MSG_PUB.COUNT_AND_GET (
2268 p_count => x_msg_count,
2269 p_data => x_msg_data );
2270
2271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2272 ROLLBACK TO WAITLIST_PUB;
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2274 FND_MSG_PUB.COUNT_AND_GET (
2275 p_count => x_msg_count,
2276 p_data => x_msg_data );
2277
2278 WHEN OTHERS THEN
2279 ROLLBACK TO WAITLIST_PUB;
2280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2281 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
2282 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2283 FND_MSG_PUB.ADD;
2284 FND_MSG_PUB.COUNT_AND_GET (
2285 p_count => x_msg_count,
2286 p_data => x_msg_data );
2287 END waitlist;
2288
2289 END igs_en_ivr_pub;