Workflow notification mailer setup in oracle apps R12 - Office 365

Check where telnet services are enabled to mailserver

[root@vis Desktop]# telnet smtp.office365.com 587

Trying 132.245.41.98...

Connected to smtp.office365.com.

if not install tel net using yum or rpm

[root@vis Desktop]# yum install telnet

outbound
---------
1) Configure the Mailer Outbound with the given SMTP server from backend as  

  a. Run the $FND_TOP/sql/afsvcpup.sql script to set below parameters

   # cd $FND_TOP/sql
   # sqlplus apps/apps @afsvcpup.sql 

  b. Component Id: 10006  SSL Trust store: 10243 
     /u01/vis/visapps/apps/fs1/EBSapps/comn/util/jdk64/jre/lib/security/cacerts

  c. Component Id: 10006   Outbound Server Name: 10079 
     server name: smtp.office365.com

  d. Component Id: 10006  OutboundUser: 10302
     Username: workflowtest-elh@expolanka.com

  e. Component Id: 10006  Outbound Security Protocol: 10341 
     Connection Security: STARTTLS

  f. Component Id: 10006  Outbound Thread Count: 10086 
     Outbound Thread Count:  1 

2) Set the Outbound user password by running the script wfmlrpwupd_out.sql as below. 
   sqlplus apps/apps @wfmlrpwupd_out.sql  <Outbound_pwd> <Mailer_comp_ID>
   *** copy the wfmlrpwupd_out.sql to user home and set the permission.

3) Now disable the Inbound Processing from backend 
   
 a. Component Id: 10006 , Inbound Thread Count: 10082 
    Inbound Thread Count: 0

4) Set Reply-to Address
   Component Id: 10006 , Reply-to Address: 10089    
   Reply-to Address: workflowtest-elh@expolanka.com

* Try out test mailer and see if the test mail goes. 

* Once the above is successful then go for the inbound processing.  


5) Go to OAM and start all try to see if the mailer comes up for the outbound processing.

inbound 
----------

1) Configure the Mailer Inbound with given IMAP server from backend as below 

 a. Run the $FND_TOP/sql/afsvcpup.sql script to set below parameters

   # cd $FND_TOP/sql
   # sqlplus apps/apps @afsvcpup.sql 

 b. Component Id: 10006   Inbound Server: Name10069   
    Server Name:  outlook.office365.com:143

 c. Component Id: 10006  Username : 10054
    Username: workflowtest-elh@expolanka.com

 d. Component Id: 10006  Inbound Security Protocol: 10340 
    IMAP Security Protocol: STARTTLS

 e. Component Id: 10006  Inbound Thread Count: 10082 
    Inbound Thread Count: 1 

2)  Set the Inbound user password by running the script wfmlrpwupd_in.sql as below. 
     
     # sqlplus apps/apps @wfmlrpwupd_in.sql
      <Inbound_pwd> <Mailer_comp_ID>          comp_id: 10006

      *** copy the wfmlrpwupd_out.sql to user home and set the permission.

3) Now stop the Notification Mailer component from Service Components UI page and then start it 

4) Verify that Mailer is running fine now and Inbound is enabled and all the Inbound parameters are correctly set 

5) Respond to some email notifications and verify that Mailer is processing the Inbound messages 

--------------------------------------------------------------------------------------------------------------

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.

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)