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.

How To Create Custom Web-Adi Integrators

Web Applications Desktop Integrator (Web ADI) is a self-service Oracle application, accessed using a browser. Unlike ADI, the Web ADI software is not installed on individual client machines, as the processing takes place on the server rather than on individual client PCs.

Web ADI generates Microsoft Excel or Word documents on your desktop, and downloads the data you have selected from the application. You can modify the data in the Microsoft Excel or Word documents, working either online or offline, and upload the data using Web ADI. In addition to the Web ADI integrators supplied with Oracle Applications, you can create your own integrators to allow download and upload data from Oracle windows or direct from Web ADI.

Except for HRMS, it seems to me that other Oracle Development teams have not utilized Web ADI to its full potential. There were some discussions in forums and Oracle blogs on whether this approach is even needed. I feel like every available interface and API that let you integrate with legacy or 3rd party applications should come with a standard Web ADI integrator.

Creating Custom Integrators

Thanks to the HRMS Web ADI, you can define your own custom HRMS and non-HRMS integrators and in a combination with the seeded Oracle APIs and interfaces, implement elegant solutions for downloading or uploading data. Examples for frequently used custom integrators include upload of employees, users, POs and requisitions, AP invoices, project events, AR transactions, etc.

Creating custom integrators is done by a system administrator. Let’s define an integrator to create employees to explain how to create custom integrators. Employees and employee assignments can be created using the standard HR_EMPLOYEE_API. Our custom integrator will upload data to this API.

Grant Access to Required Functions

As System Administrator, navigate to Application > Menu, and query up ‘Desktop Integration Menu’. Make sure to include functions required for respective integrators. In case of custom Create Employee integrator, you’ll need to include the following functions (no prompts are necessary):

• HR Integrator Setup Integrator
• HR Maintain Integrator Form Functions Integrator

Create ‘HR Integrator Setup’ Web ADI Document

• Log in as Desktop Integration responsibility. Select Create Document from the menu. The Settings page of the Web ADI wizard appears.
• In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
• In the Integrator page, select the seeded integrator “HR Integrator Setup”.
• In the Layout page, select the default layout “HR Integrator Setup”.
• In the Content page, select None.
• In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.
• A spreadsheet document is created, containing all the fields you need to enter to create your own integrator.

• Define all fields to create your Create Employee integrator and upload it to the database.

Controlling Access to Custom Integrators

Custom integrators, like predefined integrators, must be associated with form functions. Follow the steps below to associate your Create Employee integrator with the functions required to define Web ADI layout and Web ADI document.

• Select Create Document from the Web ADI menu.
• Select a spreadsheet viewer. Leave the Reporting check box unselected to download data for updating.
• Select HR Maintain Integrator Form Functions Integrator.
• Enter the Application Short Name (for example, your custom application name). Optionally, enter the integrator name.
• The displayed spreadsheet shows your selected custom integrators, and has a column for associating form functions. Enter the form function required for controlling access to the integrator.
• Upload your changes to the database.

Define Layout of Custom Integrator

Each integrator must have at least one layout specifying the columns to appear in the spreadsheet. To define a layout:

• Choose Define Layout from the Web ADI menu. The Layout page appears.
• Select the integrator for which you want to define the layout, and choose Go.
• Choose the Define Layout button to define a new layout.
• In the Define Layout page, enter a unique name for the layout. This appears whenever the user is required to select the layout, so should clearly indicate the purpose of the layout.
• Enter a unique key. The key is used internally by the system to identify the mapping.
• The Field List defaults to the complete list of columns available in the view or API.
• Choose Continue. In case of Create Employee integrator, the layout is based on the the HR_EMPLOYEE_API columns. In the next page, you select a subset of these fields to include in your layout.

Create ‘Create Employee’ Spreadsheet

And finally, using the custom integrator and layout defined above, create a new ‘Create Employee’ WebADI upload spreadsheet:

• From the Navigator window (Oracle Personal Home Page), click on ‘Desktop Integration’ responsibility. The Settings page of the Web ADI wizard appears.
• In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
• In the Integrator page, select your new integrator ‘Create Employee’.
• In the Layout page, select the default layout “Create Employee”.
• In the Content page, select None.
• In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.

• A spreadsheet document is created and ready to use for creating employees.
• Save a local copy to your PC.

Basic Steps to Enable Multi-Org

Define the Organization Structure.
=============================================================
Plan and define the entities in your organizational structure.A successful implementation of Multiple Organization Support in Oracle Applications primarily depends on correctly defining your organization structure in the hierarchy used by Oracle Applications. A careful analysis and design of a company’s organization structure is critical for future success.
===================================================================
The following points describe how the Multi-Org model relates organizations:
=============================================================
• A Business Group is the highest level of the structure and has no accounting impact.The Business Group determines which employees will be available to Sets of Books and Operating Units related to that Business Group.
• Set of Books is the highest level which impacts the accounting side of business.
• Set of Books is associated with a single Business Group, multiple Sets of Books may be associated with a single Business Group.
• Each Set of Books may have a different chart of accounts structure, calendar, or functional currency.
• Each Legal Entity is associated with a single Set of Books, multiple Legal Entities may be associated with a single Set of Books.
• Each Operating Unit is associated with a single Legal Entity, multiple Operating Units may be associated with a single Legal Entity.
• An Inventory Organization may be associated with any Operating Unit within the same Set of Books.
===============================================================
Convert to Multi-Org:
===============================================================
After defining at least one operating unit, use ADADMIN to convert to Multi-Org. This step only needs to be perform one time. After ADADMIN is run, the Seed Data Replication program copies the applicable setup data delivered with Oracle Applications for each defined operating unit. If you add additional operating units, you can run the See Data Replication program manually at any time. Note that seed data is only data that comes with the database and does not include any operating unit specific setups you may perform.Run ADADMIN to Convert to Multiple Org
• When you select the adadmin option, Convert to Multiple Organization architecture, the Multi-Org switch is automatically turned on; a flag called multi_org_flag, is set to Y. The seed data replication program is then executed and processes according to the value of the multi_org_flag.
Run Seed Data Replication Seed data is replicated under the following circumstances:
• Submit the Replicate Seed Data concurrent request using the System Administrator responsibility.
The seed data replication program works in two modes:
• Single organization: This mode applies when the seed data replication program is executed within the context of a particular operating unit (or Org_ID).
• All organizations: This mode applies when the seed data replication program is executed without an operating unit context.
===================================================================
The following steps are required to add new operating units to your Organizational Structure:
=============================================================
• Revise the Organization Structure
• Define Sets of Books (optional)
• Define Organizations and Relationships
• Define Responsibilities
• Set Profile Options for each Responsibility
• Define Inventory Organization Security (optional)
• Implement the Application products
• Secure Balancing Segment Values (optional)
• Run the Setup Validation Report (recommended)
• Implement Document Sequencing (optional)
• Define Intercompany Relations (optional)

Changes in Existing Web-Adi Letter Column/Data

Changes in Existing Web-Adi Letter Column/Data

====================================================================

1. Find the Integrator Name of the letter.Form Function name is same as Integrator Name.

2. Find the form function.

3.SELECT integrator_code, application_idFROM bne_integrators_vl vlWHERE user_name IN ('XX Request for further references', 'XX Sorry Interview did not work') ;Here you find the Integrator Code.

4. Use FNDLOAD command to download the Integartor as ================================================================

FNDLOAD apps/hp75400 0 Y DOWNLOAD $BNE_TOP/admin/import/bneint.lct XX_HR_2_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN="PER" INTEGRATOR_CODE="HR_2_INTG"

=================================================================

The integrator will be downloaded to the current location from where the command issued as XX_HR_2_INTG.ldt for above example.Issue this command to the application server.

5.open XX_HR_2_INTG.ldt file with wordpad here you will find the Integrator Interface code name.

6.Now issue the following commandselect user_name from BNE_INTERFACES_VL where INTEGRATOR_CODE LIKE 'HR_2_INTG'

7.From above querry you will find the view name used for this letter generation.ie, user_name form above querry obtain is nothing but the database view name.

8.Now by opening this view you can modify the select statement used in creation of this view.

9.Recreate the view after changes are done.

10. Finish..now you can check the data by generating letter using web-adi.

=============================================================

Very Important Link for this : http://oracle.anilpassi.com/fndload-for-oracle-web-adi.html