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