Skip to content

Oracle Workflow related frequently used queries

Published April 12, 2016 By Brijesh Gogia

Below are some of the useful workflow script that we use frequently in our 11i/R12 instances

VERIFY THE WORKFLOW IMAP SETTING

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 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 ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO') 
order by c.component_id, c.component_name,p.parameter_name;

 

SET OVERRIDE EMAIL ADDRESS FROM BACKEND
Example below where we updated the email address to non-existent email since the instance was non-production and we didn’t want WF emails to flow out
update fnd_svc_comp_param_vals
set    parameter_value = 'dummy@dummy.com'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);

 

CORRECT THE EMAIL PREFRENCE SO THAT USER CAN START RECEIVING EMAILS
Verify
SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference) FROM wf_roles WHERE name = upper('&USERNAME');
Update if required
exec FND_PREFERENCE.put('&USERNAME', 'WF', 'MAILTYPE', 'MAILHTML');
OR
update fnd_user_preferences
set preference_value='MAILHTML'
where preference_name='MAILTYPE'
and module_name='WF'
and user_name ='&USERNAME';

 

CHECK IF NOTIFICATION ARE GOING OUT
Run from toad/sql developer and focus on status and mail_status column.
mail_status = ‘MAIL’ means mail not yet sent
mail_status = ‘SENT’ means email went out
status =’OPEN’ means notification is open and not acted upon by user
status=’CLOSED’ means notification was acted upon by user.
To see all notifications in last 2 days
select * from wf_notifications where begin_date> sysdate-2 order by notification_id desc;
To see all notifications of the “test notification” type in last 2 days
select * from wf_notifications where begin_date> sysdate-2 and message_type='WFTESTS' order by notification_id desc;

 

CHECK ALL ENTRIES WHICH HAVE MAIL_STATUS=’MAIL’
SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;

 

TEST IF WF IMAP SERVER IS WORKING NORMAL
Try telnet IMAP session :
– login to your Concurrent  Managers node as the owner of the Conc Manager processes
– launch telnet IMAP session. Example:
telnet mailsrv.domain.com 143
– you should receive message back from IMAP Server like :
Trying <IP address> ...
Connected to mailsrv
Escape character is '^]'.
* OK Domino IMAP4 Server Release 5.0.11 ready Sun, 23 Nov 2013 10:26:49 -0800
Then enter command to login
(Note : the number “1” at the start of the command must be entered, it is mandatory) :
1 login < IMAP account > <IMAP account password>
– you should receive message back from IMAP Server like :
1 OK LOGIN completed
if ok then check the INBOX :
1 select "INBOX"
you should receive message back like :
* 23 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 0] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
if ok then logout :
1 logout

 

POST CLONE WF STEPS FOR NON-PRODUCTION INSTANCES
If your intention is to stop unnecessary WF email to go out of Non-Production instances after clone then below steps can be followed. These steps are exhaustive and  will take care that even if some DBA accidentally start WF services in the Non-production instances, WF emails won’t go out to users/suppliers

 

1) EXECUTE THE WFNTFQUP.SQL SCRIPT
Run the script 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.
sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr
Example:
sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys
 

2) EXECUTE THE WFMLPCLN SCRIPT
 The wfmlpcln.sql script is located in the $FND_TOP/sql directory. Use the script as follows:
sqlplus <user/pwd> @$FND_TOP/sql/wfmlpcln.sql
The wfmlpcln.sql script performs the following actions for all notification mailers in the Oracle E-Business Suite instance where it’s run:
1) Resets required configuration parameters to their initial values as follows:
Outbound E-mail Account (SMTP): Server Name – Null
Inbound E-mail Account (IMAP): Server Name – Null
Inbound E-mail Account (IMAP): Username – Null
Inbound E-mail Account (IMAP): Password – Null
From – Workflow Mailer
Reply-to Address – Null
HTML Agent – Null
2) Sets the override e-mail address to NONE.
3) Sets the notification mailer service component status to NOT_CONFIGURED.
4) Sets the status of the seeded subscription to the oracle.apps.wf.notification.send.group event group to DISABLED.
5) Sets the mail status to null for all notifications that formerly had a mail status of MAIL, ERROR, or FAILED, indicating that they were eligible to be sent by e-mail.

 

3) UPDATE THE WF_NOTIFICATIONS TABLE MANUALLY ALSO
A) CHECK WF_NOTIFICATIONS
SQL> select notification_id, status, mail_status, begin_date 
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';
B) UPDATE WF_NOTIFICATIONS
SQL> update WF_NOTIFICATIONS set status = 'CLOSED', mail_status = 'SENT', end_date = sysdate;
SQL> Commit;
C) CHECK WF_NOTIFICATIONS AGAIN
SQL> select notification_id, status, mail_status, begin_date 
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';
<SHOULD SHOW 0 RECORDS>

 

4) UPDATE FND_USER_PREFRENCES TABLE
UPDATE
Update FND_USER_PREFERENCES set preference_value= 'QUERY' where preference_name='MAILTYPE' and preference_value!='QUERY';
VERIFY
set lines 132
col PREFERENCE_VALUE format a45
select preference_value,count(*) from fnd_user_preferences where preference_name='MAILTYPE'  group by preference_value;
This step will also make sure that email do not reach users.

 

5) UPDATE USER’S EMAIL ADDRESSES
SQL> update fnd_user set email_address = email_address||'xxx' where email_address is not null;
SQL> update po_vendor_sites_all set email_address = email_address||'xxx', remittance_email = remittance_email||'xxx';
SQL> update wf_local_roles set email_address = email_address||'xxx' where email_address is not null;
SQL> update per_all_people_f set email_address = email_address||'xxx' where email_address is not null;
 

6) SET OVERRIDE EMAIL ADDRESS FROM BACKEND
Example below where we updated the email address to non-existent email since the instance was non-production and we didn’t want WF emails to flow out
update fnd_svc_comp_param_vals
set    parameter_value = 'dummy@dummy.com'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);

 

For Information purpose, below are the 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.1 convert 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

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 3,084

Related posts:

  1. SYSADMIN login performance issue in Oracle EBS R12
  2. Frequently used Tablespace Queries
  3. Frequently used SQL Queries for ASM
  4. Adding new user in Oracle Apps (EBS) from back end
  5. Data encryption related Oracle parameters
  6. Output Post Processor (OPP) related issues in 11i/R12
  7. Oracle Applications Database frequently used scripts
  8. KEY ORACLE BACKGROUND PROCESSES RELATED TO BACKUP AND RECOVERY
  9. Basic operations related to SQL Profile
  10. Oracle Applications Concurrent Manager related scripts

Categories:Apps (11i, R12) scripts Apps(11i, R12) Basics ORACLE APPLICATIONS

One Comment

  1. UTTAM RAWAT UTTAM RAWAT

    select * from fnd_folders where WHERE_CLAUSE like (‘%FIN_ASSY%’) ;

    Enabling Oracle Applications folder feature in a custom form — VIEW THE QUERY OF FOLDER.

    January 3, 2023 Reply
Leave a Reply