[Home] [Help]
PACKAGE BODY: APPS.IGF_DB_DL_ORIG_ACK
Source
1 PACKAGE BODY igf_db_dl_orig_ack AS
2 /* $Header: IGFDB03B.pls 120.2 2006/02/01 02:39:50 ridas ship $ */
3
4 /*************************************************************
5 Created By : prchandr
6 Date Created On : 2000/12/20
7 Purpose :
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 (reverse chronological order - newest change first)
12 bvisvana 19-Jul-2005 Bug 4101317 - added new exception INVALID_PHASE_IN_PARTICIPANT
13 ayedubat 20-OCT-2004 FA 149 COD-XML Standards build bug # 3416863
14 Replaced the reference of igf_db_awd_disb_dtl with igf_aw_db_chg_dtls table
15 Changed the logic as per the TD, FA149_TD_COD_XML_i1a.doc
16 veramach 29-Jan-2004 bug 3408092 added 2004-2005 in l_dl_version checks
17 sjadhav 31-Mar-2003 Bug 2863960
18 added desc for disb data displayed
19 in the log file
20
21 vvutukur 21-Feb-2003 Enh#2758823.FA117 Build.
22 Modified procedure disb_load_data.
23
24 ***************************************************************/
25
26
27
28 FILE_NOT_LOADED EXCEPTION;
29 SKIP_THIS_RECORD EXCEPTION;
30 INVALID_PHASE_IN_PARTICIPANT EXCEPTION;
31
32 -- Procedure to Load the Data from the Data File into the Interface tables.
33 -- Before loading, it does lot of checks to ensure it is the right file
34 -- and returns the dbth_id, for further processing.
35 PROCEDURE disb_load_data(p_dbth_id OUT NOCOPY igf_sl_dl_batch.dbth_id%TYPE,
36 p_batch_id OUT NOCOPY igf_sl_dl_batch.batch_id%TYPE,
37 p_dl_version OUT NOCOPY igf_sl_dl_file_type.dl_version%TYPE,
38 p_dl_file_type OUT NOCOPY igf_sl_dl_file_type.dl_loan_catg%TYPE)
39 AS
40 /*************************************************************
41 Created By : Prajeesh Chandran .K
42 Date Created On : 2000/12/20
43 Purpose :Procedure to load the datas in igf_db_dl_resp from igf_load_file_T
44 Know limitations, enhancements or remarks
45 Change History
46 Bug :2255281
47 Desc:DL VERSION TO BE CHECKED FOR DL CHANGE ORIG AND DISB ORIGINATION LOAN PROGRAMS.
48 Who When What
49 vvutukur 21-Feb-2003 Enh#2758823.FA117 Build. Modified the if condition to include 03-04 removing 02-03.
50 ie., Changed IF l_dl_version IN ('2001-2002','2002-2003') to IF l_dl_version IN ('2002-2003','2003-2004').
51 In the call to igf_db_dl_disb_resp_pkg.insert_row passed NULL to x_sch_code_status,x_loan_num_status,
52 x_disb_num_status,x_trans_date_status,x_trans_date_status.
53 mesriniv 19-MAR-2002 Added Version 2002-2003 check
54 (reverse chronological order - newest change first)
55 ***************************************************************/
56
57 l_temp VARCHAR2(30);
58 l_last_lort_id NUMBER;
59 l_number_rec NUMBER;
60 l_accept_rec NUMBER;
61 l_reject_rec NUMBER;
62 l_pending_rec NUMBER;
63
64 -- The fields have not been defined as tablename.field%TYPE on
65 -- purpose to feedback a proper message to the user
66 l_rec_batch_id VARCHAR2(100);
67 l_rec_message_class VARCHAR2(100);
68 l_rec_bth_creation_date VARCHAR2(100);
69 l_rec_batch_rej_code VARCHAR2(100);
70 l_rec_batch_type VARCHAR2(100);
71
72
73 l_rowid VARCHAR2(25);
74 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
75 l_dl_version igf_lookups_view.lookup_code%TYPE;
76 l_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
77 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
78 x_ddrp_id igf_db_dl_disb_resp.ddrp_id%TYPE;
79 -- ## Cursor for formulating the header
80
81 CURSOR c_header
82 IS
83 SELECT RTRIM(SUBSTR(record_data, 23, 23)) batch_id,
84 RTRIM(SUBSTR(record_data, 15, 8)) message_class,
85 RTRIM(SUBSTR(record_data, 46, 16)) bth_creation_date,
86 RTRIM(SUBSTR(record_data, 60, 2)) batch_rej_code,
87 RTRIM(SUBSTR(record_data, 23, 2)) batch_type
88 FROM igf_sl_load_file_t
89 WHERE lort_id = 1
90 AND record_data LIKE 'DL HEADER%'
91 AND file_type = 'DL_DISB';
92
93
94 -- ## Cursor for formulating the trailer
95
96 CURSOR c_trailer
97 IS
98 SELECT lort_id last_lort_id,
99 RTRIM(SUBSTR(record_data,15,7)) number_rec,
100 RTRIM(SUBSTR(record_data,22,5)) accept_rec,
101 RTRIM(SUBSTR(record_data,27,5)) reject_rec,
102 RTRIM(SUBSTr(record_data,32,5)) pending_rec
103 FROM igf_sl_load_file_t
104 WHERE lort_id = (SELECT MAX(lort_id) FROM igf_sl_load_file_t)
105 AND record_data LIKE 'DL TRAILER%'
106 AND file_type = 'DL_DISB';
107
108 BEGIN
109
110 -- Assuming that Header and Trailer record format does not change
111 -- since the header record contains Message Class Info, which
112 -- indicates the version of the File.
113
114 -- ************* Check File Uploaded ********************
115
116 -- Get the Header details
117 OPEN c_header;
118 FETCH c_header INTO l_rec_batch_id,
119 l_rec_message_class,
120 l_rec_bth_creation_date,
121 l_rec_batch_rej_code,
122 l_rec_batch_type;
123
124 IF c_header%NOTFOUND THEN
125 CLOSE c_header;
126 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
127 -- Message : Response File uploaded is not complete.
128 igs_ge_msg_stack.add;
129 RAISE FILE_NOT_LOADED;
130 END IF;
131 CLOSE c_header;
132
133
134 -- Check whether the File is valid/Not. (ie whether any wrong file is used)
135 -- File can be Origination Response For Stafford/PLUS OR a Credit Response.
136 -- Also, Check if the file is an OUTPUT File.
137 igf_sl_gen.get_dl_batch_details(l_rec_message_class, l_rec_batch_type,
138 l_dl_version, l_dl_file_type, l_dl_loan_catg);
139
140
141 IF l_dl_file_type IN ('DL_DISB_ACK','DL_DISB_BOOK')
142 AND l_dl_loan_catg = 'DL' THEN
143 NULL;
144 ELSE
145 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
146 -- Message : This is not a valid file
147 igs_ge_msg_stack.add;
148 RAISE FILE_NOT_LOADED;
149 END IF;
150
151 IF l_dl_file_type ='DL_DISB_ACK' THEN
152 -- This is an Direct Loan Disbursment Acknowledgment File
153 fnd_message.set_name('IGF','IGF_DB_DL_ACK_FILE');
154 fnd_file.put_line(fnd_file.log,fnd_message.get);
155 ELSIF l_dl_file_type='DL_DISB_BOOK' THEN
156 fnd_message.set_name('IGF','IGF_DB_DL_BOOK_FILE');
157 fnd_file.put_line(fnd_file.log,fnd_message.get);
158
159 END IF;
160
161
162 -- Check whether the File was Fully transferred.
163 -- Get the record details in the File.
164 OPEN c_trailer;
165 FETCH c_trailer into l_last_lort_id, l_number_rec, l_accept_rec,
166 l_reject_rec, l_pending_rec;
167 IF c_trailer%NOTFOUND THEN
168 CLOSE c_trailer;
169 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
170 igs_ge_msg_stack.add;
171 RAISE FILE_NOT_LOADED;
172 END IF;
173 CLOSE c_trailer;
174
175
176 IF l_rec_batch_rej_code IS NOT NULL THEN
177 fnd_message.set_name('IGF','IGF_GE_BATCH_REJECTED');
178 fnd_message.set_token('BATCH', l_rec_batch_id);
179 fnd_message.set_token('REASON', igf_aw_gen.lookup_desc('IGF_SL_DL_BATCH_REJ',l_rec_batch_rej_code));
180 igs_ge_msg_stack.add;
181 RAISE FILE_NOT_LOADED;
182 -- Message : Batch #BATCH was rejected. Reason : #REASON.
183
184 END IF;
185
186
187 l_rowid := NULL;
188 igf_sl_dl_batch_pkg.insert_row (
189 x_mode => 'R',
190 x_rowid => l_rowid,
191 x_dbth_id => l_dbth_id,
192 x_batch_id => l_rec_batch_id,
193 x_message_class => l_rec_message_class,
194 x_bth_creation_date => TO_DATE(l_rec_bth_creation_date,'YYYYMMDDHH24MISS'),
195 x_batch_rej_code => l_rec_batch_rej_code,
196 x_end_date => NULL,
197 x_batch_type => l_rec_batch_type,
198 x_send_resp => 'R',
199 x_status => 'Y'
200 );
201
202
203 -- ************* Disbursement Transactions ********************
204
205 DECLARE
206 l_actual_rec NUMBER DEFAULT 0;
207 l_lor_resp_num NUMBER;
208 x_ddrp_Id NUMBER;
209 l_rowid ROWID;
210 CURSOR cur_disb
211 IS
212 SELECT record_data
213 FROM igf_sl_load_file_t
214 WHERE lort_id between 2 AND (l_last_lort_id-1)
215 AND file_type = 'DL_DISB';
216 BEGIN
217 --Added Version 2002-2003 as per Bug 2255281 DL VERSION TO BE CHECKED FOR DL CHANGE ORIG AND DISB ORIGINATION LOAN PROGRAMS.
218
219
220 IF l_dl_version IN ('2002-2003','2003-2004','2004-2005') THEN
221
222 -- File is Origination Response File For Stafford/PLUS.
223 IF l_dl_file_type IN ('DL_DISB_ACK','DL_DISB_BOOK') THEN
224 FOR lcur_disb IN cur_disb LOOP
225 l_actual_rec := l_actual_rec + 1;
226
227 igf_db_dl_disb_resp_pkg.insert_row (
228 x_mode => 'R',
229 x_rowid => l_rowid,
230 x_ddrp_id => x_ddrp_id,
231 x_dbth_id => l_dbth_id,
232 x_loan_number => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,1,21))),
233 x_disb_num => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,22,2)))),
234 x_disb_activity => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,24,1))),
235 x_transaction_date => TO_DATE(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,25,8))),'YYYYMMDD'),
236 x_disb_seq_num => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,33,2)))),
237 x_disb_gross_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,35,5)))),
238 x_fee_1 => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,40,5)))),
239 x_disb_net_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,45,5)))),
240 x_int_rebate_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,50,5)))),
241 x_user_ident => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,56,8))),
242 x_disb_batch_id => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,64,23))),
243 x_school_id => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,87,6))),
244 x_sch_code_status => NULL,
245 x_loan_num_status => NULL,
246 x_disb_num_status => NULL,
247 x_disb_activity_status => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,96,10))),
248 x_trans_date_status => NULL,
249 x_disb_seq_num_status => NULL,
250 x_loc_disb_gross_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,108,5)))),
251 x_loc_fee_1 => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,113,5)))),
252 x_loc_disb_net_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,118,5)))),
253 x_servicer_refund_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,123,6)))),
254 x_loc_int_rebate_amt => TO_NUMBER(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,130,5)))),
255 x_loc_net_booked_loan => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,139,5))),
256 x_ack_date => TO_DATE(LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,144,8))),'YYYYMMDD'),
257 x_affirm_flag => LTRIM(RTRIM(SUBSTR(lcur_disb.record_data,152,1))),
258 x_status => 'N'
259 );
260
261 END LOOP;
262 IF l_actual_rec <> l_number_rec THEN
263 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
264 -- Message : The Actual Number of records does not match with the one mentioned in the trailer
265 igs_ge_msg_stack.add;
266 RAISE FILE_NOT_LOADED;
267 END IF;
268 END IF;
269 END IF;
270
271 END;
272
273 p_dbth_id := l_dbth_id;
274 p_dl_version := l_dl_version;
275 p_dl_file_type := l_dl_file_type;
276 p_batch_id := l_rec_batch_id;
277
278 EXCEPTION
279 WHEN app_exception.record_lock_exception THEN
280 RAISE;
281 WHEN FILE_NOT_LOADED THEN
282 RAISE;
283 WHEN OTHERS THEN
284 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
285 fnd_message.set_token('NAME','IGF_DB_DL_ORIG_ACK.DISB_LOAD_DATA');
286 fnd_file.put_line(fnd_file.log,SQLERRM);
287 igs_ge_msg_stack.add;
288 app_exception.raise_exception;
289 END disb_load_data;
290
291
292 --
293 -- main procedure
294 --
295
296 PROCEDURE disb_ack(errbuf OUT NOCOPY VARCHAR2,
297 retcode OUT NOCOPY NUMBER,
298 p_org_id IN NUMBER )
299 AS
300 /*************************************************************
301 Created By : Prajeesh Chandran .K
302 Date Created On : 2000/12/20
303 Purpose :
304 Know limitations, enhancements or remarks
305 Change History
306 Who When What
307 (reverse chronological order - newest change first)
308 bvisvana 19-Jul-2005 Bug 4101317 - RAISE INVALID_PHASE_IN_PARTICIPANT in case on award year not a phase in participant
309 vvutukur 26-Feb-2003 Enh#2758823.FA117 Build. Replaced message IGS_GE_FILE_NOT_LOADED with IGF_GE_FILE_NOT_LOADED as the former one
310 is not a correct one.Removed validations regarding loan_num_status,disb_num_status,disb_seq_num_status,sch_code_status.
311 Removed if condition IF lcur_awdisb.booking_batch_id IS NOT NULL THEN.
312 ridas 31-Jan-2006 Bug #4951401. Added procedure igf_aw_gen.set_org_id()
313 ***************************************************************/
314
315 l_dbth_id igf_sl_dl_batch.dbth_id%TYPE; -- ## Variable for the batch ID
316 l_batch_type igf_sl_dl_batch.batch_type%TYPE;
317 l_stat VARCHAR2(30);
318 l_batch_id igf_sl_dl_batch.batch_id%TYPE;
319
320 l_dl_version igf_sl_dl_file_type.dl_version%TYPE;
321 l_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
322 l_disb_status igf_aw_db_chg_dtls.disb_status%TYPE;
323 l_disb_status_date igf_aw_db_chg_dtls.disb_status_date%TYPE;
324 l_disb_ack_date igf_db_dl_disb_resp.ack_date%TYPE DEFAULT NULL;
325
326 l_mesg_str1 VARCHAR2(4000); -- ## String to put in the log file
327 l_mesg_str2 VARCHAR2(4000); -- ## String to put in the log file
328 l_mesg_str3 VARCHAR2(4000); -- ## String to put in the log file
329
330 l_loan_number_desc igf_lookups_view.meaning%TYPE;
331 l_disb_num_desc igf_lookups_view.meaning%TYPE;
332 l_disb_seq_num_desc igf_lookups_view.meaning%TYPE;
333 l_disb_gross_amt_desc igf_lookups_view.meaning%TYPE;
334 l_disb_net_amt_desc igf_lookups_view.meaning%TYPE;
335 l_int_rebate_amt_desc igf_lookups_view.meaning%TYPE;
336 l_fee_1_desc igf_lookups_view.meaning%TYPE;
337 l_sch_code_status_desc igf_lookups_view.meaning%TYPE;
338 l_loan_num_status_desc igf_lookups_view.meaning%TYPE;
339 l_disb_num_status_desc igf_lookups_view.meaning%TYPE;
340 l_disb_seq_num_status_desc igf_lookups_view.meaning%TYPE;
341 l_disb_date_desc igf_lookups_view.meaning%TYPE;
342
343 -- ## Cursor to get the Disbursement Details to Process the acknowlegement Process
344
345 CURSOR cur_db_resp(l_dbth_id igf_sl_dl_batch.dbth_id%TYPE)
346 IS
347 SELECT *
348 FROM igf_db_dl_disb_resp
349 WHERE dbth_id = l_dbth_id
350 AND status = 'N';
351
352 -- ## Cursor to Get the award disbursements for the particular loannumber,award id ,disbursement Number and disbursement sequence number
353
354 CURSOR cur_awdisb(l_loan_number igf_db_cl_disb_resp.loan_number%TYPE,
355 l_disb_num igf_aw_db_chg_dtls.disb_num%TYPE,
356 l_disb_seq_num igf_aw_db_chg_dtls.disb_seq_num%TYPE)
357 IS
358 SELECT adcd.*
359 FROM igf_aw_db_chg_dtls adcd,
360 igf_sl_loans sl
361 WHERE
362 sl.loan_number = l_loan_number AND
363 adcd.award_id = sl.award_id AND
364 adcd.disb_num = l_disb_num AND
365 adcd.disb_seq_num = l_disb_seq_num;
366
367 lcur_awdisb cur_awdisb%ROWTYPE;
368
369 l_log_start_flag BOOLEAN;
370
371 CURSOR award_year_cur ( cp_dl_version IGF_SL_DL_SETUP.dl_version%TYPE) IS
372 SELECT ci_cal_type, ci_sequence_number
373 FROM igf_sl_dl_setup
374 WHERE dl_version = cp_dl_version;
375 award_year_rec award_year_cur%ROWTYPE;
376 l_cod_year_flag BOOLEAN;
377
378 -- Used to print the disbursement details in the log file
379 TYPE disb_dtl_rec IS RECORD
380 (
381 p_disb_num NUMBER(15),
382 p_disb_seq_num NUMBER(15),
383 p_disb_date DATE,
384 p_disb_amount NUMBER(10,3),
385 p_rec_type VARCHAR2(1)
386 );
387
388 TYPE disb_dtl_tab IS TABLE OF disb_dtl_rec INDEX BY BINARY_INTEGER;
389 l_disb_dtl_tab disb_dtl_tab;
390 l_disb_count NUMBER(15);
391
392
393 -- ## Local Procedure to print the Loan Number, Disb Number and Disb Seq Num in the LOG File
394
395 PROCEDURE log_start(p_loan_number igf_db_dl_disb_resp.loan_number%TYPE,
396 p_disb_num igf_db_dl_disb_resp.disb_num%TYPE,
397 p_disb_seq_num igf_db_dl_disb_resp.disb_seq_num%TYPE
398 )
399 AS
400 BEGIN
401
402 IF l_log_start_flag = FALSE THEN
403
404 fnd_file.put_line(fnd_file.log,RPAD(l_loan_number_desc ,50)||' : '||p_loan_number);
405 fnd_file.put_line(fnd_file.log,RPAD(l_disb_num_desc ,50)||' : '||TO_CHAR(p_disb_num) );
406 fnd_file.put_line(fnd_file.log,RPAD(l_disb_seq_num_desc,50)||' : '||TO_CHAR(p_disb_seq_num));
407
408 l_log_start_flag := TRUE;
409 END IF;
410
411 END;
412
413 BEGIN
414
415 igf_aw_gen.set_org_id(p_org_id);
416
417 -- Load the Data into the Batch and Response Tables
418 disb_load_data(l_dbth_id, l_batch_id,l_dl_version, l_dl_file_type);
419
420 -- Get the System Award Year from l_dl_version
421 OPEN award_year_cur (l_dl_version);
422 FETCH award_year_cur INTO award_year_rec;
423 CLOSE award_year_cur;
424
425 -- Check wether the awarding year is COD-XML processing Year or not
426 l_cod_year_flag := NULL;
427 l_cod_year_flag := igf_sl_dl_validation.check_full_participant (award_year_rec.ci_cal_type,award_year_rec.ci_sequence_number,'DL');
428
429 -- If the award year is FULL_PARTICIPANT then raise the error message
430 -- and stop processing else continue the process
431 IF l_cod_year_flag THEN
432 fnd_message.set_name('IGF','IGF_SL_COD_NO_DISB_ACK');
433 igs_ge_msg_stack.add;
434 RAISE INVALID_PHASE_IN_PARTICIPANT;
435 END IF;
436
437 -- ## Getting the Loopkup Descriptions.
438
439 l_loan_number_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER');
440 l_disb_num_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_NUM');
441 l_disb_seq_num_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_SEQ_NUM');
442 l_disb_gross_amt_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_GROSS_AMT');
443 l_fee_1_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','FEE_1');
444 l_disb_net_amt_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_NET_AMT');
445 l_int_rebate_amt_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','INT_REBATE_AMT');
446 l_disb_date_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_DATE');
447
448 FOR lcur_db_resp IN cur_db_resp(l_dbth_id)
449 LOOP -- ## Open the Disb LOOP
450
451 BEGIN
452
453 l_mesg_str1 := NULL;
454 l_mesg_str2 := NULL;
455 l_mesg_str3 := NULL;
456 l_disb_ack_date := NULL;
457 l_log_start_flag := FALSE;
458
459 -- If the disbursement sequence number is between 60 and 100 then
460 -- Store the values in a temparary PL/SQL table to print at the end
461 IF (lcur_db_resp.disb_seq_num > 60 AND lcur_db_resp.disb_seq_num < 100) THEN
462
463 l_disb_dtl_tab(1).p_disb_num := lcur_db_resp.disb_num;
464 l_disb_dtl_tab(1).p_disb_seq_num := lcur_db_resp.disb_seq_num;
465 l_disb_dtl_tab(1).p_disb_date := lcur_db_resp.transaction_date;
466 l_disb_dtl_tab(1).p_disb_amount := lcur_db_resp.disb_gross_amt;
467
468 -- If disb_seq_num > 60 and < 91 then print under COD generated disbursements received section
469 IF (lcur_db_resp.disb_seq_num > 60 AND lcur_db_resp.disb_seq_num < 91) THEN
470 l_disb_dtl_tab(1).p_rec_type := 'C';
471 -- If disb_Seq_num > 90 and < 100 then print under Payment to Servicing Response Received section
472 ELSIF (lcur_db_resp.disb_seq_num > 90 AND lcur_db_resp.disb_seq_num < 100) THEN
473 l_disb_dtl_tab(1).p_rec_type := 'P';
474 END IF;
475
476 END IF;
477
478 OPEN cur_awdisb(lcur_db_resp.loan_number,lcur_db_resp.disb_num,lcur_db_resp.disb_seq_num); -- ## Open the award Disb Loop
479 FETCH cur_awdisb INTO lcur_awdisb;
480 IF cur_awdisb%NOTFOUND THEN
481 CLOSE cur_awdisb; -- Bug 4101482 Cursor not closed
482 fnd_message.set_name('IGF','IGF_DB_NO_AWARD_DTL');
483 fnd_message.set_token('AWARD',lcur_db_resp.loan_number);
484 fnd_message.set_token('DNUM',lcur_db_resp.disb_num);
485 fnd_message.set_token('DSEQNUM',lcur_db_resp.disb_seq_num);
486 fnd_file.put_line(fnd_file.log,fnd_message.get);
487 RAISE SKIP_THIS_RECORD;
488
489 END IF;
490
491 -- ## Check if file type is Acknolwedgement
492
493 IF l_dl_file_type = 'DL_DISB_ACK' THEN
494
495
496 -- ## Check If already acknowledged
497 IF lcur_awdisb.ack_date IS NULL THEN
498
499
500 IF lcur_db_resp.disb_activity_status IS NOT NULL THEN
501
502 log_start(lcur_db_resp.loan_number, lcur_db_resp.disb_num, lcur_db_resp.disb_seq_num);
503
504 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_DB_DL_REJ_CODES',
505 LTRIM(RTRIM(SUBSTR(lcur_db_resp.disb_activity_status,1,2)))
506 )
507 );
508 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_DB_DL_REJ_CODES',
509 LTRIM(RTRIM(SUBSTR(lcur_db_resp.disb_activity_status,3,2)))
510 )
511 );
512 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_DB_DL_REJ_CODES',
513 LTRIM(RTRIM(SUBSTR(lcur_db_resp.disb_activity_status,5,2)))
514 )
515 );
516 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_DB_DL_REJ_CODES',
517 LTRIM(RTRIM(SUBSTR(lcur_db_resp.disb_activity_status,7,2)))
518 )
519 );
520 END IF;
521
522
523 IF lcur_db_resp.disb_activity_status IS NULL THEN
524 l_disb_status := 'A';
525 l_disb_status_date := TRUNC(SYSDATE);
526 l_disb_ack_date := lcur_db_resp.ack_date;
527 ELSE
528 l_disb_status := 'R';
529 l_disb_status_date := TRUNC(SYSDATE);
530 END IF;
531
532
533 -- ## Update the igf_aw_db_chg_dtls table with the disb status as Rejected
534 -- ## and disb status date as current data
535
536 IF lcur_db_resp.loc_disb_gross_amt IS NOT NULL
537 OR lcur_db_resp.loc_fee_1 IS NOT NULL
538 OR lcur_db_resp.loc_disb_net_amt IS NOT NULL THEN
539
540 --
541 -- ## Compare If there is any difference exists between amt and fee fields
542 -- ## in the tables like igf_aw_db_chg_dtls and igf_db_dl_disb_resp.If any
543 -- ## difference exists then log it in the file.
544 -- ## Below, NVL(,lcur_awdisb.field) is given so that, if the field in flat file
545 -- ## is NULL, then it means that it matches with the values sent by the school.
546 --
547
548 IF NVL(lcur_db_resp.loc_disb_gross_amt,lcur_awdisb.disb_accepted_amt) <> NVL(lcur_awdisb.disb_accepted_amt,0)
549 OR NVL(lcur_db_resp.loc_fee_1,lcur_awdisb.orig_fee_amt) <> NVL(lcur_awdisb.orig_fee_amt,0)
550 OR NVL(lcur_db_resp.loc_disb_net_amt,lcur_awdisb.disb_net_amt) <> NVL(lcur_awdisb.disb_net_amt,0) THEN
551
552
553 l_mesg_str1 := RPAD(' ',35);
554 l_mesg_str2 := RPAD(igf_aw_gen.lookup_desc('IGF_SL_GEN','LOC_DISB_DETAILS'),35);
555 l_mesg_str3 := RPAD(igf_aw_gen.lookup_desc('IGF_SL_GEN','OFA_DISB_DETAILS'),35);
556
557 l_mesg_str1 := l_mesg_str1 || LPAD(l_disb_gross_amt_desc,50);
558 l_mesg_str2 := l_mesg_str2 || LPAD(NVL(lcur_db_resp.loc_disb_gross_amt,''),50);
559 l_mesg_str3 := l_mesg_str3 || LPAD(lcur_awdisb.disb_accepted_amt,50);
560
561 l_mesg_str1 := l_mesg_str1 || LPAD(l_fee_1_desc,50);
562 l_mesg_str2 := l_mesg_str2 || LPAD(NVL(lcur_db_resp.loc_fee_1,''),50);
563 l_mesg_str3 := l_mesg_str3 || LPAD(lcur_awdisb.orig_fee_amt,50);
564
565 l_mesg_str1 := l_mesg_str1 || LPAD(l_disb_net_amt_desc,50);
566 l_mesg_str2 := l_mesg_str2 || LPAD(NVL(lcur_db_resp.loc_disb_net_amt,''),50);
567 l_mesg_str3 := l_mesg_str3 || LPAD(lcur_awdisb.disb_net_amt,50);
568
569 fnd_file.new_line(fnd_file.log,1);
570 fnd_file.put_line(fnd_file.log,l_mesg_str1);
571 fnd_file.put_line(fnd_file.log,l_mesg_str2);
572 fnd_file.put_line(fnd_file.log,l_mesg_str3);
573
574 END IF;
575
576 END IF;
577
578 DECLARE
579
580 CURSOR c_tbh_cur IS
581 SELECT adcd.*,adcd.ROWID
582 FROM igf_aw_db_chg_dtls adcd
583 WHERE adcd.award_id = lcur_awdisb.award_id
584 AND adcd.disb_num = lcur_db_resp.disb_num
585 AND adcd.disb_seq_num = lcur_db_resp.disb_seq_num
586 FOR UPDATE OF adcd.award_id NOWAIT;
587
588 BEGIN
589 FOR tbh_rec in c_tbh_cur LOOP
590
591 igf_aw_db_chg_dtls_pkg.update_row (
592 x_rowid => tbh_rec.ROWID,
593 x_award_id => tbh_rec.award_id,
594 x_disb_num => tbh_rec.disb_num,
595 x_disb_seq_num => tbh_rec.disb_seq_num,
596 x_disb_accepted_amt => tbh_rec.disb_accepted_amt,
597 x_orig_fee_amt => tbh_rec.orig_fee_amt,
598 x_disb_net_amt => tbh_rec.disb_net_amt,
599 x_disb_date => tbh_rec.disb_date,
600 x_disb_activity => tbh_rec.disb_activity,
601 x_disb_status => l_disb_status,
602 x_disb_status_date => l_disb_status_date,
603 x_disb_rel_flag => tbh_rec.disb_rel_flag,
604 x_first_disb_flag => tbh_rec.first_disb_flag,
605 x_interest_rebate_amt => tbh_rec.interest_rebate_amt,
606 x_disb_conf_flag => tbh_rec.disb_conf_flag,
607 x_pymnt_prd_start_date => tbh_rec.pymnt_prd_start_date,
608 x_note_message => tbh_rec.note_message,
609 x_batch_id_txt => tbh_rec.batch_id_txt,
610 x_ack_date => l_disb_ack_date,
611 x_booking_id_txt => tbh_rec.booking_id_txt,
612 x_booking_date => tbh_rec.booking_date,
613 x_mode => 'R');
614
615 END LOOP;
616 END;
617 END IF;
618
619
620 -- ## If the file type is Booking then pick up the data from the igf_aw_db_chg_dtls
621 -- ## and update the igf_aw_db_chg_dtls with the disb status as booked, booking batch id
622 -- ## with the current batch ID and disb status date as current date
623
624 ELSIF l_dl_file_type = 'DL_DISB_BOOK' THEN
625 DECLARE
626
627 CURSOR c_tbh_cur IS
628 SELECT adcd.*, adcd.ROWID
629 FROM igf_aw_db_chg_dtls adcd
630 WHERE adcd.award_id = lcur_awdisb.award_id
631 AND adcd.disb_num = lcur_db_resp.disb_num
632 AND adcd.disb_seq_num = lcur_db_resp.disb_seq_num
633 FOR UPDATE OF adcd.award_id NOWAIT;
634
635 BEGIN
636
637 FOR tbh_recss in c_tbh_cur LOOP
638
639 igf_aw_db_chg_dtls_pkg.update_row (
640 x_rowid => tbh_recss.ROWID,
641 x_award_id => tbh_recss.award_id,
642 x_disb_num => tbh_recss.disb_num,
643 x_disb_seq_num => tbh_recss.disb_seq_num,
644 x_disb_accepted_amt => tbh_recss.disb_accepted_amt,
645 x_orig_fee_amt => tbh_recss.orig_fee_amt,
646 x_disb_net_amt => tbh_recss.disb_net_amt,
647 x_disb_date => tbh_recss.disb_date,
648 x_disb_activity => tbh_recss.disb_activity,
649 x_disb_status => 'B',
650 x_disb_status_date => TRUNC(SYSDATE),
651 x_disb_rel_flag => tbh_recss.disb_rel_flag,
652 x_first_disb_flag => tbh_recss.first_disb_flag,
653 x_interest_rebate_amt => tbh_recss.interest_rebate_amt,
654 x_disb_conf_flag => tbh_recss.disb_conf_flag,
655 x_pymnt_prd_start_date => tbh_recss.pymnt_prd_start_date,
656 x_note_message => tbh_recss.note_message,
657 x_batch_id_txt => tbh_recss.batch_id_txt,
658 x_ack_date => l_disb_ack_date,
659 x_booking_id_txt => l_batch_id,
660 x_booking_date => lcur_db_resp.transaction_date,
661 x_mode => 'R');
662
663 END LOOP;
664 END;
665 END IF;
666
667
668 -- ## Update the igf_db_dl_disb_resp table with the status as Y(Processed);
669
670
671 igf_db_dl_disb_resp_pkg.update_row (
672 x_mode => 'R',
673 x_rowid => lcur_db_resp.row_id,
674 x_ddrp_id => lcur_db_resp.ddrp_id,
675 x_dbth_id => lcur_db_resp.dbth_id,
676 x_loan_number => lcur_db_resp.loan_number,
677 x_disb_num => lcur_db_resp.disb_num,
678 x_disb_activity => lcur_db_resp.disb_activity,
679 x_transaction_date => lcur_db_resp.transaction_date,
680 x_disb_seq_num => lcur_db_resp.disb_seq_num,
681 x_disb_gross_amt => lcur_db_resp.disb_gross_amt,
682 x_fee_1 => lcur_db_resp.fee_1,
683 x_disb_net_amt => lcur_db_resp.disb_net_amt,
684 x_int_rebate_amt => lcur_db_resp.int_rebate_amt,
685 x_user_ident => lcur_db_resp.user_ident,
686 x_disb_batch_id => lcur_db_resp.disb_batch_id,
687 x_school_id => lcur_db_resp.school_id,
688 x_sch_code_status => lcur_db_resp.sch_code_status,
689 x_loan_num_status => lcur_db_resp.loan_num_status,
690 x_disb_num_status => lcur_db_resp.disb_num_status,
691 x_disb_activity_status => lcur_db_resp.disb_activity_status,
692 x_trans_date_status => lcur_db_resp.trans_date_status,
693 x_disb_seq_num_status => lcur_db_resp.disb_seq_num_status,
694 x_loc_disb_gross_amt => lcur_db_resp.loc_disb_gross_amt,
695 x_loc_fee_1 => lcur_db_resp.loc_fee_1,
696 x_loc_disb_net_amt => lcur_db_resp.loc_disb_net_amt,
697 x_servicer_refund_amt => lcur_db_resp.servicer_refund_amt,
698 x_loc_int_rebate_amt => lcur_db_resp.loc_int_rebate_amt,
699 x_loc_net_booked_loan => lcur_db_resp.loc_net_booked_loan,
700 x_ack_date => lcur_db_resp.ack_date,
701 x_affirm_flag => lcur_db_resp.affirm_flag,
702 x_status =>'Y' );
703 CLOSE cur_awdisb;
704
705 EXCEPTION
706 WHEN SKIP_THIS_RECORD THEN
707 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
708 fnd_file.put_line(fnd_file.log,fnd_message.get);
709 fnd_file.new_line(fnd_file.log,1);
710 END;
711
712 END LOOP; -- ## End Loop of Disb Loop
713
714 COMMIT;
715
716 -- Print the disbursment details of 'COD generated disbursements received' in the log file
717 fnd_message.set_name('IGF','IGF_SL_DL_DB_COD_DISB');
718 fnd_file.put_line(fnd_file.log,fnd_message.get);
719 FOR l_disb_count IN 1 .. l_disb_dtl_tab.COUNT LOOP
720
721 IF l_disb_dtl_tab(l_disb_count).p_rec_type = 'C' THEN
722 fnd_file.put_line(fnd_file.log,RPAD(l_disb_num_desc ,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_num));
723 fnd_file.put_line(fnd_file.log,RPAD(l_disb_seq_num_desc ,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_seq_num));
724 fnd_file.put_line(fnd_file.log,RPAD(l_disb_date_desc ,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_date));
725 fnd_file.put_line(fnd_file.log,RPAD(l_disb_gross_amt_desc,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_amount));
726 END IF;
727
728 END LOOP;
729
730 -- Print the disbursment details of 'Payment to Servicing Response Received' in the log file
731 fnd_message.set_name('IGF','IGF_SL_DL_DB_PYMN_SRVC');
732 fnd_file.put_line(fnd_file.log,fnd_message.get);
733 FOR l_disb_count IN 1 .. l_disb_dtl_tab.COUNT LOOP
734
735 IF l_disb_dtl_tab(l_disb_count).p_rec_type = 'P' THEN
736 fnd_file.put_line(fnd_file.log,RPAD(l_disb_num_desc ,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_num));
737 fnd_file.put_line(fnd_file.log,RPAD(l_disb_seq_num_desc ,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_seq_num));
738 fnd_file.put_line(fnd_file.log,RPAD(l_disb_date_desc ,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_date));
739 fnd_file.put_line(fnd_file.log,RPAD(l_disb_gross_amt_desc,50)||' : '||TO_CHAR(l_disb_dtl_tab(l_disb_count).p_disb_amount));
740 END IF;
741
742 END LOOP;
743
744 EXCEPTION
745
746 WHEN app_exception.record_lock_exception THEN
747
748 ROLLBACK;
749 retcode := 2;
750 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
751 igs_ge_msg_stack.conc_exception_hndl;
752
753 WHEN FILE_NOT_LOADED THEN
754
755 ROLLBACK;
756 retcode := 2;
757 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
758 igs_ge_msg_stack.conc_exception_hndl;
759
760 WHEN INVALID_PHASE_IN_PARTICIPANT THEN
761
762 ROLLBACK;
763 retcode := 2;
764 errbuf := fnd_message.get_string('IGF','IGF_SL_COD_NO_DISB_ACK');
765 igs_ge_msg_stack.conc_exception_hndl;
766
767 WHEN OTHERS THEN
768
769 IF cur_awdisb%ISOPEN THEN
770 CLOSE cur_awdisb;
771 END IF;
772 ROLLBACK;
773
774 retcode := 2;
775 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
776 fnd_file.put_line(fnd_file.log,SQLERRM);
777 igs_ge_msg_stack.conc_exception_hndl;
778
779 END disb_ack;
780
781 END igf_db_dl_orig_ack;