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_CO_S_LTR_PARAM_PKG

Source


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