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_OR_FUNC_FLTR_PKG

Source


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