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_DL_CHG_SEND_PKG

Source


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