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_DTLS_PKG

Source


1 PACKAGE BODY igs_he_ut_prs_dtls_pkg AS
2 /* $Header: IGSWI40B.pls 120.2 2005/07/03 18:45:30 appldev ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_he_ut_prs_dtls%ROWTYPE;
6   new_references igs_he_ut_prs_dtls%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_award_cd                          IN     VARCHAR2,
14     x_number_of_qual                    IN     NUMBER,
15     x_tariff_score                      IN     NUMBER,
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 : 30-AUG-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_prs_dtls
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.person_id                         := x_person_id;
57     new_references.award_cd                          := x_award_cd;
58     new_references.number_of_qual                    := x_number_of_qual;
59     new_references.tariff_score                      := x_tariff_score;
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   PROCEDURE check_parent_existance AS
76   /*
77   ||  Created By : prasad marada
78   ||  Created On : 28-AUG-2003
79   ||  Purpose : Checks for the existance of Parent records.
80   ||  Known limitations, enhancements or remarks :
81   ||  Change History :
82   ||  Who             When            What
83   ||  (reverse chronological order - newest change first)
84   */
85   BEGIN
86 
87     -- check for the tariff calcultion type
88     IF (((old_references.tariff_calc_type_cd = new_references.tariff_calc_type_cd) AND
89          (old_references.person_id = new_references.person_id)) OR
90         ((new_references.tariff_calc_type_cd IS NULL) OR
91          (new_references.person_id IS NULL))) THEN
92       NULL;
93     ELSIF NOT igs_he_ut_prs_calcs_pkg.get_pk_for_validation (
94                 new_references.tariff_calc_type_cd,
95                 new_references.person_id
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      -- Check for award cd existance
103      IF ((old_references.award_cd = new_references.award_cd) OR
104          (new_references.award_cd IS NULL)) THEN
105          NULL;
106      ELSIF NOT igs_ps_awd_pkg.get_pk_for_validation(new_references.award_cd) THEN
107         fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
108         igs_ge_msg_stack.add;
109         app_exception.raise_exception;
110      END IF;
111 
112   END check_parent_existance;
113 
114   FUNCTION get_pk_for_validation (
115     x_tariff_calc_type_cd               IN     VARCHAR2,
116     x_person_id                         IN     NUMBER,
117     x_award_cd                          IN     VARCHAR2
118   ) RETURN BOOLEAN AS
119   /*
120   ||  Created By : [email protected]
121   ||  Created On : 30-AUG-2003
122   ||  Purpose : Validates the Primary Key of the table.
123   ||  Known limitations, enhancements or remarks :
124   ||  Change History :
125   ||  Who             When            What
126   ||  (reverse chronological order - newest change first)
127   */
128     CURSOR cur_rowid IS
129       SELECT   rowid
130       FROM     igs_he_ut_prs_dtls
131       WHERE    tariff_calc_type_cd = x_tariff_calc_type_cd
132       AND      person_id = x_person_id
133       AND      award_cd = x_award_cd
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   PROCEDURE get_fk_igs_he_ut_prs_calcs (
153      x_tariff_calc_type_cd               IN     VARCHAR2,
154      x_person_id                         IN     NUMBER
155    ) IS
156 
157       CURSOR cur_rowid IS
158       SELECT   rowid
159       FROM     igs_he_ut_prs_dtls
160       WHERE   ((tariff_calc_type_cd = x_tariff_calc_type_cd) AND
161                (person_id = x_person_id));
162 
163     lv_rowid cur_rowid%ROWTYPE;
164 
165   BEGIN
166 
167     OPEN cur_rowid;
168     FETCH cur_rowid INTO lv_rowid;
169 
170     IF (cur_rowid%FOUND) THEN
171       CLOSE cur_rowid;
172       fnd_message.set_name ('IGS', 'IGS_HE_HUPD_HUPC_FK');
173       igs_ge_msg_stack.add;
174       app_exception.raise_exception;
175       RETURN;
176     END IF;
177     CLOSE cur_rowid;
178 
179   END get_fk_igs_he_ut_prs_calcs;
180 
181   PROCEDURE before_dml (
182     p_action                            IN     VARCHAR2,
183     x_rowid                             IN     VARCHAR2,
184     x_tariff_calc_type_cd               IN     VARCHAR2,
185     x_person_id                         IN     NUMBER,
186     x_award_cd                          IN     VARCHAR2,
187     x_number_of_qual                    IN     NUMBER,
188     x_tariff_score                      IN     NUMBER,
189     x_creation_date                     IN     DATE,
190     x_created_by                        IN     NUMBER,
191     x_last_update_date                  IN     DATE,
192     x_last_updated_by                   IN     NUMBER,
193     x_last_update_login                 IN     NUMBER
194   ) AS
195   /*
196   ||  Created By : [email protected]
197   ||  Created On : 30-AUG-2003
198   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
199   ||            Trigger Handlers for the table, before any DML operation.
200   ||  Known limitations, enhancements or remarks :
201   ||  Change History :
202   ||  Who             When            What
203   ||  (reverse chronological order - newest change first)
204   */
205   BEGIN
206 
207     set_column_values (
208       p_action,
209       x_rowid,
210       x_tariff_calc_type_cd,
211       x_person_id,
212       x_award_cd,
213       x_number_of_qual,
214       x_tariff_score,
215       x_creation_date,
216       x_created_by,
217       x_last_update_date,
218       x_last_updated_by,
219       x_last_update_login
220     );
221 
222     IF (p_action = 'INSERT') THEN
223       -- Call all the procedures related to Before Insert.
224       IF ( get_pk_for_validation(
225              new_references.tariff_calc_type_cd,
226              new_references.person_id,
227              new_references.award_cd
228            )
229          ) THEN
230         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
231         igs_ge_msg_stack.add;
232         app_exception.raise_exception;
233       END IF;
234       check_parent_existance;
235     ELSIF (p_action = 'VALIDATE_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              new_references.person_id,
240              new_references.award_cd
241            )
242          ) THEN
243         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
244         igs_ge_msg_stack.add;
245         app_exception.raise_exception;
246       END IF;
247     END IF;
248 
249     IF (p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE')) THEN
250       l_rowid := NULL;
251     END IF;
252 
253   END before_dml;
254 
255 
256   PROCEDURE insert_row (
257     x_rowid                             IN OUT NOCOPY VARCHAR2,
258     x_tariff_calc_type_cd               IN     VARCHAR2,
259     x_person_id                         IN     NUMBER,
260     x_award_cd                          IN     VARCHAR2,
261     x_number_of_qual                    IN     NUMBER,
262     x_tariff_score                      IN     NUMBER,
263     x_mode                              IN     VARCHAR2
264   ) AS
265   /*
266   ||  Created By : [email protected]
267   ||  Created On : 30-AUG-2003
268   ||  Purpose : Handles the INSERT DML logic for the table.
269   ||  Known limitations, enhancements or remarks :
270   ||  Change History :
271   ||  Who             When            What
272   ||  (reverse chronological order - newest change first)
273   */
274 
275     x_last_update_date           DATE;
276     x_last_updated_by            NUMBER;
277     x_last_update_login          NUMBER;
278 
279   BEGIN
280 
281     x_last_update_date := SYSDATE;
282     IF (x_mode = 'I') THEN
283       x_last_updated_by := 1;
284       x_last_update_login := 0;
285     ELSIF (X_MODE IN ('R', 'S')) THEN
286       x_last_updated_by := fnd_global.user_id;
287       IF (x_last_updated_by IS NULL) THEN
288         x_last_updated_by := -1;
289       END IF;
290       x_last_update_login := fnd_global.login_id;
291       IF (x_last_update_login IS NULL) THEN
292         x_last_update_login := -1;
293       END IF;
294     ELSE
295       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
296       fnd_message.set_token ('ROUTINE', 'IGS_HE_UT_PRS_DTLS_PKG.INSERT_ROW');
297       igs_ge_msg_stack.add;
298       app_exception.raise_exception;
299     END IF;
300 
301     before_dml(
302       p_action                            => 'INSERT',
303       x_rowid                             => x_rowid,
304       x_tariff_calc_type_cd               => x_tariff_calc_type_cd,
305       x_person_id                         => x_person_id,
306       x_award_cd                          => x_award_cd,
307       x_number_of_qual                    => x_number_of_qual,
308       x_tariff_score                      => x_tariff_score,
309       x_creation_date                     => x_last_update_date,
310       x_created_by                        => x_last_updated_by,
311       x_last_update_date                  => x_last_update_date,
312       x_last_updated_by                   => x_last_updated_by,
313       x_last_update_login                 => x_last_update_login
314     );
315 
316      IF (x_mode = 'S') THEN
317     igs_sc_gen_001.set_ctx('R');
318   END IF;
319  INSERT INTO igs_he_ut_prs_dtls (
320       tariff_calc_type_cd,
321       person_id,
322       award_cd,
323       number_of_qual,
324       tariff_score,
325       creation_date,
326       created_by,
327       last_update_date,
328       last_updated_by,
329       last_update_login
330     ) VALUES (
331       new_references.tariff_calc_type_cd,
332       new_references.person_id,
333       new_references.award_cd,
334       new_references.number_of_qual,
335       new_references.tariff_score,
336       x_last_update_date,
337       x_last_updated_by,
338       x_last_update_date,
339       x_last_updated_by,
340       x_last_update_login
341     ) RETURNING ROWID INTO x_rowid;
342  IF (x_mode = 'S') THEN
343     igs_sc_gen_001.unset_ctx('R');
344   END IF;
345 
346 
347     l_rowid := NULL;
348 
349 
350 EXCEPTION
351   WHEN OTHERS THEN
352     IF (SQLCODE IN (-28115, -28113, -28111)) THEN
353       fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
354       fnd_message.set_token ('ERR_CD', SQLCODE);
355       igs_ge_msg_stack.add;
356       igs_sc_gen_001.unset_ctx('R');
357       app_exception.raise_exception;
358     ELSE
359       igs_sc_gen_001.unset_ctx('R');
360       RAISE;
361     END IF;
362  END insert_row;
363 
364 
365   PROCEDURE lock_row (
366     x_rowid                             IN     VARCHAR2,
367     x_tariff_calc_type_cd               IN     VARCHAR2,
368     x_person_id                         IN     NUMBER,
369     x_award_cd                          IN     VARCHAR2,
370     x_number_of_qual                    IN     NUMBER,
371     x_tariff_score                      IN     NUMBER
372   ) AS
373   /*
374   ||  Created By : [email protected]
375   ||  Created On : 30-AUG-2003
376   ||  Purpose : Handles the LOCK mechanism for the table.
377   ||  Known limitations, enhancements or remarks :
378   ||  Change History :
379   ||  Who             When            What
380   ||  (reverse chronological order - newest change first)
381   */
382     CURSOR c1 IS
383       SELECT
384         number_of_qual,
385         tariff_score
386       FROM  igs_he_ut_prs_dtls
387       WHERE rowid = x_rowid
388       FOR UPDATE NOWAIT;
389 
390     tlinfo c1%ROWTYPE;
391 
392   BEGIN
393 
394     OPEN c1;
395     FETCH c1 INTO tlinfo;
396     IF (c1%notfound) THEN
397       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
398       igs_ge_msg_stack.add;
399       CLOSE c1;
400       app_exception.raise_exception;
401       RETURN;
402     END IF;
403     CLOSE c1;
404 
405     IF (
406         (tlinfo.number_of_qual = x_number_of_qual)
407         AND (tlinfo.tariff_score = x_tariff_score)
408        ) THEN
409       NULL;
410     ELSE
411       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
412       igs_ge_msg_stack.add;
413       app_exception.raise_exception;
414     END IF;
415 
416     RETURN;
417 
418   END lock_row;
419 
420 
421   PROCEDURE update_row (
422     x_rowid                             IN     VARCHAR2,
423     x_tariff_calc_type_cd               IN     VARCHAR2,
424     x_person_id                         IN     NUMBER,
425     x_award_cd                          IN     VARCHAR2,
426     x_number_of_qual                    IN     NUMBER,
427     x_tariff_score                      IN     NUMBER,
428     x_mode                              IN     VARCHAR2
429   ) AS
430   /*
431   ||  Created By : [email protected]
432   ||  Created On : 30-AUG-2003
433   ||  Purpose : Handles the UPDATE DML logic for the table.
434   ||  Known limitations, enhancements or remarks :
435   ||  Change History :
436   ||  Who             When            What
437   ||  (reverse chronological order - newest change first)
438   */
439     x_last_update_date           DATE ;
440     x_last_updated_by            NUMBER;
441     x_last_update_login          NUMBER;
442 
443   BEGIN
444 
445     x_last_update_date := SYSDATE;
446     IF (X_MODE = 'I') THEN
447       x_last_updated_by := 1;
448       x_last_update_login := 0;
449     ELSIF (X_MODE IN ('R', 'S')) THEN
450       x_last_updated_by := fnd_global.user_id;
451       IF x_last_updated_by IS NULL THEN
452         x_last_updated_by := -1;
453       END IF;
454       x_last_update_login := fnd_global.login_id;
455       IF (x_last_update_login IS NULL) THEN
456         x_last_update_login := -1;
457       END IF;
458     ELSE
459       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
460       fnd_message.set_token ('ROUTINE', 'IGS_HE_UT_PRS_DTLS_PKG.UPDATE_ROW');
461       igs_ge_msg_stack.add;
462       app_exception.raise_exception;
463     END IF;
464 
465     before_dml(
466       p_action                            => 'UPDATE',
467       x_rowid                             => x_rowid,
468       x_tariff_calc_type_cd               => x_tariff_calc_type_cd,
469       x_person_id                         => x_person_id,
470       x_award_cd                          => x_award_cd,
471       x_number_of_qual                    => x_number_of_qual,
472       x_tariff_score                      => x_tariff_score,
473       x_creation_date                     => x_last_update_date,
474       x_created_by                        => x_last_updated_by,
475       x_last_update_date                  => x_last_update_date,
476       x_last_updated_by                   => x_last_updated_by,
477       x_last_update_login                 => x_last_update_login
478     );
479 
480      IF (x_mode = 'S') THEN
481     igs_sc_gen_001.set_ctx('R');
482   END IF;
483  UPDATE igs_he_ut_prs_dtls
484       SET
485         number_of_qual                    = new_references.number_of_qual,
486         tariff_score                      = new_references.tariff_score,
487         last_update_date                  = x_last_update_date,
488         last_updated_by                   = x_last_updated_by,
489         last_update_login                 = x_last_update_login
490       WHERE rowid = x_rowid;
491 
492     IF (SQL%NOTFOUND) THEN
493      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
494      igs_ge_msg_stack.add;
495      igs_sc_gen_001.unset_ctx('R');
496      app_exception.raise_exception;
497  END IF;
498  IF (x_mode = 'S') THEN
499     igs_sc_gen_001.unset_ctx('R');
500   END IF;
501 
502 
503     l_rowid := NULL;
504 
505   EXCEPTION
506     WHEN OTHERS THEN
507       IF (SQLCODE = (-28115)) THEN
508         fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
509         fnd_message.set_token ('ERR_CD', SQLCODE);
510         igs_ge_msg_stack.add;
511         igs_sc_gen_001.unset_ctx('R');
512         app_exception.raise_exception;
513       ELSE
514         igs_sc_gen_001.unset_ctx('R');
515         RAISE;
516       END IF;
517 
518   END update_row;
519 
520 
521   PROCEDURE add_row (
522     x_rowid                             IN OUT NOCOPY VARCHAR2,
523     x_tariff_calc_type_cd               IN     VARCHAR2,
524     x_person_id                         IN     NUMBER,
525     x_award_cd                          IN     VARCHAR2,
526     x_number_of_qual                    IN     NUMBER,
527     x_tariff_score                      IN     NUMBER,
528     x_mode                              IN     VARCHAR2
529   ) AS
530   /*
531   ||  Created By : [email protected]
532   ||  Created On : 30-AUG-2003
533   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
534   ||  Known limitations, enhancements or remarks :
535   ||  Change History :
536   ||  Who             When            What
537   ||  (reverse chronological order - newest change first)
538   */
539     CURSOR c1 IS
540       SELECT   rowid
541       FROM     igs_he_ut_prs_dtls
542       WHERE    tariff_calc_type_cd               = x_tariff_calc_type_cd
543       AND      person_id                         = x_person_id
544       AND      award_cd                          = x_award_cd;
545 
546   BEGIN
547 
548     OPEN c1;
549     FETCH c1 INTO x_rowid;
550     IF (c1%NOTFOUND) THEN
551       CLOSE c1;
552 
553       insert_row (
554         x_rowid,
555         x_tariff_calc_type_cd,
556         x_person_id,
557         x_award_cd,
558         x_number_of_qual,
559         x_tariff_score,
560         x_mode
561       );
562       RETURN;
563     END IF;
564     CLOSE c1;
565 
566     update_row (
567       x_rowid,
568       x_tariff_calc_type_cd,
569       x_person_id,
570       x_award_cd,
571       x_number_of_qual,
572       x_tariff_score,
573       x_mode
574     );
575 
576   END add_row;
577 
578 
579   PROCEDURE delete_row (
580     x_rowid IN VARCHAR2,
581   x_mode IN VARCHAR2
582   ) AS
583   /*
584   ||  Created By : [email protected]
585   ||  Created On : 30-AUG-2003
586   ||  Purpose : Handles the DELETE DML logic for the table.
587   ||  Known limitations, enhancements or remarks :
588   ||  Change History :
589   ||  Who             When            What
590   ||  (reverse chronological order - newest change first)
591   */
592   BEGIN
593 
594     before_dml (
595       p_action => 'DELETE',
596       x_rowid => x_rowid
597     );
598 
599      IF (x_mode = 'S') THEN
600     igs_sc_gen_001.set_ctx('R');
601   END IF;
602  DELETE FROM igs_he_ut_prs_dtls
603     WHERE rowid = x_rowid;
604 
605     IF (SQL%NOTFOUND) THEN
606      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
607      igs_ge_msg_stack.add;
608      igs_sc_gen_001.unset_ctx('R');
609      app_exception.raise_exception;
610  END IF;
611  IF (x_mode = 'S') THEN
612     igs_sc_gen_001.unset_ctx('R');
613   END IF;
614 
615 
616     l_rowid := NULL;
617 
618   END delete_row;
619 
620 
621 END igs_he_ut_prs_dtls_pkg;