wf tables in oracle apps r12
In this post , We will be discuss about the wf tables in oracle apps r12. wf tables help to store the workflow related informations in oracle apps. We do get the different set of wf information in oracle apps wf tables. To track the Workflow approval process , We need to use these wf tables , which do store the information about each workflow approval process. For example , If we want to track the PO approval Workflow for one Purchase order then we can refer these wf tables which do help to track the every thing about workflow about that process. Here below i will share these WF tables in oracle apps r12 in more detail.
12 Most Important wf tables in oracle apps r12
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
WF_ITEMS :-
For Example , I will take the example of Purchase Order Approval. Oracle EBS do create the Item Key for Each Purchase order approval proces. In the same way it work for ' Expense' 'Requisitions' and others.
We need to use the ITEM_TYPE='POAPPRV' , Using this Table , We can find that , how many purchase order apprvoal process has been triggered through
workflow and whats its state. Like , Owner Role, User Key is the Purchase Order No., Workflow start date and End date.
WF_ITEM_TYPES : -
The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE
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.
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
The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.
WF_ITEM_ACTIVITY_STATUSES :-
The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK).
WF_MESSAGES :-
WF_MESSAGES helps to store the Registry or definition of the workflow Notifications messages. When we do create the Workflow notifications , We do create Notifications through workflow
messages so this tables help to store the Registry or definition of the workflow Notifications messages
WF_MESSAGE_ATTRIBUTES :-
WF_MESSAGE_ATTRIBUTES helps to store the Workflow message attributes which we are using in the workflow notification messages. Each message attribute drive some value in run time and display in the Workflow notification
message.
WF_NOTIFICATIONS :-
This table help to track the each workflow notifications sent by workflow each process. We can track the wrokflow notifications for each process like PO , Expenses , Requisition , AP Invoice.
Workflow Notification mailer troubleshooting with wf tables in oracle apps r12
Step1:- 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’
if you want to see the open notifications which are pending for user action and those Email notification are delivered then you can use this below query
if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.
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”
Verify whether the message is processed in WF_DEFERRED queue
select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= :NOTIFICATION_ID
2. 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
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’
if you want to see the open notifications which are pending for user action and those Email notification are delivered then you can use this below query
if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.
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”
Verify whether the message is processed in WF_DEFERRED queue
select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= :NOTIFICATION_ID
2. 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
0 comments:
Post a Comment