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_ORGANIZATION_HISTORY

Source

View Text - Preformatted


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

View Text - HTML Formatted

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