Author Archive

Discoverer Business Views Overview

Jun 20, 2006 DBI, Discoverer, ERP

What are Business Views that Oracle refers to within all the intelligence products like BIS, Discoverer for APPs and Daily Business Intelligence?

  • A Business View is a database view on top of Oracle Apps Tables
  • Logical view encapsulating key columns and extra information from Flex Fields (Key and Descriptive)
  • Easy and Efficient mechanism to reflect any Flex Field changes
  • Pre-built Security encoded within each Business views.
  • Security is defined based on Operating Unit, Inventory Organization and Business Groups
  • Pre-seeded EUL Folders use Business Views as their data source
    Business Views is comparable to Noetix views

Why do we need to Regenerate Business Views?

  • Oracle just provides the base skeleton of the view.
  • This skeleton view usually has a suffix of _FV (FV = Full View)
  • This skeleton view has all the required columns plus instructions on how to extract the Flex Field Columns
  • Sample instruction ‘_DF:PER:PER_GRADES:grd‘
  • Business View Generator Program reads the skeleton view definition and creates a complete _FG view (Full Generated)
  • EUL Folders point to the _FG views

Restrict Discoverer Data access

Jun 13, 2006 Discoverer

Restrict Discoverer Data access by apps user_id, org_id, responsibility etc..

A simple solution is to create a simple sql folder in your EUL with the below query

select fnd_global.resp_id,
fnd_global.resp_name,
fnd_global.user_id,
fnd_global.user_name,
fnd_global.employee_id,
fnd_global.per_security_profile_id,
fnd_global.org_id
from dual;

You can use this folder joined to any other folder to restrict data based on user_id, employee_id or org_id

Alternatively you can incorporate the above query in your view to restrict data directly inside the view and use that view in the discoverer EUL folder.

Convert DFF (Descriptive Flex field) to Number

Jun 10, 2006 ERP

Often data in Oracle Applications is stored in extended columns known as Descriptive Flex field columns. These are character columns which you can map it to store any kind of information. On the front end, it is possible to apply validations like numeric, date or character but the ultimate value that gets stored inside these flex columns is just character strings.

The trouble comes during the time of reporting. Its not a problem if you just want to display the value stored in the column. The pain starts when you want to do some logic on top of those numeric or date values. In this article we just consider numeric values and how can they be utilized.

The problem can be simplified by using the to_number function that Oracle provides but this assumes that the column infact has *ONLY NUMERIC* values and no “white space,” or “non-numeric” characters in it. If it does have non-numeric characters then the to_number function is going to bomb right in your face. Descriptive Flex fields are context dependent ie. each attribute column can store different data types for a given row, so for row 1 the attribute1 column might store a numeric value, row2 might store a date value and so on…

To deal with the above situation, we can create a new generic function as below

function get_number(p_val varchar2) return number is
l_value number;
begin
l_value := to_number(p_val);
return l_value;
exception
when others then
return -1;
end;

In the above wrapper function, it tries to convert the character value into number, if it succeeds then returns the value else returns a -1.

So you can modify this function to return any value depending on the requirement e.g return a zero value instead of -1. The reason we chose -1 is to highlight any data issues. If we return a zero value then there is a possibility that the user might overlook the data error. So depending on the situation return a value which would tell the user that the data is not correct e.g like a huge negative value.

You can enhance the function to strip out non-numeric values like spaces etc using ltrim, rtrim or replace functions but caution should be exercised to not overload this function with more logic and calculations as it will effect the performance of your reports.

Â