Database View and Custom Folders does not returns the same results in Discoverer

When running a workbook against a Database View with the same SQL as a Discoverer Custom Folder, the Workbook returns the expected results. If however, the same Workbook is built on top of a Discoverer Custom Folder, which is exactly a copy of the Database View, the value returned is different and it is not correct.

Changes

Here is an example of how to recreate the problem :

1. Create the Database View :
   
   CREATE VIEW DISCO_CALCULATION_VW AS SELECT 1950 AS annual_hours , 52 AS period_divisor , 37.5 AS normal_hours , 10000 AS fte_annual_salary FROM dual
   
   
2. Create a Discoverer Database Folder on this created View ;
   
3. Create a Discoverer Custom Folder with exactly the same code ;
4. On both the folder add the following Calculated Item :
   
   ( Normal Hours/Standard Hours )*Fte Annual Salary
   
   where
   Standard Hours = Annual Hours/Period Divisor
   
   
5. Create a Workbook on both the Folders using Discoverer Desktop. The results for the calculated items will be different.
   

Cause

For the Workbook built on the Discoverer Custom Folder the sql executed is

"SELECT 1950, 10000, 37.5, 52, 1950/52, ( 37.5/1950/52 )*10000 FROM DUAL DUAL"

This means that the order given to the calculation is not working logically as expected and as returned in the database view.

Solution

To make the behavior to be the same on both the folders, the order of the calculation evaluated can be forced by changing the calculation from

"( 37.5/1950/52 )*10000"

to

"( 37.5/(1950/52))*10000"

which in turns began a modification of the Calculation item Standard Hours from :

Annual Hours/Period Divisor

to

(Annual Hours/Period Divisor)

Cumulative Patch Certification with Applications

Numbers of questions coming for information about the certification of Cumulative Patches with Applications installation. First of all, it is important to clarify the types of Discoverer solutions to 'Apps certification' :

1. Custom Solution
   - Custom solution is built by customer and is not dependent on any 'Apps certification' ;
   - Custom solution is simply using the Discoverer native functionality to connect to EBS DB.
   
2. (2) BIS Solution
   - BIS is an Oracle pre-built and shipped with EBS solution.
   - This solution comprises of BAs, folders, workbooks, etc.
   - The 'Apps certification' means that BIS Dev team tests all BAs and workbooks with a new Discoverer version against EBS.
   

In any case customers using Discoverer 10.1.2.2 against EBS 11i are still fully supported and all new problems will be handled normally even reported to Development as bugs if needed. Concerning the Cumulative Patches, you can go for any Cum. Patch version higher than Cum.Patch 3 (6155500) (You may want to skip Cum.Patch 5 6472361, which I see is not fully stable).

AS Patch Set 10.1.2.3

Application Server/Developer Suite Patch Set 10.1.2.3.0 is production on Windows and Solaris.

Patch 5983622
PLACEHOLDER BUG FOR AS/DS 10.1.2.3 PATCHSET RELEASE
Release iAS 10.1.2.3
Product Oracle Application Server Products

This is used patching Admin/Desktop on Windows too.

Note Discoverer 10.1.2.3 is not certified with EBS as well as bug fixes into cp4, cp5 and cp6 are not fixed in 10.1.2.3. CP for 10.1.2.3 will include all the missing bug fixes on top of 10.1.2.3.