{"id":6929,"date":"2024-05-06T12:45:49","date_gmt":"2024-05-06T10:45:49","guid":{"rendered":"https:\/\/promatis.com\/ch\/accessing-ebs-apis-from-apex-hr_security\/"},"modified":"2024-05-24T12:54:54","modified_gmt":"2024-05-24T10:54:54","slug":"accessing-ebs-apis-from-apex-hr_security","status":"publish","type":"post","link":"https:\/\/promatis.com\/ch\/accessing-ebs-apis-from-apex-hr_security\/","title":{"rendered":"Accessing EBS APIs from APEX - HR_SECURITY"},"content":{"rendered":"

[et_pb_section fb_built=\"1\" custom_padding_last_edited=\"on|tablet\" disabled_on=\"off|off|off\" admin_label=\"Sektion\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_padding=\"5vh||5vh||true|false\" custom_padding_tablet=\"5vh||5vh||true|false\" custom_padding_phone=\"5vh||5vh||true|false\" locked=\"off\" global_colors_info=\"{}\" global_module=\"23\" theme_builder_area=\"post_content\"][et_pb_row column_structure=\"1_4,3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_margin=\"||0px||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"1_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_image src=\"https:\/\/promatis.com\/wp-content\/uploads\/2022\/07\/johannes-michler.png\" alt=\"Johannes Michler PROMATIS Horus Oracle\" title_text=\"johannes-michler\" _builder_version=\"4.20.2\" _module_preset=\"default\" width=\"90%\" custom_margin=\"0vh||0vh||true|false\" border_radii=\"on|516px|516px|516px|516px\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][\/et_pb_image][\/et_pb_column][et_pb_column type=\"3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text ul_type=\"square\" _builder_version=\"4.23.1\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" link_font=\"Open Sans||||on|||gcid-0becd5ff-19fc-4653-a221-c8c75771a987|\" link_font_size=\"22px\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_2_line_height=\"1.6em\" header_6_font_size=\"16px\" custom_margin=\"2vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22link_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n

Johannes Michler<\/a><\/div>\n

[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_2_line_height=\"1.6em\" header_6_font_size=\"16px\" custom_margin=\"1vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n


Executive Vice President<\/strong> \u2013\u00a0Head of Platforms\u00a0&\u00a0Development<\/p>\n

[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" text_font=\"Open Sans||||||||\" text_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" text_font_size=\"22px\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_2_line_height=\"1.6em\" header_6_font_size=\"16px\" custom_margin=\"5px||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22text_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/i><\/a><\/i><\/a><\/i><\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=\"1\" _builder_version=\"4.16\" _module_preset=\"default\" custom_padding=\"0vh||10vh||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_row use_custom_gutter=\"on\" _builder_version=\"4.17.3\" _module_preset=\"default\" custom_padding=\"0px||0px||true|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"4_4\" _builder_version=\"4.16\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text _builder_version=\"4.25.1\" _module_preset=\"default\" background_enable_color=\"off\" custom_padding=\"0px||0px||true|false\" inline_fonts=\"Times New Roman\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22background_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n

\"\"<\/p>\n

As you're probably aware, Oracle APEX is a great tool to extend Oracle E-Business Suite. When doing so recently for a customer where I wanted to provide a way to add lines to existing orders using the OE_ORDER_PUB API I ran into a very strange situation. While my own tests worked great, the business users were not able to use the extension and got quite obscure errors as follows:<\/p>\n

\n

The item specified is invalid or does not exist in the warehouse you specified. Please enter a valid item-warehouse combination.<\/p>\n<\/blockquote>\n

The very same test case worked for my own user.<\/p>\n

Let's have a look how we analyzed and finally solved that problem.<\/p>\n

Overview on extending EBS with APEX<\/h2>\n

Oracle provides a comprehensive whitepaper on how to use APEX to extend E-Business Suite: Extending Oracle E-Business Suite Release 12.2 using Oracle APEX<\/a><\/p>\n

\"\"<\/p>\n

Recommended Database Configuration (as of mentioned Oracle Whitepaper)<\/em><\/p>\n

The procedure suggests having a xx_apex_global package in the APPS schema that itself has an apps_initialize procedure that calls the E-Business Suite built-in function to initialize a session context for a given user, responsibility, and application.<\/p>\n

Finding the cause of the API error<\/h2>\n

When calling OE_ORDER_PUB after doing such a context initialization for some users produced an error (OE_INVALID_ITEM_WHSE) as follows:<\/p>\n

\n

The item specified is invalid or does not exist in the warehouse you specified. Please enter a valid item-warehouse combination.<\/p>\n<\/blockquote>\n

Analyzing this further gave, that there were no values in mtl_organizations - even though the context was properly initialized.<\/p>\n

create or replace PROCEDURE APPS.xxis_mtl_param_test (p_user_id NUMBER)\nAUTHID definer\nAS\n  l_tmp VARCHAR2(2000);\nBEGIN\n  dbms_output.put_line('starting; DB-User is:' || user);\n  fnd_global.apps_initialize(p_user_id , 52203, 660);\n  dbms_output.put_line('user is now: ' || fnd_global.user_id);\n\n\n  SELECT\n    LISTAGG(hao.organization_code, ';')\n  INTO l_tmp\n  FROM\n    mtl_organizations hao;\n\n  dbms_output.put_line('mtl_organizations: ' || l_tmp);\nEND;\n<\/pre>\n
To make things worse, the values that we got when calling xxis_mtl_param_test where different for the following situations:<\/p>\n
    \n
  • when called in a SQL Developer session as the APPS database user initializing for a user IT_GUY, that is allowed to access all inventory organizations according to its security profile, everything is working fine.<\/li>\n
  • when called in a APEX session, with both a real application or from the APEX Workbench (so technically as the APEX_PUBLIC_USER) initializing for a user IT_GUY, that is allowed to access all inventory organizations according to its security profile, everything is working fine.<\/li>\n
  • when called in a SQL Developer session as the APPS database user initializing for a user BUSINESS_GUY, that is allowed to access only specific inventory organizations according to its security profile, everything is working fine. The list of accessible inventory organizations is filtered as expected:
    \"\"
    Accessing mtl_organizations as the Business User connected through APPS: Working<\/em><\/li>\n
  • when called in a APEX session, with both a real application or from the APEX Workbench (so technically as the APEX_PUBLIC_USER) initializing for a user BUSINESS_GUY, that is allowed to access only specific inventory organizations according to its security profile, no records are shown up in mtl_organizations.
    \"\"
    Accessing mtl_organizations as the Business User connected through APEX_PUBLIC_USER: No accessible mtl_organizations<\/em><\/li>\n<\/ul>\n
    Debugging the code executed further brought up the HR_SECURITY package as the problematic part, since the mtl_parameters query contains a predicate as follows:<\/p>\n
    hr_security.show_record('HR_ALL_ORGANIZATION_UNITS',organization_id)='TRUE'<\/pre>\n
    \u00a0Looking into the package I found the following code as the root cause:<\/p>\n
     CURSOR csr_get_schema_mode
    IS
    SELECT DECODE(READ_ONLY_FLAG,'U', 'Y'
    ,'M', 'Y'
    ,'K', 'Y'
    ,'N') schema_mode,
    DECODE(SUBSTRB(USERENV('CLIENT_INFO'), 1, 1),' ', NULL,
    SUBSTRB(USERENV('CLIENT_INFO'),1, 10))
    FROM FND_ORACLE_USERID
    WHERE ORACLE_USERNAME = user;<\/pre>\n
    That package obviously goes for the connecting user and classifies that into \"real\/good\" users (such as the APPS user) and read-only-users (such as any custom schema - be it registered with adsplice or not.<\/p>\n
    Doing a quick hack of that decode (e.g. another decode for APEX_PUBLIC_USER to have schema_mode='Y') solved the above test case and made the OE_ORDER_PUB API call working as well.<\/p>\n
    Obviously, it is not a good idea to modify an Oracle seeded package. So, I checked (with the great help of Oracle Support) for a better solution.<\/p>\n
    Workaround for Bug 16914457<\/h2>\n
    A temporary better workaround was to run the following statement:<\/p>\n
    exec fnd_oracle_user_pkg.load_row('APEX_PUBLIC_USER', 'CUSTOM', 'INVALID', 'Apex Schema Description', 'N', 'K');
    commit;<\/pre>\n
    That changed the APEX_PUBLIC_USER (through which ORDS connects for APEX applications in my case) to be classified as 'K' which had no negative side effects in our tests.<\/p>\n
    Summary<\/h2>\n
    The above workaround allows accessing OE_ORDER_PUB and other APIs form apex without issues. I hope that there will be an official certification of the workaround through My Oracle Support soon.<\/p>\n
    [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
    As you're probably aware, Oracle APEX is a great tool to extend Oracle E-Business Suite. When doing so recently for a customer where I wanted to provide a way to add lines to existing orders using the OE_ORDER_PUB API I ran into a very strange situation.<\/p>\n","protected":false},"author":2,"featured_media":1243,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[23],"tags":[564,88,85],"dipi_cpt_category":[],"class_list":["post-6929","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-techblog","tag-apex","tag-e-business-suite","tag-oracle"],"_links":{"self":[{"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/posts\/6929"}],"collection":[{"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/comments?post=6929"}],"version-history":[{"count":0,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/posts\/6929\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/media\/1243"}],"wp:attachment":[{"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/media?parent=6929"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/categories?post=6929"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/tags?post=6929"},{"taxonomy":"dipi_cpt_category","embeddable":true,"href":"https:\/\/promatis.com\/ch\/wp-json\/wp\/v2\/dipi_cpt_category?post=6929"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}