[Home] [Help]
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;
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;
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
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
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;
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
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
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: --
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: --
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)
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)
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)
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)
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
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: --
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
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(+)
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(+)
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
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
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'
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