Oracle Workflow Related.

Purge WF_NOTIFICATION_OUT queue cd $FND_TOP/patch/115/sql
sqlplus apps/ @wfntfqup.sql APPS (appspwd) APPLSYS
e.g sqlplus apps/apps @wfntfqup.sql APPS apps APPLSYS

This purges the WF_NOTIFICATION_OUT queue and rebuilds it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications
waiting in the queue to be sent. It will then populate the queue with the current data in the WF_NOTIFICATIONS table.
Only notifications with mail_status = 'MAIL' and status = 'OPEN' will be re-enqueued in the WF_NOTIFICATION_OUT queue and sent by the mailer.

Workflow TEST Address Update
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10093

Enter a value for the parameter : WFdevUsers@abc.com

Setup Test address/override address for WF
Below is the script to update the override address from backend. You do not need the verification code to set the override address using the below script

update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);


Workflow From Address Update :
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10065

Enter a value for the parameter : Lenovo DEV Workflow Mailer"

set overriding address:
update FND_SVC_COMP_PARAM_VALS
set parameter_value = 'Configuration.Workflows@abc.com'
where PARAMETER_ID = 10057;


Scipt to see workflow configuration
SQL> select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
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
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


SQL to monitor (check status) of Workflow Notification Mailer (Java)

SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

sqlplus -s system/${PASSWD}@${ORACLE_SID} << SQLEND >${script}/apps/wf_status.out
set linesize 90
col COMPONENT_NAME format a50
col COMPONENT_status format a20
spool ${script}/apps/wf_status.lst
select COMPONENT_ID,COMPONENT_NAME,COMPONENT_STATUS from apps.fnd_svc_components
where COMPONENT_ID not in (10000,10001,10020,10021,10022) and COMPONENT_STATUS <> 'RUNNING';

select mail_status, count(*) from apps.wf_notifications
where status = 'OPEN'
and mail_status in ('MAIL','ERROR')
group by mail_status;
spool off"



To see error message for a workflow notification
SQL> select ERROR_MESSAGE from wf_item_activity_statuses_v WHERE NOTIFICATION_ID = 7377659;

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address

E-Mail Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED and
b) Notification mail_status is MAIL or INVALID and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(’&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)


Workflow version embedded in 11i
************************
Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0

You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.

To Configure Workflow Notification Mailer need below information

PARAMETER_NAME VALUE
------------------------------ ------------------------------
Inbound Server Name wfmailer.abc.com
Username wftst
Password oracle
Outbound Server Name wfmailer.abc.com
Reply-to Address wftst@abc.com


Taking a backup of the workflow configuration
cd $HOME/
sqlplus “/ as sysdba”
spool wf_mailer.log
set lines 130
set pages 200
col value format a30
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
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


To schdule Gather schema stats and purge workflow runtime data

Login into application as sysadmin and schedule below Requests.
Gather Schema Statistics (ALL , 20% , degree 6 ) to run every SUN and WED
Workflow Background Process to run every 10 mts and Apply the interval as :From the completion of the prior run
Purge Obsolete Workflow Runtime data - Every week
Workflow Control Queue Cleanup - Every 12 hours


To update WF notification status
IMPORTANT STEP ! Connect to SQL*PLUS as APPS user and do the following steps
update applsys.wf_notifications
set status ='CLOSED', mail_status ='SENT', end_date ='01-JAN-01'
where mail_status='MAIL';
update wf_notifications set status=’CLOSED’;
commit;"
Select distinct status from wf_notification" this should return only one value CLOSED.
or simple do
update wf_notifications set status = 'CLOSED', mail_status = 'SENT';
commit;


Update workflow a/c pwd from backend
set define off
update APPLSYS.FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE= '_@#0@##^90@#!4^86#!$^68$#9$4#@@$6!!!9#0@`$B9+}*0&9&@0&8#|'
where
PARAMETER_ID=
( select parameter_id from APPLSYS.FND_SVC_COMP_PARAMS_B where parameter_name = 'INBOUND_PASSWORD');

To check WF mail status
select count(*), mail_status
from wf_notifications
where begin_date > sysdate - 1
group by mail_status;

There are 141 messages with status `MAIL¿, this status should remain very short and then progress to status `SENT¿.
: OAM Login > Site Map > Workflow > Service Components.
These same detail are also given in the output from script $FND_TOP/sql/wfver.sql


To check the WF status from backend

check the status of Agent Listeners:

set pagesize 400
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
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;


Typical output from this

COMPONENT_NAME STARTUP_MODE COMPONENT_STATU
--------------------------------------------- --------------- ---------------
WF_JMS_IN Listener(M4U) AUTOMATIC RUNNING
Workflow Deferred Agent Listener AUTOMATIC RUNNING
Workflow Deferred Notification Agent Listener AUTOMATIC RUNNING
Workflow Error Agent Listener AUTOMATIC RUNNING
Workflow Inbound Notifications Agent Listener AUTOMATIC RUNNING
Workflow Java Deferred Agent Listener AUTOMATIC RUNNING
Workflow Java Error Agent Listener AUTOMATIC RUNNING
Workflow Notification Mailer AUTOMATIC RUNNING
ECX Inbound Agent Listener MANUAL STOPPED
ECX Transaction Agent Listener MANUAL STOPPED
Web Services IN Agent MANUAL STOPPED
Web Services OUT Agent MANUAL STOPPED
Workflow Inbound JMS Agent Listener MANUAL STOPPED

13 rows selected.


Steps to start/stop notification mailer

1.a Check workflow mailer service current status
sqlplus apps/

select running_processes
from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';

Number of running processes should be greater than 0

1.b Find current mailer status
sqlplus apps/

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');

Possible values:
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

2. Stop notification mailer
sqlplus apps/

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Stop Mailer
--------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/

3. Start notification mailer
sqlplus apps/

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
--------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/


TO configure workflow
Workflow Mailer Notification settings.
Log on to OAM
Click on Workflow Administrator -> Business Event Local System
Change VIS.ABC.COM to PROD.ABC.COM

Click on Workflow Manager ->Notification Mailer -> Workflow Notification Mailer -> Edit
Edit outbound Email Account (SMTP) -> Server Name =Concurrent Tier
IMAP Server -> Server Name = xxx.xxx.x.xx
Username/password: wfvis/xxxxx :
Reply-To Address: wfvis@abc.com

Click on Advance -> Next->Next
Under General Tab
Mailer Node =WFVIS
Under IMAP: Enter server IP address, username and password of wfvis
Under Outbound Email Account
Outbound Server Name= concurrent tier
Click Next –>Next….Save and Apply

Workflow Version with Apps
Connect to Database as apps user
SQL> select TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0


Workflow logfile location
$APPLCSF/$APPLLOG with filename like FNDCPGSC[pid].txt

After executing autoconfig make sure Workflow System Administrator is NOT set to SYSADMIN .
Please set this to ""Workflow Administrator Web (New)""
Following script will take care of this .

sqlplus apps/""pwd""
SQL> update wf_resources set text = 'FND_RESP|FND|FNDWF_ADMIN_WEB_NEW|STANDARD' where name = 'WF_ADMIN_ROLE';

1 row updated.

SQL> commit;

Commit complete.

or xml changes can be done link this.
$ grep -i s_wf_admin_role PROD_mdsxaorit.xml
username oa_var=""s_wf_admin_role"">FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD

To set the Workflow Administrator from Sys Admin to Workflow Administrator
Navigate to Responsibility -- Workflow Administarator
then Administrator Workflow -- Administration

On top we will find Workflow Configuration :

Workflow System Administrator :: This value we need to change to Workflow Administrator

Or we can update from backend
Update wf_resources set text='FND_RESP|FND|FNDWF_ADMIN|STANDARD' where name = 'WF_ADMIN_ROLE';

Or can be set in xml,
grep -i s_wf_admin_role TRAIN2_vcosxaor09.xml username oa_var=""s_wf_admin_role"">FND_RESP|FND|FNDWF_ADMIN|STANDARD


modify this profile option to have the value of an actual App Server
WF: Workflow Mailer Framework Web Agent : http://hostname.domainname:8000

Make sure following parameters are set for Workflow :
As system administrator –
Oracle Applications Manager
Workflow Manager

Inbound Server Name wfmailer.domain.com
Outbound Server Name wfmailer.domain.com
From The lenovo PROD Workflow Mailer
Reply-to Address wfvis@domain.com


Workflow smtp port 25 and imap port 143
[root@hostname]# telnet wfmailer 143