Granular Access for EBS-APEX Extension

In versions 5.1 and 5.2 of the “Extending Oracle E-Business Suite Release 12.2 Using Oracle APEX” technical paper, the EBS team has provided a streamlined patches to ease the integration between Oracle APEX and E-Business Suite.

The patches allow to have great functionalities that can be summarized as follows:

• Single Sign-On based on the current EBS User’s Session.

• Embeddable APEX Application inside Oracle EBS or Standalone if needed.

• EBS integrated screens to manage the links between the EBS functions and APEX Applications in certain Workspaces and their target Pages.

• Oracle APEX Schema registration with EBS.

• Registering APEX_PUBLIC_USER to allow certain EBS APIs to work seamlessly with Oracle APEX.

Those were great additions that made the integration and writing extensions for EBS using Oracle APEX easier.

While the current patches will insure that the logged in APEX Users are having access to the Responsibility and Function that they navigated from EBS to APEX respected in the APEX Application, the current technical paper doesn’t include yet, the ability to have a granular authorization access inside an APEX application using arbitrary EBS Responsibilities and Functions.

In this blog post I provide such function. The function takes a couple of parameters. The first one will check if the current APEX user is having a certain EBS Responsibility. The other will check if the current APEX user is having access to a certain EBS Function.

Below is the Spec for the “has_responsibility_or_function” function.

create or replace package FND_APEX AUTHID DEFINER as

FUNCTION authentication RETURN BOOLEAN;

FUNCTION authorization RETURN BOOLEAN;

FUNCTION has_responsibility_or_function(p_user_name      IN VARCHAR2,
    p_resp_key       IN VARCHAR2 DEFAULT NULL,
    p_function_name  IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN;

end FND_APEX;

The implementation for the function could look something like the following code:

   /*
      SHLAYEL: This function tests a responsibility or function for a logged APEX user
   */

   FUNCTION has_responsibility_or_function (
    p_user_name      IN VARCHAR2,
    p_resp_key       IN VARCHAR2 DEFAULT NULL,
    p_function_name  IN VARCHAR2 DEFAULT NULL
   ) RETURN BOOLEAN IS
      l_count INTEGER := 0;
   BEGIN
      -- Check if the user has the specified responsibility
      IF p_resp_key IS NOT NULL THEN
         SELECT COUNT(*)
         INTO l_count
         FROM fnd_user u
         JOIN fnd_user_resp_groups g
               ON u.user_id = g.user_id
         JOIN fnd_responsibility r
               ON g.responsibility_id = r.responsibility_id
         WHERE u.user_name = UPPER(p_user_name)
            AND r.responsibility_key = UPPER(p_resp_key)
            AND SYSDATE BETWEEN g.start_date AND NVL(g.end_date, SYSDATE + 1);

         IF l_count > 0 THEN
               RETURN TRUE;
         END IF;
      END IF;

      -- Check if the user has the specified function
      IF p_function_name IS NOT NULL THEN
        IF fnd_function.test(p_function_name) THEN
            RETURN TRUE;
        END IF;
      END IF;

      RETURN FALSE;

   EXCEPTION
      WHEN OTHERS THEN
         RETURN FALSE;
   END;

Now it is much easier to fine tune the authorization for a page, region, item, or properties in Oracle APEX based on an EBS responsibility or function.

For instance, I could use the following code to make a Page Item “Read Only” if I do not have the “XX_APEX_RESPONSIBILITY” assigned to the current APEX user.

-- Check if user has a responsibility
IF apps.fnd_apex.has_responsibility_or_function(:APP_USER, p_resp_key => 'XX_APEX_RESP') THEN
    RETURN FALSE;
ELSE 
    RETURN TRUE;
END IF;

I am currently working closely with the EBS team to have this as part of the patch and make it included in the FND_APEX package.

I am also working on an Authorization Plugin that will be available in the near future that will have even more cooler features.

Stay tuned, and Happy APEXing!