Workflow Queries in oracle apps
In this post , We will discuss about Workflow Queries in oracle apps. We will share most important Workflow Queries in oracle apps. These workflow queries we do uses in day to day operations while working in oracle apps. These workflow queries help to troubleshoot and verify the workflow data from backend. We will also shared the workflow tables used by these Workflow Queries. These Workflow Queries helps to extract the complete workflow data in oracle apps. Please find below the complete detail about Workflow Queries in oracle apps.
 |
| Workflow Queries in oracle apps |
12 Most Important wf tables used by Workflow Queries in oracle apps.
1.WF_ITEMS
2.WF_ITEM_TYPES
3.WF_ITEM_ATTRIBUTES
4.WF_ACTIVITIES
5.WF_ACTIVITY_ATTRIBUTES
6.WF_ITEM_ACTIVITY_STATUSES
7.WF_MESSAGE_ATTRIBUTES
8.WF_ACTIVITY_ATTR_VALUES
9.WF_MESSAGES
10.WF_NOTIFICATIONS
11.WF_NOTIFICATION_ATTRIBUTES
12.WF_PROCESS_ACTIVITIES
 |
Workflow Queries in oracle apps |
Most Important Workflow Queries in oracle apps
Ist Workflow Query
We can see all workflow notification in This below Tables.
Select * From Wf_Notifications
If you want to see the open notifications which are pending for user action then you can use below query.
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
If you want to see the open notifications which are pending for user action and those Email notification still pending to deliver then you can use this below query
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’
Select * From Wf_Notifications
If you want to see the open notifications which are pending for user action then you can use below query.
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
If you want to see the open notifications which are pending for user action and those Email notification still pending to deliver then you can use this below query
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’
if you want to see the open notifications whose Email notifications Failed to get delivered then you can use below query.
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘FAILED’
If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’
if you want to see the open notifications whose Email notifications Failed to get delivered then you can use below query.
Select * From Wf_Notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘FAILED’
If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
2nd Workflow Query :-
Verify whether the message is processed in WF_DEFERRED queue
select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= :NOTIFICATION_ID
select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= :NOTIFICATION_ID
If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue
select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key =
If user is not receiving the emails from system then check the preferences in this below tables for the user_name
select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’TEST_USER’ ;
Status – Active
Notification_preference-> Should be Email
Email Address should not be null
WF_ACTIVITIES :-
WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications,functions or folders. ITEM_TYPE (PK), RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG
 |
| Workflow Queries in oracle apps |
WF_ITEM_ATTRIBUTES :-
WF_ITEM_ATTRIBUTES help to store the different Workflow Attributes which is currently used in the Workflow Specific Process. For example, we are seeing the Workflow Item attributes for Purchase Order.
 |
| Workflow Queries in oracle apps |
0 comments:
Post a Comment