View: HRFV_POSITION_HISTORY

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_POSITION_HISTORY

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, POS.NAME POSITION_NAME
, ASS.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, HR_DISCOVERER.CHECK_END_DATE(ASF.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASS.PERSON_ID PERSON_ID
, ASS.POSITION_ID POSITION_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_POSITIONS POS
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_F ASF
, PER_ASSIGNMENTS_F ASS
WHERE POS.POSITION_ID = ASS.POSITION_ID
AND ASS.PERSON_ID = PEO.PERSON_ID
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.ASSIGNMENT_TYPE = 'E'
AND NOT EXISTS ( SELECT NULL
FROM PER_ASSIGNMENTS_F ASS1
WHERE ASS1.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND (ASS1.POSITION_ID = ASS.POSITION_ID OR (ASS1.POSITION_ID IS NULL
AND ASS.POSITION_ID IS NULL))
AND ASS1.EFFECTIVE_START_DATE = ( SELECT MAX(ASS2.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F ASS2
WHERE ASS2.ASSIGNMENT_ID = ASS1.ASSIGNMENT_ID
AND ASS2.EFFECTIVE_START_DATE < ASS.EFFECTIVE_START_DATE )
AND ASS1.ASSIGNMENT_TYPE = 'E' )
AND ASS.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
AND ASF.EFFECTIVE_END_DATE = ( SELECT MAX(ASSF.EFFECTIVE_END_DATE)
FROM PER_ASSIGNMENTS_F ASS3
, PER_ASSIGNMENTS_F ASSF
WHERE ASS3.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS3.ASSIGNMENT_ID = ASSF.ASSIGNMENT_ID
AND (ASS3.POSITION_ID = ASS.POSITION_ID OR (ASS3.POSITION_ID IS NULL
AND ASS.POSITION_ID IS NULL))
AND ASSF.ASSIGNMENT_TYPE = 'E'
AND ASS3.ASSIGNMENT_TYPE = 'E'
AND ( ( ASSF.EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE = HR_GENERAL.END_OF_TIME
AND ASSF.EFFECTIVE_END_DATE = ASS.EFFECTIVE_END_DATE ) OR ( ASS3.EFFECTIVE_START_DATE >= ASS.EFFECTIVE_START_DATE
AND ( ASSF.EFFECTIVE_END_DATE = (SELECT MIN(ASS4.EFFECTIVE_START_DATE)-1
FROM PER_ASSIGNMENTS_F ASS4
WHERE ASS4.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS4.EFFECTIVE_START_DATE >= ASS3.EFFECTIVE_START_DATE
AND ASS4.ASSIGNMENT_TYPE = 'E'
AND ( NVL(ASS4.POSITION_ID
, -1) <> NVL(ASS.POSITION_ID
, -1) ) ) ) ) OR ( ASS3.EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE
AND NOT EXISTS ( SELECT NULL
FROM PER_ASSIGNMENTS_F ASS7
WHERE ASS7.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS7.EFFECTIVE_START_DATE > ASS3.EFFECTIVE_START_DATE
AND ASS7.ASSIGNMENT_TYPE = 'E'
AND ( NVL(ASS7.POSITION_ID
, -1) <> NVL(ASS.POSITION_ID
, -1) ) )
AND ( ASSF.EFFECTIVE_END_DATE = HR_GENERAL.END_OF_TIME ) ) ) )
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY

Columns