EMPLOYEE Termination
DECLARE
--
--Common Variables
l_terminate_emp_flag VARCHAR2 (1) := 'N';
l_terminate_msg VARCHAR2 (600);
l_person_id NUMBER
:= 533285;
l_le_terminate_emp_exception EXCEPTION;
--- DECLARE variables for
HR_EX_EMPLOYEE_WORKER_API.actual_termination_emp
--- IN variables
l_effective_date DATE;
l_termination_reason
per_periods_of_service.leaving_reason%TYPE := 'XXSD_RESG5';
-- HR_LOOKUPS (TYPE:
LEAV_REAS)
l_person_type_id
per_person_types.person_type_id%TYPE := 1130;
-- 'EX_EMP'
l_period_of_service_id
per_periods_of_service.period_of_service_id%TYPE;
l_actual_termination_date
per_periods_of_service.actual_termination_date%TYPE :=
TRUNC (SYSDATE);
l_last_standard_process_date
per_periods_of_service.last_standard_process_date%TYPE :=
TRUNC (SYSDATE + 10);
l_object_version_number
per_periods_of_service.object_version_number%TYPE;
l_start_date
per_periods_of_service.date_start%TYPE;
l_notif_term_date DATE;
--- OUT variables
l_supervisor_warning BOOLEAN :=
FALSE;
l_event_warning BOOLEAN
:= FALSE;
l_interview_warning BOOLEAN
:= FALSE;
l_review_warning BOOLEAN
:= FALSE;
l_recruiter_warning BOOLEAN
:= FALSE;
l_asg_future_changes_warning BOOLEAN :=
FALSE;
l_entries_changed_warning VARCHAR2 (300);
l_pay_proposal_warning BOOLEAN
:= FALSE;
l_dod_warning BOOLEAN :=
FALSE;
l_alu_change_warning VARCHAR2 (300);
--- DECLARE variables for
HR_EX_EMPLOYEE_WORKER_API.final_process_emp
--- IN variables
l_final_process_date
per_periods_of_service.final_process_date%TYPE;
--- OUT variables
l_org_now_no_manager_warning BOOLEAN
:= FALSE;
l_f_asg_future_changes_warning BOOLEAN
:= FALSE;
l_f_entries_changed_warning VARCHAR2 (300);
--
BEGIN
--
BEGIN
SELECT pos.period_of_service_id,
pos.object_version_number,
date_start
INTO l_period_of_service_id,
l_object_version_number,
l_start_date
FROM per_periods_of_service pos
WHERE pos.person_id = l_person_id;
EXCEPTION
WHEN OTHERS
THEN
l_terminate_msg := 'Error while selecting employee
details : ' || SUBSTR (SQLERRM, 1, 150);
RAISE l_le_terminate_emp_exception;
END;
--
SAVEPOINT terminate_employee_s1;
--
BEGIN
/*
* This API terminates an employee.
* This API converts a person of type
Employee >to a person of type
* Ex-Employee. The person's period of
service and any employee assignments are ended.
*/
hr_ex_employee_api.actual_termination_emp (p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_period_of_service_id => l_period_of_service_id,
p_object_version_number => l_object_version_number,
p_actual_termination_date => l_actual_termination_date,
p_last_standard_process_date => l_last_standard_process_date,
p_person_type_id => l_person_type_id,
p_leaving_reason => l_termination_reason,
p_supervisor_warning => l_supervisor_warning,
p_event_warning => l_event_warning,
p_interview_warning => l_interview_warning,
p_review_warning => l_review_warning,
p_recruiter_warning => l_recruiter_warning,
p_asg_future_changes_warning => l_asg_future_changes_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_pay_proposal_warning => l_pay_proposal_warning,
p_dod_warning => l_dod_warning,
p_alu_change_warning => l_alu_change_warning
);
IF l_object_version_number IS NULL
THEN
l_terminate_emp_flag := 'N';
l_terminate_msg := 'Warning validating API:
hr_ex_employee_api.actual_termination_emp';
RAISE l_le_terminate_emp_exception;
END IF;
l_terminate_emp_flag := 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_terminate_msg := 'Error validating API:
hr_ex_employee_api.actual_termination_emp : ' || SUBSTR
(SQLERRM, 1, 150);
RAISE l_le_terminate_emp_exception;
END;
IF l_terminate_emp_flag = 'Y'
THEN
BEGIN
IF l_start_date > TRUNC (SYSDATE)
THEN
l_notif_term_date := l_start_date + 1;
ELSE
l_notif_term_date := TRUNC (SYSDATE);
END IF;
/*
* This API updates employee
termination information.
* The ex-employee must exist in the
relevant business group
*/
apps.hr_ex_employee_api.update_term_details_emp (p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_period_of_service_id => l_period_of_service_id,
p_notified_termination_date => l_notif_term_date,
p_projected_termination_date => l_notif_term_date,
p_object_version_number => l_object_version_number
);
EXCEPTION
WHEN OTHERS
THEN
l_terminate_msg := 'Error validating API:
hr_ex_employee_api.update_term_details_emp : ' || SUBSTR
(SQLERRM, 1, 1500);
l_terminate_emp_flag := 'N';
RAISE l_le_terminate_emp_exception;
END;
BEGIN
/*
* This API set the final process date
for a terminated employee.
* This API covers the second step in
terminating a period of service and all
* current assignments for an employee.
It updates the period of service
* details and date-effectively deletes all
employee assignments as of the final process date.
*/
apps.hr_ex_employee_api.final_process_emp (p_validate => FALSE,
p_period_of_service_id => l_period_of_service_id,
p_object_version_number => l_object_version_number,
p_final_process_date => l_final_process_date,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_asg_future_changes_warning =>
l_f_asg_future_changes_warning,
p_entries_changed_warning => l_f_entries_changed_warning
);
EXCEPTION
WHEN
OTHERS
THEN
l_terminate_msg := 'Error validating API:
hr_ex_employee_api.final_process_emp : ' || SUBSTR (SQLERRM, 1, 150);
RAISE l_le_terminate_emp_exception;
END;
END IF;
--
COMMIT;
--
EXCEPTION
WHEN l_le_terminate_emp_exception
THEN
DBMS_OUTPUT.put_line
(l_terminate_msg);
ROLLBACK TO terminate_employee_s1;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('Terminate Employee. Error
OTHERS while validating: ' || SQLERRM);
ROLLBACK TO terminate_employee_s1;
END;
CONTINGENT WORKER Termination
DECLARE
--
--Common Variables
l_terminate_cwk_flag VARCHAR2 (1) := 'N';
l_terminate_msg VARCHAR2 (600);
l_person_id NUMBER
:= 533485;
l_le_terminate_cwk_exception EXCEPTION;
--- DECLARE variables for
hr_contingent_worker_api.actual_termination_placement
--- IN variables
l_effective_date DATE
:= TRUNC (SYSDATE);
l_termination_reason
per_periods_of_placement.termination_reason%TYPE :=
'EP';
-- select * from hr_lookups
where lookup_type = 'HR_CWK_TERMINATION_REASONS'
l_person_type_id
per_person_types.person_type_id%TYPE := 1129;
-- EX_CWK
l_period_of_placement_id
per_periods_of_placement.period_of_placement_id%TYPE;
l_actual_termination_date
per_periods_of_placement.actual_termination_date%TYPE :=
TRUNC (SYSDATE);
l_last_standard_process_date
per_periods_of_placement.last_standard_process_date%TYPE :=
TRUNC (SYSDATE + 10);
l_object_version_number
per_periods_of_placement.object_version_number%TYPE;
l_start_date
per_periods_of_placement.date_start%TYPE;
l_notif_term_date DATE;
--- OUT variables
l_supervisor_warning BOOLEAN
:= FALSE;
l_event_warning BOOLEAN
:= FALSE;
l_interview_warning BOOLEAN
:= FALSE;
l_review_warning BOOLEAN
:= FALSE;
l_recruiter_warning BOOLEAN
:= FALSE;
l_asg_future_changes_warning BOOLEAN
:= FALSE;
l_entries_changed_warning VARCHAR2 (300);
l_pay_proposal_warning BOOLEAN
:= FALSE;
l_dod_warning BOOLEAN
:= FALSE;
--- DECLARE variables for
hr_contingent_worker_api.final_process_placement
--- IN variables
l_final_process_date
per_periods_of_service.final_process_date%TYPE;
--- OUT variables
l_org_now_no_manager_warning BOOLEAN
:= FALSE;
--
BEGIN
--
BEGIN
SELECT pos.period_of_placement_id,
pos.object_version_number,
date_start
INTO l_period_of_placement_id,
l_object_version_number,
l_start_date
FROM per_periods_of_placement pos
WHERE pos.person_id = l_person_id;
EXCEPTION
WHEN OTHERS
THEN
l_terminate_msg := 'Error while selecting cwk
details : ' || SUBSTR (SQLERRM, 1, 150);
RAISE l_le_terminate_cwk_exception;
END;
--
SAVEPOINT terminate_cwk_s1;
--
BEGIN
/*
This API covers the first step in
terminating a period of placement and
all current assignments for a cwk,
identified by person_id and date_start.
You can use the API to set the actual
termination date, the last standard
process date, the new assignment status
and the new person type
*/
hr_contingent_worker_api.actual_termination_placement
(p_validate => FALSE,
p_effective_date => l_effective_date,
p_person_id => l_person_id,
p_date_start => l_start_date,
p_person_type_id => l_person_type_id,
p_actual_termination_date => l_actual_termination_date,
p_termination_reason => l_termination_reason
--In/Out
,
p_object_version_number => l_object_version_number,
p_last_standard_process_date => l_last_standard_process_date
--Out
,
p_supervisor_warning => l_supervisor_warning,
p_event_warning => l_event_warning,
p_interview_warning => l_interview_warning,
p_review_warning => l_review_warning,
p_recruiter_warning => l_recruiter_warning,
p_asg_future_changes_warning => l_asg_future_changes_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_pay_proposal_warning => l_pay_proposal_warning,
p_dod_warning => l_dod_warning
);
IF l_object_version_number IS NULL
THEN
l_terminate_cwk_flag := 'N';
l_terminate_msg := 'Warning validating API:
hr_contingent_worker_api.actual_termination_placement';
RAISE l_le_terminate_cwk_exception;
END IF;
l_terminate_cwk_flag := 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_terminate_msg := 'Error validating API:
hr_contingent_worker_api.actual_termination_placement : ' || SUBSTR (SQLERRM, 1, 150);
RAISE l_le_terminate_cwk_exception;
END;
--hr_contingent_worker_api.actual_termination_placement
IF l_terminate_cwk_flag = 'Y'
THEN
BEGIN
/*
This API covers the second step in
terminating a period of placement and
all current assignments for an
cwk. It updates the period of placement
details and date-effectively deletes
all the contingent worker assignments
as of the final process date.
*/
hr_contingent_worker_api.final_process_placement
(p_validate => FALSE,
p_person_id => l_person_id,
p_date_start => l_start_date
--In/Out
,
p_object_version_number => l_object_version_number,
p_final_process_date => l_final_process_date
--Out
,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_asg_future_changes_warning => l_asg_future_changes_warning,
p_entries_changed_warning => l_entries_changed_warning
);
EXCEPTION
WHEN OTHERS
THEN
l_terminate_msg := 'Error validating API:
hr_contingent_worker_api.final_process_placement : ' || SUBSTR (SQLERRM, 1, 150);
RAISE l_le_terminate_cwk_exception;
END;
--hr_contingent_worker_api.final_process_placement
END IF;
--
COMMIT;
--
EXCEPTION
WHEN l_le_terminate_cwk_exception
THEN
DBMS_OUTPUT.put_line
(l_terminate_msg);
ROLLBACK TO terminate_cwk_s1;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('Terminate CWK. Error OTHERS
while validating: '
|| SQLERRM);
ROLLBACK TO terminate_cwk_s1;
END;