VIEW: APPS.JTF_TERRITORIES_NAV

Source

View Text - Preformatted

SELECT "LABEL","NODE_TYPE","NODE_KEY","ICON_NAME","LEAF_FLAG","PARENT_NODE_TYPE","PARENT_NODE_KEY","DISPLAY_RANK" FROM ( SELECT jtall.name || ' [' || jtall.rank || ']' label, nt.node_type node_type, '"' || jtall.terr_id || '"' node_key, decode(count(jtall2.parent_territory_id),0, 'territor','aftreecl') icon_name, decode(count(jtall2.parent_territory_id),0,'Y','N') leaf_flag, 'TERRITORY' parent_node_type, '"'|| jtall.parent_territory_id || '"' parent_node_key, jtall.rank display_rank from jtf_nav_node_types_v nt, jtf_terr jtall, jtf_sources js, jtf_terr_usgs jtu, jtf_terr jtall2 WHERE nt.root_key = 'JTF_TERR_ROOT' and nt.node_type = 'TERRITORY' and jtall.terr_id = jtu.terr_id and jtu.source_id = js.source_id and jtall.terr_id = jtall2.parent_territory_id(+) and jtall.parent_territory_id != 1 and jtall.template_flag <> 'Y' and jtall.escalation_territory_flag <> 'Y' and jtall.org_id = jtall2.org_id(+) group by jtall.name, nt.icon_name, nt.node_type, jtall.terr_id, jtall.parent_territory_id, jtall.rank UNION ALL select jtall.name || ' [' || jtall.rank || ']' label, nt.node_type node_type, '"' || jtall.terr_id || '"' node_key, decode(count(jtall2.parent_territory_id),0, 'territor','aftreecl') icon_name, decode(count(jtall2.parent_territory_id),0,'Y','N') leaf_flag, 'SOURCE' parent_node_type, '"'|| js.source_id || '"' parent_node_key, jtall.rank display_rank from jtf_nav_node_types_v nt, jtf_terr jtall, jtf_terr jtall2, jtf_sources js, jtf_terr_usgs jtu where nt.root_key = 'JTF_TERR_ROOT' and nt.node_type = 'TERRITORY' and jtall.terr_id = jtu.terr_id and jtu.source_id = js.source_id and jtall.parent_territory_id = 1 and jtall.terr_id = jtall2.parent_territory_id(+) and jtall.template_flag <> 'Y' and jtall.escalation_territory_flag <> 'Y' and jtall.org_id = jtall2.org_id(+) group by jtall.name, nt.icon_name, nt.node_type, jtall.terr_id, jtall.parent_territory_id, js.source_id, jtall.rank UNION ALL select s.meaning label, nt.node_type node_type, '"' || s.source_id || '"' node_key, nt.icon_name icon_name, 'N' leaf_flag, 'TERRITORY' parent_node_type, '"'|| t.terr_id || '"' parent_node_key, 0 display_rank from jtf_nav_node_types_v nt, jtf_sources s, jtf_terr t where nt.root_key = 'JTF_TERR_ROOT' and nt.node_type = 'SOURCE' and t.terr_id = 1 UNION ALL select cat.name label, nt.node_type node_type, '"' || cat.terr_id || '"' node_key, 'aftreecl' icon_name, 'N' leaf_flag, 'ROOT' parent_node_type, nt.root_key parent_node_key, 0 display_rank from jtf_nav_node_types_v nt, jtf_terr cat where nt.root_key = 'JTF_TERR_ROOT' and nt.node_type = 'TERRITORY' and cat.terr_id = 1 ) ORDER BY DISPLAY_RANK, LABEL

View Text - HTML Formatted

SELECT "LABEL"
, "NODE_TYPE"
, "NODE_KEY"
, "ICON_NAME"
, "LEAF_FLAG"
, "PARENT_NODE_TYPE"
, "PARENT_NODE_KEY"
, "DISPLAY_RANK"
FROM ( SELECT JTALL.NAME || ' [' || JTALL.RANK || ']' LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || JTALL.TERR_ID || '"' NODE_KEY
, DECODE(COUNT(JTALL2.PARENT_TERRITORY_ID)
, 0
, 'TERRITOR'
, 'AFTREECL') ICON_NAME
, DECODE(COUNT(JTALL2.PARENT_TERRITORY_ID)
, 0
, 'Y'
, 'N') LEAF_FLAG
, 'TERRITORY' PARENT_NODE_TYPE
, '"'|| JTALL.PARENT_TERRITORY_ID || '"' PARENT_NODE_KEY
, JTALL.RANK DISPLAY_RANK
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_TERR JTALL
, JTF_SOURCES JS
, JTF_TERR_USGS JTU
, JTF_TERR JTALL2
WHERE NT.ROOT_KEY = 'JTF_TERR_ROOT'
AND NT.NODE_TYPE = 'TERRITORY'
AND JTALL.TERR_ID = JTU.TERR_ID
AND JTU.SOURCE_ID = JS.SOURCE_ID
AND JTALL.TERR_ID = JTALL2.PARENT_TERRITORY_ID(+)
AND JTALL.PARENT_TERRITORY_ID != 1
AND JTALL.TEMPLATE_FLAG <> 'Y'
AND JTALL.ESCALATION_TERRITORY_FLAG <> 'Y'
AND JTALL.ORG_ID = JTALL2.ORG_ID(+) GROUP BY JTALL.NAME
, NT.ICON_NAME
, NT.NODE_TYPE
, JTALL.TERR_ID
, JTALL.PARENT_TERRITORY_ID
, JTALL.RANK UNION ALL SELECT JTALL.NAME || ' [' || JTALL.RANK || ']' LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || JTALL.TERR_ID || '"' NODE_KEY
, DECODE(COUNT(JTALL2.PARENT_TERRITORY_ID)
, 0
, 'TERRITOR'
, 'AFTREECL') ICON_NAME
, DECODE(COUNT(JTALL2.PARENT_TERRITORY_ID)
, 0
, 'Y'
, 'N') LEAF_FLAG
, 'SOURCE' PARENT_NODE_TYPE
, '"'|| JS.SOURCE_ID || '"' PARENT_NODE_KEY
, JTALL.RANK DISPLAY_RANK
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_TERR JTALL
, JTF_TERR JTALL2
, JTF_SOURCES JS
, JTF_TERR_USGS JTU
WHERE NT.ROOT_KEY = 'JTF_TERR_ROOT'
AND NT.NODE_TYPE = 'TERRITORY'
AND JTALL.TERR_ID = JTU.TERR_ID
AND JTU.SOURCE_ID = JS.SOURCE_ID
AND JTALL.PARENT_TERRITORY_ID = 1
AND JTALL.TERR_ID = JTALL2.PARENT_TERRITORY_ID(+)
AND JTALL.TEMPLATE_FLAG <> 'Y'
AND JTALL.ESCALATION_TERRITORY_FLAG <> 'Y'
AND JTALL.ORG_ID = JTALL2.ORG_ID(+) GROUP BY JTALL.NAME
, NT.ICON_NAME
, NT.NODE_TYPE
, JTALL.TERR_ID
, JTALL.PARENT_TERRITORY_ID
, JS.SOURCE_ID
, JTALL.RANK UNION ALL SELECT S.MEANING LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || S.SOURCE_ID || '"' NODE_KEY
, NT.ICON_NAME ICON_NAME
, 'N' LEAF_FLAG
, 'TERRITORY' PARENT_NODE_TYPE
, '"'|| T.TERR_ID || '"' PARENT_NODE_KEY
, 0 DISPLAY_RANK
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_SOURCES S
, JTF_TERR T
WHERE NT.ROOT_KEY = 'JTF_TERR_ROOT'
AND NT.NODE_TYPE = 'SOURCE'
AND T.TERR_ID = 1 UNION ALL SELECT CAT.NAME LABEL
, NT.NODE_TYPE NODE_TYPE
, '"' || CAT.TERR_ID || '"' NODE_KEY
, 'AFTREECL' ICON_NAME
, 'N' LEAF_FLAG
, 'ROOT' PARENT_NODE_TYPE
, NT.ROOT_KEY PARENT_NODE_KEY
, 0 DISPLAY_RANK
FROM JTF_NAV_NODE_TYPES_V NT
, JTF_TERR CAT
WHERE NT.ROOT_KEY = 'JTF_TERR_ROOT'
AND NT.NODE_TYPE = 'TERRITORY'
AND CAT.TERR_ID = 1 ) ORDER BY DISPLAY_RANK
, LABEL