Dashboards, Reporting & Business Intelligence

This handy query will assist you in finding if a column name is already being used in any discoverer report/workbook

select * from
(
SELECT
o100588.doc_name workbook_name,
— o100588.doc_developer_key workbook_key,
decode(o100590.ex_to_type, ‘ITE’, ‘Item’, ‘JOI’, ‘Join’, ‘FIL’, ‘Condition’, o100590.ex_to_type) dependency_type,
o100590.ex_to_par_name folder,
(decode(SUBSTR(o100590.ex_to_name, 1, 19), ‘AUTO_GENERATED_NAME’, (decode(o100590.ex_to_type, ‘JOI’, (decode((decode(o100590.ex_to_type, ‘JOI’, EUL_US.EUL5_get_join_expid(o100590.ex_to_id, o100590.ex_to_devkey), to_number(NULL))), NULL, NULL, EUL_US.EUL5_get_join((decode(o100590.ex_to_type, ‘JOI’, EUL_US.EUL5_get_join_expid(o100590.ex_to_id, o100590.ex_to_devkey), to_number(NULL)))))), ‘ITE’, (decode((decode(o100590.ex_to_type, ‘ITE’, o100590.ex_to_id, to_number(NULL))), NULL, NULL, EUL_US.EUL5_get_autogen_item_name((decode(o100590.ex_to_type, ‘ITE’, o100590.ex_to_id, to_number(NULL)))))), ‘FIL’, (decode((decode(o100590.ex_to_type, ‘FIL’, o100590.ex_to_id, to_number(NULL))), NULL, NULL, o100590.ex_to_name)), NULL)), o100590.ex_to_name)) Dependency_name ,
o100590.ex_to_par_devkey folder_key,
(
select sobj_ext_table from EUL_US.EUL5_OBJS
where obj_developer_key=o100590.ex_to_par_devkey
) Table_View
, o100590.ex_to_devkey Dependency_key
FROM EUL_US.EUL5_documents o100588,
EUL_US.EUL5_elem_xrefs o100590
WHERE(((to_number(o100590.ex_from_id)) = o100588.doc_id))
AND(o100588.doc_content_type = ‘application/vnd.oracle-disco.wb’)
and ( o100588.doc_id = 0 or lower(o100588.doc_name) like lower(’%') )
)
where lower(dependency_name) like lower(’%column_name%’)

If your Discoverer reports are not returning data in R12 then here are the steps

R12 uses new Security Policies and for Receivables, it uses Views starting with ARFG
Please follow the following steps :
1. Create a new responsibility with Accounts Payables/ Accounts Receivables as Application. (skip this if you already have custom responsibilities)
2. Set the profile options necessary for AP / AR like MO: Operating Unit ,MO: Security Profile
3. Do the necessary setup required for GL like segment rules and profile options
4. Set the system profile option: ‘Initialization SQL Statement – Custom’ for the responsibility to value
‘Begin GL_SECURITY_PKG.init(); end;’ .
5. Login to Receivables responsibility and you will get discoverer data for AP, AR and GL.

If you happen to install Discoverer seeded apps EUL and use the seeded GL workbooks then there are chances that your workbooks may not show any data. (seeded means that comes pre-installed or with a patch from Oracle)

The cause for this no data in workbook issue is incomplete BIS setups

The “FII: GL BIS Discoverer access” profile option was not granted to the custom responsibility or to the Oracle “Applications Administrator” responsibility (used to administer the Discoverer EUL).

If this option is not set, no data will be returned in standard/seeded Discoverer GL workbooks.

How to fix this

1. Login as Sysadmin and System Administrator responsibility

Navigate to Profile => System

Enter ‘Yes’ in the highlighted field under responsibility

Save the settings and repeat the process for all other responsibilities that would access the GL Business area

Refresh the EUL and check if you are able to see the data. You will need to restart Discoverer Plus. Restart Oracle applications if you are launching Discoverer Plus from within Oracle apps.

This assumes that you have followed through all the installation steps in the document

How to know which Discoverer sessions runs specific workbook, what application user ID and responsibility, what they are running?

Step 1:
======

/* Create funciton in Database base using APPS Schema */
Create or replace function disco_client_info
(workbook_name varchar2) return number is
BEGIN
--Set the client inforamtions
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(fnd_global.user_id||'~'||fnd_global.resp_id||'~'||workbook_name);
return 1;
--
END disco_client_info;

Grant execute on disco_client_info to Public;


/* Grant execute privilege to Public */

Grant execute on disco_client_info to Public;

Step 2
=======
Register this function via the Discoverer Administrator

Step 3
=======
Call this function in Discoverer Desktop in all workbooks (which you want to monitor) as condition like below:

disco_client_info(’WORKBOOK_NAME’)=1

Since the function is always going to return 1 the condition will return true always.

Step 4
=======
If you run the below query you will get the information desired:

select module,client_info from v$session where module like 'Discoverer4';


Note: Module information ‘Discoverer4′ is set by Discoverer for Discoverer 4 version
using DBMS_APPLICATION_INFO.SET_MODULE. Change ‘Discoverer4′ to ‘Discoverer902′ for Discoverer 9.0.2 ,
‘Disco9′ for discoverer 9.0.4 and ‘Disco10′ for discoverer 10.1.X.

The draw back is, you are required to hardcode the workbook name in the condition since you can get workbook information dynamically only in title.

-submitted by Rajan

We just did an official PR on the new version of Discoverer Dashboard. Here is the link
What do users say about this tool?
“Coming into an environment where it’s your responsibility to perform data analysis on thousands of Discoverer reports can be a very daunting task. Trying to identify who owns the reports, how many reports each business unit owns, or more importantly what dependencies are required by each report would take countless hours of analysis. After searching on the web for a product that would aid in my task, I was advised by users of the Oracle Technical Network Forums to try InfoCaptor.

After installing the trial version of the product and reviewing some of its capabilities, I instantly knew my corporation couldn’t do without this tool. Dealing with RudraSoft LLC in answering technical questions or providing extra information vital to my analysis has been very refreshing. It’s very rare in today’s technology market you receive true professional service. If you are in need of a tool that with delivering fast metadata on the Discoverer reports in your environment while saving countless person hours, InfoCaptor is definitely the product of choice”

That’s all for this year, Merry Christmas and Happy Holidays

Agree or not, with all the recent acquisitions, Oracle has ended with multiple apples in the same basket. Just take the ad-hoc category for instance. This category was filled by Oracle Discoverer and then came Siebel Answers and I am not sure how Brio compares with the former two but if it does then it could be the third apple in the basket. Believe it or not multiple products in the same category is often deemed confusing rather than complimenting each other.

So what are the options? Oracle is putting all its force behind Siebel and we could see atleast one release of Discoverer. I think the best thing for Oracle would be to make Discoverer as a freeware tool.

Seeing MySQL as a threat, Oracle made its own version of database as a freeware. They already had Jdeveloper (a complete development environment) as freeware. So the only component that is missing as freeware is the BI and reporting piece.

Having a free Database + free Development tool + free reporting = more power to Oracle!

The more I think of this it becomes more compelling to release Oracle Discoverer as a freeware. If in future, Oracle doesn’t like to maintain or keep developing Discoverer then there is an alternative to make it Open Source. This could counter some of the threat from the open source BI companies like Pentaho and JasperSoft.

What do you think? Does it make sense to release Discoverer as a freeware tool?

As part of the testing process for the upcoming EUL documentation feature in InfoCaptor, one of the user’s (Lance Botha – an active member of Discoverer forum ) was having trouble using the new discoverer dashboard. He was running against the EUL4 version whereas I had the opportunity to test it on only eul5 version. With some quick testing I came up with a list of functions that were newly added in EUL5 version and are missing in eul4. Below is a list of all the cloned functions for EUL4. The below functions should be created as the eul owner and should be executed after the eul4.sql and eul4_apps.sql scripts have been run. I will post detailed steps once the new release is out.

create or replace FUNCTION EUL4_GET_HIER_EXPID(HINAME IN VARCHAR2)
RETURN NUMBER IS
EXPID NUMBER(10);
--
--
HIID NUMBER(10);
HILHNID NUMBER(10);
--
--
--
CURSOR GET_HIID IS
SELECT HI_ID
FROM EUL4_HIERARCHIES
WHERE HI_NAME=HINAME;
--
--
CURSOR GET_HILHNID IS
SELECT IHS_HN_ID_CHILD
FROM EUL4_HI_SEGMENTS
WHERE IHS_HI_ID=HIID
AND IHS_HN_ID_CHILD NOT IN
(SELECT IHS_HN_ID_PARENT
FROM EUL4_HI_SEGMENTS
WHERE IHS_HI_ID=HIID);
--
--
CURSOR GET_EXPID IS
SELECT HIL_EXP_ID
FROM EUL4_IG_EXP_LINKS
WHERE HIL_HN_ID=HILHNID;
--
--
BEGIN
--
IF HINAME IS NULL THEN
EXPID:=NULL;
ELSE
--
OPEN GET_HIID;
FETCH GET_HIID INTO HIID;
IF GET_HIID%NOTFOUND THEN
EXPID:=-1;
RETURN EXPID;
END IF;
CLOSE GET_HIID;
--
OPEN GET_HILHNID;
FETCH GET_HILHNID INTO HILHNID;
IF GET_HILHNID%NOTFOUND THEN
EXPID:=-2;
RETURN EXPID;
END IF;
CLOSE GET_HILHNID;
--
OPEN GET_EXPID;
FETCH GET_EXPID INTO EXPID;
IF GET_EXPID%NOTFOUND THEN
EXPID:=-3;
END IF;
CLOSE GET_EXPID;
--
END IF;
RETURN EXPID;
END EUL4_GET_HIER_EXPID;
/

create or replace FUNCTION EUL4_GET_DATEHIER_TMPLT_NAME(IBHDBHID IN NUMBER)
RETURN VARCHAR2 IS
DATE_HIER_TMPLTE_NAME VARCHAR2(200);
--
--
--
--
--
CURSOR GET_DATEHIER_NAME IS
SELECT HI_NAME
FROM EUL4_HIERARCHIES
WHERE HI_ID=IBHDBHID
AND HI_TYPE='DBH';
--
--
--
--
BEGIN
--
--
IF IBHDBHID IS NULL THEN
DATE_HIER_TMPLTE_NAME:='ERROR - NOT ASSOCIATED TO A DATE HIERARCHY TEMPLATE';
ELSE
--
OPEN GET_DATEHIER_NAME;
FETCH GET_DATEHIER_NAME INTO DATE_HIER_TMPLTE_NAME;
IF GET_DATEHIER_NAME%NOTFOUND THEN
DATE_HIER_TMPLTE_NAME:='Error - Unable to find Date Hierarchy Template Name';
END IF;
CLOSE GET_DATEHIER_NAME;
END IF;
RETURN DATE_HIER_TMPLTE_NAME;
END EUL4_GET_DATEHIER_TMPLT_NAME;
/

create or replace FUNCTION EUL4_GET_FOLDERNAME(EXPID IN NUMBER)
RETURN VARCHAR2 IS
FLRNAME VARCHAR2(200);
--
--
--
--
--
CURSOR GET_FLDRNAM IS
SELECT OBJ_NAME
FROM EUL4_OBJS,EUL4_EXPRESSIONS
WHERE EUL4_OBJS.OBJ_ID=EUL4_EXPRESSIONS.IT_OBJ_ID
AND EUL4_EXPRESSIONS.EXP_ID=EXPID;
--
--
--
--
BEGIN
--
IF EXPID IS NULL THEN
FLRNAME:=NULL;
ELSE
OPEN GET_FLDRNAM;
FETCH GET_FLDRNAM INTO FLRNAME;
IF GET_FLDRNAM%NOTFOUND THEN
FLRNAME:='Error - Unable to find Folder Name';
END IF;
CLOSE GET_FLDRNAM;
END IF;
RETURN FLRNAME;
END EUL4_GET_FOLDERNAME;
/

create or replace FUNCTION EUL4_GET_HIERNODE_EXPID(HNID IN NUMBER)
RETURN NUMBER IS
EXPID NUMBER(10);
--
--
--
--
--
CURSOR GET_EXP_ID IS
SELECT HIL_EXP_ID
FROM EUL4_IG_EXP_LINKS
WHERE HIL_HN_ID=HNID
AND HIL_HN_ID IS NOT NULL;
--
--
--
--
BEGIN
--
OPEN GET_EXP_ID;
FETCH GET_EXP_ID INTO EXPID;
IF GET_EXP_ID%NOTFOUND THEN
EXPID:=NULL;
END IF;
CLOSE GET_EXP_ID;
RETURN EXPID;
END EUL4_GET_HIERNODE_EXPID;
/

create or replace FUNCTION EUL4_GET_AUTOGEN_ITEM_NAME(EXPID IN NUMBER)
RETURN VARCHAR2 IS
DEP_ITEM VARCHAR2(4000);
--
--
CIDEXP        NUMBER;
EXPNAME        VARCHAR2(200);
--
--
-- This cursor finds out if the item is a complex item (ie. dependant on another item)
-- It retuns not rows if it has no dependancies (ie. simple folder)
--
CURSOR EXPOBJ IS
SELECT CID_EXP_ID,EXP_NAME
FROM EUL4_EXP_DEPS, EUL4_EXPRESSIONS
WHERE EUL4_EXP_DEPS.CD_EXP_ID = EXPID
AND CID_EXP_ID IS NOT NULL
AND EUL4_EXP_DEPS.CD_EXP_ID = EUL4_EXPRESSIONS.EXP_ID;
--
--
--
CURSOR GET_EXP_NAME IS
SELECT EXP_NAME
FROM EUL4_EXPRESSIONS
WHERE EXP_ID=EXPID;
--
--
--
--
BEGIN
--
IF EXPID IS NULL THEN
DEP_ITEM:=NULL;
ELSE
--
OPEN EXPOBJ;
FETCH EXPOBJ INTO CIDEXP,EXPNAME;
IF EXPOBJ%NOTFOUND THEN
OPEN GET_EXP_NAME;
FETCH GET_EXP_NAME INTO EXPNAME;
CLOSE GET_EXP_NAME;
END IF;
CLOSE EXPOBJ;
IF SUBSTR(EXPNAME,1,19) <> 'AUTO_GENERATED_NAME' THEN
DEP_ITEM:=EXPNAME;
RETURN DEP_ITEM;
ELSE
DEP_ITEM:=EUL4_GET_AUTOGEN_ITEM_NAME(CIDEXP);
END IF;
END IF;
RETURN DEP_ITEM;
END EUL4_GET_AUTOGEN_ITEM_NAME;
/

create or replace FUNCTION EUL4_GET_JOIN(EXPID IN NUMBER,JOINAME IN VARCHAR2:='Y')
RETURN VARCHAR2 IS
JOIN VARCHAR2(4000);
--
-- If JOINAME is 'Y' then this returns the Autogenerated Join Name if it is 'N' or some other value it returns the the actual Join Details
--
--
JOIN_FORMULA    VARCHAR2(500);
JOIN_BIT    VARCHAR2(100);
JOIN_TYPE    VARCHAR2(20);
STARTPT     NUMBER :=1;
ENDPT        NUMBER :=1;
FINISHPT    NUMBER:=1;
EXPIDMSTR    NUMBER(10);
EXPIDDTL    NUMBER(10);
MASTERFLDR    VARCHAR2(4000);
DETAILFLDR    VARCHAR2(4000);
OPERATOR    VARCHAR2(10);
JOINMSTR    VARCHAR2(4000);
JOINDTL        VARCHAR2(4000);
CNTR        NUMBER :=0;
--
--
--
CURSOR GET_FORMULA IS
SELECT EXP_FORMULA1
FROM EUL4_EXPRESSIONS
WHERE EXP_TYPE='JP'
AND EXP_ID=EXPID;
--
--
--
--
BEGIN
--
IF EXPID IS NULL THEN
JOIN:='Name is just Too long to display';
ELSE
OPEN GET_FORMULA;
FETCH GET_FORMULA INTO JOIN_FORMULA;
IF GET_FORMULA%NOTFOUND THEN
JOIN:='Error - Unable to find Join';
END IF;
JOIN_BIT:=SUBSTR(JOIN_FORMULA,3,4);
IF JOIN_BIT=',98]' THEN
JOIN_TYPE:='MULTI';
ELSE
JOIN_TYPE:='SINGLE';
END IF;
IF JOIN_TYPE='MULTI' THEN
FINISHPT:=LENGTH(JOIN_FORMULA);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,3)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,4)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
MASTERFLDR:=EUL4_GET_FOLDERNAME(EXPIDMSTR);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,5)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,6)-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
DETAILFLDR:=EUL4_GET_FOLDERNAME(EXPIDDTL);
IF upper(JOINAME)='Y' THEN
JOINMSTR:=MASTERFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
CNTR:=11;
WHILE INSTR(JOIN_FORMULA,CHR(44),1,CNTR)<>0 LOOP
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-3)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:= JOINMSTR||','||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:= JOINDTL||','||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
CNTR:=CNTR+5;
END LOOP;
CNTR:=CNTR-1;
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)+1;
ENDPT:=FINISHPT-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:= JOINMSTR||','||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR)||' -> ';
JOINDTL:= JOINDTL||','||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
JOIN:=JOINMSTR||JOINDTL;
ELSE
JOINMSTR:=MASTERFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
IF SUBSTR(JOIN_FORMULA,11,2) = '81' THEN
OPERATOR:= ' = ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '82' THEN
OPERATOR:= ' <> ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '83' THEN
OPERATOR:= ' > ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '84' THEN
OPERATOR:= ' < ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '86' THEN
OPERATOR:= ' >= ';
ELSE
OPERATOR:= ' ? ';
END IF;
JOIN:=JOINMSTR||OPERATOR||JOINDTL||CHR(10);
CNTR:=11;
WHILE INSTR(JOIN_FORMULA,CHR(44),1,CNTR)<>0 LOOP
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-3)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:= MASTERFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:= DETAILFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-4)+1;
IF SUBSTR(JOIN_FORMULA,STARTPT,2) = '81' THEN
OPERATOR:= ' = ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '82' THEN
OPERATOR:= ' <> ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '83' THEN
OPERATOR:= ' > ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '84' THEN
OPERATOR:= ' < ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '86' THEN
OPERATOR:= ' >= ';
ELSE
OPERATOR:= ' ? ';
END IF;
CNTR:=CNTR+5;
JOIN:=JOIN||JOINMSTR||OPERATOR||JOINDTL||CHR(10);
END LOOP;
CNTR:=CNTR-1;
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)+1;
ENDPT:=FINISHPT-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:=MASTERFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-3)+1;
IF SUBSTR(JOIN_FORMULA,STARTPT,2) = '81' THEN
OPERATOR:= ' = ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '82' THEN
OPERATOR:= ' <> ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '83' THEN
OPERATOR:= ' > ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '84' THEN
OPERATOR:= ' < ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '86' THEN
OPERATOR:= ' >= ';
ELSE
OPERATOR:= ' ? ';
END IF;
JOIN:=JOIN||JOINMSTR||OPERATOR||JOINDTL;
END IF;
ELSE
FINISHPT:=LENGTH(JOIN_FORMULA);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,2)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,3)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
MASTERFLDR:=EUL4_GET_FOLDERNAME(EXPIDMSTR);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,4)+1;
ENDPT:=FINISHPT-1;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
DETAILFLDR:=EUL4_GET_FOLDERNAME(EXPIDDTL);
IF upper(JOINAME)='Y' THEN
JOINMSTR:=MASTERFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR)||' -> ';
JOINDTL:=DETAILFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
JOIN:=JOINMSTR||JOINDTL;
ELSE
IF SUBSTR(JOIN_FORMULA,4,2) = '81' THEN
OPERATOR:= ' = ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '82' THEN
OPERATOR:= ' <> ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '83' THEN
OPERATOR:= ' > ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '84' THEN
OPERATOR:= ' < ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '86' THEN
OPERATOR:= ' >= ';
ELSE
OPERATOR:= ' ? ';
END IF;
JOINMSTR:=MASTERFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||'.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
JOIN:=JOINMSTR||OPERATOR||JOINDTL;
END IF;
END IF;
CLOSE GET_FORMULA;
END IF;
RETURN JOIN;
END EUL4_GET_JOIN;
/

Recently, my current client asked me if there was any easy way to document all the EUL information in one file. I thought of recommending the standard EUL workbooks to extract the necessary information but since the client asked for an easy way I did not suggest that. It is certainly one way of documenting it but not necessarily the best way.

The problem was, there was no tool or method available in order to create the document in the best way.

So with almost 20 hrs of coding during the weekend, I whipped up some java code, using the standard queries and came up with a robust mechanism to create the document in just “One Click”.

Check this Sample Discoverer EUL and Workbook Generated Document,

It took less than 1 minute to generate the entire document into a pdf file with just “One Click”. Isn’t that easier than the
standard EUL workbooks?

The beauty of this generator is, you can add queries and create child nodes in the PDF document. You have solid formatting options. This can be run against any database containing the EUL information but before I present this to my client I would like your opinion on whether the document is presentable, does it have all the necessary information, any flaws, any suggestions?

You can email me at infocaptor”AT’gmail.com

Thank you

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.

Have a nice day!