Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Wednesday, December 23, 2015

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;

Tuesday, December 22, 2015

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;

Wednesday, December 16, 2015

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