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

Source


1 PACKAGE BODY igs_fi_pp_ins_appls_pkg AS
2 /* $Header: IGSSIE2B.pls 115.1 2003/09/16 12:25:32 smvk noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_fi_pp_ins_appls%ROWTYPE;
6   new_references igs_fi_pp_ins_appls%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_installment_application_id        IN     NUMBER,
12     x_application_type_code             IN     VARCHAR2,
13     x_installment_id                    IN     NUMBER,
14     x_credit_id                         IN     NUMBER,
15     x_credit_activity_id                IN     NUMBER,
16     x_applied_amt                       IN     NUMBER,
17     x_transaction_date                    IN     DATE,
18     x_link_application_id               IN     NUMBER,
19     x_creation_date                     IN     DATE,
20     x_created_by                        IN     NUMBER,
21     x_last_update_date                  IN     DATE,
22     x_last_updated_by                   IN     NUMBER,
23     x_last_update_login                 IN     NUMBER
24   ) AS
25   /*
26   ||  Created By : smvk
27   ||  Created On : 24-AUG-2003
28   ||  Purpose : Initialises the Old and New references for the columns of the table.
29   ||  Known limitations, enhancements or remarks :
30   ||  Change History :
31   ||  Who             When            What
32   ||  (reverse chronological order - newest change first)
33   */
34 
35     CURSOR cur_old_ref_values IS
36       SELECT   *
37       FROM     igs_fi_pp_ins_appls
38       WHERE    rowid = x_rowid;
39 
40   BEGIN
41 
42     l_rowid := x_rowid;
43 
44     -- Code for setting the Old and New Reference Values.
45     -- Populate Old Values.
46     OPEN cur_old_ref_values;
47     FETCH cur_old_ref_values INTO old_references;
48     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
49       CLOSE cur_old_ref_values;
50       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
51       igs_ge_msg_stack.add;
52       app_exception.raise_exception;
53       RETURN;
54     END IF;
55     CLOSE cur_old_ref_values;
56 
57     -- Populate New Values.
58     new_references.installment_application_id        := x_installment_application_id;
59     new_references.application_type_code             := x_application_type_code;
60     new_references.installment_id                    := x_installment_id;
61     new_references.credit_id                         := x_credit_id;
62     new_references.credit_activity_id                := x_credit_activity_id;
63     new_references.applied_amt                       := x_applied_amt;
64     new_references.transaction_date                    := x_transaction_date;
65     new_references.link_application_id               := x_link_application_id;
66 
67     IF (p_action = 'UPDATE') THEN
68       new_references.creation_date                   := old_references.creation_date;
69       new_references.created_by                      := old_references.created_by;
70     ELSE
71       new_references.creation_date                   := x_creation_date;
72       new_references.created_by                      := x_created_by;
73     END IF;
74 
75     new_references.last_update_date                  := x_last_update_date;
76     new_references.last_updated_by                   := x_last_updated_by;
77     new_references.last_update_login                 := x_last_update_login;
78 
79   END set_column_values;
80 
81 
82   PROCEDURE check_parent_existance AS
83   /*
84   ||  Created By : smvk
85   ||  Created On : 24-AUG-2003
86   ||  Purpose : Checks for the existance of Parent records.
87   ||  Known limitations, enhancements or remarks :
88   ||  Change History :
89   ||  Who             When            What
90   ||  (reverse chronological order - newest change first)
91   */
92   BEGIN
93 
94     IF (((old_references.installment_id = new_references.installment_id)) OR
95         ((new_references.installment_id IS NULL))) THEN
96       NULL;
97     ELSIF NOT igs_fi_pp_instlmnts_pkg.get_pk_for_validation (
98                 new_references.installment_id
99               ) THEN
100       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
101       igs_ge_msg_stack.add;
102       app_exception.raise_exception;
103     END IF;
104 
105     IF (((old_references.credit_id = new_references.credit_id)) OR
106         ((new_references.credit_id IS NULL))) THEN
107       NULL;
108     ELSIF NOT igs_fi_credits_pkg.get_pk_for_validation (
109                 new_references.credit_id
110               ) THEN
111       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
112       igs_ge_msg_stack.add;
113       app_exception.raise_exception;
114     END IF;
115 
116     IF (((old_references.credit_activity_id = new_references.credit_activity_id)) OR
117         ((new_references.credit_activity_id IS NULL))) THEN
118       NULL;
119     ELSIF NOT igs_fi_cr_activities_pkg.get_pk_for_validation (
120                 new_references.credit_activity_id
121               ) THEN
122       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
123       igs_ge_msg_stack.add;
124       app_exception.raise_exception;
125     END IF;
126 
127   END check_parent_existance;
128 
129 
130   FUNCTION get_pk_for_validation (
131     x_installment_application_id        IN     NUMBER
132   ) RETURN BOOLEAN AS
133   /*
134   ||  Created By : smvk
135   ||  Created On : 24-AUG-2003
136   ||  Purpose : Validates the Primary Key of the table.
137   ||  Known limitations, enhancements or remarks :
138   ||  Change History :
139   ||  Who             When            What
140   ||  (reverse chronological order - newest change first)
141   */
142     CURSOR cur_rowid IS
143       SELECT   rowid
144       FROM     igs_fi_pp_ins_appls
145       WHERE    installment_application_id = x_installment_application_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_igs_fi_pp_instlmnts (
166     x_installment_id                    IN     NUMBER
167   ) AS
168   /*
169   ||  Created By : smvk
170   ||  Created On : 24-AUG-2003
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     igs_fi_pp_ins_appls
180       WHERE   ((installment_id = x_installment_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 ('IGS', 'IGS_FI_PPIA_PPIN_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_igs_fi_pp_instlmnts;
198 
199   PROCEDURE before_dml (
200     p_action                            IN     VARCHAR2,
201     x_rowid                             IN     VARCHAR2,
202     x_installment_application_id        IN     NUMBER,
203     x_application_type_code             IN     VARCHAR2,
204     x_installment_id                    IN     NUMBER,
205     x_credit_id                         IN     NUMBER,
206     x_credit_activity_id                IN     NUMBER,
207     x_applied_amt                       IN     NUMBER,
208     x_transaction_date                    IN     DATE,
209     x_link_application_id               IN     NUMBER,
210     x_creation_date                     IN     DATE,
211     x_created_by                        IN     NUMBER,
212     x_last_update_date                  IN     DATE,
213     x_last_updated_by                   IN     NUMBER,
214     x_last_update_login                 IN     NUMBER
215   ) AS
216   /*
217   ||  Created By : smvk
218   ||  Created On : 24-AUG-2003
219   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
220   ||            Trigger Handlers for the table, before any DML operation.
221   ||  Known limitations, enhancements or remarks :
222   ||  Change History :
223   ||  Who             When            What
224   ||  (reverse chronological order - newest change first)
225   */
226   BEGIN
227 
228     set_column_values (
229       p_action,
230       x_rowid,
231       x_installment_application_id,
232       x_application_type_code,
233       x_installment_id,
234       x_credit_id,
235       x_credit_activity_id,
236       x_applied_amt,
237       x_transaction_date,
238       x_link_application_id,
239       x_creation_date,
240       x_created_by,
241       x_last_update_date,
242       x_last_updated_by,
243       x_last_update_login
244     );
245 
246     IF (p_action = 'INSERT') THEN
247       -- Call all the procedures related to Before Insert.
248       IF ( get_pk_for_validation(
249              new_references.installment_application_id
250            )
251          ) THEN
252         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
253         igs_ge_msg_stack.add;
254         app_exception.raise_exception;
255       END IF;
256       check_parent_existance;
257     ELSIF (p_action = 'UPDATE') THEN
258       -- Call all the procedures related to Before Update.
259       check_parent_existance;
260     ELSIF (p_action = 'VALIDATE_INSERT') THEN
261       -- Call all the procedures related to Before Insert.
262       IF ( get_pk_for_validation (
263              new_references.installment_application_id
264            )
265          ) THEN
266         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
267         igs_ge_msg_stack.add;
268         app_exception.raise_exception;
269       END IF;
270     END IF;
271 
272   END before_dml;
273 
274 
275   PROCEDURE insert_row (
276     x_rowid                             IN OUT NOCOPY VARCHAR2,
277     x_installment_application_id        IN OUT NOCOPY NUMBER,
278     x_application_type_code             IN     VARCHAR2,
279     x_installment_id                    IN     NUMBER,
280     x_credit_id                         IN     NUMBER,
281     x_credit_activity_id                IN     NUMBER,
282     x_applied_amt                       IN     NUMBER,
283     x_transaction_date                    IN     DATE,
284     x_link_application_id               IN     NUMBER,
285     x_mode                              IN     VARCHAR2
286   ) AS
287   /*
288   ||  Created By : smvk
289   ||  Created On : 24-AUG-2003
290   ||  Purpose : Handles the INSERT DML logic for the table.
291   ||  Known limitations, enhancements or remarks :
292   ||  Change History :
293   ||  Who             When            What
294   ||  (reverse chronological order - newest change first)
295   */
296 
297     x_last_update_date           DATE;
298     x_last_updated_by            NUMBER;
299     x_last_update_login          NUMBER;
300     x_request_id                 NUMBER;
301     x_program_id                 NUMBER;
302     x_program_application_id     NUMBER;
303     x_program_update_date        DATE;
304 
305   BEGIN
306 
307     x_last_update_date := SYSDATE;
308     IF (x_mode = 'I') THEN
309       x_last_updated_by := 1;
310       x_last_update_login := 0;
311     ELSIF (x_mode = 'R') THEN
312       x_last_updated_by := fnd_global.user_id;
313       IF (x_last_updated_by IS NULL) THEN
314         x_last_updated_by := -1;
315       END IF;
316       x_last_update_login := fnd_global.login_id;
317       IF (x_last_update_login IS NULL) THEN
318         x_last_update_login := -1;
319       END IF;
320       x_request_id             := fnd_global.conc_request_id;
321       x_program_id             := fnd_global.conc_program_id;
322       x_program_application_id := fnd_global.prog_appl_id;
323 
324       IF (x_request_id = -1) THEN
325         x_request_id             := NULL;
326         x_program_id             := NULL;
327         x_program_application_id := NULL;
328         x_program_update_date    := NULL;
329       ELSE
330         x_program_update_date    := SYSDATE;
331       END IF;
332     ELSE
333       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
334       fnd_message.set_token ('ROUTINE', 'IGS_FI_PP_INS_APPLS_PKG.INSERT_ROW');
335       igs_ge_msg_stack.add;
336       app_exception.raise_exception;
337     END IF;
338 
339     x_installment_application_id := NULL;
340 
341     before_dml(
342       p_action                            => 'INSERT',
343       x_rowid                             => x_rowid,
344       x_installment_application_id        => x_installment_application_id,
345       x_application_type_code             => x_application_type_code,
346       x_installment_id                    => x_installment_id,
347       x_credit_id                         => x_credit_id,
348       x_credit_activity_id                => x_credit_activity_id,
349       x_applied_amt                       => x_applied_amt,
350       x_transaction_date                    => x_transaction_date,
351       x_link_application_id               => x_link_application_id,
352       x_creation_date                     => x_last_update_date,
353       x_created_by                        => x_last_updated_by,
354       x_last_update_date                  => x_last_update_date,
355       x_last_updated_by                   => x_last_updated_by,
356       x_last_update_login                 => x_last_update_login
357     );
358 
359     INSERT INTO igs_fi_pp_ins_appls (
360       installment_application_id,
361       application_type_code,
362       installment_id,
363       credit_id,
364       credit_activity_id,
365       applied_amt,
366       transaction_date,
367       link_application_id,
368       creation_date,
369       created_by,
370       last_update_date,
371       last_updated_by,
372       last_update_login,
373       request_id,
374       program_id,
375       program_application_id,
376       program_update_date
377     ) VALUES (
378       igs_fi_pp_ins_appls_s.NEXTVAL,
379       new_references.application_type_code,
380       new_references.installment_id,
381       new_references.credit_id,
382       new_references.credit_activity_id,
383       new_references.applied_amt,
384       new_references.transaction_date,
385       new_references.link_application_id,
386       x_last_update_date,
387       x_last_updated_by,
388       x_last_update_date,
389       x_last_updated_by,
390       x_last_update_login ,
391       x_request_id,
392       x_program_id,
393       x_program_application_id,
394       x_program_update_date
395     ) RETURNING ROWID, installment_application_id INTO x_rowid, x_installment_application_id;
396 
397   END insert_row;
398 
399 
400   PROCEDURE lock_row (
401     x_rowid                             IN     VARCHAR2,
402     x_installment_application_id        IN     NUMBER,
403     x_application_type_code             IN     VARCHAR2,
404     x_installment_id                    IN     NUMBER,
405     x_credit_id                         IN     NUMBER,
406     x_credit_activity_id                IN     NUMBER,
407     x_applied_amt                       IN     NUMBER,
408     x_transaction_date                    IN     DATE,
409     x_link_application_id               IN     NUMBER
410   ) AS
411   /*
412   ||  Created By : smvk
413   ||  Created On : 24-AUG-2003
414   ||  Purpose : Handles the LOCK mechanism for the table.
415   ||  Known limitations, enhancements or remarks :
416   ||  Change History :
417   ||  Who             When            What
418   ||  (reverse chronological order - newest change first)
419   */
420     CURSOR c1 IS
421       SELECT
422         application_type_code,
423         installment_id,
424         credit_id,
425         credit_activity_id,
426         applied_amt,
427         transaction_date,
428         link_application_id
429       FROM  igs_fi_pp_ins_appls
430       WHERE rowid = x_rowid
431       FOR UPDATE NOWAIT;
432 
433     tlinfo c1%ROWTYPE;
434 
435   BEGIN
436 
437     OPEN c1;
438     FETCH c1 INTO tlinfo;
439     IF (c1%notfound) THEN
440       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
441       igs_ge_msg_stack.add;
442       CLOSE c1;
443       app_exception.raise_exception;
444       RETURN;
445     END IF;
446     CLOSE c1;
447 
448     IF (
449         (tlinfo.application_type_code = x_application_type_code)
450         AND (tlinfo.installment_id = x_installment_id)
451         AND (tlinfo.credit_id = x_credit_id)
452         AND (tlinfo.credit_activity_id = x_credit_activity_id)
453         AND (tlinfo.applied_amt = x_applied_amt)
454         AND (tlinfo.transaction_date = x_transaction_date)
455         AND ((tlinfo.link_application_id = x_link_application_id) OR ((tlinfo.link_application_id IS NULL) AND (X_link_application_id IS NULL)))
456        ) THEN
457       NULL;
458     ELSE
459       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
460       igs_ge_msg_stack.add;
461       app_exception.raise_exception;
462     END IF;
463 
464     RETURN;
465 
466   END lock_row;
467 
468 
469   PROCEDURE update_row (
470     x_rowid                             IN     VARCHAR2,
471     x_installment_application_id        IN     NUMBER,
472     x_application_type_code             IN     VARCHAR2,
473     x_installment_id                    IN     NUMBER,
474     x_credit_id                         IN     NUMBER,
475     x_credit_activity_id                IN     NUMBER,
476     x_applied_amt                       IN     NUMBER,
477     x_transaction_date                    IN     DATE,
478     x_link_application_id               IN     NUMBER,
479     x_mode                              IN     VARCHAR2
480   ) AS
481   /*
482   ||  Created By : smvk
483   ||  Created On : 24-AUG-2003
484   ||  Purpose : Handles the UPDATE DML logic for the table.
485   ||  Known limitations, enhancements or remarks :
486   ||  Change History :
487   ||  Who             When            What
488   ||  (reverse chronological order - newest change first)
489   */
490     x_last_update_date           DATE ;
491     x_last_updated_by            NUMBER;
492     x_last_update_login          NUMBER;
493     x_request_id                 NUMBER;
494     x_program_id                 NUMBER;
495     x_program_application_id     NUMBER;
496     x_program_update_date        DATE;
497 
498   BEGIN
499 
500     x_last_update_date := SYSDATE;
501     IF (X_MODE = 'I') THEN
502       x_last_updated_by := 1;
503       x_last_update_login := 0;
504     ELSIF (x_mode = 'R') THEN
505       x_last_updated_by := fnd_global.user_id;
506       IF x_last_updated_by IS NULL THEN
507         x_last_updated_by := -1;
508       END IF;
509       x_last_update_login := fnd_global.login_id;
510       IF (x_last_update_login IS NULL) THEN
511         x_last_update_login := -1;
512       END IF;
513     ELSE
514       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
515       fnd_message.set_token ('ROUTINE', 'IGS_FI_PP_INS_APPLS_PKG.UPDATE_ROW');
516       igs_ge_msg_stack.add;
517       app_exception.raise_exception;
518     END IF;
519 
520     before_dml(
521       p_action                            => 'UPDATE',
522       x_rowid                             => x_rowid,
523       x_installment_application_id        => x_installment_application_id,
524       x_application_type_code             => x_application_type_code,
525       x_installment_id                    => x_installment_id,
526       x_credit_id                         => x_credit_id,
527       x_credit_activity_id                => x_credit_activity_id,
528       x_applied_amt                       => x_applied_amt,
529       x_transaction_date                    => x_transaction_date,
530       x_link_application_id               => x_link_application_id,
531       x_creation_date                     => x_last_update_date,
532       x_created_by                        => x_last_updated_by,
533       x_last_update_date                  => x_last_update_date,
534       x_last_updated_by                   => x_last_updated_by,
535       x_last_update_login                 => x_last_update_login
536     );
537 
538     IF (x_mode = 'R') THEN
539       x_request_id := fnd_global.conc_request_id;
540       x_program_id := fnd_global.conc_program_id;
541       x_program_application_id := fnd_global.prog_appl_id;
542       IF (x_request_id =  -1) THEN
543         x_request_id := old_references.request_id;
544         x_program_id := old_references.program_id;
545         x_program_application_id := old_references.program_application_id;
546         x_program_update_date := old_references.program_update_date;
547       ELSE
548         x_program_update_date := SYSDATE;
549       END IF;
550     END IF;
551 
552     UPDATE igs_fi_pp_ins_appls
553       SET
554         application_type_code             = new_references.application_type_code,
555         installment_id                    = new_references.installment_id,
556         credit_id                         = new_references.credit_id,
557         credit_activity_id                = new_references.credit_activity_id,
558         applied_amt                       = new_references.applied_amt,
559         transaction_date                    = new_references.transaction_date,
560         link_application_id               = new_references.link_application_id,
561         last_update_date                  = x_last_update_date,
562         last_updated_by                   = x_last_updated_by,
563         last_update_login                 = x_last_update_login ,
564         request_id                        = x_request_id,
565         program_id                        = x_program_id,
566         program_application_id            = x_program_application_id,
567         program_update_date               = x_program_update_date
568       WHERE rowid = x_rowid;
569 
570     IF (SQL%NOTFOUND) THEN
571       RAISE NO_DATA_FOUND;
572     END IF;
573 
574   END update_row;
575 
576 
577   PROCEDURE add_row (
578     x_rowid                             IN OUT NOCOPY VARCHAR2,
579     x_installment_application_id        IN OUT NOCOPY NUMBER,
580     x_application_type_code             IN     VARCHAR2,
581     x_installment_id                    IN     NUMBER,
582     x_credit_id                         IN     NUMBER,
583     x_credit_activity_id                IN     NUMBER,
584     x_applied_amt                       IN     NUMBER,
585     x_transaction_date                    IN     DATE,
586     x_link_application_id               IN     NUMBER,
587     x_mode                              IN     VARCHAR2
588   ) AS
589   /*
590   ||  Created By : smvk
591   ||  Created On : 24-AUG-2003
592   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
593   ||  Known limitations, enhancements or remarks :
594   ||  Change History :
595   ||  Who             When            What
596   ||  (reverse chronological order - newest change first)
597   */
598     CURSOR c1 IS
599       SELECT   rowid
600       FROM     igs_fi_pp_ins_appls
601       WHERE    installment_application_id        = x_installment_application_id;
602 
603   BEGIN
604 
605     OPEN c1;
606     FETCH c1 INTO x_rowid;
607     IF (c1%NOTFOUND) THEN
608       CLOSE c1;
609 
610       insert_row (
611         x_rowid,
612         x_installment_application_id,
613         x_application_type_code,
614         x_installment_id,
615         x_credit_id,
616         x_credit_activity_id,
617         x_applied_amt,
618         x_transaction_date,
619         x_link_application_id,
620         x_mode
621       );
622       RETURN;
623     END IF;
624     CLOSE c1;
625 
626     update_row (
627       x_rowid,
628       x_installment_application_id,
629       x_application_type_code,
630       x_installment_id,
631       x_credit_id,
632       x_credit_activity_id,
633       x_applied_amt,
634       x_transaction_date,
635       x_link_application_id,
636       x_mode
637     );
638 
639   END add_row;
640 
641 
642   PROCEDURE delete_row (
643     x_rowid IN VARCHAR2
644   ) AS
645   /*
646   ||  Created By : smvk
647   ||  Created On : 24-AUG-2003
648   ||  Purpose : Handles the DELETE DML logic for the table.
649   ||  Known limitations, enhancements or remarks :
650   ||  Change History :
651   ||  Who             When            What
652   ||  (reverse chronological order - newest change first)
653   */
654   BEGIN
655 
656     before_dml (
657       p_action => 'DELETE',
658       x_rowid => x_rowid
659     );
660 
661     DELETE FROM igs_fi_pp_ins_appls
662     WHERE rowid = x_rowid;
663 
664     IF (SQL%NOTFOUND) THEN
665       RAISE NO_DATA_FOUND;
666     END IF;
667 
668   END delete_row;
669 
670 
671 END igs_fi_pp_ins_appls_pkg;