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_APPL_STATUS_PKG

Source


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