[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CL_ORIG
Source
1 PACKAGE BODY igf_sl_cl_orig AS
2 /* $Header: IGFSL08B.pls 120.5 2006/08/08 06:27:29 akomurav noship $ */
3
4 /*
5 ---------------------------------------------------------------------------------
6 -- Created By : mesriniv
7 -- Date Created By : 2000/11/17
8 -- Purpose : To Create Output Files for Commom Line Loans
9 -- Known Limitations,Enhancements or Remarks
10 -- Change History :
11 ---------------------------------------------------------------------------------
12 -- bvisvana 10-Apr-2006 FA 161 - Bug # 5006583 - CL4 Addendum
13 -- Two new columns (borrower alien reg number and e-signature source type code) + TBH impact
14 -- upinjark 28-Mar-2005 Bug - 4117260
15 Removed condition applied while fixing bug -4103342
16 and reverted back to the original condition
17 "Req Serial Loan Code should be Blank for FLP/ALT -field 90"
18 -- mnade 07-Jan-2005 Bug - 4103342 Need to send Requested amount insted of accepted amount.
19 loan_amt_accepted -> requested_loan_amt.
20 For CL4 - Serial Code will populated for PLUS and ALT Loans.
21 Type 4 Records, Other Loan Amount for this period is populated.
22 ZIP will go with 0 fill instead of 9 fill.
23 -- smadathi 16-Nov-2004 Bug 3416936. Added new business logic as part of
24 -- CL4 changes
25 ---------------------------------------------------------------------------------
26 cdcruz 28-Oct-2004 FA152 Auto Re-pkg Build
27 Modified the call to igf_aw_packng_subfns.get_fed_efc()
28 as part of dependency.
29 ---------------------------------------------------------------------------------
30 brajendr 12-Oct-2004 FA138 ISIR Enhacements
31 Modified the reference of payment_isir_id
32
33 -- Who When What
34 ---------------------------------------------------------------------------------
35 -- ridas 17-Sep-2004 Bug #3691146: Query optimized by using the table igf_sl_cl_recipient
36 -- instead of the view igf_sl_cl_recipient_v
37 ---------------------------------------------------------------------------------
38 -- sjadhav 23-Jul-2004 Bug 3787350, corrected zip code format
39 ---------------------------------------------------------------------------------
40 -- veramach 04-May-2004 bug 3603289
41 -- Modified cursor cur_student_licence to select
42 -- dependency_status from ISIR. other details are
43 -- derived from igf_sl_gen.get_person_details.
44 -----------------------------------------------------------------------------------
45 -- ugummall 29-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
46 -- 1. Added 4 new parameters to cl_originate and 3 to sub_cl_originate
47 -- 2. Changed the cursor cur_loan_dtls to include two extra parameters
48 -- namely p_base_id and p_school_id.
49 ---------------------------------------------------------------------------------
50 -- sjadhav 7-Oct-2003 Bug 3104228 Fa 122 Build
51 -- Added media type and recipient id
52 -- and relationship cd parameters
53 -- Removed ref to obsolete columns
54 ---------------------------------------------------------------------------------
55 -- veramach 25-SEP-2003 1. Corrected cursor cur_get_fin_aid to take award_status as parameter
56 -- 2. Changed ' ' to '0' for fed_stafford_loan_debt,fed_sls_debt,heal_debt,perkins_debt,other_debt,borw_gross_annual_sal,
57 -- borw_other_income,stud_mth_housing_pymt,stud_mth_crdtcard_pymt,stud_mth_auto_pymt,stud_mth_ed_loan_pymt,stud_mth_other_pymt
58 -- in @4 records
59 -- 3. borr_sign_ind and stud_sign_ind are now printed as ' ' instead of 'N'
60 -- 4. Added debug log messages
61 -----------------------------------------------------------------------------------------------------------------------------
62 -- veramach 23-SEP-2003 Bug 3104228: 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
63 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
64 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
65 -- chg_batch_id,appl_send_error_codes from igf_sl_lor
66 -- 2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
67 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
68 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
69 -- chg_batch_id from igf_sl_lor_loc
70 -- 3. Changed cur_recip_dts to take p_loan_status,p_active,p_lookup_type,p_enabled_flag
71 -- as parameters
72 -- 4. Changed cursor cur_loan_dtls not to select student/borrower information. This is
73 -- derived using igf_sl_gen.get_person_dtls
74 ------------------------------------------------------------------------------------------------------------------------------
75 ---------------------------------------------------------------------------------
76 -- gmuralid 03-07-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
77 -- Added legacy record flag as parameter to
78 -- igf_sl_loans_pkg
79 ---------------------------------------------------------------------------------
80 -- sjadhav 30-Apr-2003 Bug 2922549.
81 -- Corrected condition to put Student SSN
82 -- Added code to strip zip code of special
83 -- characters
84 ---------------------------------------------------------------------------------
85 --
86 -- sjadhav 27-Mar-2003 Bug 2863960
87 -- Changed Disb Gross Amt to Disb Accepted Amt
88 -- to insert into igf_sl_awd_disb table
89 ---------------------------------------------------------------------------------
90 -- sjadhav 27-Feb-2003 Bug 2814813
91 -- 1. @8 Record indicator should be set to Y
92 -- in @1 Record at position 225 only iff the
93 -- number of disb are more than 4
94 -- 2. Added cursor cur_get_disb_num to fetch
95 -- disb count of disb records
96 -- 3. Added igf_aw_gen_002 call to get fed efc
97 -- 4. Credit Auth Ind set based on loan
98 -- type
99 -- 5. Cert Loan Amt should be sum of disb amts
100 -- reported
101 -- 6. Amounts are truncated before puting in
102 -- file
103 ---------------------------------------------------------------------------------
104 -- masehgal 08-Jan-2003 # 2593215 Removed redundant calls to acad
105 -- begin/end date fetching functions of SL11B.
106 ---------------------------------------------------------------------------------
107 -- masehgal 02-Jan-2003 # 2477912 Made changes to resolve NCAT
108 -- reported issue.
109 ---------------------------------------------------------------------------------
110 -- mesriniv 21-jun-2002 While inserting Student SSN/Parent SSN
111 -- included substr of 9 chars .
112 ---------------------------------------------------------------------------------
113 -- mesriniv 8-jun-2002 2400487
114 -- 1.Used function to format SSN while
115 -- origination
116 -- 2.Replaced Occurrences of DUNS ID with
117 -- spaces
118 ---------------------------------------------------------------------------------
119 -- masehgal 17-Feb-2002 # 2216956 FACR007
120 -- Added Elec_mpn_ind , Borrow_sign_ind
121 -- Replaced duns_school_id
122 ---------------------------------------------------------------------------------
123 -- mesriniv 18-05-2001 1.Specific checks has been
124 -- made for ALT or FLP Loans
125 -- in case of spooling @1 Records.
126 -- 2.@4 Record Spooling has been done with
127 -- latest .pdf.
128 -- 3.Some changes have been made for
129 -- Fields which had only NVL () and
130 -- now they have been made as RPAD(NVL())
131 -- 4.Code has been added to specify the
132 -- No.of @4 Records in File.
133 ---------------------------------------------------------------------------------
134 */
135
136 SKIP_RECORD EXCEPTION;
137 g_debug_runtime_level NUMBER;
138
139 lv_dependency_status igf_ap_isir_matched_all.dependency_status%TYPE;
140 lv_s_citizenship_status igf_ap_isir_matched_all.citizenship_status%TYPE;
141 lv_p_citizenship_status igf_ap_isir_matched_all.citizenship_status%TYPE;
142 lv_s_license_number igf_ap_isir_matched_all.driver_license_number%TYPE;
143 lv_s_license_state igf_ap_isir_matched_all.driver_license_state%TYPE;
144 lv_alien_reg_num igf_sl_lor_loc_all.s_alien_reg_num%TYPE; --pssahni 31-Jan-2005 changed %type from igf_ap_isir_matched_all to igf_sl_lor_loc_all
145 lv_s_legal_res_state igf_ap_isir_matched_all.s_state_legal_residence%TYPE;
146 lv_s_legal_res_date DATE;
147 lv_s_permt_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
148 lv_s_foreign_postal_code igf_sl_lor_loc_all.s_foreign_postal_code%TYPE;
149 lv_p_permt_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
150 l_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
151 lv_p_foreign_postal_code igf_sl_lor_loc_all.s_foreign_postal_code%TYPE;
152
153 x_return_status VARCHAR2(1);
154 x_msg_data VARCHAR2(30);
155 x_ope_cd igs_or_org_alt_ids.org_alternate_id%TYPE;
156
157
158 -- Query optimized by using the table igf_sl_cl_recipient instead of the view igf_sl_cl_recipient_v (bug #3691146)
159 CURSOR cur_recip_desc( p_relationship_cd VARCHAR2)
160 IS
161 SELECT
162 rcpt.recipient_id recipient_id,
163 rcpt.recipient_type recipient_type,
164 lnd.description recip_description,
165 rcpt.recip_non_ed_brc_id recip_non_ed_brc_id
166 FROM
167 igf_sl_cl_recipient rcpt,
168 igf_sl_lender lnd
169 WHERE
170 rcpt.recipient_id = lnd.lender_id AND
171 rcpt.recipient_type = 'LND' AND
172 rcpt.relationship_cd = p_relationship_cd
173 UNION ALL
174 SELECT
175 rcpt.recipient_id recipient_id,
176 rcpt.recipient_type recipient_type,
177 guarn.description recip_description,
178 rcpt.recip_non_ed_brc_id recip_non_ed_brc_id
179 FROM
180 igf_sl_cl_recipient rcpt,
181 igf_sl_guarantor guarn
182 WHERE
183 rcpt.recipient_id = guarn.guarantor_id AND
184 rcpt.recipient_type = 'GUARN' AND
185 rcpt.relationship_cd = p_relationship_cd
186 UNION ALL
187 SELECT
188 rcpt.recipient_id recipient_id,
189 rcpt.recipient_type recipient_type,
190 srvc.description recip_description,
191 rcpt.recip_non_ed_brc_id recip_non_ed_brc_id
192 FROM
193 igf_sl_cl_recipient rcpt,
194 igf_sl_servicer srvc
195 WHERE
196 rcpt.recipient_id = srvc.servicer_id AND
197 rcpt.recipient_type = 'SRVC' AND
198 rcpt.relationship_cd = p_relationship_cd;
199
200
201 recip_desc_rec cur_recip_desc%ROWTYPE;
202
203 -- To fetch the Loan Records based on the Recepient Information
204 CURSOR cur_loan_dtls(p_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
205 p_seq_number igf_ap_fa_base_rec.ci_sequence_number%TYPE,
206 p_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE,
207 p_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE,
208 p_loan_number igf_sl_loans.loan_number%TYPE,
209 p_loan_status igf_sl_loans.loan_status%TYPE,
210 p_active igf_sl_loans.active%TYPE,
211 p_relationship_cd igf_sl_lor_all.relationship_cd%TYPE,
212 p_base_id VARCHAR2,
213 p_school_id VARCHAR2
214 ) IS
215 SELECT loans.ROWID row_id,
216 loans.loan_id,
217 loans.loan_number,
218 loans.award_id,
219 awd.offered_amt loan_amt_offered,
220 awd.accepted_amt loan_amt_accepted,
221 loans.loan_per_begin_date,
222 loans.loan_per_end_date,
223 loans.loan_status,
224 loans.loan_status_date,
225 loans.loan_chg_status,
226 loans.loan_chg_status_date,
227 loans.active,
228 loans.active_date,
229 lor.orig_fee_perct,
230 lor.pnote_print_ind,
231 lor.s_default_status,
232 lor.p_default_status,
233 lor.p_person_id,
234 lor.sch_cert_date,
235 lor.prc_type_code,
236 lor.anticip_compl_date,
237 lor.cl_loan_type,
238 lor.borw_interest_ind,
239 lor.grade_level_code,
240 lor.enrollment_code,
241 lor.req_serial_loan_code,
242 lor.pnote_delivery_code,
243 lor.s_signature_code,
244 lor.p_signature_code,
245 lor.borw_outstd_loan_code,
246 lor.cl_seq_number,
247 lor.rec_type_ind,
248 lor.p_signature_date,
249 lor.borr_sign_ind,
250 lor.stud_sign_ind,
251 lor.borr_credit_auth_code,
252 lor.origination_id,
253 lor.act_serial_loan_code,
254 lor.orig_status_flag,
255 lor.orig_batch_id,
256 lor.orig_batch_date,
257 lor.orig_ack_date,
258 lor.credit_override,
259 lor.credit_decision_date,
260 lor.pnote_status,
261 lor.pnote_status_date,
262 lor.pnote_id,
263 lor.pnote_accept_amt,
264 lor.pnote_accept_date,
265 lor.borw_confirm_ind,
266 lor.unsub_elig_for_heal,
267 lor.disclosure_print_ind,
268 lor.unsub_elig_for_depnt,
269 lor.guarantee_amt,
270 lor.guarantee_date,
271 lor.guarnt_adj_ind,
272 lor.guarnt_amt_redn_code,
273 lor.guarnt_status_code,
274 lor.guarnt_status_date,
275 lor.lend_status_code,
276 lor.lend_status_date,
277 lor.last_resort_lender,
278 lor.alt_prog_type_code,
279 lor.alt_appl_ver_code,
280 lor.resp_to_orig_code,
281 lor.tot_outstd_stafford,
282 lor.tot_outstd_plus,
283 lor.alt_borw_tot_debt,
284 lor.act_interest_rate,
285 lor.service_type_code,
286 lor.rev_notice_of_guarnt,
287 lor.sch_refund_amt,
288 lor.sch_refund_date,
289 lor.uniq_layout_vend_code,
290 lor.uniq_layout_ident_code,
291 lor.pnote_batch_id,
292 lor.pnote_ack_date,
293 lor.pnote_mpn_ind,
294 recip.lender_id,
295 recip.guarantor_id,
296 recip.recipient_id,
297 recip.lend_non_ed_brc_id,
298 recip.recip_non_ed_brc_id,
299 recip.recipient_type,
300 fabase.base_id,
301 fabase.person_id student_id,
302 awd.accepted_amt,
303 fcat.fed_fund_code,
304 fabase.ci_cal_type,
305 fabase.ci_sequence_number,
306 fcat.alt_rel_code,
307 fcat.alt_loan_code,
308 lor.note_message,
309 lor.book_loan_amt_date,
310 lor.book_loan_amt,
311 lor.pymt_servicer_date,
312 lor.pymt_servicer_amt,
313 lor.rep_entity_id_txt,
314 lor.cps_trans_num,
315 lor.atd_entity_id_txt,
316 lor.s_dob_chg_date,
317 lor.p_dob_chg_date,
318 lor.crdt_decision_status,
319 lor.interest_rebate_percent_num,
320 lor.external_loan_id_txt,
321 lor.deferment_request_code,
322 lor.eft_authorization_code,
323 lor.requested_loan_amt,
324 lor.actual_record_type_code,
325 lor.reinstatement_amt,
326 lor.school_use_txt,
327 lor.lender_use_txt,
328 lor.guarantor_use_txt,
329 lor.fls_approved_amt,
330 lor.flu_approved_amt,
331 lor.flp_approved_amt,
332 lor.alt_approved_amt,
333 lor.loan_app_form_code,
334 lor.override_grade_level_code,
335 recip.relationship_cd,
336 lor.b_alien_reg_num_txt, -- fa 161 - bug # 5006583
337 lor.esign_src_typ_cd
338 FROM igf_sl_loans_all loans,
339 igf_sl_lor_all lor,
340 igf_aw_award_all awd,
341 igf_aw_fund_mast_all fmast,
342 igf_aw_fund_cat_all fcat,
343 igf_ap_fa_base_rec_all fabase,
344 igf_sl_cl_recipient recip
345 WHERE fabase.ci_cal_type = p_cal_type
346 AND fabase.ci_sequence_number = p_seq_number
347 AND fabase.base_id = awd.base_id
348 AND fabase.base_id = NVL (p_base_id, fabase.base_id)
349 AND awd.fund_id = fmast.fund_id
350 AND fmast.fund_code = fcat.fund_code
351 AND (fcat.fed_fund_code = p_fed_fund_1 or fcat.fed_fund_code = p_fed_fund_2)
352 AND loans.award_id = awd.award_id
353 AND loans.loan_number = NVL (p_loan_number, loans.loan_number)
354 AND loans.loan_id = lor.loan_id
355 AND loans.loan_status = p_loan_status
356 AND loans.active = p_active
357 AND SUBSTR(loans.loan_number,1,6) = SUBSTR(p_school_id,1,6)
358 AND lor.relationship_cd = recip.relationship_cd
359 AND lor.relationship_cd = NVL (p_relationship_cd, lor.relationship_cd);
360
361 loan_rec cur_loan_dtls%ROWTYPE;
362 student_dtl_rec igf_sl_gen.person_dtl_rec;
363 parent_dtl_rec igf_sl_gen.person_dtl_rec;
364
365 -- Main Procedure starts here and is a Concurrent Program
366
367 PROCEDURE cl_originate( errbuf OUT NOCOPY VARCHAR2,
368 retcode OUT NOCOPY NUMBER,
369 p_award_year IN VARCHAR2,
370 p_base_id IN VARCHAR2,
371 p_loan_catg IN igf_lookups_view.lookup_code%TYPE,
372 p_loan_number IN igf_sl_loans_all.loan_number%TYPE,
373 p_org_id IN NUMBER,
374 p_media_type IN VARCHAR2,
375 p_recipient_id IN VARCHAR2,
376 p_school_id IN VARCHAR2,
377 non_ed_branch IN VARCHAR2,
378 sch_non_ed_branch IN VARCHAR2
379 )
380 AS
381
382 /***************************************************************************************
383 Created By : mesriniv
384 Date Created By : 2000/11/17
385 Purpose : To Fetch Distinct Recipient Details and Compute Batch Id.
386 Known Limitations,Enhancements or Remarks
387 Change History :
388 Bug No:2332668 Desc:LOAN ORIGINATION PROCESS NOT RUNNING SUCCESSFULLY.
389 Who When What
390 ugummall 29-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
391 1. Added 4 new parameters namely p_base_id,
392 p_school_id, non_ed_branch and sch_non_ed_branch
393 2. Changed the cursor cur_recip_dts to select only those
394 records related to p_school_id, and only those related
395 to p_base_id if it is not null.
396 mesriniv 23-APR-2002 Added code to display the Parameters Passed
397 ***************************************************************************************/
398
399 lv_ci_cal_type igs_ca_inst.cal_type%TYPE;
400 lv_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
401 lv_recipient_id_found BOOLEAN;
402
403 lv_request_id NUMBER(10);
404 lv_request_status BOOLEAN;
405 l_i NUMBER(1);
406 l_alternate_code igs_ca_inst.alternate_code%TYPE;
407 l_lookup_type igf_lookups_view.lookup_type%TYPE;
408
409 TYPE l_parameters IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
410 l_para_rec l_parameters;
411
412 lv_complete BOOLEAN;
413
414 -- To fetch the Distinct Combinations of Recipient Details
415
416 CURSOR cur_recip_dts(
417 p_loan_status igf_sl_loans.loan_status%TYPE,
418 p_active igf_sl_loans.active%TYPE,
419 p_lookup_type igf_lookups_view.lookup_type%TYPE,
420 p_enabled_flag igf_lookups_view.enabled_flag%TYPE,
421 p_recipient_id VARCHAR2
422 ) IS
423 SELECT DISTINCT lor.relationship_cd
424 FROM igf_ap_fa_base_rec fabase,
425 igf_sl_loans lar,
426 igf_sl_lor lor,
427 igf_aw_award awd,
428 igf_aw_fund_mast fund,
429 igf_aw_fund_cat fcat,
430 igf_sl_cl_recipient recip
431 WHERE lar.loan_id = lor.loan_id
432 AND lar.award_id = awd.award_id
433 AND awd.base_id = fabase.base_id
434 AND awd.base_id = NVL(p_base_id, awd.base_id)
435 AND awd.fund_id = fund.fund_id
436 AND fund.fund_code = fcat.fund_code
437 AND fabase.ci_cal_type = lv_ci_cal_type
438 AND fabase.ci_sequence_number = lv_ci_sequence_number
439 AND lar.loan_status = p_loan_status
440 AND lar.active = p_active
441 AND recip.recipient_id = NVL(p_recipient_id,recip.recipient_id)
442 AND lor.relationship_cd = recip.relationship_cd
443 AND lar.loan_number LIKE DECODE(p_loan_number,NULL,'%',p_loan_number)
444 AND substr(lar.loan_number, 1, 6) = substr(p_school_id,1,6)
445 AND fcat.fed_fund_code IN ( SELECT DISTINCT lookup_code
446 FROM igf_lookups_view
447 WHERE lookup_type = p_lookup_type
448 AND enabled_flag = p_enabled_flag)
449 ORDER BY
450 lor.relationship_cd;
451
452 --Cursor to fetch the Meaning for displaying parameters passed
453 --Used UNION ALL here since individual select clauses
454 --have the same cost
455 --Bug No:2332668
456
457 CURSOR cur_get_parameters
458 IS
459 SELECT meaning
460 FROM igf_lookups_view
461 WHERE lookup_type = 'IGF_SL_CL_LOAN_CATG'
462 AND lookup_code = p_loan_catg
463 AND enabled_flag = 'Y'
464 UNION ALL
465 SELECT meaning
466 FROM igf_lookups_view
467 WHERE lookup_type = 'IGF_GE_PARAMETERS'
468 AND lookup_code IN ('AWARD_YEAR','LOAN_CATG','LOAN_ID','PARAMETER_PASS')
469 AND enabled_flag = 'Y';
470
471 --Cursor to get the alternate code for the calendar instance
472 --Bug No:2332668
473 CURSOR cur_alternate_code
474 IS
475 SELECT ca.alternate_code
476 FROM igs_ca_inst ca
477 WHERE ca.cal_type = lv_ci_cal_type
478 AND ca.sequence_number = lv_ci_sequence_number;
479
480
481 BEGIN
482
483 g_debug_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
484 lv_recipient_id_found := FALSE;
485 retcode:=0;
486 igf_aw_gen.set_org_id(p_org_id);
487
488 -- Assigning the Parameters to global variables
489
490 lv_ci_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
491 lv_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
492
493
494 --Get the alternate code
495 OPEN cur_alternate_code;
496 FETCH cur_alternate_code INTO l_alternate_code;
497 IF cur_alternate_code%NOTFOUND THEN
498 CLOSE cur_alternate_code;
499 fnd_message.set_name('IGF','IGF_SL_NO_CALENDAR');
500 igs_ge_msg_stack.add;
501 fnd_file.put_line(fnd_file.log,fnd_message.get);
502 app_exception.raise_exception;
503 END IF;
504 CLOSE cur_alternate_code;
505
506 --Write the details of Parameters Passed into LOG File.
507 --Bug No:2332668
508 l_i := 0;
509 OPEN cur_get_parameters;
510 LOOP
511 l_i := l_i+1;
512 FETCH cur_get_parameters INTO l_para_rec(l_i);
513 EXIT WHEN cur_get_parameters%NOTFOUND;
514 END LOOP;
515 CLOSE cur_get_parameters;
516
517 --Show the parameters passed
518 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(5),50,' '));
519 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(2),50,' ')||':'||RPAD(' ',4,' ')||l_alternate_code);
520 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(3),50,' ')||':'||RPAD(' ',4,' ')||l_para_rec(1));
521 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(4),50,' ')||':'||RPAD(' ',4,' ')||p_loan_number);
522
523 fnd_file.put_line(fnd_file.log,' ');
524
525 -- Fetch the Distinct set of Recipient details
526 IF p_loan_catg = 'CL_STAFFORD' THEN
527 l_lookup_type :='IGF_SL_CL_STAFFORD';
528 ELSIF p_loan_catg = 'CL_PLUS' THEN
529 l_lookup_type := 'IGF_SL_CL_PLUS';
530 ELSIF p_loan_catg = 'CL_ALT' THEN
531 l_lookup_type := 'IGF_SL_CL_ALT';
532 ELSIF p_loan_catg = 'CL_GPLUSFL' THEN
533 l_lookup_type := 'IGF_SL_CL_GPLUS';
534 END IF;
535
536 -- Need to call the CL Validation Process before output of Data
537 IF(fnd_log.level_statement >= g_debug_runtime_level)THEN
538 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','lv_ci_cal_type:'||lv_ci_cal_type);
539 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','lv_ci_sequence_number:'||lv_ci_sequence_number);
540 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','p_loan_number:'||p_loan_number);
541 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','p_loan_catg:'||p_loan_catg);
542 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','Calling cl_lar_validate');
543 END IF;
544 lv_complete := igf_sl_cl_validation.cl_lar_validate(lv_ci_cal_type,
545 lv_ci_sequence_number,
546 p_loan_number,
547 p_loan_catg,
548 'JOB',
549 p_school_id,
550 p_base_id
551 );
552
553 IF(fnd_log.level_statement >= g_debug_runtime_level)THEN
554 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','Called cl_lar_validate');
555 IF lv_complete THEN
556 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','cl_lar_validate returned true');
557 ELSE
558 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.cl_originate.debug','cl_lar_validate returned false');
559 END IF;
560 END IF;
561
562 FOR orec IN cur_recip_dts('V','Y',l_lookup_type,'Y',p_recipient_id) LOOP
563 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
564 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug', 'l_lookup_type:' || l_lookup_type);
565 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug', 'l_alternate_code:' || l_alternate_code);
566 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug', 'p_loan_number:' || p_loan_number);
567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug', 'l_para_rec(1):' || l_para_rec(1));
568 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug', 'relatioship code :' || orec.relationship_cd);
569 END IF;
570
571
572 -- Fetch the Recipient Description
573 OPEN cur_recip_desc( orec.relationship_cd);
574 FETCH cur_recip_desc INTO recip_desc_rec;
575 IF cur_recip_desc%NOTFOUND THEN
576 CLOSE cur_recip_desc;
577 --The recipient information does not exist
578 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
579 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug','Recipient description not found');
580 END IF;
581 fnd_message.set_name('IGF','IGF_SL_RECIP_NOT_FOUND');
582 fnd_message.set_token('REL_CODE',orec.relationship_cd);
583 fnd_file.put_line(fnd_file.log,fnd_message.get);
584 ELSE
585 --The recipient information exists
586
587 CLOSE cur_recip_desc;
588
589 lv_recipient_id_found := TRUE;
590 lv_request_status := fnd_request.set_options;
591
592 -- Concurrent Request is being made to output the data
593 lv_request_id := fnd_request.submit_request('IGF',
594 'IGFSLJ08',
595 '',
596 '',
597 FALSE,
598 lv_ci_cal_type,
599 TO_CHAR(lv_ci_sequence_number),
600 p_loan_number,
601 p_loan_catg,
602 TO_CHAR(p_org_id),
603 orec.relationship_cd,
604 p_media_type,
605 p_base_id,
606 p_school_id,
607 sch_non_ed_branch,
608 CHR(0),
609 '','','','','','','',
610 '','','','','','','','','','',
611 '','','','','','','','','','',
612 '','','','','','','','','','',
613 '','','','','','','','','','',
614 '','','','','','','','','','',
615 '','','','','','','','','','',
616 '','','','','','','','','','',
617 '','','','','','','','','','',
618 ''--'','','',
619 );
620
621
622 -- Check the Return Status of Request Id
623 IF lv_request_id = 0 THEN
624 -- On Failure of Concurrent Request
625 fnd_message.set_name('IGF','IGF_SL_CL_ORIG_REQ_FAIL');
626 fnd_message.set_token('NAME',TO_CHAR(lv_request_id));
627 igs_ge_msg_stack.add;
628 IF (fnd_log.level_exception >= g_debug_runtime_level) THEN
629 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig.cl_originate.debug','Concurrent request failed');
630 END IF;
631 app_exception.raise_exception;
632 ELSE
633 fnd_file.new_line(fnd_file.log,2);
634 fnd_message.set_name('IGF','IGF_SL_CL_ORIG_CREATED');
635 fnd_message.set_token('P_FILENAME',RPAD(TO_CHAR(lv_request_id),10));
636 fnd_message.set_token('P_RECIP_ID',RPAD(recip_desc_rec.recipient_id,10));
637 fnd_message.set_token('P_RECIP_NAME',recip_desc_rec.recip_description||' ');
638 fnd_message.set_token('P_RECIP_BRC_ID',RPAD(NVL(recip_desc_rec.recip_non_ed_brc_id,' '),'10'));
639
640 -- IGF.#P_FILENAME : #P_RECIP_ID #P_RECIP_NAME #P_RECIP_BRC_ID
641 fnd_file.put_line(fnd_file.log, fnd_message.get);
642 fnd_file.new_line(fnd_file.log,2);
643 END IF;
644 END IF;
645 END LOOP;
646
647 -- In case No Recipient details have been fetched for any of the User Inputs then Need to display a Message in the LOG File
648 IF lv_recipient_id_found = FALSE THEN
649 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
650 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.cl_originate.debug','Recipient Id not found');
651 END IF;
652 fnd_file.new_line(fnd_file.log,2);
653 fnd_file.put_line(fnd_file.log, fnd_message.get_string('IGF','IGF_SL_NO_LOAN_ORIG_DATA'));
654 fnd_file.new_line(fnd_file.log,2);
655 END IF;
656
657 COMMIT;
658
659 EXCEPTION
660 WHEN app_exception.record_lock_exception THEN
661 ROLLBACK;
662 IF cur_recip_dts%ISOPEN THEN
663 CLOSE cur_recip_dts;
664 END IF;
665 retcode := 2;
666 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
667 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
668 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.cl_originate.exception',SQLERRM);
669 END IF;
670
671 igs_ge_msg_stack.conc_exception_hndl;
672
673 WHEN OTHERS THEN
674 ROLLBACK;
675 IF cur_recip_dts%ISOPEN THEN
676 CLOSE cur_recip_dts;
677 END IF;
678 retcode := 2;
679 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
680 fnd_message.set_token('NAME','IGF_SL_CL_ORIG.CL_ORIGINATE');
681 errbuf := fnd_message.get;
682 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
683 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.cl_originate.exception',SQLERRM);
684 END IF;
685 igs_ge_msg_stack.conc_exception_hndl;
686
687 END cl_originate;
688
689
690 PROCEDURE insert_lor_loc_rec (
691 p_v_school_id IN VARCHAR2,
692 p_n_coa IN igf_ap_fa_base_rec_all.coa_f%TYPE,
693 p_n_efc IN igf_ap_fa_base_rec_all.efc_f%TYPE,
694 p_n_est_fin IN igf_aw_award_all.accepted_amt%TYPE,
695 p_c_alt_borr_ind_flag IN VARCHAR2
696 ) AS
697
698
699 /***************************************************************
700 Created By : mesriniv
701 Date Created By : 2000/11/17
702 Purpose : To insert transaction records into the igf_sl_lor_loc table
703 Known Limitations,Enhancements or Remarks
704 Change History :
705 Who When What
706 veramach 23-SEP-2003 Bug 3104228:
707 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
708 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
709 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
710 chg_batch_id,appl_send_error_codes from igf_sl_lor
711 2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
712 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
713 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
714 chg_batch_id from igf_sl_lor_loc
715 ***************************************************************/
716
717 lv_row_id ROWID;
718 -- masehgal 08-Jan-2003 # 2593215 Removed redundant calls to acad
719 -- begin/end date fetching functions of SL11B.
720
721 BEGIN
722
723 -- Fetch License State and Num into Variables
724
725 --These Variables values vary for every Student Id
726 lv_row_id := NULL;
727 lv_s_license_number := NULL;
728 lv_s_license_state := NULL;
729 lv_s_citizenship_status := NULL;
730 lv_alien_reg_num := NULL;
731 lv_dependency_status := NULL;
732 lv_s_permt_phone := NULL;
733 lv_p_permt_phone := NULL;
734 l_phone := NULL;
735 lv_s_foreign_postal_code := NULL;
736 lv_p_foreign_postal_code := NULL;
737
738 -- masehgal 08-Jan-2003 # 2593215 Removed redundant calls to acad
739 -- begin/end date fetching functions of SL11B.
740
741 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
742 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.debug', 'Before inserting into igf_sl_lor_loc' );
743 END IF;
744
745 igf_sl_lor_loc_pkg.insert_row (
746 x_mode => 'R',
747 x_rowid => lv_row_id,
748 x_loan_id => loan_rec.loan_id,
749 x_origination_id => loan_rec.origination_id,
750 x_loan_number => loan_rec.loan_number,
751 x_loan_type => loan_rec.cl_loan_type,
752 x_loan_amt_offered => loan_rec.loan_amt_offered,
753 x_loan_amt_accepted => loan_rec.loan_amt_accepted,
754 x_loan_per_begin_date => loan_rec.loan_per_begin_date,
755 x_loan_per_end_date => loan_rec.loan_per_end_date,
756 x_acad_yr_begin_date => NULL , --ld_acad_yr_begin_date,
757 x_acad_yr_end_date => NULL , --ld_acad_yr_end_date,
758 x_loan_status => loan_rec.loan_status,
759 x_loan_status_date => loan_rec.loan_status_date,
760 x_loan_chg_status => loan_rec.loan_chg_status,
761 x_loan_chg_status_date => loan_rec.loan_chg_status_date,
762 x_req_serial_loan_code => loan_rec.req_serial_loan_code,
763 x_act_serial_loan_code => loan_rec.act_serial_loan_code,
764 x_active => loan_rec.active,
765 x_active_date => loan_rec.active_date,
766 x_sch_cert_date => loan_rec.sch_cert_date,
767 x_orig_status_flag => loan_rec.orig_status_flag,
768 x_orig_batch_id => loan_rec.orig_batch_id,
769 x_orig_batch_date => loan_rec.orig_batch_date,
770 x_chg_batch_id => NULL,
771 x_orig_ack_date => loan_rec.orig_ack_date,
772 x_credit_override => loan_rec.credit_override,
773 x_credit_decision_date => loan_rec.credit_decision_date,
774 x_pnote_delivery_code => loan_rec.pnote_delivery_code,
775 x_pnote_status => loan_rec.pnote_status ,
776 x_pnote_status_date => loan_rec.pnote_status_date,
777 x_pnote_id => loan_rec.pnote_id,
778 x_pnote_print_ind => loan_rec.pnote_print_ind,
779 x_pnote_accept_amt => loan_rec.pnote_accept_amt,
780 x_pnote_accept_date => loan_rec.pnote_accept_date,
781 x_p_signature_code => loan_rec.p_signature_code,
782 x_p_signature_date => loan_rec.p_signature_date,
783 x_s_signature_code => loan_rec.s_signature_code,
784 x_unsub_elig_for_heal => loan_rec.unsub_elig_for_heal,
785 x_disclosure_print_ind => loan_rec.disclosure_print_ind,
786 x_orig_fee_perct => loan_rec.orig_fee_perct,
787 x_borw_confirm_ind => loan_rec.borw_confirm_ind,
788 x_borw_interest_ind => loan_rec.borw_interest_ind,
789 x_unsub_elig_for_depnt => loan_rec.unsub_elig_for_depnt,
790 x_guarantee_amt => loan_rec.guarantee_amt,
791 x_guarantee_date => loan_rec.guarantee_date,
792 x_guarnt_adj_ind => loan_rec.guarnt_adj_ind,
793 x_guarnt_amt_redn_code => loan_rec.guarnt_amt_redn_code,
794 x_guarnt_status_code => loan_rec.guarnt_status_code,
795 x_guarnt_status_date => loan_rec.guarnt_status_date,
796 x_lend_apprv_denied_code => NULL,
797 x_lend_apprv_denied_date => NULL,
798 x_lend_status_code => loan_rec.lend_status_code,
799 x_lend_status_date => loan_rec.lend_status_date,
800 x_grade_level_code => loan_rec.grade_level_code,
801 x_enrollment_code => loan_rec.enrollment_code,
802 x_anticip_compl_date => loan_rec.anticip_compl_date,
803 x_borw_lender_id => loan_rec.lender_id,
804 x_duns_borw_lender_id => NULL,
805 x_guarantor_id => loan_rec.guarantor_id,
806 x_duns_guarnt_id => NULL,
807 x_prc_type_code => loan_rec.prc_type_code,
808 x_rec_type_ind => loan_rec.rec_type_ind,
809 x_cl_loan_type => loan_rec.cl_loan_type,
810 x_cl_seq_number => loan_rec.cl_seq_number,
811 x_last_resort_lender => loan_rec.last_resort_lender,
812 x_lender_id => loan_rec.lender_id,
813 x_duns_lender_id => NULL,
814 x_lend_non_ed_brc_id => loan_rec.lend_non_ed_brc_id,
815 x_recipient_id => loan_rec.recipient_id,
816 x_recipient_type => loan_rec.recipient_type,
817 x_duns_recip_id => NULL,
818 x_recip_non_ed_brc_id => loan_rec.recip_non_ed_brc_id,
819 x_cl_rec_status => NULL,
820 x_cl_rec_status_last_update => NULL,
821 x_alt_prog_type_code => loan_rec.alt_prog_type_code,
822 x_alt_appl_ver_code => loan_rec.alt_appl_ver_code,
823 x_borw_outstd_loan_code => loan_rec.borw_outstd_loan_code,
824 x_mpn_confirm_code => NULL,
825 x_resp_to_orig_code => loan_rec.resp_to_orig_code,
826 x_appl_loan_phase_code => NULL,
827 x_appl_loan_phase_code_chg => NULL,
828 x_tot_outstd_stafford => loan_rec.tot_outstd_stafford,
829 x_tot_outstd_plus => loan_rec.tot_outstd_plus,
830 x_alt_borw_tot_debt => loan_rec.alt_borw_tot_debt,
831 x_act_interest_rate => loan_rec.act_interest_rate,
832 x_service_type_code => loan_rec.service_type_code,
833 x_rev_notice_of_guarnt => loan_rec.rev_notice_of_guarnt,
834 x_sch_refund_amt => loan_rec.sch_refund_amt,
835 x_sch_refund_date => loan_rec.sch_refund_date,
836 x_uniq_layout_vend_code => loan_rec.uniq_layout_vend_code,
837 x_uniq_layout_ident_code => loan_rec.uniq_layout_ident_code,
838 x_p_person_id => loan_rec.p_person_id,
839 x_p_ssn => SUBSTR(parent_dtl_rec.p_ssn,1,9),
840 x_p_ssn_chg_date => NULL,
841 x_p_last_name => parent_dtl_rec.p_last_name,
842 x_p_first_name => parent_dtl_rec.p_first_name,
843 x_p_middle_name => parent_dtl_rec.p_middle_name,
844 x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
845 x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
846 x_p_permt_city => parent_dtl_rec.p_permt_city,
847 x_p_permt_state => parent_dtl_rec.p_permt_state,
848 x_p_permt_zip => parent_dtl_rec.p_permt_zip,
849 x_p_permt_addr_chg_date => NULL,
850 x_p_permt_phone => lv_p_permt_phone,
851 x_p_email_addr => parent_dtl_rec.p_email_addr,
852 x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
853 x_p_dob_chg_date => NULL,
854 x_p_license_num => parent_dtl_rec.p_license_num,
855 x_p_license_state => parent_dtl_rec.p_license_state,
856 x_p_citizenship_status => lv_p_citizenship_status,
857 x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
858 x_p_default_status => loan_rec.p_default_status,
859 x_p_foreign_postal_code => lv_p_foreign_postal_code,
860 x_p_state_of_legal_res => parent_dtl_rec.p_state_of_legal_res,
861 x_p_legal_res_date => parent_dtl_rec.p_legal_res_date,
862 x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
863 x_s_ssn_chg_date => NULL,
864 x_s_last_name => student_dtl_rec.p_last_name,
865 x_s_first_name => student_dtl_rec.p_first_name,
866 x_s_middle_name => student_dtl_rec.p_middle_name,
867 x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
868 x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
869 x_s_permt_city => student_dtl_rec.p_permt_city,
870 x_s_permt_state => student_dtl_rec.p_permt_state,
871 x_s_permt_zip => student_dtl_rec.p_permt_zip,
872 x_s_permt_addr_chg_date => NULL,
873 x_s_permt_phone => lv_s_permt_phone,
874 x_s_local_addr1 => student_dtl_rec.p_local_addr1,
875 x_s_local_addr2 => student_dtl_rec.p_local_addr2,
876 x_s_local_city => student_dtl_rec.p_local_city,
877 x_s_local_state => student_dtl_rec.p_local_state,
878 x_s_local_zip => student_dtl_rec.p_local_zip,
879 x_s_local_addr_chg_date => NULL,
880 x_s_email_addr => student_dtl_rec.p_email_addr,
881 x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
882 x_s_dob_chg_date => NULL,
883 x_s_license_num => lv_s_license_number,
884 x_s_license_state => lv_s_license_state,
885 x_s_depncy_status => lv_dependency_status,
886 x_s_default_status => loan_rec.s_default_status,
887 x_s_citizenship_status => lv_s_citizenship_status,
888 x_s_alien_reg_num => lv_alien_reg_num,
889 x_s_foreign_postal_code => lv_s_foreign_postal_code,
890 x_pnote_batch_id => loan_rec.pnote_batch_id,
891 x_pnote_ack_date => loan_rec.pnote_ack_date,
892 x_pnote_mpn_ind => loan_rec.pnote_mpn_ind,
893 x_award_id => loan_rec.award_id ,
894 x_base_id => loan_rec.base_id ,
895 x_document_id_txt => NULL ,
896 x_loan_key_num => NULL ,
897 x_interest_rebate_percent_num => loan_rec.interest_rebate_percent_num ,
898 x_fin_award_year => NULL ,
899 x_cps_trans_num => loan_rec.cps_trans_num ,
900 x_atd_entity_id_txt => loan_rec.atd_entity_id_txt ,
901 x_rep_entity_id_txt => loan_rec.rep_entity_id_txt ,
902 x_source_entity_id_txt => NULL ,
903 x_pymt_servicer_amt => loan_rec.pymt_servicer_amt ,
904 x_pymt_servicer_date => loan_rec.pymt_servicer_date ,
905 x_book_loan_amt => loan_rec.book_loan_amt ,
906 x_book_loan_amt_date => loan_rec.book_loan_amt_date ,
907 x_s_chg_birth_date => loan_rec.s_dob_chg_date ,
908 x_s_chg_ssn => NULL ,
909 x_s_chg_last_name => NULL ,
910 x_b_chg_birth_date => loan_rec.p_dob_chg_date ,
911 x_b_chg_ssn => NULL ,
912 x_b_chg_last_name => NULL ,
913 x_note_message => loan_rec.note_message ,
914 x_full_resp_code => NULL ,
915 x_s_permt_county => student_dtl_rec.p_county ,
916 x_b_permt_county => parent_dtl_rec.p_county ,
917 x_s_permt_country => student_dtl_rec.p_country ,
918 x_b_permt_country => parent_dtl_rec.p_country ,
919 x_crdt_decision_status => loan_rec.crdt_decision_status ,
920 x_external_loan_id_txt => loan_rec.external_loan_id_txt ,
921 x_deferment_request_code => loan_rec.deferment_request_code ,
922 x_eft_authorization_code => loan_rec.eft_authorization_code ,
923 x_requested_loan_amt => loan_rec.requested_loan_amt ,
924 x_actual_record_type_code => loan_rec.actual_record_type_code ,
925 x_reinstatement_amt => loan_rec.reinstatement_amt ,
926 x_school_use_txt => loan_rec.school_use_txt ,
927 x_lender_use_txt => loan_rec.lender_use_txt ,
928 x_guarantor_use_txt => loan_rec.guarantor_use_txt ,
929 x_fls_approved_amt => loan_rec.fls_approved_amt ,
930 x_flu_approved_amt => loan_rec.flu_approved_amt ,
931 x_flp_approved_amt => loan_rec.flp_approved_amt ,
932 x_alt_approved_amt => loan_rec.alt_approved_amt ,
933 x_loan_app_form_code => loan_rec.loan_app_form_code ,
934 x_alt_borrower_ind_flag => p_c_alt_borr_ind_flag ,
935 x_school_id_txt => p_v_school_id ,
936 x_cost_of_attendance_amt => p_n_coa ,
937 x_expect_family_contribute_amt => p_n_efc ,
938 x_established_fin_aid_amount => p_n_est_fin ,
939 x_borower_electronic_sign_flag => loan_rec.borr_sign_ind ,
940 x_student_electronic_sign_flag => loan_rec.stud_sign_ind ,
941 x_borower_credit_authoriz_flag => loan_rec.borr_credit_auth_code ,
942 x_mpn_type_flag => NULL ,
943 x_esign_src_typ_cd => loan_rec.esign_src_typ_cd
944
945 );
946
947 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
948 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.debug', 'Insertion into igf_sl_lor_loc succeeded' );
949 END IF;
950
951 EXCEPTION
952
953 WHEN app_exception.record_lock_exception THEN
954 IF (fnd_log.level_exception >= g_debug_runtime_level) THEN
955 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.exception', 'Lock row failed' );
956 END IF;
957 RAISE;
958
959 WHEN OTHERS THEN
960
961 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
962 fnd_message.set_token('NAME','IGF_SL_CL_ORIG.INSERT_LOR_LOC_REC');
963 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
964 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.exception',SQLERRM);
965 END IF;
966 igs_ge_msg_stack.add;
967 app_exception.raise_exception;
968
969 END insert_lor_loc_rec;
970
971
972 PROCEDURE update_orig_batch_id(p_origination_id igf_sl_lor.origination_id%TYPE,
973 p_batch_id igf_sl_lor.orig_batch_id%TYPE)
974 IS
975 /***************************************************************
976 Created By : mesriniv
977 Date Created By : 2000/11/17
978 Purpose : To update igf_sl_lor table with origination batch id
979 Known Limitations,Enhancements or Remarks
980 Change History :
981 Who When What
982 veramach 23-SEP-2003 Bug 3104228:
983 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
984 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
985 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
986 chg_batch_id,appl_send_error_codes from igf_sl_lor
987 2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
988 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
989 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
990 chg_batch_id from igf_sl_lor_loc
991 masehgal 17-Feb-2002 # 2216956 , FACR007
992 Added Elec_mpn_indicator , Borrow_sign_ind
993 ***************************************************************/
994
995 lv_row_id ROWID;
996 l_sch_date DATE;
997
998 CURSOR c_tbh_cur IS
999 SELECT igf_sl_lor.*
1000 FROM igf_sl_lor
1001 WHERE origination_id = p_origination_id FOR UPDATE OF sch_cert_date NOWAIT;
1002
1003 CURSOR c_sl_loans (cp_n_loan_id igf_sl_loans_all.loan_id%TYPE) IS
1004 SELECT external_loan_id_txt
1005 FROM igf_sl_loans_all
1006 WHERE loan_id = cp_n_loan_id;
1007
1008 l_v_ext_loan_id_txt igf_sl_loans_all.external_loan_id_txt%TYPE;
1009
1010 BEGIN
1011
1012 FOR tbh_rec in c_tbh_cur LOOP
1013
1014 -- To Update the "Record Type Ind" to "Corrections" in the Origination Record Type Indicator
1015 -- based on if its a Reprint or New Application
1016 --MN 16-Dec-2004 15:51 This change might not required. The loan will remain in A state even after file creation
1017 -- And the user will change that to C to send Corrections.
1018 /*
1019 IF tbh_rec.rec_type_ind IN ('A','R') THEN
1020 tbh_rec.rec_type_ind:='C';
1021 END IF;
1022 */
1023 --If the School Certification Date is NOT NULL Then leave it
1024 --Else it is the File Transmission Date which is SYSDATE
1025 --Bug 2477912
1026 l_sch_date:=NULL;
1027 IF tbh_rec.sch_cert_date IS NOT NULL THEN
1028 l_sch_date:= tbh_rec.sch_cert_date;
1029 ELSE
1030 l_sch_date:=TRUNC(SYSDATE);
1031 END IF;
1032
1033 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1034 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.update_batch_orig_id.debug', 'Before inserting into igf_sl_lor, loan_id:'||tbh_rec.loan_id);
1035 END IF;
1036 OPEN c_sl_loans (cp_n_loan_id => tbh_rec.loan_id);
1037 FETCH c_sl_loans INTO l_v_ext_loan_id_txt ;
1038 CLOSE c_sl_loans ;
1039
1040 igf_sl_lor_pkg.update_row (
1041 x_Mode => 'R',
1042 x_rowid => tbh_rec.row_id,
1043 x_origination_id => tbh_rec.origination_id,
1044 x_loan_id => tbh_rec.loan_id,
1045 x_sch_cert_date => l_sch_date,
1046 x_orig_status_flag => tbh_rec.orig_status_flag,
1047 x_orig_batch_id => p_batch_id,
1048 x_orig_batch_date => TRUNC(SYSDATE),
1049 x_chg_batch_id => NULL,
1050 x_orig_ack_date => tbh_rec.orig_ack_date,
1051 x_credit_override => tbh_rec.credit_override,
1052 x_credit_decision_date => tbh_rec.credit_decision_date,
1053 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
1054 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
1055 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
1056 x_pnote_status => tbh_rec.pnote_status,
1057 x_pnote_status_date => tbh_rec.pnote_status_date,
1058 x_pnote_id => tbh_rec.pnote_id,
1059 x_pnote_print_ind => tbh_rec.pnote_print_ind,
1060 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
1061 x_pnote_accept_date => tbh_rec.pnote_accept_date,
1062 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
1063 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
1064 x_orig_fee_perct => tbh_rec.orig_fee_perct,
1065 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
1066 x_borw_interest_ind => tbh_rec.borw_interest_ind,
1067 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
1068 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
1069 x_guarantee_amt => tbh_rec.guarantee_amt,
1070 x_guarantee_date => tbh_rec.guarantee_date,
1071 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
1072 x_guarnt_status_code => tbh_rec.guarnt_status_code,
1073 x_guarnt_status_date => tbh_rec.guarnt_status_date,
1074 x_lend_apprv_denied_code => NULL,
1075 x_lend_apprv_denied_date => NULL,
1076 x_lend_status_code => tbh_rec.lend_status_code,
1077 x_lend_status_date => tbh_rec.lend_status_date,
1078 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
1079 x_grade_level_code => tbh_rec.grade_level_code,
1080 x_enrollment_code => tbh_rec.enrollment_code,
1081 x_anticip_compl_date => tbh_rec.anticip_compl_date,
1082 x_borw_lender_id => NULL,
1083 x_duns_borw_lender_id => NULL,
1084 x_guarantor_id => NULL,
1085 x_duns_guarnt_id => NULL,
1086 x_prc_type_code => tbh_rec.prc_type_code,
1087 x_cl_seq_number => tbh_rec.cl_seq_number,
1088 x_last_resort_lender => tbh_rec.last_resort_lender,
1089 x_lender_id => NULL,
1090 x_duns_lender_id => NULL,
1091 x_lend_non_ed_brc_id => tbh_rec.lend_non_ed_brc_id,
1092 x_recipient_id => NULL,
1093 x_recipient_type => NULL,
1094 x_duns_recip_id => NULL,
1095 x_recip_non_ed_brc_id => NULL,
1096 x_rec_type_ind => tbh_rec.rec_type_ind,
1097 x_cl_loan_type => tbh_rec.cl_loan_type,
1098 x_cl_rec_status => NULL,
1099 x_cl_rec_status_last_update => NULL,
1100 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
1101 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
1102 x_mpn_confirm_code => NULL,
1103 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
1104 x_appl_loan_phase_code => NULL,
1105 x_appl_loan_phase_code_chg => NULL,
1106 x_appl_send_error_codes => NULL,
1107 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
1108 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
1109 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
1110 x_act_interest_rate => tbh_rec.act_interest_rate,
1111 x_service_type_code => tbh_rec.service_type_code,
1112 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
1113 x_sch_refund_amt => tbh_rec.sch_refund_amt,
1114 x_sch_refund_date => tbh_rec.sch_refund_date,
1115 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
1116 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
1117 x_p_person_id => tbh_rec.p_person_id,
1118 x_p_ssn_chg_date => NULL,
1119 x_p_dob_chg_date => NULL,
1120 x_p_permt_addr_chg_date => NULL,
1121 x_p_default_status => tbh_rec.p_default_status,
1122 x_p_signature_code => tbh_rec.p_signature_code,
1123 x_p_signature_date => tbh_rec.p_signature_date,
1124 x_s_ssn_chg_date => NULL,
1125 x_s_dob_chg_date => NULL,
1126 x_s_permt_addr_chg_date => NULL,
1127 x_s_local_addr_chg_date => NULL,
1128 x_s_default_status => tbh_rec.s_default_status,
1129 x_s_signature_code => tbh_rec.s_signature_code,
1130 x_pnote_batch_id => tbh_rec.pnote_batch_id,
1131 x_pnote_ack_date => tbh_rec.pnote_ack_date,
1132 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
1133 x_elec_mpn_ind => tbh_rec.elec_mpn_ind,
1134 x_borr_sign_ind => tbh_rec.borr_sign_ind,
1135 x_stud_sign_ind => tbh_rec.stud_sign_ind,
1136 x_borr_credit_auth_code => tbh_rec.borr_credit_auth_code,
1137 x_relationship_cd => tbh_rec.relationship_cd ,
1138 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num ,
1139 x_cps_trans_num => tbh_rec.cps_trans_num ,
1140 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt ,
1141 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt ,
1142 x_crdt_decision_status => tbh_rec.crdt_decision_status ,
1143 x_note_message => tbh_rec.note_message ,
1144 x_book_loan_amt => tbh_rec.book_loan_amt ,
1145 x_book_loan_amt_date => tbh_rec.book_loan_amt_date ,
1146 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt ,
1147 x_pymt_servicer_date => tbh_rec.pymt_servicer_date ,
1148 x_external_loan_id_txt => l_v_ext_loan_id_txt ,
1149 x_deferment_request_code => tbh_rec.deferment_request_code ,
1150 x_eft_authorization_code => tbh_rec.eft_authorization_code ,
1151 x_requested_loan_amt => tbh_rec.requested_loan_amt ,
1152 x_actual_record_type_code => tbh_rec.actual_record_type_code ,
1153 x_reinstatement_amt => tbh_rec.reinstatement_amt ,
1154 x_school_use_txt => tbh_rec.school_use_txt ,
1155 x_lender_use_txt => tbh_rec.lender_use_txt ,
1156 x_guarantor_use_txt => tbh_rec.guarantor_use_txt ,
1157 x_fls_approved_amt => tbh_rec.fls_approved_amt ,
1158 x_flu_approved_amt => tbh_rec.flu_approved_amt ,
1159 x_flp_approved_amt => tbh_rec.flp_approved_amt ,
1160 x_alt_approved_amt => tbh_rec.alt_approved_amt ,
1161 x_loan_app_form_code => tbh_rec.loan_app_form_code ,
1162 x_override_grade_level_code => tbh_rec.override_grade_level_code ,
1163 x_b_alien_reg_num_txt => tbh_rec.b_alien_reg_num_txt ,
1164 x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd ,
1165 x_acad_begin_date => tbh_rec.acad_begin_date ,
1166 x_acad_end_date => tbh_rec.acad_end_date
1167
1168 );
1169
1170 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1171 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.update_orig_batch_id.debug', 'Insertion into igf_sl_lor succeeded, loan_id:'||tbh_rec.loan_id);
1172 END IF;
1173
1174 END LOOP;
1175
1176 EXCEPTION
1177
1178 WHEN app_exception.record_lock_exception THEN
1179 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
1180 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.update_orig_batch_id.exception','Record Lock Exception');
1181 END IF;
1182 RAISE;
1183
1184 WHEN OTHERS THEN
1185 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
1186 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.update_orig_batch_id.exception',SQLERRM);
1187 END IF;
1188 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1189 fnd_message.set_token('NAME','IGF_SL_CL_ORIG.UPDATE_ORIG_BATCH_ID');
1190 igs_ge_msg_stack.add;
1191 app_exception.raise_exception;
1192
1193 END update_orig_batch_id;
1194
1195 -- This Procedure writes the Records into the output file and is Concurrent Procedure
1196 -- It returns the Request id and Based on the value data is committed
1197
1198 PROCEDURE sub_cl_originate( errbuf OUT NOCOPY VARCHAR2,
1199 retcode OUT NOCOPY NUMBER,
1200 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
1201 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1202 p_loan_number IN igf_sl_loans_all.loan_number%TYPE,
1203 p_loan_catg IN igf_lookups_view.lookup_code%TYPE,
1204 p_org_id IN NUMBER,
1205 p_relationship_cd IN VARCHAR2,
1206 p_media_type IN VARCHAR2,
1207 p_base_id IN VARCHAR2,
1208 p_school_id IN VARCHAR2,
1209 sch_non_ed_branch IN VARCHAR2
1210 )
1211 AS
1212 /***************************************************************
1213 Created By : mesriniv
1214 Date Created By : 2000/11/17
1215 Purpose : Concurrent Program to fetch transaction records and
1216 output the data to a file.
1217 Known Limitations,Enhancements or Remarks
1218 Change History :
1219 Bug Id : 1720677 Desc : Mapping of school id in the CommonLine Setup
1220 to ope_id of FinancialAid Office Setup.
1221 Who When What
1222 museshad 05-May-2005 Bug# 4346258
1223 Added the parameter 'base_id' in the call to the
1224 function get_cl_version(). The signature of
1225 this function has been changed so that it takes
1226 into account any overriding CL version for a
1227 specific Organization Unit in FFELP Setup override.
1228 mnade 7-Feb-2005 Bug 4133414 - Alt loan rec was required to be reseted before fetching data
1229 for next loan to avoid copying data of earlier loan to next one which does not have the same.
1230 smadathi 01-12-2004 Bug 4039480. Moved the logic to obtain the borrower and student
1231 details inside the cursor cur_loan_dtls for loop
1232 veramach 15-Apr-2004 bug 3054469
1233 Impact of obsoleting igf_aw_gen_002.get_fed_efc and replacing the call
1234 with igf_aw_packng_subfns.get_fed_efc
1235 bkkumar 04-Apr-04 Bug 3409969 Added the alt_rel_code as the impact to the pick_setup routine.
1236 Added the code to populate the alt_rel_code field in the output file.
1237 ugummall 29-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
1238 1. Added 3 new parameters namely p_base_id, p_school_id, sch_non_ed_branch.
1239 2. Removed the cursors cur_ope_id and cur_school_id as we can use
1240 p_school_id passed in parameter and also sch_non_ed_branch.
1241 3. Added new cursor cur_get_school_name to get the school name.
1242
1243 agairola 15-Mar-2002 Modified the Update Row call of the IGF_SL_LOANS_PKG
1244 to include the Borrower Determination as part of Refunds
1245 DLD - 2144600
1246 mesriniv 05-APR-2001 Changed the occurrences of field fao_id
1247 to ope_id
1248 ***************************************************************/
1249
1250 no_loan_data EXCEPTION;
1251
1252 lv_batch_id igf_sl_cl_batch.batch_id%TYPE;
1253 lv_cbth_id igf_sl_cl_batch.cbth_id%TYPE;
1254 lv_file_ident_code igf_sl_cl_file_type.file_ident_code%TYPE;
1255 lv_file_ident_name igf_sl_cl_file_type.file_ident_name%TYPE;
1256 lv_recipient_type igf_sl_cl_recipient.recipient_type%TYPE;
1257 lv_recipient_id igf_sl_cl_recipient.recipient_id%TYPE;
1258 lv_recip_non_ed_brc_id igf_sl_cl_recipient.recip_non_ed_brc_id%TYPE;
1259 l_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
1260 l_efc igf_ap_fa_base_rec_all.efc_f%TYPE;
1261 lv_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE;
1262 lv_eft_authorization igf_sl_cl_setup.eft_authorization%TYPE;
1263 l_dummy_pell_efc NUMBER;
1264
1265 lv_source_name igf_lookups_view.meaning%TYPE;
1266
1267 lv_cl_version VARCHAR2(30);
1268
1269 lv_s_foreign_postal_code igf_sl_lor_loc.s_foreign_postal_code%TYPE;
1270 lv_p_foreign_postal_code igf_sl_lor_loc.p_foreign_postal_code%TYPE;
1271 l_est_fin igf_aw_award_all.accepted_amt%TYPE;
1272 l_plus_cert_amt igf_aw_award_all.accepted_amt%TYPE;
1273 l_alt_cert_amt igf_aw_award_all.accepted_amt%TYPE;
1274 l_tot_alt_debt igf_sl_alt_borw.fed_sls_debt%TYPE;
1275 alter_rec igf_sl_alt_borw%ROWTYPE;
1276
1277 l_count_4 NUMBER;
1278 lv_header_rec VARCHAR2(1000);
1279 lv_trailer_rec VARCHAR2(1000);
1280 lv_trans_count NUMBER(6);
1281 lv_8_disb_count NUMBER(6);
1282 lv_first_part_trans_rec VARCHAR2(2000);
1283 lv_third_part_trans_rec VARCHAR2(1000);
1284 lv_fifth_part_trans_rec VARCHAR2(1000);
1285 lv_seventh_part_trans_rec VARCHAR2(1000);
1286 lv_1_final_disb_date VARCHAR2(1000);
1287 lv_1_final_gross_amt VARCHAR2(1000);
1288 lv_1_final_hold_rel_ind VARCHAR2(1000);
1289 lv_8_final_disb_date VARCHAR2(1000);
1290 lv_8_final_gross_amt VARCHAR2(1000);
1291 lv_8_final_hold_rel_ind VARCHAR2(1000);
1292 lv_8_direct_to_borr_flag VARCHAR2(20);
1293 lv_l_direct_to_borr_flag VARCHAR2(20);
1294 lv_counter NUMBER;
1295 lv_row_id ROWID;
1296 lv_software_code VARCHAR2(4);
1297 lv_software_version VARCHAR2(4);
1298 lv_process_year VARCHAR2(2);
1299 l_borw_ind_code VARCHAR2(1);
1300 l_fed_appl_code VARCHAR2(1);
1301 l_s_ssn VARCHAR2(9);
1302 l_trailer_datetime VARCHAR2(14);
1303 ln_num_of_disb NUMBER;
1304 lv_indi VARCHAR2(1);
1305 p_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE;
1306 p_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE;
1307 lv_stud_sign_ind igf_sl_lor.stud_sign_ind%TYPE;
1308 lv_borw_interest_ind igf_sl_lor.borw_interest_ind%TYPE;
1309 lv_borr_sign_ind igf_sl_lor.borr_sign_ind%TYPE;
1310 lv_rel_code VARCHAR2(30);
1311 lv_person_id NUMBER;
1312 lv_party_id NUMBER;
1313 l_n_send2_rec_cnt NUMBER;
1314 l_n_send5_rec_cnt NUMBER;
1315 l_n_send7_rec_cnt NUMBER;
1316 l_v_trailer_date VARCHAR2(8);
1317 l_v_trailer_time VARCHAR2(6);
1318 l_v_owner_code VARCHAR2(30);
1319 l_at4Record VARCHAR2(1000) := '';
1320 -- To get the School Id
1321 -- Get the details of
1322 /*
1323 CURSOR cur_get_setup(p_cal_type igf_sl_cl_setup_all.ci_cal_type%TYPE,
1324 p_seq_number igf_sl_cl_setup_all.ci_sequence_number%TYPE,
1325 p_rel_code igf_sl_cl_setup_all.relationship_cd%TYPE,
1326 p_party_id igf_sl_cl_setup_all.party_id%TYPE
1327 ) IS
1328 SELECT eft_authorization
1329 FROM igf_sl_cl_setup
1330 WHERE ci_cal_type = p_cal_type
1331 AND ci_sequence_number = p_seq_number
1332 AND relationship_cd = p_rel_code
1333 AND NVL(party_id,-100) = NVL(p_party_id,-100);
1334 */
1335 -- Removed the cursor cur_school_id as p_school_id is now used instead of fetching School ID.
1336
1337
1338
1339 -- Get OPE_ID for the School
1340 -- The cursor here cur_ope_id is removed as we can use parameter p_school_id
1341
1342 student_dtl_cur igf_sl_gen.person_dtl_cur;
1343 parent_dtl_cur igf_sl_gen.person_dtl_cur;
1344
1345 -- Cursor to fetch Student License No.,State and Citizenship Status
1346
1347 CURSOR cur_isir_depend_status
1348 IS
1349 SELECT isir.dependency_status
1350 FROM igf_ap_fa_base_rec fabase, igf_ap_isir_matched isir
1351 WHERE isir.base_id = fabase.base_id
1352 AND fabase.person_id = loan_rec.student_id
1353 AND isir.payment_isir = 'Y'
1354 AND isir.system_record_type = 'ORIGINAL';
1355
1356 -- Cursor to fetch school name
1357
1358 CURSOR cur_get_school_name
1359 IS
1360 SELECT meaning
1361 FROM igf_lookups_view
1362 WHERE lookup_type = 'IGF_AP_SCHOOL_OPEID'
1363 AND lookup_code = p_school_id;
1364
1365 -- To fetch the all the Disbursement dates,Amounts and Hold Rel Indicator
1366
1367 CURSOR cur_disb_details
1368 IS
1369 SELECT disb_date,
1370 NVL(disb_accepted_amt,0) disb_accepted_amt,
1371 hold_rel_ind, direct_to_borr_flag
1372 FROM igf_aw_awd_disb
1373 WHERE award_id = loan_rec.award_id
1374 ORDER
1375 BY disb_num;
1376
1377
1378 --Cursor to fetch the data from the FA Base Record for the student baseid
1379 CURSOR cur_get_fabase
1380 IS
1381 SELECT TRUNC(coa_f)
1382 FROM igf_ap_fa_base_rec
1383 WHERE base_id = loan_rec.base_id;
1384
1385 --Fetch the Estimated Financial Aid
1386 CURSOR cur_get_fin_aid(
1387 p_award_status_1 igf_aw_award.award_status%TYPE,
1388 p_award_status_2 igf_aw_award.award_status%TYPE
1389 )
1390 IS
1391 SELECT TRUNC(SUM(NVL(NVL(accepted_amt,offered_amt),0))) etsimated_fin
1392 FROM igf_aw_award
1393 WHERE base_id = loan_rec.base_id
1394 AND award_id <> loan_rec.award_id
1395 AND (award_status = p_award_status_1 OR award_status = p_award_status_2);
1396
1397
1398 --Cursor to fetch the Total ALt Loan debt
1399 CURSOR cur_get_alt_debt
1400 IS
1401 SELECT LPAD(SUM(NVL(fed_stafford_loan_debt,0) + NVL(fed_sls_debt,0) +
1402 NVL(heal_debt,0) + NVL(perkins_debt,0) +
1403 NVL(other_debt,0)),7,0) alt_loan_debt
1404 FROM igf_sl_alt_borw,
1405 igf_sl_loans_v loanv
1406 WHERE igf_sl_alt_borw.loan_id = loanv.loan_id
1407 AND loanv.student_id = loan_rec.student_id
1408 AND loanv.ci_cal_type <> loan_rec.ci_cal_type
1409 AND loanv.ci_sequence_number <> loan_rec.ci_sequence_number;
1410
1411
1412 -- Cursor to fetch the Alternate Borrower Details if any for the Loan ID
1413 CURSOR cur_get_alternate
1414 IS
1415 SELECT alt.*
1416 FROM igf_sl_alt_borw alt
1417 WHERE loan_id=loan_rec.loan_id;
1418
1419 --
1420 -- Cursor to fetch number of disb recs for loan
1421 --
1422 CURSOR cur_get_disb_num (p_award_id igf_aw_award_all.award_id%TYPE)
1423 IS
1424 SELECT COUNT(disb_num)
1425 FROM igf_aw_awd_disb
1426 WHERE award_id = p_award_id;
1427
1428
1429 CURSOR c_sl_lor (cp_n_loan_id igf_sl_loans_all.loan_id%TYPE) IS
1430 SELECT lor.*
1431 FROM igf_sl_lor_all lor
1432 WHERE loan_id = cp_n_loan_id;
1433
1434 rec_c_sl_lor c_sl_lor%ROWTYPE;
1435
1436 -- FA 161 - rajagupt - Bug # 5006583
1437 CURSOR citizenship_dtl_cur (cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE) IS
1438 SELECT
1439 pct.restatus_code restatus_code
1440 FROM igs_lookup_values lkup,
1441 igs_pe_eit_restatus_v pct
1442 WHERE lkup.lookup_type = 'PE_CITI_STATUS'
1443 AND trim(lkup.lookup_code) = trim(pct.restatus_code)
1444 AND pct.person_id = cp_person_id
1445 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
1446 citizenship_dtl_rec citizenship_dtl_cur%ROWTYPE;
1447
1448 CURSOR cur_fa_mapping ( cp_citizenship_status igf_sl_pe_citi_map.pe_citi_stat_code%TYPE,
1449 cp_cal_type igf_sl_pe_citi_map.ci_cal_type%TYPE,
1450 cp_sequence_number igf_sl_pe_citi_map.ci_sequence_number%TYPE) IS
1451 SELECT trim(fa_citi_stat_code) fa_citi_stat_code FROM igf_sl_pe_citi_map
1452 WHERE pe_citi_stat_code = cp_citizenship_status
1453 AND ci_sequence_number = cp_sequence_number
1454 AND ci_cal_type = cp_cal_type;
1455 cur_fa_mapping_rec cur_fa_mapping%ROWTYPE;
1456
1457
1458 --Get the Software Code
1459 -- masehgal 2477912 Made "IGS " to resolve NCAT reported errors
1460 FUNCTION get_software_code
1461 RETURN VARCHAR2 IS
1462 BEGIN
1463 RETURN 'IGS ';
1464 END get_software_code;
1465
1466 --Get the Software Version
1467 -- masehgal 2477912 Made "1157" to resolve NCAT reported errors
1468 FUNCTION get_software_version
1469 RETURN VARCHAR2 IS
1470 BEGIN
1471 RETURN '1157';
1472 END get_software_version;
1473
1474 --Get the Process Year
1475 FUNCTION get_process_year
1476 RETURN VARCHAR2 IS
1477 BEGIN
1478 RETURN NVL(TO_CHAR(SYSDATE,'YY'),'01');
1479 END get_process_year;
1480
1481
1482 PROCEDURE cosigner_name_validation
1483 ( fName IN OUT NOCOPY VARCHAR2,
1484 lName IN OUT NOCOPY VARCHAR2
1485 )
1486 IS
1487 BEGIN
1488 fName := NVL(fName,' ');
1489 lName := NVL(lName,' ');
1490
1491 IF(fName <> ' ' and lName = ' ') THEN
1492 lName := 'NLN';
1493 END IF;
1494
1495 IF(lName <> ' ' and fName = ' ') THEN
1496 fName := 'NFN';
1497 END IF;
1498 END cosigner_name_validation;
1499
1500 -- To fetch the Contact Information of the Borrower
1501
1502 PROCEDURE get_contact_info(p_student_id NUMBER,
1503 lv_s_foreign_postal_code OUT NOCOPY VARCHAR2 ,
1504 lv_p_foreign_postal_code OUT NOCOPY VARCHAR2)
1505 IS
1506 BEGIN
1507
1508 -- ##################################################
1509 -- Get the Phone and Foreign Postal Code Details
1510 NULL;
1511 END get_contact_info;
1512
1513 BEGIN
1514 g_debug_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1515 l_count_4 := 0;
1516 lv_trans_count := 0;
1517 lv_8_disb_count := 0;
1518 retcode:=0;
1519 igf_aw_gen.set_org_id(p_org_id);
1520
1521 l_n_send2_rec_cnt := 0;
1522 l_n_send5_rec_cnt := 0;
1523 l_n_send7_rec_cnt := 0;
1524
1525 -- Fetch the Recipient Description
1526 OPEN cur_recip_desc(p_relationship_cd);
1527 FETCH cur_recip_desc INTO recip_desc_rec;
1528 IF cur_recip_desc%NOTFOUND THEN
1529 CLOSE cur_recip_desc;
1530
1531 --The recipient information does not exist
1532 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1533 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','Recipient description not found');
1534 END IF;
1535 fnd_message.set_name('IGF','IGF_SL_RECIP_NOT_FOUND');
1536 fnd_message.set_token('REL_CODE',p_relationship_cd);
1537 fnd_file.put_line(fnd_file.log,fnd_message.get);
1538 RETURN;
1539 END IF;
1540 CLOSE cur_recip_desc;
1541
1542 -- Check whether there are loan records to be originated after Validation.
1543 IF p_loan_catg = 'CL_STAFFORD' THEN
1544 p_fed_fund_1 := 'FLS';
1545 p_fed_fund_2 := 'FLU';
1546 END IF;
1547
1548 IF p_loan_catg = 'CL_PLUS' THEN
1549 p_fed_fund_1 := 'FLP';
1550 p_fed_fund_2 := 'FLP';
1551 END IF;
1552
1553 IF p_loan_catg = 'CL_ALT' THEN
1554 p_fed_fund_1 := 'ALT';
1555 p_fed_fund_2 := 'ALT';
1556 END IF;
1557
1558 IF p_loan_catg = 'CL_GPLUSFL' THEN
1559 p_fed_fund_1 := 'GPLUSFL';
1560 p_fed_fund_2 := 'GPLUSFL';
1561 END IF;
1562
1563 IF(fnd_log.level_statement >= g_debug_runtime_level)THEN
1564 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','p_ci_cal_type:'||p_ci_cal_type);
1565 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','p_ci_sequence_number:'||p_ci_sequence_number);
1566 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','p_fed_fund_1:'||p_fed_fund_1);
1567 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','p_fed_fund_2:'||p_fed_fund_2);
1568 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','p_loan_number:'||NVL(p_loan_number,'NULL'));
1569 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','recipient_type:'||NVL(recip_desc_rec.recipient_type,'NULL'));
1570 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','recipient_id:'||recip_desc_rec.recipient_id);
1571 END IF;
1572
1573 OPEN cur_loan_dtls(p_ci_cal_type,
1574 p_ci_sequence_number,
1575 p_fed_fund_1,
1576 p_fed_fund_2,
1577 p_loan_number,
1578 'V',
1579 'Y',
1580 p_relationship_cd,
1581 p_base_id,
1582 p_school_id
1583 );
1584 FETCH cur_loan_dtls INTO loan_rec;
1585 IF cur_loan_dtls%NOTFOUND THEN
1586 CLOSE cur_loan_dtls;
1587 IF(fnd_log.level_statement >= g_debug_runtime_level)THEN
1588 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','No loan data found');
1589 END IF;
1590 RAISE no_loan_data;
1591 END IF;
1592
1593 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1594 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Processing Loan Number:' || loan_rec.loan_number);
1595 END IF;
1596
1597 CLOSE cur_loan_dtls;
1598
1599
1600 --Fetch School Id for the Recipient Details fetched
1601 -- Removed the cursor cur_school_id as p_school_id is now used instead of fetching School ID.
1602
1603 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1604 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_eft_authorization:' || lv_eft_authorization);
1605 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'p_media_type:' || p_media_type);
1606 END IF;
1607
1608
1609 -- code which fetches ope_id code using cur_ope_id into lv_ope_id is removed.
1610 -- as lv_ope_id is being replaced by new parameter p_school_id w.r.t. FA 126.
1611
1612
1613 -- Fetch Data For concatenating values for Header Record to get value for Processing Year for concatenation
1614
1615 lv_process_year := get_process_year;
1616
1617 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1618 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_process_year:' || lv_process_year);
1619 END IF;
1620
1621 -- Compute Batch ID.
1622 -- Note : Though Batch ID is computed, not sending this batch id to commonline, as our computed
1623 -- Batch ID is more than the field size given by CL. Further, it is optional for CL.
1624
1625 lv_batch_id := NULL;
1626 lv_batch_id := '@A'
1627 ||RPAD(NVL(recip_desc_rec.recipient_id,' '),6)
1628 ||RPAD(NVL(recip_desc_rec.recip_non_ed_brc_id,' '),2)
1629 ||RPAD(NVL(lv_process_year,' '),2)
1630 ||RPAD(NVL(p_school_id,' '),8)
1631 ||RPAD(NVL(sch_non_ed_branch,' '),4)
1632 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS');
1633
1634 -- Get the Software Details
1635 lv_software_code := get_software_code;
1636 lv_software_version := get_software_version;
1637
1638 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1639 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_software_code:' || lv_software_code);
1640 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_software_version:' || lv_software_version);
1641 END IF;
1642
1643 -- museshad(Bug# 4346258) - Added the parameter p_base_id due to change in the
1644 -- signature of the function 'get_cl_version()'
1645 lv_cl_version := igf_sl_gen.get_cl_version(p_ci_cal_type, p_ci_sequence_number,loan_rec.relationship_cd,p_base_id);
1646 lv_file_ident_code := igf_sl_gen.get_cl_file_type(lv_cl_version, 'CL_ORIG_SEND', 'FILE-IDENT-CODE');
1647 lv_file_ident_name := igf_sl_gen.get_cl_file_type(lv_cl_version, 'CL_ORIG_SEND', 'FILE-IDENT-NAME');
1648
1649 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1650 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_file_ident_code:' || lv_file_ident_code);
1651 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_file_ident_name:' || lv_file_ident_name);
1652 END IF;
1653
1654 -- Get School name for p_school_id
1655 OPEN cur_get_school_name;
1656 FETCH cur_get_school_name INTO lv_source_name;
1657 CLOSE cur_get_school_name;
1658
1659 -- To create a Header Record for each of the files
1660 l_trailer_datetime :=TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'); --This is printed in Header.Same should be printed in trailer also.
1661 --Even if there is one sec diff CL Tool gives error.
1662 l_v_trailer_date := TO_CHAR(SYSDATE,'YYYYMMDD');
1663 l_v_trailer_time := TO_CHAR(SYSDATE,'HH24MISS');
1664
1665 lv_header_rec:=NULL;
1666 -- masehgal 2477912 Made record "upper" to resolve NCAT reported errors
1667 IF lv_cl_version = 'RELEASE-5' THEN
1668 lv_header_rec:=UPPER('@H'||RPAD(NVL(lv_software_code,' '),4,' ')
1669 ||RPAD(NVL(lv_software_version,' '),4,' ')
1670 ||RPAD(' ',12,' ')
1671 ||l_v_trailer_date
1672 ||l_v_trailer_time
1673 ||l_v_trailer_date
1674 ||l_v_trailer_time
1675 ||RPAD(lv_file_ident_name,19)
1676 ||RPAD(lv_file_ident_code,5)
1677 ||RPAD(NVL(lv_source_name,' '),32,' ')
1678 ||RPAD(NVL(p_school_id,' '),8,' ')
1679 ||RPAD(' ',2,' ')
1680 ||RPAD(NVL(sch_non_ed_branch,' '),4,' ')
1681 ||'S'
1682 ||RPAD(NVL(recip_desc_rec.recip_description,' '),32,' ')
1683 ||RPAD(NVL(recip_desc_rec.recipient_id,' '),8,' ')
1684 ||' '
1685 ||RPAD(NVL(recip_desc_rec.recip_non_ed_brc_id,' '),4,' ')
1686 ||NVL(p_media_type,' ')
1687 ||RPAD(' ',9,' ')
1688 ||RPAD(' ',9,' ')
1689 ||LPAD(' ',773,' ')
1690 ||'*');
1691 ELSIF lv_cl_version = 'RELEASE-4' THEN
1692 lv_header_rec:=UPPER('@H'||RPAD(NVL(lv_software_code,' '),4,' ')
1693 ||RPAD(NVL(lv_software_version,' '),4,' ')
1694 ||RPAD(' ',12,' ')
1695 ||l_v_trailer_date
1696 ||l_v_trailer_time
1697 ||l_v_trailer_date
1698 ||l_v_trailer_time
1699 ||RPAD(lv_file_ident_name,19)
1700 ||RPAD(lv_file_ident_code,5)
1701 ||RPAD(NVL(lv_source_name,' '),32,' ')
1702 ||RPAD(NVL(p_school_id,' '),8,' ')
1703 ||RPAD(' ',2,' ')
1704 ||RPAD(NVL(sch_non_ed_branch,' '),4,' ')
1705 ||'S'
1706 ||RPAD(NVL(recip_desc_rec.recip_description,' '),32,' ')
1707 ||RPAD(NVL(recip_desc_rec.recipient_id,' '),8,' ')
1708 ||' '
1709 ||RPAD(NVL(recip_desc_rec.recip_non_ed_brc_id,' '),4,' ')
1710 ||NVL(p_media_type,' ')
1711 ||RPAD(' ',9,' ')
1712 ||RPAD(' ',9,' ')
1713 ||LPAD(' ',693,' ')
1714 ||'*');
1715 END IF;
1716
1717 -- To output the Header Record into every file that would be created for the distinct Recipient details
1718
1719
1720 fnd_file.put_line(fnd_file.output,UPPER(lv_header_rec));
1721
1722 -- To insert the Computed Batch Number into CL Batch Table
1723 lv_row_id := NULL;
1724 igf_sl_cl_batch_pkg.insert_row (
1725 x_mode => 'R',
1726 x_rowid => lv_row_id,
1727 x_cbth_id => lv_cbth_id,
1728 x_batch_id => lv_batch_id,
1729 x_file_creation_date => TRUNC(SYSDATE),
1730 x_file_trans_date => TRUNC(SYSDATE),
1731 x_file_ident_code => RPAD(lv_file_ident_code,5),
1732 x_recipient_id => recip_desc_rec.recipient_id,
1733 x_recip_non_ed_brc_id => recip_desc_rec.recip_non_ed_brc_id,
1734 x_source_id => p_school_id,
1735 x_source_non_ed_brc_id => sch_non_ed_branch,
1736 x_send_resp => 'S',
1737 x_record_count_num => NULL ,
1738 x_total_net_disb_amt => NULL ,
1739 x_total_net_eft_amt => NULL ,
1740 x_total_net_non_eft_amt => NULL ,
1741 x_total_reissue_amt => NULL ,
1742 x_total_cancel_amt => NULL ,
1743 x_total_deficit_amt => NULL ,
1744 x_total_net_cancel_amt => NULL ,
1745 x_total_net_out_cancel_amt => NULL
1746 );
1747
1748 FOR loan_rec_temp IN cur_loan_dtls(p_ci_cal_type,
1749 p_ci_sequence_number,
1750 p_fed_fund_1,
1751 p_fed_fund_2,
1752 p_loan_number,
1753 'V',
1754 'Y',
1755 p_relationship_cd,
1756 p_base_id,
1757 p_school_id
1758 ) LOOP
1759 -- Initialise the Cursor Record variable to NULL.
1760
1761 BEGIN
1762
1763 loan_rec := loan_rec_temp;
1764 igf_sl_award.pick_setup(loan_rec.base_id,p_ci_cal_type,p_ci_sequence_number,lv_rel_code,lv_person_id,lv_party_id,loan_rec_temp.alt_rel_code);
1765 -- FACR116 Check if the alt_loan_code is not null and the set up record is present or not
1766 IF loan_rec_temp.fed_fund_code = 'ALT' THEN
1767 IF loan_rec_temp.alt_loan_code IS NULL THEN
1768 fnd_message.set_name('IGF','IGF_AW_NO_ALT_LOAN_CODE');
1769 fnd_file.put_line(fnd_file.log,fnd_message.get);
1770 RAISE SKIP_RECORD;
1771 ELSIF lv_rel_code IS NULL AND lv_person_id IS NULL THEN
1772 fnd_message.set_name('IGF','IGF_SL_NO_ALT_SETUP');
1773 fnd_file.put_line(fnd_file.log,fnd_message.get);
1774 RAISE SKIP_RECORD;
1775 END IF;
1776 END IF;
1777 /*
1778 OPEN cur_get_setup(p_ci_cal_type,p_ci_sequence_number,lv_rel_code,lv_party_id);
1779 FETCH cur_get_setup INTO lv_eft_authorization;
1780 CLOSE cur_get_setup;
1781 */
1782
1783 -- Call Get Information procedure to fetch values for Contact Information
1784 get_contact_info(loan_rec.student_id,lv_s_foreign_postal_code,lv_p_foreign_postal_code);
1785
1786 -- Get Few student Details from ISIR.
1787 OPEN cur_isir_depend_status;
1788 FETCH cur_isir_depend_status INTO lv_dependency_status;
1789 IF cur_isir_depend_status%NOTFOUND THEN
1790 CLOSE cur_isir_depend_status;
1791 fnd_message.set_name('IGF','IGF_GE_REC_NO_DATA_FOUND');
1792 fnd_message.set_token('P_RECORD','igf_ap_fa_base_rec');
1793 fnd_file.put_line(fnd_file.log,fnd_message.get);
1794 IF(fnd_log.level_statement >= g_debug_runtime_level)THEN
1795 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','Record Data not found');
1796 END IF;
1797 RAISE SKIP_RECORD;
1798 ELSE
1799 CLOSE cur_isir_depend_status;
1800 END IF;
1801
1802 --Fetch details of student and parent
1803 igf_sl_gen.get_person_details(loan_rec.student_id,student_dtl_cur);
1804 FETCH student_dtl_cur INTO student_dtl_rec;
1805 CLOSE student_dtl_cur;
1806 igf_sl_gen.get_person_details(loan_rec.p_person_id,parent_dtl_cur);
1807 FETCH parent_dtl_cur INTO parent_dtl_rec;
1808 CLOSE parent_dtl_cur;
1809
1810 --FA 161 - Due to the introduction of a new mapping form, we don't use the lookup tag but rather require the lookup_code itself to
1811 -- determine the mapping values...and hence the below blocks of citizenship_dtl_cur
1812 -- Determine the Student's citizenhsip with the help of new mapping
1813 -- parent_dtl_rec.p_citizenship_status and student_dtl_rec.p_citizenship_status refer to the FA citizenship mapped code instead
1814 -- of OSS lookup tag value
1815 OPEN citizenship_dtl_cur(loan_rec.student_id);
1816 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
1817 OPEN cur_fa_mapping (cp_citizenship_status => citizenship_dtl_rec.restatus_code,
1818 cp_cal_type => p_ci_cal_type,
1819 cp_sequence_number => p_ci_sequence_number);
1820 FETCH cur_fa_mapping INTO cur_fa_mapping_rec;
1821 student_dtl_rec.p_citizenship_status := cur_fa_mapping_rec.fa_citi_stat_code;
1822 CLOSE cur_fa_mapping;
1823 CLOSE citizenship_dtl_cur;
1824
1825 -- Determine the Parent's citizenhsip with the help of new mapping
1826 OPEN citizenship_dtl_cur(loan_rec.p_person_id);
1827 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
1828 OPEN cur_fa_mapping (cp_citizenship_status => citizenship_dtl_rec.restatus_code,
1829 cp_cal_type => p_ci_cal_type,
1830 cp_sequence_number => p_ci_sequence_number);
1831 FETCH cur_fa_mapping INTO cur_fa_mapping_rec;
1832 parent_dtl_rec.p_citizenship_status := cur_fa_mapping_rec.fa_citi_stat_code;
1833 CLOSE cur_fa_mapping;
1834 CLOSE citizenship_dtl_cur;
1835
1836 -- logging details of student and parent
1837
1838 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1839 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student Full Name :' ||student_dtl_rec.p_full_name );
1840 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student First Name :' ||student_dtl_rec.p_first_name);
1841 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student Birth date :' ||student_dtl_rec.p_date_of_birth);
1842 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student address1 :' ||student_dtl_rec.p_permt_addr1);
1843 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student address2 :' ||student_dtl_rec.p_permt_addr2);
1844 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student License Num :' ||student_dtl_rec.p_license_num);
1845 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student License State :' ||student_dtl_rec.p_license_state);
1846 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Student SSN :' ||student_dtl_rec.p_ssn);
1847 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower Full Name :' ||parent_dtl_rec.p_full_name );
1848 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower First Name :' ||parent_dtl_rec.p_first_name);
1849 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower Birth date :' ||parent_dtl_rec.p_date_of_birth);
1850 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower address1 :' ||parent_dtl_rec.p_permt_addr1);
1851 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower address2 :' ||parent_dtl_rec.p_permt_addr2);
1852 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower License Num :' ||parent_dtl_rec.p_license_num);
1853 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower License State :' ||parent_dtl_rec.p_license_state);
1854 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Borrower SSN :' ||parent_dtl_rec.p_ssn);
1855 END IF;
1856
1857 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1858 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_license_number:' ||lv_s_license_number);
1859 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_license_state:' ||lv_s_license_state);
1860 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_citizenship_status:' ||lv_s_citizenship_status);
1861 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_alien_reg_num:' ||lv_alien_reg_num);
1862 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_dependency_status:' ||lv_dependency_status);
1863 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_legal_res_date:' ||lv_s_legal_res_date);
1864 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_legal_res_state:' ||lv_s_legal_res_state);
1865 END IF;
1866
1867 --Code added for bug 3603289 start
1868 lv_s_license_number := student_dtl_rec.p_license_num;
1869 lv_s_license_state := student_dtl_rec.p_license_state;
1870 lv_s_citizenship_status := student_dtl_rec.p_citizenship_status;
1871 lv_alien_reg_num := student_dtl_rec.p_alien_reg_num;
1872 lv_s_legal_res_date := student_dtl_rec.p_legal_res_date;
1873 lv_s_legal_res_state := student_dtl_rec.p_state_of_legal_res;
1874 --Code added for bug 3603289 end
1875
1876 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1877 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_license_number:' ||lv_s_license_number);
1878 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_license_state:' ||lv_s_license_state);
1879 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_citizenship_status:' ||lv_s_citizenship_status);
1880 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_alien_reg_num:' ||lv_alien_reg_num);
1881 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_dependency_status:' ||lv_dependency_status);
1882 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_legal_res_date:' ||lv_s_legal_res_date);
1883 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_s_legal_res_state:' ||lv_s_legal_res_state);
1884 END IF;
1885
1886 lv_s_permt_phone := NULL;
1887 lv_p_permt_phone := NULL;
1888 l_phone := NULL;
1889
1890 --Whether Student or Parent get the Phone Number
1891 lv_s_permt_phone := igf_sl_gen.get_person_phone(loan_rec.student_id);
1892 lv_p_permt_phone := igf_sl_gen.get_person_phone(loan_rec.p_person_id);
1893
1894 l_coa := NULL;
1895 l_efc := NULL;
1896 l_est_fin := NULL;
1897
1898 --Fetch the COA and EFC with respective to the new DLD
1899 OPEN cur_get_fabase;
1900 FETCH cur_get_fabase INTO l_coa;
1901 CLOSE cur_get_fabase;
1902
1903 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1904 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'l_coa:' ||l_coa);
1905 END IF;
1906
1907 -- Get the EFC months for the for the Award Yr
1908 igf_aw_packng_subfns.get_fed_efc(
1909 l_base_id => loan_rec.base_id,
1910 l_awd_prd_code => NULL,
1911 l_efc_f => l_dummy_pell_efc,
1912 l_pell_efc => l_dummy_pell_efc,
1913 l_efc_ay => l_efc
1914 );
1915
1916 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1917 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','l_efc:'||l_efc);
1918 END IF;
1919
1920 l_efc := TRUNC(l_efc);
1921
1922 --Fetch the Estimated Financial Aid
1923 OPEN cur_get_fin_aid('OFFERED','ACCEPTED');
1924 FETCH cur_get_fin_aid INTO l_est_fin;
1925 CLOSE cur_get_fin_aid;
1926
1927 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1928 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'l_est_fin:' ||l_est_fin);
1929 END IF;
1930
1931 OPEN cur_get_disb_num(loan_rec.award_id);
1932 FETCH cur_get_disb_num INTO ln_num_of_disb;
1933 CLOSE cur_get_disb_num;
1934
1935 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1936 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'ln_num_of_disb:' ||ln_num_of_disb);
1937 END IF;
1938
1939
1940 IF lv_cl_version = 'RELEASE-5' THEN
1941 IF ln_num_of_disb > 4 THEN
1942 lv_indi := 'Y';
1943 ELSE
1944 lv_indi := ' ';
1945 END IF;
1946 IF loan_rec.borr_sign_ind = 'Y' THEN
1947 lv_borr_sign_ind := 'Y';
1948 ELSE
1949 lv_borr_sign_ind := ' ';
1950 END IF;
1951 IF loan_rec.stud_sign_ind = 'Y' THEN
1952 lv_stud_sign_ind := 'Y';
1953 ELSE
1954 lv_stud_sign_ind := ' ';
1955 END IF;
1956
1957 IF loan_rec.borw_interest_ind = 'Y' THEN
1958 lv_borw_interest_ind := 'Y';
1959 ELSE
1960 lv_borw_interest_ind := ' ';
1961 END IF;
1962
1963 END IF;
1964
1965 IF lv_cl_version = 'RELEASE-4' THEN
1966 lv_borr_sign_ind := ' ';
1967 lv_indi := ' ';
1968 lv_stud_sign_ind := ' ';
1969 END IF;
1970
1971 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1972 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_indi:' ||lv_indi);
1973 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_borr_sign_ind:' ||lv_borr_sign_ind);
1974 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'lv_stud_sign_ind:' ||lv_stud_sign_ind);
1975 END IF;
1976
1977 OPEN c_sl_lor (cp_n_loan_id => loan_rec.loan_id);
1978 FETCH c_sl_lor INTO rec_c_sl_lor;
1979 CLOSE c_sl_lor ;
1980 IF NVL(rec_c_sl_lor.loan_app_form_code,'M') = 'M' THEN
1981 rec_c_sl_lor.deferment_request_code := ' ';
1982 rec_c_sl_lor.borr_credit_auth_code := ' ';
1983 END IF;
1984 lv_eft_authorization := rec_c_sl_lor.eft_authorization_code;
1985
1986
1987 IF p_loan_catg = 'CL_STAFFORD' THEN
1988
1989 -- common for RELEASE- 4 AND RELEASE-5. No Change for stafford loans
1990
1991
1992 student_dtl_rec.p_permt_zip := TRANSLATE (UPPER(LTRIM(RTRIM(student_dtl_rec.p_permt_zip))),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*_+=-,./?><():; ','1234567890');
1993 lv_first_part_trans_rec:='@1'
1994 ||NVL(loan_rec.rec_type_ind,' ')
1995 ||RPAD(NVL(student_dtl_rec.p_last_name,' '),35,' ')
1996 ||RPAD(NVL(student_dtl_rec.p_first_name,' '),12,' ')
1997 ||RPAD(NVL(student_dtl_rec.p_middle_name,' '),1,' ')
1998 ||LPAD(NVL(student_dtl_rec.p_ssn,' '),9,' ') -- For SSN#, Padding with Spaces.
1999 ||RPAD(NVL(student_dtl_rec.p_permt_addr1,' '),30,' ')
2000 ||RPAD(NVL(student_dtl_rec.p_permt_addr2,' '),30,' ')
2001 ||RPAD(NVL(student_dtl_rec.p_permt_city,' '),24,' ')
2002 ||RPAD(' ',6,' ')
2003 ||RPAD(NVL(student_dtl_rec.p_permt_state,' '),2,' ')
2004 ||LPAD(NVL(student_dtl_rec.p_permt_zip,' '),5,'0') ||'0000' -- zip code suffix hard coded
2005 ||RPAD(NVL(lv_s_permt_phone,' '),10,' ')
2006 ||RPAD(NVL(loan_rec.lender_id,' '),6,' ')
2007 ||LPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),'0'),8,'0')
2008 ||RPAD(NVL(loan_rec.cl_loan_type,' '),2,' ')
2009 ||LPAD(TO_CHAR(NVL(loan_rec.requested_loan_amt,'0')),6,'0') --MN 7-Jan-2005
2010 ||RPAD(NVL(rec_c_sl_lor.deferment_request_code,' '),1,' ')
2011 ||LPAD(NVL(lv_borw_interest_ind,' '),1,' ')
2012 ||LPAD(NVL(lv_eft_authorization,' '),1,' ')
2013 ||LPAD(NVL(loan_rec.s_signature_code,' '),1,' ')
2014 ||LPAD(NVL(TO_CHAR(loan_rec.p_signature_date,'YYYYMMDD'),'0'),8,'0')
2015 ||RPAD(NVL(loan_rec.external_loan_id_txt,loan_rec.loan_number),17,' ')
2016 ||LPAD(NVL(TO_CHAR(loan_rec.cl_seq_number),'0'),2,'0')
2017 ||lv_indi -- Bug 2814813, this field is Y if no of disb > 4 or RELEASE-4
2018 ||RPAD(' ',3,' ')
2019 ||RPAD('0',6,'0')
2020 ||RPAD(' ',3,' ')
2021 ||RPAD(' ',9,' ')
2022 ||RPAD(' ',10,' ') -- Field 33 - Borrower Alien reg number - FA 161 - Bug # 5006583 - Not required for Stafford
2023 ||RPAD(' ',35,' ') -- last name removed
2024 ||RPAD(' ',12,' ') -- first name removed
2025 ||RPAD(' ',1,' ') -- middle name removed
2026 ||LPAD('0',9,'0') -- SSN made zero.
2027 ||LPAD('0',8,'0') -- DOB made zero
2028 ||RPAD(' ',1,' ') -- Removed Citizenship status
2029 ||RPAD(' ',1,' ') -- Removed Default Status Code
2030 ||RPAD(' ',1,' ') -- Removed Student Sign Code
2031 ||RPAD(' ',20,' ')
2032 ||LPAD(NVL(p_school_id,' '),8,'0')
2033 ||RPAD(' ',2,' ')
2034 ||LPAD(NVL(TO_CHAR(loan_rec.loan_per_begin_date,'YYYYMMDD'),'0'),8,'0')
2035 ||LPAD(NVL(TO_CHAR(loan_rec.loan_per_end_date,'YYYYMMDD'),'0'),8,'0')
2036 ||RPAD(NVL(NVL(loan_rec.override_grade_level_code,loan_rec.grade_level_code),' '),1,' ')
2037 ||RPAD(NVL(lv_borr_sign_ind,' '),1,' ')
2038 ||RPAD(NVL(loan_rec.enrollment_code,' '),1,' ')
2039 ||LPAD(NVL(TO_CHAR(loan_rec.anticip_compl_date,'YYYYMMDD'),'0'),8,'0')
2040 ||LPAD(NVL(l_coa,0),5,'0')
2041 ||LPAD(NVL(l_efc,0),5,'0')
2042 ||LPAD(NVL(l_est_fin,0),5,'0');
2043
2044
2045 -- Fed Fund Code has been hard coded here as we need to implement the lowest level
2046 -- check here.
2047 -- common for RELEASE- 4 AND RELEASE-5. No Change for stafford loans
2048 IF loan_rec.fed_fund_code = 'FLS' THEN
2049 lv_first_part_trans_rec := lv_first_part_trans_rec
2050 ||LPAD(NVL(loan_rec.loan_amt_accepted,0),5,'0')
2051 ||LPAD('0',5,'0')
2052 ||LPAD('0',5,'0');
2053
2054 ELSIF loan_rec.fed_fund_code = 'FLU' THEN
2055 -- common for RELEASE- 4 AND RELEASE-5. No Change for stafford loans
2056 lv_first_part_trans_rec := lv_first_part_trans_rec
2057 ||LPAD('0',5,'0')
2058 ||LPAD(NVL(loan_rec.loan_amt_accepted,0),5,'0')
2059 ||LPAD('0',5,'0');
2060
2061 END IF;
2062
2063
2064 -- Second Part of the Transaction Record has Concatenated Disbursement dates
2065 -- common for RELEASE- 4 AND RELEASE-5. No Change for stafford loans
2066 lv_third_part_trans_rec:= LPAD(NVL(TO_CHAR(loan_rec.sch_cert_date,'YYYYMMDD'),'0'),8,'0')
2067 ||RPAD(' ',16,' ')
2068 ||LPAD('0',9,'0')
2069 ||' '
2070 ||RPAD(NVL(loan_rec.esign_src_typ_cd,' '),9,' ') -- Field 67 - FA 161 - E-sgignature Source type code
2071 ||RPAD(NVL(loan_rec.lender_id,' '),6,' ')
2072 ||RPAD('0',5,'0')
2073 ||RPAD('0',5,'0')
2074 ||RPAD('0',5,'0')
2075 ||RPAD('0',5,'0')
2076 ||RPAD(' ',9,' ') --Duns Lender ID has been replaced by ' ' Bug 2400487
2077 ||RPAD(' ',6,' ')
2078 ||RPAD(NVL(loan_rec.guarantor_id,' '),3,' ')
2079 ||NVL(rec_c_sl_lor.loan_app_form_code,' ') -- field 76 - fed form appl code
2080 ||RPAD(' ',9,' ') -- Duns Guarant ID has been replaced by ' ' Bug 2400487
2081 ||RPAD(' ',3,' ')
2082 ||LPAD('0',8,'0')
2083 ||RPAD(NVL(lv_s_license_state,' '),2,' ')
2084 ||RPAD(NVL(lv_s_license_number,' '),20,' ')
2085 ||'N'
2086 ||RPAD(NVL(rec_c_sl_lor.school_use_txt,' '),23,' ');
2087
2088 -- Fourth Part of the Transaction Record has Concatenated Disbursement Release Indicators
2089
2090 IF lv_cl_version = 'RELEASE-5' THEN
2091
2092 lv_fifth_part_trans_rec := RPAD(' ',14,' ')
2093 ||RPAD(NVL(loan_rec.req_serial_loan_code,' '),1,' ')
2094 ||rec_c_sl_lor.borr_credit_auth_code -- this should be space if field 76 is 'M'
2095 ||RPAD(NVL(loan_rec.lend_non_ed_brc_id,' '),4,' ')
2096 ||RPAD(' ',20,' ')
2097 ||RPAD(NVL(lv_stud_sign_ind,' '),1,' ')
2098 ||RPAD(NVL(loan_rec.prc_type_code,' '),2,' ')
2099 ||RPAD(NVL(rec_c_sl_lor.guarantor_use_txt,' '),23,' ')
2100 ||RPAD(NVL(loan_rec.pnote_delivery_code,'P'),1,' ')
2101 ||' '
2102 ||LPAD('0',7,'0');
2103 ELSIF lv_cl_version = 'RELEASE-4' THEN
2104 lv_fifth_part_trans_rec := RPAD(' ',14,' ')
2105 ||RPAD(NVL(loan_rec.req_serial_loan_code,' '),1,' ')
2106 ||' ' -- this should be space as field 76 is 'M'
2107 ||RPAD(NVL(loan_rec.lend_non_ed_brc_id,' '),4,' ')
2108 ||RPAD(' ',20,' ')
2109 ||' '
2110 ||RPAD(NVL(loan_rec.prc_type_code,' '),2,' ')
2111 ||RPAD(NVL(rec_c_sl_lor.guarantor_use_txt,' '),23,' ')
2112 ||RPAD(NVL(loan_rec.pnote_delivery_code,'P'),1,' ')
2113 ||' '
2114 ||LPAD('0',7,'0');
2115 END IF;
2116 -- Sixth Part of the Transaction Record has Concatenated Disbursement Amounts
2117 IF lv_cl_version = 'RELEASE-5' THEN
2118 lv_seventh_part_trans_rec := LPAD(NVL(TO_CHAR(NULL,'YYYYMMDD'),'0'),8,'0')
2119 ||RPAD(' ',92,' ')
2120 ||RPAD('0',9,'0')
2121 ||RPAD(' ',10,' ')
2122 ||RPAD(NVL(lv_s_foreign_postal_code,' '),14,' ')
2123 ||RPAD(NVL(sch_non_ed_branch,' '),4,' ')
2124 ||RPAD(' ',123,' ')
2125 ||'*';
2126 ELSIF lv_cl_version = 'RELEASE-4' THEN
2127 lv_seventh_part_trans_rec := LPAD(NVL(TO_CHAR(NULL,'YYYYMMDD'),'0'),8,'0')
2128 ||RPAD(' ',92,' ')
2129 ||RPAD('0',9,'0')
2130 ||RPAD(' ',71,' ')
2131 ||'*';
2132 END IF;
2133
2134 --Since Alternate Loan Origination is Similar to PLUS Loan Origination only the
2135 --feilds that could be different are checked
2136
2137 ELSIF p_loan_catg IN ('CL_PLUS','CL_ALT','CL_GPLUSFL') THEN
2138 -- Checking if Parent Status is Defaulted
2139 IF loan_rec.p_default_status IN ('N','Z') THEN
2140 loan_rec.p_default_status:='N';
2141 END IF;
2142
2143 -- Checking if Student Status is Defaulted
2144 IF loan_rec.s_default_status IN ('N','Z') THEN
2145 loan_rec.s_default_status:='N';
2146 END IF;
2147
2148 --Based on Federal fund Code Need to Send the information
2149 -- as per the Send File
2150
2151 --Check if its FLP
2152 IF igf_sl_gen.chk_cl_plus(loan_rec.fed_fund_code)='TRUE' OR igf_sl_gen.chk_cl_gplus(loan_rec.fed_fund_code)='TRUE' THEN
2153
2154 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
2155 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Current loan is a plus loan');
2156 END IF;
2157
2158 l_phone := lv_p_permt_phone;
2159 l_plus_cert_amt := loan_rec.loan_amt_accepted;
2160 l_alt_cert_amt := NULL;
2161 l_tot_alt_debt := NULL;
2162 l_borw_ind_code := ' ';
2163 lv_borw_interest_ind := NULL;
2164
2165 ELSIF igf_sl_gen.chk_cl_alt(loan_rec.fed_fund_code)='TRUE' THEN
2166
2167 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
2168 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Current loan is a alternate loan');
2169 END IF;
2170
2171 l_plus_cert_amt := NULL;
2172 l_alt_cert_amt := loan_rec.loan_amt_accepted; -- should be sum of all disb for this loan
2173 l_tot_alt_debt := NULL;
2174
2175 --Need to check if Alt Loan is Borrowed by Student or Parent
2176 IF loan_rec.student_id = NVL(loan_rec.p_person_id,'0') THEN
2177 --Borrrower is Student
2178 parent_dtl_rec.p_citizenship_status :=lv_s_citizenship_status;
2179 parent_dtl_rec.p_state_of_legal_res :=lv_s_legal_res_state;
2180 parent_dtl_rec.p_legal_res_date :=lv_s_legal_res_date;
2181 loan_rec.p_default_status :=loan_rec.s_default_status;
2182 l_borw_ind_code :='Y';
2183 parent_dtl_rec.p_license_num :=lv_s_license_number;
2184 parent_dtl_rec.p_license_state :=lv_s_license_state;
2185 l_phone :=lv_s_permt_phone;
2186 ELSIF loan_rec.student_id <> NVL(loan_rec.p_person_id,'0') THEN
2187 --Borrower is not Student then send the values available and fetched as it is.
2188 l_borw_ind_code :='N'; --- Alter Borrw Indicator Code N.
2189 l_phone :=lv_p_permt_phone;
2190 END IF;
2191
2192 --Fetch the Total ALternate Debt for this Person Number minus the Current
2193 --Award Year
2194 OPEN cur_get_alt_debt;
2195 FETCH cur_get_alt_debt INTO l_tot_alt_debt;
2196 CLOSE cur_get_alt_debt;
2197
2198 END IF; -- Check for ALT or FLP
2199
2200 --
2201 -- If the Alternative Bor Indicator Code is N then we need to
2202 -- send the PLUS/ALT Student SSN, Otherwise send 0
2203 --
2204
2205 IF l_borw_ind_code = 'N' OR -- 'N' for Alt Loans
2206 l_borw_ind_code = ' ' THEN -- ' ' for FLP Loans
2207 l_s_ssn :=LPAD(NVL(student_dtl_rec.p_ssn,' '),9,' ');
2208 ELSE
2209 l_s_ssn :=LPAD('0',9,'0');
2210 END IF;
2211
2212 -- common for RELEASE- 4 AND RELEASE-5. No Change for stafford loans
2213 parent_dtl_rec.p_permt_zip := TRANSLATE (UPPER(LTRIM(RTRIM(parent_dtl_rec.p_permt_zip))),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*_+=-,./?><():; ','1234567890');
2214
2215 lv_first_part_trans_rec:='@1'
2216 ||RPAD(NVL(loan_rec.rec_type_ind,' '),1,' ')--field 2
2217 ||RPAD(NVL(parent_dtl_rec.p_last_name,' '),35,' ')--field 3
2218 ||RPAD(NVL(parent_dtl_rec.p_first_name,' '),12,' ') -- field 4
2219 ||RPAD(NVL(parent_dtl_rec.p_middle_name,' '),1,' ') --field 5
2220 ||RPAD(NVL(parent_dtl_rec.p_ssn,' '),9,' ')--field 6
2221 -- For SSN#, padding with Spaces.
2222 ||RPAD(NVL(parent_dtl_rec.p_permt_addr1,' '),30,' ')--field 7
2223 ||RPAD(NVL(parent_dtl_rec.p_permt_addr2,' '),30,' ')--field 8
2224 ||RPAD(NVL(parent_dtl_rec.p_permt_city,' '),24,' ')--field 9
2225 ||RPAD(' ',6,' ')--filler field 10
2226 ||RPAD(NVL(parent_dtl_rec.p_permt_state,' '),2,' ')--field 11
2227 ||LPAD(NVL(parent_dtl_rec.p_permt_zip,' '),5,'0') ||'0000' -- zip code suffix hard coded
2228 ||RPAD(NVL(l_phone,' '),10,' ')--field 14
2229 ||RPAD(NVL(loan_rec.lender_id,' '),6,' ')--field 15
2230 ||LPAD(NVL(TO_CHAR(parent_dtl_rec.p_date_of_birth,'YYYYMMDD'),'0'),8,'0')--field 16
2231 ||RPAD(NVL(loan_rec.cl_loan_type,' '),2,' ') -- field 17
2232 ||LPAD(TO_CHAR(NVL(loan_rec.requested_loan_amt,0)),6,'0')--field 18 MN 7-Jan-2005
2233 ||RPAD(NVL(rec_c_sl_lor.deferment_request_code,' '),1,' ')
2234 ||RPAD(NVL(lv_borw_interest_ind,' '),1,' ')--field 20
2235 ||RPAD(NVL(lv_eft_authorization,' '),1,' ')--field 21
2236 ||RPAD(NVL(loan_rec.p_signature_code,' '),1,' ')--field 22
2237 ||LPAD(NVL(TO_CHAR(loan_rec.p_signature_date,'YYYYMMDD'),'0'),8,'0')--field 23
2238 ||RPAD(NVL(loan_rec.external_loan_id_txt,loan_rec.loan_number),17,' ')
2239 ||LPAD('0',2,'0') --'00'--field 25 CommonLine Loan Sequence Number
2240 ||lv_indi -- this field is Y if no of disb > 4 or RELEASE-4
2241 ||RPAD(NVL(parent_dtl_rec.p_citizenship_status,' '),1,' ')--field 27
2242 ||RPAD(NVL(parent_dtl_rec.p_state_of_legal_res,' '),2,' ')--field 28
2243 ||LPAD(NVL(TO_CHAR(parent_dtl_rec.p_legal_res_date,'YYYYMM'),'0'),6,'0')--field 29
2244 ||RPAD(NVL(loan_rec.p_default_status,' '),1,' ')--field 30
2245 ||RPAD(NVL(loan_rec.borw_outstd_loan_code,' '),1,' ')--field 31
2246 ||l_borw_ind_code--field 32
2247 ||RPAD(' ',9,' ')--filler field 33
2248 -- DUNS Borrow Lender ID has been replaced by ' ' Bug 2400487
2249 ||RPAD(NVL(loan_rec.b_alien_reg_num_txt,' '),10,' ')-- FA 161 - Bug # 5006583 - Filler replaced by borw alien reg num
2250 ||RPAD(NVL(student_dtl_rec.p_last_name,' '),35,' ')--field 35
2251 ||RPAD(NVL(student_dtl_rec.p_first_name,' '),12,' ')--field 36
2252 ||RPAD(NVL(student_dtl_rec.p_middle_name,' '),1,' ')--field 37
2253 ||l_s_ssn--field 38
2254 ||LPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),'0'),8,'0')--field 39
2255 ||LPAD(NVL(lv_s_citizenship_status,' '),1,' ')--field 40
2256 ||LPAD(NVL(loan_rec.s_default_status,' '),1,' ')--field 41
2257 ||LPAD(NVL(loan_rec.s_signature_code,' '),1,' ')--field 42
2258 ||LPAD(' ',20,' ')--filler field 43
2259 ||LPAD(NVL(p_school_id,' '),8,'0')--field 44
2260 ||' '--filler field 45
2261 ||LPAD(NVL(TO_CHAR(loan_rec.loan_per_begin_date,'YYYYMMDD'),'0'),8,'0')--field 46
2262 ||LPAD(NVL(TO_CHAR(loan_rec.loan_per_end_date,'YYYYMMDD'),'0'),8,'0')--field 47
2263 ||RPAD(NVL(NVL(loan_rec.override_grade_level_code,loan_rec.grade_level_code),' '),1,' ')
2264 ||RPAD(NVL(lv_borr_sign_ind,' '),1,' ')--field 49
2265 ||NVL(loan_rec.enrollment_code,'F')--field 50
2266 ||LPAD(NVL(TO_CHAR(loan_rec.anticip_compl_date,'YYYYMMDD'),'0'),8,'0')--field 51
2267 ||LPAD(NVL(l_coa,0),5,'0')--field 52
2268 ||LPAD(NVL(l_efc,0),5,'0')--field 53
2269 ||LPAD(NVL(l_est_fin,0),5,'0')--field 54
2270 ||LPAD('0',5,'0')--field 55 Subsidized Federal Stafford Certified Amount
2271 ||LPAD('0',5,'0')--field 56 Unsubsidized Federal Stafford Certified Amount
2272 ||LPAD(TO_CHAR(NVL(l_plus_cert_amt,0)),5,'0');--field 57
2273
2274 -- Second Part of the Transaction Record has Concatenated Disbursement dates
2275
2276 lv_third_part_trans_rec := LPAD(NVL(TO_CHAR(loan_rec.sch_cert_date,'YYYYMMDD'),'0'),8,'0')--field 62 School Certification Date
2277 ||LPAD(' ',16,' ')--filler field 63
2278 ||LPAD(NVL(l_alt_cert_amt,0),5,0)--field 64
2279 ||RPAD('0',4,'0')--field 65 Alternative Loan Application Version Code
2280 ||RPAD(' ',2,' ')--filler field 66
2281 ||RPAD(NVL(loan_rec.esign_src_typ_cd,' '),9,' ')--filler field 67 -- FA 161 - Bug # 5006587 - E-signature Source type code
2282 ||RPAD(NVL(loan_rec.lender_id,' '),6,' ')--field 68
2283 ||RPAD('0',5,'0')--field 69 Subsidized Federal Stafford Approved Amount
2284 ||RPAD('0',5,'0')--field 70 Unsubsidized Federal Stafford Approved Amount
2285 ||RPAD('0',5,'0')--field 71 Federal PLUS Approved Amount
2286 ||RPAD('0',5,'0')--field 72 Alternative Loan Approved Amount
2287 ||RPAD(' ',9,' ') --filler field 73 DUNS LENDER ID has been replaced by ' ' Bug 2400487
2288 ||RPAD(' ',6,' ') --filler field 74
2289 ||RPAD(NVL(loan_rec.guarantor_id,' '),3,' ') --field 75
2290 ||NVL(rec_c_sl_lor.loan_app_form_code,' ') --field 76
2291 ||RPAD(' ',9,' ') -- DUNS GUARNT ID has been replaced by ' ' Bug 2400487(field 77)
2292 ||RPAD(' ',3,' ') --Filler(field 78) and Lender blanket guarantee indicator code(field 79)
2293 ||LPAD('0',8,'0') --field 80 Lender Blanket Guarantee Approval Date
2294 ||RPAD(NVL(parent_dtl_rec.p_license_state,' '),2,' ')--field 81
2295 ||RPAD(NVL(parent_dtl_rec.p_license_num,' '),20,' ') --field 82
2296 ||'N' --field 83 -borrower references code
2297 ||RPAD(NVL(rec_c_sl_lor.school_use_txt,' '),23,' ');--field 84 (School Use only)
2298
2299
2300 -- Fourth Part of the Transaction Record has Concatenated Disbursement Indicators
2301
2302
2303
2304
2305 IF lv_cl_version = 'RELEASE-5' THEN
2306
2307 lv_fifth_part_trans_rec := RPAD(' ',14,' ') --field 89 (foreign postal code)
2308 ||RPAD(NVL(loan_rec.req_serial_loan_code,' '),1,' ') -- bvisvana - FA 161 - Bug # 5006583 - It is a req field
2309 --Req Serial Loan Code should be Blank for FLP/ALT -field 90
2310 ||RPAD(NVL(rec_c_sl_lor.borr_credit_auth_code,' '),1,' ') --field 91
2311 ||RPAD(NVL(loan_rec.lend_non_ed_brc_id,' '),4,' ') --field 92
2312 ||RPAD(' ',20,' ') --field 93 (lender use only)
2313 ||RPAD(NVL(lv_stud_sign_ind,' '),1,' ')
2314 ||RPAD(NVL(loan_rec.prc_type_code,' '),2,' ')
2315 ||RPAD(' ',23,' ')--field 96 guarantor use only
2316 ||NVL(loan_rec.pnote_delivery_code,'P')
2317 ||RPAD(NVL(loan_rec.alt_loan_code,' '),3,' ')--field 98 Alternative Loan Program Type Code FACR116
2318 ||LPAD(NVL(l_tot_alt_debt,0),7,0);--field 99
2319
2320
2321 ELSIF lv_cl_version = 'RELEASE-4' THEN
2322
2323 lv_fifth_part_trans_rec := RPAD(' ',14,' ')
2324 ||RPAD(NVL(loan_rec.req_serial_loan_code,' '),1,' ') -- bvisvana - FA 161 - Bug # 5006583 - It is a req field
2325 -- Req Serial Loan Code should be Blank for FLP/ALT -field 90
2326 -- Bug fix - 4117260, - removed the condition introduced by 4103342
2327 -- and reverted back to the original condition- field 88 - Req Serial Loan Code should be Blank for FLP/ALT
2328 ||' ' -- this should be space for 'RELEASE-4'
2329 ||RPAD(NVL(loan_rec.lend_non_ed_brc_id,' '),4,' ')
2330 ||RPAD(' ',20,' ')
2331 ||' '
2332 ||RPAD(NVL(loan_rec.prc_type_code,' '),2,' ')
2333 ||RPAD(NVL(rec_c_sl_lor.guarantor_use_txt,' '),23,' ')
2334 ||RPAD(NVL(loan_rec.pnote_delivery_code,'P'),1,' ')
2335 ||RPAD(NVL(loan_rec.alt_loan_code,' '),3,' ')--field 98 Alternative Loan Program Type Code FACR116
2336 ||LPAD(NVL(l_tot_alt_debt,0),7,0);--field 99
2337 END IF;
2338
2339
2340 -- Sixth part of the Transaction Record has concatenated Disbursement Amounts
2341
2342 IF lv_cl_version = 'RELEASE-5' THEN
2343 lv_seventh_part_trans_rec := LPAD(NVL(TO_CHAR(NULL,'YYYYMMDD'),'0'),8,'0')
2344 ||RPAD(' ',92,' ')
2345 ||RPAD('0',9,'0')
2346 ||RPAD(' ',10,' ')
2347 ||RPAD(NVL(lv_s_foreign_postal_code,' '),14,' ')
2348 ||RPAD(NVL(sch_non_ed_branch,' '),4,' ')
2349 ||RPAD(' ',123,' ')
2350 ||'*';
2351 ELSIF lv_cl_version = 'RELEASE-4' THEN
2352 lv_seventh_part_trans_rec := LPAD(NVL(TO_CHAR(NULL,'YYYYMMDD'),'0'),8,'0')
2353 ||RPAD(' ',92,' ')
2354 ||RPAD('0',9,'0')
2355 ||RPAD(' ',71,' ')
2356 ||'*';
2357 END IF;
2358
2359
2360 END IF; --End of Check for Loan Category
2361
2362 -- Form the Disbursement Data to be sent.
2363 lv_counter := 0;
2364 lv_1_final_disb_date := NULL;
2365 lv_1_final_gross_amt := NULL;
2366 lv_1_final_hold_rel_ind := NULL;
2367 lv_8_final_disb_date := NULL;
2368 lv_8_final_gross_amt := NULL;
2369 lv_8_final_hold_rel_ind := NULL;
2370 lv_l_direct_to_borr_flag :=NULL;
2371 lv_8_direct_to_borr_flag :=NULL;
2372
2373 -- Open Cursor for fetching the Disbursement Dates ,Amounts and Rel Hold Indicators
2374 -- bvisvana - Bug # 5078644 - lv_8_final_gross_amt and lv_1_final_gross_amt are applicable / recommended only for Alternative loans
2375 FOR drec IN cur_disb_details LOOP
2376
2377 lv_counter := lv_counter + 1;
2378
2379 --Disbursement Hold Indicators can take values only as H - Hold / R-Rlease Hold
2380 --NVL(drec.hold_rel_ind,'N') should be replaced by NVL(drec.hold_rel_ind,'R')
2381 --Bug 2477912
2382
2383 IF lv_counter > 4 THEN
2384 lv_8_final_disb_date := lv_8_final_disb_date || LPAD(NVL(TO_CHAR(drec.disb_date, 'YYYYMMDD'),'0'),8,'0');
2385 IF p_loan_catg = 'CL_ALT' THEN -- Bug 5078644
2386 lv_8_final_gross_amt := lv_8_final_gross_amt || LPAD(TO_CHAR(NVL(drec.disb_accepted_amt,'0')),5,'0');
2387 END IF;
2388 lv_8_final_hold_rel_ind := lv_8_final_hold_rel_ind || NVL(drec.hold_rel_ind,'R');
2389 lv_8_direct_to_borr_flag := lv_8_direct_to_borr_flag || NVL(drec.direct_to_borr_flag,' ');
2390 ELSE
2391 lv_1_final_disb_date := lv_1_final_disb_date || LPAD(NVL(TO_CHAR(drec.disb_date, 'YYYYMMDD'),'0'),8,'0');
2392 IF p_loan_catg = 'CL_ALT' THEN -- Bug 5078644
2393 lv_1_final_gross_amt := lv_1_final_gross_amt || LPAD(TO_CHAR(NVL(drec.disb_accepted_amt,'0')),5,'0');
2394 END IF;
2395 lv_1_final_hold_rel_ind := lv_1_final_hold_rel_ind || NVL(drec.hold_rel_ind,'R');
2396 lv_l_direct_to_borr_flag := lv_l_direct_to_borr_flag || NVL(drec.direct_to_borr_flag,' ');
2397 END IF;
2398
2399 END LOOP; -- End of Disbursement Cursor Loop;
2400
2401 lv_third_part_trans_rec := substr(lv_third_part_trans_rec,1,8) || RPAD(lv_l_direct_to_borr_flag,4,' ') || substr(lv_third_part_trans_rec,13);
2402
2403
2404 -- Spool @1 Record
2405 -- masehgal 2477912 Made record "upper" to resolve NCAT reported errors
2406 fnd_file.put_line(fnd_file.output, UPPER(lv_first_part_trans_rec
2407 ||RPAD(lv_1_final_disb_date,32,'0')
2408 ||lv_third_part_trans_rec
2409 ||RPAD(lv_1_final_hold_rel_ind,4,' ')--fields 85 to 88(disbursement hold/release indicator)
2410 ||lv_fifth_part_trans_rec
2411 ||RPAD(NVL(lv_1_final_gross_amt,'0'),20,'0')
2412 ||lv_seventh_part_trans_rec));
2413 -- Increment the counter for the Number of @1 records in this file
2414 lv_trans_count := lv_trans_count+1;
2415
2416 IF igf_sl_gen.chk_cl_alt(loan_rec.fed_fund_code) = 'TRUE' THEN
2417 alter_rec := NULL; -- mnade 7-Feb-2005 Bug 4133414
2418 OPEN cur_get_alternate;
2419 FETCH cur_get_alternate INTO alter_rec;
2420 CLOSE cur_get_alternate;
2421
2422 -- Need to spool the @4 Record after the @1 Record if it exists for this loan id
2423 -- masehgal 2477912 Made record "upper" to resolve NCAT reported errors
2424
2425 --veramach Changed ' ' to '0' for fed_stafford_loan_debt,fed_sls_debt,heal_debt,perkins_debt,other_debt,borw_gross_annual_sal,
2426 -- borw_other_income,stud_mth_housing_pymt,stud_mth_crdtcard_pymt,stud_mth_auto_pymt,stud_mth_ed_loan_pymt,stud_mth_other_pymt
2427
2428 -- bvisvana - Validation for Cosigner Data
2429 cosigner_name_validation (fName => alter_rec.cs1_fname,
2430 lName => alter_rec.cs1_lname
2431 );
2432 cosigner_name_validation (fName => alter_rec.cs2_fname,
2433 lName => alter_rec.cs2_lname
2434 );
2435 IF lv_cl_version = 'RELEASE-5' THEN
2436 l_v_owner_code := 'NCLP05';
2437
2438 l_at4Record := '@4' ||l_v_owner_code
2439 ||LPAD(NVL(TO_CHAR(alter_rec.fed_stafford_loan_debt),'0'),5,'0') -- Federal Stafford Loan Debt
2440 ||LPAD(NVL(TO_CHAR(alter_rec.fed_sls_debt),'0'),5,'0') -- Federal SLS Debt
2441 ||LPAD(NVL(TO_CHAR(alter_rec.heal_debt),'0'),6,'0') -- HEAL Debt
2442 ||LPAD(NVL(TO_CHAR(alter_rec.perkins_debt),'0'),5,'0') -- Perkins Debt
2443 ||LPAD(NVL(TO_CHAR(alter_rec.other_debt),'0'),6,'0') -- Other Debt
2444 ||LPAD(NVL(TO_CHAR(alter_rec.other_loan_amt),'0'),7,'0') -- Other Loans this Period
2445 ||NVL(alter_rec.crdt_undr_difft_name,'N') -- Credit Under Different Name Code
2446 ||RPAD(alter_rec.cs1_lname,35,' ') -- Cosigner1 Lname
2447 ||RPAD(alter_rec.cs1_fname,12,' ') -- Cosigner1 Fname
2448 ||NVL(alter_rec.cs1_mi_txt,' ') --Cosigner1 Mname
2449 ||LPAD(NVL(alter_rec.cs1_ssn_txt,'0'),9,'0') -- Cosigner 1 SSN
2450 ||NVL(alter_rec.cs1_citizenship_status,' ') -- Cosigner 1 U.S. Citizenship Status Code
2451 ||RPAD(NVL(alter_rec.cs1_address_line_1_txt,' '),30,' ') -- Cosigner 1 Address (line 1)
2452 ||RPAD(NVL(alter_rec.cs1_address_line_2_txt,' '),30,' ') --Cosigner 1 Address (line 2)
2453 ||RPAD(NVL(alter_rec.cs1_city_txt,' '),24,' ') -- Cosigner 1 City
2454 ||LPAD(' ',6,' ') -- Filler
2455 ||LPAD(NVL(alter_rec.cs1_state_txt,' '),2,' ') -- Cosigner 1 State
2456 ||LPAD(NVL(alter_rec.cs1_zip_txt,'0'),5,'0') -- Cosigner 1 Zip Code
2457 ||LPAD(NVL(alter_rec.cs1_zip_suffix_txt,'0'),4,'0'); -- Cosigner 1 Zip Code Suffix
2458
2459 IF (alter_rec.cs1_telephone_number_txt IS NOT NULL) THEN
2460 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_telephone_number_txt,' '),10,'0'); -- Cosigner 1 Telephone Number
2461 ELSIF (alter_rec.cs1_telephone_number_txt IS NULL) THEN
2462 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_telephone_number_txt,' '),10,' ');
2463 END IF;
2464
2465 --Append cs1_signature_code_txt only if Y, if NULL or 'N' then append ' '
2466 IF (alter_rec.cs1_signature_code_txt IS NULL OR alter_rec.cs1_signature_code_txt = 'N') THEN
2467 l_at4Record := l_at4Record||' '; -- Cosigner 1 Signature Code
2468 ELSIF (alter_rec.cs1_signature_code_txt = 'Y') THEN
2469 l_at4Record := l_at4Record||alter_rec.cs1_signature_code_txt;
2470 END IF;
2471
2472 l_at4Record := l_at4Record
2473 ||RPAD(alter_rec.cs2_lname,35,' ') --Cosigner2 Lname
2474 ||RPAD(alter_rec.cs2_fname,12,' ') --Cosigner2 Fname
2475 ||NVL(alter_rec.cs2_mi_txt,' ') --Cosigner2 Mname
2476 ||LPAD(NVL(alter_rec.cs2_ssn_txt,'0'),9,'0') --Cosigner2 SSN sbould be 9 '0'
2477 ||NVL(alter_rec.cs2_citizenship_status,' ') -- Cosigner 2 U.S. Citizenship Status Code
2478 ||RPAD(NVL(alter_rec.cs2_address_line_1_txt,' '),30,' ') -- Cosigner 2 Address (line 1)
2479 ||RPAD(NVL(alter_rec.cs2_address_line_2_txt,' '),30,' ') -- Cosigner 2 Address (line 2)
2480 ||RPAD(NVL(alter_rec.cs2_city_txt,' '),24,' ') -- Cosigner 2 City
2481 ||LPAD(' ',6,' ') -- Filler
2482 ||LPAD(NVL(alter_rec.cs2_state_txt,' '),2,' ') -- Cosigner 2 State
2483 ||LPAD(NVL(alter_rec.cs2_zip_txt,'0'),5,'0') -- Cosigner 2 Zip Code
2484 ||LPAD(NVL(alter_rec.cs2_zip_suffix_txt,'0'),4,'0'); -- Cosigner 2 Zip Code Suffix
2485
2486 IF (alter_rec.cs2_telephone_number_txt IS NOT NULL) THEN
2487 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_telephone_number_txt,' '),10,'0'); -- Cosigner 2 Telephone Number
2488 ELSIF (alter_rec.cs2_telephone_number_txt IS NULL) THEN
2489 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_telephone_number_txt,' '),10,' ');
2490 END IF;
2491
2492 --Append cs2_signature_code_txt only if Y, if NULL or 'N' then append ' '
2493 IF (alter_rec.cs2_signature_code_txt IS NULL OR alter_rec.cs2_signature_code_txt = 'N') THEN
2494 l_at4Record := l_at4Record||' '; -- Cosigner 2 Signature Code
2495 ELSIF (alter_rec.cs2_signature_code_txt = 'Y') THEN
2496 l_at4Record := l_at4Record||alter_rec.cs2_signature_code_txt;
2497 END IF;
2498
2499 l_at4Record := l_at4Record
2500 ||LPAD(NVL(TO_CHAR(alter_rec.borw_gross_annual_sal),'0'),7,'0') -- Borrower Gross Annual Salary
2501 ||LPAD(NVL(TO_CHAR(alter_rec.borw_other_income),'0'),7,'0') -- Borrower Other Income
2502 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_gross_annual_sal_num),'0'),7,'0') -- Cosigner 1 Gross Annual Salary
2503 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_other_income_amt),'0'),7,'0') -- Cosigner 1 Other Income
2504 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_gross_annual_sal_num),'0'),7,'0') -- Cosigner 2 Gross Annual Salary
2505 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_other_income_amt),'0'),7,'0') -- Cosigner 2 Other Income
2506 ||LPAD(NVL(alter_rec.cs1_frgn_postal_code_txt,' '),14,' ') -- Cosigner 1 Foreign Postal Code
2507 ||LPAD(NVL(alter_rec.cs2_frgn_postal_code_txt,' '),14,' ') -- Cosigner 2 Foreign Postal Code
2508 ||LPAD(NVL(alter_rec.student_major,' '),15,' ') -- Student Major
2509 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_birth_date,'YYYYMMDD'),'0') ,8,'0') --Cosign 1 DOB
2510 ||LPAD(NVL(alter_rec.cs1_drv_license_state_txt,' '),2,' ') -- Cosigner 1 Driver's License State
2511 ||LPAD(NVL(alter_rec.cs1_drv_license_num_txt,' '),20,' ') -- Cosigner 1 Driver's License Number
2512 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_birth_date,'YYYYMMDD'),'0') ,8,'0') --Cosign 2 DOB
2513 ||LPAD(NVL(alter_rec.cs2_drv_license_state_txt,' '),2,' ') -- Cosigner 2 Driver's License State
2514 ||LPAD(NVL(alter_rec.cs2_drv_license_num_txt,' '),20,' ') -- Cosigner 2 Driver's License Number
2515 ||LPAD(' ',20,' ') -- Filler
2516 ||LPAD(' ',10,' ') -- Student Phone Number
2517 ||LPAD(NVL(alter_rec.cs1_rel_to_student_flag,' '),1,' ') -- Cosigner 1 Relationship to Student
2518 ||LPAD(' ',3,' ') -- Filler
2519 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_years_at_address_txt),'0'),2,'0') -- Cosigner 1 Years at Address
2520 ||LPAD(NVL(alter_rec.cs2_rel_to_student_flag,' '),1,' ') -- Cosigner 2 Relationship to Student
2521 ||LPAD(' ',3,' ') -- Filler
2522 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_years_at_address_txt),'0'),2,'0') -- Cosigner 2 Years at Address
2523 ||NVL(alter_rec.int_rate_opt,' ') -- Interest Rate Option
2524 ||NVL(alter_rec.repayment_opt_code,' ') -- Repayment Option Code
2525 ||LPAD(NVL(alter_rec.cs1_frgn_tel_num_prefix_txt,' '),10,' ') -- Cosigner 1 Foreign Telephone Number Prefix
2526 ||LPAD(NVL(alter_rec.cs2_frgn_tel_num_prefix_txt,' '),10,' '); -- Cosigner 2 Foreign Telephone Number Prefix
2527
2528 IF (alter_rec.stud_mth_housing_pymt IS NOT NULL) THEN
2529 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_housing_pymt),'0'),5,'0'); -- Student Monthly Housing Payment
2530 ELSIF (alter_rec.stud_mth_housing_pymt IS NULL) THEN
2531 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_housing_pymt),' '),5,' '); -- Student Monthly Housing Payment
2532 END IF;
2533
2534 IF (alter_rec.stud_mth_crdtcard_pymt IS NOT NULL) THEN
2535 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_crdtcard_pymt),'0'),5,'0'); -- Student Monthly Credit Card Payment
2536 ELSIF (alter_rec.stud_mth_crdtcard_pymt IS NULL) THEN
2537 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_crdtcard_pymt),' '),5,' '); -- Student Monthly Credit Card Payment
2538 END IF;
2539
2540 IF (alter_rec.stud_mth_auto_pymt IS NOT NULL) THEN
2541 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_auto_pymt),'0'),5,'0'); -- Student Monthly Auto Payment
2542 ELSIF (alter_rec.stud_mth_auto_pymt IS NULL) THEN
2543 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_auto_pymt),' '),5,' '); -- Student Monthly Auto Payment
2544 END IF;
2545
2546 IF (alter_rec.stud_mth_ed_loan_pymt IS NOT NULL) THEN
2547 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_ed_loan_pymt),'0'),5,'0'); -- Student Monthly Educational Loan Payment
2548 ELSIF (alter_rec.stud_mth_ed_loan_pymt IS NULL) THEN
2549 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_ed_loan_pymt),' '),5,' '); -- Student Monthly Educational Loan Payment
2550 END IF;
2551
2552 IF (alter_rec.stud_mth_other_pymt IS NOT NULL) THEN
2553 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_other_pymt),'0'),5,'0'); -- Student Monthly Other Payment
2554 ELSIF (alter_rec.stud_mth_ed_loan_pymt IS NULL) THEN
2555 l_at4Record := l_at4Record||LPAD(NVL(TO_CHAR(alter_rec.stud_mth_other_pymt),' '),5,' '); -- Student Monthly Other Payment
2556 END IF;
2557
2558 IF (alter_rec.cs1_mthl_housing_pay_txt IS NOT NULL) THEN
2559 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_housing_pay_txt,'0'),5,'0'); -- Cosigner 1 Monthly Housing Payment
2560 ELSIF (alter_rec.cs1_mthl_housing_pay_txt IS NULL) THEN
2561 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_housing_pay_txt,' '),5,' '); -- Cosigner 1 Monthly Housing Payment
2562 END IF;
2563
2564 IF (alter_rec.cs1_mthl_cc_pay_txt IS NOT NULL) THEN
2565 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_cc_pay_txt,'0'),5,'0'); -- Cosigner 1 Monthly Credit Card Payment
2566 ELSIF (alter_rec.cs1_mthl_cc_pay_txt IS NULL) THEN
2567 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_cc_pay_txt,' '),5,' '); -- Cosigner 1 Monthly Credit Card Payment
2568 END IF;
2569
2570 IF (alter_rec.cs1_mthl_auto_pay_txt IS NOT NULL) THEN
2571 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_auto_pay_txt,'0'),5,'0'); -- Cosigner 1 Monthly Auto Payment
2572 ELSIF (alter_rec.cs1_mthl_auto_pay_txt IS NULL) THEN
2573 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_auto_pay_txt,' '),5,' '); -- Cosigner 1 Monthly Auto Payment
2574 END IF;
2575
2576 IF (alter_rec.cs1_mthl_edu_loan_pay_txt IS NOT NULL) THEN
2577 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_edu_loan_pay_txt,'0'),5,'0'); -- Cosigner 1 Monthly Educational Loan Payment
2578 ELSIF (alter_rec.cs1_mthl_edu_loan_pay_txt IS NULL) THEN
2579 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_edu_loan_pay_txt,' '),5,' '); -- Cosigner 1 Monthly Educational Loan Payment
2580 END IF;
2581
2582 IF (alter_rec.cs1_mthl_other_pay_txt IS NOT NULL) THEN
2583 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_other_pay_txt,'0'),5,'0'); -- Cosigner 1 Monthly Other Payment
2584 ELSIF (alter_rec.cs1_mthl_other_pay_txt IS NULL) THEN
2585 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_mthl_other_pay_txt,' '),5,' '); -- Cosigner 1 Monthly Other Payment
2586 END IF;
2587
2588 IF (alter_rec.cs2_mthl_housing_pay_txt IS NOT NULL) THEN
2589 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_housing_pay_txt,'0'),5,'0'); -- Cosigner 2 Monthly Housing Payment
2590 ELSIF (alter_rec.cs2_mthl_housing_pay_txt IS NULL) THEN
2591 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_housing_pay_txt,' '),5,' '); -- Cosigner 2 Monthly Housing Payment
2592 END IF;
2593
2594 IF (alter_rec.cs2_mthl_cc_pay_txt IS NOT NULL) THEN
2595 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_cc_pay_txt,'0'),5,'0'); -- Cosigner 2 Monthly Credit Card Payment
2596 ELSIF (alter_rec.cs2_mthl_cc_pay_txt IS NULL) THEN
2597 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_cc_pay_txt,' '),5,' '); -- Cosigner 2 Monthly Credit Card Payment
2598 END IF;
2599
2600 IF (alter_rec.cs2_mthl_auto_pay_txt IS NOT NULL) THEN
2601 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_auto_pay_txt,'0'),5,'0'); -- Cosigner 2 Monthly Auto Payment
2602 ELSIF (alter_rec.cs2_mthl_auto_pay_txt IS NULL) THEN
2603 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_auto_pay_txt,' '),5,' '); -- Cosigner 2 Monthly Auto Payment
2604 END IF;
2605
2606 IF (alter_rec.cs2_mthl_edu_loan_pay_txt IS NOT NULL) THEN
2607 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_edu_loan_pay_txt,'0'),5,'0'); -- Cosigner 2 Monthly Educational Loan Payment
2608 ELSIF (alter_rec.cs2_mthl_edu_loan_pay_txt IS NULL) THEN
2609 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_edu_loan_pay_txt,' '),5,' '); -- Cosigner 2 Monthly Educational Loan Payment
2610 END IF;
2611
2612 IF (alter_rec.cs2_mthl_other_pay_txt IS NOT NULL) THEN
2613 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_other_pay_txt,'0'),5,'0'); -- Cosigner 2 Monthly Other Payment
2614 ELSIF (alter_rec.cs2_mthl_other_pay_txt IS NULL) THEN
2615 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_mthl_other_pay_txt,' '),5,' '); -- Cosigner 2 Monthly Other Payment
2616 END IF;
2617
2618 l_at4Record := l_at4Record||NVL(alter_rec.cs1_credit_auth_code_txt,' ') -- Cosigner 1 Credit Authorization Code
2619 ||NVL(alter_rec.cs2_credit_auth_code_txt,' '); -- Cosigner 2 Credit Authorization Code;
2620
2621 --Append cs1_signature_code_txt only if Y, if NULL or 'N' then append ' '
2622 IF (alter_rec.cs1_elect_sig_ind_code_txt IS NULL OR alter_rec.cs1_elect_sig_ind_code_txt = 'N') THEN
2623 l_at4Record := l_at4Record||' '; -- -- Cosigner 1 Electronic Signature Indicator Code
2624 ELSIF (alter_rec.cs1_elect_sig_ind_code_txt = 'Y') THEN
2625 l_at4Record := l_at4Record||alter_rec.cs1_elect_sig_ind_code_txt;
2626 END IF;
2627
2628
2629 --Append cs2_signature_code_txt only if Y, if NULL or 'N' then append ' '
2630 IF (alter_rec.cs2_elect_sig_ind_code_txt IS NULL OR alter_rec.cs2_elect_sig_ind_code_txt = 'N') THEN
2631 l_at4Record := l_at4Record||' '; -- -- Cosigner 2 Electronic Signature Indicator Code
2632 ELSIF (alter_rec.cs2_elect_sig_ind_code_txt = 'Y') THEN
2633 l_at4Record := l_at4Record||alter_rec.cs2_elect_sig_ind_code_txt;
2634 END IF;
2635
2636
2637 l_at4Record := l_at4Record ||LPAD(' ',288,' ') --Filler
2638 ||'*';
2639
2640 fnd_file.put_line(fnd_file.output,UPPER(l_at4Record));
2641
2642 ELSIF lv_cl_version = 'RELEASE-4' THEN
2643 l_v_owner_code := 'NCLP03';
2644 l_at4Record := '@4' ||l_v_owner_code
2645 ||LPAD(NVL(TO_CHAR(alter_rec.fed_stafford_loan_debt),'0'),5,'0') -- Federal Stafford Loan Debt
2646 ||LPAD(NVL(TO_CHAR(alter_rec.fed_sls_debt),'0'),5,'0') -- Federal SLS Debt
2647 ||LPAD(NVL(TO_CHAR(alter_rec.heal_debt),'0'),6,'0') -- HEAL Debt
2648 ||LPAD(NVL(TO_CHAR(alter_rec.perkins_debt),'0'),5,'0') -- Perkins Debt
2649 ||LPAD(NVL(TO_CHAR(alter_rec.other_debt),'0'),6,'0') -- Other Debt
2650 ||LPAD(NVL(TO_CHAR(alter_rec.other_loan_amt),'0'),7,'0') -- Other Loans this Period
2651 ||NVL(alter_rec.crdt_undr_difft_name,'N') -- Credit Under Different Name Code
2652 ||RPAD(alter_rec.cs1_lname,35,' ') -- Cosigner1 Lname
2653 ||RPAD(alter_rec.cs1_fname,12,' ') -- Cosigner1 Fname
2654 ||NVL(alter_rec.cs1_mi_txt,' ') --Cosigner1 Mname
2655 ||LPAD(NVL(alter_rec.cs1_ssn_txt,'0'),9,'0') -- Cosigner 1 SSN
2656 ||NVL(alter_rec.cs1_citizenship_status,' ') -- Cosigner 1 U.S. Citizenship Status Code
2657 ||RPAD(NVL(alter_rec.cs1_address_line_1_txt,' '),30,' ') -- Cosigner 1 Address (line 1)
2658 ||RPAD(NVL(alter_rec.cs1_address_line_2_txt,' '),30,' ') --Cosigner 1 Address (line 2)
2659 ||RPAD(NVL(alter_rec.cs1_city_txt,' '),24,' ') -- Cosigner 1 City
2660 ||LPAD(' ',6,' ') -- Filler
2661 ||LPAD(NVL(alter_rec.cs1_state_txt,' '),2,' ') -- Cosigner 1 State
2662 ||LPAD(NVL(alter_rec.cs1_zip_txt,'0'),5,'0') -- Cosigner 1 Zip Code
2663 ||LPAD(NVL(alter_rec.cs1_zip_suffix_txt,'0'),4,'0'); -- Cosigner 1 Zip Code Suffix
2664
2665 IF (alter_rec.cs1_telephone_number_txt IS NOT NULL) THEN
2666 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_telephone_number_txt,' '),10,'0'); -- Cosigner 1 Telephone Number
2667 ELSIF (alter_rec.cs1_telephone_number_txt IS NULL) THEN
2668 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs1_telephone_number_txt,' '),10,' ');
2669 END IF;
2670
2671
2672 --Append cs1_signature_code_txt only if Y, if NULL or 'N' then append ' '
2673 IF (alter_rec.cs1_signature_code_txt IS NULL OR alter_rec.cs1_signature_code_txt = 'N') THEN
2674 l_at4Record := l_at4Record||' '; -- Cosigner 1 Signature Code
2675 ELSIF (alter_rec.cs1_signature_code_txt = 'Y') THEN
2676 l_at4Record := l_at4Record||alter_rec.cs1_signature_code_txt;
2677 END IF;
2678
2679 l_at4Record := l_at4Record
2680 ||RPAD(alter_rec.cs2_lname,35,' ') --Cosigner2 Lname
2681 ||RPAD(alter_rec.cs2_fname,12,' ') --Cosigner2 Fname
2682 ||NVL(alter_rec.cs2_mi_txt,' ') --Cosigner2 Mname
2683 ||LPAD(NVL(alter_rec.cs2_ssn_txt,'0'),9,'0') --Cosigner2 SSN sbould be 9 '0'
2684 ||NVL(alter_rec.cs2_citizenship_status,' ') -- Cosigner 2 U.S. Citizenship Status Code
2685 ||RPAD(NVL(alter_rec.cs2_address_line_1_txt,' '),30,' ') -- Cosigner 2 Address (line 1)
2686 ||RPAD(NVL(alter_rec.cs2_address_line_2_txt,' '),30,' ') -- Cosigner 2 Address (line 2)
2687 ||RPAD(NVL(alter_rec.cs2_city_txt,' '),24,' ') -- Cosigner 2 City
2688 ||LPAD(' ',6,' ') -- Filler
2689 ||LPAD(NVL(alter_rec.cs2_state_txt,' '),2,' ') -- Cosigner 2 State
2690 ||LPAD(NVL(alter_rec.cs2_zip_txt,'0'),5,'0') -- Cosigner 2 Zip Code
2691 ||LPAD(NVL(alter_rec.cs2_zip_suffix_txt,'0'),4,'0'); -- Cosigner 2 Zip Code Suffix
2692
2693 IF (alter_rec.cs2_telephone_number_txt IS NOT NULL) THEN
2694 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_telephone_number_txt,' '),10,'0'); -- Cosigner 2 Telephone Number
2695 ELSIF (alter_rec.cs2_telephone_number_txt IS NULL) THEN
2696 l_at4Record := l_at4Record||LPAD(NVL(alter_rec.cs2_telephone_number_txt,' '),10,' ');
2697 END IF;
2698
2699
2700 --Append cs2_signature_code_txt only if Y, if NULL or 'N' then append ' '
2701 IF (alter_rec.cs2_signature_code_txt IS NULL OR alter_rec.cs2_signature_code_txt = 'N') THEN
2702 l_at4Record := l_at4Record||' '; -- Cosigner 2 Signature Code
2703 ELSIF (alter_rec.cs2_signature_code_txt = 'Y') THEN
2704 l_at4Record := l_at4Record||alter_rec.cs2_signature_code_txt;
2705 END IF;
2706
2707
2708
2709 l_at4Record := l_at4Record
2710 ||LPAD(NVL(TO_CHAR(alter_rec.borw_gross_annual_sal),'0'),7,'0') -- Borrower Gross Annual Salary
2711 ||LPAD(NVL(TO_CHAR(alter_rec.borw_other_income),'0'),7,'0') -- Borrower Other Income
2712 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_gross_annual_sal_num),'0'),7,'0') -- Cosigner 1 Gross Annual Salary
2713 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_other_income_amt),'0'),7,'0') -- Cosigner 1 Other Income
2714 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_gross_annual_sal_num),'0'),7,'0') -- Cosigner 2 Gross Annual Salary
2715 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_other_income_amt),'0'),7,'0') -- Cosigner 2 Other Income
2716 ||LPAD(NVL(alter_rec.cs1_frgn_postal_code_txt,' '),14,' ') -- Cosigner 1 Foreign Postal Code
2717 ||LPAD(NVL(alter_rec.cs2_frgn_postal_code_txt,' '),14,' ') -- Cosigner 2 Foreign Postal Code
2718 ||RPAD(NVL(alter_rec.student_major,' '),15,' ');-- Student Major
2719
2720
2721
2722 l_at4Record := l_at4Record
2723 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_birth_date,'YYYYMMDD'),'0') ,8,'0') --Cosign 1 DOB
2724 ||LPAD(NVL(alter_rec.cs1_drv_license_state_txt,' '),2,' ') -- Cosigner 1 Driver's License State
2725 ||RPAD(NVL(alter_rec.cs1_drv_license_num_txt,' '),20,' ') -- Cosigner 1 Driver's License Number
2726 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_birth_date,'YYYYMMDD'),'0') ,8,'0') --Cosign 2 DOB
2727 ||LPAD(NVL(alter_rec.cs2_drv_license_state_txt,' '),2,' ') -- Cosigner 2 Driver's License State
2728 ||RPAD(NVL(alter_rec.cs2_drv_license_num_txt,' '),20,' ') -- Cosigner 2 Driver's License Number
2729 ||LPAD(' ',20,' ') -- Filler
2730 ||LPAD(' ',10,' '); -- Student Phone Number
2731
2732 l_at4Record := l_at4Record
2733 ||LPAD(NVL(alter_rec.cs1_rel_to_student_flag,' '),1,' ') -- Cosigner 1 Relationship to Student
2734 ||LPAD(NVL(alter_rec.cs1_suffix_txt ,' '),3,' ') -- Cosigner 1 Suffix
2735 ||LPAD(NVL(TO_CHAR(alter_rec.cs1_years_at_address_txt),'0'),2,'0') -- Cosigner 1 Years at Address
2736 ||LPAD(NVL(alter_rec.cs2_rel_to_student_flag,' '),1,' ') -- Cosigner 2 Relationship to Student
2737 ||LPAD(NVL(alter_rec.cs2_suffix_txt ,' '),3,' ') -- Cosigner 2 Suffix
2738 ||LPAD(NVL(TO_CHAR(alter_rec.cs2_years_at_address_txt),'0'),2,'0') -- Cosigner 2 Years at Address
2739 ||NVL(alter_rec.int_rate_opt,' ') -- Interest Rate Option
2740 ||NVL(alter_rec.repayment_opt_code,' ') -- Repayment Option Code
2741 ||LPAD(' ',307,' ') -- Filler
2742 ||'*';
2743
2744 fnd_file.put_line(fnd_file.output,UPPER (l_at4Record));
2745 END IF;
2746
2747 -- Count the @4 Records being sent.
2748 l_count_4 := l_count_4 +1;
2749
2750 END IF;
2751
2752
2753 -- If Number of Disbursements are more than 4, then need to send the remaining
2754 -- disbursement details in the @8 Send record.
2755 -- masehgal 2477912 Made record "upper" to resolve NCAT reported errors
2756
2757 IF lv_counter > 4 THEN
2758 fnd_file.put_line(fnd_file.output,UPPER ( '@8NCLP05'
2759 ||RPAD(lv_8_final_disb_date,128,'0')
2760 ||RPAD(lv_8_final_hold_rel_ind,16,' ')
2761 ||RPAD(NVL(lv_8_final_gross_amt,'0'),80,'0')
2762 ||RPAD(lv_8_direct_to_borr_flag,16,' ')
2763 ||RPAD(' ',711,' ')
2764 ||'*'));
2765 -- Increment the counter for the Number of @8 records in this file
2766 lv_8_disb_count := lv_8_disb_count + 1;
2767 END IF;
2768
2769 -- Corresponding Loan Id in IGF_SL_LOR_LOC table should be deleted
2770 DECLARE
2771 lv_row_id ROWID;
2772 CURSOR c_tbh_cur IS
2773 SELECT row_id row_id
2774 FROM igf_sl_lor_loc
2775 WHERE loan_id = loan_rec.loan_id FOR UPDATE OF loan_status NOWAIT;
2776 BEGIN
2777 FOR tbh_rec in c_tbh_cur LOOP
2778 igf_sl_lor_loc_pkg.delete_row (tbh_rec.row_id);
2779 END LOOP;
2780 END;
2781
2782 -- Insert the same loan record into IGF_SL_LOR_LOC to keep track of Data Sent to External Processor
2783
2784 insert_lor_loc_rec (
2785 p_v_school_id => p_school_id ,
2786 p_n_coa => l_coa,
2787 p_n_efc => l_efc,
2788 p_n_est_fin => l_est_fin,
2789 p_c_alt_borr_ind_flag => l_borw_ind_code
2790 );
2791
2792 -- Corresponding Award_id should be Deleted from IGF_SL_AWD_DISB_LOC
2793 DECLARE
2794 lv_row_id ROWID;
2795 CURSOR c_tbh_cur IS
2796 SELECT row_id row_id
2797 FROM igf_sl_awd_disb_loc
2798 WHERE award_id = loan_rec.award_id;
2799 BEGIN
2800 FOR tbh_rec in c_tbh_cur LOOP
2801 igf_sl_awd_disb_loc_pkg.delete_row (tbh_rec.row_id);
2802 END LOOP;
2803 END;
2804
2805
2806 -- Insert the Disbursement Data into IGF_SL_AWD_DISB_LOC
2807 DECLARE
2808 lv_row_id ROWID;
2809 CURSOR c_tbh_cur IS
2810 SELECT *
2811 FROM igf_aw_awd_disb
2812 WHERE award_id = loan_rec.award_id;
2813 BEGIN
2814 FOR tbh_rec IN c_tbh_cur LOOP
2815 lv_row_id := NULL;
2816
2817 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
2818 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Calling igf_sl_awd_disb_loc_pkg.insert_row');
2819 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.award_id ' || tbh_rec.award_id );
2820 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.disb_num ' || tbh_rec.disb_num );
2821 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.disb_accepted_amt ' || tbh_rec.disb_accepted_amt );
2822 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.fee_1 ' || tbh_rec.fee_1 );
2823 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.fee_2 ' || tbh_rec.fee_2 );
2824 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.disb_net_amt ' || tbh_rec.disb_net_amt );
2825 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.disb_date ' || tbh_rec.disb_date );
2826 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.hold_rel_ind ' || tbh_rec.hold_rel_ind );
2827 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.fee_paid_1 ' || tbh_rec.fee_paid_1 );
2828 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', ' tbh_rec.fee_paid_2 ' || tbh_rec.fee_paid_2 );
2829 END IF;
2830
2831 igf_sl_awd_disb_loc_pkg.insert_row (
2832 x_Mode => 'R',
2833 x_rowid => lv_row_id,
2834 x_award_id => tbh_rec.award_id,
2835 x_disb_num => tbh_rec.disb_num,
2836 x_disb_gross_amt => tbh_rec.disb_accepted_amt,
2837 x_fee_1 => tbh_rec.fee_1,
2838 x_fee_2 => tbh_rec.fee_2,
2839 x_disb_net_amt => tbh_rec.disb_net_amt,
2840 x_disb_date => tbh_rec.disb_date,
2841 x_hold_rel_ind => tbh_rec.hold_rel_ind,
2842 x_fee_paid_1 => tbh_rec.fee_paid_1,
2843 x_fee_paid_2 => tbh_rec.fee_paid_2
2844 );
2845 END LOOP;
2846 END;
2847
2848 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
2849 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'igf_sl_awd_disb_loc_pkg.insert_row succeeded');
2850 END IF;
2851
2852
2853 -- Update the Loan Status to Sent and Loan Status Date to SYSDATE
2854 DECLARE
2855 lv_row_id ROWID;
2856 CURSOR c_tbh_cur IS
2857 SELECT igf_sl_loans.*
2858 FROM igf_sl_loans
2859 WHERE loan_id = loan_rec.loan_id FOR UPDATE OF igf_sl_loans.loan_status NOWAIT;
2860 BEGIN
2861 FOR tbh_rec in c_tbh_cur LOOP
2862
2863 -- Modified the Update Row call of the IGF_SL_LOANS_PKG, as part of Refunds DLD 2144600 for Borrower
2864 -- determination
2865
2866 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
2867 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Calling igf_sl_loans_pkg.update_row');
2868 END IF;
2869
2870 igf_sl_loans_pkg.update_row (
2871 x_Mode => 'R',
2872 x_rowid => tbh_rec.row_id,
2873 x_loan_id => tbh_rec.loan_id,
2874 x_award_id => tbh_rec.award_id,
2875 x_seq_num => tbh_rec.seq_num,
2876 x_loan_number => tbh_rec.loan_number,
2877 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
2878 x_loan_per_end_date => tbh_rec.loan_per_end_date,
2879 x_loan_status => 'S',
2880 x_loan_status_date => TRUNC(SYSDATE),
2881 x_loan_chg_status => tbh_rec.loan_chg_status,
2882 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
2883 x_active => tbh_rec.active,
2884 x_active_date => tbh_rec.active_date,
2885 x_borw_detrm_code => tbh_rec.borw_detrm_code,
2886 x_legacy_record_flag => NULL,
2887 x_external_loan_id_txt => tbh_rec.external_loan_id_txt
2888 );
2889 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
2890 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'igf_sl_loans_pkg.update_row succeeded');
2891 END IF;
2892
2893 END LOOP;
2894 END;
2895
2896 -- Update the Origination Batch Id with Computed Batch Id and Origination Batch Date to SYSDATE
2897
2898 Update_orig_batch_id(loan_rec.origination_id, lv_batch_id);
2899
2900 EXCEPTION
2901 WHEN SKIP_RECORD THEN
2902 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
2903 fnd_file.put_line(fnd_file.log,fnd_message.get);
2904 fnd_file.new_line(fnd_file.log,1);
2905 END;
2906 END LOOP;
2907
2908
2909 -- Creating the Trailer Record for the Set of Records Fetched and Sent To File
2910
2911 lv_trailer_rec := NULL;
2912 -- masehgal 2477912 Made record "upper" to resolve NCAT reported errors
2913 IF lv_cl_version = 'RELEASE-5' THEN
2914 lv_trailer_rec := UPPER('@T'
2915 ||LPAD(TO_CHAR(NVL(lv_trans_count,0)),6,'0')
2916 ||LPAD(TO_CHAR(NVL(l_n_send2_rec_cnt,0)),6,'0')
2917 ||l_v_trailer_date
2918 ||l_v_trailer_time
2919 ||RPAD(lv_file_ident_code,5)
2920 ||RPAD(NVL(lv_source_name,' '),32,' ')
2921 ||RPAD(NVL(p_school_id,' '),8,' ')
2922 ||' '
2923 ||RPAD(NVL(sch_non_ed_branch,' '),4,' ')
2924 ||RPAD(NVL(recip_desc_rec.recip_description,' '),32,' ')
2925 ||RPAD(NVL(recip_desc_rec.recipient_id,' '),8,' ')
2926 ||' '
2927 ||RPAD(NVL(recip_desc_rec.recip_non_ed_brc_id,' '),4,' ')
2928 ||LPAD(NVL(l_count_4,0),6,0)
2929 ||LPAD(TO_CHAR(NVL(l_n_send5_rec_cnt,0)),6,'0')
2930 ||LPAD(TO_CHAR(NVL(l_n_send7_rec_cnt,0)),6,'0')
2931 ||LPAD(' ',9,' ')
2932 ||LPAD(' ',9,' ') --As per Prev Code we were sending the School DUNS ID,but CL Ref doc says it should be spaces
2933 ||LPAD(TO_CHAR(lv_8_disb_count),6,'0')
2934 ||LPAD(' ',792,' ')
2935 ||'*');
2936 ELSIF lv_cl_version = 'RELEASE-4' THEN
2937 lv_trailer_rec := UPPER('@T'
2938 ||LPAD(TO_CHAR(NVL(lv_trans_count,0)),6,'0')
2939 ||LPAD(TO_CHAR(NVL(l_n_send2_rec_cnt,0)),6,'0')
2940 ||l_v_trailer_date
2941 ||l_v_trailer_time
2942 ||RPAD(lv_file_ident_code,5)
2943 ||RPAD(NVL(lv_source_name,' '),32,' ')
2944 ||RPAD(NVL(p_school_id,' '),8,' ')
2945 ||' '
2946 ||RPAD(NVL(sch_non_ed_branch,' '),4,' ')
2947 ||RPAD(NVL(recip_desc_rec.recip_description,' '),32,' ')
2948 ||RPAD(NVL(recip_desc_rec.recipient_id,' '),8,' ')
2949 ||' '
2950 ||RPAD(NVL(recip_desc_rec.recip_non_ed_brc_id,' '),4,' ')
2951 ||LPAD(NVL(l_count_4,0),6,0)
2952 ||LPAD(TO_CHAR(NVL(l_n_send5_rec_cnt,0)),6,'0')
2953 ||LPAD(TO_CHAR(NVL(l_n_send7_rec_cnt,0)),6,'0')
2954 ||LPAD(' ',9,' ')
2955 ||LPAD(' ',9,' ')
2956 ||LPAD(' ',718,' ')
2957 ||'*');
2958 END IF;
2959 fnd_file.put_line(fnd_file.output,UPPER(lv_trailer_rec));
2960
2961 COMMIT;
2962
2963 EXCEPTION
2964 WHEN app_exception.record_lock_exception THEN
2965 ROLLBACK;
2966 retcode := 2;
2967 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
2968 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
2969 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.exception','Record Lock Exception');
2970 END IF;
2971
2972 igs_ge_msg_stack.conc_exception_hndl;
2973
2974 WHEN no_loan_data THEN
2975 -- This will happen when either there were no loan records to originate
2976 -- OR all records which were valid and ready to Send, were not Valid.
2977
2978 retcode := 2;
2979 errbuf := fnd_message.get_string('IGF','IGF_SL_NO_LOAN_ORIG_DATA');
2980 fnd_file.put_line(fnd_file.log, '');
2981 fnd_file.put_line(fnd_file.log, errbuf);
2982 IF(fnd_log.level_statement >= g_debug_runtime_level)THEN
2983 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug','No loan origination data found');
2984 END IF;
2985 errbuf := NULL;
2986
2987 WHEN OTHERS THEN
2988 ROLLBACK;
2989 retcode := 2;
2990 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2991 fnd_message.set_token('NAME','IGF_SL_CL_ORIG.SUB_CL_ORIGINATE');
2992 IF(fnd_log.level_exception >= g_debug_runtime_level)THEN
2993 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.sub_cl_originate.exception', SQLERRM );
2994 END IF;
2995 errbuf := fnd_message.get;
2996
2997 igs_ge_msg_stack.conc_exception_hndl;
2998 END sub_cl_originate;
2999
3000
3001 END igf_sl_cl_orig;