View: HRFV_GRADE_HISTORY

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, GRD.NAME GRADE_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.GRADE_ID GRADE_ID
, ASS.PERSON_ID PERSON_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES GRD
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_F ASF
, PER_ASSIGNMENTS_F ASS
WHERE GRD.GRADE_ID = ASS.GRADE_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 NVL(ASS1.GRADE_ID
, 9.9) = NVL(ASS.GRADE_ID
, 9.9)
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 NVL(ASS3.GRADE_ID
, 9.9) = NVL(ASS.GRADE_ID
, 9.9)
AND ASSF.ASSIGNMENT_TYPE = 'E'
AND ASS3.ASSIGNMENT_TYPE = 'E'
AND ( ( ASSF.EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE = TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
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.GRADE_ID
, 9.9) <> NVL(ASS.GRADE_ID
, 9.9) ) ) ) ) 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.GRADE_ID
, 9.9) <> NVL(ASS.GRADE_ID
, 9.9) ) )
AND ( ASSF.EFFECTIVE_END_DATE = TO_DATE('31-12-4712'
, 'DD-MM-YYYY') ) ) ) )
AND ASS.BUSINESS_GROUP_ID= NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY

Columns