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_AD_SS_TERMS_PKG

Source


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