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_SUBMSN_AWD_PKG

Source


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