[Home] [Help]
300: --
301: l_proc varchar2(72);
302: l_person_type_id per_person_types.person_type_id%type := p_person_type_id;
303: l_person_type_id1 per_person_types.person_type_id%type;
304: l_person_id per_people_f.person_id%type;
305: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
306: l_employee_number per_people_f.employee_number%type;
307: l_emp_num per_people_f.employee_number%type := p_employee_number;
308: l_applicant_number per_people_f.applicant_number%TYPE;
302: l_person_type_id per_person_types.person_type_id%type := p_person_type_id;
303: l_person_type_id1 per_person_types.person_type_id%type;
304: l_person_id per_people_f.person_id%type;
305: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
306: l_employee_number per_people_f.employee_number%type;
307: l_emp_num per_people_f.employee_number%type := p_employee_number;
308: l_applicant_number per_people_f.applicant_number%TYPE;
309: l_npw_number per_people_f.npw_number%TYPE;
310: l_assignment_sequence per_assignments_f.assignment_sequence%type;
303: l_person_type_id1 per_person_types.person_type_id%type;
304: l_person_id per_people_f.person_id%type;
305: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
306: l_employee_number per_people_f.employee_number%type;
307: l_emp_num per_people_f.employee_number%type := p_employee_number;
308: l_applicant_number per_people_f.applicant_number%TYPE;
309: l_npw_number per_people_f.npw_number%TYPE;
310: l_assignment_sequence per_assignments_f.assignment_sequence%type;
311: l_assignment_number per_assignments_f.assignment_number%type;
304: l_person_id per_people_f.person_id%type;
305: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
306: l_employee_number per_people_f.employee_number%type;
307: l_emp_num per_people_f.employee_number%type := p_employee_number;
308: l_applicant_number per_people_f.applicant_number%TYPE;
309: l_npw_number per_people_f.npw_number%TYPE;
310: l_assignment_sequence per_assignments_f.assignment_sequence%type;
311: l_assignment_number per_assignments_f.assignment_number%type;
312: l_hire_date date;
305: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
306: l_employee_number per_people_f.employee_number%type;
307: l_emp_num per_people_f.employee_number%type := p_employee_number;
308: l_applicant_number per_people_f.applicant_number%TYPE;
309: l_npw_number per_people_f.npw_number%TYPE;
310: l_assignment_sequence per_assignments_f.assignment_sequence%type;
311: l_assignment_number per_assignments_f.assignment_number%type;
312: l_hire_date date;
313: l_discard_number number;
317: l_per_object_version_number number;
318: l_asg_object_version_number number;
319: l_per_effective_start_date date;
320: l_per_effective_end_date date;
321: l_full_name per_people_f.full_name%type;
322: l_per_comment_id number;
323: l_name_combination_warning boolean;
324: l_assign_payroll_warning boolean;
325: l_orig_hire_warning boolean;
2839: --
2840: -- declare local variables
2841: --
2842: l_proc varchar2(72) := g_package||'re_hire_ex_employee';
2843: l_business_group_id per_people_f.business_group_id%type;
2844: l_ovn per_people_f.object_version_number%type := p_per_object_version_number;
2845: l_name_combination_warning boolean;
2846: l_orig_hire_warning boolean;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2840: -- declare local variables
2841: --
2842: l_proc varchar2(72) := g_package||'re_hire_ex_employee';
2843: l_business_group_id per_people_f.business_group_id%type;
2844: l_ovn per_people_f.object_version_number%type := p_per_object_version_number;
2845: l_name_combination_warning boolean;
2846: l_orig_hire_warning boolean;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2843: l_business_group_id per_people_f.business_group_id%type;
2844: l_ovn per_people_f.object_version_number%type := p_per_object_version_number;
2845: l_name_combination_warning boolean;
2846: l_orig_hire_warning boolean;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2844: l_ovn per_people_f.object_version_number%type := p_per_object_version_number;
2845: l_name_combination_warning boolean;
2846: l_orig_hire_warning boolean;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2845: l_name_combination_warning boolean;
2846: l_orig_hire_warning boolean;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2846: l_orig_hire_warning boolean;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2847: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2848: l_person_type_id1 per_people_f.person_type_id%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2856: l_full_name per_people_f.full_name%type;
2849: l_comment_id per_people_f.comment_id%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2856: l_full_name per_people_f.full_name%type;
2857: l_object_version_number per_people_f.object_version_number%type;
2850: l_current_applicant_flag per_people_f.current_applicant_flag%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2856: l_full_name per_people_f.full_name%type;
2857: l_object_version_number per_people_f.object_version_number%type;
2858: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
2851: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2856: l_full_name per_people_f.full_name%type;
2857: l_object_version_number per_people_f.object_version_number%type;
2858: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
2859: l_pds_object_version_number per_periods_of_service.object_version_number%type;
2852: l_current_employee_flag per_people_f.current_employee_flag%type;
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2856: l_full_name per_people_f.full_name%type;
2857: l_object_version_number per_people_f.object_version_number%type;
2858: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
2859: l_pds_object_version_number per_periods_of_service.object_version_number%type;
2860: l_datetrack_mode varchar2(12);
2853: l_employee_number per_people_f.employee_number%type;
2854: l_applicant_number per_people_f.applicant_number%TYPE;
2855: l_npw_number per_people_f.npw_number%type;
2856: l_full_name per_people_f.full_name%type;
2857: l_object_version_number per_people_f.object_version_number%type;
2858: l_period_of_service_id per_periods_of_service.period_of_service_id%type;
2859: l_pds_object_version_number per_periods_of_service.object_version_number%type;
2860: l_datetrack_mode varchar2(12);
2861: l_effective_date date;
2928: per.employee_number,
2929: per.effective_start_date,
2930: pet.system_person_type
2931: from per_person_types pet,
2932: per_people_f per
2933: where per.person_id = p_person_id
2934: and pet.person_type_id = per.person_type_id
2935: and pet.business_group_id + 0 = per.business_group_id
2936: and p_effective_date
2940: per.employee_number,
2941: per.effective_start_date,
2942: pet.system_person_type
2943: from per_person_types pet,
2944: per_people_f per,
2945: per_person_type_usages_f ptu
2946: where per.person_id = p_person_id
2947: and pet.person_type_id = ptu.person_type_id
2948: and ptu.person_id = per.person_id
3002: -- This procedure controls and performs the following business process
3003: -- validation:
3004: -- 1) ensure that the p_person_id and p_hire_date parameters are not null.
3005: -- 2) check that this person (p_person_id) exists as of p_hire_date and
3006: -- the current person type (per_people_f.person_type_id) has a
3007: -- corresponding system person type of EX_EMP.
3008: -- 3) ensure the most recent period of service for this person has been
3009: -- completely terminated.
3010: --
3018: --
3019: l_proc varchar2(72) := g_package||'perform_validation';
3020: l_system_person_type per_person_types.system_person_type%type;
3021: l_final_process_date per_periods_of_service.final_process_date%type;
3022: l_effective_start_date per_people_f.effective_start_date%type;
3023: l_dummy_number number;
3024: l_dummy_emp_number varchar2(30);
3025: l_dummy_date date;
3026: --
3051: (p_person_id IN per_all_people_f.person_id%TYPE
3052: ,p_effective_date IN DATE
3053: ) IS
3054: SELECT bus.legislation_code
3055: FROM per_people_f per
3056: ,per_business_groups bus
3057: WHERE per.person_id = csr_per_legislation.p_person_id
3058: AND per.business_group_id+0 = bus.business_group_id
3059: AND csr_per_legislation.p_effective_date BETWEEN per.effective_start_date
3082: ,p_argument => 'hire date'
3083: ,p_argument_value => p_hire_date);
3084: --
3085: -- 2. check that this person (p_person_id) exists as of p_hire_date and the
3086: -- the current person type (per_people_f.person_type_id) has a
3087: -- corresponding system person type of EX_EMP.
3088: --
3089: get_person_details
3090: (p_person_id => p_person_id,
3603: --
3604: -- declare local variables
3605: --
3606: l_proc varchar2(72) := g_package||'apply_for_internal_vacancy';
3607: l_business_group_id per_people_f.business_group_id%type;
3608: l_name_combination_warning boolean;
3609: l_dob_null_warning boolean;
3610: l_orig_hire_warning boolean;
3611: l_organization_id per_business_groups.organization_id%type;
3609: l_dob_null_warning boolean;
3610: l_orig_hire_warning boolean;
3611: l_organization_id per_business_groups.organization_id%type;
3612: l_legislation_code per_business_groups.legislation_code%type;
3613: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
3614: l_person_type_id1 per_people_f.person_type_id%type;
3615: l_application_id per_applications.application_id%type;
3616: l_comment_id per_assignments_f.comment_id%type;
3617: l_assignment_sequence per_assignments_f.assignment_sequence%type;
3610: l_orig_hire_warning boolean;
3611: l_organization_id per_business_groups.organization_id%type;
3612: l_legislation_code per_business_groups.legislation_code%type;
3613: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
3614: l_person_type_id1 per_people_f.person_type_id%type;
3615: l_application_id per_applications.application_id%type;
3616: l_comment_id per_assignments_f.comment_id%type;
3617: l_assignment_sequence per_assignments_f.assignment_sequence%type;
3618: l_assignment_id per_assignments_f.assignment_id%type;
3616: l_comment_id per_assignments_f.comment_id%type;
3617: l_assignment_sequence per_assignments_f.assignment_sequence%type;
3618: l_assignment_id per_assignments_f.assignment_id%type;
3619: l_object_version_number per_assignments_f.object_version_number%type;
3620: l_current_applicant_flag per_people_f.current_applicant_flag%type;
3621: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3617: l_assignment_sequence per_assignments_f.assignment_sequence%type;
3618: l_assignment_id per_assignments_f.assignment_id%type;
3619: l_object_version_number per_assignments_f.object_version_number%type;
3620: l_current_applicant_flag per_people_f.current_applicant_flag%type;
3621: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3618: l_assignment_id per_assignments_f.assignment_id%type;
3619: l_object_version_number per_assignments_f.object_version_number%type;
3620: l_current_applicant_flag per_people_f.current_applicant_flag%type;
3621: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3626: l_per_object_version_number per_people_f.object_version_number%TYPE;
3619: l_object_version_number per_assignments_f.object_version_number%type;
3620: l_current_applicant_flag per_people_f.current_applicant_flag%type;
3621: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3626: l_per_object_version_number per_people_f.object_version_number%TYPE;
3627: l_full_name per_people_f.full_name%type;
3620: l_current_applicant_flag per_people_f.current_applicant_flag%type;
3621: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3626: l_per_object_version_number per_people_f.object_version_number%TYPE;
3627: l_full_name per_people_f.full_name%type;
3628: l_system_person_type per_person_types.system_person_type%type;
3621: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3626: l_per_object_version_number per_people_f.object_version_number%TYPE;
3627: l_full_name per_people_f.full_name%type;
3628: l_system_person_type per_person_types.system_person_type%type;
3629: l_effective_date date;
3622: l_current_employee_flag per_people_f.current_employee_flag%type;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3626: l_per_object_version_number per_people_f.object_version_number%TYPE;
3627: l_full_name per_people_f.full_name%type;
3628: l_system_person_type per_person_types.system_person_type%type;
3629: l_effective_date date;
3630: l_effective_start_date date;
3623: l_employee_number per_people_f.employee_number%type;
3624: l_applicant_number per_people_f.applicant_number%TYPE;
3625: l_npw_number per_people_f.npw_number%TYPE;
3626: l_per_object_version_number per_people_f.object_version_number%TYPE;
3627: l_full_name per_people_f.full_name%type;
3628: l_system_person_type per_person_types.system_person_type%type;
3629: l_effective_date date;
3630: l_effective_start_date date;
3631: l_effective_end_date date;
3662: ,ppf.employee_number
3663: ,ppf.npw_number
3664: ,ppt.system_person_type
3665: from per_person_types ppt
3666: ,per_people_f ppf
3667: where ppf.person_id = p_person_id
3668: and ppt.person_type_id = ppf.person_type_id
3669: and ppt.business_group_id + 0 = ppf.business_group_id
3670: and l_effective_date
3744: hr_utility.set_location(' Leaving:'||l_proc, 10);
3745: end if;
3746: --
3747: -- Check that this person (p_person_id) exists as of l_effective_date
3748: -- the current person type (per_people_f.person_type_id) has a
3749: -- corresponding system person type of EMP.
3750: --
3751: open csr_chk_person_exists;
3752: fetch csr_chk_person_exists into
4163: ,per.employee_number
4164: ,per.npw_number
4165: ,bus.business_group_id
4166: ,bus.legislation_code
4167: FROM per_people_f per
4168: ,per_business_groups bus
4169: ,per_person_types pet
4170: WHERE per.person_type_id = pet.person_type_id
4171: AND per.business_group_id+0 = bus.business_group_id