SELECT A.POSITION_ID , A.ASSIGNMENT_TYPE , PP.PERSON_ID , PP.FULL_NAME , PP.APPLICANT_NUMBER , PP.EMPLOYEE_NUMBER , PP.DATE_OF_BIRTH , A.ASSIGNMENT_ID , A.EFFECTIVE_START_DATE , PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID , A.POSITION_ID , A.EFFECTIVE_START_DATE , A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE , PT.SYSTEM_PERSON_TYPE , PP.BUSINESS_GROUP_ID FROM PER_ALL_PEOPLE_F PP , PER_ALL_ASSIGNMENTS_F A , PER_PERSON_TYPES PT WHERE A.PERSON_ID = PP.PERSON_ID AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND A.ASSIGNMENT_TYPE = 'E' AND A.POSITION_ID IS NOT NULL AND NOT EXISTS (SELECT NULL FROM PER_ALL_ASSIGNMENTS_F X WHERE X.ASSIGNMENT_ID = A.ASSIGNMENT_ID AND X.ASSIGNMENT_TYPE = 'E' AND X.POSITION_ID = A.POSITION_ID AND X.EFFECTIVE_END_DATE = A.EFFECTIVE_START_DATE - 1 AND X.EFFECTIVE_START_DATE < A.EFFECTIVE_START_DATE) UNION SELECT A.POSITION_ID , A.ASSIGNMENT_TYPE , PP.PERSON_ID , PP.FULL_NAME , PP.APPLICANT_NUMBER , PP.EMPLOYEE_NUMBER , PP.DATE_OF_BIRTH , A.ASSIGNMENT_ID , PER_GET_ASG_POS.START_DATE (A.ASSIGNMENT_ID , A.POSITION_ID , A.EFFECTIVE_START_DATE , A.EFFECTIVE_END_DATE) EFFECTIVE_START_DATE , PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID , A.POSITION_ID , A.EFFECTIVE_START_DATE , A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE , PT.SYSTEM_PERSON_TYPE , PP.BUSINESS_GROUP_ID FROM PER_ALL_PEOPLE_F PP , PER_ALL_ASSIGNMENTS_F A , PER_PERSON_TYPES PT WHERE A.PERSON_ID = PP.PERSON_ID AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID AND A.ASSIGNMENT_TYPE = 'A' AND A.POSITION_ID IS NOT NULL AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE