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_UNT_CRCLM_PKG

Source


1 PACKAGE BODY igs_ps_unt_crclm_pkg AS
2 /* $Header: IGSPI2BB.pls 115.5 2002/11/29 02:14:51 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_ps_unt_crclm_all%ROWTYPE;
6   new_references igs_ps_unt_crclm_all%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_curriculum_id                     IN     VARCHAR2    DEFAULT NULL,
12     x_description                       IN     VARCHAR2    DEFAULT NULL,
13     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
14     x_creation_date                     IN     DATE        DEFAULT NULL,
15     x_created_by                        IN     NUMBER      DEFAULT NULL,
16     x_last_update_date                  IN     DATE        DEFAULT NULL,
17     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
18     x_last_update_login                 IN     NUMBER      DEFAULT NULL
19   ) AS
20   /*
21   ||  Created By : apelleti
22   ||  Created On : 15-MAY-2001
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_UNT_CRCLM_ALL
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.curriculum_id                     := x_curriculum_id;
54     new_references.description                       := x_description;
55     new_references.closed_ind                        := x_closed_ind;
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_child_existance IS
73   /*
74   ||  Created By : apelleti
75   ||  Created On : 15-MAY-2001
76   ||  Purpose : Checks for the existance of Child records.
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     igs_ps_unit_ver_pkg.get_fk_igs_ps_unt_crclm_all (
85       old_references.curriculum_id
86     );
87 
88   END check_child_existance;
89 
90 
91   FUNCTION get_pk_for_validation (
92     x_curriculum_id                     IN     VARCHAR2
93   ) RETURN BOOLEAN AS
94   /*
95   ||  Created By : apelleti
96   ||  Created On : 15-MAY-2001
97   ||  Purpose : Validates the Primary Key of the table.
98   ||  Known limitations, enhancements or remarks :
99   ||  Change History :
100   ||  Who             When            What
101   ||  (reverse chronological order - newest change first)
102   */
103     CURSOR cur_rowid IS
104       SELECT   rowid
105       FROM     igs_ps_unt_crclm_all
106       WHERE    curriculum_id = x_curriculum_id
107       FOR UPDATE NOWAIT;
108 
109     lv_rowid cur_rowid%RowType;
110 
111   BEGIN
112 
113     OPEN cur_rowid;
114     FETCH cur_rowid INTO lv_rowid;
115     IF (cur_rowid%FOUND) THEN
116       CLOSE cur_rowid;
117       RETURN(TRUE);
118     ELSE
119       CLOSE cur_rowid;
120       RETURN(FALSE);
121     END IF;
122 
123   END get_pk_for_validation;
124 
125 
126   PROCEDURE before_dml (
127     p_action                            IN     VARCHAR2,
128     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
129     x_curriculum_id                     IN     VARCHAR2    DEFAULT NULL,
130     x_description                       IN     VARCHAR2    DEFAULT NULL,
131     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
132     x_creation_date                     IN     DATE        DEFAULT NULL,
133     x_created_by                        IN     NUMBER      DEFAULT NULL,
134     x_last_update_date                  IN     DATE        DEFAULT NULL,
135     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
136     x_last_update_login                 IN     NUMBER      DEFAULT NULL
137   ) AS
138   /*
139   ||  Created By : apelleti
140   ||  Created On : 15-MAY-2001
141   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
142   ||            Trigger Handlers for the table, before any DML operation.
143   ||  Known limitations, enhancements or remarks :
144   ||  Change History :
145   ||  Who             When            What
146   ||  (reverse chronological order - newest change first)
147   */
148   BEGIN
149 
150     set_column_values (
151       p_action,
152       x_rowid,
153       x_curriculum_id,
154       x_description,
155       x_closed_ind,
156       x_creation_date,
157       x_created_by,
158       x_last_update_date,
159       x_last_updated_by,
160       x_last_update_login
161     );
162 
163     IF (p_action = 'INSERT') THEN
164       -- Call all the procedures related to Before Insert.
165       IF ( get_pk_for_validation(
166              new_references.curriculum_id
167            )
168          ) THEN
169         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
170         igs_ge_msg_stack.add;
171         app_exception.raise_exception;
172       END IF;
173     ELSIF (p_action = 'DELETE') THEN
174       -- Call all the procedures related to Before Delete.
175       check_child_existance;
176     ELSIF (p_action = 'VALIDATE_INSERT') THEN
177       -- Call all the procedures related to Before Insert.
178       IF ( get_pk_for_validation (
179              new_references.curriculum_id
180            )
181          ) THEN
182         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
183         igs_ge_msg_stack.add;
184         app_exception.raise_exception;
185       END IF;
186     ELSIF (p_action = 'VALIDATE_DELETE') THEN
187       check_child_existance;
188     END IF;
189 
190   END before_dml;
191 
192 
193   PROCEDURE insert_row (
194     x_rowid                             IN OUT NOCOPY VARCHAR2,
195     x_curriculum_id                     IN OUT NOCOPY VARCHAR2,
196     x_description                       IN     VARCHAR2,
197     x_closed_ind                        IN     VARCHAR2,
198     x_mode                              IN     VARCHAR2 DEFAULT 'R'
199   ) AS
200   /*
201   ||  Created By : apelleti
202   ||  Created On : 15-MAY-2001
203   ||  Purpose : Handles the INSERT DML logic for the table.
204   ||  Known limitations, enhancements or remarks :
205   ||  Change History :
206   ||  Who             When            What
207   ||  (reverse chronological order - newest change first)
208   */
209     CURSOR c IS
210       SELECT   rowid
211       FROM     igs_ps_unt_crclm_all
212       WHERE    curriculum_id                     = x_curriculum_id;
213 
214     x_last_update_date           DATE;
215     x_last_updated_by            NUMBER;
216     x_last_update_login          NUMBER;
217 
218   BEGIN
219 
220     x_last_update_date := SYSDATE;
221     IF (x_mode = 'I') THEN
222       x_last_updated_by := 1;
223       x_last_update_login := 0;
224     ELSIF (x_mode = 'R') THEN
225       x_last_updated_by := fnd_global.user_id;
226       IF (x_last_updated_by IS NULL) THEN
227         x_last_updated_by := -1;
228       END IF;
229       x_last_update_login := fnd_global.login_id;
230       IF (x_last_update_login IS NULL) THEN
231         x_last_update_login := -1;
232       END IF;
233     ELSE
234       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
235       igs_ge_msg_stack.add;
236       app_exception.raise_exception;
237     END IF;
238 
239     IF (FND_PROFILE.VALUE('IGS_PS_CURRICULUM_ID') = 'N') THEN
240 
241       SELECT    igs_ps_unt_crclm_all_s.NEXTVAL
242       INTO      x_curriculum_id
243       FROM      dual;
244 
245     END IF;
246     new_references.org_id := igs_ge_gen_003.get_org_id;
247 
248     before_dml(
249       p_action                            => 'INSERT',
250       x_rowid                             => x_rowid,
251       x_curriculum_id                     => x_curriculum_id,
252       x_description                       => x_description,
253       x_closed_ind                        => x_closed_ind,
254       x_creation_date                     => x_last_update_date,
255       x_created_by                        => x_last_updated_by,
256       x_last_update_date                  => x_last_update_date,
257       x_last_updated_by                   => x_last_updated_by,
258       x_last_update_login                 => x_last_update_login
259     );
260 
261     INSERT INTO igs_ps_unt_crclm_all (
262       curriculum_id,
263       description,
264       closed_ind,
265       org_id,
266       creation_date,
267       created_by,
268       last_update_date,
269       last_updated_by,
270       last_update_login
271     ) VALUES (
272       new_references.curriculum_id,
273       new_references.description,
274       new_references.closed_ind,
275       new_references.org_id,
276       x_last_update_date,
277       x_last_updated_by,
278       x_last_update_date,
279       x_last_updated_by,
280       x_last_update_login
281     );
282 
283     OPEN c;
284     FETCH c INTO x_rowid;
285     IF (c%NOTFOUND) THEN
286       CLOSE c;
287       RAISE NO_DATA_FOUND;
288     END IF;
289     CLOSE c;
290 
291   END insert_row;
292 
293 
294   PROCEDURE lock_row (
295     x_rowid                             IN     VARCHAR2,
296     x_curriculum_id                     IN     VARCHAR2,
297     x_description                       IN     VARCHAR2,
298     x_closed_ind                        IN     VARCHAR2
299   ) AS
300   /*
301   ||  Created By : apelleti
302   ||  Created On : 15-MAY-2001
303   ||  Purpose : Handles the LOCK mechanism for the table.
304   ||  Known limitations, enhancements or remarks :
305   ||  Change History :
306   ||  Who             When            What
307   ||  (reverse chronological order - newest change first)
308   */
309     CURSOR c1 IS
310       SELECT
311         description,
312         closed_ind
313       FROM  igs_ps_unt_crclm_all
314       WHERE rowid = x_rowid
315       FOR UPDATE NOWAIT;
316 
317     tlinfo c1%ROWTYPE;
318 
319   BEGIN
320 
321     OPEN c1;
322     FETCH c1 INTO tlinfo;
323     IF (c1%notfound) THEN
324       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
325       igs_ge_msg_stack.add;
326       CLOSE c1;
327       app_exception.raise_exception;
328       RETURN;
329     END IF;
330     CLOSE c1;
331 
332     IF (
333         (tlinfo.description = x_description)
334         AND (tlinfo.closed_ind = x_closed_ind)
335        ) THEN
336       NULL;
337     ELSE
338       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
339       igs_ge_msg_stack.add;
340       app_exception.raise_exception;
341     END IF;
342 
343     RETURN;
344 
345   END lock_row;
346 
347 
348   PROCEDURE update_row (
349     x_rowid                             IN     VARCHAR2,
350     x_curriculum_id                     IN     VARCHAR2,
351     x_description                       IN     VARCHAR2,
352     x_closed_ind                        IN     VARCHAR2,
353     x_mode                              IN     VARCHAR2 DEFAULT 'R'
354   ) AS
355   /*
356   ||  Created By : apelleti
357   ||  Created On : 15-MAY-2001
358   ||  Purpose : Handles the UPDATE DML logic for the table.
359   ||  Known limitations, enhancements or remarks :
360   ||  Change History :
361   ||  Who             When            What
362   ||  (reverse chronological order - newest change first)
363   */
364     x_last_update_date           DATE ;
365     x_last_updated_by            NUMBER;
366     x_last_update_login          NUMBER;
367 
368   BEGIN
369 
370     x_last_update_date := SYSDATE;
371     IF (X_MODE = 'I') THEN
372       x_last_updated_by := 1;
373       x_last_update_login := 0;
374     ELSIF (x_mode = 'R') THEN
375       x_last_updated_by := fnd_global.user_id;
376       IF x_last_updated_by IS NULL THEN
377         x_last_updated_by := -1;
378       END IF;
379       x_last_update_login := fnd_global.login_id;
380       IF (x_last_update_login IS NULL) THEN
381         x_last_update_login := -1;
382       END IF;
383     ELSE
384       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
385       igs_ge_msg_stack.add;
386       app_exception.raise_exception;
387     END IF;
388 
389     before_dml(
390       p_action                            => 'UPDATE',
391       x_rowid                             => x_rowid,
392       x_curriculum_id                     => x_curriculum_id,
393       x_description                       => x_description,
394       x_closed_ind                        => x_closed_ind,
395       x_creation_date                     => x_last_update_date,
396       x_created_by                        => x_last_updated_by,
397       x_last_update_date                  => x_last_update_date,
398       x_last_updated_by                   => x_last_updated_by,
399       x_last_update_login                 => x_last_update_login
400     );
401 
402     UPDATE igs_ps_unt_crclm_all
403       SET
404         description                       = new_references.description,
405         closed_ind                        = new_references.closed_ind,
406         last_update_date                  = x_last_update_date,
407         last_updated_by                   = x_last_updated_by,
408         last_update_login                 = x_last_update_login
409       WHERE rowid = x_rowid;
410 
411     IF (SQL%NOTFOUND) THEN
412       RAISE NO_DATA_FOUND;
413     END IF;
414 
415   END update_row;
416 
417 
418   PROCEDURE add_row (
419     x_rowid                             IN OUT NOCOPY VARCHAR2,
420     x_curriculum_id                     IN OUT NOCOPY VARCHAR2,
421     x_description                       IN     VARCHAR2,
422     x_closed_ind                        IN     VARCHAR2,
423     x_mode                              IN     VARCHAR2 DEFAULT 'R'
424   ) AS
425   /*
426   ||  Created By : apelleti
427   ||  Created On : 15-MAY-2001
428   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
429   ||  Known limitations, enhancements or remarks :
430   ||  Change History :
431   ||  Who             When            What
432   ||  (reverse chronological order - newest change first)
433   */
434     CURSOR c1 IS
435       SELECT   rowid
436       FROM     igs_ps_unt_crclm_all
437       WHERE    curriculum_id                     = x_curriculum_id;
438 
439   BEGIN
440 
441     OPEN c1;
442     FETCH c1 INTO x_rowid;
443     IF (c1%NOTFOUND) THEN
444       CLOSE c1;
445 
446       insert_row (
447         x_rowid,
448         x_curriculum_id,
449         x_description,
450         x_closed_ind,
451         x_mode
452       );
453       RETURN;
454     END IF;
455     CLOSE c1;
456 
457     update_row (
458       x_rowid,
459       x_curriculum_id,
460       x_description,
461       x_closed_ind,
462       x_mode
463     );
464 
465   END add_row;
466 
467 
468   PROCEDURE delete_row (
469     x_rowid IN VARCHAR2
470   ) AS
471   /*
472   ||  Created By : apelleti
473   ||  Created On : 15-MAY-2001
474   ||  Purpose : Handles the DELETE DML logic for the table.
475   ||  Known limitations, enhancements or remarks :
476   ||  Change History :
477   ||  Who             When            What
478   ||  (reverse chronological order - newest change first)
479   */
480   BEGIN
481 
482     before_dml (
483       p_action => 'DELETE',
484       x_rowid => x_rowid
485     );
486 
487     DELETE FROM igs_ps_unt_crclm_all
488     WHERE rowid = x_rowid;
489 
490     IF (SQL%NOTFOUND) THEN
491       RAISE NO_DATA_FOUND;
492     END IF;
493 
494   END delete_row;
495 
496 
497 END igs_ps_unt_crclm_pkg;