Oracle EBS: EBS常用SQL

最近一直在忙于做EBS的二次开发，离不开拼拼湊湊的SQL语句. 这些都是大家常用的一些SQL，与大家分享,欢迎指正.

--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';