[Home] [Help]
82: AND perd.start_date = p_period_start_date ;
83:
84: BEGIN
85: g_proc := 'check_multiple_stmt';
86: hr_utility.set_location('Entering '||g_proc,10);
87: g_actn := 'check_multiple_stmt: ' ||p_person_id||'statement id : '|| p_stmt_id
88: || 'period start date :' ||p_period_start_date || 'period end date : ' || p_period_end_date ;
89: WRITE (g_actn );
90: WRITE('In procedure :' || g_proc);
90: WRITE('In procedure :' || g_proc);
91:
92: OPEN c_person_stmt (p_person_id , p_period_start_date ,p_period_end_date ,p_stmt_id);
93: IF c_person_stmt%FOUND THEN
94: hr_utility.set_location('return True From '||g_proc,10.1);
95: return TRUE;
96: ELSE
97: hr_utility.set_location('return False From '||g_proc,10.2);
98: return false;
93: IF c_person_stmt%FOUND THEN
94: hr_utility.set_location('return True From '||g_proc,10.1);
95: return TRUE;
96: ELSE
97: hr_utility.set_location('return False From '||g_proc,10.2);
98: return false;
99: END IF;
100:
101: hr_utility.set_location('Leaving '||g_proc,11);
97: hr_utility.set_location('return False From '||g_proc,10.2);
98: return false;
99: END IF;
100:
101: hr_utility.set_location('Leaving '||g_proc,11);
102: WRITE('Leaving check_multiple_stmt');
103: END check_multiple_stmt;
104:
105: --
118: IS
119: BEGIN
120: g_proc := 'get_name';
121: WRITE('In procedure :' || g_proc);
122: hr_utility.set_location('Entering '||g_proc,20);
123:
124: SELECT bg.name bg_name
125: INTO p_bg_name
126: FROM per_business_groups_perf bg
138: AND v_period_end_date >= eligy.effective_start_date(+)
139: AND (eligy.effective_end_date IS NULL OR eligy.effective_end_date >= v_period_end_date) ;
140: END IF;
141:
142: hr_utility.set_location('Leaving '||g_proc,21);
143: WRITE('Leaving get_name');
144: END get_name;
145:
146: --
161: IS
162: BEGIN
163: g_proc := 'get_emp_detail';
164: WRITE('In procedure :' || g_proc);
165: hr_utility.set_location('Entering '||g_proc,30);
166:
167: IF ( p_assignment_id IS NOT NULL) THEN
168: SELECT jobs.name
169: INTO p_job
189: AND p_period_end_date >= bg.date_from
190: AND ( bg.date_to IS NULL OR bg.date_to >= p_period_end_date);
191:
192: WRITE('Leaving get_emp_detail');
193: hr_utility.set_location('Leaving '||g_proc,31);
194: END get_emp_detail;
195:
196: --
197: -- ============================================================================
211: BEGIN
212: g_proc := 'print_cache';
213: WRITE ('Time before printing cache '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
214: WRITE ('In Procedure' ||g_proc);
215: hr_utility.set_location('Entering '||g_proc,40);
216:
217: FOR i IN 1 .. l_rep_rec.COUNT
218: LOOP
219: IF (l_rep_rec(i).P_TYPE = -1) THEN
276: l_rep_rec(i).ERROR
277: );
278: END IF;
279: END LOOP;
280: hr_utility.set_location('Leaving '||g_proc,41);
281: WRITE('Leaving print_cache');
282: END print_cache;
283:
284:
377:
378: BEGIN
379: g_proc := 'Hierarchy set - hrchy_set';
380: WRITE('In procedure :' || g_proc);
381: hr_utility.set_location('Entering '||g_proc,50);
382:
383: OPEN suc_ppl(p_benefit_action_id ) ;
384: FETCH suc_ppl INTO g_exec_param_rec.persons_proc_succ ;
385: CLOSE suc_ppl;
391: SAVEPOINT HRCHY_GEN;
392: IF (g_run_type = 'GEN') THEN
393: IF ( g_exec_param_rec.persons_proc_succ > 0) THEN
394: processed_cat.DELETE;
395: hr_utility.set_location('Inside Hierarchy Generation ',51);
396: FOR i IN 1 .. l_rep_rec.COUNT
397: LOOP
398: l_stmt_id := null;
399: IF (l_rep_rec(i).P_TYPE = -1) THEN
402: INTO stmt_perd_rec ;
403: IF (c_stmt_period%FOUND) THEN
404: IF (stmt_perd_rec.stmt_id IS NOT NULL ) THEN
405: -- WRITE('Updating the statement generated flag .Stmt Id :'|| stmt_perd_rec.stmt_id );
406: hr_utility.set_location('Updating the statement generated flag .Stmt Id : ',52);
407: UPDATE BEN_TCS_STMT
408: SET stmt_generated_flag = 'Y'
409: WHERE stmt_id = stmt_perd_rec.stmt_id ;
410: END IF;
409: WHERE stmt_id = stmt_perd_rec.stmt_id ;
410: END IF;
411: IF (stmt_perd_rec.stmt_perd_id IS NOT NULL ) THEN
412: --WRITE('Updating the statement generated flag .Stmt Period Id :'|| stmt_perd_rec.stmt_perd_id );
413: hr_utility.set_location('Updating the statement generated flag .Stmt Period Id : ',53);
414: UPDATE BEN_TCS_STMT_PERD
415: SET stmt_generated_flag = 'STMTGEN'
416: WHERE stmt_perd_id = stmt_perd_rec.stmt_perd_id ;
417: END IF;
419: CLOSE c_stmt_period ;
420:
421: END IF;
422: END LOOP;
423: hr_utility.set_location('before deleting from cat_item_hrchy: ',54);
424: FOR i IN 1.. item_hrchy_values.COUNT
425: LOOP
426: OPEN c_get_item (item_hrchy_values(i).item_id, item_hrchy_values(i).perd_id );
427: FETCH c_get_item
433: AND stmt_perd_id = item_hrchy_values(i).perd_id ;
434: END IF;
435: CLOSE c_get_item ;
436: END LOOP;
437: hr_utility.set_location('before inserting into cat_item_hrchy: ',55);
438: FOR i IN 1.. item_hrchy_values.COUNT
439: LOOP
440: item_id_found := null;
441: OPEN c_get_item (item_hrchy_values(i).item_id, item_hrchy_values(i).perd_id );
461: cat_cnt := cat_cnt +1;
462:
463: IF (item_hrchy_values(i).lvl_num = 1) THEN
464:
465: hr_utility.set_location('Updating the statement generated flag .all_objects_in_cat_id:'|| item_hrchy_values(i).all_objects_id ,56);
466: UPDATE BEN_TCS_ALL_OBJECTS_IN_CAT
467: SET stmt_generated_flag = 'Y'
468: WHERE all_objects_in_cat_id = item_hrchy_values(i).all_objects_id;
469:
466: UPDATE BEN_TCS_ALL_OBJECTS_IN_CAT
467: SET stmt_generated_flag = 'Y'
468: WHERE all_objects_in_cat_id = item_hrchy_values(i).all_objects_id;
469:
470: hr_utility.set_location('Updating the statement generated flag .row_in_cat_id:'|| item_hrchy_values(i).row_cat_id,57);
471: UPDATE BEN_TCS_ROW_IN_CAT
472: SET stmt_generated = 'Y'
473: WHERE row_in_cat_id =item_hrchy_values(i).row_cat_id;
474: END IF;
475: END IF;
476: CLOSE c_get_item ;
477:
478: END LOOP;
479: hr_utility.set_location('after inserting into cat_item_hrchy: ',58);
480: FOR i IN 1.. subcat_hrchy_values.COUNT
481: LOOP
482:
483: SELECT cat_type_cd into cat_type
488: cat_cnt := cat_cnt +1;
489: END IF;
490:
491: END LOOP;
492: hr_utility.set_location('before deleting from cat_subcat_hrchy: ',59);
493: FOR i IN 1.. subcat_hrchy_values.COUNT
494: LOOP
495: cat_id_found := null;
496: FOR j IN 1.. processed_cat.COUNT
508: WHERE sub_cat_id =subcat_hrchy_values(i).subcat_id
509: AND stmt_perd_id = subcat_hrchy_values(i).perd_id ;
510: END IF;
511: END LOOP;
512: hr_utility.set_location('before inserting into cat_subcat_hrchy: ',60);
513: FOR i IN 1.. subcat_hrchy_values.COUNT
514: LOOP
515: cat_id_found := null;
516: cat_entry_found := null;
534: subcat_hrchy_values(i).lvl_num,
535: subcat_hrchy_values(i).row_cat_id,
536: subcat_hrchy_values(i).perd_id );
537: IF ( subcat_hrchy_values(i).lvl_num =1) THEN
538: hr_utility.set_location('Updating the statement generated flag :sub_cat .row_in_cat_id:'|| subcat_hrchy_values(i).row_cat_id, 61);
539: UPDATE BEN_TCS_ROW_IN_CAT
540: SET stmt_generated = 'Y'
541: WHERE row_in_cat_id = subcat_hrchy_values(i).row_cat_id;
542: END IF;
561: OPEN c_per_stmt_period (l_rep_rec(i).PERIOD_ID );
562: FETCH c_per_stmt_period
563: INTO l_perd_id ;
564: IF (c_per_stmt_period%NOTFOUND) THEN
565: hr_utility.set_location('Statement Generated Flag reset for period' || l_rep_rec(i).PERIOD_ID ,62);
566: UPDATE BEN_TCS_STMT_PERD
567: SET stmt_generated_flag = 'STMTNGEN'
568: WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID ;
569:
588: row_cnt := row_cnt +1;
589: END LOOP;
590: CLOSE c_cat_hrchy_val;
591:
592: hr_utility.set_location('Deleting Item Hrchy for period' || l_rep_rec(i).PERIOD_ID ,63);
593: DELETE
594: FROM ben_tcs_cat_item_hrchy
595: WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
596:
593: DELETE
594: FROM ben_tcs_cat_item_hrchy
595: WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
596:
597: hr_utility.set_location('Deleting Subcat Hrchy for period' || l_rep_rec(i).PERIOD_ID ,64);
598: DELETE
599: FROM ben_tcs_cat_subcat_hrchy
600: WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
601:
603: LOOP
604: OPEN c_row_cat_id(row_in_cat(j) ,l_rep_rec(i).PERIOD_ID);
605: FETCH c_row_cat_id INTO l_row_id;
606: IF (c_row_cat_id % NOTFOUND) THEN
607: hr_utility.set_location('Stmt Generated flag reset for row' || row_in_cat(j),65);
608: UPDATE ben_tcs_row_in_cat
609: SET stmt_generated = null
610: WHERE row_in_cat_id = row_in_cat(j);
611: END IF;
615: LOOP
616: OPEN c_obj_cat_id(all_obj_cat(j),l_rep_rec(i).PERIOD_ID);
617: FETCH c_obj_cat_id INTO l_obj_id;
618: IF (c_obj_cat_id % NOTFOUND) THEN
619: hr_utility.set_location('Stmt Generated flag reset for obj' || all_obj_cat(j),66);
620: UPDATE ben_tcs_all_objects_in_cat
621: SET stmt_generated_flag = null
622: WHERE all_objects_in_cat_id = all_obj_cat(j);
623: END IF;
630: OPEN c_per_stmt(l_rep_rec(i).STMT_ID );
631: FETCH c_per_stmt
632: INTO l_stmt_id ;
633: IF (c_per_stmt%NOTFOUND) THEN
634: hr_utility.set_location('Stmt Generated flag reset for stmt' || l_rep_rec(i).STMT_ID,67);
635: UPDATE BEN_TCS_STMT
636: SET stmt_generated_flag = 'N'
637: WHERE stmt_id = l_rep_rec(i).STMT_ID;
638: END IF;
643: IF (g_validate = 'Y') THEN
644: ROLLBACK to HRCHY_GEN;
645: END IF;
646: END IF;
647: hr_utility.set_location('Leaving '||g_proc,68);
648: WRITE('Leaving hrchy_set');
649: END hrchy_set;
650:
651: PROCEDURE delete_hrchy
671: l_object_version_number NUMBER;
672:
673: BEGIN
674: g_proc := 'end_process';
675: hr_utility.set_location('Leaving '||g_proc,69);
676: --
677: -- Get totals for unprocessed, processed successfully and errored
678: --
679:
719: -- print_cache;
720: delete_hrchy;
721: COMMIT;
722:
723: hr_utility.set_location('Leaving '||g_proc,70);
724: WRITE('Leaving end_process');
725: END end_process;
726:
727:
777: asg_rec c_asg_stmt%ROWTYPE;
778: BEGIN
779: g_proc := 'purge_person_stmt';
780: WRITE ('In Procedure' ||g_proc);
781: hr_utility.set_location('Entering '||g_proc,71);
782:
783: WRITE ('===========purge statement for the person============');
784: WRITE ('||Person Id ' || p_person_id);
785: WRITE ('||Statement id ' || p_stmt_id);
794:
795: FETCH c_per_stmt_perd INTO l_per_stmt_perd;
796: EXIT WHEN c_per_stmt_perd %NOTFOUND;
797:
798: hr_utility.set_location ( 'delete per_stmt_perd_id entry for per_stmt_perd_id :' || l_per_stmt_perd,72);
799:
800: DELETE
801: FROM ben_tcs_per_stmt_perd
802: WHERE per_stmt_perd_id = l_per_stmt_perd;
813: FETCH c_per_item
814: INTO l_per_item ;
815: EXIT WHEN c_per_item%NOTFOUND;
816:
817: hr_utility.set_location ( 'delete ben_tcs_per_item_value and ben_tcs_per_item entry for per_item_id :' || l_per_item,73);
818: DELETE FROM ben_tcs_per_item_value
819: WHERE per_item_id = l_per_item;
820:
821: DELETE FROM ben_tcs_per_item
821: DELETE FROM ben_tcs_per_item
822: WHERE per_item_id = l_per_item;
823: END LOOP;
824:
825: hr_utility.set_location ( 'delete ben_tcs_asg_stmt entry for ASG_STMT_ID :' || asg_rec.asg_stmt_id,74);
826: DELETE FROM
827: ben_tcs_asg_stmt
828: WHERE ASG_STMT_ID = asg_rec.asg_stmt_id;
829:
866: ROLLBACK TO Purge;
867: END IF;
868:
869: WRITE('leaving purge ...');
870: hr_utility.set_location('Leaving '||g_proc,75);
871: EXCEPTION
872: WHEN no_data_found THEN
873: WRITE('No stmt yet generated');
874: WRITE ('Leaving purge_person_stmt');
971: BEGIN
972:
973: g_proc := 'stmt_generation';
974: WRITE ('In Procedure' ||g_proc);
975: hr_utility.set_location('Entering '||g_proc,76);
976:
977: WRITE ('===========statement generation for the person============');
978: WRITE ('||Person Id ' || p_person_id);
979: WRITE ('||Statement id ' || p_stmt_id);
982: WRITE ('=======================================================');
983:
984: SAVEPOINT generation;
985:
986: hr_utility.set_location( 'calling purge ...',77 );
987: purge_person_stmt(
988: p_validate => p_validate ,
989: p_person_id => p_person_id,
990: p_stmt_id => p_stmt_id,
994: p_business_group =>p_business_group,
995: p_period_end_date => p_period_end_date,
996: p_run_type =>p_run_type);
997:
998: hr_utility.set_location('Calling check_multiple_stmt ',78);
999: IF (check_multiple_stmt (
1000: p_person_id => p_person_id,
1001: p_stmt_id => p_stmt_id ,
1002: p_period_start_date => p_start_date,
1003: p_period_end_date => p_period_end_date) = true )
1004: THEN
1005: WRITE('Statement already exists for this period date');
1006: ELSE
1007: hr_utility.set_location('Before assignment cursor',79);
1008: OPEN c_assignment_selection(p_person_id,p_start_date,p_period_end_date);
1009: hr_utility.set_location('After assignment cursor',80 );
1010: LOOP
1011: person_created_now := 'N';
1005: WRITE('Statement already exists for this period date');
1006: ELSE
1007: hr_utility.set_location('Before assignment cursor',79);
1008: OPEN c_assignment_selection(p_person_id,p_start_date,p_period_end_date);
1009: hr_utility.set_location('After assignment cursor',80 );
1010: LOOP
1011: person_created_now := 'N';
1012: Record_Created := 'N';
1013: cntr_item := 'N';
1014: FETCH c_assignment_selection
1015: INTO l_assign;
1016: EXIT WHEN c_assignment_selection%NOTFOUND;
1017: SAVEPOINT assign_details;
1018: hr_utility.set_location('Processing assignment_number id '|| l_assign.assignment_id,81);
1019: gen := 0;
1020: OPEN c_item (p_stmt_id ) ;
1021: LOOP
1022: FETCH c_item INTO item_id_process ;
1021: LOOP
1022: FETCH c_item INTO item_id_process ;
1023: EXIT WHEN c_item%NOTFOUND;
1024: IF ( item_id_process.item_id <> -1 ) THEN
1025: hr_utility.set_location('Processing item_id ' ||item_id_process.item_id,82);
1026: SELECT source_cd , source_key ,uom ,nnmntry_uom,comp_type_cd,name
1027: INTO l_source_cd , l_source_key ,l_uom ,l_nnmntry_uom,l_comp_type_cd ,item_name
1028: FROM ben_tcs_item
1029: WHERE item_id = item_id_process.item_id;
1027: INTO l_source_cd , l_source_key ,l_uom ,l_nnmntry_uom,l_comp_type_cd ,item_name
1028: FROM ben_tcs_item
1029: WHERE item_id = item_id_process.item_id;
1030:
1031: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1032: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1033: hr_utility.set_location('Person Id' || p_person_id,85);
1034: -- hr_utility.trace_on(null,'tcs');
1035: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1028: FROM ben_tcs_item
1029: WHERE item_id = item_id_process.item_id;
1030:
1031: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1032: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1033: hr_utility.set_location('Person Id' || p_person_id,85);
1034: -- hr_utility.trace_on(null,'tcs');
1035: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1036: p_source_key => l_source_key,
1029: WHERE item_id = item_id_process.item_id;
1030:
1031: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1032: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1033: hr_utility.set_location('Person Id' || p_person_id,85);
1034: -- hr_utility.trace_on(null,'tcs');
1035: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1036: p_source_key => l_source_key,
1037: p_perd_st_dt => p_start_date,
1030:
1031: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1032: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1033: hr_utility.set_location('Person Id' || p_person_id,85);
1034: -- hr_utility.trace_on(null,'tcs');
1035: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1036: p_source_key => l_source_key,
1037: p_perd_st_dt => p_start_date,
1038: p_perd_en_dt => p_period_end_date,
1044: p_effective_date => p_period_end_date ,
1045: p_result => l_result,
1046: p_status => l_status);
1047:
1048: hr_utility.set_location('After BEN_TCS_COMPENSATION.get_value_for_item call',86);
1049: -- hr_utility.trace_off;
1050: IF ( l_status = '0') THEN
1051: IF (l_result.count > 0) THEN
1052: IF (item_id_process.cntr_cd = 'ER' OR item_id_process.cntr_cd = 'EE' ) THEN
1045: p_result => l_result,
1046: p_status => l_status);
1047:
1048: hr_utility.set_location('After BEN_TCS_COMPENSATION.get_value_for_item call',86);
1049: -- hr_utility.trace_off;
1050: IF ( l_status = '0') THEN
1051: IF (l_result.count > 0) THEN
1052: IF (item_id_process.cntr_cd = 'ER' OR item_id_process.cntr_cd = 'EE' ) THEN
1053: cntr_item := 'Y';
1058: FOR i IN 1..l_result.count
1059: LOOP
1060: IF (i =1 ) THEN
1061: IF l_count = 0 AND gen = 0 THEN
1062: hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1063: p_person_id|| p_stmt_perd_id||p_stmt_id,87);
1064:
1065: l_person_inc := 1;
1066: l_count := l_count + 1;
1081: p_period_end_date);
1082:
1083: person_created_now := 'Y';
1084:
1085: hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',88);
1086:
1087: END IF;
1088:
1089: IF gen = 0 THEN
1087: END IF;
1088:
1089: IF gen = 0 THEN
1090:
1091: hr_utility.set_location('Inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id,89);
1092: INSERT INTO ben_tcs_asg_stmt(
1093: asg_stmt_id ,
1094: stmt_id ,
1095: assignment_id,
1101: l_assign.assignment_id,
1102: ben_tcs_per_stmt_perd_s.CURRVAL,
1103: l_assign.assignment_number);
1104:
1105: hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',90);
1106: IF (Record_Created ='N') THEN
1107: get_emp_detail(
1108: l_assign.assignment_id ,
1109: p_person_id ,
1134:
1135: l_uom := l_result(i).currency_cd;
1136: l_nnmntry_uom := l_result(i).uom;
1137:
1138: hr_utility.set_location('Inserting INTO ben_tcs_per_item item_id '||item_id_process.item_id,91);
1139:
1140: INSERT INTO ben_tcs_per_item
1141: ( per_item_id,
1142: asg_stmt_id ,
1157: l_assign.assignment_id ,
1158: l_uom ,
1159: l_nnmntry_uom );
1160:
1161: hr_utility.set_location(' After inserting INTO ben_tcs_per_item ',92);
1162: END IF;
1163: IF (l_source_cd = 'BB' OR l_source_cd = 'RULE' OR l_source_cd = 'PAYCOSTG' ) THEN
1164: hr_utility.set_location(' Inserting INTO ben_tcs_per_item_value ' ,92.1);
1165: IF (l_comp_type_cd = 'DATE') THEN
1160:
1161: hr_utility.set_location(' After inserting INTO ben_tcs_per_item ',92);
1162: END IF;
1163: IF (l_source_cd = 'BB' OR l_source_cd = 'RULE' OR l_source_cd = 'PAYCOSTG' ) THEN
1164: hr_utility.set_location(' Inserting INTO ben_tcs_per_item_value ' ,92.1);
1165: IF (l_comp_type_cd = 'DATE') THEN
1166: IF( l_source_cd = 'PAYCOSTG' OR l_source_cd = 'BB' ) THEN
1167: WRITE('Data Type Mismatch for the item :' || item_name ||
1168: 'Expected Type : ' || l_comp_type_cd|| ' Actual type :' || l_result(i).actual_uom);
1279: EXIT ;
1280: END;
1281: END IF;
1282:
1283: hr_utility.set_location(' After inserting INTO ben_tcs_per_item_value ',93 );
1284: --vkodedal 7012521 Run Result ER
1285: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' or l_source_cd = 'RR') THEN
1286:
1287: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1283: hr_utility.set_location(' After inserting INTO ben_tcs_per_item_value ',93 );
1284: --vkodedal 7012521 Run Result ER
1285: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' or l_source_cd = 'RR') THEN
1286:
1287: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1288: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1289: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1290: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1291: IF (l_comp_type_cd = 'DATE') THEN
1284: --vkodedal 7012521 Run Result ER
1285: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' or l_source_cd = 'RR') THEN
1286:
1287: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1288: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1289: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1290: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1291: IF (l_comp_type_cd = 'DATE') THEN
1292: IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1285: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' or l_source_cd = 'RR') THEN
1286:
1287: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1288: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1289: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1290: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1291: IF (l_comp_type_cd = 'DATE') THEN
1292: IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1293:
1286:
1287: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1288: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1289: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1290: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1291: IF (l_comp_type_cd = 'DATE') THEN
1292: IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1293:
1294: WRITE('Data Type Mismatch for the item :' || item_name ||
1438: END IF;
1439: Rollback_Person := 'Y' ;
1440: END IF;
1441: ELSE
1442: hr_utility.set_location('stock options extended subcategory',94);
1443: get_emp_detail(
1444: l_assign.assignment_id ,
1445: p_person_id ,
1446: p_period_end_date,
1450: p_bg ) ;
1451: OPEN c_stk_opts (p_person_id , p_stmt_perd_id , p_period_end_date , p_business_group,p_emp_num );
1452: FETCH c_stk_opts INTO stk_opts_rec ;
1453: IF ( stk_opts_rec.total IS NOT NULL ) THEN
1454: hr_utility.set_location('Person has value for stock options extended subcategory',95);
1455: cntr_item := 'Y';
1456: IF l_count = 0 AND gen = 0 THEN
1457: hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1458: p_person_id|| p_stmt_perd_id||p_stmt_id ||' subcategory part' ,96);
1453: IF ( stk_opts_rec.total IS NOT NULL ) THEN
1454: hr_utility.set_location('Person has value for stock options extended subcategory',95);
1455: cntr_item := 'Y';
1456: IF l_count = 0 AND gen = 0 THEN
1457: hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1458: p_person_id|| p_stmt_perd_id||p_stmt_id ||' subcategory part' ,96);
1459:
1460: l_person_inc := 1;
1461: l_count := l_count + 1;
1474: p_person_id,
1475: null,
1476: p_period_end_date);
1477:
1478: hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',97);
1479: END IF;
1480:
1481: IF gen = 0 THEN
1482:
1479: END IF;
1480:
1481: IF gen = 0 THEN
1482:
1483: hr_utility.set_location('inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id||' subcategory',98);
1484: INSERT INTO ben_tcs_asg_stmt(
1485: asg_stmt_id ,
1486: stmt_id ,
1487: assignment_id,
1493: l_assign.assignment_id,
1494: ben_tcs_per_stmt_perd_s.CURRVAL,
1495: l_assign.assignment_number);
1496:
1497: hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',99);
1498: IF (Record_Created ='N') THEN
1499: rep_count := l_rep_rec.COUNT +1;
1500: l_rep_rec(rep_count ).p_TYPE := 0;
1501: l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1520: END LOOP;
1521:
1522: IF (Record_Created ='N') THEN
1523:
1524: hr_utility.set_location(' no report',100);
1525: get_emp_detail(
1526: l_assign.assignment_id ,
1527: p_person_id ,
1528: p_period_end_date,
1571:
1572: IF ( Rollback_Person = 'Y') THEN
1573: ROLLBACK TO generation;
1574:
1575: hr_utility.set_location('initial cnt' || l_initial_count,101);
1576: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,102);
1577:
1578: FOR i in l_initial_count .. l_rep_rec.COUNT
1579: LOOP
1572: IF ( Rollback_Person = 'Y') THEN
1573: ROLLBACK TO generation;
1574:
1575: hr_utility.set_location('initial cnt' || l_initial_count,101);
1576: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,102);
1577:
1578: FOR i in l_initial_count .. l_rep_rec.COUNT
1579: LOOP
1580: IF ( l_rep_rec(i).STMT_CREATED = 'Y' OR l_rep_rec(i).STMT_CREATED = 'N' ) THEN
1585: WRITE('person rolled back due to error ');
1586: END If;
1587:
1588: WRITE ('Leaving stmt_generation');
1589: --hr_utility.trace_off;
1590: hr_utility.set_location('Leaving '||g_proc,103);
1591:
1592: EXCEPTION
1593:
1586: END If;
1587:
1588: WRITE ('Leaving stmt_generation');
1589: --hr_utility.trace_off;
1590: hr_utility.set_location('Leaving '||g_proc,103);
1591:
1592: EXCEPTION
1593:
1594: WHEN OTHERS
1592: EXCEPTION
1593:
1594: WHEN OTHERS
1595: THEN
1596: -- hr_utility.trace_off;
1597: IF (Record_Created ='N') THEN
1598: get_emp_detail(
1599: l_assign.assignment_id ,
1600: p_person_id ,
1621: l_rep_rec(rep_count ).STMT_CREATED := 'E';
1622:
1623: ROLLBACK TO generation;
1624:
1625: hr_utility.set_location('initial cnt' || l_initial_count,104);
1626: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,105);
1627:
1628: FOR i IN l_initial_count .. l_rep_rec.COUNT
1629: LOOP
1622:
1623: ROLLBACK TO generation;
1624:
1625: hr_utility.set_location('initial cnt' || l_initial_count,104);
1626: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,105);
1627:
1628: FOR i IN l_initial_count .. l_rep_rec.COUNT
1629: LOOP
1630: IF ( l_rep_rec(i).STMT_CREATED = 'N' OR l_rep_rec(i).STMT_CREATED = 'Y' ) THEN
1664:
1665: BEGIN
1666:
1667: g_proc := 'set_wlcm_flag';
1668: hr_utility.set_location('Entering '||g_proc,106);
1669: g_actn := 'updating ben_tcs_per_stmt_perd' ;
1670:
1671: WRITE ('In Procedure' ||g_proc);
1672: WRITE ('===========Reset welcome flag for the person============');
1677: WRITE ('=======================================================');
1678:
1679: SAVEPOINT setFlag;
1680:
1681: hr_utility.set_location('statement id : '||p_stmt_id || ' statement period id :' ||p_stmt_perd_id
1682: || 'person id :' ||p_person_id,107);
1683:
1684: SELECT show_wlcm_pg_flag
1685: INTO wlcm_flag
1688: AND stmt_perd_id = p_stmt_perd_id
1689: AND person_id = p_person_id ;
1690:
1691: IF (wlcm_flag = 'N') THEN
1692: hr_utility.set_location('Updating the welcome flag for person id : ' || p_person_id || ' : ' ||p_period_end_date ,108);
1693:
1694: get_emp_detail(
1695: null ,
1696: p_person_id ,
1726: ROLLBACK TO setFlag;
1727: END IF;
1728:
1729: WRITE ('Leaving set_wlcm_flag');
1730: hr_utility.set_location('Leaving '||g_proc,109);
1731: EXCEPTION
1732: WHEN OTHERS
1733: THEN
1734: l_rep_rec(rep_count ).STMT_CREATED := 'E';
1816: BEGIN
1817:
1818: g_actn := 'Started do_multithread for the thread ' || p_thread_id;
1819: g_proc := 'do_multithread';
1820: hr_utility.set_location('Entering '||g_proc,110);
1821:
1822: benutils.g_benefit_action_id := p_benefit_action_id;
1823:
1824: WRITE ('procedure :' ||g_proc );
1821:
1822: benutils.g_benefit_action_id := p_benefit_action_id;
1823:
1824: WRITE ('procedure :' ||g_proc );
1825: hr_utility.set_location (g_actn,111);
1826: WRITE ('=====================do_multithread=============');
1827: WRITE ('||Parameter Description ');
1828: WRITE ('||p_effective_dates - ' || p_effective_date);
1829: WRITE ('||p_validate - ' || p_validate);
1833: WRITE ('================================================');
1834:
1835: l_effective_date :=
1836: TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1837: hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1838: g_actn := 'Put row in fnd_sessions...';
1839: hr_utility.set_location (g_actn,113);
1840: hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1841: dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1835: l_effective_date :=
1836: TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1837: hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1838: g_actn := 'Put row in fnd_sessions...';
1839: hr_utility.set_location (g_actn,113);
1840: hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1841: dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1842: p_commit => l_commit
1843: );
1836: TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1837: hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1838: g_actn := 'Put row in fnd_sessions...';
1839: hr_utility.set_location (g_actn,113);
1840: hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1841: dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1842: p_commit => l_commit
1843: );
1844: -- need to check .
1891: END IF;
1892:
1893: --g_cache_person_process.DELETE;
1894: g_actn := 'Loading person data into g_cache_person_process cache...';
1895: hr_utility.set_location (g_actn,114);
1896: hr_utility.set_location ('Time' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),115);
1897:
1898: OPEN c_person_for_thread (p_benefit_action_id,
1899: l_start_person_action_id,
1892:
1893: --g_cache_person_process.DELETE;
1894: g_actn := 'Loading person data into g_cache_person_process cache...';
1895: hr_utility.set_location (g_actn,114);
1896: hr_utility.set_location ('Time' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),115);
1897:
1898: OPEN c_person_for_thread (p_benefit_action_id,
1899: l_start_person_action_id,
1900: l_end_person_action_id
1931: FOR l_cnt IN 1 .. l_record_number
1932: LOOP
1933: BEGIN
1934: g_actn := 'Calling the process for the person id ...'||g_cache_person_process (l_cnt).person_id ;
1935: hr_utility.set_location(g_actn,116);
1936: IF ( p_run_type = 'GEN') THEN
1937: hr_utility.set_location (' calling stmt_generation..',117);
1938: stmt_generation (
1939: p_validate => p_validate,
1933: BEGIN
1934: g_actn := 'Calling the process for the person id ...'||g_cache_person_process (l_cnt).person_id ;
1935: hr_utility.set_location(g_actn,116);
1936: IF ( p_run_type = 'GEN') THEN
1937: hr_utility.set_location (' calling stmt_generation..',117);
1938: stmt_generation (
1939: p_validate => p_validate,
1940: p_person_id => g_cache_person_process (l_cnt).person_id ,
1941: p_person_action_id => g_cache_person_process (l_cnt).person_action_id ,
1946: p_business_group => g_cache_person_process (l_cnt).bg_id,
1947: p_run_type => p_run_type,
1948: p_start_date => p_start_date );
1949:
1950: hr_utility.set_location('After the statement generation for the person id '|| g_cache_person_process (l_cnt).person_id,118);
1951:
1952: ELSIF (p_run_type = 'WLCM_SET') THEN
1953: g_actn := 'calling set_wlcm_flag..';
1954: hr_utility.set_location(g_actn,119);
1950: hr_utility.set_location('After the statement generation for the person id '|| g_cache_person_process (l_cnt).person_id,118);
1951:
1952: ELSIF (p_run_type = 'WLCM_SET') THEN
1953: g_actn := 'calling set_wlcm_flag..';
1954: hr_utility.set_location(g_actn,119);
1955:
1956: set_wlcm_flag ( p_validate => p_validate,
1957: p_person_id => g_cache_person_process (l_cnt).person_id ,
1958: p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1963: p_period_end_date => p_end_date,
1964: p_run_type =>p_run_type
1965: ) ;
1966:
1967: hr_utility.set_location('After the set welcome flag for the person id '|| g_cache_person_process (l_cnt).person_id,120);
1968:
1969: ELSIF (p_run_type = 'PURGE') THEN
1970:
1971: g_actn := 'calling purge_person_stmt..';
1968:
1969: ELSIF (p_run_type = 'PURGE') THEN
1970:
1971: g_actn := 'calling purge_person_stmt..';
1972: hr_utility.set_location(g_actn,121);
1973:
1974: purge_person_stmt(p_validate => p_validate,
1975: p_person_id => g_cache_person_process (l_cnt).person_id ,
1976: p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1980: p_business_group => g_cache_person_process (l_cnt).bg_id ,
1981: p_period_end_date => p_end_date,
1982: p_run_type =>p_run_type);
1983:
1984: hr_utility.set_location('After the purge for the person id '|| g_cache_person_process (l_cnt).person_id,122);
1985: END IF;
1986: EXCEPTION
1987: WHEN OTHERS
1988: THEN
2011:
2012: END LOOP;
2013: print_cache;
2014: WRITE ('Leaving do_multithread');
2015: hr_utility.set_location('Leaving '||g_proc,130);
2016: EXCEPTION
2017: WHEN OTHERS
2018: THEN
2019: WRITE (SQLERRM);
2045: l_num_rows NUMBER := p_per_actn_id_array.COUNT;
2046: BEGIN
2047: g_proc := 'insert_person_actions';
2048: WRITE ('In Procedure' ||g_proc);
2049: hr_utility.set_location('Entering '||g_proc,131);
2050: WRITE ( 'Time before inserting person actions '
2051: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2052:
2053: FORALL l_count IN 1 .. p_per_actn_id_array.COUNT
2092: WRITE ( 'Time at end of insert person actions '
2093: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2094:
2095: WRITE ('Leaving insert_person_actions');
2096: hr_utility.set_location('Leaving '||g_proc,132);
2097:
2098: END insert_person_actions;
2099:
2100: --
2379:
2380:
2381: BEGIN
2382: g_actn := 'Stating the Total Compensation Statement process : ' ;
2383: -- hr_utility.trace_on(null,'tcs');
2384: hr_utility.set_location('Entering '||g_actn,150);
2385:
2386: IF (p_run_type = 'GEN') THEN
2387: g_actn := g_actn || ' Statement Generation.' ;
2380:
2381: BEGIN
2382: g_actn := 'Stating the Total Compensation Statement process : ' ;
2383: -- hr_utility.trace_on(null,'tcs');
2384: hr_utility.set_location('Entering '||g_actn,150);
2385:
2386: IF (p_run_type = 'GEN') THEN
2387: g_actn := g_actn || ' Statement Generation.' ;
2388: ELSIF (p_run_type = 'PURGE') THEN
2463: g_exec_param_rec.start_time := DBMS_UTILITY.get_time;
2464: g_validate := p_validate ;
2465:
2466: g_actn := 'Calling ben_batch_utils.ini...';
2467: hr_utility.set_location (g_actn,151);
2468: hr_utility.set_location ('ben_batch_utils.ini with PROC_INFO',152);
2469: ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2470:
2471: g_actn := 'Calling benutils.get_parameter...';
2464: g_validate := p_validate ;
2465:
2466: g_actn := 'Calling ben_batch_utils.ini...';
2467: hr_utility.set_location (g_actn,151);
2468: hr_utility.set_location ('ben_batch_utils.ini with PROC_INFO',152);
2469: ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2470:
2471: g_actn := 'Calling benutils.get_parameter...';
2472: WRITE (g_actn);
2469: ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2470:
2471: g_actn := 'Calling benutils.get_parameter...';
2472: WRITE (g_actn);
2473: hr_utility.set_location(g_actn,153);
2474: WRITE ( 'benutils.get_parameter with '
2475: || p_business_group_id
2476: || ' '
2477: || 'BENTCSSP'
2574: l_count := l_count + 1;
2575: stmt_record_rec (l_count).statement_rec := stmt_rec2;
2576: END LOOP;
2577:
2578: hr_utility.set_location( 'Number of statement ids to be processed '||l_count,154);
2579: g_exec_param_rec.Number_Of_BGs :=l_count;
2580: CLOSE c_stmt_id_bg_id;
2581: /* ELSE
2582: l_business_group_id := HR_GENERAL.GET_BUSINESS_GROUP_ID ;
2596: CLOSE c_stmt_id;
2597:
2598: END IF;
2599: g_actn := 'After Processing statement id and Eligibility Id .';
2600: hr_utility.set_location( g_actn,155);
2601:
2602: IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
2603: WRITE ('****************statement validation ************');
2604: g_actn := 'Before statement validation ...' ;
2601:
2602: IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
2603: WRITE ('****************statement validation ************');
2604: g_actn := 'Before statement validation ...' ;
2605: hr_utility.set_location(g_actn,157);
2606: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2607: hr_utility.set_location (g_actn,158);
2608: WRITE ( 'Time before validating the stataement '
2609: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2603: WRITE ('****************statement validation ************');
2604: g_actn := 'Before statement validation ...' ;
2605: hr_utility.set_location(g_actn,157);
2606: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2607: hr_utility.set_location (g_actn,158);
2608: WRITE ( 'Time before validating the stataement '
2609: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2610: );
2611: hr_utility.set_location (g_actn,159);
2607: hr_utility.set_location (g_actn,158);
2608: WRITE ( 'Time before validating the stataement '
2609: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2610: );
2611: hr_utility.set_location (g_actn,159);
2612:
2613:
2614: ben_tcs_stmt_valid_hrchy.stmt_gen_valid_process (
2615: stmt_rec.stmt_id,
2619: subcat_hrchy_values,
2620: l_status) ;
2621:
2622: g_actn := 'After statement validation ...' ;
2623: hr_utility.set_location (g_actn,160);
2624: WRITE ( 'Time after validating the stataement '
2625: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2626: );
2627: WRITE (g_actn);
2668: END IF;
2669: WHILE (l_loop_cnt <= l_count)
2670: LOOP
2671: g_actn := 'Processing the statement :'|| stmt_record_rec (l_loop_cnt).statement_rec.stmt_id;
2672: hr_utility.set_location (g_actn,161);
2673: WRITE ('****************statement validation ************');
2674: g_actn := 'Before statement validation ...' ;
2675: hr_utility.set_location (g_actn,162);
2676: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2671: g_actn := 'Processing the statement :'|| stmt_record_rec (l_loop_cnt).statement_rec.stmt_id;
2672: hr_utility.set_location (g_actn,161);
2673: WRITE ('****************statement validation ************');
2674: g_actn := 'Before statement validation ...' ;
2675: hr_utility.set_location (g_actn,162);
2676: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2677: WRITE (g_actn);
2678: WRITE ( 'Time before validating the stataement '
2679: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2686: item_hrchy_values, subcat_hrchy_values,
2687: l_status);
2688:
2689: g_actn := 'After statement validation ...' ;
2690: hr_utility.set_location (g_actn,163);
2691: WRITE ( 'Time after validating the stataement '
2692: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2693: );
2694: WRITE (g_actn);
2740: END IF;
2741:
2742: WRITE ('****************processing the person ************');
2743: g_actn := 'before processing person ids..' ;
2744: hr_utility.set_location (g_actn,164);
2745: WRITE ( 'Time before processing person ids .. '
2746: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2747: );
2748: WRITE (g_actn);
2748: WRITE (g_actn);
2749:
2750: IF ( p_person_id IS NOT NULL AND stmt_rec.valid_flag = 'Y') THEN
2751: OPEN c_person_valid_emp ( p_person_id ,l_period_start_date,l_period_end_date);
2752: hr_utility.set_location( 'processing the person id ' ||p_person_id,165);
2753: FETCH c_person_valid_emp into per_rec;
2754: IF c_person_valid_emp%NOTFOUND THEN
2755:
2756: get_name(l_business_group_id ,
2770: l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2771: l_rep_rec(rep_count ).SETUP_VALID := 'Y';
2772: l_rep_rec(rep_count ).TOTAL_PERSONS := 0 ;
2773:
2774: hr_utility.set_location('The person id :'||p_person_id || 'is not valid for this period' ||stmt_rec.stmt_id,165);
2775: fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_VALID_PERSON');
2776: fnd_message.raise_error;
2777:
2778: ELSE
2780: t_prof_tbl (1).compute_score_flag := 'N';
2781: t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
2782:
2783: IF ( stmt_rec.ee_id IS NOT NULL ) THEN
2784: hr_utility.set_location('checking the eligibity of the person_id id :'|| p_person_id ||'.Eligibility profile id is
2785: '||stmt_rec.ee_id ,166 );
2786:
2787: SELECT ELIGY_PRFL_ID INTO temp_ee_id
2788: FROM BEN_ELIGY_PRFL_F
2790: AND ELIGY_PRFL_ID = stmt_rec.ee_id
2791: AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
2792:
2793: t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id ;
2794: hr_utility.set_location( 'valid eligy id'|| temp_ee_id ,166) ;
2795: BEGIN
2796: IF (temp_ee_id is not null ) THEN
2797: hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...',167);
2798:
2793: t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id ;
2794: hr_utility.set_location( 'valid eligy id'|| temp_ee_id ,166) ;
2795: BEGIN
2796: IF (temp_ee_id is not null ) THEN
2797: hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...',167);
2798:
2799: ben_env_object.init(
2800: p_business_group_id=>l_business_group_id,
2801: p_effective_date =>l_period_end_date,
2815: p_comp_obj_mode => FALSE,
2816: p_score_tab => l_score_tab
2817: );
2818:
2819: hr_utility.set_location('After ben_evaluate_elig_profiles.eligible...',168);
2820: ELSE
2821: l_elig_return_status := TRUE ;
2822: END IF;
2823: EXCEPTION
2821: l_elig_return_status := TRUE ;
2822: END IF;
2823: EXCEPTION
2824: WHEN OTHERS THEN
2825: hr_utility.set_location('Exception : '||SQLERRM,10);
2826: l_elig_return_status := FALSE ;
2827: END;
2828: ELSE
2829: hr_utility.set_location('No eligibility profile attached to the statement' ||stmt_rec.stmt_id ||
2825: hr_utility.set_location('Exception : '||SQLERRM,10);
2826: l_elig_return_status := FALSE ;
2827: END;
2828: ELSE
2829: hr_utility.set_location('No eligibility profile attached to the statement' ||stmt_rec.stmt_id ||
2830: 'The person id '||p_person_id|| 'is eligible for processing .',169 );
2831:
2832: l_elig_return_status := TRUE ;
2833: END IF;
2878: WRITE ('=======================================================');
2879:
2880: IF l_num_rows = l_chunk_size THEN
2881: l_num_ranges := l_num_ranges + 1;
2882: hr_utility.set_location('inserting INTO person actions :..',170);
2883: g_actn := 'inserting INTO person actions : person id ' || p_person_id ;
2884: hr_utility.set_location(g_actn,171);
2885:
2886: insert_person_actions
2880: IF l_num_rows = l_chunk_size THEN
2881: l_num_ranges := l_num_ranges + 1;
2882: hr_utility.set_location('inserting INTO person actions :..',170);
2883: g_actn := 'inserting INTO person actions : person id ' || p_person_id ;
2884: hr_utility.set_location(g_actn,171);
2885:
2886: insert_person_actions
2887: (p_per_actn_id_array => l_person_action_ids,
2888: p_per_id => l_person_ids,
2898: l_bg_ids.DELETE;
2899: l_perd_ids.DELETE;
2900: END IF;
2901: ELSE
2902: hr_utility.set_location('The person id :'||p_person_id || 'is not eligible for the statement' ||stmt_rec.stmt_id ,171);
2903: --fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_ELIG_PERSON');
2904: --fnd_message.raise_error;
2905: get_name(l_business_group_id ,
2906: temp_ee_id ,
2924:
2925: ELSIF (l_business_group_id IS NOT NULL AND stmt_rec.valid_flag = 'Y' ) THEN
2926:
2927: g_actn := 'processing person ids for the business group id ' || l_business_group_id ;
2928: hr_utility.set_location(g_actn,172);
2929: temp_ee_id :=null;
2930: IF (stmt_rec.ee_id IS NOT NULL ) THEN
2931: SELECT ELIGY_PRFL_ID INTO temp_ee_id
2932: FROM BEN_ELIGY_PRFL_F
2960: INTO per_rec;
2961: EXIT WHEN c_person_selection%NOTFOUND;
2962:
2963: g_actn := 'processing the person id ' || per_rec.person_id ;
2964: hr_utility.set_location(g_actn,173);
2965: BEGIN
2966: IF (stmt_rec.ee_id IS NOT NULL ) THEN
2967: t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id;
2968: IF (temp_ee_id is not null ) THEN
2976: p_max_errors => l_max_errors_allowed,
2977: p_benefit_action_id => l_benefit_action_id
2978: );
2979:
2980: hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...' ,174);
2981: l_elig_return_status :=
2982: ben_evaluate_elig_profiles.eligible
2983: (p_person_id => per_rec.person_id,
2984: p_business_group_id => stmt_rec.bg_id ,
2986: p_eligprof_tab => t_prof_tbl,
2987: p_comp_obj_mode => FALSE,
2988: p_score_tab => l_score_tab
2989: );
2990: hr_utility.set_location('after ben_evaluate_elig_profiles.eligible...',175);
2991: ELSE
2992: l_elig_return_status := TRUE ;
2993: END IF;
2994:
2996: l_elig_return_status := TRUE ;
2997: END IF;
2998: EXCEPTION
2999: WHEN OTHERS THEN
3000: hr_utility.set_location('Exception : '||SQLERRM,10);
3001: l_elig_return_status := FALSE ;
3002: END;
3003: IF l_elig_return_status THEN
3004:
3001: l_elig_return_status := FALSE ;
3002: END;
3003: IF l_elig_return_status THEN
3004:
3005: hr_utility.set_location( 'The person id ' || per_rec.person_id || ' is eligible for the statement ' ||
3006: stmt_rec.stmt_id,176);
3007:
3008: l_num_rows := l_num_rows + 1;
3009: l_num_persons := l_num_persons + 1;
3012: l_stmt_ids.EXTEND (1);
3013: l_perd_ids.EXTEND (1);
3014: l_bg_ids.EXTEND (1);
3015:
3016: hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,177);
3017:
3018: SELECT ben_person_actions_s.NEXTVAL
3019: INTO l_person_action_ids (l_num_rows)
3020: FROM DUAL;
3036:
3037: IF l_num_rows = l_chunk_size
3038: THEN
3039: g_actn := 'inserting INTO person actions ';
3040: hr_utility.set_location(g_actn,178);
3041:
3042: l_num_ranges := l_num_ranges + 1;
3043: insert_person_actions
3044: (p_per_actn_id_array => l_person_action_ids,
3055: l_perd_ids.DELETE;
3056: l_bg_ids.DELETE;
3057: END IF;
3058: ELSE
3059: hr_utility.set_location( 'The person id ' || per_rec.person_id || ' is not eligible for the statement ' ||
3060: stmt_rec.stmt_id,179);
3061: END IF;
3062: END LOOP;
3063:
3087: LOOP
3088: l_person_temp := 0;
3089:
3090: g_actn := 'processing the stmt id ' || stmt_record_rec (l_loop_cnt).statement_rec.stmt_id ;
3091: hr_utility.set_location(g_actn,180);
3092:
3093: IF (stmt_record_rec (l_loop_cnt).statement_rec.valid_flag = 'Y' ) THEN
3094: OPEN c_person_selection (stmt_record_rec (l_loop_cnt).statement_rec.stmt_id,
3095: l_period_start_date,
3112: INTO per_rec;
3113: EXIT WHEN c_person_selection%NOTFOUND;
3114:
3115: g_actn := 'processing the person id ' || per_rec.person_id ;
3116: hr_utility.set_location(g_actn,181);
3117: BEGIN
3118: IF (stmt_record_rec (l_loop_cnt).statement_rec.ee_id IS NOT NULL ) THEN
3119:
3120: hr_utility.set_location ('checking the eligibility of the person id ' ||per_rec.person_id,182 );
3116: hr_utility.set_location(g_actn,181);
3117: BEGIN
3118: IF (stmt_record_rec (l_loop_cnt).statement_rec.ee_id IS NOT NULL ) THEN
3119:
3120: hr_utility.set_location ('checking the eligibility of the person id ' ||per_rec.person_id,182 );
3121:
3122: t_prof_tbl (1).eligy_prfl_id := stmt_record_rec (l_loop_cnt).statement_rec.ee_id;
3123:
3124: ben_env_object.init(
3129: p_threads => l_threads ,
3130: p_max_errors => l_max_errors_allowed,
3131: p_benefit_action_id => l_benefit_action_id
3132: );
3133: hr_utility.set_location('Before calling ben_evaluate_elig_profiles.eligible...',183);
3134: l_elig_return_status :=
3135: ben_evaluate_elig_profiles.eligible
3136: (p_person_id => per_rec.person_id,
3137: p_business_group_id => stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
3144: l_elig_return_status := TRUE ;
3145: END IF;
3146: EXCEPTION
3147: WHEN OTHERS THEN
3148: hr_utility.set_location('Exception : '||SQLERRM,10);
3149: l_elig_return_status := FALSE ;
3150: END;
3151: IF l_elig_return_status THEN
3152:
3158: l_stmt_ids.EXTEND (1);
3159: l_perd_ids.EXTEND (1);
3160: l_bg_ids.EXTEND (1);
3161:
3162: hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,184);
3163:
3164: SELECT ben_person_actions_s.NEXTVAL
3165: INTO l_person_action_ids (l_num_rows)
3166: FROM DUAL;
3181:
3182:
3183: IF l_num_rows = l_chunk_size THEN
3184: g_actn := 'inserting INTO person actions ';
3185: hr_utility.set_location(g_actn,185);
3186: l_num_ranges := l_num_ranges + 1;
3187: insert_person_actions
3188: (p_per_actn_id_array => l_person_action_ids,
3189: p_per_id => l_person_ids,
3199: l_bg_ids.DELETE;
3200: l_perd_ids.DELETE;
3201: END IF;
3202: ELSE
3203: hr_utility.set_location ('The person : ' ||per_rec.person_id || 'is not eligible',186);
3204: END IF;
3205: END LOOP;
3206:
3207:
3246: l_count := l_count + 1;
3247: stmt_record_rec1 (l_count).statement_rec1 := stmt_rec1;
3248: END LOOP;
3249:
3250: hr_utility.set_location ('Number of statement ids to be processed '||l_count,187);
3251: g_exec_param_rec.Number_Of_BGs :=l_count;
3252: CLOSE c_stmt_id_bg_id1;
3253: /* ELSE
3254: l_business_group_id := HR_GENERAL.GET_BUSINESS_GROUP_ID ;
3257: END IF;
3258:
3259: IF ( p_person_id IS NOT NULL)THEN
3260:
3261: hr_utility.set_location('checking for the person_id id ... '||p_person_id,188);
3262: g_exec_param_rec.Number_Of_BGs :=1;
3263: OPEN c_check_stmt_person(p_person_id ,p_stmt_id, p_period_id ) ;
3264:
3265: FETCH c_check_stmt_person into stmt_per_rec;
3315:
3316: IF l_num_rows = l_chunk_size THEN
3317: l_num_ranges := l_num_ranges + 1;
3318: g_actn := 'inserting INTO person actions : person id ' || p_person_id;
3319: hr_utility.set_location(g_actn,189);
3320: insert_person_actions
3321: (p_per_actn_id_array => l_person_action_ids,
3322: p_per_id => l_person_ids,
3323: p_benefit_action_id => l_benefit_action_id,
3349: END IF;
3350: CLOSE c_check_stmt_person ;
3351: ELSIF(l_business_group_id IS NOT NULL) THEN
3352: g_actn := 'processing the business group id ' || l_business_group_id ;
3353: hr_utility.set_location(g_actn,190);
3354: g_exec_param_rec.Number_Of_BGs :=1;
3355:
3356: t_prof_tbl (1).mndtry_flag := 'N';
3357: t_prof_tbl (1).compute_score_flag := 'N';
3380: FETCH c_check_stmt_avail_1
3381: INTO per_rec;
3382: EXIT WHEN c_check_stmt_avail_1%NOTFOUND;
3383: g_actn := 'processing the person id ' || per_rec.person_id ;
3384: hr_utility.set_location(g_actn,191);
3385:
3386: l_num_rows := l_num_rows + 1;
3387: l_num_persons := l_num_persons + 1;
3388: l_person_action_ids.EXTEND (1);
3412:
3413: IF l_num_rows = l_chunk_size
3414: THEN
3415: g_actn := 'inserting INTO person actions ';
3416: hr_utility.set_location(g_actn,192);
3417: l_num_ranges := l_num_ranges + 1;
3418: insert_person_actions
3419: (p_per_actn_id_array => l_person_action_ids,
3420: p_per_id => l_person_ids,
3455: LOOP
3456: l_person_temp := 0;
3457: g_actn := 'processing the stmt id ' || stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id
3458: || 'period id :'||stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id;
3459: hr_utility.set_location(g_actn,193);
3460: OPEN c_check_stmt_avail(stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id ,stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id ) ;
3461: get_name(p_bg_id=> stmt_record_rec1 (l_loop_cnt).statement_rec1.bg_id ,
3462: v_period_end_date => l_period_end_date ,
3463: p_bg_name =>p_bg_name ,
3476: l_stmt_ids.EXTEND (1);
3477: l_perd_ids.EXTEND (1);
3478: l_bg_ids.EXTEND (1);
3479:
3480: hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,193);
3481:
3482:
3483: SELECT ben_person_actions_s.NEXTVAL
3484: INTO l_person_action_ids (l_num_rows)
3500:
3501:
3502: IF l_num_rows = l_chunk_size THEN
3503: g_actn := 'inserting INTO person actions ';
3504: hr_utility.set_location(g_actn,194);
3505: l_num_ranges := l_num_ranges + 1;
3506: insert_person_actions
3507: (p_per_actn_id_array => l_person_action_ids,
3508: p_per_id => l_person_ids,
3546: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
3547:
3548: IF l_num_rows <> 0 THEN
3549: l_num_ranges := l_num_ranges + 1;
3550: hr_utility.set_location('l_num_ranges '||l_num_ranges,500);
3551: insert_person_actions
3552: (p_per_actn_id_array => l_person_action_ids,
3553: p_per_id => l_person_ids,
3554: p_benefit_action_id => l_benefit_action_id,
3576: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
3577:
3578: IF l_num_ranges > 1
3579: THEN
3580: hr_utility.set_location('L threads '||l_threads, 29);
3581: WRITE('l_threads'||l_threads );
3582: WRITE('l_num_ranges'||l_num_ranges );
3583: FOR loop_count IN 1 .. LEAST (l_threads, l_num_ranges) - 1
3584: LOOP
3608: ben_batch_utils.g_num_processes :=
3609: ben_batch_utils.g_num_processes + 1;
3610: ben_batch_utils.g_processes_tbl (ben_batch_utils.g_num_processes) :=
3611: l_request_id;
3612: hr_utility.set_location ('request id for this thread ' || l_request_id,160);
3613: COMMIT;
3614: END LOOP;
3615: ELSIF l_num_ranges = 0
3616: THEN
3625: WRITE ('||Parameter value ');
3626: WRITE ('||p_benefit_action_id- ' || l_benefit_action_id);
3627: WRITE ('||p_thread_id- ' || (l_threads + 1));
3628: WRITE ('==========================================================');
3629: hr_utility.set_location('L threads before calling do_mutithread'||l_threads, 29);
3630:
3631: do_multithread (errbuf => errbuf,
3632: retcode => retcode,
3633: p_validate => p_validate,
3655: WRITE ('||Number of persons processed - ' || l_num_persons);
3656: WRITE ('==========================================================');
3657:
3658: g_actn := 'Calling delete hrchy ...';
3659: hr_utility.set_location (g_actn,161);
3660: delete_hrchy;
3661: g_actn := 'Calling Hierarchy Set...';
3662: hr_utility.set_location (g_actn,162);
3663: hrchy_set(l_benefit_action_id);
3658: g_actn := 'Calling delete hrchy ...';
3659: hr_utility.set_location (g_actn,161);
3660: delete_hrchy;
3661: g_actn := 'Calling Hierarchy Set...';
3662: hr_utility.set_location (g_actn,162);
3663: hrchy_set(l_benefit_action_id);
3664: g_actn := 'Calling end_process...';
3665: hr_utility.set_location (g_actn,163);
3666: end_process (p_benefit_action_id => l_benefit_action_id
3661: g_actn := 'Calling Hierarchy Set...';
3662: hr_utility.set_location (g_actn,162);
3663: hrchy_set(l_benefit_action_id);
3664: g_actn := 'Calling end_process...';
3665: hr_utility.set_location (g_actn,163);
3666: end_process (p_benefit_action_id => l_benefit_action_id
3667: , p_person_selected => l_num_persons
3668: , p_business_group_id => l_business_group_id
3669: );
3667: , p_person_selected => l_num_persons
3668: , p_business_group_id => l_business_group_id
3669: );
3670: g_actn := 'Finished Process Procedure...';
3671: hr_utility.set_location (g_actn,164);
3672:
3673: IF (p_run_type ='GEN') THEN
3674: IF (g_validate = 'N') THEN
3675: IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
3708: END LOOP;
3709: END IF;
3710: END IF;
3711: END IF;
3712: -- hr_utility.trace_off;
3713: EXCEPTION
3714: --
3715: WHEN l_silent_error
3716: THEN