Sample SQLs - Oracle Applications Concurrent programs

Here are some commonly used SQLs to figure out various details related to concurrent program executions.

This is good reference to beginners who are working with Oracle Applications. This might be very basic and silly, if you are well experienced ... Please skip :)

1. Concurrent programs from a specific user, that was running at a given instance of time: (substitute time appropriately)
SELECT REQUEST_ID, REQUEST_DATE, REQUESTED_BY,
   PHASE_CODE, STATUS_CODE, PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
   ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS
WHERE REQUESTED_BY = 3995 -- user id
AND TO_DATE('12/12/2012 06:16:24', 'MM/DD/YYYY HH24:MI:SS') BETWEEN ACTUAL_START_DATE AND ACTUAL_COMPLETION_DATE


2. Concurrent programs that were completed successfully during the past week:
SELECT REQUEST_ID, REQUEST_DATE, REQUESTED_BY,
   PHASE_CODE, STATUS_CODE, PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
   ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS
WHERE ACTUAL_COMPLETION_DATE > SYSDATE - 7  -- past week
AND PHASE_CODE = 'C'    -- completed normal
AND STATUS_CODE = 'C'   -- completed

3. Concurrent programs that were ended in error during the past week:
SELECT REQUEST_ID, REQUEST_DATE, REQUESTED_BY,
   PHASE_CODE, STATUS_CODE, PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
   ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS
WHERE ACTUAL_COMPLETION_DATE > SYSDATE - 7  -- past week
AND PHASE_CODE = 'C'    -- completed
AND STATUS_CODE = 'E'   -- Error

4. Currently running concurrent programs:
SELECT REQUEST_ID, REQUEST_DATE, REQUESTED_BY,
   PHASE_CODE, STATUS_CODE, DECODE(STATUS_CODE, 'R', 'Running', 'W', 'Waiting', STATUS_CODE) AS STATUS_DESCRIPTION,
   PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
   ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS
WHERE ACTUAL_COMPLETION_DATE > SYSDATE - 1  -- past week
AND PHASE_CODE = 'R'    -- running



One more tip ... In any place, the REQUESTED_BY (or any place, where the queries display User Id) can be substituted with the following SQL Snippet, the user name will be displayed:

“REQUESTED_BY”
(Replace with)
“(SELECT USER_NAME FROM APPS.FND_USER WHERE USER_ID = REQUESTED_BY) AS USER_NAME”

 

Clear Cache through Functional Administrator - New R12 Responsibility

Clear Cache through Functional Administrator - New R12 Responsibility

Oracle R12 (Release 12) comes many new features. One simple yet very useful addition a new responsibility named Functional Administrator.

Even though the responsibility comes with many new features, one interesting thing to me is a tool to clear Web Server Cache. This helps to refresh the application functionality after changing a Java Page or System Profile without having to Clear Cache through server and Bounce Apache.

Steps to Access this:

Responsibility: Functional Administrator
Navigation Path: Home -> Core Services -> Caching Framework

Click on the link Global Configuration on the left side menu options. Here is a screenshot of the page:
 
Click on the buttons "Clear All Cache" and "Clear All Statistics".

Click on Apply Button.

Logout, close all browsers, login again and retest the change.


This is a standard functionality and no customizations are required to achieve this. However, in some rare cases, I faced issues where the Clear Cache button does not clear it completely. In this scenario, we need to clear cache through Application Server and bounce Apache.

Useful SQLs from FND_USER table

Just thought of sharing a few commonly used SQLs from FND_USER table. These will work with all versions of Oracle Applications (11i and R12).

If you are new to Oracle Applications, this will come very handy for you. If you are an experienced Oracle EBusiness Suite professional, you might find this too silly and you may skip this :)

Get details of a user by USER_ID or USER_NAME:

SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
  US.CREATION_DATE, US.CREATED_BY, US.LAST_UPDATE_DATE,
  US.LAST_UPDATED_BY, US.LAST_UPDATE_LOGIN, US.START_DATE,
  US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
  US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.USER_ID = 13936
-- WHERE USER_NAME = 'ATHOMAS'



List of users, who logged into the system during the past 30 minutes: (As we don’t have any other reliable place to track session length when user is in HTML session, this also can be used as the active users. Users who open forms can be tracked even better by a query below).

SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
  US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
  US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.LAST_LOGON_DATE > SYSDATE-0.02


Logins of a single user during the past 7 days: (Can be used to track unauthorized activity, updates, etc. This is linked with LAST_UPDATE_LOGIN of WHO columns)

SELECT LOGIN_ID, USER_ID, START_TIME, END_TIME, PID AS PROCESS_ID, SPID AS SUB_PROCESS, TERMINAL_ID, LOGIN_NAME, SESSION_NUMBER, SERIAL# AS SERIAL, LOGIN_TYPE
FROM APPS.FND_LOGINS FLG
WHERE START_TIME > SYSDATE - 7
AND USER_ID = 13936

Users who use FORM-based sessions: (Users who use only HTML-based windows will be missed in this query. Also the users who opened FORMS without any active form open. The FORMS home navigator window won’t be picked by this query)

SELECT SID AS SESSION_ID, SERIAL# AS SERIAL, MACHINE AS COMPUTER_NAME,
  CLIENT_IDENTIFIER AS CLIENT_USER_ID, ACTION,
  MODULE AS FORMS_MODULE, TO_CHAR (LOGON_TIME, 'dd/mm/yyyy HH24:mi:ss') LOGON_TIME, STATUS
FROM   SYS.GV_$SESSION SESS
WHERE  LOGON_TIME > SYSDATE - 0.3
-- AND MACHINE = 'NRBVLTEBS01' -- change machine name for instance (based on forms/application server)
-- AND CLIENT_IDENTIFIER = 'ATHOMAS' -- enable this, if a specific user is to be tracked
AND MODULE LIKE 'e:%' AND USERNAME = 'APPS' AND PROGRAM like 'frm%' -- string to identify the FORMS sessions


Did I miss anything? Feel free to add them in the COMMENTS section below ... It would be a great help for the EBiz community :)