Discoverer Table and Folders Workbooks Queries Tables

Key EUL tables for the different Discoverer components.

List of Business Areas

select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul5_bas
where ba_created_by like 'DISCO'

List of Folders

select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul5_objs f,
disco.eul5_ba_obj_links l,
disco.eul5_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name

List of Folder Items

select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul5_expressions i,
disco.eul5_objs f,
disco.eul5_ba_obj_links l,
disco.eul5_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name

List of Folder Joins

select key_description
from disco.eul5_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027

(substitute the numeric id from the Folders obj_id (see the List of Folder query)

List of Workbooks

select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul5_documents

Note: In the above examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples

change tthe Discovere report Owner and Check the Discoverer Report Owner

The trick is to change the owner of the workbook to an Apps user then log in as that user and delete the workbooks. I recommend doing this in Desktop.

The workbooks are stored in a table called EUL5_DOCUMENTS and the column that contains the ID of the owner is called DOC_EU_ID. This is what we will change.

Step 1:

Log in to SQL as the owner of the EUL and run this:

SELECT DOC_EU_ID, DOC_NAME FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

obviously changing eul_owner to be the EUL owner you will see the list of workbooks that you want to delete. Make a note of the DOC_EU_ID.

Step 2:

Let's get a count.

SELECT COUNT(*) FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

obviously again changing eul_owner to be the EUL owner. Make a note of the COUNT

Ok so far?

Step 3:

Next, let's find the name and ID of a real Apps user who will become a temporary owner for your workbooks. The following code will list all Apps users who currently own workbooks in the database:

SELECT DISTINCT

USERS.USER_NAME APPS_USER,

DISCO_USERS.EU_USERNAME,

DISCO_USERS.EU_ID,

DISCO_DOCS.DOC_CREATED_BY

FROM

EUL5_DOCUMENTS DISCO_DOCS,

EUL5_EUL_USERS DISCO_USERS,

APPS.FND_USER USERS

WHERE

SUBSTR(DISCO_DOCS.DOC_CREATED_BY, 2)) = USERS.USER_ID AND

DISCO_USERS.EU_ID = DISCO_DOCS.DOC_EU_ID;

Pick the Apps user you want to log in. Make a note of the DISCO_USERS.EU_ID. You will need this later.

Note: the reason I suggest using someone who already owns workbooks is this saves you from having to create an entry in the EUL5_EUL_USERS table. All users who own workbooks have an entry in this table.

Step 4:

Now let's check whether the original owner shared any workbooks. These will need deleting too.

First let's check if any sharing has been done.

SELECT COUNT(*) FROM

EUL5_DOCUMENTS, EUL5_ACCESS_PRIVS

WHERE

EUL5_DOCUMENTS.DOC_CREATED_BY = 'eul_owner' AND

EUL5_DOCUMENTS.DOC_ID = EUL5_ACCESS_PRIVS.GD_DOC_ID;

Once again replace eul_owner with real owner name. If this returns zero then no sharing has taken place.

If this returns a number greater than zero then we need to note that these records have to be removed. We'll let the system do it for us and recheck later. For now make a note of the number of shares.

Step 5:

Next we'll update the owner of the workbook(s) to the DISCO_ID you figured out earlier.

UPDATE EUL5_DOCUMENTS

SET DOC_EU_ID = the_ID_from_step_3

WHERE DOC_CREATED_BY = 'eul_owner';

You should get the same number updated as you got earlier when you did the count in Step 2. If this is true then COMMIT the change.

Step 6:

Now log in to Desktop as the Apps user that you picked and delete the workbooks. When you log in you will be informed that the workbook was created under another account and asked if you want to open it under that account or the account you are logged in as. You should open it using the account you are logged in as.

Use Desktop's normal delete mechanism for deleting the workbooks. From the toolbar use File | Manage Workbooks | Delete. You should see and delete the same workbooks you saw in Steps 1 and 2

Step 7:

Having deleted the workbooks, if there were any shares, let's make sure that all of these have gone too. You will need the DOC_EU_ID I told you to take a note of in Step 1.

SELECT COUNT(*) FROM

EUL5_ACCESS_PRIVS

WHERE

EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND

EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;

If this now returns zero then all sharing has been removed.

If this still returns a number greater than zero then we need to manually delete those records.

Before you do this, for safety take a backup of EUL5_ACCESS_PRIVS then run this:

DELETE FROM EUL5_ACCESS_PRIVS

WHERE

EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND

EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;

You should get a message that X rows have been deleted. If this count equals what you got earlier a few moments ago then COMMIT the delete. If you get a different count then use ROLLBACK and recheck your code.

change tthe Discovere report Owner and Check the Discoverer Report Owner

The trick is to change the owner of the workbook to an Apps user then log in as that user and delete the workbooks. I recommend doing this in Desktop.

The workbooks are stored in a table called EUL5_DOCUMENTS and the column that contains the ID of the owner is called DOC_EU_ID. This is what we will change.

Step 1:

Log in to SQL as the owner of the EUL and run this:

SELECT DOC_EU_ID, DOC_NAME FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

obviously changing eul_owner to be the EUL owner you will see the list of workbooks that you want to delete. Make a note of the DOC_EU_ID.

Step 2:

Let's get a count.

SELECT COUNT(*) FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

obviously again changing eul_owner to be the EUL owner. Make a note of the COUNT

Ok so far?

Step 3:

Next, let's find the name and ID of a real Apps user who will become a temporary owner for your workbooks. The following code will list all Apps users who currently own workbooks in the database:

SELECT DISTINCT

USERS.USER_NAME APPS_USER,

DISCO_USERS.EU_USERNAME,

DISCO_USERS.EU_ID,

DISCO_DOCS.DOC_CREATED_BY

FROM

EUL5_DOCUMENTS DISCO_DOCS,

EUL5_EUL_USERS DISCO_USERS,

APPS.FND_USER USERS

WHERE

SUBSTR(DISCO_DOCS.DOC_CREATED_BY, 2)) = USERS.USER_ID AND

DISCO_USERS.EU_ID = DISCO_DOCS.DOC_EU_ID;

Pick the Apps user you want to log in. Make a note of the DISCO_USERS.EU_ID. You will need this later.

Note: the reason I suggest using someone who already owns workbooks is this saves you from having to create an entry in the EUL5_EUL_USERS table. All users who own workbooks have an entry in this table.

Step 4:

Now let's check whether the original owner shared any workbooks. These will need deleting too.

First let's check if any sharing has been done.

SELECT COUNT(*) FROM

EUL5_DOCUMENTS, EUL5_ACCESS_PRIVS

WHERE

EUL5_DOCUMENTS.DOC_CREATED_BY = 'eul_owner' AND

EUL5_DOCUMENTS.DOC_ID = EUL5_ACCESS_PRIVS.GD_DOC_ID;

Once again replace eul_owner with real owner name. If this returns zero then no sharing has taken place.

If this returns a number greater than zero then we need to note that these records have to be removed. We'll let the system do it for us and recheck later. For now make a note of the number of shares.

Step 5:

Next we'll update the owner of the workbook(s) to the DISCO_ID you figured out earlier.

UPDATE EUL5_DOCUMENTS

SET DOC_EU_ID = the_ID_from_step_3

WHERE DOC_CREATED_BY = 'eul_owner';

You should get the same number updated as you got earlier when you did the count in Step 2. If this is true then COMMIT the change.

Step 6:

Now log in to Desktop as the Apps user that you picked and delete the workbooks. When you log in you will be informed that the workbook was created under another account and asked if you want to open it under that account or the account you are logged in as. You should open it using the account you are logged in as.

Use Desktop's normal delete mechanism for deleting the workbooks. From the toolbar use File | Manage Workbooks | Delete. You should see and delete the same workbooks you saw in Steps 1 and 2

Step 7:

Having deleted the workbooks, if there were any shares, let's make sure that all of these have gone too. You will need the DOC_EU_ID I told you to take a note of in Step 1.

SELECT COUNT(*) FROM

EUL5_ACCESS_PRIVS

WHERE

EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND

EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;

If this now returns zero then all sharing has been removed.

If this still returns a number greater than zero then we need to manually delete those records.

Before you do this, for safety take a backup of EUL5_ACCESS_PRIVS then run this:

DELETE FROM EUL5_ACCESS_PRIVS

WHERE

EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND

EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;

You should get a message that X rows have been deleted. If this count equals what you got earlier a few moments ago then COMMIT the delete. If you get a different count then use ROLLBACK and recheck your code.

Discoverer Info

THURSDAY, MAY 7, 2009

How to query/find discoverer reports in Oracle using APPS Schema

The table to store discoverer is stored in the EUL Schema. For e.g. if the name of EUL is EUL4_US then a schema with that name will be created in oracle database. Run following query to know the name of table

SELECT owner,table_name
 FROM   all_tables
 WHERE  owner = 'EUL4_US' AND table_name LIKE '%DOCUMENT%'

The above query in this case will list table_name EUL4_DOCUMENTS

All the discoverer report names are stored in this table, so

SELECT * FROM EUL4_US.EUL4_DOCUMENTS

should display name, owner etc of discoverer reports

TUESDAY, FEBRUARY 17, 2009

Unable to Retrieve All Rows Message in Discoverer

Problem: Trying to export report data into excel or when clicking on Tools--> Retrieve all Rows, Not all Rows have been Retrieved. Data may be inaccurate. error message is displayed.


Resolution: By Default in discoverer only 10000 records are set to be retrieved. This needs to be changed to a higher number in order to avoid this problem.
Click on Tools-->Options. Select Query Governer Tab and change the value in Limit retrieved query to99999 to allow maximum number of records to be fetched.


Keywords: Discoverer 10g

MONDAY, MAY 12, 2008

Create List of Values (LOV) in Discoverer

The Item classes in Discoverer are treated as List of Values (LOVs). A List of value can be created and referenced by several fields of the folder.
Following are the steps to create LOV in discoverer. In the example below I am creating LOV on US States.
1) Connect to Discoverer using Admin and select the business area where folder/LOV needs to be created.
2) Create a custom folder for US States using following query

select * from ar_lookups
where lookup_type like 'STATE'

3) Select the field for which Item class is to be created and right click on that to select New Item Class. Click next on the wizard and finally click finish button to complete creation of Item class. In our example, the LOV is created on the lookup code field.

4) Now if any parameter is created based on Lookup Code column then the List of value will be attached to it.
5) This LOV can also be referenced by another column. For example there is another folder which has column Bill to State. Select that column and right click to go to the properties. Click on Item Class and select the Item class created in step 3.

TUESDAY, APRIL 8, 2008

Dependent Value Parameter in Discoverer

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.

MONDAY, APRIL 7, 2008

Not Required Parameter in Discoverer

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

Discoverer Query to Display Document Name, Folder, Business Unit

Following query can be used to display Document Name, Document Key, Folder Name, Folder Key, Documents Sheets, Business Unit, Document Created By in discoverer.
Please note that the query is in crude format and not tested, any suggestion to improve this is appreciated.

SELECT DISTINCT b.doc_developer_key Document_developer_key
              , b.doc_name document_name
              , a.qs_doc_details worksheet_name
              , c.obj_name folder_name
              , c.obj_developer_key folder_developer_key
              , d.ba_name Business_unit
              , fu.user_name created_by
 FROM           eul4_us.eul4_qpp_stats a
              , eul4_us.eul4_documents b
              , eul4_us.eul4_objs c
              , eul4_us.eul4_bas d
              , eul4_us.eul4_eul_users e
              , eul4_us.eul4_ba_obj_links f
              , fnd_user fu
 WHERE          a.qs_doc_name = b.doc_name
 AND            a.qs_doc_owner = e.eu_username
 AND            b.doc_eu_id = e.eu_id
 AND            c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
 AND            c.obj_id = f.bol_obj_id
 AND            d.ba_id = f.bol_ba_id
 AND            TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)

Oracle Discoverer Reports

Types of Discoverer Reports
1) Table

2) Page Detailed Table

3) Crosstab Report

4) Page Detail Crosstab

We can create Page Detailed report using Table report next select Page Items and give the items.

We can hide duplicate records using the check box click HIDE DUPLICATE ROWS.

Next Conditions.

Next Grouping and sorting.

Add new sheet : Go Sheet à New Sheet.

Format: We can change the color and background, font.

Conditional Format: Format à Exception we can do conditionally change the format or color.

Title: Format à sheetà title and Background.

Tools:

1) Conditions: We can give for a particular column

    Create Calculation, Select Item, New Parameter, Select Parameter, Sub Query

2) Sort:  Lo-Hi and Hi- Lo

3) Calculations:

4) Percentages:

5) Totals: Grand Total or Sub Total For Each Level. [Arithmetic Operations like Sum, Average, Max, Min, Count, Percentage etc]

6) Parameters: We can create Parameters here. And Assign to Calculation or format.

7) Options:

  a) Query Governor:  Retrieve the data incrementally in groups of 100 rows.

  b) Cancel Value Retrieval after 5.5 Seconds

Graph: New Graph and we can create a graph.

The WorkBook extension is DIS.

Discoverer Admin:

Tools à Privileges à Admin privileges and Desktop Privileges.

Select The User and give Permissions.

Here We unselect Save to Database so then the user cannot save edited workbook in database.

Toolsà Options à Query Governor à give the time limit for query execution.

Create a Item Class:  In Dis Admin goto the item which u want to create item class

And  Right Click  select New Item Class and select field and next finish.

Generic user:

First create a workbook and share to scott(generic user) first next he will share to all the people. then we delete the first workbook. File manage workbook delete.

Fantrap Detection: This is when Cartesian join it kills automatically. This one is in tools-->options--> advanced --> fantrap detection check that. It will delete Cartesian joins.

Folders:

1) Simple: This is from one table

2) Complex: This is from 2 or more tables.

3) Custom: From Sql Query

4) Summary Folder: It Creates a Materialized View.

We can create FOLDER, Insertà Folderà pick New/Custom/From database

Business Area

We can create a BA , Insertà BAà New/ From Database

  

EUL

We can create or delete EUL, Toolsà EUL Managerà create / delete EUL

 We can filter the Data at Folder Level.

Vpd: Discover for vpd in  eul_$postlogin_trigger name for this function.

We Can register our own Functions  in Discoverer Admin à Tools à Register Plsql Functions. And we can use in workbooks at à tools à conditionsà Functions.

Sharing The WorkBook:

Go to Discoverer Desktop à File àManage WorkBooks à  Sharing.

Export And Import:

 We can export and import EUL and BA.

Dis Admin à File à Export à Chose EUL or BA or Selected Objects à and Give File name and location à The Extension is .eex and save the log file also.