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_FTE_PROPRT_PKG

Source


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