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