[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_LOR_LOC_HISTORY_PKG
Source
1 PACKAGE BODY igf_sl_lor_loc_history_pkg AS
2 /* $Header: IGFLI36B.pls 120.1 2006/04/19 08:30:23 bvisvana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_lor_loc_history%ROWTYPE;
6 new_references igf_sl_lor_loc_history%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_loan_id IN NUMBER,
12 x_origination_id IN NUMBER,
13 x_loan_number IN VARCHAR2,
14 x_loan_type IN VARCHAR2,
15 x_loan_amt_offered IN NUMBER,
16 x_loan_amt_accepted IN NUMBER,
17 x_loan_per_begin_date IN DATE,
18 x_loan_per_end_date IN DATE,
19 x_acad_yr_begin_date IN DATE,
20 x_acad_yr_end_date IN DATE,
21 x_loan_status IN VARCHAR2,
22 x_loan_status_date IN DATE,
23 x_loan_chg_status IN VARCHAR2,
24 x_loan_chg_status_date IN DATE,
25 x_req_serial_loan_code IN VARCHAR2,
26 x_act_serial_loan_code IN VARCHAR2,
27 x_active IN VARCHAR2,
28 x_active_date IN DATE,
29 x_sch_cert_date IN DATE,
30 x_orig_status_flag IN VARCHAR2,
31 x_orig_batch_id IN VARCHAR2,
32 x_orig_batch_date IN DATE,
33 x_orig_ack_date IN DATE,
34 x_credit_override IN VARCHAR2,
35 x_credit_decision_date IN DATE,
36 x_pnote_delivery_code IN VARCHAR2,
37 x_pnote_status IN VARCHAR2,
38 x_pnote_status_date IN DATE,
39 x_pnote_id IN VARCHAR2,
40 x_pnote_print_ind IN VARCHAR2,
41 x_pnote_accept_amt IN NUMBER,
42 x_pnote_accept_date IN DATE,
43 x_p_signature_code IN VARCHAR2,
44 x_p_signature_date IN DATE,
45 x_s_signature_code IN VARCHAR2,
46 x_unsub_elig_for_heal IN VARCHAR2,
47 x_disclosure_print_ind IN VARCHAR2,
48 x_orig_fee_perct IN NUMBER,
49 x_borw_confirm_ind IN VARCHAR2,
50 x_borw_interest_ind IN VARCHAR2,
51 x_unsub_elig_for_depnt IN VARCHAR2,
52 x_guarantee_amt IN NUMBER,
53 x_guarantee_date IN DATE,
54 x_guarnt_adj_ind IN VARCHAR2,
55 x_guarnt_amt_redn_code IN VARCHAR2,
56 x_guarnt_status_code IN VARCHAR2,
57 x_guarnt_status_date IN DATE,
58 x_lend_apprv_denied_code IN VARCHAR2,
59 x_lend_apprv_denied_date IN DATE,
60 x_lend_status_code IN VARCHAR2,
61 x_lend_status_date IN DATE,
62 x_grade_level_code IN VARCHAR2,
63 x_enrollment_code IN VARCHAR2,
64 x_anticip_compl_date IN DATE,
65 x_borw_lender_id IN VARCHAR2,
66 x_guarantor_id IN VARCHAR2,
67 x_prc_type_code IN VARCHAR2,
68 x_rec_type_ind IN VARCHAR2,
69 x_cl_loan_type IN VARCHAR2,
70 x_cl_seq_number IN NUMBER,
71 x_last_resort_lender IN VARCHAR2,
72 x_lender_id IN VARCHAR2,
73 x_lend_non_ed_brc_id IN VARCHAR2,
74 x_recipient_id IN VARCHAR2,
75 x_recipient_type IN VARCHAR2,
76 x_recip_non_ed_brc_id IN VARCHAR2,
77 x_cl_rec_status IN VARCHAR2,
78 x_cl_rec_status_last_update IN DATE,
79 x_alt_prog_type_code IN VARCHAR2,
80 x_alt_appl_ver_code IN NUMBER,
81 x_borw_outstd_loan_code IN VARCHAR2,
82 x_mpn_confirm_code IN VARCHAR2,
83 x_resp_to_orig_code IN VARCHAR2,
84 x_appl_loan_phase_code IN VARCHAR2,
85 x_appl_loan_phase_code_chg IN DATE,
86 x_tot_outstd_stafford IN NUMBER,
87 x_tot_outstd_plus IN NUMBER,
88 x_alt_borw_tot_debt IN NUMBER,
89 x_act_interest_rate IN NUMBER,
90 x_service_type_code IN VARCHAR2,
91 x_rev_notice_of_guarnt IN VARCHAR2,
92 x_sch_refund_amt IN NUMBER,
93 x_sch_refund_date IN DATE,
94 x_uniq_layout_vend_code IN VARCHAR2,
95 x_uniq_layout_ident_code IN VARCHAR2,
96 x_p_person_id IN NUMBER,
97 x_p_ssn IN VARCHAR2,
98 x_p_last_name IN VARCHAR2,
99 x_p_first_name IN VARCHAR2,
100 x_p_middle_name IN VARCHAR2,
101 x_p_permt_addr1 IN VARCHAR2,
102 x_p_permt_addr2 IN VARCHAR2,
103 x_p_permt_city IN VARCHAR2,
104 x_p_permt_state IN VARCHAR2,
105 x_p_permt_zip IN VARCHAR2,
106 x_p_permt_addr_chg_date IN DATE,
107 x_p_permt_phone IN VARCHAR2,
108 x_p_email_addr IN VARCHAR2,
109 x_p_date_of_birth IN DATE,
110 x_p_license_num IN VARCHAR2,
111 x_p_license_state IN VARCHAR2,
112 x_p_citizenship_status IN VARCHAR2,
113 x_p_alien_reg_num IN VARCHAR2,
114 x_p_default_status IN VARCHAR2,
115 x_p_foreign_postal_code IN VARCHAR2,
116 x_p_state_of_legal_res IN VARCHAR2,
117 x_p_legal_res_date IN DATE,
118 x_s_ssn IN VARCHAR2,
119 x_s_last_name IN VARCHAR2,
120 x_s_first_name IN VARCHAR2,
121 x_s_middle_name IN VARCHAR2,
122 x_s_permt_addr1 IN VARCHAR2,
123 x_s_permt_addr2 IN VARCHAR2,
124 x_s_permt_city IN VARCHAR2,
125 x_s_permt_state IN VARCHAR2,
126 x_s_permt_zip IN VARCHAR2,
127 x_s_permt_addr_chg_date IN DATE,
128 x_s_permt_phone IN VARCHAR2,
129 x_s_local_addr1 IN VARCHAR2,
130 x_s_local_addr2 IN VARCHAR2,
131 x_s_local_city IN VARCHAR2,
132 x_s_local_state IN VARCHAR2,
133 x_s_local_zip IN VARCHAR2,
134 x_s_email_addr IN VARCHAR2,
135 x_s_date_of_birth IN DATE,
136 x_s_license_num IN VARCHAR2,
137 x_s_license_state IN VARCHAR2,
138 x_s_depncy_status IN VARCHAR2,
139 x_s_default_status IN VARCHAR2,
140 x_s_citizenship_status IN VARCHAR2,
141 x_s_alien_reg_num IN VARCHAR2,
142 x_s_foreign_postal_code IN VARCHAR2,
143 x_pnote_batch_id IN VARCHAR2,
144 x_pnote_ack_date IN DATE,
145 x_pnote_mpn_ind IN VARCHAR2,
146 x_award_id IN NUMBER,
147 x_base_id IN NUMBER,
148 x_loan_key_num IN NUMBER,
149 x_fin_award_year IN VARCHAR2,
150 x_cps_trans_num IN NUMBER,
151 x_pymt_servicer_amt IN NUMBER,
152 x_pymt_servicer_date IN DATE,
153 x_book_loan_amt IN NUMBER,
154 x_book_loan_amt_date IN DATE,
155 x_s_chg_ssn IN VARCHAR2,
156 x_s_chg_last_name IN VARCHAR2,
157 x_b_chg_ssn IN VARCHAR2,
158 x_b_chg_last_name IN VARCHAR2,
159 x_note_message IN VARCHAR2,
160 x_full_resp_code IN VARCHAR2,
161 x_s_permt_county IN VARCHAR2,
162 x_b_permt_county IN VARCHAR2,
163 x_s_permt_country IN VARCHAR2,
164 x_b_permt_country IN VARCHAR2,
165 x_crdt_decision_status IN VARCHAR2,
166 x_b_chg_birth_date IN DATE,
167 x_s_chg_birth_date IN DATE,
168 x_external_loan_id_txt IN VARCHAR2,
169 x_deferment_request_code IN VARCHAR2,
170 x_eft_authorization_code IN VARCHAR2,
171 x_requested_loan_amt IN NUMBER,
172 x_actual_record_type_code IN VARCHAR2,
173 x_reinstatement_amt IN NUMBER,
174 x_lender_use_txt IN VARCHAR2,
175 x_guarantor_use_txt IN VARCHAR2,
176 x_fls_approved_amt IN NUMBER,
177 x_flu_approved_amt IN NUMBER,
178 x_flp_approved_amt IN NUMBER,
179 x_alt_approved_amt IN NUMBER,
180 x_loan_app_form_code IN VARCHAR2,
181 x_alt_borrower_ind_flag IN VARCHAR2,
182 x_school_id_txt IN VARCHAR2,
183 x_cost_of_attendance_amt IN NUMBER,
184 x_EXPECT_FAMILY_CONTRIBUTE_AMT IN NUMBER,
185 x_established_fin_aid_amount IN NUMBER,
186 x_BOROWER_ELECTRONIC_SIGN_FLAG IN VARCHAR2,
187 x_student_electronic_sign_flag IN VARCHAR2,
188 x_BOROWER_CREDIT_AUTHORIZ_FLAG IN VARCHAR2,
189 x_mpn_type_flag IN VARCHAR2,
190 x_school_use_txt IN VARCHAR2,
191 x_document_id_txt IN VARCHAR2,
192 x_atd_entity_id_txt IN VARCHAR2,
193 x_rep_entity_id_txt IN VARCHAR2,
194 x_source_entity_id_txt IN VARCHAR2,
195 x_interest_rebate_percent_num IN NUMBER,
196 x_esign_src_typ_cd IN VARCHAR2,
197 x_loansh_id IN NUMBER,
198 x_source_txt IN VARCHAR2,
199 x_creation_date IN DATE,
200 x_created_by IN NUMBER,
201 x_last_update_date IN DATE,
202 x_last_updated_by IN NUMBER,
203 x_last_update_login IN NUMBER
204 ) AS
205 /*
206 || Created By : [email protected]
207 || Created On : 03-NOV-2004
208 || Purpose : Initialises the Old and New references for the columns of the table.
209 || Known limitations, enhancements or remarks :
210 || Change History :
211 || Who When What
212 || (reverse chronological order - newest change first)
213 */
214
215 CURSOR cur_old_ref_values IS
216 SELECT *
220 BEGIN
217 FROM igf_sl_lor_loc_history
218 WHERE rowid = x_rowid;
219
221
222 l_rowid := x_rowid;
223
224 -- Code for setting the Old and New Reference Values.
225 -- Populate Old Values.
226 OPEN cur_old_ref_values;
227 FETCH cur_old_ref_values INTO old_references;
228 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
229 CLOSE cur_old_ref_values;
230 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
231 igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 RETURN;
234 END IF;
235 CLOSE cur_old_ref_values;
236
237 -- Populate New Values.
238 new_references.loan_id := x_loan_id;
239 new_references.origination_id := x_origination_id;
240 new_references.loan_number := x_loan_number;
241 new_references.loan_type := x_loan_type;
242 new_references.loan_amt_offered := x_loan_amt_offered;
243 new_references.loan_amt_accepted := x_loan_amt_accepted;
244 new_references.loan_per_begin_date := x_loan_per_begin_date;
245 new_references.loan_per_end_date := x_loan_per_end_date;
246 new_references.acad_yr_begin_date := x_acad_yr_begin_date;
247 new_references.acad_yr_end_date := x_acad_yr_end_date;
248 new_references.loan_status := x_loan_status;
249 new_references.loan_status_date := x_loan_status_date;
250 new_references.loan_chg_status := x_loan_chg_status;
251 new_references.loan_chg_status_date := x_loan_chg_status_date;
252 new_references.req_serial_loan_code := x_req_serial_loan_code;
253 new_references.act_serial_loan_code := x_act_serial_loan_code;
254 new_references.active := x_active;
255 new_references.active_date := x_active_date;
256 new_references.sch_cert_date := x_sch_cert_date;
257 new_references.orig_status_flag := x_orig_status_flag;
258 new_references.orig_batch_id := x_orig_batch_id;
259 new_references.orig_batch_date := x_orig_batch_date;
260 new_references.orig_ack_date := x_orig_ack_date;
261 new_references.credit_override := x_credit_override;
262 new_references.credit_decision_date := x_credit_decision_date;
263 new_references.pnote_delivery_code := x_pnote_delivery_code;
264 new_references.pnote_status := x_pnote_status;
265 new_references.pnote_status_date := x_pnote_status_date;
266 new_references.pnote_id := x_pnote_id;
267 new_references.pnote_print_ind := x_pnote_print_ind;
268 new_references.pnote_accept_amt := x_pnote_accept_amt;
269 new_references.pnote_accept_date := x_pnote_accept_date;
270 new_references.p_signature_code := x_p_signature_code;
271 new_references.p_signature_date := x_p_signature_date;
272 new_references.s_signature_code := x_s_signature_code;
273 new_references.unsub_elig_for_heal := x_unsub_elig_for_heal;
274 new_references.disclosure_print_ind := x_disclosure_print_ind;
275 new_references.orig_fee_perct := x_orig_fee_perct;
276 new_references.borw_confirm_ind := x_borw_confirm_ind;
277 new_references.borw_interest_ind := x_borw_interest_ind;
278 new_references.unsub_elig_for_depnt := x_unsub_elig_for_depnt;
279 new_references.guarantee_amt := x_guarantee_amt;
280 new_references.guarantee_date := x_guarantee_date;
281 new_references.guarnt_adj_ind := x_guarnt_adj_ind;
282 new_references.guarnt_amt_redn_code := x_guarnt_amt_redn_code;
283 new_references.guarnt_status_code := x_guarnt_status_code;
284 new_references.guarnt_status_date := x_guarnt_status_date;
285 new_references.lend_apprv_denied_code := x_lend_apprv_denied_code;
286 new_references.lend_apprv_denied_date := x_lend_apprv_denied_date;
287 new_references.lend_status_code := x_lend_status_code;
288 new_references.lend_status_date := x_lend_status_date;
289 new_references.grade_level_code := x_grade_level_code;
290 new_references.enrollment_code := x_enrollment_code;
291 new_references.anticip_compl_date := x_anticip_compl_date;
292 new_references.borw_lender_id := x_borw_lender_id;
293 new_references.guarantor_id := x_guarantor_id;
294 new_references.prc_type_code := x_prc_type_code;
295 new_references.rec_type_ind := x_rec_type_ind;
296 new_references.cl_loan_type := x_cl_loan_type;
297 new_references.cl_seq_number := x_cl_seq_number;
298 new_references.last_resort_lender := x_last_resort_lender;
299 new_references.lender_id := x_lender_id;
300 new_references.lend_non_ed_brc_id := x_lend_non_ed_brc_id;
301 new_references.recipient_id := x_recipient_id;
302 new_references.recipient_type := x_recipient_type;
303 new_references.recip_non_ed_brc_id := x_recip_non_ed_brc_id;
304 new_references.cl_rec_status := x_cl_rec_status;
305 new_references.cl_rec_status_last_update := x_cl_rec_status_last_update;
306 new_references.alt_prog_type_code := x_alt_prog_type_code;
310 new_references.resp_to_orig_code := x_resp_to_orig_code;
307 new_references.alt_appl_ver_code := x_alt_appl_ver_code;
308 new_references.borw_outstd_loan_code := x_borw_outstd_loan_code;
309 new_references.mpn_confirm_code := x_mpn_confirm_code;
311 new_references.appl_loan_phase_code := x_appl_loan_phase_code;
312 new_references.appl_loan_phase_code_chg := x_appl_loan_phase_code_chg;
313 new_references.tot_outstd_stafford := x_tot_outstd_stafford;
314 new_references.tot_outstd_plus := x_tot_outstd_plus;
315 new_references.alt_borw_tot_debt := x_alt_borw_tot_debt;
316 new_references.act_interest_rate := x_act_interest_rate;
317 new_references.service_type_code := x_service_type_code;
318 new_references.rev_notice_of_guarnt := x_rev_notice_of_guarnt;
319 new_references.sch_refund_amt := x_sch_refund_amt;
320 new_references.sch_refund_date := x_sch_refund_date;
321 new_references.uniq_layout_vend_code := x_uniq_layout_vend_code;
322 new_references.uniq_layout_ident_code := x_uniq_layout_ident_code;
323 new_references.p_person_id := x_p_person_id;
324 new_references.p_ssn := x_p_ssn;
325 new_references.p_last_name := x_p_last_name;
326 new_references.p_first_name := x_p_first_name;
327 new_references.p_middle_name := x_p_middle_name;
328 new_references.p_permt_addr1 := x_p_permt_addr1;
329 new_references.p_permt_addr2 := x_p_permt_addr2;
330 new_references.p_permt_city := x_p_permt_city;
331 new_references.p_permt_state := x_p_permt_state;
332 new_references.p_permt_zip := x_p_permt_zip;
333 new_references.p_permt_addr_chg_date := x_p_permt_addr_chg_date;
334 new_references.p_permt_phone := x_p_permt_phone;
335 new_references.p_email_addr := x_p_email_addr;
336 new_references.p_date_of_birth := x_p_date_of_birth;
337 new_references.p_license_num := x_p_license_num;
338 new_references.p_license_state := x_p_license_state;
339 new_references.p_citizenship_status := x_p_citizenship_status;
340 new_references.p_alien_reg_num := x_p_alien_reg_num;
341 new_references.p_default_status := x_p_default_status;
342 new_references.p_foreign_postal_code := x_p_foreign_postal_code;
343 new_references.p_state_of_legal_res := x_p_state_of_legal_res;
344 new_references.p_legal_res_date := x_p_legal_res_date;
345 new_references.s_ssn := x_s_ssn;
346 new_references.s_last_name := x_s_last_name;
347 new_references.s_first_name := x_s_first_name;
348 new_references.s_middle_name := x_s_middle_name;
349 new_references.s_permt_addr1 := x_s_permt_addr1;
350 new_references.s_permt_addr2 := x_s_permt_addr2;
351 new_references.s_permt_city := x_s_permt_city;
352 new_references.s_permt_state := x_s_permt_state;
353 new_references.s_permt_zip := x_s_permt_zip;
354 new_references.s_permt_addr_chg_date := x_s_permt_addr_chg_date;
355 new_references.s_permt_phone := x_s_permt_phone;
356 new_references.s_local_addr1 := x_s_local_addr1;
357 new_references.s_local_addr2 := x_s_local_addr2;
358 new_references.s_local_city := x_s_local_city;
359 new_references.s_local_state := x_s_local_state;
360 new_references.s_local_zip := x_s_local_zip;
361 new_references.s_email_addr := x_s_email_addr;
362 new_references.s_date_of_birth := x_s_date_of_birth;
363 new_references.s_license_num := x_s_license_num;
364 new_references.s_license_state := x_s_license_state;
365 new_references.s_depncy_status := x_s_depncy_status;
366 new_references.s_default_status := x_s_default_status;
367 new_references.s_citizenship_status := x_s_citizenship_status;
368 new_references.s_alien_reg_num := x_s_alien_reg_num;
369 new_references.s_foreign_postal_code := x_s_foreign_postal_code;
370 new_references.pnote_batch_id := x_pnote_batch_id;
371 new_references.pnote_ack_date := x_pnote_ack_date;
372 new_references.pnote_mpn_ind := x_pnote_mpn_ind;
373 new_references.award_id := x_award_id;
374 new_references.base_id := x_base_id;
375 new_references.loan_key_num := x_loan_key_num;
376 new_references.fin_award_year := x_fin_award_year;
377 new_references.cps_trans_num := x_cps_trans_num;
378 new_references.pymt_servicer_amt := x_pymt_servicer_amt;
379 new_references.pymt_servicer_date := x_pymt_servicer_date;
380 new_references.book_loan_amt := x_book_loan_amt;
381 new_references.book_loan_amt_date := x_book_loan_amt_date;
382 new_references.s_chg_ssn := x_s_chg_ssn;
383 new_references.s_chg_last_name := x_s_chg_last_name;
384 new_references.b_chg_ssn := x_b_chg_ssn;
385 new_references.b_chg_last_name := x_b_chg_last_name;
389 new_references.b_permt_county := x_b_permt_county;
386 new_references.note_message := x_note_message;
387 new_references.full_resp_code := x_full_resp_code;
388 new_references.s_permt_county := x_s_permt_county;
390 new_references.s_permt_country := x_s_permt_country;
391 new_references.b_permt_country := x_b_permt_country;
392 new_references.crdt_decision_status := x_crdt_decision_status;
393 new_references.b_chg_birth_date := x_b_chg_birth_date;
394 new_references.s_chg_birth_date := x_s_chg_birth_date;
395 new_references.external_loan_id_txt := x_external_loan_id_txt;
396 new_references.deferment_request_code := x_deferment_request_code;
397 new_references.eft_authorization_code := x_eft_authorization_code;
398 new_references.requested_loan_amt := x_requested_loan_amt;
399 new_references.actual_record_type_code := x_actual_record_type_code;
400 new_references.reinstatement_amt := x_reinstatement_amt;
401 new_references.lender_use_txt := x_lender_use_txt;
402 new_references.guarantor_use_txt := x_guarantor_use_txt;
403 new_references.fls_approved_amt := x_fls_approved_amt;
404 new_references.flu_approved_amt := x_flu_approved_amt;
405 new_references.flp_approved_amt := x_flp_approved_amt;
406 new_references.alt_approved_amt := x_alt_approved_amt;
407 new_references.loan_app_form_code := x_loan_app_form_code;
408 new_references.alt_borrower_ind_flag := x_alt_borrower_ind_flag;
409 new_references.school_id_txt := x_school_id_txt;
410 new_references.cost_of_attendance_amt := x_cost_of_attendance_amt;
411 new_references.EXPECT_FAMILY_CONTRIBUTE_AMT := x_EXPECT_FAMILY_CONTRIBUTE_AMT;
412 new_references.established_fin_aid_amount := x_established_fin_aid_amount;
413 new_references.BOROWER_ELECTRONIC_SIGN_FLAG := x_BOROWER_ELECTRONIC_SIGN_FLAG;
414 new_references.student_electronic_sign_flag := x_student_electronic_sign_flag;
415 new_references.BOROWER_CREDIT_AUTHORIZ_FLAG := x_BOROWER_CREDIT_AUTHORIZ_FLAG;
416 new_references.mpn_type_flag := x_mpn_type_flag;
417 new_references.school_use_txt := x_school_use_txt;
418 new_references.document_id_txt := x_document_id_txt;
419 new_references.atd_entity_id_txt := x_atd_entity_id_txt;
420 new_references.rep_entity_id_txt := x_rep_entity_id_txt;
421 new_references.source_entity_id_txt := x_source_entity_id_txt;
422 new_references.interest_rebate_percent_num := x_interest_rebate_percent_num;
423 new_references.esign_src_typ_cd := x_esign_src_typ_cd;
424 new_references.loansh_id := x_loansh_id;
425 new_references.source_txt := x_source_txt;
426
427 IF (p_action = 'UPDATE') THEN
428 new_references.creation_date := old_references.creation_date;
429 new_references.created_by := old_references.created_by;
430 ELSE
431 new_references.creation_date := x_creation_date;
432 new_references.created_by := x_created_by;
433 END IF;
434
435 new_references.last_update_date := x_last_update_date;
436 new_references.last_updated_by := x_last_updated_by;
437 new_references.last_update_login := x_last_update_login;
438
439 END set_column_values;
440
441
442 PROCEDURE before_dml (
443 p_action IN VARCHAR2,
444 x_rowid IN VARCHAR2,
445 x_loan_id IN NUMBER,
446 x_origination_id IN NUMBER,
447 x_loan_number IN VARCHAR2,
448 x_loan_type IN VARCHAR2,
449 x_loan_amt_offered IN NUMBER,
450 x_loan_amt_accepted IN NUMBER,
451 x_loan_per_begin_date IN DATE,
452 x_loan_per_end_date IN DATE,
453 x_acad_yr_begin_date IN DATE,
454 x_acad_yr_end_date IN DATE,
455 x_loan_status IN VARCHAR2,
456 x_loan_status_date IN DATE,
457 x_loan_chg_status IN VARCHAR2,
458 x_loan_chg_status_date IN DATE,
459 x_req_serial_loan_code IN VARCHAR2,
460 x_act_serial_loan_code IN VARCHAR2,
461 x_active IN VARCHAR2,
462 x_active_date IN DATE,
463 x_sch_cert_date IN DATE,
464 x_orig_status_flag IN VARCHAR2,
465 x_orig_batch_id IN VARCHAR2,
466 x_orig_batch_date IN DATE,
467 x_orig_ack_date IN DATE,
468 x_credit_override IN VARCHAR2,
469 x_credit_decision_date IN DATE,
470 x_pnote_delivery_code IN VARCHAR2,
471 x_pnote_status IN VARCHAR2,
472 x_pnote_status_date IN DATE,
473 x_pnote_id IN VARCHAR2,
474 x_pnote_print_ind IN VARCHAR2,
475 x_pnote_accept_amt IN NUMBER,
476 x_pnote_accept_date IN DATE,
480 x_unsub_elig_for_heal IN VARCHAR2,
477 x_p_signature_code IN VARCHAR2,
478 x_p_signature_date IN DATE,
479 x_s_signature_code IN VARCHAR2,
481 x_disclosure_print_ind IN VARCHAR2,
482 x_orig_fee_perct IN NUMBER,
483 x_borw_confirm_ind IN VARCHAR2,
484 x_borw_interest_ind IN VARCHAR2,
485 x_unsub_elig_for_depnt IN VARCHAR2,
486 x_guarantee_amt IN NUMBER,
487 x_guarantee_date IN DATE,
488 x_guarnt_adj_ind IN VARCHAR2,
489 x_guarnt_amt_redn_code IN VARCHAR2,
490 x_guarnt_status_code IN VARCHAR2,
491 x_guarnt_status_date IN DATE,
492 x_lend_apprv_denied_code IN VARCHAR2,
493 x_lend_apprv_denied_date IN DATE,
494 x_lend_status_code IN VARCHAR2,
495 x_lend_status_date IN DATE,
496 x_grade_level_code IN VARCHAR2,
497 x_enrollment_code IN VARCHAR2,
498 x_anticip_compl_date IN DATE,
499 x_borw_lender_id IN VARCHAR2,
500 x_guarantor_id IN VARCHAR2,
501 x_prc_type_code IN VARCHAR2,
502 x_rec_type_ind IN VARCHAR2,
503 x_cl_loan_type IN VARCHAR2,
504 x_cl_seq_number IN NUMBER,
505 x_last_resort_lender IN VARCHAR2,
506 x_lender_id IN VARCHAR2,
507 x_lend_non_ed_brc_id IN VARCHAR2,
508 x_recipient_id IN VARCHAR2,
509 x_recipient_type IN VARCHAR2,
510 x_recip_non_ed_brc_id IN VARCHAR2,
511 x_cl_rec_status IN VARCHAR2,
512 x_cl_rec_status_last_update IN DATE,
513 x_alt_prog_type_code IN VARCHAR2,
514 x_alt_appl_ver_code IN NUMBER,
515 x_borw_outstd_loan_code IN VARCHAR2,
516 x_mpn_confirm_code IN VARCHAR2,
517 x_resp_to_orig_code IN VARCHAR2,
518 x_appl_loan_phase_code IN VARCHAR2,
519 x_appl_loan_phase_code_chg IN DATE,
520 x_tot_outstd_stafford IN NUMBER,
521 x_tot_outstd_plus IN NUMBER,
522 x_alt_borw_tot_debt IN NUMBER,
523 x_act_interest_rate IN NUMBER,
524 x_service_type_code IN VARCHAR2,
525 x_rev_notice_of_guarnt IN VARCHAR2,
526 x_sch_refund_amt IN NUMBER,
527 x_sch_refund_date IN DATE,
528 x_uniq_layout_vend_code IN VARCHAR2,
529 x_uniq_layout_ident_code IN VARCHAR2,
530 x_p_person_id IN NUMBER,
531 x_p_ssn IN VARCHAR2,
532 x_p_last_name IN VARCHAR2,
533 x_p_first_name IN VARCHAR2,
534 x_p_middle_name IN VARCHAR2,
535 x_p_permt_addr1 IN VARCHAR2,
536 x_p_permt_addr2 IN VARCHAR2,
537 x_p_permt_city IN VARCHAR2,
538 x_p_permt_state IN VARCHAR2,
539 x_p_permt_zip IN VARCHAR2,
540 x_p_permt_addr_chg_date IN DATE,
541 x_p_permt_phone IN VARCHAR2,
542 x_p_email_addr IN VARCHAR2,
543 x_p_date_of_birth IN DATE,
544 x_p_license_num IN VARCHAR2,
545 x_p_license_state IN VARCHAR2,
546 x_p_citizenship_status IN VARCHAR2,
547 x_p_alien_reg_num IN VARCHAR2,
548 x_p_default_status IN VARCHAR2,
549 x_p_foreign_postal_code IN VARCHAR2,
550 x_p_state_of_legal_res IN VARCHAR2,
551 x_p_legal_res_date IN DATE,
552 x_s_ssn IN VARCHAR2,
553 x_s_last_name IN VARCHAR2,
554 x_s_first_name IN VARCHAR2,
555 x_s_middle_name IN VARCHAR2,
556 x_s_permt_addr1 IN VARCHAR2,
557 x_s_permt_addr2 IN VARCHAR2,
558 x_s_permt_city IN VARCHAR2,
559 x_s_permt_state IN VARCHAR2,
560 x_s_permt_zip IN VARCHAR2,
561 x_s_permt_addr_chg_date IN DATE,
562 x_s_permt_phone IN VARCHAR2,
563 x_s_local_addr1 IN VARCHAR2,
564 x_s_local_addr2 IN VARCHAR2,
565 x_s_local_city IN VARCHAR2,
566 x_s_local_state IN VARCHAR2,
567 x_s_local_zip IN VARCHAR2,
568 x_s_email_addr IN VARCHAR2,
569 x_s_date_of_birth IN DATE,
570 x_s_license_num IN VARCHAR2,
571 x_s_license_state IN VARCHAR2,
572 x_s_depncy_status IN VARCHAR2,
573 x_s_default_status IN VARCHAR2,
574 x_s_citizenship_status IN VARCHAR2,
578 x_pnote_ack_date IN DATE,
575 x_s_alien_reg_num IN VARCHAR2,
576 x_s_foreign_postal_code IN VARCHAR2,
577 x_pnote_batch_id IN VARCHAR2,
579 x_pnote_mpn_ind IN VARCHAR2,
580 x_award_id IN NUMBER,
581 x_base_id IN NUMBER,
582 x_loan_key_num IN NUMBER,
583 x_fin_award_year IN VARCHAR2,
584 x_cps_trans_num IN NUMBER,
585 x_pymt_servicer_amt IN NUMBER,
586 x_pymt_servicer_date IN DATE,
587 x_book_loan_amt IN NUMBER,
588 x_book_loan_amt_date IN DATE,
589 x_s_chg_ssn IN VARCHAR2,
590 x_s_chg_last_name IN VARCHAR2,
591 x_b_chg_ssn IN VARCHAR2,
592 x_b_chg_last_name IN VARCHAR2,
593 x_note_message IN VARCHAR2,
594 x_full_resp_code IN VARCHAR2,
595 x_s_permt_county IN VARCHAR2,
596 x_b_permt_county IN VARCHAR2,
597 x_s_permt_country IN VARCHAR2,
598 x_b_permt_country IN VARCHAR2,
599 x_crdt_decision_status IN VARCHAR2,
600 x_b_chg_birth_date IN DATE,
601 x_s_chg_birth_date IN DATE,
602 x_external_loan_id_txt IN VARCHAR2,
603 x_deferment_request_code IN VARCHAR2,
604 x_eft_authorization_code IN VARCHAR2,
605 x_requested_loan_amt IN NUMBER,
606 x_actual_record_type_code IN VARCHAR2,
607 x_reinstatement_amt IN NUMBER,
608 x_lender_use_txt IN VARCHAR2,
609 x_guarantor_use_txt IN VARCHAR2,
610 x_fls_approved_amt IN NUMBER,
611 x_flu_approved_amt IN NUMBER,
612 x_flp_approved_amt IN NUMBER,
613 x_alt_approved_amt IN NUMBER,
614 x_loan_app_form_code IN VARCHAR2,
615 x_alt_borrower_ind_flag IN VARCHAR2,
616 x_school_id_txt IN VARCHAR2,
617 x_cost_of_attendance_amt IN NUMBER,
618 x_EXPECT_FAMILY_CONTRIBUTE_AMT IN NUMBER,
619 x_established_fin_aid_amount IN NUMBER,
620 x_BOROWER_ELECTRONIC_SIGN_FLAG IN VARCHAR2,
621 x_student_electronic_sign_flag IN VARCHAR2,
622 x_BOROWER_CREDIT_AUTHORIZ_FLAG IN VARCHAR2,
623 x_mpn_type_flag IN VARCHAR2,
624 x_school_use_txt IN VARCHAR2,
625 x_document_id_txt IN VARCHAR2,
626 x_atd_entity_id_txt IN VARCHAR2,
627 x_rep_entity_id_txt IN VARCHAR2,
628 x_source_entity_id_txt IN VARCHAR2,
629 x_interest_rebate_percent_num IN NUMBER,
630 x_esign_src_typ_cd IN VARCHAR2,
631 x_loansh_id IN NUMBER,
632 x_source_txt IN VARCHAR2,
633 x_creation_date IN DATE,
634 x_created_by IN NUMBER,
635 x_last_update_date IN DATE,
636 x_last_updated_by IN NUMBER,
637 x_last_update_login IN NUMBER
638 ) AS
639 /*
640 || Created By : [email protected]
641 || Created On : 03-NOV-2004
642 || Purpose : Initialises the columns, Checks Constraints, Calls the
643 || Trigger Handlers for the table, before any DML operation.
644 || Known limitations, enhancements or remarks :
645 || Change History :
646 || Who When What
647 || (reverse chronological order - newest change first)
648 */
649 BEGIN
650
651 set_column_values (
652 p_action,
653 x_rowid,
654 x_loan_id,
655 x_origination_id,
656 x_loan_number,
657 x_loan_type,
658 x_loan_amt_offered,
659 x_loan_amt_accepted,
660 x_loan_per_begin_date,
661 x_loan_per_end_date,
662 x_acad_yr_begin_date,
663 x_acad_yr_end_date,
664 x_loan_status,
665 x_loan_status_date,
666 x_loan_chg_status,
667 x_loan_chg_status_date,
668 x_req_serial_loan_code,
669 x_act_serial_loan_code,
670 x_active,
671 x_active_date,
672 x_sch_cert_date,
673 x_orig_status_flag,
674 x_orig_batch_id,
675 x_orig_batch_date,
676 x_orig_ack_date,
677 x_credit_override,
678 x_credit_decision_date,
679 x_pnote_delivery_code,
680 x_pnote_status,
681 x_pnote_status_date,
682 x_pnote_id,
683 x_pnote_print_ind,
684 x_pnote_accept_amt,
685 x_pnote_accept_date,
686 x_p_signature_code,
687 x_p_signature_date,
688 x_s_signature_code,
689 x_unsub_elig_for_heal,
690 x_disclosure_print_ind,
694 x_unsub_elig_for_depnt,
691 x_orig_fee_perct,
692 x_borw_confirm_ind,
693 x_borw_interest_ind,
695 x_guarantee_amt,
696 x_guarantee_date,
697 x_guarnt_adj_ind,
698 x_guarnt_amt_redn_code,
699 x_guarnt_status_code,
700 x_guarnt_status_date,
701 x_lend_apprv_denied_code,
702 x_lend_apprv_denied_date,
703 x_lend_status_code,
704 x_lend_status_date,
705 x_grade_level_code,
706 x_enrollment_code,
707 x_anticip_compl_date,
708 x_borw_lender_id,
709 x_guarantor_id,
710 x_prc_type_code,
711 x_rec_type_ind,
712 x_cl_loan_type,
713 x_cl_seq_number,
714 x_last_resort_lender,
715 x_lender_id,
716 x_lend_non_ed_brc_id,
717 x_recipient_id,
718 x_recipient_type,
719 x_recip_non_ed_brc_id,
720 x_cl_rec_status,
721 x_cl_rec_status_last_update,
722 x_alt_prog_type_code,
723 x_alt_appl_ver_code,
724 x_borw_outstd_loan_code,
725 x_mpn_confirm_code,
726 x_resp_to_orig_code,
727 x_appl_loan_phase_code,
728 x_appl_loan_phase_code_chg,
729 x_tot_outstd_stafford,
730 x_tot_outstd_plus,
731 x_alt_borw_tot_debt,
732 x_act_interest_rate,
733 x_service_type_code,
734 x_rev_notice_of_guarnt,
735 x_sch_refund_amt,
736 x_sch_refund_date,
737 x_uniq_layout_vend_code,
738 x_uniq_layout_ident_code,
739 x_p_person_id,
740 x_p_ssn,
741 x_p_last_name,
742 x_p_first_name,
743 x_p_middle_name,
744 x_p_permt_addr1,
745 x_p_permt_addr2,
746 x_p_permt_city,
747 x_p_permt_state,
748 x_p_permt_zip,
749 x_p_permt_addr_chg_date,
750 x_p_permt_phone,
751 x_p_email_addr,
752 x_p_date_of_birth,
753 x_p_license_num,
754 x_p_license_state,
755 x_p_citizenship_status,
756 x_p_alien_reg_num,
757 x_p_default_status,
758 x_p_foreign_postal_code,
759 x_p_state_of_legal_res,
760 x_p_legal_res_date,
761 x_s_ssn,
762 x_s_last_name,
763 x_s_first_name,
764 x_s_middle_name,
765 x_s_permt_addr1,
766 x_s_permt_addr2,
767 x_s_permt_city,
768 x_s_permt_state,
769 x_s_permt_zip,
770 x_s_permt_addr_chg_date,
771 x_s_permt_phone,
772 x_s_local_addr1,
773 x_s_local_addr2,
774 x_s_local_city,
775 x_s_local_state,
776 x_s_local_zip,
777 x_s_email_addr,
778 x_s_date_of_birth,
779 x_s_license_num,
780 x_s_license_state,
781 x_s_depncy_status,
782 x_s_default_status,
783 x_s_citizenship_status,
784 x_s_alien_reg_num,
785 x_s_foreign_postal_code,
786 x_pnote_batch_id,
787 x_pnote_ack_date,
788 x_pnote_mpn_ind,
789 x_award_id,
790 x_base_id,
791 x_loan_key_num,
792 x_fin_award_year,
793 x_cps_trans_num,
794 x_pymt_servicer_amt,
795 x_pymt_servicer_date,
796 x_book_loan_amt,
797 x_book_loan_amt_date,
798 x_s_chg_ssn,
799 x_s_chg_last_name,
800 x_b_chg_ssn,
801 x_b_chg_last_name,
802 x_note_message,
803 x_full_resp_code,
804 x_s_permt_county,
805 x_b_permt_county,
806 x_s_permt_country,
807 x_b_permt_country,
808 x_crdt_decision_status,
809 x_b_chg_birth_date,
810 x_s_chg_birth_date,
811 x_external_loan_id_txt,
812 x_deferment_request_code,
813 x_eft_authorization_code,
814 x_requested_loan_amt,
815 x_actual_record_type_code,
816 x_reinstatement_amt,
817 x_lender_use_txt,
818 x_guarantor_use_txt,
819 x_fls_approved_amt,
820 x_flu_approved_amt,
821 x_flp_approved_amt,
822 x_alt_approved_amt,
823 x_loan_app_form_code,
824 x_alt_borrower_ind_flag,
825 x_school_id_txt,
826 x_cost_of_attendance_amt,
827 x_EXPECT_FAMILY_CONTRIBUTE_AMT,
828 x_established_fin_aid_amount,
829 x_BOROWER_ELECTRONIC_SIGN_FLAG,
830 x_student_electronic_sign_flag,
831 x_BOROWER_CREDIT_AUTHORIZ_FLAG,
832 x_mpn_type_flag,
833 x_school_use_txt,
834 x_document_id_txt,
835 x_atd_entity_id_txt,
836 x_rep_entity_id_txt,
837 x_source_entity_id_txt,
838 x_interest_rebate_percent_num,
839 x_esign_src_typ_cd,
840 x_loansh_id,
841 x_source_txt,
842 x_creation_date,
843 x_created_by,
844 x_last_update_date,
845 x_last_updated_by,
846 x_last_update_login
847 );
848
849 IF (p_action = 'INSERT') THEN
850 -- Call all the procedures related to Before Insert.
851 IF ( get_pk_for_validation( new_references.loansh_id
852
853 )
854 ) THEN
855 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
856 igs_ge_msg_stack.add;
857 app_exception.raise_exception;
858 END IF;
859 ELSIF (p_action = 'VALIDATE_INSERT') THEN
860 -- Call all the procedures related to Before Insert.
864 ) THEN
861 IF ( get_pk_for_validation ( new_references.loansh_id
862
863 )
865 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
866 igs_ge_msg_stack.add;
867 app_exception.raise_exception;
868 END IF;
869 END IF;
870
871 END before_dml;
872
873 FUNCTION get_pk_for_validation (
874 x_loansh_id IN NUMBER
875 ) RETURN BOOLEAN AS
876 /*
877 || Created By : [email protected]
878 || Created On : 21-OCT-2004
879 || Purpose : Validates the Primary Key of the table.
880 || Known limitations, enhancements or remarks :
881 || Change History :
882 || Who When What
883 || (reverse chronological order - newest change first)
884 */
885 CURSOR cur_rowid IS
886 SELECT rowid
887 FROM igf_sl_lor_loc_history
888 WHERE loansh_id = x_loansh_id
889 FOR UPDATE NOWAIT;
890
891 lv_rowid cur_rowid%RowType;
892
893 BEGIN
894
895 OPEN cur_rowid;
896 FETCH cur_rowid INTO lv_rowid;
897 IF (cur_rowid%FOUND) THEN
898 CLOSE cur_rowid;
899 RETURN(TRUE);
900 ELSE
901 CLOSE cur_rowid;
902 RETURN(FALSE);
903 END IF;
904
905 END get_pk_for_validation;
906
907
908 PROCEDURE insert_row (
909 x_rowid IN OUT NOCOPY VARCHAR2,
910 x_loan_id IN NUMBER,
911 x_origination_id IN NUMBER,
912 x_loan_number IN VARCHAR2,
913 x_loan_type IN VARCHAR2,
914 x_loan_amt_offered IN NUMBER,
915 x_loan_amt_accepted IN NUMBER,
916 x_loan_per_begin_date IN DATE,
917 x_loan_per_end_date IN DATE,
918 x_acad_yr_begin_date IN DATE,
919 x_acad_yr_end_date IN DATE,
920 x_loan_status IN VARCHAR2,
921 x_loan_status_date IN DATE,
922 x_loan_chg_status IN VARCHAR2,
923 x_loan_chg_status_date IN DATE,
924 x_req_serial_loan_code IN VARCHAR2,
925 x_act_serial_loan_code IN VARCHAR2,
926 x_active IN VARCHAR2,
927 x_active_date IN DATE,
928 x_sch_cert_date IN DATE,
929 x_orig_status_flag IN VARCHAR2,
930 x_orig_batch_id IN VARCHAR2,
931 x_orig_batch_date IN DATE,
932 x_orig_ack_date IN DATE,
933 x_credit_override IN VARCHAR2,
934 x_credit_decision_date IN DATE,
935 x_pnote_delivery_code IN VARCHAR2,
936 x_pnote_status IN VARCHAR2,
937 x_pnote_status_date IN DATE,
938 x_pnote_id IN VARCHAR2,
939 x_pnote_print_ind IN VARCHAR2,
940 x_pnote_accept_amt IN NUMBER,
941 x_pnote_accept_date IN DATE,
942 x_p_signature_code IN VARCHAR2,
943 x_p_signature_date IN DATE,
944 x_s_signature_code IN VARCHAR2,
945 x_unsub_elig_for_heal IN VARCHAR2,
946 x_disclosure_print_ind IN VARCHAR2,
947 x_orig_fee_perct IN NUMBER,
948 x_borw_confirm_ind IN VARCHAR2,
949 x_borw_interest_ind IN VARCHAR2,
950 x_unsub_elig_for_depnt IN VARCHAR2,
951 x_guarantee_amt IN NUMBER,
952 x_guarantee_date IN DATE,
953 x_guarnt_adj_ind IN VARCHAR2,
954 x_guarnt_amt_redn_code IN VARCHAR2,
955 x_guarnt_status_code IN VARCHAR2,
956 x_guarnt_status_date IN DATE,
957 x_lend_apprv_denied_code IN VARCHAR2,
958 x_lend_apprv_denied_date IN DATE,
959 x_lend_status_code IN VARCHAR2,
960 x_lend_status_date IN DATE,
961 x_grade_level_code IN VARCHAR2,
962 x_enrollment_code IN VARCHAR2,
963 x_anticip_compl_date IN DATE,
964 x_borw_lender_id IN VARCHAR2,
965 x_guarantor_id IN VARCHAR2,
966 x_prc_type_code IN VARCHAR2,
967 x_rec_type_ind IN VARCHAR2,
968 x_cl_loan_type IN VARCHAR2,
969 x_cl_seq_number IN NUMBER,
970 x_last_resort_lender IN VARCHAR2,
971 x_lender_id IN VARCHAR2,
972 x_lend_non_ed_brc_id IN VARCHAR2,
973 x_recipient_id IN VARCHAR2,
974 x_recipient_type IN VARCHAR2,
975 x_recip_non_ed_brc_id IN VARCHAR2,
976 x_cl_rec_status IN VARCHAR2,
977 x_cl_rec_status_last_update IN DATE,
978 x_alt_prog_type_code IN VARCHAR2,
979 x_alt_appl_ver_code IN NUMBER,
980 x_borw_outstd_loan_code IN VARCHAR2,
981 x_mpn_confirm_code IN VARCHAR2,
982 x_resp_to_orig_code IN VARCHAR2,
983 x_appl_loan_phase_code IN VARCHAR2,
987 x_alt_borw_tot_debt IN NUMBER,
984 x_appl_loan_phase_code_chg IN DATE,
985 x_tot_outstd_stafford IN NUMBER,
986 x_tot_outstd_plus IN NUMBER,
988 x_act_interest_rate IN NUMBER,
989 x_service_type_code IN VARCHAR2,
990 x_rev_notice_of_guarnt IN VARCHAR2,
991 x_sch_refund_amt IN NUMBER,
992 x_sch_refund_date IN DATE,
993 x_uniq_layout_vend_code IN VARCHAR2,
994 x_uniq_layout_ident_code IN VARCHAR2,
995 x_p_person_id IN NUMBER,
996 x_p_ssn IN VARCHAR2,
997 x_p_last_name IN VARCHAR2,
998 x_p_first_name IN VARCHAR2,
999 x_p_middle_name IN VARCHAR2,
1000 x_p_permt_addr1 IN VARCHAR2,
1001 x_p_permt_addr2 IN VARCHAR2,
1002 x_p_permt_city IN VARCHAR2,
1003 x_p_permt_state IN VARCHAR2,
1004 x_p_permt_zip IN VARCHAR2,
1005 x_p_permt_addr_chg_date IN DATE,
1006 x_p_permt_phone IN VARCHAR2,
1007 x_p_email_addr IN VARCHAR2,
1008 x_p_date_of_birth IN DATE,
1009 x_p_license_num IN VARCHAR2,
1010 x_p_license_state IN VARCHAR2,
1011 x_p_citizenship_status IN VARCHAR2,
1012 x_p_alien_reg_num IN VARCHAR2,
1013 x_p_default_status IN VARCHAR2,
1014 x_p_foreign_postal_code IN VARCHAR2,
1015 x_p_state_of_legal_res IN VARCHAR2,
1016 x_p_legal_res_date IN DATE,
1017 x_s_ssn IN VARCHAR2,
1018 x_s_last_name IN VARCHAR2,
1019 x_s_first_name IN VARCHAR2,
1020 x_s_middle_name IN VARCHAR2,
1021 x_s_permt_addr1 IN VARCHAR2,
1022 x_s_permt_addr2 IN VARCHAR2,
1023 x_s_permt_city IN VARCHAR2,
1024 x_s_permt_state IN VARCHAR2,
1025 x_s_permt_zip IN VARCHAR2,
1026 x_s_permt_addr_chg_date IN DATE,
1027 x_s_permt_phone IN VARCHAR2,
1028 x_s_local_addr1 IN VARCHAR2,
1029 x_s_local_addr2 IN VARCHAR2,
1030 x_s_local_city IN VARCHAR2,
1031 x_s_local_state IN VARCHAR2,
1032 x_s_local_zip IN VARCHAR2,
1033 x_s_email_addr IN VARCHAR2,
1034 x_s_date_of_birth IN DATE,
1035 x_s_license_num IN VARCHAR2,
1036 x_s_license_state IN VARCHAR2,
1037 x_s_depncy_status IN VARCHAR2,
1038 x_s_default_status IN VARCHAR2,
1039 x_s_citizenship_status IN VARCHAR2,
1040 x_s_alien_reg_num IN VARCHAR2,
1041 x_s_foreign_postal_code IN VARCHAR2,
1042 x_pnote_batch_id IN VARCHAR2,
1043 x_pnote_ack_date IN DATE,
1044 x_pnote_mpn_ind IN VARCHAR2,
1045 x_award_id IN NUMBER,
1046 x_base_id IN NUMBER,
1047 x_loan_key_num IN NUMBER,
1048 x_fin_award_year IN VARCHAR2,
1049 x_cps_trans_num IN NUMBER,
1050 x_pymt_servicer_amt IN NUMBER,
1051 x_pymt_servicer_date IN DATE,
1052 x_book_loan_amt IN NUMBER,
1053 x_book_loan_amt_date IN DATE,
1054 x_s_chg_ssn IN VARCHAR2,
1055 x_s_chg_last_name IN VARCHAR2,
1056 x_b_chg_ssn IN VARCHAR2,
1057 x_b_chg_last_name IN VARCHAR2,
1058 x_note_message IN VARCHAR2,
1059 x_full_resp_code IN VARCHAR2,
1060 x_s_permt_county IN VARCHAR2,
1061 x_b_permt_county IN VARCHAR2,
1062 x_s_permt_country IN VARCHAR2,
1063 x_b_permt_country IN VARCHAR2,
1064 x_crdt_decision_status IN VARCHAR2,
1065 x_b_chg_birth_date IN DATE,
1066 x_s_chg_birth_date IN DATE,
1067 x_external_loan_id_txt IN VARCHAR2,
1068 x_deferment_request_code IN VARCHAR2,
1069 x_eft_authorization_code IN VARCHAR2,
1070 x_requested_loan_amt IN NUMBER,
1071 x_actual_record_type_code IN VARCHAR2,
1072 x_reinstatement_amt IN NUMBER,
1073 x_lender_use_txt IN VARCHAR2,
1074 x_guarantor_use_txt IN VARCHAR2,
1075 x_fls_approved_amt IN NUMBER,
1076 x_flu_approved_amt IN NUMBER,
1077 x_flp_approved_amt IN NUMBER,
1078 x_alt_approved_amt IN NUMBER,
1079 x_loan_app_form_code IN VARCHAR2,
1080 x_alt_borrower_ind_flag IN VARCHAR2,
1081 x_school_id_txt IN VARCHAR2,
1082 x_cost_of_attendance_amt IN NUMBER,
1083 x_EXPECT_FAMILY_CONTRIBUTE_AMT IN NUMBER,
1084 x_established_fin_aid_amount IN NUMBER,
1088 x_mpn_type_flag IN VARCHAR2,
1085 x_BOROWER_ELECTRONIC_SIGN_FLAG IN VARCHAR2,
1086 x_student_electronic_sign_flag IN VARCHAR2,
1087 x_BOROWER_CREDIT_AUTHORIZ_FLAG IN VARCHAR2,
1089 x_school_use_txt IN VARCHAR2,
1090 x_document_id_txt IN VARCHAR2,
1091 x_atd_entity_id_txt IN VARCHAR2,
1092 x_rep_entity_id_txt IN VARCHAR2,
1093 x_source_entity_id_txt IN VARCHAR2,
1094 x_interest_rebate_percent_num IN NUMBER,
1095 x_esign_src_typ_cd IN VARCHAR2,
1096 x_loansh_id IN OUT NOCOPY NUMBER,
1097 x_source_txt IN VARCHAR2,
1098 x_mode IN VARCHAR2
1099 ) AS
1100 /*
1101 || Created By : [email protected]
1102 || Created On : 03-NOV-2004
1103 || Purpose : Handles the INSERT DML logic for the table.
1104 || Known limitations, enhancements or remarks :
1105 || Change History :
1106 || Who When What
1107 || (reverse chronological order - newest change first)
1108 */
1109
1110 x_last_update_date DATE;
1111 x_last_updated_by NUMBER;
1112 x_last_update_login NUMBER;
1113 x_request_id NUMBER;
1114 x_program_id NUMBER;
1115 x_program_application_id NUMBER;
1116 x_program_update_date DATE;
1117
1118 BEGIN
1119
1120 x_last_update_date := SYSDATE;
1121 IF (x_mode = 'I') THEN
1122 x_last_updated_by := 1;
1123 x_last_update_login := 0;
1124 ELSIF (x_mode = 'R') THEN
1125 x_last_updated_by := fnd_global.user_id;
1126 IF (x_last_updated_by IS NULL) THEN
1127 x_last_updated_by := -1;
1128 END IF;
1129 x_last_update_login := fnd_global.login_id;
1130 IF (x_last_update_login IS NULL) THEN
1131 x_last_update_login := -1;
1132 END IF;
1133 x_request_id := fnd_global.conc_request_id;
1134 x_program_id := fnd_global.conc_program_id;
1135 x_program_application_id := fnd_global.prog_appl_id;
1136
1137 IF (x_request_id = -1) THEN
1138 x_request_id := NULL;
1139 x_program_id := NULL;
1140 x_program_application_id := NULL;
1141 x_program_update_date := NULL;
1142 ELSE
1143 x_program_update_date := SYSDATE;
1144 END IF;
1145 ELSE
1146 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1147 fnd_message.set_token ('ROUTINE', 'igf_sl_lor_loc_history_PKG.INSERT_ROW');
1148 igs_ge_msg_stack.add;
1149 app_exception.raise_exception;
1150 END IF;
1151
1152
1153 SELECT igf_sl_lor_loc_history_s.NEXTVAL INTO x_loansh_id FROM dual;
1154
1155 before_dml(
1156 p_action => 'INSERT',
1157 x_rowid => x_rowid,
1158 x_loan_id => x_loan_id,
1159 x_origination_id => x_origination_id,
1160 x_loan_number => x_loan_number,
1161 x_loan_type => x_loan_type,
1162 x_loan_amt_offered => x_loan_amt_offered,
1163 x_loan_amt_accepted => x_loan_amt_accepted,
1164 x_loan_per_begin_date => x_loan_per_begin_date,
1165 x_loan_per_end_date => x_loan_per_end_date,
1166 x_acad_yr_begin_date => x_acad_yr_begin_date,
1167 x_acad_yr_end_date => x_acad_yr_end_date,
1168 x_loan_status => x_loan_status,
1169 x_loan_status_date => x_loan_status_date,
1170 x_loan_chg_status => x_loan_chg_status,
1171 x_loan_chg_status_date => x_loan_chg_status_date,
1172 x_req_serial_loan_code => x_req_serial_loan_code,
1173 x_act_serial_loan_code => x_act_serial_loan_code,
1174 x_active => x_active,
1175 x_active_date => x_active_date,
1176 x_sch_cert_date => x_sch_cert_date,
1177 x_orig_status_flag => x_orig_status_flag,
1178 x_orig_batch_id => x_orig_batch_id,
1179 x_orig_batch_date => x_orig_batch_date,
1180 x_orig_ack_date => x_orig_ack_date,
1181 x_credit_override => x_credit_override,
1182 x_credit_decision_date => x_credit_decision_date,
1183 x_pnote_delivery_code => x_pnote_delivery_code,
1184 x_pnote_status => x_pnote_status,
1185 x_pnote_status_date => x_pnote_status_date,
1186 x_pnote_id => x_pnote_id,
1187 x_pnote_print_ind => x_pnote_print_ind,
1188 x_pnote_accept_amt => x_pnote_accept_amt,
1189 x_pnote_accept_date => x_pnote_accept_date,
1190 x_p_signature_code => x_p_signature_code,
1191 x_p_signature_date => x_p_signature_date,
1192 x_s_signature_code => x_s_signature_code,
1193 x_unsub_elig_for_heal => x_unsub_elig_for_heal,
1197 x_borw_interest_ind => x_borw_interest_ind,
1194 x_disclosure_print_ind => x_disclosure_print_ind,
1195 x_orig_fee_perct => x_orig_fee_perct,
1196 x_borw_confirm_ind => x_borw_confirm_ind,
1198 x_unsub_elig_for_depnt => x_unsub_elig_for_depnt,
1199 x_guarantee_amt => x_guarantee_amt,
1200 x_guarantee_date => x_guarantee_date,
1201 x_guarnt_adj_ind => x_guarnt_adj_ind,
1202 x_guarnt_amt_redn_code => x_guarnt_amt_redn_code,
1203 x_guarnt_status_code => x_guarnt_status_code,
1204 x_guarnt_status_date => x_guarnt_status_date,
1205 x_lend_apprv_denied_code => x_lend_apprv_denied_code,
1206 x_lend_apprv_denied_date => x_lend_apprv_denied_date,
1207 x_lend_status_code => x_lend_status_code,
1208 x_lend_status_date => x_lend_status_date,
1209 x_grade_level_code => x_grade_level_code,
1210 x_enrollment_code => x_enrollment_code,
1211 x_anticip_compl_date => x_anticip_compl_date,
1212 x_borw_lender_id => x_borw_lender_id,
1213 x_guarantor_id => x_guarantor_id,
1214 x_prc_type_code => x_prc_type_code,
1215 x_rec_type_ind => x_rec_type_ind,
1216 x_cl_loan_type => x_cl_loan_type,
1217 x_cl_seq_number => x_cl_seq_number,
1218 x_last_resort_lender => x_last_resort_lender,
1219 x_lender_id => x_lender_id,
1220 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
1221 x_recipient_id => x_recipient_id,
1222 x_recipient_type => x_recipient_type,
1223 x_recip_non_ed_brc_id => x_recip_non_ed_brc_id,
1224 x_cl_rec_status => x_cl_rec_status,
1225 x_cl_rec_status_last_update => x_cl_rec_status_last_update,
1226 x_alt_prog_type_code => x_alt_prog_type_code,
1227 x_alt_appl_ver_code => x_alt_appl_ver_code,
1228 x_borw_outstd_loan_code => x_borw_outstd_loan_code,
1229 x_mpn_confirm_code => x_mpn_confirm_code,
1230 x_resp_to_orig_code => x_resp_to_orig_code,
1231 x_appl_loan_phase_code => x_appl_loan_phase_code,
1232 x_appl_loan_phase_code_chg => x_appl_loan_phase_code_chg,
1233 x_tot_outstd_stafford => x_tot_outstd_stafford,
1234 x_tot_outstd_plus => x_tot_outstd_plus,
1235 x_alt_borw_tot_debt => x_alt_borw_tot_debt,
1236 x_act_interest_rate => x_act_interest_rate,
1237 x_service_type_code => x_service_type_code,
1238 x_rev_notice_of_guarnt => x_rev_notice_of_guarnt,
1239 x_sch_refund_amt => x_sch_refund_amt,
1240 x_sch_refund_date => x_sch_refund_date,
1241 x_uniq_layout_vend_code => x_uniq_layout_vend_code,
1242 x_uniq_layout_ident_code => x_uniq_layout_ident_code,
1243 x_p_person_id => x_p_person_id,
1244 x_p_ssn => x_p_ssn,
1245 x_p_last_name => x_p_last_name,
1246 x_p_first_name => x_p_first_name,
1247 x_p_middle_name => x_p_middle_name,
1248 x_p_permt_addr1 => x_p_permt_addr1,
1249 x_p_permt_addr2 => x_p_permt_addr2,
1250 x_p_permt_city => x_p_permt_city,
1251 x_p_permt_state => x_p_permt_state,
1252 x_p_permt_zip => x_p_permt_zip,
1253 x_p_permt_addr_chg_date => x_p_permt_addr_chg_date,
1254 x_p_permt_phone => x_p_permt_phone,
1255 x_p_email_addr => x_p_email_addr,
1256 x_p_date_of_birth => x_p_date_of_birth,
1257 x_p_license_num => x_p_license_num,
1258 x_p_license_state => x_p_license_state,
1259 x_p_citizenship_status => x_p_citizenship_status,
1260 x_p_alien_reg_num => x_p_alien_reg_num,
1261 x_p_default_status => x_p_default_status,
1262 x_p_foreign_postal_code => x_p_foreign_postal_code,
1263 x_p_state_of_legal_res => x_p_state_of_legal_res,
1264 x_p_legal_res_date => x_p_legal_res_date,
1265 x_s_ssn => x_s_ssn,
1266 x_s_last_name => x_s_last_name,
1267 x_s_first_name => x_s_first_name,
1268 x_s_middle_name => x_s_middle_name,
1269 x_s_permt_addr1 => x_s_permt_addr1,
1270 x_s_permt_addr2 => x_s_permt_addr2,
1271 x_s_permt_city => x_s_permt_city,
1272 x_s_permt_state => x_s_permt_state,
1273 x_s_permt_zip => x_s_permt_zip,
1274 x_s_permt_addr_chg_date => x_s_permt_addr_chg_date,
1275 x_s_permt_phone => x_s_permt_phone,
1276 x_s_local_addr1 => x_s_local_addr1,
1277 x_s_local_addr2 => x_s_local_addr2,
1278 x_s_local_city => x_s_local_city,
1279 x_s_local_state => x_s_local_state,
1280 x_s_local_zip => x_s_local_zip,
1284 x_s_license_state => x_s_license_state,
1281 x_s_email_addr => x_s_email_addr,
1282 x_s_date_of_birth => x_s_date_of_birth,
1283 x_s_license_num => x_s_license_num,
1285 x_s_depncy_status => x_s_depncy_status,
1286 x_s_default_status => x_s_default_status,
1287 x_s_citizenship_status => x_s_citizenship_status,
1288 x_s_alien_reg_num => x_s_alien_reg_num,
1289 x_s_foreign_postal_code => x_s_foreign_postal_code,
1290 x_pnote_batch_id => x_pnote_batch_id,
1291 x_pnote_ack_date => x_pnote_ack_date,
1292 x_pnote_mpn_ind => x_pnote_mpn_ind,
1293 x_award_id => x_award_id,
1294 x_base_id => x_base_id,
1295 x_loan_key_num => x_loan_key_num,
1296 x_fin_award_year => x_fin_award_year,
1297 x_cps_trans_num => x_cps_trans_num,
1298 x_pymt_servicer_amt => x_pymt_servicer_amt,
1299 x_pymt_servicer_date => x_pymt_servicer_date,
1300 x_book_loan_amt => x_book_loan_amt,
1301 x_book_loan_amt_date => x_book_loan_amt_date,
1302 x_s_chg_ssn => x_s_chg_ssn,
1303 x_s_chg_last_name => x_s_chg_last_name,
1304 x_b_chg_ssn => x_b_chg_ssn,
1305 x_b_chg_last_name => x_b_chg_last_name,
1306 x_note_message => x_note_message,
1307 x_full_resp_code => x_full_resp_code,
1308 x_s_permt_county => x_s_permt_county,
1309 x_b_permt_county => x_b_permt_county,
1310 x_s_permt_country => x_s_permt_country,
1311 x_b_permt_country => x_b_permt_country,
1312 x_crdt_decision_status => x_crdt_decision_status,
1313 x_b_chg_birth_date => x_b_chg_birth_date,
1314 x_s_chg_birth_date => x_s_chg_birth_date,
1315 x_external_loan_id_txt => x_external_loan_id_txt,
1316 x_deferment_request_code => x_deferment_request_code,
1317 x_eft_authorization_code => x_eft_authorization_code,
1318 x_requested_loan_amt => x_requested_loan_amt,
1319 x_actual_record_type_code => x_actual_record_type_code,
1320 x_reinstatement_amt => x_reinstatement_amt,
1321 x_lender_use_txt => x_lender_use_txt,
1322 x_guarantor_use_txt => x_guarantor_use_txt,
1323 x_fls_approved_amt => x_fls_approved_amt,
1324 x_flu_approved_amt => x_flu_approved_amt,
1325 x_flp_approved_amt => x_flp_approved_amt,
1326 x_alt_approved_amt => x_alt_approved_amt,
1327 x_loan_app_form_code => x_loan_app_form_code,
1328 x_alt_borrower_ind_flag => x_alt_borrower_ind_flag,
1329 x_school_id_txt => x_school_id_txt,
1330 x_cost_of_attendance_amt => x_cost_of_attendance_amt,
1331 x_EXPECT_FAMILY_CONTRIBUTE_AMT => x_EXPECT_FAMILY_CONTRIBUTE_AMT,
1332 x_established_fin_aid_amount => x_established_fin_aid_amount,
1333 x_BOROWER_ELECTRONIC_SIGN_FLAG => x_BOROWER_ELECTRONIC_SIGN_FLAG,
1334 x_student_electronic_sign_flag => x_student_electronic_sign_flag,
1335 x_BOROWER_CREDIT_AUTHORIZ_FLAG => x_BOROWER_CREDIT_AUTHORIZ_FLAG,
1336 x_mpn_type_flag => x_mpn_type_flag,
1337 x_school_use_txt => x_school_use_txt,
1338 x_document_id_txt => x_document_id_txt,
1339 x_atd_entity_id_txt => x_atd_entity_id_txt,
1340 x_rep_entity_id_txt => x_rep_entity_id_txt,
1341 x_source_entity_id_txt => x_source_entity_id_txt,
1342 x_interest_rebate_percent_num => x_interest_rebate_percent_num,
1343 x_esign_src_typ_cd => x_esign_src_typ_cd,
1344 x_loansh_id => x_loansh_id,
1345 x_source_txt => x_source_txt,
1346 x_creation_date => x_last_update_date,
1347 x_created_by => x_last_updated_by,
1348 x_last_update_date => x_last_update_date,
1349 x_last_updated_by => x_last_updated_by,
1350 x_last_update_login => x_last_update_login
1351 );
1352
1353 INSERT INTO igf_sl_lor_loc_history (
1354 loan_id,
1355 origination_id,
1356 loan_number,
1357 loan_type,
1358 loan_amt_offered,
1359 loan_amt_accepted,
1360 loan_per_begin_date,
1361 loan_per_end_date,
1362 acad_yr_begin_date,
1363 acad_yr_end_date,
1364 loan_status,
1365 loan_status_date,
1366 loan_chg_status,
1367 loan_chg_status_date,
1368 req_serial_loan_code,
1369 act_serial_loan_code,
1370 active,
1371 active_date,
1372 sch_cert_date,
1373 orig_status_flag,
1374 orig_batch_id,
1375 orig_batch_date,
1376 orig_ack_date,
1377 credit_override,
1378 credit_decision_date,
1379 pnote_delivery_code,
1380 pnote_status,
1381 pnote_status_date,
1382 pnote_id,
1383 pnote_print_ind,
1384 pnote_accept_amt,
1385 pnote_accept_date,
1386 p_signature_code,
1387 p_signature_date,
1391 orig_fee_perct,
1388 s_signature_code,
1389 unsub_elig_for_heal,
1390 disclosure_print_ind,
1392 borw_confirm_ind,
1393 borw_interest_ind,
1394 unsub_elig_for_depnt,
1395 guarantee_amt,
1396 guarantee_date,
1397 guarnt_adj_ind,
1398 guarnt_amt_redn_code,
1399 guarnt_status_code,
1400 guarnt_status_date,
1401 lend_apprv_denied_code,
1402 lend_apprv_denied_date,
1403 lend_status_code,
1404 lend_status_date,
1405 grade_level_code,
1406 enrollment_code,
1407 anticip_compl_date,
1408 borw_lender_id,
1409 guarantor_id,
1410 prc_type_code,
1411 rec_type_ind,
1412 cl_loan_type,
1413 cl_seq_number,
1414 last_resort_lender,
1415 lender_id,
1416 lend_non_ed_brc_id,
1417 recipient_id,
1418 recipient_type,
1419 recip_non_ed_brc_id,
1420 cl_rec_status,
1421 cl_rec_status_last_update,
1422 alt_prog_type_code,
1423 alt_appl_ver_code,
1424 borw_outstd_loan_code,
1425 mpn_confirm_code,
1426 resp_to_orig_code,
1427 appl_loan_phase_code,
1428 appl_loan_phase_code_chg,
1429 tot_outstd_stafford,
1430 tot_outstd_plus,
1431 alt_borw_tot_debt,
1432 act_interest_rate,
1433 service_type_code,
1434 rev_notice_of_guarnt,
1435 sch_refund_amt,
1436 sch_refund_date,
1437 uniq_layout_vend_code,
1438 uniq_layout_ident_code,
1439 p_person_id,
1440 p_ssn,
1441 p_last_name,
1442 p_first_name,
1443 p_middle_name,
1444 p_permt_addr1,
1445 p_permt_addr2,
1446 p_permt_city,
1447 p_permt_state,
1448 p_permt_zip,
1449 p_permt_addr_chg_date,
1450 p_permt_phone,
1451 p_email_addr,
1452 p_date_of_birth,
1453 p_license_num,
1454 p_license_state,
1455 p_citizenship_status,
1456 p_alien_reg_num,
1457 p_default_status,
1458 p_foreign_postal_code,
1459 p_state_of_legal_res,
1460 p_legal_res_date,
1461 s_ssn,
1462 s_last_name,
1463 s_first_name,
1464 s_middle_name,
1465 s_permt_addr1,
1466 s_permt_addr2,
1467 s_permt_city,
1468 s_permt_state,
1469 s_permt_zip,
1470 s_permt_addr_chg_date,
1471 s_permt_phone,
1472 s_local_addr1,
1473 s_local_addr2,
1474 s_local_city,
1475 s_local_state,
1476 s_local_zip,
1477 s_email_addr,
1478 s_date_of_birth,
1479 s_license_num,
1480 s_license_state,
1481 s_depncy_status,
1482 s_default_status,
1483 s_citizenship_status,
1484 s_alien_reg_num,
1485 s_foreign_postal_code,
1486 pnote_batch_id,
1487 pnote_ack_date,
1488 pnote_mpn_ind,
1489 award_id,
1490 base_id,
1491 loan_key_num,
1492 fin_award_year,
1493 cps_trans_num,
1494 pymt_servicer_amt,
1495 pymt_servicer_date,
1496 book_loan_amt,
1497 book_loan_amt_date,
1498 s_chg_ssn,
1499 s_chg_last_name,
1500 b_chg_ssn,
1501 b_chg_last_name,
1502 note_message,
1503 full_resp_code,
1504 s_permt_county,
1505 b_permt_county,
1506 s_permt_country,
1507 b_permt_country,
1508 crdt_decision_status,
1509 b_chg_birth_date,
1510 s_chg_birth_date,
1511 external_loan_id_txt,
1512 deferment_request_code,
1513 eft_authorization_code,
1514 requested_loan_amt,
1515 actual_record_type_code,
1516 reinstatement_amt,
1517 lender_use_txt,
1518 guarantor_use_txt,
1519 fls_approved_amt,
1520 flu_approved_amt,
1521 flp_approved_amt,
1522 alt_approved_amt,
1523 loan_app_form_code,
1524 alt_borrower_ind_flag,
1525 school_id_txt,
1526 cost_of_attendance_amt,
1527 EXPECT_FAMILY_CONTRIBUTE_AMT,
1528 established_fin_aid_amount,
1529 BOROWER_ELECTRONIC_SIGN_FLAG,
1530 student_electronic_sign_flag,
1531 BOROWER_CREDIT_AUTHORIZ_FLAG,
1532 mpn_type_flag,
1533 school_use_txt,
1534 document_id_txt,
1535 atd_entity_id_txt,
1536 rep_entity_id_txt,
1537 source_entity_id_txt,
1538 interest_rebate_percent_num,
1539 esign_src_typ_cd,
1540 loansh_id,
1541 source_txt,
1542 creation_date,
1543 created_by,
1544 last_update_date,
1545 last_updated_by,
1546 last_update_login,
1547 request_id,
1548 program_id,
1549 program_application_id,
1550 program_update_date
1551 ) VALUES (
1552 new_references.loan_id,
1553 new_references.origination_id,
1554 new_references.loan_number,
1555 new_references.loan_type,
1556 new_references.loan_amt_offered,
1557 new_references.loan_amt_accepted,
1558 new_references.loan_per_begin_date,
1559 new_references.loan_per_end_date,
1560 new_references.acad_yr_begin_date,
1561 new_references.acad_yr_end_date,
1562 new_references.loan_status,
1563 new_references.loan_status_date,
1564 new_references.loan_chg_status,
1565 new_references.loan_chg_status_date,
1566 new_references.req_serial_loan_code,
1567 new_references.act_serial_loan_code,
1568 new_references.active,
1572 new_references.orig_batch_id,
1569 new_references.active_date,
1570 new_references.sch_cert_date,
1571 new_references.orig_status_flag,
1573 new_references.orig_batch_date,
1574 new_references.orig_ack_date,
1575 new_references.credit_override,
1576 new_references.credit_decision_date,
1577 new_references.pnote_delivery_code,
1578 new_references.pnote_status,
1579 new_references.pnote_status_date,
1580 new_references.pnote_id,
1581 new_references.pnote_print_ind,
1582 new_references.pnote_accept_amt,
1583 new_references.pnote_accept_date,
1584 new_references.p_signature_code,
1585 new_references.p_signature_date,
1586 new_references.s_signature_code,
1587 new_references.unsub_elig_for_heal,
1588 new_references.disclosure_print_ind,
1589 new_references.orig_fee_perct,
1590 new_references.borw_confirm_ind,
1591 new_references.borw_interest_ind,
1592 new_references.unsub_elig_for_depnt,
1593 new_references.guarantee_amt,
1594 new_references.guarantee_date,
1595 new_references.guarnt_adj_ind,
1596 new_references.guarnt_amt_redn_code,
1597 new_references.guarnt_status_code,
1598 new_references.guarnt_status_date,
1599 new_references.lend_apprv_denied_code,
1600 new_references.lend_apprv_denied_date,
1601 new_references.lend_status_code,
1602 new_references.lend_status_date,
1603 new_references.grade_level_code,
1604 new_references.enrollment_code,
1605 new_references.anticip_compl_date,
1606 new_references.borw_lender_id,
1607 new_references.guarantor_id,
1608 new_references.prc_type_code,
1609 new_references.rec_type_ind,
1610 new_references.cl_loan_type,
1611 new_references.cl_seq_number,
1612 new_references.last_resort_lender,
1613 new_references.lender_id,
1614 new_references.lend_non_ed_brc_id,
1615 new_references.recipient_id,
1616 new_references.recipient_type,
1617 new_references.recip_non_ed_brc_id,
1618 new_references.cl_rec_status,
1619 new_references.cl_rec_status_last_update,
1620 new_references.alt_prog_type_code,
1621 new_references.alt_appl_ver_code,
1622 new_references.borw_outstd_loan_code,
1623 new_references.mpn_confirm_code,
1624 new_references.resp_to_orig_code,
1625 new_references.appl_loan_phase_code,
1626 new_references.appl_loan_phase_code_chg,
1627 new_references.tot_outstd_stafford,
1628 new_references.tot_outstd_plus,
1629 new_references.alt_borw_tot_debt,
1630 new_references.act_interest_rate,
1631 new_references.service_type_code,
1632 new_references.rev_notice_of_guarnt,
1633 new_references.sch_refund_amt,
1634 new_references.sch_refund_date,
1635 new_references.uniq_layout_vend_code,
1636 new_references.uniq_layout_ident_code,
1637 new_references.p_person_id,
1638 new_references.p_ssn,
1639 new_references.p_last_name,
1640 new_references.p_first_name,
1641 new_references.p_middle_name,
1642 new_references.p_permt_addr1,
1643 new_references.p_permt_addr2,
1644 new_references.p_permt_city,
1645 new_references.p_permt_state,
1646 new_references.p_permt_zip,
1647 new_references.p_permt_addr_chg_date,
1648 new_references.p_permt_phone,
1649 new_references.p_email_addr,
1650 new_references.p_date_of_birth,
1651 new_references.p_license_num,
1652 new_references.p_license_state,
1653 new_references.p_citizenship_status,
1654 new_references.p_alien_reg_num,
1655 new_references.p_default_status,
1656 new_references.p_foreign_postal_code,
1657 new_references.p_state_of_legal_res,
1658 new_references.p_legal_res_date,
1659 new_references.s_ssn,
1660 new_references.s_last_name,
1661 new_references.s_first_name,
1662 new_references.s_middle_name,
1663 new_references.s_permt_addr1,
1664 new_references.s_permt_addr2,
1665 new_references.s_permt_city,
1666 new_references.s_permt_state,
1667 new_references.s_permt_zip,
1668 new_references.s_permt_addr_chg_date,
1669 new_references.s_permt_phone,
1670 new_references.s_local_addr1,
1671 new_references.s_local_addr2,
1672 new_references.s_local_city,
1673 new_references.s_local_state,
1674 new_references.s_local_zip,
1675 new_references.s_email_addr,
1676 new_references.s_date_of_birth,
1677 new_references.s_license_num,
1678 new_references.s_license_state,
1679 new_references.s_depncy_status,
1680 new_references.s_default_status,
1681 new_references.s_citizenship_status,
1682 new_references.s_alien_reg_num,
1683 new_references.s_foreign_postal_code,
1684 new_references.pnote_batch_id,
1685 new_references.pnote_ack_date,
1686 new_references.pnote_mpn_ind,
1687 new_references.award_id,
1688 new_references.base_id,
1689 new_references.loan_key_num,
1690 new_references.fin_award_year,
1691 new_references.cps_trans_num,
1692 new_references.pymt_servicer_amt,
1693 new_references.pymt_servicer_date,
1694 new_references.book_loan_amt,
1695 new_references.book_loan_amt_date,
1696 new_references.s_chg_ssn,
1697 new_references.s_chg_last_name,
1698 new_references.b_chg_ssn,
1699 new_references.b_chg_last_name,
1700 new_references.note_message,
1701 new_references.full_resp_code,
1702 new_references.s_permt_county,
1703 new_references.b_permt_county,
1704 new_references.s_permt_country,
1705 new_references.b_permt_country,
1709 new_references.external_loan_id_txt,
1706 new_references.crdt_decision_status,
1707 new_references.b_chg_birth_date,
1708 new_references.s_chg_birth_date,
1710 new_references.deferment_request_code,
1711 new_references.eft_authorization_code,
1712 new_references.requested_loan_amt,
1713 new_references.actual_record_type_code,
1714 new_references.reinstatement_amt,
1715 new_references.lender_use_txt,
1716 new_references.guarantor_use_txt,
1717 new_references.fls_approved_amt,
1718 new_references.flu_approved_amt,
1719 new_references.flp_approved_amt,
1720 new_references.alt_approved_amt,
1721 new_references.loan_app_form_code,
1722 new_references.alt_borrower_ind_flag,
1723 new_references.school_id_txt,
1724 new_references.cost_of_attendance_amt,
1725 new_references.EXPECT_FAMILY_CONTRIBUTE_AMT,
1726 new_references.established_fin_aid_amount,
1727 new_references.BOROWER_ELECTRONIC_SIGN_FLAG,
1728 new_references.student_electronic_sign_flag,
1729 new_references.BOROWER_CREDIT_AUTHORIZ_FLAG,
1730 new_references.mpn_type_flag,
1731 new_references.school_use_txt,
1732 new_references.document_id_txt,
1733 new_references.atd_entity_id_txt,
1734 new_references.rep_entity_id_txt,
1735 new_references.source_entity_id_txt,
1736 new_references.interest_rebate_percent_num,
1737 new_references.esign_src_typ_cd,
1738 igf_sl_lor_loc_history_s.NEXTVAL,
1739 new_references.source_txt,
1740 x_last_update_date,
1741 x_last_updated_by,
1742 x_last_update_date,
1743 x_last_updated_by,
1744 x_last_update_login ,
1745 x_request_id,
1746 x_program_id,
1747 x_program_application_id,
1748 x_program_update_date
1749 ) RETURNING ROWID, loansh_id INTO x_rowid, x_loansh_id;
1750
1751 END insert_row;
1752
1753
1754 PROCEDURE lock_row (
1755 x_rowid IN VARCHAR2,
1756 x_loan_id IN NUMBER,
1757 x_origination_id IN NUMBER,
1758 x_loan_number IN VARCHAR2,
1759 x_loan_type IN VARCHAR2,
1760 x_loan_amt_offered IN NUMBER,
1761 x_loan_amt_accepted IN NUMBER,
1762 x_loan_per_begin_date IN DATE,
1763 x_loan_per_end_date IN DATE,
1764 x_acad_yr_begin_date IN DATE,
1765 x_acad_yr_end_date IN DATE,
1766 x_loan_status IN VARCHAR2,
1767 x_loan_status_date IN DATE,
1768 x_loan_chg_status IN VARCHAR2,
1769 x_loan_chg_status_date IN DATE,
1770 x_req_serial_loan_code IN VARCHAR2,
1771 x_act_serial_loan_code IN VARCHAR2,
1772 x_active IN VARCHAR2,
1773 x_active_date IN DATE,
1774 x_sch_cert_date IN DATE,
1775 x_orig_status_flag IN VARCHAR2,
1776 x_orig_batch_id IN VARCHAR2,
1777 x_orig_batch_date IN DATE,
1778 x_orig_ack_date IN DATE,
1779 x_credit_override IN VARCHAR2,
1780 x_credit_decision_date IN DATE,
1781 x_pnote_delivery_code IN VARCHAR2,
1782 x_pnote_status IN VARCHAR2,
1783 x_pnote_status_date IN DATE,
1784 x_pnote_id IN VARCHAR2,
1785 x_pnote_print_ind IN VARCHAR2,
1786 x_pnote_accept_amt IN NUMBER,
1787 x_pnote_accept_date IN DATE,
1788 x_p_signature_code IN VARCHAR2,
1789 x_p_signature_date IN DATE,
1790 x_s_signature_code IN VARCHAR2,
1791 x_unsub_elig_for_heal IN VARCHAR2,
1792 x_disclosure_print_ind IN VARCHAR2,
1793 x_orig_fee_perct IN NUMBER,
1794 x_borw_confirm_ind IN VARCHAR2,
1795 x_borw_interest_ind IN VARCHAR2,
1796 x_unsub_elig_for_depnt IN VARCHAR2,
1797 x_guarantee_amt IN NUMBER,
1798 x_guarantee_date IN DATE,
1799 x_guarnt_adj_ind IN VARCHAR2,
1800 x_guarnt_amt_redn_code IN VARCHAR2,
1801 x_guarnt_status_code IN VARCHAR2,
1802 x_guarnt_status_date IN DATE,
1803 x_lend_apprv_denied_code IN VARCHAR2,
1804 x_lend_apprv_denied_date IN DATE,
1805 x_lend_status_code IN VARCHAR2,
1806 x_lend_status_date IN DATE,
1807 x_grade_level_code IN VARCHAR2,
1808 x_enrollment_code IN VARCHAR2,
1809 x_anticip_compl_date IN DATE,
1810 x_borw_lender_id IN VARCHAR2,
1811 x_guarantor_id IN VARCHAR2,
1812 x_prc_type_code IN VARCHAR2,
1813 x_rec_type_ind IN VARCHAR2,
1814 x_cl_loan_type IN VARCHAR2,
1815 x_cl_seq_number IN NUMBER,
1816 x_last_resort_lender IN VARCHAR2,
1817 x_lender_id IN VARCHAR2,
1818 x_lend_non_ed_brc_id IN VARCHAR2,
1819 x_recipient_id IN VARCHAR2,
1820 x_recipient_type IN VARCHAR2,
1821 x_recip_non_ed_brc_id IN VARCHAR2,
1825 x_alt_appl_ver_code IN NUMBER,
1822 x_cl_rec_status IN VARCHAR2,
1823 x_cl_rec_status_last_update IN DATE,
1824 x_alt_prog_type_code IN VARCHAR2,
1826 x_borw_outstd_loan_code IN VARCHAR2,
1827 x_mpn_confirm_code IN VARCHAR2,
1828 x_resp_to_orig_code IN VARCHAR2,
1829 x_appl_loan_phase_code IN VARCHAR2,
1830 x_appl_loan_phase_code_chg IN DATE,
1831 x_tot_outstd_stafford IN NUMBER,
1832 x_tot_outstd_plus IN NUMBER,
1833 x_alt_borw_tot_debt IN NUMBER,
1834 x_act_interest_rate IN NUMBER,
1835 x_service_type_code IN VARCHAR2,
1836 x_rev_notice_of_guarnt IN VARCHAR2,
1837 x_sch_refund_amt IN NUMBER,
1838 x_sch_refund_date IN DATE,
1839 x_uniq_layout_vend_code IN VARCHAR2,
1840 x_uniq_layout_ident_code IN VARCHAR2,
1841 x_p_person_id IN NUMBER,
1842 x_p_ssn IN VARCHAR2,
1843 x_p_last_name IN VARCHAR2,
1844 x_p_first_name IN VARCHAR2,
1845 x_p_middle_name IN VARCHAR2,
1846 x_p_permt_addr1 IN VARCHAR2,
1847 x_p_permt_addr2 IN VARCHAR2,
1848 x_p_permt_city IN VARCHAR2,
1849 x_p_permt_state IN VARCHAR2,
1850 x_p_permt_zip IN VARCHAR2,
1851 x_p_permt_addr_chg_date IN DATE,
1852 x_p_permt_phone IN VARCHAR2,
1853 x_p_email_addr IN VARCHAR2,
1854 x_p_date_of_birth IN DATE,
1855 x_p_license_num IN VARCHAR2,
1856 x_p_license_state IN VARCHAR2,
1857 x_p_citizenship_status IN VARCHAR2,
1858 x_p_alien_reg_num IN VARCHAR2,
1859 x_p_default_status IN VARCHAR2,
1860 x_p_foreign_postal_code IN VARCHAR2,
1861 x_p_state_of_legal_res IN VARCHAR2,
1862 x_p_legal_res_date IN DATE,
1863 x_s_ssn IN VARCHAR2,
1864 x_s_last_name IN VARCHAR2,
1865 x_s_first_name IN VARCHAR2,
1866 x_s_middle_name IN VARCHAR2,
1867 x_s_permt_addr1 IN VARCHAR2,
1868 x_s_permt_addr2 IN VARCHAR2,
1869 x_s_permt_city IN VARCHAR2,
1870 x_s_permt_state IN VARCHAR2,
1871 x_s_permt_zip IN VARCHAR2,
1872 x_s_permt_addr_chg_date IN DATE,
1873 x_s_permt_phone IN VARCHAR2,
1874 x_s_local_addr1 IN VARCHAR2,
1875 x_s_local_addr2 IN VARCHAR2,
1876 x_s_local_city IN VARCHAR2,
1877 x_s_local_state IN VARCHAR2,
1878 x_s_local_zip IN VARCHAR2,
1879 x_s_email_addr IN VARCHAR2,
1880 x_s_date_of_birth IN DATE,
1881 x_s_license_num IN VARCHAR2,
1882 x_s_license_state IN VARCHAR2,
1883 x_s_depncy_status IN VARCHAR2,
1884 x_s_default_status IN VARCHAR2,
1885 x_s_citizenship_status IN VARCHAR2,
1886 x_s_alien_reg_num IN VARCHAR2,
1887 x_s_foreign_postal_code IN VARCHAR2,
1888 x_pnote_batch_id IN VARCHAR2,
1889 x_pnote_ack_date IN DATE,
1890 x_pnote_mpn_ind IN VARCHAR2,
1891 x_award_id IN NUMBER,
1892 x_base_id IN NUMBER,
1893 x_loan_key_num IN NUMBER,
1894 x_fin_award_year IN VARCHAR2,
1895 x_cps_trans_num IN NUMBER,
1896 x_pymt_servicer_amt IN NUMBER,
1897 x_pymt_servicer_date IN DATE,
1898 x_book_loan_amt IN NUMBER,
1899 x_book_loan_amt_date IN DATE,
1900 x_s_chg_ssn IN VARCHAR2,
1901 x_s_chg_last_name IN VARCHAR2,
1902 x_b_chg_ssn IN VARCHAR2,
1903 x_b_chg_last_name IN VARCHAR2,
1904 x_note_message IN VARCHAR2,
1905 x_full_resp_code IN VARCHAR2,
1906 x_s_permt_county IN VARCHAR2,
1907 x_b_permt_county IN VARCHAR2,
1908 x_s_permt_country IN VARCHAR2,
1909 x_b_permt_country IN VARCHAR2,
1910 x_crdt_decision_status IN VARCHAR2,
1911 x_b_chg_birth_date IN DATE,
1912 x_s_chg_birth_date IN DATE,
1913 x_external_loan_id_txt IN VARCHAR2,
1914 x_deferment_request_code IN VARCHAR2,
1915 x_eft_authorization_code IN VARCHAR2,
1916 x_requested_loan_amt IN NUMBER,
1917 x_actual_record_type_code IN VARCHAR2,
1918 x_reinstatement_amt IN NUMBER,
1919 x_lender_use_txt IN VARCHAR2,
1920 x_guarantor_use_txt IN VARCHAR2,
1921 x_fls_approved_amt IN NUMBER,
1922 x_flu_approved_amt IN NUMBER,
1923 x_flp_approved_amt IN NUMBER,
1927 x_school_id_txt IN VARCHAR2,
1924 x_alt_approved_amt IN NUMBER,
1925 x_loan_app_form_code IN VARCHAR2,
1926 x_alt_borrower_ind_flag IN VARCHAR2,
1928 x_cost_of_attendance_amt IN NUMBER,
1929 x_EXPECT_FAMILY_CONTRIBUTE_AMT IN NUMBER,
1930 x_established_fin_aid_amount IN NUMBER,
1931 x_BOROWER_ELECTRONIC_SIGN_FLAG IN VARCHAR2,
1932 x_student_electronic_sign_flag IN VARCHAR2,
1933 x_BOROWER_CREDIT_AUTHORIZ_FLAG IN VARCHAR2,
1934 x_mpn_type_flag IN VARCHAR2,
1935 x_school_use_txt IN VARCHAR2,
1936 x_document_id_txt IN VARCHAR2,
1937 x_atd_entity_id_txt IN VARCHAR2,
1938 x_rep_entity_id_txt IN VARCHAR2,
1939 x_source_entity_id_txt IN VARCHAR2,
1940 x_interest_rebate_percent_num IN NUMBER,
1941 x_esign_src_typ_cd IN VARCHAR2,
1942 x_loansh_id IN NUMBER,
1943 x_source_txt IN VARCHAR2
1944 ) AS
1945 /*
1946 || Created By : [email protected]
1947 || Created On : 03-NOV-2004
1948 || Purpose : Handles the LOCK mechanism for the table.
1949 || Known limitations, enhancements or remarks :
1950 || Change History :
1951 || Who When What
1952 || (reverse chronological order - newest change first)
1953 */
1954 CURSOR c1 IS
1955 SELECT
1956 loan_id,
1957 origination_id,
1958 loan_number,
1959 loan_type,
1960 loan_amt_offered,
1961 loan_amt_accepted,
1962 loan_per_begin_date,
1963 loan_per_end_date,
1964 acad_yr_begin_date,
1965 acad_yr_end_date,
1966 loan_status,
1967 loan_status_date,
1968 loan_chg_status,
1969 loan_chg_status_date,
1970 req_serial_loan_code,
1971 act_serial_loan_code,
1972 active,
1973 active_date,
1974 sch_cert_date,
1975 orig_status_flag,
1976 orig_batch_id,
1977 orig_batch_date,
1978 orig_ack_date,
1979 credit_override,
1980 credit_decision_date,
1981 pnote_delivery_code,
1982 pnote_status,
1983 pnote_status_date,
1984 pnote_id,
1985 pnote_print_ind,
1986 pnote_accept_amt,
1987 pnote_accept_date,
1988 p_signature_code,
1989 p_signature_date,
1990 s_signature_code,
1991 unsub_elig_for_heal,
1992 disclosure_print_ind,
1993 orig_fee_perct,
1994 borw_confirm_ind,
1995 borw_interest_ind,
1996 unsub_elig_for_depnt,
1997 guarantee_amt,
1998 guarantee_date,
1999 guarnt_adj_ind,
2000 guarnt_amt_redn_code,
2001 guarnt_status_code,
2002 guarnt_status_date,
2003 lend_apprv_denied_code,
2004 lend_apprv_denied_date,
2005 lend_status_code,
2006 lend_status_date,
2007 grade_level_code,
2008 enrollment_code,
2009 anticip_compl_date,
2010 borw_lender_id,
2011 guarantor_id,
2012 prc_type_code,
2013 rec_type_ind,
2014 cl_loan_type,
2015 cl_seq_number,
2016 last_resort_lender,
2017 lender_id,
2018 lend_non_ed_brc_id,
2019 recipient_id,
2020 recipient_type,
2021 recip_non_ed_brc_id,
2022 cl_rec_status,
2023 cl_rec_status_last_update,
2024 alt_prog_type_code,
2025 alt_appl_ver_code,
2026 borw_outstd_loan_code,
2027 mpn_confirm_code,
2028 resp_to_orig_code,
2029 appl_loan_phase_code,
2030 appl_loan_phase_code_chg,
2031 tot_outstd_stafford,
2032 tot_outstd_plus,
2033 alt_borw_tot_debt,
2034 act_interest_rate,
2035 service_type_code,
2036 rev_notice_of_guarnt,
2037 sch_refund_amt,
2038 sch_refund_date,
2039 uniq_layout_vend_code,
2040 uniq_layout_ident_code,
2041 p_person_id,
2042 p_ssn,
2043 p_last_name,
2044 p_first_name,
2045 p_middle_name,
2046 p_permt_addr1,
2047 p_permt_addr2,
2048 p_permt_city,
2049 p_permt_state,
2050 p_permt_zip,
2051 p_permt_addr_chg_date,
2052 p_permt_phone,
2053 p_email_addr,
2054 p_date_of_birth,
2055 p_license_num,
2056 p_license_state,
2057 p_citizenship_status,
2058 p_alien_reg_num,
2059 p_default_status,
2060 p_foreign_postal_code,
2061 p_state_of_legal_res,
2062 p_legal_res_date,
2063 s_ssn,
2064 s_last_name,
2065 s_first_name,
2066 s_middle_name,
2067 s_permt_addr1,
2068 s_permt_addr2,
2069 s_permt_city,
2070 s_permt_state,
2071 s_permt_zip,
2072 s_permt_addr_chg_date,
2073 s_permt_phone,
2074 s_local_addr1,
2075 s_local_addr2,
2076 s_local_city,
2077 s_local_state,
2078 s_local_zip,
2079 s_email_addr,
2080 s_date_of_birth,
2081 s_license_num,
2082 s_license_state,
2083 s_depncy_status,
2084 s_default_status,
2085 s_citizenship_status,
2086 s_alien_reg_num,
2087 s_foreign_postal_code,
2088 pnote_batch_id,
2089 pnote_ack_date,
2090 pnote_mpn_ind,
2091 award_id,
2092 base_id,
2093 loan_key_num,
2094 fin_award_year,
2095 cps_trans_num,
2096 pymt_servicer_amt,
2097 pymt_servicer_date,
2098 book_loan_amt,
2099 book_loan_amt_date,
2100 s_chg_ssn,
2101 s_chg_last_name,
2102 b_chg_ssn,
2103 b_chg_last_name,
2104 note_message,
2105 full_resp_code,
2106 s_permt_county,
2107 b_permt_county,
2108 s_permt_country,
2109 b_permt_country,
2110 crdt_decision_status,
2111 b_chg_birth_date,
2112 s_chg_birth_date,
2113 external_loan_id_txt,
2114 deferment_request_code,
2115 eft_authorization_code,
2116 requested_loan_amt,
2117 actual_record_type_code,
2118 reinstatement_amt,
2119 lender_use_txt,
2120 guarantor_use_txt,
2121 fls_approved_amt,
2122 flu_approved_amt,
2123 flp_approved_amt,
2124 alt_approved_amt,
2125 loan_app_form_code,
2126 alt_borrower_ind_flag,
2127 school_id_txt,
2128 cost_of_attendance_amt,
2129 EXPECT_FAMILY_CONTRIBUTE_AMT,
2130 established_fin_aid_amount,
2131 BOROWER_ELECTRONIC_SIGN_FLAG,
2132 student_electronic_sign_flag,
2133 BOROWER_CREDIT_AUTHORIZ_FLAG,
2134 mpn_type_flag,
2135 school_use_txt,
2136 document_id_txt,
2137 atd_entity_id_txt,
2138 rep_entity_id_txt,
2139 source_entity_id_txt,
2140 interest_rebate_percent_num,
2141 esign_src_typ_cd,
2142 loansh_id,
2143 source_txt
2144 FROM igf_sl_lor_loc_history
2145 WHERE rowid = x_rowid
2146 FOR UPDATE NOWAIT;
2147
2148 tlinfo c1%ROWTYPE;
2149
2150 BEGIN
2151
2152 OPEN c1;
2153 FETCH c1 INTO tlinfo;
2154 IF (c1%notfound) THEN
2155 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2156 igs_ge_msg_stack.add;
2157 CLOSE c1;
2158 app_exception.raise_exception;
2159 RETURN;
2160 END IF;
2161 CLOSE c1;
2162
2163 IF (
2164 (tlinfo.loan_id = x_loan_id)
2165 AND (tlinfo.origination_id = x_origination_id)
2166 AND (tlinfo.loan_number = x_loan_number)
2167 AND (tlinfo.loan_type = x_loan_type)
2168 AND ((tlinfo.loan_amt_offered = x_loan_amt_offered) OR ((tlinfo.loan_amt_offered IS NULL) AND (X_loan_amt_offered IS NULL)))
2169 AND ((tlinfo.loan_amt_accepted = x_loan_amt_accepted) OR ((tlinfo.loan_amt_accepted IS NULL) AND (X_loan_amt_accepted IS NULL)))
2170 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)))
2171 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)))
2172 AND ((tlinfo.acad_yr_begin_date = x_acad_yr_begin_date) OR ((tlinfo.acad_yr_begin_date IS NULL) AND (X_acad_yr_begin_date IS NULL)))
2173 AND ((tlinfo.acad_yr_end_date = x_acad_yr_end_date) OR ((tlinfo.acad_yr_end_date IS NULL) AND (X_acad_yr_end_date IS NULL)))
2174 AND ((tlinfo.loan_status = x_loan_status) OR ((tlinfo.loan_status IS NULL) AND (X_loan_status IS NULL)))
2175 AND ((tlinfo.loan_status_date = x_loan_status_date) OR ((tlinfo.loan_status_date IS NULL) AND (X_loan_status_date IS NULL)))
2176 AND ((tlinfo.loan_chg_status = x_loan_chg_status) OR ((tlinfo.loan_chg_status IS NULL) AND (X_loan_chg_status IS NULL)))
2177 AND ((tlinfo.loan_chg_status_date = x_loan_chg_status_date) OR ((tlinfo.loan_chg_status_date IS NULL) AND (X_loan_chg_status_date IS NULL)))
2178 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)))
2179 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)))
2180 AND ((tlinfo.active = x_active) OR ((tlinfo.active IS NULL) AND (X_active IS NULL)))
2181 AND ((tlinfo.active_date = x_active_date) OR ((tlinfo.active_date IS NULL) AND (X_active_date IS NULL)))
2182 AND ((tlinfo.sch_cert_date = x_sch_cert_date) OR ((tlinfo.sch_cert_date IS NULL) AND (X_sch_cert_date IS NULL)))
2183 AND ((tlinfo.orig_status_flag = x_orig_status_flag) OR ((tlinfo.orig_status_flag IS NULL) AND (X_orig_status_flag IS NULL)))
2184 AND ((tlinfo.orig_batch_id = x_orig_batch_id) OR ((tlinfo.orig_batch_id IS NULL) AND (X_orig_batch_id IS NULL)))
2185 AND ((tlinfo.orig_batch_date = x_orig_batch_date) OR ((tlinfo.orig_batch_date IS NULL) AND (X_orig_batch_date IS NULL)))
2186 AND ((tlinfo.orig_ack_date = x_orig_ack_date) OR ((tlinfo.orig_ack_date IS NULL) AND (X_orig_ack_date IS NULL)))
2187 AND ((tlinfo.credit_override = x_credit_override) OR ((tlinfo.credit_override IS NULL) AND (X_credit_override IS NULL)))
2188 AND ((tlinfo.credit_decision_date = x_credit_decision_date) OR ((tlinfo.credit_decision_date IS NULL) AND (X_credit_decision_date IS NULL)))
2189 AND ((tlinfo.pnote_delivery_code = x_pnote_delivery_code) OR ((tlinfo.pnote_delivery_code IS NULL) AND (X_pnote_delivery_code IS NULL)))
2190 AND ((tlinfo.pnote_status = x_pnote_status) OR ((tlinfo.pnote_status IS NULL) AND (X_pnote_status IS NULL)))
2191 AND ((tlinfo.pnote_status_date = x_pnote_status_date) OR ((tlinfo.pnote_status_date IS NULL) AND (X_pnote_status_date IS NULL)))
2192 AND ((tlinfo.pnote_id = x_pnote_id) OR ((tlinfo.pnote_id IS NULL) AND (X_pnote_id IS NULL)))
2193 AND ((tlinfo.pnote_print_ind = x_pnote_print_ind) OR ((tlinfo.pnote_print_ind IS NULL) AND (X_pnote_print_ind IS NULL)))
2194 AND ((tlinfo.pnote_accept_amt = x_pnote_accept_amt) OR ((tlinfo.pnote_accept_amt IS NULL) AND (X_pnote_accept_amt IS NULL)))
2195 AND ((tlinfo.pnote_accept_date = x_pnote_accept_date) OR ((tlinfo.pnote_accept_date IS NULL) AND (X_pnote_accept_date IS NULL)))
2196 AND ((tlinfo.p_signature_code = x_p_signature_code) OR ((tlinfo.p_signature_code IS NULL) AND (X_p_signature_code IS NULL)))
2197 AND ((tlinfo.p_signature_date = x_p_signature_date) OR ((tlinfo.p_signature_date IS NULL) AND (X_p_signature_date IS NULL)))
2198 AND ((tlinfo.s_signature_code = x_s_signature_code) OR ((tlinfo.s_signature_code IS NULL) AND (X_s_signature_code IS NULL)))
2199 AND ((tlinfo.unsub_elig_for_heal = x_unsub_elig_for_heal) OR ((tlinfo.unsub_elig_for_heal IS NULL) AND (X_unsub_elig_for_heal IS NULL)))
2200 AND ((tlinfo.disclosure_print_ind = x_disclosure_print_ind) OR ((tlinfo.disclosure_print_ind IS NULL) AND (X_disclosure_print_ind IS NULL)))
2201 AND ((tlinfo.orig_fee_perct = x_orig_fee_perct) OR ((tlinfo.orig_fee_perct IS NULL) AND (X_orig_fee_perct IS NULL)))
2205 AND ((tlinfo.guarantee_amt = x_guarantee_amt) OR ((tlinfo.guarantee_amt IS NULL) AND (X_guarantee_amt IS NULL)))
2202 AND ((tlinfo.borw_confirm_ind = x_borw_confirm_ind) OR ((tlinfo.borw_confirm_ind IS NULL) AND (X_borw_confirm_ind IS NULL)))
2203 AND ((tlinfo.borw_interest_ind = x_borw_interest_ind) OR ((tlinfo.borw_interest_ind IS NULL) AND (X_borw_interest_ind IS NULL)))
2204 AND ((tlinfo.unsub_elig_for_depnt = x_unsub_elig_for_depnt) OR ((tlinfo.unsub_elig_for_depnt IS NULL) AND (X_unsub_elig_for_depnt IS NULL)))
2206 AND ((tlinfo.guarantee_date = x_guarantee_date) OR ((tlinfo.guarantee_date IS NULL) AND (X_guarantee_date IS NULL)))
2207 AND ((tlinfo.guarnt_adj_ind = x_guarnt_adj_ind) OR ((tlinfo.guarnt_adj_ind IS NULL) AND (X_guarnt_adj_ind IS NULL)))
2208 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)))
2209 AND ((tlinfo.guarnt_status_code = x_guarnt_status_code) OR ((tlinfo.guarnt_status_code IS NULL) AND (X_guarnt_status_code IS NULL)))
2210 AND ((tlinfo.guarnt_status_date = x_guarnt_status_date) OR ((tlinfo.guarnt_status_date IS NULL) AND (X_guarnt_status_date IS NULL)))
2211 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)))
2212 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)))
2213 AND ((tlinfo.lend_status_code = x_lend_status_code) OR ((tlinfo.lend_status_code IS NULL) AND (X_lend_status_code IS NULL)))
2214 AND ((tlinfo.lend_status_date = x_lend_status_date) OR ((tlinfo.lend_status_date IS NULL) AND (X_lend_status_date IS NULL)))
2215 AND ((tlinfo.grade_level_code = x_grade_level_code) OR ((tlinfo.grade_level_code IS NULL) AND (X_grade_level_code IS NULL)))
2216 AND ((tlinfo.enrollment_code = x_enrollment_code) OR ((tlinfo.enrollment_code IS NULL) AND (X_enrollment_code IS NULL)))
2217 AND ((tlinfo.anticip_compl_date = x_anticip_compl_date) OR ((tlinfo.anticip_compl_date IS NULL) AND (X_anticip_compl_date IS NULL)))
2218 AND ((tlinfo.borw_lender_id = x_borw_lender_id) OR ((tlinfo.borw_lender_id IS NULL) AND (X_borw_lender_id IS NULL)))
2219 AND ((tlinfo.guarantor_id = x_guarantor_id) OR ((tlinfo.guarantor_id IS NULL) AND (X_guarantor_id IS NULL)))
2220 AND ((tlinfo.prc_type_code = x_prc_type_code) OR ((tlinfo.prc_type_code IS NULL) AND (X_prc_type_code IS NULL)))
2221 AND ((tlinfo.rec_type_ind = x_rec_type_ind) OR ((tlinfo.rec_type_ind IS NULL) AND (X_rec_type_ind IS NULL)))
2222 AND ((tlinfo.cl_loan_type = x_cl_loan_type) OR ((tlinfo.cl_loan_type IS NULL) AND (X_cl_loan_type IS NULL)))
2223 AND ((tlinfo.cl_seq_number = x_cl_seq_number) OR ((tlinfo.cl_seq_number IS NULL) AND (X_cl_seq_number IS NULL)))
2224 AND ((tlinfo.last_resort_lender = x_last_resort_lender) OR ((tlinfo.last_resort_lender IS NULL) AND (X_last_resort_lender IS NULL)))
2225 AND ((tlinfo.lender_id = x_lender_id) OR ((tlinfo.lender_id IS NULL) AND (X_lender_id IS NULL)))
2226 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)))
2227 AND ((tlinfo.recipient_id = x_recipient_id) OR ((tlinfo.recipient_id IS NULL) AND (X_recipient_id IS NULL)))
2228 AND ((tlinfo.recipient_type = x_recipient_type) OR ((tlinfo.recipient_type IS NULL) AND (X_recipient_type IS NULL)))
2229 AND ((tlinfo.recip_non_ed_brc_id = x_recip_non_ed_brc_id) OR ((tlinfo.recip_non_ed_brc_id IS NULL) AND (X_recip_non_ed_brc_id IS NULL)))
2230 AND ((tlinfo.cl_rec_status = x_cl_rec_status) OR ((tlinfo.cl_rec_status IS NULL) AND (X_cl_rec_status IS NULL)))
2231 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)))
2232 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)))
2233 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)))
2234 AND ((tlinfo.borw_outstd_loan_code = x_borw_outstd_loan_code) OR ((tlinfo.borw_outstd_loan_code IS NULL) AND (X_borw_outstd_loan_code IS NULL)))
2235 AND ((tlinfo.mpn_confirm_code = x_mpn_confirm_code) OR ((tlinfo.mpn_confirm_code IS NULL) AND (X_mpn_confirm_code IS NULL)))
2236 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)))
2237 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)))
2238 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)))
2239 AND ((tlinfo.tot_outstd_stafford = x_tot_outstd_stafford) OR ((tlinfo.tot_outstd_stafford IS NULL) AND (X_tot_outstd_stafford IS NULL)))
2240 AND ((tlinfo.tot_outstd_plus = x_tot_outstd_plus) OR ((tlinfo.tot_outstd_plus IS NULL) AND (X_tot_outstd_plus IS NULL)))
2241 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)))
2242 AND ((tlinfo.act_interest_rate = x_act_interest_rate) OR ((tlinfo.act_interest_rate IS NULL) AND (X_act_interest_rate IS NULL)))
2243 AND ((tlinfo.service_type_code = x_service_type_code) OR ((tlinfo.service_type_code IS NULL) AND (X_service_type_code IS NULL)))
2244 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)))
2245 AND ((tlinfo.sch_refund_amt = x_sch_refund_amt) OR ((tlinfo.sch_refund_amt IS NULL) AND (X_sch_refund_amt IS NULL)))
2246 AND ((tlinfo.sch_refund_date = x_sch_refund_date) OR ((tlinfo.sch_refund_date IS NULL) AND (X_sch_refund_date IS NULL)))
2247 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)))
2248 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)))
2249 AND ((tlinfo.p_person_id = x_p_person_id) OR ((tlinfo.p_person_id IS NULL) AND (X_p_person_id IS NULL)))
2253 AND ((tlinfo.p_middle_name = x_p_middle_name) OR ((tlinfo.p_middle_name IS NULL) AND (X_p_middle_name IS NULL)))
2250 AND ((tlinfo.p_ssn = x_p_ssn) OR ((tlinfo.p_ssn IS NULL) AND (X_p_ssn IS NULL)))
2251 AND ((tlinfo.p_last_name = x_p_last_name) OR ((tlinfo.p_last_name IS NULL) AND (X_p_last_name IS NULL)))
2252 AND ((tlinfo.p_first_name = x_p_first_name) OR ((tlinfo.p_first_name IS NULL) AND (X_p_first_name IS NULL)))
2254 AND ((tlinfo.p_permt_addr1 = x_p_permt_addr1) OR ((tlinfo.p_permt_addr1 IS NULL) AND (X_p_permt_addr1 IS NULL)))
2255 AND ((tlinfo.p_permt_addr2 = x_p_permt_addr2) OR ((tlinfo.p_permt_addr2 IS NULL) AND (X_p_permt_addr2 IS NULL)))
2256 AND ((tlinfo.p_permt_city = x_p_permt_city) OR ((tlinfo.p_permt_city IS NULL) AND (X_p_permt_city IS NULL)))
2257 AND ((tlinfo.p_permt_state = x_p_permt_state) OR ((tlinfo.p_permt_state IS NULL) AND (X_p_permt_state IS NULL)))
2258 AND ((tlinfo.p_permt_zip = x_p_permt_zip) OR ((tlinfo.p_permt_zip IS NULL) AND (X_p_permt_zip IS NULL)))
2259 AND ((tlinfo.p_permt_addr_chg_date = x_p_permt_addr_chg_date) OR ((tlinfo.p_permt_addr_chg_date IS NULL) AND (X_p_permt_addr_chg_date IS NULL)))
2260 AND ((tlinfo.p_permt_phone = x_p_permt_phone) OR ((tlinfo.p_permt_phone IS NULL) AND (X_p_permt_phone IS NULL)))
2261 AND ((tlinfo.p_email_addr = x_p_email_addr) OR ((tlinfo.p_email_addr IS NULL) AND (X_p_email_addr IS NULL)))
2262 AND ((tlinfo.p_date_of_birth = x_p_date_of_birth) OR ((tlinfo.p_date_of_birth IS NULL) AND (X_p_date_of_birth IS NULL)))
2263 AND ((tlinfo.p_license_num = x_p_license_num) OR ((tlinfo.p_license_num IS NULL) AND (X_p_license_num IS NULL)))
2264 AND ((tlinfo.p_license_state = x_p_license_state) OR ((tlinfo.p_license_state IS NULL) AND (X_p_license_state IS NULL)))
2265 AND ((tlinfo.p_citizenship_status = x_p_citizenship_status) OR ((tlinfo.p_citizenship_status IS NULL) AND (X_p_citizenship_status IS NULL)))
2266 AND ((tlinfo.p_alien_reg_num = x_p_alien_reg_num) OR ((tlinfo.p_alien_reg_num IS NULL) AND (X_p_alien_reg_num IS NULL)))
2267 AND ((tlinfo.p_default_status = x_p_default_status) OR ((tlinfo.p_default_status IS NULL) AND (X_p_default_status IS NULL)))
2268 AND ((tlinfo.p_foreign_postal_code = x_p_foreign_postal_code) OR ((tlinfo.p_foreign_postal_code IS NULL) AND (X_p_foreign_postal_code IS NULL)))
2269 AND ((tlinfo.p_state_of_legal_res = x_p_state_of_legal_res) OR ((tlinfo.p_state_of_legal_res IS NULL) AND (X_p_state_of_legal_res IS NULL)))
2270 AND ((tlinfo.p_legal_res_date = x_p_legal_res_date) OR ((tlinfo.p_legal_res_date IS NULL) AND (X_p_legal_res_date IS NULL)))
2271 AND ((tlinfo.s_ssn = x_s_ssn) OR ((tlinfo.s_ssn IS NULL) AND (X_s_ssn IS NULL)))
2272 AND ((tlinfo.s_last_name = x_s_last_name) OR ((tlinfo.s_last_name IS NULL) AND (X_s_last_name IS NULL)))
2273 AND ((tlinfo.s_first_name = x_s_first_name) OR ((tlinfo.s_first_name IS NULL) AND (X_s_first_name IS NULL)))
2274 AND ((tlinfo.s_middle_name = x_s_middle_name) OR ((tlinfo.s_middle_name IS NULL) AND (X_s_middle_name IS NULL)))
2275 AND ((tlinfo.s_permt_addr1 = x_s_permt_addr1) OR ((tlinfo.s_permt_addr1 IS NULL) AND (X_s_permt_addr1 IS NULL)))
2276 AND ((tlinfo.s_permt_addr2 = x_s_permt_addr2) OR ((tlinfo.s_permt_addr2 IS NULL) AND (X_s_permt_addr2 IS NULL)))
2277 AND ((tlinfo.s_permt_city = x_s_permt_city) OR ((tlinfo.s_permt_city IS NULL) AND (X_s_permt_city IS NULL)))
2278 AND ((tlinfo.s_permt_state = x_s_permt_state) OR ((tlinfo.s_permt_state IS NULL) AND (X_s_permt_state IS NULL)))
2279 AND ((tlinfo.s_permt_zip = x_s_permt_zip) OR ((tlinfo.s_permt_zip IS NULL) AND (X_s_permt_zip IS NULL)))
2280 AND ((tlinfo.s_permt_addr_chg_date = x_s_permt_addr_chg_date) OR ((tlinfo.s_permt_addr_chg_date IS NULL) AND (X_s_permt_addr_chg_date IS NULL)))
2281 AND ((tlinfo.s_permt_phone = x_s_permt_phone) OR ((tlinfo.s_permt_phone IS NULL) AND (X_s_permt_phone IS NULL)))
2282 AND ((tlinfo.s_local_addr1 = x_s_local_addr1) OR ((tlinfo.s_local_addr1 IS NULL) AND (X_s_local_addr1 IS NULL)))
2283 AND ((tlinfo.s_local_addr2 = x_s_local_addr2) OR ((tlinfo.s_local_addr2 IS NULL) AND (X_s_local_addr2 IS NULL)))
2284 AND ((tlinfo.s_local_city = x_s_local_city) OR ((tlinfo.s_local_city IS NULL) AND (X_s_local_city IS NULL)))
2285 AND ((tlinfo.s_local_state = x_s_local_state) OR ((tlinfo.s_local_state IS NULL) AND (X_s_local_state IS NULL)))
2286 AND ((tlinfo.s_local_zip = x_s_local_zip) OR ((tlinfo.s_local_zip IS NULL) AND (X_s_local_zip IS NULL)))
2287 AND ((tlinfo.s_email_addr = x_s_email_addr) OR ((tlinfo.s_email_addr IS NULL) AND (X_s_email_addr IS NULL)))
2288 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)))
2289 AND ((tlinfo.s_license_num = x_s_license_num) OR ((tlinfo.s_license_num IS NULL) AND (X_s_license_num IS NULL)))
2290 AND ((tlinfo.s_license_state = x_s_license_state) OR ((tlinfo.s_license_state IS NULL) AND (X_s_license_state IS NULL)))
2291 AND ((tlinfo.s_depncy_status = x_s_depncy_status) OR ((tlinfo.s_depncy_status IS NULL) AND (X_s_depncy_status IS NULL)))
2292 AND ((tlinfo.s_default_status = x_s_default_status) OR ((tlinfo.s_default_status IS NULL) AND (X_s_default_status IS NULL)))
2293 AND ((tlinfo.s_citizenship_status = x_s_citizenship_status) OR ((tlinfo.s_citizenship_status IS NULL) AND (X_s_citizenship_status IS NULL)))
2294 AND ((tlinfo.s_alien_reg_num = x_s_alien_reg_num) OR ((tlinfo.s_alien_reg_num IS NULL) AND (X_s_alien_reg_num IS NULL)))
2295 AND ((tlinfo.s_foreign_postal_code = x_s_foreign_postal_code) OR ((tlinfo.s_foreign_postal_code IS NULL) AND (X_s_foreign_postal_code IS NULL)))
2296 AND ((tlinfo.pnote_batch_id = x_pnote_batch_id) OR ((tlinfo.pnote_batch_id IS NULL) AND (X_pnote_batch_id IS NULL)))
2297 AND ((tlinfo.pnote_ack_date = x_pnote_ack_date) OR ((tlinfo.pnote_ack_date IS NULL) AND (X_pnote_ack_date IS NULL)))
2298 AND ((tlinfo.pnote_mpn_ind = x_pnote_mpn_ind) OR ((tlinfo.pnote_mpn_ind IS NULL) AND (X_pnote_mpn_ind IS NULL)))
2299 AND ((tlinfo.award_id = x_award_id) OR ((tlinfo.award_id IS NULL) AND (X_award_id IS NULL)))
2300 AND ((tlinfo.base_id = x_base_id) OR ((tlinfo.base_id IS NULL) AND (X_base_id IS NULL)))
2301 AND ((tlinfo.loan_key_num = x_loan_key_num) OR ((tlinfo.loan_key_num IS NULL) AND (X_loan_key_num IS NULL)))
2305 AND ((tlinfo.pymt_servicer_date = x_pymt_servicer_date) OR ((tlinfo.pymt_servicer_date IS NULL) AND (X_pymt_servicer_date IS NULL)))
2302 AND ((tlinfo.fin_award_year = x_fin_award_year) OR ((tlinfo.fin_award_year IS NULL) AND (X_fin_award_year IS NULL)))
2303 AND ((tlinfo.cps_trans_num = x_cps_trans_num) OR ((tlinfo.cps_trans_num IS NULL) AND (X_cps_trans_num IS NULL)))
2304 AND ((tlinfo.pymt_servicer_amt = x_pymt_servicer_amt) OR ((tlinfo.pymt_servicer_amt IS NULL) AND (X_pymt_servicer_amt IS NULL)))
2306 AND ((tlinfo.book_loan_amt = x_book_loan_amt) OR ((tlinfo.book_loan_amt IS NULL) AND (X_book_loan_amt IS NULL)))
2307 AND ((tlinfo.book_loan_amt_date = x_book_loan_amt_date) OR ((tlinfo.book_loan_amt_date IS NULL) AND (X_book_loan_amt_date IS NULL)))
2308 AND ((tlinfo.s_chg_ssn = x_s_chg_ssn) OR ((tlinfo.s_chg_ssn IS NULL) AND (X_s_chg_ssn IS NULL)))
2309 AND ((tlinfo.s_chg_last_name = x_s_chg_last_name) OR ((tlinfo.s_chg_last_name IS NULL) AND (X_s_chg_last_name IS NULL)))
2310 AND ((tlinfo.b_chg_ssn = x_b_chg_ssn) OR ((tlinfo.b_chg_ssn IS NULL) AND (X_b_chg_ssn IS NULL)))
2311 AND ((tlinfo.b_chg_last_name = x_b_chg_last_name) OR ((tlinfo.b_chg_last_name IS NULL) AND (X_b_chg_last_name IS NULL)))
2312 AND ((tlinfo.note_message = x_note_message) OR ((tlinfo.note_message IS NULL) AND (X_note_message IS NULL)))
2313 AND ((tlinfo.full_resp_code = x_full_resp_code) OR ((tlinfo.full_resp_code IS NULL) AND (X_full_resp_code IS NULL)))
2314 AND ((tlinfo.s_permt_county = x_s_permt_county) OR ((tlinfo.s_permt_county IS NULL) AND (X_s_permt_county IS NULL)))
2315 AND ((tlinfo.b_permt_county = x_b_permt_county) OR ((tlinfo.b_permt_county IS NULL) AND (X_b_permt_county IS NULL)))
2316 AND ((tlinfo.s_permt_country = x_s_permt_country) OR ((tlinfo.s_permt_country IS NULL) AND (X_s_permt_country IS NULL)))
2317 AND ((tlinfo.b_permt_country = x_b_permt_country) OR ((tlinfo.b_permt_country IS NULL) AND (X_b_permt_country IS NULL)))
2318 AND ((tlinfo.crdt_decision_status = x_crdt_decision_status) OR ((tlinfo.crdt_decision_status IS NULL) AND (X_crdt_decision_status IS NULL)))
2319 AND ((tlinfo.b_chg_birth_date = x_b_chg_birth_date) OR ((tlinfo.b_chg_birth_date IS NULL) AND (X_b_chg_birth_date IS NULL)))
2320 AND ((tlinfo.s_chg_birth_date = x_s_chg_birth_date) OR ((tlinfo.s_chg_birth_date IS NULL) AND (X_s_chg_birth_date IS NULL)))
2321 AND ((tlinfo.external_loan_id_txt = x_external_loan_id_txt) OR ((tlinfo.external_loan_id_txt IS NULL) AND (X_external_loan_id_txt IS NULL)))
2322 AND ((tlinfo.deferment_request_code = x_deferment_request_code) OR ((tlinfo.deferment_request_code IS NULL) AND (X_deferment_request_code IS NULL)))
2323 AND ((tlinfo.eft_authorization_code = x_eft_authorization_code) OR ((tlinfo.eft_authorization_code IS NULL) AND (X_eft_authorization_code IS NULL)))
2324 AND ((tlinfo.requested_loan_amt = x_requested_loan_amt) OR ((tlinfo.requested_loan_amt IS NULL) AND (X_requested_loan_amt IS NULL)))
2325 AND ((tlinfo.actual_record_type_code = x_actual_record_type_code) OR ((tlinfo.actual_record_type_code IS NULL) AND (X_actual_record_type_code IS NULL)))
2326 AND ((tlinfo.reinstatement_amt = x_reinstatement_amt) OR ((tlinfo.reinstatement_amt IS NULL) AND (X_reinstatement_amt IS NULL)))
2327 AND ((tlinfo.lender_use_txt = x_lender_use_txt) OR ((tlinfo.lender_use_txt IS NULL) AND (X_lender_use_txt IS NULL)))
2328 AND ((tlinfo.guarantor_use_txt = x_guarantor_use_txt) OR ((tlinfo.guarantor_use_txt IS NULL) AND (X_guarantor_use_txt IS NULL)))
2329 AND ((tlinfo.fls_approved_amt = x_fls_approved_amt) OR ((tlinfo.fls_approved_amt IS NULL) AND (X_fls_approved_amt IS NULL)))
2330 AND ((tlinfo.flu_approved_amt = x_flu_approved_amt) OR ((tlinfo.flu_approved_amt IS NULL) AND (X_flu_approved_amt IS NULL)))
2331 AND ((tlinfo.flp_approved_amt = x_flp_approved_amt) OR ((tlinfo.flp_approved_amt IS NULL) AND (X_flp_approved_amt IS NULL)))
2332 AND ((tlinfo.alt_approved_amt = x_alt_approved_amt) OR ((tlinfo.alt_approved_amt IS NULL) AND (X_alt_approved_amt IS NULL)))
2333 AND ((tlinfo.loan_app_form_code = x_loan_app_form_code) OR ((tlinfo.loan_app_form_code IS NULL) AND (X_loan_app_form_code IS NULL)))
2334 AND ((tlinfo.alt_borrower_ind_flag = x_alt_borrower_ind_flag) OR ((tlinfo.alt_borrower_ind_flag IS NULL) AND (X_alt_borrower_ind_flag IS NULL)))
2335 AND ((tlinfo.school_id_txt = x_school_id_txt) OR ((tlinfo.school_id_txt IS NULL) AND (X_school_id_txt IS NULL)))
2336 AND ((tlinfo.cost_of_attendance_amt = x_cost_of_attendance_amt) OR ((tlinfo.cost_of_attendance_amt IS NULL) AND (X_cost_of_attendance_amt IS NULL)))
2337 AND ((tlinfo.EXPECT_FAMILY_CONTRIBUTE_AMT = x_EXPECT_FAMILY_CONTRIBUTE_AMT) OR ((tlinfo.EXPECT_FAMILY_CONTRIBUTE_AMT IS NULL) AND (X_EXPECT_FAMILY_CONTRIBUTE_AMT IS NULL)))
2338 AND ((tlinfo.established_fin_aid_amount = x_established_fin_aid_amount) OR ((tlinfo.established_fin_aid_amount IS NULL) AND (X_established_fin_aid_amount IS NULL)))
2339 AND ((tlinfo.BOROWER_ELECTRONIC_SIGN_FLAG = x_BOROWER_ELECTRONIC_SIGN_FLAG) OR ((tlinfo.BOROWER_ELECTRONIC_SIGN_FLAG IS NULL) AND (X_BOROWER_ELECTRONIC_SIGN_FLAG IS NULL)))
2340 AND ((tlinfo.student_electronic_sign_flag = x_student_electronic_sign_flag) OR ((tlinfo.student_electronic_sign_flag IS NULL) AND (X_student_electronic_sign_flag IS NULL)))
2341 AND ((tlinfo.BOROWER_CREDIT_AUTHORIZ_FLAG = x_BOROWER_CREDIT_AUTHORIZ_FLAG) OR ((tlinfo.BOROWER_CREDIT_AUTHORIZ_FLAG IS NULL) AND (X_BOROWER_CREDIT_AUTHORIZ_FLAG IS NULL)))
2342 AND ((tlinfo.mpn_type_flag = x_mpn_type_flag) OR ((tlinfo.mpn_type_flag IS NULL) AND (X_mpn_type_flag IS NULL)))
2343 AND ((tlinfo.school_use_txt = x_school_use_txt) OR ((tlinfo.school_use_txt IS NULL) AND (X_school_use_txt IS NULL)))
2344 AND ((tlinfo.document_id_txt = x_document_id_txt) OR ((tlinfo.document_id_txt IS NULL) AND (X_document_id_txt IS NULL)))
2345 AND ((tlinfo.atd_entity_id_txt = x_atd_entity_id_txt) OR ((tlinfo.atd_entity_id_txt IS NULL) AND (X_atd_entity_id_txt IS NULL)))
2346 AND ((tlinfo.rep_entity_id_txt = x_rep_entity_id_txt) OR ((tlinfo.rep_entity_id_txt IS NULL) AND (X_rep_entity_id_txt IS NULL)))
2347 AND ((tlinfo.source_entity_id_txt = x_source_entity_id_txt) OR ((tlinfo.source_entity_id_txt IS NULL) AND (X_source_entity_id_txt IS NULL)))
2348 AND ((tlinfo.interest_rebate_percent_num = x_interest_rebate_percent_num) OR ((tlinfo.interest_rebate_percent_num IS NULL) AND (X_interest_rebate_percent_num IS NULL)))
2352 ) THEN
2349 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)))
2350 AND (tlinfo.loansh_id = x_loansh_id)
2351 AND ((tlinfo.source_txt = x_source_txt) OR ((tlinfo.source_txt IS NULL) AND (X_source_txt IS NULL)))
2353 NULL;
2354 ELSE
2355 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2356 igs_ge_msg_stack.add;
2357 app_exception.raise_exception;
2358 END IF;
2359
2360 RETURN;
2361
2362 END lock_row;
2363
2364
2365 PROCEDURE update_row (
2366 x_rowid IN VARCHAR2,
2367 x_loan_id IN NUMBER,
2368 x_origination_id IN NUMBER,
2369 x_loan_number IN VARCHAR2,
2370 x_loan_type IN VARCHAR2,
2371 x_loan_amt_offered IN NUMBER,
2372 x_loan_amt_accepted IN NUMBER,
2373 x_loan_per_begin_date IN DATE,
2374 x_loan_per_end_date IN DATE,
2375 x_acad_yr_begin_date IN DATE,
2376 x_acad_yr_end_date IN DATE,
2377 x_loan_status IN VARCHAR2,
2378 x_loan_status_date IN DATE,
2379 x_loan_chg_status IN VARCHAR2,
2380 x_loan_chg_status_date IN DATE,
2381 x_req_serial_loan_code IN VARCHAR2,
2382 x_act_serial_loan_code IN VARCHAR2,
2383 x_active IN VARCHAR2,
2384 x_active_date IN DATE,
2385 x_sch_cert_date IN DATE,
2386 x_orig_status_flag IN VARCHAR2,
2387 x_orig_batch_id IN VARCHAR2,
2388 x_orig_batch_date IN DATE,
2389 x_orig_ack_date IN DATE,
2390 x_credit_override IN VARCHAR2,
2391 x_credit_decision_date IN DATE,
2392 x_pnote_delivery_code IN VARCHAR2,
2393 x_pnote_status IN VARCHAR2,
2394 x_pnote_status_date IN DATE,
2395 x_pnote_id IN VARCHAR2,
2396 x_pnote_print_ind IN VARCHAR2,
2397 x_pnote_accept_amt IN NUMBER,
2398 x_pnote_accept_date IN DATE,
2399 x_p_signature_code IN VARCHAR2,
2400 x_p_signature_date IN DATE,
2401 x_s_signature_code IN VARCHAR2,
2402 x_unsub_elig_for_heal IN VARCHAR2,
2403 x_disclosure_print_ind IN VARCHAR2,
2404 x_orig_fee_perct IN NUMBER,
2405 x_borw_confirm_ind IN VARCHAR2,
2406 x_borw_interest_ind IN VARCHAR2,
2407 x_unsub_elig_for_depnt IN VARCHAR2,
2408 x_guarantee_amt IN NUMBER,
2409 x_guarantee_date IN DATE,
2410 x_guarnt_adj_ind IN VARCHAR2,
2411 x_guarnt_amt_redn_code IN VARCHAR2,
2412 x_guarnt_status_code IN VARCHAR2,
2413 x_guarnt_status_date IN DATE,
2414 x_lend_apprv_denied_code IN VARCHAR2,
2415 x_lend_apprv_denied_date IN DATE,
2416 x_lend_status_code IN VARCHAR2,
2417 x_lend_status_date IN DATE,
2418 x_grade_level_code IN VARCHAR2,
2419 x_enrollment_code IN VARCHAR2,
2420 x_anticip_compl_date IN DATE,
2421 x_borw_lender_id IN VARCHAR2,
2422 x_guarantor_id IN VARCHAR2,
2423 x_prc_type_code IN VARCHAR2,
2424 x_rec_type_ind IN VARCHAR2,
2425 x_cl_loan_type IN VARCHAR2,
2426 x_cl_seq_number IN NUMBER,
2427 x_last_resort_lender IN VARCHAR2,
2428 x_lender_id IN VARCHAR2,
2429 x_lend_non_ed_brc_id IN VARCHAR2,
2430 x_recipient_id IN VARCHAR2,
2431 x_recipient_type IN VARCHAR2,
2432 x_recip_non_ed_brc_id IN VARCHAR2,
2433 x_cl_rec_status IN VARCHAR2,
2434 x_cl_rec_status_last_update IN DATE,
2435 x_alt_prog_type_code IN VARCHAR2,
2436 x_alt_appl_ver_code IN NUMBER,
2437 x_borw_outstd_loan_code IN VARCHAR2,
2438 x_mpn_confirm_code IN VARCHAR2,
2439 x_resp_to_orig_code IN VARCHAR2,
2440 x_appl_loan_phase_code IN VARCHAR2,
2441 x_appl_loan_phase_code_chg IN DATE,
2442 x_tot_outstd_stafford IN NUMBER,
2443 x_tot_outstd_plus IN NUMBER,
2444 x_alt_borw_tot_debt IN NUMBER,
2445 x_act_interest_rate IN NUMBER,
2446 x_service_type_code IN VARCHAR2,
2447 x_rev_notice_of_guarnt IN VARCHAR2,
2448 x_sch_refund_amt IN NUMBER,
2449 x_sch_refund_date IN DATE,
2450 x_uniq_layout_vend_code IN VARCHAR2,
2451 x_uniq_layout_ident_code IN VARCHAR2,
2452 x_p_person_id IN NUMBER,
2453 x_p_ssn IN VARCHAR2,
2454 x_p_last_name IN VARCHAR2,
2455 x_p_first_name IN VARCHAR2,
2456 x_p_middle_name IN VARCHAR2,
2457 x_p_permt_addr1 IN VARCHAR2,
2461 x_p_permt_zip IN VARCHAR2,
2458 x_p_permt_addr2 IN VARCHAR2,
2459 x_p_permt_city IN VARCHAR2,
2460 x_p_permt_state IN VARCHAR2,
2462 x_p_permt_addr_chg_date IN DATE,
2463 x_p_permt_phone IN VARCHAR2,
2464 x_p_email_addr IN VARCHAR2,
2465 x_p_date_of_birth IN DATE,
2466 x_p_license_num IN VARCHAR2,
2467 x_p_license_state IN VARCHAR2,
2468 x_p_citizenship_status IN VARCHAR2,
2469 x_p_alien_reg_num IN VARCHAR2,
2470 x_p_default_status IN VARCHAR2,
2471 x_p_foreign_postal_code IN VARCHAR2,
2472 x_p_state_of_legal_res IN VARCHAR2,
2473 x_p_legal_res_date IN DATE,
2474 x_s_ssn IN VARCHAR2,
2475 x_s_last_name IN VARCHAR2,
2476 x_s_first_name IN VARCHAR2,
2477 x_s_middle_name IN VARCHAR2,
2478 x_s_permt_addr1 IN VARCHAR2,
2479 x_s_permt_addr2 IN VARCHAR2,
2480 x_s_permt_city IN VARCHAR2,
2481 x_s_permt_state IN VARCHAR2,
2482 x_s_permt_zip IN VARCHAR2,
2483 x_s_permt_addr_chg_date IN DATE,
2484 x_s_permt_phone IN VARCHAR2,
2485 x_s_local_addr1 IN VARCHAR2,
2486 x_s_local_addr2 IN VARCHAR2,
2487 x_s_local_city IN VARCHAR2,
2488 x_s_local_state IN VARCHAR2,
2489 x_s_local_zip IN VARCHAR2,
2490 x_s_email_addr IN VARCHAR2,
2491 x_s_date_of_birth IN DATE,
2492 x_s_license_num IN VARCHAR2,
2493 x_s_license_state IN VARCHAR2,
2494 x_s_depncy_status IN VARCHAR2,
2495 x_s_default_status IN VARCHAR2,
2496 x_s_citizenship_status IN VARCHAR2,
2497 x_s_alien_reg_num IN VARCHAR2,
2498 x_s_foreign_postal_code IN VARCHAR2,
2499 x_pnote_batch_id IN VARCHAR2,
2500 x_pnote_ack_date IN DATE,
2501 x_pnote_mpn_ind IN VARCHAR2,
2502 x_award_id IN NUMBER,
2503 x_base_id IN NUMBER,
2504 x_loan_key_num IN NUMBER,
2505 x_fin_award_year IN VARCHAR2,
2506 x_cps_trans_num IN NUMBER,
2507 x_pymt_servicer_amt IN NUMBER,
2508 x_pymt_servicer_date IN DATE,
2509 x_book_loan_amt IN NUMBER,
2510 x_book_loan_amt_date IN DATE,
2511 x_s_chg_ssn IN VARCHAR2,
2512 x_s_chg_last_name IN VARCHAR2,
2513 x_b_chg_ssn IN VARCHAR2,
2514 x_b_chg_last_name IN VARCHAR2,
2515 x_note_message IN VARCHAR2,
2516 x_full_resp_code IN VARCHAR2,
2517 x_s_permt_county IN VARCHAR2,
2518 x_b_permt_county IN VARCHAR2,
2519 x_s_permt_country IN VARCHAR2,
2520 x_b_permt_country IN VARCHAR2,
2521 x_crdt_decision_status IN VARCHAR2,
2522 x_b_chg_birth_date IN DATE,
2523 x_s_chg_birth_date IN DATE,
2524 x_external_loan_id_txt IN VARCHAR2,
2525 x_deferment_request_code IN VARCHAR2,
2526 x_eft_authorization_code IN VARCHAR2,
2527 x_requested_loan_amt IN NUMBER,
2528 x_actual_record_type_code IN VARCHAR2,
2529 x_reinstatement_amt IN NUMBER,
2530 x_lender_use_txt IN VARCHAR2,
2531 x_guarantor_use_txt IN VARCHAR2,
2532 x_fls_approved_amt IN NUMBER,
2533 x_flu_approved_amt IN NUMBER,
2534 x_flp_approved_amt IN NUMBER,
2535 x_alt_approved_amt IN NUMBER,
2536 x_loan_app_form_code IN VARCHAR2,
2537 x_alt_borrower_ind_flag IN VARCHAR2,
2538 x_school_id_txt IN VARCHAR2,
2539 x_cost_of_attendance_amt IN NUMBER,
2540 x_EXPECT_FAMILY_CONTRIBUTE_AMT IN NUMBER,
2541 x_established_fin_aid_amount IN NUMBER,
2542 x_BOROWER_ELECTRONIC_SIGN_FLAG IN VARCHAR2,
2543 x_student_electronic_sign_flag IN VARCHAR2,
2544 x_BOROWER_CREDIT_AUTHORIZ_FLAG IN VARCHAR2,
2545 x_mpn_type_flag IN VARCHAR2,
2546 x_school_use_txt IN VARCHAR2,
2547 x_document_id_txt IN VARCHAR2,
2548 x_atd_entity_id_txt IN VARCHAR2,
2549 x_rep_entity_id_txt IN VARCHAR2,
2550 x_source_entity_id_txt IN VARCHAR2,
2551 x_interest_rebate_percent_num IN NUMBER,
2552 x_esign_src_typ_cd IN VARCHAR2,
2553 x_loansh_id IN NUMBER,
2554 x_source_txt IN VARCHAR2,
2555 x_mode IN VARCHAR2
2556 ) AS
2557 /*
2558 || Created By : [email protected]
2559 || Created On : 03-NOV-2004
2560 || Purpose : Handles the UPDATE DML logic for the table.
2561 || Known limitations, enhancements or remarks :
2565 */
2562 || Change History :
2563 || Who When What
2564 || (reverse chronological order - newest change first)
2566 x_last_update_date DATE ;
2567 x_last_updated_by NUMBER;
2568 x_last_update_login NUMBER;
2569 x_request_id NUMBER;
2570 x_program_id NUMBER;
2571 x_program_application_id NUMBER;
2572 x_program_update_date DATE;
2573
2574 BEGIN
2575
2576 x_last_update_date := SYSDATE;
2577 IF (X_MODE = 'I') THEN
2578 x_last_updated_by := 1;
2579 x_last_update_login := 0;
2580 ELSIF (x_mode = 'R') THEN
2581 x_last_updated_by := fnd_global.user_id;
2582 IF x_last_updated_by IS NULL THEN
2583 x_last_updated_by := -1;
2584 END IF;
2585 x_last_update_login := fnd_global.login_id;
2586 IF (x_last_update_login IS NULL) THEN
2587 x_last_update_login := -1;
2588 END IF;
2589 ELSE
2590 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
2591 fnd_message.set_token ('ROUTINE', 'igf_sl_lor_loc_history_PKG.UPDATE_ROW');
2592 igs_ge_msg_stack.add;
2593 app_exception.raise_exception;
2594 END IF;
2595
2596 before_dml(
2597 p_action => 'UPDATE',
2598 x_rowid => x_rowid,
2599 x_loan_id => x_loan_id,
2600 x_origination_id => x_origination_id,
2601 x_loan_number => x_loan_number,
2602 x_loan_type => x_loan_type,
2603 x_loan_amt_offered => x_loan_amt_offered,
2604 x_loan_amt_accepted => x_loan_amt_accepted,
2605 x_loan_per_begin_date => x_loan_per_begin_date,
2606 x_loan_per_end_date => x_loan_per_end_date,
2607 x_acad_yr_begin_date => x_acad_yr_begin_date,
2608 x_acad_yr_end_date => x_acad_yr_end_date,
2609 x_loan_status => x_loan_status,
2610 x_loan_status_date => x_loan_status_date,
2611 x_loan_chg_status => x_loan_chg_status,
2612 x_loan_chg_status_date => x_loan_chg_status_date,
2613 x_req_serial_loan_code => x_req_serial_loan_code,
2614 x_act_serial_loan_code => x_act_serial_loan_code,
2615 x_active => x_active,
2616 x_active_date => x_active_date,
2617 x_sch_cert_date => x_sch_cert_date,
2618 x_orig_status_flag => x_orig_status_flag,
2619 x_orig_batch_id => x_orig_batch_id,
2620 x_orig_batch_date => x_orig_batch_date,
2621 x_orig_ack_date => x_orig_ack_date,
2622 x_credit_override => x_credit_override,
2623 x_credit_decision_date => x_credit_decision_date,
2624 x_pnote_delivery_code => x_pnote_delivery_code,
2625 x_pnote_status => x_pnote_status,
2626 x_pnote_status_date => x_pnote_status_date,
2627 x_pnote_id => x_pnote_id,
2628 x_pnote_print_ind => x_pnote_print_ind,
2629 x_pnote_accept_amt => x_pnote_accept_amt,
2630 x_pnote_accept_date => x_pnote_accept_date,
2631 x_p_signature_code => x_p_signature_code,
2632 x_p_signature_date => x_p_signature_date,
2633 x_s_signature_code => x_s_signature_code,
2634 x_unsub_elig_for_heal => x_unsub_elig_for_heal,
2635 x_disclosure_print_ind => x_disclosure_print_ind,
2636 x_orig_fee_perct => x_orig_fee_perct,
2637 x_borw_confirm_ind => x_borw_confirm_ind,
2638 x_borw_interest_ind => x_borw_interest_ind,
2639 x_unsub_elig_for_depnt => x_unsub_elig_for_depnt,
2640 x_guarantee_amt => x_guarantee_amt,
2641 x_guarantee_date => x_guarantee_date,
2642 x_guarnt_adj_ind => x_guarnt_adj_ind,
2643 x_guarnt_amt_redn_code => x_guarnt_amt_redn_code,
2644 x_guarnt_status_code => x_guarnt_status_code,
2645 x_guarnt_status_date => x_guarnt_status_date,
2646 x_lend_apprv_denied_code => x_lend_apprv_denied_code,
2647 x_lend_apprv_denied_date => x_lend_apprv_denied_date,
2648 x_lend_status_code => x_lend_status_code,
2649 x_lend_status_date => x_lend_status_date,
2650 x_grade_level_code => x_grade_level_code,
2651 x_enrollment_code => x_enrollment_code,
2652 x_anticip_compl_date => x_anticip_compl_date,
2653 x_borw_lender_id => x_borw_lender_id,
2654 x_guarantor_id => x_guarantor_id,
2655 x_prc_type_code => x_prc_type_code,
2656 x_rec_type_ind => x_rec_type_ind,
2657 x_cl_loan_type => x_cl_loan_type,
2658 x_cl_seq_number => x_cl_seq_number,
2659 x_last_resort_lender => x_last_resort_lender,
2660 x_lender_id => x_lender_id,
2661 x_lend_non_ed_brc_id => x_lend_non_ed_brc_id,
2662 x_recipient_id => x_recipient_id,
2663 x_recipient_type => x_recipient_type,
2664 x_recip_non_ed_brc_id => x_recip_non_ed_brc_id,
2665 x_cl_rec_status => x_cl_rec_status,
2666 x_cl_rec_status_last_update => x_cl_rec_status_last_update,
2667 x_alt_prog_type_code => x_alt_prog_type_code,
2668 x_alt_appl_ver_code => x_alt_appl_ver_code,
2669 x_borw_outstd_loan_code => x_borw_outstd_loan_code,
2670 x_mpn_confirm_code => x_mpn_confirm_code,
2671 x_resp_to_orig_code => x_resp_to_orig_code,
2672 x_appl_loan_phase_code => x_appl_loan_phase_code,
2673 x_appl_loan_phase_code_chg => x_appl_loan_phase_code_chg,
2674 x_tot_outstd_stafford => x_tot_outstd_stafford,
2675 x_tot_outstd_plus => x_tot_outstd_plus,
2676 x_alt_borw_tot_debt => x_alt_borw_tot_debt,
2677 x_act_interest_rate => x_act_interest_rate,
2678 x_service_type_code => x_service_type_code,
2679 x_rev_notice_of_guarnt => x_rev_notice_of_guarnt,
2680 x_sch_refund_amt => x_sch_refund_amt,
2681 x_sch_refund_date => x_sch_refund_date,
2682 x_uniq_layout_vend_code => x_uniq_layout_vend_code,
2683 x_uniq_layout_ident_code => x_uniq_layout_ident_code,
2684 x_p_person_id => x_p_person_id,
2685 x_p_ssn => x_p_ssn,
2686 x_p_last_name => x_p_last_name,
2687 x_p_first_name => x_p_first_name,
2688 x_p_middle_name => x_p_middle_name,
2689 x_p_permt_addr1 => x_p_permt_addr1,
2690 x_p_permt_addr2 => x_p_permt_addr2,
2691 x_p_permt_city => x_p_permt_city,
2692 x_p_permt_state => x_p_permt_state,
2693 x_p_permt_zip => x_p_permt_zip,
2694 x_p_permt_addr_chg_date => x_p_permt_addr_chg_date,
2695 x_p_permt_phone => x_p_permt_phone,
2696 x_p_email_addr => x_p_email_addr,
2697 x_p_date_of_birth => x_p_date_of_birth,
2698 x_p_license_num => x_p_license_num,
2699 x_p_license_state => x_p_license_state,
2700 x_p_citizenship_status => x_p_citizenship_status,
2701 x_p_alien_reg_num => x_p_alien_reg_num,
2702 x_p_default_status => x_p_default_status,
2703 x_p_foreign_postal_code => x_p_foreign_postal_code,
2704 x_p_state_of_legal_res => x_p_state_of_legal_res,
2705 x_p_legal_res_date => x_p_legal_res_date,
2706 x_s_ssn => x_s_ssn,
2707 x_s_last_name => x_s_last_name,
2708 x_s_first_name => x_s_first_name,
2709 x_s_middle_name => x_s_middle_name,
2710 x_s_permt_addr1 => x_s_permt_addr1,
2711 x_s_permt_addr2 => x_s_permt_addr2,
2712 x_s_permt_city => x_s_permt_city,
2713 x_s_permt_state => x_s_permt_state,
2714 x_s_permt_zip => x_s_permt_zip,
2715 x_s_permt_addr_chg_date => x_s_permt_addr_chg_date,
2716 x_s_permt_phone => x_s_permt_phone,
2717 x_s_local_addr1 => x_s_local_addr1,
2718 x_s_local_addr2 => x_s_local_addr2,
2719 x_s_local_city => x_s_local_city,
2720 x_s_local_state => x_s_local_state,
2721 x_s_local_zip => x_s_local_zip,
2722 x_s_email_addr => x_s_email_addr,
2723 x_s_date_of_birth => x_s_date_of_birth,
2724 x_s_license_num => x_s_license_num,
2725 x_s_license_state => x_s_license_state,
2726 x_s_depncy_status => x_s_depncy_status,
2727 x_s_default_status => x_s_default_status,
2728 x_s_citizenship_status => x_s_citizenship_status,
2729 x_s_alien_reg_num => x_s_alien_reg_num,
2730 x_s_foreign_postal_code => x_s_foreign_postal_code,
2731 x_pnote_batch_id => x_pnote_batch_id,
2732 x_pnote_ack_date => x_pnote_ack_date,
2733 x_pnote_mpn_ind => x_pnote_mpn_ind,
2734 x_award_id => x_award_id,
2735 x_base_id => x_base_id,
2736 x_loan_key_num => x_loan_key_num,
2737 x_fin_award_year => x_fin_award_year,
2738 x_cps_trans_num => x_cps_trans_num,
2739 x_pymt_servicer_amt => x_pymt_servicer_amt,
2740 x_pymt_servicer_date => x_pymt_servicer_date,
2741 x_book_loan_amt => x_book_loan_amt,
2742 x_book_loan_amt_date => x_book_loan_amt_date,
2743 x_s_chg_ssn => x_s_chg_ssn,
2744 x_s_chg_last_name => x_s_chg_last_name,
2745 x_b_chg_ssn => x_b_chg_ssn,
2746 x_b_chg_last_name => x_b_chg_last_name,
2747 x_note_message => x_note_message,
2748 x_full_resp_code => x_full_resp_code,
2749 x_s_permt_county => x_s_permt_county,
2750 x_b_permt_county => x_b_permt_county,
2751 x_s_permt_country => x_s_permt_country,
2752 x_b_permt_country => x_b_permt_country,
2753 x_crdt_decision_status => x_crdt_decision_status,
2754 x_b_chg_birth_date => x_b_chg_birth_date,
2755 x_s_chg_birth_date => x_s_chg_birth_date,
2756 x_external_loan_id_txt => x_external_loan_id_txt,
2757 x_deferment_request_code => x_deferment_request_code,
2758 x_eft_authorization_code => x_eft_authorization_code,
2759 x_requested_loan_amt => x_requested_loan_amt,
2760 x_actual_record_type_code => x_actual_record_type_code,
2761 x_reinstatement_amt => x_reinstatement_amt,
2762 x_lender_use_txt => x_lender_use_txt,
2763 x_guarantor_use_txt => x_guarantor_use_txt,
2764 x_fls_approved_amt => x_fls_approved_amt,
2765 x_flu_approved_amt => x_flu_approved_amt,
2766 x_flp_approved_amt => x_flp_approved_amt,
2767 x_alt_approved_amt => x_alt_approved_amt,
2768 x_loan_app_form_code => x_loan_app_form_code,
2769 x_alt_borrower_ind_flag => x_alt_borrower_ind_flag,
2770 x_school_id_txt => x_school_id_txt,
2771 x_cost_of_attendance_amt => x_cost_of_attendance_amt,
2772 x_EXPECT_FAMILY_CONTRIBUTE_AMT => x_EXPECT_FAMILY_CONTRIBUTE_AMT,
2773 x_established_fin_aid_amount => x_established_fin_aid_amount,
2774 x_BOROWER_ELECTRONIC_SIGN_FLAG => x_BOROWER_ELECTRONIC_SIGN_FLAG,
2775 x_student_electronic_sign_flag => x_student_electronic_sign_flag,
2776 x_BOROWER_CREDIT_AUTHORIZ_FLAG => x_BOROWER_CREDIT_AUTHORIZ_FLAG,
2777 x_mpn_type_flag => x_mpn_type_flag,
2778 x_school_use_txt => x_school_use_txt,
2779 x_document_id_txt => x_document_id_txt,
2780 x_atd_entity_id_txt => x_atd_entity_id_txt,
2781 x_rep_entity_id_txt => x_rep_entity_id_txt,
2782 x_source_entity_id_txt => x_source_entity_id_txt,
2783 x_interest_rebate_percent_num => x_interest_rebate_percent_num,
2784 x_esign_src_typ_cd => x_esign_src_typ_cd,
2785 x_loansh_id => x_loansh_id,
2786 x_source_txt => x_source_txt,
2787 x_creation_date => x_last_update_date,
2788 x_created_by => x_last_updated_by,
2789 x_last_update_date => x_last_update_date,
2790 x_last_updated_by => x_last_updated_by,
2791 x_last_update_login => x_last_update_login
2792 );
2793
2794 IF (x_mode = 'R') THEN
2795 x_request_id := fnd_global.conc_request_id;
2796 x_program_id := fnd_global.conc_program_id;
2797 x_program_application_id := fnd_global.prog_appl_id;
2798 IF (x_request_id = -1) THEN
2799 x_request_id := old_references.request_id;
2800 x_program_id := old_references.program_id;
2801 x_program_application_id := old_references.program_application_id;
2802 x_program_update_date := old_references.program_update_date;
2803 ELSE
2804 x_program_update_date := SYSDATE;
2805 END IF;
2806 END IF;
2807
2808 UPDATE igf_sl_lor_loc_history
2809 SET
2810 loan_id = new_references.loan_id,
2811 origination_id = new_references.origination_id,
2812 loan_number = new_references.loan_number,
2813 loan_type = new_references.loan_type,
2814 loan_amt_offered = new_references.loan_amt_offered,
2815 loan_amt_accepted = new_references.loan_amt_accepted,
2819 acad_yr_end_date = new_references.acad_yr_end_date,
2816 loan_per_begin_date = new_references.loan_per_begin_date,
2817 loan_per_end_date = new_references.loan_per_end_date,
2818 acad_yr_begin_date = new_references.acad_yr_begin_date,
2820 loan_status = new_references.loan_status,
2821 loan_status_date = new_references.loan_status_date,
2822 loan_chg_status = new_references.loan_chg_status,
2823 loan_chg_status_date = new_references.loan_chg_status_date,
2824 req_serial_loan_code = new_references.req_serial_loan_code,
2825 act_serial_loan_code = new_references.act_serial_loan_code,
2826 active = new_references.active,
2827 active_date = new_references.active_date,
2828 sch_cert_date = new_references.sch_cert_date,
2829 orig_status_flag = new_references.orig_status_flag,
2830 orig_batch_id = new_references.orig_batch_id,
2831 orig_batch_date = new_references.orig_batch_date,
2832 orig_ack_date = new_references.orig_ack_date,
2833 credit_override = new_references.credit_override,
2834 credit_decision_date = new_references.credit_decision_date,
2835 pnote_delivery_code = new_references.pnote_delivery_code,
2836 pnote_status = new_references.pnote_status,
2837 pnote_status_date = new_references.pnote_status_date,
2838 pnote_id = new_references.pnote_id,
2839 pnote_print_ind = new_references.pnote_print_ind,
2840 pnote_accept_amt = new_references.pnote_accept_amt,
2841 pnote_accept_date = new_references.pnote_accept_date,
2842 p_signature_code = new_references.p_signature_code,
2843 p_signature_date = new_references.p_signature_date,
2844 s_signature_code = new_references.s_signature_code,
2845 unsub_elig_for_heal = new_references.unsub_elig_for_heal,
2846 disclosure_print_ind = new_references.disclosure_print_ind,
2847 orig_fee_perct = new_references.orig_fee_perct,
2848 borw_confirm_ind = new_references.borw_confirm_ind,
2849 borw_interest_ind = new_references.borw_interest_ind,
2850 unsub_elig_for_depnt = new_references.unsub_elig_for_depnt,
2851 guarantee_amt = new_references.guarantee_amt,
2852 guarantee_date = new_references.guarantee_date,
2853 guarnt_adj_ind = new_references.guarnt_adj_ind,
2854 guarnt_amt_redn_code = new_references.guarnt_amt_redn_code,
2855 guarnt_status_code = new_references.guarnt_status_code,
2856 guarnt_status_date = new_references.guarnt_status_date,
2857 lend_apprv_denied_code = new_references.lend_apprv_denied_code,
2858 lend_apprv_denied_date = new_references.lend_apprv_denied_date,
2859 lend_status_code = new_references.lend_status_code,
2860 lend_status_date = new_references.lend_status_date,
2861 grade_level_code = new_references.grade_level_code,
2862 enrollment_code = new_references.enrollment_code,
2863 anticip_compl_date = new_references.anticip_compl_date,
2864 borw_lender_id = new_references.borw_lender_id,
2865 guarantor_id = new_references.guarantor_id,
2866 prc_type_code = new_references.prc_type_code,
2867 rec_type_ind = new_references.rec_type_ind,
2868 cl_loan_type = new_references.cl_loan_type,
2869 cl_seq_number = new_references.cl_seq_number,
2870 last_resort_lender = new_references.last_resort_lender,
2871 lender_id = new_references.lender_id,
2872 lend_non_ed_brc_id = new_references.lend_non_ed_brc_id,
2873 recipient_id = new_references.recipient_id,
2874 recipient_type = new_references.recipient_type,
2875 recip_non_ed_brc_id = new_references.recip_non_ed_brc_id,
2876 cl_rec_status = new_references.cl_rec_status,
2877 cl_rec_status_last_update = new_references.cl_rec_status_last_update,
2878 alt_prog_type_code = new_references.alt_prog_type_code,
2879 alt_appl_ver_code = new_references.alt_appl_ver_code,
2880 borw_outstd_loan_code = new_references.borw_outstd_loan_code,
2881 mpn_confirm_code = new_references.mpn_confirm_code,
2882 resp_to_orig_code = new_references.resp_to_orig_code,
2883 appl_loan_phase_code = new_references.appl_loan_phase_code,
2884 appl_loan_phase_code_chg = new_references.appl_loan_phase_code_chg,
2885 tot_outstd_stafford = new_references.tot_outstd_stafford,
2886 tot_outstd_plus = new_references.tot_outstd_plus,
2887 alt_borw_tot_debt = new_references.alt_borw_tot_debt,
2888 act_interest_rate = new_references.act_interest_rate,
2889 service_type_code = new_references.service_type_code,
2890 rev_notice_of_guarnt = new_references.rev_notice_of_guarnt,
2891 sch_refund_amt = new_references.sch_refund_amt,
2892 sch_refund_date = new_references.sch_refund_date,
2893 uniq_layout_vend_code = new_references.uniq_layout_vend_code,
2897 p_last_name = new_references.p_last_name,
2894 uniq_layout_ident_code = new_references.uniq_layout_ident_code,
2895 p_person_id = new_references.p_person_id,
2896 p_ssn = new_references.p_ssn,
2898 p_first_name = new_references.p_first_name,
2899 p_middle_name = new_references.p_middle_name,
2900 p_permt_addr1 = new_references.p_permt_addr1,
2901 p_permt_addr2 = new_references.p_permt_addr2,
2902 p_permt_city = new_references.p_permt_city,
2903 p_permt_state = new_references.p_permt_state,
2904 p_permt_zip = new_references.p_permt_zip,
2905 p_permt_addr_chg_date = new_references.p_permt_addr_chg_date,
2906 p_permt_phone = new_references.p_permt_phone,
2907 p_email_addr = new_references.p_email_addr,
2908 p_date_of_birth = new_references.p_date_of_birth,
2909 p_license_num = new_references.p_license_num,
2910 p_license_state = new_references.p_license_state,
2911 p_citizenship_status = new_references.p_citizenship_status,
2912 p_alien_reg_num = new_references.p_alien_reg_num,
2913 p_default_status = new_references.p_default_status,
2914 p_foreign_postal_code = new_references.p_foreign_postal_code,
2915 p_state_of_legal_res = new_references.p_state_of_legal_res,
2916 p_legal_res_date = new_references.p_legal_res_date,
2917 s_ssn = new_references.s_ssn,
2918 s_last_name = new_references.s_last_name,
2919 s_first_name = new_references.s_first_name,
2920 s_middle_name = new_references.s_middle_name,
2921 s_permt_addr1 = new_references.s_permt_addr1,
2922 s_permt_addr2 = new_references.s_permt_addr2,
2923 s_permt_city = new_references.s_permt_city,
2924 s_permt_state = new_references.s_permt_state,
2925 s_permt_zip = new_references.s_permt_zip,
2926 s_permt_addr_chg_date = new_references.s_permt_addr_chg_date,
2927 s_permt_phone = new_references.s_permt_phone,
2928 s_local_addr1 = new_references.s_local_addr1,
2929 s_local_addr2 = new_references.s_local_addr2,
2930 s_local_city = new_references.s_local_city,
2931 s_local_state = new_references.s_local_state,
2932 s_local_zip = new_references.s_local_zip,
2933 s_email_addr = new_references.s_email_addr,
2934 s_date_of_birth = new_references.s_date_of_birth,
2935 s_license_num = new_references.s_license_num,
2936 s_license_state = new_references.s_license_state,
2937 s_depncy_status = new_references.s_depncy_status,
2938 s_default_status = new_references.s_default_status,
2939 s_citizenship_status = new_references.s_citizenship_status,
2940 s_alien_reg_num = new_references.s_alien_reg_num,
2941 s_foreign_postal_code = new_references.s_foreign_postal_code,
2942 pnote_batch_id = new_references.pnote_batch_id,
2943 pnote_ack_date = new_references.pnote_ack_date,
2944 pnote_mpn_ind = new_references.pnote_mpn_ind,
2945 award_id = new_references.award_id,
2946 base_id = new_references.base_id,
2947 loan_key_num = new_references.loan_key_num,
2948 fin_award_year = new_references.fin_award_year,
2949 cps_trans_num = new_references.cps_trans_num,
2950 pymt_servicer_amt = new_references.pymt_servicer_amt,
2951 pymt_servicer_date = new_references.pymt_servicer_date,
2952 book_loan_amt = new_references.book_loan_amt,
2953 book_loan_amt_date = new_references.book_loan_amt_date,
2954 s_chg_ssn = new_references.s_chg_ssn,
2955 s_chg_last_name = new_references.s_chg_last_name,
2956 b_chg_ssn = new_references.b_chg_ssn,
2957 b_chg_last_name = new_references.b_chg_last_name,
2958 note_message = new_references.note_message,
2959 full_resp_code = new_references.full_resp_code,
2960 s_permt_county = new_references.s_permt_county,
2961 b_permt_county = new_references.b_permt_county,
2962 s_permt_country = new_references.s_permt_country,
2963 b_permt_country = new_references.b_permt_country,
2964 crdt_decision_status = new_references.crdt_decision_status,
2965 b_chg_birth_date = new_references.b_chg_birth_date,
2966 s_chg_birth_date = new_references.s_chg_birth_date,
2967 external_loan_id_txt = new_references.external_loan_id_txt,
2968 deferment_request_code = new_references.deferment_request_code,
2969 eft_authorization_code = new_references.eft_authorization_code,
2970 requested_loan_amt = new_references.requested_loan_amt,
2971 actual_record_type_code = new_references.actual_record_type_code,
2972 reinstatement_amt = new_references.reinstatement_amt,
2973 lender_use_txt = new_references.lender_use_txt,
2977 flp_approved_amt = new_references.flp_approved_amt,
2974 guarantor_use_txt = new_references.guarantor_use_txt,
2975 fls_approved_amt = new_references.fls_approved_amt,
2976 flu_approved_amt = new_references.flu_approved_amt,
2978 alt_approved_amt = new_references.alt_approved_amt,
2979 loan_app_form_code = new_references.loan_app_form_code,
2980 alt_borrower_ind_flag = new_references.alt_borrower_ind_flag,
2981 school_id_txt = new_references.school_id_txt,
2982 cost_of_attendance_amt = new_references.cost_of_attendance_amt,
2983 EXPECT_FAMILY_CONTRIBUTE_AMT = new_references.EXPECT_FAMILY_CONTRIBUTE_AMT,
2984 established_fin_aid_amount = new_references.established_fin_aid_amount,
2985 BOROWER_ELECTRONIC_SIGN_FLAG = new_references.BOROWER_ELECTRONIC_SIGN_FLAG,
2986 student_electronic_sign_flag = new_references.student_electronic_sign_flag,
2987 BOROWER_CREDIT_AUTHORIZ_FLAG = new_references.BOROWER_CREDIT_AUTHORIZ_FLAG,
2988 mpn_type_flag = new_references.mpn_type_flag,
2989 school_use_txt = new_references.school_use_txt,
2990 document_id_txt = new_references.document_id_txt,
2991 atd_entity_id_txt = new_references.atd_entity_id_txt,
2992 rep_entity_id_txt = new_references.rep_entity_id_txt,
2993 source_entity_id_txt = new_references.source_entity_id_txt,
2994 interest_rebate_percent_num = new_references.interest_rebate_percent_num,
2995 esign_src_typ_cd = new_references.esign_src_typ_cd,
2996 loansh_id = new_references.loansh_id,
2997 source_txt = new_references.source_txt,
2998 last_update_date = x_last_update_date,
2999 last_updated_by = x_last_updated_by,
3000 last_update_login = x_last_update_login ,
3001 request_id = x_request_id,
3002 program_id = x_program_id,
3003 program_application_id = x_program_application_id,
3004 program_update_date = x_program_update_date
3005 WHERE rowid = x_rowid;
3006
3007 IF (SQL%NOTFOUND) THEN
3008 RAISE NO_DATA_FOUND;
3009 END IF;
3010
3011 END update_row;
3012
3013
3014 PROCEDURE add_row (
3015 x_rowid IN OUT NOCOPY VARCHAR2,
3016 x_loan_id IN NUMBER,
3017 x_origination_id IN NUMBER,
3018 x_loan_number IN VARCHAR2,
3019 x_loan_type IN VARCHAR2,
3020 x_loan_amt_offered IN NUMBER,
3021 x_loan_amt_accepted IN NUMBER,
3022 x_loan_per_begin_date IN DATE,
3023 x_loan_per_end_date IN DATE,
3024 x_acad_yr_begin_date IN DATE,
3025 x_acad_yr_end_date IN DATE,
3026 x_loan_status IN VARCHAR2,
3027 x_loan_status_date IN DATE,
3028 x_loan_chg_status IN VARCHAR2,
3029 x_loan_chg_status_date IN DATE,
3030 x_req_serial_loan_code IN VARCHAR2,
3031 x_act_serial_loan_code IN VARCHAR2,
3032 x_active IN VARCHAR2,
3033 x_active_date IN DATE,
3034 x_sch_cert_date IN DATE,
3035 x_orig_status_flag IN VARCHAR2,
3036 x_orig_batch_id IN VARCHAR2,
3037 x_orig_batch_date IN DATE,
3038 x_orig_ack_date IN DATE,
3039 x_credit_override IN VARCHAR2,
3040 x_credit_decision_date IN DATE,
3041 x_pnote_delivery_code IN VARCHAR2,
3042 x_pnote_status IN VARCHAR2,
3043 x_pnote_status_date IN DATE,
3044 x_pnote_id IN VARCHAR2,
3045 x_pnote_print_ind IN VARCHAR2,
3046 x_pnote_accept_amt IN NUMBER,
3047 x_pnote_accept_date IN DATE,
3048 x_p_signature_code IN VARCHAR2,
3049 x_p_signature_date IN DATE,
3050 x_s_signature_code IN VARCHAR2,
3051 x_unsub_elig_for_heal IN VARCHAR2,
3052 x_disclosure_print_ind IN VARCHAR2,
3053 x_orig_fee_perct IN NUMBER,
3054 x_borw_confirm_ind IN VARCHAR2,
3055 x_borw_interest_ind IN VARCHAR2,
3056 x_unsub_elig_for_depnt IN VARCHAR2,
3057 x_guarantee_amt IN NUMBER,
3058 x_guarantee_date IN DATE,
3059 x_guarnt_adj_ind IN VARCHAR2,
3060 x_guarnt_amt_redn_code IN VARCHAR2,
3061 x_guarnt_status_code IN VARCHAR2,
3062 x_guarnt_status_date IN DATE,
3063 x_lend_apprv_denied_code IN VARCHAR2,
3064 x_lend_apprv_denied_date IN DATE,
3065 x_lend_status_code IN VARCHAR2,
3066 x_lend_status_date IN DATE,
3067 x_grade_level_code IN VARCHAR2,
3068 x_enrollment_code IN VARCHAR2,
3069 x_anticip_compl_date IN DATE,
3070 x_borw_lender_id IN VARCHAR2,
3071 x_guarantor_id IN VARCHAR2,
3072 x_prc_type_code IN VARCHAR2,
3073 x_rec_type_ind IN VARCHAR2,
3074 x_cl_loan_type IN VARCHAR2,
3075 x_cl_seq_number IN NUMBER,
3076 x_last_resort_lender IN VARCHAR2,
3077 x_lender_id IN VARCHAR2,
3078 x_lend_non_ed_brc_id IN VARCHAR2,
3079 x_recipient_id IN VARCHAR2,
3080 x_recipient_type IN VARCHAR2,
3081 x_recip_non_ed_brc_id IN VARCHAR2,
3082 x_cl_rec_status IN VARCHAR2,
3083 x_cl_rec_status_last_update IN DATE,
3084 x_alt_prog_type_code IN VARCHAR2,
3085 x_alt_appl_ver_code IN NUMBER,
3086 x_borw_outstd_loan_code IN VARCHAR2,
3087 x_mpn_confirm_code IN VARCHAR2,
3088 x_resp_to_orig_code IN VARCHAR2,
3089 x_appl_loan_phase_code IN VARCHAR2,
3090 x_appl_loan_phase_code_chg IN DATE,
3091 x_tot_outstd_stafford IN NUMBER,
3092 x_tot_outstd_plus IN NUMBER,
3093 x_alt_borw_tot_debt IN NUMBER,
3094 x_act_interest_rate IN NUMBER,
3095 x_service_type_code IN VARCHAR2,
3096 x_rev_notice_of_guarnt IN VARCHAR2,
3097 x_sch_refund_amt IN NUMBER,
3098 x_sch_refund_date IN DATE,
3099 x_uniq_layout_vend_code IN VARCHAR2,
3100 x_uniq_layout_ident_code IN VARCHAR2,
3101 x_p_person_id IN NUMBER,
3102 x_p_ssn IN VARCHAR2,
3103 x_p_last_name IN VARCHAR2,
3104 x_p_first_name IN VARCHAR2,
3105 x_p_middle_name IN VARCHAR2,
3106 x_p_permt_addr1 IN VARCHAR2,
3107 x_p_permt_addr2 IN VARCHAR2,
3108 x_p_permt_city IN VARCHAR2,
3109 x_p_permt_state IN VARCHAR2,
3110 x_p_permt_zip IN VARCHAR2,
3111 x_p_permt_addr_chg_date IN DATE,
3112 x_p_permt_phone IN VARCHAR2,
3113 x_p_email_addr IN VARCHAR2,
3114 x_p_date_of_birth IN DATE,
3115 x_p_license_num IN VARCHAR2,
3116 x_p_license_state IN VARCHAR2,
3117 x_p_citizenship_status IN VARCHAR2,
3118 x_p_alien_reg_num IN VARCHAR2,
3119 x_p_default_status IN VARCHAR2,
3120 x_p_foreign_postal_code IN VARCHAR2,
3121 x_p_state_of_legal_res IN VARCHAR2,
3122 x_p_legal_res_date IN DATE,
3123 x_s_ssn IN VARCHAR2,
3124 x_s_last_name IN VARCHAR2,
3125 x_s_first_name IN VARCHAR2,
3126 x_s_middle_name IN VARCHAR2,
3127 x_s_permt_addr1 IN VARCHAR2,
3128 x_s_permt_addr2 IN VARCHAR2,
3129 x_s_permt_city IN VARCHAR2,
3130 x_s_permt_state IN VARCHAR2,
3131 x_s_permt_zip IN VARCHAR2,
3132 x_s_permt_addr_chg_date IN DATE,
3133 x_s_permt_phone IN VARCHAR2,
3134 x_s_local_addr1 IN VARCHAR2,
3135 x_s_local_addr2 IN VARCHAR2,
3136 x_s_local_city IN VARCHAR2,
3137 x_s_local_state IN VARCHAR2,
3138 x_s_local_zip IN VARCHAR2,
3139 x_s_email_addr IN VARCHAR2,
3140 x_s_date_of_birth IN DATE,
3141 x_s_license_num IN VARCHAR2,
3142 x_s_license_state IN VARCHAR2,
3143 x_s_depncy_status IN VARCHAR2,
3144 x_s_default_status IN VARCHAR2,
3145 x_s_citizenship_status IN VARCHAR2,
3146 x_s_alien_reg_num IN VARCHAR2,
3147 x_s_foreign_postal_code IN VARCHAR2,
3148 x_pnote_batch_id IN VARCHAR2,
3149 x_pnote_ack_date IN DATE,
3150 x_pnote_mpn_ind IN VARCHAR2,
3151 x_award_id IN NUMBER,
3152 x_base_id IN NUMBER,
3153 x_loan_key_num IN NUMBER,
3154 x_fin_award_year IN VARCHAR2,
3155 x_cps_trans_num IN NUMBER,
3156 x_pymt_servicer_amt IN NUMBER,
3157 x_pymt_servicer_date IN DATE,
3158 x_book_loan_amt IN NUMBER,
3159 x_book_loan_amt_date IN DATE,
3160 x_s_chg_ssn IN VARCHAR2,
3161 x_s_chg_last_name IN VARCHAR2,
3162 x_b_chg_ssn IN VARCHAR2,
3163 x_b_chg_last_name IN VARCHAR2,
3164 x_note_message IN VARCHAR2,
3165 x_full_resp_code IN VARCHAR2,
3166 x_s_permt_county IN VARCHAR2,
3167 x_b_permt_county IN VARCHAR2,
3168 x_s_permt_country IN VARCHAR2,
3169 x_b_permt_country IN VARCHAR2,
3173 x_external_loan_id_txt IN VARCHAR2,
3170 x_crdt_decision_status IN VARCHAR2,
3171 x_b_chg_birth_date IN DATE,
3172 x_s_chg_birth_date IN DATE,
3174 x_deferment_request_code IN VARCHAR2,
3175 x_eft_authorization_code IN VARCHAR2,
3176 x_requested_loan_amt IN NUMBER,
3177 x_actual_record_type_code IN VARCHAR2,
3178 x_reinstatement_amt IN NUMBER,
3179 x_lender_use_txt IN VARCHAR2,
3180 x_guarantor_use_txt IN VARCHAR2,
3181 x_fls_approved_amt IN NUMBER,
3182 x_flu_approved_amt IN NUMBER,
3183 x_flp_approved_amt IN NUMBER,
3184 x_alt_approved_amt IN NUMBER,
3185 x_loan_app_form_code IN VARCHAR2,
3186 x_alt_borrower_ind_flag IN VARCHAR2,
3187 x_school_id_txt IN VARCHAR2,
3188 x_cost_of_attendance_amt IN NUMBER,
3189 x_EXPECT_FAMILY_CONTRIBUTE_AMT IN NUMBER,
3190 x_established_fin_aid_amount IN NUMBER,
3191 x_BOROWER_ELECTRONIC_SIGN_FLAG IN VARCHAR2,
3192 x_student_electronic_sign_flag IN VARCHAR2,
3193 x_BOROWER_CREDIT_AUTHORIZ_FLAG IN VARCHAR2,
3194 x_mpn_type_flag IN VARCHAR2,
3195 x_school_use_txt IN VARCHAR2,
3196 x_document_id_txt IN VARCHAR2,
3197 x_atd_entity_id_txt IN VARCHAR2,
3198 x_rep_entity_id_txt IN VARCHAR2,
3199 x_source_entity_id_txt IN VARCHAR2,
3200 x_interest_rebate_percent_num IN NUMBER,
3201 x_esign_src_typ_cd IN VARCHAR2,
3202 x_loansh_id IN OUT NOCOPY NUMBER,
3203 x_source_txt IN VARCHAR2,
3204 x_mode IN VARCHAR2
3205 ) AS
3206 /*
3207 || Created By : [email protected]
3208 || Created On : 03-NOV-2004
3209 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
3210 || Known limitations, enhancements or remarks :
3211 || Change History :
3212 || Who When What
3213 || (reverse chronological order - newest change first)
3214 */
3215 CURSOR c1 IS
3216 SELECT rowid
3217 FROM igf_sl_lor_loc_history
3218 WHERE loansh_id = x_loansh_id;
3219
3220 BEGIN
3221
3222 OPEN c1;
3223 FETCH c1 INTO x_rowid;
3224 IF (c1%NOTFOUND) THEN
3225 CLOSE c1;
3226
3227 insert_row (
3228 x_rowid,
3229 x_loan_id,
3230 x_origination_id,
3231 x_loan_number,
3232 x_loan_type,
3233 x_loan_amt_offered,
3234 x_loan_amt_accepted,
3235 x_loan_per_begin_date,
3236 x_loan_per_end_date,
3237 x_acad_yr_begin_date,
3238 x_acad_yr_end_date,
3239 x_loan_status,
3240 x_loan_status_date,
3241 x_loan_chg_status,
3242 x_loan_chg_status_date,
3243 x_req_serial_loan_code,
3244 x_act_serial_loan_code,
3245 x_active,
3246 x_active_date,
3247 x_sch_cert_date,
3248 x_orig_status_flag,
3249 x_orig_batch_id,
3250 x_orig_batch_date,
3251 x_orig_ack_date,
3252 x_credit_override,
3253 x_credit_decision_date,
3254 x_pnote_delivery_code,
3255 x_pnote_status,
3256 x_pnote_status_date,
3257 x_pnote_id,
3258 x_pnote_print_ind,
3259 x_pnote_accept_amt,
3260 x_pnote_accept_date,
3261 x_p_signature_code,
3262 x_p_signature_date,
3263 x_s_signature_code,
3264 x_unsub_elig_for_heal,
3265 x_disclosure_print_ind,
3266 x_orig_fee_perct,
3267 x_borw_confirm_ind,
3268 x_borw_interest_ind,
3269 x_unsub_elig_for_depnt,
3270 x_guarantee_amt,
3271 x_guarantee_date,
3272 x_guarnt_adj_ind,
3273 x_guarnt_amt_redn_code,
3274 x_guarnt_status_code,
3275 x_guarnt_status_date,
3276 x_lend_apprv_denied_code,
3277 x_lend_apprv_denied_date,
3278 x_lend_status_code,
3279 x_lend_status_date,
3280 x_grade_level_code,
3281 x_enrollment_code,
3282 x_anticip_compl_date,
3283 x_borw_lender_id,
3284 x_guarantor_id,
3285 x_prc_type_code,
3286 x_rec_type_ind,
3287 x_cl_loan_type,
3288 x_cl_seq_number,
3289 x_last_resort_lender,
3290 x_lender_id,
3291 x_lend_non_ed_brc_id,
3292 x_recipient_id,
3293 x_recipient_type,
3294 x_recip_non_ed_brc_id,
3295 x_cl_rec_status,
3296 x_cl_rec_status_last_update,
3297 x_alt_prog_type_code,
3298 x_alt_appl_ver_code,
3299 x_borw_outstd_loan_code,
3300 x_mpn_confirm_code,
3301 x_resp_to_orig_code,
3302 x_appl_loan_phase_code,
3303 x_appl_loan_phase_code_chg,
3304 x_tot_outstd_stafford,
3305 x_tot_outstd_plus,
3306 x_alt_borw_tot_debt,
3307 x_act_interest_rate,
3308 x_service_type_code,
3309 x_rev_notice_of_guarnt,
3310 x_sch_refund_amt,
3311 x_sch_refund_date,
3312 x_uniq_layout_vend_code,
3313 x_uniq_layout_ident_code,
3314 x_p_person_id,
3315 x_p_ssn,
3316 x_p_last_name,
3317 x_p_first_name,
3321 x_p_permt_city,
3318 x_p_middle_name,
3319 x_p_permt_addr1,
3320 x_p_permt_addr2,
3322 x_p_permt_state,
3323 x_p_permt_zip,
3324 x_p_permt_addr_chg_date,
3325 x_p_permt_phone,
3326 x_p_email_addr,
3327 x_p_date_of_birth,
3328 x_p_license_num,
3329 x_p_license_state,
3330 x_p_citizenship_status,
3331 x_p_alien_reg_num,
3332 x_p_default_status,
3333 x_p_foreign_postal_code,
3334 x_p_state_of_legal_res,
3335 x_p_legal_res_date,
3336 x_s_ssn,
3337 x_s_last_name,
3338 x_s_first_name,
3339 x_s_middle_name,
3340 x_s_permt_addr1,
3341 x_s_permt_addr2,
3342 x_s_permt_city,
3343 x_s_permt_state,
3344 x_s_permt_zip,
3345 x_s_permt_addr_chg_date,
3346 x_s_permt_phone,
3347 x_s_local_addr1,
3348 x_s_local_addr2,
3349 x_s_local_city,
3350 x_s_local_state,
3351 x_s_local_zip,
3352 x_s_email_addr,
3353 x_s_date_of_birth,
3354 x_s_license_num,
3355 x_s_license_state,
3356 x_s_depncy_status,
3357 x_s_default_status,
3358 x_s_citizenship_status,
3359 x_s_alien_reg_num,
3360 x_s_foreign_postal_code,
3361 x_pnote_batch_id,
3362 x_pnote_ack_date,
3363 x_pnote_mpn_ind,
3364 x_award_id,
3365 x_base_id,
3366 x_loan_key_num,
3367 x_fin_award_year,
3368 x_cps_trans_num,
3369 x_pymt_servicer_amt,
3370 x_pymt_servicer_date,
3371 x_book_loan_amt,
3372 x_book_loan_amt_date,
3373 x_s_chg_ssn,
3374 x_s_chg_last_name,
3375 x_b_chg_ssn,
3376 x_b_chg_last_name,
3377 x_note_message,
3378 x_full_resp_code,
3379 x_s_permt_county,
3380 x_b_permt_county,
3381 x_s_permt_country,
3382 x_b_permt_country,
3383 x_crdt_decision_status,
3384 x_b_chg_birth_date,
3385 x_s_chg_birth_date,
3386 x_external_loan_id_txt,
3387 x_deferment_request_code,
3388 x_eft_authorization_code,
3389 x_requested_loan_amt,
3390 x_actual_record_type_code,
3391 x_reinstatement_amt,
3392 x_lender_use_txt,
3393 x_guarantor_use_txt,
3394 x_fls_approved_amt,
3395 x_flu_approved_amt,
3396 x_flp_approved_amt,
3397 x_alt_approved_amt,
3398 x_loan_app_form_code,
3399 x_alt_borrower_ind_flag,
3400 x_school_id_txt,
3401 x_cost_of_attendance_amt,
3402 x_EXPECT_FAMILY_CONTRIBUTE_AMT,
3403 x_established_fin_aid_amount,
3404 x_BOROWER_ELECTRONIC_SIGN_FLAG,
3405 x_student_electronic_sign_flag,
3406 x_BOROWER_CREDIT_AUTHORIZ_FLAG,
3407 x_mpn_type_flag,
3408 x_school_use_txt,
3409 x_document_id_txt,
3410 x_atd_entity_id_txt,
3411 x_rep_entity_id_txt,
3412 x_source_entity_id_txt,
3413 x_interest_rebate_percent_num,
3414 x_esign_src_typ_cd,
3415 x_loansh_id,
3416 x_source_txt,
3417 x_mode
3418 );
3419 RETURN;
3420 END IF;
3421 CLOSE c1;
3422
3423 update_row (
3424 x_rowid,
3425 x_loan_id,
3426 x_origination_id,
3427 x_loan_number,
3428 x_loan_type,
3429 x_loan_amt_offered,
3430 x_loan_amt_accepted,
3431 x_loan_per_begin_date,
3432 x_loan_per_end_date,
3433 x_acad_yr_begin_date,
3434 x_acad_yr_end_date,
3435 x_loan_status,
3436 x_loan_status_date,
3437 x_loan_chg_status,
3438 x_loan_chg_status_date,
3439 x_req_serial_loan_code,
3440 x_act_serial_loan_code,
3441 x_active,
3442 x_active_date,
3443 x_sch_cert_date,
3444 x_orig_status_flag,
3445 x_orig_batch_id,
3446 x_orig_batch_date,
3447 x_orig_ack_date,
3448 x_credit_override,
3449 x_credit_decision_date,
3450 x_pnote_delivery_code,
3451 x_pnote_status,
3452 x_pnote_status_date,
3453 x_pnote_id,
3454 x_pnote_print_ind,
3455 x_pnote_accept_amt,
3456 x_pnote_accept_date,
3457 x_p_signature_code,
3458 x_p_signature_date,
3459 x_s_signature_code,
3460 x_unsub_elig_for_heal,
3461 x_disclosure_print_ind,
3462 x_orig_fee_perct,
3463 x_borw_confirm_ind,
3464 x_borw_interest_ind,
3465 x_unsub_elig_for_depnt,
3466 x_guarantee_amt,
3467 x_guarantee_date,
3468 x_guarnt_adj_ind,
3469 x_guarnt_amt_redn_code,
3470 x_guarnt_status_code,
3471 x_guarnt_status_date,
3472 x_lend_apprv_denied_code,
3473 x_lend_apprv_denied_date,
3474 x_lend_status_code,
3475 x_lend_status_date,
3476 x_grade_level_code,
3477 x_enrollment_code,
3478 x_anticip_compl_date,
3479 x_borw_lender_id,
3480 x_guarantor_id,
3481 x_prc_type_code,
3482 x_rec_type_ind,
3483 x_cl_loan_type,
3484 x_cl_seq_number,
3485 x_last_resort_lender,
3486 x_lender_id,
3487 x_lend_non_ed_brc_id,
3488 x_recipient_id,
3489 x_recipient_type,
3490 x_recip_non_ed_brc_id,
3491 x_cl_rec_status,
3492 x_cl_rec_status_last_update,
3493 x_alt_prog_type_code,
3494 x_alt_appl_ver_code,
3495 x_borw_outstd_loan_code,
3496 x_mpn_confirm_code,
3497 x_resp_to_orig_code,
3498 x_appl_loan_phase_code,
3499 x_appl_loan_phase_code_chg,
3500 x_tot_outstd_stafford,
3501 x_tot_outstd_plus,
3502 x_alt_borw_tot_debt,
3503 x_act_interest_rate,
3504 x_service_type_code,
3505 x_rev_notice_of_guarnt,
3506 x_sch_refund_amt,
3507 x_sch_refund_date,
3508 x_uniq_layout_vend_code,
3509 x_uniq_layout_ident_code,
3510 x_p_person_id,
3511 x_p_ssn,
3512 x_p_last_name,
3513 x_p_first_name,
3514 x_p_middle_name,
3515 x_p_permt_addr1,
3516 x_p_permt_addr2,
3517 x_p_permt_city,
3518 x_p_permt_state,
3519 x_p_permt_zip,
3520 x_p_permt_addr_chg_date,
3521 x_p_permt_phone,
3522 x_p_email_addr,
3523 x_p_date_of_birth,
3524 x_p_license_num,
3525 x_p_license_state,
3526 x_p_citizenship_status,
3527 x_p_alien_reg_num,
3528 x_p_default_status,
3529 x_p_foreign_postal_code,
3530 x_p_state_of_legal_res,
3531 x_p_legal_res_date,
3532 x_s_ssn,
3533 x_s_last_name,
3534 x_s_first_name,
3535 x_s_middle_name,
3536 x_s_permt_addr1,
3537 x_s_permt_addr2,
3538 x_s_permt_city,
3539 x_s_permt_state,
3540 x_s_permt_zip,
3541 x_s_permt_addr_chg_date,
3542 x_s_permt_phone,
3543 x_s_local_addr1,
3544 x_s_local_addr2,
3545 x_s_local_city,
3546 x_s_local_state,
3547 x_s_local_zip,
3548 x_s_email_addr,
3549 x_s_date_of_birth,
3550 x_s_license_num,
3551 x_s_license_state,
3552 x_s_depncy_status,
3553 x_s_default_status,
3554 x_s_citizenship_status,
3555 x_s_alien_reg_num,
3556 x_s_foreign_postal_code,
3557 x_pnote_batch_id,
3558 x_pnote_ack_date,
3559 x_pnote_mpn_ind,
3560 x_award_id,
3561 x_base_id,
3562 x_loan_key_num,
3563 x_fin_award_year,
3564 x_cps_trans_num,
3565 x_pymt_servicer_amt,
3566 x_pymt_servicer_date,
3567 x_book_loan_amt,
3568 x_book_loan_amt_date,
3569 x_s_chg_ssn,
3570 x_s_chg_last_name,
3571 x_b_chg_ssn,
3572 x_b_chg_last_name,
3573 x_note_message,
3574 x_full_resp_code,
3575 x_s_permt_county,
3576 x_b_permt_county,
3577 x_s_permt_country,
3578 x_b_permt_country,
3579 x_crdt_decision_status,
3580 x_b_chg_birth_date,
3581 x_s_chg_birth_date,
3582 x_external_loan_id_txt,
3583 x_deferment_request_code,
3584 x_eft_authorization_code,
3585 x_requested_loan_amt,
3586 x_actual_record_type_code,
3587 x_reinstatement_amt,
3588 x_lender_use_txt,
3589 x_guarantor_use_txt,
3590 x_fls_approved_amt,
3591 x_flu_approved_amt,
3592 x_flp_approved_amt,
3593 x_alt_approved_amt,
3594 x_loan_app_form_code,
3595 x_alt_borrower_ind_flag,
3596 x_school_id_txt,
3597 x_cost_of_attendance_amt,
3598 x_EXPECT_FAMILY_CONTRIBUTE_AMT,
3599 x_established_fin_aid_amount,
3600 x_BOROWER_ELECTRONIC_SIGN_FLAG,
3601 x_student_electronic_sign_flag,
3602 x_BOROWER_CREDIT_AUTHORIZ_FLAG,
3603 x_mpn_type_flag,
3604 x_school_use_txt,
3605 x_document_id_txt,
3606 x_atd_entity_id_txt,
3607 x_rep_entity_id_txt,
3608 x_source_entity_id_txt,
3609 x_interest_rebate_percent_num,
3610 x_esign_src_typ_cd,
3611 x_loansh_id,
3612 x_source_txt,
3613 x_mode
3614 );
3615
3616 END add_row;
3617
3618
3619 PROCEDURE delete_row (
3620 x_rowid IN VARCHAR2
3621 ) AS
3622 /*
3623 || Created By : [email protected]
3624 || Created On : 03-NOV-2004
3625 || Purpose : Handles the DELETE DML logic for the table.
3626 || Known limitations, enhancements or remarks :
3627 || Change History :
3628 || Who When What
3629 || (reverse chronological order - newest change first)
3630 */
3631 BEGIN
3632
3633 before_dml (
3634 p_action => 'DELETE',
3635 x_rowid => x_rowid
3636 );
3637
3638 DELETE FROM igf_sl_lor_loc_history
3639 WHERE rowid = x_rowid;
3640
3641 IF (SQL%NOTFOUND) THEN
3642 RAISE NO_DATA_FOUND;
3643 END IF;
3644
3645 END delete_row;
3646
3647
3648 END igf_sl_lor_loc_history_pkg;