PSP Organization Default Usage Encumbrance

select 
A.default_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_PSPENCDU_XMLP_PKG.cf_1formula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' )) CF_currency_format, 
	PSP_PSPENCDU_XMLP_PKG.cf_currency_total_dspformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' ), :CF_currency_format, :CS_currency_total) CF_currency_total_dsp, 
	PSP_PSPENCDU_XMLP_PKG.cf_org_total_dspformula(:CS_ORG_TOTAL, :CF_currency_format) CF_org_total_dsp, 
	PSP_PSPENCDU_XMLP_PKG.cf_org_nameformula(A.expenditure_organization_id) CF_org_name, 
	PSP_PSPENCDU_XMLP_PKG.cf_pay_totalformula(:CS_pay_total, :CF_currency_format) CF_pay_total, 
	PSP_PSPENCDU_XMLP_PKG.cf_award_numberformula(A.award_id) CF_award_number, 
	PSP_PSPENCDU_XMLP_PKG.cf_gl_descformula(A.gl_code_combination_id) CF_gl_desc, 
	PSP_PSPENCDU_XMLP_PKG.cf_project_numberformula(A.project_id) CF_project_number, 
	PSP_PSPENCDU_XMLP_PKG.cf_charging_instructionsformul() CF_Charging_instructions, 
	PSP_PSPENCDU_XMLP_PKG.cf_suspense_org_accountformula(A.gl_code_combination_id, PSP_PSPENCDU_XMLP_PKG.cf_project_numberformula(A.project_id), PSP_PSPENCDU_XMLP_PKG.cf_award_numberformula(A.award_id), PSP_PSPENCDU_XMLP_PKG.cf_task_nameformula(A.task_id, A.project_id), PSP_PSPENCDU_XMLP_PKG.cf_org_nameformula(A.expenditure_organization_id) , A.expenditure_type) CF_default_org_account, 
	PSP_PSPENCDU_XMLP_PKG.cf_task_nameformula(A.task_id, A.project_id) CF_task_number, 
	PSP_PSPENCDU_XMLP_PKG.cf_enc_amount_dspformula(:CS_Enc_amount, :CF_currency_format) CF_enc_amount_dsp, 
	PSP_PSPENCDU_XMLP_PKG.cf_employee_numberformula(A.person_id) CF_employee_number, 
	PSP_PSPENCDU_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.default_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.default_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.default_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,1 ASC,9 ASC,10 ASC,11 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 , 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 , 22