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;

Query: Job Extra Informations

select
    pjt.name                           "Name",
    --
    pjei.jei_information_category           "Type",
    decode (pjei.jei_information_category,
       'Job Category',pjei.jei_information1||'.'||pjei.jei_information2||'.'||pjei.jei_information3||'.'
                ||pjei.jei_information4|| '.'|| pjei.jei_information5|| '.'|| pjei.jei_information6,
       'MX_SS_SALARY_TYPE', pjei.jei_information1,
       'PER_SUCCESSION_PLANNING', pjei.jei_information2|| '.'|| pjei.jei_information1,
       null)                      "Details",
    --
    pj.job_id
from
    per_job_extra_info pjei,
    per_jobs pj,
    per_jobs_tl pjt
where
    1=1
and pjei.job_id = pj.job_id
and pj.job_id = pjt.job_id
and pjt.language = userenv('LANG')
--
and pjt.name like 'ACC400%'
order by
    1;

Query: Position Valid Grades

select
    hapf.name                           "Position Name",
    --
    pgl.name                            "Grade",
    pg.date_from                        "Date From",
    --
    pg.date_to                          "Date To",
    hapf.position_id
from
    per_valid_grades pvg,
    per_grades pg,
    per_grades_tl pgl,
    hr_all_positions_f hapf
where 1=1
and pgl.grade_id = pg.grade_id
and pgl.language = userenv('LANG')
and pg.grade_id = pvg.grade_id
and pvg.position_id = hapf.position_id
--
and hapf.name like '1001.Operations Vice President';

Query: Grade Rates

select
    pr.name                       "Rate",
    --
    pgt.name                      "Grade Name",
    fcv.name                      "Currency",
    pgr.value                     "Value",
    pgr.minimum                   "Minimum",
    pgr.maximum                   "Maximum",
    pgr.mid_value                 "Mid Value",
    pgr.effective_start_date      "Eff: Dates From",
    pgr.effective_end_date        "Eff: Dates To",
    --
    pgt.grade_id,
    pr.rate_id  
from
    per_grades_tl pgt,
    pay_grade_rules_f pgr,
    pay_rates pr    ,
    fnd_currencies_vl fcv  
where
    1=1
and pgt.grade_id(+) = pgr.grade_or_spinal_point_id
and pr.rate_id = pgr.rate_id
and fcv.currency_code(+) = pgr.currency_code
--
and pr.name = 'Standard Hourly Rates'
order by
    1,2,3,4

Query: Grade Pay Scales

select
    pps.name                            "Name",
    --
    pps.last_automatic_increment_date   "Last Auto Increment",
    pps.increment_frequency             "Increment Freq:Number",
    pps.increment_period                "Increment Freq:Period",
    psp.spinal_point                    "Point",
    psp.sequence                        "Sequence",
    --
    pps.parent_spine_id
from
    per_parent_spines pps,
    per_spinal_points psp
where
    1=1
and pps.parent_spine_id = psp.parent_spine_id
--
and pps.name = 'Hrly Pay Scale:Welder /Laborer'
order by
    1,2,3;

Query: Grade Scale Rates

select
    pgtl.name                   "Grade Name" ,
    --
    pps.name                    "Pay Scale Name",
    pspsf.sequence              "Ceiling: Step",
    grs.starting_step           "Starting Step",
    grs.effective_start_date    "Effective Start Date",
    rownum                      "Step",
    psp.spinal_point            "Point",
    --
    pgtl.grade_id
from
    per_grades_tl pgtl,
    per_grade_spines_f grs,
    per_parent_spines pps,
    per_spinal_points psp,
    per_spinal_point_steps_f pspsf
where
    1=1
and grs.grade_id = pgtl.grade_id
and grs.parent_spine_id = pps.parent_spine_id
and psp.parent_spine_id = pps.parent_spine_id
AND grs.ceiling_step_id = pspsf.step_id
--
and  pgtl.name = '200.2.Central'
order by
    1,2,5,6;

Query: People Assignment Salary - Current

select
    ppf.employee_number                                             "Employee Number",
    --
    ppp.change_date                                                 "From",
    ppp.proposed_salary_n                                           "Salary",
    (ppp.proposed_salary_n*12)                                      "Annual Salary",
    (ppp.proposed_salary_n*12)                                      "FT Annual Salary",
    per_saladmin_utility.get_fte_factor (ppp.assignment_id,ppp.change_date)     "FTE Factor",  
    hr_general.decode_lookup ('PER_SAL_PROPOSAL_STATUS', approved)  "Status",
    ppp.next_perf_review_date                                       "Next Salary Review",
    hr_general.decode_lookup ('PROPOSAL_REASON', proposal_reason)   "Reason For Change",
    ppp.date_to                                                     "To",
    --
    ppp.pay_proposal_id,
    ppp.assignment_id,
    ppp.event_id,
    ppp.business_group_id
    --
from
    per_pay_proposals ppp,
    per_performance_reviews ppr,
    per_assignments_f2 paa,
    per_people_f ppf,
    per_pay_bases ppb,
    pay_input_values_f piv,
    pay_element_types_f pet  
where  
    1=1
and ppp.performance_review_id =ppr.performance_review_id(+)
and ppp.assignment_id = paa.assignment_id
and paa.person_id = ppf.person_id
and sysdate between ppp.change_date and nvl (ppp.date_to, hr_general.end_of_time)
and ppp.change_date between paa.effective_start_date and paa.effective_end_date
and paa.pay_basis_id(+) = ppb.pay_basis_id
and ppb.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and ppp.change_date between piv.effective_start_date and piv.effective_end_date
and ppp.change_date between pet.effective_start_date and pet.effective_end_date
--
and ppf.employee_number = '1309'
order by
   1,2,3;