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_SERVICER_PKG

Source


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