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