View: PER_ASSIGNMENTS_V11

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_ASSIGNMENTS_V11

View Text

SELECT A.POSITION_ID POSITION_ID
, A.ASSIGNMENT_TYPE ASSIGNMENT_TYPE
, PP.PERSON_ID PERSON_ID
, PP.FULL_NAME FULL_NAME
, PP.APPLICANT_NUMBER APPLICANT_NUMBER
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH DATE_OF_BIRTH
, A.ASSIGNMENT_ID ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE START_DATE
, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) END_DATE
, PT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PP.NPW_NUMBER NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_WORKER_USER_PERSON_TYPE(FS.EFFECTIVE_DATE
, PP.PERSON_ID) USER_PERSON_TYPE
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
, FND_SESSIONS FS
WHERE A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID
AND TRUNC(FS.EFFECTIVE_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND FS.SESSION_ID = USERENV('SESSIONID')
AND A.ASSIGNMENT_TYPE IN ('E'
, 'C')
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 = A.ASSIGNMENT_TYPE
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
, PP.NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(FS.EFFECTIVE_DATE
, PP.PERSON_ID) USER_PERSON_TYPE
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
, FND_SESSIONS FS
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(FS.EFFECTIVE_DATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND TRUNC(FS.EFFECTIVE_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND FS.SESSION_ID = USERENV('SESSIONID')

Columns