HR Locations

-- ##############################################################################
--        LOCATIONS BASIC DETAILS
-- ##############################################################################

      SELECT bus_gp.NAME bus_gp
           , inv_org.NAME inv_org
           , hla.location_code
           , hla.description
           , hla.inactive_date
           , hla.address_line_1
           , hla.address_line_2
           , hla.address_line_3
           , hla.town_or_city
           , hla.postal_code
        FROM hr.hr_locations_all hla
   LEFT JOIN hr.hr_all_organization_units_tl bus_gp ON hla.business_group_id = bus_gp.organization_id
   LEFT JOIN hr.hr_all_organization_units_tl inv_org ON hla.inventory_organization_id = inv_org.organization_id
        JOIN applsys.fnd_user fu ON hla.created_by = fu.user_id;

-- ##############################################################################
--      LOCATIONS - HR STAFF ASSIGNMENT LINKS
-- ##############################################################################

-- count

      SELECT hla.location_code
           , hla.description description
           , COUNT(*) assignment_count
        FROM hr.hr_locations_all hla
        JOIN hr.per_all_assignments_f paaf ON hla.location_id = paaf.location_id
        JOIN hr.per_all_people_f papf ON paaf.person_id = papf.person_id AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date) AND (SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       WHERE paaf.primary_flag = 'Y'
         AND paaf.assignment_type = 'E'
    GROUP BY hla.description
           , hla.location_code
    ORDER BY 3 DESC;

-- details

      SELECT papf.employee_number
           , papf.full_name
           , paaf.assignment_number
           , hla.location_code
           , hla.description description
        FROM hr.hr_locations_all hla
        JOIN hr.per_all_assignments_f paaf ON hla.location_id = paaf.location_id
        JOIN hr.per_all_people_f papf ON paaf.person_id = papf.person_id AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date) AND (SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       WHERE paaf.primary_flag = 'Y'
         AND paaf.assignment_type = 'E'
    ORDER BY 3 DESC;

-- ##############################################################################
--        LOCATIONS - HR ORG LINKS
-- ##############################################################################

-- count

      SELECT hla.location_code
           , hla.description description
           , COUNT(*) ct
        FROM hr.hr_locations_all hla
        JOIN hr.hr_all_organization_units haou ON haou.location_id = hla.location_id
    GROUP BY hla.description
           , hla.location_code
    ORDER BY 3 DESC;

-- details

      SELECT haou.name hr_org
           , hla.location_code
           , hla.description description
        FROM hr.hr_locations_all hla
        JOIN hr.hr_all_organization_units haou ON haou.location_id = hla.location_id;

Operating Units

The following SQL won’t work, unless you either run this first: ALTER SESSION SET nls_language = 'AMERICAN';, or have your NLS Language setting set to American – e.g. in SQL Developer Settings:

SQL Developer NLS Settings

-- ##############################################################################
--      OPERATING UNITS
-- ##############################################################################

SELECT * FROM apps.hr_operating_units;

Business Groups

-- ##############################################################################
--      BUSINESS GROUPS
-- ##############################################################################

    SELECT hrl.country
         , hroutl_bg.name bg
         , hroutl_bg.organization_id
         , lep.legal_entity_id
         , lep.name legal_entity
         , hroutl_ou.name ou_name
         , hroutl_ou.organization_id org_id
         , hrl.location_id
         , hrl.location_code
         , hrl.description
         , glev.flex_segment_value
      FROM xle.xle_entity_profiles lep
      JOIN xle.xle_registrations reg ON lep.legal_entity_id = reg.source_id
      JOIN hr.hr_locations_all hrl ON hrl.location_id = reg.location_id
      JOIN ar.hz_parties hzp ON lep.party_id = hzp.party_id
      JOIN apps.hr_operating_units hro ON lep.legal_entity_id = hro.default_legal_context_id
      JOIN hr.hr_all_organization_units_tl hroutl_bg ON hroutl_bg.organization_id = hro.business_group_id
      JOIN hr.hr_all_organization_units_tl hroutl_ou ON hroutl_ou.organization_id = hro.organization_id
 LEFT JOIN gl.gl_legal_entities_bsvs glev ON glev.legal_entity_id = lep.legal_entity_id
     WHERE lep.transacting_entity_flag = 'Y'
       AND reg.source_table = 'XLE_ENTITY_PROFILES';

HR Records – Supervisor Hierarchy

These are some SQLs I used to use in a job where we used HR, and used the standard supervisor hierarchy.

Sections:

  1. HR Supervisor Hierarchy
  2. Staff List with Supervisor Loop

HR Supervisor Hierarchy

If you use the HR Supervisor Hierarchy, there are lots of different SQLs you can use to extract information to do with that kind of thing.

The key thing to note in the SQL is that this bit of SQL:

CONNECT BY PRIOR person_id = supervisor_id
    START WITH employee_number = :empno;

Means that when you run the code, you need to enter the employee number of the most senior person, and the SQL will loop through the records and show the chain going down to the most junior.

If you change the “…CONNECT BY…” to this:

CONNECT BY PRIOR supervisor_id = person_id

You can enter the employee number of the most junior person, and it’ll loop through with them at the top and go down to the most senior at the bottom.

Staff List with Supervisor Loop

Enter a junior’s employee number, to loop through all the way through to the big cheese boss person at the top of the greasy pole!

/*##############################################################################
#   STAFF LIST WITH SUPERVISOR CHECKING
#   http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=599866
/*############################################################################*/

        SELECT e.bus_gp
             , e.full_name
             , e.employee_number empno
             , LEVEL
             , SYS_CONNECT_BY_PATH(e.last_name, '/') PATH
             , e.limit_req_min
             , e.limit_req_max
             , e.limit_po_min
             , e.limit_po_max
             , e.limit_cpa_min
             , e.limit_cpa_max
             , e.job
             , e.user_name
             , e.end_date
             , e.email_address
             , e.user_location
             , e.hr_org
             , e.default_charge_account
             , e.personid
          FROM (SELECT ppf.person_id
                     , ppf.employee_number
                     , ppf.full_name
                     , ppf.last_name
                     , ppf.person_id personid
                     , hlat.location_code user_location
                     , haou.NAME hr_org
                     , paf.supervisor_id
                     , pj.NAME job
                     , ppf.email_address
                     , fu.user_name
                     , fu.end_date
                     , pg.NAME grade
                     , bus_gp.NAME bus_gp
                     , gcc.concatenated_segments default_charge_account
                     , (SELECT min(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'REQUISITION') limit_req_min
                     , (SELECT max(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'REQUISITION') limit_req_max
                     , (SELECT min(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PO') limit_po_min
                     , (SELECT max(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PO') limit_po_max
                     , (SELECT min(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PA') limit_cpa_min
                     , (SELECT max(pcr.amount_limit) FROM po.po_position_controls_all ppca , po.po_control_rules pcr, po.po_control_functions pcf WHERE pcr.control_group_id = ppca.control_group_id AND pcf.control_function_id = ppca.control_function_id AND ppca.job_id = pj.job_id AND ppca.end_date IS NULL AND pcr.object_code = 'DOCUMENT_TOTAL' AND pcf.document_type_code = 'PA') limit_cpa_max
                  FROM hr.per_all_people_f ppf
                  JOIN hr.per_all_assignments_f paf             ON ppf.person_id =                      paf.person_id
             LEFT JOIN hr.per_jobs pj                           ON paf.job_id =                         pj.job_id
             LEFT JOIN hr.per_grades pg                         ON paf.grade_id =                       pg.grade_id
             LEFT JOIN hr.hr_all_organization_units haou        ON paf.organization_id =                haou.organization_id
             LEFT JOIN hr.hr_locations_all_tl hlat              ON paf.location_id =                    hlat.location_id
             LEFT JOIN applsys.fnd_user fu                      ON paf.person_id =                      fu.employee_id
             LEFT JOIN hr.hr_all_organization_units_tl bus_gp   ON paf.business_group_id =              bus_gp.organization_id
             LEFT JOIN apps.gl_code_combinations_kfv gcc        ON paf.default_code_comb_id =           gcc.code_combination_id
                 WHERE 1 = 1
                   AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
                   AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
                   AND paf.primary_flag = 'Y'
                   AND paf.assignment_type = 'E'
                   AND 1 = 1) e
    CONNECT BY PRIOR person_id = supervisor_id
    START WITH employee_number = 123456;

Here are some different versions, which are more cut down. Start with the employee number of the most senior person.

-- ##############################################################################
--   HR Manager Looping SQL
-- ############################################################################*/
    SELECT     LPAD(' ', (LEVEL - 1) * 10, ' ') || h.person_id person_id
             , LEVEL
             , f.full_name
             , pg.name gd
             , f.employee_number empno
             , u.user_name user_name
             , u.description
             , pj.NAME job_title
             , haou.name hr_org
          FROM hr.per_all_people_f f
          JOIN hr.per_all_assignments_f h           ON f.person_id = h.person_id
     LEFT JOIN hr.hr_all_organization_units haou    ON h.organization_id = haou.organization_id
     LEFT JOIN hr.per_jobs pj                       ON h.job_id = pj.job_id
     LEFT JOIN hr.per_grades pg                     ON h.grade_id = pg.grade_id
     LEFT JOIN applsys.fnd_user u                   ON h.person_id = u.employee_id
    START WITH SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND f.employee_number = :employee_number
    CONNECT BY PRIOR h.person_id = supervisor_id
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date;

    -- trimmed version HR tables only
    -- start with employee number of person at the top of the chain (e.g. most senior person)
    SELECT     LPAD(' ', (LEVEL - 1) * 10, ' ') || h.person_id person_id
             , LEVEL
             , h.person_id
             , h.supervisor_id
             , f.full_name
             , f.employee_number
          FROM hr.per_all_people_f f
          JOIN hr.per_all_assignments_f h ON  f.person_id = h.person_id
    START WITH SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND f.employee_number = :employee_number
    CONNECT BY PRIOR h.person_id = h.supervisor_id
           AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date;

HR Records

Sections:

  1. Basic HR Record 1
  2. Basic HR Record 2
  3. Basic HR Record 3
  4. Basic HR Record 4 – Active HR Records with no Oracle Account
  5. More Detailed HR Staff Report

Basic HR Record 1

This is a very simple look at only the PER_ALL_PEOPLE_F table which holds details about the person record.

-- ##############################################################################
--    BASIC HR RECORD 1
-- ############################################################################*/

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
       , applsys.fnd_user fu
   WHERE papf.last_updated_by = fu.user_id
     AND papf.full_name LIKE :pn
ORDER BY papf.last_update_date DESC;

Basic HR Record 2

Slightly more involved, pulling in the assignments table as well, which means you can then find things like Job Title and HR Organization by joining to the relevant tables.

-- ##############################################################################
--    BASIC HR RECORD 2
-- ############################################################################*/

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
     , pj.NAME job_title
     , papf.email_address
     , paaf.normal_hours
  FROM hr.per_all_people_f papf
     , hr.per_all_assignments_f paaf
     , hr.hr_all_organization_units haou
     , hr.per_jobs pj
     , applsys.fnd_user fu
 WHERE 1 = 1
   AND papf.person_id = paaf.person_id
   and paaf.last_updated_by = fu.user_id
   AND paaf.organization_id = haou.organization_id(+)
   AND paaf.job_id = pj.job_id(+)
   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.full_name LIKE :pn
   AND 1 = 1;

-- slight variation

SELECT   papf.employee_number
       , papf.effective_start_date
       , papf.effective_end_date
       , paaf.effective_start_date
       , paaf.effective_end_date
       , past.*
    FROM hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.per_assignment_status_types past
       , applsys.fnd_user fu
   WHERE fu.employee_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.assignment_status_type_id = past.assignment_status_type_id
     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 NVL(fu.end_date, SYSDATE + 1) > SYSDATE
     AND fu.user_name = 'BOBHOPE';

Basic HR Record 3

-- ##############################################################################
--      BASIC HR RECORD 3
-- ##############################################################################

     select ppx.full_name requester_full_name
          , ppx.employee_number requester_empno
          , haou.name hr_org
          , pptt.user_person_type person_type
          , pax.assignment_number
          , look_asg_status.meaning assign_status
          , fu.user_name requester_user_name
          , fu.email_address requester_email
          , pj.name job_title
          , pp.name position
          , pg.name grade
          , gcc.concatenated_segments default_expense_account
       from per_people_x ppx
       join per_assignments_x pax on ppx.person_id = pax.person_id
  left join per_assignment_status_types past on pax.assignment_status_type_id = past.assignment_status_type_id
  left join hr_all_organization_units haou on pax.organization_id = haou.organization_id
  left join fnd_user fu on fu.employee_id = ppx.person_id
  left join per_jobs pj on pj.job_id = pax.job_id
  left join per_positions pp on pax.position_id = pp.position_id
  left join per_grades pg on pax.grade_id = pg.grade_id
  left join gl_code_combinations_kfv gcc on pax.default_code_comb_id = gcc.code_combination_id
  left join fnd_lookup_values look_asg_status on look_asg_status.lookup_code = past.per_system_status and look_asg_status.lookup_type = 'PER_ASS_SYS_STATUS'
  left join per_person_types_tl pptt on ppx.person_type_id = pptt.person_type_id
      where 1 = 1
--        and ppx.full_name = 'Hope, Bob'
        and ppx.full_name LIKE 'Hope%';

Basic HR Record 4 – Active HR Records with no Oracle Account

HR records are linked to Oracle EBS user accounts via the FND_USER table. Having a left join on it means that you could edit it to find HR records not linked to an EBS account by adding something like AND fu.user_id IS NULL.

-- ##############################################################################
--      BASIC HR RECORD 4
-- ##############################################################################

     select ppx.full_name requester_full_name
          , ppx.employee_number requester_empno
          , haou.name hr_org
          , pptt.user_person_type person_type
          , pax.assignment_number
          , look_asg_status.meaning assign_status
          , fu.user_name requester_user_name
          , fu.email_address requester_email
          , pj.name job_title
          , pp.name position
          , pg.name grade
          , gcc.concatenated_segments default_expense_account
       from per_people_x ppx
       join per_assignments_x pax on ppx.person_id = pax.person_id
  left join per_assignment_status_types past on pax.assignment_status_type_id = past.assignment_status_type_id
  left join hr_all_organization_units haou on pax.organization_id = haou.organization_id
  left join fnd_user fu on fu.employee_id = ppx.person_id
  left join per_jobs pj on pj.job_id = pax.job_id
  left join per_positions pp on pax.position_id = pp.position_id
  left join per_grades pg on pax.grade_id = pg.grade_id
  left join gl_code_combinations_kfv gcc on pax.default_code_comb_id = gcc.code_combination_id
  left join fnd_lookup_values look_asg_status on look_asg_status.lookup_code = past.per_system_status and look_asg_status.lookup_type = 'PER_ASS_SYS_STATUS'
  left join per_person_types_tl pptt on ppx.person_type_id = pptt.person_type_id
      where 1 = 1
--        and ppx.full_name = 'Hope, Bob'
--        and ppx.full_name LIKE 'Smith%'
        and fu.user_name is null
        and pptt.user_person_type = 'Employee Permanent'
        and look_asg_status.meaning = 'Active Assignment'
        and papf.current_employee_flag = 'Y'
        and paaf.assignment_type = 'E'
        and paaf.primary_flag = 'Y'
        and 1 = 1;

More Detailed HR Staff Report

-- ##############################################################################
--      MORE DETAILED HR STAFF REPORT
-- ##############################################################################

SELECT   papf.business_group_id bgid
       , papf.full_name
       , NVL2(fu.user_guid,'Y','N') sso
       , papf.employee_number empno
       , NVL(fu.user_name, '###') login
       , fu.description
       , NVL(papf2.full_name, 'n/a') "**BOSS"
       , NVL(gal.authorization_limit, 0) gl_limit
       , NVL(awsl.signing_limit, 0) iexpenses_limit
       , NVL((SELECT 'Y' from po.po_agents pa where pa.agent_id = papf.person_id),'N') buyer
       , pj.NAME job_title
       , pjd.segment1 job_def
       , haou.NAME hr_org
       , gcc.concatenated_segments default_charge_account
       , fu.start_date user_start_date
       , fu.last_logon_date
       , papf.email_address
       , pg.NAME grade
       , hla.description user_location
       , '## MANAGER ##'
       , papf2.employee_number mgr_empno
       , fu2.description mgr_desc
       , TRIM(fu2.user_name) mgr_uname
       , fu2.end_date
       , fu2.last_update_date
       , papf2.email_address mgr_email
       , '## IDs ##'
       , papf.person_id 
       , fu.user_id
       , papf2.person_id mgr_person_id                
    FROM applsys.fnd_user fu
       , applsys.fnd_user fu2
       , ap.ap_web_signing_limits_all awsl
       , gl.gl_authorization_limits gal
       , hr.per_all_people_f papf
       , hr.per_all_people_f papf2
       , hr.per_all_assignments_f paaf
       , hr.per_jobs pj
       , hr.per_job_definitions pjd
       , hr.per_grades pg
       , hr.hr_all_organization_units haou
       , hr.hr_locations_all hla
       , hr.per_person_type_usages_f pptu
       , hr.per_assignment_status_types past
       , gl.gl_code_combinations_kfv gcc
   WHERE papf.person_id = fu.employee_id(+)
     AND papf.person_id = paaf.person_id
     AND papf.person_id = pptu.person_id(+)
     AND papf.person_id = gal.employee_id(+)
     AND papf.person_id = awsl.employee_id(+)
     AND paaf.organization_id = haou.organization_id(+)
     AND paaf.location_id = hla.location_id(+)
     AND paaf.supervisor_id = papf2.person_id(+)
     AND paaf.job_id = pj.job_id(+)
     AND paaf.grade_id = pg.grade_id(+)
     AND paaf.assignment_status_type_id = past.assignment_status_type_id(+)
     AND fu2.employee_id(+) = papf2.person_id    
     AND pj.job_definition_id = pjd.job_definition_id(+)
     AND haou.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id(+)
     AND paaf.default_code_comb_id = gcc.code_combination_id
     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 SYSDATE BETWEEN pptu.effective_start_date AND pptu.effective_end_date
     AND SYSDATE BETWEEN NVL(papf2.effective_start_date, SYSDATE) AND NVL(papf2.effective_end_date, SYSDATE)
     AND past.per_system_status IN('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
     AND papf.current_employee_flag = 'Y'
     AND paaf.assignment_type = 'E'
     AND paaf.primary_flag = 'Y'
     AND papf.full_name LIKE :pn
ORDER BY papf.last_name;

Date Tracking and Basic HR Table Walkthrough

Sections:

  1. Intro
  2. Date Tracking
  3. Returning data for today’s date
  4. Time Machine SQL
  5. Adding in HR Assignments
  6. Adding in Job Titles, Grade, Location etc.
  7. Multiple Assignments

Intro

If you spend any time working on HR records in Oracle EBS (e.g. 11.5.10 / Release 12), you’ll be familiar with the option on the People > Enter and Maintain screen where you can change the effective date… which in Oracle HR terms is called date tracking. In effect, when you make changes to an HR record, you can either correct or update a record – the info below provides more details.

Date Tracking

As taken from Manoj Madhusoodanan’s Oracle Blog Post Date Tracking in Oracle HRMS:

Update Date Track Modes To maintain employee data effectively Oracle HCM is using a mechanism called date tracking. The main motive behind the date track mode is to maintain past, present and future data effectively.

The various update date track modes are:

– CORRECTION: Over writes the data. No history will maintain.
– UPDATE: Keeps the history and new change will effect as of effective date
– UPDATE_CHANGE_INSERT: Inserts the record and preserves the future
– UPDATE_OVERRIDE: Inserts the record and overrides the future

Therefore if you ran a simple check on the main HR record table “per_all_people_f” you might end up with multiple rows for the same person because over time, changes have been made to their HR record.

-- ##############################################################################
--   BASIC HR RECORDS
-- ############################################################################*/

-- employee header

    SELECT papf.person_id
         , papf.employee_number
         , papf.national_identifier
         , papf.date_of_birth
         , papf.email_address
         , papf.title
         , papf.first_name
         , papf.last_name
         , papf.full_name
         , papf.known_as
         , papf.sex
         , papf.suffix
         , papf.start_date
         , papf.effective_start_date
         , papf.effective_end_date
         , papf.marital_status
         , papf.object_version_number
      FROM hr.per_all_people_f papf
     WHERE papf.employee_number = '123456';

Let’s say this SQL returned 6 records, with effective_start_date and effective_end_date values as follows:

EFFECTIVE_START_DATE    | EFFECTIVE_END_DATE
=====================================================
01/01/1990      | 26/09/2006
27/09/2006      | 02/04/2007
03/04/2007      | 24/04/2007
25/04/2007      | 30/09/2007
01/10/2007      | 29/01/2008
30/01/2008      | 31/12/4712

Returning data for today’s date

To change your SQL to only return data relevant to today’s date, you can change the above SQL to:

SELECT papf.*
      FROM hr.per_all_people_f papf
     WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
       AND papf.employee_number = '123456';

The “SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date” line is used to restrict the results to data that is current for today’s date.

Alternatively, you can use the PER_PEOPLE_X view instead of using PER_ALL_PEOPLE_F as the view only returns records current as of sysdate.

Time Machine SQL

Using the same logic above, we could change the SQL to return data for a person for a date in the past, which is sometimes useful.

SELECT papf.*
      FROM hr.per_all_people_f papf
     WHERE '28-SEP-2006' BETWEEN papf.effective_start_date AND papf.effective_end_date
       AND papf.employee_number = '123456';

Adding in HR Assignments

While per_all_people_f contains details about a person such as their name, marital status, date of birth and so on, details to do with their job (or assignment) are held on the per_all_assignments_f table.

This links through to things like the person’s job title, grade, supervisor, default costing, location, HR org etc.

The assignment table is also date tracked in the same way as per_all_people_f.

SELECT papf.person_id
         , papf.employee_number
         , papf.full_name
         , '############'
         , paaf.*
      FROM hr.per_all_people_f papf
      JOIN hr.per_all_assignments_f paaf ON papf.person_id = paaf.person_id
     WHERE 1 = 1
       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.employee_number = '123456'
       AND 1 = 1;
Alternatively, you can use the PER_ASSIGNMENTS_X view instead of using PER_ALL_ASSIGNMENTS_F as the view only returns records current as of sysdate.

Adding in Job Titles, Grade, Location etc.

This shows an example of how to bring in things like the job title, grade, location and HR Org:

SELECT papf.person_id
         , papf.employee_number
         , papf.full_name
         , pj.name job_title
         , pg.name grade
         , hla.location_code location
         , haou.name hr_org
      FROM hr.per_all_people_f papf
      JOIN hr.per_all_assignments_f paaf ON papf.person_id = paaf.person_id
 LEFT JOIN hr.per_jobs pj ON paaf.job_id = pj.job_id
 LEFT JOIN hr.per_grades pg ON paaf.grade_id = pg.grade_id
 LEFT JOIN hr.hr_locations_all hla ON paaf.location_id = hla.location_id
 LEFT JOIN hr.hr_all_organization_units haou ON haou.organization_id = paaf.location_id
     WHERE 1 = 1
       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.employee_number = '123456'
       AND 1 = 1;

Multiple Assignments

Since staff can have multiple assignments, sometimes you might want to add in extra checks into your SQL to only return specific types of assignments – for example:

 ...
            AND papf.current_employee_flag = 'Y'
            AND paaf.assignment_type = 'E'
            AND paaf.primary_flag = 'Y'
            ...

HR Records – Infinite Hierarchy Loops

Sections:

  1. ORA-01436: CONNECT BY loop in user data
  2. Recreating ORA-01436 using dummy data
  3. Finding Infinite Loops

ORA-01436: CONNECT BY loop in user data

Sometimes you might run your SQL to check supervisor hierarchies, and end up with this error:

ORA-01436: CONNECT BY loop in user data

Recreating ORA-01436 using dummy data

If you do, it can take a while to track that down. Luckily you can use SQL to find the faulty record, which is normally where a manager ends up as their own manager.

For example, taking this SQL:

WITH tbl_data AS
(SELECT 12132  person_id, 'Elizabeth II'                             full_name, NULL  supervisor_id FROM DUAL UNION ALL
 SELECT 9910   person_id, 'Charles, Prince of Wales'                 full_name, 12132 supervisor_id FROM DUAL UNION ALL
 SELECT 11019  person_id, 'Prince Andrew, Duke of York'              full_name, 12132 supervisor_id FROM DUAL UNION ALL
 SELECT 7305   person_id, 'Prince William, Duke of Cambridge'        full_name, 9910  supervisor_id FROM DUAL UNION ALL
 SELECT 7304   person_id, 'Prince Henry of Wales'                    full_name, 9910  supervisor_id FROM DUAL UNION ALL
 SELECT 19436  person_id, 'Prince George of Cambridge'               full_name, 7305  supervisor_id FROM DUAL UNION ALL
 SELECT 19437  person_id, 'Princess Charlotte of Cambridge'          full_name, 7305  supervisor_id FROM DUAL UNION ALL
 SELECT 23213  person_id, 'Princess Beatrice of York'                full_name, 11019 supervisor_id FROM DUAL UNION ALL
 SELECT 21315  person_id, 'Princess Eugenie of York'                 full_name, 11019 supervisor_id FROM DUAL)
        SELECT LPAD(' ', (LEVEL - 1) * 10, ' ') || person_id person_id
             , full_name
          FROM tbl_data
    START WITH person_id = 12132
    CONNECT BY PRIOR person_id = supervisor_id;
    
--------------------------

PERSON_ID                              | FULL_NAME
=======================================| =========================================
12132                                  | Elizabeth II
          9910                         | Charles, Prince of Wales
                    7304               | Prince Henry of Wales
                    7305               | Prince William, Duke of Cambridge
                              19436    | Prince George of Cambridge
                              19437    | Princess Charlotte of Cambridge
          11019                        | Prince Andrew, Duke of York
                    21315              | Princess Eugenie of York
23213                                  | Princess Beatrice of York

If you made Elizabeth II the manager of “Princess Beatrice of York”, and ran the checking SQL, you’d get the error – e.g. via this SQL:

WITH tbl_data AS
(SELECT 12132  person_id, 'Elizabeth II'                             full_name, 11019 supervisor_id FROM DUAL UNION ALL
 SELECT 9910   person_id, 'Charles, Prince of Wales'                 full_name, 12132 supervisor_id FROM DUAL UNION ALL
 SELECT 11019  person_id, 'Prince Andrew, Duke of York'              full_name, 12132 supervisor_id FROM DUAL UNION ALL
 SELECT 7305   person_id, 'Prince William, Duke of Cambridge'        full_name, 9910  supervisor_id FROM DUAL UNION ALL
 SELECT 7304   person_id, 'Prince Henry of Wales'                    full_name, 9910  supervisor_id FROM DUAL UNION ALL
 SELECT 19436  person_id, 'Prince George of Cambridge'               full_name, 7305  supervisor_id FROM DUAL UNION ALL
 SELECT 19437  person_id, 'Princess Charlotte of Cambridge'          full_name, 7305  supervisor_id FROM DUAL UNION ALL
 SELECT 23213  person_id, 'Princess Beatrice of York'                full_name, 11019 supervisor_id FROM DUAL UNION ALL
 SELECT 21315  person_id, 'Princess Eugenie of York'                 full_name, 11019 supervisor_id FROM DUAL)
        SELECT LPAD(' ', (LEVEL - 1) * 10, ' ') || person_id person_id
             , full_name
          FROM tbl_data
    START WITH person_id = 12132
    CONNECT BY PRIOR person_id = supervisor_id;

Finding Infinite Loops

You can use SQL to find the problem records – for example, for the dummy data above:

WITH tbl_data AS
(SELECT 12132  person_id, 'Elizabeth II'                             full_name, 11019 supervisor_id FROM DUAL UNION ALL
 SELECT 9910   person_id, 'Charles, Prince of Wales'                 full_name, 12132 supervisor_id FROM DUAL UNION ALL
 SELECT 11019  person_id, 'Prince Andrew, Duke of York'              full_name, 12132 supervisor_id FROM DUAL UNION ALL
 SELECT 7305   person_id, 'Prince William, Duke of Cambridge'        full_name, 9910  supervisor_id FROM DUAL UNION ALL
 SELECT 7304   person_id, 'Prince Henry of Wales'                    full_name, 9910  supervisor_id FROM DUAL UNION ALL
 SELECT 19436  person_id, 'Prince George of Cambridge'               full_name, 7305  supervisor_id FROM DUAL UNION ALL
 SELECT 19437  person_id, 'Princess Charlotte of Cambridge'          full_name, 7305  supervisor_id FROM DUAL UNION ALL
 SELECT 23213  person_id, 'Princess Beatrice of York'                full_name, 11019 supervisor_id FROM DUAL UNION ALL
 SELECT 21315  person_id, 'Princess Eugenie of York'                 full_name, 11019 supervisor_id FROM DUAL)
    SELECT DISTINCT SYS_CONNECT_BY_PATH((full_name || ' - ' || person_id), '____') AS PATH
                  , CONNECT_BY_ISCYCLE
               FROM tbl_data
              WHERE 1 = 1
                AND CONNECT_BY_ISCYCLE = 1
         START WITH person_id = :personid
         CONNECT BY NOCYCLE PRIOR person_id = supervisor_id;
         
------------------------

PATH: ____Elizabeth II - 12132____Prince Andrew, Duke of York - 11019

These SQLs can be used to find loops in the standard Oracle Financials HR tables:

-- SQL to find supervisor infinite loops
    -- answered via https://forums.oracle.com/forums/thread.jspa?messageID=10083043
    
    SELECT DISTINCT SYS_CONNECT_BY_PATH((papf.full_name || ' - ' || papf.employee_number), '____') AS PATH
                  , CONNECT_BY_ISCYCLE
               FROM hr.per_all_assignments_f h
                  , hr.per_all_people_f papf
              WHERE h.person_id = papf.person_id
                AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
                AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
                AND CONNECT_BY_ISCYCLE = 1
         START WITH SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
                AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
                AND h.person_id = :personid
         CONNECT BY NOCYCLE PRIOR h.person_id = supervisor_id
                AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
                AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date;

    --slightly more involved version            
    SELECT     LPAD(' ', (LEVEL - 1) * 10, ' ') || h.person_id person_id
             , SYS_CONNECT_BY_PATH((f.full_name || ' - ' || f.employee_number), '____') AS PATH
             , LEVEL
             , f.full_name
             , pg.name gd
             , f.employee_number empno
             , u.user_name user_name
--             , u.description
             , pj.NAME job_title
             , haou.name hr_org
             , CONNECT_BY_ISCYCLE
          FROM hr.per_all_people_f f
          JOIN hr.per_all_assignments_f h ON f.person_id = h.person_id
          JOIN hr.hr_all_organization_units haou ON h.organization_id = haou.organization_id
          JOIN hr.per_jobs pj ON h.job_id = pj.job_id
     LEFT JOIN hr.per_grades pg ON h.grade_id = pg.grade_id
     LEFT JOIN applsys.fnd_user u ON h.person_id = u.employee_id
         WHERE 1 = 1
           AND CONNECT_BY_ISCYCLE = 1
    START WITH f.employee_number = :employee_number
           and SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
    CONNECT BY nocycle prior h.person_id = supervisor_id
           AND SYSDATE BETWEEN f.effective_start_date AND f.effective_end_date
           AND SYSDATE BETWEEN h.effective_start_date AND h.effective_end_date;