BI Publisher – Get all resources from a The same Business Unit as a specific Resource User

SELECT 
DISTINCT JTF_RS_RESOURCE_PROFILES.PARTY_ID
 FROM 
(
      Select
         PersonNameDPEO.PERSON_NAME_ID,
         PersonNameDPEO.PERSON_ID,
         PersonNameDPEO.DISPLAY_NAME as PERSON_DISPLAY_NAME,
         PersonDPEO.PERSON_NUMBER,
         PersonDPEO.PERSON_ID as PERSON_ID2,
         PersonDPEO.EFFECTIVE_START_DATE as PERSON_ESDATE,
         PersonDPEO.EFFECTIVE_END_DATE as PERSON_EEDATE,
         JobDPEO.JOB_ID,
         JobDPEO.NAME as JOB_NAME,
         BusinessUnitPEO.BU_ID,
         BusinessUnitPEO.BU_NAME,
         AssignmentSupervisorDPEO.ASSIGNMENT_ID,
         AssignmentSupervisorDPEO.ASSIGNMENT_SUPERVISOR_ID,
         AssignmentSupervisorDPEO.MANAGER_ID,
         AssignmentSupervisorDPEO.EFFECTIVE_START_DATE as ASSIGNMENT_DPEO_ESDATE,
         AssignmentSupervisorDPEO.EFFECTIVE_END_DATE as ASSIGNMENT_DPEO_EEDATE,
         ManagerPersonNameDPEO.DISPLAY_NAME as MANAGER_DISPLAY_NAME,
         PersonNameDPEO.EFFECTIVE_START_DATE as PERSON_NAME_DPEO_ESDATE,
         PersonNameDPEO.EFFECTIVE_END_DATE as PERSON_NAME_DPEO_EEDATE,
         PersonNameDPEO.OBJECT_VERSION_NUMBER as PERSON_NAME_DPEO_OVN,
         JobDPEO.EFFECTIVE_START_DATE as JOB_DPEO_ESDATE,
         JobDPEO.EFFECTIVE_END_DATE as JOB_DPEO_EEDATE,
         JobDPEO.OBJECT_VERSION_NUMBER as JOB_DPEO_OVN,
         DepartmentDPEO.NAME as DEPT_NAME,
         DepartmentDPEO.ORGANIZATION_ID as DEPT_ID,
         DepartmentDPEO.EFFECTIVE_START_DATE as DEPT_ESDATE,
         DepartmentDPEO.EFFECTIVE_END_DATE as DEPT_EEDATE 
      FROM
         PER_PERSON_NAMES_F_V PersonNameDPEO 
         LEFT JOIN
            PER_ALL_PEOPLE_F PersonDPEO 
            ON (PersonDPEO.Person_Id = PersonNameDPEO.Person_Id 
            AND TRUNC(SYSDATE) Between PersonDPEO.Effective_Start_Date AND PersonDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_ALL_ASSIGNMENTS_M AssignmentDPEO 
            ON (AssignmentDPEO.Person_Id = PersonNameDPEO.Person_Id 
            AND AssignmentDPEO.Primary_Flag = 'Y' 
            AND AssignmentDPEO.Assignment_Status_Type = 'ACTIVE' 
            AND AssignmentDPEO.Effective_Latest_Change = 'Y' 
            AND TRUNC(Sysdate) BETWEEN AssignmentDPEO.Effective_Start_Date AND AssignmentDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_JOBS_F_VL JobDPEO 
            ON (JobDPEO.Job_Id = AssignmentDPEO.Job_Id 
            And TRUNC(SYSDATE) Between JobDPEO.Effective_Start_Date AND JobDPEO.Effective_End_Date) 
         LEFT JOIN
            FUN_ALL_BUSINESS_UNITS_V BusinessUnitPEO 
            ON BusinessUnitPEO.Bu_Id = AssignmentDPEO.Business_Unit_Id 
         LEFT JOIN
            PER_ASSIGNMENT_SUPERVISORS_F AssignmentSupervisorDPEO 
            ON (AssignmentSupervisorDPEO.Assignment_Id = AssignmentDPEO.Assignment_Id 
            AND AssignmentSupervisorDPEO.Primary_Flag = 'Y' 
            AND TRUNC(SYSDATE) BETWEEN AssignmentSupervisorDPEO.Effective_Start_Date AND AssignmentSupervisorDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_PERSON_NAMES_F_V ManagerPersonNameDPEO 
            ON (ManagerPersonNameDPEO.Person_Id = AssignmentSupervisorDPEO.Manager_Id 
            AND TRUNC(SYSDATE) BETWEEN ManagerPersonNameDPEO.Effective_Start_Date AND ManagerPersonNameDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_DEPARTMENTS DepartmentDPEO 
            ON (AssignmentDPEO.ORGANIZATION_ID = DepartmentDPEO.ORGANIZATION_ID 
            AND TRUNC(SYSDATE) BETWEEN DepartmentDPEO.Effective_Start_Date AND DepartmentDPEO.Effective_End_Date) 
      Where
         TRUNC(SYSDATE) BETWEEN PersonNameDPEO.Effective_Start_Date AND PersonNameDPEO.Effective_End_Date
) USER_INFO INNER JOIN PER_USERS ON (USER_INFO.PERSON_ID2 = PER_USERS.PERSON_ID) INNER JOIN HZ_PARTIES ON (PER_USERS.USER_GUID = HZ_PARTIES.USER_GUID) INNER JOIN JTF_RS_RESOURCE_PROFILES ON (JTF_RS_RESOURCE_PROFILES.PARTY_ID = HZ_PARTIES.PARTY_ID)
WHERE JTF_RS_RESOURCE_PROFILES.PARTY_ID <> :Party_ID AND USER_INFO.BU_ID IN (
 
SELECT 
USER_INFO.BU_ID
 FROM 
(
      Select
         PersonNameDPEO.PERSON_NAME_ID,
         PersonNameDPEO.PERSON_ID,
         PersonNameDPEO.DISPLAY_NAME as PERSON_DISPLAY_NAME,
         PersonDPEO.PERSON_NUMBER,
         PersonDPEO.PERSON_ID as PERSON_ID2,
         PersonDPEO.EFFECTIVE_START_DATE as PERSON_ESDATE,
         PersonDPEO.EFFECTIVE_END_DATE as PERSON_EEDATE,
         JobDPEO.JOB_ID,
         JobDPEO.NAME as JOB_NAME,
         BusinessUnitPEO.BU_ID,
         BusinessUnitPEO.BU_NAME,
         AssignmentSupervisorDPEO.ASSIGNMENT_ID,
         AssignmentSupervisorDPEO.ASSIGNMENT_SUPERVISOR_ID,
         AssignmentSupervisorDPEO.MANAGER_ID,
         AssignmentSupervisorDPEO.EFFECTIVE_START_DATE as ASSIGNMENT_DPEO_ESDATE,
         AssignmentSupervisorDPEO.EFFECTIVE_END_DATE as ASSIGNMENT_DPEO_EEDATE,
         ManagerPersonNameDPEO.DISPLAY_NAME as MANAGER_DISPLAY_NAME,
         PersonNameDPEO.EFFECTIVE_START_DATE as PERSON_NAME_DPEO_ESDATE,
         PersonNameDPEO.EFFECTIVE_END_DATE as PERSON_NAME_DPEO_EEDATE,
         PersonNameDPEO.OBJECT_VERSION_NUMBER as PERSON_NAME_DPEO_OVN,
         JobDPEO.EFFECTIVE_START_DATE as JOB_DPEO_ESDATE,
         JobDPEO.EFFECTIVE_END_DATE as JOB_DPEO_EEDATE,
         JobDPEO.OBJECT_VERSION_NUMBER as JOB_DPEO_OVN,
         DepartmentDPEO.NAME as DEPT_NAME,
         DepartmentDPEO.ORGANIZATION_ID as DEPT_ID,
         DepartmentDPEO.EFFECTIVE_START_DATE as DEPT_ESDATE,
         DepartmentDPEO.EFFECTIVE_END_DATE as DEPT_EEDATE 
      FROM
         PER_PERSON_NAMES_F_V PersonNameDPEO 
         LEFT JOIN
            PER_ALL_PEOPLE_F PersonDPEO 
            ON (PersonDPEO.Person_Id = PersonNameDPEO.Person_Id 
            AND TRUNC(SYSDATE) Between PersonDPEO.Effective_Start_Date AND PersonDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_ALL_ASSIGNMENTS_M AssignmentDPEO 
            ON (AssignmentDPEO.Person_Id = PersonNameDPEO.Person_Id 
            AND AssignmentDPEO.Primary_Flag = 'Y' 
            AND AssignmentDPEO.Assignment_Status_Type = 'ACTIVE' 
            AND AssignmentDPEO.Effective_Latest_Change = 'Y' 
            AND TRUNC(Sysdate) BETWEEN AssignmentDPEO.Effective_Start_Date AND AssignmentDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_JOBS_F_VL JobDPEO 
            ON (JobDPEO.Job_Id = AssignmentDPEO.Job_Id 
            And TRUNC(SYSDATE) Between JobDPEO.Effective_Start_Date AND JobDPEO.Effective_End_Date) 
         LEFT JOIN
            FUN_ALL_BUSINESS_UNITS_V BusinessUnitPEO 
            ON BusinessUnitPEO.Bu_Id = AssignmentDPEO.Business_Unit_Id 
         LEFT JOIN
            PER_ASSIGNMENT_SUPERVISORS_F AssignmentSupervisorDPEO 
            ON (AssignmentSupervisorDPEO.Assignment_Id = AssignmentDPEO.Assignment_Id 
            AND AssignmentSupervisorDPEO.Primary_Flag = 'Y' 
            AND TRUNC(SYSDATE) BETWEEN AssignmentSupervisorDPEO.Effective_Start_Date AND AssignmentSupervisorDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_PERSON_NAMES_F_V ManagerPersonNameDPEO 
            ON (ManagerPersonNameDPEO.Person_Id = AssignmentSupervisorDPEO.Manager_Id 
            AND TRUNC(SYSDATE) BETWEEN ManagerPersonNameDPEO.Effective_Start_Date AND ManagerPersonNameDPEO.Effective_End_Date) 
         LEFT JOIN
            PER_DEPARTMENTS DepartmentDPEO 
            ON (AssignmentDPEO.ORGANIZATION_ID = DepartmentDPEO.ORGANIZATION_ID 
            AND TRUNC(SYSDATE) BETWEEN DepartmentDPEO.Effective_Start_Date AND DepartmentDPEO.Effective_End_Date) 
      Where
         TRUNC(SYSDATE) BETWEEN PersonNameDPEO.Effective_Start_Date AND PersonNameDPEO.Effective_End_Date
) USER_INFO INNER JOIN PER_USERS ON (USER_INFO.PERSON_ID2 = PER_USERS.PERSON_ID) INNER JOIN HZ_PARTIES ON (PER_USERS.USER_GUID = HZ_PARTIES.USER_GUID)  INNER JOIN JTF_RS_RESOURCE_PROFILES ON (JTF_RS_RESOURCE_PROFILES.PARTY_ID = HZ_PARTIES.PARTY_ID)
WHERE JTF_RS_RESOURCE_PROFILES.PARTY_ID = :Party_ID
 
)