View: PER_PEOPLE_ASSIGNMENTS_V1

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

View Text

SELECT P.PERSON_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.ORDER_NAME
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, A.ASSIGNMENT_NUMBER
, A.ASSIGNMENT_TYPE
, A.ORGANIZATION_ID
, A.PAYROLL_ID
, A.PRIMARY_FLAG
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, HR_GENERAL.DECODE_GRADE(A.GRADE_ID)
, HR_GENERAL.DECODE_JOB(A.JOB_ID)
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID)
, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID)
, HR_GENERAL.DECODE_PAYROLL(A.PAYROLL_ID) /*
, PTTL.USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(S.EFFECTIVE_DATE
, P.PERSON_ID)
, A.MANAGER_FLAG
, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID)
, HR_GENERAL.DECODE_PERSON_NAME(A.SUPERVISOR_ID) FULL_NAME
, HR_GENERAL.GET_USER_STATUS(A.ASSIGNMENT_STATUS_TYPE_ID)
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.ASSIGNMENT_STATUS_TYPE_ID
, PG.SEGMENT1
, PG.SEGMENT2
, PG.SEGMENT3
, PG.SEGMENT4
, PG.SEGMENT5
, PG.SEGMENT6
, PG.SEGMENT7
, PG.SEGMENT8
, PG.SEGMENT9
, PG.SEGMENT10
, PG.SEGMENT11
, PG.SEGMENT12
, PG.SEGMENT13
, PG.SEGMENT14
, PG.SEGMENT15
, PG.SEGMENT16
, PG.SEGMENT17
, PG.SEGMENT18
, PG.SEGMENT19
, PG.SEGMENT20
, PG.SEGMENT21
, PG.SEGMENT22
, PG.SEGMENT23
, PG.SEGMENT24
, PG.SEGMENT25
, PG.SEGMENT26
, PG.SEGMENT27
, PG.SEGMENT28
, PG.SEGMENT29
, PG.SEGMENT30
, A.GRADE_ID
, A.JOB_ID
, A.POSITION_ID
, A.ASSIGNMENT_SEQUENCE
, P.NPW_NUMBER
, P.CURRENT_NPW_FLAG
FROM PAY_PEOPLE_GROUPS PG
, PER_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES_TL PTTL
, FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID')
AND P.PERSON_ID = A.PERSON_ID
AND S.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID (+)
AND P.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID (+)
AND PTTL.LANGUAGE(+) = USERENV('LANG') UNION ALL SELECT P.PERSON_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.ORDER_NAME
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, A.ASSIGNMENT_NUMBER
, A.ASSIGNMENT_TYPE
, A.ORGANIZATION_ID
, A.PAYROLL_ID
, A.PRIMARY_FLAG
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, HR_GENERAL.DECODE_GRADE(A.GRADE_ID)
, HR_GENERAL.DECODE_JOB(A.JOB_ID)
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID)
, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID)
, HR_GENERAL.DECODE_PAYROLL(A.PAYROLL_ID)
, PTTL.USER_PERSON_TYPE
, A.MANAGER_FLAG
, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID)
, HR_GENERAL.DECODE_PERSON_NAME(A.SUPERVISOR_ID)
, HR_GENERAL.GET_USER_STATUS(A.ASSIGNMENT_STATUS_TYPE_ID)
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.ASSIGNMENT_STATUS_TYPE_ID
, PG.SEGMENT1
, PG.SEGMENT2
, PG.SEGMENT3
, PG.SEGMENT4
, PG.SEGMENT5
, PG.SEGMENT6
, PG.SEGMENT7
, PG.SEGMENT8
, PG.SEGMENT9
, PG.SEGMENT10
, PG.SEGMENT11
, PG.SEGMENT12
, PG.SEGMENT13
, PG.SEGMENT14
, PG.SEGMENT15
, PG.SEGMENT16
, PG.SEGMENT17
, PG.SEGMENT18
, PG.SEGMENT19
, PG.SEGMENT20
, PG.SEGMENT21
, PG.SEGMENT22
, PG.SEGMENT23
, PG.SEGMENT24
, PG.SEGMENT25
, PG.SEGMENT26
, PG.SEGMENT27
, PG.SEGMENT28
, PG.SEGMENT29
, PG.SEGMENT30
, A.GRADE_ID
, A.JOB_ID
, A.POSITION_ID
, A.ASSIGNMENT_SEQUENCE
, P.NPW_NUMBER
, P.CURRENT_NPW_FLAG
FROM PAY_PEOPLE_GROUPS PG
, PER_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES_TL PTTL
, FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID')
AND P.PERSON_ID = A.PERSON_ID
AND A.EFFECTIVE_END_DATE < S.EFFECTIVE_DATE
AND A.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F A2
WHERE A.ASSIGNMENT_ID = A2.ASSIGNMENT_ID )
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID (+)
AND P.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID (+)
AND PTTL.LANGUAGE (+)= USERENV('LANG')

Columns