[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_archive_row(p_assactid in number,
p_effective_date in date,
p_tab_rec_data in action_info_table) is
l_proc constant varchar2(50):= g_package||'insert_archive_row';
end insert_archive_row;
select pcv_information1 ,
pcv_information3 ,
pcv_information4
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_TEACHER_NUM'
and pcv.business_group_id = g_business_group_id;
select pcv_information1
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_QTS_SRC'
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information2
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_QTS_ROUTE_SRC'
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information4,
decode(pcv_information1,'JOB',pcv_information3, pcv_information5)
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_HLTA_STATUS_SRC'
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_POST_SOURCE'
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information2,
pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_ORIGIN_SRC'
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information2,
pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_DESTINATION_SRC' -- Check in database
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_ROLE_SOURCE'
and pcv.business_group_id = g_business_group_id;
select pcv_information1,
pcv_information2,
pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_ADD_ROLE_SOURCE'
and pcv.business_group_id = g_business_group_id;
'select '||l_column||
' from per_all_people_f where ATTRIBUTE_CATEGORY = '''||l_context||'''
and person_id = :person_id
and :effective_date between effective_start_date and effective_end_date';
g_teacher_sql_str := 'select max('||l_column||')'||
' from per_people_extra_info where information_type = '''||l_context||'''
and person_id = :person_id
and '||l_column ||' is not null ';
'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information4))
from per_all_assignments_f paf,
per_grades pgr,
per_grade_definitions pgd ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pgr.business_group_id
and pcv.business_group_id = paf.business_group_id
and pgr.grade_definition_id = pgd.grade_definition_id
and paf.grade_id = pgr.grade_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and person_id = :person_id
and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_MAPPING''
and ((pcv_information3 is null
and pgd.'||l_qts_grade_seg_name||' = pcv.pcv_information2 )
or (pcv_information3 is not null
and pgd.'||l_qts_grade_seg_name||' between pcv.pcv_information2 and pcv_information3))';
'select max(pcv.pcv_information4)
from per_all_assignments_f paf,
per_grades pgr,
per_grade_definitions pgd ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pgr.business_group_id
and pcv.business_group_id = paf.business_group_id
and pgr.grade_definition_id = pgd.grade_definition_id
and paf.grade_id = pgr.grade_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and person_id = :person_id
and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_ROUTE_MAPPING''
and ((pcv_information3 is null
and pgd.'||l_qts_route_seg_name||' = pcv.pcv_information2 )
or (pcv_information3 is not null
and pgd.'||l_qts_route_seg_name||' between pcv.pcv_information2 and pcv_information3))';
'select max(pcv.pcv_information4) QT_status
from per_all_assignments_f paf,
per_jobs job,
per_job_definitions jobdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = job.business_group_id
and pcv.business_group_id = paf.business_group_id
and job.job_definition_id = jobdef.job_definition_id
and paf.job_id = job.job_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and person_id = :person_id
and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_ROUTE_MAPPING''
and jobdef.'||l_qts_route_seg_name||' = pcv.pcv_information2';
'select max(pcv.pcv_information4) QT_status
from per_all_assignments_f paf,
per_positions pos,
per_position_definitions posdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pos.business_group_id
and pcv.business_group_id = paf.business_group_id
and pos.position_definition_id = posdef.position_definition_id
and paf.position_id = pos.position_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and person_id = :person_id
and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_ROUTE_MAPPING''
and posdef.'||l_qts_route_seg_name||' = pcv.PCV_INFORMATION2';
'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
from per_all_assignments_f paf,
per_jobs job,
per_job_definitions jobdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = job.business_group_id
and pcv.business_group_id = paf.business_group_id
and job.job_definition_id = jobdef.job_definition_id
and paf.job_id = job.job_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and person_id = :person_id
and pcv.pcv_information_category = ''PQP_GB_SWF_HLTA_STATUS_MAPPING''
and jobdef.'||l_hlta_seg_name||' = pcv.pcv_information2';
'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
from per_all_people_f pap,
pqp_configuration_values pcv
where attribute_category = '''||l_hlta_context_name||'''
and pap.person_id = :person_id
and :effective_date between pap.effective_start_date and pap.effective_end_date
and pcv.business_group_id = pap.business_group_id
and pcv.pcv_information_category = ''PQP_GB_SWF_HLTA_STATUS_MAPPING''
and pap.'||l_hlta_seg_name||' = pcv.pcv_information2';
'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
from per_all_assignments_f paf,
pqp_configuration_values pcv
where ass_attribute_category = '''||l_hlta_context_name||'''
and paf.person_id = :person_id
and :effective_date between paf.effective_start_date and paf.effective_end_date
and pcv.business_group_id = paf.business_group_id
and pcv.pcv_information_category = ''PQP_GB_SWF_HLTA_STATUS_MAPPING''
and pap.'||l_hlta_seg_name||' = pcv.pcv_information2';
'select pcv.pcv_information4
from per_all_assignments_f paf,
per_grades pgr,
per_grade_definitions pgd ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pgr.business_group_id
and pcv.business_group_id = paf.business_group_id
and pgr.grade_definition_id = pgd.grade_definition_id
and paf.grade_id = pgr.grade_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_POST_MAPPING''
and ((pcv_information3 is null
and pgd.'||l_cont_post_seg||' = pcv.pcv_information2 )
or (pcv_information3 is not null
and pgd.'||l_cont_post_seg||' between pcv.pcv_information2 and pcv_information3))';
'select pcv.pcv_information4
from per_all_assignments_f paf,
per_jobs job,
per_job_definitions jobdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = job.business_group_id
and pcv.business_group_id = paf.business_group_id
and job.job_definition_id = jobdef.job_definition_id
and paf.job_id = job.job_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_POST_MAPPING''
and jobdef.'||l_cont_post_seg||' = pcv.pcv_information2';
'select pcv.pcv_information4
from per_all_assignments_f paf,
per_positions pos,
per_position_definitions posdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pos.business_group_id
and pcv.business_group_id = paf.business_group_id
and pos.position_definition_id = posdef.position_definition_id
and paf.position_id = pos.position_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_POST_MAPPING''
and posdef.'||l_cont_post_seg||' = pcv.pcv_information2';
'select '||l_origin_segment||'
from per_all_assignments_f where ass_attribute_category = '''||l_origin_context||'''
and assignment_id = :assignment_id
and :effective_date between effective_start_date and effective_end_date';
'select '||l_origin_segment||
' from per_all_people_f where ATTRIBUTE_CATEGORY = '''||l_origin_context||'''
and person_id = :person_id
and :effective_date between effective_start_date and effective_end_date';
'select '||l_destination_segment||'
from per_all_assignments_f where ass_attribute_category = '''||l_destination_context||'''
and assignment_id = :assignment_id
and :effective_date between effective_start_date and effective_end_date';
'select '||l_destination_segment||
' from per_periods_of_service where ATTRIBUTE_CATEGORY = '''||l_destination_context||'''
and person_id = :person_id';
'select pcv.pcv_information4
from per_all_assignments_f paf,
per_grades pgr,
per_grade_definitions pgd ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pgr.business_group_id
and pcv.business_group_id = paf.business_group_id
and pgr.grade_definition_id = pgd.grade_definition_id
and paf.grade_id = pgr.grade_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
and ((pcv_information3 is null
and pgd.'||l_role_segment||' = pcv.pcv_information2 )
or (pcv_information3 is not null
and pgd.'||l_role_segment||' between pcv.pcv_information2 and pcv_information3))';
'select pcv.pcv_information4
from per_all_assignments_f paf,
per_jobs job,
per_job_definitions jobdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = job.business_group_id
and pcv.business_group_id = paf.business_group_id
and job.job_definition_id = jobdef.job_definition_id
and paf.job_id = job.job_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
and jobdef.'||l_role_segment||' = pcv.pcv_information2';
'select pcv.pcv_information4
from per_all_assignments_f paf,
per_positions pos,
per_position_definitions posdef ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pos.business_group_id
and pcv.business_group_id = paf.business_group_id
and pos.position_definition_id = posdef.position_definition_id
and paf.position_id = pos.position_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
and posdef.'||l_role_segment||' = pcv.pcv_information2';
'select pcv.pcv_information4
from per_all_assignments_f paa,
per_position_extra_info pei,
pqp_configuration_values pcv
where paa.assignment_id = :p_assignment_id
and pei.position_id = paa.position_id
and pei.information_type = '''||l_addl_role_context||'''
and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
and paa.business_group_id = pcv.business_group_id
and pei.'||l_addl_role_segment||' = pcv.pcv_information2
and :effective_date between paa.effective_start_date and paa.effective_end_date';
'select pcv.pcv_information4
from per_all_assignments_f paa,
per_job_extra_info jei,
pqp_configuration_values pcv
where paa.assignment_id = :p_assignment_id
and jei.job_id = paa.job_id
and jei.information_type = '''||l_addl_role_context||'''
and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
and paa.business_group_id = pcv.business_group_id
and jei.'||l_addl_role_segment||' = pcv.pcv_information2
and :effective_date between paa.effective_start_date and paa.effective_end_date';
'select pcv.pcv_information4
from per_assignment_extra_info aei,
pqp_configuration_values pcv
where aei.assignment_id = :p_assignment_id
and aei.information_type = '''||l_addl_role_context||'''
and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
and pcv.business_group_id = :bg_id
and aei.'||l_addl_role_segment||' = pcv.pcv_information2';
insert into pay_message_lines(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values(
pay_message_lines_s.nextval
,null
,p_message_level
,assignment_action_id
,'A'
,substr(p_message_text,1,240)
);
sqlstr := 'select distinct person_id '
||'from per_all_people_f ppf, '
||'pay_payroll_actions ppa '
||'where ppa.payroll_action_id = :payroll_action_id '
||'and ppa.business_group_id = ppf.business_group_id '
||'order by ppf.person_id';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_pactid
and assignment_id = p_assignment_id;
select min(assignment_action_id)
from pay_assignment_actions
where assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id)
and payroll_action_id = p_pactid;
select(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_YEAR')) census_year,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_DAY')) census_day,
add_months(to_date((pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_ST_DAY'))),-12) cont_st_day,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_END_DAY')) cont_end_day,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'LEA_NUM')) lea_num,
upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_ABS')) exclude_abs,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL')) exclude_qual,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ASG_SET')) asg_set,
effective_date,
business_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
select distinct asg.assignment_id,
asg.person_id
from per_all_assignments_f asg,
hr_location_extra_info hlei,
per_people_extra_info pei,
(select distinct min(asg1.effective_start_date) over( partition by assignment_id) effective_start_date,
max(asg1.effective_end_date) over( partition by assignment_id) effective_end_date,
first_value(location_id)over( partition by assignment_id order by asg1.effective_end_date desc ) location_id,
asg1.assignment_id
from per_all_assignments_f asg1,
per_assignment_status_types pas
where asg1.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = 'ACTIVE_ASSIGN') min_max
where asg.person_id between stperson and endperson
and asg.business_group_id + 0 = g_business_group_id
and min_max.location_id = hlei.location_id
and hlei.information_type = 'PQP_GB_EDU_ESTB_INFO'
and hlei.lei_information6 = g_lea_number
and (g_estb_number is null
or hlei.lei_information2 = g_estb_number)
and pei.person_id = asg.person_id
and pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
and pei.pei_information5 <> 'OTHER'
and min_max.assignment_id = asg.assignment_id
and (min_max.effective_end_date between g_cont_data_st_date and g_cont_data_end_date -- contract change or the contract is terminated
or (g_census_day between min_max.effective_start_date and min_max.effective_end_date
) -- Check for snapshot data
or (g_exclude_absence = 'No' -- Check absence existance only if it is not excluded
and exists (select 1 -- If an absence exists in the previous calander year
from per_absence_attendances abs
where abs.person_id = asg.person_id
and abs.business_group_id = asg.business_group_id
and (abs.date_end between g_cont_data_st_date and g_cont_data_end_date
or abs.date_start between g_cont_data_st_date and g_cont_data_end_date))
)
or (g_exclude_qual = 'No' -- Qualification present or not?
and g_census_day between min_max.effective_start_date and min_max.effective_end_date -- check if contract is still
and exists (select 1 from per_qualifications qual -- valid only if qual data is included
where qual.person_id = asg.person_id )
)
)
-- 28 Days Condition
and decode(min_max.effective_end_date,hr_general.end_of_time,to_date(g_census_day),min_max.effective_end_date)-
min_max.effective_start_date >= 28
-- 28 Days Condition
and (g_data_ret_type <> 'TYPE3'
or (g_data_ret_type = 'TYPE3' and pei.pei_information5 = 'CENTRAL_STAFF'))
and (p_asg_set_id is null -- don't check for assignment set in this case
or exists (select 1
from hr_assignment_sets has1
where has1.assignment_set_id = p_asg_set_id
and has1.business_group_id = asg.business_group_id
and nvl(has1.payroll_id,asg.payroll_id) = asg.payroll_id
and (not exists (select 1 -- chk no amendments
from hr_assignment_set_amendments hasa1
where hasa1.assignment_set_id = has1.assignment_set_id)
or exists (select 1 -- chk include amendments
from hr_assignment_set_amendments hasa2
where hasa2.assignment_set_id = has1.assignment_set_id
and hasa2.assignment_id = asg.assignment_id
and nvl(hasa2.include_or_exclude,'I') = 'I')
or (not exists (select 1 --chk no exlude amendments
from hr_assignment_set_amendments hasa3
where hasa3.assignment_set_id = has1.assignment_set_id
and hasa3.assignment_id = asg.assignment_id
and nvl(hasa3.include_or_exclude,'I') = 'E')
and not exists (select 1 --and chk no Inc amendments
from hr_assignment_set_amendments hasa4
where hasa4.assignment_set_id = has1.assignment_set_id
and nvl(hasa4.include_or_exclude,'I') = 'I')
) -- end checking exclude amendments
) -- done checking amendments
) -- done asg set check when not null
); -- end of asg set check
select distinct asg.assignment_id,asg.person_id
from per_all_assignments_f asg,
pqp_assignment_attributes_f att,
hr_location_extra_info hlei,
per_people_extra_info pei,
(select min(att1.effective_start_date) effective_start_date,
max(att1.effective_end_date) effective_end_date,
att1.assignment_id
from pqp_assignment_attributes_f att1
group by assignment_id ) min_max,
(select first_value(location_id)over( partition by assignment_id order by asg1.effective_end_date desc ) location_id,
asg1.assignment_id
from per_all_assignments_f asg1,
per_assignment_status_types pas
where asg1.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = 'ACTIVE_ASSIGN') loc
where asg.person_id between stperson and endperson
and asg.business_group_id + 0 = g_business_group_id
and att.business_group_id = asg.business_group_id
and asg.assignment_id = att.assignment_id
and loc.assignment_id = asg.assignment_id
and loc.location_id = hlei.location_id
and hlei.information_type = 'PQP_GB_EDU_ESTB_INFO'
and hlei.lei_information6 = g_lea_number
and (g_estb_number is null
or hlei.lei_information2 = g_estb_number)
and pei.person_id = asg.person_id
and pei.pei_information5 <> 'OTHER'
and pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
and min_max.assignment_id = asg.assignment_id
and (min_max.effective_end_date between g_cont_data_st_date and g_cont_data_end_date -- contract change or the contract is terminated
or (g_census_day between min_max.effective_start_date and min_max.effective_end_date
) -- Check for snapshot data only
or (g_exclude_absence = 'No' -- Check absence existance only if it is not excluded
and exists (select 1 -- If an absence exists in the previous calander year
from per_absence_attendances abs
where abs.person_id = asg.person_id
and abs.business_group_id = asg.business_group_id
and (abs.date_end between g_cont_data_st_date and g_cont_data_end_date
or abs.date_start between g_cont_data_st_date and g_cont_data_end_date))
)
or (g_exclude_qual = 'No' -- Qualification present or not?
and g_census_day between min_max.effective_start_date and min_max.effective_end_date -- check if contract is still
and exists (select 1 from per_qualifications qual -- valid only if qual data is included
where qual.person_id = asg.person_id )
)
)
-- 28 Days Condition
and decode(min_max.effective_end_date,hr_general.end_of_time,to_date(g_census_day),min_max.effective_end_date)-
min_max.effective_start_date >= 28
-- 28 Days Condition
and (g_data_ret_type <> 'TYPE3'
or (g_data_ret_type = 'TYPE3' and pei.pei_information5 = 'CENTRAL_STAFF'))
and (p_asg_set_id is null -- don't check for assignment set in this case
or exists (select 1
from hr_assignment_sets has1
where has1.assignment_set_id = p_asg_set_id
and has1.business_group_id = asg.business_group_id
and nvl(has1.payroll_id,asg.payroll_id) = asg.payroll_id
and (not exists (select 1 -- chk no amendments
from hr_assignment_set_amendments hasa1
where hasa1.assignment_set_id = has1.assignment_set_id)
or exists (select 1 -- chk include amendments
from hr_assignment_set_amendments hasa2
where hasa2.assignment_set_id = has1.assignment_set_id
and hasa2.assignment_id = asg.assignment_id
and nvl(hasa2.include_or_exclude,'I') = 'I')
or (not exists (select 1 --chk no exlude amendments
from hr_assignment_set_amendments hasa3
where hasa3.assignment_set_id = has1.assignment_set_id
and hasa3.assignment_id = asg.assignment_id
and nvl(hasa3.include_or_exclude,'I') = 'E')
and not exists (select 1 --and chk no Inc amendments
from hr_assignment_set_amendments hasa4
where hasa4.assignment_set_id = has1.assignment_set_id
and nvl(hasa4.include_or_exclude,'I') = 'I')
) -- end checking exclude amendments
) -- done checking amendments
) -- done asg set check when not null
); -- end of asg set check
select distinct pcv_information1
from pqp_configuration_values
where pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
and business_group_id = g_business_group_id;
select pay_assignment_actions_s.nextval
into l_ass_act_id
from dual;
select pay_assignment_actions_s.nextval
into l_ass_act_id
from dual;
select membership_number
from per_qualifications
where person_id = p_person_id
and membership_number is not null;
select pcv_information2
from pqp_configuration_values
where pcv_information_category = p_context_name
and pcv_information1 = p_value
and business_group_id = g_business_group_id;
select /*+ ORDERED */
pap.person_id,
pap.employee_number,
paa.assignment_id,
pap.first_name,
pap.last_name ,
paa.assignment_number,
pap.national_identifier,
decode(pap.sex,'M','1','F','2','9') sex,
pap.previous_last_name,
per_information1 ethnic_code,
pap.date_of_birth date_of_birth,
to_char(pap.date_of_birth,'YYYY-MM-DD') dob_dcsf
from pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f pap
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and csr_effective_date between paa.effective_start_date and paa.effective_end_date
and csr_effective_date between pap.effective_start_date and pap.effective_end_date;
select hr_general.decode_lookup('YES_NO',pei_information1 ) qt_sts,
fnd_date.canonical_to_date(pei_information2) qt_status_date,
pei_information3 qts_route,
pei_information4 teacher_number,
pei_information5 workforce_inc_typ,
hr_general.decode_lookup('YES_NO',pei_information9) hlta_sts,
pei_information10 origin
from per_people_extra_info pei
where pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
and pei.person_id = p_person_id;
select 'YES'
from per_disabilities_f pdf
where pdf.person_id = p_person_id
and csr_effective_date between pdf.effective_start_date and pdf.effective_end_date;
select max(effective_end_date)
from pay_assignment_actions act,
per_all_assignments_f paa,
per_assignment_status_types pas
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and paa.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = 'ACTIVE_ASSIGN';
select max(effective_end_date)
from pay_assignment_actions act,
pqp_assignment_attributes_f att
where act.assignment_action_id = p_assactid
and act.assignment_id = att.assignment_id;
select ppf.person_id,ppf.employee_number
from pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f ppf
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and ppf.person_id = paa.person_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
select hlei.lei_information2
from per_all_assignments_f asg,
hr_location_extra_info hlei
where asg.business_group_id + 0 = g_business_group_id
and asg.location_id = hlei.location_id
and asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and asg.location_id = hlei.location_id
and hlei.information_type = 'PQP_GB_EDU_ESTB_INFO'
and hlei.lei_information6 = g_lea_number;
select 'Y'
into l_valid_ethnic_code
from dual
where exists
(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'UK_ETHNIC_CODES'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_staff_rec.ethnic_code);
select 'Y'
into l_valid_ethnic_code
from dual
where exists(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'PQP_GB_SWF_QTS_ROUTES'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_qts_route);
select ff.formula_id,ff.formula_name
from ff_formulas_f ff
where ff.formula_id = p_formula_id
and ff.business_group_id = p_business_group_id
and p_effective_date between ff.effective_start_date and ff.effective_end_date;
select ff.formula_id,ff.formula_name
from ff_formulas_f ff
where ff.formula_name = 'GB_CALCULATE_FTE_USING_PQP_CONTRACT_TYPES'
and ff.legislation_code = 'GB'
and p_effective_date between ff.effective_start_date and ff.effective_end_date;
select pexi.eei_information1,to_char(nvl(sum(prrv.result_value),0),'fm999999.00')
bulk collect into p_addl_payments
from per_all_assignments_f paf,
pay_element_entries_f pee,
pay_element_type_extra_info pexi,
pay_run_results prr,
pay_input_values_f piv,
pay_run_result_values prrv,
pay_assignment_actions assact,
pay_payroll_actions pact,
per_time_periods pptp
where paf.assignment_id = p_assignment_id
and paf.assignment_id = pee.assignment_id
and pee.element_type_id = pexi.element_type_id
and pexi.information_type = 'PQP_SWFC_ADDITIONAL_PAYMNT_CAT'
and pexi.element_type_id = prr.element_type_id
and prr.assignment_action_id = assact.assignment_action_id
and assact.payroll_action_id = pact.payroll_action_id
and pact.time_period_id = pptp.time_period_id
and pptp.regular_payment_date between add_months(trunc(g_census_day),-12) and trunc(g_census_day)-1
and prr.run_result_id = prrv.run_result_id
and prr.status in ('P','PA')
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pexi.element_type_id
and piv.name = 'Pay Value'
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and p_effective_date between pee.effective_start_date and pee.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date
group by pexi.eei_information1;
select paa.assignment_number assignment_number,
paa.employee_category employee_cat,
paa.employment_category assignment_cat
from per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
select pcv_information4
from pqp_configuration_values
where business_group_id = g_business_group_id
and pcv_information_category = 'PQP_GB_SWF_ROLE_MAPPING'
and pcv_information2 = p_role;
select pcv_information1 hrs_source
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_HOURS'
and pcv.business_group_id = g_business_group_id;
select pcv_information1 hrs_source,
pcv_information2 contract_type,
pcv_information3 hrs_per_wk_formula,
pcv_information4 wks_per_yr_source,
pcv_information5 wks_per_yr_formula,
pcv_information6 wks_per_yr_column
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_HOURS'
and nvl(pcv.pcv_information2,p_contract_type) = p_contract_type
and pcv.business_group_id = g_business_group_id;
select pcv_information8 default_weeks_per_yr
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_HOURS'
and pcv.pcv_information7 = decode(p_staff_cat,1,'REGULAR_TEACHER',2,'AGENCY_TEACHER',3,'TEACHING_ASSISTANT',4,'OTHER_SUPPORT_STAFF')
and pcv.business_group_id = g_business_group_id;
select att.contract_type,work_pattern
from per_all_assignments_f paa,
pqp_assignment_attributes_f att
where paa.assignment_id = p_assignment_id
and paa.assignment_id = att.assignment_id
and p_effective_date between att.effective_start_date and att.effective_end_date
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
select paa.assignment_number,paa.person_id,paa.frequency,paa.normal_hours
from per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
select val.value
from pay_user_tables tab,
pay_user_columns col,
pay_user_rows_f r,
pay_user_column_instances_f val
where tab.user_table_name = 'PQP_COMPANY_WORK_PATTERNS'
and tab.user_table_id=col.user_table_id
and tab.user_table_id=r.user_table_id
and col.user_column_id=val.user_column_id
and r.user_row_id= val.user_row_id
and col.user_column_name = p_work_pattern
and r.row_low_range_or_name = 'Number of Working Days'
and g_census_day between r.effective_start_date and r.effective_end_date
and g_census_day between val.effective_start_date and val.effective_end_date;
select val.value
from pay_user_tables tab,
pay_user_columns col,
pay_user_rows_f r,
pay_user_column_instances_f val
where tab.user_table_name = 'PQP_CONTRACT_TYPES'
and tab.business_group_id = g_business_group_id
and tab.user_table_id=col.user_table_id
and tab.user_table_id=r.user_table_id
and col.user_column_id=val.user_column_id
and r.user_row_id= val.user_row_id
and col.user_column_id = p_column_no
and r.row_low_range_or_name = p_contract_type
and g_census_day between r.effective_start_date and r.effective_end_date
and g_census_day between val.effective_start_date and val.effective_end_date;
select pcv_information1,pcv_information2
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_FTE_HOURS'
and pcv.business_group_id = g_business_group_id;
select value
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and unit = 'HOURS'
and p_effective_date between effective_end_date and effective_start_date;
select pcv_information1
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
and pcv.business_group_id = g_business_group_id;
select paat.person_id,
paat.date_start,
to_char(paat.date_start,'YYYY-MM-DD'),
paat.date_end,
to_char(paat.date_end,'YYYY-MM-DD'),
to_char(paat.absence_days,'fm999.0'),
pcv.pcv_information5 absence_category,
p_estab_no
bulk collect into p_abs_tab
from per_absence_attendances paat,
per_absence_attendance_types paatt,
pqp_configuration_values pcv
where paat.absence_attendance_type_id = paatt.absence_attendance_type_id
and paat.person_id = p_person_id
and pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
and pcv_information1 = 'ABSENCE_CATEGORY'
and pcv.business_group_id = g_business_group_id
and pcv_information3 = paatt.absence_category
and (paat.date_end between g_cont_data_st_date and g_cont_data_end_date or
paat.date_start between g_cont_data_st_date and g_cont_data_end_date)
order by paat.date_start asc;
select paat.person_id,
paat.date_start,
to_char(paat.date_start,'YYYY-MM-DD'),
paat.date_end,
to_char(paat.date_end,'YYYY-MM-DD'),
to_char(paat.absence_days,'fm999.0'),
pcv.pcv_information5 absence_type,
p_estab_no
bulk collect into p_abs_tab
from per_absence_attendances paat,
pqp_configuration_values pcv
where paat.person_id = p_person_id
and pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
and pcv_information1 = 'ABSENCE_TYPE'
and pcv.business_group_id = g_business_group_id
and pcv_information4 = paat.absence_attendance_type_id
and (paat.date_end between g_cont_data_st_date and g_cont_data_end_date or
paat.date_start between g_cont_data_st_date and g_cont_data_end_date)
order by paat.date_start asc;
select paat.person_id,
paat.date_start,
to_char(paat.date_start,'YYYY-MM-DD'),
paat.date_end,
to_char(paat.date_end,'YYYY-MM-DD'),
to_char(paat.absence_days,'fm999.0'),
pcv.pcv_information5 absence_reason,
p_estab_no
bulk collect into p_abs_tab
from per_absence_attendances paat,
per_abs_attendance_reasons paatr,
pqp_configuration_values pcv
where paatr.abs_attendance_reason_id = paat.abs_attendance_reason_id
and paat.person_id = p_person_id
and pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
and pcv_information1 = 'ABSENCE_REASON'
and pcv.business_group_id = g_business_group_id
and pcv_information3 = paatr.name
and (paat.date_end between g_cont_data_st_date and g_cont_data_end_date or
paat.date_start between g_cont_data_st_date and g_cont_data_end_date)
order by paat.date_start asc;
select paa.assignment_id assignment_id,
paa.assignment_number assignment_number,
paa.employment_category contract_agg_type,
paa.employee_category employee_cat,
paa.employment_category assignment_cat
from pay_assignment_actions act,
per_all_assignments_f paa
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
select min(paa.effective_start_date) contract_st_date,
max(paa.effective_end_date) contract_end_date,
to_char(min(paa.effective_start_date),'YYYY-MM-DD') contract_st_date_dcsf,
decode(to_char(max(paa.effective_end_date) ,'YYYY-MM-DD'),'4712-12-31',null,
to_char(max(paa.effective_end_date) ,'YYYY-MM-DD'))contract_end_date_dcsf,
to_char(min(paa.effective_start_date) ,'YYYY-MM-DD') date_of_arrival_dcsf
from per_all_assignments_f paa,
per_assignment_status_types pas
where paa.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = 'ACTIVE_ASSIGN'
and paa.assignment_id = p_assignment_id;
select paa.assignment_id assignment_id,
paa.assignment_number assignment_number,
att.contract_type contract_agg_type,
paa.employee_category employee_cat,
paa.employment_category assignment_cat
from per_all_assignments_f paa,
pay_assignment_actions act,
pqp_assignment_attributes_f att
where act.assignment_action_id = p_assactid
and act.assignment_id = att.assignment_id
and paa.assignment_id = att.assignment_id
and p_effective_date between att.effective_start_date and att.effective_end_date
and p_effective_date between paa.effective_start_date and paa.effective_end_date;
select min(att.effective_start_date) contract_st_date,
max(att.effective_end_date) contract_end_date,
to_char(min(att.effective_start_date) ,'YYYY-MM-DD') contract_st_date_dcsf,
decode(to_char(max(att.effective_end_date) ,'YYYY-MM-DD'),'4712-12-31',null,
to_char(max(att.effective_end_date) ,'YYYY-MM-DD'))contract_end_date_dcsf,
to_char(min(paa.effective_start_date) ,'YYYY-MM-DD') date_of_arrival_dcsf
from pqp_assignment_attributes_f att,
per_all_assignments_f paa
where att.assignment_id = p_assignment_id
and paa.assignment_id = att.assignment_id;
select pcv_information1, pcv_information2
from pqp_configuration_values
where business_group_id = g_business_group_id
and pcv_information_category = 'PQP_GB_SWF_CNTRT_ARRIVAL_DATE';
select pcv_information4
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_POST_MAPPING'
and pcv_information2 = p_emp_or_asgcat
and pcv.business_group_id = g_business_group_id;
select pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_ORIGIN_MAPPING'
and pcv_information2 = l_origin
and pcv.business_group_id = g_business_group_id;
select pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_DESTINATION_MAPPING'
and pcv_information2 = l_destination
and pcv.business_group_id = g_business_group_id;
select aei_information1
from per_assignment_extra_info
where aei_information_category = 'PQP_SCHOOL_WORKFORCE_CENSUS'
and assignment_id = p_assignment_id;
select decode(pei_information5,'CENTRAL_STAFF','L','SCHOOL_STAFF','S',null) workforce_inc_typ --- which can be archived
from per_people_extra_info pei
where pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
and pei.person_id = p_person_id;
select pcv_information5
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
and pcv_information4 = p_pqp_cont_type
and pcv.business_group_id = g_business_group_id;
select pcv_information5
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
and pcv_information3 = p_cont_type
and pcv.business_group_id = g_business_group_id;
select 'Y'
into l_valid_flag
from dual
where exists(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'PQP_GB_SWF_CNTRCT_AGRMNT_TYPES'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_contract_agg_type);
-- 4361, 4362 Deleted
-- 4370 already handled in select
-- 4375
/*if g_census_term <> 'SPRING' then -- Added as PRM and other contract types can exist in the system without end date
if l_contract_agg_type <> 'FXT' and (l_asg_dates_rec.contract_end_date > g_census_day
or months_between(l_asg_dates_rec.contract_end_date ,l_asg_dates_rec.contract_st_date) < 1) then
l_arch := false;
select 'Y'
into l_valid_flag
from dual
where exists
(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'PQP_GB_SWF_DESTINATION_CODES'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_dcsf_destination);
select 'Y'
into l_valid_flag
from dual
where exists
(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'PQP_GB_SWF_ORIGIN_CODES'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_dcsf_origin);
select qual.qualification_id qual_id,
qualtyp.qualification_type_id qualification_type_id,
qualtyp.category qual_cat,
decode(qua_information_category, 'GB', qua_information1, null) qual_code_dff,
decode(qua_information_category, 'GB', qua_information2, null) subject1_dff,
decode(qua_information_category, 'GB', qua_information3, null) subject2_dff,
decode(qua_information_category, 'GB', decode(qua_information4,'Y','true','false'), null) verified_dff
from per_qualifications qual, per_qualification_types qualtyp
where qual.person_id = p_person_id
and qual.qualification_type_id = qualtyp.qualification_type_id;
select max(decode(seq, 1, subject_dcsf, null)) subject_1,
max(decode(seq, 2, subject_dcsf, null)) subject_2
from (select subject_dcsf, seq
from (select pcv.pcv_information2 subject_dcsf, row_number() over(order by major desc) seq
from per_qualifications qual, per_subjects_taken sub,
pqp_configuration_values pcv
where qual.qualification_id = sub.qualification_id
and qual.person_id = p_person_id
and qual.qualification_id = p_qual_id
and pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_SUBJECT_MAP'
and pcv.pcv_information1 = sub.subject
and pcv.business_group_id = g_business_group_id)
where seq < 3);
select pcv_information1
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_CODE_MAP'
and pcv.business_group_id = g_business_group_id;
select pcv_information5
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_CODE_MAP'
and pcv.pcv_information3 = p_qual_cat
and pcv.business_group_id = g_business_group_id;
select pcv_information5
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_CODE_MAP'
and pcv.pcv_information4 = p_qual_typ
and pcv.business_group_id = g_business_group_id;
select ps.parent_spine_id
from per_grade_spines_f grs,
per_grades gdt,
per_parent_spines ps,
per_all_assignments_f asg
where grs.grade_id = gdt.grade_id
and grs.parent_spine_id = ps.parent_spine_id
and asg.grade_id = grs.grade_id
and asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between grs.effective_start_date and grs.effective_end_date;
select pcv.pcv_information2
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_PAY_SCALE_MAPPING'
and pcv.pcv_information1 = p_pay_scale
and pcv.business_group_id = g_business_group_id;
select spinal_point,psp.spinal_point_id
from per_spinal_point_placements_f pspp,
per_spinal_point_steps_f psps,
per_spinal_points psp
where pspp.assignment_id = p_assignment_id
and pspp.step_id = psps.step_id
and psps.spinal_point_id = psp.spinal_point_id
and p_effective_date between pspp.effective_start_date and pspp.effective_end_date
and p_effective_date between psps.effective_start_date and psps.effective_end_date;
select pcv.pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_SPINE_POINT_MAPPING'
and pcv.pcv_information1 = p_pay_scale
and pcv.pcv_information2 = p_spinal_point
and pcv.business_group_id = g_business_group_id;
select to_char(value,'fm999999.00')
from pay_grade_rules_f pgr
where grade_or_spinal_point_id = p_spinal_point_id
and p_effective_date between pgr.effective_start_date and pgr.effective_end_date;
select pcv_information1, pcv_information2, pcv_information3
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_REG_SPINE_SRC'
and pcv.business_group_id = g_business_group_id;
select pcv.pcv_information4
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_REG_SPINE_MAP_PYSCL'
and pcv.pcv_information1 = p_pay_scale
and p_spinal_point between pcv.pcv_information2 and pcv.pcv_information3
and pcv.business_group_id = g_business_group_id;
select decode(tp_safeguarded_rate_type,'SN','True','SP','True','G','True','False')
from pqp_assignment_attributes_f
where assignment_id= p_assignment_id;
l_regional_spine_sql_str :='select pcv.pcv_information4
from per_all_assignments_f paf,
per_grades pgr,
per_grade_definitions pgd ,
pqp_configuration_values pcv
where paf.business_group_id + 0 = :bg_id
and paf.business_group_id = pgr.business_group_id
and pcv.business_group_id = paf.business_group_id
and pgr.grade_definition_id = pgd.grade_definition_id
and paf.grade_id = pgr.grade_id
and :eff_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = :assignment_id
and pcv.pcv_information_category = ''PQP_GB_SWF_REG_SPINE_MAP_GRD''
and ((pcv_information3 is null
and pgd.'||l_regional_spine_segment||' = pcv.pcv_information2 )
or (pcv_information3 is not null
and pgd.'||l_regional_spine_segment||' between pcv.pcv_information2 and pcv_information3))';
select 'Y'
into l_valid_flag
from dual
where exists
(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'PQP_GB_REGIONAL_SPINE_CODE'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_reg_spinal_point_dcsf);
select 'Y'
into l_valid_flag
from dual
where exists
(select lookup_code
from hr_lookups hl
where hl.lookup_type = 'PQP_GB_DCSF_SPINE_POINTS'
and hl.enabled_flag = 'Y'
and hl.lookup_code = l_spinal_point_dcsf);
select upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_YEAR')) census_year,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_DAY')) census_day,
add_months(to_date((pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_ST_DAY'))),-12) cont_st_day,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_END_DAY')) cont_end_day,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'LEA_NUM')) lea_num,
upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_ABS')) exclude_abs,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL')) exclude_qual,
business_group_id,
ppa.payroll_action_id
from pay_assignment_actions paa, pay_payroll_actions ppa
where paa.assignment_action_id = p_assactid
and paa.payroll_action_id = ppa.payroll_action_id;
select paa.assignment_id , paa.employment_category, paa.assignment_number
from pay_assignment_actions act,
per_all_assignments_f paa,
per_assignment_status_types pas
where act.assignment_action_id = p_assactid
and act.assignment_id = paa.assignment_id
and p_census_day between paa.effective_start_date and paa.effective_end_date
and paa.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = 'ACTIVE_ASSIGN';
select distinct pcv_information1
from pqp_configuration_values
where pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
and business_group_id = g_business_group_id;
select pcv_information1,pcv_information2
from pqp_configuration_values pcv
where pcv.pcv_information_category = 'PQP_GB_SWF_FTE_HOURS'
and pcv.business_group_id = g_business_group_id;
insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
insert_archive_row(p_assactid, g_census_day, l_archive_tab);
select(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_YEAR')) census_year,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_DAY')) census_day,
add_months(to_date((pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_ST_DAY'))),-12) cont_st_day,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_END_DAY')) cont_end_day,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'LEA_NUM')) lea_num,
upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_ABS')) exclude_abs,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL')) exclude_qual,
(pay_gb_eoy_archive.get_parameter(legislative_parameters,'ASG_SET')) asg_set,
effective_date,
business_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
select distinct
peo.first_name f_name ,
peo.middle_names m_name,
peo.last_name l_name,
peo.title title,
peo.employee_number emp_no,
paf.assignment_number asg_no,
peo.national_identifier ni_no,
paa.assignment_action_id asg_act_id
from pay_payroll_actions pay,
pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f peo,
(select max(effective_end_date) effective_date,assignment_id
from per_all_assignments_f
group by assignment_id) max_eff_date
where pay.payroll_action_id = pactid
and paa.payroll_action_id = pay.payroll_action_id
and paf.assignment_id = paa.assignment_id
and peo.person_id = paf.person_id
and max_eff_date.assignment_id = paf.assignment_id
and exists (select 'X'
from pay_message_lines pml
where paa.assignment_action_id = pml.source_id)
and max_eff_date.effective_date between paf.effective_start_date and paf.effective_end_date
and max_eff_date.effective_date between peo.effective_start_date and peo.effective_end_date;
select pml.line_text error_text
from pay_message_lines pml
where pml.source_id = p_asg_act_id
and pml.MESSAGE_LEVEL = 'F'
and pml.line_sequence < (select line_sequence
from pay_message_lines pml1
where pml1.source_id = p_asg_act_id
and pml1.line_text like 'Error ORA-20001: Error(s) found while archiving data.')
UNION ALL
select pml.line_text error_text
from pay_message_lines pml
where pml.source_id = p_asg_act_id
and pml.message_level = 'W';
select distinct
peo.first_name f_name ,
peo.middle_names m_name,
peo.last_name l_name,
peo.title title,
peo.employee_number emp_no,
paf.assignment_number asg_no,
peo.national_identifier ni_no,
paa.assignment_action_id asg_act_id
from pay_payroll_actions pay,
pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f peo,
(select max(effective_end_date) effective_date,assignment_id
from per_all_assignments_f
group by assignment_id) max_eff_date
where pay.payroll_action_id = pactid
and paa.payroll_action_id = pay.payroll_action_id
and paf.assignment_id = paa.assignment_id
and peo.person_id = paf.person_id
and max_eff_date.assignment_id = paf.assignment_id
and not exists (select 'X'
from pay_message_lines pml
where paa.assignment_action_id = pml.source_id
and message_level <> 'W')
and max_eff_date.effective_date between paf.effective_start_date and paf.effective_end_date
and max_eff_date.effective_date between peo.effective_start_date and peo.effective_end_date;
select action_information4 person_id,sum(action_information7)
from pay_action_information pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = pactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'GB_SWF_HOURS_DETAILS'
and action_information5 IN ('PRM','TMP','FXT')
and action_information6 is null
group by action_information4
having sum(action_information7) > 1.5;
select peo.first_name f_name ,
peo.middle_names m_name,
peo.last_name l_name,
peo.title title,
peo.employee_number emp_no,
peo.national_identifier ni_no
from per_all_people_f peo
where person_id = p_person_id;
select COUNT(*)
from pay_action_information pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = pactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
and action_information1 = 'HDTR';
select COUNT(*)
from pay_action_information pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = pactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
and action_information1 = 'EPSY'
and action_information2 = 'F';
select COUNT(*)
from pay_action_information pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = pactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
and action_information1 = 'EPSY'
and action_information2 = 'P';
select sum(pai2.action_information7)
from pay_action_information pai,
pay_action_information pai2,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = pactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_context_id = pai2.action_context_id
and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
and pai2.action_information_category = 'GB_SWF_HOURS_DETAILS'
and pai.action_information1 = 'EPSY'
and pai.action_information2 = 'P';