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

Useful SQL/Query - EBS Descriptive Flex Fields (DFF)

Here are a few SQLs to help working with Oracle EBS (E-Business Suite) Descriptive Flex Fields. You may have to make appropriate changes for your environment.

 

 

All custom DFFs - contexts, fields, values by module/application

 

SELECT APL.APPLICATION_ID, APL.APPLICATION_NAME,

 FDF.TITLE                             "DFF TITLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME",

 FUSG.ENABLED_FLAG   -- , APL.*

FROM FND_DESCR_FLEX_COL_USAGE_VL FUSG, FND_DESCRIPTIVE_FLEXS_VL FDF, FND_FLEX_VALUE_SETS FFV, FND_APPLICATION_VL APL

WHERE FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)

 -- AND FDF.TITLE = 'Employee Confirmation Date'  -- Replace this with Flex Field Title

 -- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'

 -- AND FUSG.ENABLED_FLAG = 'Y'

 AND FUSG.CREATED_BY >= 1000

 AND FDF.APPLICATION_ID = APL.APPLICATION_ID

 AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

 AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY APL.APPLICATION_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME, FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;

 

All DFF definitions in the system, includes lot of standard fields

This may be useful, if you are more comfortable to filter in Excel

 

SELECT FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FDF.TITLE                             "DFF TITLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME",

 FUSG.ENABLED_FLAG

FROM FND_DESCR_FLEX_COL_USAGE_VL   FUSG, FND_DESCRIPTIVE_FLEXS_VL      FDF, FND_FLEX_VALUE_SETS           FFV

WHERE FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)

-- AND FDF.TITLE = 'Employee Confirmation Date'  -- Replace this with Flex Field Title

-- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'

-- AND FUSG.ENABLED_FLAG = 'Y'

AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY FDF.APPLICATION_TABLE_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME, FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;

 

 

 

 

Query to find DFF information

 

SELECT FDF.TITLE                             "DFF TITLE",

 FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME"

FROM FND_DESCR_FLEX_COL_USAGE_VL   FUSG,

 FND_DESCRIPTIVE_FLEXS_VL      FDF,

 FND_FLEX_VALUE_SETS           FFV

WHERE FDF.TITLE = 'Employee Confirmation Date'         -- Replace this with Flex Field Title

AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US' 

AND FUSG.ENABLED_FLAG = 'Y'

AND FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID

AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY

 FUSG.DESCRIPTIVE_FLEXFIELD_NAME,

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE,

 FUSG.COLUMN_SEQ_NUM;