欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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