转载自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
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 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.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
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'
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
FND_DESCR_FLEX_COL_USAGE_VL FND_DFU
WHERE FND_DFV.TITLE = '物料' --如:物料
AND FND_DFU.DESCRIPTIVE_FLEXFIELD_NAME =
FND_DFV.DESCRIPTIVE_FLEXFIELD_NAME
采购订单配置和暂挂问题解决方法
---查询请购单的状态
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