EBS二次开发常用的SQL

1、本帖最后由 ljb_ 于 2010-12-10 15:41 编辑-(1./* 根据报表文件名称关键字查找报表的执行文件名称等信息*/SELECT A.USER_CONCURRENT_PROGRAM_NAME as 用户并发程序名称,A.CONCURRENT_PROGRAM_NAME as 并发程序,A.OUTPUT_FILE_TYPE as 输出格式,B.EXECUTION_FILE_NAME 执行文件名,B.EXECUTABLE_NAME,FND_L.MEANING as 文件格式,B.USER_EXECUTABLE_NAME as 执行文件名,B.DESCRIPTION as 描述FROM

2、 FND_CONCURRENT_PROGRAMS_VL A,fnd_executables_vl B,FND_LOOKUPS FND_LWHERE A.APPLICATION_ID=B.APPLICATION_IDAND A.EXECUTABLE_ID=B.EXECUTABLE_IDAND B.EXECUTION_METHOD_CODE= FND_L.LOOKUP_CODE(+AND FND_L.LOOKUP_TYPE='CP_EXECUTION_METHOD_CODE'AND A.USER_CONCURRENT_PROGRAM_NAME LIKE '挑库单报表%

3、9;-(2./*查找在标准请求组里提交的报表所在的职责*/SELECT A.RESPONSIBILITY_NAME,B.USER_CONCURRENT_PROGRAM_NAME,B.CONCURRENT_PROGRAM_NAME FROM FND_RESPONSIBILITY_VL A,FND_CONCURRENT_PROGRAMS_VL B,FND_REQUEST_GROUP_UNITS CWHERE A.APPLICATION_ID=C.APPLICATION_IDAND A.REQUEST_GROUP_ID=C.REQUEST_GROUP_IDAND B.APPLICATION_ID=C

4、.UNIT_APPLICATION_IDAND B.CONCURRENT_PROGRAM_ID=C.REQUEST_UNIT_IDAND B.USER_CONCURRENT_PROGRAM_NAME LIKE '%挑库单报表%'-(3./*查找在菜单里提交的报表所在职责*/SELECT A.RESPONSIBILITY_NAME as 职责名,B.PROMPT as 并发程序名,F.USER_CONCURRENT_PROGRAM_NAMEFROM FND_RESPONSIBILITY_VL A,FND_MENU_ENTRIES_VL B,FND_FORM_FUNCTIONS_V

5、L C,FND_REQUEST_GROUPS D,FND_REQUEST_GROUP_UNITS E,FND_CONCURRENT_PROGRAMS_VL FWHERE A.MENU_ID=B.MENU_IDAND B.FUNCTION_ID=C.FUNCTION_IDAND C.PARAMETERS LIKE '%'|D.REQUEST_GROUP_CODE|'%'AND D.APPLICATION_ID=E.APPLICATION_IDAND D.REQUEST_GROUP_ID=E.REQUEST_GROUP_IDAND E.UNIT_APPLICATIO

6、N_ID=F.APPLICATION_IDAND E.REQUEST_UNIT_ID=F.CONCURRENT_PROGRAM_IDAND F.USER_CONCURRENT_PROGRAM_NAME LIKE '%挑库单报表%'-(4./* 根据窗口名称查找关键字弹性域用到的表,列等信息*/-SELECT C.ID_FLEX_NAME,A.ID_FLEX_STRUCTURE_NAME,B.FORM_LEFT_PROMPT,C.APPLICATION_TABLE_NAME,B.APPLICATION_COLUMN_NAME,B.FLEX_VALUE_SET_ID FROM FN

7、D_ID_FLEX_STRUCTURES_VL A,FND_ID_FLEX_SEGMENTS_VL B,FND_ID_FLEXS CWHERE A.ID_FLEX_STRUCTURE_NAME='帐户别名' -用你自己要查的代替,就是Form窗口的标题AND A.APPLICATION_ID=B.APPLICATION_IDAND A.ID_FLEX_CODE=B.ID_FLEX_CODEAND A.ID_FLEX_NUM=B.ID_FLEX_NUMAND A.APPLICATION_ID=C.APPLICATION_IDAND A.ID_FLEX_CODE=C.ID_FLEX

8、_CODE-根据上面FLEX_VALUE_SET_ID查弹性域的数据SELECT *FROM FND_FLEX_VALUES_VL TWHERE T.FLEX_VALUE_SET_ID=1005982 - FLEX_VALUE_SET_ID-具体某一数据SELECT *FROM FND_FLEX_VALUES_VL TWHERE T.FLEX_VALUE_SET_ID=1005982AND T.FLEX_VALUE='720611'(5/*根据描述性弹性域的标题查找描述性弹性域表和列*/-SELECT FND_DFV.TITLE,FND_DFV.DESCRIPTIVE_FLEX

9、FIELD_NAME ,FND_DFV.APPLICATION_TABLE_NAME ,FND_DFU.APPLICATION_COLUMN_NAME,FND_DFU.FORM_LEFT_PROMPT,FND_DFU.FORM_ABOVE_PROMPTFROM FND_DESCRIPTIVE_FLEXS_VL FND_DFV,FND_DESCR_FLEX_COL_USAGE_VL FND_DFUWHERE FND_DFV.TITLE='物料' -如:物料AND FND_DFU.DESCRIPTIVE_FLEXFIELD_NAME=FND_DFV.DESCRIPTIVE_FLEX

10、FIELD_NAME(6。查找运行请求时间,参数等(可以是某用户的,某个报表SELECT C.USER_NAME,papf.full_name,B.USER_CONCURRENT_PROGRAM_NAME,A.REQUEST_DATE,A.ARGUMENT_TEXT,(A.ACTUAL_COMPLETION_DATE-A.ACTUAL_START_DATE*24*60 MINUTES,A.ACTUAL_START_DATE,A.ACTUAL_COMPLETION_DATE,a.request_id,a.outfile_nameFROM FND_CONCURRENT_REQUESTS A,FND

11、_CONCURRENT_PROGRAMS_VL B,FND_USER C,per_all_people_f papfWHERE A.CONCURRENT_PROGRAM_ID=B.CONCURRENT_PROGRAM_IDAND A.REQUESTED_BY=C.USER_IDand c.user_name=papf.employee_number(+AND A.ACTUAL_COMPLETION_DATE IS NOT NULLand B.USER_CONCURRENT_PROGRAM_NAME='你的程序名称' - like '%XXX%'and c.use

12、r_name=' 你要找的用户的'and a.request_date<=to_date('2005-03-01 23:59:59','yyyy-mm-dd hh24:mi:ss'and a.request_date>=to_date('2005-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'AND a.request_id> 2254198-=-下面的是用于修改表单注册情况=-select * from FND_FORM_VL where form_name=&#

13、39;出货信息' order by last_update_date descupdate FND_FORM set form_name='OUTINVINFO' where form_id=58864select * from fnd_form where form_name='出货信息'commit;-=-select item as 物料,subinventory as 子库存,locator as 货位,results_transaction_uom as 单位 from MTL_TXN_REQUEST_HEADERS_V select lot_

14、number as 批次,LOT_EXPIRATION_DATE as 到期日,pimary_quantity as 数量 from MTL_TXN_REQUEST_LINES_V/*=物料发送请求行视图=*/select * from MTL_TXN_REQUEST_LINES_Vselect * from MTL_TXN_REQUEST_HEADERS_V-其他信息从OE上取-organization_id = :_id -select * from MTL_ONHAND_LOCATOR_Vselect * from MTL_MATERIAL_TRANSACTIO

15、NS_TEMPselect * from MTL_SERIAL_NUMBERS_TEMPselect * from MTL_TRANSACTION_LOTS_TEMP-通过PO, 找点收单号:=通过PO, 找点收单号:作者: moonsoft(发表于: 2006.05.08 16:20分类: 分销出处: -select rsh.receipt_numfrom po_headers_all poh,rcv_shipment_headers rsh,rcv_shipment_lines rsl,po_lines_all pol,po_line_locations_all pollwherepoh.

16、segment1='20600021'andpoh.po_header_id=pol.po_header_idandpol.po_line_id=poll.po_line_idandpoll.line_location_id=rsl.po_line_location_idandrsh.SHIPMENT_HEADER_ID=rsl.shipment_header_id/*-/接收事务处理-*/ select * from RCV_TRANSACTIONS_Vselect * from RCV_TRANSACTIONS_INTERFACEselect * from MTL_TRAN

17、SACTION_LOTS_TEMPselect * from MTL_SERIAL_NUMBERS_TEMP -条码-select * from QA_RESULTS_Vselect * from ic_lots_mstselect * from mtl_serial_numbers -条码表-select * from wms_license_plate_numbersselect * from rcv_lots_supplyselect * from oe_lot_serial_numbers -销售订单批次条码- select * from rcv_transactions -/接收事务

18、处理-select * from sy_reas_cdsselect * from mtl_serial_numbers_all_vselect * from qa_plan_char_value_lookupsselect * from mtl_lot_numbersselect * from mtl_serial_numbers-序列号select * from wip_operations_all_vselect * from cs_counter_valuesselect * from wip_discrete_jobs_all_vselect * from cs_incidentss

19、elect * from qa_ahl_mrselect * from cs_countersselect * from qa_csi_item_instancesselect * from mtl_system_items_kfvselect * from wms_lpn_contentsselect * from ic_item_mstselect * from ic_tran_pndselect * from ic_loct_invselect * from ic_loct_mstselect * from hr_employees-人事人员select * from pjm_proje

20、cts_all_vselect * from PA_TASKS_EXPEND_Vselect * from wip_osp_jobs_val_vselect * from mtl_kanban_cardsselect * from hr_locations_all -人事组织档案- select * from hr_locations_all_tl -同上select * from mtl_serial_numbers_all_v -序列视图select * from hz_parties-select * from po_pos_val_vselect * from rcv_transact

21、ions_interfaceselect * from PO_LINE_LOCATIONSselect * from PO_REQUISITION_LINESselect * from wip_discrete_jobs_all_vselect * from oke_k_headers_lov_vselect * from oke_k_lines_full_vselect * from oke_k_deliverables_vlselect * from OE_SOLD_TO_ORGS_V -select * from cst_cost_groups -select * from rcv_sh

22、ipment_headers -发运头select * from rcv_shipment_lines -发运体select * from rcv_transactions_interfaceselect * from oe_order_lines_all -订单select * from oe_order_headers_all -订单select * from oe_transaction_types_all -订单处理交易类型select * from MTL_TRANSACTION_TYPES-交易的类型-select * from oe_transaction_types_tl -订

23、单处理类型select * from rcv_transactions -接收事务处理select * from rcv_supply -select * from oe_transaction_typesselect * from oe_po_enter_receipts_vselect * from mtl_customer_items_all_vselect * from mtl_lot_issues_val_v -select * from mtl_uom_conversions -select * from mtl_uom_class_conversions -select * fr

24、om po_lines_supplier_items_vselect * from per_all_people_f -select * from financials_system_parametersselect * from org_freight -运输组织-select * from mtl_supply -物料供给select * from org_organization_definitions -库存组织定义select * from po_vendor_sitesselect * from rcv_sources_both_val_v -select * from rcv_s

25、uppliers_val_v -select * from hr_locations_all -select * from hr_locations_all_tl -select * from mtl_item_revisions -select * from mtl_system_items_kfv -正规ID编码,-select * from po_requisition_linesselect * from financials_system_parametersselect * from po_lookup_codes -select * from po_requisition_hea

26、dersselect * from rcv_shipment_lines-select * from rcv_transactions-select * from po_line_locationsselect * from hr_locations_all_tl -select * from po_releasesselect * from po_pos_all_vselect * from po_pos_val_vselect * from per_all_people_f -select * from rcv_transactions_interfaceselect * from mtl

27、_serial_numbers -SERIAL-select * from mtl_transaction_lots_tempselect * from mtl_employees_view -select * from po_suppliers_val_v -select * from mtl_employees_current_view -select * from mtl_item_status -物料项状态基础表- select * from org_organization_definitions -select * from mtl_secondary_inventories -子

28、库存组织-select * from mtl_transaction_types -select * from mtl_txn_source_types -select * from mtl_system_items_vl -select * from mtl_system_items_kfv -select * from mtl_category_sets_vl -select * from mtl_physical_inventories_v -select * from mtl_kanban_cardsselect * from mtl_item_sub_inventoriesselec

29、t * from fnd_folders -select * from fnd_user -系统用户select * from so_order_types_all -销售订单类型select * from oe_order_headers_all -select * from qa_customers_lov_v -select * from qa_sales_orders_lov_v-select * from PO_VENDORS -采购供货方-select * from po_shipments_all_vselect * from po_lines_val_vselect * fro

30、m po_pos_val_vselect * from mtl_task_vselect * from pjm_projects_all_vselect * from qa_customers_lov_v -select * from mtl_item_revisions -select * from mtl_category_sets-select * from wip_operations_all_vselect * from wip_first_open_schedule_vselect * from wip_discrete_jobs_all_vselect * from wip_li

31、nes_val_vselect * from mtl_item_uoms_view -select * from bom_resources_val_vselect * from bom_departments_val_vselect * from qa_plan_char_value_lookups -select * from qa_plans-select * from qa_specs_vselect * from qa_specs_val_vselect * from po_lookup_codes -select * from hr_employees_current_v -sel

32、ect * from po_quality_codesselect * from mtl_transaction_reasons -库存相关接转信息- select * from mtl_uom_conversions_val_v -select * from mtl_uom_class_conversions -select * from hr_locations_all -select * from hr_locations_all_tl -select * from hz_locations -select * from hz_party_sites -select * from hz_

33、cust_site_uses_all -select * from hz_cust_acct_sites_all -select * from hz_cust_accounts -select * from oe_order_lines_all -select * from oe_drop_ship_sourcesselect * from rcv_trx_int_lots_vselect * from mtl_rma_serial_tempselect * from rcv_trx_int_serials_vselect * from po_distributionsselect * fro

34、m pjm_projects_all_vselect * from po_distributionsselect * from hr_locations_all_tl-select * from hr_employees_current_v -select * from po_lookup_codes -select * from pjm_projects_all_vselect * from pa_tasks_expend_vselect * from mtl_kanban_cardsselect * from qa_plans_val_v -签证分类select t.meaning fro

35、m fnd_lookup_values_vl t Where t.lookup_type='CUX_FC_QZYY'- 任务IDSelect Distinct A.Task_Number,A.Task_Name From pa_tasks A-/发运-select * from wsh_new_deliveries_vselect * from mtl_txn_request_lines_vselect * from oe_order_headers_vselect * from wsh.wsh_delivery_details-/出货信息-select * from oe_l

36、ot_serial_numbers -订单批次与序列号select * from ic_lots_mstselect * from mtl_serial_numbers-序列号select * from rcv_transactions -接收处理select * from gml_recv_trans_mapselect * from ic_tran_pndselect * from ic_loct_invselect reason_code,reason_desc1 from sy_reas_cds order by 1select * from MTL_MATERIAL_TRANSACT

37、IONS-是物料交易表,-它存放着相关库存物料的每一笔交易,或库存更新的每一笔数据-物料处理,(库存物料事物表select * from MTL_CONSUMPTION_TXN_TEMPselect * from hr_locations_all -收货地点档案- select * from hz_locations-交货地点select * from wms_lpn_contentsselect * from mtl_subinventories_val_v -子库-select * from mtl_object_genealogyselect * from mtl_lot_numbers

38、-物料批号select * from mtl_lot_issues_val_v -物料批号发出select * from wms_license_plate_numbersselect * from cst_cost_groupsselect * from mtl_item_sub_val_vselect * from mtl_subinventories_trk_val_v-select * from mtl_item_sub_trk_val_vselect * from mtl_item_sub_exp_val_vselect * from mtl_sub_exp_val_vselect

39、* from mtl_so_rma_interfaceselect * from mtl_system_items-库存、工程和采购物料的明细-物料信息- select * from mtl_item_revisions-修订select * from bom_departmentsselect * from wip_lines_all_vselect * from wip_entitiesselect * from wip_discrete_jobs_all_vselect * from wsh_inv_delivery_details_v-库存存货发放明细select * from mtl

40、_txn_request_lines-请求select * from mtl_material_transactions_tempselect * from mtl_transaction_types-物料处理类型select * from pjm_unit_numbers_lov_vselect * from mtl_sales_orders-销售订单select * from mtl_secondary_inventories -select * from mtl_lot_numbers-select * from pjm_tasks_vselect * from pjm_projects

41、_vselect * from pjm_project_parametersselect * from fnd_user -系统用户select * from mtl_txn_request_headers-头select * from mtl_txn_request_lines-体select * from mtl_onhand_quantities_detail-物料的库存明细-select * from fnd_folders-select * from mtl_item_sub_inventoriesselect * from mtl_kanban_cards-物料看板select *

42、 from mtl_physical_inventories_v-物理库存select * from mtl_category_sets_vl-物料类别select * from mtl_system_items_vlselect * from mtl_txn_source_typesselect * from mtl_transaction_types-处理类型select * from org_organization_definitions-库存组织 where orselect * from mtl_item_status-物料状态select * from mtl_employees

43、_current_view-员工select * from po_suppliers_val_v-采购供应商视图select * from mtl_employees_view-员工select * from mtl_transaction_lots_tempselect * from pa_projects_expend_vselect * from pa_tasks_expend_vselect * from pa_organizations_expend_vselect * from mtl_onhand_quantities_detail-物料现存量明细select * from mt

44、l_lot_numbers-物料批号select * from mtl_transaction_reasons-物料处理原因select * from mtl_item_uoms_view-物料单位mtl_units_of_measureselect * from mtl_so_rma_interfaceselect * from mtl_system_items-物料项select * from mtl_item_sub_ast_trk_val_vselect * from mtl_lot_issues_val_v-批次-select * from mtl_so_rma_interfaces

45、elect * from mtl_sub_ast_trk_val_v -子库select * from pa_expenditure_typesselect * from MTL_TXN_REQUEST_LINES_V-发送请求select * from MTL_SERIAL_NUMBERS_TEMPselect * from MTL_TRANSACTION_LOTS_TEMPselect * from MTL_MATERIAL_TRANSACTIONS_TEMPselect * from MTL_ITEM_LOCATTIONS-货位Select * from Po_Requisition_H

46、eaders_all-请购单头表Select * from Po_Requisition_lines_all-体表Select * from PO_HEADER_ALL-采购订单头Select * from PO_LINES_ALL-采购订单体select * from wsh_pick_slip_vselect * from mtl_system_items_vl msi - bug# 3306781 select * from wsh_delivery_details wddselect * from mtl_txn_request_lines mtrlselect * from mtl_

47、txn_request_headers mtrhselect * from wsh_delivery_assignments wdaselect * from wsh_new_deliveries wndselect * from oe_order_lines_all oolaselect * from oe_sets osselect * from wsh_pick_grouping_rules wpgrselect * from hz_locations -交货位置表select * from hr_locations_all-收貨位置表Select flex_value_set_id F

48、rom apps.fnd_flex_value_setsselect * from MTL_TXN_REQUEST_LINES_V -查找物料搬运单 SELECT TRANSACTION_TYPE_NAME,TRANSACTION_TYPE_ID,TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID,LINE_ID,REQUEST_NUMBER,HEADER_ID, MOVE_ORDER_TYPE,MOVE_ORDER_TYPE_NAME,LINE_NUMBER,ORGANIZATION_ID, INVENTORY_ITEM_ID,REVISION

49、,FROM_SUBINVENTORY_CODE,FROM_LOCATOR_ID, TO_SUBINVENTORY_CODE,FROM_SUB_LOCATOR_TYPE,TO_LOCATOR_ID,TO_ACCOUNT_ID, LOT_NUMBER,SERIAL_NUMBER_START,SERIAL_NUMBER_END,UNIT_NUMBER,UOM_CODE, QUANTITY,REQUIRED_QUANTITY,QUANTITY_DELIVERED,QUANTITY_DETAILED,DATE_REQUIRED, REASON_ID,REFERENCE,REFERENCE_ID,REFE

50、RENCE_TYPE_CODE,PROJECT_ID,TASK_ID, TRANSACTION_HEADER_ID,LINE_STATUS,STATUS_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,CREATED_BY,REQUEST_ID,CREATION_DATE, PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,ATTRIBUTE1,ATTRIBUTE2, ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUT

51、E7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10 ,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE_CATEGORY, TO_SUB_LOCATOR_TYPE,FROM_SUB_ASSET,FROM_SUB_QUANTITY_TRACKED,FROM_SUB_MATERIAL_ACCOUNT, TXN_SOURCE_ID,TXN_SOURCE_LINE_ID,TXN_SOURCE_LINE_DETAIL_ID,PRIMARY_QUANTITY, TO_ORGANIZATION_I

52、D,PICK_STRATEGY_ID,PUT_AWAY_STRATEGY_ID,SHIP_TO_LOCATION_ID FROM MTL_TXN_REQUEST_LINES_V WHERE organization_id = '117' AND -1=-1 and ( mtl_txn_request_lines_v.move_order_type != 6 AND mtl_txn_request_lines_v.request_number between '4009' AND '4009' AND mtl_txn_request_lines_v.line_status in (3,7,9 and (REQUEST_NUMBER='4009' order by REQUEST_NUMBER,MOVE_ORDER_TYPE_NAME,LINE_NUMBER -哪个用户锁定了哪个表的 SQL-SELECT c.owner ,c.object_name ,c.object_t