I’m goofing off in Discoverer.  Yes, it’s an older tool, but it is still in use.  Maybe an article on that later. What was I saying??  Oh yes, Discoverer.  For the longest time, we have been creating Business Areas and adding folders in an effort to “empower” our users.  We have situations where users need SOME of the columns in a table, but not ALL of them.  Here is where the complex folders come into play.  Yes I know they discuss this in the training and all the books, but for some reason, it just didn’t click until I was trying to do something completely different.  Then the “Aha” moment.  So lets get to it.

This scenario will be based off of an example from EBS 11.5.10.2
Scenario:
Report request -> user requests a report that will display the end date of an employee FND User record along with some person and assignment information.

Solution:
Put the necessary tables into a business area, join them and let the users have at it.

Anyone familiar with EBS knows that this requires at least these three tables;
FND_USER
PER_ALL_PEOPLE_F (or a derivative)
PER_ALL_ASSIGMENTS_F (or a derivative)

 Problem: Your Information Security Officer says that you cannot expose the entire contents of the FND_USER table to the the requester.

*NOTE: I could create and register functions to get this data, but this would require migrating objects into the production environment and that was not an option.

Choice 1 is to write some custom SQL and register that in discoverer. My issues with having sex began at the age of 54. I thought it was too early to give up intimate relations. Erection reached with Levitra 20 mg lasts longer and its strength is the same as with other erectile dysfunction pills.

Choice 2 is to use Complex Folders in Discoverer

So why not Choice 1:
As soon as that was done, the user(s) would want some other column requiring an edit to the SQL and re-registering, and this defeats the purpose of Discoverer by eliminating some of the flexibility provided to the end user.

On to Choice 2:
If you don’t know, the Complex Folder functionality in Discoverer allows the Discoverer Administrator to combine columns from multiple tables and present them in the Discoverer Plus interface as a single folder.  Kinda like building a view with the Administrator Tool.  As we know, Views allow us to present only the data we want to the users.  In this case that’s exactly what we need.  With the proper join conditions, we will be able to give user(s) access the columns in the PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F as well as the effective_end_date column in the FND_USER table.

Tomorrow I will show you how.