Thursday, January 24, 2008

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.

16 comments:

Sukarna said...

we can always achieve this modifying CUSTOM.pll instead of modifying the form and getting it wiped out when a patch is applied.

sukarna

Sukarna said...

Modify CUSTOM.pll to set the default value based on a profile option. This is the neat way to achieve the desired functionality.

sukarna

Gareth said...

Hi Sukarna,

CUSTOM.pll and Forms Personalization are not an option with the version of the forms I looked at. If you look at the code, 'PDF' and the dynamic LOV associated to the Format field are harcoded. There is no trigger I can find that gives the hook required to change the default. You have to navigate to the "Options" block in order for the required trigger to fire - but I don't navigate to the "Options" block!!

If you have a working example using CUSTOM.pll then post or email the solution.

Gareth

Norman said...

Hi

Another way might be to write a PLSQL package to run reports. The package would need to be built as a concurrent program, and make use of function fnd_request.add_layout to set output_format to 'PDF' before fnd_request.submit_request.

Haven’t tried it, but the method is supported and wouldn't get blown away.

--Norman

Gareth said...

Hi Norman,

The goal here is to default the output for any report (both new and existing) in the simplest way possible.

If you are building your own report you already have complete control and can use my solution as well - both at submit time, and as logic as part of your add_layout call. Your suggestion could work for new reports, but we only need a stop gap until Oracle releases the code for the enhancement, so its not worth investing due to the fact that you'd need call your code for every report - and it adds a layer of complexity.

Gareth

Yura said...
This comment has been removed by the author.
Sukarna said...

Check it under $AU_TOP/forms/US

Sukarna

Ike Wiggins said...

Gareth, is there any reason why one couldn't just do a forms personalization for this and change the default lov using your example?

Also, just curious if you tried that on WHEN-FORM-INSTANCE or WHEN-NEW-ITEM-INSTANCE you tried defaulting the :TEMPLATES.FORMAT?

Ike Wiggins
http://bipublisher.blogspot.com

Gareth said...

Hi Ike,
Yes, there is a very good reason - the LOV is a completely dynamic LOV i.e. the LOV SQL is hardcoded in side a PL/SQL unit within the form. The PDF default is also hardcoded inside the form immediately before the SQL that creates the LOV. So whatever you do in Forms Personalizations/CUSTOM.pll it has no effect. I tried a whole lot of my bag of tricks ... and I have quite a few ;-) ... but nothing worked!
The form mod I use here changes the line that hardcodes the 'PDF' default.

PS. Hence the reason Tim also recommends using my workaround for now. For ref:
http://blogs.oracle.com/xmlpublisher/2008/01/25#a779

Gareth

Gareth said...

Hi Ike,
Just wanted to note that Forms Personalization can be used IF you navigate to the Options block, but I don't want to have to navigate to the "Options" block for every request in order to change the default.
Gareth

Paulb said...

I was able to get around this problem by altering the values in the table fnd_conc_pp_actions, before calling fnd_request.submit_request.

The API call to change the layout options was :

l_layout_return := FND_REQUEST.add_layout ('XXKLC',
'KLC_AUDIT_XML',
'en',
'US',
'EXCEL');

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

Hi Paul,
Thanks for the input, good to think about alternative solutions, however the method you mention wouldn't be visible through the form, which is what is achieved by this post.
It would be nice as an alternative to form modification, if it could be used via forms personalization.

Gareth

Anonymous said...
This comment has been removed by a blog administrator.
Ravi said...

Hi,

I am interested in the alternate approach, i.e calling package..solution writeen by paul

was able to get around this problem by altering the values in the table fnd_conc_pp_actions, before calling fnd_request.submit_request.

The API call to change the layout options was :

l_layout_return := FND_REQUEST.add_layout ('XXKLC',
'KLC_AUDIT_XML',
'en',
'US',
'EXCEL');

where do I call the above code, in my package or I have to personalize the submit request form?

Thanks
ravi

Gareth said...

Hi Ravi,

The code would go in the calling concurrent program, eg. report after report trigger, or in pl/sql package etc.

However, 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.

Regards,
Gareth