[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_ESS_ESD_DATA
Source
1 PACKAGE BODY igf_gr_ess_esd_data AS
2 /* $Header: IGFGR06B.pls 120.3 2006/04/06 06:10:40 veramach ship $ */
3
4 /***************************************************************
5 Created By : adhawan
6 Date Created By : 2000/12/20
7 Purpose : To Load the data into IGF_GR_ELEC_STAT_SUM and IGF_GR_ELEC_STAT_DET tables
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 -- Bug ID : 1731177
14 -- Who When What
15 -- masehgal 17-Feb-2002 # 2216956 FACR007
16 -- Removed "Last Payment Number" from Summary
17 -- sjadhav 16-apr-2001 Added P_ESD_IND parameter to the main procedure
18 -- If P_ESD_IND is 'S' ; we should run summary Process
19 -- else if its 'D' then we should run Detail Process.
20 ***************************************************************/
21
22
23 param_error EXCEPTION;
24 invalid_version EXCEPTION; -- Thrown if the award year doesn't matches with that on the flat file.
25
26 g_ver_num VARCHAR2(30) DEFAULT NULL; -- Flat File Version Number
27 g_c_alt_code VARCHAR2(80) DEFAULT NULL; -- To hold alternate code.
28
29
30 PROCEDURE split_ess_fields ( p_record_data IN igf_gr_load_file_t.record_data%TYPE,
31 p_ess_rec OUT NOCOPY igf_gr_elec_stat_sum%ROWTYPE )
32 AS
33 /***************************************************************
34 Created By : avenkatr
35 Date Created By : 2000/12/20
36 Purpose : To split data in the single record_data column of igf_gr_load_file_t
37 into the different columns of igf_gr_elec_stat_sum table
38
39 Known Limitations,Enhancements or Remarks
40 Change History :
41 Who When What
42 smvk 11-Feb-2003 Bug # 2758812. Added '2003-2004' in g_ver_num checking.
43 ***************************************************************/
44
45
46 BEGIN
47
48 IF g_ver_num IN ('2002-2003', '2003-2004','2004-2005') THEN
49
50 BEGIN
51 p_ess_rec.rep_pell_id := SUBSTR(p_record_data,2,6);
52 p_ess_rec.duns_id := SUBSTR(p_record_data,8,11);
53 p_ess_rec.gaps_award_num := SUBSTR(p_record_data,30,16);
54 p_ess_rec.acct_schedule_number := SUBSTR(p_record_data,46,5);
55 p_ess_rec.acct_schedule_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,51,8),'YYYYMMDD');
56 p_ess_rec.prev_obligation_amt := TO_NUMBER(SUBSTR(p_record_data,59,11))/100;
57 p_ess_rec.obligation_adj_amt := TO_NUMBER(SUBSTR(p_record_data,70,11))/100;
58 p_ess_rec.curr_obligation_amt := TO_NUMBER(SUBSTR(p_record_data,81,11))/100;
59 p_ess_rec.prev_obligation_pymt_amt := TO_NUMBER(SUBSTR(p_record_data,92,11))/100;
60 p_ess_rec.obligation_pymt_adj_amt := TO_NUMBER(SUBSTR(p_record_data,103,11))/100;
61 p_ess_rec.curr_obligation_pymt_amt := TO_NUMBER(SUBSTR(p_record_data,114,11))/100;
62 p_ess_rec.ytd_total_recp := TO_NUMBER(SUBSTR(p_record_data,125,7));
63 p_ess_rec.ytd_accepted_disb_amt := TO_NUMBER(SUBSTR(p_record_data,132,11))/100;
64 p_ess_rec.ytd_posted_disb_amt := TO_NUMBER(SUBSTR(p_record_data,143,11))/100;
65 p_ess_rec.ytd_admin_cost_allowance := TO_NUMBER(SUBSTR(p_record_data,154,11))/100;
66 p_ess_rec.caps_drwn_dn_pymts := TO_NUMBER(SUBSTR(p_record_data,165,13))/100;
67 p_ess_rec.gaps_last_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,178,8),'YYYYMMDD');
68
69
70 EXCEPTION
71 WHEN OTHERS THEN -- Number / Date format exception
72 RAISE igf_gr_gen.skip_this_record;
73
74 END;
75
76 ELSIF g_ver_num IN ('2005-2006','2006-2007') THEN
77
78 BEGIN
79 p_ess_rec.rep_pell_id := SUBSTR(p_record_data,2,6);
80 p_ess_rec.duns_id := SUBSTR(p_record_data,8,11);
81 p_ess_rec.gaps_award_num := SUBSTR(p_record_data,30,16);
82 p_ess_rec.acct_schedule_number := NULL;
83 p_ess_rec.acct_schedule_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,51,8),'YYYYMMDD');
84 p_ess_rec.prev_obligation_amt := TO_NUMBER(SUBSTR(p_record_data,59,11))/100;
85 p_ess_rec.obligation_adj_amt := TO_NUMBER(SUBSTR(p_record_data,70,11))/100;
86 p_ess_rec.curr_obligation_amt := TO_NUMBER(SUBSTR(p_record_data,81,11))/100;
87 p_ess_rec.prev_obligation_pymt_amt := TO_NUMBER(SUBSTR(p_record_data,92,11))/100;
88 p_ess_rec.obligation_pymt_adj_amt := TO_NUMBER(SUBSTR(p_record_data,103,11))/100;
89 p_ess_rec.curr_obligation_pymt_amt := TO_NUMBER(SUBSTR(p_record_data,114,11))/100;
90 p_ess_rec.ytd_total_recp := TO_NUMBER(SUBSTR(p_record_data,125,7));
91 p_ess_rec.ytd_accepted_disb_amt := TO_NUMBER(SUBSTR(p_record_data,132,11))/100;
92 p_ess_rec.ytd_posted_disb_amt := NULL;
93 p_ess_rec.ytd_admin_cost_allowance := TO_NUMBER(SUBSTR(p_record_data,154,11))/100;
94 p_ess_rec.caps_drwn_dn_pymts := TO_NUMBER(SUBSTR(p_record_data,165,13))/100;
95 p_ess_rec.gaps_last_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,178,8),'YYYYMMDD');
96
97
98 EXCEPTION
99 WHEN OTHERS THEN -- Number / Date format exception
100 RAISE igf_gr_gen.skip_this_record;
101
102 END;
103 ELSE
104 RAISE igf_gr_gen.no_file_version;
105 END IF;
106
107 EXCEPTION
108
109 WHEN igf_gr_gen.skip_this_record THEN
110 RAISE;
111
112 WHEN igf_gr_gen.no_file_version THEN
113 RAISE;
114
115 WHEN OTHERS THEN
116 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
117 fnd_message.set_token('NAME','igf_gr_ess_esd_data.split_ess_fields');
118 igs_ge_msg_stack.add;
119 app_exception.raise_exception;
120
121 END split_ess_fields;
122
123
124 PROCEDURE split_esd_fields ( p_record_data IN igf_gr_load_file_t.record_data%TYPE,
125 p_esd_rec OUT NOCOPY igf_gr_elec_stat_det%ROWTYPE )
126 AS
127 /***************************************************************
128 Created By : avenkatr
129 Date Created By : 2000/12/20
130 Purpose : To split data in the single record_data column of igf_gr_load_file_t
131 into the different columns of igf_gr_elec_stat_sum table
132
133 Known Limitations,Enhancements or Remarks
134 Change History :
135 Who When What
136 smvk 11-Feb-2003 Bug # 2758812. Added '2003-2004' in g_ver_num checking.
137 ***************************************************************/
138
139 BEGIN
140
141 IF g_ver_num IN ('2002-2003', '2003-2004','2004-2005') THEN
142
143 BEGIN
144 p_esd_rec.rep_pell_id := SUBSTR(p_record_data,2,6);
145 p_esd_rec.duns_id := SUBSTR(p_record_data,8,11);
146 p_esd_rec.gaps_award_num := SUBSTR(p_record_data,30,16);
147 p_esd_rec.transaction_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,46,8),'YYYYMMDD');
148 p_esd_rec.db_cr_flag := SUBSTR(p_record_data,54,1);
149 p_esd_rec.adj_amt := TO_NUMBER(SUBSTR(p_record_data,55,11))/100;
150 p_esd_rec.gaps_process_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,66,8),'YYYYMMDD');
151 p_esd_rec.adj_batch_id := SUBSTR(p_record_data,74,26);
152
153 EXCEPTION
154 WHEN OTHERS THEN -- Number / Date format exception
155 RAISE igf_gr_gen.skip_this_record;
156 END;
157
158 ELSIF g_ver_num IN ('2005-2006','2006-2007') THEN
159
160 BEGIN
161 p_esd_rec.rep_pell_id := SUBSTR(p_record_data,2,6);
162 p_esd_rec.duns_id := SUBSTR(p_record_data,8,11);
163 p_esd_rec.gaps_award_num := SUBSTR(p_record_data,30,16);
164 p_esd_rec.transaction_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,46,8),'YYYYMMDD');
165 p_esd_rec.db_cr_flag := SUBSTR(p_record_data,54,1);
166 p_esd_rec.adj_amt := TO_NUMBER(SUBSTR(p_record_data,55,11))/100;
167 p_esd_rec.gaps_process_date := fnd_date.STRING_TO_DATE(SUBSTR(p_record_data,66,8),'YYYYMMDD');
168 p_esd_rec.adj_batch_id := SUBSTR(p_record_data,74,30);
169
170 EXCEPTION
171 WHEN OTHERS THEN -- Number / Date format exception
172 RAISE igf_gr_gen.skip_this_record;
173 END;
174
175 ELSE
176 RAISE igf_gr_gen.no_file_version;
177 END IF;
178
179 EXCEPTION
180
181 WHEN igf_gr_gen.skip_this_record THEN
182 RAISE;
183
184 WHEN igf_gr_gen.no_file_version THEN
185 RAISE;
186
187 WHEN OTHERS THEN
188 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
189 fnd_message.set_token('NAME','igf_gr_ess_esd_data.split_esd_fields');
190 igs_ge_msg_stack.add;
191 app_exception.raise_exception;
192
193 END split_esd_fields;
194
195
196 PROCEDURE insert_in_ess_table ( p_ess_rec IN igf_gr_elec_stat_sum%ROWTYPE )
197 AS
198 /***************************************************************
199 Created By : avenkatr
200 Date Created By : 2000/12/19
201 Purpose : To Load data into igf_gr_elec_stat_sum table
202
203 Known Limitations,Enhancements or Remarks
204 Change History :
205 Who When What
206 ***************************************************************/
207
208 lv_rowid VARCHAR2(25);
209 lv_ess_id NUMBER;
210
211 BEGIN
212 /* Call the table handler of the table igf_gr_elec_stat_sum to insert data */
213 igf_gr_elec_stat_sum_pkg.insert_row (
214 x_mode => 'R',
215 x_rowid => lv_rowid,
216 x_acct_schedule_number => p_ess_rec.acct_schedule_number,
217 x_acct_schedule_date => p_ess_rec.acct_schedule_date,
218 x_prev_obligation_amt => p_ess_rec.prev_obligation_amt,
219 x_obligation_adj_amt => p_ess_rec.obligation_adj_amt,
220 x_curr_obligation_amt => p_ess_rec.curr_obligation_amt,
221 x_prev_obligation_pymt_amt => p_ess_rec.prev_obligation_pymt_amt,
222 x_obligation_pymt_adj_amt => p_ess_rec.obligation_pymt_adj_amt,
223 x_curr_obligation_pymt_amt => p_ess_rec.curr_obligation_pymt_amt,
224 x_ytd_total_recp => p_ess_rec.ytd_total_recp,
225 x_ytd_accepted_disb_amt => p_ess_rec.ytd_accepted_disb_amt ,
226 x_ytd_posted_disb_amt => p_ess_rec.ytd_posted_disb_amt ,
227 x_ytd_admin_cost_allowance => p_ess_rec.ytd_admin_cost_allowance ,
228 x_caps_drwn_dn_pymts => p_ess_rec.caps_drwn_dn_pymts,
229 x_gaps_last_date => p_ess_rec.gaps_last_date,
230 x_last_pymt_number => p_ess_rec.last_pymt_number,
231 x_ess_id => lv_ess_id,
232 x_rep_pell_id => p_ess_rec.rep_pell_id,
233 x_duns_id => p_ess_rec.duns_id,
234 x_gaps_award_num => p_ess_rec.gaps_award_num );
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
239 fnd_message.set_token('NAME','igf_gr_ess_esd_data.insert_in_ess_table');
240 igs_ge_msg_stack.add;
241 app_exception.raise_exception;
242
243 END insert_in_ess_table;
244
245
246 PROCEDURE insert_in_esd_table ( p_esd_rec IN igf_gr_elec_stat_det%ROWTYPE )
247 AS
248 /***************************************************************
249 Created By : avenkatr
250 Date Created By : 2000/12/19
251 Purpose : To Load data into igf_gr_elec_stat_sum table
252
253 Known Limitations,Enhancements or Remarks
254 Change History :
255 Who When What
256 ***************************************************************/
257
258 lv_rowid VARCHAR2(25);
259 lv_esd_id NUMBER;
260
261 BEGIN
262
263 igf_gr_elec_stat_det_pkg.insert_row (
264 x_mode => 'R',
265 x_rowid => lv_rowid,
266 x_esd_id => lv_esd_id,
267 x_rep_pell_id => p_esd_rec.rep_pell_id,
268 x_duns_id => p_esd_rec.duns_id ,
269 x_gaps_award_num => p_esd_rec.gaps_award_num,
270 x_transaction_date => p_esd_rec.transaction_date,
271 x_db_cr_flag => p_esd_rec.db_cr_flag ,
272 x_adj_amt => p_esd_rec.adj_amt ,
273 x_gaps_process_date => p_esd_rec.gaps_process_date,
274 x_adj_batch_id => p_esd_rec.adj_batch_id);
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
279 fnd_message.set_token('NAME','igf_gr_ess_esd_data.insert_in_esd_table');
280 igs_ge_msg_stack.add;
281 app_exception.raise_exception;
282
283 END insert_in_esd_table;
284
285
286 PROCEDURE ess_load IS
287 /***************************************************************
288 Created By : adhawan
289 Date Created By : 2000/12/20
290 Purpose : To Load the data into IGF_GR_ELEC_STAT_SUM table
291
292 Known Limitations,Enhancements or Remarks
293 Change History :
294 -- Who When What
295 -- smvk 11-Feb-2003 Bug # 2758812. Added the code to check version mismatch and
296 -- validate the number of records mentioned in the trailer record.
297 -- masehgal 17-Feb-2002 # 2216956 FACR007
298 -- Removed "Last Payment Number" from Summary
299 ***************************************************************/
300
301 l_last_gldr_id NUMBER;
302 l_number_rec NUMBER;
303 l_batch_id VARCHAR2(100);
304 lp_count NUMBER DEFAULT 0;
305 lf_count NUMBER DEFAULT 0;
306
307 l_c_message VARCHAR2(30); -- Local variable to hold message
308
309 BEGIN
310
311 igf_gr_gen.process_pell_ack ( g_ver_num,
312 'ESS',
313 l_number_rec,
314 l_last_gldr_id,
315 l_batch_id);
316
317 -- Check the award year matches with the award year in PELL setup.
318 igf_gr_gen.match_file_version (g_ver_num, l_batch_id, l_c_message);
319 IF l_c_message = 'IGF_GR_VRSN_MISMTCH' THEN
320 fnd_message.set_name ('IGF',l_c_message);
321 fnd_message.set_token('CYCL',substr(l_batch_id,3,4));
322 fnd_message.set_token('BATCH',l_batch_id);
323 fnd_message.set_token('AWD_YR',g_c_alt_code);
324 fnd_message.set_token('VRSN',g_ver_num);
325 fnd_file.put_line(fnd_file.log,fnd_message.get);
326 RAISE invalid_version;
327 END IF;
328
329 IF l_number_rec > 0 THEN
330
331 DECLARE
332
333 CURSOR c_ess_data
334 IS
335 SELECT
336 record_data
337 FROM
338 igf_gr_load_file_t
339 WHERE
340 gldr_id BETWEEN 2 AND (l_last_gldr_id - 1)
341 AND
342 file_type = 'ESS'
343 ORDER BY
344 gldr_id;
345
346 ess_rec_data c_ess_data%ROWTYPE;
347 lv_ess_row igf_gr_elec_stat_sum%ROWTYPE;
348
349 BEGIN
350 --
351 -- Check for the type of data in the Flat File
352 --
353 FOR ess_rec_data IN c_ess_data LOOP
354
355 IF SUBSTR(ess_rec_data.record_data,1,1) ='S' THEN
356
357 BEGIN
358 --
359 -- split
360 --
361 split_ess_fields(ess_rec_data.record_data,
362 lv_ess_row);
363 --
364 -- Insert
365 --
366
367 insert_in_ess_table(lv_ess_row);
368
369
370 fnd_message.set_name('IGF','IGF_GR_ESS_LOAD_PASS');
371 fnd_message.set_token('VALUE',' ');
372 fnd_file.put_line(fnd_file.log,fnd_message.get);
373
374 lp_count := lp_count + 1;
375
376 EXCEPTION
377 WHEN igf_gr_gen.skip_this_record THEN
378 fnd_message.set_name('IGF','IGF_GR_ESS_LOAD_FAIL');
379 fnd_message.set_token('VALUE',' ');
380 fnd_file.put_line(fnd_file.log,fnd_message.get);
381
382 lf_count := lf_count + 1;
383 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
384 fnd_file.put_line(fnd_file.log,fnd_message.get);
385
386 WHEN igf_gr_gen.no_file_version THEN
387 RAISE;
388
389 END;
390 ELSE
391 lf_count := lf_count + 1;
392 END IF;
393 END LOOP;
394
395 END;
396
397 END IF;
398
399 IF l_number_rec <> (lp_count + lf_count) THEN
400 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
401 fnd_file.put_line(fnd_file.log,fnd_message.get);
402 RAISE igf_gr_gen.file_not_loaded;
403 END IF;
404
405 fnd_message.set_name('IGF','IGF_GR_FILE_REC_CNT');
406 fnd_message.set_token('CNT',l_number_rec);
407 fnd_file.put_line(fnd_file.log,fnd_message.get);
408
409 fnd_message.set_name('IGF','IGF_GR_FILE_REC_PAS');
410 fnd_message.set_token('CNT',lp_count);
411 fnd_file.put_line(fnd_file.log,fnd_message.get);
412
413 fnd_message.set_name('IGF','IGF_GR_FILE_REC_FAL');
414 fnd_message.set_token('CNT',lf_count);
415 fnd_file.put_line(fnd_file.log,fnd_message.get);
416
417
418 EXCEPTION
419 WHEN invalid_version THEN
420 RAISE;
421
422 WHEN igf_gr_gen.no_file_version THEN
423 RAISE;
424 WHEN igf_gr_gen.corrupt_data_file THEN
425 RAISE;
426 WHEN igf_gr_gen.file_not_loaded THEN
427 RAISE;
428
429 WHEN OTHERS THEN
430 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
431 fnd_message.set_token('NAME','igf_gr_ess_esd_data.ess_load ');
432 igs_ge_msg_stack.add;
433 app_exception.raise_exception;
434
435 END ess_load;
436
437 PROCEDURE esd_load IS
438 /***************************************************************
439 Created By : adhawan
440 Date Created By : 2000/12/20
441 Purpose : To Load the data into IGF_GR_ELEC_STAT_SUM table
442
443 Known Limitations,Enhancements or Remarks
444 Change History :
445 -- Who When What
446 -- masehgal 17-Feb-2002 # 2216956 FACR007
447 -- Removed "Last Payment Number" from Summary
448 ***************************************************************/
449
450 l_last_gldr_id NUMBER;
451 l_number_rec NUMBER;
452 lp_count NUMBER DEFAULT 0;
453 lf_count NUMBER DEFAULT 0;
454 l_batch_id VARCHAR2(100);
455
456 l_c_message VARCHAR2(30); -- Local variable to hold message
457
458 BEGIN
459
460 igf_gr_gen.process_pell_ack ( g_ver_num,
461 'ESD',
462 l_number_rec,
463 l_last_gldr_id,
464 l_batch_id);
465
466 -- Check the award year matches with the award year in PELL setup.
467 igf_gr_gen.match_file_version (g_ver_num, l_batch_id, l_c_message);
468 IF l_c_message = 'IGF_GR_VRSN_MISMTCH' THEN
469 fnd_message.set_name ('IGF',l_c_message);
470 fnd_message.set_token('CYCL',substr(l_batch_id,3,4));
471 fnd_message.set_token('BATCH',l_batch_id);
472 fnd_message.set_token('AWD_YR',g_c_alt_code);
473 fnd_message.set_token('VRSN',g_ver_num);
474 fnd_file.put_line(fnd_file.log,fnd_message.get);
475 RAISE invalid_version;
476 END IF;
477
478 IF l_number_rec > 0 THEN
479
480 DECLARE
481
482 CURSOR c_esd_data
483 IS
484 SELECT
485 record_data
486 FROM
487 igf_gr_load_file_t
488 WHERE
489 gldr_id BETWEEN 2 AND (l_last_gldr_id - 1)
490 AND
491 file_type = 'ESD'
492 ORDER BY
493 gldr_id;
494
495 esd_rec_data c_esd_data%ROWTYPE;
496 lv_esd_row igf_gr_elec_stat_det%ROWTYPE;
497
498 BEGIN
499 --
500 -- Check for the type of data in the Flat File
501 --
502 FOR esd_rec_data IN c_esd_data LOOP
503
504 IF SUBSTR(esd_rec_data.record_data,1,1) ='D' THEN
505 BEGIN
506 --
507 -- split
508 --
509 split_esd_fields(esd_rec_data.record_data,
510 lv_esd_row);
511 --
512 -- Insert
513 --
514
515 insert_in_esd_table(lv_esd_row);
516
517
518 fnd_message.set_name('IGF','IGF_GR_ESD_LOAD_PASS');
519 fnd_message.set_token('VALUE',' ');
520 fnd_file.put_line(fnd_file.log,fnd_message.get);
521 lp_count := lp_count + 1;
522
523 EXCEPTION
524 WHEN igf_gr_gen.skip_this_record THEN
525 fnd_message.set_name('IGF','IGF_GR_ESD_LOAD_FAIL');
526 fnd_message.set_token('VALUE',' ' );
527 fnd_file.put_line(fnd_file.log,fnd_message.get);
528 lf_count := lf_count + 1;
529 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
530 fnd_file.put_line(fnd_file.log,fnd_message.get);
531
532 WHEN igf_gr_gen.no_file_version THEN
533 RAISE;
534 END;
535 ELSE
536 lf_count := lf_count + 1;
537 END IF;
538 END LOOP;
539
540 END;
541
542 END IF;
543
544 IF l_number_rec <> (lp_count + lf_count) THEN
545 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
546 fnd_file.put_line(fnd_file.log,fnd_message.get);
547 RAISE igf_gr_gen.file_not_loaded;
548 END IF;
549
550 fnd_message.set_name('IGF','IGF_GR_FILE_REC_CNT');
551 fnd_message.set_token('CNT',l_number_rec);
552 fnd_file.put_line(fnd_file.log,fnd_message.get);
553
554 fnd_message.set_name('IGF','IGF_GR_FILE_REC_PAS');
555 fnd_message.set_token('CNT',lp_count);
556 fnd_file.put_line(fnd_file.log,fnd_message.get);
557
558 fnd_message.set_name('IGF','IGF_GR_FILE_REC_FAL');
559 fnd_message.set_token('CNT',lf_count);
560 fnd_file.put_line(fnd_file.log,fnd_message.get);
561
562
563
564 EXCEPTION
565 WHEN invalid_version THEN
566 RAISE;
567 WHEN igf_gr_gen.no_file_version THEN
568 RAISE;
569 WHEN igf_gr_gen.corrupt_data_file THEN
570 RAISE;
571 WHEN igf_gr_gen.file_not_loaded THEN
572 RAISE;
573
574 WHEN OTHERS THEN
575 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
576 fnd_message.set_token('NAME','igf_gr_ess_esd_data.esd_load ');
577 igs_ge_msg_stack.add;
578 app_exception.raise_exception;
579
580 END esd_load;
581
582
583 PROCEDURE main(
584 errbuf OUT NOCOPY VARCHAR2,
585 retcode OUT NOCOPY NUMBER,
586 p_awd_yr IN VARCHAR2,
587 p_org_id IN NUMBER
588 )
589 AS
590 /***************************************************************
591 Created By : adhawan
592 Date Created By : 2000/12/20
593 Purpose : To Load the IGF_GR_ELEC_STAT_SUM and IGF_GR_ELEC_STAT_DET table
594
595 Known Limitations,Enhancements or Remarks
596 Change History :
597 Who When What
598 --
599 -- Bug ID : 1731177
600 -- who when what
601 -- sjadhav 16-apr-2001 Call to summary procedure
602 --
603 --
604 ***************************************************************/
605 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
606 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
607
608
609 BEGIN
610
611 retcode:= 0;
612 igf_aw_gen.set_org_id(p_org_id);
613 retcode := 0;
614 igf_aw_gen.set_org_id(p_org_id);
615
616 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
617 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_awd_yr,11));
618
619
620 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
621 RAISE param_error;
622 END IF;
623
624 -- Get the Flat File Version and then Proceed
625 --
626 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
627 g_c_alt_code := igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number);
628
629 IF g_ver_num ='NULL' THEN
630 RAISE igf_gr_gen.no_file_version;
631 ELSE
632 esd_load;
633 END IF;
634
635 COMMIT;
636
637 EXCEPTION
638
639 WHEN invalid_version THEN
640 ROLLBACK;
641 retcode := 2;
642
643 WHEN no_data_found THEN
644 ROLLBACK;
645 retcode := 2;
646 errbuf := fnd_message.get_string('IGF','IGF_GR_NO_PELL_SETUP');
647 fnd_file.put_line(fnd_file.log,errbuf);
648
649 WHEN param_error THEN
650 ROLLBACK;
651 retcode := 2;
652 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
653 fnd_file.put_line(fnd_file.log,errbuf);
654
655 WHEN igf_gr_gen.corrupt_data_file THEN
656 ROLLBACK;
657 retcode := 2;
658 errbuf := fnd_message.get_string('IGF','IGF_GR_CORRUPT_DATA_FILE');
659 fnd_file.put_line(fnd_file.log,errbuf);
660
661 WHEN igf_gr_gen.file_not_loaded THEN
662 ROLLBACK;
663 retcode := 2;
664 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
665 fnd_file.put_line(fnd_file.log,errbuf);
666
667 WHEN igf_gr_gen.no_file_version THEN
668 ROLLBACK;
669 retcode := 2;
670 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
671 igs_ge_msg_stack.conc_exception_hndl;
672
673 WHEN others THEN
674
675 ROLLBACK;
676 retcode := 2;
677 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
678 igs_ge_msg_stack.conc_exception_hndl;
679
680 END main;
681
682
683 PROCEDURE main_s(
684 errbuf OUT NOCOPY VARCHAR2,
685 retcode OUT NOCOPY NUMBER,
686 p_awd_yr IN VARCHAR2,
687 p_org_id IN NUMBER
688 )
689 AS
690 /***************************************************************
691 Created By : adhawan
692 Date Created By : 2000/12/20
693 Purpose : To Load the IGF_GR_ELEC_STAT_SUM and IGF_GR_ELEC_STAT_DET table
694
695 Known Limitations,Enhancements or Remarks
696 Change History :
697 Who When What
698 --
699 -- Bug ID : 1731177
700 -- who when what
701 -- sjadhav 16-apr-2001 Call to summary procedure
702 --
703 --
704 ***************************************************************/
705 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
706 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
707
708 BEGIN
709
710 retcode:= 0;
711 igf_aw_gen.set_org_id(p_org_id);
712 retcode := 0;
713 igf_aw_gen.set_org_id(p_org_id);
714
715 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
716 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_awd_yr,11));
717
718 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
719 RAISE param_error;
720 END IF;
721
722 --
723 -- Get the Flat File Version and then Proceed
724 --
725 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
726 g_c_alt_code := igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number);
727
728 IF g_ver_num ='NULL' THEN
729 RAISE igf_gr_gen.no_file_version;
730 ELSE
731 ess_load;
732 END IF;
733
734 COMMIT;
735
736 EXCEPTION
737
738 WHEN invalid_version THEN
739 ROLLBACK;
740 retcode := 2;
741
742 WHEN param_error THEN
743 ROLLBACK;
744 retcode := 2;
745 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
746 fnd_file.put_line(fnd_file.log,errbuf);
747
748 WHEN igf_gr_gen.corrupt_data_file THEN
749 ROLLBACK;
750 retcode := 2;
751 errbuf := fnd_message.get_string('IGF','IGF_GR_CORRUPT_DATA_FILE');
752 fnd_file.put_line(fnd_file.log,errbuf);
753
754 WHEN igf_gr_gen.file_not_loaded THEN
755 ROLLBACK;
756 retcode := 2;
757 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
758 fnd_file.put_line(fnd_file.log,errbuf);
759
760 WHEN igf_gr_gen.no_file_version THEN
761 ROLLBACK;
762 retcode := 2;
763 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
764 igs_ge_msg_stack.conc_exception_hndl;
765
766 WHEN others THEN
767
768 ROLLBACK;
769 retcode := 2;
770 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
771 igs_ge_msg_stack.conc_exception_hndl;
772
773 END main_s;
774
775 END igf_gr_ess_esd_data;