REM +======================================================================+ REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA| REM | All rights reserved. | REM +======================================================================+ REM REM NAME REM wfengv.sql - WorkFlow ENGine Views REM +======================================================================+ WHENEVER SQLERROR EXIT FAILURE ROLLBACK; SET VERIFY OFF DEFINE hdr = "$Header: wfengv.sql 26.3 2000/07/24 23:44:14 jinliu ship $" /* ** WF_ACTIVITIES */ create or replace force view WF_ACTIVITIES_VL ( ROW_ID, ITEM_TYPE, NAME, VERSION, TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, END_DATE, FUNCTION, RESULT_TYPE, COST, READ_ROLE, WRITE_ROLE, EXECUTE_ROLE, ICON_NAME, MESSAGE, ERROR_PROCESS, RUNNABLE_FLAG, ERROR_ITEM_TYPE, FUNCTION_TYPE, EVENT_NAME, DIRECTION, DISPLAY_NAME, DESCRIPTION ) as select /* $Header: wfengv.sql 26.3 2000/07/24 23:44:14 jinliu ship $ */ B.ROWID ROW_ID, B.ITEM_TYPE, B.NAME, B.VERSION, B.TYPE, B.RERUN, B.EXPAND_ROLE, B.PROTECT_LEVEL, B.CUSTOM_LEVEL, B.BEGIN_DATE, B.END_DATE, B.FUNCTION, B.RESULT_TYPE, B.COST, B.READ_ROLE, B.WRITE_ROLE, B.EXECUTE_ROLE, B.ICON_NAME, B.MESSAGE, B.ERROR_PROCESS, B.RUNNABLE_FLAG, B.ERROR_ITEM_TYPE, B.FUNCTION_TYPE, B.EVENT_NAME, B.DIRECTION, T.DISPLAY_NAME, T.DESCRIPTION from WF_ACTIVITIES B, WF_ACTIVITIES_TL T where B.ITEM_TYPE = T.ITEM_TYPE and B.NAME = T.NAME and B.VERSION = T.VERSION and T.LANGUAGE = userenv('LANG'); /* ** WF_ACTIVITY_ATTRIBUTES */ create or replace force view WF_ACTIVITY_ATTRIBUTES_VL ( ROW_ID, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, ACTIVITY_VERSION, NAME, SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL, SUBTYPE, FORMAT, TEXT_DEFAULT, NUMBER_DEFAULT, DATE_DEFAULT, DISPLAY_NAME, DESCRIPTION ) as select /* $Header: wfengv.sql 26.3 2000/07/24 23:44:14 jinliu ship $ */ B.ROWID ROW_ID, B.ACTIVITY_ITEM_TYPE, B.ACTIVITY_NAME, B.ACTIVITY_VERSION, B.NAME, B.SEQUENCE, B.TYPE, B.VALUE_TYPE, B.PROTECT_LEVEL, B.CUSTOM_LEVEL, B.SUBTYPE, B.FORMAT, B.TEXT_DEFAULT, B.NUMBER_DEFAULT, B.DATE_DEFAULT, T.DISPLAY_NAME, T.DESCRIPTION from WF_ACTIVITY_ATTRIBUTES B, WF_ACTIVITY_ATTRIBUTES_TL T where B.ACTIVITY_ITEM_TYPE = T.ACTIVITY_ITEM_TYPE and B.ACTIVITY_NAME = T.ACTIVITY_NAME and B.ACTIVITY_VERSION = T.ACTIVITY_VERSION and B.NAME = T.NAME and T.LANGUAGE = userenv('LANG'); /* ** WF_ITEM_ACTIVITY_STATUSES_V */ create or replace force view WF_ITEM_ACTIVITY_STATUSES_V ( row_id, source, item_type, item_type_display_name, item_type_description, item_key, user_key, item_begin_date, item_end_date, activity_id, activity_label, activity_name, activity_display_name, activity_description, activity_type_code, activity_type_display_name, execution_time, activity_begin_date, activity_end_date, activity_status_code, activity_status_display_name, activity_result_code, activity_result_display_name, assigned_user, assigned_user_display_name, notification_id, outbound_queue_id, error_name, error_message, error_stack ) as select /* &&hdr */ IAS.ROWID, 'R', IAS.ITEM_TYPE, IT.DISPLAY_NAME, IT.DESCRIPTION, IAS.ITEM_KEY, I.USER_KEY, I.BEGIN_DATE, I.END_DATE, IAS.PROCESS_ACTIVITY, PA.INSTANCE_LABEL, A.NAME, A.DISPLAY_NAME, A.DESCRIPTION, A.TYPE, L_AT.MEANING, IAS.EXECUTION_TIME, IAS.BEGIN_DATE, IAS.END_DATE, IAS.ACTIVITY_STATUS, L_AS.MEANING, IAS.ACTIVITY_RESULT_CODE, wf_core.activity_result(A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE), IAS.ASSIGNED_USER, wf_directory.getroledisplayname(IAS.ASSIGNED_USER), IAS.NOTIFICATION_ID, IAS.OUTBOUND_QUEUE_ID, IAS.ERROR_NAME, IAS.ERROR_MESSAGE, IAS.ERROR_STACK from WF_LOOKUPS L_AT, WF_LOOKUPS L_AS, WF_ACTIVITIES_VL A, WF_PROCESS_ACTIVITIES PA, WF_ITEM_TYPES_VL IT, WF_ITEMS I, WF_ITEM_ACTIVITY_STATUSES IAS where IAS.ITEM_TYPE = I.ITEM_TYPE and IAS.ITEM_KEY = I.ITEM_KEY and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE) and I.ITEM_TYPE = IT.NAME and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID and PA.ACTIVITY_NAME = A.NAME and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE and L_AT.LOOKUP_TYPE = 'WFENG_ACTIVITY_TYPE' and L_AT.LOOKUP_CODE = A.TYPE and L_AS.LOOKUP_TYPE = 'WFENG_STATUS' and L_AS.LOOKUP_CODE = IAS.ACTIVITY_STATUS union all select IAS.ROWID, 'H', IAS.ITEM_TYPE, IT.DISPLAY_NAME, IT.DESCRIPTION, IAS.ITEM_KEY, I.USER_KEY, I.BEGIN_DATE, I.END_DATE, IAS.PROCESS_ACTIVITY, PA.INSTANCE_LABEL, A.NAME, A.DISPLAY_NAME, A.DESCRIPTION, A.TYPE, L_AT.MEANING, IAS.EXECUTION_TIME, IAS.BEGIN_DATE, IAS.END_DATE, IAS.ACTIVITY_STATUS, L_AS.MEANING, IAS.ACTIVITY_RESULT_CODE, wf_core.activity_result(A.RESULT_TYPE,IAS.ACTIVITY_RESULT_CODE), IAS.ASSIGNED_USER, wf_directory.getroledisplayname(IAS.ASSIGNED_USER), IAS.NOTIFICATION_ID, IAS.OUTBOUND_QUEUE_ID, IAS.ERROR_NAME, IAS.ERROR_MESSAGE, IAS.ERROR_STACK from WF_LOOKUPS L_AT, WF_LOOKUPS L_AS, WF_ACTIVITIES_VL A, WF_PROCESS_ACTIVITIES PA, WF_ITEM_TYPES_VL IT, WF_ITEMS I, WF_ITEM_ACTIVITY_STATUSES_H IAS where IAS.ITEM_TYPE = I.ITEM_TYPE and IAS.ITEM_KEY = I.ITEM_KEY and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE) and I.ITEM_TYPE = IT.NAME and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID and PA.ACTIVITY_NAME = A.NAME and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE and L_AT.LOOKUP_TYPE = 'WFENG_ACTIVITY_TYPE' and L_AT.LOOKUP_CODE = A.TYPE and L_AS.LOOKUP_TYPE = 'WFENG_STATUS' and L_AS.LOOKUP_CODE = IAS.ACTIVITY_STATUS; /* ** WF_ITEMS_V */ create or replace force view WF_ITEMS_V ( ITEM_TYPE, ITEM_KEY, USER_KEY, ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, OWNER_ROLE, PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT, BEGIN_DATE, END_DATE ) as select /* &&hdr */ ITEM_TYPE, ITEM_KEY, USER_KEY, ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, OWNER_ROLE, PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT, BEGIN_DATE, END_DATE from WF_ITEMS; /* ** WF_RUNNABLE_PROCESSES_V */ CREATE OR REPLACE FORCE VIEW wf_runnable_processes_v AS SELECT /* &&hdr */ wfa_act.item_type item_type, wfa_act.name process_name, wfa_act.display_name display_name FROM wf_activities_vl wfa_act WHERE wfa_act.runnable_flag = 'Y' AND wfa_act.type = 'PROCESS' AND sysdate BETWEEN wfa_act.begin_date AND nvl(wfa_act.end_date, sysdate); commit; exit;