select APPLICATION_ID, APPLICATION_NAME, /* CATEGORY_ID, CATEGORY_NAME,*/ MODEL_ID, MODEL_NAME, MODEL_STATUS, NODETYPE_ID, NODE_TYPE, NODETYPE_NAME, NODE_NAME, NODE_INDEX, UNIQUE_ID, UNIQUE_ID_WITH_NODETYPE_NAME, PROPERTY_NAME, NO_OF_SETS, ARRAY_INDEX, PROPERTY_VALUE, ORIGINAL_TAB_NAME, CLASS_ALIAS_NAME, TAB_NAME, PROPERTY_ALIAS_NAME, ORIGINAL_PROPERTY_NAME, DISPLAY, NODE_ID, CLASS_ID, CLASS_NAME, PROPERTY_ID, NODE_TYPE_CONFIG_CLASS_ID, TAB_ORDER, PROPERTY_ORDER from ((SELECT alias_names.application_id, alias_names.application_name, /* t_categories.category_id, t_categories.category_name,*/ T_GPS_MODELS.MODEL_ID, T_GPS_MODELS.MODEL_NAME, T_GPS_MODELS.MODEL_STATUS, -- Model Version alias_names.NODETYPE_id, T_GPS_NODES.NODE_TYPE, alias_names.description Nodetype_NAME, T_GPS_NODES.NODE_NAME, T_GPS_NODES.Node_Index, -- Indexing same Node Types for differentiation --substr(T_GPS_NODES.node_name,instr(T_GPS_NODES.node_name, chr(10))) NODE_TYPE, /* decode(alias_names.tab_name,null,alias_names.class_alias_name,alias_names.tab_name)|| ' - ' || alias_names.property_alias_name, */ T_GPS_NODE_CLASS_PROPS_UNIQ_ID.PROPERTY_VALUE UNIQUE_ID, (alias_names.description || ' (' || T_GPS_NODE_CLASS_PROPS_UNIQ_ID.PROPERTY_VALUE || ')') UNIQUE_ID_WITH_NODETYPE_NAME, (decode(alias_names.tab_name, null, alias_names.class_alias_name, alias_names.tab_name) || ' - ' || alias_names.property_alias_name) PROPERTY_NAME, -- TODO In which case we will not have TAB NAME 1 No_Of_Sets, -- 2 If a node can have multiple value sets for Properties. 1 otherwise. 0 array_Index, -- Array Index when there are multiple value sets for Properties T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_VALUE, alias_names.tab_name original_tab_name, alias_names.class_alias_name, decode(alias_names.tab_name, null, alias_names.class_alias_name, alias_names.tab_name) tab_name, alias_names.property_alias_name, T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_NAME original_PROPERTY_NAME, alias_names.display, T_GPS_NODES.NODE_ID, T_GPS_NODE_CLASSES.CLASS_ID, T_GPS_NODE_CLASSES.CLASS_NAME, T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_ID, alias_names.class_id node_type_config_class_id, alias_names.tab_id tab_Order, alias_names.property_Order /* , T_GPS_NODE_CLASSES.Class_Name, 'eDynamo.classes.' || alias_names.class_name, T_GPS_NODE_CLASS_PROPERTIES.Property_Name, alias_names.property_name*/ /*, --alias_names.*, T_GPS_NODE_CLASSES.Class_Name, alias_names.class_name, T_GPS_NODE_CLASS_PROPERTIES.Property_Name, alias_names.property_name, alias_names.property_alias_name*/ FROM T_GPS_MODELS T_GPS_MODELS, /* T_CATEGORIES t_categories,*/ (select t.*, (sum(1) over(PARTITION BY t.model_id, t.node_type order by t.node_id)) Node_Index from t_gps_nodes t) T_GPS_NODES, T_GPS_NODE_CLASSES T_GPS_NODE_CLASSES, T_GPS_NODE_CLASS_PROPERTIES T_GPS_NODE_CLASS_PROPERTIES, T_GPS_NODE_CLASS_PROPERTIES T_GPS_NODE_CLASS_PROPS_UNIQ_ID, (select tgnsc_props.*, tgpo.property_id property_order from (select tgnts.application_id, tga.application_name, tgnt.nodetype_id, tgnt.nodetype, tgnt.description, tgnts.nodetype_state_id, tgnts.visible_flag, tgnsc.class_id, 'eDynamo.classes.' || tgnsc.class_name class_name, tgnsc.alias_name class_alias_name, tgnscp.property_id, tgnscp.property_name, tgnscp.alias_name property_alias_name, tgnscp.display, tgctg.tab_id, tgnscp.tab_name from t_gps_nt_state_classes tgnsc left outer join t_gps_nt_st_class_props tgnscp on tgnsc.class_id = tgnscp.class_id, t_gps_nodetypes tgnt, t_gps_nodetype_states tgnts, t_gps_applications tga, t_gps_class_tabgroups tgctg, t_gps_class_tabgroup_childs tgctc where tgnt.nodetype_id = tgnts.nodetype_id and tgnts.application_id = tga.application_id and tgnts.nodetype_state_id = tgnsc.nodetype_state_id and tgnscp.display = 'true' --LATER and tgnts.visible_flag = 'true' and tgnscp.class_id = tgctg.class_id and tgnscp.tab_name = tgctg.tab_name and tgctg.tab_id = tgctc.childtab_id and tga.application_name = 'Business Process Mapping' -- and tga.application_name = 'TPM Standard Application' -- and tgnscp.alias_name like '%Unit of Measure%' -- and tgnt.nodetype = 'TRI2_BPM_STANDARD_TRANSACTION' ) tgnsc_props left outer join t_gps_property_order tgpo on tgnsc_props.tab_id = tgpo.tab_id and tgnsc_props.property_name = tgpo.property -- and tgnsc_props.nodetype = 'TRI2_BPM_STANDARD_TRANSACTION' -- and tgnsc_props.application_name = 'Business Process Mapping' -- and tgnsc_props.class_alias_name ='Standard Transaction' -- and tgnsc_props.class_alias_name = 'Check ATP' --and tgnscp.display = 'true' --and tgnsc_props.property_alias_name like '%Unique Id%' -- and tgnsc_props.property_alias_name = 'Concurrent Program' /* order by tgnsc_props.nodetype_id, tgnsc_props.tab_id, tgpo.property_id*/ ) alias_names WHERE /*T_GPS_MODELS.model_type like 'HIERARCHY' and */ T_GPS_MODELS.MODEL_ID = T_GPS_NODES.MODEL_ID /*AND t_categories.category_id = T_GPS_MODELS.CATEGORY_ID*/ AND T_GPS_NODES.NODE_ID = T_GPS_NODE_CLASSES.NODE_ID --not to get deleted nodes AND (T_GPS_NODES.POSITION_X <> 0 OR T_GPS_NODES.POSITION_Y <> 0) AND T_GPS_NODE_CLASSES.CLASS_ID = T_GPS_NODE_CLASS_PROPERTIES.CLASS_ID AND T_GPS_NODE_CLASS_PROPERTIES.CLASS_ID = T_GPS_NODE_CLASS_PROPS_UNIQ_ID.CLASS_ID AND T_GPS_NODE_CLASS_PROPS_UNIQ_ID.PROPERTY_NAME = 'UNIQUE_IDENTIFICATION' and T_GPS_NODES.node_type = alias_names.NodeType --'TRT_FG' and T_GPS_NODE_CLASSES.Class_Name = alias_names.class_name and alias_names.property_name = T_GPS_NODE_CLASS_PROPERTIES.Property_Name -- (+) --todo -- and alias_names.display = 'true' --and alias_names.property_alias_name = 'Concurrent Program' /* and alias_names.application_name like 'Business Process Mapping' */ --63396, -- and T_GPS_MODELS.model_id in (63399, 63299) --and T_GPS_MODELS.MODEL_NAME = 'Prasad_1' -- and T_GPS_NODES.NODE_TYPE = 'TRI2_BPM_STANDARD_TRANSACTION' --and T_GPS_MODELS.model_status = 3 --and T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_NAME = 'UNIQUE_IDENTIFICATION' --and T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_NAME like '%DOC%' --and rownum < 100 --and alias_names.application_name = 'TPM Standard Application' /* and model_name in ('9763-12') and alias_names.property_alias_name like '%Item%' and node_type='TRT_FG'*/ /* and T_GPS_MODELS.MODEL_NAme in( 'TEST TPM','TEST TPM')*/ ) union (SELECT alias_names.application_id, alias_names.application_name, /* t_categories.category_id, t_categories.category_name,*/ T_GPS_MODELS.MODEL_ID, T_GPS_MODELS.MODEL_NAME, T_GPS_MODELS.MODEL_STATUS, alias_names.nodetype_id, T_GPS_NODES.NODE_TYPE, alias_names.description Nodetype_NAME, T_GPS_NODES.NODE_NAME, T_GPS_NODES.Node_Index, --substr(T_GPS_NODES.node_name,instr(T_GPS_NODES.node_name, chr(10))) NODE_TYPE, /* decode(alias_names.tab_name,null,alias_names.class_alias_name,alias_names.tab_name)|| ' - ' || alias_names.property_alias_name, */ T_GPS_NODE_CLASS_PROPS_UNIQ_ID.PROPERTY_VALUE UNIQUE_ID, (alias_names.description || ' (' || T_GPS_NODE_CLASS_PROPS_UNIQ_ID.PROPERTY_VALUE || ')') UNIQUE_ID_WITH_NODETYPE_NAME, (decode(alias_names.tab_name, null, alias_names.class_alias_name, alias_names.tab_name) || '[' || T_GPS_NODE_CLASSES.array_Index || ']-' || alias_names.property_alias_name) PROPERTY_NAME, 2 No_Of_Sets, T_GPS_NODE_CLASSES.array_Index, T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_VALUE, alias_names.tab_name original_tab_name, alias_names.class_alias_name, decode(alias_names.tab_name, null, alias_names.class_alias_name, alias_names.tab_name) tab_name, (alias_names.property_alias_name || '[' || T_GPS_NODE_CLASSES.array_Index || ']') property_alias_name, --property alias name T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_NAME original_PROPERTY_NAME, alias_names.display, T_GPS_NODES.NODE_ID, T_GPS_NODE_CLASSES.CLASS_ID, T_GPS_NODE_CLASSES.CLASS_NAME, T_GPS_NODE_CLASS_PROPERTIES.PROPERTY_ID, alias_names.class_id node_type_config_class_id, T_GPS_NODE_CLASSES.CLASS_ID tab_Order, T_GPS_NODE_CLASS_PROPERTIES.property_id property_Order /*, --alias_names.*, T_GPS_NODE_CLASSES.Class_Name, alias_names.class_name, T_GPS_NODE_CLASS_PROPERTIES.Property_Name, alias_names.property_name, alias_names.property_alias_name*/ FROM T_GPS_MODELS T_GPS_MODELS, /*T_CATEGORIES t_categories,*/ (select t.*, (sum(1) over(PARTITION BY t.model_id, t.node_type order by t.node_id)) Node_Index from t_gps_nodes t) T_GPS_NODES, (select t.*, sum(1) over(PARTITION BY t.node_id, t.class_name order by t.node_id, t.class_id) array_Index from T_GPS_NODE_CLASSES t) T_GPS_NODE_CLASSES, T_GPS_NODE_CLASS_PROPERTIES T_GPS_NODE_CLASS_PROPERTIES, T_GPS_NODE_CLASS_PROPERTIES T_GPS_NODE_CLASS_PROPS_UNIQ_ID, (select tgnts.application_id, tga.application_name, tgnt.nodetype_id, tgnt.nodetype, tgnt.description, 'eDynamo.classes.' || tgnsc.class_name class_name, tgnsc.alias_name class_alias_name, tgnscp.property_name, tgnscp.display, tgnscp.alias_name property_alias_name, tgnscp.tab_name, tgnsc.class_id --, -- tgctg.tab_id, --tgpo.property_id from t_gps_nodetypes tgnt, t_gps_nodetype_states tgnts, t_gps_nt_state_classes tgnsc, t_gps_nt_st_class_props tgnscp left outer join t_gps_class_tabgroups tgctg on tgnscp.class_id = tgctg.class_id, t_gps_applications tga where tgnt.nodetype_id = tgnts.nodetype_id and tgnts.application_id = tga.application_id and tgnts.nodetype_state_id = tgnsc.nodetype_state_id and tgnsc.class_id = tgnscp.class_id and tgnts.visible_flag = 'true' and tgnscp.display = 'true' -- and tgnscp.class_id = tgctg.class_id(+) and tgctg.class_id is null /*and tgnscp.property_name = tgpo.property and tgctg.tab_id = tgctc.childtab_id and tgctg.tab_id = tgpo.tab_id*/ and tga.application_name = 'Business Process Mapping') alias_names WHERE T_GPS_MODELS.MODEL_ID = T_GPS_NODES.MODEL_ID /* AND t_categories.category_id = T_GPS_MODELS.CATEGORY_ID*/ AND T_GPS_NODES.NODE_ID = T_GPS_NODE_CLASSES.NODE_ID --not to get deleted nodes AND (T_GPS_NODES.POSITION_X <> 0 OR T_GPS_NODES.POSITION_Y <> 0) AND T_GPS_NODE_CLASSES.CLASS_ID = T_GPS_NODE_CLASS_PROPERTIES.CLASS_ID AND T_GPS_NODE_CLASS_PROPERTIES.NODE_ID = T_GPS_NODE_CLASS_PROPS_UNIQ_ID.NODE_ID AND T_GPS_NODE_CLASS_PROPS_UNIQ_ID.PROPERTY_NAME = 'UNIQUE_IDENTIFICATION' and T_GPS_NODES.node_type = alias_names.NodeType --'TRT_FG' and T_GPS_NODE_CLASSES.Class_Name = alias_names.class_name and T_GPS_NODE_CLASS_PROPERTIES.Property_Name = alias_names.property_name --not to get Upload property in the Document Drive Tab and T_GPS_NODE_CLASS_PROPERTIES.Property_Name <> 'documentData' -- and alias_names.application_name like 'Business Process Mapping' -- and T_GPS_MODELS.model_id in (63399, 63299) --63396, -- and T_GPS_MODELS.model_id in (63432,61770 ) -- and rownum < 10 -- and T_GPS_MODELS.MODEL_NAME = 'Prasad_1' --and T_GPS_NODES.NODE_TYPE = 'TRI2_BPM_STANDARD_TRANSACTION' ) ) all_node_props --where model_id in (63433) -- and property_id= 20791465 --and property_value= 'END000000183' --application_name like 'Business Process Mapping' --and MODEL_NAME in ('Prasad_1') --application_id = 5158 -- model_id in (63432, 63433); -- (63399, 63299) -- and model_status in (48, 49) --(1,4) -- and NODE_TYPE = 'TRI2_BPM_STANDARD_TRANSACTION' -- and model_status = 4 --63396, --and MODEL_NAME = '3.0.0.0.0 - ResMed F2D Business Process' -- and property_name = 'Main-Unique Id' --and rownum < 10 /*order by model_name, NODE_ID, NODE_TYPE, Node_Index, model_status, No_Of_Sets, tab_Order, property_Order */ order by model_name, model_status desc, NODE_ID, UNIQUE_ID, tab_Order, property_Order, No_Of_Sets asc