FND Design Data [Home] [Help]

View: PER_ASSIGNMENTS_V9

Product: PER - Human Resources
Description: Used to support user interface
Implementation/DBA Data: ViewAPPS.PER_ASSIGNMENTS_V9
View Text

SELECT A.POSITION_ID
, A.ASSIGNMENT_TYPE
, PP.PERSON_ID
, PP.FULL_NAME
, PP.APPLICANT_NUMBER
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PT.SYSTEM_PERSON_TYPE
, PP.BUSINESS_GROUP_ID
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
WHERE A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND A.ASSIGNMENT_TYPE = 'E'
AND A.POSITION_ID IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM PER_ALL_ASSIGNMENTS_F X
WHERE X.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND X.ASSIGNMENT_TYPE = 'E'
AND X.POSITION_ID = A.POSITION_ID
AND X.EFFECTIVE_END_DATE = A.EFFECTIVE_START_DATE - 1
AND X.EFFECTIVE_START_DATE < A.EFFECTIVE_START_DATE) UNION SELECT A.POSITION_ID
, A.ASSIGNMENT_TYPE
, PP.PERSON_ID
, PP.FULL_NAME
, PP.APPLICANT_NUMBER
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, A.ASSIGNMENT_ID
, PER_GET_ASG_POS.START_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) EFFECTIVE_START_DATE
, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PT.SYSTEM_PERSON_TYPE
, PP.BUSINESS_GROUP_ID
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
WHERE A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID
AND A.ASSIGNMENT_TYPE = 'A'
AND A.POSITION_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE

Columns

Name
POSITION_ID
ASSIGNMENT_TYPE
PERSON_ID
FULL_NAME
APPLICANT_NUMBER
EMPLOYEE_NUMBER
DATE_OF_BIRTH
ASSIGNMENT_ID
START_DATE
END_DATE
SYSTEM_PERSON_TYPE
BUSINESS_GROUP_ID