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_SYS_RT_CL_FLD_PKG

Source


1 PACKAGE BODY igs_he_sys_rt_cl_fld_pkg AS
2 /* $Header: IGSWI14B.pls 120.1 2006/02/06 21:06:34 anwest noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_he_sys_rt_cl_fld%ROWTYPE;
6   new_references igs_he_sys_rt_cl_fld%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    ,
11     x_system_return_class_type          IN     VARCHAR2    ,
12     x_field_number                      IN     NUMBER      ,
13     x_field_name                        IN     VARCHAR2    ,
14     x_field_description                 IN     VARCHAR2    ,
15     x_datatype                          IN     VARCHAR2    ,
16     x_length                            IN     NUMBER      ,
17     x_mandatory_flag                    IN     VARCHAR2    ,
18     x_closed_flag                       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_SYS_RT_CL_FLD
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.system_return_class_type          := x_system_return_class_type;
59     new_references.field_number                      := x_field_number;
60     new_references.field_name                        := x_field_name;
61     new_references.field_description                 := x_field_description;
62     new_references.datatype                          := x_datatype;
63     new_references.length                            := x_length;
64     new_references.mandatory_flag                    := x_mandatory_flag;
65     new_references.closed_flag                       := x_closed_flag;
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_child_existance IS
83     /*
84     ||  Created By : ANWEST Oracle ADC
85     ||  Created On : 09-JAN-2006
86     ||  Purpose : Checks for the existance of Child records.
87     ||  Known limitations, enhancements or remarks :
88     ||  Change History :
89     ||  Who             When            What
90     ||  (reverse chronological order - newest change first)
91     ||  ANWEST          09-JAN-2006     Changes as per HE305
92     */
93     BEGIN
94 
95       igs_he_sys_rt_cl_ass_pkg.get_fk_igs_he_sys_rt_cl_fld (
96         old_references.system_return_class_type,
97         old_references.field_number
98         );
99 
100     END check_child_existance;
101 
102   PROCEDURE check_parent_existance AS
103   /*
104   ||  Created By : rgopalan
105   ||  Created On : 15-JUN-2001
106   ||  Purpose : Checks for the existance of Parent records.
107   ||  Known limitations, enhancements or remarks :
108   ||  Change History :
109   ||  Who             When            What
110   ||  (reverse chronological order - newest change first)
111   */
112   BEGIN
113 
114     IF (((old_references.system_return_class_type = new_references.system_return_class_type)) OR
115         ((new_references.system_return_class_type IS NULL))) THEN
116       NULL;
117     ELSIF NOT igs_he_sys_rtn_clas_seed_pkg.get_pk_for_validation (
118                 new_references.system_return_class_type
119               ) THEN
120       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
121       igs_ge_msg_stack.add;
122       app_exception.raise_exception;
123     END IF;
124 
125   END check_parent_existance;
126 
127 
128   FUNCTION get_pk_for_validation (
129     x_system_return_class_type          IN     VARCHAR2,
130     x_field_number                      IN     NUMBER
131   ) RETURN BOOLEAN AS
132   /*
133   ||  Created By : rgopalan
134   ||  Created On : 15-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_he_sys_rt_cl_fld
144       WHERE    system_return_class_type = x_system_return_class_type
145       AND      field_number = x_field_number
146       FOR UPDATE NOWAIT;
147 
148     lv_rowid cur_rowid%RowType;
149 
150   BEGIN
151 
152     OPEN cur_rowid;
153     FETCH cur_rowid INTO lv_rowid;
154     IF (cur_rowid%FOUND) THEN
155       CLOSE cur_rowid;
156       RETURN(TRUE);
157     ELSE
158       CLOSE cur_rowid;
159       RETURN(FALSE);
160     END IF;
161 
162   END get_pk_for_validation;
163 
164 
165   PROCEDURE get_fk_igs_he_sys_rtn_clas (
166     x_system_return_class_type          IN     VARCHAR2
167   ) AS
168   /*
169   ||  Created By : rgopalan
170   ||  Created On : 15-JUN-2001
171   ||  Purpose : Validates the Foreign Keys for the table.
172   ||  Known limitations, enhancements or remarks :
173   ||  Change History :
174   ||  Who             When            What
175   ||  (reverse chronological order - newest change first)
176   */
177     CURSOR cur_rowid IS
178       SELECT   rowid
179       FROM     igs_he_sys_rt_cl_fld
180       WHERE   ((system_return_class_type = x_system_return_class_type));
181 
182     lv_rowid cur_rowid%RowType;
183 
184   BEGIN
185 
186     OPEN cur_rowid;
187     FETCH cur_rowid INTO lv_rowid;
188     IF (cur_rowid%FOUND) THEN
189       CLOSE cur_rowid;
190       fnd_message.set_name ('IGS', 'IGS_HE_HESRCFD_HESRCD_FK');
191       igs_ge_msg_stack.add;
192       app_exception.raise_exception;
193       RETURN;
194     END IF;
195     CLOSE cur_rowid;
196 
197   END get_fk_igs_he_sys_rtn_clas;
198 
199 
200   PROCEDURE before_dml (
201     p_action                            IN     VARCHAR2,
202     x_rowid                             IN     VARCHAR2    ,
203     x_system_return_class_type          IN     VARCHAR2    ,
204     x_field_number                      IN     NUMBER      ,
205     x_field_name                        IN     VARCHAR2    ,
206     x_field_description                 IN     VARCHAR2    ,
207     x_datatype                          IN     VARCHAR2    ,
208     x_length                            IN     NUMBER      ,
209     x_mandatory_flag                    IN     VARCHAR2    ,
210     x_closed_flag                       IN     VARCHAR2    ,
211     x_creation_date                     IN     DATE        ,
212     x_created_by                        IN     NUMBER      ,
213     x_last_update_date                  IN     DATE        ,
214     x_last_updated_by                   IN     NUMBER      ,
215     x_last_update_login                 IN     NUMBER
216 ) AS
217   /*
218   ||  Created By : rgopalan
219   ||  Created On : 15-JUN-2001
220   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
221   ||            Trigger Handlers for the table, before any DML operation.
222   ||  Known limitations, enhancements or remarks :
223   ||  Change History :
224   ||  Who             When            What
225   ||  (reverse chronological order - newest change first)
226   */
227   BEGIN
228 
229     set_column_values (
230       p_action,
231       x_rowid,
232       x_system_return_class_type,
233       x_field_number,
234       x_field_name,
235       x_field_description,
236       x_datatype,
237       x_length,
238       x_mandatory_flag,
239       x_closed_flag,
240       x_creation_date,
241       x_created_by,
242       x_last_update_date,
243       x_last_updated_by,
244       x_last_update_login
245     );
246 
247     IF (p_action = 'INSERT') THEN
248       -- Call all the procedures related to Before Insert.
249       IF ( get_pk_for_validation(
250              new_references.system_return_class_type,
251              new_references.field_number
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 = 'VALIDATE_INSERT') THEN
263       -- Call all the procedures related to Before Insert.
264       IF ( get_pk_for_validation (
265              new_references.system_return_class_type,
266              new_references.field_number
267            )
268          ) THEN
269         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
270         igs_ge_msg_stack.add;
271         app_exception.raise_exception;
272       END IF;
273     ELSIF (p_action = 'DELETE') THEN
274       -- Call all the procedures related to Before Delete.
275       check_child_existance;
276     ELSIF (p_action = 'VALIDATE_DELETE') THEN
277       check_child_existance;
278     END IF;
279 
280   END before_dml;
281 
282 
283   PROCEDURE insert_row (
284     x_rowid                             IN OUT NOCOPY VARCHAR2,
285     x_system_return_class_type          IN     VARCHAR2,
286     x_field_number                      IN     NUMBER,
287     x_field_name                        IN     VARCHAR2,
288     x_field_description                 IN     VARCHAR2,
289     x_datatype                          IN     VARCHAR2,
290     x_length                            IN     NUMBER,
291     x_mandatory_flag                    IN     VARCHAR2,
292     x_closed_flag                       IN     VARCHAR2,
293     x_mode                              IN     VARCHAR2
294 ) AS
295   /*
296   ||  Created By : rgopalan
297   ||  Created On : 15-JUN-2001
298   ||  Purpose : Handles the INSERT DML logic for the table.
299   ||  Known limitations, enhancements or remarks :
300   ||  Change History :
301   ||  Who             When            What
302   ||  (reverse chronological order - newest change first)
303   */
304     CURSOR c IS
305       SELECT   rowid
306       FROM     igs_he_sys_rt_cl_fld
307       WHERE    system_return_class_type          = x_system_return_class_type
308       AND      field_number                      = x_field_number;
309 
310     x_last_update_date           DATE;
311     x_last_updated_by            NUMBER;
312     x_last_update_login          NUMBER;
313 
314   BEGIN
315 
316     x_last_update_date := SYSDATE;
317     IF (x_mode = 'I') THEN
318       x_last_updated_by := 1;
319       x_last_update_login := 0;
320     ELSIF (x_mode = 'R') THEN
321       x_last_updated_by := fnd_global.user_id;
322       IF (x_last_updated_by IS NULL) THEN
323         x_last_updated_by := -1;
324       END IF;
325       x_last_update_login := fnd_global.login_id;
326       IF (x_last_update_login IS NULL) THEN
327         x_last_update_login := -1;
328       END IF;
329     ELSE
330       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
331       igs_ge_msg_stack.add;
332       app_exception.raise_exception;
333     END IF;
334 
335     before_dml(
336       p_action                            => 'INSERT',
337       x_rowid                             => x_rowid,
338       x_system_return_class_type          => x_system_return_class_type,
339       x_field_number                      => x_field_number,
340       x_field_name                        => x_field_name,
341       x_field_description                 => x_field_description,
342       x_datatype                          => x_datatype,
343       x_length                            => x_length,
344       x_mandatory_flag                    => x_mandatory_flag,
345       x_closed_flag                       => x_closed_flag,
346       x_creation_date                     => x_last_update_date,
347       x_created_by                        => x_last_updated_by,
348       x_last_update_date                  => x_last_update_date,
349       x_last_updated_by                   => x_last_updated_by,
350       x_last_update_login                 => x_last_update_login
351     );
352 
353     INSERT INTO igs_he_sys_rt_cl_fld (
354       system_return_class_type,
355       field_number,
356       field_name,
357       field_description,
358       datatype,
359       length,
360       mandatory_flag,
361       closed_flag,
362       creation_date,
363       created_by,
364       last_update_date,
365       last_updated_by,
366       last_update_login
367     ) VALUES (
368       new_references.system_return_class_type,
369       new_references.field_number,
370       new_references.field_name,
371       new_references.field_description,
372       new_references.datatype,
373       new_references.length,
374       new_references.mandatory_flag,
375       new_references.closed_flag,
376       x_last_update_date,
377       x_last_updated_by,
378       x_last_update_date,
379       x_last_updated_by,
380       x_last_update_login
381     );
382 
383     OPEN c;
384     FETCH c INTO x_rowid;
385     IF (c%NOTFOUND) THEN
386       CLOSE c;
387       RAISE NO_DATA_FOUND;
388     END IF;
389     CLOSE c;
390 
391   END insert_row;
392 
393 
394   PROCEDURE lock_row (
395     x_rowid                             IN     VARCHAR2,
396     x_system_return_class_type          IN     VARCHAR2,
397     x_field_number                      IN     NUMBER,
398     x_field_name                        IN     VARCHAR2,
399     x_field_description                 IN     VARCHAR2,
400     x_datatype                          IN     VARCHAR2,
401     x_length                            IN     NUMBER,
402     x_mandatory_flag                    IN     VARCHAR2,
406   ||  Created By : rgopalan
403     x_closed_flag                       IN     VARCHAR2
404   ) AS
405   /*
407   ||  Created On : 15-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         field_name,
417         field_description,
418         datatype,
419         length,
420         mandatory_flag,
421         closed_flag
422       FROM  igs_he_sys_rt_cl_fld
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.field_name = x_field_name)
443         AND (tlinfo.field_description = x_field_description)
444         AND (tlinfo.datatype = x_datatype)
445         AND (tlinfo.length = x_length)
446         AND (tlinfo.mandatory_flag = x_mandatory_flag)
447         AND (tlinfo.closed_flag = x_closed_flag)
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_system_return_class_type          IN     VARCHAR2,
464     x_field_number                      IN     NUMBER,
465     x_field_name                        IN     VARCHAR2,
466     x_field_description                 IN     VARCHAR2,
467     x_datatype                          IN     VARCHAR2,
468     x_length                            IN     NUMBER,
469     x_mandatory_flag                    IN     VARCHAR2,
470     x_closed_flag                       IN     VARCHAR2,
471     x_mode                              IN     VARCHAR2
472 ) AS
473   /*
474   ||  Created By : rgopalan
475   ||  Created On : 15-JUN-2001
476   ||  Purpose : Handles the UPDATE DML logic for the table.
477   ||  Known limitations, enhancements or remarks :
478   ||  Change History :
479   ||  Who             When            What
480   ||  (reverse chronological order - newest change first)
481   */
482     x_last_update_date           DATE ;
483     x_last_updated_by            NUMBER;
484     x_last_update_login          NUMBER;
485 
486   BEGIN
487 
488     x_last_update_date := SYSDATE;
489     IF (X_MODE = 'I') THEN
490       x_last_updated_by := 1;
491       x_last_update_login := 0;
492     ELSIF (x_mode = 'R') THEN
493       x_last_updated_by := fnd_global.user_id;
494       IF x_last_updated_by IS NULL THEN
495         x_last_updated_by := -1;
496       END IF;
497       x_last_update_login := fnd_global.login_id;
498       IF (x_last_update_login IS NULL) THEN
499         x_last_update_login := -1;
500       END IF;
501     ELSE
502       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
503       igs_ge_msg_stack.add;
504       app_exception.raise_exception;
505     END IF;
506 
507     before_dml(
508       p_action                            => 'UPDATE',
509       x_rowid                             => x_rowid,
510       x_system_return_class_type          => x_system_return_class_type,
511       x_field_number                      => x_field_number,
512       x_field_name                        => x_field_name,
513       x_field_description                 => x_field_description,
514       x_datatype                          => x_datatype,
515       x_length                            => x_length,
516       x_mandatory_flag                    => x_mandatory_flag,
517       x_closed_flag                       => x_closed_flag,
518       x_creation_date                     => x_last_update_date,
519       x_created_by                        => x_last_updated_by,
520       x_last_update_date                  => x_last_update_date,
521       x_last_updated_by                   => x_last_updated_by,
522       x_last_update_login                 => x_last_update_login
523     );
524 
525     UPDATE igs_he_sys_rt_cl_fld
526       SET
527         field_name                        = new_references.field_name,
528         field_description                 = new_references.field_description,
529         datatype                          = new_references.datatype,
530         length                            = new_references.length,
531         mandatory_flag                    = new_references.mandatory_flag,
532         closed_flag                       = new_references.closed_flag,
533         last_update_date                  = x_last_update_date,
534         last_updated_by                   = x_last_updated_by,
535         last_update_login                 = x_last_update_login
536       WHERE rowid = x_rowid;
537 
538     IF (SQL%NOTFOUND) THEN
539       RAISE NO_DATA_FOUND;
540     END IF;
541 
542   END update_row;
543 
544 
545   PROCEDURE add_row (
546     x_rowid                             IN OUT NOCOPY VARCHAR2,
547     x_system_return_class_type          IN     VARCHAR2,
548     x_field_number                      IN     NUMBER,
549     x_field_name                        IN     VARCHAR2,
550     x_field_description                 IN     VARCHAR2,
551     x_datatype                          IN     VARCHAR2,
552     x_length                            IN     NUMBER,
553     x_mandatory_flag                    IN     VARCHAR2,
554     x_closed_flag                       IN     VARCHAR2,
555     x_mode                              IN     VARCHAR2
556 ) AS
557   /*
558   ||  Created By : rgopalan
559   ||  Created On : 15-JUN-2001
560   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
561   ||  Known limitations, enhancements or remarks :
562   ||  Change History :
563   ||  Who             When            What
564   ||  (reverse chronological order - newest change first)
565   */
566     CURSOR c1 IS
567       SELECT   rowid
568       FROM     igs_he_sys_rt_cl_fld
569       WHERE    system_return_class_type          = x_system_return_class_type
570       AND      field_number                      = x_field_number;
571 
572   BEGIN
573 
574     OPEN c1;
575     FETCH c1 INTO x_rowid;
576     IF (c1%NOTFOUND) THEN
577       CLOSE c1;
578 
579       insert_row (
580         x_rowid,
581         x_system_return_class_type,
582         x_field_number,
583         x_field_name,
584         x_field_description,
585         x_datatype,
586         x_length,
587         x_mandatory_flag,
588         x_closed_flag,
589         x_mode
590       );
591       RETURN;
592     END IF;
593     CLOSE c1;
594 
595     update_row (
596       x_rowid,
597       x_system_return_class_type,
598       x_field_number,
599       x_field_name,
600       x_field_description,
601       x_datatype,
602       x_length,
603       x_mandatory_flag,
604       x_closed_flag,
605       x_mode
606     );
607 
608   END add_row;
609 
610 
611   PROCEDURE delete_row (
612     x_rowid IN VARCHAR2
613   ) AS
614   /*
615   ||  Created By : rgopalan
616   ||  Created On : 15-JUN-2001
617   ||  Purpose : Handles the DELETE DML logic for the table.
618   ||  Known limitations, enhancements or remarks :
619   ||  Change History :
620   ||  Who             When            What
621   ||  (reverse chronological order - newest change first)
622   */
623   BEGIN
624 
625     before_dml (
626       p_action => 'DELETE',
627       x_rowid => x_rowid
628     );
629 
630     DELETE FROM igs_he_sys_rt_cl_fld
631     WHERE rowid = x_rowid;
632 
633     IF (SQL%NOTFOUND) THEN
634       RAISE NO_DATA_FOUND;
635     END IF;
636 
637   END delete_row;
638 
639 
640 END igs_he_sys_rt_cl_fld_pkg;