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