SELECT * FROM ( 
SELECT
	pf.person_number,
	piv.name, 
ABS(prrv.result_value)
 result_value
	
	FROM
		per_person_secured_list_v pf,	
		(SELECT PAA1.*,
		RANK() OVER (PARTITION BY paa1.PERSON_ID ORDER BY paa1.EFFECTIVE_START_DATE DESC) ASSIGN_RANK
		FROM per_all_assignments_f paa1
		WHERE 1=1
                AND paa1.SYSTEM_PERSON_TYPE = 'EMP'
		AND paa1.assignment_status_type = 'ACTIVE')paa,
		pay_pay_relationships_dn prel,
		pay_rel_groups_dn payrel,
		pay_assigned_payrolls_dn papd,
		pay_payroll_secured_list_v pp,
		pay_payroll_actions ppa,
		pay_payroll_rel_actions ppra,
		pay_run_results prr,
		pay_input_values_vl piv,
		pay_run_result_values prrv,
		pay_element_types_vl ele,
		pay_time_periods ptp,
		pay_ele_classifications_vl pec

	WHERE 1=1
AND paa.ASSIGN_RANK=1
		AND pf.person_id=paa.person_id 
		AND pf.person_id=prel.person_id
		AND prel.payroll_relationship_id = payrel.payroll_relationship_id
		AND prel.payroll_relationship_id = ppra.payroll_relationship_id
		AND payrel.parent_rel_group_id = papd.payroll_term_id
		AND payrel.assignment_id = paa.assignment_id
		AND papd.payroll_id = pp.payroll_id
		AND papd.payroll_id = ppa.payroll_id
		AND ppa.payroll_action_id = ppra.payroll_action_id
		AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id
		AND prr.run_result_id = prrv.run_result_id
		AND prr.enterprise_id = prrv.enterprise_id
		AND prr.element_type_id = ele.element_type_id
		AND prr.element_type_id = piv.element_type_id
		AND ptp.time_period_id = ppa.earn_time_period_id --payroll period for employee action
		AND ptp.period_category = 'E' --earning period category
		AND piv.input_value_id = prrv.input_value_id --input value is a out put parameter for each element default is pay value
		AND UPPER(ele.element_name)='ANNUAL LEAVE PROVISION'
		AND UPPER(piv.name) IN ('ENDING ACCRUAL BALANCE','OPENNING ACCRUAL BALANCE','PAY VALUE','GROSS SALARY','VACATION TRANSACTIONS')
		AND UPPER(pec.classification_name) ='INFORMATION'
		AND ppra.action_status = 'C' --payroll run completed
		AND ppa.action_type in ('R', 'Q') --types of payroll run(payroll calculation r, quick pay q)
		AND ppa.action_status = 'C' --payroll run completed
		AND ppra.retro_component_id IS NULL --retro payment
		AND ele.classification_id = pec.classification_id
		AND payrel.group_type = 'A'
		
		AND ppa.effective_date  BETWEEN  ptp.start_date AND  ptp.end_date
		AND  ppa.effective_date BETWEEN TRUNC(:p_calculation_date, 'MM') AND LAST_DAY(:p_calculation_date)
			
		AND ppa.effective_date BETWEEN pf.effective_start_date AND LAST_DAY(pf.effective_end_date)
		AND ppa.effective_date BETWEEN prel.start_date AND prel.end_date
		AND ppa.effective_date BETWEEN payrel.start_date AND payrel.end_date
		AND ppa.effective_date BETWEEN papd.start_date AND papd.end_date
		AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
		AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
		AND ppa.effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
	AND ((paa.legal_entity_id IN (:p_leg_id)) OR (COALESCE(:p_leg_id,NULL) IS NULL))

ORDER BY pf.person_number
)PIVOT (
SUM(result_value)
FOR name IN('Openning Accrual Balance','Ending Accrual Balance','Pay Value','Gross Salary','Vacation Transactions')
)