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
             
            )
            
            
              
        
        
        
        

:Party_ID - The party Id of a User Resource