View HLPDTEST.STATEGPS.T_USER_ROLES_APPLICATIONS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Tue May 24 01:05 IST 2022 |
View Definition:
SELECT T_USERS.USER_ID,T_USERS.USER_NAME,T_ROLES.ROLE_NAME,T_GPS_APPLICATIONS.APPLICATION_NAME,T_USERS.TRADING_PARTY_ID,
T_GPS_MENUCONFIG.NAME WORKFLOW_TYPE,'BUILD/VIEW' OBJECT_TYPE
FROM TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_APP_PROPERTIES T_GPS_APP_PROPERTIES,
STATEGPS.T_GPS_MENUCONFIG T_GPS_MENUCONFIG,
STATEGPS.T_GPS_ROLE_MENUCONFIG T_GPS_ROLE_MENUCONFIG
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_APP_PROPERTIES.APPLICATION_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID = T_GPS_ROLE_MENUCONFIG.ROLE_APP_ID
AND T_GPS_MENUCONFIG.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_MENUCONFIG.MENU_ID = T_GPS_MENUCONFIG.MENU_ID
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' OR T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL)
AND T_GPS_APP_PROPERTIES.PROPERTY_NAME = 'WORKFLOW DISABLED'
AND T_GPS_APP_PROPERTIES.PROPERTY_VALUE = 'TRUE'
AND (T_GPS_MENUCONFIG.NAME = 'Build' OR T_GPS_MENUCONFIG.NAME = 'View')
AND T_GPS_MENUCONFIG.MODE_ID = 0
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
UNION
SELECT T_USERS.USER_ID,
T_USERS.USER_NAME,
T_ROLES.ROLE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_USERS.TRADING_PARTY_ID,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME WORKFLOW_TYPE,
'WORKFLOW' OBJECT_TYPE
FROM TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_GPS_ROLE_WORKFLOW_TYPES T_GPS_ROLE_WORKFLOW_TYPES
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_WORKFLOW_TYPES.ROLE_APP_ID =
T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID
AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID =
T_GPS_ROLE_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' OR
T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL)
AND (T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' OR
T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG IS NULL)
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
UNION
SELECT T_USERS.USER_ID,
T_USERS.USER_NAME,
T_ROLES.ROLE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_USERS.TRADING_PARTY_ID,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME || ' / ' || t_gps_workflow_states.state_name WORKFLOW_TYPE,
'WORKFLOW/STATES' OBJECT_TYPE
FROM TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_GPS_ROLE_WORKFLOW_TYPES T_GPS_ROLE_WORKFLOW_TYPES,
STATEGPS.t_gps_role_states t_gps_role_states,
STATEGPS.t_gps_workflow_states t_gps_workflow_states
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_WORKFLOW_TYPES.ROLE_APP_ID =
T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID
and t_gps_workflow_types.workflow_type_id = t_gps_workflow_states.workflow_type_id
and t_gps_role_states.from_state_id = t_gps_workflow_states.state_id
and t_gps_role_states.role_workflow_type_id = t_gps_role_workflow_types.role_workflow_type_id
AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID =
T_GPS_ROLE_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' OR
T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL)
AND (T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' OR
T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG IS NULL)
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
UNION
SELECT T_USERS.USER_ID,
T_USERS.USER_NAME,
T_ROLES.ROLE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_USERS.TRADING_PARTY_ID,
T_GPS_VIEWER_INSTANCES_SET.SET_NAME || ' / ' ||
T_GPS_VIEWER_INSTANCES.FORM_NAME REPORT_NAME,
'VIEWER SET / REPORT' OBJECT_TYPE
FROM TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_VIEWER_INSTANCES_SET T_GPS_VIEWER_INSTANCES_SET,
STATEGPS.T_GPS_VIEWER_INSTANCES T_GPS_VIEWER_INSTANCES,
STATEGPS.T_GPS_ROLE_VIEWER_INSTANCES T_GPS_ROLE_VIEWER_INSTANCES,
STATEGPS.T_GPS_ROLE_VIEWER_SETS T_GPS_ROLE_VIEWER_SETS
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_VIEWER_INSTANCES_SET.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_VIEWER_INSTANCES.SET_ID = T_GPS_VIEWER_INSTANCES_SET.SET_ID
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_VIEWER_SETS.ROLE_APP_ID =
T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID
AND T_GPS_ROLE_VIEWER_SETS.SET_ID = T_GPS_VIEWER_INSTANCES_SET.SET_ID
AND T_GPS_ROLE_VIEWER_INSTANCES.ROLE_VIEWERSET_ID =
T_GPS_ROLE_VIEWER_SETS.ROLE_VIEWERSET_ID
AND T_GPS_ROLE_VIEWER_INSTANCES.VIEWER_INSTANCE_ID =
T_GPS_VIEWER_INSTANCES.VIEWER_INSTANCES_ID
AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true'
AND T_GPS_VIEWER_INSTANCES.VISIBLE_FLAG = 'true'
AND T_GPS_VIEWER_INSTANCES_SET.VISIBLE_FLAG = 'true'
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
UNION
SELECT T_USERS.USER_ID,
T_USERS.USER_NAME,
T_ROLES.ROLE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_USERS.TRADING_PARTY_ID,
T_GPS_CUSTOM_SETS.CUSTOM_SET_NAME CUSTOM_SET_NAME,
'CUSTOM SET' OBJECT_TYPE
FROM TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_CUSTOM_SETS T_GPS_CUSTOM_SETS,
STATEGPS.T_GPS_ROLE_CUSTOM_SETS T_GPS_ROLE_CUSTOM_SETS
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_CUSTOM_SETS.ROLE_APP_ID =
T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID
AND T_GPS_CUSTOM_SETS.CUSTOM_SET_ID =
T_GPS_ROLE_CUSTOM_SETS.CUSTOM_SET_ID
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' OR
T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL)
AND (T_GPS_CUSTOM_SETS.VISIBLE_FLAG = 'true' OR
T_GPS_CUSTOM_SETS.VISIBLE_FLAG IS NULL)
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
UNION
SELECT T_USERS.USER_ID,
T_USERS.USER_NAME,
T_ROLES.ROLE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_USERS.TRADING_PARTY_ID,
T_CATEGORIES.CATEGORY_NAME CATEGORY_NAME,
'CATEGORY NAME' OBJECT_TYPE
FROM TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_CATEGORIES T_CATEGORIES,
STATEGPS.T_GPS_ROLE_CATEGORIES T_GPS_ROLE_CATEGORIES
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID =
T_GPS_ROLE_CATEGORIES.ROLE_APP_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_CATEGORIES.CATEGORY_ID = T_GPS_ROLE_CATEGORIES.CATEGORY_ID
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' OR
T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL)
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
UNION
/*
* This below select query is to get ACL(access control list) including NODETYPES, so that to provide access depend on
* whether user have nodetype access or not. This requirement arise when nodeFrameworks are being displayed nodetype specific.
*
*/
SELECT T_USERS.USER_ID,
T_USERS.USER_NAME,
T_ROLES.ROLE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_USERS.TRADING_PARTY_ID,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME || ' / ' || t_gps_workflow_states.state_name || ' / ' || T_GPS_NODETYPES.Nodetype WORKFLOW_TYPE,
'WORKFLOW/STATE/NODETYPES' OBJECT_TYPE
FROM
TAS.T_USERS T_USERS,
TAS.T_USER_ROLES T_USER_ROLES,
TAS.T_ROLES T_ROLES,
STATEGPS.T_GPS_ROLES T_GPS_ROLES,
STATEGPS.T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_APPLICATIONS,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_GPS_ROLE_WORKFLOW_TYPES T_GPS_ROLE_WORKFLOW_TYPES,
STATEGPS.t_gps_role_states t_gps_role_states,
STATEGPS.t_gps_workflow_states t_gps_workflow_states,
STATEGPS.T_GPS_NODETYPES T_GPS_NODETYPES,
STATEGPS.T_GPS_ROLE_NODETYPE_STATES T_GPS_ROLE_NODETYPE_STATES,
STATEGPS.T_GPS_NODETYPE_STATES T_GPS_NODETYPE_STATES
WHERE T_USERS.USER_ID = T_USER_ROLES.USER_ID
AND T_USER_ROLES.ROLE_ID = T_ROLES.ROLE_ID
AND T_ROLES.ROLE_NAME = T_GPS_ROLES.ROLE_NAME
AND T_GPS_ROLE_APPLICATIONS.ROLE_ID = T_GPS_ROLES.ROLE_ID
AND T_GPS_ROLE_APPLICATIONS.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID
AND T_GPS_ROLE_WORKFLOW_TYPES.ROLE_APP_ID = T_GPS_ROLE_APPLICATIONS.ROLE_APP_ID
and t_gps_workflow_types.workflow_type_id = t_gps_workflow_states.workflow_type_id
and t_gps_role_states.from_state_id = t_gps_workflow_states.state_id
and t_gps_role_states.role_workflow_type_id = t_gps_role_workflow_types.role_workflow_type_id
and t_gps_role_states.role_state_id = t_gps_role_nodetype_states.role_state_id
AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID = T_GPS_ROLE_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND T_GPS_NODETYPE_STATES.State_Id = t_gps_workflow_states.state_id
AND T_GPS_NODETYPE_STATES.Nodetype_Id = T_GPS_NODETYPES.Nodetype_Id
AND T_GPS_ROLE_NODETYPE_STATES.Nodetype_Id = T_GPS_NODETYPE_STATES.Nodetype_Id
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true' OR
T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL)
AND (T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true' OR
T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG IS NULL)
AND (T_USERS.EFFECTIVE_END_DATE IS NULL OR (trunc(SYSDATE) <= trunc(T_USERS.EFFECTIVE_END_DATE)))
ORDER BY 2,3,4,6
Possibly Referenced Tables/Views:
T_CATEGORIES T_GPS_APP_PROPERTIES T_GPS_APPLICATIONS T_GPS_CUSTOM_SETS T_GPS_MENUCONFIG T_GPS_NODETYPE_STATES T_GPS_NODETYPES T_GPS_ROLE_APPLICATIONS T_GPS_ROLE_CATEGORIES T_GPS_ROLE_CUSTOM_SETS T_GPS_ROLE_MENUCONFIG T_GPS_ROLE_NODETYPE_STATES T_GPS_ROLE_STATES T_GPS_ROLE_VIEWER_INSTANCES T_GPS_ROLE_VIEWER_SETS T_GPS_ROLE_WORKFLOW_TYPES T_GPS_ROLES T_GPS_VIEWER_INSTANCES T_GPS_VIEWER_INSTANCES_SET T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_TYPES
![]() ![]() |