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 HRMS

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 668 0

It's always nice to see new database features, and I especially like to see features which implement concepts that are commonly coded at the application level. I've posted a number of blogs recently on the new Analytic Views functionality in 12.2, however today I'm going to look at a feature introduced a little earlier in 12c (12.1) - Temporal Validity.

It's probably best explained with an example, so let's take a look at some sample (simplified) date-tracked data such as employees in a Human Resources system.

1 Alice Jones Finance Active Employee 01-Jan-2016 17-Aug-2016
1 Alice Smith Finance Active Employee 18-Aug-2016 31-Oct-2016
1 Alice Smith Human Resources Active Employee 01-Nov-2016 18-Nov-2016
1 Alice Smith Human Resources Inactive Employee 19-Nov-2016 31-Dec-4712

For anyone not familiar with what the above is representing, we see that up until 17-Aug-2016 we have an employee named Alice Jones working in the Finance department. From 18-Aug-2016 she has changed her name to Alice Smith, still working in the Finance department. Then from 01-Nov-2016 she begins working in Human Resources up until 18-Nov-2016 when she left the company. This representation of data allows us to query historic data and see what the status of an employee was at any given point in time. First let's create a sample data model to represent that.

Last modified on Continue reading
Tagged in: HRMS sql tips
in Technical 1771 0

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