Resend, OPEN,CANCELLED Workflow Notification mails

Many notification mailers stuck in the mailer queue (status='OPEN' and mail_status='MAIL')

SQL> select count(*) from wf_notifications where status='OPEN' and mail_status='MAIL';

select count(*),status, MAIL_STATUS from wf_notifications group by status, MAIL_STATUS order by status;

or , for a more detailed list:

select subject,from_user,notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

Solution:

the original problem could be caused by any of the following - mail server problems, network problems

Resend them by doing the following:

1) stop workflow mailer

2) cd $FND_TOP/patch/115/sql

3) sqlplus APPS/<passwd> @wfntfqup.sql APPS <passwd> APPLSYS

4) start workflow mailer and monitor the queue

Workflow mailer log file:

cd $APPLCSF/$APPLLOG

/u02/PROD/inst/apps/PROD_??????/logs/appl/conc/log >ls -ltr FNDCPG*

To purge old pending notification messages:

update wf_notifications set mail_status = 'SENT' where status = 'OPEN';

$FND_TOP/sql/wfrmitms.sql (to delete status information in Oracle Workflow runtime tables for a particular item type),
$FND_TOP/sql/wfrmitt.sql (to delete all data in all Oracle Workflow design time and runtime tables for a particular item type).
$FND_TOP/sql/wfrmall.sql (to delete all data in all Oracle Workflow design time and runtime tables for all item type).

Check workflow mailer service current status

 sqlplus apps/<apps password>
  select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

 Find current mailer status

  sqlplus apps/<apps password>
  select component_status
    from apps.fnd_svc_components
   where component_id = 
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');

  Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

How to resend a missed out e-mail notification which was sent out for approval of Purchase orders.

To implement the solution perform the following steps:

1. Update the WF_NOTIFICATIONS table using the following script:

SQL> update wf_notifications set status ='OPEN', mail_status ='MAIL', end_date=NULL where notification_id=<NID>;

SQL> commit;

The example below, wil create a sql script which you can run to do the update for all OPEN/MAIL workflow mails:

select 'update wf_notifications set status ='||'''OPEN'''||',
mail_status='||'''MAIL'''||', end_date=NULL
where notification_id='||notification_id||';' 
from wf_notifications
where status='OPEN'
and mail_status='MAIL';

NOTE: HERE THE end_date COLUMN IS NOT UPDATED.

2. Then execute the following command:

SQL> execute wf_xml.EnqueueNotification(<NID>);

SQL>commit;

The example below will spool a sql script which you can run for all OPEN/MAIL workflow mails:

select 'execute wf_xml.EnqueueNotification('||notification_id||');' from wf_notifications where status='OPEN' and mail_status='MAIL';

and confirm that queue WF_SMTP_O_1_QUEUE is started

execute DBMS_AQADM.start_queue('APPLSYS.WF_SMTP_O_1_QUEUE',TRUE,TRUE);

Possible values for <wished_preference> are:

QUERY (corresponds to preference value "Do not send me mail")
MAILTEXT (corresponds to preference value "Plain text mail")
MAILATTH (corresponds to preference value "Plain text mail with HTML attachments")
MAILHTML (corresponds to preference value "HTML mail with attachments")
MAILHTM2 (corresponds to preference value "HTML mail")
SUMMARY (corresponds to preference value "Plain text summary mail")
SUMHTML (corresponds to preference value "HTML summary mail")
DISABLED (corresponds to preference value "Disabled")

NETDEV WATCHDOG: eth*: transmit timed out

This is error occur when ever the network interface is hanged, It has something to do with TCP Segmentation Offload (TSO)

below steps is the workaround and it has solved the problem.


1. rebooted the server in single use mode
2. restarted the network interface and started the server in normal mode
3. ethtool -K eth0 tso off

If the network interface is configured as bound or Team.. then reconfigure it. 

FND STATUS_CODE

A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

PHASE_CODE column
C Completed
I Inactive
P Pending
R Running

linux ps --option

linux ps --option

PROCESS STATE CODES
Here are the different values that the s, stat and state output specifiers
(header "STAT" or "S") will display to describe the state of a process.
D    Uninterruptible sleep (usually IO)
R    Running or runnable (on run queue)
S    Interruptible sleep (waiting for an event to complete)
T    Stopped, either by a job control signal or because it is being traced.
W    paging (not valid since the 2.6.xx kernel)
X    dead (should never be seen)
Z    Defunct ("zombie") process, terminated but not reaped by its parent.

For BSD formats and when the stat keyword is used, additional characters may
be displayed:
<    high-priority (not nice to other users)
N    low-priority (nice to other users)
L    has pages locked into memory (for real-time and custom IO)
s    is a session leader
l    is multi-threaded (using CLONE_THREAD, like NPTL pthreads do)
+    is in the foreground process group

difference between base table and multi-org table?

multi_org tables have the colums org_id and end's with _all but not in base tables

BASE TABLES ENDS WITH _B, TABLES WHICH ARE HAVING _B HAS TO BE TRANSLATED TO MULTIPLE LANGUAGES
EXAMPLE ITEM INFORMATION IS STORED IN BASE TABLE WHICH HAS TO BE TRANSLATED FOR THIS WE HAVE ANOTHER TABLE WHICH ENDS WITH _TL

_ALL : Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.
_TL  These tables provide multiple language support.
_B these are the BASE tables.
_F these are date tracked tables, which occur in HR and Payroll.
_V tables are the views created on base tables
_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').
_S are sequences, used for finding new values for the primary key of a table.
_A are Audit Shadow Tables
_AVN and _ACN are Audit Shadow Views (when data was changed, and with what value

Linux exit Variables

$$ = The PID number of the process executing the shell.

$? = Exit status variable.

$0 = The name of the command you used to call a program.

$1 = The first argument on the command line.

$2 = The second argument on the command line.

$n = The nth argument on the command line.

$* = All the arguments on the command line.

$# The number of command line arguments.

Find the model and serial number of a Linux server

dmidecode -t system