EBS二次開(kāi)發(fā)常用的SQL

1、本帖最后由 ljb_ 于 2010-12-10 15:41 編輯-(1./* 根據(jù)報(bào)表文件名稱關(guān)鍵字查找報(bào)表的執(zhí)行文件名稱等信息*/SELECT A.USER_CONCURRENT_PROGRAM_NAME as 用戶并發(fā)程序名稱,A.CONCURRENT_PROGRAM_NAME as 并發(fā)程序,A.OUTPUT_FILE_TYPE as 輸出格式,B.EXECUTION_FILE_NAME 執(zhí)行文件名,B.EXECUTABLE_NAME,FND_L.MEANING as 文件格式,B.USER_EXECUTABLE_NAME as 執(zhí)行文件名,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 '挑庫(kù)單報(bào)表%

3、9;-(2./*查找在標(biāo)準(zhǔn)請(qǐng)求組里提交的報(bào)表所在的職責(zé)*/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 '%挑庫(kù)單報(bào)表%'-(3./*查找在菜單里提交的報(bào)表所在職責(zé)*/SELECT A.RESPONSIBILITY_NAME as 職責(zé)名,B.PROMPT as 并發(fā)程序名,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 '%挑庫(kù)單報(bào)表%'-(4./* 根據(jù)窗口名稱查找關(guān)鍵字彈性域用到的表,列等信息*/-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窗口的標(biāo)題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-根據(jù)上面FLEX_VALUE_SET_ID查彈性域的數(shù)據(jù)SELECT *FROM FND_FLEX_VALUES_VL TWHERE T.FLEX_VALUE_SET_ID=1005982 - FLEX_VALUE_SET_ID-具體某一數(shù)據(jù)SELECT *FROM FND_FLEX_VALUES_VL TWHERE T.FLEX_VALUE_SET_ID=1005982AND T.FLEX_VALUE='720611'(5/*根據(jù)描述性彈性域的標(biāo)題查找描述性彈性域表和列*/-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。查找運(yùn)行請(qǐng)求時(shí)間,參數(shù)等(可以是某用戶的,某個(gè)報(bào)表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-=-下面的是用于修改表單注冊(cè)情況=-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 子庫(kù)存,locator as 貨位,results_transaction_uom as 單位 from MTL_TXN_REQUEST_HEADERS_V select lot_

14、number as 批次,LOT_EXPIRATION_DATE as 到期日,pimary_quantity as 數(shù)量 from MTL_TXN_REQUEST_LINES_V/*=物料發(fā)送請(qǐng)求行視圖=*/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-通過(guò)PO, 找點(diǎn)收單號(hào):=通過(guò)PO, 找點(diǎn)收單號(hào):作者: moonsoft(發(fā)表于: 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/*-/接收事務(wù)處理-*/ 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 -/接收事務(wù)

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-序列號(hào)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 -發(fā)運(yùn)頭select * from rcv_shipment_lines -發(fā)運(yùn)體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 -接收事務(wù)處理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 -運(yùn)輸組織-select * from mtl_supply -物料供給select * from org_organization_definitions -庫(kù)存組織定義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 -正規(guī)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 -物料項(xiàng)狀態(tài)基礎(chǔ)表- select * from org_organization_definitions -select * from mtl_secondary_inventories -子

28、庫(kù)存組織-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 -系統(tǒng)用戶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 -采購(gòu)供貨方-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 -庫(kù)存相關(guān)接轉(zhuǎn)信息- 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'- 任務(wù)IDSelect Distinct A.Task_Number,A.Task_Name From pa_tasks A-/發(fā)運(yùn)-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 -訂單批次與序列號(hào)select * from ic_lots_mstselect * from mtl_serial_numbers-序列號(hào)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-是物料交易表,-它存放著相關(guān)庫(kù)存物料的每一筆交易,或庫(kù)存更新的每一筆數(shù)據(jù)-物料處理,(庫(kù)存物料事物表select * from MTL_CONSUMPTION_TXN_TEMPselect * from hr_locations_all -收貨地點(diǎn)檔案- select * from hz_locations-交貨地點(diǎn)select * from wms_lpn_contentsselect * from mtl_subinventories_val_v -子庫(kù)-select * from mtl_object_genealogyselect * from mtl_lot_numbers

38、-物料批號(hào)select * from mtl_lot_issues_val_v -物料批號(hào)發(fā)出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-庫(kù)存、工程和采購(gòu)物料的明細(xì)-物料信息- 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-庫(kù)存存貨發(fā)放明細(xì)select * from mtl

40、_txn_request_lines-請(qǐng)求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 -系統(tǒng)用戶select * from mtl_txn_request_headers-頭select * from mtl_txn_request_lines-體select * from mtl_onhand_quantities_detail-物料的庫(kù)存明細(xì)-select * from fnd_folders-select * from mtl_item_sub_inventoriesselect * from mtl_kanban_cards-物料看板select *

42、 from mtl_physical_inventories_v-物理庫(kù)存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-庫(kù)存組織 where orselect * from mtl_item_status-物料狀態(tài)select * from mtl_employees

43、_current_view-員工select * from po_suppliers_val_v-采購(gòu)供應(yīng)商視圖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-物料現(xiàn)存量明細(xì)select * from mt

44、l_lot_numbers-物料批號(hào)select * from mtl_transaction_reasons-物料處理原因select * from mtl_item_uoms_view-物料單位mtl_units_of_measureselect * from mtl_so_rma_interfaceselect * from mtl_system_items-物料項(xiàng)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 -子庫(kù)select * from pa_expenditure_typesselect * from MTL_TXN_REQUEST_LINES_V-發(fā)送請(qǐng)求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-請(qǐng)購(gòu)單頭表Select * from Po_Requisition_lines_all-體表Select * from PO_HEADER_ALL-采購(gòu)訂單頭Select * from PO_LINES_ALL-采購(gòu)訂單體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 -查找物料搬運(yùn)單 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 -哪個(gè)用戶鎖定了哪個(gè)表的 SQL-SELECT c.owner ,c.object_name ,c.object_t