Terminate an employee in oracle R12 using standard API
Here the leaving reason and the person type id should be careful on selecting. The leaving reason we can't able to give as we like.
The below api was used
1) hr_ex_employee_api.actual_termination_emp
2) hr_ex_employee_api.final_process_emp
3) hr_ex_employee_api.update_term_details_emp
These 3 also should be executed else when we reverse terminate an employee it won't work. This will look at the payroll as well. Check with business or use the mentioned function terminate_employee below so that the payroll won't get affected.
Based on the link I validated -
https://community.oracle.com/message/14155828#14155828
Also in the code below the business group id and the person type id is the mandatory one . the person type id 1320 is the current active employee in Oracle which is employee and in the api there passes the person type id as 1323 i.e the terminated record will be stored as Ex-employee
Below code works fine for me
/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Description:
Object Name: 3241_Datafix.adh
- - - - - - - -
W o r k L o g
- - - - - - - -
Work Work Date
Case dd-Mon-yyyy Resource Version Description of Work...
------ ----------- ---------- ------- ------------------------------------------
3241 07-JUL-2016 Sathish 1 Object genesis.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
SET SERVEROUTPUT ON;
Declare
Cursor c_emp_cur Is
SELECT pos.period_of_service_id,
pos.object_version_number,
pos.date_start ,
paf.person_id ,
paf.person_type_id
FROM apps.per_all_people_f paf,
apps.per_periods_of_service pos
WHERE paf.person_id =pos.person_id
AND paf.business_group_id=89
AND paf.person_id IN (768,
)
AND paf.current_employee_flag = 'Y'
AND paf.current_emp_or_apl_flag = 'Y'
AND trunc(SYSDATE) BETWEEN paf.effective_start_date AND
nvl(paf.effective_end_date, SYSDATE + 1)
AND paf.person_type_id IN
(SELECT person_type_id
FROM apps.per_person_types
WHERE upper(user_person_type) = 'EMPLOYEE');
l_validate Boolean := False;
l_period_of_service_id Number;
l_object_version_number Number;
l_actual_notice_period_date Date;
l_effective_date date; -- This should be a termination date
l_supervisor_warning Boolean;
l_event_warning Boolean;
l_interview_warning Boolean;
l_review_warning Boolean;
l_recruiter_warning Boolean;
l_asg_future_changes_warning Boolean;
l_f_asg_future_changes_warning Boolean;
l_pay_proposal_warning Boolean;
l_dod_warning Boolean;
l_final_process_date DATE ;
l_org_now_no_manager_warning Boolean;
l_entries_changed_warning Varchar2(255);
l_f_entries_changed_warning Varchar2(255);
l_alu_change_warning Varchar2(255);
l_person_type_id Number;
l_last_std_process_date_out Date; -- This should be a termination date
cnt NUMBER := 0;
l_errcount NUMBER := 0;
begin
For c_emp_rec in c_emp_cur
Loop
l_period_of_service_id := c_emp_rec.period_of_service_id;
l_object_version_number := c_emp_rec.object_version_number;
l_actual_notice_period_date := l_final_process_date ; --c_emp_rec.end_date;
l_effective_date := l_final_process_date ; --sysdate;
-- l_person_type_id := c_emp_rec.person_type_id;
Begin
hr_ex_employee_api.actual_termination_emp
(p_validate => l_validate
,p_effective_date => l_effective_date
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_actual_termination_date => l_actual_notice_period_date
,p_last_standard_process_date => l_actual_notice_period_date
,p_person_type_id => '1323'
,p_leaving_reason => 'R'
,p_last_std_process_date_out => l_last_std_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
,p_alu_change_warning => l_alu_change_warning
);
Below updating accepted termination date, notified termination date, projected termination date will affect the employee who resigned by getting final payout. The dates should not be equal to the same termination date so passing it as null
From:
hr_ex_employee_api.update_term_details_emp
(p_validate => l_validate
,p_effective_date => l_effective_date
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_accepted_termination_date => l_effective_date
,p_leaving_reason => 'R'
,p_notified_termination_date => l_effective_date
,p_projected_termination_date => l_effective_date
);
Modified To:
hr_ex_employee_api.update_term_details_emp(p_validate => l_validate
,p_effective_date => l_effective_date
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_leaving_reason => 'R' );
l_final_process_date := l_effective_date;
hr_ex_employee_api.final_process_emp
(p_validate => l_validate
,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
);
cnt := cnt + 1;
Exception
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error for employee person id : '|| c_emp_rec.person_id ||'Error message: ' || sqlerrm);
ROLLBACK;
l_errcount := l_errcount + 1;
End;
End Loop;
commit;
DBMS_OUTPUT.put_line ('Number of rows updated successfully : ' || cnt);
DBMS_OUTPUT.put_line ('Total Error records which is rollback : ' || l_errcount);
DBMS_OUTPUT.put_line (' Total number of records which need to be updated : ' || ( cnt + l_errcount ));
End;
/
same above as the function in which I used for termination
FUNCTION terminate_employee(emp_rec IN OUT emprec
,errbuff OUT VARCHAR2
,retcode OUT VARCHAR2) RETURN BOOLEAN AS
dt_end DATE := NULL;
l_validate BOOLEAN := FALSE;
l_period_of_service_id NUMBER;
l_object_version_number per_all_people_f.object_version_number%TYPE;
l_actual_notice_period_date DATE;
l_effective_date DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_f_asg_future_changes_warning BOOLEAN;
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
--l_final_process_date DATE := SYSDATE; -- Commented by sathish for V2
l_final_process_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_entries_changed_warning VARCHAR2(255);
l_f_entries_changed_warning VARCHAR2(255);
l_alu_change_warning VARCHAR2(255);
l_person_type_id NUMBER;
l_last_std_process_date_out DATE;
cnt NUMBER := 0;
l_errcount NUMBER := 0;
l_op_name VARCHAR2(100);
ln_business_group_id per_business_groups.business_group_id%TYPE;
-- Local Constant Declaration
c_h_comp_code CONSTANT VARCHAR2(10) := '21181';
c_s_comp_code CONSTANT VARCHAR2(10) := '21301';
c_h_op_name CONSTANT VARCHAR2(100) := 'Operating unit 1';
c_s_op_name CONSTANT VARCHAR2(100) := 'Operting unit 2 ';
c_person_type_id per_person_types.person_type_id%TYPE;
CURSOR c_emp_cur IS
SELECT pos.period_of_service_id
,pos.object_version_number
,pos.date_start
,paf.person_id --,paf.person_type_id
FROM apps.per_all_people_f paf
,apps.per_periods_of_service pos
WHERE paf.person_id = pos.person_id
AND paf.business_group_id = ln_business_group_id --89
AND paf.employee_number = emp_rec.employee_number
AND trunc(SYSDATE) BETWEEN paf.effective_start_date AND
nvl(paf.effective_end_date, SYSDATE + 1)
AND paf.person_type_id IN
(SELECT person_type_id
FROM apps.per_person_types
WHERE upper(user_person_type) = g_employee_type) -- 'EMPLOYEE'
AND emp_rec.status = g_val_terminate -- Comment this line
AND paf.current_employee_flag = 'Y'
AND paf.current_emp_or_apl_flag = 'Y'
AND paf.attribute10 IS NOT NULL;
BEGIN
fnd_file.put_line(fnd_file.log, 'Before Terminating Employee');
fnd_file.put_line(fnd_file.log, 'emp_rec.employee_number ' || emp_rec.employee_number);
fnd_file.put_line(fnd_file.log, 'emp_rec.status ' || emp_rec.status);
fnd_file.put_line(fnd_file.log, 'emp_rec.hire_date ' || emp_rec.hire_date);
fnd_file.put_line(fnd_file.log, 'emp_rec.termination_date ' || emp_rec.termination_date);
-- Fetch the operating unit name based on the employee company code
-- If company code is 21181, the operating unit to which employee belongs is OU
IF (emp_rec.oracle_acct_segment1 = c_h_comp_code)
THEN
l_op_name := c_h_op_name;
-- If company code is 21301, the operating unit to which employee belongs is OU
ELSIF (emp_rec.oracle_acct_segment1 = c_slk_comp_code)
THEN
l_op_name := c_s_op_name;
-- For other company codes, make the OU to NULL
ELSE
l_op_name := NULL;
END IF;
fnd_file.put_line(fnd_file.log
,'The employee belongs to operating unit ' || l_op_name);
-- Fetch the business group id and person type id for employee person type based on the operating unit
SELECT haou.business_group_id
INTO ln_business_group_id
FROM hr.hr_all_organization_units haou
WHERE haou.name = l_op_name;
fnd_file.put_line(fnd_file.log
,'The employee belongs to business group id ' ||
ln_business_group_id);
SELECT person_type_id
INTO c_person_type_id
FROM per_person_types
WHERE business_group_id = ln_business_group_id
AND user_person_type = g_ex_employee_type; -- 'Ex-employee'
FOR c_emp_rec IN c_emp_cur LOOP
l_period_of_service_id := c_emp_rec.period_of_service_id;
l_object_version_number := c_emp_rec.object_version_number;
-- l_actual_notice_period_date := c_emp_rec.end_date;
l_effective_date := nvl(emp_rec.termination_date, SYSDATE);
l_last_std_process_date_out := nvl(emp_rec.termination_date, SYSDATE); -- Added by Sathish for V2
-- l_person_type_id := c_emp_rec.person_type_id;
BEGIN
hr_ex_employee_api.actual_termination_emp(p_validate => l_validate
,p_effective_date => l_effective_date
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_actual_termination_date => l_effective_date
,p_last_standard_process_date => l_effective_date
,p_person_type_id => c_person_type_id
,p_leaving_reason => 'R'
,p_last_std_process_date_out => l_last_std_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
,p_alu_change_warning => l_alu_change_warning);
/* hr_ex_employee_api.update_term_details_emp(p_validate => l_validate
,p_effective_date => l_effective_date
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_accepted_termination_date => l_effective_date
,p_leaving_reason => 'R'
,p_notified_termination_date => l_effective_date
,p_projected_termination_date => l_effective_date); */
hr_ex_employee_api.update_term_details_emp(p_validate => l_validate
,p_effective_date => l_effective_date
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_object_version_number
,p_leaving_reason => 'R' );
l_final_process_date := l_last_std_process_date_out;
hr_ex_employee_api.final_process_emp(p_validate => l_validate
,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);
cnt := cnt + 1;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log
,'Error for employee person id : ' || c_emp_rec.person_id ||
'Error message: ' || SQLERRM);
ROLLBACK;
l_errcount := l_errcount + 1;
g_error_message := 'Error(' || SQLCODE || ') ' || SQLERRM;
g_key_info := NULL;
g_error_code := 'terminate_employee';
RETURN FALSE;
END;
END LOOP;
COMMIT;
fnd_file.put_line(fnd_file.log, 'After Terminating Employee');
fnd_file.put_line(fnd_file.log, 'Number of persons terminated successfully : ' || cnt);
fnd_file.put_line(fnd_file.log, 'Total Error records which is Rollback : ' || l_errcount);
fnd_file.put_line(fnd_file.log
,' Total number of persons which need to be terminated : ' || (cnt + l_errcount));
RETURN TRUE;
END terminate_employee;
How to validate: Check the passed parameter date and reason in below tables
SELECT * FROM APPS.per_all_people_f paf where paf.person_id IN (768,
15148);
select * from per_periods_of_service where person_id IN (768,
15148);
select * from apps.per_all_assignments_f where person_id IN (768,
15148);
Front end:
So when you terminate using this API (as you mentioned), it uses a few functions. The best way to check this is in the application as the user above mentioned.
The pathway is this (assuming your menus aren't custom): US Super HRMS Manager (or comparable responsibility) > Enter and Maintain (Form)> Others(button) >End Employment. This will show if you properly updated the termination dates including final process, last standard process and actual. It will also show you the leaving reason. These are from your #2.
If you want to look it up in the table, the table is per_periods_of_service. It's just an update to insert the data. I don't think that there is a new row as well.
Note that once you update the final process date, the assignment record (per_all_assignments_f) will insert at least 1 row (a terminated record) and modify 1 row (to update the effective end date of the existing record). It will also insert a 'terminated' assignment record if your dates do not match. The way to check this in the app is:
US Super HRMS Manager (or comparable responsibility) > Enter and Maintain (Form)>Assignments (button). This might require date tracking depending on the level of checks you do.
Also as you mentioned, it will update per_people_f with an update and insert row.
Lastly, you can view the code from these API's using SQL to see the tables adjusted.
Good luck.
------------------------------------------------------------------------------------------------
ORACLE HRMS
-------------------------------
Terminations
----------------
FREQUENTLY ASKED QUESTIONS
-----------------------------------------------
31-JAN-2002
CONTENTS
---------------
1) Where do you terminate an employee in the application?
2) How do you terminate an employee?
3) What API’s are available for termination?
4) How do you rehire a terminated employee?
5) How do you perform a reverse termination?
6) How do you populate the Leaving Reason list of values on Termination form?
7) What is the significance of the fields in the Termination Dates zone on Termination form?
i.e. Notified, Projected, Actual, Last Standard Process, Final Process.
8) How do you terminate in a shared HR installation?
9) How do you change the end date (termination date) in a shared HR installation?
10)R11.5 – Why does terminating an employee produce error APP-BEN-92108?
11) How do you terminate in Self Service Human Resources?
12) How do you pay terminated employees in Oracle Payroll?
13) What happens to element entries upon termination?
14) Why do element entries remain end dated upon reverse termination?
QUESTIONS AND ANSWERS
---------------------------------------
1) Navigate to US HR or US HRMS Manager (or equivalent) > People > Enter and Maintain >
Others > End Employment.
2) On Terminate screen, select Actual Process Date and/or Final Process Date and click on
Terminate button. Optionally, enter ‘Leaving Reason,’ ‘Notified,’ ‘Projected,’ ‘Last Standard
Proces’ values.
3) Hr_ex_employee_api (File name: peexeapi.pkb)
This file contains three (3) procedures.....
hr_ex_employee_api.actual_termination_emp
hr_ex_employee_api.update_term_details_emp
hr_ex_employee_api.final_process_emp
Note: The API's have to be called in that order.
4) Ensure the Final Process Date field is populated on Terminate screen. Navigate back to
People form and change person type of Ex-employee to Employee from list of values.
5) On Terminate screen, datetrack to termination date and select Reverse Termination button.
6) This is controlled by Lookup Type ‘LEAV_REAS.’ Add any number of records to this
lookup. Navigation: US HRMS Manager > Other Definitions > Application Utilities
Lookups
7) Notification Date:
This is for information only and is optional.
Projected Date:
This is for information only and is optional.
Actual Date:
This corresponds to the actual date the employee left the organization.
When you DateTrack after this date the People form will show the person as an
Ex-Employee. This is required.
Last Standard Process Date:
Where Oracle Payroll is used this will be the date after which no further
payroll processing can occur. This is optional and can be left blank.
Element entries defined against the employee would be end dated with this
date rather than the actual date if they were defined with the standard rule
of Last Standard Process.
Final Process Date:
This will represent the last date for late payments for Oracle Payroll users.
An example would be where certain elements were paid in arrears - such
as commission. Element entries defined against the employee would be end dated
with this date rather than the actual date if they were defined with the
standard rule of Final Close.
8) In shared HR installations, you’ll navigate to the shared People form (PERWSEMP). Query
employee and populate End Date field.
Note: The navigation varies based on financial application you are using. For example:
Responsibility Navigation
--------------------------------- -------------------------------
PO Super User GUI Setup/Personnel/Employees
PA Projects Costing Super User GUI Setup/Human Resources/Employees
AP Manager GUI Employees/Enter Employees
9) This example uses Projects responsibility:
A. Select the Responsibility Project Billing Super User or Costing Super User.
B. Navigate: Setup -> Human Resources -> Employees
C. Query the employee.
D. Clear the End Date field.
E. Save.
F. Enter the new end-date.
G. Save.
10) You must define a default monthly payroll for each business group you maintain.
Benefit assignments are linked to the default monthly payroll in order to calculate premiums
for continuing benefits such as COBRA. (Oracle Development recommends defining a Monthly
payroll as default at the GRE level.)
The monthly default payroll does not need to be unique. If a monthly payroll is already
set up for your business group, you may choose to use that payroll as a default, rather than
setting up a new one.
To assign a default payroll for Benefits:
3.1. (N)Work Structures -> Organization -> Description.
3.2. Query the Organization.
3.3. Under Organization Classifications -> Business Group click on the
Others button.
3.4. Choose Benefits Defaults Enter the Monthly payroll you will use for
the default for Benefits.
11) Manager Self-Service > Termination (US) > Select employee > terminate
12) Ensure the terminated employee has an assignment status related to ‘Process’ payrolls. You
can confirm which statuses you have set under Work Structures > Status. Element entries on the
terminated employee should be non-recurring elements.
13) All element entries are end-dated.
14) US seeded elements presently set as 'Final Close,’ have a formula-based stop rule that fires
the first time the element is processed after actual termination. Since the elements are enddated
by the payroll run and not as a direct result of the termination, the reverse termination cannot
unendate the element entry with any certainty.
We have enabled 'Last Standard Process' for the US so you can create a new element and set the
termination rule to the new option.
You cannot change the termination rule for elements once entries and run results exist.
credit: cquick, ssnyder, merickso
