[Home] [Help]
PACKAGE BODY: APPS.IGF_DB_CL_DISB_RESP_PKG
Source
1 PACKAGE BODY igf_db_cl_disb_resp_pkg AS
2 /* $Header: IGFDI04B.pls 120.1 2006/08/08 06:28:49 ridas noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_db_cl_disb_resp_all%ROWTYPE;
6 new_references igf_db_cl_disb_resp_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_cdbr_id IN NUMBER ,
12 x_cbth_id IN NUMBER ,
13 x_record_type IN VARCHAR2,
14 x_loan_number IN VARCHAR2,
15 x_cl_seq_number IN NUMBER ,
16 x_b_last_name IN VARCHAR2,
17 x_b_first_name IN VARCHAR2,
18 x_b_middle_name IN VARCHAR2,
19 x_b_ssn IN VARCHAR2,
20 x_b_addr_line_1 IN VARCHAR2,
21 x_b_addr_line_2 IN VARCHAR2,
22 x_b_city IN VARCHAR2,
23 x_b_state IN VARCHAR2,
24 x_b_zip IN NUMBER ,
25 x_b_zip_suffix IN NUMBER ,
26 x_b_addr_chg_date IN DATE ,
27 x_eft_auth_code IN VARCHAR2,
28 x_s_last_name IN VARCHAR2,
29 x_s_first_name IN VARCHAR2,
30 x_s_middle_initial IN VARCHAR2,
31 x_s_ssn IN VARCHAR2,
32 x_school_id IN NUMBER ,
33 x_school_use IN VARCHAR2,
34 x_loan_per_start_date IN DATE ,
35 x_loan_per_end_date IN DATE ,
36 x_cl_loan_type IN VARCHAR2,
37 x_alt_prog_type_code IN VARCHAR2,
38 x_lender_id IN VARCHAR2,
39 x_lend_non_ed_brc_id IN VARCHAR2,
40 x_lender_use IN VARCHAR2,
41 x_borw_confirm_ind IN VARCHAR2,
42 x_tot_sched_disb IN NUMBER ,
43 x_fund_release_date IN DATE ,
44 x_disb_num IN NUMBER ,
45 x_guarantor_id IN VARCHAR2,
46 x_guarantor_use IN VARCHAR2,
47 x_guarantee_date IN DATE ,
48 x_guarantee_amt IN NUMBER ,
49 x_gross_disb_amt IN NUMBER ,
50 x_fee_1 IN NUMBER ,
51 x_fee_2 IN NUMBER ,
52 x_net_disb_amt IN NUMBER ,
53 x_fund_dist_mthd IN VARCHAR2,
54 x_check_number IN VARCHAR2,
55 x_late_disb_ind IN VARCHAR2,
56 x_prev_reported_ind IN VARCHAR2,
57 x_err_code1 IN VARCHAR2,
58 x_err_code2 IN VARCHAR2,
59 x_err_code3 IN VARCHAR2,
60 x_err_code4 IN VARCHAR2,
61 x_err_code5 IN VARCHAR2,
62 x_fee_paid_2 IN NUMBER ,
63 x_lender_name IN VARCHAR2,
64 x_net_cancel_amt IN NUMBER ,
65 x_duns_lender_id IN VARCHAR2,
66 x_duns_guarnt_id IN VARCHAR2,
67 x_hold_rel_ind IN VARCHAR2,
68 x_pnote_code IN VARCHAR2,
69 x_pnote_status_date IN DATE ,
70 x_fee_paid_1 IN NUMBER ,
71 x_netted_cancel_amt IN NUMBER ,
72 x_outstd_cancel_amt IN NUMBER ,
73 x_sch_non_ed_brc_id IN VARCHAR2,
74 x_status IN VARCHAR2,
75 x_creation_date IN DATE ,
76 x_created_by IN NUMBER ,
77 x_last_update_date IN DATE ,
78 x_last_updated_by IN NUMBER ,
79 x_last_update_login IN NUMBER ,
80 x_esign_src_typ_cd IN VARCHAR2,
81 x_direct_to_borr_flag IN VARCHAR2
82 ) AS
83 /*
84 || Created By : prchandr
85 || Created On : 14-DEC-2000
86 || Purpose : Initialises the Old and New references for the columns of the table.
87 || Known limitations, enhancements or remarks :
88 || Change History :
89 || Who When What
90 || (reverse chronological order - newest change first)
91 */
92
93 CURSOR cur_old_ref_values IS
94 SELECT *
95 FROM igf_db_cl_disb_resp_all
96 WHERE rowid = x_rowid;
97
98 BEGIN
99
100 l_rowid := x_rowid;
101
102 -- Code for setting the Old and New Reference Values.
103 -- Populate Old Values.
104 OPEN cur_old_ref_values;
105 FETCH cur_old_ref_values INTO old_references;
106 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
107 CLOSE cur_old_ref_values;
108 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
109 igs_ge_msg_stack.add;
110 app_exception.raise_exception;
111 RETURN;
112 END IF;
113 CLOSE cur_old_ref_values;
114
115 -- Populate New Values.
116 new_references.cdbr_id := x_cdbr_id;
117 new_references.cbth_id := x_cbth_id;
118 new_references.record_type := x_record_type;
119 new_references.loan_number := x_loan_number;
120 new_references.cl_seq_number := x_cl_seq_number;
121 new_references.b_last_name := x_b_last_name;
122 new_references.b_first_name := x_b_first_name;
123 new_references.b_middle_name := x_b_middle_name;
124 new_references.b_ssn := x_b_ssn;
125 new_references.b_addr_line_1 := x_b_addr_line_1;
126 new_references.b_addr_line_2 := x_b_addr_line_2;
127 new_references.b_city := x_b_city;
128 new_references.b_state := x_b_state;
129 new_references.b_zip := x_b_zip;
130 new_references.b_zip_suffix := x_b_zip_suffix;
131 new_references.b_addr_chg_date := x_b_addr_chg_date;
132 new_references.eft_auth_code := x_eft_auth_code;
133 new_references.s_last_name := x_s_last_name;
134 new_references.s_first_name := x_s_first_name;
135 new_references.s_middle_initial := x_s_middle_initial;
136 new_references.s_ssn := x_s_ssn;
137 new_references.school_id := x_school_id;
138 new_references.school_use := x_school_use;
139 new_references.loan_per_start_date := x_loan_per_start_date;
140 new_references.loan_per_end_date := x_loan_per_end_date;
141 new_references.cl_loan_type := x_cl_loan_type;
142 new_references.alt_prog_type_code := x_alt_prog_type_code;
143 new_references.lender_id := x_lender_id;
144 new_references.lend_non_ed_brc_id := x_lend_non_ed_brc_id;
145 new_references.lender_use := x_lender_use;
146 new_references.borw_confirm_ind := x_borw_confirm_ind;
147 new_references.tot_sched_disb := x_tot_sched_disb;
148 new_references.fund_release_date := x_fund_release_date;
149 new_references.disb_num := x_disb_num;
150 new_references.guarantor_id := x_guarantor_id;
151 new_references.guarantor_use := x_guarantor_use;
152 new_references.guarantee_date := x_guarantee_date;
153 new_references.guarantee_amt := x_guarantee_amt;
154 new_references.gross_disb_amt := x_gross_disb_amt;
155 new_references.fee_1 := x_fee_1;
156 new_references.fee_2 := x_fee_2;
157 new_references.net_disb_amt := x_net_disb_amt;
158 new_references.fund_dist_mthd := x_fund_dist_mthd;
159 new_references.check_number := x_check_number;
160 new_references.late_disb_ind := x_late_disb_ind;
161 new_references.prev_reported_ind := x_prev_reported_ind;
162 new_references.err_code1 := x_err_code1;
163 new_references.err_code2 := x_err_code2;
164 new_references.err_code3 := x_err_code3;
165 new_references.err_code4 := x_err_code4;
166 new_references.err_code5 := x_err_code5;
167 new_references.fee_paid_2 := x_fee_paid_2;
168 new_references.lender_name := x_lender_name;
169 new_references.net_cancel_amt := x_net_cancel_amt;
170 new_references.duns_lender_id := x_duns_lender_id;
171 new_references.duns_guarnt_id := x_duns_guarnt_id;
172 new_references.hold_rel_ind := x_hold_rel_ind;
173 new_references.pnote_code := x_pnote_code;
174 new_references.pnote_status_date := x_pnote_status_date;
175 new_references.fee_paid_1 := x_fee_paid_1;
176 new_references.netted_cancel_amt := x_netted_cancel_amt;
177 new_references.outstd_cancel_amt := x_outstd_cancel_amt;
178 new_references.sch_non_ed_brc_id := x_sch_non_ed_brc_id;
179 new_references.status := x_status;
180 new_references.esign_src_typ_cd := x_esign_src_typ_cd;
181 new_references.direct_to_borr_flag := x_direct_to_borr_flag;
182
183
184 IF (p_action = 'UPDATE') THEN
185 new_references.creation_date := old_references.creation_date;
186 new_references.created_by := old_references.created_by;
187 ELSE
188 new_references.creation_date := x_creation_date;
189 new_references.created_by := x_created_by;
190 END IF;
191
192 new_references.last_update_date := x_last_update_date;
193 new_references.last_updated_by := x_last_updated_by;
194 new_references.last_update_login := x_last_update_login;
195
196 END set_column_values;
197
198
199 PROCEDURE check_parent_existance AS
200 /*
201 || Created By : prchandr
202 || Created On : 14-DEC-2000
203 || Purpose : Checks for the existance of Parent records.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209 BEGIN
210
211 IF (((old_references.cbth_id = new_references.cbth_id)) OR
212 ((new_references.cbth_id IS NULL))) THEN
213 NULL;
214 ELSIF NOT igf_sl_cl_batch_pkg.get_pk_for_validation (
215 new_references.cbth_id
216 ) THEN
217 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
218 igs_ge_msg_stack.add;
219 app_exception.raise_exception;
220 END IF;
221
222 END check_parent_existance;
223
224
225 FUNCTION get_pk_for_validation (
226 x_cdbr_id IN NUMBER
227 ) RETURN BOOLEAN AS
228 /*
229 || Created By : prchandr
230 || Created On : 14-DEC-2000
231 || Purpose : Validates the Primary Key of the table.
232 || Known limitations, enhancements or remarks :
233 || Change History :
234 || Who When What
235 || (reverse chronological order - newest change first)
236 */
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM igf_db_cl_disb_resp_all
240 WHERE cdbr_id = x_cdbr_id
241 FOR UPDATE NOWAIT;
242
243 lv_rowid cur_rowid%RowType;
244
245 BEGIN
246
247 OPEN cur_rowid;
248 FETCH cur_rowid INTO lv_rowid;
249 IF (cur_rowid%FOUND) THEN
250 CLOSE cur_rowid;
251 RETURN(TRUE);
252 ELSE
253 CLOSE cur_rowid;
254 RETURN(FALSE);
255 END IF;
256
257 END get_pk_for_validation;
258
259
260 PROCEDURE get_fk_igf_sl_cl_batch (
261 x_cbth_id IN NUMBER
262 ) AS
263 /*
264 || Created By : prchandr
265 || Created On : 14-DEC-2000
266 || Purpose : Validates the Foreign Keys for the table.
267 || Known limitations, enhancements or remarks :
268 || Change History :
269 || Who When What
270 || (reverse chronological order - newest change first)
271 */
272 CURSOR cur_rowid IS
273 SELECT rowid
274 FROM igf_db_cl_disb_resp_all
275 WHERE ((cbth_id = x_cbth_id));
276
277 lv_rowid cur_rowid%RowType;
278
279 BEGIN
280
281 OPEN cur_rowid;
282 FETCH cur_rowid INTO lv_rowid;
283 IF (cur_rowid%FOUND) THEN
284 CLOSE cur_rowid;
285 fnd_message.set_name ('IGF', 'IGF_DB_CDBR_CBTH_FK');
286 igs_ge_msg_stack.add;
287 app_exception.raise_exception;
288 RETURN;
289 END IF;
290 CLOSE cur_rowid;
291
292 END get_fk_igf_sl_cl_batch;
293
294
295 PROCEDURE before_dml (
296 p_action IN VARCHAR2,
297 x_rowid IN VARCHAR2,
298 x_cdbr_id IN NUMBER ,
299 x_cbth_id IN NUMBER ,
300 x_record_type IN VARCHAR2,
301 x_loan_number IN VARCHAR2,
302 x_cl_seq_number IN NUMBER ,
303 x_b_last_name IN VARCHAR2,
304 x_b_first_name IN VARCHAR2,
305 x_b_middle_name IN VARCHAR2,
306 x_b_ssn IN VARCHAR2,
307 x_b_addr_line_1 IN VARCHAR2,
308 x_b_addr_line_2 IN VARCHAR2,
309 x_b_city IN VARCHAR2,
310 x_b_state IN VARCHAR2,
311 x_b_zip IN NUMBER ,
312 x_b_zip_suffix IN NUMBER ,
313 x_b_addr_chg_date IN DATE ,
314 x_eft_auth_code IN VARCHAR2,
315 x_s_last_name IN VARCHAR2,
316 x_s_first_name IN VARCHAR2,
317 x_s_middle_initial IN VARCHAR2,
318 x_s_ssn IN VARCHAR2,
319 x_school_id IN NUMBER ,
320 x_school_use IN VARCHAR2,
321 x_loan_per_start_date IN DATE ,
322 x_loan_per_end_date IN DATE ,
323 x_cl_loan_type IN VARCHAR2,
324 x_alt_prog_type_code IN VARCHAR2,
325 x_lender_id IN VARCHAR2,
326 x_lend_non_ed_brc_id IN VARCHAR2,
327 x_lender_use IN VARCHAR2,
328 x_borw_confirm_ind IN VARCHAR2,
329 x_tot_sched_disb IN NUMBER ,
330 x_fund_release_date IN DATE ,
331 x_disb_num IN NUMBER ,
332 x_guarantor_id IN VARCHAR2,
336 x_gross_disb_amt IN NUMBER ,
333 x_guarantor_use IN VARCHAR2,
334 x_guarantee_date IN DATE ,
335 x_guarantee_amt IN NUMBER ,
337 x_fee_1 IN NUMBER ,
338 x_fee_2 IN NUMBER ,
339 x_net_disb_amt IN NUMBER ,
340 x_fund_dist_mthd IN VARCHAR2,
341 x_check_number IN VARCHAR2,
342 x_late_disb_ind IN VARCHAR2,
343 x_prev_reported_ind IN VARCHAR2,
344 x_err_code1 IN VARCHAR2,
345 x_err_code2 IN VARCHAR2,
346 x_err_code3 IN VARCHAR2,
347 x_err_code4 IN VARCHAR2,
348 x_err_code5 IN VARCHAR2,
349 x_fee_paid_2 IN NUMBER ,
350 x_lender_name IN VARCHAR2,
351 x_net_cancel_amt IN NUMBER ,
352 x_duns_lender_id IN VARCHAR2,
353 x_duns_guarnt_id IN VARCHAR2,
354 x_hold_rel_ind IN VARCHAR2,
355 x_pnote_code IN VARCHAR2,
356 x_pnote_status_date IN DATE ,
357 x_fee_paid_1 IN NUMBER ,
358 x_netted_cancel_amt IN NUMBER ,
359 x_outstd_cancel_amt IN NUMBER ,
360 x_sch_non_ed_brc_id IN VARCHAR2,
361 x_status IN VARCHAR2,
362 x_creation_date IN DATE ,
363 x_created_by IN NUMBER ,
364 x_last_update_date IN DATE ,
365 x_last_updated_by IN NUMBER ,
366 x_last_update_login IN NUMBER ,
367 x_esign_src_typ_cd IN VARCHAR2,
368 x_direct_to_borr_flag IN VARCHAR2
369 ) AS
370 /*
371 || Created By : prchandr
372 || Created On : 14-DEC-2000
373 || Purpose : Initialises the columns, Checks Constraints, Calls the
374 || Trigger Handlers for the table, before any DML operation.
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 */
380 BEGIN
381
382 set_column_values (
383 p_action,
384 x_rowid,
385 x_cdbr_id,
386 x_cbth_id,
387 x_record_type,
388 x_loan_number,
389 x_cl_seq_number,
390 x_b_last_name,
391 x_b_first_name,
392 x_b_middle_name,
393 x_b_ssn,
394 x_b_addr_line_1,
395 x_b_addr_line_2,
396 x_b_city,
397 x_b_state,
398 x_b_zip,
399 x_b_zip_suffix,
400 x_b_addr_chg_date,
401 x_eft_auth_code,
402 x_s_last_name,
403 x_s_first_name,
404 x_s_middle_initial,
405 x_s_ssn,
406 x_school_id,
407 x_school_use,
408 x_loan_per_start_date,
409 x_loan_per_end_date,
410 x_cl_loan_type,
411 x_alt_prog_type_code,
412 x_lender_id,
413 x_lend_non_ed_brc_id,
414 x_lender_use,
415 x_borw_confirm_ind,
416 x_tot_sched_disb,
417 x_fund_release_date,
418 x_disb_num,
419 x_guarantor_id,
420 x_guarantor_use,
421 x_guarantee_date,
422 x_guarantee_amt,
423 x_gross_disb_amt,
424 x_fee_1,
425 x_fee_2,
426 x_net_disb_amt,
427 x_fund_dist_mthd,
428 x_check_number,
429 x_late_disb_ind,
430 x_prev_reported_ind,
431 x_err_code1,
432 x_err_code2,
433 x_err_code3,
434 x_err_code4,
435 x_err_code5,
436 x_fee_paid_2,
437 x_lender_name,
438 x_net_cancel_amt,
439 x_duns_lender_id,
440 x_duns_guarnt_id,
441 x_hold_rel_ind,
442 x_pnote_code,
443 x_pnote_status_date,
444 x_fee_paid_1,
445 x_netted_cancel_amt,
446 x_outstd_cancel_amt,
447 x_sch_non_ed_brc_id,
448 x_status,
449 x_creation_date,
450 x_created_by,
451 x_last_update_date,
452 x_last_updated_by,
453 x_last_update_login,
454 x_esign_src_typ_cd,
455 x_direct_to_borr_flag
456 );
457
458 IF (p_action = 'INSERT') THEN
459 -- Call all the procedures related to Before Insert.
460 IF ( get_pk_for_validation(
461 new_references.cdbr_id
462 )
463 ) THEN
464 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
465 igs_ge_msg_stack.add;
466 app_exception.raise_exception;
467 END IF;
468 check_parent_existance;
469 ELSIF (p_action = 'UPDATE') THEN
470 -- Call all the procedures related to Before Update.
471 check_parent_existance;
472 ELSIF (p_action = 'VALIDATE_INSERT') THEN
473 -- Call all the procedures related to Before Insert.
474 IF ( get_pk_for_validation (
478 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
475 new_references.cdbr_id
476 )
477 ) THEN
479 igs_ge_msg_stack.add;
480 app_exception.raise_exception;
481 END IF;
482 END IF;
483
484 END before_dml;
485
486
487 PROCEDURE insert_row (
488 x_rowid IN OUT NOCOPY VARCHAR2,
489 x_cdbr_id IN OUT NOCOPY NUMBER,
490 x_cbth_id IN NUMBER,
491 x_record_type IN VARCHAR2,
492 x_loan_number IN VARCHAR2,
493 x_cl_seq_number IN NUMBER,
494 x_b_last_name IN VARCHAR2,
495 x_b_first_name IN VARCHAR2,
496 x_b_middle_name IN VARCHAR2,
497 x_b_ssn IN VARCHAR2,
498 x_b_addr_line_1 IN VARCHAR2,
499 x_b_addr_line_2 IN VARCHAR2,
500 x_b_city IN VARCHAR2,
501 x_b_state IN VARCHAR2,
502 x_b_zip IN NUMBER,
503 x_b_zip_suffix IN NUMBER,
504 x_b_addr_chg_date IN DATE,
505 x_eft_auth_code IN VARCHAR2,
506 x_s_last_name IN VARCHAR2,
507 x_s_first_name IN VARCHAR2,
508 x_s_middle_initial IN VARCHAR2,
509 x_s_ssn IN VARCHAR2,
510 x_school_id IN NUMBER,
511 x_school_use IN VARCHAR2,
512 x_loan_per_start_date IN DATE,
513 x_loan_per_end_date IN DATE,
514 x_cl_loan_type IN VARCHAR2,
515 x_alt_prog_type_code IN VARCHAR2,
516 x_lender_id IN VARCHAR2,
517 x_lend_non_ed_brc_id IN VARCHAR2,
518 x_lender_use IN VARCHAR2,
519 x_borw_confirm_ind IN VARCHAR2,
520 x_tot_sched_disb IN NUMBER,
521 x_fund_release_date IN DATE,
522 x_disb_num IN NUMBER,
523 x_guarantor_id IN VARCHAR2,
524 x_guarantor_use IN VARCHAR2,
525 x_guarantee_date IN DATE,
526 x_guarantee_amt IN NUMBER,
527 x_gross_disb_amt IN NUMBER,
528 x_fee_1 IN NUMBER,
529 x_fee_2 IN NUMBER,
530 x_net_disb_amt IN NUMBER,
531 x_fund_dist_mthd IN VARCHAR2,
532 x_check_number IN VARCHAR2,
533 x_late_disb_ind IN VARCHAR2,
534 x_prev_reported_ind IN VARCHAR2,
535 x_err_code1 IN VARCHAR2,
536 x_err_code2 IN VARCHAR2,
537 x_err_code3 IN VARCHAR2,
538 x_err_code4 IN VARCHAR2,
539 x_err_code5 IN VARCHAR2,
540 x_fee_paid_2 IN NUMBER,
541 x_lender_name IN VARCHAR2,
542 x_net_cancel_amt IN NUMBER,
543 x_duns_lender_id IN VARCHAR2,
544 x_duns_guarnt_id IN VARCHAR2,
545 x_hold_rel_ind IN VARCHAR2,
546 x_pnote_code IN VARCHAR2,
547 x_pnote_status_date IN DATE,
548 x_fee_paid_1 IN NUMBER,
549 x_netted_cancel_amt IN NUMBER,
550 x_outstd_cancel_amt IN NUMBER,
551 x_sch_non_ed_brc_id IN VARCHAR2,
552 x_status IN VARCHAR2,
553 x_mode IN VARCHAR2,
554 x_esign_src_typ_cd IN VARCHAR2,
555 x_direct_to_borr_flag IN VARCHAR2
556 ) AS
557 /*
558 || Created By : prchandr
559 || Created On : 14-DEC-2000
560 || Purpose : Handles the INSERT DML logic for the table.
561 || Known limitations, enhancements or remarks :
562 || Change History :
563 || Who When What
564 || (reverse chronological order - newest change first)
565 */
566 CURSOR c IS
567 SELECT rowid
568 FROM igf_db_cl_disb_resp_all
569 WHERE cdbr_id = x_cdbr_id;
570
571 x_last_update_date DATE;
572 x_last_updated_by NUMBER;
573 x_last_update_login NUMBER;
574 x_request_id NUMBER;
575 x_program_id NUMBER;
576 x_program_application_id NUMBER;
577 x_program_update_date DATE;
578 l_org_id igf_db_cl_disb_resp_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
579
580 BEGIN
581
582 x_last_update_date := SYSDATE;
583 IF (x_mode = 'I') THEN
584 x_last_updated_by := 1;
585 x_last_update_login := 0;
586 ELSIF (x_mode = 'R') THEN
587 x_last_updated_by := fnd_global.user_id;
591 x_last_update_login := fnd_global.login_id;
588 IF (x_last_updated_by IS NULL) THEN
589 x_last_updated_by := -1;
590 END IF;
592 IF (x_last_update_login IS NULL) THEN
593 x_last_update_login := -1;
594 END IF;
595 x_request_id := fnd_global.conc_request_id;
596 x_program_id := fnd_global.conc_program_id;
597 x_program_application_id := fnd_global.prog_appl_id;
598
599 IF (x_request_id = -1) THEN
600 x_request_id := NULL;
601 x_program_id := NULL;
602 x_program_application_id := NULL;
603 x_program_update_date := NULL;
604 ELSE
605 x_program_update_date := SYSDATE;
606 END IF;
607 ELSE
608 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
609 igs_ge_msg_stack.add;
610 app_exception.raise_exception;
611 END IF;
612
613 SELECT igf_db_cl_disb_resp_s.NEXTVAL
614 INTO x_cdbr_id
615 FROM dual;
616
617 before_dml(
618 p_action => 'INSERT',
619 x_rowid => x_rowid,
620 x_cdbr_id => x_cdbr_id,
621 x_cbth_id => x_cbth_id,
622 x_record_type => x_record_type,
623 x_loan_number => x_loan_number,
624 x_cl_seq_number => x_cl_seq_number,
625 x_b_last_name => x_b_last_name,
626 x_b_first_name => x_b_first_name,
627 x_b_middle_name => x_b_middle_name,
628 x_b_ssn => x_b_ssn,
629 x_b_addr_line_1 => x_b_addr_line_1,
630 x_b_addr_line_2 => x_b_addr_line_2,
631 x_b_city => x_b_city,
632 x_b_state => x_b_state,
633 x_b_zip => x_b_zip,
634 x_b_zip_suffix => x_b_zip_suffix,
635 x_b_addr_chg_date => x_b_addr_chg_date,
636 x_eft_auth_code => x_eft_auth_code,
637 x_s_last_name => x_s_last_name,
638 x_s_first_name => x_s_first_name,
639 x_s_middle_initial => x_s_middle_initial,
640 x_s_ssn => x_s_ssn,
641 x_school_id => x_school_id,
642 x_school_use => x_school_use,
643 x_loan_per_start_date => x_loan_per_start_date,
644 x_loan_per_end_date => x_loan_per_end_date,
645 x_cl_loan_type => x_cl_loan_type,
646 x_alt_prog_type_code => x_alt_prog_type_code,
647 x_lender_id => x_lender_id,
648 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
649 x_lender_use => x_lender_use,
650 x_borw_confirm_ind => x_borw_confirm_ind,
651 x_tot_sched_disb => x_tot_sched_disb,
652 x_fund_release_date => x_fund_release_date,
653 x_disb_num => x_disb_num,
654 x_guarantor_id => x_guarantor_id,
655 x_guarantor_use => x_guarantor_use,
656 x_guarantee_date => x_guarantee_date,
657 x_guarantee_amt => x_guarantee_amt,
658 x_gross_disb_amt => x_gross_disb_amt,
659 x_fee_1 => x_fee_1,
660 x_fee_2 => x_fee_2,
661 x_net_disb_amt => x_net_disb_amt,
662 x_fund_dist_mthd => x_fund_dist_mthd,
663 x_check_number => x_check_number,
664 x_late_disb_ind => x_late_disb_ind,
665 x_prev_reported_ind => x_prev_reported_ind,
666 x_err_code1 => x_err_code1,
667 x_err_code2 => x_err_code2,
668 x_err_code3 => x_err_code3,
669 x_err_code4 => x_err_code4,
670 x_err_code5 => x_err_code5,
671 x_fee_paid_2 => x_fee_paid_2,
672 x_lender_name => x_lender_name,
673 x_net_cancel_amt => x_net_cancel_amt,
674 x_duns_lender_id => x_duns_lender_id,
675 x_duns_guarnt_id => x_duns_guarnt_id,
676 x_hold_rel_ind => x_hold_rel_ind,
677 x_pnote_code => x_pnote_code,
678 x_pnote_status_date => x_pnote_status_date,
679 x_fee_paid_1 => x_fee_paid_1,
680 x_netted_cancel_amt => x_netted_cancel_amt,
681 x_outstd_cancel_amt => x_outstd_cancel_amt,
682 x_sch_non_ed_brc_id => x_sch_non_ed_brc_id,
683 x_status => x_status,
684 x_creation_date => x_last_update_date,
685 x_created_by => x_last_updated_by,
686 x_last_update_date => x_last_update_date,
687 x_last_updated_by => x_last_updated_by,
688 x_last_update_login => x_last_update_login,
689 x_esign_src_typ_cd => x_esign_src_typ_cd,
690 x_direct_to_borr_flag => x_direct_to_borr_flag
691 );
692
696 record_type,
693 INSERT INTO igf_db_cl_disb_resp_all (
694 cdbr_id,
695 cbth_id,
697 loan_number,
698 cl_seq_number,
699 b_last_name,
700 b_first_name,
701 b_middle_name,
702 b_ssn,
703 b_addr_line_1,
704 b_addr_line_2,
705 b_city,
706 b_state,
707 b_zip,
708 b_zip_suffix,
709 b_addr_chg_date,
710 eft_auth_code,
711 s_last_name,
712 s_first_name,
713 s_middle_initial,
714 s_ssn,
715 school_id,
716 school_use,
717 loan_per_start_date,
718 loan_per_end_date,
719 cl_loan_type,
720 alt_prog_type_code,
721 lender_id,
722 lend_non_ed_brc_id,
723 lender_use,
724 borw_confirm_ind,
725 tot_sched_disb,
726 fund_release_date,
727 disb_num,
728 guarantor_id,
729 guarantor_use,
730 guarantee_date,
731 guarantee_amt,
732 gross_disb_amt,
733 fee_1,
734 fee_2,
735 net_disb_amt,
736 fund_dist_mthd,
737 check_number,
738 late_disb_ind,
739 prev_reported_ind,
740 err_code1,
741 err_code2,
742 err_code3,
743 err_code4,
744 err_code5,
745 fee_paid_2,
746 lender_name,
747 net_cancel_amt,
748 duns_lender_id,
749 duns_guarnt_id,
750 hold_rel_ind,
751 pnote_code,
752 pnote_status_date,
753 fee_paid_1,
754 netted_cancel_amt,
755 outstd_cancel_amt,
756 sch_non_ed_brc_id,
757 status,
758 creation_date,
759 created_by,
760 last_update_date,
761 last_updated_by,
762 last_update_login,
763 request_id,
764 program_id,
765 program_application_id,
766 program_update_date,
767 org_id,
768 esign_src_typ_cd,
769 direct_to_borr_flag
770 ) VALUES (
771 new_references.cdbr_id,
772 new_references.cbth_id,
773 new_references.record_type,
774 new_references.loan_number,
775 new_references.cl_seq_number,
776 new_references.b_last_name,
777 new_references.b_first_name,
778 new_references.b_middle_name,
779 new_references.b_ssn,
780 new_references.b_addr_line_1,
781 new_references.b_addr_line_2,
782 new_references.b_city,
783 new_references.b_state,
784 new_references.b_zip,
785 new_references.b_zip_suffix,
786 new_references.b_addr_chg_date,
787 new_references.eft_auth_code,
788 new_references.s_last_name,
789 new_references.s_first_name,
790 new_references.s_middle_initial,
791 new_references.s_ssn,
792 new_references.school_id,
793 new_references.school_use,
794 new_references.loan_per_start_date,
795 new_references.loan_per_end_date,
796 new_references.cl_loan_type,
797 new_references.alt_prog_type_code,
798 new_references.lender_id,
799 new_references.lend_non_ed_brc_id,
800 new_references.lender_use,
801 new_references.borw_confirm_ind,
802 new_references.tot_sched_disb,
803 new_references.fund_release_date,
804 new_references.disb_num,
805 new_references.guarantor_id,
806 new_references.guarantor_use,
807 new_references.guarantee_date,
808 new_references.guarantee_amt,
809 new_references.gross_disb_amt,
810 new_references.fee_1,
811 new_references.fee_2,
812 new_references.net_disb_amt,
813 new_references.fund_dist_mthd,
814 new_references.check_number,
815 new_references.late_disb_ind,
816 new_references.prev_reported_ind,
817 new_references.err_code1,
818 new_references.err_code2,
819 new_references.err_code3,
820 new_references.err_code4,
821 new_references.err_code5,
822 new_references.fee_paid_2,
823 new_references.lender_name,
824 new_references.net_cancel_amt,
825 new_references.duns_lender_id,
826 new_references.duns_guarnt_id,
827 new_references.hold_rel_ind,
828 new_references.pnote_code,
829 new_references.pnote_status_date,
830 new_references.fee_paid_1,
831 new_references.netted_cancel_amt,
832 new_references.outstd_cancel_amt,
833 new_references.sch_non_ed_brc_id,
834 new_references.status,
835 x_last_update_date,
836 x_last_updated_by,
837 x_last_update_date,
838 x_last_updated_by,
839 x_last_update_login ,
840 x_request_id,
841 x_program_id,
842 x_program_application_id,
843 x_program_update_date,
844 l_org_id,
845 new_references.esign_src_typ_cd,
846 new_references.direct_to_borr_flag
847 );
848
849 OPEN c;
850 FETCH c INTO x_rowid;
851 IF (c%NOTFOUND) THEN
852 CLOSE c;
853 RAISE NO_DATA_FOUND;
854 END IF;
855 CLOSE c;
856
860 PROCEDURE lock_row (
857 END insert_row;
858
859
861 x_rowid IN VARCHAR2,
862 x_cdbr_id IN NUMBER,
863 x_cbth_id IN NUMBER,
864 x_record_type IN VARCHAR2,
865 x_loan_number IN VARCHAR2,
866 x_cl_seq_number IN NUMBER,
867 x_b_last_name IN VARCHAR2,
868 x_b_first_name IN VARCHAR2,
869 x_b_middle_name IN VARCHAR2,
870 x_b_ssn IN VARCHAR2,
871 x_b_addr_line_1 IN VARCHAR2,
872 x_b_addr_line_2 IN VARCHAR2,
873 x_b_city IN VARCHAR2,
874 x_b_state IN VARCHAR2,
875 x_b_zip IN NUMBER,
876 x_b_zip_suffix IN NUMBER,
877 x_b_addr_chg_date IN DATE,
878 x_eft_auth_code IN VARCHAR2,
879 x_s_last_name IN VARCHAR2,
880 x_s_first_name IN VARCHAR2,
881 x_s_middle_initial IN VARCHAR2,
882 x_s_ssn IN VARCHAR2,
883 x_school_id IN NUMBER,
884 x_school_use IN VARCHAR2,
885 x_loan_per_start_date IN DATE,
886 x_loan_per_end_date IN DATE,
887 x_cl_loan_type IN VARCHAR2,
888 x_alt_prog_type_code IN VARCHAR2,
889 x_lender_id IN VARCHAR2,
890 x_lend_non_ed_brc_id IN VARCHAR2,
891 x_lender_use IN VARCHAR2,
892 x_borw_confirm_ind IN VARCHAR2,
893 x_tot_sched_disb IN NUMBER,
894 x_fund_release_date IN DATE,
895 x_disb_num IN NUMBER,
896 x_guarantor_id IN VARCHAR2,
897 x_guarantor_use IN VARCHAR2,
898 x_guarantee_date IN DATE,
899 x_guarantee_amt IN NUMBER,
900 x_gross_disb_amt IN NUMBER,
901 x_fee_1 IN NUMBER,
902 x_fee_2 IN NUMBER,
903 x_net_disb_amt IN NUMBER,
904 x_fund_dist_mthd IN VARCHAR2,
905 x_check_number IN VARCHAR2,
906 x_late_disb_ind IN VARCHAR2,
907 x_prev_reported_ind IN VARCHAR2,
908 x_err_code1 IN VARCHAR2,
909 x_err_code2 IN VARCHAR2,
910 x_err_code3 IN VARCHAR2,
911 x_err_code4 IN VARCHAR2,
912 x_err_code5 IN VARCHAR2,
913 x_fee_paid_2 IN NUMBER,
914 x_lender_name IN VARCHAR2,
915 x_net_cancel_amt IN NUMBER,
916 x_duns_lender_id IN VARCHAR2,
917 x_duns_guarnt_id IN VARCHAR2,
918 x_hold_rel_ind IN VARCHAR2,
919 x_pnote_code IN VARCHAR2,
920 x_pnote_status_date IN DATE,
921 x_fee_paid_1 IN NUMBER,
922 x_netted_cancel_amt IN NUMBER,
923 x_outstd_cancel_amt IN NUMBER,
924 x_sch_non_ed_brc_id IN VARCHAR2,
925 x_status IN VARCHAR2,
926 x_esign_src_typ_cd IN VARCHAR2,
927 x_direct_to_borr_flag IN VARCHAR2
928 ) AS
929 /*
930 || Created By : prchandr
931 || Created On : 14-DEC-2000
932 || Purpose : Handles the LOCK mechanism for the table.
933 || Known limitations, enhancements or remarks :
934 || Change History :
935 || Who When What
936 || (reverse chronological order - newest change first)
937 */
938 CURSOR c1 IS
939 SELECT
940 cbth_id,
941 record_type,
942 loan_number,
943 cl_seq_number,
944 b_last_name,
945 b_first_name,
946 b_middle_name,
947 b_ssn,
948 b_addr_line_1,
949 b_addr_line_2,
950 b_city,
951 b_state,
952 b_zip,
953 b_zip_suffix,
954 b_addr_chg_date,
955 eft_auth_code,
956 s_last_name,
957 s_first_name,
958 s_middle_initial,
959 s_ssn,
960 school_id,
961 school_use,
962 loan_per_start_date,
963 loan_per_end_date,
964 cl_loan_type,
965 alt_prog_type_code,
966 lender_id,
967 lend_non_ed_brc_id,
968 lender_use,
969 borw_confirm_ind,
970 tot_sched_disb,
971 fund_release_date,
972 disb_num,
973 guarantor_id,
974 guarantor_use,
975 guarantee_date,
976 guarantee_amt,
980 net_disb_amt,
977 gross_disb_amt,
978 fee_1,
979 fee_2,
981 fund_dist_mthd,
982 check_number,
983 late_disb_ind,
984 prev_reported_ind,
985 err_code1,
986 err_code2,
987 err_code3,
988 err_code4,
989 err_code5,
990 fee_paid_2,
991 lender_name,
992 net_cancel_amt,
993 duns_lender_id,
994 duns_guarnt_id,
995 hold_rel_ind,
996 pnote_code,
997 pnote_status_date,
998 fee_paid_1,
999 netted_cancel_amt,
1000 outstd_cancel_amt,
1001 sch_non_ed_brc_id,
1002 status,
1003 org_id,
1004 esign_src_typ_cd,
1005 direct_to_borr_flag
1006 FROM igf_db_cl_disb_resp_all
1007 WHERE rowid = x_rowid
1008 FOR UPDATE NOWAIT;
1009
1010 tlinfo c1%ROWTYPE;
1011
1012 BEGIN
1013
1014 OPEN c1;
1015 FETCH c1 INTO tlinfo;
1016 IF (c1%notfound) THEN
1017 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1018 igs_ge_msg_stack.add;
1019 CLOSE c1;
1020 app_exception.raise_exception;
1021 RETURN;
1022 END IF;
1023 CLOSE c1;
1024
1025 IF (
1026 (tlinfo.cbth_id = x_cbth_id)
1027 AND ((tlinfo.record_type = x_record_type) OR ((tlinfo.record_type IS NULL) AND (X_record_type IS NULL)))
1028 AND (tlinfo.loan_number = x_loan_number)
1029 AND (tlinfo.cl_seq_number = x_cl_seq_number)
1030 AND ((tlinfo.b_last_name = x_b_last_name) OR ((tlinfo.b_last_name IS NULL) AND (X_b_last_name IS NULL)))
1031 AND ((tlinfo.b_first_name = x_b_first_name) OR ((tlinfo.b_first_name IS NULL) AND (X_b_first_name IS NULL)))
1032 AND ((tlinfo.b_middle_name = x_b_middle_name) OR ((tlinfo.b_middle_name IS NULL) AND (X_b_middle_name IS NULL)))
1033 AND ((tlinfo.b_ssn = x_b_ssn) OR ((tlinfo.b_ssn IS NULL) AND (X_b_ssn IS NULL)))
1034 AND ((tlinfo.b_addr_line_1 = x_b_addr_line_1) OR ((tlinfo.b_addr_line_1 IS NULL) AND (X_b_addr_line_1 IS NULL)))
1035 AND ((tlinfo.b_addr_line_2 = x_b_addr_line_2) OR ((tlinfo.b_addr_line_2 IS NULL) AND (X_b_addr_line_2 IS NULL)))
1036 AND ((tlinfo.b_city = x_b_city) OR ((tlinfo.b_city IS NULL) AND (X_b_city IS NULL)))
1037 AND ((tlinfo.b_state = x_b_state) OR ((tlinfo.b_state IS NULL) AND (X_b_state IS NULL)))
1038 AND ((tlinfo.b_zip = x_b_zip) OR ((tlinfo.b_zip IS NULL) AND (X_b_zip IS NULL)))
1039 AND ((tlinfo.b_zip_suffix = x_b_zip_suffix) OR ((tlinfo.b_zip_suffix IS NULL) AND (X_b_zip_suffix IS NULL)))
1040 AND ((tlinfo.b_addr_chg_date = x_b_addr_chg_date) OR ((tlinfo.b_addr_chg_date IS NULL) AND (X_b_addr_chg_date IS NULL)))
1041 AND ((tlinfo.eft_auth_code = x_eft_auth_code) OR ((tlinfo.eft_auth_code IS NULL) AND (X_eft_auth_code IS NULL)))
1042 AND ((tlinfo.s_last_name = x_s_last_name) OR ((tlinfo.s_last_name IS NULL) AND (X_s_last_name IS NULL)))
1043 AND ((tlinfo.s_first_name = x_s_first_name) OR ((tlinfo.s_first_name IS NULL) AND (X_s_first_name IS NULL)))
1044 AND ((tlinfo.s_middle_initial = x_s_middle_initial) OR ((tlinfo.s_middle_initial IS NULL) AND (X_s_middle_initial IS NULL)))
1045 AND ((tlinfo.s_ssn = x_s_ssn) OR ((tlinfo.s_ssn IS NULL) AND (X_s_ssn IS NULL)))
1046 AND (tlinfo.school_id = x_school_id)
1047 AND ((tlinfo.school_use = x_school_use) OR ((tlinfo.school_use IS NULL) AND (X_school_use IS NULL)))
1048 AND ((tlinfo.loan_per_start_date = x_loan_per_start_date) OR ((tlinfo.loan_per_start_date IS NULL) AND (X_loan_per_start_date IS NULL)))
1049 AND ((tlinfo.loan_per_end_date = x_loan_per_end_date) OR ((tlinfo.loan_per_end_date IS NULL) AND (X_loan_per_end_date IS NULL)))
1050 AND ((tlinfo.cl_loan_type = x_cl_loan_type) OR ((tlinfo.cl_loan_type IS NULL) AND (X_cl_loan_type IS NULL)))
1051 AND ((tlinfo.alt_prog_type_code = x_alt_prog_type_code) OR ((tlinfo.alt_prog_type_code IS NULL) AND (X_alt_prog_type_code IS NULL)))
1052 AND ((tlinfo.lender_id = x_lender_id) OR ((tlinfo.lender_id IS NULL) AND (X_lender_id IS NULL)))
1053 AND ((tlinfo.lend_non_ed_brc_id = x_lend_non_ed_brc_id) OR ((tlinfo.lend_non_ed_brc_id IS NULL) AND (X_lend_non_ed_brc_id IS NULL)))
1054 AND ((tlinfo.lender_use = x_lender_use) OR ((tlinfo.lender_use IS NULL) AND (X_lender_use IS NULL)))
1055 AND ((tlinfo.borw_confirm_ind = x_borw_confirm_ind) OR ((tlinfo.borw_confirm_ind IS NULL) AND (X_borw_confirm_ind IS NULL)))
1056 AND ((tlinfo.tot_sched_disb = x_tot_sched_disb) OR ((tlinfo.tot_sched_disb IS NULL) AND (X_tot_sched_disb IS NULL)))
1057 AND ((tlinfo.fund_release_date = x_fund_release_date) OR ((tlinfo.fund_release_date IS NULL) AND (X_fund_release_date IS NULL)))
1058 AND (tlinfo.disb_num = x_disb_num)
1059 AND ((tlinfo.guarantor_id = x_guarantor_id) OR ((tlinfo.guarantor_id IS NULL) AND (X_guarantor_id IS NULL)))
1060 AND ((tlinfo.guarantor_use = x_guarantor_use) OR ((tlinfo.guarantor_use IS NULL) AND (X_guarantor_use IS NULL)))
1061 AND ((tlinfo.guarantee_date = x_guarantee_date) OR ((tlinfo.guarantee_date IS NULL) AND (X_guarantee_date IS NULL)))
1062 AND ((tlinfo.guarantee_amt = x_guarantee_amt) OR ((tlinfo.guarantee_amt IS NULL) AND (X_guarantee_amt IS NULL)))
1063 AND ((tlinfo.gross_disb_amt = x_gross_disb_amt) OR ((tlinfo.gross_disb_amt IS NULL) AND (X_gross_disb_amt IS NULL)))
1064 AND ((tlinfo.fee_1 = x_fee_1) OR ((tlinfo.fee_1 IS NULL) AND (X_fee_1 IS NULL)))
1065 AND ((tlinfo.fee_2 = x_fee_2) OR ((tlinfo.fee_2 IS NULL) AND (X_fee_2 IS NULL)))
1069 AND ((tlinfo.late_disb_ind = x_late_disb_ind) OR ((tlinfo.late_disb_ind IS NULL) AND (X_late_disb_ind IS NULL)))
1066 AND ((tlinfo.net_disb_amt = x_net_disb_amt) OR ((tlinfo.net_disb_amt IS NULL) AND (X_net_disb_amt IS NULL)))
1067 AND ((tlinfo.fund_dist_mthd = x_fund_dist_mthd) OR ((tlinfo.fund_dist_mthd IS NULL) AND (X_fund_dist_mthd IS NULL)))
1068 AND ((tlinfo.check_number = x_check_number) OR ((tlinfo.check_number IS NULL) AND (X_check_number IS NULL)))
1070 AND ((tlinfo.prev_reported_ind = x_prev_reported_ind) OR ((tlinfo.prev_reported_ind IS NULL) AND (X_prev_reported_ind IS NULL)))
1071 AND ((tlinfo.err_code1 = x_err_code1) OR ((tlinfo.err_code1 IS NULL) AND (X_err_code1 IS NULL)))
1072 AND ((tlinfo.err_code2 = x_err_code2) OR ((tlinfo.err_code2 IS NULL) AND (X_err_code2 IS NULL)))
1073 AND ((tlinfo.err_code3 = x_err_code3) OR ((tlinfo.err_code3 IS NULL) AND (X_err_code3 IS NULL)))
1074 AND ((tlinfo.err_code4 = x_err_code4) OR ((tlinfo.err_code4 IS NULL) AND (X_err_code4 IS NULL)))
1075 AND ((tlinfo.err_code5 = x_err_code5) OR ((tlinfo.err_code5 IS NULL) AND (X_err_code5 IS NULL)))
1076 AND ((tlinfo.fee_paid_2 = x_fee_paid_2) OR ((tlinfo.fee_paid_2 IS NULL) AND (X_fee_paid_2 IS NULL)))
1077 AND ((tlinfo.lender_name = x_lender_name) OR ((tlinfo.lender_name IS NULL) AND (X_lender_name IS NULL)))
1078 AND ((tlinfo.net_cancel_amt = x_net_cancel_amt) OR ((tlinfo.net_cancel_amt IS NULL) AND (X_net_cancel_amt IS NULL)))
1079 AND ((tlinfo.duns_lender_id = x_duns_lender_id) OR ((tlinfo.duns_lender_id IS NULL) AND (X_duns_lender_id IS NULL)))
1080 AND ((tlinfo.duns_guarnt_id = x_duns_guarnt_id) OR ((tlinfo.duns_guarnt_id IS NULL) AND (X_duns_guarnt_id IS NULL)))
1081 AND ((tlinfo.hold_rel_ind = x_hold_rel_ind) OR ((tlinfo.hold_rel_ind IS NULL) AND (X_hold_rel_ind IS NULL)))
1082 AND ((tlinfo.pnote_code = x_pnote_code) OR ((tlinfo.pnote_code IS NULL) AND (X_pnote_code IS NULL)))
1083 AND ((tlinfo.pnote_status_date = x_pnote_status_date) OR ((tlinfo.pnote_status_date IS NULL) AND (X_pnote_status_date IS NULL)))
1084 AND ((tlinfo.fee_paid_1 = x_fee_paid_1) OR ((tlinfo.fee_paid_1 IS NULL) AND (X_fee_paid_1 IS NULL)))
1085 AND ((tlinfo.netted_cancel_amt = x_netted_cancel_amt) OR ((tlinfo.netted_cancel_amt IS NULL) AND (X_netted_cancel_amt IS NULL)))
1086 AND ((tlinfo.outstd_cancel_amt = x_outstd_cancel_amt) OR ((tlinfo.outstd_cancel_amt IS NULL) AND (X_outstd_cancel_amt IS NULL)))
1087 AND ((tlinfo.sch_non_ed_brc_id = x_sch_non_ed_brc_id) OR ((tlinfo.sch_non_ed_brc_id IS NULL) AND (X_sch_non_ed_brc_id IS NULL)))
1088 AND (tlinfo.status = x_status)
1089 AND ((tlinfo.esign_src_typ_cd = x_esign_src_typ_cd) OR ((tlinfo.esign_src_typ_cd IS NULL) AND (x_esign_src_typ_cd IS NULL)))
1090 AND ((tlinfo.direct_to_borr_flag = x_direct_to_borr_flag) OR ((tlinfo.direct_to_borr_flag IS NULL) AND (x_direct_to_borr_flag IS NULL)))
1091 ) THEN
1092 NULL;
1093 ELSE
1094 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1095 igs_ge_msg_stack.add;
1096 app_exception.raise_exception;
1097 END IF;
1098
1099 RETURN;
1100
1101 END lock_row;
1102
1103
1104 PROCEDURE update_row (
1105 x_rowid IN VARCHAR2,
1106 x_cdbr_id IN NUMBER,
1107 x_cbth_id IN NUMBER,
1108 x_record_type IN VARCHAR2,
1109 x_loan_number IN VARCHAR2,
1110 x_cl_seq_number IN NUMBER,
1111 x_b_last_name IN VARCHAR2,
1112 x_b_first_name IN VARCHAR2,
1113 x_b_middle_name IN VARCHAR2,
1114 x_b_ssn IN VARCHAR2,
1115 x_b_addr_line_1 IN VARCHAR2,
1116 x_b_addr_line_2 IN VARCHAR2,
1117 x_b_city IN VARCHAR2,
1118 x_b_state IN VARCHAR2,
1119 x_b_zip IN NUMBER,
1120 x_b_zip_suffix IN NUMBER,
1121 x_b_addr_chg_date IN DATE,
1122 x_eft_auth_code IN VARCHAR2,
1123 x_s_last_name IN VARCHAR2,
1124 x_s_first_name IN VARCHAR2,
1125 x_s_middle_initial IN VARCHAR2,
1126 x_s_ssn IN VARCHAR2,
1127 x_school_id IN NUMBER,
1128 x_school_use IN VARCHAR2,
1129 x_loan_per_start_date IN DATE,
1130 x_loan_per_end_date IN DATE,
1131 x_cl_loan_type IN VARCHAR2,
1132 x_alt_prog_type_code IN VARCHAR2,
1133 x_lender_id IN VARCHAR2,
1134 x_lend_non_ed_brc_id IN VARCHAR2,
1135 x_lender_use IN VARCHAR2,
1136 x_borw_confirm_ind IN VARCHAR2,
1137 x_tot_sched_disb IN NUMBER,
1138 x_fund_release_date IN DATE,
1139 x_disb_num IN NUMBER,
1140 x_guarantor_id IN VARCHAR2,
1141 x_guarantor_use IN VARCHAR2,
1142 x_guarantee_date IN DATE,
1143 x_guarantee_amt IN NUMBER,
1144 x_gross_disb_amt IN NUMBER,
1145 x_fee_1 IN NUMBER,
1146 x_fee_2 IN NUMBER,
1147 x_net_disb_amt IN NUMBER,
1151 x_prev_reported_ind IN VARCHAR2,
1148 x_fund_dist_mthd IN VARCHAR2,
1149 x_check_number IN VARCHAR2,
1150 x_late_disb_ind IN VARCHAR2,
1152 x_err_code1 IN VARCHAR2,
1153 x_err_code2 IN VARCHAR2,
1154 x_err_code3 IN VARCHAR2,
1155 x_err_code4 IN VARCHAR2,
1156 x_err_code5 IN VARCHAR2,
1157 x_fee_paid_2 IN NUMBER,
1158 x_lender_name IN VARCHAR2,
1159 x_net_cancel_amt IN NUMBER,
1160 x_duns_lender_id IN VARCHAR2,
1161 x_duns_guarnt_id IN VARCHAR2,
1162 x_hold_rel_ind IN VARCHAR2,
1163 x_pnote_code IN VARCHAR2,
1164 x_pnote_status_date IN DATE,
1165 x_fee_paid_1 IN NUMBER,
1166 x_netted_cancel_amt IN NUMBER,
1167 x_outstd_cancel_amt IN NUMBER,
1168 x_sch_non_ed_brc_id IN VARCHAR2,
1169 x_status IN VARCHAR2,
1170 x_mode IN VARCHAR2,
1171 x_esign_src_typ_cd IN VARCHAR2,
1172 x_direct_to_borr_flag IN VARCHAR2
1173 ) AS
1174 /*
1175 || Created By : prchandr
1176 || Created On : 14-DEC-2000
1177 || Purpose : Handles the UPDATE DML logic for the table.
1178 || Known limitations, enhancements or remarks :
1179 || Change History :
1180 || Who When What
1181 || (reverse chronological order - newest change first)
1182 */
1183 x_last_update_date DATE ;
1184 x_last_updated_by NUMBER;
1185 x_last_update_login NUMBER;
1186 x_request_id NUMBER;
1187 x_program_id NUMBER;
1188 x_program_application_id NUMBER;
1189 x_program_update_date DATE;
1190
1191 BEGIN
1192
1193 x_last_update_date := SYSDATE;
1194 IF (X_MODE = 'I') THEN
1195 x_last_updated_by := 1;
1196 x_last_update_login := 0;
1197 ELSIF (x_mode = 'R') THEN
1198 x_last_updated_by := fnd_global.user_id;
1199 IF x_last_updated_by IS NULL THEN
1200 x_last_updated_by := -1;
1201 END IF;
1202 x_last_update_login := fnd_global.login_id;
1203 IF (x_last_update_login IS NULL) THEN
1204 x_last_update_login := -1;
1205 END IF;
1206 ELSE
1207 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1208 igs_ge_msg_stack.add;
1209 app_exception.raise_exception;
1210 END IF;
1211
1212 before_dml(
1213 p_action => 'UPDATE',
1214 x_rowid => x_rowid,
1215 x_cdbr_id => x_cdbr_id,
1216 x_cbth_id => x_cbth_id,
1217 x_record_type => x_record_type,
1218 x_loan_number => x_loan_number,
1219 x_cl_seq_number => x_cl_seq_number,
1220 x_b_last_name => x_b_last_name,
1221 x_b_first_name => x_b_first_name,
1222 x_b_middle_name => x_b_middle_name,
1223 x_b_ssn => x_b_ssn,
1224 x_b_addr_line_1 => x_b_addr_line_1,
1225 x_b_addr_line_2 => x_b_addr_line_2,
1226 x_b_city => x_b_city,
1227 x_b_state => x_b_state,
1228 x_b_zip => x_b_zip,
1229 x_b_zip_suffix => x_b_zip_suffix,
1230 x_b_addr_chg_date => x_b_addr_chg_date,
1231 x_eft_auth_code => x_eft_auth_code,
1232 x_s_last_name => x_s_last_name,
1233 x_s_first_name => x_s_first_name,
1234 x_s_middle_initial => x_s_middle_initial,
1235 x_s_ssn => x_s_ssn,
1236 x_school_id => x_school_id,
1237 x_school_use => x_school_use,
1238 x_loan_per_start_date => x_loan_per_start_date,
1239 x_loan_per_end_date => x_loan_per_end_date,
1240 x_cl_loan_type => x_cl_loan_type,
1241 x_alt_prog_type_code => x_alt_prog_type_code,
1242 x_lender_id => x_lender_id,
1243 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
1244 x_lender_use => x_lender_use,
1245 x_borw_confirm_ind => x_borw_confirm_ind,
1246 x_tot_sched_disb => x_tot_sched_disb,
1247 x_fund_release_date => x_fund_release_date,
1248 x_disb_num => x_disb_num,
1249 x_guarantor_id => x_guarantor_id,
1250 x_guarantor_use => x_guarantor_use,
1251 x_guarantee_date => x_guarantee_date,
1252 x_guarantee_amt => x_guarantee_amt,
1253 x_gross_disb_amt => x_gross_disb_amt,
1254 x_fee_1 => x_fee_1,
1255 x_fee_2 => x_fee_2,
1256 x_net_disb_amt => x_net_disb_amt,
1260 x_prev_reported_ind => x_prev_reported_ind,
1257 x_fund_dist_mthd => x_fund_dist_mthd,
1258 x_check_number => x_check_number,
1259 x_late_disb_ind => x_late_disb_ind,
1261 x_err_code1 => x_err_code1,
1262 x_err_code2 => x_err_code2,
1263 x_err_code3 => x_err_code3,
1264 x_err_code4 => x_err_code4,
1265 x_err_code5 => x_err_code5,
1266 x_fee_paid_2 => x_fee_paid_2,
1267 x_lender_name => x_lender_name,
1268 x_net_cancel_amt => x_net_cancel_amt,
1269 x_duns_lender_id => x_duns_lender_id,
1270 x_duns_guarnt_id => x_duns_guarnt_id,
1271 x_hold_rel_ind => x_hold_rel_ind,
1272 x_pnote_code => x_pnote_code,
1273 x_pnote_status_date => x_pnote_status_date,
1274 x_fee_paid_1 => x_fee_paid_1,
1275 x_netted_cancel_amt => x_netted_cancel_amt,
1276 x_outstd_cancel_amt => x_outstd_cancel_amt,
1277 x_sch_non_ed_brc_id => x_sch_non_ed_brc_id,
1278 x_status => x_status,
1279 x_creation_date => x_last_update_date,
1280 x_created_by => x_last_updated_by,
1281 x_last_update_date => x_last_update_date,
1282 x_last_updated_by => x_last_updated_by,
1283 x_last_update_login => x_last_update_login,
1284 x_esign_src_typ_cd => x_esign_src_typ_cd,
1285 x_direct_to_borr_flag => x_direct_to_borr_flag
1286 );
1287
1288 IF (x_mode = 'R') THEN
1289 x_request_id := fnd_global.conc_request_id;
1290 x_program_id := fnd_global.conc_program_id;
1291 x_program_application_id := fnd_global.prog_appl_id;
1292 IF (x_request_id = -1) THEN
1293 x_request_id := old_references.request_id;
1294 x_program_id := old_references.program_id;
1295 x_program_application_id := old_references.program_application_id;
1296 x_program_update_date := old_references.program_update_date;
1297 ELSE
1298 x_program_update_date := SYSDATE;
1299 END IF;
1300 END IF;
1301
1302 UPDATE igf_db_cl_disb_resp_all
1303 SET
1304 cbth_id = new_references.cbth_id,
1305 record_type = new_references.record_type,
1306 loan_number = new_references.loan_number,
1307 cl_seq_number = new_references.cl_seq_number,
1308 b_last_name = new_references.b_last_name,
1309 b_first_name = new_references.b_first_name,
1310 b_middle_name = new_references.b_middle_name,
1311 b_ssn = new_references.b_ssn,
1312 b_addr_line_1 = new_references.b_addr_line_1,
1313 b_addr_line_2 = new_references.b_addr_line_2,
1314 b_city = new_references.b_city,
1315 b_state = new_references.b_state,
1316 b_zip = new_references.b_zip,
1317 b_zip_suffix = new_references.b_zip_suffix,
1318 b_addr_chg_date = new_references.b_addr_chg_date,
1319 eft_auth_code = new_references.eft_auth_code,
1320 s_last_name = new_references.s_last_name,
1321 s_first_name = new_references.s_first_name,
1322 s_middle_initial = new_references.s_middle_initial,
1323 s_ssn = new_references.s_ssn,
1324 school_id = new_references.school_id,
1325 school_use = new_references.school_use,
1326 loan_per_start_date = new_references.loan_per_start_date,
1327 loan_per_end_date = new_references.loan_per_end_date,
1328 cl_loan_type = new_references.cl_loan_type,
1329 alt_prog_type_code = new_references.alt_prog_type_code,
1330 lender_id = new_references.lender_id,
1331 lend_non_ed_brc_id = new_references.lend_non_ed_brc_id,
1332 lender_use = new_references.lender_use,
1333 borw_confirm_ind = new_references.borw_confirm_ind,
1334 tot_sched_disb = new_references.tot_sched_disb,
1335 fund_release_date = new_references.fund_release_date,
1336 disb_num = new_references.disb_num,
1337 guarantor_id = new_references.guarantor_id,
1338 guarantor_use = new_references.guarantor_use,
1339 guarantee_date = new_references.guarantee_date,
1340 guarantee_amt = new_references.guarantee_amt,
1341 gross_disb_amt = new_references.gross_disb_amt,
1342 fee_1 = new_references.fee_1,
1343 fee_2 = new_references.fee_2,
1347 late_disb_ind = new_references.late_disb_ind,
1344 net_disb_amt = new_references.net_disb_amt,
1345 fund_dist_mthd = new_references.fund_dist_mthd,
1346 check_number = new_references.check_number,
1348 prev_reported_ind = new_references.prev_reported_ind,
1349 err_code1 = new_references.err_code1,
1350 err_code2 = new_references.err_code2,
1351 err_code3 = new_references.err_code3,
1352 err_code4 = new_references.err_code4,
1353 err_code5 = new_references.err_code5,
1354 fee_paid_2 = new_references.fee_paid_2,
1355 lender_name = new_references.lender_name,
1356 net_cancel_amt = new_references.net_cancel_amt,
1357 duns_lender_id = new_references.duns_lender_id,
1358 duns_guarnt_id = new_references.duns_guarnt_id,
1359 hold_rel_ind = new_references.hold_rel_ind,
1360 pnote_code = new_references.pnote_code,
1361 pnote_status_date = new_references.pnote_status_date,
1362 fee_paid_1 = new_references.fee_paid_1,
1363 netted_cancel_amt = new_references.netted_cancel_amt,
1364 outstd_cancel_amt = new_references.outstd_cancel_amt,
1365 sch_non_ed_brc_id = new_references.sch_non_ed_brc_id,
1366 status = new_references.status,
1367 last_update_date = x_last_update_date,
1368 last_updated_by = x_last_updated_by,
1369 last_update_login = x_last_update_login ,
1370 request_id = x_request_id,
1371 program_id = x_program_id,
1372 program_application_id = x_program_application_id,
1373 program_update_date = x_program_update_date,
1374 esign_src_typ_cd = new_references.esign_src_typ_cd,
1375 direct_to_borr_flag = new_references.direct_to_borr_flag
1376 WHERE rowid = x_rowid;
1377
1378 IF (SQL%NOTFOUND) THEN
1379 RAISE NO_DATA_FOUND;
1380 END IF;
1381
1382 END update_row;
1383
1384
1385 PROCEDURE add_row (
1386 x_rowid IN OUT NOCOPY VARCHAR2,
1387 x_cdbr_id IN OUT NOCOPY NUMBER,
1388 x_cbth_id IN NUMBER,
1389 x_record_type IN VARCHAR2,
1390 x_loan_number IN VARCHAR2,
1391 x_cl_seq_number IN NUMBER,
1392 x_b_last_name IN VARCHAR2,
1393 x_b_first_name IN VARCHAR2,
1394 x_b_middle_name IN VARCHAR2,
1395 x_b_ssn IN VARCHAR2,
1396 x_b_addr_line_1 IN VARCHAR2,
1397 x_b_addr_line_2 IN VARCHAR2,
1398 x_b_city IN VARCHAR2,
1399 x_b_state IN VARCHAR2,
1400 x_b_zip IN NUMBER,
1401 x_b_zip_suffix IN NUMBER,
1402 x_b_addr_chg_date IN DATE,
1403 x_eft_auth_code IN VARCHAR2,
1404 x_s_last_name IN VARCHAR2,
1405 x_s_first_name IN VARCHAR2,
1406 x_s_middle_initial IN VARCHAR2,
1407 x_s_ssn IN VARCHAR2,
1408 x_school_id IN NUMBER,
1409 x_school_use IN VARCHAR2,
1410 x_loan_per_start_date IN DATE,
1411 x_loan_per_end_date IN DATE,
1412 x_cl_loan_type IN VARCHAR2,
1413 x_alt_prog_type_code IN VARCHAR2,
1414 x_lender_id IN VARCHAR2,
1415 x_lend_non_ed_brc_id IN VARCHAR2,
1416 x_lender_use IN VARCHAR2,
1417 x_borw_confirm_ind IN VARCHAR2,
1418 x_tot_sched_disb IN NUMBER,
1419 x_fund_release_date IN DATE,
1420 x_disb_num IN NUMBER,
1421 x_guarantor_id IN VARCHAR2,
1422 x_guarantor_use IN VARCHAR2,
1423 x_guarantee_date IN DATE,
1424 x_guarantee_amt IN NUMBER,
1425 x_gross_disb_amt IN NUMBER,
1426 x_fee_1 IN NUMBER,
1427 x_fee_2 IN NUMBER,
1428 x_net_disb_amt IN NUMBER,
1429 x_fund_dist_mthd IN VARCHAR2,
1430 x_check_number IN VARCHAR2,
1431 x_late_disb_ind IN VARCHAR2,
1432 x_prev_reported_ind IN VARCHAR2,
1433 x_err_code1 IN VARCHAR2,
1434 x_err_code2 IN VARCHAR2,
1435 x_err_code3 IN VARCHAR2,
1436 x_err_code4 IN VARCHAR2,
1440 x_net_cancel_amt IN NUMBER,
1437 x_err_code5 IN VARCHAR2,
1438 x_fee_paid_2 IN NUMBER,
1439 x_lender_name IN VARCHAR2,
1441 x_duns_lender_id IN VARCHAR2,
1442 x_duns_guarnt_id IN VARCHAR2,
1443 x_hold_rel_ind IN VARCHAR2,
1444 x_pnote_code IN VARCHAR2,
1445 x_pnote_status_date IN DATE,
1446 x_fee_paid_1 IN NUMBER,
1447 x_netted_cancel_amt IN NUMBER,
1448 x_outstd_cancel_amt IN NUMBER,
1449 x_sch_non_ed_brc_id IN VARCHAR2,
1450 x_status IN VARCHAR2,
1451 x_mode IN VARCHAR2,
1452 x_esign_src_typ_cd IN VARCHAR2,
1453 x_direct_to_borr_flag IN VARCHAR2
1454 ) AS
1455 /*
1456 || Created By : prchandr
1457 || Created On : 14-DEC-2000
1458 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1459 || Known limitations, enhancements or remarks :
1460 || Change History :
1461 || Who When What
1462 || (reverse chronological order - newest change first)
1463 */
1464 CURSOR c1 IS
1465 SELECT rowid
1466 FROM igf_db_cl_disb_resp_all
1467 WHERE cdbr_id = x_cdbr_id;
1468
1469 BEGIN
1470
1471 OPEN c1;
1472 FETCH c1 INTO x_rowid;
1473 IF (c1%NOTFOUND) THEN
1474 CLOSE c1;
1475
1476 insert_row (
1477 x_rowid,
1478 x_cdbr_id,
1479 x_cbth_id,
1480 x_record_type,
1481 x_loan_number,
1482 x_cl_seq_number,
1483 x_b_last_name,
1484 x_b_first_name,
1485 x_b_middle_name,
1486 x_b_ssn,
1487 x_b_addr_line_1,
1488 x_b_addr_line_2,
1489 x_b_city,
1490 x_b_state,
1491 x_b_zip,
1492 x_b_zip_suffix,
1493 x_b_addr_chg_date,
1494 x_eft_auth_code,
1495 x_s_last_name,
1496 x_s_first_name,
1497 x_s_middle_initial,
1498 x_s_ssn,
1499 x_school_id,
1500 x_school_use,
1501 x_loan_per_start_date,
1502 x_loan_per_end_date,
1503 x_cl_loan_type,
1504 x_alt_prog_type_code,
1505 x_lender_id,
1506 x_lend_non_ed_brc_id,
1507 x_lender_use,
1508 x_borw_confirm_ind,
1509 x_tot_sched_disb,
1510 x_fund_release_date,
1511 x_disb_num,
1512 x_guarantor_id,
1513 x_guarantor_use,
1514 x_guarantee_date,
1515 x_guarantee_amt,
1516 x_gross_disb_amt,
1517 x_fee_1,
1518 x_fee_2,
1519 x_net_disb_amt,
1520 x_fund_dist_mthd,
1521 x_check_number,
1522 x_late_disb_ind,
1523 x_prev_reported_ind,
1524 x_err_code1,
1525 x_err_code2,
1526 x_err_code3,
1527 x_err_code4,
1528 x_err_code5,
1529 x_fee_paid_2,
1530 x_lender_name,
1531 x_net_cancel_amt,
1532 x_duns_lender_id,
1533 x_duns_guarnt_id,
1534 x_hold_rel_ind,
1535 x_pnote_code,
1536 x_pnote_status_date,
1537 x_fee_paid_1,
1538 x_netted_cancel_amt,
1539 x_outstd_cancel_amt,
1540 x_sch_non_ed_brc_id,
1541 x_status,
1542 x_mode,
1543 x_esign_src_typ_cd,
1544 x_direct_to_borr_flag
1545 );
1546 RETURN;
1547 END IF;
1548 CLOSE c1;
1549
1550 update_row (
1551 x_rowid,
1552 x_cdbr_id,
1553 x_cbth_id,
1554 x_record_type,
1555 x_loan_number,
1556 x_cl_seq_number,
1557 x_b_last_name,
1558 x_b_first_name,
1559 x_b_middle_name,
1560 x_b_ssn,
1561 x_b_addr_line_1,
1562 x_b_addr_line_2,
1563 x_b_city,
1564 x_b_state,
1565 x_b_zip,
1566 x_b_zip_suffix,
1567 x_b_addr_chg_date,
1568 x_eft_auth_code,
1569 x_s_last_name,
1570 x_s_first_name,
1571 x_s_middle_initial,
1572 x_s_ssn,
1573 x_school_id,
1574 x_school_use,
1575 x_loan_per_start_date,
1576 x_loan_per_end_date,
1577 x_cl_loan_type,
1578 x_alt_prog_type_code,
1579 x_lender_id,
1580 x_lend_non_ed_brc_id,
1581 x_lender_use,
1582 x_borw_confirm_ind,
1583 x_tot_sched_disb,
1584 x_fund_release_date,
1585 x_disb_num,
1586 x_guarantor_id,
1587 x_guarantor_use,
1588 x_guarantee_date,
1589 x_guarantee_amt,
1590 x_gross_disb_amt,
1591 x_fee_1,
1592 x_fee_2,
1593 x_net_disb_amt,
1594 x_fund_dist_mthd,
1595 x_check_number,
1596 x_late_disb_ind,
1597 x_prev_reported_ind,
1598 x_err_code1,
1599 x_err_code2,
1600 x_err_code3,
1601 x_err_code4,
1602 x_err_code5,
1603 x_fee_paid_2,
1604 x_lender_name,
1605 x_net_cancel_amt,
1606 x_duns_lender_id,
1607 x_duns_guarnt_id,
1608 x_hold_rel_ind,
1609 x_pnote_code,
1610 x_pnote_status_date,
1611 x_fee_paid_1,
1612 x_netted_cancel_amt,
1613 x_outstd_cancel_amt,
1614 x_sch_non_ed_brc_id,
1615 x_status,
1616 x_mode,
1617 x_esign_src_typ_cd,
1618 x_direct_to_borr_flag
1619 );
1620
1621 END add_row;
1622
1623
1624 PROCEDURE delete_row (
1625 x_rowid IN VARCHAR2
1626 ) AS
1627 /*
1628 || Created By : prchandr
1629 || Created On : 14-DEC-2000
1630 || Purpose : Handles the DELETE DML logic for the table.
1631 || Known limitations, enhancements or remarks :
1632 || Change History :
1633 || Who When What
1634 || (reverse chronological order - newest change first)
1635 */
1636 BEGIN
1637
1638 before_dml (
1639 p_action => 'DELETE',
1640 x_rowid => x_rowid
1641 );
1642
1643 DELETE FROM igf_db_cl_disb_resp_all
1644 WHERE rowid = x_rowid;
1645
1646 IF (SQL%NOTFOUND) THEN
1647 RAISE NO_DATA_FOUND;
1648 END IF;
1649
1650 END delete_row;
1651
1652
1653 END igf_db_cl_disb_resp_pkg;