Here's a brisk case study into the power of core BI Publisher functionality in combination with the Oracle eBusiness Suite with a focus on Bursting.
The requirement here is to send Suppliers their Remittance Advice via email with a pixel perfect PDF attachment, of course with minimal coding. I'm limiting the functionality to email remittance advice for EFT payments in order to simplify this tutorial. Plus there are some coding shortcuts - your're more than welcome to provide free code fixes if you can spot them ;-)
Test Data
Firstly, lets look at the test data ingredients used:
- 1 Supplier: say "Virtuate"
- 1 Supplier Site: say "WELLINGTON" with Remittance Email address entered, Payment Method = EFT, Bank Account assigned
- 2 Invoices for the Supplier say INV0001, INV0002
- 1 Payment Batch say BURST_01 where payments have been made and payment batch confirmed
XML Data Source
Now, we need to create the Separate Remittance XML file somehow. Rather than reinvent the wheel, I started with a stock standard Payables Separate Remittance Advice report (APXPBSRA.rdf), and did the following:
- Setup a new concurrent program definition by copying the existing one, renaming, changing the executable, changing output to XML
- Added the new concurrent program to Payables "All Reports" request group
- Copied the report definition $AP_TOP/reports/US/APXPBSRA.rdf to a new report XXV8_APXPBSRA.rdf under modifications top directory
- Spiced up the new report with a couple of extra fields (Remittance Email, Fax number, etc)
- Restricted the data returned to only Suppliers Sites with a Remittance Email address (take this out later for fax/print etc).
- Restricted the data returned to only payments with payment method of EFT (checks have their on remittance advice).
- Ran to get the XML output. Note the <APXPBSRA><LIST_G_SEL_CHECKS><G_SEL_CHECKS> structure, we will use this later in the Bursting Control File
- And of course hacked the XML output - cut'n'paste style to add more data rather than having to key it (an additional supplier and invoices). Note this technique includes overwriting the output file $APPLCSF/$APPLOUT/o{REQUEST_ID}.req and follows the principles in my post on masquerading one request as another.
Layout
Okay, that sorts out the base report and data, now onto the fun stuff with a few screenshots:
- Create a new pretty RTF layout template
- Register the Data Definition
- Register the Template
- Run XML Report Publisher on the request that produced the prior XML data, and all lovely!
Bursting
Righto, now onto the Bursting part. We're going to:
- Create a Bursting Control File to email Suppliers
- Upload the control file to the Data Definition
- Test it out by calling the XML Publisher Report Bursting Program
- (Optional) Extend the Report to automatically submit the Bursting program
At this point please make sure you have done the following EBS bursting prerequisite steps:
Bursting Control File
Next, lets get into the Bursting control file and look at it a bit closer:
1. Create Bursting Control File to email Suppliers custom Separate Remittance Advice
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request select="/APXPBSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS">
<xapi:delivery>
<xapi:email server="smtp.yourdomain.com" port="25" from="youremail@yourdomain.com" reply-to ="">
<xapi:message id="${C_CHECK_ID}" to="${C_REMITTANCE_EMAIL}" cc="yourcc@yourdomain.com" attachment="true"
subject="Virtuate - Remittance Advice for Payment ${C_CHECK_NUMBER}">
Please find attached Remittance Advice for payment ${C_CHECK_NUMBER}.
Regards,
The Payables Team
Virtuate Limited
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output="Remit_Advice_${C_CHECK_NUMBER}" output-type="pdf" delivery="${C_CHECK_ID}">
<xapi:template type="rtf" location="xdo://SQLAP.XXV8_APXPBSRA.en.US/?getSource=true" filter=""></xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Hmm, what does all this jargon in the control file do? Well, here's a pretty picture that explains a lot of it:
2. Make sure it all works ... gotta make sure its the right flavor!
Navigate into Payables, Submit Request, XML Publisher Report Bursting Program, and specify the request from your last custom Separate Remittance Advice request.
Hey presto, take a look at your email:
Check the output:
3. (Optional) Extend the Report to automatically submit the Bursting program
Automatic Burst
Now, we don't want to have to manually (or via request set) submit the Bursting program every time we run the report, so let's automate that. By putting a parameter on the new Separate Remittance Advice report to control whether we Burst, and submitting a new request in the after report trigger, we can achieve this. We'll implement this is a similar fashion to my post on Beautiful Statements in 1 Easy Step.
So lets do this:
- Add parameter P_BURST to report and concurrent program definition (Yes/No).
- Add code to after report trigger.
declare
v_req_id number := 0;
begin
if nvl(:p_burst,'N') = 'Y' then
v_req_id := xxv8_xmlp_burst_pkg.submit_request_burst('XXV8_APXPBSRA',:p_conc_request_id);
if v_req_id > 0 then
srw.message(20002, 'Submitted request_id ' || v_req_id);
commit;
else
srw.message(20002, 'Failed to submit request');
end if;
end if;
end;
- Create PL/SQL package to do the submit of Bursting Program.
create or replace package XXV8_XMLP_BURST_PKG AUTHID CURRENT_USER AS
function submit_request_burst
( p_code in varchar2
, p_request_id in number
) return number;
end XXV8_XMLP_BURST_PKG;
/
create or replace package body XXV8_XMLP_BURST_PKG AS
function submit_request_burst
( p_code in varchar2
, p_request_id in number
) return number
is
l_req_id number := 0;
begin
if p_code = 'XXV8_APXPBSRA' then
l_req_id := fnd_request.submit_request('XDO','XDOBURSTREP',NULL,NULL,FALSE,
p_request_id);
end if;
return l_req_id;
end submit_request_burst;
end XXV8_XMLP_BURST_PKG;
/
- Test it all out!
Sweet, all automatic, working and ready for the primetime!
Issues
But let's note a few issues to look out for.
- I'm not very happy about "hardcoding" the SMTP server details in the control file. Would be great if BIP honoured either the Workflow SMTP setup or database smtp_server parameter. However, since the control file is in a structured data field in the database shouldn't be hard to write a script to update them all. Left to a future exercise though!
- I'm not very happy about "real" emails being delivered from Test/Development etc. environments. Would be great if BIP honoured the "Test Address" workflow parameter. Left to a future exercise, also related to issue Issue 1.
- Resolving items higher up the XML tree seems to be an issue with bursting in that they drop out and the solution as in my post on disappearing parameters doesn't work. Update: This functionality seems to be a known issue. Enhancement Request 6969869 has been logged for this issue. Thanks Lavina! In the meantime looks like XSLT transformation would come in handy, but that's a separate post!
- Minor issue that layout is not applied to the Bursting Request so no output, but can just run XML Report Publisher over it.
References:
Thanks to Dilip for the suggestions and request to put this together.
NB: In this tutorial I used Payables (SQLAP) as the application under which I register the new concurrent program and subsequently BIP Template and Data Definition, but custom Virtuate application for concurrent executable. This is to make the tutorial a working example without additional setup. You may want to use your modifications or similar custom application for all new entities.
PS. This Remittance Advice email bursting solution is an alternative to the standard Workflow Remittance Email solution.
PPS. If this all seems to hard and you are interested in BIP or Workflow Supplier Remittance Advice Email/Fax solution as a Consulting solution, contact me - see my Profile for email address.
