Useful Scripts for Workflow Mailer Troubleshooting

-- to find status of Workflow Notification Mailer
SELECT component_name, component_status
  FROM fnd_svc_components
 WHERE component_type = 'WF_MAILER';

-- to find failed notifications
SELECT NOTIFICATION_ID,
       MESSAGE_TYPE,
       MESSAGE_NAME,
       STATUS,
       MAIL_STATUS,
       FROM_USER,
       TO_USER
  FROM wf_notifications
 WHERE MAIL_STATUS = 'FAILED';

-- to find pending e-mail notifications by group process
SELECT COUNT (*), message_name
    FROM wf_notifications
   WHERE STATUS = 'OPEN'
     AND mail_status = 'MAIL'
GROUP BY message_name;

--to find sent e-mail notifications 
  SELECT *
    FROM wf_notifications
   WHERE STATUS = 'OPEN' AND mail_status = 'SENT'
ORDER BY begin_date DESC;

SELECT mail_status, status
  FROM wf_notifications
 WHERE 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 logging in application + click on preference + the notification preference

-- to find a message processed in WF_DEFERRED queue
SELECT *
  FROM applsys.aq$wf_deferred a
 WHERE a.user_data.getEventKey () = '';

--If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, 
--If it errored out it will be enqueued to WF_ERROR queue

SELECT wf.user_data.event_name Event_Name,
       wf.user_data.event_key Event_Key,
       wf.user_data.error_stack Error_Stack,
       wf.user_data.error_message Error_Msg
  FROM wf_error wf
 WHERE wf.user_data.event_key ='';

-- to find SUCCESS and FAIL mails 
SELECT from_user,
       to_user,
       notification_id,
       status,
       mail_status,
       begin_date
  FROM WF_NOTIFICATIONS
 WHERE status = 'OPEN';

SELECT from_user,
       to_user,
       notification_id,
       status,
       mail_status,
       begin_date,
       USER_KEY,
       ITEM_KEY,
       MESSAGE_TYPE,
       MESSAGE_NAME begin_date
  FROM WF_NOTIFICATIONS
 WHERE status = 'OPEN';

-- to find Users that notifications got stuck
SELECT MESSAGE_TYPE, COUNT (1)
    FROM wf_notifications
   WHERE status = 'OPEN' AND mail_status = 'MAIL'
GROUP BY MESSAGE_TYPE;

/******
--For Example,
MESSAGE_TYPE    COUNT(1)
WFERROR         77799
WFTESTS          2
OEOH                 156  --mails of Orders not sent
OMERROR        1632 -- Error
********/

--to find mail not received by Users
SELECT Name,
       DISPLAY_NAME,
       EMAIL_ADDRESS,  --should not be NULL
       NOTIFICATION_PREFERENCE, --Mailtext
       STATUS --Active
  FROM wf_users
 WHERE DISPLAY_NAME in ('xxx','yyy');

--to find notification not sent and waiting to mail
SELECT notification_id,
       status,
       mail_status,
       begin_date
  FROM WF_NOTIFICATIONS
 WHERE status = 'OPEN' AND mail_status = 'MAIL';

/******************************
--To debug refer: $FND_TOP/sql execute wfmlrdbg.sql
--To find Workflow logs: FNDCPGSC*.txt under $APPLCSF/$APPLOG

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 415516.1 - How to Check Whether Notification Mailer is Working or Not
Note: 831982.1 - 11i/R12 - A guide for troubleshoting Workflow Notification Emails - Inbound and Outbound
Note: 1012344.7 - Notifications Not Being Sent In Workflow
Note: 560472.1 - Workflow Mailers Not Sending Notifications
Note: 753845.1 - How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.
**********************************/

Useful Scripts for System Admins

-- to list Active Users

select fnd.user_name,

       icx.responsibility_application_id,

       icx.responsibility_id,

       frt.responsibility_name,

       icx.session_id,

       icx.first_connect,

       icx.last_connect,

       DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status

from

       fnd_user fnd,

       icx_sessions icx,

       fnd_responsibility_tl frt

where

       fnd.user_id = icx.user_id and

       icx.responsibility_id = frt.responsibility_id and

       icx.disabled_flag <> 'Y' and

       trunc(icx.last_connect) = trunc(sysdate)

order by icx.last_connect;

--to list Scheduled Programs

SELECT fcr.request_id,

            fcpt.user_concurrent_program_name

         || NVL2 (fcr.description, '(' || fcr.description || ')', NULL)

            conc_prog,

         fu.user_name requestor,

         fu.description requested_by,

         fu.email_address,

         frt.responsibility_name requested_by_resp,

         TRIM (fl.meaning) status,

         fcr.phase_code,

         fcr.status_code,

         fcr.argument_text "PARAMETERS",

         '------>' dates,

         TO_CHAR (fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested,

         TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')

            requested_start,

         TO_CHAR ( (fcr.requested_start_date), 'HH24:MI:SS') start_time,

         '------>' holds,

         DECODE (fcr.hold_flag,  'Y', 'Yes',  'N', 'No') on_hold,

         CASE WHEN fcr.hold_flag = 'Y' THEN SUBSTR (u2.description, 0, 40) END

            last_update_by,

         CASE WHEN fcr.hold_flag = 'Y' THEN fcr.last_update_date END

            last_update_date,

         '------>' prints,

         fcr.number_of_copies print_count,

         fcr.printer,

         fcr.print_style,

         '------>' schedule,

         fcr.increment_dates,

         CASE

            WHEN fcrc.CLASS_INFO IS NULL

            THEN

                  'Yes: '

               || TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')

            ELSE

               'n/a'

         END

            run_once,

         CASE

            WHEN fcrc.class_type = 'P'

            THEN

                  'Repeat every '

               || SUBSTR (fcrc.class_info, 1, INSTR (fcrc.class_info, ':') - 1)

               || DECODE (SUBSTR (fcrc.class_info,

                                    INSTR (fcrc.class_info,

                                           ':',

                                           1,

                                           1)

                                  + 1,

                                  1),

                          'N', ' minutes',

                          'M', ' months',

                          'H', ' hours',

                          'D', ' days')

               || DECODE (SUBSTR (fcrc.class_info,

                                    INSTR (fcrc.class_info,

                                           ':',

                                           1,

                                           2)

                                  + 1,

                                  1),

                          'S', ' from the start of the prior run',

                          'C', ' from the completion of the prior run')

            ELSE

               'n/a'

         END

            set_days_of_week,

         CASE

            WHEN     fcrc.class_type = 'S'

                 AND INSTR (SUBSTR (fcrc.class_info, 33), '1', 1) > 0

            THEN

                  'Days of week: '

               || DECODE (SUBSTR (fcrc.class_info, 33, 1), '1', 'Sun, ')

               || DECODE (SUBSTR (fcrc.class_info, 34, 1), '1', 'Mon, ')

               || DECODE (SUBSTR (fcrc.class_info, 35, 1), '1', 'Tue, ')

               || DECODE (SUBSTR (fcrc.class_info, 36, 1), '1', 'Wed, ')

               || DECODE (SUBSTR (fcrc.class_info, 37, 1), '1', 'Thu, ')

               || DECODE (SUBSTR (fcrc.class_info, 38, 1), '1', 'Fri, ')

               || DECODE (SUBSTR (fcrc.class_info, 39, 1), '1', 'Sat ')

            ELSE

               'n/a'

         END

            days_of_week,

         CASE

            WHEN     fcrc.class_type = 'S'

                 AND INSTR (SUBSTR (fcrc.class_info, 1, 31), '1', 1) > 0

            THEN

                  'Set Days of Month: '

               || DECODE (SUBSTR (fcrc.class_info, 1, 1), '1', '1st, ')

               || DECODE (SUBSTR (fcrc.class_info, 2, 1), '1', '2nd, ')

               || DECODE (SUBSTR (fcrc.class_info, 3, 1), '1', '3rd, ')

               || DECODE (SUBSTR (fcrc.class_info, 4, 1), '1', '4th, ')

               || DECODE (SUBSTR (fcrc.class_info, 5, 1), '1', '5th, ')

               || DECODE (SUBSTR (fcrc.class_info, 6, 1), '1', '6th, ')

               || DECODE (SUBSTR (fcrc.class_info, 7, 1), '1', '7th, ')

               || DECODE (SUBSTR (fcrc.class_info, 8, 1), '1', '8th, ')

               || DECODE (SUBSTR (fcrc.class_info, 9, 1), '1', '9th, ')

               || DECODE (SUBSTR (fcrc.class_info, 10, 1), '1', '10th, ')

               || DECODE (SUBSTR (fcrc.class_info, 11, 1), '1', '11th, ')

               || DECODE (SUBSTR (fcrc.class_info, 12, 1), '1', '12th, ')

               || DECODE (SUBSTR (fcrc.class_info, 13, 1), '1', '13th, ')

               || DECODE (SUBSTR (fcrc.class_info, 14, 1), '1', '14th, ')

               || DECODE (SUBSTR (fcrc.class_info, 15, 1), '1', '15th, ')

               || DECODE (SUBSTR (fcrc.class_info, 16, 1), '1', '16th, ')

               || DECODE (SUBSTR (fcrc.class_info, 17, 1), '1', '17th, ')

               || DECODE (SUBSTR (fcrc.class_info, 18, 1), '1', '18th, ')

               || DECODE (SUBSTR (fcrc.class_info, 19, 1), '1', '19th, ')

               || DECODE (SUBSTR (fcrc.class_info, 20, 1), '1', '20th, ')

               || DECODE (SUBSTR (fcrc.class_info, 21, 1), '1', '21st, ')

               || DECODE (SUBSTR (fcrc.class_info, 22, 1), '1', '22nd, ')

               || DECODE (SUBSTR (fcrc.class_info, 23, 1), '1', '23rd,')

               || DECODE (SUBSTR (fcrc.class_info, 24, 1), '1', '24th, ')

               || DECODE (SUBSTR (fcrc.class_info, 25, 1), '1', '25th, ')

               || DECODE (SUBSTR (fcrc.class_info, 26, 1), '1', '26th, ')

               || DECODE (SUBSTR (fcrc.class_info, 27, 1), '1', '27th, ')

               || DECODE (SUBSTR (fcrc.class_info, 28, 1), '1', '28th, ')

               || DECODE (SUBSTR (fcrc.class_info, 29, 1), '1', '29th, ')

               || DECODE (SUBSTR (fcrc.class_info, 30, 1), '1', '30th, ')

               || DECODE (SUBSTR (fcrc.class_info, 31, 1), '1', '31st. ')

            ELSE

               'n/a'

         END

            days_of_month,

         CASE

            WHEN     fcrc.class_type = 'S'

                 AND SUBSTR (fcrc.class_info, 32, 1) = '1'

            THEN

               'Yes'

            ELSE

               'n/a'

         END

            last_day_of_month_ticked,

         fcrc.CLASS_INFO

    FROM applsys.fnd_concurrent_requests fcr,

         applsys.fnd_user fu,

         applsys.fnd_user u2,

         applsys.fnd_concurrent_programs fcp,

         applsys.fnd_concurrent_programs_tl fcpt,

         applsys.fnd_printer_styles_tl fpst,

         applsys.fnd_conc_release_classes fcrc,

         applsys.fnd_responsibility_tl frt,

         apps.fnd_lookups fl

   WHERE     fcp.application_id = fcpt.application_id

         AND fcr.requested_by = fu.user_id

         AND fcr.concurrent_program_id = fcp.concurrent_program_id

         AND fcr.program_application_id = fcp.application_id

         AND fcr.concurrent_program_id = fcpt.concurrent_program_id

         AND fcr.responsibility_id = frt.responsibility_id

         AND fcr.last_updated_by = u2.user_id

         AND fcr.print_style = fpst.printer_style_name(+)

         AND fcr.release_class_id = fcrc.release_class_id(+)

         AND fcr.status_code = fl.lookup_code

         AND fl.lookup_type = 'CP_STATUS_CODE'

         AND fcr.phase_code = 'P'

         --AND fcr.status_code in ('E') -- ('I','Q')

         AND 1 = 1

ORDER BY fu.description, fcr.requested_start_date ASC;

--to list Long running jobs

SELECT

      f.request_id ,

      pt.user_concurrent_program_name||decode(decode(nvl(f.description,'xxx'),'xxx', '','-'),'-',' ('||f.description||')','') user_program_name,

      f.actual_start_date start_on,

      f.actual_completion_date end_on,

     -- (f.actual_completion_date - f.actual_start_date) time_on,

      floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)

        || ' HOURS ' ||

        floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)

        || ' MINUTES ' ||

        round((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600 -

        (floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)*60) ))

        || ' SECS ' time_difference,

      p.concurrent_program_name program_name,

      (select user_name from fnd_user fu

       where fu.user_id = f.requested_by) req_by,

      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

      f.status_code

from  apps.fnd_concurrent_programs p,

      apps.fnd_concurrent_programs_tl pt,

      apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

      and f.program_application_id = p.application_id

      and f.concurrent_program_id = pt.concurrent_program_id

      and f.program_application_id = pt.application_id

      AND pt.language = USERENV('Lang')

      and f.actual_start_date is not null

      and trunc(f.actual_start_date) > trunc(sysdate-2)

      and (f.actual_completion_date - f.actual_start_date) > 0.001

      and pt.user_concurrent_program_name not in ('Planning Manager')

order by

      f.actual_completion_date desc --f.actual_start_date desc;

--to list Completed Error jobs for a period and Vers

select

      f.request_id,      pt.user_concurrent_program_name||decode(decode(nvl(f.description,'xxx'),'xxx', '','-'),'-',' ('||f.description||')','') user_program_name,

      f.actual_start_date start_on,

      f.actual_completion_date end_on,

      floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)

        || ' HOURS ' ||

        floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)

        || ' MINUTES ' ||

        round((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600 -

        (floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)*60) ))

        || ' SECS ' time_difference,

      p.concurrent_program_name concurrent_program_name,

      (select user_name from fnd_user fu

       where fu.user_id = f.requested_by) req_by,

      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

      f.status_code

from  apps.fnd_concurrent_programs p,

      apps.fnd_concurrent_programs_tl pt,

      apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

      and f.program_application_id = p.application_id

      and f.concurrent_program_id = pt.concurrent_program_id

      and f.program_application_id = pt.application_id

      AND pt.language = USERENV('Lang')

      and f.actual_start_date is not null

      and f.status_code in ('E')

      and f.actual_start_date is not null

      and (trunc(f.actual_start_date) > trunc(sysdate-1)

      and TO_CHAR(f.actual_start_date, 'HH:MI:SS') > TO_CHAR(SYSDATE - INTERVAL '60' MINUTE, 'HH:MI:SS'))

order by

      f.actual_start_date desc;

select

      f.request_id ,      pt.user_concurrent_program_name||decode(decode(nvl(f.description,'xxx'),'xxx', '','-'),'-',' ('||f.description||')','') user_program_name,

      f.actual_start_date start_on,

      f.actual_completion_date end_on,

      floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)

        || ' HOURS ' ||

        floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)

        || ' MINUTES ' ||

        round((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600 -

        (floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)*60) ))

        || ' SECS ' time_difference,

      p.concurrent_program_name concurrent_program_name,

       (select user_name from fnd_user fu

       where fu.user_id = f.requested_by) req_by,

      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

      f.status_code

from  apps.fnd_concurrent_programs p,

      apps.fnd_concurrent_programs_tl pt,

      apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

      and f.program_application_id = p.application_id

      and f.concurrent_program_id = pt.concurrent_program_id

      and f.program_application_id = pt.application_id

      AND pt.language = USERENV('Lang')

      and f.actual_start_date is not null

      and f.status_code in ('E')

      and f.actual_start_date is not null

      and (trunc(f.actual_start_date) > trunc(sysdate-2))

order by

      f.actual_start_date desc;

select FCR.request_id,

       FU.user_name,

       FCPT.user_concurrent_program_name,

       to_char(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') startDt,

       to_char(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') EndDt,

       FCR.logfile_name

  from fnd_concurrent_requests FCR,

       fnd_concurrent_programs_tl FCPT,

       fnd_user FU

 where FCR.program_application_id = FCPT.application_id

   and FCR.concurrent_program_id = FCPT.concurrent_program_id

   and FCR.requested_by = FU.user_id

   and FCR.phase_code='C'

   and FCR.status_code = 'E'

   and FCR.actual_completion_date between sysdate-1 and sysdate

order by 1 desc;

select FCR.request_id,

       FU.user_name,

       FCPT.user_concurrent_program_name,

       to_char(FCR.actual_start_date,'DD-MON-YYYY HH24:MI:SS') startDt,

       to_char(FCR.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') EndDt,

       FCR.logfile_name

  from fnd_concurrent_requests FCR,

       fnd_concurrent_programs_tl FCPT,

       fnd_user FU

 where FCR.program_application_id = FCPT.application_id

   and FCR.concurrent_program_id = FCPT.concurrent_program_id

   and FCR.requested_by = FU.user_id

   and FCR.phase_code='C'

   and FCR.status_code = 'E'

   and FCR.actual_completion_date between sysdate-1 and sysdate

order by 1 desc;

--to list Profile Options

SELECT p.profile_option_name SHORT_NAME,

         n.user_profile_option_name NAME,

         DECODE (

            v.level_id,

            10001, 'Site',

            10002, 'Application',

            10003, 'Responsibility',

            10004, 'User',

            10005, 'Server',

            10006, 'Org',

            10007, DECODE (

                      TO_CHAR (v.level_value2),

                      '-1', 'Responsibility',

                      DECODE (TO_CHAR (v.level_value),

                              '-1', 'Server',

                              'Server+Resp')),

            'UnDef')

            LEVEL_SET,

         DECODE (

            TO_CHAR (v.level_id),

            '10001', '',

            '10002', app.application_short_name,

            '10003', rsp.responsibility_key,

            '10004', usr.user_name,

            '10005', svr.node_name,

            '10006', org.name,

            '10007', DECODE (TO_CHAR (v.level_value2),

                             '-1', rsp.responsibility_key,

                             DECODE (TO_CHAR (v.level_value),

                                     '-1', (SELECT node_name

                                              FROM fnd_nodes

                                             WHERE node_id = v.level_value2),

                                        (SELECT node_name

                                           FROM fnd_nodes

                                          WHERE node_id = v.level_value2)

                                     || '-'

                                     || rsp.responsibility_key)),

            'UnDef')

            "CONTEXT",

         v.profile_option_value VALUE,

         v.LAST_UPDATED_BY,

         v.LAST_UPDATE_DATE

    FROM fnd_profile_options p,

         fnd_profile_option_values v,

         fnd_profile_options_tl n,

         fnd_user usr,

         fnd_application app,

         fnd_responsibility rsp,

         fnd_nodes svr,

         hr_operating_units org

   WHERE     p.profile_option_id = v.profile_option_id(+)

         AND p.profile_option_name = n.profile_option_name

       AND UPPER (p.profile_option_name) IN (SELECT profile_option_name

                                                 FROM fnd_profile_options_tl

                                                where upper(user_profile_option_name) LIKE UPPER ('%XDO%'))

         AND usr.user_id(+) = v.level_value

         AND rsp.application_id(+) = v.level_value_application_id

         AND rsp.responsibility_id(+) = v.level_value

         AND app.application_id(+) = v.level_value

         AND svr.node_id(+) = v.level_value

         AND org.organization_id(+) = v.level_value

ORDER BY short_name,

         user_profile_option_name,

         level_id,

         level_set;

SELECT opt.profile_option_name,

       VAL.level_id,

       val.profile_option_value VALUE,

       val.LAST_UPDATED_BY,

       val.LAST_UPDATE_DATE

  FROM APPLSYS.FND_PROFILE_OPTION_VALUES VAL,

       APPLSYS.FND_PROFILE_OPTIONS OPT,

       APPLSYS.FND_USER FUSER

 WHERE     VAL.PROFILE_OPTION_ID = OPT.PROFILE_OPTION_ID

       AND VAL.LEVEL_VALUE = FUSER.USER_ID

       AND FUSER.USER_NAME = ;

--to list Profile Option Changes for the last days

SELECT p.profile_option_name SHORT_NAME,

         n.user_profile_option_name NAME,

         DECODE (

            v.level_id,

            10001, 'Site',

            10002, 'Application',

            10003, 'Responsibility',

            10004, 'User',

            10005, 'Server',

            10006, 'Org',

            10007, DECODE (

                      TO_CHAR (v.level_value2),

                      '-1', 'Responsibility',

                      DECODE (TO_CHAR (v.level_value),

                              '-1', 'Server',

                              'Server+Resp')),

            'UnDef')

            LEVEL_SET,

         DECODE (

            TO_CHAR (v.level_id),

            '10001', '',

            '10002', app.application_short_name,

            '10003', rsp.responsibility_key,

            '10004', usr.user_name,

            '10005', svr.node_name,

            '10006', org.name,

            '10007', DECODE (TO_CHAR (v.level_value2),

                             '-1', rsp.responsibility_key,

                             DECODE (TO_CHAR (v.level_value),

                                     '-1', (SELECT node_name

                                              FROM fnd_nodes

                                             WHERE node_id = v.level_value2),

                                        (SELECT node_name

                                           FROM fnd_nodes

                                          WHERE node_id = v.level_value2)

                                     || '-'

                                     || rsp.responsibility_key)),

            'UnDef')

            "CONTEXT",

         v.profile_option_value VALUE,

         v.LAST_UPDATED_BY,

         v.LAST_UPDATE_DATE

    FROM fnd_profile_options p,

         fnd_profile_option_values v,

         fnd_profile_options_tl n,

         fnd_user usr,

         fnd_application app,

         fnd_responsibility rsp,

         fnd_nodes svr,

         hr_operating_units org

   WHERE     p.profile_option_id = v.profile_option_id(+)

         AND p.profile_option_name = n.profile_option_name

         AND v.last_update_date > (sysdate-14)

         AND usr.user_id(+) = v.level_value

         AND rsp.application_id(+) = v.level_value_application_id

         AND rsp.responsibility_id(+) = v.level_value

         AND app.application_id(+) = v.level_value

         AND svr.node_id(+) = v.level_value

         AND org.organization_id(+) = v.level_value

ORDER BY v.LAST_UPDATE_DATE desc;

        

--EY Profiles check

SELECT

DECODE (fpova.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'USER',fpova.level_id) profile_level,

      DECODE (level_id,

                 10001, NULL,

                 10002, fa.application_name,

                 10003, fr.responsibility_name,

                 10004, fu.user_name) NonSite_Description,

         fu.user_name,

         fpov.user_profile_option_name,

         fpov.profile_option_name,

         fpova.profile_option_value,

         fpova.last_update_date UPDT_DT,

         fu2.user_name updt_by

    FROM fnd_profile_options_vl fpov,

         fnd_profile_option_values fpova,

         fnd_application_tl fa,

         fnd_responsibility_tl fr,

         fnd_user fu,

         fnd_user fu2

   WHERE     fpov.application_id = fpova.application_id

         AND fpov.profile_option_id = fpova.profile_option_id

         AND start_date_active <= SYSDATE

         AND NVL (end_date_active, SYSDATE) >= SYSDATE

         AND (   site_enabled_flag = 'Y'

              OR app_enabled_flag = 'Y'

              OR resp_enabled_flag = 'Y'

              OR user_enabled_flag = 'Y')

         AND fpova.level_value = fa.application_id(+)

         AND fpova.level_value = fr.responsibility_id(+)

         AND fpova.level_value = fu.user_id(+)

         AND fpova.last_updated_by = fu2.user_id(+)

        -- AND fu.user_name != 'SYSADMIN'

        --  AND fpov.user_profile_option_name NOT IN ('EGO: User Organization Context','Printer', 'Concurrent:Report Copies')

ORDER BY fpova.last_update_date desc;  

--to list Conc Programs and Conc Set

-- to list all the Request Sets created with a Concurrent Program given 

SELECT DISTINCT user_request_set_name

  FROM FND_REQUEST_SETS_TL

 WHERE request_set_id IN

          (SELECT request_set_id

             FROM FND_REQUEST_SET_PROGRAMS

            WHERE concurrent_program_id =

                     (SELECT CONCURRENT_PROGRAM_ID

                        FROM fnd_concurrent_programs_tl

                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

--to list all concurrent programs of a Request Set 

SELECT USER_CONCURRENT_PROGRAM_NAME

  FROM fnd_concurrent_programs_tl

 WHERE CONCURRENT_PROGRAM_ID IN

          (SELECT concurrent_program_id

             FROM FND_REQUEST_SET_PROGRAMS

            WHERE request_set_id =

                     (SELECT request_set_id

                        FROM FND_REQUEST_SETS_TL

                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

--to list users who have a responsibility

SELECT usr.user_id,

       usr.user_name,

       res.RESPONSIBILITY_ID,

       res.RESPONSIBILITY_NAME

  FROM apps.FND_USER usr,

       apps.FND_RESPONSIBILITY_TL res,

       apps.FND_USER_RESP_GROUPS grp

 WHERE     UPPER (res.RESPONSIBILITY_NAME) LIKE

           UPPER ('%' || NVL ('System Administrator', 'INV') || '%') -- resp name

       AND UPPER (res.RESPONSIBILITY_NAME) NOT LIKE '%AX%'

       AND UPPER (res.RESPONSIBILITY_NAME) NOT LIKE '%OPM%'

       AND grp.responsibility_id = res.responsibility_id

       AND grp.user_id = usr.user_id;

--to list Responsibilities of an user

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND UPPER(fu.user_name)      =  UPPER()

   AND ( furg.end_date is NULL OR furg.end_date > sysdate)

UNION

   SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM  fnd_user_resp_groups_indirect        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND UPPER(fu.user_name)      =  UPPER()

   AND ( furg.end_date is NULL OR furg.end_date > sysdate)

 ORDER BY "Responsibility Name";

--to find status of Receiving Transaction Manager

SELECT fdg.data_group_name,

       user_concurrent_queue_name Manager,

       concurrent_queue_name,

       concurrent_queue_id,

       sleep_seconds,

       running_processes,

       max_processes,

       DECODE (NVL (control_code, 'A'),

               'A', 'Activate concurrent Manager',

               'D', 'Deactivate concurrent manager',

               'E', 'Deactivated',

               'N', 'Target node/queue unavailable',

               'R', 'Restart concurrent manager',

               'T', 'Terminate requests and deactivate manager',

               'U', 'Update concurrent manager env inf.',

               'V', 'Verify concurrent managers status',

               'X', 'Terminated',

               'Error control code')

          CONTROL_CODE

  FROM FND_CONCURRENT_QUEUES_VL FCQ,

       FND_CONCURRENT_PROCESSORS FCP,

       FND_DATA_GROUPS FDG

 WHERE     FCQ.DATA_GROUP_ID = FDG.DATA_GROUP_ID

       AND FCP.CONCURRENT_PROCESSOR_ID = FCQ.CONCURRENT_PROCESSOR_ID

       AND FCP.CONCURRENT_PROCESSOR_NAME = 'RCVOLTM';

--to find Managers’ status

SELECT *

  FROM (SELECT q.user_concurrent_queue_name service_name,

               a.application_name srvc_app_name,

               a.application_short_name srvc_app_short_name,

               q.concurrent_queue_name service_short_name,

               DECODE (

                  (SELECT COUNT (*)

                     FROM apps.fnd_concurrent_processes fcp1

                    WHERE     fcp1.concurrent_queue_id =

                                 q.concurrent_queue_id

                          AND fcp1.queue_application_id = q.application_id

                          AND (   fcp1.process_status_code IN ('C', 'M')

                               OR (    fcp1.process_status_code IN ('A',

                                                                    'D',

                                                                    'T')

                                   AND EXISTS

                                          (SELECT 1

                                             FROM gv$session

                                            WHERE fcp1.session_id = audsid)))) /*actual_processes */

                                                                              ,

                  0, DECODE (q.max_processes, 0, 'NOT_STARTED', 'DOWN'),

                  q.max_processes, 'UP',

                  'WARNING')

                  service_status,

               q.max_processes target_processes,

               (SELECT COUNT (*)

                  FROM apps.fnd_concurrent_processes fcp2

                 WHERE     fcp2.concurrent_queue_id = q.concurrent_queue_id

                       AND fcp2.queue_application_id = q.application_id

                       AND (   fcp2.process_status_code IN ('C', 'M') /*

                          Connecting or Migrating */

                            OR (    fcp2.process_status_code IN ('A',

                                                                 'D',

                                                                 'T')

                                AND EXISTS

                                       (SELECT 1

                                          FROM gv$session

                                         WHERE fcp2.session_id = audsid))))

                  actual_processes,

               '' MESSAGE,

               s.service_handle srvc_handle

          FROM apps.fnd_concurrent_queues_vl q,

               apps.fnd_application_vl a,

               apps.fnd_cp_services s

         WHERE     q.application_id = a.application_id

               AND s.service_id = q.manager_type

        UNION

        /* Need to cover the case where a manager has no rows in

        FND_CONCURRENT_PROCESSES. Outer joins won't cut it. */

        SELECT q.user_concurrent_queue_name service_name,

               a.application_name srvc_app_name,

               a.application_short_name srvc_app_short_name,

               q.concurrent_queue_name srvc_short_name,

               DECODE (q.max_processes, 0, 'NOT_STARTED', 'DOWN')

                  service_status,

               q.max_processes target_processes,

               0 actual_processes,

               '' MESSAGE,

               s.service_handle srvc_handle

          FROM apps.fnd_concurrent_queues_vl q,

               apps.fnd_application_vl a,

               apps.fnd_cp_services s

         WHERE     q.application_id = a.application_id

               AND s.service_id = q.manager_type

               AND NOT EXISTS

                      (SELECT 1

                         FROM apps.fnd_concurrent_processes p

                        WHERE     process_status_code IN ('C',

                                                          'M',

                                                          'A',

                                                          'D',

                                                          'T')

                              AND q.concurrent_queue_id =

                                     p.concurrent_queue_id

                              AND q.application_id = p.queue_application_id))

--WHERE service_name like  'MRP Manager' --'Inventory Manager'

  order by service_status desc;

--to list Resps/Users Having Access to Disco Workbook

SELECT docs.doc_name,

       fnd.responsibility_name,

       priv.ap_eu_id,

       DECODE (usr.eu_role_flag,  0, 'user',  1, 'role') user_role,

       usr.eu_username

  FROM eul5_us.eul5_documents docs,

       eul5_us.eul5_access_privs priv,

       eul5_us.eul5_eul_users usr,

       fnd_responsibility_tl fnd

 WHERE docs.doc_id = priv.gd_doc_id(+)

       AND priv.ap_eu_id = usr.eu_id(+)

       AND usr.eu_username = '#' || fnd.responsibility_id(+) || '#' || fnd.application_id(+)

       AND priv.ap_type = 'GD'

       AND docs.doc_name LIKE 'XX%'; --custom name

--to check emails of Executives

SELECT DISTINCT a.last_name,

  a.first_name,

  a.full_name,

  b.user_name,

  a.email_address AS "Emp Email",

  b.email_address AS "FND Email"

FROM

  per_all_people_f a,

  fnd_user b

WHERE 1=1

AND a.person_id = b.employee_id(+)

AND last_name IN (   )

AND NVL (TRUNC (a.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)

AND NVL (TRUNC (a.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)

ORDER BY 1 ASC; 

--to check emails of suppliers

select  aps.segment1 "Vendor Number"

        ,aps.vendor_name "Vendor Name"

        ,iepa.remit_advice_email "Remittance Advice Email"

  from  ap_suppliers aps

       ,iby_external_payees_all iepa

 where  iepa.payee_party_id = aps.party_id

 and iepa.remit_advice_email is not NULL;

 select * from PO_VENDOR_CONTACTS where EMAIL_ADDRESS is not NUll;

select email_address from HZ_ORG_CONTACTS_V

 where email_address is not NULL;

--to find status of a completed job giving id

SELECT argument_text, a.*

  FROM fnd_concurrent_requests a

 WHERE concurrent_program_id = <57334> AND argument_text LIKE <', , , , 15'>

 order by last_update_date desc;

SELECT a.argument_text, a.actual_start_date, a.*

    FROM fnd_concurrent_requests a

   WHERE concurrent_program_id = <57334> AND status_code = 'E'

ORDER BY a.actual_start_date desc;

--to find user-manager-resps

SELECT DISTINCT fu.user_name          AS "User Name",

                ppf.full_name         AS "Employee Name",

                ppf1.full_name        AS "Supervisor Name",

                frt.RESPONSIBILITY_NAME AS "Responsibility"

    FROM per_all_people_f     ppf,

         per_all_assignments_f paaf,

         per_all_people_f     ppf1,

         fnd_user             fu,

         fnd_user_resp_groups furg,

         fnd_responsibility_tl frt

   WHERE     1 = 1

         AND ppf.person_id = paaf.person_id

         AND SYSDATE BETWEEN paaf.effective_start_date

                         AND paaf.effective_end_date

         AND paaf.supervisor_id = ppf1.person_id(+)

         AND TRUNC (SYSDATE) BETWEEN TRUNC (PPF.effective_start_date)

                             AND TRUNC (NVL (PPF.effective_end_date,SYSDATE))

         AND TRUNC (SYSDATE) BETWEEN TRUNC (PPF1.effective_start_date)

                             AND TRUNC (NVL(PPF1.effective_end_date,SYSDATE))

         AND PPF.PERSON_ID = FU.EMPLOYEE_ID(+)

         AND fu.USER_ID = furg.USER_ID

         AND (TO_CHAR (fu.END_DATE) IS NULL OR fu.END_DATE > SYSDATE)

         AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID

         AND (TO_CHAR (furg.END_DATE) IS NULL OR furg.END_DATE > SYSDATE)

ORDER BY 1;