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

1Oracle-ERP11i(OPM)—表结构、API接口笔记Author:Jarwang（王重东）CreateDate:July5th,2008UpdateDate:November20th,2010ControlNo:CurrentEdition:1.0声明：本文可以任意免费转载、复制、传播。但您务必保持其完整性！Ifyoucopythisdocument,yououghttokeepthedocumentcompletely.2DocumentControlModifyRecordDateAuthorVersionModifiedreferenceJuly5th,2008jarwang1.0ApprovedNamePositionsignatureDistributeNumNameLocation&Position12343IndexDocumentControl2ModifyRecord2Approved2Index3Preface6ApplicationDeveloper7ValueSet7DataTable7Lookups7DataTable7E-RDiagram7Frequently-UsedSQL8QueryLookup8QueryValueSet8ValuesSet8DataTable8E-RDiagram9OPMProcessExecution10Formula10DataTable10E-RDiagram10Recipe10DataTable10E-RDiagram11ProductionDocument11DataTable11E-RDiagram12Frequently-UsedSQL12Frequently-UsedAPI12CreateBatchesAPI13BatchescompletedAPI16OPMInventory17ItemMaster17DataTable17E-RDiagram17Organization18DataTable18E-RDiagram18InventoryQuantity19DataTable19Frequently-UsedSQL19QueryOPMOrganization19QueryLotStatus20Frequently-UsedAPI20OPMAdjustImmediateAPI20OPMMoveImmediateAPI22HumanResource25HREmployee25Frequently-UsedAPI25CreateHREmployeeAPI25CreateHREmployeeAPI--FULL27PurchaseOrder33Requisition334DataTable33E-RDiagram33PurchaseDocument33DataTable33E-RDiagram34Quotation34DataTable34Receiving35DataTable35E-RDiagram35PositionHierarchy35DataTable35E-RDiagram36Frequently-UsedSQL36POrelationITEM36QueryPO37QueryPOQuotation40Frequently-UsedAPI40CreatePOByInterface40OrderManagement41SalesOrders41DataTable41E-RDiagram41Deliver41DataTable41E-RDiagram42Frequently-UsedAPI42CreateSalesOrderAPI43Frequently-UsedSQL47QueryOrder47QueryProfitCenter50QueryMonthySaleAmount50Workflow52WorkflowUser&Role52DataTable52E-RDiagram53GeneralLegder55Journals55DataTable55E-RDiagram55Frequently-UsedSQL56QueryGL56OPMGLdrilldownPO58AccountReceivable59Customer59DataTable59E-RDiagram59Transaction60DataTable60E-RDiagram61Receipts61DataTable61E-RDiagram62Frequently-UsedSQL62QueryCustomer62QueryCustomerAddress63Frequently-UsedAPI63CreateARInvoicesAPI63CreateARReceiptsAPI63ApplyARReceiptsAPI65AccountsPayable675Supplier67DataTable67E-RDiagram67Invoices67DataTable67E-RDiagram68Payment68DataTable68E-RDiagram69Bank69DataTable69E-RDiagram69Frequently-UsedSQL70QueryVendorLiability70QueryUnpaidPrepayment70QueryUnAppliedPrepayment70Frequently-UsedAPI74CreateAPInvoiceInterfacetable74OPMCost75CostEvent75DataTable75E-RDiagram75CostDetail76DataTable76Frequently-UsedSQL76QueryCost77SubledgerQuery-PO78System83SystemAdministrator83Frequently-UsedAPI83CreateARInvoicesAPI83Frequently-UsedSQL83QueryRequest83OtherNotes856PrefaceOracleERP本身异常庞大，因此OracleERP的表结构也异常庞大。使用下述语句，可以得到OracleERP的表数量有1W多(此方法不精确，但也可以说明表的庞大)。selectcount(*)fromdba_tablesdtwheredt.tablespace_name='APPS_TS_TX_DATA'另外，EBS大多数表都没有PrimaryKey，ForeginKey，并且大多数表都会有100多列。熟悉EBS全部的表结构有点不太可能，事实上要画出OracleERP完整E-R图几乎不可能。对于常用模块的表结构还是得掌握的，否则不熟悉表结构，对OracleERP进行二次开发，就无异于难上加难。本文是基于OracleEBS11.5.10.2。参考文档：://www.oracle.com/technology/documentation/applications.html7ApplicationDeveloperValueSetDataTable值集表NO.TableNameDescriptionNote1APPS.FND_FLEX_VALUE_SETS值集题头表2APPS.FND_FLEX_VALUES值集行明细表视图：FND_FLEX_VALUES_VL3APPS.FND_FLEX_VALUES_TL值集多语言表LookupsDataTableEBS中所谓Lookups主要是指提供给LOV组件作为数据源。例如：AP发票的InvoiceType。主要涉及二张表：APPLSYS.FND_LOOKUP_TYPES和APPLSYS.FND_LOOKUP_VALUES，由于EBS的多语言，所以还有一张多语言表APPLSYS.FND_LOOKUP_TYPES_TL。另外Lookups定义时分为系统级、用户级、可扩展级。对于系统级Lookups是不允许修改的。NO.TableNameDescriptionNote1APPLSYS.FND_LOOKUP_TYPES查找代码题头表2APPLSYS.FND_LOOKUP_VALUES查找代码行明细表3APPLSYS.FND_LOOKUP_TYPES_TL查找代码多语言表E-RDiagramLookups关联E-R图8Frequently-UsedSQLQueryLookup根据类别查找Lookup的值selectFLV.LOOKUP_CODE,FLV.MEANING,FLV.DESCRIPTIONfromAPPLSYS.FND_LOOKUP_VALUESflvwhereflv.lookup_type='VENDORTYPE'--查找代码类别andlanguage=userenv('LANG');QueryValueSetValuesSetDataTableEBS值集定义表NO.TableNameDescriptionNote1APPLSYS.FND_FLEX_VALUE_SETS值集表2APPLSYS.FND_FLEX_VALUES_TL值表一般使用视图FND_FLEX_VALUES_VL3APPLSYS.FND_FLEX_VALUES值多语言表9E-RDiagram关联E-R图10OPMProcessExecutionFormulaDataTable配方关联表：NO.TableNameDescriptionNote1GMD.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-RDiagram配方关联E-R图RecipeDataTable处方关联表：NO.TableNameDescriptionNote1GMD.GMD_RECIPES_TL处方题头多语言表2GMD.GMD_RECIPES_B处方题头表3GMD.GMD_RECIPE_ROUTING_STEPS处方步骤数量表114GMD.GMD_RECIPE_PROCESS_LOSS处方组织表5GMD.GMD_RECIPE_VALIDITY_RULES处方有效性规则表不同工厂的处方生产规则。通过有效性规则来创建工单。6APPS.GMD_ROUTINGS_VL工艺路线一般使用此视图E-RDiagram处方关联E-R图ProductionDocumentDataTable1.生产工单创建的业务过程：n要加工什么？这涉及到车间任务的来源。）n怎样加工？（用到处方，包括配方和工艺路线）ü工艺路线：工艺路线定义了生产该产品所采用的方法或步骤。工艺路线由工序步骤组成。ü工序：工序是一条工艺路线的一道工序步骤。工序步骤由活动组成。ü活动：活动就是一道工序步骤中所做的各种动作活动。ü资源：资源指的是对应活动所使用的生产设备以及相关设备所耗用的各种费用构成（例如水、电、汽、人工等）。n怎样控制加工过程？（物料控制、资源控制、步骤控制）2.生产工单关联的表(常用)：NO.TableNameNote1GME.GME_BATCH_HEADER生产工单表2GME.GME_MATERIAL_DETAILS生产工单物料明细表。LINE_TYPE字段标识出配料(-1)、副产品(2)、产品(1)123GME.GME_BATCH_STEPS生产工单批步骤表4GME.GME_BATCH_STEP_ACTIVITIES生产工单批活动表5GME.GME_BATCH_STEP_RESOURCES生产工单批资源表6GME.GME_INVENTORY_TXNS_GTMP生产工单分配表此表只是临时表，真正库存信息是在gmi.ic_tran_pnd表中E-RDi