Terminate Employee record in Oracle HRMS

[anilpassi]

DECLARE


l_validate_mode BOOLEAN := FALSE;
l_business_group_id NUMBER;
l_employee_no NUMBER;
l_date1 DATE;

-- API Return Variables

l_person_id NUMBER;
l_assignment_id NUMBER;
l_obj_version_number NUMBER;
l_eff_start_date DATE;
l_eff_end_date DATE;
l_comment_id NUMBER;
l_error_text VARCHAR2(1000);

l_last_standard_process_date DATE;
l_obj_no1 NUMBER;
l_obj_no2 NUMBER;
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_entries_changed_warning VARCHAR2(10);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_ppos_id NUMBER;

-- Constant variables

l_module_id CONSTANT VARCHAR2(30) := 'XXFTHR_MIGRATION019';

-- Error Handling variables

l_error_message VARCHAR2(150);
l_error_code VARCHAR2(30);
l_error_statement VARCHAR2(50);

-- Count Variables
l_count_total NUMBER := 0;
l_count_success NUMBER := 0; -- Total number of successful rows

l_cnt1 NUMBER :=0;
l_errm VARCHAR2(100);
l_err_at_stmt NUMBER;

-- Cursor definitions


CURSOR c_emp_ter IS
SELECT rowid row_id
, employee_number
, lrl_leave_date
, termination_date
, leave_cd1
, lieu_hours_flat
, lieu_hours_prem
, holiday_balhours
, in_lieu_notice_amount
, assignment_id
, person_id
FROM xxft_emp_termination
WHERE record_loaded='V'
--AND lrl_job_no=1
--AND employee_number = '1934344'
ORDER BY employee_number;

CURSOR c_ppos (c_person_id IN NUMBER) IS
SELECT period_of_service_id, object_version_number
FROM per_periods_of_service
WHERE person_id = c_person_id;


CURSOR c_FocusThread_code (c_legacy_type IN VARCHAR2, c_legacy_code IN VARCHAR2) IS
SELECT FocusThread_lookup_cd
FROM xxft_lookup_values
WHERE legacy_lookup_cd = c_legacy_code
AND legacy_lookup_type = c_legacy_type;

CURSOR c_lookup_code (c_lookup_type IN VARCHAR2, c_meaning IN VARCHAR2) IS
SELECT lookup_code
FROM hr_lookups
WHERE meaning = c_meaning
AND lookup_type = c_lookup_type;

--
BEGIN

DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Started Terminate Employees at: '||to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('--');

-- Get business group id

SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = 'Setup Business Group';

--First update person_id and date_from using a corelated subquery
--to xxft_employee

UPDATE xxft_emp_termination a
SET person_id = (SELECT person_id
FROM xxft_employee b
WHERE a.employee_number = b.employee_number);

UPDATE xxft_emp_termination a
SET assignment_id = (SELECT assignment_id
FROM xxft_employee b
WHERE a.employee_number = b.employee_number);


-- ************************************************************
-- Start Main Loop
-- ************************************************************


FOR rec IN c_emp_ter LOOP

l_err_at_stmt :=10;

l_employee_no := rec.employee_number;

l_person_id := NULL;
l_assignment_id := NULL;

l_last_standard_process_date := last_day(rec.termination_date);
l_obj_no1 := NULL;
--l_obj_no2 := NULL;
l_supervisor_warning := NULL;
l_event_warning := NULL;
l_interview_warning := NULL;
l_review_warning := NULL;
l_recruiter_warning := NULL;
l_asg_future_changes_warning := NULL;
l_entries_changed_warning := NULL;
l_pay_proposal_warning := NULL;
l_dod_warning := NULL;

l_cnt1 := l_cnt1 + 1;

BEGIN

OPEN c_ppos(rec.person_id);
FETCH c_ppos INTO l_ppos_id, l_obj_no1;
IF c_ppos%NOTFOUND THEN
NULL;
END IF;
CLOSE c_ppos;


--HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP
--HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP
--HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP

hr_ex_employee_api.actual_termination_emp(
--p_validate => FALSE
p_effective_date => rec.termination_date
,p_period_of_service_id => l_ppos_id
,p_actual_termination_date => rec.termination_date
,p_person_type_id => 6 -- ex employee id
,p_assignment_status_type_id => 3 -- Terminate Assignment status
,p_leaving_reason => rec.leave_cd1
-- OUT
,p_last_standard_process_date => l_last_standard_process_date
,p_object_version_number => l_obj_no1
,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
);


hr_ex_employee_api.update_term_details_emp
(--p_validate
p_effective_date => rec.termination_date
,p_period_of_service_id => l_ppos_id
--,p_termination_accepted_person
--,p_accepted_termination_date
--,p_comments
--,p_leaving_reason
,p_notified_termination_date => rec.termination_date
,p_projected_termination_date => rec.termination_date
-- OUT
,p_object_version_number => l_obj_no1
) ;


UPDATE per_all_assignments_f
SET effective_end_date = l_last_standard_process_date
WHERE effective_end_date = to_date ('31-DEC-4712','DD-MON-YYYY')
AND assignment_id = rec.assignment_id;

UPDATE xxft_emp_termination
SET record_loaded = 'Y'
WHERE rowid=rec.row_id;

l_count_success := l_count_success + 1;

EXCEPTION
WHEN OTHERS THEN

l_errm := substr(ltrim(sqlerrm),1,100);

-- dbms_output.put_line (to_char(l_employee_no)||' : '||
-- to_char(l_date1,'DD-MON-YYYY'));

-- dbms_output.put_line(sqlerrm);

INSERT INTO xxft_error_log ( module_id,
emp_no,
error_desc,
run_date)
VALUES ( l_module_id
, rec.employee_number
, l_errm
, sysdate
);

UPDATE xxft_emp_termination
SET record_loaded = 'N'
WHERE rowid=rec.row_id;

END;

l_err_at_stmt :=40;

IF l_cnt1>=10 THEN
COMMIT;
l_cnt1:=0;
END IF;


END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('No of Terminated records '||to_Char(l_count_success));


IF l_count_success != l_count_total THEN
NULL;
--DBMS_OUTPUT.PUT_LINE('-- *************************************************************');
--DBMS_OUTPUT.PUT_LINE('-- **Please examine the error table for a list of errored rows**');
--DBMS_OUTPUT.PUT_LINE('-- *************************************************************');
END IF;

DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- Finished Terminate Employees load at: '||to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');


---------
---------
EXCEPTION
---------
---------

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('No of records terminated '||to_Char(l_count_success));
DBMS_OUTPUT.PUT_LINE('Error at '|| to_char(l_err_at_stmt) );
DBMS_OUTPUT.PUT_LINE(SQLERRM||SQLCODE);

l_error_message := substr(ltrim(sqlerrm),1,100);
l_error_code := sqlcode;


INSERT INTO xxft_error_log ( module_id,
emp_no,
error_desc,
run_date)
VALUES ( l_module_id
, Null
, l_error_statement||', '||l_error_message||', Error code: '||l_error_code
, sysdate
);

DBMS_OUTPUT.PUT_LINE('Unhandled termination of Terminate Employees at: '||to_char(SYSDATE, 'DD-MON-RRRR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------');
END;
/

[Anil3107]

hi

[Anil3107]

hii

[Anil3107]

Hi Anil,

I need to call the hr_ex_employee_api from OAF custom page. Can I use the code provided by you. Also I have seen that you have used some custom tables in this.

I am new to this fields. Can you share your suggestions.

Thanks

[appsguy]

Hi Anil,

Thanks for the wonderful site. When I am terminating an employee using this AOI, I am receiving the below error message.

API Error:ORA-20001: A person type with a system person type EX_EMP must be specified.

I am using the person_type_id = 6.

I searched in google and metalink but didn't find much information. Can you please help me in this.

Thanks,

[appsguy]

I checked the table per_person_types and the value is 9 for EX-EMP. Now I am not getting that error.