Workflow Tables

WF_ACTIVITIEStable stores the definition of an activity. Activities can

be processes, notifications, functions or folders. A process activity is a

modelled workflow process, which can be included as an activity in

other processes to represent a sub–process. A notification activity sends

a message to a performer. A functions activity performs an automated

function that is written as a PL/SQL stored procedure. A folder activity

is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row

defines one work item within the system.

The WF_ITEM_ATTRIBUTEStable stores definitions of attributes

associated with a process. Each row includes the sequence in which the

attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONSholds the runtime information about a specific

instance of a sent message. A new row is created in the table each time a

message is sent

******************************************************************************

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

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.

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

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