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_FORM_QUALS_PKG

Source


1 PACKAGE BODY igs_uc_form_quals_pkg AS
2 /* $Header: IGSXI51B.pls 120.1 2005/09/27 19:34:42 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_uc_form_quals%ROWTYPE;
6   new_references igs_uc_form_quals%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_app_no                            IN     NUMBER,
12     x_qual_id                           IN     NUMBER,
13     x_qual_type                         IN     VARCHAR2,
14     x_award_body                        IN     VARCHAR2,
15     x_title                             IN     VARCHAR2,
16     x_grade                             IN     VARCHAR2,
17     x_qual_date                         IN     DATE,
18     x_creation_date                     IN     DATE,
19     x_created_by                        IN     NUMBER,
20     x_last_update_date                  IN     DATE,
21     x_last_updated_by                   IN     NUMBER,
22     x_last_update_login                 IN     NUMBER
23   ) AS
24   /*
25   ||  Created By : [email protected]
26   ||  Created On : 14-JUL-2003
27   ||  Purpose : Initialises the Old and New references for the columns of the table.
28   ||  Known limitations, enhancements or remarks :
29   ||  Change History :
30   ||  Who             When            What
31   ||  (reverse chronological order - newest change first)
32   */
33 
34     CURSOR cur_old_ref_values IS
35       SELECT   *
36       FROM     igs_uc_form_quals
37       WHERE    rowid = x_rowid;
38 
39   BEGIN
40 
41     l_rowid := x_rowid;
42 
43     -- Code for setting the Old and New Reference Values.
44     -- Populate Old Values.
45     OPEN cur_old_ref_values;
46     FETCH cur_old_ref_values INTO old_references;
47     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
48       CLOSE cur_old_ref_values;
49       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
50       igs_ge_msg_stack.add;
51       app_exception.raise_exception;
52       RETURN;
53     END IF;
54     CLOSE cur_old_ref_values;
55 
56     -- Populate New Values.
57     new_references.app_no                            := x_app_no;
58     new_references.qual_id                           := x_qual_id;
59     new_references.qual_type                         := x_qual_type;
60     new_references.award_body                        := x_award_body;
61     new_references.title                             := x_title;
62     new_references.grade                             := x_grade;
63     new_references.qual_date                         := x_qual_date;
64 
65     IF (p_action = 'UPDATE') THEN
66       new_references.creation_date                   := old_references.creation_date;
67       new_references.created_by                      := old_references.created_by;
68     ELSE
69       new_references.creation_date                   := x_creation_date;
70       new_references.created_by                      := x_created_by;
71     END IF;
72 
73     new_references.last_update_date                  := x_last_update_date;
74     new_references.last_updated_by                   := x_last_updated_by;
75     new_references.last_update_login                 := x_last_update_login;
76 
77   END set_column_values;
78 
79 
80   PROCEDURE check_uniqueness AS
81   /*
82   ||  Created By : [email protected]
83   ||  Created On : 14-JUL-2003
84   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
85   ||  Known limitations, enhancements or remarks :
86   ||  Change History :
87   ||  Who             When            What
88   ||  (reverse chronological order - newest change first)
89   */
90   BEGIN
91 
92     IF ( get_uk_for_validation (
93            new_references.app_no,
94            new_references.qual_type,
95            new_references.title
96          )
97        ) THEN
98       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
99       igs_ge_msg_stack.add;
100       app_exception.raise_exception;
101     END IF;
102 
103   END check_uniqueness;
104 
105 
106   PROCEDURE check_parent_existance AS
107   /*
108   ||  Created By : [email protected]
109   ||  Created On : 14-JUL-2003
110   ||  Purpose : Checks for the existance of Parent records.
111   ||  Known limitations, enhancements or remarks :
112   ||  Change History :
113   ||  Who             When            What
114   ||  (reverse chronological order - newest change first)
115   */
116   BEGIN
117 
118     IF (((old_references.app_no = new_references.app_no)) OR
119         ((new_references.app_no IS NULL))) THEN
120       NULL;
121     ELSIF NOT igs_uc_applicants_pkg.get_uk_For_validation (
122                 new_references.app_no
123               ) THEN
124       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
125       igs_ge_msg_stack.add;
126       app_exception.raise_exception;
127     END IF;
128 
129   END check_parent_existance;
130 
131 
132   FUNCTION get_uk_for_validation (
133     x_app_no                            IN     NUMBER,
134     x_qual_type                         IN     VARCHAR2,
135     x_title                             IN     VARCHAR2
136   ) RETURN BOOLEAN AS
137   /*
138   ||  Created By : [email protected]
139   ||  Created On : 14-JUL-2003
140   ||  Purpose : Validates the Unique Keys 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_form_quals
149       WHERE    app_no = x_app_no
150       AND      qual_type = x_qual_type
151       AND      title = x_title
152       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
153 
154     lv_rowid cur_rowid%RowType;
155 
156   BEGIN
157 
158     OPEN cur_rowid;
159     FETCH cur_rowid INTO lv_rowid;
160     IF (cur_rowid%FOUND) THEN
161       CLOSE cur_rowid;
162         RETURN (true);
163         ELSE
164        CLOSE cur_rowid;
165       RETURN(FALSE);
166     END IF;
167 
168   END get_uk_for_validation ;
169 
170 
171   PROCEDURE get_ufk_igs_uc_applicants (
172     x_app_no                            IN     NUMBER
173   ) AS
174   /*
175   ||  Created By : [email protected]
176   ||  Created On : 14-JUL-2003
177   ||  Purpose : Validates the Foreign Keys for the table.
178   ||  Known limitations, enhancements or remarks :
179   ||  Change History :
180   ||  Who             When            What
181   ||  (reverse chronological order - newest change first)
182   */
183     CURSOR cur_rowid IS
184       SELECT   rowid
185       FROM     igs_uc_form_quals
186       WHERE   ((app_no = x_app_no));
187 
188     lv_rowid cur_rowid%RowType;
189 
190   BEGIN
191 
192     OPEN cur_rowid;
193     FETCH cur_rowid INTO lv_rowid;
194     IF (cur_rowid%FOUND) THEN
195       CLOSE cur_rowid;
196       fnd_message.set_name ('IGS', 'IGS_UC_UAFRQA_UCAP_FK');
197       igs_ge_msg_stack.add;
198       app_exception.raise_exception;
199       RETURN;
200     END IF;
201     CLOSE cur_rowid;
202 
203   END get_ufk_igs_uc_applicants;
204 
205 
206   PROCEDURE before_dml (
207     p_action                            IN     VARCHAR2,
208     x_rowid                             IN     VARCHAR2,
209     x_app_no                            IN     NUMBER,
210     x_qual_id                           IN     NUMBER,
211     x_qual_type                         IN     VARCHAR2,
212     x_award_body                        IN     VARCHAR2,
213     x_title                             IN     VARCHAR2,
214     x_grade                             IN     VARCHAR2,
215     x_qual_date                         IN     DATE,
216     x_creation_date                     IN     DATE,
217     x_created_by                        IN     NUMBER,
218     x_last_update_date                  IN     DATE,
219     x_last_updated_by                   IN     NUMBER,
220     x_last_update_login                 IN     NUMBER
221   ) AS
222   /*
223   ||  Created By : [email protected]
224   ||  Created On : 14-JUL-2003
225   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
226   ||            Trigger Handlers for the table, before any DML operation.
227   ||  Known limitations, enhancements or remarks :
228   ||  Change History :
229   ||  Who             When            What
230   ||  (reverse chronological order - newest change first)
231   */
232   BEGIN
233 
234     set_column_values (
235       p_action,
236       x_rowid,
237       x_app_no,
238       x_qual_id,
239       x_qual_type,
240       x_award_body,
241       x_title,
242       x_grade,
243       x_qual_date,
244       x_creation_date,
245       x_created_by,
246       x_last_update_date,
247       x_last_updated_by,
248       x_last_update_login
249     );
250 
251     IF (p_action = 'INSERT') THEN
252       -- Call all the procedures related to Before Insert.
253       check_uniqueness;
254       check_parent_existance;
255     ELSIF (p_action = 'UPDATE') THEN
256       -- Call all the procedures related to Before Update.
257       check_uniqueness;
258       check_parent_existance;
259     ELSIF (p_action = 'VALIDATE_INSERT') THEN
260       -- Call all the procedures related to Before Insert.
261       check_uniqueness;
262     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
263       check_uniqueness;
264     END IF;
265 
266   END before_dml;
267 
268 
269   PROCEDURE insert_row (
270     x_rowid                             IN OUT NOCOPY VARCHAR2,
271     x_app_no                            IN     NUMBER,
272     x_qual_id                           IN     NUMBER,
273     x_qual_type                         IN     VARCHAR2,
274     x_award_body                        IN     VARCHAR2,
275     x_title                             IN     VARCHAR2,
276     x_grade                             IN     VARCHAR2,
277     x_qual_date                         IN     DATE,
278     x_mode                              IN     VARCHAR2
279   ) AS
280   /*
281   ||  Created By : [email protected]
282   ||  Created On : 14-JUL-2003
283   ||  Purpose : Handles the INSERT DML logic for the table.
284   ||  Known limitations, enhancements or remarks :
285   ||  Change History :
286   ||  Who             When            What
287   ||  (reverse chronological order - newest change first)
288   */
289 
290     x_last_update_date           DATE;
291     x_last_updated_by            NUMBER;
292     x_last_update_login          NUMBER;
293 
294   BEGIN
295 
296     x_last_update_date := SYSDATE;
297     IF (x_mode = 'I') THEN
298       x_last_updated_by := 1;
299       x_last_update_login := 0;
300     ELSIF (x_mode = 'R') THEN
301       x_last_updated_by := fnd_global.user_id;
302       IF (x_last_updated_by IS NULL) THEN
303         x_last_updated_by := -1;
304       END IF;
305       x_last_update_login := fnd_global.login_id;
306       IF (x_last_update_login IS NULL) THEN
307         x_last_update_login := -1;
308       END IF;
309     ELSE
310       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
311       fnd_message.set_token ('ROUTINE', 'IGS_UC_FORM_QUALS_PKG.INSERT_ROW');
312       igs_ge_msg_stack.add;
313       app_exception.raise_exception;
314     END IF;
315 
316     before_dml(
317       p_action                            => 'INSERT',
318       x_rowid                             => x_rowid,
319       x_app_no                            => x_app_no,
320       x_qual_id                           => x_qual_id,
321       x_qual_type                         => x_qual_type,
322       x_award_body                        => x_award_body,
323       x_title                             => x_title,
324       x_grade                             => x_grade,
325       x_qual_date                         => x_qual_date,
326       x_creation_date                     => x_last_update_date,
327       x_created_by                        => x_last_updated_by,
328       x_last_update_date                  => x_last_update_date,
329       x_last_updated_by                   => x_last_updated_by,
330       x_last_update_login                 => x_last_update_login
331     );
332 
333     INSERT INTO igs_uc_form_quals (
334       app_no,
335       qual_id,
336       qual_type,
337       award_body,
338       title,
339       grade,
340       qual_date,
341       creation_date,
342       created_by,
343       last_update_date,
344       last_updated_by,
345       last_update_login
346     ) VALUES (
347       new_references.app_no,
348       new_references.qual_id,
349       new_references.qual_type,
350       new_references.award_body,
351       new_references.title,
352       new_references.grade,
353       new_references.qual_date,
354       x_last_update_date,
355       x_last_updated_by,
356       x_last_update_date,
357       x_last_updated_by,
358       x_last_update_login
359     ) RETURNING ROWID INTO x_rowid;
360 
361   END insert_row;
362 
363 
364   PROCEDURE lock_row (
365     x_rowid                             IN     VARCHAR2,
366     x_app_no                            IN     NUMBER,
367     x_qual_id                           IN     NUMBER,
368     x_qual_type                         IN     VARCHAR2,
369     x_award_body                        IN     VARCHAR2,
370     x_title                             IN     VARCHAR2,
371     x_grade                             IN     VARCHAR2,
372     x_qual_date                         IN     DATE
373   ) AS
374   /*
375   ||  Created By : [email protected]
376   ||  Created On : 14-JUL-2003
377   ||  Purpose : Handles the LOCK mechanism for the table.
378   ||  Known limitations, enhancements or remarks :
379   ||  Change History :
380   ||  Who             When            What
381   ||  (reverse chronological order - newest change first)
382   */
383     CURSOR c1 IS
384       SELECT
385         app_no,
386         qual_id,
387         qual_type,
388         award_body,
389         title,
390         grade,
391         qual_date
392       FROM  igs_uc_form_quals
393       WHERE rowid = x_rowid
394       FOR UPDATE NOWAIT;
395 
396     tlinfo c1%ROWTYPE;
397 
398   BEGIN
399 
400     OPEN c1;
401     FETCH c1 INTO tlinfo;
402     IF (c1%notfound) THEN
403       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
404       igs_ge_msg_stack.add;
405       CLOSE c1;
406       app_exception.raise_exception;
407       RETURN;
408     END IF;
409     CLOSE c1;
410 
411     IF (
412         (tlinfo.app_no = x_app_no)
413         AND (tlinfo.qual_id = x_qual_id)
414         AND ((tlinfo.qual_type = x_qual_type) OR ((tlinfo.qual_type IS NULL) AND (X_qual_type IS NULL)))
415         AND ((tlinfo.award_body = x_award_body) OR ((tlinfo.award_body IS NULL) AND (X_award_body IS NULL)))
416         AND ((tlinfo.title = x_title) OR ((tlinfo.title IS NULL) AND (X_title IS NULL)))
417         AND ((tlinfo.grade = x_grade) OR ((tlinfo.grade IS NULL) AND (X_grade IS NULL)))
418         AND ((tlinfo.qual_date = x_qual_date) OR ((tlinfo.qual_date IS NULL) AND (X_qual_date IS NULL)))
419        ) THEN
420       NULL;
421     ELSE
422       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
423       igs_ge_msg_stack.add;
424       app_exception.raise_exception;
425     END IF;
426 
427     RETURN;
428 
429   END lock_row;
430 
431 
432   PROCEDURE update_row (
433     x_rowid                             IN     VARCHAR2,
434     x_app_no                            IN     NUMBER,
435     x_qual_id                           IN     NUMBER,
436     x_qual_type                         IN     VARCHAR2,
437     x_award_body                        IN     VARCHAR2,
438     x_title                             IN     VARCHAR2,
439     x_grade                             IN     VARCHAR2,
440     x_qual_date                         IN     DATE,
441     x_mode                              IN     VARCHAR2
442   ) AS
443   /*
444   ||  Created By : [email protected]
445   ||  Created On : 14-JUL-2003
446   ||  Purpose : Handles the UPDATE DML logic for the table.
447   ||  Known limitations, enhancements or remarks :
448   ||  Change History :
449   ||  Who             When            What
450   ||  (reverse chronological order - newest change first)
451   */
452     x_last_update_date           DATE ;
453     x_last_updated_by            NUMBER;
454     x_last_update_login          NUMBER;
455 
456   BEGIN
457 
458     x_last_update_date := SYSDATE;
459     IF (X_MODE = 'I') THEN
460       x_last_updated_by := 1;
461       x_last_update_login := 0;
462     ELSIF (x_mode = 'R') THEN
463       x_last_updated_by := fnd_global.user_id;
464       IF x_last_updated_by IS NULL THEN
465         x_last_updated_by := -1;
466       END IF;
467       x_last_update_login := fnd_global.login_id;
468       IF (x_last_update_login IS NULL) THEN
469         x_last_update_login := -1;
470       END IF;
471     ELSE
472       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
473       fnd_message.set_token ('ROUTINE', 'IGS_UC_FORM_QUALS_PKG.UPDATE_ROW');
474       igs_ge_msg_stack.add;
475       app_exception.raise_exception;
476     END IF;
477 
478     before_dml(
479       p_action                            => 'UPDATE',
480       x_rowid                             => x_rowid,
481       x_app_no                            => x_app_no,
482       x_qual_id                           => x_qual_id,
483       x_qual_type                         => x_qual_type,
484       x_award_body                        => x_award_body,
485       x_title                             => x_title,
486       x_grade                             => x_grade,
487       x_qual_date                         => x_qual_date,
488       x_creation_date                     => x_last_update_date,
489       x_created_by                        => x_last_updated_by,
490       x_last_update_date                  => x_last_update_date,
491       x_last_updated_by                   => x_last_updated_by,
492       x_last_update_login                 => x_last_update_login
493     );
494 
495     UPDATE igs_uc_form_quals
496       SET
497         app_no                            = new_references.app_no,
498         qual_id                           = new_references.qual_id,
499         qual_type                         = new_references.qual_type,
500         award_body                        = new_references.award_body,
501         title                             = new_references.title,
502         grade                             = new_references.grade,
503         qual_date                         = new_references.qual_date,
504         last_update_date                  = x_last_update_date,
505         last_updated_by                   = x_last_updated_by,
506         last_update_login                 = x_last_update_login
507       WHERE rowid = x_rowid;
508 
509     IF (SQL%NOTFOUND) THEN
510       RAISE NO_DATA_FOUND;
511     END IF;
512 
513   END update_row;
514 
515 
516   PROCEDURE add_row (
517     x_rowid                             IN OUT NOCOPY VARCHAR2,
518     x_app_no                            IN     NUMBER,
519     x_qual_id                           IN     NUMBER,
520     x_qual_type                         IN     VARCHAR2,
521     x_award_body                        IN     VARCHAR2,
522     x_title                             IN     VARCHAR2,
523     x_grade                             IN     VARCHAR2,
524     x_qual_date                         IN     DATE,
525     x_mode                              IN     VARCHAR2
526   ) AS
527   /*
528   ||  Created By : [email protected]
529   ||  Created On : 14-JUL-2003
530   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
531   ||  Known limitations, enhancements or remarks :
532   ||  Change History :
533   ||  Who             When            What
534   ||  (reverse chronological order - newest change first)
535   */
536     CURSOR c1 IS
537       SELECT   rowid
538       FROM     igs_uc_form_quals
539       WHERE    app_no = x_app_no;
540 
541   BEGIN
542 
543     OPEN c1;
544     FETCH c1 INTO x_rowid;
545     IF (c1%NOTFOUND) THEN
546       CLOSE c1;
547 
548       insert_row (
549         x_rowid,
550         x_app_no,
551         x_qual_id,
552         x_qual_type,
553         x_award_body,
554         x_title,
555         x_grade,
556         x_qual_date,
557         x_mode
558       );
559       RETURN;
560     END IF;
561     CLOSE c1;
562 
563     update_row (
564       x_rowid,
565       x_app_no,
566       x_qual_id,
567       x_qual_type,
568       x_award_body,
569       x_title,
570       x_grade,
571       x_qual_date,
572       x_mode
573     );
574 
575   END add_row;
576 
577 
578   PROCEDURE delete_row (
579     x_rowid IN VARCHAR2
580   ) AS
581   /*
582   ||  Created By : [email protected]
583   ||  Created On : 14-JUL-2003
584   ||  Purpose : Handles the DELETE DML logic for the table.
585   ||  Known limitations, enhancements or remarks :
586   ||  Change History :
587   ||  Who             When            What
588   ||  (reverse chronological order - newest change first)
589   */
590   BEGIN
591 
592     before_dml (
593       p_action => 'DELETE',
594       x_rowid => x_rowid
595     );
596 
597     DELETE FROM igs_uc_form_quals
598     WHERE rowid = x_rowid;
599 
600     IF (SQL%NOTFOUND) THEN
601       RAISE NO_DATA_FOUND;
602     END IF;
603 
604   END delete_row;
605 
606 
607 END igs_uc_form_quals_pkg;