VIEW: APPS.ENG_SECURITY_GROUP_MEMBERS_V

Source

View Text - Preformatted

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' or hr_employee.current_npw_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)

View Text - HTML Formatted

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' OR HR_EMPLOYEE.CURRENT_NPW_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)