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_PS_UNIT_TYPE_LVL_PKG

Source


1 PACKAGE BODY igs_ps_unit_type_lvl_pkg AS
2 /* $Header: IGSPI1YB.pls 120.1 2005/08/10 01:16:13 appldev ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_ps_unit_type_lvl%ROWTYPE;
6   new_references igs_ps_unit_type_lvl%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_unit_type_id                      IN     NUMBER      DEFAULT NULL,
12     x_level_code                        IN     VARCHAR2    DEFAULT NULL,
13     x_description                       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 : apelleti
23   ||  Created On : 02-MAY-2001
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_PS_UNIT_TYPE_LVL
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.unit_type_id                      := x_unit_type_id;
55     new_references.level_code                        := x_level_code;
56     new_references.description                       := x_description;
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_uniqueness AS
75   /*
76   ||  Created By : apelleti
77   ||  Created On : 02-MAY-2001
78   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
79   ||  Known limitations, enhancements or remarks :
80   ||  Change History :
81   ||  Who             When            What
82   ||  (reverse chronological order - newest change first)
83   */
84   BEGIN
85 
86     IF ( get_uk_for_validation (
87            new_references.level_code
88          )
89        ) THEN
90       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
91       igs_ge_msg_stack.add;
92       app_exception.raise_exception;
93     END IF;
94 
95   END check_uniqueness;
96 
97 
98 
99   FUNCTION get_pk_for_validation (
100     x_unit_type_id                      IN     NUMBER
101   ) RETURN BOOLEAN AS
102   /*
103   ||  Created By : apelleti
104   ||  Created On : 02-MAY-2001
105   ||  Purpose : Validates the Primary Key of the table.
106   ||  Known limitations, enhancements or remarks :
107   ||  Change History :
108   ||  Who             When            What
109   ||  (reverse chronological order - newest change first)
110   */
111     CURSOR cur_rowid IS
112       SELECT   rowid
113       FROM     igs_ps_unit_type_lvl
114       WHERE    unit_type_id = x_unit_type_id ;
115 
116     lv_rowid cur_rowid%RowType;
117 
118   BEGIN
119 
120     OPEN cur_rowid;
121     FETCH cur_rowid INTO lv_rowid;
122     IF (cur_rowid%FOUND) THEN
123       CLOSE cur_rowid;
124       RETURN(TRUE);
125     ELSE
126       CLOSE cur_rowid;
127       RETURN(FALSE);
128     END IF;
129 
130   END get_pk_for_validation;
131 
132 
133   FUNCTION get_uk_for_validation (
134     x_level_code                        IN     VARCHAR2
135   ) RETURN BOOLEAN AS
136   /*
137   ||  Created By : apelleti
138   ||  Created On : 02-MAY-2001
139   ||  Purpose : Validates the Unique Keys 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_ps_unit_type_lvl
148       WHERE    level_code = x_level_code
149       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
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_uk_for_validation ;
166 
167 
168   PROCEDURE before_dml (
169     p_action                            IN     VARCHAR2,
170     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
171     x_unit_type_id                      IN     NUMBER      DEFAULT NULL,
172     x_level_code                        IN     VARCHAR2    DEFAULT NULL,
173     x_description                       IN     VARCHAR2    DEFAULT NULL,
174     x_closed_ind                        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 : apelleti
183   ||  Created On : 02-MAY-2001
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_unit_type_id,
197       x_level_code,
198       x_description,
199       x_closed_ind,
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.unit_type_id
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_uniqueness;
218     ELSIF (p_action = 'UPDATE') THEN
219       -- Call all the procedures related to Before Update.
220       check_uniqueness;
221     ELSIF (p_action = 'VALIDATE_INSERT') THEN
222       -- Call all the procedures related to Before Insert.
223       IF ( get_pk_for_validation (
224              new_references.unit_type_id
225            )
226          ) THEN
227         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
228         igs_ge_msg_stack.add;
229         app_exception.raise_exception;
230       END IF;
231       check_uniqueness;
232     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
233       check_uniqueness;
234     END IF;
235 
236     l_rowid:=NULL;
237 
238   END before_dml;
239 
240 
241   PROCEDURE insert_row (
242     x_rowid                             IN OUT NOCOPY VARCHAR2,
243     x_unit_type_id                      IN OUT NOCOPY NUMBER,
244     x_level_code                        IN     VARCHAR2,
245     x_description                       IN     VARCHAR2,
246     x_closed_ind                        IN     VARCHAR2,
247     x_mode                              IN     VARCHAR2 DEFAULT 'R'
248   ) AS
249   /*
250   ||  Created By : apelleti
251   ||  Created On : 02-MAY-2001
252   ||  Purpose : Handles the INSERT DML logic for the table.
253   ||  Known limitations, enhancements or remarks :
254   ||  Change History :
255   ||  Who             When            What
256   ||  (reverse chronological order - newest change first)
257   */
258     CURSOR c IS
259       SELECT   rowid
260       FROM     igs_ps_unit_type_lvl
261       WHERE    unit_type_id                      = x_unit_type_id;
262 
263     x_last_update_date           DATE;
264     x_last_updated_by            NUMBER;
265     x_last_update_login          NUMBER;
266 
267   BEGIN
268 
269     x_last_update_date := SYSDATE;
270     IF (x_mode = 'I') THEN
271       x_last_updated_by := 1;
272       x_last_update_login := 0;
273     ELSIF (x_mode = 'R') THEN
274       x_last_updated_by := fnd_global.user_id;
275       IF (x_last_updated_by IS NULL) THEN
276         x_last_updated_by := -1;
277       END IF;
278       x_last_update_login := fnd_global.login_id;
279       IF (x_last_update_login IS NULL) THEN
280         x_last_update_login := -1;
281       END IF;
282     ELSE
283       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
284       igs_ge_msg_stack.add;
285       app_exception.raise_exception;
286     END IF;
287 
288     SELECT    igs_ps_unit_type_lvl_s.NEXTVAL
289     INTO      x_unit_type_id
290     FROM      dual;
291 
292     before_dml(
293       p_action                            => 'INSERT',
294       x_rowid                             => x_rowid,
295       x_unit_type_id                      => x_unit_type_id,
296       x_level_code                        => x_level_code,
297       x_description                       => x_description,
298       x_closed_ind                        => x_closed_ind,
299       x_creation_date                     => x_last_update_date,
300       x_created_by                        => x_last_updated_by,
301       x_last_update_date                  => x_last_update_date,
302       x_last_updated_by                   => x_last_updated_by,
303       x_last_update_login                 => x_last_update_login
304     );
305 
306     INSERT INTO igs_ps_unit_type_lvl (
307       unit_type_id,
308       level_code,
309       description,
310       closed_ind,
311       creation_date,
312       created_by,
313       last_update_date,
314       last_updated_by,
315       last_update_login
316     ) VALUES (
317       new_references.unit_type_id,
318       new_references.level_code,
319       new_references.description,
320       new_references.closed_ind,
321       x_last_update_date,
322       x_last_updated_by,
323       x_last_update_date,
324       x_last_updated_by,
325       x_last_update_login
326     );
327 
328     OPEN c;
329     FETCH c INTO x_rowid;
330     IF (c%NOTFOUND) THEN
331       CLOSE c;
332       RAISE NO_DATA_FOUND;
333     END IF;
334     CLOSE c;
335 
336   END insert_row;
337 
338 
339   PROCEDURE lock_row (
340     x_rowid                             IN     VARCHAR2,
341     x_unit_type_id                      IN     NUMBER,
342     x_level_code                        IN     VARCHAR2,
343     x_description                       IN     VARCHAR2,
344     x_closed_ind                        IN     VARCHAR2
345   ) AS
346   /*
347   ||  Created By : apelleti
348   ||  Created On : 02-MAY-2001
349   ||  Purpose : Handles the LOCK mechanism for the table.
350   ||  Known limitations, enhancements or remarks :
351   ||  Change History :
352   ||  Who             When            What
353   ||  (reverse chronological order - newest change first)
354   */
355     CURSOR c1 IS
356       SELECT
357         level_code,
358         description,
359         closed_ind
360       FROM  igs_ps_unit_type_lvl
361       WHERE rowid = x_rowid
362       FOR UPDATE NOWAIT;
363 
364     tlinfo c1%ROWTYPE;
365 
366   BEGIN
367 
368     OPEN c1;
369     FETCH c1 INTO tlinfo;
370     IF (c1%notfound) THEN
371       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
372       igs_ge_msg_stack.add;
373       CLOSE c1;
374       app_exception.raise_exception;
375       RETURN;
376     END IF;
377     CLOSE c1;
378 
379     IF (
380         (tlinfo.level_code = x_level_code)
381         AND (tlinfo.description = x_description)
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 (
397     x_rowid                             IN     VARCHAR2,
398     x_unit_type_id                      IN     NUMBER,
399     x_level_code                        IN     VARCHAR2,
400     x_description                       IN     VARCHAR2,
401     x_closed_ind                        IN     VARCHAR2,
402     x_mode                              IN     VARCHAR2 DEFAULT 'R'
403   ) AS
404   /*
405   ||  Created By : apelleti
406   ||  Created On : 02-MAY-2001
407   ||  Purpose : Handles the UPDATE DML logic for the table.
408   ||  Known limitations, enhancements or remarks :
409   ||  Change History :
410   ||  Who             When            What
411   ||  (reverse chronological order - newest change first)
412   */
413     x_last_update_date           DATE ;
414     x_last_updated_by            NUMBER;
415     x_last_update_login          NUMBER;
416 
417   BEGIN
418 
419     x_last_update_date := SYSDATE;
420     IF (X_MODE = 'I') THEN
421       x_last_updated_by := 1;
422       x_last_update_login := 0;
423     ELSIF (x_mode = 'R') THEN
424       x_last_updated_by := fnd_global.user_id;
425       IF x_last_updated_by IS NULL THEN
426         x_last_updated_by := -1;
427       END IF;
428       x_last_update_login := fnd_global.login_id;
429       IF (x_last_update_login IS NULL) THEN
430         x_last_update_login := -1;
431       END IF;
432     ELSE
433       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
434       igs_ge_msg_stack.add;
435       app_exception.raise_exception;
436     END IF;
437 
438     before_dml(
439       p_action                            => 'UPDATE',
440       x_rowid                             => x_rowid,
441       x_unit_type_id                      => x_unit_type_id,
442       x_level_code                        => x_level_code,
443       x_description                       => x_description,
444       x_closed_ind                        => x_closed_ind,
445       x_creation_date                     => x_last_update_date,
446       x_created_by                        => x_last_updated_by,
447       x_last_update_date                  => x_last_update_date,
448       x_last_updated_by                   => x_last_updated_by,
449       x_last_update_login                 => x_last_update_login
450     );
451 
452     UPDATE igs_ps_unit_type_lvl
453       SET
454         level_code                        = new_references.level_code,
455         description                       = new_references.description,
456         closed_ind                        = new_references.closed_ind,
457         last_update_date                  = x_last_update_date,
458         last_updated_by                   = x_last_updated_by,
459         last_update_login                 = x_last_update_login
460       WHERE rowid = x_rowid;
461 
462     IF (SQL%NOTFOUND) THEN
463       RAISE NO_DATA_FOUND;
464     END IF;
465 
466   END update_row;
467 
468 
469   PROCEDURE add_row (
470     x_rowid                             IN OUT NOCOPY VARCHAR2,
471     x_unit_type_id                      IN OUT NOCOPY NUMBER,
472     x_level_code                        IN     VARCHAR2,
473     x_description                       IN     VARCHAR2,
474     x_closed_ind                        IN     VARCHAR2,
475     x_mode                              IN     VARCHAR2 DEFAULT 'R'
476   ) AS
477   /*
478   ||  Created By : apelleti
479   ||  Created On : 02-MAY-2001
480   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
481   ||  Known limitations, enhancements or remarks :
482   ||  Change History :
483   ||  Who             When            What
484   ||  (reverse chronological order - newest change first)
485   */
486     CURSOR c1 IS
487       SELECT   rowid
488       FROM     igs_ps_unit_type_lvl
489       WHERE    unit_type_id                      = x_unit_type_id;
490 
491   BEGIN
492 
493     OPEN c1;
494     FETCH c1 INTO x_rowid;
495     IF (c1%NOTFOUND) THEN
496       CLOSE c1;
497 
498       insert_row (
499         x_rowid,
500         x_unit_type_id,
501         x_level_code,
502         x_description,
503         x_closed_ind,
504         x_mode
505       );
506       RETURN;
507     END IF;
508     CLOSE c1;
509 
510     update_row (
511       x_rowid,
512       x_unit_type_id,
513       x_level_code,
514       x_description,
515       x_closed_ind,
516       x_mode
517     );
518 
519   END add_row;
520 
521 
522 END igs_ps_unit_type_lvl_pkg;