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 E-Business Suite

Taking a break out of the APEX Exploits series for this as it's something I keep coming across at different client sites.

select p.full_name, su.supervisor
  from per_people_x p, per_assignments_x a, per_people_x s
 where a.person_id = p.person_id
   and a.person_id (+) = a.supervisor_id;

With me (or the reader) being told: This query gives you all employees with their supervisors. Ah - but does it? Well, yes it does... sometimes.

See the problem is, many a time a developer will use a view such as per_people_x without knowing exactly what it is doing, and what impact future configuration changes might have. They know the view only returns current records, It runs fine in the development tool (Toad, SQL Developer etc), and when put in an Oracle form it works fine too. All good. However, at some point in the future, after HR have been changing some of their security settings, we suddenly find that people's supervisors aren't being reported as expected. Why? Because the per_person_x view is secured. We have the ability to see the people via our HR security profile, however we might not have access to see the records of their supervisor - that includes something as simple as the name. In reality, our query should probably be:

select p.full_name, su.supervisor
  from per_people_x p, per_assignments_x a, per_all_people_f s
 where a.person_id = p.person_id
   and a.person_id (+) = a.supervisor_id
   and trunc(sysdate) between a.effective_start_date(+) and a.effective_end_date(+);

But then again - should it? Are we interested in who is a person's supervisor now, or who was the supervisor at a particular point in time (i.e. maybe the run date of the Payroll) - hint - the above query shows the former. What about if the person is no longer an employee - they may not get included in the above.

Last modified on Continue reading
Tagged in: E-Business Suite HRMS
in Technical 383 0

A question that comes up all the time on the OTN Forums is "How do I know who updated this record?". Generally the response is to point the member in the direction of the last_updated_by column on the table. The next question often then comes up... So how do I see what the old value was?". Hmmmm... a little more tricky. We can try to look at flashback query however if the update was more than a few hours ago on a busy OLTP system then the chances are the undo has been cleared out. Sometimes we get lucky and the table holds a date-tracked history (HRMS tables for example). But usually it's just tough luck - once the data has been overwritten, it's gone (without resorting to backups).
So if you have some important tables in your E-Business Suite system that you wish to track changes on such as this, then you might want to consider enabling AuditTrail on those tables. It doesn't require any additional licences and is relatively easy to configure. Rather than just narrating what it does, I'll explain with an example.

Assume we have a concern that somebody might update the definition of a form function in EBS and do things we should be tracking; things like altering the parameters to the form. First we need to know what table(s) the data is being maintained in. There are several ways to do this, however often the easiest it to simply do Help > Record History or Help > Diagnostics > Examine > [SYSTEM|LAST_QUERY] and track the view through to the base table(s). So in our case it is fnd_form_functions.

Form Last Query

In order to use AuditTrail the table needs to be registered with the application. For standard tables this is (generally) done - you can check for the existence of a record in FND_TABLES - however for custom tables you'll need to register the table using ad_dd.register_table and ad_dd.register_column, then register the primary key of the table using ad_dd.register_primary_key and ad_dd.register_primary_key_column. Unfortunately there isn't a screen with update capabilities to do this. What I do for custom tables is use a script which reads the data dictionary for a given table and does all this for me. Anyway, once we have the table registered, we need to enable the owner of that table for auditing. In the System Administrator responsibility, navigate to Security > AuditTrail (all our configuration will be done via this menu path) and select the Install option. For our table, fnd_form_functions, the owner is APPLSYS.

SQL> Select owner, table_name
  2  From all_tables
  3  Where table_name='FND_TABLES';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
APPLSYS                        FND_TABLES

In my vision instance this user is already enabled.

Enable User for Auditing

Next we need to create an Audit Group - this is a logical grouping of like audited tables. Navigate to the Groups menu option and we'll create that. Note that we have to set the initial status to Enable Requested. This is very important.

Last modified on Continue reading
Tagged in: E-Business Suite
in Technical 977 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 1923 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 1816 2

I've posted a few things over the previous months regarding new or changed functionality in E-Business Suite R12.2, and here is something that I think will be particularly useful. In previous versions of E-Business Suite it's possible to grant access to full account level access to another user, however as of 12.2 this has been enhanced to responsibility-level access. So you no longer have to give another user full access to your account, you can delegate just the specific responsibilities you wish.

It's probably easiest to show this via a demo. I log in using my user "beyond" and select Manage Proxies from the options menu. You might notice I'm using the Framework Simplified view here too! The reason for which will be apparent later :)

Select Manage Proxies

Last modified on Continue reading
Tagged in: E-Business Suite
in Technical 2121 0