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_POSITION_HISTORY

Source

View Text - Preformatted


SELECT /* $Header: perpohis.odf 120.0 2005/05/30 20:55:55 appldev noship $ */ /* unique attributes */ bgrT.name business_group_name ,peo.full_name person_name ,pos.name position_name /* regular attributes */ ,ass.effective_start_date effective_start_date ,hr_discoverer.check_end_date(asf.effective_end_date) effective_end_date ,ass.assignment_number assignment_number /* foreign key resolutions */ ,peo.employee_number employee_number /* ids */ ,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' /* check for end of time dates */ 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 ) /* check for rows which have changes after */ 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 >= ass.effective_start_date /*Bug 3832076*/ AND ass4.assignment_type = 'E' AND ( NVL(ass4.position_id, -1) <> NVL(ass.position_id, -1) ) ) ) ) /* check for rows which do not have changes after */ 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

View Text - HTML Formatted

SELECT /* $HEADER: PERPOHIS.ODF 120.0 2005/05/30 20:55:55 APPLDEV NOSHIP $ */ /* UNIQUE ATTRIBUTES */ BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, POS.NAME POSITION_NAME /* REGULAR ATTRIBUTES */
, ASS.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, HR_DISCOVERER.CHECK_END_DATE(ASF.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER /* FOREIGN KEY RESOLUTIONS */
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER /* IDS */
, 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' /* CHECK FOR END OF TIME DATES */
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 ) /* CHECK FOR ROWS WHICH HAVE CHANGES AFTER */ 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 >= ASS.EFFECTIVE_START_DATE /*BUG 3832076*/
AND ASS4.ASSIGNMENT_TYPE = 'E'
AND ( NVL(ASS4.POSITION_ID
, -1) <> NVL(ASS.POSITION_ID
, -1) ) ) ) ) /* CHECK FOR ROWS WHICH DO NOT HAVE CHANGES AFTER */ 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