Oracle Payroll Run Results Discoverer Report

  SELECT    'Period=>'
          || period_name
          || ': Payroll Action ID =>'
          || ppa.payroll_action_id
          || ': Consolidation Set =>'
          || con_set.consolidation_set_name
          || ': Run Date => '
          || ppa.creation_date pay_action,
          pbg.NAME business_group, papf.business_group_id,
          papf.employee_number || ':' || papf.full_name employee,
          papf.employee_number emp_no, papf.full_name emp_name,
          papf.start_date hiredate, hou.NAME org_name, pp.NAME pos_name,
          paaf.assignment_id assignment_id, paa.assignment_action_id,
          ppa.payroll_action_id, ppa.time_period_id, ppa.action_type,
          ppa.effective_date, ppa.payroll_id, ppf.payroll_name,
          ptp.period_name, ptp.end_date, papf.start_date commencement_date,
          petf.element_name, pec.classification_name,
          DECODE (pec.classification_name,
                  'Voluntary Deductions', -TO_NUMBER (prrv.result_value),
                  TO_NUMBER (prrv.result_value)
                 ) result_value,
          ppg.segment1 power_retailer, ppg.segment2 petty_cash_holder,
          ppg.segment3 direct_indirect
     FROM per_all_people_f papf,
          per_all_assignments_f paaf,
          pay_people_groups ppg,
          hr_all_organization_units hou,
          per_positions pp,
          pay_assignment_actions paa,
          pay_payroll_actions ppa,
          per_time_periods ptp,
          pay_run_results prr,
          pay_element_types_f petf,
          pay_input_values_f pivf,
          pay_run_result_values prrv,
          pay_element_classifications pec,
          per_business_groups pbg,
          pay_payrolls_f ppf,
          pay_consolidation_sets con_set
    WHERE ppa.effective_date BETWEEN papf.effective_start_date
                                 AND papf.effective_end_date
      AND papf.person_id = paaf.person_id
      AND paaf.primary_flag = 'Y'
      AND ppa.effective_date BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
      AND paaf.people_group_id = ppg.people_group_id
      AND paaf.organization_id = hou.organization_id
      AND paaf.position_id = pp.position_id(+)
      AND paaf.assignment_id = paa.assignment_id
      AND paa.payroll_action_id = ppa.payroll_action_id
      AND ppa.action_status = 'C'
      AND ppa.payroll_id = paaf.payroll_id
      AND ppa.time_period_id = ptp.time_period_id
      AND ppa.payroll_id = ptp.payroll_id
      AND paa.assignment_action_id = prr.assignment_action_id
      AND prr.element_type_id = petf.element_type_id
      AND petf.element_type_id = pivf.element_type_id
      AND pivf.input_value_id = prrv.input_value_id
      AND prrv.run_result_id = prr.run_result_id
      AND ptp.end_date BETWEEN petf.effective_start_date
                           AND petf.effective_end_date
      AND ptp.end_date BETWEEN pivf.effective_start_date
                           AND pivf.effective_end_date
      AND petf.classification_id = pec.classification_id
      AND pec.classification_name IN
             ('Earnings', 'Voluntary Deductions', 'Social Insurance',
              'Involuntary Deductions', 'Information')
      AND pivf.NAME = 'Pay Value'
      AND prrv.result_value IS NOT NULL
      AND papf.business_group_id = pbg.business_group_id
      AND ppf.payroll_id = ppa.payroll_id
      AND ppa.consolidation_set_id = con_set.consolidation_set_id;