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_RULES_PKG

Source


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