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;

- Role vs Responsibility -

The Responsibility has been used not only to define the application navigation menus, but also to define privileges and permissions. Using this definition of responsibility, it is necessary to create several similar responsibilities in order to effectively carve out data and functional security access for a group of users.

Going forward (from EBS 11.5.10 onwards), Users will no longer need to be directly assigned the lower level permissions and responsibilities, as these can be implicitly inherited based upon the roles assigned to the user. Roles can now be defined to consolidate responsibilities and other roles through role inheritance, as well as lower level permissions (functions) and data security policies.

The benefits of implementing Role Based Access Control (RBAC) are
- Structured user access control
- Reduced cost of administering user access control system
- Streamlined setup and implementation of security policies and rules

Refer System Administrator's Guide – Security document for more details.

- XML Publisher Report in EBS -

Oracle XML Publisher is a new Java based product available within the technology stack of the EBS. It provides users with a template-based, easy-to-use publishing solution based on standard well-known technologies and tools. So users can take advantage of it easily to rapidly develop and maintain report formats.

This document helps for buddies who developing his first XML Publisher Report. Follow these steps to register your XML Publisher Report in EBS.

1. Attach "XML Publisher Administrator" responsibility to APPS User
2. Develop simple report; register it in EBS like standard concurrent program. Make sure that the Output Format is set as 'XML'. Pls note that Oracle Reports will be obsolete in coming EBS versions. So it is advisable to use Data Templates rather than Oracle Reports. If you use Data Templates then no need to have Report Concurrent Program. Refer Developer’s guide for more details.
3. Add this program to the responsibility; Run the report and download the concurrent program output (XML file) to desktop. This XML will be used as Sample for Template development.
4. Download patch 5027437 (p5027437_11i_MSWIN.zip) from Metalink and install it in the Desktop OR identify the patch for EBS version that you have and install it.
5. Open Ms Word. You can see new Template Builder toolbar
6. Click 'Load XML Data' in Data menu. Give the XML output file that you downloaded from Report Concurrent Program
7. Use Table/Form wizard to build template to build the layout
8. Save the file in Rich Text Format (RTF)
9. Preview the report output from MS Word.
10. Define new Data Definition (Navigation: XML Publisher Administrator - Data Definitions - Create Data Definition)
    Enter Name, Application, Code, Preview Data (sample XML output file) and Start Date. Make sure the Code and Report Concurrent Program Short Name are same
11. Define new Template (Navigation: XML Publisher Administrator - Templates - Create Template)
    - Enter Name, Code, Application, Type (make it RTF), Data Definition (select the one you created)
    - Enter the File as the RFT file created
    - Enter Language and Territory

Now XML Publisher Report is registered in EBS and ready to function. This report can be executed in two ways

• Run the Report concurrent program, note the request ID. Then run the XML Report Publisher concurrent program with the above request id and template as parameter
• Change Concurrent Program Format to XML and submit the request. Program will pickup the template abd generate the output.

Note that XML Publisher Report strategies and setups are kept on changing. So please refer Oracle XML Publisher Administration and Developer's Guide and Oracle XML Publisher Report Designer's Guide documents with respect to your EBS version if you face any problem to register/run the report.

Reference

• Oracle XML Publisher Administration and Developer's Guide
• Oracle XML Publisher Report Designer's Guide
• Oracle BI Publisher Discussion Forum

- Invoking BPEL Process using Pl/SQL -

BPEL process can be invoked from Oracle database in different ways. Following sample can be used to invoke BPEL process using SOAP and pl/sql APIs.

- Change Provider URL, WS name in the sample code given below
- This program uses only standard String Input parameter. Make the necessary changes if WSDL has different parameter(s).

DECLARE
lc_soap_request VARCHAR2(3000);
lc_soap_respond VARCHAR2(3000);

lc_input VARCHAR2(5) := 11; -- Changes this as per BPEL Process (WSDL) definition
lc_namespace VARCHAR2(128) := 'xmlns="http://xmlns.oracle.com/<WS Name>"'; -- Replace <WS Name> with BPEL Process
lc_ws_request VARCHAR2(50) := <BPEL Process Request> --Get it from WSDL file

lc_http_req UTL_HTTP.req;
lc_http_resp UTL_HTTP.resp;
BEGIN

lc_soap_request := '<?xml version = "1.0" encoding = "UTF-8"?>'
||'<SOAP-ENV:Envelope '
||'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" '
||'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" '
||'xmlns:xsd="http://www.w3.org/1999/XMLSchema">'
||'<SOAP-ENV:Body>'
||'<'||lc_ws_request||' '||lc_namespace||'>'
||'<input '||lc_namespace||'>'||lc_input||'</input>'
||'</'||lc_ws_request||'>'
||'</SOAP-ENV:Body>'
||'</SOAP-ENV:Envelope>';

lc_http_req := UTL_HTTP.begin_request(
'<URL>' -- Get this from BPEL Processes->WSDL->Endpoint Location OR this can be get it from WS ESB definition tab
,'POST'
,'HTTP/1.1'
);

UTL_HTTP.set_header (
lc_http_req
, 'Content-Type'
, 'text/xml'
);

UTL_HTTP.set_header (
lc_http_req
, 'Content-Length'
, LENGTH(lc_soap_request)
);

UTL_HTTP.set_header (
lc_http_req
, 'SOAPAction'
, 'process'
);

UTL_HTTP.write_text(lc_http_req, lc_soap_request);

lc_http_resp := UTL_HTTP.get_response(lc_http_req);

UTL_HTTP.read_text(lc_http_resp, lc_soap_respond);

UTL_HTTP.end_response(lc_http_resp);

DBMS_OUTPUT.put_line(substr(lc_soap_respond,1 ,250));
DBMS_OUTPUT.put_line(substr(lc_soap_respond,251,500));

END;

Limitations:
- Exceptions/Fault handling should be controlled manually
- Program will just initiate the Web Service. Auditing or back tracking should be handled manually
- This approach might not be as per Oracle EBS standards. Please check EBS and BPEL documents for more information related to standards

- R12 Installation with External Hard Disk & XP -

Installed R12 in my laptop recently, thought of sharing my experience to everyone.

I followed Hsawwan’s thread from beginning and installed successfully in few hours.

Hardware & Software Specifications:

• Intel Core 2 Duo, 2GHz
• 3GB of RAM
• 160 GB Hard Drive + 80 GB External hard drive
• Windows XP Professional with Service Pack 2

Challenges Faced:

• Not able to ping my machine either using domain name or IP address. After couple of hour’s investigation, found it was because of enabled Norton Internet Security. I disabled it and followed the installation steps.
• Installation System check failed when I entered UNIX Toolkit base path of cygwin. It should include bin directory as well. Eg C:/cygwin/bin. Whereas for VC++ we should not include bin, it should be upto VC base path.
• As I had only 160 GB HDD, used external HDD. Modified the DB data file path to use my external HDD for Archive, Log. Make sure to keep all system related files in internal HDD to improve the performance.

Installation took around 3 hours and 30 min to see login screen. Performance is very good so far.

Ref: http://forums.oracle.com/forums/thread.jspa?threadID=494790&tstart=0

- Projects R12 New Features & Enhancements -

Following are highlights of Oracle Projects new features and enhancements in Oracle E-Business Suite (EBS) Release 12. Most of the features listed here are available from Oracle Projects Family Pack M.

Project Costing

• Labor Cost Processing for Contingent Workers - Timecard and PO can be linked
• Changes with respect to Oracle Payables Invoice Lines new feature
• Adjust supplier cost in Oracle Project Costing under certain conditions
• New mass adjust concurrent process
• Oracle Payables now provides a drilldown to the Project Expenditure Inquiry window from the invoice workbench.
• Integration with Oracle Project Manufacturing

Project Billing

• Integration with Oracle E-Business Tax
• Customers can now be attached at Top Task
• Invoice Method at Top Task: Enables to invoice customers with different methods such as, fixed price and time and materials, within the same project.
• Invoice Write-Off at line level
• Invoice Concessions for credit memos
• Project functional currency conversion attributes
• Enhanced Billing Review with New Interface

Project Foundation

• Integration with Oracle Sub-ledger Accounting
• Multi-Org Access Control
• Separation of Reporting and Planning Resources
• Increased granularity for planning and reporting against inventory items and manufacturing resources through increased integration with SCM.
• Resource Breakdown Structure enhancements
• Define and utilize contingent workers on projects in the same capacities and manner as employees.
• Enhanced Configurability of Project Home, Project List, Workbench and Page Layouts
• Element level options while coping a project to create another project

Oracle Project Management

• Supports dependencies between tasks within a single project or across projects.
• Now can assign planning resources to tasks, including people, equipment, material items, and financial resources.
• Can associate workflow to tasks that can be configured to send notifications depending on various businesses.
• Expanded Integration with Microsoft Project
• Can define the deliverables for a project, including all the associated actions needed to complete each deliverable.

Oracle Project Portfolio Analysis

Project Portfolio Analysis is a new product helps companies align their portfolios of projects and programs with their organizational objectives by taking advantage of Oracle Project’s standard project criteria, project scoring and ranking, and what-if portfolio scenarios.