[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from per_all_people_f
where person_id = cp_person_id
and rownum = 1;
select applicant_number
from per_all_people_f
where person_id = cp_person_id
and business_group_id = cp_business_group_id
and applicant_number is not null
and (cp_effective_date between effective_start_date and effective_end_date
or effective_start_date > cp_effective_date)
order by effective_start_date ASC;
SELECT pbg.method_of_generation_apl_num
INTO l_method_of_generation
FROM per_business_groups_perf pbg
WHERE pbg.business_group_id = p_business_group_id;
PROCEDURE Update_Person_Rec
(p_person_id number
,p_effective_start_date date
,p_effective_end_date date
,p_person_type_id number
,p_applicant_number varchar2
,p_current_emp_apl_flag varchar2
,p_current_apl_flag varchar2
,p_object_version_number in out nocopy number -- BUG4081676
) IS
--
l_ovn per_all_people_f.object_version_number%TYPE;
UPDATE per_all_people_f
set person_type_id = p_person_type_id
,current_applicant_flag = p_current_apl_flag
,current_emp_or_apl_flag = p_current_emp_apl_flag
,applicant_number = p_applicant_number
,object_version_number = l_ovn -- BUG4081676
where person_id = p_person_id
and effective_start_date = p_effective_start_date
and effective_end_date = p_effective_end_date;
END Update_Person_rec;
PROCEDURE Insert_Person_Rec(p_rec csr_person_record%ROWTYPE
,p_person_id number
,p_effective_start_date date
,p_effective_end_date date
,p_person_type_id number
,p_applicant_number varchar2
,p_current_emp_apl_flag varchar2
,p_current_apl_flag varchar2
,p_current_npw_flag varchar2
,p_current_employee_flag varchar2
,p_object_version_number in out nocopy number -- BUG4081676
) IS
--
l_created_by per_all_people_f.created_by%TYPE;
l_last_update_date per_all_people_f.last_update_date%TYPE;
l_last_updated_by per_all_people_f.last_updated_by%TYPE;
l_last_update_login per_all_people_f.last_update_login%TYPE;
l_last_update_date := sysdate;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
INSERT INTO per_all_people_f
(person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
start_date,
applicant_number,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_employee_data_verified,
date_of_birth,
email_address,
employee_number,
expense_check_send_to_address,
first_name,
full_name,
known_as,
marital_status,
middle_names,
nationality,
national_identifier,
previous_last_name,
registered_disabled_flag,
sex,
title,
vendor_id,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
per_information_category,
per_information1,
per_information2,
per_information3,
per_information4,
per_information5,
per_information6,
per_information7,
per_information8,
per_information9,
per_information10,
per_information11,
per_information12,
per_information13,
per_information14,
per_information15,
per_information16,
per_information17,
per_information18,
per_information19,
per_information20,
object_version_number,
suffix,
DATE_OF_DEATH,
BACKGROUND_CHECK_STATUS ,
BACKGROUND_DATE_CHECK ,
BLOOD_TYPE ,
CORRESPONDENCE_LANGUAGE ,
FAST_PATH_EMPLOYEE ,
FTE_CAPACITY ,
HOLD_APPLICANT_DATE_UNTIL ,
HONORS ,
INTERNAL_LOCATION ,
LAST_MEDICAL_TEST_BY ,
LAST_MEDICAL_TEST_DATE ,
MAILSTOP ,
OFFICE_NUMBER ,
ON_MILITARY_SERVICE ,
ORDER_NAME ,
PRE_NAME_ADJUNCT ,
PROJECTED_START_DATE ,
REHIRE_AUTHORIZOR ,
REHIRE_RECOMMENDATION ,
RESUME_EXISTS ,
RESUME_LAST_UPDATED ,
SECOND_PASSPORT_EXISTS ,
STUDENT_STATUS ,
WORK_SCHEDULE ,
PER_INFORMATION21 ,
PER_INFORMATION22 ,
PER_INFORMATION23 ,
PER_INFORMATION24 ,
PER_INFORMATION25 ,
PER_INFORMATION26 ,
PER_INFORMATION27 ,
PER_INFORMATION28 ,
PER_INFORMATION29 ,
PER_INFORMATION30 ,
REHIRE_REASON ,
benefit_group_id ,
receipt_of_death_cert_date ,
coord_ben_med_pln_no ,
coord_ben_no_cvg_flag ,
COORD_BEN_MED_EXT_ER,
COORD_BEN_MED_PL_NAME,
COORD_BEN_MED_INSR_CRR_NAME,
COORD_BEN_MED_INSR_CRR_IDENT,
COORD_BEN_MED_CVG_STRT_DT,
COORD_BEN_MED_CVG_END_DT,
uses_tobacco_flag ,
dpdnt_adoption_date ,
dpdnt_vlntry_svce_flag ,
original_date_of_hire ,
town_of_birth ,
region_of_birth ,
country_of_birth ,
global_person_id ,
party_id ,
npw_number,
current_npw_flag,
local_name,
global_name,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
-- ---------------------------------------------
VALUES
-- ---------------------------------------------
(p_person_id,
p_effective_start_date,
p_effective_end_date,
p_rec.business_group_id,
p_person_type_id,
p_rec.last_name,
p_rec.start_date,
p_applicant_number,
p_rec.comment_id,
p_current_apl_flag,
p_current_emp_apl_flag,
p_current_employee_flag,
p_rec.date_employee_data_verified,
p_rec.date_of_birth,
p_rec.email_address,
p_rec.employee_number,
p_rec.expense_check_send_to_address,
p_rec.first_name,
p_rec.full_name,
p_rec.known_as,
p_rec.marital_status,
p_rec.middle_names,
p_rec.nationality,
p_rec.national_identifier,
p_rec.previous_last_name,
p_rec.registered_disabled_flag,
p_rec.sex,
p_rec.title,
p_rec.vendor_id,
p_rec.request_id,
p_rec.program_application_id,
p_rec.program_id,
p_rec.program_update_date,
p_rec.attribute_category,
p_rec.attribute1,
p_rec.attribute2,
p_rec.attribute3,
p_rec.attribute4,
p_rec.attribute5,
p_rec.attribute6,
p_rec.attribute7,
p_rec.attribute8,
p_rec.attribute9,
p_rec.attribute10,
p_rec.attribute11,
p_rec.attribute12,
p_rec.attribute13,
p_rec.attribute14,
p_rec.attribute15,
p_rec.attribute16,
p_rec.attribute17,
p_rec.attribute18,
p_rec.attribute19,
p_rec.attribute20,
p_rec.attribute21,
p_rec.attribute22,
p_rec.attribute23,
p_rec.attribute24,
p_rec.attribute25,
p_rec.attribute26,
p_rec.attribute27,
p_rec.attribute28,
p_rec.attribute29,
p_rec.attribute30,
p_rec.per_information_category,
p_rec.per_information1,
p_rec.per_information2,
p_rec.per_information3,
p_rec.per_information4,
p_rec.per_information5,
p_rec.per_information6,
p_rec.per_information7,
p_rec.per_information8,
p_rec.per_information9,
p_rec.per_information10,
p_rec.per_information11,
p_rec.per_information12,
p_rec.per_information13,
p_rec.per_information14,
p_rec.per_information15,
p_rec.per_information16,
p_rec.per_information17,
p_rec.per_information18,
p_rec.per_information19,
p_rec.per_information20,
-- p_rec.object_version_number,
l_ovn, -- BUG4081676
p_rec.suffix,
p_rec.DATE_OF_DEATH ,
p_rec.BACKGROUND_CHECK_STATUS ,
p_rec.BACKGROUND_DATE_CHECK ,
p_rec.BLOOD_TYPE ,
p_rec.CORRESPONDENCE_LANGUAGE ,
p_rec.FAST_PATH_EMPLOYEE ,
p_rec.FTE_CAPACITY ,
p_rec.HOLD_APPLICANT_DATE_UNTIL ,
p_rec.HONORS ,
p_rec.INTERNAL_LOCATION ,
p_rec.LAST_MEDICAL_TEST_BY ,
p_rec.LAST_MEDICAL_TEST_DATE ,
p_rec.MAILSTOP ,
p_rec.OFFICE_NUMBER ,
p_rec.ON_MILITARY_SERVICE ,
p_rec.ORDER_NAME ,
p_rec.PRE_NAME_ADJUNCT ,
p_rec.PROJECTED_START_DATE ,
p_rec.REHIRE_AUTHORIZOR ,
p_rec.REHIRE_RECOMMENDATION ,
p_rec.RESUME_EXISTS ,
p_rec.RESUME_LAST_UPDATED ,
p_rec.SECOND_PASSPORT_EXISTS ,
p_rec.STUDENT_STATUS ,
p_rec.WORK_SCHEDULE ,
p_rec.PER_INFORMATION21 ,
p_rec.PER_INFORMATION22 ,
p_rec.PER_INFORMATION23 ,
p_rec.PER_INFORMATION24 ,
p_rec.PER_INFORMATION25 ,
p_rec.PER_INFORMATION26 ,
p_rec.PER_INFORMATION27 ,
p_rec.PER_INFORMATION28 ,
p_rec.PER_INFORMATION29 ,
p_rec.PER_INFORMATION30 ,
p_rec.REHIRE_REASON ,
p_rec.BENEFIT_GROUP_ID ,
p_rec.RECEIPT_OF_DEATH_CERT_DATE ,
p_rec.COORD_BEN_MED_PLN_NO ,
p_rec.COORD_BEN_NO_CVG_FLAG ,
p_rec.COORD_BEN_MED_EXT_ER,
p_rec.COORD_BEN_MED_PL_NAME,
p_rec.COORD_BEN_MED_INSR_CRR_NAME,
p_rec.COORD_BEN_MED_INSR_CRR_IDENT,
p_rec.COORD_BEN_MED_CVG_STRT_DT,
p_rec.COORD_BEN_MED_CVG_END_DT ,
p_rec.USES_TOBACCO_FLAG ,
p_rec.DPDNT_ADOPTION_DATE ,
p_rec.DPDNT_VLNTRY_SVCE_FLAG ,
p_rec.ORIGINAL_DATE_OF_HIRE ,
p_rec.town_of_birth ,
p_rec.region_of_birth ,
p_rec.country_of_birth ,
p_rec.global_person_id ,
p_rec.party_id ,
p_rec.npw_number,
p_current_npw_flag,
p_rec.local_name,
p_rec.global_name,
p_rec.created_by,
p_rec.creation_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login
);
END Insert_Person_Rec;
PROCEDURE Update_PER_PTU_to_EX_APL
(p_business_group_id IN number
,p_person_id IN number
,p_effective_date IN date
,p_person_type_id IN number -- EX_APL type
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy DATE
)
IS
--
cursor csr_get_person_details(cp_person_id number, cp_effective_date date)
IS
select *
from per_all_people_f peo
where person_id = cp_person_id
and (effective_start_date >= cp_effective_date
OR
cp_effective_date between effective_start_date
and effective_end_date)
order by peo.effective_start_date ASC
for update of person_type_id;
select ppt.system_person_type
from per_person_types ppt
where ppt.person_type_id = cp_person_type_id;
select ptu.person_type_id, ppt.system_person_type
,ptu.effective_start_date, ptu.effective_end_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = cp_person_id
and cp_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type in ('APL','EX_APL')
order by effective_start_date ASC;
l_proc constant varchar2(100) := g_package||'Update_PER_PTU_to_EX_APL';
Update_Person_Rec
(p_person_id => l_person_rec.person_id
,p_effective_start_date => l_person_rec.effective_start_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => l_person_rec.applicant_number
,p_current_emp_apl_flag => l_current_emp_or_apl_flag
,p_current_apl_flag => l_current_applicant_flag
,p_object_version_number => l_ovn);
UPDATE per_all_people_f
set effective_end_date = l_effective_date -1
where person_id = l_person_rec.person_id
and effective_start_date = l_person_rec.effective_start_date
and effective_end_date = l_person_rec.effective_end_date;
Insert_Person_Rec
(p_rec => l_person_rec
,p_person_id => p_person_id
,p_effective_start_date => l_effective_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => l_person_rec.applicant_number
,p_current_emp_apl_flag => l_current_emp_or_apl_flag
,p_current_apl_flag => l_current_applicant_flag
,p_current_employee_flag => l_person_rec.current_employee_flag
,p_current_npw_flag => l_person_rec.current_npw_flag
,p_object_version_number => l_ovn);
Update_Person_Rec
(p_person_id => l_person_rec.person_id
,p_effective_start_date => l_person_rec.effective_start_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => l_person_rec.applicant_number
,p_current_emp_apl_flag => l_current_emp_or_apl_flag
,p_current_apl_flag => l_current_applicant_flag
,p_object_version_number => l_ovn);
,p_datetrack_update_mode => hr_api.g_update_override
);
,p_datetrack_update_mode => hr_api.g_update
);
end Update_PER_PTU_to_EX_APL;
PROCEDURE Update_PER_PTU_Records
(p_business_group_id IN number
,p_person_id IN number
,p_effective_date IN date
,p_applicant_number IN varchar2
,p_APL_person_type_id IN number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy DATE
,p_per_object_version_number in out nocopy number -- BUG4081676
)
IS
--
cursor csr_get_person_details(cp_person_id number, cp_effective_date date)
IS
select *
from per_all_people_f peo
where person_id = cp_person_id
and (effective_start_date >= cp_effective_date
OR
cp_effective_date between effective_start_date
and effective_end_date)
order by peo.effective_start_date ASC
for update of person_type_id;
select ppt.system_person_type
from per_person_types ppt
where ppt.person_type_id = cp_person_type_id;
select ptu.person_type_id, ppt.system_person_type
,ptu.effective_start_date, ptu.effective_end_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = cp_person_id
and (cp_effective_date between ptu.effective_start_date
and ptu.effective_end_date
or
effective_start_date > cp_effective_date)
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type in ('APL','EX_APL')
order by effective_start_date ASC;
l_proc constant varchar2(100) := g_package||'Update_PER_PTU_Records';
select object_version_number
from per_all_people_f
where person_id = p_person_id
and effective_start_date = l_new_effective_date
and effective_end_date = l_per_effective_end_date;
Insert_Person_Rec
(p_rec => l_person_rec
,p_person_id => p_person_id
,p_effective_start_date => l_effective_date
,p_effective_end_date => l_person_rec.effective_start_date - 1
,p_person_type_id => l_ptu_person_type_id
,p_applicant_number => p_applicant_number
,p_current_emp_apl_flag => 'Y'
,p_current_apl_flag => 'Y'
,p_current_employee_flag => NULL
,p_current_npw_flag => NULL
,p_object_version_number => l_ovn); -- BUG4081676
Update_Person_Rec
(p_person_id => l_person_rec.person_id
,p_effective_start_date => l_person_rec.effective_start_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => l_applicant_number
,p_current_emp_apl_flag => 'Y'
,p_current_apl_flag => 'Y'
,p_object_version_number => l_ovn); -- BUG4081676
Update_Person_Rec
(p_person_id => l_person_rec.person_id
,p_effective_start_date => l_person_rec.effective_start_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => p_applicant_number
,p_current_emp_apl_flag => 'Y'
,p_current_apl_flag => 'Y'
,p_object_version_number => l_ovn); -- BUG4081676
UPDATE per_all_people_f
set effective_end_date = l_effective_date -1
where person_id = l_person_rec.person_id
and effective_start_date = l_person_rec.effective_start_date
and effective_end_date = l_person_rec.effective_end_date;
Insert_Person_Rec
(p_rec => l_person_rec
,p_person_id => p_person_id
,p_effective_start_date => l_effective_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => p_applicant_number
,p_current_emp_apl_flag => 'Y'
,p_current_apl_flag => 'Y'
,p_current_employee_flag => l_person_rec.current_employee_flag
,p_current_npw_flag => l_person_rec.current_npw_flag
,p_object_version_number => l_ovn); -- BUG4081676
Update_Person_Rec
(p_person_id => l_person_rec.person_id
,p_effective_start_date => l_person_rec.effective_start_date
,p_effective_end_date => l_person_rec.effective_end_date
,p_person_type_id => l_new_person_type_id
,p_applicant_number => l_applicant_number
,p_current_emp_apl_flag => 'Y'
,p_current_apl_flag => 'Y'
,p_object_version_number => l_ovn); -- BUG4081676
,p_datetrack_delete_mode => hr_api.g_future_change
);
,p_datetrack_delete_mode => hr_api.g_future_change
);
,p_datetrack_delete_mode => hr_api.g_future_change
);
,p_datetrack_update_mode => hr_api.g_update
);
,p_datetrack_update_mode => hr_api.g_update_override
);
,p_datetrack_update_mode => hr_api.g_update
);
end Update_PER_PTU_Records;
select *
from per_all_people_f peo
where person_id = cp_person_id
and (cp_ex_apl_date between effective_start_date
and effective_end_date -- becomes ex-apl on this date
or cp_apl_date between effective_start_date
and effective_end_date -- is apl on this date
)
order by peo.effective_start_date ASC;
select ppt.system_person_type
from per_person_types ppt
where ppt.person_type_id = cp_person_type_id;
select ptu.person_type_id, ppt.system_person_type
,ptu.effective_start_date, ptu.effective_end_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = cp_person_id
and cp_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type in ('APL','EX_APL')
order by effective_start_date ASC;
UPDATE per_all_people_f
SET person_type_id = p_person_type
WHERE person_id = p_ex_apl_rec.person_id
AND effective_start_date = p_ex_apl_rec.effective_start_date
AND effective_end_date = p_ex_apl_rec.effective_end_date;
UPDATE per_all_people_f
SET effective_end_date = p_ex_apl_date - 1
WHERE person_id = p_ex_apl_rec.person_id
AND effective_start_date = p_ex_apl_rec.effective_start_date
AND effective_end_date = p_ex_apl_rec.effective_end_date;
Insert_Person_Rec(
p_rec => p_ex_apl_rec
,p_person_id => p_ex_apl_rec.person_id
,p_effective_start_date => p_ex_apl_date
,p_effective_end_date => p_apl_date - 1
,p_person_type_id => p_person_type
,p_applicant_number => p_ex_apl_rec.applicant_number
,p_current_emp_apl_flag => p_current_emp_apl_flag
,p_current_apl_flag => p_current_apl_flag
,p_current_employee_flag => p_ex_apl_rec.current_employee_flag
,p_current_npw_flag => p_ex_apl_rec.current_npw_flag
,p_object_version_number => p_object_version_number -- BUG4081676
);
if p_mode = 'UPDATE_CHANGE_INSERT' then
-- insert APL record as of p_apl_date
Insert_Person_Rec(
p_rec => p_ex_apl_rec
,p_person_id => p_ex_apl_rec.person_id
,p_effective_start_date => p_apl_date
,p_effective_end_date => p_ex_apl_rec.effective_end_date
,p_person_type_id => p_ex_apl_rec.person_type_id
,p_applicant_number => p_ex_apl_rec.applicant_number
,p_current_emp_apl_flag => p_ex_apl_rec.current_emp_or_apl_flag
,p_current_apl_flag => p_ex_apl_rec.current_applicant_flag
,p_current_employee_flag => p_ex_apl_rec.current_employee_flag
,p_current_npw_flag => p_ex_apl_rec.current_npw_flag
,p_object_version_number => p_object_version_number -- BUG4081676
);
elsif p_mode = 'UPDATE_OVERRIDE' then
-- insert APL record as of p_apl_date
UPDATE per_all_people_f
SET effective_start_date = p_apl_date
WHERE person_id = p_apl_rec.person_id
AND effective_start_date = p_apl_rec.effective_start_date
AND effective_end_date = p_apl_rec.effective_end_date;
Upd_person(p_mode => 'UPDATE_CHANGE_INSERT'
,p_ex_apl_rec => l_ex_per_rec
,p_apl_rec => NULL
,p_ex_apl_date => p_ex_apl_effective_date
,p_apl_date => p_apl_effective_date
,p_person_type => l_new_person_type_id
,p_current_emp_apl_flag => l_current_emp_or_apl_flag
,p_current_apl_flag => l_current_applicant_flag
,p_object_version_number => l_ovn -- BUG4081676
);
Upd_person(p_mode => 'UPDATE_OVERRIDE'
,p_ex_apl_rec => l_ex_per_rec
,p_apl_rec => l_apl_per_rec
,p_ex_apl_date => p_ex_apl_effective_date
,p_apl_date => p_apl_effective_date
,p_person_type => l_new_person_type_id
,p_current_emp_apl_flag => l_current_emp_or_apl_flag
,p_current_apl_flag => l_current_applicant_flag
,p_object_version_number => l_ovn -- BUG4081676
);
,p_datetrack_update_mode => hr_api.g_update_change_insert
);
,p_datetrack_update_mode => hr_api.g_update
);
,p_datetrack_update_mode => hr_api.g_update
);
PROCEDURE Update_APL_Assignments
(p_business_group_id IN number
,p_old_application_id IN number
,p_new_application_id IN number
)
IS
BEGIN
hr_utility.trace('Update APL asg belonging to future applications');
UPDATE per_all_assignments_f
set application_id = p_new_application_id
where business_group_id = p_business_group_id
and application_id is not null
and application_id = p_old_application_id;
END Update_APL_Assignments;
select application_id, date_received, object_version_number
from per_applications
where person_id = cp_person_id
and date_received > cp_effective_date
order by date_received asc;
select application_id, date_received, object_version_number
from per_applications
where person_id = cp_person_id
and date_received <= cp_effective_date
and nvl(date_end,hr_api.g_eot) >= cp_effective_date;
select application_id, date_received, object_version_number
from per_applications
where person_id = cp_person_id
and date_end = cp_effective_date-1;
hr_utility.trace('Insert brand new application');
Update_APL_assignments(p_business_group_id, l_del_fut_apl_id,l_future_apl_id);
Update_APL_assignments(p_business_group_id, l_future_apl_id,l_current_apl_id);
Update_APL_assignments(p_business_group_id, l_del_fut_apl_id,l_current_apl_id);
select application_id
from per_applications
where person_id = p_person_id
and date_received > p_effective_date
order by date_received asc;
select application_id
from per_applications
where person_id = p_person_id
and date_received < p_effective_date
and nvl(date_end,hr_api.g_eot) >= p_effective_date;
select application_id
from per_applications
where person_id = p_person_id
and date_end = p_effective_date-1;
select 'Y'
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.effective_start_date > p_effective_date
and paf.assignment_type = 'A'
and paf.application_id = p_application_id;
select ppf.business_group_id
,ppf.employee_number
,ppf.npw_number
,ppf.date_of_birth
,ppf.party_id
,ppf.effective_start_date
,ppt.system_person_type
from per_person_types ppt
,per_people_f ppf
where ppf.person_id = p_person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.business_group_id + 0 = ppf.business_group_id
and (l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date or ppf.effective_start_date > l_effective_date)
order by ppf.effective_start_date ASC;
select organization_id
,legislation_code
,default_start_time
,default_end_time
,fnd_number.canonical_to_number(working_hours)
,frequency
from per_business_groups
where business_group_id = l_business_group_id;
select recruiter_id
,grade_id
,position_id
,job_id
,location_id
,people_group_id
,organization_id -- added org id to cursor. thayden 7/10.
,business_group_id -- added business_group_id to cursor lma 7/11
from per_vacancies
where vacancy_id = p_vacancy_id;
SELECT null
FROM per_all_people_f
WHERE person_id = cp_person_id
AND (effective_start_date > cp_termination_date
OR
cp_termination_date between effective_start_date
and effective_end_date)
for update nowait;
SELECT null
FROM per_person_type_usages_f ptu
,per_person_types ppt
WHERE person_id = cp_person_id
AND (effective_start_date > cp_termination_date
OR
cp_termination_date between effective_start_date
and effective_end_date)
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('APL','EX_APL')
for update nowait;
Update_PER_PTU_Records
(p_business_group_id => l_business_group_id
,p_person_id => p_person_id
,p_effective_date => l_effective_date
,p_applicant_number => l_applicant_number
,P_APL_person_type_id => l_person_type_id
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_per_object_version_number => l_per_object_version_number --BUG4081676
);