SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ PER.PERSON_ID ID , PER.FULL_NAME VALUE , PER.EFFECTIVE_START_DATE DATE_FROM , PER.EFFECTIVE_END_DATE DATE_TO /*PARENT IDS*/ , PER.BUSINESS_GROUP_ID BUSINESS_GROUP_ID /*OTHER IDS*/ , PER.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG , PER.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG , PER.CURRENT_EMP_OR_APL_FLAG CURRENT_EMP_OR_APL_FLAG , PER.PERSON_TYPE_ID PERSON_TYPE_ID , PER.COMMENT_ID COMMENT_ID FROM PER_PEOPLE_X PER WHERE /*JOINS*/ /*DATE*/ /*RESTRICTIONS*/ CURRENT_EMPLOYEE_FLAG = 'Y' AND EXISTS /*CHECK THAT THE PERSON IS: NOT A TOP LEVEL SUPERVISOR A SUPERVISOR*/ ( SELECT -1 FROM HRI_SUPV_HRCHY_SUMMARY SHS , PER_ALL_ASSIGNMENTS_F ASG WHERE PER.PERSON_ID = SHS.SUB_PERSON_ID AND PER.PERSON_ID = ASG.SUPERVISOR_ID ) /*SECURITY*/ AND PER.BUSINESS_GROUP_ID = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID , PER.BUSINESS_GROUP_ID) WITH READ ONLY