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_NAMES_PKG

Source


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