View: HRFV_ORGANIZATION_HISTORY

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

View Text

SELECT BGR.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, ORGT.NAME ORGANIZATION_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.ORGANIZATION_ID ORGANIZATION_ID
, ASS.PERSON_ID PERSON_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_BUSINESS_GROUPS BGR
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_F ASF
, PER_ASSIGNMENTS_F ASS
WHERE ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASS.PERSON_ID = PEO.PERSON_ID
AND ASS.BUSINESS_GROUP_ID = BGR.BUSINESS_GROUP_ID
AND ASS.ASSIGNMENT_TYPE = 'E'
AND NOT EXISTS ( SELECT NULL
FROM PER_ASSIGNMENTS_F ASS1
WHERE ASS1.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS1.ORGANIZATION_ID = ASS.ORGANIZATION_ID
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.ORGANIZATION_ID = ASS.ORGANIZATION_ID
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 ASS4.ORGANIZATION_ID <> ASS.ORGANIZATION_ID) ) OR (ASS3.EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE
AND ASSF.EFFECTIVE_END_DATE = HR_GENERAL.END_OF_TIME
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 ASS7.ORGANIZATION_ID <> ASS.ORGANIZATION_ID) ) ) )
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY

Columns