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_FAC_TASK_TYP_PKG

Source


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