PSP Encumbrance Suspense Charge

select
A.suspense_org_account_id,
A.gl_code_combination_id,
 A.person_id,
A.assignment_id,
A.enc_element_type_id,
sum(A.encumbrance_amount) encumbrance_amount ,
A.award_id,
 A.task_id ,
A.expenditure_type,
A.expenditure_organization_id,
A.project_id,
greatest(greatest(:p_begin_date,a.hierarchy_start_date ) ,min(b.effective_start_date)) enc_start_date,
least( least(:p_end_date,a.hierarchy_end_date ), max(b.effective_end_date)) enc_end_date,
a.payroll_id,
pre.error_message suspense_reason_code,
fnd.meaning  Charge_type,
petf.element_name,
b.assignment_number,
papf.full_name,
pay.payroll_name ,
hru.name,
DECODE(pec.uom, 'M', currency.currency_code, 'STAT') currency_code,
--	PSP_PSPENCSC_XMLP_PKG.cf_currency_total_dspformula(:CS_currency_total, :CF_currency_format) CF_currency_total_dsp,
	PSP_PSPENCSC_XMLP_PKG.cf_currency_formatformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' )) CF_currency_format,
	PSP_PSPENCSC_XMLP_PKG.susp_organizationformula(A.suspense_org_account_id) CF_susp_organization,
--	PSP_PSPENCSC_XMLP_PKG.cf_org_total_dspformula(:CS_ORG_TOTAL, :CF_currency_format) CF_org_total_dsp,
	PSP_PSPENCSC_XMLP_PKG.cf_org_nameformula(A.expenditure_organization_id) CF_org_name,
--	PSP_PSPENCSC_XMLP_PKG.cf_pay_totalformula(:CS_pay_total, :CF_currency_format) CF_pay_total,
	PSP_PSPENCSC_XMLP_PKG.cf_award_numberformula(A.award_id) CF_award_number,
	PSP_PSPENCSC_XMLP_PKG.cf_gl_descformula(A.gl_code_combination_id) CF_gl_desc,
	PSP_PSPENCSC_XMLP_PKG.cf_project_numberformula(A.project_id) CF_project_number,
	PSP_PSPENCSC_XMLP_PKG.cf_charging_instructionsformul() CF_Charging_instructions,
	PSP_PSPENCSC_XMLP_PKG.cf_suspense_org_accountformula(A.gl_code_combination_id, PSP_PSPENCSC_XMLP_PKG.cf_project_numberformula(A.project_id), PSP_PSPENCSC_XMLP_PKG.cf_award_numberformula(A.award_id), PSP_PSPENCSC_XMLP_PKG.cf_task_nameformula(A.task_id, A.project_id), PSP_PSPENCSC_XMLP_PKG.cf_org_nameformula(A.expenditure_organization_id), A.expenditure_type) CF_suspense_org_account,
	PSP_PSPENCSC_XMLP_PKG.cf_task_nameformula(A.task_id, A.project_id) CF_task_number,
--	PSP_PSPENCSC_XMLP_PKG.cf_enc_amount_dspformula(:CS_Enc_amount, :CF_currency_format) CF_enc_amount_dsp,
	PSP_PSPENCSC_XMLP_PKG.cf_employee_numberformula(A.person_id) CF_employee_number
--	PSP_PSPENCSC_XMLP_PKG.cf_emp_total_dspformula(:CS_EMP_TOTAL, :CF_currency_format) CF_emp_total_dsp
from psp_enc_lines_HISTORY a , per_all_assignments_f B, fnd_lookup_values_vl fnd,
 psp_report_errors pre,pay_element_types_f petf,
per_all_people_f papf,pay_all_payrolls_f pay,
hr_organization_units hru , psp_organization_accounts pao,
	psp_enc_controls pec,
	(SELECT psp_general.get_currency_code(:p_business_group) currency_code FROM DUAL) currency
 where A.suspense_org_account_id is not null
 AND A.change_flag in ('U' , 'N')
 And A.enc_start_date <= :p_end_date
 And A.enc_end_date >= :p_begin_date
 And A.assignment_id   = B.assignment_id
 AND fnd.lookup_type = 'PSP_SCHEDULE_TYPES'
and     fnd.enabled_flag = 'Y'
and  sysdate between fnd.start_date_active and nvl(fnd.end_date_active, fnd_date.canonical_to_date('4000/01/31'))
and a.enc_start_date <= nvl(hru.date_to, apps.fnd_date.canonical_to_date('4000/01/31'))
    and  a.enc_end_date >=  hru.date_from
 AND  hru.organization_id = pao.organization_id
 AND  pao.organization_account_id =  A.suspense_org_account_id
AND   FND.LOOKUP_CODE = a.HIERARCHY_CODE
AND  pre.value1 (+) = a.payroll_id
and  pre.PAYROLL_ACTION_ID (+) = A.PAYROLL_ACTION_ID
and  pre.SOURCE_ID(+) = A.assignment_id
And  pre.value3 (+)= a.enc_element_type_id
AND  pre.INFORMATION6 (+) = a.hierarchy_code
and  nvl(fnd_date.canonical_to_date(pre.information4) , HIERARCHY_END_DATE )<=   HIERARCHY_END_DATE
and  nvl(fnd_date.canonical_to_date(pre.information5),HIERARCHY_start_DATE) >=   HIERARCHY_start_DATE
AND  a.enc_start_date <= petf.effective_end_date
     and  a.enc_end_date >=  petf.effective_start_date
AND  a.enc_start_date <= papf.effective_end_date
     and  a.enc_end_date >=  papf.effective_start_date
AND  a.enc_start_date <= pay.effective_end_date
     and  a.enc_end_date >=  pay.effective_start_date
AND	a.enc_start_date <= b.effective_end_date
AND	a.enc_end_date >=  b.effective_start_date
AND  	pay.payroll_id = a.payroll_id
AND	b.person_id = papf.person_id
AND	a.person_id = b.person_id
AND	a.enc_element_type_id = petf.element_type_id
ANd	pec.enc_control_id = a.enc_control_id
&p_organizations
&p_payroll_id
GROUP BY A.business_group_id,A.set_of_books_id ,A.payroll_id,A.person_id,A.assignment_id,
A.enc_element_type_id,A.suspense_org_account_id,A.project_id,A.expenditure_organization_id,
A.expenditure_type,A.task_id,A.award_id,A.gl_code_combination_id,pre.error_message,
fnd.meaning,
a.hierarchy_start_date,
a.hierarchy_end_date,
petf.element_name,
b.assignment_number,
papf.full_name,
pay.payroll_name,
hru.name,
DECODE(pec.uom, 'M', currency.currency_code, 'STAT')
/*ORDER BY 22 ASC,21 ASC,20 ASC,14 ASC,2 ASC,9 ASC,10 ASC,11 ASC,1 ASC,7 ASC,8 ASC,19 ASC,18 ASC,17 ASC,3 ASC,4 ASC,16 ASC,5 ASC,6 ASC,13 ASC,12 ASC , 22 , hru.name , A.gl_code_combination_id , A.project_id , A.task_id , A.award_id , A.expenditure_type , A.expenditure_organization_id , pay.payroll_name , papf.full_name , b.assignment_number , petf.element_name , 12 , 13*/
ORDER BY 22,21,20,14,2,19,18,17,3,4,16,5,6,13,12,15