Accessing EBS APIs from APEX - HR_SECURITY

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:

• The item specified is invalid or does not exist in the warehouse you specified. Please enter a valid item-warehouse combination.

The very same test case worked for my own user.

Let's have a look how we analyzed and finally solved that problem.

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

Recommended Database Configuration (as of mentioned Oracle Whitepaper)

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.

When calling OE_ORDER_PUB after doing such a context initialization for some users produced an error (OE_INVALID_ITEM_WHSE) as follows:

• The item specified is invalid or does not exist in the warehouse you specified. Please enter a valid item-warehouse combination.

Analyzing this further gave, that there were no values in mtl_organizations - even though the context was properly initialized.

create or replace PROCEDURE APPS.xxis_mtl_param_test (p_user_id NUMBER)
AUTHID definer
AS
  l_tmp VARCHAR2(2000);
BEGIN
  dbms_output.put_line('starting; DB-User is:' || user);
  fnd_global.apps_initialize(p_user_id , 52203, 660);
  dbms_output.put_line('user is now: ' || fnd_global.user_id);


  SELECT
    LISTAGG(hao.organization_code, ';')
  INTO l_tmp
  FROM
    mtl_organizations hao;

  dbms_output.put_line('mtl_organizations: ' || l_tmp);
END;

To make things worse, the values that we got when calling xxis_mtl_param_test where different for the following situations:

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

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

• 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

• 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

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:

hr_security.show_record('HR_ALL_ORGANIZATION_UNITS',organization_id)='TRUE'

Looking into the package I found the following code as the root cause:

 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;

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.

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.

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.

A temporary better workaround was to run the following statement:

exec fnd_oracle_user_pkg.load_row('APEX_PUBLIC_USER', 'CUSTOM', 'INVALID', 'Apex Schema Description', 'N', 'K');
commit;

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.

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.