VIEW: APPS.JTF_TERR_TYPES_NAV

Source

View Text - Preformatted

SELECT s.meaning label, nt.node_type node_type, '"' || s.source_id || '"' node_key, nt.icon_name icon_name, 'N' leaf_flag, 'ROOT' parent_node_type, nt.root_key parent_node_key from jtf_nav_node_types_v nt, jtf_sources s, jtf_terr t WHERE nt.root_key = 'JTF_TERR_TYPE_ROOT' and nt.node_type = 'SOURCE' and t.terr_id = 1 UNION select jtt.name label, nt.node_type node_type, '"' || jtt.terr_type_id || '"' node_key, nt.icon_name icon_name, decode(count(jtall.territory_type_id),0,'Y','N') leaf_flag, 'SOURCE' parent_node_type, '"' || js.source_id || '"' parent_node_key from jtf_nav_node_types_v nt, jtf_terr_types jtt, jtf_terr jtall, jtf_terr_type_usgs jttu, jtf_sources js where nt.root_key = 'JTF_TERR_TYPE_ROOT' and nt.node_type = 'TERR_TYPE' and jtt.terr_type_id = jtall.territory_type_id(+) and jtt.terr_type_id = jttu.terr_type_id and jttu.source_id = js.source_id group by jtt.name, nt.node_type, jtt.terr_type_id, nt.node_type, nt.icon_name, nt.root_key, js.source_id UNION select ta.name label, nt.node_type node_type, '"' || ta.terr_id || '"' node_key, nt.icon_name icon_name, 'Y' leaf_flag, 'TERR_TYPE' parent_node_type, '"' || ta.territory_type_id || '"' parent_node_key from jtf_nav_node_types_v nt, jtf_terr ta, jtf_terr_types tt where nt.root_key = 'JTF_TERR_TYPE_ROOT' and nt.node_type = 'TERRITORY' and ta.territory_type_id = tt.terr_type_id order by label

View Text - HTML Formatted

SELECT S.MEANING LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || S.SOURCE_ID || '"' NODE_KEY
, NT.ICON_NAME ICON_NAME
, 'N' LEAF_FLAG
, 'ROOT' PARENT_NODE_TYPE
, NT.ROOT_KEY PARENT_NODE_KEY
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_SOURCES S
, JTF_TERR T
WHERE NT.ROOT_KEY = 'JTF_TERR_TYPE_ROOT'
AND NT.NODE_TYPE = 'SOURCE'
AND T.TERR_ID = 1 UNION SELECT JTT.NAME LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || JTT.TERR_TYPE_ID || '"' NODE_KEY
, NT.ICON_NAME ICON_NAME
, DECODE(COUNT(JTALL.TERRITORY_TYPE_ID)
, 0
, 'Y'
, 'N') LEAF_FLAG
, 'SOURCE' PARENT_NODE_TYPE
, '"' || JS.SOURCE_ID || '"' PARENT_NODE_KEY
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_TERR_TYPES JTT
, JTF_TERR JTALL
, JTF_TERR_TYPE_USGS JTTU
, JTF_SOURCES JS
WHERE NT.ROOT_KEY = 'JTF_TERR_TYPE_ROOT'
AND NT.NODE_TYPE = 'TERR_TYPE'
AND JTT.TERR_TYPE_ID = JTALL.TERRITORY_TYPE_ID(+)
AND JTT.TERR_TYPE_ID = JTTU.TERR_TYPE_ID
AND JTTU.SOURCE_ID = JS.SOURCE_ID GROUP BY JTT.NAME
, NT.NODE_TYPE
, JTT.TERR_TYPE_ID
, NT.NODE_TYPE
, NT.ICON_NAME
, NT.ROOT_KEY
, JS.SOURCE_ID UNION SELECT TA.NAME LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || TA.TERR_ID || '"' NODE_KEY
, NT.ICON_NAME ICON_NAME
, 'Y' LEAF_FLAG
, 'TERR_TYPE' PARENT_NODE_TYPE
, '"' || TA.TERRITORY_TYPE_ID || '"' PARENT_NODE_KEY
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_TERR TA
, JTF_TERR_TYPES TT
WHERE NT.ROOT_KEY = 'JTF_TERR_TYPE_ROOT'
AND NT.NODE_TYPE = 'TERRITORY'
AND TA.TERRITORY_TYPE_ID = TT.TERR_TYPE_ID ORDER BY LABEL