SELECT MEMBER_GROUP.OBJECT_ID GROUP_ID , HR_EMPLOYEE.PARTY_ID MEMBER_PERSON_ID , FND_USER.USER_ID MEMBER_USER_ID , FND_USER.USER_NAME MEMBER_USER_NAME , 'INTERNAL' MEMBER_PERSON_TYPE FROM FND_USER FND_USER , PER_ALL_PEOPLE_F HR_EMPLOYEE , HZ_RELATIONSHIPS MEMBER_GROUP WHERE HR_EMPLOYEE.PERSON_ID = FND_USER.EMPLOYEE_ID AND HR_EMPLOYEE.CURRENT_EMPLOYEE_FLAG = 'Y' AND HR_EMPLOYEE.EFFECTIVE_START_DATE <= SYSDATE AND (HR_EMPLOYEE.EFFECTIVE_END_DATE >= SYSDATE OR HR_EMPLOYEE.EFFECTIVE_END_DATE IS NULL) AND MEMBER_GROUP.SUBJECT_ID = HR_EMPLOYEE.PARTY_ID AND MEMBER_GROUP.SUBJECT_TYPE = 'PERSON' AND MEMBER_GROUP.OBJECT_TYPE = 'GROUP' AND MEMBER_GROUP.RELATIONSHIP_TYPE = 'MEMBERSHIP' AND MEMBER_GROUP.STATUS = 'A' AND MEMBER_GROUP.START_DATE <= SYSDATE AND NVL(MEMBER_GROUP.END_DATE , SYSDATE) >= SYSDATE AND FND_USER.START_DATE <= SYSDATE AND (NVL(FND_USER.END_DATE , SYSDATE) >= SYSDATE) UNION ALL SELECT MEMBER_GROUP.OBJECT_ID GROUP_ID , FND_USER.PERSON_PARTY_ID MEMBER_PERSON_ID , FND_USER.USER_ID MEMBER_USER_ID , FND_USER.USER_NAME MEMBER_USER_NAME , 'VENDOR' MEMBER_PERSON_TYPE FROM FND_USER FND_USER , HZ_CODE_ASSIGNMENTS VEND_ASSIGN , HZ_RELATIONSHIPS MEMBER_GROUP WHERE VEND_ASSIGN.OWNER_TABLE_ID = FND_USER.PERSON_PARTY_ID AND VEND_ASSIGN.OWNER_TABLE_NAME = 'HZ_PARTIES' AND VEND_ASSIGN.CLASS_CODE = 'VENDOR_USER' AND VEND_ASSIGN.CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE' AND MEMBER_GROUP.SUBJECT_ID = FND_USER.PERSON_PARTY_ID AND MEMBER_GROUP.SUBJECT_TYPE = 'PERSON' AND MEMBER_GROUP.OBJECT_TYPE = 'GROUP' AND MEMBER_GROUP.RELATIONSHIP_TYPE = 'MEMBERSHIP' AND MEMBER_GROUP.STATUS = 'A' AND MEMBER_GROUP.START_DATE <= SYSDATE AND NVL(MEMBER_GROUP.END_DATE , SYSDATE) >= SYSDATE AND FND_USER.EMPLOYEE_ID IS NULL AND FND_USER.START_DATE <= SYSDATE AND NVL(FND_USER.END_DATE , SYSDATE) >= SYSDATE UNION ALL SELECT MEMBER_GROUP.OBJECT_ID GROUP_ID , FND_USER.PERSON_PARTY_ID MEMBER_PERSON_ID , FND_USER.USER_ID MEMBER_USER_ID , FND_USER.USER_NAME MEMBER_USER_NAME , 'CUSTOMER' MEMBER_PERSON_TYPE FROM FND_USER FND_USER , HZ_RELATIONSHIPS MEMBER_GROUP WHERE EXISTS (SELECT 'X' FROM HZ_CUST_ACCOUNTS HCA WHERE HCA.PARTY_ID = FND_USER.PERSON_PARTY_ID AND HCA.STATUS = 'A') AND MEMBER_GROUP.SUBJECT_ID = FND_USER.PERSON_PARTY_ID AND MEMBER_GROUP.SUBJECT_TYPE = 'PERSON' AND MEMBER_GROUP.OBJECT_TYPE = 'GROUP' AND MEMBER_GROUP.RELATIONSHIP_TYPE = 'MEMBERSHIP' AND MEMBER_GROUP.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND MEMBER_GROUP.STATUS = 'A' AND MEMBER_GROUP.START_DATE <= SYSDATE AND NVL(MEMBER_GROUP.END_DATE , SYSDATE ) >= SYSDATE AND FND_USER.EMPLOYEE_ID IS NULL AND FND_USER.START_DATE <= SYSDATE AND (NVL(FND_USER.END_DATE , SYSDATE) >= SYSDATE)