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_UC_OLD_OUSTAT_PKG

Source


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