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_EN_USEC_STAT_DSP_PKG

Source


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