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_CALC_LVL_PKG

Source


1 PACKAGE BODY igs_he_ut_calc_lvl_pkg AS
2 /* $Header: IGSWI34B.pls 115.1 2003/09/02 15:31:18 smaddali noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_he_ut_calc_lvl%ROWTYPE;
6   new_references igs_he_ut_calc_lvl%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_tariff_level_cd                   IN     VARCHAR2,
13     x_closed_ind                        IN     VARCHAR2,
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 : 11-FEB-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_calc_lvl
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.tariff_level_cd                   := x_tariff_level_cd;
55     new_references.closed_ind                        := x_closed_ind;
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 
72   PROCEDURE check_parent_existance AS
73   /*
74   ||  Created By : [email protected]
75   ||  Created On : 11-FEB-2003
76   ||  Purpose : Checks for the existance of Parent records.
77   ||  Known limitations, enhancements or remarks :
78   ||  Change History :
79   ||  Who             When            What
80   ||  (reverse chronological order - newest change first)
81   */
82   BEGIN
83 
84     IF (((old_references.tariff_calc_type_cd = new_references.tariff_calc_type_cd)) OR
85         ((new_references.tariff_calc_type_cd IS NULL))) THEN
86       NULL;
87     -- smaddali modified call to add new parameter , bug#2717747 hefd202_2 build
88     ELSIF NOT igs_he_ut_calc_type_pkg.get_pk_for_validation (
89                 new_references.tariff_calc_type_cd ,
90                 'N'
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   END check_parent_existance;
98 
99 
100   FUNCTION get_pk_for_validation (
101     x_tariff_calc_type_cd               IN     VARCHAR2,
102     x_tariff_level_cd                   IN     VARCHAR2
103   ) RETURN BOOLEAN AS
104   /*
105   ||  Created By : [email protected]
106   ||  Created On : 11-FEB-2003
107   ||  Purpose : Validates the Primary Key of the table.
108   ||  Known limitations, enhancements or remarks :
109   ||  Change History :
110   ||  Who             When            What
111   ||  (reverse chronological order - newest change first)
112   */
113     CURSOR cur_rowid IS
114       SELECT   rowid
115       FROM     igs_he_ut_calc_lvl
116       WHERE    tariff_calc_type_cd = x_tariff_calc_type_cd
117       AND      tariff_level_cd = x_tariff_level_cd ;
118 
119     lv_rowid cur_rowid%RowType;
120 
121   BEGIN
122 
123     OPEN cur_rowid;
124     FETCH cur_rowid INTO lv_rowid;
125     IF (cur_rowid%FOUND) THEN
126       CLOSE cur_rowid;
127       RETURN(TRUE);
128     ELSE
129       CLOSE cur_rowid;
130       RETURN(FALSE);
131     END IF;
132 
133   END get_pk_for_validation;
134 
135 
136   PROCEDURE before_dml (
137     p_action                            IN     VARCHAR2,
138     x_rowid                             IN     VARCHAR2,
139     x_tariff_calc_type_cd               IN     VARCHAR2,
140     x_tariff_level_cd                   IN     VARCHAR2,
141     x_closed_ind                        IN     VARCHAR2,
142     x_creation_date                     IN     DATE,
143     x_created_by                        IN     NUMBER,
144     x_last_update_date                  IN     DATE,
145     x_last_updated_by                   IN     NUMBER,
146     x_last_update_login                 IN     NUMBER
147   ) AS
148   /*
149   ||  Created By : [email protected]
150   ||  Created On : 11-FEB-2003
151   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
152   ||            Trigger Handlers for the table, before any DML operation.
153   ||  Known limitations, enhancements or remarks :
154   ||  Change History :
155   ||  Who             When            What
156   ||  (reverse chronological order - newest change first)
157   */
158   BEGIN
159 
160     set_column_values (
161       p_action,
162       x_rowid,
163       x_tariff_calc_type_cd,
164       x_tariff_level_cd,
165       x_closed_ind,
166       x_creation_date,
167       x_created_by,
168       x_last_update_date,
169       x_last_updated_by,
170       x_last_update_login
171     );
172 
173     IF (p_action = 'INSERT') THEN
174       -- Call all the procedures related to Before Insert.
175       IF ( get_pk_for_validation(
176              new_references.tariff_calc_type_cd,
177              new_references.tariff_level_cd
178            )
179          ) THEN
180         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
181         igs_ge_msg_stack.add;
182         app_exception.raise_exception;
183       END IF;
184       check_parent_existance;
185     ELSIF (p_action = 'UPDATE') THEN
186       -- Call all the procedures related to Before Update.
187       check_parent_existance;
188     ELSIF (p_action = 'VALIDATE_INSERT') THEN
189       -- Call all the procedures related to Before Insert.
190       IF ( get_pk_for_validation (
191              new_references.tariff_calc_type_cd,
192              new_references.tariff_level_cd
193            )
194          ) THEN
195         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
196         igs_ge_msg_stack.add;
197         app_exception.raise_exception;
198       END IF;
199     END IF;
200 
201   END before_dml;
202 
203 
204   PROCEDURE insert_row (
205     x_rowid                             IN OUT NOCOPY VARCHAR2,
206     x_tariff_calc_type_cd               IN     VARCHAR2,
207     x_tariff_level_cd                   IN     VARCHAR2,
208     x_closed_ind                        IN     VARCHAR2,
209     x_mode                              IN     VARCHAR2
210   ) AS
211   /*
212   ||  Created By : [email protected]
213   ||  Created On : 11-FEB-2003
214   ||  Purpose : Handles the INSERT DML logic for the table.
215   ||  Known limitations, enhancements or remarks :
216   ||  Change History :
217   ||  Who             When            What
218   ||  (reverse chronological order - newest change first)
219   */
220 
221     x_last_update_date           DATE;
222     x_last_updated_by            NUMBER;
223     x_last_update_login          NUMBER;
224 
225   BEGIN
226 
227     x_last_update_date := SYSDATE;
228     IF (x_mode = 'I') THEN
229       x_last_updated_by := 1;
230       x_last_update_login := 0;
231     ELSIF (x_mode = 'R') THEN
232       x_last_updated_by := fnd_global.user_id;
233       IF (x_last_updated_by IS NULL) THEN
234         x_last_updated_by := -1;
235       END IF;
236       x_last_update_login := fnd_global.login_id;
237       IF (x_last_update_login IS NULL) THEN
238         x_last_update_login := -1;
239       END IF;
240     ELSE
241       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
242       fnd_message.set_token ('ROUTINE', 'IGS_HE_UT_CALC_LVL_PKG.INSERT_ROW');
243       igs_ge_msg_stack.add;
244       app_exception.raise_exception;
245     END IF;
246 
247     before_dml(
248       p_action                            => 'INSERT',
249       x_rowid                             => x_rowid,
250       x_tariff_calc_type_cd               => x_tariff_calc_type_cd,
251       x_tariff_level_cd                   => x_tariff_level_cd,
252       x_closed_ind                        => x_closed_ind,
253       x_creation_date                     => x_last_update_date,
254       x_created_by                        => x_last_updated_by,
255       x_last_update_date                  => x_last_update_date,
256       x_last_updated_by                   => x_last_updated_by,
257       x_last_update_login                 => x_last_update_login
258     );
259 
260 
261     INSERT INTO igs_he_ut_calc_lvl (
262       tariff_calc_type_cd,
263       tariff_level_cd,
264       closed_ind,
265       creation_date,
266       created_by,
267       last_update_date,
268       last_updated_by,
269       last_update_login
270     ) VALUES (
271       new_references.tariff_calc_type_cd,
272       new_references.tariff_level_cd,
273       new_references.closed_ind,
274       x_last_update_date,
275       x_last_updated_by,
276       x_last_update_date,
277       x_last_updated_by,
278       x_last_update_login
279     ) RETURNING ROWID INTO x_rowid;
280 
281   END insert_row;
282 
283 
284   PROCEDURE lock_row (
285     x_rowid                             IN     VARCHAR2,
286     x_tariff_calc_type_cd               IN     VARCHAR2,
287     x_tariff_level_cd                   IN     VARCHAR2,
288     x_closed_ind                        IN     VARCHAR2
289   ) AS
290   /*
291   ||  Created By : [email protected]
292   ||  Created On : 11-FEB-2003
293   ||  Purpose : Handles the LOCK mechanism for the table.
294   ||  Known limitations, enhancements or remarks :
295   ||  Change History :
296   ||  Who             When            What
297   ||  (reverse chronological order - newest change first)
298   */
299     CURSOR c1 IS
300       SELECT
301         closed_ind
302       FROM  igs_he_ut_calc_lvl
303       WHERE rowid = x_rowid
304       FOR UPDATE NOWAIT;
305 
306     tlinfo c1%ROWTYPE;
307 
308   BEGIN
309 
310     OPEN c1;
311     FETCH c1 INTO tlinfo;
312     IF (c1%notfound) THEN
313       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
314       igs_ge_msg_stack.add;
315       CLOSE c1;
316       app_exception.raise_exception;
317       RETURN;
318     END IF;
319     CLOSE c1;
320 
321     IF (
322         (tlinfo.closed_ind = x_closed_ind)
323        ) THEN
324       NULL;
325     ELSE
326       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
327       igs_ge_msg_stack.add;
328       app_exception.raise_exception;
329     END IF;
330 
331     RETURN;
332 
333   END lock_row;
334 
335 
336   PROCEDURE update_row (
337     x_rowid                             IN     VARCHAR2,
338     x_tariff_calc_type_cd               IN     VARCHAR2,
339     x_tariff_level_cd                   IN     VARCHAR2,
340     x_closed_ind                        IN     VARCHAR2,
341     x_mode                              IN     VARCHAR2
342   ) AS
343   /*
344   ||  Created By : [email protected]
345   ||  Created On : 11-FEB-2003
346   ||  Purpose : Handles the UPDATE DML logic for the table.
347   ||  Known limitations, enhancements or remarks :
348   ||  Change History :
349   ||  Who             When            What
350   ||  (reverse chronological order - newest change first)
351   */
352     x_last_update_date           DATE ;
353     x_last_updated_by            NUMBER;
354     x_last_update_login          NUMBER;
355 
356   BEGIN
357 
358     x_last_update_date := SYSDATE;
359     IF (X_MODE = 'I') THEN
360       x_last_updated_by := 1;
361       x_last_update_login := 0;
362     ELSIF (x_mode = 'R') THEN
363       x_last_updated_by := fnd_global.user_id;
364       IF x_last_updated_by IS NULL THEN
365         x_last_updated_by := -1;
366       END IF;
367       x_last_update_login := fnd_global.login_id;
368       IF (x_last_update_login IS NULL) THEN
369         x_last_update_login := -1;
370       END IF;
371     ELSE
372       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
373       fnd_message.set_token ('ROUTINE', 'IGS_HE_UT_CALC_LVL_PKG.UPDATE_ROW');
374       igs_ge_msg_stack.add;
375       app_exception.raise_exception;
376     END IF;
377 
378     before_dml(
379       p_action                            => 'UPDATE',
380       x_rowid                             => x_rowid,
381       x_tariff_calc_type_cd               => x_tariff_calc_type_cd,
382       x_tariff_level_cd                   => x_tariff_level_cd,
383       x_closed_ind                        => x_closed_ind,
384       x_creation_date                     => x_last_update_date,
385       x_created_by                        => x_last_updated_by,
386       x_last_update_date                  => x_last_update_date,
387       x_last_updated_by                   => x_last_updated_by,
388       x_last_update_login                 => x_last_update_login
389     );
390 
391     UPDATE igs_he_ut_calc_lvl
392       SET
393         closed_ind                        = new_references.closed_ind,
394         last_update_date                  = x_last_update_date,
395         last_updated_by                   = x_last_updated_by,
396         last_update_login                 = x_last_update_login
397       WHERE rowid = x_rowid;
398 
399     IF (SQL%NOTFOUND) THEN
400       RAISE NO_DATA_FOUND;
401     END IF;
402 
403   END update_row;
404 
405 
406   PROCEDURE add_row (
407     x_rowid                             IN OUT NOCOPY VARCHAR2,
408     x_tariff_calc_type_cd               IN     VARCHAR2,
409     x_tariff_level_cd                   IN     VARCHAR2,
410     x_closed_ind                        IN     VARCHAR2,
411     x_mode                              IN     VARCHAR2
412   ) AS
413   /*
414   ||  Created By : [email protected]
415   ||  Created On : 11-FEB-2003
416   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
417   ||  Known limitations, enhancements or remarks :
418   ||  Change History :
419   ||  Who             When            What
420   ||  (reverse chronological order - newest change first)
421   */
422     CURSOR c1 IS
423       SELECT   rowid
424       FROM     igs_he_ut_calc_lvl
425       WHERE    tariff_calc_type_cd               = x_tariff_calc_type_cd
426       AND      tariff_level_cd                   = x_tariff_level_cd;
427 
428   BEGIN
429 
430     OPEN c1;
431     FETCH c1 INTO x_rowid;
432     IF (c1%NOTFOUND) THEN
433       CLOSE c1;
434 
435       insert_row (
436         x_rowid,
437         x_tariff_calc_type_cd,
438         x_tariff_level_cd,
439         x_closed_ind,
440         x_mode
441       );
442       RETURN;
443     END IF;
444     CLOSE c1;
445 
446     update_row (
447       x_rowid,
448       x_tariff_calc_type_cd,
449       x_tariff_level_cd,
450       x_closed_ind,
451       x_mode
452     );
453 
454   END add_row;
455 
456 
457 
458 
459 
460 END igs_he_ut_calc_lvl_pkg;