4 Replies Latest reply: Jan 22, 2009 12:20 PM by 612348 RSS

    WorkFlow mail

    612348
      More problems with workflow. Daily we have to bounce the workflow mail queue inorder for email to be sent.
      Anybody had this problem before or can recommend a solution. We are scheduled for GOLIVE March 30.

      We have a open SR with Oracle but nothing has been resolved.

      We have at this point (per Oracle's request):

      +1. 2. rebuild the notification_out queue+

      REBUILD THE WF_NOTIFICATION_OUT QUEUE

      STEP 1. Stop the mailer, listeners and service compoment container in that order.


      STEP 2. Run $FND_TOP/sql/wfntfqup.sql

      a- sqlplus apps/ @$FND_TOP/patch/115/sql/wfntfqup apps

      applsys

      This job purges the wf_notification_out outbound message queue and repopulates from the WF_NOTIFICATION table:

      STEP 3. ensure that this index is in place:
      SQL> elect index_name,column_name,column_position
      from dba_ind_columns
      where table_name='WF_NOTIFICATION_OUT' and
      table_owner='APPLSYS'
        • 1. Re: WorkFlow mail
          Srini Chavali-Oracle
          Can you pl check the workflow mailer logs (should be titled FND*txt in $APPLCSF/APPLLOG directory) to see if there are any errors reported and post them there ? What version of Apps are you implementing and what ATG RUP version are you on ? Did this issue surface recently ?

          HTH
          Srini
          • 2. Re: WorkFlow mail
            Hussein Sawwan-Oracle
            Please review the following notes, it should be helpful:

            Note: 736898.1 - How to Rebuild Mailer's Queue when it is Inconsistent or Corrupted ?
            https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=736898.1

            Note: 372933.1 - How to purge e-mail notifications from the workflow queue so the e-mail is not sent
            https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=372933.1

            Note: 431399.1 - Notification Mailer Log Grows Rapidly When Notification Mailer Is Started
            https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=431399.1

            Note: 560472.1 - Workflow Mailers Not Sending Notifications
            https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=560472.1
            • 3. Re: WorkFlow mail
              625827
              seems like you have junk emails in your wfmailer inbox. Means the id you used to configure your wf mailer account has some junk emails in Inbox which wfmailer is not able to handle and it remains there. Do one thing, configure new mailbox with outlook or some other client with same credentials you used to configure wfmailer and see what you see in inbox, just delete those email and then see the mailer. You can use below mentioned script to get alerts

              =================================================
              #!/bin/ksh
              #Script to monitor WF Mailer status
              . .env

              cd /tmp
              sqlplus /nolog << EOF
              connect apps/$APPSPWD
              set heading off
              set pages 0
              set feedback off
              spool monitor_mailer.lst
              select COMPONENT_STATUS from FND_SVC_COMPONENTS where COMPONENT_ID='10006';
              spool off
              EOF
              MONITOR=`grep -v "SQL>" monitor_mailer.lst|sed s/\ //g`

              if [ $MONITOR != "RUNNING" ] ; then
              echo $MONITOR | mailx -s "WFMAILER: Check Workflow Mailer STATUS" xyz@gmail.com
              fi
              /bin/rm -f /tmp/monitor_mailer.lst
              =======================================================
              • 4. Re: WorkFlow mail
                612348
                Thanks for the great assistance.

                Turns out that we had a corrupted record in the wf_notification_out table. That record has been removed and everything seems to be working, today as least. I will keep everyone posted.