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_QUAL_TYPE_PKG

Source


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