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

Thread: Workflow Mailer Debugging Script for Debugging Emails issues

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    Delhi, India, India
    Posts
    128

    Workflow Mailer Debugging Script for Debugging Emails issues

    Workflow Mailer Debugging Script for Debugging Emails issues

    This article containts various Workflow and Business Event debugging scripts.
    --> Checking workflow Components status wheather are they running or stopped.
    select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
    from fnd_svc_components
    where component_type like 'WF%'
    order by 1 desc,2,3;

    -->Query to get the log file of active workflow mailer and workflow agent listener Container
    --Note All Workflow Agent Components logs will stored in single file ie. container log file.

    select fl.meaning,fcp.process_status_code,
    decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
    fcp.concurrent_process_id,os_process_id, fcp.logfile_name
    from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
    where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
    and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
    fl.lookup_code=fcp.process_status_code
    and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
    order by fcp.logfile_name;

    -->Linux Shell script Command to get outbound error in Mailer
    grep -i '^[[A-Za-z].*(in|out).*boundThreadGroup.*(UNEXPECTED|ERROR).* exception.*'<logfilename> | tail -10 ;

    --Note: All Mailer log files starts with name FNDCPGSC prefix
    --Linux Shell script Command to get inbound processing error in Mailer
    grep -i '^[[A-Za-z].*.*inboundThreadGroup.*(UNEXPECTED|ERROR).*except ion.*'<logfilename> | tail -10 ;

    ---Query to Check Workflow Mailer Backlog
    --State=Ready implies that emails are not being sent & Waiting mailer to sent emails
    select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by
    tab.msg_state ;

    -->Check any particular Alert Message email has be pending by Mailer
    select decode(wno.state,
    0, '0 = Pending in mailer queue',
    1, '1 = Pending in mailer queue',
    2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
    3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
    to_char(DEQ_TIME),
    wno.user_data.TEXT_VC
    from wf_notification_out wno
    where corrid='APPS:ALR'
    and upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';

    --Check The Workflow notification has ben sent or not
    select mail_status, status from wf_notifications where notification_id=<notification_id>
    --If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the
    notification
    --If mail_status is SENT, its means mailer has sent email
    --If mail_status is Null & status is OPEN, its means that no need to send email as notification
    preference of user is "Don't send email"
    --Notification preference of user can be set by user by loggin in application + click on preference + the notification preference
    -- Check Wheather workflow background Engine is workfing for given workflow or not in last 2days
    -- Note: Workflow Deferred activities are run by workflow background engine.
    select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.*
    from fnd_concurrent_requests a
    where CONCURRENT_PROGRAM_ID = (select concurrent_program_id from fnd_concurrent_programs where
    CONCURRENT_PROGRAM_NAME='FNDWFBG')
    and last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'
    order by last_update_date desc

    -- Check wheather any business event is pending to process
    -- ie. Query to get event status & parameters value of particular event in wf_deferred table.
    select wd.user_Data.event_name,wd.user_Data.event_key,
    rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by
    n.name) as serial_no,
    n.NAME Parameter_name, N.value Parameter_value ,
    decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,
    wd.user_Data.SEND_DATE,
    wd.user_Data.ERROR_MESSAGE,
    wd.user_Data.ERROR_STACK,
    wd.msgid,wd.delay
    from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n
    where lower(wd.user_data.event_name)='<event Name >'
    order by wd.user_Data.event_name, wd.user_Data.event_key, n.name

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    Thanks Amit! This is really very helpful to diagnose stuck notifications , as well as Workflow Service Problems

+ Reply to Thread

Similar Threads

  1. How to test SMTP and IMAP for the Workflow Notification Mailer
    By Hemant in forum Concurrent Processing Server / Workflow
    Replies: 0
    Last Post: 07-04-2012, 12:43 AM
  2. no workflow emails are coming out of the system WITH wf_notifications.mail_status NUL
    By Amit in forum Concurrent Processing Server / Workflow
    Replies: 0
    Last Post: 11-04-2011, 09:51 AM
  3. How to troubleshoot Workflow Notificatio Mailer in R12
    By deviilm17 in forum Concurrent Processing Server / Workflow
    Replies: 1
    Last Post: 11-03-2011, 09:08 AM
  4. Retrieve the log file of active workflow mailer and workflow agent listener Container
    By Hemant in forum SCRIPTS : Concurrent Manager and Workflow
    Replies: 0
    Last Post: 11-01-2011, 09:47 AM
  5. Workflow Mailer Debugging Script for Debugging Emails issues
    By Amit in forum Concurrent Processing Server / Workflow
    Replies: 1
    Last Post: 11-01-2011, 09:43 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