1、oracle ebs常用数据表（Oracle EBS 常用数据表）Source: http:/ inventoryTwo meanings of organization:1. operating units, A/B/C branch, A, following A1, A2 and other factories, the theme is for independent accounting of the organizationORG, ORG_ID;2. inventory organization, such as manufacturers warehouse, such as
2、A1, A2 and other factoriesOrganization_id;HR_ORGANIZATION_UNITSOrg_organization_definitionsMtl_subinventory_ inventory organization unitMTL_PARAMETERS - inventory organization parameters (not using ID, directly using name)MTL_SYSTEM_ITEMS_b - material information (ibid., stock organization name)MTL_
3、SECONDARY_INVENTORIES - sub inventory organizations -MTL_ITEM_LOCATTIONS - space - SUBINVENTROY_CODEMtl_Material_Transactions - (inventory) material listCost mtl_transaction_accountsTransaction_cost is the cost of things;ACTUAL_COST is the actual cost calculated through a cost algorithm, the princip
4、al measurement unitExisting quantitySummary history (plus and minus totals)Mtl_Material_TransactionsMTL_ONHAND_QUANTITIES the existing scale, organization / inventory / storage / item summary may according to picking FIFO statistics, if you set the “no negative inventory“, this does not appear to be
5、 negativePORequisition head tablePo_Requisition_Headers_allRow tablePo_Requisition_lines_allPurchase orderPO_HEADER_ALLPO_LINES_ALLProcurement receipt - return / Inter Organizational transfer / normal condition requires the use of this moduleRCV_TRANSACTIONS1. received 100 units of goods into the “q
6、uality inspection“ section2. accept / reject3. inventory / returnThere are three different states! For example, receive 100, 80 accept storage, and 20 return, then there are 80 accepted transactions, /20 returnsSelect, TRANSACTION_TYPE, DESTINATION_TYPE_CODE, from, RCV_TRANSACTIONSYou can see the fo
7、llowing stages:A1.RECEIVE - RECEIVINGA2.ACCEPT - RECEIVINGA3.DELIERY INVETORY (affecting existing inventory)If you follow the normal mode, you will end up generating MTL_MATERIAL_TRANSACTIONSSales orderOE_ORDER_headers_allSOLD_FROM_ORG_IDSOLD_TO_ORG_ID is the client layerSHIP_FROM_ORG_IDSHIP_TO_ORG_
8、ID is the customer receiving layerINVOICE_TO_ORG_ID is a single layer for customersDELIVER_TO_ORG_IDClient structureCustomer RA_customersCustomer Address Ra_AddressesAddress goods arrive at site RA_SITE_USES_ALLAddress invoice arrives at siteOE_ORDER_LINEs_allGL credentialsGl_je_batchesCertificate d
9、ate: DEFAULT_EFFECTIVE_DATEAccounting period: DEFAULT_PERIOD_NAMEOriginal currency certificates, credits, credits, credits: RUNNING_TOTAL_DR/CR, such as US dollarsCollection of currency certificates, credits and credits: RUNNING_TOTAL_ACCOUNTED_DR/CRGl_je_headers journal header informationLot number
10、: JE_BATCH_IDAccounting period: PERIOD_NAMECurrency: CURRENCY_CODEExchange rate type: CURRENCY_CONVERSION_TYPEExchange rate date: CURRENCY_CONVERSION_DATEBooks: SET_OF_BOOKS_ID GL_SETS_OF_BOOKSDocument type: JE_CATEGORY reference, GL_JE_SOURCESCertificate source: JE_SOURCEGl_je_lines journal body in
11、formationCODE_COMBINATION_ID subject combination numberGL_BALANCES general ledger balancePERIOD_NET_DR/CR net worthBEGIN_BALANCE_DR/CR initial volumeGL_JE_BATCHES journal batch information (same year)GL_ARCHIVE_BATCHES journal filingGL_JE_HEADERS journal header informationGL_JE_LINES journal details
12、Among them, GL_JE_BATCHES and GL_JE_HEADERS are related by JE_BATCH_ID, and are one to many relationsAmong them, GL_JE_HEADERS and GL_JE_LINES are related by JE_HEADER_ID, and are one to many relationsGL_SETS_OF_BOOKS accounting information, each account has one line of recordsGL_PERIODS accounting
13、period informationGL_CODE_COMBINATIONS code combination informationGL_BALANCE general ledger balance sheetGL_DAILY_RATES daily rateGL_TRANSLATION_RATES_V monthly exchange rate - the average exchange rate during the periodGL_BUDEGETS general ledger budget informationThe GL_BUDGET_TYPES budget type ta
14、ble supports only one budget type, so there is only one recordGL_BUDGET_VERSIONS Budget Version information,通过 budget _ version _ id与 gl _ budegets对应gl _ interface 总账接口表gl _ budget _ assignment _ ranges 预算分配范围gl _ budget _ assignments 预算帐户信息gl _ budget _ period _ ranges 预算历期信息gl _ budget _ interface
15、 预算导入接口表gl _ budget _ frozen _ ranges 预算冻结信息gl _ budget _ entities 预算部门信息ar应收发票ra _ customer _ wood _ allcustomer _ wood _ id 发票编号bill _ two _ site _ use _ id 客户收单方编号primary _ sales _ id销售员reference是 oracle提供的外部编号输入框, 但是由于版本问题和长度 ( = 30), 不建议用户使用, 如果要使用外部编号, 请使用说明性弹性域ra _ customer _ wood _ lines _ a
16、llline _ id 行号inventory _ item _ id 可以为空, 比如非物料的服务, 只在description中出现 / 税行descriptionquantity _ invoice 开票数量line _ type 行类型 (一般 / 税)extend _ price 本行金额注意: 税行是隐藏行, 所以至少会有两行收款情况scar _ cash _ receipts _ all (还包含了非收款信息)cash receipt _ _ id 内部 codereceipt _ number 收款号receipt _ date 收款日期amount 总额receipt _ t
17、ype 现金 / 杂项 cash / risefunctional _ amount 本位币计量金额ui上为 receipts核销关系不是一一对应, 也不是一次核销 100%, ui上右下方的application 按钮scar _ receivable _ applications _ allapplied _ customer _ wood _ id 发票编号applied _ customer _ wood _ line _ id 发票行编号status app表示核销 / unapp 表示未核销amount _ applied 匹配金额注意: 红冲收款报表时间跨月的问题; 必须联查 s
18、car _ cash _ receipts _ all和 scar _ cash _ receipt _ history _ allap应付帐款 (是我方人员按照供应商提供的纸张发票信息录入) ui 上的 invoiceap _ invoices _ all实际付款 paymentap _ cheques _ all核销关系 同 ar, 右下方的 payment 按钮ap _ invoice _ payments _ all客户余额表, 情况比较复杂: 比如两个用户合并, 应收应付差额, 预付款资产信息 fa _ the obligation to ensure addition ality名
19、称编号分类数量资产类别fa _ categories资产帐簿fa _ book _ controls 和会计帐簿有什么关系?fa _ booksui中的 inquirymothed是折旧方法 (直线法 / 产量法)fa _ distribution _ history分配 assignment, 给什么部门使用多少location _ id 部门 联查 fa _ locations折旧信息 (分摊方法)fa _ deprn _ retailperiod _ counter 折旧期间编号折旧事务 (新增、重建、转移、报废)fa _ transaction _ headersselect find
20、 _ profile.value (org _ id ) from dualselect * from mr _ operating _ units hou where hou.organization _ id = 204- findselect * from find _ applicationselect * from find _ application _ tl where application _ id = 101select * from find _ application _ vl where application _ id = 101- - - - 值集select *
21、 from find _ flex _ value _ setsselect * from find _ flex _ valuesselect * from find _ flex _ values _ vl- - - - 弹性域select * from find _ id _ flexsselect * from find _ id _ flex _ structures where id _ flex _ code = gl # select * from find _ id _ flex _ segment where id _ flex _ code = gl # and id _
22、 flex _ num = 50671select * from find _ profile _ options _ vlselect * from find _ concurrent _ programme 程序表select * from find _ concurrent _ requests 请求表select * from find _ concurrent _ processes 进程表- invselect * from org _ organization _ definitions 库存组织select * from mtl _ parameter 组织参数select *
23、 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_m
24、aterial_transactions物料事务记录SELECT * FROM mtl_transaction_accounts会计分录SELECT * FROM mtl_transaction_types事务类型SELECT * FROM mtl_txn_source_types事务来源类型SELECT * FROM mfg_lookups毫升 ml.lookup_type =“mtl_transaction_action”宝SELECT * FROM po_requisition_headers_all请求头SELECT * FROM po_requisition_lines_all请求行
25、SELECT * FROM po_headers_all订单头SELECT * FROM po_lines_all订单行SELECT * FROM po_line_locations_allSELECT * 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_agentsSEL
26、ECT * FROM po_vendors订单SELECT * FROM po_vendor_sites_allOESELECT * 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_detailsSELECT * F
27、ROM wsh_delivery_assignmentsGLSELECT * FROM gl_sets_of_books总帐SELECT * FROM gl_code_combinations GCC在 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凭证分类SELECT * FROM gl_je_sou
28、rces凭证来源SELECT * FROM gl_summary_templates科目汇总模板SELECT * FROM gl_account_hierarchies科目汇总模板层次ARSELECT * 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
29、 ar_receivable_applications_all核销SELECT * FROM ar_payment_schedules_all发票调整SELECT * FROM ar_adjustments_all会计分录SELECT * FROM ar_distributions_all付款计划美联社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核销
