ERP 11g常用表名
Oracle Erp维护(包含常用表名)
--fnd
select * from fnd_application
select * from fnd_application_tl where application_id=101
select * from fnd_application_vl where application_id = 101
----值集
select * from fnd_flex_value_sets
select * from fnd_flex_values
select * from fnd_flex_values_vl
----弹性域
select * from fnd_id_flexs
select * from fnd_id_flex_structures where id_flex_code='GL#'
select * from fnd_id_flex_segments where id_flex_code='GL#' and id_flex_num=50671
select * from fnd_profile_options_vl
select * from fnd_concurrent_programs 程序表
select * from fnd_concurrent_requests 请求表
select * from fnd_concurrent_processes 进程表
--inv
select * from org_organization_definitions 库存组织
select * from mtl_parameters 组织参数
select * from mtl_system_items_b where inventory_item_id = 171 and organization_id=204 物料表
select * from mtl_secondary_inventories 子库存
select * from mtl_item_locations 货位
select * from mtl_lot_numbers 批次
select * from mtl_onhand_quantities 现有量表
select * from mtl_serial_numbers 序列
select * from mtl_material_transactions 物料事务记录
select * from mtl_transaction_accounts 会计分录
select * from mtl_transaction_types 事务类型
select * from mtl_txn_source_types 事务来源类型
select * from mfg_lookups ml where ml.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
--po
select * from po_requisition_headers_all 请求头
select * from po_requisition_lines_all 请求行
select * from po_headers_all 订单头
select * from po_lines_all 订单行
select * from po_line_locations_all
select * from po_distributions_all 分配
select * from po_releases_all 发送
select * from rcv_shipment_headers 采购接收头
select * from rcv_shipment_lines 采购接收行
select * from rcv_transactions 接收事务处理
select * from po_agents
select * from po_vendors 订单
select * from po_vendor_sites_all
--oe
select * from ra_customers 客户
select * from ra_addresses_all 地址
select * from ra_site_uses_all 用户
select * from oe_order_headers_all 销售头
select * from oe_order_lines_all 销售行
select * from wsh_new_deliveries 发送
select * from wsh_delivery_details
select * from wsh_delivery_assignments
--gl
select * from gl_sets_of_books 总帐
select * from gl_code_combinations gcc where gcc.summary_flag='Y' 科目组合
select * from gl_balances 科目余额
select * from gl_je_batches 凭证批
select * from gl_je_headers 凭证头
select * from gl_je_lines 凭证行
select * from gl_je_categories 凭证分类
selec
t * from gl_je_sources 凭证来源
select * from gl_summary_templates 科目汇总模板
select * from gl_account_hierarchies 科目汇总模板层次
--ar
select * from ar_batches_all 事务处理批
select * from ra_customer_trx_all 发票头
select * from ra_customer_trx_lines_all 发票行
select * from ra_cust_trx_line_gl_dist_all 发票分配
select * from ar_cash_receipts_all 收款
select * from ar_receivable_applications_all 核销
select * from ar_payment_schedules_all 发票调整
select * from ar_adjustments_all 会计分录
select * from ar_distributions_all 付款计划
--ap
select * from ap_invoices_all 发票头
select * from ap_invoice_distributions_all 发票行
select * from ap_payment_schedules_all 付款计划
select * from ap_check_stocks_all 单据
select * from ap_checks_all 付款
select * from ap_bank_branches 银行
select * from ap_bank_accounts_all 银行帐号
select * from ap_invoice_payments_all 核销
erp常用sql
----查找运行请求时间，参数等（可以是某用户的，某个报表）
SELECT https://www.sodocs.net/doc/ad6878392.html,ER_NAME,
papf.full_name,
https://www.sodocs.net/doc/ad6878392.html,ER_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 = https://www.sodocs.net/doc/ad6878392.html,ER_ID
and https://www.sodocs.net/doc/ad6878392.html,er_name = papf.employee_number(+)
AND A.ACTUAL_COMPLETION_DATE IS NOT NULL
and https://www.sodocs.net/doc/ad6878392.html,ER_CONCURRENT_PROGRAM_NAME = '你的程序名称' --- like '%XXX%'
and https://www.sodocs.net/doc/ad6878392.html,er_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,https://www.sodocs.net/doc/ad6878392.html,ER_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 https://www.sodocs.net/doc/ad6878392.html,ER_CONCURRENT_PROGRAM_NAME LIKE '%物料%'
/* 3 查找在菜单里提交的报表所在职责*/
SELECT A.RESPONSIBILITY_NAME, B.PROMPT, https://www.sodocs.net/doc/ad6878392.html,ER_CONCURRENT_PROGRAM_NAME
FROM FND_RESPONSIBILITY_VL A,
FND_MENU_ENTRIES_VL B,
FND_FORM_FUNCTIO
NS_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 https://www.sodocs.net/doc/ad6878392.html,ER_CONCURRENT_PROGRAM_NAME LIKE '%物料%' --报表名
and A.RESPONSIBILITY_NAME like 'ML%'
order by A.RESPONSIBILITY_NAME
/* 1 根据报表文件名称关键字查找报表的执行文件名称等信息*/
SELECT https://www.sodocs.net/doc/ad6878392.html,ER_CONCURRENT_PROGRAM_NAME,
A.CONCURRENT_PROGRAM_NAME,
A.OUTPUT_FILE_TYPE,
B.EXECUTION_FILE_NAME,
B.EXECUTABLE_NAME,
FND_L.MEANING,
https://www.sodocs.net/doc/ad6878392.html,ER_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 https://www.sodocs.net/doc/ad6878392.html,ER_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 https://www.sodocs.net/doc/ad6878392.html,NGUAGE = userenv('LANG')
For Update