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_SUB_ER_RT_PKG

Source


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