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_HE_USR_RTN_CLAS_PKG

Source


1 PACKAGE BODY igs_he_usr_rtn_clas_pkg AS
2 /* $Header: IGSWI15B.pls 115.7 2002/12/20 08:53:20 bayadav noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_he_usr_rtn_clas%ROWTYPE;
6   new_references igs_he_usr_rtn_clas%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    ,
11     x_usr_rtn_clas_id                   IN     NUMBER      ,
12     x_system_return_class_type          IN     VARCHAR2    ,
13     x_user_return_subclass              IN     VARCHAR2    ,
14     x_description                       IN     VARCHAR2    ,
15     x_record_id                         IN     VARCHAR2    ,
16     x_rec_id_seg1                       IN     VARCHAR2    ,
17     x_rec_id_seg2                       IN     VARCHAR2    ,
18     x_rec_id_seg3                       IN     VARCHAR2    ,
19     x_creation_date                     IN     DATE        ,
20     x_created_by                        IN     NUMBER      ,
21     x_last_update_date                  IN     DATE        ,
22     x_last_updated_by                   IN     NUMBER      ,
23     x_last_update_login                 IN     NUMBER
24   ) AS
25   /*
26   ||  Created By : rgopalan
27   ||  Created On : 15-JUN-2001
28   ||  Purpose : Initialises the Old and New references for the columns of the table.
29   ||  Known limitations, enhancements or remarks :
30   ||  Change History :
31   ||  Who             When            What
32   ||  (reverse chronological order - newest change first)
33   */
34 
35     CURSOR cur_old_ref_values IS
36       SELECT   *
37       FROM     IGS_HE_USR_RTN_CLAS
38       WHERE    rowid = x_rowid;
39 
40   BEGIN
41 
42     l_rowid := x_rowid;
43 
44     -- Code for setting the Old and New Reference Values.
45     -- Populate Old Values.
46     OPEN cur_old_ref_values;
47     FETCH cur_old_ref_values INTO old_references;
48     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
49       CLOSE cur_old_ref_values;
50       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
51       igs_ge_msg_stack.add;
52       app_exception.raise_exception;
53       RETURN;
54     END IF;
55     CLOSE cur_old_ref_values;
56 
57     -- Populate New Values.
58     new_references.usr_rtn_clas_id                   := x_usr_rtn_clas_id;
59     new_references.system_return_class_type          := x_system_return_class_type;
60     new_references.user_return_subclass              := x_user_return_subclass;
61     new_references.description                       := x_description;
62     new_references.record_id                         := x_record_id;
63     new_references.rec_id_seg1                       := x_rec_id_seg1;
64     new_references.rec_id_seg2                       := x_rec_id_seg2;
65     new_references.rec_id_seg3                       := x_rec_id_seg3;
66 
67     IF (p_action = 'UPDATE') THEN
68       new_references.creation_date                   := old_references.creation_date;
69       new_references.created_by                      := old_references.created_by;
70     ELSE
71       new_references.creation_date                   := x_creation_date;
72       new_references.created_by                      := x_created_by;
73     END IF;
74 
75     new_references.last_update_date                  := x_last_update_date;
76     new_references.last_updated_by                   := x_last_updated_by;
77     new_references.last_update_login                 := x_last_update_login;
78 
79   END set_column_values;
80 
81 
82   PROCEDURE check_parent_existance AS
83   /*
84   ||  Created By : rgopalan
85   ||  Created On : 15-JUN-2001
86   ||  Purpose : Checks for the existance of Parent records.
87   ||  Known limitations, enhancements or remarks :
88   ||  Change History :
89   ||  Who             When            What
90   ||  (reverse chronological order - newest change first)
91   */
92   BEGIN
93 
94     IF (((old_references.system_return_class_type = new_references.system_return_class_type)) OR
95         ((new_references.system_return_class_type IS NULL))) THEN
96       NULL;
97     ELSIF NOT igs_he_sys_rtn_clas_pkg.get_pk_for_validation (
98                 new_references.system_return_class_type
99               ) THEN
100       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
101       igs_ge_msg_stack.add;
102       app_exception.raise_exception;
103     END IF;
104 
105   END check_parent_existance;
106 
107 
108   PROCEDURE check_child_existance IS
109   /*
110   ||  Created By : rgopalan
111   ||  Created On : 15-JUN-2001
112   ||  Purpose : Checks for the existance of Child records.
113   ||  Known limitations, enhancements or remarks :
114   ||  Change History :
115   ||  Who             When            What
116   ||  (reverse chronological order - newest change first)
117   */
118   BEGIN
119 
120     igs_he_submsn_return_pkg.get_fk_igs_he_usr_rtn_clas (
121       old_references.user_return_subclass
122     );
123 
124     igs_he_usr_rt_cl_fld_pkg.get_fk_igs_he_usr_rtn_clas (
125       old_references.user_return_subclass
126     );
127 
128   END check_child_existance;
129 
130 
131   FUNCTION get_pk_for_validation (
132     x_user_return_subclass              IN     VARCHAR2
133   ) RETURN BOOLEAN AS
134   /*
135   ||  Created By : rgopalan
136   ||  Created On : 15-JUN-2001
137   ||  Purpose : Validates the Primary Key of the table.
138   ||  Known limitations, enhancements or remarks :
139   ||  Change History :
140   ||  Who             When            What
141   ||  (reverse chronological order - newest change first)
142   */
143     CURSOR cur_rowid IS
144       SELECT   rowid
145       FROM     igs_he_usr_rtn_clas
146       WHERE    user_return_subclass = x_user_return_subclass
147       FOR UPDATE NOWAIT;
148 
149     lv_rowid cur_rowid%RowType;
150 
151   BEGIN
152 
153     OPEN cur_rowid;
154     FETCH cur_rowid INTO lv_rowid;
155     IF (cur_rowid%FOUND) THEN
156       CLOSE cur_rowid;
157       RETURN(TRUE);
158     ELSE
159       CLOSE cur_rowid;
160       RETURN(FALSE);
161     END IF;
162 
163   END get_pk_for_validation;
164 
165 
166   PROCEDURE get_fk_igs_he_sys_rtn_clas (
167     x_system_return_class_type          IN     VARCHAR2
168   ) AS
169   /*
170   ||  Created By : rgopalan
171   ||  Created On : 15-JUN-2001
172   ||  Purpose : Validates the Foreign Keys for the table.
173   ||  Known limitations, enhancements or remarks :
174   ||  Change History :
175   ||  Who             When            What
176   ||  (reverse chronological order - newest change first)
177   */
178     CURSOR cur_rowid IS
179       SELECT   rowid
180       FROM     igs_he_usr_rtn_clas
181       WHERE   ((system_return_class_type = x_system_return_class_type));
182 
183     lv_rowid cur_rowid%RowType;
184 
185   BEGIN
186 
187     OPEN cur_rowid;
188     FETCH cur_rowid INTO lv_rowid;
189     IF (cur_rowid%FOUND) THEN
190       CLOSE cur_rowid;
191       fnd_message.set_name ('IGS', 'IGS_HE_HEURCD_HESRCD_FK');
192       igs_ge_msg_stack.add;
193       app_exception.raise_exception;
194       RETURN;
195     END IF;
196     CLOSE cur_rowid;
197 
198   END get_fk_igs_he_sys_rtn_clas;
199 
200 
201   PROCEDURE before_dml (
202     p_action                            IN     VARCHAR2,
203     x_rowid                             IN     VARCHAR2    ,
204     x_usr_rtn_clas_id                   IN     NUMBER      ,
205     x_system_return_class_type          IN     VARCHAR2    ,
206     x_user_return_subclass              IN     VARCHAR2    ,
207     x_description                       IN     VARCHAR2    ,
208     x_record_id                         IN     VARCHAR2    ,
209     x_rec_id_seg1                       IN     VARCHAR2    ,
210     x_rec_id_seg2                       IN     VARCHAR2    ,
211     x_rec_id_seg3                       IN     VARCHAR2    ,
212     x_creation_date                     IN     DATE        ,
213     x_created_by                        IN     NUMBER      ,
214     x_last_update_date                  IN     DATE        ,
215     x_last_updated_by                   IN     NUMBER      ,
216     x_last_update_login                 IN     NUMBER
217   ) AS
218   /*
219   ||  Created By : rgopalan
220   ||  Created On : 15-JUN-2001
221   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
222   ||            Trigger Handlers for the table, before any DML operation.
223   ||  Known limitations, enhancements or remarks :
224   ||  Change History :
225   ||  Who             When            What
226   ||  (reverse chronological order - newest change first)
227   */
228   BEGIN
229 
230     set_column_values (
231       p_action,
232       x_rowid,
233       x_usr_rtn_clas_id,
234       x_system_return_class_type,
235       x_user_return_subclass,
236       x_description,
237       x_record_id,
238       x_rec_id_seg1,
239       x_rec_id_seg2,
240       x_rec_id_seg3,
241       x_creation_date,
242       x_created_by,
243       x_last_update_date,
244       x_last_updated_by,
245       x_last_update_login
246     );
247 
248     IF (p_action = 'INSERT') THEN
249       -- Call all the procedures related to Before Insert.
250       IF ( get_pk_for_validation(
251              new_references.user_return_subclass
252            )
253          ) THEN
254         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
255         igs_ge_msg_stack.add;
256         app_exception.raise_exception;
257       END IF;
258       check_parent_existance;
259     ELSIF (p_action = 'UPDATE') THEN
260       -- Call all the procedures related to Before Update.
261       check_parent_existance;
262     ELSIF (p_action = 'DELETE') THEN
263       -- Call all the procedures related to Before Delete.
264       check_child_existance;
265     ELSIF (p_action = 'VALIDATE_INSERT') THEN
266       -- Call all the procedures related to Before Insert.
267       IF ( get_pk_for_validation (
268              new_references.user_return_subclass
269            )
270          ) THEN
271         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
272         igs_ge_msg_stack.add;
273         app_exception.raise_exception;
274       END IF;
275     ELSIF (p_action = 'VALIDATE_DELETE') THEN
276       check_child_existance;
277     END IF;
278 
279   END before_dml;
280 
281 
282   PROCEDURE insert_row (
283     x_rowid                             IN OUT NOCOPY VARCHAR2,
284     x_usr_rtn_clas_id                   IN OUT NOCOPY NUMBER,
285     x_system_return_class_type          IN     VARCHAR2,
286     x_user_return_subclass              IN     VARCHAR2,
287     x_description                       IN     VARCHAR2,
288     x_record_id                         IN     VARCHAR2,
289     x_rec_id_seg1                       IN     VARCHAR2,
290     x_rec_id_seg2                       IN     VARCHAR2,
291     x_rec_id_seg3                       IN     VARCHAR2,
292     x_mode                              IN     VARCHAR2
293   ) AS
294   /*
295   ||  Created By : rgopalan
296   ||  Created On : 15-JUN-2001
297   ||  Purpose : Handles the INSERT DML logic for the table.
298   ||  Known limitations, enhancements or remarks :
299   ||  Change History :
300   ||  Who             When            What
301   ||  (reverse chronological order - newest change first)
302   */
303     CURSOR c IS
304       SELECT   rowid
305       FROM     igs_he_usr_rtn_clas
306       WHERE    user_return_subclass              = x_user_return_subclass;
307 
308     x_last_update_date           DATE;
309     x_last_updated_by            NUMBER;
310     x_last_update_login          NUMBER;
311 
312   BEGIN
313 
314     x_last_update_date := SYSDATE;
315     IF (x_mode = 'I') THEN
316       x_last_updated_by := 1;
317       x_last_update_login := 0;
318     ELSIF (x_mode = 'R') THEN
319       x_last_updated_by := fnd_global.user_id;
320       IF (x_last_updated_by IS NULL) THEN
321         x_last_updated_by := -1;
322       END IF;
323       x_last_update_login := fnd_global.login_id;
324       IF (x_last_update_login IS NULL) THEN
325         x_last_update_login := -1;
326       END IF;
327     ELSE
328       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
329       igs_ge_msg_stack.add;
330       app_exception.raise_exception;
331     END IF;
332 
333     SELECT    igs_he_usr_rtn_clas_s.NEXTVAL
334     INTO      x_usr_rtn_clas_id
335     FROM      dual;
336 
337     before_dml(
338       p_action                            => 'INSERT',
339       x_rowid                             => x_rowid,
340       x_usr_rtn_clas_id                   => x_usr_rtn_clas_id,
341       x_system_return_class_type          => x_system_return_class_type,
342       x_user_return_subclass              => x_user_return_subclass,
343       x_description                       => x_description,
344       x_record_id                         => x_record_id,
345       x_rec_id_seg1                       => x_rec_id_seg1,
346       x_rec_id_seg2                       => x_rec_id_seg2,
347       x_rec_id_seg3                       => x_rec_id_seg3,
348       x_creation_date                     => x_last_update_date,
349       x_created_by                        => x_last_updated_by,
350       x_last_update_date                  => x_last_update_date,
351       x_last_updated_by                   => x_last_updated_by,
352       x_last_update_login                 => x_last_update_login
353     );
354 
355     INSERT INTO igs_he_usr_rtn_clas (
356       usr_rtn_clas_id,
357       system_return_class_type,
358       user_return_subclass,
359       description,
360       record_id,
361       rec_id_seg1,
362       rec_id_seg2,
363       rec_id_seg3,
364       creation_date,
365       created_by,
366       last_update_date,
367       last_updated_by,
368       last_update_login
369     ) VALUES (
370       new_references.usr_rtn_clas_id,
371       new_references.system_return_class_type,
372       new_references.user_return_subclass,
373       new_references.description,
374       new_references.record_id,
375       new_references.rec_id_seg1,
376       new_references.rec_id_seg2,
377       new_references.rec_id_seg3,
378       x_last_update_date,
379       x_last_updated_by,
380       x_last_update_date,
381       x_last_updated_by,
382       x_last_update_login
383     );
384 
385     OPEN c;
386     FETCH c INTO x_rowid;
387     IF (c%NOTFOUND) THEN
388       CLOSE c;
389       RAISE NO_DATA_FOUND;
390     END IF;
391     CLOSE c;
392 
393   END insert_row;
394 
395 
396   PROCEDURE lock_row (
397     x_rowid                             IN     VARCHAR2,
398     x_usr_rtn_clas_id                   IN     NUMBER,
399     x_system_return_class_type          IN     VARCHAR2,
400     x_user_return_subclass              IN     VARCHAR2,
401     x_description                       IN     VARCHAR2,
402     x_record_id                         IN     VARCHAR2,
403     x_rec_id_seg1                       IN     VARCHAR2,
404     x_rec_id_seg2                       IN     VARCHAR2,
405     x_rec_id_seg3                       IN     VARCHAR2
406   ) AS
407   /*
408   ||  Created By : rgopalan
409   ||  Created On : 15-JUN-2001
410   ||  Purpose : Handles the LOCK mechanism for the table.
411   ||  Known limitations, enhancements or remarks :
412   ||  Change History :
413   ||  Who             When            What
414   ||  (reverse chronological order - newest change first)
415   */
416     CURSOR c1 IS
417       SELECT
418         usr_rtn_clas_id,
419         system_return_class_type,
420         description,
421         record_id,
422         rec_id_seg1,
423         rec_id_seg2,
424         rec_id_seg3
425       FROM  igs_he_usr_rtn_clas
426       WHERE rowid = x_rowid
427       FOR UPDATE NOWAIT;
428 
429     tlinfo c1%ROWTYPE;
430 
431   BEGIN
432 
433     OPEN c1;
434     FETCH c1 INTO tlinfo;
435     IF (c1%notfound) THEN
436       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
437       igs_ge_msg_stack.add;
438       CLOSE c1;
439       app_exception.raise_exception;
440       RETURN;
441     END IF;
442     CLOSE c1;
443 
444     IF (
445         (tlinfo.usr_rtn_clas_id = x_usr_rtn_clas_id)
446         AND (tlinfo.system_return_class_type = x_system_return_class_type)
447         AND (tlinfo.description = x_description)
448         AND (tlinfo.record_id = x_record_id)
449         AND (tlinfo.rec_id_seg1 = x_rec_id_seg1)
450         AND (tlinfo.rec_id_seg2 = x_rec_id_seg2)
451         AND (tlinfo.rec_id_seg3 = x_rec_id_seg3)
452        ) THEN
453       NULL;
454     ELSE
455       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
456       igs_ge_msg_stack.add;
457       app_exception.raise_exception;
458     END IF;
459 
460     RETURN;
461 
462   END lock_row;
463 
464 
465   PROCEDURE update_row (
466     x_rowid                             IN     VARCHAR2,
467     x_usr_rtn_clas_id                   IN     NUMBER,
468     x_system_return_class_type          IN     VARCHAR2,
469     x_user_return_subclass              IN     VARCHAR2,
470     x_description                       IN     VARCHAR2,
471     x_record_id                         IN     VARCHAR2,
472     x_rec_id_seg1                       IN     VARCHAR2,
473     x_rec_id_seg2                       IN     VARCHAR2,
474     x_rec_id_seg3                       IN     VARCHAR2,
475     x_mode                              IN     VARCHAR2
476   ) AS
477   /*
478   ||  Created By : rgopalan
479   ||  Created On : 15-JUN-2001
480   ||  Purpose : Handles the UPDATE DML logic for the table.
481   ||  Known limitations, enhancements or remarks :
482   ||  Change History :
483   ||  Who             When            What
484   ||  (reverse chronological order - newest change first)
485   */
486     x_last_update_date           DATE ;
487     x_last_updated_by            NUMBER;
488     x_last_update_login          NUMBER;
489 
490   BEGIN
491 
492     x_last_update_date := SYSDATE;
493     IF (X_MODE = 'I') THEN
494       x_last_updated_by := 1;
495       x_last_update_login := 0;
496     ELSIF (x_mode = 'R') THEN
497       x_last_updated_by := fnd_global.user_id;
498       IF x_last_updated_by IS NULL THEN
499         x_last_updated_by := -1;
500       END IF;
501       x_last_update_login := fnd_global.login_id;
502       IF (x_last_update_login IS NULL) THEN
503         x_last_update_login := -1;
504       END IF;
505     ELSE
506       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
507       igs_ge_msg_stack.add;
508       app_exception.raise_exception;
509     END IF;
510 
511     before_dml(
512       p_action                            => 'UPDATE',
513       x_rowid                             => x_rowid,
514       x_usr_rtn_clas_id                   => x_usr_rtn_clas_id,
515       x_system_return_class_type          => x_system_return_class_type,
516       x_user_return_subclass              => x_user_return_subclass,
517       x_description                       => x_description,
518       x_record_id                         => x_record_id,
519       x_rec_id_seg1                       => x_rec_id_seg1,
520       x_rec_id_seg2                       => x_rec_id_seg2,
521       x_rec_id_seg3                       => x_rec_id_seg3,
522       x_creation_date                     => x_last_update_date,
523       x_created_by                        => x_last_updated_by,
524       x_last_update_date                  => x_last_update_date,
525       x_last_updated_by                   => x_last_updated_by,
526       x_last_update_login                 => x_last_update_login
527     );
528 
529     UPDATE igs_he_usr_rtn_clas
530       SET
531         usr_rtn_clas_id                   = new_references.usr_rtn_clas_id,
532         system_return_class_type          = new_references.system_return_class_type,
533         description                       = new_references.description,
534         record_id                         = new_references.record_id,
535         rec_id_seg1                       = new_references.rec_id_seg1,
536         rec_id_seg2                       = new_references.rec_id_seg2,
537         rec_id_seg3                       = new_references.rec_id_seg3,
538         last_update_date                  = x_last_update_date,
539         last_updated_by                   = x_last_updated_by,
540         last_update_login                 = x_last_update_login
541       WHERE rowid = x_rowid;
542 
543     IF (SQL%NOTFOUND) THEN
544       RAISE NO_DATA_FOUND;
545     END IF;
546 
547   END update_row;
548 
549 
550   PROCEDURE add_row (
551     x_rowid                             IN OUT NOCOPY VARCHAR2,
552     x_usr_rtn_clas_id                   IN OUT NOCOPY NUMBER,
553     x_system_return_class_type          IN     VARCHAR2,
554     x_user_return_subclass              IN     VARCHAR2,
555     x_description                       IN     VARCHAR2,
556     x_record_id                         IN     VARCHAR2,
557     x_rec_id_seg1                       IN     VARCHAR2,
558     x_rec_id_seg2                       IN     VARCHAR2,
559     x_rec_id_seg3                       IN     VARCHAR2,
560     x_mode                              IN     VARCHAR2
561   ) AS
562   /*
563   ||  Created By : rgopalan
564   ||  Created On : 15-JUN-2001
565   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
566   ||  Known limitations, enhancements or remarks :
567   ||  Change History :
568   ||  Who             When            What
569   ||  (reverse chronological order - newest change first)
570   */
571     CURSOR c1 IS
572       SELECT   rowid
573       FROM     igs_he_usr_rtn_clas
574       WHERE    user_return_subclass              = x_user_return_subclass;
575 
576   BEGIN
577 
578     OPEN c1;
579     FETCH c1 INTO x_rowid;
580     IF (c1%NOTFOUND) THEN
581       CLOSE c1;
582 
583       insert_row (
584         x_rowid,
585         x_usr_rtn_clas_id,
586         x_system_return_class_type,
587         x_user_return_subclass,
588         x_description,
589         x_record_id,
590         x_rec_id_seg1,
591         x_rec_id_seg2,
592         x_rec_id_seg3,
593         x_mode
594       );
595       RETURN;
596     END IF;
597     CLOSE c1;
598 
599     update_row (
600       x_rowid,
601       x_usr_rtn_clas_id,
602       x_system_return_class_type,
603       x_user_return_subclass,
604       x_description,
605       x_record_id,
606       x_rec_id_seg1,
607       x_rec_id_seg2,
608       x_rec_id_seg3,
609       x_mode
610     );
611 
612   END add_row;
613 
614 
615   PROCEDURE delete_row (
616     x_rowid IN VARCHAR2
617   ) AS
618   /*
619   ||  Created By : rgopalan
620   ||  Created On : 15-JUN-2001
621   ||  Purpose : Handles the DELETE DML logic for the table.
622   ||  Known limitations, enhancements or remarks :
623   ||  Change History :
624   ||  Who             When            What
625   ||  (reverse chronological order - newest change first)
626   */
627   BEGIN
628 
629     before_dml (
630       p_action => 'DELETE',
631       x_rowid => x_rowid
632     );
633 
634     DELETE FROM igs_he_usr_rtn_clas
635     WHERE rowid = x_rowid;
636 
637     IF (SQL%NOTFOUND) THEN
638       RAISE NO_DATA_FOUND;
639     END IF;
640 
641   END delete_row;
642 
643 
644 END igs_he_usr_rtn_clas_pkg;