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_CONFIGS_PKG

Source


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