oracle-EBS-常用的相关表整理

select * from dba_users order by 1 --数据库 scheme
select * from sys.dba_sys_privs where grantee = 'APPS' --权限表
select * from FND_USER where USER_ID= 1896
select * from per_people_f
select * from dba_objects where object_name = 'wsh_deliverables_v' --对象
select object_name from user_objects where object_type='L_AR_PAYMETN_SCHEDULES_V'
select dbms_metadata.get_ddl('VIEW','LS_AR_PAYMENT_SCHEDULES_V') from dual; --查看视图的组成,注意VIEW 要大写

select * from FND_LOOKUP_VALUES where MEANING LIKE 'CITY%' --参数表
select * from org_organization_definitions where OPERATING_UNIT in (562) --库存组织
select * from HR_OPERATING_UNITS  -公司组织
select * from mtl_secondary_inventories  仓位代码和描述
select * from mtl_item_locations where ORGANIZATION_ID ='119' --库存对应的具体货位

select * from mtl_item_locations
select * from gmf_period_balances  --GL区间

select * from hr_locations_all --收货地点名称表
select * from hr_locations_all_tl --收货地点表
select * from po_ship_to_loc_org_v where organization_id = 120 --收货方相关表
select * from ap_suppliers where vendor_name like '%霖%' --供应商名称表
select * from ap_supplier_sites_all assa where VENDOR_ID in ( 194044) --供应商地点
select * from AP_SUPPLIER_CONTACTS --联系人
select * from PO_VENDOR_CONTACTS --联系人
select * from AP_TERMS -- 付款条件 START_DATE_ACTIVE :生效日期 END_DATE_ACTIVE :失效日期
select * from PO_VENDOR_CONTACTS --供应商联络人
select * from hz_contact_points -- 联系电话
select * from hz_parties AA WHERE AA.party_name like '%四川%' ; --供应商具体信息表
select * from po_vendors where VENDOR_NAME like '%扬子%'

select * from FND_CONC_REQ_SUMMARY_V where PROGRAM like '%Credit%' ARGUMENT_TEXT like '%1067907%'  --查看并发请求队列
select * from FND_CONC_REQ_SUMMARY_V where REQUEST_ID =13761979
select * from MTL_TXN_REQUEST_LINES_V
select * from CM_ACER_MSG where request_id=5179909 order by message_comment --查看请求的具体描述比如跑成本时候的警告

select * from PER_ALL_PEOPLE_F --员工主表
select * from PO_AGENTS --po采购员 Buyer的ID值，對應到員工主檔(PERSON_ID)
select * from mtl_lot_numbers where ORGANIZATION_ID = 119 --货位表
select * from hz_cust_accounts AA WHERE aa.PARTY_ID = 127010
select * from hz_parties
select * from ra_terms

--采购--------------------------------
select * from PO_REQUISITION_HEADERS_ALL --请购单SEGMENT1 为请购单号
select * from PO_REQUISITION_LINES_ALL --请购单
select * from PO_REQ_DISTRIBUTIONS_ALL  --请购单明细
select * from po_headers  --PO头表
select * from PO_HEADERS_ALL
select * from PO_LINES_ALL
select * from po_line_locations
select * from Po_Line_Locations_all 与po line 是1对多的关系，提交运费后，QUANTITY_RECEIVED显示生产接收数量，qc检验后，QUANTITY_ACCEPTED有数量,QC未入库状态的退给供应商，是减少QUANTITY_RECEIVED
select * from po_distributions_all --分配，QUANTITY_DELIVERED是入库数,可以和mtl_material_transactions做对比,注意，采购订单中分配中可以分配多行，以此行数可能比Po_Line_Locations_all还多
select * from AP_TERMS_TL --付款条件 --PO_HEADERS_ALL.TERMS_ID = AP_TERMS_TL.TERM_ID
--PO_LINE_LOCATIONS_ALL.TERMS_ID = AP_TERMS_TL.TERM_ID
select * from RCV_ROUTING_HEADERS where --接收方式
select * from HR_LOCATIONS --送货地点
select * from HR_LOCATIONS_ALL
select * from po_releases_all where PO_HEADER_ID in (4148182,4148182) --订单发放
select * from PO_LINE_LOCATIONS_TRX_V WHERE PO_HEADER_ID in (4148182,4148182)
select * from MTL_SUPPLY where SUPPLY_TYPE_CODE = 'PO' and PO_HEADER_ID=4148182 --接收信息表，产生PO单后就会有，生产接收后此处QUANTITY会变为剩余生产接收数量，与rcv_supply 有区别
接收后：
更新rcv_transactions_interface的数量，Po_Line_Locations_all中QUANTITY_RECEIVED还没数量
select * from rcv_headers_interface where RECEIPT_HEADER_ID in (4148182)
select * from rcv_transactions_interface where po_header_id in (4148182) --接收后写入,po headerid,有个PRIMARY_QUANTITY，接收数，提交运费后，此信息清空
select * from rcv_shipment_headers where SHIPMENT_HEADER_ID in (6798232,0) RECEIPT_NUM in ( 30006861,0 ) and SHIP_TO_ORG_ID = 271 and SHIPMENT_HEADER_ID in (33205) --接收发送头表，接收后，产生编号，以及运费信息
select * from rcv_shipment_lines where SHIPMENT_HEADER_ID in (6798232,0); --只接收后找不到明细，提交运费后，产生明细
select * from rcv_transactions where PO_HEADER_ID=4148182 4050091 4367334 and SHIPMENT_HEADER_ID =118668 and SHIPMENT_LINE_ID = 38478 order by TRANSACTION_ID; --采购处理事务表，只接收，找不到明细，提交运费后产生receive明细，qc检验后产生Acceppt类型，其他每笔交易都会有类型
select * from QA_RESULTS
SELECT * FROM rcv_supply where PO_HEADER_ID = 4148182 -- 与MTL_SUPPLY类似，但更详细，完全Delivery之后，RCV_SUPPLY和MTL_SUPPLY就会被删除，若只有生产做过接收，没明细。提交运费后产生明细，明细总数为生产接收数，但QC检验或入库
但qc检验后，此处会分行，总数量为生产的接收数，与父类型rcv_transactions的id对应。入库后，此处为剩余的待接收和待入库数
select * from rcv_receiving_sub_ledger where RCV_TRANSACTION_ID =6287702 --暂记应付表 记录采购订单接收后，产生的暂记应付信息(接收事务处理产生的分配行)
入库：
select * from rcv_transactions_interface where trunc(CREATION_DATE) >=to_date('20201228', 'RRRRMMDD')
select * from MTL_TRANSACTIONS_INTERFACE where trunc(CREATION_DATE) >=to_date('20201228', 'RRRRMMDD') SOURCE_HEADER_ID = 4148182
-- 要想物料事务处理管理器能处理库存接口中的数据，则必须保证如下标志的信息：
-- PROCESS_FLAG = 'Y'
-- LOCK_FLAG = 'N' or NULL
-- TRANSACTION_MODE = 3
-- ERROR_CODE = NULL
-- ERROR_EXPLANATION = NULL
--并且当接口中的数据发生错误，也必须把这些标志修改为如上的状态，
--数据才会被处理
--同理MTL_MATERIAL_TRANSACTIONS_TEMP也是如此
select * from MTL_MATERIAL_TRANSACTIONS_TEMP where trunc(CREATION_DATE) >=to_date('20201228', 'RRRRMMDD') TRANSACTION_HEADER_ID= 6673991 INVENTORY_ITEM_ID=13741
select * from MTL_MATERIAL_TRANSACTIONS
select * from mtl_transaction_lot_numbers where TRANSACTION_ID in ( 52851804, 52851805 )--批次
select * from mtl_lot_numbers where LOT_NUMBER ='20160531'

select * from mtl_system_items msi --物料描述表
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID =13741 SEGMENT1 like '10211001227002-000%' --物料信息表
select * from MTL_SYSTEM_ITEMS_FVL --简体的物料描述表
SELECT * FROM MTL_CATEGORIES_B --物料分类
SELECT * FROM MTL_CATEGORIES_V --物料分类
select * from mtl_material_transactions  --物料事务处理,非常重要
select * from Mtl_Generic_Dispositions
select * from mtl_transaction_types where TRANSACTION_SOURCE_TYPE_ID =2 -- 事务类型
select * from mfg_lookups where lookup_type ='MTL_TRANSACTION_ACTION' -- 事务处理活动（transaction_action_id） --無聊搬運單 LOOKUP_TYPE = 'MOVE_ORDER_TYPE'
select lookup_code,meaning from mfg_lookups where lookup_type ='MTL_DISPOSITION' --事务处理来源（transaction_source_type_id）
select lookup_code,DESCRIPTION Meaning from po_lookup_codes where lookup_type = 'RCV TRANSACTION TYPE' --RCV_TRANSACTIONS_INTERFACE 中的 事务处理类型（TRANSACTION_TYPE）

select * from mtl_onhand_quantities  --库存现有量：
select * from MTL_TRANSACTIONS_INTERFACE --库存事务接口表
select * from org_acct_periods  --库存区间

---财务匹配，验证后：
select * from AP_BATCHES_V -- 总账批试图
select * from AP_BATCHES_ALL where batch_id=160183 -- 总账批
select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =63002)); --从po分配表取

select * from ap_invoices_all aia, ap_invoice_lines_all aila, ap_invoice_distributions_all aida
where aia.invoice_id=aila.invoice_id
and aila.invoice_id=aida.invoice_id and aila.line_number=aida.invoice_line_number
and aia.VENDOR_ID= 1050 and INVOICE_NUM = '515101' aila.po_header_id=99005 ; -- po_distribution_id，如果是杂项匹配运费，则没有，

select * from ap_invoice_lines_all --明细，--如果ap未验证，不会写入数据到 QUANTITY_INVOICED，AMOUNT
-- Invoice distributions are validated individually and the status is stored at the invoice distribution level. This status is stored in AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG.
--Valid values for the column are:
--A - Validated (it used to be called Approved)
--N or NULL - Never validated
--T - Tested but not validated
--The invoice header form derives the invoice validation status based on the following:
--'Validated'
--- If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'
--'Never Validated'
--- If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'
--'Needs Revalidation'
--- If there are any rows in AP_HOLDS that do not have a release code.
--- If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.
--- If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).
--I hope this information helps you.

Select * FROM rcv_Accounting_Events Where rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =4148182);

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =82003);
select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =82003);

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id =82003);
select * from FINANCIALS_SYSTEM_PARAMS_ALL
select * from ap_invoices_interface
select * from ap_invoice_lines_interface

--成本
select * from MTL_PARAMETERS --定义成本方法， Primary_cost_method ：1 : 標準成本2 : 移動平均
select * from CST_COST_TYPES --成本类型
select * from BOM_RESOURCES --成本子要素
select * from CST_RESOURCE_COSTS --资源成本费率
select * from CST_DEPARTMENT_OVERHEADS
select * from CST_ITEM_COSTS --料件成本
select * from CST_ITEM_COST_DETAILS
select * from MTL_TRANSACTION_ACCOUNTS
select * from CST_COST_HISTORY_V --料件历史成本
select * from WIP_TRANSACTIONS
select * from WIP_TRANSACTION_ACCOUNTS
select * from WIP_PERIOD_BALANCES

--工艺路线
select * from fm_form_mst --配方
select gr.*,gor.resources,goa.activity,goa.* from gmd_routings gr
left join FM_ROUT_DTL frd on gr.ROUTING_ID =frd.ROUTING_ID
left join GMD_OPERATION_ACTIVITIES goa on frd.OPRN_ID = goa.OPRN_ID --and goa.ACTIVITY like 'L%'
left join GMD_OPERATION_RESOURCES gor on goa.OPRN_LINE_ID = gor.OPRN_LINE_ID -- and gor.RESOURCES like '%DL%'
where gr.ROUTING_NO like '%240-406%'
select * FROM GMD_OPERATION_RESOURCES
select * FROM FM_ROUT_DTL WHERE (OPRN_ID=661)
select * from gmd_operations_vl where (OPRN_ID=661) --流程工序
select * from GMD_OPERATION_ACTIVITIES where OPRN_ID =661 --活动

SELECT * FROM CR_RSRC_MST_VL WHERE (RESOURCES like '%2-%') order by RESOURCES --通用资源
SELECT * FROM GMP_RESOURCE_DEVICES WHERE (RESOURCE_ID=17008) --工厂资源