Query to Get Net Run Result from Payroll or Quick Pay Run

  SELECT per.employee_number,

         per.full_name,

         pcak_org.segment2 location,

         pcak_org.segment3 sub_location,

         DECODE (ppa.action_type,  'Q', 'Quick Pay',  'R', 'Run') action_type,

         SUM (

            CASE

               WHEN UPPER (pec.classification_name) = 'EARNINGS'

               THEN

                  TO_NUMBER (NVL (prrv.result_value, 0))

               ELSE

                  0

            END)

            gross_earnings,

         SUM (

            CASE

               WHEN UPPER (pec.classification_name) IN

                       ('VOLUNTARY DEDUCTIONS',

                        'INVOLUNTARY DEDUCTIONS',

                        'SOCIAL INSURANCE')

               THEN

                  TO_NUMBER (NVL (prrv.result_value, 0))

               ELSE

                  0

            END)

            deductions,

         SUM (

            CASE

               WHEN UPPER (pec.classification_name) = 'EARNINGS'

               THEN

                  TO_NUMBER (NVL (prrv.result_value, 0))

               ELSE

                  0

            END)

         - SUM (

              CASE

                 WHEN UPPER (pec.classification_name) IN

                         ('VOLUNTARY DEDUCTIONS',

                          'INVOLUNTARY DEDUCTIONS',

                          'SOCIAL INSURANCE')

                 THEN

                    TO_NUMBER (NVL (prrv.result_value, 0))

                 ELSE

                    0

              END)

            net_earnings

    FROM pay_payroll_actions ppa,

         pay_payrolls_f ppf,

         pay_assignment_actions paa,

         per_all_assignments_f paaf,

         per_all_people_f per,

         pay_run_results prr,

         pay_run_result_values prrv,

         pay_input_values_f pivf,

         pay_element_types_f petf,

         pay_element_classifications pec,

         hr_all_organization_units haou,

         pay_cost_allocation_keyflex pcak_org

   WHERE ppf.payroll_id = ppa.payroll_id

         AND ppa.effective_date BETWEEN ppf.effective_start_date

                                    AND ppf.effective_end_date

         AND ppa.payroll_action_id = paa.payroll_action_id

         AND paaf.assignment_id = paa.assignment_id

         AND ppa.effective_date BETWEEN paaf.effective_start_date

                                    AND paaf.effective_end_date

         AND ppa.action_type = NVL (:l_action_type, ppa.action_type)

         AND ppa.effective_date BETWEEN :l_start_date AND :l_end_date

         AND per.person_id = paaf.person_id

         AND prr.assignment_action_id = paa.assignment_action_id

         AND prr.run_result_id = prrv.run_result_id

         AND pivf.input_value_id = prrv.input_value_id

         AND prr.element_type_id = petf.element_type_id

         AND pec.classification_id = petf.classification_id

         AND UPPER (pivf.name) = 'PAY VALUE'

         AND ppa.effective_date BETWEEN per.effective_start_date

                                    AND per.effective_end_date

         AND ppa.effective_date BETWEEN petf.effective_start_date

                                    AND petf.effective_end_date

         AND ppa.effective_date BETWEEN pivf.effective_start_date

                                    AND pivf.effective_end_date

         AND paaf.organization_id = haou.organization_id

         AND haou.cost_allocation_keyflex_id =

                pcak_org.cost_allocation_keyflex_id

         AND ppa.effective_date BETWEEN haou.date_from

                                    AND NVL (haou.date_to,

                                             hr_general.end_of_time)

GROUP BY per.employee_number,

         per.full_name,

         ppa.action_type,

         pcak_org.segment2,

         pcak_org.segment3