HRMS basic SQL in oracle apps

Most Basic HR SQL

A lot of HR tables have date tracking on them as part of the HRMS date tracking functionality... as such, you'll often seen bits in the SQL to do with looking at the effective_start_date and effective_end_dates.
If you search for this:

SELECT   papf.full_name
       , papf.person_id
       , papf.employee_number empno
       , papf.last_update_date last_updated
       , fu.description updated_by
       , papf.business_group_id bg
       , papf.current_employee_flag cur_emp_flag
       , papf.effective_start_date
       , papf.effective_end_date
    FROM hr.per_all_people_f papf
   WHERE papf.full_name = "Smith, Mr Unique"
ORDER BY papf.last_update_date DESC;

You might get a number of different rows. That's because even though only one man called "Unique Smith" worked for your organisation, he might have had a number of changes over the course of his employment.
As these are HR tables, when changes are made, instead of just updating the same record, the existing record is end-dated and a new one is created.
To restrict the SQL to only return the row relevant today's date, change the SQL above to this:

SELECT   papf.full_name        , papf.person_id        , papf.employee_number empno        , papf.last_update_date last_updated        , fu.description updated_by        , papf.business_group_id bg        , papf.current_employee_flag cur_emp_flag        , papf.effective_start_date        , papf.effective_end_date     FROM hr.per_all_people_f papf    WHERE papf.full_name = "Smith, Mr Unique"      AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date ORDER BY papf.last_update_date DESC;

HR Assignments

The concept of date tracking become even more apparent in the assignments table, since over the course of their employment staff can have any number of changes made to the job they do, e.g. as they work their way up through the organisation, they get promoted and are given new jobs.
Some people have more than one current job as well - in which case you might want to include a check in the SQL to only return their primary assignment.
To include assignment information, the above SQL can be developed to include assignment, job and hr-org tables:\

SELECT papf.business_group_id bg      , papf.employee_number empno      , papf.full_name      , paaf.last_update_date assg_updated      , papf.last_update_date papf_updated      , fu.description assg_updated_by      , papf.effective_start_date papf_start      , papf.effective_end_date papf_end      , paaf.assignment_number assg_num      , paaf.effective_start_date paaf_start      , paaf.effective_end_date paaf_end      , papf.current_employee_flag      , haou.name hr_org      , pj.name job_title      , papf.email_address      , paaf.normal_hours     FROM hr.per_all_people_f papf        , hr.per_all_assignments_f paaf        , hr.per_jobs pj        , hr.hr_all_organization_units haou    WHERE papf.person_id = paaf.person_id      AND paaf.job_id = pj.job_id(+)      AND paaf.organization_id = haou.organization_id(+)      AND papf.full_name = "Smith, Mr Unique"      AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date      AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date      AND papf.current_employee_flag = 'Y' -- person is current_employee      AND paaf.assignment_type = 'E'       -- another useful flag to check      AND paaf.primary_flag = 'Y'          -- return only primary assignment      AND papf.full_name LIKE :pn ORDER BY papf.last_update_date DESC;

Seeing information from the past

You can alter your SQL to look at information from the past - for example, to see the job someone did on August 5th 2009:

SELECT papf.business_group_id bg      , papf.employee_number empno      , papf.full_name      , paaf.last_update_date assg_updated      , papf.last_update_date papf_updated      , fu.description assg_updated_by      , papf.effective_start_date papf_start      , papf.effective_end_date papf_end      , paaf.assignment_number assg_num      , paaf.effective_start_date paaf_start      , paaf.effective_end_date paaf_end      , papf.current_employee_flag      , haou.name hr_org      , pj.name job_title      , papf.email_address      , paaf.normal_hours     FROM hr.per_all_people_f papf        , hr.per_all_assignments_f paaf        , hr.per_jobs pj        , hr.hr_all_organization_units haou    WHERE papf.person_id = paaf.person_id      AND paaf.job_id = pj.job_id(+)      AND paaf.organization_id = haou.organization_id(+)      AND papf.full_name = "Smith, Mr Unique"      AND '05-AUG-2009' BETWEEN papf.effective_start_date AND papf.effective_end_date      AND '05-AUG-2009' BETWEEN paaf.effective_start_date AND paaf.effective_end_date      AND papf.current_employee_flag = 'Y' -- person is current_employee      AND paaf.assignment_type = 'E'       -- another useful flag to check      AND paaf.primary_flag = 'Y'          -- return only primary assignment      AND papf.full_name LIKE :pn ORDER BY papf.last_update_date DESC;

Supervisors / location info etc.

As per the intro to this page, the staff listing reports page has some additional examples, showing how you can find out who a person's supervisor is, the location they work in, their approval limit etc. etc.