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_LENDER_PKG

Source


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