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_TYPE_PKG

Source


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