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_OR_CWLK_PKG

Source


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