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:
- Create a concurrent request to generate a report for the user.
- 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:
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), ' ') || '</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
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
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
It is important to designate this function as "Start" in the node tab or the workflow will not work
Same with the End function:
Enjoy!
