View HLPDTEST.STATEGPS.T_ALL_BPM_LATEST_VER_MODELS_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
TRADING_PARTY_ID number 22  √  null
T_TRADING_PARTY.TRADING_PARTY_ID Implied Constraint R
CATEGORY_ID number 22
T_CATEGORIES.CATEGORY_ID Implied Constraint R
CATEGORY_NAME varchar2 255  √  null
LATEST_VER_MODEL_ID number 22  √  null
MODEL_NAME varchar2 4000  √  null
DESCRIPTION varchar2 4000  √  null
LATEST_VERSION number 4  √  null
LATEST_REQUESTED_VERSION number 4  √  null
IS_LATEST_VERSION varchar2 3  √  null
T_BPM_APR_REQUEST_ID number 10  √  null
T_BPM_APR_REQUESTS.T_BPM_APR_REQUEST_ID Implied Constraint R
REQUESTED_MODEL_INSTANCE_ID number 10  √  null
REQUEST_NUMBER varchar2 100  √  null
REQUESTED_MODEL_ID number 10  √  null
REASON_FOR_REQUEST varchar2 2000  √  null
REQUESTED_BY varchar2 100  √  null
REQUESTED_USER_FULL_NAME varchar2 302  √  null
REQUESTED_DATE timestamp(6) 11,6  √  null
ACTION_TAKEN_BY varchar2 100  √  null
ACTION_TAKEN_USER_FULL_NAME varchar2 302  √  null
ACTION_TAKEN_DATE timestamp(6) 11,6  √  null
APPROVER_COMMENTS varchar2 500  √  null
INSTANCE_ID number 22  √  null
T_GPS_WORKFLOW_INSTANCES.INSTANCE_ID Implied Constraint R
CURRENT_STATE varchar2 255  √  null

Analyzed at Sun Oct 18 01:05 IST 2020

View Definition:
SELECT C.TRADING_PARTY_ID, C.CATEGORY_ID, C.CATEGORY_NAME, M.MODEL_ID Latest_Ver_Model_Id, M.MODEL_NAME, --M.MODEL_TYPE, --M.DESCRIPTION, TGM.DESCRIPTION, TGM.MODEL_STATUS Latest_Version, tgm_req.MODEL_STATUS Latest_Requested_Version, decode(TGM.MODEL_STATUS, tgm_req.MODEL_STATUS, 'Yes', 'No') Is_Latest_Version, --TGM.*, --T_BPM_APR_REQUESTS.*, --need to select each column seperately so that model_id can be given an alias as "Requested model Id T_BPM_APR_REQUESTS.T_BPM_APR_REQUEST_ID, T_BPM_APR_REQUESTS.INSTANCE_ID Requested_Model_Instance_id, T_BPM_APR_REQUESTS.REQUEST_NUMBER, T_BPM_APR_REQUESTS.MODEL_ID Requested_Model_Id, T_BPM_APR_REQUESTS.REASON_FOR_REQUEST, T_BPM_APR_REQUESTS.REQUESTED_BY, (SELECT CASE WHEN T_USERS.FIRST_NAME IS NOT NULL THEN T_USERS.FIRST_NAME || ' ' ELSE '' END || CASE WHEN T_USERS.MIDDLE_NAME IS NOT NULL THEN T_USERS.MIDDLE_NAME || ' ' ELSE '' END || COALESCE(T_USERS.LAST_NAME, '') from t_users where t_users.user_name=T_BPM_APR_REQUESTS.REQUESTED_BY) Requested_User_Full_Name, T_BPM_APR_REQUESTS.REQUESTED_DATE, T_BPM_APR_REQUESTS.ACTION_TAKEN_BY, (SELECT CASE WHEN T_USERS.FIRST_NAME IS NOT NULL THEN T_USERS.FIRST_NAME || ' ' ELSE '' END || CASE WHEN T_USERS.MIDDLE_NAME IS NOT NULL THEN T_USERS.MIDDLE_NAME || ' ' ELSE '' END || COALESCE(T_USERS.LAST_NAME, '') from t_users where t_users.user_name=T_BPM_APR_REQUESTS.ACTION_TAKEN_BY) Action_Taken_User_Full_Name, T_BPM_APR_REQUESTS.ACTION_TAKEN_DATE, T_BPM_APR_REQUESTS.APPROVER_COMMENTS, T_GPS_INSTANCES.instance_id, T_GPS_WORKFLOW_STATES.STATE_NAME CURRENT_STATE --, N.*, NT.*, NS.* FROM T_GPS_APPLICATIONS APP, T_CATEGORIES C, T_GPS_MASTER_MODELS M, T_GPS_MODELS TGM LEFT OUTER JOIN (t_gps_models tgm_req JOIN(T_BPM_APR_REQUESTS T_BPM_APR_REQUESTS LEFT OUTER JOIN(T_GPS_INSTANCES T_GPS_INSTANCES JOIN T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES ON T_GPS_INSTANCES.CURRENT_STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID AND T_GPS_INSTANCES.ACTIVE = 1 AND T_GPS_INSTANCES.instance_present = 1 ) ON (T_BPM_APR_REQUESTS.instance_id = T_GPS_INSTANCES.instance_id )) ON tgm_req.model_id = T_BPM_APR_REQUESTS.model_id and tgm_req.NODE_ID IS NULL -- to filter drawn models -- and tgm_req.model_name = '2.0.0.0.0 - Sales Order Process' AND NOT EXISTS (SELECT 1 FROM T_BPM_APR_REQUESTS bbar, t_gps_models ttgm WHERE ttgm.model_id = bbar.model_id AND ttgm.model_name = tgm_req.model_name AND ttgm.category_id = tgm_req.category_id AND ttgm.model_status > tgm_req.model_status)) ON tgm_req.model_name = TGM.MODEL_NAME AND tgm_req.category_id = tgm.category_id WHERE M.CATEGORY_ID = C.CATEGORY_ID AND APP.APPLICATION_NAME = 'Business Process Mapping' AND TGM.MODEL_ID = M.MODEL_ID AND TGM.NODE_ID IS NULL AND M.MODEL_NAME NOT LIKE 'Scenario%' -- and tgm.model_id = 64238 --and tgm.model_name like '%2.0.0.0.0 - Sales Order Process%' --163822.0 -- and nt.nodetype_id in (350,362) --AND C.CATEGORY_NAME = 'H2R' --AND C.trading_party_id = 127 -- AND U.User_Name = 'chandolu.srilakshmi@gmail.com' ORDER BY C.CATEGORY_NAME, M.MODEL_NAME
 
Possibly Referenced Tables/Views:


Close relationships: