[Home] [Help]
View: HZ_GROUP_WF_USER_ROLES_V
View Text
SELECT USER_NAME
, USER_ORIG_SYSTEM
, USER_ORIG_SYSTEM_ID
, ROLE_NAME
, ROLE_ORIG_SYSTEM
, ROLE_ORIG_SYSTEM_ID
, START_DATE
, EXPIRATION_DATE
, SECURITY_GROUP_ID
, PARTITION_ID
FROM ( SELECT 'HZ_PARTY:'||TO_CHAR(SP.PARTY_ID) USER_NAME
, 'HZ_PARTY' USER_ORIG_SYSTEM
, SP.PARTY_ID USER_ORIG_SYSTEM_ID
, 'HZ_GROUP:'||TO_CHAR(OP.PARTY_ID) ROLE_NAME
, 'HZ_GROUP' ROLE_ORIG_SYSTEM
, OP.PARTY_ID ROLE_ORIG_SYSTEM_ID
, PR.START_DATE START_DATE
, PR.END_DATE EXPIRATION_DATE
, NULL SECURITY_GROUP_ID
, 8 PARTITION_ID
, RANK() OVER (PARTITION BY PR.SUBJECT_ID
, PR.OBJECT_ID ORDER BY PR.RELATIONSHIP_ID DESC) AS RELRANK
FROM HZ_RELATIONSHIPS PR
, HZ_PARTIES SP
, HZ_PARTIES OP
WHERE SP.PARTY_ID = PR.SUBJECT_ID
AND SP.PARTY_TYPE = 'PERSON'
AND OP.PARTY_ID = PR.OBJECT_ID
AND OP.PARTY_TYPE = 'GROUP'
AND PR.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND PR.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SP.PARTY_TYPE = PR.SUBJECT_TYPE
AND OP.PARTY_TYPE = PR.OBJECT_TYPE
AND OP.STATUS IN ('A'
, 'I')
AND SP.STATUS IN ('A'
, 'I')
AND PR.STATUS IN ('A'
, 'I') ) TEMP
WHERE TEMP.RELRANK = 1
Columns
Name |
USER_NAME |
USER_ORIG_SYSTEM |
USER_ORIG_SYSTEM_ID |
ROLE_NAME |
ROLE_ORIG_SYSTEM |
ROLE_ORIG_SYSTEM_ID |
START_DATE |
EXPIRATION_DATE |
SECURITY_GROUP_ID |
PARTITION_ID |