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.