Oracle EBS: EBS常用SQL

最近一直在忙于做EBS的二次开发，离不开拼拼湊湊的SQL语句. 这些都是大家常用的一些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.STATUS = 'ACTIVE'
           AND V$SESSION_WAIT.EVENT != 'client message'
orDER BY   V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;

--6查找用户的职责 author:dezai
select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
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.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

--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   mtl_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,模块的简称代码 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_logins a,
         v$session b,
         v$process c,
         apps.fnd_user d
Where       b.paddr = c.addr
         AND a.pid = c.pid
         AND a.spid = b.process
         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.SOURCE_LANG
  FROM   mtl_units_of_measure muom;

--14OU 库存组织与子库存 author:dezai

Select   hou.organization_id ou_org_id,
         hou.NAME ou_name,
         ood.organization_id org_org_id,
         ood.organization_code org_org_code,
         msi.secondary_inventory_name,
         msi.description
  FROM   hr_organization_information hoi,
         hr_organization_units hou,
         org_organization_definitions ood,
         mtl_secondary_inventories msi
Where       hoi.org_information1 = 'OPERATING_UNIT'
         AND hoi.organization_id = hou.organization_id
         AND ood.operating_unit = hoi.organization_id
         AND ood.organization_id = msi.organization_id;


--15查询库存物料现有量 author:dezai
  Select   ms.*
    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,
         sob.name,
         lo.SESSION_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_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,
         vs.process
  FROM   fnd_logins fl,
         fnd_user fu,
         v$locked_object 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 = fl.process_spid(+)
         AND vp.pid = fl.pid(+)
         AND fl.user_id = fu.user_id(+)
         --AND c.object_name LIKE '%' || UPPER('&tab_name_leaveblank4all') || '%'
         AND NVL (vs.status, 'XX') != 'KILLED';


一点积累,与大家分享.请多多指教。

上一篇: Oracle EBS: 查看表单名,表名,字段名等数据源的方法
下一篇: [译]Oracle API Profile 的应用
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: EBS常用SQLEBS常用SQL EBSSQLEBSSQL EBSEBS
相关日志: