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

Source


1 PACKAGE BODY igi_rpi_audit_items_all_pkg AS
2 /* $Header: igiraiab.pls 120.4.12000000.1 2007/08/31 05:52:29 mbremkum noship $ */
3 
4   l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 
6   l_state_level number:=FND_LOG.LEVEL_STATEMENT;
7   l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
8   l_event_level number:=FND_LOG.LEVEL_EVENT;
9   l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
10   l_error_level number:=FND_LOG.LEVEL_ERROR;
11   l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
12 
13   l_rowid VARCHAR2(25);
14   old_references igi_rpi_audit_items_all%ROWTYPE;
15   new_references igi_rpi_audit_items_all%ROWTYPE;
16 
17   PROCEDURE set_column_values (
18     p_action                            IN     VARCHAR2,
19     x_rowid                             IN     VARCHAR2,
20     x_item_id                           IN     NUMBER,
21     x_price                             IN     NUMBER,
22     x_effective_date                    IN     DATE,
23     x_revised_effective_date            IN     DATE,
24     x_revised_price                     IN     NUMBER,
25     x_run_id                            IN     NUMBER,
26     x_org_id                            IN     NUMBER,
27     x_creation_date                     IN     DATE,
28     x_created_by                        IN     NUMBER,
29     x_last_update_date                  IN     DATE,
30     x_last_updated_by                   IN     NUMBER,
31     x_last_update_login                 IN     NUMBER ) AS
32   /*
33   ||  Created By : panaraya
34   ||  Created On : 06-MAR-2002
35   ||  Purpose : Initialises the Old and New references for the columns of the table.
36   ||  Known limitations, enhancements or remarks :
37   ||  Change History :
38   ||  Who             When            What
39   ||  (reverse chronological order - newest change first)
40   */
41 
42     CURSOR cur_old_ref_values IS
43       SELECT   *
44       FROM     IGI_RPI_AUDIT_ITEMS_ALL
45       WHERE    rowid = x_rowid;
46 
47   BEGIN
48 
49     l_rowid := x_rowid;
50 
51     -- Code for setting the Old and New Reference Values.
52     -- Populate Old Values.
53     OPEN cur_old_ref_values;
54     FETCH cur_old_ref_values INTO old_references;
55     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
56       CLOSE cur_old_ref_values;
57       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
58 
59       --Bug 3199481 (start)
60       If (l_unexp_level >= l_debug_level) then
61          FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_audit_items_all_pkg.set_column_values.Msg1',FALSE);
62       End if;
63       --Bug 3199481 (end)
64       app_exception.raise_exception;
65       RETURN;
66     END IF;
67     CLOSE cur_old_ref_values;
68 
69     -- Populate New Values.
70     new_references.item_id                           := x_item_id;
71     new_references.price                             := x_price;
72     new_references.effective_date                    := x_effective_date;
73     new_references.revised_effective_date            := x_revised_effective_date;
74     new_references.revised_price                     := x_revised_price;
75     new_references.run_id                            := x_run_id;
76     new_references.org_id                            := x_org_id;
77 
78     IF (p_action = 'UPDATE') THEN
79       new_references.creation_date                   := old_references.creation_date;
80       new_references.created_by                      := old_references.created_by;
81     ELSE
82       new_references.creation_date                   := x_creation_date;
83       new_references.created_by                      := x_created_by;
84     END IF;
85 
86     new_references.last_update_date                  := x_last_update_date;
87     new_references.last_updated_by                   := x_last_updated_by;
88     new_references.last_update_login                 := x_last_update_login;
89 
90   END set_column_values;
91 
92 
93   PROCEDURE check_parent_existance(x_item_id in number) AS
94   /*
95   ||  Created By : panaraya
96   ||  Created On : 06-MAR-2002
97   ||  Purpose : Checks for the existance of Parent records.
98   ||  Known limitations, enhancements or remarks :
99   ||  Change History :
100   ||  Who             When            What
101   ||  (reverse chronological order - newest change first)
102   */
103 
104   CURSOR c is select 'X' from igi_rpi_items_all where item_id=x_item_id;
105 
106  l c%rowtype;
107   BEGIN
108     open c;
109     fetch c into l;
110 
111     IF (((old_references.item_id = new_references.item_id)) OR
112         ((new_references.item_id IS NULL))) THEN
113       NULL;
114     ELSIF c%NOTFOUND
115               THEN
116       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
117 
118       --Bug 3199481 (start)
119       If (l_unexp_level >= l_debug_level) then
120          FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_audit_items_all_pkg.check_parent_existence.Msg1',FALSE);
121       End if;
122       --Bug 3199481 (end)
123       app_exception.raise_exception;
124     END IF;
125 
126   END check_parent_existance;
127 
128 
129 
130   PROCEDURE before_dml (
131     p_action                            IN     VARCHAR2,
132     x_rowid                             IN     VARCHAR2,
133     x_item_id                           IN     NUMBER,
134     x_price                             IN     NUMBER,
135     x_effective_date                    IN     DATE,
136     x_revised_effective_date            IN     DATE,
137     x_revised_price                     IN     NUMBER,
138     x_run_id                            IN     NUMBER,
139     x_org_id                            IN     NUMBER,
140     x_creation_date                     IN     DATE,
141     x_created_by                        IN     NUMBER,
142     x_last_update_date                  IN     DATE,
143     x_last_updated_by                   IN     NUMBER,
144     x_last_update_login                 IN     NUMBER ) AS
145   /*
146   ||  Created By : panaraya
147   ||  Created On : 06-MAR-2002
148   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
149   ||            Trigger Handlers for the table, before any DML operation.
150   ||  Known limitations, enhancements or remarks :
151   ||  Change History :
152   ||  Who             When            What
153   ||  (reverse chronological order - newest change first)
154   */
155   BEGIN
156   set_column_values (
157       p_action,
158       x_rowid,
159       x_item_id,
160       x_price,
161       x_effective_date,
162       x_revised_effective_date,
163       x_revised_price,
164       x_run_id,
165       x_org_id,
166       x_creation_date,
167       x_created_by,
168       x_last_update_date,
169       x_last_updated_by,
170       x_last_update_login
171     );
172 
173     IF (p_action = 'INSERT') THEN
174       -- Call all the procedures related to Before Insert.
175       check_parent_existance(x_item_id);
176     ELSIF (p_action = 'UPDATE') THEN
177       -- Call all the procedures related to Before Update.
178       check_parent_existance(x_item_id);
179     ELSIF (p_action = 'VALIDATE_INSERT') THEN
180       -- Call all the procedures related to Before Insert.
181 	null;
182     END IF;
183 
184   END before_dml;
185 
186 
187   PROCEDURE insert_row (
188     x_rowid                             IN OUT NOCOPY VARCHAR2,
189     x_item_id                           IN     NUMBER,
190     x_price                             IN     NUMBER,
191     x_effective_date                    IN     DATE,
192     x_revised_effective_date            IN     DATE,
193     x_revised_price                     IN     NUMBER,
194     x_run_id                            IN     NUMBER,
195     x_org_id                            IN     NUMBER,
196     x_mode                              IN     VARCHAR2 ) AS
197   /*
198   ||  Created By : panaraya
199   ||  Created On : 06-MAR-2002
200   ||  Purpose : Handles the INSERT DML logic for the table.
201   ||  Known limitations, enhancements or remarks :
202   ||  Change History :
203   ||  Who             When            What
204   ||  (reverse chronological order - newest change first)
205   */
206     CURSOR c IS
207       SELECT   rowid
208       FROM     igi_rpi_audit_items_all
209       WHERE    item_id=x_item_id and run_id=x_run_id;
210 
211     x_last_update_date           DATE;
212     x_last_updated_by            NUMBER;
213     x_last_update_login          NUMBER;
214 
215   BEGIN
216     x_last_update_date := SYSDATE;
217     IF (x_mode = 'I') THEN
218       x_last_updated_by := 1;
219       x_last_update_login := 0;
220     ELSIF (x_mode = 'R') THEN
221       x_last_updated_by := fnd_global.user_id;
222       IF (x_last_updated_by IS NULL) THEN
223         x_last_updated_by := -1;
224       END IF;
225       x_last_update_login := fnd_global.login_id;
226       IF (x_last_update_login IS NULL) THEN
227         x_last_update_login := -1;
228       END IF;
229     ELSE
230       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
231 
232       --Bug 3199481 (start)
233       If (l_unexp_level >= l_debug_level) then
234          FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_audit_items_all_pkg.insert_row.Msg1',FALSE);
235       End if;
236       --Bug 3199481 (end)
237       app_exception.raise_exception;
238     END IF;
239 
240     before_dml(
241       p_action                            => 'INSERT',
242       x_rowid                             => x_rowid,
243       x_item_id                           => x_item_id,
244       x_price                             => x_price,
245       x_effective_date                    => x_effective_date,
246       x_revised_effective_date            => x_revised_effective_date,
247       x_revised_price                     => x_revised_price,
248       x_run_id                            => x_run_id,
249       x_org_id                            => NVL (x_org_id,0),
250       x_creation_date                     => x_last_update_date,
251       x_created_by                        => x_last_updated_by,
252       x_last_update_date                  => x_last_update_date,
253       x_last_updated_by                   => x_last_updated_by,
254       x_last_update_login                 => x_last_update_login
255     );
256     INSERT INTO igi_rpi_audit_items_all (
257       item_id,
258       price,
259       effective_date,
260       revised_effective_date,
261       revised_price,
262       run_id,
263       org_id,
264       creation_date,
265       created_by,
266       last_update_date,
267       last_updated_by,
268       last_update_login
269     ) VALUES (
270       new_references.item_id,
271       new_references.price,
272       new_references.effective_date,
273       new_references.revised_effective_date,
274       new_references.revised_price,
275       new_references.run_id,
276       new_references.org_id,
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     );
283 
284     OPEN c;
285     FETCH c INTO x_rowid;
286     IF (c%NOTFOUND) THEN
287       CLOSE c;
288       RAISE NO_DATA_FOUND;
289     END IF;
290     CLOSE c;
291 
292   END insert_row;
293 
294 
295   PROCEDURE lock_row (
296     x_rowid                             IN     VARCHAR2,
297     x_item_id                           IN     NUMBER,
298     x_price                             IN     NUMBER,
299     x_effective_date                    IN     DATE,
300     x_revised_effective_date            IN     DATE,
301     x_revised_price                     IN     NUMBER,
302     x_run_id                            IN     NUMBER,
303     x_org_id                            IN     NUMBER
304   ) AS
305   /*
306   ||  Created By : panaraya
307   ||  Created On : 06-MAR-2002
308   ||  Purpose : Handles the LOCK mechanism for the table.
309   ||  Known limitations, enhancements or remarks :
310   ||  Change History :
311   ||  Who             When            What
312   ||  (reverse chronological order - newest change first)
313   */
314     CURSOR c1 IS
315       SELECT
316         item_id,
317         price,
318         effective_date,
319         revised_effective_date,
320         revised_price,
321         run_id,
322         org_id
323       FROM  igi_rpi_audit_items_all
324       WHERE rowid = x_rowid
325       FOR UPDATE NOWAIT;
326 
327     tlinfo c1%ROWTYPE;
328 
329   BEGIN
330 
331     OPEN c1;
332     FETCH c1 INTO tlinfo;
333     IF (c1%notfound) THEN
334       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
335 
336       --Bug 3199481 (start)
337       If (l_unexp_level >= l_debug_level) then
338          FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_audit_items_all_pkg.lock_row.Msg1',FALSE);
339       End if;
340       --Bug 3199481 (end)
341 null;
342       CLOSE c1;
343       app_exception.raise_exception;
344       RETURN;
345     END IF;
346     CLOSE c1;
347 
348     IF (
349         (tlinfo.item_id = x_item_id)
350         AND (tlinfo.price = x_price)
351         AND (tlinfo.effective_date = x_effective_date)
352         AND ((tlinfo.revised_effective_date = x_revised_effective_date) OR ((tlinfo.revised_effective_date IS NULL) AND (X_revised_effective_date IS NULL)))
353         AND ((tlinfo.revised_price = x_revised_price) OR ((tlinfo.revised_price IS NULL) AND (X_revised_price IS NULL)))
354         AND ((tlinfo.run_id = x_run_id) OR ((tlinfo.run_id IS NULL) AND (X_run_id IS NULL)))
355         AND ((tlinfo.org_id = x_org_id) OR ((tlinfo.org_id IS NULL) AND (X_org_id IS NULL)))
356        ) THEN
357       NULL;
358     ELSE
359       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
360 
361       --Bug 3199481 (start)
362       If (l_unexp_level >= l_debug_level) then
363          FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_audit_items_all_pkg.lock_row.Msg2',FALSE);
364       End if;
365       --Bug 3199481 (end)
366       app_exception.raise_exception;
367     END IF;
368 
369     RETURN;
370 
371   END lock_row;
372 
373 
374   PROCEDURE update_row (
375     x_rowid                             IN     VARCHAR2,
376     x_item_id                           IN     NUMBER,
377     x_price                             IN     NUMBER,
378     x_effective_date                    IN     DATE,
379     x_revised_effective_date            IN     DATE,
380     x_revised_price                     IN     NUMBER,
381     x_run_id                            IN     NUMBER,
382     x_org_id                            IN     NUMBER,
383     x_mode                              IN     VARCHAR2 ) AS
384   /*
385   ||  Created By : panaraya
386   ||  Created On : 06-MAR-2002
387   ||  Purpose : Handles the UPDATE DML logic 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     x_last_update_date           DATE ;
394     x_last_updated_by            NUMBER;
395     x_last_update_login          NUMBER;
396 
397   BEGIN
398 
399     x_last_update_date := SYSDATE;
400     IF (X_MODE = 'I') THEN
401       x_last_updated_by := 1;
402       x_last_update_login := 0;
403     ELSIF (x_mode = 'R') THEN
404       x_last_updated_by := fnd_global.user_id;
405       IF x_last_updated_by IS NULL THEN
406         x_last_updated_by := -1;
407       END IF;
408       x_last_update_login := fnd_global.login_id;
409       IF (x_last_update_login IS NULL) THEN
410         x_last_update_login := -1;
411       END IF;
412     ELSE
413       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
414 
415       --Bug 3199481 (start)
416       If (l_unexp_level >= l_debug_level) then
417          FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_audit_items_all_pkg.update_row.Msg1',FALSE);
418       End if;
419       --Bug 3199481 (end)
420       app_exception.raise_exception;
421     END IF;
422 
423     before_dml(
424       p_action                            => 'UPDATE',
425       x_rowid                             => x_rowid,
426       x_item_id                           => x_item_id,
427       x_price                             => x_price,
428       x_effective_date                    => x_effective_date,
429       x_revised_effective_date            => x_revised_effective_date,
430       x_revised_price                     => x_revised_price,
431       x_run_id                            => x_run_id,
432       x_org_id                            => NVL (x_org_id,0),
433       x_creation_date                     => x_last_update_date,
434       x_created_by                        => x_last_updated_by,
435       x_last_update_date                  => x_last_update_date,
436       x_last_updated_by                   => x_last_updated_by,
437       x_last_update_login                 => x_last_update_login
438     );
439 
440     UPDATE igi_rpi_audit_items_all
441       SET
442         item_id                           = new_references.item_id,
443         price                             = new_references.price,
444         effective_date                    = new_references.effective_date,
445         revised_effective_date            = new_references.revised_effective_date,
446         revised_price                     = new_references.revised_price,
447         run_id                            = new_references.run_id,
448         org_id                            = new_references.org_id,
449         last_update_date                  = x_last_update_date,
450         last_updated_by                   = x_last_updated_by,
451         last_update_login                 = x_last_update_login
452       WHERE rowid = x_rowid;
453 
454     IF (SQL%NOTFOUND) THEN
455       RAISE NO_DATA_FOUND;
456     END IF;
457 
458   END update_row;
459 
460 
461   PROCEDURE add_row (
462     x_rowid                             IN OUT NOCOPY VARCHAR2,
463     x_item_id                           IN     NUMBER,
464     x_price                             IN     NUMBER,
465     x_effective_date                    IN     DATE,
466     x_revised_effective_date            IN     DATE,
467     x_revised_price                     IN     NUMBER,
468     x_run_id                            IN     NUMBER,
469     x_org_id                            IN     NUMBER,
470     x_mode                              IN     VARCHAR2 ) AS
471   /*
472   ||  Created By : panaraya
473   ||  Created On : 06-MAR-2002
474   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
475   ||  Known limitations, enhancements or remarks :
476   ||  Change History :
477   ||  Who             When            What
478   ||  (reverse chronological order - newest change first)
479   */
480     CURSOR c1 IS
481       SELECT   rowid
482       FROM     igi_rpi_audit_items_all
483       WHERE    item_id=x_item_id and run_id=x_run_id;
484 
485   BEGIN
486 
487     OPEN c1;
488     FETCH c1 INTO x_rowid;
489     IF (c1%NOTFOUND) THEN
490       CLOSE c1;
491 
492       insert_row (
493         x_rowid,
494         x_item_id,
495         x_price,
496         x_effective_date,
497         x_revised_effective_date,
498         x_revised_price,
499         x_run_id,
500         x_org_id,
501         x_mode
502       );
503       RETURN;
504     END IF;
505     CLOSE c1;
506 
507     update_row (
508       x_rowid,
509       x_item_id,
510       x_price,
511       x_effective_date,
512       x_revised_effective_date,
513       x_revised_price,
514       x_run_id,
515       x_org_id,
516       x_mode
517     );
518 
519   END add_row;
520 
521 
522   PROCEDURE delete_row (
523     x_rowid IN VARCHAR2
524   ) AS
525   /*
529   ||  Known limitations, enhancements or remarks :
526   ||  Created By : panaraya
527   ||  Created On : 06-MAR-2002
528   ||  Purpose : Handles the DELETE DML logic for the table.
530   ||  Change History :
531   ||  Who             When            What
532   ||  (reverse chronological order - newest change first)
533   */
534   BEGIN
535 
536     before_dml (
537       p_action => 'DELETE',
538       x_rowid => x_rowid
539     );
540 
541     DELETE FROM igi_rpi_audit_items_all
542     WHERE rowid = x_rowid;
543 
544     IF (SQL%NOTFOUND) THEN
545       RAISE NO_DATA_FOUND;
546     END IF;
547 
548   END delete_row;
549 
550 
551 END igi_rpi_audit_items_all_pkg;