EBS常用SQL

77 阅读 0 评论 51 点赞

我是靠谱客的博主想人陪招牌，最近开发中收集的这篇文章主要介绍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';