Tuesday, November 15, 2016

Query to Find All Active Employees and Current Salary.


Oracle HR and Payroll Related Queries


 Query to Find All Active Employees and Current Salary.





************************

select EMPLOYEE_NUMBER,a.FULL_NAME,x.USER_PERSON_TYPE, c.PROPOSED_SALARY_n Basi_Salary–,c.CHANGE_DATE–,c.*
from per_people_f A,per_assignments_f b,pER_PAY_pROposals c
,per_person_types x
, per_person_type_usages_f e
where a.PERSON_ID=b.PERSON_ID
and b.ASSIGNMENT_ID=C.ASSIGNMENT_ID
and a.EMPLOYEE_NUMBER is not null
–and a.EMPLOYEE_NUMBER=:emp_num
and c.CHANGE_DATE = (select max(d.CHANGE_DATE) from pER_PAY_pROposals d where d.ASSIGNMENT_ID=b.ASSIGNMENT_ID and d.approved = ‘Y’)
–and c.CHANGE_DATE>=:change_date
and b.PAYROLL_ID=62
and :p_effective_date between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
and :p_effective_date between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
and a.PERSOn_id = e.PERSON_ID
and a.EFFECTIVE_START_DATE between e.EFFECTIVE_START_DATE and e.EFFECTIVE_END_DATE
and e.PERSON_TYPE_ID = x.PERSON_TYPE_ID
and x.SYSTEM_PERSON_TYPE = ‘EMP’

 **************************************


No comments: