Overlapping Work Relationship (Multiple Work Relationship With Same LE In Given Date Range) (Doc ID 2178025.1)

Last updated on AUGUST 31, 2016

Applies to:

Oracle Fusion Global Human Resources Cloud Service - Version 11.1.10.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.10.0.0 version, Global Human Resources

Issue :

------------------------------------

Few employe are overlapping work relationship


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. It is a data issue.
2. Found from the SQL GHR_EMPL_DATA_QUERY
3. SELECT
  pos.legal_entity_id pos_legal_entity_id
  , pos.period_of_service_id pos_period_of_service_id
  , pos.date_start pos_date_start
  , pos.actual_termination_date pos_actual_termination_date
  , pos.period_type pos_period_type
  , pos.person_id pos_person_id
  , pos.last_update_date pos_last_update_date
  , PERSON_NUMBER
FROM per_periods_of_service pos
  , per_periods_of_service pos1
  , per_all_people_f paaf
WHERE 1=1
 AND paaf.person_id = pos.person_id
 AND pos1.person_id = pos.person_id
 AND pos1.legal_entity_id = pos.legal_entity_id
 AND pos1.period_type = pos.period_type
 AND pos1.period_of_service_id <> pos.period_of_service_id
 AND ((pos1.date_start <= pos.date_start
 AND NVL(pos1.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) >= pos.date_start)
 OR (pos1.date_start BETWEEN pos.date_start AND NVL(pos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))))
 AND pos1.date_start BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms