[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
pgh.name, pgv.version_number, pgn.entity_id, pgn.hierarchy_node_id
into
c_hierarchy_name, c_hierarchy_version_num, c_parent_org_id, c_parent_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 null
into l_dummy
from hr_all_organization_units
where organization_id = c_parent_org_id
and location_id is not null;
select null
into l_dummy
from hr_organization_information
where organization_id = c_parent_org_id
and org_information_context = 'EEO_Spec';
select null
into l_dummy
from hr_location_extra_info hlei1,
hr_location_extra_info hlei2,
per_gen_hierarchy_nodes pgn,
hr_locations_all eloc
where pgn.hierarchy_version_id = p_hierarchy_version_id
and pgn.node_type = 'EST'
and eloc.location_id = pgn.entity_id
and hlei1.location_id = eloc.location_id
and hlei1.information_type = 'EEO-1 Specific Information'
and hlei1.lei_information_category= 'EEO-1 Specific Information'
and hlei2.location_id = eloc.location_id
and hlei2.information_type = 'Establishment Information'
and hlei2.lei_information_category= 'Establishment Information';
select 'Y'
from
per_all_assignments_f ass
where
ass.person_id = person_id1
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and (ass.location_id is null
or ass.location_id not in
(select entity_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and node_type <> 'PAR'))
;
select 'Y'
from
per_all_assignments_f ass
where
ass.person_id = person_id1
and ass.business_group_id = P_BUSINESS_GROUP_ID
and report_date_end between ass.effective_start_date and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and exists (select 'x'
from hr_organization_information hoi1
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);
select 'Y'
from per_all_people_f peo
where
(
(peo.per_information1 is not null
and exists
(select null
from hr_lookups
where peo.per_information1 = lookup_code
and lookup_type = 'US_ETHNIC_GROUP'
)
)
)
and peo.person_id = person_id1
and peo.per_information_category = 'US'
and peo.business_group_id = P_BUSINESS_GROUP_ID
and report_date_end between peo.effective_start_date
and peo.effective_end_date
;
select 'Y'
from
per_all_assignments_f ass
where
ass.person_id = person_id1
and ass.business_group_id = P_BUSINESS_GROUP_ID
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and ass.employment_category is not null
and exists (select 'x'
from hr_organization_information hoi2
where ass.employment_category = hoi2.org_information1
and hoi2.org_information_context = 'Reporting Categories'
and hoi2.organization_id = P_BUSINESS_GROUP_ID);
select 'Y'
from
per_all_assignments_f ass
,per_jobs job
where
ass.person_id = person_id1
and ass.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and ass.job_id is not null
and ass.job_id = job.job_id
and job.job_information_category = 'US'
and report_date_end between job.date_from
and nvl(job.date_to, report_date_end)
and job.job_information1 in
(select lookup_code
from hr_lookups
where lookup_type = 'US_EEO1_JOB_CATEGORIES')
;
select substr(peo.full_name,1,150) emp_name,
peo.employee_number emp_num,
peo.sex sex
from per_all_people_f peo
where peo.person_id = person_id1
and peo.business_group_id = P_BUSINESS_GROUP_ID
and report_date_end between peo.effective_start_date
and peo.effective_end_date
;
select nvl(jbt.name, 'Not Specified')||' '||nvl(lup.meaning,'') job_cat
from hr_lookups lup
,per_all_assignments_f ass
,per_jobs_vl job
,per_jobs_tl jbt
where ass.person_id = person_id1
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and report_date_end between job.date_from
and nvl(job.date_to, report_date_end)
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and ass.business_group_id = P_BUSINESS_GROUP_ID
and job.business_group_id = P_BUSINESS_GROUP_ID
and ass.job_id = jbt.job_id (+)
and jbt.language(+) = userenv('LANG')
and ass.job_id = job.job_id (+)
and job.job_information1 = lup.lookup_code(+)
and lup.lookup_type(+) = 'US_EEO1_JOB_CATEGORIES'
and job.job_information_category(+) = 'US' ;
select nvl(lup.meaning, 'Not Specified') ethnic
from hr_lookups lup
,per_all_people_f peo
where peo.person_id = person_id1
and peo.business_group_id = P_BUSINESS_GROUP_ID
and peo.per_information_category(+) = 'US'
and peo.per_information1 = lup.lookup_code(+)
and lup.lookup_type(+) = 'US_ETHNIC_GROUP'
and report_date_end between peo.effective_start_date
and peo.effective_end_date
;
select nvl(lup.meaning, 'Not Specified') emp_cat
from hr_lookups lup
,per_all_assignments_f ass
where ass.person_id = person_id1
and ass.business_group_id = P_BUSINESS_GROUP_ID
and ass.employment_category = lup.lookup_code(+)
and lup.lookup_type(+) = 'EMP_CAT'
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y';
select past.user_status ustat
from per_all_assignments_f ass
,per_assignment_status_types past
where ass.person_id = person_id1
and ass.business_group_id = P_BUSINESS_GROUP_ID
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and ass.assignment_status_type_id = past.assignment_status_type_id
and past.active_flag = 'Y'
and past.primary_flag = 'P'
;
select 'Y'
from
per_all_assignments_f ass
,hr_location_extra_info hlei1
,hr_location_extra_info hlei2
where
ass.person_id = person_id1
and report_date_end between ass.effective_start_date
and ass.effective_end_date
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
and to_char(hlei1.location_id) = ass.location_id
and to_char(hlei2.location_id) = ass.location_id
and hlei1.location_id = hlei2.location_id
and hlei1.information_type = 'EEO-1 Specific Information'
and hlei1.lei_information_category= 'EEO-1 Specific Information'
and hlei2.information_type = 'Establishment Information'
and hlei2.lei_information_category= 'Establishment Information';
SELECT 'Y'
INTO l_two_or_more
FROM per_people_extra_info
WHERE person_id = person_id1
AND information_type = 'US_ETHNIC_ORIGIN'
AND pei_information7 = 'Y'
AND (DECODE(pei_information1, 'Y', 1, 0)
+ DECODE(pei_information2, 'Y', 1, 0)
+ DECODE(pei_information3, 'Y', 1, 0)
+ DECODE(pei_information4, 'Y', 1, 0)
+ DECODE(pei_information5, 'Y', 1, 0)
+ DECODE(pei_information6, 'Y', 1, 0)
) <= 1;