[Home] [Help]
PACKAGE BODY: APPS.IRC_PURGE_OLD_DATA_PKG
Source
1 package body irc_purge_old_data_pkg as
2 /* $Header: ircpurge.pkb 120.15.12020000.2 2012/07/04 13:22:17 amnaraya ship $ */
3 -- ----------------------------------------------------------------------------
4 -- |--------------------------< anonymize_candidate_data >--------------------|
5 -- ----------------------------------------------------------------------------
6 --
7 procedure anonymize_candidate_data
8 (
9 p_party_id in number
10 ,p_effective_date in date
11 ) is
12 --
13 l_proc varchar2(72) := 'anonymize_candidate_data';
14 l_person_ovn number;
15 l_notif_pref_ovn number;
16 l_notif_pref_id number;
17 l_effective_start_date date;
18 l_effective_end_date date;
19 l_full_name per_all_people_f.full_name%type;
20 l_employee_number per_all_people_f.employee_number%type;
21 l_comment_id number;
22 l_name_combination_warning boolean;
23 l_assign_payroll_warning boolean;
24 l_orig_hire_warning boolean;
25 l_last_name per_all_people_f.last_name%type;
26 l_print_element_info varchar2(32000);
27 --
28 cursor csr_update_person is
29 select person_id, object_version_number,employee_number,effective_start_date,last_name
30 from per_all_people_f
31 where party_id = p_party_id;
32 --
33 cursor csr_update_notif_pref is
34 select object_version_number,notification_preference_id
35 from irc_notification_preferences
36 where party_id = p_party_id;
37 --
38 begin
39 --
40 -- Update all the person records to Anonymous
41 --
42 hr_utility.set_location('Entering Anonymize Candidate Data:'||l_proc, 10);
43 for rec_person in csr_update_person loop
44 hr_utility.set_location(l_proc, 20);
45 l_person_ovn := rec_person.object_version_number;
46 l_employee_number := rec_person.employee_number;
47 l_last_name := rec_person.last_name;
48 --
49 /* call to hr_person_api.update_person to set the names to Anonymous */
50 if(l_last_name <> fnd_message.get_string('PER','IRC_412172_ANONYMOUS_NAME')) then
51 hr_person_api.update_person
52 (
53 p_effective_date => rec_person.effective_start_date
54 ,p_datetrack_update_mode => 'CORRECTION'
55 ,p_person_id => rec_person.person_id
56 ,p_object_version_number => l_person_ovn
57 ,p_employee_number => l_employee_number
58 ,p_last_name => fnd_message.get_string
59 ('PER','IRC_412172_ANONYMOUS_NAME')
60 ,p_first_name => ''
61 ,p_known_as => ''
62 ,p_middle_names => ''
63 ,p_previous_last_name => ''
64 ,p_effective_start_date => l_effective_start_date
65 ,p_effective_end_date => l_effective_end_date
66 ,p_full_name => l_full_name
67 ,p_comment_id => l_comment_id
68 ,p_name_combination_warning => l_name_combination_warning
69 ,p_assign_payroll_warning => l_assign_payroll_warning
70 ,p_orig_hire_warning => l_orig_hire_warning
71 );
72 end if;
73 end loop;
74 --
75 -- Update the irc_notification_preferences table to make the candidate
76 -- non-searchable
77 --
78 open csr_update_notif_pref;
79 fetch csr_update_notif_pref into l_notif_pref_ovn,l_notif_pref_id;
80 hr_utility.set_location(l_proc, 40);
81 irc_notification_prefs_api.update_notification_prefs
82 (
83 p_notification_preference_id => l_notif_pref_id
84 ,p_effective_date => p_effective_date
85 ,p_allow_access => 'N'
86 ,p_receive_info_mail => 'N'
87 ,p_matching_jobs => 'N'
88 ,p_object_version_number => l_notif_pref_ovn
89 );
90 close csr_update_notif_pref;
91 hr_utility.set_location('Leaving Anonymize Candidate Data:'||l_proc, 100);
92 end anonymize_candidate_data;
93 --
94 --
95 -- ----------------------------------------------------------------------------
96 -- |--------------------------< send_notification_to_person >-----------------|
97 -- ----------------------------------------------------------------------------
98 --
99 procedure send_notification_to_person
100 (
101 p_person_id in number
102 ) is
103 --
104 l_proc varchar2(72) := 'send_notification_to_person';
105 l_subject fnd_new_messages.message_text%type :=
106 fnd_message.get_string('PER','IRC_412169_PURGE_SUBJECT');
107 l_nid number;
108 l_name per_all_people_f.full_name%type;
109 l_message_conc_html varchar2(15600);
110 l_message_conc_text varchar2(15600);
111 l_usrName varchar2(200);
112 l_last_name per_all_people_f.last_name%type;
113 --
114 cursor csr_name is
115 select full_name , last_name
116 from per_all_people_f
117 where person_id = p_person_id;
118
119 cursor csr_getfnduser(l_personIdIn in number) is
120 select user_name
121 from fnd_user
122 where employee_id = l_personIdIn;
123 --
124 begin
125 --
126 hr_utility.set_location('Entering Send Notification to Person:'||l_proc, 10);
127 open csr_name;
128 fetch csr_name into l_name,l_last_name;
129 close csr_name;
130 if l_last_name = fnd_message.get_string('PER','IRC_412172_ANONYMOUS_NAME') then
131 return;
132 end if;
133 --
134 open csr_getfnduser(p_person_id);
135 fetch csr_getfnduser into l_usrName;
136 close csr_getfnduser;
137 if l_usrName is null then
138 raise_application_error (-20001,'No wf role is assigned to the candidate');
139 end if;
140 --
141 fnd_message.set_name('PER','IRC_412170_PURGE_MESSAGE_TEXT');
142 fnd_message.set_token('PERSON_FULL_NAME',l_name);
143 l_message_conc_text := fnd_message.get;
144 --
145 fnd_message.set_name('PER','IRC_412171_PURGE_MESSAGE_HTML');
146 fnd_message.set_token('PERSON_FULL_NAME',l_name);
147 l_message_conc_html := fnd_message.get;
148 --
149 l_nid := irc_notification_helper_pkg.send_notification(
150 p_person_id => p_person_id
151 ,p_subject => l_subject
152 ,p_html_body => l_message_conc_html
153 ,p_text_body => l_message_conc_text
154 );
155 hr_utility.set_location('Leaving Send Notification to Person:'||l_proc, 100);
156 --
157 end send_notification_to_person;
158 -- ----------------------------------------------------------------------------
159 -- |--------------------------< delete_person >-------------------------------|
160 -- ----------------------------------------------------------------------------
161 --
162 procedure delete_person
163 (
164 p_party_id in number
165 ,p_root_person_id in number
166 ,p_effective_date in date
167 ) is
168 --
169 l_user_name fnd_user.user_name%type;
170 --
171 cursor csr_ias_del is
172 select ias.assignment_status_id, ias.object_version_number
173 from irc_assignment_statuses ias, per_all_assignments_f asg,
174 per_all_people_f per
175 where asg.assignment_id = ias.assignment_id
176 and asg.person_id = per.person_id
177 and per.party_id = p_party_id;
178 --
179 cursor csr_per_del is
180 select per.person_id
181 from per_all_people_f per
182 where per.party_id = p_party_id
183 and p_effective_date between per.effective_start_date
184 and per.effective_end_date;
185 --
186 cursor csr_usr_del is
187 select usr.user_name
188 from fnd_user usr
189 where usr.employee_id = p_root_person_id;
190 --
191 begin
192 --
193 -- Delete Fnd User record
194 --
195 open csr_usr_del;
196 fetch csr_usr_del into l_user_name;
197 close csr_usr_del;
198 --
199 if l_user_name is not null then
200 fnd_user_pkg.UpdateUser(
201 x_user_name => l_user_name
202 ,x_owner => 'CUST'
203 ,x_employee_id => fnd_user_pkg.null_number
204 );
205 --
206 -- Disable user
207 --
208 fnd_user_pkg.DisableUser(
209 username => l_user_name
210 );
211 end if;
212 --
213 -- Delete Irc Assignment Statuses record
214 --
215 for rec_ias in csr_ias_del
216 loop
217 irc_ias_del.del
218 (
219 p_assignment_status_id => rec_ias.assignment_status_id
220 ,p_object_version_number => rec_ias.object_version_number
221 );
222 end loop;
223 --
224 -- Delete Person record using hr_person_delete.delete_a_person. This
225 -- procedure deletes a person completely from the HR database. Deletes
226 -- from all tables referencing this person.
227 --
228 for rec_ppf in csr_per_del
229 loop
230 hr_person_delete.delete_a_person
231 (
232 p_person_id => rec_ppf.person_id
233 ,p_form_call => false
234 ,p_session_date => p_effective_date
235 );
236 end loop;
237 --
238 end delete_person;
239 --
240 --
241 -- ----------------------------------------------------------------------------
242 -- |--------------------------< delete_person_child_data >--------------------|
243 -- ----------------------------------------------------------------------------
244 --
245 procedure delete_person_child_data
246 (
247 p_party_id in number
248 ,p_root_person_id in number
249 ,p_effective_date in date
250 ,p_process_type in varchar2
251 ) is
252 --
253 l_proc varchar2(72) := 'delete_person_child_data';
254 --
255
256 cursor csr_irc_doc is
257 select document_id, object_version_number , party_id,end_date,type,person_id
258 from irc_documents
259 where party_id = p_party_id;
260 --
261 cursor csr_irc_notif is
262 select notification_preference_id, object_version_number
263 from irc_notification_preferences
264 where party_id = p_party_id;
265 --
266 cursor csr_jbi is
267 select job_basket_item_id, object_version_number
268 from irc_job_basket_items
269 where party_id = p_party_id;
270 --
271 cursor csr_per_qual is
272 select qualification_id, object_version_number
273 from per_qualifications
274 where party_id = p_party_id;
275 --
276 cursor csr_per_est_att is
277 select attendance_id, object_version_number
278 from per_establishment_attendances
279 where party_id = p_party_id;
280 --
281 cursor csr_per_prev_empl is
282 select previous_employer_id, object_version_number
283 from per_previous_employers
284 where party_id = p_party_id;
285 --
286 cursor csr_per_comps is
287 select competence_element_id, object_version_number
288 from per_competence_elements
289 where party_id = p_party_id;
290 --
291 cursor csr_per_addr is
292 select address_id, object_version_number
293 from per_addresses
294 where party_id = p_party_id
295 order by primary_flag, date_from desc;
296 --
297 cursor csr_isc_work is
298 select search_criteria_id, object_version_number
299 from irc_search_criteria
300 where object_id = p_root_person_id
301 and object_type = 'WPREF';
302 --
303 cursor csr_isc_person is
304 select search_criteria_id, object_version_number
305 from irc_search_criteria
306 where object_id = p_root_person_id
307 and object_type = 'PERSON';
308 --
309 cursor csr_phn_party is
310 select phone_id, object_version_number
311 from per_phones
312 where party_id = p_party_id;
313 --
314 cursor csr_ivc_cons is
315 select vacancy_consideration_id, object_version_number
316 from irc_vacancy_considerations
317 where party_id = p_party_id;
318 --
319
320 -- For IRC_INTERVIEW_DETAILS
321 -------------------------
322 cursor csr_irc_iid is
323 select iid.interview_details_id, iid.object_version_number, iid.start_date, iid.end_date
324 from irc_interview_details iid,
325 per_events pe
326 where pe.party_id = p_party_id
327 and iid.event_id = pe.event_id;
328
329 -- For IRC_COMM_MESSAGES
330 ---------------------
331 cursor csr_irc_cmm is
332 select icm.COMMUNICATION_MESSAGE_ID, icm.OBJECT_VERSION_NUMBER
333 from irc_comm_messages icm,
334 irc_comm_topics ict,
335 irc_communications ic,
336 per_all_assignments_f paf,
337 per_all_people_f ppf
338 where ppf.party_id = p_party_id
339 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
340 and paf.person_id = ppf.person_id
341 and paf.effective_end_date in (select max(paf1.effective_end_date)
342 from per_all_assignments_f paf1
343 where paf1.assignment_id = paf.assignment_id )
344 and ic.object_id = paf.assignment_id
345 and ict.COMMUNICATION_ID = ic.COMMUNICATION_ID
346 and icm.COMMUNICATION_TOPIC_ID = ict.COMMUNICATION_TOPIC_ID;
347
348 -- For IRC_COMM_RECIPIENTS
349 -----------------------
350 cursor csr_irc_cmr is
351 select icr.COMMUNICATION_RECIPIENT_ID, icr.OBJECT_VERSION_NUMBER
352 from irc_comm_recipients icr,
353 irc_comm_topics ict,
354 irc_communications ic,
355 per_all_assignments_f paf,
356 per_all_people_f ppf
357 where ppf.party_id = p_party_id
358 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
359 and paf.person_id = ppf.person_id
360 and paf.effective_end_date in (select max(paf1.effective_end_date)
361 from per_all_assignments_f paf1
362 where paf1.assignment_id = paf.assignment_id )
363 and ic.object_id = paf.assignment_id
364 and ict.COMMUNICATION_ID = ic.COMMUNICATION_ID
365 and icr.COMMUNICATION_OBJECT_ID = ict.COMMUNICATION_TOPIC_ID
366 and icr.COMMUNICATION_OBJECT_TYPE = 'TOPIC';
367
368 -- For IRC_COMM_TOPICS
369 -------------------
370 cursor csr_irc_cmt is
371 select ict.COMMUNICATION_TOPIC_ID, ict.OBJECT_VERSION_NUMBER
372 from irc_comm_topics ict,
373 irc_communications ic,
374 per_all_assignments_f paf,
375 per_all_people_f ppf
376 where ppf.party_id = p_party_id
377 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
378 and paf.person_id = ppf.person_id
379 and paf.effective_end_date in (select max(paf1.effective_end_date)
380 from per_all_assignments_f paf1
381 where paf1.assignment_id = paf.assignment_id )
382 and ic.object_id = paf.assignment_id
383 and ict.COMMUNICATION_ID = ic.COMMUNICATION_ID;
384
385 -- For IRC_COMMUNICATIONS
386 ----------------------
387 cursor csr_irc_cmc is
388 select ic.COMMUNICATION_ID, ic.OBJECT_VERSION_NUMBER
389 from irc_communications ic,
390 per_all_assignments_f paf,
391 per_all_people_f ppf
392 where ppf.party_id = p_party_id
393 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
394 and paf.person_id = ppf.person_id
395 and paf.effective_end_date in (select max(paf1.effective_end_date)
396 from per_all_assignments_f paf1
397 where paf1.assignment_id = paf.assignment_id )
398 and ic.object_id = paf.assignment_id;
399 --
400 begin
401 hr_utility.set_location('Entering Purge Person Info:'||l_proc, 10);
402 --
403 -- Delete from IRC_DOCUMENTS
404 --
405
406 for rec_irc_doc in csr_irc_doc loop
407 irc_document_api.delete_document
408 (p_document_id => rec_irc_doc.document_id
409 ,p_object_version_number => rec_irc_doc.object_version_number
410 ,p_effective_date => p_effective_date
411 ,p_person_id => rec_irc_doc.person_id
412 ,p_party_id => rec_irc_doc.party_id
413 ,p_end_date => rec_irc_doc.end_date
414 ,p_type =>rec_irc_doc.type
415 );
416 end loop;
417
418
419
420
421
422 --
423 -- Delete from PER_QUALIFICATIONS
424 --
425 hr_utility.set_location(l_proc, 20);
426 for rec_per_qual in csr_per_qual loop
427 per_qualifications_api.delete_qualification
428 (p_qualification_id => rec_per_qual.qualification_id
429 ,p_object_version_number => rec_per_qual.object_version_number
430 );
431 end loop;
432 --
433 -- Delete from PER_ESTABLISHMENT_ATTENDANCES
434 -- There should not be a child record in Per Qualification table mapped
435 -- by Attendance ID
436 --
437 hr_utility.set_location(l_proc, 30);
438 for rec_per_est_att in csr_per_est_att loop
439 per_estab_attendances_api.delete_attended_estab
440 (p_attendance_id => rec_per_est_att.attendance_id
441 ,p_object_version_number => rec_per_est_att.object_version_number
442 );
443 end loop;
444 --
445 -- Delete from Per_Previous_Employers
446 -- This deletes data from Per_Previous_jobs an per_previous_job_usages also
447 --
448 hr_utility.set_location(l_proc, 40);
449 for rec_per_prev_empl in csr_per_prev_empl loop
450 hr_previous_employment_api.delete_previous_employer
451 (p_previous_employer_id => rec_per_prev_empl.previous_employer_id
452 ,p_object_version_number => rec_per_prev_empl.object_version_number
453 );
454 end loop;
455 --
456 -- Api checks for competence_element_id being a parent.
457 -- Delete from Per_Competence_Elements
458 --
459 hr_utility.set_location(l_proc, 50);
460 for rec_per_comps in csr_per_comps loop
461 hr_competence_element_api.delete_competence_element
462 (p_competence_element_id => rec_per_comps.competence_element_id
463 ,p_object_version_number => rec_per_comps.object_version_number
464 );
465 end loop;
466 --
467 --Delete from Per_Addresses
468 --
469 hr_utility.set_location(l_proc, 60);
470 for rec_per_addr in csr_per_addr loop
471 per_add_del.del
472 (p_address_id => rec_per_addr.address_id
473 ,p_object_version_number => rec_per_addr.object_version_number
474 );
475 end loop;
476 --
477 -- Delete from IRC_JOB_BASKET_ITEMS
478 --
479 hr_utility.set_location(l_proc, 80);
480 for rec_jbi in csr_jbi loop
481 irc_job_basket_items_api.delete_job_basket_item
482 (p_job_basket_item_id => rec_jbi.job_basket_item_id
483 ,p_object_version_number => rec_jbi.object_version_number
484 );
485 end loop;
486 --
487 -- Delete the work preferences
488 --
489 hr_utility.set_location(l_proc, 90);
490 for rec_isc_work in csr_isc_work loop
491 irc_search_criteria_api.delete_work_choices
492 (p_search_criteria_id => rec_isc_work.search_criteria_id
493 ,p_object_version_number => rec_isc_work.object_version_number
494 );
495 end loop;
496 --
497 -- Delete the Job Saved Searches
498 --
499 hr_utility.set_location(l_proc, 100);
500 for rec_isc_party in csr_isc_person loop
501 irc_search_criteria_api.delete_saved_search
502 (p_search_criteria_id => rec_isc_party.search_criteria_id
503 ,p_object_version_number => rec_isc_party.object_version_number
504 );
505 end loop;
506 --
507 -- Delete from PER_PHONES
508 --
509 hr_utility.set_location(l_proc, 110);
510 for rec_phn in csr_phn_party loop
511 hr_phone_api.delete_phone
512 (p_phone_id => rec_phn.phone_id
513 ,p_object_version_number => rec_phn.object_version_number
514 );
515 end loop;
516 --
517 -- Delete from IRC_VACANCY_CONSIDERATIONS
518 --
519 hr_utility.set_location(l_proc, 112);
520 for rec_ivc in csr_ivc_cons loop
521 irc_vacancy_considerations_api.delete_vacancy_consideration
522 (p_vacancy_consideration_id => rec_ivc.vacancy_consideration_id
523 ,p_object_version_number => rec_ivc.object_version_number
524 );
525 end loop;
526 --
527 -- Delete from IRC_INTERVIEW_DETAILS
528 --
529 for rec_irc_iid in csr_irc_iid loop
530 delete from irc_interview_details
531 where interview_details_id = rec_irc_iid.interview_details_id;
532 end loop;
533 --
534 -- Delete from IRC_COMM_MESSAGES
535 --
536 for rec_irc_cmm in csr_irc_cmm loop
537 irc_cmm_del.del
538 (p_communication_message_id => rec_irc_cmm.COMMUNICATION_MESSAGE_ID
539 ,p_object_version_number => rec_irc_cmm.OBJECT_VERSION_NUMBER
540 );
541 end loop;
542 --
543 -- Delete from IRC_COMM_RECIPIENTS
544 --
545 for rec_irc_cmr in csr_irc_cmr loop
546 irc_cmr_del.del
547 (p_communication_recipient_id => rec_irc_cmr.COMMUNICATION_RECIPIENT_ID
548 ,p_object_version_number => rec_irc_cmr.OBJECT_VERSION_NUMBER
549 );
550 end loop;
551 --
552 -- Delete from IRC_COMM_TOPICS
553 --
554 for rec_irc_cmt in csr_irc_cmt loop
555 irc_cmt_del.del
556 (p_communication_topic_id => rec_irc_cmt.COMMUNICATION_TOPIC_ID
557 ,p_object_version_number => rec_irc_cmt.OBJECT_VERSION_NUMBER
558 );
559 end loop;
560 --
561 -- Delete from IRC_COMMUNICATIONS
562 --
563 for rec_irc_cmc in csr_irc_cmc loop
564 irc_cmc_del.del
565 (p_communication_id => rec_irc_cmc.COMMUNICATION_ID
566 ,p_object_version_number => rec_irc_cmc.OBJECT_VERSION_NUMBER
567 );
568 end loop;
569 --
570 if(p_process_type='DEL') then
571 --
572 -- Delete from IRC_NOTIFICATION_PREFERENCES
573 --
574 hr_utility.set_location(l_proc, 115);
575 for rec_irc_notif in csr_irc_notif loop
576 irc_notification_prefs_api.delete_notification_prefs
577 (p_notification_preference_id => rec_irc_notif.notification_preference_id
578 ,p_object_version_number => rec_irc_notif.object_version_number
579 );
580 end loop;
581 --
582 -- Call a procedure to delete the person,asignment and the related records
583 --
584 hr_utility.set_location(l_proc, 120);
585 irc_purge_old_data_pkg.delete_person
586 (p_party_id => p_party_id
587 ,p_root_person_id => p_root_person_id
588 ,p_effective_date => p_effective_date
589 );
590 --
591 elsif(p_process_type ='DELUPD') then
592 --
593 -- Call a procedure to update the person record
594 --
595 hr_utility.set_location(l_proc, 130);
596 irc_purge_old_data_pkg.anonymize_candidate_data
597 (p_party_id => p_party_id
598 ,p_effective_date => p_effective_date
599 );
600 --
601 end if ;
602 --
603 hr_utility.set_location('Leaving Purge Person Info:'||l_proc,200);
604 end delete_person_child_data;
605 --
606 -- ----------------------------------------------------------------------------
607 -- |--------------------------< notify_or_purge >-----------------------------|
608 -- ----------------------------------------------------------------------------
609 --
610 procedure notify_or_purge
611 (
612 p_effective_date in date
613 ,p_process_type in varchar2
614 ,p_party_id in number
615 ,p_root_person_id in number
616 ) is
617 --
618 l_proc varchar2(72) := 'notify_or_purge';
619 l_print_element_info varchar2(32000);
620 l_person_full_name per_all_people_f.full_name%type;
621 cursor csr_full_name is
622 select full_name
623 from per_all_people_f
624 where person_id = p_root_person_id
625 and p_effective_date between effective_start_date
626 and effective_end_date;
627 --
628 begin
629 --
630 hr_utility.set_location('Entering Notify or Purge:'||l_proc, 10);
631 -- Fetch the person full name
632 --
633 open csr_full_name;
634 fetch csr_full_name into l_person_full_name;
635 close csr_full_name;
636 --
637 if(p_process_type = 'NOTIFY') then
638 --
639 hr_utility.set_location(l_proc, 20);
640 irc_purge_old_data_pkg.send_notification_to_person
641 (p_person_id => p_root_person_id
642 );
643 elsif(p_process_type = 'UPD') then
644 --
645 hr_utility.set_location(l_proc, 30);
646 irc_purge_old_data_pkg.anonymize_candidate_data
647 (p_party_id => p_party_id
648 ,p_effective_date => p_effective_date
649 );
650 elsif(p_process_type = 'DEL' or p_process_type = 'DELUPD') then
651 --
652 hr_utility.set_location(l_proc, 40);
653 irc_purge_old_data_pkg.delete_person_child_data
654 (p_party_id => p_party_id
655 ,p_root_person_id => p_root_person_id
656 ,p_effective_date => p_effective_date
657 ,p_process_type => p_process_type
658 );
659 end if;
660 commit;
661 --
662 -- Write the details of the person record
663 --
664 l_print_element_info := rpad(nvl(l_person_full_name,' '),60)||' '||
665 rpad(nvl(to_char(p_root_person_id),' '),10)||' '||
666 rpad(nvl(to_char(p_party_id),' '),10)||' '||
667 rpad(nvl('SUCCESS',' '),10);
668 --
669 Fnd_file.put_line(FND_FILE.LOG,l_print_element_info);
670 hr_utility.set_location('Leaving Notify or Purge:'||l_proc, 100);
671 exception
672 when others then
673 l_print_element_info := rpad(nvl(l_person_full_name,' '),60)||' '||
674 rpad(nvl(to_char(p_root_person_id),' '),10)||' '||
675 rpad(nvl(to_char(p_party_id),' '),10)||' '||
676 rpad(nvl('FAILURE',' '),10);
677 Fnd_file.put_line(FND_FILE.LOG,l_print_element_info);
678 Fnd_file.put_line(FND_FILE.LOG,' FAILURE REASON:'||sqlerrm);
679 hr_utility.set_location('Leaving Notify or Purge:'||l_proc, 100);
680 rollback;
681 end notify_or_purge;
682 --
683 -- ----------------------------------------------------------------------------
684 -- |--------------------------< get_max_updated_date >------------------------|
685 -- ----------------------------------------------------------------------------
686 --
687 function get_max_updated_date
688 (
689 p_person_id in number
690 ) return date is
691 --
692 l_max_date date := null;
693 --
694 cursor csr_max_updated_date is
695 select GREATEST (
696 NVL (MAX (addr.last_update_date), hr_api.g_sot),
697 NVL (MAX (phn.last_update_date), hr_api.g_sot),
698 NVL (MAX (ido.last_update_date), hr_api.g_sot),
699 NVL (MAX (pem.last_update_date), hr_api.g_sot),
700 NVL (MAX (esa.last_update_date), hr_api.g_sot),
701 NVL (MAX (qua.last_update_date), hr_api.g_sot),
702 NVL (MAX (pce.last_update_date), hr_api.g_sot),
703 NVL (MAX (jbo.last_update_date), hr_api.g_sot),
704 NVL (MAX (iscw.last_update_date), hr_api.g_sot),
705 NVL (MAX (iscp.last_update_date), hr_api.g_sot),
706 MAX (per2.last_update_date),
707 NVL (MAX (asg.last_update_date), hr_api.g_sot)
708 )
709 from per_addresses addr,
710 per_phones phn,
711 irc_documents ido,
712 irc_search_criteria iscp,
713 irc_search_criteria iscw,
714 per_previous_employers pem,
715 per_establishment_attendances esa,
716 per_qualifications qua,
717 per_competence_elements pce,
718 irc_job_basket_items jbo,
719 per_all_people_f per1,
720 per_all_assignments_f asg,
721 per_all_people_f per2
722 where per1.person_id=p_person_id
723 and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
724 and per1.party_id = per2.party_id
725 and per2.person_id = asg.person_id(+)
726 and per1.person_id = ido.person_id(+)
727 and per1.person_id = addr.person_id(+)
728 and per1.person_id = phn.parent_id(+)
729 and phn.parent_table(+) = 'PER_ALL_PEOPLE_F'
730 and per1.person_id = pem.person_id(+)
731 and per1.person_id = esa.person_id(+)
732 and per1.person_id = qua.person_id(+)
733 and per1.person_id = pce.person_id(+)
734 and per1.person_id = jbo.person_id(+)
735 and per1.person_id = iscp.object_id(+)
736 and per1.person_id = iscw.object_id(+)
737 and iscp.object_type(+) = 'PERSON'
738 and iscw.object_type(+) = 'WPREF';
739 --
740 begin
741 open csr_max_updated_date;
742 fetch csr_max_updated_date into l_max_date;
743 close csr_max_updated_date;
744 return l_max_date;
745 end get_max_updated_date;
746 --
747 -- ----------------------------------------------------------------------------
748 -- |--------------------------< last_application_date >-----------------------|
749 -- ----------------------------------------------------------------------------
750 --
751 function last_application_date
752 (
753 p_party_id in number
754 ,p_effective_date in date
755 )
756 return date is
757 --
758 l_max_application_date date := null;
759 --
760 cursor csr_last_application_date is
761 select max(asg.effective_start_date)
762 from per_all_assignments_f asg, per_all_people_f per
763 where per.party_id = p_party_id
764 and p_effective_date
765 between per.effective_start_date and per.effective_end_date
766 and asg.person_id = per.person_id
767 and asg.assignment_type = 'A'
768 and not exists (select 1 from per_all_assignments_f asg2
769 where asg.assignment_id = asg2.assignment_id
770 and asg.effective_start_date > asg2.effective_start_date);
771 --
772 begin
773 --
774 open csr_last_application_date;
775 fetch csr_last_application_date into l_max_application_date;
776 close csr_last_application_date;
777 return l_max_application_date;
778 --
779 end last_application_date;
780 --
781 function is_free_to_purge(p_party_id number,p_effective_date date) return string is
782 cursor c1 (p_party_id number,p_effective_date date) is
783 select 1
784 from per_all_people_f per1
785 ,per_person_type_usages_f ptu
786 ,per_person_types ppt
787 where per1.party_id = p_party_id
788 and per1.person_id = ptu.person_id
789 and p_effective_date between per1.effective_start_date and per1.effective_end_date
790 and ptu.effective_end_date > p_effective_date
791 and ptu.person_type_id = ppt.person_type_id
792 and ppt.system_person_type not in ('EX_APL', 'OTHER','IRC_REG_USER');
793 l_dummy number;
794 begin
795 open c1(p_party_id,p_effective_date);
796 fetch c1 into l_dummy;
797 if c1%found then
798 close c1;
799 return 'FALSE';
800 else
801 close c1;
802 return 'TRUE';
803 end if;
804 end is_free_to_purge;
805 --
806 -- ----------------------------------------------------------------------------
807 -- |--------------------------< purge_records >-------------------------------|
808 -- ----------------------------------------------------------------------------
809 --
810 procedure purge_records
811 (
812 p_effective_date in Date
813 ,p_process_type in Varchar2
814 ,p_months in Number
815 ,p_measure_type Varchar2
816 ) is
817 --
818 l_proc varchar2(72) := 'purge_records';
819 l_header Varchar2(500);
820 l_underline Varchar2(500);
821 --
822 cursor csr_last_login_date is
823 select inp.person_id,
824 inp.party_id
825 from irc_notification_preferences inp
826 , fnd_user usr
827 where inp.person_id = usr.employee_id
828 and is_free_to_purge(inp.party_id,p_effective_date)='TRUE'
829 and p_months < months_between(p_effective_date,usr.last_logon_date);
830 --
831 cursor csr_last_update_date is
832 select inp.person_id,
833 inp.party_id
834 from irc_notification_preferences inp
835 where is_free_to_purge(inp.party_id,p_effective_date)='TRUE'
836 and p_months < months_between(p_effective_date
837 ,inp.last_update_date)
838 and p_months < months_between(p_effective_date
839 ,irc_purge_old_data_pkg.get_max_updated_date(inp.person_id));
840 --
841 cursor csr_last_application_date is
842 select inp.person_id,
843 inp.party_id
844 from irc_notification_preferences inp
845 where exists (select 1
846 from per_all_people_f per
847 , per_person_type_usages_f ptu,
848 per_person_types ppt
849 where per.party_id=inp.party_id
850 and nvl(per.current_emp_or_apl_flag,'N')='N'
851 and nvl(per.current_npw_flag,'N')='N'
852 and per.person_id=ptu.person_id
853 and p_effective_date between per.effective_start_date
854 and per.effective_end_date
855 and p_effective_date between ptu.effective_start_date
856 and ptu.effective_end_date
857 and ptu.person_type_id=ppt.person_type_id
858 and ppt.system_person_type = 'EX_APL'
859 )
860 and irc_purge_old_data_pkg.is_free_to_purge(inp.party_id,p_effective_date)='TRUE'
861 and p_months < months_between(p_effective_date,
862 irc_purge_old_data_pkg.last_application_date
863 (inp.party_id,p_effective_date)) ;
864 --
865 begin
866 --
867 hr_utility.set_location('Entering Purge Records:'||l_proc, 10);
868 l_header := rpad('FULL_NAME',60)||' '||
869 rpad('PERSON_ID',10)||' '||
870 rpad('PARTY_ID',10)||' '||
871 rpad('RESULT',10);
872 --
873 l_underline := rpad('-',60,'-')||' '||
874 rpad('-',10,'-')||' '||
875 rpad('-',10,'-')||' '||
876 rpad('-',10,'-');
877 --
878 Fnd_file.put_line(FND_FILE.LOG,l_header);
879 Fnd_file.put_line(FND_FILE.LOG,l_underline);
880 if(p_measure_type = 'LOGINDATE') then
881 --
882 hr_utility.set_location(l_proc, 20);
883 for rec_last_login_date in csr_last_login_date loop
884 irc_purge_old_data_pkg.notify_or_purge
885 (p_effective_date => p_effective_date
886 ,p_process_type => p_process_type
887 ,p_party_id => rec_last_login_date.party_id
888 ,p_root_person_id => rec_last_login_date.person_id
889 );
890 end loop;
891 elsif(p_measure_type = 'UPDATEDATE') then
892 --
893 hr_utility.set_location(l_proc, 30);
894 for rec_last_update_date in csr_last_update_date loop
895 irc_purge_old_data_pkg.notify_or_purge
896 (p_effective_date => p_effective_date
897 ,p_process_type => p_process_type
898 ,p_party_id => rec_last_update_date.party_id
899 ,p_root_person_id => rec_last_update_date.person_id
900 );
901 end loop;
902 elsif(p_measure_type = 'APPLDATE') then
903 --
904 hr_utility.set_location(l_proc, 40);
905 for rec_last_appl_date in csr_last_application_date loop
906 irc_purge_old_data_pkg.notify_or_purge
907 (p_effective_date => p_effective_date
908 ,p_process_type => p_process_type
909 ,p_party_id => rec_last_appl_date.party_id
910 ,p_root_person_id => rec_last_appl_date.person_id
911 );
912 end loop;
913 end if;
914 hr_utility.set_location('Leaving Purge Records:'||l_proc, 100);
915 end purge_records;
916 -- ----------------------------------------------------------------------------
917 -- |--------------------------< purge_record_process >------------------------|
918 -- ----------------------------------------------------------------------------
919 --
920 procedure purge_record_process (errbuf out nocopy varchar2
921 ,retcode out nocopy varchar2
922 ,p_effective_date in varchar2
923 ,p_process_type in varchar2
924 ,p_measure_type in varchar2
925 ,p_months in number) is
926 --
927 l_proc varchar2(72) := 'purge_record_process';
928 --
929 l_process_type varchar2(50);
930 l_measure_type varchar2(50);
931 cursor getprstype(l_prcCodeIn in varchar2) is
932 select meaning
933 from hr_lookups
934 where lookup_type = 'IRC_PROCESS_TYPE'
935 and lookup_code = l_prcCodeIn;
936
937 cursor getmeasureType(l_msrCodeIn in varchar2) is
938 select meaning
939 from hr_lookups
940 where lookup_type = 'IRC_MEASURE_TYPE'
941 and lookup_code = l_msrCodeIn;
942 begin
943 --
944 hr_utility.set_location('Entering Purge Record Process:'||l_proc, 10);
945 --
946 open getprstype(p_process_type);
947 fetch getprstype into l_process_type;
948 close getprstype;
949 open getmeasureType(p_measure_type);
950 fetch getmeasureType into l_measure_type;
951 close getmeasureType;
952 Fnd_file.put_line(FND_FILE.LOG,'This report shows the result of the candidates processed using following');
953 Fnd_file.put_line(FND_FILE.LOG,'parameter');
954 Fnd_file.put_line(FND_FILE.LOG,' ');
955 Fnd_file.put_line(FND_FILE.LOG,'Purge Type:'||l_measure_type);
956 Fnd_file.put_line(FND_FILE.LOG,'Activity Criteria:'||l_process_type);
957 Fnd_file.put_line(FND_FILE.LOG,'Effective Date:'||p_effective_date);
958 Fnd_file.put_line(FND_FILE.LOG,'Months Since Activity:'||p_months);
959 irc_purge_old_data_pkg.purge_records
960 (p_effective_date => fnd_date.canonical_to_date(p_effective_date)
961 ,p_process_type => p_process_type
962 ,p_months => p_months
963 ,p_measure_type => p_measure_type
964 );
965 retcode := 0;
966 hr_utility.set_location('Leaving Purge Record Process:'||l_proc, 70);
967 exception
968 when others then
969 --
970 hr_utility.set_location('Leaving Purge Record Process:'||l_proc, 80);
971 rollback;
972 --
973 -- Set the return parameters to indicate failure
974 --
975 errbuf := sqlerrm;
976 retcode := 2;
977 --
978 end purge_record_process;
979 --
980 -- ----------------------------------------------------------------------------
981 -- |--------------------------< clean_employee_data >-------------------------|
982 -- ----------------------------------------------------------------------------
983 procedure clean_employee_data(p_process_ctrl IN varchar2
984 ,p_start_pkid IN number
985 ,p_end_pkid IN number
986 ,p_rows_processed OUT nocopy number
987 )
988 is
989 cursor csr_upd_employee is
990 select user_name
991 from fnd_user u
992 where u.employee_id is not null
993 and u.user_id between p_start_pkid and p_end_pkid
994 and not exists(select null
995 from per_all_people_f
996 where person_id = u.employee_id
997 );
998 l_rows_processed number := 0;
999 begin
1000 for csr_rec in csr_upd_employee
1001 loop
1002 fnd_user_pkg.UpdateUser(
1003 x_user_name => csr_rec.user_name
1004 ,x_owner => 'CUST'
1005 ,x_employee_id => fnd_user_pkg.null_number
1006 );
1007 l_rows_processed := l_rows_processed + 1;
1008 end loop;
1009 p_rows_processed := l_rows_processed;
1010 end clean_employee_data;
1011 --
1012 end irc_purge_old_data_pkg;