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_EN_INTM_TYPES_PKG

Source


1 PACKAGE BODY igs_en_intm_types_pkg AS
2 /* $Header: IGSEI56B.pls 115.6 2003/02/20 11:26:14 kkillams noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_en_intm_types%ROWTYPE;
6   new_references igs_en_intm_types%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_intermission_type                 IN     VARCHAR2    DEFAULT NULL,
12     x_description                       IN     VARCHAR2    DEFAULT NULL,
13     x_appr_reqd_ind                     IN     VARCHAR2    DEFAULT NULL,
14     x_study_antr_inst_ind               IN     VARCHAR2    DEFAULT NULL,
15     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
16     x_creation_date                     IN     DATE        DEFAULT NULL,
17     x_created_by                        IN     NUMBER      DEFAULT NULL,
18     x_last_update_date                  IN     DATE        DEFAULT NULL,
19     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
20     x_last_update_login                 IN     NUMBER      DEFAULT NULL
21   ) AS
22   /*
23   ||  Created By : pkpatel
24   ||  Created On : 03-SEP-2001
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_en_intm_types
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.intermission_type                 := x_intermission_type;
56     new_references.description                       := x_description;
57     new_references.appr_reqd_ind                     := x_appr_reqd_ind;
58     new_references.study_antr_inst_ind               := x_study_antr_inst_ind;
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   FUNCTION get_uk_for_validation (
76     x_intermission_type                 IN     VARCHAR2
77   ) RETURN BOOLEAN AS
78   /*
79   ||  Created By : pkpatel
80   ||  Created On : 03-SEP-2001
81   ||  Purpose : Validates the Unique Keys of the table.
82   ||  Known limitations, enhancements or remarks :
83   ||  Change History :
84   ||  Who             When            What
85   ||  kkillams        20-02-2003      Removed for update no wait clause
86   ||                                  in the cursor cur_rowid w.r.t. bug 2762883
87   ||  (reverse chronological order - newest change first)
88   */
89     CURSOR cur_rowid IS
90       SELECT   rowid
91       FROM     igs_en_intm_types
92       WHERE    intermission_type = x_intermission_type
93       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
94 
95 
96     lv_rowid cur_rowid%RowType;
97 
98   BEGIN
99 
100     OPEN cur_rowid;
101     FETCH cur_rowid INTO lv_rowid;
102     IF (cur_rowid%FOUND) THEN
103       CLOSE cur_rowid;
104         RETURN (true);
105         ELSE
106        CLOSE cur_rowid;
107       RETURN(FALSE);
108     END IF;
109 
110   END get_uk_for_validation ;
111 
112 
113   PROCEDURE before_dml (
114     p_action                            IN     VARCHAR2,
115     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
116     x_intermission_type                 IN     VARCHAR2    DEFAULT NULL,
117     x_description                       IN     VARCHAR2    DEFAULT NULL,
118     x_appr_reqd_ind                     IN     VARCHAR2    DEFAULT NULL,
119     x_study_antr_inst_ind               IN     VARCHAR2    DEFAULT NULL,
120     x_closed_ind                        IN     VARCHAR2    DEFAULT NULL,
121     x_creation_date                     IN     DATE        DEFAULT NULL,
122     x_created_by                        IN     NUMBER      DEFAULT NULL,
123     x_last_update_date                  IN     DATE        DEFAULT NULL,
124     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
125     x_last_update_login                 IN     NUMBER      DEFAULT NULL
126   ) AS
127   /*
128   ||  Created By : pkpatel
129   ||  Created On : 03-SEP-2001
130   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
131   ||            Trigger Handlers for the table, before any DML operation.
132   ||  Known limitations, enhancements or remarks :
133   ||  Change History :
134   ||  Who             When            What
135   ||  (reverse chronological order - newest change first)
136   */
137   BEGIN
138 
139     set_column_values (
140       p_action,
141       x_rowid,
142       x_intermission_type,
143       x_description,
144       x_appr_reqd_ind,
145       x_study_antr_inst_ind,
146       x_closed_ind,
147       x_creation_date,
148       x_created_by,
149       x_last_update_date,
150       x_last_updated_by,
151       x_last_update_login
152     );
153 
154     IF (p_action = 'INSERT') THEN
155       -- Call all the procedures related to Before Insert.
156       IF ( get_uk_for_validation (
157            new_references.intermission_type
158          )
159                   ) THEN
160         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
161         igs_ge_msg_stack.add;
162         app_exception.raise_exception;
163       END IF;
164     ELSIF (p_action = 'VALIDATE_INSERT') THEN
165       -- Call all the procedures related to Before Insert.
166       IF ( get_uk_for_validation (
167            new_references.intermission_type
168          )
169                   ) THEN
170         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
171         igs_ge_msg_stack.add;
172         app_exception.raise_exception;
173       END IF;
174     END IF;
175 
176   END before_dml;
177 
178 
179   PROCEDURE insert_row (
180     x_rowid                             IN OUT NOCOPY VARCHAR2,
181     x_intermission_type                 IN     VARCHAR2,
182     x_description                       IN     VARCHAR2,
183     x_appr_reqd_ind                     IN     VARCHAR2,
184     x_study_antr_inst_ind               IN     VARCHAR2,
185     x_closed_ind                        IN     VARCHAR2,
186     x_mode                              IN     VARCHAR2 DEFAULT 'R'
187   ) AS
188   /*
189   ||  Created By : pkpatel
190   ||  Created On : 03-SEP-2001
191   ||  Purpose : Handles the INSERT DML logic for the table.
192   ||  Known limitations, enhancements or remarks :
193   ||  Change History :
194   ||  Who             When            What
195   ||  (reverse chronological order - newest change first)
196   */
197     CURSOR c IS
198       SELECT   rowid
199       FROM     igs_en_intm_types
200       WHERE    intermission_type = x_intermission_type;
201 
202     x_last_update_date           DATE;
203     x_last_updated_by            NUMBER;
204     x_last_update_login          NUMBER;
205 
206   BEGIN
207 
208     x_last_update_date := SYSDATE;
209     IF (x_mode = 'I') THEN
210       x_last_updated_by := 1;
211       x_last_update_login := 0;
212     ELSIF (x_mode = 'R') THEN
213       x_last_updated_by := fnd_global.user_id;
214       IF (x_last_updated_by IS NULL) THEN
215         x_last_updated_by := -1;
216       END IF;
217       x_last_update_login := fnd_global.login_id;
218       IF (x_last_update_login IS NULL) THEN
219         x_last_update_login := -1;
220       END IF;
221     ELSE
222       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
223       igs_ge_msg_stack.add;
224       app_exception.raise_exception;
225     END IF;
226 
227     before_dml(
228       p_action                            => 'INSERT',
229       x_rowid                             => x_rowid,
230       x_intermission_type                 => x_intermission_type,
231       x_description                       => x_description,
232       x_appr_reqd_ind                     => x_appr_reqd_ind,
233       x_study_antr_inst_ind               => x_study_antr_inst_ind,
234       x_closed_ind                        => x_closed_ind,
235       x_creation_date                     => x_last_update_date,
236       x_created_by                        => x_last_updated_by,
237       x_last_update_date                  => x_last_update_date,
238       x_last_updated_by                   => x_last_updated_by,
239       x_last_update_login                 => x_last_update_login
240     );
241 
242     INSERT INTO igs_en_intm_types (
243       intermission_type,
244       description,
245       appr_reqd_ind,
246       study_antr_inst_ind,
247       closed_ind,
248       creation_date,
249       created_by,
250       last_update_date,
251       last_updated_by,
252       last_update_login
253     ) VALUES (
254       new_references.intermission_type,
255       new_references.description,
256       new_references.appr_reqd_ind,
257       new_references.study_antr_inst_ind,
258       new_references.closed_ind,
259       x_last_update_date,
260       x_last_updated_by,
261       x_last_update_date,
262       x_last_updated_by,
263       x_last_update_login
264     );
265 
266     OPEN c;
267     FETCH c INTO x_rowid;
268     IF (c%NOTFOUND) THEN
269       CLOSE c;
270       RAISE NO_DATA_FOUND;
271     END IF;
272     CLOSE c;
273 
274   END insert_row;
275 
276 
277   PROCEDURE lock_row (
278     x_rowid                             IN     VARCHAR2,
279     x_intermission_type                 IN     VARCHAR2,
280     x_description                       IN     VARCHAR2,
281     x_appr_reqd_ind                     IN     VARCHAR2,
282     x_study_antr_inst_ind               IN     VARCHAR2,
283     x_closed_ind                        IN     VARCHAR2
284   ) AS
285   /*
286   ||  Created By : pkpatel
287   ||  Created On : 03-SEP-2001
288   ||  Purpose : Handles the LOCK mechanism for the table.
289   ||  Known limitations, enhancements or remarks :
290   ||  Change History :
291   ||  Who             When            What
292   ||  (reverse chronological order - newest change first)
293   */
294     CURSOR c1 IS
295       SELECT
296         intermission_type,
297         description,
298         appr_reqd_ind,
299         study_antr_inst_ind,
300         closed_ind
301       FROM  igs_en_intm_types
302       WHERE rowid = x_rowid
303       FOR UPDATE NOWAIT;
304 
305     tlinfo c1%ROWTYPE;
306 
307   BEGIN
308 
309     OPEN c1;
310     FETCH c1 INTO tlinfo;
311     IF (c1%notfound) THEN
312       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
313       igs_ge_msg_stack.add;
314       CLOSE c1;
315       app_exception.raise_exception;
316       RETURN;
317     END IF;
318     CLOSE c1;
319 
320     IF (
321         (tlinfo.intermission_type = x_intermission_type)
322         AND (tlinfo.description = x_description)
323         AND (tlinfo.appr_reqd_ind = x_appr_reqd_ind)
324         AND (tlinfo.study_antr_inst_ind = x_study_antr_inst_ind)
325         AND (tlinfo.closed_ind = x_closed_ind)
326        ) THEN
327       NULL;
328     ELSE
329       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
330       igs_ge_msg_stack.add;
331       app_exception.raise_exception;
332     END IF;
333 
334     RETURN;
335 
336   END lock_row;
337 
338 
339   PROCEDURE update_row (
340     x_rowid                             IN     VARCHAR2,
341     x_intermission_type                 IN     VARCHAR2,
342     x_description                       IN     VARCHAR2,
343     x_appr_reqd_ind                     IN     VARCHAR2,
344     x_study_antr_inst_ind               IN     VARCHAR2,
345     x_closed_ind                        IN     VARCHAR2,
346     x_mode                              IN     VARCHAR2 DEFAULT 'R'
347   ) AS
348   /*
349   ||  Created By : pkpatel
350   ||  Created On : 03-SEP-2001
351   ||  Purpose : Handles the UPDATE DML logic for the table.
352   ||  Known limitations, enhancements or remarks :
353   ||  Change History :
354   ||  Who             When            What
355   ||  kkillams        20-02-2003      Removed the intermission_type column  from udpate statement
356   ||  (reverse chronological order - newest change first)
357   */
358     x_last_update_date           DATE ;
359     x_last_updated_by            NUMBER;
360     x_last_update_login          NUMBER;
361 
362   BEGIN
363 
364     x_last_update_date := SYSDATE;
365     IF (X_MODE = 'I') THEN
366       x_last_updated_by := 1;
367       x_last_update_login := 0;
368     ELSIF (x_mode = 'R') THEN
369       x_last_updated_by := fnd_global.user_id;
370       IF x_last_updated_by IS NULL THEN
371         x_last_updated_by := -1;
372       END IF;
373       x_last_update_login := fnd_global.login_id;
374       IF (x_last_update_login IS NULL) THEN
375         x_last_update_login := -1;
376       END IF;
377     ELSE
378       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
379       igs_ge_msg_stack.add;
380       app_exception.raise_exception;
381     END IF;
382 
383     before_dml(
384       p_action                            => 'UPDATE',
385       x_rowid                             => x_rowid,
386       x_intermission_type                 => x_intermission_type,
387       x_description                       => x_description,
388       x_appr_reqd_ind                     => x_appr_reqd_ind,
389       x_study_antr_inst_ind               => x_study_antr_inst_ind,
390       x_closed_ind                        => x_closed_ind,
391       x_creation_date                     => x_last_update_date,
392       x_created_by                        => x_last_updated_by,
393       x_last_update_date                  => x_last_update_date,
394       x_last_updated_by                   => x_last_updated_by,
395       x_last_update_login                 => x_last_update_login
396     );
397 
398     UPDATE igs_en_intm_types
399       SET
400         description                       = new_references.description,
401         appr_reqd_ind                     = new_references.appr_reqd_ind,
402         study_antr_inst_ind               = new_references.study_antr_inst_ind,
403         closed_ind                        = new_references.closed_ind,
404         last_update_date                  = x_last_update_date,
405         last_updated_by                   = x_last_updated_by,
406         last_update_login                 = x_last_update_login
407       WHERE rowid = x_rowid;
408 
409     IF (SQL%NOTFOUND) THEN
410       RAISE NO_DATA_FOUND;
411     END IF;
412 
413   END update_row;
414 
415 
416   PROCEDURE add_row (
417     x_rowid                             IN OUT NOCOPY VARCHAR2,
418     x_intermission_type                 IN     VARCHAR2,
419     x_description                       IN     VARCHAR2,
420     x_appr_reqd_ind                     IN     VARCHAR2,
421     x_study_antr_inst_ind               IN     VARCHAR2,
422     x_closed_ind                        IN     VARCHAR2,
423     x_mode                              IN     VARCHAR2 DEFAULT 'R'
424   ) AS
425   /*
426   ||  Created By : pkpatel
427   ||  Created On : 03-SEP-2001
428   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
429   ||  Known limitations, enhancements or remarks :
430   ||  Change History :
431   ||  Who             When            What
432   ||  (reverse chronological order - newest change first)
433   */
434     CURSOR c1 IS
435       SELECT   rowid
436       FROM     igs_en_intm_types
437       WHERE    intermission_type = x_intermission_type;
438 
439   BEGIN
440 
441     OPEN c1;
442     FETCH c1 INTO x_rowid;
443     IF (c1%NOTFOUND) THEN
444       CLOSE c1;
445 
446       insert_row (
447         x_rowid,
448         x_intermission_type,
449         x_description,
450         x_appr_reqd_ind,
451         x_study_antr_inst_ind,
452         x_closed_ind,
453         x_mode
454       );
455       RETURN;
456     END IF;
457     CLOSE c1;
458 
459     update_row (
460       x_rowid,
461       x_intermission_type,
462       x_description,
463       x_appr_reqd_ind,
464       x_study_antr_inst_ind,
465       x_closed_ind,
466       x_mode
467     );
468 
469   END add_row;
470 
471 END igs_en_intm_types_pkg;