PO常用SQL语言

1、bility_id -7查找组织信息 author:dezai select organization_id ID,Organization_code 代码,Organization_name 名称, OPERATING_UNIT 营运OUID from org_organization_definitions ood; -8查找物料基本信息 author:dezai Select organization_id 组织ID, Inventory_item_id 物料ID, Segment1 物料代码, Description 物料描述, Primary_uom_code 物料单位 FROM m

2、tl_system_items_b msib; -9查找付款条件 author:dezai Select at.TERM_ID ID, at.NAME 名称, at.DESCRIPTION 说明 FROM ap_terms at; -10查找税码 author:dezai Select atca.Tax_Id ID, atca.Name 名称, atca.Set_Of_Books_Id 所属账套ID, atca.Description 描述, atca.Org_Id 组织ID FROM Ap_Tax_Codes_All atca; -11查询所有应用模块的ID,对应的职责ID,模块的简称代码

3、author:dezai Select resp.application_id, resp.Responsibility_Id, resp.Responsibility_Key, appl.application_short_name FROM fnd_responsibility resp, fnd_application appl Where resp.application_id = appl.application_id; -12查询当前系统登录的用户数 author:dezai Select COUNT (DISTINCT d.user_name FROM apps.fnd_logi

4、ns a, v$session b, v$process c, apps.fnd_user d Where b.paddr = c.addr AND a.pid = c.pid AND a.spid = cess AND d.user_id = a.user_id AND (d.user_name = 'USER_NAME' or 1 = 1; -13查询系统当前物料单位列表 author:dezai Select muom.UNIT_OF_MEASURE, muom.UOM_CODE, muom.DESCRIPTION, muom.UOM_CLASS, muom.S

5、OURCE_LANG FROM mtl_units_of_measure muom; -14OU 库存组织与子库存 author:dezai Select anization_id ou_org_id, hou.NAME ou_name, anization_id org_org_id, anization_code org_org_code, msi.secondary_inventory_name, msi.description FROM hr_organization_information hoi, hr_organization_units

6、 hou, org_organization_definitions ood, mtl_secondary_inventories msi Where _information1 = 'OPERATING_UNIT' AND anization_id = anization_id AND ood.operating_unit = anization_id AND anization_id = anization_id; -15查询库存物料现有量 author:dezai Select ms.*

7、FROM mtl_supply ms, po_headers_all ph Where ms.po_header_id = ph.po_header_id AND ph.segment1 = '2009001' -PO号 orDER BY ms.po_header_id, ms.po_release_id, ms.po_line_id, ms.po_line_location_id, ms.po_distribution_id; -16 查找死锁进程 author:dezai Select vs.username, lo.OBJECT_ID, , lo.SESS

8、ION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_ NAME, lo.PROCESS FROM V$LOCKED_OBJECT lo, V$SESSION vs, sys.obj$ sob Where lo.SESSION_ID = vs.SID AND sob.obj# = lo.OBJECT_ID; -17 中断死锁进程 author:dezai Alter SYSTEM KILL SESSION 'sid,serial#' -18 查找死锁进程2 author:dezai Select c.owner, c.object

9、_name, c.object_type, fu.user_name locking_fnd_user_name, fl.start_time locking_fnd_user_login_time, vs.module, vs.machine, vs.osuser, vlocked.oracle_username, vs.SID, vp.pid, vp.spid AS os_process, vs.serial#, vs.status, vs.saddr, vs.audsid, cess FROM fnd_logins fl, fnd_user fu, v$locked_obje

10、ct vlocked, v$process vp, v$session vs, dba_objects c Where vs.SID = vlocked.session_id AND vlocked.object_id = c.object_id AND vs.paddr = vp.addr AND vp.spid = cess_spid(+ AND vp.pid = fl.pid(+ AND fl.user_id = fu.user_id(+ -AND c.object_name LIKE '%' | UPPER('&tab_name_leaveb

11、lank4all' | '%' AND NVL (vs.status, 'XX' != 'KILLED' -1.已审批的请购单 author:dezai Select prh.requisition_header_id, prl.requisition_line_id,prh.segment1 from PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL where prh.requisition_header_id = prl.requisition_header_i

12、d /* and prh.requisition_header_id = 100 */ and prh.authorization_status = 'APPROVED' -2.已审批的采购单 author:dezai Select ph.po_header_id,pl.PO_LINE_ID,ph.segment1,ph.* from Po_Lines_all pl,Po_Headers_All ph where pl.PO_HEADER_ID=ph.po_header_id /* and ph.po_header_id = 22-1*/ and ph.authorizatio

13、n_status = 'APPROVED' and NVL(ph.cancel_flag,'N'<>'Y' and ph.creation_date>=trunc(sysdate -3查看用户的职责 author:dezai select c.user_name as login_name, d.full_name as employee_name, as department_name, a.user_id as user_id, a.responsibility_id as responsibility_id,

14、 b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME from FND_USER_RESP_GROUPS a, FND_RESPONSIBILITY_VL b, fnd_user c, hr_employees d, per_assignments_f e, hr_all_organization_units_tl f where a.user_id = c.user_id and c.employee_id = d.employee_id and c.employee_id = e.PERSON_ID and e.ORGANIZATION_ID = f.

15、organization_id and a.responsibility_id = b.RESPONSIBILITY_ID and sysdate > e.EFFECTIVE_START_DATE and sysdate < e.EFFECTIVE_END_DATE order by c.description, c.user_name, a.responsibility_id -4AP发票 select ai.* from ap_invoices_all ai ,ap_invoice_distributions_all id ,po_line_locations_all pll

16、,po_lines_all pl ,po_headers_all ph ,po_distributions_all pd where pl.po_header_id = ph.po_header_id /* and ph.segment1 = '2001' -采购订单编 号*/ and pll.po_line_id = pl.po_line_id and pll.line_location_id = pd.line_location_id and id.po_distribution_id = pd.po_distribution_id and ai.invoice_id =

17、id.invoice_id; -5未过帐付款行 author:dezai Select rownum seq_id, aip.invoice_payment_id, aip.accounting_event_id, aip.accounting_date, aip.check_id, aip.amount aip_amount, ac.check_number, ac.check_date, ac.amount ac_amount, hou.NAME org_name FROM ap_invoice_payments_all aip, ap_checks_all ac, ap_system_p

18、arameters_all asp, hr_operating_units hou Where aip.posted_flag IN ('N', 'S' /* AND aip.accounting_date BETWEEN g_period_start AND g_period_end*/ AND _id = _id /* AND asp.set_of_books_id = g_set_of_books_id*/ AND ac.check_id = aip.check_id AND nvl(asp.when_to_account_pm

19、t, 'ALWAYS' = 'ALWAYS' AND _id = anization_id -6查询用户刚刚执行的SQL author:dezai Select sql_text from v$sql orDER BY first_load_time DESC; -7查看数据库对象 author:dezai Select owner, object_type, status, COUNT(* count# FROM all_objects GROUP BY owner, object_type, status; -8查看哪些用户连接

20、author:dezai select s.osuser os_user_name, decode(sign(48 - command, 1, to_char(command, 'Action Code #' | to_char(command action, gram oracle_process, status session_status, s.terminal terminal, gram program, s.username user_name, s.fixed_table_sequence activity_meter, ''

21、query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER' order by s.username, s.osuser - 9最耗费CPU资源的TOP 25 SQLs author:dezai select * from ( select rank( over(order by buffer_gets desc as rank_bufgets ,to_ch

22、ar(100 * ratio_to_report(buffer_gets over(, '999.99' pct_bufgets ,sql_text from v$sqlarea where rownum<26 ; -10查询所有的SQL Statment author:dezai Select sql_text FROM v$sqltext a Where a.hash_value = (Select sql_hash_value FROM v$session b Where b.SID = '&sid' orDER BY piece ASC -

23、11查询SESSION_ID author:dezai select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",sess.serial#, pro.spid "OS ProID", pro.username "Oracle UsrName", gram "Program", sess.module "Module", sess.username, sess.o

24、suser, sess.machine, sess.terminal, gram, sess.type from v$process pro,v$session sess , v$sql sql where pro.addr=sess.paddr and sess.sql_address = sql.address(+ and sess.sid = &SESSION_ID -13查询ORACLE_ID select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid &quo

25、t;SessID",pro.spid "OS ProID", pro.username "Oracle UsrName", gram "Program", sess.module "Module", sess.username, sess.osuser, sess.machine, sess.terminal, gram, sess.type from v$process pro,v$session sess , v$sql sql where pro.ADDR=sess.p

26、addr and sess.sql_address = sql.address (+ - sql.address = sess.sql_address(+ ? and pro.pid= &ORACLE_PROCESS_ID - 14OS_PROCESS_ID select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",pro.spid "OS ProID", pro.username "Oracle UsrName&q

27、uot;, gram "Program", sess.module "Module", sess.username, sess.osuser, sess.machine, sess.terminal, gram, sess.type from v$process pro,v$session sess , v$sql sql where pro.ADDR=sess.paddr and sess.sql_address = sql.address (+ - sql.address = sess.sql_address(+ ? a

28、nd pro.spid = &OS_ID - 15SQL select sql.SQL_TEXT,sql.BUFFER_GETS , sql.CPU_TIME, sql.DISK_READS,sql.ELAPSED_TIME ,sql.RUNTIME_MEM,sql.EXECUTIONS ,sql.FETCHES,sql.PARSE_CALLS,sql.FIRST_LOAD_TIME,sql.MODULE,sql.OPTIMIZER_COST, sql.OPTIMIZER_MODE,sql.SORTS , sess.SID , sess.PROGRAM ,SESS.TYPE from

29、v$sql sql , v$session sess where sess.SQL_ADDRESS(+=sql.ADDRESS and upper(sql_text like '%&sql%' - 16查找请求IDRequest_ID Select cess_status_code,a.oracle_process_id,a.os_process_id,process_start_date FROM FND_CONCURRENT_PROCESSES A , FND_CONCURRENT_REQUESTS B Where B.CONTROLLING_MANAGE

30、R=A.CONCURRENT_PROCESS_ID AND B.REQUEST_ID='&req_id' select 'CONC',p.pid,request_id , p.username OS_NAME , p.spid , s.sid , s.username , s.serial# , s.sql_address ,u.user_name ,CONCURRENT_PROGRAM_NAME from v$process p , v$session s ,fnd_concurrent_requests cr ,fnd_user u,fnd_conc

31、urrent_programs cp where s.audsid=cr.oracle_session_id - 如果是oracle report, using the following sql and cr.REQUESTED_BY = u.user_id and s.paddr = p.addr and cp.CONCURRENT_PROGRAM_ID =cr.CONCURRENT_PROGRAM_ID and cr.request_id = &REQUESTID select request_id,STATUS_CODE,PHASE_CODE ,oracle_id,OS_PRO

32、CESS_ID, oracle_session_id,concurrent_program_id, printer from fnd_concurrent_requests where request_id = &REQUEST_ID -17查找后台正在运行的concurrent reporter SESSION select sid, serial#,osuser, process, machine, program,module from v$session where status='ACTIVE' AND SCHEMANAME<>'SYS&#

33、39; AND UPPER(PROGRAM LIKE 'AR60RUN%' Select r.request_id "Request ID", s.sid "Session ID" , s.serial# FROM applsys.fnd_concurrent_requests r, applsys.fnd_concurrent_queues_tl qt, applsys.fnd_concurrent_queues q, applsys.fnd_concurrent_processes p, v$session s Where r.con

34、trolling_manager=p.concurrent_process_id AND q.application_id=p.queue_application_id AND q.concurrent_queue_ ssion_userid, SYS_CONTEXT('USERENV','PROXY_USER' proxy_user, SYS_CONTEXT('USERENV','PROXY_USERID' proxy_userid, SYS_CONTEXT('USERENV','DB_DOMAIN' db_domain, SYS_CONTEXT('USERENV','DB_NAME' db_name, SYS_CONTEXT('USERENV','HOST' host, SYS_CONTEXT('USERENV','OS_USER' os_user, SYS_CONTEXT('USERENV','EXTERNAL_NAME' external_name, SYS_CONTEXT('USERENV',&