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
No hay comentarios:
Publicar un comentario