WORKFLOW
TABLES
SELECT
* FROM WF_USER_ROLE_ASSIGNMENTS
SELECT
* FROM WF_USER_ROLES
SELECT
* FROM WF_ROLES
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.
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')
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
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
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 required.
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 required.
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 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
