EBS: Payroll Query

To get the Payroll details .

/* Formatted on 01/12/2015 11:57:50 (QP5 v5.126.903.23003) */
  SELECT   employee_number,
           full_name,
           ptp.period_name,
           pec.classification_name,
           petf.element_name,
           petf.reporting_name,
           DECODE (petf.processing_type,
                   'N',
                   'Non-Recurring',
                   'R',
                   'Recurring')
              processing_type,
           pivf.NAME,
           prrv.result_value pay_value
    FROM   pay_run_result_values prrv,
           pay_run_results prr,
           pay_input_values_f pivf,
           pay_element_classifications pec,
           pay_element_types_f petf,
           per_all_people_f papf,
           per_all_assignments_f paaf,
           pay_assignment_actions paa,
           pay_payroll_actions ppa,
           pay_all_payrolls_f papfp,
           per_time_periods ptp
   WHERE       prrv.input_value_id = pivf.input_value_id
           AND prrv.run_result_id = prr.run_result_id
           AND pivf.NAME = 'Pay Value'
           AND ptp.end_date BETWEEN pivf.effective_start_date
                                AND  pivf.effective_end_date
           AND pivf.element_type_id = petf.element_type_id
           AND pec.classification_name =
                 NVL (:p_classification_name, pec.classification_name)
           AND pec.classification_id = petf.classification_id
           AND petf.element_name = NVL (:p_element_name, petf.element_name)
           AND ptp.end_date BETWEEN petf.effective_start_date
                                AND  petf.effective_end_date
           AND prr.element_type_id = petf.element_type_id
           AND prr.assignment_action_id = paa.assignment_action_id
           AND papf.employee_number =
                 NVL (:p_employee_number, papf.employee_number)
           AND ptp.end_date BETWEEN papf.effective_start_date
                                AND  papf.effective_end_date
           AND papf.person_id = paaf.person_id
           AND paaf.payroll_id = ppa.payroll_id
           AND ppa.effective_date BETWEEN paaf.effective_start_date
                                      AND  paaf.effective_end_date
           AND paaf.primary_flag = 'Y'
           AND paaf.business_group_id = ppa.business_group_id
           AND paaf.business_group_id = ppa.business_group_id
           AND paaf.assignment_id = paa.assignment_id
           AND paa.payroll_action_id = ppa.payroll_action_id
           AND ( (paa.source_action_id IS NULL AND ppa.action_type = 'R')
                OR (paa.source_action_id IS NOT NULL AND ppa.action_type = 'Q'))
           AND ppa.action_status = 'C'
           AND ppa.action_type IN ('Q', 'R')
           AND ppa.business_group_id = :p_business_group_id
           AND ppa.payroll_id = ptp.payroll_id
           AND ppa.time_period_id = ptp.time_period_id
           AND ptp.end_date BETWEEN papfp.effective_start_date
                                AND  papfp.effective_end_date
           AND papfp.payroll_name = NVL (:p_payroll_name, papfp.payroll_name)
           AND papfp.payroll_id = ptp.payroll_id
           AND ptp.period_name = NVL (:p_period_name, ptp.period_name)
           AND pec.classification_name IN ('Earnings', 'Voluntary Deductions')
ORDER BY   1,
           2,
           3,
           4,
           5;




Enjoy and Stay Well !! :)