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:
we can always achieve this modifying CUSTOM.pll instead of modifying the form and getting it wiped out when a patch is applied.
sukarna
Modify CUSTOM.pll to set the default value based on a profile option. This is the neat way to achieve the desired functionality.
sukarna
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
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
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
Check it under $AU_TOP/forms/US
Sukarna
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
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
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
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');
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
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
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
Post a Comment