Turn SQL into an EBS Excel report

The new and free version of my Excel Out program allows creating Excel reports by pasting a query in the attachment of the concurrent request.

Installation

Install it by downloading this file Excel_Out.zip

Unzip all files in your Oracle Home, either on your local machine or the server and run eo_install.sql from SQL Plus. 

If you prefer using SQLDeveloper, TOAD or similar tool you can directly paste and execute this script: EO_SQL_DEV.sql

Either of these scripts  will install the above 'Excel Out' program that you can run immediately from System Administrator. 

Before you run it the first time you must mount the concurrent request output directory on the Database Server and point the program to it by setting up the profile option "Concurrent Requests Output DIR".

Most, but not all, of companies already have the directory with concurrent request outputs mounted on the database server. If your company does not then you will need to have your Linux admins do this for you. If your DB and application server are running on the same machine, you don't need the mount, but still need to set up the profile value. 

If you are not sure if it is mounted, follow steps below and it will become apparent. 

The physical location of the directory can be identified by running this simple statement:

SELECT OUTFILE_NAME FROM FND_CONCURRENT_REQUESTS where LAST_UPDATE_DATE>sysdate-1

Once you identified the output directory check if it is set up as a database directory. In my case this was:

select directory_name from all_directories 

where directory_path='/dev_oraapp/dev122/fs_ne/inst/DEV122_apdev001/logs/appl/conc/out'

If not you will need to define it as a database directory using the CREATE DIRECTORY syntax. 

If the directory is already defined then most likely it is already mounted on the database server

Once the directory is defined you can set up the "Concurrent Requests Output DIR" profile option:

The PROD and test instances will most likely each need a different value for this profile. 

Now you can run the Excel Out report. 

If the output does not open, but instead you get a message that the output file could not be read that means the concurrent output directory is not mounted on the database server. 

Building a new report

To create a new report copy the Excel Out concurrent request with all of its parameters then click on the attachment icon and add a new SQL attachment with a new query:

DO NOT put a semicolon at the end of the query, this will fail the program. 

If your report needs parameters that are specified during runtime put them in the query using the p_parameterXX syntax. 

During the runtime each p_parameter value will be replaced with the actual value selected by the user in the parameter. 

For dates use syntax:

fnd_date.canonical_to_date(p_parameter01)

if you want to make a parameter optional use the nvl syntax

person_id=nvl(p_parameter03, person_id)

If the person_id is specified in the parameter the query will only return records for that particular person_id, but if the user does not provide a value this will evaluate to :

person_id=person_id

meaning the parameter will have no impact on the query, making it optional. 

Troubleshooting errors

If your report failed to execute, go the the request log, copy the query and try to execute directly in SQL. This should give you an idea of that the problem is. 

Never use a semicolon in the attachment as this will guarantee the report failure. 

How it works

The concurrent request grabs the query from the attachment and writes its output into an Excel file. If you want to have a specific naming convention for the output file, specify the prefix in parameter 90.

The Excel file is written using the AS_XSLX PL/SQL package written by Anton Scheffer.  The AS_XSLX is an excellent piece of software that you can utilize for a number of other Excel reporting requirements. 

I had to slightly modify the procedure query2sheet so I renamed the package to  AS_XSLXEBS to prevent an overwrite in case you already have your own copy of AS_XSLX.

My modifications are:

- Remove the underscore for all column names and make them Sentence Case, bold and underlined

- Adjust the column width to the GREATEST(length(header label), max(data length))

This formatting allows the reports to look more user friendly. 

This is free to use and modify as you please. For details see the disclaimers in the code. 

Enjoy!

Robust messaging system in EBS

 Oracle EBS does not provide a straightforward and efficient tool for communicating important messages to users.

Currently, we have two main options for delivering actionable information to users:

1. Create a concurrent request to generate a report for the user.
2. Develop a workflow to send a message.

However, both methods are resource-intensive and add significant overhead to any project where they are implemented.

For the first option, a report must be developed, typically using Oracle Reports, followed by creating a BI Publisher template. This process requires a developer skilled in Oracle Reports and BI Publisher environments.

Workflows, on the other hand, are time-consuming to develop and test. Custom workflows, beyond the seeded ones, are rarely used for communication, which is unfortunate because workflow messages appear on the To-Do worklist that opens upon application login. This worklist contains all pending tasks and can serve as a checklist at the start of each workday.

Below, I outline a simple yet powerful API that enables you to transform any SQL query into a professionally formatted worklist message, offering a more efficient and user-friendly communication tool.

Here is a simple query:

   SELECT Organization_id,  organization_code org_code, calendar_code calendar, creation_date,       last_update_date

    FROM mtl_parameters

ORDER BY 2

instantly transformed into an Excel exportable worklist notification:

An this is the API call:

DECLARE

   query VARCHAR2(4000) := 

      'SELECT 

           Organization_id, 

           organization_code org_code, 

           Calendar_code calendar,

           creation_date,

           last_update_date

       FROM 

           mtl_parameters 

       ORDER BY 

           2';

BEGIN 

   XX_UNIVERSAL_WORKFLOW.start_workflow (

      p_query             => query, -- Query that will retrieve the data for the report

      p_performer         => 'PBELTER', -- Recipient of the notification, application user name from fnd_user table

      p_org_id            => 81, -- Organization ID, mainly to select the corporate logo in the report, different per company

      p_MESSAGE_TITLE     => 'Warehouse listing', -- Notification subject

      p_DOCUMENT_HEADING  => 'List of Warehouses', -- Name of the report printed in the header

      p_GENERIC_MSG_TOP   => 'Here is the list of warehouses:<br>

                              It contains the code and the calendar<br>

                              But also a date when the warehouse was created<br>

                              And when it was updated', -- Introductory message that prints before all the data. Can be in HTML

      p_GENERIC_MSG_BOTTOM => 'So now that we are done:<br>

                              ENJOY THE REST OF YOUR DAY!!' -- Summary message that prints after all the data

   );

END;

To run this you need a very simple workflow with only 1 function that delivers the message:

And the PL/QL package to run it. 

You can download the workflow form here: Download workflow

If the link doesn't work the end of document shows the detailed steps

Below you will find the PL/SQL package with detailed in-line comments and the details of how to set up your workflow. The Workflow needs to be set up only once, regardless of where you call the API from and what queries you are passing.

In the code you will find the corporate logo:

https://www.blogger.com/img/logo_blogger_40px_2x.png

Replace it with the link to your logo

You can download the package from here: Download Package

CREATE OR REPLACE PACKAGE APPS.XX_UNIVERSAL_WORKFLOW

AS

-- By Peter Belter 16-SEP-2024

   -- Define the VARIABLE_TABLE type as a table of VARCHAR2(20000)

   TYPE VARIABLE_TABLE IS TABLE OF VARCHAR2 (20000);

   -- Procedure declaration to print query columns and values

   PROCEDURE print_query_columns (p_query       IN     VARCHAR2, -- Input query to describe and print column names

                                  col_headers      OUT VARIABLE_TABLE, -- OUT parameter to hold column headers

                                  col_values       OUT VARIABLE_TABLE); -- OUT parameter to hold column values

   -- Procedure declaration for building notification body

   PROCEDURE build_ntf_body (document_id     IN            VARCHAR2,

                             display_type    IN            VARCHAR2,

                             document        IN OUT NOCOPY CLOB,

                             document_type   IN OUT NOCOPY VARCHAR2--,  query           IN            VARCHAR2

                             );

  Procedure start_workflow (p_query in varchar2, -- query that will retrieve the data for the report

  p_performer in varchar2, -- receiient of the notification, application user name from fnd_user table

  p_org_id in  number, -- organization ID, mainly to select the corporate logo in the report, different per company

  p_MESSAGE_TITLE in varchar2, -- notification subject

  p_DOCUMENT_HEADING  in varchar2, -- name of the report printed in the header

  p_GENERIC_MSG_TOP in varchar2,  -- introductory message that prints before all the data. Can be in html

  p_GENERIC_MSG_BOTTOM in varchar2 -- summary message that prints after all the data

  )    ;   

END XX_UNIVERSAL_WORKFLOW;

/

CREATE OR REPLACE PACKAGE BODY APPS.XX_UNIVERSAL_WORKFLOW

AS

-- By Peter Belter 16-SEP-2024

   PROCEDURE print_query_columns (

    p_query       IN     VARCHAR2,        -- Input query to describe and print column names

    col_headers   OUT    VARIABLE_TABLE,  -- OUT parameter to hold column headers

    col_values    OUT    VARIABLE_TABLE   -- OUT parameter to hold column values

) IS

    c                   INTEGER;           -- Cursor ID declared within the procedure

    col_count           INTEGER;           -- Number of columns in the result set

    desc_tab            DBMS_SQL.DESC_TAB2;  -- Table of column descriptions

    col_value_varchar   VARCHAR2 (20000);  -- Temporary variable to hold VARCHAR2 values

    col_value_number    NUMBER;            -- Temporary variable to hold NUMBER values

    col_value_date      DATE;              -- Temporary variable to hold DATE values

    exec_result         INTEGER;           -- Variable to store the result of execution

    -- Function to transform column names

    FUNCTION transform_column_name (col_name VARCHAR2) RETURN VARCHAR2 IS

        transformed_name VARCHAR2(4000);

    BEGIN

        -- Transformation logic

        IF col_name LIKE '%_ID' THEN

            transformed_name := INITCAP(REPLACE(SUBSTR(col_name, 1, LENGTH(col_name) - 3), '_', ' ')) || ' ID';

        ELSE

            transformed_name := INITCAP(REPLACE(col_name, '_', ' '));

        END IF;

        RETURN transformed_name;

    END transform_column_name;

BEGIN

    -- Open a new cursor

    c := DBMS_SQL.OPEN_CURSOR;

    BEGIN

        -- Parse the input query

        DBMS_SQL.PARSE(c, p_query, DBMS_SQL.NATIVE);

        -- Describe the columns of the query

        DBMS_SQL.DESCRIBE_COLUMNS2(c, col_count, desc_tab);

        -- Initialize the VARIABLE_TABLE to store column headers and values

        col_headers := VARIABLE_TABLE();

        col_values := VARIABLE_TABLE();

        -- Load column names into col_headers and define columns for fetching values

        col_headers.EXTEND(col_count);

        FOR i IN 1 .. col_count LOOP

            col_headers(i) := transform_column_name(desc_tab(i).col_name);

            -- Define columns dynamically based on their data types

            IF desc_tab(i).col_type = 2 THEN -- NUMBER type

                DBMS_SQL.DEFINE_COLUMN(c, i, col_value_number);

            ELSIF desc_tab(i).col_type = 12 THEN -- DATE type

                DBMS_SQL.DEFINE_COLUMN(c, i, col_value_date);

            ELSE -- Assume VARCHAR2 or CHAR type

                DBMS_SQL.DEFINE_COLUMN(c, i, col_value_varchar, 20000);

            END IF;

        END LOOP;

        -- Execute the query

        exec_result := DBMS_SQL.EXECUTE(c);

        -- Fetch the rows and populate col_values

        WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP

            FOR i IN 1 .. col_count LOOP

                IF desc_tab(i).col_type = 2 THEN -- NUMBER type

                    DBMS_SQL.COLUMN_VALUE(c, i, col_value_number);

                    col_values.EXTEND;

                    col_values(col_values.COUNT) := TO_CHAR(col_value_number);

                ELSIF desc_tab(i).col_type = 12 THEN -- DATE type

                    DBMS_SQL.COLUMN_VALUE(c, i, col_value_date);

                    col_values.EXTEND;

                    col_values(col_values.COUNT) := TO_CHAR(col_value_date, 'dd-MON-yy HH24:MI:SS');

                ELSE -- VARCHAR2 or CHAR type

                    DBMS_SQL.COLUMN_VALUE(c, i, col_value_varchar);

                    col_values.EXTEND;

                    col_values(col_values.COUNT) := col_value_varchar;

                END IF;

            END LOOP;

        END LOOP;

        -- Close the cursor

        DBMS_SQL.CLOSE_CURSOR(c);

    EXCEPTION

        WHEN OTHERS THEN

            -- Close the cursor if an error occurs

            IF DBMS_SQL.IS_OPEN(c) THEN

                DBMS_SQL.CLOSE_CURSOR(c);

            END IF;

            -- Output the SQLCODE and SQLERRM for debugging

            --DBMS_OUTPUT.PUT_LINE('Error in print_query_columns: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM);

            -- Check if the error is due to invalid SQL syntax

            IF ABS(SQLCODE) BETWEEN 900 AND 999 OR ABS(SQLCODE) BETWEEN 20000 AND 20999 THEN

                -- Handle syntax errors

                --DBMS_OUTPUT.PUT_LINE('Invalid SQL query syntax: ' || SQLERRM);

                -- Replace col_headers and col_values with the default error message

                col_headers := VARIABLE_TABLE();

                col_headers.EXTEND(1);

                col_headers(1) := 'Error';

                col_values := VARIABLE_TABLE();

                col_values.EXTEND(1);

                col_values(1) := 'The query passed to the procedure had invalid syntax';

            ELSE

                -- For other errors, re-raise the exception

                RAISE;

            END IF;

    END;

END print_query_columns;

   PROCEDURE build_ntf_body (

       document_id     IN            VARCHAR2,

       display_type    IN            VARCHAR2,

       document        IN OUT NOCOPY CLOB,

       document_type   IN OUT NOCOPY VARCHAR2

   ) IS

       l_corp_logo       VARCHAR2(4000);

       main_hdr1         CLOB;

       main_hdr_ending   CLOB;

       l_button          CLOB;

       l_document_name   VARCHAR2(4000);

       l_current_date    VARCHAR2(100);

       col_headers       VARIABLE_TABLE := VARIABLE_TABLE();  -- Initialize collection

       col_values        VARIABLE_TABLE := VARIABLE_TABLE();  -- Initialize collection

       html_table        CLOB;

       col_count         INTEGER;

       v_itemtype        VARCHAR2(20);

       v_itemkey         VARCHAR2(50);

       query             VARCHAR2(4000);

       l_org_id          NUMBER;

       l_top_message     VARCHAR2(4000);

       l_bottom_message  VARCHAR2(4000);

       l_missing_query   VARCHAR2(200);

   BEGIN

       -- Initialize CLOB variables

       DBMS_LOB.CREATETEMPORARY(main_hdr1, TRUE);

       DBMS_LOB.CREATETEMPORARY(main_hdr_ending, TRUE);

       DBMS_LOB.CREATETEMPORARY(l_button, TRUE);

       DBMS_LOB.CREATETEMPORARY(html_table, TRUE);

       DBMS_LOB.CREATETEMPORARY(document, TRUE);

       v_itemtype := SUBSTR(document_id, 1, INSTR(document_id, '|') - 1);

       v_itemkey := SUBSTR(document_id, INSTR(document_id, '|') + 1);

       -- Safely retrieve the query attribute

       BEGIN

           query := WF_ENGINE.GetItemAttrTEXT(v_itemtype, v_itemkey, 'QUERY');

       EXCEPTION

           WHEN NO_DATA_FOUND THEN

               l_missing_query := 'The query was not passed into the procedure';

               query := 'SELECT ''' || l_missing_query || ''' AS Error FROM dual';

       END;

       -- Safely retrieve other attributes

       BEGIN

           l_org_id := WF_ENGINE.getItemAttrNumber(v_itemtype, v_itemkey, 'ORG_ID');

       EXCEPTION

           WHEN NO_DATA_FOUND THEN

               l_org_id := NULL;

       END;

       BEGIN

           l_document_name := WF_ENGINE.getItemAttrText(v_itemtype, v_itemkey, 'DOCUMENT_HEADING');

       EXCEPTION

           WHEN NO_DATA_FOUND THEN

               l_document_name := 'Default Document Name';

       END;

       BEGIN

           l_top_message := WF_ENGINE.getItemAttrText(v_itemtype, v_itemkey, 'GENERIC_MSG_TOP');

       EXCEPTION

           WHEN NO_DATA_FOUND THEN

               l_top_message := '';

       END;

       BEGIN

           l_bottom_message := WF_ENGINE.getItemAttrText(v_itemtype, v_itemkey, 'GENERIC_MSG_BOTTOM');

       EXCEPTION

           WHEN NO_DATA_FOUND THEN

               l_bottom_message := '';

       END;

       l_corp_logo := '<a href="https://www.blogger.com/img/logo_blogger_40px_2x.png" target="_blank">'

                      || '<img style="display:block" height="74" alt="Blogger" '

                      || 'src="https://www.blogger.com/img/logo_blogger_40px_2x.png" '

                      || 'width="249" border="0"></a>';

       l_current_date := UPPER(TO_CHAR(SYSDATE, 'dd-mon-yy hh24:mi:ss'));

       -- Create the Export to Excel button with JavaScript

       DBMS_LOB.APPEND(l_button, '<button onclick="exportTableToExcel(''dataTable'', ''exported_data'')">Export to Excel</button>'

                                || '<script>'

                                || 'function exportTableToExcel(tableID, filename = '''') {'

                                || 'var downloadLink;'

                                || 'var dataType = ''application/vnd.ms-excel'';'

                                || 'var tableSelect = document.getElementById(tableID);'

                                || 'tableSelect.style.fontSize = "10pt";'

                                || 'if (!tableSelect || tableSelect.rows.length === 0) {'

                                || 'alert(''The table is empty or not found.'');'

                                || 'return;'

                                || '}'

                                || 'var tableHTML = tableSelect.outerHTML;'

                                || 'filename = filename ? filename + ''.xls'' : ''excel_data.xls'';'

                                || 'downloadLink = document.createElement("a");'

                                || 'document.body.appendChild(downloadLink);'

                                || 'if (navigator.msSaveOrOpenBlob) {'

                                || 'var blob = new Blob([''\ufeff'', tableHTML], { type: dataType });'

                                || 'navigator.msSaveOrOpenBlob(blob, filename);'

                                || '} else {'

                                || 'downloadLink.href = ''data:'' + dataType + '';base64,'' + window.btoa(unescape(encodeURIComponent(tableHTML)));'

                                || 'downloadLink.download = filename;'

                                || 'downloadLink.click();'

                                || '}'

                                || 'tableSelect.style.fontSize = "";'

                                || 'document.body.removeChild(downloadLink);'

                                || '}'

                                || '</script>');

       -- Create the top section of the email

       DBMS_LOB.APPEND(main_hdr1, '<html><head><title>Export Table to Excel</title>'

                                 || '<style>'

                                 || '.exportTable { border-collapse: collapse; }'

                                 || '.exportTable th { padding: 8px; text-align: left; background-color: #f2f2f2; border: none; }'

                                 || '.exportTable td { padding: 8px; text-align: left; border-bottom: 1px solid #cccccc; }'

                                 || 'button { margin-left: 10px; padding: 10px 20px; font-size: 16px; cursor: pointer; }'

                                 || '</style></head><body>'

                                 || '<div style="width: 100%; background-color: #72A1C8; height: 5px;"></div>'

                                 || '<div style="display: flex; align-items: center; padding: 10px 0;">'

                                 || l_corp_logo

                                 || '<div style="margin-left: 40px; display: flex; align-items: center;">'

                                 || '<h3 style="margin: 0; display: inline-block;">' || l_document_name || '</h3>'

                                 || '<span style="font-size: 10px; color: #000000; margin-left: 20px;">' || l_current_date || '</span>'

                                 || l_button

                                 || '</div></div>'

                                 || '<div style="width: 100%; background-color: #72A1C8; height: 5px;"></div>'

                                 || '<div style="height: 25px;"></div>'

                                 || '<div style="font-size: 12px; font-weight: bold; margin-top: 10px; margin-bottom: 10px;">' || l_top_message || '</div>');

       -- Call print_query_columns to get headers and values

       print_query_columns(query, col_headers, col_values);

       -- Prepare the HTML table

       DBMS_LOB.APPEND(html_table, '<table id="dataTable" class="exportTable" style="font-size: 11px; color: #000000; width: 100%;" cellpadding="4">');

       DBMS_LOB.APPEND(html_table, '<thead><tr>');

       FOR i IN 1 .. col_headers.COUNT LOOP

           DBMS_LOB.APPEND(html_table, '<th style="padding: 4px;">' || col_headers(i) || '</th>');

       END LOOP;

       DBMS_LOB.APPEND(html_table, '</tr></thead><tbody>');

       col_count := col_headers.COUNT;

       FOR i IN 1 .. col_values.COUNT LOOP

           IF (i - 1) MOD col_count = 0 THEN

               DBMS_LOB.APPEND(html_table, '<tr>');

           END IF;

           DBMS_LOB.APPEND(html_table, '<td style="padding: 4px;">' || NVL(col_values(i), '&nbsp;') || '</td>');

           IF i MOD col_count = 0 THEN

               DBMS_LOB.APPEND(html_table, '</tr>');

           END IF;

       END LOOP;

       DBMS_LOB.APPEND(html_table, '</tbody></table>');

       -- Add the bottom message

       DBMS_LOB.APPEND(html_table, '<div style="font-size: 12px; font-weight: bold; margin-top: 10px; margin-bottom: 10px;">' || l_bottom_message || '</div>');

       -- Prepare the footer and close the HTML document

       DBMS_LOB.APPEND(main_hdr_ending, '</body></html>');

       -- Combine the header, export table, and footer to create the final document

       DBMS_LOB.APPEND(document, main_hdr1);

       DBMS_LOB.APPEND(document, html_table);

       DBMS_LOB.APPEND(document, main_hdr_ending);

   EXCEPTION

       WHEN OTHERS THEN

           --DBMS_OUTPUT.PUT_LINE('Error in build_ntf_body: ' || SQLERRM);

           -- Safely free the temporary CLOBs without additional checks

           BEGIN

               DBMS_LOB.FREETEMPORARY(main_hdr1);

           EXCEPTION

               WHEN OTHERS THEN

                   NULL;

           END;

           BEGIN

               DBMS_LOB.FREETEMPORARY(l_button);

           EXCEPTION

               WHEN OTHERS THEN

                   NULL;

           END;

           BEGIN

               DBMS_LOB.FREETEMPORARY(html_table);

           EXCEPTION

               WHEN OTHERS THEN

                   NULL;

           END;

           BEGIN

               DBMS_LOB.FREETEMPORARY(main_hdr_ending);

           EXCEPTION

               WHEN OTHERS THEN

                   NULL;

           END;

           BEGIN

               DBMS_LOB.FREETEMPORARY(document);

           EXCEPTION

               WHEN OTHERS THEN

                   NULL;

           END;

           RAISE;

   END build_ntf_body;

   PROCEDURE start_workflow (

       p_query              IN VARCHAR2,

       p_performer          IN VARCHAR2,

       p_org_id             IN NUMBER,

       p_MESSAGE_TITLE      IN VARCHAR2,

       p_DOCUMENT_HEADING   IN VARCHAR2,

       p_GENERIC_MSG_TOP    IN VARCHAR2,

       p_GENERIC_MSG_BOTTOM IN VARCHAR2

   ) IS

       l_itemkey    VARCHAR2(50);

       l_itemtype   VARCHAR2(20) := 'WFUNIVER';

       l_query      VARCHAR2(4000);

   BEGIN

       --DBMS_OUTPUT.PUT_LINE('Workflow is getting initialized');

       l_itemkey := TO_CHAR(XX_UNIVERSAL_WF_S.NEXTVAL);

       --DBMS_OUTPUT.PUT_LINE('l_itemkey=' || l_itemkey);

       wf_engine.createprocess(l_itemtype, l_itemkey, 'UNIVERSAL_NTF');

       --DBMS_OUTPUT.PUT_LINE('Process created');

       wf_engine.setitemuserkey(itemtype => l_itemtype, itemkey => l_itemkey, userkey => l_itemkey);

       --DBMS_OUTPUT.PUT_LINE('User key created');

       wf_engine.setitemowner(itemtype => l_itemtype, itemkey => l_itemkey, owner => p_performer);

       --DBMS_OUTPUT.PUT_LINE('Owner set');

       -- Set item attributes

       WF_ENGINE.setItemAttrText(l_itemtype, l_itemkey, 'PERFORMER', p_performer);

       WF_ENGINE.setItemAttrNumber(l_itemtype, l_itemkey, 'ORG_ID', p_org_id);

       WF_ENGINE.setItemAttrText(l_itemtype, l_itemkey, 'MESSAGE_TITLE', p_MESSAGE_TITLE);

       WF_ENGINE.setItemAttrText(l_itemtype, l_itemkey, 'DOCUMENT_HEADING', p_DOCUMENT_HEADING);

       WF_ENGINE.setItemAttrText(l_itemtype, l_itemkey, 'GENERIC_MSG_TOP', p_GENERIC_MSG_TOP);

       WF_ENGINE.setItemAttrText(l_itemtype, l_itemkey, 'GENERIC_MSG_BOTTOM', p_GENERIC_MSG_BOTTOM);

       WF_ENGINE.setItemAttrText(l_itemtype, l_itemkey, 'QUERY', p_query);

       -- Build the message

       wf_engine.setitemattrdocument(

           itemtype   => l_itemtype,

           itemkey    => l_itemkey,

           aname      => 'MESSAGE',

           documentid => 'PLSQLCLOB:XX_UNIVERSAL_WORKFLOW.BUILD_NTF_BODY/' || l_itemtype || '|' || l_itemkey

       );

       wf_engine.startprocess(l_itemtype, l_itemkey);

       COMMIT;

       --DBMS_OUTPUT.PUT_LINE('Query set');

       l_query := WF_ENGINE.getItemAttrText(l_itemtype, l_itemkey, 'QUERY');

       --DBMS_OUTPUT.PUT_LINE('l_query:=' || l_query);

   EXCEPTION

       WHEN OTHERS THEN

       null;

           --DBMS_OUTPUT.PUT_LINE('Error in start_workflow: ' || SQLERRM);

   END start_workflow;

END XX_UNIVERSAL_WORKFLOW;

/

Here are the workflow builder steps:

Create the new Item Type WFUNIVER

Create new Process UNIVERSAL_NTF

Create attributes:

Attributes that are text:

QUERY

PERFORMER

MESSAGE_TITLE

DOCUMENT_HEADING

GENERIC_MSG_TOP

GENERIC_MSG_BOTTOM ​

ORG_ID is a numeric attribute

MESSAGE is a Document. This is the most important attribute as it contains the message displayed in the notification:

Now define the GENERIC_MSG itself

&MESSAGE_TITLE in the subject and &MESSAGE in the HTML body

For the message to work drag the top MESSAGE_TITLE and MESSAGE attributes to attach them to the message itself.

The GENERIC_NTF notification needs to have the Generic Message attaches to it.

Now it is time to build the Process.

Double click the Universal Notification icon

The drag the Start and End Function in there. The easiest way is to copy them from the "Standard" workflow item type but you can define them from scratch here as well.

It is important to designate this function as "Start" in the node tab or the workflow will not work

Same with the End function:

Drag the newly defined notification into the process and join it to the start and end by holding the mouse right button. 

The notification must have the performer sourced form the item attribute named Performer

Enjoy!