VIEW: APPS.JTF_TTY_MY_DIRECTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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