Java KeyStore file and Java signing

After JRE (Java Runtime Environment) 1.8 patches are applied to EBS R12 by following Doc 393931.1, the next step is Java signing as described in Doc ID 1591073.1.

1. Created keystore (JKS) file $APPL_TOP/admin/adkeystore.dat

- First, backup file adkeystore.dat and verify the content of adsign.txt is correct.
- Then run below line to create a new JKS file:

$ cd $APPL_TOP/admin
$ adjkey -initialize -keysize 2048
                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA
                             AD Java Key Generation
                                    Version 12.0.0
NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.

Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
Enter the APPS username: apps
Enter the APPS password:

Successfully created javaVersionFile.
adjkey will now create a signing entity for you.

Enter the Name of your Company (used for both CN and
ORGANIZATION NAME) [CN/ORGANIZATION NAME] : siteName Inc.
Enter the department or group that will use the certificate [ORGANIZATION UNIT] : siteName Inc.
Enter the full name of the city where your organization's
head office is located [LOCALITY] :  New York
Enter the full name of the State, Province or County where
your organization's head office is located [STATE] :  NY
Enter the two-letter ISO abbreviation for your country
(for example, US for the United States) [COUNTRY] : US
Enter keystore password:  Re-enter new password: Enter key password for ... ...
adjkey is complete.

You do not need to enter a new password for the keystore, as it will take the default. Use below code to see the passwords:

SQL> set serveroutput on
SQL> declare
spass varchar2(30);
kpass varchar2(30);
begin
ad_jar.get_jripasswords(spass, kpass);
dbms_output.put_line(spass);
dbms_output.put_line(kpass);
end;
/  
puneet       <== default password for keystore
myxuan     <== default password for the key

2. Create CSR (Certificate Signing Request) file

$ export JRI_DATA_LOC=$APPL_TOP/admin
$ cd  $APPL_TOP/admin

$ keytool -sigalg SHA1withRSA -certreq -keystore adkeystore.dat -file adkeystore.csr -alias EBSDEV_devserver1d
Enter keystore password:
Enter key password for <EBSDEV_devserver1d>

$ openssl req -in adkeystore.csr -text -noout | grep "Signature Algorithm"
    Signature Algorithm: sha1WithRSAEncryption
$ openssl req -in adkeystore.csr -noout -text       <== verify a CSR

Note: " $ adjkey -certreq -file adkeystore.csr " also creates a .csr file.

3. Send adkeystore.csr file to Certificate Authority of the company to sign. They shall send 3 .cer files back. They are the keys.

Use below line to see the content of cert file, e.g.
$ keytool -printcert -v -file RootCA.cer

4. Import keys (i.e. cert files) to JKS on the same server where CRS file was generated

$ echo $OA_JRE_TOP
/u05/app/EBSDEV/apps/tech_st/10.1.3/appsutil/jdk/jre
$ export SEC_PROP_LOC=$OA_JRE_TOP/lib/security   (<= No need for me as cacerts is not used)

$ keytool -import -alias ebsrootca -file RootCA.cer -trustcacerts -v -keystore adkeystore.dat  (??)
Enter keystore password:     <== puneet
Certificate already exists in system-wide CA keystore under alias <digicertassuredidrootca>
Do you still want to add it to your own keystore? [no]:  yes
Certificate was added to keystore
[Storing adkeystore.dat]

$ keytool -import -alias interCA -file siteName.cer -trustcacerts -keystore adkeystore.dat
Enter keystore password:
Owner: CN=siteName Inc., O=siteName Inc., L=New York, ST=NY, C=US
Issuer: CN=DigiCert SHA2 Assured ID Code Signing CA, OU=www.digicert.com, O=DigiCert Inc, C=US
Serial number: c123456f86c0a6a4bbe83e69e0c1ff5
Valid from: Mon Sep 21 20:00:00 EDT 2015 until: Tue Sep 26 08:00:00 EDT 2017
Certificate fingerprints:
         MD5:  34:3B:35:0F:6A:43:22:B3:6B:63:82:F3:B3:02:0F:74
         SHA1: 6C:36:7D:54:9A:F6:52:1C:18:45:2B:6E:FB:D4:EF:75:EE:3E:81:E8
         Signature algorithm name: SHA256withRSA
         Version: 3
Extensions:
... ... ...
Trust this certificate? [no]:  yes
Certificate was added to keystore

$  keytool -import -alias EBSDEV -file codeSigningCA.cer -trustcacerts -keystore adkeystore.dat
Enter keystore password:
Certificate was added to keystore

5. Verify the contents of JKS file

$ keytool -list -keystore ewallet.jks -storepass keystorePWD
$ keytool -list -v -keystore adkeystore.dat    <= to the keystore detail

Below JKS file with 3 entries works well for me:

$ keytool -list -keystore adkeystore.dat
Enter keystore password:

Keystore type: JKS
Keystore provider: SUN

Your keystore contains 3 entries

ebsdev_devserver1d, Nov 4, 2015, PrivateKeyEntry,
Certificate fingerprint (MD5): 34:3B:35:0F:6A:43:22:B3:6B:63:82:F3:B3:02:0F:74
interca, Sep 22, 2015, trustedCertEntry,
Certificate fingerprint (MD5): 34:3B:35:0F:6A:43:22:B3:6B:63:82:F3:B3:02:0F:74
ebsdev, Oct 27, 2015, trustedCertEntry,
Certificate fingerprint (MD5): B7:55:37:6C:3D:2A:CE:BB:A1:88:49:D6:04:36:1B:D6

6. Run the Java signing on each EBS web/Forms node

$ stop all apps services
$ adadmin
    ==> 1 Select Generate Applications Files
    ==> 4 Generate Product JAR Files    Yes.  (Do force the regeneration of all JAR files.)

  ... ... ... 
  You can safely ignore any warnings about missing metadata entries in JAR and Zip files
  ......
  Removed appsborg2.cmd.
  Successfully created new appsborg2.zip.
  Copied appsborg2.zip from AU_TOP to  AF_JLIB.

Note1: if for some reason, the keystore password saved in the database does not match the password in the JKS file, adadmin will fail. You will have to change the JKS password to make them match.
Note2: if adadmin fails or .jar files do not get signed, there is something wrong with JKS file adkeystore.dat. You may delete it and re-do it after the issue is identified. Some warnings on .zip files can be ignored during adadmin run.
Note3: if adadmin fails with error "adogjf() Unable to generate jar files under JAVA_TOP", there could be a problem with the content of $APPL_TOP/admin/adsign.txt.

7. Verify .jar files are newly signed. If it works, .jar files not only get new timestamp but also get signed with 3 certificates. For example, check one file:

$ jarsigner -verify -verbose -certs $AD_TOP/java/jar/adxlib.jar
... ... ... ...
 X.509, CN=siteName Inc., O=siteName Inc., L=New York, ST=NY, C=US
 [certificate is valid from 12/21/15 8:00 PM to 12/26/17 8:00 AM]
 X.509, CN=DigiCert SHA2 Assured ID Code Signing CA,OU=www.digicert.com,O=DigiCert Inc,C=US
 [certificate is valid from 11/20/13 8:00 AM to 11/20/28 8:00 AM]
 X.509, CN=DigiCert Assured ID Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
 [certificate is valid from 12/9/07 7:00 PM to 12/9/32 7:00 PM]

Note: In my server, 3 date ranges match with those in 3 .cer files.

8.  start apps services and launch the Forms
Now, on a client machine with JRE 1.8 installed, R12 Forms shall launch smoothly (without placing the URL in the exception list of Security tab in Java console).

If the JRE version (i.e. 1.8.0_51) on the server does not match the JRE version (i.e. 1.8.0_66) on users' machine, It will popup a confirmation before EBS Forms show up.

9. If JKS file adkeystore.dat worked on one server, it can be used in all other servers of the company after the alias is changed to the new instance info, such as from Dev to QA:

$ keytool -changealias -alias ebsdev_devserver1d -destalias EBSQA_qaserver2q -keystore adkeystore.dat
$ keytool -changealias -alias ebsdev -destalias EBSQA -keystore adkeystore.dat

10. How to change keystore password and key password

$ adjkey -storepasswd
                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA
                             AD Java Key Generation
                                 Version 12.0.0
NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
Enter the APPS username: apps
Enter the APPS password:

Successfully created javaVersionFile.
alias name used is EBSDEV_devserver1d

Enter the new keystore password:        <== testit1
Enter keystore password:  New keystore password: Re-enter new keystore password:
keytool -storepasswd -keystore $APPL_TOP/admin/adkeystore.dat
The above Java program completed successfully.

$ adjkey -keypasswd
                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA
                             AD Java Key Generation
                                 Version 12.0.0
NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
Enter the APPS username: apps
Enter the APPS password:

Successfully created javaVersionFile.
alias name used is EBSDEV_devserver1d

Enter the new key password:     <== myxuan2
... ... ...
keytool -keypasswd -keystore $APPL_TOP/admin/adkeystore.dat -alias EBSDEV_devserver1d
The above Java program completed successfully.

Notes: " adjkey -storepasswd" and "adjkey -keypasswd" will changes the passwords in both database and file adkeystore.dat. But, if passwords in the database and in file adkeystore.dat do not match, it will give error " keytool error: java.io.IOException: Keystore was tampered with, or password was incorrect ".  In this case, use keytool to change the passwords in file adkeystore.dat to make them match the ones in the database as the first step.

What below will do?

SQL> exec ad_jar.DEL_JRIPASSWORDS;

SQL> exec ad_jar.PUT_JRIPASSWORDS('storePWD',' keyPWD');

11. How to delete a key from keystore
$ keytool -delete -alias mykey -keystore adkeystore.dat

NOTES:
- Keytool reference:
http://docs.oracle.com/javase/6/docs/technotes/tools/solaris/keytool.html
- For Oracle Fusion Middleware 11.1.1.1.0 and later, use ORAPKI to manage Wallet. See Doc ID 1226654.1 - How to Create a Wallet via ORAPKI in FMW 11g.

APList - Payables Invoice Data Collection Test

When users open a SR with Oracle Support for data issue, they frequently ask for APList report. Really, it is a standard diagnostic test in R12 Payables (SQLAP). It gets the name maybe because its back-end xml file is called APListXml.xml. I copy steps from Doc ID 732163.1 here to skip logging into Oracle Support website each time when need it:

1. Log onto Oracle E-Business Suite
2. Connect to responsibility Application Diagnostics
3. Select the Diagnose menu option
4. Click button Select Application and select Application "Payables"
5. Scroll down to group "Invoice"
6. Select test name "Invoice Data", then click on EXECUTE on the bottom
7. Input Parameters (* required)
   • Responsibility Id (LOV) *    <== grant a super user resp. to yourself first if you do not see one on the LOV
   • Search Criteria (LOV)  
   • Supplier Name (LOV)  
   • Invoice Id (LOV) *  
   • Include GL Tables (Yes or No) (LOV)  
   • Include Related Data (Yes or No) (LOV)  
   • Max Rows Displayed

R12 concurrent manager troubleshooting

Concurrent manages may not work properly due to different reasons.  To check if any concurrent jobs are running, run script afcmrrq.sql at $FND_TOP/sql. See Doc ID 2089560.6 (How To Tell If Concurrent Managers For A Particular SID Are Running) for other scripts.

1. After instance refresh or clone, concurrent managers fail to start.
When old (or source) server info is still saved in the database tables, concurrent managers get confused and will not start properly, even after below two lines to clean source info before running adconfig on database server:
$ sqlplus apps/passwd
SQL> @cmclean.sql         ( <= only before R12.2 )
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
SQL> commit;

SQL> select * from fnd_nodes;   (shall have 0 row. But one row after DBA runs adconfig script)
SQL> select unique(node_name) from fnd_concurrent_queues;
SQL> select * from fnd_concurrent_processes;
SQL> select * from fnd_conflicts_domain;

I saw message lines in log files under $APPLCSF/log:
CONC-SM TNS FAIL
Routine AFPEIM encountered an error while starting concurrent manager FNDSCH with library $APPL_TOP/fnd/12.0.0/bin/FNDSCH.
Check that your system has enough resources to start a concurrent manager process. Contact your system ad : 06-DEC-2015 09:58:42

The best fix is to apply patch 18539575 to fix a bug in R12.1 (See Doc ID 1646026.1). After this patch, concurrent services start normally in my refreshed instances.

One time, when the CM failed to start, I see message in the log file: 
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library $APPL_TOP/fnd/12.0.0/bin/FNDLIBR.
Check that your system has enough resources to start a concurrent manager process. Contact your sys : 30-DEC-2015 15:08:42
Starting Internal Concurrent Manager Concurrent Manager : 30-DEC-2015 15:08:42
CONC-SM TNS FAIL
 : ICM failed to start for target node1Name.  Review ICM log files for additional information.
                     Process monitor session ended : 30-DEC-2015 15:08:42

I realized that node1Name is the wrong node. So, I ran sql to update the data (with caution!):

SQL> select unique(node_name) from fnd_concurrent_queues;
NODE_NAME
------------------------------
node1Name
node2Name

SQL> select count(*) from fnd_concurrent_queues;
  COUNT(*)
----------------
        45

SQL> create table fnd_concurrent_queues_BK_jan as select * from fnd_concurrent_queues;
SQL> update fnd_concurrent_queues set NODE_NAME='node2Name'
where NODE_NAME='node1Name';

3 rows updated.

SQL> select unique(node_name) from fnd_concurrent_queues;
NODE_NAME
------------------------------
node2Name
SQL> commit;

After the table was updated, all concurrent mangers started on server node2Name.

2. When all concurrent manager processes were not fully shutdown or not properly shutdown in some situation (e.g. database connections were interrupted), CM services may fail on re-start. The number of processes in "Actual" and "Target" may not match with message " System Hold, Fix Manager before resetting counters ". In this case, first try shall be to use OAM to re-start individuals. For example (in Oracle Applications Manager version 2.3.1):

Site Map => Generic Services
On radio button select Output Post Processor (or, Conflict Resolution Manager ) => View Detail => Start (or verify) in the dropdown on the top => Go
(click on Next10) Generic Service Component Container => Workflow Agent Listener Service => Start in the dropdown on the top => Go

Site Map => Request Processing Manager
On radio button select Standard Manager => View Status tab on the upper => Start => Ok. Then click on Service Instances on the top to return to manager list.

3. When try to open a Request log or output file in GUI, it gives error:

An error occurred while attempting to establish an Applications File Server connection with the
node FNDFS_NODE2NAME. There may be a network configuration problem, or the TNS listener
on node FNDFS_NODE2NAME may not be running. Please contact your system administrator.

Here, Node2NAME is the concurrent server. After you make sure the listener (tnslsnr) is started, the first try is to ping the tnsname on Node1 (web/forms server): $ tnsping FNDFS_NODE2NAME
If you can not ping it, the real problem could be in $TNS_ADMIN/tnsnames.ora file on Node1. In one case, "domain.com" was not attached to Node2NAME in entries in tnsnames.ora file on Node1 server. After I re-ran autoconfig on all nodes, the problem got fixed.

Another place to check, make sure Profile options " RRA:% " have nothing strange.

If it works, below line shall list in "Concurrent => Manager => Administrator":
NAME                                   Node      Actual    Target   Status
Service Manager: Node1    Node1     1            0          {blank}

For Service Manager, if the Status shows "Target node/queue unavailable", clicking on "Restart" or OAM tries may not help. 

UPDATE in Sept 2020: Recently, seems the R12.1 clone script on RHEL7 did catch all info during the clone (maybe due to database connection error at the begining). After a clone completion and an adautocfg.sh run on all nodes, the tnsnames.ora file on CM node missed some entries. After 2nd run of adautocfg.sh on all nodes, some entries of tnsnames.ora on CM node missed "domain.com". tnsnames.ora file on CM node matches the original tnsnames.ora prior to clone until 3rd run of adautocfg.sh completed. 

4. Sometimes you are in a hurry to stop CM services but the OS processes keep running. You may see in the GUI form the "Actual" is non-zero while the "Target" is zero, which means there are still some requests are running. If you click "Terminate" the concurrent manager on the GUI, it will NOT kill its OS process. So this will not really speed up. The best way is to Find what are the running requests and then cancel the requests.

5. One time, Output Post Processor did not start. I checked file $APPLCSF/log/FNDOPPxxxxx.txt and saw error
Exception in static block of jtf.cache.CacheManager. Stack trace is: oracle.apps.jtf.base.resources.FrameworkException:
IAS Cache initialization failed. The Distributed Caching System failed to initialize on port: 12351. The list of hosts in the distributed caching system is: 157.121.49.41 157.121.53.42 157.121.53.43 . The port 12351 should be free on each host running the JVMs.

That means port 12351 is in use. After this port became free, Output Post Processor started.

6. One way to check if CM files on file system are good or not, run below report for a test. If all work, it will generate and save Active Users report for you even when all EBS services are shutdown at OS level.

$INST_TOP/ora/10.1.2/bin/appsrwrun.sh userid=apps/appsPWD mode=character report=$FND_TOP/reports/US/FNDSCURS.rdf \
batch=yes destype=file desname=./areport.out desformat=$FND_TOP/reports/HPL pagesize=132x66 traceopts=trace_all tracefile=areport.trc tracemode=trace_replace 

7. Below error may indicate that some node name is not registered in FND_NODES table. autoconfig may not run on all nodes after a cleaning.

List of errors encountered:
.............................................................................
_ 1 _
Concurrent Manager cannot find error description for CONC-System Node
Name not Registered
Contact your support representative.
.............................................................................

List of errors encountered:
.............................................................................
_ 1 _
Routine AFPCAL received failure code while parsing or running your
concurrent program CPMGR
Review your concurrent request log file for more detailed information.
Make sure you are passing arguments in the correct format.

8. If you do not want concurrent managers to run on a node at all, use two parameters in $CONTEXT_FILE to turn off them:

<oa_service_group_status oa_var="s_batch_status">disabled</oa_service_group_status><oa_service_group_status oa_var="s_other_service_group_status">disabled</oa_service_group_status>

I noticed once below error in a manager log file, while Concurrent processing works fine. I believe the error showed up after CM services got started on a wrong node by wrong values in $CONTEXT_FILE when the node got bounced from a crash. Oracle Support said it is a database issue. But I did not do anything on tables and the error went away by itself after above two values were set to "disabled" in $CONTEXT_FILE on that node.

Routine &ROUTINE has attempted to start the internal concurrent manager. The ICM is already running. Contact you system administrator for further assistance.afpdlrq received an unsuccessful result from PL/SQL procedure or function FND_DCP.Request_Session_Lock.
Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.
Possible DBMS_LOCK.Request result ORACLE error 1036 in tag_db_session

Cause: tag_db_session failed due to ORA-01036: illegal variable name/number.

The SQL statement being executed at the time of the error was: ... and was executed from the file &ERRFILE.Call to establish_icm failed
The Internal Concurrent Manager has encountered an error.

Review concurrent manager log file for more detailed information. : 26-JUL-2015 10:52:55 -
Shutting down Internal Concurrent Manager : 26-JUL-2015 10:52:55

List of errors encountered:
.............................................................................
_ 1 _
Routine AFPCSQ encountered an ORACLE error. .
Review your error messages for the cause of the error. (=<POINTER>)
.............................................................................

List of errors encountered:
.............................................................................
_ 1 _
Routine AFPCAL received failure code while parsing or running your
concurrent program CPMGR
Review your concurrent request log file for more detailed information.
Make sure you are passing arguments in the correct format.
.............................................................................
The EBSXXXX_0726@EBSXXXX internal concurrent manager has terminated with status 1 - giving up.

Place all pending concurrent requests on hold

During a long maintenance of multiple tasks, apps services may be brought down and up for two or three times. You may want to hold all scheduled jobs from execution until all tasks are completed.

1. Create a temp table to hold all request IDs
SQL> create table apps.FND_CONC_REQ_S_V_onhold as
select decode(phase_code,
              'P','Pending',
                  'Running') Phase_code,
       decode(status_code, 
              'R', 'Normal',
              'W', 'Paused',
              'Q', 'Standby',
               'I', 'Scheduled') Status_code,
     decode(hold_flag,'Y','Yes','No') Hold_flag,
     request_id,
     substr(requestor,1,10) usern_name,
     to_char(requested_start_date,'MM/DD/YYYY HH24:MI:SS') req_start_date,
     substr(program,1,50) program
from FND_CONC_REQ_SUMMARY_V
where phase_code in ('P')  -- in Pending
and status_code != 'W'      -- not in Paused
and hold_flag != 'Y'            -- not on hold
and to_date(requested_start_date,'DD-MON-YY')
between to_date(sysdate,'DD-MON-YY')
       and to_date(sysdate+2,'DD-MON-YY')
order by phase_code desc, requested_start_date, request_id
;

2. Update table  fnd_concurrent_requests to hold them
SQL> UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
and status_code in ('Q','I')  -- Standby or Scheduled
and hold_flag = 'N'
and request_id in (
select request_id from apps.FND_CONC_REQ_S_V_onhold);

3. Check/confirm any running requests before shutting down concurrent managers, if you like.
SQL> SELECT decode(phase_code,
              'P','Pending',
                  'Running') Phase_code,
       decode(status_code, 
              'R', 'Normal',
              'W', 'Paused',
              'Q', 'Standby',
              'I', 'Scheduled') Status_code,
     decode(hold_flag,'Y','Yes','No') Hold_flag,
     request_id,
     substr(requestor,1,10) usern_name,
     to_char(requested_start_date,'MM/DD/YYYY HH24:MI:SS') req_start_date,
     to_char(actual_start_date,'MM/DD/YYYY HH24:MI:SS') actual_start_date,
     substr(program,1,50) program
  FROM fnd_conc_req_summary_v
  WHERE phase_code in ('R', 'P')  order by program;

4. After maintenance, put on-hold requests back to scheduler
SQL> UPDATE fnd_concurrent_requests
SET hold_flag = 'N'
WHERE phase_code = 'P'
and status_code in ('Q','I')  -- Standby or Scheduled
and hold_flag = 'Y'
and request_id in (
select request_id from apps.FND_CONC_REQ_S_V_onhold);

SQL> drop table apps.FND_CONC_REQ_S_V_onhold;  -- optional

UPDATE in 2022: In R12.2, when used below line to hold concurrent jobs for a period of time for database upgrade, and after upgrade logged onto GUI forms to cancel the HOLD, some concurrent jobs got "No Manager" status.

SQL> update  apps.fnd_concurrent_requests 

set hold_flag = 'Y' 

where status_code in ('Q','I') and hold_flag = 'N';

The problem is column EDITION_NAME of table fnd_concurrent_requests was populated with invalid data. The fix is to find the current edition and run an update to the column.  

SQL> select edition_name from all_editions;

EDITION_NAME

-----------------------

ORA$BASE

SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

SQL> update fnd_concurrent_requests

set edition_name='ORA$BASE'

where request_id in (228633x, 228633x, 228632x, 228632x, 228624x, 228624x)

;

Concurrent Processing: What to Do When Concurrent Requests Are Not Processing and Have Inactive No Manager Status? (Doc ID 1311526.1)

Disable responsibilities to restrict EBS site access

During business events such as month-end close, only a small group of users are allowed to access EBS website. This can not be accomplished by placing it in maintenance mode. But we can disable most responsibilities to restrict and manage users' access without changing users' accounts.

-- disable responsibilities. Assume GL_SuperUser, AR_SuperUser are two special responsibilities
SQL> update FND_RESPONSIBILITY   
set end_date = sysdate   
where application_id = 30003   
and RESPONSIBILITY_ID in (select responsibility_id   
from FND_RESPONSIBILITY_TL   
where application_id = 30003   
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))   
;   
Commit;

-- After maintenance event, run sql to enable the responsibilities (without bouncing Apps services)
SQL> update FND_RESPONSIBILITY
set end_date = null
where application_id = 30003   
and RESPONSIBILITY_ID in (select responsibility_id   
from FND_RESPONSIBILITY_TL   
where application_id = 30003   
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))   
;   
Commit;

When a responsibility was created, it would be associated with an Application. Below query will find how many responsibilities were created for each application.
SQ> select a.application_id, b.application_short_name, count(*)
from FND_RESPONSIBILITY a , fnd_application b
where a.application_id = b.application_id
group by a.application_id, application_short_name
order by a.application_id;

application_id application_short_name count(*)
---------------  ----------------------------  ---------
0            FND               24
1            SYSADMIN    3
99          OAM              1
101        SQLGL           27
140        OFA               7
... ... ...
9001      MTH               2
9004      INL                 1
30003    PUGL             20     <== custom stuff
30004    PUAR             3
30005    PUFND           4

Additionally, below queries can answer questions:

1) the list of responsibilities for a user
2) the list of responsibilities for an application

SQL> SELECT user_name, frt.RESPONSIBILITY_NAME, furg.end_date, fr.application_id
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
AND fu.user_name = 'EBSuserName'
-- AND fr.application_id = 30003
ORDER BY 1,2;

How many users use an application with each responsibility?
SQL> SELECT fr.application_id, frt.RESPONSIBILITY_NAME, count(*)
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
group by fr.application_id, frt.RESPONSIBILITY_NAME
ORDER BY 1,2;