Table walkthrough - HRMS

This post to make us familiar with basic HRMS tables. Since for each employee user creation is a normal process, I am mentioning about user details table too.

--User Details

select * from FND_USER where user_name = :name

-- Employee Associated with User

select * from PER_ALL_PEOPLE_F where person_id in (select employee_id from fnd_user where user_name = :name)

-- Assignment for Employee/User

select * from PER_ALL_ASSIGNMENTS_F where person_id in (select employee_id from fnd_user where user_name = :name)

-- Position for Employee/User

select a.name,a.* from PER_ALL_POSITIONS a where position_id in  (select position_id  from per_all_assignments_f where person_id in (select employee_id from fnd_user where user_name = :name))

-- Position Hirerachy for Employee/User

SELECT  ppse.subordinate_position_id,ppse.parent_position_id,pap.name "Subordinate Position"  FROM

PER_POS_STRUCTURE_ELEMENTS ppse, per_all_positions pap WHERE ppse.subordinate_position_id=pap.position_id

START WITH   ppse.subordinate_position_id in  (select position_id  from per_all_assignments_f where person_id in (select employee_id from fnd_user where user_name = :name))

CONNECT BY PRIOR  ppse.parent_position_id = ppse.subordinate_position_id;         

PS – Post intention is to be familiar with HRMS Tables name, below written script can be used by adding additional filter conditions to filter inactive records.

TRUNC(effective_start_date)   <= TRUNC(SYSDATE)

                        AND      TRUNC(effective_end_date)     > TRUNC(SYSDATE)