What is Oracle Workflow Manager in oracle apps R12

Oracle Workflow Manager

Oracle Workflow Manager is a component of Oracle Applications Manager that allows system administrators to manage Oracle Workflow for multiple Oracle E-Business Suite instances from a single console.
Using Oracle Workflow Manager, administrators can control Workflow system services, such as notification mailers, agent listeners, and other service components, background engines, purging obsolete Workflow data, and cleanup of the Workflow control queue. Administrators can also monitor work item processing by viewing the distribution of all work items by status and drilling down to additional information. Additionally, they can monitor event message processing for local Business Event System agents by viewing the distribution of event messages by status as well as queue propagation schedules. With this ability to monitor work items and event messages, a system administrator can identify possible bottlenecks easily.
To access Oracle Workflow Manager, log into Oracle Applications Manager and select an applications system. Then, you can follow one of the following navigation paths:

• Choose Workflow Manager from the pull-down menu in the Applications Dashboard page and click the Go button.
  
• Choose Site Map, choose the Administration tab, and then choose the Home link in the Workflow region of the Site Map page. You can also choose one of the other links in the Workflow region to navigate directly to the corresponding page within Oracle Workflow Manager.

Navigation: Applications Dashboard > (pull-down menu) Workflow Manager > (B) Go
You can also use other features to help manage Oracle Workflow.

• Use Oracle Diagnostics Framework to run diagnostic tests that check the setup of your Oracle Workflow installation and review debugging information.
  
• Use Oracle E-Business Suite Logging to review Oracle Workflow logs. Oracle Workflow uses the Oracle E-Business Suite Logging framework to standardize and centralize in the database logging activities related to the Oracle Workflow Business Event System and Oracle XML Gateway.
  Note: The Java middle tier components of Oracle Workflow, including notification mailers and agent listeners, also use Oracle E-Business Suite Logging; however, due to the high volume of messages that pass through these components, their information is logged to the file system by default.
  
  

  Oracle workflow notification mailer related some scripts.

  
  **************************************************
  
  
  1. Workflow: version
   
•  $FND_TOP/sql/wfver.sql
  
  **************************************************
  2. check workflow status.
  set linesize 120
  set pagesize 50
  column COMPONENT_NAME format a45
  column STARTUP_MODE format a15
  column COMPONENT_STATUS format a15
  select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date
  from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
  where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
  order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
  
  
  
  **************************************************
  3. check if workflow is used by only one instance
  col value format a20
  select p.parameter_id,
  p.parameter_name,
  v.parameter_value value
  from apps.fnd_svc_comp_param_vals_v v,
  apps.fnd_svc_comp_params_b p,
  apps.fnd_svc_components c
  where c.component_type = 'WF_MAILER'
  and v.component_id = c.component_id
  and v.parameter_id = p.parameter_id
  and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
  order by p.parameter_name;
  
  **************************************************
  4. check if processor_read_timeout_close is set to 'Y'
  set pagesize 100
  set linesize 132
  set feedback off
  set verify off
  
  col value format a35
  col component_name format a30
  
  select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
  from apps.fnd_svc_comp_param_vals_v v, apps.fnd_svc_comp_params_b p, apps.fnd_svc_components c
  where c.component_type = 'WF_MAILER'
  and v.component_id = c.component_id
  and v.parameter_id = p.parameter_id
  and p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
  order by c.component_name,p.parameter_name;
  
  **************************************************
  5. check for bad e-mail address
  
  If below SQL statement is returning rows you need to correct the email addresses for associated users:
  
  set linesize 170
  col name format a40
  col email_address format a80
  select name, email_address from apps.wf_local_roles where email_address like '% %';
  
  select name, email_address from apps.wf_local_roles where email_address like '%%';
  
  **************************************************
  6. How to know mail sent to a user with details:
  select name, display_name, notification_preference, email_address from wf_local_roles where name = '';
  
  **************************************************
  7. How to know whether it is set to correct url from porfile options:
  set linesize 155;
  set pagesize 200;
  set verify off;
  col Profile format a50;
  col Value format a50;
  select t.PROFILE_OPTION_ID ID, z.USER_PROFILE_OPTION_NAME Profile,
  nvl(v.PROFILE_OPTION_VALUE,'Replace with non-virtual URL') Value
  from apps.fnd_profile_options t, apps.fnd_profile_option_values v, apps.fnd_profile_options_tl z
  where (v.PROFILE_OPTION_ID (+) = t.PROFILE_OPTION_ID)
  and (z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME)
  and (t.PROFILE_OPTION_NAME in ('WF_MAIL_WEB_AGENT'));
  
  **************************************************
  8. How to know reqid, process id, sid..
  select request_id,phase_code,status_code,hold_flag from fnd_concurrent_requests where REQUEST_ID=20422815;
  
  select ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=20422815;
  
  select a.session_id,
  b.owner,
  b.object_type,
  b.object_name,
  a.oracle_username,
  a.os_user_name,
  a.process,
  a.locked_mode
  from v$locked_object a, dba_objects b
  where b.object_id = a.object_id
  and a.session_id='3383';
  
  select name, display_name, notification_preference, email_address from wf_local_roles where name = 'SYSADMIN';
  
  
  **************************************************
  9. workflow patches  will provide us information as to your base line code level.
  Many issues are only relevant to a certain code level so this information is essential:
  
  set linesize 155;
  set pagesize 200;
  set verify off;
  select b.bug_number bug, b.LAST_UPDATED_BY ldate, decode( bug_number, 2728236 , 'OWF.G INCLUDED IN 11.5.9',
  3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
  3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
  3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
  3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
  3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
  3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5', 3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
  3868138, 'POST OWF.G ROLLUP 7 - 11.5.9.7',
  3262919, 'FMWK.H',
  3262159, 'FND.H INCLUDE OWF.H',
  3258819, 'OWF.H INCLUDED IN 11.5.10',
  3438354, '11i.ATG_PF.H INCLUDE OWF.H',
  3140000, 'ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK',
  3240000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1',
  3460000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2',
  3480000, 'ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK',
  4017300, 'ATG_PF:11.5.10 Consolidated Update (CU1) for ATG Product Family',
  4125550, 'ATG_PF:11.5.10 Consolidated Update (CU2) for ATG Product Family',
  4719658, 'ONE OFF PATCH FOR MISSING RESPONSIBILITIES - WFDS Fix',
  5121512, 'AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1',
  6008417, 'AOL USER RESPONSIBILITY SECURITY FIXES 2b',
  4676589, '11i.ATG_PF.H RUP4',
  5473858, '11i.ATG_PF.H RUP5',
  5903765, '11i.ATG_PF.H RUP6',
  4334965, '11i.ATG_PF.H RUP3') patch
  from apps.AD_BUGS b
  where b. BUG_NUMBER in ('2728236','3031977','3061871','3124460','3316333','3314376','3409889','3492743','3262159','3262919','3868138','3258819','3438354','3240000','3460000','3140000','3480000','4017300','4125550','4719658','5121512','6008417','4676589','5473858','5903765','4334965')
  order by patch;
  
  **************************************************
  10. Workflow: To see failed, open notifications
  SELECT message_type, COUNT(1)
  FROM apps.wf_notifications
  WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'
  GROUP BY message_type;
  
  
  
  **************************************************
  11. To check if email address, notification preference, display_name
  
  
  select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = 'h@h.com' and STATUS = 'ACTIVE' and DISPLAY_NAME = 'xxxxx';
  
  select DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE';
  
  select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE' and EMAIL_ADDRESS = 'mmmmm@yahoo.com';
  
  **************************************************
  12. How to know workflow responsibility from backend:
  
  select wes.status, wes.phase, wes.rule_function, wes.on_error_code from wf_events we, wf_event_subscriptions wes
  where we.name='oracle.apps.fnd.wf.ds.userRole.updated' and we.guid=wes.event_filter_guid;
  
  **************************************************
  13. Steps to drop and recreate WF_CONTROL queue:
  
  a. Shut down the concurrent managers.
  
  b. Connect to sqlplus session as APPS user:
  Execute: (For Workflow Embedded within Apps)
  
  SQL>exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.WF_CONTROL', force =>TRUE);
  
  SQL> commit;
  
  Execute: (For Standalone Workflow)
  sqlplus / @wfctlqec.sql
  
  c. Execute wfjmsqc2.sql for creating all Advanced Queues with JMS Text structures.
  
  Usage:
  sqlplus / @wfjmsqc2.sql
  
  Both of the script are under $FND_TOP/patch/115/sql or $FND_TOP/sql directories.
  
  d.Run afwfqgnt.sql to recreate grants/synonyms for Workflow Advanced Queue tables
  **************************************************
  Usage:
  sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS APPLSYS
  Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.

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

Configuration for Workflow notification mailer setup in Oracle Applications R12

The main component of the Oracle Workflow Notification Mailer is the executable
WF-MAIL. This is a server side program that queries the database for any pending
notifications. It then dispatches these notifications by calling send-mail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Send-mail) and Application level setup.

1. OS Level Setup
2. Application level Configuration

1. OS (RHEL5.8) level Setup
At Os level we need to do bellow setups before configuring Application level
Sendmail should be installed by default when you install RedHat Linux. If it is not then you need to install the Sendmail RPM’s with the Red Hat distribution Sendmail RPM’s (In Linux 5.8) 

[root@sujeet ~]# rpm -qa |grep sendmail*

sendmail-8.13.8-8.1.el5_7

sendmail-cf-8.13.8-8.1.el5_7

1.Enable the SMTP and DEVECOT services

In order to configure Workflow notification mailer We need to enable theSMTP and DEVECOT services at the OS level.

Install Dovecot RPM if not installed 

[root@sujeet ~]# rpm -qa |grep dove*

dovecot-1.0.7-7.el5_7.1

Start the Dovecot services 

Start the services from Command 

[root@sujeet ~]# /etc/init.d/dovecot restart

Or 

Start Dovecot services from Server 

Navigation to enable these services

System –>Administration –> Server Settions–>Services

Start the dovecot services then save 

Check the Dovecot services 
[root@sujeet ~]# telnet 192.168.125.125 

2.Check SMTP and IMAP services

Check weather SMTP and IMAP services are working or not by using following command

#telnet <domain_name> smtp

#telnet <domain_name> imap

1. 1. If the above commands doesn’t start then Goto the below location

$cd /etc/mail/

Edit the file sendmail.mc and change DAEMON_OPTIONS as below

DAEMON_OPTIONS(‘port=25,Addr=192.168.125.125,Name=MTA’)dnl

2.2 Now configure a Mailserver in the OS as follows

2.2.1 Navigation

Applications—>Internet —>Email

Specify fullname: eg :applmgr

Email address :sonujha86@gmail.com

2.2.2 In the Receiving mail setup window specify the

Hostname : ceserp.computechcorp.net

Username : applmgr

click Forward

2.2.3 In the sending mail setup window specify

Hostname : ceserp.computechcorp.net

click Forward

1. 1. 1. At last click on Apply to save the settings

3.Configuring WF_Mailer setup

Navigation:

Logon to Oracle Applications as System Adminstrator resposibility.

click on Workflow:Oracle Applications Manager : workflow manager

Note:Before configuring WorkflowNotification Mailer setup Make sure that we create two folders in our Mail account as

1.PROCESSED

2.DISCARD

If the WorkFlow MAILER is not configured in the the Oracle Applications then the following screen appears as Notification Mailers : Unavailable

–Start all the Concurrent Managers related to workflow..

/*Click on configuration components*/

Start all the available configuration service components

To Start all the available configuration service components

Go to Related Links –> Notification Mailers then start all services. Then click on

Notification Mailers : Unavailable

Then the following screen appears:

Click on edit.

Then there will be 8 steps to be complete the WF_MAIL configuration setup

1.DEFINE

Here nothing to do. Click on Next button.

2.DETAILS

Here based on the requirement change the values.For basic requirement no changes are needed.

Click on next.

3.EMail Servers

1. In General section.

Here no need to change the General section.

2. InBound EmailAccount section

specify

Inbound Protocol :IMAP

Inbound servername :ceserp.computechcorp.net

Username: applmgr

password : applmgr <password what we specified at OS level>.

3. Outbound MailAccount section

specify

Outbound Protocol : SMTP

Outbound servername: ceserp.computechcorp.net

Testaddress : bhanu.sanam@gmail.com

4. Email Processing secion

specify

Processed folder :PROCESSED

Discard folder : DISCARD

Then click on Next

4.MessageGeneration

From: u can specify u r own tag name

Reply-to Address :applmgr@ceserp.computechcorp.net

No need to change the rest of things

Click on Next.

5.schedule Events

Nothing to do Here .

Click on Next.

6.Tags

Nothing to do here .

Click on Next.

7.Test

Test the configuration by specifying the Recipient role:Application level User Name

(When Creating user we need to specify mail address field).Click on “send text message”.

click on Next

8.Review.

The above screens shows all the details what we configured till now.

Click on Finish to exit the setup.