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_BRC_PKG

Source


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