[Home] [Help]
8: --
9: -- Package cursor
10: --
11: CURSOR csr_future_asgs
12: (p_person_id IN per_all_people_f.person_id%TYPE
13: ,p_effective_date IN DATE
14: )
15: IS
16: SELECT asg.assignment_id
49: --
50: -- {End Of Comments}
51: --
52: FUNCTION future_asgs_count
53: (p_person_id IN per_all_people_f.person_id%TYPE
54: ,p_effective_date IN DATE
55: )
56: RETURN INTEGER
57: IS
228: -- Declare cursors and local variables
229: --
230: l_proc varchar2(72) := g_package||'create_applicant';
231: l_date_received per_applications.date_received%TYPE;
232: l_applicant_number per_all_people_f.applicant_number%TYPE;
233: l_date_employee_data_verified per_all_people_f.date_employee_data_verified%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
229: --
230: l_proc varchar2(72) := g_package||'create_applicant';
231: l_date_received per_applications.date_received%TYPE;
232: l_applicant_number per_all_people_f.applicant_number%TYPE;
233: l_date_employee_data_verified per_all_people_f.date_employee_data_verified%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
230: l_proc varchar2(72) := g_package||'create_applicant';
231: l_date_received per_applications.date_received%TYPE;
232: l_applicant_number per_all_people_f.applicant_number%TYPE;
233: l_date_employee_data_verified per_all_people_f.date_employee_data_verified%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
231: l_date_received per_applications.date_received%TYPE;
232: l_applicant_number per_all_people_f.applicant_number%TYPE;
233: l_date_employee_data_verified per_all_people_f.date_employee_data_verified%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
239: l_person_id per_all_people_f.person_id%TYPE;
232: l_applicant_number per_all_people_f.applicant_number%TYPE;
233: l_date_employee_data_verified per_all_people_f.date_employee_data_verified%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
239: l_person_id per_all_people_f.person_id%TYPE;
240: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
233: l_date_employee_data_verified per_all_people_f.date_employee_data_verified%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
239: l_person_id per_all_people_f.person_id%TYPE;
240: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
241: l_application_id per_applications.application_id%TYPE;
234: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
239: l_person_id per_all_people_f.person_id%TYPE;
240: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
241: l_application_id per_applications.application_id%TYPE;
242: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
235: l_background_date_check per_all_people_f.background_date_check%TYPE;
236: l_hold_applicant_date_until per_all_people_f.hold_applicant_date_until%TYPE;
237: l_projected_start_date per_all_people_f.projected_start_date%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
239: l_person_id per_all_people_f.person_id%TYPE;
240: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
241: l_application_id per_applications.application_id%TYPE;
242: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
243: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
238: l_resume_last_updated per_all_people_f.resume_last_updated%TYPE;
239: l_person_id per_all_people_f.person_id%TYPE;
240: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
241: l_application_id per_applications.application_id%TYPE;
242: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
243: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
244: l_apl_object_version_number per_applications.object_version_number%TYPE;
245: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
241: l_application_id per_applications.application_id%TYPE;
242: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
243: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
244: l_apl_object_version_number per_applications.object_version_number%TYPE;
245: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
247: l_full_name per_all_people_f.full_name%TYPE;
248: l_per_comment_id per_all_people_f.comment_id%TYPE;
249: l_employee_number per_all_people_f.employee_number%TYPE;
242: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
243: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
244: l_apl_object_version_number per_applications.object_version_number%TYPE;
245: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
247: l_full_name per_all_people_f.full_name%TYPE;
248: l_per_comment_id per_all_people_f.comment_id%TYPE;
249: l_employee_number per_all_people_f.employee_number%TYPE;
250: l_npw_number per_all_people_f.npw_number%TYPE;
243: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
244: l_apl_object_version_number per_applications.object_version_number%TYPE;
245: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
247: l_full_name per_all_people_f.full_name%TYPE;
248: l_per_comment_id per_all_people_f.comment_id%TYPE;
249: l_employee_number per_all_people_f.employee_number%TYPE;
250: l_npw_number per_all_people_f.npw_number%TYPE;
251: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
244: l_apl_object_version_number per_applications.object_version_number%TYPE;
245: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
247: l_full_name per_all_people_f.full_name%TYPE;
248: l_per_comment_id per_all_people_f.comment_id%TYPE;
249: l_employee_number per_all_people_f.employee_number%TYPE;
250: l_npw_number per_all_people_f.npw_number%TYPE;
251: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
252: l_name_combination_warning boolean;
245: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
247: l_full_name per_all_people_f.full_name%TYPE;
248: l_per_comment_id per_all_people_f.comment_id%TYPE;
249: l_employee_number per_all_people_f.employee_number%TYPE;
250: l_npw_number per_all_people_f.npw_number%TYPE;
251: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
252: l_name_combination_warning boolean;
253: l_orig_hire_warning boolean;
246: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
247: l_full_name per_all_people_f.full_name%TYPE;
248: l_per_comment_id per_all_people_f.comment_id%TYPE;
249: l_employee_number per_all_people_f.employee_number%TYPE;
250: l_npw_number per_all_people_f.npw_number%TYPE;
251: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
252: l_name_combination_warning boolean;
253: l_orig_hire_warning boolean;
254: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
250: l_npw_number per_all_people_f.npw_number%TYPE;
251: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
252: l_name_combination_warning boolean;
253: l_orig_hire_warning boolean;
254: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
255: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
251: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
252: l_name_combination_warning boolean;
253: l_orig_hire_warning boolean;
254: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
255: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
252: l_name_combination_warning boolean;
253: l_orig_hire_warning boolean;
254: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
255: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
253: l_orig_hire_warning boolean;
254: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
255: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
261: l_person_type_id per_all_people_f.person_type_id%TYPE;
254: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
255: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
261: l_person_type_id per_all_people_f.person_type_id%TYPE;
262: l_person_type_id1 per_all_people_f.person_type_id%TYPE;
255: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
261: l_person_type_id per_all_people_f.person_type_id%TYPE;
262: l_person_type_id1 per_all_people_f.person_type_id%TYPE;
263: l_dob_null_warning boolean;
256: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
261: l_person_type_id per_all_people_f.person_type_id%TYPE;
262: l_person_type_id1 per_all_people_f.person_type_id%TYPE;
263: l_dob_null_warning boolean;
264: l_phn_object_version_number per_phones.object_version_number%TYPE;
257: l_date_of_death per_all_people_f.date_of_death%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
261: l_person_type_id per_all_people_f.person_type_id%TYPE;
262: l_person_type_id1 per_all_people_f.person_type_id%TYPE;
263: l_dob_null_warning boolean;
264: l_phn_object_version_number per_phones.object_version_number%TYPE;
265: l_phone_id per_phones.phone_id%TYPE;
258: l_receipt_of_death_cert_date per_all_people_f.receipt_of_death_cert_date%TYPE;
259: l_dpdnt_adoption_date per_all_people_f.dpdnt_adoption_date%TYPE;
260: l_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
261: l_person_type_id per_all_people_f.person_type_id%TYPE;
262: l_person_type_id1 per_all_people_f.person_type_id%TYPE;
263: l_dob_null_warning boolean;
264: l_phn_object_version_number per_phones.object_version_number%TYPE;
265: l_phone_id per_phones.phone_id%TYPE;
266: --
622: ,p_date_to => null
623: ,p_phone_type => 'W1'
624: ,p_phone_number => p_work_telephone
625: ,p_parent_id => l_person_id
626: ,p_parent_table => 'PER_ALL_PEOPLE_F'
627: ,p_validate => FALSE
628: ,p_effective_date => l_date_received
629: ,p_object_version_number => l_phn_object_version_number --out
630: ,p_phone_id => l_phone_id --out
788: -- Start of fix for bug 3062967
789: --
790: SELECT object_version_number
791: INTO p_per_object_Version_number
792: FROM per_all_people_f
793: WHERE person_id = l_person_id
794: And effective_start_Date = l_per_effective_start_date
795: and effective_end_Date = l_per_effective_end_date;
796: --
1404: -- base release
1405: procedure hire_applicant
1406: (p_validate in boolean , --default false,
1407: p_hire_date in date,
1408: p_person_id in per_all_people_f.person_id%TYPE,
1409: p_assignment_id in number, --default null,
1410: p_person_type_id in number, --default null,
1411: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1412: p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1407: p_hire_date in date,
1408: p_person_id in per_all_people_f.person_id%TYPE,
1409: p_assignment_id in number, --default null,
1410: p_person_type_id in number, --default null,
1411: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1412: p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1413: p_per_effective_start_date out nocopy date,
1414: p_per_effective_end_date out nocopy date,
1415: p_unaccepted_asg_del_warning out nocopy boolean,
1408: p_person_id in per_all_people_f.person_id%TYPE,
1409: p_assignment_id in number, --default null,
1410: p_person_type_id in number, --default null,
1411: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1412: p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1413: p_per_effective_start_date out nocopy date,
1414: p_per_effective_end_date out nocopy date,
1415: p_unaccepted_asg_del_warning out nocopy boolean,
1416: p_assign_payroll_warning out nocopy boolean,
1423: -- Declare cursors and local variables
1424: --
1425: l_proc varchar2(72) := g_package||'hire__applicant';
1426: --
1427: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1428: l_employee_number per_all_people_f.employee_number%TYPE;
1429: l_per_effective_start_date date;
1430: l_per_effective_end_date date;
1431: l_unaccepted_asg_del_warning boolean;
1424: --
1425: l_proc varchar2(72) := g_package||'hire__applicant';
1426: --
1427: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1428: l_employee_number per_all_people_f.employee_number%TYPE;
1429: l_per_effective_start_date date;
1430: l_per_effective_end_date date;
1431: l_unaccepted_asg_del_warning boolean;
1432: l_assign_payroll_warning boolean;
1475: --
1476: procedure hire_applicant
1477: (p_validate in boolean, --default false,
1478: p_hire_date in date,
1479: p_person_id in per_all_people_f.person_id%TYPE,
1480: p_assignment_id in number, --default null,
1481: p_person_type_id in number, --default null,
1482: p_national_identifier in per_all_people_f.national_identifier%type, --default hr_api.g_varchar2,
1483: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1478: p_hire_date in date,
1479: p_person_id in per_all_people_f.person_id%TYPE,
1480: p_assignment_id in number, --default null,
1481: p_person_type_id in number, --default null,
1482: p_national_identifier in per_all_people_f.national_identifier%type, --default hr_api.g_varchar2,
1483: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1484: p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1485: p_per_effective_start_date out nocopy date,
1486: p_per_effective_end_date out nocopy date,
1479: p_person_id in per_all_people_f.person_id%TYPE,
1480: p_assignment_id in number, --default null,
1481: p_person_type_id in number, --default null,
1482: p_national_identifier in per_all_people_f.national_identifier%type, --default hr_api.g_varchar2,
1483: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1484: p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1485: p_per_effective_start_date out nocopy date,
1486: p_per_effective_end_date out nocopy date,
1487: p_unaccepted_asg_del_warning out nocopy boolean,
1480: p_assignment_id in number, --default null,
1481: p_person_type_id in number, --default null,
1482: p_national_identifier in per_all_people_f.national_identifier%type, --default hr_api.g_varchar2,
1483: p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
1484: p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
1485: p_per_effective_start_date out nocopy date,
1486: p_per_effective_end_date out nocopy date,
1487: p_unaccepted_asg_del_warning out nocopy boolean,
1488: p_assign_payroll_warning out nocopy boolean,
1500: l_exists varchar2(1);
1501: l_count number;
1502: l_multi_flag boolean;
1503: l_chk_system_status per_assignment_status_types.per_system_status%TYPE;
1504: l_chk_person_id per_all_people_f.person_id%TYPE;
1505: --
1506: l_person_type_id number := p_person_type_id;
1507: l_person_type_id1 number;
1508: l_unaccepted_asg_del_warning boolean;
1507: l_person_type_id1 number;
1508: l_unaccepted_asg_del_warning boolean;
1509: --
1510: l_system_person_type per_person_types.system_person_type%TYPE;
1511: l_business_group_id per_all_people_f.business_group_id%TYPE;
1512: l_legislation_code per_business_groups.legislation_code%TYPE;
1513: l_applicant_number per_all_people_f.applicant_number%TYPE;
1514: l_application_id per_applications.application_id%TYPE;
1515: l_apl_object_version_number per_applications.application_id%TYPE;
1509: --
1510: l_system_person_type per_person_types.system_person_type%TYPE;
1511: l_business_group_id per_all_people_f.business_group_id%TYPE;
1512: l_legislation_code per_business_groups.legislation_code%TYPE;
1513: l_applicant_number per_all_people_f.applicant_number%TYPE;
1514: l_application_id per_applications.application_id%TYPE;
1515: l_apl_object_version_number per_applications.application_id%TYPE;
1516: --
1517: l_hire_date date;
1520: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1521: l_assignment_id per_assignments_f.assignment_id%TYPE;
1522: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1523: --
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1527: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1521: l_assignment_id per_assignments_f.assignment_id%TYPE;
1522: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1523: --
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1527: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1529: l_comment_id per_assignments_f.comment_id%TYPE;
1522: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1523: --
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1527: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1529: l_comment_id per_assignments_f.comment_id%TYPE;
1530: l_current_applicant_flag varchar2(1);
1523: --
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1527: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1529: l_comment_id per_assignments_f.comment_id%TYPE;
1530: l_current_applicant_flag varchar2(1);
1531: l_current_emp_or_apl_flag varchar2(1);
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1527: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1529: l_comment_id per_assignments_f.comment_id%TYPE;
1530: l_current_applicant_flag varchar2(1);
1531: l_current_emp_or_apl_flag varchar2(1);
1532: l_current_employee_flag varchar2(1);
1529: l_comment_id per_assignments_f.comment_id%TYPE;
1530: l_current_applicant_flag varchar2(1);
1531: l_current_emp_or_apl_flag varchar2(1);
1532: l_current_employee_flag varchar2(1);
1533: l_full_name per_all_people_f.full_name%TYPE;
1534: l_name_combination_warning boolean;
1535: l_assign_payroll_warning boolean;
1536: l_orig_hire_warning boolean;
1537: l_hourly_salaried_warning boolean;
1566: where pps.person_id = p_person_id
1567: and pps.actual_termination_date = p_hire_date-1;
1568: --bug no 5105005
1569:
1570: lv_per_object_version_number per_all_people_f.object_version_number%TYPE := p_per_object_version_number ;
1571: lv_employee_number per_all_people_f.employee_number%TYPE := p_employee_number ;
1572: --
1573: --
1574: -- Bug# 2273304 Start Here
1567: and pps.actual_termination_date = p_hire_date-1;
1568: --bug no 5105005
1569:
1570: lv_per_object_version_number per_all_people_f.object_version_number%TYPE := p_per_object_version_number ;
1571: lv_employee_number per_all_people_f.employee_number%TYPE := p_employee_number ;
1572: --
1573: --
1574: -- Bug# 2273304 Start Here
1575: --
1579: l_maximum_age number(3); --2273304
1580: --
1581: cursor csr_date_of_birth is
1582: select date_of_birth
1583: from per_all_people_f ppf
1584: where ppf.person_id = p_person_id
1585: and l_business_group_id = ppf.business_group_id
1586: and p_hire_date between effective_start_date
1587: and nvl(effective_end_date,p_hire_date);
1611: pap.application_id,
1612: pap.object_version_number,
1613: per.npw_number,
1614: per.original_date_of_hire -- #2978566
1615: from per_all_people_f per,
1616: per_business_groups bus,
1617: per_person_types ppt,
1618: per_applications pap
1619: where per.person_type_id = ppt.person_type_id
1637: --
1638: cursor csr_chk_assignment_id is
1639: select per.person_id,
1640: pas.per_system_status
1641: from per_all_people_f per,
1642: per_assignments_f asg,
1643: per_assignment_status_types pas
1644: where asg.assignment_status_type_id = pas.assignment_status_type_id
1645: and per.person_id = asg.person_id
1794: --
1795: cursor csr_get_legislation_code is
1796: select legislation_code
1797: from per_business_groups_perf pbg
1798: ,per_all_people_f ppf
1799: where ppf.person_id = p_person_id
1800: and pbg.business_group_id+0 = ppf.business_group_id;
1801:
1802: cursor csr_get_new_hire_code is
1800: and pbg.business_group_id+0 = ppf.business_group_id;
1801:
1802: cursor csr_get_new_hire_code is
1803: SELECT per_information7
1804: FROM per_all_people_f
1805: WHERE person_id = p_person_id
1806: AND effective_start_date =
1807: (select max(effective_start_date)
1808: from per_all_people_f
1804: FROM per_all_people_f
1805: WHERE person_id = p_person_id
1806: AND effective_start_date =
1807: (select max(effective_start_date)
1808: from per_all_people_f
1809: where person_id = p_person_id
1810: );
1811: -- end of bug3572499
1812:
2189: --
2190: -- Fix for bug 2881076 end.
2191: hr_utility.set_location(l_proc,170);
2192: --
2193: -- Lock the person record in PER_ALL_PEOPLE_F ready for UPDATE at a later point.
2194: -- (Note: This is necessary because calling the table handlers in locking
2195: -- ladder order invokes an error in per_apl_upd.upd due to the person
2196: -- being modified by the per_per_upd.upd table handler.)
2197: per_per_shd.lck
2222: hr_utility.set_location(l_proc,181);
2223: --
2224: for phn_rec in csr_phn(per_per_shd.g_old_rec.party_id) loop
2225: l_phn_ovn:=phn_rec.object_version_number;
2226: l_parent_table := 'PER_ALL_PEOPLE_F';
2227: per_phn_upd.upd(p_phone_id => phn_rec.phone_id
2228: ,p_parent_id => per_per_shd.g_old_rec.person_id
2229: ,p_parent_table => l_parent_table
2230: ,p_object_version_number => l_phn_ovn
2358: -- enf of bug3572499
2359:
2360: --
2361: -- Update the person details by calling upd procedure in
2362: -- the per_all_people_f table.
2363: --
2364: l_person_type_id1 :=
2365: hr_person_type_usage_info.get_default_person_type_id
2366: (l_business_group_id
3038: -- OLD
3039: PROCEDURE terminate_applicant
3040: (p_validate IN BOOLEAN --DEFAULT FALSE
3041: ,p_effective_date IN DATE
3042: ,p_person_id IN per_all_people_f.person_id%TYPE
3043: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3044: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT hr_api.g_number
3045: ,p_termination_reason IN per_applications.termination_reason%TYPE --DEFAULT NULL
3046: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3039: PROCEDURE terminate_applicant
3040: (p_validate IN BOOLEAN --DEFAULT FALSE
3041: ,p_effective_date IN DATE
3042: ,p_person_id IN per_all_people_f.person_id%TYPE
3043: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3044: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT hr_api.g_number
3045: ,p_termination_reason IN per_applications.termination_reason%TYPE --DEFAULT NULL
3046: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3047: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3042: ,p_person_id IN per_all_people_f.person_id%TYPE
3043: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3044: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT hr_api.g_number
3045: ,p_termination_reason IN per_applications.termination_reason%TYPE --DEFAULT NULL
3046: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3047: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3048: )
3049: IS
3050: Begin
3043: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3044: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT hr_api.g_number
3045: ,p_termination_reason IN per_applications.termination_reason%TYPE --DEFAULT NULL
3046: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3047: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3048: )
3049: IS
3050: Begin
3051: hr_applicant_api.terminate_applicant
3067: --
3068: PROCEDURE terminate_applicant
3069: (p_validate IN BOOLEAN
3070: ,p_effective_date IN DATE
3071: ,p_person_id IN per_all_people_f.person_id%TYPE
3072: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3073: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3074: ,p_termination_reason IN per_applications.termination_reason%TYPE
3075: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3068: PROCEDURE terminate_applicant
3069: (p_validate IN BOOLEAN
3070: ,p_effective_date IN DATE
3071: ,p_person_id IN per_all_people_f.person_id%TYPE
3072: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3073: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3074: ,p_termination_reason IN per_applications.termination_reason%TYPE
3075: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3076: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3072: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3073: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3074: ,p_termination_reason IN per_applications.termination_reason%TYPE
3075: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3076: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3077: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3078: )
3079: IS
3080: l_warning boolean;
3073: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3074: ,p_termination_reason IN per_applications.termination_reason%TYPE
3075: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3076: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3077: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3078: )
3079: IS
3080: l_warning boolean;
3081: BEGIN
3099: --
3100: PROCEDURE terminate_applicant
3101: (p_validate IN BOOLEAN
3102: ,p_effective_date IN DATE
3103: ,p_person_id IN per_all_people_f.person_id%TYPE
3104: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3105: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3106: ,p_termination_reason IN per_applications.termination_reason%TYPE
3107: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3100: PROCEDURE terminate_applicant
3101: (p_validate IN BOOLEAN
3102: ,p_effective_date IN DATE
3103: ,p_person_id IN per_all_people_f.person_id%TYPE
3104: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3105: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3106: ,p_termination_reason IN per_applications.termination_reason%TYPE
3107: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3108: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3104: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3105: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3106: ,p_termination_reason IN per_applications.termination_reason%TYPE
3107: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3108: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3109: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3110: ,p_remove_fut_asg_warning OUT NOCOPY BOOLEAN -- 3652025
3111: )
3112: IS
3105: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3106: ,p_termination_reason IN per_applications.termination_reason%TYPE
3107: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3108: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3109: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3110: ,p_remove_fut_asg_warning OUT NOCOPY BOOLEAN -- 3652025
3111: )
3112: IS
3113: BEGIN
3132: --
3133: PROCEDURE terminate_applicant
3134: (p_validate IN BOOLEAN
3135: ,p_effective_date IN DATE
3136: ,p_person_id IN per_all_people_f.person_id%TYPE
3137: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3138: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3139: ,p_termination_reason IN per_applications.termination_reason%TYPE
3140: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3133: PROCEDURE terminate_applicant
3134: (p_validate IN BOOLEAN
3135: ,p_effective_date IN DATE
3136: ,p_person_id IN per_all_people_f.person_id%TYPE
3137: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3138: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3139: ,p_termination_reason IN per_applications.termination_reason%TYPE
3140: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3141: ,p_change_reason IN per_all_assignments_f.change_reason%TYPE -- 4066579
3138: ,p_person_type_id IN per_person_types.person_type_id%TYPE
3139: ,p_termination_reason IN per_applications.termination_reason%TYPE
3140: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3141: ,p_change_reason IN per_all_assignments_f.change_reason%TYPE -- 4066579
3142: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3143: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3144: ,p_remove_fut_asg_warning OUT NOCOPY BOOLEAN -- 3652025
3145: )
3146: IS
3139: ,p_termination_reason IN per_applications.termination_reason%TYPE
3140: ,p_assignment_status_type_id IN per_all_assignments_f.assignment_status_type_id%TYPE
3141: ,p_change_reason IN per_all_assignments_f.change_reason%TYPE -- 4066579
3142: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3143: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3144: ,p_remove_fut_asg_warning OUT NOCOPY BOOLEAN -- 3652025
3145: )
3146: IS
3147: --
3152: l_legislation_code varchar2(30);
3153: l_asg_status_ovn number;
3154: l_effective_date DATE;
3155: --
3156: l_object_version_number CONSTANT per_all_people_f.object_version_number%TYPE := p_object_version_number;
3157: l_person_type_id per_person_types.person_type_id%TYPE := p_person_type_id;
3158: l_person_type_id1 per_person_types.person_type_id%TYPE;
3159: --
3160: l_assignment_status_id number;
3167: l_business_group_id hr_all_organization_units.organization_id%TYPE;
3168: l_org_now_no_manager_warning BOOLEAN;
3169: l_system_person_type per_person_types.system_person_type%TYPE;
3170: l_comment_id hr_comments.comment_id%TYPE;
3171: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3172: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3173: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3174: l_full_name per_all_people_f.full_name%TYPE;
3175: l_name_combination_warning BOOLEAN;
3168: l_org_now_no_manager_warning BOOLEAN;
3169: l_system_person_type per_person_types.system_person_type%TYPE;
3170: l_comment_id hr_comments.comment_id%TYPE;
3171: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3172: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3173: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3174: l_full_name per_all_people_f.full_name%TYPE;
3175: l_name_combination_warning BOOLEAN;
3176: l_dob_null_warning BOOLEAN;
3169: l_system_person_type per_person_types.system_person_type%TYPE;
3170: l_comment_id hr_comments.comment_id%TYPE;
3171: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3172: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3173: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3174: l_full_name per_all_people_f.full_name%TYPE;
3175: l_name_combination_warning BOOLEAN;
3176: l_dob_null_warning BOOLEAN;
3177: l_orig_hire_warning BOOLEAN;
3170: l_comment_id hr_comments.comment_id%TYPE;
3171: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3172: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3173: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3174: l_full_name per_all_people_f.full_name%TYPE;
3175: l_name_combination_warning BOOLEAN;
3176: l_dob_null_warning BOOLEAN;
3177: l_orig_hire_warning BOOLEAN;
3178: --
3175: l_name_combination_warning BOOLEAN;
3176: l_dob_null_warning BOOLEAN;
3177: l_orig_hire_warning BOOLEAN;
3178: --
3179: lv_object_version_number per_all_people_f.object_version_number%TYPE := p_object_version_number ;
3180: l_remove_future_asg_warning BOOLEAN;
3181: l_count NUMBER;
3182: --fix for bug 7229710 Starts here.
3183: l_vacancy_id number;
3195: --fix for bug 7229710 Ends here.
3196:
3197: CURSOR csr_applications
3198: (p_effective_date IN DATE
3199: ,p_person_id IN per_all_people_f.person_id%TYPE
3200: )
3201: IS
3202: SELECT apl.application_id
3203: ,apl.object_version_number
3219: l_application csr_applications%ROWTYPE;
3220: --
3221: CURSOR csr_assignments
3222: (p_effective_date IN DATE
3223: ,p_person_id IN per_all_people_f.person_id%TYPE
3224: )
3225: IS
3226: SELECT asg.assignment_id
3227: ,asg.object_version_number
3251: ORDER BY as2.effective_start_date, as2.assignment_id ASC;
3252:
3253: CURSOR csr_lock_person(cp_person_id number, cp_termination_date date) IS
3254: SELECT null
3255: FROM per_all_people_f
3256: WHERE person_id = cp_person_id
3257: AND (effective_start_date > cp_termination_date
3258: OR
3259: cp_termination_date between effective_start_date
3736: -- OLD
3737: PROCEDURE convert_to_applicant
3738: (p_validate IN BOOLEAN --DEFAULT FALSE
3739: ,p_effective_date IN DATE
3740: ,p_person_id IN per_all_people_f.person_id%TYPE
3741: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3742: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3743: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3744: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3737: PROCEDURE convert_to_applicant
3738: (p_validate IN BOOLEAN --DEFAULT FALSE
3739: ,p_effective_date IN DATE
3740: ,p_person_id IN per_all_people_f.person_id%TYPE
3741: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3742: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3743: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3744: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3745: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3738: (p_validate IN BOOLEAN --DEFAULT FALSE
3739: ,p_effective_date IN DATE
3740: ,p_person_id IN per_all_people_f.person_id%TYPE
3741: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3742: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3743: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3744: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3745: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3746: ) IS
3740: ,p_person_id IN per_all_people_f.person_id%TYPE
3741: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3742: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3743: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3744: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3745: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3746: ) IS
3747: l_warning boolean;
3748: BEGIN
3741: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3742: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3743: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3744: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3745: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3746: ) IS
3747: l_warning boolean;
3748: BEGIN
3749: hr_applicant_api.convert_to_applicant
3765: -- NEW
3766: PROCEDURE convert_to_applicant
3767: (p_validate IN BOOLEAN --DEFAULT FALSE
3768: ,p_effective_date IN DATE
3769: ,p_person_id IN per_all_people_f.person_id%TYPE
3770: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3771: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3772: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3773: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3766: PROCEDURE convert_to_applicant
3767: (p_validate IN BOOLEAN --DEFAULT FALSE
3768: ,p_effective_date IN DATE
3769: ,p_person_id IN per_all_people_f.person_id%TYPE
3770: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3771: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3772: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3773: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3774: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3767: (p_validate IN BOOLEAN --DEFAULT FALSE
3768: ,p_effective_date IN DATE
3769: ,p_person_id IN per_all_people_f.person_id%TYPE
3770: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3771: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3772: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3773: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3774: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3775: ,p_appl_override_warning OUT NOCOPY boolean -- 3652025
3769: ,p_person_id IN per_all_people_f.person_id%TYPE
3770: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3771: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3772: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3773: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3774: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3775: ,p_appl_override_warning OUT NOCOPY boolean -- 3652025
3776: )
3777: IS
3770: ,p_object_version_number IN OUT NOCOPY per_all_people_f.object_version_number%TYPE
3771: ,p_applicant_number IN OUT NOCOPY per_all_people_f.applicant_number%TYPE
3772: ,p_person_type_id IN per_person_types.person_type_id%TYPE --DEFAULT NULL
3773: ,p_effective_start_date OUT NOCOPY per_all_people_f.effective_start_date%TYPE
3774: ,p_effective_end_date OUT NOCOPY per_all_people_f.effective_end_date%TYPE
3775: ,p_appl_override_warning OUT NOCOPY boolean -- 3652025
3776: )
3777: IS
3778: --
3782: --
3783: l_effective_date DATE;
3784: --
3785: l_object_version_number CONSTANT per_all_assignments_f.object_version_number%TYPE := p_object_version_number;
3786: l_applicant_number CONSTANT per_all_people_f.applicant_number%TYPE := p_applicant_number;
3787: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
3788: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
3789: --
3790: l_person_type_id per_person_types.person_type_id%TYPE;
3783: l_effective_date DATE;
3784: --
3785: l_object_version_number CONSTANT per_all_assignments_f.object_version_number%TYPE := p_object_version_number;
3786: l_applicant_number CONSTANT per_all_people_f.applicant_number%TYPE := p_applicant_number;
3787: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
3788: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
3789: --
3790: l_person_type_id per_person_types.person_type_id%TYPE;
3791: l_person_type_id1 per_person_types.person_type_id%TYPE;
3784: --
3785: l_object_version_number CONSTANT per_all_assignments_f.object_version_number%TYPE := p_object_version_number;
3786: l_applicant_number CONSTANT per_all_people_f.applicant_number%TYPE := p_applicant_number;
3787: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
3788: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
3789: --
3790: l_person_type_id per_person_types.person_type_id%TYPE;
3791: l_person_type_id1 per_person_types.person_type_id%TYPE;
3792: --
3794: l_system_person_type per_person_types.system_person_type%TYPE;
3795: l_effective_start_date DATE;
3796: l_effective_end_date DATE;
3797: l_comment_id hr_comments.comment_id%TYPE;
3798: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3799: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3800: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3801: l_full_name per_all_people_f.full_name%TYPE;
3802: l_name_combination_warning BOOLEAN;
3795: l_effective_start_date DATE;
3796: l_effective_end_date DATE;
3797: l_comment_id hr_comments.comment_id%TYPE;
3798: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3799: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3800: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3801: l_full_name per_all_people_f.full_name%TYPE;
3802: l_name_combination_warning BOOLEAN;
3803: l_dob_null_warning BOOLEAN;
3796: l_effective_end_date DATE;
3797: l_comment_id hr_comments.comment_id%TYPE;
3798: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3799: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3800: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3801: l_full_name per_all_people_f.full_name%TYPE;
3802: l_name_combination_warning BOOLEAN;
3803: l_dob_null_warning BOOLEAN;
3804: l_orig_hire_warning BOOLEAN;
3797: l_comment_id hr_comments.comment_id%TYPE;
3798: l_current_applicant_flag per_all_people_f.current_applicant_flag%TYPE;
3799: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%TYPE;
3800: l_current_employee_flag per_all_people_f.current_employee_flag%TYPE;
3801: l_full_name per_all_people_f.full_name%TYPE;
3802: l_name_combination_warning BOOLEAN;
3803: l_dob_null_warning BOOLEAN;
3804: l_orig_hire_warning BOOLEAN;
3805: l_application_id per_applications.application_id%TYPE;
3809: l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
3810: l_person_type_usage_id per_person_type_usages.person_type_usage_id%TYPE;
3811: l_ptu_object_version_number per_person_type_usages.object_version_number%TYPE;
3812: --
3813: lv_object_version_number per_all_people_f.object_version_number%TYPE := p_object_version_number ;
3814: lv_applicant_number per_all_people_f.applicant_number%TYPE := p_applicant_number ;
3815: l_datetrack_mode varchar2(30); -- Bug 2738584
3816: --
3817: l_per_effective_start_date per_all_people_f.effective_end_date%TYPE;
3810: l_person_type_usage_id per_person_type_usages.person_type_usage_id%TYPE;
3811: l_ptu_object_version_number per_person_type_usages.object_version_number%TYPE;
3812: --
3813: lv_object_version_number per_all_people_f.object_version_number%TYPE := p_object_version_number ;
3814: lv_applicant_number per_all_people_f.applicant_number%TYPE := p_applicant_number ;
3815: l_datetrack_mode varchar2(30); -- Bug 2738584
3816: --
3817: l_per_effective_start_date per_all_people_f.effective_end_date%TYPE;
3818: l_per_effective_end_date per_all_people_f.effective_start_date%TYPE;
3813: lv_object_version_number per_all_people_f.object_version_number%TYPE := p_object_version_number ;
3814: lv_applicant_number per_all_people_f.applicant_number%TYPE := p_applicant_number ;
3815: l_datetrack_mode varchar2(30); -- Bug 2738584
3816: --
3817: l_per_effective_start_date per_all_people_f.effective_end_date%TYPE;
3818: l_per_effective_end_date per_all_people_f.effective_start_date%TYPE;
3819: l_appl_override_warning boolean;
3820: --
3821: -- Local cursors
3814: lv_applicant_number per_all_people_f.applicant_number%TYPE := p_applicant_number ;
3815: l_datetrack_mode varchar2(30); -- Bug 2738584
3816: --
3817: l_per_effective_start_date per_all_people_f.effective_end_date%TYPE;
3818: l_per_effective_end_date per_all_people_f.effective_start_date%TYPE;
3819: l_appl_override_warning boolean;
3820: --
3821: -- Local cursors
3822: --
3820: --
3821: -- Local cursors
3822: --
3823: CURSOR csr_per_details
3824: (p_person_id IN per_all_people_f.person_id%TYPE
3825: ,p_effective_date IN DATE
3826: )
3827: IS
3828: SELECT pet.person_type_id
4166: -- apply_for_job_anytime
4167: --
4168: l_apl_object_version_number per_applications.object_version_number%TYPE;
4169: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
4170: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
4171: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
4172: --
4173: --
4174: -- select and validate the person
4167: --
4168: l_apl_object_version_number per_applications.object_version_number%TYPE;
4169: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
4170: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
4171: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
4172: --
4173: --
4174: -- select and validate the person
4175: --