[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
l_parent_node_id,
hoi1.org_information1 "Par Report Name"
from
hr_organization_information hoi1
where
hoi1.organization_id = l_parent_org_id
and hoi1.org_information_context = 'VETS_Spec' ;
Select
pghn.hierarchy_node_id "Est_Node_Id"
,pghn.parent_hierarchy_node_id "Parent Node Id"
,upper(hlei1.lei_information1) "Est_Rep_Name"
,hlei2.lei_information10 "Headquarters"
,upper(ltrim(rtrim(eloc.address_line_1))||' '||
ltrim(rtrim(eloc.address_line_2))||' '||
ltrim(rtrim(eloc.address_line_3))||', '||
ltrim(rtrim(eloc.town_or_city))||', '||
ltrim(rtrim(eloc.region_1))||', '||
ltrim(rtrim(eloc.region_2))||' '||
ltrim(rtrim(eloc.postal_code))) "Estab Address"
from
hr_location_extra_info hlei1
,hr_location_extra_info hlei2
,per_gen_hierarchy_nodes pghn
,hr_locations_all eloc
where
(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 hlei1.location_id = hlei2.location_id
and hlei1.location_id = pghn.entity_id
and pghn.parent_hierarchy_node_id = l_parent_node_id
and pghn.node_type = 'EST'
and eloc.location_id = pghn.entity_id;
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
where peo.person_id = asg.person_id
and job.job_information_category = 'US'
and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
and job.job_information1 is not null
and asg.job_id = job.job_id
and asg.business_group_id = P_BUSINESS_GROUP_ID
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
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 asg.effective_start_date =
(select max(ass1.effective_start_date)
from per_all_assignments_f ass1
where l_end_date between ass1.effective_start_date and ass1.effective_end_date
and asg.person_id = ass1.person_id
and ass1.assignment_type = 'E'
and ass1.primary_flag = 'Y'
)
and l_end_date between asg.effective_start_date and asg.effective_end_date
and l_end_date between peo.effective_start_date and peo.effective_end_date
and asg.location_id in
(select distinct pgn.entity_id
from per_gen_hierarchy_nodes pgn
where pgn.hierarchy_version_id = p_hierarchy_version_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
where peo.person_id = asg.person_id
and job.job_information_category = 'US'
and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
and job.job_information1 is not null
and asg.job_id = job.job_id
and asg.business_group_id = P_BUSINESS_GROUP_ID
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
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 asg.effective_start_date =
(select max(ass1.effective_start_date)
from per_all_assignments_f ass1
where l_end_date between ass1.effective_start_date and ass1.effective_end_date
and asg.person_id = ass1.person_id
and ass1.assignment_type = 'E'
and ass1.primary_flag = 'Y'
)
and l_end_date between asg.effective_start_date and asg.effective_end_date
and l_end_date between peo.effective_start_date and peo.effective_end_date
and asg.location_id in
(select distinct pgn.entity_id
from per_gen_hierarchy_nodes pgn
where pgn.hierarchy_version_id = p_hierarchy_version_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_end_date) > 0 ;
SELECT
peo.person_id
,peo.last_name
,peo.first_name
,peo.employee_number
,peo.per_information25 veteran
,asg.assignment_id
from per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
where peo.person_id = asg.person_id
and job.job_information_category = 'US'
and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
and job.job_information1 is not null
and asg.job_id = job.job_id
and asg.business_group_id = P_BUSINESS_GROUP_ID
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
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 asg.effective_start_date =
(select max(ass1.effective_start_date)
from per_all_assignments_f ass1
where l_end_date between ass1.effective_start_date and ass1.effective_end_date
and asg.person_id = ass1.person_id
and ass1.assignment_type = 'E'
and ass1.primary_flag = 'Y'
)
and l_end_date between asg.effective_start_date and asg.effective_end_date
and l_end_date between peo.effective_start_date and peo.effective_end_date
and asg.location_id in
(select distinct pgn.entity_id
from per_gen_hierarchy_nodes pgn
where pgn.hierarchy_version_id = p_hierarchy_version_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
,asg.assignment_id
from per_all_people_f peo,
per_all_assignments_f asg,
per_jobs_vl job
where peo.person_id = asg.person_id
and job.job_information_category = 'US'
and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
and job.job_information1 is not null
and asg.job_id = job.job_id
and asg.business_group_id = P_BUSINESS_GROUP_ID
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
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 asg.effective_start_date =
(select max(ass1.effective_start_date)
from per_all_assignments_f ass1
where l_end_date between ass1.effective_start_date and ass1.effective_end_date
and asg.person_id = ass1.person_id
and ass1.assignment_type = 'E'
and ass1.primary_flag = 'Y'
)
and l_end_date between asg.effective_start_date and asg.effective_end_date
and l_end_date between peo.effective_start_date and peo.effective_end_date
and asg.location_id in
(select distinct pgn.entity_id
from per_gen_hierarchy_nodes pgn
where pgn.hierarchy_version_id = p_hierarchy_version_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_end_date) > 0 ;
select
count(distinct asg.person_id)
into l_count_emps
from per_all_assignments_f asg,
per_periods_of_service pps /*8667924*/
where
asg.business_group_id = P_BUSINESS_GROUP_ID
and asg.assignment_type = 'E'
and asg.primary_flag = 'Y'
and asg.person_id = pps.person_id
and asg.business_group_id = pps.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' )
or
months_between(l_end_date,pps.actual_termination_date) between 0 and 12 /*8667924*/
)
and asg.effective_start_date =
(select max(ass1.effective_start_date)
from per_all_assignments_f ass1
where l_end_date between ass1.effective_start_date and ass1.effective_end_date
and asg.person_id = ass1.person_id
and ass1.assignment_type = 'E'
and ass1.primary_flag = 'Y'
)
and l_end_date between asg.effective_start_date and asg.effective_end_date
and asg.location_id in
(select distinct pgn.entity_id
from per_gen_hierarchy_nodes pgn
where pgn.hierarchy_version_id = p_hierarchy_version_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 sysdate
into l_report_date
from dual;