User Accounts – Basic Details

-- ##############################################################################
--      BASIC USER ACCOUNTS
-- ##############################################################################

     select fu.user_id
          , fu.user_name
          , fu.description
          , fu.email_address
          , fu.employee_id
          , fu.last_logon_date
          , to_char(fu.start_date, 'DD-MON-YYYY') start_date
          , to_char(fu.end_date, 'DD-MON-YYYY') end_date
          , fu.password_lifespan_days pwd_days
          , fu.password_date
          , ppx.full_name hr_full_name
       from fnd_user fu
  left join per_people_x ppx on fu.employee_id = ppx.person_id
      where 1 = 1
--        and lower(fu.email_address) like '%bob%'
        and fu.user_name = 'BOBHOPE'
   order by fu.user_name;

Attachments

Basic FND Attachments SQL

Attachments can be attached to various “things” in Oracle EBS, like Purchase Orders or Invoices.

For example, if you email out POs, the email PO (e.g. a PDF) will be attached to the PO. If you scan invoices, often the image, or a link to the image, will be attached to the invoice.

The fnd_document_entities_tl.user_entity_name will list the entity (e.g. PO Head or Invoice). And then you can get the document ID the attachment is linked to, from fnd_attached_documents.pk1_value. From there you can link back to your key document, such as the PO number of AP Invoice.

-- ##############################################################################
--        BASIC ATTACHMENT INFO
-- ##############################################################################

  SELECT ad.pk1_value -- key document_id
       , fu.description
       , det.user_entity_name -- e.g. PO Head, Invoice, Disbursement Payment Instruction etc
       , ad.creation_date
       , ad.entity_name
       , d.document_id
       , dt.file_name
    FROM applsys.fnd_document_datatypes dat
       , applsys.fnd_document_entities_tl det
       , applsys.fnd_documents_tl dt
       , applsys.fnd_documents d
       , applsys.fnd_document_categories_tl dct
       , applsys.fnd_attached_documents ad
       , applsys.fnd_user fu
   WHERE d.document_id = ad.document_id
     AND dt.document_id = d.document_id
     AND dct.category_id = d.category_id
     AND d.datatype_id = dat.datatype_id
     AND ad.entity_name = det.data_object_code
     AND ad.created_by = fu.user_id
     and ad.pk1_value = '85233'
--     and ad.entity_name = 'AP_INVOICES'
--     AND d.document_id = 1005520
     and 1 = 1;

Counting Entity Names

You can count the number of entities your attachments are linked to:

-- ##############################################################################
--        COUNT ENTITY NAME TYPES
-- ##############################################################################

  SELECT det.user_entity_name
       , count(*) ct
    FROM applsys.fnd_document_datatypes dat
       , applsys.fnd_document_entities_tl det
       , applsys.fnd_documents_tl dt
       , applsys.fnd_documents d
       , applsys.fnd_document_categories_tl dct
       , applsys.fnd_attached_documents ad
       , applsys.fnd_user fu
   WHERE d.document_id = ad.document_id
     AND dt.document_id = d.document_id
     AND dct.category_id = d.category_id
     AND d.datatype_id = dat.datatype_id
     AND ad.entity_name = det.data_object_code
     AND ad.created_by = fu.user_id
--     and ad.pk1_value = '85233'
--     and ad.entity_name = 'AP_INVOICES'
--     AND d.document_id = 1005520
     and 1 = 1
     group by det.user_entity_name;

SQL Example – Link to Purchase Order

Here’s an example linking back to the Purchase Order:

-- source view = 'FND_ATTACHED_DOCS_FORM_VL'

-- ##############################################################################
--        DOCUMENT ATTACHMENTS 
-- ##############################################################################

  SELECT CASE
            WHEN(det.user_entity_name LIKE 'PO Head%')
               THEN (SELECT pha.segment1
                       FROM po.po_headers_all pha
                      WHERE pha.po_header_id = ad.pk1_value)
            WHEN(det.user_entity_name = 'PO Line')
               THEN (SELECT pha.segment1
                       FROM po.po_headers_all pha
                          , po.po_lines_all pla
                      WHERE pha.po_header_id = pla.po_header_id
                        AND pla.po_line_id = ad.pk1_value)
            WHEN(det.user_entity_name = 'REQ Line')
               THEN (SELECT prha.segment1
                       FROM po.po_requisition_headers_all prha
                          , po.po_requisition_lines_all prla
                      WHERE prha.requisition_header_id =
                                                    prla.requisition_header_id
                        AND prla.requisition_line_id = ad.pk1_value)
            WHEN(det.user_entity_name LIKE 'REQ%')
               THEN (SELECT prha.segment1
                       FROM po.po_requisition_headers_all prha
                      WHERE prha.requisition_header_id = ad.pk1_value)
            ELSE 'UNDEFINED'
         END doc_num
       , CASE
            WHEN(det.user_entity_name LIKE 'PO%')
               THEN 'PO'
            WHEN(det.user_entity_name LIKE 'REQ%')
               THEN 'REQ'
            ELSE 'UNDEFINED'
         END doc_type
       --, ad.seq_num
,        SUBSTR(fu.description, 0, 30) || '...' created_by
       , dt.file_name
       , dt.description
       , det.user_entity_name
       , ad.creation_date
       , ad.entity_name
       , ad.pk1_value
    FROM applsys.fnd_document_datatypes dat
       , applsys.fnd_document_entities_tl det
       , applsys.fnd_documents_tl dt
       , applsys.fnd_documents d
       , applsys.fnd_document_categories_tl dct
       , applsys.fnd_attached_documents ad
       , applsys.fnd_user fu
   WHERE d.document_id = ad.document_id
     AND dt.document_id = d.document_id
     AND dct.category_id = d.category_id
     AND d.datatype_id = dat.datatype_id
     AND ad.entity_name = det.data_object_code
     AND ad.created_by = fu.user_id
     AND d.creation_date > '01-OCT-2007'
     AND (
             det.user_entity_name LIKE 'PO%'
          OR det.user_entity_name LIKE 'REQ%'
         )
     AND dt.file_name NOT LIKE '%.pdf%'
     AND dat.user_name = 'File'
ORDER BY 7 DESC;

Responsibilities – Attached to Menu

This SQL returns responsibilities with access to a particular menu, any level down, not just the top level.

Enter user menu name e.g. AR_INTERFACE_GUI.

-- ##############################################################################
--      RESPS ATTACHED TO A MENU 
-- ##############################################################################

  SELECT DISTINCT frt.responsibility_name
                , fr.responsibility_key
                , fr.responsibility_id
                , (SELECT DISTINCT COUNT (*) FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE) user_count
                , fat.application_name app
                , fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_responsibility fr
    JOIN applsys.fnd_responsibility_tl frt ON fr.application_id =    frt.application_id
                                          AND fr.responsibility_id = frt.responsibility_id
    JOIN applsys.fnd_application_tl fat    ON fr.application_id =    fat.application_id 
    JOIN applsys.wf_local_user_roles wlur  ON fr.responsibility_id = wlur.role_orig_system_id 
    JOIN applsys.fnd_menus_tl fmt          ON fr.menu_id =           fmt.menu_id 
    JOIN applsys.fnd_menus fm              ON fm.menu_id =           fmt.menu_id
   WHERE fr.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

-- without linking to responsibilities (since some menus are not linked to responsibilities, but might be a parent menu containing a sub menu you want to delete)

  SELECT DISTINCT fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_menus_tl fmt
       , applsys.fnd_menus fm
   WHERE fm.menu_id = fmt.menu_id
--     AND fmt.user_menu_name != :menu
     AND fmt.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

Responsibilities – Attached to Function

This SQL will return responsibilities with access to a particular function, any level down, not just at the top level.

Search against a function name e.g. AR_ARXCWMAI_QIT.

-- ##############################################################################
--      RESPS ATTACHED TO A FUNCTION 
-- ############################################################################*/

SELECT DISTINCT frt.responsibility_id
              , frt.responsibility_name
              , fr.responsibility_key
              , fr.creation_date
              , fa.application_short_name
              , fmt.user_menu_name menu
              , (SELECT DISTINCT COUNT(*) FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE AND fr.end_date IS NULL) user_count
           FROM applsys.fnd_responsibility fr
           JOIN applsys.fnd_responsibility_tl frt ON fr.application_id = frt.application_id
                                                 AND fr.responsibility_id = frt.responsibility_id 
           JOIN applsys.fnd_application fa        ON fa.application_id = fr.application_id 
           JOIN applsys.wf_local_user_roles wlur  ON fr.responsibility_id = wlur.role_orig_system_id 
      LEFT JOIN applsys.fnd_menus_tl fmt          ON fr.menu_id = fmt.menu_id          -- responsibilities are not always linked to a menu
          WHERE wlur.role_orig_system = 'FND_RESP'
            AND SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date , SYSDATE + 1)
            AND SYSDATE BETWEEN wlur.start_date AND NVL(wlur.expiration_date, SYSDATE + 1)
            AND fr.menu_id IN(
                   SELECT     menu_id
                         FROM applsys.fnd_menu_entries fme
                   CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
                   START WITH fme.function_id =
                                     (SELECT function_id
                                        FROM applsys.fnd_form_functions fff
                                       WHERE fff.function_name = :function_name));

Request Groups

Sections:

  1. Request Group Details
  2. Request Groups And Responsibilities
  3. Concurrent Programs and Whether Assigned to a Request Group
  4. Request Sets and Whether Assigned to a Request Group
  5. Request Groups Against Resps – Basic List
  6. Request Sets
  7. Request Sets and Concurrent Programs – Comparison between two Request Groups

Request Group Details

This lists the contents of a request group.

-- ##############################################################################
--       REQUEST GROUP DETAILS
-- ##############################################################################

    SELECT frg.request_group_name
         , frg.creation_date
         , fu1.user_name created_by
         , fat1.application_name group_application
         , DECODE(frgu.request_unit_type, 'P', 'Program', 'S', 'Request Set', 'A', 'Application') type
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.user_concurrent_program_name FROM applsys.fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frst.user_request_set_name FROM applsys.fnd_request_sets_tl frst WHERE frst.request_set_id = frgu.request_unit_id AND frst.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'A' THEN
                (SELECT fat.application_name FROM applsys.fnd_application_tl fat WHERE fat.application_id = frgu.request_unit_id)
           END name
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.enabled_flag FROM applsys.fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_enabled
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.concurrent_program_id FROM applsys.fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_id
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.end_date_active FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_end_date
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.request_set_id FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_id
         , fat2.application_name application
         , frgu.last_update_date line_updated
         , fu1.user_name line_updated_by 
      FROM applsys.fnd_request_groups frg
      JOIN applsys.fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id =     frgu.request_group_id
      JOIN applsys.fnd_application_tl fat1      ON frg.application_id =       fat1.application_id
      JOIN applsys.fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
      JOIN applsys.fnd_user fu1                 ON frg.created_by =           fu1.user_id
      JOIN applsys.fnd_user fu2                 ON frgu.last_updated_by =     fu2.user_id
     WHERE 1 = 1
       AND frg.request_group_name = 'All Reports'
       AND fat1.application_name = 'Payables'
       AND 1 = 1;

Request Groups And Responsibilities

-- ##############################################################################
--       REQUEST GROUPS AND RESPONSIBILITIES
-- ##############################################################################

-- This is useful if you want to work out which responsibilities have access to
-- specific concurrent programs, request sets or applications

-- concurrent requests

SELECT frg.request_group_name
     , frg.request_group_id
     , fat.application_name
     , frt.responsibility_name
     , frt.creation_date
     , frt.created_by
     , fcpt.user_concurrent_program_name job_name
     , DECODE(frgu.request_unit_type,'S','Request Set','P','Program') job_type
     , fat2.application_name job_app
  FROM applsys.fnd_request_groups frg
  JOIN applsys.fnd_request_group_units frgu    ON frg.application_id =       frgu.application_id AND frg.request_group_id = frgu.request_group_id AND frgu.request_unit_type = 'P'
  JOIN applsys.fnd_concurrent_programs_tl fcpt ON frgu.request_unit_id =     fcpt.concurrent_program_id
  JOIN applsys.fnd_responsibility fr           ON fr.request_group_id =      frg.request_group_id
  JOIN applsys.fnd_responsibility_tl frt       ON fr.responsibility_id =     frt.responsibility_id
  JOIN applsys.fnd_application_tl fat          ON frgu.application_id =      fat.application_id
  JOIN applsys.fnd_application_tl fat2         ON frgu.unit_application_id = fat2.application_id
 WHERE 1 = 1
   AND frt.responsibility_name = 'AP Manager'
   AND frg.request_group_name = 'All Reports'
   AND fat.application_name = 'Payables'
   AND fcpt.user_concurrent_program_name LIKE 'AP%'
--   AND fr.end_date IS NULL
   AND 1 = 1;

-- request sets

SELECT frg.request_group_name
     , fat.application_name
     , frt.responsibility_name
     , frst.user_request_set_name job_name
     , DECODE(frgu.request_unit_type,'S','Request Set','P','Program') job_type
     , fat2.application_name job_app
  FROM applsys.fnd_request_groups frg
  JOIN applsys.fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id = frgu.request_group_id AND frgu.request_unit_type = 'S'
  JOIN applsys.fnd_responsibility fr        ON fr.request_group_id =      frg.request_group_id
  JOIN applsys.fnd_responsibility_tl frt    ON fr.responsibility_id =     frt.responsibility_id
  JOIN applsys.fnd_request_sets_tl frst     ON frgu.request_unit_id =     frst.request_set_id
  JOIN applsys.fnd_application_tl fat       ON frgu.application_id =      fat.application_id
  JOIN applsys.fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
 WHERE 1 = 1 
   AND frt.responsibility_name = 'Project Super User'
ORDER BY 1, 2, 4;

-- linked to applications

SELECT DISTINCT
       frg.request_group_name
     , fat.application_name
     , frt.responsibility_name
     , fat2.application_name assigned_application_name
  FROM applsys.fnd_request_groups frg       
  JOIN applsys.fnd_request_group_units frgu ON frg.application_id =       frgu.application_id
                                           AND frg.request_group_id =     frgu.request_group_id
  JOIN applsys.fnd_responsibility fr        ON fr.request_group_id =      frg.request_group_id
  JOIN applsys.fnd_responsibility_tl frt    ON fr.responsibility_id =     frt.responsibility_id 
  JOIN applsys.fnd_application_tl fat       ON frgu.application_id =      fat.application_id 
  JOIN applsys.fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
 WHERE frt.responsibility_name = 'Project Super User'
ORDER BY 1, 2;

Concurrent Programs and Whether Assigned to a Request Group

-- ##############################################################################
--        CONCURRENT PROGRAMS AND WHETHER ASSIGNED TO A REQUEST GROUP
-- ##############################################################################

SELECT   fcpt.user_concurrent_program_name request_name
       , fat.application_name
       , fcpt.creation_date
       , fu.user_name created_by
       , (SELECT COUNT(*)
             FROM applsys.fnd_request_group_units frgu
            WHERE frgu.request_unit_type = 'P'
              AND frgu.request_unit_id = fcpt.concurrent_program_id) request_group_count
    FROM applsys.fnd_concurrent_programs_tl fcpt
    JOIN applsys.fnd_application_tl fat ON fcpt.application_id = fat.application_id
    JOIN applsys.fnd_user fu            ON fcpt.created_by =     fu.user_id
   WHERE fcpt.user_concurrent_program_name = 'Purge Transaction Objects Diagnostics'
ORDER BY fcpt.user_concurrent_program_name;

Request Sets and Whether Assigned to a Request Group

-- ##############################################################################
--        REQUEST SETS AND WHETHER ASSIGNED TO A REQUEST GROUP
-- ##############################################################################

SELECT   frst.user_request_set_name
       , fat.application_name
       , frst.creation_date
       , fu.description created_by
       , (SELECT COUNT(*)
             FROM applsys.fnd_request_group_units frgu
            WHERE frgu.request_unit_type = 'S'
              AND frgu.request_unit_id = frst.request_set_id) request_group_count
    FROM applsys.fnd_request_sets_tl frst
    JOIN applsys.fnd_application_tl fat ON frst.application_id = fat.application_id
    JOIN applsys.fnd_user fu            ON frst.created_by =     fu.user_id
   WHERE frst.user_request_set_name = 'Purge Transaction Objects Diagnostics'
ORDER BY frst.user_request_set_name;

Request Groups Against Resps – Basic List

-- ##############################################################################
--       REQUEST GROUPS AGAINST RESPS - BASIC LIST
-- ##############################################################################

SELECT rtl.responsibility_name
     , rg.request_group_name
     , rg.description
     , fat.application_name
  FROM applsys.fnd_responsibility_tl rtl
  JOIN applsys.fnd_responsibility r   ON rtl.responsibility_id =  r.responsibility_id
  JOIN applsys.fnd_request_groups rg  ON r.request_group_id =     rg.request_group_id
                                     AND r.group_application_id = rg.application_id
  JOIN applsys.fnd_application_tl fat ON r.application_id =       fat.application_id
 WHERE rtl.responsibility_name LIKE '%User%';

Request Sets

-- ##############################################################################
--       REQUESET SETS
-- ##############################################################################

select * from applsys.fnd_request_sets_tl frst;

Request Sets and Concurrent Programs – Comparison between two Request Groups

If you have two request groups and want to see what’s in one but not the other, you can export their contents to Excel and do a Vlookup or something like that. Or you can use MINUS instead. You put e.g. Request Group 1 in the first SQL, and Request Group 2 in the second SQL, with a Minus between them. That’ll show you what’s in the first group and not in the second. You can then swap the names around and re-do to compare the other way round.

-- ##############################################################################
--       COMPARING TWO REQUEST GROUPS - IN ONE AND NOT IN THE OTHER
-- ##############################################################################

    SELECT CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.user_concurrent_program_name FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frst.user_request_set_name FROM fnd_request_sets_tl frst WHERE frst.request_set_id = frgu.request_unit_id AND frst.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'A' THEN
                (SELECT fat.application_name FROM fnd_application_tl fat WHERE fat.application_id = frgu.request_unit_id)
           END name
         , DECODE(frgu.request_unit_type, 'P', 'Program', 'S', 'Request Set', 'A', 'Application') type
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.creation_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_created
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.last_update_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_updated
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.enabled_flag FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_enabled
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.concurrent_program_id FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_id
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.end_date_active FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_end_date
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.request_set_id FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_id
         , fat2.application_name application
      FROM fnd_request_groups frg
      JOIN fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id =     frgu.request_group_id
      JOIN fnd_application_tl fat1      ON frg.application_id =       fat1.application_id
      JOIN fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
     WHERE 1 = 1
       AND frg.request_group_name LIKE 'Request Group 1'
--       AND frg.request_group_name LIKE 'Request Group 2'
       AND 1 = 1
minus
    SELECT CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.user_concurrent_program_name FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frst.user_request_set_name FROM fnd_request_sets_tl frst WHERE frst.request_set_id = frgu.request_unit_id AND frst.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'A' THEN
                (SELECT fat.application_name FROM fnd_application_tl fat WHERE fat.application_id = frgu.request_unit_id)
           END name
         , DECODE(frgu.request_unit_type, 'P', 'Program', 'S', 'Request Set', 'A', 'Application') type
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.creation_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_created
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.last_update_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_updated
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.enabled_flag FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_enabled
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.concurrent_program_id FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_id
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.end_date_active FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_end_date
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.request_set_id FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_id
         , fat2.application_name application
      FROM fnd_request_groups frg
      JOIN fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id =     frgu.request_group_id
      JOIN fnd_application_tl fat1      ON frg.application_id =       fat1.application_id
      JOIN fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
     WHERE 1 = 1
--       AND frg.request_group_name LIKE 'Request Group 1'
       AND frg.request_group_name LIKE 'Request Group 2'
       AND 1 = 1;

Extracting Menu Structures using SQL

Menus that are assigned to Oracle EBS responsibilities are often nested structures which contain lots of levels. The SQLs on this page explore that a bit.

Here are some basic details about menus assigned to responsibilities:

-- ##############################################################################
--      RESPONSIBILITIES ASSIGNED TO MENUS 
-- ##############################################################################

    SELECT fat.application_name
         , fa.application_short_name
         , frt.responsibility_id
         , frt.responsibility_name
         , fmt.user_menu_name
      FROM applsys.fnd_responsibility fr
      JOIN applsys.fnd_responsibility_tl frt ON fr.application_id =    frt.application_id 
      JOIN applsys.fnd_menus_tl fmt          ON fr.responsibility_id = frt.responsibility_id AND fr.menu_id = fmt.menu_id
      JOIN applsys.fnd_application fa        ON frt.application_id =   fa.application_id 
      JOIN applsys.fnd_application_tl fat    ON fr.application_id =    fa.application_id AND fa.application_id = fat.application_id 
     WHERE 1 = 1
       AND NVL(fr.end_date, SYSDATE + 1) > SYSDATE -- responsibility is active
       AND fmt.user_menu_name = 'PO_NAVIGATION'
  ORDER BY fa.application_short_name
         , frt.responsibility_name;

-- ##############################################################################
--       COUNT OF RESPONSIBILITIES ASSIGNED TO A MENU
-- ##############################################################################

SELECT fmt.user_menu_name
     , COUNT(DISTINCT frt.responsibility_id) count
  FROM applsys.fnd_responsibility fr
  JOIN applsys.fnd_responsibility_tl frt ON fr.responsibility_id = frt.responsibility_id AND fr.application_id = frt.application_id 
  JOIN applsys.fnd_menus_tl fmt ON fr.menu_id = fmt.menu_id
 WHERE fmt.user_menu_name = 'INV_NAVIGATE'
GROUP BY fmt.user_menu_name;

This SQL is useful because you can enter in the Menu Name, and the SQL will export the menu structure, showing menus, functions and prompts. The CONNECT BY is used to extract the menu levels / hierarchy.

-- ##############################################################################
--      MENU TREE VIEW
-- ##############################################################################
           SELECT fmev.entry_sequence seq
                , LEVEL
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmv.user_menu_name menu
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
                , fmv.menu_name
                , fmv.user_menu_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT user_function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END user_function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT vvv.description
                           FROM apps.fnd_form_functions_vl vvv
                          WHERE function_id = fmev.function_id)
                  END function_description
                , fmev.description entry_description
             FROM apps.fnd_menus_vl fmv
             JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
            WHERE 1 = 1
              AND fmev.prompt IS NOT NULL
--              AND LEVEL = 1
       CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       START WITH fmv.user_menu_name = 'PO_NAVIGATION'
ORDER SIBLINGS BY fmev.entry_sequence;

If you want a “flat” view of a menu, with just the top level menu / function details as you see on the SysAdmin Menu form, this can be used.

-- ##############################################################################
--      MENU FLAT VIEW
-- ##############################################################################
SELECT fmv.menu_name
     , fmev.entry_sequence seq
     , fmev.prompt
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT user_menu_name FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu                   
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT user_function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END user_fcn_name
     , fmev.description
  FROM apps.fnd_menus_vl fmv
  JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
   AND user_menu_name = 'PO_NAVIGATION'
   AND 1 = 1;

Here are some more variations, looking only at menus instead of menus and functions together.

-- ##############################################################################
--      MENU TREE WALK EXCLUDING FUNCTIONS
-- ##############################################################################

-- v1

    SELECT LPAD(' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
         , LEVEL
         , fmev.entry_sequence
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
       AND fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmv.menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence ;

-- v2

    SELECT LPAD(' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
         , LEVEL
         , fmev.entry_sequence
         , fmt.user_menu_name
         , fmev.creation_date
         , fu.user_name
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
 LEFT JOIN applsys.fnd_menus_tl fmt ON fmv.menu_id = fmt.menu_id 
      JOIN applsys.fnd_user fu ON fmev.created_by = fu.user_id
     wHERE fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmt.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

Here are lots more menu tree walking examples.

-- ##############################################################################
--      MORE MENU TREE WALKING EXAMPLES
-- ##############################################################################

-- v1

    SELECT fmev.entry_sequence seq
         , LEVEL
         , LPAD(' ', (LEVEL - 1) * 4, ' ') || fmev.prompt prompt
      FROM apps.fnd_menus_vl fmv
                 JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id          = fmv.menu_id
           LEFT JOIN apps.fnd_responsibility_vl frv ON frv.menu_id           = fmv.menu_id
           LEFT JOIN apps.fnd_menus_vl fmv2         ON fmv2.menu_id          = fmev.sub_menu_id
           LEFT JOIN apps.fnd_resp_functions frf    ON frf.responsibility_id = frv.responsibility_id AND frf.action_id = fmev.sub_menu_id
     WHERE fmev.prompt IS NOT NULL
       AND frf.responsibility_id IS NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       AND fmev.prompt IS NOT NULL
       AND frf.responsibility_id IS NULL
START WITH frv.responsibility_name = 'System Administrator'
ORDER SIBLINGS BY fmev.entry_sequence;

-- v2

    SELECT fmev.entry_sequence seq
         , LPAD(' ', (LEVEL - 1) * 4, ' ') || fmev.prompt prompt
         , fmev.menu_id
         , fmev.sub_menu_id         
         , fmev.function_id
      FROM apps.fnd_menus_vl fmv
      JOIN applsys.fnd_menus_tl fmt ON fmv.menu_id = fmt.menu_id
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
 LEFT JOIN apps.fnd_responsibility_vl frv ON fmv.menu_id = frv.menu_id
     WHERE fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       AND fmev.prompt IS NOT NULL
START WITH fmt.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;
  
-- v3

      SELECT fmev.entry_sequence seq
           , LPAD(' ', (LEVEL - 1) * 10, ' ') || fmev.prompt prompt
           , fmv.menu_name
           , CASE
                 WHEN fmev.function_id IS NOT NULL
                    THEN 'Function'
                 ELSE 'Menu'
              END m_f        
           , CASE
                 WHEN fmev.function_id IS NOT NULL
                    THEN (SELECT 'Function: ' || function_name
                            FROM apps.fnd_form_functions_vl
                           WHERE function_id = fmev.function_id)
                 ELSE (SELECT 'Menu: ' || menu_name
                         FROM apps.fnd_menus_vl e
                        WHERE e.menu_id = fmev.sub_menu_id)
              END this
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmv.user_menu_name = 'CST_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

-- v4
-- https://community.oracle.com/thread/535595

    SELECT LEVEL
         , fm.menu_id
         , fm.menu_name
         , fm.user_menu_name
         , fme.sub_menu_id
         , fme.function_id
         , fff.function_name
         , fff.user_function_name
      FROM apps.fnd_menus_vl fm
         , apps.fnd_menu_entries fme
         , apps.fnd_form_functions_vl fff
     WHERE fme.menu_id = fm.menu_id
       AND fff.function_id(+) = fme.function_id
CONNECT BY fm.menu_id = PRIOR fme.sub_menu_id
START WITH fm.user_menu_name = 'INV_NAVIGATE';

-- v5
-- https://community.oracle.com/thread/1057001

       SELECT LPAD(' ',(LEVEL - 1) * 20)
           || TRIM(user_menu_name)
           || ' -> '
           || CASE
                 WHEN me.function_id IS NOT NULL
                    THEN (SELECT user_function_name
                            FROM apps.fnd_form_functions_vl
                           WHERE function_id = me.function_id)
                 ELSE (SELECT user_menu_name
                         FROM apps.fnd_menus_vl e
                        WHERE e.menu_id = me.sub_menu_id)
              END nam
      FROM apps.fnd_menus_vl fm
         , apps.fnd_menu_entries me
     WHERE me.menu_id = fm.menu_id
START WITH fm.user_menu_name = 'INV_NAVIGATE'
CONNECT BY PRIOR me.sub_menu_id = fm.menu_id;

-- v6
-- another one

    SELECT m.menu_name
         , m.user_menu_name
         , m.sub_menu
         , LEVEL
         , SYS_CONNECT_BY_PATH(m.sub_menu, '/') PATH
      FROM (SELECT fmv.menu_name
                 , fmv.user_menu_name
                 , fmv.user_menu_name sub_menu
                 , fmev.sub_menu_id
                 , fmv.menu_id
              FROM apps.fnd_menu_entries_vl fmev
                 , apps.fnd_menus_vl fmv
             WHERE fmv.menu_id = fmev.menu_id) m
CONNECT BY PRIOR sub_menu_id = menu_id
START WITH user_menu_name = 'INV_NAVIGATE';

Responsibilities – Exclusions

Each responsibility is linked to a menu. If you have 2 responsibilities linked to the same menu, you can limit what specific parts of the menu each responsibility has access to by using exclusions.

You can decide to restrict functions, or menus… but getting that data out in a meaninful way can be a bit limited. The main limitation with using the responsibility form is that you can’t search for items in it. For example, you might want to check if a responsibility has a specific exclusion against it. If you click into the exclusions, and press F11, enter something to search for, and press CTRL + F11, the search doesn’t work, it just returns everything.

This SQL can be handy when you want to search for specific exclusions, or just want to export everything at once.

Simple Exclusions List

  SELECT frt.responsibility_name
       , frf.creation_date
       , frf.created_by
       , DECODE (frf.rule_type,  'M', 'Menu',  'F', 'Function') type_
       , CASE
            WHEN frf.rule_type = 'M'
            THEN
               (SELECT fmv.user_menu_name
                  FROM apps.fnd_menus_vl fmv
                 WHERE frf.action_id = fmv.menu_id
                   AND frf.rule_type = 'M')
            WHEN frf.rule_type = 'F'
            THEN
               (SELECT ffvl.user_function_name
                  FROM apps.fnd_form_functions_vl ffvl
                 WHERE frf.action_id = ffvl.function_id
                   AND frf.rule_type = 'F')
         END
            detail
       , CASE
            WHEN frf.rule_type = 'M'
            THEN
               (SELECT fmv.menu_name
                  FROM apps.fnd_menus fmv
                 WHERE frf.action_id = fmv.menu_id
                   AND frf.rule_type = 'M')
            WHEN frf.rule_type = 'F'
            THEN
               (SELECT ffvl.function_name
                  FROM apps.fnd_form_functions ffvl
                 WHERE frf.action_id = ffvl.function_id
                   AND frf.rule_type = 'F')
         END
            detail2
    FROM apps.fnd_resp_functions frf
       , applsys.fnd_responsibility_tl frt
       , applsys.fnd_user fu
   WHERE frf.responsibility_id = frt.responsibility_id
     AND frf.created_by = fu.user_id
     AND frt.responsibility_name LIKE '%General%Ledger%';

All Exclusions On Responsibilities, Another Way To Get Info Using A Union

SELECT frt.responsibility_name
     , fmv.menu_name
     , fmv.user_menu_name menu
     , fmv.description menu_desc
     , '' fcn_name
     , '' fcn
     , '' fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_menus_vl fmv
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = fmv.menu_id
   AND frf.rule_type = 'M'
   AND frt.responsibility_name = 'AP Inquiry'
UNION
SELECT frt.responsibility_name
     , ffvl.function_name
     , '' menu
     , '' menu_desc
     , ffvl.function_name fcn_name
     , ffvl.user_function_name fcn
     , ffvl.description fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_form_functions_vl ffvl
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = ffvl.function_id
   AND frf.rule_type = 'F'
   AND frt.responsibility_name = 'AP Inquiry';

All Exclusions On Responsibilities With Resp Assigned Stats

SELECT frt.responsibility_name
     , (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct
     , fmv.menu_name
     , fmv.user_menu_name menu
     , fmv.description menu_desc
     , '' fcn_name
     , '' fcn
     , '' fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_menus_vl fmv
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = fmv.menu_id
   AND frf.rule_type = 'M'
--   AND frt.responsibility_name = 'AP Inquiry'
   AND (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0
UNION
SELECT frt.responsibility_name
     , (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id 
	AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct
     , ffvl.function_name
     , '' menu
     , '' menu_desc
     , ffvl.function_name fcn_name
     , ffvl.user_function_name fcn
     , ffvl.description fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_form_functions_vl ffvl
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = ffvl.function_id
   AND frf.rule_type = 'F'
--   AND frt.responsibility_name = 'AP Inquiry'
   AND (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0;

Menu Exclusions On Responsibilities

SELECT frt.responsibility_name
     , fmv.menu_name
     , fmv.user_menu_name ex_name
     , fmv.description menu_description
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_menus_vl fmv
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = fmv.menu_id
   AND frf.rule_type = 'M'
   AND frt.responsibility_name = 'Inventory';

Function Exclusions On Responsibilities

SELECT frt.responsibility_name
     , ffvl.function_name
     , ffvl.user_function_name
     , ffvl.description function_description
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_form_functions_vl ffvl
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = ffvl.function_id
   AND frf.rule_type = 'F'
   AND ffvl.function_name = 'INV_INVTTMTX_MISC'
--   AND frt.responsibility_name = 'Inventory'
   AND 1 = 1;

Lookup Values

-- ##############################################################################
--      LOOKUPS
-- ##############################################################################

     select flt.lookup_type
          , fltt.description
          , fat.application_name app
          , flv.lookup_code
          , flv.meaning
          , flv.enabled_flag
          , TO_CHAR(start_date_active, 'DD-MON-RRRR') from_
          , TO_CHAR(end_date_active, 'DD-MON-RRRR') to_
          , flv.creation_date
          , fu_cr.user_name cr_by
          , flv.last_update_date
          , fu_up.user_name up_by
       from fnd_lookup_types flt 
       JOIN fnd_lookup_types_tl fltt ON flt.lookup_type = fltt.lookup_type
       JOIN fnd_application_tl fat on flt.application_id = fat.application_id
       JOIN fnd_lookup_values flv ON flv.lookup_type = flt.lookup_type
       JOIN fnd_user fu_cr ON flv.created_by = fu_cr.user_id
       JOIN fnd_user fu_up ON flv.last_updated_by = fu_up.user_id
      WHERE 1 = 1
        AND flt.LOOKUP_TYPE = 'BOB_HOPE_JOKES'
        AND 1 = 1;

User Logins

If a user is end-dated on a test system, and you remove the end-date from their account but don’t reset their password, you will show as the person who last updated the record even if that user logs in.

If you reset a user’s password, then when they log in they have to choose a new password. They then show as the last person to update their FND user account.

-- user, login details, login count

select fu.user_name
     , (select max(start_time) from applsys.fnd_logins fl where fl.user_id = fu.user_id) last_login
     , (select count(*) from applsys.fnd_logins fl where fl.user_id = fu.user_id) login_count
     , (select count(*) from applsys.fnd_logins fl where fl.user_id = fu.user_id AND start_time > TRUNC(SYSDATE)) login_count_today 
  from applsys.fnd_user fu
 where 1 = 1
--   AND fu.user_name IN ('BOBHOPE')
   AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
   ORDER BY 3 DESC;

-- login details line level

SELECT fu.description
     , fu.user_name
     , fl.start_time
     , fl.*
  FROM applsys.fnd_logins fl
  JOIN applsys.fnd_user fu ON fl.user_id = fu.user_id 
 WHERE 1 = 1
   AND fl.login_type = 'FORM'
   AND fl.user_id > 0 -- NOT SYSADMIN
--   AND fu.user_name = 'BOBHOPE'
   AND fl.start_time > TRUNC (SYSDATE) - 1
   AND 1 = 1
ORDER BY fl.start_time DESC;

-- logins last x days

SELECT fu.description
     , fu.user_name
     , count(*) ct
  FROM applsys.fnd_logins fl
  JOIN applsys.fnd_user fu ON fl.user_id = fu.user_id
 WHERE 1 = 1
   AND fl.start_time > TRUNC (SYSDATE) - 5
   AND fl.login_type = 'FORM'
   AND fl.user_id > 0 -- NOT SYSADMIN
--   AND fu.user_name = 'BOBHOPE'
   AND 1 = 1
GROUP BY fu.description
     , fu.user_name
ORDER BY 3 DESC;

-- unsuccessful logins

SELECT fu.description
     , fu.user_name
     , ful.*
  FROM applsys.fnd_unsuccessful_logins ful
  JOIN applsys.fnd_user fu ON ful.user_id = fu.user_id 
 WHERE 1 = 1
--   AND fu.user_name = 'BOBHOPE'
ORDER BY ful.attempt_time DESC;


SQL to Check for Invalid Email Settings for Users

Sometimes users have incorrect notification preferences set, so don’t receive email notifications. Or users might have incorrectly formatted email addresses. The SQLs can help with finding such issues.

-- ##############################################################################
--         INCORRECT E-MAIL SETTINGS
-- ##############################################################################

SELECT *
  FROM applsys.wf_local_roles
 WHERE notification_preference <> 'MAILHTML'
   AND orig_system = 'PER';
   
-- ##############################################################################
--        USERS WHERE NOTIFICATION_PREFERENCE IS NOT SET TO ‘MAILHTML’
-- ##############################################################################

SELECT fu.user_id
     , fu.user_name
     , wlr.notification_preference
     , fu.email_address fu_email
     , wlr.email_address wlr_email
  FROM applsys.fnd_user fu
     , applsys.wf_local_roles wlr
 WHERE wlr.NAME = fu.user_name
   AND wlr.orig_system = 'PER'
   AND wlr.notification_preference <> 'MAILHTML';

-- ##############################################################################
--        USERS WHERE THE EMAIL ADDRESS CONTAINS INVALID CHARACTERS.
--        https://community.oracle.com/thread/658934
-- ##############################################################################

SELECT fu.user_id
     , fu.user_name
     , wlr.notification_preference
     , wlr.email_address
     , DECODE(
          TRANSLATE(
             LOWER(wlr.email_address)
           , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'
           , '+'
          )
        , ' ', 'SPACE'
        , (
           TRANSLATE(
              LOWER(wlr.email_address)
            , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'
            , '+'
           )
          )
       ) invalid_character
  FROM applsys.fnd_user fu
     , applsys.wf_local_roles wlr
 WHERE wlr.NAME = fu.user_name
   AND wlr.orig_system = 'PER'
   AND TRANSLATE(
          LOWER(wlr.email_address)
        , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'
        -- + followed by all the acceptable characters
       ,  '+'
       ) IS NOT NULL;

--- another version

SELECT   fu.user_id
       , fu.user_name
       , wlr.notification_preference
       , wlr.email_address
       , NVL
            (
            DECODE(
               TRANSLATE(
                  LOWER(wlr.email_address)
                , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
                , '+'
               )
             , ' ', 'SPACE'
             , (
                TRANSLATE(
                   LOWER(wlr.email_address)
                 , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
                 , '+'
                )
               )
            )
          , 'NONE'
         ) invalid_character
       , CASE
            WHEN TRANSLATE(
                   LOWER(wlr.email_address)
                 , '+0123456789abcdefghijklmnopqrstuvwxyz/-'''
                 , '+'
                ) LIKE '%@.%'
            AND TRANSLATE(
                   LOWER(wlr.email_address)
                 , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
                 , '+'
                ) IS NULL
               THEN 'VALID'
            ELSE 'INVALID'
         END email_format
    FROM applsys.fnd_user fu
       , applsys.wf_local_roles wlr
   WHERE wlr.NAME = fu.user_name
     AND wlr.orig_system = 'PER'
     AND (
             (
              TRANSLATE(
                 LOWER(wlr.email_address)
               , '+0123456789abcdefghijklmnopqrstuvwxyz.@/-'''
               , '+'
              ) IS NOT NULL
             )
          OR (
              TRANSLATE(
                 LOWER(wlr.email_address)
               , '+0123456789abcdefghijklmnopqrstuvwxyz/-'''
               , '+'
              ) NOT LIKE '%@.%'
             )
         )
ORDER BY fu.user_id;