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_HE_UT_PRS_CALCS_PKG

Source


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