[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_PRINT_PNOTE
Source
1 PACKAGE BODY igf_sl_dl_print_pnote AS
2 /* $Header: IGFSL16B.pls 120.1 2006/04/18 23:19:15 akomurav noship $ */
3 /***************************************************************
4 Created By : avenkatr
5 Date Created By : 2001/05/08
6 Purpose : To Print and process the Promissory note
7 Known Limitations,Enhancements or Remarks
8 Change History :
9 Who When What
10 -------------------------------------------------------------------------------------
11 -- akomurav 17-Apr-2006 Build FA161 and 162.
12 -- TBH Impact change done in update_pnote_status().
13 -------------------------------------------------------------------------------------
14 -- svuppala 4-Nov-2004 #3416936 FA 134 TBH impacts for newly added columns
15 -------------------------------------------------------------------------------------
16
17 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
18 -- a) Impact of adding the relationship_cd
19 -- in igf_sl_lor_all table and obsoleting
20 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
21 -- GUARANTOR_ID, DUNS_GUARNT_ID,
22 -- LENDER_ID, DUNS_LENDER_ID
23 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
24 -- RECIPIENT_TYPE,DUNS_RECIP_ID
25 -- RECIP_NON_ED_BRC_ID columns.
26 --------------------------------------------------------------------------------------
27 veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
28 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
29 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
30 chg_batch_id,appl_send_error_codes from igf_sl_lor
31 veramach 16-SEP-2003 FA 122 loan enhancements build
32 1.Added update_pnote_status method
33 2.Changed cursors c_stafford_det and c_plus_det to remove references of igf_sl_plus_borw.
34 3.the borrower info is now derived using igf_sl_gen.get_person_details procedure
35 Bug :- 2426609 SSN Format Incorrect in Output File
36 mesriniv 25-jun-2002 Code has been added inorder to handle the if No Data is fetched by the
37 Parent Details and Student Details Cursors and skip the records.
38 Change History :
39 Bug :- 2426609 SSN Format Incorrect in Output File
40 Who When What
41 mesriniv 21-jun-2002 While inserting Student SSN/Parent SSN
42 formatting and substr of 9 chars is done.
43 Code added to display the Parameters passed.
44 Who When What
45 masehgal 19-Feb-2002 # 2216956 FACR007
46 Added Elec_mpn_ind , Borr_sign_ind in igf_sl_lor_pkg
47 ***************************************************************/
48
49 g_debug_runtime_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
50
51 -- Main Procedure starts here and is a Concurrent Program
52
53 PROCEDURE process_pnote(
54 ERRBUF OUT NOCOPY VARCHAR2,
55 RETCODE OUT NOCOPY NUMBER,
56 p_award_year IN VARCHAR2,
57 p_loan_catg IN igf_lookups_view.lookup_code%TYPE,
58 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
59 p_loan_number IN igf_sl_loans_v.loan_number%TYPE,
60 p_org_id IN NUMBER
61 )AS
62 /***************************************************************
63 Created By : avenkatr
64 Date Created By : 2001/08/05
65 Purpose :
66 Known Limitations,Enhancements or Remarks
67 Change History :
68 Who When What
69 veramach 16-SEP-2003 FA 122 loan enhancements build
70 1.Changed cursors c_stafford_det and c_plus_det to remove references of igf_sl_plus_borw.
71 2.the borrower info is now derived using igf_sl_gen.get_person_details procedure
72 3.changed c_dl_orig_recs cursor -added 2 where clauses on ci_cal_type and ci_sequence_number and
73 changed the signature to take lookup_type and pnote_status as arguments
74
75 masehgal 19-Feb-2002 # 2216956 FACR007
76 Added Elec_mpn_ind , Borr_sign_ind in igf_sl_lor_pkg
77 ****************************************************************/
78
79 student_dtl_rec igf_sl_gen.person_dtl_rec;
80 student_dtl_cur igf_sl_gen.person_dtl_cur;
81
82 parent_dtl_rec igf_sl_gen.person_dtl_rec;
83 parent_dtl_cur igf_sl_gen.person_dtl_cur;
84
85 l_debug_str fnd_log_messages.message_text%TYPE;
86
87 CURSOR c_pnote_check( x_ci_cal_type igf_sl_dl_setup.ci_cal_type%TYPE,
88 x_ci_sequence_number igf_sl_dl_setup.ci_sequence_number%TYPE ) IS
89 SELECT pnote_print_ind, ci_alternate_code
90 FROM igf_sl_dl_setup_v
91 WHERE ci_cal_type = x_ci_cal_type AND ci_sequence_number = x_ci_sequence_number;
92
93 CURSOR c_lor_rec( x_loan_id igf_sl_lor.loan_id%TYPE ) IS
94 SELECT igf_sl_lor.*
95 FROM igf_sl_lor
96 WHERE loan_id = x_loan_id FOR UPDATE OF igf_sl_lor.pnote_status NOWAIT;
97
98 CURSOR c_dl_orig_recs( x_ci_cal_type igf_sl_lor_v.ci_cal_type%TYPE,
99 x_ci_sequence_number igf_sl_lor_v.ci_sequence_number%TYPE,
100 x_loan_number igf_sl_lor_v.loan_number%TYPE,
101 x_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
102 x_pnote_status igf_sl_lor_v.pnote_status%TYPE,
103 x_lookup_type igf_lookups_view.lookup_type%TYPE
104 ) IS
105 SELECT lor.loan_id
106 FROM igf_sl_lor_v lor, igf_ap_fa_base_rec fa
107 WHERE lor.pnote_status = x_pnote_status AND
108 lor.student_id = fa.person_id AND
109 fa.base_id = NVL(x_base_id,fa.base_id) AND
110 lor.fed_fund_code IN ( SELECT DISTINCT lookup_code
111 FROM igf_lookups_view
112 WHERE lookup_type = x_lookup_type ) AND
113 lor.ci_cal_type = x_ci_cal_type AND
114 lor.ci_sequence_number = x_ci_sequence_number AND
115 lor.loan_number = NVL(x_loan_number, lor.loan_number) AND
116 fa.ci_cal_type = x_ci_cal_type AND
117 fa.ci_sequence_number = x_ci_sequence_number;
118
119 CURSOR c_stafford_det(
120 x_loan_id igf_sl_loans_all.LOAN_ID%TYPE
121 ) IS
122 SELECT loans.loan_number,
123 fa.person_id student_id,
124 prsn.api_person_id s_ssn,
125 prsn.given_names,
126 prsn.surname,
127 prsn.middle_name,
128 prsn.birth_dt
129 FROM igf_sl_loans loans,
130 igf_aw_award awd,
131 igf_ap_fa_base_rec fa,
132 igf_ap_person_v prsn
133 WHERE loans.loan_id = x_loan_id
134 AND loans.award_id = awd.award_id
135 AND awd.base_id = fa.base_id
136 AND fa.person_id = prsn.person_id;
137
138 CURSOR c_plus_det(
139 x_loan_id igf_sl_loans.loan_id%TYPE
140 ) IS
141 SELECT loans.loan_number,
142 awd.offered_amt loan_amt_offered,
143 awd.accepted_amt loan_amt_accepted,
144 loans.loan_per_begin_date,
145 loans.loan_per_end_date,
146 fa.person_id student_id,
147 prsn.api_person_id s_ssn,
148 prsn.given_names,
149 prsn.surname,
150 prsn.middle_name,
151 prsn.birth_dt,
152 lor.p_person_id,
153 parent.api_person_id,
154 parent.given_names p_first_name,
155 parent.surname p_last_name,
156 parent.middle_name p_middle_name,
157 parent.birth_dt p_date_of_birth
158 FROM igf_sl_lor lor,
159 igf_sl_loans loans,
160 igf_aw_award awd,
161 igf_ap_fa_base_rec fa,
162 igf_ap_person_v prsn,
163 igs_pe_person_v parent
164 WHERE lor.loan_id = x_loan_id
165 AND lor.loan_id = loans.loan_id
166 AND loans.award_id = awd.award_id
167 AND awd.base_id = fa.base_id
168 AND fa.person_id = prsn.person_id
169 AND NVL( lor.p_person_id, 0) = parent.person_id (+);
170
171
172 --Get the Student Person,Parent Person and Loan Number for the current Loan ID
173 CURSOR cur_get_loans(p_ln_id igf_sl_loans_all.loan_id%TYPE) IS
174 SELECT student_id,p_person_id,loan_number
175 FROM igf_sl_lor_v
176 WHERE loan_id=p_ln_id;
177
178 l_loan_rec cur_get_loans%ROWTYPE;
179 l_loan_rec_det cur_get_loans%ROWTYPE;
180
181 l_ci_cal_type igf_sl_dl_setup_v.ci_cal_type%TYPE;
182 l_ci_sequence_number igf_sl_dl_setup_v.ci_sequence_number%TYPE;
183 l_alternate_code igs_ca_inst.alternate_code%TYPE;
184 l_batch_seq_num NUMBER(15);
185 l_no_of_pnotes NUMBER;
186 l_person_phone igf_sl_dl_pnote_p_p.s_phone%TYPE;
187 l_parent_phone igf_sl_dl_pnote_p_p.p_phone%TYPE;
188 l_rowid VARCHAR2(30);
189 l_log_mesg VARCHAR2(2000);
190 l_pnsp_id igf_sl_dl_pnote_s_p.pnsp_id%TYPE;
191 l_pnpp_id igf_sl_dl_pnote_p_p.pnpp_id%TYPE;
192 l_person_num igf_aw_award_v.person_number%TYPE;
193 l_heading VARCHAR2(100);
194 l_stud_number igf_aw_award_v.person_number%TYPE;
195 l_parent_number igf_aw_award_v.person_number%TYPE;
196 l_ret BOOLEAN DEFAULT FALSE;
197 l_prnt_ln igf_sl_loans_all.loan_number%TYPE;
198
199 r_pnote c_pnote_check%ROWTYPE;
200 r_dl_orig_rec c_dl_orig_recs%ROWTYPE;
201 r_dl_lor_rec c_lor_rec%ROWTYPE;
202 r_stafford_det c_stafford_det%ROWTYPE;
203 r_plus_det c_plus_det%ROWTYPE;
204
205 l_lookup_type igf_lookups_view.lookup_type%TYPE;
206 l_pnote_status igf_sl_lor_v.pnote_status%TYPE;
207
208 l_current_per_num igf_aw_award_v.person_number%TYPE;
209
210 SKIP_LOAN_RECORD EXCEPTION;
211
212 --Internal Procedure to display LOG Message
213 PROCEDURE log_message(p_stud_id igf_ap_fa_base_rec_all.person_id%TYPE,p_ln_number igf_sl_loans_all.loan_number%TYPE) IS
214 /***************************************************************
215 Created By : mesriniv
216 Date Created By : 2002/06/25
217 Purpose :
218 Known Limitations,Enhancements or Remarks
219 Change History :
220 Who When What
221 **************************************************************/
222 BEGIN
223 --Display message Promissory Note Processed for Person Number:-
224 l_ret:=igf_gr_gen.get_per_num(p_stud_id,l_current_per_num);
225
226 fnd_file.new_line(fnd_file.log,1);
227 fnd_message.set_name('IGF','IGF_SL_PROC_PROM');
228 fnd_message.set_token('P_STUD',l_current_per_num);
229 fnd_message.set_token('P_LOAN',p_ln_number);
230 fnd_file.put_line(fnd_file.log,fnd_message.get);
231 END log_message;
232
233 PROCEDURE update_pnote_status(
234 p_loan_id igf_sl_loans_all.loan_id%TYPE
235 ) AS
236 ------------------------------------------------------------------
237 --Created by : veramach, Oracle India
238 --Date created: 22-SEP-2003
239 --
240 --Purpose:
241 -- Update pnote status in igf_sl_lor table from G to P
242 --
243 --Known limitations/enhancements and/or remarks:
244 --
245 --Change History:
246 --Who When What
247 -------------------------------------------------------------------------------------
248 --akomurav 28-FEB-2006 Build FA161 and FA162.
249 -- TBH Impact change done in igf_sl_lor_pkg.update_row().
250 ----------------------------------------------------------------------------------
251 --bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
252 -- Impact of adding the relationship_cd
253 -- in igf_sl_lor_all table and obsoleting
254 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
255 -- GUARANTOR_ID, DUNS_GUARNT_ID,
256 -- LENDER_ID, DUNS_LENDER_ID
257 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
258 -- RECIPIENT_TYPE,DUNS_RECIP_ID
259 -- RECIP_NON_ED_BRC_ID columns.
260 ----------------------------------------------------------------------------------
261 --veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
262 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
263 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
264 -- chg_batch_id,appl_send_error_codes from igf_sl_lor
265 -------------------------------------------------------------------
266
267 BEGIN
268 /* If the loan id is processed,
269 Set the PNote Status for this loan ID as 'Printed'
270 */
271 FOR r_dl_lor_rec IN c_lor_rec( p_loan_id )
272 LOOP
273 BEGIN
274 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
275 l_debug_str := l_debug_str || ' Before updating igf_sl_lor : loan_id ' || p_loan_id;
276 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_print_pnote.update_pnote_status.debug', l_debug_str);
277 l_debug_str := NULL;
278 END IF;
279
280 igf_sl_lor_pkg.update_row (
281 X_mode => 'R',
282 x_rowid => r_dl_lor_rec.row_id,
283 x_origination_id => r_dl_lor_rec.origination_id,
284 x_loan_id => p_loan_id,
285 x_sch_cert_date => r_dl_lor_rec.sch_cert_date,
286 x_orig_status_flag => r_dl_lor_rec.orig_status_flag,
287 x_orig_batch_id => r_dl_lor_rec.orig_batch_id,
288 x_orig_batch_date => r_dl_lor_rec.orig_batch_date,
289 x_chg_batch_id => NULL,
290 x_orig_ack_date => r_dl_lor_rec.orig_ack_date,
291 x_credit_override => r_dl_lor_rec.credit_override,
292 x_credit_decision_date => r_dl_lor_rec.credit_decision_date,
293 x_req_serial_loan_code => r_dl_lor_rec.req_serial_loan_code,
294 x_act_serial_loan_code => r_dl_lor_rec.act_serial_loan_code,
295 x_pnote_delivery_code => r_dl_lor_rec.pnote_delivery_code,
296 x_pnote_status => 'P',
297 x_pnote_status_date => r_dl_lor_rec.pnote_status_date,
298 x_pnote_id => r_dl_lor_rec.pnote_id,
299 x_pnote_print_ind => r_dl_lor_rec.pnote_print_ind,
300 x_pnote_accept_amt => r_dl_lor_rec.pnote_accept_amt,
301 x_pnote_accept_date => r_dl_lor_rec.pnote_accept_date,
302 x_unsub_elig_for_heal => r_dl_lor_rec.unsub_elig_for_heal,
303 x_disclosure_print_ind => r_dl_lor_rec.disclosure_print_ind,
304 x_orig_fee_perct => r_dl_lor_rec.orig_fee_perct,
305 x_borw_confirm_ind => r_dl_lor_rec.borw_confirm_ind,
306 x_borw_interest_ind => r_dl_lor_rec.borw_interest_ind,
307 x_borw_outstd_loan_code => r_dl_lor_rec.borw_outstd_loan_code,
308 x_unsub_elig_for_depnt => r_dl_lor_rec.unsub_elig_for_depnt,
309 x_guarantee_amt => r_dl_lor_rec.guarantee_amt,
310 x_guarantee_date => r_dl_lor_rec.guarantee_date,
311 x_guarnt_amt_redn_code => r_dl_lor_rec.guarnt_amt_redn_code,
312 x_guarnt_status_code => r_dl_lor_rec.guarnt_status_code,
313 x_guarnt_status_date => r_dl_lor_rec.guarnt_status_date,
314 x_lend_apprv_denied_code => NULL,
315 x_lend_apprv_denied_date => NULL,
316 x_lend_status_code => r_dl_lor_rec.lend_status_code,
317 x_lend_status_date => r_dl_lor_rec.lend_status_date,
318 x_guarnt_adj_ind => r_dl_lor_rec.guarnt_adj_ind,
319 x_grade_level_code => r_dl_lor_rec.grade_level_code,
320 x_enrollment_code => r_dl_lor_rec.enrollment_code,
321 x_anticip_compl_date => r_dl_lor_rec.anticip_compl_date,
322 x_borw_lender_id => NULL,
323 x_duns_borw_lender_id => NULL,
324 x_guarantor_id => NULL,
325 x_duns_guarnt_id => NULL,
326 x_prc_type_code => r_dl_lor_rec.prc_type_code,
327 x_cl_seq_number => r_dl_lor_rec.cl_seq_number,
328 x_last_resort_lender => r_dl_lor_rec.last_resort_lender,
329 x_lender_id => NULL,
330 x_duns_lender_id => NULL,
331 x_lend_non_ed_brc_id => NULL,
332 x_recipient_id => NULL,
333 x_recipient_type => NULL,
334 x_duns_recip_id => NULL,
335 x_recip_non_ed_brc_id => NULL,
336 x_rec_type_ind => r_dl_lor_rec.rec_type_ind,
337 x_cl_loan_type => r_dl_lor_rec.cl_loan_type,
338 x_cl_rec_status => NULL,
339 x_cl_rec_status_last_update => NULL,
340 x_alt_prog_type_code => r_dl_lor_rec.alt_prog_type_code,
341 x_alt_appl_ver_code => r_dl_lor_rec.alt_appl_ver_code,
342 x_mpn_confirm_code => NULL,
343 x_resp_to_orig_code => r_dl_lor_rec.resp_to_orig_code,
344 x_appl_loan_phase_code => NULL,
345 x_appl_loan_phase_code_chg => NULL,
346 x_appl_send_error_codes => NULL,
347 x_tot_outstd_stafford => r_dl_lor_rec.tot_outstd_stafford,
348 x_tot_outstd_plus => r_dl_lor_rec.tot_outstd_plus,
349 x_alt_borw_tot_debt => r_dl_lor_rec.alt_borw_tot_debt,
350 x_act_interest_rate => r_dl_lor_rec.act_interest_rate,
351 x_service_type_code => r_dl_lor_rec.service_type_code,
352 x_rev_notice_of_guarnt => r_dl_lor_rec.rev_notice_of_guarnt,
353 x_sch_refund_amt => r_dl_lor_rec.sch_refund_amt,
354 x_sch_refund_date => r_dl_lor_rec.sch_refund_date,
355 x_uniq_layout_vend_code => r_dl_lor_rec.uniq_layout_vend_code,
356 x_uniq_layout_ident_code => r_dl_lor_rec.uniq_layout_ident_code,
357 x_p_person_id => r_dl_lor_rec.p_person_id,
358 x_p_ssn_chg_date => NULL,
359 x_p_dob_chg_date => NULL,
360 x_p_permt_addr_chg_date => r_dl_lor_rec.p_permt_addr_chg_date,
361 x_p_default_status => r_dl_lor_rec.p_default_status,
362 x_p_signature_code => r_dl_lor_rec.p_signature_code,
363 x_p_signature_date => r_dl_lor_rec.p_signature_date,
364 x_s_ssn_chg_date => NULL,
365 x_s_dob_chg_date => NULL,
366 x_s_permt_addr_chg_date => r_dl_lor_rec.s_permt_addr_chg_date,
367 x_s_local_addr_chg_date => NULL,
368 x_s_default_status => r_dl_lor_rec.s_default_status,
369 x_s_signature_code => r_dl_lor_rec.s_signature_code,
370 x_pnote_batch_id => r_dl_lor_rec.pnote_batch_id,
371 x_pnote_ack_date => r_dl_lor_rec.pnote_ack_date,
372 x_pnote_mpn_ind => r_dl_lor_rec.pnote_mpn_ind,
373 x_elec_mpn_ind => r_dl_lor_rec.elec_mpn_ind,
374 x_borr_sign_ind => r_dl_lor_rec.borr_sign_ind,
375 x_stud_sign_ind => r_dl_lor_rec.stud_sign_ind,
376 x_borr_credit_auth_code => r_dl_lor_rec.borr_credit_auth_code,
377 x_relationship_cd => r_dl_lor_rec.relationship_cd,
378 x_interest_rebate_percent_num => r_dl_lor_rec.interest_rebate_percent_num,
379 x_cps_trans_num => r_dl_lor_rec.cps_trans_num,
380 x_atd_entity_id_txt => r_dl_lor_rec.atd_entity_id_txt ,
381 x_rep_entity_id_txt => r_dl_lor_rec.rep_entity_id_txt,
382 x_crdt_decision_status => r_dl_lor_rec.crdt_decision_status,
383 x_note_message => r_dl_lor_rec.note_message,
384 x_book_loan_amt => r_dl_lor_rec.book_loan_amt ,
385 x_book_loan_amt_date => r_dl_lor_rec.book_loan_amt_date,
386 x_pymt_servicer_amt => r_dl_lor_rec.pymt_servicer_amt,
387 x_pymt_servicer_date => r_dl_lor_rec.pymt_servicer_date,
388 x_requested_loan_amt => r_dl_lor_rec.requested_loan_amt,
389 x_eft_authorization_code => r_dl_lor_rec.eft_authorization_code,
390 x_external_loan_id_txt => r_dl_lor_rec.external_loan_id_txt,
391 x_deferment_request_code => r_dl_lor_rec.deferment_request_code ,
392 x_actual_record_type_code => r_dl_lor_rec.actual_record_type_code,
393 x_reinstatement_amt => r_dl_lor_rec.reinstatement_amt,
394 x_school_use_txt => r_dl_lor_rec.school_use_txt,
395 x_lender_use_txt => r_dl_lor_rec.lender_use_txt,
396 x_guarantor_use_txt => r_dl_lor_rec.guarantor_use_txt,
397 x_fls_approved_amt => r_dl_lor_rec.fls_approved_amt,
398 x_flu_approved_amt => r_dl_lor_rec.flu_approved_amt,
399 x_flp_approved_amt => r_dl_lor_rec.flp_approved_amt,
400 x_alt_approved_amt => r_dl_lor_rec.alt_approved_amt,
401 x_loan_app_form_code => r_dl_lor_rec.loan_app_form_code,
402 x_override_grade_level_code => r_dl_lor_rec.override_grade_level_code,
403 x_b_alien_reg_num_txt => r_dl_lor_rec.b_alien_reg_num_txt,
404 x_esign_src_typ_cd => r_dl_lor_rec.esign_src_typ_cd,
405 x_acad_begin_date => r_dl_lor_rec.acad_begin_date,
406 x_acad_end_date => r_dl_lor_rec.acad_end_date
407 );
408 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
409 l_debug_str := l_debug_str || 'Updated igf_sl_lor : loan_id ' || p_loan_id;
410 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_print_pnote.update_pnote_status.debug', l_debug_str);
411 l_debug_str := NULL;
412 END IF;
413
414 END;
415
416 END LOOP; /* End of r_dl_lor_rec loop (i.e) end of update lor rec loop */
417
418 END update_pnote_status;
419
420
421 BEGIN
422 RETCODE:=0;
423
424 igf_aw_gen.set_org_id(p_org_id);
425
426 l_batch_seq_num := NULL;
427 l_no_of_pnotes := 0;
428
429 l_ci_cal_type := LTRIM(RTRIM(SUBSTR( p_award_year,1,10)));
430 l_ci_sequence_number := TO_NUMBER(SUBSTR( p_award_year,11));
431
432 l_alternate_code :=igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number);
433 l_heading :=igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER');
434
435 --Display all Headings
436
437
438 l_log_mesg :=igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS');
439 fnd_file.put_line(fnd_file.log, l_log_mesg);
440 l_log_mesg := RPAD(igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','CI_ALTERNATE_CODE'),80,' ') ||':'||RPAD(' ',4,' ')||l_alternate_code ;
441 fnd_file.put_line(fnd_file.log, l_log_mesg);
442
443 l_log_mesg := RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_CATG'),80,' ')||':'||RPAD(' ',4,' ')||
444 igf_aw_gen.lookup_desc('IGF_SL_DL_LOAN_CATG',p_loan_catg) ;
445 fnd_file.put_line(fnd_file.log, l_log_mesg);
446
447 --Display the Person Number for the Base ID
448 l_person_num :=NULL;
449 IF p_base_id IS NOT NULL THEN
450 l_person_num:=igf_gr_gen.get_per_num(p_base_id);
451 END IF;
452
453
454 l_log_mesg := RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),80,' ')||':'||RPAD(' ',4,' ')||l_person_num ;
455 fnd_file.put_line(fnd_file.log, l_log_mesg);
456
457 l_log_mesg := RPAD(l_heading,80,' ')||':'||RPAD(' ',4,' ')|| NVL( p_loan_number , NULL );
458 fnd_file.put_line(fnd_file.log, l_log_mesg);
459
460 fnd_file.new_line(fnd_file.log, 1);
461
462 /* Check if school is configured to print */
463 OPEN c_pnote_check( l_ci_cal_type, l_ci_sequence_number );
464 FETCH c_pnote_check INTO r_pnote;
465 IF ( c_pnote_check%NOTFOUND ) THEN
466 CLOSE c_pnote_check;
467 fnd_message.set_name('IGF', 'IGF_SL_NO_DL_SETUP');
468 fnd_file.put_line(fnd_file.log, fnd_message.get);
469 RAISE NO_DATA_FOUND;
470 END IF;
471 CLOSE c_pnote_check;
472
473 /* The school is not configured to print, so exit after displaying a message */
474 IF ( r_pnote.pnote_print_ind <> 'F' ) THEN
475 fnd_message.set_name('IGF', 'IGF_SL_PNOTE_SCH_NOPRNT');
476 fnd_message.set_token('AWD_YR', r_pnote.ci_alternate_code );
477 fnd_file.put_line(fnd_file.log, fnd_message.get);
478 RETURN;
479 END IF;
480
481
482 /* Get all DL orig recs with Pnote status as 'Ready to Print' */
483 l_pnote_status := 'G'; /*Ready to print */
484
485 IF p_loan_catg = 'DL_STAFFORD' THEN
486 l_lookup_type := 'IGF_SL_DL_STAFFORD';
487 END IF;
488
489 IF p_loan_catg = 'DL_PLUS' THEN
490 l_lookup_type := 'IGF_SL_DL_PLUS';
491 END IF;
492
493 FOR r_dl_orig_rec IN c_dl_orig_recs( l_ci_cal_type, l_ci_sequence_number , p_loan_number, p_base_id , l_pnote_status,l_lookup_type)
494 LOOP
495 BEGIN
496
497 IF ( p_loan_catg = 'DL_STAFFORD' ) THEN
498
499 /* Get person details for this loan id */
500 OPEN c_stafford_det( r_dl_orig_rec.loan_id );
501 FETCH c_stafford_det INTO r_stafford_det;
502 IF c_stafford_det%FOUND THEN
503 CLOSE c_stafford_det;
504
505 l_loan_rec:=NULL;
506 OPEN cur_get_loans(r_dl_orig_rec.loan_id);
507 FETCH cur_get_loans INTO l_loan_rec;
508 CLOSE cur_get_loans;
509
510 IF l_loan_rec.student_id IS NOT NULL THEN
511 l_ret:=igf_gr_gen.get_per_num(l_loan_rec.student_id,l_stud_number);
512 END IF;
513
514 --Fetch details of student
515 igf_sl_gen.get_person_details(r_stafford_det.student_id,student_dtl_cur);
516 FETCH student_dtl_cur INTO student_dtl_rec;
517
518 IF student_dtl_rec.p_permt_addr1 IS NULL THEN
519
520 CLOSE student_dtl_cur;
521 --Display message in Log File that "Home" address not available for Student and Skip record.
522 fnd_file.new_line(fnd_file.log,1);
523 fnd_file.put_line(fnd_file.log,l_heading||': '||l_loan_rec.loan_number);
524 fnd_message.set_name('IGF','IGF_SL_NO_S_HOME_ADDR');
525 fnd_message.set_token('P_STUD',l_stud_number);
526 fnd_file.put_line(fnd_file.log,fnd_message.get);
527 RAISE SKIP_LOAN_RECORD;
528
529 END IF;
530
531 /* Get phone details */
532 l_person_phone := igf_sl_gen.get_person_phone( r_stafford_det.student_id );
533
534 IF l_batch_seq_num IS NULL THEN
535 SELECT igf_sl_dl_pnote_bth_s.nextval INTO l_batch_seq_num FROM dual;
536 END IF;
537
538 --Inorder to rollback this Transaction whenever Lock Error happens
539 SAVEPOINT sp_prom_note;
540 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
541 l_debug_str := l_debug_str || 'Before inserting into igf_sl_dl_pnote_s_p';
542 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_print_pnote.process_pnote.debug', l_debug_str);
543 l_debug_str := NULL;
544 END IF;
545
546 /* Insert into IGF_SL_DL_PNOTE_S_P */
547 igf_sl_dl_pnote_s_p_pkg.insert_row(
548 x_mode => 'R',
549 x_rowid => l_rowid,
550 x_pnsp_id => l_pnsp_id,
551 x_batch_seq_num => l_batch_seq_num,
552 x_loan_id => r_dl_orig_rec.loan_id,
553 x_loan_number => r_stafford_det.loan_number,
554 x_person_id => r_stafford_det.student_id,
555 x_s_ssn => SUBSTR(igf_ap_matching_process_pkg.remove_spl_chr(r_stafford_det.s_ssn),1,9),
556 x_s_first_name => r_stafford_det.given_names,
557 x_s_last_name => r_stafford_det.surname,
558 x_s_middle_name => r_stafford_det.middle_name,
559 x_s_date_of_birth => r_stafford_det.birth_dt,
560 x_s_license_num => student_dtl_rec.p_license_num,
561 x_s_license_state => student_dtl_rec.p_license_state,
562 x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
563 x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
564 x_s_permt_city => student_dtl_rec.p_permt_city,
565 x_s_permt_state => student_dtl_rec.p_permt_state,
566 x_s_permt_zip => student_dtl_rec.p_permt_zip,
567 x_s_email_addr => student_dtl_rec.p_email_addr,
568 x_s_phone => l_person_phone,
569 x_status => 'N' /* not printed */
570 );
571 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
572 l_debug_str := l_debug_str || 'Inserted into igf_sl_dl_pnote_s_p';
573 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_print_pnote.process_pnote.debug', l_debug_str);
574 l_debug_str := NULL;
575 END IF;
576
577 --Since the loan is processed, call update_pnote_status to update the pnote_status to 'printed'
578
579 update_pnote_status(r_dl_orig_rec.loan_id);
580
581 CLOSE student_dtl_cur;
582 END IF;
583
584 IF c_stafford_det%ISOPEN THEN
585 CLOSE c_stafford_det;
586 END IF;
587
588 ELSIF ( p_loan_catg = 'DL_PLUS' ) THEN
589
590 /* Insert into IGF_SL_DL_PNOTE_P_P */
591 /* Get person details for this loan id */
592 OPEN c_plus_det( r_dl_orig_rec.loan_id );
593 FETCH c_plus_det INTO r_plus_det;
594
595 IF c_plus_det%FOUND THEN
596 CLOSE c_plus_det;
597
598 l_loan_rec:=NULL;
599 OPEN cur_get_loans(r_dl_orig_rec.loan_id);
600 FETCH cur_get_loans INTO l_loan_rec;
601 CLOSE cur_get_loans;
602 l_stud_number :=NULL;
603 l_parent_number:=NULL;
604
605 --Get Student Person Number
606 IF l_loan_rec.student_id IS NOT NULL THEN
607 l_ret:=igf_gr_gen.get_per_num(l_loan_rec.student_id,l_stud_number);
608 END IF;
609
610 --Get Parent Person Number
611 IF l_loan_rec.p_person_id IS NOT NULL THEN
612 l_ret:=igf_gr_gen.get_per_num(l_loan_rec.p_person_id,l_parent_number);
613 END IF;
614
615 --Fetch details of student and parent
616 igf_sl_gen.get_person_details(r_plus_det.student_id,student_dtl_cur);
617 FETCH student_dtl_cur INTO student_dtl_rec;
618
619 igf_sl_gen.get_person_details(r_plus_det.p_person_id,parent_dtl_cur);
620 FETCH parent_dtl_cur INTO parent_dtl_rec;
621
622
623 IF student_dtl_rec.p_permt_addr1 IS NULL OR parent_dtl_rec.p_permt_addr1 IS NULL THEN
624 --Display message in Log File that "Home" Address not present for Student and Parent and Skip record.
625 fnd_file.new_line(fnd_file.log,1);
626 fnd_file.put_line(fnd_file.log,l_heading||': '||l_loan_rec.loan_number);
627 fnd_message.set_name('IGF','IGF_SL_NO_SP_HOME_ADDR');
628 fnd_message.set_token('P_STUD',l_stud_number);
629 fnd_message.set_token('P_PAR',l_parent_number);
630 fnd_file.put_line(fnd_file.log,fnd_message.get);
631 fnd_file.new_line(fnd_file.log,1);
632 RAISE SKIP_LOAN_RECORD;
633 END IF;
634
635
636 /* Get phone details */
637 l_person_phone := igf_sl_gen.get_person_phone( r_plus_det.student_id ) ;
638 l_parent_phone := igf_sl_gen.get_person_phone( r_plus_det.p_person_id );
639
640
641 IF l_batch_seq_num IS NULL THEN
642 SELECT igf_sl_dl_pnote_bth_s.nextval INTO l_batch_seq_num FROM dual;
643 END IF;
644
645 --Transaction to be rolled back whenever Lock Error
646 SAVEPOINT sp_prom_note;
647 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
648 l_debug_str := l_debug_str || 'Before inserting into igf_sl_dl_pnote_p_p';
649 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_print_pnote.process_pnote.debug', l_debug_str);
650 l_debug_str := NULL;
651 END IF;
652
653 --insert into igf_sl_dl_pnote_p_p table
654 igf_sl_dl_pnote_p_p_pkg.insert_row(
655 x_mode => 'R',
656 x_rowid => l_rowid,
657 x_pnpp_id => l_pnpp_id,
658 x_batch_seq_num => l_batch_seq_num,
659 x_loan_id => r_dl_orig_rec.loan_id,
660 x_loan_number => r_plus_det.loan_number,
661 x_loan_amt_offered => r_plus_det.loan_amt_offered,
662 x_loan_amt_accepted => r_plus_det.loan_amt_accepted,
663 x_loan_per_begin_date => r_plus_det.loan_per_begin_date,
664 x_loan_per_end_date => r_plus_det.loan_per_end_date,
665 x_person_id => r_plus_det.student_id,
666 x_s_ssn => SUBSTR(igf_ap_matching_process_pkg.remove_spl_chr(r_plus_det.s_ssn),1,9),
667 x_s_first_name => r_plus_det.given_names,
668 x_s_last_name => r_plus_det.surname,
669 x_s_middle_name => r_plus_det.middle_name,
670 x_s_date_of_birth => r_plus_det.birth_dt,
671 x_s_citizenship_status => student_dtl_rec.p_citizenship_status,
672 x_s_alien_reg_number => student_dtl_rec.p_alien_reg_num,
673 x_s_license_num => student_dtl_rec.p_license_num,
674 x_s_license_state => student_dtl_rec.p_license_state,
675 x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
676 x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
677 x_s_permt_city => student_dtl_rec.p_permt_city,
678 x_s_permt_state => student_dtl_rec.p_permt_state,
679 x_s_permt_province => student_dtl_rec.p_province,
680 x_s_permt_county => student_dtl_rec.p_county,
681 x_s_permt_country => student_dtl_rec.p_country,
682 x_s_permt_zip => student_dtl_rec.p_permt_zip,
683 x_s_email_addr => student_dtl_rec.p_email_addr,
684 x_s_phone => l_person_phone,
685 x_p_person_id => r_plus_det.p_person_id,
686 x_p_ssn => SUBSTR(igf_ap_matching_process_pkg.remove_spl_chr(r_plus_det.api_person_id),1,9),
687 x_p_last_name => parent_dtl_rec.p_last_name,
688 x_p_first_name => parent_dtl_rec.p_first_name,
689 x_p_middle_name => parent_dtl_rec.p_middle_name,
690 x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
691 x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
692 x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
693 x_p_license_num => parent_dtl_rec.p_license_num,
694 x_p_license_state => parent_dtl_rec.p_license_state,
695 x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
696 x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
697 x_p_permt_city => parent_dtl_rec.p_permt_city,
698 x_p_permt_state => parent_dtl_rec.p_permt_state,
699 x_p_permt_province => parent_dtl_rec.p_province,
700 x_p_permt_county => parent_dtl_rec.p_county,
701 x_p_permt_country => parent_dtl_rec.p_country,
702 x_p_permt_zip => parent_dtl_rec.p_permt_zip,
703 x_p_email_addr => parent_dtl_rec.p_email_addr,
704 x_p_phone => l_parent_phone,
705 x_status => 'N' /* Not printed */
706 );
707 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
708 l_debug_str := l_debug_str || 'Inserted into igf_sl_dl_pnote_s';
709 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_print_pnote.process_pnote.debug', l_debug_str);
710 l_debug_str := NULL;
711 END IF;
712
713 --Since the loan is processed, call update_pnote_status to update the pnote_status to 'printed'
714 update_pnote_status(r_dl_orig_rec.loan_id);
715 CLOSE student_dtl_cur;
716 CLOSE parent_dtl_cur;
717 IF c_plus_det%ISOPEN THEN
718 CLOSE c_plus_det;
719 END IF;
720
721 END IF;
722
723 END IF; /* End of loan category IF check */
724
725 --Call procedure to log message
726 IF p_loan_catg='DL_PLUS' THEN
727 log_message(r_plus_det.student_id,r_plus_det.loan_number);
728 ELSIF p_loan_catg ='DL_STAFFORD' THEN
729 log_message(r_stafford_det.student_id,r_stafford_det.loan_number);
730 END IF;
731
732 /* Increment the no of promissory notes processed correctly */
733 l_no_of_pnotes := l_no_of_pnotes + 1;
734
735 EXCEPTION
736 WHEN app_exception.record_lock_exception THEN
737
738 l_loan_rec_det:=NULL;
739 OPEN cur_get_loans(r_dl_orig_rec.loan_id);
740 FETCH cur_get_loans INTO l_loan_rec_det;
741
742 CLOSE cur_get_loans;
743
744 fnd_file.new_line(fnd_file.log,1);
745 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
746 fnd_file.put_line(fnd_file.log,fnd_message.get);
747 fnd_file.put_line(fnd_file.log,l_heading||': '||l_loan_rec_det.loan_number);
748 fnd_file.new_line(fnd_file.log,1);
749 ROLLBACK TO sp_prom_note;
750
751 WHEN SKIP_LOAN_RECORD THEN
752 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
753 fnd_file.put_line(fnd_file.log,fnd_message.get);
754
755 END ;
756
757 END LOOP; /* End of r_dl_orig_rec loop (i.e) end of the DL Orig recs loop */
758
759 /* Display the details in the Log file */
760 fnd_file.new_line(fnd_file.log, 2);
761
762 fnd_message.set_name('IGF', 'IGF_SL_NO_OF_PNOTES');
763 fnd_message.set_token('NO_OF_PNOTES', l_no_of_pnotes );
764 fnd_file.put_line(fnd_file.log, fnd_message.get);
765
766 IF l_batch_seq_num IS NOT NULL THEN
767
768 fnd_message.set_name('IGF', 'IGF_SL_PNOTE_BATCH_SEQNO');
769 fnd_message.set_token('PNOTE_BATCH_SEQNO', NVL( l_batch_seq_num, NULL) );
770 fnd_file.put_line(fnd_file.log, fnd_message.get);
771
772 END IF;
773
774
775 EXCEPTION
776 WHEN NO_DATA_FOUND THEN
777 NULL;
778
779 WHEN app_exception.record_lock_exception THEN
780 ROLLBACK;
781 retcode := 2;
782 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
783 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
784
785 WHEN OTHERS THEN
786 ROLLBACK;
787 RETCODE :=2;
788 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
789 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_sl_dl_print_pnote.process_pnote.exception', l_debug_str || SQLERRM );
790 END IF;
791
792 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
793 fnd_message.set_token('NAME','igf_sl_dl_print_pnote.process_pnote');
794
795 ERRBUF := fnd_message.get;
796 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
797
798 END process_pnote;
799
800 END igf_sl_dl_print_pnote;