eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_UC_APP_CLR_RND_PKG

Source


1 PACKAGE BODY igs_uc_app_clr_rnd_pkg AS
2 /* $Header: IGSXI05B.pls 115.12 2003/07/21 12:21:58 ayedubat noship $ */
3 
4 
5   l_rowid VARCHAR2(25);
6   old_references igs_uc_app_clr_rnd%ROWTYPE;
7   new_references igs_uc_app_clr_rnd%ROWTYPE;
8 
9   PROCEDURE set_column_values (
10     p_action                            IN     VARCHAR2,
11     x_rowid                             IN     VARCHAR2,
12     x_app_clear_round_id                IN     NUMBER  ,
13     x_clearing_app_id                   IN     NUMBER  ,
14     x_app_no                            IN     NUMBER  ,
15     x_enquiry_no                        IN     NUMBER  ,
16     x_round_no                          IN     NUMBER  ,
17     x_institution                       IN     VARCHAR2,
18     x_ucas_program_code                 IN     VARCHAR2,
19     x_ucas_campus                       IN     VARCHAR2,
20     x_oss_program_code                  IN     VARCHAR2,
21     x_oss_program_version               IN     NUMBER  ,
22     x_oss_location                      IN     VARCHAR2,
23     x_faculty                           IN     VARCHAR2,
24     x_accommodation_reqd                IN     VARCHAR2,
25     x_round_type                        IN     VARCHAR2,
26     x_result                            IN     VARCHAR2,
27     x_creation_date                     IN     DATE    ,
28     x_created_by                        IN     NUMBER  ,
29     x_last_update_date                  IN     DATE    ,
30     x_last_updated_by                   IN     NUMBER  ,
31     x_last_update_login                 IN     NUMBER  ,
32     x_oss_attendance_type               IN     VARCHAR2,
33     x_oss_attendance_mode               IN     VARCHAR2,
34     x_system_code                       IN     VARCHAR2
35   ) AS
36   /*
37   ||  Created By : [email protected]
38   ||  Created On : 21-FEB-2002
39   ||  Purpose : Initialises the Old and New references for the columns of the table.
40   ||  Known limitations, enhancements or remarks :
41   ||  Change History :
42   ||  Who             When            What
43   ||  smaddali  10-jun-03    obsoleting datetimestamp column for ucfd203 - multiple cycles build, bug#2669208  |
44   ||  (reverse chronological order - newest change first)
45   */
46 
47     CURSOR cur_old_ref_values IS
48       SELECT   *
49       FROM     IGS_UC_APP_CLR_RND
50       WHERE    rowid = x_rowid;
51 
52   BEGIN
53 
54     l_rowid := x_rowid;
55 
56     -- Code for setting the Old and New Reference Values.
57     -- Populate Old Values.
58     OPEN cur_old_ref_values;
59     FETCH cur_old_ref_values INTO old_references;
60     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
61       CLOSE cur_old_ref_values;
62       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
63       igs_ge_msg_stack.add;
64       app_exception.raise_exception;
65       RETURN;
66     END IF;
67     CLOSE cur_old_ref_values;
68 
69     -- Populate New Values.
70     new_references.app_clear_round_id                := x_app_clear_round_id;
71     new_references.clearing_app_id                   := x_clearing_app_id;
72     new_references.app_no                            := x_app_no;
73     new_references.enquiry_no                        := x_enquiry_no;
74     new_references.round_no                          := x_round_no;
75     new_references.institution                       := x_institution;
76     new_references.ucas_program_code                 := x_ucas_program_code;
77     new_references.ucas_campus                       := x_ucas_campus;
78     new_references.oss_program_code                  := x_oss_program_code;
79     new_references.oss_program_version               := x_oss_program_version;
80     new_references.oss_location                      := x_oss_location;
81     new_references.faculty                           := x_faculty;
82     new_references.accommodation_reqd                := x_accommodation_reqd;
83     new_references.round_type                        := x_round_type;
84     new_references.result                            := x_result;
85     new_references.system_code                       := x_system_code;
86 
87     IF (p_action = 'UPDATE') THEN
88       new_references.creation_date                   := old_references.creation_date;
89       new_references.created_by                      := old_references.created_by;
90     ELSE
91       new_references.creation_date                   := x_creation_date;
92       new_references.created_by                      := x_created_by;
93     END IF;
94 
95     new_references.last_update_date                  := x_last_update_date;
96     new_references.last_updated_by                   := x_last_updated_by;
97     new_references.last_update_login                 := x_last_update_login;
98 
99     new_references.oss_attendance_type               := x_oss_attendance_type;
100     new_references.oss_attendance_mode               :=	x_oss_attendance_mode;
101 
102   END set_column_values;
103 
104 
105   PROCEDURE check_parent_existance AS
106   /*
107   ||  Created By : [email protected]
108   ||  Created On : 21-FEB-2002
109   ||  Purpose : Checks for the existance of Parent records.
110   ||  Known limitations, enhancements or remarks :
111   ||  Change History :
112   ||  Who             When            What
113   ||  (reverse chronological order - newest change first)
114   */
115 
116     -- Cursor to fetch the current Institute Code
117     CURSOR crnt_inst_cur IS
118     SELECT DISTINCT current_inst_code
119     FROM   igs_uc_defaults
120     WHERE  current_inst_code IS NOT NULL;
121     l_crnt_institute igs_uc_defaults.current_inst_code%TYPE;
122 
123   BEGIN
124 
125     IF (
126         (
127          (old_references.ucas_program_code = new_references.ucas_program_code) AND
128          (old_references.institution = new_references.institution) AND
129          (old_references.ucas_campus = new_references.ucas_campus) AND
130          (old_references.system_code = new_references.system_code)
131         )
132         OR
133         (
134          (new_references.ucas_program_code IS NULL) OR
135          (new_references.institution IS NULL) OR
136          (new_references.ucas_campus IS NULL) OR
137          (new_references.system_code IS NULL)
138         )
139        ) THEN
140       NULL;
141 
142     ELSE
143 
144       l_crnt_institute := NULL;
145       OPEN crnt_inst_cur;
146       FETCH crnt_inst_cur INTO l_crnt_institute;
147       CLOSE crnt_inst_cur;
148 
149       IF  new_references.institution = l_crnt_institute AND
150              NOT igs_uc_crse_dets_pkg.get_pk_for_validation (
151                   new_references.ucas_program_code,
152                   new_references.institution,
153                   new_references.ucas_campus,
154                   new_references.system_code
155                 ) THEN
156         fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
157         igs_ge_msg_stack.add;
158         app_exception.raise_exception;
159 
160       END IF;
161 
162     END IF;
163 
164     IF (((old_references.clearing_app_id = new_references.clearing_app_id)) OR
165         ((new_references.clearing_app_id IS NULL))) THEN
166       NULL;
167     ELSIF NOT igs_uc_app_clearing_pkg.get_pk_for_validation (
168                 new_references.clearing_app_id
169               ) THEN
170       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
171       igs_ge_msg_stack.add;
172       app_exception.raise_exception;
173     END IF;
174 
175   END check_parent_existance;
176 
177 
178   FUNCTION get_pk_for_validation (
179     x_app_clear_round_id                IN     NUMBER
180   ) RETURN BOOLEAN AS
181   /*
182   ||  Created By : [email protected]
183   ||  Created On : 21-FEB-2002
184   ||  Purpose : Validates the Primary Key of the table.
185   ||  Known limitations, enhancements or remarks :
186   ||  Change History :
187   ||  Who             When            What
188   ||  (reverse chronological order - newest change first)
189   */
190     CURSOR cur_rowid IS
191       SELECT   rowid
192       FROM     igs_uc_app_clr_rnd
193       WHERE    app_clear_round_id = x_app_clear_round_id ;
194 
195     lv_rowid cur_rowid%RowType;
196 
197   BEGIN
198 
199     OPEN cur_rowid;
200     FETCH cur_rowid INTO lv_rowid;
201     IF (cur_rowid%FOUND) THEN
202       CLOSE cur_rowid;
203       RETURN(TRUE);
204     ELSE
205       CLOSE cur_rowid;
206       RETURN(FALSE);
207     END IF;
208 
209   END get_pk_for_validation;
210 
211 
212   PROCEDURE get_fk_igs_uc_crse_dets (
213     x_ucas_program_code                 IN     VARCHAR2,
214     x_institute                         IN     VARCHAR2,
215     x_ucas_campus                       IN     VARCHAR2,
216     x_system_code                       IN     VARCHAR2
217   ) AS
218   /*
219   ||  Created By : [email protected]
220   ||  Created On : 21-FEB-2002
221   ||  Purpose : Validates the Foreign Keys for the table.
222   ||  Known limitations, enhancements or remarks :
223   ||  Change History :
224   ||  Who             When            What
225   ||  (reverse chronological order - newest change first)
226   ||  Nishikant       17Jun2002       Bug#2415346. UCAPCR_UCCSDE_FKIGS_UC_CRSE_DETS
227   ||                                  message was replaced with IGS_UC_UCAPCR_UCCSDE_FK.
228   */
229     CURSOR cur_rowid IS
230       SELECT   rowid
231       FROM     igs_uc_app_clr_rnd
232       WHERE   ((institution = x_institute) AND
233                (ucas_campus = x_ucas_campus) AND
234                (ucas_program_code = x_ucas_program_code) AND
235                (system_code = x_system_code));
236 
237     lv_rowid cur_rowid%RowType;
238 
239   BEGIN
240 
241     OPEN cur_rowid;
242     FETCH cur_rowid INTO lv_rowid;
243     IF (cur_rowid%FOUND) THEN
244       CLOSE cur_rowid;
245       fnd_message.set_name ('IGS', 'IGS_UC_UCAPCR_UCCSDE_FK');
246       igs_ge_msg_stack.add;
247       app_exception.raise_exception;
248       RETURN;
249     END IF;
250     CLOSE cur_rowid;
251 
252   END get_fk_igs_uc_crse_dets;
253 
254 
255   PROCEDURE get_fk_igs_uc_app_clearing (
256     x_clearing_app_id                   IN     NUMBER
257   ) AS
258   /*
259   ||  Created By : [email protected]
260   ||  Created On : 21-FEB-2002
261   ||  Purpose : Validates the Foreign Keys for the table.
262   ||  Known limitations, enhancements or remarks :
263   ||  Change History :
264   ||  Who             When            What
265   ||  (reverse chronological order - newest change first)
266   ||  Nishikant       17Jun2002       Bug#2415346. UCAPCR_UCAPCL_FKIGS_UC_APP_CLEARING
267   ||                                  message was replaced with IGS_UC_UCAPCR_UCAPCL_FK.
268   */
269     CURSOR cur_rowid IS
270       SELECT   rowid
271       FROM     igs_uc_app_clr_rnd
272       WHERE   ((clearing_app_id = x_clearing_app_id));
273 
274     lv_rowid cur_rowid%RowType;
275 
276   BEGIN
277 
278     OPEN cur_rowid;
279     FETCH cur_rowid INTO lv_rowid;
280     IF (cur_rowid%FOUND) THEN
281       CLOSE cur_rowid;
282       fnd_message.set_name ('IGS', 'IGS_UC_UCAPCR_UCAPCL_FK');
283       igs_ge_msg_stack.add;
284       app_exception.raise_exception;
285       RETURN;
286     END IF;
287     CLOSE cur_rowid;
288 
289   END get_fk_igs_uc_app_clearing;
290 
291 
292   PROCEDURE before_dml (
293     p_action                            IN     VARCHAR2,
294     x_rowid                             IN     VARCHAR2,
295     x_app_clear_round_id                IN     NUMBER  ,
296     x_clearing_app_id                   IN     NUMBER  ,
297     x_app_no                            IN     NUMBER  ,
298     x_enquiry_no                        IN     NUMBER  ,
299     x_round_no                          IN     NUMBER  ,
300     x_institution                       IN     VARCHAR2,
301     x_ucas_program_code                 IN     VARCHAR2,
302     x_ucas_campus                       IN     VARCHAR2,
303     x_oss_program_code                  IN     VARCHAR2,
304     x_oss_program_version               IN     NUMBER  ,
305     x_oss_location                      IN     VARCHAR2,
306     x_faculty                           IN     VARCHAR2,
307     x_accommodation_reqd                IN     VARCHAR2,
308     x_round_type                        IN     VARCHAR2,
309     x_result                            IN     VARCHAR2,
310     x_creation_date                     IN     DATE    ,
311     x_created_by                        IN     NUMBER  ,
312     x_last_update_date                  IN     DATE    ,
313     x_last_updated_by                   IN     NUMBER  ,
314     x_last_update_login                 IN     NUMBER  ,
315     x_oss_attendance_type               IN     VARCHAR2,
316     x_oss_attendance_mode               IN     VARCHAR2,
317     x_system_code                       IN     VARCHAR2
318   ) AS
319   /*
320   ||  Created By : [email protected]
321   ||  Created On : 21-FEB-2002
322   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
323   ||            Trigger Handlers for the table, before any DML operation.
324   ||  Known limitations, enhancements or remarks :
325   ||  Change History :
326   ||  Who             When            What
327   ||  smaddali  10-jun-03    obsoleting datetimestamp column for ucfd203 - multiple cycles build, bug#2669208  |
328   ||  (reverse chronological order - newest change first)
329   */
330   BEGIN
331 
332     set_column_values (
333       p_action,
334       x_rowid,
335       x_app_clear_round_id,
336       x_clearing_app_id,
337       x_app_no,
338       x_enquiry_no,
339       x_round_no,
340       x_institution,
341       x_ucas_program_code,
342       x_ucas_campus,
343       x_oss_program_code,
344       x_oss_program_version,
345       x_oss_location,
346       x_faculty,
347       x_accommodation_reqd,
348       x_round_type,
349       x_result,
350       x_creation_date,
351       x_created_by,
352       x_last_update_date,
353       x_last_updated_by,
354       x_last_update_login,
355       x_oss_attendance_type,
356       x_oss_attendance_mode,
357       x_system_code
358     );
359 
360     IF (p_action = 'INSERT') THEN
361       -- Call all the procedures related to Before Insert.
362       IF ( get_pk_for_validation(
363              new_references.app_clear_round_id
364            )
365          ) THEN
366         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
367         igs_ge_msg_stack.add;
368         app_exception.raise_exception;
369       END IF;
370       check_parent_existance;
371     ELSIF (p_action = 'UPDATE') THEN
372       -- Call all the procedures related to Before Update.
373       check_parent_existance;
374     ELSIF (p_action = 'VALIDATE_INSERT') THEN
375       -- Call all the procedures related to Before Insert.
376       IF ( get_pk_for_validation (
377              new_references.app_clear_round_id
378            )
379          ) THEN
380         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
381         igs_ge_msg_stack.add;
382         app_exception.raise_exception;
383       END IF;
384     END IF;
385 
386   END before_dml;
387 
388 
389   PROCEDURE insert_row (
390     x_rowid                             IN OUT NOCOPY VARCHAR2,
391     x_app_clear_round_id                IN OUT NOCOPY NUMBER,
392     x_clearing_app_id                   IN     NUMBER,
393     x_app_no                            IN     NUMBER,
394     x_enquiry_no                        IN     NUMBER,
395     x_round_no                          IN     NUMBER,
396     x_institution                       IN     VARCHAR2,
397     x_ucas_program_code                 IN     VARCHAR2,
398     x_ucas_campus                       IN     VARCHAR2,
399     x_oss_program_code                  IN     VARCHAR2,
400     x_oss_program_version               IN     NUMBER,
401     x_oss_location                      IN     VARCHAR2,
402     x_faculty                           IN     VARCHAR2,
403     x_accommodation_reqd                IN     VARCHAR2,
404     x_round_type                        IN     VARCHAR2,
405     x_result                            IN     VARCHAR2,
406     x_mode                              IN     VARCHAR2 ,
407     x_oss_attendance_type               IN     VARCHAR2,
408     x_oss_attendance_mode               IN     VARCHAR2,
409     x_system_code                       IN     VARCHAR2
410   ) AS
411   /*
412   ||  Created By : [email protected]
413   ||  Created On : 21-FEB-2002
414   ||  Purpose : Handles the INSERT DML logic for the table.
415   ||  Known limitations, enhancements or remarks :
416   ||  Change History :
417   ||  Who             When            What
418   ||  smaddali  10-jun-03    obsoleting datetimestamp column for ucfd203 - multiple cycles build, bug#2669208  |
419   ||  (reverse chronological order - newest change first)
420   */
421     CURSOR c IS
422       SELECT   rowid
423       FROM     igs_uc_app_clr_rnd
424       WHERE    app_clear_round_id                = x_app_clear_round_id;
425 
426     x_last_update_date           DATE;
427     x_last_updated_by            NUMBER;
428     x_last_update_login          NUMBER;
429 
430   BEGIN
431 
432     x_last_update_date := SYSDATE;
433     IF (x_mode = 'I') THEN
434       x_last_updated_by := 1;
435       x_last_update_login := 0;
436     ELSIF (x_mode = 'R') THEN
437       x_last_updated_by := fnd_global.user_id;
438       IF (x_last_updated_by IS NULL) THEN
439         x_last_updated_by := -1;
440       END IF;
441       x_last_update_login := fnd_global.login_id;
442       IF (x_last_update_login IS NULL) THEN
443         x_last_update_login := -1;
444       END IF;
445     ELSE
446       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
447       igs_ge_msg_stack.add;
448       app_exception.raise_exception;
449     END IF;
450 
451     SELECT    igs_uc_app_clr_rnd_s.NEXTVAL
452     INTO      x_app_clear_round_id
453     FROM      dual;
454 
455     before_dml(
456       p_action                            => 'INSERT',
457       x_rowid                             => x_rowid,
458       x_app_clear_round_id                => x_app_clear_round_id,
459       x_clearing_app_id                   => x_clearing_app_id,
460       x_app_no                            => x_app_no,
461       x_enquiry_no                        => x_enquiry_no,
462       x_round_no                          => x_round_no,
463       x_institution                       => x_institution,
464       x_ucas_program_code                 => x_ucas_program_code,
465       x_ucas_campus                       => x_ucas_campus,
466       x_oss_program_code                  => x_oss_program_code,
467       x_oss_program_version               => x_oss_program_version,
468       x_oss_location                      => x_oss_location,
469       x_faculty                           => x_faculty,
470       x_accommodation_reqd                => x_accommodation_reqd,
471       x_round_type                        => x_round_type,
472       x_result                            => x_result,
473       x_creation_date                     => x_last_update_date,
474       x_created_by                        => x_last_updated_by,
475       x_last_update_date                  => x_last_update_date,
476       x_last_updated_by                   => x_last_updated_by,
477       x_last_update_login                 => x_last_update_login,
478       x_oss_attendance_type               => x_oss_attendance_type,
479       x_oss_attendance_mode               => x_oss_attendance_mode,
480       x_system_code                       =>x_system_code
481     );
482 
483     INSERT INTO igs_uc_app_clr_rnd (
484       app_clear_round_id,
485       clearing_app_id,
486       app_no,
487       enquiry_no,
488       round_no,
489       institution,
490       ucas_program_code,
491       ucas_campus,
492       oss_program_code,
493       oss_program_version,
494       oss_location,
495       faculty,
496       accommodation_reqd,
497       round_type,
498       result,
499       creation_date,
500       created_by,
501       last_update_date,
502       last_updated_by,
503       last_update_login,
504       oss_attendance_type,
505       oss_attendance_mode,
506       system_code
507     ) VALUES (
508       new_references.app_clear_round_id,
509       new_references.clearing_app_id,
510       new_references.app_no,
511       new_references.enquiry_no,
512       new_references.round_no,
513       new_references.institution,
514       new_references.ucas_program_code,
515       new_references.ucas_campus,
516       new_references.oss_program_code,
517       new_references.oss_program_version,
518       new_references.oss_location,
519       new_references.faculty,
520       new_references.accommodation_reqd,
521       new_references.round_type,
522       new_references.result,
523       x_last_update_date,
524       x_last_updated_by,
525       x_last_update_date,
526       x_last_updated_by,
527       x_last_update_login,
528       new_references.oss_attendance_type,
529       new_references.oss_attendance_mode,
530       new_references.system_code
531     );
532 
533     OPEN c;
534     FETCH c INTO x_rowid;
535     IF (c%NOTFOUND) THEN
536       CLOSE c;
537       RAISE NO_DATA_FOUND;
538     END IF;
539     CLOSE c;
540 
541   END insert_row;
542 
543 
544   PROCEDURE lock_row (
545     x_rowid                             IN     VARCHAR2,
546     x_app_clear_round_id                IN     NUMBER,
547     x_clearing_app_id                   IN     NUMBER,
548     x_app_no                            IN     NUMBER,
549     x_enquiry_no                        IN     NUMBER,
550     x_round_no                          IN     NUMBER,
551     x_institution                       IN     VARCHAR2,
552     x_ucas_program_code                 IN     VARCHAR2,
553     x_ucas_campus                       IN     VARCHAR2,
554     x_oss_program_code                  IN     VARCHAR2,
555     x_oss_program_version               IN     NUMBER,
556     x_oss_location                      IN     VARCHAR2,
557     x_faculty                           IN     VARCHAR2,
558     x_accommodation_reqd                IN     VARCHAR2,
559     x_round_type                        IN     VARCHAR2,
560     x_result                            IN     VARCHAR2,
561     x_oss_attendance_type               IN     VARCHAR2,
562     x_oss_attendance_mode               IN     VARCHAR2,
563     x_system_code                       IN     VARCHAR2
564   ) AS
565   /*
566   ||  Created By : [email protected]
567   ||  Created On : 21-FEB-2002
568   ||  Purpose : Handles the LOCK mechanism for the table.
569   ||  Known limitations, enhancements or remarks :
570   ||  Change History :
571   ||  Who             When            What
572   ||  smaddali  10-jun-03    obsoleting datetimestamp column for ucfd203 - multiple cycles build, bug#2669208  |
573   ||  (reverse chronological order - newest change first)
574   */
575     CURSOR c1 IS
576       SELECT
577         clearing_app_id,
578         app_no,
579         enquiry_no,
580         round_no,
581         institution,
582         ucas_program_code,
583         ucas_campus,
584         oss_program_code,
585         oss_program_version,
586         oss_location,
587         faculty,
588         accommodation_reqd,
589         round_type,
590         result,
591 	oss_attendance_type,
592 	oss_attendance_mode,
593 	system_code
594       FROM  igs_uc_app_clr_rnd
595       WHERE rowid = x_rowid
596       FOR UPDATE NOWAIT;
597 
598     tlinfo c1%ROWTYPE;
599 
600   BEGIN
601 
602     OPEN c1;
603     FETCH c1 INTO tlinfo;
604     IF (c1%notfound) THEN
605       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
606       igs_ge_msg_stack.add;
607       CLOSE c1;
608       app_exception.raise_exception;
609       RETURN;
610     END IF;
611     CLOSE c1;
612 
613     IF (
614         (tlinfo.clearing_app_id = x_clearing_app_id)
615         AND ((tlinfo.app_no = x_app_no) OR ((tlinfo.app_no IS NULL) AND (X_app_no IS NULL)))
616         AND ((tlinfo.enquiry_no = x_enquiry_no) OR ((tlinfo.enquiry_no IS NULL) AND (X_enquiry_no IS NULL)))
617         AND ((tlinfo.round_no = x_round_no) OR ((tlinfo.round_no IS NULL) AND (X_round_no IS NULL)))
618         AND ((tlinfo.system_code = x_system_code) )
619         AND ((tlinfo.institution = x_institution) OR ((tlinfo.institution IS NULL) AND (X_institution IS NULL)))
620         AND ((tlinfo.ucas_program_code = x_ucas_program_code) OR ((tlinfo.ucas_program_code IS NULL) AND (X_ucas_program_code IS NULL)))
621         AND ((tlinfo.ucas_campus = x_ucas_campus) OR ((tlinfo.ucas_campus IS NULL) AND (X_ucas_campus IS NULL)))
622         AND ((tlinfo.oss_program_code = x_oss_program_code) OR ((tlinfo.oss_program_code IS NULL) AND (X_oss_program_code IS NULL)))
623         AND ((tlinfo.oss_program_version = x_oss_program_version) OR ((tlinfo.oss_program_version IS NULL) AND (X_oss_program_version IS NULL)))
624         AND ((tlinfo.oss_location = x_oss_location) OR ((tlinfo.oss_location IS NULL) AND (X_oss_location IS NULL)))
625         AND ((tlinfo.faculty = x_faculty) OR ((tlinfo.faculty IS NULL) AND (X_faculty IS NULL)))
626         AND (tlinfo.accommodation_reqd = x_accommodation_reqd)
627         AND ((tlinfo.round_type = x_round_type) OR ((tlinfo.round_type IS NULL) AND (X_round_type IS NULL)))
628         AND ((tlinfo.result = x_result) OR ((tlinfo.result IS NULL) AND (X_result IS NULL)))
629         AND ((tlinfo.oss_attendance_type = x_oss_attendance_type) OR ((tlinfo.oss_attendance_type IS NULL) AND (x_oss_attendance_type IS NULL)))
630         AND ((tlinfo.oss_attendance_mode = x_oss_attendance_mode) OR ((tlinfo.oss_attendance_mode IS NULL) AND (x_oss_attendance_mode IS NULL)))
631        ) THEN
632       NULL;
633     ELSE
634       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
635       igs_ge_msg_stack.add;
636       app_exception.raise_exception;
637     END IF;
638 
639     RETURN;
640 
641   END lock_row;
642 
643 
644   PROCEDURE update_row (
645     x_rowid                             IN     VARCHAR2,
646     x_app_clear_round_id                IN     NUMBER,
647     x_clearing_app_id                   IN     NUMBER,
648     x_app_no                            IN     NUMBER,
649     x_enquiry_no                        IN     NUMBER,
650     x_round_no                          IN     NUMBER,
651     x_institution                       IN     VARCHAR2,
652     x_ucas_program_code                 IN     VARCHAR2,
653     x_ucas_campus                       IN     VARCHAR2,
654     x_oss_program_code                  IN     VARCHAR2,
655     x_oss_program_version               IN     NUMBER,
656     x_oss_location                      IN     VARCHAR2,
657     x_faculty                           IN     VARCHAR2,
658     x_accommodation_reqd                IN     VARCHAR2,
659     x_round_type                        IN     VARCHAR2,
660     x_result                            IN     VARCHAR2,
661     x_mode                              IN     VARCHAR2 ,
662     x_oss_attendance_type               IN     VARCHAR2,
663     x_oss_attendance_mode               IN     VARCHAR2,
664     x_system_code                       IN     VARCHAR2
665   ) AS
666   /*
667   ||  Created By : [email protected]
668   ||  Created On : 21-FEB-2002
669   ||  Purpose : Handles the UPDATE DML logic for the table.
670   ||  Known limitations, enhancements or remarks :
671   ||  Change History :
672   ||  Who             When            What
673   ||  smaddali  10-jun-03    obsoleting datetimestamp column for ucfd203 - multiple cycles build, bug#2669208  |
674   ||  (reverse chronological order - newest change first)
675   */
676     x_last_update_date           DATE ;
677     x_last_updated_by            NUMBER;
678     x_last_update_login          NUMBER;
679 
680   BEGIN
681 
682     x_last_update_date := SYSDATE;
683     IF (X_MODE = 'I') THEN
684       x_last_updated_by := 1;
685       x_last_update_login := 0;
686     ELSIF (x_mode = 'R') THEN
687       x_last_updated_by := fnd_global.user_id;
688       IF x_last_updated_by IS NULL THEN
689         x_last_updated_by := -1;
690       END IF;
691       x_last_update_login := fnd_global.login_id;
692       IF (x_last_update_login IS NULL) THEN
693         x_last_update_login := -1;
694       END IF;
695     ELSE
696       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
697       igs_ge_msg_stack.add;
698       app_exception.raise_exception;
699     END IF;
700 
701     before_dml(
702       p_action                            => 'UPDATE',
703       x_rowid                             => x_rowid,
704       x_app_clear_round_id                => x_app_clear_round_id,
705       x_clearing_app_id                   => x_clearing_app_id,
706       x_app_no                            => x_app_no,
707       x_enquiry_no                        => x_enquiry_no,
708       x_round_no                          => x_round_no,
709       x_institution                       => x_institution,
710       x_ucas_program_code                 => x_ucas_program_code,
711       x_ucas_campus                       => x_ucas_campus,
712       x_oss_program_code                  => x_oss_program_code,
713       x_oss_program_version               => x_oss_program_version,
714       x_oss_location                      => x_oss_location,
715       x_faculty                           => x_faculty,
716       x_accommodation_reqd                => x_accommodation_reqd,
717       x_round_type                        => x_round_type,
718       x_result                            => x_result,
719       x_creation_date                     => x_last_update_date,
720       x_created_by                        => x_last_updated_by,
721       x_last_update_date                  => x_last_update_date,
722       x_last_updated_by                   => x_last_updated_by,
723       x_last_update_login                 => x_last_update_login,
724       x_oss_attendance_type               => x_oss_attendance_type,
725       x_oss_attendance_mode               => x_oss_attendance_mode,
726       x_system_code                       => x_system_code
727     );
728 
729     UPDATE igs_uc_app_clr_rnd
730       SET
731         clearing_app_id                   = new_references.clearing_app_id,
732         app_no                            = new_references.app_no,
733         enquiry_no                        = new_references.enquiry_no,
734         round_no                          = new_references.round_no,
735         institution                       = new_references.institution,
736         ucas_program_code                 = new_references.ucas_program_code,
737         ucas_campus                       = new_references.ucas_campus,
738         oss_program_code                  = new_references.oss_program_code,
739         oss_program_version               = new_references.oss_program_version,
740         oss_location                      = new_references.oss_location,
741         faculty                           = new_references.faculty,
742         accommodation_reqd                = new_references.accommodation_reqd,
743         round_type                        = new_references.round_type,
744         result                            = new_references.result,
745         last_update_date                  = x_last_update_date,
746         last_updated_by                   = x_last_updated_by,
747         last_update_login                 = x_last_update_login,
748 	oss_attendance_type               = new_references.oss_attendance_type,
749 	oss_attendance_mode               = new_references.oss_attendance_mode,
750 	system_code			  = new_references.system_code
751       WHERE rowid = x_rowid;
752 
753     IF (SQL%NOTFOUND) THEN
754       RAISE NO_DATA_FOUND;
755     END IF;
756 
757   END update_row;
758 
759 
760   PROCEDURE add_row (
761     x_rowid                             IN OUT NOCOPY VARCHAR2,
762     x_app_clear_round_id                IN OUT NOCOPY NUMBER,
763     x_clearing_app_id                   IN     NUMBER,
764     x_app_no                            IN     NUMBER,
765     x_enquiry_no                        IN     NUMBER,
766     x_round_no                          IN     NUMBER,
767     x_institution                       IN     VARCHAR2,
768     x_ucas_program_code                 IN     VARCHAR2,
769     x_ucas_campus                       IN     VARCHAR2,
770     x_oss_program_code                  IN     VARCHAR2,
771     x_oss_program_version               IN     NUMBER,
772     x_oss_location                      IN     VARCHAR2,
773     x_faculty                           IN     VARCHAR2,
774     x_accommodation_reqd                IN     VARCHAR2,
775     x_round_type                        IN     VARCHAR2,
776     x_result                            IN     VARCHAR2,
777     x_mode                              IN     VARCHAR2 ,
778     x_oss_attendance_type               IN     VARCHAR2,
779     x_oss_attendance_mode               IN     VARCHAR2,
780     x_system_code                       IN     VARCHAR2
781   ) AS
782   /*
783   ||  Created By : [email protected]
784   ||  Created On : 21-FEB-2002
785   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
786   ||  Known limitations, enhancements or remarks :
787   ||  Change History :
788   ||  Who             When            What
789   ||  smaddali  10-jun-03    obsoleting datetimestamp column for ucfd203 - multiple cycles build, bug#2669208  |
790   ||  (reverse chronological order - newest change first)
791   */
792     CURSOR c1 IS
793       SELECT   rowid
794       FROM     igs_uc_app_clr_rnd
795       WHERE    app_clear_round_id                = x_app_clear_round_id;
796 
797   BEGIN
798 
799     OPEN c1;
800     FETCH c1 INTO x_rowid;
801     IF (c1%NOTFOUND) THEN
802       CLOSE c1;
803 
804       insert_row (
805         x_rowid,
806         x_app_clear_round_id,
807         x_clearing_app_id,
808         x_app_no,
809         x_enquiry_no,
810         x_round_no,
811         x_institution,
812         x_ucas_program_code,
813         x_ucas_campus,
814         x_oss_program_code,
815         x_oss_program_version,
816         x_oss_location,
817         x_faculty,
818         x_accommodation_reqd,
819         x_round_type,
820         x_result,
821         x_mode,
822 	x_oss_attendance_type,
823 	x_oss_attendance_mode,
824 	x_system_code
825       );
826       RETURN;
827     END IF;
828     CLOSE c1;
829 
830     update_row (
831       x_rowid,
832       x_app_clear_round_id,
833       x_clearing_app_id,
834       x_app_no,
835       x_enquiry_no,
836       x_round_no,
837       x_institution,
838       x_ucas_program_code,
839       x_ucas_campus,
840       x_oss_program_code,
841       x_oss_program_version,
842       x_oss_location,
843       x_faculty,
844       x_accommodation_reqd,
845       x_round_type,
846       x_result,
847       x_mode,
848       x_oss_attendance_type,
849       x_oss_attendance_mode,
850       x_system_code
851     );
852 
853   END add_row;
854 
855 
856   PROCEDURE delete_row (
857     x_rowid IN VARCHAR2
858   ) AS
859   /*
860   ||  Created By : [email protected]
861   ||  Created On : 21-FEB-2002
862   ||  Purpose : Handles the DELETE DML logic for the table.
863   ||  Known limitations, enhancements or remarks :
864   ||  Change History :
865   ||  Who             When            What
866   ||  (reverse chronological order - newest change first)
867   */
868   BEGIN
869 
870     before_dml (
871       p_action => 'DELETE',
872       x_rowid => x_rowid
873     );
874 
875     DELETE FROM igs_uc_app_clr_rnd
876     WHERE rowid = x_rowid;
877 
878     IF (SQL%NOTFOUND) THEN
879       RAISE NO_DATA_FOUND;
880     END IF;
881 
882   END delete_row;
883 
884 
885 END igs_uc_app_clr_rnd_pkg;