Workflow Notification using OA Framework

If the notification contents are simple and static then Message body is used to build. Otherwise mostly PLSQL Document Type Attributes are used to build complex and dynamic contents. It is easy to develop. But it has few limitations like

• Look and Feel won’t be like Self Service Pages
• Very difficult to format
• Maintenance is costly
• APIs (PL/SQL Web Toolkit) used won’t be available from R12

To avoid these, the notification body can be built using OA Framework. Same document type attribute can be used with small changes in the syntax like

JSP:/OA_HTML/OA.jsp?region=<OAFRegion>&<parameters>

Advantages of using this are

• Uniform Look and Feel across all self service pages
• Framework will format the contents
• OA Framework Personalization and Extension can be used for any modifications
• Much better performance than PLSQL
• Upgrade Safe

In fact Oracle is advising to use OAF to build notification contents. Developer should follow few important steps to use OA Regions in Workflow Notification. Refer Oracle Application Workflow Guide for more details.

Export from APPS

In Apps existing export function can be used to export the information in the current block. The following solution can be used to export not only from the specific block, can be used to based the information available in the form or not available in the form.

Approach:
- Build a new package using few web toolkit APIs (refer the sample below)
- This package has to be registered as Web Plsql to access as web toolkit.
- Create new function to call the above package as type "SSWA plsql function" and HTML Call as above API.
- Execute the created new function from wherever export needed. It can be from button in a form or can be a special menu

Sample API:
CREATE OR REPLACE PACKAGE BODY EXPORT IS

PROCEDURE main (
p_user_id NUMBER
) IS

lc_line VARCHAR2(4000);
lc_mime_type VARCHAR2(100) := 'text/csv';
lc_char_set VARCHAR2(100) := NULL;
ln_length NUMBER := 0;
lc_blob BLOB;
lc_new_line VARCHAR2(10) := CHR(10);

CURSOR lcu_users IS
SELECT user_name
,start_date
,end_date
,email_address
FROM fnd_user
WHERE user_id = p_user_id;

BEGIN

FND_GLOBAL.apps_initialize(FND_PROFILE.value('USER_ID')
,FND_PROFILE.value('RESP_ID')
,FND_PROFILE.value('RESP_APPL_ID')
);

DBMS_LOB.CreateTemporary(lc_blob, TRUE, DBMS_LOB.SESSION);

FOR lr_users IN lcu_users
LOOP

lc_line := lr_users.user_name ','
lr_users.start_date ','
lr_users.end_date ','
lr_users.email_address lc_new_line;

-- Covert into RAW
lc_line := utl_raw.cast_to_raw(lc_line);
ln_length := utl_raw.length(lc_line);

-- Write the data into BLOB
DBMS_LOB.WriteAppend(lc_blob, ln_length, lc_line);

END LOOP;

-- Set Mime Type
OWA_UTIL.mime_header(lc_mime_type, FALSE, lc_char_set);
htp.p( 'Content-length: ' DBMS_LOB.getlength(lc_blob));
OWA_UTIL.http_header_close;

-- Download it as CSV
WPG_DOCLOAD.download_file(lc_blob);

EXCEPTION
WHEN OTHERS THEN
HTP.htmlOpen;
HTP.headOpen;
HTP.title('404 Not Found');
HTP.headClose;
HTP.bodyOpen;
HTP.hr;
HTP.header(nsize=>1, cheader=>SQLERRM);
HTP.hr;
HTP.p(FND_MESSAGE.Get_String('GMD','LM_BAD_FILENAME'));
HTP.bodyClose;
HTP.htmlClose;
END main;
END EXPORT;