Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: script to check / clear wf mailer queue

  1. #1

    script to check / clear wf mailer queue

    can anyone provide me script to clear wf mailer queue
    in R12

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    You should schedule 3 concurrent Programs to manage Workflow,
    The “Workflow Background Engine” Program will also take care of Deferred and Timed Out Requests.

    “Workflow Background Engine” = Atleast every 30 mins
    “Purge Obsolete Workflow Runtime Data” = once a week
    “Workflow Control Queue Cleanup” = once in 12 hours

    The Notification Mailer reads notifications to send from the queue WF_NOTIFICATION_OUT.
    Select from the queue-view for the pending Notifiacation :

    A) To check for a particular Notification :

    select notification_id,msg_state,msg_id,role,corrid,enq_t ime,deq_time
    from (select msg_id, o.enq_time, o.deq_time, msg_state
    ,(select str_value
    from table (o.user_data.header.properties)
    where name = 'NOTIFICATION_ID') notification_id
    , (select str_value
    from table (o.user_data.header.properties)
    where name = 'ROLE') role
    , (select str_value
    from table (o.user_data.header.properties)
    where name = 'Q_CORRELATION_ID') corrid
    from applsys.aq$wf_notification_out o)
    where notification_id=<notification_id>
    and rownum=1;

    If you received a result on the query, the notification is in the queue.

    B) To check the status of the queue :

    select name,enqueue_enabled,dequeue_enabled
    from dba_queues
    where name='WF_NOTIFICATION_OUT';


    C) If the query returned results and the msg_state is ‘READY’, the notification is still being processed.
    The notification is still in the deferred queue, waiting to be picked up by the ‘Workflow Deferred Notification Agent Listener’.
    Check the status of this listener. And if needed, check its logfile.
    Try Restarting this Workflow Agent Listeners.

    D) The last option :
    Clear and repopulate the Workflow Queues.
    Can be rebuild by :

    sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup APPS apps APPLSYS

+ Reply to Thread

Similar Threads

  1. can anyone provide me script to know the pending notification in oracle wf mailer
    By deviilm17 in forum SCRIPTS : Concurrent Manager and Workflow
    Replies: 2
    Last Post: 11-12-2011, 04:38 PM
  2. Script to check the Database space
    By Amit in forum Database Scripts
    Replies: 0
    Last Post: 11-03-2011, 03:53 PM
  3. Health Check script for RAC DB
    By Amit in forum Database Scripts
    Replies: 0
    Last Post: 11-03-2011, 12:41 PM
  4. Script to check if SSO in Enabled
    By Amit in forum SCRIPTS : Users,Profiles,Applications,Responsibilities,Forms,JDBC,Patch,Versions,Products
    Replies: 0
    Last Post: 11-01-2011, 03:29 PM
  5. Check any particular Alert Message email has be pending by Mailer
    By Hemant in forum SCRIPTS : Concurrent Manager and Workflow
    Replies: 0
    Last Post: 11-01-2011, 09:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics