[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_APP_CHOICES_PKG
Source
1 PACKAGE BODY igs_uc_app_choices_pkg AS
2 /* $Header: IGSXI02B.pls 120.2 2005/07/03 18:49:54 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_app_choices%ROWTYPE;
6 new_references igs_uc_app_choices%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_app_choice_id IN NUMBER ,
12 x_app_id IN NUMBER ,
13 x_app_no IN NUMBER ,
14 x_choice_no IN NUMBER ,
15 x_last_change IN DATE ,
16 x_institute_code IN VARCHAR2 ,
17 x_ucas_program_code IN VARCHAR2 ,
18 x_oss_program_code IN VARCHAR2 ,
19 x_oss_program_version IN NUMBER ,
20 x_oss_attendance_type IN VARCHAR2 ,
21 x_oss_attendance_mode IN VARCHAR2 ,
22 x_campus IN VARCHAR2 ,
23 x_oss_location IN VARCHAR2 ,
24 x_faculty IN VARCHAR2 ,
25 x_entry_year IN NUMBER ,
26 x_entry_month IN NUMBER ,
27 x_point_of_entry IN NUMBER ,
28 x_home IN VARCHAR2 ,
29 x_deferred IN VARCHAR2 ,
30 x_route_b_pref_round IN NUMBER ,
31 x_route_b_actual_round IN NUMBER ,
32 x_condition_category IN VARCHAR2 ,
33 x_condition_code IN VARCHAR2 ,
34 x_decision IN VARCHAR2 ,
35 x_decision_date IN DATE ,
36 x_decision_number IN NUMBER ,
37 x_reply IN VARCHAR2 ,
38 x_summary_of_cond IN VARCHAR2 ,
39 x_choice_cancelled IN VARCHAR2 ,
40 x_action IN VARCHAR2 ,
41 x_substitution IN VARCHAR2 ,
42 x_date_substituted IN DATE ,
43 x_prev_institution IN VARCHAR2 ,
44 x_prev_course IN VARCHAR2 ,
45 x_prev_campus IN VARCHAR2 ,
46 x_ucas_amendment IN VARCHAR2 ,
47 x_withdrawal_reason IN VARCHAR2 ,
48 x_offer_course IN VARCHAR2 ,
49 x_offer_campus IN VARCHAR2 ,
50 x_offer_crse_length IN NUMBER ,
51 x_offer_entry_month IN VARCHAR2 ,
52 x_offer_entry_year IN VARCHAR2 ,
53 x_offer_entry_point IN VARCHAR2 ,
54 x_offer_text IN VARCHAR2 ,
55 x_creation_date IN DATE ,
56 x_created_by IN NUMBER ,
57 x_last_update_date IN DATE ,
58 x_last_updated_by IN NUMBER ,
59 x_last_update_login IN NUMBER ,
60 x_export_to_oss_status IN VARCHAR2,
61 x_error_code IN VARCHAR2,
62 x_request_id IN NUMBER,
63 x_batch_id IN NUMBER,
64 x_extra_round_nbr IN NUMBER,
65 x_system_code IN VARCHAR2 ,
66 x_part_time IN VARCHAR2 ,
67 x_interview IN DATE ,
68 x_late_application IN VARCHAR2 ,
69 x_modular IN VARCHAR2 ,
70 x_residential IN VARCHAR2,
71 -- smaddali added this column for ucfd203 build bug #2669208
72 x_ucas_cycle IN NUMBER
73 ) AS
74 /*
75 || Created By : [email protected]
76 || Created On : 21-FEB-2002
77 || Purpose : Initialises the Old and New references for the columns of the table.
78 || Known limitations, enhancements or remarks :
79 || Change History :
80 || Who When What
81 || smaddali 10-jun-03 adding ucas_cycle and obsoleting timestamp columns for ucfd203 bug#2669208
82 || (reverse chronological order - newest change first)
83 */
84
85 CURSOR cur_old_ref_values IS
86 SELECT *
87 FROM IGS_UC_APP_CHOICES
88 WHERE rowid = x_rowid;
89
90 BEGIN
91
92 l_rowid := x_rowid;
93
94 -- Code for setting the Old and New Reference Values.
95 -- Populate Old Values.
96 OPEN cur_old_ref_values;
97 FETCH cur_old_ref_values INTO old_references;
98 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
99 CLOSE cur_old_ref_values;
100 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103 RETURN;
104 END IF;
105 CLOSE cur_old_ref_values;
106
107 -- Populate New Values.
108 new_references.app_choice_id := x_app_choice_id;
109 new_references.app_id := x_app_id;
110 new_references.app_no := x_app_no;
111 new_references.choice_no := x_choice_no;
112 new_references.last_change := x_last_change;
113 new_references.institute_code := x_institute_code;
114 new_references.ucas_program_code := x_ucas_program_code;
115 new_references.oss_program_code := x_oss_program_code;
116 new_references.oss_program_version := x_oss_program_version;
117 new_references.oss_attendance_type := x_oss_attendance_type;
118 new_references.oss_attendance_mode := x_oss_attendance_mode;
119 new_references.campus := x_campus;
120 new_references.oss_location := x_oss_location;
121 new_references.faculty := x_faculty;
122 new_references.entry_year := x_entry_year;
123 new_references.entry_month := x_entry_month;
124 new_references.point_of_entry := x_point_of_entry;
125 new_references.home := x_home;
126 new_references.deferred := x_deferred;
127 new_references.route_b_pref_round := x_route_b_pref_round;
128 new_references.route_b_actual_round := x_route_b_actual_round;
129 new_references.condition_category := x_condition_category;
130 new_references.condition_code := x_condition_code;
131 new_references.decision := x_decision;
132 new_references.decision_date := x_decision_date;
133 new_references.decision_number := x_decision_number;
134 new_references.reply := x_reply;
135 new_references.summary_of_cond := x_summary_of_cond;
136 new_references.choice_cancelled := x_choice_cancelled;
137 new_references.action := x_action;
138 new_references.substitution := x_substitution;
139 new_references.date_substituted := x_date_substituted;
140 new_references.prev_institution := x_prev_institution;
141 new_references.prev_course := x_prev_course;
142 new_references.prev_campus := x_prev_campus;
143 new_references.ucas_amendment := x_ucas_amendment;
144 new_references.withdrawal_reason := x_withdrawal_reason;
145 new_references.offer_course := x_offer_course;
146 new_references.offer_campus := x_offer_campus;
147 new_references.offer_crse_length := x_offer_crse_length;
148 new_references.offer_entry_month := x_offer_entry_month;
149 new_references.offer_entry_year := x_offer_entry_year;
150 new_references.offer_entry_point := x_offer_entry_point;
151 new_references.offer_text := x_offer_text;
152 new_references.export_to_oss_status := x_export_to_oss_status;
153 new_references.error_code := x_error_code;
154 new_references.request_id := x_request_id;
155 new_references.batch_id := x_batch_id;
156 new_references.system_code := x_system_code;
157 new_references.part_time := x_part_time;
158 new_references.interview := x_interview;
159 new_references.late_application := x_late_application;
160 new_references.modular := x_modular;
161 new_references.residential := x_residential;
162 -- smaddali added new column for ucfd203 - bug#2669208
163 new_references.ucas_cycle := x_ucas_cycle;
164
165
166 IF (p_action = 'UPDATE') THEN
167 new_references.creation_date := old_references.creation_date;
168 new_references.created_by := old_references.created_by;
169 ELSE
170 new_references.creation_date := x_creation_date;
171 new_references.created_by := x_created_by;
172 END IF;
173
174 new_references.last_update_date := x_last_update_date;
175 new_references.last_updated_by := x_last_updated_by;
176 new_references.last_update_login := x_last_update_login;
177
178 -- The below column is added newly in UCFD06 - 24SEP2002 - Bug#2574566
179 new_references.extra_round_nbr := x_extra_round_nbr;
180
181 END set_column_values;
182
183
184 PROCEDURE check_child_existance IS
185 /*
186 || Created By : [email protected]
187 || Created On : 21-FEB-2002
188 || Purpose : Checks for the existance of Child records.
189 || Known limitations, enhancements or remarks :
190 || Change History :
191 || Who When What
192 || (reverse chronological order - newest change first)
193 */
194 BEGIN
195 igs_uc_app_cho_cnds_pkg.get_fk_igs_uc_app_choices(old_references.app_choice_id);
196
197 END check_child_existance;
198
199 PROCEDURE check_uniqueness AS
200 /*
201 || Created By : [email protected]
202 || Created On : 21-FEB-2002
203 || Purpose : Handles the Unique Constraint logic defined for the columns.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 || smaddali 10-jun-03 adding ucas_cycle to UK for bug#2669208 ucfd203 build
209 */
210 BEGIN
211
212 IF ( get_uk_for_validation (
213 new_references.app_no,
214 new_references.choice_no,
215 -- smaddali added new column for ucfd203 - bug#2669208
216 new_references.ucas_cycle
217 )
218 ) THEN
219 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
220 igs_ge_msg_stack.add;
221 app_exception.raise_exception;
222 END IF;
223
224 END check_uniqueness;
225
226
227 PROCEDURE check_parent_existance AS
228 /*
229 || Created By : [email protected]
230 || Created On : 21-FEB-2002
231 || Purpose : Checks for the existance of Parent records.
232 || Known limitations, enhancements or remarks :
233 || Change History :
234 || Who When What
235 || (reverse chronological order - newest change first)
236 */
237
238 -- Cursor to fetch the current Institute Code
239 CURSOR crnt_inst_cur IS
240 SELECT DISTINCT current_inst_code
241 FROM igs_uc_defaults
242 WHERE current_inst_code IS NOT NULL;
243 l_crnt_institute igs_uc_defaults.current_inst_code%TYPE;
244
245 BEGIN
246
247 IF (((old_references.app_id = new_references.app_id)) OR
248 ((new_references.app_id IS NULL))) THEN
249 NULL;
250 ELSIF NOT igs_uc_applicants_pkg.get_pk_for_validation (
251 new_references.app_id
252 ) THEN
253 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
254 igs_ge_msg_stack.add;
255 app_exception.raise_exception;
256 END IF;
257
258 IF (((old_references.condition_category = new_references.condition_category) AND
259 (old_references.condition_code = new_references.condition_code)) OR
260 ((new_references.condition_category IS NULL) OR
261 (new_references.condition_code IS NULL))) THEN
262 NULL;
263 ELSIF NOT igs_uc_offer_conds_pkg.get_pk_for_validation (
264 new_references.condition_category,
265 new_references.condition_code
266 ) THEN
267 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
268 igs_ge_msg_stack.add;
269 app_exception.raise_exception;
270 END IF;
271
272 IF (
273 (
274 (old_references.ucas_program_code = new_references.ucas_program_code) AND
275 (old_references.campus = new_references.campus) OR
276 (old_references.institute_code = new_references.institute_code) OR
277 (old_references.system_code = new_references.system_code)
278 )
279 AND
280 (
281 (new_references.ucas_program_code IS NULL) OR
282 (new_references.campus IS NULL) OR
283 (new_references.institute_code IS NULL) OR
284 (new_references.system_code IS NULL)
285 )
286 ) THEN
287 NULL;
288 ELSE
289
290 l_crnt_institute := NULL;
291 OPEN crnt_inst_cur;
292 FETCH crnt_inst_cur INTO l_crnt_institute;
293 CLOSE crnt_inst_cur;
294
295 IF new_references.institute_code = l_crnt_institute AND
296 NOT igs_uc_crse_dets_pkg.get_pk_for_validation (
297 new_references.ucas_program_code,
298 new_references.institute_code,
299 new_references.campus,
300 new_references.system_code) THEN
301
302 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
303 igs_ge_msg_stack.add;
304 app_exception.raise_exception;
305
306 END IF;
307 END IF;
308
309
310 END check_parent_existance;
311
312
313 FUNCTION get_pk_for_validation (
314 x_app_choice_id IN NUMBER
315 ) RETURN BOOLEAN AS
316 /*
317 || Created By : [email protected]
318 || Created On : 21-FEB-2002
319 || Purpose : Validates the Primary Key of the table.
320 || Known limitations, enhancements or remarks :
321 || Change History :
322 || Who When What
323 || (reverse chronological order - newest change first)
324 */
325 CURSOR cur_rowid IS
326 SELECT rowid
327 FROM igs_uc_app_choices
328 WHERE app_choice_id = x_app_choice_id ;
329
330 lv_rowid cur_rowid%RowType;
331
332 BEGIN
333
334 OPEN cur_rowid;
335 FETCH cur_rowid INTO lv_rowid;
336 IF (cur_rowid%FOUND) THEN
337 CLOSE cur_rowid;
338 RETURN(TRUE);
339 ELSE
340 CLOSE cur_rowid;
341 RETURN(FALSE);
342 END IF;
343
344 END get_pk_for_validation;
345
346
347 FUNCTION get_uk_for_validation (
348 x_app_no IN NUMBER,
349 x_choice_no IN NUMBER,
350 -- smaddali added new column for ucfd203 - bug#2669208
351 x_ucas_cycle IN NUMBER
352 ) RETURN BOOLEAN AS
353 /*
354 || Created By : [email protected]
355 || Created On : 21-FEB-2002
356 || Purpose : Validates the Unique Keys of the table.
357 || Known limitations, enhancements or remarks :
358 || Change History :
359 || Who When What
360 || smaddali 10-jun-03 adding ucas_cycle to UK for bug#2669208 ucfd203 build
361 || (reverse chronological order - newest change first)
362 */
363 CURSOR cur_rowid IS
364 SELECT rowid
365 FROM igs_uc_app_choices
366 WHERE app_no = x_app_no
367 AND choice_no = x_choice_no
368 AND ucas_cycle = x_ucas_cycle
369 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
370
371 lv_rowid cur_rowid%RowType;
372
373 BEGIN
374
375 OPEN cur_rowid;
376 FETCH cur_rowid INTO lv_rowid;
377 IF (cur_rowid%FOUND) THEN
378 CLOSE cur_rowid;
379 RETURN (true);
380 ELSE
381 CLOSE cur_rowid;
382 RETURN(FALSE);
383 END IF;
384
385 END get_uk_for_validation ;
386
387
388 PROCEDURE get_fk_igs_uc_applicants (
389 x_app_id IN NUMBER
390 ) AS
391 /*
392 || Created By : [email protected]
393 || Created On : 21-FEB-2002
394 || Purpose : Validates the Foreign Keys for the table.
395 || Known limitations, enhancements or remarks :
396 || Change History :
397 || Who When What
398 || (reverse chronological order - newest change first)
399 || Nishikant 17Jun2002 Bug#2415346. UCAPCH_UCAP_FKIGS_UC_APPLICANTS
400 || message was replaced with IGS_UC_UCAPCH_UCAP_FK.
401 */
402 CURSOR cur_rowid IS
403 SELECT rowid
404 FROM igs_uc_app_choices
405 WHERE ((app_id = x_app_id));
406
407 lv_rowid cur_rowid%RowType;
408
409 BEGIN
410
411 OPEN cur_rowid;
412 FETCH cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 CLOSE cur_rowid;
415 fnd_message.set_name ('IGS', 'IGS_UC_UCAPCH_UCAP_FK');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 RETURN;
419 END IF;
420 CLOSE cur_rowid;
421
422 END get_fk_igs_uc_applicants;
423
424
425 PROCEDURE get_fk_igs_uc_offer_conds (
426 x_condition_category IN VARCHAR2,
427 x_condition_name IN VARCHAR2
428 ) AS
429 /*
430 || Created By : [email protected]
431 || Created On : 21-FEB-2002
432 || Purpose : Validates the Foreign Keys for the table.
433 || Known limitations, enhancements or remarks :
434 || Change History :
435 || Who When What
436 || (reverse chronological order - newest change first)
437 || Nishikant 17Jun2002 Bug#2415346. UCAPACH_UCOC_FKIGS_UC_OFFER_CONDS
438 || message was replaced with IGS_UC_UCAPCH_UCOC_FK
439 */
440 CURSOR cur_rowid IS
441 SELECT rowid
442 FROM igs_uc_app_choices
443 WHERE ((condition_category = x_condition_category) AND
444 (condition_code = x_condition_name));
445
446 lv_rowid cur_rowid%RowType;
447
448 BEGIN
449
450 OPEN cur_rowid;
451 FETCH cur_rowid INTO lv_rowid;
452 IF (cur_rowid%FOUND) THEN
453 CLOSE cur_rowid;
454 fnd_message.set_name ('IGS', 'IGS_UC_UCAPCH_UCOC_FK');
455 igs_ge_msg_stack.add;
456 app_exception.raise_exception;
457 RETURN;
458 END IF;
459 CLOSE cur_rowid;
460
461 END get_fk_igs_uc_offer_conds;
462
463
464 PROCEDURE get_fk_igs_uc_crse_dets(
465 x_ucas_program_code IN VARCHAR2,
466 x_institute_code IN VARCHAR2,
467 x_campus IN VARCHAR2,
468 x_system_code IN VARCHAR2
469 ) AS
470 /*
471 || Created By : bayadav
472 || Created On : 21-NOV-2002
473 || Purpose : Validates the Foreign Keys for the table.
474 || Known limitations, enhancements or remarks :
475 || Change History :
476 || Who When What
477 || (reverse chronological order - newest change first)
478 */
479 CURSOR cur_rowid IS
480 SELECT rowid
481 FROM igs_uc_app_choices
482 WHERE ((ucas_program_code = x_ucas_program_code) AND
483 (institute_code = x_institute_code) AND
484 (campus = x_campus) AND
485 (system_code = x_system_code));
486
487 lv_rowid cur_rowid%RowType;
488
489 BEGIN
490
491 OPEN cur_rowid;
492 FETCH cur_rowid INTO lv_rowid;
493 IF (cur_rowid%FOUND) THEN
494 CLOSE cur_rowid;
495 fnd_message.set_name ('IGS', 'IGS_UC_UCAPCH_UCCSDE_FK');
496 igs_ge_msg_stack.add;
497 app_exception.raise_exception;
498 RETURN;
499 END IF;
500 CLOSE cur_rowid;
501
502 END get_fk_igs_uc_crse_dets;
503
504
505 PROCEDURE before_dml (
506 p_action IN VARCHAR2,
507 x_rowid IN VARCHAR2 ,
508 x_app_choice_id IN NUMBER ,
509 x_app_id IN NUMBER ,
510 x_app_no IN NUMBER ,
511 x_choice_no IN NUMBER ,
512 x_last_change IN DATE ,
513 x_institute_code IN VARCHAR2 ,
514 x_ucas_program_code IN VARCHAR2 ,
515 x_oss_program_code IN VARCHAR2 ,
516 x_oss_program_version IN NUMBER ,
517 x_oss_attendance_type IN VARCHAR2 ,
518 x_oss_attendance_mode IN VARCHAR2 ,
519 x_campus IN VARCHAR2 ,
520 x_oss_location IN VARCHAR2 ,
521 x_faculty IN VARCHAR2 ,
522 x_entry_year IN NUMBER ,
523 x_entry_month IN NUMBER ,
524 x_point_of_entry IN NUMBER ,
525 x_home IN VARCHAR2 ,
526 x_deferred IN VARCHAR2 ,
527 x_route_b_pref_round IN NUMBER ,
528 x_route_b_actual_round IN NUMBER ,
529 x_condition_category IN VARCHAR2 ,
530 x_condition_code IN VARCHAR2 ,
531 x_decision IN VARCHAR2 ,
532 x_decision_date IN DATE ,
533 x_decision_number IN NUMBER ,
534 x_reply IN VARCHAR2 ,
535 x_summary_of_cond IN VARCHAR2 ,
536 x_choice_cancelled IN VARCHAR2 ,
537 x_action IN VARCHAR2 ,
538 x_substitution IN VARCHAR2 ,
539 x_date_substituted IN DATE ,
540 x_prev_institution IN VARCHAR2 ,
541 x_prev_course IN VARCHAR2 ,
542 x_prev_campus IN VARCHAR2 ,
543 x_ucas_amendment IN VARCHAR2 ,
544 x_withdrawal_reason IN VARCHAR2 ,
545 x_offer_course IN VARCHAR2 ,
546 x_offer_campus IN VARCHAR2 ,
547 x_offer_crse_length IN NUMBER ,
548 x_offer_entry_month IN VARCHAR2 ,
549 x_offer_entry_year IN VARCHAR2 ,
550 x_offer_entry_point IN VARCHAR2 ,
551 x_offer_text IN VARCHAR2 ,
552 x_creation_date IN DATE ,
553 x_created_by IN NUMBER ,
554 x_last_update_date IN DATE ,
555 x_last_updated_by IN NUMBER ,
556 x_last_update_login IN NUMBER ,
557 x_export_to_oss_status IN VARCHAR2,
558 x_error_code IN VARCHAR2,
559 x_request_id IN NUMBER,
560 x_batch_id IN NUMBER,
561 -- The below column is added newly in UCFD06 - 24SEP2002 - Bug#2574566
562 x_extra_round_nbr IN NUMBER,
563 -- The below columns are added newly in UCFD102 - Bug#2643048
564 x_system_code IN VARCHAR2 ,
565 x_part_time IN VARCHAR2 ,
566 x_interview IN DATE ,
567 x_late_application IN VARCHAR2 ,
568 x_modular IN VARCHAR2 ,
569 x_residential IN VARCHAR2 ,
570 -- smaddali added this column for ucfd203 build bug #2669208
571 x_ucas_cycle IN NUMBER
572 ) IS
573 /*
574 || Created By : [email protected]
575 || Created On : 21-FEB-2002
576 || Purpose : Initialises the columns, Checks Constraints, Calls the
577 || Trigger Handlers for the table, before any DML operation.
578 || Known limitations, enhancements or remarks :
579 || Change History :
580 || Who When What
581 || smaddali 10-jun-03 adding ucas_cycle and obsoleting timestamps for bug#2669208 ucfd203 build
582 || (reverse chronological order - newest change first)
583 */
584 BEGIN
585
586 set_column_values (
587 p_action,
588 x_rowid,
589 x_app_choice_id,
590 x_app_id,
591 x_app_no,
592 x_choice_no,
593 x_last_change,
594 x_institute_code,
595 x_ucas_program_code,
596 x_oss_program_code,
597 x_oss_program_version,
598 x_oss_attendance_type,
599 x_oss_attendance_mode,
600 x_campus,
601 x_oss_location,
602 x_faculty,
603 x_entry_year,
604 x_entry_month,
605 x_point_of_entry,
606 x_home,
607 x_deferred,
608 x_route_b_pref_round,
609 x_route_b_actual_round,
610 x_condition_category,
611 x_condition_code,
612 x_decision,
613 x_decision_date,
614 x_decision_number,
615 x_reply,
616 x_summary_of_cond,
617 x_choice_cancelled,
618 x_action,
619 x_substitution,
620 x_date_substituted,
621 x_prev_institution,
622 x_prev_course,
623 x_prev_campus,
624 x_ucas_amendment,
625 x_withdrawal_reason,
626 x_offer_course,
627 x_offer_campus,
628 x_offer_crse_length,
629 x_offer_entry_month,
630 x_offer_entry_year,
631 x_offer_entry_point,
632 x_offer_text,
633 x_creation_date,
634 x_created_by,
635 x_last_update_date,
636 x_last_updated_by,
637 x_last_update_login ,
638 x_export_to_oss_status ,
639 x_error_code ,
640 x_request_id ,
641 x_batch_id ,
642 x_extra_round_nbr ,
643 x_system_code ,
644 x_part_time ,
645 x_interview ,
646 x_late_application ,
647 x_modular ,
648 x_residential ,
649 -- smaddali added new column for ucfd203 - bug#2669208
650 x_ucas_cycle );
651
652 IF (p_action = 'INSERT') THEN
653 -- Call all the procedures related to Before Insert.
654 IF ( get_pk_for_validation(
655 new_references.app_choice_id
656 )
657 ) THEN
658 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
659 igs_ge_msg_stack.add;
660 app_exception.raise_exception;
661 END IF;
662 check_uniqueness;
663 check_parent_existance;
664 ELSIF (p_action = 'UPDATE') THEN
665 -- Call all the procedures related to Before Update.
666 check_uniqueness;
667 check_parent_existance;
668 ELSIF (p_action = 'VALIDATE_INSERT') THEN
669 -- Call all the procedures related to Before Insert.
670 IF ( get_pk_for_validation (
671 new_references.app_choice_id
672 )
673 ) THEN
674 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
675 igs_ge_msg_stack.add;
676 app_exception.raise_exception;
677 END IF;
678 check_uniqueness;
679 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
680 check_uniqueness;
681 ELSIF (p_action = 'DELETE') THEN
682 -- Call all the procedures related to Before Delete.
683 check_child_existance;
684 ELSIF (p_action = 'VALIDATE_DELETE') THEN
685 check_child_existance;
686 END IF;
687
688 END before_dml;
689
690
691 PROCEDURE insert_row (
692 x_rowid IN OUT NOCOPY VARCHAR2,
693 x_app_choice_id IN OUT NOCOPY NUMBER,
694 x_app_id IN NUMBER,
695 x_app_no IN NUMBER,
696 x_choice_no IN NUMBER,
697 x_last_change IN DATE,
698 x_institute_code IN VARCHAR2,
699 x_ucas_program_code IN VARCHAR2,
700 x_oss_program_code IN VARCHAR2,
701 x_oss_program_version IN NUMBER,
702 x_oss_attendance_type IN VARCHAR2,
703 x_oss_attendance_mode IN VARCHAR2,
704 x_campus IN VARCHAR2,
705 x_oss_location IN VARCHAR2,
706 x_faculty IN VARCHAR2,
707 x_entry_year IN NUMBER,
708 x_entry_month IN NUMBER,
709 x_point_of_entry IN NUMBER,
710 x_home IN VARCHAR2,
711 x_deferred IN VARCHAR2,
712 x_route_b_pref_round IN NUMBER,
713 x_route_b_actual_round IN NUMBER,
714 x_condition_category IN VARCHAR2,
715 x_condition_code IN VARCHAR2,
716 x_decision IN VARCHAR2,
717 x_decision_date IN DATE,
718 x_decision_number IN NUMBER,
719 x_reply IN VARCHAR2,
720 x_summary_of_cond IN VARCHAR2,
721 x_choice_cancelled IN VARCHAR2,
722 x_action IN VARCHAR2,
723 x_substitution IN VARCHAR2,
724 x_date_substituted IN DATE,
725 x_prev_institution IN VARCHAR2,
726 x_prev_course IN VARCHAR2,
727 x_prev_campus IN VARCHAR2,
728 x_ucas_amendment IN VARCHAR2,
729 x_withdrawal_reason IN VARCHAR2,
730 x_offer_course IN VARCHAR2,
731 x_offer_campus IN VARCHAR2,
732 x_offer_crse_length IN NUMBER,
733 x_offer_entry_month IN VARCHAR2,
734 x_offer_entry_year IN VARCHAR2,
735 x_offer_entry_point IN VARCHAR2,
736 x_offer_text IN VARCHAR2,
737 x_mode IN VARCHAR2 ,
738 x_export_to_oss_status IN VARCHAR2,
739 x_error_code IN VARCHAR2,
740 x_request_id IN NUMBER,
741 x_batch_id IN NUMBER,
742 -- The below column is added newly in UCFD06 - 24SEP2002 - Bug#2574566
743 x_extra_round_nbr IN NUMBER,
744 -- The below column is added newly in UCFD102 - Bug#2643048
745 x_system_code IN VARCHAR2 ,
746 x_part_time IN VARCHAR2 ,
747 x_interview IN DATE ,
748 x_late_application IN VARCHAR2 ,
749 x_modular IN VARCHAR2 ,
750 x_residential IN VARCHAR2 ,
751 -- smaddali added this column for ucfd203 build bug #2669208
752 x_ucas_cycle IN NUMBER
753 ) IS
754 /*
755 || Created By : [email protected]
756 || Created On : 21-FEB-2002
757 || Purpose : Handles the INSERT DML logic for the table.
758 || Known limitations, enhancements or remarks :
759 || Change History :
760 || Who When What
761 || smaddali 10-jun-03 adding ucas_cycle and obsoleting timestamps for bug#2669208 ucfd203 build
762 || (reverse chronological order - newest change first)
763 */
764 CURSOR c IS
765 SELECT rowid
766 FROM igs_uc_app_choices
767 WHERE app_choice_id = x_app_choice_id;
768
769 x_last_update_date DATE;
770 x_last_updated_by NUMBER;
771 x_last_update_login NUMBER;
772
773 BEGIN
774
775 x_last_update_date := SYSDATE;
776 IF (x_mode = 'I') THEN
777 x_last_updated_by := 1;
778 x_last_update_login := 0;
779 ELSIF (X_MODE IN ('R', 'S')) THEN
780 x_last_updated_by := fnd_global.user_id;
781 IF (x_last_updated_by IS NULL) THEN
782 x_last_updated_by := -1;
783 END IF;
784 x_last_update_login := fnd_global.login_id;
785 IF (x_last_update_login IS NULL) THEN
786 x_last_update_login := -1;
787 END IF;
788 ELSE
789 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
790 igs_ge_msg_stack.add;
791 app_exception.raise_exception;
792 END IF;
793
794 SELECT igs_uc_app_choices_s.NEXTVAL
795 INTO x_app_choice_id
796 FROM dual;
797
798 before_dml(
799 p_action => 'INSERT',
800 x_rowid => x_rowid,
801 x_app_choice_id => x_app_choice_id,
802 x_app_id => x_app_id,
803 x_app_no => x_app_no,
804 x_choice_no => x_choice_no,
805 x_last_change => x_last_change,
806 x_institute_code => x_institute_code,
807 x_ucas_program_code => x_ucas_program_code,
808 x_oss_program_code => x_oss_program_code,
809 x_oss_program_version => x_oss_program_version,
810 x_oss_attendance_type => x_oss_attendance_type,
811 x_oss_attendance_mode => x_oss_attendance_mode,
812 x_campus => x_campus,
813 x_oss_location => x_oss_location,
814 x_faculty => x_faculty,
815 x_entry_year => x_entry_year,
816 x_entry_month => x_entry_month,
817 x_point_of_entry => x_point_of_entry,
818 x_home => x_home,
819 x_deferred => x_deferred,
820 x_route_b_pref_round => x_route_b_pref_round,
821 x_route_b_actual_round => x_route_b_actual_round,
822 x_condition_category => x_condition_category,
823 x_condition_code => x_condition_code,
824 x_decision => x_decision,
825 x_decision_date => x_decision_date,
826 x_decision_number => x_decision_number,
827 x_reply => x_reply,
828 x_summary_of_cond => x_summary_of_cond,
829 x_choice_cancelled => x_choice_cancelled,
830 x_action => x_action,
831 x_substitution => x_substitution,
832 x_date_substituted => x_date_substituted,
833 x_prev_institution => x_prev_institution,
834 x_prev_course => x_prev_course,
835 x_prev_campus => x_prev_campus,
836 x_ucas_amendment => x_ucas_amendment,
837 x_withdrawal_reason => x_withdrawal_reason,
838 x_offer_course => x_offer_course,
839 x_offer_campus => x_offer_campus,
840 x_offer_crse_length => x_offer_crse_length,
841 x_offer_entry_month => x_offer_entry_month,
842 x_offer_entry_year => x_offer_entry_year,
843 x_offer_entry_point => x_offer_entry_point,
844 x_offer_text => x_offer_text,
845 x_creation_date => x_last_update_date,
846 x_created_by => x_last_updated_by,
847 x_last_update_date => x_last_update_date,
848 x_last_updated_by => x_last_updated_by,
849 x_last_update_login => x_last_update_login ,
850 x_export_to_oss_status =>x_export_to_oss_status,
851 x_error_code =>x_error_code ,
852 x_request_id =>x_request_id,
853 x_batch_id =>x_batch_id,
854 x_extra_round_nbr => x_extra_round_nbr,
855 x_system_code => x_system_code,
856 x_part_time => x_part_time ,
857 x_interview => x_interview ,
858 x_late_application => x_late_application ,
859 x_modular => x_modular ,
860 x_residential => x_residential ,
861 -- smaddali added new column for ucfd203 - bug#2669208
862 x_ucas_cycle => x_ucas_cycle
863 );
864
865 IF (x_mode = 'S') THEN
866 igs_sc_gen_001.set_ctx('R');
867 END IF;
868 INSERT INTO igs_uc_app_choices (
869 app_choice_id,
870 app_id,
871 app_no,
872 choice_no,
873 last_change,
874 institute_code,
875 ucas_program_code,
876 oss_program_code,
877 oss_program_version,
878 oss_attendance_type,
879 oss_attendance_mode,
880 campus,
881 oss_location,
882 faculty,
883 entry_year,
884 entry_month,
885 point_of_entry,
886 home,
887 deferred,
888 route_b_pref_round,
889 route_b_actual_round,
890 condition_category,
891 condition_code,
892 decision,
893 decision_date,
894 decision_number,
895 reply,
896 summary_of_cond,
897 choice_cancelled,
898 action,
899 substitution,
900 date_substituted,
901 prev_institution,
902 prev_course,
903 prev_campus,
904 ucas_amendment,
905 withdrawal_reason,
906 offer_course,
907 offer_campus,
908 offer_crse_length,
909 offer_entry_month,
910 offer_entry_year,
911 offer_entry_point,
912 offer_text,
913 creation_date,
914 created_by,
915 last_update_date,
916 last_updated_by,
917 last_update_login,
918 export_to_oss_status ,
919 error_code ,
920 request_id ,
921 batch_id,
922 extra_round_nbr,
923 system_code,
924 part_time,
925 interview,
926 late_application,
927 modular,
928 residential,
929 -- smaddali added new column for ucfd203 - bug#2669208
930 ucas_cycle
931 )
932 VALUES (
933 new_references.app_choice_id,
934 new_references.app_id,
935 new_references.app_no,
936 new_references.choice_no,
937 new_references.last_change,
938 new_references.institute_code,
939 new_references.ucas_program_code,
940 new_references.oss_program_code,
941 new_references.oss_program_version,
942 new_references.oss_attendance_type,
943 new_references.oss_attendance_mode,
944 new_references.campus,
945 new_references.oss_location,
946 new_references.faculty,
947 new_references.entry_year,
948 new_references.entry_month,
949 new_references.point_of_entry,
950 new_references.home,
951 new_references.deferred,
952 new_references.route_b_pref_round,
953 new_references.route_b_actual_round,
954 new_references.condition_category,
955 new_references.condition_code,
956 new_references.decision,
957 new_references.decision_date,
958 new_references.decision_number,
959 new_references.reply,
960 new_references.summary_of_cond,
961 new_references.choice_cancelled,
962 new_references.action,
963 new_references.substitution,
964 new_references.date_substituted,
965 new_references.prev_institution,
966 new_references.prev_course,
967 new_references.prev_campus,
968 new_references.ucas_amendment,
969 new_references.withdrawal_reason,
970 new_references.offer_course,
971 new_references.offer_campus,
972 new_references.offer_crse_length,
973 new_references.offer_entry_month,
974 new_references.offer_entry_year,
975 new_references.offer_entry_point,
976 new_references.offer_text,
977 x_last_update_date,
978 x_last_updated_by,
979 x_last_update_date,
980 x_last_updated_by,
981 x_last_update_login ,
982 new_references.export_to_oss_status,
983 new_references.error_code ,
984 new_references.request_id ,
985 new_references.batch_id,
986 new_references.extra_round_nbr,
987 new_references.system_code,
988 new_references.part_time,
989 new_references.interview,
990 new_references.late_application,
991 new_references.modular,
992 new_references.residential,
993 -- smaddali added new column for ucfd203 - bug#2669208
994 new_references.ucas_cycle
995 );
996 IF (x_mode = 'S') THEN
997 igs_sc_gen_001.unset_ctx('R');
998 END IF;
999
1000
1001 OPEN c;
1002 FETCH c INTO x_rowid;
1003 IF (c%NOTFOUND) THEN
1004 CLOSE c;
1005 RAISE NO_DATA_FOUND;
1006 END IF;
1007 CLOSE c;
1008
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1013 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1014 fnd_message.set_token ('ERR_CD', SQLCODE);
1015 igs_ge_msg_stack.add;
1016 igs_sc_gen_001.unset_ctx('R');
1017 app_exception.raise_exception;
1018 ELSE
1019 igs_sc_gen_001.unset_ctx('R');
1020 RAISE;
1021 END IF;
1022 END insert_row;
1023
1024
1025 PROCEDURE lock_row (
1026 x_rowid IN VARCHAR2,
1027 x_app_choice_id IN NUMBER,
1028 x_app_id IN NUMBER,
1029 x_app_no IN NUMBER,
1030 x_choice_no IN NUMBER,
1031 x_last_change IN DATE,
1032 x_institute_code IN VARCHAR2,
1033 x_ucas_program_code IN VARCHAR2,
1034 x_oss_program_code IN VARCHAR2,
1035 x_oss_program_version IN NUMBER,
1036 x_oss_attendance_type IN VARCHAR2,
1037 x_oss_attendance_mode IN VARCHAR2,
1038 x_campus IN VARCHAR2,
1039 x_oss_location IN VARCHAR2,
1040 x_faculty IN VARCHAR2,
1041 x_entry_year IN NUMBER,
1042 x_entry_month IN NUMBER,
1043 x_point_of_entry IN NUMBER,
1044 x_home IN VARCHAR2,
1045 x_deferred IN VARCHAR2,
1046 x_route_b_pref_round IN NUMBER,
1047 x_route_b_actual_round IN NUMBER,
1048 x_condition_category IN VARCHAR2,
1049 x_condition_code IN VARCHAR2,
1050 x_decision IN VARCHAR2,
1051 x_decision_date IN DATE,
1052 x_decision_number IN NUMBER,
1053 x_reply IN VARCHAR2,
1054 x_summary_of_cond IN VARCHAR2,
1055 x_choice_cancelled IN VARCHAR2,
1056 x_action IN VARCHAR2,
1057 x_substitution IN VARCHAR2,
1058 x_date_substituted IN DATE,
1059 x_prev_institution IN VARCHAR2,
1060 x_prev_course IN VARCHAR2,
1061 x_prev_campus IN VARCHAR2,
1062 x_ucas_amendment IN VARCHAR2,
1063 x_withdrawal_reason IN VARCHAR2,
1064 x_offer_course IN VARCHAR2,
1065 x_offer_campus IN VARCHAR2,
1066 x_offer_crse_length IN NUMBER,
1067 x_offer_entry_month IN VARCHAR2,
1068 x_offer_entry_year IN VARCHAR2,
1069 x_offer_entry_point IN VARCHAR2,
1070 x_offer_text IN VARCHAR2,
1071 x_export_to_oss_status IN VARCHAR2,
1072 x_error_code IN VARCHAR2,
1073 x_request_id IN NUMBER,
1074 x_batch_id IN NUMBER,
1075 -- The below column is added newly in UCFD06 - 24SEP2002 - Bug#2574566
1076 x_extra_round_nbr IN NUMBER,
1077 -- The below column is added newly in UCFD102 - Bug#2643048
1078 x_system_code IN VARCHAR2 ,
1079 x_part_time IN VARCHAR2 ,
1080 x_interview IN DATE ,
1081 x_late_application IN VARCHAR2 ,
1082 x_modular IN VARCHAR2 ,
1083 x_residential IN VARCHAR2 ,
1084 -- smaddali added this column for ucfd203 build bug #2669208
1085 x_ucas_cycle IN NUMBER
1086 ) IS
1087 /*
1088 || Created By : [email protected]
1089 || Created On : 21-FEB-2002
1090 || Purpose : Handles the LOCK mechanism for the table.
1091 || Known limitations, enhancements or remarks :
1092 || Change History :
1093 || Who When What
1094 || smaddali 10-jun-03 adding ucas_cycle and obsoleting timestamps for bug#2669208 ucfd203 build
1095 || (reverse chronological order - newest change first)
1096 */
1097 CURSOR c1 IS
1098 SELECT
1099 app_id,
1100 app_no,
1101 choice_no,
1102 last_change,
1103 institute_code,
1104 ucas_program_code,
1105 oss_program_code,
1106 oss_program_version,
1107 oss_attendance_type,
1108 oss_attendance_mode,
1109 campus,
1110 oss_location,
1111 faculty,
1112 entry_year,
1113 entry_month,
1114 point_of_entry,
1115 home,
1116 deferred,
1117 route_b_pref_round,
1118 route_b_actual_round,
1119 condition_category,
1120 condition_code,
1121 decision,
1122 decision_date,
1123 decision_number,
1124 reply,
1125 summary_of_cond,
1126 choice_cancelled,
1127 action,
1128 substitution,
1129 date_substituted,
1130 prev_institution,
1131 prev_course,
1132 prev_campus,
1133 ucas_amendment,
1134 withdrawal_reason,
1135 offer_course,
1136 offer_campus,
1137 offer_crse_length,
1138 offer_entry_month,
1139 offer_entry_year,
1140 offer_entry_point,
1141 offer_text,
1142 export_to_oss_status ,
1143 error_code ,
1144 request_id ,
1145 batch_id,
1146 extra_round_nbr,
1147 -- The below column is added newly in UCFD102 - Bug#2643048
1148 system_code,
1149 part_time,
1150 interview,
1151 late_application,
1152 modular,
1153 residential,
1154 -- smaddali added new column for ucfd203 - bug#2669208
1155 ucas_cycle
1156 FROM igs_uc_app_choices
1157 WHERE rowid = x_rowid
1158 FOR UPDATE NOWAIT;
1159
1160 tlinfo c1%ROWTYPE;
1161
1162 BEGIN
1163
1164 OPEN c1;
1165 FETCH c1 INTO tlinfo;
1166 IF (c1%notfound) THEN
1167 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1168 igs_ge_msg_stack.add;
1169 CLOSE c1;
1170 app_exception.raise_exception;
1171 RETURN;
1172 END IF;
1173 CLOSE c1;
1174
1175 IF (
1176 (tlinfo.app_id = x_app_id)
1177 AND (tlinfo.app_no = x_app_no)
1178 AND (tlinfo.choice_no = x_choice_no)
1179 AND (tlinfo.last_change = x_last_change)
1180 AND (tlinfo.institute_code = x_institute_code)
1181 AND (tlinfo.ucas_program_code = x_ucas_program_code)
1182 AND ((tlinfo.oss_program_code = x_oss_program_code) OR ((tlinfo.oss_program_code IS NULL) AND (X_oss_program_code IS NULL)))
1183 AND ((tlinfo.oss_program_version = x_oss_program_version) OR ((tlinfo.oss_program_version IS NULL) AND (X_oss_program_version IS NULL)))
1184 AND ((tlinfo.oss_attendance_type = x_oss_attendance_type) OR ((tlinfo.oss_attendance_type IS NULL) AND (X_oss_attendance_type IS NULL)))
1185 AND ((tlinfo.oss_attendance_mode = x_oss_attendance_mode) OR ((tlinfo.oss_attendance_mode IS NULL) AND (X_oss_attendance_mode IS NULL)))
1186 AND (tlinfo.campus = x_campus)
1187 AND ((tlinfo.oss_location = x_oss_location) OR ((tlinfo.oss_location IS NULL) AND (X_oss_location IS NULL)))
1188 AND ((tlinfo.faculty = x_faculty) OR ((tlinfo.faculty IS NULL) AND (X_faculty IS NULL)))
1189 AND (tlinfo.entry_year = x_entry_year)
1190 AND (tlinfo.entry_month = x_entry_month)
1191 AND ((tlinfo.point_of_entry = x_point_of_entry) OR ((tlinfo.point_of_entry IS NULL) AND (X_point_of_entry IS NULL)))
1192 AND (tlinfo.home = x_home)
1193 AND (tlinfo.deferred = x_deferred)
1194 AND ((tlinfo.route_b_pref_round = x_route_b_pref_round) OR ((tlinfo.route_b_pref_round IS NULL) AND (X_route_b_pref_round IS NULL)))
1195 AND ((tlinfo.route_b_actual_round = x_route_b_actual_round) OR ((tlinfo.route_b_actual_round IS NULL) AND (X_route_b_actual_round IS NULL)))
1196 AND ((tlinfo.condition_category = x_condition_category) OR ((tlinfo.condition_category IS NULL) AND (X_condition_category IS NULL)))
1197 AND ((tlinfo.condition_code = x_condition_code) OR ((tlinfo.condition_code IS NULL) AND (X_condition_code IS NULL)))
1198 AND ((tlinfo.decision = x_decision) OR ((tlinfo.decision IS NULL) AND (X_decision IS NULL)))
1199 AND ((tlinfo.decision_date = x_decision_date) OR ((tlinfo.decision_date IS NULL) AND (X_decision_date IS NULL)))
1200 AND ((tlinfo.decision_number = x_decision_number) OR ((tlinfo.decision_number IS NULL) AND (X_decision_number IS NULL)))
1201 AND ((tlinfo.reply = x_reply) OR ((tlinfo.reply IS NULL) AND (X_reply IS NULL)))
1202 AND ((tlinfo.summary_of_cond = x_summary_of_cond) OR ((tlinfo.summary_of_cond IS NULL) AND (X_summary_of_cond IS NULL)))
1203 AND ((tlinfo.choice_cancelled = x_choice_cancelled) OR ((tlinfo.choice_cancelled IS NULL) AND (X_choice_cancelled IS NULL)))
1204 AND ((tlinfo.action = x_action) OR ((tlinfo.action IS NULL) AND (X_action IS NULL)))
1205 AND ((tlinfo.substitution = x_substitution) OR ((tlinfo.substitution IS NULL) AND (X_substitution IS NULL)))
1206 AND ((tlinfo.date_substituted = x_date_substituted) OR ((tlinfo.date_substituted IS NULL) AND (X_date_substituted IS NULL)))
1207 AND ((tlinfo.prev_institution = x_prev_institution) OR ((tlinfo.prev_institution IS NULL) AND (X_prev_institution IS NULL)))
1208 AND ((tlinfo.prev_course = x_prev_course) OR ((tlinfo.prev_course IS NULL) AND (X_prev_course IS NULL)))
1209 AND ((tlinfo.prev_campus = x_prev_campus) OR ((tlinfo.prev_campus IS NULL) AND (X_prev_campus IS NULL)))
1210 AND ((tlinfo.ucas_amendment = x_ucas_amendment) OR ((tlinfo.ucas_amendment IS NULL) AND (X_ucas_amendment IS NULL)))
1211 AND ((tlinfo.withdrawal_reason = x_withdrawal_reason) OR ((tlinfo.withdrawal_reason IS NULL) AND (X_withdrawal_reason IS NULL)))
1212 AND ((tlinfo.offer_course = x_offer_course) OR ((tlinfo.offer_course IS NULL) AND (X_offer_course IS NULL)))
1213 AND ((tlinfo.offer_campus = x_offer_campus) OR ((tlinfo.offer_campus IS NULL) AND (X_offer_campus IS NULL)))
1214 AND ((tlinfo.offer_crse_length = x_offer_crse_length) OR ((tlinfo.offer_crse_length IS NULL) AND (X_offer_crse_length IS NULL)))
1215 AND ((tlinfo.offer_entry_month = x_offer_entry_month) OR ((tlinfo.offer_entry_month IS NULL) AND (X_offer_entry_month IS NULL)))
1216 AND ((tlinfo.offer_entry_year = x_offer_entry_year) OR ((tlinfo.offer_entry_year IS NULL) AND (X_offer_entry_year IS NULL)))
1217 AND ((tlinfo.offer_entry_point = x_offer_entry_point) OR ((tlinfo.offer_entry_point IS NULL) AND (X_offer_entry_point IS NULL)))
1218 AND ((tlinfo.offer_text = x_offer_text) OR ((tlinfo.offer_text IS NULL) AND (X_offer_text IS NULL)))
1219 AND ((tlinfo.export_to_oss_status = x_export_to_oss_status ) OR ((tlinfo.export_to_oss_status IS NULL) AND (X_export_to_oss_status IS NULL)))
1220 AND ((tlinfo.error_code = x_error_code) OR ((tlinfo.error_code IS NULL) AND (X_error_code IS NULL)))
1221 AND ((tlinfo.request_id = x_request_id) OR ((tlinfo.request_id IS NULL) AND (X_request_id IS NULL)))
1222 AND ((tlinfo.batch_id = x_batch_id) OR ((tlinfo.batch_id IS NULL) AND (X_batch_id IS NULL)))
1223 AND ((tlinfo.extra_round_nbr = x_extra_round_nbr) OR ((tlinfo.extra_round_nbr IS NULL) AND (x_extra_round_nbr IS NULL)))
1224 AND ((tlinfo.system_code = x_system_code) )
1225 AND ((tlinfo.part_time = x_part_time) OR ((tlinfo.part_time IS NULL) AND (x_part_time IS NULL)))
1226 AND ((tlinfo.interview = x_interview) OR ((tlinfo.interview IS NULL) AND (x_interview IS NULL)))
1227 AND ((tlinfo.late_application = x_late_application) OR ((tlinfo.late_application IS NULL) AND (x_late_application IS NULL)))
1228 AND ((tlinfo.modular = x_modular) OR ((tlinfo.modular IS NULL) AND (x_modular IS NULL)))
1229 AND ((tlinfo.residential = x_residential) OR ((tlinfo.residential IS NULL) AND (x_residential IS NULL)))
1230 -- smaddali added new column for ucfd203 - bug#2669208
1231 AND (tlinfo.ucas_cycle = x_ucas_cycle)
1232 ) THEN
1233 NULL;
1234 ELSE
1235 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1236 igs_ge_msg_stack.add;
1237 app_exception.raise_exception;
1238 END IF;
1239
1240 RETURN;
1241
1242 END lock_row;
1243
1244
1245 PROCEDURE update_row (
1246 x_rowid IN VARCHAR2,
1247 x_app_choice_id IN NUMBER,
1248 x_app_id IN NUMBER,
1249 x_app_no IN NUMBER,
1250 x_choice_no IN NUMBER,
1251 x_last_change IN DATE,
1252 x_institute_code IN VARCHAR2,
1253 x_ucas_program_code IN VARCHAR2,
1254 x_oss_program_code IN VARCHAR2,
1255 x_oss_program_version IN NUMBER,
1256 x_oss_attendance_type IN VARCHAR2,
1257 x_oss_attendance_mode IN VARCHAR2,
1258 x_campus IN VARCHAR2,
1259 x_oss_location IN VARCHAR2,
1260 x_faculty IN VARCHAR2,
1261 x_entry_year IN NUMBER,
1262 x_entry_month IN NUMBER,
1263 x_point_of_entry IN NUMBER,
1264 x_home IN VARCHAR2,
1265 x_deferred IN VARCHAR2,
1266 x_route_b_pref_round IN NUMBER,
1267 x_route_b_actual_round IN NUMBER,
1268 x_condition_category IN VARCHAR2,
1269 x_condition_code IN VARCHAR2,
1270 x_decision IN VARCHAR2,
1271 x_decision_date IN DATE,
1272 x_decision_number IN NUMBER,
1273 x_reply IN VARCHAR2,
1274 x_summary_of_cond IN VARCHAR2,
1275 x_choice_cancelled IN VARCHAR2,
1276 x_action IN VARCHAR2,
1277 x_substitution IN VARCHAR2,
1278 x_date_substituted IN DATE,
1279 x_prev_institution IN VARCHAR2,
1280 x_prev_course IN VARCHAR2,
1281 x_prev_campus IN VARCHAR2,
1282 x_ucas_amendment IN VARCHAR2,
1283 x_withdrawal_reason IN VARCHAR2,
1284 x_offer_course IN VARCHAR2,
1285 x_offer_campus IN VARCHAR2,
1286 x_offer_crse_length IN NUMBER,
1287 x_offer_entry_month IN VARCHAR2,
1288 x_offer_entry_year IN VARCHAR2,
1289 x_offer_entry_point IN VARCHAR2,
1290 x_offer_text IN VARCHAR2,
1291 x_mode IN VARCHAR2 ,
1292 x_export_to_oss_status IN VARCHAR2,
1293 x_error_code IN VARCHAR2,
1294 x_request_id IN NUMBER,
1295 x_batch_id IN NUMBER,
1296 -- The below column is added newly in UCFD06 - 24SEP2002 - Bug#2574566
1297 x_extra_round_nbr IN NUMBER,
1298 -- The below column is added newly in UCFD102- Bug#2643048
1299 x_system_code IN VARCHAR2 ,
1300 x_part_time IN VARCHAR2 ,
1301 x_interview IN DATE ,
1302 x_late_application IN VARCHAR2 ,
1303 x_modular IN VARCHAR2 ,
1304 x_residential IN VARCHAR2 ,
1305 -- smaddali added this column for ucfd203 build bug #2669208
1306 x_ucas_cycle IN NUMBER
1307 ) IS
1308 /*
1309 || Created By : [email protected]
1310 || Created On : 21-FEB-2002
1311 || Purpose : Handles the UPDATE DML logic for the table.
1312 || Known limitations, enhancements or remarks :
1313 || Change History :
1314 || Who When What
1315 || smaddali 10-jun-03 adding ucas_cycle and obsoleting timestamps for bug#2669208 ucfd203 build
1316 || (reverse chronological order - newest change first)
1317 */
1318 x_last_update_date DATE ;
1319 x_last_updated_by NUMBER;
1320 x_last_update_login NUMBER;
1321
1322 BEGIN
1323
1324 x_last_update_date := SYSDATE;
1325 IF (X_MODE = 'I') THEN
1326 x_last_updated_by := 1;
1327 x_last_update_login := 0;
1328 ELSIF (X_MODE IN ('R', 'S')) THEN
1329 x_last_updated_by := fnd_global.user_id;
1330 IF x_last_updated_by IS NULL THEN
1331 x_last_updated_by := -1;
1332 END IF;
1333 x_last_update_login := fnd_global.login_id;
1334 IF (x_last_update_login IS NULL) THEN
1335 x_last_update_login := -1;
1336 END IF;
1337 ELSE
1338 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1339 igs_ge_msg_stack.add;
1340 app_exception.raise_exception;
1341 END IF;
1342
1343 before_dml(
1344 p_action => 'UPDATE',
1345 x_rowid => x_rowid,
1346 x_app_choice_id => x_app_choice_id,
1347 x_app_id => x_app_id,
1348 x_app_no => x_app_no,
1349 x_choice_no => x_choice_no,
1350 x_last_change => x_last_change,
1351 x_institute_code => x_institute_code,
1352 x_ucas_program_code => x_ucas_program_code,
1353 x_oss_program_code => x_oss_program_code,
1354 x_oss_program_version => x_oss_program_version,
1355 x_oss_attendance_type => x_oss_attendance_type,
1356 x_oss_attendance_mode => x_oss_attendance_mode,
1357 x_campus => x_campus,
1358 x_oss_location => x_oss_location,
1359 x_faculty => x_faculty,
1360 x_entry_year => x_entry_year,
1361 x_entry_month => x_entry_month,
1362 x_point_of_entry => x_point_of_entry,
1363 x_home => x_home,
1364 x_deferred => x_deferred,
1365 x_route_b_pref_round => x_route_b_pref_round,
1366 x_route_b_actual_round => x_route_b_actual_round,
1367 x_condition_category => x_condition_category,
1368 x_condition_code => x_condition_code,
1369 x_decision => x_decision,
1370 x_decision_date => x_decision_date,
1371 x_decision_number => x_decision_number,
1372 x_reply => x_reply,
1373 x_summary_of_cond => x_summary_of_cond,
1374 x_choice_cancelled => x_choice_cancelled,
1375 x_action => x_action,
1376 x_substitution => x_substitution,
1377 x_date_substituted => x_date_substituted,
1378 x_prev_institution => x_prev_institution,
1379 x_prev_course => x_prev_course,
1380 x_prev_campus => x_prev_campus,
1381 x_ucas_amendment => x_ucas_amendment,
1382 x_withdrawal_reason => x_withdrawal_reason,
1383 x_offer_course => x_offer_course,
1384 x_offer_campus => x_offer_campus,
1385 x_offer_crse_length => x_offer_crse_length,
1386 x_offer_entry_month => x_offer_entry_month,
1387 x_offer_entry_year => x_offer_entry_year,
1388 x_offer_entry_point => x_offer_entry_point,
1389 x_offer_text => x_offer_text,
1390 x_creation_date => x_last_update_date,
1391 x_created_by => x_last_updated_by,
1392 x_last_update_date => x_last_update_date,
1393 x_last_updated_by => x_last_updated_by,
1394 x_last_update_login => x_last_update_login ,
1395 x_export_to_oss_status =>x_export_to_oss_status,
1396 x_error_code =>x_error_code ,
1397 x_request_id =>x_request_id,
1398 x_batch_id =>x_batch_id,
1399 x_extra_round_nbr =>x_extra_round_nbr,
1400 x_system_code =>x_system_code ,
1401 x_part_time =>x_part_time,
1402 x_interview =>x_interview,
1403 x_late_application =>x_late_application,
1404 x_modular =>x_modular,
1405 x_residential =>x_residential,
1406 -- smaddali added new column for ucfd203 - bug#2669208
1407 x_ucas_cycle => x_ucas_cycle
1408 );
1409
1410 IF (x_mode = 'S') THEN
1411 igs_sc_gen_001.set_ctx('R');
1412 END IF;
1413 UPDATE igs_uc_app_choices
1414 SET
1415 app_id = new_references.app_id,
1416 app_no = new_references.app_no,
1417 choice_no = new_references.choice_no,
1418 last_change = new_references.last_change,
1419 institute_code = new_references.institute_code,
1420 ucas_program_code = new_references.ucas_program_code,
1421 oss_program_code = new_references.oss_program_code,
1422 oss_program_version = new_references.oss_program_version,
1423 oss_attendance_type = new_references.oss_attendance_type,
1424 oss_attendance_mode = new_references.oss_attendance_mode,
1425 campus = new_references.campus,
1426 oss_location = new_references.oss_location,
1427 faculty = new_references.faculty,
1428 entry_year = new_references.entry_year,
1429 entry_month = new_references.entry_month,
1430 point_of_entry = new_references.point_of_entry,
1431 home = new_references.home,
1432 deferred = new_references.deferred,
1433 route_b_pref_round = new_references.route_b_pref_round,
1434 route_b_actual_round = new_references.route_b_actual_round,
1435 condition_category = new_references.condition_category,
1436 condition_code = new_references.condition_code,
1437 decision = new_references.decision,
1438 decision_date = new_references.decision_date,
1439 decision_number = new_references.decision_number,
1440 reply = new_references.reply,
1441 summary_of_cond = new_references.summary_of_cond,
1442 choice_cancelled = new_references.choice_cancelled,
1443 action = new_references.action,
1444 substitution = new_references.substitution,
1445 date_substituted = new_references.date_substituted,
1446 prev_institution = new_references.prev_institution,
1447 prev_course = new_references.prev_course,
1448 prev_campus = new_references.prev_campus,
1449 ucas_amendment = new_references.ucas_amendment,
1450 withdrawal_reason = new_references.withdrawal_reason,
1451 offer_course = new_references.offer_course,
1452 offer_campus = new_references.offer_campus,
1453 offer_crse_length = new_references.offer_crse_length,
1454 offer_entry_month = new_references.offer_entry_month,
1455 offer_entry_year = new_references.offer_entry_year,
1456 offer_entry_point = new_references.offer_entry_point,
1457 offer_text = new_references.offer_text,
1458 last_update_date = new_references.last_update_date,
1459 last_updated_by = new_references.last_updated_by,
1460 last_update_login = new_references.last_update_login ,
1461 export_to_oss_status = new_references.export_to_oss_status,
1462 error_code = new_references.error_code ,
1463 request_id = new_references.request_id,
1464 batch_id = new_references.batch_id,
1465 extra_round_nbr = new_references.extra_round_nbr,
1466 -- The below column is added newly in UCFD102- Bug#2643048
1467 system_code = new_references.system_code,
1468 part_time = new_references.part_time ,
1469 interview = new_references.interview ,
1470 late_application = new_references.late_application ,
1471 modular = new_references.modular,
1472 residential = new_references.residential,
1473 -- smaddali added new column for ucfd203 - bug#2669208
1474 ucas_cycle = new_references.ucas_cycle
1475 WHERE rowid = x_rowid;
1476
1477 IF (SQL%NOTFOUND) THEN
1478 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1479 igs_ge_msg_stack.add;
1480 igs_sc_gen_001.unset_ctx('R');
1481 app_exception.raise_exception;
1482 END IF;
1483 IF (x_mode = 'S') THEN
1484 igs_sc_gen_001.unset_ctx('R');
1485 END IF;
1486
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 IF (SQLCODE = (-28115)) THEN
1490 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1491 fnd_message.set_token ('ERR_CD', SQLCODE);
1492 igs_ge_msg_stack.add;
1493 igs_sc_gen_001.unset_ctx('R');
1494 app_exception.raise_exception;
1495 ELSE
1496 igs_sc_gen_001.unset_ctx('R');
1497 RAISE;
1498 END IF;
1499
1500 END update_row;
1501
1502
1503 PROCEDURE add_row (
1504 x_rowid IN OUT NOCOPY VARCHAR2,
1505 x_app_choice_id IN OUT NOCOPY NUMBER,
1506 x_app_id IN NUMBER,
1507 x_app_no IN NUMBER,
1508 x_choice_no IN NUMBER,
1509 x_last_change IN DATE,
1510 x_institute_code IN VARCHAR2,
1511 x_ucas_program_code IN VARCHAR2,
1512 x_oss_program_code IN VARCHAR2,
1513 x_oss_program_version IN NUMBER,
1514 x_oss_attendance_type IN VARCHAR2,
1515 x_oss_attendance_mode IN VARCHAR2,
1516 x_campus IN VARCHAR2,
1517 x_oss_location IN VARCHAR2,
1518 x_faculty IN VARCHAR2,
1519 x_entry_year IN NUMBER,
1520 x_entry_month IN NUMBER,
1521 x_point_of_entry IN NUMBER,
1522 x_home IN VARCHAR2,
1523 x_deferred IN VARCHAR2,
1524 x_route_b_pref_round IN NUMBER,
1525 x_route_b_actual_round IN NUMBER,
1526 x_condition_category IN VARCHAR2,
1527 x_condition_code IN VARCHAR2,
1528 x_decision IN VARCHAR2,
1529 x_decision_date IN DATE,
1530 x_decision_number IN NUMBER,
1531 x_reply IN VARCHAR2,
1532 x_summary_of_cond IN VARCHAR2,
1533 x_choice_cancelled IN VARCHAR2,
1534 x_action IN VARCHAR2,
1535 x_substitution IN VARCHAR2,
1536 x_date_substituted IN DATE,
1537 x_prev_institution IN VARCHAR2,
1538 x_prev_course IN VARCHAR2,
1539 x_prev_campus IN VARCHAR2,
1540 x_ucas_amendment IN VARCHAR2,
1541 x_withdrawal_reason IN VARCHAR2,
1542 x_offer_course IN VARCHAR2,
1543 x_offer_campus IN VARCHAR2,
1544 x_offer_crse_length IN NUMBER,
1545 x_offer_entry_month IN VARCHAR2,
1546 x_offer_entry_year IN VARCHAR2,
1547 x_offer_entry_point IN VARCHAR2,
1548 x_offer_text IN VARCHAR2,
1549 x_mode IN VARCHAR2 ,
1550 x_export_to_oss_status IN VARCHAR2,
1551 x_error_code IN VARCHAR2,
1552 x_request_id IN NUMBER,
1553 x_batch_id IN NUMBER,
1554 -- The below column is added newly in UCFD06 - 24SEP2002 - Bug#2574566
1555 x_extra_round_nbr IN NUMBER,
1556 -- The below column is added newly in UCFD102- Bug#2640438
1557 x_system_code IN VARCHAR2 ,
1558 x_part_time IN VARCHAR2 ,
1559 x_interview IN DATE ,
1560 x_late_application IN VARCHAR2 ,
1561 x_modular IN VARCHAR2 ,
1562 x_residential IN VARCHAR2 ,
1563 -- smaddali added this column for ucfd203 build bug #2669208
1564 x_ucas_cycle IN NUMBER
1565 ) IS
1566 /*
1567 || Created By : [email protected]
1568 || Created On : 21-FEB-2002
1569 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1570 || Known limitations, enhancements or remarks :
1571 || Change History :
1572 || Who When What
1573 || smaddali 10-jun-03 adding ucas_cycle and obsoleting timestamps for bug#2669208 ucfd203 build
1574 || (reverse chronological order - newest change first)
1575 */
1576 CURSOR c1 IS
1577 SELECT rowid
1578 FROM igs_uc_app_choices
1579 WHERE app_choice_id = x_app_choice_id;
1580
1581 BEGIN
1582
1583 OPEN c1;
1584 FETCH c1 INTO x_rowid;
1585 IF (c1%NOTFOUND) THEN
1586 CLOSE c1;
1587
1588 insert_row (
1589 x_rowid,
1590 x_app_choice_id,
1591 x_app_id,
1592 x_app_no,
1593 x_choice_no,
1594 x_last_change,
1595 x_institute_code,
1596 x_ucas_program_code,
1597 x_oss_program_code,
1598 x_oss_program_version,
1599 x_oss_attendance_type,
1600 x_oss_attendance_mode,
1601 x_campus,
1602 x_oss_location,
1603 x_faculty,
1604 x_entry_year,
1605 x_entry_month,
1606 x_point_of_entry,
1607 x_home,
1608 x_deferred,
1609 x_route_b_pref_round,
1610 x_route_b_actual_round,
1611 x_condition_category,
1612 x_condition_code,
1613 x_decision,
1614 x_decision_date,
1615 x_decision_number,
1616 x_reply,
1617 x_summary_of_cond,
1618 x_choice_cancelled,
1619 x_action,
1620 x_substitution,
1621 x_date_substituted,
1622 x_prev_institution,
1623 x_prev_course,
1624 x_prev_campus,
1625 x_ucas_amendment,
1626 x_withdrawal_reason,
1627 x_offer_course,
1628 x_offer_campus,
1629 x_offer_crse_length,
1630 x_offer_entry_month,
1631 x_offer_entry_year,
1632 x_offer_entry_point,
1633 x_offer_text,
1634 x_mode ,
1635 x_export_to_oss_status ,
1636 x_error_code ,
1637 x_request_id ,
1638 x_batch_id ,
1639 x_extra_round_nbr,
1640 x_system_code ,
1641 x_part_time ,
1642 x_interview ,
1643 x_late_application ,
1644 x_modular ,
1645 x_residential ,
1646 -- smaddali added new column for ucfd203 - bug#2669208
1647 x_ucas_cycle
1648 );
1649 RETURN;
1650 END IF;
1651 CLOSE c1;
1652
1653 update_row (
1654 x_rowid,
1655 x_app_choice_id,
1656 x_app_id,
1657 x_app_no,
1658 x_choice_no,
1659 x_last_change,
1660 x_institute_code,
1661 x_ucas_program_code,
1662 x_oss_program_code,
1663 x_oss_program_version,
1664 x_oss_attendance_type,
1665 x_oss_attendance_mode,
1666 x_campus,
1667 x_oss_location,
1668 x_faculty,
1669 x_entry_year,
1670 x_entry_month,
1671 x_point_of_entry,
1672 x_home,
1673 x_deferred,
1674 x_route_b_pref_round,
1675 x_route_b_actual_round,
1676 x_condition_category,
1677 x_condition_code,
1678 x_decision,
1679 x_decision_date,
1680 x_decision_number,
1681 x_reply,
1682 x_summary_of_cond,
1683 x_choice_cancelled,
1684 x_action,
1685 x_substitution,
1686 x_date_substituted,
1687 x_prev_institution,
1688 x_prev_course,
1689 x_prev_campus,
1690 x_ucas_amendment,
1691 x_withdrawal_reason,
1692 x_offer_course,
1693 x_offer_campus,
1694 x_offer_crse_length,
1695 x_offer_entry_month,
1696 x_offer_entry_year,
1697 x_offer_entry_point,
1698 x_offer_text,
1699 x_mode ,
1700 x_export_to_oss_status ,
1701 x_error_code ,
1702 x_request_id ,
1703 x_batch_id ,
1704 x_extra_round_nbr,
1705 x_system_code ,
1706 x_part_time ,
1707 x_interview ,
1708 x_late_application ,
1709 x_modular ,
1710 x_residential ,
1711 -- smaddali added new column for ucfd203 - bug#2669208
1712 x_ucas_cycle
1713 );
1714
1715 END add_row;
1716
1717
1718 PROCEDURE delete_row (
1719 x_rowid IN VARCHAR2,
1720 x_mode IN VARCHAR2
1721 ) IS
1722 /*
1723 || Created By : [email protected]
1724 || Created On : 21-FEB-2002
1725 || Purpose : Handles the DELETE DML logic for the table.
1726 || Known limitations, enhancements or remarks :
1727 || Change History :
1728 || Who When What
1729 || (reverse chronological order - newest change first)
1730 */
1731 BEGIN
1732
1733 before_dml (
1734 p_action => 'DELETE',
1735 x_rowid => x_rowid
1736 );
1737
1738 IF (x_mode = 'S') THEN
1739 igs_sc_gen_001.set_ctx('R');
1740 END IF;
1741 DELETE FROM igs_uc_app_choices
1742 WHERE rowid = x_rowid;
1743
1744 IF (SQL%NOTFOUND) THEN
1745 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1746 igs_ge_msg_stack.add;
1747 igs_sc_gen_001.unset_ctx('R');
1748 app_exception.raise_exception;
1749 END IF;
1750 IF (x_mode = 'S') THEN
1751 igs_sc_gen_001.unset_ctx('R');
1752 END IF;
1753
1754
1755 END delete_row;
1756
1757
1758 END igs_uc_app_choices_pkg;