[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_ORIG_ACK
Source
1 PACKAGE BODY igf_sl_dl_orig_ack AS
2 /* $Header: IGFSL04B.pls 120.5 2006/04/18 03:28:34 akomurav noship $ */
3 ---------------------------------------------------------------------------------
4 -- akomurav 28-Feb-2006 Build FA161.
5 -- TBH Impact change done in upd_lor_record() and
6 -- upd_lor_loc_record()
7 -----------------------------------------------------------------------------------
8 -- museshad 20-Feb-2006 Bug 5045766 - SQL Repository Issue.
9 -- In upd_lor_record(), modified cursor c_lar_cur
10 -- for better performance.
11 -----------------------------------------------------------------------------------
12 -- mnade 28-Dec-2004 #4085937 The status from ACK file for PLUS loans is
13 -- now handled in different manner.
14
15 -- svuppala 4-Nov-2004 #3416936 FA 134 TBH impacts for newly added columns
16
17 -- veramach 29-Jan-2004 bug 3408092 added 2004-2005 in dl_version checks
18 -----------------------------------------------------------------------------------
19 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
20 -- a) Impact of adding the relationship_cd
21 -- in igf_sl_lor_all table and obsoleting
22 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
23 -- GUARANTOR_ID, DUNS_GUARNT_ID,
24 -- LENDER_ID, DUNS_LENDER_ID
25 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
26 -- RECIPIENT_TYPE,DUNS_RECIP_ID
27 -- RECIP_NON_ED_BRC_ID columns.
28 -- b) Also the DUNS_BORW_LENDER_ID
29 -- DUNS_GUARNT_ID
30 -- DUNS_LENDER_ID
31 -- DUNS_RECIP_ID columns are osboleted from the
32 -- igf_sl_lor_loc_all table.
33 -----------------------------------------------------------------------------------------------------------------------------
34 -- veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
35 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
36 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
37 -- chg_batch_id,appl_send_error_codes from igf_sl_lor
38 -- Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
39 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
40 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
41 -- chg_batch_id from igf_sl_lor_loc
42 ------------------------------------------------------------------------------------------------------------------------------
43 ---------------------------------------------------------------------------
44 -- sjadhav 28-Mar-2003 Bug 2863960 added code 'X' for orign records
45 ---------------------------------------------------------------------------
46 -- sjadhav 18-Feb-2003 Bug 2758812 - FA 117 Build
47 -- Added
48 -- endorser_amount,mpn_status,
49 -- mpn_id,mpn_type,mpn_indicator fields
50 -- Changes as per TD
51 -- Added dl_credit_ack
52 ---------------------------------------------------------------------------
53 -- adhawan 19-feb-2002 Bug 2216956
54 -- Modified the tbh call of igf_sl_lor table
55 -- to include elec_mpn_ind ,
56 -- borr_sign_ind,stud_sign_ind,
57 -- borr_credit_auth_code
58 -- Modified the tbh call of
59 -- igf_sl_dl_resp table to include elec_mpn_ind
60 -- Modified in dl_load_data procedure ,
61 -- to add position reference for elec_mpn_ind
62 ---------------------------------------------------------------------------
63 -- npalanis 11/jan/2002 The process Direct Loan Origination
64 -- Acknowledgment Process is modified to pick
65 -- up disbursement records that are in planned
66 -- state,
67 -- insert records into IGF_DB_DISB_HOLDS
68 -- table with hold 'EXTERNAL' and
69 -- hold type 'SYSTEM' and also update
70 -- manual_hold_ind flag in
71 -- IGF_AW_AWD_DISB table to 'Y'.
72 -- Enh Bug No-2154941.
73 ---------------------------------------------------------------------------
74 -- ssawhney 2nd jan TBH call of IGF_AW_AWD_DISB table changed
75 -- in Stud Emp build
76 -- En Bug No 2161847
77 ---------------------------------------------------------------------------
78 -- mesriniv 13/07/2001 ENH BUG No:1806850
79 -- ENH Description -Awards Build for NOV 2001
80 -- Modified the call to
81 -- igf_aw_awd_disb_pkg.update_row
82 ---------------------------------------------------------------------------
83 -- rboddu 18/05/2001 ENH BUG NO: 1769051
84 -- ENH DESCRIPTION: Loan Processing-Nov 2001
85 -- Uploaded pnote_id field of
86 -- IGF_SL_DL_LOR_RESP table into
87 -- pnote_mpn_ind field
88 -- of IGF_SL_LOR, IGF_SL_LOR_LOC tables.
89 -- If School is configured to print,
90 -- then the pnote_status field of IGF_SL_LOR
91 -- is set to 'G' (Ready to Print)
92 ---------------------------------------------------------------------------
93 -- Created By : venagara
94 -- Date Created On : 2000/11/22
95 -- Purpose :
96 -- Know limitations, enhancements or remarks
97 ---------------------------------------------------------------------------
98
99
100 g_lor_loc_rec igf_sl_lor_loc%ROWTYPE;
101 g_sl_dl_lor_resp igf_sl_dl_lor_resp%ROWTYPE;
102 g_entry_point VARCHAR2(10) DEFAULT 'NULL';
103 file_not_loaded EXCEPTION;
104 yr_full_participant EXCEPTION;
105
106 -- FORWARD DECLARATION OF PRIVATE PROCEDURES
107 PROCEDURE upd_lor_record(p_loan_number IN igf_sl_loans.loan_number%TYPE,
108 p_process IN VARCHAR2,
109 p_rec_present OUT NOCOPY VARCHAR2,
110 p_rec_updated OUT NOCOPY VARCHAR2 );
111 PROCEDURE upd_lor_loc_record(p_loan_id igf_sl_lor.loan_id%TYPE, p_process VARCHAR2);
112
113
114
115
116 -- Procedure to Load the Data from the Data File into the Interface tables.
117 -- Before loading, it does lot of checks to ensure it is the right file
118 -- and returns the dbth_id, for further processing.
119 PROCEDURE dl_load_data(p_dbth_id OUT NOCOPY igf_sl_dl_batch.dbth_id%TYPE,
120 p_dl_version OUT NOCOPY igf_sl_dl_file_type.dl_version%TYPE,
121 p_dl_loan_catg OUT NOCOPY igf_sl_dl_file_type.dl_loan_catg%TYPE)
122 AS
123 /*************************************************************
124 Created By : venagara
125 Date Created On : 2000/11/22
126 Purpose :
127 Know limitations, enhancements or remarks
128 Change History
129 Who When What
130
131 (reverse chronological order - newest change first)
132 ***************************************************************/
133
134 l_temp VARCHAR2(30);
135 l_last_lort_id NUMBER;
136 l_number_rec NUMBER;
137 l_accept_rec NUMBER;
138 l_reject_rec NUMBER;
139 l_pending_rec NUMBER;
140
141 -- The fields have not been defined as tablename.field%TYPE on
142 -- purpose to feedback a proper message to the user
143 l_rec_batch_id VARCHAR2(100);
144 l_rec_message_class VARCHAR2(100);
145 l_rec_bth_creation_date VARCHAR2(100);
146 l_rec_batch_rej_code VARCHAR2(100);
147 l_rec_batch_type VARCHAR2(100);
148
149 l_rowid VARCHAR2(25);
150 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
151 l_dl_version igf_lookups_view.lookup_code%TYPE;
152 l_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
153 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
154
155 CURSOR c_header IS
156 SELECT RTRIM(SUBSTR(record_data, 23, 23)) batch_id,
157 RTRIM(SUBSTR(record_data, 15, 8)) message_class,
158 RTRIM(SUBSTR(record_data, 46, 16)) bth_creation_date,
159 RTRIM(SUBSTR(record_data, 60, 2)) batch_rej_code,
160 RTRIM(SUBSTR(record_data, 23, 2)) batch_type
161 FROM igf_sl_load_file_t
162 WHERE lort_id = 1
163 AND record_data LIKE 'DL HEADER%'
164 AND file_type = 'DL_ORIG_ACK';
165
166 CURSOR c_trailer IS
167 SELECT lort_id last_lort_id,
168 RTRIM(SUBSTR(record_data,15,7)) number_rec,
169 RTRIM(SUBSTR(record_data,22,5)) accept_rec,
170 RTRIM(SUBSTR(record_data,27,5)) reject_rec,
171 RTRIM(SUBSTr(record_data,32,5)) pending_rec
172 FROM igf_sl_load_file_t
173 WHERE lort_id = (SELECT MAX(lort_id) FROM igf_sl_load_file_t)
174 AND record_data LIKE 'DL TRAILER%'
175 AND file_type = 'DL_ORIG_ACK';
176
177 -- Get the award year
178 CURSOR c_get_awd_year( p_dl_version igf_lookups_view.lookup_code%TYPE)
179 IS
180 SELECT ci_cal_type, ci_sequence_number
181 FROM igf_sl_dl_setup
182 WHERE dl_version = p_dl_version;
183
184 awd_year_rec c_get_awd_year%ROWTYPE;
185
186 BEGIN
187
188 -- Assuming that Header and Trailer record format does not change
189 -- since the header record contains Message Class Info, which
190 -- indicates the version of the File.
191
192 -- Check File Uploaded
193
194 -- Get the Header details
195
196 OPEN c_header;
197 FETCH c_header INTO l_rec_batch_id,
198 l_rec_message_class,
199 l_rec_bth_creation_date,
200 l_rec_batch_rej_code,
201 l_rec_batch_type;
202 IF c_header%NOTFOUND THEN
203 CLOSE c_header;
204 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
205 fnd_file.put_line(fnd_file.log,fnd_message.get);
206 -- Message : Response File uploaded is not complete.
207 RAISE file_not_loaded;
208 END IF;
209 CLOSE c_header;
210
211
212 -- Check whether the File is valid/Not. (ie whether any wrong file is used)
213 -- File can be Origination Response For Stafford/PLUS OR a Credit Response.
214 -- Also, Check if the file is an OUTPUT File.
215 --
216 igf_sl_gen.get_dl_batch_details(l_rec_message_class,
217 l_rec_batch_type,
218 l_dl_version,
219 l_dl_file_type,
220 l_dl_loan_catg);
221
222 -- determine the system awd year and run the process only for phase in participant
223 OPEN c_get_awd_year(l_dl_version);
224 FETCH c_get_awd_year INTO awd_year_rec;
225 CLOSE c_get_awd_year;
226
227 IF igf_sl_dl_validation.check_full_participant (awd_year_rec.ci_cal_type, awd_year_rec.ci_sequence_number,'DL') THEN
228
229 IF g_entry_point = 'CREDIT' THEN
230 fnd_message.set_name('IGF','IGF_SL_COD_NO_CRDT_ACK');
231 fnd_file.put_line(fnd_file.log,fnd_message.get);
232 raise yr_full_participant;
233
234 ELSE
235 fnd_message.set_name('IGF','IGF_SL_COD_NO_ACK');
236 fnd_file.put_line(fnd_file.log,fnd_message.get);
237 raise yr_full_participant;
238 END IF;
239
240 END IF;
241
242 --
243 -- if dl orig process is run with credit files
244 -- then do not process
245 -- here g_entry_point will be 'NULL'
246 --
247
248 IF l_dl_file_type = 'DL_ORIG_ACK'
249 AND l_dl_loan_catg IN ('DL_STAFFORD','DL_PLUS')
250 AND g_entry_point = 'NULL' THEN
251 NULL;
252 ELSIF g_entry_point <> 'CREDIT' THEN
253 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
254 fnd_file.put_line(fnd_file.log,fnd_message.get);
255 -- Message : This is not a valid file
256 RAISE file_not_loaded;
257 END IF;
258
259 --
260 -- if credit process is run with dl orig files
261 -- then do not process
262 -- here g_entry_point will be 'CREDIT'
263 --
264
265 IF l_dl_file_type = 'DL_ORIG_ACK'
266 AND l_dl_loan_catg = 'DL_PLUS_CREDIT'
267 AND g_entry_point = 'CREDIT' THEN
268 NULL;
269 ELSIF g_entry_point <> 'NULL' THEN
270 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
271 fnd_file.put_line(fnd_file.log,fnd_message.get);
272 -- Message : This is not a valid file
273 RAISE file_not_loaded;
274 END IF;
275
276 IF l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS') THEN
277 -- This is an Direct Loan Origination Acknowledgment File
278 fnd_message.set_name('IGF','IGF_SL_DL_ORIG_ACK_FILE');
279 fnd_file.put_line(fnd_file.log,fnd_message.get);
280
281 ELSIF l_dl_loan_catg = 'DL_PLUS_CREDIT' THEN
282 -- This is an Direct Loan Credit Response File
283 fnd_message.set_name('IGF','IGF_SL_DL_CREDIT_ACK_FILE');
284 fnd_file.put_line(fnd_file.log,fnd_message.get);
285
286 END IF;
287
288
289 -- Check whether the File was Fully transferred.
290 -- Get the record details in the File.
291 OPEN c_trailer;
292 FETCH c_trailer into l_last_lort_id, l_number_rec, l_accept_rec,
293 l_reject_rec, l_pending_rec;
294 IF c_trailer%NOTFOUND THEN
295 CLOSE c_trailer;
296 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
297 fnd_file.put_line(fnd_file.log,fnd_message.get);
298 RAISE file_not_loaded;
299 END IF;
300 CLOSE c_trailer;
301
302
303 IF l_rec_batch_rej_code IS NOT NULL THEN
304 fnd_message.set_name('IGF','IGF_GE_BATCH_REJECTED');
305 fnd_message.set_token('BATCH', l_rec_batch_id);
306 fnd_message.set_token('REASON', igf_aw_gen.lookup_desc('IGF_SL_DL_BATCH_REJ',l_rec_batch_rej_code));
307 fnd_file.put_line(fnd_file.log,fnd_message.get);
308 RAISE file_not_loaded;
309 END IF;
310
311
312 l_rowid := NULL;
313 igf_sl_dl_batch_pkg.insert_row (
314 x_mode => 'R',
315 x_rowid => l_rowid,
316 x_dbth_id => l_dbth_id,
317 x_batch_id => l_rec_batch_id,
318 x_message_class => l_rec_message_class,
319 x_bth_creation_date => TO_DATE(l_rec_bth_creation_date,'YYYYMMDDHH24MISS'),
320 x_batch_rej_code => l_rec_batch_rej_code,
321 x_end_date => NULL,
322 x_batch_type => l_rec_batch_type,
323 x_send_resp => 'R',
324 x_status => 'N'
325 );
326
327
328 /*************** Load Transactions ********************/
329 DECLARE
330 l_actual_rec NUMBER DEFAULT 0;
331 l_lor_resp_num NUMBER;
332 CURSOR c_trans IS
333 SELECT record_data FROM igf_sl_load_file_t
334 WHERE lort_id between 2 AND (l_last_lort_id-1)
335 AND file_type = 'DL_ORIG_ACK';
336
337 BEGIN
338
339 IF l_dl_version = '2002-2003' THEN
340
341 -- File is Origination Response File For Stafford/PLUS.
342 IF l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS') THEN
343 FOR orec IN c_trans LOOP
344 l_actual_rec := l_actual_rec + 1;
345 l_rowid := NULL;
346 igf_sl_dl_lor_resp_pkg.insert_row (
347 x_mode => 'R',
348 x_rowid => l_rowid,
349 x_lor_resp_num => l_lor_resp_num,
350 x_dbth_id => l_dbth_id,
351 x_orig_batch_id => RTRIM(SUBSTR(orec.record_data, 9,23)),
352 x_loan_number => RTRIM(SUBSTR(orec.record_data,32,21)),
353 x_orig_ack_date => TO_DATE(SUBSTR(orec.record_data,1,8),'YYYYMMDD'),
354 x_orig_status_flag => RTRIM(SUBSTR(orec.record_data,53,1)),
355 x_orig_reject_reasons => RTRIM(SUBSTR(orec.record_data,54,10)),
356 x_pnote_status => RTRIM(SUBSTR(orec.record_data,64,1)),
357 x_pnote_id => RTRIM(SUBSTR(orec.record_data,90,1)),
358 x_pnote_accept_amt => NULL,
359 x_loan_amount_accepted => LTRIM(RTRIM(SUBSTR(orec.record_data,91,5))),
360 x_status => 'N',
361 x_elec_mpn_ind => NVL(RTRIM(SUBSTR(orec.record_data,65,1)),'P') --added for 2216956
362 );
363 END LOOP;
364 IF l_actual_rec <> l_number_rec THEN
365 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
366 -- Message : The Actual Number of records does not match with the one mentioned in the trailer
367 fnd_file.put_line(fnd_file.log,fnd_message.get);
368 RAISE file_not_loaded;
369 END IF;
370 END IF;
371
372 -- File is Credit Response File For PLUS.
373 IF l_dl_loan_catg = 'DL_PLUS_CREDIT' THEN
374 FOR orec IN c_trans LOOP
375 l_actual_rec := l_actual_rec + 1;
376 l_rowid := NULL;
377 igf_sl_dl_lor_crresp_pkg.insert_row (
378 x_mode => 'R',
379 x_rowid => l_rowid,
380 X_lor_resp_num => l_lor_resp_num,
381 X_dbth_id => l_dbth_id,
382 X_loan_number => RTRIM(SUBSTR(orec.record_data,9,21)),
383 X_credit_override => RTRIM(SUBSTR(orec.record_data,30,1)),
384 X_credit_decision_date => TO_DATE(SUBSTR(orec.record_data,1,8),'YYYYMMDD'),
385 X_status => 'N'
386 );
387 END LOOP;
388 IF l_actual_rec <> l_number_rec THEN
389 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
390 fnd_file.put_line(fnd_file.log,fnd_message.get);
391 RAISE file_not_loaded;
392 END IF;
393 END IF;
394
395 END IF; -- End of condition for VERSION.
396
397
398
399 IF l_dl_version IN ('2003-2004','2004-2005') THEN
400
401 -- File is Origination Response File For Stafford/PLUS.
402 IF l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS') THEN
403 FOR orec IN c_trans LOOP
404 l_actual_rec := l_actual_rec + 1;
405 l_rowid := NULL;
406 igf_sl_dl_lor_resp_pkg.insert_row (
407 x_mode => 'R',
408 x_rowid => l_rowid,
409 x_lor_resp_num => l_lor_resp_num,
410 x_dbth_id => l_dbth_id,
411 x_orig_batch_id => RTRIM(SUBSTR(orec.record_data, 9,23)),
412 x_loan_number => RTRIM(SUBSTR(orec.record_data,32,21)),
413 x_orig_ack_date => TO_DATE(SUBSTR(orec.record_data,1,8),'YYYYMMDD'),
414 x_orig_status_flag => RTRIM(SUBSTR(orec.record_data,53,1)),
415 x_orig_reject_reasons => RTRIM(SUBSTR(orec.record_data,54,10)),
416 x_pnote_status => RTRIM(SUBSTR(orec.record_data,64,1)),
417 x_pnote_id => RTRIM(SUBSTR(orec.record_data,90,1)),
418 x_pnote_accept_amt => NULL,
419 x_loan_amount_accepted => NULL,
420 x_status => 'N',
421 x_elec_mpn_ind => NVL(RTRIM(SUBSTR(orec.record_data,65,1)),'P') --added for 2216956
422 );
423 END LOOP;
424 IF l_actual_rec <> l_number_rec THEN
425 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
426 -- Message : The Actual Number of records does not match with the one mentioned in the trailer
427 fnd_file.put_line(fnd_file.log,fnd_message.get);
428 RAISE file_not_loaded;
429 END IF;
430 END IF;
431
432 -- File is Credit Response File For PLUS.
433 IF l_dl_loan_catg = 'DL_PLUS_CREDIT' THEN
434 FOR orec IN c_trans LOOP
435 l_actual_rec := l_actual_rec + 1;
436 l_rowid := NULL;
437 igf_sl_dl_lor_crresp_pkg.insert_row (
438 x_mode => 'R',
439 x_rowid => l_rowid,
440 x_lor_resp_num => l_lor_resp_num,
441 x_dbth_id => l_dbth_id,
442 x_loan_number => LTRIM(RTRIM(SUBSTR(orec.record_data,9,21))),
443 x_credit_override => LTRIM(RTRIM(SUBSTR(orec.record_data,30,1))),
444 x_credit_decision_date => TO_DATE(substr(orec.record_data,1,8),'YYYYMMDD'),
445 x_status => 'N',
446 x_endorser_amount => NVL(TO_NUMBER(LTRIM(RTRIM(SUBSTR(orec.record_data,31,5)))),0),
447 x_mpn_status => LTRIM(RTRIM(SUBSTR(orec.record_data,36,1))),
448 x_mpn_id => LTRIM(RTRIM(SUBSTR(orec.record_data,37,21))),
449 x_mpn_type => LTRIM(RTRIM(SUBSTR(orec.record_data,58,1))),
450 x_mpn_indicator => LTRIM(RTRIM(SUBSTR(orec.record_data,59,1)))
451 );
452 END LOOP;
453 IF l_actual_rec <> l_number_rec THEN
454 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
455 fnd_file.put_line(fnd_file.log,fnd_message.get);
456 RAISE file_not_loaded;
457 END IF;
458 END IF;
459
460 END IF; -- End of condition for VERSION.
461
462 END;
463
464 p_dbth_id := l_dbth_id;
465 p_dl_version := l_dl_version;
466 p_dl_loan_catg := l_dl_loan_catg;
467
468
469 EXCEPTION
470 WHEN app_exception.record_lock_exception THEN
471 RAISE;
472 WHEN file_not_loaded THEN
473 RAISE;
474 WHEN yr_full_participant THEN
475 NULL;
476 WHEN OTHERS THEN
477 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
478 fnd_message.set_token('NAME','IGF_SL_DL_ORIG_ACK.DL_LOAD_DATA');
479 fnd_file.put_line(fnd_file.log,SQLERRM);
480 igs_ge_msg_stack.add;
481 app_exception.raise_exception;
482
483 END dl_load_data;
484
485
486
487
488 /* MAIN PROCEDURE */
489 PROCEDURE dl_orig_ack(errbuf OUT NOCOPY VARCHAR2,
490 retcode OUT NOCOPY NUMBER,
491 P_org_id IN NUMBER )
492 AS
493 /*************************************************************
494 Created By : venagara
495 Date Created On : 2000/11/22
496 Purpose :
497 Know limitations, enhancements or remarks
498 Change History
499 Who When What
500 npalanis 11/jan/2002 This procedure is modified to pick up disbursement records that
501 are in planned state,insert records into IGF_DB_DISB_HOLDS table
502 with hold 'EXTERNAL' and hold type 'SYSTEM' and also update
503 manual_hold_ind flag in IGF_AW_AWD_DISB table to 'Y'.
504 enh bug no-2154941.
505
506 (reverse chronological order - newest change first)
507 ***************************************************************/
508
509 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
510 l_batch_type igf_sl_dl_batch.batch_type%TYPE;
511 l_rec_present VARCHAR2(10);
512 l_rec_updated VARCHAR2(10);
513 l_stat VARCHAR2(30);
514
515 l_dl_version igf_sl_dl_file_type.dl_version%TYPE;
516 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
517
518 CURSOR cur_batch
519 IS
520 SELECT igf_sl_dl_batch.*
521 FROM igf_sl_dl_batch
522 WHERE dbth_id = l_dbth_id
523 AND status = 'N';
524
525 CURSOR cur_lor_resp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE)
526 IS
527 SELECT igf_sl_dl_lor_resp.*
528 FROM igf_sl_dl_lor_resp
529 WHERE dbth_id = l_dbth_id
530 AND status = 'N';
531
532 CURSOR cur_lor_crresp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE)
533 IS
534 SELECT igf_sl_dl_lor_crresp.*
535 FROM igf_sl_dl_lor_crresp
536 WHERE dbth_id = l_dbth_id
537 AND status = 'N';
538
539 BEGIN
540
541 retcode := 0;
542
543 igf_aw_gen.set_org_id(p_org_id);
544
545 --
546 -- Load the Data into the Batch and Response Tables
547 --
548 dl_load_data(l_dbth_id, l_dl_version, l_dl_loan_catg);
549
550 FOR dbth_rec IN cur_batch LOOP
551
552 IF l_dl_loan_catg IN ('DL_STAFFORD','DL_PLUS') THEN
553 -- These are Direct Loan Origination Response records.
554
555 FOR resp_rec IN cur_lor_resp(l_dbth_id) LOOP
556 l_rec_present := 'N';
557 l_rec_updated := 'N';
558
559 -- Use this to update igf_sl_lor_loc table, with old values.
560 g_lor_loc_rec.orig_status_flag := resp_rec.orig_status_flag;
561 g_lor_loc_rec.orig_ack_date := resp_rec.orig_ack_date;
562 g_lor_loc_rec.pnote_status := resp_rec.pnote_status;
563
564 IF resp_rec.pnote_status = 'Q' THEN
565 g_lor_loc_rec.pnote_status := 'R';
566 ELSIF resp_rec.pnote_status = 'P' THEN
567 g_lor_loc_rec.pnote_status := 'F';
568 END IF;
569
570 g_lor_loc_rec.pnote_status_date := resp_rec.orig_ack_date;
571 g_lor_loc_rec.pnote_mpn_ind := resp_rec.pnote_id;
572 g_sl_dl_lor_resp.elec_mpn_ind := resp_rec.elec_mpn_ind;
573
574 -- Update the IGF_SL_LOR record.
575 -- MN 27-Dec-2004 15:33 - Following matrix will be used to update the loan status based on responses for PLUS loans.
576 -- B = Rejected Origination
577 -- C = Accepted Origination/Credit Check Accepted (PLUS only)
578 -- D = Accepted Origination/Credit Check Denied (PLUS only)
579 -- X = Accepted Origination/Credit Check Pending (PLUS only)
580 -- Based on the orig_status_flag only the crdt_decision_status will be changed.
581 IF l_dl_loan_catg = 'DL_PLUS' THEN
582 IF resp_rec.orig_status_flag = 'B' THEN
583 g_lor_loc_rec.crdt_decision_status := NULL;
584 ELSIF resp_rec.orig_status_flag = 'C' THEN
585 g_lor_loc_rec.crdt_decision_status := 'A';
586 ELSIF resp_rec.orig_status_flag = 'D' THEN
587 g_lor_loc_rec.crdt_decision_status := 'D';
588 ELSIF resp_rec.orig_status_flag = 'X' THEN
589 g_lor_loc_rec.crdt_decision_status := 'P';
590 END IF;
591 END IF;
592
593 upd_lor_record(resp_rec.loan_number, 'ORIG_ACK', l_rec_present, l_rec_updated);
594
595 IF l_rec_present = 'N' THEN
596 fnd_message.set_name('IGF','IGF_SL_NO_LOAN_NUMBER');
597 fnd_message.set_token('LOAN_NUMBER',resp_rec.loan_number);
598 fnd_file.put_line(fnd_file.log, fnd_message.get);
599 -- Message : Loan ID #LOAN_NUMBER does not exist in the Financial Aid System
600 ELSE
601 -- If the Loan Number is a Valid Loan-number
602
603 IF l_rec_updated = 'N' THEN
604 fnd_message.set_name('IGF','IGF_SL_OLD_ORIG_ACK_DATE');
605 fnd_message.set_token('LOAN_NUMBER',resp_rec.loan_number);
606 fnd_file.put_line(fnd_file.log, fnd_message.get);
607 -- Message : Acknowledgment for Loan ID #LOAN_NUMBER not loaded, due to old acknowledgement date
608 ELSE
609 -- Show any Origination Reject Codes in the Log File, if any.
610 DECLARE
611 CURSOR c_rej IS
612 SELECT lookup_code, meaning FROM igf_lookups_view
613 WHERE lookup_type = 'IGF_SL_DL_ORIG_REJ_CODES'
614 AND lookup_code IN (SUBSTR(resp_rec.orig_reject_reasons, 1,2),
615 SUBSTR(resp_rec.orig_reject_reasons, 3,2),
616 SUBSTR(resp_rec.orig_reject_reasons, 5,2),
617 SUBSTR(resp_rec.orig_reject_reasons, 7,2),
618 SUBSTR(resp_rec.orig_reject_reasons, 9,2));
619 BEGIN
620 igf_sl_edit.delete_edit(resp_rec.loan_number, 'R');
621 FOR rrec IN c_rej LOOP
622 fnd_file.put_line(fnd_file.log, ' '||rrec.meaning);
623 igf_sl_edit.insert_edit(resp_rec.loan_number, 'R', 'IGF_SL_DL_ORIG_REJ_CODES',
624 rrec.lookup_code, '', '');
625 END LOOP;
626 END;
627 END IF; -- End of condition for "l_rec_updated = 'N' "
628 END IF; -- End of condition for "l_rec_present = 'N' "
629
630
631 IF l_rec_present = 'N' THEN
632 l_stat := 'I'; -- Invalid Loan Number. So, Not Loaded.
633 ELSE
634 IF l_rec_updated = 'N' THEN
635 l_stat := 'O'; -- Old Acknowledgment Data. Not Loaded.
636 ELSIF l_rec_updated = 'Y' THEN
637 l_stat := 'Y'; -- Loan Acknowledgment Data uploaded.
638 END IF;
639 END IF;
640
641 -- Update the Response File's transaction record with the Processing Status
642 igf_sl_dl_lor_resp_pkg.update_row (
643 x_mode => 'R',
644 x_rowid => resp_rec.row_id,
645 x_lor_resp_num => resp_rec.lor_resp_num,
646 x_dbth_id => resp_rec.dbth_id,
647 x_orig_batch_id => resp_rec.orig_batch_id,
648 x_loan_number => resp_rec.loan_number,
649 x_orig_ack_date => resp_rec.orig_ack_date,
650 x_orig_status_flag => resp_rec.orig_status_flag,
651 x_orig_reject_reasons => resp_rec.orig_reject_reasons,
652 x_pnote_status => resp_rec.pnote_status,
653 x_pnote_id => resp_rec.pnote_id,
654 x_pnote_accept_amt => resp_rec.pnote_accept_amt,
655 x_loan_amount_accepted => resp_rec.loan_amount_accepted,
656 x_status => l_stat,
657 x_elec_mpn_ind => resp_rec.elec_mpn_ind
658 );
659
660
661 END LOOP; -- End of igf_sl_dl_lor_resp table LOOP.
662
663
664 ELSIF l_dl_loan_catg = 'DL_PLUS_CREDIT' THEN
665
666 --
667 -- These are Direct Loan Credit Response records.
668 --
669
670 FOR crresp_rec IN cur_lor_crresp(l_dbth_id) LOOP
671
672 l_rec_present := 'N';
673 l_rec_updated := 'N';
674
675 -- Use this to update igf_sl_lor_loc table, with old values.
676 g_lor_loc_rec.credit_override := crresp_rec.credit_override;
677 g_lor_loc_rec.credit_decision_date := crresp_rec.credit_decision_date;
678 g_lor_loc_rec.pnote_status := crresp_rec.mpn_status;
679 g_lor_loc_rec.pnote_status_date := crresp_rec.credit_decision_date;
680
681 IF crresp_rec.mpn_status = 'R' THEN
682 g_lor_loc_rec.pnote_status := 'F';
683 END IF;
684
685 g_lor_loc_rec.pnote_id := crresp_rec.mpn_id;
686 g_lor_loc_rec.pnote_mpn_ind := crresp_rec.mpn_indicator;
687 g_sl_dl_lor_resp.elec_mpn_ind := crresp_rec.mpn_type;
688 g_lor_loc_rec.pnote_accept_amt := crresp_rec.endorser_amount;
689
690
691 -- Update the IGF_SL_LOR record.
692 upd_lor_record(crresp_rec.loan_number, 'CREDIT_ACK', l_rec_present, l_rec_updated);
693
694 IF l_rec_present = 'N' THEN
695 fnd_message.set_name('IGF','IGF_SL_NO_LOAN_NUMBER');
696 fnd_message.set_token('LOAN_NUMBER',crresp_rec.loan_number);
697 fnd_file.put_line(fnd_file.log, fnd_message.get);
698 -- Message : Loan ID #LOAN_NUMBER does not exist in the Financial Aid System
699 ELSE
700 -- If the Loan Number is a Valid Loan-number
701 IF l_rec_updated = 'N' THEN
702 fnd_message.set_name('IGF','IGF_SL_OLD_CREDT_ACK_DATE');
703 fnd_message.set_token('LOAN_NUMBER',crresp_rec.loan_number);
704 fnd_file.put_line(fnd_file.log, fnd_message.get);
705 -- Message : Acknowledgment for Loan ID #LOAN_NUMBER not loaded, due to old credit acknowledgement date
706 END IF; -- End of condition for "l_rec_updated = 'N' "
707 END IF; -- End of condition for "l_rec_present = 'N' "
708
709
710 IF l_rec_present = 'N' THEN
711 l_stat := 'I'; -- Invalid Loan Number. So, Not Loaded.
712 ELSE
713 IF l_rec_updated = 'N' THEN
714 l_stat := 'O'; -- Old Acknowledgment Data. Not Loaded.
715 ELSIF l_rec_updated = 'Y' THEN
716 l_stat := 'Y'; -- Loan Acknowledgment Data uploaded.
717 END IF;
718 END IF;
719
720 -- Update the Credit Response File's transaction record with the Processing Status
721 igf_sl_dl_lor_crresp_pkg.update_row (
722 x_mode => 'R',
723 x_rowid => crresp_rec.row_id,
724 x_lor_resp_num => crresp_rec.lor_resp_num,
725 x_dbth_id => crresp_rec.dbth_id,
726 x_loan_number => crresp_rec.loan_number,
727 x_credit_override => crresp_rec.credit_override,
728 x_credit_decision_date => crresp_rec.credit_decision_date,
729 x_status => l_stat
730 );
731
732
733 END LOOP; -- End of igf_sl_dl_lor_crresp table LOOP.
734
735 END IF; -- End of Condition for l_dl_loan_catg
736
737 -- Update the DL_BATCH record as Successfully Uploaded.
738 igf_sl_dl_batch_pkg.update_row (
739 x_mode => 'R',
740 x_rowid => dbth_rec.row_id,
741 x_dbth_id => dbth_rec.dbth_id,
742 x_batch_id => dbth_rec.batch_id,
743 x_message_class => dbth_rec.message_class,
744 x_bth_creation_date => dbth_rec.bth_creation_date,
745 x_batch_rej_code => dbth_rec.batch_rej_code,
746 x_end_date => dbth_rec.end_date,
747 x_batch_type => dbth_rec.batch_type,
748 x_send_resp => dbth_rec.send_resp,
749 x_status => 'Y'
750 );
751
752
753 END LOOP; -- End of Batch ID "FOR LOOP"
754
755 COMMIT;
756
757 EXCEPTION
758 WHEN app_exception.record_lock_exception THEN
759 ROLLBACK;
760 retcode := 2;
761 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
762 igs_ge_msg_stack.conc_exception_hndl;
763 WHEN file_not_loaded THEN
764 ROLLBACK;
765 retcode := 2;
766 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
767 igs_ge_msg_stack.conc_exception_hndl;
768 WHEN OTHERS THEN
769 ROLLBACK;
770 retcode := 2;
771 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
772 fnd_file.put_line(fnd_file.log,SQLERRM);
773 igs_ge_msg_stack.conc_exception_hndl;
774 END dl_orig_ack;
775
776
777
778 PROCEDURE upd_lor_record(p_loan_number IN igf_sl_loans.loan_number%TYPE,
779 p_process IN VARCHAR2,
780 p_rec_present OUT NOCOPY VARCHAR2,
781 p_rec_updated OUT NOCOPY VARCHAR2 )
782 AS
783 /*************************************************************
784 Created By : venagara
785 Date Created On : 2000/11/22
786 Purpose :
787 Know limitations, enhancements or remarks
788 Change History
789 Who When What
790 --------------------------------------------------------------------------------------------
791 -- museshad 20-Feb-2006 Bug 5045766 - SQL Repository Issue.
792 -- Modified cursor c_lar_cur for better performance.
793 ---------------------------------------------------------------------------------------------
794 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
795 -- a) Impact of adding the relationship_cd
796 -- in igf_sl_lor_all table and obsoleting
797 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
798 -- GUARANTOR_ID, DUNS_GUARNT_ID,
799 -- LENDER_ID, DUNS_LENDER_ID
800 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
801 -- RECIPIENT_TYPE,DUNS_RECIP_ID
802 -- RECIP_NON_ED_BRC_ID columns.
803 ---------------------------------------------------------------------------------------------
804 veramach 23-SEP-2003 Bug 3104228:
805 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
806 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
807 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
808 chg_batch_id,appl_send_error_codes from igf_sl_lor
809 2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
810 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
811 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
812 chg_batch_id from igf_sl_lor_loc
813
814 agairola 15-MAR-2002 Modified the Update Row call of the IGF_SL_LOANS_PKG
815 added as part of the Refunds DLD - 2144600
816 rboddu 18/05/2001 uploaded pnote_id field of IGF_SL_DL_LOR_RESP table into
817 pnote_mpn_ind field of IGF_SL_LOR table.
818 If School is configured to print then the pnote_status
819 field of IGF_SL_LOR is set to 'G' (Ready to Print)
820
821 (reverse chronological order - newest change first)
822 ***************************************************************/
823
824 l_row_id VARCHAR2(25);
825 l_print_opt igf_sl_dl_setup.pnote_print_ind%TYPE;
826
827 CURSOR c_tbh_cur IS
828 SELECT igf_sl_lor.* FROM igf_sl_lor
829 WHERE loan_id = (SELECT loan_id FROM igf_sl_loans lar
830 WHERE loan_number = p_loan_number)
831 FOR UPDATE OF igf_sl_lor.sch_cert_date NOWAIT;
832
833 CURSOR c_lar_cur(c_loan_id igf_sl_loans.loan_id%TYPE)
834 IS
835 SELECT loan.rowid row_id,
836 loan.*,
837 fcat.fed_fund_code
838 FROM igf_sl_loans_all loan,
839 igf_aw_award_all awd,
840 igf_aw_fund_mast_all fmast,
841 igf_aw_fund_cat_all fcat
842 WHERE
843 loan.award_id = awd.award_id AND
844 awd.fund_id = fmast.fund_id AND
845 fcat.fund_code = fmast.fund_code AND
846 loan.loan_id = c_loan_id;
847
848 lar_rec c_lar_cur%ROWTYPE;
849
850 CURSOR c_print_cur
851 IS
852 SELECT
853 pnote_print_ind
854 FROM
855 igf_sl_dl_setup
856 WHERE (ci_cal_type,ci_sequence_number) = ( SELECT fmast.ci_cal_type,
857 fmast.ci_sequence_number
858 FROM igf_sl_loans loans,
859 igf_aw_award awd,
860 igf_aw_fund_mast fmast
861 WHERE loans.loan_number = p_loan_number
862 AND loans.award_id = awd.award_id
863 AND awd.fund_id = fmast.fund_id
864 );
865
866 BEGIN
867 p_rec_present := 'N';
868 p_rec_updated := 'N';
869
870 FOR tbh_rec in c_tbh_cur LOOP
871
872 -- If the Loan-Number is a Valid Loan-Number
873 p_rec_present := 'Y';
874
875
876 IF p_process = 'ORIG_ACK'
877 AND ( tbh_rec.orig_ack_date IS NULL
878 OR tbh_rec.orig_ack_date < g_lor_loc_rec.orig_ack_date ) THEN
879
880 -- If the Loan origination acknowledgment record has to be loaded.
881 p_rec_updated := 'Y';
882
883 tbh_rec.orig_status_flag := g_lor_loc_rec.orig_status_flag;
884 tbh_rec.orig_ack_date := g_lor_loc_rec.orig_ack_date;
885 tbh_rec.pnote_status := g_lor_loc_rec.pnote_status;
886 tbh_rec.pnote_status_date := g_lor_loc_rec.pnote_status_date;
887 tbh_rec.pnote_mpn_ind := g_lor_loc_rec.pnote_mpn_ind;
888 tbh_rec.elec_mpn_ind := g_sl_dl_lor_resp.elec_mpn_ind; --added for 2216956
889 tbh_rec.crdt_decision_status:= g_lor_loc_rec.crdt_decision_status; --added for FA149
890
891 ELSIF p_process = 'CREDIT_ACK'
892 AND ( tbh_rec.credit_decision_date IS NULL
893 OR tbh_rec.credit_decision_date < g_lor_loc_rec.credit_decision_date ) THEN
894
895 -- If the Loan credit acknowledgment record has to be loaded.
896 p_rec_updated := 'Y';
897
898 tbh_rec.credit_override := g_lor_loc_rec.credit_override;
899 tbh_rec.credit_decision_date := g_lor_loc_rec.credit_decision_date;
900 tbh_rec.pnote_status := g_lor_loc_rec.pnote_status;
901 tbh_rec.pnote_status_date := g_lor_loc_rec.pnote_status_date;
902 tbh_rec.pnote_mpn_ind := g_lor_loc_rec.pnote_mpn_ind;
903 tbh_rec.elec_mpn_ind := g_sl_dl_lor_resp.elec_mpn_ind; --added for 2216956
904 tbh_rec.pnote_id := g_lor_loc_rec.pnote_id;
905 tbh_rec.pnote_accept_amt := g_lor_loc_rec.pnote_accept_amt;
906
907 END IF;
908
909
910 -- If the Loan has to updated with the information from the File
911 IF p_rec_updated = 'Y' THEN
912
913 -- Delete all previous reject records, for Response process.
914 igf_sl_edit.delete_edit(p_loan_number, 'R');
915 igf_sl_lor_pkg.update_row (
916 x_mode => 'R',
917 x_rowid => tbh_rec.row_id,
918 x_origination_id => tbh_rec.origination_id,
919 x_loan_id => tbh_rec.loan_id,
920 x_sch_cert_date => tbh_rec.sch_cert_date,
921 x_orig_status_flag => tbh_rec.orig_status_flag,
922 x_orig_batch_id => tbh_rec.orig_batch_id,
923 x_orig_batch_date => tbh_rec.orig_batch_date,
924 x_chg_batch_id => NULL,
925 x_orig_ack_date => tbh_rec.orig_ack_date,
926 x_credit_override => tbh_rec.credit_override,
927 x_credit_decision_date => tbh_rec.credit_decision_date,
928 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
929 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
930 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
931 x_pnote_status => tbh_rec.pnote_status,
932 x_pnote_status_date => tbh_rec.pnote_status_date,
933 x_pnote_id => tbh_rec.pnote_id,
934 x_pnote_batch_id => tbh_rec.pnote_batch_id,
935 x_pnote_ack_date => tbh_rec.pnote_ack_date,
936 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
937 x_pnote_print_ind => tbh_rec.pnote_print_ind,
938 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
939 x_pnote_accept_date => tbh_rec.pnote_accept_date,
940 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
941 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
942 x_orig_fee_perct => tbh_rec.orig_fee_perct,
943 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
944 x_borw_interest_ind => tbh_rec.borw_interest_ind,
945 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
946 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
947 x_guarantee_amt => tbh_rec.guarantee_amt,
948 x_guarantee_date => tbh_rec.guarantee_date,
949 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
950 x_guarnt_status_code => tbh_rec.guarnt_status_code,
951 x_guarnt_status_date => tbh_rec.guarnt_status_date,
952 x_lend_apprv_denied_code => NULL,
953 x_lend_apprv_denied_date => NULL,
954 x_lend_status_code => tbh_rec.lend_status_code,
955 x_lend_status_date => tbh_rec.lend_status_date,
956 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
957 x_grade_level_code => tbh_rec.grade_level_code,
958 x_enrollment_code => tbh_rec.enrollment_code,
959 x_anticip_compl_date => tbh_rec.anticip_compl_date,
960 x_borw_lender_id => NULL,
961 x_duns_borw_lender_id => NULL,
962 x_guarantor_id => NULL,
963 x_duns_guarnt_id => NULL,
964 x_prc_type_code => tbh_rec.prc_type_code,
965 x_cl_seq_number => tbh_rec.cl_seq_number,
966 x_last_resort_lender => tbh_rec.last_resort_lender,
967 x_lender_id => NULL,
968 x_duns_lender_id => NULL,
969 x_lend_non_ed_brc_id => NULL,
970 x_recipient_id => NULL,
971 x_recipient_type => NULL,
972 x_duns_recip_id => NULL,
973 x_recip_non_ed_brc_id => NULL,
974 x_rec_type_ind => tbh_rec.rec_type_ind,
975 x_cl_loan_type => tbh_rec.cl_loan_type,
976 x_cl_rec_status => NULL,
977 x_cl_rec_status_last_update => NULL,
978 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
979 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
980 x_mpn_confirm_code => NULL,
981 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
982 x_appl_loan_phase_code => NULL,
983 x_appl_loan_phase_code_chg => NULL,
984 x_appl_send_error_codes => NULL,
985 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
986 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
987 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
988 x_act_interest_rate => tbh_rec.act_interest_rate,
989 x_service_type_code => tbh_rec.service_type_code,
990 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
991 x_sch_refund_amt => tbh_rec.sch_refund_amt,
992 x_sch_refund_date => tbh_rec.sch_refund_date,
993 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
994 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
995 x_p_person_id => tbh_rec.p_person_id,
996 x_p_ssn_chg_date => NULL,
997 x_p_dob_chg_date => NULL,
998 x_p_permt_addr_chg_date => tbh_rec.p_permt_addr_chg_date,
999 x_p_default_status => tbh_rec.p_default_status,
1000 x_p_signature_code => tbh_rec.p_signature_code,
1001 x_p_signature_date => tbh_rec.p_signature_date,
1002 x_s_ssn_chg_date => NULL,
1003 x_s_dob_chg_date => NULL,
1004 x_s_permt_addr_chg_date => tbh_rec.s_permt_addr_chg_date,
1005 x_s_local_addr_chg_date => NULL,
1006 x_s_default_status => tbh_rec.s_default_status,
1007 x_s_signature_code => tbh_rec.s_signature_code,
1008 x_elec_mpn_ind => tbh_rec.elec_mpn_ind,
1009 x_borr_sign_ind => tbh_rec.borr_sign_ind,
1010 x_stud_sign_ind => tbh_rec.stud_sign_ind,
1011 x_borr_credit_auth_code => tbh_rec.borr_credit_auth_code,
1012 x_relationship_cd => tbh_rec.relationship_cd,
1013 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
1014 x_cps_trans_num => tbh_rec.cps_trans_num,
1015 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt ,
1016 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt,
1017 x_crdt_decision_status => tbh_rec.crdt_decision_status,
1018 x_note_message => tbh_rec.note_message,
1019 x_book_loan_amt => tbh_rec.book_loan_amt ,
1020 x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
1021 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
1022 x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
1023 x_requested_loan_amt => tbh_rec.requested_loan_amt,
1024 x_eft_authorization_code => tbh_rec.eft_authorization_code,
1025 x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
1026 x_deferment_request_code => tbh_rec.deferment_request_code ,
1027 x_actual_record_type_code => tbh_rec.actual_record_type_code,
1028 x_reinstatement_amt => tbh_rec.reinstatement_amt,
1029 x_school_use_txt => tbh_rec.school_use_txt,
1030 x_lender_use_txt => tbh_rec.lender_use_txt,
1031 x_guarantor_use_txt => tbh_rec.guarantor_use_txt,
1032 x_fls_approved_amt => tbh_rec.fls_approved_amt,
1033 x_flu_approved_amt => tbh_rec.flu_approved_amt,
1034 x_flp_approved_amt => tbh_rec.flp_approved_amt,
1035 x_alt_approved_amt => tbh_rec.alt_approved_amt,
1036 x_loan_app_form_code => tbh_rec.loan_app_form_code,
1037 x_override_grade_level_code => tbh_rec.override_grade_level_code,
1038 x_b_alien_reg_num_txt => tbh_rec.b_alien_reg_num_txt,
1039 x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd,
1040 x_acad_begin_date => tbh_rec.acad_begin_date,
1041 x_acad_end_date => tbh_rec.acad_end_date
1042 );
1043
1044
1045 -- Update the Loan Status Based on the LOR's Origination Status
1046 OPEN c_lar_cur(tbh_rec.loan_id);
1047 FETCH c_lar_cur INTO lar_rec;
1048 IF c_lar_cur%NOTFOUND THEN
1049 CLOSE c_lar_cur;
1050 RAISE NO_DATA_FOUND;
1051 END IF;
1052 DECLARE
1053 l_update_loan VARCHAR2(30) DEFAULT 'N';
1054 l_loan_status_dt igf_sl_loans.loan_status_date%TYPE;
1055 l_loan_status igf_sl_loans.loan_status%TYPE;
1056 BEGIN
1057
1058 -- Set the Loan Status and Loan Status Date to existing values
1059 g_lor_loc_rec.loan_status := lar_rec.loan_status;
1060 g_lor_loc_rec.loan_status_date := lar_rec.loan_status_date;
1061
1062 IF igf_sl_gen.chk_dl_stafford(lar_rec.fed_fund_code) = 'TRUE' THEN
1063 IF tbh_rec.orig_status_flag = 'A' THEN -- Orgination Accepted
1064 l_loan_status := 'A'; -- Loan is Accepted
1065 l_loan_status_dt := TRUNC(SYSDATE);
1066 l_update_loan := 'Y';
1067 ELSIF tbh_rec.orig_status_flag = 'B' THEN -- Origination Rejected
1068 l_loan_status := 'R'; -- Loan is Rejected
1069 l_loan_status_dt := TRUNC(SYSDATE);
1070 l_update_loan := 'Y';
1071 END IF;
1072 ELSIF igf_sl_gen.chk_dl_plus(lar_rec.fed_fund_code) = 'TRUE' THEN
1073 IF tbh_rec.orig_status_flag IN ('X','C') THEN -- Origination Accepted, Credit Accepted
1074 l_loan_status := 'A'; -- Loan is Accepted
1075 ELSIF tbh_rec.orig_status_flag IN ('B','D') THEN -- Orig Rejected OR Orig Accept,Credit Denied
1076 l_loan_status := 'R'; -- Loan is Rejected
1077 END IF;
1078 l_loan_status_dt := TRUNC(SYSDATE);
1079 l_update_loan := 'Y';
1080 END IF;
1081
1082 IF l_update_loan = 'Y' THEN
1083 fnd_message.set_name('IGF','IGF_SL_LOAN_STATUS');
1084 fnd_message.set_token('LOAN_NUMBER',p_loan_number);
1085 fnd_message.set_token('LOAN_STATUS',igf_aw_gen.lookup_desc('IGF_SL_REC_STATUS',l_loan_status));
1086 fnd_file.put_line(fnd_file.log, fnd_message.get);
1087 -- Message : Loan ID #LOAN_NUMBER has been #LOAN_STATUS
1088
1089 -- Set the Loan Status and Loan Status Date to New Values
1090 g_lor_loc_rec.loan_status := l_loan_status;
1091 g_lor_loc_rec.loan_status_date := l_loan_status_dt;
1092
1093 -- Modified the Update Row call to include the Borrower Determination
1094 -- added as part of Refunds DLD - 2144600
1095 igf_sl_loans_pkg.update_row (
1096 x_mode => 'R',
1097 x_rowid => lar_rec.row_id,
1098 x_loan_id => lar_rec.loan_id,
1099 x_award_id => lar_rec.award_id,
1100 x_seq_num => lar_rec.seq_num,
1101 x_loan_number => lar_rec.loan_number,
1102 x_loan_per_begin_date => lar_rec.loan_per_begin_date,
1103 x_loan_per_end_date => lar_rec.loan_per_end_date,
1104 x_loan_status => l_loan_status,
1105 x_loan_status_date => l_loan_status_dt,
1106 x_loan_chg_status => lar_rec.loan_chg_status,
1107 x_loan_chg_status_date => lar_rec.loan_chg_status_date,
1108 x_active => lar_rec.active,
1109 x_active_date => lar_rec.active_date,
1110 x_borw_detrm_code => lar_rec.borw_detrm_code,
1111 x_external_loan_id_txt => lar_rec.external_loan_id_txt
1112
1113 );
1114 END IF;
1115 END;
1116
1117 CLOSE c_lar_cur;
1118
1119 -- Update the LOR LOC record, to reflect the incoming information.
1120 upd_lor_loc_record(tbh_rec.loan_id, p_process);
1121
1122 END IF;
1123 END LOOP;
1124
1125 EXCEPTION
1126
1127 WHEN app_exception.record_lock_exception THEN
1128 RAISE;
1129 WHEN others THEN
1130 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1131 fnd_message.set_token('NAME','IGF_SL_DL_ORIG_ACK.UPD_LOR_RECORD');
1132 fnd_file.put_line(fnd_file.log,SQLERRM);
1133 igs_ge_msg_stack.add;
1134 app_exception.raise_exception;
1135 END upd_lor_record;
1136
1137
1138
1139 PROCEDURE upd_lor_loc_record(p_loan_id igf_sl_lor.loan_id%TYPE, p_process VARCHAR2)
1140 AS
1141 /*************************************************************
1142 Created By : venagara
1143 Date Created On : 2000/11/22
1144 Purpose :
1145 Know limitations, enhancements or remarks
1146 Change History
1147 Who When What
1148 -----------------------------------------------------------------------------------------------------------
1149 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
1150 -- The DUNS_BORW_LENDER_ID,
1151 -- DUNS_GUARNT_ID,
1152 -- DUNS_LENDER_ID,
1153 -- DUNS_RECIP_ID columns are osboleted from the
1154 -- igf_sl_lor_loc_all table.
1155 -----------------------------------------------------------------------------------------------------------
1156 veramach 23-SEP-2003 Bug 3104228:
1157 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
1158 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
1159 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
1160 chg_batch_id,appl_send_error_codes from igf_sl_lor
1161 2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
1162 cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
1163 p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
1164 chg_batch_id from igf_sl_lor_loc
1165 rboddu 18/05/2001 uploaded pnote_id field of IGF_SL_DL_LOR_RESP table into
1166 pnote_mpn_ind field of IGF_SL_LOR_LOC table.
1167
1168 (reverse chronological order - newest change first)
1169 ***************************************************************/
1170
1171 CURSOR c_tbh_cur
1172 IS
1173 SELECT
1174 igf_sl_lor_loc.*
1175 FROM
1176 igf_sl_lor_loc
1177 WHERE loan_id = p_loan_id
1178 FOR UPDATE OF igf_sl_lor_loc.loan_status NOWAIT;
1179
1180 BEGIN
1181
1182 FOR tbh_rec in c_tbh_cur LOOP
1183
1184 IF p_process = 'ORIG_ACK' THEN
1185 tbh_rec.orig_status_flag := g_lor_loc_rec.orig_status_flag;
1186 tbh_rec.orig_ack_date := g_lor_loc_rec.orig_ack_date;
1187 tbh_rec.pnote_status := g_lor_loc_rec.pnote_status;
1188 tbh_rec.pnote_status_date := g_lor_loc_rec.pnote_status_date;
1189 tbh_rec.pnote_mpn_ind := g_lor_loc_rec.pnote_mpn_ind;
1190
1191 ELSIF p_process = 'CREDIT_ACK' THEN
1192 tbh_rec.credit_override := g_lor_loc_rec.credit_override;
1193 tbh_rec.credit_decision_date := g_lor_loc_rec.credit_decision_date;
1194 tbh_rec.pnote_status := g_lor_loc_rec.pnote_status;
1195 tbh_rec.pnote_status_date := g_lor_loc_rec.pnote_status_date;
1196 tbh_rec.pnote_mpn_ind := g_lor_loc_rec.pnote_mpn_ind;
1197 END IF;
1198
1199 tbh_rec.loan_status := g_lor_loc_rec.loan_status;
1200 tbh_rec.loan_status_date := g_lor_loc_rec.loan_status_date;
1201
1202 igf_sl_lor_loc_pkg.update_row (
1203 x_mode => 'R',
1204 x_rowid => tbh_rec.row_id,
1205 x_loan_id => tbh_rec.loan_id,
1206 x_origination_id => tbh_rec.origination_id,
1207 x_loan_number => tbh_rec.loan_number,
1208 x_loan_type => tbh_rec.loan_type,
1209 x_loan_amt_offered => tbh_rec.loan_amt_offered,
1210 x_loan_amt_accepted => tbh_rec.loan_amt_accepted,
1211 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
1212 x_loan_per_end_date => tbh_rec.loan_per_end_date,
1213 x_acad_yr_begin_date => tbh_rec.acad_yr_begin_date,
1214 x_acad_yr_end_date => tbh_rec.acad_yr_end_date,
1215 x_loan_status => tbh_rec.loan_status,
1216 x_loan_status_date => tbh_rec.loan_status_date,
1217 x_loan_chg_status => tbh_rec.loan_chg_status,
1218 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
1219 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
1220 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
1221 x_active => tbh_rec.active,
1222 x_active_date => tbh_rec.active_date,
1223 x_sch_cert_date => tbh_rec.sch_cert_date,
1224 x_orig_status_flag => tbh_rec.orig_status_flag,
1225 x_orig_batch_id => tbh_rec.orig_batch_id,
1226 x_orig_batch_date => tbh_rec.orig_batch_date,
1227 x_chg_batch_id => NULL,
1228 x_orig_ack_date => tbh_rec.orig_ack_date,
1229 x_credit_override => tbh_rec.credit_override,
1230 x_credit_decision_date => tbh_rec.credit_decision_date,
1231 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
1232 x_pnote_status => tbh_rec.pnote_status,
1233 x_pnote_status_date => tbh_rec.pnote_status_date,
1234 x_pnote_id => tbh_rec.pnote_id,
1235 x_pnote_print_ind => tbh_rec.pnote_print_ind,
1236 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
1237 x_pnote_accept_date => tbh_rec.pnote_accept_date,
1238 x_p_signature_code => tbh_rec.p_signature_code,
1239 x_p_signature_date => tbh_rec.p_signature_date,
1240 x_s_signature_code => tbh_rec.s_signature_code,
1241 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
1242 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
1243 x_orig_fee_perct => tbh_rec.orig_fee_perct,
1244 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
1245 x_borw_interest_ind => tbh_rec.borw_interest_ind,
1246 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
1247 x_guarantee_amt => tbh_rec.guarantee_amt,
1248 x_guarantee_date => tbh_rec.guarantee_date,
1249 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
1250 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
1251 x_guarnt_status_code => tbh_rec.guarnt_status_code,
1252 x_guarnt_status_date => tbh_rec.guarnt_status_date,
1253 x_lend_apprv_denied_code => NULL,
1254 x_lend_apprv_denied_date => NULL,
1255 x_lend_status_code => tbh_rec.lend_status_code,
1256 x_lend_status_date => tbh_rec.lend_status_date,
1257 x_grade_level_code => tbh_rec.grade_level_code,
1258 x_enrollment_code => tbh_rec.enrollment_code,
1259 x_anticip_compl_date => tbh_rec.anticip_compl_date,
1260 x_borw_lender_id => tbh_rec.borw_lender_id,
1261 x_duns_borw_lender_id => NULL,
1262 x_guarantor_id => tbh_rec.guarantor_id,
1263 x_duns_guarnt_id => NULL,
1264 x_prc_type_code => tbh_rec.prc_type_code,
1265 x_rec_type_ind => tbh_rec.rec_type_ind,
1266 x_cl_loan_type => tbh_rec.cl_loan_type,
1267 x_cl_seq_number => tbh_rec.cl_seq_number,
1268 x_last_resort_lender => tbh_rec.last_resort_lender,
1269 x_lender_id => tbh_rec.lender_id,
1270 x_duns_lender_id => NULL,
1271 x_lend_non_ed_brc_id => tbh_rec.lend_non_ed_brc_id,
1272 x_recipient_id => tbh_rec.recipient_id,
1273 x_recipient_type => tbh_rec.recipient_type,
1274 x_duns_recip_id => NULL,
1275 x_recip_non_ed_brc_id => tbh_rec.recip_non_ed_brc_id,
1276 x_cl_rec_status => NULL,
1277 x_cl_rec_status_last_update => NULL,
1278 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
1279 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
1280 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
1281 x_mpn_confirm_code => NULL,
1282 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
1283 x_appl_loan_phase_code => NULL,
1284 x_appl_loan_phase_code_chg => NULL,
1285 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
1286 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
1287 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
1288 x_act_interest_rate => tbh_rec.act_interest_rate,
1289 x_service_type_code => tbh_rec.service_type_code,
1290 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
1291 x_sch_refund_amt => tbh_rec.sch_refund_amt,
1292 x_sch_refund_date => tbh_rec.sch_refund_date,
1293 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
1294 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
1295 x_p_person_id => tbh_rec.p_person_id,
1296 x_p_ssn => tbh_rec.p_ssn,
1297 x_p_ssn_chg_date => NULL,
1298 x_p_last_name => tbh_rec.p_last_name,
1299 x_p_first_name => tbh_rec.p_first_name,
1300 x_p_middle_name => tbh_rec.p_middle_name,
1301 x_p_permt_addr1 => tbh_rec.p_permt_addr1,
1302 x_p_permt_addr2 => tbh_rec.p_permt_addr2,
1303 x_p_permt_city => tbh_rec.p_permt_city,
1304 x_p_permt_state => tbh_rec.p_permt_state,
1305 x_p_permt_zip => tbh_rec.p_permt_zip,
1306 x_p_permt_addr_chg_date => tbh_rec.p_permt_addr_chg_date,
1307 x_p_permt_phone => tbh_rec.p_permt_phone,
1308 x_p_email_addr => tbh_rec.p_email_addr,
1309 x_p_date_of_birth => tbh_rec.p_date_of_birth,
1310 x_p_dob_chg_date => NULL,
1311 x_p_license_num => tbh_rec.p_license_num,
1312 x_p_license_state => tbh_rec.p_license_state,
1313 x_p_citizenship_status => tbh_rec.p_citizenship_status,
1314 x_p_alien_reg_num => tbh_rec.p_alien_reg_num,
1315 x_p_default_status => tbh_rec.p_default_status,
1316 x_p_foreign_postal_code => tbh_rec.p_foreign_postal_code,
1317 x_p_state_of_legal_res => tbh_rec.p_state_of_legal_res,
1318 x_p_legal_res_date => tbh_rec.p_legal_res_date,
1319 x_s_ssn => tbh_rec.s_ssn,
1320 x_s_ssn_chg_date => NULL,
1321 x_s_last_name => tbh_rec.s_last_name,
1322 x_s_first_name => tbh_rec.s_first_name,
1323 x_s_middle_name => tbh_rec.s_middle_name,
1324 x_s_permt_addr1 => tbh_rec.s_permt_addr1,
1325 x_s_permt_addr2 => tbh_rec.s_permt_addr2,
1326 x_s_permt_city => tbh_rec.s_permt_city,
1327 x_s_permt_state => tbh_rec.s_permt_state,
1328 x_s_permt_zip => tbh_rec.s_permt_zip,
1329 x_s_permt_addr_chg_date => tbh_rec.s_permt_addr_chg_date,
1330 x_s_permt_phone => tbh_rec.s_permt_phone,
1331 x_s_local_addr1 => tbh_rec.s_local_addr1,
1332 x_s_local_addr2 => tbh_rec.s_local_addr2,
1333 x_s_local_city => tbh_rec.s_local_city,
1334 x_s_local_state => tbh_rec.s_local_state,
1335 x_s_local_zip => tbh_rec.s_local_zip,
1336 x_s_local_addr_chg_date => NULL,
1337 x_s_email_addr => tbh_rec.s_email_addr,
1338 x_s_date_of_birth => tbh_rec.s_date_of_birth,
1339 x_s_dob_chg_date => NULL,
1340 x_s_license_num => tbh_rec.s_license_num,
1341 x_s_license_state => tbh_rec.s_license_state,
1342 x_s_depncy_status => tbh_rec.s_depncy_status,
1343 x_s_default_status => tbh_rec.s_default_status,
1344 x_s_citizenship_status => tbh_rec.s_citizenship_status,
1345 x_s_alien_reg_num => tbh_rec.s_alien_reg_num,
1346 x_s_foreign_postal_code => tbh_rec.s_foreign_postal_code,
1347 x_pnote_batch_id => tbh_rec.pnote_batch_id,
1348 x_pnote_ack_date => tbh_rec.pnote_ack_date,
1349 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
1350 x_award_id => tbh_rec.award_id,
1351 x_base_id => tbh_rec.base_id,
1352 x_document_id_txt => tbh_rec.document_id_txt,
1353 x_loan_key_num => tbh_rec.loan_key_num,
1354 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
1355 x_fin_award_year => tbh_rec.fin_award_year,
1356 x_cps_trans_num => tbh_rec.cps_trans_num,
1357 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt,
1358 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt,
1359 x_source_entity_id_txt => tbh_rec.source_entity_id_txt,
1360 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
1361 x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
1362 x_book_loan_amt => tbh_rec.book_loan_amt,
1363 x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
1364 x_s_chg_birth_date => tbh_rec.s_chg_birth_date,
1365 x_s_chg_ssn => tbh_rec.s_chg_ssn,
1366 x_s_chg_last_name => tbh_rec.s_chg_last_name,
1367 x_b_chg_birth_date => tbh_rec.b_chg_birth_date,
1368 x_b_chg_ssn => tbh_rec.b_chg_ssn,
1369 x_b_chg_last_name => tbh_rec.b_chg_last_name,
1370 x_note_message => tbh_rec.note_message,
1371 x_full_resp_code => tbh_rec.full_resp_code,
1372 x_s_permt_county => tbh_rec.s_permt_county,
1373 x_b_permt_county => tbh_rec.b_permt_county,
1374 x_s_permt_country => tbh_rec.s_permt_country,
1375 x_b_permt_country => tbh_rec.b_permt_country,
1376 x_crdt_decision_status => tbh_rec.crdt_decision_status,
1377 x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
1378 x_deferment_request_code => tbh_rec.deferment_request_code,
1379 x_eft_authorization_code => tbh_rec.eft_authorization_code,
1380 x_requested_loan_amt => tbh_rec.requested_loan_amt,
1381 x_actual_record_type_code => tbh_rec.actual_record_type_code,
1382 x_reinstatement_amt => tbh_rec.reinstatement_amt,
1383 x_lender_use_txt => tbh_rec.lender_use_txt,
1384 x_guarantor_use_txt => tbh_rec.guarantor_use_txt,
1385 x_fls_approved_amt => tbh_rec.fls_approved_amt,
1386 x_flu_approved_amt => tbh_rec.flu_approved_amt,
1387 x_flp_approved_amt => tbh_rec.flp_approved_amt,
1388 x_alt_approved_amt => tbh_rec.alt_approved_amt,
1389 x_loan_app_form_code => tbh_rec.loan_app_form_code,
1390 x_alt_borrower_ind_flag => tbh_rec.alt_borrower_ind_flag,
1391 x_school_id_txt => tbh_rec.school_id_txt,
1392 x_cost_of_attendance_amt => tbh_rec.cost_of_attendance_amt,
1393 x_established_fin_aid_amount => tbh_rec.established_fin_aid_amount,
1394 x_student_electronic_sign_flag => tbh_rec.student_electronic_sign_flag,
1395 x_mpn_type_flag => tbh_rec.mpn_type_flag,
1396 x_school_use_txt => tbh_rec.school_use_txt,
1397 x_expect_family_contribute_amt => tbh_rec.expect_family_contribute_amt,
1398 x_borower_electronic_sign_flag => tbh_rec.borower_electronic_sign_flag,
1399 x_borower_credit_authoriz_flag => tbh_rec.borower_credit_authoriz_flag,
1400 x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd
1401
1402 );
1403
1404 END LOOP;
1405
1406 EXCEPTION
1407 WHEN app_exception.record_lock_exception THEN
1408 RAISE;
1409 WHEN others THEN
1410 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1411 fnd_message.set_token('NAME','IGF_SL_DL_ORIG_ACK.UPD_LOR_LOC_RECORD');
1412 fnd_file.put_line(fnd_file.log,SQLERRM);
1413 igs_ge_msg_stack.add;
1414 app_exception.raise_exception;
1415
1416 END upd_lor_loc_record;
1417
1418
1419 PROCEDURE dl_credit_ack(errbuf OUT NOCOPY VARCHAR2,
1420 retcode OUT NOCOPY NUMBER)
1421 IS
1422 ---------------------------------------------------------------------------
1423 -- sjadhav 18-Feb-2003 Bug 2758812 - FA 117 Build
1424 -- This procedure is the entry point for
1425 -- concurrent manager for DL PLUS Credit ack
1426 -- processing
1427 ---------------------------------------------------------------------------
1428
1429 BEGIN
1430 g_entry_point := 'CREDIT';
1431 dl_orig_ack(errbuf,retcode,NULL);
1432
1433 EXCEPTION
1434
1435 WHEN app_exception.record_lock_exception THEN
1436 ROLLBACK;
1437 retcode := 2;
1438 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
1439 igs_ge_msg_stack.conc_exception_hndl;
1440 WHEN file_not_loaded THEN
1441 ROLLBACK;
1442 retcode := 2;
1443 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
1444 igs_ge_msg_stack.conc_exception_hndl;
1445 WHEN OTHERS THEN
1446 ROLLBACK;
1447 retcode := 2;
1448 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1449 fnd_file.put_line(fnd_file.log,SQLERRM);
1450 igs_ge_msg_stack.conc_exception_hndl;
1451
1452 END dl_credit_ack;
1453
1454
1455 END igf_sl_dl_orig_ack;