[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_PNOTE_ACK
Source
1 PACKAGE BODY igf_sl_dl_pnote_ack AS
2 /* $Header: IGFSL15B.pls 120.1 2006/04/18 03:50:11 akomurav noship $ */
3 --
4 ---------------------------------------------------------------------------------
5 --
6 -- Created By : prchandr
7 -- Date Created On : 2000/05/09
8 -- Purpose : Package for Promissory Note Acknowledgement Process
9 -- Know limitations, enhancements or remarks
10 -- Change History
11 --
12 --------------------------------------------------------------------------------------
13 -- Who When What
14 ---------------------------------------------------------------------------------------
15 ---------------------------------------------------------------------------------------
16 -- akomurav 17-Apr-2006 Build FA161 and 162.
17 -- TBH Impact change done in igf_sl_lor_pkg.update_row().
18 ----------------------------------------------------------------------------------------
19
20 -- svuppala 4-Nov-2004 #3416936 FA 134 TBH impacts for newly added columns
21 ---------------------------------------------------------------------------------------
22
23 -- ayedubat 20-OCT-2004 FA 149 COD-XML Standards build bug # 3416863
24 -- Changed the logic as per the TD, FA149_TD_COD_XML_i1a.doc
25 -----------------------------------------------------------------------------------
26 -- veramach 29-Jan-2004 bug 3408092 added 2004-2005 in p_dl_version checks
27 -----------------------------------------------------------------------------------
28 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
29 -- a) Impact of adding the relationship_cd
30 -- in igf_sl_lor_all table and obsoleting
31 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
32 -- GUARANTOR_ID, DUNS_GUARNT_ID,
33 -- LENDER_ID, DUNS_LENDER_ID
34 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
35 -- RECIPIENT_TYPE,DUNS_RECIP_ID
36 -- RECIP_NON_ED_BRC_ID columns.
37 -----------------------------------------------------------------------------------------------------------------------------
38 -- veramach 23-SEP-2003 Bug 3104228: 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,appl_send_error_codes from igf_sl_lor
42 -- Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
43 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
44 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
45 -- chg_batch_id from igf_sl_lor_loc
46 ------------------------------------------------------------------------------------------------------------------------------
47 ---------------------------------------------------------------------------------
48 -- sjadhav 27-Mar-2003 Bug 2863960
49 -- Changed Disb Gross Amt to Disb Accepted Amt
50 -- to insert into igf_sl_awd_disb table
51 ---------------------------------------------------------------------------------
52 -- smvk 25-Feb-2003 Bug # 2758823. DL-03-04 updates Build.
53 ---------------------------------------------------------------------------------
54 -- masehgal 17-Feb-2002 # 2216956 FACR007
55 -- Added Elec_mpn_ind,
56 -- Borrow_sign_ind in
57 -- igf_sl_lor_pkg.update_row
58 -- and
59 -- igf_sl_cl_resp_r1
60 ---------------------------------------------------------------------------------
61 -- npalanis 11/jan/2002 The process Common Line Origination
62 -- Process( procedure place_holds_disb )
63 -- is modified to pick up disbursement
64 -- records that are in planned state,
65 -- insert records into IGF_DB_DISB_HOLDS
66 -- table with hold 'EXTERNAL' and
67 -- hold type 'SYSTEM' and also
68 -- update manual_hold_ind flag in
69 -- IGF_AW_AWD_DISB table to 'Y'.
70 -- enh bug no-2154941.
71 ---------------------------------------------------------------------------------
72 -- ssawhney 2nd jan TBH call of IGF_AW_AWD_DISB table
73 -- changed in Stud Emp build
74 -- en bug no 2161847
75 ---------------------------------------------------------------------------------
76 -- mesriniv 13/07/2001 Modified the call to
77 -- igf_aw_awd_disb_pkg.update_row
78 -- since 9 columns were added to the
79 -- table igf_aw_awd_disb_all.
80 ---------------------------------------------------------------------------------
81 --
82
83 g_lor_loc_rec igf_sl_lor_loc%ROWTYPE;
84 FILE_NOT_LOADED EXCEPTION;
85 p_disb_title VARCHAR2(1000);
86 p_disb_under_line VARCHAR2(1000);
87
88 g_log_title VARCHAR2(1000);
89 g_log_start_flag BOOLEAN;
90
91 PROCEDURE update_resp_edit(p_dlpnr_id igf_sl_dl_pnote_resp_all.dlpnr_id%TYPE,
92 p_status igf_sl_dl_pnote_resp_all.status%TYPE);
93
94 PROCEDURE compare_disbursements(p_loan_number igf_sl_loans_all.loan_number%TYPE ,
95 loaded_1rec igf_sl_dl_pnote_resp%ROWTYPE);
96
97
98 PROCEDURE log_message(p_loan_number igf_sl_loans_all.loan_number%TYPE) IS
99 BEGIN
100
101 IF g_log_start_flag = FALSE THEN
102 fnd_file.put_line(fnd_file.log, '');
103 fnd_file.put_line(fnd_file.log, '');
104 fnd_file.put_line(fnd_file.log, RPAD('-',80,'-'));
105
106 IF g_log_title IS NULL THEN
107 g_log_title := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER')||' : ';
108 END IF;
109
110 fnd_file.put_line(fnd_file.log, g_log_title||p_loan_number);
111 g_log_start_flag := TRUE;
112 END IF;
113
114 END log_message;
115
116
117 -- Procedure to Load the Data from the Data File into the Interface tables.
118 -- Before loading, it does lot of checks to ensure it is the right file
119 -- and returns the dbth_id, for further processing.
120
121 PROCEDURE dl_load_data(p_dbth_id OUT NOCOPY igf_sl_dl_batch.dbth_id%TYPE,
122 p_dl_version OUT NOCOPY igf_sl_dl_file_type.dl_version%TYPE,
123 p_dl_loan_catg OUT NOCOPY igf_sl_dl_file_type.dl_loan_catg%TYPE)
124 AS
125 /*************************************************************
126 Created By : prchandr
127 Date Created On : 2001/05/09
128 Purpose :
129 Know limitations, enhancements or remarks
130 Change History
131 Who When What
132 smvk 18-Feb-2003 Bug # 2758823. Coded for '2003-2004' structure.
133
134 (reverse chronological order - newest change first)
135 ***************************************************************/
136
137 l_temp VARCHAR2(30);
138 l_last_lort_id NUMBER;
139 l_number_rec NUMBER;
140 l_accept_rec NUMBER;
141 l_reject_rec NUMBER;
142 l_pending_rec NUMBER;
143
144 -- The fields have not been defined as tablename.field%TYPE on
145 -- purpose to feedback a proper message to the user
146 l_rec_batch_id VARCHAR2(100);
147 l_rec_message_class VARCHAR2(100);
148 l_rec_bth_creation_date VARCHAR2(100);
149 l_rec_batch_rej_code VARCHAR2(100);
150 l_rec_batch_type VARCHAR2(100);
151
152 l_rowid VARCHAR2(25);
153 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
154 l_dl_version igf_lookups_view.lookup_code%TYPE;
155 l_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
156 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
157
158 -- ## Cursor for Fetching Header records
159
160 CURSOR c_header IS
161 SELECT RTRIM(SUBSTR(record_data, 23, 23)) batch_id,
162 RTRIM(SUBSTR(record_data, 15, 8)) message_class,
163 RTRIM(SUBSTR(record_data, 46, 16)) bth_creation_date,
164 RTRIM(SUBSTR(record_data, 60, 2)) batch_rej_code,
165 RTRIM(SUBSTR(record_data, 23, 2)) batch_type
166 FROM igf_sl_load_file_t
167 WHERE lort_id = 1
168 AND record_data LIKE 'DL HEADER%'
169 AND file_type = 'DL_PNOTE_ACK';
170
171 -- ## Cursor for Fetching Trailer Records
172
173 CURSOR c_trailer IS
174 SELECT lort_id last_lort_id,
175 RTRIM(SUBSTR(record_data,15,7)) number_rec,
176 RTRIM(SUBSTR(record_data,22,5)) accept_rec,
177 RTRIM(SUBSTR(record_data,27,5)) reject_rec,
178 RTRIM(SUBSTr(record_data,32,5)) pending_rec
179 FROM igf_sl_load_file_t
180 WHERE lort_id = (SELECT MAX(lort_id) FROM igf_sl_load_file_t)
181 AND record_data LIKE 'DL TRAILER%'
182 AND file_type = 'DL_PNOTE_ACK';
183
184 CURSOR award_year_cur ( cp_dl_version IGF_SL_DL_SETUP.dl_version%TYPE) IS
185 SELECT ci_cal_type, ci_sequence_number
186 FROM igf_sl_dl_setup
187 WHERE dl_version = cp_dl_version;
188 award_year_rec award_year_cur%ROWTYPE;
189 l_cod_year_flag BOOLEAN;
190
191 BEGIN
192
193 -- Assuming that Header and Trailer record format does not change
194 -- since the header record contains Message Class Info, which
195 -- indicates the version of the File.
196
197 /*************** Check File Uploaded ********************/
198
199 -- Get the Header details
200 OPEN c_header;
201 FETCH c_header INTO l_rec_batch_id,
202 l_rec_message_class,
203 l_rec_bth_creation_date,
204 l_rec_batch_rej_code,
205 l_rec_batch_type;
206 IF c_header%NOTFOUND THEN
207 CLOSE c_header;
208 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
209 -- Message : Response File uploaded is not complete.
210 igs_ge_msg_stack.add;
211 RAISE FILE_NOT_LOADED;
212 END IF;
213 CLOSE c_header;
214
215 -- Check whether the File is valid/Not. (ie whether any wrong file is used)
216 -- File can be Origination Response For Stafford/PLUS OR a Credit Response.
217 -- Also, Check if the file is an OUTPUT File.
218
219
220 igf_sl_gen.get_dl_batch_details(l_rec_message_class, l_rec_batch_type,
221 l_dl_version, l_dl_file_type, l_dl_loan_catg);
222
223 IF l_dl_file_type = 'DL_PNOTE_ACK'
224 AND l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS','DL_STAFFORD_PLUS') THEN
225 NULL;
226 ELSE
227 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
228 -- Message : This is not a valid file
229 igs_ge_msg_stack.add;
230 RAISE FILE_NOT_LOADED;
231 END IF;
232
233 IF l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS','DL_STAFFORD_PLUS') THEN
234 -- This is an Direct Loan Promissory Note Acknowledgment File
235 fnd_message.set_name('IGF','IGF_SL_DL_ORIG_PNOTE_FILE');
236 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET);
237
238 END IF;
239
240 -- Get the System Award Year from l_dl_version
241 OPEN award_year_cur (l_dl_version);
242 FETCH award_year_cur INTO award_year_rec;
243 CLOSE award_year_cur;
244
245 -- Check wether the awarding year is COD-XML processing Year or not
246 l_cod_year_flag := NULL;
247 l_cod_year_flag := igf_sl_dl_validation.check_full_participant (award_year_rec.ci_cal_type,award_year_rec.ci_sequence_number,'DL');
248
249 -- If the award year is FULL_PARTICIPANT then raise the error message
250 -- and stop processing else continue the process
251 IF l_cod_year_flag THEN
252
253 fnd_message.set_name('IGF','IGF_SL_COD_NO_PNOTE_ACK');
254 fnd_file.put_line(fnd_file.log,fnd_message.get);
255 RETURN;
256
257 END IF;
258
259 -- Check whether the File was Fully transferred.
260 -- Get the record details in the File.
261 OPEN c_trailer;
262 FETCH c_trailer into l_last_lort_id, l_number_rec, l_accept_rec,
263 l_reject_rec, l_pending_rec;
264 IF c_trailer%NOTFOUND THEN
265 CLOSE c_trailer;
266 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
267 igs_ge_msg_stack.add;
268 RAISE FILE_NOT_LOADED;
269 END IF;
270 CLOSE c_trailer;
271
272
273
274 IF l_rec_batch_rej_code IS NOT NULL THEN
275 fnd_message.set_name('IGF','IGF_GE_BATCH_REJECTED');
276 fnd_message.set_token('BATCH', l_rec_batch_id);
277 fnd_message.set_token('REASON', igf_aw_gen.lookup_desc('IGF_SL_DL_BATCH_REJ',l_rec_batch_rej_code));
278 igs_ge_msg_stack.add;
279
280 -- Message : Batch #BATCH was rejected. Reason : #REASON.
281 RAISE FILE_NOT_LOADED;
282 END IF;
283
284
285 l_rowid := NULL;
286 igf_sl_dl_batch_pkg.insert_row (
287 x_mode => 'R',
288 x_rowid => l_rowid,
289 X_dbth_id => l_dbth_id,
290 X_batch_id => l_rec_batch_id,
291 X_message_class => l_rec_message_class,
292 X_bth_creation_date => TO_DATE(l_rec_bth_creation_date,'YYYYMMDDHH24MISS'),
293 X_batch_rej_code => l_rec_batch_rej_code,
294 X_end_date => NULL,
295 X_batch_type => l_rec_batch_type,
296 X_send_resp => 'R',
297 X_status => 'Y'
298 );
299
300
301
302
303 /*************** Load Transactions ********************/
304
305 DECLARE
306
307 l_actual_rec NUMBER DEFAULT 0;
308 l_lor_resp_num NUMBER;
309 lv_rowid VARCHAR2(100);
310 lc_rowid VARCHAR2(100);
311 l_dlpnr_id igf_sl_dl_pnote_resp.dlpnr_id%TYPE;
312 l_dlpdr_id igf_sl_dl_pdet_resp.dlpdr_id%TYPE;
313 l_c_elec_mpn_ind igf_sl_dl_pnote_resp.ELEC_MPN_IND%TYPE := NULL;
314
315 CURSOR c_trans
316 IS
317 SELECT record_data
318 FROM igf_sl_load_file_t
319 WHERE lort_id between 2 AND (l_last_lort_id-1)
320 AND file_type = 'DL_PNOTE_ACK';
321
322 BEGIN
323 IF l_dl_version = '2002-2003' THEN
324
325
326 -- File is Origination Response File For Stafford/PLUS.
327 IF l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS','DL_STAFFORD_PLUS') THEN
328
329 FOR orec IN c_trans LOOP
330 l_actual_rec := l_actual_rec + 1;
331 l_rowid := NULL;
332 l_c_elec_mpn_ind := SUBSTR(orec.record_data,190,1);
333 IF l_c_elec_mpn_ind IS NULL THEN
334 l_c_elec_mpn_ind := 'P';
335 ELSE
336 l_c_elec_mpn_ind := SUBSTR(orec.record_data,190,1);
337 END IF;
338 igf_sl_dl_pnote_resp_pkg.insert_row (
339 x_rowid => lv_rowid,
340 x_dlpnr_id => l_dlpnr_id,
341 x_dbth_id => l_dbth_id,
342 x_pnote_ack_date => TO_DATE(RTRIM(SUBSTR(orec.record_data, 1,8)),'YYYYMMDD'),
343 x_pnote_batch_id => RTRIM(SUBSTR(orec.record_data, 9,23)),
344 x_loan_number => RTRIM(SUBSTR(orec.record_data, 32,21)),
345 x_pnote_status => RTRIM(SUBSTR(orec.record_data, 53,1)),
346 x_pnote_rej_codes => RTRIM(SUBSTR(orec.record_data, 54,10)),
347 x_mpn_ind => RTRIM(SUBSTR(orec.record_data, 164,21)),
348 x_pnote_accept_amt => LTRIM(RTRIM(SUBSTR(orec.record_data, 185,5))),
349 x_elec_mpn_ind => l_c_elec_mpn_ind,
350 x_status => 'N',
351 x_mode => 'R'
352 );
353
354
355 -- ## For each Loan there will be Many Disbursement Records
356 -- ## Insert that record in IGF_SL_DL_PDET_RESP table
357
358 FOR i IN 0..19 LOOP
359 IF RTRIM(SUBSTR(orec.record_data, 64 + (i * 5),5)) IS NOT NULL THEN
360
361 igf_sl_dl_pdet_resp_pkg.insert_row (
362 x_mode => 'R',
363 x_rowid => lc_rowid,
364 x_dlpnr_id => l_dlpnr_id,
365 x_dlpdr_id => i + 1,
366 x_disb_gross_amt => TO_NUMBER(RTRIM(SUBSTR(orec.record_data, 64 + (i * 5),5))));
367 END IF;
368
369 END LOOP;
370
371 END LOOP;
372
373 END IF;
374 ELSIF l_dl_version IN ('2003-2004','2004-2005') THEN
375 -- File is Origination Response File For Stafford/PLUS.
376 IF l_dl_loan_catg in ('DL_STAFFORD','DL_PLUS','DL_STAFFORD_PLUS') THEN
377
378 FOR orec IN c_trans LOOP
379 l_actual_rec := l_actual_rec + 1;
380 l_rowid := NULL;
381 l_c_elec_mpn_ind := SUBSTR(orec.record_data,190,1);
382
383 IF l_c_elec_mpn_ind IS NULL THEN
384 l_c_elec_mpn_ind := 'P';
385 ELSE
386 l_c_elec_mpn_ind := SUBSTR(orec.record_data,190,1);
387 END IF;
388 igf_sl_dl_pnote_resp_pkg.insert_row (
389 x_rowid => lv_rowid,
390 x_dlpnr_id => l_dlpnr_id,
391 x_dbth_id => l_dbth_id,
392 x_pnote_ack_date => TO_DATE(RTRIM(SUBSTR(orec.record_data, 1,8)),'YYYYMMDD'),
393 x_pnote_batch_id => RTRIM(SUBSTR(orec.record_data, 9,23)),
394 x_loan_number => RTRIM(SUBSTR(orec.record_data, 32,21)),
395 x_pnote_status => RTRIM(SUBSTR(orec.record_data, 53,1)),
396 x_pnote_rej_codes => RTRIM(SUBSTR(orec.record_data, 54,10)),
397 x_mpn_ind => RTRIM(SUBSTR(orec.record_data, 164,21)),
398 x_pnote_accept_amt => NULL,
399 x_elec_mpn_ind => l_c_elec_mpn_ind,
400 x_status => 'N',
401 x_mode => 'R'
402 );
403
404 END LOOP;
405
406 END IF;
407
408 END IF; -- End of condition for VERSION.
409
410 IF l_actual_rec <> l_number_rec THEN
411 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
412 -- Message : The Actual Number of records does not match with the one mentioned in the trailer
413 igs_ge_msg_stack.add;
414 RAISE FILE_NOT_LOADED;
415 END IF;
416
417 END;
418
419 p_dbth_id := l_dbth_id;
420 p_dl_version := l_dl_version;
421 p_dl_loan_catg := l_dl_loan_catg;
422
423
424 EXCEPTION
425 WHEN app_exception.record_lock_exception THEN
426 RAISE;
427 WHEN FILE_NOT_LOADED THEN
428 RAISE;
429 WHEN OTHERS THEN
430 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
431 fnd_message.set_token('NAME','IGF_SL_DL_PNOTE_ACK.DL_LOAD_DATA');
432 fnd_file.put_line(fnd_file.log,SQLERRM);
433 igs_ge_msg_stack.add;
434 app_exception.raise_exception;
435 END dl_load_data;
436
437 /* MAIN PROCEDURE */
438 PROCEDURE process_ack(errbuf OUT NOCOPY VARCHAR2,
439 retcode OUT NOCOPY NUMBER,
440 P_org_id IN NUMBER)
441 AS
442 /*************************************************************
443 Created By : prchandr
444 Date Created On : 2001/05/09
445 Purpose :
446 Know limitations, enhancements or remarks
447 Change History
448 Who When What
449 ------------------------------------------------------------------------------
450 bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
451 Impact of adding the relationship_cd
452 in igf_sl_lor_all table and obsoleting
453 BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
454 GUARANTOR_ID, DUNS_GUARNT_ID,
455 LENDER_ID, DUNS_LENDER_ID
456 LEND_NON_ED_BRC_ID, RECIPIENT_ID
457 RECIPIENT_TYPE,DUNS_RECIP_ID
458 RECIP_NON_ED_BRC_ID columns.
459 ------------------------------------------------------------------------------
460 smvk 18-Feb-2003 Bug # 2758823. Coded for '2003-2004' structure.
461 masehgal 19-Feb-2002 # 2216956 FACR007
462 Added Stud_sign_ind , Borr_sign_ind
463 (reverse chronological order - newest change first)
464 ***************************************************************/
465
466 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
467 l_batch_type igf_sl_dl_batch.batch_type%TYPE;
468 l_rec_present VARCHAR2(10);
469 l_rec_updated VARCHAR2(10);
470 l_stat VARCHAR2(30);
471
472 l_dl_version igf_sl_dl_file_type.dl_version%TYPE;
473 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
474
475 -- ## Cursor to get the batch Records
476
477 CURSOR cur_batch IS
478 SELECT igf_sl_dl_batch.* FROM igf_sl_dl_batch
479 WHERE dbth_id = l_dbth_id;
480
481 -- ## Cursor to Fetch the Unprocessed Records from Promissory Note
482 -- ## Table
483
484 CURSOR cur_pnote_resp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE) IS
485 SELECT igf_sl_dl_pnote_resp.* FROM igf_sl_dl_pnote_resp
486 WHERE dbth_id = l_dbth_id
487 AND status = 'N';
488
489
490 -- To Check whether the Loan Exists in Financial Aid i.e exists in
491
492 CURSOR cur_loans(p_loan_number igf_sl_dl_pnote_resp_all.loan_number%TYPE) IS
493 SELECT fed_fund_code, count(*) countcol
494 FROM igf_sl_loans_v loans
495 WHERE loan_number=p_loan_number
496 GROUP BY fed_fund_code;
497
498 -- TO get loan_id from the particular loan_number
499
500 CURSOR cur_loanid(p_loan_number igf_sl_loans_all.loan_number%TYPE) IS
501 SELECT loan_id FROM
502 igf_sl_loans
503 WHERE trim(loan_number)=trim(p_loan_number);
504
505 -- To get the next sequence number
506 CURSOR c_seq_num IS
507 SELECT igf_sl_wf_process_s.NEXTVAL
508 FROM DUAL;
509
510
511 l_cur_loans cur_loans%ROWTYPE;
512 lcur_loanid cur_loanid%ROWTYPE;
513 l_seq_no NUMBER;
514 -- ## User Defined Exceptions
515
516 invalid_loan EXCEPTION;
517 Rec_no_update EXCEPTION;
518 no_loan_id EXCEPTION;
519
520 BEGIN
521
522 retcode := 0;
523 -- ## Set the Org ID
524 igf_aw_gen.set_org_id(p_org_id);
525
526
527 -- Load the Data into the Batch and Response Tables
528 dl_load_data(l_dbth_id, l_dl_version, l_dl_loan_catg);
529
530 IF l_dl_loan_catg IN ('DL_PLUS','DL_STAFFORD','DL_STAFFORD_PLUS') THEN
531
532
533 FOR dbth_rec IN cur_batch LOOP -- ## Outer Loop for selecting from Batch Table
534
535
536 FOR resp_rec IN cur_pnote_resp(l_dbth_id) LOOP -- ## Selects records from PNOTE_ERSP table
537 BEGIN
538
539 g_log_start_flag := FALSE;
540 l_rec_present := 'N';
541 l_rec_updated := 'N';
542
543 OPEN cur_loans(resp_rec.loan_number);
544 FETCH cur_loans INTO l_cur_loans;
545
546 -- Check if the Loan is in Financial Aid else skip the record
547 -- and raise a exception
548
549 IF l_cur_loans.countcol = 0 THEN
550 log_message(resp_rec.loan_number);
551 update_resp_edit(resp_rec.dlpnr_id,'I'); -- ## Set the status as Invalid Loan
552 CLOSE cur_loans;
553 RAISE invalid_loan;
554 END IF;
555 CLOSE cur_loans;
556
557 -- ## Check for Reject Codes IF EXISTS then
558 -- ## skip the record and PUt the message in LOG File
559 -- ## with the reject Descriptions
560 IF resp_rec.pnote_rej_codes IS NOT NULL THEN
561
562 log_message(resp_rec.loan_number);
563 fnd_message.set_name('IGF','IGF_SL_DL_REJ_EXISTS');
564 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET);
565 resp_rec.pnote_rej_codes := translate(resp_rec.pnote_rej_codes,'0',' ');
566 DECLARE
567 CURSOR c_rej IS
568 SELECT lookup_code, meaning FROM igf_lookups_view
569 WHERE lookup_type = 'IGF_SL_PNOTE_REJ_CODES'
570 AND lookup_code IN (LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 1,2))),
571 LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 3,2))),
572 LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 5,2))),
573 LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 7,2))),
574 LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 9,2))));
575 BEGIN
576 igf_sl_edit.delete_edit(resp_rec.loan_number, 'P');
577 FOR rrec IN c_rej LOOP
578 fnd_file.put_line(fnd_file.log, ' '||RPAD(rrec.lookup_code,3)||' - '||rrec.meaning);
579 igf_sl_edit.insert_edit(resp_rec.loan_number, 'P', 'IGF_SL_PNOTE_REJ_CODES',
580 rrec.lookup_code, '', '');
581 END LOOP;
582 END;
583 END IF;
584
585 -- ## Check if PNOTE is Rejected then Skip the Record
586 -- ## and call for the Work Flow Process.
587
588 IF resp_rec.pnote_status='R' THEN
589
590 OPEN cur_loanid(resp_rec.loan_number);
591
592 FETCH cur_loanid INTO lcur_loanid;
593 log_message(resp_rec.loan_number);
594 fnd_message.set_name('IGF','IGF_SL_DL_PNOTE_REJECTED');
595 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET);
596
597 IF cur_loanid%NOTFOUND THEN
598 CLOSE cur_loanid;
599 log_message(resp_rec.loan_number);
600 update_resp_edit(resp_rec.dlpnr_id,'I');
601 RAISE invalid_loan;
602 END IF;
603
604 OPEN c_seq_num;
605 FETCH c_seq_num INTO l_seq_no;
606 CLOSE c_seq_num;
607
608 wf_engine.CreateProcess
609 (itemtype => 'DLPNA',
610 itemkey => l_seq_no,
611 process => 'WF_DLPNOTE_REJ');
612
613
614 wf_engine.SetItemAttrNumber('DLPNA',
615 l_seq_no,
616 'VDBTHID',
617 l_dbth_id);
618
619 wf_engine.SetItemAttrNumber
620 ('DLPNA',
621 l_seq_no,
622 'VLOANID',
623 lcur_loanid.loan_id);
624
625 wf_engine.StartProcess
626 (itemtype => 'DLPNA',
627 itemkey =>l_seq_no );
628
629 END IF;
630
631
632 DECLARE
633 CURSOR c_tbh_cur IS
634 SELECT igf_sl_lor.* FROM igf_sl_lor
635 WHERE loan_id = (SELECT loan_id FROM igf_sl_loans lar
636 WHERE loan_number = resp_rec.loan_number)
637 FOR UPDATE NOWAIT ;
638 BEGIN
639
640 FOR tbh_rec IN c_tbh_cur LOOP
641 -- ## Check if Promissory Note is Accepted and Ack Date is NOT NULL
642 -- ## then Print the message and skip the record and raise a
643 -- ## User Defined exception
644 IF tbh_rec.pnote_status='A' AND tbh_rec.pnote_ack_date IS NOT NULL THEN
645 log_message(resp_rec.loan_number);
646 fnd_message.set_name('IGF','IGF_SL_DL_AlREADY_ACCEPTED');
647 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET);
648 update_resp_edit(resp_rec.dlpnr_id,'U');
649 RAISE Rec_no_update;
650 ELSE
651
652 -- ## If all the conditions are satisfied then
653 -- ## check if PNOTE_ACK_DATE in PNOTE TABLE is greater than
654 -- ## that in LOR table then update the LOR table with
655 -- ## PNOTE related details in LOR table
656
657 tbh_rec.pnote_status := resp_rec.pnote_status;
658 tbh_rec.pnote_status_date := TRUNC(sysdate);
659 tbh_rec.pnote_batch_id := resp_rec.pnote_batch_id;
660 tbh_rec.pnote_ack_date := resp_rec.pnote_ack_date;
661 tbh_rec.pnote_id := resp_rec.mpn_ind;
662 tbh_rec.pnote_accept_amt := resp_rec.pnote_accept_amt;
663 tbh_rec.pnote_accept_date := TRUNC(sysdate);
664 tbh_rec.elec_mpn_ind := resp_rec.elec_mpn_ind;
665
666 igf_sl_lor_pkg.update_row (
667 X_Mode => 'R',
668 x_rowid => tbh_rec.row_id,
669 x_origination_id => tbh_rec.origination_id,
670 x_loan_id => tbh_rec.loan_id,
671 x_sch_cert_date => tbh_rec.sch_cert_date,
672 x_orig_status_flag => tbh_rec.orig_status_flag,
673 x_orig_batch_id => tbh_rec.orig_batch_id,
674 x_orig_batch_date => tbh_rec.orig_batch_date,
675 x_chg_batch_id => NULL,
676 x_orig_ack_date => tbh_rec.orig_ack_date,
677 x_credit_override => tbh_rec.credit_override,
678 x_credit_decision_date => tbh_rec.credit_decision_date,
679 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
680 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
681 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
682 x_pnote_status => tbh_rec.pnote_status,
683 x_pnote_status_date => tbh_rec.pnote_status_date,
684 x_pnote_id => tbh_rec.pnote_id,
685 x_pnote_batch_id => tbh_rec.pnote_batch_id,
686 x_pnote_ack_date => tbh_rec.pnote_ack_date,
687 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
688 x_pnote_print_ind => tbh_rec.pnote_print_ind,
689 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
690 x_pnote_accept_date => tbh_rec.pnote_accept_date,
691 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
692 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
693 x_orig_fee_perct => tbh_rec.orig_fee_perct,
694 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
695 x_borw_interest_ind => tbh_rec.borw_interest_ind,
696 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
697 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
698 x_guarantee_amt => tbh_rec.guarantee_amt,
699 x_guarantee_date => tbh_rec.guarantee_date,
700 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
701 x_guarnt_status_code => tbh_rec.guarnt_status_code,
702 x_guarnt_status_date => tbh_rec.guarnt_status_date,
703 x_lend_apprv_denied_code => NULL,
704 x_lend_apprv_denied_date => NULL,
705 x_lend_status_code => tbh_rec.lend_status_code,
706 x_lend_status_date => tbh_rec.lend_status_date,
707 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
708 x_grade_level_code => tbh_rec.grade_level_code,
709 x_enrollment_code => tbh_rec.enrollment_code,
710 x_anticip_compl_date => tbh_rec.anticip_compl_date,
711 x_borw_lender_id => NULL,
712 x_duns_borw_lender_id => NULL,
713 x_guarantor_id => NULL,
714 x_duns_guarnt_id => NULL,
715 x_prc_type_code => tbh_rec.prc_type_code,
716 x_cl_seq_number => tbh_rec.cl_seq_number,
717 x_last_resort_lender => tbh_rec.last_resort_lender,
718 x_lender_id => NULL,
719 x_duns_lender_id => NULL,
720 x_lend_non_ed_brc_id => NULL,
721 x_recipient_id => NULL,
722 x_recipient_type => NULL,
723 x_duns_recip_id => NULL,
724 x_recip_non_ed_brc_id => NULL,
725 x_rec_type_ind => tbh_rec.rec_type_ind,
726 x_cl_loan_type => tbh_rec.cl_loan_type,
727 x_cl_rec_status => NULL,
728 x_cl_rec_status_last_update => NULL,
729 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
730 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
731 x_mpn_confirm_code => NULL,
732 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
733 x_appl_loan_phase_code => NULL,
734 x_appl_loan_phase_code_chg => NULL,
735 x_appl_send_error_codes => NULL,
736 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
737 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
738 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
739 x_act_interest_rate => tbh_rec.act_interest_rate,
740 x_service_type_code => tbh_rec.service_type_code,
741 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
742 x_sch_refund_amt => tbh_rec.sch_refund_amt,
743 x_sch_refund_date => tbh_rec.sch_refund_date,
744 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
745 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
746 x_p_person_id => tbh_rec.p_person_id,
747 x_p_ssn_chg_date => NULL,
748 x_p_dob_chg_date => NULL,
749 x_p_permt_addr_chg_date => tbh_rec.p_permt_addr_chg_date,
750 x_p_default_status => tbh_rec.p_default_status,
751 x_p_signature_code => tbh_rec.p_signature_code,
752 x_p_signature_date => tbh_rec.p_signature_date,
753 x_s_ssn_chg_date => NULL,
754 x_s_dob_chg_date => NULL,
755 x_s_permt_addr_chg_date => tbh_rec.s_permt_addr_chg_date,
756 x_s_local_addr_chg_date => NULL,
757 x_s_default_status => tbh_rec.s_default_status,
758 x_s_signature_code => tbh_rec.s_signature_code,
759 x_elec_mpn_ind => tbh_rec.elec_mpn_ind,
760 x_borr_sign_ind => tbh_rec.borr_sign_ind,
761 x_stud_sign_ind => tbh_rec.stud_sign_ind,
762 x_borr_credit_auth_code => tbh_rec.borr_credit_auth_code,
763 x_relationship_cd => tbh_rec.relationship_cd,
764 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
765 x_cps_trans_num => tbh_rec.cps_trans_num,
766 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt,
767 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt,
768 x_crdt_decision_status => tbh_rec.crdt_decision_status,
769 x_note_message => tbh_rec.note_message,
770 x_book_loan_amt => tbh_rec.book_loan_amt,
771 x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
772 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
773 x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
774 x_requested_loan_amt => tbh_rec.requested_loan_amt,
775 x_eft_authorization_code => tbh_rec.eft_authorization_code,
776 x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
777 x_deferment_request_code => tbh_rec.deferment_request_code ,
778 x_actual_record_type_code => tbh_rec.actual_record_type_code,
779 x_reinstatement_amt => tbh_rec.reinstatement_amt,
780 x_school_use_txt => tbh_rec.school_use_txt,
781 x_lender_use_txt => tbh_rec.lender_use_txt,
782 x_guarantor_use_txt => tbh_rec.guarantor_use_txt,
783 x_fls_approved_amt => tbh_rec.fls_approved_amt,
784 x_flu_approved_amt => tbh_rec.flu_approved_amt,
785 x_flp_approved_amt => tbh_rec.flp_approved_amt,
786 x_alt_approved_amt => tbh_rec.alt_approved_amt,
787 x_loan_app_form_code => tbh_rec.loan_app_form_code,
788 x_override_grade_level_code => tbh_rec.override_grade_level_code,
789 x_b_alien_reg_num_txt => tbh_rec.b_alien_reg_num_txt,
790 x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd,
791 x_acad_begin_date => tbh_rec.acad_begin_date,
792 x_acad_end_date => tbh_rec.acad_end_date
793 );
794
795 -- ## Incase of PLUS loans then addtional Validations need to be
796 -- ## done. Compare the Disbursements for uniqueness in PDET_RESP
797 -- ## table and award Disb Table.
798 IF igf_sl_gen.chk_dl_plus(l_cur_loans.fed_fund_code) = 'TRUE' AND
799 l_dl_version = '2002-2003' THEN
800
801 DECLARE
802 CURSOR cur_award(p_loan_number igf_sl_dl_pnote_resp_all.loan_number%TYPE) IS
803 SELECT NVL(loan_amt_offered,loan_amt_accepted) loan_amt FROM
804 igf_sl_loans_v WHERE
805 loan_number = TRIM(p_loan_number);
806
807 lcur_award cur_award%ROWTYPE;
808 BEGIN
809
810 OPEN cur_award(resp_rec.loan_number);
811 FETCH cur_award INTO lcur_award;
812 EXIT WHEN cur_award%NOTFOUND;
813 IF resp_rec.pnote_accept_amt <> lcur_award.loan_amt THEN
814 log_message(resp_rec.loan_number);
815 fnd_file.put_line(fnd_file.log,
816 RPAD(igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','PNOTE_ACCEPT_AMT'),40,' ')||' : '||
817 TO_CHAR(resp_rec.pnote_accept_amt));
818 fnd_file.put_line(fnd_file.log,
819 RPAD(igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_AMT_ACCEPTED'),40,' ')||' : '||
820 TO_CHAR(lcur_award.loan_amt));
821 END IF;
822 END;
823 END IF;
824 IF l_dl_version = '2002-2003' THEN
825 compare_disbursements(resp_rec.loan_number,resp_rec);
826 END IF;
827 END IF;
828
829 update_resp_edit(resp_rec.dlpnr_id,'Y');
830
831 END LOOP;
832
833 END;
834
835
836
837 EXCEPTION
838 WHEN invalid_loan THEN
839 fnd_message.set_name('IGF','IGF_SL_DL_INVALID_LOAN');
840 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET);
841 WHEN Rec_no_update THEN
842 NULL;
843 WHEN no_loan_id THEN
844 NULL;
845 END;
846
847 END LOOP;
848
849 END LOOP;
850
851 END IF; -- Condition for Loan Category Checking
852
853 fnd_file.put_line(fnd_file.log, '');
854
855 COMMIT;
856
857 EXCEPTION
858 WHEN app_exception.record_lock_exception THEN
859 ROLLBACK;
860 retcode := 2;
861 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
862 igs_ge_msg_stack.conc_exception_hndl;
863 WHEN FILE_NOT_LOADED THEN
864 ROLLBACK;
865 retcode := 2;
866 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
867 igs_ge_msg_stack.conc_exception_hndl;
868 WHEN OTHERS THEN
869 ROLLBACK;
870 retcode := 2;
871 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
872 fnd_file.put_line(fnd_file.log,SQLERRM);
873 igs_ge_msg_stack.conc_exception_hndl;
874
875 END process_ack;
876
877 PROCEDURE update_resp_edit(p_dlpnr_id igf_sl_dl_pnote_resp_all.dlpnr_id%TYPE,p_status igf_sl_dl_pnote_resp_all.status%TYPE) IS
878
879 CURSOR c_tbh_cur IS
880 SELECT resp.* FROM igf_sl_dl_pnote_resp resp
881 WHERE dlpnr_id = p_dlpnr_id
882 FOR UPDATE NOWAIT;
883
884 /*************************************************************
885 Created By : prchandr
886 Date Created On : 2001/05/09
887 Purpose : Procedure to Update the PNOTE_RESP table
888 Know limitations, enhancements or remarks
889 Change History
890 Who When What
891 masehgal 19-Feb-2002 # 2216956 FACR007
892 Added Elec_mpn_ind
893 (reverse chronological order - newest change first)
894 ***************************************************************/
895 BEGIN
896
897 FOR tbh_cur IN c_tbh_cur LOOP
898
899 igf_sl_dl_pnote_resp_pkg.update_row (
900 x_mode => 'R',
901 x_rowid => tbh_cur.row_id,
902 x_dlpnr_id => tbh_cur.dlpnr_id,
903 x_dbth_id => tbh_cur.dbth_id,
904 x_pnote_ack_date => tbh_cur.pnote_ack_date,
905 x_pnote_batch_id => tbh_cur.pnote_batch_id,
906 x_loan_number => tbh_cur.loan_number,
907 x_pnote_status => tbh_cur.pnote_status,
908 x_pnote_rej_codes => tbh_cur.pnote_rej_codes,
909 x_mpn_ind => tbh_cur.mpn_ind,
910 x_pnote_accept_amt => tbh_cur.pnote_accept_amt,
911 x_elec_mpn_ind => tbh_cur.elec_mpn_ind,
912 x_status => p_status
913 );
914
915 END LOOP;
916
917 END update_resp_edit;
918
919 PROCEDURE compare_disbursements(p_loan_number igf_sl_loans_all.loan_number%TYPE ,
920 loaded_1rec igf_sl_dl_pnote_resp%ROWTYPE)
921 AS
922 /***************************************************************
923 Created By : prchandr
924 Date Created By : 2000/12/07
925 Purpose : To Compare the Disbursement Amounts specified in the
926 FILE WITH THAT in the IGF_AW_AWD_DISB table
927 Known Limitations,Enhancements or Remarks
928 Change History :
929 Who When What
930 ***************************************************************/
931
932 l_old_count NUMBER;
933 l_new_count NUMBER;
934 l_award_id igf_aw_awd_disb.award_id%TYPE;
935 l_disb_num igf_aw_awd_disb.disb_num%TYPE;
936 l_disb_gross_amt igf_aw_awd_disb.disb_gross_amt%TYPE;
937
938
939 --Count the No.of Disbursements for the award id in Awards Disbursements Table
940 CURSOR cur_count_old_disb
941 IS
942 SELECT award_id, NVL(COUNT(disb_num),0) FROM igf_aw_awd_disb
943 WHERE award_id = (SELECT award_id FROM igf_sl_loans
944 WHERE loan_number = p_loan_number)
945 GROUP BY award_id;
946
947 --Count the No.of Disbursements for the award id in Response8 Disbursements Table
948 CURSOR cur_count_new_disb
949 IS
950 SELECT NVL(COUNT(resp.dlpdr_id),0) FROM igf_sl_dl_pdet_resp resp
951 WHERE dlpnr_id = loaded_1rec.dlpnr_id;
952
953 -- Check if the Disb-Num and Disb_gross_amts are same between the File and
954 -- currently in our system.
955 CURSOR cur_disb_same_data IS
956 SELECT disb_num, disb_gross_amt FROM
957 ((
958 SELECT disb_num, NVL(disb_accepted_amt,0) disb_gross_amt FROM igf_aw_awd_disb adisb
959 WHERE award_id = l_award_id
960 MINUS
961 SELECT dlpdr_id, disb_gross_amt FROM igf_sl_dl_pdet_resp resp
962 WHERE dlpnr_id = loaded_1rec.dlpnr_id
963 )
964 UNION
965 (SELECT dlpdr_id, disb_gross_amt FROM igf_sl_dl_pdet_resp resp
966 WHERE dlpnr_id = loaded_1rec.dlpnr_id
967 MINUS
968 SELECT disb_num, NVL(disb_accepted_amt,0) disb_gross_amt FROM igf_aw_awd_disb adisb
969 WHERE award_id = l_award_id
970 )
971 );
972
973 --select the NewDisbursements for the award id in Response8 Disbursements Table
974 CURSOR cur_new_disbursements
975 IS
976 SELECT * FROM igf_sl_dl_pdet_resp resp
977 WHERE dlpnr_id = loaded_1rec.dlpnr_id
978 ORDER By dlpnr_id;
979
980 --Select the old Disbursements for the award id in Awards Disbursements Table
981 CURSOR cur_old_disbursements
982 IS
983 SELECT * FROM igf_aw_awd_disb
984 WHERE award_id = l_award_id
985 ORDER BY disb_num;
986
987
988 --To update the Resp
989 --Records with Y as Record Status
990
991
992 PROCEDURE show_disb_details
993 AS
994
995
996 -- Show all Disb details (From OFA)
997 -- Disb-Num Disb-Gross
998 -- Show all Disb detail (From File)
999 -- Disb-Num Disb-Gross
1000
1001 BEGIN
1002
1003 IF p_disb_title IS NULL THEN
1004 p_disb_title := LPAD(igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_NUM'),30)
1005 ||LPAD(igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_GROSS_AMT'),30);
1006
1007
1008 p_disb_under_line := RPAD('-',30,'-')
1009 ||RPAD('-',30,'-');
1010 END IF;
1011
1012 fnd_file.put_line(fnd_file.log,' ');
1013 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_GEN','OFA_DISB_DETAILS'));
1014 fnd_file.put_line(fnd_file.log,p_disb_title);
1015 fnd_file.put_line(fnd_file.log,p_disb_under_line);
1016 --To show the Disbursement Details in OFA
1017
1018 FOR OFA_disb IN cur_old_disbursements
1019 LOOP
1020 fnd_file.put_line(fnd_file.log,
1021 LPAD(TO_CHAR(OFA_disb.disb_num),30)
1022 ||LPAD(TO_CHAR(OFA_disb.disb_accepted_amt),30));
1023 END LOOP;
1024
1025 --To show the Disbursement details in File
1026 fnd_file.put_line(fnd_file.log,' ');
1027 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_GEN','LOC_DISB_DETAILS'));
1028 fnd_file.put_line(fnd_file.log,p_disb_title);
1029 fnd_file.put_line(fnd_file.log,p_disb_under_line);
1030
1031 FOR LOC_disb IN cur_new_disbursements
1032 LOOP
1033 fnd_file.put_line(fnd_file.log,
1034 LPAD(TO_CHAR(LOC_disb.dlpdr_id),30)
1035 ||LPAD(TO_CHAR(LOC_disb.disb_gross_amt),30));
1036
1037 END LOOP;
1038
1039 END show_disb_details;
1040
1041
1042 PROCEDURE place_disb_holds(p_award_id igf_aw_awd_disb.award_id%TYPE)
1043 AS
1044 CURSOR c_tbh_cur IS
1045 SELECT adisb.* FROM igf_aw_awd_disb adisb
1046 WHERE award_id = p_award_id and
1047 trans_type = 'P';
1048
1049 CURSOR cur_disb_hold_exists(cp_award_id igf_db_disb_holds.award_id%TYPE,
1050 cp_disb_num igf_db_disb_holds.disb_num%TYPE,
1051 cp_hold igf_db_disb_holds.hold%TYPE )
1052 IS
1053 SELECT COUNT(row_id)
1054 FROM igf_db_disb_holds
1055 WHERE award_id = cp_award_id
1056 AND disb_num = cp_disb_num
1057 AND hold = cp_hold
1058 AND release_flag ='N';
1059
1060 l_rowid VARCHAR2(30);
1061 l_hold_id igf_db_disb_holds.hold_id%TYPE;
1062 l_rec_count NUMBER;
1063
1064 BEGIN
1065
1066 FOR tbh_rec in c_tbh_cur LOOP
1067
1068 l_rowid := NULL;
1069 l_hold_id := NULL;
1070 OPEN cur_disb_hold_exists(tbh_rec.award_id,tbh_rec.disb_num,'DL_PROM');
1071 FETCH cur_disb_hold_exists into l_rec_count;
1072 IF NOT ( nvl(l_rec_count,0) > 0) THEN
1073
1074 igf_db_disb_holds_pkg.insert_row (
1075 x_mode => 'R',
1076 x_rowid => l_rowid,
1077 x_hold_id => l_hold_id,
1078 x_award_id => tbh_rec.award_id,
1079 x_disb_num => tbh_rec.disb_num,
1080 x_hold => 'DL_PROM',
1081 x_hold_type => 'SYSTEM',
1082 x_hold_date => TRUNC(sysdate),
1083 x_release_flag => 'N',
1084 x_release_reason => NULL,
1085 x_release_date => NULL
1086 );
1087 END IF;
1088 Close cur_disb_hold_exists;
1089
1090 END LOOP;
1091 END place_disb_holds;
1092
1093
1094 BEGIN
1095
1096 --Fetch the Old and New No.of Records
1097
1098 OPEN cur_count_old_disb;
1099 FETCH cur_count_old_disb INTO l_award_id, l_old_count;
1100 IF l_old_count=0 THEN
1101 CLOSE cur_count_old_disb;
1102 RAISE NO_DATA_FOUND;
1103 END IF;
1104 CLOSE cur_count_old_disb;
1105
1106
1107 OPEN cur_count_new_disb;
1108 FETCH cur_count_new_disb INTO l_new_count;
1109 IF l_new_count=0 THEN
1110 CLOSE cur_count_new_disb;
1111 RAISE NO_DATA_FOUND;
1112 END IF;
1113 CLOSE cur_count_new_disb;
1114
1115 IF l_old_count <> l_new_count THEN
1116
1117 log_message(p_loan_number);
1118 -- Show all details like Old(From OFA) and New Disbursement Amounts(From File)
1119 show_disb_details;
1120
1121 -- Place Process Holds on All the Disbursement records.
1122 place_disb_holds(l_award_id);
1123
1124 ELSE
1125
1126 OPEN cur_disb_same_data;
1127 FETCH cur_disb_same_data into l_disb_num, l_disb_gross_amt;
1128 IF cur_disb_same_data%NOTFOUND THEN
1129
1130 -- Indicates that disbursement data (Number of Disbursements and
1131 -- disb-gross-amts and loan_requested_amt ) are currently same,
1132 -- what was sent to the external processor.
1133
1134 NULL;
1135 ELSE
1136
1137 log_message(p_loan_number);
1138 show_disb_details;
1139
1140 -- Place Process Holds on All the Disbursement records.
1141 place_disb_holds(l_award_id);
1142
1143 END IF;
1144 CLOSE cur_disb_same_data;
1145
1146 END IF;
1147
1148 EXCEPTION
1149 WHEN app_exception.record_lock_exception THEN
1150 RAISE;
1151 WHEN OTHERS THEN
1152 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1153 fnd_message.set_token('NAME','IGF_SL_DL_PNOTE_ACK.COMPARE_DISBURSEMENTS');
1154 fnd_file.put_line(fnd_file.log,SQLERRM);
1155 igs_ge_msg_stack.add;
1156 app_exception.raise_exception;
1157 END compare_disbursements;
1158
1159 END igf_sl_dl_pnote_ack;