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