Wednesday, March 26, 2008

BI Publisher EBS Bursting 101: A tutorial/case study including Bursting Control File to email Suppliers Remittance Advice in 1 easy step

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:

  1. Setup a new concurrent program definition by copying the existing one, renaming, changing the executable, changing output to XML
  2. Added the new concurrent program to Payables "All Reports" request group
  3. Copied the report definition $AP_TOP/reports/US/APXPBSRA.rdf to a new report XXV8_APXPBSRA.rdf under modifications top directory
  4. Spiced up the new report with a couple of extra fields (Remittance Email, Fax number, etc)
  5. Restricted the data returned to only Suppliers Sites with a Remittance Email address (take this out later for fax/print etc).
  6. Restricted the data returned to only payments with payment method of EFT (checks have their on remittance advice).
  7. 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
  8. 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:

  1. Create a new pretty RTF layout template
  2. Register the Data Definition
  3. Register the Template
  4. 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:

  1. Create a Bursting Control File to email Suppliers
  2. Upload the control file to the Data Definition
  3. Test it out by calling the XML Publisher Report Bursting Program
  4. (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:

  • (Optional, but highly recommended) Upgrade to 11.5.10.2 / XMLP 5.6.3 or higher (ATG RUP5 or higher is nice)
  • Apply 5968876 XDO:EBS-BURSTING INTEGRATION PATCH
  • Restarted your applications processes - or the button to upload your bursting control file won't appear!
  • Set the Temporary Directory under XML Publisher Administrator, Administration, General - to e.g. /tmp, or you'll get error message:
    java.lang.NullPointerException at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory(JCP4XDOBurstingEngine.java:413)
  • Assign Concurrent Program "XML Publisher Report Bursting Program" to the appropriate Request Group, e.g. "All Reports" / Payables
  • Make sure you have an SMTP server that you can send your email through!

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.

  1. 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!
  2. 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.
  3. 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!
  4. 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.

278 comments:

1 – 200 of 278   Newer›   Newest»
Nitin Jain said...

Nice article Gareth. I see that BIP 5.6.3 has better capabilities when compared to its previous versions. Got to explore more.

//Nitin

Greg said...

Very nice article. Very Helpful.

Nitya Ryali said...

Hi Gareth
I fumbled upon on your blog when looking for account generator.

your site has got loads of interesting & useful articles.

Thanks for sharing them.

Kishore Ryali

Nitya Ryali said...

Hi Gareth

Regarding the hard coding of host name & port.

If we put them in custom profiles and get the values in xml data file by storing them in report placeholder columns, Can we use those placeholder columns in xapi:email node of Document Processor?

Regards,
Kishore Ryali

Gareth said...

Hi Aragorn,
Thanks for the comments.
Haven't tried yet but I think you could create profile options for server/port, I would default them from the WF Notification mailer setup.
Watch out for Issue 3 though!
Gareth

Tash said...

Hi, i did a report in report 6i, and generated the xml and using it in BI Publisher. I want to know, i need to get the P_CONC_REQUEST_ID. how do i do that. because i need it to display. do i need placeholder columns.

Gareth said...

Hi Tash,
There are a couple of methods. Easiest might be to use placeholder or column sourced from report parameter :P_CONC_REQUEST_ID. Of course as long as you have called
SRW.USER_EXIT('FND SRWINIT');
and have p_conc_request_id parameter setup.

Note Issue 3 in my post, i.e. put the column/placeholder at the level at which you want to burst.

Gareth

Nitya Ryali said...

Hi Gareth,
Issue 3 is still a prob.
Thanks for addressing.

Kishore Ryali

Unknown said...

Hi Gareth,

Great article!
I am getting errors on this line:
location="xdo://${TEMPLATE_APPL_SHORTNAME}.${TEMPLATE_CODE}.en.00/?getSource=true"

Looks like it can't handle the variables. What am i doing wrong?

Gareth said...

Hi Matt,

What's with the .00 after the .en? I'll give the variables a try next time I do some BIP work.

Gareth

bstatham said...

Great article! I wish you would have written it a year ago. : ) I actually implemented something similar, but without the bursting. It was my first time experimenting with BI Publisher. I have a totally separate custom PL/SQL process that runs automatically after our EFT payments are generated. For each check id that I extract, I call a conc req to create the XML, and the PDF output, then I call a second conc req to email the PDF attachement to the supplier. Needless to say I kick-off a lot of conc reqs each night. Bursting would have helped me out a lot... now I know. Thanks!

Unknown said...

Gareth,
I tried yr sample solution and it worked but I am not getting results when I am putting multiple document data in XML meaning if I am putting 2 invoices data bursting program is only processing first invoice and not next one.
Any idea...

Unknown said...

Very nice article Gareth.. I'm a regular vistor to your blog.. Recently i started playing around BI Publisher Stuff.. I have one question related to this post.. If i want to print only remittance info per page per supplier, what should i do in the template.. What i meant by that if a supplier has one invoice that particular page should not have other supplier's remittance info..

Thanks for your help,
Scharan

Gareth said...

Hi Nagarjun,
Make sure that wherever you expect your output to go to that both invoices have the right delivery address present, e.g. both have email address.
Make sure you don't have any if statements that would cause the invoice not to print.
Make sure you are doing burst and any "for-each" at the right level.

Regards,
Gareth

Gareth said...

Hi Shree,
Use the @section on your for each invoice call, ie. something like < ? for-each@section:XXXX ? >

This would put each invoice on its own page.

Regards,
Gareth

Unknown said...

Hi Gareth,

The .00 at the end of my url means i do not have a territory.

Did you already try my variable error?

Gr, Matt

Gareth said...

Hi Matt,
I've tried variables in the location tag and probably getting same error as you, error in bursting concurrent request log file:
[EXCEPTION] The URL ( xdo://${C_APP_SHORT_NAME}.${C_TEMPLATE_CODE}.en.US/?getSource=true ) is invalid.

And the email is sent out without the PDF attachment.

Guess it might be time for enhancement request!

Alternatively you could use a template with variable subtemplates that match the logic you require.

Gareth

Ike Wiggins said...

Nice article about bursting. I have been using for year and half and it is such a fantastic tool.

Might I make suggestion. Using reports6i to generate xml is really kind of an obsolete technology. It's like fred flinstone peddling his feet to move vs the a regular car. You might want to start using data templates. One of the biggest benefits you will really see is the interopability that they deliver (genenerating xml from anywhere and in real-time (portal, adf, apex, 11i, JEdwards, Peoplesoft, OAF, etc). You would really be suprised how much similiar the technologies are and how much time you will save using data templates.

Gareth, here me out, download the BIPublisherIDE (it's free too!) and give it a go. It's really not to difficult and I can guarantee your development time will improve. It will take a couple of days but you will be well on your way to doing all bip reporting done locally on your pc, for any environment, etc.

http://bipublisher.blogspot.com/2008/03/bi-publisher-bipublisheride.html

Let me know if your interested and I can help you get up and running.

Ike Wiggins
http://bipublisher.blogspot.com

Gareth said...

Hi Ike,

I really value your comments. At the end of the day it doesn't really matter how the XML is generated, BIP can use it. Oracle Reports isn't obsolete yet although it is on the crossroads. The old rule "don't change it if it ain't broke" comes to mind as well!

For the majority of us using EBS with most on 11.5.10.2 or thereabouts the easiest, fastest and least costly method to generate XML is that really really simple change in the "Output Type" drop down from PDF (or Text) to XML and you're away laughing with the power of BIP. And hence this is the method used in this post. Conversion from RDF to PL/SQL / dataTemplate is an option, but that is more $$$ and time!

If any new reports are needed or any major modifications are required, absolutely go for it and switch to dataTemplates!

Taking a look at the BIPublisherIDE is definitely on my todo list, sounds like an excellent tool to add to the bag of tricks!

Thanks,
Gareth

Unknown said...

Alternatively we used the filter tag, not real nice but it works fine!

Unknown said...

When we start our concurrent program to burst the document to a file system location, the result of that concurrent request output is null. So you don't get the document in the *.out file. In the logfile of the bursting request there is a null output file. Is there a way to get the document output in the *.out file? It is no option to start an other request with only the XML Report Publisher.
Does anybody got an idea?

Gareth said...

Hi Matt,

Not sure I understand your requirements.

Bursting requires the original request's file which is your *.out file.

Additionally, the point of bursting is delivering to multiple destinations, so having only one target *.out file doesn't really make sense. If you only have one target file, then you don't need to burst at all! Just copy the *.out file to the target (using bursting, or print driver or utl_file or java).

Gareth

Kirti Gupta said...

Hi Gareth,

I want to know one thing about remittance advice notification, if i have to add some more information in standard FYI:Remittance Advice Notification,like address , phone, mail id,Invoice description how can i achieve this ? Shall i have to change the package.procedure(get_paid_invoice) where notification message with formatting is done in standard workflow APPEWF ?

Gareth said...

Yes - copy and modify.

G.

mahesh said...

Hi gareth,

i am using the same control file but i am getting the body as attachment only but not in the message please suggest me.

Regards,
Mahesh

Gareth said...

Hi Mahesh,
Not sure whats happening there, double check everything.
Gareth

mahesh said...

Hi Gareth,


Please find the sent to your mail bursting control file i am using,i am getting body as an attachment in the mail but not as a simple body.

also please suggest me how to do FNDLOAD for bursting control file.

Regards,
Mahesh

mahesh said...

Hi Gareth,

i am following the below said procedure for AR Statement print:

1.Place the Customized Report XXARXSGPO.rdf in the custom_top.
2.There will not need to Register a new Report. A Link has to be made on the seeded report ARXSGPO to a another new Custom report MTUARXSGPO.rdf
3.Make a Symbolic Link
mv ARXSGPO.rdf ARXSGPO.rdf.original
ln -s /u05/DEV/appldev/devappl/XXAR/11.5.0/reports/US/XXARXSGPO.rdf ARXSGPO.rdf .
4.Run the same Seeded Program.

But the problem in this approach is user cannot see the standard statement print program ouput hecan see the customized report output.

but now the user want both the reports.

can u suggest any approach which will make both the standard and customized AR Statement Print working fine at a time.

Regards,
Mahesh

Gareth said...

Hi Mahesh,

Good question. The underlying issue here is that the Statement Generate program (ARXSGP) automatically submits the Print Statements report (ARXSGPO) within the same concurrent request, and since ARXSGP is a spawned program (compiled C) its tough to change it!

Off the top of my head, firstly you could provide a mechanism whereby the user can switch the executable on the ARXSGPO program between the custom and standard report. So lets say you set up your new custom executable XXARXSGPO, then you do a Forms Personalization or CUSTOM.pll extension to add two new SPECIAL menu items on the Statements submission form:
1. Set Standard Statements
2. Set Custom Statements
Then the user would click this and behind the scenes it would update fnd_concurrent_programs with the corresponding executable_id.

Another option would be to have multiple layouts within XXARXSGPO.rdf and switch somehow, e.g. based on User level profile option (that the user could change). This way means no unsupported mod like the update to fnd_concurrent_programs in the first option.

Anyone else got an easy answer?

Regards,
Gareth

Unknown said...

Hi Gareth,

Automatic Burst doesn't work for me. After changing error message in AfterReport trigger in report to:
srw.message(20002, 'Failed to submit request('||fnd_message.get||')');
I received in log file:
MSG MSG-20002: Failed to submit request(Encountered an error while getting the ORACLE user account for your concurrent request.
Contact your system administrator.)
Could You help me with that please?

Pawel

Gareth said...

Hi Pawel,
Make sure that the Application that you registered your concurrent programs under have an Oracle Account registered via System Administrator, Security > Oracle > UserID
Gareth

mahesh said...

Hi Gareth,

Thanks for the reply, i got the result of two seperate programs custom amd standard AR Statement, i got it by combination of shell script and using it from the form ARXSUPST using the host command,i used two shell script on in new form instance trigger it will create shell link from standard report to custrom report and other shell script in key exit trigger it will delete the shell link when user closes the form after request completes.but i am facing one problem here if user closed the form after submission the concurrent program will be in the middle of execution and it will be terminated with error,can u suggest if there is any way to submit shell script from after report trigger in report.

Regards,
Mahesh

Gareth said...

Hi Mahesh.

Yeah for shell script in after report trigger, just create external procedure and PL/SQL package wrapper that you call from the after report trigger.

Gareth

mahesh said...

Thanks gareth,

But there is one problem here, if submit the shellscript from after report trigger,and the report error out then shellscript wont be called and link created wont be deleted.can u suggest for any other alternative.

Regards,
Mahesh

Gareth said...

Hi Mahesh,
I'm not convinced your solution is really required - I haven't seen your full requirements, but one way would be to delete and create the link in the same shell script!
Gareth

mahesh said...

Hi gareth,

can't understand how to go with single shell script.the requirement is the link to be created at starting of the report and link to be deleted after the report completion.any way to achieve this from single shell script.
i will mail both of the shell scripts to your mail.

Thanks,
Mahesh

iamchandru said...

Hi Gareth,
What we need to make the bursting engine work in Release 12, what patch or prereqs.?
thanks
mouly

Gareth said...

Hi iamchandru,

There were a couple of fixes to bursting in 12.0.3, but I'd apply 12.0.4 http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6272680

Regards,
Gareth

Unknown said...

Hi Gareth

I hope You could help me to resolve following issue with special characters.
I've got
xapi:message ... subject="${SUBJECT}"
and field subject in my report contains specjal char: "John O'Neil"
but I received an e-mail with: "John O&apos;Neil"

What can I do to received "John O'Neil"?

Thanks

Gareth said...

Hi Pawel,

Haven't hit that yet - Best to log SR or post to Forums as my work queue is a little overbearing at present.

Gareth

Tom said...

Hi Gareth,

Great article - very helpful.
Nothing is easy... When I run "Bursting Statement Generation Program (XML Publisher Report Bursting Program)" to test if everything works I get the following in the log:

(I broke the lines to fit)
--Exception
null
java.lang.NullPointerException
at oracle.apps.xdo.oa.cp. JCP4XDOBurstingEngine. getControlFile(JCP4XDOBurstingEngine.java:401)
at oracle.apps.xdo.oa.cp. JCP4XDOBurstingEngine. runProgram(JCP4XDOBurstingEngine.java:237)
at oracle.apps.fnd.cp.request. Run.main(Run.java:161)

Then my OPP log says:
[9/23/08 11:47:20 PM] [STATEMENT] [43818:RT1135372] Failed to process the template
and
Caused by: oracle.xdo.parser.v2.XMLParseException: Start of root element expected

I cannot attach the control file since the site gives me an error:
Your HTML cannot be accepted: PHP, ASP, and other server-side scripting is not allowed

Can you help please. I spent long hours trying different things but no luck.

Gareth said...

Hi Tom,
Sounds like syntax problem ... check your xml header or drop me the files you are working with.

Gareth

Tom said...

When I try to paste the control file I get an error at the blog.

Your HTML cannot be accepted: PHP, ASP, and other server-side scripting is not allowed.

Is there any other way I can send them to you.
And by the way thanks for your quick response.

Gareth said...

Drop me an email ggr888 [AT] gmail [D0T] com

Tom said...
This comment has been removed by the author.
Tom said...

Well, after long battle I finally solved the problem.
Here is shortly what caused the problem...
When I defined the Data Definition I selected Custom Application as the owner of it. Since the process I am using it for is the seeded AR Statement process I should use the Receivables Application as the owner of my Data Definition. Ones I changed this everything started to work.

Gareth one more time thank you for your help.

Tom said...

Does anyone know why the "Bursting Statement..." concurrent job produces nice "Bursting Status Report" but it produces nothing if called from after report trigger of the report.

Tom said...

Actually the "out" file exists but it is an raw XML file.

Unknown said...

Nice Article Gareth. I have one question that i did not see addressed here. Is it possible to email the entire PDF document that gets generated from the XML/BI Publisher to an email address specified in the parameter screen of the report.
I tried to get the param to print on the XML output by modifying the report (rdf) but could not see the email address on the xml output. Not sure if i am missing out something very basic..

Thanks in advance for the help.

-venky

Gareth said...

Hi Venky,

Yes, but I think with the current bursting implementation, the parameter value would have to appear in the XML tree below the group upon which you burst.
So if your xml is like (excuse the spaces - blogger doesn't like xml):
< rowset>
< param1>some@email< /param1>
< row>< field1>xxxx< /field1>< /row>
< row>< field1>yyyy< /field1>< /row>
< /rowset >
and you burst at rowset/row then param1 won't be accessible as it is higher up the tree. XML needs to be like:
< rowset>
< row>< param1>some@email< /param1>
< field1>xxxx< /field1>< /row>
< row>< param1>some@email< /param1>
< field1>yyyy< /field1>< /row>
< /rowset>

Give it a try and let us know.

Regards,
Gareth

Unknown said...

Gareth:

Thanks for the tips. That worked. I am now getting the mail. However i am still facing a couple of issues.
1. Issue reported earlier by mahesh where i am getting the body of the mail also as an attachment.
2. Automatic busrting is not happening. Running the concurrent is sending the mail.
I would appreciate any obvious mistakes/pointers that i need to take care of so that the above two work!!
Thanks for all the help
Regards
Venky

Gareth said...

Hi Venky,

1) Re the email body, I'll get back to you.

2) Auto burst, Make sure the pl/sql code to submit the bursting concurrent program is using the correct application (and check in the log file of the report to see if it is submitting the conc request).

Regards,
Gareth

Gareth said...

Hi Venky,

Re the email body, try adding:
content-type="text/html"
after
attachment="true" in the bursting control file. (Thanks Mahesh).

Regards,
Gareth

Unknown said...

Gareth: Thanks, i was just about to reply to you indicating that that is exactly what i did to get the body in the mail as opposed to an attachment.

For the other problem, i had to explicitly declare and initialize the variables and that helped kick off the bursting program. This is what i added to the bursting package which helped.
select requested_by
into v_user_id
from fnd_concurrent_requests
where request_id =p_request_id;

select frt.responsibility_id,frt.application_id
into v_resp_id,v_appl_resp_id
from fnd_responsibility_tl frt,
fnd_user_resp_groups frgp
where frt.responsibility_id =frgp.responsibility_id
and user_id =v_user_id
and responsibility_name ='ABC'
and nvl(frgp.end_date , sysdate+ 1) > trunc(sysdate)
and rownum=1;

fnd_global.apps_initialize
(v_user_id,
v_resp_id,
v_appl_resp_id
);

Gareth said...

Hi Venky,

You shouldn't have to do that if you're submitting through the concurrent manager, is you program in the correct request group.

Gareth

Unknown said...

Hi Gareth

This is a great tutorial on how to use the XML publisher for emailing.

For the Bursting Control File, I need to include french characters to the body of the email but fails to load to the Data Definition.

Error is:
The uploaded file ####.xml is invalid. The file should be in XML-BURSTING-FILE format.

Thank you

Gareth said...

Hi Trina,

Glad you like it!

Haven't tried, but are you putting an "encoding" in your bursting XML Control file in the xml header? e.g. western european ISO8859P1 or similar?

Regards,
Gareth

Unknown said...

Hi Gareth

Yes, i am using the encoding from your sample control file:

encoding="UTF-8"

Would this be why?

Also, I was encountering the issue where the email body was an attachment but resolved it in this blog by adding content-type="text/html". However, the email body appears as one continuous line. It does not recognize the page breaks that I have included in the control file.

Thanks again for the help!

Unknown said...

Hi Gareth

Solved the french character issue.
Had to specify:

encoding="windows-1252"

Thanks!

Gareth said...

Perhaps try a different encoding ... and log an SR too. Please post solution here if you find one!

Gareth

Alka said...

Hi Gareth,

Thanks for all the useful information.....it must be difficult to come up with such an organized piece on blog, esp. finding time to do it while working.

I had a question - Is it possible to conditionally change the message of the email in control file? I have a need for that and would really appreciate if you had an idea on how to do it.

Thanks and regards,
Alka

Gareth said...

Hi Alka,

Thanks for the comments on the blog, glad you find it useful. This post took me a lot longer to put together than coding it! And I think a little extra effort goes a long way to help other developers out... a stack of ideas yet to be published.

As you can see you can put XML elements in the email message, so there is your conditional message - populate the right message at XML extraction time.

Alternatively, the control file is just an XML blob in the database that you could programatically change.

Post a comment with how you get on.

Regards,
Gareth

Alka said...

Gareth,

Thanks for the idea.....works like a charm!!! I created the email body in a formula column in my .rdf, and referenced it in the bursting control file. Sometimes things are much simpler than they seem, and this was surely a great example :-)

Alka

Unknown said...

Gareth,
Thanks for the beautiful article about XML Bursting.

I tried to use the xml publisher bursting for one of the concurrent requests, the program ends normal.
But i havnt recieve the E-Mail.
In the concurrent log it says

oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] Message has not been sent for following invalid addresses: gjagesh@gmail.com

Please let me know any setup need to be done in the server.

Am running this in a development server.

Thanks,
Jagesh.

Gareth said...

Hi Jagesh,

Can you send emails through the email server from the command line on the server to that email address?

Regards,
Gareth

Unknown said...

Thanks Gareth,
I check with our DBA, because of the development server the mails can be sent only to the common mail id.
I tested with that and is working fine.
Once again thanks for ur gr8 blog.

Unknown said...

Hi Gareth
I succesfully used your tutorial on bursting to send emails.
Thanks a lot for that.
I now want to tackle FAX.
How does that work: like an email I believe fax also has a server to send faxes.My company does not want to use CUPS, what else could you suggest and also is it possible for you to show an example for Fax.
Please advice.

Thanks Again
Ray

Gareth said...

Hi Ray,

Your fax needs to be setup on an IPP server. Alternatively what I do (for testing) is just use an internet based faxing service where you can email the recipient. Personally I don't run a real fax any more, I just use (New Zealand based) www.2talk.co.nz for all my VoIP, Fax, Phone Forwarding but haven't tried bursting with them! I'm sure you can find a suitable provider.

If you go down the Fax and IPP Fax Server route, the readme for patch 5968876 has example fax delivery conrol file.
http://updates.oracle.com/download/5968876.html

Regards,
Gareth

Unknown said...

Hi Gareth

I have already had the patch 5968876 applied and hence could add a control file to the data definition.I know the syntax for the fax in the control file; I guess I am stuck because we don't have a fax server to carry out the faxing. The Designer and Developer guide show CUPS being used as a fax server. Since our Server people don't want to go with CUPS, I am searching for a new Fax server that would work with XML/BI Publisher. I know only CUPS is supported by Oracle. I want something that I could use through my control file( email and fax through control file- Bursting concept)
Anyhooo, I followed your example to the tee and email worked perfect thru the Bursting concept.Thanks for that.
I will keep you posted about what we decide for Faxing and if you have any ideas or come across any other 3rd party fax server which I can reference through my control file, please let me know.
< xapi:fax server="ipp://myprinter.us.oracle.com:631/printers/dummyPrinter" >
< xapi :number id= "fax1" >919999999999 < / xapi:number >
< / xapi:fax >

Also do you have any idea about Faxination Account and how to reference that in the control file.

Regards
Ray

Gareth said...

Hi Ray,

Sorry, haven't done fax. However, there is also the "Custom" delivery channel, where you build your own delivery method via the API.

Regards,
Gareth

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...

Hi Again Gareth!


I am currently working on Bursting concept in XML.
I have succesfully created the control file and got the email to work as I had mentioned in my previous posts. However I am not able to get Fax functionality to work at all.
My Unix guy has installed CUPS and he is able to fax from the cups server to the fax machine but when I submit the concurrent request nothing happens with the fax. There is no activity at all.
here is the fax section from control file without spaces:
< (space) xapi : fax server="http://abccups21:631/printers/efax" (space) >
< (space) xapi:number id="FAX1" (space) > ${FAX_NUMBER} < (space) /xapi:number (space) >
< (space) /xapi:fax (space) >
We were able to fax directly from the fax server to a fax machine.
We also installed the cupserver on the instance from which we run concurrent request and ran the lp print command and it faxes fine from that instance too.
But when I run the concurrent request, email is sent but there is no fax recieved on the fax machine.
As mentioned above I have shown the syntax I used for fax in the control file.
In the rdf , I have defined a user parameter , fax_number and use select :p_fax_number from dual
In the concurrent program define I have defined the parameter fax_number with token p_fax_number.
So control file , rdf and conc. prog parameters are taken care of. Is there anything I am missing.
Gareth can you please throw some light on this issue.
Thanks in Advance.

Ray

Unknown said...

I am facing some problem in this issue.
Please help me....

I need to develop mechanism for varying XML Publisher output by instance in other words Instance based Output of XML Report Publisher without customizing particular rdf or package.

for this we need to call particular template or format that template depend upon Instance name. So we need instance name in XML file but we don't have instance name in that particular XML Data file.

How can I add Instance name in rtf template and XML data file without customizing XML Source package or rdf Report to compare that Instance name with current instance name in RTF Template.

For example:
If Instance name = Production then call watermark of company logo otherwise simple report output.

Note: don’t have Instance name in XML Data file.


Thanks in Advance
Nilesh

Gareth said...

Hi Nilesh,

I assume you are running with EBS, there are a number of options:

1. From memory I think there is a way to pickup the value of a concurrent parameter from within the RTF without the data being in XML - just can't seem to find out how at the moment. If possible this option would mean you have to define concurrent program parameter for each program - not the best.

2. Extend the XDO core to put in the data you need, or allow the logic you need in RTF. Early on in BIP lifecycle I remember someone extending the XMLP core to allow SQL Statements from within RTF definition. Apols can't find that at the moment either!

3. You could pass the output from your required request to another request that populates the XML data with your required instance name. E.g. using SAX or Java Concurrent Program.

4. You could always call a subtemplate and just load the subtemplate you need into the appropriate instance

5. Check this post that may be related: http://blogs.oracle.com/xmlpublisher/2007/12/11/

Regards,
Gareth

Gareth said...

Hi Nilesh,

Here's an even nicer way I'm sure you can find a way to use it:
http://blogs.oracle.com/xmlpublisher/2006/04/hotels_nashville_style_and_how.html

Regards,
Gareth

Unknown said...

Hi: I am faced with a new problem. We are building an xml report in Hebrew. The template (RTF) file is translated into english. When we run the program, the output of the concurrent (view output), does a mirror image of the rtf (as expected since hebrew is read from right to left). However, the bursting program generated pdf is not the same. It gets printed like english. Has anyone faced this problem and knows a solution?
thanks
venky

Gareth said...

Hi Venky,

I'll see if I can check with someone that works with Hebrew and BIP.

Gareth

Gareth said...

Hi Venky,
Apols for the delay. For Hebrew if it's an issue of right to left its usually in the JVM NLS_LANG parameter. Maybe it's American_English. Please try changing NLS_LANG to Hebrew_Israel - or similar parameter on the bursting jvm properties.

If you want more info, drop me and email and I'll connect you to appropriate person!

Regards,
Gareth

Unknown said...

Gareth:
What we did for now is to recreate the template so that it reflects the way it is supposed to show up in hebrew (right to left). We changed the locale to english so that the report is not transposed. That way, we are now getting both the bursted email as well as the report output both from right to left.
I will however try out your suggestion and see if it works..
Thanks again for your help and support.
Regards
-Venky

Unknown said...

Hi: I am facing with a problem that I am sure some of you already have a solution for. In a PO report, we are prining comments. There could be up to 6 comments. However, there is not always going to be a comment in the PO. I am keeping 6 place holders for the 6 comments. However, this is leaving a lot of white space whey say, there is only one comment. Is there a way around this? Any suggestions are appreciated.
Thanks, Venky

Gareth said...

Hi Venky,

Just use an if statement around your comments and/or don't have any whitespace (spaces/carriage returns) on lines where you have form fields.

Regards,
Gareth

Unknown said...

Hi Gareth,

Does BI Publisher bursting support XSL-TEXT template?
if I use xapi:template type="xsl-text" instead of "xsl-fo" the bursting concurrent request errored out with a FileNotFoundException. How can I configure bursting control file to deliver a csv file generated with an xsl-text template through email?

Thanks,
Vamshi.

Gareth said...

Hi Vamshi,

Have you tried ETEXT - very quick and easy to get a CSV.

Regards,
Gareth

Unknown said...

Hi Gareth,

I have couple of questions regarding etext.
1) Can we set local variables and perform some condition based formating?
2) Does bursting engine support xapi:template type="etext"?

Thanks,
Vamshi.

Unknown said...

Hi Gareth,

My earlier post is a bit generic about etext. I would like to try it if it resolves the bursting issue with XSL-TEXT template and if it is possible to have a condition such as
if /XYZ='Y' then show /A else leave blank.

Thanks,
Vamshi.

Unknown said...

Hi Gareth

There was an earlier post (and solution) to this error that I am faced with. However, the solution did not work for me. (i.e. change the data definition and template to Receivables application)

I am attempting to Email the AR Statements (using the BI Publisher Control Bursting File). When I execute:

l_req_id := fnd_request.submit_request('XDO','XDOBURSTREP',NULL,NULL,FALSE,p_request_id);

Below is the error:

XML/BI Publisher Version : 5.6.3
Updating request description
Retrieving XML request information
Preparing parameters
--Exception
null
java.lang.NullPointerException
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getControlFile(JCP4XDOBurstingEngine.java:401)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:237)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)

Can you help?
Thanks
Trina

Gareth said...

Hi Trina,

Please make sure you have uploaded your bursting control file to XML Publisher Data Definition.

Regards,
Gareth

Unknown said...

Hi Gareth

The bursting control file has been uploaded to the Data Definition and I still recieve the same error: getControlfile

How does it know which Data Definition to use? Is it based on the request id that is being passed in to the Submit_Request?

l_req_id := fnd_request.submit_request('XDO','XDOBURSTREP',NULL,NULL,FALSE,p_request_id);

Thanks
Trina

Gareth said...

Hi Trina,

I think it should be the Data Definition with Code that matches the concurrent program name (code) of the request that you are bursting.

Regards,
Gareth

Unknown said...

Hi Gareth

I followed your steps to integrate AR Statements and BI Templates.
This works fine. The statement is created in a PDF format.

http://garethroberts.blogspot.com/2008/01/beautiful-statements-in-1-easy-step.html

The Template and Data Definition has been defined with code name SHAWARXSGPO under the Receivables application. The Data Definition has an uploaded Bursting Control File.


In package XXV8_XMLP_PKG.submit_request_xmlp

l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
p_request_id,
222, -- Receivables
'SHAWARXSGPO', -- Statement Generate
'en-US', -- English
'N','RTF','PDF');


where l_req_id is passed as a parameter from the above submit_request...

l_req_id2 := fnd_request.submit_request('XDO','XDOBURSTREP',NULL,NULL,FALSE,l_req_id);

The concurrent program name is:
(1) ARXSGP module: Statement Generation Program
(2) XDOREPPB module: XML Report Publisher
(3) XDOBURSTREP module: XML Publisher Report Bursting Program

Unknown said...

Hello

Resolved my issue of JAVA null error when executing XDOBURSTREP.

I had to define the Template and Data Defintion as ARXSGP since I was using the Oracle Standard program (Statement Generation Program: ARXSGP)

RE: http://forums.oracle.com/forums/thread.jspa?threadID=635799

Now I have the issue of where the Statement Report must complete first before we can execute the bursting. Any thoughts there?
I have attempted to use FND_CONCURRENT.WAIT_FOR_REQUEST.

Thanks
Trina

Gareth said...

Hi Trina,

Good to hear, hope my response helped you in the right direction.

Yes, wait_for_request or a loop with dbms_lock.sleep or similar but be aware that this would leave a "running" request so need to watch the number of standard manager queues.

Regards,
Gareth

Unknown said...

Please disregard the issue about waiting for the statement to complete before bursting.

I was referring to the wrong P_REQUEST_ID. It should be the request id of the ARXSGP concurrent request.

All works great now.
Thanks for your help.

Unknown said...

Hi Gareth

Would you know how we can have the ARXSGP Template use two different template layouts and email the correct template?

Thanks for your help again.
Trina

Gareth said...

Hi Trina,

There are a number of ways to do it:
1. Multiple deliveries in the bursting control file with xpath to restrict data to that matching the template on the delivery

2a. Logic inside template that calls subtemplate based on data in the XML
2b. Logic inside template that has IF statement around it to use the correct layout.

Personally I like the last option if only a couple of template layouts, simplifies setup, but depends on the complexity of your needs and maintainability of the templates.

Regards,
Gareth

Tom said...

Hello again,

I used this example to burst an email and it worked perfectly fine. Now I'm facing new problem... For all of our new reports we start using SQLX (plsql) to create an XML data file. After creating the XMLP template and defining the concurrent job of XML output type the report correctly shows as an output. Is there anyway I can redirect this output to bursting engine so I can email the report?

Gareth said...

Hi Tom,

Yes, create and upload the Bursting Control File with Email delivery to your XML Publisher Data Definition that generates the XML data.

Regards,
Gareth

தங்ஸ் said...

Thanks for the nice article!

Is there any way to generate the individual PDF files for each customer? I dont have to send emails..but need to generate different PDF files for each customer...Also, I dont want to go thru LOOP process, firing conc pgm for each customer..Instead, I would like to submit the conc pgm one for all customers..output must generate individual PDFs for each customer. is that possible?? Also, can we change the PDF file name while generating itself??

Thanks in advance

Gareth said...

Hi தங்ஸ்,

The answers to your questions are Yes! The reason for bursting is exactly that - take one XML file and split it up e.g. by customer and do what you need to do with it - email or print or fax or local file copy or custom... you can choose the file name, e.g. have the customer number as part of the file name - this is in the bursting control file.

Regards,
Gareth

தங்ஸ் said...

Thanks Gareth! My requirement is to generate different PDFs and move the files to another server. Is this possible? Is there any documentation link available for bursting excercise???

தங்ஸ் said...

Would be nice, if you could help me on this..

1. I am getting 'No such file found' error while running XML Burst conc prgm. Error message says '/tmp/YYYYMMDD_XXXX/20333.pdf' file not found.. Its pointing out the '/tmp/' folder that we mention under XML Admin/Properties/General/Temporary directory.

2. xapi:delivery tag is mandtaory?? Without that, I am getting en error while uploading control file

3. Is there anything for FTP?? like xapi:ftp??

Gareth said...

Hi தங்ஸ்,
1. Please check your tmp file is set to a specific directory and applmgr has read/write permissions.
2. Delivery specifies how to deliver the file.
3. Yes, check patch 5968876 readme.

Regards,
Gareth

RS said...

Hi Gareth,
I am trying to generate seperate pdf's in wip depending on the job number.
I have successfully done this in one report. But the XML Bursting Program errors out in the second report. The XML Bursting program log file does not show any error. I have two questions"
1. Do we have a debug file where we can see what is the error.
2. Are there some special xml template syntax such as begin@param or sort by which the bursting program does not understand.
Please note that the report publishes correct output from the same template file, but the Bursting program fails. I have re-checked the control file and it seems ok.
Could u help. Thanks a lot.

RS said...

Hi Gareth,

I removed the syntax
?param@begin:P_SORT_BY? from the template. Instead I used a sort_by data field from the query and the XML Bursting program stopped erroring. Note that the XML Publisher would generate the output perfectly with this syntax.
I did not get any documentation supporting this.
Would you have an idea.
Thanks for the great articles.

Runa

Gareth said...

Hi Runa,

Glad you found the source of your problem. Unfortunately there are many hidden gotchas and undocumented features with XML / BI Publisher so you might have just found one.

There are a number of options for debugging, but personally I use the JVM debug option as described here

Regards,
Gareth

RS said...

Hi,
I am now facing a problem with translations. My original control file which worked great read smthg like
xapi:template type="rtf" locale="" location="xdo://XXWIP.XXWIP_SHOP.en.US/?getSource=true" translation="" filter="" />
Now I want the bursting control file to pick up the language in run time. As suggested by Oracle, replace en.US with ${language}.${territory}. But did not work.
I have tried multiple options but xapi:template syntax does not seem to parse the variable at runtime. Have you been able to provide this kind of syntax in the bursting file. For xapi:template tag to parse the variable, do we need some escape characters written before them?

Gareth said...

Hi Runa,

Sorry, haven't used variables in the language/territory. But I have seen the "filter" field used to switch between different language/territory templates - if your language/territories are a limited list, then that may be an alternative option. Something like this:

< xapi:template type="rtf" location="/tmpl/US.rtf" filter=".//ROW[PLANG='US']"/ >
< xapi:template type="rtf" location="/tmpl/CA.rtf" filter=".//ROW[PLANG='CA']"/ >

Regards,
Gareth

Unknown said...

Hi Gareth,

I have a question on page number.

I am printing a invoice report in which some invoice may have a cover page to print the customer address.

i need to print the page number(p 1 of 1) in the footer of the invoice report. To print the page number i need to skip the cover pages.

For example if a invoice is having one cover page and one invoice page then the page number should print on the Invoice page as P 1 of 1. It should skip the cover page.

I already tried for the option of introducing section break in between the cover page and invoice page but it is not working.

My template look like below:
for-each@section:G_MAIN
for-each@section:G_ADDRESS
Cover Page Details(some times cover page may return more than one address)
end for-each
Invoice Page Details
end for-each


Please advice if you know any option to fix this.

Gareth said...

Hi Jag,

Can you send me your template and xml? Lots of questions on page number, I want to give it a try.

Thanks,
Gareth

Unknown said...

Hi Gareth,

Please send me your mail id.

My email id: gjagesh@gmail.com

Thanks

Unknown said...

Hi Gareth,

I have fixed the issue by following the metalink Doc ID: 411875.1

Thanks for your time.

Thanks,
Jagesh.

Unknown said...

Hi Gareth,

I get the following error while running Bursting. The error is

---------------------------------
Error!! Could not deliver the output for Delivery channel:null .
---------------------------------

The SMTP server has been set up correctly but for some reason the Bursting Control file doesnot seem to be recognizing the STMP server and throwing the above error.

This is the syntax I am using in the Bursting Control File. localhost has a valid SMTP server configured.

--------------------------------
xapi:email server="localhost" port="25" from="amit@oxygendevelopment.com" reply-to =" "
--------------------------------

I have tried using the server name, IP Address and localhost for the SMTP server but nothing seems to work

Could you please provide some inputs.

Regards
Amit

Gareth said...

Hi Amit,
You can test the connectivity of the email server by telnet to port 25 and type command to smtp server to get response like this:
> telnet localhost 25
> ehlo

Make sure this is working before proceeding. Then check the syntax in your Bursting Control File. Ike Wiggins has created a tool for generating Bursting Control Files - check it out if you still have issues.

Regards,
Gareth

Unknown said...

I am not getting any data when I use AP_VENDOR_SITES_V view. Out installation is on multiorg. When I run the same query to by setting session context dbms_application_info.set_client_info(xx) in TOAD, I get the data.

I have also used P_CONC_REQUEST_ID under the user parameters.

Appreciate your help in advance.

Gareth said...

Hi Rohan,

Make sure that the responsibility from which you submit the concurrent program has MO: Operating Unit profile option set.

Regards,
Gareth

Unknown said...

Hi Gareth,

MO:Operating Unit profile is set correctly. I get the data when I use the _AlL tables but not when I use the views. Am I missing something here.

Regards,
Rohan

Gareth said...

Hi Rohan,
If you're using Release 11i you should be fine. If you're using Release 12 check my blog post here.

Regards,
Gareth

Unknown said...

Gareth,
We have an ampersand in our org name (i.e. LD & Timekeeping Apps). We handled it it XML generation with a replace function (& is replaced with &amp) and its works great. But if I need to display that org name in the bursting file email subject or body, it truncates everything before "&" and shows only "Timekeeping Apps" for org name instead of "LD & Timekeeping Apps". Any suggestions?
Here is part of my bursting file:

content-type = "text/html"
subject = "Employee Review by Department Report for department number ${DEPT}">

Thanks,
Dinesh

Gareth said...

Hi Dinesh,

Try with a semicolon & amp ;

Regards,
Gareth

Unknown said...

Yes, we did replace & with & amp ; while generating XML. The element with the ampersand shows up correctly in the output generated using the BI publisher template. But the bursting control file which emails me doesn't do the same when I referenced same element in my bursting control file subject or body.

Thanks,
Dinesh

Gareth said...

Hi Dinesh,

Best option would be to log an SR in that case I think. Perhaps post on Oracle forums if you haven't already.

Regards,
Gareth

Chikku said...

Hi,

I am working in BI publisher now and in that my bursting program is completed successful but email is not delivered to the person. Can you please help me in this.

Gareth said...

Hi Pakalavarathy,
Have you tested the bursting to another email address? Assuming you have perhaps your email address is incorrect, or your email server is not accepting emails from the server where you are bursting. Or the email is going to junk email folder?

Regards,
Gareth

Chikku said...

I have checked it in the junk email folder and have tried with another email address too. But it dint.

Chikku said...

I have checked all those three suggestions provided by you. Everything was correct but still i dint receive the email. Could you please tell me suggestions

Gareth said...

Hi Pakalavarathy,

Can you confirm that you can send emails from the EBS bursting host to your email server specified in the bursting control file? Send me an email with your bursting control file ggr888 [at] gmail [d0t] com.

Regards,
Gareth

Chikku said...

Can you tell me the use of xapi:filesystem usage in xml

Gareth said...

Check out the readme for patch 5968876, example is:
< xapi:filesystem id="file1"
output="/conclog/log/jtf_top/atgcadev/out/${TRX_NUMBER}.pdf"/ >

Regards,
Gareth

Yella Reddy said...

I have done the above exercise..but i am getting the following error..can u please give me the reason..

APPLLCSP Environment Variable set to :

XML_REPORTS_XENVIRONMENT is :
/u04/oracle/devdb/devdbora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb

XENVIRONMENT is set to /u04/oracle/devdb/devdbora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb


Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.UTF8

'.,'

REP-3000: Internal error starting Oracle Toolkit.
MSG-20002: Failed to submit request

Gareth said...

Hi Yella,
Make sure your DISPLAY environment variable is set and your X Server / vnc / xvfb / XMing service is running.
Regards,
Gareth

Niladri Banerjee said...

I have a requirement where the client asked for an acknowledgement at user level if the user receives the email or not. How we can achieve this? Any idea!!! pls mail to me at niladri.banerjee@in.ibm.com.

Thanks,
Niladri Banerjee

Gareth said...

Hi Niladri,
Email does not guarantee the user receives the email. If you have to guarantee receipt you would need the user to click on something that tracks they have received the email.
Regards,
Gareth

iamchandru said...

Just a thought!
At the email header, investigate if you can set read receipt. so that you will be notified when the user reads that email.

Gareth said...

Yes, but not all email clients support read receipts, and also you'd still need to write something that processes the read receipts.
Regards,
Gareth

Rudra. said...

I was receiving the following error: Error!! Could not deliver the output for Delivery channel:null.
But all of a sudden this has stopped and reports run successfully. Can you pls help me in understanding what can be the reason for it starting to work suddenly? Can it be a cache error?

Gareth said...

Hi Rudra,

Something must have changed ... email address with null value? ID in bursting control file null in XML? Bursting Control File changed? Best to log an SR on this one, or post more details to Oracle Forums.

Regards,
Gareth

Rudra. said...

Thanks Gareth for your prompt reply. I have not changed the control bursting file at all. Email addresses were not null. What else should I check? Global properties? (I was using Ike's bursting tool and set some global properties but later reverted back. Can that be an impact?)

Gareth said...

Yes - something must have changed and global configuration would be a possibility.

Rudra. said...

But I reverted it back. Can it work even then? Its happening here.

Thanks for the help,
Rudra

Gareth said...

Not sure you'll have to keeping testing it.

Rudra. said...

Gareth,

You have done the trick. Query was fetching no records so email was actually becoming NULL.

God bless you.

Thanks,
Rudra

Sundeep said...

Hi Gareth,
Is there a way you can fax documents via rightfax by using bursting engine?

Thanks,
Sundeep

Gareth said...

Hi Sandeep,

Its been a while since I did anything with Rightfax (not since bursting came on the scene), but I'm pretty sure there'll be something you can do. E.g. Burst to filesystem then have Rightfax poll that directory.

Regards,
Gareth

Kron77 said...
This comment has been removed by the author.
Kron77 said...

Hi Gareth,
again, nice work on this one. I actually have a rather generic question, most likely related to my lack of functional knowledge in the AP space. I am struggling to work out how this solution actually hooks into the payment process. Currently on my instance here, I am getting plain text emails which are fired from a subscription on the oracle.apps.ap.payment business event. Your solution describes automating the firing of the emails through the concurrent program, but I am yet to identify when it actually gets fired. Can you help me understand how/when the email is triggered automatically?
Cheers

Gareth said...

Hi Kieron,

There are two methods of sending Suppliers Remittance advice in Payables 11i:
1. Separate Remittance Advice report (batch process) traditionally used for Print.
2. Built-in Payment Event that triggers a Workflow Email Notification.

The solution described here uses the Separate Remittance Advice report (batch process) rather than the built-in payment event (fires per payment).

Regards,
Gareth

Kron77 said...

Hi Gareth,
Thanks for the explanation. I guess the question I might get asked at some point is: "is it possible to produce the PDF remittance advice using the second method (built-in payment event from the workflow email notification)". Is there a technical inhibitor to that, what would be the pros/cons?

Gareth said...

Hi Kieron,
You would need to rework something that is easily done using a slightly different process. The standard business event calls a PL/SQL package to do the formatting and sends email via Workflow, so technology is different. You'd need a call back and a PDF generating PL/SQL procedure to do what you need, plus when you go to Release 12 it would be obsoleted.
I'd recommended going BI Publisher route, or waiting for R12.

Regards,
Gareth

Gareth said...

PS. Kieron - by "wait for R12" I meant wait for your organization to upgrade/implement R12!

Kron77 said...

Hi Gareth,
Fully understand and appreciate the return comments. Thanks for a great post!

iamchandru said...

Hi Gareth,
we have this peculiar situation, say in your example
<xapi:message id="${C_CHECK_ID}" to="${C_REMITTANCE_EMAIL}"

if the to email address is null, the bursting conc program errors out. Do we do some condition check in bursting control file or anywhere of handling this if to email address is null rather than report ends in error.

thanks for your help.

Gareth said...

Yes, you can put a filter in the document template filter attribute.
For example to filter where delivery_mode = email, assuming you have that attribute:
filter="/XXRAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE[DELIVERY_MODE='Email']"

Regards,
Gareth

Kron77 said...

Hi Again Gareth, sorry for posting again - I am nearly there; I managed to get a nice email with PDF and it looks great. My final question partly relates to my last question about how to fit this solution into the payment run. Again, I apologise if I am missing something that is standard AP functionality. So I created a XXSeperate Remittance Advice program to create the PDF and it works fine when I run it stand-alone. When I try and put that XXSeparate Remittance Advice program into the payment process, I can't get it to fire automatically. My goal is to get the Payment Batch process to automatically fire the production of the PDF(s) for each payment in the payment batch. I added the XXseparate remittance advice to the payment format screen (at the bottom under programs - separate remittance field) and then mucked around with the options for remittance "Before Document, After Document, None" etc, but no matter what, it doesn't automatically fire the XXSeparate Remittance Advice program. The user has the option of choosing the "Print Remittance Advice" checkbox when issuing the payment batch action, but users being users, they will most likely forget etc. I guess I am trying to replicate the functionality of the BES WF which will guarantee an email per payment. I hope this question makes sense, sorry for the length!

Gareth said...

Hi Kieron,
Check out the automatic burst and if you need to create forms personalization or something to automatically check the Print option if that helps!

Regards,
Gareth

RS said...

Hi Gareth,
I have a problem with bursting and barcodes. The XML Pub output displays the barcode correctly, but the bursted pdf does not display the barcode. It seems that the bursting engine does not recognize the barcode font.

I searched the internet and there are places where we are being advised to either create xdo.cfg or modify xdo.cfg. We have tried those options but without luck.

There is also Oracle patch 7352374 for this issue, but that also did not solve the problem.

The only thing working of us is to change the rtf template property to add in the barcode font and hardcode in the directories. But that does not suit us for portability.
Have you an idea ?

Gareth said...

Create a font mapping and font mapping set, then assign that to the format template properties.

Regards
Gareth

RS said...

Thanks for the quick reply Gareth. But could you please lead me to where I would find the syntax of attaching a font mapping property to a rtf template.
My search is only resulting in how to attach font property.

Gareth said...

Hi Runa,
Its just setup steps in XML Publisher - follow MOS Notes 551630.1 and 373377.1 and make sure that language and territory are left BLANK.

Regards,
Gareth

Unknown said...

HI gareth,
I'm using bursting technique to send PO via email to Suppliers......
I'm able to send email...
But the problem is
what do you do if transmission fails and notifications can't go out. like email is down......this is raised by my client...........

Unknown said...

Hi Gareth,

My XML Bursting program is working fine, i.e, I am able to receive output in email. But I am unable to view output for the report.

I have given site value for the xml publisher administration temporary directory, yet the template is not getting applied to the output of the concurrent program. I see xml tags instead of the PDF output in the view output button.

Please help.

Thanks,
Suma

Gareth said...

Hi Theja,

There are a variety of failure scenarios you need to cater for depending on your delivery channel (email or print or FTP). If a full failure (e.g email server down) then you'll see all deliveries have fail status and you can re-burst the entire batch. If single failure then you'll need to diagnose the problem and perhaps solve that individually. For returned emails you'll need to manually monitor your "Reply-to" email address.

Regards,
Gareth

Gareth said...

Hi Suma,

What you see in the XML is pretty much what you see in the report output, but you can manually run the Bursting Status Report concurrent program on the bursting concurrent request - you'll need to assign Bursting Status Report to the appropriate request group.

Regards,
Gareth

kaushek said...

Hi Gareth,

I am using R12 applications.
I get this error 'Error!! Could not deliver the output for Delivery channel:null' in the output file of the bursting concurrent program.
The smtp server name the ids the mail id and all other things are fine. I don't know why i am facing this issue.
Below is the bursting control file.






Please review the attached list of roles









Any help would be greatly appreciated.

Gareth said...

Hi Kaushek,

Error "Could not deliver the output for Delivery channel:null" is kind of like a generic error when a fundamental delivery failure occurs. Check your bursting control file and all other aspects - make sure you can telnet 25 from the host where you are running bursting.

Regards,
Gareth

kaushek said...

Gareth,

Thanks for a quick reply.
I checked my bursting file and everything seems to be correct.
I also telnet 25 with my smtp server name and it was working fine.
I am not able to make out what could have gone wrong and where?
What other smtp configurations i would have to set? Or am i missing something very silly?

Gareth said...

Hi Kaushek,

Try switching on debugging.
1. Run the 'Prints environment variable values' request for variable AF_JRE_TOP.
2. Connect to the Concurrent Manager server as ‘applmgr’.
3. Create an $XDO_TOP/temp directory.
4. Go to the $AF_JRE_TOP/jre/lib directory (AF_JRE_TOP from step 1).
5. Create an xdodebug.cfg file with the following 2 lines (replace path):
LogLevel=STATEMENT
LogDir="path to XDO_TOP"/temp
6. Restart the Concurrent Managers
7. Reproduce the problem.
Debug files will be created under the $XDO_TOP/temp

Regards,
Gareth

kaushek said...

Gareth,

I executed the steps you mentioned.
when i see the $XDO_TOP/temp directory i see many files created for the single program.
These are the files.
xdo.log
xdo_062410_113619945_fo_data_174.xml(this file is empty)
xdo_062410_113619945_fo_fo_176.fo
xdo_062410_113619945_fo_out75.out

I can see error in log file

[062410_113618900][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): Use SMTP authentication with xdo/*******
[062410_113618903][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): Character set for MIME headers : UTF-8
[062410_113618904][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): Character encoding for MIME headers : B
[062410_113619184][oracle.apps.xdo.batch.DeliveryHelper][EXCEPTION] javax.mail.AuthenticationFailedException

What could be this error related to? wht setup is missing?

Gareth said...

Hi Kaushek,

It says SMTP Authentication failed - have you intentionally switched on SMTP Authentication? If not, switch it off, and try again (not sure where, must be XML Publisher setting somewhere).

Regards,
Gareth

Ranjith said...

Hi Gareth,

Can you please help me on this. I have a requirement where the AR Invoices should be split & FTP. I have tried different methods by seeing your blog and others. But solution not acheived. I have tried out the mailing through burstign and it worked. But FTP is not working. I have alreay spent lot of time doing these alerations..searching net...blogs..etc..please help me. Log file shows nothing about any error or exception. But the destination server/folder desonot receive the files. Not sure what is the problem. can you guys please help me out. I am pasting the Control file and log file below:
------------
Control File:
----














-------------
Log File:
----
----
[ASYNC_TIMEOUT:Integer] [86400000]
[HOST:String] [141.146.168.51]
[RETRY:Integer] [0]
[ASYNC_CHECK_INTERVAL:Integer] [60000]
[TEMP_FILE_SUFFIX:String] [.tmp]
[USERNAME:String] [infgeori]
[PASSWORD:String] [infgeori]
[TEMP_FILE_PREFIX:String] [dlvr]
[FTP_REMOTE_DIRECTORY:String] [/TGEORI/incoming/Test01]
[FTP_REMOTE_FILENAME:String] [CS10-9997.pdf]
[RETRY_INTERVAL:Integer] [60000]
----

[070810_092920546][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequest][STATEMENT] submit(): BUFFERING_MODE is OFF.
[070810_092920546][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequest][STATEMENT] submit(): Start reading the document file. : /usr/tmp/070810_092259725/CS10-9999.pdf
[070810_092920546][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequest][STATEMENT] submit(): Calling DeliveryRequestHandler.submitRequest()
[070810_092920546][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequestHandler][STATEMENT] submitRequest(): Called
[070810_092920549][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequestHandler][STATEMENT] submitRequest(): Opening connection : [141.146.168.51][21][infgeori][****][/TGEORI/incoming/Test01][CS10-9997.pdf][true]
[070810_093229555][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequest][STATEMENT] submit(): Finished calling DeliveryRequestHandler.submitRequest()
[070810_093229556][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequest][STATEMENT] submit(): Process done successfully. Exiting submit()
[070810_093229558][oracle.apps.xdo.batch.BurstingProcessorEngine][STATEMENT] ========================> startElement() ::: endDocument is entered <========================
[070810_093229558][oracle.apps.xdo.batch.BurstingProcessorEngine][STATEMENT] ========================> startElement() ::: endDocument is entered <========================
Bursting process complete..



**** I have tried the Mailing burst and it worked. But FTP is not working.

Sincerely awaiting your help,
Ranjith

Ranjith said...

just in case pasting the control file again:

Control File:
----















Regards,
Ranjith

Ranjith said...

posting the control file again..not sure why it is not appearing..may be becoz of tags


Control File:
----
xml version="1.0" encoding="UTF-8"
xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting"
xapi:request select="/XX_TEST_BURST_001/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE"
xapi:delivery
xapi:ftp id="ftp1" server="141.146.168.51" user="infgeori" password="infgeori" remote-directory="/TGEORI/incoming/Test01" remote-file="${TRX_NUMBER}.pdf"
xapi:ftp
xapi:delivery
xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="ftp1"
xapi:template type="rtf" location="xdo://XBOL.XX_TEST_BURST_001_XT.en.US/?getSource=true"
xapi:template
xapi:document>
xapi:request
xapi:requestset

Regards,
Ranjith

Gareth said...

Hi Ranjith,

Can you manually FTP using the settings you've put in the control file?

If not send me through your Control File, XML Data File and RTF template. ggr888 [at] gmail . com

Regards,
Gareth

kaushek said...

Hi Gareth,

My report has a a very specific requirement.
Its like I would have to burst the report based on three parameters and not just one.
For example: I need to segregate the date based on parent org id, child org id and then the manager id ...all the three.
The combination of all the 3 is unique.
Can this be done using xml bursting in the burst control file.
To my knowledge I have seen examples where segregation is done based on only one parameter.

Gareth said...

Hi Kaushek,

You can put xquery syntax in the filter attribute of the document element in the bursting control file to probably achieve what you require. You can have multiple documents with varying filter clauses to match your requirement.
Example filter is like this:
filter="/TMP005/G_INVOICE[TWL_AGENT_MODE='Email']"

Regards,
Gareth

kaushek said...

Thanks Gareth for a quick response.
But I didn't really get what you wanted to convey.
In the bursting file I have
request select="/XXOTC_MER_PACK_LIST/LIST_G_MAIN/G_MAIN/LIST_G_HEADER_ID/G_HEADER_ID">

Now the burst control file would split the huge report based on every new occurrence of header_id.
In my case i would have to do this on every new occurrence of parent org id, child org id and also owner name of that org id.
So you see its three parameters here.
How can I write the request select element for three parameters?

Gareth said...

Hi Kaushek,
What are the requirements? Do you just need to split because of layout differences and different delivery? If so then the document filter I mentioned would meet your requirements.
Otherwise I'm not sure I've tried to do something along those lines. How about concatenating the fields in the source XML via the extract routine? Drop me an email if you want to discuss this in more detail.

Regards,
Gareth

kaushek said...

The requirement is to create a report (this comes under user management module) and send the report output to number of people.
I have the sql query with me.
The output would have org id, user id, description, employee number, country,roles assigned, application name,user name, email adrress1, email address2.

Now the query would fetch millions of rows depending upon the org id it was run for.
We have to generate number of excel sheet reports for each organization id(each org id would have any number of employee associated) and send the report to the email address1 and email address2 fetched in the query.(email address2 would not be sent all reports only few unlike email address1).
Now when I have to burst this big spread sheet into several smaller ones based on org id and also based on email address1 and emails address2.
I know that bursting is done only on one element of the xml output. My doubt is can be burst based on three different elements like org id, email id1 and email id 2?

kaushek said...

whats your email id by the way?
Incase i might want to mail you.

Gareth said...

Hi Kaushek,

Sounds to me like you only have one grouping element - org_id, and multiple deliveries will cover the different emails. Since you have control over the SQL you can generate the required grouping element, as a concatenation of the fields if necessary. And you can still use filter on the document element to meet filter rules across multiple elements if that is more appropriate. Email is ggr888 [AT] gmail [D0T] com.

Regards,
Gareth

kaushek said...

Gareth,

I have prepared the bursting cntl file.
I hope the filter is correct.

filter= "/XXOTC_MER_ROLES_LIST/LIST_G_USER_ID_/G_EMAIL_ID_2[email_id_2 != '']"

kaushek said...

Hi Gareth,

With your vital help I was able to construct a good control file and now it works fine.
I was wondering if I want to print sysdate as one of my date values for each time the report is run,is there a way in burst control file i can use some function which will fetch sysdate ...without providing sysdate as a data source from the xml file.

Gareth said...

Hi Kaushek,

Good to hear you've make progress.
I'm not aware of solution to put sysdate in the delivery body although I remember somewhere about the filename being able to have a date in it - try posting question on Oracle forums. Or review Tim's blog posts http://blogs.oracle.com/xmlpublisher.

Regards,
Gareth

kv said...

Garth,
Great article. Thx a lot your dedication.

The restriction to use the email server name, did it work with placeholder logic?

Thanks
Vinayag.

Gareth said...

Hi Vinayag,

Yes, you can use placeholders for email server etc.

Regards,
Gareth

Unknown said...

Hi Gareth,
Nice article. I have use this article couple of times to clarify my doubts. Recently I documented most of the bursting control file features into one single article as samples. Appreciate you comments on this article.
<Exploring BI Publisher Bursting functionality

Raj

Gareth said...

Hi Raj,

Looks like a very useful article, thanks for the reference.

Regards,
Gareth

Unknown said...

Hi Gareth
I am trying to burst a invoice report and send an email per invoice and even though I have specified the break, the report does not break.The email goes to the email in the second invoice.It does not break on the invoice.






Dear Sir/Madam,
Please find attached invoice for the product you purchased
Regards
Order Fulfillment Service.









Anand

Gareth said...

Hi Cannon,
Sorry, can't see your XML... just post the "select" part of your bursting control file - make sure it is correct. Alternatively drop me an email ggr 888 [at] gmail [D0T] com.

Regards,
Gareth

Unknown said...

EBS

Itsme said...

I step3 Data Definition CODE for XXV8_APXPBSRA which you provided in the screen shot, is Preivew Data Mandatory to upload a XML and if so what can we put there. Please suggest.

Thankyou,
ABR

Gareth said...

Hi ABR,
No, preview data is not mandatory - it is only so developers etc have a sample that they can preview.
Regards,
Gareth

Mahdev said...

Hi,

I want to add a customize BIP report to be spawned by the ARXSGP - Statement Generation Program.

I've customized the ARXSGPO - Print Statement, which is called by the ARXSGP - Statement Generation Program, to output type XML type and added a Data Definition and Layout Template to it. But the ARXSGPO - Print Statement program doesn't seem to get spawned .


Thanks
Regis.

Gareth said...

Hi Mahdev,

You'll need to try something along the lines of this (spawn another program):
http://garethroberts.blogspot.com/2008/01/beautiful-statements-in-1-easy-step.html

Regards,
Gareth

Mrs P said...

Hi Gareth,

Thank you for posting this how-to article on bursting. I am using this as a tutorial for myself in learning how to burst an xml report in our 11.5.10.2 test environment.

After creation of a new custom Separate Remittance Advice, complete with XML definition & Bursting Control File attached, I am now trying to run the XML Publisher Report Bursting Program to email my report.

However, after adding this XML Publisher Report Bursting Program in the All Reports/Payables request group, I am getting the following error:

APP-FND-00222 Encountered an error while getting the Oracle user account for your concurrent request.

Currently, the responsibility I am using is Custom Oracle Payables. This responsibility has the request group defined as All Reports/Payables. So I still added my report and the XML Publisher Bursting program under this responsibility.

However, I did change the application (instead of Payables, I put Custom Oracle Payables) both in the Concurrent Program and Executable.

I checked this error online and in metalink, the Data Group (System Administrator) was the culprit. I checked and under the Standard data group, this Custom responsibility was added and an oracle id was defined.

Why am I getting this error??

Tom said...

Hello Gareth, I'm using your bursting solution for a while and it works fine thank you.
Recently I have the required to actually burst the raw XML data (not pdf or excel) - is it possible?
Basically our EBS suite must send data in XML format to another system via email. I would like to use bursting to achive this.

Tomas

Gareth said...

Hi Tomas,

Yes, I have recently implemented something similar, the approach was to use ETEXT (hardcode the XML tags inside each field). Not very nice but it works. Remember to XML encode any source text that may include XML characters, e.g. using dbms_xmlgen.convert.

Regards,
Gareth

invinci said...

Hi,

I have a issue with bursting the Excel formatted output of BI publisher report. If the size is less than everything works fine. If the output file size is 33 MB, i am not able to view the output in the request form and also bursting is not working. Can someone help me to resolve this issue.

Thanks in Advance

invinci said...

Hi,

I have a issue with bursting the Excel formatted output of BI publisher report. If the size is less than everything works fine. If the output file size is 33 MB, i am not able to view the output in the request form and also bursting is not working. Can you please let me know if there is any restriction in the size of excel output for bursting.

Thanks in Advance

Gareth said...

Hi Invinci,

At a guess ...

If your not EBusiness Suite, check the java heap size of your JVM and increase via -Xmx1024m or similar.

If you're EBusiness Suite check that you've allocated enough memory to the JVM put -Xmx1024m in the options field on concurrent program.

See if there are any errors in the Output Post Process concurrent manager log file under Administer Concurrent Manager in System Administrator.

Regards,
Gareth

«Oldest ‹Older   1 – 200 of 278   Newer› Newest»