View: JTF_RS_SRP_GROUPS_V

Product:	JTF - CRM Foundation
Description:	View for fetching the Default (Primary) group for a Salesrep.
Implementation/DBA Data:	APPS.JTF_RS_SRP_GROUPS_V

View Text

SELECT RESOURCE_ID
, SALESREP_ID
, ORG_ID
, USER_ID
, GROUP_ID
, GROUP_NAME
, ROLE_TYPE_CODE
, ROLE_ID
, ROLE_TYPE_PRIORITY
, ROLE_PRIORITY
, ROLE_START_DATE
, ROLE_END_DATE
, DENORM_COUNT
FROM ( SELECT MEM.RESOURCE_ID
, MEM.GROUP_ID
, GRP.GROUP_NAME
, SRP.SALESREP_ID
, SRP.ORG_ID
, RES.USER_ID
, DECODE(ROL.ROLE_TYPE_CODE
, 'SALES'
, 1
, 2) ROLE_TYPE_PRIORITY
, DECODE('Y'
, ROL.MEMBER_FLAG
, 1
, ROL.MANAGER_FLAG
, 2) ROLE_PRIORITY
, RRL.START_DATE_ACTIVE ROLE_START_DATE
, NVL(RRL.END_DATE_ACTIVE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR')) ROLE_END_DATE
, RRL.ROLE_ID
, ROL.ROLE_TYPE_CODE
, COUNT(DEN.GROUP_ID) DENORM_COUNT
FROM JTF_RS_ROLE_RELATIONS RRL
, JTF_RS_GROUP_MEMBERS MEM
, JTF_RS_SALESREPS SRP
, JTF_RS_GROUP_USAGES USG
, JTF_RS_ROLES_B ROL
, JTF_RS_GROUPS_DENORM DEN
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_GROUPS_TL GRP
WHERE RRL.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND RRL.DELETE_FLAG = 'N'
AND RRL.ROLE_RESOURCE_ID = MEM.GROUP_MEMBER_ID
AND MEM.DELETE_FLAG = 'N'
AND MEM.RESOURCE_ID = SRP.RESOURCE_ID
AND MEM.GROUP_ID = USG.GROUP_ID
AND USG.USAGE = 'SALES'
AND RRL.ROLE_ID = ROL.ROLE_ID
AND 'Y' IN (ROL.MEMBER_FLAG
, ROL.MANAGER_FLAG)
AND MEM.RESOURCE_ID = RES.RESOURCE_ID
AND MEM.GROUP_ID = DEN.GROUP_ID
AND ( RRL.START_DATE_ACTIVE BETWEEN DEN.START_DATE_ACTIVE
AND NVL(DEN.END_DATE_ACTIVE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR')) OR DEN.START_DATE_ACTIVE BETWEEN RRL.START_DATE_ACTIVE
AND NVL(RRL.END_DATE_ACTIVE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR')) )
AND MEM.GROUP_ID = GRP.GROUP_ID
AND GRP.LANGUAGE = USERENV('LANG') GROUP BY MEM.RESOURCE_ID
, MEM.GROUP_ID
, SRP.SALESREP_ID
, SRP.ORG_ID
, RES.USER_ID
, RRL.START_DATE_ACTIVE
, RRL.END_DATE_ACTIVE
, DECODE(ROL.ROLE_TYPE_CODE
, 'SALES'
, 1
, 2)
, DECODE('Y'
, ROL.MEMBER_FLAG
, 1
, ROL.MANAGER_FLAG
, 2)
, RRL.ROLE_ID
, ROL.ROLE_TYPE_CODE
, GRP.GROUP_NAME UNION SELECT TO_NUMBER(-1) RESOURCE_ID
, TO_NUMBER(-1) GROUP_ID
, 'DUMMY' GROUP_NAME
, TO_NUMBER(-9999) SALESREP_ID
, TO_NUMBER(-9999) ORG_ID
, TO_NUMBER(-9999) USER_ID
, TO_NUMBER(9999999999) ROLE_TYPE_PRIORITY
, TO_NUMBER(9999999999) ROLE_PRIORITY
, TO_DATE('01/01/1901'
, 'MM/DD/RRRR') ROLE_START_DATE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR') ROLE_END_DATE
, TO_NUMBER(0) ROLE_ID
, 'DUMMY' ROLE_TYPE_CODE
, TO_NUMBER(99999999999) DENORM_COUNT
FROM DUAL ORDER BY DENORM_COUNT ASC
, ROLE_TYPE_PRIORITY ASC
, ROLE_PRIORITY ASC )

Columns