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