Problem
You want to email a report, but you don't want it to be an attachment. You want it embedded in the email. (Mobile users like this).
Solution
It's very similar to the normal method of using a control file with a few things to watch out for.
I like to burst via a PL/SQL Package. I'll use a very simple example (one field).
Step 1:
Create your code for creating the XML Output.
PROCEDURE put_xml(p_tag IN VARCHAR2, p_value IN varchar2) IS
BEGIN
fnd_file.put_line(FND_FILE.OUTPUT,CHR(10) || '<' || p_tag || '>' || REPLACE(replace(replace(p_value,'&','&'),'>','>'),'<','<') || '</' || p_tag || '>');
END put_xml;
PROCEDURE report(ERRBUFF OUT VARCHAR2,RETCODE OUT VARCHAR2) IS
CURSOR c_main IS
SELECT incident_id
FROM xxgns_lt_tickets_interface
WHERE new_ticket = 'Y';
v_request_id NUMBER;
BEGIN
fnd_file.put_line(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8"?>');
fnd_file.put_line(FND_FILE.OUTPUT,'<XXGNS_LIVETIME_INTERFACE_RPT>');
fnd_file.put_line(FND_FILE.OUTPUT,'<REPORT>');
FOR v_main IN c_main LOOP
fnd_file.put_line(FND_FILE.OUTPUT,'<BDY>');
PUT_XML('INCIDENT_ID',v_main.incident_id);
fnd_file.put_line(FND_FILE.OUTPUT,'</BDY>');
END LOOP;
fnd_file.put_line(FND_FILE.OUTPUT,'</REPORT>');
fnd_file.put_line(FND_FILE.OUTPUT,'</XXGNS_LIVETIME_INTERFACE_RPT>');
v_request_id :=
fnd_request.submit_request ('XDO',
'XDOBURSTREP',
NULL,
NULL,
NULL,
fnd_global.conc_request_id,
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Request submitted for bursting: '||v_request_id);
END;
Things to note:
1.The name of your report and repeating groups. (XXGNS_LIVETIME_INTERFACE_RPT/REPORT) You will need these in your bursting control file.
2. A call to submit bursting (XDOBURSTREP). If you don't put this code in, nothing happens.
Step 2:
Create your concurrent program definition, calling your package. Output type is XML (though you can leave it as text until you are happy with the XML output.
Step 3:
Create your template. Be as fancy as you want. My template builder is broken so I create an extremely basic one manually. New word doc > save as Rich Text (RTF).
<?for-each:BDY?>
<?INCIDENT_ID?>
<?end for-each?>
Step 4:
XML Setup: Create your data definition and template.
Some things to note at this stage:
Output format is HTML
Template Tpe is RFT
Note the language and territory of your template, they will be needed in the bursting control file.
Step 5:
Create you bursting control file (Actually, you would create this before step 4 and attach it to the DD, but it makes sense to talk about the BCF after showing you the template)
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
<xapi:request select="/XXGNS_LIVETIME_INTERFACE_RPT/REPORT">
<xapi:delivery>
<xapi:email id="barry.brierley@oracle.com" server="serverr_name" port="25" from="noreply@oracle.com" reply-to="noreply@oracle.com">
<xapi:message id="1" to="barry.brierley@oracle.com" attachment="true" content-type="html/text" subject="TEST">
Please review the attached document
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output="1" output-type="html" delivery="1">
<xapi:template type="rtf" location="xdo://GNS.XXGNS_LIVETIME_INTERFACE_RPT.en.00?getSource=true"/>
</xapi:document>
</xapi:request>
</xapi:requestset>
Important things to note:
"<xapi:request select="/XXGNS_LIVETIME_INTERFACE_RPT/REPORT">"
This needs to match your XML output.
"<xapi:email id="barry.brierley@oracle.com" server="serverr_name" port="25" from="noreply@oracle.com" reply-to="noreply@oracle.com">"
You need to know your server settings. the email ID is not important, but it you were going to send different emails by group, it would have to be unique.
"<xapi:message id="1" to="barry.brierley@oracle.com" attachment="true" content-type="html/text" subject="TEST"> "
Attachment="true". It doesn't actually create an attachment.
This is where your "to" and "subject" of your email are set. You could quite easily get these from your XML output and there lots of examples in forums on how to do this.
"<xapi:document output="1" output-type="html" delivery="1">
<xapi:template type="rtf" location="xdo://GNS.XXGNS_LIVETIME_INTERFACE_RPT.en.00?getSource=true"/>"
Very important.The output type tells it to embed the document in the email (the whole point of showing you this post)
Also note the call. "GNS.XXGNS_LIVETIME_INTERFACE_RPT.en.00". This is the application short name, template code, language and territory (which in my case was null). This catches a lot of people out, they fo for "en.US", and don't upload their template as a US territory template.
Result
It's a very simple output, but it's clearly in the message body, and you can pretty the template up all you like.
Not the "Please review the attached document" in the BCF is ignored.This is a throwback from using a sample of a BCF which sends output as an attachment. I'm fine with that, I want the output to come from my template
Acknowledgment
https://community.oracle.com/thread/1070989
Comments
Post a Comment