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_PS_SCH_PRG_CFIG_PKG

Source


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