Add PQH Roles and add users to it

Complete procedure to add PQH Roles and add users to it

PROCEDURE mpl_cse_create_roles(
errbuf OUT NOCOPY  VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
/*************************************************************************************
**  Name       : MPL_CSE_CREATE_ROLES
**  DESCRIPTION: This procedure used to define roles for AME
**************************************************************************************/
l_num_role_id      pqh_roles.role_id%TYPE;
l_num_object_version_number   pqh_roles.object_version_number%type;
l_num_person_extra_info_id   per_people_extra_info.person_extra_info_id%type;
l_num_ovn_pie      per_people_extra_info.object_version_number%type;
l_num_person_id      per_people_extra_info.person_id%type;
l_num_loop_count        NUMBER;
l_var_user              VARCHAR2(1000);
l_var_err               VARCHAR2(1000);
l_num_addusrrole_id     NUMBER;
l_var_process           VARCHAR2(10);
CURSOR cur_role
IS
SELECT *
FROM mpl_cse_role_conv_stg
WHERE process_flag IS NULL;
BEGIN
l_num_object_version_number := NULL;
l_num_role_id               := NULL;
l_num_person_id             := NULL;
l_num_person_extra_info_id  := NULL;
l_num_ovn_pie               := NULL;
FOR cur_role_rec IN cur_role
LOOP
BEGIN
pqh_roles_api.create_role
( p_validate          => FALSE ,
p_role_id           => l_num_role_id ,
p_role_name         => cur_role_rec.role_name ,
p_business_group_id   => 0 ,
p_role_type_cd    => ‘EMP’ ,
p_enable_flag    => ‘Y’ ,
p_object_version_number  => l_num_object_version_number ,
p_effective_date    => SYSDATE ,
p_information_category  => cur_role_rec.location
);
COMMIT;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
l_var_err     := SQLERRM;
l_var_process :=’N’;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = ‘Role Could not be  created ‘
|| l_var_err
WHERE role_name = cur_role_rec.role_name;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Role Could not be  created’);
END;
/*adding users to role*******/
/*checking number of user in a role*/
BEGIN
SELECT DECODE(cur_role_rec.member_1, NULL, 0, 1) + DECODE(cur_role_rec.member_2, NULL, 0, 1) + DECODE(cur_role_rec.member_3, NULL, 0, 1)
INTO l_num_loop_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_num_loop_count := 0;
l_var_process    :=’N’;
END;
IF cur_role_rec.member_1 IS NOT NULL OR cur_role_rec.member_2 IS NOT NULL OR cur_role_rec.member_3 IS NOT NULL THEN
FOR i IN 1..l_num_loop_count
LOOP
l_var_user          := NULL;
l_num_person_id     := NULL;
l_num_addusrrole_id := NULL;
l_var_process       := NULL;
IF i                 =1 THEN
l_var_user        := cur_role_rec.member_1_emp_num;
ELSIF i              =2 THEN
l_var_user        := cur_role_rec.member_2_emp_num;
ELSIF i              =3 THEN
l_var_user        := cur_role_rec.member_3_emp_num;
END IF;
BEGIN
SELECT person_id
INTO l_num_person_id
FROM per_all_people_f
WHERE employee_number =l_var_user
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_var_process :=’N’;
l_var_user    := NULL;
dbms_output.put_line(‘person_id:’||SQLERRM);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ User could not be added to Role. Employee Number: ‘
||cur_role_rec.member_1_emp_num
WHERE role_name = cur_role_rec.role_name;
END;
BEGIN
SELECT role_id
INTO l_num_addusrrole_id
FROM pqh_roles
WHERE role_name = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘role_id:’||SQLERRM);
l_var_process       :=’N’;
l_num_addusrrole_id := NULL;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ Role not valid’
WHERE role_name = cur_role_rec.role_name;
END;
IF NVL(l_var_process,’Y’) !=’N’ THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
( p_validate       => FALSE ,
p_person_id      => l_num_person_id ,
p_information_type     => ‘PQH_ROLE_USERS’ ,
p_pei_information_category   => ‘PQH_ROLE_USERS’ ,
p_pei_information3     => l_num_addusrrole_id ,
p_pei_information4     => ‘N’ ,
p_pei_information5     => ‘Y’ ,
p_pei_information9     => ‘N’ ,
p_person_extra_info_id    =>l_num_person_extra_info_id ,
p_object_version_number   =>l_num_ovn_pie
);
IF l_num_person_extra_info_id IS NOT NULL THEN
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
ELSE
DBMS_OUTPUT.PUT_LINE(‘USER NOT LOADED :’||l_num_person_id);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘Seeded api ERROR hr_person_extra_info_api.create_person_extra_info ‘
WHERE role_name = cur_role_rec.role_name;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,’user Could not be  be added to role’);
dbms_output.put_line(‘user Could not be  be added to role’||SQLERRM);
l_var_err := SUBSTR(SQLERRM,1000);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘user could not be adde for perosn_id’
||l_num_person_id
WHERE role_name = cur_role_rec.role_name;
END;
END IF;
END LOOP;
END IF ;
COMMIT;
END LOOP;
END mpl_cse_create_roles;

Employee Bank Details API

For updating employee bank details following code can be used:

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

— PROGRAM TYPE : Procedure

— DESCRIPTION : This is used to capture Bank Details
— INPUTS : None
— OUTPUTS : None
— ===================================================================*/

AS

CURSOR CUR_BANK
is
SELECT
EUREHKA_NUMBER,
START_DATE,
BANK_CODE,
BANK_ACC_NUMBER,
BANK_COUNTRY,
PAYMENT_CURRENCY,
IBAN,
SWIFT_CODE,
— BENEFICIARY,
— PURCHASE_ORDER_NUMBER,
— SUPPLIER_NAME,
— PURCHASE_ORDER_LINE,
RECORD_NO
FROM
XXHR_EMP_BANK_STG
WHERE
PROCESS_FLAG IS NULL;
— and EUREHKA_NUMBER = ‘91400000’;

l_num_record_no number;
l_var_process varchar2(100);
l_num_priority number;
l_num_assign_id number;
l_var_pay_method varchar2(100);
l_num_pay_method_id number;
l_var_bank_country varchar2(100);
l_var_bank_currency varchar2(100);
l_err_msg varchar2(32767);
l_num_per_payment_method_id number;
l_num_external_account_id number;
l_num_object_version_number number;
l_dat_effective_start_date DATE;
l_dat_effective_end_date date;
l_num_comment_id number;
l_num_person_id number;
l_var_person_type varchar2(100);
l_num_vendor_id number;
l_num_vendor_site_id number;
l_num_po_header_id number;
l_num_line_id number;
l_bool_org_no_manager_warning boolean;
l_dte_effective_start_date date;
l_dte_effective_end_date date;
l_num_comment_id1 number;
l_bool_no_managers_warning BOOLEAN;
l_bool_other_manager_warning boolean;
l_num_soft_coding_keyflex_id number;
l_var_concatenated_segments varchar2(100);
l_bool_hourly_salaried_warning boolean;
l_num_id_flex_num number;
sql_err varchar2(30000);
l_num_person_analysis_id number;
— l_num_id_flex_num number;
l_num_analysis_criteria_id number;
l_num_pea_ovn NUMBER;
l_var_payroll_present VARCHAR2(100);
l_num_payroll_id NUMBER;
l_dat_hire_date DATE;
L_NUM_FAILED_RECORDS NUMBER;
l_num_successful_records number;
l_dat_effective date;

BEGIN
FOR R_BANK IN CUR_BANK
loop
l_num_record_no :=NULL;
l_var_process :=NULL;
l_num_priority :=NULL;
l_num_assign_id :=NULL;
l_var_pay_method :=NULL;
l_num_pay_method_id :=NULL;
l_var_bank_country :=NULL;
l_var_bank_currency :=NULL;
l_err_msg :=NULL;
l_num_per_payment_method_id :=NULL;
l_num_external_account_id :=NULL;
l_num_object_version_number :=NULL;
l_dat_effective_start_date :=NULL;
l_dat_effective_end_date :=NULL;
l_num_comment_id :=NULL;
l_num_person_id :=NULL;
l_var_person_type :=NULL;
l_num_vendor_id :=NULL;
l_num_vendor_site_id :=NULL;
l_num_po_header_id :=NULL;
l_num_line_id :=NULL;
l_bool_org_no_manager_warning :=NULL;
l_dte_effective_start_date :=NULL;
l_dte_effective_end_date :=NULL;
l_num_comment_id1 :=NULL;
l_bool_no_managers_warning :=NULL;
l_bool_other_manager_warning :=NULL;
l_num_soft_coding_keyflex_id :=NULL;
l_var_concatenated_segments :=NULL;
l_bool_hourly_salaried_warning :=NULL;
l_num_id_flex_num :=NULL;
sql_err :=NULL;
l_num_person_analysis_id :=NULL;
— l_num_id_flex_num :=NULL;
l_num_analysis_criteria_id :=NULL;
l_num_pea_ovn :=NULL;
l_var_payroll_present :=NULL;
l_num_payroll_id :=NULL;
l_dat_hire_date :=null;
l_dat_effective :=NULL;
l_num_record_no := r_bank.record_no;

BEGIN
SELECT paaf.assignment_id, papf.person_id , paaf.object_version_number, payroll_id , papf.start_date
INTO l_num_assign_id ,l_num_person_id, l_num_object_version_number, l_num_payroll_id, l_dat_hire_date
from
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND papf.attribute1 = r_bank.EUREHKA_NUMBER
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
AND trunc(SYSDATE) BETWEEN trunc(paaf.effective_start_date) AND trunc(paaf.effective_end_date);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to fetch EMP’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
l_var_process := ‘N’;
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Emp’ );
END;

/*effective date– since some emp do not have their payroll attached date from date of hire. for attaching bank details to emp
payroll is mandatory*/
begin
select min(paaf.effective_start_date)
INTO l_dat_effective
from
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND papf.attribute1 = r_bank.EUREHKA_NUMBER
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and payroll_id is not null;
— AND trunc(SYSDATE) BETWEEN trunc(paaf.effective_start_date) AND trunc(paaf.effective_end_date);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to fetch effective date’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
l_var_process := ‘N’;
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Emp’ );
END;

/*
================================================================================
–Get Person Type
================================================================================
*/

BEGIN
select HR_PERSON_TYPE_USAGE_INFO.get_user_person_type(l_dat_hire_date,l_num_person_id)
into l_var_person_type
from dual;
exception when others then
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Person_type’ );
l_var_process := ‘N’;
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
error_msg = error_msg ||’–‘||’unable to fetch Person Type’
where record_no = l_num_record_no;

END;

fnd_file.put_line (fnd_file.log, ‘l_var_person_type’ ||l_var_person_type);
/*
================================================================================
–Get Bank Country
================================================================================
*/
— IF r_bank.bank_country = ‘MY’ THEN
— l_num_priority := 1;
— ELSE
— l_num_priority := 2;
— END IF;

begin
— SELECT DECODE(upper(r_bank.BANK_COUNTRY),’MY’,’Technip MY Primary’,’Technip MY Secondary’ )
— INTO l_var_pay_method
— FROM dual;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘R_BANK.BANK_COUNTRY’||R_BANK.BANK_COUNTRY );
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘R_BANK.PAYMENT_CURRENCY’||R_BANK.PAYMENT_CURRENCY );
IF (UPPER(R_BANK.BANK_COUNTRY) =’MY’ AND upper(R_BANK.PAYMENT_CURRENCY) =’MYR’)
THEN
L_VAR_PAY_METHOD :=’Technip MY Primary’;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘primary’);
l_num_priority := ‘1’;
ELSE
L_VAR_PAY_METHOD := ‘Technip MY Secondary’;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘secondary’);
l_num_priority := ‘2’;
END IF;

EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’unable to fetch pay method’
WHERE RECORD_NO = l_num_record_no;
fnd_file.put_line (fnd_file.log, ‘issue in getting pay method name’ );
l_var_process := ‘N’;
end;
/*
================================================================================
–Get Pay method Id
================================================================================
*/
BEGIN
SELECT org_payment_method_id
INTO l_num_pay_method_id
FROM pay_org_payment_methods_f
WHERE org_payment_method_name = l_var_pay_method ;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log, ‘issue in getting pay method ID’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’issue in getting pay method ID’
WHERE record_no = l_num_record_no;
l_var_process := ‘N’;
END;
/*
================================================================================
–Get Bank Country
================================================================================
*/
begin
SELECT TERRITORY_CODE
into l_var_bank_country
FROM
FND_TERRITORIES
where TERRITORY_CODE = r_bank.bank_country;
exception
when others then
fnd_file.put_line (fnd_file.log, ‘Bank Country not found’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’Bank Country not found’
where record_no = l_num_record_no;
l_var_process := ‘N’;

end;
/*
================================================================================
–Get Currency
================================================================================
*/
begin
select ffv.flex_value
into l_var_bank_currency
from
fnd_flex_value_sets ffvs,
fnd_flex_values ffv
where
ffvs.flex_value_set_name =’XXHR_MY_BANK_CURRENCY’
and ffv.flex_value_set_id = ffvs.flex_value_set_id
and ffv.flex_value = r_bank.PAYMENT_CURRENCY;
exception
when others then
fnd_file.put_line (fnd_file.log, ‘Bank Currency not found’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’Bank Currency not found’
where record_no = l_num_record_no;
l_var_process := ‘N’;

END;
if nvl(l_var_process,’Y’)!= ‘N’
THEN
if upper(l_var_person_type) =’EMPLOYEE’or upper(l_var_person_type) =’EX-EMPLOYEE’
THEN
fnd_file.put_line (fnd_file.log, ’employee’ );

/*
================================================================================
–Get Payroll
================================================================================
*/
BEGIN
SELECT payroll_id
into l_var_payroll_present
FROM
pay_all_payrolls_f
WHERE
payroll_id = l_num_payroll_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’No Payroll’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
— l_var_process := ‘N’;
fnd_file.put_line (fnd_file.LOG, ‘No Payroll’ );
END;

BEGIN
–fnd_file.put_line (fnd_file.log, ‘EMP’);
hr_personal_pay_method_api.create_personal_pay_method
( — Input data elementds
— ——————–
p_validate => FALSE,
p_effective_date => l_dat_effective,
p_assignment_id => l_num_assign_id,
p_org_payment_method_id => l_num_pay_method_id,
p_priority => l_num_priority,
p_percentage => 100,
p_segment1 => replace(replace(replace(replace(r_bank.bank_code, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment2 => replace(replace(replace(replace(r_bank.bank_acc_number, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment3 => l_var_bank_country,
p_segment4 => l_var_bank_currency,
p_segment5 => replace(replace(replace(replace(r_bank.iban, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment6 => replace(replace(replace(replace(r_bank.swift_code, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_territory_code => ‘MY’,
— Output data elementds
— ———————
p_personal_payment_method_id => l_num_per_payment_method_id,
p_external_account_id => l_num_external_account_id,
p_object_version_number => l_num_object_version_number,
p_effective_start_date => l_dat_effective_start_date,
p_effective_end_date => l_dat_effective_end_date,
p_comment_id => l_num_comment_id
);
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘S’
WHERE record_no = r_bank.record_no;

L_NUM_SUCCESSFUL_RECORDS:= L_NUM_SUCCESSFUL_RECORDS + 1;

commit;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
— l_var_process := ‘N’;
— DBMS_OUTPUT.put_line (‘Inner Exception: ‘ || l_err_msg);
UPDATE XXHR_EMP_BANK_STG
SET PROCESS_FLAG = ‘E’,
error_msg =l_err_msg
where record_no = r_bank.record_no;
fnd_file.put_line (fnd_file.log, ‘Bank Details not created for ‘||r_bank.eurehka_number||
‘error: ‘ ||l_err_msg);
commit;
end;
— END IF;END IF;

END IF;

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

commit;
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 );
commit;
end XXHR_BANK_DETAILS;

HR_289575_ASG_ASG_NOT_EMP – error in attaching PO information for CWK

For update of PO Information for contingent worker using HR_ASSIGNMENT_API.UPDATE_CWK_ASG API: the following error often occurs:

HR_289575_ASG_ASG_NOT_EMP

Use the following query to find the error:

SELECT MESSAGE_TEXT FROM FND_NEW_MESSAGES
where message_name = ‘HR_289575_ASG_ASG_NOT_EMP’;

The issue comes due to the following :

The assignment must be an non payrolled worker assignment as of the effective date.

We cannot attach PO information to a non payrolled worker.

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;

ora-20001: HR_289609_PDP_NOT_EXIST

While Terminating CWK we might encounter the error: ora-20001: HR_289609_PDP_NOT_EXIST

Used the following query to find out the message :

select message_text from fnd_new_messages
where message_name = ‘HR_289609_PDP_NOT_EXISTS’;

Use the following query to get the Date_start that needs to be passed to P_DATE_START

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.employee_number= C_CWK_TERM.EMPLOYEE_NUMBER
and papf.current_npw_flag = ‘Y’
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date;

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;

Employee – Assignment Details

Employee and assignment details can be fetched using the following query :

SELECT
papf.attribute1 “euhreka_emp_number”,
papf.first_name “First Name”,
papf.last_name “last Name”,
PAPF.DATE_OF_BIRTH “dob”,
–papf.nationality “nationality”,
DECODE(PAPF.SEX,’F’,’Female’,’M’,’Male’) “gender”,
PAPF.START_DATE “Hire Date”,
HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(sysdate,PAPF.PERSON_ID) “Person Type”,
PAPF.EMAIL_ADDRESS “email”,
(select MEANING from HR_LOOKUPS where LOOKUP_CODE =PAPF.MARITAL_STATUS and LOOKUP_TYPE =’MAR_STATUS’) “marital_status”,
–paaf.effective_start_date “Assignment Start Date”,
HOU.name “Organization”,
HOU.ATTRIBUTE1 “COmpany BSV”,
hou.attribute1 “euHReka Org id”,
pj.name “Job”,
pg.name “Grade”,
ppf.payroll_name “Payroll”,
HL.DESCRIPTION “Location”,
–decode(HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(sysdate,PAPF.PERSON_ID), ‘Ex-Employee’,select actual_termination_date from per_periods_of_service where person_id = papf.person_id) “resign date”,
XXHR_GET_TERMINATION_DATE(papf.person_id)”Termination Date”,
(select SEGMENT1 from PAY_PEOPLE_GROUPS where PEOPLE_GROUP_ID = PAAF.PEOPLE_GROUP_ID)”Contract Type”,
(select segment2 from pay_people_groups where people_group_id = paaf.people_group_id)”Home not Home”,
(select MEANING from HR_LOOKUPS where LOOKUP_CODE =PAAF.EMPLOYMENT_CATEGORY and LOOKUP_TYPE =’EMP_CAT’) “Emp Assignment Category”,
(select meaning from hr_lookups where lookup_code =paaf.employment_category and lookup_type =’CWK_ASG_CATEGORY’) “CWK Assignment Category”,
PAST.USER_STATUS “Assignment Status” ,
(select FULL_NAME from PER_ALL_PEOPLE_F where PERSON_ID = PAAF.SUPERVISOR_ID and sysdate between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE) “Supervisor”,
(select attribute1 from per_all_people_F where person_id = paaf.supervisor_id and sysdate between papf.effective_start_date and papf.effective_end_date) “Supervisor euhReka Id”,
sob.name “Ledger”,
PAaf.VENDOR_ID ,
pov.vendor_name ,
PAaf.VENDOR_SITE_ID ,
povs.vendor_site_code ,
PAaf.PO_HEADER_ID ,
poh.segment1 po_number ,
paaf.po_line_id ,
pol.line_num po_line_number ,
paaf.normal_hours “Normal Hours”,
hr_general.decode_lookup(‘FREQUENCY’, paaf.frequency) frequency_meaning ,
gcc.segment1 “Company”,
gcc.segment2 “Account”,
gcc.segment3 “Project”,
gcc.segment4 “InterCompnay”,
gcc.segment5 “Future”
from
per_people_f papf,
PER_ASSIGNMENTS_F PAAF,
–per_person_types ppt,
–per_person_type_usages pptu
hr_all_organization_units hou,
per_jobs pj,
per_grades pg,
pay_all_payrolls_f ppf,
hr_locations hl,
per_assignment_status_types_v past,
gl_sets_of_books sob,
po_vendors pov ,
PO_VENDOR_SITES_ALL POVS ,
po_headers_all poh ,
PO_LINES_ALL POL,
GL_CODE_COMBINATIONS GCC
–XXHR_EUREH_GEOS_ACTION XA,
–XXHR_EUREH_GEOS_PERSONAL xp
where 1=1
and papf.person_id = paaf.person_id
and sysdate between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE
and paaf.object_version_number = (select max(object_version_number)from per_all_assignments_f where person_id = papf.person_id)
–and XXHR_GET_TERMINATION_DATE(papf.person_id) between paaf.effective_start_date and paaf.effective_end_date
–and PPT.PERSON_TYPE_ID = PAPF.PERSON_TYPE_ID
and hou.organization_id(+) = paaf.organization_id
AND PAAF.JOB_ID = PJ.JOB_ID (+)
–and papf.attribute1 = ‘91409201’
and pg.grade_id(+) = paaf.grade_id
and ppf.payroll_id(+) = paaf.payroll_id
and PAAF.LOCATION_ID = HL.LOCATION_ID(+)
and paaf.assignment_status_type_id = past.assignment_status_type_id(+)
and paaf.set_of_books_id = sob.set_of_books_id (+)
and paaf.vendor_id = pov.vendor_id (+)
and paaf.vendor_site_id = povs.vendor_site_id (+)
and paaf.po_header_id = poh.po_header_id (+)
AND PAAF.PO_LINE_ID = POL.PO_LINE_ID (+)
and GCC.CODE_COMBINATION_ID(+) = PAAF.DEFAULT_CODE_COMB_ID

Employee- Assignment query