1 PACKAGE BODY igf_ap_lg_coa_imp AS
2 /* $Header: IGFAP40B.pls 120.2 2006/01/17 02:37:45 tsailaja noship $ */
3
4 g_log_tab_index NUMBER := 0;
5
6 TYPE log_record IS RECORD ( person_number VARCHAR2(30),
7 message_text VARCHAR2(500));
8
9 -- The PL/SQL table for storing the log messages
10 TYPE LogTab IS TABLE OF log_record INDEX BY BINARY_INTEGER;
11
12 g_log_tab LogTab;
13
14
15 PROCEDURE log_input_params( p_batch_num IN igf_aw_li_coa_ints.batch_num%TYPE ,
16 p_alternate_code IN igs_ca_inst.alternate_code%TYPE ,
17 p_delete_flag IN VARCHAR2 ) IS
18 /*
19 || Created By : masehgal
20 || Created On : 28-May-2003
21 || Purpose : Logs all the Input Parameters
22 || Known limitations, enhancements or remarks :
23 || Change History :
24 || Who When What
25 || (reverse chronological order - newest change first)
26 */
27
28 -- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
29 CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
30 SELECT batch_desc, batch_type
31 FROM igf_ap_li_bat_ints
32 WHERE batch_num = cp_batch_num ;
33
34 l_lkup_type VARCHAR2(60) ;
35 l_lkup_code VARCHAR2(60) ;
36 l_batch_desc igf_ap_li_bat_ints.batch_desc%TYPE ;
37 l_batch_type igf_ap_li_bat_ints.batch_type%TYPE ;
38 l_batch_id igf_ap_li_bat_ints.batch_type%TYPE ;
39 l_yes_no igf_lookups_view.meaning%TYPE ;
40 l_award_year_pmpt igf_lookups_view.meaning%TYPE ;
41 l_params_pass_prmpt igf_lookups_view.meaning%TYPE ;
42 l_person_number_prmpt igf_lookups_view.meaning%TYPE ;
43 l_batch_num_prmpt igf_lookups_view.meaning%TYPE ;
44 l_error igf_lookups_view.meaning%TYPE ;
45
46 BEGIN -- begin log parameters
47
48 -- get the batch description
49 OPEN c_batch_desc( p_batch_num) ;
50 FETCH c_batch_desc INTO l_batch_desc, l_batch_type ;
51 CLOSE c_batch_desc ;
52
53 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
54 l_person_number_prmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
55 l_batch_num_prmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','BATCH_ID');
56 l_award_year_pmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','AWARD_YEAR');
57 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
58 l_params_pass_prmpt := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PARAMETER_PASS');
59
60 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
61 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
62 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
63
64 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
65 FND_FILE.PUT_LINE( FND_FILE.LOG, l_params_pass_prmpt) ; --Parameters Passed
66 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
67
68 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_award_year_pmpt, 40) || ' : '|| p_alternate_code ) ;
69
70 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_batch_num_prmpt, 40) || ' : '|| TO_CHAR(p_batch_num) || '-' || l_batch_desc ) ;
71
72 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( FND_MESSAGE.GET_STRING('IGS','IGS_GE_ASK_DEL_REC'), 40) || ' : '|| l_yes_no ) ;
73 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
74 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
75 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
80 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.log_input_params.exception','Unhandled exception in Procedure log_input_params '||SQLERRM);
81 END IF;
82 END log_input_params ;
83
84
85 PROCEDURE print_log_process( p_person_number IN VARCHAR2,
86 p_error IN VARCHAR2 ) IS
87 /*
88 || Created By : masehgal
89 || Created On : 01-Jun-2003
90 || Purpose : This process gets the records from the pl/sql table and print in the log file
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96
97 l_count NUMBER(5) := g_log_tab.COUNT;
98 l_old_person VARCHAR2(30) := '*******';
99
100 BEGIN
101
102 FOR i IN 1..l_count LOOP
103 IF l_old_person <> g_log_tab(i).person_number THEN
104 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------');
105 fnd_file.put_line(fnd_file.log,p_person_number || ' : ' || g_log_tab(i).person_number);
106 END IF;
107 fnd_file.put_line(fnd_file.log,g_log_tab(i).message_text);
108 l_old_person := g_log_tab(i).person_number;
109 END LOOP;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
114 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.print_log_process.exception','Unhandled exception in Procedure print_log_process'||SQLERRM);
115 END IF;
116 END print_log_process;
117
118
119
120 PROCEDURE chk_per_rec_stat( p_batch_num IN NUMBER,
121 p_alternate_code IN VARCHAR2,
122 p_person_number IN VARCHAR2,
123 p_rec_type OUT NOCOPY VARCHAR2 ) IS
124 /*
125 || Created By : masehgal
126 || Created On : 07-Jun-2003
127 || Purpose : This process gets the record type for the person from the interface table
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || (reverse chronological order - newest change first)
132 */
133
134 CURSOR c_get_person_rec_type ( cp_alternate_code igf_aw_li_coa_ints.ci_alternate_code%TYPE,
135 cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE,
136 cp_person_number igf_aw_li_coa_ints.person_number%TYPE) IS
137 SELECT DISTINCT(NVL(import_record_type,'*')) types
138 FROM igf_aw_li_coa_ints
139 WHERE ci_alternate_code = cp_alternate_code
140 AND batch_num = cp_batch_num
141 AND person_number = cp_person_number
142 AND import_status_type IN ('R','U') ;
143 l_count c_get_person_rec_type%ROWTYPE ;
144 l_update VARCHAR2(1) ;
145 l_others VARCHAR2(1) ;
146
147 BEGIN
148 l_update := NULL ;
149 l_others := NULL ;
150
151 FOR l_count IN c_get_person_rec_type ( p_alternate_code, p_batch_num, p_person_number)
152 LOOP
153 IF NVL(l_count.types,'*') = 'U' THEN
154 l_update := 'U' ;
155 ELSE
156 l_others := 'O' ;
157 END IF ;
158 END LOOP ;
159
160 IF l_update is NOT NULL and l_others is not null THEN
161 p_rec_type := 'E';
162 ELSIF l_update is NOT NULL and l_others is null THEN
163 p_rec_type := 'U';
164 ELSIF l_others is not null and l_update is null THEN
165 p_rec_type := 'I';
166 ELSIF l_others is null and l_update is null THEN
167 p_rec_type := 'E';
168 END IF ;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
173 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.chk_per_rec_stat.exception','Unhandled exception in Procedure chk_per_rec_stat'||SQLERRM);
174 END IF;
175 END chk_per_rec_stat;
176
177
178
179 PROCEDURE check_person_terms ( p_fa_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
180 l_per_terms_match OUT NOCOPY BOOLEAN ) IS
181 /*
182 || Created By : masehgal
183 || Created On : 28-May-2003
184 || Purpose : check persons existing terms, new added coa items terms
185 || Known limitations, enhancements or remarks :
186 || Change History :
187 || Who When What
188 || (reverse chronological order - newest change first)
189 */
190
191 -- Select All COA Items for a person
192 CURSOR person_coa_items (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
193 SELECT DISTINCT item_code
194 FROM igf_aw_coa_items
195 WHERE base_id = cp_base_id ;
196 l_item_code person_coa_items%ROWTYPE;
197
198 -- Count all terms for a person in the system
199 CURSOR person_terms ( cp_base_id igf_aw_coa_itm_terms.base_id%TYPE ) IS
200 SELECT COUNT(DISTINCT (ld_sequence_number)) person_terms
201 FROM igf_aw_coa_itm_terms
202 WHERE base_id = cp_base_id ;
203 l_person_terms NUMBER;
204
205 -- Count all terms for a person for a COA Item
206 CURSOR person_coa_terms ( cp_base_id igf_aw_coa_itm_terms.base_id%TYPE ,
207 cp_item_code igf_aw_li_coa_ints.item_code%TYPE) IS
208 SELECT COUNT(DISTINCT(ld_sequence_number)) coa_terms
209 FROM igf_aw_coa_itm_terms
210 WHERE base_id = cp_base_id
211 AND item_code = cp_item_code ;
212 l_person_coa_terms NUMBER;
213
214
215 BEGIN
216 l_per_terms_match := TRUE ;
217 -- get total terms
218 OPEN person_terms ( p_fa_base_id );
219 FETCH person_terms INTO l_person_terms ;
220 IF person_terms%NOTFOUND THEN
221 l_person_terms := 0 ;
222 END IF ;
223 CLOSE person_terms ;
224
225 -- check person terms
226 -- get diferent item codes
227 FOR l_item_code IN person_coa_items ( p_fa_base_id )
228 LOOP
229 -- get term count for each coa item
230 OPEN person_coa_terms ( p_fa_base_id, l_item_code.item_code) ;
231 FETCH person_coa_terms INTO l_person_coa_terms ;
232 CLOSE person_coa_terms ;
233 IF l_person_terms <> l_person_coa_terms THEN
234 l_per_terms_match := FALSE ;
235 END IF ;
236 END LOOP ;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
241 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.check_person_terms.exception','Unhandled exception in Procedure check_person_terms'||SQLERRM);
242 END IF;
243 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
244 fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.CHECK_PERSON_TERMS');
245 igs_ge_msg_stack.add;
246 app_exception.raise_exception;
247
248 END check_person_terms ;
249
250
251 PROCEDURE check_dup_coa ( p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
252 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
253 p_dup_coa OUT NOCOPY BOOLEAN ) IS
254 /*
255 || Created By : masehgal
256 || Created On : 28-May-2003
257 || Purpose : check duplication of COA Item
258 || Known limitations, enhancements or remarks :
259 || Change History :
260 || Who When What
261 || (reverse chronological order - newest change first)
262 */
263
264 CURSOR chk_dup_coa ( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
265 cp_item_code igf_aw_coa_items.item_code%TYPE) IS
266 SELECT 1
267 FROM igf_aw_coa_items
268 WHERE base_id = cp_base_id
269 AND item_code = cp_item_code ;
270 l_count chk_dup_coa%ROWTYPE ;
271
272 BEGIN
273 OPEN chk_dup_coa ( p_base_id, p_item_code) ;
274 FETCH chk_dup_coa INTO l_count ;
275 IF chk_dup_coa%NOTFOUND THEN
276 p_dup_coa := FALSE ;
277 ELSE
278 p_dup_coa := TRUE ;
279 END IF ;
280 CLOSE chk_dup_coa ;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
285 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.check_dup_coa.exception','Unhandled exception in Procedure check_dup_coa'||SQLERRM);
286 END IF;
287 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
288 fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.CHECK_DUP_COA');
289 igs_ge_msg_stack.add;
290 app_exception.raise_exception;
291
292 END check_dup_coa ;
293
294
295
296 PROCEDURE check_dup_coa_term ( p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
297 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
298 p_ld_seq_num IN igs_ca_inst.sequence_number%TYPE,
299 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
300 p_dup_term OUT NOCOPY BOOLEAN ) IS
301 /*
302 || Created By : masehgal
303 || Created On : 28-May-2003
304 || Purpose : check duplication of COA Item Term
305 || Known limitations, enhancements or remarks :
306 || Change History :
307 || Who When What
308 || (reverse chronological order - newest change first)
309 */
310
311 CURSOR chk_dup_term ( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
312 cp_item_code igf_aw_coa_itm_terms.item_code%TYPE,
313 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
314 cp_ld_seq_num igs_ca_inst.sequence_number%TYPE) IS
315 SELECT 1
316 FROM igf_aw_coa_itm_terms
317 WHERE base_id = cp_base_id
318 AND item_code = cp_item_code
319 AND ld_cal_type = cp_ld_cal_type
320 AND ld_sequence_number = cp_ld_seq_num ;
321 l_count chk_dup_term%ROWTYPE ;
322
323 BEGIN
324 OPEN chk_dup_term ( p_base_id, p_item_code, p_ld_cal_type, p_ld_seq_num ) ;
325 FETCH chk_dup_term INTO l_count ;
326 IF chk_dup_term%NOTFOUND THEN
327 p_dup_term := FALSE ;
328 ELSE
329 p_dup_term := TRUE ;
330 END IF ;
331 CLOSE chk_dup_term ;
332
333 EXCEPTION
334 WHEN OTHERS THEN
335 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
336 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.check_dup_coa_term.exception','Unhandled exception in Procedure check_dup_coa_term'||SQLERRM);
337 END IF;
338 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
339 fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.CHECK_DUP_COA_TERM');
340 igs_ge_msg_stack.add;
341 app_exception.raise_exception;
342
343 END check_dup_coa_term ;
344
345
346 PROCEDURE delete_coa_terms ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE) IS
347 /*
348 || Created By : masehgal
349 || Created On : 28-May-2003
350 || Purpose : deletion of COA Terms
351 || Known limitations, enhancements or remarks :
352 || Change History :
353 || Who When What
354 || (reverse chronological order - newest change first)
355 */
356
357 CURSOR del_coa_terms( cp_base_id igf_aw_coa_itm_terms.base_id%TYPE) IS
358 SELECT rowid
359 FROM igf_aw_coa_itm_terms
360 WHERE base_id = cp_base_id ;
361 lv_rowid del_coa_terms%ROWTYPE;
362
363 BEGIN
364 FOR lv_rowid IN del_coa_terms ( p_base_id)
365 LOOP
366 igf_aw_coa_itm_terms_pkg.delete_row( x_rowid => lv_rowid.rowid);
367 END LOOP;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
372 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.delete_coa_terms.exception','Unhandled exception in Procedure delete_coa_terms'||SQLERRM);
373 END IF;
374 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
375 fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.DELETE_COA_TERMS');
376 igs_ge_msg_stack.add;
377 app_exception.raise_exception;
378
379 END delete_coa_terms ;
380
381
382
383 PROCEDURE delete_coa_items ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE) IS
384 /*
385 || Created By : masehgal
386 || Created On : 28-May-2003
387 || Purpose : deletion of COA Items
388 || Known limitations, enhancements or remarks :
389 || Change History :
390 || Who When What
391 || (reverse chronological order - newest change first)
392 */
393
394 CURSOR del_coa_items( cp_base_id igf_aw_coa_itm_terms.base_id%TYPE) IS --,
395 SELECT rowid
396 FROM igf_aw_coa_items
397 WHERE base_id = cp_base_id ;
398 lv_rowid del_coa_items%ROWTYPE;
399
400 BEGIN
401 FOR lv_rowid IN del_coa_items ( p_base_id) --, p_item_code)
402 LOOP
403 igf_aw_coa_items_pkg.delete_row( x_rowid => lv_rowid.rowid);
404 END LOOP;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
409 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.delete_coa_items.exception','Unhandled exception in Procedure delete_coa_items'||SQLERRM);
410 END IF;
411 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
412 fnd_message.set_token('NAME','IGF_AP_LG_COA_IMP.DELETE_COA_ITEMS');
413 igs_ge_msg_stack.add;
414 app_exception.raise_exception;
415
416 END delete_coa_items ;
417
418
419 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
420 retcode OUT NOCOPY NUMBER,
421 p_award_year IN VARCHAR2,
422 p_batch_num IN VARCHAR2,
423 p_delete_flag IN VARCHAR2 ) IS
424 /*
425 || Created By : masehgal
426 || Created On : 28-May-2003
427 || Purpose : Main - called from submitted request
428 || Known limitations, enhancements or remarks :
429 || Change History :
430 || Who When What
431 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
432 || (reverse chronological order - newest change first)
433 */
434
435 l_prof_set VARCHAR2(1) ;
436 g_terminate_process BOOLEAN := FALSE ;
437 g_skip_person BOOLEAN := FALSE ;
438 g_skip_record BOOLEAN := FALSE ;
439 g_skip_item_insert BOOLEAN := FALSE ;
440 g_award_year_status igf_ap_batch_aw_map.award_year_status_code%TYPE ;
441 g_sys_award_year igf_ap_batch_aw_map.sys_award_year%TYPE ;
442 l_alternate_code igs_ca_inst.alternate_code%TYPE ;
443 l_rec_processed NUMBER;
444 l_rec_imported NUMBER;
445 l_rec_error NUMBER;
446 l_last_person_number igf_aw_li_coa_ints.person_number%TYPE ;
447 l_item_setup_found BOOLEAN ;
448 l_fa_base_id igf_ap_fa_base_rec.base_id%TYPE;
449 l_person_id igf_ap_fa_base_rec.person_id%TYPE;
450 l_dup_item_found BOOLEAN;
451 l_dup_coa_found BOOLEAN;
452 l_per_terms_match BOOLEAN;
453 l_oss_terms_match BOOLEAN;
454 l_error igf_lookups_view.meaning%TYPE ;
455 l_person_number igf_lookups_view.meaning%TYPE ;
456 l_token VARCHAR2(60) ;
457 l_item_amount igf_aw_coa_items.amount%TYPE := 0;
458 lv_rowid ROWID ;
459 lv_term_rowid ROWID ;
460 l_last_coa igf_aw_coa_items.item_code%TYPE;
461 l_per_item_count NUMBER ;
462 l_batch_valid VARCHAR2(1) ;
463 l_rec_type VARCHAR2(1) ;
464 l_recs_deleted BOOLEAN ;
465 l_term_chk BOOLEAN ;
466 l_counter_flag BOOLEAN ;
467 -- masehgal latest ...
468 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
469 -- using person_all_skip flag for the same
470 g_person_all_skip BOOLEAN ;
471 -- this will get set as soon as any one record for a person is errored
472 -- will get reset for a new person
473
474
475 -- cursor to get sys award year and award year status
476 CURSOR c_get_stat IS
477 SELECT award_year_status_code, sys_award_year
478 FROM igf_ap_batch_aw_map map
479 WHERE map.ci_cal_type = g_ci_cal_type
480 AND map.ci_sequence_number = g_ci_sequence_number ;
481
482 -- cursor to get persons for import
483 CURSOR c_get_persons ( cp_alternate_code igf_aw_li_coa_ints.ci_alternate_code%TYPE,
484 cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
485 SELECT batch_num,
486 coaint_id,
487 ci_alternate_code,
488 person_number,
489 item_code,
490 pell_coa_amt,
491 alt_pell_expense_amt,
492 NVL(fixed_cost_flag,'N') fixed_cost_flag,
493 ld_alternate_code,
494 term_amt,
495 import_status_type,
496 import_record_type
497 FROM igf_aw_li_coa_ints
498 WHERE ci_alternate_code = cp_alternate_code
499 AND batch_num = cp_batch_num
500 AND import_status_type IN ('R','U')
501 ORDER BY person_number , item_code, ld_alternate_code;
502
503 person_rec c_get_persons%ROWTYPE ;
504
505 -- cursor to get alternate code for award year
506 CURSOR c_alternate_code( cp_ci_cal_type igs_ca_inst.cal_type%TYPE ,
507 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE ) IS
508 SELECT alternate_code
509 FROM igs_ca_inst
510 WHERE cal_type = cp_ci_cal_type
511 AND sequence_number = cp_ci_sequence_number ;
512
513 -- check COA Setup done
514 CURSOR c_chk_coa (p_item_code igf_aw_li_coa_ints.item_code%TYPE) IS
515 SELECT 1
516 FROM igf_aw_item
517 WHERE item_code = NVL(p_item_code, item_code)
518 AND rownum = 1;
519 l_coa_exist NUMBER ;
520
521 -- cursor for items update
522 CURSOR cur_get_items (cp_base_id igf_aw_coa_itm_terms.base_id%TYPE,
523 cp_item_code igf_aw_coa_itm_terms.item_code%TYPE) IS
524 SELECT items.rowid,items.*
525 FROM igf_aw_coa_items items
526 WHERE base_id = cp_base_id
527 AND item_code = cp_item_code ;
528 l_item_rec cur_get_items%ROWTYPE ;
529
530 CURSOR cur_get_cal_info ( cp_alternate_code igs_ca_inst.alternate_code%TYPE ) IS
531 SELECT cal_type, sequence_number
532 FROM igs_ca_inst
533 WHERE alternate_code = cp_alternate_code ;
534
535 l_load_cal_type igs_ca_inst.cal_type%TYPE ;
536 l_load_seq_num igs_ca_inst.sequence_number%TYPE ;
537
538 l_old_item igf_aw_coa_items.item_code%TYPE;
539
540 BEGIN
541 igf_aw_gen.set_org_id(NULL);
542 errbuf := NULL;
543 retcode := 0;
544 l_prof_set := 'N' ;
545 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
546 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
547
548
549 -- Check if the following profiles are set
550 l_prof_set := igf_ap_gen.check_profile ;
551
552 IF l_prof_set = 'Y' THEN
553 -- profiles properly set ....... proceed
554 /**************************
555 Batch Level Checks
556 **************************/
557
558 -- Get the Award Year Calender Type and the Sequence Number
559 g_ci_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
560 g_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
561
562 -- Get the Award Year Alternate Code
563 OPEN c_alternate_code( g_ci_cal_type, g_ci_sequence_number ) ;
564 FETCH c_alternate_code INTO l_alternate_code ;
565 CLOSE c_alternate_code ;
566
567 -- Log input params
568 log_input_params( p_batch_num, l_alternate_code , p_delete_flag);
569 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
570 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_coa_imp.main.debug','Completed input parameters logging in Procedure main');
571 END IF;
572
573 -- Get Award Year Status
574 OPEN c_get_stat ;
575 FETCH c_get_stat INTO g_award_year_status, g_sys_award_year ;
576 -- check validity of award year
577 IF c_get_stat%NOTFOUND THEN
578 -- Award Year setup tampered .... Log a message
579 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWD_YR_NOT_FOUND');
580 FND_MESSAGE.SET_TOKEN('P_AWARD_YEAR', l_alternate_code);
581 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
582 g_terminate_process := TRUE ;
583 ELSE
584 -- Award year exists but is it Open/Legacy Details .... check
585 IF g_award_year_status NOT IN ('O','LD') THEN
586 FND_MESSAGE.SET_NAME('IGF','IGF_AP_LG_INVALID_STAT');
587 FND_MESSAGE.SET_TOKEN('AWARD_STATUS', g_award_year_status);
588 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
589 g_terminate_process := TRUE ;
590 END IF ; -- awd ye open or legacy detail chk
591 END IF ; -- award year invalid check
592 CLOSE c_get_stat ;
593
594 -- check COA Setup
595 OPEN c_chk_coa ( NULL);
596 FETCH c_chk_coa INTO l_coa_exist ;
597 -- if no COA Item found
598 IF c_chk_coa%NOTFOUND THEN
599 FND_MESSAGE.SET_NAME('IGF','IGF_AP_COA_SETUP_INCOM');
600 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
601 -- set terminate flag
602 g_terminate_process := TRUE ;
603 END IF ; -- setup check in interface table
604 CLOSE c_chk_coa ;
605
606 -- check validity of batch
607 l_batch_valid := igf_ap_gen.check_batch ( p_batch_num, 'COA') ;
608 IF NVL(l_batch_valid,'N') <> 'Y' THEN
609 FND_MESSAGE.SET_NAME('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
610 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
611 g_terminate_process := TRUE ;
612 END IF;
613
614 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
615 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_coa_imp.main.debug','Completed batch validations in Procedure main');
616 END IF;
617
618 /***********************************************************************
619 Person Level checks
620 l_rec_processed flag to monitor the number of records in the batch
621 submitted for processing
622 l_last_person_number Holds the last processed Person Number
623 ***********************************************************************/
624
625 -- check for terminate flag
626 IF NOT g_terminate_process THEN
627 l_last_person_number := NULL ;
628 l_rec_processed := 0 ;
629 l_per_item_count := 0 ;
630 l_rec_imported := 0 ;
631
632 -- Set an initial savepoint
633 SAVEPOINT coa_person_recs ;
634 l_counter_flag := FALSE ;
635
636 -- Select persons from interface table
637 FOR person_rec IN c_get_persons (l_alternate_code, p_batch_num)
638 LOOP
639 -- validate each person
640 l_counter_flag := FALSE ;
641 g_skip_record := FALSE ;
642
643
644 -- check if this person has been processed before ....
645 -- if yes, then skip the person related validations re-check
646 IF person_rec.person_number <> NVL(l_last_person_number,'*') THEN
647 -- code here for person terms validations and rollback/commit
648 IF l_last_person_number IS NOT NULL THEN
649
650 IF l_per_item_count > 0 THEN -- only if some inserts have happened for the person
651
652 -- masehgal latest ...
653 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
654 -- using person_all_skip flag for the same
655 -- from here ....
656 IF g_person_all_skip THEN
657
658 ROLLBACK TO coa_person_recs ;
659
660 -- mark all person records as "E"
661 UPDATE igf_aw_li_coa_ints
662 SET import_status_type = 'E'
663 WHERE batch_num = p_batch_num
664 AND person_number = l_last_person_number ;
665
666 l_rec_imported := l_rec_imported - l_per_item_count;
667 COMMIT ;
668 g_skip_person := TRUE ;
669 ELSE
670
671 igf_aw_gen_003.updating_coa_in_fa_base(l_fa_base_id);
672
673 -- ELSE do the terms match check ...
674 -- This particular terms match check has to happen after the insertion of individual records
675 IF g_award_year_status = 'O' THEN
676
677 -- coa terms match
678 check_person_terms ( l_fa_base_id, l_per_terms_match) ;
679 IF NOT l_per_terms_match THEN
680
681 FND_MESSAGE.SET_NAME('IGF','IGF_AP_COA_TERM_DIFF');
682 g_log_tab_index := g_log_tab_index + 1;
683 g_log_tab(g_log_tab_index).person_number := l_last_person_number;
684 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
685
686 -- Now rollback ....
687 ROLLBACK TO coa_person_recs ;
688
689 -- mark all person records as "E"
690 UPDATE igf_aw_li_coa_ints
691 SET import_status_type = 'E'
692 WHERE batch_num = p_batch_num
693 AND person_number = l_last_person_number ;
694 l_rec_imported := l_rec_imported - l_per_item_count;
695 COMMIT ;
696 g_skip_person := TRUE ;
697 ELSE
698
699 -- commit for the person --- terms matched
700 COMMIT;
701 END IF ; -- person terms match
702 COMMIT; -- if award year is not open then no check for terms match ...direct commit
703 END IF ; -- award year status check
704 END IF ; -- no records errored check
705 END IF ;
706 l_counter_flag := FALSE;
707 END IF ;
708
709 -- new person ..
710 -- issue SAVEPOINT
711 SAVEPOINT coa_person_recs ;
712 -- set skip flag for the new person to FALSE
713 g_skip_person := FALSE ;
714 -- masehgal latest ...
715 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
716 -- using person_all_skip flag for the same
717 g_person_all_skip := FALSE ;
718
719 l_last_coa := NULL;
720 l_per_item_count := 0 ;
721 l_old_item := NULL ;
722 l_recs_deleted := FALSE ;
723
724 -- call procedure to check person existence and fa base rec existence
725 igf_ap_gen.check_person ( person_rec.person_number, g_ci_cal_type, g_ci_sequence_number,
726 l_person_id, l_fa_base_id) ;
727
728 IF l_person_id IS NULL THEN
729
730 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PE_NOT_EXIST');
731 g_log_tab_index := g_log_tab_index + 1;
732 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
733 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
734 g_skip_person := TRUE ;
735 ELSIF l_fa_base_id IS NULL THEN
736
737 FND_MESSAGE.SET_NAME('IGF','IGF_AP_FABASE_NOT_FOUND');
738 g_log_tab_index := g_log_tab_index + 1;
739 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
740 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
741 g_skip_person := TRUE ;
742 END IF ; -- person existence check
743
744 --check if ALL person records aer marked either for insert or for update
745 -- If not , log a message, skip the person
746 chk_per_rec_stat( p_batch_num, l_alternate_code, person_rec.person_number, l_rec_type ) ;
747
748 IF l_rec_type = 'E' THEN
749
750 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PER_RECS_NOT_SAME');
751 g_log_tab_index := g_log_tab_index + 1;
752 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
753 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
754 g_skip_person := TRUE ;
755 END IF ;
756
757
758
759 END IF ; -- person already processed check
760
761 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
762 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_coa_imp.main.debug','Completed person validations in Procedure main');
763 END IF;
764 /* End Of Person level Check */
765 /**************************************************
766 COA Item Level checks
767 ***************************************************/
768
769 -- Check for person skip flag
770 IF g_skip_person THEN
771
772 -- person skip flag set....
773 -- if flag set then the person related records aer to be marked as error records and skipped
774 -- update all person records to error status
775 UPDATE igf_aw_li_coa_ints
776 SET import_status_type = 'E'
777 WHERE batch_num = p_batch_num
778 AND person_number = person_rec.person_number ;
779
780 l_rec_imported := l_rec_imported - l_per_item_count;
781
782 COMMIT ;
783
784 ELSE -- person not to b skipped
785
786 -- Item level validations ...
787 l_token := person_rec.item_code || ' COAINT_ID - ' || TO_CHAR(person_rec.coaint_id) ;
788 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PROC_ITM');
789 FND_MESSAGE.SET_TOKEN('ITEM', l_token );
790 g_log_tab_index := g_log_tab_index + 1;
791 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
792 g_log_tab(g_log_tab_index).message_text := fnd_message.get;
793
794 -- coa item present in set up
795 OPEN c_chk_coa ( person_rec.item_code);
796 FETCH c_chk_coa INTO l_coa_exist ;
797 -- if no COA Item found
798
799 IF c_chk_coa%NOTFOUND THEN
800
801 FND_MESSAGE.SET_NAME('IGF','IGF_AP_COA_INVALID_ITM');
802 FND_MESSAGE.SET_TOKEN('ITEM', person_rec.item_code);
803 g_log_tab_index := g_log_tab_index + 1;
804 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
805 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
806 g_skip_record := TRUE ;
807 -- masehgal latest ...
808 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
809 -- using person_all_skip flag for the same
810 g_person_all_skip := TRUE ;
811
812 END IF ;
813 CLOSE c_chk_coa ;
814
815 /* End of COA Existence Check */
816 /******************************************
817 COA ITEM Instance related Checks
818 *******************************************/
819 l_term_chk := igf_ap_gen.validate_cal_inst( 'LOAD', l_alternate_code, person_rec.ld_alternate_code,
820 l_load_cal_type, l_load_seq_num) ;
821
822 IF (l_load_cal_type IS NULL OR l_load_seq_num IS NULL) THEN
823 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INVALID_TERM');
824 FND_MESSAGE.SET_TOKEN('TERM', person_rec.ld_alternate_code);
825 g_log_tab_index := g_log_tab_index + 1;
826 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
827 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
828 g_skip_record := TRUE ;
829
830 -- masehgal latest ...
831 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
832 -- using person_all_skip flag for the same
833 g_person_all_skip := TRUE ;
834
835 g_skip_item_insert := TRUE ;
836 ELSIF NOT l_term_chk THEN
837
838 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWD_TERM_INVALID');
839 FND_MESSAGE.SET_TOKEN('TERM', person_rec.ld_alternate_code);
840 FND_MESSAGE.SET_TOKEN('AWARD', l_alternate_code);
841 g_log_tab_index := g_log_tab_index + 1;
842 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
843 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
844 g_skip_record := TRUE ;
845 -- masehgal latest ...
846 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
847 -- using person_all_skip flag for the same
848 g_person_all_skip := TRUE ;
849
850 g_skip_item_insert := TRUE ;
851 END IF ; --
852
853 -- coa item duplicate
854 check_dup_coa ( person_rec.item_code, l_fa_base_id, l_dup_coa_found) ;
855 IF l_dup_coa_found AND l_rec_type <> 'U' THEN
856
857 -- no message for duplicate item as term may be different
858 g_skip_item_insert := TRUE ;
859 -- do not log a message for duplicate coa item ... only for coa term
860 ELSE
861
862 g_skip_item_insert := FALSE ;
863
864 END IF ;
865
866 IF (NOT l_dup_coa_found) AND (NOT l_recs_deleted) AND l_rec_type = 'U' THEN
867
868 -- log a message for duplicate
869 FND_MESSAGE.SET_NAME('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
870 g_log_tab_index := g_log_tab_index + 1;
871 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
872 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
873 g_skip_record := TRUE ;
874 -- masehgal latest ...
875 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
876 -- using person_all_skip flag for the same
877 g_person_all_skip := TRUE ;
878
879 g_skip_item_insert := TRUE ;
880 END IF ; --
881
882
883 -- coa item term duplicate
884 -- to be performed only if item is already present ...
885 IF l_dup_coa_found THEN
886
887 check_dup_coa_term ( person_rec.item_code, l_load_cal_type, l_load_seq_num, l_fa_base_id, l_dup_item_found) ;
888
889 IF l_dup_item_found AND l_rec_type <> 'U' THEN
890
891 -- log a message for duplicate
892 FND_MESSAGE.SET_NAME('IGF','IGF_AP_COA_ITM_TERM_EXIST');
893 FND_MESSAGE.SET_TOKEN('TERM', person_rec.ld_alternate_code);
894 g_log_tab_index := g_log_tab_index + 1;
895 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
896 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
897 g_skip_record := TRUE ;
898 -- masehgal latest ...
899 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
900 -- using person_all_skip flag for the same
901 g_person_all_skip := TRUE ;
902
903 END IF ;
904 IF (NOT l_dup_item_found) AND (NOT l_recs_deleted) AND l_rec_type = 'U' THEN
905
906 -- log a message for duplicate
907 FND_MESSAGE.SET_NAME('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
908 g_log_tab_index := g_log_tab_index + 1;
909 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
910 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
911 g_skip_record := TRUE ;
912 -- masehgal latest ...
913 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
914 -- using person_all_skip flag for the same
915 g_person_all_skip := TRUE ;
916
917 END IF ; --
918 END IF ;
919
920 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
921 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_coa_imp.main.debug','Completed record validations in Procedure main');
922 END IF;
923
924 -- all record validations done ...
925 -- now check for skip record flag
926 IF g_skip_record THEN
927
928 UPDATE igf_aw_li_coa_ints
929 SET import_status_type = 'E'
930 WHERE coaint_id = person_rec.coaint_id ;
931 -- COMMIT ;
932 ELSE
933
934 -- check if the person is meant to be updated or fresh insert
935 -- if updatd, delete the previously exisiting all coa items ad terms
936 IF l_rec_type = 'U' AND (NOT l_recs_deleted) THEN
937
938 -- records exist
939 -- have to be deleted
940
941 delete_coa_terms ( l_fa_base_id );
942
943 delete_coa_items ( l_fa_base_id );
944 -- post delete set a flag that shud prevent the existing coa check for the same person
945 l_recs_deleted := TRUE ;
946 END IF ;
947
948 -- Now add records
949 l_per_item_count := l_per_item_count + 1 ;
950
951
952 -- l_old_item_term := NULL ;
953
954 IF person_rec.item_code <> NVL ( l_old_item, '*') THEN
955
956 IF NOT g_skip_item_insert THEN
957
958 -- new item ... add item and then add terms
959 l_item_amount := 0 ;
960
961 BEGIN
962
963 igf_aw_coa_items_pkg.insert_row(
964 x_rowid => lv_rowid,
965 x_base_id => l_fa_base_id,
966 x_item_code => person_rec.item_code,
967 x_amount => l_item_amount,
968 x_pell_coa_amount => person_rec.pell_coa_amt,
969 x_alt_pell_amount => person_rec.alt_pell_expense_amt,
970 x_fixed_cost => person_rec.fixed_cost_flag,
971 x_legacy_record_flag => 'Y',
972 x_lock_flag => 'N',
973 x_mode => 'R');
974
975 EXCEPTION WHEN OTHERS THEN
976 -- Note : checking is done in tbh . so re-validation avoided
977 fnd_message.set_name('IGF','IGF_AW_INCON_ITM_TERMS');
978 g_log_tab_index := g_log_tab_index + 1;
979 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
980 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
981 g_skip_record := TRUE ;
982 g_person_all_skip := TRUE ;
983
984 END;
985
986 l_old_item := person_rec.item_code ;
987
988 END IF ; -- item insertion skip check
989 END IF ;
990
991 -- now insert all the pertaining terms for the item
992 IF person_rec.item_code = l_old_item THEN
993 -- insert into the terms table
994
995 BEGIN
996
997 igf_aw_coa_itm_terms_pkg.insert_row(
998 x_rowid => lv_term_rowid,
999 x_base_id => l_fa_base_id,
1000 x_item_code => person_rec.item_code,
1001 x_amount => person_rec.term_amt,
1002 x_ld_cal_type => l_load_cal_type,
1003 x_ld_sequence_number => l_load_seq_num,
1004 x_lock_flag => 'N',
1005 x_mode => 'R');
1006
1007 EXCEPTION WHEN OTHERS THEN
1008 -- Note : checking is done in tbh . so re-validation avoided
1009 fnd_message.set_name('IGF','IGF_AW_INCON_ITM_TERMS');
1010 g_log_tab_index := g_log_tab_index + 1;
1011 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
1012 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
1013 g_skip_record := TRUE ;
1014 g_person_all_skip := TRUE ;
1015
1016 END;
1017
1018
1019 -- increment the item amount by each term amount
1020 l_item_amount := NVL(l_item_amount,0) + person_rec.term_amt ;
1021 END IF ; -- new item check ...
1022
1023 -- move update after person term check
1024
1025 -- now update the item amount
1026 OPEN cur_get_items ( l_fa_base_id, person_rec.item_code ) ;
1027 FETCH cur_get_items INTO l_item_rec ;
1028 CLOSE cur_get_items ;
1029
1030 igf_aw_coa_items_pkg.update_row (
1031 x_rowid => l_item_rec.rowid,
1032 x_base_id => l_fa_base_id,
1033 x_item_code => l_item_rec.item_code,
1034 x_amount => l_item_amount,
1035 x_pell_coa_amount => l_item_rec.pell_coa_amount,
1036 x_alt_pell_amount => l_item_rec.alt_pell_amount,
1037 x_fixed_cost => l_item_rec.fixed_cost,
1038 x_legacy_record_flag => 'Y',
1039 x_lock_flag => 'N',
1040 x_mode => 'R' );
1041
1042
1043 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1044 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_coa_imp.main.debug','Inserted COA record in Procedure main');
1045 END IF;
1046
1047 -- now update the record status
1048 IF p_delete_flag = 'Y' THEN
1049 DELETE FROM igf_aw_li_coa_ints
1050 WHERE coaint_id = person_rec.coaint_id ;
1051
1052 -- COMMIT ;
1053 ELSE
1054
1055 UPDATE igf_aw_li_coa_ints
1056 SET import_status_type = 'I'
1057 WHERE coaint_id = person_rec.coaint_id ;
1058 l_counter_flag := TRUE;
1059
1060 -- COMMIT ;
1061 END IF ;
1062
1063 END IF ; -- skip record check
1064
1065 END IF ; -- person skip flag check
1066 -- Reset the Last Person Processed
1067 IF l_last_person_number IS NOT NULL THEN
1068 l_rec_processed := l_rec_processed + 1 ;
1069 END IF;
1070 l_last_person_number := person_rec.person_number;
1071
1072 IF l_counter_flag THEN
1073 l_rec_imported := l_rec_imported + 1 ;
1074 END IF;
1075
1076 END LOOP ; -- person selection loop
1077
1078 -- code here to check for terms of last person
1079
1080 IF l_last_person_number IS NOT NULL THEN
1081 l_rec_processed := l_rec_processed + 1 ;
1082
1083 IF l_per_item_count > 0 THEN -- only if some inserts have happened for the person
1084
1085 -- masehgal latest ...
1086 -- as soon as 1 record for a person is marked as error record, we need to skip the whole person
1087 -- using person_all_skip flag for the same
1088 -- from here ....
1089 IF g_person_all_skip THEN
1090
1091 ROLLBACK TO coa_person_recs ;
1092
1093 -- mark all person records as "E"
1094 UPDATE igf_aw_li_coa_ints
1095 SET import_status_type = 'E'
1096 WHERE batch_num = p_batch_num
1097 AND person_number = l_last_person_number ;
1098
1099
1100 l_rec_imported := l_rec_imported - l_per_item_count ;
1101 COMMIT ;
1102 g_skip_person := TRUE ;
1103 ELSE
1104
1105 igf_aw_gen_003.updating_coa_in_fa_base(l_fa_base_id);
1106
1107 -- ELSE do the terms match check ...
1108 -- This particular terms match check has to happen after the insertion of individual records
1109 IF g_award_year_status = 'O' THEN
1110 -- coa terms match
1111
1112 check_person_terms ( l_fa_base_id, l_per_terms_match) ;
1113
1114 IF NOT l_per_terms_match THEN
1115
1116 FND_MESSAGE.SET_NAME('IGF','IGF_AP_COA_TERM_DIFF');
1117 g_log_tab_index := g_log_tab_index + 1;
1118 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
1119 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
1120
1121 -- Now rollback ....
1122 ROLLBACK TO coa_person_recs ;
1123
1124 -- mark all person records as "E"
1125 UPDATE igf_aw_li_coa_ints
1126 SET import_status_type = 'E'
1127 WHERE batch_num = p_batch_num
1128 AND person_number = l_last_person_number ;
1129 -- AND item_code = person_rec.item_code;
1130
1131 l_rec_imported := l_rec_imported - l_per_item_count;
1132 COMMIT ;
1133
1134 g_skip_person := TRUE ;
1135 ELSE
1136 -- commit ofr last person whose terms matched
1137 COMMIT;
1138 END IF ; -- person terms match
1139 COMMIT; -- if award year is not open then no check for terms match ...direct commit
1140 END IF ; -- award year status check
1141 END IF ; -- any record errored check !!
1142 END IF ;
1143 END IF ; -- end of last person terms verification
1144
1145
1146
1147 IF l_rec_processed = 0 THEN
1148 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWDYR_STAT_NOT_EXISTS');
1149 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1150 ELSE
1151 -- CALL THE PRINT LOG PROCESS
1152 print_log_process(l_person_number,l_error);
1153 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_PROCESSED'),50)|| TO_CHAR(l_rec_processed) );
1154 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_FAILED'),50)|| TO_CHAR(l_rec_processed - l_rec_imported));
1155 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC'),50)|| TO_CHAR(l_rec_imported));
1156
1157 IF l_rec_imported = 0 THEN
1158 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ');
1159 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '-------------------------------------------------------------');
1160 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ');
1161 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_DATA_IMP' );
1162 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET);
1163 END IF ;
1164 END IF ;
1165
1166 END IF ; -- terminate flag check
1167
1168 ELSE -- profile check
1169 -- error message
1170 -- terminate the process .. no further processing
1171 FND_MESSAGE.SET_NAME('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
1172 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1173 END IF ; -- profile check ends
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 ROLLBACK;
1178 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1179 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_coa_imp.main.exception','Unhandled exception in Procedure main'||SQLERRM);
1180 END IF;
1181 RETCODE := 2 ;
1182 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP') ;
1183 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_LG_COA_IMP.MAIN') ;
1184 errbuf := FND_MESSAGE.GET ;
1185 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
1186
1187 END main ;
1188
1189 END igf_ap_lg_coa_imp ;
[Home] [Help]