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.

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.

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.