[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:
768: asg_rec c_asg_stmt%ROWTYPE;
769: BEGIN
770: g_proc := 'purge_person_stmt';
771: WRITE ('In Procedure' ||g_proc);
772: hr_utility.set_location('Entering '||g_proc,71);
773:
774: WRITE ('===========purge statement for the person============');
775: WRITE ('||Person Id ' || p_person_id);
776: WRITE ('||Statement id ' || p_stmt_id);
787: stmt_id = p_stmt_id
788: AND stmt_perd_id = p_stmt_perd_id
789: AND person_id = p_person_id ;
790:
791: hr_utility.set_location ( 'delete per_stmt_perd_id entry for per_stmt_perd_id :' || l_per_stmt_perd,72);
792:
793: DELETE
794: FROM ben_tcs_per_stmt_perd
795: WHERE per_stmt_perd_id = l_per_stmt_perd;
806: FETCH c_per_item
807: INTO l_per_item ;
808: EXIT WHEN c_per_item%NOTFOUND;
809:
810: hr_utility.set_location ( 'delete ben_tcs_per_item_value and ben_tcs_per_item entry for per_item_id :' || l_per_item,73);
811: DELETE FROM ben_tcs_per_item_value
812: WHERE per_item_id = l_per_item;
813:
814: DELETE FROM ben_tcs_per_item
814: DELETE FROM ben_tcs_per_item
815: WHERE per_item_id = l_per_item;
816: END LOOP;
817:
818: hr_utility.set_location ( 'delete ben_tcs_asg_stmt entry for ASG_STMT_ID :' || asg_rec.asg_stmt_id,74);
819: DELETE FROM
820: ben_tcs_asg_stmt
821: WHERE ASG_STMT_ID = asg_rec.asg_stmt_id;
822:
858: ROLLBACK TO Purge;
859: END IF;
860:
861: WRITE('leaving purge ...');
862: hr_utility.set_location('Leaving '||g_proc,75);
863: EXCEPTION
864: WHEN no_data_found THEN
865: WRITE('No stmt yet generated');
866: WRITE ('Leaving purge_person_stmt');
963: BEGIN
964:
965: g_proc := 'stmt_generation';
966: WRITE ('In Procedure' ||g_proc);
967: hr_utility.set_location('Entering '||g_proc,76);
968:
969: WRITE ('===========statement generation for the person============');
970: WRITE ('||Person Id ' || p_person_id);
971: WRITE ('||Statement id ' || p_stmt_id);
974: WRITE ('=======================================================');
975:
976: SAVEPOINT generation;
977:
978: hr_utility.set_location( 'calling purge ...',77 );
979: purge_person_stmt(
980: p_validate => p_validate ,
981: p_person_id => p_person_id,
982: p_stmt_id => p_stmt_id,
986: p_business_group =>p_business_group,
987: p_period_end_date => p_period_end_date,
988: p_run_type =>p_run_type);
989:
990: hr_utility.set_location('Calling check_multiple_stmt ',78);
991: IF (check_multiple_stmt (
992: p_person_id => p_person_id,
993: p_stmt_id => p_stmt_id ,
994: p_period_start_date => p_start_date,
995: p_period_end_date => p_period_end_date) = true )
996: THEN
997: WRITE('Statement already exists for this period date');
998: ELSE
999: hr_utility.set_location('Before assignment cursor',79);
1000: OPEN c_assignment_selection(p_person_id,p_start_date,p_period_end_date);
1001: hr_utility.set_location('After assignment cursor',80 );
1002: LOOP
1003: person_created_now := 'N';
997: WRITE('Statement already exists for this period date');
998: ELSE
999: hr_utility.set_location('Before assignment cursor',79);
1000: OPEN c_assignment_selection(p_person_id,p_start_date,p_period_end_date);
1001: hr_utility.set_location('After assignment cursor',80 );
1002: LOOP
1003: person_created_now := 'N';
1004: Record_Created := 'N';
1005: cntr_item := 'N';
1006: FETCH c_assignment_selection
1007: INTO l_assign;
1008: EXIT WHEN c_assignment_selection%NOTFOUND;
1009: SAVEPOINT assign_details;
1010: hr_utility.set_location('Processing assignment_number id '|| l_assign.assignment_id,81);
1011: gen := 0;
1012: OPEN c_item (p_stmt_id ) ;
1013: LOOP
1014: FETCH c_item INTO item_id_process ;
1013: LOOP
1014: FETCH c_item INTO item_id_process ;
1015: EXIT WHEN c_item%NOTFOUND;
1016: IF ( item_id_process.item_id <> -1 ) THEN
1017: hr_utility.set_location('Processing item_id ' ||item_id_process.item_id,82);
1018: SELECT source_cd , source_key ,uom ,nnmntry_uom,comp_type_cd,name
1019: INTO l_source_cd , l_source_key ,l_uom ,l_nnmntry_uom,l_comp_type_cd ,item_name
1020: FROM ben_tcs_item
1021: WHERE item_id = item_id_process.item_id;
1019: INTO l_source_cd , l_source_key ,l_uom ,l_nnmntry_uom,l_comp_type_cd ,item_name
1020: FROM ben_tcs_item
1021: WHERE item_id = item_id_process.item_id;
1022:
1023: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1024: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1025: hr_utility.set_location('Person Id' || p_person_id,85);
1026: -- hr_utility.trace_on(null,'tcs');
1027: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1020: FROM ben_tcs_item
1021: WHERE item_id = item_id_process.item_id;
1022:
1023: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1024: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1025: hr_utility.set_location('Person Id' || p_person_id,85);
1026: -- hr_utility.trace_on(null,'tcs');
1027: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1028: p_source_key => l_source_key,
1021: WHERE item_id = item_id_process.item_id;
1022:
1023: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1024: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1025: hr_utility.set_location('Person Id' || p_person_id,85);
1026: -- hr_utility.trace_on(null,'tcs');
1027: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1028: p_source_key => l_source_key,
1029: p_perd_st_dt => p_start_date,
1022:
1023: hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1024: hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1025: hr_utility.set_location('Person Id' || p_person_id,85);
1026: -- hr_utility.trace_on(null,'tcs');
1027: BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1028: p_source_key => l_source_key,
1029: p_perd_st_dt => p_start_date,
1030: p_perd_en_dt => p_period_end_date,
1036: p_effective_date => p_period_end_date ,
1037: p_result => l_result,
1038: p_status => l_status);
1039:
1040: hr_utility.set_location('After BEN_TCS_COMPENSATION.get_value_for_item call',86);
1041: -- hr_utility.trace_off;
1042: IF ( l_status = '0') THEN
1043: IF (l_result.count > 0) THEN
1044: IF (item_id_process.cntr_cd = 'ER' OR item_id_process.cntr_cd = 'EE' ) THEN
1037: p_result => l_result,
1038: p_status => l_status);
1039:
1040: hr_utility.set_location('After BEN_TCS_COMPENSATION.get_value_for_item call',86);
1041: -- hr_utility.trace_off;
1042: IF ( l_status = '0') THEN
1043: IF (l_result.count > 0) THEN
1044: IF (item_id_process.cntr_cd = 'ER' OR item_id_process.cntr_cd = 'EE' ) THEN
1045: cntr_item := 'Y';
1050: FOR i IN 1..l_result.count
1051: LOOP
1052: IF (i =1 ) THEN
1053: IF l_count = 0 AND gen = 0 THEN
1054: hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1055: p_person_id|| p_stmt_perd_id||p_stmt_id,87);
1056:
1057: l_person_inc := 1;
1058: l_count := l_count + 1;
1073: p_period_end_date);
1074:
1075: person_created_now := 'Y';
1076:
1077: hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',88);
1078:
1079: END IF;
1080:
1081: IF gen = 0 THEN
1079: END IF;
1080:
1081: IF gen = 0 THEN
1082:
1083: hr_utility.set_location('Inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id,89);
1084: INSERT INTO ben_tcs_asg_stmt(
1085: asg_stmt_id ,
1086: stmt_id ,
1087: assignment_id,
1093: l_assign.assignment_id,
1094: ben_tcs_per_stmt_perd_s.CURRVAL,
1095: l_assign.assignment_number);
1096:
1097: hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',90);
1098: IF (Record_Created ='N') THEN
1099: get_emp_detail(
1100: l_assign.assignment_id ,
1101: p_person_id ,
1126:
1127: l_uom := l_result(i).currency_cd;
1128: l_nnmntry_uom := l_result(i).uom;
1129:
1130: hr_utility.set_location('Inserting INTO ben_tcs_per_item item_id '||item_id_process.item_id,91);
1131:
1132: INSERT INTO ben_tcs_per_item
1133: ( per_item_id,
1134: asg_stmt_id ,
1149: l_assign.assignment_id ,
1150: l_uom ,
1151: l_nnmntry_uom );
1152:
1153: hr_utility.set_location(' After inserting INTO ben_tcs_per_item ',92);
1154: END IF;
1155: IF (l_source_cd = 'BB' OR l_source_cd = 'RULE' OR l_source_cd = 'PAYCOSTG' ) THEN
1156: hr_utility.set_location(' Inserting INTO ben_tcs_per_item_value ' ,92.1);
1157: IF (l_comp_type_cd = 'DATE') THEN
1152:
1153: hr_utility.set_location(' After inserting INTO ben_tcs_per_item ',92);
1154: END IF;
1155: IF (l_source_cd = 'BB' OR l_source_cd = 'RULE' OR l_source_cd = 'PAYCOSTG' ) THEN
1156: hr_utility.set_location(' Inserting INTO ben_tcs_per_item_value ' ,92.1);
1157: IF (l_comp_type_cd = 'DATE') THEN
1158: IF( l_source_cd = 'PAYCOSTG' OR l_source_cd = 'BB' ) THEN
1159: WRITE('Data Type Mismatch for the item :' || item_name ||
1160: 'Expected Type : ' || l_comp_type_cd|| ' Actual type :' || l_result(i).actual_uom);
1271: EXIT ;
1272: END;
1273: END IF;
1274:
1275: hr_utility.set_location(' After inserting INTO ben_tcs_per_item_value ',93 );
1276:
1277: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' ) THEN
1278:
1279: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1275: hr_utility.set_location(' After inserting INTO ben_tcs_per_item_value ',93 );
1276:
1277: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' ) THEN
1278:
1279: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1280: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1281: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1282: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1283: IF (l_comp_type_cd = 'DATE') THEN
1276:
1277: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' ) THEN
1278:
1279: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1280: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1281: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1282: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1283: IF (l_comp_type_cd = 'DATE') THEN
1284: IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1277: ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' ) THEN
1278:
1279: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1280: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1281: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1282: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1283: IF (l_comp_type_cd = 'DATE') THEN
1284: IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1285:
1278:
1279: hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1280: hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1281: hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1282: hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1283: IF (l_comp_type_cd = 'DATE') THEN
1284: IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1285:
1286: WRITE('Data Type Mismatch for the item :' || item_name ||
1430: END IF;
1431: Rollback_Person := 'Y' ;
1432: END IF;
1433: ELSE
1434: hr_utility.set_location('stock options extended subcategory',94);
1435: get_emp_detail(
1436: l_assign.assignment_id ,
1437: p_person_id ,
1438: p_period_end_date,
1442: p_bg ) ;
1443: OPEN c_stk_opts (p_person_id , p_stmt_perd_id , p_period_end_date , p_business_group,p_emp_num );
1444: FETCH c_stk_opts INTO stk_opts_rec ;
1445: IF ( stk_opts_rec.total IS NOT NULL ) THEN
1446: hr_utility.set_location('Person has value for stock options extended subcategory',95);
1447: cntr_item := 'Y';
1448: IF l_count = 0 AND gen = 0 THEN
1449: hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1450: p_person_id|| p_stmt_perd_id||p_stmt_id ||' subcategory part' ,96);
1445: IF ( stk_opts_rec.total IS NOT NULL ) THEN
1446: hr_utility.set_location('Person has value for stock options extended subcategory',95);
1447: cntr_item := 'Y';
1448: IF l_count = 0 AND gen = 0 THEN
1449: hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1450: p_person_id|| p_stmt_perd_id||p_stmt_id ||' subcategory part' ,96);
1451:
1452: l_person_inc := 1;
1453: l_count := l_count + 1;
1466: p_person_id,
1467: null,
1468: p_period_end_date);
1469:
1470: hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',97);
1471: END IF;
1472:
1473: IF gen = 0 THEN
1474:
1471: END IF;
1472:
1473: IF gen = 0 THEN
1474:
1475: hr_utility.set_location('inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id||' subcategory',98);
1476: INSERT INTO ben_tcs_asg_stmt(
1477: asg_stmt_id ,
1478: stmt_id ,
1479: assignment_id,
1485: l_assign.assignment_id,
1486: ben_tcs_per_stmt_perd_s.CURRVAL,
1487: l_assign.assignment_number);
1488:
1489: hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',99);
1490: IF (Record_Created ='N') THEN
1491: rep_count := l_rep_rec.COUNT +1;
1492: l_rep_rec(rep_count ).p_TYPE := 0;
1493: l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1512: END LOOP;
1513:
1514: IF (Record_Created ='N') THEN
1515:
1516: hr_utility.set_location(' no report',100);
1517: get_emp_detail(
1518: l_assign.assignment_id ,
1519: p_person_id ,
1520: p_period_end_date,
1563:
1564: IF ( Rollback_Person = 'Y') THEN
1565: ROLLBACK TO generation;
1566:
1567: hr_utility.set_location('initial cnt' || l_initial_count,101);
1568: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,102);
1569:
1570: FOR i in l_initial_count .. l_rep_rec.COUNT
1571: LOOP
1564: IF ( Rollback_Person = 'Y') THEN
1565: ROLLBACK TO generation;
1566:
1567: hr_utility.set_location('initial cnt' || l_initial_count,101);
1568: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,102);
1569:
1570: FOR i in l_initial_count .. l_rep_rec.COUNT
1571: LOOP
1572: IF ( l_rep_rec(i).STMT_CREATED = 'Y' OR l_rep_rec(i).STMT_CREATED = 'N' ) THEN
1577: WRITE('person rolled back due to error ');
1578: END If;
1579:
1580: WRITE ('Leaving stmt_generation');
1581: --hr_utility.trace_off;
1582: hr_utility.set_location('Leaving '||g_proc,103);
1583:
1584: EXCEPTION
1585:
1578: END If;
1579:
1580: WRITE ('Leaving stmt_generation');
1581: --hr_utility.trace_off;
1582: hr_utility.set_location('Leaving '||g_proc,103);
1583:
1584: EXCEPTION
1585:
1586: WHEN OTHERS
1584: EXCEPTION
1585:
1586: WHEN OTHERS
1587: THEN
1588: -- hr_utility.trace_off;
1589: IF (Record_Created ='N') THEN
1590: get_emp_detail(
1591: l_assign.assignment_id ,
1592: p_person_id ,
1613: l_rep_rec(rep_count ).STMT_CREATED := 'E';
1614:
1615: ROLLBACK TO generation;
1616:
1617: hr_utility.set_location('initial cnt' || l_initial_count,104);
1618: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,105);
1619:
1620: FOR i IN l_initial_count .. l_rep_rec.COUNT
1621: LOOP
1614:
1615: ROLLBACK TO generation;
1616:
1617: hr_utility.set_location('initial cnt' || l_initial_count,104);
1618: hr_utility.set_location(' cnt' || l_rep_rec.COUNT,105);
1619:
1620: FOR i IN l_initial_count .. l_rep_rec.COUNT
1621: LOOP
1622: IF ( l_rep_rec(i).STMT_CREATED = 'N' OR l_rep_rec(i).STMT_CREATED = 'Y' ) THEN
1656:
1657: BEGIN
1658:
1659: g_proc := 'set_wlcm_flag';
1660: hr_utility.set_location('Entering '||g_proc,106);
1661: g_actn := 'updating ben_tcs_per_stmt_perd' ;
1662:
1663: WRITE ('In Procedure' ||g_proc);
1664: WRITE ('===========Reset welcome flag for the person============');
1669: WRITE ('=======================================================');
1670:
1671: SAVEPOINT setFlag;
1672:
1673: hr_utility.set_location('statement id : '||p_stmt_id || ' statement period id :' ||p_stmt_perd_id
1674: || 'person id :' ||p_person_id,107);
1675:
1676: SELECT show_wlcm_pg_flag
1677: INTO wlcm_flag
1680: AND stmt_perd_id = p_stmt_perd_id
1681: AND person_id = p_person_id ;
1682:
1683: IF (wlcm_flag = 'N') THEN
1684: hr_utility.set_location('Updating the welcome flag for person id : ' || p_person_id || ' : ' ||p_period_end_date ,108);
1685:
1686: get_emp_detail(
1687: null ,
1688: p_person_id ,
1718: ROLLBACK TO setFlag;
1719: END IF;
1720:
1721: WRITE ('Leaving set_wlcm_flag');
1722: hr_utility.set_location('Leaving '||g_proc,109);
1723: EXCEPTION
1724: WHEN OTHERS
1725: THEN
1726: l_rep_rec(rep_count ).STMT_CREATED := 'E';
1808: BEGIN
1809:
1810: g_actn := 'Started do_multithread for the thread ' || p_thread_id;
1811: g_proc := 'do_multithread';
1812: hr_utility.set_location('Entering '||g_proc,110);
1813:
1814: benutils.g_benefit_action_id := p_benefit_action_id;
1815:
1816: WRITE ('procedure :' ||g_proc );
1813:
1814: benutils.g_benefit_action_id := p_benefit_action_id;
1815:
1816: WRITE ('procedure :' ||g_proc );
1817: hr_utility.set_location (g_actn,111);
1818: WRITE ('=====================do_multithread=============');
1819: WRITE ('||Parameter Description ');
1820: WRITE ('||p_effective_dates - ' || p_effective_date);
1821: WRITE ('||p_validate - ' || p_validate);
1825: WRITE ('================================================');
1826:
1827: l_effective_date :=
1828: TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1829: hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1830: g_actn := 'Put row in fnd_sessions...';
1831: hr_utility.set_location (g_actn,113);
1832: hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1833: dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1827: l_effective_date :=
1828: TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1829: hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1830: g_actn := 'Put row in fnd_sessions...';
1831: hr_utility.set_location (g_actn,113);
1832: hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1833: dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1834: p_commit => l_commit
1835: );
1828: TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1829: hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1830: g_actn := 'Put row in fnd_sessions...';
1831: hr_utility.set_location (g_actn,113);
1832: hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1833: dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1834: p_commit => l_commit
1835: );
1836: -- need to check .
1883: END IF;
1884:
1885: --g_cache_person_process.DELETE;
1886: g_actn := 'Loading person data into g_cache_person_process cache...';
1887: hr_utility.set_location (g_actn,114);
1888: hr_utility.set_location ('Time' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),115);
1889:
1890: OPEN c_person_for_thread (p_benefit_action_id,
1891: l_start_person_action_id,
1884:
1885: --g_cache_person_process.DELETE;
1886: g_actn := 'Loading person data into g_cache_person_process cache...';
1887: hr_utility.set_location (g_actn,114);
1888: hr_utility.set_location ('Time' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),115);
1889:
1890: OPEN c_person_for_thread (p_benefit_action_id,
1891: l_start_person_action_id,
1892: l_end_person_action_id
1923: FOR l_cnt IN 1 .. l_record_number
1924: LOOP
1925: BEGIN
1926: g_actn := 'Calling the process for the person id ...'||g_cache_person_process (l_cnt).person_id ;
1927: hr_utility.set_location(g_actn,116);
1928: IF ( p_run_type = 'GEN') THEN
1929: hr_utility.set_location (' calling stmt_generation..',117);
1930: stmt_generation (
1931: p_validate => p_validate,
1925: BEGIN
1926: g_actn := 'Calling the process for the person id ...'||g_cache_person_process (l_cnt).person_id ;
1927: hr_utility.set_location(g_actn,116);
1928: IF ( p_run_type = 'GEN') THEN
1929: hr_utility.set_location (' calling stmt_generation..',117);
1930: stmt_generation (
1931: p_validate => p_validate,
1932: p_person_id => g_cache_person_process (l_cnt).person_id ,
1933: p_person_action_id => g_cache_person_process (l_cnt).person_action_id ,
1938: p_business_group => g_cache_person_process (l_cnt).bg_id,
1939: p_run_type => p_run_type,
1940: p_start_date => p_start_date );
1941:
1942: hr_utility.set_location('After the statement generation for the person id '|| g_cache_person_process (l_cnt).person_id,118);
1943:
1944: ELSIF (p_run_type = 'WLCM_SET') THEN
1945: g_actn := 'calling set_wlcm_flag..';
1946: hr_utility.set_location(g_actn,119);
1942: hr_utility.set_location('After the statement generation for the person id '|| g_cache_person_process (l_cnt).person_id,118);
1943:
1944: ELSIF (p_run_type = 'WLCM_SET') THEN
1945: g_actn := 'calling set_wlcm_flag..';
1946: hr_utility.set_location(g_actn,119);
1947:
1948: set_wlcm_flag ( p_validate => p_validate,
1949: p_person_id => g_cache_person_process (l_cnt).person_id ,
1950: p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1955: p_period_end_date => p_end_date,
1956: p_run_type =>p_run_type
1957: ) ;
1958:
1959: hr_utility.set_location('After the set welcome flag for the person id '|| g_cache_person_process (l_cnt).person_id,120);
1960:
1961: ELSIF (p_run_type = 'PURGE') THEN
1962:
1963: g_actn := 'calling purge_person_stmt..';
1960:
1961: ELSIF (p_run_type = 'PURGE') THEN
1962:
1963: g_actn := 'calling purge_person_stmt..';
1964: hr_utility.set_location(g_actn,121);
1965:
1966: purge_person_stmt(p_validate => p_validate,
1967: p_person_id => g_cache_person_process (l_cnt).person_id ,
1968: p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1972: p_business_group => g_cache_person_process (l_cnt).bg_id ,
1973: p_period_end_date => p_end_date,
1974: p_run_type =>p_run_type);
1975:
1976: hr_utility.set_location('After the purge for the person id '|| g_cache_person_process (l_cnt).person_id,122);
1977: END IF;
1978: EXCEPTION
1979: WHEN OTHERS
1980: THEN
2003:
2004: END LOOP;
2005: print_cache;
2006: WRITE ('Leaving do_multithread');
2007: hr_utility.set_location('Leaving '||g_proc,130);
2008: EXCEPTION
2009: WHEN OTHERS
2010: THEN
2011: WRITE (SQLERRM);
2037: l_num_rows NUMBER := p_per_actn_id_array.COUNT;
2038: BEGIN
2039: g_proc := 'insert_person_actions';
2040: WRITE ('In Procedure' ||g_proc);
2041: hr_utility.set_location('Entering '||g_proc,131);
2042: WRITE ( 'Time before inserting person actions '
2043: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2044:
2045: FORALL l_count IN 1 .. p_per_actn_id_array.COUNT
2084: WRITE ( 'Time at end of insert person actions '
2085: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2086:
2087: WRITE ('Leaving insert_person_actions');
2088: hr_utility.set_location('Leaving '||g_proc,132);
2089:
2090: END insert_person_actions;
2091:
2092: --
2348:
2349:
2350: BEGIN
2351: g_actn := 'Stating the Total Compensation Statement process : ' ;
2352: -- hr_utility.trace_on(null,'tcs');
2353: hr_utility.set_location('Entering '||g_actn,150);
2354:
2355: IF (p_run_type = 'GEN') THEN
2356: g_actn := g_actn || ' Statement Generation.' ;
2349:
2350: BEGIN
2351: g_actn := 'Stating the Total Compensation Statement process : ' ;
2352: -- hr_utility.trace_on(null,'tcs');
2353: hr_utility.set_location('Entering '||g_actn,150);
2354:
2355: IF (p_run_type = 'GEN') THEN
2356: g_actn := g_actn || ' Statement Generation.' ;
2357: ELSIF (p_run_type = 'PURGE') THEN
2432: g_exec_param_rec.start_time := DBMS_UTILITY.get_time;
2433: g_validate := p_validate ;
2434:
2435: g_actn := 'Calling ben_batch_utils.ini...';
2436: hr_utility.set_location (g_actn,151);
2437: hr_utility.set_location ('ben_batch_utils.ini with PROC_INFO',152);
2438: ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2439:
2440: g_actn := 'Calling benutils.get_parameter...';
2433: g_validate := p_validate ;
2434:
2435: g_actn := 'Calling ben_batch_utils.ini...';
2436: hr_utility.set_location (g_actn,151);
2437: hr_utility.set_location ('ben_batch_utils.ini with PROC_INFO',152);
2438: ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2439:
2440: g_actn := 'Calling benutils.get_parameter...';
2441: WRITE (g_actn);
2438: ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2439:
2440: g_actn := 'Calling benutils.get_parameter...';
2441: WRITE (g_actn);
2442: hr_utility.set_location(g_actn,153);
2443: WRITE ( 'benutils.get_parameter with '
2444: || p_business_group_id
2445: || ' '
2446: || 'BENTCSSP'
2543: l_count := l_count + 1;
2544: stmt_record_rec (l_count).statement_rec := stmt_rec2;
2545: END LOOP;
2546:
2547: hr_utility.set_location( 'Number of statement ids to be processed '||l_count,154);
2548: g_exec_param_rec.Number_Of_BGs :=l_count;
2549: CLOSE c_stmt_id_bg_id;
2550: /* ELSE
2551: l_business_group_id := HR_GENERAL.GET_BUSINESS_GROUP_ID ;
2565: CLOSE c_stmt_id;
2566:
2567: END IF;
2568: g_actn := 'After Processing statement id and Eligibility Id .';
2569: hr_utility.set_location( g_actn,155);
2570:
2571: IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
2572: WRITE ('****************statement validation ************');
2573: g_actn := 'Before statement validation ...' ;
2570:
2571: IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
2572: WRITE ('****************statement validation ************');
2573: g_actn := 'Before statement validation ...' ;
2574: hr_utility.set_location(g_actn,157);
2575: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2576: hr_utility.set_location (g_actn,158);
2577: WRITE ( 'Time before validating the stataement '
2578: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2572: WRITE ('****************statement validation ************');
2573: g_actn := 'Before statement validation ...' ;
2574: hr_utility.set_location(g_actn,157);
2575: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2576: hr_utility.set_location (g_actn,158);
2577: WRITE ( 'Time before validating the stataement '
2578: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2579: );
2580: hr_utility.set_location (g_actn,159);
2576: hr_utility.set_location (g_actn,158);
2577: WRITE ( 'Time before validating the stataement '
2578: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2579: );
2580: hr_utility.set_location (g_actn,159);
2581:
2582:
2583: ben_tcs_stmt_valid_hrchy.stmt_gen_valid_process (
2584: stmt_rec.stmt_id,
2588: subcat_hrchy_values,
2589: l_status) ;
2590:
2591: g_actn := 'After statement validation ...' ;
2592: hr_utility.set_location (g_actn,160);
2593: WRITE ( 'Time after validating the stataement '
2594: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2595: );
2596: WRITE (g_actn);
2637: END IF;
2638: WHILE (l_loop_cnt <= l_count)
2639: LOOP
2640: g_actn := 'Processing the statement :'|| stmt_record_rec (l_loop_cnt).statement_rec.stmt_id;
2641: hr_utility.set_location (g_actn,161);
2642: WRITE ('****************statement validation ************');
2643: g_actn := 'Before statement validation ...' ;
2644: hr_utility.set_location (g_actn,162);
2645: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2640: g_actn := 'Processing the statement :'|| stmt_record_rec (l_loop_cnt).statement_rec.stmt_id;
2641: hr_utility.set_location (g_actn,161);
2642: WRITE ('****************statement validation ************');
2643: g_actn := 'Before statement validation ...' ;
2644: hr_utility.set_location (g_actn,162);
2645: g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2646: WRITE (g_actn);
2647: WRITE ( 'Time before validating the stataement '
2648: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2655: item_hrchy_values, subcat_hrchy_values,
2656: l_status);
2657:
2658: g_actn := 'After statement validation ...' ;
2659: hr_utility.set_location (g_actn,163);
2660: WRITE ( 'Time after validating the stataement '
2661: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2662: );
2663: WRITE (g_actn);
2709: END IF;
2710:
2711: WRITE ('****************processing the person ************');
2712: g_actn := 'before processing person ids..' ;
2713: hr_utility.set_location (g_actn,164);
2714: WRITE ( 'Time before processing person ids .. '
2715: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2716: );
2717: WRITE (g_actn);
2717: WRITE (g_actn);
2718:
2719: IF ( p_person_id IS NOT NULL AND stmt_rec.valid_flag = 'Y') THEN
2720: OPEN c_person_valid_emp ( p_person_id ,l_period_start_date,l_period_end_date);
2721: hr_utility.set_location( 'processing the person id ' ||p_person_id,165);
2722: FETCH c_person_valid_emp into per_rec;
2723: IF c_person_valid_emp%NOTFOUND THEN
2724:
2725: get_name(l_business_group_id ,
2739: l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2740: l_rep_rec(rep_count ).SETUP_VALID := 'Y';
2741: l_rep_rec(rep_count ).TOTAL_PERSONS := 0 ;
2742:
2743: hr_utility.set_location('The person id :'||p_person_id || 'is not valid for this period' ||stmt_rec.stmt_id,165);
2744: fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_VALID_PERSON');
2745: fnd_message.raise_error;
2746:
2747: ELSE
2749: t_prof_tbl (1).compute_score_flag := 'N';
2750: t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
2751:
2752: IF ( stmt_rec.ee_id IS NOT NULL ) THEN
2753: hr_utility.set_location('checking the eligibity of the person_id id :'|| p_person_id ||'.Eligibility profile id is
2754: '||stmt_rec.ee_id ,166 );
2755:
2756: SELECT ELIGY_PRFL_ID INTO temp_ee_id
2757: FROM BEN_ELIGY_PRFL_F
2759: AND ELIGY_PRFL_ID = stmt_rec.ee_id
2760: AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
2761:
2762: t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id ;
2763: hr_utility.set_location( 'valid eligy id'|| temp_ee_id ,166) ;
2764: BEGIN
2765: IF (temp_ee_id is not null ) THEN
2766: hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...',167);
2767:
2762: t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id ;
2763: hr_utility.set_location( 'valid eligy id'|| temp_ee_id ,166) ;
2764: BEGIN
2765: IF (temp_ee_id is not null ) THEN
2766: hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...',167);
2767:
2768: ben_env_object.init(
2769: p_business_group_id=>l_business_group_id,
2770: p_effective_date =>l_period_end_date,
2784: p_comp_obj_mode => FALSE,
2785: p_score_tab => l_score_tab
2786: );
2787:
2788: hr_utility.set_location('After ben_evaluate_elig_profiles.eligible...',168);
2789: ELSE
2790: l_elig_return_status := TRUE ;
2791: END IF;
2792: EXCEPTION
2790: l_elig_return_status := TRUE ;
2791: END IF;
2792: EXCEPTION
2793: WHEN OTHERS THEN
2794: hr_utility.set_location('Exception : '||SQLERRM,10);
2795: l_elig_return_status := FALSE ;
2796: END;
2797: ELSE
2798: hr_utility.set_location('No eligibility profile attached to the statement' ||stmt_rec.stmt_id ||
2794: hr_utility.set_location('Exception : '||SQLERRM,10);
2795: l_elig_return_status := FALSE ;
2796: END;
2797: ELSE
2798: hr_utility.set_location('No eligibility profile attached to the statement' ||stmt_rec.stmt_id ||
2799: 'The person id '||p_person_id|| 'is eligible for processing .',169 );
2800:
2801: l_elig_return_status := TRUE ;
2802: END IF;
2847: WRITE ('=======================================================');
2848:
2849: IF l_num_rows = l_chunk_size THEN
2850: l_num_ranges := l_num_ranges + 1;
2851: hr_utility.set_location('inserting INTO person actions :..',170);
2852: g_actn := 'inserting INTO person actions : person id ' || p_person_id ;
2853: hr_utility.set_location(g_actn,171);
2854:
2855: insert_person_actions
2849: IF l_num_rows = l_chunk_size THEN
2850: l_num_ranges := l_num_ranges + 1;
2851: hr_utility.set_location('inserting INTO person actions :..',170);
2852: g_actn := 'inserting INTO person actions : person id ' || p_person_id ;
2853: hr_utility.set_location(g_actn,171);
2854:
2855: insert_person_actions
2856: (p_per_actn_id_array => l_person_action_ids,
2857: p_per_id => l_person_ids,
2867: l_bg_ids.DELETE;
2868: l_perd_ids.DELETE;
2869: END IF;
2870: ELSE
2871: hr_utility.set_location('The person id :'||p_person_id || 'is not eligible for the statement' ||stmt_rec.stmt_id ,171);
2872: --fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_ELIG_PERSON');
2873: --fnd_message.raise_error;
2874: get_name(l_business_group_id ,
2875: temp_ee_id ,
2893:
2894: ELSIF (l_business_group_id IS NOT NULL AND stmt_rec.valid_flag = 'Y' ) THEN
2895:
2896: g_actn := 'processing person ids for the business group id ' || l_business_group_id ;
2897: hr_utility.set_location(g_actn,172);
2898: temp_ee_id :=null;
2899: IF (stmt_rec.ee_id IS NOT NULL ) THEN
2900: SELECT ELIGY_PRFL_ID INTO temp_ee_id
2901: FROM BEN_ELIGY_PRFL_F
2929: INTO per_rec;
2930: EXIT WHEN c_person_selection%NOTFOUND;
2931:
2932: g_actn := 'processing the person id ' || per_rec.person_id ;
2933: hr_utility.set_location(g_actn,173);
2934: BEGIN
2935: IF (stmt_rec.ee_id IS NOT NULL ) THEN
2936: t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id;
2937: IF (temp_ee_id is not null ) THEN
2945: p_max_errors => l_max_errors_allowed,
2946: p_benefit_action_id => l_benefit_action_id
2947: );
2948:
2949: hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...' ,174);
2950: l_elig_return_status :=
2951: ben_evaluate_elig_profiles.eligible
2952: (p_person_id => per_rec.person_id,
2953: p_business_group_id => stmt_rec.bg_id ,
2955: p_eligprof_tab => t_prof_tbl,
2956: p_comp_obj_mode => FALSE,
2957: p_score_tab => l_score_tab
2958: );
2959: hr_utility.set_location('after ben_evaluate_elig_profiles.eligible...',175);
2960: ELSE
2961: l_elig_return_status := TRUE ;
2962: END IF;
2963:
2965: l_elig_return_status := TRUE ;
2966: END IF;
2967: EXCEPTION
2968: WHEN OTHERS THEN
2969: hr_utility.set_location('Exception : '||SQLERRM,10);
2970: l_elig_return_status := FALSE ;
2971: END;
2972: IF l_elig_return_status THEN
2973:
2970: l_elig_return_status := FALSE ;
2971: END;
2972: IF l_elig_return_status THEN
2973:
2974: hr_utility.set_location( 'The person id ' || per_rec.person_id || ' is eligible for the statement ' ||
2975: stmt_rec.stmt_id,176);
2976:
2977: l_num_rows := l_num_rows + 1;
2978: l_num_persons := l_num_persons + 1;
2981: l_stmt_ids.EXTEND (1);
2982: l_perd_ids.EXTEND (1);
2983: l_bg_ids.EXTEND (1);
2984:
2985: hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,177);
2986:
2987: SELECT ben_person_actions_s.NEXTVAL
2988: INTO l_person_action_ids (l_num_rows)
2989: FROM DUAL;
3005:
3006: IF l_num_rows = l_chunk_size
3007: THEN
3008: g_actn := 'inserting INTO person actions ';
3009: hr_utility.set_location(g_actn,178);
3010:
3011: l_num_ranges := l_num_ranges + 1;
3012: insert_person_actions
3013: (p_per_actn_id_array => l_person_action_ids,
3024: l_perd_ids.DELETE;
3025: l_bg_ids.DELETE;
3026: END IF;
3027: ELSE
3028: hr_utility.set_location( 'The person id ' || per_rec.person_id || ' is not eligible for the statement ' ||
3029: stmt_rec.stmt_id,179);
3030: END IF;
3031: END LOOP;
3032:
3056: LOOP
3057: l_person_temp := 0;
3058:
3059: g_actn := 'processing the stmt id ' || stmt_record_rec (l_loop_cnt).statement_rec.stmt_id ;
3060: hr_utility.set_location(g_actn,180);
3061:
3062: IF (stmt_record_rec (l_loop_cnt).statement_rec.valid_flag = 'Y' ) THEN
3063: OPEN c_person_selection (stmt_record_rec (l_loop_cnt).statement_rec.stmt_id,
3064: l_period_start_date,
3081: INTO per_rec;
3082: EXIT WHEN c_person_selection%NOTFOUND;
3083:
3084: g_actn := 'processing the person id ' || per_rec.person_id ;
3085: hr_utility.set_location(g_actn,181);
3086: BEGIN
3087: IF (stmt_record_rec (l_loop_cnt).statement_rec.ee_id IS NOT NULL ) THEN
3088:
3089: hr_utility.set_location ('checking the eligibility of the person id ' ||per_rec.person_id,182 );
3085: hr_utility.set_location(g_actn,181);
3086: BEGIN
3087: IF (stmt_record_rec (l_loop_cnt).statement_rec.ee_id IS NOT NULL ) THEN
3088:
3089: hr_utility.set_location ('checking the eligibility of the person id ' ||per_rec.person_id,182 );
3090:
3091: t_prof_tbl (1).eligy_prfl_id := stmt_record_rec (l_loop_cnt).statement_rec.ee_id;
3092:
3093: ben_env_object.init(
3098: p_threads => l_threads ,
3099: p_max_errors => l_max_errors_allowed,
3100: p_benefit_action_id => l_benefit_action_id
3101: );
3102: hr_utility.set_location('Before calling ben_evaluate_elig_profiles.eligible...',183);
3103: l_elig_return_status :=
3104: ben_evaluate_elig_profiles.eligible
3105: (p_person_id => per_rec.person_id,
3106: p_business_group_id => stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
3113: l_elig_return_status := TRUE ;
3114: END IF;
3115: EXCEPTION
3116: WHEN OTHERS THEN
3117: hr_utility.set_location('Exception : '||SQLERRM,10);
3118: l_elig_return_status := FALSE ;
3119: END;
3120: IF l_elig_return_status THEN
3121:
3127: l_stmt_ids.EXTEND (1);
3128: l_perd_ids.EXTEND (1);
3129: l_bg_ids.EXTEND (1);
3130:
3131: hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,184);
3132:
3133: SELECT ben_person_actions_s.NEXTVAL
3134: INTO l_person_action_ids (l_num_rows)
3135: FROM DUAL;
3150:
3151:
3152: IF l_num_rows = l_chunk_size THEN
3153: g_actn := 'inserting INTO person actions ';
3154: hr_utility.set_location(g_actn,185);
3155: l_num_ranges := l_num_ranges + 1;
3156: insert_person_actions
3157: (p_per_actn_id_array => l_person_action_ids,
3158: p_per_id => l_person_ids,
3168: l_bg_ids.DELETE;
3169: l_perd_ids.DELETE;
3170: END IF;
3171: ELSE
3172: hr_utility.set_location ('The person : ' ||per_rec.person_id || 'is not eligible',186);
3173: END IF;
3174: END LOOP;
3175:
3176:
3215: l_count := l_count + 1;
3216: stmt_record_rec1 (l_count).statement_rec1 := stmt_rec1;
3217: END LOOP;
3218:
3219: hr_utility.set_location ('Number of statement ids to be processed '||l_count,187);
3220: g_exec_param_rec.Number_Of_BGs :=l_count;
3221: CLOSE c_stmt_id_bg_id1;
3222: /* ELSE
3223: l_business_group_id := HR_GENERAL.GET_BUSINESS_GROUP_ID ;
3226: END IF;
3227:
3228: IF ( p_person_id IS NOT NULL)THEN
3229:
3230: hr_utility.set_location('checking for the person_id id ... '||p_person_id,188);
3231: g_exec_param_rec.Number_Of_BGs :=1;
3232: OPEN c_check_stmt_person(p_person_id ,p_stmt_id, p_period_id ) ;
3233:
3234: FETCH c_check_stmt_person into stmt_per_rec;
3284:
3285: IF l_num_rows = l_chunk_size THEN
3286: l_num_ranges := l_num_ranges + 1;
3287: g_actn := 'inserting INTO person actions : person id ' || p_person_id;
3288: hr_utility.set_location(g_actn,189);
3289: insert_person_actions
3290: (p_per_actn_id_array => l_person_action_ids,
3291: p_per_id => l_person_ids,
3292: p_benefit_action_id => l_benefit_action_id,
3318: END IF;
3319: CLOSE c_check_stmt_person ;
3320: ELSIF(l_business_group_id IS NOT NULL) THEN
3321: g_actn := 'processing the business group id ' || l_business_group_id ;
3322: hr_utility.set_location(g_actn,190);
3323: g_exec_param_rec.Number_Of_BGs :=1;
3324: OPEN c_check_stmt_avail(p_stmt_id , p_period_id ) ;
3325:
3326: t_prof_tbl (1).mndtry_flag := 'N';
3334: FETCH c_check_stmt_avail
3335: INTO per_rec;
3336: EXIT WHEN c_check_stmt_avail%NOTFOUND;
3337: g_actn := 'processing the person id ' || per_rec.person_id ;
3338: hr_utility.set_location(g_actn,191);
3339:
3340: l_num_rows := l_num_rows + 1;
3341: l_num_persons := l_num_persons + 1;
3342: l_person_action_ids.EXTEND (1);
3366:
3367: IF l_num_rows = l_chunk_size
3368: THEN
3369: g_actn := 'inserting INTO person actions ';
3370: hr_utility.set_location(g_actn,192);
3371: l_num_ranges := l_num_ranges + 1;
3372: insert_person_actions
3373: (p_per_actn_id_array => l_person_action_ids,
3374: p_per_id => l_person_ids,
3408: LOOP
3409: l_person_temp := 0;
3410: g_actn := 'processing the stmt id ' || stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id
3411: || 'period id :'||stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id;
3412: hr_utility.set_location(g_actn,193);
3413: 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 ) ;
3414: get_name(p_bg_id=> stmt_record_rec1 (l_loop_cnt).statement_rec1.bg_id ,
3415: v_period_end_date => l_period_end_date ,
3416: p_bg_name =>p_bg_name ,
3429: l_stmt_ids.EXTEND (1);
3430: l_perd_ids.EXTEND (1);
3431: l_bg_ids.EXTEND (1);
3432:
3433: hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,193);
3434:
3435:
3436: SELECT ben_person_actions_s.NEXTVAL
3437: INTO l_person_action_ids (l_num_rows)
3453:
3454:
3455: IF l_num_rows = l_chunk_size THEN
3456: g_actn := 'inserting INTO person actions ';
3457: hr_utility.set_location(g_actn,194);
3458: l_num_ranges := l_num_ranges + 1;
3459: insert_person_actions
3460: (p_per_actn_id_array => l_person_action_ids,
3461: p_per_id => l_person_ids,
3499: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
3500:
3501: IF l_num_rows <> 0 THEN
3502: l_num_ranges := l_num_ranges + 1;
3503: hr_utility.set_location('l_num_ranges '||l_num_ranges,500);
3504: insert_person_actions
3505: (p_per_actn_id_array => l_person_action_ids,
3506: p_per_id => l_person_ids,
3507: p_benefit_action_id => l_benefit_action_id,
3529: || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
3530:
3531: IF l_num_ranges > 1
3532: THEN
3533: hr_utility.set_location('L threads '||l_threads, 29);
3534: WRITE('l_threads'||l_threads );
3535: WRITE('l_num_ranges'||l_num_ranges );
3536: FOR loop_count IN 1 .. LEAST (l_threads, l_num_ranges) - 1
3537: LOOP
3561: ben_batch_utils.g_num_processes :=
3562: ben_batch_utils.g_num_processes + 1;
3563: ben_batch_utils.g_processes_tbl (ben_batch_utils.g_num_processes) :=
3564: l_request_id;
3565: hr_utility.set_location ('request id for this thread ' || l_request_id,160);
3566: COMMIT;
3567: END LOOP;
3568: ELSIF l_num_ranges = 0
3569: THEN
3578: WRITE ('||Parameter value ');
3579: WRITE ('||p_benefit_action_id- ' || l_benefit_action_id);
3580: WRITE ('||p_thread_id- ' || (l_threads + 1));
3581: WRITE ('==========================================================');
3582: hr_utility.set_location('L threads before calling do_mutithread'||l_threads, 29);
3583:
3584: do_multithread (errbuf => errbuf,
3585: retcode => retcode,
3586: p_validate => p_validate,
3608: WRITE ('||Number of persons processed - ' || l_num_persons);
3609: WRITE ('==========================================================');
3610:
3611: g_actn := 'Calling delete hrchy ...';
3612: hr_utility.set_location (g_actn,161);
3613: delete_hrchy;
3614: g_actn := 'Calling Hierarchy Set...';
3615: hr_utility.set_location (g_actn,162);
3616: hrchy_set(l_benefit_action_id);
3611: g_actn := 'Calling delete hrchy ...';
3612: hr_utility.set_location (g_actn,161);
3613: delete_hrchy;
3614: g_actn := 'Calling Hierarchy Set...';
3615: hr_utility.set_location (g_actn,162);
3616: hrchy_set(l_benefit_action_id);
3617: g_actn := 'Calling end_process...';
3618: hr_utility.set_location (g_actn,163);
3619: end_process (p_benefit_action_id => l_benefit_action_id
3614: g_actn := 'Calling Hierarchy Set...';
3615: hr_utility.set_location (g_actn,162);
3616: hrchy_set(l_benefit_action_id);
3617: g_actn := 'Calling end_process...';
3618: hr_utility.set_location (g_actn,163);
3619: end_process (p_benefit_action_id => l_benefit_action_id
3620: , p_person_selected => l_num_persons
3621: , p_business_group_id => l_business_group_id
3622: );
3620: , p_person_selected => l_num_persons
3621: , p_business_group_id => l_business_group_id
3622: );
3623: g_actn := 'Finished Process Procedure...';
3624: hr_utility.set_location (g_actn,164);
3625:
3626: IF (p_run_type ='GEN') THEN
3627: IF (g_validate = 'N') THEN
3628: IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
3661: END LOOP;
3662: END IF;
3663: END IF;
3664: END IF;
3665: -- hr_utility.trace_off;
3666: EXCEPTION
3667: --
3668: WHEN l_silent_error
3669: THEN