How to automatically send an EBS XML Publisher document by email

One of my clients was preparing quotes in EBS. The quote resulted in a PDF document prepared by an XML publisher based concurrent request. They were printing the quote and faxing it to the customer. It was pretty cumbersome process because they had to print it, then pick it up from the printer and only then fax it. When the fax was presented to the decision making executive it did not have the professional appeal of the nicely prepared colorful quote. But after all Oracle EBS is one of the leading enterprise systems so it should offer a way of emailing the documents automatically to the clients, shouldn’t it?

Well, not really…

I started browsing trough metalink and user forums on how to have an output of a concurrent request emailed from either a workflow or a nice button right on the quote and I found nothing. It seems like there were no good ways to do that so I had to build myself one.

Here is what needed to be done:

1. Modify the quote workflow and to add the 'concurrent request execution' and 'wait for the request completion' into the flow. This is added after a special event such as the quote approval.
2. Identify the directory where the concurrent request file, usually with the PDF or Excel output, is dumped and the name of the specific file itself
3. Write a PL/SQL package that puts the file into a BLOB and attach that package to a workflow attribute of a document type.
4. Use API from PO emailing to create ad hoc role. Workflow will not mail anything just to an e-mail address, it has to be mailed to a performer.
5. Create a notification and a message. Create a message attribute and point it to the global attribute with the PL/SQL BLOB containing the PDF file into the body of the message. This will create an attachment to your e-mail containing the PDF file. Assign the notification to a performer role created in the previous step.

This gets a bit more complex when your application and database run on 2 different servers. Your PL/SQL logic will not be able to get a file that resides on a different server. You can resolve it either by creating a link on the DB server to point to a directory on the application server, or FTP the file from the application server using the standard FTP package.

1. Modify the quote workflow and to add the concurrent request execution. Add the ‘Execute Concurrent Program’ (EXECUTECONCPROG) function to your workflow. This function can be found under the seeded ‘Concurrent Manager Functions’ (FNDCMSTD) item type. Go to the Node Attributes and specify:

i. Application Short Name -Short name of the application to which the concurrent program is registered.

ii. Program Short Name -Short name of the concurrent program to run. You will find this in the program definition screen.

iii. Number of Arguments -Number of arguments required for the concurrent program.

iv. Item Attribute Name- Name of the item attribute to store the concurrent program request ID. This is very important as it will be used to determine the name and location of the output file. In this example I use the REQUEST_ID as the attribute name.

v. Argument1, Argument2,...Argument100--Value of each concurrent program argument, ordered to match the correct syntax of the concurrent program. Up to 100 arguments are allowed, but you should only specify as many argument values as you define in the Number of Arguments activity attribute.

2. Identify the directory. The directory where the output is located and the file name can be queried from FND_CONC_REQ_OUTPUTS table, using the concurrent request id

3. Write a PL/SQL package that puts the file into a BLOB. This is where the hard part starts. First we need to create a workflow attribute of a document type:

In theory we can create an attribute of a ‘Document’ type and populate it by a PL/SQL code referenced in the Value field of the attribute definition

The syntax would include the itemtype and itemkey as variables

PLSQLBLOB:XX_ATTACHMENT.GET_DOCUMENT/'||itemtype||'|'||itemkey

It worked for me only if I passed the itemkey and itemtype as static values but never as variables. To work around that I left the value blank and populated it with a workflow function executed just after the concurrent request output. Here is the sample code:

PROCEDURE SET_ATTACHMENT_ID

IS

BEGIN

(itemtype in varchar2,

itemkey in varchar2,

actid in number,

funcmode in varchar2,

resultout in out nocopy varchar2)

wf_engine.setitemattrdocument

(itemtype=>itemtype

, itemkey=>itemkey

, aname=>'ATTACHMENT'

, documentid => 'PLSQLBLOB:XX_ATTACHMENT.GET_DOCUMENT/'||itemtype||'|'||itemkey);

END;

Now that we instructed workflow to populate the document attribute with the PL/SQL document it is time to define the execution of the document itself. Before I go to the execution package XX_ATTACHMENT.GET_DOCUMENT there is one pre-requisite step:

The directory where the output files are stored needs to be created in the database as the directory variable recognizable by PL/SQL. This is achieved with the following command

CREATE OR REPLACE DIRECTORY ‘CONC_OUTPUT’ AS ‘operating system path here’. You can find the path in the FND_CONC_REQ_OUTPUTS table.

Make sure the directory you are creating has all the proper permissions so the file can be accessed

Now we can go to the body that will get the concurrent request file put it into BLOB and associate with the workflow attribute

PROCEDURE GET_DOCUMENT

( document_id in varchar2,

display_type in varchar2,

document in out NOCOPY BLOB,

document_type in out NOCOPY varchar2)

IS

v_itemtype varchar2(200);

v_itemkey varchar2(200);

v_request_id fnd_conc_req_outputs.concurrent_request_id%TYPE;

src_lob BFILE;

dest_lob BLOB;

v_lob_len INTEGER;

v_offset NUMBER := 1;

v_buffer RAW(20);

v_buffer_size BINARY_INTEGER := 20;

v_FILEISOPEN INTEGER;

v_FILEEXISTS INTEGER;

get_file_name fnd_conc_req_outputs.file_name%TYPE;

BEGIN

-- since I put the itemtype/itemkey as the document ID this will get the item type out of it

v_itemtype := substr(document_id,1,instr(document_id,'|')-1);

-- this part will get the itemkey

v_itemkey := substr(document_id,instr(document_id,'|')+1,length(document_id)-2);

-- itemkey and itemtype are needed to get the value of the concurrent request id

v_request_id:= Wf_Engine.GetItemAttrText( itemtype => v_itemtype,

itemkey => v_itemkey,

aname => 'REQUEST_ID',

ignore_notfound => FALSE);

--which is needed to get the file name

SELECT substr(file_name,instr(file_name,'/',-1,1)+1,length(file_name))

INTO get_file_name

FROM fnd_conc_req_outputs

WHERE concurrent_request_id = v_request_id;

src_lob := BFILENAME('CONC_OUTPUT', get_file_name);

-- now that we know the file name and location we can load it to BLOB

DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE, DBMS_LOB.SESSION);

DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);

DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,

SRC_LOB => src_lob,

AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );

DBMS_LOB.CLOSE(src_lob);

-- Set the MIME type as a part of the document_type in the workflow attribute

document_type := 'application/pdf' || ';name=' || get_file_name;

-- write the BLOB to the workflow attribute

dbms_lob.copy(document, dest_lob, dbms_lob.getlength(dest_lob));

END;

The PDF output file has been located and added as a workflow attribute in the form of BLOB.

4. Use API from PO emailing to create ad hoc role. Now we have the file but still don’t have the role where we can email it. Here is a package that uses the standard API to add the ad hoc role based on the email address:

PROCEDURE SET_PERFORMER_ROLE

(itemtype in varchar2,

itemkey in varchar2,

actid in number,

funcmode in varchar2,

resultout in out nocopy varchar2)

IS

v_to_email varchar2(300);

v_header_id number;

l_adhocuser_lang WF_LANGUAGES.NLS_LANGUAGE%TYPE;

l_adhocuser_territory WF_LANGUAGES.NLS_TERRITORY%TYPE;

l_quote_email_performer WF_USERS.NAME%TYPE;

l_quote_email_add WF_USERS.EMAIL_ADDRESS%TYPE;

l_display_name WF_USERS.DISPLAY_NAME%TYPE;

l_quote_email_performer_prof WF_USERS.NAME%TYPE;

l_quote_email_add_prof WF_USERS.EMAIL_ADDRESS%TYPE;

l_display_name_prof WF_USERS.DISPLAY_NAME%TYPE;

l_performer_exists number;

l_notification_preference varchar2(20) := 'MAILHTM2';

l_when_to_archive varchar2(80);

l_archive_result varchar2(2);

BEGIN

-- Here I am getting the email address from a workflow attribute where I put it in one of my prior steps. Depending on your flow you can use a query here to get it

v_to_email:= WF_ENGINE.GETITEMATTRTEXT( itemtype => itemtype,

itemkey => itemkey,

aname => 'EMAIL_TO'),

ignore_notfound => FALSE);

-- I took a shortcut here and fixed the language to US to simplify my code

SELECT wfl.nls_language, wfl.nls_territory INTO l_adhocuser_lang, l_adhocuser_territory

FROM wf_languages wfl -- fnd_languages_vl flv

WHERE wfl.code ='US'; --flv.language_code AND flv.nls_language = L_contact language;

--* Bug 2989951. AdHocUser Name should be concatenation of the E-mail Address and the language */

l_quote_email_performer := v_to_email||'.'||l_adhocuser_lang;

l_quote_email_performer := upper(l_quote_email_performer);

l_display_name := l_quote_email_performer;

select count(*) into l_performer_exists

from wf_users where name = l_quote_email_performer;

/* Bug 2864242 The wf_local_users table is obsolete after the patch 2350501. So used the

wf_users view instead of wf_local_users table */

if (l_performer_exists = 0) then

-- This is the actual API call to add the ad hoc user to WF_ROLES

-- Pass in the correct adhocuser language and territory for CreateAdHocUser and SetAdhocUserAttr instead of null

-- if a doesn’t exists it will create it

WF_DIRECTORY.CreateAdHocUser(l_quote_email_performer, l_display_name, l_adhocuser_lang, l_adhocuser_territory, null, l_notification_preference, v_to_email, null, 'ACTIVE', null);

else

-- If a role with this email address already exists then find it

WF_DIRECTORY.SETADHOCUSERATTR(l_quote_email_performer, l_display_name, l_notification_preference, l_adhocuser_lang, l_adhocuser_territory, v_to_email, null);

end if;

wf_engine.SetItemAttrText ( itemtype => itemtype,

itemkey => itemkey,

aname => 'EMAIL_RECIPIENT_ROLE'),

avalue => l_quote_email_performer);

resultout:='COMPLETE:SUCCESS';

END;

In the above package I picked the email from 'EMAIL_TO' attribute and created a role from it which I assigned to the 'EMAIL_RECEIPIENT_ROLE' attribute.

5. Create a notification and a message.

Create Notification. In the Node tab select the performer from the item attribute from the previous step

Create a message

The &MESSAGE_ATTACHMENT attribute is included in the message body and needs to be defined and a message attribute sourced from the global attribute that contains our BLOB with the PDF quote

When creating attribute select its type as ‘Document’ and mark the ‘Attach Content’ checkbox. Point the value of the attribute to the global attribute containing the BLOB. This will create attachment to the email with the PDF or Excel document, depending on what your XML Publisher request generated.

Now attach your message to the notification that already has the performer created from the email address.

Here is how the addition to your workflow would look like