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_PS_AWD_HNR_BASE_PKG

Source


1 PACKAGE BODY igs_ps_awd_hnr_base_pkg AS
2 /* $Header: IGSPI3LB.pls 115.2 2003/10/21 08:41:12 nalkumar noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_ps_awd_hnr_base%ROWTYPE;
6   new_references igs_ps_awd_hnr_base%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_awd_hnr_basis_id                  IN     NUMBER,
12     x_award_cd                          IN     VARCHAR2,
13     x_unit_level                        IN     VARCHAR2,
14     x_weighted_average                  IN     NUMBER,
15     x_stat_type                         IN     VARCHAR2,
16     x_s_stat_element                    IN     VARCHAR2,
17     x_timeframe                         IN     VARCHAR2,
18     x_creation_date                     IN     DATE,
19     x_created_by                        IN     NUMBER,
20     x_last_update_date                  IN     DATE,
21     x_last_updated_by                   IN     NUMBER,
22     x_last_update_login                 IN     NUMBER
23   ) AS
24   /*
25   ||  Created By : [email protected]
26   ||  Created On : 17-OCT-2003
27   ||  Purpose : Initialises the Old and New references for the columns of the table.
28   ||  Known limitations, enhancements or remarks :
29   ||  Change History :
30   ||  Who             When            What
31   ||  (reverse chronological order - newest change first)
32   */
33 
34     CURSOR cur_old_ref_values IS
35       SELECT   *
36       FROM     igs_ps_awd_hnr_base
37       WHERE    rowid = x_rowid;
38 
39   BEGIN
40 
41     l_rowid := x_rowid;
42 
43     -- Code for setting the Old and New Reference Values.
44     -- Populate Old Values.
45     OPEN cur_old_ref_values;
46     FETCH cur_old_ref_values INTO old_references;
47     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
48       CLOSE cur_old_ref_values;
49       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
50       igs_ge_msg_stack.add;
51       app_exception.raise_exception;
52       RETURN;
53     END IF;
54     CLOSE cur_old_ref_values;
55 
56     -- Populate New Values.
57     new_references.awd_hnr_basis_id                  := x_awd_hnr_basis_id;
58     new_references.award_cd                          := x_award_cd;
59     new_references.unit_level                        := x_unit_level;
60     new_references.weighted_average                  := x_weighted_average;
61     new_references.stat_type                         := x_stat_type;
62     new_references.s_stat_element                    := x_s_stat_element;
63     new_references.timeframe                         := x_timeframe;
64 
65     IF (p_action = 'UPDATE') THEN
66       new_references.creation_date                   := old_references.creation_date;
67       new_references.created_by                      := old_references.created_by;
68     ELSE
69       new_references.creation_date                   := x_creation_date;
70       new_references.created_by                      := x_created_by;
71     END IF;
72 
73     new_references.last_update_date                  := x_last_update_date;
74     new_references.last_updated_by                   := x_last_updated_by;
75     new_references.last_update_login                 := x_last_update_login;
76 
77   END set_column_values;
78 
79   PROCEDURE beforeinsertupdate(p_rowid IN VARCHAR2 DEFAULT NULL) AS
80   /*
81   ||  Created By : [email protected]
82   ||  Created On : 17-OCT-2003
83   ||  Purpose : To check duplication of the unit Level in a Honors Level.
84   ||  Known limitations, enhancements or remarks :
85   ||  Change History :
86   ||  Who             When            What
87   ||  (reverse chronological order - newest change first)
88   */
89     CURSOR cur_chk_prof IS
90     SELECT 'x'
91     FROM igs_ps_awd_hnr_base
92     WHERE award_cd   = new_references.award_cd;
93     rec_chk_prof cur_chk_prof %ROWTYPE;
94 
95     CURSOR cur_chk_unk_unt_lvl IS
96     SELECT 'x'
97     FROM igs_ps_awd_hnr_base
98     WHERE unit_level = new_references.unit_level AND
99           award_cd   = new_references.award_cd AND
100           (p_rowid IS NULL OR rowid <> p_rowid);
101     rec_chk_unk_unt_lvl cur_chk_unk_unt_lvl%ROWTYPE;
102   BEGIN
103     IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' AND p_rowid IS NULL THEN
104       OPEN cur_chk_prof;
105       FETCH cur_chk_prof INTO rec_chk_prof;
106       IF cur_chk_prof%FOUND THEN
107         CLOSE cur_chk_prof;
108         FND_MESSAGE.SET_NAME ('FND', 'IGS_GE_RECORD_ALREADY_EXISTS');
109         IGS_GE_MSG_STACK.ADD;
110         APP_EXCEPTION.RAISE_EXCEPTION;
111       END IF;
112       CLOSE cur_chk_prof;
113     END IF;
114     OPEN cur_chk_unk_unt_lvl;
115     FETCH cur_chk_unk_unt_lvl INTO rec_chk_unk_unt_lvl;
116     IF cur_chk_unk_unt_lvl%FOUND THEN
117       CLOSE cur_chk_unk_unt_lvl;
118       FND_MESSAGE.SET_NAME ('FND', 'IGS_PR_AWD_UL_EXISTS');
119       IGS_GE_MSG_STACK.ADD;
120       APP_EXCEPTION.RAISE_EXCEPTION;
121     END IF;
122     CLOSE cur_chk_unk_unt_lvl;
123 
124 
125   END beforeinsertupdate;
126 
127 
128 
129   PROCEDURE check_parent_existance AS
130   /*
131   ||  Created By : [email protected]
132   ||  Created On : 17-OCT-2003
133   ||  Purpose : Checks for the existance of Parent records.
134   ||  Known limitations, enhancements or remarks :
135   ||  Change History :
136   ||  Who             When            What
137   ||  (reverse chronological order - newest change first)
138   */
139   BEGIN
140 
141     IF (((old_references.award_cd = new_references.award_cd)) OR
142         ((new_references.award_cd IS NULL))) THEN
143       NULL;
144     ELSIF NOT igs_ps_awd_pkg.get_pk_for_validation (
145                 new_references.award_cd
146               ) THEN
147       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
148       igs_ge_msg_stack.add;
149       app_exception.raise_exception;
150     END IF;
151 
152     IF (((old_references.unit_level = new_references.unit_level)) OR
153         ((new_references.unit_level IS NULL))) THEN
154       NULL;
155     ELSIF NOT igs_ps_unit_level_pkg.get_pk_for_validation (
156                 new_references.unit_level
157               ) THEN
158       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
159       igs_ge_msg_stack.add;
160       app_exception.raise_exception;
161     END IF;
162 
163     IF (((old_references.stat_type = new_references.stat_type) AND
164          (old_references.s_stat_element = new_references.s_stat_element)) OR
165         ((new_references.stat_type IS NULL) OR
166          (new_references.s_stat_element IS NULL))) THEN
167       NULL;
168     ELSIF NOT igs_pr_sta_type_ele_pkg.get_pk_for_validation (
169                 new_references.stat_type,
170                 new_references.s_stat_element
171               ) THEN
172       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
173       igs_ge_msg_stack.add;
174       app_exception.raise_exception;
175     END IF;
176 
177   END check_parent_existance;
178 
179 
180   FUNCTION get_pk_for_validation (
181     x_awd_hnr_basis_id                  IN     NUMBER
182   ) RETURN BOOLEAN AS
183   /*
184   ||  Created By : [email protected]
185   ||  Created On : 17-OCT-2003
186   ||  Purpose : Validates the Primary Key of the table.
187   ||  Known limitations, enhancements or remarks :
188   ||  Change History :
189   ||  Who             When            What
190   ||  (reverse chronological order - newest change first)
191   */
192     CURSOR cur_rowid IS
193       SELECT   rowid
194       FROM     igs_ps_awd_hnr_base
195       WHERE    awd_hnr_basis_id = x_awd_hnr_basis_id
196       FOR UPDATE NOWAIT;
197 
198     lv_rowid cur_rowid%RowType;
199 
200   BEGIN
201 
202     OPEN cur_rowid;
203     FETCH cur_rowid INTO lv_rowid;
204     IF (cur_rowid%FOUND) THEN
205       CLOSE cur_rowid;
206       RETURN(TRUE);
207     ELSE
208       CLOSE cur_rowid;
209       RETURN(FALSE);
210     END IF;
211 
212   END get_pk_for_validation;
213 
214   PROCEDURE before_dml (
215     p_action                            IN     VARCHAR2,
216     x_rowid                             IN     VARCHAR2,
217     x_awd_hnr_basis_id                  IN     NUMBER,
218     x_award_cd                          IN     VARCHAR2,
219     x_unit_level                        IN     VARCHAR2,
220     x_weighted_average                  IN     NUMBER,
221     x_stat_type                         IN     VARCHAR2,
222     x_s_stat_element                    IN     VARCHAR2,
223     x_timeframe                         IN     VARCHAR2,
224     x_creation_date                     IN     DATE,
225     x_created_by                        IN     NUMBER,
226     x_last_update_date                  IN     DATE,
227     x_last_updated_by                   IN     NUMBER,
228     x_last_update_login                 IN     NUMBER
229   ) AS
230   /*
231   ||  Created By : [email protected]
232   ||  Created On : 17-OCT-2003
233   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
234   ||            Trigger Handlers for the table, before any DML operation.
235   ||  Known limitations, enhancements or remarks :
236   ||  Change History :
237   ||  Who             When            What
238   ||  (reverse chronological order - newest change first)
239   */
240   BEGIN
241 
242     set_column_values (
243       p_action,
244       x_rowid,
245       x_awd_hnr_basis_id,
246       x_award_cd,
247       x_unit_level,
248       x_weighted_average,
249       x_stat_type,
250       x_s_stat_element,
251       x_timeframe,
252       x_creation_date,
253       x_created_by,
254       x_last_update_date,
255       x_last_updated_by,
256       x_last_update_login
257     );
258 
259     IF (p_action = 'INSERT') THEN
260       -- Call all the procedures related to Before Insert.
261       IF ( get_pk_for_validation(
262              new_references.awd_hnr_basis_id
263            )
264          ) THEN
265         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
266         igs_ge_msg_stack.add;
267         app_exception.raise_exception;
268       END IF;
269       check_parent_existance;
270       beforeinsertupdate(x_rowid);
271     ELSIF (p_action = 'UPDATE') THEN
272       -- Call all the procedures related to Before Update.
273       check_parent_existance;
274       beforeinsertupdate(x_rowid);
275     ELSIF (p_action = 'VALIDATE_INSERT') THEN
276       -- Call all the procedures related to Before Insert.
277       IF ( get_pk_for_validation (
278              new_references.awd_hnr_basis_id
279            )
280          ) THEN
281         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
282         igs_ge_msg_stack.add;
283         app_exception.raise_exception;
284       END IF;
285       beforeinsertupdate(x_rowid);
286     END IF;
287 
288   END before_dml;
289 
290 
291   PROCEDURE insert_row (
292     x_rowid                             IN OUT NOCOPY VARCHAR2,
293     x_awd_hnr_basis_id                  IN OUT NOCOPY NUMBER,
294     x_award_cd                          IN     VARCHAR2,
295     x_unit_level                        IN     VARCHAR2,
296     x_weighted_average                  IN     NUMBER,
297     x_stat_type                         IN     VARCHAR2,
298     x_s_stat_element                    IN     VARCHAR2,
299     x_timeframe                         IN     VARCHAR2,
300     x_mode                              IN     VARCHAR2
301   ) AS
302   /*
303   ||  Created By : [email protected]
304   ||  Created On : 17-OCT-2003
305   ||  Purpose : Handles the INSERT DML logic for the table.
306   ||  Known limitations, enhancements or remarks :
307   ||  Change History :
308   ||  Who             When            What
309   ||  (reverse chronological order - newest change first)
310   */
311 
312     x_last_update_date           DATE;
313     x_last_updated_by            NUMBER;
314     x_last_update_login          NUMBER;
315 
316   BEGIN
317 
318     x_last_update_date := SYSDATE;
319     IF (x_mode = 'I') THEN
320       x_last_updated_by := 1;
321       x_last_update_login := 0;
322     ELSIF (x_mode = 'R') THEN
323       x_last_updated_by := fnd_global.user_id;
324       IF (x_last_updated_by IS NULL) THEN
325         x_last_updated_by := -1;
326       END IF;
327       x_last_update_login := fnd_global.login_id;
328       IF (x_last_update_login IS NULL) THEN
329         x_last_update_login := -1;
330       END IF;
331     ELSE
332       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
333       fnd_message.set_token ('ROUTINE', 'IGS_PS_AWD_HNR_BASE_PKG.INSERT_ROW');
334       igs_ge_msg_stack.add;
335       app_exception.raise_exception;
336     END IF;
337 
338     x_awd_hnr_basis_id := NULL;
339 
340     before_dml(
341       p_action                            => 'INSERT',
342       x_rowid                             => x_rowid,
343       x_awd_hnr_basis_id                  => x_awd_hnr_basis_id,
344       x_award_cd                          => x_award_cd,
345       x_unit_level                        => x_unit_level,
346       x_weighted_average                  => x_weighted_average,
347       x_stat_type                         => x_stat_type,
348       x_s_stat_element                    => x_s_stat_element,
349       x_timeframe                         => x_timeframe,
350       x_creation_date                     => x_last_update_date,
351       x_created_by                        => x_last_updated_by,
352       x_last_update_date                  => x_last_update_date,
353       x_last_updated_by                   => x_last_updated_by,
354       x_last_update_login                 => x_last_update_login
355     );
356 
357     INSERT INTO igs_ps_awd_hnr_base (
358       awd_hnr_basis_id,
359       award_cd,
360       unit_level,
361       weighted_average,
362       stat_type,
363       s_stat_element,
364       timeframe,
365       creation_date,
366       created_by,
367       last_update_date,
368       last_updated_by,
369       last_update_login
370     ) VALUES (
371       igs_ps_awd_hnr_base_s.NEXTVAL,
372       new_references.award_cd,
373       new_references.unit_level,
374       new_references.weighted_average,
375       new_references.stat_type,
376       new_references.s_stat_element,
377       new_references.timeframe,
378       x_last_update_date,
379       x_last_updated_by,
380       x_last_update_date,
381       x_last_updated_by,
382       x_last_update_login
383     ) RETURNING ROWID, awd_hnr_basis_id INTO x_rowid, x_awd_hnr_basis_id;
384 
385   END insert_row;
386 
387 
388   PROCEDURE lock_row (
389     x_rowid                             IN     VARCHAR2,
390     x_awd_hnr_basis_id                  IN     NUMBER,
391     x_award_cd                          IN     VARCHAR2,
392     x_unit_level                        IN     VARCHAR2,
393     x_weighted_average                  IN     NUMBER,
394     x_stat_type                         IN     VARCHAR2,
395     x_s_stat_element                    IN     VARCHAR2,
396     x_timeframe                         IN     VARCHAR2
397   ) AS
398   /*
399   ||  Created By : [email protected]
400   ||  Created On : 17-OCT-2003
401   ||  Purpose : Handles the LOCK mechanism for the table.
402   ||  Known limitations, enhancements or remarks :
403   ||  Change History :
404   ||  Who             When            What
405   ||  (reverse chronological order - newest change first)
406   */
407     CURSOR c1 IS
408       SELECT
409         award_cd,
410         unit_level,
411         weighted_average,
412         stat_type,
413         s_stat_element,
414         timeframe
415       FROM  igs_ps_awd_hnr_base
416       WHERE rowid = x_rowid
417       FOR UPDATE NOWAIT;
418 
419     tlinfo c1%ROWTYPE;
420 
421   BEGIN
422 
423     OPEN c1;
424     FETCH c1 INTO tlinfo;
425     IF (c1%notfound) THEN
426       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
427       igs_ge_msg_stack.add;
428       CLOSE c1;
429       app_exception.raise_exception;
430       RETURN;
431     END IF;
432     CLOSE c1;
433 
434     IF (
435         (tlinfo.award_cd = x_award_cd)
436         AND ((tlinfo.unit_level = x_unit_level) OR ((tlinfo.unit_level IS NULL) AND (X_unit_level IS NULL)))
437         AND ((tlinfo.weighted_average = x_weighted_average) OR ((tlinfo.weighted_average IS NULL) AND (X_weighted_average IS NULL)))
438         AND ((tlinfo.stat_type = x_stat_type) OR ((tlinfo.stat_type IS NULL) AND (X_stat_type IS NULL)))
439         AND ((tlinfo.s_stat_element = x_s_stat_element) OR ((tlinfo.s_stat_element IS NULL) AND (X_s_stat_element IS NULL)))
440         AND ((tlinfo.timeframe = x_timeframe) OR ((tlinfo.timeframe IS NULL) AND (X_timeframe IS NULL)))
441        ) THEN
442       NULL;
443     ELSE
444       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
445       igs_ge_msg_stack.add;
446       app_exception.raise_exception;
447     END IF;
448 
449     RETURN;
450 
451   END lock_row;
452 
453 
454   PROCEDURE update_row (
455     x_rowid                             IN     VARCHAR2,
456     x_awd_hnr_basis_id                  IN     NUMBER,
457     x_award_cd                          IN     VARCHAR2,
458     x_unit_level                        IN     VARCHAR2,
459     x_weighted_average                  IN     NUMBER,
460     x_stat_type                         IN     VARCHAR2,
461     x_s_stat_element                    IN     VARCHAR2,
462     x_timeframe                         IN     VARCHAR2,
463     x_mode                              IN     VARCHAR2
464   ) AS
465   /*
466   ||  Created By : [email protected]
467   ||  Created On : 17-OCT-2003
468   ||  Purpose : Handles the UPDATE DML logic for the table.
469   ||  Known limitations, enhancements or remarks :
470   ||  Change History :
471   ||  Who             When            What
472   ||  (reverse chronological order - newest change first)
473   */
474     x_last_update_date           DATE ;
475     x_last_updated_by            NUMBER;
476     x_last_update_login          NUMBER;
477 
478   BEGIN
479 
480     x_last_update_date := SYSDATE;
481     IF (X_MODE = 'I') THEN
482       x_last_updated_by := 1;
483       x_last_update_login := 0;
484     ELSIF (x_mode = 'R') THEN
485       x_last_updated_by := fnd_global.user_id;
486       IF x_last_updated_by IS NULL THEN
487         x_last_updated_by := -1;
488       END IF;
489       x_last_update_login := fnd_global.login_id;
490       IF (x_last_update_login IS NULL) THEN
491         x_last_update_login := -1;
492       END IF;
493     ELSE
494       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
495       fnd_message.set_token ('ROUTINE', 'IGS_PS_AWD_HNR_BASE_PKG.UPDATE_ROW');
496       igs_ge_msg_stack.add;
497       app_exception.raise_exception;
498     END IF;
499 
500     before_dml(
501       p_action                            => 'UPDATE',
502       x_rowid                             => x_rowid,
503       x_awd_hnr_basis_id                  => x_awd_hnr_basis_id,
504       x_award_cd                          => x_award_cd,
505       x_unit_level                        => x_unit_level,
506       x_weighted_average                  => x_weighted_average,
507       x_stat_type                         => x_stat_type,
508       x_s_stat_element                    => x_s_stat_element,
509       x_timeframe                         => x_timeframe,
510       x_creation_date                     => x_last_update_date,
511       x_created_by                        => x_last_updated_by,
512       x_last_update_date                  => x_last_update_date,
513       x_last_updated_by                   => x_last_updated_by,
514       x_last_update_login                 => x_last_update_login
515     );
516 
517     UPDATE igs_ps_awd_hnr_base
518       SET
519         award_cd                          = new_references.award_cd,
520         unit_level                        = new_references.unit_level,
521         weighted_average                  = new_references.weighted_average,
522         stat_type                         = new_references.stat_type,
523         s_stat_element                    = new_references.s_stat_element,
524         timeframe                         = new_references.timeframe,
525         last_update_date                  = x_last_update_date,
526         last_updated_by                   = x_last_updated_by,
527         last_update_login                 = x_last_update_login
528       WHERE rowid = x_rowid;
529 
530     IF (SQL%NOTFOUND) THEN
531       RAISE NO_DATA_FOUND;
532     END IF;
533 
534   END update_row;
535 
536 
537   PROCEDURE add_row (
538     x_rowid                             IN OUT NOCOPY VARCHAR2,
539     x_awd_hnr_basis_id                  IN OUT NOCOPY NUMBER,
540     x_award_cd                          IN     VARCHAR2,
541     x_unit_level                        IN     VARCHAR2,
542     x_weighted_average                  IN     NUMBER,
543     x_stat_type                         IN     VARCHAR2,
544     x_s_stat_element                    IN     VARCHAR2,
545     x_timeframe                         IN     VARCHAR2,
546     x_mode                              IN     VARCHAR2
547   ) AS
548   /*
549   ||  Created By : [email protected]
550   ||  Created On : 17-OCT-2003
551   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
552   ||  Known limitations, enhancements or remarks :
553   ||  Change History :
554   ||  Who             When            What
555   ||  (reverse chronological order - newest change first)
556   */
557     CURSOR c1 IS
558       SELECT   rowid
559       FROM     igs_ps_awd_hnr_base
560       WHERE    awd_hnr_basis_id                  = x_awd_hnr_basis_id;
561 
562   BEGIN
563 
564     OPEN c1;
565     FETCH c1 INTO x_rowid;
566     IF (c1%NOTFOUND) THEN
567       CLOSE c1;
568 
569       insert_row (
570         x_rowid,
571         x_awd_hnr_basis_id,
572         x_award_cd,
573         x_unit_level,
574         x_weighted_average,
575         x_stat_type,
576         x_s_stat_element,
577         x_timeframe,
578         x_mode
579       );
580       RETURN;
581     END IF;
582     CLOSE c1;
583 
584     update_row (
585       x_rowid,
586       x_awd_hnr_basis_id,
587       x_award_cd,
588       x_unit_level,
589       x_weighted_average,
590       x_stat_type,
591       x_s_stat_element,
592       x_timeframe,
593       x_mode
594     );
595 
596   END add_row;
597 
598 
599   PROCEDURE delete_row (
600     x_rowid IN VARCHAR2
601   ) AS
602   /*
603   ||  Created By : [email protected]
604   ||  Created On : 17-OCT-2003
605   ||  Purpose : Handles the DELETE DML logic for the table.
606   ||  Known limitations, enhancements or remarks :
607   ||  Change History :
608   ||  Who             When            What
609   ||  (reverse chronological order - newest change first)
610   */
611   BEGIN
612 
613     before_dml (
614       p_action => 'DELETE',
615       x_rowid => x_rowid
616     );
617 
618     DELETE FROM igs_ps_awd_hnr_base
619     WHERE rowid = x_rowid;
620 
621     IF (SQL%NOTFOUND) THEN
622       RAISE NO_DATA_FOUND;
623     END IF;
624 
625   END delete_row;
626 
627 
628 END igs_ps_awd_hnr_base_pkg;