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_TR_STEP_CTLG_PKG

Source


1 PACKAGE BODY igs_tr_step_ctlg_pkg AS
2 /* $Header: IGSTI13B.pls 115.8 2003/06/26 06:53:14 vrathi ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_tr_step_ctlg_all%ROWTYPE;
6   new_references igs_tr_step_ctlg_all%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_step_catalog_id                   IN     NUMBER      DEFAULT NULL,
12     x_step_catalog_cd                   IN     VARCHAR2    DEFAULT NULL,
13     x_description                       IN     VARCHAR2    DEFAULT NULL,
14     x_action_days                       IN     NUMBER      DEFAULT NULL,
15     x_s_tracking_step_type              IN     VARCHAR2    DEFAULT NULL,
16     x_publish_ind                       IN     VARCHAR2    DEFAULT NULL,
17     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
18     x_creation_date                     IN     DATE        DEFAULT NULL,
19     x_created_by                        IN     NUMBER      DEFAULT NULL,
20     x_last_update_date                  IN     DATE        DEFAULT NULL,
21     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
22     x_last_update_login                 IN     NUMBER      DEFAULT NULL
23   ) AS
24   /*
25   ||  Created By : smaddali
26   ||  Created On : 18-JUN-2001
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_TR_STEP_CTLG_ALL
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.step_catalog_id                   := x_step_catalog_id;
58     new_references.step_catalog_cd                   := x_step_catalog_cd;
59     new_references.description                       := x_description;
60     new_references.action_days                       := x_action_days;
61     new_references.s_tracking_step_type              := x_s_tracking_step_type;
62     new_references.publish_ind                       := x_publish_ind;
63     new_references.closed_ind                        := x_closed_ind;
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 
80   PROCEDURE check_uniqueness AS
81   /*
82   ||  Created By : smaddali
83   ||  Created On : 18-JUN-2001
84   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
85   ||  Known limitations, enhancements or remarks :
86   ||  Change History :
87   ||  Who             When            What
88   ||  (reverse chronological order - newest change first)
89   */
90   BEGIN
91 
92     IF ( get_uk_for_validation (
93            new_references.step_catalog_cd
94          )
95        ) THEN
96       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
97       igs_ge_msg_stack.add;
98       app_exception.raise_exception;
99     END IF;
100 
101   END check_uniqueness;
102 
103   PROCEDURE check_parent_existance AS
104   /*
105   ||  Created By : smaddali
106   ||  Created On : 18-JUN-2001
107   ||  Purpose : Checks for the existance of Parent records.
108   ||  Known limitations, enhancements or remarks :
109   ||  Change History :
110   ||  Who             When            What
111   ||  (reverse chronological order - newest change first)
112   */
113   BEGIN
114 
115     IF (((old_references.s_tracking_step_type = new_references.s_tracking_step_type)) OR
116         ((new_references.s_tracking_step_type IS NULL))) THEN
117              NULL;
118     ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
119           'TRACKING_STEP_TYPE',
120           new_references.s_tracking_step_type
121           )THEN
122             Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
123             IGS_GE_MSG_STACK.ADD;
124             App_Exception.Raise_Exception;
125     END IF;
126 
127   END check_parent_existance;
128 
129   FUNCTION get_pk_for_validation (
130     x_step_catalog_id                   IN     NUMBER
131   ) RETURN BOOLEAN AS
132   /*
133   ||  Created By : smaddali
134   ||  Created On : 18-JUN-2001
135   ||  Purpose : Validates the Primary Key of the table.
136   ||  Known limitations, enhancements or remarks :
137   ||  Change History :
138   ||  Who             When            What
139   ||  (reverse chronological order - newest change first)
140   */
141     CURSOR cur_rowid IS
142       SELECT   rowid
143       FROM     igs_tr_step_ctlg_all
144       WHERE    step_catalog_id = x_step_catalog_id;
145 
146     lv_rowid cur_rowid%RowType;
147 
148   BEGIN
149 
150     OPEN cur_rowid;
151     FETCH cur_rowid INTO lv_rowid;
152     IF (cur_rowid%FOUND) THEN
153       CLOSE cur_rowid;
154       RETURN(TRUE);
155     ELSE
156       CLOSE cur_rowid;
157       RETURN(FALSE);
158     END IF;
159 
160   END get_pk_for_validation;
161 
162 
163   FUNCTION get_uk_for_validation (
164     x_step_catalog_cd                   IN     VARCHAR2
165   ) RETURN BOOLEAN AS
166   /*
167   ||  Created By : smaddali
168   ||  Created On : 18-JUN-2001
169   ||  Purpose : Validates the Unique Keys of the table.
170   ||  Known limitations, enhancements or remarks :
171   ||  Change History :
172   ||  Who             When            What
173   ||  (reverse chronological order - newest change first)
174   */
175     CURSOR cur_rowid IS
176       SELECT   rowid
177       FROM     igs_tr_step_ctlg_all
178       WHERE    step_catalog_cd = x_step_catalog_cd
179       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
180 
181     lv_rowid cur_rowid%RowType;
182 
183   BEGIN
184 
185     OPEN cur_rowid;
186     FETCH cur_rowid INTO lv_rowid;
187     IF (cur_rowid%FOUND) THEN
188       CLOSE cur_rowid;
189         RETURN (true);
190         ELSE
191        CLOSE cur_rowid;
192       RETURN(FALSE);
193     END IF;
194 
195   END get_uk_for_validation ;
196 
197 
198   PROCEDURE before_dml (
199     p_action                            IN     VARCHAR2,
200     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
201     x_step_catalog_id                   IN     NUMBER      DEFAULT NULL,
202     x_step_catalog_cd                   IN     VARCHAR2    DEFAULT NULL,
203     x_description                       IN     VARCHAR2    DEFAULT NULL,
204     x_action_days                       IN     NUMBER      DEFAULT NULL,
205     x_s_tracking_step_type              IN     VARCHAR2    DEFAULT NULL,
206     x_publish_ind                       IN     VARCHAR2    DEFAULT NULL,
207     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
208     x_creation_date                     IN     DATE        DEFAULT NULL,
209     x_created_by                        IN     NUMBER      DEFAULT NULL,
210     x_last_update_date                  IN     DATE        DEFAULT NULL,
211     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
212     x_last_update_login                 IN     NUMBER      DEFAULT NULL
213   ) AS
214   /*
215   ||  Created By : smaddali
216   ||  Created On : 18-JUN-2001
217   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
218   ||            Trigger Handlers for the table, before any DML operation.
219   ||  Known limitations, enhancements or remarks :
220   ||  Change History :
221   ||  Who             When            What
222   ||  (reverse chronological order - newest change first)
223   ||  vrathi          19-jun-2003     Bug: 2885713
224   ||                                  Made l_rowid Null after Before_DML
225   */
226   BEGIN
227 
228     set_column_values (
229       p_action,
230       x_rowid,
231       x_step_catalog_id,
232       x_step_catalog_cd,
233       x_description,
234       x_action_days,
235       x_s_tracking_step_type,
236       x_publish_ind,
237       x_closed_ind,
238       x_creation_date,
239       x_created_by,
240       x_last_update_date,
241       x_last_updated_by,
242       x_last_update_login
243     );
244 
245     IF (p_action = 'INSERT') THEN
246       -- Call all the procedures related to Before Insert.
247       IF ( get_pk_for_validation(
248              new_references.step_catalog_id
249            )
250          ) THEN
251         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
252         igs_ge_msg_stack.add;
253         app_exception.raise_exception;
254       END IF;
255       check_uniqueness;
256       check_parent_existance ;
257     ELSIF (p_action = 'UPDATE') THEN
258       -- Call all the procedures related to Before Update.
259       check_uniqueness;
260       check_parent_existance ;
261     ELSIF (p_action = 'VALIDATE_INSERT') THEN
262       -- Call all the procedures related to Before Insert.
263       IF ( get_pk_for_validation (
264              new_references.step_catalog_id
265            )
266          ) THEN
267         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
268         igs_ge_msg_stack.add;
269         app_exception.raise_exception;
270       END IF;
271       check_uniqueness;
272     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
273       check_uniqueness;
274     END IF;
275 
276     l_rowid := NULL;
277 
278   END before_dml;
279 
280 
281   PROCEDURE insert_row (
282     x_rowid                             IN OUT NOCOPY VARCHAR2,
283     x_step_catalog_id                   IN OUT NOCOPY NUMBER,
284     x_step_catalog_cd                   IN     VARCHAR2,
285     x_description                       IN     VARCHAR2,
286     x_action_days                       IN     NUMBER,
287     x_s_tracking_step_type              IN     VARCHAR2,
288     x_publish_ind                       IN     VARCHAR2,
289     x_closed_ind                        IN     VARCHAR2,
290     x_mode                              IN     VARCHAR2 DEFAULT 'R'
291   ) AS
292   /*
293   ||  Created By : smaddali
294   ||  Created On : 18-JUN-2001
295   ||  Purpose : Handles the INSERT DML logic for the table.
296   ||  Known limitations, enhancements or remarks :
297   ||  Change History :
298   ||  Who             When            What
299   ||  (reverse chronological order - newest change first)
300   */
301     CURSOR c IS
302       SELECT   rowid
303       FROM     igs_tr_step_ctlg_all
304       WHERE    step_catalog_id                   = x_step_catalog_id;
305 
306     x_last_update_date           DATE;
307     x_last_updated_by            NUMBER;
308     x_last_update_login          NUMBER;
309 
310   BEGIN
311 
312     x_last_update_date := SYSDATE;
313     IF (x_mode = 'I') THEN
314       x_last_updated_by := 1;
315       x_last_update_login := 0;
316     ELSIF (x_mode = 'R') THEN
317       x_last_updated_by := fnd_global.user_id;
318       IF (x_last_updated_by IS NULL) THEN
319         x_last_updated_by := -1;
320       END IF;
321       x_last_update_login := fnd_global.login_id;
322       IF (x_last_update_login IS NULL) THEN
323         x_last_update_login := -1;
324       END IF;
325     ELSE
326       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
327       igs_ge_msg_stack.add;
328       app_exception.raise_exception;
329     END IF;
330 
331     SELECT    igs_tr_step_ctlg_all_s.NEXTVAL
332     INTO      x_step_catalog_id
333     FROM      dual;
334 
335     new_references.org_id := igs_ge_gen_003.get_org_id;
336 
337     before_dml(
338       p_action                            => 'INSERT',
339       x_rowid                             => x_rowid,
340       x_step_catalog_id                   => x_step_catalog_id,
341       x_step_catalog_cd                   => x_step_catalog_cd,
342       x_description                       => x_description,
343       x_action_days                       => x_action_days,
344       x_s_tracking_step_type              => x_s_tracking_step_type,
345       x_publish_ind                       => x_publish_ind,
346       x_closed_ind                        => x_closed_ind,
347       x_creation_date                     => x_last_update_date,
348       x_created_by                        => x_last_updated_by,
349       x_last_update_date                  => x_last_update_date,
350       x_last_updated_by                   => x_last_updated_by,
351       x_last_update_login                 => x_last_update_login
352     );
353 
354     INSERT INTO igs_tr_step_ctlg_all (
355       step_catalog_id,
356       step_catalog_cd,
357       description,
358       action_days,
359       s_tracking_step_type,
360       publish_ind,
361       closed_ind,
362       org_id,
363       creation_date,
364       created_by,
365       last_update_date,
366       last_updated_by,
367       last_update_login
368     ) VALUES (
369       new_references.step_catalog_id,
370       new_references.step_catalog_cd,
371       new_references.description,
372       new_references.action_days,
373       new_references.s_tracking_step_type,
374       new_references.publish_ind,
375       new_references.closed_ind,
376       new_references.org_id,
377       x_last_update_date,
378       x_last_updated_by,
379       x_last_update_date,
380       x_last_updated_by,
381       x_last_update_login
382     );
383 
384     OPEN c;
385     FETCH c INTO x_rowid;
386     IF (c%NOTFOUND) THEN
387       CLOSE c;
388       RAISE NO_DATA_FOUND;
389     END IF;
390     CLOSE c;
391 
392   END insert_row;
393 
394 
395   PROCEDURE lock_row (
396     x_rowid                             IN     VARCHAR2,
397     x_step_catalog_id                   IN     NUMBER,
398     x_step_catalog_cd                   IN     VARCHAR2,
399     x_description                       IN     VARCHAR2,
400     x_action_days                       IN     NUMBER,
401     x_s_tracking_step_type              IN     VARCHAR2,
402     x_publish_ind                       IN     VARCHAR2,
403     x_closed_ind                        IN     VARCHAR2
404   ) AS
405   /*
406   ||  Created By : smaddali
407   ||  Created On : 18-JUN-2001
408   ||  Purpose : Handles the LOCK mechanism for the table.
409   ||  Known limitations, enhancements or remarks :
410   ||  Change History :
411   ||  Who             When            What
412   ||  (reverse chronological order - newest change first)
413   */
414     CURSOR c1 IS
415       SELECT
416         step_catalog_cd,
417         description,
418         action_days,
419         s_tracking_step_type,
420         publish_ind,
421         closed_ind
422       FROM  igs_tr_step_ctlg_all
423       WHERE rowid = x_rowid
424       FOR UPDATE NOWAIT;
425 
426     tlinfo c1%ROWTYPE;
427 
428   BEGIN
429 
430     OPEN c1;
431     FETCH c1 INTO tlinfo;
432     IF (c1%notfound) THEN
433       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
434       igs_ge_msg_stack.add;
435       CLOSE c1;
436       app_exception.raise_exception;
437       RETURN;
438     END IF;
439     CLOSE c1;
440 
441     IF (
442         (tlinfo.step_catalog_cd = x_step_catalog_cd)
443         AND (tlinfo.description = x_description)
444         AND (tlinfo.action_days = x_action_days)
445         AND (tlinfo.s_tracking_step_type = x_s_tracking_step_type)
446         AND (tlinfo.publish_ind = x_publish_ind)
447         AND (tlinfo.closed_ind = x_closed_ind)
448        ) THEN
449       NULL;
450     ELSE
451       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
452       igs_ge_msg_stack.add;
453       app_exception.raise_exception;
454     END IF;
455 
456     RETURN;
457 
458   END lock_row;
459 
460 
461   PROCEDURE update_row (
462     x_rowid                             IN     VARCHAR2,
463     x_step_catalog_id                   IN     NUMBER,
464     x_step_catalog_cd                   IN     VARCHAR2,
465     x_description                       IN     VARCHAR2,
466     x_action_days                       IN     NUMBER,
467     x_s_tracking_step_type              IN     VARCHAR2,
468     x_publish_ind                       IN     VARCHAR2,
469     x_closed_ind                        IN     VARCHAR2,
470     x_mode                              IN     VARCHAR2 DEFAULT 'R'
471   ) AS
472   /*
473   ||  Created By : smaddali
474   ||  Created On : 18-JUN-2001
475   ||  Purpose : Handles the UPDATE DML logic for the table.
476   ||  Known limitations, enhancements or remarks :
477   ||  Change History :
478   ||  Who             When            What
479   ||  (reverse chronological order - newest change first)
480   */
481     x_last_update_date           DATE ;
482     x_last_updated_by            NUMBER;
483     x_last_update_login          NUMBER;
484 
485   BEGIN
486 
487     x_last_update_date := SYSDATE;
488     IF (X_MODE = 'I') THEN
489       x_last_updated_by := 1;
490       x_last_update_login := 0;
491     ELSIF (x_mode = 'R') THEN
492       x_last_updated_by := fnd_global.user_id;
493       IF x_last_updated_by IS NULL THEN
494         x_last_updated_by := -1;
495       END IF;
496       x_last_update_login := fnd_global.login_id;
497       IF (x_last_update_login IS NULL) THEN
498         x_last_update_login := -1;
499       END IF;
500     ELSE
501       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
502       igs_ge_msg_stack.add;
503       app_exception.raise_exception;
504     END IF;
505 
506     before_dml(
507       p_action                            => 'UPDATE',
508       x_rowid                             => x_rowid,
509       x_step_catalog_id                   => x_step_catalog_id,
510       x_step_catalog_cd                   => x_step_catalog_cd,
511       x_description                       => x_description,
512       x_action_days                       => x_action_days,
513       x_s_tracking_step_type              => x_s_tracking_step_type,
514       x_publish_ind                       => x_publish_ind,
515       x_closed_ind                        => x_closed_ind,
516       x_creation_date                     => x_last_update_date,
517       x_created_by                        => x_last_updated_by,
518       x_last_update_date                  => x_last_update_date,
519       x_last_updated_by                   => x_last_updated_by,
520       x_last_update_login                 => x_last_update_login
521     );
522 
523     UPDATE igs_tr_step_ctlg_all
524       SET
525         step_catalog_cd                   = new_references.step_catalog_cd,
526         description                       = new_references.description,
527         action_days                       = new_references.action_days,
528         s_tracking_step_type              = new_references.s_tracking_step_type,
529         publish_ind                       = new_references.publish_ind,
530         closed_ind                        = new_references.closed_ind,
531         last_update_date                  = x_last_update_date,
532         last_updated_by                   = x_last_updated_by,
533         last_update_login                 = x_last_update_login
534       WHERE rowid = x_rowid;
535 
536     IF (SQL%NOTFOUND) THEN
537       RAISE NO_DATA_FOUND;
538     END IF;
539 
540   END update_row;
541 
542 
543   PROCEDURE add_row (
544     x_rowid                             IN OUT NOCOPY VARCHAR2,
545     x_step_catalog_id                   IN OUT NOCOPY NUMBER,
546     x_step_catalog_cd                   IN     VARCHAR2,
547     x_description                       IN     VARCHAR2,
548     x_action_days                       IN     NUMBER,
549     x_s_tracking_step_type              IN     VARCHAR2,
550     x_publish_ind                       IN     VARCHAR2,
551     x_closed_ind                        IN     VARCHAR2,
552     x_mode                              IN     VARCHAR2 DEFAULT 'R'
553   ) AS
554   /*
555   ||  Created By : smaddali
556   ||  Created On : 18-JUN-2001
557   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
558   ||  Known limitations, enhancements or remarks :
559   ||  Change History :
560   ||  Who             When            What
561   ||  (reverse chronological order - newest change first)
562   */
563     CURSOR c1 IS
564       SELECT   rowid
565       FROM     igs_tr_step_ctlg_all
566       WHERE    step_catalog_id                   = x_step_catalog_id;
567 
568   BEGIN
569 
570     OPEN c1;
571     FETCH c1 INTO x_rowid;
572     IF (c1%NOTFOUND) THEN
573       CLOSE c1;
574 
575       insert_row (
576         x_rowid,
577         x_step_catalog_id,
578         x_step_catalog_cd,
579         x_description,
580         x_action_days,
581         x_s_tracking_step_type,
582         x_publish_ind,
583         x_closed_ind,
584         x_mode
585       );
586       RETURN;
587     END IF;
588     CLOSE c1;
589 
590     update_row (
591       x_rowid,
592       x_step_catalog_id,
593       x_step_catalog_cd,
594       x_description,
595       x_action_days,
596       x_s_tracking_step_type,
597       x_publish_ind,
598       x_closed_ind,
599       x_mode
600     );
601 
602   END add_row;
603 
604 END igs_tr_step_ctlg_pkg;