Friday, November 29, 2013

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:

Post a Comment

Best Blogger TipsGet Flower Effect