[Home] [Help]
View: 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')