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_PER_STM_TYP_PKG

Source


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