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_FI_WAV_STD_PGMS_PKG

Source


1 PACKAGE BODY igs_fi_wav_std_pgms_pkg AS
2 /* $Header: IGSSIF8B.pls 120.0 2005/09/09 18:36:44 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_fi_wav_std_pgms%ROWTYPE;
6   new_references igs_fi_wav_std_pgms%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_waiver_student_id                 IN     NUMBER,
12     x_object_version_number             IN     NUMBER,
13     x_fee_cal_type                      IN     VARCHAR2,
14     x_fee_ci_sequence_number            IN     NUMBER,
15     x_waiver_name                       IN     VARCHAR2,
16     x_person_id                         IN     NUMBER,
17     x_assignment_status_code            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 : 27-JUL-2005
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_fi_wav_std_pgms
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.waiver_student_id                 := x_waiver_student_id;
58     new_references.object_version_number             := x_object_version_number;
59     new_references.fee_cal_type                      := x_fee_cal_type;
60     new_references.fee_ci_sequence_number            := x_fee_ci_sequence_number;
61     new_references.waiver_name                       := x_waiver_name;
62     new_references.person_id                         := x_person_id;
63     new_references.assignment_status_code            := x_assignment_status_code;
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 : 27-JUL-2005
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.fee_cal_type = new_references.fee_cal_type) AND
93          (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
94          (old_references.waiver_name = new_references.waiver_name)) OR
95         ((new_references.fee_cal_type IS NULL) OR
96          (new_references.fee_ci_sequence_number IS NULL) OR
97          (new_references.waiver_name IS NULL))) THEN
98       NULL;
99     ELSIF NOT igs_fi_waiver_pgms_pkg.get_pk_for_validation (
100                 new_references.fee_cal_type,
101                 new_references.fee_ci_sequence_number,
102                 new_references.waiver_name
103               ) THEN
104       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
105       igs_ge_msg_stack.add;
106       app_exception.raise_exception;
107     END IF;
108 
109   END check_parent_existance;
110 
111 
112   FUNCTION get_pk_for_validation (
113     x_waiver_student_id                 IN     NUMBER
114   ) RETURN BOOLEAN AS
115   /*
116   ||  Created By : [email protected]
117   ||  Created On : 27-JUL-2005
118   ||  Purpose : Validates the Primary Key of the table.
119   ||  Known limitations, enhancements or remarks :
120   ||  Change History :
121   ||  Who             When            What
122   ||  (reverse chronological order - newest change first)
123   */
124     CURSOR cur_rowid IS
125       SELECT   rowid
126       FROM     igs_fi_wav_std_pgms
127       WHERE    waiver_student_id = x_waiver_student_id
128       FOR UPDATE NOWAIT;
129 
130     lv_rowid cur_rowid%RowType;
131 
132   BEGIN
133 
134     OPEN cur_rowid;
135     FETCH cur_rowid INTO lv_rowid;
136     IF (cur_rowid%FOUND) THEN
137       CLOSE cur_rowid;
138       RETURN(TRUE);
139     ELSE
140       CLOSE cur_rowid;
141       RETURN(FALSE);
142     END IF;
143 
144   END get_pk_for_validation;
145 
146 
147   PROCEDURE get_fk_igs_fi_waiver_pgms (
148     x_fee_cal_type                      IN     VARCHAR2,
149     x_fee_ci_sequence_number            IN     NUMBER,
150     x_waiver_name                       IN     VARCHAR2
151   ) AS
152   /*
153   ||  Created By : [email protected]
154   ||  Created On : 27-JUL-2005
155   ||  Purpose : Validates the Foreign Keys for the table.
156   ||  Known limitations, enhancements or remarks :
157   ||  Change History :
158   ||  Who             When            What
159   ||  (reverse chronological order - newest change first)
160   */
161     CURSOR cur_rowid IS
162       SELECT   rowid
163       FROM     igs_fi_wav_std_pgms
164       WHERE   ((fee_cal_type = x_fee_cal_type) AND
165                (fee_ci_sequence_number = x_fee_ci_sequence_number) AND
166                (waiver_name = x_waiver_name));
167 
168     lv_rowid cur_rowid%RowType;
169 
170   BEGIN
171 
172     OPEN cur_rowid;
173     FETCH cur_rowid INTO lv_rowid;
174     IF (cur_rowid%FOUND) THEN
175       CLOSE cur_rowid;
176       fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
177       igs_ge_msg_stack.add;
178       app_exception.raise_exception;
179       RETURN;
180     END IF;
181     CLOSE cur_rowid;
182 
183   END get_fk_igs_fi_waiver_pgms;
184 
185 
186   PROCEDURE before_dml (
187     p_action                            IN     VARCHAR2,
188     x_rowid                             IN     VARCHAR2,
189     x_waiver_student_id                 IN     NUMBER,
190     x_object_version_number             IN     NUMBER,
191     x_fee_cal_type                      IN     VARCHAR2,
192     x_fee_ci_sequence_number            IN     NUMBER,
193     x_waiver_name                       IN     VARCHAR2,
194     x_person_id                         IN     NUMBER,
195     x_assignment_status_code            IN     VARCHAR2,
196     x_creation_date                     IN     DATE,
197     x_created_by                        IN     NUMBER,
198     x_last_update_date                  IN     DATE,
199     x_last_updated_by                   IN     NUMBER,
200     x_last_update_login                 IN     NUMBER
201   ) AS
202   /*
203   ||  Created By : [email protected]
204   ||  Created On : 27-JUL-2005
205   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
206   ||            Trigger Handlers for the table, before any DML operation.
207   ||  Known limitations, enhancements or remarks :
208   ||  Change History :
209   ||  Who             When            What
210   ||  (reverse chronological order - newest change first)
211   */
212   BEGIN
213 
214     set_column_values (
215       p_action,
216       x_rowid,
217       x_waiver_student_id,
218       x_object_version_number,
219       x_fee_cal_type,
220       x_fee_ci_sequence_number,
221       x_waiver_name,
222       x_person_id,
223       x_assignment_status_code,
224       x_creation_date,
225       x_created_by,
226       x_last_update_date,
227       x_last_updated_by,
228       x_last_update_login
229     );
230 
231     IF (p_action = 'INSERT') THEN
232       -- Call all the procedures related to Before Insert.
233       IF ( get_pk_for_validation(
234              new_references.waiver_student_id
235            )
236          ) THEN
237         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
238         igs_ge_msg_stack.add;
239         app_exception.raise_exception;
240       END IF;
241       check_parent_existance;
242     ELSIF (p_action = 'UPDATE') THEN
243       -- Call all the procedures related to Before Update.
244       check_parent_existance;
245     ELSIF (p_action = 'VALIDATE_INSERT') THEN
246       -- Call all the procedures related to Before Insert.
247       IF ( get_pk_for_validation (
248              new_references.waiver_student_id
249            )
250          ) THEN
251         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
252         igs_ge_msg_stack.add;
253         app_exception.raise_exception;
254       END IF;
255     END IF;
256 
257   END before_dml;
258 
259 
260   PROCEDURE insert_row (
261     x_rowid                             IN OUT NOCOPY VARCHAR2,
262     x_waiver_student_id                 IN OUT NOCOPY NUMBER,
263     x_fee_cal_type                      IN     VARCHAR2,
264     x_fee_ci_sequence_number            IN     NUMBER,
265     x_waiver_name                       IN     VARCHAR2,
266     x_person_id                         IN     NUMBER,
267     x_assignment_status_code            IN     VARCHAR2,
268     x_mode                              IN     VARCHAR2
269   ) AS
270   /*
271   ||  Created By : [email protected]
272   ||  Created On : 27-JUL-2005
273   ||  Purpose : Handles the INSERT DML logic for the table.
274   ||  Known limitations, enhancements or remarks :
275   ||  Change History :
276   ||  Who             When            What
277   ||  (reverse chronological order - newest change first)
278   */
279 
280     x_last_update_date           DATE;
281     x_last_updated_by            NUMBER;
282     x_last_update_login          NUMBER;
283 
284   BEGIN
285 
286     x_last_update_date := SYSDATE;
287     IF (x_mode = 'I') THEN
288       x_last_updated_by := 1;
289       x_last_update_login := 0;
290     ELSIF (x_mode = 'R') THEN
291       x_last_updated_by := fnd_global.user_id;
292       IF (x_last_updated_by IS NULL) THEN
293         x_last_updated_by := -1;
294       END IF;
295       x_last_update_login := fnd_global.login_id;
296       IF (x_last_update_login IS NULL) THEN
297         x_last_update_login := -1;
298       END IF;
299     ELSE
300       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
301       fnd_message.set_token ('ROUTINE', 'IGS_FI_WAV_STD_PGMS_PKG.INSERT_ROW');
302       igs_ge_msg_stack.add;
303       app_exception.raise_exception;
304     END IF;
305 
306     x_waiver_student_id := NULL;
307 
308     before_dml(
309       p_action                            => 'INSERT',
310       x_rowid                             => x_rowid,
311       x_waiver_student_id                 => x_waiver_student_id,
312       x_object_version_number             => 1,
313       x_fee_cal_type                      => x_fee_cal_type,
314       x_fee_ci_sequence_number            => x_fee_ci_sequence_number,
315       x_waiver_name                       => x_waiver_name,
316       x_person_id                         => x_person_id,
317       x_assignment_status_code            => x_assignment_status_code,
318       x_creation_date                     => x_last_update_date,
319       x_created_by                        => x_last_updated_by,
320       x_last_update_date                  => x_last_update_date,
321       x_last_updated_by                   => x_last_updated_by,
322       x_last_update_login                 => x_last_update_login
323     );
324 
325     INSERT INTO igs_fi_wav_std_pgms (
326       waiver_student_id,
327       object_version_number,
328       fee_cal_type,
329       fee_ci_sequence_number,
330       waiver_name,
331       person_id,
332       assignment_status_code,
333       creation_date,
334       created_by,
335       last_update_date,
336       last_updated_by,
337       last_update_login
338     ) VALUES (
339       igs_fi_wav_std_pgms_s.NEXTVAL,
340       new_references.object_version_number,
341       new_references.fee_cal_type,
342       new_references.fee_ci_sequence_number,
343       new_references.waiver_name,
344       new_references.person_id,
345       new_references.assignment_status_code,
346       x_last_update_date,
347       x_last_updated_by,
348       x_last_update_date,
349       x_last_updated_by,
350       x_last_update_login
351     ) RETURNING ROWID, waiver_student_id INTO x_rowid, x_waiver_student_id;
352 
353   END insert_row;
354 
355 
356   PROCEDURE lock_row (
357     x_rowid                             IN     VARCHAR2,
358     x_waiver_student_id                 IN     NUMBER,
359     x_object_version_number             IN     NUMBER,
360     x_fee_cal_type                      IN     VARCHAR2,
361     x_fee_ci_sequence_number            IN     NUMBER,
362     x_waiver_name                       IN     VARCHAR2,
363     x_person_id                         IN     NUMBER,
364     x_assignment_status_code            IN     VARCHAR2
365   ) AS
366   /*
367   ||  Created By : [email protected]
368   ||  Created On : 27-JUL-2005
369   ||  Purpose : Handles the LOCK mechanism for the table.
370   ||  Known limitations, enhancements or remarks :
371   ||  Change History :
372   ||  Who             When            What
373   ||  (reverse chronological order - newest change first)
374   */
375     CURSOR c1 IS
376       SELECT
377         object_version_number,
378         fee_cal_type,
379         fee_ci_sequence_number,
380         waiver_name,
381         person_id,
382         assignment_status_code
383       FROM  igs_fi_wav_std_pgms
384       WHERE rowid = x_rowid
385       FOR UPDATE NOWAIT;
386 
387     tlinfo c1%ROWTYPE;
388 
389   BEGIN
390 
391     OPEN c1;
392     FETCH c1 INTO tlinfo;
393     IF (c1%notfound) THEN
394       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
395       igs_ge_msg_stack.add;
396       CLOSE c1;
397       app_exception.raise_exception;
398       RETURN;
399     END IF;
400     CLOSE c1;
401 
402     IF (
403         (tlinfo.object_version_number = x_object_version_number)
404         AND (tlinfo.fee_cal_type = x_fee_cal_type)
405         AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
406         AND (tlinfo.waiver_name = x_waiver_name)
407         AND (tlinfo.person_id = x_person_id)
408         AND (tlinfo.assignment_status_code = x_assignment_status_code)
409        ) THEN
410       NULL;
411     ELSE
412       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
413       igs_ge_msg_stack.add;
414       app_exception.raise_exception;
415     END IF;
416 
417     RETURN;
418 
419   END lock_row;
420 
421 
422   PROCEDURE update_row (
423     x_rowid                             IN     VARCHAR2,
424     x_waiver_student_id                 IN     NUMBER,
425     x_fee_cal_type                      IN     VARCHAR2,
426     x_fee_ci_sequence_number            IN     NUMBER,
427     x_waiver_name                       IN     VARCHAR2,
428     x_person_id                         IN     NUMBER,
429     x_assignment_status_code            IN     VARCHAR2,
430     x_mode                              IN     VARCHAR2
431   ) AS
432   /*
433   ||  Created By : [email protected]
434   ||  Created On : 27-JUL-2005
435   ||  Purpose : Handles the UPDATE DML logic for the table.
436   ||  Known limitations, enhancements or remarks :
437   ||  Change History :
438   ||  Who             When            What
439   ||  (reverse chronological order - newest change first)
440   */
441     x_last_update_date           DATE ;
442     x_last_updated_by            NUMBER;
443     x_last_update_login          NUMBER;
444 
445     CURSOR cur_wav_std_pgms(cp_rowid         varchar2) IS
446       SELECT object_version_number
447       FROM   igs_fi_wav_std_pgms
448       WHERE  rowid = cp_rowid
449       FOR UPDATE NOWAIT;
450 
451     l_n_object_version_number           igs_fi_wav_std_pgms.object_version_number%TYPE;
452 
453   BEGIN
454 
455     OPEN cur_wav_std_pgms(x_rowid);
456     FETCH cur_wav_std_pgms INTO l_n_object_version_number;
457     CLOSE cur_wav_std_pgms;
458 
459     x_last_update_date := SYSDATE;
460     IF (X_MODE = 'I') THEN
461       x_last_updated_by := 1;
462       x_last_update_login := 0;
463     ELSIF (x_mode = 'R') THEN
464       x_last_updated_by := fnd_global.user_id;
465       IF x_last_updated_by IS NULL THEN
466         x_last_updated_by := -1;
467       END IF;
468       x_last_update_login := fnd_global.login_id;
469       IF (x_last_update_login IS NULL) THEN
470         x_last_update_login := -1;
471       END IF;
472     ELSE
473       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
474       fnd_message.set_token ('ROUTINE', 'IGS_FI_WAV_STD_PGMS_PKG.UPDATE_ROW');
475       igs_ge_msg_stack.add;
476       app_exception.raise_exception;
477     END IF;
478 
479     before_dml(
480       p_action                            => 'UPDATE',
481       x_rowid                             => x_rowid,
482       x_waiver_student_id                 => x_waiver_student_id,
483       x_object_version_number             => l_n_object_version_number,
484       x_fee_cal_type                      => x_fee_cal_type,
485       x_fee_ci_sequence_number            => x_fee_ci_sequence_number,
486       x_waiver_name                       => x_waiver_name,
487       x_person_id                         => x_person_id,
488       x_assignment_status_code            => x_assignment_status_code,
489       x_creation_date                     => x_last_update_date,
490       x_created_by                        => x_last_updated_by,
491       x_last_update_date                  => x_last_update_date,
492       x_last_updated_by                   => x_last_updated_by,
493       x_last_update_login                 => x_last_update_login
494     );
495 
496     UPDATE igs_fi_wav_std_pgms
497       SET
498         object_version_number             = l_n_object_version_number + 1,
499         fee_cal_type                      = new_references.fee_cal_type,
500         fee_ci_sequence_number            = new_references.fee_ci_sequence_number,
501         waiver_name                       = new_references.waiver_name,
502         person_id                         = new_references.person_id,
503         assignment_status_code            = new_references.assignment_status_code,
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_waiver_student_id                 IN OUT NOCOPY NUMBER,
519     x_object_version_number             IN     NUMBER,
520     x_fee_cal_type                      IN     VARCHAR2,
521     x_fee_ci_sequence_number            IN     NUMBER,
522     x_waiver_name                       IN     VARCHAR2,
523     x_person_id                         IN     NUMBER,
524     x_assignment_status_code            IN     VARCHAR2,
525     x_mode                              IN     VARCHAR2
526   ) AS
527   /*
528   ||  Created By : [email protected]
529   ||  Created On : 27-JUL-2005
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_fi_wav_std_pgms
539       WHERE    waiver_student_id                 = x_waiver_student_id;
540 
541   BEGIN
542 
543     OPEN c1;
544     FETCH c1 INTO x_rowid;
545     IF (c1%NOTFOUND) THEN
546       CLOSE c1;
547 
548       insert_row (
549         x_rowid,
550         x_waiver_student_id,
551         x_fee_cal_type,
552         x_fee_ci_sequence_number,
553         x_waiver_name,
554         x_person_id,
555         x_assignment_status_code,
556         x_mode
557       );
558       RETURN;
559     END IF;
560     CLOSE c1;
561 
562     update_row (
563       x_rowid,
564       x_waiver_student_id,
565       x_fee_cal_type,
566       x_fee_ci_sequence_number,
567       x_waiver_name,
568       x_person_id,
569       x_assignment_status_code,
570       x_mode
571     );
572 
573   END add_row;
574 
575 END igs_fi_wav_std_pgms_pkg;