Query for employee, element , entry value and input entry value

SELECT distinct
PETF.ELEMENT_NAME “Element Entry Name” ,
pivf.name “Input value”,
PAPF.FULL_NAME “Employee Name”,
PEEVF.EFFECTIVE_START_DATE “Entry Start Date” ,
PEEVF.EFFECTIVE_END_DATE “Entry End Date” ,
PEEVF.SCREEN_ENTRY_VALUE “Entry Values”
FROM
PAY_ELEMENT_TYPES_F PETF ,
PAY_ELEMENT_LINKS_F PELF ,
PAY_ELEMENT_ENTRIES_F PEEF ,
PER_ALL_ASSIGNMENTS_F PAAF ,
PER_ALL_PEOPLE_F PAPF ,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF ,
PAY_INPUT_VALUES_F PIVF
WHERE 1 = 1
AND PELF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PEEF.EFFECTIVE_START_DATE) AND TRUNC(PEEF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PIVF.EFFECTIVE_START_DATE) AND TRUNC(PIVF.EFFECTIVE_END_DATE)
AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.CURRENT_EMP_OR_APL_FLAG = ‘Y’
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID
AND paaf.primary_flag = ‘Y’
AND paaf.assignment_type = ‘E’
AND TRUNC(SYSDATE) BETWEEN TRUNC(PEEVF.EFFECTIVE_START_DATE) AND TRUNC(PEEVF.EFFECTIVE_END_DATE)
AND PETF.ELEMENT_NAME = ‘ESPP’
and papf.business_group_id = 96;

Advertisements

Business group query

SELECT o.organization_id,
       o.organization_id,
       otl.NAME,
       o.date_from,
       o.date_to,
       o.internal_address_line,
       o.location_id,
       o.comments
  FROM hr_all_organization_units    o,
       hr_all_organization_units_tl otl,
       hr_organization_information  o2,
       hr_organization_information  o3,
       hr_organization_information  o4
 WHERE o.organization_id = otl.organization_id
   AND o.organization_id = o2.organization_id(+)
   AND o.organization_id = o3.organization_id
   AND o.organization_id = o4.organization_id
   AND o3.org_information_context = 'Business Group Information'
   AND o2.org_information_context(+) = 'Work Day Information'
   AND o4.org_information_context = 'CLASS'
   AND o4.org_information1 = 'HR_BG'
   AND o4.org_information2 = 'Y';

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;