EBS常用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';