[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor csr_update_person is
select person_id, object_version_number,employee_number,effective_start_date,last_name
from per_all_people_f
where party_id = p_party_id;
cursor csr_update_notif_pref is
select object_version_number,notification_preference_id
from irc_notification_preferences
where party_id = p_party_id;
for rec_person in csr_update_person loop
hr_utility.set_location(l_proc, 20);
/* call to hr_person_api.update_person to set the names to Anonymous */
if(l_last_name <> fnd_message.get_string('PER','IRC_412172_ANONYMOUS_NAME')) then
hr_person_api.update_person
(
p_effective_date => rec_person.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => rec_person.person_id
,p_object_version_number => l_person_ovn
,p_employee_number => l_employee_number
,p_last_name => fnd_message.get_string
('PER','IRC_412172_ANONYMOUS_NAME')
,p_first_name => ''
,p_known_as => ''
,p_middle_names => ''
,p_previous_last_name => ''
,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
);
open csr_update_notif_pref;
fetch csr_update_notif_pref into l_notif_pref_ovn,l_notif_pref_id;
irc_notification_prefs_api.update_notification_prefs
(
p_notification_preference_id => l_notif_pref_id
,p_effective_date => p_effective_date
,p_allow_access => 'N'
,p_receive_info_mail => 'N'
,p_matching_jobs => 'N'
,p_object_version_number => l_notif_pref_ovn
);
close csr_update_notif_pref;
select full_name , last_name
from per_all_people_f
where person_id = p_person_id;
select user_name
from fnd_user
where employee_id = l_personIdIn;
procedure delete_person
(
p_party_id in number
,p_root_person_id in number
,p_effective_date in date
) is
--
l_user_name fnd_user.user_name%type;
select ias.assignment_status_id, ias.object_version_number
from irc_assignment_statuses ias, per_all_assignments_f asg,
per_all_people_f per
where asg.assignment_id = ias.assignment_id
and asg.person_id = per.person_id
and per.party_id = p_party_id;
select per.person_id
from per_all_people_f per
where per.party_id = p_party_id
and p_effective_date between per.effective_start_date
and per.effective_end_date;
select usr.user_name
from fnd_user usr
where usr.employee_id = p_root_person_id;
fnd_user_pkg.UpdateUser(
x_user_name => l_user_name
,x_owner => 'CUST'
,x_employee_id => fnd_user_pkg.null_number
);
hr_person_delete.delete_a_person
(
p_person_id => rec_ppf.person_id
,p_form_call => false
,p_session_date => p_effective_date
);
end delete_person;
procedure delete_person_child_data
(
p_party_id in number
,p_root_person_id in number
,p_effective_date in date
,p_process_type in varchar2
) is
--
l_proc varchar2(72) := 'delete_person_child_data';
select document_id, object_version_number , party_id,end_date,type,person_id
from irc_documents
where party_id = p_party_id;
select notification_preference_id, object_version_number
from irc_notification_preferences
where party_id = p_party_id;
select job_basket_item_id, object_version_number
from irc_job_basket_items
where party_id = p_party_id;
select qualification_id, object_version_number
from per_qualifications
where party_id = p_party_id;
select attendance_id, object_version_number
from per_establishment_attendances
where party_id = p_party_id;
select previous_employer_id, object_version_number
from per_previous_employers
where party_id = p_party_id;
select competence_element_id, object_version_number
from per_competence_elements
where party_id = p_party_id;
select address_id, object_version_number
from per_addresses
where party_id = p_party_id
order by primary_flag, date_from desc;
select search_criteria_id, object_version_number
from irc_search_criteria
where object_id = p_root_person_id
and object_type = 'WPREF';
select search_criteria_id, object_version_number
from irc_search_criteria
where object_id = p_root_person_id
and object_type = 'PERSON';
select phone_id, object_version_number
from per_phones
where party_id = p_party_id;
select vacancy_consideration_id, object_version_number
from irc_vacancy_considerations
where party_id = p_party_id;
select iid.interview_details_id, iid.object_version_number, iid.start_date, iid.end_date
from irc_interview_details iid,
per_events pe
where pe.party_id = p_party_id
and iid.event_id = pe.event_id;
select icm.COMMUNICATION_MESSAGE_ID, icm.OBJECT_VERSION_NUMBER
from irc_comm_messages icm,
irc_comm_topics ict,
irc_communications ic,
per_all_assignments_f paf,
per_all_people_f ppf
where ppf.party_id = p_party_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and paf.person_id = ppf.person_id
and paf.effective_end_date in (select max(paf1.effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id )
and ic.object_id = paf.assignment_id
and ict.COMMUNICATION_ID = ic.COMMUNICATION_ID
and icm.COMMUNICATION_TOPIC_ID = ict.COMMUNICATION_TOPIC_ID;
select icr.COMMUNICATION_RECIPIENT_ID, icr.OBJECT_VERSION_NUMBER
from irc_comm_recipients icr,
irc_comm_topics ict,
irc_communications ic,
per_all_assignments_f paf,
per_all_people_f ppf
where ppf.party_id = p_party_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and paf.person_id = ppf.person_id
and paf.effective_end_date in (select max(paf1.effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id )
and ic.object_id = paf.assignment_id
and ict.COMMUNICATION_ID = ic.COMMUNICATION_ID
and icr.COMMUNICATION_OBJECT_ID = ict.COMMUNICATION_TOPIC_ID
and icr.COMMUNICATION_OBJECT_TYPE = 'TOPIC';
select ict.COMMUNICATION_TOPIC_ID, ict.OBJECT_VERSION_NUMBER
from irc_comm_topics ict,
irc_communications ic,
per_all_assignments_f paf,
per_all_people_f ppf
where ppf.party_id = p_party_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and paf.person_id = ppf.person_id
and paf.effective_end_date in (select max(paf1.effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id )
and ic.object_id = paf.assignment_id
and ict.COMMUNICATION_ID = ic.COMMUNICATION_ID;
select ic.COMMUNICATION_ID, ic.OBJECT_VERSION_NUMBER
from irc_communications ic,
per_all_assignments_f paf,
per_all_people_f ppf
where ppf.party_id = p_party_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and paf.person_id = ppf.person_id
and paf.effective_end_date in (select max(paf1.effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id )
and ic.object_id = paf.assignment_id;
irc_document_api.delete_document
(p_document_id => rec_irc_doc.document_id
,p_object_version_number => rec_irc_doc.object_version_number
,p_effective_date => p_effective_date
,p_person_id => rec_irc_doc.person_id
,p_party_id => rec_irc_doc.party_id
,p_end_date => rec_irc_doc.end_date
,p_type =>rec_irc_doc.type
);
per_qualifications_api.delete_qualification
(p_qualification_id => rec_per_qual.qualification_id
,p_object_version_number => rec_per_qual.object_version_number
);
per_estab_attendances_api.delete_attended_estab
(p_attendance_id => rec_per_est_att.attendance_id
,p_object_version_number => rec_per_est_att.object_version_number
);
hr_previous_employment_api.delete_previous_employer
(p_previous_employer_id => rec_per_prev_empl.previous_employer_id
,p_object_version_number => rec_per_prev_empl.object_version_number
);
hr_competence_element_api.delete_competence_element
(p_competence_element_id => rec_per_comps.competence_element_id
,p_object_version_number => rec_per_comps.object_version_number
);
irc_job_basket_items_api.delete_job_basket_item
(p_job_basket_item_id => rec_jbi.job_basket_item_id
,p_object_version_number => rec_jbi.object_version_number
);
irc_search_criteria_api.delete_work_choices
(p_search_criteria_id => rec_isc_work.search_criteria_id
,p_object_version_number => rec_isc_work.object_version_number
);
irc_search_criteria_api.delete_saved_search
(p_search_criteria_id => rec_isc_party.search_criteria_id
,p_object_version_number => rec_isc_party.object_version_number
);
hr_phone_api.delete_phone
(p_phone_id => rec_phn.phone_id
,p_object_version_number => rec_phn.object_version_number
);
irc_vacancy_considerations_api.delete_vacancy_consideration
(p_vacancy_consideration_id => rec_ivc.vacancy_consideration_id
,p_object_version_number => rec_ivc.object_version_number
);
delete from irc_interview_details
where interview_details_id = rec_irc_iid.interview_details_id;
irc_notification_prefs_api.delete_notification_prefs
(p_notification_preference_id => rec_irc_notif.notification_preference_id
,p_object_version_number => rec_irc_notif.object_version_number
);
irc_purge_old_data_pkg.delete_person
(p_party_id => p_party_id
,p_root_person_id => p_root_person_id
,p_effective_date => p_effective_date
);
end delete_person_child_data;
select full_name
from per_all_people_f
where person_id = p_root_person_id
and p_effective_date between effective_start_date
and effective_end_date;
irc_purge_old_data_pkg.delete_person_child_data
(p_party_id => p_party_id
,p_root_person_id => p_root_person_id
,p_effective_date => p_effective_date
,p_process_type => p_process_type
);
function get_max_updated_date
(
p_person_id in number
) return date is
--
l_max_date date := null;
cursor csr_max_updated_date is
select GREATEST (
NVL (MAX (addr.last_update_date), hr_api.g_sot),
NVL (MAX (phn.last_update_date), hr_api.g_sot),
NVL (MAX (ido.last_update_date), hr_api.g_sot),
NVL (MAX (pem.last_update_date), hr_api.g_sot),
NVL (MAX (esa.last_update_date), hr_api.g_sot),
NVL (MAX (qua.last_update_date), hr_api.g_sot),
NVL (MAX (pce.last_update_date), hr_api.g_sot),
NVL (MAX (jbo.last_update_date), hr_api.g_sot),
NVL (MAX (iscw.last_update_date), hr_api.g_sot),
NVL (MAX (iscp.last_update_date), hr_api.g_sot),
MAX (per2.last_update_date),
NVL (MAX (asg.last_update_date), hr_api.g_sot)
)
from per_addresses addr,
per_phones phn,
irc_documents ido,
irc_search_criteria iscp,
irc_search_criteria iscw,
per_previous_employers pem,
per_establishment_attendances esa,
per_qualifications qua,
per_competence_elements pce,
irc_job_basket_items jbo,
per_all_people_f per1,
per_all_assignments_f asg,
per_all_people_f per2
where per1.person_id=p_person_id
and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
and per1.party_id = per2.party_id
and per2.person_id = asg.person_id(+)
and per1.person_id = ido.person_id(+)
and per1.person_id = addr.person_id(+)
and per1.person_id = phn.parent_id(+)
and phn.parent_table(+) = 'PER_ALL_PEOPLE_F'
and per1.person_id = pem.person_id(+)
and per1.person_id = esa.person_id(+)
and per1.person_id = qua.person_id(+)
and per1.person_id = pce.person_id(+)
and per1.person_id = jbo.person_id(+)
and per1.person_id = iscp.object_id(+)
and per1.person_id = iscw.object_id(+)
and iscp.object_type(+) = 'PERSON'
and iscw.object_type(+) = 'WPREF';
open csr_max_updated_date;
fetch csr_max_updated_date into l_max_date;
close csr_max_updated_date;
end get_max_updated_date;
select max(asg.effective_start_date)
from per_all_assignments_f asg, per_all_people_f per
where per.party_id = p_party_id
and p_effective_date
between per.effective_start_date and per.effective_end_date
and asg.person_id = per.person_id
and asg.assignment_type = 'A'
and not exists (select 1 from per_all_assignments_f asg2
where asg.assignment_id = asg2.assignment_id
and asg.effective_start_date > asg2.effective_start_date);
select 1
from per_all_people_f per1
,per_person_type_usages_f ptu
,per_person_types ppt
where per1.party_id = p_party_id
and per1.person_id = ptu.person_id
and p_effective_date between per1.effective_start_date and per1.effective_end_date
and ptu.effective_end_date > p_effective_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type not in ('EX_APL', 'OTHER','IRC_REG_USER');
select inp.person_id,
inp.party_id
from irc_notification_preferences inp
, fnd_user usr
where inp.person_id = usr.employee_id
and is_free_to_purge(inp.party_id,p_effective_date)='TRUE'
and p_months < months_between(p_effective_date,usr.last_logon_date);
cursor csr_last_update_date is
select inp.person_id,
inp.party_id
from irc_notification_preferences inp
where is_free_to_purge(inp.party_id,p_effective_date)='TRUE'
and p_months < months_between(p_effective_date
,inp.last_update_date)
and p_months < months_between(p_effective_date
,irc_purge_old_data_pkg.get_max_updated_date(inp.person_id));
select inp.person_id,
inp.party_id
from irc_notification_preferences inp
where exists (select 1
from per_all_people_f per
, per_person_type_usages_f ptu,
per_person_types ppt
where per.party_id=inp.party_id
and nvl(per.current_emp_or_apl_flag,'N')='N'
and nvl(per.current_npw_flag,'N')='N'
and per.person_id=ptu.person_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and p_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id=ppt.person_type_id
and ppt.system_person_type = 'EX_APL'
)
and irc_purge_old_data_pkg.is_free_to_purge(inp.party_id,p_effective_date)='TRUE'
and p_months < months_between(p_effective_date,
irc_purge_old_data_pkg.last_application_date
(inp.party_id,p_effective_date)) ;
elsif(p_measure_type = 'UPDATEDATE') then
--
hr_utility.set_location(l_proc, 30);
for rec_last_update_date in csr_last_update_date loop
irc_purge_old_data_pkg.notify_or_purge
(p_effective_date => p_effective_date
,p_process_type => p_process_type
,p_party_id => rec_last_update_date.party_id
,p_root_person_id => rec_last_update_date.person_id
);
select meaning
from hr_lookups
where lookup_type = 'IRC_PROCESS_TYPE'
and lookup_code = l_prcCodeIn;
select meaning
from hr_lookups
where lookup_type = 'IRC_MEASURE_TYPE'
and lookup_code = l_msrCodeIn;
select user_name
from fnd_user u
where u.employee_id is not null
and u.user_id between p_start_pkid and p_end_pkid
and not exists(select null
from per_all_people_f
where person_id = u.employee_id
);
fnd_user_pkg.UpdateUser(
x_user_name => csr_rec.user_name
,x_owner => 'CUST'
,x_employee_id => fnd_user_pkg.null_number
);