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.

Tuesday, March 25, 2008

Recommended Patch Lists updated on Metalink - check before implementing/upgrading

Just took a look at the Recommended Patch Lists on Metalink, first time for a while, and if my memory serves me correct there are now more products listed.

Good to see BPEL represented as well as the core Oracle eBusiness Suite, Database and Application Server product suites. Whenever implementing, upgrading or just applying a new patchset, take a look at the recommended patch lists.

Now if only Oracle would get with the times and RSS feed the functionality there. For the EBS customers something official like the Patchsets Release 11i and Release 12 Feeds would be great!

 

Wednesday, March 19, 2008

Why is mod_plsql not supported with the Oracle eBusiness Suite Release 12? Fusion Crossroads #1

Update: As posted on Steven Chan's blog, the answers to my queries in this post are now well described in Metalink Note Note:726711.1

One of the well publicized and contentious considerations at the crossroads of Fusion relates to the "official" lack of support for the mod_plsql component in the Oracle eBusiness Suite Release 12.

I'm a keen battler on items of contention and I've been tracking this one for while. A high percentage of customers I've dealt with have invested in mod_plsql (mod PL/SQL) based solutions. With its reappearance on the forums recently and interest in R12 I thought it would be nice to take a step back to this.

First of all let's neglect the question a bit longer and look at a different statement, for reference, from here:

Although mod_plsql is no longer hosted as part of the standard Release 12 technology stack infrastructure, it's still possible to use it, in albeit a configuration that requires more diskspace. You can have a separate Oracle Application Server 10g installation, either in a separate ORACLE_HOME on an existing server, or on a physically separate machine.  You can use mod_plsql and other mod_plsql-based tools (such as Application Express) on that instance to access the E-Business Suite Release 12.

Hmm, so we know that we can use mod_plsql with Release 12. Just need to spin those propellors a bit and hey presto - there you go, working again. Or even just plug that Apache module back in.

If thats the case, then how does this all relate to support? Surely if the product can be used in a supported configuration, then it should be supported!? Using Apache (Oracle HTTP Server) and mod_plsql is one supported configuration of Oracle Application Express so I don't see the problem being with mod_plsql itself. If it was an inherent problem with mod_plsql then where would that leave Metalink?

Also, the quoted statement above conveniently fails to mention that inevitable L word - licenses. I'm no expert in the L word, so I venture no further and leave that to your own due diligence!

My current understanding - and I may be wrong so correct me if I am - is that:

  1. the use of mod_plsql in combination with the APPS schema inherently raises security problems
  2. mod_plsql is not on the roadmap to Fusion

With respect to 1, security problems generally can be fixed, for example, as they were for those issues in Security Alert #28.

With respect to 2, this is a technology decision in direction made by Oracle so my feeling is that it shouldn't affect support.

So where does that leave us?

Well, I leave you to form your own opinion, but I'd love Oracle to clarify further their position on the support of mod_plsql with Release 12.

PS. For more details on using mod_plsql with Release 12 open a Support Request (SR) on Metalink and ask for Note 435544.1.

Monday, March 17, 2008

Top Ten and Favorite Posts

I've been running this blog for a while and I thought it was time for a quick recap to see what has been popular. So without further ado, here are a couple of Top Ten listings, and my favourites!

Top Ten Posts - Last Month:

  1. Internet Explorer 7 crash on jvm.dll with Oracle JInitiator - Applications Forms - Windows Live Sign-In Helper
  2. Excel file output from Oracle Applications concurrent request using SYLK. aka Look Ma, no BIP!
  3. Standard Report to CSV File via BI Publisher
  4. Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request
  5. Oracle eBusiness Suite R12 Demo on a Laptop
  6. Short n Fat vs Tall n Slim: Apps Printer Drivers 66 lines per page A4 printing
  7. Beautiful Statements in 1 Easy Step - Automatically submit XML Report Publisher request for Oracle Receivables Statements output
  8. Oracle eBusiness Suite Product and Acronym Listing
  9. Document Attachments: Private Stuff
  10. Fake It - masquerade one BIP concurrent program as another for testing purposes

Top Ten Posts - All Time:

  1. Standard Report to CSV File via BI Publisher
  2. Internet Explorer 7 crash on jvm.dll with Oracle JInitiator - Applications Forms - Windows Live Sign-In Helper
  3. Excel file output from Oracle Applications concurrent request using SYLK. aka Look Ma, no BIP!
  4. Oracle eBusiness Suite R12 Demo on a Laptop
  5. Document Attachments: Private Stuff
  6. Short n Fat vs Tall n Slim: Apps Printer Drivers 66 lines per page A4 printing
  7. Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request
  8. Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)
  9. Oracle eBusiness Suite Product and Acronym Listing
  10. Audit Trail Must Do: Bank Accounts

Gareth's Favourite Posts

  1. Secure storage of passwords in Oracle Applications via Encryption of Profile Option Values using dbms_obfuscation_toolkit and Forms Personalization
  2. Excel file output from Oracle Applications concurrent request using SYLK. aka Look Ma, no BIP!
  3. Oracle eBusiness Suite R12 Demo on a Laptop
  4. Chronological Tracing/Debugging with minimal intrusion in Oracle
  5. Development Standards - who are ya?
  6. Time for an Oracle eBusiness Suite Release 12 Upgrade? Eat your own dog food!
  7. Best Practices Solutions: choose your moving parts carefully
  8. Fake It - masquerade one BIP concurrent program as another for testing purposes
  9. Freeview TV via wok without the black bars anyone?
  10. I'm IT - I've been OraBlog tagged.

Happy Reading!

Tuesday, March 11, 2008

Time for an Oracle eBusiness Suite Release 12 Upgrade? Eat your own dog food!

A number of years ago Oracle trumpeted the global implementation of its eBusiness Suite internally. In my view, this was a very important event for all eBusiness Suite customers. Why? Oracle was not only eating its own dogfood, but also giving customers a number of signals:

  1. Oracle believes and relies on its own products.
  2. Gaps/shortcomings in its own products become highly visible internally.
  3. The readiness of major releases is signalled by Oracle's internal adoption of the release.

Release 12 has been generally available for over a year - since 1 Feb 2007, with key enhancements such as BI Publisher (XML Publisher), Integration Repository, Subledger Accounting and Multiple Organization Access (MOAC) being in the spotlight. Four Release Update Packs are available at the time of writing (to version 12.0.4). My understanding is that Oracle has upgraded its internal production instance and had a month or two to iron out the bugs, so to my mind Release 12 is ready for the primetime!

Now I can hear the echoes "but what about Fusion ... we can go directly from 11i10 (11.5.10.x) to Fusion?" ...

Well, I'm a fan of "big bang" implementations, but I also respect the path of least resistance and with what I know about Fusion my thinking is the path from Release 12 (which is based on Fusion Middleware) to Fusion will be smoother. I guess we'll see later this year when (hopefully) a number of Fusion Applications are released. I'm not fond of holding my breath for anticipated software releases!

In any case here are a few pointers to handy Release 12 Upgrade resources.

Note: for the presentations you may need Username: cboracle - Password: oraclec6

With respect to technical considerations such as the desupport of mod_plsql, the impending demise of Oracle Reports and Workflow, if you'd like my view, leave me a comment or send an email!

Update: Added links to documents thanks to David (see comments)

Monday, March 10, 2008

Boost productivity, reduce user frustration: Speed startup of Oracle Applications concurrent requests

Apps Users: Do you recognize this key sequence?
alt-v r enter alt-r alt-r alt-r alt-r alt-r ...

That's me waiting for concurrent requests to complete ... or worse ... to start!

Are you, your Developers or Test Team constantly clicking the "Refresh" button on the View Concurrent Requests screen to no avail?

If this is you then read on ... or forward to your DBA ... it could be time to decrease the sleep time of your standard concurrent manager!

Does the following query return 30, 60 or higher for the "Standard" concurrent manager?

select fcq.concurrent_queue_id
,      fcq.concurrent_queue_name
,      fcqt.user_concurrent_queue_name
,      fcqs.sleep_seconds sleep_sec
,      fcqs.min_processes min_proc
from   fnd_concurrent_queues fcq
,      fnd_concurrent_queues_tl fcqt
,      fnd_concurrent_queue_size fcqs 
,      fnd_concurrent_processors fcpr
where  fcq.concurrent_queue_id = fcqs.concurrent_queue_id
and    fcq.concurrent_queue_id = fcqt.concurrent_queue_id
and    fcq.concurrent_processor_id = fcpr.concurrent_processor_id
and    fcqt.language = 'US'
and    fcpr.concurrent_processor_name = 'FNDLIBR'
order by decode(fcq.concurrent_queue_name, 'STANDARD',0,1)
,        fcqt.user_concurrent_queue_name;

Is your average wait (AVG_WAIT) time for concurrent requests starting above a second or two?

Caveat: the following query assumes the majority of requests are not scheduled, any wait over 2minutes is excluded, and request history is retained for at least 2 weeks.

PROMPT Concurrent Requests wait stats for the prior week
select trunc(sysdate-8) date_from
,      trunc(sysdate-1) date_to
,      count(1) num_requests
,      round(sum(actual_start_date - request_date),2) tot_wait
,      round(sum(actual_start_date - request_date) / count(1),2) avg_wait
from   fnd_concurrent_requests
where  actual_start_date is not null
and    actual_start_date - request_date < 120
and    request_date >= trunc(sysdate-8)
and    request_date <  trunc(sysdate-1);

Review your Workshift for your Standard (or similar) manager.

  • System Administrator, Concurrent, Manager, Define
  • Query up the Standard manager (or similar)
  • Click on Workshifts
  • Review what the Sleep Seconds and Processes are set to.

Set the sleep time low enough, say 10 seconds, and check that there are adequate processes.

Note of course that reducing sleep time or increasing processes will put more load on your hardware, but if you have the capacity try it and see how it goes!

 

Thursday, March 06, 2008

Running Totals in BI Publisher + Cannot convert to number

Over on the BI Publisher forums, I spotted a interesting one error when attempting to put page totals using a running total using updateable variables and templated footers:

Once I run the RTF am getting error "Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number"

As per the example in document XML Publisher Users Guide 11i the syntax seemed right, i.e. where INVAMT is the field to calculate running total and RTotalVar is the running total variable:

Initialize variable:

<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', 0)?>

Set variable:

<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', xdoxslt:get_variable($_XDOCTX,'RTotalVar') + INVAMT)?> 

Show variable:

<?xdoxslt:get_variable($_XDOCTX, 'RTotalVar')?>

After a bit of tinkering with the RTF and XML, I realized the syntax was correct, so what was going on?

One extenuating factor was the location of the "set variable" form field inside a nested table. Time to move it out, no need for it to be there - unless the grouping mattered ... in this case it didn't. So moving the "set variable" form field out of the table fixed things. Moral of the story?

If somethin's not working and you think it should, and the error output ain't doin it for ya - simplify simplify - or should that be KISS KISS - until it works ;-)

Saturday, March 01, 2008

Would the REAL Excel please stand up?!

One of the considerations of using Microsoft Excel is the underlying format of the data you're looking at.

Lets say you're viewing output from an Oracle eBusiness Suite concurrent request, and Excel is automatically opened like here (SYLK) and here (BI Publisher). These solutions pose a couple of questions:

  • How do you know what the real underlying format is for the Excel file opened?
  • What are the implications of the underlying format?

A "true" Excel workbook format is a binary format. When you view concurrent request output in EXCEL format from BI Publisher then the output is actually XHTML. If a file with .xls suffix is in XHTML format and you do a blind "save" on the file, then you wouldn't know otherwise. Other formats (SYLK, CSV, etc) prompt you with "File.xls may contain features that are not compatible with XXX format. Do you want to keep the workbook in this format." Another alternative I use to identify the format and more often to change the contents without the subtleties of Excel mangling coming into play is to use a non mangling text editor with hex/binary file editing capabilities, my preferred tool is the fantastic free PSPad. As soon as I open the file the format is obvious.

Can the underlying XHTML format cause a problem?

At this stage I haven't identified any issues, but the question has already been asked. I generally don't like to take chances when an easy workaround is available so my advice is:

Use "File, Save As" functionality and save as type "Microsoft Office Excel Workbook *.xls" whenever you're not 100% sure of your underlying Excel file format.

When you do a file save as, the format that Excel thinks the file will be defaulted in the "save as type" field. So then you'll know!

PS. If anyone has any specific isses with the XHTML output from BI Publisher I'd be keen to hear.

PPS. "True" Excel templates are supposed to be coming to the eBusiness Suite BI Publisher sometime soon ... Tim any update?

PPPS. If anyone knows a CSV editor that doesn't mangle the contents (e.g. dates, number formats) like Excel but has functionality similar then I'd also love to hear!