VIEW: APPS.HZ_GROUP_WF_USER_ROLES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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