Email Purchasing Documents to Suppliers

Continuing with the emailing functionality, today’s tip is to automatically email reports from Oracle. Something like the PO Output for Communication report.

For instance have you ever wonder on how to send quotations to different suppliers automatically within Oracle EBS. Well, there are several ways to achieve this but I like this option since it allows you to customize almost anything you need and it can be incorporated into any custom report. What we are doing here is to setup a report that given a user parameter will automatically email the output. So here is a brief explanation o what we need to do it and how to do it:

We need:

1.- Oracle Reports v 6i or above.
2.- Oracle XML Publisher version 5.6.3 or above.
3.- A custom request for quotation (RFQ) report that groups by quotation-supplier and that is using an XML Publisher template.
4.- Some technical skills (as usual).

If you are new to XML Publisher, this tool is used as a standalone reporting tool. But when using it within Oracle EBS it is combined with Oracle Reports to provide fancy output formats (including PDF and EXCEL file types). You can setup templates as a Word Document, for this you need to install XML Publisher Desktop on your PC or laptop. XML Publisher came to solve the output formats lack of flexibility of Oracle Reports and it has some other enhancements as the one we will describe on this post. For a complete review on XML Publisher review the Oracle® XML Publisher Administration and Developer's Guide Release 11i Part No. E05321-013.

In general here is how we achieve it:

1.- We will use XML Publisher bursting feature. To enable this feature we need to setup the report so it can invoke the bursting program automatically.
2.- We will need to setup a control file within XML Publisher, this will tell the bursting program how to split the report and how it should be sending it
3.- We setup a new parameter in the report called P_BURST, which will allow the user to either burst or not at run time.

We will take for granted that the RFQ report has been developed already and that it uses an XML Publisher template as an output.

First, we will need to edit the custom report.

Open the rdf file in Oracle Reports. Go to Tools—Object Navigator.
Add the following user parameters:

P_BURST – type character, length 5
P_CONC_REQUEST_ID – type number, length 10

Now under the Object Navigator, go to the before report trigger and add the following line:
srw.user_exit('FND SRWINIT');

This will make Oracle Reports to save the id of the request in the variable P_CONC_REQUEST_ID

Now under the Object Navigator, go to the after report trigger and add the following lines:
req_id number;
begin
if :P_BURST = 'Y' then
req_id := apps.fnd_request.submit_request('XDO','XDOBURSTREP','',
'',FALSE,:P_CONC_REQUEST_ID,
'Y',chr(0),'','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','');
if req_id = 0 then
srw.message(100,'Bursting program failed’);
end if;
end if;
srw.user_exit('FND SRWEXIT');
return (TRUE);


Basically what this code does, is to call the bursting engine if the parameter P_BURST is received as 'Y'

This is everything that we will need to update in the report. Compile and upload the report to the server.

Next, create the control file. A control file is a set of XML instructions for the bursting program. Your control file should look like this (replace all the [] for <> I replaced them since it was being caught as XML in the post). You can use any text editor to create the control file:

1. [?xml version="1.0" encoding="UTF-8"?]
2. [xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting"]
3. [xapi:request select="/GPIN_POXRCOT2/LIST_G_DEFAULT_EFFECTIVE_DATE/G_DEFAULT_EFFECTIVE_DATE"]
4. [xapi:delivery]
5. [xapi:email server="localhost" port="25" from="${EMAIL_BUYER}"]
6. [xapi:message id="${PO_HEADER_ID}" to="${E_MAIL}" cc="${EMAIL_BUYER}" attachment="true" subject=" Solicitud de cotizacion"]
7. Dear Supplier,
8. Attached to this email you will find a request for quotation for you to review and answer.
9. Kind Regards
10. Purchasing Department
11. [/xapi:message]
12. [/xapi:email]
13. [/xapi:delivery]
14. [xapi:document output = "Cotizacion ${FOLIO} ${NOMBRE}" output-type="pdf" delivery="${PO_HEADER_ID}"]
15. [xapi:template type="rtf" locale = "esa-MX" location="/ppesci/applmgr/CUSTOM/xbol/11.5.0/templates/gpin_poxrcotms.rtf" filter =""]
16. [/xapi:template]
17. [/xapi:document]
18. [/xapi:request]
19. [/xapi:requestset]

This two lines are needed on every control file and they set up the character set and the api that will be used

1. [?xml version="1.0" encoding="UTF-8"?] 2. [xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting"]

This line tells the bursting engine how it should split the ouputs. For this example below are the groups within Oracle Reports data model and they should be splitted by supplier

3.. [xapi:request select="/GPIN_POXRCOT2/LIST_G_DEFAULT_EFFECTIVE_DATE/G_DEFAULT_EFFECTIVE_DATE"]


The following lines tell the bursting engine that it should deliver an email and the email server and port to be used (this might change from one server to another, you should check this with your system administrator). You also set the from, to, cc, attachment and subject parameters for the email.You can use fields from the report here and call them with the syntax ${NAME}. For this example in the report we obtain the supplier email ${E_MAIL} and the buyer email ${E_MAIL_BUYER} and use them to dynamically set the from and to email addresses.

4. [xapi:delivery] 5. [xapi:email server="localhost" port="25" from="${CF_EMAIL_ELABORO}"] 6. [xapi:message id="${PO_HEADER_ID}" to="${E_MAIL}" cc="${CF_EMAIL_ELABORO}" attachment="true" subject=" Solicitud de cotizacion"]

--------------------------------------------
Input the email message for the supplier
---------------------------------------------

And last, set up the file name that the attachment will have. You will also need to upload the template file (rtf file) t o an accessible location in the server. For this example we will be emailing a PDF file.

14. [xapi:document output = "Cotizacion ${FOLIO} ${NOMBRE}" output-type="pdf" delivery="${PO_HEADER_ID}"] 15. [xapi:template type="rtf" locale = "esa-MX" location="/ppesci/applmgr/CUSTOM/xbol/11.5.0/templates/gpin_poxrcotms.rtf" filter =""]

Once you have created the control file go to the XML Publisher Administrator responsibility.

Log into Home – Data Definitions

Query the data definition for the RFQ report

Click on the bursting control file button and upload the control file you just created.

Apply changes.

XML Publisher will validate that you have uploaded a valid XML File, be sure to check all the closing tags if you receive an error message.

Now we have successfully setup the report and the template for bursting.

We now log into the System Administrator responsibility and go to Applications – Define Programs. (The menu access may vary from one responsibility to another)

Query the RFQ concurrent program.

Go to the parameters window

Add a new parameter called P_BURST with a value set of YES_NO. The value set should pass the values Y and N to the report.

Save your work.

Now try to execute the report and set the bursting parameter to N.

The report should end normally.

Execute it again and now set the bursting parameter to Y.

Once the request finishes you will see a new request being executed (this is a bursting engine). Verify that the bursting engine ends normally. You should now have a new mail on the emails obtained in the report. Review the log file to check if there something wrong. Usually if emails are not being set is due to a missing email address (either supplier or buyer)

Now you have successfully configured the bursting functionality. Purchasing users (specially the really lazy ones) will love this feature since. This can be setup to any custom report, so that means that it can be used for RFQs as well as purchase orders.


Diego

Email alert for approved Purchasing Requisitions

Today's tip is for those implementers who are dealing with users who expect a little more than what the Oracle EBS does..you know, users who want the system to do things like:

* Notifications on what work they shall be doing
* Warnings when you mismatch 1,000.00 for 10,000.00
* Email the office's restaurant menu daily
* Weather analysis for the weekend on Cancún

You know, just simple things.

But hey, don't get me wrong, users might get a little too enthusiastic on what Oracle EBS does, but part of the problem comes with our pre-sales and sales buddies. Sometimes they get a little too excited when explaining all the ERP features to the users, but well what can we do this is how it works.

Anyway, after that subtle comment on over-enthusiastic users and our non-technical buddies on sales, Have you ever wonder how can you notify to buyers when a purchasing document has been approved? Simple right, just tell the user to log into his/her notifications within Oracle EBS and done. Well not quite, this tip is for users that are a little more reluctant to the whole ERP thing. Users who prefer this kind of notifications on email.

This can be achieved by using Oracle Alerts. Oracle Alerts is a EBS module that allow users to configure actions that trigger on predefined events. Most common type of alerts are email and notifications.

This functionality is quite simple to implement:
First, you will need Oracle Purchasing installed and running (of course).
Second, you need Oracle Alerts up and running.
Third, verify that Workflow Mailer is up and running (this is how email notifications are sent)
Fourth, you will need some technical skills for this, if you are one of those consultants that claim to be only functional please go to SAP, if you are on Oracle you should have some technical skills.

Later versions of Oracle Alerts use Workflow Mailer to perform email notifications, but you should verify which version are you on, since the sendmail command was used on previous releases.

For this example we will be notifiyng the buyer when a purchase requisition is been approved. Here is how is done:

1.- Configure an Alert
Go to Alert Manager responsibility
Log into Define form
Type a name and a description for the alert
Set it for type Event
Under Application choose Purchasing and under table choose PO_REQUISITION_HEADERS_ALL





Under the SQL box type the following statement:

SELECT prh.segment1 num_req, prh.description,

prh.creation_date fecha_creacion, prh.approved_date fecha_aprob,

prh.preparer_id id_usuario, pap.full_name solicitante, decode(prh.org_id,119,'XXX@XXX.com,120,XX@XXX.com')

INTO &num_req, &descr, &fec_creacion, &fec_aprob,

&id_usuario, &solicitante, &mail_comprador

FROM po_requisition_headers_all prh,

po_requisition_lines_all prl,

per_all_people_f pap

WHERE prh.requisition_header_id = prl.requisition_header_id

AND approved_date IS NOT NULL

AND pap.person_id = prh.preparer_id

AND NVL (prh.cancel_flag, 'N') = 'N'

AND prh.authorization_status = 'APPROVED'

AND prh.org_id in (119,120)

AND prh.rowid = :rowid

Just some points to notice on the SQL:

* For this example we have hard-coded the buyer's email depending on the org_id. This can be set dynamic since there a buyer field in the requisition but the users will have to type the buyer for every line in the requisition. Once you got the buyer you can get the email for that user.
* The :rowid variable restricts the statement only to the current record (the one that is being updated, inserted)
* The fields preceded by (&) within the into clause, are the fields passed as variables to the alert. This fields can be used later in the message or email.

Validate the SQL statement and confirm that there are no errors.



Now go to the Actions b utton and create a new action called SEND_MAIL (this can be any name you want)
Set the action level to Summary








Now go to the Action Details button and set the action type as Message. You will see a kind of email form. Under the TO field type &mail_comprador which is the variable name on the SQL statement for the buyer's email. Set a Subject for the email
Enter the text that you wan t the buyer to see. In this text you can include any variable from the SQL statement (such as requisition number, preparer, etc)







Close the window and now go to Action Sets and type an action set name.

Go to Action Set Details and unde r the Members tab verify that the action is set correctly





Close the window and now go to the Alert Details and verify that you have the correct operating units for the alert to work on.



Now you have succesfully created the alert. Now try approving a requisition that meets the criteria in your alert. Afterwards run the Workflow Background Process concurrent program (emails won't be send before this concurrent is executed) and wait for the email. It shouldn't take more that a couple minutes to arrive.

Hope it helps.

Diego

Welcome Post

Welcome,

If you have arrived here you are for sure either an Oracle consultant or an Oracle user, hence you are one of the thousands that have to deal with this ERP on a daily basis. We often receive negative comments about ERPs in general, so what this blog will be focus on is to provide some simple tips (maybe a few will be a little more complex) on how to make life easier when using Oracle EBS.

I have to say that even tough many solutions posted here are highly technical I’m a Functional Consultant for the Supply Chain modules. Due to the lack of technical consultants and a slight technical background I have, I got my hands really dirty in the technical stuff. If you are a functional consultant that doesn’t like to get involved into the technical well you are free to stay out of it, but from my short experience a functional-technical consultant can work much faster to solve problems than a pure functional.

The tips and solutions that I will be posting here are some of the stuff developed within the few years I have been working on Oracle EBS. I will be posting stuff on several modules such as Inventory, Purchasing, Order Management and also on BI Publisher, Oracle Alerts, Reports and so on.

Feel free to comment on any of the posts and also I you think you have an interesting solution and want me to post it here we can work on that too.