Introduction

For quite some time I've been using various pl/sql procedures on the database to submit Concurrent Requests in Oracle eBusiness Suite from various places in APEX Applications I've developed over the years.

Recently I decided it was time to start recycling and reusing the code and create some plugins to do this. Plugins allow you to extend APEX Applications with custom functionality that is not available natively in the platform.

After watching the following great in-depth video from a few years back during the 1st COVID lockdown by Stefan Dobre , I thought I'd give it a go.

Stefan had shown us how to create items and region plug-ins but I needed to create a process plugin. Which is even simpler in that there are no files or javascript or css to worry about just good old pl/sql.

Where to start?

A quick look at the existing procedures made it obvious that to call them I needed several arguments. The Program Short Name, the User and Responsibility IDs of the user, possibly an Org ID and up to 100 parameters. The 100 parameters were going to be hard to handle as a plugin only provides for up to 15 Custom Attributes in total, (EDIT: In APEX 23.1 this has been increased to 25!) but from 25 years of experience I had only seen more than 20 parameters used once or twice so I would cap the code at 25 for now! These would be handled as a single Custom Attribute, by concatenating them into a pipe-separated list that would later be split using the APEX_STRING.SPLIT function. This would allow the APEX developer to use the Plug-in and concatenate them as needed, using a simple text string or returning a value from a SQL expression. The others would be stored as separate attributes.

With the custom attributes decided upon it was time to start defining the plugin, and writing a bit of code to bring it all together.

Defining the Plug-in

The Plug-ins page can be found under the Other Components section of the Shared Components page. Simply click on Create to start the new Plug-in wizard, which is one of the shortest wizards ever! It simply asks if you are creating from scratch or Copying from an existing one. If you are creating from scratch, and I was, then it takes you to the Plug-in form, wizard finished!

Here we give the Plug-in a Name and an Internal Name, Oracle recommends making the internal name unique by appending your company domain to the start of it, just in case someone outside your organization wants to use it or you decide to sell it and retire!

Having installed the FOEX Chrome Add-in as recommended by Stefan in the video I was keen to use it to help with the boilerplate code. Alas, the Add-in errors, looks like the FOEX website has gone offline since Oracle acquired them in August 2022. Not to worry the FOS Plugin app is still available to download at FOS Plug-ins - Home. Once you have downloaded and installed the sample app in your local APEX workspace you can look through the wonderful plugins they have provided and use a suitable one to take your boilerplate from.

Boilerplate

For a Process Plug-in, I found the following was sufficient.

function function_name
  ( p_process in apex_plugin.t_process
  , p_plugin  in apex_plugin.t_plugin 
  )
return apex_plugin.t_process_exec_result
as
    --attributes
   l_param1     p_process.attribute_01%type := p_process.attribute_01;  
   l_param2     p_process.attribute_02%type := p_process.attribute_02;  
   --return variable
   l_result     apex_plugin.t_process_exec_result;

   -- Do your things here

   -- Return a success message in return variable
   l_result.success_message := 'Success Message Here!';

    return l_result;
exception
    when others then
        raise;
end function_name;

The Source code for the plugin can have as many or as few functions as you require, however only one will be called by a Process Plugin, and this is entered in the Execution Function Name field.

The function called by the Process Plugin will be called using the same two parameters, p_process and p_plugin. P_process has a type of apex_plugin.t_process and this contains any custom attributes set at the process level when the plug-in is called, and p_plugin has a type of apex_plugin.t_plugin, which contains any custom attributes defined at the Shared Component level for the plug-in. Both of these among other things contain the maximum of 15 custom attributes (EDIT: 25 with 23.1) you can define in the Process when configuring to use the Plug-in, which we'll look at shortly.

The return value from the plug-in function must be of the apex_plugin.t_process_exec_result type. Whatever text you return in the success_message field will be displayed to the user once the process completes.

Plug-in Code

Here is the completed Plug-in source code.

function submit_request
  ( p_process in apex_plugin.t_process
  , p_plugin  in apex_plugin.t_plugin 
  )
return apex_plugin.t_process_exec_result
as
    --attributes
    l_program    p_process.attribute_01%type := p_process.attribute_01; 
    l_parameters p_process.attribute_02%type := p_process.attribute_02;
    l_user_id    p_process.attribute_03%type := p_process.attribute_03;
    l_resp_id    p_process.attribute_04%type := p_process.attribute_04;
    l_org_id     p_process.attribute_05%type := p_process.attribute_05;
    --return variable
    l_result            apex_plugin.t_process_exec_result;
    -- Other local variables
    l_error_count       number := 0;
    l_request_id        number;

begin
    -- debug
    if apex_application.g_debug and substr(:DEBUG,6) >= 6 
    then
        apex_plugin_util.debug_process
          ( p_plugin  => p_plugin
          , p_process => p_process
          );
    end if;

    apps.xx_apex_plugins_pkg.submit_conc_request(
            p_user_id           => l_user_id,
            p_responsibility_id => l_resp_id,
            p_org_id            => l_org_id,
            p_program           => l_program,
            p_parameters        => l_parameters,
            x_request_id        => l_request_id);

    -- Error Handling 
    if l_request_id < 0
    then
        raise_application_error(-20001, 
        'Submit_Request_plugin - Error, Contact Suppport');
    else    
        l_result.success_message := 'Request ID:'||l_request_id||' Submitted!';
    end if;
    return l_result;
exception
    when others then
        raise;
end submit_request;

The function assigns the custom attributes to some local variables with some user-friendly names, this is optional but helps when reviewing the code.

A call to set up some debugging and output the arguments, if required.

Then the main call to a procedure on the database, passing the parameters and returning the Concurrent Request ID.

If successful the returned request ID would be greater than zero, finally, it outputs an error or success message.

Defining Custom Attributes

Custom Attributes will be available to the APEX developer to use when using a plug-in. So take some time designing the Attributes, and selecting the correct Scope, Application scope means the Attribute is set once per application under the Shared Components page, this is ideal for storing application-wide settings, Component scope means the attribute will be set when the APEX developer calls the plugin. Also set up the useful help text and default or sample data. Set the required status, maximum length and Text Case to help users enter the right values.

Provide a default value if there is a suitable default.

Enter Help Text, HTML formatting can be applied, and an example.

The help text will look like this to the user when they define the plug-in use.

In this example, there were 5 attributes defined.

Database Code

The Database procedure was as follows. This was under the apps schema and a grant execute command was used to grant permission to the APEX workspace schema. All of the code could have been stored on the database and referenced in the Execution Function Name field, in this instance, I decided to have some code in the plug-in source so the basics were visible from the APEX developer's view without having to dig into the database too much.

PROCEDURE submit_conc_request(
    p_user_id           IN NUMBER,
    p_responsibility_id IN NUMBER,
    p_org_id            IN NUMBER,
    p_program           IN VARCHAR2,
    p_parameters        IN VARCHAR2,
    x_request_id        OUT NUMBER )  is

    type paramsarray IS VARRAY(25) OF VARCHAR2(240);

    L_APPLICATION    fnd_application.application_short_name%type;
    l_application_id number;
    x                number;
    params           paramsarray := paramsarray();

    cursor para is SELECT COLUMN_VALUE val
                   FROM apex_string.split(p_parameters,'|');

begin
    -- Get Resp App id
    select application_id 
    into l_application_id
    from fnd_responsibility
    where responsibility_id = p_responsibility_id;

    -- Initialise Session
    fnd_global.apps_initialize (p_user_id,
                                p_responsibility_id,
                                l_application_id);

    mo_global.set_policy_context('S', p_org_id);

    -- Get Program App Id
    select application_short_name 
    into L_APPLICATION
    from fnd_application a, fnd_concurrent_programs p
    where concurrent_program_name = P_PROGRAM
    and p.application_id = a.application_id;

    x := 0;
    -- Get parameters
    for p in para loop
    x:=x+1;
    params.EXTEND;
    params(x) := p.val;
    end loop;
    -- Populate Null parameters
    while x < 25 loop
    x:=x+1;
    params.EXTEND;
    params(x) := null;
    end loop;


    x_request_id   := fnd_request.submit_request(
            application   => l_application,
            program       => p_program,
            sub_request   => false,
            argument1     => params(1),
            argument2     => params(2),
            argument3     => params(3),
            argument4     => params(4),
            argument5     => params(5),
            argument6     => params(6),
            argument7     => params(7),
            argument8     => params(8),
            argument9     => params(9),
            argument10    => params(10),
            argument11    => params(11),
            argument12    => params(12),
            argument13    => params(13),
            argument14    => params(14),
            argument15    => params(15),
            argument16    => params(16),
            argument17    => params(17),
            argument18    => params(18),
            argument19    => params(19),
            argument20    => params(20),
            argument21    => params(21),
            argument22    => params(22),
            argument23    => params(23),
            argument24    => params(24),
            argument25    => params(25)
        );

        IF
            x_request_id = 0
        THEN
            ROLLBACK;
        ELSE
            COMMIT;
        END IF;

    end submit_conc_request;

A quick run-through of the code above for you non-EBS devs, call the apps_initialize procedure to create an EBS session, this is required if Submitting a Concurrent Request.

Another call to set_policy_context to the Org ID the user was accessing. As part of the existing APEX integrations with EBS, we implemented the code documented in the Oracle White Paper ( Extending Oracle E-Business Suite Release 12.1.3 and Above Using Oracle Application Express (APEX) (Doc ID 1306563.1)). And also the wonderful code from Sylvain Martel and the Insum team (Top 3 Use Cases of E-Business Suite Extensions with Oracle APEX (insum.ca ))This allows our users to sign in to Oracle Applications when opening an APEX application and store their user ID and Responsibilities etc as Application items which we will then pass to the Plug-in.

Fetch the Application ID into a variable as it's required later.

Split the p_parameters argument using APEX_STRING.SPLIT and assign them into a VARRAY, then set the remaining possible parameters to null.

The final call is to the submit_request function which if it returns a number greater than zero has successfully submitted a concurrent request. Exception handling was left out of the sample code for brevity.

Enough coding, it's time to go low code and use the plugin.

Adding the Plugin to a page

To keep it simple a blank page was added to an APEX app with a single region and button. A new process was added using the Type "Submit Concurrent Request [Plug-In]" to be called by the button. Once the type has been selected you will see all the Custom Attributes appear in the Settings region.

In this example, the Payables Open Interface Import program is being requested using the program's Short Name of APXIIMPT.

The Parameters are a concatenated list using pipe separators of the parameters required for the program to work.

The User, Responsibility and Org IDs are all Applications Items.

Running the Plug-In

When the Page is run the button and a bit of boilerplate text are shown.

Once the button is clicked a message displays the Concurrent Request ID created.

In EBS the concurrent request can be seen by the Sysadmin or the user who submitted the request.

Summing Up

Make use of APEX plug-ins to recycle and reuse functionality across different pages or apps. If you are developing extensions for EBS in APEX, use Plug-Ins to build in your API calls and make them reusable. Keep as much code in the database and not in APEX for performance reasons.

Where to next?

Check out Plug-ins (apex.world ) for a library of Plug-Ins, some open source some with commercial support.

Oracle also has its own Plug-in repository at apex/plugins at 22.2 · oracle/apex · GitHub.

Please subscribe to this blog to catch the latest posts. The next one will be on integrating Digital Signatures with APEX using Docusign.

Creating an APEX Process Plugin

Table of contents