下载文档原格式
1接口表：a)OE_HEADERS_IFACE_ALL：此表为多组织表，用于将销售订单头插入开放接口。
该表存储来自于其他子系统需要导入OM模块的订单头信息，该表导入时必须输入的字段/条件：ORDER_SOURCE_ID : Order source id 可选ORIG_SYS_DOCUMENT_REF: Original system document reference 必须ORDER_SOURCE : Order source 可选OPERATION_CODE : Operation code 必须ORDER_TYPE_ID : Order type id 可选ORDER_TYPE : Order type 可选RETURN_REASON_CODE : Return reason code 仅用于订单退回SALESREP_ID : Salesrep idPRICE_LIST_ID : Price list id 用于已经booking的订单PRICE_LIST : Price list 用于已经booking的订单example:insert into oe_headers_iface_all(ORDER_SOURCE_ID,orig_sys_document_ref,ORG_ID,order_type_id,PRICE_LIST_ID,TRANSACTIONAL_CURR_CODE,SOLD_TO_ORG_ID,SHIP_TO_ORG_ID,created_by,creation_date,last_updated_by,last_update_date,operation_code)select ooha.order_source_id,ooha.orig_sys_document_ref,_id,ooha.order_type_id,ooha.price_list_id,ooha.TRANSACTIONAL_CURR_CODE,ooha.SOLD_TO_ORG_ID,ooha.SHIP_TO_ORG_ID,ooha.created_by,ooha.creation_date,fnd_er_id,sysdate,p_operation_codefrom oe_order_headers_all oohawhere order_number=p_order_number;b)OE_LINES_IFACE_ALL此表为多组织表，用于将销售订单行插入开放接口。
OracleEBS11i--DBA笔记Author:Jarwang（王重东）Create Date:February26,2009Update Date:Control No:Current Edition: 1.0声明：本文可以任意免费转载、复制、传播。
但您务必保持其完整性！If you copy this document,you ought to keep the document completely.Document ControlModify Record日期作者版本更改参考February26,2009jarwang 1.0Approved姓名职位签字Distribute编码姓名地点和位置1234IndexDocument Control2Modify Record2Approved2Index3Preface5Foundation Chapter6EBS Architecture6EBS Installation6EBS Startup6Startup&Shutdown6Startup Enterprise7 Maintenance Chapter8Change APPS pwd8EBS Clone8Apply EBS patch8Apply DB patch9Oracle DB Maitenance9RMAN Backup9ORA-0002010ORA-0981710Move tablespace datafile11Move system tablespace datafile11RMAN Backup12Oracle Application Maitenance12Purge Application logfiles and reports12 Upgrade Chapter14DB Upgrade10g14Preparation14Download Database10g14Install the Release10.2Oracle Software15Install Oracle Database10g Products from the10g Companion CD20Install the Latest Available Patch Set Release and Any Required Patches23Create nls/data/9idata directory23Shut down Applications server processes and database listener23Run the Pre-Upgrade Information Tool23Copy init<SID>.ora to new ORACLE_HOME26Modify new ORACLE_HOME init<SID>.ora26Additional database configuration27Upgrade the Datatabase27Install Oracle Data Mining and OLAP29Start the new database listener29Run adgrants.sql30Grant create procedure privilege on CTXSYS30Apply AutoConfig Patchset31Migrating to AutoConfig on the Database Tier33Migrating to AutoConfig on the Application Tier34Gather statistics for SYS schema35Re-create custom database links36Re-create grants and synonyms36Restart Application Server36Upgraded Troublesome37Config10g EM37Optimization Chapter40Application Optimization40Database Optimization40Move Controlfile40 SQL Optimization40 Query Tablespace Datafiles40 Other Notes41PrefaceOracle EBS DBA是一个很泛的概念，基本上DB与APP两者要负责。
Oracle Application 11i 二次开发经验总结开发端的环境设置在Oracle Application 11i中要增加客制的Form应用程序，开发时是基于Oracle Application 11i所带的一个模板“Template.frm”的Form原程序文档，其基本开发过程如下：1、从Oracle Application 11i应用服务器中，找到“Template.frm”的Form原程序文档，将其拷贝到开发端。
提示：如果你的开发端计算机硬盘空间足够，要求是有3.8G的空间（我想现在都不成问题了吧），那么请将包含整个源代码的目录一起复制到开发端计算机上。
我用来做试验的Oracle Application 11i的版本号是：11.5.7；安装路径在E盘上，所以后面的试验设置都将在这个路径下进行，您可以根据自己的安装路径进行相应的修改、设置；当然，如果你所用的版本比我的高，有遇到和我们现在所讲的版本中有不同的地方请也不要见怪，我相信在系统的应用上是大同小异；源代码的路径如下图（ERP－1）所示：（ERP－1）我将其复制我的开发端计算机上的具体路径和服务器上的一样，如图（ERP－2）所示：（ERP－2）当我们用Oracle Develop 6i的开发工具打开“Template.frm”这个开发模板时，会出现如下图（ERP －3）、（ERP－4）所示的对话框；当然，如果你是在服务器端开发时就不会有这样的提示，请不要见怪，看了以下的步骤就会明白。
（ERP－3）（ERP－4）系统告诉我们不能正确地读取到一些PL/SQL程序库文档，这是由于路径不正确引起的，要解决这样的问题我们还要进入第二的配置。
2、在开发端配置Form默认访问路径；请打开您的系统注册表，找到这样的路径：“HKEY_LOCAL_MACHINE\SOFTWARE\ORCLE\HOMEn\”，再找到“FORMS60_PATH”的项，修改并添加从服务器上复制过来的源代码所在的绝对路径为它的默认路径值。
Oracle Erp表接口表结构oracle 2009-03-05 20:47:19 阅读749 评论0 字号：大中小订阅下面是我在写erp接口时的积累：1.请购单：po_requisition_headers_all --请购单头po_requisition_headers_all --请购单行2.订单和一揽子协议：--erp标准接口表select * from Po_Headers_Interface where attribute8=327select * from Po_Lines_Interface--erp最终业务表select * from Po_Headers_All where segment1='2007074733'select * from Po_Lines_All where po_header_id=235600po.PO_DISTRIBUTIONS_ALL 分配行,就是订单头和订单行的唯一po.po_line_locations_all 发运行3.接收：erp接口表:Rcv_Headers_Interface/Rcv_Transactions_Interfaceerp业务表:po.rcv_shipment_headers/lines,po.rcv_transactions,接收会计分录表：Po.Rcv_Receiving_Sub_Ledger4.交货：erp接口表:Rcv_Transactions_Interfaceerp业务表:po.rcv_transactions交货后会写入库存：Mtl_Onhand_Quantities,apps.Mtl_Onhand_Sub_v,mtl_onhand_items_v5.发料：erp接口表:Mtl_Transactions_Interfaceerp业务表:WIP_REQUIREMENT_OPERATIONS,apps.Mtl_Onhand_Sub_v错误表：Mtl_Transactions_Interface[error_code,error_explanation]6.发票：发票标准接口表头体select * from ap.ap_invoices_interface where invoice_num like'01685085~5095%' --01685131~5138' --06279214/06279328' --GM0009'select * from ap.ap_invoice_lines_interface where invoice_id=26085最终发票头体ap.ap_invoices_all发票头ap.ap_invoice_distributions_all发票体发票拒绝错误信息表：select * from AP_INTERFACE_REJECTIONS创建会计分录表：Ap.Ap_Ae_Headers_AllAp.Ap_Ae_Lines_All7.付款：付款头,对应发票头select * from ap.ap_invoice_payments_all where invoice_id in(89991)付款行，对应发票行select * from ap.ap_payment_distributions_all gg where gg.invoice_payment_id =8.总帐gl.gl_interface总帐接收表ERP标准接口表select * from gl.gl_interface t where --t.accounting_date >= to_date('2006-01-01','YYYY-MM-DD') and user_je_source_name='GMCCBMS'ERP总帐最终业务表：GL_JE_HEADERSgl_je_lines获取从应付发票导入总帐的错误发票select * from ap.ap_invoices_all where invoice_num in(select distinct reference25 from gl.gl_interface where status='EC12' )会计科目表：Gl.Gl_Code_Combinations通过会计科目段找到对应的idSelect * From gl_code_combinations_kfv kWhere k.concatenated_segments = '3821.0.123203.0.0.0.0'9.需求更改：接口表:Wip_Job_Dtls_Interface错误表：wip_interface_errors_v10.工序移动：包括线圈工序移动、返回总装工序移动、返回接口表:WIP_MOVE_TXN_INTERFACE错误表：wip_txn_interface_errors[error_column,error_message]11.完工入库：包括线圈完工入库线圈完工返回总装完工入库总装完工返回库存转移接口表:MTL_TRANSACTIONS_INTERFACE错误表：Mtl_Transactions_Interface[error_code,error_explanation]基础表：po_vendors --供应商po_vendor_sites_all --供应商地址po_vendor_contacts --供应商联系人Mtl_System_Items_b --物料ap.ap_terms_lines --付款方式per_people_f --人员wip.wip_entities --工单Po.Po_Agents --采购员Apps.Fnd_User --用户apps.fnd_currencies_vl --币种_acct_periods_v --库存会计期apps.gl_period_statuses --PO会计期Gl_Period_Statuses --总帐会计期Bom_Bill_Of_Materials --bom清单。
suite of E-business applications1.应用技术(applications technology)2.客户关系管理（crm）3.财务(financials)4.供应链(supply chain)5.人力资源管理系统(hrms)6.生产制造(manufacturing)Stored in a database and file systemA file system containing—forms the allow interactive entry and updating of data.—reports that allow formatted documentation and display of data—concurrent programs that provide high-volumne , non-interactive update of data—programs and SQL scripts for maintaining the system—java that performs certain UI and business functionsAn oracle database containing1、data objects , such as—tables which are used to store customer data—indexes and sequences2、code objects , such as—PL/SQL stored procedures and triggers for performing database processing—Views , grants , synonyms and functionsOracle applications：1.CRM2.financials3.hrms4.manufacturing5.applications technologyApplications technology （layer）(应用层):A:application object B:applications DBA C:application utilities D:implementation wizard E:common modules F:alert G:workflowTechonology stack1.froms server2.reports server3.web server4.oracle 8iwhat is importance of the application technology layer?The applications technology layer provides common , basic functionality across all oracle applications product families.The oracle applications technology layer lies between the oracle tools and the oracle applications product specific modules .1.receivables2.general ledger3.fixed assets4.inventory应用产品依赖性Aol end user features—standard user interface—key flexfields and descriptive flexfields—shared flexfield value sets—standard report submission—applications online helpAol developer features—PL/SQL libraries—Custom library—GUI standards—Coding standards—Message dictionary —Standard report submission —Flexfield development—Apls—Functions securityAol features for administrators —manage and control security —set up new users—audit user activity—set user and system profiles —manager concurrent processing文件系统结构文件类型及扩展名环境变量文件<SID>_hostname.env 文件中的主要环境变量—applfenv:主要的变量文件的文件名。
1. 在oracle applications 11i 中如何快速获得上次查询的SQL 语句方法：我们不必使用SQL Trace 来获得，有一个简单的办法，即：帮助->诊断->检查->在"块"中输入"system"，在"字段"输入" last_query "，然后把鼠标移动到"值"，你就可以看到你所要的SQL 语句，简单吧！2. 如何更改Applications 中LOG 的存放位置方法：（以AIX 为例）在11i 中默认LOG 的存放位置是各个模块下log 目录，例如FND 模块log 的存放目录就是$FND_TOP/log/，可能有些用户想统一管理log，可以采取下面这种方法得到解决：更改applprod 用户（用于启动FormSever，ReportServer 和并发管理器等）的.profile，增加两行：APPLCSF=/u10/oracle/prodcomnexport APPLCSF然后重新以applprod 登录，重新启动FormSever，ReportServer 和并发管理器等（一般有个shell 文件，例如start.sh），至于为什么能起作用？原因很简单，Applications 首先查看APPLCSF 环境变量是否设置，一旦设置，所有的log 均存放在$APPLCSF/log；否则就存放在各个模块的log 目录3. 如何清除一些历史数据？方法：Applications 11i 的许多模块都提供清除相关历史数据，例如fnd 模块提供"清除过时的工作流运行时数据"的并发请求，你可以清除OM 模块的旧的工作流的历史数据（目前我们系统中每隔3 个月自动清除）；fnd 模块提供"汇总和清除并发请求统计"，你可以清除提交并发请求产生的log（目前我们系统每隔7 天自动清除）。
Oracle-ERP11i(OPM)—表结构、API接口笔记Author:Jarwang（王重东）Create Date:July5th,2008Update Date:November20th,2010Control No:Current Edition: 1.0声明：本文可以任意免费转载、复制、传播。
但您务必保持其完整性！If you copy this document,you ought to keep the document completely.Document ControlModify RecordDate Author Version Modified referenceJuly5th,2008jarwang 1.0ApprovedName Position signatureDistributeNum Name Location&Position1234IndexDocument Control2Modify Record2Approved2Index3Preface6Application Developer7Value Set7Data Table7Lookups7Data Table7E-R Diagram7Frequently-Used SQL8Query Lookup8Query ValueSet8Values Set8Data Table8E-R Diagram9 OPM Process Execution10Formula10Data Table10E-R Diagram10Recipe10Data Table10E-R Diagram11Production Document11Data Table11E-R Diagram12Frequently-Used SQL12Frequently-Used API12Create Batches API13Batches completedAPI16 OPM Inventory17Item Master17Data Table17E-R Diagram17Organization18Data Table18E-R Diagram18Inventory Quantity19Data Table19Frequently-Used SQL19Query OPM Organization19Query Lot Status20Frequently-Used API20OPM Adjust Immediate API20OPM Move Immediate API22 Human Resource25HR Employee25Frequently-Used API25Create HR Employee API25Create HR Employee API--FULL27 Purchase Order33Requisition33Data Table33 E-R Diagram33 Purchase Document33 Data Table33 E-R Diagram34 Quotation34 Data Table34 Receiving35 Data Table35 E-R Diagram35 Position Hierarchy35 Data Table35 E-R Diagram36 Frequently-Used SQL36 PO relation ITEM36 Query PO37 Query PO Quotation40 Frequently-Used API40 Create PO By Interface40 Order Management41 Sales Orders41 Data Table41 E-R Diagram41 Deliver41 Data Table41 E-R Diagram42 Frequently-Used API42 Create Sales Order API43 Frequently-Used SQL47 Query Order47 Query Profit Center50 Query Monthy Sale Amount50 Workflow52 Workflow User&Role52 Data Table52 E-R Diagram53 General Legder55 Journals55 Data Table55 E-R Diagram55 Frequently-Used SQL56 Query GL56 OPM GL drilldown PO58 Account Receivable59 Customer59 Data Table59 E-R Diagram59 Transaction60 Data Table60 E-R Diagram61 Receipts61 Data Table61 E-R Diagram62 Frequently-Used SQL62 Query Customer62 Query Customer Address63 Frequently-Used API63 Create AR Invoices API63 Create AR Receipts API63 Apply AR Receipts API65 Accounts Payable67Supplier67 Data Table67 E-R Diagram67 Invoices67 Data Table67 E-R Diagram68 Payment68 Data Table68 E-R Diagram69 Bank69 Data Table69 E-R Diagram69 Frequently-Used SQL70 Query Vendor Liability70 Query Unpaid Prepayment70 Query UnApplied Prepayment70 Frequently-Used API74 Create AP Invoice Interface table74 OPM Cost75 Cost Event75 Data Table75 E-R Diagram75 Cost Detail76 Data Table76 Frequently-Used SQL76 Query Cost77 Subledger Query-PO78 System83 System Administrator83 Frequently-Used API83 Create AR Invoices API83 Frequently-Used SQL83 Query Request83 Other Notes85PrefaceOracle ERP本身异常庞大，因此Oracle ERP的表结构也异常庞大。
使用下述语句，可以得到Oracle ERP的表数量有1W多(此方法不精确，但也可以说明表的庞大)。
select count(*)from dba_tables dt where dt.tablespace_name='APPS_TS_TX_DATA'另外，EBS大多数表都没有Primary Key，Foregin Key，并且大多数表都会有100多列。
熟悉EBS全部的表结构有点不太可能，事实上要画出Oracle ERP完整E-R图几乎不可能。
对于常用模块的表结构还是得掌握的，否则不熟悉表结构，对Oracle ERP进行二次开发，就无异于难上加难。
本文是基于Oracle EBS11.5.10.2。
参考文档：/pls/etrm/etrm_search.search/technology/documentation/applications.htmlApplication DeveloperValue SetData Table值集表NO.TableName Description Note1APPS.FND_FLEX_VALUE_SETS值集题头表2APPS.FND_FLEX_VALUES值集行明细表视图：FND_FLEX_VALUES_VL 3APPS.FND_FLEX_VALUES_TL值集多语言表LookupsData TableEBS中所谓Lookups主要是指提供给LOV组件作为数据源。
例如：AP发票的Invoice Type。
主要涉及二张表：APPLSYS.FND_LOOKUP_TYPES和APPLSYS.FND_LOOKUP_VALUES，由于EBS的多语言，所以还有一张多语言表APPLSYS.FND_LOOKUP_TYPES_TL。
另外Lookups定义时分为系统级、用户级、可扩展级。
对于系统级Lookups是不允许修改的。
NO.TableName Description Note1APPLSYS.FND_LOOKUP_TYPES查找代码题头表2APPLSYS.FND_LOOKUP_VALUES查找代码行明细表3APPLSYS.FND_LOOKUP_TYPES_TL查找代码多语言表E-R DiagramLookups关联E-R图Frequently-Used SQLQuery Lookup根据类别查找Lookup的值select FLV.LOOKUP_CODE,FLV.MEANING,FLV.DESCRIPTIONfrom APPLSYS.FND_LOOKUP_VALUES flvwhere flv.lookup_type='VENDOR TYPE'--查找代码类别and language=userenv('LANG');Query ValueSetValues SetData TableEBS值集定义表NO.TableName Description Note1APPLSYS.FND_FLEX_VALUE_SETS值集表2APPLSYS.FND_FLEX_VALUES_TL值表一般使用视图FND_FLEX_VALUES_VL 3APPLSYS.FND_FLEX_VALUES值多语言表E-R Diagram 关联E-R图OPM Process ExecutionFormulaData Table配方关联表：NO.TableName Description Note1GMD.FM_FORM_MST_B配方题头表Formula_NO与GMI.IC_ITEM_MST_B的ITEM_NO实现弱关联。
2GMD.FM_FORM_MST_TL配方题头多语言表3GMD.FM_MATL_DTL配方行明细LINE_TYPE字段标识出配料(-1)、副产品(2)、产品(1)E-R Diagram配方关联E-R图RecipeData Table处方关联表：NO.TableName Description Note1GMD.GMD_RECIPES_TL处方题头多语言表2GMD.GMD_RECIPES_B处方题头表3GMD.GMD_RECIPE_ROUTING_STEPS处方步骤数量表4GMD.GMD_RECIPE_PROCESS_LOSS处方组织表5GMD.GMD_RECIPE_VALIDITY_RULES处方有效性规则表不同工厂的处方生产规则。
