Saturday, May 2, 2015

Query to Find All Active Employees and Current Salary

select distinct
       ppf.employee_number "Employee Number",
       ppf.full_name       "Employee Name",
       ppt.user_person_type "Person Type",
       ppp.proposed_salary_n "Basic Salary",
       ppp.change_date       "Change Date",
       ppp.next_sal_review_date "Next Review Date"
from per_people_f ppf,
       per_assignments_f paf,
       per_pay_proposals ppp,
       per_person_types ppt,
       per_person_type_usages_f pptu
 where ppf.person_id     = paf.person_id
   and paf.assignment_id = ppp.assignment_id
   and ppf.employee_number is not null
   and ppp.change_date in (
                            select max(ppp1.change_date)
                              from per_pay_proposals ppp1
                             where ppp1.assignment_id = paf.assignment_id
                               and ppp1.approved = 'Y'
                          )
   and paf.payroll_id = 1131
   and :p_effective_date between ppf.effective_start_date and ppf.effective_end_date
   and :p_effective_date between paf.effective_start_date and paf.effective_end_date
   and ppf.person_id   = pptu.person_id
   and ppf.effective_start_date between pptu.effective_start_date and pptu.effective_end_date
   and pptu.person_type_id = ppt.person_type_id
   and ppt.system_person_type = 'EMP'
order by ppp.change_date desc,
         lpad(ppf.employee_number,10,0),
         ppp.proposed_salary_n,
         ppf.full_name asc;