Workflow Tables and Queries

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