View HLPDTEST.STATEGPS.T_BPM_MODEL_NODE_PROPS_PIVOT_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Sat Jul 02 01:05 IST 2022 |
View Definition:
select "MODEL_NAME",
"MODEL_DESC",
"MODEL_VERSION",
"MODEL_ID",
"CATEGORY_NAME",
"CATEGORY_ID",
"NODE_ID",
"NODE_NAME",
"NODE_TYPE",
"CREATED_BY",
"MODEL_STATE",
"MODEL_STATUS",
"APPROVE_DATE",
"MODEL_CREATER",
"DOCUMENT_ATTACHED",
"SINGAPORE_TIME",
"TRADING_PARTY_ID",
"'Name'" Name,
"'Description'" Description,
"'System Name'" System_Name,
"'Activity Type'" Active_Type,
"'Module'" Module,
"'Function Name'" Function_Name,
"'Concurrent Program'" Concurrent_Program,
"'Transmission Mode'" Transmission_Mode,
"'SOX Control ID'" SOX_Control_ID,
"'SOX Control'" SOX_Control,
"'SOX Control Type'" SOX_Control_Type,
"'SOX Owner'" SOX_Owner,
"'Assignee Team Name'" Assignee_Team_Name,
"'Assigned Business Analyst'" Assigned_Business_Analyst,
"'Assigned Business Consultant'" Assigned_Business_Consultant,
"'Assigned System Analyst'" Assigned_System_Analyst,
"'Assigned Solution Architect'" Assigned_Solution_Architect,
"'User Department'" User_Department,
"'User Role'" User_Role,
"'Site'" Site,
"'Created By Name'" Created_By_Name,
"'Created Date'" Created_Date,
"'Last Updated By Name'" Last_Updated_By_Name,
"'Last Updated Date'" Last_Updated_Date
from
(
select "MODEL_NAME","MODEL_DESC","MODEL_VERSION","MODEL_ID","CATEGORY_NAME","CATEGORY_ID","NODE_ID","NODE_NAME","NODE_TYPE","CREATED_BY","MODEL_STATE","MODEL_STATUS","APPROVE_DATE","MODEL_CREATER","DOCUMENT_ATTACHED","SINGAPORE_TIME","TRADING_PARTY_ID","'Name'","'Description'","'System Name'","'Activity Type'","'Module'","'Function Name'","'Concurrent Program'","'Transmission Mode'","'SOX Control ID'","'SOX Control'","'SOX Control Type'","'SOX Owner'","'Assignee Team Name'","'Assigned Business Analyst'","'Assigned Business Consultant'","'Assigned System Analyst'","'Assigned Solution Architect'","'User Department'","'User Role'","'Site'","'Created By Name'","'Created Date'","'Last Updated By Name'","'Last Updated Date'"
from (select y.*, b.property_alias_name, b.prop_val
from (select M1.Model_name MODEL_name,
/* trim(decode(upper(substr(m1.description, 1, 2)),
'<H',
extractValue(xmltype(m1.description),
'/html/body/text()'),
m1.description)) Model_description,*/
m1.description Model_desc,
m1.model_status model_Version,
m1.model_id model_id,
Cat.category_name Category_name,
m1.category_id,
n1.node_id,
tgn.description Node_name,
n1.node_type,
M1.CREATED_BY,
tgws.state_name mODEL_STATE,
decode(m1.model_id,
max(m1.model_id)
over(partition by m1.model_name),
'Latest',
'Old') mODEL_STATUS,
/* nvl(tgws.state_name,
decode(m1.model_id,
max(m1.model_id)
over(partition by m1.model_name),
'Latest_REV',
'Old_REV')) Model_Status,*/
tbar.action_taken_date approve_date,
-- tbar.action_taken_date - interval '15' hour approval_Date,
(TU.FIRST_NAME || ' ' || TU.LAST_NAME) MODEL_CREATER,
(select decode(count(property_name), 0, 'N', 'Y')
from T_GPS_NODE_CLASS_PROPERTIES
where node_id = n1.node_id
and property_name = 'filename') document_attached,
m1.created_date Singapore_Time,
Cat.trading_party_id
--,
-- m1.created_date - interval '15' hour Pacific_Time
from t_gps_models m1,
t_gps_nodes n1,
T_CATEGORIES Cat,
T_BPM_APR_REQUESTS tbar,
t_gps_instances tgi,
T_GPS_WORKFLOW_STATES tgws,
T_USERS TU,
t_gps_nodetypes tgn
where m1.model_id = n1.model_id
and Cat.category_id = m1.category_id
and n1.node_type <> 'LINE'
and m1.model_id = tbar.model_id(+)
and tbar.instance_id = tgi.instance_id(+)
and tgi.current_state_id = tgws.state_id(+)
and tgi.instance_present(+) = 1
and Cat.trading_party_id= (select trading_party_id from t_trading_party ttp where
ttp.trading_party_name= 'ResMed'
)--127-- Resmed Party ID
--AND Cat.CATEGORY_ID = 1655 -- ResMed Models()
and tgn.nodetype = n1.node_type
AND M1.CREATED_BY = TU.USER_NAME(+)) y,
(select tgnt.nodetype,
tga.application_name,
tgnsc.class_name,
tgnsc.alias_name class_alias_name,
tgnscp.property_name property_name,
tgnscp.display,
tgnscp.alias_name property_alias_name,
tgnscp.tab_name,
tgnsc.class_id,
tgctg.tab_id,
--tgpo.tab_id,
tgpo.property_id prop_id,
d.node_id nod_id,
d.property_value prop_val
from t_gps_nodetypes tgnt,
t_gps_nodetype_states tgnts,
t_gps_nt_state_classes tgnsc,
t_gps_nt_st_class_props tgnscp,
t_gps_applications tga,
t_gps_class_tabgroups tgctg,
t_gps_class_tabgroup_childs tgctc,
t_gps_property_order tgpo,
T_GPS_NODE_CLASSES c,
T_GPS_NODE_CLASS_PROPERTIES d
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 tgnscp.display = 'true'
and tgnts.visible_flag = 'true'
and tgnscp.class_id = tgctg.class_id
and tgnscp.tab_name = tgctg.tab_name
and tgnscp.property_name = tgpo.property
and tgctg.tab_id = tgctc.childtab_id
and tgctg.tab_id = tgpo.tab_id
and tgnscp.display = 'true'
and tga.application_name = 'Business Process Mapping'
and c.class_name = 'eDynamo.classes.' || tgnsc.class_name
and d.property_name = tgnscp.property_name
and tgnscp.alias_name in
('Name',
'Description',
'System Name',
'Activity Type',
'Module',
'Function Name',
'Concurrent Program',
'Transmission Mode',
'SOX Control ID',
'SOX Control',
'SOX Control Type',
'SOX Owner',
'Assignee Team Name',
'Assigned Business Analyst',
'Assigned Business Consultant',
'Assigned System Analyst',
'Assigned Solution Architect',
'User Department',
'User Role',
'Site',
'Created By Name',
'Created Date',
'Last Updated By Name',
'Last Updated Date')
and c.node_id = d.node_id
and c.class_id = d.class_id) b
where y.node_id = b.nod_id
and y.node_type = b.nodetype)
PIVOT(max(prop_val) FOR property_alias_name IN('Name',
'Description',
'System Name',
'Activity Type',
'Module',
'Function Name',
'Concurrent Program',
'Transmission Mode',
'SOX Control ID',
'SOX Control',
'SOX Control Type',
'SOX Owner',
'Assignee Team Name',
'Assigned Business Analyst',
'Assigned Business Consultant',
'Assigned System Analyst',
'Assigned Solution Architect',
'User Department',
'User Role',
'Site',
'Created By Name',
'Created Date',
'Last Updated By Name',
'Last Updated Date'))
--order by model_name asc
)
Possibly Referenced Tables/Views:
T_BPM_APR_REQUESTS T_CATEGORIES T_GPS_APPLICATIONS T_GPS_CLASS_TABGROUP_CHILDS T_GPS_CLASS_TABGROUPS T_GPS_INSTANCES T_GPS_MODELS T_GPS_NODE_CLASS_PROPERTIES T_GPS_NODE_CLASSES T_GPS_NODES T_GPS_NODETYPE_STATES T_GPS_NODETYPES T_GPS_NT_ST_CLASS_PROPS T_GPS_NT_STATE_CLASSES T_GPS_PROPERTY_ORDER T_GPS_WORKFLOW_STATES T_TRADING_PARTY
![]() ![]() |