[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_RECORD
Source
1 PACKAGE BODY igf_sl_dl_record AS
2 /* $Header: IGFSL11B.pls 120.1 2006/04/21 05:01:20 bvisvana noship $ */
3
4 /*
5 --------------------------------------------------------------------
6 -- who when what
7 ----------------------------------------------------------------------------------
8 -- upinjark 16-Feb-2005 Bug #4187798. Modified line no 181,227,422
9 replacing FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
10 with FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
11 ----------------------------------------------------------------------------------
12
13 brajendr 12-Oct-2004 FA138 ISIR Enhacements
14 Modified the reference of payment_isir_id
15
16 --------------------------------------------------------------------
17 -- veramach 29-Jan-2004 bug 3408092 added 2004-2005 in p_dl_version checks
18 -----------------------------------------------------------------------------------
19 -- ugummall 17-OCT-2003 Bug 3102439. FA 126. Multiple FA Offices.
20 -- New parameter p_school_code is added to procedures
21 -- DLHeader_cur and DLOrig_cur.
22 --------------------------------------------------------------------
23 -- sjadhav 7-Oct-2003 Bug 3104228 FA 122 Build
24 -- added join condition in cursor
25 -- c_lor_details for fund code
26 -- Removed ref to obsolete columns
27 --------------------------------------------------------------------
28 --
29 -- sjadhav,
30 -- Bug 2436484
31 -- removed references to igf_ap_batch_aw_map table
32 -- in get_acad_begin_date and _get_acad_end_date functions
33 -- Use igs_ad_gen_008.get_acadcal function to get acad dates
34 -- for the functions get_acad_begin_date and get_acad_end_date
35 -- the passed in parameters are not used
36 --
37 */
38
39 p_rec_length igf_sl_dl_file_type.rec_length%TYPE;
40
41 PROCEDURE DLHeader_cur(p_dl_version igf_lookups_view.lookup_code%TYPE,
42 p_dl_loan_catg igf_lookups_view.lookup_code%TYPE,
43 p_cal_type igs_ca_inst.cal_type%TYPE,
44 p_cal_seq_num igs_ca_inst.sequence_number%TYPE,
45 p_file_type igf_sl_dl_file_type.dl_file_type%TYPE,
46 p_school_code IN VARCHAR2,
47 p_dbth_id IN OUT NOCOPY igf_sl_dl_batch.dbth_id%TYPE,
48 p_batch_id IN OUT NOCOPY igf_sl_dl_batch.batch_id%TYPE,
49 p_mesg_class IN OUT NOCOPY igf_sl_dl_batch.message_class%TYPE,
50 Header_Rec IN OUT NOCOPY igf_sl_dl_record.DLHeaderType)
51 AS
52
53 /*************************************************************
54 Created By : venagara
55 Date Created On : 2000/11/13
56 Purpose :
57 Know limitations, enhancements or remarks
58 Change History:
59 Who When What
60 ugummall 17-OCT-2003 Bug 3102439. FA 126. Multiple FA Offices.
61 As school_id is obsoleted from igf_sl_dl_setup table,
62 its reference is replaced with supplied parameter p_school_code
63 smvk 18-Feb-2003 Bug # 2758823. Modified the p_dl_version checking from '2001-2002','2002-2003' to '2002-2003','2003-2004'.
64 mesriniv 8-jul-2002 Changed LPAD(TO_CHAR(p_rec_length),4) to LPAD(TO_CHAR(p_rec_length),4,0)
65 Who When What
66 mesriniv 14-MAR-2002 Added when no data found Exception as part of the Bug :- 2255281.
67 DL Version Change
68 adhawan 19-02-2002 changed the references of 2001-2002 to 2002-2003
69 changed the header and the orig record as per 2002-2003 file format changes
70 (reverse chronological order - newest change first)
71 ***************************************************************/
72
73 lv_message_class igf_sl_dl_file_type.message_class%TYPE;
74 lv_batch_type igf_sl_dl_file_type.batch_type%TYPE;
75 lv_cycle_year igf_sl_dl_file_type.cycle_year%TYPE;
76
77 BEGIN
78
79 -- Message Class, Batch Type and Cycle Year have been predefined for each Version+Filetype
80 -- in igf_sl_dl_file_type seed data table.
81 p_mesg_class := igf_sl_gen.get_dl_file_type(p_dl_version, p_file_type,
82 p_dl_loan_catg, 'MESSAGE-CLASS');
83 lv_batch_type := igf_sl_gen.get_dl_file_type(p_dl_version, p_file_type,
84 p_dl_loan_catg, 'BATCH-TYPE');
85 lv_cycle_year := igf_sl_gen.get_dl_file_type(p_dl_version, p_file_type,
86 p_dl_loan_catg, 'CYCLE-YEAR');
87 p_rec_length := igf_sl_gen.get_dl_file_type(p_dl_version, p_file_type,
88 p_dl_loan_catg, 'REC-LENGTH');
89
90 IF p_dl_version IN ('2002-2003','2003-2004','2004-2005') THEN
91
92 DECLARE
93
94 lv_file_datetime DATE;
95 lv_rowid VARCHAR2(25);
96
97 --Bug 2490289 Batch ID's Time Component and the seperate field Time Component
98 --should be same.The time component should HH24MISS and not HH24MMSS.
99 CURSOR cur_dl_setup IS
100 SELECT lv_batch_type||
101 substr(lv_cycle_year,-1,1)||
102 RPAD(p_school_code,6,' ') ||
103 TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') batch_id,
104 SYSDATE file_datetime
105 FROM igf_sl_dl_setup_v
106 WHERE ci_cal_type = p_cal_type
107 AND ci_sequence_number = p_cal_seq_num;
108
109 BEGIN
110
111 /************* Get Batch-ID details ****************/
112 OPEN cur_dl_setup;
113 FETCH cur_dl_setup into p_batch_id, lv_file_datetime;
114 IF cur_dl_setup%NOTFOUND THEN
115 CLOSE cur_dl_setup;
116 RAISE NO_DATA_FOUND;
117 END IF;
118 CLOSE cur_dl_setup;
119
120 OPEN Header_Rec FOR
121 SELECT RPAD('DL HEADER',10)
122 ||LPAD(TO_CHAR(p_rec_length),4,0)
123 ||LPAD(p_mesg_class,8)
124 ||RPAD(p_batch_id,23)
125 ||RPAD(TO_CHAR(lv_file_datetime,'YYYYMMDD'),8)
126 ||LPAD(TO_CHAR(lv_file_datetime,'HH24MISS'),6)
127 ||RPAD(' ', 2)
128 ||RPAD(' ', 8)
129 ||RPAD(' ',2)
130 ||RPAD('IGS1157',9)
131 ||RPAD(' ',(p_rec_length-80)) h_record FROM DUAL;
132
133 igf_sl_dl_batch_pkg.insert_row (
134 x_mode => 'R',
135 x_rowid => lv_rowid,
136 X_dbth_id => p_dbth_id,
137 X_batch_id => p_batch_id,
138 X_message_class => p_mesg_class,
139 X_bth_creation_date => lv_file_datetime,
140 X_batch_rej_code => NULL,
141 X_end_date => NULL,
142 X_batch_type => lv_batch_type,
143 X_send_resp => 'S', -- Send File
144 X_status => 'Y' -- Status = Processed
145 );
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 IF cur_dl_setup%ISOPEN THEN
150 CLOSE cur_dl_setup;
151 END IF;
152 fnd_message.set_name('IGF','IGF_GE_NO_DATA_FOUND');
153 fnd_message.set_token('NAME','igf_sl_dl_orig.set_batch_details');
154 igs_ge_msg_stack.add;
155 app_exception.raise_exception;
156 END;
157
158 END IF;
159
160 EXCEPTION
161 --Added this Exception as part of the Bug :- 2255281.
162 --DL Version Change
163 --This is to ensure that even if the DL Setup has the particular version specified,
164 --it is better we check if the IGF_SL_DL_FILE_TYPE (Seeded Table-Latest File Versions to
165 --shipped every time new Version Comes in) has the File Type Details.
166 --DL Setup picks up the Version from Lookups for LOOKUP_TYPE='IGF_SL_DL_VERSION'
167 WHEN NO_DATA_FOUND THEN
168 fnd_message.set_name('IGF','IGF_SL_DL_NO_VERSION');
169 fnd_message.set_token('P_DL_VERSION',p_dl_version);
170 igs_ge_msg_stack.add;
171 app_exception.raise_exception;
172 WHEN OTHERS THEN
173 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
174 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_dl_record.DLHeader_cur.exception',SQLERRM);
175 END IF;
176 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
177 fnd_message.set_token('NAME','igf_sl_dl_record.DLHeader_cur');
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END DLHeader_cur;
181
182
183 PROCEDURE DLTrailer_cur(p_dl_version igf_lookups_view.lookup_code%TYPE,
184 p_num_of_rec NUMBER,
185 Trailer_Rec IN OUT NOCOPY igf_sl_dl_record.DLTrailerType)
186 AS
187 /*************************************************************
188 Created By : venagara
189 Date Created On : 2000/11/13
190 Purpose :
191 Know limitations, enhancements or remarks
192 Change History
193 Bug No:2438434
194 Desc :DL Orig Process.Incorrect Format in Output File
195 Who When What
196 smvk 18-Feb-2003 Bug # 2758823. Modified the p_dl_version checking from '2001-2002','2002-2003' to '2002-2003','2003-2004'.
197 mesriniv 1-jul-2002 Changed LPAD(TO_CHAR(p_rec_length),4) to LPAD(TO_CHAR(p_rec_length),4,0)
198 Changed LPAD(TO_CHAR(p_num_of_rec),7) to LPAD(TO_CHAR(p_num_of_rec),7,0)
199 RPAD(' ',(p_rec_length-80)) was added make trailer rec length same as Header Record Length
200 (reverse chronological order - newest change first)
201 ***************************************************************/
202
203 BEGIN
204
205 IF p_dl_version IN ('2002-2003','2003-2004','2004-2005') THEN
206 OPEN Trailer_Rec FOR
207 SELECT RPAD('DL TRAILER',10)||
208 LPAD(TO_CHAR(p_rec_length),4,0)||
209 LPAD(TO_CHAR(p_num_of_rec),7,0)||
210 RPAD(' ', 5)||
211 RPAD(' ', 5)||
212 RPAD(' ', 5)||
213 RPAD(' ',44)||
214 RPAD(' ',(p_rec_length-80)) t_record FROM DUAL;
215 END IF;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
220 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_dl_record.DLTrailer_cur.exception',SQLERRM);
221 END IF;
222 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
223 fnd_message.set_token('NAME','igf_sl_dl_record.DLTrailer_cur');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 END DLTrailer_cur;
227
228
229 FUNCTION DLDisbDetails(p_dl_version igf_lookups_view.lookup_code%TYPE,
230 p_award_id igf_aw_award.award_id%TYPE)
231 RETURN VARCHAR2
232 AS
233 /*************************************************************
234 Created By : venagara
235 Date Created On : 2000/11/13
236 Purpose :
237 Know limitations, enhancements or remarks
238 Change History
239 Bug 2438434.Incorrect Format in Output File.
240 Who When What
241 smvk 18-Feb-2003 Bug # 2758823. Modified the p_dl_version checking from '2001-2002','2002-2003' to '2002-2003','2003-2004'.
242 mesriniv 1-jul-2002 Made LPAD of 0 for Amount Fields
243 (reverse chronological order - newest change first)
244 ***************************************************************/
245
246 lv_disb_details VARCHAR2(4000) := '';
247 --Int_rebate_amt is being picked from the Disbursement Table directly instead of the
248 --calculation
249 --Bug 2438434.
250 CURSOR cur_disb IS
251 SELECT adisb.disb_date, NVL(adisb.disb_accepted_amt,adisb.disb_gross_amt) disb_gross_amt, adisb.fee_1,
252 adisb.int_rebate_amt interest_rebate,
253 adisb.disb_net_amt
254 FROM igf_aw_award awd,
255 igf_aw_awd_disb adisb,
256 igf_aw_fund_mast fmast,
257 igf_aw_fund_cat fcat,
258 igf_ap_fa_base_rec fabase
259
260 WHERE awd.award_id = p_award_id
261 AND adisb.award_id = awd.award_id
262 AND awd.fund_id = fmast.fund_id
263 AND fmast.fund_code = fcat.fund_code
264 AND awd.base_id = fabase.base_id
265 ORDER BY adisb.disb_num;
266 BEGIN
267
268 IF p_dl_version IN ('2002-2003','2003-2004','2004-2005') THEN
269
270 FOR orec IN cur_disb LOOP
271 lv_disb_details := lv_disb_details
272 ||LPAD(NVL(TO_CHAR(orec.disb_date,'YYYYMMDD'),' '),8)
273 ||LPAD(NVL(TO_CHAR(orec.disb_gross_amt) ,'0'),5,0)
274 ||LPAD(NVL(TO_CHAR(orec.fee_1) ,'0'),5,0)
275 ||LPAD(NVL(TO_CHAR(orec.interest_rebate) ,'0'),5,0)
276 ||LPAD(NVL(TO_CHAR(orec.disb_net_amt) ,'0'),5,0);
277 END LOOP;
278
279 RETURN lv_disb_details;
280
281 END IF;
282
283 RETURN NULL;
284
285 END DLDisbDetails;
286
287
288 -- masehgal # 2593215 new procedure to return acad begin and end dates ...
289 -- this will replace get_Acad_begin_date and get_acad_end_date Functions ...
290
291 PROCEDURE get_acad_cal_dtls( p_loan_number igf_sl_loans_all.loan_number%TYPE,
292 p_acad_cal_type OUT NOCOPY igs_ca_inst_all.cal_type%TYPE,
293 p_acad_seq_num OUT NOCOPY igs_ca_inst_all.sequence_number%TYPE,
294 p_acad_begin_date IN OUT NOCOPY igs_ps_ofr_inst.ci_start_dt%TYPE,
295 p_acad_end_date IN OUT NOCOPY igs_ps_ofr_inst.ci_end_dt%TYPE ,
296 p_message OUT NOCOPY VARCHAR2 )
297 AS
298 /*************************************************************
299 Created By : masehgal
300 Date Created On : 08-Jan-2003
301 Purpose : To Get Acad Cal related details
302 Know limitations, enhancements or remarks
303 Change History
304 Who When What
305 bkkumar 30-sep-2003 Removed the c_get_base_id cursor as that was taking wrong
306 imput paramters and is not required since same information is
307 obtained from c_get_person_id cursor.
308 Added debug log messages
309 rasahoo 01-Sep-2003 Removed the Cursor c_get_enr_prog and all its references
310 as part of the build FA-114 (Obsoletion of FA base record History)
311 bkkumar 27-Aug-2003 Bug# 3071157 Removed the unnecessary to_date()
312 (reverse chronological order - newest change first)
313 ***************************************************************/
314
315 -- cursor to get person_id from loan_number
316 -- We need person id to get primary enrol prog from FA Base History
317 CURSOR c_get_person_id ( cp_loan_number igf_sl_loans.loan_number%TYPE) IS
318 SELECT fa.person_id,
319 fa.base_id
320 FROM igf_ap_fa_base_rec fa, igf_sl_loans loan , igf_aw_award awd
321 WHERE fa.base_id = awd.base_id
322 AND awd.award_id = loan.award_id
323 AND loan.loan_number = cp_loan_number ;
324
325 -- cursor to get loan_dates for that loan_number
326 -- We need loan_dates to determine proper acad cal
327 CURSOR c_get_loan_dates ( cp_loan_number igf_sl_loans.loan_number%TYPE) IS
328 SELECT loan_per_begin_date , loan_per_end_date
329 FROM igf_sl_loans
330 WHERE loan_number = cp_loan_number ;
331
332 -- cursor to get acad_cal for that loan_number
333 CURSOR get_acad_cal ( cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE ,
334 cp_ver_num igs_ps_ofr_inst.version_number%TYPE ,
335 cp_start_dt igf_sl_loans_v.loan_per_begin_date%TYPE ,
336 cp_end_dt igf_sl_loans_v.loan_per_end_date%TYPE ) IS
337 SELECT cal_type, ci_sequence_number, ci_start_dt, ci_end_dt
338 FROM igs_ps_ofr_inst
339 WHERE course_cd = cp_course_cd
340 AND version_number = cp_ver_num
341 AND ci_start_dt <= cp_start_dt
342 AND ci_end_dt >= cp_end_dt ;
343
344 l_person_id igf_ap_fa_base_rec.person_id%TYPE ;
345 l_base_id igf_ap_fa_base_rec.base_id%TYPE;
346 l_get_loan_dates_rec c_get_loan_dates%ROWTYPE ;
347 l_get_acad_cal_rec get_acad_cal%ROWTYPE ;
348
349 l_course_cd igs_ps_ofr_inst.course_cd%TYPE ;
350 l_ver_num igs_ps_ofr_inst.version_number%TYPE ;
351 l_begin_dt igf_sl_loans.loan_per_begin_date%TYPE ;
352 l_end_dt igf_sl_loans.loan_per_end_date%TYPE ;
353
354 BEGIN
355
356 -- check if loan number is null ...
357 -- if so then return
358 IF p_loan_number IS NULL THEN
359 RETURN ;
360 END IF ;
361
362 --First get the person_id
363 OPEN c_get_person_id (p_loan_number) ;
364 FETCH c_get_person_id INTO l_person_id,l_base_id ;
365 CLOSE c_get_person_id ;
366 -- if a laon exists then a person has to exist .. therefore not checking for notfound condition
367
368 -- PUT DEBUG MESSAGES HERE
369 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_record.get_acad_cal_dtls.debug','l_base_id passed to get_key_program:'|| l_base_id);
371 END IF;
372 -- Call generic API get_key_program to get course code and version number
373 igf_ap_gen_001.get_key_program(l_base_id,l_course_cd,l_ver_num);
374
375 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
376 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_record.get_acad_cal_dtls.debug','l_course_cd got from get_key_program:'|| l_course_cd);
377 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_record.get_acad_cal_dtls.debug','l_ver_num got from get_key_program:'|| l_ver_num);
378 END IF;
379
380 IF p_acad_begin_date IS NOT NULL AND p_acad_end_date IS NOT NULL THEN
381
382 l_begin_dt := p_acad_begin_date;
383 l_end_dt := p_acad_end_date;
384
385 ELSE
386 --get loan_dates
387 OPEN c_get_loan_dates (p_loan_number) ;
388 FETCH c_get_loan_dates INTO l_get_loan_dates_rec ;
389 CLOSE c_get_loan_dates ;
390 -- if a loan exists then dates have to exist .. therefore not checking for notfound condition
391 -- assign to variables
392 l_begin_dt := l_get_loan_dates_rec.loan_per_begin_date ;
393 l_end_dt := l_get_loan_dates_rec.loan_per_end_date ;
394
395 END IF;
396
397 -- Get the acad cal for these dates
398 -- bvisvana - Bug 5078761
399 IF l_course_cd IS NOT NULL AND l_ver_num IS NOT NULL THEN
400 OPEN get_acad_cal (l_course_cd, l_ver_num, l_begin_dt, l_end_dt ) ;
401 FETCH get_acad_cal INTO l_get_acad_cal_rec ;
402 IF get_acad_cal%NOTFOUND THEN
403 p_message := 'IGF_SL_NO_ACAD_DATES' ;
404 ELSE
405 -- assign to variables
406 p_acad_cal_type := l_get_acad_cal_rec.cal_type ;
407 p_acad_seq_num := l_get_acad_cal_rec.ci_sequence_number ;
408 p_acad_begin_date := l_get_acad_cal_rec.ci_start_dt ;
409 p_acad_end_date := l_get_acad_cal_rec.ci_end_dt ;
410 p_message := NULL ;
411 END IF;
412 CLOSE get_acad_cal ;
413 ELSE
414 p_message := 'IGF_SL_NO_KEYPRG_ACAD_CAL';
415 END IF; -- Bug 5078761
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
420 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_dl_record.get_acad_cal_dtls.exception',SQLERRM);
421 END IF;
422 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
423 FND_MESSAGE.SET_TOKEN('NAME','IGF_SL_DL_RECORD.GET_ACAD_CAL_DTLS');
424 IGS_GE_MSG_STACK.ADD;
425 APP_EXCEPTION.RAISE_EXCEPTION;
426 END get_acad_cal_dtls ;
427
428
429
430 PROCEDURE DLOrig_cur(p_dl_version igf_lookups_view.lookup_code%TYPE,
431 p_dl_loan_catg igf_lookups_view.lookup_code%TYPE,
432 p_ci_cal_type igs_ca_inst.cal_type%TYPE,
433 p_ci_seq_num igs_ca_inst.sequence_number%TYPE,
434 p_dl_loan_number igf_sl_loans.loan_number%TYPE,
435 p_dl_batch_id igf_sl_dl_batch.batch_id%TYPE,
436 p_school_code IN VARCHAR2,
437 Orig_Rec IN OUT NOCOPY igf_sl_dl_record.DLOrigType)
438 AS
439 /*************************************************************
440 Created By : venagara
441 Date Created On : 2000/11/13
442 Purpose :
443 Know limitations, enhancements or remarks
444 Change History:
445 Bug 2438434.Incorrect Format in Output File.
446 Who When What
447 ugummall 17-OCT-2003 Bug 3102439. FA 126. Multiple FA Offices.
448 As school_id is obsoleted from igf_sl_dl_setup table, its reference
449 is removed and supplied parameter p_school_code is used.
450
451
452 bkkumar 30-sep-2003 Bug 3104228 FA 122 Loans Enhancements
453 Added the cursor c_lor_details and
454 instead of using igf_sl_lor_dtls_v used simple
455 joins and got the details of student and parent
456 from igf_sl_gen.get_person_details.
457 Added the debugging log messages.
458 smvk 17-Feb-2003 Bug # 2758823. Added filler space from 132 to 153 for dl_version 2003-2004 for ED to use.
459 mesriniv 1-jul-2002 Made UPPERCASE for Name,Address Fields,Code added for Student/Parent Phone
460 Bug :- 2426609 SSN Format Incorrect in Output File
461 Who When What
462 mesriniv 21-jun-2002 Wherever Student SSN/Parent SSN is output
463 formatting is done before the output.
464
465 (reverse chronological order - newest change first)
466 ***************************************************************/
467
468
469 lv_acad_begin_dt igs_ca_inst.start_dt%TYPE;
470 lv_acad_end_dt igs_ca_inst.end_dt%TYPE;
471 l_p_phone VARCHAR2(80);
472 l_s_phone VARCHAR2(80);
473 lv_acad_cal_type igs_ca_inst.cal_type%TYPE := NULL;
474 lv_acad_seq_number igs_ca_inst.sequence_number%TYPE := NULL ;
475 lv_message VARCHAR2(100) ;
476
477 l_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE;
478 l_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE;
479 student_dtl_cur igf_sl_gen.person_dtl_cur;
480 parent_dtl_cur igf_sl_gen.person_dtl_cur;
481 student_dtl_rec igf_sl_gen.person_dtl_rec;
482 parent_dtl_rec igf_sl_gen.person_dtl_rec;
483
484 -- cursor to replace the columns selected from the igf_sl_lor_dtls_v FA 122 Enhancements
485
486 CURSOR c_lor_details (
487 cp_cal_type igs_ca_inst.cal_type%TYPE,
488 cp_seq_number igs_ca_inst.sequence_number%TYPE,
489 cp_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE,
490 cp_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE,
491 cp_loan_status igf_sl_loans.loan_status%TYPE,
492 cp_active igf_sl_loans.active%TYPE,
493 cp_dl_loan_number igf_sl_loans.loan_number%TYPE
494 ) IS
495 SELECT
496 loans.row_id,
497 loans.loan_id,
498 loans.loan_number,
499 loans.award_id,
500 awd.accepted_amt loan_amt_accepted,
501 loans.loan_per_begin_date,
502 loans.loan_per_end_date,
503 lor.orig_fee_perct,
504 lor.pnote_print_ind,
505 lor.s_default_status,
506 lor.p_default_status,
507 lor.p_person_id,
508 lor.grade_level_code,
509 lor.unsub_elig_for_heal,
510 lor.disclosure_print_ind,
511 lor.unsub_elig_for_depnt,
512 lor.pnote_batch_id,
513 lor.pnote_ack_date,
514 lor.pnote_mpn_ind,
515 lor.sch_cert_date,
516 fabase.base_id,
517 fabase.person_id student_id,
518 awd.accepted_amt,
519 isr.alien_reg_number,
520 isr.citizenship_status,
521 isr.dependency_status
522 FROM
523 igf_sl_loans loans,
524 igf_sl_lor lor,
525 igf_aw_award awd,
526 igf_aw_fund_mast fmast,
527 igf_aw_fund_cat fcat,
528 igf_ap_fa_base_rec fabase,
529 igf_ap_isir_matched isr
530 WHERE
531 fabase.ci_cal_type = cp_cal_type AND
532 fabase.ci_sequence_number = cp_seq_number AND
533 fabase.base_id = awd.base_id AND
534 awd.fund_id = fmast.fund_id AND
535 fabase.base_id = isr.base_id AND
536 isr.payment_isir = 'Y' AND
537 isr.system_record_type = 'ORIGINAL' AND
538 fcat.fund_code = fmast.fund_code AND
539 (fcat.fed_fund_code = cp_fed_fund_1 OR fcat.fed_fund_code = cp_fed_fund_2) AND
540 loans.award_id = awd.award_id AND
541 loans.loan_number LIKE NVL(cp_dl_loan_number,loans.loan_number) AND
542 loans.loan_id = lor.loan_id AND
543 loans.loan_status = cp_loan_status AND
544 loans.active = cp_active;
545
546 l_lor_details c_lor_details%ROWTYPE;
547
548
549 --Cursor cur_dl_setup is deleted here as school_id is obsoleted from table igf_sl_dl_setup. FA 126
550
551 --Cursor to select the Person ID for the loan ID
552
553 BEGIN
554
555
556 -- masehgal # 2593215 removing begin/end date calls ....
557 -- instead use get_acad_cal_dtls ....
558
559 get_acad_cal_dtls( p_dl_loan_number,
560 lv_acad_cal_type,
561 lv_acad_seq_number,
562 lv_acad_begin_dt,
563 lv_acad_end_dt,
564 lv_message ) ;
565 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
566 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_record.DLOrig_cur.debug','lv_message got from get_acad_cal_dtls:'|| lv_message);
567 END IF;
568 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
569 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_record.DLOrig_cur.debug','lv_acad_begin_date got from get_acad_cal_dtls:'|| lv_acad_begin_dt);
570 END IF;
571 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
572 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_record.DLOrig_cur.debug','lv_acad_end_date got from get_acad_cal_dtls:'|| lv_acad_end_dt);
573 END IF;
574 -- FA 122 Loan Enhancements derive the paramters to be passed to the c_lor cursor
575 IF p_dl_loan_catg = 'DL_STAFFORD' THEN
576 l_fed_fund_1 := 'DLS';
577 l_fed_fund_2 := 'DLU';
578 ELSIF p_dl_loan_catg = 'DL_PLUS' THEN
579 l_fed_fund_1 := 'DLP';
580 l_fed_fund_2 := 'DLP';
581 END IF;
582
583 l_lor_details := NULL;
584 OPEN c_lor_details(p_ci_cal_type,p_ci_seq_num,l_fed_fund_1,l_fed_fund_2,'V','Y',p_dl_loan_number);
585 FETCH c_lor_details INTO l_lor_details;
586 CLOSE c_lor_details;
587
588 -- FA 122 Loan Enhancements Use the igf_sl_gen.get_person_details for getting the student as
589 -- well as parent details.
590
591 -- get the student details
592 igf_sl_gen.get_person_details(l_lor_details.student_id,student_dtl_cur);
593 FETCH student_dtl_cur INTO student_dtl_rec;
594
595 -- get the parene details
596 igf_sl_gen.get_person_details(l_lor_details.p_person_id,parent_dtl_cur);
597 FETCH parent_dtl_cur INTO parent_dtl_rec;
598
599 CLOSE student_dtl_cur;
600 CLOSE parent_dtl_cur;
601
602 -- all the cursors are now using the values selected from the l_lor_details cursor and the person related info
603 -- from the student_dtl_cur , parent_dtl_cur.
604
605 IF p_dl_version = '2002-2003' THEN
606
607 IF p_dl_loan_catg = 'DL_STAFFORD' THEN
608
609 OPEN Orig_Rec FOR
610 SELECT l_lor_details.award_id award_id,
611 l_lor_details.loan_id loan_id,
612 l_lor_details.loan_number loan_number,
613 RPAD(l_lor_details.loan_number,21) -- #1
614 ||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn),9)
615 ||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
616 ||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
617 ||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
618 ||RPAD(NVL(UPPER(student_dtl_rec.p_permt_addr1)||' '||UPPER(student_dtl_rec.p_permt_addr2),' ') ,35)
619 ||RPAD(NVL(UPPER(student_dtl_rec.p_permt_city) ,' '),16)
620 ||RPAD(NVL(UPPER(student_dtl_rec.p_permt_state) ,' '), 2)
621 ||RPAD(NVL(student_dtl_rec.p_permt_zip ,' '), 9)
622 ||DECODE(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),10,0))
623 ||RPAD(' ',22)
624 ||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
625 ||LPAD(' ', 1)
626 ||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9)
627 ||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
628 ||LPAD(NVL(l_lor_details.grade_level_code ,' '), 1)
629 ||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0)
630 ||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD'),8)
631 ||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD'),8) -- #20
632 ||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
633 ||RPAD(p_dl_batch_id,23) -- #101
634 ||RPAD(NVL(l_lor_details.pnote_print_ind ,' '),1)
635 ||RPAD(NVL(decode(l_lor_details.unsub_elig_for_depnt,
636 'Y',l_lor_details.unsub_elig_for_depnt,
637 ' '),' '),1)
638 ||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
639 ||LPAD(' ', 9)
640 ||RPAD(' ',12)
641 ||RPAD(' ',16)
642 ||RPAD(' ', 1)
643 ||RPAD(' ', 1)
644 ||LPAD(' ', 9) -- #110
645 ||RPAD(' ', 8)
646 ||RPAD(' ', 1)
647 ||RPAD(NVL(p_school_code,' '),6)
648 ||RPAD(' ', 5)
649 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '||UPPER(student_dtl_rec.p_local_addr2) ,' '),35)
650 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
651 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
652 ||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
653 ||RPAD(' ', 32)
654 ||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
655 ||RPAD(' ',41) -- #124 to #143
656 ||RPAD(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),8)
657 ||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
658 ||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
659 ||RPAD(NVL(decode(l_lor_details.unsub_elig_for_heal,
660 'Y',l_lor_details.unsub_elig_for_heal,
661 ' '),' '),1)
662 ||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
663 'N',' ',
664 l_lor_details.disclosure_print_ind) ,' '),1)
665 ||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
666 transaction_rec
667 FROM dual;
668
669 ELSIF p_dl_loan_catg = 'DL_PLUS' THEN
670
671 OPEN Orig_Rec FOR
672 SELECT l_lor_details.award_id award_id,
673 l_lor_details.loan_id loan_id,
674 l_lor_details.loan_number loan_number,
675 RPAD(l_lor_details.loan_number,21) -- #1
676 ||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(parent_dtl_rec.p_ssn),9)
677 ||RPAD(NVL(UPPER(parent_dtl_rec.p_first_name),' ') ,12)
678 ||RPAD(NVL(UPPER(parent_dtl_rec.p_last_name),' ') ,16)
679 ||RPAD(NVL(UPPER(parent_dtl_rec.p_middle_name) ,' ') ,1)
680 ||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_addr1)||' '||UPPER(parent_dtl_rec.p_permt_addr2),' ') ,35)
681 ||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_city),' ') ,16)
682 ||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_state),' ') ,2) -- Should be valid 2 digit code
683 ||RPAD(NVL(parent_dtl_rec.p_permt_zip,' ') ,9)
684 ||DECODE(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),10,0)) -- #10 ######## p_phone
685 ||RPAD(' ',22)
686 ||RPAD(NVL(TO_CHAR(parent_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
687 ||LPAD(NVL(parent_dtl_rec.p_citizenship_status,' ') , 1)
688 ||LPAD(NVL(parent_dtl_rec.p_alien_reg_num,' ') ,9)
689 ||RPAD(NVL(l_lor_details.p_default_status,' ') ,1)
690 ||LPAD(NVL(l_lor_details.grade_level_code,' ') ,1)
691 ||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0) --Should >0 for anytype of LOAN
692 ||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD') ,8)
693 ||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD') ,8) -- #20
694 ||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
695 ||RPAD(p_dl_batch_id,23) -- #101
696 ||RPAD(NVL(l_lor_details.pnote_print_ind ,' '), 1)
697 ||RPAD(' ', 1) --unsub_elig_for_depnt is N/A for PLUS
698 ||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
699 ||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn), 9)
700 ||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
701 ||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
702 ||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
703 ||RPAD(NVL(l_lor_details.citizenship_status ,' '), 1)
704 ||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9) -- #110
705 ||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
706 ||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
707 ||RPAD(NVL(p_school_code ,' '), 6)
708 ||LPAD(' ', 5)
709 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '|| UPPER(student_dtl_rec.p_local_addr2),' '),35)
710 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
711 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
712 ||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
713 ||RPAD(' ', 32)
714 ||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
715 ||RPAD(' ',41) -- #124 to #143
716 ||RPAD(NVL(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),' '),8)
717 ||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
718 ||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
719 ||RPAD(' ',1) -- for plus loans Additional Unsubsidized Health .. is N/A
720 ||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
721 'N',' ',
722 l_lor_details.disclosure_print_ind) ,' '),1)
723 ||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
724 transaction_rec
725 FROM dual;
726
727 END IF;
728
729 ELSIF p_dl_version IN ('2003-2004','2004-2005') THEN
730 IF p_dl_loan_catg = 'DL_STAFFORD' THEN
731
732 OPEN Orig_Rec FOR
733 SELECT l_lor_details.award_id award_id,
734 l_lor_details.loan_id loan_id,
735 l_lor_details.loan_number loan_number,
736 RPAD(l_lor_details.loan_number,21) -- #1
737 ||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn),9)
738 ||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
739 ||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
740 ||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
741 ||RPAD(NVL(UPPER(student_dtl_rec.p_permt_addr1)||' '||UPPER(student_dtl_rec.p_permt_addr2),' ') ,35)
742 ||RPAD(NVL(UPPER(student_dtl_rec.p_permt_city) ,' '),16)
743 ||RPAD(NVL(UPPER(student_dtl_rec.p_permt_state) ,' '), 2)
744 ||RPAD(NVL(student_dtl_rec.p_permt_zip ,' '), 9)
745 ||DECODE(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(l_lor_details.base_id)),10,0))
746 ||RPAD(' ', 22)-- Filler for ED use. Bug # 2758823
747 ||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
748 ||LPAD(' ', 1)
749 ||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9)
750 ||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
751 ||LPAD(NVL(l_lor_details.grade_level_code ,' '), 1)
752 ||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0)
753 ||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD'),8)
754 ||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD'),8) -- #20
755 ||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
756 ||RPAD(p_dl_batch_id,23) -- #101
757 ||RPAD(NVL(l_lor_details.pnote_print_ind,' '),1)
758 ||RPAD(NVL(decode(l_lor_details.unsub_elig_for_depnt,
759 'Y',l_lor_details.unsub_elig_for_depnt,
760 ' '),' '),1)
761 ||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
762 ||LPAD(' ', 9)
763 ||RPAD(' ',12)
764 ||RPAD(' ',16)
765 ||RPAD(' ', 1)
766 ||RPAD(' ', 1)
767 ||LPAD(' ', 9) -- #110
768 ||RPAD(' ', 8)
769 ||RPAD(' ', 1)
770 ||RPAD(NVL(p_school_code,' '),6)
771 ||RPAD(' ', 5) -- Filler for ED use.
772 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '||UPPER(student_dtl_rec.p_local_addr2) ,' '),35)
773 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
774 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
775 ||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
776 ||RPAD(' ', 32)
777 ||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
778 ||RPAD(' ',41) -- #124 to #143
779 ||RPAD(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),8)
780 ||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
781 ||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
782 ||RPAD(NVL(decode(l_lor_details.unsub_elig_for_heal,
783 'Y',l_lor_details.unsub_elig_for_heal,
784 ' '),' '),1)
785 ||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
786 'N',' ',
787 l_lor_details.disclosure_print_ind) ,' '),1)
788 ||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
789 transaction_rec
790 FROM dual;
791
792 ELSIF p_dl_loan_catg = 'DL_PLUS' THEN
793
794 OPEN Orig_Rec FOR
795 SELECT l_lor_details.award_id award_id,
796 l_lor_details.loan_id loan_id,
797 l_lor_details.loan_number loan_number,
798 RPAD(l_lor_details.loan_number,21) -- #1
799 ||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(parent_dtl_rec.p_ssn),9)
800 ||RPAD(NVL(UPPER(parent_dtl_rec.p_first_name),' ') ,12)
801 ||RPAD(NVL(UPPER(parent_dtl_rec.p_last_name),' ') ,16)
802 ||RPAD(NVL(UPPER(parent_dtl_rec.p_middle_name) ,' ') ,1)
803 ||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_addr1)||' '||UPPER(parent_dtl_rec.p_permt_addr2),' ') ,35)
804 ||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_city),' ') ,16)
805 ||RPAD(NVL(UPPER(parent_dtl_rec.p_permt_state),' ') ,2) -- Should be valid 2 digit code
806 ||RPAD(NVL(parent_dtl_rec.p_permt_zip,' ') ,9)
807 ||DECODE(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(l_lor_details.p_person_id),10,0))
808 ||RPAD(' ', 22)-- Filler for ED use. Bug # 2758823
809 ||RPAD(NVL(TO_CHAR(parent_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
810 ||LPAD(NVL(parent_dtl_rec.p_citizenship_status,' ') , 1)
811 ||LPAD(NVL(parent_dtl_rec.p_alien_reg_num,' ') ,9)
812 ||RPAD(NVL(l_lor_details.p_default_status,' ') ,1)
813 ||LPAD(NVL(l_lor_details.grade_level_code,' ') ,1)
814 ||LPAD(TO_CHAR(l_lor_details.loan_amt_accepted), 5,0) --Should >0 for anytype of LOAN
815 ||RPAD(TO_CHAR(l_lor_details.loan_per_begin_date,'YYYYMMDD') ,8)
816 ||RPAD(TO_CHAR(l_lor_details.loan_per_end_date ,'YYYYMMDD') ,8) -- #20
817 ||RPAD(NVL(DLDisbDetails(p_dl_version, l_lor_details.award_id),' '), 560) -- #21 to #100
818 ||RPAD(p_dl_batch_id,23) -- #101
819 ||RPAD(NVL(l_lor_details.pnote_print_ind ,' '), 1)
820 ||RPAD(' ', 1) --unsub_elig_for_depnt is N/A for PLUS
821 ||LPAD(NVL(LTRIM(TO_CHAR(l_lor_details.orig_fee_perct*1000,'00000')),'0'),5) -- Ltrim() done to remove sign char space
822 ||LPAD(igf_ap_matching_process_pkg.remove_spl_chr(student_dtl_rec.p_ssn), 9)
823 ||RPAD(NVL(UPPER(student_dtl_rec.p_first_name) ,' '),12)
824 ||RPAD(NVL(UPPER(student_dtl_rec.p_last_name) ,' '),16)
825 ||RPAD(NVL(UPPER(student_dtl_rec.p_middle_name) ,' '), 1)
826 ||RPAD(NVL(l_lor_details.citizenship_status ,' '), 1)
827 ||LPAD(NVL(l_lor_details.alien_reg_number ,' '), 9) -- #110
828 ||RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8)
829 ||RPAD(NVL(l_lor_details.s_default_status ,' '), 1)
830 ||RPAD(NVL(p_school_code ,' '), 6)
831 ||RPAD(' ', 5) -- Filler for ED use
832 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_addr1)||' '|| UPPER(student_dtl_rec.p_local_addr2),' '),35)
833 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_city) ,' '),16)
834 ||RPAD(NVL(UPPER(student_dtl_rec.p_local_state) ,' '), 2)
835 ||RPAD(NVL(student_dtl_rec.p_local_zip ,' '), 9)
836 ||RPAD(' ', 32)
837 ||RPAD(NVL(l_lor_details.dependency_status ,' '), 1)
838 ||RPAD(' ',41) -- #124 to #143
839 ||RPAD(NVL(TO_CHAR(l_lor_details.sch_cert_date,'YYYYMMDD'),' '),8)
840 ||RPAD(NVL(TO_CHAR(lv_acad_begin_dt,'YYYYMMDD') ,' '),8)
841 ||RPAD(NVL(TO_CHAR(lv_acad_end_dt ,'YYYYMMDD') ,' '),8)
842 ||RPAD(' ',1) -- for plus loans Additional Unsubsidized Health .. is N/A
843 ||RPAD(NVL(decode(l_lor_details.disclosure_print_ind,
844 'N',' ',
845 l_lor_details.disclosure_print_ind) ,' '),1)
846 ||RPAD(NVL(student_dtl_rec.p_email_addr,' '),50) -- #149
847 transaction_rec
848 FROM dual;
849 END IF;
850 END IF;
851
852 EXCEPTION
853 WHEN OTHERS THEN
854 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
855 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_dl_record.DLOrig_cur.exception',SQLERRM);
856 END IF;
857 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
858 fnd_message.set_token('NAME','igf_sl_dl_record.DLOrig_cur');
859 igs_ge_msg_stack.add;
860 app_exception.raise_exception;
861 END DLOrig_cur;
862
863
864 END igf_sl_dl_record;