[Home] [Help]
98: ,p_effective_end_date OUT NOCOPY date
99: ,p_person_id OUT NOCOPY number) IS
100: --
101: l_proc varchar2(72) := g_package||'create_candidate_internal';
102: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
103: l_date_of_birth per_all_people_f.date_of_birth%type;
104: l_business_group_id per_all_people_f.business_group_id%type;
105: l_person_type_id per_all_people_f.person_type_id%type;
106: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
99: ,p_person_id OUT NOCOPY number) IS
100: --
101: l_proc varchar2(72) := g_package||'create_candidate_internal';
102: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
103: l_date_of_birth per_all_people_f.date_of_birth%type;
104: l_business_group_id per_all_people_f.business_group_id%type;
105: l_person_type_id per_all_people_f.person_type_id%type;
106: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
107: := p_last_name_phonetic;
100: --
101: l_proc varchar2(72) := g_package||'create_candidate_internal';
102: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
103: l_date_of_birth per_all_people_f.date_of_birth%type;
104: l_business_group_id per_all_people_f.business_group_id%type;
105: l_person_type_id per_all_people_f.person_type_id%type;
106: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
107: := p_last_name_phonetic;
108: --
101: l_proc varchar2(72) := g_package||'create_candidate_internal';
102: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
103: l_date_of_birth per_all_people_f.date_of_birth%type;
104: l_business_group_id per_all_people_f.business_group_id%type;
105: l_person_type_id per_all_people_f.person_type_id%type;
106: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
107: := p_last_name_phonetic;
108: --
109: -- dummy variables
107: := p_last_name_phonetic;
108: --
109: -- dummy variables
110: --
111: l_object_version_number per_all_people_f.object_version_number%type;
112: l_effective_start_date per_all_people_f.effective_start_date%type;
113: l_effective_end_date per_all_people_f.effective_end_date%type;
114: l_full_name per_all_people_f.full_name%type;
115: l_comment_id per_all_people_f.comment_id%type;
108: --
109: -- dummy variables
110: --
111: l_object_version_number per_all_people_f.object_version_number%type;
112: l_effective_start_date per_all_people_f.effective_start_date%type;
113: l_effective_end_date per_all_people_f.effective_end_date%type;
114: l_full_name per_all_people_f.full_name%type;
115: l_comment_id per_all_people_f.comment_id%type;
116: l_name_combination_warning boolean;
109: -- dummy variables
110: --
111: l_object_version_number per_all_people_f.object_version_number%type;
112: l_effective_start_date per_all_people_f.effective_start_date%type;
113: l_effective_end_date per_all_people_f.effective_end_date%type;
114: l_full_name per_all_people_f.full_name%type;
115: l_comment_id per_all_people_f.comment_id%type;
116: l_name_combination_warning boolean;
117: l_orig_hire_warning boolean;
110: --
111: l_object_version_number per_all_people_f.object_version_number%type;
112: l_effective_start_date per_all_people_f.effective_start_date%type;
113: l_effective_end_date per_all_people_f.effective_end_date%type;
114: l_full_name per_all_people_f.full_name%type;
115: l_comment_id per_all_people_f.comment_id%type;
116: l_name_combination_warning boolean;
117: l_orig_hire_warning boolean;
118: l_assign_payroll_warning boolean;
111: l_object_version_number per_all_people_f.object_version_number%type;
112: l_effective_start_date per_all_people_f.effective_start_date%type;
113: l_effective_end_date per_all_people_f.effective_end_date%type;
114: l_full_name per_all_people_f.full_name%type;
115: l_comment_id per_all_people_f.comment_id%type;
116: l_name_combination_warning boolean;
117: l_orig_hire_warning boolean;
118: l_assign_payroll_warning boolean;
119: l_legislation_code per_business_groups_perf.legislation_code%type;
117: l_orig_hire_warning boolean;
118: l_assign_payroll_warning boolean;
119: l_legislation_code per_business_groups_perf.legislation_code%type;
120: l_ptu_person_type_id number(15);
121: l_employee_number per_all_people_f.employee_number%type := hr_api.g_varchar2;
122: --
123: -- for disabling the descriptive flex field
124: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
125: hr_dflex_utility.l_ignore_dfcode_varray();
853: ,p_effective_end_date OUT NOCOPY date
854: ,p_person_id OUT NOCOPY number) IS
855: --
856: l_proc varchar2(72) := g_package||'create_registered_user';
857: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
858: l_date_of_birth per_all_people_f.date_of_birth%type;
859: l_business_group_id per_all_people_f.business_group_id%type;
860: l_person_type_id per_all_people_f.person_type_id%type;
861: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
854: ,p_person_id OUT NOCOPY number) IS
855: --
856: l_proc varchar2(72) := g_package||'create_registered_user';
857: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
858: l_date_of_birth per_all_people_f.date_of_birth%type;
859: l_business_group_id per_all_people_f.business_group_id%type;
860: l_person_type_id per_all_people_f.person_type_id%type;
861: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
862: := p_last_name_phonetic;
855: --
856: l_proc varchar2(72) := g_package||'create_registered_user';
857: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
858: l_date_of_birth per_all_people_f.date_of_birth%type;
859: l_business_group_id per_all_people_f.business_group_id%type;
860: l_person_type_id per_all_people_f.person_type_id%type;
861: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
862: := p_last_name_phonetic;
863: --
856: l_proc varchar2(72) := g_package||'create_registered_user';
857: l_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
858: l_date_of_birth per_all_people_f.date_of_birth%type;
859: l_business_group_id per_all_people_f.business_group_id%type;
860: l_person_type_id per_all_people_f.person_type_id%type;
861: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
862: := p_last_name_phonetic;
863: --
864: -- dummy variables
862: := p_last_name_phonetic;
863: --
864: -- dummy variables
865: --
866: l_object_version_number per_all_people_f.object_version_number%type;
867: l_effective_start_date per_all_people_f.effective_start_date%type;
868: l_effective_end_date per_all_people_f.effective_end_date%type;
869: l_full_name per_all_people_f.full_name%type;
870: l_comment_id per_all_people_f.comment_id%type;
863: --
864: -- dummy variables
865: --
866: l_object_version_number per_all_people_f.object_version_number%type;
867: l_effective_start_date per_all_people_f.effective_start_date%type;
868: l_effective_end_date per_all_people_f.effective_end_date%type;
869: l_full_name per_all_people_f.full_name%type;
870: l_comment_id per_all_people_f.comment_id%type;
871: l_name_combination_warning boolean;
864: -- dummy variables
865: --
866: l_object_version_number per_all_people_f.object_version_number%type;
867: l_effective_start_date per_all_people_f.effective_start_date%type;
868: l_effective_end_date per_all_people_f.effective_end_date%type;
869: l_full_name per_all_people_f.full_name%type;
870: l_comment_id per_all_people_f.comment_id%type;
871: l_name_combination_warning boolean;
872: l_orig_hire_warning boolean;
865: --
866: l_object_version_number per_all_people_f.object_version_number%type;
867: l_effective_start_date per_all_people_f.effective_start_date%type;
868: l_effective_end_date per_all_people_f.effective_end_date%type;
869: l_full_name per_all_people_f.full_name%type;
870: l_comment_id per_all_people_f.comment_id%type;
871: l_name_combination_warning boolean;
872: l_orig_hire_warning boolean;
873: l_legislation_code per_business_groups_perf.legislation_code%type;
866: l_object_version_number per_all_people_f.object_version_number%type;
867: l_effective_start_date per_all_people_f.effective_start_date%type;
868: l_effective_end_date per_all_people_f.effective_end_date%type;
869: l_full_name per_all_people_f.full_name%type;
870: l_comment_id per_all_people_f.comment_id%type;
871: l_name_combination_warning boolean;
872: l_orig_hire_warning boolean;
873: l_legislation_code per_business_groups_perf.legislation_code%type;
874: l_ptu_person_type_id number(15);
1367: ,p_per_information30 IN varchar2 default hr_api.g_varchar2
1368: ) IS
1369: --
1370: l_proc varchar2(72) := g_package||'update_registered_user';
1371: l_object_version_number per_all_people_f.object_version_number%TYPE;
1372: l_employee_number per_all_people_f.employee_number%TYPE;
1373: l_effective_date date;
1374: l_legislation_code per_business_groups.legislation_code%type;
1375: -- sex is defaulted to null - it is set to the value of sex
1368: ) IS
1369: --
1370: l_proc varchar2(72) := g_package||'update_registered_user';
1371: l_object_version_number per_all_people_f.object_version_number%TYPE;
1372: l_employee_number per_all_people_f.employee_number%TYPE;
1373: l_effective_date date;
1374: l_legislation_code per_business_groups.legislation_code%type;
1375: -- sex is defaulted to null - it is set to the value of sex
1376: -- on per_all_people_f if p_gender exists in hr_lookups
1372: l_employee_number per_all_people_f.employee_number%TYPE;
1373: l_effective_date date;
1374: l_legislation_code per_business_groups.legislation_code%type;
1375: -- sex is defaulted to null - it is set to the value of sex
1376: -- on per_all_people_f if p_gender exists in hr_lookups
1377: l_sex per_all_people_f.sex%TYPE := hr_api.g_varchar2;
1378: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
1379: := p_last_name_phonetic;
1380: --
1373: l_effective_date date;
1374: l_legislation_code per_business_groups.legislation_code%type;
1375: -- sex is defaulted to null - it is set to the value of sex
1376: -- on per_all_people_f if p_gender exists in hr_lookups
1377: l_sex per_all_people_f.sex%TYPE := hr_api.g_varchar2;
1378: l_last_name_phonetic hz_person_profiles.person_last_name_phonetic%type
1379: := p_last_name_phonetic;
1380: --
1381: -- dummy variables
1382: --
1383: l_profile_id number;
1384: l_effective_start_date date;
1385: l_effective_end_date date;
1386: l_full_name per_all_people_f.full_name%type;
1387: l_comment_id number;
1388: l_name_combination_warning boolean;
1389: l_assign_payroll_warning boolean;
1390: l_orig_hire_warning boolean;
1398: l_add_struct_k hr_kflex_utility.l_ignore_kfcode_varray :=
1399: hr_kflex_utility.l_ignore_kfcode_varray();
1400: --
1401: --
1402: -- cursor to select entries IN per_all_people_f
1403: -- relating to registered user
1404: --
1405: cursor csr_person_id(p_person_id number,p_effective_date date) is
1406: select effective_start_date,object_version_number,employee_number
1403: -- relating to registered user
1404: --
1405: cursor csr_person_id(p_person_id number,p_effective_date date) is
1406: select effective_start_date,object_version_number,employee_number
1407: from per_all_people_f
1408: where person_id = p_person_id
1409: and l_effective_date between effective_start_date and effective_end_date;
1410: --
1411: begin
1949: cursor csr_application_count(p_vacancy_id number, p_effective_date date) is
1950: select count(*)
1951: from per_all_assignments_f paaf
1952: , IRC_AGENCY_VACANCIES AGV1
1953: ,per_all_people_f per1
1954: ,irc_notification_preferences prefs
1955: where paaf.vacancy_id = p_vacancy_id and paaf.assignment_type = 'A'
1956: and p_effective_date between PAAF.EFFECTIVE_START_DATE
1957: AND PAAF.EFFECTIVE_END_DATE
2019: ,p_assignment_id IN number
2020: ,p_application_received_date IN date default null
2021: ,p_vacancy_id IN number default null
2022: ,p_posting_content_id IN number default null
2023: ,p_per_information4 IN per_all_people_f.per_information4%type default null
2024: ,p_per_object_version_number OUT NOCOPY number
2025: ,p_asg_object_version_number OUT NOCOPY number
2026: ,p_applicant_number OUT NOCOPY varchar2) IS
2027: --
2025: ,p_asg_object_version_number OUT NOCOPY number
2026: ,p_applicant_number OUT NOCOPY varchar2) IS
2027: --
2028: l_proc varchar2(72) := g_package||'registered_user_application';
2029: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2030: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2031: l_person_id per_all_people_f.person_id%TYPE;
2032: l_party_id per_all_people_f.party_id%TYPE;
2033: l_rec_person_id per_all_people_f.person_id%TYPE;
2027: --
2028: l_proc varchar2(72) := g_package||'registered_user_application';
2029: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2030: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2031: l_person_id per_all_people_f.person_id%TYPE;
2032: l_party_id per_all_people_f.party_id%TYPE;
2033: l_rec_person_id per_all_people_f.person_id%TYPE;
2034: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2035: l_effective_date date;
2028: l_proc varchar2(72) := g_package||'registered_user_application';
2029: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2030: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2031: l_person_id per_all_people_f.person_id%TYPE;
2032: l_party_id per_all_people_f.party_id%TYPE;
2033: l_rec_person_id per_all_people_f.person_id%TYPE;
2034: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2035: l_effective_date date;
2036: l_application_received_date date;
2029: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
2030: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2031: l_person_id per_all_people_f.person_id%TYPE;
2032: l_party_id per_all_people_f.party_id%TYPE;
2033: l_rec_person_id per_all_people_f.person_id%TYPE;
2034: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2035: l_effective_date date;
2036: l_application_received_date date;
2037: l_party_last_update_date date;
2035: l_effective_date date;
2036: l_application_received_date date;
2037: l_party_last_update_date date;
2038: l_return_status varchar2(10);
2039: l_applicant_number per_all_people_f.applicant_number%type;
2040: l_person_type_id per_person_types.person_type_id%type;
2041: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2042: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2037: l_party_last_update_date date;
2038: l_return_status varchar2(10);
2039: l_applicant_number per_all_people_f.applicant_number%type;
2040: l_person_type_id per_person_types.person_type_id%type;
2041: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2042: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2038: l_return_status varchar2(10);
2039: l_applicant_number per_all_people_f.applicant_number%type;
2040: l_person_type_id per_person_types.person_type_id%type;
2041: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2042: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2039: l_applicant_number per_all_people_f.applicant_number%type;
2040: l_person_type_id per_person_types.person_type_id%type;
2041: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2042: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2041: l_current_employee_flag per_all_people_f.current_employee_flag%type;
2042: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2042: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2043: l_current_npw_flag per_all_people_f.current_npw_flag%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2044:
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2045: l_last_name per_all_people_f.last_name%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2046: l_first_name per_all_people_f.first_name%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2047: l_per_information1 per_all_people_f.per_information1%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2048: l_per_information2 per_all_people_f.per_information2%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2049: l_per_information18 per_all_people_f.per_information18%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2057: l_name_suffix per_all_people_f.suffix%type;
2050: l_per_information19 per_all_people_f.per_information19%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2057: l_name_suffix per_all_people_f.suffix%type;
2058: l_known_as per_all_people_f.known_as%type;
2051: l_date_of_birth per_all_people_f.date_of_birth%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2057: l_name_suffix per_all_people_f.suffix%type;
2058: l_known_as per_all_people_f.known_as%type;
2059: l_business_group_id per_all_vacancies.business_group_id%type;
2052: l_title per_all_people_f.title%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2057: l_name_suffix per_all_people_f.suffix%type;
2058: l_known_as per_all_people_f.known_as%type;
2059: l_business_group_id per_all_vacancies.business_group_id%type;
2060: l_organization_id per_all_vacancies.organization_id%type;
2053: l_gender per_all_people_f.sex%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2057: l_name_suffix per_all_people_f.suffix%type;
2058: l_known_as per_all_people_f.known_as%type;
2059: l_business_group_id per_all_vacancies.business_group_id%type;
2060: l_organization_id per_all_vacancies.organization_id%type;
2061: l_job_id per_all_vacancies.job_id%type;
2054: l_marital_status per_all_people_f.marital_status%type;
2055: l_previous_last_name per_all_people_f.last_name%type;
2056: l_middle_name per_all_people_f.middle_names%type;
2057: l_name_suffix per_all_people_f.suffix%type;
2058: l_known_as per_all_people_f.known_as%type;
2059: l_business_group_id per_all_vacancies.business_group_id%type;
2060: l_organization_id per_all_vacancies.organization_id%type;
2061: l_job_id per_all_vacancies.job_id%type;
2062: l_position_id per_all_vacancies.position_id%type;
2068: l_legislation_code per_business_groups.legislation_code%type;
2069: --
2070: -- variables for hr_person_api.update_person
2071: --
2072: l_object_version_number per_all_people_f.object_version_number%TYPE;
2073: l_employee_number per_all_people_f.employee_number%TYPE;
2074: --
2075: -- dummy variables
2076: --
2069: --
2070: -- variables for hr_person_api.update_person
2071: --
2072: l_object_version_number per_all_people_f.object_version_number%TYPE;
2073: l_employee_number per_all_people_f.employee_number%TYPE;
2074: --
2075: -- dummy variables
2076: --
2077: l_msg_count number;
2084: l_comment_id number;
2085: l_name_combination_warning boolean;
2086: l_assign_payroll_warning boolean;
2087: l_orig_hire_warning boolean;
2088: l_object_version_number_d per_all_people_f.object_version_number%TYPE;
2089: -- those required for hr_assignment_api.create_secondary_apl_asg
2090: l_concatenated_segments varchar2(2000);
2091: l_cagr_grade_def_id number;
2092: l_cagr_concatenated_segments varchar2(2000);
2094: l_soft_coding_keyflex_id number;
2095: l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
2096: -- those required for hr_applicant_api.create_applicant
2097: l_application_id number;
2098: l_full_name per_all_people_f.full_name%type;
2099: l_person_type per_person_types.system_person_type%type;
2100: l_ptu_person_type_id number;
2101: -- for disabling the descriptive flex field
2102: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
2109: l_time_normal_start per_business_groups.default_start_time%TYPE;
2110: l_normal_hours number;
2111: l_frequency per_business_groups.frequency%TYPE;
2112: --
2113: -- cursor to select entries IN per_all_people_f
2114: -- relating to registered user
2115: --
2116: cursor csr_person_id(p_party_id number
2117: ,p_effective_date date
2121: ,per.current_employee_flag
2122: ,per.current_applicant_flag
2123: ,per.current_npw_flag
2124: ,per.applicant_number
2125: from per_all_people_f per
2126: where per.party_id = p_party_id
2127: and p_effective_date between per.effective_start_date and per.effective_end_date
2128: and business_group_id=p_business_group_id;
2129: --
2126: where per.party_id = p_party_id
2127: and p_effective_date between per.effective_start_date and per.effective_end_date
2128: and business_group_id=p_business_group_id;
2129: --
2130: -- cursor to select entries IN per_all_people_f
2131: -- for a given person over all dates
2132: --
2133: cursor csr_person_all_dates(p_party_id number
2134: ,p_business_group_id number) is
2132: --
2133: cursor csr_person_all_dates(p_party_id number
2134: ,p_business_group_id number) is
2135: select null
2136: from per_all_people_f per
2137: where per.party_id = p_party_id
2138: and business_group_id=p_business_group_id;
2139: --
2140: -- cursor to select person details for the registered user
2155: ,per_information1
2156: ,per_information2
2157: ,per_information18
2158: ,per_information19
2159: from per_all_people_f
2160: where person_id = p_person_id
2161: and p_effective_date between effective_start_date and effective_end_date;
2162: -- cursor to select business_group relating to a vacancy
2163: cursor csr_get_vac(p_vacancy_id number) is
2840: ) IS
2841: --
2842: PRAGMA autonomous_transaction;
2843: l_proc varchar2(72) := g_package||'create_user';
2844: l_person_id per_all_people_f.person_id%type;
2845: l_user_id number;
2846: l_profile_check boolean;
2847: l_user_name fnd_user.user_name%type;
2848: l_default_last_name per_all_people_f.last_name%type;
2844: l_person_id per_all_people_f.person_id%type;
2845: l_user_id number;
2846: l_profile_check boolean;
2847: l_user_name fnd_user.user_name%type;
2848: l_default_last_name per_all_people_f.last_name%type;
2849: l_effective_start_date date;
2850: l_effective_end_date date;
2851: l_sec_profile_assignment_id number;
2852: l_business_group_id number;
3446: l_date_of_birth date;
3447:
3448: cursor get_prev_emp is
3449: select per1.party_id,count(*)
3450: from per_all_people_f per1
3451: where UPPER(per1.last_name)=UPPER(p_last_name)
3452: and ( UPPER(per1.first_name)=UPPER(nvl(p_first_name,per1.first_name))
3453: or (per1.first_name is null and p_first_name is null))
3454: and ( UPPER(per1.middle_names)= UPPER(nvl(p_middle_names,per1.middle_names))
3465: or (per1.email_address is null and p_email_address is null))
3466: and (p_home_phone_number is null
3467: or exists (select 1 from per_phones phns
3468: where phns.parent_id=per1.person_id
3469: and phns.parent_table='PER_ALL_PEOPLE_F'
3470: and phns.phone_type in ('H1','H2','H3')
3471: and phns.phone_number = p_home_phone_number))
3472: and (p_work_phone_number is null
3473: or exists (select 1 from per_phones phns
3471: and phns.phone_number = p_home_phone_number))
3472: and (p_work_phone_number is null
3473: or exists (select 1 from per_phones phns
3474: where phns.parent_id=per1.person_id
3475: and phns.parent_table='PER_ALL_PEOPLE_F'
3476: and phns.phone_type in ('W1','W2','W3')
3477: and phns.phone_number = p_work_phone_number))
3478: and (p_address_line_1 is null
3479: or exists (select 1 from per_addresses addr
3479: or exists (select 1 from per_addresses addr
3480: where addr.person_id=per1.person_id
3481: and addr.address_type in ('H','REC','HOME')))
3482: and (p_manager_last_name is null
3483: or exists (select 1 from per_all_people_f per2
3484: ,per_all_assignments_f asg2
3485: where asg2.person_id=per1.person_id
3486: and asg2.assignment_type in ('E','C')
3487: and asg2.supervisor_id=per2.person_id
3503: l_count2 number;
3504:
3505: cursor current_emp(p_party_id number) is
3506: select 1
3507: from per_all_people_f per1
3508: where per1.party_id=p_party_id
3509: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
3510: and current_employee_flag='Y';
3511:
3512: l_dummy number;
3513:
3514: cursor in_reg_bg(p_party_id number) is
3515: select per1.person_id
3516: from per_all_people_f per1
3517: where per1.party_id=p_party_id
3518: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date
3519: and per1.business_group_id = fnd_profile.value('IRC_REGISTRATION_BG_ID');
3520:
3537: and prefs.object_type='WPREF';
3538:
3539: cursor get_last_emp_rec(p_party_id number) is
3540: select per1.person_id
3541: from per_all_people_f per1
3542: where per1.party_id=p_party_id
3543: and per1.current_employee_flag='Y'
3544: and per1.effective_start_date
3545: order by per1.effective_end_date desc;
3546:
3547: cursor get_last_per_rec(p_party_id number) is
3548: select per1.person_id
3549: from per_all_people_f per1
3550: where per1.party_id=p_party_id
3551: and per1.effective_start_date
3553:
3555: select per1.business_group_id
3556: ,per1.object_version_number
3557: ,per1.employee_number
3558: ,per1.effective_start_date
3559: from per_all_people_f per1
3560: where per1.person_id=p_person_id
3561: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
3562:
3563: l_person_ovn number;
3612: l_sec_profile_id number;
3613: l_password varchar2(30);
3614: l_effective_start_date date;
3615: l_effective_end_date date;
3616: l_full_name per_all_people_f.full_name%type;
3617: l_comment_id number;
3618: l_name_combination_warning boolean;
3619: l_assign_payroll_warning boolean;
3620: l_orig_hire_warning boolean;
4312: --
4313: l_user_id number;
4314: l_person_id number;
4315: l_person_party_id number;
4316: l_first_name per_all_people_f.first_name%type;
4317: l_last_name per_all_people_f.last_name%type;
4318: l_email_address per_all_people_f.email_address%type;
4319: l_default_last_name per_all_people_f.last_name%type;
4320: --
4313: l_user_id number;
4314: l_person_id number;
4315: l_person_party_id number;
4316: l_first_name per_all_people_f.first_name%type;
4317: l_last_name per_all_people_f.last_name%type;
4318: l_email_address per_all_people_f.email_address%type;
4319: l_default_last_name per_all_people_f.last_name%type;
4320: --
4321: cursor get_person_party_info is
4314: l_person_id number;
4315: l_person_party_id number;
4316: l_first_name per_all_people_f.first_name%type;
4317: l_last_name per_all_people_f.last_name%type;
4318: l_email_address per_all_people_f.email_address%type;
4319: l_default_last_name per_all_people_f.last_name%type;
4320: --
4321: cursor get_person_party_info is
4322: select user_id, employee_id, person_party_id
4315: l_person_party_id number;
4316: l_first_name per_all_people_f.first_name%type;
4317: l_last_name per_all_people_f.last_name%type;
4318: l_email_address per_all_people_f.email_address%type;
4319: l_default_last_name per_all_people_f.last_name%type;
4320: --
4321: cursor get_person_party_info is
4322: select user_id, employee_id, person_party_id
4323: from fnd_user
4336: --
4337: cursor get_bg(p_person_id number) is
4338: select per1.business_group_id,per1.object_version_number,per1.employee_number
4339: ,per1.effective_start_date
4340: from per_all_people_f per1
4341: where per1.person_id=p_person_id
4342: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
4343: --
4344: cursor ptu_exists(p_person_id number,p_person_type_id number) is
4355: --
4356: -- dummy variables
4357: --
4358: l_tmp_resp_id number;
4359: l_object_version_number per_all_people_f.object_version_number%type;
4360: l_effective_start_date per_all_people_f.effective_start_date%type;
4361: l_effective_end_date per_all_people_f.effective_end_date%type;
4362: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4363: l_profile_check boolean;
4356: -- dummy variables
4357: --
4358: l_tmp_resp_id number;
4359: l_object_version_number per_all_people_f.object_version_number%type;
4360: l_effective_start_date per_all_people_f.effective_start_date%type;
4361: l_effective_end_date per_all_people_f.effective_end_date%type;
4362: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4363: l_profile_check boolean;
4364: l_search_criteria_id number;
4357: --
4358: l_tmp_resp_id number;
4359: l_object_version_number per_all_people_f.object_version_number%type;
4360: l_effective_start_date per_all_people_f.effective_start_date%type;
4361: l_effective_end_date per_all_people_f.effective_end_date%type;
4362: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4363: l_profile_check boolean;
4364: l_search_criteria_id number;
4365: l_sc_ovn number;
4358: l_tmp_resp_id number;
4359: l_object_version_number per_all_people_f.object_version_number%type;
4360: l_effective_start_date per_all_people_f.effective_start_date%type;
4361: l_effective_end_date per_all_people_f.effective_end_date%type;
4362: l_new_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
4363: l_profile_check boolean;
4364: l_search_criteria_id number;
4365: l_sc_ovn number;
4366: l_notif_preference_id number;
4375: l_person_ovn number;
4376: l_employee_number varchar2(255);
4377: l_person_start_date date;
4378: l_dt_mode varchar2(30);
4379: l_full_name per_all_people_f.full_name%type;
4380: l_comment_id number;
4381: l_name_combination_warning boolean;
4382: l_assign_payroll_warning boolean;
4383: l_orig_hire_warning boolean;
4695: where user_name=upper(p_user_name);
4696: --
4697: cursor get_bg(p_person_id number) is
4698: select per1.business_group_id
4699: from per_all_people_f per1
4700: where per1.person_id=p_person_id
4701: and trunc(sysdate) between per1.effective_start_date and per1.effective_end_date;
4702: --
4703: cursor ptu_exists(p_person_id number,p_person_type_id number) is
5191: ,p_per_information27 IN varchar2 default null
5192: ,p_per_information28 IN varchar2 default null
5193: ,p_per_information29 IN varchar2 default null
5194: ,p_per_information30 IN varchar2 default null
5195: ,p_person_id IN per_all_people_f.person_id%type default null
5196: ) IS
5197: --
5198: PRAGMA autonomous_transaction;
5199: l_proc varchar2(72) := g_package||'create_user_internal_byReferral';
5196: ) IS
5197: --
5198: PRAGMA autonomous_transaction;
5199: l_proc varchar2(72) := g_package||'create_user_internal_byReferral';
5200: l_person_id per_all_people_f.person_id%type;
5201: l_user_id number;
5202: l_profile_check boolean;
5203: l_user_name fnd_user.user_name%type;
5204: l_default_last_name per_all_people_f.last_name%type;
5200: l_person_id per_all_people_f.person_id%type;
5201: l_user_id number;
5202: l_profile_check boolean;
5203: l_user_name fnd_user.user_name%type;
5204: l_default_last_name per_all_people_f.last_name%type;
5205: l_effective_start_date date;
5206: l_effective_end_date date;
5207: l_sec_profile_assignment_id number;
5208: l_business_group_id number;
5605: --
5606: -- Cursor declarations
5607: cursor csr_appl is
5608: select 1
5609: from per_all_people_f ppf,
5610: per_all_assignments_f paf
5611: where ppf.party_id = p_party_id
5612: and paf.person_id = ppf.person_id
5613: and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date;
5666: --
5667: -- Cursor Declarations
5668: cursor csr_offers is
5669: select paf.assignment_id
5670: from per_all_people_f ppf,
5671: per_all_assignments_f paf,
5672: irc_offers iof,
5673: irc_offer_status_history history
5674: where ppf.party_id = p_party_id
5717: --
5718: -- Cursor declarations
5719: Cursor csr_view_all_apl_count is
5720: select count(pav.vacancy_id)
5721: from per_all_people_f ppf,
5722: per_all_assignments_f paf,
5723: per_all_vacancies pav
5724: where ppf.party_id = p_party_id
5725: and paf.person_id = ppf.person_id
5771: -- Cursor declarations
5772: --
5773: cursor csr_master_new is
5774: select 1
5775: from per_all_people_f ppf_source
5776: , per_all_people_f ppf_target
5777: where ppf_source.party_id = p_souce_party_id
5778: and ppf_target.party_id = p_target_party_id
5779: and ppf_target.effective_start_date > ppf_source.effective_start_date
5772: --
5773: cursor csr_master_new is
5774: select 1
5775: from per_all_people_f ppf_source
5776: , per_all_people_f ppf_target
5777: where ppf_source.party_id = p_souce_party_id
5778: and ppf_target.party_id = p_target_party_id
5779: and ppf_target.effective_start_date > ppf_source.effective_start_date
5780: and ppf_target.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_target.party_id)
5776: , per_all_people_f ppf_target
5777: where ppf_source.party_id = p_souce_party_id
5778: and ppf_target.party_id = p_target_party_id
5779: and ppf_target.effective_start_date > ppf_source.effective_start_date
5780: and ppf_target.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_target.party_id)
5781: and ppf_source.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_source.party_id);
5782: -- Variable declarations
5783: l_dummy number;
5784: begin
5777: where ppf_source.party_id = p_souce_party_id
5778: and ppf_target.party_id = p_target_party_id
5779: and ppf_target.effective_start_date > ppf_source.effective_start_date
5780: and ppf_target.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_target.party_id)
5781: and ppf_source.effective_start_date = (select min(effective_start_date) from per_all_people_f where party_id = ppf_source.party_id);
5782: -- Variable declarations
5783: l_dummy number;
5784: begin
5785: --
5805: --
5806: -- Cursor declarations
5807: cursor csr_applications is
5808: select paf.assignment_id
5809: from per_all_people_f ppf,
5810: per_all_assignments_f paf
5811: where ppf.party_id = p_target_party_id
5812: and paf.person_id = ppf.person_id
5813: and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
5811: where ppf.party_id = p_target_party_id
5812: and paf.person_id = ppf.person_id
5813: and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
5814: and exists (select null
5815: from per_all_people_f ppf1,
5816: per_all_assignments_f paf1
5817: where ppf1.party_id = p_souce_party_id
5818: and paf1.person_id = ppf1.person_id
5819: and trunc(sysdate) between paf1.effective_start_date and paf1.effective_end_date
5844: --
5845: -- Cursor declarations
5846: cursor csr_get_persons_for_party (c_party_id number) is
5847: select person_id
5848: from per_all_people_f
5849: where party_id = c_party_id
5850: and trunc(sysdate) between effective_start_date and effective_end_date;
5851: --
5852: cursor csr_sys_person_type(c_person_id number) is
5850: and trunc(sysdate) between effective_start_date and effective_end_date;
5851: --
5852: cursor csr_sys_person_type(c_person_id number) is
5853: select pet.system_person_type
5854: from per_all_people_f per,
5855: per_person_types pet
5856: where per.person_type_id = pet.person_type_id
5857: and per.person_id = c_person_id
5858: and trunc(sysdate) between per.effective_start_date and per.effective_end_date;
5909: -- Cursor declarations
5910: --
5911: Cursor csr_created is
5912: Select 1
5913: From PER_ALL_PEOPLE_F ppf
5914: Where trunc(sysdate) = (select min(effective_start_date) from per_all_people_f where party_id=ppf.party_id)
5915: AND ppf.party_id=p_party_id;
5916: --Variable declarations
5917: l_dummy number;
5910: --
5911: Cursor csr_created is
5912: Select 1
5913: From PER_ALL_PEOPLE_F ppf
5914: Where trunc(sysdate) = (select min(effective_start_date) from per_all_people_f where party_id=ppf.party_id)
5915: AND ppf.party_id=p_party_id;
5916: --Variable declarations
5917: l_dummy number;
5918: begin
6163: ,p_asg_id OUT NOCOPY number ) is
6164: PRAGMA autonomous_transaction;
6165: --
6166: l_proc varchar2(72) := g_package||'ofr_reg_user_application';
6167: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
6168: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
6169: l_person_id per_all_people_f.person_id%TYPE;
6170: l_party_id per_all_people_f.party_id%TYPE;
6171: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6165: --
6166: l_proc varchar2(72) := g_package||'ofr_reg_user_application';
6167: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
6168: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
6169: l_person_id per_all_people_f.person_id%TYPE;
6170: l_party_id per_all_people_f.party_id%TYPE;
6171: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6172: l_application_received_date date;
6173: l_party_last_update_date date;
6166: l_proc varchar2(72) := g_package||'ofr_reg_user_application';
6167: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
6168: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
6169: l_person_id per_all_people_f.person_id%TYPE;
6170: l_party_id per_all_people_f.party_id%TYPE;
6171: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6172: l_application_received_date date;
6173: l_party_last_update_date date;
6174: l_applicant_number per_all_people_f.applicant_number%type;
6170: l_party_id per_all_people_f.party_id%TYPE;
6171: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6172: l_application_received_date date;
6173: l_party_last_update_date date;
6174: l_applicant_number per_all_people_f.applicant_number%type;
6175: l_person_type_id per_person_types.person_type_id%type;
6176: l_current_employee_flag per_all_people_f.current_employee_flag%type;
6177: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6172: l_application_received_date date;
6173: l_party_last_update_date date;
6174: l_applicant_number per_all_people_f.applicant_number%type;
6175: l_person_type_id per_person_types.person_type_id%type;
6176: l_current_employee_flag per_all_people_f.current_employee_flag%type;
6177: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6173: l_party_last_update_date date;
6174: l_applicant_number per_all_people_f.applicant_number%type;
6175: l_person_type_id per_person_types.person_type_id%type;
6176: l_current_employee_flag per_all_people_f.current_employee_flag%type;
6177: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6174: l_applicant_number per_all_people_f.applicant_number%type;
6175: l_person_type_id per_person_types.person_type_id%type;
6176: l_current_employee_flag per_all_people_f.current_employee_flag%type;
6177: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6176: l_current_employee_flag per_all_people_f.current_employee_flag%type;
6177: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6177: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6178: l_current_npw_flag per_all_people_f.current_npw_flag%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6179: --
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6180: l_last_name per_all_people_f.last_name%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6181: l_first_name per_all_people_f.first_name%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6182: l_per_information1 per_all_people_f.per_information1%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6183: l_per_information2 per_all_people_f.per_information2%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6184: l_per_information18 per_all_people_f.per_information18%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6192: l_name_suffix per_all_people_f.suffix%type;
6185: l_per_information19 per_all_people_f.per_information19%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6192: l_name_suffix per_all_people_f.suffix%type;
6193: l_known_as per_all_people_f.known_as%type;
6186: l_date_of_birth per_all_people_f.date_of_birth%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6192: l_name_suffix per_all_people_f.suffix%type;
6193: l_known_as per_all_people_f.known_as%type;
6194: l_job_id per_all_vacancies.job_id%type;
6187: l_title per_all_people_f.title%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6192: l_name_suffix per_all_people_f.suffix%type;
6193: l_known_as per_all_people_f.known_as%type;
6194: l_job_id per_all_vacancies.job_id%type;
6195: l_position_id per_all_vacancies.position_id%type;
6188: l_gender per_all_people_f.sex%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6192: l_name_suffix per_all_people_f.suffix%type;
6193: l_known_as per_all_people_f.known_as%type;
6194: l_job_id per_all_vacancies.job_id%type;
6195: l_position_id per_all_vacancies.position_id%type;
6196: l_grade_id per_all_vacancies.grade_id%type;
6189: l_marital_status per_all_people_f.marital_status%type;
6190: l_previous_last_name per_all_people_f.last_name%type;
6191: l_middle_name per_all_people_f.middle_names%type;
6192: l_name_suffix per_all_people_f.suffix%type;
6193: l_known_as per_all_people_f.known_as%type;
6194: l_job_id per_all_vacancies.job_id%type;
6195: l_position_id per_all_vacancies.position_id%type;
6196: l_grade_id per_all_vacancies.grade_id%type;
6197: l_location_id per_all_vacancies.location_id%type;
6201: l_effective_date date;
6202: --
6203: -- variables for hr_person_api.update_person
6204: --
6205: l_object_version_number per_all_people_f.object_version_number%TYPE;
6206: --
6207: -- dummy variables
6208: --
6209: l_var varchar2(1);
6211: l_effective_end_date date;
6212: l_comment_id number;
6213: l_name_combination_warning boolean;
6214: l_orig_hire_warning boolean;
6215: l_object_version_number_d per_all_people_f.object_version_number%TYPE;
6216: -- those required for hr_assignment_api.create_secondary_apl_asg
6217: l_concatenated_segments varchar2(2000);
6218: l_cagr_grade_def_id number;
6219: l_cagr_concatenated_segments varchar2(2000);
6221: l_soft_coding_keyflex_id number;
6222: l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
6223: -- those required for hr_applicant_api.create_applicant
6224: l_application_id number;
6225: l_full_name per_all_people_f.full_name%type;
6226: l_person_type per_person_types.system_person_type%type;
6227: l_ptu_person_type_id number;
6228: -- for disabling the descriptive flex field
6229: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
6235: l_time_normal_finish per_business_groups.default_end_time%TYPE;
6236: l_time_normal_start per_business_groups.default_start_time%TYPE;
6237: l_normal_hours number;
6238: l_frequency per_business_groups.frequency%TYPE;
6239: l_per_information4 per_all_people_f.per_information4%type;
6240:
6241: --
6242: -- cursor to select entries IN per_all_people_f
6243: -- relating to registered user
6238: l_frequency per_business_groups.frequency%TYPE;
6239: l_per_information4 per_all_people_f.per_information4%type;
6240:
6241: --
6242: -- cursor to select entries IN per_all_people_f
6243: -- relating to registered user
6244: --
6245: cursor csr_person_id(p_party_id number
6246: ,p_effective_date date
6250: ,per.current_employee_flag
6251: ,per.current_applicant_flag
6252: ,per.current_npw_flag
6253: ,per.applicant_number
6254: from per_all_people_f per
6255: where per.party_id = p_party_id
6256: and p_effective_date between per.effective_start_date and per.effective_end_date
6257: and business_group_id = p_business_group_id;
6258: --
6255: where per.party_id = p_party_id
6256: and p_effective_date between per.effective_start_date and per.effective_end_date
6257: and business_group_id = p_business_group_id;
6258: --
6259: -- cursor to select entries IN per_all_people_f
6260: -- for a given person over all dates
6261: --
6262: cursor csr_person_all_dates(p_party_id number
6263: ,p_business_group_id number) is
6261: --
6262: cursor csr_person_all_dates(p_party_id number
6263: ,p_business_group_id number) is
6264: select null
6265: from per_all_people_f per
6266: where per.party_id = p_party_id
6267: and business_group_id=p_business_group_id;
6268: --
6269: --
6285: ,per_information1
6286: ,per_information2
6287: ,per_information18
6288: ,per_information19
6289: from per_all_people_f
6290: where person_id = p_person_id
6291: and p_effective_date between effective_start_date and effective_end_date;
6292: --
6293: cursor get_new_asg(p_person_id number, p_effective_date date) is