eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRFV_GRADE_HISTORY

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,peo.full_name person_name ,gdt.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_tl gdt ,per_people_x peo ,per_assignments_f asf ,per_assignments_f ass WHERE gdt.grade_id = ass.grade_id AND gdt.language = userenv('LANG') 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

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, GDT.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_TL GDT
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_F ASF
, PER_ASSIGNMENTS_F ASS
WHERE GDT.GRADE_ID = ASS.GRADE_ID
AND GDT.LANGUAGE = USERENV('LANG')
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