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_LVL_AWARD_PKG

Source


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