This articles contains all the table information related to Oracle Workflows and queries joining these tables.
WORKFLOW TABLES
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 |