responsibility 查询用户,OU 职责

来源：互联网发布：淘宝图文编辑教程编辑：程序博客网时间：2024/04/28 04:48

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

原文http://www.itpub.net/thread-1683211-1-1.html