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_CA_DA_OVD_VALS_PKG

Source


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