[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_REJ_WF
Source
1 PACKAGE BODY igf_sl_rej_wf AS
2 /* $Header: IGFSL18B.pls 120.2 2006/08/08 06:59:36 veramach noship $ */
3
4 /*************************************************************
5 Created By : viramali
6 Date Created On : 2001/05/15
7 Purpose :
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 -------------------------------------------------------------------------------------
12 -- akomurav 27-Jan-2006 Build FA161 and FA162.
13 -- TBH Impact change done in manif_loan(), reprint_loan().
14 --------------------------------------------------------------------------------------
15 -- svuppala 4-Nov-2004 #3416936 FA 134 TBH impacts for newly added columns
16 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
17 -- a) Impact of adding the relationship_cd
18 -- in igf_sl_lor_all table and obsoleting
19 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
20 -- GUARANTOR_ID, DUNS_GUARNT_ID,
21 -- LENDER_ID, DUNS_LENDER_ID
22 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
23 -- RECIPIENT_TYPE,DUNS_RECIP_ID
24 -- RECIP_NON_ED_BRC_ID columns.
25 --------------------------------------------------------------------------------------
26 veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
27 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
28 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
29 chg_batch_id,appl_send_error_codes from igf_sl_lor
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 PROCEDURE send_notif(
34 itemtype IN VARCHAR2,
35 itemkey IN VARCHAR2,
36 actid IN NUMBER,
37 funcmode IN VARCHAR2,
38 resultout OUT NOCOPY VARCHAR2 )
39 AS
40 /*************************************************************
41 Created By : viramali
42 Date Created On : 2001/05/15
43 Purpose :
44 Know limitations, enhancements or remarks
45 Change History
46 Who When What
47 smvk 21-Feb-2003 Bug # 2758823. Work flow event is triggered with proper lookupcode / meaning
48 (reverse chronological order - newest change first)
49 ***************************************************************/
50 l_batchid igf_sl_dl_batch.dbth_id%TYPE;
51 l_loanid igf_sl_loans_v.loan_id%TYPE;
52 l_loannum igf_sl_loans_v.loan_number%TYPE;
53 l_name igf_sl_loans_v.full_name%TYPE;
54 l_ssn igf_sl_loans_v.s_ssn%TYPE;
55 l_pernum igf_sl_loans_v.person_number%TYPE;
56 l_reject_code igf_sl_dl_lor_resp.orig_reject_reasons%TYPE;
57 l_reject_text VARCHAR2(2000);
58 l_label igf_lookups_view.meaning%TYPE;
59 l_heading VARCHAR2(500);
60 l_role VARCHAR2(30);
61 l_n_incr NUMBER;
62 l_c_reject_code igf_sl_dl_lor_resp.orig_reject_reasons%TYPE;
63
64
65 CURSOR c_loan_details IS
66 SELECT loan_number,
67 full_name,
68 s_ssn,
69 person_number
70 FROM igf_sl_loans_v
71 WHERE loan_id = l_loanid ;
72
73 CURSOR c_pnote_resp IS
74 SELECT igf_sl_dl_pnote_resp.pnote_rej_codes
75 FROM igf_sl_dl_pnote_resp
76 WHERE dbth_id = l_batchid
77 AND loan_number = (SELECT loan_number
78 FROM igf_sl_loans
79 WHERE loan_id = l_loanid);
80
81
82 CURSOR c_rej IS
83 SELECT lookup_code, meaning
84 FROM igf_lookups_view
85 WHERE lookup_type = 'IGF_SL_PNOTE_REJ_CODES'
86 AND lookup_code IN (LTRIM(RTRIM(SUBSTR(l_reject_code, 1,2))),
87 LTRIM(RTRIM(SUBSTR(l_reject_code, 3,2))),
88 LTRIM(RTRIM(SUBSTR(l_reject_code, 5,2))),
89 LTRIM(RTRIM(SUBSTR(l_reject_code, 7,2))),
90 LTRIM(RTRIM(SUBSTR(l_reject_code, 9,2))));
91
92
93 BEGIN
94 IF ( funcmode = 'RUN' ) THEN
95
96 l_loanid := wf_engine.GetItemAttrNumber(itemtype,
97 itemkey,
98 'VLOANID');
99 l_batchid := wf_engine.GetItemAttrNumber(itemtype,
100 itemkey,
101 'VDBTHID');
102
103
104
105
106 OPEN c_loan_details;
107 FETCH c_loan_details INTO l_loannum,l_name,l_ssn,l_pernum;
108 IF c_loan_details%NOTFOUND THEN
109 CLOSE c_loan_details;
110 RAISE NO_DATA_FOUND;
111 END IF;
112 CLOSE c_loan_details;
113
114 -- setting values to the attributes: loan number,fullname,ssn and person number,performer role
115 l_role := fnd_global.user_name;
116 wf_engine.setitemattrtext(itemtype,itemkey,
117 'VROLE',l_role);
118 wf_engine.SetItemAttrText(itemtype,
119 itemkey,
120 'VLOANNUM',
121 l_loannum);
122
123 wf_engine.SetItemAttrText(itemtype,
124 itemkey,
125 'VNAME',
126 l_name);
127
128 wf_engine.SetItemAttrText(itemtype,
129 itemkey,
130 'VSSN',
131 l_ssn);
132 wf_engine.SetItemAttrText(itemtype,
133 itemkey,
134 'VPERNUM',
135 l_pernum);
136
137
138 -- populate reject codes and reasons
139
140 OPEN c_pnote_resp;
141 FETCH c_pnote_resp INTO l_reject_code;
142 IF c_pnote_resp%NOTFOUND THEN
143 CLOSE c_pnote_resp;
144 RAISE NO_DATA_FOUND;
145 END IF;
146 CLOSE c_pnote_resp;
147
148 l_c_reject_code := l_reject_code;
149 l_reject_code := TRANSLATE (l_reject_code,'0',' ');
150 l_reject_text := '';
151
152 l_n_incr := 1;
153
154 FOR rej_rec in c_rej LOOP
155
156 l_reject_text := l_reject_text ||
157 SUBSTR (l_c_reject_code,l_n_incr,2) ||
158 ' ' ||
159 rej_rec.meaning ||
160 fnd_global.newline;
161
162 l_n_incr := l_n_incr + 2;
163
164 END LOOP;
165
166 wf_engine.SetItemAttrText(itemtype,
167 itemkey,
168 'VREJ_REASONS',
169 l_reject_text);
170
171
172
173 -- populating label attributes
174
175 l_label := igf_aw_gen.lookup_desc ('IGF_SL_LOAN_FIELDS', 'S_FULL_NAME');
176
177 wf_engine.SetItemAttrText(itemtype,
178 itemkey,
179 'VNAME_LABEL',
180 l_label);
181
182
183 l_label := igf_aw_gen.lookup_desc ('IGF_SL_LOAN_FIELDS', 'S_SSN');
184
185 wf_engine.SetItemAttrText(itemtype,
186 itemkey,
187 'VSSN_LABEL',
188 l_label);
189
190
191 l_label := igf_aw_gen.lookup_desc ('IGF_SL_LOAN_FIELDS', 'S_PER_NUM');
192
193 wf_engine.SetItemAttrText(itemtype,
194 itemkey,
195 'VPERNUM_LABEL',
196 l_label);
197
198 l_label := igf_aw_gen.lookup_desc ('IGF_SL_LOAN_FIELDS', 'LOAN_ID');
199
200 wf_engine.SetItemAttrText(itemtype,
201 itemkey,
202 'VLOAN_LABEL',
203 l_label);
204
205 l_label := igf_aw_gen.lookup_desc ('IGF_SL_LOAN_FIELDS', 'S_REJECT_NUM');
206
207 wf_engine.SetItemAttrText(itemtype,
208 itemkey,
209 'VREJ_LABEL',
210 l_label);
211
212 l_label := igf_aw_gen.lookup_desc ('IGF_SL_LOAN_FIELDS', 'S_REJECT_DESC');
213
214 wf_engine.SetItemAttrText(itemtype,
215 itemkey,
216 'VDESC_LABEL',
217 l_label);
218
219 -- populate header attribute
220
221 fnd_message.set_name('IGF','IGF_SL_WF_PNOTE_REJECT');
222 l_heading := fnd_message.get;
223
224 wf_engine.SetItemAttrText(itemtype,
225 itemkey,
226 'VHEADING',
227 l_heading);
228
229 resultout := 'COMPLETE:';
230 END IF;
231
232 EXCEPTION
233
234 WHEN OTHERS THEN
235 WF_CORE.CONTEXT ('igf_sl_rej_wf', 'send_notif', itemtype, itemkey,
236 to_char(actid), funcmode);
237 RAISE;
238
239 END send_notif;
240
241 PROCEDURE manif_loan(
242 itemtype IN VARCHAR2,
243 itemkey IN VARCHAR2,
244 actid IN NUMBER,
245 funcmode IN VARCHAR2,
246 resultout OUT NOCOPY VARCHAR2 )
247 AS
248 /*************************************************************
249 Created By : viramali
250 Date Created On : 2001/05/15
251 Purpose :
252 Know limitations, enhancements or remarks
253 Change History
254 Who When What
255 -------------------------------------------------------------------------------------
256 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
257 -- a) Impact of adding the relationship_cd
258 -- in igf_sl_lor_all table and obsoleting
259 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
260 -- GUARANTOR_ID, DUNS_GUARNT_ID,
261 -- LENDER_ID, DUNS_LENDER_ID
262 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
263 -- RECIPIENT_TYPE,DUNS_RECIP_ID
264 -- RECIP_NON_ED_BRC_ID columns.
265 --------------------------------------------------------------------------------------
266 veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
267 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
268 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
269 chg_batch_id,appl_send_error_codes from igf_sl_lor
270 (reverse chronological order - newest change first)
271 ***************************************************************/
272
273 l_loanid igf_sl_loans_v.loan_id%TYPE;
274 l_tbh_rec igf_sl_lor%ROWTYPE;
275
276 CURSOR c_tbh_cur IS
277 SELECT * FROM igf_sl_lor
278 WHERE loan_id = l_loanid
279 AND pnote_status = 'R'
280 FOR UPDATE OF igf_sl_lor.pnote_status NOWAIT;
281
282
283 BEGIN
284
285 IF ( funcmode = 'RUN' ) THEN
286
287 l_loanid := wf_engine.GetItemAttrNumber(itemtype,
288 itemkey,
289 'VLOANID');
290
291
292 OPEN c_tbh_cur ;
293 FETCH c_tbh_cur INTO l_tbh_rec;
294 IF c_tbh_cur%NOTFOUND THEN
295 CLOSE c_tbh_cur;
296 RAISE NO_DATA_FOUND;
297 END IF;
298 CLOSE c_tbh_cur;
299
300
301 igf_sl_lor_pkg.update_row( x_mode => 'R',
302 x_rowid => l_tbh_rec.row_id,
303 x_origination_id => l_tbh_rec.origination_id,
304 x_loan_id => l_tbh_rec.loan_id,
305 x_sch_cert_date => l_tbh_rec.sch_cert_date,
306 x_orig_status_flag => l_tbh_rec.orig_status_flag,
307 x_orig_batch_id => l_tbh_rec.orig_batch_id,
308 x_orig_batch_date => l_tbh_rec.orig_batch_date,
309 x_chg_batch_id => NULL,
310 x_orig_ack_date => l_tbh_rec.orig_ack_date,
311 x_credit_override => l_tbh_rec.credit_override,
312 x_credit_decision_date => l_tbh_rec.credit_decision_date,
313 x_req_serial_loan_code => l_tbh_rec.req_serial_loan_code,
314 x_act_serial_loan_code => l_tbh_rec.act_serial_loan_code,
315 x_pnote_delivery_code => l_tbh_rec.pnote_delivery_code,
316 x_pnote_status => 'S',
317 x_pnote_status_date => TRUNC(SYSDATE),
318 x_pnote_id => l_tbh_rec.pnote_id,
319 x_pnote_print_ind => l_tbh_rec.pnote_print_ind,
320 x_pnote_accept_amt => l_tbh_rec.pnote_accept_amt,
321 x_pnote_accept_date => l_tbh_rec.pnote_accept_date,
322 x_pnote_batch_id => l_tbh_rec.pnote_batch_id,
323 x_pnote_ack_date => l_tbh_rec.pnote_ack_date,
324 x_pnote_mpn_ind => l_tbh_rec.pnote_mpn_ind,
325 x_unsub_elig_for_heal => l_tbh_rec.unsub_elig_for_heal,
326 x_disclosure_print_ind => l_tbh_rec.disclosure_print_ind,
327 x_orig_fee_perct => l_tbh_rec.orig_fee_perct,
328 x_borw_confirm_ind => l_tbh_rec.borw_confirm_ind,
329 x_borw_interest_ind => l_tbh_rec.borw_interest_ind,
330 x_borw_outstd_loan_code => l_tbh_rec.borw_outstd_loan_code,
331 x_unsub_elig_for_depnt => l_tbh_rec.unsub_elig_for_depnt,
332 x_guarantee_amt => l_tbh_rec.guarantee_amt,
333 x_guarantee_date => l_tbh_rec.guarantee_date,
334 x_guarnt_amt_redn_code => l_tbh_rec.guarnt_amt_redn_code,
335 x_guarnt_status_code => l_tbh_rec.guarnt_status_code,
336 x_guarnt_status_date => l_tbh_rec.guarnt_status_date,
337 x_lend_apprv_denied_code => NULL,
338 x_lend_apprv_denied_date => NULL,
339 x_lend_status_code => l_tbh_rec.lend_status_code,
340 x_lend_status_date => l_tbh_rec.lend_status_date,
341 x_guarnt_adj_ind => l_tbh_rec.guarnt_adj_ind,
342 x_grade_level_code => l_tbh_rec.grade_level_code,
343 x_enrollment_code => l_tbh_rec.enrollment_code,
344 x_anticip_compl_date => l_tbh_rec.anticip_compl_date,
345 x_borw_lender_id => NULL,
346 x_duns_borw_lender_id => NULL,
347 x_guarantor_id => NULL,
348 x_duns_guarnt_id => NULL,
349 x_prc_type_code => l_tbh_rec.prc_type_code,
350 x_cl_seq_number => l_tbh_rec.cl_seq_number,
351 x_last_resort_lender => l_tbh_rec.last_resort_lender,
352 x_lender_id => NULL,
353 x_duns_lender_id => NULL,
354 x_lend_non_ed_brc_id => NULL,
355 x_recipient_id => NULL,
356 x_recipient_type => NULL,
357 x_duns_recip_id => NULL,
358 x_recip_non_ed_brc_id => NULL,
359 x_rec_type_ind => l_tbh_rec.rec_type_ind,
360 x_cl_loan_type => l_tbh_rec.cl_loan_type,
361 x_cl_rec_status => NULL,
362 x_cl_rec_status_last_update => NULL,
363 x_alt_prog_type_code => l_tbh_rec.alt_prog_type_code,
364 x_alt_appl_ver_code => l_tbh_rec.alt_appl_ver_code,
365 x_mpn_confirm_code => NULL,
366 x_resp_to_orig_code => l_tbh_rec.resp_to_orig_code,
367 x_appl_loan_phase_code => NULL,
368 x_appl_loan_phase_code_chg => NULL,
369 x_appl_send_error_codes => NULL,
370 x_tot_outstd_stafford => l_tbh_rec.tot_outstd_stafford,
371 x_tot_outstd_plus => l_tbh_rec.tot_outstd_plus,
372 x_alt_borw_tot_debt => l_tbh_rec.alt_borw_tot_debt,
373 x_act_interest_rate => l_tbh_rec.act_interest_rate,
374 x_service_type_code => l_tbh_rec.service_type_code,
375 x_rev_notice_of_guarnt => l_tbh_rec.rev_notice_of_guarnt,
376 x_sch_refund_amt => l_tbh_rec.sch_refund_amt,
377 x_sch_refund_date => l_tbh_rec.sch_refund_date,
378 x_uniq_layout_vend_code => l_tbh_rec.uniq_layout_vend_code,
379 x_uniq_layout_ident_code => l_tbh_rec.uniq_layout_ident_code,
380 x_p_person_id => l_tbh_rec.p_person_id,
381 x_p_ssn_chg_date => NULL,
382 x_p_dob_chg_date => NULL,
383 x_p_permt_addr_chg_date => l_tbh_rec.p_permt_addr_chg_date,
384 x_p_default_status => l_tbh_rec.p_default_status,
385 x_p_signature_code => l_tbh_rec.p_signature_code,
386 x_p_signature_date => l_tbh_rec.p_signature_date,
387 x_s_ssn_chg_date => NULL,
388 x_s_dob_chg_date => NULL,
389 x_s_permt_addr_chg_date => l_tbh_rec.s_permt_addr_chg_date,
390 x_s_local_addr_chg_date => NULL,
391 x_s_default_status => l_tbh_rec.s_default_status,
392 x_s_signature_code => l_tbh_rec.s_signature_code,
393 x_elec_mpn_ind => l_tbh_rec.elec_mpn_ind,
394 x_borr_sign_ind => l_tbh_rec.borr_sign_ind,
395 x_stud_sign_ind => l_tbh_rec.stud_sign_ind,
396 x_borr_credit_auth_code => l_tbh_rec.borr_credit_auth_code,
397 x_relationship_cd => l_tbh_rec.relationship_cd,
398 x_interest_rebate_percent_num => l_tbh_rec.interest_rebate_percent_num,
399 x_cps_trans_num => l_tbh_rec.cps_trans_num,
400 x_atd_entity_id_txt => l_tbh_rec.atd_entity_id_txt ,
401 x_rep_entity_id_txt => l_tbh_rec.rep_entity_id_txt,
402 x_crdt_decision_status => l_tbh_rec.crdt_decision_status,
403 x_note_message => l_tbh_rec.note_message,
404 x_book_loan_amt => l_tbh_rec.book_loan_amt ,
405 x_book_loan_amt_date => l_tbh_rec.book_loan_amt_date,
406 x_pymt_servicer_amt => l_tbh_rec.pymt_servicer_amt,
407 x_pymt_servicer_date => l_tbh_rec.pymt_servicer_date,
408 x_requested_loan_amt => l_tbh_rec.requested_loan_amt,
409 x_eft_authorization_code => l_tbh_rec.eft_authorization_code,
410 x_external_loan_id_txt => l_tbh_rec.external_loan_id_txt,
411 x_deferment_request_code => l_tbh_rec.deferment_request_code ,
412 x_actual_record_type_code => l_tbh_rec.actual_record_type_code,
413 x_reinstatement_amt => l_tbh_rec.reinstatement_amt,
414 x_school_use_txt => l_tbh_rec.school_use_txt,
415 x_lender_use_txt => l_tbh_rec.lender_use_txt,
416 x_guarantor_use_txt => l_tbh_rec.guarantor_use_txt,
417 x_fls_approved_amt => l_tbh_rec.fls_approved_amt,
418 x_flu_approved_amt => l_tbh_rec.flu_approved_amt,
419 x_flp_approved_amt => l_tbh_rec.flp_approved_amt,
420 x_alt_approved_amt => l_tbh_rec.alt_approved_amt,
421 x_loan_app_form_code => l_tbh_rec.loan_app_form_code,
422 x_override_grade_level_code => l_tbh_rec.override_grade_level_code,
423 x_b_alien_reg_num_txt => l_tbh_rec.b_alien_reg_num_txt,
424 x_esign_src_typ_cd => l_tbh_rec.esign_src_typ_cd,
425 x_acad_begin_date => l_tbh_rec.acad_begin_date,
426 x_acad_end_date => l_tbh_rec.acad_end_date
427
428 );
429 resultout := 'COMPLETE:';
430 END IF;
431
432 EXCEPTION
433
434 WHEN OTHERS THEN
435 WF_CORE.CONTEXT ('igf_sl_rej_wf', 'send_notif', itemtype, itemkey,
436 to_char(actid), funcmode);
437 RAISE;
438
439
440 END manif_loan;
441
442
443 PROCEDURE reprint_loan(
444 itemtype IN VARCHAR2,
445 itemkey IN VARCHAR2,
446 actid IN NUMBER,
447 funcmode IN VARCHAR2,
448 resultout OUT NOCOPY VARCHAR2 )
449 AS
450 /*************************************************************
451 Created By : viramali
452 Date Created On : 2001/05/15
453 Purpose :
454 Know limitations, enhancements or remarks
455 Change History
456 Who When What
457 -------------------------------------------------------------------------------------
458 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
459 -- a) Impact of adding the relationship_cd
460 -- in igf_sl_lor_all table and obsoleting
461 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
462 -- GUARANTOR_ID, DUNS_GUARNT_ID,
463 -- LENDER_ID, DUNS_LENDER_ID
464 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
465 -- RECIPIENT_TYPE,DUNS_RECIP_ID
466 -- RECIP_NON_ED_BRC_ID columns.
467 --------------------------------------------------------------------------------------
468 veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
469 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
470 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
471 chg_batch_id,appl_send_error_codes from igf_sl_lor
472 (reverse chronological order - newest change first)
473 ***************************************************************/
474 l_loanid igf_sl_loans_v.loan_id%TYPE;
475 l_tbh_rec igf_sl_lor%ROWTYPE;
476
477 CURSOR c_tbh_cur IS
478 SELECT * FROM igf_sl_lor
479 WHERE loan_id = l_loanid
480 AND pnote_status = 'R'
481 FOR UPDATE OF igf_sl_lor.pnote_status NOWAIT;
482
483
484 BEGIN
485
486 IF ( funcmode = 'RUN' ) THEN
487
488 l_loanid := wf_engine.GetItemAttrNumber(itemtype,
489 itemkey,
490 'VLOANID');
491
492
493 OPEN c_tbh_cur ;
494 FETCH c_tbh_cur INTO l_tbh_rec;
495 IF c_tbh_cur%NOTFOUND THEN
496 CLOSE c_tbh_cur;
497 RAISE NO_DATA_FOUND;
498 END IF;
499 CLOSE c_tbh_cur;
500
501 igf_sl_lor_pkg.update_row(
502 x_mode => 'R',
503 x_rowid => l_tbh_rec.row_id,
504 x_origination_id => l_tbh_rec.origination_id,
505 x_loan_id => l_tbh_rec.loan_id,
506 x_sch_cert_date => l_tbh_rec.sch_cert_date,
507 x_orig_status_flag => l_tbh_rec.orig_status_flag,
508 x_orig_batch_id => l_tbh_rec.orig_batch_id,
509 x_orig_batch_date => l_tbh_rec.orig_batch_date,
510 x_chg_batch_id => NULL,
511 x_orig_ack_date => l_tbh_rec.orig_ack_date,
512 x_credit_override => l_tbh_rec.credit_override,
513 x_credit_decision_date => l_tbh_rec.credit_decision_date,
514 x_req_serial_loan_code => l_tbh_rec.req_serial_loan_code,
515 x_act_serial_loan_code => l_tbh_rec.act_serial_loan_code,
516 x_pnote_delivery_code => l_tbh_rec.pnote_delivery_code,
517 x_pnote_status => 'G',
518 x_pnote_status_date => TRUNC(SYSDATE),
519 x_pnote_id => l_tbh_rec.pnote_id,
520 x_pnote_print_ind => l_tbh_rec.pnote_print_ind,
521 x_pnote_accept_amt => l_tbh_rec.pnote_accept_amt,
522 x_pnote_accept_date => l_tbh_rec.pnote_accept_date,
523 x_pnote_batch_id => l_tbh_rec.pnote_batch_id,
524 x_pnote_ack_date => l_tbh_rec.pnote_ack_date,
525 x_pnote_mpn_ind => l_tbh_rec.pnote_mpn_ind,
526 x_unsub_elig_for_heal => l_tbh_rec.unsub_elig_for_heal,
527 x_disclosure_print_ind => l_tbh_rec.disclosure_print_ind,
528 x_orig_fee_perct => l_tbh_rec.orig_fee_perct,
529 x_borw_confirm_ind => l_tbh_rec.borw_confirm_ind,
530 x_borw_interest_ind => l_tbh_rec.borw_interest_ind,
531 x_borw_outstd_loan_code => l_tbh_rec.borw_outstd_loan_code,
532 x_unsub_elig_for_depnt => l_tbh_rec.unsub_elig_for_depnt,
533 x_guarantee_amt => l_tbh_rec.guarantee_amt,
534 x_guarantee_date => l_tbh_rec.guarantee_date,
535 x_guarnt_amt_redn_code => l_tbh_rec.guarnt_amt_redn_code,
536 x_guarnt_status_code => l_tbh_rec.guarnt_status_code,
537 x_guarnt_status_date => l_tbh_rec.guarnt_status_date,
538 x_lend_apprv_denied_code => NULL,
539 x_lend_apprv_denied_date => NULL,
540 x_lend_status_code => l_tbh_rec.lend_status_code,
541 x_lend_status_date => l_tbh_rec.lend_status_date,
542 x_guarnt_adj_ind => l_tbh_rec.guarnt_adj_ind,
543 x_grade_level_code => l_tbh_rec.grade_level_code,
544 x_enrollment_code => l_tbh_rec.enrollment_code,
545 x_anticip_compl_date => l_tbh_rec.anticip_compl_date,
546 x_borw_lender_id => NULL,
547 x_duns_borw_lender_id => NULL,
548 x_guarantor_id => NULL,
549 x_duns_guarnt_id => NULL,
550 x_prc_type_code => l_tbh_rec.prc_type_code,
551 x_cl_seq_number => l_tbh_rec.cl_seq_number,
552 x_last_resort_lender => l_tbh_rec.last_resort_lender,
553 x_lender_id => NULL,
554 x_duns_lender_id => NULL,
555 x_lend_non_ed_brc_id => NULL,
556 x_recipient_id => NULL,
557 x_recipient_type => NULL,
558 x_duns_recip_id => NULL,
559 x_recip_non_ed_brc_id => NULL,
560 x_rec_type_ind => l_tbh_rec.rec_type_ind,
561 x_cl_loan_type => l_tbh_rec.cl_loan_type,
562 x_cl_rec_status => NULL,
563 x_cl_rec_status_last_update => NULL,
564 x_alt_prog_type_code => l_tbh_rec.alt_prog_type_code,
565 x_alt_appl_ver_code => l_tbh_rec.alt_appl_ver_code,
566 x_mpn_confirm_code => NULL,
567 x_resp_to_orig_code => l_tbh_rec.resp_to_orig_code,
568 x_appl_loan_phase_code => NULL,
569 x_appl_loan_phase_code_chg => NULL,
570 x_appl_send_error_codes => NULL,
571 x_tot_outstd_stafford => l_tbh_rec.tot_outstd_stafford,
572 x_tot_outstd_plus => l_tbh_rec.tot_outstd_plus,
573 x_alt_borw_tot_debt => l_tbh_rec.alt_borw_tot_debt,
574 x_act_interest_rate => l_tbh_rec.act_interest_rate,
575 x_service_type_code => l_tbh_rec.service_type_code,
576 x_rev_notice_of_guarnt => l_tbh_rec.rev_notice_of_guarnt,
577 x_sch_refund_amt => l_tbh_rec.sch_refund_amt,
578 x_sch_refund_date => l_tbh_rec.sch_refund_date,
579 x_uniq_layout_vend_code => l_tbh_rec.uniq_layout_vend_code,
580 x_uniq_layout_ident_code => l_tbh_rec.uniq_layout_ident_code,
581 x_p_person_id => l_tbh_rec.p_person_id,
582 x_p_ssn_chg_date => NULL,
583 x_p_dob_chg_date => NULL,
584 x_p_permt_addr_chg_date => l_tbh_rec.p_permt_addr_chg_date,
585 x_p_default_status => l_tbh_rec.p_default_status,
586 x_p_signature_code => l_tbh_rec.p_signature_code,
587 x_p_signature_date => l_tbh_rec.p_signature_date,
588 x_s_ssn_chg_date => NULL,
589 x_s_dob_chg_date => NULL,
590 x_s_permt_addr_chg_date => l_tbh_rec.s_permt_addr_chg_date,
591 x_s_local_addr_chg_date => NULL,
592 x_s_default_status => l_tbh_rec.s_default_status,
593 x_s_signature_code => l_tbh_rec.s_signature_code,
594 x_elec_mpn_ind => l_tbh_rec.elec_mpn_ind,
595 x_borr_sign_ind => l_tbh_rec.borr_sign_ind,
596 x_stud_sign_ind => l_tbh_rec.stud_sign_ind,
597 x_borr_credit_auth_code => l_tbh_rec.borr_credit_auth_code,
598 x_relationship_cd => l_tbh_rec.relationship_cd,
599 x_interest_rebate_percent_num => l_tbh_rec.interest_rebate_percent_num,
600 x_cps_trans_num => l_tbh_rec.cps_trans_num,
601 x_atd_entity_id_txt => l_tbh_rec.atd_entity_id_txt ,
602 x_rep_entity_id_txt => l_tbh_rec.rep_entity_id_txt,
603 x_crdt_decision_status => l_tbh_rec.crdt_decision_status,
604 x_note_message => l_tbh_rec.note_message,
605 x_book_loan_amt => l_tbh_rec.book_loan_amt ,
606 x_book_loan_amt_date => l_tbh_rec.book_loan_amt_date,
607 x_pymt_servicer_amt => l_tbh_rec.pymt_servicer_amt,
608 x_pymt_servicer_date => l_tbh_rec.pymt_servicer_date,
609 x_requested_loan_amt => l_tbh_rec.requested_loan_amt,
610 x_eft_authorization_code => l_tbh_rec.eft_authorization_code,
611 x_external_loan_id_txt => l_tbh_rec.external_loan_id_txt,
612 x_deferment_request_code => l_tbh_rec.deferment_request_code ,
613 x_actual_record_type_code => l_tbh_rec.actual_record_type_code,
614 x_reinstatement_amt => l_tbh_rec.reinstatement_amt,
615 x_school_use_txt => l_tbh_rec.school_use_txt,
616 x_lender_use_txt => l_tbh_rec.lender_use_txt,
617 x_guarantor_use_txt => l_tbh_rec.guarantor_use_txt,
618 x_fls_approved_amt => l_tbh_rec.fls_approved_amt,
619 x_flu_approved_amt => l_tbh_rec.flu_approved_amt,
620 x_flp_approved_amt => l_tbh_rec.flp_approved_amt,
621 x_alt_approved_amt => l_tbh_rec.alt_approved_amt,
622 x_loan_app_form_code => l_tbh_rec.loan_app_form_code,
623 x_override_grade_level_code => l_tbh_rec.override_grade_level_code,
624 x_b_alien_reg_num_txt => l_tbh_rec.b_alien_reg_num_txt,
625 x_esign_src_typ_cd => l_tbh_rec.esign_src_typ_cd,
626 x_acad_begin_date => l_tbh_rec.acad_begin_date,
627 x_acad_end_date => l_tbh_rec.acad_end_date
628
629 );
630 resultout := 'COMPLETE:';
631 END IF;
632
633 EXCEPTION
634
635 WHEN OTHERS THEN
636 WF_CORE.CONTEXT ('igf_sl_rej_wf', 'send_notif', itemtype, itemkey,
637 to_char(actid), funcmode);
638 RAISE;
639
640
641 END reprint_loan;
642
643 END igf_sl_rej_wf;