1 PACKAGE BODY igf_db_dl_reconc AS
2 /* $Header: IGFDB07B.pls 120.3 2006/04/06 06:08:00 veramach noship $ */
3 /***************************************************************
4 Created By : adhawan
5 Date Created By : 22-jan-2002
6 Purpose : To load the
7 Known Limitations,Enhancements or Remarks:
8 Change History :2154941
9 Who When What
10 veramach 29-Jan-2004 bug 3408092 added 2004-2005 in dl_version checks
11 ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices.
12 added two new parameters school_type and p_school_code to
13 main_smr and main_dtl and removed school_id references
14 adhawan 22-jan-2002 Disbursements build
15 smvk 24-Feb-2003 Bug # 2758823. DL 03-04 Updates.
16 ***************************************************************/
17 INV_HDR_OR_TLR EXCEPTION;
18 INVALID_FILE EXCEPTION;
19 INV_TRL_NUM EXCEPTION;
20
21 FUNCTION valid_header ( p_c_file_type IN igf_sl_load_file_t.file_type%TYPE, p_c_batch_id OUT NOCOPY igf_sl_dl_batch.batch_id%TYPE,
22 p_c_message_class OUT NOCOPY igf_sl_dl_batch.message_class%TYPE, p_d_bth_creation_date OUT NOCOPY igf_sl_dl_batch.bth_creation_date%TYPE,
23 p_c_rej_code OUT NOCOPY igf_sl_dl_batch.batch_rej_code%TYPE, p_c_batch_type OUT NOCOPY igf_sl_dl_batch.batch_type%TYPE) RETURN BOOLEAN;
24
25 FUNCTION valid_trailer ( p_c_file_type IN igf_sl_load_file_t.file_type%TYPE, p_n_last_lort_id OUT NOCOPY NUMBER ,
26 p_n_rec_num OUT NOCOPY NUMBER, p_n_rec_accept OUT NOCOPY NUMBER,
27 p_n_rec_reject OUT NOCOPY NUMBER, p_n_rec_pending OUT NOCOPY NUMBER) RETURN BOOLEAN ;
28
29 FUNCTION valid_header ( p_c_file_type IN igf_sl_load_file_t.file_type%TYPE, p_c_batch_id OUT NOCOPY igf_sl_dl_batch.batch_id%TYPE,
30 p_c_message_class OUT NOCOPY igf_sl_dl_batch.message_class%TYPE, p_d_bth_creation_date OUT NOCOPY igf_sl_dl_batch.bth_creation_date%TYPE,
31 p_c_rej_code OUT NOCOPY igf_sl_dl_batch.batch_rej_code%TYPE, p_c_batch_type OUT NOCOPY igf_sl_dl_batch.batch_type%TYPE) RETURN BOOLEAN AS
32 /***************************************************************
33 Created By : smvk
34 Date Created By : 18-Feb-2003
35 Purpose : Returns TRUE if there exist a valid header for given file type. otherwise returns FALSE
36 Known Limitations,Enhancements or Remarks:
37 Change History :2154941
38 Who When What
39
40 ***************************************************************/
41 CURSOR c_header (cp_c_file_type IN igf_sl_load_file_t.file_type%TYPE) IS
42 SELECT RTRIM(LTRIM(SUBSTR(record_data, 15, 8))) message_class,
43 RTRIM(LTRIM(SUBSTR(record_data, 23, 2))) batch_type,
44 RTRIM(LTRIM(SUBSTR(record_data, 23, 23))) batch_id,
45 to_date(SUBSTR(record_data, 46, 16),'YYYYMMDDHH24MISS') bth_creation_date,
46 RTRIM(LTRIM(SUBSTR(record_data, 60, 2))) batch_rej_code
47
48 FROM igf_sl_load_file_t
49 WHERE lort_id = 1
50 AND record_data LIKE 'DL HEADER%'
51 AND file_type = cp_c_file_type;
52
53 rec_header c_header%ROWTYPE;
54
55 BEGIN
56 OPEN c_header (p_c_file_type);
57 FETCH c_header INTO rec_header;
58 IF c_header%FOUND THEN
59 p_c_batch_id := rec_header.batch_id;
60 p_c_message_class := rec_header.message_class;
61 p_d_bth_creation_date := rec_header.bth_creation_date;
62 p_c_rej_code := rec_header.batch_rej_code;
63 p_c_batch_type := rec_header.batch_type;
64 CLOSE c_header;
65 RETURN TRUE;
66 ELSE
67 CLOSE c_header;
68 RETURN FALSE;
69 END IF;
70 END valid_header;
71
72 FUNCTION valid_trailer ( p_c_file_type IN igf_sl_load_file_t.file_type%TYPE, p_n_last_lort_id OUT NOCOPY NUMBER ,
73 p_n_rec_num OUT NOCOPY NUMBER, p_n_rec_accept OUT NOCOPY NUMBER,
74 p_n_rec_reject OUT NOCOPY NUMBER, p_n_rec_pending OUT NOCOPY NUMBER) RETURN BOOLEAN AS
75
76 /***************************************************************
77 Created By : smvk
78 Date Created By : 18-Feb-2003
79 Purpose : Returns TRUE if there exist a valid trailer for given file type. otherwise returns FALSE
80 Known Limitations,Enhancements or Remarks:
81 Change History :2154941
82 Who When What
83
84 ***************************************************************/
85
86 CURSOR c_trailer (cp_c_file_type IN igf_sl_load_file_t.file_type%TYPE) IS
87 SELECT lort_id last_lort_id,
88 RTRIM(SUBSTR(record_data,15,7)) number_rec,
89 RTRIM(SUBSTR(record_data,22,5)) accept_rec,
90 RTRIM(SUBSTR(record_data,27,5)) reject_rec,
91 RTRIM(SUBSTR(record_data,32,5)) pending_rec
92 FROM igf_sl_load_file_t
93 WHERE lort_id = (SELECT MAX(lort_id) FROM igf_sl_load_file_t)
94 AND record_data LIKE 'DL TRAILER%'
95 AND file_type = cp_c_file_type;
96
97 rec_trailer c_trailer%ROWTYPE;
98
99 BEGIN
100 OPEN c_trailer (p_c_file_type);
101 FETCH c_trailer INTO rec_trailer;
102 IF c_trailer%FOUND THEN
103 p_n_last_lort_id := rec_trailer.last_lort_id;
104 p_n_rec_num := rec_trailer.number_rec;
105 p_n_rec_accept := rec_trailer.accept_rec;
106 p_n_rec_reject := rec_trailer.reject_rec;
107 p_n_rec_pending := rec_trailer.pending_rec;
108 CLOSE c_trailer;
109 RETURN TRUE;
110 ELSE
111 CLOSE c_trailer;
112 RETURN FALSE;
113 END IF;
114
115 END valid_trailer ;
116
117 PROCEDURE load_ytd_summary(p_school_code VARCHAR2, p_award_year igf_sl_dl_setup_v.ci_alternate_code%TYPE) AS
118 /***************************************************************
119 Created By : adhawan
120 Date Created By : 22-jan-2002
121 Purpose : To load the table igf_db_ytd_sum from igf_sl_load_file_t table
122 Known Limitations,Enhancements or Remarks:
123 Change History :2154941
124 Who When What
125 ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices.
126 changed first parameter to this procedure as passed to main_smr process
127 and replaced references of school_id of igf_sl_dl_setup table with newly
128 changed p_school_code w.r.t. FA 126
129 smvk 25-Feb-2003 Bug # 2758823. Validating Header and trailer. Done changes for 2003-2004 awary year.
130 adhawan 22-jan-2002 Disbursements build
131 ***************************************************************/
132
133 CURSOR c_batch IS
134 SELECT record_data FROM igf_sl_load_file_t ,igf_db_ytd_smr ln
135 WHERE file_type ='DL_YTDS' AND
136 SUBSTR(record_data,1,1)='Y' AND
137 SUBSTR(record_data,2,23) =ln.batch_id;
138 c_batch_rec c_batch%ROWTYPE;
139
140 CURSOR c_ytd_summary(cp_c_last_lort_id igf_sl_load_file_t.lort_id%TYPE) IS
141 SELECT record_data FROM
142 igf_sl_load_file_t
143 WHERE lort_id BETWEEN 2 AND (cp_c_last_lort_id-1)
144 AND file_type ='DL_YTDS'
145 AND SUBSTR(record_data,1,1)='Y'
146 AND SUBSTR(record_data,25,6)=p_school_code
147 ORDER BY lort_id ;
148 l_dl_ytds_rec igf_db_ytd_smr%ROWTYPE;
149
150 l_rowid VARCHAR2(30);
151 l_ytds_id igf_db_ytd_smr.ytds_id%TYPE;
152 l_counter NUMBER(20) := 0;
153
154 --Variables used to give the end result of the processing of the records
155
156 l_fetch_count NUMBER(20) := 0;
157 l_processed_count NUMBER(20) :=0;
158
159 -- Variables to hold the value derive from header
160 l_c_batch_id igf_sl_dl_batch.batch_id%TYPE;
161 l_c_message_class igf_sl_dl_batch.message_class%TYPE;
162 l_d_bth_creation_date igf_sl_dl_batch.bth_creation_date%TYPE;
163 l_c_rej_code igf_sl_dl_batch.batch_rej_code%TYPE;
164 l_c_batch_type igf_sl_dl_batch.batch_type%TYPE;
165
166 -- Variables to hold the value derive from trailer
167 l_n_last_lort_id NUMBER ;
168 l_n_rec_num NUMBER;
169 l_n_rec_accept NUMBER;
170 l_n_rec_reject NUMBER;
171 l_n_rec_pending NUMBER;
172
173 l_c_dl_version igf_sl_dl_file_type.dl_version%TYPE;
174 l_c_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
175 l_c_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
176
177 BEGIN
178
179 -- checking the header
180 IF NOT valid_header ( 'DL_YTDS', l_c_batch_id, l_c_message_class,
181 l_d_bth_creation_date, l_c_rej_code, l_c_batch_type) THEN
182 RAISE INV_HDR_OR_TLR;
183 END IF;
184
185 -- checking the trailer
186 IF NOT valid_trailer ( 'DL_YTDS', l_n_last_lort_id, l_n_rec_num,
187 l_n_rec_accept, l_n_rec_reject, l_n_rec_pending) THEN
188 RAISE INV_HDR_OR_TLR;
189 END IF;
190
191 -- get the DL version, DL loan catg and file_type
192 igf_sl_gen.get_dl_batch_details(l_c_message_class,l_c_batch_type,l_c_dl_version,l_c_dl_file_type,l_c_dl_loan_catg);
193 IF l_c_dl_file_type <> 'DL_YTDS' OR l_c_dl_loan_catg <> 'DL' THEN
194 RAISE INVALID_FILE;
195 END IF;
196
197
198 --To check whether the batch has been already loaded or not
199 OPEN c_batch ;
200 FETCH c_batch INTO c_batch_rec;
201 IF c_batch%FOUND THEN
202 CLOSE c_batch;
203 FND_MESSAGE.SET_NAME('IGF','IGF_GE_BATCH_ALREADY_LOADED');
204 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
205 RETURN;
206 END IF;
207 CLOSE c_batch;
208
209 -- Bug # 2758823. Removed the ref cursor c_count_ytd_rec1
210
211 /* Get the records from the temporary table. Split each column and
212 insert into the igf_db_ytd_smr_all table */
213
214 IF (l_n_last_lort_id-2) <> l_n_rec_num THEN
215 RAISE INV_TRL_NUM;
216 END IF;
217
218 FOR loadrec IN c_ytd_summary(l_n_last_lort_id) LOOP
219 BEGIN
220 IF l_c_dl_version IN ('2002-2003','2003-2004','2004-2005','2005-2006','2006-2007') THEN
221 l_dl_ytds_rec.dl_version :=l_c_dl_version ;
222 l_dl_ytds_rec.record_type :=SUBSTR(loadrec.record_data,1 ,1);
223 l_dl_ytds_rec.batch_id :=SUBSTR(loadrec.record_data, 2,23);
224 l_dl_ytds_rec.school_code :=SUBSTR(loadrec.record_data, 25,6);
225 l_dl_ytds_rec.region_code :=SUBSTR(loadrec.record_data,31,2);
226 l_dl_ytds_rec.state_code :=SUBSTR(loadrec.record_data,33,2);
227 l_dl_ytds_rec.stat_end_dt :=FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,35,8),'YYYYMMDD');
228 l_dl_ytds_rec.process_dt :=FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,43,8),'YYYYMMDD');
229 l_dl_ytds_rec.disb_smr_type :=SUBSTR(loadrec.record_data,51,2);
230 l_dl_ytds_rec.bkd_gross :=SUBSTR(loadrec.record_data,53,11);
231 l_dl_ytds_rec.bkd_fee :=SUBSTR(loadrec.record_data,64,11);
232 l_dl_ytds_rec.bkd_int_rebate :=SUBSTR(loadrec.record_data,75,11);
233 l_dl_ytds_rec.bkd_net :=SUBSTR(loadrec.record_data,86,11);
234 l_dl_ytds_rec.unbkd_gross :=SUBSTR(loadrec.record_data,97,11);
235 l_dl_ytds_rec.unbkd_fee :=SUBSTR(loadrec.record_data,108,11);
236 l_dl_ytds_rec.unbkd_int_rebate :=SUBSTR(loadrec.record_data,119,11);
237 l_dl_ytds_rec.unbkd_net :=SUBSTR(loadrec.record_data,130,11);
238 l_dl_ytds_rec.rec_count :=SUBSTR(loadrec.record_data,215,6);
239
240 l_rowid := NULL;
241 igf_db_ytd_smr_pkg.insert_row(
242 x_mode => 'R',
243 x_rowid => l_rowid,
244 x_ytds_id =>l_ytds_id,
245 x_dl_version =>l_dl_ytds_rec.dl_version ,
246 x_record_type =>l_dl_ytds_rec.record_type ,
247 x_batch_id =>l_dl_ytds_rec.batch_id ,
248 x_school_code =>l_dl_ytds_rec.school_code ,
249 x_stat_end_dt =>l_dl_ytds_rec.stat_end_dt ,
250 x_process_dt =>l_dl_ytds_rec.process_dt ,
251 x_disb_smr_type =>l_dl_ytds_rec.disb_smr_type ,
252 x_bkd_gross =>l_dl_ytds_rec.bkd_gross ,
253 x_bkd_fee =>l_dl_ytds_rec.bkd_fee ,
254 x_bkd_int_rebate =>l_dl_ytds_rec.bkd_int_rebate ,
255 x_bkd_net =>l_dl_ytds_rec.bkd_net ,
256 x_unbkd_gross =>l_dl_ytds_rec.unbkd_gross ,
257 x_unbkd_fee =>l_dl_ytds_rec.unbkd_fee ,
258 x_unbkd_int_rebate =>l_dl_ytds_rec.unbkd_int_rebate ,
259 x_unbkd_net =>l_dl_ytds_rec.unbkd_net ,
260 x_region_code =>l_dl_ytds_rec.region_code ,
261 x_state_code =>l_dl_ytds_rec.state_code ,
262 x_rec_count =>l_dl_ytds_rec.rec_count
263 );
264
265 l_processed_count := l_processed_count +1 ;
266 ELSE
267 fnd_message.set_name('IGF','IGF_SL_INVALID_RECORD');
268 fnd_message.set_token('BATCH_ID',SUBSTR(loadrec.record_data, 2,23));
269 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
270 END IF;
271 EXCEPTION
272 WHEN OTHERS THEN
273 fnd_message.set_name('IGF','IGF_SL_INVALID_RECORD');
274 fnd_message.set_token('BATCH_ID',SUBSTR(loadrec.record_data, 2,23));
275 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
276 END ;
277
278 END LOOP;
279
280 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_SUM_FET');
281 FND_MESSAGE.SET_TOKEN('VALUE',(l_n_last_lort_id-2));
282 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
283
284 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_SCHOOL_AWARD');
285 FND_MESSAGE.SET_TOKEN('VALUE',p_school_code);
286 FND_MESSAGE.SET_TOKEN('AWD_YR',p_award_year);
287 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
288
289 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_SUM_PRO');
290 FND_MESSAGE.SET_TOKEN('VALUE',l_processed_count);
291 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
292
293 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_SUM_REJ');
294 FND_MESSAGE.SET_TOKEN('VALUE',((l_n_last_lort_id-2) - l_processed_count));
295 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
296
297 EXCEPTION
298 WHEN INV_TRL_NUM THEN
299 RAISE;
300
301 WHEN INV_HDR_OR_TLR THEN
302 RAISE;
303
304 WHEN OTHERS THEN
305 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
306 FND_MESSAGE.SET_TOKEN('NAME','igf_db_dl_reconc.ytd_load_summary');
307 IGS_GE_MSG_STACK.ADD;
308 APP_EXCEPTION.RAISE_EXCEPTION;
309
310 END load_ytd_summary;
311
312 PROCEDURE load_ytd_detail(p_school_code VARCHAR2, p_award_year igf_sl_dl_setup_v.ci_alternate_code%TYPE, p_c_dl_version igf_sl_dl_setup_v.DL_VERSION%TYPE) AS
313 /***************************************************************
314 Created By : adhawan
315 Date Created By : 22-jan-2002
316 Purpose : To load igf_db_ytd_dtl from igf_sl_load_file_t
317 Known Limitations,Enhancements or Remarks:
318 Change History :2154941
319 Who When What
320 ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices.
321 changed first parameter to this procedure as passed to main_smr process
322 and replaced references of school_id of igf_sl_dl_setup table with newly
323 changed p_school_code w.r.t. FA 126
324 smvk 25-Feb-2003 Bug # 2758823. Validating Header and trailer. Done changes for 2003-2004 awary year.
325 adhawan 22-jan-2002 Disbursements build
326 ***************************************************************/
327
328 l_loaded_batch_id igf_db_ytd_dtl.batch_id%TYPE;
329 -- To ensure whether the batch is loaded , if yes then the records should not be loaded again
330 CURSOR c_batch IS
331 SELECT record_data FROM igf_sl_load_file_t ,igf_db_ytd_dtl ln
332 WHERE file_type ='DL_YTDD' AND
333 SUBSTR(record_data,1,1)='D' AND
334 SUBSTR(record_data,2,23) =ln.batch_id ;
335 c_batch_rec c_batch%ROWTYPE;
336
337
338 CURSOR c_ytd_detail (cp_c_last_lort_id igf_sl_load_file_t.lort_id%TYPE) IS
339 SELECT record_data FROM
340 igf_sl_load_file_t , igf_sl_loans_v sl
341 WHERE lort_id BETWEEN 2 AND (cp_c_last_lort_id-1)
342 AND file_type ='DL_YTDD'
343 AND SUBSTR(record_data,1,1)='D'
344 AND SUBSTR(record_data,25,6)=p_school_code -- To select only those records who have a school code passed as parameter
345 AND SUBSTR(record_data,51,21)=sl.loan_number--who have a valid loan number in the system(igf_sl_loans_v)
346 ORDER BY lort_id ;
347
348 l_dl_ytds_rec_det igf_db_ytd_dtl%ROWTYPE;
349 l_rowid VARCHAR2(30);
350 l_ytdd_id igf_db_ytd_dtl.ytdd_id%TYPE;
351 l_counter NUMBER(20) := 0;
352
353 --Variables used to give the end result of the processing of the records
354
355 l_fetch_count NUMBER(20) := 0;
356 l_processed_count NUMBER(20) :=0;
357
358 -- Variables to hold the value derive from header
359 l_c_batch_id igf_sl_dl_batch.batch_id%TYPE;
360 l_c_message_class igf_sl_dl_batch.message_class%TYPE;
361 l_d_bth_creation_date igf_sl_dl_batch.bth_creation_date%TYPE;
362 l_c_rej_code igf_sl_dl_batch.batch_rej_code%TYPE;
363 l_c_batch_type igf_sl_dl_batch.batch_type%TYPE;
364
365 -- Variables to hold the value derive from trailer
366 l_n_last_lort_id NUMBER ;
367 l_n_rec_num NUMBER;
368 l_n_rec_accept NUMBER;
369 l_n_rec_reject NUMBER;
370 l_n_rec_pending NUMBER;
371
372 l_c_dl_version igf_sl_dl_file_type.dl_version%TYPE;
373 l_c_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
374 l_c_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
375
376 BEGIN
377
378 -- checking the header
379 IF NOT valid_header ( 'DL_YTDD', l_c_batch_id, l_c_message_class,
380 l_d_bth_creation_date, l_c_rej_code, l_c_batch_type) THEN
381 RAISE INV_HDR_OR_TLR;
382 END IF;
383 -- checking the trailer
384 IF NOT valid_trailer ( 'DL_YTDD', l_n_last_lort_id, l_n_rec_num,
385 l_n_rec_accept, l_n_rec_reject, l_n_rec_pending) THEN
386 RAISE INV_HDR_OR_TLR;
387 END IF;
388
389 -- get the DL version, DL loan catg and file_type
390 igf_sl_gen.get_dl_batch_details(l_c_message_class,l_c_batch_type,l_c_dl_version,l_c_dl_file_type,l_c_dl_loan_catg);
391 IF l_c_dl_file_type <> 'DL_YTDD' OR l_c_dl_loan_catg <> 'DL' THEN
392 RAISE INVALID_FILE;
393 END IF;
394
395 /* Get the total no of records that would be processed */
396 --To check whether the batch has been already loaded or not
397 OPEN c_batch ;
398 FETCH c_batch INTO c_batch_rec;
399 IF c_batch%FOUND THEN
400 CLOSE c_batch;
401 FND_MESSAGE.SET_NAME('IGF','IGF_GE_BATCH_ALREADY_LOADED');
402 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
403 RETURN;
404 END IF;
405 CLOSE c_batch;
406
407 -- Bug # 2758823. Removed the ref cursor c_count_ytd_rec1
408
409 /* Get the records from the temporary table. Split each column and
410 insert into the igf_db_ytd_dtl table */
411
412 IF (l_n_last_lort_id-2) <> l_n_rec_num THEN
413 RAISE INV_TRL_NUM;
414 END IF;
415
416 FOR loadrec IN c_ytd_detail (l_n_last_lort_id) LOOP
417 BEGIN
418 IF l_c_dl_version IN ('2002-2003','2003-2004','2004-2005','2005-2006','2006-2007') THEN
419 l_dl_ytds_rec_det.dl_version := l_c_dl_version;
420 l_dl_ytds_rec_det.record_type := SUBSTR(loadrec.record_data,1,1);
421 l_dl_ytds_rec_det.batch_id := SUBSTR(loadrec.record_data,2,23);
422 l_dl_ytds_rec_det.school_code := SUBSTR(loadrec.record_data,25,6);
423 l_dl_ytds_rec_det.region_code := SUBSTR(loadrec.record_data,31,2);
424 l_dl_ytds_rec_det.state_code := SUBSTR(loadrec.record_data,33,2);
425 l_dl_ytds_rec_det.stat_end_dt := FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,35,8),'YYYYMMDD');
426 l_dl_ytds_rec_det.process_dt := FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,43,8),'YYYYMMDD');
427 l_dl_ytds_rec_det.loan_number := SUBSTR(loadrec.record_data,51,21);
428 l_dl_ytds_rec_det.loan_bkd_dt := FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,72,8),'YYYYMMDD');
429 l_dl_ytds_rec_det.disb_bkd_dt := FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,80,8),'YYYYMMDD');
430 l_dl_ytds_rec_det.disb_gross := LTRIM(RTRIM(SUBSTR(loadrec.record_data,88,5)));
431 l_dl_ytds_rec_det.disb_fee := LTRIM(RTRIM(SUBSTR(loadrec.record_data,93,5)));
432 l_dl_ytds_rec_det.disb_int_rebate := LTRIM(RTRIM(SUBSTR(loadrec.record_data,98,5)));
433 l_dl_ytds_rec_det.disb_net := LTRIM(RTRIM(SUBSTR(loadrec.record_data,103,5)));
434 l_dl_ytds_rec_det.disb_net_adj := LTRIM(RTRIM(SUBSTR(loadrec.record_data,108,6)));
435 l_dl_ytds_rec_det.disb_num := LTRIM(RTRIM(SUBSTR(loadrec.record_data,114,2)));
436 l_dl_ytds_rec_det.disb_seq_num := LTRIM(RTRIM(SUBSTR(loadrec.record_data,116,2)));
437 l_dl_ytds_rec_det.trans_type := LTRIM(RTRIM(SUBSTR(loadrec.record_data,118,1)));
438 l_dl_ytds_rec_det.trans_dt := FND_DATE.STRING_TO_DATE(SUBSTR(loadrec.record_data,119,8),'YYYYMMDD');
439 l_dl_ytds_rec_det.total_gross := LTRIM(RTRIM(SUBSTR(loadrec.record_data,127,5)));
440 l_dl_ytds_rec_det.total_fee := LTRIM(RTRIM(SUBSTR(loadrec.record_data,132,5)));
441 l_dl_ytds_rec_det.total_int_rebate := LTRIM(RTRIM(SUBSTR(loadrec.record_data,137,5)));
442 l_dl_ytds_rec_det.total_net := LTRIM(RTRIM(SUBSTR(loadrec.record_data,142,5)));
443 l_dl_ytds_rec_det.rec_count := LTRIM(RTRIM(SUBSTR(loadrec.record_data,215,11)));
444
445 l_rowid := NULL;
446
447 igf_db_ytd_dtl_pkg.insert_row(
448 x_mode => 'R',
449 x_rowid => l_rowid,
450 x_ytdd_id => l_ytdd_id,
451 x_dl_version => l_dl_ytds_rec_det.dl_version ,
452 x_record_type => l_dl_ytds_rec_det.record_type ,
453 x_batch_id => l_dl_ytds_rec_det.batch_id ,
454 x_school_code => l_dl_ytds_rec_det.school_code ,
455 x_stat_end_dt => l_dl_ytds_rec_det.stat_end_dt ,
456 x_process_dt => l_dl_ytds_rec_det.process_dt ,
457 x_loan_number => l_dl_ytds_rec_det.loan_number ,
458 x_loan_bkd_dt => l_dl_ytds_rec_det.loan_bkd_dt ,
459 x_disb_bkd_dt => l_dl_ytds_rec_det.disb_bkd_dt ,
460 x_disb_gross => l_dl_ytds_rec_det.disb_gross ,
461 x_disb_fee => l_dl_ytds_rec_det.disb_fee ,
462 x_disb_int_rebate => l_dl_ytds_rec_det.disb_int_rebate ,
463 x_disb_net => l_dl_ytds_rec_det.disb_net ,
464 x_disb_net_adj => l_dl_ytds_rec_det.disb_net_adj ,
465 x_disb_num => l_dl_ytds_rec_det.disb_num ,
466 x_disb_seq_num => l_dl_ytds_rec_det.disb_seq_num ,
467 x_trans_type => l_dl_ytds_rec_det.trans_type ,
468 x_trans_dt => l_dl_ytds_rec_det.trans_dt ,
469 x_total_gross => l_dl_ytds_rec_det.total_gross ,
470 x_total_fee => l_dl_ytds_rec_det.total_fee ,
471 x_total_int_rebate => l_dl_ytds_rec_det.total_int_rebate ,
472 x_total_net => l_dl_ytds_rec_det.total_net ,
473 x_region_code => l_dl_ytds_rec_det.region_code ,
474 x_state_code => l_dl_ytds_rec_det.state_code ,
475 x_rec_count => l_dl_ytds_rec_det.rec_count
476 );
477
478 l_processed_count := l_processed_count +1;
479 ELSE
480 fnd_message.set_name('IGF','IGF_SL_INVALID_RECORD');
481 fnd_message.set_token('BATCH_ID',SUBSTR(loadrec.record_data, 2,23));
482 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
483 END IF;
484
485 EXCEPTION
486 WHEN INV_TRL_NUM THEN
487 RAISE;
488 WHEN OTHERS THEN
489 fnd_message.set_name('IGF','IGF_SL_INVALID_RECORD');
490 fnd_message.set_token('BATCH_ID',SUBSTR(loadrec.record_data, 2,23));
491 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
492 END ;
493
494 END LOOP;
495
496 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_DET_FET');
497 FND_MESSAGE.SET_TOKEN('VALUE',(l_n_last_lort_id-2));
498 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
499
500 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_SCHOOL_AWD_DTL');
501 FND_MESSAGE.SET_TOKEN('VALUE',p_school_code);
502 FND_MESSAGE.SET_TOKEN('AWD_YR',p_award_year);
503 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
504
505 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_DET_PRO');
506 FND_MESSAGE.SET_TOKEN('VALUE',l_processed_count);
507 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
508
509 FND_MESSAGE.SET_NAME('IGF','IGF_DB_YTD_DET_REJ');
510 FND_MESSAGE.SET_TOKEN('VALUE',((l_n_last_lort_id-2) - l_processed_count));
511 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
512
513 EXCEPTION
514
515 WHEN INV_TRL_NUM THEN
516 RAISE;
517 WHEN INVALID_FILE THEN
518 RAISE;
519 WHEN INV_HDR_OR_TLR THEN
520 RAISE;
521 WHEN OTHERS THEN
522 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
523 FND_MESSAGE.SET_TOKEN('NAME','igf_db_dl_reconc.ytd_load_detail');
524 IGS_GE_MSG_STACK.ADD;
525 APP_EXCEPTION.RAISE_EXCEPTION;
526
527 END load_ytd_detail;
528
529
530 PROCEDURE main_smr (ERRBUF OUT NOCOPY VARCHAR2,
531 RETCODE OUT NOCOPY NUMBER,
532 p_award_year IN VARCHAR2,
533 SCHOOL_TYPE IN VARCHAR2,
534 P_SCHOOL_CODE IN VARCHAR2
535 ) IS
536 /***************************************************************
537 Created By : adhawan
538 Date Created By : 22-jan-2002
539 Purpose : To load the
540 Known Limitations,Enhancements or Remarks:
541 Change History :
542 Who When What
543 tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
544 ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices.
545 added two new parameters school_type and p_school_code and
546 removed school_id as it is being obsoleted from igf_sl_dl_setup_all table.
547 smvk 25-Feb-2003 Bug # 2758823. Removed the award year checking 2002-2003 and displaying the error message "IGF_DB_DL_VERSION_FALSE"
548 ***************************************************************/
549 l_ci_cal_type igf_sl_dl_setup.ci_cal_type%TYPE;
550 l_ci_sequence_number igf_sl_dl_setup.ci_sequence_number%TYPE;
551
552
553 CURSOR c_get_ver IS
554 SELECT dl_version , ci_alternate_code FROM
555 igf_sl_dl_setup_v
556 WHERE
557 ci_cal_type =l_ci_cal_type AND
558 ci_sequence_number =l_ci_sequence_number ;
559 c_get_ver_rec c_get_ver%ROWTYPE ;
560
561 l_award_year igf_sl_dl_setup_v.ci_alternate_code%TYPE;
562
563 BEGIN
564 igf_aw_gen.set_org_id(NULL);
565 retcode :=0;
566 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10))) ;
567 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11)) ;
568
569
570 OPEN c_get_ver ;
571 FETCH c_get_ver INTO c_get_ver_rec;
572 --Checking if the data setup for the version is there or not
573 IF c_get_ver%NOTFOUND THEN
574 CLOSE c_get_ver;
575 FND_MESSAGE.SET_NAME('IGF','IGF_DB_DL_VERSION_FALSE');
576 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
577 RETURN;
578 ELSE
579 l_award_year:=c_get_ver_rec.ci_alternate_code;
580
581 END IF;
582 CLOSE c_get_ver;
583
584 -- Calling the procedure to load the data from igf_sl_load_file_t to igf_db_ytd_smr table
585 load_ytd_summary(P_SCHOOL_CODE, l_award_year);
586
587 EXCEPTION
588 WHEN INV_TRL_NUM THEN
589 ROLLBACK;
590 retcode := 2;
591 errbuf := fnd_message.get_string('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
592
593 WHEN INVALID_FILE THEN
594 ROLLBACK;
595 retcode := 2;
596 errbuf := fnd_message.get_string('IGF','IGF_GE_INVALID_FILE');
597
598 WHEN INV_HDR_OR_TLR THEN
599 ROLLBACK;
600 retcode := 2;
601 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_COMPLETE');
602
603 WHEN app_exception.record_lock_exception THEN
604 ROLLBACK;
605 retcode := 2;
606 errbuf := FND_MESSAGE.GET_STRING('IGF','IGF_GE_LOCK_ERROR');
607 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
608
609 WHEN OTHERS THEN
610 ROLLBACK;
611 retcode := 2;
612 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
613 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
614
615 END main_smr;
616
617 PROCEDURE main_dtl (ERRBUF OUT NOCOPY VARCHAR2,
618 RETCODE OUT NOCOPY NUMBER,
619 p_award_year IN VARCHAR2,
620 SCHOOL_TYPE IN VARCHAR2,
621 P_SCHOOL_CODE IN VARCHAR2
622 ) IS
623 /***************************************************************
624 Created By : adhawan
625 Date Created By : 22-jan-2002
626 Purpose : To load the
627 Known Limitations,Enhancements or Remarks:
628 Change History :
629 Who When What
630 tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
631 ugummall 15-OCT-2003 Bug # 3102439. FA 126 Multiple FA Offices.
632 added two new parameters school_type and p_school_code and
633 removed school_id as it is being obsoleted from igf_sl_dl_setup_all table.
634 smvk 25-Feb-2003 Bug # 2758823. Removed the award year checking 2002-2003 and displaying the error message "IGF_DB_DL_VERSION_FALSE"
635 ***************************************************************/
636 l_ci_cal_type igf_sl_dl_setup.ci_cal_type%TYPE;
637 l_ci_sequence_number igf_sl_dl_setup.ci_sequence_number%TYPE;
638
639
640
641 CURSOR c_get_ver IS
642 SELECT dl_version , ci_alternate_code FROM
643 igf_sl_dl_setup_v
644 WHERE
645 ci_cal_type =l_ci_cal_type AND
646 ci_sequence_number =l_ci_sequence_number ;
647
648 c_get_ver_rec c_get_ver%ROWTYPE ;
649
650
651 l_award_year igf_sl_dl_setup_v.ci_alternate_code%TYPE;
652
653 BEGIN
654 igf_aw_gen.set_org_id(NULL);
655 retcode :=0;
656 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10))) ;
657 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11)) ;
658
659
660
661 OPEN c_get_ver ;
662 FETCH c_get_ver INTO c_get_ver_rec;
663 --Checking if the data setup for the version is there or not
664 IF c_get_ver%NOTFOUND THEN
665 CLOSE c_get_ver;
666 FND_MESSAGE.SET_NAME('IGF','IGF_DB_DL_VERSION_FALSE');
667 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
668 RETURN;
669 ELSE
670 l_award_year :=c_get_ver_rec.ci_alternate_code;
671
672 END IF;
673
674 CLOSE c_get_ver;
675
676 -- Calling the procedure to load the data from igf_sl_load_file_t to igf_db_ytd_dtl table
677 load_ytd_detail(P_SCHOOL_CODE, l_award_year, c_get_ver_rec.dl_version);
678
679 EXCEPTION
680 WHEN INV_TRL_NUM THEN
681 ROLLBACK;
682 retcode := 2;
683 errbuf := fnd_message.get_string('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
684
685 WHEN INVALID_FILE THEN
686 ROLLBACK;
687 retcode := 2;
688 errbuf := fnd_message.get_string('IGF','IGF_GE_INVALID_FILE');
689
690 WHEN INV_HDR_OR_TLR THEN
691 ROLLBACK;
692 retcode := 2;
693 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_COMPLETE');
694
695 WHEN app_exception.record_lock_exception THEN
696 ROLLBACK;
697 retcode := 2;
698 errbuf := FND_MESSAGE.GET_STRING('IGF','IGF_GE_LOCK_ERROR');
699 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
700
701 WHEN OTHERS THEN
702 ROLLBACK;
703 retcode := 2;
704 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
705 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
706
707 END main_dtl;
708
709 END igf_db_dl_reconc;
[Home] [Help]