Query to get list of online application FND users with responsibility names and forms

SELECT user_name, responsibility_name, user_form_name, TIME, pid
  FROM fnd_signon_audit_view
 WHERE responsibility_name IS NOT NULL;

Query to get Responsibility List For Specific Function (Enter Function ID)

SELECT DISTINCT responsibility_name
           FROM fnd_responsibility_vl
          WHERE 1 = 1
            AND menu_id IN (
                   SELECT a.menu_id
                     FROM fnd_menus_vl a, fnd_menu_entries_vl b
                    WHERE a.menu_id = b.menu_id AND b.function_id = 1521 --Enter function id
                   UNION
                   SELECT menu_id
                     FROM fnd_menu_entries
                    WHERE sub_menu_id IN (
                             SELECT menu_id
                               FROM fnd_menu_entries_vl
                              WHERE sub_menu_id IN (
                                       SELECT a.menu_id
                                         FROM fnd_menus_vl a,
                                              fnd_menu_entries_vl b
                                        WHERE a.menu_id = b.menu_id
                                          AND b.function_id = 1521)) -- Enter function id
                   UNION
                   SELECT menu_id
                     FROM fnd_menu_entries_vl
                    WHERE sub_menu_id IN (
                             SELECT a.menu_id
                               FROM fnd_menus_vl a, fnd_menu_entries_vl b
                              WHERE a.menu_id = b.menu_id
                                AND b.function_id = 1521)) -- Enter function id
            AND end_date IS NULL
MINUS
(SELECT c.responsibility_name
   FROM fnd_resp_functions a, fnd_responsibility b, fnd_responsibility_vl c
  WHERE a.action_id = 1521 --Enter function id
    AND b.responsibility_id = a.responsibility_id
    AND b.responsibility_id = c.responsibility_id
    AND c.end_date IS NULL)
       ORDER BY responsibility_name;

Query to list functions, responsibilities and users (Enter user function name)

SELECT DISTINCT u.user_name, rtl.responsibility_name, ff.function_name,
                ffl.user_function_name
           FROM fnd_compiled_menu_functions cmf,
                fnd_form_functions ff,
                fnd_form_functions_tl ffl,
                fnd_responsibility r,
                fnd_responsibility_tl rtl,
                fnd_user_resp_groups urg,
                fnd_user u
          WHERE cmf.function_id = ff.function_id
            AND r.menu_id = cmf.menu_id
            AND urg.responsibility_id = r.responsibility_id
            AND rtl.responsibility_id = r.responsibility_id
            AND cmf.grant_flag = 'Y'
            AND r.application_id = urg.responsibility_application_id
            AND u.user_id = urg.user_id
            --and ff.function_id=19438
            AND UPPER (ffl.user_function_name) LIKE UPPER ('Users') --Enter user function name here
            AND ff.function_id = ffl.function_id
       ORDER BY u.user_name;

Query to check EBS application product license status

--Note: WHERE STATUS can be I - Licensed, S - shared , N - NOT Licensed

SELECT   SUBSTR (app.application_short_name, 1, 10) NAME, app.application_id,
         pi.product_version, pi.status, pi.patch_level
    FROM fnd_product_installations pi, fnd_application app
   WHERE app.application_id = pi.application_id
     AND app.application_short_name LIKE '&&Application_Short_Name' --Enter application short name here
ORDER BY app.application_short_name;

License Status for all products with patch level:

SELECT fat.application_name
      ,fa.application_id
      ,fpi.patch_level
      ,decode(fpi.STATUS,'I','Licensed', 'N','Not Licensed','S','Shared','Undetermined') STATUS
  FROM fnd_product_installations fpi
      ,fnd_application fa
      ,fnd_application_tl fat
 WHERE fpi.application_id = fa.application_id
   AND fat.application_id = fa.application_id
   AND fat.LANGUAGE = 'US';

Query to find Application short name:

SELECT fa.application_short_name, fat.application_id, fa.basepath,
       fa.product_code
  FROM fnd_application_tl fat, fnd_application fa
 WHERE fat.application_id = fa.application_id
   AND fat.application_name LIKE '%Cost%%Management%'; --Enter Application full name here

Query to get list of responsibilities assigned to particular FND user

SELECT   usr.user_name,usr.description,res.responsibility_name, en.start_date
        -- en.end_date,
FROM     fnd_user usr,
         fnd_user_resp_groups_direct en,
         fnd_responsibility_vl res
   WHERE usr.user_id = en.user_id
     AND en.responsibility_id = res.responsibility_id
     AND usr.user_name IN ('RRC41569') --Enter FND username here
     AND usr.end_date IS NULL
     AND en.end_date IS NULL                    --Without end Date
ORDER BY responsibility_name;

Query to get list of FND users having particular responsibility

SELECT DISTINCT res.responsibility_name, usr.user_name, usr.description
           FROM fnd_user usr,
                fnd_user_resp_groups_direct en,
                fnd_responsibility_vl res
          WHERE usr.user_id = en.user_id
            AND en.responsibility_id = res.responsibility_id
            AND res.responsibility_name IN ('System Administrator') --Enter responsibility name here
            AND usr.end_date IS NULL   --For Active users
            AND en.end_date IS NULL
       ORDER BY responsibility_name;

Query to list all responsibilities with their excluded functions and menus

SELECT frv.responsibility_name,
       DECODE (frf.rule_type,
               'F', 'Function',
               'M', 'Menu',
               rule_type
              ) exclusion_type,
       DECODE (frf.rule_type,
               'F', (SELECT function_name || ',' || description
                       FROM fnd_form_functions_vl fnc
                      WHERE fnc.function_id = frf.action_id),
               'M', (SELECT menu_name || ',' || description
                       FROM fnd_menus_vl imn
                      WHERE imn.menu_id = frf.action_id),
               TO_CHAR (frf.action_id)
              ) excluded_menu_or_func
  FROM apps.fnd_resp_functions frf,
       apps.fnd_responsibility_vl frv
WHERE frf.responsibility_id = frv.responsibility_id
--and frv.responsibility_id = '64186';

Query to get concurrent program running from which responsibility

SELECT   responsibility_name, frg.request_group_name,
         fcpv.user_concurrent_program_name, fcpv.description
    FROM fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_vl fcpv,
         fnd_responsibility_vl frv
   where fcpv.user_concurrent_program_name like 'Create Accounting' -- Eneter concurrent program name
     AND frgu.request_unit_type like 'P'
     AND frgu.request_group_id = frg.request_group_id
     AND frgu.request_unit_id = fcpv.concurrent_program_id
     AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query to find the EBS application version upgrade history details

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, 
       START_DATE_ACTIVE updated,
       ROW_SOURCE_COMMENTS "how it is done", 
       BASE_RELEASE_FLAG "Base version" 
  FROM AD_RELEASES 
  where END_DATE_ACTIVE IS NULL;

Query to check status of Concurrent managers from backend database

SELECT   t.user_concurrent_queue_name AS "Concurrent Manager Name", 
         b.max_processes AS "Actual Processes", 
         b.running_processes AS "Target Processes", 
         b.concurrent_queue_name, b.cache_size,
         b.min_processes, b.target_processes, b.target_node, b.sleep_seconds, 
         b.diagnostic_level, b.manager_type, b.enabled_flag, t.description
    FROM fnd_concurrent_queues_tl t, fnd_concurrent_queues b
   WHERE b.application_id = t.application_id 
    AND b.concurrent_queue_id = t.concurrent_queue_id 
    AND b.enabled_flag = 'Y' 
    AND t.LANGUAGE = USERENV ('LANG')
ORDER BY b.max_processes DESC;

Query to find concurrent program execution file name, type from user concurrent program name

SELECT b.user_concurrent_program_name, b.concurrent_program_name,
       a.user_executable_name,
       DECODE (a.execution_method_code,
               'I', 'PL/SQL Stored Procedure',
               'H', 'Host',
               'S', 'Immediate',
               'J', 'Java Stored Procedure',
               'K', 'Java concurrent program',
               'M', 'Multi Language Function',
               'P', 'Oracle reports',
               'B', 'Request Set Stage Function',
               'A', 'Spawned',
               'L', 'SQL*Loader',
               'Q', 'SQL*Plus',
               'E', 'Pearl concurrent Programm',
               'Unkown Type'
              ) AS "Method Type",
       a.execution_file_name, a.application_name, a.execution_file_path
  FROM fnd_executables_form_v a, fnd_concurrent_programs_vl b
 WHERE a.executable_id = b.executable_id
   AND a.application_id = b.application_id
   AND a.executable_id > 4
   AND b.user_concurrent_program_name LIKE 'Active Users%' --Enter user concurrent program name here
   AND a.execution_method_code <> 'A' 
   AND b.ENABLED_FLAG = 'Y'
   order by a.execution_method_code;

Query to find application product short name

SELECT fa.application_short_name, fat.application_id, fa.basepath,
fa.product_code
FROM fnd_application_tl fat, fnd_application fa
WHERE fat.application_id = fa.application_id
AND fat.application_name like 'Inventory'; -- Enter full application name here

Query to get menu function hierarchy for the given responsibility name

SELECT /*+ALL_ROWS */
       srno, b.entry_sequence, padding,
       RPAD (' ', 4 * (padding - 1)) || prompt menuprompt, a.description,
       b.menu_id, b.sub_menu_id, b.function_id, c.user_function_name
  FROM apps.fnd_menu_entries_tl a,
       (SELECT     ROWNUM srno, LEVEL padding, menu_id, entry_sequence,
                   sub_menu_id, function_id
              FROM apps.fnd_menu_entries
        CONNECT BY menu_id = PRIOR sub_menu_id
        START WITH menu_id IN (
                      SELECT /*+ALL_ROWS */
                             menu_id
                        FROM apps.fnd_responsibility_vl
                       WHERE NVL (end_date, SYSDATE) > = SYSDATE
                         AND responsibility_name = 'Application Developer')) b, -- Enter responsibility name here
       fnd_form_functions_tl c
 WHERE a.menu_id = b.menu_id
   AND a.entry_sequence = b.entry_sequence
   AND a.LANGUAGE = USERENV ('LANG')
   --AND a.menu_id IN ('80825')
   AND b.function_id = c.function_id(+);

Oracle Applications Query to know the fmb name along with path if you know the user form name

SELECT
ffv.form_name, ffv.user_form_name, ffv.description,
(SELECT application_name FROM fnd_application_tl WHERE application_id = ffv.application_id) application_name,
(SELECT basepath FROM fnd_application WHERE application_id = ffv.application_id) basepath,
form_id,
audit_enabled_flag
FROM
fnd_form_vl ffv
WHERE
(form_id >= 0) AND UPPER (user_form_name) LIKE '%MASS CHANGE%'; --Enter user form name in upper case

Query to get Application Patch Applied History

select distinct aap.patch_name,  apr.patch_top,  aat.name,  aap.patch_type,  aap.creation_date,  fav.APPLICATION_NAME,apd.patch_abstract
from ad_patch_runs apr,
     ad_patch_drivers apd,
     ad_applied_patches aap,
     ad_patch_run_bugs aprb,
     ad_appl_tops aat,
     fnd_application_vl fav
where apr.appl_top_id = aat.appl_top_id
  and apr.patch_driver_id = apd.patch_driver_id
  and apr.patch_run_id = aprb.patch_run_id
  and apd.applied_patch_id = aap.applied_patch_id
  and UPPER(aprb.application_short_name) =fav.application_short_name
  and aprb.applied_flag = 'Y'
and aap.creation_date > to_date('01-DEC-2015','DD-MM-YYYY') -- Enter Date
and aat.name='appsnode1' -- Enter application node hostname
order by aap.creation_date;

Users unable to see the assigned responsibilities On E-Business Suite Login Navigator

Issue:

Sometimes we face the issue that responsibilities does not show up on E-Business Suite Login Navigator after assigning to the user

Cause:

User data in the FND_USER, FND_RESPONSIBILITY, WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS tables is not valid.

Solution:

To synchronize data follow the steps below:

1.  Navigate to the define user form (FNDSCAUS).

2.  Query up the user (that is having the issue) and insure the responsibility is NOT end dated.

3.  END DATE the USER (not the responsibility) and save the record.

4.  UNEND DATE the USER and save the record.

5.  Run the request 'Sync responsibility role data into the WF table'.

6.  Run the request "Workflow Directory Services User/Role Validation" (with parameters 10000, yes, yes, yes).

7.  Have the user log off and back on and verify that the responsibility appears.

Ref. Note ID:433466.1

For "Workflow Directory Services User/Role Validation" Request "Workflow Agent Listener Service" must be running

Ref. Note ID:733335.1

To Start the "Workflow Agent Listener Service"

Login as SYSADMIN in OAM then navigate to:

1. Sitemap > Generic Services > Status Overview > View All > Generic Service Component Container > select Workflow Agent Listener Service > Start 

Unable to query some FND users in EBS 11i from front end "Users" Form

Issue:

Recently I came across into the strange issue where some FND users are unable to query from "Users" form.

Using the EBS 11i instance, and querying a user (System Administrator > Security > User > Define), the application is spinning.

Cause:

The instance was earlier configured with SSO however EBS SSO Users not changed to EBS Local User.

DBMS_LDAP: LDAP operation - waiting event due to OID was configured earlier and trying to use DBMS_LDAP while querying the user.

select USER_GUID from FND_USER where USER_NAME='KBROCK';

USER_GUID
--------------------------------
AC76C1E60F002181E040A8C00C0A063C


--Query to check session wait:

set lines 120
set pages 1000
col event for a30
select sid, event, p1, p2, p3, p1raw from v$session_wait
where event not like '%messag%' and event not in ('pipe get','PL/SQL lock timer','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator idle wait')
and (wait_time=0 or state='WAITING')
order by 2
/


--Ouptut :

       SID EVENT                                  P1         P2         P3 P1RAW
---------- ------------------------------ ---------- ---------- ---------- --------
       424 DBMS_LDAP: LDAP operation               0          0          0 00
       430 DBMS_LDAP: LDAP operation               0          0          0 00
       600 pmon timer                            300          0          0 0000012C
       594 smon timer                            300          0          0 0000012C

Solution:

1. Unlink a FND_USER user account in Oracle EBusiness Suite that is linked to an SSO Account in Oracle Internet Directory

[approd@oradb ~]$ cd $FND_TOP/patch/115/sql/
[approd@oradb sql]$ ls -ltr fndssouu.sql

-rwxr-xr-x 1 approd dba 2102 Dec  1  2010 fndssouu.sql
[approd@oradb sql]$
[approd@oradb sql]$ sqlplus apps/apps

SQL*Plus: Release 8.0.6.0.0 - Production on Wed Oct 7 22:17:53 2015

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @fndssouu.sql KBROCK

PL/SQL procedure successfully completed.


Commit complete.

    2.  Kill the earlier DBMS_LDAP: LDAP operation sessions

    3.  Reset user_guid in bulk

--Script for all users having user_guid:

spool userguid.sh
SELECT 'sqlplus apps/apps  @$FND_TOP/patch/115/sql/fndssouu.sql  '||user_name ||' ;'
FROM fnd_user
WHERE user_guid IS NOT NULL
spool off;
/

$ sh userguid.sh > userguid.log

    4. Retest the issue.

Reference Metalink Notes:

• How to unlink a FND_USER user account in Oracle EBusiness Suite that is linked to an SSO   Account in Oracle Internet Directory (Doc ID 429375.1)
• Customer RecommendedHow To Change An EBS SSO User Change To EBS LOCAL User (Doc ID 1931776.1)