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_AS_NOTIFY_BE_PKG

Source


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