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_UC_JNT_ADM_INST_PKG

Source


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