Oracle Erp维护汇总贴

来源：互联网发布：好看的网络言情小说编辑：程序博客网时间：2024/06/06 01:59

转载自http://bbs.21manager.com/forum.php?mod=viewthread&tid=144693&extra=page%3D%7Bprevpage%7D&page=2

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

采购订单配置和暂挂问题解决方法

订单暂挂问题sql解决:

---查询请购单的状态

select

from

PO_REQUISITION_HEADERS_ALL por

where

por.requisition_header_id=63578

---修改请购单状态为未提交审批

update

po_requisition_headers_all porh

set

porh.authorization_status='INCOMPLETE'

where

porh.requisition_header_id=63578

---查询采购单状态

select

from

po_headers_all aa

where

aa.po_header_id in (20430,20431,20306)

---修改采购单状态为未审批

update

po_headers_all aa

set

aa.wf_item_type=null,

aa.wf_item_key=null,

aa.approved_flag=null,

aa.authorization_status=null

where

aa.po_header_id in (20430,20431)

值集维护

------------得到值集数据
    -------------通过值集的名称得到值集的ID
    ---值集表： fnd_flex_value_sets
    ---值集ID： flex_value_set_id
    ---值集名称：flex_value_set_name
     Select flex_value_set_id    From apps.fnd_flex_value_sets     Where flex_value_set_name='ML_INV_物料大类'

    ---通过值集ID得到值集列表
    ---值集列表：FND_FLEX_VALUES_VL
    ---列表ID：FLEX_VALUE
    ---列表名称：Description
    ---是否启用：ENABLED_FLAG   Y是，N否
    ---层次关系父：SUMMARY_FLAG   Y是，N否
    ---有效期始：START_DATE_ACTIVE
    ---有效期止：END_DATE_ACTIVE
    --排序：attribute50
     --俗称说明：attribute48
     Select A.FLEX_VALUE    flex_no
          , A.Description   flex_dsc
          ,attribute50,attribute48
          From apps.FND_FLEX_VALUES_VL a Where a.FLEX_VALUE_SET_ID=1009627 And a.ENABLED_FLAG='Y'
          For Update
          ---调整排序
          Select flex_value,attribute50 From apps.FND_FLEX_VALUES a Where a.flex_value_set_id=1009627
--Order By a.flex_value
For Update
----值集代码修改
Select * From APPS.FND_FLEX_VALUES_TL T Where
T.FLEX_VALUE_ID=59940 and T.LANGUAGE = userenv('LANG')
For Update