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_LTR_PHR_PKG

Source


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