- 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.

- Print Attachments in EBS -

The attachments feature in Oracle Application/E-Business Suite enables users to link unstructured data, such as images, word processing documents, spreadsheets, or text to their application data. For example, users can link images to items, PDF/Word document to operations as operation instructions.

It is very common requirement to print these attached documents during various processes. For example, Customer wants the Contract Agreement document along with billing invoice. In these cases users should open the attachment form and print the document manually every time. As of now EBS does not have standard mechanism to print the attachments.

This BLOG helps to print the attachment without opening the attachment form.

Approach

1. Create Oracle Directory 'OUTFILE_FOLDER' and set the path to Concurrent program Output file location.
2. Write a Java stored procedure to store the binary file to the specified location and compile it in APPS schema. (Refer: Sample-1). Instead of Java API, UTL_FILE.put_raw API can also be used if the database version is on or above 10g. 9i has limitations and bug to use this pl/sql API.
3. Write a pl/sql API to get the binary file and make the call to above Java API to store the file in the outfile location. (Refer: Sample-2)
4. Create concurrent program with Attachment File (FileID) as parameter and above pl/sql API as executable.
5. If the attachments are PDF and want to print automatically then do the printer setups and format as PDF. Note that the printer drivers should support to print PDF files (Eg: PASTA Drivers).
6. If the attachments can be in any format then set it as HTML output. In this case users have to open the concurrent program output and print it manually.
7. Initiate this concurrent program along with the standard program either by creating Request Set or customizing the standard objects.

Sample-1: Java Program

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CopyFile" AS

import java.io.*;
import java.lang.*;
import java.sql.*;
import oracle.sql.*;

public class CopyFile extends Object
{
public static int execute(String filename, BLOB blob)
{

int success = 1;

try
{
File blobFile = new File(filename);
FileOutputStream outStream = new FileOutputStream(blobFile);
InputStream inStream = blob.getBinaryStream();

int length = -1;
int size = blob.getBufferSize();
byte[] buffer = new byte[size];

while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}

inStream.close();
outStream.close();
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("ERROR(djv_exportBlob) Unable to export:"+filename);
success = 0;
}
finally
{
return success;
}

}
};

Sample-2: Pl/sql API

CREATE OR REPLACE PACKAGE print is
PROCEDURE url(
x_status OUT VARCHAR2
,x_status_code OUT VARCHAR2
,p_file_id IN NUMBER
);
END print;
/

CREATE OR REPLACE PACKAGE BODY print IS

FUNCTION executeJava(
p_filename IN VARCHAR2
,p_blob IN BLOB
)
RETURN NUMBER IS
LANGUAGE JAVA
NAME 'CopyFile.execute(java.lang.String, oracle.sql.BLOB) return integer';

PROCEDURE url(
x_status OUT VARCHAR2
,x_status_code OUT VARCHAR2
,p_file_id IN NUMBER
) IS

lb_doc BLOB;
ln_status NUMBER;
ln_request_id NUMBER;
lc_outfile_path VARCHAR2(250);

BEGIN

-- Get the file ID
SELECT file_data
INTO lb_doc
FROM fnd_lobs
WHERE file_id = p_file_id;

-- Directory Path
SELECT directory_path
INTO lc_outfile_path
FROM all_directories
WHERE directory_name = 'OUTFILE_FOLDER';

-- Concurrent Program Request ID
ln_request_id := FND_GLOBAL.conc_request_id;

ln_status := executeJava(lc_outfile_path'/o'ln_request_id'.out',lb_doc);

END url;

END print;

Further Reading
Java I/O

- Best practice to customize EBS Workflows -

Oracle Workflow is embedded in Oracle Application/E-Business Suite to automate and streamline business processes. Oracle supports to extend or customize the seeded workflows to meet the customer requirements. Oracle Workflow Builder is used to modify an existing business process without changing its application's code. Oracle Workflow also allows extending/customizing workflow processes as business rules changes.

Following customization guidelines helps the implementation team to ensure standard and safe design and development practices for easy maintenance and upgrading/patching.

Customization Guidelines

• Test the unmodified seeded workflow on a test database and ensure that it runs successfully with the setup and data specific to your environment.
• Identify the Workflow Builder version used in Oracle Applications and install the same.
• Refer to the product-specific User's Guide and any documentation update, available on MetaLink/document library, for the specific workflow of interest. These documentation sources specifically mention what should NOT be modified. Oracle Support Services will not support modifications to any object that is specifically documented as not modifiable.
• Gradually build in customizations step-by-step, and test the customized workflow after each step.
• Keep in mind the future requirments and then do the customization/extension like keeping additional dummy processes and attributes.
• When creating PL/SQL procedures, conform to the standard PL/SQL API templates documented in the Oracle Workflow Guide. Be sure to handle exceptions in the event of an error so you can track down the procedure where the error has occurred.
• Do not implement the customized workflow in production without fully ensuring that it works successfully on a test database, which is a replica of your production setup.
• Verify that all setups have been completed as documented in the Oracle Workflow Guide, and the product-specific User's Guides.

What are Not Supported

The following types of customizations are not supported:

• Modifying a workflow object that has a protection level that is less than 100.
• Altering a workflow object's protection level if its original protection level is less than 100.
• Modifying your access level to an unauthorized level of less than 100 for the purpose of modifying workflow objects that are protected at levels less than 100.
• Customizations that are explicitly documented as being UNSUPPORTED in the seeded workflow's product-specific User's Guide or documentation update notes.
• Manual modifications of Workflow tables with a prefix of WF_ or FND_ unless it is documented in the Oracle Workflow Guide or is required by Oracle Support Services.
• Modifying the APIs used unless it is documented as supported.

- Virtual Private Database vs Label Security -

Protect the data and processes from unauthorized modification, destruction, disclosure, or delay is very important for any organization. Oracle provides Oracle Label Security (OLS) and Virtual Private Database (VPD) tools to address the full spectrum of data and process security issues.

Most of us are confused to identify the right tool for data security and find difficult to distinguish OLS and VPD. Hope the following table will help to identify the right tool to enforce the data security.

Comparison between OLS and VPD

Reference:

http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96578/labels.htm

Metalink Notes: 213684.1/234599.1/230980.1

- Best practice to customize the EBS forms -

It is very common for any Oracle E-Business Suite (EBS) implementation to customize the existing standard forms. The customizations can be varying minimal to major invasive customizations. This document helps the developers/technical team to identify the relevant approach before start doing any customizations.

Following are the different methodologies can be used to change the form

• Oracle Folders
• Descriptive Flex Fields (DFF)
• Forms Personalization
• CUSTOM.pll changes
• Invasive Customizations
  
  
  
  
  

Oracle Folders

Most of the standard forms are developed with Folders. If the requirements are UI related like changing the labels, show/hide columns, sorting the information can be easily done using Oracle Folders. Either administrator can do these changes and make it available to everyone or end users themselves can do the changes as per their individual usage.

Advantages of Folders

• Easy to make the changes
• End Users themselves can do the necessary changes
• EBS fully supported. So any patch/upgrades won’t affect the changes

Limitations:

• Restricted for the changes related to UI
• End users should be trained to do these changes

Descriptive Flex Fields (DFF)

If the requirement is to maintain the additional details then DFF can be enabled. This is one time setup, mostly done by the implementation team. Administrators can make the changes at any time, but not the end users.

Advantages are

• It is easy for Implementation Team/Administrations
• EBS fully supported. So any patch/upgrades won’t affect the changes

Limitations:

• Restricted to the Attribute columns defined in the tables. Most of the tables will have 15 columns. If the requirement is to have more than the defined columns then this setup can not be used.
• Supports only very limited additional validations

Forms Personalization

This configuration tools is available from 11.5.10. It can be installed in prior versions as well. It allows

• To make declarative changes to a form
• Changes get applied at runtime when the proper trigger event occurs.
• Changes can get applied based on security like user, responsibility
• Change object properties
• Execute certain Forms built-ins
• Display messages
• Activate menu entries

Advantages

• Can be used instead of CUSTOM pll changes
• Configurations survive upgrades
• User with minimum Oracle technology skills can do the changes

Limitations

• Can only change what Forms allows at runtime
• Can only respond to certain Trigger Events

CUSTOM.pll changes

Library available in $AU_TOP/resource is modified to provide customizations to Oracle Application forms using forms builder to modify package body.

• Hide fields, tabs
• Make fields required
• Restrict update or insert
• Change prompts, tab labels
• Alter LOVs
• Create zooms and tool bar menu selections
• Validate and format
• Almost anything that can do in PL/SQL

Advantages

• Much better controlled and additional features than Forms Personalization
• Changes survive upgrades

Limitations

• Only the developers having good knowledge in Oracle Technologies can make the pll changes
• Can only respond to certain Trigger Events
• Very difficult to maintain, if the number of customizations are more. Because same CUSTOM pll will be modified for each and every form customization.

Invasive Customizations

All of the above methodologies are EBS suggested methodologies to customize the standard forms. If the requirements are very complex and not able to achieve using any of the above approaches then the standard form needs to be modified. The changes done in the standard form is called invasive customization and not supported by Oracle. Also the changes won’t survive if any patches/upgrades applied and difficult to maintain.

Invasive Customizations can be avoided using different complex approaches. For example the requirement is having additional 20 columns in the standard form with complex validation logic. Since normally the standard table has only 15 attribute columns, this requirement can not be achieved using DFF. So, alternative approach could be modifying the standard form and tables. But this is invasive customization and not supported by Oracle EBS.

This invasive customization can be avoided by developing new form. Develop the new form and call the new form from the standard form either using forms personalization or CUSTOM pll change.

Some requirements can be achieved only by changing the standard form. In these cases take the copy of the standard form, rename it, do the necessary changes and register the form. Use the new form rather than standard form. Only the standard form will be affected, if any patches/upgrades applied. New copied form will work as existing.

Conclusion

The form can be customized in different ways. But it should be maintenance free and supported by Oracle. It is implementation team/developer responsibility to identify and use the right approach to make any standard forms.

If UI related changes then form can be customized using Oracle Folders. If that is not possible then it can be done using Forms Personalization or CUSTOM pll. Still the requirements are not meat then take the copy of the form and do the necessary changes.

If the requirements are to add additional columns then use DFFs. If there are not enough DFF attributes then create new form and execute it from the standard using SPECIAL menus or ZOOM.

If the requirements have very complex validation logics then try to achieve it using Forms Personalization or CUSTOM pll. If that is not possible then copy the form and do the changes in the copied form and register it. Use the copied new form rather than standard form.

For more details refer Oracle Applications Developer’s Guide and Oracle Applications User Interface Standards.