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_AS_ATTEND_CONFIG_PKG

Source


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