Cache Clearing Technique

When you assign a new responsibility and it does
not show up for a long time, you can manually flush the
mid-tier cache.
Responsibility = Functional Administration
Core Services : Caching Framework : Clear All Cache
• This also resolves other issues related to lookups, profile options,
etc (basically any data that is cached by the Apps Tier)

Current Workflow Functionality

• Oracle Workflow Best Practices
Release 12 and Release 11i
Metalink Note: 453137.1

Current – Recent Releases
• Metalink Note: 464727.1
Oracle Workflow Documentation Updates for
11i.ATG_PF.H.delta.7 (RUP 7)
• Metalink Note:737857.1
Oracle Workflow Documentation Updates for Release
12.0.6
• Metalink Note: 565731.1
Oracle Workflow Release Notes, Release 12.1.1

• ATG, RUP6, MetaLink Note: 760386.1
How to enable Bulk Notification Response Processing
for Workflow in 11i
• This feature allows users to respond to multiple
notifications with a similar response.

• ATG RUP 7, adds the ability to include a PL/SQL
Document Attribute in your notification subject line.
• This is really helpful if having to deal with translations in
emails.

ATG RUP 7, There is now a PL/SQL API call to allow a
message to be sent via email that does not have to be
associated with a Workflow Item Type/Key.
• Sends a message to be dispatched through e-mail by a
notification mailer.
• The message content and recipients provided in the
parameters do not need to be associated with any
workflow process or directory service roles.
• The message content must be complete, preformatted,
and email-ready, and should conform to the content type
specified.

Use the ‘Workflow Directory Services
User/Role Validation’ concurrent program
(FNDWFDSURV) to validate and correct the
information about user/role associations.

Also when you assign a new responsibility and it does
not show up for a long time, you can manually flush the
mid-tier cache.
Responsibility = Functional Administration
Core Services : Caching Framework : Clear All Cache
• This also resolves other issues related to lookups, profile options,
etc (basically any data that is cached by the Apps Tier)

Workflow to BPEL Migration

A good reference on migrating to BPEL is:
Migrating to BPEL from Oracle Work Flow
An Oracle White Paper
March 2008

Account Generator Workflows

Inventory COGS Account Generator
Item Type = INVFLXWF
Display Name = ‘Inventory Cost of Goods Sold Account’
Run by the concurrent program ‘Create Intercompany AP Invoices’, short name INCIAP

OM COGS Account Generator
Item Type = OECOGS
Display Name = ‘OM : Generate Cost of Goods Sold Account’
Run by the concurrent program ‘Interface Trip Stop’, short name WSHINTERFACE

To Debug change the said profiles
Account Generator:Purge Runtime Data = No
Account Generator:Run in Debug Mode = Yes

Some learnings in Oracle Workflow Notification

Message Templates : Oracle comes seeded with Message templates in the System: Mailer Item type (WFMAIL). If there are any extra messages that comes in the notification apart from that mentioned in the message body, the same comes from the Message template.

Points to note
1. Do not modify Oracle Supplied Message templates
2. One can add new custom Message templates in the Item Type System: Mailer or in your own Item Type.

To reference the Custom Message Template
1. Create a message attribute by name "#WFM_OPEN_MAIL_FYI" under the message which has your information.
2. The value much have the template message given in format :

Message Attirbutes : Allow you to modify fetures of Workflow notification, without having to create seperate workflow mailers. Various Special Message attributes like few given below
#WFM_FROM - Change the from email address in notification
#WFM_REPLYTO - Changes the Reply To Email

Document Message Attribute : Allow you to build a message body from a query. These type of attribute references PL/SQL procedures to build a text stream.
Document Attributes Values plsql:/
procedure = PL/SQL Procedure
document_identifier = usually a concatenation of ITEM_TYPE:ITEM_KEY
example
apps.wf_engine.setitemattrdocument
(itemtype => 'WWAPAPR',
itemkey => l_curval,
aname => 'WW_OUTPUT_ATTACH',
documentid => 'PLSQLBLOB:ww_ap_pay_approval_wf_pkg.ww_set_ntf_attr/'
|| TO_CHAR (g_request_id)
);

PROCEDURE ww_set_ntf_attr (
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT BLOB,
document_type IN OUT VARCHAR2
)

document_id has the value passed from g_request_id

Workflow messages are not versioned and so any addition in the message attributes will effect the immediatly running notifications.

Workflow Notification Testing
1. Modify the Workflow Mailer Configuration using OAM
2. Enter an email address for the Test Address

Further reference
http://www.jrpjr.com
http://www.norcaloaug.org

Differences in R11 and R12 tables

Please go through the below link to find the difference between R11 and R12 tables.

Click here

How to Submit A XML Publisher Report from Script

Follow the steps to Submit A XML Publisher Report from Script

1. Select and assign the layout before submitting the request as below.
l_attached := fnd_request.add_layout ( template_appl_name => 'XXXX'
,template_code => 'MFG_LABEL_PRN'
,template_language => 'US'
,template_territory => 'en'
,output_format => 'PDF'
);

2. Call the Submit_Request API next.
ln_req_id := fnd_request.submit_request (
'mfg', 'MFG_LABEL_PRN ', 'MFG Label', TO_CHAR(SYSDATE), TRUE);

How to call a Java Stored Procedure from PL/SQL Procedure

Below is an example to call a Java Stored Procedure from PL/SQL Procedure

1. Define a Java Stored Stored procedure as below in the database.

DROP JAVA SOURCE APPS."DirList";

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED apps."DirList" AS import java.io.*;
import java.sql.*;
public class dirlist
{
public static void getlist(string directory)
throws sqlexception
{
try
{
file path = new file( directory );
string[] list = path.list();
string element;
for(int i = 0; i < list.length; i++)
{
element = list[i];
#sql { insert into xx_dir_list (filename)
values (:element) };
} catch (exception e);
{
system.out.ptintln(e.getmeaasage);
}
}
}
}


2. Call the above Java Stored Stored procedure "DirList" from the PL/SQL Procedure as follows.

CREATE OR REPLACE PROCEDURE APPS.get_dir_list (p_directory IN VARCHAR2)
AS
LANGUAGE JAVA
NAME 'DirList.getList( java.lang.String )';
/