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)