View: JTF_TR_ALL_RESOURCES_V

Product:	JTF - CRM Foundation
Description:	This view stores all the resources
Implementation/DBA Data:	APPS.JTF_TR_ALL_RESOURCES_V

View Text

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'

Columns