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.