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

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

I was inspired to post this blog by a post on the OTN Forums where a user was asking about calculating the consumption of hours against a target - that is, given a target of X hours with N hours per day, how many days would it be until they reached the target? I knew I had done similar things several times in the past, and each time I always seem to forget the actual logic and end up having to look it up again. So hopefully this post will also be also be a little reminder for me if nothing else.
Queries such as this can actually take many guises, but they all share the same underlying principle. A common use is in a warehouse environment where we have stock located in many different locations (because we can of course only fit a finite quantity of an item in a single location/box/storage unit etc) and we get a big order for an item placed. We want to iteratively pick all the items from each locator until need less than is in a locator, then we pick just the amount that is remaining.

Warehouse Diagram

Using the above example, if we had a request for item ABC of quantity 60, we could easily walk from left to right and pick 20 from Locator A, 30 from Locator B, and then the remaining 10 from the 20 in Locator D.
Often though there are requirements for stock usage in warehouses such as FIFO (First In, First Out), which basically translates to Pick the oldest stock first. This is because we don't want stock with a limited shelf life perishing whilst pickers only ever take the stock which is nearest the door!

I've seen a number of different implementations of this within an Oracle environment, some good, some not so good. The latter tend to be approaches which loop around in PL/SQL and count up the quantity from each location. What I want to do here though is demonstrate a pure SQL way of doing this using Analytic Functions. It's not particularly complicated, but it's one of those things that when you come to do it you end up scratching your head for ages trying to remember how to do it!
I'm also a big fan of do it in a single statement where possible/sensible - there's something quite satisfying with solving (seemingly) complicated problems within a single query/command :) .

Last modified on Continue reading
Tagged in: sql
in Technical 1221 2

With every major release of Oracle Applications in recent years there has been a new look and feel for OAF pages. BLAF (11i), Swan (R12.1) and finally Skyros (R12.2). One would imagine that once R12.3 comes out (or even the latter end of R12.2) it will be with the Alta skin - R12.2 already has some nods towards this.
Customer response to the E-Business Suite look and feel certainly is a mixed bag. Many dislike the washed out feel of Swan, some feel BLAF is far too dated and others simply want their ERP system to match their corporate branding.
The Oracle Forms modules are pretty much how they come - there are number of different colour themes you can apply such as red, blue, purple and green, however there isn't really a great deal to change. OAF pages however are somewhat different. They do have a very distinct look and feel. Fortunately, it is possible to customise these to great lengths, however unfortunately it's not all that intuitive. Hopefully this blog will help get you started. I will be using an E-Business Suite 12.2.4 vision instance, simply because it's what I have to hand as a pre-built VM on my laptop. The same applies to all releases though.

First you need to give yourself the Customising Look and Feel Administrator responsibility. It only has one option.

CLAF Responsibility

Once we enter the responsibility we will choose Create Look and Feel

Step 1

Depending on the version of EBS you're using, the options you'll see for the base look and feel will vary. I'd suggest choosing simple-desktop to begin with.
Clicking Next takes us to the page where everything is done. However before I jump into that (we'll come back to it shortly) we will click through and save the look and feel so we can demonstrate the starting point.

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

After being announced at Oracle Openworld 2016 for cloud-only, 12.2 is now finally available as an on-premise release and available from Oracle E-Delivery 

12.2 E-Delivery

We've been using it in the Cloud for a while here at Beyond on our Exadata instance and you may have already seen my previous blog posts on some of the new features such as Analytic Views. More to come soon.

Last modified on Continue reading
in Technical 1531 0

Whilst BICS comes with complete Schema as a Service, some customers choose to use database as a service (DBaaS) for many reasons (such as the ability to use the on-premise modelling tool and "lift and shift" the model).  DBaaS has recently been upgraded to offer 12.2 as an option for the database which is what we were trialling as part of internal R&D whilst we were formulating some recommendations for a BICS customer.

Whilst doing this we encountered a couple of issues with connectivity but fortunately we were helped out by this paper that has just been released by Oracle in February 2017 called "Known Issues for Oracle Business Intelligence Cloud Service".

In addition this paper covers a few other things you will likely want to know about, so if you have embarked on a BICS installation then I highly recommend giving this a quick read.


Last modified on Continue reading
in Installation and Patches 1207 0