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.IGF_AW_CORRESP_PKG

Source


1 PACKAGE BODY igf_aw_corresp_pkg AS
2 /* $Header: IGFWI37B.pls 115.5 2002/11/28 14:42:20 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_aw_corresp_all%ROWTYPE;
6   new_references igf_aw_corresp_all%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_crsp_id                           IN     NUMBER      DEFAULT NULL,
12     x_base_id                           IN     NUMBER      DEFAULT NULL,
13     x_flag                              IN     VARCHAR2    DEFAULT NULL,
14     x_line_number                       IN     NUMBER      DEFAULT NULL,
15     x_line_data                         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 : prchandr
24   ||  Created On : 01-JUN-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     IGF_AW_CORRESP_ALL
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.crsp_id                           := x_crsp_id;
56     new_references.base_id                           := x_base_id;
57     new_references.flag                              := x_flag;
58     new_references.line_number                       := x_line_number;
59     new_references.line_data                         := x_line_data;
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 
76   PROCEDURE check_parent_existance AS
77   /*
78   ||  Created By : prchandr
79   ||  Created On : 01-JUN-2001
80   ||  Purpose : Checks for the existance of Parent records.
81   ||  Known limitations, enhancements or remarks :
82   ||  Change History :
83   ||  Who             When            What
84   ||  (reverse chronological order - newest change first)
85   */
86   BEGIN
87 
88     IF (((old_references.base_id = new_references.base_id)) OR
89         ((new_references.base_id IS NULL))) THEN
90       NULL;
91     ELSIF NOT igf_ap_fa_base_rec_pkg.get_pk_for_validation (
92                 new_references.base_id
93               ) THEN
94       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
95       igs_ge_msg_stack.add;
96       app_exception.raise_exception;
97     END IF;
98 
99   END check_parent_existance;
100 
101 
102   FUNCTION get_pk_for_validation (
103     x_crsp_id                           IN     NUMBER
104   ) RETURN BOOLEAN AS
105   /*
106   ||  Created By : prchandr
107   ||  Created On : 01-JUN-2001
108   ||  Purpose : Validates the Primary Key of the table.
109   ||  Known limitations, enhancements or remarks :
110   ||  Change History :
111   ||  Who             When            What
112   ||  (reverse chronological order - newest change first)
113   */
114     CURSOR cur_rowid IS
115       SELECT   rowid
116       FROM     igf_aw_corresp_all
117       WHERE    crsp_id = x_crsp_id
118       FOR UPDATE NOWAIT;
119 
120     lv_rowid cur_rowid%RowType;
121 
122   BEGIN
123 
124     OPEN cur_rowid;
125     FETCH cur_rowid INTO lv_rowid;
126     IF (cur_rowid%FOUND) THEN
127       CLOSE cur_rowid;
128       RETURN(TRUE);
129     ELSE
130       CLOSE cur_rowid;
131       RETURN(FALSE);
132     END IF;
133 
134   END get_pk_for_validation;
135 
136 
137   PROCEDURE get_fk_igf_ap_fa_base_rec (
138     x_base_id                           IN     NUMBER
139   ) AS
140   /*
141   ||  Created By : prchandr
142   ||  Created On : 01-JUN-2001
143   ||  Purpose : Validates the Foreign Keys for the table.
144   ||  Known limitations, enhancements or remarks :
145   ||  Change History :
146   ||  Who             When            What
147   ||  (reverse chronological order - newest change first)
148   */
149     CURSOR cur_rowid IS
150       SELECT   rowid
151       FROM     igf_aw_corresp_all
152       WHERE   ((base_id = x_base_id));
153 
154     lv_rowid cur_rowid%RowType;
155 
156   BEGIN
157 
158     OPEN cur_rowid;
159     FETCH cur_rowid INTO lv_rowid;
160     IF (cur_rowid%FOUND) THEN
161       CLOSE cur_rowid;
162       fnd_message.set_name ('IGF', 'IGF_AW_CRSP_FA_BASE_FK');
163       igs_ge_msg_stack.add;
164       app_exception.raise_exception;
165       RETURN;
166     END IF;
167     CLOSE cur_rowid;
168 
169   END get_fk_igf_ap_fa_base_rec;
170 
171 
172   PROCEDURE before_dml (
173     p_action                            IN     VARCHAR2,
174     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
175     x_crsp_id                           IN     NUMBER      DEFAULT NULL,
176     x_base_id                           IN     NUMBER      DEFAULT NULL,
177     x_flag                              IN     VARCHAR2    DEFAULT NULL,
178     x_line_number                       IN     NUMBER      DEFAULT NULL,
179     x_line_data                         IN     VARCHAR2    DEFAULT NULL,
180     x_creation_date                     IN     DATE        DEFAULT NULL,
181     x_created_by                        IN     NUMBER      DEFAULT NULL,
182     x_last_update_date                  IN     DATE        DEFAULT NULL,
183     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
184     x_last_update_login                 IN     NUMBER      DEFAULT NULL
185   ) AS
186   /*
187   ||  Created By : prchandr
188   ||  Created On : 01-JUN-2001
189   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
190   ||            Trigger Handlers for the table, before any DML operation.
191   ||  Known limitations, enhancements or remarks :
192   ||  Change History :
193   ||  Who             When            What
194   ||  (reverse chronological order - newest change first)
195   */
196   BEGIN
197 
198     set_column_values (
199       p_action,
200       x_rowid,
201       x_crsp_id,
202       x_base_id,
203       x_flag,
204       x_line_number,
205       x_line_data,
206       x_creation_date,
207       x_created_by,
208       x_last_update_date,
209       x_last_updated_by,
210       x_last_update_login
211     );
212 
213     IF (p_action = 'INSERT') THEN
214       -- Call all the procedures related to Before Insert.
215       IF ( get_pk_for_validation(
216              new_references.crsp_id
217            )
218          ) THEN
219         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
220         igs_ge_msg_stack.add;
221         app_exception.raise_exception;
222       END IF;
223       check_parent_existance;
224     ELSIF (p_action = 'UPDATE') THEN
225       -- Call all the procedures related to Before Update.
226       check_parent_existance;
227     ELSIF (p_action = 'VALIDATE_INSERT') THEN
228       -- Call all the procedures related to Before Insert.
229       IF ( get_pk_for_validation (
230              new_references.crsp_id
231            )
232          ) THEN
233         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
234         igs_ge_msg_stack.add;
235         app_exception.raise_exception;
236       END IF;
237     END IF;
238 
239   END before_dml;
240 
241 
242   PROCEDURE insert_row (
243     x_rowid                             IN OUT NOCOPY VARCHAR2,
244     x_crsp_id                           IN OUT NOCOPY NUMBER,
245     x_base_id                           IN     NUMBER,
246     x_flag                              IN     VARCHAR2,
247     x_line_number                       IN     NUMBER,
248     x_line_data                         IN     VARCHAR2,
249     x_mode                              IN     VARCHAR2 DEFAULT 'R'
250   ) AS
251   /*
252   ||  Created By : prchandr
253   ||  Created On : 01-JUN-2001
254   ||  Purpose : Handles the INSERT DML logic for the table.
255   ||  Known limitations, enhancements or remarks :
256   ||  Change History :
257   ||  Who             When            What
258   ||  (reverse chronological order - newest change first)
259   */
260     CURSOR c IS
261       SELECT   rowid
262       FROM     igf_aw_corresp_all
263       WHERE    crsp_id                           = x_crsp_id;
264 
265     x_last_update_date           DATE;
266     x_last_updated_by            NUMBER;
267     x_last_update_login          NUMBER;
268     x_request_id                 NUMBER;
269     x_program_id                 NUMBER;
270     x_program_application_id     NUMBER;
271     x_program_update_date        DATE;
272 
273   BEGIN
274 
275     x_last_update_date := SYSDATE;
276     IF (x_mode = 'I') THEN
277       x_last_updated_by := 1;
278       x_last_update_login := 0;
279     ELSIF (x_mode = 'R') THEN
280       x_last_updated_by := fnd_global.user_id;
281       IF (x_last_updated_by IS NULL) THEN
282         x_last_updated_by := -1;
283       END IF;
284       x_last_update_login := fnd_global.login_id;
285       IF (x_last_update_login IS NULL) THEN
286         x_last_update_login := -1;
287       END IF;
288       x_request_id             := fnd_global.conc_request_id;
289       x_program_id             := fnd_global.conc_program_id;
290       x_program_application_id := fnd_global.prog_appl_id;
291 
292       IF (x_request_id = -1) THEN
293         x_request_id             := NULL;
294         x_program_id             := NULL;
295         x_program_application_id := NULL;
296         x_program_update_date    := NULL;
297       ELSE
298         x_program_update_date    := SYSDATE;
299       END IF;
300     ELSE
301       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
302       igs_ge_msg_stack.add;
303       app_exception.raise_exception;
304     END IF;
305 
306     SELECT    igf_aw_corresp_s.NEXTVAL
307     INTO      x_crsp_id
308     FROM      dual;
309 
310     new_references.org_id := igs_ge_gen_003.get_org_id;
311 
312     before_dml(
313       p_action                            => 'INSERT',
314       x_rowid                             => x_rowid,
315       x_crsp_id                           => x_crsp_id,
316       x_base_id                           => x_base_id,
317       x_flag                              => x_flag,
318       x_line_number                       => x_line_number,
319       x_line_data                         => x_line_data,
320       x_creation_date                     => x_last_update_date,
321       x_created_by                        => x_last_updated_by,
322       x_last_update_date                  => x_last_update_date,
323       x_last_updated_by                   => x_last_updated_by,
324       x_last_update_login                 => x_last_update_login
325     );
326 
327     INSERT INTO igf_aw_corresp_all (
328       crsp_id,
329       base_id,
330       flag,
331       line_number,
332       line_data,
333       org_id,
334       creation_date,
335       created_by,
336       last_update_date,
337       last_updated_by,
338       last_update_login,
339       request_id,
340       program_id,
341       program_application_id,
342       program_update_date
343     ) VALUES (
344       new_references.crsp_id,
345       new_references.base_id,
346       new_references.flag,
347       new_references.line_number,
348       new_references.line_data,
349       new_references.org_id,
350       x_last_update_date,
351       x_last_updated_by,
352       x_last_update_date,
356       x_program_id,
353       x_last_updated_by,
354       x_last_update_login ,
355       x_request_id,
357       x_program_application_id,
358       x_program_update_date
359     );
360 
361     OPEN c;
362     FETCH c INTO x_rowid;
363     IF (c%NOTFOUND) THEN
364       CLOSE c;
365       RAISE NO_DATA_FOUND;
366     END IF;
367     CLOSE c;
368 
369   END insert_row;
370 
371 
372   PROCEDURE lock_row (
373     x_rowid                             IN     VARCHAR2,
374     x_crsp_id                           IN     NUMBER,
375     x_base_id                           IN     NUMBER,
376     x_flag                              IN     VARCHAR2,
377     x_line_number                       IN     NUMBER,
378     x_line_data                         IN     VARCHAR2
379   ) AS
380   /*
381   ||  Created By : prchandr
382   ||  Created On : 01-JUN-2001
383   ||  Purpose : Handles the LOCK mechanism for the table.
384   ||  Known limitations, enhancements or remarks :
385   ||  Change History :
386   ||  Who             When            What
387   ||  (reverse chronological order - newest change first)
388   */
389     CURSOR c1 IS
390       SELECT
391         base_id,
392         flag,
393         line_number,
394         line_data
395       FROM  igf_aw_corresp_all
396       WHERE rowid = x_rowid
397       FOR UPDATE NOWAIT;
398 
399     tlinfo c1%ROWTYPE;
400 
401   BEGIN
402 
403     OPEN c1;
404     FETCH c1 INTO tlinfo;
405     IF (c1%notfound) THEN
406       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
407       igs_ge_msg_stack.add;
408       CLOSE c1;
409       app_exception.raise_exception;
410       RETURN;
411     END IF;
412     CLOSE c1;
413 
414     IF (
415         (tlinfo.base_id = x_base_id)
416         AND (tlinfo.flag = x_flag)
417         AND (tlinfo.line_number = x_line_number)
418         AND ((tlinfo.line_data = x_line_data) OR ((tlinfo.line_data IS NULL) AND (X_line_data IS NULL)))
419        ) THEN
420       NULL;
421     ELSE
422       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
423       igs_ge_msg_stack.add;
424       app_exception.raise_exception;
425     END IF;
426 
427     RETURN;
428 
429   END lock_row;
430 
431 
432   PROCEDURE update_row (
433     x_rowid                             IN     VARCHAR2,
434     x_crsp_id                           IN     NUMBER,
435     x_base_id                           IN     NUMBER,
436     x_flag                              IN     VARCHAR2,
437     x_line_number                       IN     NUMBER,
438     x_line_data                         IN     VARCHAR2,
439     x_mode                              IN     VARCHAR2 DEFAULT 'R'
440   ) AS
441   /*
442   ||  Created By : prchandr
443   ||  Created On : 01-JUN-2001
444   ||  Purpose : Handles the UPDATE DML logic for the table.
445   ||  Known limitations, enhancements or remarks :
446   ||  Change History :
447   ||  Who             When            What
448   ||  (reverse chronological order - newest change first)
449   */
450     x_last_update_date           DATE ;
451     x_last_updated_by            NUMBER;
452     x_last_update_login          NUMBER;
453     x_request_id                 NUMBER;
454     x_program_id                 NUMBER;
455     x_program_application_id     NUMBER;
456     x_program_update_date        DATE;
457 
458   BEGIN
459 
460     x_last_update_date := SYSDATE;
461     IF (X_MODE = 'I') THEN
462       x_last_updated_by := 1;
463       x_last_update_login := 0;
464     ELSIF (x_mode = 'R') THEN
465       x_last_updated_by := fnd_global.user_id;
466       IF x_last_updated_by IS NULL THEN
467         x_last_updated_by := -1;
468       END IF;
469       x_last_update_login := fnd_global.login_id;
470       IF (x_last_update_login IS NULL) THEN
471         x_last_update_login := -1;
472       END IF;
473     ELSE
474       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
475       igs_ge_msg_stack.add;
476       app_exception.raise_exception;
477     END IF;
478 
479     before_dml(
480       p_action                            => 'UPDATE',
481       x_rowid                             => x_rowid,
482       x_crsp_id                           => x_crsp_id,
483       x_base_id                           => x_base_id,
484       x_flag                              => x_flag,
485       x_line_number                       => x_line_number,
486       x_line_data                         => x_line_data,
487       x_creation_date                     => x_last_update_date,
488       x_created_by                        => x_last_updated_by,
489       x_last_update_date                  => x_last_update_date,
490       x_last_updated_by                   => x_last_updated_by,
491       x_last_update_login                 => x_last_update_login
492     );
493 
494     IF (x_mode = 'R') THEN
495       x_request_id := fnd_global.conc_request_id;
496       x_program_id := fnd_global.conc_program_id;
497       x_program_application_id := fnd_global.prog_appl_id;
498       IF (x_request_id =  -1) THEN
499         x_request_id := old_references.request_id;
500         x_program_id := old_references.program_id;
501         x_program_application_id := old_references.program_application_id;
502         x_program_update_date := old_references.program_update_date;
503       ELSE
504         x_program_update_date := SYSDATE;
505       END IF;
506     END IF;
510         base_id                           = new_references.base_id,
507 
508     UPDATE igf_aw_corresp_all
509       SET
511         flag                              = new_references.flag,
512         line_number                       = new_references.line_number,
513         line_data                         = new_references.line_data,
514         last_update_date                  = x_last_update_date,
515         last_updated_by                   = x_last_updated_by,
516         last_update_login                 = x_last_update_login ,
517         request_id                        = x_request_id,
518         program_id                        = x_program_id,
519         program_application_id            = x_program_application_id,
520         program_update_date               = x_program_update_date
521       WHERE rowid = x_rowid;
522 
523     IF (SQL%NOTFOUND) THEN
524       RAISE NO_DATA_FOUND;
525     END IF;
526 
527   END update_row;
528 
529 
530   PROCEDURE add_row (
531     x_rowid                             IN OUT NOCOPY VARCHAR2,
532     x_crsp_id                           IN OUT NOCOPY NUMBER,
533     x_base_id                           IN     NUMBER,
534     x_flag                              IN     VARCHAR2,
535     x_line_number                       IN     NUMBER,
536     x_line_data                         IN     VARCHAR2,
537     x_mode                              IN     VARCHAR2 DEFAULT 'R'
538   ) AS
539   /*
540   ||  Created By : prchandr
541   ||  Created On : 01-JUN-2001
542   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
543   ||  Known limitations, enhancements or remarks :
544   ||  Change History :
545   ||  Who             When            What
546   ||  (reverse chronological order - newest change first)
547   */
548     CURSOR c1 IS
549       SELECT   rowid
550       FROM     igf_aw_corresp_all
551       WHERE    crsp_id                           = x_crsp_id;
552 
553   BEGIN
554 
555     OPEN c1;
556     FETCH c1 INTO x_rowid;
557     IF (c1%NOTFOUND) THEN
558       CLOSE c1;
559 
560       insert_row (
561         x_rowid,
562         x_crsp_id,
563         x_base_id,
564         x_flag,
565         x_line_number,
566         x_line_data,
567         x_mode
568       );
569       RETURN;
570     END IF;
571     CLOSE c1;
572 
573     update_row (
574       x_rowid,
575       x_crsp_id,
576       x_base_id,
577       x_flag,
578       x_line_number,
579       x_line_data,
580       x_mode
581     );
582 
583   END add_row;
584 
585 
586   PROCEDURE delete_row (
587     x_rowid IN VARCHAR2
588   ) AS
589   /*
590   ||  Created By : prchandr
591   ||  Created On : 01-JUN-2001
592   ||  Purpose : Handles the DELETE DML logic for the table.
593   ||  Known limitations, enhancements or remarks :
594   ||  Change History :
595   ||  Who             When            What
596   ||  (reverse chronological order - newest change first)
597   */
598   BEGIN
599 
600     before_dml (
601       p_action => 'DELETE',
602       x_rowid => x_rowid
603     );
604 
605     DELETE FROM igf_aw_corresp_all
606     WHERE rowid = x_rowid;
607 
608     IF (SQL%NOTFOUND) THEN
609       RAISE NO_DATA_FOUND;
610     END IF;
611 
612   END delete_row;
613 
614 
615 END igf_aw_corresp_pkg;