[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(person_id) into l_count
from PER_PEOPLE_EXTRA_INFO ppei where
l_person_id = ppei.person_id
and ppei.information_type ='VETS 100A'
and pei_information1 is not null
and
( months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),0)) between 0 and 12
or
months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),12)) between 0 and 12
or
months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),24)) between 0 and 12
or
months_between(l_report_end_date,(add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),36)-2)) between 0 and 12
);
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, --converted to upper case, bug 13905482
upper(replace(loc.address_line_1,',',' ')||
' ') street1,
upper(replace(loc.address_line_2,',',' ')||
' '||
replace(loc.address_line_3,',',' ')) street2,
upper(replace(loc.town_or_city,',',' ')) city, --removed commas bug 13905482
upper(replace(loc.region_1,',',' ')) county, --removed commas bug 13905482
loc.region_2 state,
loc.postal_code zip_code,
upper(replace(replace(hoi2.org_information2,'-',''),',','')) naics, --removed hyphens and commas bug 13905482
upper(replace(replace(hoi2.org_information4,'-',''),',','')) duns, --removed hyphens and commas bug 13905482
upper(replace(replace(hoi2.org_information3,'-',''),',','')) ein --removed hyphens and commas bug 13905482
from per_gen_hierarchy_nodes pgn,
hr_all_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 pgn.business_group_id = p_business_group_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 --converted to upper case, bug 13905482
--,upper(replace(hoi2.org_information18,',',' ')) contact_telnum --removed commas bug 13905482
,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,
upper(replace(eloc.town_or_city,',',' ')) city, --removed commas bug 13905482
upper(replace(eloc.region_1,',',' ')) county, --removed commas bug 13905482
upper(eloc.region_2) state,
eloc.postal_code zip_code,
upper(replace(replace(hlei2.lei_information4,'-',''),',','')) naics, --removed hyphens and commas bug 13905482
upper(replace(replace(hlei2.lei_information2,'-',''),',','')) duns, --removed hyphens and commas bug 13905482
upper(replace(replace(hlei2.lei_information6,'-',''),',','')) ein, --removed hyphens and commas 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
count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,'AFSMDIS',
1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) no_dis_vets,
count(decode(peo.per_information25,'OTEV',1,'OTEDV',1,'AFSMDISOP',1,'AFSMNSDISOP',
1,'AFSMOP',1,'NSOP',1,'AFSMNSOP',1,'NSDISOP',1,null)) no_other_vets ,
count(decode(peo.per_information25,'AFSM',1,'AFSMNSDIS',1,'AFSMDIS',1,'AFSMDISOP',
1,'AFSMNSDISOP',1,'AFSMOP',1,'AFSMNSOP',1,'AFSMNS',1,null)) no_armed_vets,
count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) no_not_vets,
count(1) no_tot_vets, --#10118692
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
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
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'
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
))
--start of bug 13905482
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
--end of bug 13905482
group by hrl.lookup_code;
select
count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,'AFSMNSDISOP',
1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',1,'NSDISOP',1,null)) no_recently_vets ,
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
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
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'
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
))
and check_recent_or_not(peo.person_id,p_end_date) > 0
group by hrl.lookup_code;
select
count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,
'AFSMDIS',1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) nh_dis_vets,
count(decode(peo.per_information25,'OTEV',1,'OTEDV',1,'AFSMDISOP',1,
'AFSMNSDISOP',1,'AFSMOP',1,'NSOP',1,'AFSMNSOP',1,'NSDISOP',1,null)) nh_other_vets ,
count(decode(peo.per_information25,'AFSM',1,'AFSMNSDIS',1,'AFSMDIS',1,'AFSMDISOP',1,
'AFSMNSDISOP',1,'AFSMOP',1,'AFSMNSOP',1,'AFSMNS',1,null)) nh_armed_vets,
count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) nh_not_vets,
count(1) no_nh_tot_vets, --#10118692
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 hrl.lookup_code;
select
count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,
'AFSMNSDISOP',1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',
1,'NSDISOP',1,null)) nh_recently_vets ,
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
))
and check_recent_or_not(peo.person_id,p_end_date) > 0
group by hrl.lookup_code;
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'
--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 /*bug 14803681 - 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_jobs_vl job
,per_periods_of_service pps --8667924
WHERE
-- job.job_information_category = 'US'
-- and p_end_date between job.date_from and nvl(job.date_to,p_end_date)
paf.person_id = pps.person_id
and paf.business_group_id = pps.business_group_id
-- and job.job_information1 is not null
--and paf.job_id = job.job_id
and paf.business_group_id = p_business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
and p_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
<= p_end_date)
AND paf.location_id in /* bug:14803681 - 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
Commented and added the following condition to ensure that terminated
employees without FPD are not picked up for total count*/
AND p_end_date BETWEEN pps.date_start AND nvl(pps.actual_termination_date,p_end_date)
);
select count(*)
from per_gen_hierarchy_nodes
where node_type = 'EST'
and hierarchy_version_id = p_hierarchy_version_id;