Thursday, August 14, 2014

Query to find all Oracle Discoverer Reports/Workbooks

Run the following sequence of queries in APPS schema to find all Discoverer Reports/Workbooks that exist in the system.

Query1: Run the following query to know the name of table
SELECT owner,table_name
  FROM all_tables
 WHERE owner = 'EUL4_US'
   AND table_name LIKE '%DOCUMENT%';

Generally the above query returns table_name as 'EUL4_DOCUMENTS' 
Meaning, all the discoverer report/Workbook names are stored in this table

Query 2: Run the following query to see all reports
SELECT DOC_ID,
       DOC_NAME,
       DOC_DEVELOPER_KEY,
       DOC_CREATED_BY
  FROM EUL4_US.EUL4_DOCUMENTS;

Note:
If the First Query is not returning any data then try the following query and manually find the table name.
   SELECT owner,table_name
     FROM all_tables
    WHERE table_name LIKE '%EUL%DOCUMENT%';

You have to modify the 2nd query in this case with the new OWNER.TABLE_NAME

Register a Discoverer report in Oracle Apps

Steps for Register a Discoverer report in Oracle Apps

1. Login to Application Developer-->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.
    E.g:
  • Function: TEST_DISCO
  • User Function Name: Discoverer Report
  • Description: Discoverer Report
3. In Properties Tab
  • Select Type “SSWA plsql function”
  • Maintenance Mode Support – None
  • Context Dependence - Responsibility
4. In Form Tab Parameters --> Enter the Workbook name created previously
     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.RunDiscoverer

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.