Documenting EUL, Discoverer Workbook, Worksheet

Do you need to document EUL Schema, Oracle Discoverer Work-books,
worksheets?

Discoverer is a great ad-hoc and BI tool. People working and using this tool often
need to document the EUL (End User Layer) but there is no straight forward ways to
do it. I have seen clients run some scripts against the EUL meta data and then create
a word doc file for later referencing.

Lets focus on some of the key EUL tables which could be used to document the
different Discoverer components.

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

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.eul4_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.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_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.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_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.eul4_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.eul4_documents

View Discoverer Reports through Oracle Apps
Self Service
In this article we will discuss how to enable Discoverer reports to be viewed through
Oracle Applications Menu structure.

We will take an example report say "AR - Invoice Aging Buckets" with developer key
"AR_INV_AGING_REPORT"

Let us assume there is a seperate responsibility to view AR reports such as "Accounts
Receivables Reports". We also assume that there is a menu (top level) called
"ALL_AR_REPORTS" which is attached to this responsibility using the Define
Responsibility form.

Using the Discoverer User tool we share the "AR - Invoice Aging Buckets" to
"Accounts Recievables Reports" responsibility.

Now we will begin creating the Function and menus for this workbook.
· Navigate > System Administrator > Application > Function
· Go to the Description TAB
· Enter Function Name = AR_INV_AGING_REPORT_DWB
· Enter User Function Name = AR - Invoice Aging Buckets
· Enter Description = AR - Invoice Aging Buckets

We appended a suffix of "_DWB" at the end of the function name to indicate this is a
Discoverer Workbook function. You may choose any other name for your function but
it is a good idea to keep the function name same as the developer key for easy
identification and tracking.

· Now Go to the Properties TAB
· Select Type = SSWA plsql function that opens a new window (Kiosk Mode)
· Maintenance Mode Support = NoneContext Dependence = Responsibility
Selecting the Kiosk Mode opens the Discoverer workbook in a new window.
· Now Go to Form TAB

· Enter Parameters => “workbook=AR_INV_AGING_REPORT&viewer=Y”
In the above we entered the workbook developer key and forcing the report to be
opened using Discoverer Viewer. (If your ICX profile is set to use Discoverer Viewer
then you may omit the part from "&" )
· Now Go to Web HTML TAB
· Enter HTML Call = OracleOASIS.RunDiscoverer and then Click on the Save
Button

Now that we have created our Form Function we will create a new menu to hold this
function and then attach this new menu to the "ALL_AR_REPORTS" menu.

Navigate > System Administrator > Application > Menus
Create a new menu "Aging reports"


Then attach the function to this menu


Now query the main menu "ALL_AR_REPORTS"


and add submenu to this main menu


Now we have finally linked all the pieces together. Wait a minute, just ensure that
the user who needs to access this report is given the "Accounts Receivables Reports"
responsibility. So when user "ARUSER" logs onto Self service signon, the user will
see the "Accounts Receivables Reports" responsibility as part of the menu list.
Clicking on this responsibility, the user will see the "Aging Reports" as one of the
sub-menu and underneath there will be the link to the discoverer workbook. If "all is
fine", clicking on the report link will launch a seperate window where user can enter
parameter values and execute the report.

NOTE: If the menu changes does not appear as expected then ask your DBA's to
bounce the Apache middle tier. Also, make sure all the Discoverer profile values are
set properly.

The eul_date_trunc function information from a date. The idea is that it
will "truncate" out all information EXCEPT for the element(s) that you specify
in the format argument. For example if you specify eul_date_trunc(date,'mon')
then you can consider the item to ONLY contain month information. Thhis is
logically equivalent to saying to_char(date,'mon'). However eul_date_trunc
goes one step further than to_char in that it keeps the return type as date.

This has several major advantages:
It allows the user to use Oracle formatting & localization.

It automatically gives you sensible ordering (i.e. The months will
be ordered Jan, Feb, Mar... instead of Apr, Aug... that to_char would
give you).

The one disadvantage of this is that as Oracle date routines insist
on certain parts of a date always being set ( Year, Month & Day )
then we have to provide defaults for these elements if they are not
present in the format asked for. These defaults are 1900, Jan & 1
respectively. Contrary to popular belief, these were not chosen at
random but because 1900 was a century date that started on a Monday.