erp常用sql

 

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 = '你的程序名称' --- 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


/* 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 '%物料%'  --报表名   
   and  A.RESPONSIBILITY_NAME like 'ML%'
   order by A.RESPONSIBILITY_NAME


/* 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 '%物料%'


/* 根据窗口名称查找关键字弹性域用到的表，列等信息*/ --
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 = 1009677 -- FLEX_VALUE_SET_ID
        --具体某一数据
          SELECT *
                  FROM FND_FLEX_VALUES_VL T
                 WHERE T.FLEX_VALUE_SET_ID = 1009677
                   AND T.FLEX_VALUE = '720611'


/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
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
----查找运行请求时间，参数等（可以是某用户的，某个报表）
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


/* 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 '%物料%'  --报表名   
   and  A.RESPONSIBILITY_NAME like 'ML%'
   order by A.RESPONSIBILITY_NAME


/* 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 '%物料%'


/* 根据窗口名称查找关键字弹性域用到的表，列等信息*/ --
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 = 1009677 -- FLEX_VALUE_SET_ID
        --具体某一数据
          SELECT *
                  FROM FND_FLEX_VALUES_VL T
                 WHERE T.FLEX_VALUE_SET_ID = 1009677
                   AND T.FLEX_VALUE = '720611'


/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
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