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_PP_TMPL_LNS_PKG

Source


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