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.IGF_AP_INST_VER_ITEM_PKG

Source


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