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_RESULTS_PKG

Source


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