[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_CL_CHG_FILE
Source
1 PACKAGE BODY igf_sl_cl_chg_file AS
2 /* $Header: IGFSL24B.pls 120.7 2006/08/03 11:55:35 tsailaja noship $ */
3 /*
4 ------------------------------------------------------------------
5 --Created by : Sanil Madathil, Oracle IDC
6 --Date created: 21 October 2004
7 --
8 --Purpose:
9 -- Invoked : From concurrent manager
10 -- Function : FFELP Change Send File Creation
11 --
12 --
13 --Known limitations/enhancements and/or remarks:
14 --
15 --Change History:
16 --Who When What
17 --tsailaja 25-Jul-2006 Bug #5337555 FA 163 Included 'GPLUSFL'
18 -- Loan type validation
19 --bvisvana 24-Nov-2005 Bug # 4256897 - Added anticip_compl_date to the
20 TYPE lorlar_recTyp. Also added anticip_compl_date to CURSOR c_lor_lar
21 --bvisvana 18-Jul-2005 4132989 - Added sch_cert_date,loan_per_end_date,loan_per_begin_date to the
22 -- TYPE lorlar_recTyp. Also added sch_cert_date to CURSOR c_lor_lar
23 --mnade 27-Jan-2005 Bug - 4146934
24 -- Added 'G' Filter on the c_lor_lar cursor to avoid picking up
25 -- all loans.
26 -------------------------------------------------------------------
27 */
28
29 TYPE lorlar_recTyp IS RECORD (
30 cal_type igs_ca_inst_all.cal_type%TYPE,
31 sequence_number igs_ca_inst_all.sequence_number%TYPE,
32 fund_id igf_aw_fund_mast_all.fund_id%TYPE,
33 discontinue_fund igf_aw_fund_mast_all.discontinue_fund%TYPE,
34 fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
35 base_id igf_ap_fa_base_rec_all.base_id%TYPE,
36 award_id igf_aw_award_all.award_id%TYPE,
37 loan_id igf_sl_loans_all.loan_id%TYPE,
38 loan_number igf_sl_loans_all.loan_number%TYPE,
39 loan_status igf_sl_loans_all.loan_status%TYPE,
40 loan_chg_status igf_sl_loans_all.loan_chg_status%TYPE,
41 loan_active igf_sl_loans_all.active%TYPE,
42 anticip_compl_date igf_sl_lor_all.anticip_compl_date%TYPE,
43 sch_cert_date igf_sl_lor_all.sch_cert_date%TYPE,
44 loan_per_begin_date igf_sl_loans_all.loan_per_begin_date%TYPE,
45 loan_per_end_date igf_sl_loans_all.loan_per_end_date%TYPE,
46 cl_rec_status igf_sl_lor_all.cl_rec_status%TYPE,
47 prc_type_code igf_sl_lor_all.prc_type_code%TYPE,
48 cl_version igf_sl_cl_setup_all.cl_version%TYPE
49 );
50
51 -- procedure for enabling statement level logging
52 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
53 p_v_string IN VARCHAR2
54 );
55
56 PROCEDURE log_parameters ( p_v_param_typ IN VARCHAR2,
57 p_v_param_val IN VARCHAR2
58 ) ;
59
60 PROCEDURE identify_clchsn_dtls
61 (
62 p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
63 p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
64 p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
65 p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
66 p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE
67 );
68 FUNCTION get_base_id
69 (
70 p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
71 p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
72 p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
73 )
74 RETURN igf_ap_fa_base_rec_all.base_id%TYPE;
75
76 FUNCTION get_person_number (p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE)
77 RETURN hz_parties.party_number%TYPE;
78
79 FUNCTION validate_cl_lar (p_rec_lorlar lorlar_recTyp) RETURN BOOLEAN;
80
81 PROCEDURE proc_update_loan_rec(p_loan_rec igf_sl_loans%ROWTYPE);
82
83 PROCEDURE proc_update_clchsn_dtls_rec(p_v_loan_number IN igf_sl_loans.loan_number%TYPE ,
84 p_v_change_record_typ IN igf_sl_clchsn_dtls.change_record_type_txt%TYPE ,
85 p_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE ,
86 p_v_send_record_txt IN igf_sl_clchsn_dtls.send_record_txt%TYPE
87 );
88
89 PROCEDURE create_file (
90 errbuf OUT NOCOPY VARCHAR2,
91 retcode OUT NOCOPY NUMBER,
92 p_v_award_year IN VARCHAR2,
93 p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
94 p_n_dummy_1 IN NUMBER,
95 p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
96 p_n_dummy_2 IN NUMBER,
97 p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE,
98 p_n_dummy_3 IN NUMBER,
99 p_n_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE,
100 p_v_media_type IN igf_lookups_view.lookup_code%TYPE,
101 p_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE,
102 p_v_non_ed_branch IN igf_sl_school_codes_v.system_id_type%TYPE,
103 p_v_sch_non_ed_branch IN igf_sl_school_codes_v.alternate_identifier%TYPE
104 ) AS
105 ------------------------------------------------------------------
106 --Created by : Sanil Madathil, Oracle IDC
107 --Date created: 21 October 2004
108 --
109 -- Purpose:
110 -- Invoked : from concurrent manager (Job IGFSLJ20)
111 -- Function :
112 --
113 -- Parameters : p_v_award_year : IN parameter. Required.
114 -- p_n_fund_id : IN parameter
115 -- p_n_dummy_1 : IN parameter
116 -- p_n_base_id : IN parameter
117 -- p_n_dummy_2 : IN parameter
118 -- p_n_loan_id : IN parameter
119 -- p_n_dummy_3 : IN parameter
120 -- p_n_person_id_grp : IN parameter
121 -- p_v_media_type : IN parameter. Required.
122 -- p_v_school_id : IN parameter. Required.
123 -- p_v_non_ed_branch : IN parameter.
124 -- p_v_sch_non_ed_branch : IN parameter.
125 --
126 --Known limitations/enhancements and/or remarks:
127 --
128 --Change History:
129 --Who When What
130 --ridas 07-Feb-2006 Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL
131 ------------------------------------------------------------------
132 CURSOR c_igf_aw_fund_mast(cp_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
133 SELECT fmast.description fund_desc
134 ,fmast.ci_cal_type
135 ,fmast.ci_sequence_number
136 FROM igf_aw_fund_mast_all fmast
137 WHERE fmast.fund_id = cp_n_fund_id;
138
139 rec_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
140
141 CURSOR c_igf_sl_loans(cp_n_loan_id igf_sl_loans_all.loan_id%TYPE) IS
142 SELECT lar.loan_number
143 ,lar.award_id
144 FROM igf_sl_loans_all lar
145 WHERE lar.loan_id = cp_n_loan_id;
146
147 rec_c_igf_sl_loans c_igf_sl_loans%ROWTYPE;
148
149 CURSOR c_person_grp (cp_n_person_id_grp igs_pe_persid_group_all.group_id%TYPE) IS
150 SELECT pig.group_cd
151 FROM igs_pe_persid_group_all pig
152 WHERE pig.group_id = cp_n_person_id_grp;
153
154 CURSOR c_aw_lookups_view (cp_v_lookup_type igf_lookups_view.lookup_type%TYPE,
155 cp_v_lookup_code igf_lookups_view.lookup_code%TYPE,
156 cp_v_cal_type igs_ca_inst_all.cal_type%TYPE,
157 cp_n_sequence_number igs_ca_inst_all.sequence_number%TYPE
158 ) IS
159 SELECT lkups.meaning
160 FROM igf_aw_lookups_view lkups
161 WHERE lkups.lookup_type = cp_v_lookup_type
162 AND lkups.lookup_code = cp_v_lookup_code
163 AND lkups.cal_type = cp_v_cal_type
164 AND lkups.sequence_number = cp_n_sequence_number
165 AND lkups.enabled_flag = 'Y'
166 ORDER BY lookup_code;
167
168 CURSOR c_school_codes(cp_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE) IS
169 SELECT alternate_identifier
170 ,system_id_type
171 FROM igf_sl_school_codes_v
172 WHERE alternate_identifier = cp_v_school_id
173 ORDER BY alternate_identifier;
174
175 rec_c_school_codes c_school_codes%ROWTYPE;
176
177 CURSOR c_school_opeid(cp_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE) IS
178 SELECT meaning
179 FROM igf_ap_school_opeid_v
180 WHERE alternate_identifier = cp_v_school_id;
181
182 CURSOR c_sch_non_ed_branch (cp_v_non_ed_branch igf_sl_school_codes_v.system_id_type%TYPE,
183 cp_v_alt_identifier igf_sl_school_codes_v.alternate_identifier%TYPE
184 ) IS
185 SELECT 'x'
186 FROM igf_sl_school_codes_v
187 WHERE system_id_type = cp_v_non_ed_branch
188 AND alternate_identifier = cp_v_alt_identifier
189 ORDER BY alternate_identifier;
190
191
192 CURSOR c_recip_dtls( cp_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
193 cp_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
194 cp_c_loan_chg_status IN igf_sl_loans_all.loan_chg_status%TYPE,
195 cp_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE
196 ) IS
197 SELECT DISTINCT lor.relationship_cd
198 FROM igf_sl_lor_all lor
199 ,igf_sl_loans_all loans
200 ,igf_aw_award_all awd
201 ,igf_aw_fund_mast_all fmast
202 ,igf_sl_cl_recipient recip
203 WHERE loans.loan_id = lor.loan_id
204 AND loans.loan_chg_status = cp_c_loan_chg_status
205 AND awd.award_id = loans.award_id
206 AND fmast.fund_id = awd.fund_id
207 AND fmast.ci_cal_type = cp_v_cal_type
208 AND fmast.ci_sequence_number = cp_n_sequence_number
209 AND SUBSTR(loans.loan_number,1, 6) = SUBSTR(cp_v_school_id,1,6)
210 AND recip.relationship_cd = lor.relationship_cd;
211
212
213 TYPE ref_CurpersongrpTyp IS REF CURSOR;
214 c_dyn_person_grp ref_CurpersongrpTyp;
215
216 l_v_cal_type igs_ca_inst_all.cal_type%TYPE;
217 l_n_sequence_number igs_ca_inst_all.sequence_number%TYPE;
218 l_n_loan_id igf_sl_loans_all.loan_id%TYPE;
219 l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
220 l_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
221 l_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
222 l_n_person_id_grp igs_pe_persid_group_all.group_id%TYPE;
223 l_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE;
224 l_v_media_type igf_lookups_view.lookup_code%TYPE;
225 l_v_sch_non_ed_branch igf_sl_school_codes_v.alternate_identifier%TYPE;
226 l_v_non_ed_branch igf_lookups_view.lookup_code%TYPE;
227
228 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
229 l_v_alt_code igs_ca_inst_all.alternate_code%TYPE;
230 l_v_fund_desc igf_aw_fund_mast_all.description%TYPE;
231 l_v_group_cd igs_pe_persid_group_all.group_cd%TYPE;
232 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
233 l_c_flag VARCHAR2(1);
234 l_v_sql VARCHAR2(32767);
235 l_v_status VARCHAR2(1);
236 l_v_meaning igf_lookups_view.meaning%TYPE;
237 l_v_message_name fnd_new_messages.message_name%TYPE;
238 l_n_request_id NUMBER;
239 l_n_ctr_recip NUMBER;
240 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
241
242 e_skip EXCEPTION;
243
244 BEGIN
245 igf_aw_gen.set_org_id(NULL);
246 retcode := 0 ;
247
248 log_to_fnd(p_v_module => 'create_file',
249 p_v_string => ' Entered Procedure create_file: The input parameters are '||
250 ' p_v_award_year : ' ||p_v_award_year ||
251 ' p_n_fund_id : ' ||p_n_fund_id ||
252 ' p_n_base_id : ' ||p_n_base_id ||
253 ' p_n_loan_id : ' ||p_n_loan_id ||
254 ' p_n_person_id_grp : ' ||p_n_person_id_grp ||
255 ' p_v_media_type : ' ||p_v_media_type ||
256 ' p_v_school_id : ' ||p_v_school_id ||
257 ' p_v_non_ed_branch : ' ||p_v_non_ed_branch ||
258 ' p_v_sch_non_ed_branch : ' ||p_v_sch_non_ed_branch
259 );
260 -- derive the cal type and sequence number for the input award year
261 l_v_cal_type := LTRIM(RTRIM(SUBSTR(p_v_award_year,1,10)));
262 l_n_sequence_number := TO_NUMBER(SUBSTR(p_v_award_year,11));
263 -- assigning the passed input parameters to local variables
264 l_n_fund_id := p_n_fund_id;
265 l_n_base_id := p_n_base_id;
266 l_n_loan_id := p_n_loan_id;
267 l_n_person_id_grp := p_n_person_id_grp;
268 l_v_alt_code := igf_gr_gen.get_alt_code(l_v_cal_type,l_n_sequence_number);
269 l_v_media_type := p_v_media_type ;
270 l_v_school_id := p_v_school_id ;
271 l_v_non_ed_branch := p_v_non_ed_branch;
272 l_v_sch_non_ed_branch := p_v_sch_non_ed_branch;
273
274 ----------------------- parameter logging logic starts here---------------------------
275 log_to_fnd(p_v_module => 'create_file',
276 p_v_string => ' Start of Parameter logging'
277 );
278
279 fnd_file.new_line(fnd_file.log,1);
280 fnd_file.put(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
281 fnd_file.new_line(fnd_file.log,1);
282
283 OPEN c_igf_aw_fund_mast (cp_n_fund_id => l_n_fund_id);
284 FETCH c_igf_aw_fund_mast INTO rec_c_igf_aw_fund_mast;
285 CLOSE c_igf_aw_fund_mast;
286
287 l_v_fund_desc := rec_c_igf_aw_fund_mast.fund_desc;
288
289 OPEN c_igf_sl_loans (cp_n_loan_id => l_n_loan_id);
290 FETCH c_igf_sl_loans INTO rec_c_igf_sl_loans;
291 CLOSE c_igf_sl_loans;
292
293 l_v_loan_number := rec_c_igf_sl_loans.loan_number;
294
295 OPEN c_person_grp (cp_n_person_id_grp => l_n_person_id_grp);
296 FETCH c_person_grp INTO l_v_group_cd;
297 CLOSE c_person_grp;
298
299 OPEN c_school_opeid (cp_v_school_id => l_v_school_id);
300 FETCH c_school_opeid INTO l_v_meaning;
301 CLOSE c_school_opeid ;
302
303 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'),40),
304 p_v_param_val => l_v_alt_code
305 );
306 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_TYPE'),40),
307 p_v_param_val => l_v_fund_desc
308 );
309 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),40),
310 p_v_param_val => igf_gr_gen.get_per_num(l_n_base_id)
311 );
312 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER'),40),
313 p_v_param_val => l_v_loan_number
314 );
315 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'),40),
316 p_v_param_val => l_v_group_cd
317 );
318
319 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','MEDIA_TYPE'),40),
320 p_v_param_val => igf_aw_gen.lookup_desc('IGF_SL_MEDIA_TYPE',l_v_media_type)
321 );
322
323 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','SCHOOL_ID'),40),
324 p_v_param_val => l_v_meaning
325 );
326
327 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','SCH_NON_ED_BRANCH'),40),
328 p_v_param_val => l_v_sch_non_ed_branch
329 );
330
331 fnd_file.new_line(fnd_file.log,1);
332 fnd_file.put_line(fnd_file.log, '--------------------------------------------------------');
333 fnd_file.new_line(fnd_file.log,1);
334 log_to_fnd(p_v_module => 'create_file',
335 p_v_string => ' End of Parameter logging'
336 );
337 -- parameter logging logic ends here -------------------------------------------------
338
339 -- Validation of Required parameters---------------------------------------------------
340 log_to_fnd(p_v_module => 'create_file',
341 p_v_string => ' Validation of Required parameters'
342 );
343 IF p_v_award_year IS NULL THEN
344 fnd_message.set_name('IGF','IGF_SL_COD_REQ_PARAM');
345 fnd_message.set_token('PARAM',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'));
346 fnd_file.put_line(fnd_file.log, fnd_message.get);
347 fnd_file.new_line(fnd_file.log, 1);
348 retcode := 2;
349 RETURN;
350 END IF;
351
352 IF l_v_media_type IS NULL THEN
353 fnd_message.set_name('IGF','IGF_SL_COD_REQ_PARAM');
354 fnd_message.set_token('PARAM',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','MEDIA_TYPE'));
355 fnd_file.put_line(fnd_file.log, fnd_message.get);
356 fnd_file.new_line(fnd_file.log, 1);
357 retcode := 2;
358 RETURN;
359 END IF;
360
361 IF l_v_school_id IS NULL THEN
362 fnd_message.set_name('IGF','IGF_SL_COD_REQ_PARAM');
363 fnd_message.set_token('PARAM',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','SCHOOL_ID'));
364 fnd_file.put_line(fnd_file.log, fnd_message.get);
365 fnd_file.new_line(fnd_file.log, 1);
366 retcode := 2;
367 RETURN;
368 END IF;
369
370 log_to_fnd(p_v_module => 'create_file',
371 p_v_string => ' End of Validation of Required parameters'
372 );
373 ----------------------------- Validation of Required parameters ends here-------------
374
375 ---------------------------- Validation of parameters----------------------------------
376 log_to_fnd(p_v_module => 'create_file',
377 p_v_string => ' Start of Validation of input parameters'
378 );
379 -- Validate if the passed award year is valid or not
380 IF l_v_alt_code IS NULL THEN
381 fnd_message.set_name('IGF','IGF_SL_NO_CALENDAR');
382 fnd_file.put_line(fnd_file.log, fnd_message.get);
383 fnd_file.new_line(fnd_file.log, 1);
384 retcode := 2;
385 RETURN;
386 END IF;
387 --Validate the Award Year Status. If the status is not open, log the message in log file and
388 --complete the process with error.
389 l_v_message_name := NULL;
390 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => l_v_cal_type,
391 p_n_awd_seq_number => l_n_sequence_number,
392 p_v_awd_yr_status => l_v_awd_yr_status_cd,
393 p_v_message_name => l_v_message_name
394 );
395 IF l_v_message_name IS NOT NULL THEN
396 IF l_v_message_name = 'IGF_SP_INVALID_AWD_YR_STATUS' THEN
397 fnd_message.set_name('IGF',l_v_message_name);
398 ELSE
399 fnd_message.set_name('IGS',l_v_message_name);
400 END IF;
401 fnd_file.put_line(fnd_file.log,fnd_message.get);
402 retcode := 2;
403 RETURN;
404 END IF;
405
406 -- person id and person group id are mutually exclusive. Hence if both are
407 -- provided error out of the process
408 IF l_n_person_id_grp IS NOT NULL AND l_n_base_id IS NOT NULL THEN
409 fnd_message.set_name('IGF','IGF_SL_COD_INV_PARAM');
410 fnd_message.set_token('PARAM1',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
411 fnd_message.set_token('PARAM2',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'));
412 fnd_file.put_line(fnd_file.log, fnd_message.get);
413 fnd_file.new_line(fnd_file.log, 1);
414 retcode := 2;
415 RETURN;
416 END IF;
417
418 -- loan id and person group id are mutually exclusive. Hence if both are
419 -- provided error out of the process
420 IF l_n_person_id_grp IS NOT NULL AND l_n_loan_id IS NOT NULL THEN
421 fnd_message.set_name('IGF','IGF_SL_COD_INV_PARAM');
422 fnd_message.set_token('PARAM1',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
423 fnd_message.set_token('PARAM2',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER'));
424 fnd_file.put_line(fnd_file.log, fnd_message.get);
425 fnd_file.new_line(fnd_file.log, 1);
426 retcode := 2;
427 RETURN;
428 END IF;
429
430 -- validate if the person group if passed is a valid person group
431 IF l_n_person_id_grp IS NOT NULL THEN
432 IF l_v_group_cd IS NULL THEN
433 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
434 fnd_message.set_token('PARAMETER',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
435 fnd_file.put_line(fnd_file.log, fnd_message.get);
436 retcode := 2;
437 RETURN;
438 END IF;
439 END IF;
440
441 -- validate the media type passed as input parameter to th process
442 OPEN c_aw_lookups_view (cp_v_lookup_type => 'IGF_SL_MEDIA_TYPE',
443 cp_v_lookup_code => l_v_media_type,
444 cp_v_cal_type => l_v_cal_type,
445 cp_n_sequence_number => l_n_sequence_number
446 );
447
448 FETCH c_aw_lookups_view INTO l_v_meaning;
449 IF c_aw_lookups_view%NOTFOUND THEN
450 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
451 fnd_message.set_token('PARAMETER',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','MEDIA_TYPE'));
452 fnd_file.put_line(fnd_file.log, fnd_message.get);
453 retcode := 2;
454 RETURN;
455 END IF;
456 CLOSE c_aw_lookups_view;
457
458 -- validate the school id parameter passed as input to the process
459 OPEN c_school_codes (cp_v_school_id => l_v_school_id);
460 FETCH c_school_codes INTO rec_c_school_codes;
461 IF c_school_codes%NOTFOUND THEN
462 CLOSE c_school_codes;
463 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
464 fnd_message.set_token('PARAMETER',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','SCHOOL_ID'));
465 fnd_file.put_line(fnd_file.log, fnd_message.get);
466 retcode := 2;
467 RETURN;
468 END IF;
469 CLOSE c_school_codes;
470
471 -- if user has provided the value for p_v_sch_non_ed_branch parameter
472 -- validate the parameter passed as input to the process
473 IF l_v_sch_non_ed_branch IS NOT NULL THEN
474 OPEN c_sch_non_ed_branch (cp_v_non_ed_branch => l_v_non_ed_branch,
475 cp_v_alt_identifier => l_v_sch_non_ed_branch
476 );
477 FETCH c_sch_non_ed_branch INTO l_c_flag;
478 IF c_sch_non_ed_branch%NOTFOUND THEN
479 CLOSE c_sch_non_ed_branch;
480 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
481 fnd_message.set_token('PARAMETER',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','SCH_NON_ED_BRANCH'));
482 fnd_file.put_line(fnd_file.log, fnd_message.get);
483 retcode := 2;
484 RETURN;
485 END IF;
486 CLOSE c_sch_non_ed_branch;
487
488 END IF;
489
490 log_to_fnd(p_v_module => 'create_file',
491 p_v_string => ' End of Validation of input parameters'
492 );
493 -----------------------end of validation of input parameters -------------------------
494 IF l_n_person_id_grp IS NOT NULL THEN
495
496 log_to_fnd(p_v_module => 'create_file',
497 p_v_string => ' Person Group is provided as input parameter'
498 );
499
500 --Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL
501 l_v_sql := igs_pe_dynamic_persid_group.get_dynamic_sql(p_groupid => l_n_person_id_grp,
502 p_status => l_v_status,
503 p_group_type => lv_group_type
504 );
505
506 --If the sql returned is invalid.. then,
507 IF l_v_status <> 'S' THEN
508 --Log the error message and stop processing.
509 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
510 fnd_file.put_line(fnd_file.log,fnd_message.get);
511 fnd_file.put_line(fnd_file.log,l_v_sql);
512 log_to_fnd(p_v_module => ' Procedure create_file',
513 p_v_string => ' igs_pe_dynamic_persid_group.get_dynamic_sql call out returned an error status'
514 );
515 retcode := 2;
516 RETURN;
517 END IF;
518
519 log_to_fnd(p_v_module => 'create_file',
520 p_v_string => ' igs_pe_dynamic_persid_group.get_dynamic_sql call out returned a status of Succes'
521 );
522
523 --Execute the sql statement using ref cursor.
524 --Bug #5021084. Passing Group ID if the group type is STATIC.
525 IF lv_group_type = 'STATIC' THEN
526 OPEN c_dyn_person_grp FOR l_v_sql USING l_n_person_id_grp;
527 ELSIF lv_group_type = 'DYNAMIC' THEN
528 OPEN c_dyn_person_grp FOR l_v_sql;
529 END IF;
530
531 LOOP
532 BEGIN
533 --Capture the person id into a local variable l_n_person_id.
534 FETCH c_dyn_person_grp INTO l_n_person_id;
535 EXIT WHEN c_dyn_person_grp%NOTFOUND;
536 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),40),
537 p_v_param_val => get_person_number (p_n_person_id => l_n_person_id)
538 );
539 log_to_fnd(p_v_module => 'create_file',
540 p_v_string => ' processing for Person number '||get_person_number (p_n_person_id => l_n_person_id)
541 );
542 -- validate if the person has been associated with the input award year
543 l_n_base_id := get_base_id (
544 p_v_cal_type => l_v_cal_type,
545 p_n_sequence_number => l_n_sequence_number,
546 p_n_person_id => l_n_person_id
547 ) ;
548 IF l_n_base_id IS NULL THEN
549 RAISE e_skip;
550 END IF;
551 log_to_fnd(p_v_module => 'create_file',
552 p_v_string => ' Invoking procedure identify_clchsn_dtls for base id '||l_n_base_id
553 );
554 identify_clchsn_dtls
555 (
556 p_v_cal_type => l_v_cal_type,
557 p_n_sequence_number => l_n_sequence_number,
558 p_n_fund_id => l_n_fund_id,
559 p_n_base_id => l_n_base_id,
560 p_n_loan_id => l_n_loan_id
561 );
562 EXCEPTION
563 WHEN e_skip THEN
564 fnd_message.set_name('IGF','IGF_SP_NO_FA_BASE_REC');
565 fnd_file.put_line(fnd_file.log, fnd_message.get);
566 fnd_file.new_line(fnd_file.log, 1);
567 log_to_fnd(p_v_module => ' Procedure create_file',
568 p_v_string => ' No Base id found for person id '||l_n_person_id
569 );
570 END;
571 END LOOP;
572 END IF;
573 -- if base id is provided as input to the process
574 IF p_n_base_id IS NOT NULL THEN
575 l_n_base_id := p_n_base_id;
576 log_to_fnd(p_v_module => 'create_file',
577 p_v_string => ' Base id: '|| l_n_base_id|| ' provides as input to the process '
578 );
579 log_to_fnd(p_v_module => 'create_file',
580 p_v_string => ' Invoking procedure identify_clchsn_dtls for base id '||l_n_base_id
581 );
582 identify_clchsn_dtls
583 (
584 p_v_cal_type => l_v_cal_type,
585 p_n_sequence_number => l_n_sequence_number,
586 p_n_fund_id => l_n_fund_id,
587 p_n_base_id => l_n_base_id,
588 p_n_loan_id => l_n_loan_id
589 );
590 END IF;
591 -- if both person group and base id parameters are not provided as input to the process
592 IF l_n_person_id_grp IS NULL AND p_n_base_id IS NULL THEN
593 l_n_base_id := p_n_base_id;
594 log_to_fnd(p_v_module => 'create_file',
595 p_v_string => ' Both person group and base id are not provided as input to the process '
596 );
597 identify_clchsn_dtls
598 (
599 p_v_cal_type => l_v_cal_type,
600 p_n_sequence_number => l_n_sequence_number,
601 p_n_fund_id => l_n_fund_id,
602 p_n_base_id => NULL,
603 p_n_loan_id => l_n_loan_id
604 );
605 END IF;
606
607 -- Loop through the distinct recipient
608 l_n_ctr_recip := 0;
609 FOR rec_c_recip_dtls IN c_recip_dtls (cp_v_cal_type => l_v_cal_type ,
610 cp_n_sequence_number => l_n_sequence_number,
611 cp_c_loan_chg_status => 'V',
612 cp_v_school_id => l_v_school_id
613 )
614 LOOP
615 -- this would invoke igf_sl_cl_chg_file.sub_create_file procedure
616 log_to_fnd(p_v_module => 'create_file',
617 p_v_string => 'Submitting the Concurrent request for relationship code '||rec_c_recip_dtls.relationship_cd
618 );
619 l_n_request_id := fnd_request.submit_request('IGF',
620 'IGFSLJ21',
621 '',
622 '',
623 FALSE,
624 l_v_cal_type,
625 TO_CHAR(l_n_sequence_number),
626 TO_CHAR(l_n_fund_id),
627 TO_CHAR(l_n_base_id),
628 TO_CHAR(l_n_loan_id),
629 rec_c_recip_dtls.relationship_cd,
630 l_v_media_type,
631 l_v_school_id,
632 l_v_sch_non_ed_branch,
633 CHR(0),
634 '', '', '', '', '', '', '', '', '', '',
635 '', '', '', '', '', '', '', '', '', '',
636 '', '', '', '', '', '', '', '', '', '',
637 '', '', '', '', '', '', '', '', '', '',
638 '', '', '', '', '', '', '', '', '', '',
639 '', '', '', '', '', '', '', '', '', '',
640 '', '', '', '', '', '', '', '', '', '',
641 '', '', '', '', '', '', '', '', '', '',
642 '', '', '', '', '', '', '', '', '', '');
643 l_n_ctr_recip := NVL(l_n_ctr_recip,0) + 1;
644 IF l_n_request_id = 0 THEN
645 -- On Failure of Concurrent Request
646 fnd_message.set_name('IGF','IGF_SL_CL_ORIG_REQ_FAIL');
647 igs_ge_msg_stack.add;
648 log_to_fnd(p_v_module => 'create_file',
649 p_v_string => 'Concurrent request failed for relationship code '||rec_c_recip_dtls.relationship_cd
650 );
651 app_exception.raise_exception;
652 END IF;
653 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
654 fnd_file.put_line(fnd_file.log, fnd_message.get || l_n_ctr_recip );
655
656 log_to_fnd(p_v_module => 'create_file',
657 p_v_string => 'Concurrent request successfully executed for relationship code '||rec_c_recip_dtls.relationship_cd
658 );
659 END LOOP;
660
661 EXCEPTION
662
663 WHEN OTHERS THEN
664 log_to_fnd(p_v_module => 'create_file',
665 p_v_string => ' when others exception handler '||SQLERRM
666 );
667 retcode := 2;
668 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
669 igs_ge_msg_stack.conc_exception_hndl;
670 END create_file;
671
672 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
673 p_v_string IN VARCHAR2
674 ) AS
675 ------------------------------------------------------------------
676 --Created by : Sanil Madathil, Oracle IDC
677 --Date created: 21 October 2004
678 --
679 -- Purpose:
680 -- Invoked : from within create_file procedure
681 -- Function : Private procedure for logging all the statement level
682 -- messages
683 -- Parameters : p_v_module : IN parameter. Required.
684 -- p_v_string : IN parameter. Required.
685 --
686 --
687 --Known limitations/enhancements and/or remarks:
688 --
689 --Change History:
690 --Who When What
691 ------------------------------------------------------------------
692 BEGIN
693 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
694 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_sl_cl_chg_file'||p_v_module, p_v_string);
695 END IF;
696 END log_to_fnd;
697
698 PROCEDURE log_parameters ( p_v_param_typ IN VARCHAR2,
699 p_v_param_val IN VARCHAR2
700 ) AS
701 ------------------------------------------------------------------
702 --Created by : Sanil Madathil, Oracle IDC
703 --Date created: 21 October 2004
704 --
705 -- Purpose:
706 -- Invoked : from within create_file procedure
707 -- Function : Private procedure for logging
708 --
709 -- Parameters : p_v_param_typ : IN parameter. Required.
710 -- p_v_param_val : IN parameter. Required.
711 --
712 --
713 --Known limitations/enhancements and/or remarks:
714 --
715 --Change History:
716 --Who When What
717 ------------------------------------------------------------------
718 BEGIN
719 fnd_file.put_line(fnd_file.log, p_v_param_typ || ' : ' || p_v_param_val );
720 END log_parameters;
721
722 FUNCTION get_base_id
723 (
724 p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
725 p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
726 p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
727 ) RETURN igf_ap_fa_base_rec_all.base_id%TYPE AS
728 ------------------------------------------------------------------
729 --Created by : Sanil Madathil, Oracle IDC
730 --Date created: 25 October 2004
731 --
732 -- Purpose:
733 -- Invoked : from within create_file procedure
734 -- Function : Private function to return the base id for input person id and
735 -- award year cal type and sequence number
736 --
737 -- Parameters : p_v_cal_type : IN parameter. Required.
738 -- p_n_sequence_number : IN parameter. Required.
739 -- p_n_person_id : IN parameter.
740 --
741 --Known limitations/enhancements and/or remarks:
742 --
743 --Change History:
744 --Who When What
745 ------------------------------------------------------------------
746 CURSOR c_ap_fa_base_rec (cp_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
747 cp_v_cal_type igs_ca_inst_all.cal_type%TYPE,
748 cp_n_sequence_number igs_ca_inst_all.sequence_number%TYPE
749 ) IS
750 SELECT base_id
751 FROM igf_ap_fa_base_rec_all fabase
752 WHERE fabase.person_id = cp_n_person_id
753 AND fabase.ci_cal_type = cp_v_cal_type
754 AND fabase.ci_sequence_number = cp_n_sequence_number;
755
756 l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
757 BEGIN
758 log_to_fnd(p_v_module => 'get_base_id',
759 p_v_string => ' Entered Procedure get_base_id: The input parameters are '||
760 ' p_v_cal_type : ' ||p_v_cal_type ||
761 ' p_n_sequence_number : ' ||p_n_sequence_number ||
762 ' p_n_person_id : ' ||p_n_person_id
763 );
764 OPEN c_ap_fa_base_rec ( cp_n_person_id => p_n_person_id,
765 cp_v_cal_type => p_v_cal_type,
766 cp_n_sequence_number => p_n_sequence_number
767 );
768 FETCH c_ap_fa_base_rec INTO l_n_base_id;
769 CLOSE c_ap_fa_base_rec;
770 log_to_fnd(p_v_module => 'get_base_id',
771 p_v_string => ' Return value of base id : '||l_n_base_id
772 );
773 RETURN l_n_base_id;
774 END get_base_id;
775
776 FUNCTION get_person_number (p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE)
777 RETURN hz_parties.party_number%TYPE AS
778 ------------------------------------------------------------------
779 --Created by : Sanil Madathil, Oracle IDC
780 --Date created: 25 October 2004
781 --
782 -- Purpose:
783 -- Invoked : from within create_file procedure
784 -- Function : Private function to return the person number for input person id
785 --
786 -- Parameters : p_n_person_id : IN parameter. Required.
787 --
788 --
789 --Known limitations/enhancements and/or remarks:
790 --
791 --Change History:
792 --Who When What
793 ------------------------------------------------------------------
794 -- cursor to get person number
795 CURSOR c_person_number (cp_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE) IS
796 SELECT person_number
797 FROM igs_pe_person_base_v
798 WHERE person_id = cp_n_person_id;
799
800 l_v_person_number hz_parties.party_number%TYPE;
801 BEGIN
802 log_to_fnd(p_v_module => 'get_person_number',
803 p_v_string =>' Entered Procedure get_person_number: The input parameters are ' ||
804 ' p_n_person_id : ' ||p_n_person_id
805 );
806 OPEN c_person_number (cp_n_person_id => p_n_person_id);
807 FETCH c_person_number INTO l_v_person_number;
808 CLOSE c_person_number;
809 log_to_fnd(p_v_module => ' get_base_id',
810 p_v_string => ' Return value of person number : '||l_v_person_number
811 );
812 RETURN l_v_person_number;
813 END get_person_number;
814
815 FUNCTION validate_cl_lar (p_rec_lorlar lorlar_recTyp) RETURN BOOLEAN AS
816 ------------------------------------------------------------------
817 --Created by : Sanil Madathil, Oracle IDC
818 --Date created: 09 November 2004
819 --
820 -- Purpose:
821 -- Invoked : from within create_file procedure
822 -- Function : Private function to return the person number for input person id
823 --
824 -- Parameters : p_rec_lorlar : IN parameter.
825 --
826 --
827 --Known limitations/enhancements and/or remarks:
828 --
829 --Change History:
830 --Who When What
831 ------------------------------------------------------------------
832 CURSOR c_nof_awd_disb (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
833 SELECT COUNT(awd.disb_num) tot_disb
834 FROM igf_aw_awd_disb_all awd
835 WHERE awd.award_id = cp_n_award_id
836 GROUP BY awd.award_id
837 HAVING COUNT(awd.disb_num) > 4;
838
839 l_n_disb_cnt NUMBER;
840
841 l_rec_lorlar lorlar_recTyp;
842
843 BEGIN
844 log_to_fnd(p_v_module => 'validate_cl_lar',
845 p_v_string => ' Entered function validate_cl_lar: The input parameters are ' ||
846 ' p_n_base_id : ' ||p_rec_lorlar.base_id ||
847 ' p_n_loan_id : ' ||p_rec_lorlar.loan_id
848 );
849
850 l_rec_lorlar := p_rec_lorlar;
851
852 -- Verify if the fund is dicontinued or not
853 IF l_rec_lorlar.discontinue_fund = 'Y' THEN
854 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
855 fnd_message.set_token('PARAMETER',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_TYPE'));
856 fnd_file.put_line(fnd_file.log, fnd_message.get);
857 RETURN FALSE;
858 END IF;
859
860 -- Loan Type is required for Change Loan Processing. Following are valid Loan Types -
861 -- AL=Alternative loan
862 -- PL=Federal PLUS loan
863 -- SF=Subsidized Federal Stafford loan
864 -- SU=Unsubsidized Federal Stafford loan
865 -- tsailaja -FA 163 -Bug 5337555
866 IF l_rec_lorlar.fed_fund_code NOT IN ('FLS','FLU','FLP','ALT','GPLUSFL') THEN
867 fnd_message.set_name('IGF','IGF_SL_CL_CHG_LOANT_REQD');
868 fnd_file.put_line(fnd_file.log, fnd_message.get);
869 fnd_file.new_line(fnd_file.log, 1);
870 RETURN FALSE;
871 END IF;
872
873 -- Validate whether loan is active
874 IF l_rec_lorlar.loan_active <> 'Y' THEN
875 fnd_message.set_name('IGS','IGF_SL_CL_LOAN_INACTIVE');
876 fnd_message.set_token('LOAN_NUMBER',l_rec_lorlar.loan_number);
877 fnd_file.put_line(fnd_file.log, fnd_message.get);
878 RETURN FALSE;
879 END IF;
880
881 -- validate the loan status
882 IF l_rec_lorlar.loan_status <> 'A' THEN
883 fnd_message.set_name('IGF','IGF_SL_CL_INV_LOAN_STAT');
884 fnd_file.put_line(fnd_file.log, fnd_message.get);
885 fnd_file.new_line(fnd_file.log, 1);
886 RETURN FALSE;
887 END IF;
888
889 -- Validate the loan change status
890 -- loan change status should be ready to send
891 IF (l_rec_lorlar.loan_chg_status <> 'G') THEN
892 fnd_message.set_name('IGF','IGF_SL_INV_LOAN_CHG_STATUS');
893 fnd_file.put_line(fnd_file.log, fnd_message.get);
894 fnd_file.new_line(fnd_file.log, 1);
895 RETURN FALSE;
896 END IF;
897
898 -- Change Record would be created only if
899 -- The version = CommonLine Release 4 Version Loan,
900 -- Loan Status = Accepted
901 -- Loan Record Status is Guaranteed or Accepted
902 -- Processing Type Code is GP or GO
903
904 -- vaildate the cl version code
905 IF (l_rec_lorlar.cl_version <> 'RELEASE-4') THEN
906 fnd_message.set_name('IGF','IGF_SL_CL_INV_CL_VER');
907 fnd_file.put_line(fnd_file.log, fnd_message.get);
908 fnd_file.new_line(fnd_file.log, 1);
909 RETURN FALSE;
910 END IF;
911
912 -- validate the loan processing type code
913 IF (l_rec_lorlar.prc_type_code NOT IN ('GO','GP')) THEN
914 fnd_message.set_name('IGF','IGF_SL_CL_RESP_INVLID_PRC');
915 fnd_file.put_line(fnd_file.log, fnd_message.get);
916 fnd_file.new_line(fnd_file.log, 1);
917 RETURN FALSE;
918 END IF;
919
920 -- validate the Loan Record Status
921 IF l_rec_lorlar.cl_rec_status NOT IN ('B','G') THEN
922 fnd_message.set_name('IGF','IGF_SL_CL_INV_LAR_REC_STAT');
923 fnd_file.put_line(fnd_file.log, fnd_message.get);
924 fnd_file.new_line(fnd_file.log, 1);
925 RETURN FALSE;
926 END IF;
927
928 -- For Release 4 processing, only 4 disbursements are allowed.
929 OPEN c_nof_awd_disb ( cp_n_award_id => l_rec_lorlar.award_id);
930 FETCH c_nof_awd_disb INTO l_n_disb_cnt;
931 IF c_nof_awd_disb%FOUND THEN
932 CLOSE c_nof_awd_disb;
933 fnd_message.set_name('IGF','IGF_SL_CL4_DISB_EXCEED');
934 fnd_file.put_line(fnd_file.log, fnd_message.get);
935 fnd_file.new_line(fnd_file.log, 1);
936 RETURN FALSE;
937 END IF;
938 CLOSE c_nof_awd_disb;
939
940 --bvisvana - Validate the School certification date > loan end period
941 IF l_rec_lorlar.sch_cert_date > l_rec_lorlar.loan_per_end_date THEN
942 fnd_message.set_name('IGF','IGF_SL_CL_CERT_AFTER_END');
943 fnd_file.put_line(fnd_file.log, fnd_message.get);
944 fnd_file.new_line(fnd_file.log, 1);
945 RETURN FALSE;
946 END IF;
947
948 --bvisvana - Bug # 4256897 - Validate the anticip_compl_date < loan end period
949 IF l_rec_lorlar.anticip_compl_date < l_rec_lorlar.loan_per_end_date THEN
950 fnd_message.set_name('IGF','IGF_SL_CHECK_COMPLDATE');
951 fnd_message.set_token('VALUE', ' ' || l_rec_lorlar.loan_per_end_date);
952 fnd_file.put_line(fnd_file.log, fnd_message.get);
953 fnd_file.new_line(fnd_file.log, 1);
954 RETURN FALSE;
955 END IF;
956
957 -- returning true as all the validations are successful
958 log_to_fnd(p_v_module => 'validate_cl_lar',
959 p_v_string => ' Returning true as all the validations are successful'
960 );
961 RETURN TRUE;
962
963 END validate_cl_lar;
964
965 PROCEDURE identify_clchsn_dtls
966 (
967 p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
968 p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
969 p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
970 p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
971 p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE
972 ) AS
973 ------------------------------------------------------------------
974 --Created by : Sanil Madathil, Oracle IDC
975 --Date created: 25 October 2004
976 --
977 -- Purpose:
978 -- Invoked : from within create_file procedure
979 -- Function : Private procedure to identify the clchsn_dtls records
980 -- for change send file creation
981 -- Parameters : p_v_cal_type : IN parameter. Required.
982 -- p_n_sequence_number : IN parameter. Required.
983 -- p_n_fund_id : IN parameter.
984 -- p_n_base_id : IN parameter.
985 -- p_n_loan_id : IN parameter.
986 --
987 --Known limitations/enhancements and/or remarks:
988 --
989 --Change History:
990 --Who When What
991 --bvisvana 18-Jul-2005 4132989 - Added sch_cert_date to the Cursor.
992 -- For validation of Sch cert date > loan end period
993 --mnade 27-Jan-2005 Added 'G' Filter on the c_lor_lar cursor to avoid picking up
994 -- all loans.
995 ------------------------------------------------------------------
996 CURSOR c_sl_clchsn_dtls (cp_v_loan_number igf_sl_loans_all.loan_number%TYPE) IS
997 SELECT chdt.ROWID row_id
998 ,chdt.*
999 FROM igf_sl_clchsn_dtls chdt
1000 WHERE chdt.loan_number_txt = cp_v_loan_number
1001 AND chdt.status_code ='R';
1002
1003 CURSOR c_lor_lar (cp_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1004 cp_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1005 cp_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1006 cp_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1007 cp_n_loan_id IN igf_sl_loans_all.loan_id%TYPE
1008 ) IS
1009 SELECT loans.row_id
1010 ,loans.loan_id
1011 ,loans.award_id
1012 ,loans.seq_num
1013 ,loans.loan_number
1014 ,loans.loan_per_begin_date
1015 ,loans.loan_per_end_date
1016 ,loans.loan_status
1017 ,loans.loan_status_date
1018 ,loans.loan_chg_status
1019 ,loans.loan_chg_status_date
1020 ,loans.active
1021 ,loans.active_date
1022 ,loans.borw_detrm_code
1023 ,loans.legacy_record_flag
1024 ,loans.external_loan_id_txt
1025 ,lor.anticip_compl_date
1026 ,lor.sch_cert_date
1027 ,lor.prc_type_code
1028 ,lor.cl_rec_status
1029 ,lor.relationship_cd
1030 ,awd.base_id
1031 ,fmast.ci_cal_type
1032 ,fmast.ci_sequence_number
1033 ,fmast.fund_id
1034 ,fmast.fund_code
1035 ,fmast.discontinue_fund
1036 ,fcat.fed_fund_code
1037 ,clset.cl_version
1038 FROM igf_sl_lor_all lor
1039 ,igf_sl_loans loans
1040 ,igf_aw_award_all awd
1041 ,igf_aw_fund_mast_all fmast
1042 ,igf_aw_fund_cat_all fcat
1043 ,igf_sl_cl_setup_all clset
1044 WHERE ((lor.loan_id = cp_n_loan_id) OR cp_n_loan_id IS NULL)
1045 AND loans.loan_id = lor.loan_id
1046 AND awd.award_id = loans.award_id
1047 AND ((awd.base_id = cp_n_base_id) OR cp_n_base_id IS NULL)
1048 AND ((awd.fund_id = cp_n_fund_id) OR cp_n_fund_id IS NULL)
1049 AND fmast.fund_id = awd.fund_id
1050 AND fmast.ci_cal_type = cp_v_cal_type
1051 AND fmast.ci_sequence_number = cp_n_sequence_number
1052 AND fcat.fund_code = fmast.fund_code
1053 AND clset.ci_cal_type = fmast.ci_cal_type
1054 AND clset.ci_sequence_number = fmast.ci_sequence_number
1055 AND clset.relationship_cd = lor.relationship_cd
1056 AND loans.loan_chg_status = 'G'
1057 AND EXISTS (SELECT '1'
1058 FROM igf_ap_fa_base_rec_all fabase
1059 WHERE fabase.base_id = awd.base_id
1060 AND fabase.ci_cal_type = fmast.ci_cal_type
1061 AND fabase.ci_sequence_number = fmast.ci_sequence_number
1062 );
1063
1064 rec_c_lor_lar c_lor_lar%ROWTYPE;
1065 rec_sl_loans igf_sl_loans%ROWTYPE;
1066 l_rec_lorlar lorlar_recTyp;
1067
1068 l_b_return_status BOOLEAN;
1069 l_v_message_name fnd_new_messages.message_name%TYPE;
1070 l_v_message_text fnd_new_messages.message_text%TYPE;
1071 l_t_message_tokens igf_sl_cl_chg_prc.token_tab%TYPE;
1072 e_loan_skip EXCEPTION;
1073 BEGIN
1074 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1075 p_v_string => ' Entered Procedure identify_clchsn_dtls: The input parameters are '||
1076 ' p_v_cal_type : ' ||p_v_cal_type ||
1077 ' p_n_sequence_number : ' ||p_n_sequence_number ||
1078 ' p_n_fund_id : ' ||p_n_fund_id ||
1079 ' p_n_base_id : ' ||p_n_base_id ||
1080 ' p_n_loan_id : ' ||p_n_loan_id
1081 );
1082 FOR rec_c_lor_lar IN c_lor_lar(cp_v_cal_type => p_v_cal_type ,
1083 cp_n_sequence_number => p_n_sequence_number,
1084 cp_n_fund_id => p_n_fund_id ,
1085 cp_n_base_id => p_n_base_id ,
1086 cp_n_loan_id => p_n_loan_id
1087 )
1088 LOOP
1089 BEGIN
1090 fnd_file.new_line(fnd_file.log,1);
1091 fnd_file.put(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PROCESSING'),40));
1092 fnd_file.new_line(fnd_file.log,1);
1093
1094 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER'),40),
1095 p_v_param_val => rec_c_lor_lar.loan_number
1096 );
1097 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_TYPE'),40),
1098 p_v_param_val => igf_aw_gen.lookup_desc('IGF_AW_FED_FUND',rec_c_lor_lar.fed_fund_code)
1099 );
1100
1101 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_STATUS'),40),
1102 p_v_param_val => igf_aw_gen.lookup_desc('IGF_SL_LOAN_STATUS',rec_c_lor_lar.loan_status)
1103 );
1104
1105 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),40),
1106 p_v_param_val => igf_gr_gen.get_per_num(rec_c_lor_lar.base_id)
1107 );
1108 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','FUND_CODE'),40),
1109 p_v_param_val => rec_c_lor_lar.fund_code
1110 );
1111
1112 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PROCESSING_TYPE'),40),
1113 p_v_param_val => igf_aw_gen.lookup_desc('IGF_SL_PRC_TYPE_CODE',rec_c_lor_lar.prc_type_code)
1114 );
1115
1116 log_parameters ( p_v_param_typ => RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','CL_VERSION'),40),
1117 p_v_param_val => igf_aw_gen.lookup_desc('IGF_SL_CL_VERSION',rec_c_lor_lar.cl_version)
1118 );
1119 fnd_file.new_line(fnd_file.log,1);
1120
1121 -- Assigning to record type variable
1122 l_rec_lorlar.cal_type := rec_c_lor_lar.ci_cal_type ;
1123 l_rec_lorlar.sequence_number := rec_c_lor_lar.ci_sequence_number ;
1124 l_rec_lorlar.fund_id := rec_c_lor_lar.fund_id ;
1125 l_rec_lorlar.discontinue_fund := rec_c_lor_lar.discontinue_fund ;
1126 l_rec_lorlar.fed_fund_code := rec_c_lor_lar.fed_fund_code ;
1127 l_rec_lorlar.base_id := rec_c_lor_lar.base_id ;
1128 l_rec_lorlar.award_id := rec_c_lor_lar.award_id ;
1129 l_rec_lorlar.loan_id := rec_c_lor_lar.loan_id ;
1130 l_rec_lorlar.loan_number := rec_c_lor_lar.loan_number ;
1131 l_rec_lorlar.loan_status := rec_c_lor_lar.loan_status ;
1132 l_rec_lorlar.loan_chg_status := rec_c_lor_lar.loan_chg_status ;
1133 l_rec_lorlar.loan_active := rec_c_lor_lar.active ;
1134 l_rec_lorlar.sch_cert_date := rec_c_lor_lar.sch_cert_date ;
1135 l_rec_lorlar.anticip_compl_date := rec_c_lor_lar.anticip_compl_date ;
1136 l_rec_lorlar.loan_per_begin_date := rec_c_lor_lar.loan_per_begin_date ;
1137 l_rec_lorlar.loan_per_end_date := rec_c_lor_lar.loan_per_end_date ;
1138
1139 l_rec_lorlar.cl_rec_status := rec_c_lor_lar.cl_rec_status ;
1140 l_rec_lorlar.prc_type_code := rec_c_lor_lar.prc_type_code ;
1141 l_rec_lorlar.cl_version := rec_c_lor_lar.cl_version ;
1142
1143 -- assigning to loan record type variable
1144 rec_sl_loans.row_id := rec_c_lor_lar.row_id ;
1145 rec_sl_loans.loan_id := rec_c_lor_lar.loan_id ;
1146 rec_sl_loans.award_id := rec_c_lor_lar.award_id ;
1147 rec_sl_loans.seq_num := rec_c_lor_lar.seq_num ;
1148 rec_sl_loans.loan_number := rec_c_lor_lar.loan_number ;
1149 rec_sl_loans.loan_per_begin_date := rec_c_lor_lar.loan_per_begin_date ;
1150 rec_sl_loans.loan_per_end_date := rec_c_lor_lar.loan_per_end_date ;
1151 rec_sl_loans.loan_status := rec_c_lor_lar.loan_status ;
1152 rec_sl_loans.loan_status_date := rec_c_lor_lar.loan_status_date ;
1153 rec_sl_loans.loan_chg_status_date := rec_c_lor_lar.loan_chg_status_date ;
1154 rec_sl_loans.active := rec_c_lor_lar.active ;
1155 rec_sl_loans.active_date := rec_c_lor_lar.active_date ;
1156 rec_sl_loans.borw_detrm_code := rec_c_lor_lar.borw_detrm_code ;
1157 rec_sl_loans.legacy_record_flag := rec_c_lor_lar.legacy_record_flag ;
1158 rec_sl_loans.external_loan_id_txt := rec_c_lor_lar.external_loan_id_txt ;
1159
1160
1161 -- invoke the validate routine to validate the loan
1162 l_b_return_status := validate_cl_lar (p_rec_lorlar => l_rec_lorlar) ;
1163 IF NOT (l_b_return_status) THEN
1164 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1165 p_v_string => ' validation of the Loan record failed for Loan number: ' ||rec_c_lor_lar.loan_number
1166 );
1167 rec_sl_loans.loan_chg_status := 'N' ;
1168 RAISE e_loan_skip;
1169 END IF;
1170 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1171 p_v_string => ' validation of the Loan record successful for Loan number: ' ||rec_c_lor_lar.loan_number
1172 );
1173 -- for each loan number loop thru the change send details table
1174 FOR rec_c_sl_clchsn_dtls IN c_sl_clchsn_dtls (cp_v_loan_number => rec_c_lor_lar.loan_number)
1175 LOOP
1176 l_b_return_status := TRUE;
1177 l_v_message_name := NULL;
1178 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1179 p_v_string => 'Validating the Change record for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id
1180 );
1181 -- invoke validation edits to validate the change record. The validation checks if
1182 -- all the required fields are populated or not for a change record
1183 igf_sl_cl_chg_prc.validate_chg (
1184 p_n_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id,
1185 p_b_return_status => l_b_return_status,
1186 p_v_message_name => l_v_message_name,
1187 p_t_message_tokens => l_t_message_tokens
1188 );
1189
1190 IF NOT(l_b_return_status) THEN
1191 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1192 p_v_string => ' validation of the Change record failed for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id
1193 );
1194 -- substring of the out bound parameter l_v_message_name is carried
1195 -- out since it can expect either IGS OR IGF message
1196 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
1197 igf_sl_cl_chg_prc.parse_tokens(
1198 p_t_message_tokens => l_t_message_tokens);
1199 /*
1200 FOR token_counter IN l_t_message_tokens.FIRST..l_t_message_tokens.LAST LOOP
1201 fnd_message.set_token(l_t_message_tokens(token_counter).token_name, l_t_message_tokens(token_counter).token_value);
1202 END LOOP;
1203 */
1204 l_v_message_text := fnd_message.get;
1205 fnd_file.put_line(fnd_file.log, l_v_message_text);
1206 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1207 p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
1208 );
1209 igf_sl_clchsn_dtls_pkg.update_row
1210 (
1211 x_rowid => rec_c_sl_clchsn_dtls.row_id ,
1212 x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
1213 x_award_id => rec_c_sl_clchsn_dtls.award_id ,
1214 x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
1215 x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
1216 x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
1217 x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
1218 x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
1219 x_status_code => 'N' ,
1220 x_status_date => rec_c_sl_clchsn_dtls.status_date ,
1221 x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
1222 x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
1223 x_new_value_txt => rec_c_sl_clchsn_dtls.new_value_txt ,
1224 x_old_date => rec_c_sl_clchsn_dtls.old_date ,
1225 x_new_date => rec_c_sl_clchsn_dtls.new_date ,
1226 x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
1227 x_new_amt => rec_c_sl_clchsn_dtls.new_amt ,
1228 x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
1229 x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
1230 x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
1231 x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
1232 x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
1233 x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
1234 x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
1235 x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
1236 x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
1237 x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
1238 x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
1239 x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
1240 x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
1241 x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
1242 x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
1243 x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
1244 x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
1245 x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
1246 x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
1247 x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
1248 x_validation_edit_txt => l_v_message_text ,
1249 x_send_record_txt => rec_c_sl_clchsn_dtls.send_record_txt
1250 );
1251 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1252 p_v_string => ' Updated the status of change send record to Not Ready to Send'
1253 );
1254 rec_sl_loans.loan_chg_status := 'N' ;
1255 RAISE e_loan_skip;
1256 END IF;
1257 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1258 p_v_string => ' validation of the Change record successful for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id
1259 );
1260 END LOOP;
1261
1262 -- Call to update row of loans table to update the status to intermediate status 'V'
1263 rec_sl_loans.loan_chg_status := 'V' ;
1264 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1265 p_v_string => ' Updating the loan_chg_status of : ' ||rec_c_lor_lar.loan_number|| 'to V'
1266 );
1267 proc_update_loan_rec(p_loan_rec => rec_sl_loans);
1268 EXCEPTION
1269 WHEN e_loan_skip THEN
1270 -- Call to update row of loans table to update the status to 'Not ready to Sent'
1271 log_to_fnd(p_v_module => 'identify_clchsn_dtls',
1272 p_v_string => ' update row of loans table to update the status to Not ready to Sent for : ' ||rec_c_lor_lar.loan_number
1273 );
1274 proc_update_loan_rec(p_loan_rec => rec_sl_loans);
1275 END;
1276 END LOOP;
1277 END identify_clchsn_dtls;
1278
1279
1280 PROCEDURE proc_update_loan_rec(p_loan_rec igf_sl_loans%ROWTYPE) AS
1281 ------------------------------------------------------------------
1282 --Created by : Sanil Madathil, Oracle IDC
1283 --Date created: 25 October 2004
1284 --
1285 -- Purpose:
1286 -- Invoked : from within identify_clchsn_dtls procedure
1287 -- Function : Private procedure to update the loans table
1288 -- to 'V' or 'N'
1289 -- Parameters : p_loan_rec : IN parameter. Required.
1290 --
1291 --
1292 --Known limitations/enhancements and/or remarks:
1293 --
1294 --Change History:
1295 --Who When What
1296 ------------------------------------------------------------------
1297 l_loan_rec igf_sl_loans%ROWTYPE;
1298 BEGIN
1299 log_to_fnd(p_v_module => 'proc_update_loan_rec',
1300 p_v_string => ' Entered Procedure proc_update_loan_rec: The input parameters are '||
1301 ' p_loan_rec.loan_id : ' ||p_loan_rec.loan_id ||
1302 ' p_loan_rec.loan_chg_status : ' ||p_loan_rec.loan_chg_status
1303 );
1304 l_loan_rec := p_loan_rec;
1305
1306 -- parameter x_called_from would not be passed as no change record processing is
1307 -- required while updating the loans table through this process
1308 igf_sl_loans_pkg.update_row (
1309 x_rowid => l_loan_rec.row_id ,
1310 x_loan_id => l_loan_rec.loan_id ,
1311 x_award_id => l_loan_rec.award_id ,
1312 x_seq_num => l_loan_rec.seq_num ,
1313 x_loan_number => l_loan_rec.loan_number ,
1314 x_loan_per_begin_date => l_loan_rec.loan_per_begin_date ,
1315 x_loan_per_end_date => l_loan_rec.loan_per_end_date ,
1316 x_loan_status => l_loan_rec.loan_status ,
1317 x_loan_status_date => l_loan_rec.loan_status_date ,
1318 x_loan_chg_status => l_loan_rec.loan_chg_status ,
1319 x_loan_chg_status_date => l_loan_rec.loan_chg_status_date ,
1320 x_active => l_loan_rec.active ,
1321 x_active_date => l_loan_rec.active_date ,
1322 x_borw_detrm_code => l_loan_rec.borw_detrm_code ,
1323 x_mode => 'R' ,
1324 x_legacy_record_flag => l_loan_rec.legacy_record_flag ,
1325 x_external_loan_id_txt => l_loan_rec.external_loan_id_txt
1326 );
1327 log_to_fnd(p_v_module => 'proc_update_loan_rec',
1328 p_v_string => ' Updated the loan record successfully ' ||
1329 ' l_loan_rec.loan_id : ' ||l_loan_rec.loan_id ||
1330 ' l_loan_rec.loan_chg_status : ' ||l_loan_rec.loan_chg_status
1331 );
1332 END proc_update_loan_rec;
1333
1334 PROCEDURE sub_create_file(
1335 errbuf OUT NOCOPY VARCHAR2,
1336 retcode OUT NOCOPY NUMBER,
1337 p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1338 p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1339 p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1340 p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1341 p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE,
1342 p_v_relationship_cd IN igf_sl_cl_recipient.relationship_cd%TYPE,
1343 p_v_media_type IN igf_lookups_view.lookup_code%TYPE,
1344 p_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE,
1345 p_v_sch_non_ed_branch IN igf_sl_school_codes_v.alternate_identifier%TYPE
1346 ) AS
1347 ------------------------------------------------------------------
1348 --Created by : Sanil Madathil, Oracle IDC
1349 --Date created: 25 October 2004
1350 --
1351 -- Purpose:
1352 -- Invoked : from within identify_clchsn_dtls procedure
1353 -- Function : public procedure to create the change send file
1354 -- Parameters : p_v_cal_type : IN parameter. Required.
1355 -- p_n_sequence_number : IN parameter. Required.
1356 -- p_n_fund_id : IN parameter.
1357 -- p_n_base_id : IN parameter.
1358 -- p_n_loan_id : IN parameter.
1359 -- p_v_relationship_cd : IN parameter. Required
1360 -- p_v_media_type : IN parameter. Required.
1361 -- p_v_school_id : IN parameter. Required.
1362 -- p_v_sch_non_ed_branch : IN parameter.
1363 --
1364 --Known limitations/enhancements and/or remarks:
1365 --
1366 --Change History:
1367 --Who When What
1368 --bvisvana 10-Apr-2006 Build FA 161.
1369 -- TBH Impact change done in igf_sl_lor_loc_pkg.update_row()
1370 --museshad 05-May-2005 Bug# 4346258
1371 -- Added the parameter 'base_id' in the call to the
1372 -- function get_cl_version(). The signature of
1373 -- this function has been changed so that it takes
1374 -- into account any overriding CL version for a
1375 -- specific Organization Unit in FFELP Setup override.--
1376 ------------------------------------------------------------------
1377 CURSOR c_recip_dtls( cp_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1378 cp_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1379 cp_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1380 cp_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1381 cp_n_loan_id IN igf_sl_loans_all.loan_id%TYPE,
1382 cp_v_relationship_cd IN igf_sl_cl_recipient.relationship_cd%TYPE,
1383 cp_c_loan_chg_status IN igf_sl_loans_all.loan_chg_status%TYPE,
1384 cp_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE
1385 ) IS
1386 SELECT recip.rcpt_id
1387 ,recip.lender_id
1388 ,recip.lend_non_ed_brc_id
1389 ,recip.guarantor_id
1390 ,recip.recipient_id
1391 ,recip.recipient_type
1392 ,recip.recip_non_ed_brc_id
1393 ,loans.loan_id
1394 ,loans.award_id
1395 ,loans.seq_num
1396 ,loans.loan_number
1397 ,loans.loan_per_begin_date
1398 ,loans.loan_per_end_date
1399 ,loans.loan_status
1400 ,loans.loan_status_date
1401 ,loans.loan_chg_status
1402 ,loans.loan_chg_status_date
1403 ,loans.active
1404 ,loans.active_date
1405 ,loans.borw_detrm_code
1406 ,loans.legacy_record_flag
1407 ,loans.external_loan_id_txt
1408 ,lor.origination_id
1409 ,lor.sch_cert_date
1410 ,lor.orig_status_flag
1411 ,lor.orig_batch_id
1412 ,lor.orig_batch_date
1413 ,lor.chg_batch_id
1414 ,lor.orig_ack_date
1415 ,lor.credit_override
1416 ,lor.credit_decision_date
1417 ,lor.req_serial_loan_code
1418 ,lor.act_serial_loan_code
1419 ,lor.pnote_delivery_code
1420 ,lor.pnote_status
1421 ,lor.pnote_status_date
1422 ,lor.pnote_id
1423 ,lor.pnote_print_ind
1424 ,lor.pnote_accept_amt
1425 ,lor.pnote_accept_date
1426 ,lor.unsub_elig_for_heal
1427 ,lor.disclosure_print_ind
1428 ,lor.orig_fee_perct
1429 ,lor.borw_confirm_ind
1430 ,lor.borw_interest_ind
1431 ,lor.borw_outstd_loan_code
1432 ,lor.unsub_elig_for_depnt
1433 ,lor.guarantee_amt
1434 ,lor.guarantee_date
1435 ,lor.guarnt_amt_redn_code
1436 ,lor.guarnt_status_code
1437 ,lor.guarnt_status_date
1438 ,lor.lend_apprv_denied_code
1439 ,lor.lend_apprv_denied_date
1440 ,lor.lend_status_code
1441 ,lor.lend_status_date
1442 ,lor.guarnt_adj_ind
1443 ,lor.grade_level_code
1444 ,lor.enrollment_code
1445 ,lor.anticip_compl_date
1446 ,lor.borw_lender_id
1447 ,lor.duns_borw_lender_id
1448 ,lor.duns_guarnt_id
1449 ,lor.prc_type_code
1450 ,lor.cl_seq_number
1451 ,lor.last_resort_lender
1452 ,lor.duns_lender_id
1453 ,lor.duns_recip_id
1454 ,lor.rec_type_ind
1455 ,lor.cl_loan_type
1456 ,lor.cl_rec_status
1457 ,lor.cl_rec_status_last_update
1458 ,lor.alt_prog_type_code
1459 ,lor.alt_appl_ver_code
1460 ,lor.mpn_confirm_code
1461 ,lor.resp_to_orig_code
1462 ,lor.appl_loan_phase_code
1463 ,lor.appl_loan_phase_code_chg
1464 ,lor.appl_send_error_codes
1465 ,lor.tot_outstd_stafford
1466 ,lor.tot_outstd_plus
1467 ,lor.alt_borw_tot_debt
1468 ,lor.act_interest_rate
1469 ,lor.service_type_code
1470 ,lor.rev_notice_of_guarnt
1471 ,lor.sch_refund_amt
1472 ,lor.sch_refund_date
1473 ,lor.uniq_layout_vend_code
1474 ,lor.uniq_layout_ident_code
1475 ,DECODE(fcat.fed_fund_code,'FLP',lor.p_person_id,
1476 'FLS',fabase.person_id,
1477 'FLU',fabase.person_id,
1478 'GPLUSFL',fabase.person_id,
1479 'ALT',DECODE(SIGN(lor.p_person_id-fabase.person_id),0, fabase.person_id,lor.p_person_id)) borrower_id
1480 ,lor.p_ssn_chg_date
1481 ,lor.p_dob_chg_date
1482 ,lor.p_permt_addr_chg_date
1483 ,lor.p_default_status
1484 ,lor.p_signature_code
1485 ,lor.p_signature_date
1486 ,lor.s_ssn_chg_date
1487 ,lor.s_dob_chg_date
1488 ,lor.s_permt_addr_chg_date
1489 ,lor.s_local_addr_chg_date
1490 ,lor.s_default_status
1491 ,lor.s_signature_code
1492 ,lor.pnote_batch_id
1493 ,lor.pnote_ack_date
1494 ,lor.pnote_mpn_ind
1495 ,lor.elec_mpn_ind
1496 ,lor.borr_sign_ind
1497 ,lor.stud_sign_ind
1498 ,lor.borr_credit_auth_code
1499 ,lor.relationship_cd
1500 ,lor.cps_trans_num
1501 ,lor.crdt_decision_status
1502 ,lor.note_message
1503 ,lor.book_loan_amt
1504 ,lor.book_loan_amt_date
1505 ,lor.pymt_servicer_amt
1506 ,lor.pymt_servicer_date
1507 ,lor.deferment_request_code
1508 ,lor.eft_authorization_code
1509 ,lor.requested_loan_amt
1510 ,lor.actual_record_type_code
1511 ,lor.reinstatement_amt
1512 ,lor.school_use_txt
1513 ,lor.lender_use_txt
1514 ,lor.guarantor_use_txt
1515 ,lor.fls_approved_amt
1516 ,lor.flu_approved_amt
1517 ,lor.flp_approved_amt
1518 ,lor.alt_approved_amt
1519 ,lor.loan_app_form_code
1520 ,lor.override_grade_level_code
1521 ,awd.base_id
1522 ,awd.accepted_amt loan_amt_accepted
1523 ,awd.award_date
1524 ,fmast.fund_code
1525 ,fcat.fed_fund_code
1526 ,fabase.person_id student_id
1527 ,TRUNC(fabase.coa_f) coa_f
1528 FROM igf_sl_lor_all lor
1529 ,igf_sl_loans_all loans
1530 ,igf_aw_award_all awd
1531 ,igf_aw_fund_mast_all fmast
1532 ,igf_aw_fund_cat_all fcat
1533 ,igf_sl_cl_recipient recip
1534 ,igf_ap_fa_base_rec_all fabase
1535 WHERE lor.relationship_cd = cp_v_relationship_cd
1536 AND ((lor.loan_id = cp_n_loan_id) OR cp_n_loan_id IS NULL)
1537 AND loans.loan_id = lor.loan_id
1538 AND loans.loan_chg_status = cp_c_loan_chg_status
1539 AND SUBSTR(loans.loan_number,1, 6) = SUBSTR(cp_v_school_id,1,6)
1540 AND awd.award_id = loans.award_id
1541 AND ((awd.base_id = cp_n_base_id) OR cp_n_base_id IS NULL)
1542 AND ((awd.fund_id = cp_n_fund_id) OR cp_n_fund_id IS NULL)
1543 AND fmast.fund_id = awd.fund_id
1544 AND fmast.ci_cal_type = cp_v_cal_type
1545 AND fmast.ci_sequence_number = cp_n_sequence_number
1546 AND fcat.fund_code = fmast.fund_code
1547 AND recip.relationship_cd = lor.relationship_cd
1548 AND fabase.base_id = awd.base_id
1549 AND fabase.ci_cal_type = fmast.ci_cal_type
1550 AND fabase.ci_sequence_number = fmast.ci_sequence_number
1551 ORDER BY borrower_id;
1552
1553
1554 CURSOR c_school_opeid(cp_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE) IS
1555 SELECT meaning
1556 FROM igf_lookups_view
1557 WHERE lookup_type = 'IGF_AP_SCHOOL_OPEID'
1558 AND lookup_code = cp_v_school_id;
1559
1560 CURSOR c_cl_recipient_dtls (cp_v_relationship_cd IN igf_sl_cl_recipient.relationship_cd%TYPE)IS
1561 SELECT recipient_id
1562 ,recipient_type
1563 ,recip_non_ed_brc_id
1564 FROM igf_sl_cl_recipient
1565 WHERE relationship_cd = cp_v_relationship_cd;
1566
1567 CURSOR c_sl_lender (cp_n_recipient_id IN igf_sl_cl_recipient.recipient_id%TYPE) IS
1568 SELECT lender_id
1569 ,description
1570 FROM igf_sl_lender
1571 WHERE lender_id = cp_n_recipient_id
1572 AND enabled = 'Y';
1573
1574 CURSOR c_sl_guarantor (cp_n_recipient_id IN igf_sl_cl_recipient.recipient_id%TYPE) IS
1575 SELECT guarantor_id
1576 ,description
1577 FROM igf_sl_guarantor
1578 WHERE guarantor_id = cp_n_recipient_id
1579 AND enabled = 'Y';
1580
1581
1582 CURSOR c_sl_servicer (cp_n_recipient_id IN igf_sl_cl_recipient.recipient_id%TYPE) IS
1583 SELECT servicer_id
1584 ,description
1585 FROM igf_sl_servicer
1586 WHERE servicer_id = cp_n_recipient_id
1587 AND enabled = 'Y';
1588
1589 CURSOR c_sl_clchsn_dtls (cp_v_loan_number IN igf_sl_loans_all.loan_number%TYPE) IS
1590 SELECT chdt.ROWID row_id
1591 ,chdt.*
1592 FROM igf_sl_clchsn_dtls chdt
1593 WHERE chdt.loan_number_txt = cp_v_loan_number
1594 AND chdt.status_code ='R'
1595 ORDER BY change_record_type_txt,disbursement_number;
1596
1597 CURSOR c_aw_awd_disb (cp_n_award_id IN igf_aw_award_all.award_id%TYPE) IS
1598 SELECT adisb.disb_date
1599 ,NVL(adisb.disb_accepted_amt,0) disb_accepted_amt
1600 ,adisb.hold_rel_ind
1601 ,adisb.disb_num
1602 FROM igf_aw_awd_disb_all adisb
1603 WHERE adisb.award_id = cp_n_award_id
1604 ORDER BY adisb.disb_num;
1605
1606 CURSOR c_aw_awd_disb2 (cp_n_award_id IN igf_aw_award_all.award_id%TYPE,
1607 cp_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE
1608 ) IS
1609 SELECT adisb.disb_date
1610 ,NVL(adisb.disb_accepted_amt,0) disb_accepted_amt
1611 ,adisb.hold_rel_ind
1612 ,adisb.disb_num
1613 ,adisb.fee_1
1614 ,adisb.fee_2
1615 ,adisb.fee_paid_1
1616 ,adisb.fee_paid_2
1617 FROM igf_aw_awd_disb_all adisb
1618 WHERE adisb.award_id = cp_n_award_id
1619 AND adisb.disb_num = cp_n_disb_num
1620 ORDER BY adisb.disb_num;
1621
1622 rec_c_aw_awd_disb2 c_aw_awd_disb2%ROWTYPE;
1623
1624 CURSOR cur_get_fin_aid(cp_n_award_id IN igf_aw_award_all.award_id%TYPE ,
1625 cp_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1626 cp_awd_status_1 IN igf_aw_award.award_status%TYPE,
1627 cp_awd_status_2 IN igf_aw_award.award_status%TYPE
1628 ) IS
1629 SELECT TRUNC(SUM(NVL(NVL(awd.accepted_amt,awd.offered_amt),0))) etsimated_fin
1630 FROM igf_aw_award_all awd
1631 WHERE awd.base_id = cp_n_base_id
1632 AND awd.award_id <> cp_n_award_id
1633 AND (awd.award_status IN (cp_awd_status_1,cp_awd_status_2));
1634
1635 CURSOR c_sl_lor_loc (cp_n_loan_id igf_sl_loans_all.loan_id%TYPE ,
1636 cp_n_origination_id igf_sl_lor_all.origination_id%TYPE
1637 ) IS
1638 SELECT lorloc.*, lorloc.ROWID row_id
1639 FROM igf_sl_lor_loc_all lorloc
1640 WHERE loan_id = cp_n_loan_id
1641 AND origination_id = cp_n_origination_id;
1642
1643 rec_c_sl_lor_loc c_sl_lor_loc%ROWTYPE;
1644
1645 -- input variables for this procedure
1646 l_v_cal_type igs_ca_inst_all.cal_type%TYPE;
1647 l_n_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1648 l_n_loan_id igf_sl_loans_all.loan_id%TYPE;
1649 l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
1650 l_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
1651 l_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE;
1652 l_v_media_type igf_lookups_view.lookup_code%TYPE;
1653 l_v_sch_non_ed_branch igf_sl_school_codes_v.alternate_identifier%TYPE;
1654 l_v_relationship_cd igf_sl_cl_recipient.relationship_cd%TYPE;
1655
1656 l_v_alt_code igs_ca_inst_all.alternate_code%TYPE;
1657
1658 -- variables for change send header record
1659 l_v_chg_header_rec VARCHAR2(1000);
1660 l_v_file_ident_code igf_sl_cl_file_type.file_ident_code%TYPE;
1661 l_v_file_ident_name igf_sl_cl_file_type.file_ident_name%TYPE;
1662 l_v_file_crea_date VARCHAR2(8);
1663 l_v_file_crea_time VARCHAR2(6);
1664 l_v_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
1665 l_v_source_name igf_ap_school_opeid_v.meaning%TYPE;
1666 l_n_recipient_id igf_sl_cl_recipient.recipient_id%TYPE;
1667 l_v_recipient_name igf_sl_servicer.description%TYPE;
1668 l_v_recipient_type igf_sl_cl_recipient.recipient_type%TYPE;
1669 l_v_recip_non_edbrc_id igf_sl_cl_recipient.recip_non_ed_brc_id%TYPE;
1670 l_n_lender_id igf_sl_lender.lender_id%TYPE;
1671 l_n_guarantor_id igf_sl_guarantor.guarantor_id%TYPE;
1672 l_n_servicer_id igf_sl_servicer.servicer_id%TYPE;
1673 l_v_filler_2_char VARCHAR2(2);
1674 l_v_batch_id igf_sl_cl_batch.batch_id%TYPE;
1675 l_v_rowid ROWID;
1676 l_n_cbth_id igf_sl_cl_batch.cbth_id%TYPE;
1677
1678 -- variables for trailer
1679 l_v_chg_trailer_rec VARCHAR2(1000);
1680 l_n_1cntr NUMBER;
1681 l_n_2cntr NUMBER;
1682
1683 -- variables for (@1-02)
1684 l_n_borrower_id igf_ap_fa_base_rec_all.person_id%TYPE;
1685 l_v_chg_01_2_rec VARCHAR2(2000);
1686 c_borrower_dtls igf_sl_gen.person_dtl_cur;
1687 rec_borrower_dtls igf_sl_gen.person_dtl_rec;
1688 l_v_filler_3_char VARCHAR2(3);
1689
1690 -- variables for (@1-07)
1691 l_v_chg_01_7_rec VARCHAR2(4000);
1692 l_c_01_07_flg VARCHAR2(1);
1693 l_v_loan_type igf_aw_fund_cat_all.fed_fund_code%TYPE;
1694 l_v_alt_prog_type_code igf_sl_lor_all.alt_prog_type_code%TYPE;
1695 c_student_dtls igf_sl_gen.person_dtl_cur;
1696 rec_student_dtls igf_sl_gen.person_dtl_rec;
1697 l_d_revised_per_begin_dt igf_sl_loans_all.loan_per_begin_date%TYPE;
1698 l_d_revised_per_end_dt igf_sl_loans_all.loan_per_end_date%TYPE;
1699 l_d_old_per_begin_dt igf_sl_loans_all.loan_per_begin_date%TYPE;
1700 l_d_old_per_end_dt igf_sl_loans_all.loan_per_end_date%TYPE;
1701 l_v_ssn igf_ap_isir_ints_all.current_ssn_txt%TYPE;
1702
1703 -- variables for (@1-08)
1704 l_v_chg_01_8_rec VARCHAR2(4000);
1705 l_c_01_08_flg VARCHAR2(1);
1706 l_n_reinstated_loan_amt NUMBER;
1707 l_d_cancellation_date DATE;
1708
1709 -- variables for (@1-09)
1710 l_v_chg_01_9_rec VARCHAR2(4000);
1711 l_c_01_09_flg VARCHAR2(1);
1712 l_n_disb_number igf_aw_awd_disb_all.disb_num%TYPE;
1713
1714 TYPE chsn_09dtl_rectyp IS RECORD (
1715 disb_number igf_aw_awd_disb_all.disb_num%TYPE ,
1716 disb_date igf_aw_awd_disb_all.disb_date%TYPE ,
1717 disb_cancel_date igf_sl_clchsn_dtls.disbursement_cancel_date%TYPE ,
1718 disb_cancel_amt igf_sl_clchsn_dtls.disbursement_cancel_amt%TYPE ,
1719 disb_hold_rel_ind igf_aw_awd_disb_all.hold_rel_ind%TYPE ,
1720 revised_disb_date igf_sl_clchsn_dtls.disbursement_revised_date%TYPE,
1721 revised_disb_amt igf_sl_clchsn_dtls.disbursement_revised_amt%TYPE ,
1722 reinstate_ind igf_sl_clchsn_dtls.disbursement_reinst_code%TYPE
1723 );
1724 l_rec_chsn_09dtl chsn_09dtl_rectyp;
1725
1726 TYPE tab_rec_09_dtl IS TABLE OF l_rec_chsn_09dtl%TYPE INDEX BY BINARY_INTEGER;
1727 v_tab_rec_09_dtl tab_rec_09_dtl;
1728 l_n_ctr_09 NUMBER;
1729
1730 -- variables for (@1-10)
1731 l_v_chg_01_10_rec VARCHAR2(4000);
1732 l_c_01_10_flg VARCHAR2(1);
1733 l_n_post_disb_number igf_aw_awd_disb_all.disb_num%TYPE;
1734
1735 TYPE chsn_10dtl_rectyp IS RECORD (
1736 disb_number igf_aw_awd_disb_all.disb_num%TYPE ,
1737 disb_date igf_aw_awd_disb_all.disb_date%TYPE ,
1738 disb_cancel_date igf_sl_clchsn_dtls.disbursement_cancel_date%TYPE ,
1739 disb_cancel_amt igf_sl_clchsn_dtls.disbursement_cancel_amt%TYPE ,
1740 disb_consummation_ind VARCHAR2(30) ,
1741 actual_return_amt igf_aw_awd_disb_all.disb_accepted_amt%TYPE ,
1742 fund_return_mthd_code igf_aw_awd_disb_all.fund_return_mthd_code%TYPE ,
1743 fund_reissue_code igf_sl_clchsn_dtls.disbursement_reissue_code%TYPE,
1744 revised_disb_date igf_sl_clchsn_dtls.disbursement_revised_date%TYPE,
1745 revised_disb_amt igf_sl_clchsn_dtls.disbursement_revised_amt%TYPE ,
1746 reinstate_ind igf_sl_clchsn_dtls.disbursement_reinst_code%TYPE
1747 );
1748 l_rec_chsn_10dtl chsn_10dtl_rectyp;
1749
1750 TYPE tab_rec_10_dtl IS TABLE OF l_rec_chsn_10dtl%TYPE INDEX BY BINARY_INTEGER;
1751 v_tab_rec_10_dtl tab_rec_10_dtl;
1752 l_n_ctr_10 NUMBER;
1753 l_n_actual_return_amt NUMBER;
1754
1755 -- variables for (@1-24)
1756 l_v_chg_01_24_rec VARCHAR2(4000);
1757 l_v_disb_rec VARCHAR2(1000);
1758 l_c_01_24_flg VARCHAR2(1);
1759 l_n_loan_amt_increase igf_aw_awd_disb_all.disb_accepted_amt%TYPE;
1760 l_n_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
1761 l_n_efc igf_ap_fa_base_rec_all.efc_f%TYPE;
1762 l_n_est_fin igf_aw_award_all.accepted_amt%TYPE;
1763 l_n_pell_efc NUMBER;
1764 l_n_efc_f NUMBER;
1765 l_n_ctr_disb NUMBER;
1766 BEGIN
1767
1768 igf_aw_gen.set_org_id(NULL);
1769 retcode := 0 ;
1770 SAVEPOINT sub_create_file;
1771 log_to_fnd(p_v_module => 'sub_create_file',
1772 p_v_string => ' Entered Procedure sub_create_file: The input parameters are '||
1773 ' p_v_cal_type : ' ||p_v_cal_type ||
1774 ' p_n_sequence_number : ' ||p_n_sequence_number ||
1775 ' p_n_fund_id : ' ||p_n_fund_id ||
1776 ' p_n_base_id : ' ||p_n_base_id ||
1777 ' p_n_loan_id : ' ||p_n_loan_id ||
1778 ' p_v_relationship_cd : ' ||p_v_relationship_cd ||
1779 ' p_v_media_type : ' ||p_v_media_type ||
1780 ' p_v_school_id : ' ||p_v_school_id ||
1781 ' p_v_sch_non_ed_branch : ' ||p_v_sch_non_ed_branch
1782 );
1783
1784 -- assigning the passed input parameters to local variables
1785 l_v_cal_type := p_v_cal_type;
1786 l_n_sequence_number := p_n_sequence_number;
1787 l_n_fund_id := p_n_fund_id;
1788 l_n_base_id := p_n_base_id;
1789 l_n_loan_id := p_n_loan_id;
1790 l_v_relationship_cd := p_v_relationship_cd;
1791 l_v_alt_code := igf_gr_gen.get_alt_code(l_v_cal_type,l_n_sequence_number);
1792 l_v_media_type := p_v_media_type ;
1793 l_v_school_id := p_v_school_id ;
1794 l_v_sch_non_ed_branch := p_v_sch_non_ed_branch;
1795
1796
1797 l_v_file_crea_date := TO_CHAR(SYSDATE,'YYYYMMDD');
1798 l_v_file_crea_time := TO_CHAR(SYSDATE,'HH24MISS');
1799 -- obtain the common line release version based on the award year and relationship code passed as parameter
1800 -- museshad(Bug# 4346258) - Added the parameter p_base_id due to change in the
1801 -- signature of the function 'get_cl_version()'
1802 l_v_cl_version := igf_sl_gen.get_cl_version(
1803 p_ci_cal_type => l_v_cal_type ,
1804 p_ci_seq_num => l_n_sequence_number ,
1805 p_relationship_cd => l_v_relationship_cd ,
1806 p_base_id => l_n_base_id
1807 );
1808 l_v_file_ident_code := igf_sl_gen.get_cl_file_type(l_v_cl_version, 'CL_CHANGE_TRANS', 'FILE-IDENT-CODE');
1809 l_v_file_ident_name := igf_sl_gen.get_cl_file_type(l_v_cl_version, 'CL_CHANGE_TRANS', 'FILE-IDENT-NAME');
1810 -- get the source name
1811 OPEN c_school_opeid(cp_v_school_id => l_v_school_id);
1812 FETCH c_school_opeid INTO l_v_source_name;
1813 CLOSE c_school_opeid ;
1814
1815 -- get the recepient name
1816
1817 OPEN c_cl_recipient_dtls (cp_v_relationship_cd => l_v_relationship_cd);
1818 FETCH c_cl_recipient_dtls INTO l_n_recipient_id
1819 ,l_v_recipient_type
1820 ,l_v_recip_non_edbrc_id;
1821 CLOSE c_cl_recipient_dtls ;
1822
1823 log_to_fnd(p_v_module => 'sub_create_file',
1824 p_v_string => ' Recipient id : ' ||l_n_recipient_id ||
1825 ' Recipient type : ' ||l_v_recipient_type ||
1826 ' Recipient Non EdBrc Id : ' ||l_v_recip_non_edbrc_id
1827 );
1828
1829 -- if recepient is guarantor
1830 IF l_v_recipient_type = 'GUARN' THEN
1831
1832 OPEN c_sl_guarantor (cp_n_recipient_id => l_n_recipient_id);
1833 FETCH c_sl_guarantor INTO l_n_guarantor_id,l_v_recipient_name;
1834 CLOSE c_sl_guarantor ;
1835
1836 -- if recepient is lender
1837 ELSIF l_v_recipient_type = 'LND' THEN
1838
1839 OPEN c_sl_lender (cp_n_recipient_id => l_n_recipient_id);
1840 FETCH c_sl_lender INTO l_n_lender_id,l_v_recipient_name;
1841 CLOSE c_sl_lender ;
1842
1843 -- if recepient is servicer
1844 ELSIF l_v_recipient_type = 'SRVC' THEN
1845
1846 OPEN c_sl_servicer (cp_n_recipient_id => l_n_recipient_id);
1847 FETCH c_sl_servicer INTO l_n_servicer_id,l_v_recipient_name;
1848 CLOSE c_sl_servicer ;
1849
1850 END IF;
1851
1852 -- deriving the unique batch id
1853
1854 l_v_batch_id := NULL;
1855 l_v_batch_id := '@A'
1856 ||RPAD(NVL(l_n_recipient_id,' '),6)
1857 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),2)
1858 ||TO_CHAR(SYSDATE,'YY')
1859 ||RPAD(l_v_school_id,8)
1860 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4)
1861 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS');
1862 log_to_fnd(p_v_module => 'sub_create_file',
1863 p_v_string => ' Derived Unique Batchid : ' ||l_v_batch_id
1864 );
1865 -- insert into cl batch table
1866 log_to_fnd(p_v_module => 'sub_create_file',
1867 p_v_string => ' Inserting into igf_sl_cl_batch table '
1868 );
1869 l_v_rowid := NULL;
1870 igf_sl_cl_batch_pkg.insert_row (
1871 x_mode => 'R' ,
1872 x_rowid => l_v_rowid ,
1873 x_cbth_id => l_n_cbth_id ,
1874 x_batch_id => l_v_batch_id ,
1875 x_file_creation_date => TRUNC(SYSDATE) ,
1876 x_file_trans_date => TRUNC(SYSDATE) ,
1877 x_file_ident_code => RPAD(l_v_file_ident_code,5,' '),
1878 x_recipient_id => l_n_recipient_id ,
1879 x_recip_non_ed_brc_id => l_v_recip_non_edbrc_id ,
1880 x_source_id => l_v_school_id ,
1881 x_source_non_ed_brc_id => l_v_sch_non_ed_branch ,
1882 x_send_resp => 'S' ,
1883 x_record_count_num => NULL ,
1884 x_total_net_disb_amt => NULL ,
1885 x_total_net_eft_amt => NULL ,
1886 x_total_net_non_eft_amt => NULL ,
1887 x_total_reissue_amt => NULL ,
1888 x_total_cancel_amt => NULL ,
1889 x_total_deficit_amt => NULL ,
1890 x_total_net_cancel_amt => NULL ,
1891 x_total_net_out_cancel_amt => NULL
1892 );
1893 log_to_fnd(p_v_module => 'sub_create_file',
1894 p_v_string => ' Inserted successfully into igf_sl_cl_batch table '
1895 );
1896
1897 -- defining values for filler characters variables
1898 l_v_filler_2_char := RPAD(' ',2,' ');
1899 l_v_filler_3_char := RPAD(' ',3,' ');
1900
1901 -- constructing change send header record
1902 log_to_fnd(p_v_module => 'sub_create_file',
1903 p_v_string => ' constructing change send header record '
1904 );
1905 l_v_chg_header_rec := '@H'
1906 ||RPAD('IGS',4,' ')
1907 ||'1157'
1908 ||RPAD(' ',12,' ')
1909 ||l_v_file_crea_date
1910 ||l_v_file_crea_time
1911 ||l_v_file_crea_date
1912 ||l_v_file_crea_time
1913 ||RPAD(l_v_file_ident_name,19,' ')
1914 ||RPAD(l_v_file_ident_code,5,' ')
1915 ||RPAD(NVL(l_v_source_name,' '),32,' ')
1916 ||RPAD(l_v_school_id,8,' ')
1917 ||l_v_filler_2_char
1918 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
1919 ||'S'
1920 ||RPAD(NVL(l_v_recipient_name,' '),32,' ')
1921 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
1922 ||l_v_filler_2_char
1923 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
1924 ||l_v_media_type
1925 ||RPAD(' ',9,' ')
1926 ||RPAD(' ',9,' ')
1927 ||RPAD(' ',293,' ')
1928 ||'*';
1929 l_v_chg_header_rec := UPPER(l_v_chg_header_rec);
1930 -- writing the change send header record on to output file
1931 fnd_file.put_line(fnd_file.output,l_v_chg_header_rec);
1932 log_to_fnd(p_v_module => 'sub_create_file',
1933 p_v_string => ' change send header record '||l_v_chg_header_rec
1934 );
1935 -- initializing the record counters for both @1 and @2 change send reccords for RELEASE-4
1936 l_n_1cntr := 0;
1937 l_n_2cntr := 0;
1938
1939 -- initializing the borrower id to a dummy value
1940 l_n_borrower_id := -999;
1941
1942 FOR rec_c_recip_dtls IN c_recip_dtls(
1943 cp_v_cal_type => l_v_cal_type ,
1944 cp_n_sequence_number => l_n_sequence_number ,
1945 cp_n_fund_id => l_n_fund_id ,
1946 cp_n_base_id => l_n_base_id ,
1947 cp_n_loan_id => l_n_loan_id ,
1948 cp_v_relationship_cd => l_v_relationship_cd ,
1949 cp_c_loan_chg_status => 'V' ,
1950 cp_v_school_id => l_v_school_id
1951 )
1952 LOOP
1953 -- derived SSN value is set to NULL initially
1954 l_v_ssn := NULL;
1955 --One Borrower (@1-02) Detail Record in the file for each borrower
1956 IF (l_n_borrower_id <> rec_c_recip_dtls.borrower_id) THEN
1957 l_v_chg_01_2_rec := NULL;
1958 l_n_borrower_id := rec_c_recip_dtls.borrower_id;
1959 -- get the borrower details
1960 igf_sl_gen.get_person_details(rec_c_recip_dtls.borrower_id,c_borrower_dtls);
1961 FETCH c_borrower_dtls INTO rec_borrower_dtls;
1962 CLOSE c_borrower_dtls;
1963 -- constructing change send @1-02 record
1964 log_to_fnd(p_v_module => 'sub_create_file',
1965 p_v_string => ' constructing change send @1-02 record '
1966 );
1967 l_v_chg_01_2_rec := '@1'
1968 ||'02'
1969 ||RPAD(NVL(rec_borrower_dtls.p_ssn,' '),9,' ')
1970 ||RPAD(l_v_school_id,8,' ')
1971 ||l_v_filler_3_char
1972 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
1973 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
1974 ||l_v_filler_3_char
1975 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
1976 ||RPAD(' ',12,' ')
1977 ||RPAD(NVL(rec_borrower_dtls.p_last_name,' '),35,' ')
1978 ||RPAD(NVL(rec_borrower_dtls.p_first_name,' '),12,' ')
1979 ||RPAD(NVL(rec_borrower_dtls.p_middle_name,' '),1,' ')
1980 ||LPAD(NVL(TO_CHAR(rec_borrower_dtls.p_date_of_birth,'YYYYMMDD'),'0'),8,'0')
1981 ||' '
1982 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS')||RPAD('0',6,'0')
1983 ||RPAD(' ',9,' ')
1984 ||RPAD(' ',9,' ')
1985 ||RPAD(' ',183,' ')
1986 ||RPAD(' ',23,' ')
1987 ||RPAD(' ',20,' ')
1988 ||RPAD(' ',23,' ')
1989 ||RPAD(' ',80,' ')
1990 ||'*';
1991 l_v_chg_01_2_rec := UPPER(l_v_chg_01_2_rec);
1992 -- writing the change send @1-02 record on to output file
1993 fnd_file.put_line(fnd_file.output,l_v_chg_01_2_rec);
1994 l_n_1cntr := l_n_1cntr + 1;
1995 log_to_fnd(p_v_module => 'sub_create_file',
1996 p_v_string => ' change send @1-02 record '||l_v_chg_01_2_rec
1997 );
1998 END IF;
1999
2000 -- get the student details
2001 igf_sl_gen.get_person_details(rec_c_recip_dtls.student_id,c_student_dtls);
2002 FETCH c_student_dtls INTO rec_student_dtls;
2003 CLOSE c_student_dtls;
2004
2005 -- derivation of loan type code
2006 IF rec_c_recip_dtls.fed_fund_code = 'ALT' THEN
2007 l_v_loan_type := 'AL';
2008 l_v_alt_prog_type_code := rec_c_recip_dtls.alt_prog_type_code;
2009 IF (rec_c_recip_dtls.student_id <> rec_c_recip_dtls.borrower_id) THEN
2010 l_v_ssn := rec_borrower_dtls.p_ssn;
2011 ELSIF (rec_c_recip_dtls.student_id = rec_c_recip_dtls.borrower_id) THEN
2012 l_v_ssn := rec_student_dtls.p_ssn;
2013 END IF;
2014 ELSIF rec_c_recip_dtls.fed_fund_code = 'FLP' THEN
2015 l_v_loan_type := 'PL';
2016 l_v_alt_prog_type_code := ' ';
2017 l_v_ssn := rec_student_dtls.p_ssn;
2018 ELSIF rec_c_recip_dtls.fed_fund_code = 'FLS' THEN
2019 l_v_loan_type := 'SF';
2020 l_v_alt_prog_type_code := ' ';
2021 ELSIF rec_c_recip_dtls.fed_fund_code = 'FLU' THEN
2022 l_v_loan_type := 'SU';
2023 l_v_alt_prog_type_code := ' ';
2024 ELSIF rec_c_recip_dtls.fed_fund_code = 'GPLUSFL' THEN
2025 l_v_loan_type := 'GB';
2026 l_v_ssn := rec_student_dtls.p_ssn;
2027 END IF;
2028 log_to_fnd(p_v_module => 'sub_create_file',
2029 p_v_string => ' derived loan type code. loan type code : '||l_v_loan_type
2030 );
2031 -- obtain the efc
2032 igf_aw_packng_subfns.get_fed_efc(
2033 l_base_id => rec_c_recip_dtls.base_id,
2034 l_awd_prd_code => NULL,
2035 l_efc_f => l_n_efc_f,
2036 l_pell_efc => l_n_pell_efc,
2037 l_efc_ay => l_n_efc
2038 );
2039 l_n_efc := TRUNC(l_n_efc);
2040 l_n_coa := rec_c_recip_dtls.coa_f;
2041 log_to_fnd(p_v_module => 'sub_create_file',
2042 p_v_string => ' Obtained efc and coa '||
2043 ' l_n_efc : '||l_n_efc ||
2044 ' l_n_coa : '||l_n_coa
2045 );
2046 -- obtaining the estimated financial aid for the base id
2047 OPEN cur_get_fin_aid (
2048 cp_n_award_id => rec_c_recip_dtls.award_id,
2049 cp_n_base_id => rec_c_recip_dtls.base_id ,
2050 cp_awd_status_1 => 'OFFERED' ,
2051 cp_awd_status_2 => 'ACCEPTED'
2052 );
2053 FETCH cur_get_fin_aid INTO l_n_est_fin;
2054 CLOSE cur_get_fin_aid ;
2055 log_to_fnd(p_v_module => 'sub_create_file',
2056 p_v_string => ' Estimated financial aid for the base id : '||l_n_est_fin
2057 );
2058
2059 -- assigning the default loan periods from loan record
2060 l_d_old_per_begin_dt := rec_c_recip_dtls.loan_per_begin_date ;
2061 l_d_revised_per_begin_dt := rec_c_recip_dtls.loan_per_begin_date ;
2062 l_d_old_per_end_dt := rec_c_recip_dtls.loan_per_end_date ;
2063 l_d_revised_per_end_dt := rec_c_recip_dtls.loan_per_end_date ;
2064 l_n_reinstated_loan_amt := 0 ;
2065 l_d_cancellation_date := NULL;
2066 l_c_01_07_flg := 'N' ;
2067 l_c_01_08_flg := 'N' ;
2068 l_c_01_09_flg := 'N' ;
2069 l_c_01_10_flg := 'N' ;
2070 l_c_01_24_flg := 'N' ;
2071 l_v_chg_01_7_rec := NULL;
2072 l_v_chg_01_8_rec := NULL;
2073 l_v_chg_01_9_rec := NULL;
2074 l_v_chg_01_10_rec := NULL;
2075 l_v_chg_01_24_rec := NULL;
2076 l_n_disb_number := -1 ;
2077 l_n_ctr_09 := 0 ;
2078 l_n_ctr_10 := 0 ;
2079 l_n_ctr_disb := 0 ;
2080 l_n_post_disb_number := -1 ;
2081 l_n_loan_amt_increase := NULL;
2082
2083 FOR rec_c_sl_clchsn_dtls IN c_sl_clchsn_dtls (cp_v_loan_number => rec_c_recip_dtls.loan_number)
2084 LOOP
2085 --There must be one Loan Period Change (@1-07) Detail Record in the file for each loan
2086 --if revising loan period begin and end dates, student's grade level, and/or student's
2087 --anticipated completion dates. The @1-07 Detail Record can be submitted pre- or postdisbursement.
2088 IF rec_c_sl_clchsn_dtls.change_record_type_txt = '07' THEN
2089 l_c_01_07_flg := 'Y';
2090 log_to_fnd(p_v_module => 'sub_create_file',
2091 p_v_string => ' (@1-07) Detail Record found '
2092 );
2093 IF rec_c_sl_clchsn_dtls.change_field_code = 'LOAN_PER_BEGIN_DT' AND
2094 rec_c_sl_clchsn_dtls.change_code_txt = 'A' THEN
2095 l_d_old_per_begin_dt := rec_c_sl_clchsn_dtls.old_date;
2096 l_d_revised_per_begin_dt := rec_c_sl_clchsn_dtls.new_date;
2097 ELSIF rec_c_sl_clchsn_dtls.change_field_code = 'LOAN_PER_END_DT' AND
2098 rec_c_sl_clchsn_dtls.change_code_txt = 'A' THEN
2099 l_d_old_per_end_dt := rec_c_sl_clchsn_dtls.old_date;
2100 l_d_revised_per_end_dt := rec_c_sl_clchsn_dtls.new_date;
2101 END IF;
2102 END IF;
2103
2104 -- There must be one Loan Cancellation/Reinstatement (@1-08) Detail Record in the file
2105 -- for each loan to be fully cancelled or fully/partially reinstated (pre-disbursement).
2106 IF rec_c_sl_clchsn_dtls.change_record_type_txt = '08' THEN
2107 l_c_01_08_flg := 'Y';
2108 log_to_fnd(p_v_module => 'sub_create_file',
2109 p_v_string => ' (@1-08) Detail Record found '
2110 );
2111 -- Full Loan Cancellation
2112 IF rec_c_sl_clchsn_dtls.change_code_txt = 'A' THEN
2113 l_d_cancellation_date := TRUNC(SYSDATE);
2114 -- Full/Partial Loan Reinstatement
2115 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'B' THEN
2116 l_n_reinstated_loan_amt := rec_c_sl_clchsn_dtls.new_amt;
2117 END IF;
2118 END IF;
2119
2120 -- There must be one Disbursement Cancellation/Change (@1-09) Detail Record in the
2121 -- file for each loan disbursement to be cancelled, reinstated, and/or rescheduled (predisbursement).
2122 -- One Disbursement Cancellation/Change (@1-09) Detail Record in the file for
2123 -- each disbursement (if applicable)
2124 IF rec_c_sl_clchsn_dtls.change_record_type_txt = '09' THEN
2125 l_c_01_09_flg := 'Y';
2126 log_to_fnd(p_v_module => 'sub_create_file',
2127 p_v_string => ' (@1-09) Detail Record found '
2128 );
2129 IF l_n_disb_number <> rec_c_sl_clchsn_dtls.disbursement_number THEN
2130 l_n_disb_number := rec_c_sl_clchsn_dtls.disbursement_number;
2131 l_n_ctr_09 := l_n_ctr_09 + 1;
2132 OPEN c_aw_awd_disb2(
2133 cp_n_award_id => rec_c_recip_dtls.award_id,
2134 cp_n_disb_num => l_n_disb_number
2135 );
2136 FETCH c_aw_awd_disb2 INTO rec_c_aw_awd_disb2;
2137 CLOSE c_aw_awd_disb2 ;
2138 v_tab_rec_09_dtl(l_n_ctr_09).disb_number := rec_c_aw_awd_disb2.disb_num ;
2139 v_tab_rec_09_dtl(l_n_ctr_09).disb_date := rec_c_aw_awd_disb2.disb_date ;
2140 v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_date := NULL ;
2141 v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_amt := NULL ;
2142 v_tab_rec_09_dtl(l_n_ctr_09).disb_hold_rel_ind := NULL ;
2143 v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_date := rec_c_aw_awd_disb2.disb_date ;
2144 v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_amt := rec_c_aw_awd_disb2.disb_accepted_amt ;
2145 v_tab_rec_09_dtl(l_n_ctr_09).reinstate_ind := 'N' ;
2146 END IF;
2147
2148 -- Disbursement Hold Release Change
2149 IF rec_c_sl_clchsn_dtls.change_code_txt = 'E' THEN
2150 v_tab_rec_09_dtl(l_n_ctr_09).disb_hold_rel_ind := rec_c_sl_clchsn_dtls.new_value_txt ;
2151 -- Disbursement Reinstatement
2152 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'C' THEN
2153 v_tab_rec_09_dtl(l_n_ctr_09).reinstate_ind := 'Y' ;
2154 -- Disbursement Date Change
2155 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'B' THEN
2156 v_tab_rec_09_dtl(l_n_ctr_09).disb_date := rec_c_sl_clchsn_dtls.old_date;
2157 v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_date := rec_c_sl_clchsn_dtls.new_date;
2158 -- New Disbursement Addition
2159 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'D' THEN
2160 v_tab_rec_09_dtl(l_n_ctr_09).disb_date := NULL ;
2161 -- Full cancellation
2162 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'ADI' THEN
2163 v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_date := rec_c_sl_clchsn_dtls.disbursement_cancel_date ;
2164 v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_amt := rec_c_sl_clchsn_dtls.disbursement_cancel_amt ;
2165 --MN 5-Jan-2005 Updated to send the revised amount and make the date NULL only if revised_disb_amt is 0
2166 -- v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_amt := NULL ;
2167 IF v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_amt = 0 THEN
2168 v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_date := NULL ;
2169 END IF;
2170 -- Disbursement amount change other than cancellation
2171 ELSIF rec_c_sl_clchsn_dtls.change_code_txt IN ('A','AI','AD') THEN
2172 v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_amt := rec_c_sl_clchsn_dtls.new_amt ;
2173 END IF;
2174 END IF;
2175
2176 -- There must be one Disbursement Notification/Change (@1-10) Detail Record in the
2177 -- file for each loan disbursement to be cancelled, reissued, reinstated, and/or rescheduled
2178 -- (post-disbursement).
2179 -- This detail record is submitted for each disbursement to be cancelled, rescheduled,
2180 -- reissued, and/or reinstated after the release of funds for the disbursement.
2181 IF rec_c_sl_clchsn_dtls.change_record_type_txt = '10' THEN
2182 l_c_01_10_flg := 'Y';
2183 log_to_fnd(p_v_module => 'sub_create_file',
2184 p_v_string => ' (@1-10) Detail Record found '
2185 );
2186 IF l_n_post_disb_number <> rec_c_sl_clchsn_dtls.disbursement_number THEN
2187 l_n_post_disb_number := rec_c_sl_clchsn_dtls.disbursement_number;
2188 l_n_ctr_10 := l_n_ctr_10 + 1;
2189 OPEN c_aw_awd_disb2(
2190 cp_n_award_id => rec_c_recip_dtls.award_id,
2191 cp_n_disb_num => l_n_post_disb_number
2192 );
2193 FETCH c_aw_awd_disb2 INTO rec_c_aw_awd_disb2;
2194 CLOSE c_aw_awd_disb2 ;
2195 v_tab_rec_10_dtl(l_n_ctr_10).disb_number := rec_c_aw_awd_disb2.disb_num ;
2196 v_tab_rec_10_dtl(l_n_ctr_10).disb_date := rec_c_aw_awd_disb2.disb_date ;
2197 v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_date := NULL ;
2198 v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_amt := NULL ;
2199 v_tab_rec_10_dtl(l_n_ctr_10).disb_consummation_ind := NULL ;
2200 v_tab_rec_10_dtl(l_n_ctr_10).actual_return_amt := NULL ;
2201 v_tab_rec_10_dtl(l_n_ctr_10).fund_return_mthd_code := NULL ;
2202 v_tab_rec_10_dtl(l_n_ctr_10).fund_reissue_code := NULL ;
2203 v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_date := rec_c_aw_awd_disb2.disb_date ;
2204 v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_amt := rec_c_aw_awd_disb2.disb_accepted_amt ;
2205 v_tab_rec_10_dtl(l_n_ctr_10).reinstate_ind := 'N' ;
2206 END IF;
2207 -- Full or Partial Cancellation
2208 IF rec_c_sl_clchsn_dtls.change_code_txt = 'A' THEN
2209 v_tab_rec_10_dtl(l_n_ctr_10).disb_consummation_ind := 'N';
2210 v_tab_rec_10_dtl(l_n_ctr_10).fund_return_mthd_code := rec_c_sl_clchsn_dtls.disbursement_return_code ;
2211 v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_date := NULL;
2212 v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_amt := NULL;
2213 IF rec_c_sl_clchsn_dtls.change_field_code = 'DISB_AMOUNT' THEN
2214 v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_amt := rec_c_sl_clchsn_dtls.disbursement_cancel_amt ;
2215 l_n_actual_return_amt := NVL(rec_c_sl_clchsn_dtls.disbursement_cancel_amt,0) - (NVL(rec_c_aw_awd_disb2.fee_1,0) + NVL(rec_c_aw_awd_disb2.fee_2,0));
2216 v_tab_rec_10_dtl(l_n_ctr_10).actual_return_amt := l_n_actual_return_amt;
2217 ELSIF rec_c_sl_clchsn_dtls.change_field_code = 'DISB_DATE' THEN
2218 v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_date := rec_c_sl_clchsn_dtls.disbursement_cancel_date ;
2219 END IF;
2220 -- Full or Partial Reissue
2221 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'B' THEN
2222 v_tab_rec_10_dtl(l_n_ctr_10).fund_return_mthd_code := rec_c_sl_clchsn_dtls.disbursement_return_code ;
2223 v_tab_rec_10_dtl(l_n_ctr_10).fund_reissue_code := rec_c_sl_clchsn_dtls.disbursement_reissue_code ;
2224 IF rec_c_sl_clchsn_dtls.change_field_code = 'DISB_DATE' THEN
2225 v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_date := rec_c_sl_clchsn_dtls.new_date;
2226 ELSIF rec_c_sl_clchsn_dtls.change_field_code = 'DISB_AMOUNT' THEN
2227 v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_date := rec_c_sl_clchsn_dtls.disbursement_cancel_date ;
2228 v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_amt := rec_c_sl_clchsn_dtls.disbursement_cancel_amt ;
2229 l_n_actual_return_amt := NVL(rec_c_sl_clchsn_dtls.disbursement_cancel_amt,0) - (NVL(rec_c_aw_awd_disb2.fee_1,0) + NVL(rec_c_aw_awd_disb2.fee_2,0));
2230 v_tab_rec_10_dtl(l_n_ctr_10).actual_return_amt := l_n_actual_return_amt;
2231 END IF;
2232 -- Full or Partial Reinstatement
2233 ELSIF rec_c_sl_clchsn_dtls.change_code_txt = 'C' THEN
2234 v_tab_rec_10_dtl(l_n_ctr_10).reinstate_ind := 'Y' ;
2235 IF rec_c_sl_clchsn_dtls.change_field_code = 'DISB_DATE' THEN
2236 v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_date := rec_c_sl_clchsn_dtls.new_date;
2237 END IF;
2238 END IF;
2239 END IF;
2240 -- There must be one Loan Increase (@1-24) Detail Record in the file for each loan
2241 -- increase request (pre- or post-disbursement).
2242 IF rec_c_sl_clchsn_dtls.change_record_type_txt = '24' THEN
2243 l_c_01_24_flg := 'Y';
2244 log_to_fnd(p_v_module => 'sub_create_file',
2245 p_v_string => ' (@1-24) Detail Record found '
2246 );
2247 l_n_loan_amt_increase := rec_c_sl_clchsn_dtls.new_amt;
2248 END IF;
2249
2250 END LOOP;
2251
2252 IF l_c_01_07_flg = 'Y' THEN
2253 -- constructing change send (@1-07) record
2254 log_to_fnd(p_v_module => 'sub_create_file',
2255 p_v_string => ' constructing change send (@1-07) record '
2256 );
2257 l_v_chg_01_7_rec := '@1'
2258 ||'07'
2259 ||RPAD(NVL(rec_borrower_dtls.p_ssn,' '),9,' ')
2260 ||RPAD(l_v_school_id,8,' ')
2261 ||l_v_filler_3_char
2262 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
2263 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
2264 ||l_v_filler_3_char
2265 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
2266 ||RPAD(' ',16,' ')
2267 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.guarantee_date,'YYYYMMDD'),'0'),8,'0')
2268 ||l_v_loan_type
2269 ||RPAD(l_v_alt_prog_type_code,3,' ')
2270 ||RPAD('0',8,'0')
2271 ||RPAD(NVL(rec_c_recip_dtls.lender_id,' '),6,' ')
2272 ||RPAD(' ',6,' ')
2273 ||LPAD(NVL(TO_CHAR(l_d_old_per_begin_dt,'YYYYMMDD'),'0'),8,'0')
2274 ||LPAD(NVL(TO_CHAR(l_d_old_per_end_dt,'YYYYMMDD'),'0'),8,'0')
2275 ||l_v_filler_2_char
2276 ||LPAD(NVL(l_v_ssn,' '),9,' ')
2277 ||RPAD(NVL(rec_c_recip_dtls.external_loan_id_txt,rec_c_recip_dtls.loan_number),17,' ')
2278 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.cl_seq_number),'0'),2,'0')
2279 ||LPAD(NVL(TO_CHAR(l_d_revised_per_begin_dt,'YYYYMMDD'),'0'),8,'0')
2280 ||LPAD(NVL(TO_CHAR(l_d_revised_per_end_dt,'YYYYMMDD'),'0'),8,'0')
2281 ||NVL(NVL(rec_c_recip_dtls.override_grade_level_code,rec_c_recip_dtls.grade_level_code),' ')
2282 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.sch_cert_date,'YYYYMMDD'),'0'),8,'0')
2283 ||' '
2284 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.anticip_compl_date,'YYYYMMDD'),'0'),8,'0')
2285 ||' '
2286 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS')||RPAD('0',6,'0')
2287 ||RPAD(' ',9,' ')
2288 ||RPAD(' ',9,' ')
2289 ||RPAD(' ',9,' ')
2290 ||RPAD(' ',9,' ')
2291 ||RPAD(NVL(rec_c_recip_dtls.lend_non_ed_brc_id,' '),4,' ')
2292 ||RPAD(' ',100,' ')
2293 ||RPAD(NVL(rec_c_recip_dtls.school_use_txt,' '),23,' ')
2294 ||RPAD(NVL(rec_c_recip_dtls.lender_use_txt,' '),20,' ')
2295 ||RPAD(NVL(rec_c_recip_dtls.guarantor_use_txt,' '),23,' ')
2296 ||RPAD(' ',80,' ')
2297 ||'*';
2298
2299 l_v_chg_01_7_rec := UPPER(l_v_chg_01_7_rec);
2300 -- writing the change send @1-07 record on to output file
2301 fnd_file.put_line(fnd_file.output,l_v_chg_01_7_rec);
2302 l_n_1cntr := l_n_1cntr + 1;
2303 log_to_fnd(p_v_module => 'sub_create_file',
2304 p_v_string => ' change send @1-07 record '||l_v_chg_01_7_rec
2305 );
2306 log_to_fnd(p_v_module => 'sub_create_file',
2307 p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2308 ' with the send record text and to move status code to Sent for change send @1-07 record'
2309 );
2310 proc_update_clchsn_dtls_rec(
2311 p_v_loan_number => rec_c_recip_dtls.loan_number ,
2312 p_v_change_record_typ => '07' ,
2313 p_n_disb_num => NULL ,
2314 p_v_send_record_txt => SUBSTR(l_v_chg_01_7_rec,3,(INSTR(l_v_chg_01_7_rec,'*')-3))
2315 );
2316 log_to_fnd(p_v_module => 'sub_create_file',
2317 p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2318 ' for change send @1-07 record '
2319 );
2320 END IF;
2321
2322 IF l_c_01_08_flg = 'Y' THEN
2323 -- constructing change send (@1-08) record
2324 log_to_fnd(p_v_module => 'sub_create_file',
2325 p_v_string => ' constructing change send (@1-08) record '
2326 );
2327 l_v_chg_01_8_rec := '@1'
2328 ||'08'
2329 ||RPAD(NVL(rec_borrower_dtls.p_ssn,' '),9,' ')
2330 ||RPAD(l_v_school_id,8,' ')
2331 ||l_v_filler_3_char
2332 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
2333 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
2334 ||l_v_filler_3_char
2335 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
2336 ||RPAD(' ',16,' ')
2337 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.guarantee_date,'YYYYMMDD'),'0'),8,'0')
2338 ||l_v_loan_type
2339 ||RPAD(l_v_alt_prog_type_code,3,' ')
2340 ||RPAD('0',8,'0')
2341 ||RPAD(NVL(rec_c_recip_dtls.lender_id,' '),6,' ')
2342 ||RPAD(' ',6,' ')
2343 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_begin_date,'YYYYMMDD'),'0'),8,'0')
2344 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_end_date,'YYYYMMDD'),'0'),8,'0')
2345 ||l_v_filler_2_char
2346 ||LPAD(NVL(l_v_ssn,' '),9,' ')
2347 ||RPAD(NVL(rec_c_recip_dtls.external_loan_id_txt,rec_c_recip_dtls.loan_number),17,' ')
2348 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.cl_seq_number),'0'),2,'0')
2349 ||LPAD(NVL(TO_CHAR(l_d_cancellation_date,'YYYYMMDD'),'0'),8,'0')
2350 ||LPAD(TO_CHAR(NVL(l_n_reinstated_loan_amt * 100, '0')),8,'0')
2351 ||' '
2352 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS')||RPAD('0',6,'0')
2353 ||RPAD(' ',9,' ')
2354 ||RPAD(' ',9,' ')
2355 ||RPAD(' ',9,' ')
2356 ||RPAD(' ',9,' ')
2357 ||RPAD(' ',202,' ')
2358 ||RPAD(NVL(rec_c_recip_dtls.school_use_txt,' '),23,' ')
2359 ||RPAD(NVL(rec_c_recip_dtls.lender_use_txt,' '),20,' ')
2360 ||RPAD(NVL(rec_c_recip_dtls.guarantor_use_txt,' '),23,' ')
2361 ||'*';
2362
2363 l_v_chg_01_8_rec := UPPER(l_v_chg_01_8_rec);
2364 -- writing the change send @1-08 record on to output file
2365 fnd_file.put_line(fnd_file.output,l_v_chg_01_8_rec);
2366 l_n_1cntr := l_n_1cntr + 1;
2367 log_to_fnd(p_v_module => 'sub_create_file',
2368 p_v_string => ' change send @1-08 record '||l_v_chg_01_8_rec
2369 );
2370 log_to_fnd(p_v_module => 'sub_create_file',
2371 p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2372 ' with the send record text and to move status code to Sent for change send @1-08 record'
2373 );
2374 proc_update_clchsn_dtls_rec(
2375 p_v_loan_number => rec_c_recip_dtls.loan_number ,
2376 p_v_change_record_typ => '08' ,
2377 p_n_disb_num => NULL ,
2378 p_v_send_record_txt => SUBSTR(l_v_chg_01_8_rec,3,(INSTR(l_v_chg_01_8_rec,'*')-3))
2379 );
2380 log_to_fnd(p_v_module => 'sub_create_file',
2381 p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2382 ' for change send @1-08 record '
2383 );
2384 END IF;
2385
2386 IF l_c_01_09_flg = 'Y' THEN
2387 -- constructing change send (@1-09) record
2388 l_n_ctr_09 := 0;
2389 FOR l_n_ctr_09 IN v_tab_rec_09_dtl.FIRST..v_tab_rec_09_dtl.LAST
2390 LOOP
2391 log_to_fnd(p_v_module => 'sub_create_file',
2392 p_v_string => ' constructing change send (@1-09) record for ' ||
2393 ' disb number : '||v_tab_rec_09_dtl(l_n_ctr_09).disb_number ||
2394 ' l_n_ctr_09 value : '||l_n_ctr_09 ||
2395 ' disb_cancel_date : '||v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_date ||
2396 ' disb_cancel_amt : '||v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_amt ||
2397 ' disb_hold_rel_ind: '||v_tab_rec_09_dtl(l_n_ctr_09).disb_hold_rel_ind||
2398 ' revised_disb_date: '||v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_date||
2399 ' revised_disb_amt : '||v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_amt
2400 );
2401
2402 l_v_chg_01_9_rec := '@1'
2403 ||'09'
2404 ||RPAD(NVL(rec_borrower_dtls.p_ssn,' '),9,' ')
2405 ||RPAD(l_v_school_id,8,' ')
2406 ||l_v_filler_3_char
2407 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
2408 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
2409 ||l_v_filler_3_char
2410 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
2411 ||RPAD(' ',16,' ')
2412 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.guarantee_date,'YYYYMMDD'),'0'),8,'0')
2413 ||l_v_loan_type
2414 ||RPAD(l_v_alt_prog_type_code,3,' ')
2415 ||RPAD('0',8,'0')
2416 ||RPAD(NVL(rec_c_recip_dtls.lender_id,' '),6,' ')
2417 ||RPAD(' ',6,' ')
2418 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_begin_date,'YYYYMMDD'),'0'),8,'0')
2419 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_end_date,'YYYYMMDD'),'0'),8,'0')
2420 ||l_v_filler_2_char
2421 ||LPAD(NVL(l_v_ssn,' '),9,' ')
2422 ||RPAD(NVL(rec_c_recip_dtls.external_loan_id_txt,rec_c_recip_dtls.loan_number),17,' ')
2423 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.cl_seq_number),'0'),2,'0')
2424 ||' '
2425 ||NVL(TO_CHAR(v_tab_rec_09_dtl(l_n_ctr_09).disb_number),' ')
2426 ||LPAD(NVL(TO_CHAR(v_tab_rec_09_dtl(l_n_ctr_09).disb_date,'YYYYMMDD'),'0'),8,'0')
2427 ||LPAD(NVL(TO_CHAR(v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_date,'YYYYMMDD'),'0'),8,'0')
2428 ||LPAD(TO_CHAR(NVL(v_tab_rec_09_dtl(l_n_ctr_09).disb_cancel_amt * 100, '0')),8,'0')
2429 ||NVL(v_tab_rec_09_dtl(l_n_ctr_09).disb_hold_rel_ind,' ')
2430 ||LPAD(NVL(TO_CHAR(v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_date,'YYYYMMDD'),'0'),8,'0')
2431 ||LPAD(TO_CHAR(NVL(v_tab_rec_09_dtl(l_n_ctr_09).revised_disb_amt * 100, '0')),8,'0')
2432 ||NVL(v_tab_rec_09_dtl(l_n_ctr_09).reinstate_ind,' ')
2433 ||' '
2434 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS')||RPAD('0',6,'0')
2435 ||RPAD(' ',9,' ')
2436 ||RPAD(' ',9,' ')
2437 ||RPAD(' ',9,' ')
2438 ||RPAD(' ',9,' ')
2439 ||RPAD(NVL(rec_c_recip_dtls.lend_non_ed_brc_id,' '),4,' ')
2440 ||RPAD('0',8,'0')
2441 ||RPAD(' ',82,' ')
2442 ||RPAD(NVL(rec_c_recip_dtls.school_use_txt,' '),23,' ')
2443 ||RPAD(NVL(rec_c_recip_dtls.lender_use_txt,' '),20,' ')
2444 ||RPAD(NVL(rec_c_recip_dtls.guarantor_use_txt,' '),23,' ')
2445 ||RPAD(' ',80,' ')
2446 ||'*';
2447
2448 l_v_chg_01_9_rec := UPPER(l_v_chg_01_9_rec);
2449 -- writing the change send @1-09 record on to output file
2450 fnd_file.put_line(fnd_file.output,l_v_chg_01_9_rec);
2451 l_n_1cntr := l_n_1cntr + 1;
2452 log_to_fnd(p_v_module => 'sub_create_file',
2453 p_v_string => ' change send @1-09 record '||l_v_chg_01_9_rec
2454 );
2455 log_to_fnd(p_v_module => 'sub_create_file',
2456 p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2457 ' with the send record text and to move status code to Sent for change send @1-09 record'
2458 );
2459 proc_update_clchsn_dtls_rec(
2460 p_v_loan_number => rec_c_recip_dtls.loan_number ,
2461 p_v_change_record_typ => '09' ,
2462 p_n_disb_num => v_tab_rec_09_dtl(l_n_ctr_09).disb_number ,
2463 p_v_send_record_txt => SUBSTR(l_v_chg_01_9_rec,3,(INSTR(l_v_chg_01_9_rec,'*')-3))
2464 );
2465 log_to_fnd(p_v_module => 'sub_create_file',
2466 p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2467 ' for change send @1-09 record '
2468 );
2469 END LOOP;
2470 v_tab_rec_09_dtl.DELETE;
2471 END IF;
2472
2473 IF l_c_01_10_flg = 'Y' THEN
2474 l_n_ctr_10 := 0;
2475 FOR l_n_ctr_10 IN v_tab_rec_10_dtl.FIRST..v_tab_rec_10_dtl.LAST
2476 LOOP
2477 log_to_fnd(p_v_module => 'sub_create_file',
2478 p_v_string => ' constructing change send (@1-10) record '
2479 );
2480 l_v_chg_01_10_rec := '@1'
2481 ||'10'
2482 ||RPAD(NVL(rec_borrower_dtls.p_ssn,' '),9,' ')
2483 ||RPAD(l_v_school_id,8,' ')
2484 ||l_v_filler_3_char
2485 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
2486 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
2487 ||l_v_filler_3_char
2488 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
2489 ||RPAD(' ',16,' ')
2490 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.guarantee_date,'YYYYMMDD'),'0'),8,'0')
2491 ||l_v_loan_type
2492 ||RPAD(l_v_alt_prog_type_code,3,' ')
2493 ||RPAD('0',8,'0')
2494 ||RPAD(NVL(rec_c_recip_dtls.lender_id,' '),6,' ')
2495 ||RPAD(' ',6,' ')
2496 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_begin_date,'YYYYMMDD'),'0'),8,'0')
2497 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_end_date,'YYYYMMDD'),'0'),8,'0')
2498 ||l_v_filler_2_char
2499 ||LPAD(NVL(l_v_ssn,' '),9,' ')
2500 ||RPAD(NVL(rec_c_recip_dtls.external_loan_id_txt,rec_c_recip_dtls.loan_number),17,' ')
2501 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.cl_seq_number),'0'),2,'0')
2502 ||' '
2503 ||NVL(TO_CHAR(v_tab_rec_10_dtl(l_n_ctr_10).disb_number),' ')
2504 ||RPAD('0',8,'0')
2505 ||LPAD(NVL(TO_CHAR(v_tab_rec_10_dtl(l_n_ctr_10).disb_date,'YYYYMMDD'),'0'),8,'0')
2506 ||LPAD('0',8,'0')
2507 ||LPAD(NVL(TO_CHAR(v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_date,'YYYYMMDD'),'0'),8,'0')
2508 ||LPAD(TO_CHAR(NVL(v_tab_rec_10_dtl(l_n_ctr_10).disb_cancel_amt * 100, '0')),8,'0')
2509 ||NVL(v_tab_rec_10_dtl(l_n_ctr_10).disb_consummation_ind,' ')
2510 ||LPAD(TO_CHAR(NVL(v_tab_rec_10_dtl(l_n_ctr_10).actual_return_amt * 100, '0')),8,'0')
2511 ||NVL(v_tab_rec_10_dtl(l_n_ctr_10).fund_return_mthd_code,' ')
2512 ||NVL(v_tab_rec_10_dtl(l_n_ctr_10).fund_reissue_code,' ')
2513 ||LPAD(NVL(TO_CHAR(v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_date,'YYYYMMDD'),'0'),8,'0')
2514 ||LPAD(TO_CHAR(NVL(v_tab_rec_10_dtl(l_n_ctr_10).revised_disb_amt * 100, '0')),8,'0')
2515 ||NVL(v_tab_rec_10_dtl(l_n_ctr_10).reinstate_ind,' ')
2516 ||' '
2517 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS')||RPAD('0',6,'0')
2518 ||RPAD(' ',9,' ')
2519 ||RPAD(' ',9,' ')
2520 ||RPAD(' ',9,' ')
2521 ||RPAD(' ',9,' ')
2522 ||RPAD(NVL(rec_c_recip_dtls.lend_non_ed_brc_id,' '),4,' ')
2523 ||RPAD('0',8,'0')
2524 ||RPAD(' ',56,' ')
2525 ||RPAD(NVL(rec_c_recip_dtls.school_use_txt,' '),23,' ')
2526 ||RPAD(NVL(rec_c_recip_dtls.lender_use_txt,' '),20,' ')
2527 ||RPAD(NVL(rec_c_recip_dtls.guarantor_use_txt,' '),23,' ')
2528 ||RPAD(' ',80,' ')
2529 ||'*';
2530
2531 l_v_chg_01_10_rec := UPPER(l_v_chg_01_10_rec);
2532 -- writing the change send @1-10 record on to output file
2533 fnd_file.put_line(fnd_file.output,l_v_chg_01_10_rec);
2534 l_n_1cntr := l_n_1cntr + 1;
2535 log_to_fnd(p_v_module => 'sub_create_file',
2536 p_v_string => ' change send @1-10 record '||l_v_chg_01_10_rec
2537 );
2538 log_to_fnd(p_v_module => 'sub_create_file',
2539 p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2540 ' with the send record text and to move status code to Sent for change send @1-10 record'
2541 );
2542 proc_update_clchsn_dtls_rec(
2543 p_v_loan_number => rec_c_recip_dtls.loan_number ,
2544 p_v_change_record_typ => '10' ,
2545 p_n_disb_num => v_tab_rec_10_dtl(l_n_ctr_10).disb_number ,
2546 p_v_send_record_txt => SUBSTR(l_v_chg_01_10_rec,3,(INSTR(l_v_chg_01_10_rec,'*')-3))
2547 );
2548 log_to_fnd(p_v_module => 'sub_create_file',
2549 p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2550 ' for change send @1-10 record '
2551 );
2552 END LOOP;
2553 v_tab_rec_10_dtl.DELETE;
2554 END IF;
2555
2556 IF l_c_01_24_flg = 'Y' THEN
2557 log_to_fnd(p_v_module => 'sub_create_file',
2558 p_v_string => ' constructing change send (@1-24) record '
2559 );
2560 -- bvisvana - Bug # 4575843 - Truncated the increase loan amount to avoid decimals.
2561 l_v_chg_01_24_rec := '@1'
2562 ||'24'
2563 ||RPAD(NVL(rec_borrower_dtls.p_ssn,' '),9,' ')
2564 ||RPAD(l_v_school_id,8,' ')
2565 ||l_v_filler_3_char
2566 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
2567 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
2568 ||l_v_filler_3_char
2569 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
2570 ||RPAD(' ',16,' ')
2571 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.guarantee_date,'YYYYMMDD'),'0'),8,'0')
2572 ||l_v_loan_type
2573 ||RPAD(l_v_alt_prog_type_code,3,' ')
2574 ||RPAD('0',8,'0')
2575 ||RPAD(NVL(rec_c_recip_dtls.lender_id,' '),6,' ')
2576 ||RPAD(' ',6,' ')
2577 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_begin_date,'YYYYMMDD'),'0'),8,'0')
2578 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.loan_per_end_date,'YYYYMMDD'),'0'),8,'0')
2579 ||l_v_filler_2_char
2580 ||LPAD(NVL(l_v_ssn,' '),9,' ')
2581 ||RPAD(NVL(rec_c_recip_dtls.external_loan_id_txt,rec_c_recip_dtls.loan_number),17,' ')
2582 ||LPAD(NVL(TO_CHAR(rec_c_recip_dtls.cl_seq_number),'0'),2,'0')
2583 ||LPAD(TO_CHAR(NVL(TRUNC(l_n_loan_amt_increase),0)),6,'0')
2584 ||LPAD(TO_CHAR(NVL(TRUNC(l_n_loan_amt_increase),0)),6,'0')
2585 ||LPAD(TO_CHAR(NVL(l_n_coa,0)),5,'0')
2586 ||LPAD(TO_CHAR(NVL(l_n_efc,0)),5,'0')
2587 ||LPAD(TO_CHAR(NVL(l_n_est_fin,0)),5,'0');
2588 l_v_disb_rec := NULL;
2589 l_n_ctr_disb := 0 ;
2590 FOR rec_c_aw_awd_disb IN c_aw_awd_disb ( cp_n_award_id => rec_c_recip_dtls.award_id)
2591 LOOP
2592 l_n_ctr_disb := NVL(l_n_ctr_disb,0) + 1;
2593 l_v_disb_rec := l_v_disb_rec
2594 ||LPAD(NVL(TO_CHAR(rec_c_aw_awd_disb.disb_date,'YYYYMMDD'),'0'),8,'0')
2595 ||LPAD(TO_CHAR(NVL(rec_c_aw_awd_disb.disb_accepted_amt * 100, '0')),8,'0');
2596 END LOOP;
2597 IF l_n_ctr_disb = 1 THEN
2598 l_v_chg_01_24_rec := l_v_chg_01_24_rec
2599 ||l_v_disb_rec
2600 ||RPAD('0',48,'0');
2601 ELSIF l_n_ctr_disb = 2 THEN
2602 l_v_chg_01_24_rec := l_v_chg_01_24_rec
2603 ||l_v_disb_rec
2604 ||RPAD('0',32,'0');
2605 ELSIF l_n_ctr_disb = 3 THEN
2606 l_v_chg_01_24_rec := l_v_chg_01_24_rec
2607 ||l_v_disb_rec
2608 ||RPAD('0',16,'0');
2609 ELSIF l_n_ctr_disb = 4 THEN
2610 l_v_chg_01_24_rec := l_v_chg_01_24_rec
2611 ||l_v_disb_rec ;
2612 END IF;
2613 l_v_chg_01_24_rec := l_v_chg_01_24_rec
2614 ||' '
2615 ||RPAD('0',8,'0')
2616 ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS')||RPAD('0',6,'0')
2617 ||RPAD(' ',9,' ')
2618 ||RPAD(' ',9,' ')
2619 ||RPAD(' ',9,' ')
2620 ||RPAD(' ',9,' ')
2621 ||RPAD(NVL(rec_c_recip_dtls.lend_non_ed_brc_id,' '),4,' ')
2622 ||RPAD('0',8,'0')
2623 ||l_v_filler_2_char
2624 ||l_v_filler_2_char
2625 ||l_v_filler_2_char
2626 ||l_v_filler_2_char
2627 ||RPAD(' ',19,' ')
2628 ||RPAD(NVL(rec_c_recip_dtls.school_use_txt,' '),23,' ')
2629 ||RPAD(NVL(rec_c_recip_dtls.lender_use_txt,' '),20,' ')
2630 ||RPAD(NVL(rec_c_recip_dtls.guarantor_use_txt,' '),23,' ')
2631 ||RPAD(' ',80,' ')
2632 ||'*';
2633
2634 l_v_chg_01_24_rec := UPPER(l_v_chg_01_24_rec);
2635 -- writing the change send @1-24 record on to output file
2636 fnd_file.put_line(fnd_file.output,l_v_chg_01_24_rec);
2637 l_n_1cntr := l_n_1cntr + 1;
2638 log_to_fnd(p_v_module => 'sub_create_file',
2639 p_v_string => ' change send @1-24 record '||l_v_chg_01_24_rec
2640 );
2641 log_to_fnd(p_v_module => 'sub_create_file',
2642 p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2643 ' with the send record text and to move status code to Sent for change send @1-24 record'
2644 );
2645 proc_update_clchsn_dtls_rec(
2646 p_v_loan_number => rec_c_recip_dtls.loan_number ,
2647 p_v_change_record_typ => '24' ,
2648 p_n_disb_num => NULL ,
2649 p_v_send_record_txt => SUBSTR(l_v_chg_01_24_rec,3,(INSTR(l_v_chg_01_24_rec,'*')-3))
2650 );
2651 log_to_fnd(p_v_module => 'sub_create_file',
2652 p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
2653 ' for change send @1-24 record '
2654 );
2655 END IF;
2656
2657 -- invoke the igf_sl_gen.update_cl_chg_status to update the change status of loan table
2658 log_to_fnd(p_v_module => 'sub_create_file',
2659 p_v_string => 'invoking igf_sl_gen.update_cl_chg_status for loan number ='||rec_c_recip_dtls.loan_number
2660 );
2661 igf_sl_gen.update_cl_chg_status(p_v_loan_number => rec_c_recip_dtls.loan_number);
2662 log_to_fnd(p_v_module => 'sub_create_file',
2663 p_v_string => ' Call out to igf_sl_gen.update_cl_chg_status successful for loan number ='||rec_c_recip_dtls.loan_number
2664 );
2665 OPEN c_sl_lor_loc (
2666 cp_n_loan_id => rec_c_recip_dtls.loan_id,
2667 cp_n_origination_id => rec_c_recip_dtls.origination_id
2668 );
2669 FETCH c_sl_lor_loc INTO rec_c_sl_lor_loc;
2670 CLOSE c_sl_lor_loc ;
2671
2672 rec_c_sl_lor_loc.loan_per_end_date := l_d_revised_per_begin_dt;
2673 rec_c_sl_lor_loc.loan_per_end_date := l_d_revised_per_end_dt ;
2674 rec_c_sl_lor_loc.anticip_compl_date := rec_c_recip_dtls.anticip_compl_date ;
2675 rec_c_sl_lor_loc.grade_level_code := NVL(rec_c_recip_dtls.override_grade_level_code,rec_c_recip_dtls.grade_level_code);
2676 -- if no loan increase (@1-24), pass the same value originally present in the
2677 -- lor table for the input loan and origination id
2678 rec_c_sl_lor_loc.requested_loan_amt := NVL(l_n_loan_amt_increase,rec_c_sl_lor_loc.requested_loan_amt);
2679 log_to_fnd(p_v_module => 'sub_create_file',
2680 p_v_string => ' invoking igf_sl_lor_loc_pkg.update_row for ' ||
2681 ' loan id = ' ||rec_c_recip_dtls.loan_id ||
2682 ' Origination id = ' ||rec_c_sl_lor_loc.origination_id
2683 );
2684 igf_sl_lor_loc_pkg.update_row (
2685 x_rowid => rec_c_sl_lor_loc.row_id ,
2686 x_loan_id => rec_c_sl_lor_loc.loan_id ,
2687 x_origination_id => rec_c_sl_lor_loc.origination_id ,
2688 x_loan_number => rec_c_sl_lor_loc.loan_number ,
2689 x_loan_type => rec_c_sl_lor_loc.loan_type ,
2690 x_loan_amt_offered => rec_c_sl_lor_loc.loan_amt_offered ,
2691 x_loan_amt_accepted => rec_c_sl_lor_loc.loan_amt_accepted ,
2692 x_loan_per_begin_date => rec_c_sl_lor_loc.loan_per_begin_date ,
2693 x_loan_per_end_date => rec_c_sl_lor_loc.loan_per_end_date ,
2694 x_acad_yr_begin_date => rec_c_sl_lor_loc.acad_yr_begin_date ,
2695 x_acad_yr_end_date => rec_c_sl_lor_loc.acad_yr_end_date ,
2696 x_loan_status => rec_c_sl_lor_loc.loan_status ,
2697 x_loan_status_date => rec_c_sl_lor_loc.loan_status_date ,
2698 x_loan_chg_status => 'S' ,
2699 x_loan_chg_status_date => TRUNC(SYSDATE) ,
2700 x_req_serial_loan_code => rec_c_sl_lor_loc.req_serial_loan_code ,
2701 x_act_serial_loan_code => rec_c_sl_lor_loc.act_serial_loan_code ,
2702 x_active => rec_c_sl_lor_loc.active ,
2703 x_active_date => rec_c_sl_lor_loc.active_date ,
2704 x_sch_cert_date => rec_c_sl_lor_loc.sch_cert_date ,
2705 x_orig_status_flag => rec_c_sl_lor_loc.orig_status_flag ,
2706 x_orig_batch_id => rec_c_sl_lor_loc.orig_batch_id ,
2707 x_orig_batch_date => rec_c_sl_lor_loc.orig_batch_date ,
2708 x_chg_batch_id => rec_c_sl_lor_loc.chg_batch_id ,
2709 x_orig_ack_date => rec_c_sl_lor_loc.orig_ack_date ,
2710 x_credit_override => rec_c_sl_lor_loc.credit_override ,
2711 x_credit_decision_date => rec_c_sl_lor_loc.credit_decision_date ,
2712 x_pnote_delivery_code => rec_c_sl_lor_loc.pnote_delivery_code ,
2713 x_pnote_status => rec_c_sl_lor_loc.pnote_status ,
2714 x_pnote_status_date => rec_c_sl_lor_loc.pnote_status_date ,
2715 x_pnote_id => rec_c_sl_lor_loc.pnote_id ,
2716 x_pnote_print_ind => rec_c_sl_lor_loc.pnote_print_ind ,
2717 x_pnote_accept_amt => rec_c_sl_lor_loc.pnote_accept_amt ,
2718 x_pnote_accept_date => rec_c_sl_lor_loc.pnote_accept_date ,
2719 x_p_signature_code => rec_c_sl_lor_loc.p_signature_code ,
2720 x_p_signature_date => rec_c_sl_lor_loc.p_signature_date ,
2721 x_s_signature_code => rec_c_sl_lor_loc.s_signature_code ,
2722 x_unsub_elig_for_heal => rec_c_sl_lor_loc.unsub_elig_for_heal ,
2723 x_disclosure_print_ind => rec_c_sl_lor_loc.disclosure_print_ind ,
2724 x_orig_fee_perct => rec_c_sl_lor_loc.orig_fee_perct ,
2725 x_borw_confirm_ind => rec_c_sl_lor_loc.borw_confirm_ind ,
2726 x_borw_interest_ind => rec_c_sl_lor_loc.borw_interest_ind ,
2727 x_unsub_elig_for_depnt => rec_c_sl_lor_loc.unsub_elig_for_depnt ,
2728 x_guarantee_amt => rec_c_sl_lor_loc.guarantee_amt ,
2729 x_guarantee_date => rec_c_sl_lor_loc.guarantee_date ,
2730 x_guarnt_adj_ind => rec_c_sl_lor_loc.guarnt_adj_ind ,
2731 x_guarnt_amt_redn_code => rec_c_sl_lor_loc.guarnt_amt_redn_code ,
2732 x_guarnt_status_code => rec_c_sl_lor_loc.guarnt_status_code ,
2733 x_guarnt_status_date => rec_c_sl_lor_loc.guarnt_status_date ,
2734 x_lend_apprv_denied_code => rec_c_sl_lor_loc.lend_apprv_denied_code ,
2735 x_lend_apprv_denied_date => rec_c_sl_lor_loc.lend_apprv_denied_date ,
2736 x_lend_status_code => rec_c_sl_lor_loc.lend_status_code ,
2737 x_lend_status_date => rec_c_sl_lor_loc.lend_status_date ,
2738 x_grade_level_code => rec_c_sl_lor_loc.grade_level_code ,
2739 x_enrollment_code => rec_c_sl_lor_loc.enrollment_code ,
2740 x_anticip_compl_date => rec_c_sl_lor_loc.anticip_compl_date ,
2741 x_borw_lender_id => rec_c_sl_lor_loc.borw_lender_id ,
2742 x_duns_borw_lender_id => rec_c_sl_lor_loc.duns_borw_lender_id ,
2743 x_guarantor_id => rec_c_sl_lor_loc.guarantor_id ,
2744 x_duns_guarnt_id => rec_c_sl_lor_loc.duns_guarnt_id ,
2745 x_prc_type_code => rec_c_sl_lor_loc.prc_type_code ,
2746 x_rec_type_ind => rec_c_sl_lor_loc.rec_type_ind ,
2747 x_cl_loan_type => rec_c_sl_lor_loc.cl_loan_type ,
2748 x_cl_seq_number => rec_c_sl_lor_loc.cl_seq_number ,
2749 x_last_resort_lender => rec_c_sl_lor_loc.last_resort_lender ,
2750 x_lender_id => rec_c_sl_lor_loc.lender_id ,
2751 x_duns_lender_id => rec_c_sl_lor_loc.duns_lender_id ,
2752 x_lend_non_ed_brc_id => rec_c_sl_lor_loc.lend_non_ed_brc_id ,
2753 x_recipient_id => rec_c_sl_lor_loc.recipient_id ,
2754 x_recipient_type => rec_c_sl_lor_loc.recipient_type ,
2755 x_duns_recip_id => rec_c_sl_lor_loc.duns_recip_id ,
2756 x_recip_non_ed_brc_id => rec_c_sl_lor_loc.recip_non_ed_brc_id ,
2757 x_cl_rec_status => rec_c_sl_lor_loc.cl_rec_status ,
2758 x_cl_rec_status_last_update => rec_c_sl_lor_loc.cl_rec_status_last_update ,
2759 x_alt_prog_type_code => rec_c_sl_lor_loc.alt_prog_type_code ,
2760 x_alt_appl_ver_code => rec_c_sl_lor_loc.alt_appl_ver_code ,
2761 x_borw_outstd_loan_code => rec_c_sl_lor_loc.borw_outstd_loan_code ,
2762 x_mpn_confirm_code => rec_c_sl_lor_loc.mpn_confirm_code ,
2763 x_resp_to_orig_code => rec_c_sl_lor_loc.resp_to_orig_code ,
2764 x_appl_loan_phase_code => rec_c_sl_lor_loc.appl_loan_phase_code ,
2765 x_appl_loan_phase_code_chg => rec_c_sl_lor_loc.appl_loan_phase_code_chg ,
2766 x_tot_outstd_stafford => rec_c_sl_lor_loc.tot_outstd_stafford ,
2767 x_tot_outstd_plus => rec_c_sl_lor_loc.tot_outstd_plus ,
2768 x_alt_borw_tot_debt => rec_c_sl_lor_loc.alt_borw_tot_debt ,
2769 x_act_interest_rate => rec_c_sl_lor_loc.act_interest_rate ,
2770 x_service_type_code => rec_c_sl_lor_loc.service_type_code ,
2771 x_rev_notice_of_guarnt => rec_c_sl_lor_loc.rev_notice_of_guarnt ,
2772 x_sch_refund_amt => rec_c_sl_lor_loc.sch_refund_amt ,
2773 x_sch_refund_date => rec_c_sl_lor_loc.sch_refund_date ,
2774 x_uniq_layout_vend_code => rec_c_sl_lor_loc.uniq_layout_vend_code ,
2775 x_uniq_layout_ident_code => rec_c_sl_lor_loc.uniq_layout_ident_code ,
2776 x_p_person_id => rec_c_sl_lor_loc.p_person_id ,
2777 x_p_ssn => rec_c_sl_lor_loc.p_ssn ,
2778 x_p_ssn_chg_date => rec_c_sl_lor_loc.p_ssn_chg_date ,
2779 x_p_last_name => rec_c_sl_lor_loc.p_last_name ,
2780 x_p_first_name => rec_c_sl_lor_loc.p_first_name ,
2781 x_p_middle_name => rec_c_sl_lor_loc.p_middle_name ,
2782 x_p_permt_addr1 => rec_c_sl_lor_loc.p_permt_addr1 ,
2783 x_p_permt_addr2 => rec_c_sl_lor_loc.p_permt_addr2 ,
2784 x_p_permt_city => rec_c_sl_lor_loc.p_permt_city ,
2785 x_p_permt_state => rec_c_sl_lor_loc.p_permt_state ,
2786 x_p_permt_zip => rec_c_sl_lor_loc.p_permt_zip ,
2787 x_p_permt_addr_chg_date => rec_c_sl_lor_loc.p_permt_addr_chg_date ,
2788 x_p_permt_phone => rec_c_sl_lor_loc.p_permt_phone ,
2789 x_p_email_addr => rec_c_sl_lor_loc.p_email_addr ,
2790 x_p_date_of_birth => rec_c_sl_lor_loc.p_date_of_birth ,
2791 x_p_dob_chg_date => rec_c_sl_lor_loc.p_dob_chg_date ,
2792 x_p_license_num => rec_c_sl_lor_loc.p_license_num ,
2793 x_p_license_state => rec_c_sl_lor_loc.p_license_state ,
2794 x_p_citizenship_status => rec_c_sl_lor_loc.p_citizenship_status ,
2795 x_p_alien_reg_num => rec_c_sl_lor_loc.p_alien_reg_num ,
2796 x_p_default_status => rec_c_sl_lor_loc.p_default_status ,
2797 x_p_foreign_postal_code => rec_c_sl_lor_loc.p_foreign_postal_code ,
2798 x_p_state_of_legal_res => rec_c_sl_lor_loc.p_state_of_legal_res ,
2799 x_p_legal_res_date => rec_c_sl_lor_loc.p_legal_res_date ,
2800 x_s_ssn => rec_c_sl_lor_loc.s_ssn ,
2801 x_s_ssn_chg_date => rec_c_sl_lor_loc.s_ssn_chg_date ,
2802 x_s_last_name => rec_c_sl_lor_loc.s_last_name ,
2803 x_s_first_name => rec_c_sl_lor_loc.s_first_name ,
2804 x_s_middle_name => rec_c_sl_lor_loc.s_middle_name ,
2805 x_s_permt_addr1 => rec_c_sl_lor_loc.s_permt_addr1 ,
2806 x_s_permt_addr2 => rec_c_sl_lor_loc.s_permt_addr2 ,
2807 x_s_permt_city => rec_c_sl_lor_loc.s_permt_city ,
2808 x_s_permt_state => rec_c_sl_lor_loc.s_permt_state ,
2809 x_s_permt_zip => rec_c_sl_lor_loc.s_permt_zip ,
2810 x_s_permt_addr_chg_date => rec_c_sl_lor_loc.s_permt_addr_chg_date ,
2811 x_s_permt_phone => rec_c_sl_lor_loc.s_permt_phone ,
2812 x_s_local_addr1 => rec_c_sl_lor_loc.s_local_addr1 ,
2813 x_s_local_addr2 => rec_c_sl_lor_loc.s_local_addr2 ,
2814 x_s_local_city => rec_c_sl_lor_loc.s_local_city ,
2815 x_s_local_state => rec_c_sl_lor_loc.s_local_state ,
2816 x_s_local_zip => rec_c_sl_lor_loc.s_local_zip ,
2817 x_s_local_addr_chg_date => rec_c_sl_lor_loc.s_local_addr_chg_date ,
2818 x_s_email_addr => rec_c_sl_lor_loc.s_email_addr ,
2819 x_s_date_of_birth => rec_c_sl_lor_loc.s_date_of_birth ,
2820 x_s_dob_chg_date => rec_c_sl_lor_loc.s_dob_chg_date ,
2821 x_s_license_num => rec_c_sl_lor_loc.s_license_num ,
2822 x_s_license_state => rec_c_sl_lor_loc.s_license_state ,
2823 x_s_depncy_status => rec_c_sl_lor_loc.s_depncy_status ,
2824 x_s_default_status => rec_c_sl_lor_loc.s_default_status ,
2825 x_s_citizenship_status => rec_c_sl_lor_loc.s_citizenship_status ,
2826 x_s_alien_reg_num => rec_c_sl_lor_loc.s_alien_reg_num ,
2827 x_s_foreign_postal_code => rec_c_sl_lor_loc.s_foreign_postal_code ,
2828 x_mode => 'R' ,
2829 x_pnote_batch_id => rec_c_sl_lor_loc.pnote_batch_id ,
2830 x_pnote_ack_date => rec_c_sl_lor_loc.pnote_ack_date ,
2831 x_pnote_mpn_ind => rec_c_sl_lor_loc.pnote_mpn_ind ,
2832 x_award_id => rec_c_sl_lor_loc.award_id ,
2833 x_base_id => rec_c_sl_lor_loc.base_id ,
2834 x_document_id_txt => rec_c_sl_lor_loc.document_id_txt ,
2835 x_loan_key_num => rec_c_sl_lor_loc.loan_key_num ,
2836 x_interest_rebate_percent_num => rec_c_sl_lor_loc.interest_rebate_percent_num ,
2837 x_fin_award_year => rec_c_sl_lor_loc.fin_award_year ,
2838 x_cps_trans_num => rec_c_sl_lor_loc.cps_trans_num ,
2839 x_atd_entity_id_txt => rec_c_sl_lor_loc.atd_entity_id_txt ,
2840 x_rep_entity_id_txt => rec_c_sl_lor_loc.rep_entity_id_txt ,
2841 x_source_entity_id_txt => rec_c_sl_lor_loc.source_entity_id_txt ,
2842 x_pymt_servicer_amt => rec_c_sl_lor_loc.pymt_servicer_amt ,
2843 x_pymt_servicer_date => rec_c_sl_lor_loc.pymt_servicer_date ,
2844 x_book_loan_amt => rec_c_sl_lor_loc.book_loan_amt ,
2845 x_book_loan_amt_date => rec_c_sl_lor_loc.book_loan_amt_date ,
2846 x_s_chg_birth_date => rec_c_sl_lor_loc.s_chg_birth_date ,
2847 x_s_chg_ssn => rec_c_sl_lor_loc.s_chg_ssn ,
2848 x_s_chg_last_name => rec_c_sl_lor_loc.s_chg_last_name ,
2849 x_b_chg_birth_date => rec_c_sl_lor_loc.b_chg_birth_date ,
2850 x_b_chg_ssn => rec_c_sl_lor_loc.b_chg_ssn ,
2851 x_b_chg_last_name => rec_c_sl_lor_loc.b_chg_last_name ,
2852 x_note_message => rec_c_sl_lor_loc.note_message ,
2853 x_full_resp_code => rec_c_sl_lor_loc.full_resp_code ,
2854 x_s_permt_county => rec_c_sl_lor_loc.s_permt_county ,
2855 x_b_permt_county => rec_c_sl_lor_loc.b_permt_county ,
2856 x_s_permt_country => rec_c_sl_lor_loc.s_permt_country ,
2857 x_b_permt_country => rec_c_sl_lor_loc.b_permt_country ,
2858 x_crdt_decision_status => rec_c_sl_lor_loc.crdt_decision_status ,
2859 x_external_loan_id_txt => rec_c_sl_lor_loc.external_loan_id_txt ,
2860 x_deferment_request_code => rec_c_sl_lor_loc.deferment_request_code ,
2861 x_eft_authorization_code => rec_c_sl_lor_loc.eft_authorization_code ,
2862 x_requested_loan_amt => rec_c_sl_lor_loc.requested_loan_amt ,
2863 x_actual_record_type_code => rec_c_sl_lor_loc.actual_record_type_code ,
2864 x_reinstatement_amt => rec_c_sl_lor_loc.reinstatement_amt ,
2865 x_lender_use_txt => rec_c_sl_lor_loc.lender_use_txt ,
2866 x_guarantor_use_txt => rec_c_sl_lor_loc.guarantor_use_txt ,
2867 x_fls_approved_amt => rec_c_sl_lor_loc.fls_approved_amt ,
2868 x_flu_approved_amt => rec_c_sl_lor_loc.flu_approved_amt ,
2869 x_flp_approved_amt => rec_c_sl_lor_loc.flp_approved_amt ,
2870 x_alt_approved_amt => rec_c_sl_lor_loc.alt_approved_amt ,
2871 x_loan_app_form_code => rec_c_sl_lor_loc.loan_app_form_code ,
2872 x_alt_borrower_ind_flag => rec_c_sl_lor_loc.alt_borrower_ind_flag ,
2873 x_school_id_txt => rec_c_sl_lor_loc.school_id_txt ,
2874 x_cost_of_attendance_amt => rec_c_sl_lor_loc.cost_of_attendance_amt ,
2875 x_established_fin_aid_amount => rec_c_sl_lor_loc.established_fin_aid_amount ,
2876 x_student_electronic_sign_flag => rec_c_sl_lor_loc.student_electronic_sign_flag ,
2877 x_mpn_type_flag => rec_c_sl_lor_loc.mpn_type_flag ,
2878 x_school_use_txt => rec_c_sl_lor_loc.school_use_txt ,
2879 x_expect_family_contribute_amt => rec_c_sl_lor_loc.expect_family_contribute_amt ,
2880 x_borower_electronic_sign_flag => rec_c_sl_lor_loc.borower_electronic_sign_flag ,
2881 x_borower_credit_authoriz_flag => rec_c_sl_lor_loc.borower_credit_authoriz_flag ,
2882 x_esign_src_typ_cd => rec_c_sl_lor_loc.esign_src_typ_cd
2883 );
2884 log_to_fnd(p_v_module => 'sub_create_file',
2885 p_v_string => ' Call out to igf_sl_lor_loc_pkg.update_row for ' ||
2886 ' loan id = ' ||rec_c_sl_lor_loc.loan_id ||
2887 ' Origination id = ' ||rec_c_sl_lor_loc.origination_id ||
2888 ' Successful. Updated the change status to Sent '
2889 );
2890 END LOOP;
2891
2892 -- constructing change send trailer record
2893 log_to_fnd(p_v_module => 'sub_create_file',
2894 p_v_string => ' constructing change send trailer record '
2895 );
2896 l_v_chg_trailer_rec := '@T'
2897 ||LPAD(TO_CHAR(NVL(l_n_1cntr,0)),6,'0')
2898 ||LPAD(TO_CHAR(NVL(l_n_2cntr,0)),6,'0')
2899 ||l_v_file_crea_date
2900 ||l_v_file_crea_time
2901 ||RPAD(l_v_file_ident_code,5,' ')
2902 ||RPAD(NVL(l_v_source_name,' '),32,' ')
2903 ||RPAD(l_v_school_id,8,' ')
2904 ||l_v_filler_2_char
2905 ||RPAD(NVL(l_v_sch_non_ed_branch,' '),4,' ')
2906 ||RPAD(NVL(l_v_recipient_name,' '),32,' ')
2907 ||RPAD(NVL(l_n_recipient_id,' '),8,' ')
2908 ||l_v_filler_2_char
2909 ||RPAD(NVL(l_v_recip_non_edbrc_id,' '),4,' ')
2910 ||RPAD(' ',9,' ')
2911 ||RPAD(' ',9,' ')
2912 ||RPAD(' ',336,' ')
2913 ||'*';
2914 l_v_chg_trailer_rec := UPPER(l_v_chg_trailer_rec);
2915 -- writing the change send trailer record on to output file
2916 fnd_file.put_line(fnd_file.output,l_v_chg_trailer_rec);
2917 log_to_fnd(p_v_module => 'sub_create_file',
2918 p_v_string => ' change send trailer record '||l_v_chg_trailer_rec
2919 );
2920
2921 EXCEPTION
2922
2923 WHEN OTHERS THEN
2924 ROLLBACK TO sub_create_file;
2925 log_to_fnd(p_v_module => 'sub_create_file',
2926 p_v_string => ' when others exception handler ' ||SQLERRM
2927 );
2928 retcode := 2;
2929 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
2930 igs_ge_msg_stack.conc_exception_hndl;
2931 END sub_create_file;
2932
2933 PROCEDURE proc_update_clchsn_dtls_rec(p_v_loan_number IN igf_sl_loans.loan_number%TYPE ,
2934 p_v_change_record_typ IN igf_sl_clchsn_dtls.change_record_type_txt%TYPE ,
2935 p_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE ,
2936 p_v_send_record_txt IN igf_sl_clchsn_dtls.send_record_txt%TYPE
2937 ) AS
2938 ------------------------------------------------------------------
2939 --Created by : Sanil Madathil, Oracle IDC
2940 --Date created: 16 November 2004
2941 --
2942 -- Purpose:
2943 -- Invoked : from sub_create_file procedure
2944 -- Function :
2945 --
2946 -- Parameters : p_v_loan_number : IN parameter. Required.
2947 -- p_v_change_record_typ : IN parameter. Required.
2948 -- p_n_disb_num : IN parameter. Required.
2949 -- p_v_send_record_txt : IN parameter. Required.
2950 --
2951 --Known limitations/enhancements and/or remarks:
2952 --
2953 --Change History:
2954 --Who When What
2955 ------------------------------------------------------------------
2956 CURSOR c_sl_clchsn_dtls (cp_v_loan_number IN igf_sl_loans_all.loan_number%TYPE ,
2957 cp_v_change_record_typ IN igf_sl_clchsn_dtls.change_record_type_txt%TYPE ,
2958 cp_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE
2959 ) IS
2960 SELECT chdt.ROWID row_id
2961 ,chdt.*
2962 FROM igf_sl_clchsn_dtls chdt
2963 WHERE chdt.loan_number_txt = cp_v_loan_number
2964 AND chdt.change_record_type_txt = cp_v_change_record_typ
2965 AND ((chdt.disbursement_number = cp_n_disb_num) OR (cp_n_disb_num IS NULL))
2966 AND chdt.status_code ='R'
2967 ORDER BY loan_number_txt,change_record_type_txt,disbursement_number;
2968
2969 rec_c_sl_clchsn_dtls c_sl_clchsn_dtls%ROWTYPE;
2970
2971 l_v_loan_number igf_sl_loans.loan_number%TYPE ;
2972 l_v_change_record_typ igf_sl_clchsn_dtls.change_record_type_txt%TYPE ;
2973 l_n_disb_num igf_aw_awd_disb_all.disb_num%TYPE ;
2974 l_v_send_record_txt igf_sl_clchsn_dtls.send_record_txt%TYPE ;
2975 BEGIN
2976 log_to_fnd(p_v_module => 'proc_update_clchsn_dtls_rec',
2977 p_v_string => ' Entered Procedure proc_update_clchsn_dtls_rec: The input parameters are '||
2978 ' p_v_loan_number : ' ||p_v_loan_number ||
2979 ' p_v_change_record_typ : ' ||p_v_change_record_typ ||
2980 ' p_n_disb_num : ' ||p_n_disb_num ||
2981 ' p_v_send_record_txt : ' ||p_v_send_record_txt
2982 );
2983
2984 l_v_loan_number := p_v_loan_number ;
2985 l_v_change_record_typ := p_v_change_record_typ ;
2986 l_n_disb_num := p_n_disb_num ;
2987 l_v_send_record_txt := p_v_send_record_txt ;
2988
2989 FOR rec_c_sl_clchsn_dtls IN c_sl_clchsn_dtls (
2990 cp_v_loan_number => l_v_loan_number ,
2991 cp_v_change_record_typ => l_v_change_record_typ ,
2992 cp_n_disb_num => l_n_disb_num
2993 )
2994 LOOP
2995 log_to_fnd(p_v_module => 'proc_update_clchsn_dtls_rec',
2996 p_v_string => 'invoking igf_sl_clchsn_dtls_pkg.update_row for change send id ='||rec_c_sl_clchsn_dtls.clchgsnd_id
2997 );
2998
2999 igf_sl_clchsn_dtls_pkg.update_row (
3000 x_rowid => rec_c_sl_clchsn_dtls.row_id ,
3001 x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
3002 x_award_id => rec_c_sl_clchsn_dtls.award_id ,
3003 x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
3004 x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
3005 x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
3006 x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
3007 x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
3008 x_status_code => 'S' ,
3009 x_status_date => rec_c_sl_clchsn_dtls.status_date ,
3010 x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
3011 x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
3012 x_new_value_txt => rec_c_sl_clchsn_dtls.new_value_txt ,
3013 x_old_date => rec_c_sl_clchsn_dtls.old_date ,
3014 x_new_date => rec_c_sl_clchsn_dtls.new_date ,
3015 x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
3016 x_new_amt => rec_c_sl_clchsn_dtls.new_amt ,
3017 x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
3018 x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
3019 x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
3020 x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
3021 x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
3022 x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
3023 x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
3024 x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
3025 x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
3026 x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
3027 x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
3028 x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
3029 x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
3030 x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
3031 x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
3032 x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
3033 x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
3034 x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
3035 x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
3036 x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
3037 x_validation_edit_txt => rec_c_sl_clchsn_dtls.validation_edit_txt ,
3038 x_send_record_txt => l_v_send_record_txt ,
3039 x_mode => 'R'
3040 );
3041 log_to_fnd(p_v_module => 'proc_update_clchsn_dtls_rec',
3042 p_v_string => 'Call out to igf_sl_clchsn_dtls_pkg.update_row successful for change send id ='||rec_c_sl_clchsn_dtls.clchgsnd_id
3043 );
3044
3045 END LOOP;
3046
3047 END proc_update_clchsn_dtls_rec;
3048
3049 END igf_sl_cl_chg_file;