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_SL_GUARANTOR_PKG

Source


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