VIEW: APPS.WF_ITEM_ACTIVITIES_HISTORY_V

Source

View Text - Preformatted

SELECT IAS.ITEM_TYPE , IAS.ITEM_KEY , A.BEGIN_DATE ACTIVITY_DEF_BEGIN_DATE , A.END_DATE ACTIVITY_DEF_END_DATE , IAS.BEGIN_DATE BEGIN_DATE , IAS.END_DATE END_DATE , IAS.EXECUTION_TIME , TO_CHAR(IAS.BEGIN_DATE) || ' ' || TO_CHAR(IAS.BEGIN_DATE, 'HH24:MI:SS') BEGIN_DATE_TIME , DECODE(IAS.ACTIVITY_STATUS, 'COMPLETE', (IAS.END_DATE - IAS.BEGIN_DATE) * 86400, 'CANCEL', (IAS.END_DATE - IAS.BEGIN_DATE) * 86400, 'ERROR', (IAS.END_DATE - IAS.BEGIN_DATE) * 86400, (SYSDATE - IAS.BEGIN_DATE) * 86400) DURATION , PA.ACTIVITY_ITEM_TYPE , A.TYPE ACTIVITY_TYPE , PAR.NAME PARENT_ACTIVITY_NAME , A.NAME ACTIVITY_NAME , A.DISPLAY_NAME ACTIVITY_DISPLAY_NAME , PAR.DISPLAY_NAME PARENT_DISPLAY_NAME , DECODE(IAS.ACTIVITY_STATUS, 'DEFERRED', 'ACTIVE', 'WAITING', 'ACTIVE', 'NOTIFIED', 'ACTIVE', IAS.ACTIVITY_STATUS) ACTIVITY_STATUS , N.STATUS NOTIFICATION_STATUS , IAS.NOTIFICATION_ID , DECODE(A.TYPE, 'NOTICE', N.RECIPIENT_ROLE, 'WF_ENGINE') RECIPIENT_ROLE , DECODE(A.TYPE, 'NOTICE', WF_DIRECTORY.GETROLEDISPLAYNAME(N.RECIPIENT_ROLE), WF_CORE.TRANSLATE('WFMON_WF_ENGINE')) RECIPIENT_ROLE_NAME , WF_CORE.TRANSLATE (DECODE(IAS.ACTIVITY_STATUS, 'DEFERRED', 'ACTIVE', 'WAITING', 'ACTIVE', 'NOTIFIED', 'ACTIVE', IAS.ACTIVITY_STATUS)) ACTIVITY_STATUS_DISPLAY , WF_CORE.ACTIVITY_RESULT(A.RESULT_TYPE, DECODE(IAS.ACTIVITY_RESULT_CODE, '#NULL', NULL, IAS.ACTIVITY_RESULT_CODE)) RESULT FROM WF_ITEM_ACTIVITY_STATUSES IAS, WF_NOTIFICATIONS N, WF_ACTIVITIES_VL A, WF_ACTIVITIES_VL PAR, WF_PROCESS_ACTIVITIES PA WHERE IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE AND PA.ACTIVITY_NAME = A.NAME AND PA.PROCESS_NAME = PAR.NAME AND PA.PROCESS_ITEM_TYPE = PAR.ITEM_TYPE AND PA.PROCESS_VERSION = PAR.VERSION AND N.GROUP_ID(+) = IAS.NOTIFICATION_ID UNION ALL SELECT IAS.ITEM_TYPE, IAS.ITEM_KEY, A.BEGIN_DATE ACTIVITY_DEF_BEGIN_DATE, A.END_DATE ACTIVITY_DEF_END_DATE, IAS.BEGIN_DATE BEGIN_DATE, IAS.END_DATE END_DATE, IAS.EXECUTION_TIME, TO_CHAR(IAS.BEGIN_DATE) || ' ' || TO_CHAR(IAS.BEGIN_DATE, 'HH24:MI:SS') BEGIN_DATE_TIME, DECODE(IAS.ACTIVITY_STATUS, 'COMPLETE', (IAS.END_DATE - IAS.BEGIN_DATE) * 86400, 'CANCEL', (IAS.END_DATE - IAS.BEGIN_DATE) * 86400, 'ERROR', (IAS.END_DATE - IAS.BEGIN_DATE) * 86400, (SYSDATE - IAS.BEGIN_DATE) * 86400) DURATION, PA.ACTIVITY_ITEM_TYPE, A.TYPE ACTIVITY_TYPE, PAR.NAME PARENT_ACTIVITY_NAME, A.NAME ACTIVITY_NAME, A.DISPLAY_NAME ACTIVITY_DISPLAY_NAME, PAR.DISPLAY_NAME PARENT_DISPLAY_NAME, DECODE(IAS.ACTIVITY_STATUS, 'DEFERRED', 'ACTIVE', 'WAITING', 'ACTIVE', 'NOTIFIED', 'ACTIVE', IAS.ACTIVITY_STATUS) ACTIVITY_STATUS, N.STATUS NOTIFICATION_STATUS, IAS.NOTIFICATION_ID, DECODE(A.TYPE, 'NOTICE', N.RECIPIENT_ROLE, 'WF_ENGINE') RECIPIENT_ROLE, DECODE(A.TYPE, 'NOTICE', WF_DIRECTORY.GETROLEDISPLAYNAME(N.RECIPIENT_ROLE), WF_CORE.TRANSLATE('WFMON_WF_ENGINE')) RECIPIENT_ROLE_NAME, WF_CORE.TRANSLATE (DECODE(IAS.ACTIVITY_STATUS, 'DEFERRED', 'ACTIVE', 'WAITING', 'ACTIVE', 'NOTIFIED', 'ACTIVE', IAS.ACTIVITY_STATUS)) ACTIVITY_STATUS_DISPLAY, WF_CORE.ACTIVITY_RESULT(A.RESULT_TYPE, DECODE(IAS.ACTIVITY_RESULT_CODE, '#NULL', NULL, IAS.ACTIVITY_RESULT_CODE)) RESULT FROM WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_NOTIFICATIONS N, WF_ACTIVITIES_VL A, WF_ACTIVITIES_VL PAR, WF_PROCESS_ACTIVITIES PA WHERE IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE AND PA.ACTIVITY_NAME = A.NAME AND PA.PROCESS_NAME = PAR.NAME AND PA.PROCESS_ITEM_TYPE = PAR.ITEM_TYPE AND PA.PROCESS_VERSION = PAR.VERSION AND N.GROUP_ID(+) = IAS.NOTIFICATION_ID

View Text - HTML Formatted

SELECT IAS.ITEM_TYPE
, IAS.ITEM_KEY
, A.BEGIN_DATE ACTIVITY_DEF_BEGIN_DATE
, A.END_DATE ACTIVITY_DEF_END_DATE
, IAS.BEGIN_DATE BEGIN_DATE
, IAS.END_DATE END_DATE
, IAS.EXECUTION_TIME
, TO_CHAR(IAS.BEGIN_DATE) || ' ' || TO_CHAR(IAS.BEGIN_DATE
, 'HH24:MI:SS') BEGIN_DATE_TIME
, DECODE(IAS.ACTIVITY_STATUS
, 'COMPLETE'
, (IAS.END_DATE - IAS.BEGIN_DATE) * 86400
, 'CANCEL'
, (IAS.END_DATE - IAS.BEGIN_DATE) * 86400
, 'ERROR'
, (IAS.END_DATE - IAS.BEGIN_DATE) * 86400
, (SYSDATE - IAS.BEGIN_DATE) * 86400) DURATION
, PA.ACTIVITY_ITEM_TYPE
, A.TYPE ACTIVITY_TYPE
, PAR.NAME PARENT_ACTIVITY_NAME
, A.NAME ACTIVITY_NAME
, A.DISPLAY_NAME ACTIVITY_DISPLAY_NAME
, PAR.DISPLAY_NAME PARENT_DISPLAY_NAME
, DECODE(IAS.ACTIVITY_STATUS
, 'DEFERRED'
, 'ACTIVE'
, 'WAITING'
, 'ACTIVE'
, 'NOTIFIED'
, 'ACTIVE'
, IAS.ACTIVITY_STATUS) ACTIVITY_STATUS
, N.STATUS NOTIFICATION_STATUS
, IAS.NOTIFICATION_ID
, DECODE(A.TYPE
, 'NOTICE'
, N.RECIPIENT_ROLE
, 'WF_ENGINE') RECIPIENT_ROLE
, DECODE(A.TYPE
, 'NOTICE'
, WF_DIRECTORY.GETROLEDISPLAYNAME(N.RECIPIENT_ROLE)
, WF_CORE.TRANSLATE('WFMON_WF_ENGINE')) RECIPIENT_ROLE_NAME
, WF_CORE.TRANSLATE (DECODE(IAS.ACTIVITY_STATUS
, 'DEFERRED'
, 'ACTIVE'
, 'WAITING'
, 'ACTIVE'
, 'NOTIFIED'
, 'ACTIVE'
, IAS.ACTIVITY_STATUS)) ACTIVITY_STATUS_DISPLAY
, WF_CORE.ACTIVITY_RESULT(A.RESULT_TYPE
, DECODE(IAS.ACTIVITY_RESULT_CODE
, '#NULL'
, NULL
, IAS.ACTIVITY_RESULT_CODE)) RESULT
FROM WF_ITEM_ACTIVITY_STATUSES IAS
, WF_NOTIFICATIONS N
, WF_ACTIVITIES_VL A
, WF_ACTIVITIES_VL PAR
, WF_PROCESS_ACTIVITIES PA
WHERE IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
AND PA.ACTIVITY_NAME = A.NAME
AND PA.PROCESS_NAME = PAR.NAME
AND PA.PROCESS_ITEM_TYPE = PAR.ITEM_TYPE
AND PA.PROCESS_VERSION = PAR.VERSION
AND N.GROUP_ID(+) = IAS.NOTIFICATION_ID UNION ALL SELECT IAS.ITEM_TYPE
, IAS.ITEM_KEY
, A.BEGIN_DATE ACTIVITY_DEF_BEGIN_DATE
, A.END_DATE ACTIVITY_DEF_END_DATE
, IAS.BEGIN_DATE BEGIN_DATE
, IAS.END_DATE END_DATE
, IAS.EXECUTION_TIME
, TO_CHAR(IAS.BEGIN_DATE) || ' ' || TO_CHAR(IAS.BEGIN_DATE
, 'HH24:MI:SS') BEGIN_DATE_TIME
, DECODE(IAS.ACTIVITY_STATUS
, 'COMPLETE'
, (IAS.END_DATE - IAS.BEGIN_DATE) * 86400
, 'CANCEL'
, (IAS.END_DATE - IAS.BEGIN_DATE) * 86400
, 'ERROR'
, (IAS.END_DATE - IAS.BEGIN_DATE) * 86400
, (SYSDATE - IAS.BEGIN_DATE) * 86400) DURATION
, PA.ACTIVITY_ITEM_TYPE
, A.TYPE ACTIVITY_TYPE
, PAR.NAME PARENT_ACTIVITY_NAME
, A.NAME ACTIVITY_NAME
, A.DISPLAY_NAME ACTIVITY_DISPLAY_NAME
, PAR.DISPLAY_NAME PARENT_DISPLAY_NAME
, DECODE(IAS.ACTIVITY_STATUS
, 'DEFERRED'
, 'ACTIVE'
, 'WAITING'
, 'ACTIVE'
, 'NOTIFIED'
, 'ACTIVE'
, IAS.ACTIVITY_STATUS) ACTIVITY_STATUS
, N.STATUS NOTIFICATION_STATUS
, IAS.NOTIFICATION_ID
, DECODE(A.TYPE
, 'NOTICE'
, N.RECIPIENT_ROLE
, 'WF_ENGINE') RECIPIENT_ROLE
, DECODE(A.TYPE
, 'NOTICE'
, WF_DIRECTORY.GETROLEDISPLAYNAME(N.RECIPIENT_ROLE)
, WF_CORE.TRANSLATE('WFMON_WF_ENGINE')) RECIPIENT_ROLE_NAME
, WF_CORE.TRANSLATE (DECODE(IAS.ACTIVITY_STATUS
, 'DEFERRED'
, 'ACTIVE'
, 'WAITING'
, 'ACTIVE'
, 'NOTIFIED'
, 'ACTIVE'
, IAS.ACTIVITY_STATUS)) ACTIVITY_STATUS_DISPLAY
, WF_CORE.ACTIVITY_RESULT(A.RESULT_TYPE
, DECODE(IAS.ACTIVITY_RESULT_CODE
, '#NULL'
, NULL
, IAS.ACTIVITY_RESULT_CODE)) RESULT
FROM WF_ITEM_ACTIVITY_STATUSES_H IAS
, WF_NOTIFICATIONS N
, WF_ACTIVITIES_VL A
, WF_ACTIVITIES_VL PAR
, WF_PROCESS_ACTIVITIES PA
WHERE IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
AND PA.ACTIVITY_NAME = A.NAME
AND PA.PROCESS_NAME = PAR.NAME
AND PA.PROCESS_ITEM_TYPE = PAR.ITEM_TYPE
AND PA.PROCESS_VERSION = PAR.VERSION
AND N.GROUP_ID(+) = IAS.NOTIFICATION_ID