How To Open A Worksheet Directly From Oracle Applications Menu ?


Hello Friends, many of my past work involve integrating oracle discoverer with oracle applications, here there is one of the ways to leverage both to integrate within applications.

 How To Open A Worksheet Directly From Oracle Applications Menu ?

 

 If a workbook has 4 worksheets how to default to a particular worksheet. This article will help you to pass the worksheet identifier and parameter directly while opening a workbook. The worksheet identifier and the worksheet parameters for the workbook has to be specified in the form function with below mentioned guidlines.
 

Form Function  Definition

 

Function Name

Internal Function Name

Function User Name

Function name (as shown on the menu)

Type

WWW

Description

Function description

Parameters

workbook=workbookname
&PARAMETERS=sheetid~worksheet id*param_parameter name One~Parameter One Value*param_parameter name Two~Parameter Two Value*

HTML Call

OracleOasis.RunDiscoverer

 

 Parameters in the parameter list are delimited by the characters

“param_” at the beginning of each parameter and
“~” as delimiter between parameter name and value and
“*” at the end of each parameter

 ICX interprets the PARAMETER list and passes the parameters to Discoverer in the required URL format.

 Example 1

 If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT and worksheet id is 1 then parameter will be as follows

 workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*

 Example 2

If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT , worksheet id is 1 and the value for worksheet parameter “deptname”  to be passed is 10 then parameter will be as follows

workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*param_deptname~10*

 Example 3

If a workbook has two parameters “Company” and “City” you would specify in the Parameter list of the Form funtion:

param_Company~Oracle Corp.*param_City~Redwood Shores* 

Thanks – Shivmohan . do share your feedback

Sample Example on How To Extract Worksheet IDs of a Workbook through Discoverer Desktop via the d51wkdmp utility


Sample Example on How To Extract Worksheet IDs of a Workbook through Discoverer Desktop via the d51wkdmp utility

Syntax:

d51wkdmp.exe <Workbook_Name> <Output_File> DB <Connect_String>  <Eul_Schema> -f

(1) Log into your pc where Discoverer Desktop is installed.

(2) Find out the exact location of the ‘d51wkdmp.exe’ file.

(3) Click <Start> <Run> and enter ‘cmd’ to go to command prompt.

(4) Enter and run the following at the prompt, for example:
C:\> d:\oracle\BI_1012\bin\d51wkdmp.exe “Employee By Dept” Outfile2.txt DB scott/tiger@TestDB1 Eul_owner -f

WHERE there are 3 Worksheets in this example.

RESULT:  Outfile2.txt


EUL Item Reference
IoId = 16
Id = 100671
Identifier = DEPTNO
Name = Deptno
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 24
Id = 100664
Identifier = EMPNO
Name = Empno
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 32
Id = 100665
Identifier = ENAME
Name = Ename
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 40
Id = 100666
Identifier = JOB
Name = Job
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 48
Id = 100667
Identifier = MGR
Name = Mgr
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 56
Id = 100668
Identifier = HIREDATE
Name = Hiredate
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 64
Id = 100669
Identifier = SAL
Name = Sal
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Private Item
Id = -32
Name = Sal SUM
Identifier = 7
Desc =
DataType = 2
Placement = 1
Hidden = 0
IsACalc = 0
IOFormula = [1,1]([6,64])
DisplayFormula = SUM(Sal)
Query Request QR1
Distinct = 1
Axis Item Usage – Name = EUL Item – Emp 1.Empno
Axis Item Usage – Name = EUL Item – Emp 1.Ename
Axis Item Usage – Name = EUL Item – Emp 1.Job
Axis Item Usage – Name = EUL Item – Emp 1.Mgr
Axis Item Usage – Name = EUL Item – Emp 1.Hiredat
e
Axis Item Usage – Name = EUL Item – Emp 1.Deptno
Measure Item Usage – Name = Calculation – Sal SUM
Identifier = 68

///////////////////////////////////////////////////////////////////////////////
Sheet Number 1
///////////////////////////////////////////////////////////////////////////////
Sheet Name = Sheet 1
Sheet Unique Name = Workbook 11503332070
Sheet Identifier = 1
Query(s) used =
Query 1
Items :-
EUL Item – Emp 1.Empno
EUL Item – Emp 1.Ename
EUL Item – Emp 1.Job
EUL Item – Emp 1.Mgr
EUL Item – Emp 1.Hiredate
EUL Item – Emp 1.Deptno
Calculation – Sal SUM

///////////////////////////////////////////////////////////////////////////////

 

How to Find a Particular Scheduled Workbook’s Result Set Table in Discoverer


hello discoverer friends, here a good technical insight within oracle discoverer for scheduled report, what are the tables behind and how they linked. hope you would like n find it useful. do share ur feedback to improve future articles.

 

 

How to Find a Particular Scheduled Workbook’s Result Set Table in Discoverer

1. Create a simple Discoverer workbook based on SCOTT.DEPT table, workbook name is ‘DEPT’
2. Schedule DEPT workbook as ‘SCOTT’ user
3. Wait until the scheduled job has completed; in the Scheduling Manager the report status is ‘Ready’
4. Connect to SQLPlus as the EUL owner 

5. Select BR_ID, BR_WORKBOOK_NAME from EUL5_BATCH_REPORTS where BR_WORKBOOK_NAME=’DEPT’;

 

SQL> select BR_ID, BR_WORKBOOK_NAME from EUL5_BATCH_REPORTS where BR_WORKBOOK_NAME=’DEPT’;

BR_ID      BR_WORKBOOK_NAME
——————————————————————————–
203443      DEPT

6. Select BS_ID, BS_BR_ID, BS_SHEET_NAME from EUL5_BATCH_SHEETS where BS_BR_ID=’203443‘;

SQL> select BS_ID, BS_BR_ID, BS_SHEET_NAME from EUL5_BATCH_SHEETS where BS_BR_ID=’203443′;

BS_ID      BS_BR_ID      BS_SHEET_NAME
——————————————————————————–
203444      203443             Sheet 1

 

7. Select BQ_ID, BQ_BS_ID from EUL5_BATCH_QUERIES where BQ_BS_ID=’203444‘;

select BQ_ID, BQ_BS_ID from EUL5_BATCH_QUERIES where BQ_BS_ID=’203444′;

BQ_ID      BQ_BS_ID
——————– ———
203445      203444

 

8. Select BQT_ID, BQT_BQ_ID, BQT_TABLE_NAME from EUL5_BQ_TABLES where BQT_BQ_ID=’203445‘;

select BQT_ID, BQT_BQ_ID, BQT_TABLE_NAME from EUL5_BQ_TABLES where BQT_BQ_ID=’203445′;

BQT_ID      BQT_BQ_ID      BQT_TABLE_NAME
——————————————————————————————————————
203454         203445                EUL5_B070924102545Q1R1

This shows that the scheduled workbook ‘DEPT: sheet1’ is associated with the Result Set table
‘EUL5_B070924102545Q1R1’

 

SQL> select * from EUL5_B070924102545Q1R1;

BRN1 BRVC1                BRVC2
——————————————-
10        ACCOUNTING  NEW YORK
20        RESEARCH       DALLAS
30        SALES                 CHICAGO
40        OPERATIONS   BOSTON

 

9. Information about when the table was created, the creator, last updated date and who updated it can be obtained from EUL5_BQ_TABLES;

SQL> select BQT_TABLE_NAME, BQT_CREATED_BY, BQT_CREATED_DATE from EUL5_BQ_TABLES;

BQT_TABLE_NAME                 BQT_CREATED_BY      BQT_CREATED_DATE
——————————————————————————————————————
EUL5_B070924102545Q1R1      SCOTT                              24-SEP-07

 

 

 

 Thanks – Shivmohan Purohit —

 

 

How to Create a Link to a Discoverer Workbook in Apps11i


How to Create a Link to a Discoverer Workbook in Apps11i

 To create a link from the Oracle Applications 11i home page to a Discoverer workbook that will automatically open Discoverer Plus or Viewer and display the workbook using apps security.

The basic process is:

1. Create the workbook.

2. Open the workbook in the Discoverer Desktop or Plus edition and go to
‘File->Manage Workbooks->Properties’ look for the value for ‘Identifier’. Save this value.

3. Create a form function. Open the Function form and create a new function.
Define the Form Function.
The form function definition includes the properties listed in these tabs:
3.1 Description tab:
3.1.1 Function Name: BIS_[X] (use something to distinguish it from seeded functions)
3.1.2 User Function Name: This is the name that will show in the menu
3.1.3 Description: Add a description of the function if you want.
3.2 Properties tab:
3.2.1 Type: Select “SSWA plsql function”
3.2.2 Maintenance Mode Support: Leave as “None”
3.2.3 Context Dependence: Leave as “Responsibility”
3.3 Form tab:
3.3.1 Form: Leave the field blank.
3.3.2 Application: Leave the field blank.
3.3.3 Parameters: workbook=<(workbook identifier from step2) &PARAMETERS=sheetid~worksheet id*param_parameter name One~Parameter One Value*param_parameter name Two~Parameter Two Value*

Eg; ‘workbook=TEST_WORKBOOK&PARAMETERS=sheetid~1*’  would open sheet 1 of the workbook.
3.4 Web HTML tab:
3.4.1 HTML call : Set as OracleOasis.RunDiscoverer
3.5 Web Host tab:
3.5.1 Leave all fields blank.
3.6 Region tab:
3.6.1 Leave all fields blank.
3.7 Save the form.

4. Open the menu form as sysadmin.
4.1 Search for the main menu under which you want the link to appear.
4.2 Add the information you need such as prompt, submenu, description etc.
4.3 Enter into the Function field the name of the function you created in step 3.
4.4 Save the menu form.

A message will appear saying that a concurrent program will run to regenerate the menus.
You can cancel it if you want and do step 5 below if the menu does not appear after the concurrent program runs.

5. Use adadmin and recompile the menu information to make the changes appear.
6. Bounce Apache and Forms.

 

 

 

 

References : For 10g, you can reference:  Oracle® Business Intelligence Discoverer Configuration Guide

NoetixViews for Oracle E-Business Suite


NoetixViews for Oracle E-Business Suite


NoetixViews includes an extensive set of reporting objects (metadata) that deliver best-practice business views of the application’s database using an expert blend of table joins, cross operational functionality, customizations and indexes.

Ø      Business views for Oracle Financials, Order Management, Procurement, Projects, Discrete and Process Manufacturing, Human Resources, Grants, Service, Advanced Supply Chain Planning, and Enterprise Asset Management.

Ø      Metadata tailored to your configuration of Business Groups, Sets of Books, Operating Units, and Inventory Organizations.

Ø      Basic views for reports mapped to the Oracle screens; value-added views that integrate data from more complex business processes; and cross-functional views that integrate data from multiple application modules for full business process end-to-end reporting.

Ø      Unique features include special columns for key and descriptive flexfields, and lookups; support for multiple currencies and language translations; and special primary and foreign key columns for joining multiple views together for advanced reporting.

Ø      Built-in security seamlessly integrates with the data access rules of your Oracle responsibilities. Also included is support for module-specific data access controls such as GL flexfield security rules and HR security profiles.

This set of views enhances the following AOL components:

– Audit Trail

– Common Quick Picks

– Concurrent Manager Rules

– Concurrent Program Executables

– Concurrent Programs

– Currencies

– Database Conflicts in Concurrent Processing

– Descriptive Flexfields and Segments

– Flexfield Hierarchies, Values and Security

– Flexfield and Segment Qualifiers

– Key Flexfield Cross-Validation Rules

– Indexes

– Languages

– Printers

– Profile Options

– Report Request Groups

– Requests and Arguments

– Responsibilities

– Security

– Shorthand Flexfield Aliases

 

Thanks – shivmohan purohit

Steps to change the Discoverer Session timeout


Discoverer 10g : here a bit of technical work, though i havn’t tried it actually, but got it from one of good expert of Discoverer , that’s why i sharing it.

How To Change The Session Timeout For Discoverer 10g(10.1.2)?

Steps to change the Discoverer Session timeout:

1. Edit the file $ORACLE_HOME/discoverer/util/pref.txt. 
2. Under the heading [Session Manager] there is a parameter called timeout. 
Timeout = 1800 (Number of Seconds before timeout) 
3. Save the pref.txt file. 
4. Run the applypreferences script. 

When changing the timeout value to be higher than 30 minutes you will need to change the Discoverer servlet timeout as well. The servlet timeout must always be higher or equal to the Discoverer session timeout.Steps to change the Discoverer Servlet timeout:

 

1. Edit the following file: 
$ORACLE_HOME/j2ee/OC4J_BI_Forms/applications/discoverer/discoverer/WEB-INF/web.xml 
2. Add the following entry to the file: 
<session-config> 
<!– Session timeout in minutes –> 
<session-timeout>60 </session-config>  The file should look something like: 
<web-app> 
<servlet> 
<servlet-name>…</servlet-name> 
<servlet-class>…</servlet-class> 
</servlet> 
<session-config> 
<session-timeout>60</session-timeout> 
</session-config> 
</web-app>  3. Save the web.xml file. 
4. Run command ‘dcmctl updateConfig -ct oc4j -v’ to update the configuration. 
5. Restart OC4J with command ‘dcmctl restart -ct oc4j – v'.

  

How to delete scheduled workbooks and results in Discoverer


hello dear friend,

Here is a very good piece on discoverer, how to know the results of schedule reports and insight into it

How to delete scheduled workbooks and results in Discoverer.

 

A scheduled workbook can be setup to run at a particular frequency, e.g. daily, weekly, etc., or it can be set to never repeat. When a workbook is scheduled the following database objects are created:

 

A view is created containing the select statement to create the result tables (e.g. EUL5_B011206161506Q1V1)

 

A table is created for each run with the results (e.g. EUL5_B011206161506Q1R1)

 

A package is created with the run information (e.g. EUL5_BATCH_PACKAGE011206161506 )

 

A job is created to call package at specified schedule intervals (e.g. EUL5_BATCH_PACKAGE011206161506.RUN)

 

All objects have the same unique sequence number in the name.  The R1 tables hold the results of a particular run of a scheduled workbook. A scheduled workbook can have a number of R1 tables, because a new table is created every time the scheduled workbook runs. If deleting the result sets for a scheduled workbook through the Discoverer Plus or Desktop then the R1 tables will be deleted from the database. If deleting the scheduled workbook from the Scheduling Manager then all the database objects will be deleted.

Thanks

Shivmohan Purohit ———— share your feedback