1 PACKAGE BODY igf_aw_anticipated_data AS
2 /* $Header: IGFAW20B.pls 120.4 2006/01/27 00:59:32 ridas noship $ */
3
4
5 PROCEDURE process_anti_data(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
6 p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
7 p_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE,
8 p_interface igf_aw_anticpt_ints%ROWTYPE,
9 p_delete_flag VARCHAR2)
10 IS
11 ------------------------------------------------------------------
12 --Created by : ridas, Oracle India
13 --Date created: 02-NOV-2004
14 --
15 --Purpose:
16 --
17 --
18 --Known limitations/enhancements and/or remarks:
19 --
20 --Change History:
21 --Who When What
22 -------------------------------------------------------------------
23
24
25 --Cursor to fetch organizational unit
26 CURSOR c_org_unit(cp_org_unit igf_ap_fa_ant_data.org_unit_cd%TYPE)
27 IS
28 SELECT 'x'
29 FROM igs_or_inst_org_base_v
30 WHERE party_number = cp_org_unit
31 AND inst_org_ind = 'O'
32 AND SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE);
33
34 l_org_unit c_org_unit%ROWTYPE;
35
36
37 --Cursor to fetch program type
38 CURSOR c_prog_type(cp_prog_type igf_ap_fa_ant_data.program_type%TYPE)
39 IS
40 SELECT 'x'
41 FROM igs_ps_type_v
42 WHERE course_type = cp_prog_type
43 AND NVL(closed_ind,'N')<>'Y';
44
45 l_prog_type c_prog_type%ROWTYPE;
46
47
48 --Cursor to fetch program location
49 CURSOR c_prog_loc(cp_prog_loc igf_ap_fa_ant_data.program_location_cd%TYPE)
50 IS
51 SELECT 'x'
52 FROM igs_ad_location
53 WHERE location_cd = cp_prog_loc
54 AND NVL(closed_ind,'N')<>'Y';
55
56 l_prog_loc c_prog_loc%ROWTYPE;
57
58
59 --Cursor to fetch program code
60 CURSOR c_prog_cd(cp_prog_cd igf_ap_fa_ant_data.program_cd%TYPE)
61 IS
62 SELECT 'x'
63 FROM igs_ps_ver
64 WHERE course_cd = cp_prog_cd
65 AND SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE)
66 AND rownum = 1;
67
68 l_prog_cd c_prog_cd%ROWTYPE;
69
70
71 --Cursor to fetch class standing
72 CURSOR c_class_stnd(cp_class_stnd igf_ap_fa_ant_data.class_standing%TYPE)
73 IS
74 SELECT 'x'
75 FROM igs_pr_class_std_v
76 WHERE class_standing = cp_class_stnd
77 AND NVL(closed_ind,'N')<>'Y';
78
79 l_class_stnd c_class_stnd%ROWTYPE;
80
81
82 --Cursor to fetch residency status and house status
83 CURSOR c_res_status(cp_lookup_code igs_lookups_view.lookup_code%TYPE,
84 cp_lookup_type igs_lookups_view.lookup_type%TYPE
85 )
86 IS
87 SELECT 'x'
88 FROM igs_lookups_view
89 WHERE lookup_type = cp_lookup_type
90 AND lookup_code = cp_lookup_code
91 AND enabled_flag = 'Y';
92
93 l_res_status c_res_status%ROWTYPE;
94
95 l_house_status c_res_status%ROWTYPE;
96
97
98 --Cursor to fetch attendance type
99 CURSOR c_atten_type(cp_atten_type igf_ap_fa_ant_data.attendance_type%TYPE)
100 IS
101 SELECT 'x'
102 FROM igs_en_atd_type
103 WHERE attendance_type = cp_atten_type
104 AND NVL(closed_ind,'N')<>'Y';
105
106 l_atten_type c_atten_type%ROWTYPE;
107
108
109 --Cursor to fetch attendance mode
110 CURSOR c_atten_mode(cp_atten_mode igf_ap_fa_ant_data.attendance_mode%TYPE)
111 IS
112 SELECT 'x'
113 FROM igs_en_atd_mode
114 WHERE attendance_mode = cp_atten_mode
115 AND NVL(closed_ind,'N')<>'Y';
116
117 l_atten_mode c_atten_mode%ROWTYPE;
118
119
120 --Cursor to retrieve Anticipated Data
121 CURSOR c_ant_data (cp_base_id igf_ap_fa_ant_data.base_id%TYPE,
122 cp_ld_cal_type igf_ap_fa_ant_data.ld_cal_type%TYPE,
123 cp_ld_sequence_number igf_ap_fa_ant_data.ld_sequence_number%TYPE)
124 IS
125 SELECT ant.rowid row_id,
126 ant.*
127 FROM igf_ap_fa_ant_data ant
128 WHERE base_id = cp_base_id
129 AND ld_cal_type = cp_ld_cal_type
130 AND ld_sequence_number = cp_ld_sequence_number;
131
132 l_ant_data c_ant_data%ROWTYPE;
133
134 lv_set_ant_data VARCHAR2(1);
135 lv_rowid ROWID;
136 lv_ret_status VARCHAR2(1);
137 lv_message VARCHAR2(500);
138 lv_msg_text VARCHAR2(200);
139 ln_msg_index NUMBER;
140
141
142 BEGIN
143 lv_set_ant_data := 'Y';
144
145 --validate organizational unit
146 IF p_interface.org_unit_cd IS NOT NULL THEN
147 OPEN c_org_unit(p_interface.org_unit_cd );
148 FETCH c_org_unit INTO l_org_unit;
149 IF c_org_unit%NOTFOUND THEN
150 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
151 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_org_unit%NOTFOUND');
152 END IF;
153
154 lv_set_ant_data := 'N';
155 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
156 fnd_message.set_token('FIELD','ORG_UNIT_CD '||': '||p_interface.org_unit_cd );
157 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
158 END IF;
159 CLOSE c_org_unit;
160 END IF;
161
162 --validate program type
163 IF p_interface.program_type IS NOT NULL THEN
164 OPEN c_prog_type(p_interface.program_type);
165 FETCH c_prog_type INTO l_prog_type;
166 IF c_prog_type%NOTFOUND THEN
167 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
168 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_prog_type%NOTFOUND');
169 END IF;
170
171 lv_set_ant_data := 'N';
172 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
173 fnd_message.set_token('FIELD','PROGRAM_TYPE'||': '||p_interface.program_type);
174 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
175 END IF;
176 CLOSE c_prog_type;
177 END IF;
178
179 --validate program location
180 IF p_interface.program_location_cd IS NOT NULL THEN
181 OPEN c_prog_loc(p_interface.program_location_cd);
182 FETCH c_prog_loc INTO l_prog_loc;
183 IF c_prog_loc%NOTFOUND THEN
184 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
185 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_prog_loc%NOTFOUND');
186 END IF;
187
188 lv_set_ant_data := 'N';
189 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
190 fnd_message.set_token('FIELD','PROGRAM_LOCATION_CD'||': '||p_interface.program_location_cd);
191 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
192 END IF;
193 CLOSE c_prog_loc;
194 END IF;
195
196 --validate program code
197 IF p_interface.program_cd IS NOT NULL THEN
198 OPEN c_prog_cd(p_interface.program_cd);
199 FETCH c_prog_cd INTO l_prog_cd;
200 IF c_prog_cd%NOTFOUND THEN
201 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
202 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_prog_cd%NOTFOUND');
203 END IF;
204
205 lv_set_ant_data := 'N';
206 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
207 fnd_message.set_token('FIELD','PROGRAM_CD'||': '||p_interface.program_cd);
208 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
209 END IF;
210 CLOSE c_prog_cd;
211 END IF;
212
213 --validate class standing
214 IF p_interface.class_standing IS NOT NULL THEN
215 OPEN c_class_stnd(p_interface.class_standing);
216 FETCH c_class_stnd INTO l_class_stnd;
217 IF c_class_stnd%NOTFOUND THEN
218 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
219 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_class_stnd%NOTFOUND');
220 END IF;
221
222 lv_set_ant_data := 'N';
223 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
224 fnd_message.set_token('FIELD','CLASS_STANDING'||': '||p_interface.class_standing);
225 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
226 END IF;
227 CLOSE c_class_stnd;
228 END IF;
229
230 --validate residency status
231 IF p_interface.residency_status_code IS NOT NULL THEN
232 OPEN c_res_status(p_interface.residency_status_code,'PE_RES_STATUS');
233 FETCH c_res_status INTO l_res_status;
234 IF c_res_status%NOTFOUND THEN
235 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
236 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_res_status%NOTFOUND');
237 END IF;
238
239 lv_set_ant_data := 'N';
240 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
241 fnd_message.set_token('FIELD','RESIDENCY_STATUS_CODE'||': '||p_interface.residency_status_code);
242 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
243 END IF;
244 CLOSE c_res_status;
245 END IF;
246
247 --validate housing status
248 IF p_interface.housing_status_code IS NOT NULL THEN
249 OPEN c_res_status(p_interface.housing_status_code,'PE_TEA_PER_RES');
250 FETCH c_res_status INTO l_house_status;
251 IF c_res_status%NOTFOUND THEN
252 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
253 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_res_status%NOTFOUND');
254 END IF;
255
256 lv_set_ant_data := 'N';
257 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
258 fnd_message.set_token('FIELD','HOUSING_STATUS_CODE'||': '||p_interface.housing_status_code);
259 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
260 END IF;
261 CLOSE c_res_status;
262 END IF;
263
264 --validate attendance type
265 IF p_interface.attendance_type IS NOT NULL THEN
266 OPEN c_atten_type(p_interface.attendance_type);
267 FETCH c_atten_type INTO l_atten_type;
268 IF c_atten_type%NOTFOUND THEN
269 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
270 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_atten_type%NOTFOUND');
271 END IF;
272
273 lv_set_ant_data := 'N';
274 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
275 fnd_message.set_token('FIELD','ATTENDANCE_TYPE'||': '||p_interface.attendance_type);
276 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
277 END IF;
278 CLOSE c_atten_type;
279 END IF;
280
281 --validate attendance mode
282 IF p_interface.attendance_mode IS NOT NULL THEN
283 OPEN c_atten_mode(p_interface.attendance_mode);
284 FETCH c_atten_mode INTO l_atten_mode;
285 IF c_atten_mode%NOTFOUND THEN
286 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
287 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','c_atten_mode%NOTFOUND');
288 END IF;
289
290 lv_set_ant_data := 'N';
291 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
292 fnd_message.set_token('FIELD','ATTENDANCE_MODE'||': '||p_interface.attendance_mode);
293 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
294 END IF;
295 CLOSE c_atten_mode;
296 END IF;
297
298 --validate months enrolled number
299 IF p_interface.months_enrolled_num IS NOT NULL THEN
300 IF p_interface.months_enrolled_num NOT BETWEEN 1 AND 12 THEN
301 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
302 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','p_interface.months_enrolled_num NOT BETWEEN 1 AND 12');
303 END IF;
304
305 lv_set_ant_data := 'N';
306 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
307 fnd_message.set_token('FIELD','MONTHS_ENROLLED_NUM'||': '||p_interface.months_enrolled_num);
308 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
309 END IF;
310 END IF;
311
312 --validate credit point number
313 IF p_interface.credit_points_num IS NOT NULL THEN
314 IF p_interface.credit_points_num < 0 THEN
315 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
316 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','p_interface.credit_points_num < 0');
317 END IF;
318
319 lv_set_ant_data := 'N';
320 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
321 fnd_message.set_token('FIELD','CREDIT_POINTS_NUM'||': '||p_interface.credit_points_num);
322 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
323 END IF;
324 END IF;
325
326 --to check OSS attributes
327 igs_ge_msg_stack.initialize;
328 --Bug #4164450
329 igf_aw_coa_gen.check_oss_attrib (
330 p_interface.org_unit_cd ,
331 p_interface.program_cd ,
332 p_interface.program_type,
333 p_interface.program_location_cd,
334 p_interface.attendance_type,
335 p_interface.attendance_mode,
336 lv_ret_status
337 );
338
339 IF lv_ret_status <>'S' THEN
340 IF igs_ge_msg_stack.count_msg > 0 THEN
341
342 FOR i IN 1..igs_ge_msg_stack.count_msg
343 LOOP
344 igs_ge_msg_stack.get(i,'F',lv_msg_text, ln_msg_index);
345 IF i = 1 THEN
346 lv_message := RPAD(' ',5) ||
347 lv_msg_text;
348 ELSE
349 lv_message := lv_message ||
350 fnd_global.newline ||
351 RPAD(' ',5) ||
352 lv_msg_text;
353 END IF;
354 END LOOP;
355 fnd_file.put_line(fnd_file.log,lv_message);
356 END IF;
357 END IF;
358
359
360 IF lv_set_ant_data = 'Y' THEN
361 IF p_interface.import_record_type = 'U' THEN
362 OPEN c_ant_data(p_base_id,p_ld_cal_type,p_ld_sequence_number);
363 FETCH c_ant_data INTO l_ant_data;
364 CLOSE c_ant_data;
365
366 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
367 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','calling igf_ap_fa_ant_data_pkg.update_row');
368 END IF;
369
370
371 igf_ap_fa_ant_data_pkg.update_row (
372 x_mode => 'R',
373 x_rowid => l_ant_data.row_id,
374 x_base_id => p_base_id,
375 x_ld_cal_type => l_ant_data.ld_cal_type,
376 x_ld_sequence_number => l_ant_data.ld_sequence_number,
377 x_org_unit_cd => p_interface.org_unit_cd ,
378 x_program_type => p_interface.program_type,
379 x_program_location_cd => p_interface.program_location_cd,
380 x_program_cd => p_interface.program_cd,
381 x_class_standing => p_interface.class_standing,
382 x_residency_status_code => p_interface.residency_status_code,
383 x_housing_status_code => p_interface.housing_status_code,
384 x_attendance_type => p_interface.attendance_type,
385 x_attendance_mode => p_interface.attendance_mode,
386 x_months_enrolled_num => p_interface.months_enrolled_num,
387 x_credit_points_num => p_interface.credit_points_num
388 );
389
390 fnd_message.set_name('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC');
391 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
392
393 ELSIF p_interface.import_record_type = 'I' THEN
394 lv_rowid := NULL;
395
396 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
397 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','calling igf_ap_fa_ant_data_pkg.insert_row');
398 END IF;
399
400 igf_ap_fa_ant_data_pkg.insert_row (
401 x_mode => 'R',
402 x_rowid => lv_rowid,
403 x_base_id => p_base_id,
404 x_ld_cal_type => p_ld_cal_type,
405 x_ld_sequence_number => p_ld_sequence_number,
406 x_org_unit_cd => p_interface.org_unit_cd ,
407 x_program_type => p_interface.program_type,
408 x_program_location_cd => p_interface.program_location_cd,
409 x_program_cd => p_interface.program_cd,
410 x_class_standing => p_interface.class_standing,
411 x_residency_status_code => p_interface.residency_status_code,
412 x_housing_status_code => p_interface.housing_status_code,
413 x_attendance_type => p_interface.attendance_type,
414 x_attendance_mode => p_interface.attendance_mode,
415 x_months_enrolled_num => p_interface.months_enrolled_num,
416 x_credit_points_num => p_interface.credit_points_num
417 );
418
419
420 fnd_message.set_name('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC');
421 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
422
423 END IF;
424
425 IF p_delete_flag = 'Y' THEN
426 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
427 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','deleting from igf_aw_anticpt_ints');
428 END IF;
429
430 DELETE FROM igf_aw_anticpt_ints
431 WHERE batch_num = p_interface.batch_num
432 AND ci_alternate_code = p_interface.ci_alternate_code
433 AND ld_alternate_code = p_interface.ld_alternate_code
434 AND person_number = p_interface.person_number;
435
436 ELSE
437 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
438 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','updating in igf_aw_anticpt_ints');
439 END IF;
440
441 UPDATE igf_aw_anticpt_ints
442 SET IMPORT_STATUS_TYPE = 'I'
443 WHERE batch_num = p_interface.batch_num
444 AND ci_alternate_code = p_interface.ci_alternate_code
445 AND ld_alternate_code = p_interface.ld_alternate_code
446 AND person_number = p_interface.person_number;
447
448 END IF;
449
450 ELSE
451 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
452 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','No data Imported');
453 END IF;
454
455 fnd_message.set_name('IGS','IGS_EN_NO_DATA_IMP');
456 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
457
458 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
459 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','updating in igf_aw_anticpt_ints');
460 END IF;
461
462 UPDATE igf_aw_anticpt_ints
463 SET IMPORT_STATUS_TYPE = 'E'
464 WHERE batch_num = p_interface.batch_num
465 AND ci_alternate_code = p_interface.ci_alternate_code
466 AND ld_alternate_code = p_interface.ld_alternate_code
467 AND person_number = p_interface.person_number;
468
469 END IF;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
474 fnd_message.set_token('NAME','IGF_AW_ANTICIPATED_DATA.PROCESS_ANTI_DATA :' || SQLERRM);
475 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
476 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_anticipated_data.process_anti_data.exception','sql error:'||SQLERRM);
477 END IF;
478 igs_ge_msg_stack.conc_exception_hndl;
479 app_exception.raise_exception;
480
481 END process_anti_data;
482
483
484
485 -- This procedure is the callable from concurrent manager
486 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
487 retcode OUT NOCOPY NUMBER,
488 p_award_year IN VARCHAR2,
489 p_batch_id IN igf_ap_li_bat_ints.batch_num%TYPE,
490 p_del_ind IN VARCHAR2 )
491 IS
492
493 ------------------------------------------------------------------
494 --Created by : ridas, Oracle India
495 --Date created: 02-NOV-2004
496 --
497 --Purpose:
498 --
499 --
500 --Known limitations/enhancements and/or remarks:
501 --
502 --Change History:
503 --Who When What
504 --tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
505 -------------------------------------------------------------------
506
507 --Cursor to retrieve Anticipated Data From Interface Table
508 CURSOR c_interface(cp_batch_id igf_aw_anticpt_ints.batch_num%TYPE,
509 cp_alternate_code igf_aw_anticpt_ints.ci_alternate_code%TYPE)
510 IS
511 SELECT intf.*
512 FROM igf_aw_anticpt_ints intf
513 WHERE intf.batch_num = cp_batch_id
514 AND intf.ci_alternate_code = cp_alternate_code
515 AND intf.import_status_type = 'R'
516 AND intf.import_record_type in ('I','U')
517 ORDER BY intf.person_number;
518
519
520 --Cursor to check Anticipated Data From Interface Table with status as 'R'
521 CURSOR c_chk_interface(cp_batch_id igf_aw_anticpt_ints.batch_num%TYPE,
522 cp_alternate_code igf_aw_anticpt_ints.ci_alternate_code%TYPE)
523 IS
524 SELECT 'x' val
525 FROM igf_aw_anticpt_ints intf
526 WHERE intf.batch_num = cp_batch_id
527 AND intf.ci_alternate_code = cp_alternate_code
528 AND intf.import_status_type = 'R'
529 AND intf.import_record_type in ('I','U')
530 AND rownum = 1;
531
532 l_chk_interface c_chk_interface%ROWTYPE;
533
534
535 --Cursor to retrieve Anticipated Data
536 CURSOR c_ant_data (cp_base_id igf_ap_fa_ant_data.base_id%TYPE,
537 cp_ld_cal_type igf_ap_fa_ant_data.ld_cal_type%TYPE,
538 cp_ld_sequence_number igf_ap_fa_ant_data.ld_sequence_number%TYPE)
539 IS
540 SELECT ant.base_id
541 FROM igf_ap_fa_ant_data ant
542 WHERE base_id = cp_base_id
543 AND ld_cal_type = cp_ld_cal_type
544 AND ld_sequence_number = cp_ld_sequence_number;
545
546 l_ant_data c_ant_data%ROWTYPE;
547
548
549 --Cursor to validate batch no
550 CURSOR c_chk_batch(cp_batch_num igf_ap_li_bat_ints.batch_num%TYPE,
551 cp_batch_type igf_ap_li_bat_ints.batch_type%TYPE)
552 IS
553 SELECT batch_num
554 FROM igf_ap_li_bat_ints
555 WHERE batch_num = cp_batch_num
556 AND batch_type= cp_batch_type
557 AND rownum = 1;
558
559 l_chk_batch c_chk_batch%ROWTYPE;
560
561
562 CURSOR c_get_cal_typ_seq_num(p_alternate_code igs_ca_inst_all.alternate_code%TYPE,
563 p_cal_type igs_ca_inst_all.cal_type%TYPE,
564 p_sequence_number igs_ca_inst_all.sequence_number%TYPE
565 )
566 IS
567 SELECT ld_cal_type, ld_sequence_number
568 FROM igf_aw_awd_ld_cal_v cal
569 WHERE cal.aw_cal_type = p_cal_type
570 AND cal.aw_sequence_number = p_sequence_number
571 AND cal.ld_alternate_code = p_alternate_code;
572
573 l_get_cal_typ_seq_num c_get_cal_typ_seq_num%ROWTYPE;
574
575
576 lv_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
577 ln_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
578 lv_alternate_code igs_ca_inst_all.alternate_code%TYPE;
579 lv_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
580 ln_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
581 ln_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
582 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
583 lv_person_number igf_aw_anticpt_ints.person_number%TYPE;
584 param_exception EXCEPTION;
585
586 BEGIN
587 igf_aw_gen.set_org_id(NULL);
588 retcode := 0;
589 errbuf := NULL;
590 lv_ci_cal_type := TRIM(SUBSTR(p_award_year,1,10));
591 ln_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
592 lv_alternate_code := igf_gr_gen.get_alt_code(lv_ci_cal_type,ln_ci_sequence_number);
593 lv_person_number := '0';
594
595 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
596 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','p_award_year:'||p_award_year);
597 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','p_batch_id:'||p_batch_id);
598 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','p_del_ind:'||p_del_ind);
599 END IF;
600
601 fnd_file.new_line(fnd_file.log,1);
602
603 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
604 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'),40) ||': '|| lv_alternate_code);
605 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','BATCH_NUMBER'),40) ||': '||p_batch_id);
606 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','DELETE_FLAG'),40) ||': '||igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_del_ind) );
607
608 fnd_file.new_line(fnd_file.log,2);
609
610 IF (p_award_year IS NULL) OR (p_batch_id IS NULL) OR (p_del_ind IS NULL) THEN
611 RAISE param_exception;
612
613 ELSIF lv_ci_cal_type IS NULL OR ln_ci_sequence_number IS NULL THEN
614 RAISE param_exception;
615 END IF;
616
617 --Check whether batch number exist or not
618 OPEN c_chk_batch(p_batch_id,'ANT');
619 FETCH c_chk_batch INTO l_chk_batch;
620 CLOSE c_chk_batch;
621
622 IF l_chk_batch.batch_num IS NULL THEN
623 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
624 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','l_chk_batch.batch_num IS NULL');
625 END IF;
626
627 fnd_message.set_name('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
628 fnd_file.put_line(fnd_file.log,fnd_message.get);
629 RAISE param_exception;
630 END IF;
631
632 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
633
634 --check Anticipated Data From Interface Table with status as 'R'
635 OPEN c_chk_interface(p_batch_id,lv_alternate_code);
636 FETCH c_chk_interface INTO l_chk_interface;
637 CLOSE c_chk_interface;
638
639 IF l_chk_interface.val IS NULL THEN
640 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
641 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','l_chk_interface.val IS NULL');
642 END IF;
643
644 fnd_message.set_name('IGS','IGS_FI_NO_RECORD_AVAILABLE');
645 fnd_file.put_line(fnd_file.log,fnd_message.get);
646 RETURN;
647 END IF;
648
649
650 --Validate the person and INSERT/UPDATE based on the record type
651 FOR l_interface IN c_interface(p_batch_id,lv_alternate_code)
652 LOOP
653 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
654 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','validating person number :'||l_interface.person_number);
655 END IF;
656
657 fnd_file.new_line(fnd_file.log,1);
658
659 IF lv_person_number <> l_interface.person_number THEN
660 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
661 fnd_message.set_token('STDNT',l_interface.person_number);
662 fnd_file.put_line(fnd_file.log,fnd_message.get);
663
664 --here call to the generic wrapper is being made to check the validity of the perosn and base record
665 igf_ap_gen.check_person(l_interface.person_number,lv_ci_cal_type,ln_ci_sequence_number,ln_person_id,ln_base_id);
666 END IF;
667
668 lv_person_number := l_interface.person_number;
669
670 IF ln_person_id IS NULL THEN
671 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
672 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','ln_person_id IS NULL');
673 END IF;
674
675 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
676 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
677 ELSE
678 IF ln_base_id IS NULL THEN
679 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
680 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','ln_base_id IS NULL');
681 END IF;
682
683 fnd_message.set_name('IGF','IGF_AP_FABASE_NOT_FOUND');
684 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
685 ELSE
686 l_get_cal_typ_seq_num := NULL;
687
688 OPEN c_get_cal_typ_seq_num(l_interface.ld_alternate_code,
689 lv_ci_cal_type,
690 ln_ci_sequence_number
691 );
692
693 FETCH c_get_cal_typ_seq_num INTO l_get_cal_typ_seq_num;
694 CLOSE c_get_cal_typ_seq_num;
695
696 fnd_message.set_name('IGF','IGF_AW_PROC_TERM');
697 fnd_message.set_token('TERM',l_interface.ld_alternate_code);
698 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
699
700
701 IF l_get_cal_typ_seq_num.ld_cal_type IS NULL OR l_get_cal_typ_seq_num.ld_sequence_number IS NULL THEN
702 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
703 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','l_get_cal_typ_seq_num IS NULL');
704 END IF;
705
706 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
707 fnd_message.set_token('FIELD','LD_ALTERNATE_CODE'||': '||l_interface.ld_alternate_code);
708 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
709
710 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
711 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','updating in igf_aw_anticpt_ints');
712 END IF;
713
714 UPDATE igf_aw_anticpt_ints
715 SET IMPORT_STATUS_TYPE = 'E'
716 WHERE batch_num = l_interface.batch_num
717 AND ci_alternate_code = l_interface.ci_alternate_code
718 AND ld_alternate_code = l_interface.ld_alternate_code
719 AND person_number = l_interface.person_number;
720
721
722 ELSE
723 lv_ld_cal_type := l_get_cal_typ_seq_num.ld_cal_type;
724 ln_ld_sequence_number := l_get_cal_typ_seq_num.ld_sequence_number;
725
726
727 l_ant_data := NULL;
728
729 OPEN c_ant_data(ln_base_id,lv_ld_cal_type,ln_ld_sequence_number);
730 FETCH c_ant_data INTO l_ant_data;
731 CLOSE c_ant_data;
732
733 IF l_interface.import_record_type = 'U' THEN
734 IF l_ant_data.base_id IS NULL THEN
735 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
736 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','No anticipated data for record type=U');
737 END IF;
738
739 fnd_message.set_name('IGF','IGF_AW_IMP_ANT_AVAL');
740 fnd_message.set_token('REC_TYPE',l_interface.import_record_type);
741 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
742
743 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
744 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','updating in igf_aw_anticpt_ints');
745 END IF;
746
747 UPDATE igf_aw_anticpt_ints
748 SET IMPORT_STATUS_TYPE = 'E'
749 WHERE batch_num = l_interface.batch_num
750 AND ci_alternate_code = l_interface.ci_alternate_code
751 AND ld_alternate_code = l_interface.ld_alternate_code
752 AND person_number = l_interface.person_number;
753
754 ELSE
755 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
756 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','calling process_anti_data');
757 END IF;
758
759 process_anti_data(ln_base_id,lv_ld_cal_type,ln_ld_sequence_number,l_interface,p_del_ind);
760 END IF;
761
762 ELSIF l_interface.import_record_type = 'I' THEN
763 IF l_ant_data.base_id IS NOT NULL THEN
764 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
765 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','anticipated data available for record type=I');
766 END IF;
767
768 fnd_message.set_name('IGF','IGF_AW_IMP_ANT_NOT_AVAL');
769 fnd_message.set_token('REC_TYPE',l_interface.import_record_type);
770 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
771
772 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
773 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','updating in igf_aw_anticpt_ints');
774 END IF;
775
776 UPDATE igf_aw_anticpt_ints
777 SET IMPORT_STATUS_TYPE = 'E'
778 WHERE batch_num = l_interface.batch_num
779 AND ci_alternate_code = l_interface.ci_alternate_code
780 AND ld_alternate_code = l_interface.ld_alternate_code
781 AND person_number = l_interface.person_number;
782
783 ELSE
784 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
785 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.main.debug','calling process_anti_data');
786 END IF;
787
788 process_anti_data(ln_base_id,lv_ld_cal_type,ln_ld_sequence_number,l_interface,p_del_ind);
789 END IF;
790
791 END IF;
792 END IF;
793 END IF;
794 END IF;
795 COMMIT;
796
797 END LOOP;
798
799
800 EXCEPTION
801 WHEN param_exception THEN
802 retcode:=2;
803 fnd_message.set_name('IGF','IGF_AW_PARAM_ERR');
804 igs_ge_msg_stack.add;
805 errbuf := fnd_message.get;
806
807 WHEN app_exception.record_lock_exception THEN
808 ROLLBACK;
809 retcode:=2;
810 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
811 igs_ge_msg_stack.add;
812 errbuf := fnd_message.get;
813
814 WHEN OTHERS THEN
815 ROLLBACK;
816 retcode:=2;
817 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
818 igs_ge_msg_stack.add;
819 errbuf := fnd_message.get || SQLERRM;
820
821 END main;
822
823
824 END igf_aw_anticipated_data;
[Home] [Help]