Core HR SQL Queries

--Organization through Organization Hierarchy

SELECT NAME, TYPE, organization_id
                 FROM (SELECT hou.NAME, TYPE, organization_id
                         FROM hr_all_organization_units hou
                        WHERE organization_id IN (
                                 SELECT     organization_id_child
                                                              organization_id
                                       FROM (SELECT organization_id_child,
                                                    organization_id_parent,
                                                    org_structure_version_id
                                               FROM per_org_structure_elements
                                              WHERE org_structure_version_id IN (
                                                       SELECT posv.org_structure_version_id
                                                         FROM per_organization_structures pos,
                                                              per_org_structure_versions posv
                                                        WHERE primary_structure_flag =
                                                                           'Y'
                                                          AND pos.organization_structure_id =
                                                                 posv.organization_structure_id
                                                          AND pos.NAME =
                                                                 :p_x_org_hierarchy))
                                 CONNECT BY organization_id_child =
                                                   PRIOR organization_id_parent
                                 START WITH organization_id_child =
                                                             :x_organization_id)
                          AND UPPER (hou.TYPE) = :x_organization_type)
                WHERE ROWNUM = 1;

--Find Organization Manager Query
SELECT NVL
                         ((SELECT NVL (papf.person_id, 0)
                             FROM hr_all_organization_units org,
                                  hr_all_organization_units_tl org_tl,
                                  hr_organization_information org_info2,
                                  per_all_people_f papf
                            WHERE org_info2.organization_id =
                                                           org.organization_id
                              AND org_info2.org_information_context =
                                                     'Organization Name Alias'
                              AND org_info2.org_information2 = TO_CHAR (papf.person_id(+))
                              AND org.organization_id = org_tl.organization_id
                              AND org_tl.LANGUAGE = USERENV ('LANG')
                              AND org_tl.NAME = :x_org_name
                              AND papf.current_employee_flag = 'Y'
                              AND TRUNC (:x_effective_date)
                                     BETWEEN papf.effective_start_date
                                         AND papf.effective_end_date),
                          0
                         ) val
                 FROM DUAL;
               
--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


--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') = '2017'
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
--d ppf.employee_number = '39988'
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 = '1000063' 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 = '1000063'
--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 = 81)
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 = '1000063'
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