[Home] [Help]
PACKAGE BODY: APPS.IGF_DB_CL_ROSTER
Source
1 PACKAGE BODY igf_db_cl_roster AS
2 /* $Header: IGFDB04B.pls 120.5 2006/08/08 06:29:38 ridas noship $ */
3
4 -----------------------------------------------------------------------------------
5 -- Created By : sjadhav
6 -- Date Created On : 2000/12/18
7 -- Purpose :
8 -- Know limitations, enhancements or remarks
9 -- Change History
10 ----------------------------------------------------------------------------------------
11 -- svuppala 27-OCT-04 FA 134 CommonLine4 Change Origination
12 -- # 3416936 Modifications to disbursement roster process
13 ----------------------------------------------------------------------------------------
14 --ayedubat 14-OCT-04 FA 149 COD-XML Standards build bug # 3416863
15 -- Changed the TBH call of the package: IGF_AW_AWD_DISB_PKG
16 -----------------------------------------------------------------------------------
17 -- veramach July 2004 FA 151 HR integration (bug # 3709292)
18 -- Impact of obsoleting columns from igf_aw_awd_disb_pkg
19 -----------------------------------------------------------------------------------
20 -- Who When What
21 -- veramach 3-NOV-2003 FA 125 Multiple Distr Methods
22 -- Changed the call of igf_aw_awd_disb_pkg.update_row to reflect the addition of attendance_type_code
23 -----------------------------------------------------------------------------------
24 -- sjadhav 26-Mar-2003 Bug 2863960
25 -- Changed Disb Gross Amt to Disb Accepted Amt
26 -- As net amount is based on accepted amount
27 -- Cursor to read CL Setup Data modified to read
28 -- records based on award year
29 -- Corrected typos in messages and lookup codes
30 -----------------------------------------------------------------------------------
31 -- mesriniv 13-07-2001 W.r.to Awards Build,9 new columns have been added
32 -- in IGF_AW_AWD_DISB_ALL table.The call to
33 -- igf_aw_awd_disb_pkg.update_row has been modified
34 -- to reflect the changes.
35 -----------------------------------------------------------------------------------
36 -- ssawhney 2nd Jan Stud Emp build IGF_AW_AWD_DISB TBH call changed.
37 -----------------------------------------------------------------------------------
38
39 FILE_NOT_LOADED EXCEPTION;
40 CLSETUP_NOT_FOUND EXCEPTION;
41 SKIP_THIS_RECORD EXCEPTION;
42
43 g_cl_version igf_sl_cl_file_type.cl_version%TYPE;
44 g_cl_file_type igf_sl_cl_file_type.cl_file_type%TYPE;
45 --These are description for various fields which will go in log file
46
47 loan_number_desc VARCHAR2(100);
48 disb_num_desc VARCHAR2(100);
49 loc_disb_desc VARCHAR2(100);
50 ofa_disb_desc VARCHAR2(100);
51 disb_gross_amt_desc VARCHAR2(100);
52 fee_1_desc VARCHAR2(100);
53 fee_2_desc VARCHAR2(100);
54 disb_net_amt_desc VARCHAR2(100);
55 fee_paid_1_desc VARCHAR2(100);
56 fee_paid_2_desc VARCHAR2(100);
57 direct_to_borr_ind_desc VARCHAR2(100);
58
59 -- Above are description for various fields which will go in log file
60
61
62
63 -- Procedure to Load the Data from the Disbursement Roster File into the ,
64 -- and IGF_DB_CL_DISB_RESP tables.
65 -- Before loading, it does lot of checks to ensure it is the right file
66
67
68 PROCEDURE cl_load_data(p_cbth_id OUT NOCOPY igf_sl_cl_batch_all.cbth_id%TYPE)
69 IS
70
71
72 /*************************************************************
73 Created By : sjadhav
74 Date Created On : 2000/12/18
75 Purpose : To load data into Batch and Response table
76 after validating for the correctness of the data
77
78 Know limitations, enhancements or remarks
79 Change History
80 Who When What
81 ridas 07-Aug-2006 Build FA163. Changes made to include the direct disbursement
82 to borrower indicator and split of guarantee and origination fee paid.
83 svuppala 27-Oct-2004 Added new fields as per FA 134
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86
87 l_batch_id igf_sl_cl_batch_all.batch_id%TYPE;
88 l_file_creation_dt igf_sl_cl_batch_all.file_creation_date%TYPE;
89 l_file_trans_dt igf_sl_cl_batch_all.file_trans_date%TYPE;
90 l_file_ident_name VARCHAR2(100);
91 l_file_ident_code igf_sl_cl_batch_all.file_ident_code%TYPE;
92 l_source_id igf_sl_cl_batch_all.source_id%TYPE;
93 l_recipient_id igf_sl_cl_batch_all.recipient_id%TYPE;
94 l_recip_non_ed_brc_id igf_sl_cl_batch_all.recip_non_ed_brc_id%TYPE;
95 l_source_non_ed_brc_id igf_sl_cl_batch_all.source_non_ed_brc_id%TYPE;
96 l_rowid ROWID;
97 l_cbth_id igf_sl_cl_batch_all.cbth_id%TYPE;
98 l_number_rec NUMBER;
99 l_last_lort_id NUMBER;
100 l_tot_net_disb_amt NUMBER;
101 l_tot_net_eft_amt NUMBER;
102 l_tot_net_non_eft_amt NUMBER;
103 l_tot_reissue_amt NUMBER;
104 l_tot_cancel_amt NUMBER;
105 l_tot_deficit_amt NUMBER;
106 l_tot_net_cancel_amt NUMBER;
107 l_tot_net_out_cancel_amt NUMBER;
108 l_file_creation_time DATE;
109 l_source_name VARCHAR2(80);
110 l_recipient_name VARCHAR2(80);
111 l_temp VARCHAR2(10);
112 lv_header VARCHAR2(80);
113 lv_source_id VARCHAR2(80);
114 lv_source_name VARCHAR2(80);
115 lv_recipient_id VARCHAR2(80);
116 lv_recipient_name VARCHAR2(80);
117 lv_file_creation_date VARCHAR2(80);
118 lv_file_creation_time VARCHAR2(80);
119 lv_trailer VARCHAR2(80);
120 lv_number_rec VARCHAR2(80);
121 lv_tot_net_disb_amt VARCHAR2(80);
122 lv_tot_net_eft_amt VARCHAR2(80);
123 lv_tot_net_non_eft_amt VARCHAR2(80);
124 lv_tot_reissue_amt VARCHAR2(80);
125 lv_tot_cancel_amt VARCHAR2(80);
126 lv_tot_deficit_amt VARCHAR2(80);
127 lv_tot_net_cancel_amt VARCHAR2(80);
128 lv_tot_net_out_cancel_amt VARCHAR2(80);
129 l_actual_rec NUMBER DEFAULT 0;
130 l_cdbr_id igf_db_cl_disb_resp_all.cdbr_id%TYPE;
131 l_hold_rel_ind VARCHAR2(30);
132 l_pnote_code VARCHAR2(2);
133 l_pnote_status_date DATE;
134 l_fee_paid_1 NUMBER;
135 l_netted_cancel_amt NUMBER;
136 l_outstd_cancel_amt NUMBER;
137 l_sch_non_ed_brc_id VARCHAR2(30);
138 l_record_type VARCHAR2(30);
139 l_loan_number VARCHAR2(30);
140 l_cl_seq_number NUMBER;
141 l_loan_per_start_date DATE;
142 l_loan_per_end_date DATE;
143 l_lender_id VARCHAR2(30);
144 l_lend_non_ed_brc_id VARCHAR2(30);
145 l_tot_sched_disb NUMBER;
146 l_disb_num NUMBER;
147 l_guarantor_id VARCHAR2(30);
148 l_guarantee_date DATE;
149 l_guarantee_amt NUMBER;
150 l_fund_release_date DATE;
151 l_gross_disb_amt NUMBER;
152 l_fee_1 NUMBER;
153 l_fee_2 NUMBER;
154 l_net_disb_amt NUMBER;
155 l_fund_dist_mthd VARCHAR2(30);
156 l_check_number VARCHAR2(30);
157 l_late_disb_ind VARCHAR2(30);
158 l_prev_reported_ind VARCHAR2(30);
159 l_net_cancel_amt NUMBER;
160 l_fee_paid_2 NUMBER;
161 lv_disb VARCHAR2(80);
162 lv_record_type VARCHAR2(80);
163 lv_loan_sequence_number VARCHAR2(80);
164 lv_loan_number VARCHAR2(80);
165 lv_loan_period_end_date VARCHAR2(80);
166 lv_loan_period_start_date VARCHAR2(80);
167 lv_lender_id VARCHAR2(80);
168 lv_lender_non_ed_branch_id VARCHAR2(80);
169 lv_total_schd_disb VARCHAR2(80);
170 lv_disbursement_number VARCHAR2(80);
171 lv_guarantor_id VARCHAR2(80);
172 lv_guarantee_date VARCHAR2(80);
173 lv_guarantee_amount VARCHAR2(80);
174 lv_fund_release_date VARCHAR2(80);
175 lv_gross_disbursement_amount VARCHAR2(80);
176 lv_guarantee_fees VARCHAR2(80);
177 lv_origination_fees_paid VARCHAR2(80);
178 lv_origination_fees VARCHAR2(80);
179 lv_guarantee_fees_paid VARCHAR2(80);
180 lv_net_disbursement_amount VARCHAR2(80);
181 lv_fees_paid VARCHAR2(80);
182 lv_fund_distribution_method VARCHAR2(80);
183 lv_check_number VARCHAR2(80);
184 lv_late_disbursement VARCHAR2(80);
185 lv_previously_reported VARCHAR2(80);
186 lv_net_cancellation_amount VARCHAR2(80);
187 lv_netted_cancel_amount VARCHAR2(80);
188 lv_outstanding_can_amt VARCHAR2(80);
189 lv_esign_src_typ_cd VARCHAR2(80);
190 lv_direct_to_borr_ind_mng VARCHAR2(80);
191 l_direct_to_borr_ind VARCHAR2(1);
192
193
194
195 CURSOR c_header
196 IS
197 SELECT LTRIM(RTRIM(SUBSTR(record_data, 3, 12))) batch_id,
198 TO_DATE(TRIM(SUBSTR(record_data, 15, 8)), 'YYYYMMDDHH24MISS') file_creation_dt,
199 TO_DATE(TRIM(SUBSTR(record_data, 23, 6)), 'HH24MISS') file_creation_time,
200 TO_DATE(TRIM(SUBSTR(record_data, 29, 8)), 'YYYYMMDDHH24MISS') file_trans_dt,
201 LTRIM(RTRIM(SUBSTR(record_data, 43, 19))) file_ident_name,
202 LTRIM(RTRIM(SUBSTR(record_data, 62, 5))) file_ident_code,
203 LTRIM(RTRIM(SUBSTR(record_data, 67, 32))) source_name,
204 LTRIM(RTRIM(SUBSTR(record_data, 99, 8))) source_id,
205 LTRIM(RTRIM(SUBSTR(record_data, 109, 4))) source_non_ed_brc_id,
206 LTRIM(RTRIM(SUBSTR(record_data, 114, 32))) recipient_name,
207 LTRIM(RTRIM(SUBSTR(record_data, 146, 8))) recipient_id,
208 LTRIM(RTRIM(SUBSTR(record_data, 156, 4))) recip_non_ed_brc_id
209 FROM igf_sl_load_file_t
210 WHERE lort_id = 1 AND record_data LIKE '@H%' AND file_type =
211 'CL_ROSTER';
212
213 CURSOR c_trailer
214 IS
215 SELECT lort_id last_lort_id,
216 TO_NUMBER(TRIM(SUBSTR(record_data, 3, 6))) rec_count,
217 TO_NUMBER(TRIM(SUBSTR(record_data, 9, 14)))/100 tot_net_disb_amt,
218 TO_NUMBER(TRIM(SUBSTR(record_data, 23, 14)))/100 tot_net_eft_amt,
219 TO_NUMBER(TRIM(SUBSTR(record_data, 37, 14)))/100 tot_net_non_eft_amt,
220 TO_NUMBER(TRIM(SUBSTR(record_data, 51, 14)))/100 tot_reissue_amt,
221 TO_NUMBER(TRIM(SUBSTR(record_data, 105, 14)))/100 tot_cancel_amt,
222 TO_NUMBER(TRIM(SUBSTR(record_data, 119, 14)))/100 tot_deficit_amt,
223 TO_NUMBER(TRIM(SUBSTR(record_data, 142, 14)))/100 tot_net_cancel_amt,
224 TO_NUMBER(TRIM(SUBSTR(record_data, 156, 14)))/100 tot_net_out_cancel_amt
225 FROM igf_sl_load_file_t
226 WHERE lort_id = (SELECT MAX(lort_id)
227 FROM igf_sl_load_file_t)
228 AND record_data LIKE '@T%' AND file_type = 'CL_ROSTER';
229
230 CURSOR c_get_header_parameters
231 IS
232 SELECT meaning, lookup_code
233 FROM igf_lookups_view
234 WHERE lookup_type = 'IGF_SL_CL_ROSTER_LOGS';
235
236 CURSOR c_get_trailer_parameters
237 IS
238 SELECT meaning, lookup_code
239 FROM igf_lookups_view
240 WHERE lookup_type = 'IGF_SL_CL_ROSTER_LOGS';
241
242
243 CURSOR c_dbcl
244 IS
245 SELECT record_data
246 FROM igf_sl_load_file_t
247 WHERE lort_id BETWEEN 2 AND (l_last_lort_id - 1)
248 AND file_type = 'CL_ROSTER';
249
250
251 CURSOR cur_roster_logs
252 IS
253 SELECT meaning, lookup_code
254 FROM igf_lookups_view
255 WHERE lookup_type = 'IGF_SL_CL_ROSTER_LOGS';
256
257 header_parameter_rec c_get_header_parameters%ROWTYPE;
258 trailer_parameter_rec c_get_trailer_parameters%ROWTYPE;
259 roster_logs_rec cur_roster_logs%ROWTYPE;
260
261 CURSOR cur_lor_data (cp_loan_number igf_sl_loans_all.loan_number%TYPE) IS
262 SELECT lor.ROWID row_id, lor.*
263 FROM IGF_SL_LOR_ALL lor,
264 IGF_SL_LOANS_ALL loans
265 WHERE loans.loan_id = lor.loan_id
266 AND loans.loan_number = cp_loan_number;
267
268 rec_lor_data cur_lor_data%ROWTYPE;
269
270 lv_esign_roster_data igf_sl_lor_all.esign_src_typ_cd%TYPE;
271
272 BEGIN
273
274 -- Check for a proper header
275
276 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
277 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','cl_load_data Entry ');
278 END IF;
279
280 OPEN c_header;
281 FETCH c_header INTO
282 l_batch_id,
283 l_file_creation_dt,
284 l_file_creation_time,
285 l_file_trans_dt,
286 l_file_ident_name,
287 l_file_ident_code,
288 l_source_name,
289 l_source_id,
290 l_source_non_ed_brc_id,
291 l_recipient_name,
292 l_recipient_id,
293 l_recip_non_ed_brc_id;
294
295 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
296 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','cl_load_data Header fetch');
297 END IF;
298 IF c_header%NOTFOUND THEN
299 CLOSE c_header;
300 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
301 -- File uploaded is incomplete.
302 igs_ge_msg_stack.add;
303 RAISE FILE_NOT_LOADED;
304 END IF;
305 CLOSE c_header;
306
307 -- Check for a valid Disbursement Roster File
308
309 igf_sl_gen.get_cl_batch_details(
310 LTRIM(RTRIM(l_file_ident_code)), -- File_Ident_Code
311 LTRIM(RTRIM(l_file_ident_name)), -- File_Ident_Name
312 g_cl_version, g_cl_file_type);
313
314 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
315 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','Got Batch g_cl_version,g_cl_file_type,l_file_ident_name ,l_file_ident_code '
316 || g_cl_version ||' : ' || g_cl_file_type||' : ' || l_file_ident_name||' : ' || l_file_ident_code);
317 END IF;
318
319 IF g_cl_file_type = 'CL_DISB_ROSTER' THEN
320 NULL;
321 ELSE
322 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
323 igs_ge_msg_stack.add;
324 RAISE FILE_NOT_LOADED;
325 END IF;
326
327 -- File is a valid Disbursement Roster File. It should be processed.
328
329 fnd_message.set_name('IGF','IGF_DB_CL_ROSTER_FILE');
330 fnd_file.put_line(fnd_file.log,fnd_message.get);
331
332 --Check whether file has been transmitted completely or not
333
334 OPEN c_trailer;
335 FETCH c_trailer INTO l_last_lort_id, l_number_rec, l_tot_net_disb_amt,
336 l_tot_net_eft_amt, l_tot_net_non_eft_amt,
337 l_tot_reissue_amt, l_tot_cancel_amt, l_tot_deficit_amt,
338 l_tot_net_cancel_amt, l_tot_net_out_cancel_amt;
339 IF c_trailer%NOTFOUND THEN
340 CLOSE c_trailer;
341 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
342 -- File uploaded is incomplete.
343 igs_ge_msg_stack.add;
344 RAISE FILE_NOT_LOADED;
345 END IF;
346 CLOSE c_trailer;
347
348 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
349 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug',' got trailer record ');
350 END IF;
351
352
353 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
354 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','before inserting batch record');
355 END IF;
356
357 l_rowid := NULL;
358
359 IF g_cl_version = 'RELEASE-5' THEN
360 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
361 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','before inserting batch record for Release-5');
362 END IF;
363
364 igf_sl_cl_batch_pkg.insert_row(
365 x_rowid => l_rowid,
366 x_cbth_id => l_cbth_id,
367 x_batch_id => l_batch_id,
368 x_file_creation_date => l_file_creation_dt,
369 x_file_trans_date => l_file_trans_dt,
370 x_file_ident_code => l_file_ident_code,
371 x_recipient_id => l_recipient_id,
372 x_recip_non_ed_brc_id => l_recip_non_ed_brc_id,
373 x_source_id => l_source_id,
374 x_source_non_ed_brc_id => l_source_non_ed_brc_id,
375 x_send_resp => 'D',
376 x_mode => 'R',
377 x_record_count_num => l_number_rec ,
378 x_total_net_disb_amt => l_tot_net_disb_amt ,
379 x_total_net_eft_amt => l_tot_net_eft_amt ,
380 x_total_net_non_eft_amt => l_tot_net_non_eft_amt ,
381 x_total_reissue_amt => l_tot_reissue_amt ,
382 x_total_cancel_amt => l_tot_cancel_amt ,
383 x_total_deficit_amt => l_tot_deficit_amt ,
384 x_total_net_cancel_amt => l_tot_net_cancel_amt ,
385 x_total_net_out_cancel_amt => l_tot_net_out_cancel_amt
386 );
387 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
388 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','After inserting batch record for Release-5');
389 END IF;
390
391
392 ELSIF g_cl_version = 'RELEASE-4' THEN
393 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
394 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','before inserting batch record for Release-4');
395 END IF;
396
397 igf_sl_cl_batch_pkg.insert_row(
398 x_rowid => l_rowid,
399 x_cbth_id => l_cbth_id,
400 x_batch_id => l_batch_id,
401 x_file_creation_date => l_file_creation_dt,
402 x_file_trans_date => l_file_trans_dt,
403 x_file_ident_code => l_file_ident_code,
404 x_recipient_id => l_recipient_id,
405 x_recip_non_ed_brc_id => l_recip_non_ed_brc_id,
406 x_source_id => l_source_id,
407 x_source_non_ed_brc_id => l_source_non_ed_brc_id,
408 x_send_resp => 'D',
409 x_mode => 'R',
410 x_record_count_num => l_number_rec ,
411 x_total_net_disb_amt => l_tot_net_disb_amt ,
412 x_total_net_eft_amt => l_tot_net_eft_amt ,
413 x_total_net_non_eft_amt => l_tot_net_non_eft_amt ,
414 x_total_reissue_amt => l_tot_reissue_amt ,
415 x_total_cancel_amt => l_tot_cancel_amt ,
416 x_total_deficit_amt => l_tot_deficit_amt ,
417 x_total_net_cancel_amt => l_tot_net_cancel_amt ,
418 x_total_net_out_cancel_amt => l_tot_net_out_cancel_amt
419 );
420
421 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
422 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','after inserting batch record for Release-4');
423 END IF;
424
425 END IF;
426
427 OPEN c_get_header_parameters;
428 LOOP
429 FETCH c_get_header_parameters INTO header_parameter_rec;
430 EXIT WHEN c_get_header_parameters%NOTFOUND;
431
432 IF header_parameter_rec.lookup_code ='HEADER' THEN
433 lv_header := TRIM(header_parameter_rec.meaning);
434 ELSIF header_parameter_rec.lookup_code ='SOURCE_ENTITY_ID_TXT' THEN
435 lv_source_id := TRIM(header_parameter_rec.meaning);
436 ELSIF header_parameter_rec.lookup_code ='SOURCE_NAME' THEN
437 lv_source_name := TRIM(header_parameter_rec.meaning);
438 ELSIF header_parameter_rec.lookup_code ='RECIPIENT_ID' THEN
439 lv_recipient_id := TRIM(header_parameter_rec.meaning);
440 ELSIF header_parameter_rec.lookup_code ='RECIPIENT_NAME' THEN
441 lv_recipient_name := TRIM(header_parameter_rec.meaning);
442 ELSIF header_parameter_rec.lookup_code ='FILE_CREATION_DATE' THEN
443 lv_file_creation_date := TRIM(header_parameter_rec.meaning);
444 ELSIF header_parameter_rec.lookup_code ='FILE_CREATION_TIME' THEN
445 lv_file_creation_time := TRIM(header_parameter_rec.meaning);
446 END IF;
447
448 END LOOP;
449 CLOSE c_get_header_parameters;
450
451 fnd_file.new_line(fnd_file.output,1);
452 fnd_file.put_line(fnd_file.output, lv_header);
453 fnd_file.new_line(fnd_file.output,1);
454 fnd_file.put_line(fnd_file.output, RPAD(lv_source_id,30) || ' : '|| RPAD(l_source_id,40) || RPAD(lv_source_name,30) || ' : '|| l_source_name );
455 fnd_file.put_line(fnd_file.output, RPAD(lv_recipient_id,30) || ' : '|| RPAD(l_recipient_id,40) || RPAD(lv_recipient_name,30) || ' : '|| l_recipient_name);
456 fnd_file.put_line(fnd_file.output, RPAD(lv_file_creation_date,30) || ' : '|| RPAD(l_file_creation_dt,40) || RPAD(lv_file_creation_time,30) || ' : '|| l_file_creation_time);
457 fnd_file.new_line(fnd_file.output,1);
458
459
460 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
461 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','after print header');
462 END IF;
463
464 OPEN c_get_trailer_parameters;
465 LOOP
466 FETCH c_get_trailer_parameters INTO trailer_parameter_rec;
467 EXIT WHEN c_get_trailer_parameters%NOTFOUND;
468
469 IF trailer_parameter_rec.lookup_code ='TRAILER' THEN
470 lv_trailer := TRIM(trailer_parameter_rec.meaning);
471 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_DISBURSEMNT_RECORD_COUNT' THEN
472 lv_number_rec := TRIM(trailer_parameter_rec.meaning);
473 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_NET_DISBURSEMENT_AMOUNT' THEN
474 lv_tot_net_disb_amt := TRIM(trailer_parameter_rec.meaning);
475 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_NET_EFT_AMOUNT' THEN
476 lv_tot_net_eft_amt := TRIM(trailer_parameter_rec.meaning);
477 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_NET_NON-EFT_AMOUNT' THEN
478 lv_tot_net_non_eft_amt := TRIM(trailer_parameter_rec.meaning);
479 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_REISSUE_AMOUNT' THEN
480 lv_tot_reissue_amt := TRIM(trailer_parameter_rec.meaning);
481 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_CANCELLATION_AMOUNT' THEN
482 lv_tot_cancel_amt := TRIM(trailer_parameter_rec.meaning);
483 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_DEFICIT_AMOUNT' THEN
484 lv_tot_deficit_amt := TRIM(trailer_parameter_rec.meaning);
485 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_NETED_CANCELATION_AMOUNT' THEN
486 lv_tot_net_cancel_amt := TRIM(trailer_parameter_rec.meaning);
487 ELSIF trailer_parameter_rec.lookup_code ='TOTAL_NETED_OUTSTNDING_CAMOUNT' THEN
488 lv_tot_net_out_cancel_amt := TRIM(trailer_parameter_rec.meaning);
489 END IF;
490
491 END LOOP;
492 CLOSE c_get_trailer_parameters;
493
494
495 fnd_file.new_line(fnd_file.output,1);
496 fnd_file.put_line(fnd_file.output, lv_trailer);
497 fnd_file.new_line(fnd_file.output,1);
498 fnd_file.put_line(fnd_file.output, RPAD(lv_number_rec,50) || ' : '|| l_number_rec);
499 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_net_disb_amt ,50) || ' : '|| l_tot_net_disb_amt);
500 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_net_eft_amt,50) || ' : '|| l_tot_net_eft_amt);
501 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_net_non_eft_amt,50) || ' : '|| l_tot_net_non_eft_amt);
502 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_reissue_amt,50) || ' : '|| l_tot_reissue_amt);
503 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_cancel_amt,50) || ' : '|| l_tot_cancel_amt);
504 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_deficit_amt,50) || ' : '|| l_tot_deficit_amt);
505 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_net_cancel_amt ,50) || ' : '|| l_tot_net_cancel_amt);
506 fnd_file.put_line(fnd_file.output, RPAD(lv_tot_net_out_cancel_amt,50) || ' : '|| l_tot_net_out_cancel_amt);
507 fnd_file.new_line(fnd_file.output,1);
508
509
510
511 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
512 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','after print trailer');
513 END IF;
514 --Insert records into IGF_DB_CL_DISB_RESP
515 OPEN cur_roster_logs;
516 LOOP
517 FETCH cur_roster_logs INTO roster_logs_rec;
518 EXIT WHEN cur_roster_logs%NOTFOUND;
519
520 IF roster_logs_rec.lookup_code ='DISBURSEMENT_ROSTER_@1_DETAIL' THEN
521 lv_disb := TRIM(roster_logs_rec.meaning);
522 ELSIF roster_logs_rec.lookup_code ='RECORD_TYPE' THEN
523 lv_record_type := TRIM(roster_logs_rec.meaning);
524 ELSIF roster_logs_rec.lookup_code ='LOAN_SEQUENCE_NUMBER' THEN
525 lv_loan_sequence_number := TRIM(roster_logs_rec.meaning);
526 ELSIF roster_logs_rec.lookup_code ='LOAN_NUMBER' THEN
527 lv_loan_number := TRIM(roster_logs_rec.meaning);
528 ELSIF roster_logs_rec.lookup_code ='LOAN_PERIOD_END_DATE' THEN
529 lv_loan_period_end_date := TRIM(roster_logs_rec.meaning);
530 ELSIF roster_logs_rec.lookup_code ='LOAN_PERIOD_START_DATE' THEN
531 lv_loan_period_start_date := TRIM(roster_logs_rec.meaning);
532 ELSIF roster_logs_rec.lookup_code ='LENDER_ID' THEN
533 lv_lender_id := TRIM(roster_logs_rec.meaning);
534 ELSIF roster_logs_rec.lookup_code ='LENDER_NON_ED_BRANCH_ID' THEN
535 lv_lender_non_ed_branch_id := TRIM(roster_logs_rec.meaning);
536 ELSIF roster_logs_rec.lookup_code ='TOTAL_SCHEDULED_DISBURSEMENT' THEN
537 lv_total_schd_disb := TRIM(roster_logs_rec.meaning);
538 ELSIF roster_logs_rec.lookup_code ='DISBURSEMENT_NUMBER' THEN
539 lv_disbursement_number := TRIM(roster_logs_rec.meaning);
540 ELSIF roster_logs_rec.lookup_code ='GUARANTOR_ID' THEN
541 lv_guarantor_id := TRIM(roster_logs_rec.meaning);
542 ELSIF roster_logs_rec.lookup_code ='GUARANTEE_DATE' THEN
543 lv_guarantee_date := TRIM(roster_logs_rec.meaning);
544 ELSIF roster_logs_rec.lookup_code ='GUARANTEE_AMOUNT' THEN
545 lv_guarantee_amount := TRIM(roster_logs_rec.meaning);
546 ELSIF roster_logs_rec.lookup_code ='FUND_RELEASE_DATE' THEN
547 lv_fund_release_date := TRIM(roster_logs_rec.meaning);
548 ELSIF roster_logs_rec.lookup_code ='GROSS_DISBURSEMENT_AMOUNT' THEN
549 lv_gross_disbursement_amount := TRIM(roster_logs_rec.meaning);
550 ELSIF roster_logs_rec.lookup_code ='GUARANTEE_FEES' THEN
551 lv_guarantee_fees := TRIM(roster_logs_rec.meaning);
552 ELSIF roster_logs_rec.lookup_code ='ORIGINATION_FEES_PAID' THEN
553 lv_origination_fees_paid := TRIM(roster_logs_rec.meaning);
554 ELSIF roster_logs_rec.lookup_code ='ORIGINATION_FEES' THEN
555 lv_origination_fees := TRIM(roster_logs_rec.meaning);
556 ELSIF roster_logs_rec.lookup_code ='GUARANTEE_FEES_PAID' THEN
557 lv_guarantee_fees_paid := TRIM(roster_logs_rec.meaning);
558 ELSIF roster_logs_rec.lookup_code ='NET_DISBURSEMENT_AMOUNT' THEN
559 lv_net_disbursement_amount := TRIM(roster_logs_rec.meaning);
560 ELSIF roster_logs_rec.lookup_code ='FEES_PAID' THEN
561 lv_fees_paid := TRIM(roster_logs_rec.meaning);
562 ELSIF roster_logs_rec.lookup_code ='FUND_DISTRIBUTION_METHOD' THEN
563 lv_fund_distribution_method := TRIM(roster_logs_rec.meaning);
564 ELSIF roster_logs_rec.lookup_code ='CHECK_NUMBER' THEN
565 lv_check_number := TRIM(roster_logs_rec.meaning);
566 ELSIF roster_logs_rec.lookup_code ='LATE_DISBURSEMENT' THEN
567 lv_late_disbursement := TRIM(roster_logs_rec.meaning);
568 ELSIF roster_logs_rec.lookup_code ='PREVIOUSLY_REPORTED' THEN
569 lv_previously_reported := TRIM(roster_logs_rec.meaning);
570 ELSIF roster_logs_rec.lookup_code ='NET_CANCELLATION_AMOUNT' THEN
571 lv_net_cancellation_amount := TRIM(roster_logs_rec.meaning);
572 ELSIF roster_logs_rec.lookup_code ='NETTED_CANCEL_AMOUNT' THEN
573 lv_netted_cancel_amount := TRIM(roster_logs_rec.meaning);
574 ELSIF roster_logs_rec.lookup_code ='OUTSTANDING_CANCELATION_AMOUNT' THEN
575 lv_outstanding_can_amt := TRIM(roster_logs_rec.meaning);
576 ELSIF roster_logs_rec.lookup_code ='ESIGN_SRC_TYP_CD' THEN
577 lv_esign_src_typ_cd := TRIM(roster_logs_rec.meaning);
578 ELSIF roster_logs_rec.lookup_code ='DIRECT_TO_BORR_IND' THEN
579 lv_direct_to_borr_ind_mng := TRIM(roster_logs_rec.meaning);
580 END IF;
581 END LOOP;
582 CLOSE cur_roster_logs;
583
584 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
585 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','begin data processing ');
586 END IF;
587
588 fnd_file.new_line(fnd_file.output,1);
589 fnd_file.put_line(fnd_file.output, lv_disb);
590
591 FOR db_rec IN c_dbcl
592 LOOP
593
594 BEGIN
595
596 SAVEPOINT IGFDB04B_SP1_1;
597 l_actual_rec := l_actual_rec + 1;
598 l_rowid := NULL;
599
600
601 l_record_type := LTRIM(RTRIM(SUBSTR(db_rec.record_data,3,1)));
602 l_loan_number := LTRIM(RTRIM(SUBSTR(db_rec.record_data,4,17)));
603 l_cl_seq_number := LTRIM(RTRIM(SUBSTR(db_rec.record_data,21,2)));
604 l_loan_per_start_date := TO_DATE(TRIM(SUBSTR(db_rec.record_data,280,8)),'YYYYMMDD');
605 l_loan_per_end_date := TO_DATE(TRIM(SUBSTR(db_rec.record_data,288,8)),'YYYYMMDD');
606 l_lender_id := LTRIM(RTRIM(SUBSTR(db_rec.record_data,301,6)));
607 l_lend_non_ed_brc_id := LTRIM(RTRIM(SUBSTR(db_rec.record_data,307,4)));
608 l_tot_sched_disb := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,332,2)));
609 l_disb_num := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,342,1)));
610 l_guarantor_id := LTRIM(RTRIM(SUBSTR(db_rec.record_data,344,3)));
611 l_guarantee_date := TO_DATE(SUBSTR(db_rec.record_data,370,8),'YYYYMMDD'); --pssahni change fnd_date.string_to_date to TO_DATE
612 l_guarantee_amt := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,378,5)));
613 l_fund_release_date := TO_DATE(SUBSTR(db_rec.record_data,334,8),'YYYYMMDD'); --pssahni change fnd_date.string_to_date to TO_DATE
614 l_gross_disb_amt := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,383,7)))/100; -- 9(005)V99
615 l_fee_1 := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,390,7)))/100;
616 l_fee_2 := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,397,7)))/100;
617 l_net_disb_amt := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,404,7)))/100;
618 l_fund_dist_mthd := LTRIM(RTRIM(SUBSTR(db_rec.record_data,411,1)));
619 l_check_number := LTRIM(RTRIM(SUBSTR(db_rec.record_data,412,15)));
620 l_late_disb_ind := LTRIM(RTRIM(SUBSTR(db_rec.record_data,427,1)));
621 l_prev_reported_ind := LTRIM(RTRIM(SUBSTR(db_rec.record_data,428,1)));
622 l_net_cancel_amt := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,466,7)))/100;
623
624 IF g_cl_version = 'RELEASE-5' THEN
625 l_hold_rel_ind := LTRIM(RTRIM(SUBSTR(db_rec.record_data,500,1)));
626 l_pnote_code := LTRIM(RTRIM(SUBSTR(db_rec.record_data,501,2)));
627 l_pnote_status_date := TO_DATE(SUBSTR(db_rec.record_data,503,14),'YYYYMMDDHH24MISS'); --pssahni change fnd_date.string_to_date to TO_DATE
628 l_fee_paid_1 := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,517,7)))/100;
629 --Build FA163
630 l_fee_paid_2 := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,444,7)))/100;
631 l_direct_to_borr_ind := NVL(SUBSTR(db_rec.record_data,482,1),' ');
632 l_netted_cancel_amt := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,524,7)))/100;
633 l_outstd_cancel_amt := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,531,7)))/100;
634 l_sch_non_ed_brc_id := LTRIM(RTRIM(SUBSTR(db_rec.record_data,538,4)));
635 ELSIF g_cl_version = 'RELEASE-4' THEN
636 l_pnote_code := NULL;
637 l_pnote_status_date := NULL;
638 l_fee_paid_1 := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,482,5)))/100; --Build FA163
639 l_fee_paid_2 := TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,487,5)))/100;
640 l_direct_to_borr_ind := NVL(SUBSTR(db_rec.record_data,492,1),' ');
641 l_netted_cancel_amt := NULL;
642 l_outstd_cancel_amt := NULL;
643 l_sch_non_ed_brc_id := NULL;
644 END IF;
645 fnd_file.new_line(fnd_file.output,1);
646 fnd_file.put_line(fnd_file.output, RPAD(lv_record_type,40) || ' : '|| l_record_type);
647 fnd_file.put_line(fnd_file.output, RPAD(lv_loan_number,40) || ' : '|| RPAD(l_loan_number,40) || RPAD(lv_loan_sequence_number,30) || ' : '|| l_cl_seq_number);
648 fnd_file.put_line(fnd_file.output, RPAD(lv_loan_period_start_date,40) || ' : '|| RPAD(l_loan_per_start_date,40) || RPAD(lv_loan_period_end_date,30) || ' : '|| l_loan_per_end_date);
649 fnd_file.put_line(fnd_file.output, RPAD(lv_lender_id,40) || ' : '|| RPAD(l_lender_id,40) || RPAD(lv_lender_non_ed_branch_id,30) || ' : '|| l_lend_non_ed_brc_id);
650 fnd_file.put_line(fnd_file.output, RPAD(lv_total_schd_disb,40) || ' : '|| RPAD(l_tot_sched_disb,40) || RPAD(lv_disbursement_number,30) || ' : '|| l_disb_num );
651 fnd_file.put_line(fnd_file.output, RPAD(lv_guarantor_id,40) || ' : '|| RPAD(l_guarantor_id,40) || RPAD(lv_guarantee_date,30) || ' : '|| l_guarantee_date );
652 fnd_file.put_line(fnd_file.output, RPAD(lv_guarantee_amount ,40) || ' : '|| l_guarantee_amt);
653 fnd_file.put_line(fnd_file.output, RPAD(lv_fund_release_date,40) || ' : '|| l_fund_release_date);
654 fnd_file.put_line(fnd_file.output, RPAD(lv_gross_disbursement_amount,40) || ' : '|| RPAD(l_gross_disb_amt,40) || RPAD(lv_guarantee_fees,30) || ' : '|| l_fee_2);
655 fnd_file.put_line(fnd_file.output, RPAD(lv_origination_fees_paid,40) || ' : '|| RPAD(l_fee_paid_1,40) || RPAD(lv_origination_fees,30) || ' : '|| l_fee_1 );
656 fnd_file.put_line(fnd_file.output, RPAD(lv_guarantee_fees_paid ,40) || ' : '|| RPAD(NVL(l_fee_paid_2,0),40) || RPAD( lv_net_disbursement_amount,30) || ' : '|| l_net_disb_amt );
657 fnd_file.put_line(fnd_file.output, RPAD(lv_fund_distribution_method ,40) || ' : '|| RPAD(l_fund_dist_mthd,40) || RPAD(lv_check_number,30) || ' : '|| l_check_number);
658 fnd_file.put_line(fnd_file.output, RPAD(lv_late_disbursement ,40) || ' : '|| RPAD(l_late_disb_ind,40) || RPAD(lv_previously_reported,30) || ' : '|| l_prev_reported_ind);
659 fnd_file.put_line(fnd_file.output, RPAD(lv_net_cancellation_amount,40) || ' : '|| RPAD(l_net_cancel_amt,40) || RPAD(lv_netted_cancel_amount,30) || ' : '|| NVL(l_netted_cancel_amt,0) );
660 fnd_file.put_line(fnd_file.output, RPAD(lv_outstanding_can_amt,40) || ' : '|| NVL(l_outstd_cancel_amt,0));
661 fnd_file.put_line(fnd_file.output, RPAD(lv_esign_src_typ_cd,40) || ' : '|| LTRIM(RTRIM(SUBSTR(db_rec.record_data,473,9))));
662 fnd_file.put_line(fnd_file.output, RPAD(lv_direct_to_borr_ind_mng,40) || ' : '|| l_direct_to_borr_ind);
663 fnd_file.new_line(fnd_file.output,1);
664 igf_db_cl_disb_resp_pkg.insert_row (x_mode => 'R',
665 x_rowid => l_rowid,
666 x_cdbr_id => l_cdbr_id,
667 x_cbth_id => l_cbth_id,
668 x_record_type => l_record_type ,
669 x_loan_number => l_loan_number,
670 x_cl_seq_number => l_cl_seq_number,
671 x_b_last_name => LTRIM(RTRIM(SUBSTR(db_rec.record_data,23,35))),
672 x_b_first_name => LTRIM(RTRIM(SUBSTR(db_rec.record_data,58,12))),
673 x_b_middle_name => LTRIM(RTRIM(SUBSTR(db_rec.record_data,70,1))),
674 x_b_ssn => LTRIM(RTRIM(SUBSTR(db_rec.record_data,71,9))),
675 x_b_addr_line_1 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,80,30))),
676 x_b_addr_line_2 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,110,30))),
677 x_b_city => LTRIM(RTRIM(SUBSTR(db_rec.record_data,140,24))),
678 x_b_state => LTRIM(RTRIM(SUBSTR(db_rec.record_data,170,2))),
679 x_b_zip => LTRIM(RTRIM(SUBSTR(db_rec.record_data,172,5))),
680 x_b_zip_suffix => LTRIM(RTRIM(SUBSTR(db_rec.record_data,177,4))),
681 x_b_addr_chg_date => fnd_date.string_to_date(SUBSTR(db_rec.record_data,181,8),'YYYYMMDD'),
682 x_eft_auth_code => LTRIM(RTRIM(SUBSTR(db_rec.record_data,189,1))),
683 x_s_last_name => LTRIM(RTRIM(SUBSTR(db_rec.record_data,190,35))),
684 x_s_first_name => LTRIM(RTRIM(SUBSTR(db_rec.record_data,225,12))),
685 x_s_middle_initial => LTRIM(RTRIM(SUBSTR(db_rec.record_data,237,1))),
686 x_s_ssn => LTRIM(RTRIM(SUBSTR(db_rec.record_data,238,9))),
687 x_school_id => TO_NUMBER(TRIM(SUBSTR(db_rec.record_data,247,8))),
688 x_school_use => LTRIM(RTRIM(SUBSTR(db_rec.record_data,257,23))),
689 x_loan_per_start_date => l_loan_per_start_date,
690 x_loan_per_end_date => l_loan_per_end_date,
691 x_cl_loan_type => LTRIM(RTRIM(SUBSTR(db_rec.record_data,296,2))),
692 x_alt_prog_type_code => LTRIM(RTRIM(SUBSTR(db_rec.record_data,298,3))),
693 x_lender_id => l_lender_id,
694 x_lend_non_ed_brc_id => l_lend_non_ed_brc_id ,
695 x_lender_use => LTRIM(RTRIM(SUBSTR(db_rec.record_data,311,20))),
696 x_borw_confirm_ind => LTRIM(RTRIM(SUBSTR(db_rec.record_data,331,1))),
697 x_tot_sched_disb => l_tot_sched_disb,
698 x_fund_release_date => l_fund_release_date,
699 x_disb_num => l_disb_num,
700 x_guarantor_id => l_guarantor_id,
701 x_guarantor_use => LTRIM(RTRIM(SUBSTR(db_rec.record_data,347,23))),
702 x_guarantee_date => l_guarantee_date,
703 x_guarantee_amt => l_guarantee_amt,
704 x_gross_disb_amt => l_gross_disb_amt,
705 x_fee_1 => l_fee_1,
706 x_fee_2 => l_fee_2,
707 x_net_disb_amt => l_net_disb_amt ,
708 x_fund_dist_mthd => l_fund_dist_mthd,
709 x_check_number => l_check_number,
710 x_late_disb_ind => l_late_disb_ind,
711 x_prev_reported_ind => l_prev_reported_ind,
712 x_err_code1 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,429,3))),
713 x_err_code2 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,432,3))),
714 x_err_code3 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,435,3))),
715 x_err_code4 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,438,3))),
716 x_err_code5 => LTRIM(RTRIM(SUBSTR(db_rec.record_data,441,3))),
717 x_fee_paid_2 => l_fee_paid_2,
718 x_lender_name => LTRIM(RTRIM(SUBSTR(db_rec.record_data,451,15))),
719 x_net_cancel_amt => l_net_cancel_amt,
720 x_duns_lender_id => NULL,
721 x_duns_guarnt_id => NULL,
722 x_hold_rel_ind => l_hold_rel_ind,
723 x_pnote_code => l_pnote_code,
724 x_pnote_status_date => l_pnote_status_date,
725 x_fee_paid_1 => l_fee_paid_1,
726 x_netted_cancel_amt => l_netted_cancel_amt,
727 x_outstd_cancel_amt => l_outstd_cancel_amt,
728 x_sch_non_ed_brc_id => l_sch_non_ed_brc_id,
729 x_status => 'N',
730 x_esign_src_typ_cd => LTRIM(RTRIM(SUBSTR(db_rec.record_data,473,9))),
731 x_direct_to_borr_flag => l_direct_to_borr_ind);
732
733 -- FA 161 CL4 Updates build.
734 lv_esign_roster_data := LTRIM(RTRIM(SUBSTR(db_rec.record_data, 473, 9)));
735 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
736 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','Esign src typ code in roster = '||lv_esign_roster_data);
737 END IF;
738
739 IF LENGTH(lv_esign_roster_data) > 0 THEN
740 OPEN cur_lor_data(l_loan_number);
741 FETCH cur_lor_data INTO rec_lor_data;
742 IF cur_lor_data%NOTFOUND THEN
743 CLOSE cur_lor_data;
744 RAISE SKIP_THIS_RECORD;
745 ELSE
746 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
747 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.cl_load_data.debug','Esign src typ code in system = '||NVL(rec_lor_data.esign_src_typ_cd,'*'));
748 END IF;
749 IF NVL(rec_lor_data.esign_src_typ_cd,'*') <> lv_esign_roster_data THEN
750 igf_sl_lor_pkg.update_row (
751 X_Mode => 'R',
752 x_rowid => rec_lor_data.row_id,
753 x_origination_id => rec_lor_data.origination_id,
754 x_loan_id => rec_lor_data.loan_id,
755 x_sch_cert_date => rec_lor_data.sch_cert_date,
756 x_orig_status_flag => rec_lor_data.orig_status_flag,
757 x_orig_batch_id => rec_lor_data.orig_batch_id,
758 x_orig_batch_date => rec_lor_data.orig_batch_date,
759 x_chg_batch_id => rec_lor_data.chg_batch_id,
760 x_orig_ack_date => rec_lor_data.orig_ack_date,
761 x_credit_override => rec_lor_data.credit_override,
762 x_credit_decision_date => rec_lor_data.credit_decision_date,
763 x_req_serial_loan_code => rec_lor_data.req_serial_loan_code,
764 x_act_serial_loan_code => rec_lor_data.act_serial_loan_code,
765 x_pnote_delivery_code => rec_lor_data.pnote_delivery_code,
766 x_pnote_status => rec_lor_data.pnote_status,
767 x_pnote_status_date => rec_lor_data.pnote_status_date,
768 x_pnote_id => rec_lor_data.pnote_id,
769 x_pnote_print_ind => rec_lor_data.pnote_print_ind,
770 x_pnote_accept_amt => rec_lor_data.pnote_accept_amt,
771 x_pnote_accept_date => rec_lor_data.pnote_accept_date,
772 x_unsub_elig_for_heal => rec_lor_data.unsub_elig_for_heal,
773 x_disclosure_print_ind => rec_lor_data.disclosure_print_ind,
774 x_orig_fee_perct => rec_lor_data.orig_fee_perct,
775 x_borw_confirm_ind => rec_lor_data.borw_confirm_ind,
776 x_borw_interest_ind => rec_lor_data.borw_interest_ind,
777 x_borw_outstd_loan_code => rec_lor_data.borw_outstd_loan_code,
778 x_unsub_elig_for_depnt => rec_lor_data.unsub_elig_for_depnt,
779 x_guarantee_amt => rec_lor_data.guarantee_amt,
780 x_guarantee_date => rec_lor_data.guarantee_date,
781 x_guarnt_amt_redn_code => rec_lor_data.guarnt_amt_redn_code,
782 x_guarnt_status_code => rec_lor_data.guarnt_status_code,
783 x_guarnt_status_date => rec_lor_data.guarnt_status_date,
784 x_lend_apprv_denied_code => rec_lor_data.lend_apprv_denied_code, --NULL,
785 x_lend_apprv_denied_date => rec_lor_data.lend_apprv_denied_date, --NULL,
786 x_lend_status_code => rec_lor_data.lend_status_code,
787 x_lend_status_date => rec_lor_data.lend_status_date,
788 x_guarnt_adj_ind => rec_lor_data.guarnt_adj_ind,
789 x_grade_level_code => rec_lor_data.grade_level_code,
790 x_enrollment_code => rec_lor_data.enrollment_code,
791 x_anticip_compl_date => rec_lor_data.anticip_compl_date,
792 x_borw_lender_id => rec_lor_data.borw_lender_id, --NULL,
793 x_duns_borw_lender_id => rec_lor_data.duns_borw_lender_id, --NULL,
794 x_guarantor_id => rec_lor_data.guarantor_id, --NULL,
795 x_duns_guarnt_id => rec_lor_data.duns_guarnt_id, --NULL,
796 x_prc_type_code => rec_lor_data.prc_type_code,
797 x_cl_seq_number => rec_lor_data.cl_seq_number,
798 x_last_resort_lender => rec_lor_data.last_resort_lender,
799 x_lender_id => rec_lor_data.lender_id, --NULL,
800 x_duns_lender_id => rec_lor_data.duns_lender_id, --NULL,
801 x_lend_non_ed_brc_id => rec_lor_data.lend_non_ed_brc_id, --NULL,
802 x_recipient_id => rec_lor_data.recipient_id, --NULL,
803 x_recipient_type => rec_lor_data.recipient_type, --NULL,
804 x_duns_recip_id => rec_lor_data.duns_recip_id, --NULL,
805 x_recip_non_ed_brc_id => rec_lor_data.recip_non_ed_brc_id, --NULL,
806 x_rec_type_ind => rec_lor_data.rec_type_ind,
807 x_cl_loan_type => rec_lor_data.cl_loan_type,
808 x_cl_rec_status => rec_lor_data.cl_rec_status, --NULL,
809 x_cl_rec_status_last_update => rec_lor_data.cl_rec_status_last_update, --NULL,
810 x_alt_prog_type_code => rec_lor_data.alt_prog_type_code,
811 x_alt_appl_ver_code => rec_lor_data.alt_appl_ver_code,
812 x_mpn_confirm_code => rec_lor_data.mpn_confirm_code, --NULL,
813 x_resp_to_orig_code => rec_lor_data.resp_to_orig_code,
814 x_appl_loan_phase_code => rec_lor_data.appl_loan_phase_code, --NULL,
815 x_appl_loan_phase_code_chg => rec_lor_data.appl_loan_phase_code_chg, --NULL,
816 x_appl_send_error_codes => rec_lor_data.appl_send_error_codes, --NULL,
817 x_tot_outstd_stafford => rec_lor_data.tot_outstd_stafford,
818 x_tot_outstd_plus => rec_lor_data.tot_outstd_plus,
819 x_alt_borw_tot_debt => rec_lor_data.alt_borw_tot_debt,
820 x_act_interest_rate => rec_lor_data.act_interest_rate,
821 x_service_type_code => rec_lor_data.service_type_code,
822 x_rev_notice_of_guarnt => rec_lor_data.rev_notice_of_guarnt,
823 x_sch_refund_amt => rec_lor_data.sch_refund_amt,
824 x_sch_refund_date => rec_lor_data.sch_refund_date,
825 x_uniq_layout_vend_code => rec_lor_data.uniq_layout_vend_code,
826 x_uniq_layout_ident_code => rec_lor_data.uniq_layout_ident_code,
827 x_p_person_id => rec_lor_data.p_person_id,
828 x_p_ssn_chg_date => rec_lor_data.p_ssn_chg_date, --NULL,
829 x_p_dob_chg_date => rec_lor_data.p_dob_chg_date, --NULL,
830 x_p_permt_addr_chg_date => rec_lor_data.p_permt_addr_chg_date, --NULL,
831 x_p_default_status => rec_lor_data.p_default_status,
832 x_p_signature_code => rec_lor_data.p_signature_code,
833 x_p_signature_date => rec_lor_data.p_signature_date,
834 x_s_ssn_chg_date => rec_lor_data.s_ssn_chg_date, --NULL,
835 x_s_dob_chg_date => rec_lor_data.s_dob_chg_date, --NULL,
836 x_s_permt_addr_chg_date => rec_lor_data.s_permt_addr_chg_date, --NULL,
837 x_s_local_addr_chg_date => rec_lor_data.s_local_addr_chg_date, --NULL,
838 x_s_default_status => rec_lor_data.s_default_status,
839 x_s_signature_code => rec_lor_data.s_signature_code,
840 x_pnote_batch_id => rec_lor_data.pnote_batch_id,
841 x_pnote_ack_date => rec_lor_data.pnote_ack_date,
842 x_pnote_mpn_ind => rec_lor_data.pnote_mpn_ind ,
843 x_elec_mpn_ind => rec_lor_data.elec_mpn_ind ,
844 x_borr_sign_ind => rec_lor_data.borr_sign_ind ,
845 x_stud_sign_ind => rec_lor_data.stud_sign_ind ,
846 x_borr_credit_auth_code => rec_lor_data.borr_credit_auth_code ,
847 x_relationship_cd => rec_lor_data.relationship_cd,
848 x_interest_rebate_percent_num => rec_lor_data.interest_rebate_percent_num,
849 x_cps_trans_num => rec_lor_data.cps_trans_num ,
850 x_atd_entity_id_txt => rec_lor_data.atd_entity_id_txt,
851 x_rep_entity_id_txt => rec_lor_data.rep_entity_id_txt,
852 x_crdt_decision_status => rec_lor_data.crdt_decision_status,
853 x_note_message => rec_lor_data.note_message ,
854 x_book_loan_amt => rec_lor_data.book_loan_amt ,
855 x_book_loan_amt_date => rec_lor_data.book_loan_amt_date,
856 x_actual_record_type_code => rec_lor_data.actual_record_type_code,
857 x_alt_approved_amt => rec_lor_data.alt_approved_amt,
858 x_deferment_request_code => rec_lor_data.deferment_request_code,
859 x_eft_authorization_code => rec_lor_data.eft_authorization_code,
860 x_external_loan_id_txt => rec_lor_data.external_loan_id_txt,
861 x_flp_approved_amt => rec_lor_data.flp_approved_amt,
862 x_fls_approved_amt => rec_lor_data.fls_approved_amt,
863 x_flu_approved_amt => rec_lor_data.flu_approved_amt,
864 x_guarantor_use_txt => rec_lor_data.guarantor_use_txt,
865 x_lender_use_txt => rec_lor_data.lender_use_txt,
866 x_loan_app_form_code => rec_lor_data.loan_app_form_code,
867 x_override_grade_level_code => rec_lor_data.override_grade_level_code,
868 x_pymt_servicer_amt => rec_lor_data.pymt_servicer_amt,
869 x_pymt_servicer_date => rec_lor_data.pymt_servicer_date,
870 x_reinstatement_amt => rec_lor_data.reinstatement_amt,
871 x_requested_loan_amt => rec_lor_data.requested_loan_amt,
872 x_school_use_txt => rec_lor_data.school_use_txt,
873 x_b_alien_reg_num_txt => rec_lor_data.b_alien_reg_num_txt,
874 x_esign_src_typ_cd => lv_esign_roster_data,
875 x_acad_begin_date => rec_lor_data.acad_begin_date,
876 x_acad_end_date => rec_lor_data.acad_end_date
877 );
878 END IF;
879 END IF;
880 CLOSE cur_lor_data;
881 END IF;
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 ROLLBACK TO IGFDB04B_SP1_1;
886 fnd_message.set_name('IGF','IGF_SL_DB_ERROR_UPLOAD');
887 fnd_file.put_line(fnd_file.log, fnd_message.get||' '||SQLERRM);
888 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
889 fnd_file.put_line(fnd_file.log, fnd_message.get);
890 fnd_file.new_line(fnd_file.log, 1);
891 END;
892
893 END LOOP;
894 IF l_actual_rec <> l_number_rec THEN
895 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
896 igs_ge_msg_stack.add;
897 RAISE FILE_NOT_LOADED;
898 END IF;
899 p_cbth_id := l_cbth_id;
900
901 EXCEPTION
902 WHEN FILE_NOT_LOADED THEN
903 RAISE;
904 WHEN CLSETUP_NOT_FOUND THEN
905 RAISE;
906 WHEN OTHERS THEN
907 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
908 fnd_message.set_token('NAME','IGF_DB_CL_ROSTER.CL_LOAD_DATA');
909 fnd_file.put_line(fnd_file.log,SQLERRM);
910 igs_ge_msg_stack.add;
911 app_exception.raise_exception;
912
913 END cl_load_data;
914
915
916 -- ######### Main Procedure ################## --
917
918 PROCEDURE roster_ack(errbuf OUT NOCOPY VARCHAR2,
919 retcode OUT NOCOPY NUMBER,
920 p_update_disb IN VARCHAR2)
921 AS
922 /*************************************************************
923 Created By : sjadhav
924 Date Created On : 2000/12/18
925
926 Purpose :This is the procedeure called by con prog.
927 This procedure updates igf_aw_awd_disb table based on
928 certain conditions.
929 This process will set the status of all the records
930 processed as 'Processed' in the igf_db_cl_disb_resp
931 table.
932
933 Know limitations, enhancements or remarks
934 Change History
935 Who When What
936 (reverse chronological order - newest change first)
937 ridas 07-Aug-2006 Build FA163. Added condition to check the Direct_to_borr_ind difference between
938 the roster information and the disbursement table information.
939 tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
940 veramach 3-NOV-2003 FA 125 Multiple Distr Methods
941 Changed the call of igf_aw_awd_disb_pkg.update_row to reflect the addition of attendance_type_code
942 ***************************************************************/
943
944 l_cbth_id igf_sl_cl_batch_all.cbth_id%TYPE;
945 l_loan_number igf_db_cl_disb_resp_all.loan_number%TYPE;
946 l_disb_num igf_aw_awd_disb_all.disb_num%TYPE;
947
948 l_disb_gross_amt igf_db_cl_disb_resp_all.gross_disb_amt%TYPE;
949 l_fee_1 igf_db_cl_disb_resp_all.fee_1%TYPE;
950 l_fee_2 igf_db_cl_disb_resp_all.fee_2%TYPE;
951 l_net_disb_amt igf_db_cl_disb_resp_all.net_disb_amt%TYPE;
952 l_fee_paid_2 igf_db_cl_disb_resp_all.fee_paid_2%TYPE;
953 l_fee_paid_1 igf_db_cl_disb_resp_all.fee_paid_1%TYPE;
954
955 l_late_disb_ind igf_db_cl_disb_resp_all.late_disb_ind%TYPE;
956 l_fund_dist_mthd igf_db_cl_disb_resp_all.fund_dist_mthd%TYPE;
957 l_prev_reported_ind igf_db_cl_disb_resp_all.prev_reported_ind%TYPE;
958 l_fund_release_date igf_db_cl_disb_resp_all.fund_release_date%TYPE;
959 l_check_number igf_db_cl_disb_resp_all.check_number%TYPE;
960 l_rec_type igf_db_cl_disb_resp_all.record_type%TYPE;
961 lv_rec_status igf_db_cl_disb_resp_all.status%TYPE;
962 l_direct_to_borr_ind igf_db_cl_disb_resp_all.direct_to_borr_flag%TYPE;
963
964 CURSOR cur_db_resp(l_cbth_id igf_sl_cl_batch_all.cbth_id%TYPE) IS
965 SELECT cdresp.* FROM igf_db_cl_disb_resp cdresp
966 WHERE cbth_id = l_cbth_id
967 AND status = 'N'
968 FOR UPDATE OF status NOWAIT;
969
970
971 PROCEDURE log_start(p_loan_number igf_sl_loans_all.loan_number%TYPE,
972 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
973 AS
974 l_msg_str_0 VARCHAR2(1000);
975 BEGIN
976 loan_number_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER');
977 disb_num_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_NUM');
978 loc_disb_desc := igf_aw_gen.lookup_desc('IGF_SL_GEN','LOC_DISB_DETAILS');
979 ofa_disb_desc := igf_aw_gen.lookup_desc('IGF_SL_GEN','OFA_DISB_DETAILS');
980 fee_1_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','FEE_1');
981 fee_2_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','FEE_2');
982 disb_net_amt_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_NET_AMT');
983 fee_paid_1_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','FEE_PAID_1');
984 fee_paid_2_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','FEE_PAID_2');
985 disb_gross_amt_desc := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_GROSS_AMT');
986 direct_to_borr_ind_desc := igf_aw_gen.lookup_desc('IGF_SL_CL_ROSTER_LOGS','DIRECT_TO_BORR_IND');
987 fnd_file.put_line(fnd_file.log, '');
988 l_msg_str_0 := RPAD(loan_number_desc,30)||' : '|| p_loan_number ||'
989 '||RPAD(disb_num_desc,30) ||' : '||TO_CHAR(p_disb_num);
990
991 fnd_file.put_line(fnd_file.log,l_msg_str_0);
992
993 END log_start;
994
995
996 BEGIN
997
998 igf_aw_gen.set_org_id(NULL);
999 retcode := 0;
1000 -- Load the data into the Batch and Response Table
1001
1002 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1003 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug','Calling Load Data');
1004 END IF;
1005
1006 cl_load_data(l_cbth_id);
1007
1008 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1009 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug','After Load Data, Batch ID ' || l_cbth_id);
1010 END IF;
1011
1012 -- Select all the records from IGF_DB_CL_DISB_RESP table
1013 -- with status = 'N' for the batch id returned by load process.
1014
1015 FOR cbth_rec IN cur_db_resp(l_cbth_id) LOOP -- Main FOR LOOP
1016
1017 l_loan_number := cbth_rec.loan_number;
1018 l_disb_num := cbth_rec.disb_num;
1019 l_disb_gross_amt := cbth_rec.gross_disb_amt;
1020 l_fee_1 := cbth_rec.fee_1;
1021 l_fee_2 := cbth_rec.fee_2;
1022 l_net_disb_amt := cbth_rec.net_disb_amt;
1023 l_fee_paid_2 := cbth_rec.fee_paid_2;
1024 l_fee_paid_1 := cbth_rec.fee_paid_1;
1025 l_late_disb_ind := cbth_rec.late_disb_ind;
1026 l_fund_dist_mthd := cbth_rec.fund_dist_mthd;
1027 l_prev_reported_ind := cbth_rec.prev_reported_ind;
1028 l_fund_release_date := cbth_rec.fund_release_date;
1029 l_check_number := cbth_rec.check_number;
1030 l_rec_type := cbth_rec.record_type;
1031 l_direct_to_borr_ind := cbth_rec.direct_to_borr_flag;
1032
1033 DECLARE
1034
1035 l_fund_status igf_aw_awd_disb_all.fund_status%TYPE;
1036 l_awd_disb_accep_amt igf_aw_awd_disb_all.disb_accepted_amt%TYPE;
1037 l_awd_fee_1 igf_aw_awd_disb_all.fee_1%TYPE;
1038 l_awd_fee_2 igf_aw_awd_disb_all.fee_2%TYPE;
1039 l_awd_net_disb_amt igf_aw_awd_disb_all.disb_net_amt%TYPE;
1040 l_awd_fee_paid_2 igf_aw_awd_disb_all.fee_paid_2%TYPE;
1041 l_awd_fee_paid_1 igf_aw_awd_disb_all.fee_paid_1%TYPE;
1042 l_award_id igf_aw_awd_disb_all.award_id%TYPE;
1043 l_auto_late_ind igf_sl_cl_setup_all.auto_late_disb_ind%TYPE;
1044 l_awd_direct_to_borr_ind igf_aw_awd_disb_all.direct_to_borr_flag%TYPE;
1045
1046 l_msg_str1 VARCHAR2(1000);
1047 l_msg_str2 VARCHAR2(4000);
1048 l_msg_str3 VARCHAR2(1000);
1049
1050 CURSOR cur_awdisb(l_loan_number igf_db_cl_disb_resp_all.loan_number%TYPE,
1051 l_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
1052 IS
1053 SELECT
1054 disb.*
1055 FROM igf_aw_awd_disb disb,igf_sl_loans_all loans
1056 WHERE
1057 NVL(loans.external_loan_id_txt, loans.loan_number) = l_loan_number AND
1058 disb.award_id = loans.award_id AND
1059 disb.disb_num = l_disb_num
1060 FOR UPDATE OF disb.fund_status NOWAIT;
1061
1062 disb_rec cur_awdisb%ROWTYPE;
1063
1064 CURSOR c_clset (p_award_id igf_aw_award_all.award_id%TYPE)
1065 IS
1066 SELECT clset.auto_late_disb_ind
1067 FROM igf_sl_cl_setup_all clset
1068 WHERE (ci_cal_type,ci_sequence_number,relationship_cd )
1069 IN
1070 (
1071 SELECT base.ci_cal_type,base.ci_sequence_number, lor.relationship_cd
1072 FROM igf_ap_fa_base_rec_all base, igf_aw_award_all awd,
1073 igf_sl_loans_all loans,igf_sl_lor_all lor
1074 WHERE base.base_id = awd.base_id
1075 AND awd.award_id = loans.award_id
1076 AND loans.loan_id = lor.loan_id
1077 AND awd.award_id = p_award_id
1078 );
1079
1080
1081 BEGIN
1082
1083 lv_rec_status := 'N';
1084 log_start(l_loan_number, l_disb_num);
1085 -- Condition 0 : If there are any errors returned in the File, then Display and skip
1086 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1087 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug','Start processing disb response rec 1 l_loan_number,l_disb_num ' || l_loan_number||' : ' || l_disb_num);
1088 END IF;
1089
1090 IF cbth_rec.err_code1 IS NOT NULL
1091 OR cbth_rec.err_code2 IS NOT NULL
1092 OR cbth_rec.err_code3 IS NOT NULL
1093 OR cbth_rec.err_code4 IS NOT NULL
1094 OR cbth_rec.err_code5 IS NOT NULL THEN
1095
1096 IF cbth_rec.err_code1 IS NOT NULL THEN
1097 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_SL_CL_ERROR',cbth_rec.err_code1));
1098 END IF;
1099 IF cbth_rec.err_code2 IS NOT NULL THEN
1100 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_SL_CL_ERROR',cbth_rec.err_code2));
1101 END IF;
1102 IF cbth_rec.err_code3 IS NOT NULL THEN
1103 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_SL_CL_ERROR',cbth_rec.err_code3));
1104 END IF;
1105 IF cbth_rec.err_code4 IS NOT NULL THEN
1106 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_SL_CL_ERROR',cbth_rec.err_code4));
1107 END IF;
1108 IF cbth_rec.err_code5 IS NOT NULL THEN
1109 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_SL_CL_ERROR',cbth_rec.err_code5));
1110 END IF;
1111 END IF;
1112
1113
1114 -- Condition 1 : Check if there is a Disb-rec for this Loan-Number and Disb-Num
1115 OPEN cur_awdisb(l_loan_number,l_disb_num);
1116 FETCH cur_awdisb INTO disb_rec;
1117 IF cur_awdisb%NOTFOUND THEN
1118 CLOSE cur_awdisb;
1119 fnd_message.set_name('IGF','IGF_SL_NO_AWD_DISB');
1120 -- No Records in Award-Disbursement Table.
1121 fnd_file.new_line(fnd_file.log,1);
1122 fnd_file.put_line(fnd_file.log,fnd_message.get);
1123 fnd_file.new_line(fnd_file.log,1);
1124 lv_rec_status := 'F';
1125 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1126 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug','skip flag TRUE ');
1127 END IF;
1128 lv_rec_status := 'F';
1129 RAISE SKIP_THIS_RECORD;
1130 END IF;
1131 IF cur_awdisb%FOUND THEN
1132 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1133 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug','loan disb found ');
1134 END IF;
1135 OPEN c_clset(disb_rec.award_id);
1136 FETCH c_clset INTO l_auto_late_ind;
1137 IF c_clset%NOTFOUND THEN
1138 CLOSE c_clset;
1139 fnd_message.set_name('IGF','IGF_SL_NO_CL_SETUP');
1140 fnd_file.put_line(fnd_file.log,fnd_message.get);
1141 -- No Records in CommonLine Setup Table.
1142 igs_ge_msg_stack.add;
1143 RAISE CLSETUP_NOT_FOUND;
1144 END IF;
1145 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1146 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug','loan setup found, l_auto_late_ind ' || l_auto_late_ind);
1147 END IF;
1148 CLOSE c_clset;
1149 END IF;
1150
1151 l_fund_status := disb_rec.fund_status;
1152 l_awd_disb_accep_amt:= disb_rec.disb_accepted_amt;
1153 l_awd_fee_1 := disb_rec.fee_1;
1154 l_awd_fee_2 := disb_rec.fee_2;
1155 l_awd_net_disb_amt := disb_rec.disb_net_amt;
1156 l_awd_fee_paid_1 := disb_rec.fee_paid_1;
1157 l_awd_fee_paid_2 := disb_rec.fee_paid_2;
1158 l_award_id := disb_rec.award_id;
1159 l_awd_direct_to_borr_ind := disb_rec.direct_to_borr_flag;
1160
1161 -- Condition 2 : Check if the Disbursement was already FUNDED.
1162 IF l_fund_status = 'Y' THEN
1163 -- If the fund status is already funded then skip this record
1164 -- No updates for igf_aw_awd_disb table
1165 IF l_rec_type = 'N' THEN
1166 fnd_message.set_name('IGF','IGF_DB_ROST_ALFND_NOUPD');
1167 fnd_file.put_line(fnd_file.log,fnd_message.get);
1168 fnd_file.new_line(fnd_file.log,1);
1169 RAISE SKIP_THIS_RECORD;
1170 ELSE
1171 lv_rec_status := 'A';
1172 fnd_message.set_name('IGF','IGF_DB_UPD_ROST_FUND');
1173 fnd_file.put_line(fnd_file.log,fnd_message.get);
1174 fnd_file.new_line(fnd_file.log,1);
1175 END IF;
1176 END IF;
1177
1178 -- Condition 3 : Check if the Amounts are Different.
1179 -- If the fund staus is not funded then
1180 -- 1.Compare the amounts in the Roster File Records
1181 -- with those of in igf_aw_awd_disb
1182 -- records for this particular disb_num
1183
1184 IF NVL(l_disb_gross_amt,0)<> NVL(l_awd_disb_accep_amt,0) OR
1185 NVL(l_fee_1,0) <> NVL(l_awd_fee_1,0) OR
1186 NVL(l_fee_2,0) <> NVL(l_awd_fee_2,0) OR
1187 NVL(l_net_disb_amt,0) <> NVL(l_awd_net_disb_amt,0) OR
1188 NVL(l_fee_paid_1,0) <> NVL(l_awd_fee_paid_1,0) OR
1189 NVL(l_fee_paid_2,0) <> NVL(l_awd_fee_paid_2,0) OR
1190 NVL(l_direct_to_borr_ind,'*') <> NVL(l_awd_direct_to_borr_ind,'*')
1191 THEN
1192
1193 l_msg_str2 := LPAD(' ',40) || RPAD(loc_disb_desc,40) ||RPAD(ofa_disb_desc,40) ||'
1194 '||
1195 LPAD('-',120,'-') ||'
1196 '||
1197 LPAD(disb_gross_amt_desc,30)|| LPAD(NVL(TO_CHAR(l_disb_gross_amt),' '),30)||LPAD(NVL(TO_CHAR(l_awd_disb_accep_amt),' '),30)||'
1198 '||
1199 LPAD(fee_1_desc,30) || LPAD(NVL(TO_CHAR(l_fee_1),' '),30) ||LPAD(NVL(TO_CHAR(l_awd_fee_1),' '),30) ||'
1200 '||
1201 LPAD(fee_2_desc,30) || LPAD(NVL(TO_CHAR(l_fee_2),' '),30) ||LPAD(NVL(TO_CHAR(l_awd_fee_2),' '),30) ||'
1202 '||
1203 LPAD(disb_net_amt_desc,30) || LPAD(NVL(TO_CHAR(l_net_disb_amt),' '),30) ||LPAD(NVL(TO_CHAR(l_awd_net_disb_amt),' '),30) ||'
1204 '||
1205 LPAD(fee_paid_1_desc,30) || LPAD(NVL(TO_CHAR(l_fee_paid_1),' '),30) ||LPAD(NVL(TO_CHAR(l_awd_fee_paid_1),' '),30) ||'
1206 '||
1207 LPAD(fee_paid_2_desc,30) || LPAD(NVL(TO_CHAR(l_fee_paid_2),' '),30) ||LPAD(NVL(TO_CHAR(l_awd_fee_paid_2),' '),30) ||'
1208 '||
1209 LPAD(direct_to_borr_ind_desc,30) || LPAD(NVL(l_direct_to_borr_ind,' '),30) ||LPAD(NVL(l_awd_direct_to_borr_ind,' '),30);
1210
1211 fnd_message.set_name('IGF','IGF_DB_INFO_DIFFER');
1212 --Amounts are different in file and table for this record
1213 fnd_file.put_line(fnd_file.log,fnd_message.get);
1214 fnd_file.put_line(fnd_file.log,l_msg_str2);
1215 fnd_file.new_line(fnd_file.log,1);
1216 IF p_update_disb = 'N' THEN
1217 lv_rec_status := 'D';
1218 fnd_message.set_name('IGF','IGF_DB_ROST_DIFF_NOUPD');
1219 fnd_file.put_line(fnd_file.log,fnd_message.get);
1220 fnd_file.new_line(fnd_file.log,1);
1221 RAISE SKIP_THIS_RECORD;
1222 ELSIF p_update_disb = 'Y' THEN
1223 lv_rec_status := 'U';
1224 fnd_message.set_name('IGF','IGF_DB_ROST_DIFF_YSUPD');
1225 fnd_file.put_line(fnd_file.log,fnd_message.get);
1226 fnd_file.new_line(fnd_file.log,1);
1227 disb_rec.fee_1 := l_fee_1;
1228 disb_rec.fee_2 := l_fee_2;
1229 disb_rec.disb_net_amt := l_net_disb_amt;
1230 disb_rec.disb_accepted_amt := l_disb_gross_amt;
1231 disb_rec.fee_paid_1 := l_fee_paid_1;
1232 disb_rec.fee_paid_2 := l_fee_paid_2;
1233 disb_rec.direct_to_borr_flag := l_direct_to_borr_ind;
1234 END IF;
1235 END IF;
1236 -- Condition 4 : Check if it is a Late Disbursement and Whether it should be auto Loaded ?
1237 -- If the fund staus is not funded then
1238 -- 2.a. If Auto Fund Late Disb is NO and
1239 -- Late Disb Ind Code is Y then
1240 -- show on Edit Report
1241 IF l_auto_late_ind = 'N' AND
1242 l_late_disb_ind = 'Y' THEN
1243
1244 l_msg_str1 := RPAD(' ' ,40) ||RPAD(loc_disb_desc,40) ||'
1245 '||
1246 LPAD(disb_gross_amt_desc,30)||LPAD(NVL(TO_CHAR(l_disb_gross_amt),' '),30)||'
1247 '||
1248 LPAD(fee_1_desc,30) ||LPAD(NVL(TO_CHAR(l_fee_1),' '),30) ||'
1249 '||
1250 LPAD(fee_2_desc,30) ||LPAD(NVL(TO_CHAR(l_fee_2),' '),30) ||'
1251 '||
1252 LPAD(disb_net_amt_desc,30) ||LPAD(NVL(TO_CHAR(l_net_disb_amt),' '),30) ||'
1253 '||
1254 LPAD(fee_paid_1_desc,30) ||LPAD(NVL(TO_CHAR(l_fee_paid_1),' '),30) ||'
1255 '||
1256 LPAD(fee_paid_2_desc,30) ||LPAD(NVL(TO_CHAR(l_fee_paid_2),' '),30) ||'
1257 '||
1258 LPAD(direct_to_borr_ind_desc,30) ||LPAD(NVL(l_direct_to_borr_ind,' '),30);
1259
1260 fnd_message.set_name('IGF','IGF_SL_LATE_DISB');
1261 -- Late disbursement set for this record.
1262 fnd_file.put_line(fnd_file.log,fnd_message.get);
1263 fnd_file.put_line(fnd_file.log,l_msg_str1);
1264 lv_rec_status := 'L';
1265 RAISE SKIP_THIS_RECORD;
1266 END IF;
1267
1268
1269 -- 2.b.Update igf_aw_awd_disb
1270 -- set the fund status = FUNDED
1271 IF lv_rec_status = 'N' THEN
1272 lv_rec_status := 'U';
1273 fnd_message.set_name('IGF','IGF_DB_UPD_ROST_FUND');
1274 fnd_file.put_line(fnd_file.log,fnd_message.get);
1275 fnd_file.new_line(fnd_file.log,1);
1276 END IF;
1277
1278 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1279 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_cl_roster.roster_ack.debug',
1280 'Updating aw disb , award_id, disb_num, RESP_status ' || disb_rec.award_id || ' : ' || disb_rec.disb_num || ' : ' || lv_rec_status);
1281 END IF;
1282
1283 igf_aw_awd_disb_pkg.update_row(
1284 x_mode => 'R',
1285 x_rowid => disb_rec.row_id,
1286 x_award_id => disb_rec.award_id,
1287 x_disb_num => disb_rec.disb_num,
1288 x_tp_cal_type => disb_rec.tp_cal_type,
1289 x_tp_sequence_number => disb_rec.tp_sequence_number,
1290 x_disb_gross_amt => disb_rec.disb_gross_amt,
1291 x_fee_1 => disb_rec.fee_1,
1292 x_fee_2 => disb_rec.fee_2,
1293 x_disb_net_amt => disb_rec.disb_net_amt,
1294 x_disb_date => disb_rec.disb_date,
1295 x_trans_type => disb_rec.trans_type,
1296 x_elig_status => disb_rec.elig_status,
1297 x_elig_status_date => disb_rec.elig_status_date,
1298 x_affirm_flag => disb_rec.affirm_flag,
1299 x_hold_rel_ind => disb_rec.hold_rel_ind,
1300 x_manual_hold_ind => disb_rec.manual_hold_ind,
1301 x_disb_status => disb_rec.disb_status,
1302 x_disb_status_date => disb_rec.disb_status_date,
1303 x_late_disb_ind => NVL(l_late_disb_ind, disb_rec.late_disb_ind),
1304 x_fund_dist_mthd => NVL(l_fund_dist_mthd, disb_rec.fund_dist_mthd),
1305 x_prev_reported_ind => NVL(l_prev_reported_ind, disb_rec.prev_reported_ind),
1306 x_fund_release_date => NVL(l_fund_release_date,disb_rec.fund_release_date),
1307 x_fund_status => 'Y',
1308 x_fund_status_date => TRUNC(SYSDATE),
1309 x_fee_paid_1 => disb_rec.fee_paid_1,
1310 x_fee_paid_2 => disb_rec.fee_paid_2,
1311 x_cheque_number => NVL(l_check_number,disb_rec.cheque_number),
1312 x_ld_cal_type => disb_rec.ld_cal_type,
1313 x_ld_sequence_number => disb_rec.ld_sequence_number,
1314 x_disb_accepted_amt => disb_rec.disb_accepted_amt,
1315 x_disb_paid_amt => disb_rec.disb_paid_amt,
1316 x_rvsn_id => disb_rec.rvsn_id,
1317 x_int_rebate_amt => disb_rec.int_rebate_amt,
1318 x_force_disb => disb_rec.force_disb,
1319 x_min_credit_pts => disb_rec.min_credit_pts,
1320 x_disb_exp_dt => disb_rec.disb_exp_dt,
1321 x_verf_enfr_dt => disb_rec.verf_enfr_dt,
1322 x_fee_class => disb_rec.fee_class,
1323 x_show_on_bill => disb_rec.show_on_bill,
1324 x_attendance_type_code => disb_rec.attendance_type_code,
1325 x_base_attendance_type_code => disb_rec.base_attendance_type_code,
1326 x_payment_prd_st_date => disb_rec.payment_prd_st_date,
1327 x_change_type_code => disb_rec.change_type_code,
1328 x_fund_return_mthd_code => disb_rec.fund_return_mthd_code,
1329 x_direct_to_borr_flag => disb_rec.direct_to_borr_flag
1330 );
1331
1332 IF cur_awdisb%ISOPEN THEN
1333 CLOSE cur_awdisb;
1334 END IF;
1335
1336 EXCEPTION
1337
1338 WHEN SKIP_THIS_RECORD THEN
1339 IF cur_awdisb%ISOPEN THEN
1340 CLOSE cur_awdisb;
1341 END IF;
1342 END;
1343
1344 -- set the status of igf_db_cl_disb_resp record_processed = 'Y'
1345 -- update all the records which are processed
1346
1347 igf_db_cl_disb_resp_pkg.update_row(
1348 x_mode => 'R',
1349 x_rowid => cbth_rec.row_id,
1350 x_cdbr_id => cbth_rec.cdbr_id,
1351 x_cbth_id => cbth_rec.cbth_id,
1352 x_record_type => cbth_rec.record_type,
1353 x_loan_number => cbth_rec.loan_number,
1354 x_cl_seq_number => cbth_rec.cl_seq_number,
1355 x_b_last_name => cbth_rec.b_last_name,
1356 x_b_first_name => cbth_rec.b_first_name,
1357 x_b_middle_name => cbth_rec.b_middle_name,
1358 x_b_ssn => cbth_rec.b_ssn,
1359 x_b_addr_line_1 => cbth_rec.b_addr_line_1,
1360 x_b_addr_line_2 => cbth_rec.b_addr_line_2,
1361 x_b_city => cbth_rec.b_city,
1362 x_b_state => cbth_rec.b_state,
1363 x_b_zip => cbth_rec.b_zip,
1364 x_b_zip_suffix => cbth_rec.b_zip_suffix,
1365 x_b_addr_chg_date => cbth_rec.b_addr_chg_date,
1366 x_eft_auth_code => cbth_rec.eft_auth_code,
1367 x_s_last_name => cbth_rec.s_last_name,
1368 x_s_first_name => cbth_rec.s_first_name,
1369 x_s_middle_initial => cbth_rec.s_middle_initial,
1370 x_s_ssn => cbth_rec.s_ssn,
1371 x_school_id => cbth_rec.school_id,
1372 x_school_use => cbth_rec.school_use,
1373 x_loan_per_start_date => cbth_rec.loan_per_start_date,
1374 x_loan_per_end_date => cbth_rec.loan_per_end_date,
1375 x_cl_loan_type => cbth_rec.cl_loan_type,
1376 x_alt_prog_type_code => cbth_rec.alt_prog_type_code,
1377 x_lender_id => cbth_rec.lender_id,
1378 x_lend_non_ed_brc_id => cbth_rec.lend_non_ed_brc_id,
1379 x_lender_use => cbth_rec.lender_use,
1380 x_borw_confirm_ind => cbth_rec.borw_confirm_ind,
1381 x_tot_sched_disb => cbth_rec.tot_sched_disb,
1382 x_fund_release_date => cbth_rec.fund_release_date,
1383 x_disb_num => cbth_rec.disb_num,
1384 x_guarantor_id => cbth_rec.guarantor_id,
1385 x_guarantor_use => cbth_rec.guarantor_use,
1386 x_guarantee_date => cbth_rec.guarantee_date,
1387 x_guarantee_amt => cbth_rec.guarantee_amt,
1388 x_gross_disb_amt => cbth_rec.gross_disb_amt,
1389 x_fee_1 => cbth_rec.fee_1,
1390 x_fee_2 => cbth_rec.fee_2,
1391 x_net_disb_amt => cbth_rec.net_disb_amt,
1392 x_fund_dist_mthd => cbth_rec.fund_dist_mthd,
1393 x_check_number => cbth_rec.check_number,
1394 x_late_disb_ind => cbth_rec.late_disb_ind,
1395 x_prev_reported_ind => cbth_rec.prev_reported_ind,
1396 x_err_code1 => cbth_rec.err_code1,
1397 x_err_code2 => cbth_rec.err_code2,
1398 x_err_code3 => cbth_rec.err_code3,
1399 x_err_code4 => cbth_rec.err_code4,
1400 x_err_code5 => cbth_rec.err_code5,
1401 x_fee_paid_2 => cbth_rec.fee_paid_2,
1402 x_lender_name => cbth_rec.lender_name,
1403 x_net_cancel_amt => cbth_rec.net_cancel_amt,
1404 x_duns_lender_id => cbth_rec.duns_lender_id,
1405 x_duns_guarnt_id => cbth_rec.duns_guarnt_id,
1406 x_hold_rel_ind => cbth_rec.hold_rel_ind,
1407 x_pnote_code => cbth_rec.pnote_code,
1408 x_pnote_status_date => cbth_rec.pnote_status_date,
1409 x_fee_paid_1 => cbth_rec.fee_paid_1,
1410 x_netted_cancel_amt => cbth_rec.netted_cancel_amt,
1411 x_outstd_cancel_amt => cbth_rec.outstd_cancel_amt,
1412 x_sch_non_ed_brc_id => cbth_rec.sch_non_ed_brc_id,
1413 x_status => lv_rec_status,
1414 x_esign_src_typ_cd => cbth_rec.esign_src_typ_cd,
1415 x_direct_to_borr_flag => cbth_rec.direct_to_borr_flag
1416 );
1417
1418 END LOOP;
1419
1420 COMMIT;
1421
1422 EXCEPTION
1423
1424 WHEN app_exception.record_lock_exception THEN
1425 ROLLBACK;
1426 retcode := 2;
1427 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
1428 igs_ge_msg_stack.conc_exception_hndl;
1429
1430 WHEN CLSETUP_NOT_FOUND THEN
1431 ROLLBACK;
1432 retcode := 2;
1433 errbuf := fnd_message.get_string('IGF','IGF_SL_NO_CL_SETUP');
1434 igs_ge_msg_stack.conc_exception_hndl;
1435
1436 WHEN FILE_NOT_LOADED THEN
1437 ROLLBACK;
1438 retcode := 2;
1439 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
1440 igs_ge_msg_stack.conc_exception_hndl;
1441
1442 WHEN OTHERS THEN
1443 ROLLBACK;
1444 retcode := 2;
1445 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1446 fnd_file.put_line(fnd_file.log,SQLERRM);
1447 igs_ge_msg_stack.conc_exception_hndl;
1448
1449 END roster_ack;
1450
1451 END igf_db_cl_roster;