[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
pgh.name, pgv.version_number, pgn.entity_id, pgn.hierarchy_node_id
from
per_gen_hierarchy pgh,
per_gen_hierarchy_versions pgv,
per_gen_hierarchy_nodes pgn
where
pgh.hierarchy_id = p_hierarchy_id
and pgh.hierarchy_id = pgv.hierarchy_id
and pgv.hierarchy_version_id = p_hierarchy_version_id
and pgn.hierarchy_version_id = pgv.hierarchy_version_id
and pgn.node_type = 'PAR';
select
count(pghn.hierarchy_node_id)
from
per_gen_hierarchy_nodes pghn
where
pghn.hierarchy_version_id = p_hierarchy_version_id
and pghn.node_type = 'EST';
select
upper(hoi1.org_information1) "Reporting Name"
,hoi1.org_information2 "Company Number"
,hoi1.org_information3 "Type of Rep Org"
,upper(rpad(cloc.address_line_1 ||' '|| cloc.address_line_2 ||' '||
cloc.address_line_3,35)) "Parent Address"
,upper(cloc.town_or_city) "Parent City"
,upper(cloc.region_1) "Parent County"
,upper(cloc.region_2) "Parent State"
,upper(cloc.postal_code) "Parent Zip"
,hoi2.org_information17 contact_name
,substr(hoi2.org_information18,1,20) contact_telnum
,hoi2.org_information20 contact_email
,substr(hoi2.org_information18,1,20) || ' ' ||hoi2.org_information20 contact_telnum_and_email
from
hr_organization_information hoi1
,hr_locations_all cloc
,hr_organization_units hou
,hr_organization_information hoi2
where
hoi1.organization_id = l_parent_org_id
and hoi1.org_information_context = 'VETS_Spec'
and hoi1.organization_id = hou.organization_id
and hou.location_id = cloc.location_id
and hoi2.organization_id = p_business_group_id
and hoi2.org_information_context = 'EEO_REPORT' ;
select
org_information1
,org_information2
,org_information3
,org_information4
,org_information5
from
hr_organization_information
where
organization_id = l_parent_org_id
and org_information_context = 'VETS_EEO_Dup' ;
Select
distinct(eloc.region_2) "State"
from
hr_location_extra_info hlei1
,hr_location_extra_info hlei2
,per_gen_hierarchy_nodes pghn
,hr_locations_all eloc
where
pghn.parent_hierarchy_node_id = l_parent_node_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'
and hlei2.lei_information10 = 'N'
and eloc.region_2 = nvl(P_STATE,eloc.region_2);
SELECT
decode(lookup_code,1,2,2,3,3,4,4,5,5,6,6,7
,7,8,8,9,9,10,10,1) diplay_order,
lookup_code,
upper(rpad(meaning,26,'.'))||lookup_code cons_job_category_name,
lookup_code cons_job_category_code,
decode(lookup_code,'8','LABORERS/HELPERS'
,upper(meaning)) job_category_name
FROM hr_lookups
WHERE lookup_type = 'US_EEO1_JOB_CATEGORIES'
ORDER BY diplay_order ;
select
pghn1.entity_id
,pghn1.hierarchy_node_id
from
per_gen_hierarchy_nodes pghn1
,hr_location_extra_info hlei1
,hr_location_extra_info hlei2
,hr_locations_all eloc
where
pghn1.hierarchy_version_id = P_HIERARCHY_VERSION_ID
and pghn1.node_type = 'EST'
and eloc.location_id = pghn1.entity_id
and hlei1.location_id = pghn1.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'
and hlei2.lei_information10 = 'N'
and eloc.region_2 = p_state;
select count(distinct asg.person_id)
from
per_all_assignments_f asg
,per_gen_hierarchy_nodes pgn
,per_periods_of_service pps --8667924
where
asg.business_group_id = P_BUSINESS_GROUP_ID
and asg.person_id = pps.person_id
and asg.business_group_id = pps.business_group_id
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date
<= l_month_end_date)
AND
(EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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
)
OR /*8667924*/
months_between(l_month_end_date,pps.actual_termination_date) between 0 and 12 )
and asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC');
select entity_id
from
per_gen_hierarchy_nodes
where
(hierarchy_node_id = est_id
or parent_hierarchy_node_id = est_id)
and hierarchy_version_id = p_hierarchy_version_id;
select
pghn.hierarchy_node_id
from
hr_location_extra_info hlei1
,hr_location_extra_info hlei2
,per_gen_hierarchy_nodes pghn
,hr_locations_all eloc
where
pghn.hierarchy_version_id = p_hierarchy_version_id
and pghn.parent_hierarchy_node_id = p_parent_node_id
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'
and hlei2.lei_information10 = 'N'
and eloc.region_2 = state;
select count(distinct asg.person_id)
into no_loc_emps
from per_all_assignments_f asg
, per_periods_of_service pps
where
asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and pps.person_id=asg.person_id
and pps.business_group_id = asg.business_group_id
and P_DATE_END1 between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date
<= P_DATE_END1
)
and asg.business_group_id = P_BUSINESS_GROUP_ID
AND
( EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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)
OR
months_between(P_DATE_END1,pps.actual_termination_date) between 0 and 12 /*8667924*/
)
and asg.location_id = ent_id;
select count('asg')
from per_all_assignments_f asg
,per_gen_hierarchy_nodes pgn
where asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
--9011580
--AND l_month_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_end_date >= l_month_start_date
AND l_month_end_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date
<= l_month_end_date)
and asg.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC');
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,job.job_information1 job_category
,asg.assignment_id
,decode(peo.per_information25,'NOTVET',' ','VET',' ')||
decode(peo.per_information25,'VETDIS','L','AFSMNSDIS','L','OTEDV','L','AFSMDIS','L','NSDIS','L','AFSMDISOP','L','AFSMNSDISOP','L','NSDISOP','L')||
decode(peo.per_information25,'OTEV','M','OTEDV','M','AFSMDISOP','M','AFSMNSDISOP','M','AFSMOP','M','NSOP','M','AFSMNSOP','M','NSDISOP','M')||
decode(peo.per_information25,'AFSM','N','AFSMNSDIS','N','AFSMDIS','N','AFSMDISOP','N','AFSMNSDISOP','N','AFSMOP','N','AFSMNSOP','N','AFSMNS','N') veteran_category
from
per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
,per_gen_hierarchy_nodes pgn
where
peo.person_id = asg.person_id
and l_month_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_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 peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= l_month_end_date )
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= l_month_end_date)
and job.job_id = asg.job_id
and job.job_information_category = 'US'
and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
AND job.job_information1 = p_job_code
and asg.business_group_id = P_BUSINESS_GROUP_ID
and peo.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
union
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,job.job_information1 job_category
,asg.assignment_id
,decode(peo.per_information25,'NS','O','AFSMNSDIS','O','NSDIS','O','AFSMNSDISOP','O', 'NSOP','O','AFSMNSOP','O','AFSMNS','O','NSDISOP','O') veteran_category
from
per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
,per_gen_hierarchy_nodes pgn
where
peo.person_id = asg.person_id
and l_month_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_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 peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= l_month_end_date )
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= l_month_end_date)
and job.job_id = asg.job_id
and job.job_information_category = 'US'
and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
AND job.job_information1 = p_job_code
and asg.business_group_id = P_BUSINESS_GROUP_ID
and peo.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
and check_recent_or_not(peo.person_id,l_month_end_date) > 0 ;
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,job.job_information1 job_category
,asg.assignment_id
,decode(peo.per_information25,'NOTVET',' ','VET',' ')||
decode(peo.per_information25,'VETDIS','Q','AFSMNSDIS','Q','OTEDV','Q','AFSMDIS','Q','NSDIS','Q','AFSMDISOP','Q','AFSMNSDISOP','Q','NSDISOP','Q')||
decode(peo.per_information25,'OTEV','R','OTEDV','R','AFSMDISOP','R','AFSMNSDISOP','R','AFSMOP','R','NSOP','R','AFSMNSOP','R','NSDISOP','R')||
decode(peo.per_information25,'AFSM','S','AFSMNSDIS','S','AFSMDIS','S','AFSMDISOP','S','AFSMNSDISOP','S','AFSMOP','S','AFSMNSOP','S','AFSMNS','S') veteran_category
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job,
per_gen_hierarchy_nodes pgn,
per_periods_of_service pps
WHERE pps.person_id = peo.person_id
AND peo.person_id = asg.person_id
AND pps.business_group_id = P_BUSINESS_GROUP_ID
AND peo.business_group_id = P_BUSINESS_GROUP_ID
AND asg.business_group_id = P_BUSINESS_GROUP_ID
AND job.business_group_id = P_BUSINESS_GROUP_ID
AND peo.current_employee_flag = 'Y'
AND asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
and asg.effective_start_date <= l_month_end_date
AND asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
AND paf2.assignment_id = asg.assignment_id
AND paf2.effective_start_date = peo.effective_start_date
AND paf2.business_group_id = P_BUSINESS_GROUP_ID
AND paf2.primary_flag = 'Y'
AND paf2.assignment_type = 'E'
AND paf2.effective_start_date <= l_month_end_date)
AND months_between(l_month_end_date,pps.date_start) <= 12
AND months_between(l_month_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(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.job_id = job.job_id
AND job.job_information_category = 'US'
AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
AND job.job_information1 = p_job_code
and asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
union
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,job.job_information1 job_category
,asg.assignment_id
,decode(peo.per_information25,'NS','T','AFSMNSDIS','T','NSDIS','T','AFSMNSDISOP','T', 'NSOP','T','AFSMNSOP','T','AFSMNS','T','NSDISOP','T') veteran_category
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job,
per_gen_hierarchy_nodes pgn,
per_periods_of_service pps
WHERE pps.person_id = peo.person_id
AND peo.person_id = asg.person_id
AND pps.business_group_id = P_BUSINESS_GROUP_ID
AND peo.business_group_id = P_BUSINESS_GROUP_ID
AND asg.business_group_id = P_BUSINESS_GROUP_ID
AND job.business_group_id = P_BUSINESS_GROUP_ID
AND peo.current_employee_flag = 'Y'
AND asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
and asg.effective_start_date <= l_month_end_date
AND asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
AND paf2.assignment_id = asg.assignment_id
AND paf2.effective_start_date = peo.effective_start_date
AND paf2.business_group_id = P_BUSINESS_GROUP_ID
AND paf2.primary_flag = 'Y'
AND paf2.assignment_type = 'E'
AND paf2.effective_start_date <= l_month_end_date)
AND months_between(l_month_end_date,pps.date_start) <= 12
AND months_between(l_month_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(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.job_id = job.job_id
AND job.job_information_category = 'US'
AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
AND job.job_information1 = p_job_code
and asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
and check_recent_or_not(peo.person_id,l_month_end_date) > 0 ;
select A.loc_no_dis_vets, A.loc_no_other_vets,A.loc_no_armed_vets,B.loc_no_recsep_vets,A.loc_no_not_vets,A.loc_tot_emps -- #10113747
FROM
(
SELECT
count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,'AFSMDIS',1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) loc_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)) loc_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)) loc_no_armed_vets,
null loc_no_recsep_vets,
count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) loc_no_not_vets,
count(1) loc_tot_emps -- #10113747
from
per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
,per_gen_hierarchy_nodes pgn
where
peo.person_id = asg.person_id
and l_month_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_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 peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= l_month_end_date)
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= l_month_end_date)
and job.job_id = asg.job_id
and job.job_information_category = 'US'
and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
AND job.job_information1 = p_job_code
and asg.business_group_id = P_BUSINESS_GROUP_ID
and peo.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')) A
,
(SELECT
null loc_no_dis_vets,
null loc_no_other_vets,
null loc_no_armed_vets,
count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,'AFSMNSDISOP',1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',1,'NSDISOP',1,null)) loc_no_recsep_vets,
null loc_no_not_vets,
null loc_tot_emps -- #10113747
from
per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
,per_gen_hierarchy_nodes pgn
where
peo.person_id = asg.person_id
and l_month_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_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 peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= l_month_end_date)
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= l_month_end_date)
and job.job_id = asg.job_id
and job.job_information_category = 'US'
and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
AND job.job_information1 = p_job_code
and asg.business_group_id = P_BUSINESS_GROUP_ID
and peo.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
and check_recent_or_not(peo.person_id,l_month_end_date) > 0
) B;
select A.loc_nh_dis_vets,A.loc_nh_other_vets, A.loc_nh_armed_vets,B.loc_nh_recsep_vets,A.loc_nh_not_vets,A.loc_nh_tot_emps -- #10113747
FROM
(
SELECT
count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,'AFSMDIS',1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) loc_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)) loc_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)) loc_nh_armed_vets,
NULL loc_nh_recsep_vets,
count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) loc_nh_not_vets,
count(1) loc_nh_tot_emps -- #10113747
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job,
per_gen_hierarchy_nodes pgn,
per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.person_id = pps.person_id
AND peo.business_group_id = P_BUSINESS_GROUP_ID
AND asg.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 peo.current_employee_flag = 'Y'
AND asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
and asg.effective_start_date <= l_month_end_date
AND asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.assignment_id = asg.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 <= l_month_end_date)
AND months_between (l_month_end_date,pps.date_start) <= 12
AND months_between (l_month_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(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.job_id = job.job_id
AND job.job_information_category = 'US'
AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
AND job.job_information1 = p_job_code
and asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')) A
,
(
SELECT
NULL loc_nh_dis_vets,
NULL loc_nh_other_vets,
NULL loc_nh_armed_vets,
count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,'AFSMNSDISOP',1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',1,'NSDISOP',1,null)) loc_nh_recsep_vets,
NULL loc_nh_not_vets,
NULL loc_nh_tot_emps -- #10113747
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job,
per_gen_hierarchy_nodes pgn,
per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.person_id = pps.person_id
AND peo.business_group_id = P_BUSINESS_GROUP_ID
AND asg.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 peo.current_employee_flag = 'Y'
AND asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
and asg.effective_start_date <= l_month_end_date
AND asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.assignment_id = asg.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 <= l_month_end_date)
AND months_between (l_month_end_date,pps.date_start) <= 12
AND months_between (l_month_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(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.job_id = job.job_id
AND job.job_information_category = 'US'
AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
AND job.job_information1 = p_job_code
and asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
and check_recent_or_not(peo.person_id,l_month_end_date) > 0
)B;
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,job.job_information1 job_category
,asg.assignment_id
from
per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
,per_gen_hierarchy_nodes pgn
where
peo.person_id = asg.person_id
and l_month_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_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 peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= l_month_end_date )
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= l_month_end_date)
and job.job_id = asg.job_id
and job.job_information_category = 'US'
and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
AND job.job_information1 = p_job_code
and asg.business_group_id = P_BUSINESS_GROUP_ID
and peo.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
MINUS
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,job.job_information1 job_category
,asg.assignment_id
from
per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
,per_gen_hierarchy_nodes pgn
where
peo.person_id = asg.person_id
and l_month_end_date between peo.effective_start_date and peo.effective_end_date
and peo.current_employee_flag = 'Y'
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and l_month_end_date between asg.effective_start_date and asg.effective_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 peo2.current_employee_flag = 'Y'
and peo2.effective_start_date <= l_month_end_date )
and asg.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = asg.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= l_month_end_date)
and job.job_id = asg.job_id
and job.job_information_category = 'US'
and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
AND job.job_information1 = p_job_code
and asg.business_group_id = P_BUSINESS_GROUP_ID
and peo.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND asg.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 asg.location_id = pgn.entity_id
and (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
and pgn.node_type in ('EST','LOC')
and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
and check_recent_or_not(peo.person_id,l_month_end_date) > 0 ;
select
meaning
into l_state_meaning
from
hr_lookups
where
lookup_type = 'US_STATE'
and lookup_code = l_state;