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_PS_WL_OVER_RESN_PKG

Source


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