HRMS PAYROLL SQL QUIRIES

Query to to find the Employee Supervisor name

-----------------------------------------------------------------------

SELECT papf1.full_name supervisor_name

  FROM apps.per_all_people_f papf,

       apps.per_all_assignments_f paaf,

       apps.per_all_people_f papf1

 WHERE papf.person_id = paaf.person_id

   AND paaf.primary_flag = 'Y'

   AND paaf.assignment_type = 'E'

   AND paaf.supervisor_id = papf1.person_id

   AND papf1.current_employee_flag = 'Y'

   AND papf.business_group_id = paaf.business_group_id

   AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

   AND SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_date

Query to find out payroll Costing Detail  /  ”How to find out payroll costed accounts” / “Query to find out Payroll Charged Accounts”.

--------------------------------------------------------------------------------------------------------------------------------------

/* Formatted on 2013/02/06 16:56 (Formatter Plus v4.8.8) */

SELECT DISTINCT d.employee_number, d.full_name, a.concatenated_segments,

                a.element_name,

                DECODE (a.debit_or_credit, 'Debit', costed_value, 1) debit,

                DECODE (debit_or_credit, 'Credit', costed_value, 1) credit

           FROM pay_costs_v a,

                pay_assignment_actions_v b,

                per_assignments_x c,

                per_people_x d

          WHERE d.employee_number = NVL (:p_emp_no, d.employee_number)

            AND TRUNC (b.effective_date) BETWEEN :p_from_date AND :p_to_date

            AND a.assignment_action_id = b.assignment_action_id

            AND b.assignment_id = c.assignment_id

            AND c.person_id = d.person_id

Query to Find All Active Employees and Current Salary.

------------------------------------------------------

/* Formatted on 2013/02/06 16:58 (Formatter Plus v4.8.8) */

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'

Query fo Find out all Earning and Deduction Elements and values after Payroll Run

-----------------------------------------------------------------------------------------------------------------------

/* Formatted on 2013/02/06 17:09 (Formatter Plus v4.8.8) */

SELECT   ppf.employee_number, ppf.person_id, ppf.full_name,

         ppa.time_period_id, ppa.effective_date, tp.period_name,

         paf.organization_id,

         SUM (DECODE (pec.classification_name,

                      'Earnings', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) earnings,

         SUM (DECODE (pec.classification_name,

                      'Voluntary Deductions', TO_NUMBER (rrv.result_value),

                      'Involuntary Deductions', TO_NUMBER (rrv.result_value),

                      'Employer Charges', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) deductions

--ety.element_name,ety.CLASSIFICATION_ID

 --PD.SEGMENT5  POSITION_NO,PD.SEGMENT6 POSITION_NAME,

FROM     per_people_x ppf,

         per_assignments_x paf,

         pay_assignment_actions pas,

         pay_payroll_actions ppa,

         pay_run_results rr,

         pay_run_result_values rrv,

         pay_element_types_f ety,

         pay_input_values_f i,

         per_time_periods tp,

         pay_element_classifications_vl pec

   WHERE ppf.person_id = paf.person_id

     AND paf.assignment_id = pas.assignment_id

     AND pas.assignment_action_id = rr.assignment_action_id

     AND ppa.payroll_action_id = pas.payroll_action_id

     AND rr.element_type_id = ety.element_type_id

     AND i.element_type_id = ety.element_type_id

     AND rrv.run_result_id = rr.run_result_id

     AND rrv.input_value_id = i.input_value_id

     AND tp.time_period_id = ppa.time_period_id

     AND ety.classification_id = pec.classification_id

     AND i.NAME = 'Pay Value'

     --AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID

     AND ppa.effective_date BETWEEN :p_st_effect_date AND :p_end_effect_date

     AND ppf.employee_number = NVL (:p_emp_number, ppf.employee_number)

GROUP BY ppf.full_name,

         ppa.time_period_id,

         effective_date,

--To_Number(Wassa_HR_PACKAGE.Nid_Salary_By_Date(paf.assignment_id,ppa.EFFECTIVE_DATE,'N')),

         ppf.employee_number,

         ppf.person_id,

--PD.SEGMENT5 ,PD.SEGMENT6,

         tp.period_name,

         paf.organization_id

Query for Oracle Payroll Salary Slip /  Salary Slip Detail Report Query / How to find out detail of salary slip/ Element wise Landscape Salary Slip at Payroll Run Levle  or quick pay level

------------------------------------------------------------------------------------------------------------------------------------------------------

/* Formatted on 2013/02/06 17:16 (Formatter Plus v4.8.8) */

SELECT   ppf.employee_number, ppf.person_id, ppf.full_name,

         ppa.time_period_id, ppa.effective_date, tp.period_name,

         SUM (DECODE (ety.element_name,

                      'Basic Salary', TO_NUMBER (rrv.result_value),

                      'Basic Sick Leave Payment', TO_NUMBER (rrv.result_value),

                      'Basic Out Work Incident Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                      0

                     )

             ) earned_salary,

        

/********************************************************************************************************/

         SUM

            (DECODE (ety.element_name,

                     'Transportation Allowance', TO_NUMBER (rrv.result_value),

                     'Transportation Sick Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                     'Transportation Out Work Incident Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                     0

                    )

            ) transportation_allowance,

        

/*************************************************************************************************************/

         SUM

            (DECODE (ety.element_name,

                     'Work Type Allowance', TO_NUMBER (rrv.result_value),

                     'Work Type Sick Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                     'Work Type Out Work Incident Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                     0

                    )

            ) worktype_allowance,

        

/***************************************************************************************************************/

         SUM

            (DECODE (ety.element_name,

                     'Damages Allowance', TO_NUMBER (rrv.result_value),

                     'Damages Sick Leave Payment', TO_NUMBER (rrv.result_value),

                     'Damages Out Work Incident Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                     0

                    )

            ) damage_allowance,

        

/*****************************************************************************************************************/

         SUM

            (DECODE (ety.element_name,

                     'Danger Allowance', TO_NUMBER (rrv.result_value),

                     'Danger Sick Leave Payment', TO_NUMBER (rrv.result_value),

                     'Danger Out Work Incident Leave Payment', TO_NUMBER

                                                             (rrv.result_value),

                     0

                    )

            ) danger_allowance,

        

/*************************************************************************************************************/

         SUM (DECODE (ety.element_name,

                      'Inflation Allowance', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) inflation_allowance,

        

/*********************************************************  Deductions  ************************************************/

         SUM (DECODE (ety.element_name,

                      'Loan Recovery', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) loan_recovery,

         SUM (DECODE (ety.element_name,

                      'Loan Recovery Housing', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) loan_recovery_housing,

         SUM (DECODE (ety.element_name,

                      'Loan Recovery Others', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) loan_recovery_others,

         SUM (DECODE (ety.element_name,

                      'Housing Deduction', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) housing_deduction,

         SUM (DECODE (ety.element_name,

                      'Penalty', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) penalty,

        

/***********************************************************************************************************************/

         SUM (DECODE (ety.element_name,

                      'Civil Pension', TO_NUMBER (rrv.result_value),

                      'Social Insurance', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) civil_pension,

        

/************************************************************************************************************************************/

         SUM (DECODE (ety.element_name,

                      'In Out Leave Deduction', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) in_out_leave_deduction,

         SUM (DECODE (ety.element_name,

                      'Unpaid Leave Deduction', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) unpaid_leave_deduction,

         SUM (DECODE (ety.element_name,

                      'Retrieve Mony Recovery', TO_NUMBER (rrv.result_value),

                      0

                     )

             ) retrieve_mony_recovery

    FROM per_people_x ppf,

         per_assignments_x paf,

         pay_assignment_actions pas,

         pay_payroll_actions ppa,

         pay_run_results rr,

         pay_run_result_values rrv,

         pay_element_types_f ety,

         pay_input_values_f i,

         per_time_periods tp

--   PER_POSITION_DEFINITIONS PD

--PAY_INPUT_VALUES_F

WHERE    ppf.person_id = paf.person_id

     AND paf.assignment_id = pas.assignment_id

     AND pas.assignment_action_id = rr.assignment_action_id

     AND ppa.payroll_action_id = pas.payroll_action_id

     AND rr.element_type_id = ety.element_type_id

     AND i.element_type_id = ety.element_type_id

     AND rrv.run_result_id = rr.run_result_id

     AND rrv.input_value_id = i.input_value_id

     AND tp.time_period_id = ppa.time_period_id

     AND i.NAME = 'Pay Value'

--   AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID

     AND ppa.effective_date BETWEEN :p_from_date AND :p_to_date

     AND ppf.employee_number = :p_employee_number

--in(34000/*1546014859,14666,35343,15201,15202*/)

GROUP BY ppf.full_name,

         ppa.time_period_id,

         effective_date,

         ppf.employee_number,

         ppf.person_id,                            --PD.SEGMENT5 ,PD.SEGMENT6,

         tp.period_name

ORDER BY ppa.effective_date