查询EBS配置文件选项设置的sql脚本
近日根据eTRM梳理了EBS配置文件涉及的各种表，做了一个可以按照配置文件选项名称、地点、应用、职责、服务器、组织、用户查询各级别上配置文件选项设置的sql脚本，希望对大家有所帮助。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
with PROFILE_OPTION_LEVEL as ( --定义配置文件 [层级] 子查询 select 10001 Level_Id, 'Site' Level_Name from dual union select 10002 Level_Id, 'Application' Level_Name from dual union select 10003 Level_Id, 'Responsibility' Level_Name from dual union select 10004 Level_Id, 'User' Level_Name from dual union select 10005 Level_Id, 'Server' Level_Name from dual union select 10006 Level_Id, 'Organization' Level_Name from dual), PROFILE_OPTION_LEVEL_VALUE as ( --定义配置文件 [层级值] 子查询 select '10002' as Level_Value_Type, --应用 Application_Id as Level_Value_Id, Application_Name as Level_Value_Name, 0 as Level_Value_Application_Id from FND_APPLICATION_TL where Language='ZHS' union select '10003' as Level_Value_Type, --职责 Responsibility_Id as Level_Value_Id, Responsibility_Name as Level_Value_Name, Application_Id as Level_Value_Application_Id from FND_RESPONSIBILITY_TL where Language='ZHS' union select '10004' as Level_Value_Type, --用户 User_Id as Level_Value_Id, User_Name as Level_Value_Name, 0 as Level_Value_Application_Id from FND_USER union select '10005' as Level_Value_Type, --服务器 Node_Id as Level_Value_Id, Node_Name as Level_Value_Name, 0 as Level_Value_Application_Id from FND_NODES union select '10006' as Level_Value_Type, --组织 Organization_Id as Level_Value_Id, Name as Level_Value_Name, 0 as Level_Value_Application_Id from HR_OPERATING_UNITS ) select po.Profile_Option_Id,po.Profile_Option_Name,po.User_Changeable_Flag,po.User_Update_Allowed_Flag, pot.User_Profile_Option_Name,pot.Description, pol.Level_Id,pol.Level_Name, pov.Profile_Option_Value, --配置文件选项值设定 --pov.Level_Value,pov.Level_Value_Application_Id, polv.Level_Value_Name from FND_PROFILE_OPTIONS po, --User profile options FND_PROFILE_OPTIONS_TL pot, --Translations for FND_PROFILE_OPTIONS FND_PROFILE_OPTION_VALUES pov, --Values of user profile options defined at different profile levels PROFILE_OPTION_LEVEL pol, PROFILE_OPTION_LEVEL_VALUE polv where po.Profile_Option_Name = pot.Profile_Option_Name and pot.Language = 'ZHS' and po.Application_Id = pov.Application_Id and po.Profile_Option_Id = pov.Profile_Option_Id and pov.Level_Id = pol.Level_Id and (pov.Level_Id = polv.Level_Value_Type(+) and NVL(pov.Level_Value,0) = polv.Level_Value_Id(+) and NVL(pov.Level_Value_Application_Id,0) = polv.Level_Value_Application_Id(+)) --按 [配置文件选项名称] 查询 --and pot.User_Profile_Option_Name = 'MO: Operating Unit' --and pov.Profile_Option_Value = (select Organization_Id from Hr_Organization_Units_v where Name = 'Vision China') --按 [地点] 查询 --and pol.Level_Name = 'Site' --按 [应用] 查询 --and pol.Level_Name = 'Application' --and polv.Level_Value_Name = N'订单分录' --按 [职责] 查询 --and pol.Level_Name = 'Responsibility' --and polv.Level_Value_Name = 'Receivables, Vision China' --按 [服务器] 查询 --and pol.Level_Name = 'Server' --and polv.Level_Value_Name = 'EBS' --按 [组织] 查询 --and pol.Level_Name = 'Organization' --and polv.Level_Value_Name = 'Vision Operations' --按 [用户] 查询 and pol.Level_Name = 'User' and polv.Level_Value_Name = 'SYSADMIN' order by po.Profile_Option_Id,pol.Level_Id,pov.Level_Value_Application_Id,pov.Level_Value; |
