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_EN_INST_WL_STPS_PKG

Source


1 PACKAGE BODY igs_en_inst_wl_stps_pkg AS
2 /* $Header: IGSEI71B.pls 115.1 2003/09/18 03:47:45 svanukur noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_en_inst_wl_stps%ROWTYPE;
6   new_references igs_en_inst_wl_stps%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_inst_wlst_setup_id                IN     NUMBER,
12     x_waitlist_allowed_flag             IN     VARCHAR2,
13     x_time_confl_alwd_wlst_flag         IN     VARCHAR2,
14     x_simultaneous_wlst_alwd_flag       IN     VARCHAR2,
15     x_auto_enroll_waitlist_flag         IN     VARCHAR2,
16     x_include_waitlist_cp_flag          IN     VARCHAR2,
17     x_max_waitlists_student_num         IN     NUMBER,
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 : Svanukur
26   ||  Created On : 26-AUG-2003
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_en_inst_wl_stps
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.inst_wlst_setup_id                := x_inst_wlst_setup_id;
58     new_references.waitlist_allowed_flag             := x_waitlist_allowed_flag;
59     new_references.time_confl_alwd_wlst_flag    := x_time_confl_alwd_wlst_flag;
60     new_references.simultaneous_wlst_alwd_flag    := x_simultaneous_wlst_alwd_flag;
61     new_references.auto_enroll_waitlist_flag         := x_auto_enroll_waitlist_flag;
62     new_references.include_waitlist_cp_flag          := x_include_waitlist_cp_flag;
63     new_references.max_waitlists_student_num         := x_max_waitlists_student_num;
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 FUNCTION Get_PK_For_Validation (
80      x_inst_wlst_setup_id IN NUMBER
81     ) RETURN BOOLEAN AS
82 
83     CURSOR cur_rowid IS
84       SELECT   rowid
85       FROM     IGS_EN_INST_WL_STPS
86       WHERE    inst_wlst_setup_id =  x_inst_wlst_setup_id
87       FOR UPDATE NOWAIT;
88 
89     lv_rowid cur_rowid%RowType;
90 
91   BEGIN
92 
93     Open cur_rowid;
94     Fetch cur_rowid INTO lv_rowid;
95     IF (cur_rowid%FOUND) THEN
96 	Close cur_rowid;
97 	return(TRUE);
98     else
99 	Close cur_rowid;
100       Return(FALSE);
101     END IF;
102   END Get_PK_For_Validation;
103 
104   PROCEDURE before_dml (
105     p_action                            IN     VARCHAR2,
106     x_rowid                             IN     VARCHAR2,
107     x_inst_wlst_setup_id                IN     NUMBER,
108     x_waitlist_allowed_flag             IN     VARCHAR2,
109     x_time_confl_alwd_wlst_flag    IN     VARCHAR2,
110     x_simultaneous_wlst_alwd_flag    IN     VARCHAR2,
111     x_auto_enroll_waitlist_flag         IN     VARCHAR2,
112     x_include_waitlist_cp_flag          IN     VARCHAR2,
113     x_max_waitlists_student_num         IN     NUMBER,
114     x_creation_date                     IN     DATE,
115     x_created_by                        IN     NUMBER,
116     x_last_update_date                  IN     DATE,
117     x_last_updated_by                   IN     NUMBER,
118     x_last_update_login                 IN     NUMBER
119   ) AS
120   /*
121   ||  Created By : Svanukur
122   ||  Created On : 26-AUG-2003
123   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
124   ||            Trigger Handlers for the table, before any DML operation.
125   ||  Known limitations, enhancements or remarks :
126   ||  Change History :
127   ||  Who             When            What
128   ||  (reverse chronological order - newest change first)
129   */
130  CURSOR cur_no_recs IS
131  SELECT count(ROWID)
132  FROM IGS_EN_INST_WL_STPS;
133 
134  v_no_recs NUMBER(1);
135   BEGIN
136 
137     set_column_values (
138       p_action,
139       x_rowid,
140       x_inst_wlst_setup_id,
141       x_waitlist_allowed_flag,
142       x_time_confl_alwd_wlst_flag,
143       x_simultaneous_wlst_alwd_flag,
144       x_auto_enroll_waitlist_flag,
145       x_include_waitlist_cp_flag,
146       x_max_waitlists_student_num,
147       x_creation_date,
148       x_created_by,
149       x_last_update_date,
150       x_last_updated_by,
151       x_last_update_login
152     );
153 
154     IF (p_action = 'INSERT') THEN
155       -- Call all the procedures related to Before Insert.
156        OPEN cur_no_recs;
157        FETCH cur_no_recs INTO v_no_recs;
158        CLOSE cur_no_recs;
159 
160        IF v_no_recs >1 THEN
161            fnd_message.set_name ('FND', 'IGS_EN_WLST_ONLY_ONE_REC');
162            igs_ge_msg_stack.add;
163            app_exception.raise_exception;
164        END IF;
165       IF  get_pk_for_validation(new_references.inst_wlst_setup_id ) THEN
166         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
167         igs_ge_msg_stack.add;
168         app_exception.raise_exception;
169       END IF;
170     ELSIF (p_action = 'VALIDATE_INSERT') THEN
171       -- Call all the procedures related to Before Insert.
172       IF get_pk_for_validation (new_references.inst_wlst_setup_id )
173           THEN
174         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
175         igs_ge_msg_stack.add;
176         app_exception.raise_exception;
177       END IF;
178     END IF;
179 
180     IF p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE') THEN
181       l_rowid := NULL;
182     END IF;
183 
184   END before_dml;
185 
186  PROCEDURE After_DML (
187     p_action IN VARCHAR2,
188     x_rowid IN VARCHAR2
189   ) IS
190   /*************************************************************
191   Created By : svanukur
192   Date Created on : 29-AUG-2003
193   Purpose : Creation of TBH
194   Know limitations, enhancements or remarks
195   Change History
196   Who             When            What
197 
198   (reverse chronological order - newest change first)
199   ***************************************************************/
200 
201   BEGIN
202 
203     l_rowid := x_rowid;
204 
205     IF (p_action = 'INSERT') THEN
206       -- Call all the procedures related to After Insert.
207       Null;
208     ELSIF (p_action = 'UPDATE') THEN
209       -- Call all the procedures related to After Update.
210       Null;
211     ELSIF (p_action = 'DELETE') THEN
212       -- Call all the procedures related to After Delete.
213       Null;
214     END IF;
215 
216   END After_DML;
217   PROCEDURE insert_row (
218     x_rowid                             IN OUT NOCOPY VARCHAR2,
219     x_inst_wlst_setup_id                IN OUT NOCOPY   NUMBER,
220     x_waitlist_allowed_flag             IN     VARCHAR2,
221     x_time_confl_alwd_wlst_flag    IN     VARCHAR2,
222     x_simultaneous_wlst_alwd_flag    IN     VARCHAR2,
223     x_auto_enroll_waitlist_flag         IN     VARCHAR2,
224     x_include_waitlist_cp_flag          IN     VARCHAR2,
225     x_max_waitlists_student_num         IN     NUMBER,
226     x_mode                              IN     VARCHAR2
227   ) AS
228   /*
229   ||  Created By : Svanukur
230   ||  Created On : 26-AUG-2003
231   ||  Purpose : Handles the INSERT DML logic for the table.
232   ||  Known limitations, enhancements or remarks :
233   ||  Change History :
234   ||  Who             When            What
235   ||  (reverse chronological order - newest change first)
236   */
237 
238     x_last_update_date           DATE;
239     x_last_updated_by            NUMBER;
240     x_last_update_login          NUMBER;
241 
242   BEGIN
243 
244     x_last_update_date := SYSDATE;
245     IF (x_mode = 'I') THEN
246       x_last_updated_by := 1;
247       x_last_update_login := 0;
248     ELSIF (x_mode = 'R') THEN
249       x_last_updated_by := fnd_global.user_id;
250       IF (x_last_updated_by IS NULL) THEN
251         x_last_updated_by := -1;
252       END IF;
253       x_last_update_login := fnd_global.login_id;
254       IF (x_last_update_login IS NULL) THEN
255         x_last_update_login := -1;
256       END IF;
257     ELSE
258       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
259       fnd_message.set_token ('ROUTINE', 'IGS_EN_INST_WL_STPS_PKG.INSERT_ROW');
260       igs_ge_msg_stack.add;
261       app_exception.raise_exception;
262     END IF;
263 
264     before_dml(
265       p_action                            => 'INSERT',
266       x_rowid                             => x_rowid,
267       x_inst_wlst_setup_id                => x_inst_wlst_setup_id,
268       x_waitlist_allowed_flag             => x_waitlist_allowed_flag,
269       x_time_confl_alwd_wlst_flag    => x_time_confl_alwd_wlst_flag,
270       x_simultaneous_wlst_alwd_flag    => x_simultaneous_wlst_alwd_flag,
271       x_auto_enroll_waitlist_flag         => x_auto_enroll_waitlist_flag,
272       x_include_waitlist_cp_flag          => x_include_waitlist_cp_flag,
273       x_max_waitlists_student_num         => x_max_waitlists_student_num,
274       x_creation_date                     => x_last_update_date,
275       x_created_by                        => x_last_updated_by,
276       x_last_update_date                  => x_last_update_date,
277       x_last_updated_by                   => x_last_updated_by,
278       x_last_update_login                 => x_last_update_login
279     );
280 
281 
282     INSERT INTO igs_en_inst_wl_stps (
283       inst_wlst_setup_id,
284       waitlist_allowed_flag,
285       time_confl_alwd_wlst_flag,
286       simultaneous_wlst_alwd_flag,
287       auto_enroll_waitlist_flag,
288       include_waitlist_cp_flag,
289       max_waitlists_student_num,
290       creation_date,
291       created_by,
292       last_update_date,
293       last_updated_by,
294       last_update_login
295     ) VALUES (
296       1,
297       new_references.waitlist_allowed_flag,
298       new_references.time_confl_alwd_wlst_flag,
299       new_references.simultaneous_wlst_alwd_flag,
300       new_references.auto_enroll_waitlist_flag,
301       new_references.include_waitlist_cp_flag,
302       new_references.max_waitlists_student_num,
303       x_last_update_date,
304       x_last_updated_by,
305       x_last_update_date,
306       x_last_updated_by,
307       x_last_update_login
308     ) RETURNING ROWID, inst_wlst_setup_id INTO x_rowid,x_inst_wlst_setup_id;
309 
310     l_rowid := NULL;
311 
312 After_DML (
313 		p_action => 'INSERT' ,
314 		x_rowid => X_ROWID );
315   END insert_row;
316 
317 
318   PROCEDURE lock_row (
319     x_rowid                             IN     VARCHAR2,
320     x_inst_wlst_setup_id                IN     NUMBER,
321     x_waitlist_allowed_flag             IN     VARCHAR2,
322     x_time_confl_alwd_wlst_flag    IN     VARCHAR2,
323     x_simultaneous_wlst_alwd_flag    IN     VARCHAR2,
324     x_auto_enroll_waitlist_flag         IN     VARCHAR2,
325     x_include_waitlist_cp_flag          IN     VARCHAR2,
326     x_max_waitlists_student_num         IN     NUMBER
327   ) AS
328   /*
329   ||  Created By : Svanukur
330   ||  Created On : 26-AUG-2003
331   ||  Purpose : Handles the LOCK mechanism for the table.
332   ||  Known limitations, enhancements or remarks :
333   ||  Change History :
334   ||  Who             When            What
335   ||  (reverse chronological order - newest change first)
336   */
337     CURSOR c1 IS
338       SELECT
339         inst_wlst_setup_id,
340         waitlist_allowed_flag,
341         time_confl_alwd_wlst_flag,
342         simultaneous_wlst_alwd_flag,
343         auto_enroll_waitlist_flag,
344         include_waitlist_cp_flag,
345         max_waitlists_student_num
346       FROM  igs_en_inst_wl_stps
347       WHERE rowid = x_rowid
348       FOR UPDATE NOWAIT;
349 
350     tlinfo c1%ROWTYPE;
351 
352   BEGIN
353 
354     OPEN c1;
355     FETCH c1 INTO tlinfo;
356     IF (c1%notfound) THEN
357       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
358       igs_ge_msg_stack.add;
359       CLOSE c1;
360       app_exception.raise_exception;
361       RETURN;
362     END IF;
363     CLOSE c1;
364 
365     IF (
366         (tlinfo.inst_wlst_setup_id = x_inst_wlst_setup_id)
367         AND (tlinfo.waitlist_allowed_flag = x_waitlist_allowed_flag)
368         AND (tlinfo.time_confl_alwd_wlst_flag = x_time_confl_alwd_wlst_flag)
369         AND (tlinfo.simultaneous_wlst_alwd_flag = x_simultaneous_wlst_alwd_flag)
370         AND (tlinfo.auto_enroll_waitlist_flag = x_auto_enroll_waitlist_flag)
371         AND (tlinfo.include_waitlist_cp_flag = x_include_waitlist_cp_flag)
372         AND ((tlinfo.max_waitlists_student_num = x_max_waitlists_student_num) OR ((tlinfo.max_waitlists_student_num IS NULL) AND (X_max_waitlists_student_num IS NULL)))
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_inst_wlst_setup_id                IN     NUMBER,
389     x_waitlist_allowed_flag             IN     VARCHAR2,
390     x_time_confl_alwd_wlst_flag         IN     VARCHAR2,
391     x_simultaneous_wlst_alwd_flag       IN     VARCHAR2,
392     x_auto_enroll_waitlist_flag         IN     VARCHAR2,
393     x_include_waitlist_cp_flag          IN     VARCHAR2,
394     x_max_waitlists_student_num         IN     NUMBER,
395     x_mode                              IN     VARCHAR2
396   ) AS
397   /*
398   ||  Created By : Svanukur
399   ||  Created On : 26-AUG-2003
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_EN_INST_WL_STPS_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_inst_wlst_setup_id                => x_inst_wlst_setup_id,
436       x_waitlist_allowed_flag             => x_waitlist_allowed_flag,
437       x_time_confl_alwd_wlst_flag    => x_time_confl_alwd_wlst_flag,
438       x_simultaneous_wlst_alwd_flag    => x_simultaneous_wlst_alwd_flag,
439       x_auto_enroll_waitlist_flag         => x_auto_enroll_waitlist_flag,
440       x_include_waitlist_cp_flag          => x_include_waitlist_cp_flag,
441       x_max_waitlists_student_num         => x_max_waitlists_student_num,
442       x_creation_date                     => x_last_update_date,
443       x_created_by                        => x_last_updated_by,
444       x_last_update_date                  => x_last_update_date,
445       x_last_updated_by                   => x_last_updated_by,
446       x_last_update_login                 => x_last_update_login
447     );
448 
449     UPDATE igs_en_inst_wl_stps
450       SET
451         waitlist_allowed_flag             = new_references.waitlist_allowed_flag,
452         time_confl_alwd_wlst_flag    = new_references.time_confl_alwd_wlst_flag,
453         simultaneous_wlst_alwd_flag    = new_references.simultaneous_wlst_alwd_flag,
454         auto_enroll_waitlist_flag         = new_references.auto_enroll_waitlist_flag,
455         include_waitlist_cp_flag          = new_references.include_waitlist_cp_flag,
456         max_waitlists_student_num         = new_references.max_waitlists_student_num,
457         last_update_date                  = x_last_update_date,
458         last_updated_by                   = x_last_updated_by,
459         last_update_login                 = x_last_update_login
460       WHERE rowid = x_rowid;
461 
462     IF (SQL%NOTFOUND) THEN
463       RAISE NO_DATA_FOUND;
464     END IF;
465 
466     l_rowid := NULL;
467 
468   END update_row;
469 
470 
471   PROCEDURE add_row (
472     x_rowid                             IN OUT NOCOPY VARCHAR2,
473     x_inst_wlst_setup_id                IN OUT NOCOPY   NUMBER,
474     x_waitlist_allowed_flag             IN     VARCHAR2,
475     x_time_confl_alwd_wlst_flag         IN     VARCHAR2,
476     x_simultaneous_wlst_alwd_flag       IN     VARCHAR2,
477     x_auto_enroll_waitlist_flag         IN     VARCHAR2,
478     x_include_waitlist_cp_flag          IN     VARCHAR2,
479     x_max_waitlists_student_num         IN     NUMBER,
480     x_mode                              IN     VARCHAR2
481   ) AS
482   /*
483   ||  Created By : Svanukur
484   ||  Created On : 26-AUG-2003
485   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
486   ||  Known limitations, enhancements or remarks :
487   ||  Change History :
488   ||  Who             When            What
489   ||  (reverse chronological order - newest change first)
490   */
491     CURSOR c1 IS
492       SELECT   rowid
493       FROM     igs_en_inst_wl_stps
494       WHERE    inst_wlst_setup_id= x_inst_wlst_setup_id;
495 
496   BEGIN
497 
498     OPEN c1;
499     FETCH c1 INTO x_rowid;
500     IF (c1%NOTFOUND) THEN
501       CLOSE c1;
502 
503       insert_row (
504         x_rowid,
505         x_inst_wlst_setup_id,
506         x_waitlist_allowed_flag,
507         x_time_confl_alwd_wlst_flag,
508         x_simultaneous_wlst_alwd_flag,
509         x_auto_enroll_waitlist_flag,
510         x_include_waitlist_cp_flag,
511         x_max_waitlists_student_num,
512         x_mode
513       );
514       RETURN;
515     END IF;
516     CLOSE c1;
517 
518     update_row (
519       x_rowid,
520       x_inst_wlst_setup_id,
521       x_waitlist_allowed_flag,
522       x_time_confl_alwd_wlst_flag,
523       x_simultaneous_wlst_alwd_flag,
524       x_auto_enroll_waitlist_flag,
525       x_include_waitlist_cp_flag,
526       x_max_waitlists_student_num,
527       x_mode
528     );
529 
530   END add_row;
531 
532 
533   PROCEDURE delete_row (
534     x_rowid IN VARCHAR2
535   ) AS
536   /*
537   ||  Created By : Svanukur
538   ||  Created On : 26-AUG-2003
539   ||  Purpose : Handles the DELETE DML logic for the table.
540   ||  Known limitations, enhancements or remarks :
541   ||  Change History :
542   ||  Who             When            What
543   ||  (reverse chronological order - newest change first)
544   */
545   BEGIN
546 
547     before_dml (
548       p_action => 'DELETE',
549       x_rowid => x_rowid
550     );
551 
552     DELETE FROM igs_en_inst_wl_stps
553     WHERE rowid = x_rowid;
554 
555     IF (SQL%NOTFOUND) THEN
556       RAISE NO_DATA_FOUND;
557     END IF;
558 
559     l_rowid := NULL;
560 
561   END delete_row;
562 
563 
564 END igs_en_inst_wl_stps_pkg;