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_FI_WAV_PR_PREQS_PKG

Source


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