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.

VIEW: APPS.JTF_RS_DBI_RES_GRP_VL

Source

View Text - Preformatted


SELECT drg.VALUE||gr.group_name value, to_char(drg.id) id, drg.current_id, 
       drg.parent_id, drg.usage, drg.denorm_level, drg.START_DATE, drg.end_date,
       drg.mem_flag, drg.mem_status, drg.resource_id, drg.debug_column 
       ,drg.active_grp_rel_only
FROM   jtf_rs_dbi_denorm_res_groups drg 
      ,jtf_rs_groups_tl gr 
WHERE drg.user_id = fnd_global.user_id 
AND   drg.id = gr.group_id 
AND   gr.LANGUAGE = USERENV('LANG')
AND   drg.current_id <> -7777
UNION ALL
-- dummy query for PMV
SELECT 'DUMMY RECORD' VALUE, TO_CHAR(-1111) id, TO_NUMBER(-8888) current_id,
       TO_NUMBER(-8888) parent_id, null usage, TO_NUMBER(0) denorm_level, 
       to_date(NULL) start_date, to_date(NULL) end_date, 'N' mem_flag, 
       'A' mem_status, TO_NUMBER(-8888) resource_id, 'ZZ-DUMMY-RECORD' DEBUG_COLUMN 
       , 'N' active_grp_rel_only
FROM   dual 
UNION ALL
-- Group Members list
SELECT drg.VALUE||res.resource_name||'.'||gr.group_name value, drg.id_for_grp_mem id, 
       drg.current_id, drg.parent_id, drg.usage, drg.denorm_level, drg.START_DATE, 
       drg.end_date, drg.mem_flag, drg.mem_status, drg.resource_id, drg.debug_column 
       ,drg.active_grp_rel_only
FROM   jtf_rs_dbi_denorm_res_groups drg 
      ,jtf_rs_groups_tl gr 
      ,jtf_rs_resource_extns_tl res 
WHERE  drg.user_id IS NULL 
AND    drg.current_id = gr.group_id 
AND    drg.grp_mem_resource_id = res.resource_id 
AND    gr.LANGUAGE = userenv('LANG') 
AND    res.LANGUAGE = userenv('LANG')
--  where clause to prevent salesreps from seeing group memebers list 
--  IF they are not manager or admin
AND    EXISTS (SELECT '1' FROM jtf_rs_dbi_denorm_res_groups drg1
               WHERE  drg1.id = drg1.current_id
               AND    drg.current_id = drg1.current_id
               AND    drg1.user_id = fnd_global.user_id
               AND    drg1.usage = drg.usage
               AND    drg1.current_id = gr.group_id
			   )
UNION ALL 
-- Query for Salesrep Login (members login) (DBI7.1)
SELECT drg.VALUE||res.resource_name||'.'||gr.group_name value, drg.id_for_grp_mem id, 
       drg.current_id, drg.parent_id, drg.usage, drg.denorm_level, drg.START_DATE, 
       drg.end_date, drg.mem_flag, drg.mem_status, drg.resource_id, drg.debug_column 
       ,drg.active_grp_rel_only
FROM   jtf_rs_dbi_denorm_res_groups drg 
      ,jtf_rs_groups_tl gr 
      ,jtf_rs_resource_extns_tl res 
WHERE  drg.user_id = fnd_global.user_id
AND    drg.current_id = -7777
AND    drg.id = gr.group_id 
AND    drg.resource_id = res.resource_id
AND    gr.LANGUAGE = userenv('LANG') 
AND    res.LANGUAGE = userenv('LANG')
UNION ALL
-- Peer Groups
SELECT  DISTINCT (SELECT DECODE(d1.active_flag,'Y',DECODE(x.active_grp_rel_only,'Y',' -- ', ' -- [ '), ' -- [ ')
         FROM  jtf_rs_dbi_denorm_res_groups x
         WHERE x.current_id = drg1.current_id
         AND   x.id = drg1.parent_id
         AND   x.user_id = drg1.user_id
         AND   x.usage  = drg1.usage
         AND   ROWNUM = 1
         )||grp.group_name VALUE
	   , to_char(d1.group_id) id, drg1.current_id current_id,
       d1.parent_group_id parent_id, usg.usage, d1.denorm_level denorm_level,
       d1.start_date_active start_date, d1.end_date_active end_date
       ,'N' mem_flag , 'A' mem_status
       , drg1.resource_id, 'B-PEER' DEBUG_COLUMN
       ,DECODE(d1.active_flag,'Y','Y','N') active_grp_rel_only      
FROM    jtf_rs_groups_denorm d1
       ,jtf_rs_dbi_denorm_res_groups drg1
       ,jtf_rs_groups_tl grp
       ,jtf_rs_dbi_mgr_groups mgr
       ,jtf_rs_group_usages usg
WHERE  drg1.current_id = drg1.id
AND    drg1.denorm_level > 0 
AND    drg1.parent_id = d1.parent_group_id
AND    drg1.current_id <> d1.group_id
AND    drg1.parent_id = d1.actual_parent_id 
AND    d1.denorm_level = 1
AND    d1.latest_relationship_flag = 'Y'
AND    drg1.user_id = fnd_global.user_id 
AND    d1.group_id = grp.group_id
AND    grp.LANGUAGE = USERENV('LANG')
AND    mgr.user_id = drg1.user_id
AND    mgr.usage = drg1.usage
AND    d1.group_id = usg.group_id
AND    usg.usage = drg1.usage
AND    EXISTS (SELECT /*+ index(d2, JTF_RS_GROUPS_DENORM_N3) */ 1
               FROM   jtf_rs_groups_denorm d2
               WHERE  mgr.group_id = d2.parent_group_id
               AND    d1.group_id = d2.group_id
			   AND    d2.latest_relationship_flag = 'Y')

View Text - HTML Formatted

SELECT DRG.VALUE||GR.GROUP_NAME VALUE
, TO_CHAR(DRG.ID) ID
, DRG.CURRENT_ID
, DRG.PARENT_ID
, DRG.USAGE
, DRG.DENORM_LEVEL
, DRG.START_DATE
, DRG.END_DATE
, DRG.MEM_FLAG
, DRG.MEM_STATUS
, DRG.RESOURCE_ID
, DRG.DEBUG_COLUMN
, DRG.ACTIVE_GRP_REL_ONLY FROM JTF_RS_DBI_DENORM_RES_GROUPS DRG
, JTF_RS_GROUPS_TL GR WHERE DRG.USER_ID = FND_GLOBAL.USER_ID AND DRG.ID = GR.GROUP_ID AND GR.LANGUAGE = USERENV('LANG') AND DRG.CURRENT_ID <> -7777 UNION ALL -- DUMMY QUERY FOR PMV SELECT 'DUMMY RECORD' VALUE
, TO_CHAR(-1111) ID
, TO_NUMBER(-8888) CURRENT_ID
, TO_NUMBER(-8888) PARENT_ID
, NULL USAGE
, TO_NUMBER(0) DENORM_LEVEL
, TO_DATE(NULL) START_DATE
, TO_DATE(NULL) END_DATE
, 'N' MEM_FLAG
, 'A' MEM_STATUS
, TO_NUMBER(-8888) RESOURCE_ID
, 'ZZ-DUMMY-RECORD' DEBUG_COLUMN
, 'N' ACTIVE_GRP_REL_ONLY FROM DUAL UNION ALL -- GROUP MEMBERS LIST SELECT DRG.VALUE||RES.RESOURCE_NAME||'.'||GR.GROUP_NAME VALUE
, DRG.ID_FOR_GRP_MEM ID
, DRG.CURRENT_ID
, DRG.PARENT_ID
, DRG.USAGE
, DRG.DENORM_LEVEL
, DRG.START_DATE
, DRG.END_DATE
, DRG.MEM_FLAG
, DRG.MEM_STATUS
, DRG.RESOURCE_ID
, DRG.DEBUG_COLUMN
, DRG.ACTIVE_GRP_REL_ONLY FROM JTF_RS_DBI_DENORM_RES_GROUPS DRG
, JTF_RS_GROUPS_TL GR
, JTF_RS_RESOURCE_EXTNS_TL RES WHERE DRG.USER_ID IS NULL AND DRG.CURRENT_ID = GR.GROUP_ID AND DRG.GRP_MEM_RESOURCE_ID = RES.RESOURCE_ID AND GR.LANGUAGE = USERENV('LANG') AND RES.LANGUAGE = USERENV('LANG') --
WHERE CLAUSE TO PREVENT SALESREPS
FROM SEEING GROUP MEMEBERS LIST -- IF THEY ARE NOT MANAGER OR ADMIN AND EXISTS (SELECT '1'
FROM JTF_RS_DBI_DENORM_RES_GROUPS DRG1
WHERE DRG1.ID = DRG1.CURRENT_ID
AND DRG.CURRENT_ID = DRG1.CURRENT_ID
AND DRG1.USER_ID = FND_GLOBAL.USER_ID
AND DRG1.USAGE = DRG.USAGE
AND DRG1.CURRENT_ID = GR.GROUP_ID ) UNION ALL -- QUERY FOR SALESREP LOGIN (MEMBERS LOGIN) (DBI7.1) SELECT DRG.VALUE||RES.RESOURCE_NAME||'.'||GR.GROUP_NAME VALUE
, DRG.ID_FOR_GRP_MEM ID
, DRG.CURRENT_ID
, DRG.PARENT_ID
, DRG.USAGE
, DRG.DENORM_LEVEL
, DRG.START_DATE
, DRG.END_DATE
, DRG.MEM_FLAG
, DRG.MEM_STATUS
, DRG.RESOURCE_ID
, DRG.DEBUG_COLUMN
, DRG.ACTIVE_GRP_REL_ONLY FROM JTF_RS_DBI_DENORM_RES_GROUPS DRG
, JTF_RS_GROUPS_TL GR
, JTF_RS_RESOURCE_EXTNS_TL RES WHERE DRG.USER_ID = FND_GLOBAL.USER_ID AND DRG.CURRENT_ID = -7777 AND DRG.ID = GR.GROUP_ID AND DRG.RESOURCE_ID = RES.RESOURCE_ID AND GR.LANGUAGE = USERENV('LANG') AND RES.LANGUAGE = USERENV('LANG') UNION ALL -- PEER GROUPS SELECT DISTINCT (SELECT DECODE(D1.ACTIVE_FLAG
, 'Y'
, DECODE(X.ACTIVE_GRP_REL_ONLY
, 'Y'
, ' -- '
, ' -- [ ')
, ' -- [ ')
FROM JTF_RS_DBI_DENORM_RES_GROUPS X
WHERE X.CURRENT_ID = DRG1.CURRENT_ID
AND X.ID = DRG1.PARENT_ID
AND X.USER_ID = DRG1.USER_ID
AND X.USAGE = DRG1.USAGE
AND ROWNUM = 1 )||GRP.GROUP_NAME VALUE
, TO_CHAR(D1.GROUP_ID) ID
, DRG1.CURRENT_ID CURRENT_ID
, D1.PARENT_GROUP_ID PARENT_ID
, USG.USAGE
, D1.DENORM_LEVEL DENORM_LEVEL
, D1.START_DATE_ACTIVE START_DATE
, D1.END_DATE_ACTIVE END_DATE
, 'N' MEM_FLAG
, 'A' MEM_STATUS
, DRG1.RESOURCE_ID
, 'B-PEER' DEBUG_COLUMN
, DECODE(D1.ACTIVE_FLAG
, 'Y'
, 'Y'
, 'N') ACTIVE_GRP_REL_ONLY FROM JTF_RS_GROUPS_DENORM D1
, JTF_RS_DBI_DENORM_RES_GROUPS DRG1
, JTF_RS_GROUPS_TL GRP
, JTF_RS_DBI_MGR_GROUPS MGR
, JTF_RS_GROUP_USAGES USG WHERE DRG1.CURRENT_ID = DRG1.ID AND DRG1.DENORM_LEVEL > 0 AND DRG1.PARENT_ID = D1.PARENT_GROUP_ID AND DRG1.CURRENT_ID <> D1.GROUP_ID AND DRG1.PARENT_ID = D1.ACTUAL_PARENT_ID AND D1.DENORM_LEVEL = 1 AND D1.LATEST_RELATIONSHIP_FLAG = 'Y' AND DRG1.USER_ID = FND_GLOBAL.USER_ID AND D1.GROUP_ID = GRP.GROUP_ID AND GRP.LANGUAGE = USERENV('LANG') AND MGR.USER_ID = DRG1.USER_ID AND MGR.USAGE = DRG1.USAGE AND D1.GROUP_ID = USG.GROUP_ID AND USG.USAGE = DRG1.USAGE AND EXISTS (SELECT /*+ INDEX(D2
, JTF_RS_GROUPS_DENORM_N3) */ 1
FROM JTF_RS_GROUPS_DENORM D2
WHERE MGR.GROUP_ID = D2.PARENT_GROUP_ID
AND D1.GROUP_ID = D2.GROUP_ID
AND D2.LATEST_RELATIONSHIP_FLAG = 'Y')