[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CL_RESP_R1_PKG
Source
1 PACKAGE BODY igf_sl_cl_resp_r1_pkg AS
2 /* $Header: IGFLI22B.pls 120.1 2006/04/19 08:25:43 bvisvana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_cl_resp_r1_all%ROWTYPE;
6 new_references igf_sl_cl_resp_r1_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_clrp1_id IN NUMBER DEFAULT NULL,
12 x_cbth_id IN NUMBER DEFAULT NULL,
13 x_rec_code IN VARCHAR2 DEFAULT NULL,
14 x_rec_type_ind IN VARCHAR2 DEFAULT NULL,
15 x_b_last_name IN VARCHAR2 DEFAULT NULL,
16 x_b_first_name IN VARCHAR2 DEFAULT NULL,
17 x_b_middle_name IN VARCHAR2 DEFAULT NULL,
18 x_b_ssn IN NUMBER DEFAULT NULL,
19 x_b_permt_addr1 IN VARCHAR2 DEFAULT NULL,
20 x_b_permt_addr2 IN VARCHAR2 DEFAULT NULL,
21 x_b_permt_city IN VARCHAR2 DEFAULT NULL,
22 x_b_permt_state IN VARCHAR2 DEFAULT NULL,
23 x_b_permt_zip IN NUMBER DEFAULT NULL,
24 x_b_permt_zip_suffix IN NUMBER DEFAULT NULL,
25 x_b_permt_phone IN VARCHAR2 DEFAULT NULL,
26 x_b_date_of_birth IN DATE DEFAULT NULL,
27 x_cl_loan_type IN VARCHAR2 DEFAULT NULL,
28 x_req_loan_amt IN NUMBER DEFAULT NULL,
29 x_defer_req_code IN VARCHAR2 DEFAULT NULL,
30 x_borw_interest_ind IN VARCHAR2 DEFAULT NULL,
31 x_eft_auth_code IN VARCHAR2 DEFAULT NULL,
32 x_b_signature_code IN VARCHAR2 DEFAULT NULL,
33 x_b_signature_date IN DATE DEFAULT NULL,
34 x_loan_number IN VARCHAR2 DEFAULT NULL,
35 x_cl_seq_number IN NUMBER DEFAULT NULL,
36 x_b_citizenship_status IN VARCHAR2 DEFAULT NULL,
37 x_b_state_of_legal_res IN VARCHAR2 DEFAULT NULL,
38 x_b_legal_res_date IN DATE DEFAULT NULL,
39 x_b_default_status IN VARCHAR2 DEFAULT NULL,
40 x_b_outstd_loan_code IN VARCHAR2 DEFAULT NULL,
41 x_b_indicator_code IN VARCHAR2 DEFAULT NULL,
42 x_s_last_name IN VARCHAR2 DEFAULT NULL,
43 x_s_first_name IN VARCHAR2 DEFAULT NULL,
44 x_s_middle_name IN VARCHAR2 DEFAULT NULL,
45 x_s_ssn IN NUMBER DEFAULT NULL,
46 x_s_date_of_birth IN DATE DEFAULT NULL,
47 x_s_citizenship_status IN VARCHAR2 DEFAULT NULL,
48 x_s_default_code IN VARCHAR2 DEFAULT NULL,
49 x_s_signature_code IN VARCHAR2 DEFAULT NULL,
50 x_school_id IN NUMBER DEFAULT NULL,
51 x_loan_per_begin_date IN DATE DEFAULT NULL,
52 x_loan_per_end_date IN DATE DEFAULT NULL,
53 x_grade_level_code IN VARCHAR2 DEFAULT NULL,
54 x_enrollment_code IN VARCHAR2 DEFAULT NULL,
55 x_anticip_compl_date IN DATE DEFAULT NULL,
56 x_coa_amt IN NUMBER DEFAULT NULL,
57 x_efc_amt IN NUMBER DEFAULT NULL,
58 x_est_fa_amt IN NUMBER DEFAULT NULL,
59 x_fls_cert_amt IN NUMBER DEFAULT NULL,
60 x_flu_cert_amt IN NUMBER DEFAULT NULL,
61 x_flp_cert_amt IN NUMBER DEFAULT NULL,
62 x_sch_cert_date IN DATE DEFAULT NULL,
63 x_alt_cert_amt IN NUMBER DEFAULT NULL,
64 x_alt_appl_ver_code IN NUMBER DEFAULT NULL,
65 x_duns_school_id IN VARCHAR2 DEFAULT NULL,
66 x_lender_id IN VARCHAR2 DEFAULT NULL,
67 x_fls_approved_amt IN NUMBER DEFAULT NULL,
68 x_flu_approved_amt IN NUMBER DEFAULT NULL,
69 x_flp_approved_amt IN NUMBER DEFAULT NULL,
70 x_alt_approved_amt IN NUMBER DEFAULT NULL,
71 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
72 x_guarantor_id IN VARCHAR2 DEFAULT NULL,
73 x_fed_appl_form_code IN VARCHAR2 DEFAULT NULL,
74 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
75 x_lend_blkt_guarnt_ind IN VARCHAR2 DEFAULT NULL,
76 x_lend_blkt_guarnt_appr_date IN DATE DEFAULT NULL,
77 x_guarnt_adj_ind IN VARCHAR2 DEFAULT NULL,
78 x_guarantee_date IN DATE DEFAULT NULL,
79 x_guarantee_amt IN NUMBER DEFAULT NULL,
80 x_req_serial_loan_code IN VARCHAR2 DEFAULT NULL,
81 x_borw_confirm_ind IN VARCHAR2 DEFAULT NULL,
82 x_b_license_state IN VARCHAR2 DEFAULT NULL,
83 x_b_license_number IN VARCHAR2 DEFAULT NULL,
84 x_b_ref_code IN VARCHAR2 DEFAULT NULL,
85 x_pnote_delivery_code IN VARCHAR2 DEFAULT NULL,
86 x_b_foreign_postal_code IN VARCHAR2 DEFAULT NULL,
87 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
88 x_last_resort_lender IN VARCHAR2 DEFAULT NULL,
89 x_resp_to_orig_code IN VARCHAR2 DEFAULT NULL,
90 x_err_mesg_1 IN VARCHAR2 DEFAULT NULL,
91 x_err_mesg_2 IN VARCHAR2 DEFAULT NULL,
92 x_err_mesg_3 IN VARCHAR2 DEFAULT NULL,
93 x_err_mesg_4 IN VARCHAR2 DEFAULT NULL,
94 x_err_mesg_5 IN VARCHAR2 DEFAULT NULL,
95 x_guarnt_amt_redn_code IN VARCHAR2 DEFAULT NULL,
96 x_tot_outstd_stafford IN NUMBER DEFAULT NULL,
97 x_tot_outstd_plus IN NUMBER DEFAULT NULL,
98 x_b_permt_addr_chg_date IN DATE DEFAULT NULL,
99 x_alt_prog_type_code IN VARCHAR2 DEFAULT NULL,
100 x_alt_borw_tot_debt IN NUMBER DEFAULT NULL,
101 x_act_interest_rate IN NUMBER DEFAULT NULL,
102 x_prc_type_code IN VARCHAR2 DEFAULT NULL,
103 x_service_type_code IN VARCHAR2 DEFAULT NULL,
104 x_rev_notice_of_guarnt IN VARCHAR2 DEFAULT NULL,
105 x_sch_refund_amt IN NUMBER DEFAULT NULL,
106 x_sch_refund_date IN DATE DEFAULT NULL,
107 x_guarnt_status_code IN VARCHAR2 DEFAULT NULL,
108 x_lender_status_code IN VARCHAR2 DEFAULT NULL,
109 x_pnote_status_code IN VARCHAR2 DEFAULT NULL,
110 x_credit_status_code IN VARCHAR2 DEFAULT NULL,
111 x_guarnt_status_date IN DATE DEFAULT NULL,
112 x_lender_status_date IN DATE DEFAULT NULL,
113 x_pnote_status_date IN DATE DEFAULT NULL,
114 x_credit_status_date IN DATE DEFAULT NULL,
115 x_act_serial_loan_code IN VARCHAR2 DEFAULT NULL,
116 x_amt_avail_for_reinst IN NUMBER DEFAULT NULL,
117 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
118 x_uniq_layout_vend_code IN VARCHAR2 DEFAULT NULL,
119 x_uniq_layout_ident_code IN VARCHAR2 DEFAULT NULL,
120 x_resp_record_status IN VARCHAR2 DEFAULT NULL,
121 x_creation_date IN DATE DEFAULT NULL,
122 x_created_by IN NUMBER DEFAULT NULL,
123 x_last_update_date IN DATE DEFAULT NULL,
124 x_last_updated_by IN NUMBER DEFAULT NULL,
125 x_last_update_login IN NUMBER DEFAULT NULL,
126 x_borr_sign_ind IN VARCHAR2 DEFAULT NULL,
127 x_stud_sign_ind IN VARCHAR2 DEFAULT NULL,
128 x_borr_credit_auth_code IN VARCHAR2 DEFAULT NULL,
129 x_mpn_confirm_ind IN VARCHAR2 DEFAULT NULL,
130 x_lender_use_txt IN VARCHAR2 DEFAULT NULL,
131 x_guarantor_use_txt IN VARCHAR2 DEFAULT NULL,
132 x_appl_loan_phase_code IN VARCHAR2 DEFAULT NULL,
133 x_appl_loan_phase_code_chg IN DATE DEFAULT NULL,
134 x_cl_rec_status IN VARCHAR2 DEFAULT NULL,
135 x_cl_rec_status_last_update IN DATE DEFAULT NULL,
136 x_lend_apprv_denied_code IN VARCHAR2 DEFAULT NULL,
137 x_lend_apprv_denied_date IN DATE DEFAULT NULL,
138 x_cl_version_code IN VARCHAR2 DEFAULT NULL,
139 x_school_use_txt IN VARCHAR2 DEFAULT NULL,
140 x_b_alien_reg_num_txt IN VARCHAR2 DEFAULT NULL,
141 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL
142 ) AS
143 /*
144 || Created By : prchandr
145 || Created On : 02-NOV-2000
146 || Purpose : Initialises the Old and New references for the columns of the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152
153 CURSOR cur_old_ref_values IS
154 SELECT *
155 FROM igf_sl_cl_resp_r1_all
156 WHERE rowid = x_rowid;
157
158 BEGIN
159
160 l_rowid := x_rowid;
161
162 -- Code for setting the Old and New Reference Values.
163 -- Populate Old Values.
164 OPEN cur_old_ref_values;
165 FETCH cur_old_ref_values INTO old_references;
166 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
167 CLOSE cur_old_ref_values;
168 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
169 igs_ge_msg_stack.add;
170 app_exception.raise_exception;
171 RETURN;
172 END IF;
173 CLOSE cur_old_ref_values;
174
175 -- Populate New Values.
176 new_references.clrp1_id := x_clrp1_id;
177 new_references.cbth_id := x_cbth_id;
178 new_references.rec_code := x_rec_code;
179 new_references.rec_type_ind := x_rec_type_ind;
180 new_references.b_last_name := x_b_last_name;
181 new_references.b_first_name := x_b_first_name;
182 new_references.b_middle_name := x_b_middle_name;
183 new_references.b_ssn := x_b_ssn;
184 new_references.b_permt_addr1 := x_b_permt_addr1;
185 new_references.b_permt_addr2 := x_b_permt_addr2;
186 new_references.b_permt_city := x_b_permt_city;
187 new_references.b_permt_state := x_b_permt_state;
188 new_references.b_permt_zip := x_b_permt_zip;
189 new_references.b_permt_zip_suffix := x_b_permt_zip_suffix;
190 new_references.b_permt_phone := x_b_permt_phone;
191 new_references.b_date_of_birth := x_b_date_of_birth;
192 new_references.cl_loan_type := x_cl_loan_type;
193 new_references.req_loan_amt := x_req_loan_amt;
194 new_references.defer_req_code := x_defer_req_code;
195 new_references.borw_interest_ind := x_borw_interest_ind;
196 new_references.eft_auth_code := x_eft_auth_code;
197 new_references.b_signature_code := x_b_signature_code;
198 new_references.b_signature_date := x_b_signature_date;
199 new_references.loan_number := x_loan_number;
200 new_references.cl_seq_number := x_cl_seq_number;
201 new_references.b_citizenship_status := x_b_citizenship_status;
202 new_references.b_state_of_legal_res := x_b_state_of_legal_res;
203 new_references.b_legal_res_date := x_b_legal_res_date;
204 new_references.b_default_status := x_b_default_status;
205 new_references.b_outstd_loan_code := x_b_outstd_loan_code;
206 new_references.b_indicator_code := x_b_indicator_code;
207 new_references.s_last_name := x_s_last_name;
208 new_references.s_first_name := x_s_first_name;
209 new_references.s_middle_name := x_s_middle_name;
210 new_references.s_ssn := x_s_ssn;
211 new_references.s_date_of_birth := x_s_date_of_birth;
212 new_references.s_citizenship_status := x_s_citizenship_status;
213 new_references.s_default_code := x_s_default_code;
214 new_references.s_signature_code := x_s_signature_code;
215 new_references.school_id := x_school_id;
216 new_references.loan_per_begin_date := x_loan_per_begin_date;
217 new_references.loan_per_end_date := x_loan_per_end_date;
218 new_references.grade_level_code := x_grade_level_code;
219 new_references.enrollment_code := x_enrollment_code;
220 new_references.anticip_compl_date := x_anticip_compl_date;
221 new_references.coa_amt := x_coa_amt;
222 new_references.efc_amt := x_efc_amt;
223 new_references.est_fa_amt := x_est_fa_amt;
224 new_references.fls_cert_amt := x_fls_cert_amt;
225 new_references.flu_cert_amt := x_flu_cert_amt;
226 new_references.flp_cert_amt := x_flp_cert_amt;
227 new_references.sch_cert_date := x_sch_cert_date;
228 new_references.alt_cert_amt := x_alt_cert_amt;
229 new_references.alt_appl_ver_code := x_alt_appl_ver_code;
230 new_references.duns_school_id := x_duns_school_id;
231 new_references.lender_id := x_lender_id;
232 new_references.fls_approved_amt := x_fls_approved_amt;
233 new_references.flu_approved_amt := x_flu_approved_amt;
234 new_references.flp_approved_amt := x_flp_approved_amt;
235 new_references.alt_approved_amt := x_alt_approved_amt;
236 new_references.duns_lender_id := x_duns_lender_id;
237 new_references.guarantor_id := x_guarantor_id;
238 new_references.fed_appl_form_code := x_fed_appl_form_code;
239 new_references.duns_guarnt_id := x_duns_guarnt_id;
240 new_references.lend_blkt_guarnt_ind := x_lend_blkt_guarnt_ind;
241 new_references.lend_blkt_guarnt_appr_date := x_lend_blkt_guarnt_appr_date;
242 new_references.guarnt_adj_ind := x_guarnt_adj_ind;
243 new_references.guarantee_date := x_guarantee_date;
244 new_references.guarantee_amt := x_guarantee_amt;
245 new_references.req_serial_loan_code := x_req_serial_loan_code;
246 new_references.borw_confirm_ind := x_borw_confirm_ind;
247 new_references.b_license_state := x_b_license_state;
248 new_references.b_license_number := x_b_license_number;
249 new_references.b_ref_code := x_b_ref_code;
250 new_references.pnote_delivery_code := x_pnote_delivery_code;
251 new_references.b_foreign_postal_code := x_b_foreign_postal_code;
252 new_references.lend_non_ed_brc_id := x_lend_non_ed_brc_id;
253 new_references.last_resort_lender := x_last_resort_lender;
254 new_references.resp_to_orig_code := x_resp_to_orig_code;
255 new_references.err_mesg_1 := x_err_mesg_1;
256 new_references.err_mesg_2 := x_err_mesg_2;
257 new_references.err_mesg_3 := x_err_mesg_3;
258 new_references.err_mesg_4 := x_err_mesg_4;
259 new_references.err_mesg_5 := x_err_mesg_5;
260 new_references.guarnt_amt_redn_code := x_guarnt_amt_redn_code;
261 new_references.tot_outstd_stafford := x_tot_outstd_stafford;
262 new_references.tot_outstd_plus := x_tot_outstd_plus;
263 new_references.b_permt_addr_chg_date := x_b_permt_addr_chg_date;
264 new_references.alt_prog_type_code := x_alt_prog_type_code;
265 new_references.alt_borw_tot_debt := x_alt_borw_tot_debt;
266 new_references.act_interest_rate := x_act_interest_rate;
267 new_references.prc_type_code := x_prc_type_code;
268 new_references.service_type_code := x_service_type_code;
269 new_references.rev_notice_of_guarnt := x_rev_notice_of_guarnt;
270 new_references.sch_refund_amt := x_sch_refund_amt;
271 new_references.sch_refund_date := x_sch_refund_date;
272 new_references.guarnt_status_code := x_guarnt_status_code;
273 new_references.lender_status_code := x_lender_status_code;
274 new_references.pnote_status_code := x_pnote_status_code;
275 new_references.credit_status_code := x_credit_status_code;
276 new_references.guarnt_status_date := x_guarnt_status_date;
277 new_references.lender_status_date := x_lender_status_date;
278 new_references.pnote_status_date := x_pnote_status_date;
279 new_references.credit_status_date := x_credit_status_date;
280 new_references.act_serial_loan_code := x_act_serial_loan_code;
281 new_references.amt_avail_for_reinst := x_amt_avail_for_reinst;
282 new_references.sch_non_ed_brc_id := x_sch_non_ed_brc_id;
283 new_references.uniq_layout_vend_code := x_uniq_layout_vend_code;
284 new_references.uniq_layout_ident_code := x_uniq_layout_ident_code;
285 new_references.resp_record_status := x_resp_record_status;
286
287 new_references.mpn_confirm_ind := x_mpn_confirm_ind;
288 new_references.lender_use_txt := x_lender_use_txt;
289 new_references.guarantor_use_txt := x_guarantor_use_txt;
290 new_references.appl_loan_phase_code := x_appl_loan_phase_code;
291 new_references.appl_loan_phase_code_chg := x_appl_loan_phase_code_chg;
292 new_references.cl_rec_status := x_cl_rec_status;
293 new_references.cl_rec_status_last_update := x_cl_rec_status_last_update;
294 new_references.lend_apprv_denied_code := x_lend_apprv_denied_code;
295 new_references.lend_apprv_denied_date := x_lend_apprv_denied_date;
296 new_references.cl_version_code := x_cl_version_code;
297 new_references.school_use_txt := x_school_use_txt;
298
299 IF (p_action = 'UPDATE') THEN
300 new_references.creation_date := old_references.creation_date;
301 new_references.created_by := old_references.created_by;
302 ELSE
303 new_references.creation_date := x_creation_date;
304 new_references.created_by := x_created_by;
305 END IF;
306
307 new_references.last_update_date := x_last_update_date;
308 new_references.last_updated_by := x_last_updated_by;
309 new_references.last_update_login := x_last_update_login;
310 new_references.borr_sign_ind := x_borr_sign_ind;
311 new_references.stud_sign_ind := x_stud_sign_ind;
312 new_references.b_alien_reg_num_txt := x_b_alien_reg_num_txt;
313 new_references.esign_src_typ_cd := x_esign_src_typ_cd;
314 END set_column_values;
315
316
317 PROCEDURE check_parent_existance AS
318 /*
319 || Created By : prchandr
320 || Created On : 02-NOV-2000
321 || Purpose : Checks for the existance of Parent records.
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 */
327 BEGIN
328
329 IF (((old_references.cbth_id = new_references.cbth_id)) OR
330 ((new_references.cbth_id IS NULL))) THEN
331 NULL;
332 ELSIF NOT igf_sl_cl_batch_pkg.get_pk_for_validation (
333 new_references.cbth_id
334 ) THEN
335 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
336 igs_ge_msg_stack.add;
337 app_exception.raise_exception;
338 END IF;
339
340 END check_parent_existance;
341
342
343 PROCEDURE check_child_existance IS
344 /*
345 || Created By : prchandr
346 || Created On : 02-NOV-2000
347 || Purpose : Checks for the existance of Child records.
348 || Known limitations, enhancements or remarks :
349 || Change History :
350 || Who When What
351 || (reverse chronological order - newest change first)
352 */
353 BEGIN
354
355 igf_sl_cl_resp_r4_pkg.get_fk_igf_sl_cl_resp_r1 (
356 old_references.clrp1_id
357 );
358
359 igf_sl_cl_resp_r8_pkg.get_fk_igf_sl_cl_resp_r1 (
360 old_references.clrp1_id
361 );
362
363 igf_sl_cl_resp_r2_dtls_pkg.get_fk_igf_sl_cl_resp_r1 (
364 old_references.clrp1_id
365 );
366
367 igf_sl_cl_resp_r3_dtls_pkg.get_fk_igf_sl_cl_resp_r1 (
368 old_references.clrp1_id
369 );
370
371 END check_child_existance;
372
373
374 FUNCTION get_pk_for_validation (
375 x_clrp1_id IN NUMBER
376 ) RETURN BOOLEAN AS
377 /*
378 || Created By : prchandr
379 || Created On : 02-NOV-2000
380 || Purpose : Validates the Primary Key of the table.
381 || Known limitations, enhancements or remarks :
382 || Change History :
383 || Who When What
384 || (reverse chronological order - newest change first)
385 */
386 CURSOR cur_rowid IS
387 SELECT rowid
388 FROM igf_sl_cl_resp_r1_all
389 WHERE clrp1_id = x_clrp1_id
390 FOR UPDATE NOWAIT;
391
392 lv_rowid cur_rowid%RowType;
393
394 BEGIN
395
396 OPEN cur_rowid;
397 FETCH cur_rowid INTO lv_rowid;
398 IF (cur_rowid%FOUND) THEN
399 CLOSE cur_rowid;
400 RETURN(TRUE);
401 ELSE
402 CLOSE cur_rowid;
403 RETURN(FALSE);
404 END IF;
405
406 END get_pk_for_validation;
407
408
409 PROCEDURE get_fk_igf_sl_cl_batch (
410 x_cbth_id IN NUMBER
411 ) AS
412 /*
413 || Created By : prchandr
414 || Created On : 02-NOV-2000
415 || Purpose : Validates the Foreign Keys for the table.
416 || Known limitations, enhancements or remarks :
417 || Change History :
418 || Who When What
419 || (reverse chronological order - newest change first)
420 */
421 CURSOR cur_rowid IS
422 SELECT rowid
423 FROM igf_sl_cl_resp_r1_all
424 WHERE ((cbth_id = x_cbth_id));
425
426 lv_rowid cur_rowid%RowType;
427
428 BEGIN
429
430 OPEN cur_rowid;
431 FETCH cur_rowid INTO lv_rowid;
432 IF (cur_rowid%FOUND) THEN
433 CLOSE cur_rowid;
434 fnd_message.set_name ('IGF', 'IGF_SL_CLRP1_CBTH_FK');
435 igs_ge_msg_stack.add;
436 app_exception.raise_exception;
437 RETURN;
438 END IF;
439 CLOSE cur_rowid;
440
441 END get_fk_igf_sl_cl_batch;
442
443
444 PROCEDURE before_dml (
445 p_action IN VARCHAR2,
446 x_rowid IN VARCHAR2 DEFAULT NULL,
447 x_clrp1_id IN NUMBER DEFAULT NULL,
448 x_cbth_id IN NUMBER DEFAULT NULL,
449 x_rec_code IN VARCHAR2 DEFAULT NULL,
450 x_rec_type_ind IN VARCHAR2 DEFAULT NULL,
451 x_b_last_name IN VARCHAR2 DEFAULT NULL,
452 x_b_first_name IN VARCHAR2 DEFAULT NULL,
453 x_b_middle_name IN VARCHAR2 DEFAULT NULL,
454 x_b_ssn IN NUMBER DEFAULT NULL,
455 x_b_permt_addr1 IN VARCHAR2 DEFAULT NULL,
456 x_b_permt_addr2 IN VARCHAR2 DEFAULT NULL,
457 x_b_permt_city IN VARCHAR2 DEFAULT NULL,
458 x_b_permt_state IN VARCHAR2 DEFAULT NULL,
459 x_b_permt_zip IN NUMBER DEFAULT NULL,
460 x_b_permt_zip_suffix IN NUMBER DEFAULT NULL,
461 x_b_permt_phone IN VARCHAR2 DEFAULT NULL,
462 x_b_date_of_birth IN DATE DEFAULT NULL,
463 x_cl_loan_type IN VARCHAR2 DEFAULT NULL,
464 x_req_loan_amt IN NUMBER DEFAULT NULL,
465 x_defer_req_code IN VARCHAR2 DEFAULT NULL,
466 x_borw_interest_ind IN VARCHAR2 DEFAULT NULL,
467 x_eft_auth_code IN VARCHAR2 DEFAULT NULL,
468 x_b_signature_code IN VARCHAR2 DEFAULT NULL,
469 x_b_signature_date IN DATE DEFAULT NULL,
470 x_loan_number IN VARCHAR2 DEFAULT NULL,
471 x_cl_seq_number IN NUMBER DEFAULT NULL,
472 x_b_citizenship_status IN VARCHAR2 DEFAULT NULL,
473 x_b_state_of_legal_res IN VARCHAR2 DEFAULT NULL,
474 x_b_legal_res_date IN DATE DEFAULT NULL,
475 x_b_default_status IN VARCHAR2 DEFAULT NULL,
476 x_b_outstd_loan_code IN VARCHAR2 DEFAULT NULL,
477 x_b_indicator_code IN VARCHAR2 DEFAULT NULL,
478 x_s_last_name IN VARCHAR2 DEFAULT NULL,
479 x_s_first_name IN VARCHAR2 DEFAULT NULL,
480 x_s_middle_name IN VARCHAR2 DEFAULT NULL,
481 x_s_ssn IN NUMBER DEFAULT NULL,
482 x_s_date_of_birth IN DATE DEFAULT NULL,
483 x_s_citizenship_status IN VARCHAR2 DEFAULT NULL,
484 x_s_default_code IN VARCHAR2 DEFAULT NULL,
485 x_s_signature_code IN VARCHAR2 DEFAULT NULL,
486 x_school_id IN NUMBER DEFAULT NULL,
487 x_loan_per_begin_date IN DATE DEFAULT NULL,
488 x_loan_per_end_date IN DATE DEFAULT NULL,
489 x_grade_level_code IN VARCHAR2 DEFAULT NULL,
490 x_enrollment_code IN VARCHAR2 DEFAULT NULL,
491 x_anticip_compl_date IN DATE DEFAULT NULL,
492 x_coa_amt IN NUMBER DEFAULT NULL,
493 x_efc_amt IN NUMBER DEFAULT NULL,
494 x_est_fa_amt IN NUMBER DEFAULT NULL,
495 x_fls_cert_amt IN NUMBER DEFAULT NULL,
496 x_flu_cert_amt IN NUMBER DEFAULT NULL,
497 x_flp_cert_amt IN NUMBER DEFAULT NULL,
498 x_sch_cert_date IN DATE DEFAULT NULL,
499 x_alt_cert_amt IN NUMBER DEFAULT NULL,
500 x_alt_appl_ver_code IN NUMBER DEFAULT NULL,
501 x_duns_school_id IN VARCHAR2 DEFAULT NULL,
502 x_lender_id IN VARCHAR2 DEFAULT NULL,
503 x_fls_approved_amt IN NUMBER DEFAULT NULL,
504 x_flu_approved_amt IN NUMBER DEFAULT NULL,
505 x_flp_approved_amt IN NUMBER DEFAULT NULL,
506 x_alt_approved_amt IN NUMBER DEFAULT NULL,
507 x_duns_lender_id IN VARCHAR2 DEFAULT NULL,
508 x_guarantor_id IN VARCHAR2 DEFAULT NULL,
509 x_fed_appl_form_code IN VARCHAR2 DEFAULT NULL,
510 x_duns_guarnt_id IN VARCHAR2 DEFAULT NULL,
511 x_lend_blkt_guarnt_ind IN VARCHAR2 DEFAULT NULL,
512 x_lend_blkt_guarnt_appr_date IN DATE DEFAULT NULL,
513 x_guarnt_adj_ind IN VARCHAR2 DEFAULT NULL,
514 x_guarantee_date IN DATE DEFAULT NULL,
515 x_guarantee_amt IN NUMBER DEFAULT NULL,
516 x_req_serial_loan_code IN VARCHAR2 DEFAULT NULL,
517 x_borw_confirm_ind IN VARCHAR2 DEFAULT NULL,
518 x_b_license_state IN VARCHAR2 DEFAULT NULL,
519 x_b_license_number IN VARCHAR2 DEFAULT NULL,
520 x_b_ref_code IN VARCHAR2 DEFAULT NULL,
521 x_pnote_delivery_code IN VARCHAR2 DEFAULT NULL,
522 x_b_foreign_postal_code IN VARCHAR2 DEFAULT NULL,
523 x_lend_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
524 x_last_resort_lender IN VARCHAR2 DEFAULT NULL,
525 x_resp_to_orig_code IN VARCHAR2 DEFAULT NULL,
526 x_err_mesg_1 IN VARCHAR2 DEFAULT NULL,
527 x_err_mesg_2 IN VARCHAR2 DEFAULT NULL,
528 x_err_mesg_3 IN VARCHAR2 DEFAULT NULL,
529 x_err_mesg_4 IN VARCHAR2 DEFAULT NULL,
530 x_err_mesg_5 IN VARCHAR2 DEFAULT NULL,
531 x_guarnt_amt_redn_code IN VARCHAR2 DEFAULT NULL,
532 x_tot_outstd_stafford IN NUMBER DEFAULT NULL,
533 x_tot_outstd_plus IN NUMBER DEFAULT NULL,
534 x_b_permt_addr_chg_date IN DATE DEFAULT NULL,
535 x_alt_prog_type_code IN VARCHAR2 DEFAULT NULL,
536 x_alt_borw_tot_debt IN NUMBER DEFAULT NULL,
537 x_act_interest_rate IN NUMBER DEFAULT NULL,
538 x_prc_type_code IN VARCHAR2 DEFAULT NULL,
539 x_service_type_code IN VARCHAR2 DEFAULT NULL,
540 x_rev_notice_of_guarnt IN VARCHAR2 DEFAULT NULL,
541 x_sch_refund_amt IN NUMBER DEFAULT NULL,
542 x_sch_refund_date IN DATE DEFAULT NULL,
543 x_guarnt_status_code IN VARCHAR2 DEFAULT NULL,
544 x_lender_status_code IN VARCHAR2 DEFAULT NULL,
545 x_pnote_status_code IN VARCHAR2 DEFAULT NULL,
546 x_credit_status_code IN VARCHAR2 DEFAULT NULL,
547 x_guarnt_status_date IN DATE DEFAULT NULL,
548 x_lender_status_date IN DATE DEFAULT NULL,
549 x_pnote_status_date IN DATE DEFAULT NULL,
550 x_credit_status_date IN DATE DEFAULT NULL,
551 x_act_serial_loan_code IN VARCHAR2 DEFAULT NULL,
552 x_amt_avail_for_reinst IN NUMBER DEFAULT NULL,
553 x_sch_non_ed_brc_id IN VARCHAR2 DEFAULT NULL,
554 x_uniq_layout_vend_code IN VARCHAR2 DEFAULT NULL,
555 x_uniq_layout_ident_code IN VARCHAR2 DEFAULT NULL,
556 x_resp_record_status IN VARCHAR2 DEFAULT NULL,
557 x_creation_date IN DATE DEFAULT NULL,
558 x_created_by IN NUMBER DEFAULT NULL,
559 x_last_update_date IN DATE DEFAULT NULL,
560 x_last_updated_by IN NUMBER DEFAULT NULL,
561 x_last_update_login IN NUMBER DEFAULT NULL,
562 x_borr_sign_ind IN VARCHAR2 DEFAULT NULL,
563 x_stud_sign_ind IN VARCHAR2 DEFAULT NULL,
564 x_borr_credit_auth_code IN VARCHAR2 DEFAULT NULL,
565 x_mpn_confirm_ind IN VARCHAR2 DEFAULT NULL,
566 x_lender_use_txt IN VARCHAR2 DEFAULT NULL,
567 x_guarantor_use_txt IN VARCHAR2 DEFAULT NULL,
568 x_appl_loan_phase_code IN VARCHAR2 DEFAULT NULL,
569 x_appl_loan_phase_code_chg IN DATE DEFAULT NULL,
570 x_cl_rec_status IN VARCHAR2 DEFAULT NULL,
571 x_cl_rec_status_last_update IN DATE DEFAULT NULL,
572 x_lend_apprv_denied_code IN VARCHAR2 DEFAULT NULL,
573 x_lend_apprv_denied_date IN DATE DEFAULT NULL,
574 x_cl_version_code IN VARCHAR2 DEFAULT NULL,
575 x_school_use_txt IN VARCHAR2 DEFAULT NULL,
576 x_b_alien_reg_num_txt IN VARCHAR2 DEFAULT NULL,
577 x_esign_src_typ_cd IN VARCHAR2 DEFAULT NULL
578 ) AS
579 /*
580 || Created By : prchandr
581 || Created On : 02-NOV-2000
582 || Purpose : Initialises the columns, Checks Constraints, Calls the
583 || Trigger Handlers for the table, before any DML operation.
584 || Known limitations, enhancements or remarks :
585 || Change History :
586 || Who When What
587 || (reverse chronological order - newest change first)
588 */
589 BEGIN
590
591 set_column_values (
592 p_action,
593 x_rowid,
594 x_clrp1_id,
595 x_cbth_id,
596 x_rec_code,
597 x_rec_type_ind,
598 x_b_last_name,
599 x_b_first_name,
600 x_b_middle_name,
601 x_b_ssn,
602 x_b_permt_addr1,
603 x_b_permt_addr2,
604 x_b_permt_city,
605 x_b_permt_state,
606 x_b_permt_zip,
607 x_b_permt_zip_suffix,
608 x_b_permt_phone,
609 x_b_date_of_birth,
610 x_cl_loan_type,
611 x_req_loan_amt,
612 x_defer_req_code,
613 x_borw_interest_ind,
614 x_eft_auth_code,
615 x_b_signature_code,
616 x_b_signature_date,
617 x_loan_number,
618 x_cl_seq_number,
619 x_b_citizenship_status,
620 x_b_state_of_legal_res,
621 x_b_legal_res_date,
622 x_b_default_status,
623 x_b_outstd_loan_code,
624 x_b_indicator_code,
625 x_s_last_name,
626 x_s_first_name,
627 x_s_middle_name,
628 x_s_ssn,
629 x_s_date_of_birth,
630 x_s_citizenship_status,
631 x_s_default_code,
632 x_s_signature_code,
633 x_school_id,
634 x_loan_per_begin_date,
635 x_loan_per_end_date,
636 x_grade_level_code,
637 x_enrollment_code,
638 x_anticip_compl_date,
639 x_coa_amt,
640 x_efc_amt,
641 x_est_fa_amt,
642 x_fls_cert_amt,
643 x_flu_cert_amt,
644 x_flp_cert_amt,
645 x_sch_cert_date,
646 x_alt_cert_amt,
647 x_alt_appl_ver_code,
648 x_duns_school_id,
649 x_lender_id,
650 x_fls_approved_amt,
651 x_flu_approved_amt,
652 x_flp_approved_amt,
653 x_alt_approved_amt,
654 x_duns_lender_id,
655 x_guarantor_id,
656 x_fed_appl_form_code,
657 x_duns_guarnt_id,
658 x_lend_blkt_guarnt_ind,
659 x_lend_blkt_guarnt_appr_date,
660 x_guarnt_adj_ind,
661 x_guarantee_date,
662 x_guarantee_amt,
663 x_req_serial_loan_code,
664 x_borw_confirm_ind,
665 x_b_license_state,
666 x_b_license_number,
667 x_b_ref_code,
668 x_pnote_delivery_code,
669 x_b_foreign_postal_code,
670 x_lend_non_ed_brc_id,
671 x_last_resort_lender,
672 x_resp_to_orig_code,
673 x_err_mesg_1,
674 x_err_mesg_2,
675 x_err_mesg_3,
676 x_err_mesg_4,
677 x_err_mesg_5,
678 x_guarnt_amt_redn_code,
679 x_tot_outstd_stafford,
680 x_tot_outstd_plus,
681 x_b_permt_addr_chg_date,
682 x_alt_prog_type_code,
683 x_alt_borw_tot_debt,
684 x_act_interest_rate,
685 x_prc_type_code,
686 x_service_type_code,
687 x_rev_notice_of_guarnt,
688 x_sch_refund_amt,
689 x_sch_refund_date,
690 x_guarnt_status_code,
691 x_lender_status_code,
692 x_pnote_status_code,
693 x_credit_status_code,
694 x_guarnt_status_date,
695 x_lender_status_date,
696 x_pnote_status_date,
697 x_credit_status_date,
698 x_act_serial_loan_code,
699 x_amt_avail_for_reinst,
700 x_sch_non_ed_brc_id,
701 x_uniq_layout_vend_code,
702 x_uniq_layout_ident_code,
703 x_resp_record_status,
704 x_creation_date,
705 x_created_by,
706 x_last_update_date,
707 x_last_updated_by,
708 x_last_update_login,
709 x_borr_sign_ind,
710 x_stud_sign_ind,
711 x_borr_credit_auth_code,
712 x_mpn_confirm_ind,
713 x_lender_use_txt,
714 x_guarantor_use_txt,
715 x_appl_loan_phase_code,
716 x_appl_loan_phase_code_chg,
717 x_cl_rec_status,
718 x_cl_rec_status_last_update,
719 x_lend_apprv_denied_code,
720 x_lend_apprv_denied_date,
721 x_cl_version_code,
722 x_school_use_txt,
723 x_b_alien_reg_num_txt,
724 x_esign_src_typ_cd
725 );
726
727 IF (p_action = 'INSERT') THEN
728 -- Call all the procedures related to Before Insert.
729 IF ( get_pk_for_validation(
730 new_references.clrp1_id
731 )
732 ) THEN
733 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
734 igs_ge_msg_stack.add;
735 app_exception.raise_exception;
736 END IF;
737 check_parent_existance;
738 ELSIF (p_action = 'UPDATE') THEN
739 -- Call all the procedures related to Before Update.
740 check_parent_existance;
741 ELSIF (p_action = 'DELETE') THEN
742 -- Call all the procedures related to Before Delete.
743 check_child_existance;
744 ELSIF (p_action = 'VALIDATE_INSERT') THEN
745 -- Call all the procedures related to Before Insert.
746 IF ( get_pk_for_validation (
747 new_references.clrp1_id
748 )
749 ) THEN
750 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
751 igs_ge_msg_stack.add;
752 app_exception.raise_exception;
753 END IF;
754 ELSIF (p_action = 'VALIDATE_DELETE') THEN
755 check_child_existance;
756 END IF;
757
758 END before_dml;
759
760
761 PROCEDURE insert_row (
762 x_rowid IN OUT NOCOPY VARCHAR2,
763 x_clrp1_id IN OUT NOCOPY NUMBER,
764 x_cbth_id IN NUMBER,
765 x_rec_code IN VARCHAR2,
766 x_rec_type_ind IN VARCHAR2,
767 x_b_last_name IN VARCHAR2,
768 x_b_first_name IN VARCHAR2,
769 x_b_middle_name IN VARCHAR2,
770 x_b_ssn IN NUMBER,
771 x_b_permt_addr1 IN VARCHAR2,
772 x_b_permt_addr2 IN VARCHAR2,
773 x_b_permt_city IN VARCHAR2,
774 x_b_permt_state IN VARCHAR2,
775 x_b_permt_zip IN NUMBER,
776 x_b_permt_zip_suffix IN NUMBER,
777 x_b_permt_phone IN VARCHAR2,
778 x_b_date_of_birth IN DATE,
779 x_cl_loan_type IN VARCHAR2,
780 x_req_loan_amt IN NUMBER,
781 x_defer_req_code IN VARCHAR2,
782 x_borw_interest_ind IN VARCHAR2,
783 x_eft_auth_code IN VARCHAR2,
784 x_b_signature_code IN VARCHAR2,
785 x_b_signature_date IN DATE,
786 x_loan_number IN VARCHAR2,
787 x_cl_seq_number IN NUMBER,
788 x_b_citizenship_status IN VARCHAR2,
789 x_b_state_of_legal_res IN VARCHAR2,
790 x_b_legal_res_date IN DATE,
791 x_b_default_status IN VARCHAR2,
792 x_b_outstd_loan_code IN VARCHAR2,
793 x_b_indicator_code IN VARCHAR2,
794 x_s_last_name IN VARCHAR2,
795 x_s_first_name IN VARCHAR2,
796 x_s_middle_name IN VARCHAR2,
797 x_s_ssn IN NUMBER,
798 x_s_date_of_birth IN DATE,
799 x_s_citizenship_status IN VARCHAR2,
800 x_s_default_code IN VARCHAR2,
801 x_s_signature_code IN VARCHAR2,
802 x_school_id IN NUMBER,
803 x_loan_per_begin_date IN DATE,
804 x_loan_per_end_date IN DATE,
805 x_grade_level_code IN VARCHAR2,
806 x_enrollment_code IN VARCHAR2,
807 x_anticip_compl_date IN DATE,
808 x_coa_amt IN NUMBER,
809 x_efc_amt IN NUMBER,
810 x_est_fa_amt IN NUMBER,
811 x_fls_cert_amt IN NUMBER,
812 x_flu_cert_amt IN NUMBER,
813 x_flp_cert_amt IN NUMBER,
814 x_sch_cert_date IN DATE,
815 x_alt_cert_amt IN NUMBER,
816 x_alt_appl_ver_code IN NUMBER,
817 x_duns_school_id IN VARCHAR2,
818 x_lender_id IN VARCHAR2,
819 x_fls_approved_amt IN NUMBER,
820 x_flu_approved_amt IN NUMBER,
821 x_flp_approved_amt IN NUMBER,
822 x_alt_approved_amt IN NUMBER,
823 x_duns_lender_id IN VARCHAR2,
824 x_guarantor_id IN VARCHAR2,
825 x_fed_appl_form_code IN VARCHAR2,
826 x_duns_guarnt_id IN VARCHAR2,
827 x_lend_blkt_guarnt_ind IN VARCHAR2,
828 x_lend_blkt_guarnt_appr_date IN DATE,
829 x_guarnt_adj_ind IN VARCHAR2,
830 x_guarantee_date IN DATE,
831 x_guarantee_amt IN NUMBER,
832 x_req_serial_loan_code IN VARCHAR2,
833 x_borw_confirm_ind IN VARCHAR2,
834 x_b_license_state IN VARCHAR2,
835 x_b_license_number IN VARCHAR2,
836 x_b_ref_code IN VARCHAR2,
837 x_pnote_delivery_code IN VARCHAR2,
838 x_b_foreign_postal_code IN VARCHAR2,
839 x_lend_non_ed_brc_id IN VARCHAR2,
840 x_last_resort_lender IN VARCHAR2,
841 x_resp_to_orig_code IN VARCHAR2,
842 x_err_mesg_1 IN VARCHAR2,
843 x_err_mesg_2 IN VARCHAR2,
844 x_err_mesg_3 IN VARCHAR2,
845 x_err_mesg_4 IN VARCHAR2,
846 x_err_mesg_5 IN VARCHAR2,
847 x_guarnt_amt_redn_code IN VARCHAR2,
848 x_tot_outstd_stafford IN NUMBER,
849 x_tot_outstd_plus IN NUMBER,
850 x_b_permt_addr_chg_date IN DATE,
851 x_alt_prog_type_code IN VARCHAR2,
852 x_alt_borw_tot_debt IN NUMBER,
853 x_act_interest_rate IN NUMBER,
854 x_prc_type_code IN VARCHAR2,
855 x_service_type_code IN VARCHAR2,
856 x_rev_notice_of_guarnt IN VARCHAR2,
857 x_sch_refund_amt IN NUMBER,
858 x_sch_refund_date IN DATE,
859 x_guarnt_status_code IN VARCHAR2,
860 x_lender_status_code IN VARCHAR2,
861 x_pnote_status_code IN VARCHAR2,
862 x_credit_status_code IN VARCHAR2,
863 x_guarnt_status_date IN DATE,
864 x_lender_status_date IN DATE,
865 x_pnote_status_date IN DATE,
866 x_credit_status_date IN DATE,
867 x_act_serial_loan_code IN VARCHAR2,
868 x_amt_avail_for_reinst IN NUMBER,
869 x_sch_non_ed_brc_id IN VARCHAR2,
870 x_uniq_layout_vend_code IN VARCHAR2,
871 x_uniq_layout_ident_code IN VARCHAR2,
872 x_resp_record_status IN VARCHAR2,
873 x_mode IN VARCHAR2 DEFAULT 'R',
874 x_borr_sign_ind IN VARCHAR2,
875 x_stud_sign_ind IN VARCHAR2,
876 x_borr_credit_auth_code IN VARCHAR2,
877 x_mpn_confirm_ind IN VARCHAR2 ,
878 x_lender_use_txt IN VARCHAR2 ,
879 x_guarantor_use_txt IN VARCHAR2 ,
880 x_appl_loan_phase_code IN VARCHAR2 ,
881 x_appl_loan_phase_code_chg IN DATE ,
882 x_cl_rec_status IN VARCHAR2 ,
883 x_cl_rec_status_last_update IN DATE ,
884 x_lend_apprv_denied_code IN VARCHAR2 ,
885 x_lend_apprv_denied_date IN DATE ,
886 x_cl_version_code IN VARCHAR2 ,
887 x_school_use_txt IN VARCHAR2 ,
888 x_b_alien_reg_num_txt IN VARCHAR2 ,
889 x_esign_src_typ_cd IN VARCHAR2
890 ) AS
891 /*
892 || Created By : prchandr
893 || Created On : 02-NOV-2000
894 || Purpose : Handles the INSERT DML logic for the table.
895 || Known limitations, enhancements or remarks :
896 || Change History :
897 || Who When What
898 || (reverse chronological order - newest change first)
899 */
900 CURSOR c IS
901 SELECT rowid
902 FROM igf_sl_cl_resp_r1_all
903 WHERE clrp1_id = x_clrp1_id;
904
905 x_last_update_date DATE;
906 x_last_updated_by NUMBER;
907 x_last_update_login NUMBER;
908 x_request_id NUMBER;
909 x_program_id NUMBER;
910 x_program_application_id NUMBER;
911 x_program_update_date DATE;
912 l_org_id igf_sl_cl_resp_r1_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
913
914 BEGIN
915
916 x_last_update_date := SYSDATE;
917 IF (x_mode = 'I') THEN
918 x_last_updated_by := 1;
919 x_last_update_login := 0;
920 ELSIF (x_mode = 'R') THEN
921 x_last_updated_by := fnd_global.user_id;
922 IF (x_last_updated_by IS NULL) THEN
923 x_last_updated_by := -1;
924 END IF;
925 x_last_update_login := fnd_global.login_id;
926 IF (x_last_update_login IS NULL) THEN
927 x_last_update_login := -1;
928 END IF;
929 x_request_id := fnd_global.conc_request_id;
930 x_program_id := fnd_global.conc_program_id;
931 x_program_application_id := fnd_global.prog_appl_id;
932
933 IF (x_request_id = -1) THEN
934 x_request_id := NULL;
935 x_program_id := NULL;
936 x_program_application_id := NULL;
937 x_program_update_date := NULL;
938 ELSE
939 x_program_update_date := SYSDATE;
940 END IF;
941 ELSE
942 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
943 igs_ge_msg_stack.add;
944 app_exception.raise_exception;
945 END IF;
946
947 SELECT igf_sl_cl_resp_r1_s.nextval
948 INTO x_clrp1_id
949 FROM dual;
950
951 before_dml(
952 p_action => 'INSERT',
953 x_rowid => x_rowid,
954 x_clrp1_id => x_clrp1_id,
955 x_cbth_id => x_cbth_id,
956 x_rec_code => x_rec_code,
957 x_rec_type_ind => x_rec_type_ind,
958 x_b_last_name => x_b_last_name,
959 x_b_first_name => x_b_first_name,
960 x_b_middle_name => x_b_middle_name,
961 x_b_ssn => x_b_ssn,
962 x_b_permt_addr1 => x_b_permt_addr1,
963 x_b_permt_addr2 => x_b_permt_addr2,
964 x_b_permt_city => x_b_permt_city,
965 x_b_permt_state => x_b_permt_state,
966 x_b_permt_zip => x_b_permt_zip,
967 x_b_permt_zip_suffix => x_b_permt_zip_suffix,
968 x_b_permt_phone => x_b_permt_phone,
969 x_b_date_of_birth => x_b_date_of_birth,
970 x_cl_loan_type => x_cl_loan_type,
971 x_req_loan_amt => x_req_loan_amt,
972 x_defer_req_code => x_defer_req_code,
973 x_borw_interest_ind => x_borw_interest_ind,
974 x_eft_auth_code => x_eft_auth_code,
975 x_b_signature_code => x_b_signature_code,
976 x_b_signature_date => x_b_signature_date,
977 x_loan_number => x_loan_number,
978 x_cl_seq_number => x_cl_seq_number,
979 x_b_citizenship_status => x_b_citizenship_status,
980 x_b_state_of_legal_res => x_b_state_of_legal_res,
981 x_b_legal_res_date => x_b_legal_res_date,
982 x_b_default_status => x_b_default_status,
983 x_b_outstd_loan_code => x_b_outstd_loan_code,
984 x_b_indicator_code => x_b_indicator_code,
985 x_s_last_name => x_s_last_name,
986 x_s_first_name => x_s_first_name,
987 x_s_middle_name => x_s_middle_name,
988 x_s_ssn => x_s_ssn,
989 x_s_date_of_birth => x_s_date_of_birth,
990 x_s_citizenship_status => x_s_citizenship_status,
991 x_s_default_code => x_s_default_code,
992 x_s_signature_code => x_s_signature_code,
993 x_school_id => x_school_id,
994 x_loan_per_begin_date => x_loan_per_begin_date,
995 x_loan_per_end_date => x_loan_per_end_date,
996 x_grade_level_code => x_grade_level_code,
997 x_enrollment_code => x_enrollment_code,
998 x_anticip_compl_date => x_anticip_compl_date,
999 x_coa_amt => x_coa_amt,
1000 x_efc_amt => x_efc_amt,
1001 x_est_fa_amt => x_est_fa_amt,
1002 x_fls_cert_amt => x_fls_cert_amt,
1003 x_flu_cert_amt => x_flu_cert_amt,
1004 x_flp_cert_amt => x_flp_cert_amt,
1005 x_sch_cert_date => x_sch_cert_date,
1006 x_alt_cert_amt => x_alt_cert_amt,
1007 x_alt_appl_ver_code => x_alt_appl_ver_code,
1008 x_duns_school_id => x_duns_school_id,
1009 x_lender_id => x_lender_id,
1010 x_fls_approved_amt => x_fls_approved_amt,
1011 x_flu_approved_amt => x_flu_approved_amt,
1012 x_flp_approved_amt => x_flp_approved_amt,
1013 x_alt_approved_amt => x_alt_approved_amt,
1014 x_duns_lender_id => x_duns_lender_id,
1015 x_guarantor_id => x_guarantor_id,
1016 x_fed_appl_form_code => x_fed_appl_form_code,
1017 x_duns_guarnt_id => x_duns_guarnt_id,
1018 x_lend_blkt_guarnt_ind => x_lend_blkt_guarnt_ind,
1019 x_lend_blkt_guarnt_appr_date => x_lend_blkt_guarnt_appr_date,
1020 x_guarnt_adj_ind => x_guarnt_adj_ind,
1021 x_guarantee_date => x_guarantee_date,
1022 x_guarantee_amt => x_guarantee_amt,
1023 x_req_serial_loan_code => x_req_serial_loan_code,
1024 x_borw_confirm_ind => x_borw_confirm_ind,
1025 x_b_license_state => x_b_license_state,
1026 x_b_license_number => x_b_license_number,
1027 x_b_ref_code => x_b_ref_code,
1028 x_pnote_delivery_code => x_pnote_delivery_code,
1029 x_b_foreign_postal_code => x_b_foreign_postal_code,
1030 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
1031 x_last_resort_lender => x_last_resort_lender,
1032 x_resp_to_orig_code => x_resp_to_orig_code,
1033 x_err_mesg_1 => x_err_mesg_1,
1034 x_err_mesg_2 => x_err_mesg_2,
1035 x_err_mesg_3 => x_err_mesg_3,
1036 x_err_mesg_4 => x_err_mesg_4,
1037 x_err_mesg_5 => x_err_mesg_5,
1038 x_guarnt_amt_redn_code => x_guarnt_amt_redn_code,
1039 x_tot_outstd_stafford => x_tot_outstd_stafford,
1040 x_tot_outstd_plus => x_tot_outstd_plus,
1041 x_b_permt_addr_chg_date => x_b_permt_addr_chg_date,
1042 x_alt_prog_type_code => x_alt_prog_type_code,
1043 x_alt_borw_tot_debt => x_alt_borw_tot_debt,
1044 x_act_interest_rate => x_act_interest_rate,
1045 x_prc_type_code => x_prc_type_code,
1046 x_service_type_code => x_service_type_code,
1047 x_rev_notice_of_guarnt => x_rev_notice_of_guarnt,
1048 x_sch_refund_amt => x_sch_refund_amt,
1049 x_sch_refund_date => x_sch_refund_date,
1050 x_guarnt_status_code => x_guarnt_status_code,
1051 x_lender_status_code => x_lender_status_code,
1052 x_pnote_status_code => x_pnote_status_code,
1053 x_credit_status_code => x_credit_status_code,
1054 x_guarnt_status_date => x_guarnt_status_date,
1055 x_lender_status_date => x_lender_status_date,
1056 x_pnote_status_date => x_pnote_status_date,
1057 x_credit_status_date => x_credit_status_date,
1058 x_act_serial_loan_code => x_act_serial_loan_code,
1059 x_amt_avail_for_reinst => x_amt_avail_for_reinst,
1060 x_sch_non_ed_brc_id => x_sch_non_ed_brc_id,
1061 x_uniq_layout_vend_code => x_uniq_layout_vend_code,
1062 x_uniq_layout_ident_code => x_uniq_layout_ident_code,
1063 x_resp_record_status => x_resp_record_status,
1064 x_creation_date => x_last_update_date,
1065 x_created_by => x_last_updated_by,
1066 x_last_update_date => x_last_update_date,
1067 x_last_updated_by => x_last_updated_by,
1068 x_last_update_login => x_last_update_login,
1069 x_borr_sign_ind => x_borr_sign_ind,
1070 x_stud_sign_ind => x_stud_sign_ind,
1071 x_borr_credit_auth_code => x_borr_credit_auth_code,
1072 x_mpn_confirm_ind => x_mpn_confirm_ind,
1073 x_lender_use_txt => x_lender_use_txt,
1074 x_guarantor_use_txt => x_guarantor_use_txt,
1075 x_appl_loan_phase_code => x_appl_loan_phase_code,
1076 x_appl_loan_phase_code_chg => x_appl_loan_phase_code_chg,
1077 x_cl_rec_status => x_cl_rec_status,
1078 x_cl_rec_status_last_update => x_cl_rec_status_last_update,
1079 x_lend_apprv_denied_code => x_lend_apprv_denied_code,
1080 x_lend_apprv_denied_date => x_lend_apprv_denied_date,
1081 x_cl_version_code => x_cl_version_code,
1082 x_school_use_txt => x_school_use_txt,
1083 x_b_alien_reg_num_txt => x_b_alien_reg_num_txt,
1084 x_esign_src_typ_cd => x_esign_src_typ_cd
1085 );
1086
1087 INSERT INTO igf_sl_cl_resp_r1_all (
1088 clrp1_id,
1089 cbth_id,
1090 rec_code,
1091 rec_type_ind,
1092 b_last_name,
1093 b_first_name,
1094 b_middle_name,
1095 b_ssn,
1096 b_permt_addr1,
1097 b_permt_addr2,
1098 b_permt_city,
1099 b_permt_state,
1100 b_permt_zip,
1101 b_permt_zip_suffix,
1102 b_permt_phone,
1103 b_date_of_birth,
1104 cl_loan_type,
1105 req_loan_amt,
1106 defer_req_code,
1107 borw_interest_ind,
1108 eft_auth_code,
1109 b_signature_code,
1110 b_signature_date,
1111 loan_number,
1112 cl_seq_number,
1113 b_citizenship_status,
1114 b_state_of_legal_res,
1115 b_legal_res_date,
1116 b_default_status,
1117 b_outstd_loan_code,
1118 b_indicator_code,
1119 s_last_name,
1120 s_first_name,
1121 s_middle_name,
1122 s_ssn,
1123 s_date_of_birth,
1124 s_citizenship_status,
1125 s_default_code,
1126 s_signature_code,
1127 school_id,
1128 loan_per_begin_date,
1129 loan_per_end_date,
1130 grade_level_code,
1131 enrollment_code,
1132 anticip_compl_date,
1133 coa_amt,
1134 efc_amt,
1135 est_fa_amt,
1136 fls_cert_amt,
1137 flu_cert_amt,
1138 flp_cert_amt,
1139 sch_cert_date,
1140 alt_cert_amt,
1141 alt_appl_ver_code,
1142 duns_school_id,
1143 lender_id,
1144 fls_approved_amt,
1145 flu_approved_amt,
1146 flp_approved_amt,
1147 alt_approved_amt,
1148 duns_lender_id,
1149 guarantor_id,
1150 fed_appl_form_code,
1151 duns_guarnt_id,
1152 lend_blkt_guarnt_ind,
1153 lend_blkt_guarnt_appr_date,
1154 guarnt_adj_ind,
1155 guarantee_date,
1156 guarantee_amt,
1157 req_serial_loan_code,
1158 borw_confirm_ind,
1159 b_license_state,
1160 b_license_number,
1161 b_ref_code,
1162 pnote_delivery_code,
1163 b_foreign_postal_code,
1164 lend_non_ed_brc_id,
1165 last_resort_lender,
1166 resp_to_orig_code,
1167 err_mesg_1,
1168 err_mesg_2,
1169 err_mesg_3,
1170 err_mesg_4,
1171 err_mesg_5,
1172 guarnt_amt_redn_code,
1173 tot_outstd_stafford,
1174 tot_outstd_plus,
1175 b_permt_addr_chg_date,
1176 alt_prog_type_code,
1177 alt_borw_tot_debt,
1178 act_interest_rate,
1179 prc_type_code,
1180 service_type_code,
1181 rev_notice_of_guarnt,
1182 sch_refund_amt,
1183 sch_refund_date,
1184 guarnt_status_code,
1185 lender_status_code,
1186 pnote_status_code,
1187 credit_status_code,
1188 guarnt_status_date,
1189 lender_status_date,
1190 pnote_status_date,
1191 credit_status_date,
1192 act_serial_loan_code,
1193 amt_avail_for_reinst,
1194 sch_non_ed_brc_id,
1195 uniq_layout_vend_code,
1196 uniq_layout_ident_code,
1197 resp_record_status,
1198 creation_date,
1199 created_by,
1200 last_update_date,
1201 last_updated_by,
1202 last_update_login,
1203 request_id,
1204 program_id,
1205 program_application_id,
1206 program_update_date,
1207 org_id,
1208 borr_sign_ind,
1209 stud_sign_ind,
1210 borr_credit_auth_code,
1211 mpn_confirm_ind,
1212 lender_use_txt ,
1213 guarantor_use_txt,
1214 appl_loan_phase_code,
1215 appl_loan_phase_code_chg,
1216 cl_rec_status ,
1217 cl_rec_status_last_update,
1218 lend_apprv_denied_code ,
1219 lend_apprv_denied_date ,
1220 cl_version_code ,
1221 school_use_txt,
1222 b_alien_reg_num_txt,
1223 esign_src_typ_cd
1224
1225 ) VALUES (
1226 new_references.clrp1_id,
1227 new_references.cbth_id,
1228 new_references.rec_code,
1229 new_references.rec_type_ind,
1230 new_references.b_last_name,
1231 new_references.b_first_name,
1232 new_references.b_middle_name,
1233 new_references.b_ssn,
1234 new_references.b_permt_addr1,
1235 new_references.b_permt_addr2,
1236 new_references.b_permt_city,
1237 new_references.b_permt_state,
1238 new_references.b_permt_zip,
1239 new_references.b_permt_zip_suffix,
1240 new_references.b_permt_phone,
1241 new_references.b_date_of_birth,
1242 new_references.cl_loan_type,
1243 new_references.req_loan_amt,
1244 new_references.defer_req_code,
1245 new_references.borw_interest_ind,
1246 new_references.eft_auth_code,
1247 new_references.b_signature_code,
1248 new_references.b_signature_date,
1249 new_references.loan_number,
1250 new_references.cl_seq_number,
1251 new_references.b_citizenship_status,
1252 new_references.b_state_of_legal_res,
1253 new_references.b_legal_res_date,
1254 new_references.b_default_status,
1255 new_references.b_outstd_loan_code,
1256 new_references.b_indicator_code,
1257 new_references.s_last_name,
1258 new_references.s_first_name,
1259 new_references.s_middle_name,
1260 new_references.s_ssn,
1261 new_references.s_date_of_birth,
1262 new_references.s_citizenship_status,
1263 new_references.s_default_code,
1264 new_references.s_signature_code,
1265 new_references.school_id,
1266 new_references.loan_per_begin_date,
1267 new_references.loan_per_end_date,
1268 new_references.grade_level_code,
1269 new_references.enrollment_code,
1270 new_references.anticip_compl_date,
1271 new_references.coa_amt,
1272 new_references.efc_amt,
1273 new_references.est_fa_amt,
1274 new_references.fls_cert_amt,
1275 new_references.flu_cert_amt,
1276 new_references.flp_cert_amt,
1277 new_references.sch_cert_date,
1278 new_references.alt_cert_amt,
1279 new_references.alt_appl_ver_code,
1280 new_references.duns_school_id,
1281 new_references.lender_id,
1282 new_references.fls_approved_amt,
1283 new_references.flu_approved_amt,
1284 new_references.flp_approved_amt,
1285 new_references.alt_approved_amt,
1286 new_references.duns_lender_id,
1287 new_references.guarantor_id,
1288 new_references.fed_appl_form_code,
1289 new_references.duns_guarnt_id,
1290 new_references.lend_blkt_guarnt_ind,
1291 new_references.lend_blkt_guarnt_appr_date,
1292 new_references.guarnt_adj_ind,
1293 new_references.guarantee_date,
1294 new_references.guarantee_amt,
1295 new_references.req_serial_loan_code,
1296 new_references.borw_confirm_ind,
1297 new_references.b_license_state,
1298 new_references.b_license_number,
1299 new_references.b_ref_code,
1300 new_references.pnote_delivery_code,
1301 new_references.b_foreign_postal_code,
1302 new_references.lend_non_ed_brc_id,
1303 new_references.last_resort_lender,
1304 new_references.resp_to_orig_code,
1305 new_references.err_mesg_1,
1306 new_references.err_mesg_2,
1307 new_references.err_mesg_3,
1308 new_references.err_mesg_4,
1309 new_references.err_mesg_5,
1310 new_references.guarnt_amt_redn_code,
1311 new_references.tot_outstd_stafford,
1312 new_references.tot_outstd_plus,
1313 new_references.b_permt_addr_chg_date,
1314 new_references.alt_prog_type_code,
1315 new_references.alt_borw_tot_debt,
1316 new_references.act_interest_rate,
1317 new_references.prc_type_code,
1318 new_references.service_type_code,
1319 new_references.rev_notice_of_guarnt,
1320 new_references.sch_refund_amt,
1321 new_references.sch_refund_date,
1322 new_references.guarnt_status_code,
1323 new_references.lender_status_code,
1324 new_references.pnote_status_code,
1325 new_references.credit_status_code,
1326 new_references.guarnt_status_date,
1327 new_references.lender_status_date,
1328 new_references.pnote_status_date,
1329 new_references.credit_status_date,
1330 new_references.act_serial_loan_code,
1331 new_references.amt_avail_for_reinst,
1332 new_references.sch_non_ed_brc_id,
1333 new_references.uniq_layout_vend_code,
1334 new_references.uniq_layout_ident_code,
1335 new_references.resp_record_status,
1336 x_last_update_date,
1337 x_last_updated_by,
1338 x_last_update_date,
1339 x_last_updated_by,
1340 x_last_update_login ,
1341 x_request_id,
1342 x_program_id,
1343 x_program_application_id,
1344 x_program_update_date,
1345 l_org_id,
1346 new_references.borr_sign_ind,
1347 new_references.stud_sign_ind,
1348 new_references.borr_credit_auth_code,
1349 new_references.mpn_confirm_ind,
1350 new_references.lender_use_txt ,
1351 new_references.guarantor_use_txt,
1352 new_references.appl_loan_phase_code,
1353 new_references.appl_loan_phase_code_chg,
1354 new_references.cl_rec_status ,
1355 new_references.cl_rec_status_last_update,
1356 new_references.lend_apprv_denied_code ,
1357 new_references.lend_apprv_denied_date ,
1358 new_references.cl_version_code ,
1359 new_references.school_use_txt ,
1360 new_references.b_alien_reg_num_txt ,
1361 new_references.esign_src_typ_cd
1362 );
1363
1364 OPEN c;
1365 FETCH c INTO x_rowid;
1366 IF (c%NOTFOUND) THEN
1367 CLOSE c;
1368 RAISE NO_DATA_FOUND;
1369 END IF;
1370 CLOSE c;
1371
1372 END insert_row;
1373
1374
1375 PROCEDURE lock_row (
1376 x_rowid IN VARCHAR2,
1377 x_clrp1_id IN NUMBER,
1378 x_cbth_id IN NUMBER,
1379 x_rec_code IN VARCHAR2,
1380 x_rec_type_ind IN VARCHAR2,
1381 x_b_last_name IN VARCHAR2,
1382 x_b_first_name IN VARCHAR2,
1383 x_b_middle_name IN VARCHAR2,
1384 x_b_ssn IN NUMBER,
1385 x_b_permt_addr1 IN VARCHAR2,
1386 x_b_permt_addr2 IN VARCHAR2,
1387 x_b_permt_city IN VARCHAR2,
1388 x_b_permt_state IN VARCHAR2,
1389 x_b_permt_zip IN NUMBER,
1390 x_b_permt_zip_suffix IN NUMBER,
1391 x_b_permt_phone IN VARCHAR2,
1392 x_b_date_of_birth IN DATE,
1393 x_cl_loan_type IN VARCHAR2,
1394 x_req_loan_amt IN NUMBER,
1395 x_defer_req_code IN VARCHAR2,
1396 x_borw_interest_ind IN VARCHAR2,
1397 x_eft_auth_code IN VARCHAR2,
1398 x_b_signature_code IN VARCHAR2,
1399 x_b_signature_date IN DATE,
1400 x_loan_number IN VARCHAR2,
1401 x_cl_seq_number IN NUMBER,
1402 x_b_citizenship_status IN VARCHAR2,
1403 x_b_state_of_legal_res IN VARCHAR2,
1404 x_b_legal_res_date IN DATE,
1405 x_b_default_status IN VARCHAR2,
1406 x_b_outstd_loan_code IN VARCHAR2,
1407 x_b_indicator_code IN VARCHAR2,
1408 x_s_last_name IN VARCHAR2,
1409 x_s_first_name IN VARCHAR2,
1410 x_s_middle_name IN VARCHAR2,
1411 x_s_ssn IN NUMBER,
1412 x_s_date_of_birth IN DATE,
1413 x_s_citizenship_status IN VARCHAR2,
1414 x_s_default_code IN VARCHAR2,
1415 x_s_signature_code IN VARCHAR2,
1416 x_school_id IN NUMBER,
1417 x_loan_per_begin_date IN DATE,
1418 x_loan_per_end_date IN DATE,
1419 x_grade_level_code IN VARCHAR2,
1420 x_enrollment_code IN VARCHAR2,
1421 x_anticip_compl_date IN DATE,
1422 x_coa_amt IN NUMBER,
1423 x_efc_amt IN NUMBER,
1424 x_est_fa_amt IN NUMBER,
1425 x_fls_cert_amt IN NUMBER,
1426 x_flu_cert_amt IN NUMBER,
1427 x_flp_cert_amt IN NUMBER,
1428 x_sch_cert_date IN DATE,
1429 x_alt_cert_amt IN NUMBER,
1430 x_alt_appl_ver_code IN NUMBER,
1431 x_duns_school_id IN VARCHAR2,
1432 x_lender_id IN VARCHAR2,
1433 x_fls_approved_amt IN NUMBER,
1434 x_flu_approved_amt IN NUMBER,
1435 x_flp_approved_amt IN NUMBER,
1436 x_alt_approved_amt IN NUMBER,
1437 x_duns_lender_id IN VARCHAR2,
1438 x_guarantor_id IN VARCHAR2,
1439 x_fed_appl_form_code IN VARCHAR2,
1440 x_duns_guarnt_id IN VARCHAR2,
1441 x_lend_blkt_guarnt_ind IN VARCHAR2,
1442 x_lend_blkt_guarnt_appr_date IN DATE,
1443 x_guarnt_adj_ind IN VARCHAR2,
1444 x_guarantee_date IN DATE,
1445 x_guarantee_amt IN NUMBER,
1446 x_req_serial_loan_code IN VARCHAR2,
1447 x_borw_confirm_ind IN VARCHAR2,
1448 x_b_license_state IN VARCHAR2,
1449 x_b_license_number IN VARCHAR2,
1450 x_b_ref_code IN VARCHAR2,
1451 x_pnote_delivery_code IN VARCHAR2,
1452 x_b_foreign_postal_code IN VARCHAR2,
1453 x_lend_non_ed_brc_id IN VARCHAR2,
1454 x_last_resort_lender IN VARCHAR2,
1455 x_resp_to_orig_code IN VARCHAR2,
1456 x_err_mesg_1 IN VARCHAR2,
1457 x_err_mesg_2 IN VARCHAR2,
1458 x_err_mesg_3 IN VARCHAR2,
1459 x_err_mesg_4 IN VARCHAR2,
1460 x_err_mesg_5 IN VARCHAR2,
1461 x_guarnt_amt_redn_code IN VARCHAR2,
1462 x_tot_outstd_stafford IN NUMBER,
1463 x_tot_outstd_plus IN NUMBER,
1464 x_b_permt_addr_chg_date IN DATE,
1465 x_alt_prog_type_code IN VARCHAR2,
1466 x_alt_borw_tot_debt IN NUMBER,
1467 x_act_interest_rate IN NUMBER,
1468 x_prc_type_code IN VARCHAR2,
1469 x_service_type_code IN VARCHAR2,
1470 x_rev_notice_of_guarnt IN VARCHAR2,
1471 x_sch_refund_amt IN NUMBER,
1472 x_sch_refund_date IN DATE,
1473 x_guarnt_status_code IN VARCHAR2,
1474 x_lender_status_code IN VARCHAR2,
1475 x_pnote_status_code IN VARCHAR2,
1476 x_credit_status_code IN VARCHAR2,
1477 x_guarnt_status_date IN DATE,
1478 x_lender_status_date IN DATE,
1479 x_pnote_status_date IN DATE,
1480 x_credit_status_date IN DATE,
1481 x_act_serial_loan_code IN VARCHAR2,
1482 x_amt_avail_for_reinst IN NUMBER,
1483 x_sch_non_ed_brc_id IN VARCHAR2,
1484 x_uniq_layout_vend_code IN VARCHAR2,
1485 x_uniq_layout_ident_code IN VARCHAR2,
1486 x_resp_record_status IN VARCHAR2,
1487 x_borr_sign_ind IN VARCHAR2,
1488 x_stud_sign_ind IN VARCHAR2,
1489 x_borr_credit_auth_code IN VARCHAR2,
1490 x_mpn_confirm_ind IN VARCHAR2 ,
1491 x_lender_use_txt IN VARCHAR2 ,
1492 x_guarantor_use_txt IN VARCHAR2 ,
1493 x_appl_loan_phase_code IN VARCHAR2 ,
1494 x_appl_loan_phase_code_chg IN DATE ,
1495 x_cl_rec_status IN VARCHAR2 ,
1496 x_cl_rec_status_last_update IN DATE ,
1497 x_lend_apprv_denied_code IN VARCHAR2 ,
1498 x_lend_apprv_denied_date IN DATE ,
1499 x_cl_version_code IN VARCHAR2 ,
1500 x_school_use_txt IN VARCHAR2 ,
1501 x_b_alien_reg_num_txt IN VARCHAR2 ,
1502 x_esign_src_typ_cd IN VARCHAR2
1503 ) AS
1504 /*
1505 || Created By : prchandr
1506 || Created On : 02-NOV-2000
1507 || Purpose : Handles the LOCK mechanism for the table.
1508 || Known limitations, enhancements or remarks :
1509 || Change History :
1510 || Who When What
1511 || (reverse chronological order - newest change first)
1512 */
1513 CURSOR c1 IS
1514 SELECT
1515 cbth_id,
1516 rec_code,
1517 rec_type_ind,
1518 b_last_name,
1519 b_first_name,
1520 b_middle_name,
1521 b_ssn,
1522 b_permt_addr1,
1523 b_permt_addr2,
1524 b_permt_city,
1525 b_permt_state,
1526 b_permt_zip,
1527 b_permt_zip_suffix,
1528 b_permt_phone,
1529 b_date_of_birth,
1530 cl_loan_type,
1531 req_loan_amt,
1532 defer_req_code,
1533 borw_interest_ind,
1534 eft_auth_code,
1535 b_signature_code,
1536 b_signature_date,
1537 loan_number,
1538 cl_seq_number,
1539 b_citizenship_status,
1540 b_state_of_legal_res,
1541 b_legal_res_date,
1542 b_default_status,
1543 b_outstd_loan_code,
1544 b_indicator_code,
1545 s_last_name,
1546 s_first_name,
1547 s_middle_name,
1548 s_ssn,
1549 s_date_of_birth,
1550 s_citizenship_status,
1551 s_default_code,
1552 s_signature_code,
1553 school_id,
1554 loan_per_begin_date,
1555 loan_per_end_date,
1556 grade_level_code,
1557 enrollment_code,
1558 anticip_compl_date,
1559 coa_amt,
1560 efc_amt,
1561 est_fa_amt,
1562 fls_cert_amt,
1563 flu_cert_amt,
1564 flp_cert_amt,
1565 sch_cert_date,
1566 alt_cert_amt,
1567 alt_appl_ver_code,
1568 duns_school_id,
1569 lender_id,
1570 fls_approved_amt,
1571 flu_approved_amt,
1572 flp_approved_amt,
1573 alt_approved_amt,
1574 duns_lender_id,
1575 guarantor_id,
1576 fed_appl_form_code,
1577 duns_guarnt_id,
1578 lend_blkt_guarnt_ind,
1579 lend_blkt_guarnt_appr_date,
1580 guarnt_adj_ind,
1581 guarantee_date,
1582 guarantee_amt,
1583 req_serial_loan_code,
1584 borw_confirm_ind,
1585 b_license_state,
1586 b_license_number,
1587 b_ref_code,
1588 pnote_delivery_code,
1589 b_foreign_postal_code,
1590 lend_non_ed_brc_id,
1591 last_resort_lender,
1592 resp_to_orig_code,
1593 err_mesg_1,
1594 err_mesg_2,
1595 err_mesg_3,
1596 err_mesg_4,
1597 err_mesg_5,
1598 guarnt_amt_redn_code,
1599 tot_outstd_stafford,
1600 tot_outstd_plus,
1601 b_permt_addr_chg_date,
1602 alt_prog_type_code,
1603 alt_borw_tot_debt,
1604 act_interest_rate,
1605 prc_type_code,
1606 service_type_code,
1607 rev_notice_of_guarnt,
1608 sch_refund_amt,
1609 sch_refund_date,
1610 guarnt_status_code,
1611 lender_status_code,
1612 pnote_status_code,
1613 credit_status_code,
1614 guarnt_status_date,
1615 lender_status_date,
1616 pnote_status_date,
1617 credit_status_date,
1618 act_serial_loan_code,
1619 amt_avail_for_reinst,
1620 sch_non_ed_brc_id,
1621 uniq_layout_vend_code,
1622 uniq_layout_ident_code,
1623 resp_record_status,
1624 org_id,
1625 borr_sign_ind,
1626 stud_sign_ind,
1627 borr_credit_auth_code,
1628 mpn_confirm_ind,
1629 lender_use_txt,
1630 guarantor_use_txt,
1631 appl_loan_phase_code,
1632 appl_loan_phase_code_chg,
1633 cl_rec_status,
1634 cl_rec_status_last_update,
1635 lend_apprv_denied_code ,
1636 lend_apprv_denied_date ,
1637 cl_version_code,
1638 school_use_txt,
1639 b_alien_reg_num_txt,
1640 esign_src_typ_cd
1641
1642 FROM igf_sl_cl_resp_r1_all
1643 WHERE rowid = x_rowid
1644 FOR UPDATE NOWAIT;
1645
1646 tlinfo c1%ROWTYPE;
1647
1648 BEGIN
1649
1650 OPEN c1;
1651 FETCH c1 INTO tlinfo;
1652 IF (c1%notfound) THEN
1653 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1654 igs_ge_msg_stack.add;
1655 CLOSE c1;
1656 app_exception.raise_exception;
1657 RETURN;
1658 END IF;
1659 CLOSE c1;
1660
1661 IF (
1662 (tlinfo.cbth_id = x_cbth_id)
1663 AND (tlinfo.rec_code = x_rec_code)
1664 AND (tlinfo.rec_type_ind = x_rec_type_ind)
1665 AND ((tlinfo.b_last_name = x_b_last_name) OR ((tlinfo.b_last_name IS NULL) AND (X_b_last_name IS NULL)))
1666 AND ((tlinfo.b_first_name = x_b_first_name) OR ((tlinfo.b_first_name IS NULL) AND (X_b_first_name IS NULL)))
1667 AND ((tlinfo.b_middle_name = x_b_middle_name) OR ((tlinfo.b_middle_name IS NULL) AND (X_b_middle_name IS NULL)))
1668 AND ((tlinfo.b_ssn = x_b_ssn) OR ((tlinfo.b_ssn IS NULL) AND (X_b_ssn IS NULL)))
1669 AND ((tlinfo.b_permt_addr1 = x_b_permt_addr1) OR ((tlinfo.b_permt_addr1 IS NULL) AND (X_b_permt_addr1 IS NULL)))
1670 AND ((tlinfo.b_permt_addr2 = x_b_permt_addr2) OR ((tlinfo.b_permt_addr2 IS NULL) AND (X_b_permt_addr2 IS NULL)))
1671 AND ((tlinfo.b_permt_city = x_b_permt_city) OR ((tlinfo.b_permt_city IS NULL) AND (X_b_permt_city IS NULL)))
1672 AND ((tlinfo.b_permt_state = x_b_permt_state) OR ((tlinfo.b_permt_state IS NULL) AND (X_b_permt_state IS NULL)))
1673 AND ((tlinfo.b_permt_zip = x_b_permt_zip) OR ((tlinfo.b_permt_zip IS NULL) AND (X_b_permt_zip IS NULL)))
1674 AND ((tlinfo.b_permt_zip_suffix = x_b_permt_zip_suffix) OR ((tlinfo.b_permt_zip_suffix IS NULL) AND (X_b_permt_zip_suffix IS NULL)))
1675 AND ((tlinfo.b_permt_phone = x_b_permt_phone) OR ((tlinfo.b_permt_phone IS NULL) AND (X_b_permt_phone IS NULL)))
1676 AND ((tlinfo.b_date_of_birth = x_b_date_of_birth) OR ((tlinfo.b_date_of_birth IS NULL) AND (X_b_date_of_birth IS NULL)))
1677 AND ((tlinfo.cl_loan_type = x_cl_loan_type) OR ((tlinfo.cl_loan_type IS NULL) AND (X_cl_loan_type IS NULL)))
1678 AND ((tlinfo.req_loan_amt = x_req_loan_amt) OR ((tlinfo.req_loan_amt IS NULL) AND (X_req_loan_amt IS NULL)))
1679 AND ((tlinfo.defer_req_code = x_defer_req_code) OR ((tlinfo.defer_req_code IS NULL) AND (X_defer_req_code IS NULL)))
1680 AND ((tlinfo.borw_interest_ind = x_borw_interest_ind) OR ((tlinfo.borw_interest_ind IS NULL) AND (X_borw_interest_ind IS NULL)))
1681 AND ((tlinfo.eft_auth_code = x_eft_auth_code) OR ((tlinfo.eft_auth_code IS NULL) AND (X_eft_auth_code IS NULL)))
1682 AND ((tlinfo.b_signature_code = x_b_signature_code) OR ((tlinfo.b_signature_code IS NULL) AND (X_b_signature_code IS NULL)))
1683 AND ((tlinfo.b_signature_date = x_b_signature_date) OR ((tlinfo.b_signature_date IS NULL) AND (X_b_signature_date IS NULL)))
1684 AND ((tlinfo.loan_number = x_loan_number) OR ((tlinfo.loan_number IS NULL) AND (X_loan_number IS NULL)))
1685 AND ((tlinfo.cl_seq_number = x_cl_seq_number) OR ((tlinfo.cl_seq_number IS NULL) AND (X_cl_seq_number IS NULL)))
1686 AND ((tlinfo.b_citizenship_status = x_b_citizenship_status) OR ((tlinfo.b_citizenship_status IS NULL) AND (X_b_citizenship_status IS NULL)))
1687 AND ((tlinfo.b_state_of_legal_res = x_b_state_of_legal_res) OR ((tlinfo.b_state_of_legal_res IS NULL) AND (X_b_state_of_legal_res IS NULL)))
1688 AND ((tlinfo.b_legal_res_date = x_b_legal_res_date) OR ((tlinfo.b_legal_res_date IS NULL) AND (X_b_legal_res_date IS NULL)))
1689 AND ((tlinfo.b_default_status = x_b_default_status) OR ((tlinfo.b_default_status IS NULL) AND (X_b_default_status IS NULL)))
1690 AND ((tlinfo.b_outstd_loan_code = x_b_outstd_loan_code) OR ((tlinfo.b_outstd_loan_code IS NULL) AND (X_b_outstd_loan_code IS NULL)))
1691 AND ((tlinfo.b_indicator_code = x_b_indicator_code) OR ((tlinfo.b_indicator_code IS NULL) AND (X_b_indicator_code IS NULL)))
1692 AND ((tlinfo.s_last_name = x_s_last_name) OR ((tlinfo.s_last_name IS NULL) AND (X_s_last_name IS NULL)))
1693 AND ((tlinfo.s_first_name = x_s_first_name) OR ((tlinfo.s_first_name IS NULL) AND (X_s_first_name IS NULL)))
1694 AND ((tlinfo.s_middle_name = x_s_middle_name) OR ((tlinfo.s_middle_name IS NULL) AND (X_s_middle_name IS NULL)))
1695 AND ((tlinfo.s_ssn = x_s_ssn) OR ((tlinfo.s_ssn IS NULL) AND (X_s_ssn IS NULL)))
1696 AND ((tlinfo.s_date_of_birth = x_s_date_of_birth) OR ((tlinfo.s_date_of_birth IS NULL) AND (X_s_date_of_birth IS NULL)))
1697 AND ((tlinfo.s_citizenship_status = x_s_citizenship_status) OR ((tlinfo.s_citizenship_status IS NULL) AND (X_s_citizenship_status IS NULL)))
1698 AND ((tlinfo.s_default_code = x_s_default_code) OR ((tlinfo.s_default_code IS NULL) AND (X_s_default_code IS NULL)))
1699 AND ((tlinfo.s_signature_code = x_s_signature_code) OR ((tlinfo.s_signature_code IS NULL) AND (X_s_signature_code IS NULL)))
1700 AND ((tlinfo.school_id = x_school_id) OR ((tlinfo.school_id IS NULL) AND (X_school_id IS NULL)))
1701 AND ((tlinfo.loan_per_begin_date = x_loan_per_begin_date) OR ((tlinfo.loan_per_begin_date IS NULL) AND (X_loan_per_begin_date IS NULL)))
1702 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)))
1703 AND ((tlinfo.grade_level_code = x_grade_level_code) OR ((tlinfo.grade_level_code IS NULL) AND (X_grade_level_code IS NULL)))
1704 AND ((tlinfo.enrollment_code = x_enrollment_code) OR ((tlinfo.enrollment_code IS NULL) AND (X_enrollment_code IS NULL)))
1705 AND ((tlinfo.anticip_compl_date = x_anticip_compl_date) OR ((tlinfo.anticip_compl_date IS NULL) AND (X_anticip_compl_date IS NULL)))
1706 AND ((tlinfo.coa_amt = x_coa_amt) OR ((tlinfo.coa_amt IS NULL) AND (X_coa_amt IS NULL)))
1707 AND ((tlinfo.efc_amt = x_efc_amt) OR ((tlinfo.efc_amt IS NULL) AND (X_efc_amt IS NULL)))
1708 AND ((tlinfo.est_fa_amt = x_est_fa_amt) OR ((tlinfo.est_fa_amt IS NULL) AND (X_est_fa_amt IS NULL)))
1709 AND ((tlinfo.fls_cert_amt = x_fls_cert_amt) OR ((tlinfo.fls_cert_amt IS NULL) AND (X_fls_cert_amt IS NULL)))
1710 AND ((tlinfo.flu_cert_amt = x_flu_cert_amt) OR ((tlinfo.flu_cert_amt IS NULL) AND (X_flu_cert_amt IS NULL)))
1711 AND ((tlinfo.flp_cert_amt = x_flp_cert_amt) OR ((tlinfo.flp_cert_amt IS NULL) AND (X_flp_cert_amt IS NULL)))
1712 AND ((tlinfo.sch_cert_date = x_sch_cert_date) OR ((tlinfo.sch_cert_date IS NULL) AND (X_sch_cert_date IS NULL)))
1713 AND ((tlinfo.alt_cert_amt = x_alt_cert_amt) OR ((tlinfo.alt_cert_amt IS NULL) AND (X_alt_cert_amt IS NULL)))
1714 AND ((tlinfo.alt_appl_ver_code = x_alt_appl_ver_code) OR ((tlinfo.alt_appl_ver_code IS NULL) AND (X_alt_appl_ver_code IS NULL)))
1715 AND ((tlinfo.duns_school_id = x_duns_school_id) OR ((tlinfo.duns_school_id IS NULL) AND (X_duns_school_id IS NULL)))
1716 AND ((tlinfo.lender_id = x_lender_id) OR ((tlinfo.lender_id IS NULL) AND (X_lender_id IS NULL)))
1717 AND ((tlinfo.fls_approved_amt = x_fls_approved_amt) OR ((tlinfo.fls_approved_amt IS NULL) AND (X_fls_approved_amt IS NULL)))
1718 AND ((tlinfo.flu_approved_amt = x_flu_approved_amt) OR ((tlinfo.flu_approved_amt IS NULL) AND (X_flu_approved_amt IS NULL)))
1719 AND ((tlinfo.flp_approved_amt = x_flp_approved_amt) OR ((tlinfo.flp_approved_amt IS NULL) AND (X_flp_approved_amt IS NULL)))
1720 AND ((tlinfo.alt_approved_amt = x_alt_approved_amt) OR ((tlinfo.alt_approved_amt IS NULL) AND (X_alt_approved_amt IS NULL)))
1721 AND ((tlinfo.duns_lender_id = x_duns_lender_id) OR ((tlinfo.duns_lender_id IS NULL) AND (X_duns_lender_id IS NULL)))
1722 AND ((tlinfo.guarantor_id = x_guarantor_id) OR ((tlinfo.guarantor_id IS NULL) AND (X_guarantor_id IS NULL)))
1723 AND ((tlinfo.fed_appl_form_code = x_fed_appl_form_code) OR ((tlinfo.fed_appl_form_code IS NULL) AND (X_fed_appl_form_code IS NULL)))
1724 AND ((tlinfo.duns_guarnt_id = x_duns_guarnt_id) OR ((tlinfo.duns_guarnt_id IS NULL) AND (X_duns_guarnt_id IS NULL)))
1725 AND ((tlinfo.lend_blkt_guarnt_ind = x_lend_blkt_guarnt_ind) OR ((tlinfo.lend_blkt_guarnt_ind IS NULL) AND (X_lend_blkt_guarnt_ind IS NULL)))
1726 AND ((tlinfo.lend_blkt_guarnt_appr_date = x_lend_blkt_guarnt_appr_date) OR ((tlinfo.lend_blkt_guarnt_appr_date IS NULL) AND (X_lend_blkt_guarnt_appr_date IS NULL)))
1727 AND ((tlinfo.guarnt_adj_ind = x_guarnt_adj_ind) OR ((tlinfo.guarnt_adj_ind IS NULL) AND (X_guarnt_adj_ind IS NULL)))
1728 AND ((tlinfo.guarantee_date = x_guarantee_date) OR ((tlinfo.guarantee_date IS NULL) AND (X_guarantee_date IS NULL)))
1729 AND ((tlinfo.guarantee_amt = x_guarantee_amt) OR ((tlinfo.guarantee_amt IS NULL) AND (X_guarantee_amt IS NULL)))
1730 AND ((tlinfo.req_serial_loan_code = x_req_serial_loan_code) OR ((tlinfo.req_serial_loan_code IS NULL) AND (X_req_serial_loan_code IS NULL)))
1731 AND ((tlinfo.borw_confirm_ind = x_borw_confirm_ind) OR ((tlinfo.borw_confirm_ind IS NULL) AND (X_borw_confirm_ind IS NULL)))
1732 AND ((tlinfo.b_license_state = x_b_license_state) OR ((tlinfo.b_license_state IS NULL) AND (X_b_license_state IS NULL)))
1733 AND ((tlinfo.b_license_number = x_b_license_number) OR ((tlinfo.b_license_number IS NULL) AND (X_b_license_number IS NULL)))
1734 AND ((tlinfo.b_ref_code = x_b_ref_code) OR ((tlinfo.b_ref_code IS NULL) AND (X_b_ref_code IS NULL)))
1735 AND ((tlinfo.pnote_delivery_code = x_pnote_delivery_code) OR ((tlinfo.pnote_delivery_code IS NULL) AND (X_pnote_delivery_code IS NULL)))
1736 AND ((tlinfo.b_foreign_postal_code = x_b_foreign_postal_code) OR ((tlinfo.b_foreign_postal_code IS NULL) AND (X_b_foreign_postal_code IS NULL)))
1737 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)))
1738 AND ((tlinfo.last_resort_lender = x_last_resort_lender) OR ((tlinfo.last_resort_lender IS NULL) AND (X_last_resort_lender IS NULL)))
1739 AND ((tlinfo.resp_to_orig_code = x_resp_to_orig_code) OR ((tlinfo.resp_to_orig_code IS NULL) AND (X_resp_to_orig_code IS NULL)))
1740 AND ((tlinfo.err_mesg_1 = x_err_mesg_1) OR ((tlinfo.err_mesg_1 IS NULL) AND (X_err_mesg_1 IS NULL)))
1741 AND ((tlinfo.err_mesg_2 = x_err_mesg_2) OR ((tlinfo.err_mesg_2 IS NULL) AND (X_err_mesg_2 IS NULL)))
1742 AND ((tlinfo.err_mesg_3 = x_err_mesg_3) OR ((tlinfo.err_mesg_3 IS NULL) AND (X_err_mesg_3 IS NULL)))
1743 AND ((tlinfo.err_mesg_4 = x_err_mesg_4) OR ((tlinfo.err_mesg_4 IS NULL) AND (X_err_mesg_4 IS NULL)))
1744 AND ((tlinfo.err_mesg_5 = x_err_mesg_5) OR ((tlinfo.err_mesg_5 IS NULL) AND (X_err_mesg_5 IS NULL)))
1745 AND ((tlinfo.guarnt_amt_redn_code = x_guarnt_amt_redn_code) OR ((tlinfo.guarnt_amt_redn_code IS NULL) AND (X_guarnt_amt_redn_code IS NULL)))
1746 AND ((tlinfo.tot_outstd_stafford = x_tot_outstd_stafford) OR ((tlinfo.tot_outstd_stafford IS NULL) AND (X_tot_outstd_stafford IS NULL)))
1747 AND ((tlinfo.tot_outstd_plus = x_tot_outstd_plus) OR ((tlinfo.tot_outstd_plus IS NULL) AND (X_tot_outstd_plus IS NULL)))
1748 AND ((tlinfo.b_permt_addr_chg_date = x_b_permt_addr_chg_date) OR ((tlinfo.b_permt_addr_chg_date IS NULL) AND (X_b_permt_addr_chg_date IS NULL)))
1749 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)))
1750 AND ((tlinfo.alt_borw_tot_debt = x_alt_borw_tot_debt) OR ((tlinfo.alt_borw_tot_debt IS NULL) AND (X_alt_borw_tot_debt IS NULL)))
1751 AND ((tlinfo.act_interest_rate = x_act_interest_rate) OR ((tlinfo.act_interest_rate IS NULL) AND (X_act_interest_rate IS NULL)))
1752 AND (tlinfo.prc_type_code = x_prc_type_code)
1753 AND ((tlinfo.service_type_code = x_service_type_code) OR ((tlinfo.service_type_code IS NULL) AND (X_service_type_code IS NULL)))
1754 AND ((tlinfo.rev_notice_of_guarnt = x_rev_notice_of_guarnt) OR ((tlinfo.rev_notice_of_guarnt IS NULL) AND (X_rev_notice_of_guarnt IS NULL)))
1755 AND ((tlinfo.sch_refund_amt = x_sch_refund_amt) OR ((tlinfo.sch_refund_amt IS NULL) AND (X_sch_refund_amt IS NULL)))
1756 AND ((tlinfo.sch_refund_date = x_sch_refund_date) OR ((tlinfo.sch_refund_date IS NULL) AND (X_sch_refund_date IS NULL)))
1757 AND ((tlinfo.guarnt_status_code = x_guarnt_status_code) OR ((tlinfo.guarnt_status_code IS NULL) AND (X_guarnt_status_code IS NULL)))
1758 AND ((tlinfo.lender_status_code = x_lender_status_code) OR ((tlinfo.lender_status_code IS NULL) AND (X_lender_status_code IS NULL)))
1759 AND ((tlinfo.pnote_status_code = x_pnote_status_code) OR ((tlinfo.pnote_status_code IS NULL) AND (X_pnote_status_code IS NULL)))
1760 AND ((tlinfo.credit_status_code = x_credit_status_code) OR ((tlinfo.credit_status_code IS NULL) AND (X_credit_status_code IS NULL)))
1761 AND ((tlinfo.guarnt_status_date = x_guarnt_status_date) OR ((tlinfo.guarnt_status_date IS NULL) AND (X_guarnt_status_date IS NULL)))
1762 AND ((tlinfo.lender_status_date = x_lender_status_date) OR ((tlinfo.lender_status_date IS NULL) AND (X_lender_status_date IS NULL)))
1763 AND ((tlinfo.pnote_status_date = x_pnote_status_date) OR ((tlinfo.pnote_status_date IS NULL) AND (X_pnote_status_date IS NULL)))
1764 AND ((tlinfo.credit_status_date = x_credit_status_date) OR ((tlinfo.credit_status_date IS NULL) AND (X_credit_status_date IS NULL)))
1765 AND ((tlinfo.act_serial_loan_code = x_act_serial_loan_code) OR ((tlinfo.act_serial_loan_code IS NULL) AND (X_act_serial_loan_code IS NULL)))
1766 AND ((tlinfo.amt_avail_for_reinst = x_amt_avail_for_reinst) OR ((tlinfo.amt_avail_for_reinst IS NULL) AND (X_amt_avail_for_reinst IS NULL)))
1767 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)))
1768 AND ((tlinfo.uniq_layout_vend_code = x_uniq_layout_vend_code) OR ((tlinfo.uniq_layout_vend_code IS NULL) AND (X_uniq_layout_vend_code IS NULL)))
1769 AND ((tlinfo.uniq_layout_ident_code = x_uniq_layout_ident_code) OR ((tlinfo.uniq_layout_ident_code IS NULL) AND (X_uniq_layout_ident_code IS NULL)))
1770 AND ((tlinfo.resp_record_status = x_resp_record_status) OR ((tlinfo.resp_record_status IS NULL) AND (X_resp_record_status IS NULL)))
1771 AND ((tlinfo.borr_sign_ind = x_borr_sign_ind) OR ((tlinfo.borr_sign_ind IS NULL) AND (X_borr_sign_ind IS NULL)))
1772 AND ((tlinfo.stud_sign_ind = x_stud_sign_ind) OR ((tlinfo.stud_sign_ind IS NULL) AND (X_stud_sign_ind IS NULL)))
1773 AND ((tlinfo.borr_credit_auth_code = x_borr_credit_auth_code) OR ((tlinfo.borr_credit_auth_code IS NULL) AND (X_borr_credit_auth_code IS NULL)))
1774 AND ((tlinfo.mpn_confirm_ind = x_mpn_confirm_ind) OR ((tlinfo.mpn_confirm_ind IS NULL) AND (X_mpn_confirm_ind IS NULL)))
1775 AND ((tlinfo.lender_use_txt = x_lender_use_txt) OR ((tlinfo.lender_use_txt IS NULL) AND (X_lender_use_txt IS NULL)))
1776 AND ((tlinfo.guarantor_use_txt = x_guarantor_use_txt) OR ((tlinfo.guarantor_use_txt IS NULL) AND (X_guarantor_use_txt IS NULL)))
1777 AND ((tlinfo.appl_loan_phase_code = x_appl_loan_phase_code) OR ((tlinfo.appl_loan_phase_code IS NULL) AND (X_appl_loan_phase_code IS NULL)))
1778 AND ((tlinfo.appl_loan_phase_code_chg = x_appl_loan_phase_code_chg) OR ((tlinfo.appl_loan_phase_code_chg IS NULL) AND (X_appl_loan_phase_code_chg IS NULL)))
1779 AND ((tlinfo.cl_rec_status = x_cl_rec_status) OR ((tlinfo.cl_rec_status IS NULL) AND (X_cl_rec_status IS NULL)))
1780 AND ((tlinfo.cl_rec_status_last_update = x_cl_rec_status_last_update) OR ((tlinfo.cl_rec_status_last_update IS NULL) AND (X_cl_rec_status_last_update IS NULL)))
1781 AND ((tlinfo.lend_apprv_denied_code = x_lend_apprv_denied_code) OR ((tlinfo.lend_apprv_denied_code IS NULL) AND (X_lend_apprv_denied_code IS NULL)))
1782 AND ((tlinfo.lend_apprv_denied_date = x_lend_apprv_denied_date) OR ((tlinfo.lend_apprv_denied_date IS NULL) AND (X_lend_apprv_denied_date IS NULL)))
1783 AND ((tlinfo.cl_version_code = x_cl_version_code) OR ((tlinfo.cl_version_code IS NULL) AND (X_cl_version_code IS NULL)))
1784 AND ((tlinfo.school_use_txt = x_school_use_txt) OR ((tlinfo.school_use_txt IS NULL) AND (X_school_use_txt IS NULL)))
1785 AND ((tlinfo.b_alien_reg_num_txt = x_b_alien_reg_num_txt) OR ((tlinfo.b_alien_reg_num_txt IS NULL) AND (X_b_alien_reg_num_txt IS NULL)))
1786 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)))
1787 ) THEN
1788 NULL;
1789 ELSE
1790 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1791 igs_ge_msg_stack.add;
1792 app_exception.raise_exception;
1793 END IF;
1794
1795 RETURN;
1796
1797 END lock_row;
1798
1799
1800 PROCEDURE update_row (
1801 x_rowid IN VARCHAR2,
1802 x_clrp1_id IN NUMBER,
1803 x_cbth_id IN NUMBER,
1804 x_rec_code IN VARCHAR2,
1805 x_rec_type_ind IN VARCHAR2,
1806 x_b_last_name IN VARCHAR2,
1807 x_b_first_name IN VARCHAR2,
1808 x_b_middle_name IN VARCHAR2,
1809 x_b_ssn IN NUMBER,
1810 x_b_permt_addr1 IN VARCHAR2,
1811 x_b_permt_addr2 IN VARCHAR2,
1812 x_b_permt_city IN VARCHAR2,
1813 x_b_permt_state IN VARCHAR2,
1814 x_b_permt_zip IN NUMBER,
1815 x_b_permt_zip_suffix IN NUMBER,
1816 x_b_permt_phone IN VARCHAR2,
1817 x_b_date_of_birth IN DATE,
1818 x_cl_loan_type IN VARCHAR2,
1819 x_req_loan_amt IN NUMBER,
1820 x_defer_req_code IN VARCHAR2,
1821 x_borw_interest_ind IN VARCHAR2,
1822 x_eft_auth_code IN VARCHAR2,
1823 x_b_signature_code IN VARCHAR2,
1824 x_b_signature_date IN DATE,
1825 x_loan_number IN VARCHAR2,
1826 x_cl_seq_number IN NUMBER,
1827 x_b_citizenship_status IN VARCHAR2,
1828 x_b_state_of_legal_res IN VARCHAR2,
1829 x_b_legal_res_date IN DATE,
1830 x_b_default_status IN VARCHAR2,
1831 x_b_outstd_loan_code IN VARCHAR2,
1832 x_b_indicator_code IN VARCHAR2,
1833 x_s_last_name IN VARCHAR2,
1834 x_s_first_name IN VARCHAR2,
1835 x_s_middle_name IN VARCHAR2,
1836 x_s_ssn IN NUMBER,
1837 x_s_date_of_birth IN DATE,
1838 x_s_citizenship_status IN VARCHAR2,
1839 x_s_default_code IN VARCHAR2,
1840 x_s_signature_code IN VARCHAR2,
1841 x_school_id IN NUMBER,
1842 x_loan_per_begin_date IN DATE,
1843 x_loan_per_end_date IN DATE,
1844 x_grade_level_code IN VARCHAR2,
1845 x_enrollment_code IN VARCHAR2,
1846 x_anticip_compl_date IN DATE,
1847 x_coa_amt IN NUMBER,
1848 x_efc_amt IN NUMBER,
1849 x_est_fa_amt IN NUMBER,
1850 x_fls_cert_amt IN NUMBER,
1851 x_flu_cert_amt IN NUMBER,
1852 x_flp_cert_amt IN NUMBER,
1853 x_sch_cert_date IN DATE,
1854 x_alt_cert_amt IN NUMBER,
1855 x_alt_appl_ver_code IN NUMBER,
1856 x_duns_school_id IN VARCHAR2,
1857 x_lender_id IN VARCHAR2,
1858 x_fls_approved_amt IN NUMBER,
1859 x_flu_approved_amt IN NUMBER,
1860 x_flp_approved_amt IN NUMBER,
1861 x_alt_approved_amt IN NUMBER,
1862 x_duns_lender_id IN VARCHAR2,
1863 x_guarantor_id IN VARCHAR2,
1864 x_fed_appl_form_code IN VARCHAR2,
1865 x_duns_guarnt_id IN VARCHAR2,
1866 x_lend_blkt_guarnt_ind IN VARCHAR2,
1867 x_lend_blkt_guarnt_appr_date IN DATE,
1868 x_guarnt_adj_ind IN VARCHAR2,
1869 x_guarantee_date IN DATE,
1870 x_guarantee_amt IN NUMBER,
1871 x_req_serial_loan_code IN VARCHAR2,
1872 x_borw_confirm_ind IN VARCHAR2,
1873 x_b_license_state IN VARCHAR2,
1874 x_b_license_number IN VARCHAR2,
1875 x_b_ref_code IN VARCHAR2,
1876 x_pnote_delivery_code IN VARCHAR2,
1877 x_b_foreign_postal_code IN VARCHAR2,
1878 x_lend_non_ed_brc_id IN VARCHAR2,
1879 x_last_resort_lender IN VARCHAR2,
1880 x_resp_to_orig_code IN VARCHAR2,
1881 x_err_mesg_1 IN VARCHAR2,
1882 x_err_mesg_2 IN VARCHAR2,
1883 x_err_mesg_3 IN VARCHAR2,
1884 x_err_mesg_4 IN VARCHAR2,
1885 x_err_mesg_5 IN VARCHAR2,
1886 x_guarnt_amt_redn_code IN VARCHAR2,
1887 x_tot_outstd_stafford IN NUMBER,
1888 x_tot_outstd_plus IN NUMBER,
1889 x_b_permt_addr_chg_date IN DATE,
1890 x_alt_prog_type_code IN VARCHAR2,
1891 x_alt_borw_tot_debt IN NUMBER,
1892 x_act_interest_rate IN NUMBER,
1893 x_prc_type_code IN VARCHAR2,
1894 x_service_type_code IN VARCHAR2,
1895 x_rev_notice_of_guarnt IN VARCHAR2,
1896 x_sch_refund_amt IN NUMBER,
1897 x_sch_refund_date IN DATE,
1898 x_guarnt_status_code IN VARCHAR2,
1899 x_lender_status_code IN VARCHAR2,
1900 x_pnote_status_code IN VARCHAR2,
1901 x_credit_status_code IN VARCHAR2,
1902 x_guarnt_status_date IN DATE,
1903 x_lender_status_date IN DATE,
1904 x_pnote_status_date IN DATE,
1905 x_credit_status_date IN DATE,
1906 x_act_serial_loan_code IN VARCHAR2,
1907 x_amt_avail_for_reinst IN NUMBER,
1908 x_sch_non_ed_brc_id IN VARCHAR2,
1909 x_uniq_layout_vend_code IN VARCHAR2,
1910 x_uniq_layout_ident_code IN VARCHAR2,
1911 x_resp_record_status IN VARCHAR2,
1912 x_mode IN VARCHAR2 DEFAULT 'R',
1913 x_borr_sign_ind IN VARCHAR2,
1914 x_stud_sign_ind IN VARCHAR2,
1915 x_borr_credit_auth_code IN VARCHAR2,
1916 x_mpn_confirm_ind IN VARCHAR2 ,
1917 x_lender_use_txt IN VARCHAR2 ,
1918 x_guarantor_use_txt IN VARCHAR2 ,
1919 x_appl_loan_phase_code IN VARCHAR2 ,
1920 x_appl_loan_phase_code_chg IN DATE ,
1921 x_cl_rec_status IN VARCHAR2 ,
1922 x_cl_rec_status_last_update IN DATE ,
1923 x_lend_apprv_denied_code IN VARCHAR2 ,
1924 x_lend_apprv_denied_date IN DATE ,
1925 x_cl_version_code IN VARCHAR2 ,
1926 x_school_use_txt IN VARCHAR2 ,
1927 x_b_alien_reg_num_txt IN VARCHAR2 ,
1928 x_esign_src_typ_cd IN VARCHAR2
1929 ) AS
1930 /*
1931 || Created By : prchandr
1932 || Created On : 02-NOV-2000
1933 || Purpose : Handles the UPDATE DML logic for the table.
1934 || Known limitations, enhancements or remarks :
1935 || Change History :
1936 || Who When What
1937 || (reverse chronological order - newest change first)
1938 */
1939 x_last_update_date DATE ;
1940 x_last_updated_by NUMBER;
1941 x_last_update_login NUMBER;
1942 x_request_id NUMBER;
1943 x_program_id NUMBER;
1944 x_program_application_id NUMBER;
1945 x_program_update_date DATE;
1946
1947 BEGIN
1948
1949 x_last_update_date := SYSDATE;
1950 IF (X_MODE = 'I') THEN
1951 x_last_updated_by := 1;
1952 x_last_update_login := 0;
1953 ELSIF (x_mode = 'R') THEN
1954 x_last_updated_by := fnd_global.user_id;
1955 IF x_last_updated_by IS NULL THEN
1956 x_last_updated_by := -1;
1957 END IF;
1958 x_last_update_login := fnd_global.login_id;
1959 IF (x_last_update_login IS NULL) THEN
1960 x_last_update_login := -1;
1961 END IF;
1962 ELSE
1963 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1964 igs_ge_msg_stack.add;
1965 app_exception.raise_exception;
1966 END IF;
1967
1968 before_dml(
1969 p_action => 'UPDATE',
1970 x_rowid => x_rowid,
1971 x_clrp1_id => x_clrp1_id,
1972 x_cbth_id => x_cbth_id,
1973 x_rec_code => x_rec_code,
1974 x_rec_type_ind => x_rec_type_ind,
1975 x_b_last_name => x_b_last_name,
1976 x_b_first_name => x_b_first_name,
1977 x_b_middle_name => x_b_middle_name,
1978 x_b_ssn => x_b_ssn,
1979 x_b_permt_addr1 => x_b_permt_addr1,
1980 x_b_permt_addr2 => x_b_permt_addr2,
1981 x_b_permt_city => x_b_permt_city,
1982 x_b_permt_state => x_b_permt_state,
1983 x_b_permt_zip => x_b_permt_zip,
1984 x_b_permt_zip_suffix => x_b_permt_zip_suffix,
1985 x_b_permt_phone => x_b_permt_phone,
1986 x_b_date_of_birth => x_b_date_of_birth,
1987 x_cl_loan_type => x_cl_loan_type,
1988 x_req_loan_amt => x_req_loan_amt,
1989 x_defer_req_code => x_defer_req_code,
1990 x_borw_interest_ind => x_borw_interest_ind,
1991 x_eft_auth_code => x_eft_auth_code,
1992 x_b_signature_code => x_b_signature_code,
1993 x_b_signature_date => x_b_signature_date,
1994 x_loan_number => x_loan_number,
1995 x_cl_seq_number => x_cl_seq_number,
1996 x_b_citizenship_status => x_b_citizenship_status,
1997 x_b_state_of_legal_res => x_b_state_of_legal_res,
1998 x_b_legal_res_date => x_b_legal_res_date,
1999 x_b_default_status => x_b_default_status,
2000 x_b_outstd_loan_code => x_b_outstd_loan_code,
2001 x_b_indicator_code => x_b_indicator_code,
2002 x_s_last_name => x_s_last_name,
2003 x_s_first_name => x_s_first_name,
2004 x_s_middle_name => x_s_middle_name,
2005 x_s_ssn => x_s_ssn,
2006 x_s_date_of_birth => x_s_date_of_birth,
2007 x_s_citizenship_status => x_s_citizenship_status,
2008 x_s_default_code => x_s_default_code,
2009 x_s_signature_code => x_s_signature_code,
2010 x_school_id => x_school_id,
2011 x_loan_per_begin_date => x_loan_per_begin_date,
2012 x_loan_per_end_date => x_loan_per_end_date,
2013 x_grade_level_code => x_grade_level_code,
2014 x_enrollment_code => x_enrollment_code,
2015 x_anticip_compl_date => x_anticip_compl_date,
2016 x_coa_amt => x_coa_amt,
2017 x_efc_amt => x_efc_amt,
2018 x_est_fa_amt => x_est_fa_amt,
2019 x_fls_cert_amt => x_fls_cert_amt,
2020 x_flu_cert_amt => x_flu_cert_amt,
2021 x_flp_cert_amt => x_flp_cert_amt,
2022 x_sch_cert_date => x_sch_cert_date,
2023 x_alt_cert_amt => x_alt_cert_amt,
2024 x_alt_appl_ver_code => x_alt_appl_ver_code,
2025 x_duns_school_id => x_duns_school_id,
2026 x_lender_id => x_lender_id,
2027 x_fls_approved_amt => x_fls_approved_amt,
2028 x_flu_approved_amt => x_flu_approved_amt,
2029 x_flp_approved_amt => x_flp_approved_amt,
2030 x_alt_approved_amt => x_alt_approved_amt,
2031 x_duns_lender_id => x_duns_lender_id,
2032 x_guarantor_id => x_guarantor_id,
2033 x_fed_appl_form_code => x_fed_appl_form_code,
2034 x_duns_guarnt_id => x_duns_guarnt_id,
2035 x_lend_blkt_guarnt_ind => x_lend_blkt_guarnt_ind,
2036 x_lend_blkt_guarnt_appr_date => x_lend_blkt_guarnt_appr_date,
2037 x_guarnt_adj_ind => x_guarnt_adj_ind,
2038 x_guarantee_date => x_guarantee_date,
2039 x_guarantee_amt => x_guarantee_amt,
2040 x_req_serial_loan_code => x_req_serial_loan_code,
2041 x_borw_confirm_ind => x_borw_confirm_ind,
2042 x_b_license_state => x_b_license_state,
2043 x_b_license_number => x_b_license_number,
2044 x_b_ref_code => x_b_ref_code,
2045 x_pnote_delivery_code => x_pnote_delivery_code,
2046 x_b_foreign_postal_code => x_b_foreign_postal_code,
2047 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
2048 x_last_resort_lender => x_last_resort_lender,
2049 x_resp_to_orig_code => x_resp_to_orig_code,
2050 x_err_mesg_1 => x_err_mesg_1,
2051 x_err_mesg_2 => x_err_mesg_2,
2052 x_err_mesg_3 => x_err_mesg_3,
2053 x_err_mesg_4 => x_err_mesg_4,
2054 x_err_mesg_5 => x_err_mesg_5,
2055 x_guarnt_amt_redn_code => x_guarnt_amt_redn_code,
2056 x_tot_outstd_stafford => x_tot_outstd_stafford,
2057 x_tot_outstd_plus => x_tot_outstd_plus,
2058 x_b_permt_addr_chg_date => x_b_permt_addr_chg_date,
2059 x_alt_prog_type_code => x_alt_prog_type_code,
2060 x_alt_borw_tot_debt => x_alt_borw_tot_debt,
2061 x_act_interest_rate => x_act_interest_rate,
2062 x_prc_type_code => x_prc_type_code,
2063 x_service_type_code => x_service_type_code,
2064 x_rev_notice_of_guarnt => x_rev_notice_of_guarnt,
2065 x_sch_refund_amt => x_sch_refund_amt,
2066 x_sch_refund_date => x_sch_refund_date,
2067 x_guarnt_status_code => x_guarnt_status_code,
2068 x_lender_status_code => x_lender_status_code,
2069 x_pnote_status_code => x_pnote_status_code,
2070 x_credit_status_code => x_credit_status_code,
2071 x_guarnt_status_date => x_guarnt_status_date,
2072 x_lender_status_date => x_lender_status_date,
2073 x_pnote_status_date => x_pnote_status_date,
2074 x_credit_status_date => x_credit_status_date,
2075 x_act_serial_loan_code => x_act_serial_loan_code,
2076 x_amt_avail_for_reinst => x_amt_avail_for_reinst,
2077 x_sch_non_ed_brc_id => x_sch_non_ed_brc_id,
2078 x_uniq_layout_vend_code => x_uniq_layout_vend_code,
2079 x_uniq_layout_ident_code => x_uniq_layout_ident_code,
2080 x_resp_record_status => x_resp_record_status,
2081 x_creation_date => x_last_update_date,
2082 x_created_by => x_last_updated_by,
2083 x_last_update_date => x_last_update_date,
2084 x_last_updated_by => x_last_updated_by,
2085 x_last_update_login => x_last_update_login,
2086 x_borr_sign_ind => x_borr_sign_ind,
2087 x_stud_sign_ind => x_stud_sign_ind,
2088 x_borr_credit_auth_code => x_borr_credit_auth_code,
2089 x_mpn_confirm_ind => x_mpn_confirm_ind,
2090 x_lender_use_txt => x_lender_use_txt,
2091 x_guarantor_use_txt => x_guarantor_use_txt,
2092 x_appl_loan_phase_code => x_appl_loan_phase_code,
2093 x_appl_loan_phase_code_chg => x_appl_loan_phase_code_chg,
2094 x_cl_rec_status => x_cl_rec_status,
2095 x_cl_rec_status_last_update => x_cl_rec_status_last_update,
2096 x_lend_apprv_denied_code => x_lend_apprv_denied_code,
2097 x_lend_apprv_denied_date => x_lend_apprv_denied_date,
2098 x_cl_version_code => x_cl_version_code,
2099 x_school_use_txt => x_school_use_txt,
2100 x_b_alien_reg_num_txt => x_b_alien_reg_num_txt,
2101 x_esign_src_typ_cd => x_esign_src_typ_cd
2102 );
2103
2104 IF (x_mode = 'R') THEN
2105 x_request_id := fnd_global.conc_request_id;
2106 x_program_id := fnd_global.conc_program_id;
2107 x_program_application_id := fnd_global.prog_appl_id;
2108 IF (x_request_id = -1) THEN
2109 x_request_id := old_references.request_id;
2110 x_program_id := old_references.program_id;
2111 x_program_application_id := old_references.program_application_id;
2112 x_program_update_date := old_references.program_update_date;
2113 ELSE
2114 x_program_update_date := SYSDATE;
2115 END IF;
2116 END IF;
2117
2118 UPDATE igf_sl_cl_resp_r1_all
2119 SET
2120 cbth_id = new_references.cbth_id,
2121 rec_code = new_references.rec_code,
2122 rec_type_ind = new_references.rec_type_ind,
2123 b_last_name = new_references.b_last_name,
2124 b_first_name = new_references.b_first_name,
2125 b_middle_name = new_references.b_middle_name,
2126 b_ssn = new_references.b_ssn,
2127 b_permt_addr1 = new_references.b_permt_addr1,
2128 b_permt_addr2 = new_references.b_permt_addr2,
2129 b_permt_city = new_references.b_permt_city,
2130 b_permt_state = new_references.b_permt_state,
2131 b_permt_zip = new_references.b_permt_zip,
2132 b_permt_zip_suffix = new_references.b_permt_zip_suffix,
2133 b_permt_phone = new_references.b_permt_phone,
2134 b_date_of_birth = new_references.b_date_of_birth,
2135 cl_loan_type = new_references.cl_loan_type,
2136 req_loan_amt = new_references.req_loan_amt,
2137 defer_req_code = new_references.defer_req_code,
2138 borw_interest_ind = new_references.borw_interest_ind,
2139 eft_auth_code = new_references.eft_auth_code,
2140 b_signature_code = new_references.b_signature_code,
2141 b_signature_date = new_references.b_signature_date,
2142 loan_number = new_references.loan_number,
2143 cl_seq_number = new_references.cl_seq_number,
2144 b_citizenship_status = new_references.b_citizenship_status,
2145 b_state_of_legal_res = new_references.b_state_of_legal_res,
2146 b_legal_res_date = new_references.b_legal_res_date,
2147 b_default_status = new_references.b_default_status,
2148 b_outstd_loan_code = new_references.b_outstd_loan_code,
2149 b_indicator_code = new_references.b_indicator_code,
2150 s_last_name = new_references.s_last_name,
2151 s_first_name = new_references.s_first_name,
2152 s_middle_name = new_references.s_middle_name,
2153 s_ssn = new_references.s_ssn,
2154 s_date_of_birth = new_references.s_date_of_birth,
2155 s_citizenship_status = new_references.s_citizenship_status,
2156 s_default_code = new_references.s_default_code,
2157 s_signature_code = new_references.s_signature_code,
2158 school_id = new_references.school_id,
2159 loan_per_begin_date = new_references.loan_per_begin_date,
2160 loan_per_end_date = new_references.loan_per_end_date,
2161 grade_level_code = new_references.grade_level_code,
2162 enrollment_code = new_references.enrollment_code,
2163 anticip_compl_date = new_references.anticip_compl_date,
2164 coa_amt = new_references.coa_amt,
2165 efc_amt = new_references.efc_amt,
2166 est_fa_amt = new_references.est_fa_amt,
2167 fls_cert_amt = new_references.fls_cert_amt,
2168 flu_cert_amt = new_references.flu_cert_amt,
2169 flp_cert_amt = new_references.flp_cert_amt,
2170 sch_cert_date = new_references.sch_cert_date,
2171 alt_cert_amt = new_references.alt_cert_amt,
2172 alt_appl_ver_code = new_references.alt_appl_ver_code,
2173 duns_school_id = new_references.duns_school_id,
2174 lender_id = new_references.lender_id,
2175 fls_approved_amt = new_references.fls_approved_amt,
2176 flu_approved_amt = new_references.flu_approved_amt,
2177 flp_approved_amt = new_references.flp_approved_amt,
2178 alt_approved_amt = new_references.alt_approved_amt,
2179 duns_lender_id = new_references.duns_lender_id,
2180 guarantor_id = new_references.guarantor_id,
2181 fed_appl_form_code = new_references.fed_appl_form_code,
2182 duns_guarnt_id = new_references.duns_guarnt_id,
2183 lend_blkt_guarnt_ind = new_references.lend_blkt_guarnt_ind,
2184 lend_blkt_guarnt_appr_date = new_references.lend_blkt_guarnt_appr_date,
2185 guarnt_adj_ind = new_references.guarnt_adj_ind,
2186 guarantee_date = new_references.guarantee_date,
2187 guarantee_amt = new_references.guarantee_amt,
2188 req_serial_loan_code = new_references.req_serial_loan_code,
2189 borw_confirm_ind = new_references.borw_confirm_ind,
2190 b_license_state = new_references.b_license_state,
2191 b_license_number = new_references.b_license_number,
2192 b_ref_code = new_references.b_ref_code,
2193 pnote_delivery_code = new_references.pnote_delivery_code,
2194 b_foreign_postal_code = new_references.b_foreign_postal_code,
2195 lend_non_ed_brc_id = new_references.lend_non_ed_brc_id,
2196 last_resort_lender = new_references.last_resort_lender,
2197 resp_to_orig_code = new_references.resp_to_orig_code,
2198 err_mesg_1 = new_references.err_mesg_1,
2199 err_mesg_2 = new_references.err_mesg_2,
2200 err_mesg_3 = new_references.err_mesg_3,
2201 err_mesg_4 = new_references.err_mesg_4,
2202 err_mesg_5 = new_references.err_mesg_5,
2203 guarnt_amt_redn_code = new_references.guarnt_amt_redn_code,
2204 tot_outstd_stafford = new_references.tot_outstd_stafford,
2205 tot_outstd_plus = new_references.tot_outstd_plus,
2206 b_permt_addr_chg_date = new_references.b_permt_addr_chg_date,
2207 alt_prog_type_code = new_references.alt_prog_type_code,
2208 alt_borw_tot_debt = new_references.alt_borw_tot_debt,
2209 act_interest_rate = new_references.act_interest_rate,
2210 prc_type_code = new_references.prc_type_code,
2211 service_type_code = new_references.service_type_code,
2212 rev_notice_of_guarnt = new_references.rev_notice_of_guarnt,
2213 sch_refund_amt = new_references.sch_refund_amt,
2214 sch_refund_date = new_references.sch_refund_date,
2215 guarnt_status_code = new_references.guarnt_status_code,
2216 lender_status_code = new_references.lender_status_code,
2217 pnote_status_code = new_references.pnote_status_code,
2218 credit_status_code = new_references.credit_status_code,
2219 guarnt_status_date = new_references.guarnt_status_date,
2220 lender_status_date = new_references.lender_status_date,
2221 pnote_status_date = new_references.pnote_status_date,
2222 credit_status_date = new_references.credit_status_date,
2223 act_serial_loan_code = new_references.act_serial_loan_code,
2224 amt_avail_for_reinst = new_references.amt_avail_for_reinst,
2225 sch_non_ed_brc_id = new_references.sch_non_ed_brc_id,
2226 uniq_layout_vend_code = new_references.uniq_layout_vend_code,
2227 uniq_layout_ident_code = new_references.uniq_layout_ident_code,
2228 resp_record_status = new_references.resp_record_status,
2229 last_update_date = x_last_update_date,
2230 last_updated_by = x_last_updated_by,
2231 last_update_login = x_last_update_login ,
2232 request_id = x_request_id,
2233 program_id = x_program_id,
2234 program_application_id = x_program_application_id,
2235 program_update_date = x_program_update_date,
2236 borr_sign_ind = new_references.borr_sign_ind,
2237 stud_sign_ind = new_references.stud_sign_ind,
2238 borr_credit_auth_code = new_references.borr_credit_auth_code,
2239 mpn_confirm_ind = new_references.mpn_confirm_ind,
2240 lender_use_txt = new_references.lender_use_txt,
2241 guarantor_use_txt = new_references.guarantor_use_txt,
2242 appl_loan_phase_code = new_references.appl_loan_phase_code,
2243 appl_loan_phase_code_chg = new_references.appl_loan_phase_code_chg,
2244 cl_rec_status = new_references.cl_rec_status ,
2245 cl_rec_status_last_update = new_references.cl_rec_status_last_update,
2246 lend_apprv_denied_code = new_references.lend_apprv_denied_code,
2247 lend_apprv_denied_date = new_references.lend_apprv_denied_date,
2248 cl_version_code = new_references.cl_version_code,
2249 school_use_txt = new_references.school_use_txt,
2250 b_alien_reg_num_txt = new_references.b_alien_reg_num_txt,
2251 esign_src_typ_cd = new_references.esign_src_typ_cd
2252 WHERE rowid = x_rowid;
2253
2254 IF (SQL%NOTFOUND) THEN
2255 RAISE NO_DATA_FOUND;
2256 END IF;
2257
2258 END update_row;
2259
2260
2261 PROCEDURE add_row (
2262 x_rowid IN OUT NOCOPY VARCHAR2,
2263 x_clrp1_id IN OUT NOCOPY NUMBER,
2264 x_cbth_id IN NUMBER,
2265 x_rec_code IN VARCHAR2,
2266 x_rec_type_ind IN VARCHAR2,
2267 x_b_last_name IN VARCHAR2,
2268 x_b_first_name IN VARCHAR2,
2269 x_b_middle_name IN VARCHAR2,
2270 x_b_ssn IN NUMBER,
2271 x_b_permt_addr1 IN VARCHAR2,
2272 x_b_permt_addr2 IN VARCHAR2,
2273 x_b_permt_city IN VARCHAR2,
2274 x_b_permt_state IN VARCHAR2,
2275 x_b_permt_zip IN NUMBER,
2276 x_b_permt_zip_suffix IN NUMBER,
2277 x_b_permt_phone IN VARCHAR2,
2278 x_b_date_of_birth IN DATE,
2279 x_cl_loan_type IN VARCHAR2,
2280 x_req_loan_amt IN NUMBER,
2281 x_defer_req_code IN VARCHAR2,
2282 x_borw_interest_ind IN VARCHAR2,
2283 x_eft_auth_code IN VARCHAR2,
2284 x_b_signature_code IN VARCHAR2,
2285 x_b_signature_date IN DATE,
2286 x_loan_number IN VARCHAR2,
2287 x_cl_seq_number IN NUMBER,
2288 x_b_citizenship_status IN VARCHAR2,
2289 x_b_state_of_legal_res IN VARCHAR2,
2290 x_b_legal_res_date IN DATE,
2291 x_b_default_status IN VARCHAR2,
2292 x_b_outstd_loan_code IN VARCHAR2,
2293 x_b_indicator_code IN VARCHAR2,
2294 x_s_last_name IN VARCHAR2,
2295 x_s_first_name IN VARCHAR2,
2296 x_s_middle_name IN VARCHAR2,
2297 x_s_ssn IN NUMBER,
2298 x_s_date_of_birth IN DATE,
2299 x_s_citizenship_status IN VARCHAR2,
2300 x_s_default_code IN VARCHAR2,
2301 x_s_signature_code IN VARCHAR2,
2302 x_school_id IN NUMBER,
2303 x_loan_per_begin_date IN DATE,
2304 x_loan_per_end_date IN DATE,
2305 x_grade_level_code IN VARCHAR2,
2306 x_enrollment_code IN VARCHAR2,
2307 x_anticip_compl_date IN DATE,
2308 x_coa_amt IN NUMBER,
2309 x_efc_amt IN NUMBER,
2310 x_est_fa_amt IN NUMBER,
2311 x_fls_cert_amt IN NUMBER,
2312 x_flu_cert_amt IN NUMBER,
2313 x_flp_cert_amt IN NUMBER,
2314 x_sch_cert_date IN DATE,
2315 x_alt_cert_amt IN NUMBER,
2316 x_alt_appl_ver_code IN NUMBER,
2317 x_duns_school_id IN VARCHAR2,
2318 x_lender_id IN VARCHAR2,
2319 x_fls_approved_amt IN NUMBER,
2320 x_flu_approved_amt IN NUMBER,
2321 x_flp_approved_amt IN NUMBER,
2322 x_alt_approved_amt IN NUMBER,
2323 x_duns_lender_id IN VARCHAR2,
2324 x_guarantor_id IN VARCHAR2,
2325 x_fed_appl_form_code IN VARCHAR2,
2326 x_duns_guarnt_id IN VARCHAR2,
2327 x_lend_blkt_guarnt_ind IN VARCHAR2,
2328 x_lend_blkt_guarnt_appr_date IN DATE,
2329 x_guarnt_adj_ind IN VARCHAR2,
2330 x_guarantee_date IN DATE,
2331 x_guarantee_amt IN NUMBER,
2332 x_req_serial_loan_code IN VARCHAR2,
2333 x_borw_confirm_ind IN VARCHAR2,
2334 x_b_license_state IN VARCHAR2,
2335 x_b_license_number IN VARCHAR2,
2336 x_b_ref_code IN VARCHAR2,
2337 x_pnote_delivery_code IN VARCHAR2,
2338 x_b_foreign_postal_code IN VARCHAR2,
2339 x_lend_non_ed_brc_id IN VARCHAR2,
2340 x_last_resort_lender IN VARCHAR2,
2341 x_resp_to_orig_code IN VARCHAR2,
2342 x_err_mesg_1 IN VARCHAR2,
2343 x_err_mesg_2 IN VARCHAR2,
2344 x_err_mesg_3 IN VARCHAR2,
2345 x_err_mesg_4 IN VARCHAR2,
2346 x_err_mesg_5 IN VARCHAR2,
2347 x_guarnt_amt_redn_code IN VARCHAR2,
2348 x_tot_outstd_stafford IN NUMBER,
2349 x_tot_outstd_plus IN NUMBER,
2350 x_b_permt_addr_chg_date IN DATE,
2351 x_alt_prog_type_code IN VARCHAR2,
2352 x_alt_borw_tot_debt IN NUMBER,
2353 x_act_interest_rate IN NUMBER,
2354 x_prc_type_code IN VARCHAR2,
2355 x_service_type_code IN VARCHAR2,
2356 x_rev_notice_of_guarnt IN VARCHAR2,
2357 x_sch_refund_amt IN NUMBER,
2358 x_sch_refund_date IN DATE,
2359 x_guarnt_status_code IN VARCHAR2,
2360 x_lender_status_code IN VARCHAR2,
2361 x_pnote_status_code IN VARCHAR2,
2362 x_credit_status_code IN VARCHAR2,
2363 x_guarnt_status_date IN DATE,
2364 x_lender_status_date IN DATE,
2365 x_pnote_status_date IN DATE,
2366 x_credit_status_date IN DATE,
2367 x_act_serial_loan_code IN VARCHAR2,
2368 x_amt_avail_for_reinst IN NUMBER,
2369 x_sch_non_ed_brc_id IN VARCHAR2,
2370 x_uniq_layout_vend_code IN VARCHAR2,
2371 x_uniq_layout_ident_code IN VARCHAR2,
2372 x_resp_record_status IN VARCHAR2,
2373 x_mode IN VARCHAR2 DEFAULT 'R',
2374 x_borr_sign_ind IN VARCHAR2,
2375 x_stud_sign_ind IN VARCHAR2,
2376 x_borr_credit_auth_code IN VARCHAR2 ,
2377 x_mpn_confirm_ind IN VARCHAR2 ,
2378 x_lender_use_txt IN VARCHAR2 ,
2379 x_guarantor_use_txt IN VARCHAR2 ,
2380 x_appl_loan_phase_code IN VARCHAR2 ,
2381 x_appl_loan_phase_code_chg IN DATE ,
2382 x_cl_rec_status IN VARCHAR2 ,
2383 x_cl_rec_status_last_update IN DATE ,
2384 x_lend_apprv_denied_code IN VARCHAR2 ,
2385 x_lend_apprv_denied_date IN DATE ,
2386 x_cl_version_code IN VARCHAR2 ,
2387 x_school_use_txt IN VARCHAR2 ,
2388 x_b_alien_reg_num_txt IN VARCHAR2 ,
2389 x_esign_src_typ_cd IN VARCHAR2
2390 ) AS
2391 /*
2392 || Created By : prchandr
2393 || Created On : 02-NOV-2000
2394 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
2395 || Known limitations, enhancements or remarks :
2396 || Change History :
2397 || Who When What
2398 || (reverse chronological order - newest change first)
2399 */
2400 CURSOR c1 IS
2401 SELECT rowid
2402 FROM igf_sl_cl_resp_r1_all
2403 WHERE clrp1_id = x_clrp1_id;
2404
2405 BEGIN
2406
2407 OPEN c1;
2408 FETCH c1 INTO x_rowid;
2409 IF (c1%NOTFOUND) THEN
2410 CLOSE c1;
2411
2412 insert_row (
2413 x_rowid,
2414 x_clrp1_id,
2415 x_cbth_id,
2416 x_rec_code,
2417 x_rec_type_ind,
2418 x_b_last_name,
2419 x_b_first_name,
2420 x_b_middle_name,
2421 x_b_ssn,
2422 x_b_permt_addr1,
2423 x_b_permt_addr2,
2424 x_b_permt_city,
2425 x_b_permt_state,
2426 x_b_permt_zip,
2427 x_b_permt_zip_suffix,
2428 x_b_permt_phone,
2429 x_b_date_of_birth,
2430 x_cl_loan_type,
2431 x_req_loan_amt,
2432 x_defer_req_code,
2433 x_borw_interest_ind,
2434 x_eft_auth_code,
2435 x_b_signature_code,
2436 x_b_signature_date,
2437 x_loan_number,
2438 x_cl_seq_number,
2439 x_b_citizenship_status,
2440 x_b_state_of_legal_res,
2441 x_b_legal_res_date,
2442 x_b_default_status,
2443 x_b_outstd_loan_code,
2444 x_b_indicator_code,
2445 x_s_last_name,
2446 x_s_first_name,
2447 x_s_middle_name,
2448 x_s_ssn,
2449 x_s_date_of_birth,
2450 x_s_citizenship_status,
2451 x_s_default_code,
2452 x_s_signature_code,
2453 x_school_id,
2454 x_loan_per_begin_date,
2455 x_loan_per_end_date,
2456 x_grade_level_code,
2457 x_enrollment_code,
2458 x_anticip_compl_date,
2459 x_coa_amt,
2460 x_efc_amt,
2461 x_est_fa_amt,
2462 x_fls_cert_amt,
2463 x_flu_cert_amt,
2464 x_flp_cert_amt,
2465 x_sch_cert_date,
2466 x_alt_cert_amt,
2467 x_alt_appl_ver_code,
2468 x_duns_school_id,
2469 x_lender_id,
2470 x_fls_approved_amt,
2471 x_flu_approved_amt,
2472 x_flp_approved_amt,
2473 x_alt_approved_amt,
2474 x_duns_lender_id,
2475 x_guarantor_id,
2476 x_fed_appl_form_code,
2477 x_duns_guarnt_id,
2478 x_lend_blkt_guarnt_ind,
2479 x_lend_blkt_guarnt_appr_date,
2480 x_guarnt_adj_ind,
2481 x_guarantee_date,
2482 x_guarantee_amt,
2483 x_req_serial_loan_code,
2484 x_borw_confirm_ind,
2485 x_b_license_state,
2486 x_b_license_number,
2487 x_b_ref_code,
2488 x_pnote_delivery_code,
2489 x_b_foreign_postal_code,
2490 x_lend_non_ed_brc_id,
2491 x_last_resort_lender,
2492 x_resp_to_orig_code,
2493 x_err_mesg_1,
2494 x_err_mesg_2,
2495 x_err_mesg_3,
2496 x_err_mesg_4,
2497 x_err_mesg_5,
2498 x_guarnt_amt_redn_code,
2499 x_tot_outstd_stafford,
2500 x_tot_outstd_plus,
2501 x_b_permt_addr_chg_date,
2502 x_alt_prog_type_code,
2503 x_alt_borw_tot_debt,
2504 x_act_interest_rate,
2505 x_prc_type_code,
2506 x_service_type_code,
2507 x_rev_notice_of_guarnt,
2508 x_sch_refund_amt,
2509 x_sch_refund_date,
2510 x_guarnt_status_code,
2511 x_lender_status_code,
2512 x_pnote_status_code,
2513 x_credit_status_code,
2514 x_guarnt_status_date,
2515 x_lender_status_date,
2516 x_pnote_status_date,
2517 x_credit_status_date,
2518 x_act_serial_loan_code,
2519 x_amt_avail_for_reinst,
2520 x_sch_non_ed_brc_id,
2521 x_uniq_layout_vend_code,
2522 x_uniq_layout_ident_code,
2523 x_resp_record_status,
2524 x_mode,
2525 x_borr_sign_ind,
2526 x_stud_sign_ind,
2527 x_borr_credit_auth_code,
2528 x_mpn_confirm_ind,
2529 x_lender_use_txt,
2530 x_guarantor_use_txt ,
2531 x_appl_loan_phase_code,
2532 x_appl_loan_phase_code_chg,
2533 x_cl_rec_status ,
2534 x_cl_rec_status_last_update,
2535 x_lend_apprv_denied_code ,
2536 x_lend_apprv_denied_date ,
2537 x_cl_version_code,
2538 x_school_use_txt,
2539 x_b_alien_reg_num_txt,
2540 x_esign_src_typ_cd
2541 );
2542 RETURN;
2543 END IF;
2544 CLOSE c1;
2545
2546 update_row (
2547 x_rowid,
2548 x_clrp1_id,
2549 x_cbth_id,
2550 x_rec_code,
2551 x_rec_type_ind,
2552 x_b_last_name,
2553 x_b_first_name,
2554 x_b_middle_name,
2555 x_b_ssn,
2556 x_b_permt_addr1,
2557 x_b_permt_addr2,
2558 x_b_permt_city,
2559 x_b_permt_state,
2560 x_b_permt_zip,
2561 x_b_permt_zip_suffix,
2562 x_b_permt_phone,
2563 x_b_date_of_birth,
2564 x_cl_loan_type,
2565 x_req_loan_amt,
2566 x_defer_req_code,
2567 x_borw_interest_ind,
2568 x_eft_auth_code,
2569 x_b_signature_code,
2570 x_b_signature_date,
2571 x_loan_number,
2572 x_cl_seq_number,
2573 x_b_citizenship_status,
2574 x_b_state_of_legal_res,
2575 x_b_legal_res_date,
2576 x_b_default_status,
2577 x_b_outstd_loan_code,
2578 x_b_indicator_code,
2579 x_s_last_name,
2580 x_s_first_name,
2581 x_s_middle_name,
2582 x_s_ssn,
2583 x_s_date_of_birth,
2584 x_s_citizenship_status,
2585 x_s_default_code,
2586 x_s_signature_code,
2587 x_school_id,
2588 x_loan_per_begin_date,
2589 x_loan_per_end_date,
2590 x_grade_level_code,
2591 x_enrollment_code,
2592 x_anticip_compl_date,
2593 x_coa_amt,
2594 x_efc_amt,
2595 x_est_fa_amt,
2596 x_fls_cert_amt,
2597 x_flu_cert_amt,
2598 x_flp_cert_amt,
2599 x_sch_cert_date,
2600 x_alt_cert_amt,
2601 x_alt_appl_ver_code,
2602 x_duns_school_id,
2603 x_lender_id,
2604 x_fls_approved_amt,
2605 x_flu_approved_amt,
2606 x_flp_approved_amt,
2607 x_alt_approved_amt,
2608 x_duns_lender_id,
2609 x_guarantor_id,
2610 x_fed_appl_form_code,
2611 x_duns_guarnt_id,
2612 x_lend_blkt_guarnt_ind,
2613 x_lend_blkt_guarnt_appr_date,
2614 x_guarnt_adj_ind,
2615 x_guarantee_date,
2616 x_guarantee_amt,
2617 x_req_serial_loan_code,
2618 x_borw_confirm_ind,
2619 x_b_license_state,
2620 x_b_license_number,
2621 x_b_ref_code,
2622 x_pnote_delivery_code,
2623 x_b_foreign_postal_code,
2624 x_lend_non_ed_brc_id,
2625 x_last_resort_lender,
2626 x_resp_to_orig_code,
2627 x_err_mesg_1,
2628 x_err_mesg_2,
2629 x_err_mesg_3,
2630 x_err_mesg_4,
2631 x_err_mesg_5,
2632 x_guarnt_amt_redn_code,
2633 x_tot_outstd_stafford,
2634 x_tot_outstd_plus,
2635 x_b_permt_addr_chg_date,
2636 x_alt_prog_type_code,
2637 x_alt_borw_tot_debt,
2638 x_act_interest_rate,
2639 x_prc_type_code,
2640 x_service_type_code,
2641 x_rev_notice_of_guarnt,
2642 x_sch_refund_amt,
2643 x_sch_refund_date,
2644 x_guarnt_status_code,
2645 x_lender_status_code,
2646 x_pnote_status_code,
2647 x_credit_status_code,
2648 x_guarnt_status_date,
2649 x_lender_status_date,
2650 x_pnote_status_date,
2651 x_credit_status_date,
2652 x_act_serial_loan_code,
2653 x_amt_avail_for_reinst,
2654 x_sch_non_ed_brc_id,
2655 x_uniq_layout_vend_code,
2656 x_uniq_layout_ident_code,
2657 x_resp_record_status,
2658 x_mode,
2659 x_borr_sign_ind,
2660 x_stud_sign_ind,
2661 x_borr_credit_auth_code,
2662 x_mpn_confirm_ind,
2663 x_lender_use_txt,
2664 x_guarantor_use_txt ,
2665 x_appl_loan_phase_code,
2666 x_appl_loan_phase_code_chg,
2667 x_cl_rec_status ,
2668 x_cl_rec_status_last_update,
2669 x_lend_apprv_denied_code ,
2670 x_lend_apprv_denied_date ,
2671 x_cl_version_code,
2672 x_school_use_txt,
2673 x_b_alien_reg_num_txt,
2674 x_esign_src_typ_cd
2675 );
2676
2677 END add_row;
2678
2679
2680 PROCEDURE delete_row (
2681 x_rowid IN VARCHAR2
2682 ) AS
2683 /*
2684 || Created By : prchandr
2685 || Created On : 02-NOV-2000
2686 || Purpose : Handles the DELETE DML logic for the table.
2687 || Known limitations, enhancements or remarks :
2688 || Change History :
2689 || Who When What
2690 || (reverse chronological order - newest change first)
2691 */
2692 BEGIN
2693
2694 before_dml (
2695 p_action => 'DELETE',
2696 x_rowid => x_rowid
2697 );
2698
2699 DELETE FROM igf_sl_cl_resp_r1_all
2700 WHERE rowid = x_rowid;
2701
2702 IF (SQL%NOTFOUND) THEN
2703 RAISE NO_DATA_FOUND;
2704 END IF;
2705
2706 END delete_row;
2707
2708
2709 END igf_sl_cl_resp_r1_pkg;