Terminate Employee

For terminating Employees the following code needs to be used:

terminate emp

procedure XXHR_EX_EMP_CREATE
(ERRBUF OUT NOCOPY varchar2,
RETCODE OUT NOCOPY VARCHAR2 )
/* =====================================================================
— NAME : xxhr_ex_emp_create

— PROGRAM TYPE : Procedure

— DESCRIPTION : terminated employees

— INPUTS : None
— OUTPUTS : None
— ===================================================================*/
AS
/*
================================================================================
–Cursor to get data from Staging Table
================================================================================
*/
CURSOR c_master—(p_batch_id number)
IS
SELECT
employee_number,
business_group,
last_name,
record_no,
process_flag,
person_type,
last_working_date,
error_code,
error_msg,
last_updated_by,
last_update_date,
created_by,
last_update_login
FROM XXHR_EMP_MASTER_STG
WHERE
1=1
and process_flag = ‘P’
AND migration_type = ‘INSERT’
and person_type = ‘Ex-Employee’;
— and batch_id = p_batch_id
— and batch_id = xxhremp_seq.curval;
— and employee_number = ‘91404898’;

/*
================================================================================
–Local Variable
================================================================================
*/
l_num_period_of_sevc_id NUMBER;
l_num_ovn NUMBER;
l_num_record_no NUMBER;
l_var_EMPLOYEE_NUMBER NUMBER;
l_bool_supervisor_warning BOOLEAN;
l_bool_event_warning BOOLEAN;
l_bool_interview_warning BOOLEAN;
l_bool_review_warning BOOLEAN;
l_bool_recruiter_warning BOOLEAN;
l_bool_asg_future_warning BOOLEAN;
l_var_entries_warning VARCHAR2(100);
l_bool_pay_proposal_warning BOOLEAN;
L_BOOL_DOD_WARNING BOOLEAN;
L_VAR_FLAG varchar2(100);
L_NUM_SUCCESSFUL_REC number;
L_NUM_FAILED_REC number;
L_DAT_FINAL_PROCESS_DATE DATE;
L_BOOL_ORG_NO_MGR_WARNING BOOLEAN;
L_BOOL_ASG_FUTURE_CHNG_WARN BOOLEAN;
L_VAR_ENT_CHNG_WARN varchar2(100);
L_NUM_OVN2 number;
L_NUM_PERIOD_OF_SEVC_ID2 number;

begin

for r_master in c_master—(xxhremp_seq.currval)
loop
l_num_record_no := r_master.record_no;
l_var_EMPLOYEE_NUMBER := r_master.employee_number;
— IF upper(r_master.person_type) =’EX-EMPLOYEE’
— THEN
BEGIN
SELECT pps.period_of_service_id, pps.object_version_number
INTO l_num_period_of_sevc_id, l_num_ovn
FROM per_all_people_f papf,
per_periods_of_service pps
WHERE papf.person_id = pps.person_id
and PAPF.BUSINESS_GROUP_ID = FND_PROFILE.value(‘PER_BUSINESS_GROUP_ID’)
and papf.attribute1 = r_master.employee_number
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date;
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_MASTER_STG
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG
||’unable to fetch service period id from ex employee for employee Number ‘
||l_var_EMPLOYEE_NUMBER
WHERE record_no = l_num_record_no;
commit;
l_var_flag :=’N’;
end;
if nvl(l_var_flag,’Y’) != ‘N’
then
begin

HR_EX_EMPLOYEE_API.actual_termination_emp
(p_validate => false
,p_effective_date => r_master.last_working_date
,p_period_of_service_id => l_num_period_of_sevc_id
,p_object_version_number => l_num_ovn
,p_actual_termination_date => r_master.last_working_date
,p_last_standard_process_date => r_master.last_working_date
,p_supervisor_warning => l_bool_supervisor_warning
,p_event_warning => l_bool_event_warning
,p_interview_warning => l_bool_interview_warning
,p_review_warning => l_bool_review_warning
,p_recruiter_warning => l_bool_recruiter_warning
,p_asg_future_changes_warning => l_bool_asg_future_warning
,p_entries_changed_warning => l_var_entries_warning
,p_pay_proposal_warning => l_bool_pay_proposal_warning
,p_dod_warning => l_bool_dod_warning
);

L_NUM_OVN2 :=L_NUM_OVN;
L_DAT_FINAL_PROCESS_DATE := to_date(R_MASTER.LAST_WORKING_DATE ,’DD-MON-RRRR’)+ 90;
L_NUM_PERIOD_OF_SEVC_ID2 := L_NUM_PERIOD_OF_SEVC_ID;
begin
HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP
(P_VALIDATE =>false
,P_PERIOD_OF_SERVICE_ID => L_NUM_PERIOD_OF_SEVC_ID2
,P_OBJECT_VERSION_NUMBER => L_NUM_OVN2
,P_FINAL_PROCESS_DATE => L_DAT_FINAL_PROCESS_DATE
,P_ORG_NOW_NO_MANAGER_WARNING => L_BOOL_ORG_NO_MGR_WARNING
,P_ASG_FUTURE_CHANGES_WARNING => L_BOOL_ASG_FUTURE_CHNG_WARN
,P_ENTRIES_CHANGED_WARNING => L_vaR_ENT_CHNG_WARN
);
EXCEPTION when OTHERS then
UPDATE XXHR_EMP_MASTER_STG
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG
||’Unable to insert last process date’
WHERE record_no = l_num_record_no;

FND_FILE.PUT_LINE (FND_FILE.log, ‘Unable to insert last process date’||R_MASTER.EMPLOYEE_NUMBER);
commit;
L_VAR_FLAG := ‘N’;

end;

update XXHR_EMP_MASTER_STG
set PROCESS_FLAG = ‘S’
where RECORD_NO = L_NUM_RECORD_NO;

l_num_SUCCESSFUL_REC := l_num_SUCCESSFUL_REC + 1;

commit;
exception when others then
UPDATE XXHR_EMP_MASTER_STG
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG
||’Unable to terminate emp’
WHERE record_no = l_num_record_no;
fnd_file.put_line (fnd_file.log, ‘Unable to terminate emp for employee Number ‘||r_master.employee_number);
commit;
l_var_flag := ‘N’;
end;

— end if;
end if;
IF nvl(l_var_flag,’Y’) = ‘N’
THEN
l_num_FAILED_REC := l_num_FAILED_REC + 1;
END IF;

— end_time:=sysdate;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
‘Number of successful records is ‘
|| l_num_successful_rec
|| ‘ , failed records is ‘
|| L_NUM_FAILED_REC
);
end;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s