O SpecGold OracleBusIntApps7 clr



   Call us now 

  Manchester Office

  +44 (0) 8450 940 998




Welcome to the Beyond Blog

As you'd expect from the winners of the Specialized Partner of the Year: Business Analytics at the Oracle UKI Specialized Partner Awards 2014, Beyond work with leading edge BI Applications primarily within the UK Public Sector. We intend to share some of our ideas and discoveries via our blog and hopefully enrich the wider discussion surrounding Oracle Business Intelligence and driving improved insight for customers

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Oracle Application Express (APEX) is increasingly being used to produce custom developments within Oracle E-Business Suite. I've posted previously about how to configure your custom applications to use E-Business Suite context for purposes such as foundation API's, MOAC security, profile values and such (using fnd_global.apps_initialize), however I've generally put in a note of warning that such techniques may not work with Human Resources Security. So here I will explain why that is, what you might be able to do to get around that. This assumes the Embedded PL/SQL Gateway (EPG) is being used.

First, let's recap how HR Security is implemented with E-Business Suite, taking People table as an example.
We have the base table per_all_people_f which is not secured (by VPD or otherwise). Built upon this are a set of secure views such as per_people_f which are queries from the base table however with a call to the API hr_security as follows.

per_people_f snippet

The key call in this code is to hr_security.show_person(...) which evaluates the users access to that person record based upon the security profile the user is currently using (the actual method for this depends on whether Cross Business Group security is in use).

Last modified on Continue reading
in Technical 3885 2

The latest Oracle BI EE 12c VM is now available for download.

Last modified on Continue reading
in Technical 2722 0

I recently needed to use the LISTAGG function in a custom Oracle ODI interface. However to my dismay I found that ODI didn't recognise LISTAGG as an aggregate function (i.e. didn't add a GROUP BY clause to the SQL generated).

No Group By

After a bit of searching on Google I found I wasn't the only person to have hit this and found this blog post which explains a method for adding the function to the language in the ODI topology. I tried and failed - no matter what I did I couldn't get it to work.
So instead I tried a little trick I have used in the past for something simlar - to add a commented out aggregate function in the column source.


Last modified on Continue reading
Tagged in: ODI
in Business Intelligence 5893 3

The WITH clause (or subquery factoring clause) is a very useful construct. It allows us to materialize the results of a SQL statement to use multiple times, without having to re-execute that statement. Here is an academic example of that in practice.

With demo As
 (Select * 
   From Employees e
  Where e.job_id = 'IT_PROG')
Select * From demo d, demo d_mgr
 Where d_mgr.employee_id = d.manager_id; 

It's very unlikely that we'd produce the above requirement as-is, however for the purposes of this demo it'll suffice.

Last modified on Continue reading
Tagged in: Optimzer Oracle sql
in Technical 2275 0

One of the advantages of a Cloud BI solution is that it received regular updates and so is always "current" without the consumer having to do anything. 

The recent November 2015 refresh has provided a new, cleaner interface as well as a number of additional features.

Last modified on Continue reading
in Technical 1709 0