Thursday, January 24, 2008

Beautiful Statements in 1 Easy Step - Automatically submit XML Report Publisher request for Oracle Receivables Statements output

Update: This post is specifically for Release 11i. New functionality in Release 12 implements BI Publisher in many of the 3rd party facing documents.

This post is specific to Receivables Statements, but equally applies to Dunning Letters as well. It requires you having setup your Data Definition and Template, in the case of Statements codes ARXSGP/ARXSGPO, and changed your output to XML on the concurrent program setup for Statements/Statement Print.

As per Note:337740.1 and Note:429283.1, and as per Enhancement Request 4461071, there is currently a limitation with Statements/Dunning Letters that you can't automatically get BI Publisher/XML Publisher output from the concurrent request. A couple of attempts have been made to add layouts, submit requests to the after report trigger in ARXSGPO.rdf, but can end up with either:

A reports compilation error when you don't pass all 100 parameters to fnd_request.submit_request:

unsupport construct or internal error [2601]

or the XML Report Publisher request completing with a warning, when all 100 parameters passed to fnd_request.submit_request:

One or more post-processing actions failed. Consult the OPP service log for details.
Output Post Processor log shows [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

with Output Post Processor log showin

 [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

So what can we do? Here's one solution, and this applies equally for any concurrent request producing XML where you don't have control over the template/layout.

Create a package:

create or replace package XXV8_XMLP_PKG AUTHID CURRENT_USER AS
  function submit_request_xmlp
  ( p_code in varchar2
  , p_request_id in number
  ) return number;
end XXV8_XMLP_PKG;
/
create or replace package body XXV8_XMLP_PKG AS
function submit_request_xmlp
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'ARXSGP' then
    l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
                                           p_request_id,
                                           222, -- Receivables
                                           'ARXSGP', -- Statement Generate
                                           'en-US', -- English
                                           'N','RTF','PDF');
  end if;
  return l_req_id;
end submit_request_xmlp;

end XXV8_XMLP_PKG;
/

Add the following to the after report trigger in ARXSGPO.rdf:

  declare
    v_req_id number := 0;
  begin
    v_req_id := xxv8_xmlp_pkg.submit_request_xmlp('ARXSGP',: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;

Run Statements and fingers crossed you'll have beautiful output in one easy step!

Update: Fixed mismatch between code passed to package by after report trigger and the "if" statement code, they must match ARXSGP=ARXSGP!

Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request

Update 2: This solution is now obsolete:

  • Patch 5612820 and 7627832 for R11i have been released for this issue. Applying these patches will overwrite the customization. See this post for details.
  • Patch 5612820 for R12 has been released. See this post for details.

Well, a few people have been frustrated with the default output format of PDF when submitting BI Publisher based concurrent requests.

Oracle's better solution to provide a default output format on the template definition isn't here yet. For reference see Metalink Note 401328.1, or Bug 5612820 or Bug 5036916, or Forums here or here

I'm not going to hold my breath, so herein lies a solution to set the default based on a profile option value, using an unsupported form modification to FNDRSRUN.fmb (Submit Requests or Standard Request Submission). I don't usually recommend modifications, but in this case its one line of code and the impact is very minor, so if its blown away, we'll just have to get over it ... check the caveats at the bottom of the post too!

Note that it is possible to set the default format to Excel, RTF or whatever your preferred output format is via forms personalization if you always navigate to the Options, Layout block of the Submit Requests screen. But 99 times out of 100 I don't go there.

Onto the instructions.

1. Create profile option "XML Publisher Default Format".

Navigate to Application Developer, Profile

Create new profile option

  • Name = XXV8_XMLP_DEFAULT_FORMAT
  • Application = (Your modifications application or Application Object Library)
  • User Profile Name = XML Publisher Default Format
  • SQL Validation:
SQL="SELECT MEANING \"Default Output Format\"
  , LOOKUP_CODE
  INTO :VISIBLE_OPTION_VALUE
  , :PROFILE_OPTION_VALUE
  FROM FND_LOOKUP_VALUES_VL
  WHERE  LOOKUP_TYPE = 'XDO_OUTPUT_TYPE'"
COLUMN="\"Default Output Format\"(50)"

2. Set profile option value to Excel (or RTF etc) at the required levels

Navigate to System Administrator, Profile, System

Find you profile option XML Publisher Default Format and set values as required.

3. Modify Form FNDRSRUN.fmb

Copy and open form $AU_TOP/forms/US/FNDRSRUN.fmb

Open Program Unit WORK_ORDER

Find the line:

:templates.format := 'PDF';

Note this is line/char 349/41 in Release 11i FNDRSRUN.fmb 115.169 or 359/40i in Release 12 FNDRSRUN.fmb 120.29

Change to:

-- GR 24-JAN-08 Override default BI Publisher layout output format
--:templates.format := 'PDF';
:templates.format := nvl(fnd_profile.value('XXV8_XMLP_DEFAULT_FORMAT'),'PDF');

4. Compile Form FNDRSRUN.fmb to FNDRSRUN.fmx

Copy the new FNDRSRUN.fmb to your custom top forms/US directory

Compile the fmb to fmx.

FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/plsql
f60gen Module=FNDRSRUN.fmb Userid=apps/apps > genform.log

5. Replace standard FNDRSRUN.fmx with modified version

Note: Replace XXV8_TOP with your custom top.

cd $FND_TOP/forms/US
mv -i FNDRSRUN.fmx FNDRSRUN.fmx.orig
ln -s $XXV8_TOP/forms/US/FNDRSRUN.fmx FNDRSRUN.fmx 

6. Test it out.

Some caveats here:

  • This is an unsupported form modification that will be blown away when the FNDRSRUN form is upgraded (hopefully when the real solution appears).
  • The instructions above only replace the executable .fmx version of the FNDRSRUn file, so if FNDRSRUN is regenerated/recompiled via adadmin or similar then the modification will not be in place. Redo the "replace" step.
  • This modification assumes a template type of RTF. PDF can only produce PDF output, so you need to watch this when you set the profile option value.
  • If you always click on the Options button, then use a forms personalization instead of this forms modification.

Thursday, January 10, 2008

I'm IT - I've been OraBlog tagged.

Tim Dexter tagged me in the current round of OraBlog tag, so as per the rules here are the 8 things you might not know about me:

  1. Pianist? nope, Computer Science. My career could have been as a musician. When it came to University I auditioned and got accepted into a piano performance spot, so my choice was Computer Science or Musician. Either way I'd be tapping a keyboard so my $$ motivated decision meant Comp Sci was it. Plus my brother is better on the music front ;-)
  2. Mmmm, Pork Chops Chocolate. If you leave chocolate near me you won't see it again.
  3. First Day Blues. As a graduate intake at Oracle New Zealand back in 1995, on my first day no one expected me there, so my first week was spent with a SQL*Plus and DBA training manual.
  4. Pickled. I once worked in a pickle factory in Japan.
  5. The Beach. I grew up near, or should that be on, the beach - nothing better than salt water and fresh warm air. It's a shame I'm not a windsurfer in Windy Wellington though.
  6. Patchsets. I'm behind the R11i and R12 patchsets blogs that some Oracle Apps folks might find useful.
  7. Games. Another influence when I was young was Rally-X. So much so a couple of years back I put together an arcade style MAME machine that included hacking a keyboard and wiring up a custom joystick port.
  8. Adrenalin Rocks. Every once in a while I gotta have a bit of a thrill and there ain't much better than in New Zealand. If you're in the "posh part of down under" as Tim says, I'll be more than happy to help you out with the 134m Nevis Bungy, 7m waterfall Kaituna Rafting, Kayaking Aniwhenua Falls or Mountain Biking the Queen Charlotte Walkway.

Now to the tagging, bound to tag someone already tagged, but hey, will try anyway!

  1. Anil Passi
  2. Atul Kumar
  3. Bas Klaassen
  4. Fadi Hasweh
  5. Jornica
  6. Lucas Jellema
  7. Patrick Wolf
  8. Sam