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