Query: Salary Management

select
    ppf.employee_number                 "Employee Number",
    --
    ppf.full_name                         "Full Name",
    psf.assignment_number                 "Assignment Number",
    ppb.name                              "Salary basis",
    ppp.change_date                       "Approved Date",
    ppp.proposed_salary_n                 "Approved Salary",
    pro1.change_date                      "Change Date",
    pro1.proposed_salary_n                "Proposed Salary",
    proposal_reason                       "Proposal Reason",
    pppc.change_percentage                "COL %",
    pppc.change_amount_n                  "COL Amount",
    psf.employment_category               "Assignment Category",           -- lookup;EMP_CAT
    --
    ppf.person_id,
    ppb.pay_basis_id
from
     per_all_people_f ppf,
     per_assignments_f psf,
     per_pay_bases ppb,
      per_pay_proposals pro1,
     (select ppp.change_date,
                  ppp.proposed_salary_n,
                  ppp.approved,
                  ppp.assignment_id
             from per_pay_proposals ppp
            where approved = 'Y')  ppp,
    per_pay_proposal_components pppc        
where
    1=1
and ppf.person_id = psf.person_id
and psf.pay_basis_id = ppb.pay_basis_id
and psf.assignment_id = pro1.assignment_id(+)
and pro1.approved(+) = 'N'
and psf.assignment_id = ppp.assignment_id(+)
and (ppp.change_date is null
     or ppp.change_date = (select max (change_date)
                        from per_pay_proposals pro3
                        where ppp.assignment_id = pro3.assignment_id
                        and pro3.approved = 'Y'))
and pro1.pay_proposal_id = pppc.pay_proposal_id(+)
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between psf.effective_start_date and psf.effective_end_date
--
and ppf.employee_number = '1309'
order by
    1,2;