Oracle-ERP11i表结构、API接口笔记

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处方有效性规则表不同工厂的处方生产规则。