ERP开发中常用的一些工具代码

--查询功能所属的菜单

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%杂项出入库报表%'