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