View HLPDTEST.STATEGPS.T_GPS_MODEL_NODE_PROPS_COMP_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Mar 01 01:05 IST 2021 |
View Definition:
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
Possibly Referenced Tables/Views:
![]() ![]() |