Deleting a Schedules Request


How to delete the saved schedules, which were saved from the ‘Schedules’ tab of the ‘Submit Request’ window.
There is no standard functionality to delete any saved schedule.

The saved schedules are being stored in the table fnd_conc_release_classes_vl.
Select release_class_id, application_id, release_class_name schedule_name,
description, class_type schedule_type
from fnd_conc_release_classes_vl
where owner_req_id is null
and enabled_flag != ‘N’
and nvl(start_date_active,sysdate) <= sysdate
and nvl(end_date_active, sysdate) >= sysdate
order by release_class_name

If you want to delete any saved schedule from the LOV, then you can perform any of the following:
Delete the row from the table FND_CONC_RELEASE_CLASSES_VL for the saved schedule.
OR
It will be better to update the ENABLED_FLAG = ‘N’ for the saved schedule instead of deleting the row from the table FND_CONC_RELEASE_CLASSES_VL.

thanks – shivmohan

Advertisements

Sample Example on Viewing Worksheet through Discoverer Plus via URL Link


Sample Example on Viewing Worksheet through Discoverer Plus via URL Link

Sample example on viewing worksheet through discoverer plus via URL link.

Syntax:

http://<Hostname&gt;:<port#>/discoverer/plus?&cn=<Connection_Key>&opendbid=<Workbook_ID>&sheetid=<Worksheet_ID>

http://<Hostname>:<port#>/discoverer/plus?&cn=<Connection_Key>&opendb=<Workbook_Name>&sheet=<Worksheet_Name>

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

Discoverer SQL: Long Running Discoverer Report


Discoverer SQL: Long Running Discoverer Report

Here is a small SQL quite useful to work with discoverer to identify long running reports and start looking into the reason / troubleshooting.

SELECT q.qs_doc_name Worksheet,

 q.qs_doc_details Sheet ,

 q.qs_num_rows Lines,

 QS_ACT_CPU_TIME CPU_Time,

 q.qs_act_elap_time Seconds,

 FLOOR(q.qs_act_elap_time/60) || ‘.’ || MOD(FLOOR(q.qs_act_elap_time), 60) minutes, 

 q.qs_created_date Executed,

 q.QS_DOC_OWNER Owner,

 u.user_name APPS_User,

CASE

WHEN q.qs_state = 0 THEN ‘ERROR :CANCELLED’

WHEN q.qs_state = 1 THEN ‘RUNNING’

WHEN q.qs_state = 2 THEN ‘COMPLETED’

END “CURRENT STATUS”

  FROM discovr_apps.eul5_qpp_stats q,

 fnd_user u

 WHERE TRUNC(qs_created_date) >= TRUNC(SYSDATE)-1

AND TO_NUMBER(SUBSTR(q.qs_created_by,2,8)) = u.user_id

–AND u.user_name LIKE ‘D%’

–AND q.qs_state =1

  –AND FLOOR(q.qs_act_elap_time/60) >= 30 

  ORDER BY q.qs_act_elap_time DESC;

Thanks – Shivmohan Purohit

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