[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hoi1.org_information2 company_number,
decode(hoi1.org_information3,'2S','S','1P','P','3B','B') contractor_type,
nvl(hoi1.org_information1,hou.name) parent_company,
upper(loc.address_line_1||
' '||
loc.address_line_2||
' '||
loc.address_line_3) street,
upper(loc.town_or_city) city,
upper(loc.region_1) county,
loc.region_2 state,
loc.postal_code zip_code,
hoi2.org_information2 naics,
hoi2.org_information4 duns,
hoi2.org_information3 ein
from --per_gen_hierarchy_nodes pghn,
per_gen_hierarchy_nodes pgn,
hr_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2,
hr_locations_all loc
where pgn.hierarchy_version_id = p_hierarchy_version_id
and pgn.node_type = 'PAR'
and pgn.entity_id = hou.organization_id
and hoi1.org_information_context = 'VETS_Spec'
and hoi1.organization_id = hou.organization_id
and hoi2.org_information_context = 'VETS_EEO_Dup'
and hoi2.organization_id = hou.organization_id
--and hou.location_id(+)= loc.location_id;
select upper(replace(hoi1.org_information2,',',' ')) company_number,
decode(hoi1.org_information3,'2S','S','1P','P','3B','B') contractor_type,
upper(replace(nvl(hoi1.org_information1,hou.name),',',' ')) parent_company,
upper(replace(loc.address_line_1,',',' ')) street1,
upper(replace(loc.address_line_2,',',' ')||
' '||
replace(loc.address_line_3,',',' ')) street2, --bug 13905482
upper(replace (loc.town_or_city,',',' ') )city,
upper(replace(loc.region_1,',',' ')) county,
loc.region_2 state,
loc.postal_code zip_code,
upper(replace(replace(hoi2.org_information2,'-',''),',','')) naics, --bug 13905482
upper(replace(replace(hoi2.org_information4,'-',''),',','')) duns, --bug 13905482
upper(replace(replace(hoi2.org_information3,'-',''),',','')) ein --bug 13905482
from per_gen_hierarchy_nodes pgn,
hr_all_organization_units hou, -- vik
-- hr_organization_units hou, -- vik
hr_organization_information hoi1,
hr_organization_information hoi2,
hr_locations_all loc
where pgn.hierarchy_version_id = p_hierarchy_version_id
and pgn.node_type = 'PAR'
and pgn.entity_id = hou.organization_id
and pgn.business_group_id = p_business_group_id -- vik
--and hou.organization_id = p_business_group_id -- vik BUG4179427
-- and loc.business_group_id = p_business_group_id -- vik bg_id
and hoi1.org_information_context = 'VETS_Spec'
and hoi1.organization_id = hou.organization_id
and hoi2.org_information_context = 'VETS_EEO_Dup'
and hoi2.organization_id = hou.organization_id
--and hou.location_id(+)= loc.location_id;
select
upper(replace(hoi2.org_information17,',',' ')) contact_name
--,upper(replace(hoi2.org_information18,',',' ')) contact_telnum
,upper(replace(translate(hoi2.org_information18,'''`;"|\-+=_#$%^&*@!~:<>?/()[]{},.',' '),' ','')) contact_telnum -- removed special characters
select upper(replace(hlei1.lei_information1,',',' ')) reporting_name,
upper(replace(hlei1.lei_information2,',',' ')) unit_number,
upper(replace(eloc.address_line_1,',',' ')) street,
upper(
replace(eloc.address_line_2,',',' ')||
' '||
replace(eloc.address_line_3,',',' ')
) street2, --bug 13905482
upper(replace(eloc.town_or_city,',',' ' )) city,
upper(replace(eloc.region_1,',',' ')) county,
upper(eloc.region_2) state,
eloc.postal_code zip_code,
upper(replace(replace(hlei2.lei_information4,'-',''),',','')) naics, --bug 13905482
upper(replace(replace(hlei2.lei_information2,'-',''),',','')) duns, --bug 13905482
upper(replace(replace(hlei2.lei_information6,'-',''),',','')) ein, --bug 13905482
hlei2.lei_information10 hq,
eloc.location_id,
pghn.hierarchy_node_id
from per_gen_hierarchy_nodes pghn,
hr_location_extra_info hlei1,
hr_location_extra_info hlei2,
hr_locations_all eloc
where pghn.hierarchy_version_id = p_hierarchy_version_id
and pghn.node_type = 'EST'
and eloc.location_id = pghn.entity_id
and hlei1.location_id = pghn.entity_id
and hlei1.location_id = hlei2.location_id
and hlei1.information_type = 'VETS-100 Specific Information'
and hlei1.lei_information_category= 'VETS-100 Specific Information'
and hlei2.information_type = 'Establishment Information'
and hlei2.lei_information_category= 'Establishment Information'
order by eloc.region_2,decode(hlei2.lei_information10,'Y',1,2);
select nvl(count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNS',1,
'VIETDISNSOP',1,null)),0) no_dis_vets,
nvl(count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,null)),0) no_viet_vets,
nvl(count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
null)),0) no_other_vets,
hrl.lookup_code
from per_periods_of_service pds,
per_people_f peo,
per_assignments_f ass,
hr_organization_information hoi1,
hr_organization_information hoi2,
per_jobs job,
hr_lookups hrl
where (pds.date_start <= p_end_date
and nvl(pds.actual_termination_date,hr_api.g_eot) >= p_end_date
or pds.date_start between p_start_date and p_end_date
and p_end_date between pds.date_start and nvl(pds.actual_termination_date,hr_api.g_eot))
and pds.person_id = ass.person_id
and peo.person_id = ass.person_id
and job.date_from <= p_end_date
and nvl(job.date_to,hr_api.g_eot) >= p_start_date
and job.job_information1 = hrl.lookup_code
and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
and ass.job_id = job.job_id
and peo.effective_start_date <= p_end_date
and peo.effective_end_date >= p_start_date
and peo.current_employee_flag = 'Y'
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and ass.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.person_id = ass.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= p_end_date)
and peo.effective_start_date =
(select max(peo2.effective_start_date)
from per_people_f peo2
where peo2.person_id = peo.person_id
and peo.current_employee_flag = 'Y'
and peo2.effective_start_date < p_end_date)
and to_char(ass.assignment_status_type_id) = hoi1.org_information1
and hoi1.org_information_context = 'Reporting Statuses'
and hoi1.organization_id = p_business_group_id
and ass.employment_category = hoi2.org_information1
and hoi2.organization_id = p_business_group_id
and hoi2.org_information_context = 'Reporting Categories'
and ass.effective_start_date <= p_end_date
and ass.effective_end_date >= p_start_date
and to_char(ass.location_id) in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and (hierarchy_node_id = l_hierarchy_node_id
or parent_hierarchy_node_id = l_hierarchy_node_id))
group by hrl.lookup_code; */
select nvl(
count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNS',1,
'VIETDISNSOP',1,'AFSMDIS',1,'AFSMDISOP',1,'AFSMDISNS',1,
'AFSMNSDISOP',1,'AFSMDISVIET',1,'AFSMVIETNSDIS',1,'AFSMVIETOPDIS'
,1,'AFSMVIETNSDISOP',1,null))
,0)
no_dis_vets,
nvl(
count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,'AFSMVIET',1,'AFSMVIETOP',1,'AFSMNSVIET',1,
'AFSMVIETNSOP',1,'AFSMDISVIET',1,'AFSMVIETNSDIS',1,'AFSMVIETOPDIS',1,
'AFSMVIETNSDISOP',1,null))
,0)
no_viet_vets,
nvl(
count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
'AFSMOP',1,'AFSMDISOP',1,'AFSMNSOP',1,'AFSMNSDISOP', 1,'AFSMVIETNSOP',1,
'AFSMVIETOPDIS',1,'AFSMVIETNSDISOP',1,'AFSMVIETOP',1,
null))
,0)
no_other_vets,
nvl(
count(decode(peo.per_information5,'NS',1,'NSDIS',1,'NSOP',1,
'NSDISOP',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,null))
,0) no_newlysep_vets, --added for bug 13905482
decode(hrl.lookup_code,10,1,hrl.lookup_code) lookup_code
from per_periods_of_service pds,
per_all_people_f peo,
per_all_assignments_f ass,
hr_organization_information hoi1,
hr_organization_information hoi2,
per_jobs job,
hr_lookups hrl
where
pds.date_start <= p_end_date
and nvl(pds.actual_termination_date,p_end_date + 1) >= p_end_date
and pds.person_id = ass.person_id
and peo.person_id = ass.person_id
-- Bug# 5000214
--and job.date_from <= p_end_date
--and nvl(job.date_to,(p_end_date + 1)) >= p_start_date
and p_end_date between job.date_from and nvl(job.date_to, p_end_date)
and job.job_information1 = hrl.lookup_code
and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
and ass.job_id = job.job_id
-- Bug# 5000214
--and peo.effective_start_date <= p_end_date
--and peo.effective_end_date >= p_start_date
and p_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
--
and ass.effective_start_date =
(select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = ass.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= p_end_date
)
--
and peo.effective_start_date =
(select max(peo2.effective_start_date)
from per_all_people_f peo2
where peo2.person_id = peo.person_id
and peo.current_employee_flag = 'Y'
and peo2.effective_start_date < p_end_date
)
--
and to_char(ass.assignment_status_type_id) = hoi1.org_information1
and hoi1.org_information_context = 'Reporting Statuses'
and hoi1.organization_id = p_business_group_id
and ass.employment_category = hoi2.org_information1
and hoi2.organization_id = p_business_group_id
and hoi2.org_information_context = 'Reporting Categories'
-- Bug 5000214
--and ass.effective_start_date <= p_end_date
--and ass.effective_end_date >= p_start_date
and p_end_date between ass.effective_start_date and ass.effective_end_date
--
and ass.location_id in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and (hierarchy_node_id = l_hierarchy_node_id
or parent_hierarchy_node_id = l_hierarchy_node_id
))
group by decode(hrl.lookup_code,10,1,hrl.lookup_code);
select count(decode(peo.per_information5,'NOTVET',1,NULL,1,'VET',1,
null)) tot_new_hires,
count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNSOP',1,
'VIETDISNS',1,null)) no_nh_dis_vets,
count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,null)) no_nh_viet_vets,
count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
null)) no_nh_other_vets,
count(decode(peo.per_information5,'NS',1,'NSDIS',1,'NSOP',1,
'NSDISOP',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,null)) no_nh_newly_sep_vets,
hrl.lookup_code
from per_periods_of_service pds,
per_people_f peo,
per_assignments_f ass,
hr_organization_information hoi1,
hr_organization_information hoi2,
per_jobs job,
hr_lookups hrl
where peo.person_id = ass.person_id
and peo.current_employee_flag = 'Y'
and pds.date_start
between p_start_date
and p_end_date
and pds.person_id = ass.person_id
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and ass.effective_start_date <= p_end_date
and ass.effective_end_date >= p_start_date
and ass.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.person_id = ass.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= p_end_date)
and peo.effective_start_date =
(select max(peo2.effective_start_date)
from per_people_f peo2
where peo2.person_id = peo.person_id
and peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= p_end_date)
and to_char(ass.assignment_status_type_id) = hoi1.org_information1
and hoi1.org_information_context = 'Reporting Statuses'
and hoi1.organization_id = p_business_group_id
and ass.employment_category = hoi2.org_information1
and hoi2.org_information_context = 'Reporting Categories'
and hoi2.organization_id = p_business_group_id
and ass.job_id = job.job_id
and job.job_information_category = 'US'
and job.date_from <= p_end_date
and nvl(job.date_to,hr_api.g_eot) >= p_start_date
and job.job_information1 = hrl.lookup_code
and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
and to_char(ass.location_id) in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and (hierarchy_node_id = l_hierarchy_node_id
or parent_hierarchy_node_id = l_hierarchy_node_id))
group by hrl.lookup_code;
select
count(1) total, --BUG 13905482
count(decode(peo.per_information5,'NOTVET',1,NULL,1,'VET',1,
null)) tot_new_hires,
count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNSOP',1,
'VIETDISNS',1,'AFSMDIS',1,'AFSMDISOP',1,'AFSMDISNS',1,
'AFSMNSDISOP',1,'AFSMDISVIET',1, 'AFSMVIETNSDIS',1,
'AFSMVIETOPDIS',1,'AFSMVIETNSDISOP',1,null)) no_nh_dis_vets,
count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,'AFSMVIET',1,'AFSMVIETOP',1,'AFSMNSVIET',1,
'AFSMVIETNSOP',1,'AFSMDISVIET',1,'AFSMVIETNSDIS',1,'AFSMVIETOPDIS',1,
'AFSMVIETNSDISOP',1,null)) no_nh_viet_vets,
count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
'AFSMOP',1,'AFSMDISOP',1,'AFSMNSOP',1,'AFSMNSDISOP',1,'AFSMVIETNSOP',1,
'AFSMVIETOPDIS',1,'AFSMVIETNSDISOP',1,'AFSMVIETOP',1,
null)) no_nh_other_vets,
count(decode(peo.per_information5,'NS',1,'NSDIS',1,'NSOP',1,
'NSDISOP',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
'VIETDISNSOP',1,'AFSMNS',1,'AFSMDISNS',1,'AFSMNSOP',1,
'AFSMNSVIET',1,'AFSMNSDISOP',1,'AFSMVIETNSOP',1,'AFSMVIETNSDIS',1,
'AFSMVIETNSDISOP',1,null)) no_nh_newly_sep_vets,
decode(hrl.lookup_code,'10','1',hrl.lookup_code) lookup_code
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_jobs job,
hr_lookups hrl,
per_periods_of_service pps
WHERE peo.person_id = ass.person_id
AND peo.person_id = pps.person_id
AND peo.business_group_id = p_business_group_id
AND ass.business_group_id = p_business_group_id
AND job.business_group_id = p_business_group_id
AND pps.business_group_id = p_business_group_id
AND ass.job_id = job.job_id
AND peo.current_employee_flag = 'Y'
AND ass.assignment_type = 'E'
AND ass.primary_flag = 'Y'
AND job.job_information_category = 'US'
AND ass.effective_start_date <= p_end_date
AND job.job_information1 = hrl.lookup_code
AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND ass.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = ass.person_id
and paf2.assignment_id = ass.assignment_id
and paf2.effective_start_date = peo.effective_start_date
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= p_end_date)
AND months_between (p_end_date,pps.date_start) <= 12
AND months_between (p_end_date,pps.date_start) >= 0
AND peo.effective_start_date = pps.date_start
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = p_business_group_id
AND ass.employment_category = hoi2.org_information1
AND hoi2.organization_id = p_business_group_id
AND hoi2.org_information_context = 'Reporting Categories'
AND hoi1.organization_id = hoi2.organization_id)
and ass.location_id in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and (hierarchy_node_id = l_hierarchy_node_id
or parent_hierarchy_node_id = l_hierarchy_node_id
))
group by decode(hrl.lookup_code,'10','1',hrl.lookup_code);
select count(*) num_people
from per_people_f ppf,
per_assignments_f paf
where ppf.person_id = paf.person_id
and ppf.business_group_id = p_business_group_id
and ppf.current_employee_flag = 'Y'
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and l_start_date
between paf.effective_start_date
and paf.effective_end_date
and l_start_date
between ppf.effective_start_date
and ppf.effective_end_date
and to_char(paf.location_id) in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and (hierarchy_node_id = l_hierarchy_node_id
or parent_hierarchy_node_id = l_hierarchy_node_id));
select count ('x') num_people
from per_all_assignments_f ass,
hr_organization_information hoi1,
hr_organization_information hoi2
where ass.assignment_type = 'e'
and ass.primary_flag = 'y'
and l_month_start_date between ass.effective_start_date and ass.effective_end_date
and ass.business_group_id = p_business_group_id
--
and to_char(ass.assignment_status_type_id) = hoi1.org_information1
and hoi1.org_information_context = 'REPORTING STATUSES'
and hoi1.organization_id = p_business_group_id
and ass.employment_category = hoi2.org_information1
and hoi2.org_information_context = 'REPORTING CATEGORIES'
and hoi2.organization_id = p_business_group_id
--
and ass.location_id in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and (hierarchy_node_id = l_hierarchy_node_id
or parent_hierarchy_node_id = l_hierarchy_node_id
));
SELECT count(*) num_people
FROM per_all_assignments_f paf
WHERE paf.business_group_id = p_business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
-- The following condition is modified for the bug# 5000214
--9011580
--AND l_month_start_date between asg.effective_start_date and asg.effective_end_date
and paf.effective_end_date >= l_month_start_date
AND l_month_end_date between paf.effective_start_date and paf.effective_end_date
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = paf.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date
<= l_month_end_date
)
AND paf.business_group_id = p_business_group_id
AND paf.location_id in --#10332486 Removed to_char
(SELECT entity_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id = p_hierarchy_version_id
AND (hierarchy_node_id = l_hierarchy_node_id
OR parent_hierarchy_node_id = l_hierarchy_node_id
))
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(paf.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND paf.employment_category = hoi2.org_information1
AND hoi2.organization_id = P_BUSINESS_GROUP_ID
AND hoi2.org_information_context = 'Reporting Categories');
SELECT count(distinct paf.person_id) num_people
FROM per_all_assignments_f paf
,per_periods_of_service pps --8667924
WHERE paf.business_group_id = p_business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
and paf.person_id = pps.person_id
and paf.business_group_id = pps.business_group_id
-- Bug# 5000214
and p_end_date between paf.effective_start_date and paf.effective_end_date
/*and paf.effective_start_date <= p_end_date
and paf.effective_end_date >= p_start_date*/
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = paf.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date
<= p_end_date)
AND paf.location_id in --#10332486 Removed to_char
(SELECT entity_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id = p_hierarchy_version_id
AND (hierarchy_node_id = l_hierarchy_node_id
OR parent_hierarchy_node_id = l_hierarchy_node_id
))
AND
(EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(paf.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND paf.employment_category = hoi2.org_information1
AND hoi2.organization_id = P_BUSINESS_GROUP_ID
AND hoi2.org_information_context = 'Reporting Categories')
OR /*8667924*/
months_between(p_end_date,pps.actual_termination_date) between 0 and 12 );
select count(*)
from per_gen_hierarchy_nodes
where node_type = 'EST'
and hierarchy_version_id = p_hierarchy_version_id;