二次開發-CSDN博客

(1)./* 根据报表文件名称关键字查找报表的执行文件名称等信息*/
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 'C%杂项出入库报表%'

(2)./*查找在标准请求组里提交的报表所在的职责*/
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 '%杂项出入库报表%'

(3)./*查找在菜单里提交的报表所在职责*/
SELECT A.RESPONSIBILITY_NAME,B.PROMPT,F.USER_CONCURRENT_PROGRAM_NAME
FROM FND_RESPONSIBILITY_VL A,
FND_MENU_ENTRIES_VL B,
FND_FORM_FUNCTIONS_VL C,
FND_REQUEST_GROUPS D,
FND_REQUEST_GROUP_UNITS E,
FND_CONCURRENT_PROGRAMS_VL F
WHERE A.MENU_ID=B.MENU_ID
AND B.FUNCTION_ID=C.FUNCTION_ID
AND C.PARAMETERS LIKE '%'||D.REQUEST_GROUP_CODE||'%'
AND D.APPLICATION_ID=E.APPLICATION_ID
AND D.REQUEST_GROUP_ID=E.REQUEST_GROUP_ID
AND E.UNIT_APPLICATION_ID=F.APPLICATION_ID
AND E.REQUEST_UNIT_ID=F.CONCURRENT_PROGRAM_ID
AND F.USER_CONCURRENT_PROGRAM_NAME LIKE '%杂项出入库报表%'

(4).
/* 根据窗口名称查找关键字弹性域用到的表，列等信息*/--
SELECT C.ID_FLEX_NAME,A.ID_FLEX_STRUCTURE_NAME,B.FORM_LEFT_PROMPT,C.APPLICATION_TABLE_NAME,B.APPLICATION_COLUMN_NAME,B.FLEX_VALUE_SET_ID
FROM FND_ID_FLEX_STRUCTURES_VL A,
FND_ID_FLEX_SEGMENTS_VL B,
FND_ID_FLEXS C
WHERE A.ID_FLEX_STRUCTURE_NAME='帐户别名' --用你自己要查的代替，就是Form窗口的标题
AND A.APPLICATION_ID=B.APPLICATION_ID
AND A.ID_FLEX_CODE=B.ID_FLEX_CODE
AND A.ID_FLEX_NUM=B.ID_FLEX_NUM
AND A.APPLICATION_ID=C.APPLICATION_ID
AND A.ID_FLEX_CODE=C.ID_FLEX_CODE
--根据上面FLEX_VALUE_SET_ID查弹性域的数据
SELECT *
FROM FND_FLEX_VALUES_VL T
WHERE T.FLEX_VALUE_SET_ID=1005982 -- FLEX_VALUE_SET_ID
--具体某一数据
SELECT *
FROM FND_FLEX_VALUES_VL T
WHERE T.FLEX_VALUE_SET_ID=1005982
AND T.FLEX_VALUE='720611'

(5)
/*根据描述性弹性域的标题查找描述性弹性域表和列*/--

SELECT FND_DFV.TITLE,
FND_DFV.DESCRIPTIVE_FLEXFIELD_NAME ,
FND_DFV.APPLICATION_TABLE_NAME ,
FND_DFU.APPLICATION_COLUMN_NAME,
FND_DFU.FORM_LEFT_PROMPT,
FND_DFU.FORM_ABOVE_PROMPT
FROM FND_DESCRIPTIVE_FLEXS_VL FND_DFV,
FND_DESCR_FLEX_COL_USAGE_VL FND_DFU
WHERE FND_DFV.TITLE='物料' --如：物料
AND FND_DFU.DESCRIPTIVE_FLEXFIELD_NAME=FND_DFV.DESCRIPTIVE_FLEXFIELD_NAME

（6）。查找运行请求时间，参数等（可以是某用户的，某个报表）
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='你的程序名称' --- like '%XXX%'
and c.user_name=' 你要找的用户的'
and a.request_date<=to_date('2005-03-01 23:59:59','yyyy-mm-dd hh24:mi:ss')
and a.request_date>=to_date('2005-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND a.request_id> 2254198---为了提高速度，选一个合适时间点的ID

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7960369/viewspace-237868/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7960369/viewspace-237868/