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.IGF_AW_FRLOG_PKG

Source


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