Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 10 May 2016

Query To find employee Salary details in oracle apps

SELECT   ppf.employee_number "EMPLOYEE NUMBER", ppp.change_date "FROM",
         ppp.date_to "TO",
           NVL (ppp.proposed_salary_n, 0)
         - (LAG (ppp.proposed_salary_n) OVER (ORDER BY ppp.change_date))
                                                              "CHANGE AMOUNT",
         ROUND
            (  (  (  NVL (ppp.proposed_salary_n, 0)
                   - (LAG (ppp.proposed_salary_n) OVER (ORDER BY ppp.change_date)
                     )
                  )
                / ppp.proposed_salary_n
               )
             * 100,
             2
            ) "CHANGE %",
         ppp.proposed_salary_n "SALARY",
         (ppp.proposed_salary_n * ppb.pay_annualization_factor
         ) "ANNUALIZED SALARY",
         pet.input_currency_code "CURRENCY", ppb.NAME "SALARY BASIS",
         ppp.approved "STATUS", ppp.pay_proposal_id
    FROM per_pay_proposals ppp,
         per_performance_reviews ppr,
         per_all_assignments_f paa,
         per_all_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 paa.assignment_id = ppp.assignment_id
     AND paa.person_id = ppf.person_id
     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 = '30987'
--AND PAA.ASSIGNMENT_NUMBER   = '471-96-6472'
GROUP BY ppf.employee_number,
         ppp.change_date,
         ppp.date_to,
         NVL (ppp.proposed_salary_n, 0),
         ppp.proposed_salary_n,
         ppp.change_date,
         NVL (ppp.proposed_salary_n, 0),
         ppp.proposed_salary_n,
         (ppp.proposed_salary_n * ppb.pay_annualization_factor
         ),
         ppp.change_date,
         pet.input_currency_code,
         ppb.NAME,
         ppp.approved,
         ppp.pay_proposal_id

ORDER BY change_date DESC;
Share this article :

0 comments:

Post a Comment