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_LOR_RESP_PKG

Source


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