{"id":869,"date":"2016-08-05T14:21:12","date_gmt":"2016-08-05T06:21:12","guid":{"rendered":"http:\/\/www.oracle.name\/?p=869"},"modified":"2016-09-29T16:40:14","modified_gmt":"2016-09-29T08:40:14","slug":"%e8%81%8c%e8%b4%a3%e4%b8%8e%e9%85%8d%e7%bd%ae%e6%96%87%e4%bb%b6%e7%9a%84%e5%85%b3%e7%b3%bb%e4%bf%a1%e6%81%af","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=869","title":{"rendered":"\u804c\u8d23\u4e0e\u914d\u7f6e\u6587\u4ef6\u7684\u5173\u7cfb\u4fe1\u606f"},"content":{"rendered":"

\n\tselect fpv.profile_option_value, fpv.level_value, fst.responsibility_name
\n\t  from fnd_profile_options fpo,
\n\t       fnd_profile_option_values fpv,
\n\t       fnd_profile_options_tl tl,
\n\t       fnd_responsibility_tl fst,
\n\t       (SELECT 10001 level_id, '\u5730\u70b9' \u6587\u4ef6\u5b89\u5168\u6027
\n\t          FROM dual
\n\t        UNION
\n\t        SELECT 10002 level_id, '\u5e94\u7528\u4ea7\u54c1' \u6587\u4ef6\u5b89\u5168\u6027
\n\t          FROM dual
\n\t        UNION
\n\t        SELECT 10003 level_id, '\u8d23\u4efb' \u6587\u4ef6\u5b89\u5168\u6027
\n\t          FROM dual
\n\t        UNION
\n\t        SELECT 10004 level_id, '\u7528\u6237' \u6587\u4ef6\u5b89\u5168\u6027
\n\t          FROM dual
\n\t        UNION
\n\t        SELECT 10005 level_id, '\u670d\u52a1\u5668' \u6587\u4ef6\u5b89\u5168\u6027
\n\t          FROM dual
\n\t        UNION
\n\t        SELECT 10006 level_id, '\u7ec4\u7ec7' \u6587\u4ef6\u5b89\u5168\u6027 FROM dual) lv
\n\t where 1 = 1
\n\t   and fpv.profile_option_id = fpo.profile_option_id
\n\t   and tl.language = 'ZHS'
\n\t   and fst.language = 'ZHS'
\n\t   and tl.profile_option_name = fpo.profile_option_name
\n\t      –and tl.user_profile_option_name like '%MO%\u4e1a\u52a1\u5b9e\u4f53%'
\n\t      –and fpo.profile_option_name = 'DEFAULT_ORG_ID'
\n\t   and fpo.profile_option_name = 'CUX_INV_ACCESS_SINV_CQC'
\n\t   and fpv.level_value = fst.responsibility_id
\n\t   and lv.level_id = fpv.level_id
\n\t–and fpv.level_value = fnd_global.resp_id
\n\t–and fpv.level_id = 10003 –\u804c\u8d23\u5c42\n<\/p>\n

\n\t \n<\/p>\n

\n\t–\u521d\u59cb\u5316\u73af\u5883<\/span>

\n\t
\n\tBEGIN<\/span>

\n\t
\n\t  fnd_global.apps_initialize(user_id      => 0,<\/span>

\n\t
\n\t                             resp_id      => 50658,<\/span>

\n\t
\n\t                             resp_appl_id => 20003);<\/span>

\n\t
\n\t  mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 82);<\/span>

\n\t
\n\t  mo_global.init('SQLAP');<\/span>

\n\t
\n\tEND;<\/span>

\n\t
\n\t

\n\t
\n\t–\u4ee5\u4e0bSQL\u67e5\u8be2\u5f53\u524d\u914d\u7f6e\u7684MO\uff1a\u5b89\u5168\u6027\u914d\u7f6e\u6587\u4ef6<\/span>

\n\t
\n\tSELECT *<\/span>

\n\t
\n\t  FROM per_security_profiles psp<\/span>

\n\t
\n\t WHERE psp.security_profile_id =<\/span>

\n\t
\n\t \n<\/p>\n

\n\t       to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'));\n<\/p>\n

\n\t\u548cfnd_profile.value('ORG_ID') \u6765\u914d\u7f6e\n<\/p>\n

\n\t

\n\t
\n\t–\u4ee5\u4e0bSQL\u7528\u6765\u83b7\u53d6\u5f53\u524d\u5b89\u5168\u6027\u914d\u7f6e\u6587\u4ef6\u548c\u5f53\u524d\u7528\u6237\u53ef\u8bbf\u95ee\u7684OU<\/span>

\n\t
\n\tSELECT hou.organization_id, hou.name<\/span>

\n\t
\n\t  FROM hr_operating_units hou<\/span>

\n\t
\n\t WHERE hou.usable_flag IS NULL<\/span>

\n\t
\n\t   AND EXISTS<\/span>

\n\t
\n\t (SELECT 1<\/span>

\n\t
\n\t          FROM per_organization_list per<\/span>

\n\t
\n\t         WHERE per.organization_id = hou.organization_id<\/span>

\n\t
\n\t           AND per.security_profile_id =<\/span>

\n\t
\n\t               to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))<\/span>

\n\t
\n\t        UNION<\/span>

\n\t
\n\t        SELECT 1<\/span>

\n\t
\n\t          FROM dual<\/span>

\n\t
\n\t         WHERE to_number(fnd_profile.value('ORG_ID')) = hou.organization_id)<\/span>\n<\/p>\n

\n\t \n<\/p>\n

\n\t \n<\/p>\n

\n\t\u8868\u540d\uff1aper_organization_list per, hr_operating_units hr<\/font>\n<\/p>\n

\n\t\u6761\u4ef6\uff1aWHERE per.security_profile_id(+) = to_number(fnd_profile.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL'))
\n\tAND hr.organization_id = per.organization_id(+)
\n\tAND hr.usable_flag is null
\n\tAND (EXISTS (SELECT 1
\n\t           FROM per_organization_list per1
\n\t          WHERE hr.organization_id = per1.organization_id
\n\t            AND per1.security_profile_id =
\n\t                to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))) OR
\n\t        fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL') = 0)<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

select fpv.profile_option_value, fpv.level_value, fst.r … \u7ee7\u7eed\u9605\u8bfb\u804c\u8d23\u4e0e\u914d\u7f6e\u6587\u4ef6\u7684\u5173\u7cfb\u4fe1\u606f<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[31,1],"tags":[131,124],"_links":{"self":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/869"}],"collection":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=869"}],"version-history":[{"count":4,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/869\/revisions"}],"predecessor-version":[{"id":891,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/869\/revisions\/891"}],"wp:attachment":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}