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.

Deleting Data Definition and Template from back end for BI Publisher report

To delete Data definition and template from Back end for a BI Publisher report the following code must be used.

Script for deleting the datadefinition
————————————————>
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW(‘XX’,’XXHR_EUREH_GEOS_ERROR_REPORT’);
END;
——————————————————–
——————————————————–
script for deleting the template
——————————————————>
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW(‘XX’,’XXHR_EUREH_GEOS_ERROR_REPORT’);
END;

Need to commit after running the script

OR

run the following

declare
begin
delete from XDO_TEMPLATES_B where template_code = ‘template code’;
delete from XDO_TEMPLATES_TL where template_code = ‘template code’;
delete from xdo_lobs where lob_code = ‘template code’;
delete from XDO_DS_DEFINITIONS_TL where data_source_code = ‘data definition code’;
delete from XDO_DS_DEFINITIONS_b where data_source_code = ‘data definition code’;
end;

 

 

 

 

 

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

Organization query

select distinct HAOU.name,
haou.date_from “From_date”,
HV.ORG_INFORMATION1_MEANING “classification”,
HAOU.INTERNAL_EXTERNAL_FLAG “Inetrnal_external”,
(select meaning from hr_lookups where lookup_type =’ORG_TYPE’ and lookup_code= haou.type)”Type”,
(select hl.description from hr_locations hl where hl.location_id = haou.location_id) “location”
from HR_ORGANIZATION_INFORMATION HOI,
HR_ALL_ORGANIZATION_UNITS HAOU,
HR_ORG_INFORMATION_TYPES HOIT,
HR_ORGANIZATION_INFORMATION_V hv
WHERE HOI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
and HOIT.ORG_INFORMATION_TYPE = HOI.ORG_INFORMATION_CONTEXT
and HV.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
and HV.ORG_INFORMATION1_MEANING is not null
ORDER BY HAOU.name;