View: JTF_TTY_SRCH_MY_RESOURCES_V

Product:	JTF - CRM Foundation
Description:	Shows all the direct and indirect reports of a salesperson
Implementation/DBA Data:	APPS.JTF_TTY_SRCH_MY_RESOURCES_V

View Text

SELECT DIR.RESOURCE_ID RESOURCE_ID
, MY_GRPS.GROUP_ID GROUP_ID
, ROL.ROLE_CODE
, MY_GRPS.CURRENT_USER_ID CURRENT_USER_ID
FROM JTF_RS_ROLES_VL ROL
, JTF_RS_ROLE_RELATIONS RLT
, JTF_RS_GROUP_MEMBERS GRPMEMO
, JTF_RS_RESOURCE_EXTNS_VL DIR
, ( /* MY_GRPS INLINE VIEW */ /* GROUPS LOGGED-IN USER MANAGES/ADMINISTRATES */ SELECT /*+ NO_MERGE */ DV.GROUP_ID
, DV.PARENT_GROUP_ID
, SGH.RESOURCE_ID
, MRSC.USER_ID CURRENT_USER_ID
, MRSC.RESOURCE_ID CURRENT_USER_RSC_ID
, USG.USAGE
, ROL.ROLE_CODE CURRENT_USER_ROLE_CODE
FROM JTF_RS_GROUP_USAGES USG
, JTF_RS_GROUPS_DENORM DV
, JTF_RS_REP_MANAGERS SGH
, JTF_RS_RESOURCE_EXTNS MRSC
, JTF_RS_ROLES_B ROL
, JTF_RS_ROLE_RELATIONS RLT
WHERE USG.USAGE = 'SALES'
AND USG.GROUP_ID = DV.GROUP_ID
AND RLT.ROLE_ID = ROL.ROLE_ID
AND RLT.ROLE_RELATE_ID = SGH.PAR_ROLE_RELATE_ID
AND SYSDATE BETWEEN NVL(DV.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(DV.END_DATE_ACTIVE
, SYSDATE+1)
AND DV.PARENT_GROUP_ID = SGH.GROUP_ID
AND SYSDATE BETWEEN SGH.START_DATE_ACTIVE
AND NVL(SGH.END_DATE_ACTIVE
, SYSDATE+1)
AND SGH.RESOURCE_ID = SGH.PARENT_RESOURCE_ID
AND (SGH.HIERARCHY_TYPE IN ('MGR_TO_MGR') OR ROL.ROLE_CODE = FND_PROFILE.VALUE('JTF_TTY_NA_PROXY_USER_ROLE'))
AND MRSC.RESOURCE_ID = SGH.RESOURCE_ID ) MY_GRPS
WHERE ( ROL.MEMBER_FLAG = 'Y' OR ROL.MANAGER_FLAG = 'Y' )
AND RLT.ROLE_ID = ROL.ROLE_ID
AND RLT.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND RLT.DELETE_FLAG = 'N'
AND SYSDATE BETWEEN RLT.START_DATE_ACTIVE
AND NVL(RLT.END_DATE_ACTIVE
, SYSDATE+1)
AND RLT.ROLE_RESOURCE_ID = GRPMEMO.GROUP_MEMBER_ID
AND GRPMEMO.DELETE_FLAG = 'N'
AND GRPMEMO.RESOURCE_ID = DIR.RESOURCE_ID
AND GRPMEMO.GROUP_ID = MY_GRPS.GROUP_ID
AND SYSDATE BETWEEN DIR.START_DATE_ACTIVE
AND NVL(DIR.END_DATE_ACTIVE
, SYSDATE+1) UNION ALL /* BASE SALESPERSON LOGGED IN
, I.E.
, USER IS NOT ** A MANAGER OF A SALESGROUP */ SELECT DIR.RESOURCE_ID RESOURCE_ID
, SALES_GRPS.GROUP_ID GROUP_ID
, ROL.ROLE_CODE
, DIR.USER_ID CURRENT_USER_ID
FROM JTF_RS_ROLES_VL ROL
, JTF_RS_ROLE_RELATIONS RLT
, JTF_RS_GROUP_MEMBERS GRPMEMO
, JTF_RS_RESOURCE_EXTNS_VL DIR
, ( /* SALES GROUPS INLINE VIEW */ SELECT DV.GROUP_ID
, DV.GROUP_ID PARENT_GROUP_ID
, NULL PARENT_GROUP_NAME
FROM JTF_RS_GROUP_USAGES USG
, JTF_RS_GROUPS_B DV
WHERE USG.USAGE = 'SALES'
AND USG.GROUP_ID = DV.GROUP_ID
AND SYSDATE BETWEEN NVL(DV.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(DV.END_DATE_ACTIVE
, SYSDATE+1) ) SALES_GRPS
WHERE ROL.MEMBER_FLAG = 'Y'
AND RLT.ROLE_ID = ROL.ROLE_ID
AND RLT.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND RLT.DELETE_FLAG = 'N'
AND SYSDATE BETWEEN RLT.START_DATE_ACTIVE
AND NVL(RLT.END_DATE_ACTIVE
, SYSDATE+1)
AND RLT.ROLE_RESOURCE_ID = GRPMEMO.GROUP_MEMBER_ID
AND GRPMEMO.DELETE_FLAG = 'N'
AND GRPMEMO.RESOURCE_ID = DIR.RESOURCE_ID
AND GRPMEMO.GROUP_ID = SALES_GRPS.GROUP_ID
AND SYSDATE BETWEEN DIR.START_DATE_ACTIVE
AND NVL(DIR.END_DATE_ACTIVE
, SYSDATE+1)
AND NOT EXISTS ( SELECT NULL
FROM JTF_RS_REP_MANAGERS MGR
WHERE MGR.PARENT_RESOURCE_ID = DIR.RESOURCE_ID
AND MGR.PARENT_RESOURCE_ID = MGR.RESOURCE_ID
AND MGR.GROUP_ID = GRPMEMO.GROUP_ID
AND MGR.HIERARCHY_TYPE = 'MGR_TO_MGR'
AND SYSDATE BETWEEN MGR.START_DATE_ACTIVE
AND NVL(MGR.END_DATE_ACTIVE
, SYSDATE+1))

Columns