ebs常用sql
--- 查询系统已定义OU，ORG 以及对于基本信息 ，有利于评估系统
SELECT DISTINCT HOU.BUSINESS_GROUP_ID,
HOU.BUSINESS_GROUP_NAME,
HOU.OPERATING_UNITS_ID ORG_ID,
OPERATING_UNIT_NAME,
DATE_FROM,
DATE_TO,
LEGAL_ENTITY_ID,
LEGAL_ENTITY_NAME,
LOCATION_NAME,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_ID,
OOD.ORGANIZATION_NAME,
OOD.USER_DEFINITION_ENABLE_DATE,
OOD.DISABLE_DATE,
OOD.SET_OF_BOOKS_ID,
OOD.INVENTORY_ENABLED_FLAG
FROM APPS.HRFV_OPERATING_UNITS HOU, --HR_OPERATING_UNITS;
apps.Org_organization_definitions OOD
WHERE HOU.OPERATING_UNITS_ID = OOD.OPERATING_UNIT
ORDER BY HOU.OPERATING_UNITS_ID, --ORG_ID
OOD.ORGANIZATION_CODE
-- GETactive organization structure of an EBS instance .V1.3last_updated@2012.7.20
SELECT DISTINCT --HOU.BUSINESS_GROUP_ID,
HOU.BUSINESS_GROUP_NAME,
HOU.OPERATING_UNITS_ID ORG_ID,
OPERATING_UNIT_NAME,
DATE_FROM,
DATE_TO,
-- LEGAL_ENTITY_ID,
LEGAL_ENTITY_NAME,
LOCATION_NAME,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_ID,
OOD.ORGANIZATION_NAME,
OOD.USER_DEFINITION_ENABLE_DATE org_USER_DEFIN_ENABLE_DATE,
--OOD.DISABLE_DATE,
--OOD.SET_OF_BOOKS_ID,
OOD.INVENTORY_ENABLED_FLAG,
master_org inv_master_org,
WMS_ENABLED_FLAG,
organization_paramete. calendar_code,
PRIMARY_COST_DUMMY PRIMARY_COST_METHOD,
(SELECT cost_group
FROM APPS.CST_COST_GROUPS_V
where cost_group_id =
organization_paramete.DEFAULT_COST_GROUP_ID) DEFAULT_cost_group_name,
COST_ORGANIZATION_ID,
sob.name SET_OF_BOOK_NAME,
sob.CURRENCY_CODE SOB_FUNCTIONAL_CURRENCY,
sob.CHART_OF_ACCOUNTS_NAME,
sob.LATEST_OPENED_PERIOD_NAME
FROM APPS.HRFV_OPERATING_UNITS HOU, --HR_OPERATING_UNITS;
apps.Org_organization_definitions OOD,
(select organization_id,
organization_code,
calendar_code,
master_org,
WMS_ENABLED_FLAG,
COST_ORGANIZATION_ID,
primary_cost_method,
PRIMARY_COST_DUMMY,
DEFAULT_COST_GROUP_ID
from apps.MTL_PARAMETERS_VIEW) organization_paramete,
(SELECT SET_OF_BOOKS_ID,
CURRENCY_CODE,
CHART_OF_ACCOUNTS_NAME,
NAME,
SHORT_NAME,
DESCRIPTION,
LATEST_OPENED_PERIOD_NAME,
ALLOW_INTERCOMPANY_POST_FLAG
FROM APPS.GL_SETS_OF_BOOKS_V) sob
WHERE HOU.OPERATING_UNITS_ID = OOD.OPERATING_UNIT
and OOD.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID
and organization_paramete.organization_id = OOD.ORGANIZATION_ID
and NVL(DATE_TO,SYSDATE) >=sysdate
ORDER BY HOU.OPERATING_UNITS_ID, --ORG_ID
OOD.ORGANIZATION_CODE
---- user responsibility 查询用户指责 查询当前所有有效用户
select distinct users.user_id,
users.user_name,
users.description,
users.start_date user_start_date,
users.end_date user_end_date,
users.email_address,
resp.responsibility_name,
user_resp.start_date,
user_resp.end_date,
user_resp.last_update_date,
appl.application_short_name,
appl.application_name
from apps.fnd_user users,
apps.fnd_user_resp_groups_direct user_resp,
apps.FND_RESPONSIBILITY_VL resp,
apps.FND_APPLICATION_VL appl
where users.user_id = user_resp.user_id
and user_resp.responsibility_application_id =
resp.application_id
and user_resp.responsibility_id = resp.responsibility_id
andresp.application_id =appl.application_id
and users.user_name = '123'
--- 查询某个组织OU 有多少职责RESPONSIBILITY
SELECT DISTINCT --HOU.BUSINESS_GROUP_ID,
HOU.BUSINESS_GROUP_NAME,
HOU.OPERATING_UNITS_ID ORG_ID,
OPERATING_UNIT_NAME,
DATE_FROM,
DATE_TO,
-- LEGAL_ENTITY_ID,
LEGAL_ENTITY_NAME,
LOCATION_NAME,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_ID,
OOD.ORGANIZATION_NAME,
OOD.USER_DEFINITION_ENABLE_DATE org_USER_DEFIN_ENABLE_DATE,
--OOD.DISABLE_DATE,
--OOD.SET_OF_BOOKS_ID,
OOD.INVENTORY_ENABLED_FLAG,
master_org inv_master_org,
WMS_ENABLED_FLAG,
organization_paramete. calendar_code,
PRIMARY_COST_DUMMY PRIMARY_COST_METHOD,
(SELECT cost_group
FROM APPS.CST_COST_GROUPS_V
where cost_group_id =
organization_paramete.DEFAULT_COST_GROUP_ID) DEFAULT_cost_group_name,
COST_ORGANIZATION_ID,
sob.name SET_OF_BOOK_NAME,
sob.CURRENCY_CODE SOB_FUNCTIONAL_CURRENCY,
sob.CHART_OF_ACCOUNTS_NAME,
sob.LATEST_OPENED_PERIOD_NAME,
/* (SELECT distinct responsibility_name
FROM apps.FND_RESPONSIBILITY_TL
where responsibility_id = ou_resp.responsibility_id and rownum =1 ) responsiblity_Name*/
CASE
WHEN (select COUNT(responsiBIlity_NAME)
from FND_RESPONSIBILITY_VL
WHERE responsiBIlity_ID = ou_resp.responsiBIlity_ID) > 1 THEN
'Mutiple responsiBIlity_NAME for responsiBIlity_ID:' ||
responsiBIlity_ID
else
(select responsiBIlity_name
from FND_RESPONSIBILITY_VL
where RESPONSIBILITY_ID = ou_resp.RESPONSIBILITY_ID
AND RESPONSIBILITY_ID NOT IN
(select RESPONSIBILITY_ID
from apps.FND_RESPONSIBILITY_VL
group by responsiBIlity_ID
having count(responsiBIlity_NAME) > 1))
end responsiBIlity_name,
CASE
WHEN (select COUNT(responsiBIlity_NAME)
from FND_RESPONSIBILITY_VL
WHERE responsiBIlity_ID = ou_resp.responsiBIlity_ID) > 1 THEN
'Mutiple responsiBIlity_NAME for responsiBIlity_ID:' ||
responsiBIlity_ID
else
(select application_name || ':' ||
application_short_name
from FND_RESPONSIBILITY_VL RE, apps.FND_APPLICATION_VL appl
where RESPONSIBILITY_ID = ou_resp.RESPONSIBILITY_ID
AND RE.APPLICATION_ID = APPL.APPLICATION_ID
AND RESPONSIBILITY_ID NOT IN
(select RESPONSIBILITY_ID
from apps.FND_RESPONSIBILITY_VL
group by responsiBIlity_ID
having count(responsiBIlity_NAME) > 1))
end APPLICATION_name
FROM APPS.HRFV_OPERATING_UNITS HOU, --HR_OPERATING_UNITS;
apps.Org_organization_definitions OOD,
(select organization_id,
organization_code,
calendar_code,
master_org,
WMS_ENABLED_FLAG,
COST_ORGANIZATION_ID,
primary_cost_method,
PRIMARY_COST_DUMMY,
DEFAULT_COST_GROUP_ID
from apps.MTL_PARAMETERS_VIEW) organization_paramete,
(SELECT SET_OF_BOOKS_ID,
CURRENCY_CODE,
CHART_OF_ACCOUNTS_NAME,
NAME,
SHORT_NAME,
DESCRIPTION,
LATEST_OPENED_PERIOD_NAME,
ALLOW_INTERCOMPANY_POST_FLAG
FROM APPS.GL_SETS_OF_BOOKS_V) sob,
APPS.BIS_OPERATING_UNITS_V ou_resp
WHERE HOU.OPERATING_UNITS_ID = OOD.OPERATING_UNIT
and OOD.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID
and organization_paramete.organization_id = OOD.ORGANIZATION_ID
and NVL(DATE_TO, SYSDATE) >= sysdate
and ou_resp.id(+) = HOU.OPERATING_UNITS_ID
ORDER BY HOU.OPERATING_UNITS_ID, --ORG_ID
OOD.ORGANIZATION_CODE
页:
[1]