erp常用sql

最新推荐文章于 2024-03-11 11:03:40 发布

zzq8851

最新推荐文章于 2024-03-11 11:03:40 发布

阅读量670

点赞数

分类专栏： Oracle-ErpSql资源文章标签： flex date structure 报表 application table

Oracle-ErpSql资源专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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