Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.

  • 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 HRMS - Which View Should I Use?

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

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.

 

What I am leading up to here is that it is important to use the appropriate/correct view for the purpose, with the business requirements defining that.
The principles I'm going to explain here apply to a number of different objects in the same manner - people, assignments, positions etc. However I'm going to use people for the purposes of this demonstration.
Let's look at the objects available for querying people (from an R12.2. environment).

select owner, object_name, object_type
from all_objects
where object_name like 'PER%PEOPLE%'
and object_type in ('VIEW','TABLE')
order by owner, object_name, object_type

OWNER OBJECT_NAME                    OBJECT_TYPE        
----- ------------------------------ -------------------
APPS  PER_ALL_PEOPLE                 VIEW               
APPS  PER_ALL_PEOPLE_D               VIEW               
APPS  PER_PEOPLE                     VIEW                             
APPS  PER_PEOPLE_F                   VIEW                             
APPS  PER_PEOPLE_V                   VIEW               
APPS  PER_PEOPLE_V2                  VIEW               
APPS  PER_PEOPLE_V3                  VIEW               
APPS  PER_PEOPLE_V4                  VIEW               
APPS  PER_PEOPLE_V7                  VIEW               
APPS  PER_PEOPLE_X                   VIEW               
HR    PER_ALL_PEOPLE_F               TABLE                    

We're not interested in some - so I've trimmed them from the ouput, but there are a few variations we can see in terms of usages of the word _ALL_ and the suffix (_X, _D, No Suffix etc). So here is a brief summary of what does what. In general the suffix denotes the Date Tracking mode (although it can be an indication that a view contains translated values) and the usage of _ALL_ (or absence of) denotes security.

  • View/Table contains _ALL_ : The data is not secured. I.e. Per_all_people_f.
  • View doesn't contain _ALL_ : The data is secured according to the HR Security Profile of the users responsibility.
  • View/Table has a _F suffix : The data is not date-tracked. It will return all visible records. I.e. per_all_people_f.
  • View/Table has a _D suffix : The data is not date-tracked. The view generally contains translated values, which are descriptive names based on the users language such as the Nationality description. I.e. per_all_people_d.
  • View has a _X suffix : The data is date-tracked to sysdate. I.e. per_people_x.
  • View has no suffix : The data is date-tracked to the session date. That is - the effective_date held against the record in the table fnd_sessions for the current session_id. i.e. per_people.
  • View has a _V{Number} suffix : The implementation of these varies significantly. Some are session date tracked, some are date-tracked to the latest record, others not at all. They usually contain lookups to language translated values and are generally used in Oracle Forms. I.e. per_people_v7

As you can see there are not objects for all possible combinations (i.e. per_all_people_x doesn't exist), however given the above set of rules it is usually easy to see which view to use. I.e. if you want a secured session date effective query, one would use the per_people view. However if you wanted an unsecured session date tracked query, we would use per_all_people instead. Note in our example above where we wanted a Sysdate tracked non-secured view. There is no per_all_people_x view - and it would be wrong of us to use per_people_x as this would potentially be implicated by security changes in the future - so we must use an unsecured object (either the per_all_people_f base table or the per_all_people_d unsecured view) and date-track it manually.

This kind of thing is especially important in LOV queries. I recently had to use a form which used per_people_x to select the list of employees for an Email To field for a system alert. As I wasn't the supervisor of the System Administrator, I wasn't able to select him in the form. The LOV should have used an unsecured object instead, however when it was written it was done so for a non-HR responsibility - and unfortunately the default functionality in HR is that the views return all data unless a security profile is applied - so at time of creation it seemed to work correctly.
Any developer who is using HR data needs to be making these considerations - not just looking at the current configuration, but at what would be the effect if that was to change. In reality, that should be in the business specification anyway. A final word of warning - please don't just date-track everything to sysdate as I see a lot of people doing :) . Understand the data - how it changes over time - and where you can and cannot expect to find a record under different circumstances.

Back to the APEX Security Exploits next time - keep an eye out.

Also, as a footnote, I'm pleased to announce that I've been invited to become an Oracle ACE ♠ - which means I will have to keep up the contributions and think of interesting things to write :) . You can find my profile here.

Last modified on
Tagged in: E-Business Suite HRMS
in Technical Hits: 255 0 Comments
0

My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.











  ACE Logo




Cert Logo  
Cert Logo 2

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest
Guest Saturday, 16 December 2017