ERP开发中常用的一些工具代码
程序员文章站
2022-07-03 23:23:12
...
--查询功能所属的菜单
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 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
;
/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
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 1 = 1
AND FND_DFV.TITLE LIKE '%&title%' --如:物料'%附加题头信息%'
AND FND_DFU.DESCRIPTIVE_FLEXFIELD_NAME =
FND_DFV.DESCRIPTIVE_FLEXFIELD_NAME
;
/* 根据窗口名称查找关键字弹性域用到的表,列等信息*/ --
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 LIKE '%&帐户别名%' --用你自己要查的代替,就是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'
;
--查找在菜单里提交的报表所在职责
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 '%杂项出入库报表%';
--查找在标准请求组里提交的报表所在的职责
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 '%杂项出入库报表%';
--根据报表文件名称关键字查找报表的执行文件名称等信息
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%杂项出入库报表%'