VIEW: APPS.CSF_DC_RESOURCES_V

Source

View Text - Preformatted

SELECT DISTINCT resource_id , resource_type , resource_name , MAX (terr_id) territory_id FROM (SELECT /*+ cardinality (t 1) */ DISTINCT jtr.resource_id , jtr.resource_type , csf_resource_pub.get_resource_name (jtr.resource_id, jtr.resource_type ) resource_name, jtb.terr_id , RANK () OVER (PARTITION BY jtr.resource_id, jtr.resource_type ORDER BY NVL (jtb.absolute_rank, 0 ) DESC NULLS LAST) AS comp_rank FROM jtf_terr_rsc_all jtr, jtf_terr_all jtb , TABLE (CAST (csf_util_pvt.get_selected_terr_table AS jtf_number_table ) ) t WHERE jtb.terr_id = jtr.terr_id AND jtr.terr_id = t.column_value AND NVL(FND_PROFILE.value('CSF_DC_DISPLAY_ONLY_TECHNICIANS'), 'N') ='N' UNION SELECT /*+ cardinality (t 1) */ DISTINCT jtr.resource_id , jtr.resource_type , csf_resource_pub.get_resource_name (jtr.resource_id, jtr.resource_type ) resource_name, jtb.terr_id , RANK () OVER (PARTITION BY jtr.resource_id, jtr.resource_type ORDER BY NVL (jtb.absolute_rank, 0 ) DESC NULLS LAST) AS comp_rank FROM jtf_terr_rsc_all jtr, jtf_terr_all jtb , TABLE (CAST (csf_util_pvt.get_selected_terr_table AS jtf_number_table ) ) t, jtf_rs_defresroles_vl roles WHERE jtb.terr_id = jtr.terr_id AND jtr.terr_id = t.column_value AND jtr.resource_id = roles.role_resource_id AND roles.role_type_code = decode(NVL(FND_PROFILE.value('CSF_DC_DISPLAY_ONLY_TECHNICIANS'), 'N'),'Y','CSF_REPRESENTATIVE','TT') AND (SYSDATE >= TRUNC (roles.res_rl_start_date) OR roles.res_rl_start_date IS NULL) AND (SYSDATE <= TRUNC (roles.res_rl_end_date) + 1 OR roles.res_rl_end_date IS NULL) AND NVL(roles.delete_flag, 'N') = 'N' ) WHERE comp_rank = 1 AND (RESOURCE_ID,RESOURCE_TYPE ) NOT IN (SELECT B.RESOURCE_ID, B.RESOURCE_TYPE FROM JTF_RS_DEFRESROLES_VL A, JTF_RS_ALL_RESOURCES_VL B, JTF_RS_ROLES_B D WHERE A.ROLE_RESOURCE_ID =RESOURCE_ID AND B.RESOURCE_ID =A.ROLE_RESOURCE_ID AND B.RESOURCE_TYPE =RESOURCE_TYPE AND D.ROLE_ID = A.ROLE_ID AND NVL( A.DELETE_FLAG, 'N') = 'N' AND A.ROLE_TYPE_CODE ='CSF_THIRD_PARTY' AND (SYSDATE >= TRUNC (A.RES_RL_START_DATE) OR A.RES_RL_START_DATE IS NULL) AND (SYSDATE <= TRUNC (A.RES_RL_END_DATE) + 1 OR A.RES_RL_END_DATE IS NULL) AND D.ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR') ) GROUP BY resource_id, resource_type, resource_name

View Text - HTML Formatted

SELECT DISTINCT RESOURCE_ID
, RESOURCE_TYPE
, RESOURCE_NAME
, MAX (TERR_ID) TERRITORY_ID
FROM (SELECT /*+ CARDINALITY (T 1) */ DISTINCT JTR.RESOURCE_ID
, JTR.RESOURCE_TYPE
, CSF_RESOURCE_PUB.GET_RESOURCE_NAME (JTR.RESOURCE_ID
, JTR.RESOURCE_TYPE ) RESOURCE_NAME
, JTB.TERR_ID
, RANK () OVER (PARTITION BY JTR.RESOURCE_ID
, JTR.RESOURCE_TYPE ORDER BY NVL (JTB.ABSOLUTE_RANK
, 0 ) DESC NULLS LAST) AS COMP_RANK
FROM JTF_TERR_RSC_ALL JTR
, JTF_TERR_ALL JTB
, TABLE (CAST (CSF_UTIL_PVT.GET_SELECTED_TERR_TABLE AS JTF_NUMBER_TABLE ) ) T
WHERE JTB.TERR_ID = JTR.TERR_ID
AND JTR.TERR_ID = T.COLUMN_VALUE
AND NVL(FND_PROFILE.VALUE('CSF_DC_DISPLAY_ONLY_TECHNICIANS')
, 'N') ='N' UNION SELECT /*+ CARDINALITY (T 1) */ DISTINCT JTR.RESOURCE_ID
, JTR.RESOURCE_TYPE
, CSF_RESOURCE_PUB.GET_RESOURCE_NAME (JTR.RESOURCE_ID
, JTR.RESOURCE_TYPE ) RESOURCE_NAME
, JTB.TERR_ID
, RANK () OVER (PARTITION BY JTR.RESOURCE_ID
, JTR.RESOURCE_TYPE ORDER BY NVL (JTB.ABSOLUTE_RANK
, 0 ) DESC NULLS LAST) AS COMP_RANK
FROM JTF_TERR_RSC_ALL JTR
, JTF_TERR_ALL JTB
, TABLE (CAST (CSF_UTIL_PVT.GET_SELECTED_TERR_TABLE AS JTF_NUMBER_TABLE ) ) T
, JTF_RS_DEFRESROLES_VL ROLES
WHERE JTB.TERR_ID = JTR.TERR_ID
AND JTR.TERR_ID = T.COLUMN_VALUE
AND JTR.RESOURCE_ID = ROLES.ROLE_RESOURCE_ID
AND ROLES.ROLE_TYPE_CODE = DECODE(NVL(FND_PROFILE.VALUE('CSF_DC_DISPLAY_ONLY_TECHNICIANS')
, 'N')
, 'Y'
, 'CSF_REPRESENTATIVE'
, 'TT')
AND (SYSDATE >= TRUNC (ROLES.RES_RL_START_DATE) OR ROLES.RES_RL_START_DATE IS NULL)
AND (SYSDATE <= TRUNC (ROLES.RES_RL_END_DATE) + 1 OR ROLES.RES_RL_END_DATE IS NULL)
AND NVL(ROLES.DELETE_FLAG
, 'N') = 'N' )
WHERE COMP_RANK = 1
AND (RESOURCE_ID
, RESOURCE_TYPE ) NOT IN (SELECT B.RESOURCE_ID
, B.RESOURCE_TYPE
FROM JTF_RS_DEFRESROLES_VL A
, JTF_RS_ALL_RESOURCES_VL B
, JTF_RS_ROLES_B D
WHERE A.ROLE_RESOURCE_ID =RESOURCE_ID
AND B.RESOURCE_ID =A.ROLE_RESOURCE_ID
AND B.RESOURCE_TYPE =RESOURCE_TYPE
AND D.ROLE_ID = A.ROLE_ID
AND NVL( A.DELETE_FLAG
, 'N') = 'N'
AND A.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
AND (SYSDATE >= TRUNC (A.RES_RL_START_DATE) OR A.RES_RL_START_DATE IS NULL)
AND (SYSDATE <= TRUNC (A.RES_RL_END_DATE) + 1 OR A.RES_RL_END_DATE IS NULL)
AND D.ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID'
, 'CSF_THIRD_PARTY_ADMINISTRATOR') ) GROUP BY RESOURCE_ID
, RESOURCE_TYPE
, RESOURCE_NAME