oracle ebs 生态圈,发几个 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

-- GET  active organization structure of an EBS instance .V1.3  last_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

and  resp.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