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_PNOTE_STAT_H_PKG

Source


1 PACKAGE BODY igf_sl_pnote_stat_h_pkg AS
2 /* $Header: IGFLI28B.pls 115.5 2002/11/28 14:27:52 nsidana noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_sl_pnote_stat_h%ROWTYPE;
6   new_references igf_sl_pnote_stat_h%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_dlpnh_id                          IN     NUMBER      DEFAULT NULL,
12     x_loan_id                           IN     NUMBER      DEFAULT NULL,
13     x_pnote_status                      IN     VARCHAR2    DEFAULT NULL,
14     x_pnote_status_date                 IN     DATE        DEFAULT NULL,
15     x_creation_date                     IN     DATE        DEFAULT NULL,
16     x_created_by                        IN     NUMBER      DEFAULT NULL,
17     x_last_update_date                  IN     DATE        DEFAULT NULL,
18     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
19     x_last_update_login                 IN     NUMBER      DEFAULT NULL
20   ) AS
21   /*
22   ||  Created By : viramali
23   ||  Created On : 10-MAY-2001
24   ||  Purpose : Initialises the Old and New references for the columns of the table.
25   ||  Known limitations, enhancements or remarks :
26   ||  Change History :
27   ||  Who             When            What
28   ||  (reverse chronological order - newest change first)
29   */
30 
31     CURSOR cur_old_ref_values IS
32       SELECT   *
33       FROM     IGF_SL_PNOTE_STAT_H
34       WHERE    rowid = x_rowid;
35 
36   BEGIN
37 
38     l_rowid := x_rowid;
39 
40     -- Code for setting the Old and New Reference Values.
41     -- Populate Old Values.
42     OPEN cur_old_ref_values;
43     FETCH cur_old_ref_values INTO old_references;
44     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45       CLOSE cur_old_ref_values;
46       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47       igs_ge_msg_stack.add;
48       app_exception.raise_exception;
49       RETURN;
50     END IF;
51     CLOSE cur_old_ref_values;
52 
53     -- Populate New Values.
54     new_references.dlpnh_id                          := x_dlpnh_id;
55     new_references.loan_id                           := x_loan_id;
56     new_references.pnote_status                      := x_pnote_status;
57     new_references.pnote_status_date                 := x_pnote_status_date;
58 
59     IF (p_action = 'UPDATE') THEN
60       new_references.creation_date                   := old_references.creation_date;
61       new_references.created_by                      := old_references.created_by;
62     ELSE
63       new_references.creation_date                   := x_creation_date;
64       new_references.created_by                      := x_created_by;
65     END IF;
66 
67     new_references.last_update_date                  := x_last_update_date;
68     new_references.last_updated_by                   := x_last_updated_by;
69     new_references.last_update_login                 := x_last_update_login;
70 
71   END set_column_values;
72 
73 
74   PROCEDURE check_parent_existance AS
75   /*
76   ||  Created By : viramali
77   ||  Created On : 10-MAY-2001
78   ||  Purpose : Checks for the existance of Parent records.
79   ||  Known limitations, enhancements or remarks :
80   ||  Change History :
81   ||  Who             When            What
82   ||  (reverse chronological order - newest change first)
83   */
84   BEGIN
85 
86     IF (((old_references.loan_id = new_references.loan_id)) OR
87         ((new_references.loan_id IS NULL))) THEN
88       NULL;
89     ELSIF NOT igf_sl_lor_pkg.get_uk_For_validation (
90                 new_references.loan_id
91               ) THEN
92       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
93       igs_ge_msg_stack.add;
94       app_exception.raise_exception;
95     END IF;
96 
97   END check_parent_existance;
98 
99 
100   FUNCTION get_pk_for_validation (
101     x_dlpnh_id                          IN     NUMBER
102   ) RETURN BOOLEAN AS
103   /*
104   ||  Created By : viramali
105   ||  Created On : 10-MAY-2001
106   ||  Purpose : Validates the Primary Key of the table.
107   ||  Known limitations, enhancements or remarks :
108   ||  Change History :
109   ||  Who             When            What
110   ||  (reverse chronological order - newest change first)
111   */
112     CURSOR cur_rowid IS
113       SELECT   rowid
114       FROM     igf_sl_pnote_stat_h
115       WHERE    dlpnh_id = x_dlpnh_id
116       FOR UPDATE NOWAIT;
117 
118     lv_rowid cur_rowid%RowType;
119 
120   BEGIN
121 
122     OPEN cur_rowid;
123     FETCH cur_rowid INTO lv_rowid;
124     IF (cur_rowid%FOUND) THEN
125       CLOSE cur_rowid;
126       RETURN(TRUE);
127     ELSE
128       CLOSE cur_rowid;
129       RETURN(FALSE);
130     END IF;
131 
132   END get_pk_for_validation;
133 
134 
135   PROCEDURE get_ufk_igf_sl_lor (
136     x_loan_id                           IN     NUMBER
137   ) AS
138   /*
139   ||  Created By : viramali
140   ||  Created On : 10-MAY-2001
141   ||  Purpose : Validates the Foreign Keys for the table.
142   ||  Known limitations, enhancements or remarks :
143   ||  Change History :
144   ||  Bug : 2413695 IGF Messages Issues
145   ||  Who             When            What
146   ||  mesriniv        13-jun-2002    changed msg name -IGF_DLPNH_LOR_FK
147   ||  (reverse chronological order - newest change first)
148   */
149     CURSOR cur_rowid IS
150       SELECT   rowid
151       FROM     igf_sl_pnote_stat_h
152       WHERE   ((loan_id = x_loan_id));
153 
154     lv_rowid cur_rowid%RowType;
155 
156   BEGIN
157 
158     OPEN cur_rowid;
159     FETCH cur_rowid INTO lv_rowid;
160     IF (cur_rowid%FOUND) THEN
161       CLOSE cur_rowid;
162       fnd_message.set_name ('IGF', 'IGF_SL_DLPNH_LOR_FK');
163       igs_ge_msg_stack.add;
164       app_exception.raise_exception;
165       RETURN;
166     END IF;
167     CLOSE cur_rowid;
168 
169   END get_ufk_igf_sl_lor;
170 
171 
172   PROCEDURE before_dml (
173     p_action                            IN     VARCHAR2,
174     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
175     x_dlpnh_id                          IN     NUMBER      DEFAULT NULL,
176     x_loan_id                           IN     NUMBER      DEFAULT NULL,
177     x_pnote_status                      IN     VARCHAR2    DEFAULT NULL,
178     x_pnote_status_date                 IN     DATE        DEFAULT NULL,
179     x_creation_date                     IN     DATE        DEFAULT NULL,
180     x_created_by                        IN     NUMBER      DEFAULT NULL,
181     x_last_update_date                  IN     DATE        DEFAULT NULL,
182     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
183     x_last_update_login                 IN     NUMBER      DEFAULT NULL
184   ) AS
185   /*
186   ||  Created By : viramali
187   ||  Created On : 10-MAY-2001
188   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
189   ||            Trigger Handlers for the table, before any DML operation.
190   ||  Known limitations, enhancements or remarks :
191   ||  Change History :
192   ||  Who             When            What
193   ||  (reverse chronological order - newest change first)
194   */
195   BEGIN
196 
197     set_column_values (
198       p_action,
199       x_rowid,
200       x_dlpnh_id,
201       x_loan_id,
202       x_pnote_status,
203       x_pnote_status_date,
204       x_creation_date,
205       x_created_by,
206       x_last_update_date,
207       x_last_updated_by,
208       x_last_update_login
209     );
210 
211     IF (p_action = 'INSERT') THEN
212       -- Call all the procedures related to Before Insert.
213       IF ( get_pk_for_validation(
214              new_references.dlpnh_id
215            )
216          ) THEN
217         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
218         igs_ge_msg_stack.add;
219         app_exception.raise_exception;
220       END IF;
221       check_parent_existance;
222     ELSIF (p_action = 'UPDATE') THEN
223       -- Call all the procedures related to Before Update.
224       check_parent_existance;
225     ELSIF (p_action = 'VALIDATE_INSERT') THEN
226       -- Call all the procedures related to Before Insert.
227       IF ( get_pk_for_validation (
228              new_references.dlpnh_id
229            )
230          ) THEN
231         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
232         igs_ge_msg_stack.add;
233         app_exception.raise_exception;
234       END IF;
235     END IF;
236 
237   END before_dml;
238 
239 
240   PROCEDURE insert_row (
241     x_rowid                             IN OUT NOCOPY VARCHAR2,
242     x_dlpnh_id                          IN OUT NOCOPY NUMBER,
243     x_loan_id                           IN     NUMBER,
244     x_pnote_status                      IN     VARCHAR2,
245     x_pnote_status_date                 IN     DATE,
246     x_mode                              IN     VARCHAR2 DEFAULT 'R'
247   ) AS
248   /*
249   ||  Created By : viramali
250   ||  Created On : 10-MAY-2001
251   ||  Purpose : Handles the INSERT DML logic for the table.
252   ||  Known limitations, enhancements or remarks :
253   ||  Change History :
254   ||  Who             When            What
255   ||  (reverse chronological order - newest change first)
256   */
257     CURSOR c IS
258       SELECT   rowid
259       FROM     igf_sl_pnote_stat_h
260       WHERE    dlpnh_id                          = x_dlpnh_id;
261 
262     x_last_update_date           DATE;
263     x_last_updated_by            NUMBER;
264     x_last_update_login          NUMBER;
265     x_request_id                 NUMBER;
266     x_program_id                 NUMBER;
267     x_program_application_id     NUMBER;
268     x_program_update_date        DATE;
269 
270   BEGIN
271 
272     x_last_update_date := SYSDATE;
273     IF (x_mode = 'I') THEN
274       x_last_updated_by := 1;
275       x_last_update_login := 0;
276     ELSIF (x_mode = 'R') THEN
277       x_last_updated_by := fnd_global.user_id;
278       IF (x_last_updated_by IS NULL) THEN
279         x_last_updated_by := -1;
280       END IF;
281       x_last_update_login := fnd_global.login_id;
282       IF (x_last_update_login IS NULL) THEN
283         x_last_update_login := -1;
284       END IF;
285       x_request_id             := fnd_global.conc_request_id;
286       x_program_id             := fnd_global.conc_program_id;
287       x_program_application_id := fnd_global.prog_appl_id;
288 
289       IF (x_request_id = -1) THEN
290         x_request_id             := NULL;
291         x_program_id             := NULL;
292         x_program_application_id := NULL;
293         x_program_update_date    := NULL;
294       ELSE
295         x_program_update_date    := SYSDATE;
296       END IF;
297     ELSE
298       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
299       igs_ge_msg_stack.add;
300       app_exception.raise_exception;
301     END IF;
302 
303     SELECT    igf_sl_pnote_stat_h_s.NEXTVAL
304     INTO      x_dlpnh_id
305     FROM      dual;
306 
307     before_dml(
308       p_action                            => 'INSERT',
309       x_rowid                             => x_rowid,
310       x_dlpnh_id                          => x_dlpnh_id,
311       x_loan_id                           => x_loan_id,
312       x_pnote_status                      => x_pnote_status,
313       x_pnote_status_date                 => x_pnote_status_date,
314       x_creation_date                     => x_last_update_date,
315       x_created_by                        => x_last_updated_by,
316       x_last_update_date                  => x_last_update_date,
317       x_last_updated_by                   => x_last_updated_by,
318       x_last_update_login                 => x_last_update_login
319     );
320 
321     INSERT INTO igf_sl_pnote_stat_h (
322       dlpnh_id,
323       loan_id,
324       pnote_status,
325       pnote_status_date,
326       creation_date,
327       created_by,
328       last_update_date,
329       last_updated_by,
330       last_update_login,
331       request_id,
332       program_id,
333       program_application_id,
334       program_update_date
335     ) VALUES (
336       new_references.dlpnh_id,
337       new_references.loan_id,
338       new_references.pnote_status,
339       new_references.pnote_status_date,
340       x_last_update_date,
341       x_last_updated_by,
342       x_last_update_date,
343       x_last_updated_by,
344       x_last_update_login ,
345       x_request_id,
346       x_program_id,
347       x_program_application_id,
348       x_program_update_date
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_dlpnh_id                          IN     NUMBER,
365     x_loan_id                           IN     NUMBER,
366     x_pnote_status                      IN     VARCHAR2,
367     x_pnote_status_date                 IN     DATE
368   ) AS
369   /*
370   ||  Created By : viramali
371   ||  Created On : 10-MAY-2001
372   ||  Purpose : Handles the LOCK mechanism for the table.
373   ||  Known limitations, enhancements or remarks :
374   ||  Change History :
375   ||  Who             When            What
376   ||  (reverse chronological order - newest change first)
377   */
378     CURSOR c1 IS
379       SELECT
380         loan_id,
381         pnote_status,
382         pnote_status_date
383       FROM  igf_sl_pnote_stat_h
384       WHERE rowid = x_rowid
385       FOR UPDATE NOWAIT;
386 
387     tlinfo c1%ROWTYPE;
388 
389   BEGIN
390 
391     OPEN c1;
392     FETCH c1 INTO tlinfo;
393     IF (c1%notfound) THEN
394       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
395       igs_ge_msg_stack.add;
396       CLOSE c1;
397       app_exception.raise_exception;
398       RETURN;
399     END IF;
400     CLOSE c1;
401 
402     IF (
403         (tlinfo.loan_id = x_loan_id)
404         AND ((tlinfo.pnote_status = x_pnote_status) OR ((tlinfo.pnote_status IS NULL) AND (X_pnote_status IS NULL)))
405         AND ((tlinfo.pnote_status_date = x_pnote_status_date) OR ((tlinfo.pnote_status_date IS NULL) AND (X_pnote_status_date IS NULL)))
406        ) THEN
407       NULL;
408     ELSE
409       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
410       igs_ge_msg_stack.add;
411       app_exception.raise_exception;
412     END IF;
413 
414     RETURN;
415 
416   END lock_row;
417 
418 
419   PROCEDURE update_row (
420     x_rowid                             IN     VARCHAR2,
421     x_dlpnh_id                          IN     NUMBER,
422     x_loan_id                           IN     NUMBER,
423     x_pnote_status                      IN     VARCHAR2,
424     x_pnote_status_date                 IN     DATE,
425     x_mode                              IN     VARCHAR2 DEFAULT 'R'
426   ) AS
427   /*
428   ||  Created By : viramali
429   ||  Created On : 10-MAY-2001
430   ||  Purpose : Handles the UPDATE DML logic for the table.
431   ||  Known limitations, enhancements or remarks :
432   ||  Change History :
433   ||  Who             When            What
434   ||  (reverse chronological order - newest change first)
435   */
436     x_last_update_date           DATE ;
437     x_last_updated_by            NUMBER;
438     x_last_update_login          NUMBER;
439     x_request_id                 NUMBER;
440     x_program_id                 NUMBER;
441     x_program_application_id     NUMBER;
442     x_program_update_date        DATE;
443 
444   BEGIN
445 
446     x_last_update_date := SYSDATE;
447     IF (X_MODE = 'I') THEN
448       x_last_updated_by := 1;
449       x_last_update_login := 0;
450     ELSIF (x_mode = 'R') THEN
451       x_last_updated_by := fnd_global.user_id;
452       IF x_last_updated_by IS NULL THEN
453         x_last_updated_by := -1;
454       END IF;
455       x_last_update_login := fnd_global.login_id;
456       IF (x_last_update_login IS NULL) THEN
457         x_last_update_login := -1;
458       END IF;
459     ELSE
460       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
461       igs_ge_msg_stack.add;
462       app_exception.raise_exception;
463     END IF;
464 
465     before_dml(
466       p_action                            => 'UPDATE',
467       x_rowid                             => x_rowid,
468       x_dlpnh_id                          => x_dlpnh_id,
469       x_loan_id                           => x_loan_id,
470       x_pnote_status                      => x_pnote_status,
471       x_pnote_status_date                 => x_pnote_status_date,
472       x_creation_date                     => x_last_update_date,
473       x_created_by                        => x_last_updated_by,
474       x_last_update_date                  => x_last_update_date,
475       x_last_updated_by                   => x_last_updated_by,
476       x_last_update_login                 => x_last_update_login
477     );
478 
479     IF (x_mode = 'R') THEN
480       x_request_id := fnd_global.conc_request_id;
481       x_program_id := fnd_global.conc_program_id;
482       x_program_application_id := fnd_global.prog_appl_id;
483       IF (x_request_id =  -1) THEN
484         x_request_id := old_references.request_id;
485         x_program_id := old_references.program_id;
486         x_program_application_id := old_references.program_application_id;
487         x_program_update_date := old_references.program_update_date;
488       ELSE
489         x_program_update_date := SYSDATE;
490       END IF;
491     END IF;
492 
493     UPDATE igf_sl_pnote_stat_h
494       SET
495         loan_id                           = new_references.loan_id,
496         pnote_status                      = new_references.pnote_status,
497         pnote_status_date                 = new_references.pnote_status_date,
498         last_update_date                  = x_last_update_date,
499         last_updated_by                   = x_last_updated_by,
500         last_update_login                 = x_last_update_login ,
501         request_id                        = x_request_id,
502         program_id                        = x_program_id,
503         program_application_id            = x_program_application_id,
504         program_update_date               = x_program_update_date
505       WHERE rowid = x_rowid;
506 
507     IF (SQL%NOTFOUND) THEN
508       RAISE NO_DATA_FOUND;
509     END IF;
510 
511   END update_row;
512 
513 
514   PROCEDURE add_row (
515     x_rowid                             IN OUT NOCOPY VARCHAR2,
516     x_dlpnh_id                          IN OUT NOCOPY NUMBER,
517     x_loan_id                           IN     NUMBER,
518     x_pnote_status                      IN     VARCHAR2,
519     x_pnote_status_date                 IN     DATE,
520     x_mode                              IN     VARCHAR2 DEFAULT 'R'
521   ) AS
522   /*
523   ||  Created By : viramali
524   ||  Created On : 10-MAY-2001
525   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
526   ||  Known limitations, enhancements or remarks :
527   ||  Change History :
528   ||  Who             When            What
529   ||  (reverse chronological order - newest change first)
530   */
531     CURSOR c1 IS
532       SELECT   rowid
533       FROM     igf_sl_pnote_stat_h
534       WHERE    dlpnh_id                          = x_dlpnh_id;
535 
536   BEGIN
537 
538     OPEN c1;
539     FETCH c1 INTO x_rowid;
540     IF (c1%NOTFOUND) THEN
541       CLOSE c1;
542 
543       insert_row (
544         x_rowid,
545         x_dlpnh_id,
546         x_loan_id,
547         x_pnote_status,
548         x_pnote_status_date,
549         x_mode
550       );
551       RETURN;
552     END IF;
553     CLOSE c1;
554 
555     update_row (
556       x_rowid,
557       x_dlpnh_id,
558       x_loan_id,
559       x_pnote_status,
560       x_pnote_status_date,
561       x_mode
562     );
563 
564   END add_row;
565 
566 
567   PROCEDURE delete_row (
568     x_rowid IN VARCHAR2
569   ) AS
570   /*
571   ||  Created By : viramali
572   ||  Created On : 10-MAY-2001
573   ||  Purpose : Handles the DELETE DML logic for the table.
574   ||  Known limitations, enhancements or remarks :
575   ||  Change History :
576   ||  Who             When            What
577   ||  (reverse chronological order - newest change first)
578   */
579   BEGIN
580 
581     before_dml (
582       p_action => 'DELETE',
583       x_rowid => x_rowid
584     );
585 
586     DELETE FROM igf_sl_pnote_stat_h
587     WHERE rowid = x_rowid;
588 
589     IF (SQL%NOTFOUND) THEN
590       RAISE NO_DATA_FOUND;
591     END IF;
592 
593   END delete_row;
594 
595 
596 END igf_sl_pnote_stat_h_pkg;