[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_CHG_ACK
Source
1 PACKAGE BODY igf_sl_dl_chg_ack AS
2 /* $Header: IGFSL06B.pls 120.2 2006/04/19 08:41:21 bvisvana noship $ */
3
4 /*************************************************************
5 Created By : venagara
6 Date Created On : 2000/11/29
7 Purpose :
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 (reverse chronological order - newest change first)
12 -------------------------------------------------------------------------
13 -- museshad 20-Feb-2006 Bug 5045784 - SQL Repository Issue.
14 -- In dl_chg_ack(), modified cursor cur_get_fed_fund
15 -- for better performance.
16 -----------------------------------------------------------------------------------
17 -- veramach 29-Jan-2004 bug 3408092 added 2004-2005 in p_dl_version checks
18 -----------------------------------------------------------------------------------
19 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
20 -- The DUNS_BORW_LENDER_ID,
21 -- DUNS_GUARNT_ID,
22 -- DUNS_LENDER_ID,
23 -- DUNS_RECIP_ID columns are osboleted from the
24 -- igf_sl_lor_loc_all table.
25 ---------------------------------------------------------------------------
26 gmuralid 03-07-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
27 Added legacy record flag as parameter to
28 igf_sl_loans_pkg
29
30 vvutukur 21-Feb-2003 Enh#2758823.FA117 Build. Modified procedure dl_load_data.
31 ***************************************************************/
32
33
34 g_lor_loc_rec igf_sl_lor_loc%ROWTYPE;
35 g_dl_version igf_lookups_view.lookup_code%TYPE;
36
37 FILE_NOT_LOADED exception;
38 FILE_ALREADY_LOADED exception;
39 NOT_PHASE_IN_PARTICIPANT exception ;
40
41
42 /* FORWARD DECLARATION OF PRIVATE PROCEDURES */
43 PROCEDURE upd_dl_chg_resp(p_dbth_id igf_sl_dl_batch.dbth_id%TYPE,
44 p_loan_number igf_sl_loans.loan_number%TYPE,
45 p_status igf_sl_dl_chg_resp.status%TYPE);
46
47 PROCEDURE upd_loan_record(p_loan_number IN igf_sl_loans.loan_number%TYPE,
48 p_loan_status IN igf_sl_loans_all.loan_status%TYPE,
49 p_loan_chg_status IN igf_sl_loans.loan_chg_status%TYPE,
50 p_rec_present OUT NOCOPY VARCHAR2);
51
52 PROCEDURE upd_lor_loc_record(p_loan_id igf_sl_lor.loan_id%TYPE,
53 p_loan_chg_status igf_sl_loans.loan_chg_status%TYPE);
54
55
56
57
58 -- Procedure which loads the Data loaded by SQL*Loader into the
59 -- temp table, parses it as according to the DL File Spec and
60 -- loads it into the DL interface tables.
61
62 PROCEDURE dl_load_data(p_dbth_id OUT NOCOPY igf_sl_dl_batch.dbth_id%TYPE)
63 AS
64 /*************************************************************
65 Created By : venagara
66 Date Created On : 2000/11/22
67 Purpose :
68 Know limitations, enhancements or remarks
69 Change History
70 Bug : 2255281 DL Version to checked for DL Programs
71 Who When What
72 vvutukur 21-Feb-2003 Enh#2758823.FA117 Build. Modified the if condition to include 03-04 removing 02-03.
73 ie., Changed IF g_dl_version IN ('2001-2002','2002-2003') to IF g_dl_version IN ('2002-2003','2003-2004').
74 mesriniv 19-mar-2002 Added version 2002-2003 for DL Version Check
75 (reverse chronological order - newest change first)
76 ***************************************************************/
77
78 l_temp VARCHAR2(30);
79 l_last_lort_id NUMBER;
80 l_number_rec NUMBER;
81 l_accept_rec NUMBER;
82 l_reject_rec NUMBER;
83 l_pending_rec NUMBER;
84
85 -- The fields have not been defined as tablename.field%TYPE on
86 -- purpose to feedback a proper message to the user
87 l_rec_batch_id VARCHAR2(100);
88 l_rec_message_class VARCHAR2(100);
89 l_rec_bth_creation_date VARCHAR2(100);
90 l_rec_batch_rej_code VARCHAR2(100);
91 l_rec_batch_type VARCHAR2(100);
92
93 l_rowid VARCHAR2(25);
94 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
95 l_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
96 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
97
98 -- Cursor to parse and load the Header record.
99 CURSOR c_header IS
100 SELECT substr(record_data, 23, 23) batch_id,
101 substr(record_data, 15, 8) message_class,
102 substr(record_data, 46, 16) bth_creation_date,
103 substr(record_data, 60, 2) batch_rej_code,
104 substr(record_data, 23, 2) batch_type
105 FROM igf_sl_load_file_t
106 WHERE lort_id = 1
107 AND record_data like 'DL HEADER%'
108 AND file_type = 'DL_CHG_ACK';
109
110 -- Cursor to parse and load the Trailer record.
111 CURSOR c_trailer IS
112 SELECT lort_id last_lort_id,
113 substr(record_data,15,7) number_rec,
114 substr(record_data,22,5) accept_rec,
115 substr(record_data,27,5) reject_rec,
116 substr(record_data,32,5) pending_rec
117 FROM igf_sl_load_file_t
118 WHERE lort_id = (select max(lort_id) FROM igf_sl_load_file_t)
119 AND record_data like 'DL TRAILER%'
120 AND file_type = 'DL_CHG_ACK';
121
122 -- Cursor to check the Batch Details, if the same Batch is loaded again.
123 CURSOR c_dbth(l_batch_id igf_sl_dl_batch.batch_id%TYPE,
124 l_message_class igf_sl_dl_batch.message_class%TYPE,
125 l_bth_creation_date VARCHAR2,
126 l_batch_type igf_sl_dl_batch.batch_type%TYPE)
127 IS
128 SELECT 'x' FROM igf_sl_dl_batch
129 WHERE batch_id = l_batch_id
130 AND message_class = l_message_class
131 AND to_char(bth_creation_date,'YYYYMMDDHH24MISS') = l_bth_creation_date
132 AND batch_type = l_batch_type;
133
134
135 -- Get the details of award year
136 CURSOR c_get_award_year( p_dl_version igf_lookups_view.lookup_code%TYPE )
137 IS
138 SELECT ci_cal_type, ci_sequence_number
139 FROM igf_sl_dl_setup
140 WHERE dl_version = p_dl_version ;
141
142 l_award_year c_get_award_year%ROWTYPE;
143
144 BEGIN
145
146 -- Assuming that Header and Trailer record format does not change
147 -- since the header record contains Message Class Info, which
148 -- indicates the version of the File.
149
150
151 /*********** Validate the File and Load it ***************/
152
153 -- Get the Header details
154 OPEN c_header;
155 FETCH c_header INTO l_rec_batch_id,
156 l_rec_message_class,
157 l_rec_bth_creation_date,
158 l_rec_batch_rej_code,
159 l_rec_batch_type;
160 IF c_header%NOTFOUND THEN
161 CLOSE c_header;
162 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
163 -- Message : Response File uploaded is not complete.
164 igs_ge_msg_stack.add;
165 RAISE FILE_NOT_LOADED;
166 END IF;
167 CLOSE c_header;
168
169
170 -- Check whether the File is valid/Not. (ie whether any wrong file is loaded)
171 igf_sl_gen.get_dl_batch_details(l_rec_message_class, l_rec_batch_type,
172 g_dl_version, l_dl_file_type, l_dl_loan_catg);
173 IF g_dl_version = 'INVALID-FILE'
174 OR l_dl_file_type = 'INVALID-FILE'
175 OR l_dl_file_type <> 'DL_CHG_ACK' THEN
176 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
177 -- Message : This is not a valid file
178 igs_ge_msg_stack.add;
179 RAISE FILE_NOT_LOADED;
180 END IF;
181
182 -- pssahni 07-Feb-2005
183 -- Check if this is a phase-in participant
184 OPEN c_get_award_year(g_dl_version);
185 FETCH c_get_award_year INTO l_award_year;
186 IF c_get_award_year%FOUND THEN
187 IF (igf_sl_dl_validation.check_full_participant (l_award_year.ci_cal_type, l_award_year.ci_sequence_number,'DL')) THEN
188 RAISE NOT_PHASE_IN_PARTICIPANT;
189 END IF;
190 END IF;
191 CLOSE c_get_award_year;
192
193
194 -- This is an Direct Loan Change Acknowledgment File
195 fnd_message.set_name('IGF','IGF_SL_DL_CHG_ACK_FILE');
196 fnd_file.put_line(FND_FILE.LOG,FND_MESSAGE.GET);
197 fnd_file.put_line(fnd_file.log,' ');
198
199
200 -- Check whether the File was Fully transferred.
201 -- Get the record details in the File.
202 OPEN c_trailer;
203 FETCH c_trailer into l_last_lort_id, l_number_rec, l_accept_rec,
204 l_reject_rec, l_pending_rec;
205 IF c_trailer%NOTFOUND THEN
206 CLOSE c_trailer;
207 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
208 igs_ge_msg_stack.add;
209 RAISE FILE_NOT_LOADED;
210 END IF;
211 CLOSE c_trailer;
212
213
214 -- Check if the entire batch was rejected for any reason or not
215 IF LTRIM(RTRIM(l_rec_batch_rej_code)) IS NOT NULL THEN
216 fnd_message.set_name('IGF','IGF_GE_BATCH_REJECTED');
217 fnd_message.set_token('BATCH', l_rec_batch_id);
218 fnd_message.set_token('REASON', igf_aw_gen.lookup_desc('IGF_SL_DL_BATCH_REJ',l_rec_batch_rej_code));
219 -- Message : Batch #BATCH was rejected. Reason : #REASON.
220 igs_ge_msg_stack.add;
221 RAISE FILE_NOT_LOADED;
222 END IF;
223
224 -- Check if the File already Loaded earlier or not
225 OPEN c_dbth(l_rec_batch_id, l_rec_message_class, l_rec_bth_creation_date, l_rec_batch_type);
226 FETCH c_dbth INTO l_temp;
227 IF c_dbth%NOTFOUND THEN
228 CLOSE c_dbth;
229 ELSE
230 CLOSE c_dbth;
231 RAISE FILE_ALREADY_LOADED;
232 END IF;
233
234 l_rowid := NULL;
235 -- Insert the Batch Details into the igf_sl_dl_batch table.
236 igf_sl_dl_batch_pkg.insert_row (
237 x_mode => 'R',
238 x_rowid => l_rowid,
239 X_dbth_id => l_dbth_id,
240 X_batch_id => l_rec_batch_id,
241 X_message_class => l_rec_message_class,
242 X_bth_creation_date => to_date(l_rec_bth_creation_date,'YYYYMMDDHH24MISS'),
243 X_batch_rej_code => l_rec_batch_rej_code,
244 X_end_date => NULL,
245 X_batch_type => l_rec_batch_type,
246 X_send_resp => 'R',
247 X_status => 'N'
248 );
249
250
251 /*************** Load Transaction records ***************/
252
253
254 DECLARE
255 l_actual_rec NUMBER DEFAULT 0;
256 l_lor_resp_num NUMBER;
257 CURSOR c_trans IS
258 SELECT record_data FROM igf_sl_load_file_t
259 WHERE lort_id between 2 AND (l_last_lort_id-1)
260 AND file_type = 'DL_CHG_ACK';
261 BEGIN
262
263 --Added extra check for 2002-2003 as per Enh Bug 2255281
264 IF g_dl_version IN ('2002-2003','2003-2004','2004-2005') THEN
265
266 FOR orec IN c_trans LOOP
267 l_actual_rec := l_actual_rec + 1;
268 FOR l_incr in 1..10 LOOP
269
270 IF RTRIM(substr(orec.record_data, 24+(56*(l_incr-1)), 4)) IS NOT NULL THEN
271 l_rowid := NULL;
272 igf_sl_dl_chg_resp_pkg.insert_row (
273 x_mode => 'R',
274 x_rowid => l_rowid,
275 X_resp_num => l_lor_resp_num,
276 X_dbth_id => l_dbth_id,
277 X_batch_id => RTRIM(substr(orec.record_data, 590,23) ),
278 X_loan_number => RTRIM(substr(orec.record_data, 1,21) ),
279 X_chg_code => RTRIM(substr(orec.record_data, 24+(56*(l_incr-1)), 4) ),
280 X_reject_code => RTRIM(substr(orec.record_data, 78+(56*(l_incr-1)), 2) ),
281 X_new_value => RTRIM(substr(orec.record_data, 28+(56*(l_incr-1)), 50) ),
282 X_loan_ident_err_code => RTRIM(substr(orec.record_data, 22,2)),
283 X_status => 'N'
284 );
285 END IF;
286 END LOOP;
287 END LOOP;
288 IF l_actual_rec <> l_number_rec THEN
289 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
290 -- Message : The Actual Number of records does not match with the one mentioned in the trailer
291 igs_ge_msg_stack.add;
292 RAISE FILE_NOT_LOADED;
293 END IF;
294
295 END IF; -- End of condition for VERSION.
296
297 END;
298
299 p_dbth_id := l_dbth_id;
300
301
302 EXCEPTION
303 WHEN FILE_NOT_LOADED THEN
304 RAISE;
305 WHEN FILE_ALREADY_LOADED THEN
306 RAISE;
307 WHEN NOT_PHASE_IN_PARTICIPANT THEN
308 RAISE;
309 WHEN OTHERS THEN
310 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
311 fnd_message.set_token('NAME','igf_sl_dl_chg_ack.dl_load_data'||sqlerrm);
312 igs_ge_msg_stack.add;
313 app_exception.raise_exception;
314 END dl_load_data;
315
316
317
318
319 /* MAIN PROCEDURE */
320 PROCEDURE dl_chg_ack(errbuf OUT NOCOPY VARCHAR2,
321 retcode OUT NOCOPY NUMBER,
322 p_org_id IN NUMBER )
323 AS
324 /*************************************************************
325 Created By : venagara
326 Date Created On : 2000/11/29
327 Purpose :
328 Know limitations, enhancements or remarks
329 Change History:
330 Bug:- 2383350 Loan Cancellation
331 Who When What
332 museshad 20-Feb-2006 Bug 5045784 - SQL Repository Issue.
333 Modified cursor cur_get_fed_fund for better
334 performance.
335
336 mesriniv 28-may-2002 Code added to handle Cancelling a Loan
337
338 (reverse chronological order - newest change first)
339 ***************************************************************/
340
341 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
342 l_batch_type igf_sl_dl_batch.batch_type%TYPE;
343 l_rec_present VARCHAR2(10);
344 l_stat VARCHAR2(30);
345 l_loan_chg_status igf_lookups_view.lookup_code%TYPE;
346 l_sub_col_heading VARCHAR2(1000) DEFAULT NULL;
347
348 CURSOR cur_batch IS
349 SELECT igf_sl_dl_batch.* FROM igf_sl_dl_batch
350 WHERE dbth_id = l_dbth_id
351 AND status = 'N';
352
353 -- Get the Count of Records which have Reject Codes, for each change SENT
354 -- OR Count of Records which have Errors for the ENTIRE Line SENT
355 CURSOR cur_chg_resp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE) IS
356 SELECT loan_number,
357 NVL(SUM(DECODE(loan_ident_err_code,NULL,0,1)),0) loan_err_count,
358 NVL(SUM(DECODE(reject_code,NULL,1,0)),0) accept_count,
359 NVL(SUM(DECODE(reject_code,NULL,0,1)),0) reject_count
360 FROM igf_sl_dl_chg_resp
361 WHERE dbth_id = l_dbth_id
362 AND status = 'N'
363 GROUP BY loan_number;
364
365 -- Get the fed fund code for the loan number
366 CURSOR cur_get_fed_fund(p_loan_number igf_sl_loans_all.loan_number%TYPE) IS
367 SELECT fcat.fed_fund_code
368 FROM igf_sl_loans_all loan,
369 igf_aw_award_all awd,
370 igf_aw_fund_mast_all fmast,
371 igf_aw_fund_cat_all fcat
372 WHERE
373 loan.award_id = awd.award_id AND
374 awd.fund_id = fmast.fund_id AND
375 fcat.fund_code = fmast.fund_code AND
376 loan.loan_number = p_loan_number;
377
378 --Added code as part of Bug :- 2383350 Loan Cancellation
379 --Check if the received file has the change code as LOAN_AMT_ACCEPTED
380 --for the particular Loan Number
381 CURSOR cur_get_loan_accp_amt(p_loan_number igf_sl_loans_all.loan_number%TYPE,
382 p_dbth_id igf_sl_dl_batch.dbth_id%TYPE,
383 p_loan_catg igf_sl_dl_chg_fld.loan_catg%TYPE
384 ) IS
385 SELECT resp.new_value
386 FROM igf_sl_dl_chg_resp resp,igf_sl_dl_chg_fld chgfld
387 WHERE loan_number =p_loan_number
388 AND dbth_id =p_dbth_id
389 AND status ='N'
390 AND resp.chg_code = chgfld.chg_code
391 AND chgfld.dl_version = g_dl_version
392 AND chgfld.fld_name ='LOAN_AMT_ACCEPTED'
393 AND chgfld.loan_catg =p_loan_catg;
394
395
396 l_loan_accp_amt igf_aw_award_all.accepted_amt%TYPE;
397 l_loan_status igf_sl_loans_all.loan_status%TYPE;
398 l_loan_catg igf_sl_dl_chg_fld.loan_catg%TYPE;
399 l_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE;
400
401 BEGIN
402
403 igf_aw_gen.set_org_id(p_org_id);
404
405 -- Load the Data into the Batch and Response Tables
406 dl_load_data(l_dbth_id);
407
408 FOR dbth_rec IN cur_batch LOOP
409
410
411 -- These are Direct Loan Change Response records.
412
413 FOR resp_rec IN cur_chg_resp(l_dbth_id) LOOP
414 l_rec_present := 'N';
415
416 IF ((resp_rec.reject_count > 0) OR (resp_rec.loan_err_count > 0)) THEN
417 l_loan_chg_status := 'R'; -- REJECTED (Code for LOAN_CHG_STATUS)
418 ELSE
419 l_loan_chg_status := 'A'; -- ACCEPTED (Code for LOAN_CHG_STATUS)
420
421 --Get the fed fund code for the loan
422 OPEN cur_get_fed_fund(resp_rec.loan_number);
423 FETCH cur_get_fed_fund INTO l_fed_fund_code;
424 CLOSE cur_get_fed_fund;
425
426 IF l_fed_fund_code ='DLP' THEN
427 l_loan_catg :='DL_PLUS';
428 ELSE
429 l_loan_catg :='DL_STAFFORD';
430 END IF;
431
432 --Fetch the Response data for the particular loan number
433 --for which the CHANGE CODE Field Name is LOAN_ACCEPTED_AMT
434 --The field name and corr Change Code are present in IGF_SL_DL_CHG_FLD
435 --for the current loan category and also the file format version
436 --Seeded Data should be available
437
438 OPEN cur_get_loan_accp_amt(resp_rec.loan_number,l_dbth_id,l_loan_catg);
439 FETCH cur_get_loan_accp_amt INTO l_loan_accp_amt;
440
441 --This ensures that seeded data is present and also the
442 --Loan Accepted Amt is received from LOC
443 IF cur_get_loan_accp_amt%FOUND THEN
444
445 IF NVL(l_loan_accp_amt,0) = 0 THEN
446 l_loan_status :='C';
447 END IF;
448
449 END IF;
450 CLOSE cur_get_loan_accp_amt;
451
452 END IF;
453
454
455 -- Update the IGF_SL_LOANS record with the Loan Change Status
456 upd_loan_record(resp_rec.loan_number,l_loan_status,l_loan_chg_status, l_rec_present);
457
458 -- If the Loan# being uploaded is not a Valid Loan#
459 IF l_rec_present = 'N' THEN
460 fnd_message.set_name('IGF','IGF_SL_NO_LOAN_NUMBER');
461 fnd_message.set_token('LOAN_NUMBER',resp_rec.loan_number);
462 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
463 -- Message : Loan ID #LOAN_NUMBER does not exist in the Financial Aid System
464
465 -- Update the Loan Change Response Record as NOT UPLOADED, Since it is INVALID
466 upd_dl_chg_resp(l_dbth_id, resp_rec.loan_number, 'I');
467
468 ELSE
469 -- If the Loan Number is a Valid Loan-number
470
471 -- If the Loan Changes were Rejected, Then
472 -- Update the Loan Change Status of the Loan Application record
473 -- and also display and insert the reject codes.
474 IF l_loan_chg_status = 'R' THEN
475
476 -- Show "Changes for Loan ID #LOAN_NUMBER were #LOAN_CHG_STATUS" on the Log File.
477 fnd_message.set_name('IGF','IGF_SL_LOAN_CHG_STATUS');
478 fnd_message.set_token('LOAN_NUMBER',resp_rec.loan_number);
479 fnd_message.set_token('LOAN_STATUS',
480 igf_aw_gen.lookup_desc('IGF_SL_REC_STATUS',l_loan_chg_status));
481 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
482
483
484 /************* Show Reject Details on the LOG File ***************/
485 DECLARE
486
487 -- Loan Error for the ENTIRE line are retrieved here.
488 CURSOR cur_loan_err_resp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE,
489 l_loan_number igf_sl_loans.loan_number%TYPE) IS
490 SELECT DISTINCT rchg.loan_ident_err_code
491 FROM igf_sl_dl_chg_resp rchg
492 WHERE rchg.dbth_id = l_dbth_id
493 AND rchg.loan_number = l_loan_number
494 AND rchg.loan_ident_err_code IS NOT NULL
495 AND rchg.status = 'N';
496
497 -- Reject Codes for Individual Field Changes are retrieved here.
498 CURSOR cur_chg_rej_resp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE,
499 l_loan_number igf_sl_loans.loan_number%TYPE) IS
500 SELECT fld_name, cchgv.description fld_desc, rchg.reject_code, rchg.new_value
501 FROM igf_sl_dl_chg_resp rchg,
502 igf_sl_dl_chg_fld_v cchgv
503 WHERE rchg.dbth_id = l_dbth_id
504 AND rchg.loan_number = l_loan_number
505 AND rchg.reject_code IS NOT NULL
506 AND rchg.status = 'N'
507 AND rchg.chg_code = cchgv.chg_code
508 AND cchgv.dl_version = g_dl_version;
509
510 BEGIN
511
512 -- Delete the Edit Records from the table, with "Errors occurred during Change Resp"
513 igf_sl_edit.delete_edit(resp_rec.loan_number, 'H');
514
515 -- If Reject codes are present for each LINE, then show those.
516 IF resp_rec.loan_err_count > 0 THEN
517
518 FOR lrec IN cur_loan_err_resp(l_dbth_id, resp_rec.loan_number) LOOP
519
520 -- Display on the LOG File.
521 fnd_file.put_line(FND_FILE.LOG,
522 igf_aw_gen.lookup_desc('IGF_SL_DL_CHG_REJ_CODES', lrec.loan_ident_err_code));
523
524 -- Insert Loan Error Code details into the edit_report table.
525 igf_sl_edit.insert_edit(resp_rec.loan_number,
526 'H', -- EDITS from Change Response
527 'IGF_SL_DL_CHG_REJ_CODES',
528 lrec.loan_ident_err_code,
529 NULL,
530 NULL);
531 END LOOP;
532
533 END IF; -- END of condition "resp_rec.reject_count > 0 "
534
535 -- If Reject codes are present for each field, then show those.
536 IF resp_rec.reject_count > 0 THEN
537
538 -- Show a Sub-heading on the Log File, to show the reject descriptions.
539 IF l_sub_col_heading IS NULL THEN
540 l_sub_col_heading := RPAD(igf_aw_gen.lookup_desc('IGF_SL_GEN','FIELDS'),50)
541 ||' '
542 ||RPAD(igf_aw_gen.lookup_desc('IGF_SL_GEN','VALUES'),50)
543 ||' '
544 ||igf_aw_gen.lookup_desc('IGF_SL_GEN','REJ_REASON');
545 END IF;
546 fnd_file.put_line(FND_FILE.LOG, l_sub_col_heading);
547 fnd_file.put_line(FND_FILE.LOG, RPAD('-',50,'-')||' '||RPAD('-',50,'-')||' '
548 ||RPAD('-',100,'-') );
549
550 FOR rrec IN cur_chg_rej_resp(l_dbth_id, resp_rec.loan_number) LOOP
551
552 -- Display on the LOG File.
553 fnd_file.put_line(FND_FILE.LOG, RPAD(rrec.fld_desc,50) ||' '
554 ||RPAD(rrec.new_value,50)||' '
555 ||igf_aw_gen.lookup_desc('IGF_SL_DL_CHG_REJ_CODES',rrec.reject_code));
556
557 -- Insert Reject details into the edit_report table.
558 igf_sl_edit.insert_edit(resp_rec.loan_number,
559 'H', -- EDITS from Change Response
560 'IGF_SL_DL_CHG_REJ_CODES',
561 rrec.reject_code,
562 rrec.fld_name,
563 rrec.new_value);
564 END LOOP;
565
566 END IF; -- END of condition "resp_rec.reject_count > 0 "
567
568 END;
569
570 END IF; -- End of condition for "l_loan_chg_status = 'R' "
571
572 -- Update the Loan Change Response Record as UPLOADED, as the Loan ID exists
573 upd_dl_chg_resp(l_dbth_id, resp_rec.loan_number, 'Y');
574
575 END IF; -- End of condition for "l_rec_present = 'N' "
576
577
578 -- This is done to get a seperator of 2 Lines between each Loan rejected.
579 IF l_loan_chg_status <> 'A' THEN
580 fnd_file.put_line(FND_FILE.LOG,'');
581 fnd_file.put_line(FND_FILE.LOG,'');
582 END IF;
583
584 END LOOP; -- End of igf_sl_dl_chg_resp table LOOP.
585
586
587 -- Update the DL_BATCH record as Successfully Uploaded.
588 igf_sl_dl_batch_pkg.update_row (
589 X_Mode => 'R',
590 x_rowid => dbth_rec.row_id,
591 x_dbth_id => dbth_rec.dbth_id,
592 x_batch_id => dbth_rec.batch_id,
593 x_message_class => dbth_rec.message_class,
594 x_bth_creation_date => dbth_rec.bth_creation_date,
595 x_batch_rej_code => dbth_rec.batch_rej_code,
596 x_end_date => dbth_rec.end_date,
597 x_batch_type => dbth_rec.batch_type,
598 x_send_resp => dbth_rec.send_resp,
599 x_status => 'Y'
600 );
601
602
603 END LOOP; -- End of Batch ID "FOR LOOP"
604
605 COMMIT;
606
607 EXCEPTION
608 WHEN FILE_NOT_LOADED THEN
609 ROLLBACK;
610 retcode := 2;
611 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
612 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
613 WHEN FILE_ALREADY_LOADED THEN
614 ROLLBACK;
615 retcode := 2;
616 errbuf := fnd_message.get_string('IGF','IGF_GE_BATCH_ALEARDY_LOADED');
617 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
618
619 WHEN NOT_PHASE_IN_PARTICIPANT THEN
620 ROLLBACK;
621 fnd_message.set_name('IGF','IGF_SL_COD_NO_CHG_ACK');
622 fnd_file.put_line(FND_FILE.LOG,FND_MESSAGE.GET);
623 fnd_file.put_line(fnd_file.log,' ');
624
625 WHEN OTHERS THEN
626 ROLLBACK;
627 retcode := 2;
628 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
629 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
630 END dl_chg_ack;
631
632
633
634
635 PROCEDURE upd_dl_chg_resp(p_dbth_id igf_sl_dl_batch.dbth_id%TYPE,
636 p_loan_number igf_sl_loans.loan_number%TYPE,
637 p_status igf_sl_dl_chg_resp.status%TYPE)
638 IS
639 /*************************************************************
640 Created By : venagara
641 Date Created On : 2000/11/22
642 Purpose :
643 Know limitations, enhancements or remarks
644 Change History
645 Who When What
646
647 (reverse chronological order - newest change first)
648 ***************************************************************/
649
650 lv_row_id VARCHAR2(25);
651 CURSOR c_tbh_cur IS
652 SELECT igf_sl_dl_chg_resp.* FROM igf_sl_dl_chg_resp
653 WHERE dbth_id = p_dbth_id
654 AND loan_number = p_loan_number;
655 BEGIN
656
657 FOR tbh_rec in c_tbh_cur LOOP
658
659 tbh_rec.status := p_status;
660
661 igf_sl_dl_chg_resp_pkg.update_row (
662 X_Mode => 'R',
663 x_rowid => tbh_rec.row_id,
664 x_resp_num => tbh_rec.resp_num,
665 x_dbth_id => tbh_rec.dbth_id,
666 x_batch_id => tbh_rec.batch_id,
667 x_loan_number => tbh_rec.loan_number,
668 x_chg_code => tbh_rec.chg_code,
669 x_reject_code => tbh_rec.reject_code,
670 x_new_value => tbh_rec.new_value,
671 x_loan_ident_err_code => tbh_rec.loan_ident_err_code,
672 x_status => tbh_rec.status
673 );
674 END LOOP;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
679 fnd_message.set_token('NAME','igf_sl_dl_chg_ack.upd_dl_chg_resp');
680 igs_ge_msg_stack.add;
681 app_exception.raise_exception;
682 END upd_dl_chg_resp;
683
684
685
686 -- Update the IGF_SL_LOANS record, for the Loan-Number, for the New Change Status
687 -- If the Loan_number is not present in OFA, then return p_rec_present as 'N'
688 PROCEDURE upd_loan_record(p_loan_number IN igf_sl_loans.loan_number%TYPE,
689 p_loan_status IN igf_sl_loans_all.loan_status%TYPE,
690 p_loan_chg_status IN igf_sl_loans.loan_chg_status%TYPE,
691 p_rec_present OUT NOCOPY VARCHAR2)
692 AS
693 /*************************************************************
694 Created By : venagara
695 Date Created On : 2000/11/22
696 Purpose :
697 Know limitations, enhancements or remarks
698 Change History
699 Who When What
700 agairola 15-Mar-2002 Modified the Update Row call
701 of the IGF_SL_LOANS_PKG to include the
702 Borrower Determination as part of Refunds DLD 2144600
703 (reverse chronological order - newest change first)
704 ***************************************************************/
705
706 l_row_id VARCHAR2(25);
707 CURSOR c_tbh_cur IS
708 SELECT igf_sl_loans.* FROM igf_sl_loans
709 WHERE loan_number = p_loan_number
710 FOR UPDATE OF loan_id NOWAIT;
711
712 BEGIN
713 p_rec_present := 'N';
714
715 FOR tbh_rec in c_tbh_cur LOOP
716
717 -- If the Loan-Number is a Valid Loan-Number
718 p_rec_present := 'Y';
719
720 tbh_rec.loan_chg_status := p_loan_chg_status;
721 tbh_rec.loan_chg_status_date := TRUNC(SYSDATE);
722
723 --Message in the Log File which specifies that a Loan is Cancelled and Update Loan Status and Date
724 IF p_loan_status ='C' THEN
725 tbh_rec.loan_status:=p_loan_status;
726 tbh_rec.loan_status_date := TRUNC(SYSDATE);
727 fnd_file.put_line(fnd_file.log,' ');
728 fnd_message.set_name('IGF','IGF_SL_LOAN_CANCELLED');
729 fnd_message.set_token('LOAN_NO',tbh_rec.loan_number);
730 --Loan Number tbh_rec.loan_number has been cancelled.
731 fnd_file.put_line(fnd_file.log,fnd_message.get);
732 END IF;
733
734
735
736 -- Modified the Update Row call for the Borrower Determination
737 -- as part of the Refunds DLD 2144600
738 igf_sl_loans_pkg.update_row (
739 X_Mode => 'R',
740 x_rowid => tbh_rec.row_id,
741 x_loan_id => tbh_rec.loan_id,
742 x_award_id => tbh_rec.award_id,
743 x_seq_num => tbh_rec.seq_num,
744 x_loan_number => tbh_rec.loan_number,
745 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
746 x_loan_per_end_date => tbh_rec.loan_per_end_date,
747 x_loan_status => tbh_rec.loan_status,
748 x_loan_status_date => tbh_rec.loan_status_date,
749 x_loan_chg_status => tbh_rec.loan_chg_status,
750 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
751 x_active => tbh_rec.active,
752 x_active_date => tbh_rec.active_date,
753 x_borw_detrm_code => tbh_rec.borw_detrm_code,
754 x_legacy_record_flag => NULL,
755 x_external_loan_id_txt => tbh_rec.external_loan_id_txt
756
757 );
758
759 -- Update the LOR LOC record, to reflect the incoming information.
760 upd_lor_loc_record(tbh_rec.loan_id, p_loan_chg_status);
761
762 END LOOP;
763
764 EXCEPTION
765 WHEN others THEN
766 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
767 fnd_message.set_token('NAME','igf_sl_dl_chg_ack.upd_loan_record');
768 igs_ge_msg_stack.add;
769 app_exception.raise_exception;
770 END upd_loan_record;
771
772
773
774 -- Update the IGF_SL_LOR_LOC record for the New Loan Change Status, for the passed Loan-Number
775 PROCEDURE upd_lor_loc_record(p_loan_id igf_sl_lor.loan_id%TYPE,
776 p_loan_chg_status igf_sl_loans.loan_chg_status%TYPE)
777 AS
778 /*************************************************************
779 Created By : venagara
780 Date Created On : 2000/11/22
781 Purpose :
782 Know limitations, enhancements or remarks
783 Change History
784 Who When What
785 -------------------------------------------------------------------------
786 -- bvisvana 10-Apr-2006 Build FA 161. TBH Impact change
787 -- in igf_sl_lor_loc_pkg.update_row()
788 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
789 -- The DUNS_BORW_LENDER_ID,
790 -- DUNS_GUARNT_ID,
791 -- DUNS_LENDER_ID,
792 -- DUNS_RECIP_ID columns are osboleted from the
793 -- igf_sl_lor_loc_all table.
794 ---------------------------------------------------------------------------
795 (reverse chronological order - newest change first)
796 ***************************************************************/
797 CURSOR c_tbh_cur IS
798 SELECT igf_sl_lor_loc.* FROM igf_sl_lor_loc
799 WHERE loan_id = p_loan_id;
800 BEGIN
801
802 FOR tbh_rec in c_tbh_cur LOOP
803
804 tbh_rec.loan_chg_status := p_loan_chg_status;
805 tbh_rec.loan_chg_status_date := TRUNC(SYSDATE);
806
807 igf_sl_lor_loc_pkg.update_row (
808 X_Mode => 'R',
809 x_rowid => tbh_rec.row_id,
810 x_loan_id => tbh_rec.loan_id,
811 x_origination_id => tbh_rec.origination_id,
812 x_loan_number => tbh_rec.loan_number,
813 x_loan_type => tbh_rec.loan_type,
814 x_loan_amt_offered => tbh_rec.loan_amt_offered,
815 x_loan_amt_accepted => tbh_rec.loan_amt_accepted,
816 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
817 x_loan_per_end_date => tbh_rec.loan_per_end_date,
818 x_acad_yr_begin_date => tbh_rec.acad_yr_begin_date,
819 x_acad_yr_end_date => tbh_rec.acad_yr_end_date,
820 x_loan_status => tbh_rec.loan_status,
821 x_loan_status_date => tbh_rec.loan_status_date,
822 x_loan_chg_status => tbh_rec.loan_chg_status,
823 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
824 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
825 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
826 x_active => tbh_rec.active,
827 x_active_date => tbh_rec.active_date,
828 x_sch_cert_date => tbh_rec.sch_cert_date,
829 x_orig_status_flag => tbh_rec.orig_status_flag,
830 x_orig_batch_id => tbh_rec.orig_batch_id,
831 x_orig_batch_date => tbh_rec.orig_batch_date,
832 x_chg_batch_id => tbh_rec.chg_batch_id,
833 x_orig_ack_date => tbh_rec.orig_ack_date,
834 x_credit_override => tbh_rec.credit_override,
835 x_credit_decision_date => tbh_rec.credit_decision_date,
836 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
837 x_pnote_status => tbh_rec.pnote_status,
838 x_pnote_status_date => tbh_rec.pnote_status_date,
839 x_pnote_id => tbh_rec.pnote_id,
840 x_pnote_print_ind => tbh_rec.pnote_print_ind,
841 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
842 x_pnote_accept_date => tbh_rec.pnote_accept_date,
843 x_p_signature_code => tbh_rec.p_signature_code,
844 x_p_signature_date => tbh_rec.p_signature_date,
845 x_s_signature_code => tbh_rec.s_signature_code,
846 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
847 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
848 x_orig_fee_perct => tbh_rec.orig_fee_perct,
849 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
850 x_borw_interest_ind => tbh_rec.borw_interest_ind,
851 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
852 x_guarantee_amt => tbh_rec.guarantee_amt,
853 x_guarantee_date => tbh_rec.guarantee_date,
854 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
855 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
856 x_guarnt_status_code => tbh_rec.guarnt_status_code,
857 x_guarnt_status_date => tbh_rec.guarnt_status_date,
858 x_lend_apprv_denied_code => tbh_rec.lend_apprv_denied_code,
859 x_lend_apprv_denied_date => tbh_rec.lend_apprv_denied_date,
860 x_lend_status_code => tbh_rec.lend_status_code,
861 x_lend_status_date => tbh_rec.lend_status_date,
862 x_grade_level_code => tbh_rec.grade_level_code,
863 x_enrollment_code => tbh_rec.enrollment_code,
864 x_anticip_compl_date => tbh_rec.anticip_compl_date,
865 x_borw_lender_id => tbh_rec.borw_lender_id,
866 x_duns_borw_lender_id => NULL,
867 x_guarantor_id => tbh_rec.guarantor_id,
868 x_duns_guarnt_id => NULL,
869 x_prc_type_code => tbh_rec.prc_type_code,
870 x_rec_type_ind => tbh_rec.rec_type_ind,
871 x_cl_loan_type => tbh_rec.cl_loan_type,
872 x_cl_seq_number => tbh_rec.cl_seq_number,
873 x_last_resort_lender => tbh_rec.last_resort_lender,
874 x_lender_id => tbh_rec.lender_id,
875 x_duns_lender_id => NULL,
876 x_lend_non_ed_brc_id => tbh_rec.lend_non_ed_brc_id,
877 x_recipient_id => tbh_rec.recipient_id,
878 x_recipient_type => tbh_rec.recipient_type,
879 x_duns_recip_id => NULL,
880 x_recip_non_ed_brc_id => tbh_rec.recip_non_ed_brc_id,
881 x_cl_rec_status => tbh_rec.cl_rec_status,
882 x_cl_rec_status_last_update => tbh_rec.cl_rec_status_last_update,
883 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
884 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
885 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
886 x_mpn_confirm_code => tbh_rec.mpn_confirm_code,
887 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
888 x_appl_loan_phase_code => tbh_rec.appl_loan_phase_code,
889 x_appl_loan_phase_code_chg => tbh_rec.appl_loan_phase_code_chg,
890 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
891 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
892 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
893 x_act_interest_rate => tbh_rec.act_interest_rate,
894 x_service_type_code => tbh_rec.service_type_code,
895 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
896 x_sch_refund_amt => tbh_rec.sch_refund_amt,
897 x_sch_refund_date => tbh_rec.sch_refund_date,
898 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
899 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
900 x_p_person_id => tbh_rec.p_person_id,
901 x_p_ssn => tbh_rec.p_ssn,
902 x_p_ssn_chg_date => tbh_rec.p_ssn_chg_date,
903 x_p_last_name => tbh_rec.p_last_name,
904 x_p_first_name => tbh_rec.p_first_name,
905 x_p_middle_name => tbh_rec.p_middle_name,
906 x_p_permt_addr1 => tbh_rec.p_permt_addr1,
907 x_p_permt_addr2 => tbh_rec.p_permt_addr2,
908 x_p_permt_city => tbh_rec.p_permt_city,
909 x_p_permt_state => tbh_rec.p_permt_state,
910 x_p_permt_zip => tbh_rec.p_permt_zip,
911 x_p_permt_addr_chg_date => tbh_rec.p_permt_addr_chg_date,
912 x_p_permt_phone => tbh_rec.p_permt_phone,
913 x_p_email_addr => tbh_rec.p_email_addr,
914 x_p_date_of_birth => tbh_rec.p_date_of_birth,
915 x_p_dob_chg_date => tbh_rec.p_dob_chg_date,
916 x_p_license_num => tbh_rec.p_license_num,
917 x_p_license_state => tbh_rec.p_license_state,
918 x_p_citizenship_status => tbh_rec.p_citizenship_status,
919 x_p_alien_reg_num => tbh_rec.p_alien_reg_num,
920 x_p_default_status => tbh_rec.p_default_status,
921 x_p_foreign_postal_code => tbh_rec.p_foreign_postal_code,
922 x_p_state_of_legal_res => tbh_rec.p_state_of_legal_res,
923 x_p_legal_res_date => tbh_rec.p_legal_res_date,
924 x_s_ssn => tbh_rec.s_ssn,
925 x_s_ssn_chg_date => tbh_rec.s_ssn_chg_date,
926 x_s_last_name => tbh_rec.s_last_name,
927 x_s_first_name => tbh_rec.s_first_name,
928 x_s_middle_name => tbh_rec.s_middle_name,
929 x_s_permt_addr1 => tbh_rec.s_permt_addr1,
930 x_s_permt_addr2 => tbh_rec.s_permt_addr2,
931 x_s_permt_city => tbh_rec.s_permt_city,
932 x_s_permt_state => tbh_rec.s_permt_state,
933 x_s_permt_zip => tbh_rec.s_permt_zip,
934 x_s_permt_addr_chg_date => tbh_rec.s_permt_addr_chg_date,
935 x_s_permt_phone => tbh_rec.s_permt_phone,
936 x_s_local_addr1 => tbh_rec.s_local_addr1,
937 x_s_local_addr2 => tbh_rec.s_local_addr2,
938 x_s_local_city => tbh_rec.s_local_city,
939 x_s_local_state => tbh_rec.s_local_state,
940 x_s_local_zip => tbh_rec.s_local_zip,
941 x_s_local_addr_chg_date => tbh_rec.s_local_addr_chg_date,
942 x_s_email_addr => tbh_rec.s_email_addr,
943 x_s_date_of_birth => tbh_rec.s_date_of_birth,
944 x_s_dob_chg_date => tbh_rec.s_dob_chg_date,
945 x_s_license_num => tbh_rec.s_license_num,
946 x_s_license_state => tbh_rec.s_license_state,
947 x_s_depncy_status => tbh_rec.s_depncy_status,
948 x_s_default_status => tbh_rec.s_default_status,
949 x_s_citizenship_status => tbh_rec.s_citizenship_status,
950 x_s_alien_reg_num => tbh_rec.s_alien_reg_num,
951 x_s_foreign_postal_code => tbh_rec.s_foreign_postal_code,
952 x_pnote_batch_id => tbh_rec.pnote_batch_id,
953 x_pnote_ack_date => tbh_rec.pnote_ack_date,
954 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
955 x_award_id => tbh_rec.award_id,
956 x_base_id => tbh_rec.base_id,
957 x_document_id_txt => tbh_rec.document_id_txt,
958 x_loan_key_num => tbh_rec.loan_key_num,
959 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
960 x_fin_award_year => tbh_rec.fin_award_year,
961 x_cps_trans_num => tbh_rec.cps_trans_num,
962 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt,
963 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt,
964 x_source_entity_id_txt => tbh_rec.source_entity_id_txt,
965 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
966 x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
967 x_book_loan_amt => tbh_rec.book_loan_amt,
968 x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
969 x_s_chg_birth_date => tbh_rec.s_chg_birth_date,
970 x_s_chg_ssn => tbh_rec.s_chg_ssn,
971 x_s_chg_last_name => tbh_rec.s_chg_last_name,
972 x_b_chg_birth_date => tbh_rec.b_chg_birth_date,
973 x_b_chg_ssn => tbh_rec.b_chg_ssn,
974 x_b_chg_last_name => tbh_rec.b_chg_last_name,
975 x_note_message => tbh_rec.note_message,
976 x_full_resp_code => tbh_rec.full_resp_code,
977 x_s_permt_county => tbh_rec.s_permt_county,
978 x_b_permt_county => tbh_rec.b_permt_county,
979 x_s_permt_country => tbh_rec.s_permt_country,
980 x_b_permt_country => tbh_rec.b_permt_country,
981 x_crdt_decision_status => tbh_rec.crdt_decision_status,
982 x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
983 x_deferment_request_code => tbh_rec.deferment_request_code,
984 x_eft_authorization_code => tbh_rec.eft_authorization_code,
985 x_requested_loan_amt => tbh_rec.requested_loan_amt,
986 x_actual_record_type_code => tbh_rec.actual_record_type_code,
987 x_reinstatement_amt => tbh_rec.reinstatement_amt,
988 x_lender_use_txt => tbh_rec.lender_use_txt,
989 x_guarantor_use_txt => tbh_rec.guarantor_use_txt,
990 x_fls_approved_amt => tbh_rec.fls_approved_amt,
991 x_flu_approved_amt => tbh_rec.flu_approved_amt,
992 x_flp_approved_amt => tbh_rec.flp_approved_amt,
993 x_alt_approved_amt => tbh_rec.alt_approved_amt,
994 x_loan_app_form_code => tbh_rec.loan_app_form_code,
995 x_alt_borrower_ind_flag => tbh_rec.alt_borrower_ind_flag,
996 x_school_id_txt => tbh_rec.school_id_txt,
997 x_cost_of_attendance_amt => tbh_rec.cost_of_attendance_amt,
998 x_established_fin_aid_amount => tbh_rec.established_fin_aid_amount,
999 x_student_electronic_sign_flag => tbh_rec.student_electronic_sign_flag,
1000 x_mpn_type_flag => tbh_rec.mpn_type_flag,
1001 x_school_use_txt => tbh_rec.school_use_txt,
1002 x_expect_family_contribute_amt => tbh_rec.expect_family_contribute_amt,
1003 x_borower_electronic_sign_flag => tbh_rec.borower_electronic_sign_flag,
1004 x_borower_credit_authoriz_flag => tbh_rec.borower_credit_authoriz_flag ,
1005 x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd
1006
1007 );
1008
1009 END LOOP;
1010
1011 EXCEPTION
1012 WHEN others THEN
1013 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1014 fnd_message.set_token('NAME','igf_sl_dl_chg_ack.upd_lor_loc_record');
1015 igs_ge_msg_stack.add;
1016 app_exception.raise_exception;
1017 END upd_lor_loc_record;
1018
1019
1020 END igf_sl_dl_chg_ack;