ERP开发过程中常用的SQL查询脚本

--查找运行请求时间，参数等（可以是某用户的，某个报表）

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;