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.

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.

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