PO常用SQL语言.docVIP

PO常用SQL语言 Oracle EBS:常用SQL语句 /* Formatted on 2009-11-03 23:10:34 (QP5 v5.114.809.3010) */ --1查找系统用户基本信息 author:dezai Select USER_ID, USERNAME, DESCRIPTION, EMPLOYEDD_ID, PERSON_PARTY_ID FROM Fnd_User; --2查找供应商基本信息(供应商,供应商地点,联系人) author:dezai Select pv.vendor_id vendor_id, PVSA.VENDOR_SITE_ID vendor_site_id, pv.vendor_name vendor_name, PVSA.VENDOR_SITE_CODE vendor_site_code, PVSA.ORG_ID org_id, pv.segment1 vendor_code, pvc.area_code || pvc.phone vendor_phone, PVC.FAX_AREA_CODE || pvc.fax vendor_fax, PVSA.TERMS_ID terms_id, PVSA.VAT_CODE vat_code, PVC.LAST_NAME || PVC.MIDDLE_NAME || PVC.FIRST_NAME contact_man FROM po_vendors pv, po_vendor_sites_all pvsa, po_vendor_contacts pvc Where pv.vendor_id = pvsa.vendor_id AND pvsa.vendor_site_id = pvc.vendor_site_id orDER BY org_Id DESC; --3查找所有的interface表 author:dezai Select * FROM dba_objects db Where db.object_type = TABLE AND db.object_name LIKE %INTERFACE%; --4查找对应模块的interface表 author:dezai Select * FROM dba_objects db Where db.object_type = TABLE AND db.object_name LIKE %INTERFACE% AND owner LIKE PO; --5查找用户当前的状态 author:dezai Select SUBSTR (V$SESSION.USERNAME, 1, 8) USERNAME, V$SESSION.OSUSER OSUSER, -- DECODE(V$SESSION.SERVER,DEDICATED,D,SHARED,S,O) SERVER, V$SQLAREA.DISK_READS DISK_READS, V$SQLAREA.BUFFER_GETS BUFFER_GETS, SUBSTR (V$SESSION.LOCKWAIT, 1, 10) LOCKWAIT, V$SESSION.PROCESS PID, V$SESSION_WAIT.EVENT EVENT, V$SQLAREA.SQL_TEXT SQL FROM V$SESSION_WAIT, V$SQLAREA, V$SESSION Where V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND V$SESSION.SID = V$SESSION_WAIT.SID(+) AND V$SESSION.STA