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_LOOKUPS_PKG

Source


1 PACKAGE BODY igs_ad_ss_lookups_pkg AS
2 /* $Header: IGSAIH9B.pls 120.1 2005/09/08 15:55:55 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_ad_ss_lookups%ROWTYPE;
6   new_references igs_ad_ss_lookups%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_ss_lookup_type                    IN     VARCHAR2,
13     x_ss_lookup_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 : stammine
23   ||  Created On : 07-JUL-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_ss_lookups
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_application_type        := x_admission_application_type;
55     new_references.ss_lookup_type                    := x_ss_lookup_type;
56     new_references.ss_lookup_code                    := x_ss_lookup_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 : stammine
77   ||  Created On : 07-JUL-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_application_type = new_references.admission_application_type)) OR
87         ((new_references.admission_application_type IS NULL))) THEN
88       NULL;
89     ELSIF NOT igs_ad_ss_appl_typ_pkg.get_pk_for_validation (
90                 new_references.admission_application_type
91               ) THEN
92       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
93       igs_ge_msg_stack.add;
94       app_exception.raise_exception;
95     END IF;
96 
97   END check_parent_existance;
98 
99 
100   FUNCTION get_pk_for_validation (
101     x_admission_application_type        IN     VARCHAR2,
102     x_ss_lookup_type                    IN     VARCHAR2,
103     x_ss_lookup_code                    IN     VARCHAR2
104   ) RETURN BOOLEAN AS
105   /*
106   ||  Created By : stammine
107   ||  Created On : 07-JUL-2005
108   ||  Purpose : Validates the Primary Key of the table.
109   ||  Known limitations, enhancements or remarks :
110   ||  Change History :
111   ||  Who             When            What
112   ||  (reverse chronological order - newest change first)
113   */
114     CURSOR cur_rowid IS
115       SELECT   rowid
116       FROM     igs_ad_ss_lookups
117       WHERE    admission_application_type = x_admission_application_type
118       AND      ss_lookup_type = x_ss_lookup_type
119       AND      ss_lookup_code = x_ss_lookup_code
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 : stammine
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_lookups
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_ss_lookup_type                    IN     VARCHAR2,
179     x_ss_lookup_code                    IN     VARCHAR2,
180     x_closed_flag                       IN     VARCHAR2,
181     x_creation_date                     IN     DATE,
182     x_created_by                        IN     NUMBER,
183     x_last_update_date                  IN     DATE,
184     x_last_updated_by                   IN     NUMBER,
185     x_last_update_login                 IN     NUMBER
186   ) AS
187   /*
188   ||  Created By : stammine
189   ||  Created On : 07-JUL-2005
190   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
191   ||            Trigger Handlers for the table, before any DML operation.
192   ||  Known limitations, enhancements or remarks :
193   ||  Change History :
194   ||  Who             When            What
195   ||  (reverse chronological order - newest change first)
196   */
197   BEGIN
198 
199     set_column_values (
200       p_action,
201       x_rowid,
202       x_admission_application_type,
203       x_ss_lookup_type,
204       x_ss_lookup_code,
205       x_closed_flag,
206       x_creation_date,
207       x_created_by,
208       x_last_update_date,
209       x_last_updated_by,
210       x_last_update_login
211     );
212 
213     IF (p_action = 'INSERT') THEN
214       -- Call all the procedures related to Before Insert.
215       IF ( get_pk_for_validation(
216              new_references.admission_application_type,
217              new_references.ss_lookup_type,
218              new_references.ss_lookup_code
219            )
220          ) THEN
221         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
222         igs_ge_msg_stack.add;
223         app_exception.raise_exception;
224       END IF;
225       check_parent_existance;
226     ELSIF (p_action = 'UPDATE') THEN
227       -- Call all the procedures related to Before Update.
228       check_parent_existance;
229     ELSIF (p_action = 'VALIDATE_INSERT') THEN
230       -- Call all the procedures related to Before Insert.
231       IF ( get_pk_for_validation (
232              new_references.admission_application_type,
233              new_references.ss_lookup_type,
234              new_references.ss_lookup_code
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_ss_lookup_type                    IN     VARCHAR2,
250     x_ss_lookup_code                    IN     VARCHAR2,
251     x_closed_flag                       IN     VARCHAR2,
252     x_mode                              IN     VARCHAR2
253   ) AS
254   /*
255   ||  Created By : stammine
256   ||  Created On : 07-JUL-2005
257   ||  Purpose : Handles the INSERT DML logic for the table.
258   ||  Known limitations, enhancements or remarks :
259   ||  Change History :
260   ||  Who             When            What
261   ||  (reverse chronological order - newest change first)
262   */
263 
264     x_last_update_date           DATE;
265     x_last_updated_by            NUMBER;
266     x_last_update_login          NUMBER;
267 
268   BEGIN
269 
270     x_last_update_date := SYSDATE;
271     IF (x_mode = 'I') THEN
272       x_last_updated_by := 1;
273       x_last_update_login := 0;
274     ELSIF (x_mode = 'R') THEN
275       x_last_updated_by := fnd_global.user_id;
276       IF (x_last_updated_by IS NULL) THEN
277         x_last_updated_by := -1;
278       END IF;
279       x_last_update_login := fnd_global.login_id;
280       IF (x_last_update_login IS NULL) THEN
281         x_last_update_login := -1;
282       END IF;
283     ELSE
284       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
285       fnd_message.set_token ('ROUTINE', 'IGS_AD_SS_LOOKUPS_PKG.INSERT_ROW');
286       igs_ge_msg_stack.add;
287       app_exception.raise_exception;
288     END IF;
289 
290     before_dml(
291       p_action                            => 'INSERT',
292       x_rowid                             => x_rowid,
293       x_admission_application_type        => x_admission_application_type,
294       x_ss_lookup_type                    => x_ss_lookup_type,
295       x_ss_lookup_code                    => x_ss_lookup_code,
296       x_closed_flag                       => x_closed_flag,
297       x_creation_date                     => x_last_update_date,
298       x_created_by                        => x_last_updated_by,
299       x_last_update_date                  => x_last_update_date,
300       x_last_updated_by                   => x_last_updated_by,
301       x_last_update_login                 => x_last_update_login
302     );
303 
304     INSERT INTO igs_ad_ss_lookups (
305       admission_application_type,
306       ss_lookup_type,
307       ss_lookup_code,
308       closed_flag,
309       creation_date,
310       created_by,
311       last_update_date,
312       last_updated_by,
313       last_update_login
314     ) VALUES (
315       new_references.admission_application_type,
316       new_references.ss_lookup_type,
317       new_references.ss_lookup_code,
318       new_references.closed_flag,
319       x_last_update_date,
320       x_last_updated_by,
321       x_last_update_date,
322       x_last_updated_by,
323       x_last_update_login
324     ) RETURNING ROWID, admission_application_type INTO x_rowid, x_admission_application_type;
325 
326     IF (x_rowid IS NULL) THEN
327        RAISE NO_DATA_FOUND;
328     END IF;
329 
330   END insert_row;
331 
332 
333   PROCEDURE lock_row (
334     x_rowid                             IN     VARCHAR2,
335     x_admission_application_type        IN     VARCHAR2,
336     x_ss_lookup_type                    IN     VARCHAR2,
337     x_ss_lookup_code                    IN     VARCHAR2,
338     x_closed_flag                       IN     VARCHAR2
339   ) AS
340   /*
341   ||  Created By : stammine
342   ||  Created On : 07-JUL-2005
343   ||  Purpose : Handles the LOCK mechanism for the table.
344   ||  Known limitations, enhancements or remarks :
345   ||  Change History :
346   ||  Who             When            What
347   ||  (reverse chronological order - newest change first)
348   */
349     CURSOR c1 IS
350       SELECT
351         closed_flag
352       FROM  igs_ad_ss_lookups
353       WHERE rowid = x_rowid
354       FOR UPDATE NOWAIT;
355 
356     tlinfo c1%ROWTYPE;
357 
358   BEGIN
359 
360     OPEN c1;
361     FETCH c1 INTO tlinfo;
362     IF (c1%notfound) THEN
363       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
364       igs_ge_msg_stack.add;
365       CLOSE c1;
366       app_exception.raise_exception;
367       RETURN;
368     END IF;
369     CLOSE c1;
370 
371     IF (
372         (tlinfo.closed_flag = x_closed_flag)
373        ) THEN
374       NULL;
375     ELSE
376       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
377       igs_ge_msg_stack.add;
378       app_exception.raise_exception;
379     END IF;
380 
381     RETURN;
382 
383   END lock_row;
384 
385 
386   PROCEDURE update_row (
387     x_rowid                             IN     VARCHAR2,
388     x_admission_application_type        IN     VARCHAR2,
389     x_ss_lookup_type                    IN     VARCHAR2,
390     x_ss_lookup_code                    IN     VARCHAR2,
391     x_closed_flag                       IN     VARCHAR2,
392     x_mode                              IN     VARCHAR2
393   ) AS
394   /*
395   ||  Created By : stammine
396   ||  Created On : 07-JUL-2005
397   ||  Purpose : Handles the UPDATE DML logic for the table.
398   ||  Known limitations, enhancements or remarks :
399   ||  Change History :
400   ||  Who             When            What
401   ||  (reverse chronological order - newest change first)
402   */
403     x_last_update_date           DATE ;
404     x_last_updated_by            NUMBER;
405     x_last_update_login          NUMBER;
406 
407   BEGIN
408 
409     x_last_update_date := SYSDATE;
410     IF (X_MODE = 'I') THEN
411       x_last_updated_by := 1;
412       x_last_update_login := 0;
413     ELSIF (x_mode = 'R') THEN
414       x_last_updated_by := fnd_global.user_id;
415       IF x_last_updated_by IS NULL THEN
416         x_last_updated_by := -1;
417       END IF;
418       x_last_update_login := fnd_global.login_id;
419       IF (x_last_update_login IS NULL) THEN
420         x_last_update_login := -1;
421       END IF;
422     ELSE
423       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
424       fnd_message.set_token ('ROUTINE', 'IGS_AD_SS_LOOKUPS_PKG.UPDATE_ROW');
425       igs_ge_msg_stack.add;
426       app_exception.raise_exception;
427     END IF;
428 
429     before_dml(
430       p_action                            => 'UPDATE',
431       x_rowid                             => x_rowid,
432       x_admission_application_type        => x_admission_application_type,
433       x_ss_lookup_type                    => x_ss_lookup_type,
434       x_ss_lookup_code                    => x_ss_lookup_code,
435       x_closed_flag                       => x_closed_flag,
436       x_creation_date                     => x_last_update_date,
437       x_created_by                        => x_last_updated_by,
438       x_last_update_date                  => x_last_update_date,
439       x_last_updated_by                   => x_last_updated_by,
440       x_last_update_login                 => x_last_update_login
441     );
442 
443     UPDATE igs_ad_ss_lookups
444       SET
445         closed_flag                       = new_references.closed_flag,
446         last_update_date                  = x_last_update_date,
447         last_updated_by                   = x_last_updated_by,
448         last_update_login                 = x_last_update_login
449       WHERE rowid = x_rowid;
450 
451     IF (SQL%NOTFOUND) THEN
452       RAISE NO_DATA_FOUND;
453     END IF;
454 
455   END update_row;
456 
457 
458   PROCEDURE add_row (
459     x_rowid                             IN OUT NOCOPY VARCHAR2,
460     x_admission_application_type        IN OUT NOCOPY VARCHAR2,
461     x_ss_lookup_type                    IN     VARCHAR2,
462     x_ss_lookup_code                    IN     VARCHAR2,
463     x_closed_flag                       IN     VARCHAR2,
464     x_mode                              IN     VARCHAR2
465   ) AS
466   /*
467   ||  Created By : stammine
468   ||  Created On : 07-JUL-2005
469   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
470   ||  Known limitations, enhancements or remarks :
471   ||  Change History :
472   ||  Who             When            What
473   ||  (reverse chronological order - newest change first)
474   */
475     CURSOR c1 IS
476       SELECT   rowid
477       FROM     igs_ad_ss_lookups
478       WHERE    admission_application_type        = x_admission_application_type
479       AND      ss_lookup_type                    = x_ss_lookup_type
480       AND      ss_lookup_code                    = x_ss_lookup_code;
481 
482   BEGIN
483 
484     OPEN c1;
485     FETCH c1 INTO x_rowid;
486     IF (c1%NOTFOUND) THEN
487       CLOSE c1;
488 
489       insert_row (
490         x_rowid,
491         x_admission_application_type,
492         x_ss_lookup_type,
493         x_ss_lookup_code,
494         x_closed_flag,
495         x_mode
496       );
497       RETURN;
498     END IF;
499     CLOSE c1;
500 
501     update_row (
502       x_rowid,
503       x_admission_application_type,
504       x_ss_lookup_type,
505       x_ss_lookup_code,
506       x_closed_flag,
507       x_mode
508     );
509 
510   END add_row;
511 
512 
513   PROCEDURE delete_row (
514     x_rowid IN VARCHAR2
515   ) AS
516   /*
517   ||  Created By : stammine
518   ||  Created On : 07-JUL-2005
519   ||  Purpose : Handles the DELETE DML logic for the table.
520   ||  Known limitations, enhancements or remarks :
521   ||  Change History :
522   ||  Who             When            What
523   ||  (reverse chronological order - newest change first)
524   */
525   BEGIN
526 
527     before_dml (
528       p_action => 'DELETE',
529       x_rowid => x_rowid
530     );
531 
532     DELETE FROM igs_ad_ss_lookups
533     WHERE rowid = x_rowid;
534 
535     IF (SQL%NOTFOUND) THEN
536       RAISE NO_DATA_FOUND;
537     END IF;
538 
539   END delete_row;
540 
541 
542 END igs_ad_ss_lookups_pkg;