Error 500--Internal Server Error in discoverer.

Sometimes we get error while retrieving the list of values for a Discoverer report in the Application showing 

500 Internal Server Error as shown below.

This can be resolved by following the below steps.

1)Select the preferences on the top right corner of the page.

2)Click on the preferences and a New window will be displayed as shown below.

Here change the value for Cancel list-of-values retrieval after.
(In my case i have changed it to 240 as the column contains very huge data for LOV's.)

3)Change the value and click on apply.

And the issue is resolved.

If the same issue occurs in the Discoverer Desktop (or) Discoverer Plus as shown below.

1) Go to Tools -> Options.

A pop-up will be opened as shown in below screenshot.

2)Click on the Query Governor Tab and change the value for Cancel value retrieval after.
3)Click on OK.

The issue is resolved.

Challa.

How to run Discoverer Reports in Multi-Org Environment?

When working in R12 MOAC environment, sometimes discoverer reports which are based on some context based view will not give data. Then do the following setup to make the discoverer reports work in MOAC environment.

1) Create a Security Profile in HR

1) Run concurrent program Security List Maintenance

1) Tag this profile to MO: Security Profile option at responsibility level.

1) Update profile option “Initialization SQL Statement – Custom” with following pl/sql block at responsibility level.

begin if (fnd_profile.value(‘XLA_MO_SECURITY_PROFILE_LEVEL’)= 0) 

then mo_global.init(‘S’); 

else 

GL_SECURITY_PKG.init(); mo_global.init(‘M’); 

end if; 

end;

When multiple organizations are included in Security Profile: TEST SECURITY PROFILE, This profile is attached to respDiscoverer EUL Management.

Challa.

Discoverer Report Registration in Oracle Applications

The following are the steps,to register a discoverer report in Oracle Apps. Make sure you followed each and every step correctly.

1. Login to Application Developer or System Adminstrator -->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.

E.g
Function: UNPAID_INVOICES
User Function Name: Supplier Invoices Validated But not unpaid.
Description: Give meaningful meaning.

In Properties Tab:

Select Type “SSWA jsp function”
Maintenance Mode Support – None
Context Dependence - Responsibility


4. In Form Tab  Parameters --> Enter the Workbook name created previously

mode=DISCO&workbook= Supplier Invoices Validated But Unpaid Report(workbook name you created in discoverer desktop)

E.g
workbook=TEST_WORKBOOK
In case you want to give some parameters then you can use:
workbook=<workbook_name>&parameters=<Parameter_nam e1>~<value1>*<Parameter_name2>~<value2>*<Parameter _name3>~<value3>*Example
workbook=<workbook_name>&Parameters=age~26*salary~ 1000*

5. In Web HTML Tab

Enter HTML Call - OracleOasis.jsp

6. Now you can add this Function in any existing or new Menu

Also Note that Discoverer Report cannot be invoked when you’ve direct login into Oracle Applications through http://<url>:<port number>/dev60cgi/f60cgi
You need to access through logging in from the browser web page. Otherwise it will give an Error.

7. Now navigate to Responsibility which contain the Menu to which you added this function.

8. Click the Function Name to Display the report.

Challa.

Oracle Discoverer Administrator and Discoverer Desktop

Oracle Discoverer is a business intelligence tool to support organizational decisions and  data will show in the form of excel format.

Components of discoverer:-

1.Discoverer Administration Edition
2. Discoverer Desktop Edition

Architecture Of Discoverer Administartion Edition:-

i.End User Layer
ii.Business Area
iii.Business Folder

Overview of Business Areas:-
-A business area is a collection of related information in the database.
-A business area is a set of related information with a common business purpose
-For example, information about Sales may be stored in one business area, whilinformation about Cops is stored in another business area-In simple words it can be termed as collections of objects in a particular module

Overview of Business Folders:-
-Simple Folders – Folders that are based on a database table (e.g.: ITEM)
-Custom Folders – Folders that contain a custom SQL query.
-Complex Folders – Folders that are based on multiple simple folders.

Here are the steps for creating the ‘Business area’

Open Discoverer Administrative Edition

Logon to Discoverer Administrative Edition using SYSADMIN user



Click Connect


Choose a Responsibility and Click OK




Click Create a New Business Area and Click Next


Select Any User and Click Next


Expand the Node and Select Any Table or View And Click Next


Click Next


Name the Business Area and Description Appropriately And Click Finish the Business Area Will be created and you would view the following screen



Close the Administrative Tasklist Window and Expand the Business Area


Delete The Folder under the Business Area


Click Yes


Now the business Area is Empty
Logon to SQL Plus and Create a View according to the requirement.
Relogon to Discoverer Administration Edition to have the Schema Refreshed And Open the Business Area Created Earlier.

Right Click on the Business Area Created and Select the New Folder from Database Option





Click Next




Select the Schema APPS and Click Next as shown below




Expand the APPS Schema


Select the View Created at SQL Plus and Click Next


Click Finish
The Folder is Created

Expand the Business Area and you can view the Folder


Click Tools à Security Menu


Assign the Users/Responsibilities who can access the Business Area and Click OK
Here are the screen shots for creating the workbooks in the Discoverer Desktop:-

Logon to the Discoverer Desktop Edition to create Work Books


Login as SYSADMIN User


Select System Administrator Responsibility and Click Ok




Select Create a new workbook option


Select one of the Display Style as per the requirement and Click Next


Select the Business Area and the folder on which you would like to create the Workbook and Click Next


Check Show Page Items and Click Next


You could add the condition required by clicking New.




Select New Parameter Option from the List You will get the following screen




Enter the Name, Prompt, Description and other Fields and Click OK

Click Ok

Click Next



You can create the Sort Condition so that the Workbook would sort the data accordingly.

Click Add

Select the Field on which you would like to sort the data and Click ok

Add as many sorts you need

Click Finish

You workbook is Created.



Go to File- Manage- wokbooks  Properties


Give the Identifier and Description


Click Ok


Click Yes
Go to Sheet à Rename Sheet Menu

Give an Appropriate Name and Click ok



Click Save


Select Database



Give an appropriate name and Click Save
Goto File - Manageworkbooks- sharing menu
Select the Workbook and assign it to the responsibility who can access the workbooks as shown in the screen


Click Ok.


Challa.