PSP Assignments processed by Create and Update Encumbrance lines

SELECT		ppf.full_name,
		paf.assignment_number,
		paf.assignment_id,
		pecah.change_type,
		Pecah.action_type,
		Pecah.reference_id, 
	PSP_PSPENASG_XMLP_PKG.cf_change_sourceformula(pecah.change_type) CF_change_source, 
	PSP_PSPENASG_XMLP_PKG.cf_org_nameformula(Pecah.reference_id, paf.assignment_id, Pecah.action_type, pecah.change_type) CF_ORG_NAME, 
	PSP_PSPENASG_XMLP_PKG.cf_element_nameformula(Pecah.reference_id, Pecah.action_type, pecah.change_type) CF_element_name
FROM		psp_enc_changed_asg_history pecah,
		Per_all_people_f ppf,
		Per_all_assignments_f paf
WHERE		pecah.assignment_id 	= paf.assignment_id
AND		paf.person_id 		= ppf.person_id
AND (trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
or (trunc(sysdate) < (select min(effective_start_date) from per_all_people_f ppf2 where
ppf2.person_id=ppf.person_id)))
AND	(	trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
OR  (trunc(sysdate) < (select min(paf2.effective_start_date) from per_all_assignments_f paf2 where
paf2.assignment_id=paf.assignment_id)))
AND 		pecah.request_id		= :p_request_id
AND		(NVL(pecah.action_type, pecah.ROWID),NVL(pecah.reference_id,0))      
		= (SELECT NVL(pecah2.action_type,pecah2.ROWID),NVL(pecah2.reference_id,0)
				FROM    psp_enc_changed_asg_history pecah2
				WHERE pecah2.request_id = :p_request_id
				AND	      pecah2.change_type = pecah.change_type
				AND	pecah2.assignment_id = pecah.assignment_id
					     AND       rownum = 1)
GROUP BY 	pecah.change_type,
		ppf.full_name,
		Paf.assignment_number,		
		paf.assignment_id,		
		pecah.action_type,
		pecah.reference_id
		order by 7,4,1,3,2,5,6