Workflow Tables and Queries

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

 

Click here to copy script to clipboard

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')

Click here to copy script to clipboard

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

Click here to copy script to clipboard

 

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            

Click here to copy script to clipboard