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_PRT_AWARD_PKG

Source


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