PSP Encumbrance History by Organization

Description
Categories: BI Publisher, Human Resources
Application: Labor Distribution
Source: PSP: Encumbrance History by Organization Report (XML)
Short Name: PSPENORG_XML
DB package: PSP_PSPENORG_XMLP_PKG
SELECT 	 pelh.payroll_id,
	hrou.name,
	ppf.full_name,
	paf.assignment_number,
	pelh.enc_element_type_id,
	pelh.hierarchy_code  	Charge_Type,
	pelh.gl_code_combination_id,
	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) enc_amount,
                papf.payroll_name,
                petf.element_name,
	DECODE(pec.uom, 'M', currency.currency_code, 'STAT') currency_code, 
	PSP_PSPENORG_XMLP_PKG.cf_currency_formatformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' )) CF_currency_format, 
--	PSP_PSPENORG_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_PSPENORG_XMLP_PKG.cf_org_total_dspformula(:CS_org_total, :CF_currency_format) CF_org_total_dsp, 
--	PSP_PSPENORG_XMLP_PKG.cf_pay_total_dispformula(:CS_pay_total, :CF_currency_format) CF_pay_total_disp, 
	PSP_PSPENORG_XMLP_PKG.cf_charging_instformula(pelh.gl_code_combination_id, Pelh.project_id, Pelh.task_id, Pelh.award_id, Pelh.expenditure_organization_id, Pelh.expenditure_type) CF_charging_inst, 
--	PSP_PSPENORG_XMLP_PKG.cf_enc_amount_dspformula(SUM ( pelh.encumbrance_amount ), :CF_currency_format) CF_enc_amount_dsp, 
	PSP_PSPENORG_XMLP_PKG.cf_awd_end_dtformula(Pelh.award_id) CF_awd_end_dt, 
	PSP_PSPENORG_XMLP_PKG.cf_date_checkformula(PSP_PSPENORG_XMLP_PKG.cf_awd_end_dtformula(Pelh.award_id), least ( least ( :p_end_date , pelh.hierarchy_end_date ) , max ( paf.effective_end_date ) )) CF_Date_Check, 
	PSP_PSPENORG_XMLP_PKG.cf_enc_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_enc_date_range
FROM 	psp_enc_lines_history pelh,
	Per_all_people_f ppf,
	Per_all_assignments_f paf ,
	hr_all_organization_units hrou,
	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        	pelh.enc_summary_line_id =  pesl.enc_summary_line_id
AND	pelh.assignment_id = paf.assignment_id
AND	paf.organization_id = hrou.organization_id
AND	paf.person_id = ppf.person_id
AND	pelh.person_id = paf.person_id
AND	pec.enc_control_id = pesl.enc_control_id
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
 &p_organization_id
&p_payroll_id
AND  pelh.encumbrance_date  between :p_begin_date and :p_end_date
AND  pelh.encumbrance_date between petf.effective_start_date and petf.effective_end_date
AND  pelh.enc_element_type_id = petf.element_type_id
AND  pelh.payroll_id = papf.payroll_id
AND   pelh.encumbrance_date between papf.effective_start_date and papf.effective_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,
		hrou.name,
		ppf.full_name,
		paf.assignment_number,
		pelh.enc_element_type_id,
		pelh.hierarchy_code,
		pelh.gl_code_combination_id,
		Pelh.project_id,
		pelh.award_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 18 ASC,2 ASC,16 ASC,1 ASC , 17 , 2 , 16 , 3 , 4 , 17 , 13 , 14 , 6
Parameter Name SQL text Validation
PSP_REPORT_TYPE2
 
PSP_REPORT_TYPE
 
p_set_of_books_id
 
Number
p_business_group_id
 
Number
End Date
 
Date
Begin Date
 
Date
Payroll Set
 
LOV Oracle
Organization Set
 
LOV Oracle