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_EN_SEVIS_AUTH_PKG

Source


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