Workflow

Oracle Workflow Notification Query

Firstly, retrieve all the messages from the system which have a message attribute which is does not have a type of RESPOND, but which also do not have any RESPOND attributes.

SELECT *
FROM   ( SELECT DISTINCT ‘FYI’          action
,      wfi.display_name        item_type
,      wfma.message_type       item_type_internal
,      wfmt.display_name       message_name
,      wfma.message_name       message_internal
FROM   wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfma.SUBTYPE     != ‘RESPOND’
AND    wfma.message_type = wfi.name
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
AND    (wfma.message_type, wfma.message_name) NOT IN ( SELECT DISTINCT wfma2.message_type
,      wfma2.message_name
FROM   wf_message_attributes   wfma2
WHERE  wfma2.SUBTYPE      = ‘RESPOND’
)
UNION
SELECT DISTINCT ‘Response Required’
,      wfi.display_name        item_type
,      wfma.message_type       item_type_internal
,      wfmt.display_name       message_name
,      wfma.message_name       message_internal
FROM   wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfma.SUBTYPE     = ‘RESPOND’
AND    wfma.message_type = wfi.name
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
) messages
ORDER BY item_type_internal, message_internal

 

 

We now need to include the notifications that are open in the system, so include WF_NOTIFICATIONS in the query as well

SELECT *
FROM   ( SELECT DISTINCT ‘FYI’           action
,      wfi.display_name         item_type
,      wfn.message_type         item_type_internal
,      wfmt.display_name        message_name
,      wfn.message_name         message_internal
,      wfn.notification_id
FROM   wf_notifications        wfn
,      wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfn.message_type  = wfma.message_type
AND    wfn.message_name  = wfma.message_name
AND    wfma.message_type = wfi.name
AND    wfma.SUBTYPE     != ‘RESPOND’
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
AND    (wfn.message_type, wfn.message_name) NOT IN ( SELECT DISTINCT wfn2.message_type
,      wfn2.message_name
FROM   wf_notifications        wfn2
,      wf_message_attributes   wfma2
WHERE  wfn2.message_type  = wfma2.message_type
AND    wfn2.message_name  = wfma2.message_name
AND    wfma2.SUBTYPE      = ‘RESPOND’ )
UNION
SELECT DISTINCT ‘Response Required’
,      wfi.display_name         item_type
,      wfn.message_type         item_type_internal
,      wfmt.display_name        message_name
,      wfn.message_name         message_internal
,      wfn.notification_id
FROM   wf_notifications        wfn
,      wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfn.message_type  = wfma.message_type
AND    wfn.message_name  = wfma.message_name
AND    wfma.message_type = wfi.name
AND    wfma.SUBTYPE      = ‘RESPOND’
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
) notifications
ORDER BY item_type_internal

How to check Open Workflow Notification from backend and how to Approve/Reject.

select  wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type = ‘XXRECNQM’
and  wias.item_key like ‘XXRECNQM%’;

 

 

BEGIN
wf_notification.setattrtext(nid => ‘NOTIFICATION_ID’,aname => ‘RESULT’,avalue =>  ‘REJECTED/APPROVED’ );
wf_notification.respond(nid => ‘NOTIFICATION_ID’,respond_comment => ‘Reject from Backend’,responder =>  ‘USER_NAME/RESPONDER_NAME’ );
Commit;
END;

——————————————————-

 

Close FYI Notification

BEGIN
wf_notification.close(NOTIFICATION_ID,’SYSADMIN’);

END;

 

 

 

How to update Workflow Administrator Role in Oracle Applications

If you want to see workflow details (owned by other users) or status diagram in Oracle Applications 11i/R12 then you should belong to one of responsibilities/user listed under WF_ADMIN_ROLE.

By default (in 11i & R12) this role is set to user sysadmin (In old versions 11.5.8 or prior, it used to set to *)

If you wish to change WF_ADMIN_ROLE, as per most of meta link notes either

1. Change it via Workflow Administrator Web Applications responsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration)

or update table
2. SQL> update wf_resources set text=’&Enter_Admin_Name’ where name=’WF_ADMIN_ROLE’;

to set it to everyone, use
SQL> update wf_resources set text=’*’ where name=’WF_ADMIN_ROLE’;

.

Problem with above solution
– Execution of Auto config will override above settings. Auto config will pick value against parameter s_wf_admin_role (default value SYSADMIN) from context file $CONTEXT_FILE
.

Correct way to set Workflow Administrator Role

1. First identify Workflow Role Name associated with User or Responsibility.

A. For setting Admin Role to specific user
SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘&USER_NAME’  and ORIG_SYSTEM=’FND_USR’;

B. For setting it to a responsibility (so that all users with that responsibility can view other user’s Workflow)
SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘&Responsibility_Name’ and ORIG_SYSTEM=’FND_RESP’;

SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘System Administrator‘;  (output for System Administrator responsibility should look like)FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD

SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘Application Developer‘; (output for Application Developer responsibility should look like)
FND_RESP|FND|APPLICATION_DEVELOPER|STANDARD

2. Update context file ($CONTEXT_FILE) variable s_wf_admin_role (If you don’t see this parameter in context file then apply latest Autoconfig Patch) to value from above query

For Sysadmin User – Set it to SYSADMIN

For System Administrator Responsibility – Set it to
FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD

For Application Developer Responsibility – Set it to FND_RESP|FND|APPLICATION_DEVELOPER|STANDARD

3. Run Autoconfig
$OAD_TOP/admin/scripts/$CONTEXT_NAME/adautocfg.sh (11i)
$ADMIN_SCRIPTS_HOME/adautocfg.sh (R12)

Oracle Workflow – Tables Description and usage

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
The WF_ITEM_ATTRIBUTES table 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. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES
WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES
The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES
WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS
WF_NOTIFICATIONS holds 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.

WF_NOTIFICATION_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS
WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

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_ITEM_ACTIVITY_STATUSES_H
The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES
WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS
The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES
The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

Workflow Notification Mailer Setup in Oracle Apps R12/12i

On popular demand from readers, I am going to cover Workflow Notification Mailer in Oracle Applications R12/12i.
Workflow notification mailer setup in R12 is similar to 11i ( In both release 11i (OWF.H and higher in 11i) & R12 are Java Mailer)
* Previous version of Notification Mailer in 11i was based on C also called as C Mailer

Things to note
1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.


Step to configure Workflow Notification Mailer

1. Login to Apps R12 with System Administrator Reponsibility
2. under Workflow : Oracle Applications Manager click on Workflow Manager

workflow notification mailer

If this is first time you are configuring Workflow Notification Mailer in Oracle Apps R12/12i you will see Notification Mailers as unavailable as shown in screenshot

Click on Notification Mailers

Workflow Notification Mailer Setup in Oracle Apps R12/12i

In next screen (as shown below, click on Edit Button)

Workflow Notification Mailer Setup in Oracle Apps R12/12i

Here you have option to select Inbound notification setup or Just outgoing Notification Setup.

Workflow Notification Mailer Setup in Oracle Apps R12/12i

Provide SMTP Server Name (ensure that CM node should be able to connect to SMTP Server or SMTP Relay)

Workflow Notification Mailer Setup in Oracle Apps R12/12i

Uncheck Inbound Processing (from above screen), if you don’t wish to configure Inbound Notification Mailer.

If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)

Click on Apply button to finish configuration, at this stage Notification Mailer will test SMTP Server & IMAP Server connectivity.

Workflow Tables Information in R12

WF_ITEM_TYPES – It defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE

WF_ITEM_ATTRIBUTES – 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. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES -table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES – table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES – contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS holds 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.

WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

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_ITEM_ACTIVITY_STATUSES_H – table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

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

Oracle Workflow – Important Queries

 

–Select all workflow items for a given item type
SELECT item_type,
       item_key,
       to_char(begin_date,
               ‘DD-MON-RR HH24:MI:SS’) begin_date,
       to_char(end_date,
               ‘DD-MON-RR HH24:MI:SS’) end_date,
       root_activity activity
  FROM apps.wf_items
 WHERE item_type = ‘&item_type’
   AND end_date IS NULL
 ORDER BY to_date(begin_date,
                  ‘DD-MON-YYYY hh24:mi:ss’) DESC;
 
— notifications sent by a given workflow
select  wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type = ‘WSHSUPI’
and  wias.item_key = ‘CMS21408’
/
–prompt **** Find the Activity Statuses for all workflow activities of a given item type and item key
SELECT execution_time,
       to_char(ias.begin_date,
               ‘DD-MON-RR HH24:MI:SS’) begin_date,
       ap.display_name || ‘/’ || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = ‘&item_type’
   AND ias.item_key = ‘&item_key’
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = ‘&item_type’
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               ‘DD-MON-RR HH24:MI:SS’) begin_date,
       ap.display_name || ‘/’ || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = ‘&item_type’
   AND ias.item_key = ‘&item_key’
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = ‘&item_type’
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/
–Get a list of all Errored Workflow Activities for a given item type/ item key
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = ‘&item_type’
   AND ias.item_key = ‘&item_key’
   AND ias.activity_status = ‘ERROR’
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = ‘&item_type’
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/
 
–prompt *** Error Process Activity Statuses
SELECT execution_time,
       to_char(ias.begin_date,
               ‘DD-MON-RR HH24:MI:SS’) begin_date,
       ap.display_name || ‘/’ || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = ‘&item_type’
   AND i.parent_item_key = ‘&item_key’
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               ‘DD-MON-RR HH24:MI:SS’) begin_date,
       ap.display_name || ‘/’ || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = ‘&item_type’
   AND i.parent_item_key = ‘&item_key’
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/
 
prompt **** Error Process Errored Activities
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.activity_status = ‘ERROR’
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = ‘&item_type’
   AND i.parent_item_key = ‘&item_key’
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/
 
prompt **** Attribute Values
SELECT NAME attr_name,
       nvl(text_value,
           nvl(to_char(number_value),
               to_char(date_value))) VALUE
  FROM wf_item_attribute_values
 WHERE item_type = upper(‘&item_type’)
   AND item_key = nvl(‘&item_key’,
                      item_key)
/
–Count of all workflow deferred activities based
SELECT COUNT(1),
       was.item_type
  FROM apps.wf_items                  wi,
       apps.wf_item_activity_statuses was,
       apps.wf_process_activities     pra
 WHERE wi.item_type = was.item_type
   AND wi.item_key = was.item_key
   AND wi.end_date IS NULL
   AND was.end_date IS NULL
   AND was.activity_status = ‘DEFERRED’
      –AND was.item_type = ‘REQAPPRV’
   AND was.item_type = wi.item_type
   AND pra.instance_id(+) = was.process_activity
 GROUP BY was.item_type;
–check the various workflow agent listeners and their statuses
SELECT t.component_name,
       p.owner,
       p.queue_table,
       t.correlation_id
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || ‘.’ || p.name = o.queue_name
   AND t.component_type LIKE ‘WF_%AGENT%’;
–query to find records that are pending in each of the workflow agent listener queues
SELECT ‘select ”’ || t.component_name || ‘ (queue_table: ‘ || p.queue_table ||
       ‘)”||” Count: ”||count(*) c from ‘ || p.owner || ‘.’ || p.queue_table ||
       ‘ where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
       nvl2(t.correlation_id,
            ‘and corrid like ”’ || t.correlation_id || ”’ ‘,
            NULL) || ‘having count(*)>0;’
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || ‘.’ || p.name = o.queue_name
   AND t.component_type LIKE ‘WF_%AGENT%’;
–Look for deferred events in wf_deferred. this can also be used to track the status of notifications/business events that are waiting to be processed/that have errored out
SELECT a.user_data.geteventname(),
       decode(a.state,
              0,
              ‘0 = Ready’,
              1,
              ‘1 = Delayed’,
              2,
              ‘2 = Retained/Processed’,
              3,
              ‘3 = Exception’,
              to_char(a.state)) state,
       a.user_data.PARAMETER_LIST,
       a.user_data.event_data,
       a.user_data.event_key,
       a.*
  FROM apps.wf_deferred a
 WHERE corrid LIKE ‘%oracle.apps.wsh.sup.ssro’
   AND rownum < 10;