--查找运行请求时间,参数等(可以是某用户的,某个报表)
SELECT C.USER_NAME,
PAPF.FULL_NAME,
B.USER_CONCURRENT_PROGRAM_NAME,
A.REQUEST_DATE,
A.ARGUMENT_TEXT,
(A.ACTUAL_COMPLETION_DATE - A.ACTUAL_START_DATE) * 24 * 60 MINUTES,
A.ACTUAL_START_DATE,
A.ACTUAL_COMPLETION_DATE,
A.REQUEST_ID,
A.OUTFILE_NAME
FROM FND_CONCURRENT_REQUESTS A,
FND_CONCURRENT_PROGRAMS_VL B,
FND_USER C,
PER_ALL_PEOPLE_F PAPF
WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND A.REQUESTED_BY = C.USER_ID
AND C.USER_NAME = PAPF.EMPLOYEE_NUMBER(+)
AND A.ACTUAL_COMPLETION_DATE IS NOT NULL
AND B.USER_CONCURRENT_PROGRAM_NAME = 'CUX:采购合同执行情况表' -- like '%XXX%'
AND C.USER_NAME = 'HAND_YC' --登录系统的用的用户名
AND A.REQUEST_DATE <=
TO_DATE('2012-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
AND A.REQUEST_DATE >=
TO_DATE('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND A.REQUEST_ID > 2254198 ---为了提高速度,选一个合适时间点的ID
--查询功能所属的菜单
SELECT FFFV.FUNCTION_NAME, FM.MENU_NAME, FR.RESPONSIBILITY_KEY
FROM FND_FORM_FUNCTIONS_VL FFFV,
FND_MENU_ENTRIES_VL FMEV,
FND_MENUS FM,
FND_RESPONSIBILITY FR
WHERE FFFV.FUNCTION_ID = FMEV.FUNCTION_ID
AND FMEV.MENU_ID = FM.MENU_ID
AND FMEV.MENU_ID = FR.MENU_ID(+)
AND FFFV.FUNCTION_NAME LIKE '&function_name'
——查找在标准请求组里提交的报表所在的职责
SELECT A.RESPONSIBILITY_NAME,
B.USER_CONCURRENT_PROGRAM_NAME,
B.CONCURRENT_PROGRAM_NAME
FROM FND_RESPONSIBILITY_VL A,
FND_CONCURRENT_PROGRAMS_VL B,
FND_REQUEST_GROUP_UNITS C
WHERE A.APPLICATION_ID = C.APPLICATION_ID
AND A.REQUEST_GROUP_ID = C.REQUEST_GROUP_ID
AND B.APPLICATION_ID = C.UNIT_APPLICATION_ID
AND B.CONCURRENT_PROGRAM_ID = C.REQUEST_UNIT_ID
AND B.USER_CONCURRENT_PROGRAM_NAME LIKE 'CUX:交易方余额信息汇总表'
ORDER BY A.RESPONSIBILITY_NAME
--根据报表文件名称关键字查找报表的执行文件名称等信息
SELECT A.USER_CONCURRENT_PROGRAM_NAME,
A.CONCURRENT_PROGRAM_NAME,
A.OUTPUT_FILE_TYPE,
B.EXECUTION_FILE_NAME,
B.EXECUTABLE_NAME,
FND_L.MEANING,
B.USER_EXECUTABLE_NAME,
B.DESCRIPTION
FROM FND_CONCURRENT_PROGRAMS_VL A,
FND_EXECUTABLES_VL B,
FND_LOOKUPS FND_L
WHERE A.APPLICATION_ID = B.APPLICATION_ID
AND A.EXECUTABLE_ID = B.EXECUTABLE_ID
AND B.EXECUTION_METHOD_CODE = FND_L.LOOKUP_CODE(+)
AND FND_L.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
AND A.USER_CONCURRENT_PROGRAM_NAME LIKE 'CUX%'
---查询出所有独立值集
SELECT DISTINCT FFVS.FLEX_VALUE_SET_NAME "值集简称",
FFVS.DESCRIPTION "值集说明"
FROM FND_FLEX_VALUE_SETS FFVS,
FND_CONCURRENT_PROGRAMS_VL FCP,
FND_DESCR_FLEX_COL_USAGE_VL FDFCU
WHERE '$SRS$.' || FCP.CONCURRENT_PROGRAM_NAME =
FDFCU.DESCRIPTIVE_FLEXFIELD_NAME
AND FDFCU.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME LIKE 'CUX%' ----值集名
AND FFVS.VALIDATION_TYPE = 'I' --独立值集标识
ORDER BY FFVS.FLEX_VALUE_SET_NAME;