Extensible Flexfield (EFF)

Extensible Flexfield (EFF) is a new feature added in Fusion Application with existing Descriptive & Key Flexfields. It is similar to descriptive flexfield, but with the ability to add as many context-sensitive segments (attributes) to a flexfield as they need.

Some of the key benefits using Extensible Flexfields are

1. Number of configurable segments are not fixed unlike fixed number of segments in DFF
2. Attributes can be grouped into one or more attribute groups (ie contexts) rather than only one in DFF
3. EFF supports one-to-many relationships between the row and the extended attribute rows
4. Improved access controls than using DFF. It is possible to control the view and edit the attributes based on the context configurations.

To know more about this, refer Fusion Application Developer’s Guide & Extensibility Guide.

How to use ADF in E-Business Suite?

Oracle recently announced the different types of supported integration between EBS and ADF.

1. Integration to EBS using the Oracle SOA Suite and Application Development Framework 11g (ADF 11g). This type of integration typically uses the AppsDataSource feature but no other parts of EBS SDK.
2. Launch ADF Application from EBS, in this case the Oracle ADF user interface is launched from EBS home page. There is no further user interface interaction from ADF application with EBS.
3. Integration that allows users to move back and forth between the ADF user interface and EBS. For example, a user could go to an ADF page from an OA Framework page and back again, or ADF components could be embedded into an OAF page. This requires some context being shared between OAF and ADF, as well as session management between the two systems.

However option 2 and 3 supported only from R12, 3rd one is still not fully supported. For more detailed information refer Oracle Support Document (1296491.1).

Responsibilities using Concurrent Program

We know to use any Concurrent Program first it needs to be added to the Request Group and Request Group needs to be attached to Responsibility. Then we can able to submit the program using program request form.

One program can be attached to many Request Groups. Again Request Group can be attached to many Responsibilities. Because of One-Many-Many relationship, it is very difficult to find out responsibilities using particular concurrent program in UI forms.

But we can get the list from database using the following SQL...

SELECT
FRS.responsibility_key
,FRS.responsibility_name
,FRG.request_group_name
,FCP.concurrent_program_name
,FCP.user_concurrent_program_name
FROM
fnd_concurrent_programs_vl FCP
,fnd_request_group_units FRU
,fnd_request_groups FRG
,fnd_responsibility_vl FRS
WHERE FCP.concurrent_program_name = (Program_Name)
AND FRU.request_unit_id = FCP.concurrent_program_id
AND FRG.application_id = FRU.application_id
AND FRG.request_group_id = FRU.request_group_id
AND FRS.group_application_id = FRG.application_id
AND FRS.request_group_id = FRG.request_group_id

SQL to get list of Scheduled Concurrent Programs

Any Oracle Application (EBS) user can schedule Concurrent Programs, if he has access to it. Down the line, it is very difficult for users/administrators/developers to remember list of schedules concurrent programs. Following SQL will help to get the latest list of Scheduled Concurrent Programs.

SELECT FCR.request_id REQUEST_ID
,FCP.concurrent_program_name PROGRAM_SHORT_NAME
,FCP.user_concurrent_program_name PROGRAM_NAME
,FNU.user_name SUBMITTED_BY
,TO_CHAR(FCR.requested_start_date
,'DD-MON-YYYY HH24:MM:SS'
) REQUEST_START_DATE
,'Every '|| DECODE(LENGTH(FCL.class_info)
,39,FCL.class_info
,SUBSTR(FCL.class_info,1,INSTR(FCL.class_info,':',1)-1)||' '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1)+1,1)
,'N','Minute(s) '
,'D','Day(s) '
,'H','Hour(s) '
,'M','Month(s) '
)
|| 'after '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1,2)+1,1)
,'S','Start '
,'C','Completion '
)
|| 'of prior request'
) SCHEDULED_INTERVAL
,NVL(TO_CHAR(FCL.end_date_active
,'DD-MON-YYYY'),'forever'
) ENDING_ON
FROM APPS.fnd_concurrent_requests FCR
,APPS.fnd_concurrent_programs_vl FCP
,APPS.fnd_user FNU
,APPS.fnd_conc_release_classes FCL
WHERE FCR.phase_code = 'P'
AND FCR.status_code IN ('I','Q')
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCR.requested_by = FNU.user_id
AND FCR.release_class_app_id = FCL.application_id
AND FCR.release_class_id = FCL.release_class_id
ORDER BY FCP.concurrent_program_name
, FCR.requested_start_date

Submitting XML Publisher Report Concurrent Program from PL/SQL

XML Publisher template will be attached as layout when we submit concurrent program using request form based on the program short name. Whereas, if we submit the program using FND_REQUEST.submit_request API then we will find only XML output, template won’t be used.

To avoid this issue, program layout needs to be set using FND_REQUEST.add_layout API with template application, code, language, territory and output format before submit request.

DBMS Change Notification

This is about DBMS Change Notification, enhanced feature introduced from version 10g.

DBMS Change Notification can only be used for tables those are having length (including schema name) less than 30 characters. For example, Oracle Project module PA.PA_STD_BILL_RATE_SCHEDULES_ALL table length is more than 30, so this table can not be used for DBMS Change Notification.

Also DBMS Change Notification treats particular change as bulk if numbers of changed records are more than 80. In this case ROWID will be empty. So we won’t know the affected records.

Java Stored Procedure in Oracle

Though we can write business logic, data validation, exception handling in pl/sql, still we need to use languages like C, Pro*C, Java for complex and system programming in Oracle.

Recently we had a requirement to convert the Images and store it into Oracle. As we know pl/sql won’t support graphics I used Java program for image conversion and used it in my plsql.

Java program should have public method with required parameters and return statement if needed. For example, let as take addition of two numbers. Two numbers should be input parameters and the method should return sum of these two.

Compile the java file and keep the class file in folder/directory registered in Oracle Database. We can create new directory if needed using following syntax.

CREATE DIRECTORY Bfile_dir AS '/usr/temp';

Store the class file into database using following syntax.

CREATE OR REPLACE JAVA CLASS USING BFILE (Bfile_dir, 'Addition.class');

loadjava executable can also be used instead of the above statement. Now java class is available to use. Create function/package to use in pl/sql.

CREATE OR REPLACE FUNCTION add_numbers(no1 NUMBER, no2 NUMBER) RETURN NUMBER AS
LANGUAGE JAVA NAME 'Addition.get(int,int) return int';

I used Oracle 10g Release 2. Refer Oracle Database Java Developer’s Guide for more details.

Single BPEL Process for Multiple Operations using Pick Activity

Usually we will create individual BPEL Processes for each Operation in either synchronous or asynchronous. This blog will help to build single BPEL Process for more than one Operation for exceptional cases like requesting system might have restricted number of adapters or ports to connect to other systems. This can be done using Pick activity.

1. Get target system WSDL file
2. Create Empty Project
3. Create Partner Link for the WSDL file
4. Select Pick Activity and drag and drop into Process
5. Don’t forget to select Create Instance check box in Pick activity
6. Add OnMessage Branch as much as the number of Operations as you have
7. Go to any one OnMessage. Select Partner Link created, select the Operation and create Local Request Variable.
8. Add activities and partner links you wanted for the selected operation
   Finally add Reply/Callback activity based on type of Operation.
9. Repeat this for all remaining Operations
10. Deploy into BPEL Server
11. Initiate the process from where ever you want. Note that this process can not be initiated from 10g BPEL Console. May be this is a bug.

Validate & Get Values from XML using PL/SQL

There are various methods available in PLSQL to read, write and validate XML. Following sample code has simple approach to read, validate and get element value from XML using DBMS_XMLDOM API. This sample will read employee name and print it in the console.

DECLARE
lc_return_msg VARCHAR2(1000);
lc_xml_buf VARCHAR2(2000);
lc_xml_value VARCHAR2(2000);
lc_resp_xml XMLType;
lc_xml_doc DBMS_XMLDOM.DOMDocument;
lc_xml_ndoc DBMS_XMLDOM.DOMNode;
lc_xml_docelem DBMS_XMLDOM.DOMElement;
lc_xml_node DBMS_XMLDOM.DOMNode;
lc_xml_childnode DBMS_XMLDOM.DOMNode;
lc_xml_nodelist DBMS_XMLDOM.DOMNodelist;
BEGIN
-- XML Message
lc_return_msg := '<?xml version="1.0" encoding="utf-8"?>
<Department>
<Code>001</Code>
<Name>Sales</Name>
<Employees>
<Employee>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<DoB>01/01/1975</DoB>
</Employee>
<Employee>
<FirstName>Adam</FirstName>
<LastName>Ford</LastName>
<DoB>12/03/1983</DoB>
</Employee>
</Employees>
</Department>';

-- Validate XML
lc_resp_xml := XMLType(lc_return_msg);

-- Create DOMDocument Handle
lc_xml_doc := DBMS_XMLDOM.newDOMDocument(lc_resp_xml);
lc_xml_ndoc := DBMS_XMLDOM.makeNode(lc_xml_doc);

DBMS_XMLDOM.writeToBuffer(lc_xml_ndoc, lc_xml_buf);

-- Get all elements
lc_xml_docelem := DBMS_XMLDOM.getDocumentElement(lc_xml_doc);

-- Get Result Elemement
lc_xml_nodelist := DBMS_XMLDOM.getElementsByTagName(lc_xml_docelem, 'FirstName');

-- Get Second Employee Name
lc_xml_node := DBMS_XMLDOM.item(lc_xml_nodelist, 1);
lc_xml_childnode := DBMS_XMLDOM.getFirstChild(lc_xml_node);
lc_xml_value := DBMS_XMLDOM.getNodeValue(lc_xml_childnode);

-- Print second employee name
DBMS_OUTPUT.put_line(lc_xml_value);
END;

APPS Context in BPEL OA Adapter

Oracle Application Adapter (OA Adapter) connects to EBS Database as user SYSADMIN and responsibility System Administrator. This needs to be changed for different reasons like Security, Using Multi Operating Units.

If user and responsibility are know and won’t change then this can be hardcoded in OA Adapter Partner Link Created. Search for element Responsibility, change the Username, Responsibility and save the file.

If Contexts are dynamic then follow these steps:

1. Create new Message Type Variable for Header_msg in AppsContextHeader.wsdl file.
2. Assign Username, Responsibility & ORG_ID variables
3. Go to Adapters tab in OA Adapter Partner Link Invoke activity and Select the above created new variable as Input Header Variable.

XML Publisher Report: Few Tips

Here are few useful tips to design XML Publisher RTF Templates.

1. Printing Report Parameters
In report, we need to print Report Parameters and might need to use it multiple times. To declare the parameter, create new field at beginning of the report with help text <xsl:param name="<ParameterName>" xdofo:ctx="begin"/>. Create one more new field with help text <?$new_field?> to print wherever parameter needs to be printed.

For example, assume P_EMPLOYEE_NAME is one of the report parameter. To use that in RTF, create new filed named P_EMP_NAME with help text <xsl:param name="P_EMPLOYEE_NAME" xdofo:ctx="begin"/>. To print this parameter value create one more new field with name <?$P_EMP_NAME?> as help text.

2. Fit into single page
Oracle Report Layout has “Fit to Page” property. If we set this to “Yes” then if the page is not enough to fit the content then entire content will be printed in next page. This is known and wildly used property. Is this possible in RTF Template?

Yes. First identify contents that need to fit into a page. Insert a table with only one row and column. Keep the identified contents inside this table. Select Table Property. Navigate to “Row” tab. Uncheck “Allow Row to break across Pages’ check box if it is checked. This will make sure the contents won’t split across pages.

3. Font Support
You might have noticed some time Report Output won’t match with RFT Template Font. This is because PDF Engine will support only specific fonts. If you use fonts that are not supported by PDF Engine then engine will use it is default font. So try to use fonts that are PDF engine supports.

Some time we have to use specific fonts that PDF engine didn’t support. Best example is company name, logo etc. Mostly we use these in report headers. Simple workaround is copy the logo or name or any text that we want it in specific font. Convert into to image and use that image wherever we need. Of course it will affect the performance if we use too many images in a template.

Dynamic Workflow Notification Attachments

We know Workflow Notification can have attachments also. This is very useful feature and widely used in real-time. Big limitation in this method is if we know the number of attachments then this approach is easy to implement. Otherwise if we do not know the number attachments needs to attach, it is very difficult to use this approach. For example one PO can have multiple attachments. If the requirement is to include all of them in PO Approval notification then it is difficult using this approach.

One of the workaround could be listing all the attachments in the notification body (rather than as attachment) with dynamic hyperlink to the attached documents. This is can be done either using PLSQL or OA Framework document type.

Example:
Create simple workflow with message and notification. This workflow can have user_id, resp_id, resp_appl_id attributes. This will be used to set application context.

Create Document Type attribute to generate notification body.

In the PLSQL API which is used to generate notification body:
Initialize Application Context using available attribute values in workflow

Frame SQL query to get documents that needs to attach from fnd_lobs table.

Use FND_GFM and FND_WEB_CONFIG APIs to get document URL.

Write HTML TABLE tags and add File Name as one of the column with above hyperlink to the document. Use target as _blank to open the document in new browser.

Notification body will be generated with all available attachment documents. Clicking the link will download the document in the browser.

Limitation:
1. If user preference is set to send notifications over e-mail then user should have access to the application when he try to access the attachments from e-mail.
2. Document should be available in EBS fnd_lobs table.

Oracle 11g Database New Features for Developers

• Table can be altered to Read Only or Read Write.

• A new XMLIndex_clause lets you create an XMLIndex index for XML data.

• In CREATE TABLE, A new virtual_column_definition create a virtual column.

• SELECT has new PIVOT syntax that rotates rows into columns. A new UNPIVOT operation to query data to rotate columns into rows.

• TRUNCATE statement was presented as a single statement with separate syntactic branches for TABLE and CLUSTER. That command has now been divided into TRUNCATE CLUSTER and TRUNCATE TABLE for consistency with other top-level SQL statements.

• Enhancements to Regular Expression (REGEXP_INSTR, REGEXP_COUNT and REGEXP_SUBSTR) Built-in SQL Functions.

• The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop and transfers control to the end of the loop).

• Allow Sequences in PL/SQL Expressions. PL/SQL now recognizes the SQL pseudo columns CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM.

• Both native dynamic SQL and the DBMS_SQL package have been enhanced and support dynamic SQL statement larger than 32KB by allowing it to be a CLOB.

• Named and Mixed Notation in PL/SQL Subprogram Invocations are allowed.

• PL/SQL provides a function result cache. To use it, use the RESULT_CACHE clause in each PL/SQL function whose results you want cached. Because the function result cache is stored in a shared global area (SGA), it is available to any session that runs your application.

• PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code.

• Subprogram Inlining replaces a subprogram call (to a subprogram in the same PL/SQL unit) with a copy of the called subprogram.

• Oracle Application Express provides an application development tool that is built into the database and is installed in the database by default.

• Create Rules and Actions for DML Events With Rules Manager. In 10g only INSERT is supported.

• Rules Manager now includes a PL/SQL API to store reusable and shareable rule conditions in SQL WHERE clause format.

• Unicode 5.0 Support

• New Compound Trigger Type: Compound trigger has a section for each of the BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT timing points. All of these sections can access a common PL/SQL state.

• Client-Side Query Cache feature enables caching of query result sets in client memory. The cached result set data is transparently kept consistent with any changes done on the server side.

• Enable the XDB HTTP Server for SOA feature allows Oracle Database to be treated as simply another service provider in a service-oriented architecture (SOA) environment.

• Automatic SQL Tuning with Self-Learning Capabilities: Automatically detects high-load SQL statements and then tunes them automatically as needed in the maintenance window by creating appropriate SQL Profiles. It also issues proactive advice to create new access structures such as indexes that will significantly improve the performance of the high-load SQL statements.

• Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases.

• Database Resident Connection Pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented.

• Online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc. expect triggers), provided the redefinition does not logically affect them.

• Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

Refer Oracle 11g Database Documents to learn more and to find examles

Oracle 10g Database New Features for Developers

• This version of the Oracle Database has been designed to meet two key goals: Reducing the cost of manageability and Delivering increased performance for all key workloads.

• Major new high-availability features are also provided, including new Flashback capabilities (including new Flashback Database and Flashback Table) that reduce the downtime caused by human errors. In addition, support for rolling upgrades has been provided to reduce the downtime associated with database and application upgrades.

• Application development improvements include a new built-in application development environment, support for a high-performance and highly integrated XML capability across the entire technology stack, and a framework provided with the database that enables desktop and middle-tier applications to retrieve and extract data from the database using standard Web Services mechanisms.

• Automatic Storage Management (ASM) automates and simplifies the optimal layout of datafiles, control files, and log files. Database files are automatically distributed across all available disks, and database storage is rebalanced whenever the storage configuration changes.

• Automatic Workload Repository (AWR) is the latest evolution of statspack which provides the central information store for all Oracle 10g self-tuning functionality.

• Rename already existing tablespace. No longer have to create a new tablespace, copy the contents from the old tablespace, and drop the old tablespace.

• Automatic Shared Memory Tuning automates the configuration of System Global Area (SGA) memory-related parameters (buffer cache, shared pool) through self-tuning algorithms.

• SQL Tuning Advisor is a new server tool that eliminates manual tuning of SQL statements as an input and gives advice in the form of precise SQL actions for tuning the SQL along with their expected performance benefit.
• All of the DML statements (INSERT, UPDATE, DELETE, MERGE) now have an error logging clause.

• Conditional Compilation: Enables to selectively include code depending on the values of the conditions evaluated during compilation. Also useful when you want to execute debugging procedures in a development environment, but want to turn off in other instances.

• Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package

• Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database.

• Database Change Notification: Receive notification whenever a change occurs in the database on the result set of registered queries. This feature enables any cache or object holding query results to ensure that it contains the very latest data.

• Enhancements in Spatial and Multimedia data management

• New top-level SQL statements have been added to support Automatic Storage Management

• New syntax in ALTER SYSTEM that lets you flushes the buffer cache of the system global area (SGA)

• New syntax in ALTER TABLE that lets you manually compact the table segment, adjust the high water mark, and free the recuperated space

• New category of collection functions lets you manipulate nested tables and varrays like SET,CARDINALITY,POWERMULTISET

• New set of aggregate functions to support statistical analysis of data

• Added hierarchical pseudo columns CONNECT_BY_ISLEAF and CONNECT_BY_ISCYCLE for better usage

• LOB column can be used in UPDATE clause when creating an update DML trigger

• New locale-independent format elements (in particular for Number and Date Formats) have been added to the tables

• New Conditions for nested tables like IS A SET, IS ANY, IS EMPTY, IS PRESENT, MEMBER

• FORALL statement can handle associative arrays and nested tables with deleted elements

• Oracle can issue warnings when you compile subprograms that produce ambiguous results or use inefficient constructs. PLSQL_WARNINGS initialization parameter is controlling this.

• Supports Unicode 4.0

• DBMS_FILE_TRANSFER new API to make binary copies of files on the local server or to transfer files between the local server and remote servers

• MERGE statement enhancement to select rows from one or more sources for update or insertion into a table or view

• Introduces the new IEEE floating-point types BINARY_FLOAT and BINARY_DOUBLE

• UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE)

• UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package, but this required knowledge of the SMTP protocol.

Refer Oracle 10g Database Documents to learn more and to find examles.

Oracle 9i Database New Features for Developers

• New SQL enhancements to bring Oracle in line with the ANSI/ISO SQL

• Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.

• Declarative primary key, unique key and foreign key constraints can now be defined against views

• Multitable Inserts concept can be used single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables.

• Allows the renaming of table columns and constraints

• Insert into or update a SQL table by specifying a PL/SQL record variable, rather than specifying each record attribute separately. Also select entire rows into a PL/SQL table of records, rather than using a separate PL/SQL table for each SQL column.

• Create collections that are indexed by VARCHAR2 values

• UTL_FILE contains several new functions that useful to perform general file-management operations from PL/SQL

• PL/SQL supports the complete range of syntax for SQL statements, such as INSERT, UPDATE, DELETE, and so on. If received errors for valid SQL syntax in PL/SQL programs before, those statements should now work.

• The new data type TIMESTAMP records time values including fractional seconds. New data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE allow adjusting date and timing values to account for time zone differences.

• Perform bulk SQL operations, such as bulk fetches, using native dynamic SQL (the EXECUTE IMMEDIATE statement)

• MERGE Statement: This specialized statement combines insert and update into a single operation. It is intended for data warehousing applications that perform particular patterns of inserts and updates.

• SELECT can have multiple groupings in the GROUP BY clause, for selective analysis across multiple dimensions.

• Enhancements in Oracle Text formerly interMedia Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database.

• Includes Oracle XML DB, which is a set of built-in high-performance storage and retrieval technologies geared toward XML

• Supports JDK 1.3

• Full support for ANSI-style CASE statements and expressions

• Custom aggregate functions can be defined for working with complex data

• Oracle Ultra Search is able both to search the contents of a database to find documents, newspaper articles, and other information stored inside a database and to search the contents of static HTML pages.

• Oracle Spatial Enhancements: Location capabilities in Oracle9i and Oracle Spatial have been greatly enhanced. Content stored in Oracle9i can now be associated with related location criteria and services. New support for online mapping, yellow pages, driving directions, traffic, and geocoding services allow online content to be merged with database content.

Refer Oracle 9i Database Documents to learn more and to find examles.

JDeveloper Patch with OA Framework Extension

We know JDeveloper is used for OA Framework (OAF) extensions and customizations for self service forms. This note gives the instruction on how to find right JDeveloper patch for creating OA Extensions with EBS Release 11i and 12.

EBS has lots of versions like 11.5.9, 11.5.10, 12 etc. Developer should use the correct version of JDeveloper with OAF extension. When you create extensions or customize EBS OA Framework pages, you must use the version of JDeveloper shipped by the EBS product team. The version of JDeveloper is specific to the ATG patch level, so there is a new version of JDeveloper for each release of ATG patch-set.

To determine which patch to use, you can check the framework version in your instance by using http://(host):(port)/OA_HTML/OAInfo.jsp, then choose the matched JDeveloper patch.

For Example if the instance base URL is http://vis.oracle.com:8080/ then type http://vis.oracle.com:8080/OA_HTML/OAInfo.jsp. Note down OA Framework Version. Go to Metalink Note 416708.1, find out the JDeveloper patch matching with OAF version and download. Unzip the downloaded file.

We can also find useful informations in metalink.oracle.com.

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;

- 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