Oracle EBS – Workflow Background SQLs

 

Business Need:

Some random useful SQLs/Queries related to Oracle Applications (EBS) concurrent programs, schedules, workflow background programs, requisition import process, etc.

 

Queries:

All Concurrent Jobs ran more than 10 times during previous day

-- all concurrent job executions during a single day, more than 10
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-2.1 AND SYSDATE-1.1
--AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
HAVING COUNT(*) > 9
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Get name of above Concurrent Program ID

SELECT *
FROM APPS.FND_CONCURRENT_PROGRAMS_VL CP
--WHERE USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';          -- use name from above SQL
WHERE CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';
 

 

Get more details on frequent program – Eg: Workflow Background

-- qry sql - notes - check concurrent program running prod wfbg
-- WFBG & ReqImport Only, last 2.4 hrs ~around 72 should be good
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.104 AND SYSDATE+.004
AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Requests by a specific user

-- Programs ran by <USER> during past 15 minutes
SELECT CR.REQUEST_ID, CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, TO_CHAR(CR.REQUEST_DATE, 'Mon/dd hh24miss') REQUEST_DATE, CR.REQUESTED_BY
  , CR.PHASE_CODE, DECODE(CR.PHASE_CODE, 'C', 'Completed', 'P', 'Pending', 'zOthers') AS PHASE_NAME
  , CR.STATUS_CODE, DECODE(CR.STATUS_CODE, 'C', 'Normal', 'I', 'Scheduled', 'zOthers') AS STATUS_NAME
  , CR.LOGFILE_NAME, CR.OUTFILE_NAME, CR.ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = 1151              -- use user id of the given user
ORDER BY CR.REQUEST_DATE DESC;
 

All Requests during last 24 hours

-- Conc Requests ran during past 24 hours
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Similar to above

SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
 AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;

  

Keywords:

Oracle EBS, R12, R12.2.8, Workflow, Background, Concurrent Programs, Concurrent Jobs, Executions, WFBG, FND, Query, SQL, SQLPLUS