ORA-20001: Supervisor is not valid for the duration of the assignment

For Updating the Supervisor info we often come across the the following error when we try to update assignment details in CORRECTION mode:

Lets take the example:
Employee Start Date 01-NOV-2010
supervisor Start Date 01-JUL-11
assignment start date 2-Jan-14

As supervisor only started 01-JUL-2011 you cannot update the employee’s assignment recor which starts 01-NOV-2010 with that supervisor, So that means you have to date track to 01-JUL-2011 and save the change to the assignment record in UPDATE mode.
So first period from 01-NOV-2010 to 30-JUN-2011 there will be no supervisor and from 01-JUL-2011 there will be a supervisor.
The system cannot update the assignment starting 01-NOV-2010 in CORRECTION mode as the supervisor does not exist on 01-NOV-2010

Use the following code:

/*
================================================================================
Picking supervisor_id of the employee
================================================================================
*/
if R_ASSIGNMENT.SUPERVISOR_NUMBER is not null
then

BEGIN
select PAPF.PERSON_ID, start_date
into l_num_supervisor_id , l_start_date
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.ATTRIBUTE1 = REPLACE(REPLACE(REPLACE(REPLACE(R_ASSIGNMENT.SUPERVISOR_NUMBER, CHR(10), ”), CHR(13), ”), CHR(09), ”),’ ‘,”)
and sysdate between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE
and business_group_id = fnd_profile.value(‘PER_BUSINESS_GROUP_ID’) ;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_var_error_code := SQLCODE;
L_VAR_FLAG := ‘N’;
l_var_ERROR_MSG := ‘Supervisor id no does not exists’||sqlerrm;

UPDATE XXhr_EMP_ASSIGNMENT_STG
set PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG||’–‘ ||l_var_error_msg,
ERROR_CODE = l_var_ERROR_CODE
where RECORD_NO = l_num_RECORD_NO;

fnd_file.put_line (fnd_file.LOG,
‘Supervisor id does not exists ‘
);
WHEN OTHERS
then
l_var_flag := ‘N’;

UPDATE XXhr_EMP_ASSIGNMENT_STG
set PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG||’–‘ ||’Chk Supervisor id’
where RECORD_NO = l_num_RECORD_NO;

DBMS_OUTPUT.put_line (‘Exception within Supervisor id blk’);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
ELSE
l_num_supervisor_id := null;
end if;

/* if supervisor hire date is after employee hire date then UPDATE else CORRECTION
l_start_date — supervisor start date
L_DAT_START_DATE — emp start date
l_dat_assg_eff_start_date — emp assgn start date
*/
— FND_FILE.PUT_LINE (FND_FILE.LOG,’assignmnet start date l_dte_effective_date Super’|| l_start_date);
— FND_FILE.PUT_LINE (FND_FILE.LOG,’assignmnet start date l_dte_effective_date Emp Ass’|| l_dat_assg_eff_start_date);

IF (l_start_date > l_dat_assg_eff_start_date )
THEN
L_DTE_EFFECTIVE_DATE := L_START_DATE;
L_VAR_DATETRACK_UPDATE_MODE := ‘UPDATE’;

— FND_FILE.PUT_LINE (FND_FILE.LOG,’assignmnet start date l_dte_effective_date UPDATE’|| L_DTE_EFFECTIVE_DATE);

ELSE
L_DTE_EFFECTIVE_DATE:= l_dat_assg_eff_start_date ;–r_assignment.from_date;
L_VAR_DATETRACK_UPDATE_MODE :=’CORRECTION’;
— FND_FILE.PUT_LINE (FND_FILE.LOG,’assignmnet start date l_dte_effective_date CORRECTION’|| L_DTE_EFFECTIVE_DATE);

end if;

/*
================================================================================
Picking OVN of the employee assgnment
================================================================================
*/
BEGIN
SELECT paaf.assignment_id,
PAAF.OBJECT_VERSION_NUMBER,
PAAF.EFFECTIVE_START_DATE
INTO L_NUM_ASSIGNMENT_ID,
L_NUM_OBJECT_VERSION_NUMBER,
l_dat_assg_eff_start_date
FROM per_all_assignments_f paaf
WHERE paaf.person_id =l_num_person_id
AND primary_flag = ‘Y’
AND l_dte_effective_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND business_group_id = fnd_profile.value(‘PER_BUSINESS_GROUP_ID’) ;

EXCEPTION
WHEN OTHERS
THEN
l_var_error_code := SQLCODE;
l_var_flag := ‘N’;
l_var_error_msg := ‘Assignment id or object version no does not exists’;

UPDATE XXhr_EMP_ASSIGNMENT_STG
set PROCESS_FLAG = ‘E’,
ERROR_MSG = ERROR_MSG||’–‘ ||l_var_error_msg,
ERROR_CODE = l_var_ERROR_CODE
where RECORD_NO = l_num_RECORD_NO;

fnd_file.put_line
(FND_FILE.log,
‘Assignment id or object version no does not exists ‘
);

END;

/*
================================================================================
if assigment has been modified on the same day then Correction mode to be used
================================================================================
*/
IF L_DAT_ASSG_EFF_START_DATE = L_DTE_EFFECTIVE_DATE
THEN

FND_FILE.PUT_LINE (FND_FILE.LOG,’IF L_DAT_ASSG_EFF_START_DATE = L_DTE_EFFECTIVE_DATE’|| L_DTE_EFFECTIVE_DATE);
L_VAR_DATETRACK_UPDATE_MODE := ‘CORRECTION’;
end if;