Workflow Tables and Queries

[Sara Khan]

This articles contains all the table information related to Oracle Workflows and queries joining these tables.

Query1: Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.

Query2: Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req

WORKFLOW TABLES
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS



SELECT * FROM WF_USER_ROLES



SELECT * FROM WF_ROLES



SELECT * FROM WF_ITEMS



SELECT * FROM WF_ITEM_ATTRIBUTES



SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES



SELECT * FROM WF_ITEM_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITIES



SELECT * FROM WF_ACTIVITIES_TL



SELECT * FROM WF_ACTIVITY_ATTRIBUTES



SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITY_TRANSITIONS



SELECT * FROM WF_DEFERRED--WF_CONTROL



SELECT * FROM WF_ACTIVITY_ATTR_VALUES

WHERE NAME LIKE '%MASTER%'

AND PROCESS_ACTIVITY_ID

IN(

SELECT *-- PROCESS_ACTIVITY

FROM WF_ITEM_ACTIVITY_STATUSES

WHERE ITEM_TYPE = 'ERP'

AND ITEM_KEY ='63865'

)



SELECT * FROM WF_ITEM_TYPES



SELECT * FROM WF_LOOKUPS_TL



SELECT * FROM WF_NOTIFICATIONS

WHERE MESSAGE_TYPE ='ERP'

ORDER BY BEGIN_DATE DESC



SELECT * FROM WF_NOTIFICATION_ATTRIBUTES



SELECT * FROM WF_MESSAGES



SELECT * FROM WF_MESSAGES_TL



SELECT * FROM WF_MESSAGE_ATTRIBUTES



SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL



SELECT * FROM WF_ETS



SELECT * FROM WF_PROCESS_ACTIVITIES

LIST OF ACTIVITIES FOR AN ITEMTYPE

SELECT A.ITEM_KEY,

B.ACTIVITY_NAME,

A.ACTIVITY_STATUS,

A.ACTIVITY_RESULT_CODE,

A.ASSIGNED_USER,

A.BEGIN_DATE,

A.END_DATE

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

AND A.ITEM_KEY = 64077

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')


TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING
SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS

LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS


SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS

FROM

(SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS

WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5

Your suggestions are welcome to improve ........ more you share, more you learn

[tosekhar]

Here is a Query to find list of items that are in error:-

SELECT DISTINCT TO_CHAR(begin_date,'DD-MON-RRRR HH:MI:SS PM'),
wpa.activity_name ,
wis.*
FROM apps.WF_ITEM_ACTIVITY_STATUSES wis ,
apps.wf_process_activities wpa
WHERE 1=1
and item_type in ('POAPPRV') -- eg: PO Approval workflow
--and item_key in (' ') --optional
--AND error_message LIKE '%ORA-01438:%' --optional
and activity_status in ( 'ERROR','DEFERRED')
AND begin_date > sysdate -7 --last 7 days
AND wis.process_activity= wpa.instance_id
ORDER BY begin_date DESC ;

Thanks,
Sekhar