PSP Encumbrance History by Award

SELECT 	pelh.payroll_id,
	gaa.award_number,
	ppf.full_name,
	paf.assignment_number,
	pelh.enc_element_type_id,
	pelh.hierarchy_code Charge_Type,
	Pelh.project_id,
	Pelh.expenditure_organization_id,
	Pelh.expenditure_type,
	Pelh.task_id,
	Pelh.award_id,
	greatest(greatest(:p_begin_date,pelh.hierarchy_start_date) ,min(paf.effective_start_date))  enc_begin_date, 
	least(least(:p_end_date,pelh.hierarchy_end_date) ,max(paf.effective_end_date)) enc_end_date, 
	SUM(pelh.encumbrance_amount),
	papf.payroll_name,
	petf.element_name,
	DECODE(pec.uom, 'M', currency.currency_code, 'STAT') currency_code, 
	PSP_PSPENAWD_XMLP_PKG.cf_currency_formatformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' )) CF_currency_format, 
	PSP_PSPENAWD_XMLP_PKG.cf_currency_total_dspformula(:CS_currency_total, :CF_currency_format) CF_currency_total_dsp, 
	PSP_PSPENAWD_XMLP_PKG.cf_award_total_dspformula(:CS_award_total, :CF_currency_format) CF_award_total_dsp, 
	PSP_PSPENAWD_XMLP_PKG.cf_pay_total_dspformula(:CS_pay_total, :CF_currency_format) CF_pay_total_dsp, 
	PSP_PSPENAWD_XMLP_PKG.cf_charging_instformula(Pelh.project_id, Pelh.task_id, Pelh.award_id, Pelh.expenditure_organization_id, Pelh.expenditure_type) CF_charging_inst, 
	--PSP_PSPENAWD_XMLP_PKG.cf_pelh_enc_amount_dspformula(SUM ( pelh.encumbrance_amount ), :CF_currency_format) CF_pelh_enc_amount_dsp,
	SUM ( pelh.encumbrance_amount ) encumbered_amt,
	PSP_PSPENAWD_XMLP_PKG.cf_awd_end_dtformula(Pelh.award_id) CF_awd_end_dt, 
	PSP_PSPENAWD_XMLP_PKG.cf_date_checkformula(:CF_awd_end_dt, least ( least ( :p_end_date , pelh.hierarchy_end_date ) , max ( paf.effective_end_date ) )) CF_Date_Check, 
	PSP_PSPENAWD_XMLP_PKG.cf_end_date_rangeformula(greatest ( greatest ( :p_begin_date , pelh.hierarchy_start_date ) , min ( paf.effective_start_date ) ), least ( least ( :p_end_date , pelh.hierarchy_end_date ) , max ( paf.effective_end_date ) )) CF_end_date_range
FROM 	psp_enc_lines_history pelh,
	Per_all_people_f ppf,
	Per_all_assignments_f paf ,
	gms_awards_all gaa,
	psp_enc_summary_lines pesl,
	pay_all_payrolls_f papf,
	pay_element_types_f petf,
	psp_enc_controls pec,
	(SELECT psp_general.get_currency_code(:p_business_group_id) currency_code FROM DUAL) currency
WHERE 	pelh.status_code <>'L'
AND	pesl.status_code <> 'S'
AND	pec.enc_control_id = pesl.enc_control_id
AND	pesl.enc_summary_line_id = pelh.enc_summary_line_id 
AND	pelh.assignment_id = paf.assignment_id
AND	paf.person_id = ppf.person_id
AND	pelh.person_id = paf.person_id
AND	pelh.payroll_id = papf.payroll_id
AND	pelh.enc_element_type_id = petf.element_type_id
AND	pelh.encumbrance_date between petf.effective_start_date and petf.effective_end_date
AND	pelh.encumbrance_date between papf.effective_start_date AND papf.effective_end_date
AND	pelh.encumbrance_date between ppf.effective_Start_date and ppf.effective_End_date
and	pelh.enc_start_date  <= paf.effective_end_date
AND	pelh.enc_end_date >= paf.effective_start_date
AND		pelh.award_id = gaa.award_id
&p_award_id
&p_payroll_id
AND     pelh.encumbrance_date  BETWEEN :p_begin_date AND :p_end_date
AND     pelh.set_of_books_id = :p_set_of_books_id 
AND     pelh.business_group_id = :p_business_group_id
GROUP BY	pelh.payroll_id, 	
	gaa.award_number,
	Pelh.award_id,
	ppf.full_name,
	paf.assignment_number,
	pelh.enc_element_type_id,
	pelh.hierarchy_code,
	Pelh.project_id,
	Pelh.expenditure_organization_id,
	Pelh.expenditure_type,
	Pelh.task_id,
	papf.payroll_name,
	pelh.hierarchy_start_date,
	pelh.hierarchy_end_date,
	paf.organization_id,
	petf.element_name,
	DECODE(pec.uom, 'M', currency.currency_code, 'STAT')
order by 17,2,15,3,4,16,12,13,6