eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: JTF_TTY_MY_DIRECTS_V

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

View Text

SELECT MY_REPS.RESOURCE_ID
, MY_REPS.RESOURCE_NAME
, MY_REPS.GROUP_ID
, GVL.GROUP_NAME
, MY_REPS.ROLE_CODE
, MY_REPS.ROLE_NAME
, MY_REPS.DIR_USER_ID
, MY_REPS.CURRENT_USER_ID
, MY_REPS.PARENT_GROUP_ID
, MY_REPS.CURRENT_USER_ROLE_CODE
, MY_REPS.CURRENT_USER_RSC_ID
FROM JTF_RS_GROUPS_VL GVL
, ( /* SALESPERSON IS A MEMBER OF ONE OF HIS MGR'S GROUP OR ** IS A MANAGER OF A CHILD GROUP OF ONE OF HIS MGR'S GROUPS */ SELECT DIR.RESOURCE_ID
, DIR.RESOURCE_NAME
, DIR.USER_ID DIR_USER_ID
, MY_GRPS.GROUP_ID
, MY_GRPS.PARENT_GROUP_ID
, MY_GRPS.CURRENT_USER_ID
, ROL.ROLE_CODE
, ROL.ROLE_NAME
, MY_GRPS.CURRENT_USER_ROLE_CODE
, MY_GRPS.CURRENT_USER_RSC_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 ROL.ROLE_TYPE_CODE IN ( 'SALES'
, 'TELESALES'
, 'FIELDSALES' )
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 ROL.ACTIVE_FLAG = 'Y'
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 (DV.IMMEDIATE_PARENT_FLAG = 'Y' OR DV.GROUP_ID = DV.PARENT_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.MANAGER_FLAG = 'Y'
AND ROL.ACTIVE_FLAG = 'Y'
AND ROL.ROLE_TYPE_CODE IN ( 'SALES'
, 'FIELDSALES'
, 'TELESALES' )
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 SELECT DIR.RESOURCE_ID
, DIR.RESOURCE_NAME
, DIR.USER_ID DIR_USER_ID
, MY_GRPS.GROUP_ID
, MY_GRPS.PARENT_GROUP_ID
, MY_GRPS.CURRENT_USER_ID
, ROL.ROLE_CODE
, ROL.ROLE_NAME
, MY_GRPS.CURRENT_USER_ROLE_CODE
, MY_GRPS.CURRENT_USER_RSC_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 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 ROL.ROLE_TYPE_CODE IN ( 'SALES'
, 'TELESALES'
, 'FIELDSALES' )
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 ROL.ACTIVE_FLAG = 'Y'
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 (DV.GROUP_ID = DV.PARENT_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'
AND ROL.ACTIVE_FLAG = 'Y'
AND ROL.ROLE_TYPE_CODE IN ( 'SALES'
, 'FIELDSALES'
, 'TELESALES' )
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 /* USER IS A MEMBER OF A PARENT GROUP OR GROUP HE DOESN'T MANAGE */ SELECT RSC.RESOURCE_ID
, RSC.RESOURCE_NAME
, RSC.USER_ID DIR_USER_ID
, GRPMEM.GROUP_ID
, GRPMEM.GROUP_ID PARENT_GROUP_ID
, RSC.USER_ID CURRENT_USER_ID
, ROL.ROLE_CODE
, ROL.ROLE_NAME
, ROL.ROLE_CODE CURRENT_USER_ROLE_CODE
, RSC.RESOURCE_ID CURRENT_USER_RSC_ID
FROM JTF_RS_RESOURCE_EXTNS_VL RSC
, JTF_RS_GROUPS_VL GRP
, JTF_RS_ROLES_VL ROL
, JTF_RS_GROUP_MEMBERS GRPMEM
, JTF_RS_ROLE_RELATIONS RLT
WHERE RSC.RESOURCE_ID = GRPMEM.RESOURCE_ID
AND GRPMEM.GROUP_ID = GRP.GROUP_ID
AND GRPMEM.DELETE_FLAG = 'N'
AND SYSDATE BETWEEN GRP.START_DATE_ACTIVE
AND NVL(GRP.END_DATE_ACTIVE
, SYSDATE+1 )
AND GRPMEM.GROUP_MEMBER_ID = RLT.ROLE_RESOURCE_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_ID = ROL.ROLE_ID
AND ROL.MEMBER_FLAG = 'Y'
AND ROL.ACTIVE_FLAG = 'Y'
AND ROL.ROLE_TYPE_CODE IN ( 'SALES'
, 'TELESALES'
, 'FIELDSALES' )
AND SYSDATE BETWEEN RSC.START_DATE_ACTIVE
AND NVL(RSC.END_DATE_ACTIVE
, SYSDATE+1)
AND NOT EXISTS ( SELECT NULL
FROM JTF_RS_REP_MANAGERS MGR
WHERE MGR.PARENT_RESOURCE_ID = RSC.RESOURCE_ID
AND MGR.PARENT_RESOURCE_ID = MGR.RESOURCE_ID
AND MGR.GROUP_ID = GRPMEM.GROUP_ID
AND MGR.HIERARCHY_TYPE = 'MGR_TO_MGR'
AND SYSDATE BETWEEN MGR.START_DATE_ACTIVE
AND NVL(MGR.END_DATE_ACTIVE
, SYSDATE+1) ) ) MY_REPS
WHERE MY_REPS.GROUP_ID = GVL.GROUP_ID

Columns

Name
RESOURCE_ID
RESOURCE_NAME
GROUP_ID
GROUP_NAME
ROLE_CODE
ROLE_NAME
DIR_USER_ID
CURRENT_USER_ID
PARENT_GROUP_ID
CURRENT_USER_ROLE_CODE
CURRENT_USER_RSC_ID