VIEW: APPS.CN_COMP_GROUPS_NAV

Source

View Text - Preformatted

SELECT substrb(cg.name,1,78) label, nt.node_type node_type, cg.comp_group_id node_key, nt.icon_name icon_name, 'N' leaf_flag, 'COMP_GROUP' parent_node_type, cgh.parent_comp_group_id parent_node_key, trunc(cgh.start_date_active) child_start_date, trunc(cgh.end_date_active) child_end_date, trunc(cgh2.start_date_active) parent_start_date, trunc(cgh2.end_date_active) parent_end_date from cn_comp_groups cg, cn_nav_node_types nt, cn_comp_group_hier cgh, cn_comp_group_hier cgh2 WHERE nt.node_type = 'COMP_GROUP' and nt.root_key = 'CN_COMP_GROUP_ROOT' and cg.comp_group_id = cgh.comp_group_id and cgh.parent_comp_group_id = cgh2.comp_group_id(+) UNION ALL select substrb( s.name,1,78) label, nt.node_type node_type, s.salesrep_id node_key, nt.icon_name icon_name, 'Y' leaf_flag, 'COMP_GROUP' parent_node_type, gm.group_id parent_node_key, trunc(cgh.start_date_active) child_start_date, trunc(cgh.end_date_active) child_end_date, trunc(cgh.start_date_active) parent_start_date, trunc(cgh.end_date_active) parent_end_date from cn_salesreps s, jtf_rs_group_members gm, cn_nav_node_types nt, cn_comp_groups cgh where s.resource_id = gm.resource_id and nt.node_type = 'SALESREP' and nt.root_key = 'CN_COMP_GROUP_ROOT' and gm.group_id = cgh.comp_group_id and gm.delete_flag = 'N'

View Text - HTML Formatted

SELECT SUBSTRB(CG.NAME
, 1
, 78) LABEL
, NT.NODE_TYPE NODE_TYPE
, CG.COMP_GROUP_ID NODE_KEY
, NT.ICON_NAME ICON_NAME
, 'N' LEAF_FLAG
, 'COMP_GROUP' PARENT_NODE_TYPE
, CGH.PARENT_COMP_GROUP_ID PARENT_NODE_KEY
, TRUNC(CGH.START_DATE_ACTIVE) CHILD_START_DATE
, TRUNC(CGH.END_DATE_ACTIVE) CHILD_END_DATE
, TRUNC(CGH2.START_DATE_ACTIVE) PARENT_START_DATE
, TRUNC(CGH2.END_DATE_ACTIVE) PARENT_END_DATE
FROM CN_COMP_GROUPS CG
, CN_NAV_NODE_TYPES NT
, CN_COMP_GROUP_HIER CGH
, CN_COMP_GROUP_HIER CGH2
WHERE NT.NODE_TYPE = 'COMP_GROUP'
AND NT.ROOT_KEY = 'CN_COMP_GROUP_ROOT'
AND CG.COMP_GROUP_ID = CGH.COMP_GROUP_ID
AND CGH.PARENT_COMP_GROUP_ID = CGH2.COMP_GROUP_ID(+) UNION ALL SELECT SUBSTRB( S.NAME
, 1
, 78) LABEL
, NT.NODE_TYPE NODE_TYPE
, S.SALESREP_ID NODE_KEY
, NT.ICON_NAME ICON_NAME
, 'Y' LEAF_FLAG
, 'COMP_GROUP' PARENT_NODE_TYPE
, GM.GROUP_ID PARENT_NODE_KEY
, TRUNC(CGH.START_DATE_ACTIVE) CHILD_START_DATE
, TRUNC(CGH.END_DATE_ACTIVE) CHILD_END_DATE
, TRUNC(CGH.START_DATE_ACTIVE) PARENT_START_DATE
, TRUNC(CGH.END_DATE_ACTIVE) PARENT_END_DATE
FROM CN_SALESREPS S
, JTF_RS_GROUP_MEMBERS GM
, CN_NAV_NODE_TYPES NT
, CN_COMP_GROUPS CGH
WHERE S.RESOURCE_ID = GM.RESOURCE_ID
AND NT.NODE_TYPE = 'SALESREP'
AND NT.ROOT_KEY = 'CN_COMP_GROUP_ROOT'
AND GM.GROUP_ID = CGH.COMP_GROUP_ID
AND GM.DELETE_FLAG = 'N'