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_AW_ITEM_PKG

Source


1 PACKAGE BODY igf_aw_item_pkg AS
2 /* $Header: IGFWI02B.pls 115.10 2003/12/05 16:07:01 ugummall ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_aw_item%ROWTYPE;
6   new_references igf_aw_item%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_item_code                         IN     VARCHAR2,
12     x_description                       IN     VARCHAR2,
13     x_active                            IN     VARCHAR2,
14     x_creation_date                     IN     DATE,
15     x_created_by                        IN     NUMBER,
16     x_last_update_date                  IN     DATE,
17     x_last_updated_by                   IN     NUMBER,
18     x_last_update_login                 IN     NUMBER,
19     x_item_category_code                IN     VARCHAR2
20   ) AS
21   /*
22   ||  Created By : avenkatr
23   ||  Created On : 08-NOV-2000
24   ||  Purpose : Initialises the Old and New references for the columns of the table.
25   ||  Known limitations, enhancements or remarks :
26   ||  Change History :
27   ||  Who             When            What
28   ||  (reverse chronological order - newest change first)
29   */
30 
31     CURSOR cur_old_ref_values IS
32       SELECT   *
33       FROM     IGF_AW_ITEM
34       WHERE    rowid = x_rowid;
35 
36   BEGIN
37 
38     l_rowid := x_rowid;
39 
40     -- Code for setting the Old and New Reference Values.
41     -- Populate Old Values.
42     OPEN cur_old_ref_values;
43     FETCH cur_old_ref_values INTO old_references;
44     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45       CLOSE cur_old_ref_values;
46       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47       igs_ge_msg_stack.add;
48       app_exception.raise_exception;
49       RETURN;
50     END IF;
51     CLOSE cur_old_ref_values;
52 
53     -- Populate New Values.
54     new_references.item_code                         := x_item_code;
55     new_references.description                       := x_description;
56     new_references.active                            := x_active;
57     new_references.item_category_code                := x_item_category_code;
58 
59     IF (p_action = 'UPDATE') THEN
60       new_references.creation_date                   := old_references.creation_date;
61       new_references.created_by                      := old_references.created_by;
62     ELSE
63       new_references.creation_date                   := x_creation_date;
64       new_references.created_by                      := x_created_by;
65     END IF;
66 
67     new_references.last_update_date                  := x_last_update_date;
68     new_references.last_updated_by                   := x_last_updated_by;
69     new_references.last_update_login                 := x_last_update_login;
70 
71   END set_column_values;
72 
73 
74   PROCEDURE check_child_existance IS
75   /*
76   ||  Created By : avenkatr
77   ||  Created On : 08-NOV-2000
78   ||  Purpose : Checks for the existance of Child records.
79   ||  Known limitations, enhancements or remarks :
80   ||  Change History :
81   ||  Who             When            What
82   ||  (reverse chronological order - newest change first)
83   */
84   BEGIN
85 
86    igf_aw_coa_grp_item_pkg.get_fk_igf_aw_item (
87       old_references.item_code
88     );
89 
90    igf_aw_coa_items_pkg.get_fk_igf_aw_item(
91       old_references.item_code
92     );
93 
94   END check_child_existance;
95 
96 
97   FUNCTION get_pk_for_validation (
98     x_item_code                         IN     VARCHAR2
99   ) RETURN BOOLEAN AS
100   /*
101   ||  Created By : avenkatr
102   ||  Created On : 08-NOV-2000
103   ||  Purpose : Validates the Primary Key of the table.
104   ||  Known limitations, enhancements or remarks :
105   ||  Change History :2166780
106   ||  Who             When            What
107   ||  adhawan         23-feb-02'      made the primary key incasesensitive by adding the UPPER clause
108   ||  (reverse chronological order - newest change first)
109   */
110     CURSOR cur_rowid IS
111       SELECT   rowid
112       FROM     igf_aw_item
113       WHERE    UPPER(item_code) = UPPER(x_item_code)
114       FOR UPDATE NOWAIT;
115 
116     lv_rowid cur_rowid%RowType;
117 
118   BEGIN
119 
120     OPEN cur_rowid;
121     FETCH cur_rowid INTO lv_rowid;
122     IF (cur_rowid%FOUND) THEN
123       CLOSE cur_rowid;
124       RETURN(TRUE);
125     ELSE
126       CLOSE cur_rowid;
127       RETURN(FALSE);
128     END IF;
129 
130   END get_pk_for_validation;
131 
132 
133   PROCEDURE before_dml (
134     p_action                            IN     VARCHAR2,
135     x_rowid                             IN     VARCHAR2,
136     x_item_code                         IN     VARCHAR2,
137     x_description                       IN     VARCHAR2,
138     x_active                            IN     VARCHAR2,
139     x_creation_date                     IN     DATE,
140     x_created_by                        IN     NUMBER,
141     x_last_update_date                  IN     DATE,
142     x_last_updated_by                   IN     NUMBER,
143     x_last_update_login                 IN     NUMBER,
144     x_item_category_code                IN     VARCHAR2
145   ) AS
146   /*
147   ||  Created By : avenkatr
148   ||  Created On : 08-NOV-2000
149   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
150   ||            Trigger Handlers for the table, before any DML operation.
151   ||  Known limitations, enhancements or remarks :
152   ||  Change History :
153   ||  Who             When            What
154   ||  (reverse chronological order - newest change first)
155   */
156   BEGIN
157 
158     set_column_values (
159       p_action,
160       x_rowid,
161       x_item_code,
162       x_description,
163       x_active,
164       x_creation_date,
165       x_created_by,
166       x_last_update_date,
167       x_last_updated_by,
168       x_last_update_login,
169       x_item_category_code
170     );
171 
172     IF (p_action = 'INSERT') THEN
173       -- Call all the procedures related to Before Insert.
174       IF ( get_pk_for_validation(
175              new_references.item_code
176            )
177          ) THEN
178         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
179         igs_ge_msg_stack.add;
180         app_exception.raise_exception;
181       END IF;
182     ELSIF (p_action = 'DELETE') THEN
183       -- Call all the procedures related to Before Delete.
184       check_child_existance;
185     ELSIF (p_action = 'VALIDATE_INSERT') THEN
186       -- Call all the procedures related to Before Insert.
187       IF ( get_pk_for_validation (
188              new_references.item_code
189            )
190          ) THEN
191         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
192         igs_ge_msg_stack.add;
193         app_exception.raise_exception;
194       END IF;
195     ELSIF (p_action = 'VALIDATE_DELETE') THEN
196       check_child_existance;
197     END IF;
198 
199   END before_dml;
200 
201 
202   PROCEDURE insert_row (
203     x_rowid                             IN OUT NOCOPY VARCHAR2,
204     x_item_code                         IN     VARCHAR2,
205     x_description                       IN     VARCHAR2,
206     x_active                            IN     VARCHAR2,
207     x_mode                              IN     VARCHAR2,
208     x_item_category_code                IN     VARCHAR2
209   ) AS
210   /*
211   ||  Created By : avenkatr
212   ||  Created On : 08-NOV-2000
213   ||  Purpose : Handles the INSERT DML logic for the table.
214   ||  Known limitations, enhancements or remarks :
215   ||  Change History :
216   ||  Who             When            What
217   ||  (reverse chronological order - newest change first)
218   */
219     CURSOR c IS
220       SELECT   rowid
221       FROM     igf_aw_item
222       WHERE    item_code                         = x_item_code;
223 
224     x_last_update_date           DATE;
225     x_last_updated_by            NUMBER;
226     x_last_update_login          NUMBER;
227 
228   BEGIN
229 
230     x_last_update_date := SYSDATE;
231     IF (x_mode = 'I') THEN
232       x_last_updated_by := 1;
233       x_last_update_login := 0;
234     ELSIF (x_mode = 'R') THEN
235       x_last_updated_by := fnd_global.user_id;
236       IF (x_last_updated_by IS NULL) THEN
237         x_last_updated_by := -1;
238       END IF;
239       x_last_update_login := fnd_global.login_id;
240       IF (x_last_update_login IS NULL) THEN
241         x_last_update_login := -1;
242       END IF;
243     ELSE
244       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
245       igs_ge_msg_stack.add;
246       app_exception.raise_exception;
247     END IF;
248 
249     before_dml(
250       p_action                            => 'INSERT',
251       x_rowid                             => x_rowid,
252       x_item_code                         => x_item_code,
253       x_description                       => x_description,
254       x_active                            => x_active,
255       x_creation_date                     => x_last_update_date,
256       x_created_by                        => x_last_updated_by,
257       x_last_update_date                  => x_last_update_date,
258       x_last_updated_by                   => x_last_updated_by,
259       x_last_update_login                 => x_last_update_login,
260       x_item_category_code                => x_item_category_code
261     );
262 
263     INSERT INTO igf_aw_item (
264       item_code,
265       description,
266       active,
267       creation_date,
268       created_by,
269       last_update_date,
270       last_updated_by,
271       last_update_login,
272       item_category_code
273     ) VALUES (
274       new_references.item_code,
275       new_references.description,
276       new_references.active,
277       x_last_update_date,
278       x_last_updated_by,
279       x_last_update_date,
280       x_last_updated_by,
281       x_last_update_login,
282       new_references.item_category_code
283     );
284 
285     OPEN c;
286     FETCH c INTO x_rowid;
287     IF (c%NOTFOUND) THEN
288       CLOSE c;
289       RAISE NO_DATA_FOUND;
290     END IF;
291     CLOSE c;
292 
293   END insert_row;
294 
295 
296   PROCEDURE lock_row (
297     x_rowid                             IN     VARCHAR2,
298     x_item_code                         IN     VARCHAR2,
299     x_description                       IN     VARCHAR2,
300     x_active                            IN     VARCHAR2,
301     x_item_category_code                IN     VARCHAR2
302   ) AS
303   /*
304   ||  Created By : avenkatr
305   ||  Created On : 08-NOV-2000
306   ||  Purpose : Handles the LOCK mechanism for the table.
307   ||  Known limitations, enhancements or remarks :
308   ||  Change History :
309   ||  Who             When            What
310   ||  (reverse chronological order - newest change first)
311   */
312     CURSOR c1 IS
313       SELECT
314         description,
315         active,
316         item_category_code
317       FROM  igf_aw_item
318       WHERE rowid = x_rowid
319       FOR UPDATE NOWAIT;
320 
321     tlinfo c1%ROWTYPE;
322 
323   BEGIN
324 
325     OPEN c1;
326     FETCH c1 INTO tlinfo;
327     IF (c1%notfound) THEN
328       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
329       igs_ge_msg_stack.add;
330       CLOSE c1;
331       app_exception.raise_exception;
332       RETURN;
333     END IF;
334     CLOSE c1;
335 
336     IF (
337         ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
338         AND (tlinfo.active = x_active)
339         AND ((tlinfo.item_category_code = x_item_category_code) OR ((tlinfo.item_category_code IS NULL) AND (x_item_category_code IS NULL)))
340        ) THEN
341       NULL;
342     ELSE
343       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
344       igs_ge_msg_stack.add;
345       app_exception.raise_exception;
346     END IF;
347 
348     RETURN;
349 
350   END lock_row;
351 
352 
353   PROCEDURE update_row (
354     x_rowid                             IN     VARCHAR2,
355     x_item_code                         IN     VARCHAR2,
356     x_description                       IN     VARCHAR2,
357     x_active                            IN     VARCHAR2,
358     x_mode                              IN     VARCHAR2,
359     x_item_category_code                IN     VARCHAR2
360   ) AS
361   /*
362   ||  Created By : avenkatr
363   ||  Created On : 08-NOV-2000
364   ||  Purpose : Handles the UPDATE 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     x_last_update_date           DATE ;
371     x_last_updated_by            NUMBER;
372     x_last_update_login          NUMBER;
373 
374   BEGIN
375 
376     x_last_update_date := SYSDATE;
377     IF (X_MODE = 'I') THEN
378       x_last_updated_by := 1;
379       x_last_update_login := 0;
380     ELSIF (x_mode = 'R') THEN
381       x_last_updated_by := fnd_global.user_id;
382       IF x_last_updated_by IS NULL THEN
383         x_last_updated_by := -1;
384       END IF;
385       x_last_update_login := fnd_global.login_id;
386       IF (x_last_update_login IS NULL) THEN
387         x_last_update_login := -1;
388       END IF;
389     ELSE
390       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
391       igs_ge_msg_stack.add;
392       app_exception.raise_exception;
393     END IF;
394 
395     before_dml(
396       p_action                            => 'UPDATE',
397       x_rowid                             => x_rowid,
398       x_item_code                         => x_item_code,
399       x_description                       => x_description,
400       x_active                            => x_active,
401       x_creation_date                     => x_last_update_date,
402       x_created_by                        => x_last_updated_by,
403       x_last_update_date                  => x_last_update_date,
404       x_last_updated_by                   => x_last_updated_by,
405       x_last_update_login                 => x_last_update_login,
406       x_item_category_code                => x_item_category_code
407     );
408 
409     UPDATE igf_aw_item
410       SET
411         description                       = new_references.description,
412         active                            = new_references.active,
413         last_update_date                  = x_last_update_date,
414         last_updated_by                   = x_last_updated_by,
415         last_update_login                 = x_last_update_login,
416         item_category_code                = new_references.item_category_code
417       WHERE rowid = x_rowid;
418 
419     IF (SQL%NOTFOUND) THEN
420       RAISE NO_DATA_FOUND;
421     END IF;
422 
423   END update_row;
424 
425 
426   PROCEDURE add_row (
427     x_rowid                             IN OUT NOCOPY VARCHAR2,
428     x_item_code                         IN     VARCHAR2,
429     x_description                       IN     VARCHAR2,
430     x_active                            IN     VARCHAR2,
431     x_mode                              IN     VARCHAR2,
432     x_item_category_code                IN     VARCHAR2
433   ) AS
434   /*
435   ||  Created By : avenkatr
436   ||  Created On : 08-NOV-2000
437   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
438   ||  Known limitations, enhancements or remarks :
439   ||  Change History :
440   ||  Who             When            What
441   ||  (reverse chronological order - newest change first)
442   */
443     CURSOR c1 IS
444       SELECT   rowid
445       FROM     igf_aw_item
446       WHERE    item_code                         = x_item_code;
447 
448   BEGIN
449 
450     OPEN c1;
451     FETCH c1 INTO x_rowid;
452     IF (c1%NOTFOUND) THEN
453       CLOSE c1;
454 
455       insert_row (
456         x_rowid,
457         x_item_code,
458         x_description,
459         x_active,
460         x_mode,
461         x_item_category_code
462       );
463       RETURN;
464     END IF;
465     CLOSE c1;
466 
467     update_row (
468       x_rowid,
469       x_item_code,
470       x_description,
471       x_active,
472       x_mode,
473       x_item_category_code
474     );
475 
476   END add_row;
477 
478 
479   PROCEDURE delete_row (
480     x_rowid IN VARCHAR2
481   ) AS
482   /*
483   ||  Created By : avenkatr
484   ||  Created On : 08-NOV-2000
485   ||  Purpose : Handles the DELETE DML logic for the table.
486   ||  Known limitations, enhancements or remarks :
487   ||  Change History :
488   ||  Who             When            What
489   ||  (reverse chronological order - newest change first)
490   */
491   BEGIN
492 
493     before_dml (
494       p_action => 'DELETE',
495       x_rowid => x_rowid
496     );
497 
498     DELETE FROM igf_aw_item
499     WHERE rowid = x_rowid;
500 
501     IF (SQL%NOTFOUND) THEN
502       RAISE NO_DATA_FOUND;
503     END IF;
504 
505   END delete_row;
506 
507 END igf_aw_item_pkg;