[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_person(p_rec in out nocopy per_all_people_f%rowtype) is
--
l_effective_start_date date;
l_proc varchar2(80) := g_package||'update_person';
hr_person_api.update_person
(p_effective_date => p_rec.effective_start_date,
p_datetrack_update_mode => 'CORRECTION',
p_person_id => p_rec.person_id,
p_object_version_number => p_rec.object_version_number,
p_employee_number => p_rec.employee_number,
p_party_id => p_rec.party_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment_id,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning,
p_orig_hire_warning => l_orig_hire_warning);
update per_all_people_f
set party_id = p_rec.party_id
where person_id = p_rec.person_id
and p_rec.effective_start_date
between effective_start_date
and effective_end_date;
end update_person;
procedure update_child_tables(p_rec in out nocopy per_all_people_f%rowtype) is
--
l_proc varchar2(80) := g_package||'update_child_tables';
select *
from per_competence_elements
where person_id = p_rec.person_id;
select /*+ INDEX(per_events) */ *
from per_events
where assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_rec.person_id);
select *
from per_addresses
where person_id = p_rec.person_id;
select *
from per_phones
where parent_id = p_rec.person_id
and parent_table = 'PER_ALL_PEOPLE_F';
select *
from per_qualifications
where person_id = p_rec.person_id;
select *
from per_establishment_attendances
where person_id = p_rec.person_id;
select qua.*
from per_qualifications qua,
per_establishment_attendances esa
where esa.attendance_id = qua.attendance_id
and esa.person_id = p_rec.person_id;
select *
from per_previous_employers
where person_id = p_rec.person_id;
update per_competence_elements
set party_id = p_rec.party_id
where competence_element_id = l_competences.competence_element_id;
hr_competence_element_api.update_competence_element
(p_competence_element_id => l_competences.competence_element_id,
p_object_version_number => l_competences.object_version_number,
p_effective_date => p_rec.effective_start_date,
p_party_id => p_rec.party_id);
update per_events
set party_id = p_rec.party_id
where event_id = l_events.event_id;
per_events_api.update_event
(p_event_id => l_events.event_id,
p_party_id => p_rec.party_id,
p_object_version_number => l_events.object_version_number);
hr_person_address_api.update_person_address
(p_effective_date => p_rec.effective_start_date,
p_address_id => l_addresses.address_id,
p_party_id => p_rec.party_id,
p_object_version_number => l_addresses.object_version_number);
update per_addresses
set party_id = p_rec.party_id
where address_id = l_addresses.address_id;
hr_phone_api.update_phone
(p_phone_id => l_phones.phone_id,
p_object_version_number => l_phones.object_version_number,
p_party_id => p_rec.party_id,
p_effective_date => p_rec.effective_start_date);
update per_phones
set party_id = p_rec.party_id
where phone_id = l_phones.phone_id;
update per_qualifications
set party_id = p_rec.party_id
where qualification_id = l_qualifications.qualification_id;
update per_qualifications
set party_id = p_rec.party_id
where qualification_id = l_qualifications.qualification_id;
update per_establishment_attendances
set party_id = p_rec.party_id
where attendance_id = l_establishment_attendances.attendance_id;
update per_previous_employers
set party_id = p_rec.party_id
where previous_employer_id = l_prev_employers.previous_employer_id;
end update_child_tables;
select legislation_code
into l_legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
select *
from per_all_people_f
where party_id is null
and mod(person_id,p_number_of_workers) = p_current_worker-1
and effective_end_date = hr_api.g_eot;
select *
from per_all_people_f
where party_id is null
and effective_end_date <> hr_api.g_eot
and person_id = p_person_id;
update_person(p_rec => l_person);
update_person(p_rec => l_old_person);
update_child_tables(p_rec => l_person);
procedure create_update_contact_point
(p_rec in out nocopy per_all_people_f%rowtype) is
--
l_proc varchar2(80) := g_package||'create_update_contact_point';
SELECT email_address, last_update_date,contact_point_id,status
FROM hz_contact_points hcp
WHERE hcp.contact_point_type = 'EMAIL'
and hcp.owner_table_name = 'HZ_PARTIES'
and hcp.owner_table_id = p_rec.party_id
order by last_update_date desc,contact_point_id desc;
select max(object_version_number)
into l_object_version_number
from hz_contact_points hcp
where hcp.contact_point_id = l_c1.contact_point_id;
hz_contact_point_v2pub.update_contact_point
(
--p_api_version => 1.0,
p_contact_point_rec => l_contact_point_rec,
p_email_rec => l_email_rec,
-- p_last_update_date => l_c1.last_update_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_web_rec => l_web_rec,
p_edi_rec => l_edi_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec,
p_init_msg_list => l_init_msg_list,
p_object_version_number => l_object_version_number
);
select last_update_date
into l_c1.last_update_date
from hz_contact_points
where contact_point_id = l_c1.contact_point_id;
hz_contact_point_v2pub.update_contact_point
(
--p_api_version => 1.0,
p_contact_point_rec => l_contact_point_rec,
p_email_rec => l_email_rec,
-- p_last_update_date => l_c1.last_update_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_web_rec => l_web_rec,
p_edi_rec => l_edi_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec,
p_init_msg_list => l_init_msg_list,
p_object_version_number => l_object_version_number
);
hz_contact_point_v2pub.update_contact_point
(
--p_api_version => 1.0,
p_contact_point_rec => l_contact_point_rec,
p_email_rec => l_email_rec,
-- p_last_update_date => l_c1.last_update_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_web_rec => l_web_rec,
p_edi_rec => l_edi_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec,
p_init_msg_list => l_init_msg_list,
p_object_version_number => l_object_version_number
);
end create_update_contact_point;
SELECT typ.system_person_type
FROM per_person_types typ
,per_person_type_usages_f ptu
WHERE typ.person_type_id = ptu.person_type_id
--AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id
ORDER BY DECODE(typ.system_person_type
,'EMP' ,1
,'CWK' ,2
,'APL' ,3
,'EX_EMP',4
,'EX_CWK',5
,'EX_APL',6
,7
);
SELECT typ.system_person_type
FROM per_all_people_f ppf
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ppf.party_id = p_party_id
AND ppf.effective_end_date = p_effective_date
AND ppf.person_id = ptu.person_id
AND typ.person_type_id = ptu.person_type_id
--Remove this as we want to view all PTU types.
--AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
AND ptu.effective_end_date = p_effective_date
ORDER BY DECODE(typ.system_person_type
,'EMP' ,1
,'CWK' ,2
,'APL' ,3
,'EX_EMP',4
,'EX_CWK',5
,'EX_APL',6
,7
);
SELECT typ.system_person_type
FROM per_all_people_f ppf
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ppf.person_id = p_person_id
AND ppf.effective_end_date = p_effective_date
AND ppf.person_id = ptu.person_id
AND typ.person_type_id = ptu.person_type_id
--Remove this as we want to view all PTU types.
--AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
AND ptu.effective_end_date = p_effective_date
ORDER BY DECODE(typ.system_person_type
,'EMP' ,1
,'CWK' ,2
,'APL' ,3
,'EX_EMP',4
,'EX_CWK',5
,'EX_APL',6
,7
);
update PER_ADDRESSES
set party_id = null
where party_id = p_party_id;
update PER_COMPETENCE_ELEMENTS
set party_id = null
where party_id = p_party_id;
update PER_ESTABLISHMENT_ATTENDANCES
set party_id = null
where party_id = p_party_id;
update PER_EVENTS
set party_id = null
where party_id = p_party_id;
update PER_PHONES
set party_id = null
where party_id = p_party_id;
update PER_PREVIOUS_EMPLOYERS
set party_id = null
where party_id = p_party_id;
update PER_QUALIFICATIONS
set party_id = null
where party_id = p_party_id;
update PER_ALL_PEOPLE_F
set party_id = null
where party_id = p_party_id;
select pce.party_id
from per_competence_elements pce,
hr_tca_party_unmerge punm
where pce.party_id = punm.party_id
and punm.status = 'PURGE';
select eve.party_id
from per_events eve,
hr_tca_party_unmerge punm
where eve.party_id = punm.party_id
and punm.status = 'PURGE';
select addr.party_id
from per_addresses addr,
hr_tca_party_unmerge punm
where addr.party_id = punm.party_id
and punm.status = 'PURGE';
select phn.party_id
from per_phones phn,
hr_tca_party_unmerge punm
where phn.party_id = punm.party_id
and punm.status = 'PURGE';
select qua.party_id
from per_qualifications qua,
hr_tca_party_unmerge punm
where qua.party_id = punm.party_id
and punm.status = 'PURGE';
select esta.party_id
from per_establishment_attendances esta,
hr_tca_party_unmerge punm
where esta.party_id = punm.party_id
and punm.status = 'PURGE';
select pemp.party_id
from per_previous_employers pemp,
hr_tca_party_unmerge punm
where pemp.party_id = punm.party_id
and punm.status = 'PURGE';
select papf.party_id
from per_all_people_f papf,
hr_tca_party_unmerge punm
where papf.party_id = punm.party_id
and punm.status = 'PURGE';
update per_competence_elements
set party_id = null
where party_id = t_pce_party_id(i);
t_pce_party_id.delete;
update per_events
set party_id = null
where party_id = t_eve_party_id(i);
t_eve_party_id.delete;
update per_addresses
set party_id = null
where party_id = t_add_party_id(i);
t_add_party_id.delete;
update per_phones
set party_id = null
where party_id = t_phn_party_id(i);
t_phn_party_id.delete;
update per_qualifications
set party_id = null
where party_id = t_qua_party_id(i);
t_qua_party_id.delete;
update per_establishment_attendances
set party_id = null
where party_id = t_esta_party_id(i);
t_esta_party_id.delete;
update per_previous_employers
set party_id = null
where party_id = t_pemp_party_id(i);
t_pemp_party_id.delete;
update per_all_people_f
set party_id = null
where party_id = t_papf_party_id(i);
t_papf_party_id.delete;
update PER_ADDRESSES
set party_id = p_party_id
where person_id = p_person_id;
update PER_COMPETENCE_ELEMENTS
set party_id = p_party_id
where person_id = p_person_id;
update PER_ESTABLISHMENT_ATTENDANCES
set party_id = p_party_id
where person_id = p_person_id;
select assignment_id
bulk collect into l_assignment_id
from per_all_assignments_f
where person_id = p_person_id;
update PER_EVENTS
set party_id = p_party_id
where assignment_id = l_assignment_id(i);
/*update PER_EVENTS
set party_id = p_party_id
where assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id);*/
update PER_PHONES
set party_id = p_party_id
-- There is no person_id column in per_phones table
-- where person_id = p_person_id;
update PER_PREVIOUS_EMPLOYERS
set party_id = p_party_id
where person_id = p_person_id;
update PER_QUALIFICATIONS
set party_id = p_party_id
where person_id = p_person_id;
update PER_ALL_PEOPLE_F
set party_id = p_party_id
where person_id = p_person_id;
delete from hr_purge_parties_gt;
insert into hr_purge_parties_gt (party_id) values (p_party_id);
l_subset_sql varchar2(1000) := 'party_id in (select party_id
from hr_purge_parties_gt)';
select party_id
from hr_purge_parties_gt;
/* Insert the details into HZ_PURGE_BATCHES.
*/
hr_utility.set_location(l_proc,30);
select hz_purge_batches_s.nextval
into l_batchid
from sys.dual;
insert into hz_purge_batches (batch_id,
batch_name,
subset_sql,
creation_date,
created_by,
last_update_date,
last_updated_by)
values (l_batchid,
'HR Contact Purge - '||l_batchid,
l_subset_sql,
sysdate,
fnd_global.login_id,
sysdate,
fnd_global.login_id);
select distinct p.person_id, p.party_id
from per_all_people_f p
where p.rowid
between p_start_rowid and p_end_rowid
and p.party_id is not null
and not exists (select null
from hr_tca_party_unmerge ptyun
where ptyun.party_id = p.party_id);
SELECT typ.system_person_type
FROM per_all_people_f ppf
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ppf.party_id = p_party_id
AND ppf.effective_end_date = p_effective_date
AND ppf.person_id = ptu.person_id
AND typ.person_type_id = ptu.person_type_id
--Remove this as we want to view all PTU types.
--AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
AND ptu.effective_end_date = p_effective_date
ORDER BY DECODE(typ.system_person_type
,'EMP' ,1
,'CWK' ,2
,'APL' ,3
,'EX_EMP',4
,'EX_CWK',5
,'EX_APL',6
,7
);
INSERT INTO hr_tca_party_unmerge (party_id,status) VALUES (t_party_id_notto_purge(i),'NOPURGE');
t_party_id_notto_purge.delete;
INSERT INTO hr_tca_party_unmerge (party_id,status) VALUES (t_party_id_to_purge(i),'PURGE');
t_party_id_to_purge.delete;
t_person_id.delete;
t_party_id.delete;
select count(party_id)
from hr_tca_party_unmerge
where status = 'PURGE';
select min(rowid),
max(rowid)
from per_all_people_f;
select release_name
from fnd_product_groups;
insert into hr_purge_parties_gt (PARTY_ID)
select distinct party_id
from hr_tca_party_unmerge
where status = 'PURGE';
update hr_tca_party_unmerge
set status = 'PURGE COMPLETED'
where status = 'PURGE';
select count(party_id)
from hr_tca_party_unmerge
where status = 'PURGE';
insert into hr_purge_parties_gt (PARTY_ID)
select distinct party_id
from hr_tca_party_unmerge
where status = 'PURGE';
update hr_tca_party_unmerge
set status = 'PURGE COMPLETED'
where status = 'PURGE';
select width
from fnd_columns
where column_name = col_name
and application_id = fndApplicationIdIn
and table_id = (select table_id
from fnd_tables
where table_name = tab_name
and application_id = fndApplicationIdIn); --modified for bug 6931585
select null
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and enabled_flag = 'Y'
and view_application_id = 222
and trunc(sysdate) between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID)
and language = userenv('LANG');
select release_name
from fnd_product_groups;
update hz_parties
set orig_system_reference = p_rec.party_id
where party_id = p_rec.party_id
and substr(orig_system_reference,1,4) = 'PER:';
INSERT INTO hr_tca_party_unmerge (party_id,status)
VALUES (p_rec.party_id,'PURGE');
update_tca_person(p_rec => p_rec,
p_overwrite_data => 'N');
create_update_contact_point(p_rec => p_rec);
procedure update_tca_person
(p_rec in out nocopy per_all_people_f%rowtype,
p_overwrite_data in varchar2 default 'Y') is
--
l_proc varchar2(80) := g_package||'.update_tca_person';
l_party_last_update_date date;
select 1
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and enabled_flag = 'Y'
and view_application_id = 222
and trunc(sysdate) between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and SECURITY_GROUP_ID =
fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID)
and language = userenv('LANG');
hr_utility.set_location('UPDATE_TCA_PERSON Before middle Name ###'||p_rec.middle_names||'***',99);
select max(last_update_date)
into l_party_last_update_date
from hz_parties
where party_id = p_rec.party_id;
select max(object_version_number)
into l_party_object_version_number
from hz_parties
where party_id = p_rec.party_id;
hz_party_v2pub.update_person
(
--p_api_version => 1.0,
p_init_msg_list => 'F',
--p_commit => 'F',
p_person_rec => l_person_rec,
--p_party_last_update_date => l_party_last_update_date,
p_party_object_version_number => l_party_object_version_number, --Added for bug# 2648797
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
--p_validation_level => 100
);
fnd_message.set_token('PROCEDURE','per_hrtca_merge.update_tca_person');
update hz_parties
set orig_system_reference = 'PER:'||p_rec.person_id
where party_id = p_rec.party_id
and substr(orig_system_reference,1,4) <> 'PER:';
select 1 into l_dummy from dual;
select 1 into l_dummy from dual;
create_update_contact_point(p_rec => p_rec);
select *
from per_all_people_f
where effective_start_date <= p_rec.effective_end_date
and effective_end_date >= p_rec.effective_start_date
and person_id <> p_rec.person_id
and party_id = p_rec.party_id
order by person_id, effective_start_date;
select *
from per_all_people_f
where person_id = cp_person_id
and cp_effective_date between effective_start_date and effective_end_date;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
select person_id
into l_dummy_lock_id
from per_all_people_f
where l_ref_effective_start_date
between effective_start_date
and effective_end_date
and person_id = l_ref_person_id
for update nowait;
l_copy_rec.resume_last_updated :=
propagate_value
(l_person(l_count).resume_last_updated,
p_rec.resume_last_updated,
p_overwrite_data);
update per_all_people_f
set last_name = p_rec.last_name,
full_name = l_full_name,
date_of_birth = l_copy_rec.date_of_birth,
first_name = l_copy_rec.first_name,
known_as = l_copy_rec.known_as,
marital_status = l_copy_rec.marital_status,
middle_names = l_copy_rec.middle_names,
nationality = l_copy_rec.nationality,
sex = l_copy_rec.sex,
title = l_copy_rec.title,
blood_type = l_copy_rec.blood_type,
correspondence_language = l_copy_rec.correspondence_language,
honors = l_copy_rec.honors,
pre_name_adjunct = l_copy_rec.pre_name_adjunct,
rehire_authorizor = l_copy_rec.rehire_authorizor,
rehire_recommendation = l_copy_rec.rehire_recommendation,
resume_exists = l_copy_rec.resume_exists,
resume_last_updated = l_copy_rec.resume_last_updated,
second_passport_exists = l_copy_rec.second_passport_exists,
student_status = l_copy_rec.student_status,
suffix = l_copy_rec.suffix,
date_of_death = l_copy_rec.date_of_death,
uses_tobacco_flag = l_copy_rec.uses_tobacco_flag,
town_of_birth = l_copy_rec.town_of_birth,
region_of_birth = l_copy_rec.region_of_birth,
country_of_birth = l_copy_rec.country_of_birth,
fast_path_employee = l_copy_rec.fast_path_employee,
email_address = l_copy_rec.email_address,
fte_capacity = l_copy_rec.fte_capacity,
previous_last_name = l_copy_rec.previous_last_name, -- bug fix 3598173.
order_name = l_order_name,
global_name = l_global_name,
local_name = l_local_name
where person_id = l_ref_person_id
and l_ref_effective_start_date
between effective_start_date
and effective_end_date;
elsif l_datetrack_mode = 'UPDATE' then
--
hr_utility.set_location('Updating Record in update mode',10);
update per_all_people_f
set effective_end_date = p_rec.effective_start_date-1
where person_id = l_ref_person_id
and l_ref_effective_start_date
between effective_start_date
and effective_end_date;
hr_utility.set_location('Getting max OVN in update mode',10);
hr_utility.set_location('inserting new record in update mode',10);
l_copy_rec.resume_last_updated :=
propagate_value
(l_person(l_count).resume_last_updated,
p_rec.resume_last_updated,
p_overwrite_data);
hr_utility.set_location('Before insert for update event',10);
insert into per_all_people_f
(person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
full_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,
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 ,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
global_name,
local_name,
npw_number, -- 5123559
current_npw_flag) -- 5123559)
-- ----------------------- +
VALUES
-- ----------------------- +
(l_person(l_count).person_id,
p_rec.effective_start_date,
hr_api.g_eot,
l_person_rec.business_group_id,
l_person_rec.person_type_id,
p_rec.last_name,
l_full_name,
l_person_rec.start_date,
l_person_rec.applicant_number,
l_person_rec.comment_id,
l_person_rec.current_applicant_flag,
l_person_rec.current_emp_or_apl_flag,
l_person_rec.current_employee_flag,
l_person_rec.date_employee_data_verified,
l_copy_rec.date_of_birth,
l_copy_rec.email_address,
l_person_rec.employee_number,
l_person_rec.expense_check_send_to_address,
l_copy_rec.first_name,
l_copy_rec.known_as,
l_copy_rec.marital_status,
l_copy_rec.middle_names,
l_copy_rec.nationality,
l_person_rec.national_identifier,
l_copy_rec.previous_last_name, -- Bug fix 3598173.
l_person_rec.registered_disabled_flag,
l_copy_rec.sex,
l_copy_rec.title,
l_person_rec.vendor_id,
l_person_rec.request_id,
l_person_rec.program_application_id,
l_person_rec.program_id,
l_person_rec.program_update_date,
l_person_rec.attribute_category,
l_person_rec.attribute1,
l_person_rec.attribute2,
l_person_rec.attribute3,
l_person_rec.attribute4,
l_person_rec.attribute5,
l_person_rec.attribute6,
l_person_rec.attribute7,
l_person_rec.attribute8,
l_person_rec.attribute9,
l_person_rec.attribute10,
l_person_rec.attribute11,
l_person_rec.attribute12,
l_person_rec.attribute13,
l_person_rec.attribute14,
l_person_rec.attribute15,
l_person_rec.attribute16,
l_person_rec.attribute17,
l_person_rec.attribute18,
l_person_rec.attribute19,
l_person_rec.attribute20,
l_person_rec.attribute21,
l_person_rec.attribute22,
l_person_rec.attribute23,
l_person_rec.attribute24,
l_person_rec.attribute25,
l_person_rec.attribute26,
l_person_rec.attribute27,
l_person_rec.attribute28,
l_person_rec.attribute29,
l_person_rec.attribute30,
l_person_rec.per_information_category,
l_person_rec.per_information1,
l_person_rec.per_information2,
l_person_rec.per_information3,
l_person_rec.per_information4,
l_person_rec.per_information5,
l_person_rec.per_information6,
l_person_rec.per_information7,
l_person_rec.per_information8,
l_person_rec.per_information9,
l_person_rec.per_information10,
l_person_rec.per_information11,
l_person_rec.per_information12,
l_person_rec.per_information13,
l_person_rec.per_information14,
l_person_rec.per_information15,
l_person_rec.per_information16,
l_person_rec.per_information17,
l_person_rec.per_information18,
l_person_rec.per_information19,
l_person_rec.per_information20,
l_person(l_count).object_version_number,
l_copy_rec.suffix,
l_copy_rec.DATE_OF_DEATH,
l_person_rec.BACKGROUND_CHECK_STATUS ,
l_person_rec.BACKGROUND_DATE_CHECK ,
l_copy_rec.BLOOD_TYPE,
l_copy_rec.CORRESPONDENCE_LANGUAGE,
l_copy_rec.FAST_PATH_EMPLOYEE,
l_copy_rec.FTE_CAPACITY,
l_person_rec.HOLD_APPLICANT_DATE_UNTIL ,
l_copy_rec.HONORS,
l_person_rec.INTERNAL_LOCATION ,
l_person_rec.LAST_MEDICAL_TEST_BY ,
l_person_rec.LAST_MEDICAL_TEST_DATE ,
l_person_rec.MAILSTOP ,
l_person_rec.OFFICE_NUMBER ,
l_person_rec.ON_MILITARY_SERVICE ,
l_ORDER_NAME ,
l_copy_rec.PRE_NAME_ADJUNCT,
l_person_rec.PROJECTED_START_DATE ,
l_copy_rec.REHIRE_AUTHORIZOR,
l_copy_rec.REHIRE_RECOMMENDATION,
l_copy_rec.RESUME_EXISTS,
l_copy_rec.RESUME_LAST_UPDATED,
l_copy_rec.SECOND_PASSPORT_EXISTS,
l_copy_rec.STUDENT_STATUS,
l_person_rec.WORK_SCHEDULE ,
l_person_rec.per_iNFORMATION21 ,
l_person_rec.per_iNFORMATION22 ,
l_person_rec.per_iNFORMATION23 ,
l_person_rec.per_iNFORMATION24 ,
l_person_rec.per_iNFORMATION25 ,
l_person_rec.per_iNFORMATION26 ,
l_person_rec.per_iNFORMATION27 ,
l_person_rec.per_iNFORMATION28 ,
l_person_rec.per_iNFORMATION29 ,
l_person_rec.per_iNFORMATION30 ,
l_person_rec.REHIRE_REASON ,
l_person_rec.BENEFIT_GROUP_ID ,
l_person_rec.RECEIPT_OF_DEATH_CERT_DATE ,
l_person_rec.COORD_BEN_MED_PLN_NO ,
l_person_rec.COORD_BEN_NO_CVG_FLAG ,
l_person_rec.COORD_BEN_MED_EXT_ER,
l_person_rec.COORD_BEN_MED_PL_NAME,
l_person_rec.COORD_BEN_MED_INSR_CRR_NAME,
l_person_rec.COORD_BEN_MED_INSR_CRR_IDENT,
l_person_rec.COORD_BEN_MED_CVG_STRT_DT,
l_person_rec.COORD_BEN_MED_CVG_END_DT ,
l_copy_rec.USES_TOBACCO_FLAG,
l_person_rec.DPDNT_ADOPTION_DATE ,
l_person_rec.DPDNT_VLNTRY_SVCE_FLAG ,
l_person_rec.ORIGINAL_DATE_OF_HIRE ,
l_copy_rec.town_of_birth,
l_copy_rec.region_of_birth,
l_copy_rec.country_of_birth,
l_person_rec.global_person_id ,
l_person_rec.party_id ,
l_person_rec.created_by,
l_person_rec.creation_date,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_global_name,
l_local_name,
l_person_rec.npw_number, -- 5123559
l_person_rec.current_npw_flag); -- 5123559
elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
--
hr_utility.set_location('updating record in update change insert mode',10);
update per_all_people_f
set effective_end_date = p_rec.effective_start_date-1
where person_id = l_person(l_count).person_id
and p_rec.effective_start_date
between effective_start_date
and effective_end_date;
hr_utility.set_location('getting max ovn in update change insert mode',10);
hr_utility.set_location('inserting record in update change insert mode',10);
l_copy_rec.resume_last_updated :=
propagate_value
(l_person(l_count).resume_last_updated,
p_rec.resume_last_updated,
p_overwrite_data);
insert into per_all_people_f
(person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
full_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,
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 ,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
global_name,
local_name)
-- ------------------------ +
VALUES
-- ------------------------ +
(l_person(l_count).person_id,
p_rec.effective_start_date,
l_person(l_count).effective_end_date,
l_person_rec.business_group_id,
l_person_rec.person_type_id,
p_rec.last_name,
l_full_name,
l_person_rec.start_date,
l_person_rec.applicant_number,
l_person_rec.comment_id,
l_person_rec.current_applicant_flag,
l_person_rec.current_emp_or_apl_flag,
l_person_rec.current_employee_flag,
l_person_rec.date_employee_data_verified,
l_copy_rec.date_of_birth,
l_copy_rec.email_address,
l_person_rec.employee_number,
l_person_rec.expense_check_send_to_address,
l_copy_rec.first_name,
l_copy_rec.known_as,
l_copy_rec.marital_status,
l_copy_rec.middle_names,
l_copy_rec.nationality,
l_person_rec.national_identifier,
l_copy_rec.previous_last_name, -- bug fix 3598173.
l_person_rec.registered_disabled_flag,
l_copy_rec.sex,
l_copy_rec.title,
l_person_rec.vendor_id,
l_person_rec.request_id,
l_person_rec.program_application_id,
l_person_rec.program_id,
l_person_rec.program_update_date,
l_person_rec.attribute_category,
l_person_rec.attribute1,
l_person_rec.attribute2,
l_person_rec.attribute3,
l_person_rec.attribute4,
l_person_rec.attribute5,
l_person_rec.attribute6,
l_person_rec.attribute7,
l_person_rec.attribute8,
l_person_rec.attribute9,
l_person_rec.attribute10,
l_person_rec.attribute11,
l_person_rec.attribute12,
l_person_rec.attribute13,
l_person_rec.attribute14,
l_person_rec.attribute15,
l_person_rec.attribute16,
l_person_rec.attribute17,
l_person_rec.attribute18,
l_person_rec.attribute19,
l_person_rec.attribute20,
l_person_rec.attribute21,
l_person_rec.attribute22,
l_person_rec.attribute23,
l_person_rec.attribute24,
l_person_rec.attribute25,
l_person_rec.attribute26,
l_person_rec.attribute27,
l_person_rec.attribute28,
l_person_rec.attribute29,
l_person_rec.attribute30,
l_person_rec.per_information_category,
l_person_rec.per_information1,
l_person_rec.per_information2,
l_person_rec.per_information3,
l_person_rec.per_information4,
l_person_rec.per_information5,
l_person_rec.per_information6,
l_person_rec.per_information7,
l_person_rec.per_information8,
l_person_rec.per_information9,
l_person_rec.per_information10,
l_person_rec.per_information11,
l_person_rec.per_information12,
l_person_rec.per_information13,
l_person_rec.per_information14,
l_person_rec.per_information15,
l_person_rec.per_information16,
l_person_rec.per_information17,
l_person_rec.per_information18,
l_person_rec.per_information19,
l_person_rec.per_information20,
l_person(l_count).object_version_number,
l_copy_rec.suffix,
l_copy_rec.DATE_OF_DEATH,
l_person_rec.BACKGROUND_CHECK_STATUS ,
l_person_rec.BACKGROUND_DATE_CHECK ,
l_copy_rec.BLOOD_TYPE,
l_copy_rec.CORRESPONDENCE_LANGUAGE,
l_copy_rec.FAST_PATH_EMPLOYEE,
l_copy_rec.FTE_CAPACITY,
l_person_rec.HOLD_APPLICANT_DATE_UNTIL ,
l_copy_rec.HONORS,
l_person_rec.INTERNAL_LOCATION ,
l_person_rec.LAST_MEDICAL_TEST_BY ,
l_person_rec.LAST_MEDICAL_TEST_DATE ,
l_person_rec.MAILSTOP ,
l_person_rec.OFFICE_NUMBER ,
l_person_rec.ON_MILITARY_SERVICE ,
l_ORDER_NAME ,
l_copy_rec.PRE_NAME_ADJUNCT,
l_person_rec.PROJECTED_START_DATE ,
l_copy_rec.REHIRE_AUTHORIZOR,
l_copy_rec.REHIRE_RECOMMENDATION,
l_copy_rec.RESUME_EXISTS,
l_copy_rec.RESUME_LAST_UPDATED,
l_copy_rec.SECOND_PASSPORT_EXISTS,
l_copy_rec.STUDENT_STATUS,
l_person_rec.WORK_SCHEDULE ,
l_person_rec.per_iNFORMATION21 ,
l_person_rec.per_iNFORMATION22 ,
l_person_rec.per_iNFORMATION23 ,
l_person_rec.per_iNFORMATION24 ,
l_person_rec.per_iNFORMATION25 ,
l_person_rec.per_iNFORMATION26 ,
l_person_rec.per_iNFORMATION27 ,
l_person_rec.per_iNFORMATION28 ,
l_person_rec.per_iNFORMATION29 ,
l_person_rec.per_iNFORMATION30 ,
l_person_rec.REHIRE_REASON ,
l_person_rec.BENEFIT_GROUP_ID ,
l_person_rec.RECEIPT_OF_DEATH_CERT_DATE ,
l_person_rec.COORD_BEN_MED_PLN_NO ,
l_person_rec.COORD_BEN_NO_CVG_FLAG ,
l_person_rec.COORD_BEN_MED_EXT_ER,
l_person_rec.COORD_BEN_MED_PL_NAME,
l_person_rec.COORD_BEN_MED_INSR_CRR_NAME,
l_person_rec.COORD_BEN_MED_INSR_CRR_IDENT,
l_person_rec.COORD_BEN_MED_CVG_STRT_DT,
l_person_rec.COORD_BEN_MED_CVG_END_DT ,
l_copy_rec.USES_TOBACCO_FLAG,
l_person_rec.DPDNT_ADOPTION_DATE ,
l_person_rec.DPDNT_VLNTRY_SVCE_FLAG ,
l_person_rec.ORIGINAL_DATE_OF_HIRE ,
l_copy_rec.town_of_birth,
l_copy_rec.region_of_birth,
l_copy_rec.country_of_birth,
l_person_rec.global_person_id ,
l_person_rec.party_id ,
l_person_rec.created_by,
l_person_rec.creation_date,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_global_name,
l_local_name);
hr_person_api.update_person
(p_effective_date => p_rec.effective_start_date,
p_datetrack_update_mode => l_datetrack_mode,
p_person_id => l_person(l_count).person_id,
p_object_version_number => l_person(l_count).object_version_number,
p_employee_number => l_person(l_count).employee_number,
p_last_name => p_rec.last_name,
p_date_of_birth => p_rec.date_of_birth,
p_first_name => p_rec.first_name,
p_known_as => p_rec.known_as,
p_marital_status => p_rec.marital_status,
p_middle_names => p_rec.middle_names,
p_nationality => p_rec.nationality,
p_sex => p_rec.sex,
p_title => p_rec.title,
p_blood_type => p_rec.blood_type,
p_correspondence_language => p_rec.correspondence_language,
p_honors => p_rec.honors,
p_pre_name_adjunct => p_rec.pre_name_adjunct,
p_rehire_authorizor => p_rec.rehire_authorizor,
p_rehire_recommendation => p_rec.rehire_recommendation,
p_resume_exists => p_rec.resume_exists,
p_resume_last_updated => p_rec.resume_last_updated,
p_second_passport_exists => p_rec.second_passport_exists,
p_student_status => p_rec.student_status,
p_suffix => p_rec.suffix,
p_date_of_death => p_rec.date_of_death,
p_uses_tobacco_flag => p_rec.uses_tobacco_flag,
p_town_of_birth => p_rec.town_of_birth,
p_region_of_birth => p_rec.region_of_birth,
p_country_of_birth => p_rec.country_of_birth,
p_fast_path_employee => p_rec.fast_path_employee,
p_email_address => p_rec.email_address,
p_fte_capacity => p_rec.fte_capacity,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment_id,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning,
p_orig_hire_warning => l_orig_hire_warning);
hr_utility.set_location('finding dt delete modes',10);
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_change_insert then
--
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
elsif l_update then
--
l_datetrack_mode := 'UPDATE';
if l_datetrack_mode = 'UPDATE' then
--
hr_utility.set_location('updating person in update mode',10);
update per_all_people_f
set effective_end_date = p_rec.effective_end_date
where person_id = l_person(l_count).person_id
and p_rec.effective_start_date
between effective_start_date
and effective_end_date;
hr_utility.set_location('getting max ovn in update mode',10);
hr_utility.set_location('inserting record in update mode',10);
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 ,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
global_name,
local_name)
select
l_person(l_count).person_id,
p_rec.effective_end_date+1,
hr_api.g_eot,
business_group_id,
person_type_id,
p_rec.last_name,
start_date,
applicant_number,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_employee_data_verified,
p_rec.date_of_birth,
p_rec.email_address,
employee_number,
expense_check_send_to_address,
p_rec.first_name,
full_name,
p_rec.known_as,
p_rec.marital_status,
p_rec.middle_names,
p_rec.nationality,
national_identifier,
p_rec.previous_last_name, -- Bug fix 3598173.
registered_disabled_flag,
p_rec.sex,
p_rec.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,
l_person(l_count).object_version_number,
p_rec.suffix,
p_rec.DATE_OF_DEATH ,
BACKGROUND_CHECK_STATUS ,
BACKGROUND_DATE_CHECK ,
p_rec.BLOOD_TYPE ,
p_rec.CORRESPONDENCE_LANGUAGE ,
p_rec.FAST_PATH_EMPLOYEE ,
p_rec.FTE_CAPACITY ,
HOLD_APPLICANT_DATE_UNTIL ,
p_rec.HONORS ,
INTERNAL_LOCATION ,
LAST_MEDICAL_TEST_BY ,
LAST_MEDICAL_TEST_DATE ,
MAILSTOP ,
OFFICE_NUMBER ,
ON_MILITARY_SERVICE ,
ORDER_NAME ,
p_rec.PRE_NAME_ADJUNCT ,
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 ,
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 ,
p_rec.USES_TOBACCO_FLAG ,
DPDNT_ADOPTION_DATE ,
DPDNT_VLNTRY_SVCE_FLAG ,
ORIGINAL_DATE_OF_HIRE ,
p_rec.town_of_birth ,
p_rec.region_of_birth ,
p_rec.country_of_birth ,
global_person_id ,
party_id ,
created_by,
creation_date,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
global_name,
local_name
from per_all_people_f
where person_id = l_person(l_count).person_id
and p_rec.effective_start_date-1
between effective_start_date
and effective_end_date;
elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
--
hr_utility.set_location('updating record in update change insert mode',10);
update per_all_people_f
set effective_end_date = p_rec.effective_end_date
where person_id = l_person(l_count).person_id
and p_rec.effective_start_date
between effective_start_date
and effective_end_date;
hr_utility.set_location('getting max ovn in update change insert mode',10);
hr_utility.set_location('inserting record in update change insert mode',10);
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 ,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
global_name,
local_name)
select
l_person(l_count).person_id,
p_rec.effective_end_date+1,
l_person(l_count).effective_end_date,
business_group_id,
person_type_id,
p_rec.last_name,
start_date,
applicant_number,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_employee_data_verified,
p_rec.date_of_birth,
p_rec.email_address,
employee_number,
expense_check_send_to_address,
p_rec.first_name,
full_name,
p_rec.known_as,
p_rec.marital_status,
p_rec.middle_names,
p_rec.nationality,
national_identifier,
p_rec.previous_last_name, -- bug fix 3598173
registered_disabled_flag,
p_rec.sex,
p_rec.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,
l_person(l_count).object_version_number,
p_rec.suffix,
p_rec.DATE_OF_DEATH ,
BACKGROUND_CHECK_STATUS ,
BACKGROUND_DATE_CHECK ,
p_rec.BLOOD_TYPE ,
p_rec.CORRESPONDENCE_LANGUAGE ,
p_rec.FAST_PATH_EMPLOYEE ,
p_rec.FTE_CAPACITY ,
HOLD_APPLICANT_DATE_UNTIL ,
p_rec.HONORS ,
INTERNAL_LOCATION ,
LAST_MEDICAL_TEST_BY ,
LAST_MEDICAL_TEST_DATE ,
MAILSTOP ,
OFFICE_NUMBER ,
ON_MILITARY_SERVICE ,
ORDER_NAME ,
p_rec.PRE_NAME_ADJUNCT ,
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 ,
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 ,
p_rec.USES_TOBACCO_FLAG ,
DPDNT_ADOPTION_DATE ,
DPDNT_VLNTRY_SVCE_FLAG ,
ORIGINAL_DATE_OF_HIRE ,
p_rec.town_of_birth ,
p_rec.region_of_birth ,
p_rec.country_of_birth ,
global_person_id ,
party_id ,
created_by,
creation_date,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
global_name,
local_name
from per_all_people_f
where person_id = l_person(l_count).person_id
and p_rec.effective_start_date-1
between effective_start_date
and effective_end_date;
hr_person_api.update_person
(p_effective_date => p_rec.effective_end_date,
p_datetrack_update_mode => l_datetrack_mode,
p_person_id => l_person(l_count).person_id,
p_object_version_number => l_person(l_count).object_version_number,
p_employee_number => l_person(l_count).employee_number,
p_last_name => l_person(l_count).last_name,
p_date_of_birth => l_person(l_count).date_of_birth,
p_first_name => l_person(l_count).first_name,
p_known_as => l_person(l_count).known_as,
p_marital_status => l_person(l_count).marital_status,
p_middle_names => l_person(l_count).middle_names,
p_nationality => l_person(l_count).nationality,
p_sex => l_person(l_count).sex,
p_title => l_person(l_count).title,
p_blood_type => l_person(l_count).blood_type,
p_correspondence_language => l_person(l_count).correspondence_language,
p_honors => l_person(l_count).honors,
p_pre_name_adjunct => l_person(l_count).pre_name_adjunct,
p_rehire_authorizor => l_person(l_count).rehire_authorizor,
p_rehire_recommendation => l_person(l_count).rehire_recommendation,
p_resume_exists => l_person(l_count).resume_exists,
p_resume_last_updated => l_person(l_count).resume_last_updated,
p_second_passport_exists => l_person(l_count).second_passport_exists,
p_student_status => l_person(l_count).student_status,
p_suffix => l_person(l_count).suffix,
p_date_of_death => l_person(l_count).date_of_death,
p_uses_tobacco_flag => l_person(l_count).uses_tobacco_flag,
p_town_of_birth => l_person(l_count).town_of_birth,
p_region_of_birth => l_person(l_count).region_of_birth,
p_country_of_birth => l_person(l_count).country_of_birth,
p_fast_path_employee => l_person(l_count).fast_path_employee,
p_email_address => l_person(l_count).email_address,
p_fte_capacity => l_person(l_count).fte_capacity,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment_id,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning,
p_orig_hire_warning => l_orig_hire_warning);
select ppf.*
from per_all_people_f ppf
where ppf.party_id = p_from_fk_id
order by ppf.effective_start_date;
hr_person_api.update_person
(p_effective_date => l_person.effective_start_date,
p_datetrack_update_mode => 'CORRECTION',
p_person_id => l_person.person_id,
p_object_version_number => l_person.object_version_number,
p_employee_number => l_person.employee_number,
p_party_id => l_person.party_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment_id,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning,
p_orig_hire_warning => l_orig_hire_warning);
update per_all_people_f
set party_id = l_person.party_id
where person_id = l_person.person_id
and effective_start_date = l_person.effective_start_date;
update_child_tables(p_rec => l_person);
select *
from per_all_people_f
where party_id = p_party_id
and p_effective_date
between effective_start_date
and effective_end_date;
l_rec.resume_last_updated := l_c1.resume_last_updated;
select *
from per_all_people_f ppf
where ppf.email_address is not null
and mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
and ppf.effective_end_date = hr_api.g_eot
and ppf.party_id is not null
and not exists(select null
from hz_contact_points
where owner_table_name = 'HZ_PARTIES'
and owner_table_id = ppf.party_id
and email_address = nvl(ppf.email_address,'NULL'));
create_update_contact_point(p_rec => l_person);
select *
from per_all_people_f ppf
where mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
and ppf.effective_end_date = hr_api.g_eot
and ppf.party_id is not null
and exists(select null
from hz_person_profiles
where party_id = ppf.party_id
and nvl(gender,'Z') in ('Z','U','M','F'));
update hz_person_profiles
set gender = decode(l_person.sex,null,'UNSPECIFIED'
,'F','FEMALE'
,'MALE')
where party_id = l_person.party_id;
select *
from per_all_people_f
where party_id = p_party_id
and person_id = p_person_id
and p_effective_date
between effective_start_date
and effective_end_date;
l_rec.resume_last_updated := l_c1.resume_last_updated;
select person_id
from per_all_people_f
where party_id is null
and rowid between p_start_rowid and p_end_rowid
and effective_end_date = hr_api.g_eot;
select *
from per_all_people_f
where person_id = p_person_id
and effective_end_date = hr_api.g_eot;
update per_all_people_f
set party_id = t_party_id(i)
where person_id = t_elig_person_id(i);
update per_competence_elements
set party_id = t_party_id(i)
where person_id = t_elig_person_id(i);
update per_events
set party_id = t_party_id(i)
where assignment_id in
(select assignment_id
from per_all_assignments_f
where person_id = t_elig_person_id(i));
update per_addresses
set party_id = t_party_id(i)
where person_id = t_elig_person_id(i);
update per_phones
set party_id = t_party_id(i)
where parent_id = t_elig_person_id(i)
and parent_table = 'PER_ALL_PEOPLE_F';
update per_qualifications
set party_id = t_party_id(i)
where person_id = t_elig_person_id(i);
update per_establishment_attendances
set party_id = t_party_id(i)
where person_id = t_elig_person_id(i);
update per_previous_employers
set party_id = t_party_id(i)
where person_id = t_elig_person_id(i);
t_person_id.delete;
t_party_id.delete;
t_elig_person_id.delete;
select /*+ rowid(ppf) */ *
from per_all_people_f ppf
where ppf.email_address is not null
and ppf.ROWID between p_start_rowid and p_end_rowid
and ppf.effective_end_date = hr_api.g_eot
and ppf.party_id is not null
and not exists(select /*+ no_unnest */ null
from hz_contact_points
where owner_table_name = 'HZ_PARTIES'
and owner_table_id = ppf.party_id
and email_address = nvl(ppf.email_address,'NULL'));
create_update_contact_point(p_rec => l_person);
INSERT INTO hr_tca_party_unmerge (party_id,status)
VALUES (p_party_id,'PURGE');
ROLLBACK TO hr_delete_person;