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”