Query to get all the personalization on the OAF pages in the application

SELECT   PATH.path_docid perz_doc_id,

         jdr_mds_internal.getdocumentname (PATH.path_docid) perz_doc_path

    FROM jdr_paths PATH

   WHERE PATH.path_docid IN (

            SELECT DISTINCT comp_docid

                       FROM jdr_components

                      WHERE comp_seq = 0

                        AND comp_element = 'customization'

                        AND comp_id IS NULL)

     AND UPPER (jdr_mds_internal.getdocumentname (PATH.path_docid)) LIKE

                                                 UPPER ('%AbsenceHomePagePG%')

ORDER BY perz_doc_path;

View the output of a non XML report in Excel format

To view the output of a non XML report in Excel format, please follow the below steps:

1-      Run the request and wait for becoming (Complete) and then press the (View Output) button.
2-      Form the menu, choose Tools => Copy File
3-      This will open your output in the internet browser.

From the menu, Choose File => Save As

4-      Save the file as *.txt
5-      Open this txt file using the Excel
6-      Save you work.

Query to get application users of a a responsibility

SELECT fu.user_name "User Name", fr.responsibility_name "Responsibility",
fa.application_name "Application", fur.start_date "From",
fur.end_date "To", fm.user_menu_name "Menu",
frg.request_group_name "Request Group"
FROM fnd_user fu,
fnd_user_resp_groups_all fur,
fnd_responsibility_vl fr,
fnd_application_vl fa,
fnd_menus_vl fm,
fnd_request_groups frg
WHERE fu.user_id = fur.user_id
AND fur.responsibility_id = fr.responsibility_id
AND fr.application_id = fa.application_id
AND fr.menu_id = fm.menu_id
AND fr.request_group_id = frg.request_group_id(+)
AND UPPER (fr.responsibility_name) = UPPER ('System Administration')

Password Security in Oracle Apps R12

Signon Password Failure Limit

The Signon Password Failure Limit profile option determines the maximum number of

login attempts before the user's account is disabled.

Users cannot see or update this profile option.

The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess

The Signon Password Hard to Guess profile option sets rules for choosing passwords

to ensure that they will be "hard to guess." A password is considered hard-to-guess

if it follows these rules:

. The password contains at least one letter and at least one number.

. The password does not contain the username.

. The password does not contain repeating characters.

Users can see but not update this profile option.

The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length

Signon Password Length sets the minimum length of an Applications signon password.

If no value is entered the minimum length defaults to 5.

Users can see but not update this profile option.

The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse

This profile option specifies the number of days that a user must wait before being

allowed to reuse a password.

Users can see but not update this profile option.

The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Signon Password Case

This profile option is not available from the beginning.

With 11i.ATG_PF.H RUP3 comes the system profile 'Password Case Option'

After 11i.ATG_PF.H.RUP4 this system profile option was renamed to 'Signon Password Case'.

There are two settings: 'Sensitive' and 'Insensitive'.

The default is 'Insensitive'.

Setting this profile option to 'Sensitive' will make the password case sensitive.

'Mixed' is no longer supported.


These profiles should only be set at Site level.

They can be set at other levels, such as User or Responsibility.


However, when logging in there is no User context, so if a User is prompted to

change their password at login, the profiles are only evaluated at site level.



Once logged in and resetting passwords using Preferences->Change Password,

or the Security ->User->Define form these other levels will have effect and will confuse the issue.


So these profiles should only be set at Site level, for consistent enforcement.

Cancel/ Restart the Cost Manager

Behavior of Cost Manager:

Cost manager is not a typical concurrent manager that it finishes after is costing some transactions.
Cost manager it is rescheduling itself based on the the process interval specified in interface managers form.

Cost Manager should stay in this status : “Pending Scheduled” and will restart itself based on the process interval specified in interface managers form. It calculates the re-launch time as start time + process interval.
But there is no change in status when this happen

To cancel/ Restart the Cost Manager : use one of the following method:

Note : If you want to launch a new manager, cancel earlier manager first.

a) use 'System Administrator' responsibility
Navigate to Concurrent/Request/view
Set : specific Request=on
Name=Cost Manager.

In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days.
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.

From here Cancel the Cost Manager which is Pending Scheduled.
or


2 . Stop the Cost Manager.

a) If the above query did not retrieve any records then there is nothing to do, the Cost Manager is already stopped.

b) Otherwise, if the first record is showing Phase=Completed and Status=Warning, then review the request log.
If the request log is showing the following, it suggests there is an older concurrent request in Running or Pending phase:
"A Cost Manager is already launched.

Action: If you want to launch a new manager, cancel earlier manager first. (MODULE=CMCTCM) : "
In this case, scroll down until encountering the relevant concurrent request.


c) If the record is showing Phase=Completed, then there is nothing to do, the Cost Manager is already stopped.

d) If the record is showing Phase=Running then wait and query again and again using the Refresh Data button, until Phase becomes Completed or Pending.

e) When the record is showing Phase=Pending and Status=Scheduled, then perform the following quickly:
i) cancel the concurrent request using the Cancel button at the bottom
ii) press button Yes to acknowledge the Decision message.

f) If the Cost Manager was running, you may have to wait for the completion of the cost workers. There are different program names here, depending on the costing methods used at your site.
As soon as the Cost Manager is stopped, you can monitor the costing process by repeatedly running the following SQL:

i) cost workers:

SQL> select organization_id,costed_flag,count(*) from mtl_material_transactions where costed_flag is not null group by organization_id,costed_flag;

or

ii) COSTMANAGER SQL script:


SQL> SELECT request_id RequestId,
request_date RequestDt, concurrent_program_name,
phase_code Phase,
status_code Status FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
WHERE fcp.application_id = 702 AND
fcp.concurrent_program_name in ('CMCTCM', 'CMCMCW', 'CMCACW') AND
fcr.concurrent_program_id = fcp.concurrent_program_id AND
fcr.program_application_id = 702 AND fcr.phase_code <> 'C'

Cancel all the requests from the above output by navigating to,
System Administrator à Concurrent à Request.
Query for the Request Id returned from the select above.

In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.

Cancel the request id other than running. Let the running request get over.

2 - Run the cmclean.sql script available from MOS Note 134007.1 //< not required >


3 - Restart the Cost Manager


Inventory  >> Setup : Transactions >> Interface Managers


4. The Interface Manager screen pops up . The Cost Manager should be the“first” one and its status should be “Active”.


5. A list of organizations pops up :
Select 'Cost Manager' - choose Tools à Launch Manager


If it is not, select it then click on tools on the menu bar and select launch. It will start and run according to its schedule.


Tools Menu à select Launch Manager


Check to see whether actual cost worker completes successfully. Also verify using the below that there are no error records:


SQL> select * from mtl_material_transactions where costed_flag = 'E';


Note : few important points :
To verify the version :
SQL> SELECT name, text FROM dba_source
WHERE name LIKE 'CMCTCM'
AND UPPER(text) LIKE '%$HEADER%'
AND line < 7;


PROCESS INTERVAL :
select PROCESSING_TIMEOUT, WORKER_ROWS, PROCESS_INTERVAL
from mtl_interface_proc_controls
where PROCESS_NAME='CMCTCM';
process_interval is in seconds and not in minutes. To schedule the cost manager every, say, five minutes,




a). Relink the CMCTCM executable: adrelink.sh force=y ranlib=y "bom CMCTCM"
b). Create Accounting - Cost Management Cannot Print Report out of memory [ID 1062578.1]
c). Data Collection Scripts for Cost Management [ID 378348.1]
d). EBS LCM - How to do Setup for Landed Cost Management LCM [ID 837161.1]
e). How to resolve Pending or Erred Cost Manager or Cost Workers [ID 748704.1]
f). Cost Manager (CMCTCM) - is Scheduled More Than Once [ID 255947.1]
g). Cost Manager not processing records [ID 217068.1]
++++++++++++Manjunatha+++++++++++++++++++++
141493.1, 373207.1, 304313.1, 1360701.1, 740039.1 ,218204.1 & 304313.1
++++++++++++Manjunatha+++++++++++++++++++++

Difference between adautoconfig.sh, adautocfg.sh and adconfig.sh

Using AutoConfig you can reconfigure your Oracle E-Business Suite configuration from values mentioned in $CONTEXT_FILE

adautocfg.sh : is a wrapper around autoconfig script($AD_TOP/bin/adconfig.sh) .

a) For application Tier this file is located in $ADMIN_SCRIPTS_HOME (in R12) or $COMMON_TOP/admin/scripts/$CONTEXT_NAME (in 11i)

b) For Database Tierthis file file is in $ORACLE_HOME/ appsutil/ scripts $CONTEXT_NAME (both 11i & R12)

This file invokes $AD_TOP/bin/adconfig.sh on Application Tier and $ORACLE_HOME/ appsutil/bin/adconfig.sh on Database Tier.

adconfig.sh : is script (which in turn calls adconfig.pl) to run Autoconfig tool which reconfigures application or database tier using context file (XML file) . adconfig.sh is located in $AD_TOP/bin (For 11i & R12 Application Tier) and $ORACLE_HOME/appsutil/bin (For 11i & R12 Database Tier)

Find out if a product is installed in e-Business Suite or not

We can find out what products are installed in e-Business Suite, and their patch levels, via Oracle Applications Manager.

We will also find a list of the installed by running the script <adutconf.sql> ($AD_TOP/sql/adutconf.sql), or in the file <applprod.txt>. You can find out the patch level of any product you have installed in e-Business Suite by running the following query via SQL:

$ sqlplus apps / <pwd>


SQL>SELECT fav.application_id,
fav.application_short_name,
fav.application_name,
fav.basepath,
fav.creation_date,
fpi.status,
fpi.patch_level,
fpi.product_version,
fpi.TABLESPACE,
fpi.index_tablespace,
fpi.temporary_tablespace
FROM fnd_application_vl fav,
fnd_product_installations fpi
WHERE fav.application_id = fpi.application_id(+)
ORDER BY creation_date DESC


$ sqlplus apps / <pwd>

SQL> SELECT substr(APPLICATION_SHORT_NAME,1,8) product,
substr(PRODUCT_VERSION,1,14) version,
substr(PATCH_LEVEL,1,11) patch_level
FROM fnd_application a, fnd_product_installations p
WHERE a.application_id = p.application_id
ORDER BY application_short_name
/

If you know the product code, you can obtain output for individual products with the following SQL:

SQL> SELECT patch_level
FROM fnd_product_installations
WHERE patch_level LIKE '<product_code>'
/
for example: WHERE patch_level LIKE 'BIS'


The product code is the 2- or 3-letter code you see when you search for a patch in Metalink by product family, and in the name of the patchset, e.g. 11i.BIS.L.

As a double check that the database knows that the product is installed, you can run the following query. It should produce the same answer:

SQL> SELECT fa.application_id id, fa.application_short_name app, fa.application_name, fpi.status,
fpi.patch_level
FROM fnd_application_all_view fa, fnd_product_installations fpi
WHERE fa.application_id = fpi.application_id
AND fpi.patch_level LIKE '<product_code>'
/

Audit users in Oracle Applications

The auditing in Oracle Application could be a three step process.
Setting up Audit
Review the audit information
Purge the Audit information

Setting up front end access Audit:

As a standard feature, Oracle application does store information for user logon, wrong password attempt, and concurrent process execution. To enable the auditing at user, responsibility and form access, we need to enable the profile option ‘Sign-On: Audit Level’. This profile option has four values. Each value has different capability to capture audit information.

1. If the value is setup as ‘None’
No additional information is tracked.

2. If the value is setup as ‘User’
System tracks:
who signs on to Oracle Apps
the time users log on and off
the terminals in use

3. If the value is setup as ‘Responsibility’
System tracks:
User Info
the responsibilities user choose
how much time users spend using each responsibility

4. If the value is setup as ‘Form’
Tracks:
User Info
Responsibility Info
the forms users choose
how long users spend using each form

Apart from this below mentioned information is logged in oracle by default

Unsuccessful Logins

Unsuccessful password attempts are automatically recorded in the APPLSYS.FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES tables. There is no way to disable this functionality.

Concurrent Requests

All concurrent requests are recorded in the FND_CONCURRENT_REQUESTS table. There is no configuration required

Review the Audit information

There are two methods to access and review end user access audit information

Using Oracle Applications
There are standard reports to access signon, unsuccessful signon, responsibility usage, form usage, and concurrent request usage. These reports are accessed through the system administrator responsibility. The following are the standard reports for end-user auditing –
Signon Audit Users
Signon Audit Responsibilities
Signon Audit Forms
Signon Audit Concurrent Requests
Signon Audit Unsuccessful Logins
Or the same information can be retrieved from the sql plus as the information captured in the below tables

FND_LOGINS – holds information about users login to system, when and how long.

This table holds one row for each login.

FND_LOGIN_RESPONSIBILITIES – holds information about changes of responsibilities, when and how long being at each responsibility.
For each change this table holds one row with values that identify the user's login session, the user's current responsibility, and when the user is in the responsibility

FND_LOGIN_RESP_FORMS – holds information about using forms, when and how long.
This table holds one row for each form used in the same session with values that identify the user's login session, current responsibility, when and how long using each form.

In order to see the user audit information, there are 5 reports (concurrents) that you can use:
1) Signon Audit Concurrent Request
Show concurrent requests audit information – the user who run this request, when, from which responsibility and form, for all concurrent requests run in the system.

2) Signon Audit Forms
Show audit information about which user enter to which form, when and for how long.

3) Signon Audit Responsibilities
Show audit information about which user choose which responsibility, when and how long he stayed in each responsibility.

4) Signon Audit Unsuccessful Logins
Show audit information about unsuccessful logins to Oracle Applications.

5) Signon Audit Users
Show audit information about who sign on, when and for how long.


- In addition there is a monitor screen where you can see online data about users connected to system, which responsibility and form they are using and how long they are connected.
You can use this monitor from:
System Administrator responsibility -> security -> User -> Monitor.

- To inform users about unsuccessful logins to their account, you can set the “Sign-On:Notification” profile to Yes.

Display all Reports Module Wise

SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1

Track Current Apps Versions

1)select product_version,patch_level from fnd_product_installations
Get current version ana Patch level information.

2)select * FROM V$VERSION
Database Version infomation.

3)select * from v$instance
Instance details

4)select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;
Current XML Parser Version info.

5)select TEXT from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION'
Workflow version Number.

6)select home_url from icx_parameters
Oracle applications front end URL

7)SELECT VALUE FROM V$PARAMETER WHERE NAME=’USER_DUMP_DEST’
Get the Trace file location.

8) XML Publisher Vesion info.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.

Change IP Address of Oracle Application Server

1. Change the IP Address in the Server;

2. Verify the current ip address setup in the Oracle Applications environment. Connect as apps user into SQL*Plus and run:

select NODE_NAME, STATUS, NODE_MODE, NODE_ID,SERVER_ADDRESS, HOST, DOMAIN, WEBHOST, VIRTUAL_IP from fnd_nodes where node_name = upper('hostname');

3. Run the following command to remove the old ip address from the Oracle Applications tables:

perl $AD_TOP/bin/adgentns.pl appspass=apps contextfile=$APPL_TOP/admin/<SID>_hostname.xml -removeserver

replace <SID>_hostname.xml for the context file name under the $APPL_TOP/admin directory;

then connect to SQL*Plus as apps user and run:

begin
FND_NET_SERVICES.remove_server('<SID>', '<hostname>');
end;
/
commit;
/

replace <SID> by the SID of the environment and <hostname> by the hostname in the environment. Both must be entered in upper case.

4. Run autoconfig to populate the values using the new IP Address.

5. Confirm the ip address has been changed to the new value changed in the step 1:

select NODE_NAME, STATUS, NODE_MODE, NODE_ID,SERVER_ADDRESS, HOST, DOMAIN, WEBHOST, VIRTUAL_IP from fnd_nodes where node_name = upper('hostname');

Setup Password Security in Oracle Apps R12

Signon Password Failure Limit
The Signon Password Failure Limit profile option determines the maximum number of
login attempts before the user's account is disabled.
Users cannot see or update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess
The Signon Password Hard to Guess profile option sets rules for choosing passwords
to ensure that they will be "hard to guess." A password is considered hard-to-guess
if it follows these rules:
. The password contains at least one letter and at least one number.
. The password does not contain the username.
. The password does not contain repeating characters.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length
Signon Password Length sets the minimum length of an Applications signon password.
If no value is entered the minimum length defaults to 5.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse
This profile option specifies the number of days that a user must wait before being
allowed to reuse a password.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Signon Password Case
This profile option is not available from the beginning.
With 11i.ATG_PF.H RUP3 comes the system profile 'Password Case Option'
After 11i.ATG_PF.H.RUP4 this system profile option was renamed to 'Signon Password Case'.
There are two settings: 'Sensitive' and 'Insensitive'.
The default is 'Insensitive'.
Setting this profile option to 'Sensitive' will make the password case sensitive.
'Mixed' is no longer supported.

These profiles should only be set at Site level.
They can be set at other levels, such as User or Responsibility.

However, when logging in there is no User context, so if a User is prompted to
change their password at login, the profiles are only evaluated at site level.

Once logged in and resetting passwords using Preferences->Change Password,
or the Security ->User->Define form these other levels will have effect and will confuse the issue.

So these profiles should only be set at Site level, for consistent enforcement.

Enabling Enterprise Manager on the Oracle E-Business Suite Release 12

For the process to complete successfully, the database and database listener must be started.
It is not neccessary that any other Apps services are running for this process to complete.

Source the environment for the 10g ORACLE_HOME. With a Release 12 environment this would mean running the _.env script as generated by AutoConfig in the 10gR2 ORACLE_HOME.

Implement a password file and to overcome any ORA-01031 errors on the database.

It is also worth considering converting your Applications database to work with an SPFile
instead of an init.ora. An SPFile is approximately analogous to being a binary equivalent of the init.ora and allows for dynamic changes to many database parameters that previously would have required a database restart.

Next, as the owner of the 10gR2 ORACLE_HOME, run the following command to create the Enterprise Manager repository:-

$ emca -config dbcontrol db -repos create

When the above create command is run you will be prompted for the following:

STARTED EMCA at Oct 15, 2007 12:26:22 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the values appropriate to your environment:-

Database SID: VIS
Listener port number: 1521
Password for SYS user: change_on_install
Password for DBSNMP user: dbsnmp
Password for SYSMAN user: sysman
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings

Database ORACLE_HOME ................ /oracle/VIS/db/tech_st/10.2.0

Database hostname ................ xxxxxxx.xx.oracle.com
Listener port number ................ 1521
Database SID ................ VIS
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y

The repository should then start to be created. Typically this can take anywhere between 10 and 60 minutes depending on hardware and database configuration and size.

INFO: Creating the EM repository (this may take a while) ...

When the repository is created the following message should appear....

INFO: >>>>>> The Database Control URL is http://xxxxxxxxxx.xx.oracle.com:1158/em <<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 13, 2007 2:13:48 PM

Use the above Database Control URL to access the Enterprise Manager console through a browser interface.

Us the following command to drop an EM repository that has already been created:

$ emca -deconfig dbcontrol db -repos drop

To enable or disable browser access to dbconsole use the following command after sourcing
the environment for the 10g ORACLE_HOME. With a Release 12 environment this would
mean running the _.env script as generated by AutoConfig in the 10gR2 ORACLE_HOME.

$ emctl start dbconsole

To disable browser access use the following command:

$ emctl stop dbconsole

Get the current Patchset level of Oracle Applications products in R12

1) Login to OAM

(Responsibility "Oracle Applications Manager", menu "OAM Support Cart")

and Navigate: Support Cart -> Applications Signature -> Collect -> Check "Product Information" box -> Click on "View" (eyeglasses)

In R12

(Responsibility "System Administrator", Menu "Oracle Application Manager")
and Navigate : Applications Usage >> Products Installed


This will display the following patchset information :

Application Name
Current Patch Level
Product Version
Status (Installed, Shared Product, Inactive)

2) The patchset level information can be retrieved directly via sqlplus with the following diagnostic script : $AD_TOP/sql/adutconf.sql

3) Use the below query to pull out the details

SQL>SELECT fav.application_id,
fav.application_short_name, 
fav.application_name,
fav.basepath,
fav.creation_date,
fpi.status,
fpi.patch_level,
fpi.product_version,
fpi.TABLESPACE,
fpi.index_tablespace,
fpi.temporary_tablespace
FROM fnd_application_vl fav,
fnd_product_installations fpi
WHERE fav.application_id = fpi.application_id(+) 
ORDER BY creation_date DESC

Query to list out Responsibilities assigned to a Request Group

SELECT responsibility_name ,
  request_group_name        ,
  frg.description
   FROM fnd_request_groups frg,
  fnd_responsibility_vl frv
  WHERE frv.request_group_id = frg.request_group_id
  AND request_group_name LIKE '%'
--AND responsibility_name LIKE '%'
ORDER BY request_group_name,
responsibility_name

Query to list all Oracle Reports assigned to a Responsibility

SELECT fcpl.user_concurrent_program_name "REPORT NAME",
                fnrtl.responsibility_name, frg.request_group_name,
                fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
           FROM apps.fnd_request_groups frg,
                apps.fnd_request_group_units frgu,
                apps.fnd_concurrent_programs fcp,
                apps.fnd_concurrent_programs_tl fcpl,
                apps.fnd_executables fe,
                apps.fnd_responsibility fnr,
                apps.fnd_responsibility_tl fnrtl
          WHERE frg.application_id = frgu.application_id
            AND frg.request_group_id = frgu.request_group_id
            AND frg.request_group_id = fnr.request_group_id
            AND frg.application_id = fnr.application_id
            AND fnr.responsibility_id = fnrtl.responsibility_id
            AND frgu.request_unit_id = fcp.concurrent_program_id
            AND frgu.unit_application_id = fcp.application_id
            AND fcp.concurrent_program_id = fcpl.concurrent_program_id
            AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
            AND fcp.executable_id = fe.executable_id
            AND fe.application_id = fcp.executable_application_id
            AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
            -- Example Resp. Name : Oracle Inventory or put '%' to get the list of all the responsibilities
            AND fnrtl.LANGUAGE = 'US'
            AND fcpl.LANGUAGE = 'US';

Count Total Number of Users Connected to ORACLE Apps

If we want to determine how many users are connected to Oracle apps ,V$session does not give the true picture as mostly there are more than 1 oracle session for the same forms connection depending on how many forms the user has opened up.
Solution

1:- Can use this SQL statement to count concurrent_users in Oracle apps:

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

2:- In the Oracle Applications Manager, go to the site map, and select the "Applications Usage" option in the Activity region. There you will be able to report on many usage questions, such as:

Products Installed
Application Users Per Module Summary
Applications Usage Reports
Suppliers
Purchase Line Items Processed - Internet Supplier Portal
Purchase Line Items Processed - Purchasing Intelligence
Order Entry Lines Processed - Ordering Application
Purchase Line Items Processed - iProcurement
Expense Reports Processed - Internet Expense
Invoice Line Items Processed - Accounts Receivables
Note the Application Users per module summary is based on the number of users that have Active responsibilities for the Application module. I am not aware of there being any check on last usage date for the responsibility.

3:- Also if you are using responsibilities connected to a custom-application, which is standard Consulting practice in some countries, those users will be counted as users of the custom-application and not users of the actual Application-module for the screens they are accessing.

4:- Run the following queries:-
This will give the number of users on the system in the past 1 hour.
select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

This will give the number of users on the system in the past 1 day.
select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

This will show the activity in the last 15 minutes.
select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

5:- Check the Note:233871.1 which will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.

Surely this will affect the performance but how it affects is based on the number of users logged in and accessing the forms.

Factoring in System Overhead
===========================

In planning your organizations Sign On Audit implementation, you should consider the additional system overhead required to precisely User and Data Auditing monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.

Users and Responsibilites in Oracle Apps

Script to find all the responsibilities of a 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('SYSADMIN') -- <change it>
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY frt.responsibility_name;

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities

SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SYSADMIN' --- for all user or for a particular user
-- AND fur.responsibility_application_id = 1 -- to check users for particular responsibility
order by user_name

List Of Responsibilities Assigned To All Users

SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
FND_USER_RESP_GROUPS_ALL FURGA,
FND_USER FU,
FND_USER FUCB,
FND_USER FULUB,
FND_USER FULUL,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FRTL,
FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = 'US'
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;

Query to list Active Responsibilities of an Active User

SELECT fu.user_name,
       frv.responsibility_name,
       frv.responsibility_key,
       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND fu.user_name                     = '&user_name'
AND furgd.start_date                <= sysdate
AND NVL(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND NVL(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND NVL(frv.end_date, sysdate + 1)   > sysdate;