Terminate CWK

The following code could be used fro terminating CWK :

terminate CWK

PROCEDURE XXHR_CWK_TERMINATE
(ERRBUF OUT NOCOPY varchar2,
RETCODE OUT NOCOPY VARCHAR2 )
/* =====================================================================
— NAME : XXHR_CWK_TERMINATE

— PROGRAM TYPE : Procedure

— DESCRIPTION : This is used to terminate an CWK
— INPUTS : None
— OUTPUTS : None
— ===================================================================*/
AS

cursor c_cwk_terminate
IS
SELECT
EMPLOYEE_NUMBER,
TO_DATE(TO_CHAR(last_working_date,’DD-MON-RRRR’),’DD-MON-RRRR’) -1 TO_DATE,
TO_DATE(TO_CHAR(LAST_WORKING_DATE,’DD-MON-RRRR’),’DD-MON-RRRR’) FROM_DATE,
ADD_MONTHS(TO_DATE(TO_CHAR(LAST_WORKING_DATE,’DD-MON-RRRR’),’DD-MON-RRRR’),3) FINAL_PROCESS_DATE,
RECORD_NO
FROM XXHR_EMP_MASTER_STG
WHERE PROCESS_FLAG =’P’;

L_DAT_EFFECTIVE_DATE DATE;
L_NUM_PERSON_ID NUMBER;
L_DAT_DATE_START DATE;
L_NUM_ovn NUMBER;
L_DAT_FINAL_PROCESS_DATE DATE;
–L_DAT_LAST_STD_PROCESS_DATE DATE;
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_CHANGED_WARNING VARCHAR2(1000);
l_bool_PAY_PROPOSAL_WARNING BOOLEAN;
L_BOOL_DOD_WARNING BOOLEAN;
l_bool_NO_MANAGER_WARNING BOOLEAN;
L_BOOL_ADDL_RIGHTS_WARNING BOOLEAN;
L_NUM_RECORD_NO NUMBER;
L_VAR_EMPLOYEE_NUMBER VARCHAR2(100);
L_VAR_PROCESS varchar2(100);
L_NUM_SUCCESSFUL_RECORDS number:= 0;
L_NUM_FAILED_RECORDS number := 0;
l_var_error varchar2(4000);

BEGIN

for c_cwk_term in c_cwk_terminate
loop

L_DAT_EFFECTIVE_DATE :=NULL;
L_NUM_PERSON_ID :=NULL;
L_DAT_DATE_START :=NULL;
L_NUM_ovn :=NULL;
L_DAT_FINAL_PROCESS_DATE :=NULL;
–L_DAT_LAST_STD_PROCESS_DATE :=NULL;
L_BOOL_SUPERVISOR_WARNING :=NULL;
l_bool_EVENT_WARNING :=NULL;
l_bool_INTERVIEW_WARNING :=NULL;
l_bool_REVIEW_WARNING :=NULL;
L_BOOL_RECRUITER_WARNING :=NULL;
L_BOOL_ASG_FUTURE_WARNING :=NULL;
l_var_ENTRIES_CHANGED_WARNING :=NULL;
l_bool_PAY_PROPOSAL_WARNING :=NULL;
L_BOOL_DOD_WARNING :=NULL;
l_bool_NO_MANAGER_WARNING :=NULL;
L_BOOL_ADDL_RIGHTS_WARNING :=NULL;
L_NUM_RECORD_NO :=NULL;
L_VAR_EMPLOYEE_NUMBER :=NULL;
L_VAR_PROCESS :=null;
l_var_error := null;

l_num_record_no := c_cwk_term.RECORD_NO;
l_var_EMPLOYEE_NUMBER := c_cwk_term.EMPLOYEE_NUMBER ;

begin
select PPS.OBJECT_VERSION_NUMBER, PAPF.PERSON_ID,PPS.DATE_START
INTO l_num_ovn, l_num_person_id, l_dat_date_start
FROM per_all_people_f papf,
PER_PERIODS_OF_PLACEMENT_V pps
WHERE papf.person_id = pps.person_id
and PAPF.BUSINESS_GROUP_ID = FND_PROFILE.value(‘PER_BUSINESS_GROUP_ID’)
AND papf.attribute1 = C_CWK_TERM.EMPLOYEE_NUMBER
and papf.current_npw_flag = ‘Y’
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 CWK’
WHERE RECORD_NO = l_num_record_no;
COMMIT;
L_VAR_PROCESS := ‘N’;

END;
IF NVL(L_VAR_PROCESS,’Y’) !=’N’
THEN
begin
HR_CONTINGENT_WORKER_API.TERMINATE_PLACEMENT(
P_VALIDATE => FALSE
,P_EFFECTIVE_DATE => C_CWK_TERM.FROM_DATE
,P_PERSON_ID => L_NUM_PERSON_ID
,P_DATE_START => l_dat_date_start
,P_OBJECT_VERSION_NUMBER => L_NUM_OVN
,P_ACTUAL_TERMINATION_DATE => C_CWK_TERM.FROM_DATE
,P_FINAL_PROCESS_DATE => C_CWK_TERM.FINAL_PROCESS_DATE — final process date is 3 months after the actual termination date
,p_last_standard_process_date => C_CWK_TERM.final_process_date
— ,p_termination_reason => ‘Deceased’
,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_CHANGED_WARNING
,P_PAY_PROPOSAL_WARNING => L_BOOL_PAY_PROPOSAL_WARNING
,P_DOD_WARNING => L_BOOL_DOD_WARNING
,P_ORG_NOW_NO_MANAGER_WARNING => L_BOOL_NO_MANAGER_WARNING
,P_ADDL_RIGHTS_WARNING => L_BOOL_ADDL_RIGHTS_WARNING
);

L_NUM_SUCCESSFUL_RECORDS:= L_NUM_SUCCESSFUL_RECORDS + 1;

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

EXCEPTION
when OTHERS then
l_var_error := sqlerrm;
UPDATE XXHR_EMP_MASTER_STG
set PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to terminate CWK’||l_var_error
WHERE RECORD_NO = l_num_record_no;

COMMIT;
L_Var_PROCESS := ‘N’;
END;
END IF;

IF L_VAR_PROCESS = ‘N’
THEN
L_NUM_FAILED_RECORDS :=L_NUM_FAILED_RECORDS +1;
end if;

end loop;
FND_FILE.PUT_LINE (FND_FILE.log, ‘Number of Successful records :’||L_NUM_SUCCESSFUL_RECORDS );
fnd_file.put_line (fnd_file.log, ‘Number of Failed records :’||l_num_failed_records );
END XXHR_CWK_TERMINATE;

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