I have seen a few organizations sometimes end date the user rather than the employee especially when the organizations tend not to use Employee Payroll, which clearly makes sense. But the Inactive Employees have to be made Ex-Employees at some point of time for the integrity of the system and Reporting purpose.
Hereby is the script to modify Inactive Employees(personalized to the Employees without a user-login, but can also be personalized to your need) to Ex-Employees:
DECLARE
ld_last_std_process_date_out date := TRUNC(TO_DATE('23-JUL-2002', 'DD-MON-RRRR'));
l_period_of_service_id PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;
lb_supervisor_warning boolean;
lb_event_warning boolean;
lb_interview_warning boolean;
lb_review_warning boolean;
lb_recruiter_warning boolean;
lb_pay_proposal_warning boolean;
lb_dod_warning boolean;
--lc_object_version_number number(15) := 1;
ld_final_process_date date := SYSDATE-14;
lb_org_now_no_manager_warning boolean;
lb_asg_future_changes_warning boolean;
lb_entries_changed_warning varchar2(240);
ln_error number := 0;
TYPE t_person_id IS TABLE OF PER_ALL_PEOPLE_F.person_id%TYPE;
TYPE t_full_name IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
TYPE t_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_ass_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_ass_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_ass_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_object_version_number IS TABLE OF PER_PERIODS_OF_SERVICE.object_version_number%TYPE;
TYPE t_period_of_service_id IS TABLE OF PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;
TYPE t_end_date IS TABLE OF fnd_user.end_date%TYPE;
lt_person_id t_person_id;
lt_full_name t_full_name;
lt_eff_start_date t_eff_start_date;
lt_eff_end_date t_eff_end_date;
lt_ass_eff_start_date t_ass_eff_start_date;
lt_ass_eff_end_date t_ass_eff_end_date;
lt_last_update_date t_last_update_date;
lt_ass_last_update_date t_ass_last_update_date;
lt_object_version_number t_object_version_number;
lt_period_of_service_id t_period_of_service_id;
lt_end_date t_end_date;
CURSOR c_inactive_emp
IS
select distinct papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
,pps.period_of_service_id, fu.end_date
from per_all_people_f papf
,per_all_assignments_f pasf
,per_periods_of_service pps
,fnd_user fu
where trunc(sysdate) between trunc(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
and papf.person_id = pasf.person_id
and papf.person_id = pps.person_id
and papf.person_id = fu.employee_id
and EXISTS (select 1
from fnd_user fu
where fu.employee_id = papf.person_id
and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
)
and NVL(current_employee_flag, 'N') = 'Y'
order by papf.person_id;
/*
CURSOR c_inactive_emp
IS
SELECT DISTINCT papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
,pps.period_of_service_id
FROM per_all_people_f papf
,per_all_assignments_f pasf
,per_periods_of_service pps
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
and papf.person_id = pasf.person_id
and papf.person_id = pps.person_id
and EXISTS (select 1
from fnd_user fu
where fu.employee_id = papf.person_id
and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
)
and NVL(current_employee_flag, 'N') = 'Y'
ORDER BY papf.person_id;
*/
begin
OPEN c_inactive_emp;
FETCH c_inactive_emp BULK COLLECT INTO lt_person_id, lt_full_name, lt_eff_start_date, lt_eff_end_date, lt_ass_eff_start_date
,lt_ass_eff_end_date, lt_last_update_date, lt_ass_last_update_date, lt_object_version_number
,lt_period_of_service_id, lt_end_date;
CLOSE c_inactive_emp;
FOR i in 1..lt_person_id.COUNT LOOP
ln_error := 0;
dbms_output.put_line('Full Name:'||lt_full_name(i));
dbms_output.put_line('Effective Start Date:'||lt_eff_start_date(i));
dbms_output.put_line('Effective End Date:'||lt_eff_end_date(i));
dbms_output.put_line('Assignment Effective Start Date:'||lt_ass_eff_start_date(i));
dbms_output.put_line('Assignment Effective End Date:'||lt_ass_eff_end_date(i));
dbms_output.put_line('Period of Service:'||lt_period_of_service_id(i));
dbms_output.put_line('Object Version:'||lt_object_version_number(i));
dbms_output.put_line('End Date:'||lt_end_date(i));
BEGIN
hr_ex_employee_api.actual_termination_emp(
p_effective_date => lt_end_date(i)
,p_period_of_service_id => lt_period_of_service_id(i)
,p_object_version_number => lt_object_version_number(i)
,p_actual_termination_date => lt_end_date(i)
,p_person_type_id => 69 -- Person Type Id is EX_EMP
,p_last_std_process_date_out => ld_last_std_process_date_out
,p_supervisor_warning => lb_supervisor_warning
,p_event_warning => lb_event_warning
,p_interview_warning => lb_interview_warning
,p_review_warning => lb_review_warning
,p_recruiter_warning => lb_recruiter_warning
,p_asg_future_changes_warning => lb_asg_future_changes_warning
,p_entries_changed_warning => lb_entries_changed_warning
,p_pay_proposal_warning => lb_pay_proposal_warning
,p_dod_warning => lb_dod_warning
);
dbms_output.put_line('actual_termination_emp completed successfully for Employee:'||lt_full_name(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
ln_error := 1;
END;
IF (ln_error = 0) THEN
BEGIN
hr_ex_employee_api.update_term_details_emp
(p_effective_date => lt_end_date(i)
,p_period_of_service_id => lt_period_of_service_id(i)
,p_object_version_number => lt_object_version_number(i)
,p_accepted_termination_date => lt_end_date(i)
);
dbms_output.put_line('update_term_details_emp completed successfully for Employee:'||lt_full_name(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
ln_error := 1;
END;
END IF;
dbms_output.put_line('Employee: '||lt_full_name(i)||' update completed');
/* Not Required if Payroll is not used
hr_ex_employee_api.final_process_emp
(p_period_of_service_id => l_period_of_service_id
,p_object_version_number => lc_object_version_number
,p_final_process_date => ld_final_process_date
,p_org_now_no_manager_warning => lb_org_now_no_manager_warning
,p_asg_future_changes_warning => lb_asg_future_changes_warning
,p_entries_changed_warning => lb_entries_changed_warning
);
*/
END LOOP;
exception
when others then
dbms_output.put_line('Exception'||SQLERRM||SQLCODE);
end;
hr_ex_employee_api1.final_process_emp is not required to be called following the other 2 API's if Oracle Payroll is not implemented.
Print This Post
Hereby is the script to modify Inactive Employees(personalized to the Employees without a user-login, but can also be personalized to your need) to Ex-Employees:
DECLARE
ld_last_std_process_date_out date := TRUNC(TO_DATE('23-JUL-2002', 'DD-MON-RRRR'));
l_period_of_service_id PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;
lb_supervisor_warning boolean;
lb_event_warning boolean;
lb_interview_warning boolean;
lb_review_warning boolean;
lb_recruiter_warning boolean;
lb_pay_proposal_warning boolean;
lb_dod_warning boolean;
--lc_object_version_number number(15) := 1;
ld_final_process_date date := SYSDATE-14;
lb_org_now_no_manager_warning boolean;
lb_asg_future_changes_warning boolean;
lb_entries_changed_warning varchar2(240);
ln_error number := 0;
TYPE t_person_id IS TABLE OF PER_ALL_PEOPLE_F.person_id%TYPE;
TYPE t_full_name IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
TYPE t_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_ass_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_ass_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_ass_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_object_version_number IS TABLE OF PER_PERIODS_OF_SERVICE.object_version_number%TYPE;
TYPE t_period_of_service_id IS TABLE OF PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;
TYPE t_end_date IS TABLE OF fnd_user.end_date%TYPE;
lt_person_id t_person_id;
lt_full_name t_full_name;
lt_eff_start_date t_eff_start_date;
lt_eff_end_date t_eff_end_date;
lt_ass_eff_start_date t_ass_eff_start_date;
lt_ass_eff_end_date t_ass_eff_end_date;
lt_last_update_date t_last_update_date;
lt_ass_last_update_date t_ass_last_update_date;
lt_object_version_number t_object_version_number;
lt_period_of_service_id t_period_of_service_id;
lt_end_date t_end_date;
CURSOR c_inactive_emp
IS
select distinct papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
,pps.period_of_service_id, fu.end_date
from per_all_people_f papf
,per_all_assignments_f pasf
,per_periods_of_service pps
,fnd_user fu
where trunc(sysdate) between trunc(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
and papf.person_id = pasf.person_id
and papf.person_id = pps.person_id
and papf.person_id = fu.employee_id
and EXISTS (select 1
from fnd_user fu
where fu.employee_id = papf.person_id
and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
)
and NVL(current_employee_flag, 'N') = 'Y'
order by papf.person_id;
/*
CURSOR c_inactive_emp
IS
SELECT DISTINCT papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
,pps.period_of_service_id
FROM per_all_people_f papf
,per_all_assignments_f pasf
,per_periods_of_service pps
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
and papf.person_id = pasf.person_id
and papf.person_id = pps.person_id
and EXISTS (select 1
from fnd_user fu
where fu.employee_id = papf.person_id
and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
)
and NVL(current_employee_flag, 'N') = 'Y'
ORDER BY papf.person_id;
*/
begin
OPEN c_inactive_emp;
FETCH c_inactive_emp BULK COLLECT INTO lt_person_id, lt_full_name, lt_eff_start_date, lt_eff_end_date, lt_ass_eff_start_date
,lt_ass_eff_end_date, lt_last_update_date, lt_ass_last_update_date, lt_object_version_number
,lt_period_of_service_id, lt_end_date;
CLOSE c_inactive_emp;
FOR i in 1..lt_person_id.COUNT LOOP
ln_error := 0;
dbms_output.put_line('Full Name:'||lt_full_name(i));
dbms_output.put_line('Effective Start Date:'||lt_eff_start_date(i));
dbms_output.put_line('Effective End Date:'||lt_eff_end_date(i));
dbms_output.put_line('Assignment Effective Start Date:'||lt_ass_eff_start_date(i));
dbms_output.put_line('Assignment Effective End Date:'||lt_ass_eff_end_date(i));
dbms_output.put_line('Period of Service:'||lt_period_of_service_id(i));
dbms_output.put_line('Object Version:'||lt_object_version_number(i));
dbms_output.put_line('End Date:'||lt_end_date(i));
BEGIN
hr_ex_employee_api.actual_termination_emp(
p_effective_date => lt_end_date(i)
,p_period_of_service_id => lt_period_of_service_id(i)
,p_object_version_number => lt_object_version_number(i)
,p_actual_termination_date => lt_end_date(i)
,p_person_type_id => 69 -- Person Type Id is EX_EMP
,p_last_std_process_date_out => ld_last_std_process_date_out
,p_supervisor_warning => lb_supervisor_warning
,p_event_warning => lb_event_warning
,p_interview_warning => lb_interview_warning
,p_review_warning => lb_review_warning
,p_recruiter_warning => lb_recruiter_warning
,p_asg_future_changes_warning => lb_asg_future_changes_warning
,p_entries_changed_warning => lb_entries_changed_warning
,p_pay_proposal_warning => lb_pay_proposal_warning
,p_dod_warning => lb_dod_warning
);
dbms_output.put_line('actual_termination_emp completed successfully for Employee:'||lt_full_name(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
ln_error := 1;
END;
IF (ln_error = 0) THEN
BEGIN
hr_ex_employee_api.update_term_details_emp
(p_effective_date => lt_end_date(i)
,p_period_of_service_id => lt_period_of_service_id(i)
,p_object_version_number => lt_object_version_number(i)
,p_accepted_termination_date => lt_end_date(i)
);
dbms_output.put_line('update_term_details_emp completed successfully for Employee:'||lt_full_name(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
ln_error := 1;
END;
END IF;
dbms_output.put_line('Employee: '||lt_full_name(i)||' update completed');
/* Not Required if Payroll is not used
hr_ex_employee_api.final_process_emp
(p_period_of_service_id => l_period_of_service_id
,p_object_version_number => lc_object_version_number
,p_final_process_date => ld_final_process_date
,p_org_now_no_manager_warning => lb_org_now_no_manager_warning
,p_asg_future_changes_warning => lb_asg_future_changes_warning
,p_entries_changed_warning => lb_entries_changed_warning
);
*/
END LOOP;
exception
when others then
dbms_output.put_line('Exception'||SQLERRM||SQLCODE);
end;
hr_ex_employee_api1.final_process_emp is not required to be called following the other 2 API's if Oracle Payroll is not implemented.
Print This Post