View: JTF_RS_DBI_RES_GRP_VL

Product:	JTF - CRM Foundation
Description:
Implementation/DBA Data:	APPS.JTF_RS_DBI_RES_GRP_VL

View Text

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')