How to update override email address using backend sql query in EBS 11i/12.x?

Query to check Override address in EBS workflow notification Mailer:

SQL>

 select fscpv.parameter_value

    from fnd_svc_comp_params_tl fscpt

    ,fnd_svc_comp_param_vals fscpv

    where fscpt.display_name = 'Test Address'

    and fscpt.parameter_id = fscpv.parameter_id;   

PARAMETER_VALUE

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

NONE

SQL Query to update Override email address from backend in EBS :

SQL> update fnd_svc_comp_param_vals

set    parameter_value = 'TEST_EMAIL@DOMAIN.COM'

where  parameter_id =

( select parameter_id

 from   fnd_svc_comp_params_tl

 where  display_name = 'Test Address');  

1 row updated.

SQL> commit;

Commit complete.

Verify that override email address is updated:

SQL> select fscpv.parameter_value

    from fnd_svc_comp_params_tl fscpt

    ,fnd_svc_comp_param_vals fscpv

    where fscpt.display_name = 'Test Address'

    and fscpt.parameter_id = fscpv.parameter_id;  

PARAMETER_VALUE

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

TEST_EMAIL@DOMAIN.COM

SQL>

How to modify workflow administrator role in Oracle EBS?

By default, Workflow System administrator role is set to SYSADMIN User in Oracle EBS 11i/R12. This means only SYSADMIN user can have access to view Oracle workflow notifications of all the EBS users.
We can modify this to any other EBS user who has administrator rights or assign to a reponsibility. For Example, If we change Workflow System administrator to Responsinility name "Workflow Administrator Web Applications", All users who are assigned with this responsibility can have access to view workflow details owned by other Oracle EBS users.

Let us identify the scenarios which require Workflow System administrator privilege.
- Workflow notification is errored and not processed to next approver
- There is an urgent requirement to delegate the workflow notification to another approver.
- Check the status of workflow notification
- Retry / Rewind the workflow notification
- Check the pending notifications for any Oracle EBS user
- Check Workflow Status Diagram

Steps to modify Workflow system administrator role in Oracle EBS 11i/R12

Workflow System Administrator can be changed in below ways.

1.update wf_resources set text='&Enter_Admin_Name' where name='WF_ADMIN_ROLE';

e.g
update wf_resources set text='FND_RESP1:20420' where name='WF_ADMIN_ROLE';


2. Change the value of Context file parameter s_wf_admin_role and run Autoconfig

$ cat $CONTEXT_FILE|grep wf_admin
         <username oa_var="s_wf_admin_role" customized="yes">SYSADMIN</username>


3. Change it from Workflow Administrator Web Applications responsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration


[Note: Ensure that context file parameter "s_wf_admin_role" is updated with modified value to preserve changes during autoconfig Run]

Oracle Workflow:How to modify workflow administrator role in Oracle EBS?

By default,Oracle Workflow System administrator role is set to SYSADMIN User in Oracle EBS 11i/R12. 
This means only SYSADMIN user can have access to view Oracle workflow notifications of all the EBS users.
We can modify this to any other EBS user who has administrator rights or assign to a reponsibility.
For Example, If we change Oracle Workflow System administrator to Responsinility name "Workflow Administrator Web Applications", All users 
who are assigned with this responsibility can have access to view workflow details owned by other Oracle applications users.

Let us identify the scenarios which require Workflow System administrator privilege.
- Workflow notification is errored and not processed to next approver
- There is an urgent requirement to delegate the workflow notification to another approver.
- Check the status of workflow notification
- Retry / Rewind the workflow notification
- Check the pending notifications for any Oracle EBS user
- Check Workflow Status Diagram

Steps to modify Workflow system administrator role in Oracle EBS 11i/R12

Oracle Workflow System Administrator can be changed in below ways.

1.update wf_resources set text='&Enter_Admin_Name' where name='WF_ADMIN_ROLE';

e.g
update wf_resources set text='FND_RESP1:20420' where name='WF_ADMIN_ROLE';


2. Change the value of Context file parameter s_wf_admin_role and run Autoconfig

$ cat $CONTEXT_FILE|grep wf_admin
         <username oa_var="s_wf_admin_role" customized="yes">SYSADMIN</username>


3. Change it from Workflow Administrator Web Applications responsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration


[Note: Ensure that context file parameter "s_wf_admin_role" is updated with modified value to preserve changes during autoconfig Run]

How to Purge Workflow Notifications from Mailer Queue, so that Email is not sent to Users?

What to do when Workflow Notification  Mailer was not running properly for a certain period of time and accumulated all the emails in Workflow Queue.
We can Purge the workflow Mails which are not required by updating mail_status='SENT' for unwanted notifications in wf_notifications table.

Here are a couple of reasons to Purge Workflow Notifications

a)The end user may not want to receive outdated email.
b)The Workflow Queue has accumulated a lot of emails during the issue period, and processing has become very slow.

Below are the steps to Purge Workflow Notifications which are not required

1.First Take a Backup of wf_notifications table  and stop the Workflow Notification Mailer

2. Verify notifications from WF_NOTIFICATIONS table that has potential for being sent

SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     where status in ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     order by notification_id;
   
3. Narrow down the emails which are not to be sent by the Mailer from a specific date range.

SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     Where Status In ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     and begin_date < sysdate-30              -- List only emails older than 30 days ago
     order by notification_id;

4. Set Mail_status='SENT' in WF_NOTIFICATIONS

SQL>update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and begin_date < sysdate-30;      

SQL>Commit;


This will update  notifications ,which are older than 30 days and waiting to be sent by the mailer to SENT.
Therefore the notifications will not get emailed when the Mailer is restarted.

5.Run the script $FND_TOP/patch/115/sql/wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild 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.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer. (or CANCELED and INVALID if certain concurrent reports are run)

Example :
$ sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr


Example Syntax:
$ sqlplus apps/***@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps ***** applsys


6.Now start the Workflow Notification Mailer.