[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select decode(pbg.METHOD_OF_GENERATION_EMP_NUM,'A'
, p_property_off , p_property_on)
,decode(pbg.METHOD_OF_GENERATION_APL_NUM,'A'
, p_property_off, p_property_on)
into p_employee_property
, p_applicant_property
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select hoi1.org_information12
, hoi1.org_information13
into p_minimum_age
, p_maximum_age
from hr_organization_information hoi1
where p_business_group_id +0 = hoi1.organization_id
and hoi1.org_information_context = 'Business Group Information';
select past.assignment_status_type_id
from per_assignment_status_types past
, per_ass_status_type_amends pasa
where pasa.assignment_status_type_id(+) = past.assignment_status_type_id
and pasa.business_group_id(+) + 0 = p_business_group_id
and nvl(past.business_group_id,p_business_group_id) = p_business_group_id
and nvl(past.legislation_code, p_legislation_code) =p_legislation_code
and nvl(pasa.active_flag,past.active_flag) = 'Y'
and nvl(pasa.default_flag,past.default_flag) = 'Y'
and nvl(pasa.per_system_status,past.per_system_status) = p_required_type;
select 'Y'
from sys.dual
where exists( select 1 from FND_DESCR_FLEX_CONTEXTS fdfc
where fdfc.APPLICATION_ID = 800
and fdfc.DESCRIPTIVE_FLEXFIELD_NAME = 'Person Developer DF'
and fdfc.enabled_flag = 'Y'
and fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_legislation_code);
select 'Y'
from sys.dual
where exists( select 1 from fnd_descr_flex_column_usages fdfc
where fdfc.APPLICATION_ID = 800
and fdfc.DESCRIPTIVE_FLEXFIELD_NAME = 'PER_PEOPLE'
and fdfc.enabled_flag = 'Y');
cursor fut_apl is select 'Y'
from sys.dual
where exists (select 'future assignment exists'
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type = 'A'
and paf.effective_start_date >= p_hire_date);
cursor fut_apl(l_assignment_id number) is select 'Y'
from sys.dual
where exists (select 'future assignment exists'
from per_assignments_f paf
where paf.assignment_id = l_assignment_id
and paf.assignment_type = 'A'
and paf.effective_start_date >= p_hire_date);
procedure update_period(p_person_id number
,p_hire_date date
,p_new_hire_date date
,p_adjusted_svc_date in date ) is
--
-- Update Period of serivice start date when Hire_date
-- has changed and Person_type has not.
--
-- Define Cursor.
--
cursor pps is select rowid,pps.*
from per_periods_of_service pps
where person_id = p_person_id
and date_start = p_hire_date
for update of date_start nowait;
hr_utility.set_message_token('PROCEDURE','per_people3_pkg.UPDATE_PERIOD');
per_periods_of_service_pkg.update_row(p_row_id => pps_rec.rowid
,p_period_of_service_id => pps_rec.PERIOD_OF_SERVICE_ID
,p_business_group_id => pps_rec.BUSINESS_GROUP_ID
,p_person_id => pps_rec.PERSON_ID
,p_date_start => p_new_hire_date
,p_termination_accepted_per_id => pps_rec.TERMINATION_ACCEPTED_PERSON_ID
,p_accepted_termination_date => pps_rec.ACCEPTED_TERMINATION_DATE
,p_actual_termination_date => pps_rec.ACTUAL_TERMINATION_DATE
,p_comments => pps_rec.COMMENTS
,p_final_process_date => pps_rec.FINAL_PROCESS_DATE
,p_last_standard_process_date => pps_rec.LAST_STANDARD_PROCESS_DATE
,p_leaving_reason => pps_rec.LEAVING_REASON
,p_notified_termination_date => pps_rec.NOTIFIED_TERMINATION_DATE
,p_projected_termination_date => pps_rec.PROJECTED_TERMINATION_DATE
,p_request_id => pps_rec.REQUEST_ID
,p_program_application_id => pps_rec.PROGRAM_APPLICATION_ID
,p_program_id => pps_rec.PROGRAM_ID
,p_program_update_date => pps_rec.PROGRAM_UPDATE_DATE
,p_attribute_category => pps_rec.ATTRIBUTE_CATEGORY
,p_attribute1 => pps_rec.ATTRIBUTE1
,p_attribute2 => pps_rec.ATTRIBUTE2
,p_attribute3 => pps_rec.ATTRIBUTE3
,p_attribute4 => pps_rec.ATTRIBUTE4
,p_attribute5 => pps_rec.ATTRIBUTE5
,p_attribute6 => pps_rec.ATTRIBUTE6
,p_attribute7 => pps_rec.ATTRIBUTE7
,p_attribute8 => pps_rec.ATTRIBUTE8
,p_attribute9 => pps_rec.ATTRIBUTE9
,p_attribute10 => pps_rec.ATTRIBUTE10
,p_attribute11 => pps_rec.ATTRIBUTE11
,p_attribute12 => pps_rec.ATTRIBUTE12
,p_attribute13 => pps_rec.ATTRIBUTE13
,p_attribute14 => pps_rec.ATTRIBUTE14
,p_attribute15 => pps_rec.ATTRIBUTE15
,p_attribute16 => pps_rec.ATTRIBUTE16
,p_attribute17 => pps_rec.ATTRIBUTE17
,p_attribute18 => pps_rec.ATTRIBUTE18
,p_attribute19 => pps_rec.ATTRIBUTE19
,p_attribute20 => pps_rec.ATTRIBUTE20
,p_pds_information_category => pps_rec.PDS_INFORMATION_CATEGORY
,p_pds_information1 => pps_rec.PDS_INFORMATION1
,p_pds_information2 => pps_rec.PDS_INFORMATION2
,p_pds_information3 => pps_rec.PDS_INFORMATION3
,p_pds_information4 => pps_rec.PDS_INFORMATION4
,p_pds_information5 => pps_rec.PDS_INFORMATION5
,p_pds_information6 => pps_rec.PDS_INFORMATION6
,p_pds_information7 => pps_rec.PDS_INFORMATION7
,p_pds_information8 => pps_rec.PDS_INFORMATION8
,p_pds_information9 => pps_rec.PDS_INFORMATION9
,p_pds_information10 => pps_rec.PDS_INFORMATION10
,p_pds_information11 => pps_rec.PDS_INFORMATION11
,p_pds_information12 => pps_rec.PDS_INFORMATION12
,p_pds_information13 => pps_rec.PDS_INFORMATION13
,p_pds_information14 => pps_rec.PDS_INFORMATION14
,p_pds_information15 => pps_rec.PDS_INFORMATION15
,p_pds_information16 => pps_rec.PDS_INFORMATION16
,p_pds_information17 => pps_rec.PDS_INFORMATION17
,p_pds_information18 => pps_rec.PDS_INFORMATION18
,p_pds_information19 => pps_rec.PDS_INFORMATION19
,p_pds_information20 => pps_rec.PDS_INFORMATION20
,p_pds_information21 => pps_rec.PDS_INFORMATION21
,p_pds_information22 => pps_rec.PDS_INFORMATION22
,p_pds_information23 => pps_rec.PDS_INFORMATION23
,p_pds_information24 => pps_rec.PDS_INFORMATION24
,p_pds_information25 => pps_rec.PDS_INFORMATION25
,p_pds_information26 => pps_rec.PDS_INFORMATION26
,p_pds_information27 => pps_rec.PDS_INFORMATION27
,p_pds_information28 => pps_rec.PDS_INFORMATION28
,p_pds_information29 => pps_rec.PDS_INFORMATION29
,p_pds_information30 => pps_rec.PDS_INFORMATION30
,p_adjusted_svc_date => l_adjusted_svc_date);
end update_period;
select assignment_id
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type = 'E'
and p_start_date between
paf.effective_start_date and paf.effective_end_date;
select pav.vacancy_id,pav.name
,pa.assignment_id -- #2381925
from per_assignments pa, per_all_vacancies pav
, per_assignment_status_types pas
where person_id = p_person_id
and pav.vacancy_id = pa.vacancy_id
and pa.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = 'ACCEPTED'
and pa.assignment_type = 'A'
and pav.vacancy_id >nvl(p_last_vacancy,0)
order by pav.vacancy_id asc;
select vacancy_id
into l_dummy_id
from per_all_vacancies pav
where pav.number_of_openings <
(select count(distinct assignment_id) + 1
from per_all_assignments_f paf
where paf.vacancy_id = pav.vacancy_id
and paf.assignment_type = 'E')
and pav.vacancy_id = p_vacancy_id;
select count(pa.assignment_id)
into p_num_accepted_appls
from per_assignments pa
, per_assignment_status_types past
where pa.person_id = p_person_id
and pa.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACCEPTED';
select pa.assignment_id
into p_new_primary_id
from per_assignments pa
, per_assignment_status_types past
where pa.person_id = p_person_id
and pa.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACCEPTED';
select count(pa.assignment_id)
into p_num_appls
from per_assignments pa
where pa.person_id =p_person_id
and pa.assignment_type = 'A';
select min(effective_start_date), max(effective_end_date)
from per_people_f
where person_id = p_person_id;
select min(effective_start_date), max(effective_end_date)
from per_assignments_f
where assignment_id = p_assignment_id;
select fcl.meaning
into p_ethnic_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_ETHNIC_GROUP'
and application_id = 800
and fcl.lookup_code = p_ethnic_code;
select fcl.meaning
into p_visa_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_VISA_TYPE'
and application_id = 800
and fcl.lookup_code = p_visa_code;
select fcl.meaning
into p_veteran_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_VETERAN_STATUS'
and application_id = 800
and fcl.lookup_code = p_veteran_code;
select fcl.meaning
into p_i9_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'PER_US_I9_STATE'
and application_id = 800
and fcl.lookup_code = p_i9_code;
select fcl.meaning
into p_new_hire_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_NEW_HIRE_STATUS'
and application_id = 800
and fcl.lookup_code = p_new_hire_code;
select fcl.meaning
into p_reason_for_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_NEW_HIRE_EXCEPTIONS'
and application_id = 800
and fcl.lookup_code = p_reason_for_code;
select fcl.meaning
into p_ethnic_disc_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_ETHNIC_DISCLOSURE'
and application_id = 800
and fcl.lookup_code = p_ethnic_disc_code;
select fcl.meaning
into p_ethnic_meaning
from fnd_common_lookups fcl
where fcl.lookup_type = 'ETH_TYPE'
and application_id = 800
and fcl.lookup_code = p_ethnic_code;
select 'X'
into l_temp
from sys.dual
where exists ( select 'Events rows exist'
from per_events pe
, per_assignments_f a
where pe.business_group_id +0 = p_business_group_id
and pe.assignment_id = a.assignment_id
and pe.date_start
between a.effective_start_date and a.effective_end_date
and pe.date_start > p_hire_date
and a.person_id = p_person_id
);