View HLPDTEST.STATEGPS.T_BPM_MODEL_NODE_PROPS_PIVOT_V Generated by
SchemaSpy
Legend: SourceForge.net
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
MODEL_NAME varchar2 255
MODEL_DESC varchar2 4000  √  null
MODEL_VERSION number 4  √  null
MODEL_ID number 22
T_GPS_MODELS.MODEL_ID Implied Constraint R
CATEGORY_NAME varchar2 255  √  null
CATEGORY_ID number 22
T_CATEGORIES.CATEGORY_ID Implied Constraint R
NODE_ID number 22
T_GPS_NODES.NODE_ID Implied Constraint R
NODE_NAME varchar2 1000  √  null
NODE_TYPE varchar2 255  √  null
CREATED_BY varchar2 255  √  null
MODEL_STATE varchar2 255  √  null
MODEL_STATUS varchar2 6  √  null
APPROVE_DATE timestamp(6) 11,6  √  null
MODEL_CREATER varchar2 201  √  null
DOCUMENT_ATTACHED varchar2 1  √  null
SINGAPORE_TIME date 7  √  null
TRADING_PARTY_ID number 22  √  null
T_TRADING_PARTY.TRADING_PARTY_ID Implied Constraint R
NAME varchar2 4000  √  null
DESCRIPTION varchar2 4000  √  null
SYSTEM_NAME varchar2 4000  √  null
ACTIVE_TYPE varchar2 4000  √  null
MODULE varchar2 4000  √  null
FUNCTION_NAME varchar2 4000  √  null
CONCURRENT_PROGRAM varchar2 4000  √  null
TRANSMISSION_MODE varchar2 4000  √  null
SOX_CONTROL_ID varchar2 4000  √  null
SOX_CONTROL varchar2 4000  √  null
SOX_CONTROL_TYPE varchar2 4000  √  null
SOX_OWNER varchar2 4000  √  null
ASSIGNEE_TEAM_NAME varchar2 4000  √  null
ASSIGNED_BUSINESS_ANALYST varchar2 4000  √  null
ASSIGNED_BUSINESS_CONSULTANT varchar2 4000  √  null
ASSIGNED_SYSTEM_ANALYST varchar2 4000  √  null
ASSIGNED_SOLUTION_ARCHITECT varchar2 4000  √  null
USER_DEPARTMENT varchar2 4000  √  null
USER_ROLE varchar2 4000  √  null
SITE varchar2 4000  √  null
CREATED_BY_NAME varchar2 4000  √  null
CREATED_DATE varchar2 4000  √  null
LAST_UPDATED_BY_NAME varchar2 4000  √  null
LAST_UPDATED_DATE varchar2 4000  √  null

Analyzed at Tue Sep 22 01:05 IST 2020

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:


Close relationships: