[Home] [Help]
126: l_proc constant varchar2(50):= g_package||'insert_archive_row';
127: l_ovn number;
128: l_action_id number;
129: begin
130: hr_utility.set_location('Entering: '||l_proc,1);
131: if p_tab_rec_data.count > 0 then
132: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
133:
134: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
130: hr_utility.set_location('Entering: '||l_proc,1);
131: if p_tab_rec_data.count > 0 then
132: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
133:
134: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
135: hr_utility.trace('action_context_id = '|| p_assactid);
136: hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
137: if p_tab_rec_data(i).action_info_category is not null then
138: pay_action_information_api.create_action_information(
131: if p_tab_rec_data.count > 0 then
132: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
133:
134: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
135: hr_utility.trace('action_context_id = '|| p_assactid);
136: hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
137: if p_tab_rec_data(i).action_info_category is not null then
138: pay_action_information_api.create_action_information(
139: p_action_information_id => l_action_id,
132: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
133:
134: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
135: hr_utility.trace('action_context_id = '|| p_assactid);
136: hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
137: if p_tab_rec_data(i).action_info_category is not null then
138: pay_action_information_api.create_action_information(
139: p_action_information_id => l_action_id,
140: p_object_version_number => l_ovn,
176: );
177: end if;
178: end loop;
179: end if;
180: hr_utility.set_location('Leaving: '||l_proc,999);
181: end insert_archive_row;
182:
183:
184: -------------------------------Procedure dyn_sql --------------------------------------
274:
275:
276:
277: begin
278: hr_utility.set_location('Entering '|| l_proc, 10);
279:
280:
281: hr_utility.set_location('Teachers Number : building teachers number start ', 20);
282:
277: begin
278: hr_utility.set_location('Entering '|| l_proc, 10);
279:
280:
281: hr_utility.set_location('Teachers Number : building teachers number start ', 20);
282:
283: open get_context_values;
284: fetch get_context_values into g_teach_dff_name,l_context,l_column;
285: close get_context_values;
287: if g_teach_dff_name is null then
288: fnd_file.put_line(fnd_file.log,'Staff Details - Teachers Number ');
289: end if;
290:
291: hr_utility.set_location('Teachers Number DFF name :'||g_teach_dff_name,20);
292: hr_utility.set_location('Teachers Number Context name :'||l_context,30);
293: hr_utility.set_location('Teachers Number Column name :'||l_column,40);
294:
295:
288: fnd_file.put_line(fnd_file.log,'Staff Details - Teachers Number ');
289: end if;
290:
291: hr_utility.set_location('Teachers Number DFF name :'||g_teach_dff_name,20);
292: hr_utility.set_location('Teachers Number Context name :'||l_context,30);
293: hr_utility.set_location('Teachers Number Column name :'||l_column,40);
294:
295:
296:
289: end if;
290:
291: hr_utility.set_location('Teachers Number DFF name :'||g_teach_dff_name,20);
292: hr_utility.set_location('Teachers Number Context name :'||l_context,30);
293: hr_utility.set_location('Teachers Number Column name :'||l_column,40);
294:
295:
296:
297:
310:
311: end if;
312:
313:
314: hr_utility.set_location('Teachers Number SQL Str :'||g_teacher_sql_str,60);
315: hr_utility.set_location('Teachers Number : building teachers number End ',70);
316: hr_utility.set_location('QT status : building QT status start ',80);
317:
318:
311: end if;
312:
313:
314: hr_utility.set_location('Teachers Number SQL Str :'||g_teacher_sql_str,60);
315: hr_utility.set_location('Teachers Number : building teachers number End ',70);
316: hr_utility.set_location('QT status : building QT status start ',80);
317:
318:
319: open get_qts_source;
312:
313:
314: hr_utility.set_location('Teachers Number SQL Str :'||g_teacher_sql_str,60);
315: hr_utility.set_location('Teachers Number : building teachers number End ',70);
316: hr_utility.set_location('QT status : building QT status start ',80);
317:
318:
319: open get_qts_source;
320: fetch get_qts_source into l_qts_grade_seg_name;
341: and pgd.'||l_qts_grade_seg_name||' between pcv.pcv_information2 and pcv_information3))';
342: end if;
343:
344:
345: hr_utility.set_location('QTS SQL Str :'||g_teacher_sql_str,90);
346: hr_utility.set_location('QT status : building QT status end ',100);
347:
348:
349: open get_qts_route_source;
342: end if;
343:
344:
345: hr_utility.set_location('QTS SQL Str :'||g_teacher_sql_str,90);
346: hr_utility.set_location('QT status : building QT status end ',100);
347:
348:
349: open get_qts_route_source;
350: fetch get_qts_route_source into l_qts_route_dff_name,l_qts_route_seg_name;
638: and pcv.business_group_id = :bg_id
639: and aei.'||l_addl_role_segment||' = pcv.pcv_information2';
640: end if;
641:
642: hr_utility.set_location('Leaving '|| l_proc, 110);
643: exception
644: when others then
645: hr_utility.set_location('Leaving '|| l_proc, 999);
646: hr_utility.set_location(sqlerrm,9999);
641:
642: hr_utility.set_location('Leaving '|| l_proc, 110);
643: exception
644: when others then
645: hr_utility.set_location('Leaving '|| l_proc, 999);
646: hr_utility.set_location(sqlerrm,9999);
647: hr_utility.raise_error;
648: end dyn_sql;
649: -------------------------------Procedure pay_message_lines ---------------------------
642: hr_utility.set_location('Leaving '|| l_proc, 110);
643: exception
644: when others then
645: hr_utility.set_location('Leaving '|| l_proc, 999);
646: hr_utility.set_location(sqlerrm,9999);
647: hr_utility.raise_error;
648: end dyn_sql;
649: -------------------------------Procedure pay_message_lines ---------------------------
650: -- Procedure to insert error messages to pay_message_lines
643: exception
644: when others then
645: hr_utility.set_location('Leaving '|| l_proc, 999);
646: hr_utility.set_location(sqlerrm,9999);
647: hr_utility.raise_error;
648: end dyn_sql;
649: -------------------------------Procedure pay_message_lines ---------------------------
650: -- Procedure to insert error messages to pay_message_lines
651: -- pragma autonomous_transaction is used here to isolate this transaction from
658: is
659: pragma autonomous_transaction;
660: l_proc constant varchar2(50):= g_package||'populate_run_msg';
661: begin
662: hr_utility.set_location(' Entering:'||l_proc,111);
663:
664: insert into pay_message_lines(line_sequence,
665: payroll_id,
666: message_level,
675: ,'A'
676: ,substr(p_message_text,1,240)
677: );
678:
679: hr_utility.set_location(' Entering:'||l_proc,999);
680: commit;
681: exception when others then
682: hr_utility.trace('Error occured in populate_run_msg');
683: hr_utility.set_location(' Leaving with error:'||l_proc,000);
678:
679: hr_utility.set_location(' Entering:'||l_proc,999);
680: commit;
681: exception when others then
682: hr_utility.trace('Error occured in populate_run_msg');
683: hr_utility.set_location(' Leaving with error:'||l_proc,000);
684: raise;
685: end populate_run_msg;
686:
679: hr_utility.set_location(' Entering:'||l_proc,999);
680: commit;
681: exception when others then
682: hr_utility.trace('Error occured in populate_run_msg');
683: hr_utility.set_location(' Leaving with error:'||l_proc,000);
684: raise;
685: end populate_run_msg;
686:
687: -------------------------------Procedure range_cursor --------------------------
695: is
696: l_proc constant varchar2(35) := g_package
697: ||'range_cursor';
698: begin
699: hr_utility.set_location('Entering: '
700: ||l_proc,1);
701:
702:
703: sqlstr := 'select distinct person_id '
706: ||'where ppa.payroll_action_id = :payroll_action_id '
707: ||'and ppa.business_group_id = ppf.business_group_id '
708: ||'order by ppf.person_id';
709:
710: hr_utility.trace(' Range Cursor Statement : '
711: ||sqlstr);
712:
713: hr_utility.set_location(' Leaving: '
714: ||l_proc,100);
709:
710: hr_utility.trace(' Range Cursor Statement : '
711: ||sqlstr);
712:
713: hr_utility.set_location(' Leaving: '
714: ||l_proc,100);
715: exception
716: when others then
717: hr_utility.set_location(' Leaving: '
713: hr_utility.set_location(' Leaving: '
714: ||l_proc,100);
715: exception
716: when others then
717: hr_utility.set_location(' Leaving: '
718: ||l_proc,50);
719:
720: fnd_file.put_line(fnd_file.log,substr('Error in rangecode '
721: ||sqlerrm(sqlcode),1,80));
774:
775:
776: begin
777:
778: hr_utility.set_location('Entering: '||l_proc,1);
779:
780: open csr_check_action;
781: fetch csr_check_action into l_action;
782: close csr_check_action;
786: else
787: l_ret := false;
788: end if;
789:
790: hr_utility.set_location('Leaving: '||l_proc,999);
791: return l_ret;
792: end check_max_action;
793: ------------------------------procedure action_creation-------------------------
794: -- creates assignment action for the assignments selected by the cursor
1012: e_contract_type_nt_defined exception;
1013: l_contract_type varchar2(50);
1014: begin
1015: if chunk = 1 then
1016: hr_utility.set_location('Entering: '||l_proc,1);
1017: end if;
1018: -----
1019: open csr_parameter_info;
1020: fetch csr_parameter_info into g_census_year,
1047: fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proce');
1048: raise e_contract_type_nt_defined;
1049: end if;
1050: if chunk = 1 then
1051: hr_utility.set_location('g_pick_from_asg : ' || g_pick_from_asg,30);
1052: end if;
1053:
1054:
1055: if chunk = 1 then
1052: end if;
1053:
1054:
1055: if chunk = 1 then
1056: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1057: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1053:
1054:
1055: if chunk = 1 then
1056: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1057: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1054:
1055: if chunk = 1 then
1056: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1057: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1055: if chunk = 1 then
1056: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1057: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1056: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1057: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1057: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1065: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1058: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1065: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1066: end if;
1059: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1065: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1066: end if;
1067:
1060: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1065: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1066: end if;
1067:
1068: if g_pick_from_asg = 'N' then
1061: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063: hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1065: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1066: end if;
1067:
1068: if g_pick_from_asg = 'N' then
1069:
1070: for asg_rec in csr_contract(l_asg_set) loop
1071: -- Check if assignment action already created for this assignment
1072: l_arch := check_action(pactid,asg_rec.assignment_id);
1073: if l_arch then
1074: -- hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
1075: select pay_assignment_actions_s.nextval
1076: into l_ass_act_id
1077: from dual;
1078: --
1085: end if;
1086: end loop; -- end asg_rec
1087: elsif g_pick_from_asg = 'Y' then
1088: if chunk = 1 then
1089: hr_utility.set_location('Entering csr_asg',40);
1090: end if;
1091: for asg_rec in csr_asg(l_asg_set) loop
1092: -- Check if assignment action already created for this assignment
1093: l_arch := check_action(pactid,asg_rec.assignment_id);
1091: for asg_rec in csr_asg(l_asg_set) loop
1092: -- Check if assignment action already created for this assignment
1093: l_arch := check_action(pactid,asg_rec.assignment_id);
1094: if l_arch then
1095: -- hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
1096: select pay_assignment_actions_s.nextval
1097: into l_ass_act_id
1098: from dual;
1099: --
1124: e_teacher_no_nt_found exception;
1125: l_teachers_num varchar2(100);
1126: l_proc constant varchar2(50) := g_package || ' get_teachers_number';
1127: begin
1128: hr_utility.set_location('Entering '|| l_proc, 10);
1129:
1130: if g_debug then
1131: hr_utility.set_location('p_person_id :'||p_person_id,10);
1132: hr_utility.set_location('p_effective_date :'||p_effective_date,10);
1127: begin
1128: hr_utility.set_location('Entering '|| l_proc, 10);
1129:
1130: if g_debug then
1131: hr_utility.set_location('p_person_id :'||p_person_id,10);
1132: hr_utility.set_location('p_effective_date :'||p_effective_date,10);
1133: end if;
1134:
1135: if g_teach_dff_name = 'PER_PEOPLE' then
1128: hr_utility.set_location('Entering '|| l_proc, 10);
1129:
1130: if g_debug then
1131: hr_utility.set_location('p_person_id :'||p_person_id,10);
1132: hr_utility.set_location('p_effective_date :'||p_effective_date,10);
1133: end if;
1134:
1135: if g_teach_dff_name = 'PER_PEOPLE' then
1136:
1154:
1155: -- Bug#12599238
1156:
1157:
1158: hr_utility.set_location('Leaving '|| l_proc, 100);
1159: exception when
1160: no_data_found then
1161: hr_utility.set_location('Leaving with error '|| l_proc, 888);
1162: return null;
1157:
1158: hr_utility.set_location('Leaving '|| l_proc, 100);
1159: exception when
1160: no_data_found then
1161: hr_utility.set_location('Leaving with error '|| l_proc, 888);
1162: return null;
1163: when others then
1164: hr_utility.set_location('Leaving with error '|| l_proc, 999);
1165: hr_utility.set_location(sqlerrm,999);
1160: no_data_found then
1161: hr_utility.set_location('Leaving with error '|| l_proc, 888);
1162: return null;
1163: when others then
1164: hr_utility.set_location('Leaving with error '|| l_proc, 999);
1165: hr_utility.set_location(sqlerrm,999);
1166: return null;
1167: end get_teachers_number;
1168:
1161: hr_utility.set_location('Leaving with error '|| l_proc, 888);
1162: return null;
1163: when others then
1164: hr_utility.set_location('Leaving with error '|| l_proc, 999);
1165: hr_utility.set_location(sqlerrm,999);
1166: return null;
1167: end get_teachers_number;
1168:
1169: ------------------------------function get_dcsf_values--------------------------
1306: l_asg_end_date date;
1307:
1308:
1309: begin
1310: hr_utility.set_location('Entering: '||l_proc,1);
1311: l_arch := true;
1312:
1313:
1314: hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1310: hr_utility.set_location('Entering: '||l_proc,1);
1311: l_arch := true;
1312:
1313:
1314: hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1315: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1311: l_arch := true;
1312:
1313:
1314: hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1315: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1312:
1313:
1314: hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1315: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1313:
1314: hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1315: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1314: hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1315: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1315: hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1316: hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1317: hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325: hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1318: hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325: hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1326: hr_utility.set_location('g_hlta_sql_str : ' || g_hlta_sql_str,30);
1319: hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325: hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1326: hr_utility.set_location('g_hlta_sql_str : ' || g_hlta_sql_str,30);
1327:
1320: hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325: hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1326: hr_utility.set_location('g_hlta_sql_str : ' || g_hlta_sql_str,30);
1327:
1328: -- check if staff is present as of census day
1321: hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325: hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1326: hr_utility.set_location('g_hlta_sql_str : ' || g_hlta_sql_str,30);
1327:
1328: -- check if staff is present as of census day
1329: open csr_person_details(g_census_day);
1322: hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323: hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324: hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325: hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1326: hr_utility.set_location('g_hlta_sql_str : ' || g_hlta_sql_str,30);
1327:
1328: -- check if staff is present as of census day
1329: open csr_person_details(g_census_day);
1330: fetch csr_person_details into l_staff_rec;
1329: open csr_person_details(g_census_day);
1330: fetch csr_person_details into l_staff_rec;
1331: if csr_person_details%notfound then
1332: l_asg_active_on_census_day := false; -- continuous data to be archived
1333: hr_utility.set_location('Staff record not present on census day'||'Assignment act ID :'||p_assactid,1);
1334: end if;
1335: close csr_person_details;
1336:
1337: -- if staff present as of census day then the effective date for the contract
1381:
1382: open csr_person_details(l_effective_date);
1383: fetch csr_person_details into l_staff_rec;
1384: if csr_person_details%notfound then
1385: hr_utility.set_location('Staff record could not be fetched as of ' || l_effective_date ||' for Assignment act ID :'||p_assactid,9999);
1386: fnd_file.put_line(fnd_file.log,'Staff record could not be fetched as of ' || l_effective_date ||' for Assignment act ID :'||p_assactid);
1387: --raise e_cont_eff_dt_nt_found;
1388: end if;
1389: close csr_person_details;
1397: close get_estab_no;
1398:
1399: if g_estb_number is not null and g_estb_number <> l_estab_number then
1400: l_arch := false;
1401: hr_utility.set_location('This assignment should not be processed',99);
1402: end if;
1403:
1404:
1405:
1411: -- continues
1412: begin
1413: l_teachers_number:= get_teachers_number(l_person_id,l_effective_date);
1414: exception when others then
1415: hr_utility.set_location('Teachers Number could not be fetched as of ' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1416: end;
1417:
1418: begin
1419: execute immediate g_qts_sql_str into l_qt_status using g_business_group_id,l_effective_date, l_person_id;
1417:
1418: begin
1419: execute immediate g_qts_sql_str into l_qt_status using g_business_group_id,l_effective_date, l_person_id;
1420: exception when others then
1421: hr_utility.set_location('QT status could not be fetched as of ' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1422: end;
1423:
1424: hr_utility.set_location('l_qt_status :'||l_qt_status,10);
1425:
1420: exception when others then
1421: hr_utility.set_location('QT status could not be fetched as of ' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1422: end;
1423:
1424: hr_utility.set_location('l_qt_status :'||l_qt_status,10);
1425:
1426: begin
1427: execute immediate g_qts_route_sql_str into l_qts_route using g_business_group_id,l_effective_date, l_person_id;
1428: exception when others then
1425:
1426: begin
1427: execute immediate g_qts_route_sql_str into l_qts_route using g_business_group_id,l_effective_date, l_person_id;
1428: exception when others then
1429: hr_utility.set_location('QTS Route could not be fetched as of' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1430: end;
1431:
1432: begin
1433: if g_hlta_dff_name = 'JOB' then
1436: execute immediate g_hlta_sql_str into l_hlta_status using l_person_id, l_effective_date;
1437: end if;
1438:
1439: exception when others then
1440: hr_utility.set_location('HLTA status could not be fetched as of' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1441: end;
1442:
1443: -- Specific staff details can be entered in the extra person information
1444: -- fetch those values and use them if the computed value(thru configuration)
1464:
1465: -- 4100
1466: if l_qt_status = 'Yes' and l_teachers_number is null then
1467: l_arch := false;
1468: hr_utility.set_location('Teachers number error',10);
1469: populate_run_msg(p_assactid,'Qualified Teacher with Teacher Number missing');
1470: end if;
1471:
1472: -- 4105
1471:
1472: -- 4105
1473: if l_teachers_number is not null and length(l_teachers_number) <> 7 then
1474: l_arch := false;
1475: hr_utility.set_location('Teachers number error',20);
1476: populate_run_msg(p_assactid,'Teacher number is not 7 digits');
1477: end if;
1478:
1479:
1480: begin
1481: l_teachers_number := to_number(l_teachers_number);
1482: exception when others then
1483: l_arch := false;
1484: hr_utility.set_location('Teachers number has invalid characters',20);
1485: populate_run_msg(p_assactid,'Teachers number has invalid characters');
1486: end;
1487:
1488:
1489: -- 4110
1490: if l_staff_rec.last_name is null then
1491: l_arch := false;
1492: populate_run_msg(p_assactid,'Family Name is missing');
1493: hr_utility.set_location('last Name error',10);
1494: elsif l_staff_rec.last_name is not null and instr(l_staff_rec.last_name,' ') > 0 then
1495: l_arch := false;
1496: populate_run_msg(p_assactid,'Family Name contains too many consecutive spaces');
1497: hr_utility.set_location('last Name error',10);
1493: hr_utility.set_location('last Name error',10);
1494: elsif l_staff_rec.last_name is not null and instr(l_staff_rec.last_name,' ') > 0 then
1495: l_arch := false;
1496: populate_run_msg(p_assactid,'Family Name contains too many consecutive spaces');
1497: hr_utility.set_location('last Name error',10);
1498: end if;
1499:
1500: -- 4120
1501: if l_staff_rec.first_name is null then
1499:
1500: -- 4120
1501: if l_staff_rec.first_name is null then
1502: l_arch := false;
1503: hr_utility.set_location('First Name error',10);
1504: populate_run_msg(p_assactid,'Given Name is missing');
1505: elsif l_staff_rec.first_name is not null and instr(l_staff_rec.first_name,' ') > 0 then
1506: l_arch := false;
1507: hr_utility.set_location('First Name error',20);
1503: hr_utility.set_location('First Name error',10);
1504: populate_run_msg(p_assactid,'Given Name is missing');
1505: elsif l_staff_rec.first_name is not null and instr(l_staff_rec.first_name,' ') > 0 then
1506: l_arch := false;
1507: hr_utility.set_location('First Name error',20);
1508: populate_run_msg(p_assactid,'Given Name contains too many consecutive spaces');
1509: end if;
1510:
1511: -- 4140
1510:
1511: -- 4140
1512: if instr(l_staff_rec.previous_last_name,' ') > 0 then
1513: l_arch := false;
1514: hr_utility.set_location('Previous Name error',10);
1515: populate_run_msg(p_assactid,'Former Family Name contains too many consecutive spaces');
1516: end if;
1517:
1518: -- 4150,4160Q,4155
1518: -- 4150,4160Q,4155
1519: if l_staff_rec.national_identifier is null then
1520: l_arch := false ;
1521: populate_run_msg(p_assactid,'Member of workforce with missing NI Number');
1522: hr_utility.set_location('NI error',20);
1523: elsif l_staff_rec.national_identifier is not null and
1524: hr_gb_utility.ni_validate(l_staff_rec.national_identifier,sysdate) <> 0 then
1525: l_arch := false;
1526: populate_run_msg(p_assactid,'NI Number has invalid Format');
1523: elsif l_staff_rec.national_identifier is not null and
1524: hr_gb_utility.ni_validate(l_staff_rec.national_identifier,sysdate) <> 0 then
1525: l_arch := false;
1526: populate_run_msg(p_assactid,'NI Number has invalid Format');
1527: hr_utility.set_location('NI error',10);
1528: if substr(l_staff_rec.national_identifier,1,2) in ('GB','BG','NK','KN','TN','NT','ZZ') then
1529: l_arch := false;
1530: populate_run_msg(p_assactid,'Appears to be a temporary or non-standard NI Number. This must be resolved or removed');
1531: hr_utility.set_location('NI error',10);
1527: hr_utility.set_location('NI error',10);
1528: if substr(l_staff_rec.national_identifier,1,2) in ('GB','BG','NK','KN','TN','NT','ZZ') then
1529: l_arch := false;
1530: populate_run_msg(p_assactid,'Appears to be a temporary or non-standard NI Number. This must be resolved or removed');
1531: hr_utility.set_location('NI error',10);
1532: end if;
1533: end if;
1534:
1535: --4180
1535: --4180
1536: if l_staff_rec.sex is null then
1537: l_arch := false;
1538: populate_run_msg(p_assactid,'Gender is missing');
1539: hr_utility.set_location('Gender error',10);
1540: end if;
1541:
1542: -- 4190
1543: if l_staff_rec.dob_dcsf is null then
1542: -- 4190
1543: if l_staff_rec.dob_dcsf is null then
1544: l_arch := false;
1545: populate_run_msg(p_assactid,'Date of Birth missing');
1546: hr_utility.set_location('DOB error',30);
1547: end if;
1548:
1549: -- 4200 /* Corrected the code.Bug#12586059 */
1550: if not(months_between(g_census_day,l_staff_rec.date_of_birth)/12 between 15 and 100) then
1549: -- 4200 /* Corrected the code.Bug#12586059 */
1550: if not(months_between(g_census_day,l_staff_rec.date_of_birth)/12 between 15 and 100) then
1551: l_arch := false;
1552: populate_run_msg(p_assactid,'Person''s age must be between 15 and 100 years');
1553: hr_utility.set_location('Age error',30);
1554: end if;
1555:
1556: -- Bug#12586059
1557: -- check removed as per new specification.
1558:
1559: -- 4210Q
1560: -- if not(months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 between 16 and 70) then
1561: -- populate_run_msg(p_assactid,'Please check: Person''s age expected to be between 16 and 70 years','W');
1562: -- hr_utility.set_location('Age warning',30);
1563: -- end if;
1564:
1565: -- Bug#12586059
1566:
1580: when others
1581: then
1582: l_arch := false;
1583: populate_run_msg (p_assactid, 'Ethnicity is invalid ');
1584: hr_utility.set_location ('Ethnicity error', 10);
1585: end;
1586: end if;
1587:
1588: if l_staff_rec.ethnic_code is not null and length(l_staff_rec.ethnic_code) <> 4 then
1587:
1588: if l_staff_rec.ethnic_code is not null and length(l_staff_rec.ethnic_code) <> 4 then
1589: l_arch := false;
1590: populate_run_msg (p_assactid, 'Ethnicity is invalid ');
1591: hr_utility.set_location ('Ethnicity length error', 10);
1592: end if;
1593:
1594: if l_staff_rec.ethnic_code is null then
1595: populate_run_msg (p_assactid, 'Please Check: Ethnicity is missing ','W');
1592: end if;
1593:
1594: if l_staff_rec.ethnic_code is null then
1595: populate_run_msg (p_assactid, 'Please Check: Ethnicity is missing ','W');
1596: hr_utility.set_location ('Ethnicity length error', 10);
1597: end if;
1598:
1599: -- 4225 Disablity hardcoded, cannot be other than YES or NO.
1600: -- 4230
1600: -- 4230
1601: if l_qt_status is not null and l_qt_status not in ('Yes','No') then
1602: l_arch := false;
1603: populate_run_msg (p_assactid, 'QT Status is invalid ');
1604: hr_utility.set_location (l_qt_status||' QT status error', 10);
1605: end if;
1606:
1607: -- 4235Q
1608: if l_qt_status = 'Yes' and months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 < 21 then
1606:
1607: -- 4235Q
1608: if l_qt_status = 'Yes' and months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 < 21 then
1609: populate_run_msg (p_assactid, 'Person cannot be shown as having QT status and be under 21 on 1 January','W');
1610: hr_utility.set_location ('QT status Age error', 10);
1611: end if;
1612:
1613: -- 4240
1614: if l_hlta_status is not null and l_hlta_status not in ('Yes','No') then
1613: -- 4240
1614: if l_hlta_status is not null and l_hlta_status not in ('Yes','No') then
1615: l_arch := false;
1616: populate_run_msg (p_assactid, 'HLTA Status is invalid ');
1617: hr_utility.set_location (l_hlta_status||' HLTA status error', 10);
1618: end if;
1619:
1620: -- 4245
1621: if l_hlta_status = 'Yes' and months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 < 18 then
1620: -- 4245
1621: if l_hlta_status = 'Yes' and months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 < 18 then
1622: l_arch := false;
1623: populate_run_msg (p_assactid, 'Person cannot be shown as having HLTA status and be under 18 on 1 January');
1624: hr_utility.set_location (l_hlta_status||' HLTA status Age error', 10);
1625: end if;
1626:
1627: -- 4250
1628: if l_qts_route is not null then
1639: when others
1640: then
1641: l_arch := false;
1642: populate_run_msg (p_assactid, 'QTS Route is invalid');
1643: hr_utility.set_location (l_qts_route||' QTS route error', 10);
1644: end;
1645: end if;
1646:
1647: if l_qts_route is not null and length(l_qts_route) <> 4 then
1646:
1647: if l_qts_route is not null and length(l_qts_route) <> 4 then
1648: l_arch := false;
1649: populate_run_msg (p_assactid, 'QTS Route is invalid');
1650: hr_utility.set_location (l_qts_route||' QTS route length error', 10);
1651: end if;
1652:
1653: -- DO NOT CHANGE the archive structure as many values from this are passed as
1654: -- input values for othe procedures in archive code
1675: p_staff_rec.act_info16 := l_effective_date; -- passed as in parameter to all functions
1676: -- called in archive code
1677:
1678:
1679: hr_utility.set_location('Leaving: '||l_proc,999);
1680: return l_arch;
1681: exception
1682: when others then
1683: hr_utility.trace(sqlerrm);
1679: hr_utility.set_location('Leaving: '||l_proc,999);
1680: return l_arch;
1681: exception
1682: when others then
1683: hr_utility.trace(sqlerrm);
1684: hr_utility.set_location('leaving with error: '||l_proc,9999);
1685: raise;
1686: end fetch_staff_details;
1687:
1680: return l_arch;
1681: exception
1682: when others then
1683: hr_utility.trace(sqlerrm);
1684: hr_utility.set_location('leaving with error: '||l_proc,9999);
1685: raise;
1686: end fetch_staff_details;
1687:
1688: /*-------------------------function run_user_formula --------------------------*/
1716: l_warning_msg varchar2(300);
1717:
1718: l_proc constant varchar2(50):= g_package||'run_user_formula';
1719: begin
1720: hr_utility.set_location('Entering: '||l_proc,10);
1721: -- setting the contexts
1722: p_inputs(1).name := 'ASSIGNMENT_ID';
1723: p_inputs(1).value := p_assignment_id;
1724: p_inputs(2).name := 'EFFECTIVE_DATE';
1729: l_formula_id := null;
1730: open get_formula_id;
1731: fetch get_formula_id into l_formula_id,l_formula_name;
1732: if get_formula_id%notfound then
1733: hr_utility.set_location('formula -'||l_formula_name||'- not present/effective in table',11);
1734: l_warning_msg := 'formula -'||l_formula_name||'- not present or effective for assignment '||p_assignment_number||' on '||p_effective_date;
1735: fnd_file.put_line (fnd_file.log, l_warning_msg);
1736: end if;
1737: close get_formula_id;
1736: end if;
1737: close get_formula_id;
1738:
1739: if l_formula_id is not null then
1740: hr_utility.trace(' Inside run_user_formula '||l_formula_name);
1741: ff_exec.init_formula(l_formula_id, p_effective_date , l_inputs, l_outputs);
1742:
1743: if l_inputs.count > 0 and p_inputs.count > 0 then
1744: for i in 1..l_inputs.count
1753: end loop;
1754: end if;
1755:
1756: for i in 1..l_inputs.count loop
1757: hr_utility.trace(' i= '||i||' name '||l_inputs(i).name ||' value '||l_inputs(i).value);
1758: end loop;
1759:
1760: ff_exec.run_formula(l_inputs,l_outputs);
1761: hr_utility.trace(' calculated value from user formula '||l_outputs(1).value);
1757: hr_utility.trace(' i= '||i||' name '||l_inputs(i).name ||' value '||l_inputs(i).value);
1758: end loop;
1759:
1760: ff_exec.run_formula(l_inputs,l_outputs);
1761: hr_utility.trace(' calculated value from user formula '||l_outputs(1).value);
1762: l_result := l_outputs(1).value ;
1763: end if;
1764:
1765: hr_utility.set_location('Leaving: '||l_proc,999);
1761: hr_utility.trace(' calculated value from user formula '||l_outputs(1).value);
1762: l_result := l_outputs(1).value ;
1763: end if;
1764:
1765: hr_utility.set_location('Leaving: '||l_proc,999);
1766:
1767: return l_result;
1768:
1769: exception
1767: return l_result;
1768:
1769: exception
1770: when others then
1771: hr_utility.trace(sqlerrm);
1772: hr_utility.set_location('leaving with error: '||l_proc,9999);
1773: raise;
1774: end;
1775:
1768:
1769: exception
1770: when others then
1771: hr_utility.trace(sqlerrm);
1772: hr_utility.set_location('leaving with error: '||l_proc,9999);
1773: raise;
1774: end;
1775:
1776:
1802: l_warning_msg varchar2(300);
1803:
1804: l_proc constant varchar2(50):= g_package||'run_seeded_formula';
1805: begin
1806: hr_utility.set_location('Entering: '||l_proc,10);
1807: -- setting the contexts
1808: p_inputs(1).name := 'ASSIGNMENT_ID';
1809: p_inputs(1).value := p_assignment_id;
1810: p_inputs(2).name := 'DATE_EARNED';
1815: l_formula_id := null;
1816: open get_formula_id;
1817: fetch get_formula_id into l_formula_id,l_formula_name;
1818: if get_formula_id%notfound then
1819: hr_utility.set_location('formula -'||l_formula_name||'- not present/effective in table',11);
1820: l_warning_msg := 'formula -'||l_formula_name||'- not present or effective for assignment '||p_assignment_number||' on '||p_effective_date;
1821: fnd_file.put_line (fnd_file.log, l_warning_msg);
1822: end if;
1823: close get_formula_id;
1822: end if;
1823: close get_formula_id;
1824:
1825: if l_formula_id is not null then
1826: hr_utility.trace(' Inside run_seeded_formula '||l_formula_name);
1827: ff_exec.init_formula(l_formula_id, p_effective_date , l_inputs, l_outputs);
1828:
1829: if l_inputs.count > 0 and p_inputs.count > 0 then
1830: for i in 1..l_inputs.count
1839: end loop;
1840: end if;
1841:
1842: for i in 1..l_inputs.count loop
1843: hr_utility.trace(' i= '||i||' name '||l_inputs(i).name ||' value '||l_inputs(i).value);
1844: end loop;
1845:
1846: ff_exec.run_formula(l_inputs,l_outputs);
1847:
1845:
1846: ff_exec.run_formula(l_inputs,l_outputs);
1847:
1848: for i in 1..l_outputs.count loop
1849: hr_utility.trace(' i= '||i||' name '||l_outputs(i).name ||' value '||l_outputs(i).value);
1850:
1851: if l_outputs(i).name = 'FTE' then
1852: fte_ratio := l_outputs(i).value;
1853: end if;
1858: end loop;
1859:
1860: end if;
1861:
1862: hr_utility.set_location('Leaving: '||l_proc,999);
1863:
1864: exception
1865: when others then
1866: hr_utility.trace(sqlerrm);
1862: hr_utility.set_location('Leaving: '||l_proc,999);
1863:
1864: exception
1865: when others then
1866: hr_utility.trace(sqlerrm);
1867: hr_utility.set_location('leaving with error: '||l_proc,9999);
1868: raise;
1869: end run_seeded_formula;
1870:
1863:
1864: exception
1865: when others then
1866: hr_utility.trace(sqlerrm);
1867: hr_utility.set_location('leaving with error: '||l_proc,9999);
1868: raise;
1869: end run_seeded_formula;
1870:
1871: ---------------------------function fetch_addl_payment_details -----------------
1877:
1878: l_proc constant varchar2(50):= g_package||'fetch_addl_payment_details';
1879:
1880: begin
1881: hr_utility.set_location('Entering: '||l_proc,10);
1882: hr_utility.set_location('Parameters : ',20);
1883: hr_utility.set_location('p_assactid : '||p_assactid,20);
1884: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1885: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1878: l_proc constant varchar2(50):= g_package||'fetch_addl_payment_details';
1879:
1880: begin
1881: hr_utility.set_location('Entering: '||l_proc,10);
1882: hr_utility.set_location('Parameters : ',20);
1883: hr_utility.set_location('p_assactid : '||p_assactid,20);
1884: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1885: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1886:
1879:
1880: begin
1881: hr_utility.set_location('Entering: '||l_proc,10);
1882: hr_utility.set_location('Parameters : ',20);
1883: hr_utility.set_location('p_assactid : '||p_assactid,20);
1884: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1885: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1886:
1887: begin
1880: begin
1881: hr_utility.set_location('Entering: '||l_proc,10);
1882: hr_utility.set_location('Parameters : ',20);
1883: hr_utility.set_location('p_assactid : '||p_assactid,20);
1884: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1885: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1886:
1887: begin
1888: select pexi.eei_information1,to_char(nvl(sum(prrv.result_value),0),'fm999999.00')
1881: hr_utility.set_location('Entering: '||l_proc,10);
1882: hr_utility.set_location('Parameters : ',20);
1883: hr_utility.set_location('p_assactid : '||p_assactid,20);
1884: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1885: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1886:
1887: begin
1888: select pexi.eei_information1,to_char(nvl(sum(prrv.result_value),0),'fm999999.00')
1889: bulk collect into p_addl_payments
1916: group by pexi.eei_information1;
1917:
1918: exception
1919: when others then
1920: hr_utility.trace(sqlerrm);
1921: hr_utility.set_location('leaving with error: '||l_proc,9999);
1922: raise;
1923: end;
1924: hr_utility.set_location('Leaving: '||l_proc,99);
1917:
1918: exception
1919: when others then
1920: hr_utility.trace(sqlerrm);
1921: hr_utility.set_location('leaving with error: '||l_proc,9999);
1922: raise;
1923: end;
1924: hr_utility.set_location('Leaving: '||l_proc,99);
1925: return true;
1920: hr_utility.trace(sqlerrm);
1921: hr_utility.set_location('leaving with error: '||l_proc,9999);
1922: raise;
1923: end;
1924: hr_utility.set_location('Leaving: '||l_proc,99);
1925: return true;
1926: end fetch_addl_payment_details;
1927:
1928: ---------------------------function fetch_role_details -------------------------
1960: l_role_tab_idx pls_integer;
1961:
1962:
1963: begin
1964: hr_utility.set_location('Entering: '||l_proc,10);
1965: hr_utility.set_location('p_assactid : '||p_assactid,20);
1966: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1967: hr_utility.set_location('p_person_id : '||p_person_id,20);
1968: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1961:
1962:
1963: begin
1964: hr_utility.set_location('Entering: '||l_proc,10);
1965: hr_utility.set_location('p_assactid : '||p_assactid,20);
1966: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1967: hr_utility.set_location('p_person_id : '||p_person_id,20);
1968: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1969:
1962:
1963: begin
1964: hr_utility.set_location('Entering: '||l_proc,10);
1965: hr_utility.set_location('p_assactid : '||p_assactid,20);
1966: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1967: hr_utility.set_location('p_person_id : '||p_person_id,20);
1968: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1969:
1970:
1963: begin
1964: hr_utility.set_location('Entering: '||l_proc,10);
1965: hr_utility.set_location('p_assactid : '||p_assactid,20);
1966: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1967: hr_utility.set_location('p_person_id : '||p_person_id,20);
1968: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1969:
1970:
1971: if g_role_src in ('JOB','GRD','POS') then
1964: hr_utility.set_location('Entering: '||l_proc,10);
1965: hr_utility.set_location('p_assactid : '||p_assactid,20);
1966: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1967: hr_utility.set_location('p_person_id : '||p_person_id,20);
1968: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1969:
1970:
1971: if g_role_src in ('JOB','GRD','POS') then
1972: --fetch main role
1972: --fetch main role
1973: begin
1974: execute immediate g_role_sql_str into l_main_role using g_business_group_id,p_effective_date, p_assignment_id;
1975: exception when others then
1976: hr_utility.set_location('main role could not be fetched as of ' || p_effective_date ||' for Assignment ID :'||p_assignment_id,9999);
1977: end;
1978: elsif g_role_src in ('EMP_CAT','EMPLOYEE_CATG') then -- asg category
1979: open get_assignment_details;
1980: fetch get_assignment_details into l_assignment_number,l_employee_cat,l_asg_cat;
1991: close role_dcsf;
1992: end if;
1993:
1994: if l_main_role is null then
1995: hr_utility.set_location('Main role not fetched for Assignment no :'||l_assignment_number,9999);
1996: end if;
1997:
1998: -- Bulk collect additional roles into p_addl_role
1999: if g_addl_role_src = 'Assignment Developer DF' then
1999: if g_addl_role_src = 'Assignment Developer DF' then
2000: begin
2001: execute immediate g_addl_role_sql_str bulk collect into p_addl_role using p_assignment_id,g_business_group_id;
2002: exception when others then
2003: hr_utility.set_location('Error while fetching Additional Role for Assignment ID :'||p_assignment_id,7777);
2004: end;
2005: elsif g_addl_role_src in('Extra Position Info DDF','Extra Job Info DDF') then
2006: begin
2007: execute immediate g_addl_role_sql_str bulk collect into p_addl_role using p_assignment_id,p_effective_date;
2005: elsif g_addl_role_src in('Extra Position Info DDF','Extra Job Info DDF') then
2006: begin
2007: execute immediate g_addl_role_sql_str bulk collect into p_addl_role using p_assignment_id,p_effective_date;
2008: exception when others then
2009: hr_utility.set_location('Error while fetching Additional Role' || p_effective_date ||'for Assignment ID :'||p_assignment_id,8888);
2010: end;
2011: end if;
2012:
2013: l_role_tab_idx := p_addl_role.count;
2013: l_role_tab_idx := p_addl_role.count;
2014:
2015: p_addl_role(l_role_tab_idx) := l_main_role; -- Append main role with all additional roles
2016:
2017: hr_utility.set_location('Leaving: '||l_proc,999);
2018: return true;
2019: exception when others then
2020: hr_utility.set_location('Leaving with error : '||l_proc,99999);
2021: end fetch_role_details;
2016:
2017: hr_utility.set_location('Leaving: '||l_proc,999);
2018: return true;
2019: exception when others then
2020: hr_utility.set_location('Leaving with error : '||l_proc,99999);
2021: end fetch_role_details;
2022:
2023: function fetch_hours_details(p_assactid in number,
2024: p_assignment_id in number ,
2137: l_check_if_num number;
2138: l_error_flag varchar2(1) := 'N';
2139:
2140: begin
2141: hr_utility.set_location('Entering: '||l_proc,10);
2142: hr_utility.set_location('Parameters : ',20);
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2138: l_error_flag varchar2(1) := 'N';
2139:
2140: begin
2141: hr_utility.set_location('Entering: '||l_proc,10);
2142: hr_utility.set_location('Parameters : ',20);
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2139:
2140: begin
2141: hr_utility.set_location('Entering: '||l_proc,10);
2142: hr_utility.set_location('Parameters : ',20);
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2147: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2140: begin
2141: hr_utility.set_location('Entering: '||l_proc,10);
2142: hr_utility.set_location('Parameters : ',20);
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2147: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2148: hr_utility.set_location('g_data_ret_type : '||g_data_ret_type,20);
2141: hr_utility.set_location('Entering: '||l_proc,10);
2142: hr_utility.set_location('Parameters : ',20);
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2147: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2148: hr_utility.set_location('g_data_ret_type : '||g_data_ret_type,20);
2149:
2142: hr_utility.set_location('Parameters : ',20);
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2147: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2148: hr_utility.set_location('g_data_ret_type : '||g_data_ret_type,20);
2149:
2150:
2143: hr_utility.set_location('p_assactid : '||p_assactid,20);
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2147: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2148: hr_utility.set_location('g_data_ret_type : '||g_data_ret_type,20);
2149:
2150:
2151: open get_hrs_source;
2144: hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145: hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146: hr_utility.set_location('p_person_id : '||p_person_id,20);
2147: hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2148: hr_utility.set_location('g_data_ret_type : '||g_data_ret_type,20);
2149:
2150:
2151: open get_hrs_source;
2152: fetch get_hrs_source into l_hrs_src;
2190:
2191: if l_hrs_details_rec.hrs_per_wk_formula is not null then
2192: begin
2193: l_hours_per_week := run_user_formula(l_hrs_details_rec.hrs_per_wk_formula,p_assignment_id,p_effective_date,g_business_group_id,l_assignment_number);
2194: hr_utility.set_location('Formula Result Value '||l_hours_per_week,20);
2195: exception when others then
2196: populate_run_msg(p_assactid,'Error while executing formula id '|| l_hrs_details_rec.hrs_per_wk_formula||'. Please refer Log file for details' );
2197: fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_hrs_details_rec.hrs_per_wk_formula);
2198: fnd_file.put_line(fnd_file.log,sqlerrm);
2202: if l_hrs_details_rec.wks_per_yr_source = 'FORMULA' then
2203: if l_hrs_details_rec.wks_per_yr_formula is not null then
2204: begin
2205: l_weeks_per_yr := run_user_formula(l_hrs_details_rec.wks_per_yr_formula,p_assignment_id,p_effective_date,g_business_group_id,l_assignment_number);
2206: hr_utility.set_location('Formula Result Value '||l_weeks_per_yr,20);
2207: exception when others then
2208: populate_run_msg(p_assactid,'Error while executing formula id '|| l_hrs_details_rec.wks_per_yr_formula||'. Please refer Log file for details' );
2209: fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_hrs_details_rec.wks_per_yr_formula);
2210: fnd_file.put_line(fnd_file.log,sqlerrm);
2231:
2232: if l_fte_src = 'CAL' and l_fte_formula_id is not null then
2233: begin
2234: l_fte_hrs := run_user_formula(l_fte_formula_id,p_assignment_id,p_effective_date,g_business_group_id,l_assignment_number);
2235: hr_utility.set_location('Formula Result Value '||l_fte_hrs,20);
2236: exception when others then
2237: populate_run_msg(p_assactid,'Error while executing formula id '|| l_fte_formula_id||'. Please refer Log file for details' );
2238: fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_fte_formula_id);
2239: fnd_file.put_line(fnd_file.log,sqlerrm);
2253:
2254:
2255: l_fte_hrs := l_hours_per_week/l_fte_ratio;
2256:
2257: hr_utility.set_location('l_fte_ratio'||l_fte_ratio,20);
2258: hr_utility.set_location('l_hours_per_week'||l_hours_per_week,20);
2259:
2260: if l_error_message is not null then
2261: populate_run_msg(p_assactid,'Error while executing formula GB_CALCULATE_FTE_USING_PQP_CONTRACT_TYPES.'||l_error_message );
2254:
2255: l_fte_hrs := l_hours_per_week/l_fte_ratio;
2256:
2257: hr_utility.set_location('l_fte_ratio'||l_fte_ratio,20);
2258: hr_utility.set_location('l_hours_per_week'||l_hours_per_week,20);
2259:
2260: if l_error_message is not null then
2261: populate_run_msg(p_assactid,'Error while executing formula GB_CALCULATE_FTE_USING_PQP_CONTRACT_TYPES.'||l_error_message );
2262: fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_fte_formula_id);
2264: end if;
2265:
2266: if l_fte_hrs = 0 or l_fte_hrs is null then
2267: l_arch := false;
2268: hr_utility.set_location('FTE Hrs is not defiled or FTE hrs is Zero',20);
2269: populate_run_msg(p_assactid,'FTE Hrs is not defiled or FTE hrs is Zero. This could be if Work Pattern is not defined for the assignment');
2270: end if;
2271:
2272: end if;
2281: begin
2282: l_hours_per_week := to_char(to_number(l_hours_per_week),'fm99.00');
2283: l_check_if_num := l_hours_per_week;
2284: exception when others then
2285: hr_utility.set_location('Hours Per Week has invalid characters',20);
2286: populate_run_msg(p_assactid,'Hours Per Week has invalid characters');
2287: end;
2288:
2289: -- 4740
2296: If l_contract_type is not null and l_hours_per_week is null then
2297:
2298: -- Bug#12599238
2299: l_arch := false;
2300: hr_utility.set_location('Hours worked per week is missing',20);
2301: populate_run_msg(p_assactid,'Hours worked per week is missing');
2302: end if;
2303:
2304: hr_utility.set_location('FTE Hours '|| l_fte_hrs,20);
2300: hr_utility.set_location('Hours worked per week is missing',20);
2301: populate_run_msg(p_assactid,'Hours worked per week is missing');
2302: end if;
2303:
2304: hr_utility.set_location('FTE Hours '|| l_fte_hrs,20);
2305: begin
2306:
2307: -- 4750 /* Added the Code.Bug#12586059 */
2308: l_fte_hrs := to_char(to_number(l_fte_hrs),'fm99.00');
2309: l_check_if_num := l_fte_hrs;
2310: exception when others then
2311: l_error_flag := 'Y';
2312: l_arch := false;
2313: hr_utility.set_location('FTE Hours has invalid characters '|| l_fte_hrs,20);
2314: populate_run_msg(p_assactid,'FTE Hours has invalid characters');
2315: end;
2316:
2317:
2325:
2326: -- Bug#12599238
2327:
2328: l_arch := false;
2329: hr_utility.set_location('FTE Hours per week is missing',20);
2330: populate_run_msg(p_assactid,'FTE Hours per week is missing');
2331: end if;
2332:
2333: -- 4765
2332:
2333: -- 4765
2334: if not(l_fte_hrs between 24 and 40) then
2335: l_arch := false;
2336: hr_utility.set_location('FTE hours should be at least 24 and no greater than 40',20);
2337: populate_run_msg(p_assactid,'FTE hours should be at least 24 and no greater than 40');
2338: end if;
2339:
2340: -- 4770
2342: l_weeks_per_yr := to_char(to_number(l_weeks_per_yr),'fm99');
2343: l_check_if_num := l_weeks_per_yr;
2344: exception when others then
2345: l_arch := false;
2346: hr_utility.set_location('Weeks per year has invalid characters',20);
2347: populate_run_msg(p_assactid,'Weeks per year has invalid characters');
2348: end;
2349:
2350: -- 4780
2355: if l_weeks_per_yr is null then
2356:
2357: -- Bug#12599238
2358: l_arch := false;
2359: hr_utility.set_location('Weeks per year is missing',20);
2360: populate_run_msg(p_assactid,'Weeks per year is missing');
2361: end if;
2362:
2363: if l_error_flag <>'Y' then
2362:
2363: if l_error_flag <>'Y' then
2364: if l_fte_hrs <> 0 and p_contract_type IN ('PRM','FXT','TMP') and l_fte_ratio > 1.5 then
2365: l_arch := false;
2366: hr_utility.set_location('FTE Error',20);
2367: populate_run_msg(p_assactid,'The same person has a total Full Time Equivalent ratio greater than 1.5');
2368: end if;
2369:
2370: if l_fte_hrs <> 0 and p_contract_type IN ('PRM','FXT','TMP') and l_fte_ratio between 1.2 and 1.5 then
2367: populate_run_msg(p_assactid,'The same person has a total Full Time Equivalent ratio greater than 1.5');
2368: end if;
2369:
2370: if l_fte_hrs <> 0 and p_contract_type IN ('PRM','FXT','TMP') and l_fte_ratio between 1.2 and 1.5 then
2371: hr_utility.set_location('FTE Warning',20);
2372: populate_run_msg(p_assactid,'Please Check: The same person has a total Full Time Equivalent ratio greater than 1.2 and less than or equal to 1.5','W');
2373: end if;
2374: end if;
2375: end if ; -- g_data_ret_type <> 'TYPE4'
2388: p_hrs_rec.act_info7 := l_fte_ratio;
2389:
2390: return l_arch;
2391: exception when others then
2392: hr_utility.trace(sqlerrm);
2393: hr_utility.set_location('leaving with error: '||l_proc,7777);
2394: raise;
2395: end fetch_hours_details;
2396:
2389:
2390: return l_arch;
2391: exception when others then
2392: hr_utility.trace(sqlerrm);
2393: hr_utility.set_location('leaving with error: '||l_proc,7777);
2394: raise;
2395: end fetch_hours_details;
2396:
2397: ---------------------------------function get_person_category-------------------
2413:
2414: l_proc constant varchar2(50) := g_package || ' get_person_category';
2415: l_person_category varchar2(30);
2416: begin
2417: hr_utility.set_location('Entering: '||l_proc,10);
2418:
2419: -- Incorporated New logic for person category
2420: if ( (p_end_date-p_start_date) >= 28) then
2421: if (p_post = 'TAS') then
2430: end if;
2431: end if;
2432: end if;
2433:
2434: hr_utility.set_location('Entering: '||l_proc,10);
2435: return l_person_category;
2436: end get_person_category;
2437: ---
2438: ----------------------------function fetch_absence_details----------------------
2465: -- Bug#12586059
2466: l_arch boolean := true;
2467:
2468: begin
2469: hr_utility.set_location('Entering: '||l_proc,10);
2470: open get_abs_source;
2471: fetch get_abs_source into l_abs_source;
2472: close get_abs_source;
2473:
2496: order by paat.date_start asc;
2497:
2498: exception
2499: when others then
2500: hr_utility.trace(sqlerrm);
2501: hr_utility.set_location('leaving with error: '||l_proc,7777);
2502: raise;
2503: end;
2504: elsif l_abs_source = 'ABSENCE_TYPE' then
2497:
2498: exception
2499: when others then
2500: hr_utility.trace(sqlerrm);
2501: hr_utility.set_location('leaving with error: '||l_proc,7777);
2502: raise;
2503: end;
2504: elsif l_abs_source = 'ABSENCE_TYPE' then
2505: begin
2524: order by paat.date_start asc;
2525:
2526: exception
2527: when others then
2528: hr_utility.trace(sqlerrm);
2529: hr_utility.set_location('leaving with error: '||l_proc,7777);
2530: raise;
2531: end;
2532: elsif l_abs_source = 'ABSENCE_REASON' then
2525:
2526: exception
2527: when others then
2528: hr_utility.trace(sqlerrm);
2529: hr_utility.set_location('leaving with error: '||l_proc,7777);
2530: raise;
2531: end;
2532: elsif l_abs_source = 'ABSENCE_REASON' then
2533: begin
2554: order by paat.date_start asc;
2555:
2556: exception
2557: when others then
2558: hr_utility.trace(sqlerrm);
2559: hr_utility.set_location('leaving with error: '||l_proc,7777);
2560: raise;
2561: end;
2562: end if;
2555:
2556: exception
2557: when others then
2558: hr_utility.trace(sqlerrm);
2559: hr_utility.set_location('leaving with error: '||l_proc,7777);
2560: raise;
2561: end;
2562: end if;
2563:
2611:
2612: -- 4920
2613: if l_first_day_of_abs = 'Y' then
2614: l_arch := false;
2615: hr_utility.set_location('First Day of absence must be in the academic year before Census day',20);
2616: populate_run_msg(p_assactid,'First Day of absence must be in the academic year before Census day');
2617: end if;
2618:
2619: -- 4936
2618:
2619: -- 4936
2620: if l_last_day_of_abs = 'Y' then
2621: l_arch := false;
2622: hr_utility.set_location('Last Day of absence must be in the current or preceding academic year',20);
2623: populate_run_msg(p_assactid,'Last Day of absence must be in the current or preceding academic year');
2624: end if;
2625:
2626: --4945Q
2624: end if;
2625:
2626: --4945Q
2627: if l_last_day_of_abs_41_days = 'Y' then
2628: hr_utility.set_location('Please check: Last Day of absence is not expected to be after Census day',20);
2629: populate_run_msg(p_assactid,'Last Day of absence must be in the current or preceding academic year Census day','W');
2630: end if;
2631:
2632: if l_date_end_missing = 'Y' then
2629: populate_run_msg(p_assactid,'Last Day of absence must be in the current or preceding academic year Census day','W');
2630: end if;
2631:
2632: if l_date_end_missing = 'Y' then
2633: hr_utility.set_location('Last Day of absence is missing - please check absence is ongoing',20);
2634: populate_run_msg(p_assactid,'Last Day of absence is missing - please check absence is ongoing','W');
2635: end if;
2636:
2637: if l_last_before_first = 'Y' then
2634: populate_run_msg(p_assactid,'Last Day of absence is missing - please check absence is ongoing','W');
2635: end if;
2636:
2637: if l_last_before_first = 'Y' then
2638: hr_utility.set_location('Last Day of absence cannot be before First Day of absence',20);
2639: populate_run_msg(p_assactid,'Last Day of absence cannot be before First Day of absence','W');
2640: end if;
2641:
2642: -- 4945Q
2640: end if;
2641:
2642: -- 4945Q
2643: if l_last_after_census = 'Y' then
2644: hr_utility.set_location('Please check: Last Day of absence should not be after the Census Day',20);
2645: populate_run_msg(p_assactid,'Please check: Last Day of absence should not be after the Census Day','W');
2646: end if;
2647:
2648: -- 4950Q
2646: end if;
2647:
2648: -- 4950Q
2649: if l_no_of_last_day_missing > 1 then
2650: hr_utility.set_location('Please check - more than one absence record without an end date',20);
2651: populate_run_msg(p_assactid,'Please check - more than one absence record without an end date','W');
2652: end if;
2653:
2654: if l_wrking_days_lost_missing = 'Y' then
2652: end if;
2653:
2654: if l_wrking_days_lost_missing = 'Y' then
2655: l_arch := false;
2656: hr_utility.set_location('Where Last Day of a sickness absence is provided then the number of Working Days Lost must also be provided',20);
2657: populate_run_msg(p_assactid,'Where Last Day of a sickness absence is provided then the number of Working Days Lost must also be provided');
2658: end if;
2659:
2660: -- Bug#12586059
2660: -- Bug#12586059
2661: -- 4960Q
2662: if l_day_of_diff_nodes_overlap = 'Y' then
2663: l_arch := false;
2664: hr_utility.set_location('Please check - more than one absence record with Absence Category of Sickness covering the same date(s)',20);
2665: populate_run_msg(p_assactid,'Please check - more than one absence record with Absence Category of Sickness covering the same date(s)');
2666: end if;
2667: -- Bug#12586059
2668:
2667: -- Bug#12586059
2668:
2669: end if;
2670:
2671: hr_utility.set_location('Leaving: '||l_proc,99);
2672:
2673: return l_arch;
2674: exception when others then
2675: hr_utility.set_location('Leaving with error: '||l_proc,9999);
2671: hr_utility.set_location('Leaving: '||l_proc,99);
2672:
2673: return l_arch;
2674: exception when others then
2675: hr_utility.set_location('Leaving with error: '||l_proc,9999);
2676: raise;
2677: end fetch_absence_details;
2678: ------------------------------function fetch_contract_details--------------------------------------
2679: --Fetches contract details
2818: l_valid_flag varchar2(1);
2819: l_date_of_arrival_dcsf varchar2(10);
2820: l_role_count number;
2821: begin
2822: hr_utility.set_location('Entering '|| l_proc, 10);
2823: hr_utility.set_location('Parameters :', 20);
2824: hr_utility.set_location('p_effective_date :'|| p_effective_date, 20);
2825: hr_utility.set_location('p_person_id :'|| p_person_id, 20);
2826: hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2819: l_date_of_arrival_dcsf varchar2(10);
2820: l_role_count number;
2821: begin
2822: hr_utility.set_location('Entering '|| l_proc, 10);
2823: hr_utility.set_location('Parameters :', 20);
2824: hr_utility.set_location('p_effective_date :'|| p_effective_date, 20);
2825: hr_utility.set_location('p_person_id :'|| p_person_id, 20);
2826: hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2827:
2820: l_role_count number;
2821: begin
2822: hr_utility.set_location('Entering '|| l_proc, 10);
2823: hr_utility.set_location('Parameters :', 20);
2824: hr_utility.set_location('p_effective_date :'|| p_effective_date, 20);
2825: hr_utility.set_location('p_person_id :'|| p_person_id, 20);
2826: hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2827:
2828: if g_pick_from_asg is null then
2821: begin
2822: hr_utility.set_location('Entering '|| l_proc, 10);
2823: hr_utility.set_location('Parameters :', 20);
2824: hr_utility.set_location('p_effective_date :'|| p_effective_date, 20);
2825: hr_utility.set_location('p_person_id :'|| p_person_id, 20);
2826: hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2827:
2828: if g_pick_from_asg is null then
2829: fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proceed');
2822: hr_utility.set_location('Entering '|| l_proc, 10);
2823: hr_utility.set_location('Parameters :', 20);
2824: hr_utility.set_location('p_effective_date :'|| p_effective_date, 20);
2825: hr_utility.set_location('p_person_id :'|| p_person_id, 20);
2826: hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2827:
2828: if g_pick_from_asg is null then
2829: fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proceed');
2830: hr_utility.raise_error;
2826: hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2827:
2828: if g_pick_from_asg is null then
2829: fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proceed');
2830: hr_utility.raise_error;
2831: end if;
2832:
2833: if p_effective_date is null then
2834: fnd_file.put_line(fnd_file.log,'Contract Details - Contract effective date could not be determined.');
2831: end if;
2832:
2833: if p_effective_date is null then
2834: fnd_file.put_line(fnd_file.log,'Contract Details - Contract effective date could not be determined.');
2835: hr_utility.raise_error;
2836: end if;
2837:
2838: open get_date_of_arrival_src;
2839: fetch get_date_of_arrival_src into l_date_of_arrival_src,l_user_formula_name;
2854:
2855: if l_date_of_arrival_src = 'CAL' then
2856: begin
2857: l_date_of_arrival_dcsf := run_user_formula(l_user_formula_name,l_asg_rec.assignment_id,g_census_day,g_business_group_id,l_asg_rec.assignment_number);
2858: hr_utility.set_location('Formula Result Value '||l_date_of_arrival_dcsf,20);
2859: exception when others then
2860: populate_run_msg(p_assactid,'Error while executing formula id '|| l_user_formula_name||'. Please refer Log file for details' );
2861: fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_user_formula_name);
2862: fnd_file.put_line(fnd_file.log,sqlerrm);
2880:
2881: if l_date_of_arrival_src = 'CAL' then
2882: begin
2883: l_date_of_arrival_dcsf :=run_user_formula(l_user_formula_name,l_asg_rec.assignment_id,g_census_day,g_business_group_id,l_asg_rec.assignment_number);
2884: hr_utility.set_location('Formula Result Value '||l_date_of_arrival_dcsf,20);
2885: exception when others then
2886: populate_run_msg(p_assactid,'Error while executing formula '|| l_user_formula_name||'. Please refer Log file for details' );
2887: fnd_file.put_line(fnd_file.log,'Error while executing formula '|| l_user_formula_name);
2888: fnd_file.put_line(fnd_file.log,sqlerrm);
2896: if g_cont_post_src in ('JOB','GRD','POS') then
2897: begin
2898: execute immediate g_cont_post_sql_str into l_post using g_business_group_id,p_effective_date, l_asg_rec.assignment_id;
2899: exception when others then
2900: hr_utility.set_location('Post could not be fetched as of' || p_effective_date ||'for Assignment number :'||l_asg_rec.assignment_number,9999);
2901: end;
2902: elsif g_cont_post_src = 'EMP_CAT' then
2903: open get_post(l_asg_rec.assignment_cat);
2904: fetch get_post into l_post;
2916: if g_origin_dff = 'PER_ASSIGNMENTS' then
2917: begin
2918: execute immediate g_origin_sql_str into l_origin using l_asg_rec.assignment_id, p_effective_date;
2919: exception when others then
2920: hr_utility.set_location('Origin could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,8888);
2921: end;
2922: elsif g_origin_dff = 'PER_PEOPLE' then
2923: begin
2924: execute immediate g_origin_sql_str into l_origin using p_person_id, p_effective_date;
2922: elsif g_origin_dff = 'PER_PEOPLE' then
2923: begin
2924: execute immediate g_origin_sql_str into l_origin using p_person_id, p_effective_date;
2925: exception when others then
2926: hr_utility.set_location('Origin could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,9999);
2927: end;
2928: end if;
2929:
2930: open get_origin(l_origin);
2934: if g_destination_dff = 'PER_ASSIGNMENTS' then
2935: begin
2936: execute immediate g_destination_sql_str into l_destination using l_asg_rec.assignment_id, p_effective_date;
2937: exception when others then
2938: hr_utility.set_location('Destination could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,8888);
2939: end;
2940: elsif upper(g_destination_dff) like 'TERM%' then -- get the exact value
2941: begin
2942: execute immediate g_destination_sql_str into l_destination using p_person_id;
2940: elsif upper(g_destination_dff) like 'TERM%' then -- get the exact value
2941: begin
2942: execute immediate g_destination_sql_str into l_destination using p_person_id;
2943: exception when others then
2944: hr_utility.set_location('Destination could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,9999);
2945: end;
2946: end if;
2947:
2948: open get_destination(l_destination);
2959: l_person_category := get_person_category(l_contract_agg_type,l_asg_dates_rec.contract_st_date,l_asg_dates_rec.contract_end_date,l_post,p_role_tab);
2960:
2961: if l_person_category is null then
2962: l_arch := false;
2963: hr_utility.set_location('Staff Category Could not be determined',10);
2964: populate_run_msg(p_assactid,'Staff Category Could not be determined.This could be becacuse of incorrect Post or Role or Contract Type.');
2965: end if;
2966: -- 4285
2967: if l_establishment is not null then
2975: or l_establishment between 5400 and 5499
2976: or l_establishment between 5900 and 5949
2977: or l_establishment between 6900 and 6924) then
2978: l_arch := false;
2979: hr_utility.set_location('Estab Number in Contract module is not valid',10);
2980: populate_run_msg(p_assactid,'Estab Number in Contract module is not valid');
2981: end if;
2982: end if;
2983:
2995: exception
2996: when OTHERS then
2997: l_arch := false;
2998: populate_run_msg (p_assactid, 'Contract / Agreement Type is invalid');
2999: hr_utility.set_location ('Contract / Agreement Type is invalid', 10);
3000: end;
3001:
3002: if length(l_contract_agg_type) <> 3 then
3003: l_arch := false;
3000: end;
3001:
3002: if length(l_contract_agg_type) <> 3 then
3003: l_arch := false;
3004: hr_utility.set_location('Contract / Agreement Type is invalid',10);
3005: populate_run_msg(p_assactid,'Contract / Agreement Type is invalid');
3006: end if;
3007: end if;
3008:
3008:
3009: -- 4350
3010: if l_asg_dates_rec.contract_st_date is null then
3011: l_arch := false;
3012: hr_utility.set_location('Start Date is missing',10);
3013: populate_run_msg(p_assactid,'Start Date is missing');
3014: end if;
3015:
3016: -- 4355
3015:
3016: -- 4355
3017: if g_census_day < l_asg_dates_rec.contract_st_date then
3018: l_arch := false;
3019: hr_utility.set_location('Contract Start Date can not be in the future',10);
3020: populate_run_msg(p_assactid,'Contract Start Date can not be in the future');
3021: end if;
3022:
3023: -- 4357
3022:
3023: -- 4357
3024: if months_between(trunc(g_census_day),l_asg_dates_rec.contract_st_date)/12 > 50 then
3025: l_arch := false;
3026: hr_utility.set_location('Contract / Agreement Start Date more than 50 years ago',10);
3027: populate_run_msg(p_assactid,'Contract / Agreement Start Date more than 50 years ago');
3028: end if;
3029:
3030: -- 4360
3033: if not(l_asg_dates_rec.contract_st_date between to_date('01-09-'||(g_census_year-1),'DD-MM-YYYY') and to_date('31-08-'||(g_census_year),'DD-MM-YYYY'))
3034: and not(l_asg_dates_rec.contract_end_date between to_date('01-09-'||(g_census_year-1),'DD-MM-YYYY') and to_date('31-08-'||(g_census_year),'DD-MM-YYYY'))
3035: and l_asg_dates_rec.contract_end_date < g_census_day then
3036: l_arch := false;
3037: hr_utility.set_location('Contract end date error',20);
3038: populate_run_msg(p_assactid,'Contract has invalid End date for this Census');
3039: end if;
3040: end if;
3041:
3047: /*if g_census_term <> 'SPRING' then -- Added as PRM and other contract types can exist in the system without end date
3048: if l_contract_agg_type <> 'FXT' and (l_asg_dates_rec.contract_end_date > g_census_day
3049: or months_between(l_asg_dates_rec.contract_end_date ,l_asg_dates_rec.contract_st_date) < 1) then
3050: l_arch := false;
3051: hr_utility.set_location('Contract / Agreement end date must be at least a month after the start date, and on or prior to Census Day, for this type of contract or agreement',20);
3052: populate_run_msg(p_assactid,'Contract / Agreement end date must be at least a month after the start date, and on or prior to Census Day, for this type of contract or agreement');
3053: end if;
3054: end if;*/
3055:
3060:
3061: if l_contract_agg_type <> 'PRM' and l_asg_dates_rec.contract_end_date is null
3062: and (g_census_day - l_asg_dates_rec.contract_st_date < 27) then
3063: l_arch := false;
3064: hr_utility.set_location('Contract Start Date must be at least 27 days prior to the Census Reference Date',20);
3065: populate_run_msg(p_assactid,'Contract Start Date must be at least 27 days prior to the Census Reference Date');
3066: end if;
3067:
3068: -- Bug#12586059
3069:
3070: -- 4380
3071: if l_contract_agg_type = 'FXT' and l_asg_dates_rec.contract_end_date_dcsf is null then
3072: l_arch := false;
3073: hr_utility.set_location('Contract / Agreement Type is Fixed Term therefore End Date must be specified',20);
3074: populate_run_msg(p_assactid,'Contract / Agreement Type is Fixed Term therefore End Date must be specified');
3075: end if;
3076:
3077: -- 4385
3078: if (l_person_category = 1 or l_person_category = 2) and l_asg_dates_rec.contract_end_date_dcsf is not null and l_asg_dates_rec.contract_end_date < g_census_day
3079: and l_dcsf_destination is null then
3080:
3081: l_arch := false;
3082: hr_utility.set_location('Destination code must be provided for completed contracts',20);
3083: populate_run_msg(p_assactid,'Destination code must be provided for completed contracts');
3084: end if;
3085:
3086: -- 4390Q
3085:
3086: -- 4390Q
3087: if l_dcsf_destination is not null and l_asg_dates_rec.contract_end_date_dcsf is null then
3088: l_arch := false; -- Bug 12906657
3089: hr_utility.set_location('Please check: Destination code has been provided therefore contract End Date must be specified ',20);
3090: populate_run_msg(p_assactid,'Please check: Destination code has been provided therefore contract End Date must be specified ','W');
3091: end if;
3092:
3093: -- 4400
3092:
3093: -- 4400
3094: if l_asg_dates_rec.contract_st_date > l_asg_dates_rec.contract_end_date then
3095: l_arch := false;
3096: hr_utility.set_location('Contract End Date cannot be before contract Start Date',20);
3097: populate_run_msg(p_assactid,'Contract End Date cannot be before contract Start Date');
3098: end if;
3099:
3100: -- 4410
3099:
3100: -- 4410
3101: if l_post is null then
3102: l_arch := false;
3103: hr_utility.set_location('Post is missing',20);
3104: populate_run_msg(p_assactid,'Post is missing');
3105: end if;
3106:
3107: -- 4700/4710
3107: -- 4700/4710
3108: l_role_count := 0;
3109:
3110: for i in p_role_tab.first .. p_role_tab.last loop
3111: hr_utility.set_location('Roles for this contract'||p_role_tab(i),25);
3112: if p_role_tab(i) is not null then
3113: l_role_count := l_role_count+1;
3114: end if;
3115: end loop;
3115: end loop;
3116:
3117: if l_role_count = 0 then
3118: l_arch := false;
3119: hr_utility.set_location('Role Identifier is missing ',20);
3120: populate_run_msg(p_assactid,'No Role details have been supplied');
3121: end if;
3122:
3123:
3134: end if;
3135: end loop;
3136: if l_valid_flag = 'N' then
3137: l_arch := false;
3138: hr_utility.set_location('Post is inconsistent with Role Identifier',20);
3139: populate_run_msg(p_assactid,'Post is inconsistent with Role Identifier');
3140: end if;
3141: end if;
3142:
3158: end loop;
3159: if l_valid_flag = 'N' then
3160: l_arch := false;
3161: -- Bug#12599238
3162: -- hr_utility.set_location('Role of Teaching Assistant or Higher Level Teaching Assistant should not be linked to the post of Support Staff',20);
3163: -- populate_run_msg(p_assactid,'Role of Teaching Assistant or Higher Level Teaching Assistant should not be linked to the post of Support Staff');
3164: hr_utility.set_location('Role of Teaching Assistant or Higher Level Teaching Assistant should be linked to the post of Teaching Assistant',20);
3165: populate_run_msg(p_assactid,'Role of Teaching Assistant or Higher Level Teaching Assistant should be linked to the post of Teaching Assistant');
3166: -- Bug#12599238
3160: l_arch := false;
3161: -- Bug#12599238
3162: -- hr_utility.set_location('Role of Teaching Assistant or Higher Level Teaching Assistant should not be linked to the post of Support Staff',20);
3163: -- populate_run_msg(p_assactid,'Role of Teaching Assistant or Higher Level Teaching Assistant should not be linked to the post of Support Staff');
3164: hr_utility.set_location('Role of Teaching Assistant or Higher Level Teaching Assistant should be linked to the post of Teaching Assistant',20);
3165: populate_run_msg(p_assactid,'Role of Teaching Assistant or Higher Level Teaching Assistant should be linked to the post of Teaching Assistant');
3166: -- Bug#12599238
3167: end if;
3168: end if;
3179: end if;
3180: end loop;
3181: if l_valid_flag = 'N' then
3182: l_arch := false;
3183: hr_utility.set_location('Post shown as Head Teacher. One of the associated Roles must also be Head Teacher',20);
3184: populate_run_msg(p_assactid,'Post shown as Head Teacher. One of the associated Roles must also be Head Teacher');
3185: end if;
3186: end if;
3187:
3195: end if;
3196: end loop;
3197: if l_valid_flag = 'N' then
3198: l_arch := false;
3199: hr_utility.set_location('Post shown as Deputy Head. One of the associated Roles must also be Deputy Head',20);
3200: populate_run_msg(p_assactid,'Post shown as Deputy Head. One of the associated Roles must also be Deputy Head');
3201: end if;
3202: end if;
3203:
3211: end if;
3212: end loop;
3213: if l_valid_flag = 'N' then
3214: l_arch := false;
3215: hr_utility.set_location('Post shown as Deputy Head. One of the associated Roles must also be Deputy Head',20);
3216: populate_run_msg(p_assactid,'Post shown as Deputy Head. One of the associated Roles must also be Deputy Head');
3217: end if;
3218: end if;
3219:
3227: end if;
3228: end loop;
3229: if l_valid_flag = 'Y' and p_hlta_status = 'No' then
3230: l_arch := false;
3231: hr_utility.set_location('If role is HLTA then HLTA Status must be Yes',20);
3232: populate_run_msg(p_assactid,'If role is HLTA then HLTA Status must be Yes');
3233: end if;
3234: end;
3235:
3240: end if;
3241:
3242: -- 4425Q
3243: if months_between(trunc(g_census_day),l_date_of_arrival)/ 12 > 50 then
3244: hr_utility.set_location('Please check: Date of Arrival in School is more than 50 years ago',20);
3245: populate_run_msg(p_assactid,'Please check: Date of Arrival in School is more than 50 years ago','W');
3246: end if;
3247:
3248:
3248:
3249: -- 4430
3250: if l_person_category in (1,2,3) and l_la_or_school_level = 'S' and l_date_of_arrival_dcsf is null then
3251: l_arch := false;
3252: hr_utility.set_location('Date of Arrival in School must be supplied',20);
3253: populate_run_msg(p_assactid,'Date of Arrival in School must be supplied');
3254: end if;
3255:
3256: -- 4440Q
3254: end if;
3255:
3256: -- 4440Q
3257: if l_person_category in (1,2,3) and l_date_of_arrival > l_asg_dates_rec.contract_st_date then
3258: hr_utility.set_location('Please check: Date of Arrival in School should not be later than the start of the contract ',20);
3259: populate_run_msg(p_assactid,'Please check: Date of Arrival in School should not be later than the start of the contract','W');
3260: end if;
3261:
3262: -- 4570
3262: -- 4570
3263: if l_daily_rate is not null
3264: and (length(l_daily_rate) <> 1 or l_daily_rate not in ('Y','N')) then
3265: l_arch := false;
3266: hr_utility.set_location('Daily Rate is invalid',20);
3267: populate_run_msg(p_assactid,'Daily Rate is invalid');
3268: end if;
3269:
3270: -- Bug 12906657
3272: -- For other Staff categories it is invalid and should throw an error message
3273:
3274: if l_person_category in (1,3,4) and l_daily_rate = 'Y' then
3275: l_arch := false;
3276: hr_utility.set_location('Daily Rate indicator of Y is only valid for Agency/SA Teacher. Please correct the data and resubmit for person_id:'|| p_person_id ,20);
3277: populate_run_msg(p_assactid,'Daily Rate indicator of Y is only valid for Agency/SA Teacher. Please correct the data and resubmit');
3278: end if;
3279: -- Bug 12906657
3280:
3294: when others
3295: then
3296: l_arch := false;
3297: populate_run_msg (p_assactid, 'Destination code is invalid');
3298: hr_utility.set_location ('Destination code is invalid', 10);
3299: end;
3300:
3301: if length(l_dcsf_destination) <> 6 then
3302: l_arch := false;
3300:
3301: if length(l_dcsf_destination) <> 6 then
3302: l_arch := false;
3303: populate_run_msg (p_assactid, 'Destination code is invalid');
3304: hr_utility.set_location ('Destination code is invalid', 20);
3305: end if;
3306: end if;
3307:
3308: -- 4600
3321: when others
3322: then
3323: l_arch := false;
3324: populate_run_msg (p_assactid, 'Origin code is invalid');
3325: hr_utility.set_location ('Origin code is invalid', 10);
3326: end;
3327:
3328: if length(l_dcsf_origin) <> 6 then
3329: l_arch := false;
3327:
3328: if length(l_dcsf_origin) <> 6 then
3329: l_arch := false;
3330: populate_run_msg (p_assactid, 'Origin code is invalid');
3331: hr_utility.set_location ('Origin code is invalid', 20);
3332: end if;
3333: end if;
3334:
3335: -- 4610Q
3334:
3335: -- 4610Q
3336: if l_person_category in (1,3) and l_asg_dates_rec.contract_st_date > to_date('31-08-2009','DD-MM-YYYY') and l_dcsf_origin is null then
3337: populate_run_msg (p_assactid, 'Please check: Origin should be provided','W');
3338: hr_utility.set_location ('Please check: Origin should be provided', 20);
3339: end if;
3340:
3341: -- Additional Validations
3342:
3342:
3343: if l_contract_agg_type is null then
3344: l_arch := false;
3345: populate_run_msg (p_assactid, 'Contract Aggreement type can not be null. This error could have occured if the DCSF mapping is incorrect or missing');
3346: hr_utility.set_location ('Contract Aggreement type can not be null. This error could have occured if the DCSF mapping is incorrect or missing', 20);
3347: end if;
3348: p_contract_rec.action_info_category := 'GB_SWF_CONTRACT_DETAILS';
3349: p_contract_rec.person_id := p_person_id;
3350: p_contract_rec.assignment_id := l_asg_rec.assignment_id;
3369: p_contract_rec.act_info13 := p_effective_date; -- staff effective_date
3370: -- passed as parameter to fetch_payment_details
3371: p_contract_rec.act_info14 := l_person_category;
3372: --
3373: hr_utility.set_location('Leaving '|| l_proc, 99);
3374: return l_arch;
3375: exception when others then
3376: hr_utility.set_location(sqlerrm, 999);
3377: hr_utility.set_location('Leaving with error'|| l_proc, 999);
3372: --
3373: hr_utility.set_location('Leaving '|| l_proc, 99);
3374: return l_arch;
3375: exception when others then
3376: hr_utility.set_location(sqlerrm, 999);
3377: hr_utility.set_location('Leaving with error'|| l_proc, 999);
3378: end fetch_contract_details;
3379:
3380: -----------------------function fetch_qualification_details---------------------
3373: hr_utility.set_location('Leaving '|| l_proc, 99);
3374: return l_arch;
3375: exception when others then
3376: hr_utility.set_location(sqlerrm, 999);
3377: hr_utility.set_location('Leaving with error'|| l_proc, 999);
3378: end fetch_contract_details;
3379:
3380: -----------------------function fetch_qualification_details---------------------
3381: function fetch_qualification_details (p_assactid in number,
3446: l_missing_verified varchar2(1) := 'N';
3447: l_same_sub_1_2 varchar2(1) := 'N';
3448: l_arch boolean := true;
3449: begin
3450: hr_utility.set_location('Entering :'||l_proc,10);
3451: hr_utility.set_location('Parameters :',20);
3452: hr_utility.set_location('p_assactid :'||p_assactid,20);
3453: hr_utility.set_location('p_person_id :'||p_person_id,20);
3454:
3447: l_same_sub_1_2 varchar2(1) := 'N';
3448: l_arch boolean := true;
3449: begin
3450: hr_utility.set_location('Entering :'||l_proc,10);
3451: hr_utility.set_location('Parameters :',20);
3452: hr_utility.set_location('p_assactid :'||p_assactid,20);
3453: hr_utility.set_location('p_person_id :'||p_person_id,20);
3454:
3455: open get_qual_code_src;
3448: l_arch boolean := true;
3449: begin
3450: hr_utility.set_location('Entering :'||l_proc,10);
3451: hr_utility.set_location('Parameters :',20);
3452: hr_utility.set_location('p_assactid :'||p_assactid,20);
3453: hr_utility.set_location('p_person_id :'||p_person_id,20);
3454:
3455: open get_qual_code_src;
3456: fetch get_qual_code_src into l_qual_code_src;
3449: begin
3450: hr_utility.set_location('Entering :'||l_proc,10);
3451: hr_utility.set_location('Parameters :',20);
3452: hr_utility.set_location('p_assactid :'||p_assactid,20);
3453: hr_utility.set_location('p_person_id :'||p_person_id,20);
3454:
3455: open get_qual_code_src;
3456: fetch get_qual_code_src into l_qual_code_src;
3457: close get_qual_code_src;
3528:
3529: if l_invalid_qual_code = 'Y' then
3530: l_arch := false;
3531: populate_run_msg (p_assactid, 'Qualification Code is invalid');
3532: hr_utility.set_location ('Qualification Code is invalid', 20);
3533: end if;
3534:
3535:
3536: if l_missing_qual_code = 'Y' then
3535:
3536: if l_missing_qual_code = 'Y' then
3537: l_arch := false;
3538: populate_run_msg (p_assactid, 'Qualification Code is missing');
3539: hr_utility.set_location ('Qualification Code is missing', 20);
3540: end if;
3541:
3542: if l_sub_code_1_invalid = 'Y' then
3543: l_arch := false;
3541:
3542: if l_sub_code_1_invalid = 'Y' then
3543: l_arch := false;
3544: populate_run_msg (p_assactid, 'Subject Code 1 is invalid');
3545: hr_utility.set_location ('Subject Code 1 is invalid', 20);
3546: end if;
3547:
3548: if l_sub_code_2_invalid = 'Y' then
3549: l_arch := false;
3547:
3548: if l_sub_code_2_invalid = 'Y' then
3549: l_arch := false;
3550: populate_run_msg (p_assactid, 'Second qualification Subject Code 2 is invalid');
3551: hr_utility.set_location ('Second qualification Subject Code 2 is invalid', 20);
3552: end if;
3553:
3554: if l_sub1_missing = 'Y' then
3555: l_arch := false;
3553:
3554: if l_sub1_missing = 'Y' then
3555: l_arch := false;
3556: populate_run_msg (p_assactid, 'Subject Code 1 is missing');
3557: hr_utility.set_location ('Subject Code 1 is missing', 20);
3558: end if;
3559:
3560: if l_same_sub_1_2 = 'Y' then
3561: l_arch := false;
3559:
3560: if l_same_sub_1_2 = 'Y' then
3561: l_arch := false;
3562: populate_run_msg (p_assactid, 'Qualification Subject Code 1 and Subject Code 2 cannot be the same');
3563: hr_utility.set_location ('Qualification Subject Code 1 and Subject Code 2 cannot be the same', 20);
3564: end if;
3565:
3566: -- Commented out for Bug# 10106993
3567: /*
3567: /*
3568: if l_missing_verified = 'Y' then
3569: l_arch := false;
3570: populate_run_msg (p_assactid, 'Qualification Verified is missing');
3571: hr_utility.set_location ('Qualification Verified is missing', 20);
3572: end if;
3573: */
3574: -- Commented out for Bug# 10106993
3575:
3572: end if;
3573: */
3574: -- Commented out for Bug# 10106993
3575:
3576: hr_utility.set_location('Leaving :'||l_proc,99);
3577: return l_arch;
3578: exception when others then
3579: hr_utility.set_location(sqlerrm,999);
3580: hr_utility.set_location('Leaving with error:'||l_proc,999);
3575:
3576: hr_utility.set_location('Leaving :'||l_proc,99);
3577: return l_arch;
3578: exception when others then
3579: hr_utility.set_location(sqlerrm,999);
3580: hr_utility.set_location('Leaving with error:'||l_proc,999);
3581: raise;
3582: end fetch_qualification_details;
3583:
3576: hr_utility.set_location('Leaving :'||l_proc,99);
3577: return l_arch;
3578: exception when others then
3579: hr_utility.set_location(sqlerrm,999);
3580: hr_utility.set_location('Leaving with error:'||l_proc,999);
3581: raise;
3582: end fetch_qualification_details;
3583:
3584: ------------------------------function fetch_payment_details--------------------
3683:
3684:
3685:
3686: begin
3687: hr_utility.set_location('Entering :'||l_proc,100);
3688:
3689: hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3685:
3686: begin
3687: hr_utility.set_location('Entering :'||l_proc,100);
3688:
3689: hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3686: begin
3687: hr_utility.set_location('Entering :'||l_proc,100);
3688:
3689: hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3687: hr_utility.set_location('Entering :'||l_proc,100);
3688:
3689: hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3688:
3689: hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696: hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3689: hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696: hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3697: hr_utility.set_location('p_daily_rate :' ||p_daily_rate,110);
3690: hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696: hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3697: hr_utility.set_location('p_daily_rate :' ||p_daily_rate,110);
3698:
3691: hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696: hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3697: hr_utility.set_location('p_daily_rate :' ||p_daily_rate,110);
3698:
3699: open pay_scale;
3692: hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696: hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3697: hr_utility.set_location('p_daily_rate :' ||p_daily_rate,110);
3698:
3699: open pay_scale;
3700: fetch pay_scale into l_pay_scale;
3693: hr_utility.set_location('p_post :' ||p_post,110);
3694: hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695: hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696: hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3697: hr_utility.set_location('p_daily_rate :' ||p_daily_rate,110);
3698:
3699: open pay_scale;
3700: fetch pay_scale into l_pay_scale;
3701: close pay_scale;
3741:
3742: begin
3743: execute immediate l_regional_spine_sql_str into l_reg_spinal_point_dcsf using g_business_group_id,p_effective_date, p_assignment_id;
3744: exception when others then
3745: hr_utility.set_location('Spinal could not be fetched as of' || p_effective_date ||'for Assignment ID :'||p_assignment_id,9999);
3746: end;
3747:
3748: elsif l_regional_spine_context = 'PAYSCALE_SPINEPOINT' then
3749: open reg_spinal_point_dcsf(l_pay_scale,l_spinal_point_id);
3759:
3760: -- 4460
3761: if length(l_pay_scale_dcsf) <> 2 then
3762: l_arch := false;
3763: hr_utility.set_location('Pay Scale is invalid',10);
3764: populate_run_msg(p_assactid,'Pay Scale is invalid');
3765: end if;
3766:
3767: -- 4470
3766:
3767: -- 4470
3768: if l_pay_scale_dcsf in('LD', 'TE', 'TU', 'EX' ,'AS')and p_qt_status <> 'Yes' then
3769: l_arch := false;
3770: hr_utility.set_location('Pay Scale type inconsistent with Qualified Teacher Status',10);
3771: populate_run_msg(p_assactid,'Pay Scale type inconsistent with Qualified Teacher Status');
3772: end if;
3773:
3774: -- 4480
3773:
3774: -- 4480
3775: if p_post = 'SUP' and l_pay_scale_dcsf in('LD', 'TE', 'TU', 'EX' ,'AS') then
3776: l_arch := false;
3777: hr_utility.set_location('Pay Scale is invalid for the given Post',10);
3778: populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3779: end if;
3780:
3781: -- 4490
3781: -- 4490
3782: if p_person_category in (1,2) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3783: and (p_daily_rate = 'N' or p_daily_rate is null) and p_post in('HDT','DHT','AHT') and l_pay_scale_dcsf not in ('LD', 'OT') then /*Bug#125860598 */
3784: l_arch := false;
3785: hr_utility.set_location('Pay Scale is invalid for the given Post',20);
3786: populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3787: end if;
3788:
3789: -- 4500
3789: -- 4500
3790: if p_person_category in (1,2) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3791: and (p_daily_rate = 'N' or p_daily_rate is null )and p_post = 'AST' and l_pay_scale_dcsf not in ('AS', 'OT')then /*Bug#125860598 */
3792: l_arch := false;
3793: hr_utility.set_location('Pay Scale is invalid for the given Post',30);
3794: populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3795: end if;
3796:
3797: -- 4505
3797: -- 4505
3798: if p_person_category in (1,2) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3799: and (p_daily_rate = 'N' or p_daily_rate is null ) and p_post = 'EXL' and l_pay_scale_dcsf not in ('EX', 'OT')then /*Bug#125860598 */
3800: l_arch := false;
3801: hr_utility.set_location('Pay Scale is invalid for the given Post',40);
3802: populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3803: end if;
3804:
3805: -- 4510
3818: exception
3819: when others
3820: then
3821: l_arch := false;
3822: hr_utility.set_location('Regional Pay Spine is invalid',10);
3823: populate_run_msg(p_assactid,'Regional Pay Spine is invalid');
3824: end;
3825:
3826: if length(l_reg_spinal_point_dcsf) <> 2 then
3824: end;
3825:
3826: if length(l_reg_spinal_point_dcsf) <> 2 then
3827: l_arch := false;
3828: hr_utility.set_location('Regional Pay Spine is invalid',20);
3829: populate_run_msg(p_assactid,'Regional Pay Spine is invalid');
3830: end if;
3831: end if;
3832:
3845: exception
3846: when others
3847: then
3848: l_arch := false;
3849: hr_utility.set_location('Spine Point is invalid',10);
3850: populate_run_msg(p_assactid,'Spine Point is invalid');
3851: end;
3852:
3853: if length(l_spinal_point_dcsf) not between 1 and 6 then
3851: end;
3852:
3853: if length(l_spinal_point_dcsf) not between 1 and 6 then
3854: l_arch := false;
3855: hr_utility.set_location('Spine Point is invalid',20);
3856: populate_run_msg(p_assactid,'Spine Point is invalid');
3857: end if;
3858: end if;
3859:
3861: -- 4540
3862: if p_person_category in (2,3) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3863: and p_daily_rate = 'N' and l_salary_rate is null then
3864: l_arch := false;
3865: hr_utility.set_location('Salary Rate must be provided where Daily Rate is false',10);
3866: populate_run_msg(p_assactid,'Salary Rate must be provided where Daily Rate is false');
3867: end if;
3868:
3869: -- 4550
3869: -- 4550
3870: if p_person_category in (2,3) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3871: and p_daily_rate = 'Y' and l_salary_rate is not null then
3872: l_arch := false;
3873: hr_utility.set_location('Salary Rate must not be provided where Daily Rate is true',10);
3874: populate_run_msg(p_assactid,'Salary Rate must not be provided where Daily Rate is true');
3875: end if;
3876:
3877: p_payment_rec.action_info_category := 'GB_SWF_PAYMENT_DETAILS';
3884: p_payment_rec.act_info4 := l_salary_rate;
3885: p_payment_rec.act_info5 := l_safe_grd_sal;
3886:
3887:
3888: hr_utility.set_location('Leaving :'||l_proc,999);
3889:
3890: return l_arch;
3891: exception when others then
3892: hr_utility.set_location('Leaving with error:'||l_proc,9999);
3888: hr_utility.set_location('Leaving :'||l_proc,999);
3889:
3890: return l_arch;
3891: exception when others then
3892: hr_utility.set_location('Leaving with error:'||l_proc,9999);
3893: raise;
3894: end fetch_payment_details;
3895:
3896: ----------------------procedure archinit----------------------------------------
3907:
3908: param_details_rec param_details%rowtype;
3909:
3910: begin
3911: hr_utility.set_location('Entering '|| l_proc, 10);
3912: open param_details;
3913: fetch param_details into param_details_rec;
3914: close param_details;
3915:
3927: fnd_file.put_line(fnd_file.output,' ');
3928: end if;
3929:
3930:
3931: hr_utility.set_location('Leaving '|| l_proc, 110);
3932: exception
3933: when others then
3934: hr_utility.set_location('Leaving '|| l_proc, 999);
3935: hr_utility.set_location(sqlerrm,9999);
3930:
3931: hr_utility.set_location('Leaving '|| l_proc, 110);
3932: exception
3933: when others then
3934: hr_utility.set_location('Leaving '|| l_proc, 999);
3935: hr_utility.set_location(sqlerrm,9999);
3936: hr_utility.raise_error;
3937: end archinit;
3938:
3931: hr_utility.set_location('Leaving '|| l_proc, 110);
3932: exception
3933: when others then
3934: hr_utility.set_location('Leaving '|| l_proc, 999);
3935: hr_utility.set_location(sqlerrm,9999);
3936: hr_utility.raise_error;
3937: end archinit;
3938:
3939: ---------------------------procedure archive_code-------------------------------
3932: exception
3933: when others then
3934: hr_utility.set_location('Leaving '|| l_proc, 999);
3935: hr_utility.set_location(sqlerrm,9999);
3936: hr_utility.raise_error;
3937: end archinit;
3938:
3939: ---------------------------procedure archive_code-------------------------------
3940: procedure archive_code(p_assactid in number,
4015: where pcv.pcv_information_category = 'PQP_GB_SWF_FTE_HOURS'
4016: and pcv.business_group_id = g_business_group_id;
4017:
4018: begin
4019: hr_utility.set_location('Entering: '||l_proc,10);
4020: -----
4021: open csr_parameter_info;
4022: fetch csr_parameter_info into g_census_year,
4023: g_census_day,
4050: -- in the archive procedures.this has been wrapped into a procedure to keep the
4051: -- dynamic sql logic separate from the archive code.
4052: dyn_sql;
4053: ------------------------------------------------------------------------------
4054: hr_utility.set_location('Archiving Starts for assignment_action_id :'||p_assactid ,20);
4055: -- archive starts
4056: -- l_archive_tab table will be populated with all the archive records and
4057: -- finally will be passed to the archive api. the first record will be put in
4058: -- 0th index and any new records that are to be populated are to be populated
4060: if g_data_ret_type <> 'TYPE4' then
4061:
4062: l_archive_person := fetch_staff_details(p_assactid,p_effective_date,l_archive_tab(0));
4063:
4064: hr_utility.set_location('Archiving Staff Details Complete: '||l_proc,100);
4065:
4066: l_archive_contract := fetch_contract_details(p_assactid,l_archive_tab(0).act_info16,l_archive_tab(0).person_id,l_archive_tab(0).act_info13,l_archive_tab(0).act_info2,l_archive_tab(1),l_role_tab);
4067:
4068: hr_utility.set_location('Archiving Contract Details Complete: '||l_proc,200);
4064: hr_utility.set_location('Archiving Staff Details Complete: '||l_proc,100);
4065:
4066: l_archive_contract := fetch_contract_details(p_assactid,l_archive_tab(0).act_info16,l_archive_tab(0).person_id,l_archive_tab(0).act_info13,l_archive_tab(0).act_info2,l_archive_tab(1),l_role_tab);
4067:
4068: hr_utility.set_location('Archiving Contract Details Complete: '||l_proc,200);
4069:
4070: l_archive_payment := fetch_payment_details(p_assactid,l_archive_tab(0).act_info16,l_archive_tab(0).person_id,l_archive_tab(1).assignment_id,
4071: l_archive_tab(1).act_info5,l_archive_tab(0).act_info12,l_archive_tab(1).act_info14, l_archive_tab(1).act_info3,l_archive_tab(1).act_info4,l_archive_tab(1).act_info7,l_archive_tab(2));
4072:
4069:
4070: l_archive_payment := fetch_payment_details(p_assactid,l_archive_tab(0).act_info16,l_archive_tab(0).person_id,l_archive_tab(1).assignment_id,
4071: l_archive_tab(1).act_info5,l_archive_tab(0).act_info12,l_archive_tab(1).act_info14, l_archive_tab(1).act_info3,l_archive_tab(1).act_info4,l_archive_tab(1).act_info7,l_archive_tab(2));
4072:
4073: hr_utility.set_location('Archiving Payment Details Complete: '||l_proc,300);
4074:
4075: l_archive_tab_index := l_archive_tab.count;
4076:
4077:
4084:
4085: l_archive_tab_index := l_archive_tab_index +1;
4086: end loop;
4087:
4088: hr_utility.set_location('Archiving Role Details Complete: '||l_proc,400);
4089:
4090:
4091: if check_max_action(p_assactid,l_archive_tab(0).person_id,l_pactid) then
4092:
4111: end loop;
4112: end if;
4113: end if;
4114:
4115: hr_utility.set_location('Archiving Absence Complete: '||l_proc,500);
4116:
4117: if g_exclude_qual = 'No' then
4118: l_archive_qual := fetch_qualification_details(p_assactid,l_archive_tab(0).person_id,l_archive_tab(0).act_info2,l_qual_tab);
4119: l_archive_tab_index := l_archive_tab.count;
4134: end loop;
4135: end if;
4136: end if;
4137:
4138: hr_utility.set_location('Archiving Qualification Complete: '||l_proc,600);
4139: end if; -- end check_max_action
4140:
4141:
4142: l_archive_addl_payment := fetch_addl_payment_details(p_assactid,l_archive_tab(1).assignment_id,l_archive_tab(0).act_info16,p_addl_payment_tab);
4154: end loop;
4155: end if;
4156:
4157:
4158: hr_utility.set_location('Archiving Addl Payments Complete: '||l_proc,700);
4159:
4160: l_archive_tab_index:= l_archive_tab.count;
4161:
4162: l_archive_hrs := fetch_hours_details(p_assactid,l_archive_tab(1).assignment_id,l_archive_tab(0).act_info16,
4161:
4162: l_archive_hrs := fetch_hours_details(p_assactid,l_archive_tab(1).assignment_id,l_archive_tab(0).act_info16,
4163: l_archive_tab(1).act_info14,l_archive_tab(0).person_id,l_archive_tab(1).act_info2,l_archive_tab(1).act_info4,l_archive_tab(l_archive_tab_index));
4164:
4165: hr_utility.set_location('Archiving Hours Complete: '||l_proc,800);
4166:
4167: if l_archive_person and l_archive_contract and l_archive_payment and l_archive_abs and
4168: l_archive_qual and l_archive_addl_payment and l_archive_hrs then
4169:
4180:
4181: if l_assignment_id is not null then
4182:
4183: l_arch_role := fetch_role_details(p_assactid,g_census_day,null,l_assignment_id,l_role_tab);
4184: hr_utility.set_location('Test 0: '||l_proc,800);
4185:
4186: for i in l_role_tab.first .. l_role_tab.last loop
4187: if l_role_tab(i) = 'EPSY' then
4188: l_epsy := 'Y';
4188: l_epsy := 'Y';
4189: end if;
4190: end loop;
4191:
4192: hr_utility.set_location('Test 1: '||l_proc,800);
4193: if l_epsy = 'Y' then
4194: -- If the value for l_employment_category is seeded
4195: l_temp_or_perm := pqp_gb_t1_pension_extracts.get_translate_asg_emp_cat_code
4196: (l_employment_category
4204: ,g_census_day
4205: ,'Pension Extracts Employment Category Code'
4206: ,g_business_group_id
4207: ) ;
4208: hr_utility.set_location('Test 2: '||l_proc,800);
4209:
4210: l_archive_hrs := fetch_hours_details(p_assactid,l_assignment_id,g_census_day,4,null,l_contract_type, null,l_archive_tab(0) );
4211: l_archive_tab_index:= l_archive_tab.count;
4212: hr_utility.set_location('Test 3: '||l_proc,800);
4208: hr_utility.set_location('Test 2: '||l_proc,800);
4209:
4210: l_archive_hrs := fetch_hours_details(p_assactid,l_assignment_id,g_census_day,4,null,l_contract_type, null,l_archive_tab(0) );
4211: l_archive_tab_index:= l_archive_tab.count;
4212: hr_utility.set_location('Test 3: '||l_proc,800);
4213: for i in l_role_tab.first .. l_role_tab.last loop
4214: l_archive_tab(l_archive_tab_index).action_info_category := 'GB_SWF_ROLE_DETAILS';
4215: l_archive_tab(l_archive_tab_index).person_id := l_archive_tab(1).person_id;
4216: l_archive_tab(l_archive_tab_index).assignment_id := l_archive_tab(1).assignment_id;
4220:
4221: l_archive_tab_index := l_archive_tab_index +1;
4222: end loop;
4223:
4224: hr_utility.set_location('Test 4: '||l_proc,800);
4225: insert_archive_row(p_assactid, g_census_day, l_archive_tab);
4226: end if;
4227: end if;
4228: end if;
4227: end if;
4228: end if;
4229:
4230:
4231: hr_utility.set_location('leaving: '||l_proc,999);
4232:
4233: exception
4234: when others then
4235: hr_utility.trace(sqlerrm);
4231: hr_utility.set_location('leaving: '||l_proc,999);
4232:
4233: exception
4234: when others then
4235: hr_utility.trace(sqlerrm);
4236: hr_utility.set_location('Error in Archive: '||l_proc,999);
4237: raise;
4238: end archive_code;
4239:
4232:
4233: exception
4234: when others then
4235: hr_utility.trace(sqlerrm);
4236: hr_utility.set_location('Error in Archive: '||l_proc,999);
4237: raise;
4238: end archive_code;
4239:
4240: --------------------------- procedure deinit_code------------------------------
4420: and pai.action_information2 = 'P';
4421:
4422:
4423: begin
4424: hr_utility.set_location('Entering: '||l_proc,10);
4425: open param_details;
4426: fetch param_details into param_details_rec;
4427: close param_details;
4428:
4543:
4544:
4545: end if;
4546:
4547: hr_utility.set_location('leaving: '||l_proc,999);
4548: exception
4549: when others then
4550: hr_utility.set_location('Error in deinit: '||sqlerrm||l_proc,999);
4551: end deinit_code;
4546:
4547: hr_utility.set_location('leaving: '||l_proc,999);
4548: exception
4549: when others then
4550: hr_utility.set_location('Error in deinit: '||sqlerrm||l_proc,999);
4551: end deinit_code;
4552:
4553: end pqp_gb_swf_archive;