[Home] [Help]
PACKAGE BODY: APPS.IGF_DB_DL_DISB_RESP_PKG
Source
1 PACKAGE BODY igf_db_dl_disb_resp_pkg AS
2 /* $Header: IGFDI03B.pls 115.8 2002/11/28 14:14:20 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_db_dl_disb_resp_all%ROWTYPE;
6 new_references igf_db_dl_disb_resp_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ddrp_id IN NUMBER,
12 x_dbth_id IN NUMBER,
13 x_loan_number IN VARCHAR2,
14 x_disb_num IN NUMBER,
15 x_disb_activity IN VARCHAR2,
16 x_transaction_date IN DATE,
17 x_disb_seq_num IN NUMBER,
18 x_disb_gross_amt IN NUMBER,
19 x_fee_1 IN NUMBER,
20 x_disb_net_amt IN NUMBER,
21 x_int_rebate_amt IN NUMBER,
22 x_user_ident IN VARCHAR2,
23 x_disb_batch_id IN VARCHAR2,
24 x_school_id IN VARCHAR2,
25 x_sch_code_status IN VARCHAR2,
26 x_loan_num_status IN VARCHAR2,
27 x_disb_num_status IN VARCHAR2,
28 x_disb_activity_status IN VARCHAR2,
29 x_trans_date_status IN VARCHAR2,
30 x_disb_seq_num_status IN VARCHAR2,
31 x_loc_disb_gross_amt IN NUMBER,
32 x_loc_fee_1 IN NUMBER,
33 x_loc_disb_net_amt IN NUMBER,
34 x_servicer_refund_amt IN NUMBER,
35 x_loc_int_rebate_amt IN NUMBER,
36 x_loc_net_booked_loan IN NUMBER,
37 x_ack_date IN DATE,
38 x_affirm_flag IN VARCHAR2,
39 x_status IN VARCHAR2,
40 x_creation_date IN DATE,
41 x_created_by IN NUMBER,
42 x_last_update_date IN DATE,
43 x_last_updated_by IN NUMBER,
44 x_last_update_login IN NUMBER
45 ) AS
46 /*
47 || Created By : venagara
48 || Created On : 18-JAN-2001
49 || Purpose : Initialises the Old and New references for the columns of the table.
50 || Known limitations, enhancements or remarks :
51 || Change History :
52 || Who When What
53 || (reverse chronological order - newest change first)
54 */
55
56 CURSOR cur_old_ref_values IS
57 SELECT *
58 FROM igf_db_dl_disb_resp_all
59 WHERE rowid = x_rowid;
60
61 BEGIN
62
63 l_rowid := x_rowid;
64
65 -- Code for setting the Old and New Reference Values.
66 -- Populate Old Values.
67 OPEN cur_old_ref_values;
68 FETCH cur_old_ref_values INTO old_references;
69 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
70 CLOSE cur_old_ref_values;
71 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
72 igs_ge_msg_stack.add;
73 app_exception.raise_exception;
74 RETURN;
75 END IF;
76 CLOSE cur_old_ref_values;
77
78 -- Populate New Values.
79 new_references.ddrp_id := x_ddrp_id;
80 new_references.dbth_id := x_dbth_id;
81 new_references.loan_number := x_loan_number;
82 new_references.disb_num := x_disb_num;
83 new_references.disb_activity := x_disb_activity;
84 new_references.transaction_date := x_transaction_date;
85 new_references.disb_seq_num := x_disb_seq_num;
86 new_references.disb_gross_amt := x_disb_gross_amt;
87 new_references.fee_1 := x_fee_1;
88 new_references.disb_net_amt := x_disb_net_amt;
89 new_references.int_rebate_amt := x_int_rebate_amt;
90 new_references.user_ident := x_user_ident;
91 new_references.disb_batch_id := x_disb_batch_id;
92 new_references.school_id := x_school_id;
93 new_references.sch_code_status := x_sch_code_status;
94 new_references.loan_num_status := x_loan_num_status;
95 new_references.disb_num_status := x_disb_num_status;
96 new_references.disb_activity_status := x_disb_activity_status;
97 new_references.trans_date_status := x_trans_date_status;
98 new_references.disb_seq_num_status := x_disb_seq_num_status;
99 new_references.loc_disb_gross_amt := x_loc_disb_gross_amt;
100 new_references.loc_fee_1 := x_loc_fee_1;
101 new_references.loc_disb_net_amt := x_loc_disb_net_amt;
102 new_references.servicer_refund_amt := x_servicer_refund_amt;
103 new_references.loc_int_rebate_amt := x_loc_int_rebate_amt;
104 new_references.loc_net_booked_loan := x_loc_net_booked_loan;
105 new_references.ack_date := x_ack_date;
106 new_references.affirm_flag := x_affirm_flag;
107 new_references.status := x_status;
108
109 IF (p_action = 'UPDATE') THEN
110 new_references.creation_date := old_references.creation_date;
111 new_references.created_by := old_references.created_by;
112 ELSE
113 new_references.creation_date := x_creation_date;
114 new_references.created_by := x_created_by;
115 END IF;
116
117 new_references.last_update_date := x_last_update_date;
118 new_references.last_updated_by := x_last_updated_by;
119 new_references.last_update_login := x_last_update_login;
120
121 END set_column_values;
122
123
124 PROCEDURE check_parent_existance AS
125 /*
126 || Created By : venagara
127 || Created On : 18-JAN-2001
128 || Purpose : Checks for the existance of Parent records.
129 || Known limitations, enhancements or remarks :
130 || Change History :
131 || Who When What
132 || (reverse chronological order - newest change first)
133 */
134 BEGIN
135
136 IF (((old_references.dbth_id = new_references.dbth_id)) OR
137 ((new_references.dbth_id IS NULL))) THEN
138 NULL;
139 ELSIF NOT igf_sl_dl_batch_pkg.get_pk_for_validation (
140 new_references.dbth_id
141 ) THEN
142 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
143 igs_ge_msg_stack.add;
144 app_exception.raise_exception;
145 END IF;
146
147 END check_parent_existance;
148
149
150 FUNCTION get_pk_for_validation (
151 x_ddrp_id IN NUMBER
152 ) RETURN BOOLEAN AS
153 /*
154 || Created By : venagara
155 || Created On : 18-JAN-2001
156 || Purpose : Validates the Primary Key of the table.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM igf_db_dl_disb_resp_all
165 WHERE ddrp_id = x_ddrp_id
166 FOR UPDATE NOWAIT;
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 OPEN cur_rowid;
173 FETCH cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 CLOSE cur_rowid;
176 RETURN(TRUE);
177 ELSE
178 CLOSE cur_rowid;
179 RETURN(FALSE);
180 END IF;
181
182 END get_pk_for_validation;
183
184
185 PROCEDURE get_fk_igf_sl_dl_batch (
186 x_dbth_id IN NUMBER
187 ) AS
188 /*
189 || Created By : venagara
190 || Created On : 18-JAN-2001
191 || Purpose : Validates the Foreign Keys for the table.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 CURSOR cur_rowid IS
198 SELECT rowid
199 FROM igf_db_dl_disb_resp_all
200 WHERE ((dbth_id = x_dbth_id));
201
202 lv_rowid cur_rowid%RowType;
203
204 BEGIN
205
206 OPEN cur_rowid;
207 FETCH cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 CLOSE cur_rowid;
210 fnd_message.set_name ('IGF', 'IGF_DB_DDRP_DBTH_FK');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 RETURN;
214 END IF;
215 CLOSE cur_rowid;
216
217 END get_fk_igf_sl_dl_batch;
218
219
220 PROCEDURE before_dml (
221 p_action IN VARCHAR2,
222 x_rowid IN VARCHAR2,
223 x_ddrp_id IN NUMBER,
224 x_dbth_id IN NUMBER,
225 x_loan_number IN VARCHAR2,
226 x_disb_num IN NUMBER,
227 x_disb_activity IN VARCHAR2,
228 x_transaction_date IN DATE,
229 x_disb_seq_num IN NUMBER,
230 x_disb_gross_amt IN NUMBER,
231 x_fee_1 IN NUMBER,
232 x_disb_net_amt IN NUMBER,
233 x_int_rebate_amt IN NUMBER,
234 x_user_ident IN VARCHAR2,
235 x_disb_batch_id IN VARCHAR2,
236 x_school_id IN VARCHAR2,
237 x_sch_code_status IN VARCHAR2,
238 x_loan_num_status IN VARCHAR2,
239 x_disb_num_status IN VARCHAR2,
240 x_disb_activity_status IN VARCHAR2,
241 x_trans_date_status IN VARCHAR2,
242 x_disb_seq_num_status IN VARCHAR2,
243 x_loc_disb_gross_amt IN NUMBER,
244 x_loc_fee_1 IN NUMBER,
245 x_loc_disb_net_amt IN NUMBER,
246 x_servicer_refund_amt IN NUMBER,
247 x_loc_int_rebate_amt IN NUMBER,
248 x_loc_net_booked_loan IN NUMBER,
249 x_ack_date IN DATE,
250 x_affirm_flag IN VARCHAR2,
251 x_status IN VARCHAR2,
252 x_creation_date IN DATE,
253 x_created_by IN NUMBER,
254 x_last_update_date IN DATE,
255 x_last_updated_by IN NUMBER,
256 x_last_update_login IN NUMBER
257 ) AS
258 /*
259 || Created By : venagara
260 || Created On : 18-JAN-2001
261 || Purpose : Initialises the columns, Checks Constraints, Calls the
262 || Trigger Handlers for the table, before any DML operation.
263 || Known limitations, enhancements or remarks :
264 || Change History :
265 || Who When What
266 || (reverse chronological order - newest change first)
267 */
268 BEGIN
269
270 set_column_values (
271 p_action,
272 x_rowid,
273 x_ddrp_id,
274 x_dbth_id,
275 x_loan_number,
276 x_disb_num,
277 x_disb_activity,
278 x_transaction_date,
279 x_disb_seq_num,
280 x_disb_gross_amt,
281 x_fee_1,
282 x_disb_net_amt,
283 x_int_rebate_amt,
284 x_user_ident,
285 x_disb_batch_id,
286 x_school_id,
287 x_sch_code_status,
288 x_loan_num_status,
289 x_disb_num_status,
290 x_disb_activity_status,
291 x_trans_date_status,
292 x_disb_seq_num_status,
293 x_loc_disb_gross_amt,
294 x_loc_fee_1,
295 x_loc_disb_net_amt,
296 x_servicer_refund_amt,
297 x_loc_int_rebate_amt,
298 x_loc_net_booked_loan,
299 x_ack_date,
300 x_affirm_flag,
301 x_status,
302 x_creation_date,
303 x_created_by,
304 x_last_update_date,
305 x_last_updated_by,
306 x_last_update_login
307 );
308
309 IF (p_action = 'INSERT') THEN
310 -- Call all the procedures related to Before Insert.
311 IF ( get_pk_for_validation(
312 new_references.ddrp_id
313 )
314 ) THEN
315 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
316 igs_ge_msg_stack.add;
317 app_exception.raise_exception;
318 END IF;
319 check_parent_existance;
320 ELSIF (p_action = 'UPDATE') THEN
321 -- Call all the procedures related to Before Update.
322 check_parent_existance;
323 ELSIF (p_action = 'VALIDATE_INSERT') THEN
324 -- Call all the procedures related to Before Insert.
325 IF ( get_pk_for_validation (
326 new_references.ddrp_id
327 )
328 ) THEN
329 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
330 igs_ge_msg_stack.add;
331 app_exception.raise_exception;
332 END IF;
333 END IF;
334
335 END before_dml;
336
337
338 PROCEDURE insert_row (
339 x_rowid IN OUT NOCOPY VARCHAR2,
340 x_ddrp_id IN OUT NOCOPY NUMBER,
341 x_dbth_id IN NUMBER,
342 x_loan_number IN VARCHAR2,
343 x_disb_num IN NUMBER,
344 x_disb_activity IN VARCHAR2,
345 x_transaction_date IN DATE,
346 x_disb_seq_num IN NUMBER,
347 x_disb_gross_amt IN NUMBER,
348 x_fee_1 IN NUMBER,
349 x_disb_net_amt IN NUMBER,
350 x_int_rebate_amt IN NUMBER,
351 x_user_ident IN VARCHAR2,
352 x_disb_batch_id IN VARCHAR2,
353 x_school_id IN VARCHAR2,
354 x_sch_code_status IN VARCHAR2,
355 x_loan_num_status IN VARCHAR2,
356 x_disb_num_status IN VARCHAR2,
357 x_disb_activity_status IN VARCHAR2,
358 x_trans_date_status IN VARCHAR2,
359 x_disb_seq_num_status IN VARCHAR2,
360 x_loc_disb_gross_amt IN NUMBER,
361 x_loc_fee_1 IN NUMBER,
362 x_loc_disb_net_amt IN NUMBER,
363 x_servicer_refund_amt IN NUMBER,
364 x_loc_int_rebate_amt IN NUMBER,
365 x_loc_net_booked_loan IN NUMBER,
366 x_ack_date IN DATE,
367 x_affirm_flag IN VARCHAR2,
368 x_status IN VARCHAR2,
369 x_mode IN VARCHAR2
370 ) AS
371 /*
372 || Created By : venagara
373 || Created On : 18-JAN-2001
374 || Purpose : Handles the INSERT DML logic for the table.
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 */
380 CURSOR c IS
381 SELECT rowid
382 FROM igf_db_dl_disb_resp_all
383 WHERE ddrp_id = x_ddrp_id;
384
385 x_last_update_date DATE;
386 x_last_updated_by NUMBER;
387 x_last_update_login NUMBER;
388 x_request_id NUMBER;
389 x_program_id NUMBER;
390 x_program_application_id NUMBER;
391 x_program_update_date DATE;
392 l_org_id igf_db_dl_disb_resp_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
393
394 BEGIN
395
396 x_last_update_date := SYSDATE;
397 IF (x_mode = 'I') THEN
398 x_last_updated_by := 1;
399 x_last_update_login := 0;
400 ELSIF (x_mode = 'R') THEN
401 x_last_updated_by := fnd_global.user_id;
402 IF (x_last_updated_by IS NULL) THEN
403 x_last_updated_by := -1;
404 END IF;
405 x_last_update_login := fnd_global.login_id;
406 IF (x_last_update_login IS NULL) THEN
407 x_last_update_login := -1;
408 END IF;
409 x_request_id := fnd_global.conc_request_id;
410 x_program_id := fnd_global.conc_program_id;
411 x_program_application_id := fnd_global.prog_appl_id;
412
413 IF (x_request_id = -1) THEN
414 x_request_id := NULL;
415 x_program_id := NULL;
416 x_program_application_id := NULL;
417 x_program_update_date := NULL;
418 ELSE
419 x_program_update_date := SYSDATE;
420 END IF;
421 ELSE
422 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
423 igs_ge_msg_stack.add;
424 app_exception.raise_exception;
425 END IF;
426
427 SELECT igf_db_dl_disb_resp_s.NEXTVAL INTO x_ddrp_id FROM dual;
428 before_dml(
429 p_action => 'INSERT',
430 x_rowid => x_rowid,
431 x_ddrp_id => x_ddrp_id,
432 x_dbth_id => x_dbth_id,
433 x_loan_number => x_loan_number,
434 x_disb_num => x_disb_num,
435 x_disb_activity => x_disb_activity,
436 x_transaction_date => x_transaction_date,
437 x_disb_seq_num => x_disb_seq_num,
438 x_disb_gross_amt => x_disb_gross_amt,
439 x_fee_1 => x_fee_1,
440 x_disb_net_amt => x_disb_net_amt,
441 x_int_rebate_amt => x_int_rebate_amt,
442 x_user_ident => x_user_ident,
443 x_disb_batch_id => x_disb_batch_id,
444 x_school_id => x_school_id,
445 x_sch_code_status => x_sch_code_status,
446 x_loan_num_status => x_loan_num_status,
447 x_disb_num_status => x_disb_num_status,
448 x_disb_activity_status => x_disb_activity_status,
449 x_trans_date_status => x_trans_date_status,
450 x_disb_seq_num_status => x_disb_seq_num_status,
451 x_loc_disb_gross_amt => x_loc_disb_gross_amt,
452 x_loc_fee_1 => x_loc_fee_1,
453 x_loc_disb_net_amt => x_loc_disb_net_amt,
454 x_servicer_refund_amt => x_servicer_refund_amt,
455 x_loc_int_rebate_amt => x_loc_int_rebate_amt,
456 x_loc_net_booked_loan => x_loc_net_booked_loan,
457 x_ack_date => x_ack_date,
458 x_affirm_flag => x_affirm_flag,
459 x_status => x_status,
460 x_creation_date => x_last_update_date,
461 x_created_by => x_last_updated_by,
462 x_last_update_date => x_last_update_date,
463 x_last_updated_by => x_last_updated_by,
464 x_last_update_login => x_last_update_login
465 );
466
467 INSERT INTO igf_db_dl_disb_resp_all (
468 ddrp_id,
469 dbth_id,
470 loan_number,
471 disb_num,
472 disb_activity,
473 transaction_date,
474 disb_seq_num,
475 disb_gross_amt,
476 fee_1,
477 disb_net_amt,
478 int_rebate_amt,
479 user_ident,
480 disb_batch_id,
481 school_id,
482 sch_code_status,
483 loan_num_status,
484 disb_num_status,
485 disb_activity_status,
486 trans_date_status,
487 disb_seq_num_status,
488 loc_disb_gross_amt,
489 loc_fee_1,
490 loc_disb_net_amt,
491 servicer_refund_amt,
492 loc_int_rebate_amt,
493 loc_net_booked_loan,
494 ack_date,
495 affirm_flag,
496 status,
497 creation_date,
498 created_by,
499 last_update_date,
500 last_updated_by,
501 last_update_login,
502 request_id,
503 program_id,
504 program_application_id,
505 program_update_date,
506 org_id
507 ) VALUES (
508 new_references.ddrp_id,
509 new_references.dbth_id,
510 new_references.loan_number,
511 new_references.disb_num,
512 new_references.disb_activity,
513 new_references.transaction_date,
514 new_references.disb_seq_num,
515 new_references.disb_gross_amt,
516 new_references.fee_1,
517 new_references.disb_net_amt,
518 new_references.int_rebate_amt,
519 new_references.user_ident,
520 new_references.disb_batch_id,
521 new_references.school_id,
522 new_references.sch_code_status,
523 new_references.loan_num_status,
524 new_references.disb_num_status,
525 new_references.disb_activity_status,
526 new_references.trans_date_status,
527 new_references.disb_seq_num_status,
528 new_references.loc_disb_gross_amt,
529 new_references.loc_fee_1,
530 new_references.loc_disb_net_amt,
531 new_references.servicer_refund_amt,
532 new_references.loc_int_rebate_amt,
533 new_references.loc_net_booked_loan,
534 new_references.ack_date,
535 new_references.affirm_flag,
536 new_references.status,
537 x_last_update_date,
538 x_last_updated_by,
539 x_last_update_date,
540 x_last_updated_by,
541 x_last_update_login ,
542 x_request_id,
543 x_program_id,
544 x_program_application_id,
545 x_program_update_date,
546 l_org_id
547 );
548
549 OPEN c;
550 FETCH c INTO x_rowid;
551 IF (c%NOTFOUND) THEN
552 CLOSE c;
553 RAISE NO_DATA_FOUND;
554 END IF;
555 CLOSE c;
556
557 END insert_row;
558
559
560 PROCEDURE lock_row (
561 x_rowid IN VARCHAR2,
562 x_ddrp_id IN NUMBER,
563 x_dbth_id IN NUMBER,
564 x_loan_number IN VARCHAR2,
565 x_disb_num IN NUMBER,
566 x_disb_activity IN VARCHAR2,
567 x_transaction_date IN DATE,
568 x_disb_seq_num IN NUMBER,
569 x_disb_gross_amt IN NUMBER,
570 x_fee_1 IN NUMBER,
571 x_disb_net_amt IN NUMBER,
572 x_int_rebate_amt IN NUMBER,
573 x_user_ident IN VARCHAR2,
574 x_disb_batch_id IN VARCHAR2,
575 x_school_id IN VARCHAR2,
576 x_sch_code_status IN VARCHAR2,
577 x_loan_num_status IN VARCHAR2,
578 x_disb_num_status IN VARCHAR2,
579 x_disb_activity_status IN VARCHAR2,
580 x_trans_date_status IN VARCHAR2,
581 x_disb_seq_num_status IN VARCHAR2,
582 x_loc_disb_gross_amt IN NUMBER,
583 x_loc_fee_1 IN NUMBER,
584 x_loc_disb_net_amt IN NUMBER,
585 x_servicer_refund_amt IN NUMBER,
586 x_loc_int_rebate_amt IN NUMBER,
587 x_loc_net_booked_loan IN NUMBER,
588 x_ack_date IN DATE,
589 x_affirm_flag IN VARCHAR2,
590 x_status IN VARCHAR2
591 ) AS
592 /*
593 || Created By : venagara
594 || Created On : 18-JAN-2001
595 || Purpose : Handles the LOCK mechanism for the table.
596 || Known limitations, enhancements or remarks :
597 || Change History :
598 || Who When What
599 || (reverse chronological order - newest change first)
600 */
601 CURSOR c1 IS
602 SELECT
603 dbth_id,
604 loan_number,
605 disb_num,
606 disb_activity,
607 transaction_date,
608 disb_seq_num,
609 disb_gross_amt,
610 fee_1,
611 disb_net_amt,
612 int_rebate_amt,
613 user_ident,
614 disb_batch_id,
615 school_id,
616 sch_code_status,
617 loan_num_status,
618 disb_num_status,
619 disb_activity_status,
620 trans_date_status,
621 disb_seq_num_status,
622 loc_disb_gross_amt,
623 loc_fee_1,
624 loc_disb_net_amt,
625 servicer_refund_amt,
626 loc_int_rebate_amt,
627 loc_net_booked_loan,
628 ack_date,
629 affirm_flag,
630 status,
631 org_id
632 FROM igf_db_dl_disb_resp_all
633 WHERE rowid = x_rowid
634 FOR UPDATE NOWAIT;
635
636 tlinfo c1%ROWTYPE;
637
638 BEGIN
639
640 OPEN c1;
641 FETCH c1 INTO tlinfo;
642 IF (c1%notfound) THEN
643 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
644 igs_ge_msg_stack.add;
645 CLOSE c1;
646 app_exception.raise_exception;
647 RETURN;
648 END IF;
649 CLOSE c1;
650
651 IF (
652 (tlinfo.dbth_id = x_dbth_id)
653 AND (tlinfo.loan_number = x_loan_number)
654 AND (tlinfo.disb_num = x_disb_num)
655 AND ((tlinfo.disb_activity = x_disb_activity) OR ((tlinfo.disb_activity IS NULL) AND (X_disb_activity IS NULL)))
656 AND ((tlinfo.transaction_date = x_transaction_date) OR ((tlinfo.transaction_date IS NULL) AND (X_transaction_date IS NULL)))
657 AND ((tlinfo.disb_seq_num = x_disb_seq_num) OR ((tlinfo.disb_seq_num IS NULL) AND (X_disb_seq_num IS NULL)))
658 AND ((tlinfo.disb_gross_amt = x_disb_gross_amt) OR ((tlinfo.disb_gross_amt IS NULL) AND (X_disb_gross_amt IS NULL)))
659 AND ((tlinfo.fee_1 = x_fee_1) OR ((tlinfo.fee_1 IS NULL) AND (X_fee_1 IS NULL)))
660 AND ((tlinfo.disb_net_amt = x_disb_net_amt) OR ((tlinfo.disb_net_amt IS NULL) AND (X_disb_net_amt IS NULL)))
661 AND ((tlinfo.int_rebate_amt = x_int_rebate_amt) OR ((tlinfo.int_rebate_amt IS NULL) AND (X_int_rebate_amt IS NULL)))
662 AND ((tlinfo.user_ident = x_user_ident) OR ((tlinfo.user_ident IS NULL) AND (X_user_ident IS NULL)))
663 AND ((tlinfo.disb_batch_id = x_disb_batch_id) OR ((tlinfo.disb_batch_id IS NULL) AND (X_disb_batch_id IS NULL)))
664 AND ((tlinfo.school_id = x_school_id) OR ((tlinfo.school_id IS NULL) AND (X_school_id IS NULL)))
665 AND ((tlinfo.sch_code_status = x_sch_code_status) OR ((tlinfo.sch_code_status IS NULL) AND (X_sch_code_status IS NULL)))
666 AND ((tlinfo.loan_num_status = x_loan_num_status) OR ((tlinfo.loan_num_status IS NULL) AND (X_loan_num_status IS NULL)))
667 AND ((tlinfo.disb_num_status = x_disb_num_status) OR ((tlinfo.disb_num_status IS NULL) AND (X_disb_num_status IS NULL)))
668 AND ((tlinfo.disb_activity_status = x_disb_activity_status) OR ((tlinfo.disb_activity_status IS NULL) AND (X_disb_activity_status IS NULL)))
669 AND ((tlinfo.trans_date_status = x_trans_date_status) OR ((tlinfo.trans_date_status IS NULL) AND (X_trans_date_status IS NULL)))
670 AND ((tlinfo.disb_seq_num_status = x_disb_seq_num_status) OR ((tlinfo.disb_seq_num_status IS NULL) AND (X_disb_seq_num_status IS NULL)))
671 AND ((tlinfo.loc_disb_gross_amt = x_loc_disb_gross_amt) OR ((tlinfo.loc_disb_gross_amt IS NULL) AND (X_loc_disb_gross_amt IS NULL)))
672 AND ((tlinfo.loc_fee_1 = x_loc_fee_1) OR ((tlinfo.loc_fee_1 IS NULL) AND (X_loc_fee_1 IS NULL)))
673 AND ((tlinfo.loc_disb_net_amt = x_loc_disb_net_amt) OR ((tlinfo.loc_disb_net_amt IS NULL) AND (X_loc_disb_net_amt IS NULL)))
674 AND ((tlinfo.servicer_refund_amt = x_servicer_refund_amt) OR ((tlinfo.servicer_refund_amt IS NULL) AND (X_servicer_refund_amt IS NULL)))
675 AND ((tlinfo.loc_int_rebate_amt = x_loc_int_rebate_amt) OR ((tlinfo.loc_int_rebate_amt IS NULL) AND (X_loc_int_rebate_amt IS NULL)))
676 AND ((tlinfo.loc_net_booked_loan = x_loc_net_booked_loan) OR ((tlinfo.loc_net_booked_loan IS NULL) AND (X_loc_net_booked_loan IS NULL)))
677 AND ((tlinfo.ack_date = x_ack_date) OR ((tlinfo.ack_date IS NULL) AND (X_ack_date IS NULL)))
678 AND ((tlinfo.affirm_flag = x_affirm_flag) OR ((tlinfo.affirm_flag IS NULL) AND (X_affirm_flag IS NULL)))
679 AND (tlinfo.status = x_status)
680 ) THEN
681 NULL;
682 ELSE
683 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
684 igs_ge_msg_stack.add;
685 app_exception.raise_exception;
686 END IF;
687
688 RETURN;
689
690 END lock_row;
691
692
693 PROCEDURE update_row (
694 x_rowid IN VARCHAR2,
695 x_ddrp_id IN NUMBER,
696 x_dbth_id IN NUMBER,
697 x_loan_number IN VARCHAR2,
698 x_disb_num IN NUMBER,
699 x_disb_activity IN VARCHAR2,
700 x_transaction_date IN DATE,
701 x_disb_seq_num IN NUMBER,
702 x_disb_gross_amt IN NUMBER,
703 x_fee_1 IN NUMBER,
704 x_disb_net_amt IN NUMBER,
705 x_int_rebate_amt IN NUMBER,
706 x_user_ident IN VARCHAR2,
707 x_disb_batch_id IN VARCHAR2,
708 x_school_id IN VARCHAR2,
709 x_sch_code_status IN VARCHAR2,
710 x_loan_num_status IN VARCHAR2,
711 x_disb_num_status IN VARCHAR2,
712 x_disb_activity_status IN VARCHAR2,
713 x_trans_date_status IN VARCHAR2,
714 x_disb_seq_num_status IN VARCHAR2,
715 x_loc_disb_gross_amt IN NUMBER,
716 x_loc_fee_1 IN NUMBER,
717 x_loc_disb_net_amt IN NUMBER,
718 x_servicer_refund_amt IN NUMBER,
719 x_loc_int_rebate_amt IN NUMBER,
720 x_loc_net_booked_loan IN NUMBER,
721 x_ack_date IN DATE,
722 x_affirm_flag IN VARCHAR2,
723 x_status IN VARCHAR2,
724 x_mode IN VARCHAR2
725 ) AS
726 /*
727 || Created By : venagara
728 || Created On : 18-JAN-2001
729 || Purpose : Handles the UPDATE DML logic for the table.
730 || Known limitations, enhancements or remarks :
731 || Change History :
732 || Who When What
733 || (reverse chronological order - newest change first)
734 */
735 x_last_update_date DATE ;
736 x_last_updated_by NUMBER;
737 x_last_update_login NUMBER;
738 x_request_id NUMBER;
739 x_program_id NUMBER;
740 x_program_application_id NUMBER;
741 x_program_update_date DATE;
742
743 BEGIN
744
745 x_last_update_date := SYSDATE;
746 IF (X_MODE = 'I') THEN
747 x_last_updated_by := 1;
748 x_last_update_login := 0;
749 ELSIF (x_mode = 'R') THEN
750 x_last_updated_by := fnd_global.user_id;
751 IF x_last_updated_by IS NULL THEN
752 x_last_updated_by := -1;
753 END IF;
754 x_last_update_login := fnd_global.login_id;
755 IF (x_last_update_login IS NULL) THEN
756 x_last_update_login := -1;
757 END IF;
758 ELSE
759 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
760 igs_ge_msg_stack.add;
761 app_exception.raise_exception;
762 END IF;
763
764 before_dml(
765 p_action => 'UPDATE',
766 x_rowid => x_rowid,
767 x_ddrp_id => x_ddrp_id,
768 x_dbth_id => x_dbth_id,
769 x_loan_number => x_loan_number,
770 x_disb_num => x_disb_num,
771 x_disb_activity => x_disb_activity,
772 x_transaction_date => x_transaction_date,
773 x_disb_seq_num => x_disb_seq_num,
774 x_disb_gross_amt => x_disb_gross_amt,
775 x_fee_1 => x_fee_1,
776 x_disb_net_amt => x_disb_net_amt,
777 x_int_rebate_amt => x_int_rebate_amt,
778 x_user_ident => x_user_ident,
779 x_disb_batch_id => x_disb_batch_id,
780 x_school_id => x_school_id,
781 x_sch_code_status => x_sch_code_status,
782 x_loan_num_status => x_loan_num_status,
783 x_disb_num_status => x_disb_num_status,
784 x_disb_activity_status => x_disb_activity_status,
785 x_trans_date_status => x_trans_date_status,
786 x_disb_seq_num_status => x_disb_seq_num_status,
787 x_loc_disb_gross_amt => x_loc_disb_gross_amt,
788 x_loc_fee_1 => x_loc_fee_1,
789 x_loc_disb_net_amt => x_loc_disb_net_amt,
790 x_servicer_refund_amt => x_servicer_refund_amt,
791 x_loc_int_rebate_amt => x_loc_int_rebate_amt,
792 x_loc_net_booked_loan => x_loc_net_booked_loan,
793 x_ack_date => x_ack_date,
794 x_affirm_flag => x_affirm_flag,
795 x_status => x_status,
796 x_creation_date => x_last_update_date,
797 x_created_by => x_last_updated_by,
798 x_last_update_date => x_last_update_date,
799 x_last_updated_by => x_last_updated_by,
800 x_last_update_login => x_last_update_login
801 );
802
803 IF (x_mode = 'R') THEN
804 x_request_id := fnd_global.conc_request_id;
805 x_program_id := fnd_global.conc_program_id;
806 x_program_application_id := fnd_global.prog_appl_id;
807 IF (x_request_id = -1) THEN
808 x_request_id := old_references.request_id;
809 x_program_id := old_references.program_id;
810 x_program_application_id := old_references.program_application_id;
811 x_program_update_date := old_references.program_update_date;
812 ELSE
813 x_program_update_date := SYSDATE;
814 END IF;
815 END IF;
816
817 UPDATE igf_db_dl_disb_resp_all
818 SET
819 dbth_id = new_references.dbth_id,
820 loan_number = new_references.loan_number,
821 disb_num = new_references.disb_num,
822 disb_activity = new_references.disb_activity,
823 transaction_date = new_references.transaction_date,
824 disb_seq_num = new_references.disb_seq_num,
825 disb_gross_amt = new_references.disb_gross_amt,
826 fee_1 = new_references.fee_1,
827 disb_net_amt = new_references.disb_net_amt,
828 int_rebate_amt = new_references.int_rebate_amt,
829 user_ident = new_references.user_ident,
830 disb_batch_id = new_references.disb_batch_id,
831 school_id = new_references.school_id,
832 sch_code_status = new_references.sch_code_status,
833 loan_num_status = new_references.loan_num_status,
834 disb_num_status = new_references.disb_num_status,
835 disb_activity_status = new_references.disb_activity_status,
836 trans_date_status = new_references.trans_date_status,
837 disb_seq_num_status = new_references.disb_seq_num_status,
838 loc_disb_gross_amt = new_references.loc_disb_gross_amt,
839 loc_fee_1 = new_references.loc_fee_1,
840 loc_disb_net_amt = new_references.loc_disb_net_amt,
841 servicer_refund_amt = new_references.servicer_refund_amt,
842 loc_int_rebate_amt = new_references.loc_int_rebate_amt,
843 loc_net_booked_loan = new_references.loc_net_booked_loan,
844 ack_date = new_references.ack_date,
845 affirm_flag = new_references.affirm_flag,
846 status = new_references.status,
847 last_update_date = x_last_update_date,
848 last_updated_by = x_last_updated_by,
849 last_update_login = x_last_update_login ,
850 request_id = x_request_id,
851 program_id = x_program_id,
852 program_application_id = x_program_application_id,
853 program_update_date = x_program_update_date
854 WHERE rowid = x_rowid;
855
856 IF (SQL%NOTFOUND) THEN
857 RAISE NO_DATA_FOUND;
858 END IF;
859
860 END update_row;
861
862
863 PROCEDURE add_row (
864 x_rowid IN OUT NOCOPY VARCHAR2,
865 x_ddrp_id IN OUT NOCOPY NUMBER,
866 x_dbth_id IN NUMBER,
867 x_loan_number IN VARCHAR2,
868 x_disb_num IN NUMBER,
869 x_disb_activity IN VARCHAR2,
870 x_transaction_date IN DATE,
871 x_disb_seq_num IN NUMBER,
872 x_disb_gross_amt IN NUMBER,
873 x_fee_1 IN NUMBER,
874 x_disb_net_amt IN NUMBER,
875 x_int_rebate_amt IN NUMBER,
876 x_user_ident IN VARCHAR2,
877 x_disb_batch_id IN VARCHAR2,
878 x_school_id IN VARCHAR2,
879 x_sch_code_status IN VARCHAR2,
880 x_loan_num_status IN VARCHAR2,
881 x_disb_num_status IN VARCHAR2,
882 x_disb_activity_status IN VARCHAR2,
883 x_trans_date_status IN VARCHAR2,
884 x_disb_seq_num_status IN VARCHAR2,
885 x_loc_disb_gross_amt IN NUMBER,
886 x_loc_fee_1 IN NUMBER,
887 x_loc_disb_net_amt IN NUMBER,
888 x_servicer_refund_amt IN NUMBER,
889 x_loc_int_rebate_amt IN NUMBER,
890 x_loc_net_booked_loan IN NUMBER,
891 x_ack_date IN DATE,
892 x_affirm_flag IN VARCHAR2,
893 x_status IN VARCHAR2,
894 x_mode IN VARCHAR2
895 ) AS
896 /*
897 || Created By : venagara
898 || Created On : 18-JAN-2001
899 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
900 || Known limitations, enhancements or remarks :
901 || Change History :
902 || Who When What
903 || (reverse chronological order - newest change first)
904 */
905 CURSOR c1 IS
906 SELECT rowid
907 FROM igf_db_dl_disb_resp_all
908 WHERE ddrp_id = x_ddrp_id;
909
910 BEGIN
911
912 OPEN c1;
913 FETCH c1 INTO x_rowid;
914 IF (c1%NOTFOUND) THEN
915 CLOSE c1;
916
917 insert_row (
918 x_rowid,
919 x_ddrp_id,
920 x_dbth_id,
921 x_loan_number,
922 x_disb_num,
923 x_disb_activity,
924 x_transaction_date,
925 x_disb_seq_num,
926 x_disb_gross_amt,
927 x_fee_1,
928 x_disb_net_amt,
929 x_int_rebate_amt,
930 x_user_ident,
931 x_disb_batch_id,
932 x_school_id,
933 x_sch_code_status,
934 x_loan_num_status,
935 x_disb_num_status,
936 x_disb_activity_status,
937 x_trans_date_status,
938 x_disb_seq_num_status,
939 x_loc_disb_gross_amt,
940 x_loc_fee_1,
941 x_loc_disb_net_amt,
942 x_servicer_refund_amt,
943 x_loc_int_rebate_amt,
944 x_loc_net_booked_loan,
945 x_ack_date,
946 x_affirm_flag,
947 x_status,
948 x_mode
949 );
950 RETURN;
951 END IF;
952 CLOSE c1;
953
954 update_row (
955 x_rowid,
956 x_ddrp_id,
957 x_dbth_id,
958 x_loan_number,
959 x_disb_num,
960 x_disb_activity,
961 x_transaction_date,
962 x_disb_seq_num,
963 x_disb_gross_amt,
964 x_fee_1,
965 x_disb_net_amt,
966 x_int_rebate_amt,
967 x_user_ident,
968 x_disb_batch_id,
969 x_school_id,
970 x_sch_code_status,
971 x_loan_num_status,
972 x_disb_num_status,
973 x_disb_activity_status,
974 x_trans_date_status,
975 x_disb_seq_num_status,
976 x_loc_disb_gross_amt,
977 x_loc_fee_1,
978 x_loc_disb_net_amt,
979 x_servicer_refund_amt,
980 x_loc_int_rebate_amt,
981 x_loc_net_booked_loan,
982 x_ack_date,
983 x_affirm_flag,
984 x_status,
985 x_mode
986 );
987
988 END add_row;
989
990
991 PROCEDURE delete_row (
992 x_rowid IN VARCHAR2
993 ) AS
994 /*
995 || Created By : venagara
996 || Created On : 18-JAN-2001
997 || Purpose : Handles the DELETE DML logic for the table.
998 || Known limitations, enhancements or remarks :
999 || Change History :
1000 || Who When What
1001 || (reverse chronological order - newest change first)
1002 */
1003 BEGIN
1004
1005 before_dml (
1006 p_action => 'DELETE',
1007 x_rowid => x_rowid
1008 );
1009
1010 DELETE FROM igf_db_dl_disb_resp_all
1011 WHERE rowid = x_rowid;
1012
1013 IF (SQL%NOTFOUND) THEN
1014 RAISE NO_DATA_FOUND;
1015 END IF;
1016
1017 END delete_row;
1018
1019
1020 END igf_db_dl_disb_resp_pkg;