Core HR SQL Queries

By | February 24, 2015

Organization through Organization Hierarchy
Find the Parent Organization of a Particular Type through Organization Hierarchy on the basis of any organization. For Example I need the Branch Name on the basis of any organization (Department, Sub Department or Section under it.

Organization Hierarchy

Query to Get Parent Organization of a Particular Type

Download Org Hierachy Query

Find Organization Manager Query
There is one screen which hold the Organization and Manager relationship. If you want to get manager for any organization, then use following query to get the Manager ID.

Organization Manager

Download Org Manager Query

 

Find the List Users that have a particular Responsibility

select rg.user_id,fu.user_name,ppf.employee_number,ppf.full_name,rt.responsibility_name
from fnd_responsibility_tl rt,FND_USER_RESP_GROUPS_DIRECT rg,fnd_user fu, per_all_people_f ppf
where upper(RESPONSIBILITY_NAME) like ‘%APPROVALS MANAGEMENT BUSINESS ANALYST’
and rt.RESPONSIBILITY_ID = rg.RESPONSIBILITY_ID
and rg.user_id = fu.user_id
and fu.employee_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
Core HR Queries

Find total Active employee in the company

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where ppf.current_employee_flag = ‘Y’
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number is not null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Employee’
and business_group_id = ppf.BUSINESS_GROUP_ID
)
Get Month Wise Hired Employees Head Count

select to_char(ORIGINAL_DATE_OF_HIRE,’MON’) MM, COUNT(PERSON_ID) TOTAL from per_people_x
WHERE to_char(ORIGINAL_DATE_OF_HIRE,’RRRR’) = ‘2014’
group by to_char(ORIGINAL_DATE_OF_HIRE,’MON’)

Provide a List of Active Employees along with their Supervisor’s Name and email address
select ppf.employee_number,ppf.full_name,ppf.email_address ,paaf.supervisor_id,sup.full_name,sup.email_address
from per_all_people_f ppf,per_all_assignments_f paaf,per_people_x sup
where ppf.current_employee_flag = ‘Y’
and paaf.supervisor_id (+) = sup.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppf.person_id = paaf.person_id
and ppf.employee_number is not null
and ppf.employee_number = ‘539988’
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Employee’
and business_group_id = ppf.BUSINESS_GROUP_ID
)

Get the Employee Salary Increase Summary

select ppf.employee_number,ppf.full_name,ppp.proposed_salary_n,ppp.change_date
from per_pay_proposals ppp,per_all_people_f ppf,per_all_assignments_f paaf
where ppf.person_id = paaf.person_id
and paaf.assignment_id = ppp.assignment_id
and ppf.employee_number = ‘603167’
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppp.approved = ‘Y’
and change_date = (select min(change_date) from per_pay_proposals where assignment_id = ppp.assignment_id and proposed_salary_n = ppp.proposed_salary_n)
order by ppp.change_date desc

Get the History for An Employee’s Transfers in the company

Get the list of employee who were terminated and have been rehired

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and person_id in
( select person_id
from (
select count(1),person_id
from per_periods_of_service
group by person_id
having count(1) > 1
)
)
Get the List of Terminated Employees

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and current_employee_flag is null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Ex-employee’
and business_group_id = ppf.BUSINESS_GROUP_ID
)

Get an Employees Leave History

select a.date_start,a.date_end,a.absence_days,
(select name from per_ABSENCE_ATTENDANCE_types
where ABSENCE_ATTENDANCE_TYPE_ID = a.ABSENCE_ATTENDANCE_TYPE_ID
and business_group_id =a.business_group_id) leave_type
from per_absence_attendances a
where person_id = (select person_id from per_all_people_f where employee_number = ‘47457’ and employee_number is not null)
order by date_start desc

Get EIT Based information for employees

select ppf.employee_number,ppf.full_name, ppei.* from per_all_people_f ppf, per_people_extra_info ppei
where ppf.person_id = ppei.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = ‘603167’
and ppei.information_type = ‘XX_OTHER_DETAILS’

Get SIT Based information for employees

select * from per_person_analyses ppa,per_analysis_criteria pac
where ppa.ANALYSIS_CRITERIA_ID = pac.ANALYSIS_CRITERIA_ID
and person_id = 115043
and pac.ID_FLEX_NUM =
(
select fis.ID_FLEX_NUM
from FND_ID_FLEXS fif, FND_ID_FLEX_STRUCTURES fis
where fif.ID_FLEX_CODE = fis.ID_FLEX_CODE
and id_flex_structure_code = ‘XX_PROBATION_EVALUATION’
)

Get the List of all Organization which are below an organization in Org Hierarchy

Get the List of all Organization which are Above an organization in Org Hierarchy

Parent and Child Organizations List

select hp.name parent_org, hc.name child_organization
from PER_ORG_STRUCTURE_ELEMENTS pose, PER_ORG_STRUCTURE_VERSIONS posv, hr_all_organization_units hc, hr_all_organization_units hp
where 1=1
and pose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and pose.ORGANIZATION_ID_CHILD = hc.organization_id
and pose.ORGANIZATION_ID_PARENT = hp.organization_id

Get the Oracle Balance Values 

SELECT pdb.defined_balance_id,dim.dimension_name
FROM pay_defined_balances pdb,
pay_balance_types typ,
pay_balance_dimensions dim
WHERE pdb.balance_type_id = typ.balance_type_id
AND pdb.balance_dimension_id = dim.balance_dimension_id
AND dim.legislation_code = ( SELECT legislation_code FROM per_business_groups WHERE business_group_id = 83)
AND UPPER (typ.balance_name) = UPPER (‘Indemnity Days’)
— AND UPPER (dim.dimension_name) = UPPER (‘Assignment Run’);

select pay_balance_pkg.get_value (P_Balance_ID, P_Assignment_Action_ID, trunc(sysdate))
from dual

Trunc(Sysdate) will return you balance value as of Date, you can use any old date as per your requirement.

Disable Responsibilities for Users

Normally such requirement occurs when you want that no user should be able to logged in during any particular time like some upgrade project or any critical migration activity. You can disable the users by following query.  Al though direct updates are not allowed but since this table does not contain object version id column so we used queries mentioned below. Please consult Oracle Support if you want to double check the impact of using this update statement.

update fnd_responsibility a
set a.END_DATE = to_date(’05-APR-2016′)
where a.RESPONSIBILITY_KEY in
(      SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL   OR   A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%’)
and C.EMPLOYEE_ID is not null
)

In order to reset the responsibilities back to original state

update fnd_responsibility a
set a.END_DATE = NULL
where a.RESPONSIBILITY_KEY in
(      SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL   OR   A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%’)
and C.EMPLOYEE_ID is not null
)

and end_date IS not NULL
and end_date = ’05-APR-2016′

Provide the List of Employees current and Previous Assignment Details

(shows  Employee Organization Change history. Like wise we can change the query to show the history of Grade, Payroll, Organization, Location, Position, Job change summary)

select
ppf.employee_number
,paaf.assignment_number
,ppf.full_name
,paaf.organization_id current_org_id
,paaf.effective_start_date Curr_org_start_date
,(select name from hr_all_organization_units where organization_id = paaf.organization_id) current_org_name
,paaf_prev.effective_start_date prev_org_start_date
,paaf_prev.organization_id prev_org_id
,(select name from hr_all_organization_units where organization_id = paaf_prev.organization_id) prev_org
from per_all_assignments_f paaf,per_all_assignments_f paaf_prev,pay_people_groups ppg,pay_people_groups ppg_prev,per_all_people_f ppf
where paaf_prev.effective_end_date + 1= paaf.effective_start_date
and paaf_prev.assignment_id = paaf.assignment_id
and paaf_prev.assignment_type = ‘E’
and ppf.employee_number = ‘557331’
and paaf.assignment_type = ‘E’
and paaf.organization_id <> paaf_prev.organization_id
and paaf.PEOPLE_GROUP_ID = ppg.people_group_id
and paaf_prev.PEOPLE_GROUP_ID = ppg_prev.people_group_id
and paaf.effective_start_date between ppf.effective_start_date and ppf.effective_end_date
and paaf.person_id = ppf.person_id
order by paaf_prev.effective_start_date desc

Employee Short Leave Details and Hours Calculation

select paa.person_id,paa.date_start, paa.date_end,time_start,time_end
,round ( ( ((substr(time_end,1,2) -substr(time_start,1,2)) * 60
+ (substr(time_end,4,2) -substr(time_start,4,2))) / 60 ) ,2) Hours
from per_absence_attendances paa,per_absence_Attendance_types paat
where paa.person_id = 68567
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.name = ‘Short Leave’
and paa.date_start between :P_Period_Start_date and :P_Period_End_date
and paa.date_end between :P_Period_Start_date and :P_Period_End_date

Payroll
——-

If have like contents in this post and you think it can be helpful to others, please share it at least once in your circle, in this way, you will join me in my cause to Learn Share and Grow. Come on, Lets Grow Together.

Other Related Posts

Useful Self Service Queries
WISTU – What I Suggest To U
Free Online Test
Solved Issued of Empty Email Notification
Steps for creating custom form in Oracle Apps
How to create backend logging mechanism
Useful Apps SQL Queries Pool
Top 10 Apps Technical Interview Questions