[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_MRR_LOAD_DATA
Source
1 PACKAGE BODY igf_gr_mrr_load_data AS
2 /* $Header: IGFGR05B.pls 120.2 2006/04/06 06:10:12 veramach ship $ */
3
4 /***************************************************************
5 Created By : avenkatr
6 Date Created By : 2000/12/20
7 Purpose : To upload data into the IGF_GR_MRR table
8
9 Known Limitations,Enhancements or Remarks
10 Change History :
11 Who When What
12 veramach 29-Jan-2004 Bug 3408092 Added 2004-2005 in g_ver_num checks
13 smvk 11-Feb-2003 Modified the procedure load_ack and split_into_fields.
14 2216956 13-02-2002 Added the field current_ssn in the tbh call and in the split_into_fields procedure
15 ***************************************************************/
16
17
18 param_error EXCEPTION;
19 invalid_version EXCEPTION; -- Thrown if the award year doesn't matches with that on the flat file.
20
21 g_ver_num VARCHAR2(30) DEFAULT NULL; -- Flat File Version Number
22 g_c_alt_code VARCHAR2(80) DEFAULT NULL; -- To hold alternate code.
23
24 PROCEDURE split_into_fields ( p_record_data IN igf_gr_load_file_t.record_data%TYPE,
25 p_igf_gr_mrr_row OUT NOCOPY igf_gr_mrr%ROWTYPE )
26 AS
27 /***************************************************************
28 Created By : avenkatr
29 Date Created By : 2000/12/20
30 Purpose : To split data in the single record_data column of igf_gr_load_file_t
31 into the different columns of igf_gr_mrr table
32
33 Known Limitations,Enhancements or Remarks
34 Change History :
35 Who When What
36 smvk 11-Feb-2003 Bug # 2758812. Added '2003-2004' in g_ver_num checking.
37 ***************************************************************/
38
39 BEGIN
40
41 IF g_ver_num IN ('2002-2003', '2003-2004','2004-2005','2005-2006','2006-2007') THEN
42
43 BEGIN
44 p_igf_gr_mrr_row.record_type := SUBSTR( p_record_data, 1, 2);
45 p_igf_gr_mrr_row.req_inst_pell_id := SUBSTR( p_record_data, 3, 6);
46 p_igf_gr_mrr_row.mrr_code1 := SUBSTR( p_record_data, 9, 1);
47 p_igf_gr_mrr_row.mrr_code2 := SUBSTR( p_record_data, 10, 1);
48 p_igf_gr_mrr_row.mr_stud_id := SUBSTR( p_record_data, 11, 11);
49 p_igf_gr_mrr_row.mr_inst_pell_id := SUBSTR( p_record_data, 22, 6);
50 p_igf_gr_mrr_row.stud_orig_ssn := SUBSTR( p_record_data, 28, 9);
51 p_igf_gr_mrr_row.orig_name_cd := SUBSTR( p_record_data, 37, 2);
52 p_igf_gr_mrr_row.inst_pell_id := SUBSTR( p_record_data, 39, 6);
53 p_igf_gr_mrr_row.inst_name := SUBSTR( p_record_data, 45, 70);
54 p_igf_gr_mrr_row.inst_addr1 := SUBSTR( p_record_data, 115, 35);
55 p_igf_gr_mrr_row.inst_addr2 := SUBSTR( p_record_data, 150, 35);
56 p_igf_gr_mrr_row.inst_city := SUBSTR( p_record_data, 185, 25);
57 p_igf_gr_mrr_row.inst_state := SUBSTR( p_record_data, 210, 2);
58 p_igf_gr_mrr_row.zip_code := SUBSTR( p_record_data, 212, 9);
59 p_igf_gr_mrr_row.faa_name := SUBSTR( p_record_data, 221, 30);
60 p_igf_gr_mrr_row.faa_tel := SUBSTR( p_record_data, 251, 10);
61 p_igf_gr_mrr_row.faa_fax := SUBSTR( p_record_data, 261, 10);
62 p_igf_gr_mrr_row.faa_internet_addr := SUBSTR( p_record_data, 271, 50);
63 p_igf_gr_mrr_row.schd_pell_grant := TO_NUMBER(SUBSTR( p_record_data, 321, 7))/100;
64 p_igf_gr_mrr_row.orig_awd_amt := TO_NUMBER(SUBSTR( p_record_data, 328, 7))/100;
65 p_igf_gr_mrr_row.tran_num := SUBSTR( p_record_data, 335, 2);
66 p_igf_gr_mrr_row.efc := TO_NUMBER(SUBSTR( p_record_data, 337, 5));
67 p_igf_gr_mrr_row.enrl_dt := FND_DATE.STRING_TO_DATE(SUBSTR( p_record_data, 342, 8), 'YYYYMMDD');
68 p_igf_gr_mrr_row.orig_creation_dt := FND_DATE.STRING_TO_DATE(SUBSTR( p_record_data, 350, 8), 'YYYYMMDD');
69 p_igf_gr_mrr_row.disb_accepted_amt := TO_NUMBER(SUBSTR( p_record_data, 358, 7))/100;
70 p_igf_gr_mrr_row.last_active_dt := FND_DATE.STRING_TO_DATE(SUBSTR( p_record_data, 365, 8), 'YYYYMMDD');
71 p_igf_gr_mrr_row.next_est_disb_dt := FND_DATE.STRING_TO_DATE(SUBSTR( p_record_data, 373, 8), 'YYYYMMDD');
72 p_igf_gr_mrr_row.eligibility_used := TO_NUMBER(SUBSTR( p_record_data, 381, 5));
73 p_igf_gr_mrr_row.ed_use_flags := SUBSTR( p_record_data, 386, 10);
74 p_igf_gr_mrr_row.stud_last_name := SUBSTR( p_record_data, 396, 16);
75 p_igf_gr_mrr_row.stud_first_name := SUBSTR( p_record_data, 412, 12);
76 p_igf_gr_mrr_row.stud_middle_name := SUBSTR( p_record_data, 424, 1);
77 p_igf_gr_mrr_row.stud_dob := FND_DATE.STRING_TO_DATE(SUBSTR( p_record_data, 425, 8), 'YYYYMMDD');
78 p_igf_gr_mrr_row.current_ssn := SUBSTR(p_record_data,433,9);
79
80 EXCEPTION
81 WHEN OTHERS THEN -- Number / Date format exception
82 RAISE igf_gr_gen.skip_this_record;
83
84 END;
85
86 ELSE
87 RAISE igf_gr_gen.no_file_version;
88 END IF;
89
90 EXCEPTION
91
92 WHEN igf_gr_gen.skip_this_record THEN
93 RAISE;
94
95 WHEN igf_gr_gen.no_file_version THEN
96 RAISE;
97
98 WHEN OTHERS THEN
99 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
100 fnd_message.set_token('NAME','igf_gr_mrr_load_data.split_into_fields');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103
104 END split_into_fields;
105
106
107 PROCEDURE insert_in_mrr_table ( p_mrr_rec IN igf_gr_mrr%ROWTYPE )
108 AS
109 /***************************************************************
110 Created By : avenkatr
111 Date Created By : 2000/12/19
112 Purpose : To Load data into IGF_GR_MRR table
113
114 Known Limitations,Enhancements or Remarks
115 Change History :
116 Who When What
117 ***************************************************************/
118 lv_rowid VARCHAR2(25);
119 lv_mrr_id NUMBER;
120
121 BEGIN
122 /* Call the table handler of the table igf_gr_mrr to insert data */
123 igf_gr_mrr_pkg.insert_row (
124 x_rowid => lv_rowid,
125 x_mrr_id => lv_mrr_id,
126 x_record_type => p_mrr_rec.record_type,
127 x_req_inst_pell_id => p_mrr_rec.req_inst_pell_id,
128 x_mrr_code1 => p_mrr_rec.mrr_code1,
129 x_mrr_code2 => p_mrr_rec.mrr_code2,
130 x_mr_stud_id => p_mrr_rec.mr_stud_id,
131 x_mr_inst_pell_id => p_mrr_rec.mr_inst_pell_id,
132 x_stud_orig_ssn => p_mrr_rec.stud_orig_ssn,
133 x_orig_name_cd => p_mrr_rec.orig_name_cd,
134 x_inst_pell_id => p_mrr_rec.inst_pell_id,
135 x_inst_name => p_mrr_rec.inst_name,
136 x_inst_addr1 => p_mrr_rec.inst_addr1,
137 x_inst_addr2 => p_mrr_rec.inst_addr2,
138 x_inst_city => p_mrr_rec.inst_city,
139 x_inst_state => p_mrr_rec.inst_state,
140 x_zip_code => p_mrr_rec.zip_code,
141 x_faa_name => p_mrr_rec.faa_name,
142 x_faa_tel => p_mrr_rec.faa_tel,
143 x_faa_fax => p_mrr_rec.faa_fax,
144 x_faa_internet_addr => p_mrr_rec.faa_internet_addr,
145 x_schd_pell_grant => p_mrr_rec.schd_pell_grant,
146 x_orig_awd_amt => p_mrr_rec.orig_awd_amt,
147 x_tran_num => p_mrr_rec.tran_num,
148 x_efc => p_mrr_rec.efc ,
149 x_enrl_dt => p_mrr_rec.enrl_dt,
150 x_orig_creation_dt => p_mrr_rec.orig_creation_dt,
151 x_disb_accepted_amt => p_mrr_rec.disb_accepted_amt,
152 x_last_active_dt => p_mrr_rec.last_active_dt,
153 x_next_est_disb_dt => p_mrr_rec.next_est_disb_dt,
154 x_eligibility_used => p_mrr_rec.eligibility_used,
155 x_ed_use_flags => p_mrr_rec.ed_use_flags,
156 x_stud_last_name => p_mrr_rec.stud_last_name,
157 x_stud_first_name => p_mrr_rec.stud_first_name,
158 x_stud_middle_name => p_mrr_rec.stud_middle_name,
159 x_stud_dob => p_mrr_rec.stud_dob,
160 x_current_ssn => p_mrr_rec.current_ssn,
161 x_mode => 'R'
162 ) ;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
167 fnd_message.set_token('NAME','igf_gr_mrr_load_data.insert_in_mrr_table');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END insert_in_mrr_table;
171
172
173 PROCEDURE load_ack
174 IS
175 /***************************************************************
176 Created By :
177 Date Created By :
178 Purpose :
179
180 Known Limitations,Enhancements or Remarks
181 Change History :
182 Who When What
183 smvk 11-Feb-2003 Bug # 2758812. Added the code to check version mismatch and
184 validate the number of records mentioned in the trailer record.
185 ***************************************************************/
186
187 l_last_gldr_id NUMBER;
188 l_number_rec NUMBER;
189 l_batch_id VARCHAR2(100);
190 lp_count NUMBER DEFAULT 0;
191 lf_count NUMBER DEFAULT 0;
192
193 l_c_message VARCHAR2(30); -- Local variable to hold message
194
195 BEGIN
196
197
198 igf_gr_gen.process_pell_ack ( g_ver_num,
199 'MRR',
200 l_number_rec,
201 l_last_gldr_id,
202 l_batch_id);
203
204 -- Check the award year matches with the award year in PELL setup.
205 igf_gr_gen.match_file_version (g_ver_num, l_batch_id, l_c_message);
206 IF l_c_message = 'IGF_GR_VRSN_MISMTCH' THEN
207 fnd_message.set_name ('IGF',l_c_message);
208 fnd_message.set_token('CYCL',substr(l_batch_id,3,4));
209 fnd_message.set_token('BATCH',l_batch_id);
210 fnd_message.set_token('AWD_YR',g_c_alt_code);
211 fnd_message.set_token('VRSN',g_ver_num);
212 fnd_file.put_line(fnd_file.log,fnd_message.get);
213 RAISE invalid_version;
214 END IF;
215
216 IF l_number_rec > 0 THEN
217
218 DECLARE
219
220 CURSOR c_mrr_data
221 IS
222 SELECT
223 record_data
224 FROM
225 igf_gr_load_file_t
226 WHERE
227 gldr_id BETWEEN 2 AND (l_last_gldr_id - 1)
228 AND
229 file_type = 'MRR'
230 ORDER BY
231 gldr_id;
232
233 mrr_rec_data c_mrr_data%ROWTYPE;
234 lv_mrr_row igf_gr_mrr%ROWTYPE;
235
236 BEGIN
237 --
238 -- Check for the type of data in the Flat File
239 --
240 FOR mrr_rec_data IN c_mrr_data LOOP
241
242 IF ( (SUBSTR(mrr_rec_data.record_data, 1, 1)) = 'O' ) THEN
243 --
244 -- This file has Origination records and has to be uploaded in igf_gr_ytd_orig table
245 --
246 BEGIN
247 --
248 -- Split the data in the column of igf_gr_load_file_t into the columns of igf_gr_ytd_orig file
249 --
250 split_into_fields (mrr_rec_data.record_data,
251 lv_mrr_row);
252
253 --
254 -- Insert this new record into the igf_gr_ytd_orig table
255 --
256 insert_in_mrr_table (lv_mrr_row );
257 --
258 -- Make an entry in the log file indicating Success
259 --
260 fnd_message.set_name('IGF','IGF_GR_MRR_LOAD_PASS');
261 fnd_message.set_token('STUD_ORIG_SSN',lv_mrr_row.stud_orig_ssn);
262 fnd_file.put_line(fnd_file.log,fnd_message.get());
263 lp_count := lp_count + 1;
264
265 EXCEPTION
266
267 WHEN igf_gr_gen.skip_this_record THEN
268 fnd_message.set_name('IGF','IGF_GR_MRR_LOAD_FAIL');
269 fnd_message.set_token('STUD_ORIG_SSN',SUBSTR( mrr_rec_data.record_data, 28, 9));
270 fnd_file.put_line(fnd_file.log,fnd_message.get());
271 lf_count := lf_count + 1;
272 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
273 fnd_file.put_line(fnd_file.log,fnd_message.get);
274
275 WHEN igf_gr_gen.no_file_version THEN
276 RAISE;
277
278 END ;
279
280 ELSE
281 lf_count := lf_count + 1;
282 END IF;
283
284 END LOOP;
285
286 EXCEPTION
287
288 WHEN igf_gr_gen.no_file_version THEN
289 RAISE;
290
291 END;
292
293 END IF;
294
295 IF l_number_rec <> (lp_count + lf_count) THEN
296 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
297 fnd_file.put_line(fnd_file.log,fnd_message.get);
298 RAISE igf_gr_gen.file_not_loaded;
299 END IF;
300
301 fnd_message.set_name('IGF','IGF_GR_FILE_REC_CNT');
302 fnd_message.set_token('CNT',l_number_rec);
303 fnd_file.put_line(fnd_file.log,fnd_message.get);
304
305 fnd_message.set_name('IGF','IGF_GR_FILE_REC_PAS');
306 fnd_message.set_token('CNT',lp_count);
307 fnd_file.put_line(fnd_file.log,fnd_message.get);
308
309 fnd_message.set_name('IGF','IGF_GR_FILE_REC_FAL');
310 fnd_message.set_token('CNT',lf_count);
311 fnd_file.put_line(fnd_file.log,fnd_message.get);
312
313 EXCEPTION
314 WHEN invalid_version THEN
315 RAISE;
316 WHEN igf_gr_gen.no_file_version THEN
317 RAISE;
318 WHEN igf_gr_gen.corrupt_data_file THEN
319 RAISE;
320 WHEN igf_gr_gen.file_not_loaded THEN
321 RAISE;
322
323 WHEN OTHERS THEN
324 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
325 fnd_message.set_token('NAME','igf_gr_mrr_load_data.load_ack');
326 igs_ge_msg_stack.add;
327 app_exception.raise_exception;
328
329 END load_ack;
330
331
332 --
333 -- MAIN PROCEDURE
334 --
335
336 PROCEDURE mrr_load_file(
337 errbuf OUT NOCOPY VARCHAR2,
338 retcode OUT NOCOPY NUMBER,
339 p_awd_yr IN VARCHAR2,
340 p_org_id IN NUMBER
341 )
342 AS
343 /***************************************************************
344 Created By : avenkatr
345 Date Created By : 2000/12/19
346 Purpose : To Load data into IGF_GR_MRR table
347
348 Known Limitations,Enhancements or Remarks
349 Change History :
350 Who When What
351 ***************************************************************/
352
353 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
354 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
355
356 BEGIN
357
358 retcode := 0;
359 igf_aw_gen.set_org_id(p_org_id);
360
361 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
362 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_awd_yr,11));
363
364 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
365 RAISE param_error;
366 END IF;
367
368 --
369 -- Get the Flat File Version and then Proceed
370 --
371 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
372 g_c_alt_code := igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number);
373
374 IF g_ver_num ='NULL' THEN
375 RAISE igf_gr_gen.no_file_version;
376 ELSE
377 load_ack;
378 END IF;
379
380 COMMIT;
381
382 EXCEPTION
383 WHEN invalid_version THEN
384 ROLLBACK;
385 retcode := 2;
386
387 WHEN param_error THEN
388 ROLLBACK;
389 retcode := 2;
390 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
391 fnd_file.put_line(fnd_file.log,errbuf);
392
393 WHEN igf_gr_gen.corrupt_data_file THEN
394 ROLLBACK;
395 retcode := 2;
396 errbuf := fnd_message.get_string('IGF','IGF_GR_CORRUPT_DATA_FILE');
397 fnd_file.put_line(fnd_file.log,errbuf);
398
399 WHEN igf_gr_gen.file_not_loaded THEN
400 ROLLBACK;
401 retcode := 2;
402 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
403 fnd_file.put_line(fnd_file.log,errbuf);
404
405 WHEN igf_gr_gen.no_file_version THEN
406 ROLLBACK;
407 retcode := 2;
408 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
409 igs_ge_msg_stack.conc_exception_hndl;
410
411 WHEN others THEN
412
413 ROLLBACK;
414 retcode := 2;
415 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
416 igs_ge_msg_stack.conc_exception_hndl;
417
418
419 END mrr_load_file;
420
421
422 END igf_gr_mrr_load_data;