VIEW: APPS.JTF_TR_ALL_RESOURCES_V

Source

View Text - Preformatted

SELECT JRARV1.RESOURCE_ID , JRARV1.RESOURCE_NUMBER , JRARV1.RESOURCE_TYPE , JRARV1.RESOURCE_NAME , JRARV1.EMAIL , JRARV1.START_DATE_ACTIVE , JRARV1.END_DATE_ACTIVE , JRARV1.PHONE , TO_NUMBER(NULL) GROUP_ID , NULL GROUP_NAME , NULL ROLE FROM JTF_RS_ALL_RESOURCES_VL JRARV1 UNION ALL SELECT JRARV1.RESOURCE_ID , JRARV1.RESOURCE_NUMBER , JRARV1.RESOURCE_TYPE , JRARV1.RESOURCE_NAME , JRARV1.EMAIL , JRARV1.START_DATE_ACTIVE , JRARV1.END_DATE_ACTIVE , JRARV1.PHONE , JRGV.GROUP_ID , JRGV.GROUP_NAME , NULL ROLE FROM JTF_RS_GROUP_MEMBERS_VL JRGMV , JTF_RS_GROUPS_VL JRGV , JTF_RS_ALL_RESOURCES_VL JRARV1 WHERE JRGMV.RESOURCE_ID = JRARV1.RESOURCE_ID AND JRGMV.GROUP_ID = JRGV.GROUP_ID AND JRGMV.GROUP_MEMBER_ID NOT IN ( SELECT JRRRV.ROLE_RESOURCE_ID FROM JTF_RS_ROLE_RELATIONS_VL JRRRV WHERE JRRRV.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER') UNION ALL SELECT JRARV1.RESOURCE_ID , JRARV1.RESOURCE_NUMBER , JRARV1.RESOURCE_TYPE , JRARV1.RESOURCE_NAME resource_name , JRARV1.EMAIL , JRARV1.START_DATE_ACTIVE , JRARV1.END_DATE_ACTIVE , JRARV1.PHONE , TO_NUMBER(NULL) GROUP_ID , NULL GROUP_NAME , JRRRV.ROLE_NAME ROLE FROM JTF_RS_ROLE_RELATIONS_VL JRRRV , JTF_RS_ALL_RESOURCES_VL JRARV1 WHERE JRRRV.ROLE_RESOURCE_ID = JRARV1.RESOURCE_ID AND JRRRV.ROLE_RESOURCE_TYPE = DECODE( JRARV1.RESOURCE_TYPE , 'RS_TEAM', 'RS_TEAM' , 'RS_GROUP', 'RS_GROUP' , 'RS_INDIVIDUAL') UNION ALL SELECT JRARV1.RESOURCE_ID , JRARV1.RESOURCE_NUMBER , JRARV1.RESOURCE_TYPE , JRARV1.RESOURCE_NAME resource_name , JRARV1.EMAIL , JRARV1.START_DATE_ACTIVE , JRARV1.END_DATE_ACTIVE , JRARV1.PHONE , JRGV.GROUP_ID , JRGV.GROUP_NAME , JRRRV.ROLE_NAME ROLE FROM JTF_RS_ROLE_RELATIONS_VL JRRRV , JTF_RS_GROUP_MEMBERS_VL JRGMV , JTF_RS_GROUPS_VL JRGV , JTF_RS_ALL_RESOURCES_VL JRARV1 WHERE JRGMV.RESOURCE_ID = JRARV1.RESOURCE_ID AND JRGMV.GROUP_ID = JRGV.GROUP_ID AND JRRRV.ROLE_RESOURCE_ID = JRGMV.GROUP_MEMBER_ID AND JRRRV.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'

View Text - HTML Formatted

SELECT JRARV1.RESOURCE_ID
, JRARV1.RESOURCE_NUMBER
, JRARV1.RESOURCE_TYPE
, JRARV1.RESOURCE_NAME
, JRARV1.EMAIL
, JRARV1.START_DATE_ACTIVE
, JRARV1.END_DATE_ACTIVE
, JRARV1.PHONE
, TO_NUMBER(NULL) GROUP_ID
, NULL GROUP_NAME
, NULL ROLE
FROM JTF_RS_ALL_RESOURCES_VL JRARV1 UNION ALL SELECT JRARV1.RESOURCE_ID
, JRARV1.RESOURCE_NUMBER
, JRARV1.RESOURCE_TYPE
, JRARV1.RESOURCE_NAME
, JRARV1.EMAIL
, JRARV1.START_DATE_ACTIVE
, JRARV1.END_DATE_ACTIVE
, JRARV1.PHONE
, JRGV.GROUP_ID
, JRGV.GROUP_NAME
, NULL ROLE
FROM JTF_RS_GROUP_MEMBERS_VL JRGMV
, JTF_RS_GROUPS_VL JRGV
, JTF_RS_ALL_RESOURCES_VL JRARV1
WHERE JRGMV.RESOURCE_ID = JRARV1.RESOURCE_ID
AND JRGMV.GROUP_ID = JRGV.GROUP_ID
AND JRGMV.GROUP_MEMBER_ID NOT IN ( SELECT JRRRV.ROLE_RESOURCE_ID
FROM JTF_RS_ROLE_RELATIONS_VL JRRRV
WHERE JRRRV.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER') UNION ALL SELECT JRARV1.RESOURCE_ID
, JRARV1.RESOURCE_NUMBER
, JRARV1.RESOURCE_TYPE
, JRARV1.RESOURCE_NAME RESOURCE_NAME
, JRARV1.EMAIL
, JRARV1.START_DATE_ACTIVE
, JRARV1.END_DATE_ACTIVE
, JRARV1.PHONE
, TO_NUMBER(NULL) GROUP_ID
, NULL GROUP_NAME
, JRRRV.ROLE_NAME ROLE
FROM JTF_RS_ROLE_RELATIONS_VL JRRRV
, JTF_RS_ALL_RESOURCES_VL JRARV1
WHERE JRRRV.ROLE_RESOURCE_ID = JRARV1.RESOURCE_ID
AND JRRRV.ROLE_RESOURCE_TYPE = DECODE( JRARV1.RESOURCE_TYPE
, 'RS_TEAM'
, 'RS_TEAM'
, 'RS_GROUP'
, 'RS_GROUP'
, 'RS_INDIVIDUAL') UNION ALL SELECT JRARV1.RESOURCE_ID
, JRARV1.RESOURCE_NUMBER
, JRARV1.RESOURCE_TYPE
, JRARV1.RESOURCE_NAME RESOURCE_NAME
, JRARV1.EMAIL
, JRARV1.START_DATE_ACTIVE
, JRARV1.END_DATE_ACTIVE
, JRARV1.PHONE
, JRGV.GROUP_ID
, JRGV.GROUP_NAME
, JRRRV.ROLE_NAME ROLE
FROM JTF_RS_ROLE_RELATIONS_VL JRRRV
, JTF_RS_GROUP_MEMBERS_VL JRGMV
, JTF_RS_GROUPS_VL JRGV
, JTF_RS_ALL_RESOURCES_VL JRARV1
WHERE JRGMV.RESOURCE_ID = JRARV1.RESOURCE_ID
AND JRGMV.GROUP_ID = JRGV.GROUP_ID
AND JRRRV.ROLE_RESOURCE_ID = JRGMV.GROUP_MEMBER_ID
AND JRRRV.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'