[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_CRSE_DETS_PKG
Source
1 PACKAGE BODY igs_uc_crse_dets_pkg AS
2 /* $Header: IGSXI14B.pls 115.12 2003/06/11 10:57:55 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_crse_dets%ROWTYPE;
6 new_references igs_uc_crse_dets%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ucas_program_code IN VARCHAR2,
12 x_oss_program_code IN VARCHAR2,
13 x_oss_program_version IN NUMBER ,
14 x_institute IN VARCHAR2,
15 x_uvcourse_updater IN VARCHAR2,
16 x_uvcrsevac_updater IN VARCHAR2,
17 x_short_title IN VARCHAR2,
18 x_long_title IN VARCHAR2,
19 x_ucas_campus IN VARCHAR2,
20 x_oss_location IN VARCHAR2,
21 x_faculty IN VARCHAR2,
22 x_total_no_of_seats IN NUMBER ,
23 x_min_entry_points IN NUMBER ,
24 x_max_entry_points IN NUMBER ,
25 x_current_validity IN VARCHAR2,
26 x_deferred_validity IN VARCHAR2,
27 x_term_1_start IN DATE ,
28 x_term_1_end IN DATE ,
29 x_term_2_start IN DATE ,
30 x_term_2_end IN DATE ,
31 x_term_3_start IN DATE ,
32 x_term_3_end IN DATE ,
33 x_term_4_start IN DATE ,
34 x_term_4_end IN DATE ,
35 x_cl_updated IN VARCHAR2,
36 x_cl_date IN DATE ,
37 x_vacancy_status IN VARCHAR2,
38 x_no_of_vacancy IN VARCHAR2,
39 x_score IN NUMBER ,
40 x_rb_full IN VARCHAR2,
41 x_scot_vac IN VARCHAR2,
42 x_sent_to_ucas IN VARCHAR2,
43 x_creation_date IN DATE ,
44 x_created_by IN NUMBER ,
45 x_last_update_date IN DATE ,
46 x_last_updated_by IN NUMBER ,
47 x_last_update_login IN NUMBER ,
48 x_ucas_system_id IN NUMBER ,
49 x_oss_attendance_type IN VARCHAR2,
50 x_oss_attendance_mode IN VARCHAR2,
51 x_joint_admission_ind IN VARCHAR2,
52 x_open_extra_ind IN VARCHAR2,
53 x_system_code IN VARCHAR2,
54 x_clearing_options IN VARCHAR2,
55 x_imported IN VARCHAR2,
56 x_keywrds_changed IN VARCHAR2
57 ) AS
58 /*
59 || Created By : rgopalan
60 || Created On : 01-OCT-2001
61 || Purpose : Initialises the Old and New references for the columns of the table.
62 || Known limitations, enhancements or remarks :
63 || Change History :
64 || Who When What
65 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208
66 || (reverse chronological order - newest change first)
67 */
68
69 CURSOR cur_old_ref_values IS
70 SELECT *
71 FROM IGS_UC_CRSE_DETS
72 WHERE rowid = x_rowid;
73
74 BEGIN
75
76 l_rowid := x_rowid;
77
78 -- Code for setting the Old and New Reference Values.
79 -- Populate Old Values.
80 OPEN cur_old_ref_values;
81 FETCH cur_old_ref_values INTO old_references;
82 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
83 CLOSE cur_old_ref_values;
84 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
85 igs_ge_msg_stack.add;
86 app_exception.raise_exception;
87 RETURN;
88 END IF;
89 CLOSE cur_old_ref_values;
90
91 -- Populate New Values.
92 new_references.ucas_program_code := x_ucas_program_code;
93 new_references.oss_program_code := x_oss_program_code;
94 new_references.oss_program_version := x_oss_program_version;
95 new_references.institute := x_institute;
96 new_references.uvcourse_updater := x_uvcourse_updater;
97 new_references.uvcrsevac_updater := x_uvcrsevac_updater;
98 new_references.short_title := x_short_title;
99 new_references.long_title := x_long_title;
100 new_references.ucas_campus := x_ucas_campus;
101 new_references.oss_location := x_oss_location;
102 new_references.faculty := x_faculty;
103 new_references.total_no_of_seats := x_total_no_of_seats;
104 new_references.min_entry_points := x_min_entry_points;
105 new_references.max_entry_points := x_max_entry_points;
106 new_references.current_validity := x_current_validity;
107 new_references.deferred_validity := x_deferred_validity;
108 new_references.term_1_start := x_term_1_start;
109 new_references.term_1_end := x_term_1_end;
110 new_references.term_2_start := x_term_2_start;
111 new_references.term_2_end := x_term_2_end;
112 new_references.term_3_start := x_term_3_start;
113 new_references.term_3_end := x_term_3_end;
114 new_references.term_4_start := x_term_4_start;
115 new_references.term_4_end := x_term_4_end;
116 new_references.cl_updated := x_cl_updated;
117 new_references.cl_date := x_cl_date;
118 new_references.vacancy_status := x_vacancy_status;
119 new_references.no_of_vacancy := x_no_of_vacancy;
120 new_references.score := x_score;
121 new_references.rb_full := x_rb_full;
122 new_references.scot_vac := x_scot_vac;
123 new_references.sent_to_ucas := x_sent_to_ucas;
124 new_references.ucas_system_id := x_ucas_system_id;
125 new_references.oss_attendance_type := x_oss_attendance_type;
126 new_references.oss_attendance_mode := x_oss_attendance_mode;
127 new_references.joint_admission_ind := x_joint_admission_ind;
128 new_references.open_extra_ind := x_open_extra_ind;
129 new_references.system_code := x_system_code;
130 new_references.clearing_options := x_clearing_options;
131 new_references.imported := x_imported;
132 new_references.keywrds_changed := x_keywrds_changed;
133
134 IF (p_action = 'UPDATE') THEN
135 new_references.creation_date := old_references.creation_date;
136 new_references.created_by := old_references.created_by;
137 ELSE
138 new_references.creation_date := x_creation_date;
139 new_references.created_by := x_created_by;
140 END IF;
141
142 new_references.last_update_date := x_last_update_date;
143 new_references.last_updated_by := x_last_updated_by;
144 new_references.last_update_login := x_last_update_login;
145
146 END set_column_values;
147
148 PROCEDURE check_parent_existance AS
149 /*
150 || Created By : [email protected]
151 || Created On : 11-APR-2002
152 || Purpose : Checks for the existance of Parent records.
153 || Known limitations, enhancements or remarks :
154 || Change History :
155 || Who When What
156 || (reverse chronological order - newest change first)
157 */
158 BEGIN
159
160 IF (((old_references.system_code = new_references.system_code)) OR
161 ((new_references.system_code IS NULL))) THEN
162 NULL;
163 ELSIF NOT igs_uc_defaults_pkg.get_pk_for_validation (
164 new_references.system_code
165 ) THEN
166 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
167 igs_ge_msg_stack.add;
168 app_exception.raise_exception;
169 END IF;
170
171 END check_parent_existance;
172
173
174 FUNCTION get_pk_for_validation (
175 x_ucas_program_code IN VARCHAR2,
176 x_institute IN VARCHAR2,
177 x_ucas_campus IN VARCHAR2 ,
178 x_system_code IN VARCHAR2
179 ) RETURN BOOLEAN AS
180 /*
181 || Created By : rgopalan
182 || Created On : 01-OCT-2001
183 || Purpose : Validates the Primary Key of the table.
184 || Known limitations, enhancements or remarks :
185 || Change History :
186 || Who When What
187 || (reverse chronological order - newest change first)
188 */
189 CURSOR cur_rowid IS
190 SELECT rowid
191 FROM igs_uc_crse_dets
192 WHERE ucas_program_code = x_ucas_program_code
193 AND institute = x_institute
194 AND ucas_campus = x_ucas_campus
195 AND system_code = x_system_code ;
196
197 lv_rowid cur_rowid%RowType;
198
199 BEGIN
200
201 OPEN cur_rowid;
202 FETCH cur_rowid INTO lv_rowid;
203 IF (cur_rowid%FOUND) THEN
204 CLOSE cur_rowid;
205 RETURN(TRUE);
206 ELSE
207 CLOSE cur_rowid;
208 RETURN(FALSE);
209 END IF;
210
211 END get_pk_for_validation;
212
213 PROCEDURE get_fk_igs_uc_defaults (
214 x_system_code IN VARCHAR2
215 ) AS
216 /*
217 || Created By : sunitha maddali
218 || Created On : 10-Jun-2003
219 || Purpose : Validates the Foreign Keys for the table.
220 || Known limitations, enhancements or remarks :
221 || Change History :
222 || Who When What
223 || (reverse chronological order - newest change first)
224 */
225 CURSOR cur_rowid IS
226 SELECT rowid
227 FROM igs_uc_crse_dets
228 WHERE ((system_code = x_system_code));
229
230 lv_rowid cur_rowid%RowType;
231
232 BEGIN
233
234 OPEN cur_rowid;
235 FETCH cur_rowid INTO lv_rowid;
236 IF (cur_rowid%FOUND) THEN
237 CLOSE cur_rowid;
238 fnd_message.set_name ('IGS', 'IGS_UC_UCCSDE_UAS_FK');
239 igs_ge_msg_stack.add;
240 app_exception.raise_exception;
241 RETURN;
242 END IF;
243 CLOSE cur_rowid;
244
245 END get_fk_igs_uc_defaults;
246
247 PROCEDURE before_dml (
248 p_action IN VARCHAR2,
249 x_rowid IN VARCHAR2,
250 x_ucas_program_code IN VARCHAR2,
251 x_oss_program_code IN VARCHAR2,
252 x_oss_program_version IN NUMBER ,
253 x_institute IN VARCHAR2,
254 x_uvcourse_updater IN VARCHAR2,
255 x_uvcrsevac_updater IN VARCHAR2,
256 x_short_title IN VARCHAR2,
257 x_long_title IN VARCHAR2,
258 x_ucas_campus IN VARCHAR2,
259 x_oss_location IN VARCHAR2,
260 x_faculty IN VARCHAR2,
261 x_total_no_of_seats IN NUMBER ,
262 x_min_entry_points IN NUMBER ,
263 x_max_entry_points IN NUMBER ,
264 x_current_validity IN VARCHAR2,
265 x_deferred_validity IN VARCHAR2,
266 x_term_1_start IN DATE ,
267 x_term_1_end IN DATE ,
268 x_term_2_start IN DATE ,
269 x_term_2_end IN DATE ,
270 x_term_3_start IN DATE ,
271 x_term_3_end IN DATE ,
272 x_term_4_start IN DATE ,
273 x_term_4_end IN DATE ,
274 x_cl_updated IN VARCHAR2,
275 x_cl_date IN DATE ,
276 x_vacancy_status IN VARCHAR2,
277 x_no_of_vacancy IN VARCHAR2,
278 x_score IN NUMBER ,
279 x_rb_full IN VARCHAR2,
280 x_scot_vac IN VARCHAR2,
281 x_sent_to_ucas IN VARCHAR2,
282 x_creation_date IN DATE ,
283 x_created_by IN NUMBER ,
284 x_last_update_date IN DATE ,
285 x_last_updated_by IN NUMBER ,
286 x_last_update_login IN NUMBER ,
287 x_ucas_system_id IN NUMBER ,
288 x_oss_attendance_type IN VARCHAR2,
289 x_oss_attendance_mode IN VARCHAR2,
290 x_joint_admission_ind IN VARCHAR2,
291 x_open_extra_ind IN VARCHAR2,
292 x_system_code IN VARCHAR2,
293 x_clearing_options IN VARCHAR2,
294 x_imported IN VARCHAR2,
295 x_keywrds_changed IN VARCHAR2
296 ) AS
297 /*
298 || Created By : rgopalan
299 || Created On : 01-OCT-2001
300 || Purpose : Initialises the columns, Checks Constraints, Calls the
301 || Trigger Handlers for the table, before any DML operation.
302 || Known limitations, enhancements or remarks :
303 || Change History :
304 || Who When What
305 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208
306 || (reverse chronological order - newest change first)
307 */
308 BEGIN
309
310 set_column_values (
311 p_action,
312 x_rowid,
313 x_ucas_program_code,
314 x_oss_program_code,
315 x_oss_program_version,
316 x_institute,
317 x_uvcourse_updater,
318 x_uvcrsevac_updater,
319 x_short_title,
320 x_long_title,
321 x_ucas_campus,
322 x_oss_location,
323 x_faculty,
324 x_total_no_of_seats,
325 x_min_entry_points,
326 x_max_entry_points,
327 x_current_validity,
328 x_deferred_validity,
329 x_term_1_start,
330 x_term_1_end,
331 x_term_2_start,
332 x_term_2_end,
333 x_term_3_start,
334 x_term_3_end,
335 x_term_4_start,
336 x_term_4_end,
337 x_cl_updated,
338 x_cl_date,
339 x_vacancy_status,
340 x_no_of_vacancy,
341 x_score,
342 x_rb_full,
343 x_scot_vac,
344 x_sent_to_ucas,
345 x_creation_date,
346 x_created_by,
347 x_last_update_date,
348 x_last_updated_by,
352 x_oss_attendance_mode,
349 x_last_update_login,
350 x_ucas_system_id,
351 x_oss_attendance_type,
353 x_joint_admission_ind,
354 x_open_extra_ind ,
355 x_system_code ,
356 x_clearing_options ,
357 x_imported ,
358 x_keywrds_changed
359 );
360
361 IF (p_action = 'INSERT') THEN
362 -- Call all the procedures related to Before Insert.
363 IF ( get_pk_for_validation(
364 new_references.ucas_program_code,
365 new_references.institute,
366 new_references.ucas_campus,
367 new_references.system_code )
368 ) THEN
369 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
370 igs_ge_msg_stack.add;
371 app_exception.raise_exception;
372 END IF;
373 check_parent_existance;
374 ELSIF (p_action = 'UPDATE') THEN
375 -- Call all the procedures related to Before Update.
376 check_parent_existance;
377 ELSIF (p_action = 'VALIDATE_INSERT') THEN
378 -- Call all the procedures related to Before Insert.
379 IF ( get_pk_for_validation (
380 new_references.ucas_program_code,
381 new_references.institute,
382 new_references.ucas_campus,
383 new_references.system_code
384 )
385 ) THEN
386 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
387 igs_ge_msg_stack.add;
388 app_exception.raise_exception;
389 END IF;
390 END IF;
391
392 END before_dml;
393
394
395 PROCEDURE insert_row (
396 x_rowid IN OUT NOCOPY VARCHAR2,
397 x_ucas_program_code IN VARCHAR2,
398 x_oss_program_code IN VARCHAR2,
399 x_oss_program_version IN NUMBER,
400 x_institute IN VARCHAR2,
401 x_uvcourse_updater IN VARCHAR2,
402 x_uvcrsevac_updater IN VARCHAR2,
403 x_short_title IN VARCHAR2,
404 x_long_title IN VARCHAR2,
405 x_ucas_campus IN VARCHAR2,
406 x_oss_location IN VARCHAR2,
407 x_faculty IN VARCHAR2,
408 x_total_no_of_seats IN NUMBER,
409 x_min_entry_points IN NUMBER,
410 x_max_entry_points IN NUMBER,
411 x_current_validity IN VARCHAR2,
412 x_deferred_validity IN VARCHAR2,
413 x_term_1_start IN DATE,
414 x_term_1_end IN DATE,
415 x_term_2_start IN DATE,
416 x_term_2_end IN DATE,
417 x_term_3_start IN DATE,
418 x_term_3_end IN DATE,
419 x_term_4_start IN DATE,
420 x_term_4_end IN DATE,
421 x_cl_updated IN VARCHAR2,
422 x_cl_date IN DATE,
423 x_vacancy_status IN VARCHAR2,
424 x_no_of_vacancy IN VARCHAR2,
425 x_score IN NUMBER,
426 x_rb_full IN VARCHAR2,
427 x_scot_vac IN VARCHAR2,
428 x_sent_to_ucas IN VARCHAR2,
429 x_ucas_system_id IN NUMBER,
430 x_mode IN VARCHAR2,
431 x_oss_attendance_type IN VARCHAR2,
432 x_oss_attendance_mode IN VARCHAR2,
433 x_joint_admission_ind IN VARCHAR2,
434 x_open_extra_ind IN VARCHAR2,
435 x_system_code IN VARCHAR2,
436 x_clearing_options IN VARCHAR2,
437 x_imported IN VARCHAR2,
438 x_keywrds_changed IN VARCHAR2
439 ) AS
440 /*
441 || Created By : rgopalan
442 || Created On : 01-OCT-2001
443 || Purpose : Handles the INSERT DML logic for the table.
444 || Known limitations, enhancements or remarks :
445 || Change History :
446 || Who When What
447 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208
448 || (reverse chronological order - newest change first)
449 */
450 CURSOR c IS
451 SELECT rowid
452 FROM igs_uc_crse_dets
453 WHERE ucas_program_code = x_ucas_program_code
454 AND institute = x_institute
455 AND ucas_campus = x_ucas_campus;
456
457 x_last_update_date DATE;
458 x_last_updated_by NUMBER;
459 x_last_update_login NUMBER;
460
461 BEGIN
462
463 x_last_update_date := SYSDATE;
464 IF (x_mode = 'I') THEN
465 x_last_updated_by := 1;
466 x_last_update_login := 0;
467 ELSIF (x_mode = 'R') THEN
468 x_last_updated_by := fnd_global.user_id;
469 IF (x_last_updated_by IS NULL) THEN
470 x_last_updated_by := -1;
471 END IF;
472 x_last_update_login := fnd_global.login_id;
473 IF (x_last_update_login IS NULL) THEN
477 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
474 x_last_update_login := -1;
475 END IF;
476 ELSE
478 igs_ge_msg_stack.add;
479 app_exception.raise_exception;
480 END IF;
481
482 before_dml(
483 p_action => 'INSERT',
484 x_rowid => x_rowid,
485 x_ucas_program_code => x_ucas_program_code,
486 x_oss_program_code => x_oss_program_code,
487 x_oss_program_version => x_oss_program_version,
488 x_institute => x_institute,
489 x_uvcourse_updater => x_uvcourse_updater,
490 x_uvcrsevac_updater => x_uvcrsevac_updater,
491 x_short_title => x_short_title,
492 x_long_title => x_long_title,
493 x_ucas_campus => x_ucas_campus,
494 x_oss_location => x_oss_location,
495 x_faculty => x_faculty,
496 x_total_no_of_seats => x_total_no_of_seats,
497 x_min_entry_points => x_min_entry_points,
498 x_max_entry_points => x_max_entry_points,
499 x_current_validity => x_current_validity,
500 x_deferred_validity => x_deferred_validity,
501 x_term_1_start => x_term_1_start,
502 x_term_1_end => x_term_1_end,
503 x_term_2_start => x_term_2_start,
504 x_term_2_end => x_term_2_end,
505 x_term_3_start => x_term_3_start,
506 x_term_3_end => x_term_3_end,
507 x_term_4_start => x_term_4_start,
508 x_term_4_end => x_term_4_end,
509 x_cl_updated => x_cl_updated,
510 x_cl_date => x_cl_date,
511 x_vacancy_status => x_vacancy_status,
512 x_no_of_vacancy => x_no_of_vacancy,
513 x_score => x_score,
514 x_rb_full => x_rb_full,
515 x_scot_vac => x_scot_vac,
516 x_sent_to_ucas => x_sent_to_ucas,
517 x_creation_date => x_last_update_date,
518 x_created_by => x_last_updated_by,
519 x_last_update_date => x_last_update_date,
520 x_last_updated_by => x_last_updated_by,
521 x_last_update_login => x_last_update_login,
522 x_ucas_system_id => x_ucas_system_id,
523 x_oss_attendance_type => x_oss_attendance_type,
524 x_oss_attendance_mode => x_oss_attendance_mode,
525 x_joint_admission_ind => x_joint_admission_ind,
526 x_open_extra_ind => x_open_extra_ind ,
527 x_system_code => x_system_code,
528 x_clearing_options => x_clearing_options,
529 x_imported => x_imported,
530 x_keywrds_changed => x_keywrds_changed
531 );
532
533 INSERT INTO igs_uc_crse_dets (
534 ucas_program_code,
535 oss_program_code,
536 oss_program_version,
537 institute,
538 uvcourse_updater,
539 uvcrsevac_updater,
540 short_title,
541 long_title,
542 ucas_campus,
543 oss_location,
544 faculty,
545 total_no_of_seats,
546 min_entry_points,
547 max_entry_points,
548 current_validity,
549 deferred_validity,
550 term_1_start,
551 term_1_end,
552 term_2_start,
553 term_2_end,
554 term_3_start,
555 term_3_end,
556 term_4_start,
557 term_4_end,
558 cl_updated,
559 cl_date,
560 vacancy_status,
561 no_of_vacancy,
562 score,
563 rb_full,
564 scot_vac,
565 sent_to_ucas,
566 creation_date,
567 created_by,
568 last_update_date,
569 last_updated_by,
570 last_update_login,
571 ucas_system_id,
572 oss_attendance_type,
573 oss_attendance_mode,
574 joint_admission_ind,
575 open_extra_ind ,
576 clearing_options,
577 imported,
578 system_code,
579 keywrds_changed
580 ) VALUES (
581 new_references.ucas_program_code,
582 new_references.oss_program_code,
583 new_references.oss_program_version,
584 new_references.institute,
585 new_references.uvcourse_updater,
586 new_references.uvcrsevac_updater,
587 new_references.short_title,
588 new_references.long_title,
589 new_references.ucas_campus,
590 new_references.oss_location,
591 new_references.faculty,
592 new_references.total_no_of_seats,
593 new_references.min_entry_points,
594 new_references.max_entry_points,
595 new_references.current_validity,
596 new_references.deferred_validity,
597 new_references.term_1_start,
601 new_references.term_3_start,
598 new_references.term_1_end,
599 new_references.term_2_start,
600 new_references.term_2_end,
602 new_references.term_3_end,
603 new_references.term_4_start,
604 new_references.term_4_end,
605 new_references.cl_updated,
606 new_references.cl_date,
607 new_references.vacancy_status,
608 new_references.no_of_vacancy,
609 new_references.score,
610 new_references.rb_full,
611 new_references.scot_vac,
612 new_references.sent_to_ucas,
613 x_last_update_date,
614 x_last_updated_by,
615 x_last_update_date,
616 x_last_updated_by,
617 x_last_update_login,
618 new_references.ucas_system_id,
619 new_references.oss_attendance_type,
620 new_references.oss_attendance_mode,
621 new_references.joint_admission_ind,
622 new_references.open_extra_ind,
623 new_references.clearing_options,
624 new_references.imported,
625 new_references.system_code ,
626 new_references.keywrds_changed
627 );
628
629 OPEN c;
630 FETCH c INTO x_rowid;
631 IF (c%NOTFOUND) THEN
632 CLOSE c;
633 RAISE NO_DATA_FOUND;
634 END IF;
635 CLOSE c;
636
637 END insert_row;
638
639
640 PROCEDURE lock_row (
641 x_rowid IN VARCHAR2,
642 x_ucas_program_code IN VARCHAR2,
643 x_oss_program_code IN VARCHAR2,
644 x_oss_program_version IN NUMBER,
645 x_institute IN VARCHAR2,
646 x_uvcourse_updater IN VARCHAR2,
647 x_uvcrsevac_updater IN VARCHAR2,
648 x_short_title IN VARCHAR2,
649 x_long_title IN VARCHAR2,
650 x_ucas_campus IN VARCHAR2,
651 x_oss_location IN VARCHAR2,
652 x_faculty IN VARCHAR2,
653 x_total_no_of_seats IN NUMBER,
654 x_min_entry_points IN NUMBER,
655 x_max_entry_points IN NUMBER,
656 x_current_validity IN VARCHAR2,
657 x_deferred_validity IN VARCHAR2,
658 x_term_1_start IN DATE,
659 x_term_1_end IN DATE,
660 x_term_2_start IN DATE,
661 x_term_2_end IN DATE,
662 x_term_3_start IN DATE,
663 x_term_3_end IN DATE,
664 x_term_4_start IN DATE,
665 x_term_4_end IN DATE,
666 x_cl_updated IN VARCHAR2,
667 x_cl_date IN DATE,
668 x_vacancy_status IN VARCHAR2,
669 x_no_of_vacancy IN VARCHAR2,
670 x_score IN NUMBER,
671 x_rb_full IN VARCHAR2,
672 x_scot_vac IN VARCHAR2,
673 x_sent_to_ucas IN VARCHAR2,
674 x_ucas_system_id IN NUMBER,
675 x_oss_attendance_type IN VARCHAR2,
676 x_oss_attendance_mode IN VARCHAR2,
677 x_joint_admission_ind IN VARCHAR2,
678 x_open_extra_ind IN VARCHAR2,
679 x_system_code IN VARCHAR2,
680 x_clearing_options IN VARCHAR2,
681 x_imported IN VARCHAR2,
682 x_keywrds_changed IN VARCHAR2
683 ) AS
684 /*
685 || Created By : rgopalan
686 || Created On : 01-OCT-2001
687 || Purpose : Handles the LOCK mechanism for the table.
688 || Known limitations, enhancements or remarks :
689 || Change History :
690 || Who When What
691 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208
692 || (reverse chronological order - newest change first)
693 */
694 CURSOR c1 IS
695 SELECT
696 oss_program_code,
697 oss_program_version,
698 uvcourse_updater,
699 uvcrsevac_updater,
700 short_title,
701 long_title,
702 oss_location,
703 faculty,
704 total_no_of_seats,
705 min_entry_points,
706 max_entry_points,
707 current_validity,
708 deferred_validity,
709 term_1_start,
710 term_1_end,
711 term_2_start,
712 term_2_end,
713 term_3_start,
714 term_3_end,
715 term_4_start,
716 term_4_end,
717 cl_updated,
718 cl_date,
719 vacancy_status,
720 no_of_vacancy,
721 score,
722 rb_full,
723 scot_vac,
724 sent_to_ucas,
725 ucas_system_id,
726 oss_attendance_type,
727 oss_attendance_mode,
728 joint_admission_ind,
729 open_extra_ind ,
730 system_code ,
731 clearing_options,
732 imported,
736 FOR UPDATE NOWAIT;
733 keywrds_changed
734 FROM igs_uc_crse_dets
735 WHERE rowid = x_rowid
737
738 tlinfo c1%ROWTYPE;
739
740 BEGIN
741
742 OPEN c1;
743 FETCH c1 INTO tlinfo;
744 IF (c1%notfound) THEN
745 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
746 igs_ge_msg_stack.add;
747 CLOSE c1;
748 app_exception.raise_exception;
749 RETURN;
750 END IF;
751 CLOSE c1;
752
753 IF (
754 ((tlinfo.oss_program_code = x_oss_program_code) OR ((tlinfo.oss_program_code IS NULL) AND (X_oss_program_code IS NULL)))
755 AND ((tlinfo.oss_program_version = x_oss_program_version) OR ((tlinfo.oss_program_version IS NULL) AND (X_oss_program_version IS NULL)))
756 AND (tlinfo.uvcourse_updater = x_uvcourse_updater)
757 AND (tlinfo.uvcrsevac_updater = x_uvcrsevac_updater)
758 AND ((tlinfo.short_title = x_short_title) OR ((tlinfo.short_title IS NULL) AND (X_short_title IS NULL)))
759 AND ((tlinfo.long_title = x_long_title) OR ((tlinfo.long_title IS NULL) AND (X_long_title IS NULL)))
760 AND ((tlinfo.oss_location = x_oss_location) OR ((tlinfo.oss_location IS NULL) AND (X_oss_location IS NULL)))
761 AND ((tlinfo.faculty = x_faculty) OR ((tlinfo.faculty IS NULL) AND (X_faculty IS NULL)))
762 AND ((tlinfo.total_no_of_seats = x_total_no_of_seats) OR ((tlinfo.total_no_of_seats IS NULL) AND (X_total_no_of_seats IS NULL)))
763 AND ((tlinfo.min_entry_points = x_min_entry_points) OR ((tlinfo.min_entry_points IS NULL) AND (X_min_entry_points IS NULL)))
764 AND ((tlinfo.max_entry_points = x_max_entry_points) OR ((tlinfo.max_entry_points IS NULL) AND (X_max_entry_points IS NULL)))
765 AND ((tlinfo.current_validity = x_current_validity) OR ((tlinfo.current_validity IS NULL) AND (X_current_validity IS NULL)))
766 AND ((tlinfo.deferred_validity = x_deferred_validity) OR ((tlinfo.deferred_validity IS NULL) AND (X_deferred_validity IS NULL)))
767 AND ((tlinfo.term_1_start = x_term_1_start) OR ((tlinfo.term_1_start IS NULL) AND (X_term_1_start IS NULL)))
768 AND ((tlinfo.term_1_end = x_term_1_end) OR ((tlinfo.term_1_end IS NULL) AND (X_term_1_end IS NULL)))
769 AND ((tlinfo.term_2_start = x_term_2_start) OR ((tlinfo.term_2_start IS NULL) AND (X_term_2_start IS NULL)))
770 AND ((tlinfo.term_2_end = x_term_2_end) OR ((tlinfo.term_2_end IS NULL) AND (X_term_2_end IS NULL)))
771 AND ((tlinfo.term_3_start = x_term_3_start) OR ((tlinfo.term_3_start IS NULL) AND (X_term_3_start IS NULL)))
772 AND ((tlinfo.term_3_end = x_term_3_end) OR ((tlinfo.term_3_end IS NULL) AND (X_term_3_end IS NULL)))
773 AND ((tlinfo.term_4_start = x_term_4_start) OR ((tlinfo.term_4_start IS NULL) AND (X_term_4_start IS NULL)))
774 AND ((tlinfo.term_4_end = x_term_4_end) OR ((tlinfo.term_4_end IS NULL) AND (X_term_4_end IS NULL)))
775 AND ((tlinfo.cl_updated = x_cl_updated) OR ((tlinfo.cl_updated IS NULL) AND (X_cl_updated IS NULL)))
776 AND ((tlinfo.cl_date = x_cl_date) OR ((tlinfo.cl_date IS NULL) AND (X_cl_date IS NULL)))
777 AND ((tlinfo.vacancy_status = x_vacancy_status) OR ((tlinfo.vacancy_status IS NULL) AND (X_vacancy_status IS NULL)))
778 AND ((tlinfo.no_of_vacancy = x_no_of_vacancy) OR ((tlinfo.no_of_vacancy IS NULL) AND (X_no_of_vacancy IS NULL)))
779 AND ((tlinfo.score = x_score) OR ((tlinfo.score IS NULL) AND (X_score IS NULL)))
780 AND ((tlinfo.rb_full = x_rb_full) OR ((tlinfo.rb_full IS NULL) AND (X_rb_full IS NULL)))
781 AND ((tlinfo.scot_vac = x_scot_vac) OR ((tlinfo.scot_vac IS NULL) AND (X_scot_vac IS NULL)))
782 AND (tlinfo.sent_to_ucas = x_sent_to_ucas)
783 AND ((tlinfo.ucas_system_id = x_ucas_system_id) OR ((tlinfo.ucas_system_id IS NULL) AND (X_ucas_system_id IS NULL)))
784 AND ((tlinfo.oss_attendance_type = x_oss_attendance_type) OR (( tlinfo.oss_attendance_type IS NULL) AND (x_oss_attendance_type IS NULL)))
785 AND ((tlinfo.oss_attendance_mode = x_oss_attendance_mode) OR (( tlinfo.oss_attendance_mode IS NULL) AND (x_oss_attendance_mode IS NULL)))
786 AND ((tlinfo.joint_admission_ind = x_joint_admission_ind) OR (( tlinfo.joint_admission_ind IS NULL) AND (x_joint_admission_ind IS NULL)))
787 AND ((tlinfo.open_extra_ind = x_open_extra_ind) OR (( tlinfo.open_extra_ind IS NULL) AND (x_open_extra_ind IS NULL)))
788 AND ((tlinfo.clearing_options = x_clearing_options) OR (( tlinfo.clearing_options IS NULL) AND (x_clearing_options IS NULL)))
789 AND ((tlinfo.imported = x_imported) OR (( tlinfo.imported IS NULL) AND (x_imported IS NULL)))
790 AND ((tlinfo.keywrds_changed = x_keywrds_changed) OR (( tlinfo.keywrds_changed IS NULL) AND (x_keywrds_changed IS NULL)))
791 ) THEN
792 NULL;
793 ELSE
794
795 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
796 igs_ge_msg_stack.add;
797 app_exception.raise_exception;
798 END IF;
799
800 RETURN;
801
802 END lock_row;
803
804
805 PROCEDURE update_row (
806 x_rowid IN VARCHAR2,
807 x_ucas_program_code IN VARCHAR2,
808 x_oss_program_code IN VARCHAR2,
809 x_oss_program_version IN NUMBER,
810 x_institute IN VARCHAR2,
811 x_uvcourse_updater IN VARCHAR2,
812 x_uvcrsevac_updater IN VARCHAR2,
813 x_short_title IN VARCHAR2,
814 x_long_title IN VARCHAR2,
818 x_total_no_of_seats IN NUMBER,
815 x_ucas_campus IN VARCHAR2,
816 x_oss_location IN VARCHAR2,
817 x_faculty IN VARCHAR2,
819 x_min_entry_points IN NUMBER,
820 x_max_entry_points IN NUMBER,
821 x_current_validity IN VARCHAR2,
822 x_deferred_validity IN VARCHAR2,
823 x_term_1_start IN DATE,
824 x_term_1_end IN DATE,
825 x_term_2_start IN DATE,
826 x_term_2_end IN DATE,
827 x_term_3_start IN DATE,
828 x_term_3_end IN DATE,
829 x_term_4_start IN DATE,
830 x_term_4_end IN DATE,
831 x_cl_updated IN VARCHAR2,
832 x_cl_date IN DATE,
833 x_vacancy_status IN VARCHAR2,
834 x_no_of_vacancy IN VARCHAR2,
835 x_score IN NUMBER,
836 x_rb_full IN VARCHAR2,
837 x_scot_vac IN VARCHAR2,
838 x_sent_to_ucas IN VARCHAR2,
839 x_ucas_system_id IN NUMBER,
840 x_mode IN VARCHAR2,
841 x_oss_attendance_type IN VARCHAR2,
842 x_oss_attendance_mode IN VARCHAR2,
843 x_joint_admission_ind IN VARCHAR2,
844 x_open_extra_ind IN VARCHAR2,
845 x_system_code IN VARCHAR2,
846 x_clearing_options IN VARCHAR2,
847 x_imported IN VARCHAR2,
848 x_keywrds_changed IN VARCHAR2
849 ) AS
850 /*
851 || Created By : rgopalan
852 || Created On : 01-OCT-2001
853 || Purpose : Handles the UPDATE DML logic for the table.
854 || Known limitations, enhancements or remarks :
855 || Change History :
856 || Who When What
857 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208
858 || (reverse chronological order - newest change first)
859 */
860 x_last_update_date DATE ;
861 x_last_updated_by NUMBER;
862 x_last_update_login NUMBER;
863
864 BEGIN
865
866 x_last_update_date := SYSDATE;
867 IF (X_MODE = 'I') THEN
868 x_last_updated_by := 1;
869 x_last_update_login := 0;
870 ELSIF (x_mode = 'R') THEN
871 x_last_updated_by := fnd_global.user_id;
872 IF x_last_updated_by IS NULL THEN
873 x_last_updated_by := -1;
874 END IF;
875 x_last_update_login := fnd_global.login_id;
876 IF (x_last_update_login IS NULL) THEN
877 x_last_update_login := -1;
878 END IF;
879 ELSE
880 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
881 igs_ge_msg_stack.add;
882 app_exception.raise_exception;
883 END IF;
884
885 before_dml(
886 p_action => 'UPDATE',
887 x_rowid => x_rowid,
888 x_ucas_program_code => x_ucas_program_code,
889 x_oss_program_code => x_oss_program_code,
890 x_oss_program_version => x_oss_program_version,
891 x_institute => x_institute,
892 x_uvcourse_updater => x_uvcourse_updater,
893 x_uvcrsevac_updater => x_uvcrsevac_updater,
894 x_short_title => x_short_title,
895 x_long_title => x_long_title,
896 x_ucas_campus => x_ucas_campus,
897 x_oss_location => x_oss_location,
898 x_faculty => x_faculty,
899 x_total_no_of_seats => x_total_no_of_seats,
900 x_min_entry_points => x_min_entry_points,
901 x_max_entry_points => x_max_entry_points,
902 x_current_validity => x_current_validity,
903 x_deferred_validity => x_deferred_validity,
904 x_term_1_start => x_term_1_start,
905 x_term_1_end => x_term_1_end,
906 x_term_2_start => x_term_2_start,
907 x_term_2_end => x_term_2_end,
908 x_term_3_start => x_term_3_start,
909 x_term_3_end => x_term_3_end,
910 x_term_4_start => x_term_4_start,
911 x_term_4_end => x_term_4_end,
912 x_cl_updated => x_cl_updated,
913 x_cl_date => x_cl_date,
914 x_vacancy_status => x_vacancy_status,
915 x_no_of_vacancy => x_no_of_vacancy,
916 x_score => x_score,
917 x_rb_full => x_rb_full,
918 x_scot_vac => x_scot_vac,
919 x_sent_to_ucas => x_sent_to_ucas,
920 x_creation_date => x_last_update_date,
924 x_last_update_login => x_last_update_login,
921 x_created_by => x_last_updated_by,
922 x_last_update_date => x_last_update_date,
923 x_last_updated_by => x_last_updated_by,
925 x_ucas_system_id => x_ucas_system_id,
926 x_oss_attendance_type => x_oss_attendance_type,
927 x_oss_attendance_mode => x_oss_attendance_mode,
928 x_joint_admission_ind => x_joint_admission_ind,
929 x_open_extra_ind => x_open_extra_ind,
930 x_system_code => x_system_code,
931 x_clearing_options => x_clearing_options,
932 x_imported => x_imported ,
933 x_keywrds_changed => x_keywrds_changed
934 );
935
936 UPDATE igs_uc_crse_dets
937 SET
938 oss_program_code = new_references.oss_program_code,
939 oss_program_version = new_references.oss_program_version,
940 uvcourse_updater = new_references.uvcourse_updater,
941 uvcrsevac_updater = new_references.uvcrsevac_updater,
942 short_title = new_references.short_title,
943 long_title = new_references.long_title,
944 oss_location = new_references.oss_location,
945 faculty = new_references.faculty,
946 total_no_of_seats = new_references.total_no_of_seats,
947 min_entry_points = new_references.min_entry_points,
948 max_entry_points = new_references.max_entry_points,
949 current_validity = new_references.current_validity,
950 deferred_validity = new_references.deferred_validity,
951 term_1_start = new_references.term_1_start,
952 term_1_end = new_references.term_1_end,
953 term_2_start = new_references.term_2_start,
954 term_2_end = new_references.term_2_end,
955 term_3_start = new_references.term_3_start,
956 term_3_end = new_references.term_3_end,
957 term_4_start = new_references.term_4_start,
958 term_4_end = new_references.term_4_end,
959 cl_updated = new_references.cl_updated,
960 cl_date = new_references.cl_date,
961 vacancy_status = new_references.vacancy_status,
962 no_of_vacancy = new_references.no_of_vacancy,
963 score = new_references.score,
964 rb_full = new_references.rb_full,
965 scot_vac = new_references.scot_vac,
966 sent_to_ucas = new_references.sent_to_ucas,
967 last_update_date = x_last_update_date,
968 last_updated_by = x_last_updated_by,
969 last_update_login = x_last_update_login,
970 ucas_system_id = new_references.ucas_system_id,
971 oss_attendance_type = new_references.oss_attendance_type,
972 oss_attendance_mode = new_references.oss_attendance_mode,
973 joint_admission_ind = new_references.joint_admission_ind,
974 open_extra_ind = new_references.open_extra_ind,
975 clearing_options = new_references.clearing_options,
976 imported = new_references.imported ,
977 keywrds_changed = new_references.keywrds_changed
978 WHERE rowid = x_rowid;
979
980 IF (SQL%NOTFOUND) THEN
981 RAISE NO_DATA_FOUND;
982 END IF;
983
984 END update_row;
985
986
987 PROCEDURE add_row (
988 x_rowid IN OUT NOCOPY VARCHAR2,
989 x_ucas_program_code IN VARCHAR2,
990 x_oss_program_code IN VARCHAR2,
991 x_oss_program_version IN NUMBER,
992 x_institute IN VARCHAR2,
993 x_uvcourse_updater IN VARCHAR2,
994 x_uvcrsevac_updater IN VARCHAR2,
995 x_short_title IN VARCHAR2,
996 x_long_title IN VARCHAR2,
997 x_ucas_campus IN VARCHAR2,
998 x_oss_location IN VARCHAR2,
999 x_faculty IN VARCHAR2,
1000 x_total_no_of_seats IN NUMBER,
1001 x_min_entry_points IN NUMBER,
1002 x_max_entry_points IN NUMBER,
1003 x_current_validity IN VARCHAR2,
1004 x_deferred_validity IN VARCHAR2,
1005 x_term_1_start IN DATE,
1006 x_term_1_end IN DATE,
1007 x_term_2_start IN DATE,
1008 x_term_2_end IN DATE,
1009 x_term_3_start IN DATE,
1010 x_term_3_end IN DATE,
1011 x_term_4_start IN DATE,
1012 x_term_4_end IN DATE,
1013 x_cl_updated IN VARCHAR2,
1017 x_score IN NUMBER,
1014 x_cl_date IN DATE,
1015 x_vacancy_status IN VARCHAR2,
1016 x_no_of_vacancy IN VARCHAR2,
1018 x_rb_full IN VARCHAR2,
1019 x_scot_vac IN VARCHAR2,
1020 x_sent_to_ucas IN VARCHAR2,
1021 x_ucas_system_id IN NUMBER,
1022 x_mode IN VARCHAR2,
1023 x_oss_attendance_type IN VARCHAR2,
1024 x_oss_attendance_mode IN VARCHAR2,
1025 x_joint_admission_ind IN VARCHAR2,
1026 x_open_extra_ind IN VARCHAR2,
1027 x_system_code IN VARCHAR2,
1028 x_clearing_options IN VARCHAR2,
1029 x_imported IN VARCHAR2,
1030 x_keywrds_changed IN VARCHAR2
1031 ) AS
1032 /*
1033 || Created By : rgopalan
1034 || Created On : 01-OCT-2001
1035 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1036 || Known limitations, enhancements or remarks :
1037 || Change History :
1038 || Who When What
1039 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208
1040 || (reverse chronological order - newest change first)
1041 */
1042 CURSOR c1 IS
1043 SELECT rowid
1044 FROM igs_uc_crse_dets
1045 WHERE ucas_program_code = x_ucas_program_code
1046 AND institute = x_institute
1047 AND ucas_campus = x_ucas_campus;
1048
1049 BEGIN
1050
1051 OPEN c1;
1052 FETCH c1 INTO x_rowid;
1053 IF (c1%NOTFOUND) THEN
1054 CLOSE c1;
1055
1056 insert_row (
1057 x_rowid,
1058 x_ucas_program_code,
1059 x_oss_program_code,
1060 x_oss_program_version,
1061 x_institute,
1062 x_uvcourse_updater,
1063 x_uvcrsevac_updater,
1064 x_short_title,
1065 x_long_title,
1066 x_ucas_campus,
1067 x_oss_location,
1068 x_faculty,
1069 x_total_no_of_seats,
1070 x_min_entry_points,
1071 x_max_entry_points,
1072 x_current_validity,
1073 x_deferred_validity,
1074 x_term_1_start,
1075 x_term_1_end,
1076 x_term_2_start,
1077 x_term_2_end,
1078 x_term_3_start,
1079 x_term_3_end,
1080 x_term_4_start,
1081 x_term_4_end,
1082 x_cl_updated,
1083 x_cl_date,
1084 x_vacancy_status,
1085 x_no_of_vacancy,
1086 x_score,
1087 x_rb_full,
1088 x_scot_vac,
1089 x_sent_to_ucas,
1090 x_ucas_system_id,
1091 x_mode,
1092 x_oss_attendance_type,
1093 x_oss_attendance_mode,
1094 x_joint_admission_ind,
1095 x_open_extra_ind ,
1096 x_system_code ,
1097 x_clearing_options ,
1098 x_imported ,
1099 x_keywrds_changed
1100 );
1101 RETURN;
1102 END IF;
1103 CLOSE c1;
1104
1105 update_row (
1106 x_rowid,
1107 x_ucas_program_code,
1108 x_oss_program_code,
1109 x_oss_program_version,
1110 x_institute,
1111 x_uvcourse_updater,
1112 x_uvcrsevac_updater,
1113 x_short_title,
1114 x_long_title,
1115 x_ucas_campus,
1116 x_oss_location,
1117 x_faculty,
1118 x_total_no_of_seats,
1119 x_min_entry_points,
1120 x_max_entry_points,
1121 x_current_validity,
1122 x_deferred_validity,
1123 x_term_1_start,
1124 x_term_1_end,
1125 x_term_2_start,
1126 x_term_2_end,
1127 x_term_3_start,
1128 x_term_3_end,
1129 x_term_4_start,
1130 x_term_4_end,
1131 x_cl_updated,
1132 x_cl_date,
1133 x_vacancy_status,
1134 x_no_of_vacancy,
1135 x_score,
1136 x_rb_full,
1137 x_scot_vac,
1138 x_sent_to_ucas,
1139 x_ucas_system_id,
1140 x_mode,
1141 x_oss_attendance_type,
1142 x_oss_attendance_mode,
1143 x_joint_admission_ind,
1144 x_open_extra_ind ,
1145 x_system_code ,
1146 x_clearing_options ,
1147 x_imported ,
1148 x_keywrds_changed
1149 );
1150
1151 END add_row;
1152
1153
1154 END igs_uc_crse_dets_pkg;