/* This Query used to retrieve the Run Result Values for a Single Element or Group of Elements.We can use it for Querying the Information Element also Change the details Accordingly. Eg: How Employee paid by an Element in a Period*/
SELECT ppa.effective_date P_Date,
papf.employee_number EMP_No,
papf.person_id PersonID,
papf.full_name EMP_NAME,
petf.element_name ElementName,
pivf.NAME INNAME,
prrv.result_value VResult
FROM per_all_people_f papf,
per_all_assignments_f paaf,
pay_input_values_f pivf,
pay_run_results prr,
pay_run_result_values prrv,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f petf,
pay_element_classifications pec,
per_time_periods ptp,
pay_all_payrolls_f papsf
WHERE papf.person_id = paaf.person_id
AND pivf.input_value_id = prrv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND paa.assignment_id = paaf.assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pec.classification_id = petf.classification_id
AND prr.element_type_id = petf.element_type_id
AND ptp.time_period_id = ppa.time_period_id
--AND paaf.business_group_id=9112
AND pec.classification_name IN ('Earnings', 'Involuntary Deductions', 'Involuntary Deductions', 'Pre Social Insurance Deductions', 'Pre-Tax Deductions', 'Tax Deductions', 'Voluntary Deductions', 'Voluntary Deductions', 'Information')
AND ppa.action_type IN ('R', 'Q')
AND papsf.payroll_id = paaf.payroll_id
-- and papf.person_id = 1477
AND ppa.date_earned BETWEEN papsf.effective_start_date AND papsf.effective_end_date
AND ppa.date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ppa.date_earned BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND ppa.date_earned BETWEEN petf.effective_start_date AND petf.effective_end_date
AND pivf.NAME = 'Pay Value' /* Add Input Values if needed*/
AND petf.element_name IN ('Basic Salary') /* Add Elements if needed*/
--AND TO_CHAR (ppa.effective_date, 'MON-YYYY') = 'MAR-2016'
AND ppa.effective_date BETWEEN '01-JAN-2016' AND '31-OCT-2016';