[Home] [Help]
93: ,p_effective_end_date OUT NOCOPY date
94: ,p_person_id OUT NOCOPY number) IS
95: --
96: l_proc varchar2(72) := g_package||'create_candidate_internal';
97: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
98: l_date_of_birth per_all_people_f.date_of_birth%type;
99: l_business_group_id per_all_people_f.business_group_id%type;
100: l_person_type_id per_all_people_f.person_type_id%type;
101: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
94: ,p_person_id OUT NOCOPY number) IS
95: --
96: l_proc varchar2(72) := g_package||'create_candidate_internal';
97: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
98: l_date_of_birth per_all_people_f.date_of_birth%type;
99: l_business_group_id per_all_people_f.business_group_id%type;
100: l_person_type_id per_all_people_f.person_type_id%type;
101: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
102: := p_last_name_phonetic;
95: --
96: l_proc varchar2(72) := g_package||'create_candidate_internal';
97: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
98: l_date_of_birth per_all_people_f.date_of_birth%type;
99: l_business_group_id per_all_people_f.business_group_id%type;
100: l_person_type_id per_all_people_f.person_type_id%type;
101: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
102: := p_last_name_phonetic;
103: --
96: l_proc varchar2(72) := g_package||'create_candidate_internal';
97: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
98: l_date_of_birth per_all_people_f.date_of_birth%type;
99: l_business_group_id per_all_people_f.business_group_id%type;
100: l_person_type_id per_all_people_f.person_type_id%type;
101: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
102: := p_last_name_phonetic;
103: --
104: -- dummy variables
102: := p_last_name_phonetic;
103: --
104: -- dummy variables
105: --
106: l_object_version_number per_all_people_f.object_version_number%type;
107: l_effective_start_date per_all_people_f.effective_start_date%type;
108: l_effective_end_date per_all_people_f.effective_end_date%type;
109: l_full_name per_all_people_f.full_name%type;
110: l_comment_id per_all_people_f.comment_id%type;
103: --
104: -- dummy variables
105: --
106: l_object_version_number per_all_people_f.object_version_number%type;
107: l_effective_start_date per_all_people_f.effective_start_date%type;
108: l_effective_end_date per_all_people_f.effective_end_date%type;
109: l_full_name per_all_people_f.full_name%type;
110: l_comment_id per_all_people_f.comment_id%type;
111: l_name_combination_warning boolean;
104: -- dummy variables
105: --
106: l_object_version_number per_all_people_f.object_version_number%type;
107: l_effective_start_date per_all_people_f.effective_start_date%type;
108: l_effective_end_date per_all_people_f.effective_end_date%type;
109: l_full_name per_all_people_f.full_name%type;
110: l_comment_id per_all_people_f.comment_id%type;
111: l_name_combination_warning boolean;
112: l_orig_hire_warning boolean;
105: --
106: l_object_version_number per_all_people_f.object_version_number%type;
107: l_effective_start_date per_all_people_f.effective_start_date%type;
108: l_effective_end_date per_all_people_f.effective_end_date%type;
109: l_full_name per_all_people_f.full_name%type;
110: l_comment_id per_all_people_f.comment_id%type;
111: l_name_combination_warning boolean;
112: l_orig_hire_warning boolean;
113: l_assign_payroll_warning boolean;
106: l_object_version_number per_all_people_f.object_version_number%type;
107: l_effective_start_date per_all_people_f.effective_start_date%type;
108: l_effective_end_date per_all_people_f.effective_end_date%type;
109: l_full_name per_all_people_f.full_name%type;
110: l_comment_id per_all_people_f.comment_id%type;
111: l_name_combination_warning boolean;
112: l_orig_hire_warning boolean;
113: l_assign_payroll_warning boolean;
114: l_legislation_code per_business_groups_perf.legislation_code%type;
112: l_orig_hire_warning boolean;
113: l_assign_payroll_warning boolean;
114: l_legislation_code per_business_groups_perf.legislation_code%type;
115: l_ptu_person_type_id number(15);
116: l_employee_number per_all_people_f.employee_number%type := hr_api.g_varchar2;
117: --
118: -- for disabling the descriptive flex field
119: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
120: hr_dflex_utility.l_ignore_dfcode_varray();
826: ,p_effective_end_date OUT NOCOPY date
827: ,p_person_id OUT NOCOPY number) IS
828: --
829: l_proc varchar2(72) := g_package||'create_registered_user';
830: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
831: l_date_of_birth per_all_people_f.date_of_birth%type;
832: l_business_group_id per_all_people_f.business_group_id%type;
833: l_person_type_id per_all_people_f.person_type_id%type;
834: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
827: ,p_person_id OUT NOCOPY number) IS
828: --
829: l_proc varchar2(72) := g_package||'create_registered_user';
830: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
831: l_date_of_birth per_all_people_f.date_of_birth%type;
832: l_business_group_id per_all_people_f.business_group_id%type;
833: l_person_type_id per_all_people_f.person_type_id%type;
834: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
835: := p_last_name_phonetic;
828: --
829: l_proc varchar2(72) := g_package||'create_registered_user';
830: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
831: l_date_of_birth per_all_people_f.date_of_birth%type;
832: l_business_group_id per_all_people_f.business_group_id%type;
833: l_person_type_id per_all_people_f.person_type_id%type;
834: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
835: := p_last_name_phonetic;
836: --
829: l_proc varchar2(72) := g_package||'create_registered_user';
830: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
831: l_date_of_birth per_all_people_f.date_of_birth%type;
832: l_business_group_id per_all_people_f.business_group_id%type;
833: l_person_type_id per_all_people_f.person_type_id%type;
834: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
835: := p_last_name_phonetic;
836: --
837: -- dummy variables
835: := p_last_name_phonetic;
836: --
837: -- dummy variables
838: --
839: l_object_version_number per_all_people_f.object_version_number%type;
840: l_effective_start_date per_all_people_f.effective_start_date%type;
841: l_effective_end_date per_all_people_f.effective_end_date%type;
842: l_full_name per_all_people_f.full_name%type;
843: l_comment_id per_all_people_f.comment_id%type;
836: --
837: -- dummy variables
838: --
839: l_object_version_number per_all_people_f.object_version_number%type;
840: l_effective_start_date per_all_people_f.effective_start_date%type;
841: l_effective_end_date per_all_people_f.effective_end_date%type;
842: l_full_name per_all_people_f.full_name%type;
843: l_comment_id per_all_people_f.comment_id%type;
844: l_name_combination_warning boolean;
837: -- dummy variables
838: --
839: l_object_version_number per_all_people_f.object_version_number%type;
840: l_effective_start_date per_all_people_f.effective_start_date%type;
841: l_effective_end_date per_all_people_f.effective_end_date%type;
842: l_full_name per_all_people_f.full_name%type;
843: l_comment_id per_all_people_f.comment_id%type;
844: l_name_combination_warning boolean;
845: l_orig_hire_warning boolean;
838: --
839: l_object_version_number per_all_people_f.object_version_number%type;
840: l_effective_start_date per_all_people_f.effective_start_date%type;
841: l_effective_end_date per_all_people_f.effective_end_date%type;
842: l_full_name per_all_people_f.full_name%type;
843: l_comment_id per_all_people_f.comment_id%type;
844: l_name_combination_warning boolean;
845: l_orig_hire_warning boolean;
846: l_legislation_code per_business_groups_perf.legislation_code%type;
839: l_object_version_number per_all_people_f.object_version_number%type;
840: l_effective_start_date per_all_people_f.effective_start_date%type;
841: l_effective_end_date per_all_people_f.effective_end_date%type;
842: l_full_name per_all_people_f.full_name%type;
843: l_comment_id per_all_people_f.comment_id%type;
844: l_name_combination_warning boolean;
845: l_orig_hire_warning boolean;
846: l_legislation_code per_business_groups_perf.legislation_code%type;
847: l_ptu_person_type_id number(15);
1340: ,p_per_information30 IN varchar2 default hr_api.g_varchar2
1341: ) IS
1342: --
1343: l_proc varchar2(72) := g_package||'update_registered_user';
1344: l_object_version_number per_all_people_f.object_version_number%TYPE;
1345: l_employee_number per_all_people_f.employee_number%TYPE;
1346: l_effective_date date;
1347: l_legislation_code per_business_groups.legislation_code%type;
1348: -- sex is defaulted to null - it is set to the value of sex
1341: ) IS
1342: --
1343: l_proc varchar2(72) := g_package||'update_registered_user';
1344: l_object_version_number per_all_people_f.object_version_number%TYPE;
1345: l_employee_number per_all_people_f.employee_number%TYPE;
1346: l_effective_date date;
1347: l_legislation_code per_business_groups.legislation_code%type;
1348: -- sex is defaulted to null - it is set to the value of sex
1349: -- on per_all_people_f if p_gender exists in hr_lookups
1345: l_employee_number per_all_people_f.employee_number%TYPE;
1346: l_effective_date date;
1347: l_legislation_code per_business_groups.legislation_code%type;
1348: -- sex is defaulted to null - it is set to the value of sex
1349: -- on per_all_people_f if p_gender exists in hr_lookups
1350: l_sex per_all_people_f.sex%TYPE := hr_api.g_varchar2;
1351: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
1352: := p_last_name_phonetic;
1353: --
1346: l_effective_date date;
1347: l_legislation_code per_business_groups.legislation_code%type;
1348: -- sex is defaulted to null - it is set to the value of sex
1349: -- on per_all_people_f if p_gender exists in hr_lookups
1350: l_sex per_all_people_f.sex%TYPE := hr_api.g_varchar2;
1351: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
1352: := p_last_name_phonetic;
1353: --
1354: -- dummy variables
1355: --
1356: l_profile_id number;
1357: l_effective_start_date date;
1358: l_effective_end_date date;
1359: l_full_name per_all_people_f.full_name%type;
1360: l_comment_id number;
1361: l_name_combination_warning boolean;
1362: l_assign_payroll_warning boolean;
1363: l_orig_hire_warning boolean;
1371: l_add_struct_k hr_kflex_utility.l_ignore_kfcode_varray :=
1372: hr_kflex_utility.l_ignore_kfcode_varray();
1373: --
1374: --
1375: -- cursor to select entries IN per_all_people_f
1376: -- relating to registered user
1377: --
1378: cursor csr_person_id(p_person_id number,p_effective_date date) is
1379: select effective_start_date,object_version_number,employee_number
1376: -- relating to registered user
1377: --
1378: cursor csr_person_id(p_person_id number,p_effective_date date) is
1379: select effective_start_date,object_version_number,employee_number
1380: from per_all_people_f
1381: where person_id = p_person_id
1382: and l_effective_date between effective_start_date and effective_end_date;
1383: --
1384: begin
1924: cursor csr_application_count(p_vacancy_id number, p_effective_date date) is
1925: select count(*)
1926: from per_all_assignments_f paaf
1927: , IRC_AGENCY_VACANCIES AGV1
1928: ,per_all_people_f per1
1929: ,irc_notification_preferences prefs
1930: where paaf.vacancy_id = p_vacancy_id and paaf.assignment_type = 'A'
1931: and p_effective_date between PAAF.EFFECTIVE_START_DATE
1932: AND PAAF.EFFECTIVE_END_DATE
1999: ,p_asg_object_version_number OUT NOCOPY number
2000: ,p_applicant_number OUT NOCOPY varchar2) IS
2001: --
2002: l_proc varchar2(72) := g_package||'registered_user_application';
2003: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2004: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2005: l_person_id per_all_people_f.person_id%TYPE;
2006: l_party_id per_all_people_f.party_id%TYPE;
2007: l_rec_person_id per_all_people_f.person_id%TYPE;
2001: --
2002: l_proc varchar2(72) := g_package||'registered_user_application';
2003: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2004: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2005: l_person_id per_all_people_f.person_id%TYPE;
2006: l_party_id per_all_people_f.party_id%TYPE;
2007: l_rec_person_id per_all_people_f.person_id%TYPE;
2008: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2009: l_effective_date date;
2002: l_proc varchar2(72) := g_package||'registered_user_application';
2003: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2004: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2005: l_person_id per_all_people_f.person_id%TYPE;
2006: l_party_id per_all_people_f.party_id%TYPE;
2007: l_rec_person_id per_all_people_f.person_id%TYPE;
2008: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2009: l_effective_date date;
2010: l_application_received_date date;
2003: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2004: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2005: l_person_id per_all_people_f.person_id%TYPE;
2006: l_party_id per_all_people_f.party_id%TYPE;
2007: l_rec_person_id per_all_people_f.person_id%TYPE;
2008: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2009: l_effective_date date;
2010: l_application_received_date date;
2011: l_party_last_update_date date;
2009: l_effective_date date;
2010: l_application_received_date date;
2011: l_party_last_update_date date;
2012: l_return_status varchar2(10);
2013: l_applicant_number per_all_people_f.applicant_number%type;
2014: l_person_type_id per_person_types.person_type_id%type;
2015: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2016: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2011: l_party_last_update_date date;
2012: l_return_status varchar2(10);
2013: l_applicant_number per_all_people_f.applicant_number%type;
2014: l_person_type_id per_person_types.person_type_id%type;
2015: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2016: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2012: l_return_status varchar2(10);
2013: l_applicant_number per_all_people_f.applicant_number%type;
2014: l_person_type_id per_person_types.person_type_id%type;
2015: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2016: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2013: l_applicant_number per_all_people_f.applicant_number%type;
2014: l_person_type_id per_person_types.person_type_id%type;
2015: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2016: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2015: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2016: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2016: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2017: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2018:
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2019: l_last_name per_all_people_f.last_name%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2020: l_first_name per_all_people_f.first_name%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2021: l_per_information1 per_all_people_f.per_information1%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2022: l_per_information2 per_all_people_f.per_information2%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2023: l_per_information18 per_all_people_f.per_information18%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2031: l_name_suffix per_all_people_f.suffix%type;
2024: l_per_information19 per_all_people_f.per_information19%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2031: l_name_suffix per_all_people_f.suffix%type;
2032: l_known_as per_all_people_f.known_as%type;
2025: l_date_of_birth per_all_people_f.date_of_birth%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2031: l_name_suffix per_all_people_f.suffix%type;
2032: l_known_as per_all_people_f.known_as%type;
2033: l_business_group_id per_all_vacancies.business_group_id%type;
2026: l_title per_all_people_f.title%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2031: l_name_suffix per_all_people_f.suffix%type;
2032: l_known_as per_all_people_f.known_as%type;
2033: l_business_group_id per_all_vacancies.business_group_id%type;
2034: l_organization_id per_all_vacancies.organization_id%type;
2027: l_gender per_all_people_f.sex%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2031: l_name_suffix per_all_people_f.suffix%type;
2032: l_known_as per_all_people_f.known_as%type;
2033: l_business_group_id per_all_vacancies.business_group_id%type;
2034: l_organization_id per_all_vacancies.organization_id%type;
2035: l_job_id per_all_vacancies.job_id%type;
2028: l_marital_status per_all_people_f.marital_status%type;
2029: l_previous_last_name per_all_people_f.last_name%type;
2030: l_middle_name per_all_people_f.middle_names%type;
2031: l_name_suffix per_all_people_f.suffix%type;
2032: l_known_as per_all_people_f.known_as%type;
2033: l_business_group_id per_all_vacancies.business_group_id%type;
2034: l_organization_id per_all_vacancies.organization_id%type;
2035: l_job_id per_all_vacancies.job_id%type;
2036: l_position_id per_all_vacancies.position_id%type;
2042: l_legislation_code per_business_groups.legislation_code%type;
2043: --
2044: -- variables for hr_person_api.update_person
2045: --
2046: l_object_version_number per_all_people_f.object_version_number%TYPE;
2047: l_employee_number per_all_people_f.employee_number%TYPE;
2048: --
2049: -- dummy variables
2050: --
2043: --
2044: -- variables for hr_person_api.update_person
2045: --
2046: l_object_version_number per_all_people_f.object_version_number%TYPE;
2047: l_employee_number per_all_people_f.employee_number%TYPE;
2048: --
2049: -- dummy variables
2050: --
2051: l_msg_count number;
2058: l_comment_id number;
2059: l_name_combination_warning boolean;
2060: l_assign_payroll_warning boolean;
2061: l_orig_hire_warning boolean;
2062: l_object_version_number_d per_all_people_f.object_version_number%TYPE;
2063: -- those required for hr_assignment_api.create_secondary_apl_asg
2064: l_concatenated_segments varchar2(2000);
2065: l_cagr_grade_def_id number;
2066: l_cagr_concatenated_segments varchar2(2000);
2068: l_soft_coding_keyflex_id number;
2069: l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
2070: -- those required for hr_applicant_api.create_applicant
2071: l_application_id number;
2072: l_full_name per_all_people_f.full_name%type;
2073: l_person_type per_person_types.system_person_type%type;
2074: l_ptu_person_type_id number;
2075: -- for disabling the descriptive flex field
2076: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
2078: -- for disabling the key flex field
2079: l_add_struct_k hr_kflex_utility.l_ignore_kfcode_varray :=
2080: hr_kflex_utility.l_ignore_kfcode_varray();
2081: --
2082: -- cursor to select entries IN per_all_people_f
2083: -- relating to registered user
2084: --
2085: cursor csr_person_id(p_party_id number
2086: ,p_effective_date date
2090: ,per.current_employee_flag
2091: ,per.current_applicant_flag
2092: ,per.current_npw_flag
2093: ,per.applicant_number
2094: from per_all_people_f per
2095: where per.party_id = p_party_id
2096: and p_effective_date between per.effective_start_date and per.effective_end_date
2097: and business_group_id=p_business_group_id;
2098: --
2095: where per.party_id = p_party_id
2096: and p_effective_date between per.effective_start_date and per.effective_end_date
2097: and business_group_id=p_business_group_id;
2098: --
2099: -- cursor to select entries IN per_all_people_f
2100: -- for a given person over all dates
2101: --
2102: cursor csr_person_all_dates(p_party_id number
2103: ,p_business_group_id number) is
2101: --
2102: cursor csr_person_all_dates(p_party_id number
2103: ,p_business_group_id number) is
2104: select null
2105: from per_all_people_f per
2106: where per.party_id = p_party_id
2107: and business_group_id=p_business_group_id;
2108: --
2109: -- cursor to select person details for the registered user
2124: ,per_information1
2125: ,per_information2
2126: ,per_information18
2127: ,per_information19
2128: from per_all_people_f
2129: where person_id = p_person_id
2130: and p_effective_date between effective_start_date and effective_end_date;
2131: -- cursor to select business_group relating to a vacancy
2132: cursor csr_get_vac(p_vacancy_id number) is
2723: ) IS
2724: --
2725: PRAGMA autonomous_transaction;
2726: l_proc varchar2(72) := g_package||'create_user';
2727: l_person_id per_all_people_f.person_id%type;
2728: l_user_id number;
2729: l_profile_check boolean;
2730: l_user_name fnd_user.user_name%type;
2731: l_default_last_name per_all_people_f.last_name%type;
2727: l_person_id per_all_people_f.person_id%type;
2728: l_user_id number;
2729: l_profile_check boolean;
2730: l_user_name fnd_user.user_name%type;
2731: l_default_last_name per_all_people_f.last_name%type;
2732: l_effective_start_date date;
2733: l_effective_end_date date;
2734: l_sec_profile_assignment_id number;
2735: l_business_group_id number;
3321: l_date_of_birth date;
3322:
3323: cursor get_prev_emp is
3324: select per1.party_id,count(*)
3325: from per_all_people_f per1
3326: where per1.last_name=decode(p_last_name,NULL,per1.last_name,p_last_name)
3327: and (per1.first_name=nvl(p_first_name,per1.first_name)
3328: or (per1.first_name is null and p_first_name is null))
3329: and (per1.middle_names=nvl(p_middle_names,per1.middle_names)
3340: or (per1.email_address is null and p_email_address is null))
3341: and (p_home_phone_number is null
3342: or exists (select 1 from per_phones phns
3343: where phns.parent_id=per1.person_id
3344: and phns.parent_table='PER_ALL_PEOPLE_F'
3345: and phns.phone_type in ('H1','H2','H3')
3346: and phns.phone_number = p_home_phone_number))
3347: and (p_work_phone_number is null
3348: or exists (select 1 from per_phones phns
3346: and phns.phone_number = p_home_phone_number))
3347: and (p_work_phone_number is null
3348: or exists (select 1 from per_phones phns
3349: where phns.parent_id=per1.person_id
3350: and phns.parent_table='PER_ALL_PEOPLE_F'
3351: and phns.phone_type in ('W1','W2','W3')
3352: and phns.phone_number = p_work_phone_number))
3353: and (p_address_line_1 is null
3354: or exists (select 1 from per_addresses addr
3354: or exists (select 1 from per_addresses addr
3355: where addr.person_id=per1.person_id
3356: and addr.address_type in ('H','REC','HOME')))
3357: and (p_manager_last_name is null
3358: or exists (select 1 from per_all_people_f per2
3359: ,per_all_assignments_f asg2
3360: where asg2.person_id=per1.person_id
3361: and asg2.assignment_type in ('E','C')
3362: and asg2.supervisor_id=per2.person_id
3378: l_count2 number;
3379:
3380: cursor current_emp(p_party_id number) is
3381: select 1
3382: from per_all_people_f per1
3383: where per1.party_id=p_party_id
3384: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
3385: and current_employee_flag='Y';
3386:
3387: l_dummy number;
3388:
3389: cursor in_reg_bg(p_party_id number) is
3390: select per1.person_id
3391: from per_all_people_f per1
3392: where per1.party_id=p_party_id
3393: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
3394: and per1.business_group_id = fnd_profile.value('IRC_REGISTRATION_BG_ID');
3395:
3412: and prefs.object_type='WPREF';
3413:
3414: cursor get_last_emp_rec(p_party_id number) is
3415: select per1.person_id
3416: from per_all_people_f per1
3417: where per1.party_id=p_party_id
3418: and per1.current_employee_flag='Y'
3419: and per1.effective_start_date
3420: order by per1.effective_end_date desc;
3421:
3422: cursor get_last_per_rec(p_party_id number) is
3423: select per1.person_id
3424: from per_all_people_f per1
3425: where per1.party_id=p_party_id
3426: and per1.effective_start_date
3428:
3430: select per1.business_group_id
3431: ,per1.object_version_number
3432: ,per1.employee_number
3433: ,per1.effective_start_date
3434: from per_all_people_f per1
3435: where per1.person_id=p_person_id
3436: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
3437:
3438: l_person_ovn number;
3478: l_sec_profile_id number;
3479: l_password varchar2(30);
3480: l_effective_start_date date;
3481: l_effective_end_date date;
3482: l_full_name per_all_people_f.full_name%type;
3483: l_comment_id number;
3484: l_name_combination_warning boolean;
3485: l_assign_payroll_warning boolean;
3486: l_orig_hire_warning boolean;
4084: --
4085: l_user_id number;
4086: l_person_id number;
4087: l_person_party_id number;
4088: l_first_name per_all_people_f.first_name%type;
4089: l_last_name per_all_people_f.last_name%type;
4090: l_email_address per_all_people_f.email_address%type;
4091: l_default_last_name per_all_people_f.last_name%type;
4092: --
4085: l_user_id number;
4086: l_person_id number;
4087: l_person_party_id number;
4088: l_first_name per_all_people_f.first_name%type;
4089: l_last_name per_all_people_f.last_name%type;
4090: l_email_address per_all_people_f.email_address%type;
4091: l_default_last_name per_all_people_f.last_name%type;
4092: --
4093: cursor get_person_party_info is
4086: l_person_id number;
4087: l_person_party_id number;
4088: l_first_name per_all_people_f.first_name%type;
4089: l_last_name per_all_people_f.last_name%type;
4090: l_email_address per_all_people_f.email_address%type;
4091: l_default_last_name per_all_people_f.last_name%type;
4092: --
4093: cursor get_person_party_info is
4094: select user_id, employee_id, person_party_id
4087: l_person_party_id number;
4088: l_first_name per_all_people_f.first_name%type;
4089: l_last_name per_all_people_f.last_name%type;
4090: l_email_address per_all_people_f.email_address%type;
4091: l_default_last_name per_all_people_f.last_name%type;
4092: --
4093: cursor get_person_party_info is
4094: select user_id, employee_id, person_party_id
4095: from fnd_user
4108: --
4109: cursor get_bg(p_person_id number) is
4110: select per1.business_group_id,per1.object_version_number,per1.employee_number
4111: ,per1.effective_start_date
4112: from per_all_people_f per1
4113: where per1.person_id=p_person_id
4114: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
4115: --
4116: cursor ptu_exists(p_person_id number,p_person_type_id number) is
4127: --
4128: -- dummy variables
4129: --
4130: l_tmp_resp_id number;
4131: l_object_version_number per_all_people_f.object_version_number%type;
4132: l_effective_start_date per_all_people_f.effective_start_date%type;
4133: l_effective_end_date per_all_people_f.effective_end_date%type;
4134: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4135: l_profile_check boolean;
4128: -- dummy variables
4129: --
4130: l_tmp_resp_id number;
4131: l_object_version_number per_all_people_f.object_version_number%type;
4132: l_effective_start_date per_all_people_f.effective_start_date%type;
4133: l_effective_end_date per_all_people_f.effective_end_date%type;
4134: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4135: l_profile_check boolean;
4136: l_search_criteria_id number;
4129: --
4130: l_tmp_resp_id number;
4131: l_object_version_number per_all_people_f.object_version_number%type;
4132: l_effective_start_date per_all_people_f.effective_start_date%type;
4133: l_effective_end_date per_all_people_f.effective_end_date%type;
4134: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4135: l_profile_check boolean;
4136: l_search_criteria_id number;
4137: l_sc_ovn number;
4130: l_tmp_resp_id number;
4131: l_object_version_number per_all_people_f.object_version_number%type;
4132: l_effective_start_date per_all_people_f.effective_start_date%type;
4133: l_effective_end_date per_all_people_f.effective_end_date%type;
4134: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4135: l_profile_check boolean;
4136: l_search_criteria_id number;
4137: l_sc_ovn number;
4138: l_notif_preference_id number;
4147: l_person_ovn number;
4148: l_employee_number varchar2(255);
4149: l_person_start_date date;
4150: l_dt_mode varchar2(30);
4151: l_full_name per_all_people_f.full_name%type;
4152: l_comment_id number;
4153: l_name_combination_warning boolean;
4154: l_assign_payroll_warning boolean;
4155: l_orig_hire_warning boolean;
4465: where user_name=upper(p_user_name);
4466: --
4467: cursor get_bg(p_person_id number) is
4468: select per1.business_group_id
4469: from per_all_people_f per1
4470: where per1.person_id=p_person_id
4471: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
4472: --
4473: cursor ptu_exists(p_person_id number,p_person_type_id number) is
4961: ,p_per_information27 IN varchar2 default null
4962: ,p_per_information28 IN varchar2 default null
4963: ,p_per_information29 IN varchar2 default null
4964: ,p_per_information30 IN varchar2 default null
4965: ,p_person_id IN per_all_people_f.person_id%type default null
4966: ) IS
4967: --
4968: PRAGMA autonomous_transaction;
4969: l_proc varchar2(72) := g_package||'create_user_internal_byReferral';
4966: ) IS
4967: --
4968: PRAGMA autonomous_transaction;
4969: l_proc varchar2(72) := g_package||'create_user_internal_byReferral';
4970: l_person_id per_all_people_f.person_id%type;
4971: l_user_id number;
4972: l_profile_check boolean;
4973: l_user_name fnd_user.user_name%type;
4974: l_default_last_name per_all_people_f.last_name%type;
4970: l_person_id per_all_people_f.person_id%type;
4971: l_user_id number;
4972: l_profile_check boolean;
4973: l_user_name fnd_user.user_name%type;
4974: l_default_last_name per_all_people_f.last_name%type;
4975: l_effective_start_date date;
4976: l_effective_end_date date;
4977: l_sec_profile_assignment_id number;
4978: l_business_group_id number;