1. Tables and Views for Project Management
  2. GMS_HCM_PERSONNEL_DATES_V

GMS_HCM_PERSONNEL_DATES_V

Details

  • Schema: FUSION

  • Object owner: GMS

  • Object type: VIEW

Columns

Name

PERSON_ID

PERSON_NUMBER

PERS_START_DATE

PERS_END_DATE

ASSIGNMENT_START_DATE

ASSIGNMENT_END_DATE

Query

SQL_Statement

SELECT DISTINCT PersonPEO.PERSON_ID AS PERSON_ID,

AllPeople.PERSON_NUMBER AS PERSON_NUMBER,

AllPeople.EFFECTIVE_START_DATE AS PERS_START_DATE,

AllPeople.EFFECTIVE_END_DATE AS PERS_END_DATE,

Assignments.EFFECTIVE_START_DATE AS ASSIGNMENT_START_DATE,

Assignments.EFFECTIVE_END_DATE AS ASSIGNMENT_END_DATE

FROM PER_PERSONS PersonPEO,

PER_ALL_PEOPLE_F AllPeople,

PER_ALL_ASSIGNMENTS_M Assignments

WHERE (PersonPEO.PERSON_ID= AllPeople.PERSON_ID

AND TRUNC(TO_DATE(GREATEST(trunc(SYSDATE), PersonPEO.START_DATE))) BETWEEN AllPeople.EFFECTIVE_START_DATE AND AllPeople.EFFECTIVE_END_DATE)

AND (AllPeople.PERSON_ID = Assignments.PERSON_ID (+)

AND TRUNC(TO_DATE(GREATEST(trunc(SYSDATE), AllPeople.START_DATE))) BETWEEN Assignments.EFFECTIVE_START_DATE(+) AND Assignments.EFFECTIVE_END_DATE(+))

and Assignments.ASSIGNMENT_TYPE in ('E','C') and Assignments.PRIMARY_FLAG='Y'