Steps to activate User Preferred Time Zone Support

From release 11.5.10 CU2, EBS introduced new feature called User Preferred Time Zone Support. Ie each user can specify their time zone preference, and the system will honor this preference for display and entry of date with time fields. Before 11.5.10 CU2, end users had to interact with the system in time zone set at database level.

To activate this feature following steps to be true or setup properly.

• Database should be Oracle RDBMS 9i or higher
• Environment variable ORA_TZFILE must be configured to use the time zone file timezlrg.dat rather than timezone.dat (Note: database needs to be restarted if this environment is changed).
• The database must be started in the standard corporate time zone
• Profile Server Timezone (SERVER_TIMEZONE_ID) must be set at the Site level, and must be set to the same standard corporate time zone as the database
• Profile Client Timezone (CLIENT_TIMEZONE_ID) must be set at the user level
• Profile Enable Timezone Conversions (ENABLE_TIMEZONE_CONVERSIONS) must be set to Yes at the Site level
• Profile Concurrent: Multiple Time Zones (CONC_MULTI_TZ) should be set to No at the Site level
• Environment variable FORMS60_APPSLIBS must be set in the Forms tier with APPS standards libraries like APPCORE FNDSQF APPDAYPK...

For more details, refer User Preferred Time Zone Support Documents and metalink notes 330075.1 & 340512.1.

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;