Friday, December 30, 2016

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;