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
Subscribe to this list via RSS Blog posts tagged in APEX

A while back I posted instructions on how to create an organization/position chart in Oracle APEX using the Google Charts API. That was a little manual and not massively simple, so I have encapsulated that process into an APEX plugin which is released Apex.World. The GitHub project page can be found here which is the master repository for this plugin.

Please feedback any issues through the issue tracker and feel free to offer any suggestions (or clone the repo and contribute).



Last modified on Continue reading
Tagged in: APEX APEX 5.1
in Technical 154 0

Whilst working on a client project recently I created a page in APEX with a number of different regions, selectable via a Region Selector. The way this page was to be used, the user may not always want to click on every single region each time they use the page. Unfortunately the default behaviour with APEX is that all regions are rendered on the page at load time, so if some queries take a short while to run then your user is waiting for data to return that they aren't even going to use.
What I really wanted was for the code in the region to only actually run when the user chose the region from the selector. I even posted on the OTN Forums to ask if anyone had done similar in the past. Ultimately though it seemed nobody has, so I thought I'd give it a try.

To explain the concept first, this idea works by creating a page item which is checked in a query predicate. i.e.

Select *
  From my_table
 Where :p1_show_data = 'Y';

We take advantage of the fact that the optimizer can deduce that if :p1_show_data does not equal 'Y' then the query is going to return all rows.
By exploiting that, we can devise a solution where our region queries check the value of an item which is empty when originally rendered, however is populated when the region is refreshed - and we trigger a refresh of the corresponding region when the user selects a tab.

Here is how I achieved this (note - demo done in APEX 5.1.2, however should be backwards compatible across at least APEX 5.x).

First we create a hidden page item that restricts the queries. I created one called P1_SHOW_REGION. Then we modify our queries to take advantage of this.

select * from table(delay_table(5))  where :P1_SHOW_REGION is not null

My delay_table is simply a pipelined function that takes a value and waits that number of seconds to return values - it lets me test the report regions by simulating a long-running query. I'll post the code in the comments.
Next I created a before header process which resets the session state for the item - ensuring it is blank when the page loads.

Blank Item

Then I created a computation firing after regions which sets the value to "Y" - so the value is set in the session state ready to be used by our region refresh process.

Set Value

Finally I created the following JavaScript snippet and added this to the Execute When Page Loads section of the page definition.

var regionSelectorShown = new Array("Empty");

$(".apex-rds").data("onRegionChange",function(mode,activeTab) {
  if (typeof regionSelectorShown[activeTab.href] === 'undefined') {

Execute on Page Load

So what is that doing? We are adding a callback on the onRegionChange event, which when triggered adds the name of the region (activeTab.href) to the array regionSelectorShown. This is purely so once we have shown the region within a page, we don't re-execute the query again if the user tabs out and back in again. Then we call the apexrefresh trigger passing in the region name to the jQuery selector - this causes a partial page refresh (PPR) of the region - which now sees the value of :P1_SHOW_DATA as "Y" and thus executes the query in full.
Now we see when we load the page, the region shown first fires the callback and shows initially. Then as we click through other regions, we get the processing icon (whilst waiting for the data to come back via my delay_table function) and the region shows. If we tab out and back in again, we don't re-call the refresh process as the regionSelectorShown array has a value indexed by the region ID indicating we have already shown it and so don't need to again.

Region Loading


Region Loaded

As always, there is always room for improvement and extension of this - if you do so then I'd really appreciate it if you could drop me a line in the comments so others can benefit. It would be nice (and I'd have thought relativly easy) if this kind of functionality was considered for inclusion in the standard APEX build as a feature in future releases.

Last modified on Continue reading
Tagged in: APEX
in Technical 326 1

Oracle's APEX ships with some great plugins such as D3 Collapsible Treemaps.  The most immediate way to see these is to install the Sample Charts application and have a play with it.

Now it is then fairly striaghtforward to utilise this plug-in in your own APEX applications.  Here I simply exported the plugin from the Sample Chart applications and loaded it into my new application.  I then changed the SQL to drive from my own tables and here we are.  What is appealing about this visualisation is that it shows the number of children under each node before you click to expland.  For example, here I can see that 1200 has 5 children underneath it.


I have also used the "Tooltip" funtionality to give me a nice popup description when I "Mouse-over" the node. 



Also each node can be made as a link that can drill off to show the user further detail, so it does really become quite a useful visualisation that can be used quickly and easily.

The Sample applications that ship with APEX ( and I'm currently using the latest 5.1.1 ) come with quite a few plugins that show off the extensibility of the framework and should give you some ideas on how best to use them, so if you are upgrading from older versions of APEX and making the leap to 5.1 then i'd highly recommend installing  selection of the applications and see what's been shipped there.  Of course this is the release that has finally integrated JET charts and we will cover some of their use and examples such as drilling to detail in a later blog.



Last modified on Continue reading
Tagged in: APEX APEX 5.1 D3
in Business Intelligence 603 0

Many clients I have worked for have expressed an interest in a graphical organization chart generated from their E-Business Suite ERP system. Unfortunately the org chart diagrammer in EBS isn't really all that great.

EBS Diagrammer
And the strategic Oracle reporting tools (Business Intelligence etc) are even worse!

It's actually surprisingly difficult to render a tree structure - it can be done purely using lists and CSS, and whilst extremely powerful, it can become difficult to manage for a beginner. So I thought I'd present another option - using Google Charts Organization Chart API (which I suspect is just a wrapper around HTML lists anyway). I will do this for now in Oracle APEX however the principle applies equally to other technologies (I will possibly post follow-ups with OBIEE etc in the future).
So how does the API work? Basically we include the API on our page and then make calls to the various methods provided. The developer page has a simple example that we can use as our starting point. Please take a quick look over there before continuing reading.

Ok, so let's start off. First of all we will create a new APEX desktop application. I am using the very latest APEX build, however this should apply to most recent versions.

APEX Application

Next we need the query which will generate the hierarchy. The nice thing about this is that Google charts does that for you if you provide parent/child tuples, so there is no need to traverse the hierarchy ourselves. The following simple query gives us the current primary organization structure records.

Select parent, child
  From per_organization_structures pos,
       per_org_structure_versions posv,
       per_org_structure_elements pose,
       per_business_groups pbg,
       hr_all_organization_units org_parent,
       hr_all_organization_units org_child
 Where posv.organization_structure_id = pos.organization_structure_id
   And pose.org_structure_version_id = posv.org_structure_version_id
   And pbg.business_group_id = pos.business_group_id
   And org_parent.organization_id = pose.organization_id_parent
   And org_child.organization_id = pose.organization_id_child
   And Trunc(Sysdate) >= posv.date_from 
   And (Trunc(Sysdate) <= posv.date_to Or posv.date_to Is Null)
   And pos.primary_structure_flag = 'Y'
   And = 'Vision Corporation';

Now we'll create a region to display our org chart. The demo on Google goes this by placing the API calls in the Head section of the page, however we can equally do it in the Body section. There are a number of different ways to pushing the data to the API - looping through the rows, as a JSON structure etc - to keep it simple I'm going to use the former and PL/SQL Dynamic Content region. Including the API calls, and calls to Htp.P to output the HTML, we get something along the lines of this as a starter for ten.

Last modified on Continue reading
Tagged in: APEX E-Business Suite
in Technical 1063 2

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 3593 2