PSP Reconciliation between pre-generated distribution lines and summary lines

SELECT	person_id,
	assignment_id,
	gl_code_combination_id,
	project_id,
	task_id,
	award_id,
	expenditure_type,
	expenditure_organization_id,
	sum(decode(dr_cr_flag, 'C', summary_amount, 0)) sl_credit_amount,
	sum(decode(dr_cr_flag, 'D', summary_amount, 0)) sl_debit_amount,
	currency_code, 
	PSP_PSPRCPGD_XMLP_PKG.cf_currency_codeformula(currency_code) CF_currency_code, 
	PSP_PSPRCPGD_XMLP_PKG.cf_currency_formatformula(currency_code) CF_currency_format, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_d_total_dspformula(:CS_sum_sl_d_total, :CF_currency_format) CF_sum_sl_d_total_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_c_total_dspformula(:CS_sum_sl_c_total, :CF_currency_format) CF_sum_sl_c_total_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_d_total_dspformula(:CS_sum_dl_d_total, :CF_currency_format) CF_sum_dl_d_total_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_c_total_dspformula(:CS_sum_dl_c_total, :CF_currency_format) CF_sum_dl_c_total_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_currency_totalform(:CS_sum_dl_d_total, :CS_sum_sl_d_total, :CS_sum_dl_c_total, :CS_sum_sl_c_total) CF_mismatch_currency_total, 
	PSP_PSPRCPGD_XMLP_PKG.cf_person_nameformula(person_id) CF_person_name, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_d_person_dspformula(:sum_sl_d_person, :CF_currency_format) CF_sum_sl_d_person_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_c_person_dspformula(:sum_sl_c_person, :CF_currency_format) CF_sum_sl_c_person_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_d_person_dspformula(:sum_dl_d_person, :CF_currency_format) CF_sum_dl_d_person_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_c_person_dspformula(:sum_dl_c_person, :CF_currency_format) CF_sum_dl_c_person_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_personformula(:sum_dl_d_person, :sum_sl_d_person, :sum_dl_c_person, :sum_sl_c_person) CF_mismatch_person, 
	PSP_PSPRCPGD_XMLP_PKG.cf_assignment_numberformula(assignment_id) CF_assignment_number, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_d_assg_dspformula(:sum_sl_d_assg, :CF_currency_format) CF_sum_sl_d_assg_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_c_assg_dspformula(:sum_sl_c_assg, :CF_currency_format) CF_sum_sl_c_assg_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_d_assg_dspformula(:sum_dl_d_assg, :CF_currency_format) CF_sum_dl_d_assg_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_c_assg_dspformula(:sum_dl_c_assg, :CF_currency_format) CF_sum_dl_c_assg_dsp, 
PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_assgformula(:sum_dl_d_assg, :sum_sl_d_assg, :sum_dl_c_assg, :sum_sl_c_assg) CF_mismatch_assg, 
	PSP_PSPRCPGD_XMLP_PKG.cf_sl_debit_amount_dspformula(sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), :CF_currency_format) CF_sl_debit_amount_dsp, 
	PSP_PSPRCPGD_XMLP_PKG.cf_amt_sl_cformula(gl_code_combination_id, sum ( decode ( dr_cr_flag , 'C' , summary_amount , 0 ) )) CF_amt_sl_c, 
	PSP_PSPRCPGD_XMLP_PKG.cf_amt_sl_c_dspformula(:CF_amt_sl_c, :CF_currency_format) CF_amt_sl_c_dsp, 
--	PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_dformula(gl_code_combination_id) CF_amt_dl_d, 
	PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_dformula(person_id,assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id ) CF_amt_dl_d, 
	PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_d_dspformula(:CF_amt_dl_d, :CF_currency_format) CF_amt_dl_d_dsp, 
--	PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_eltformula(:CF_amt_dl_d, sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), :CF_amt_sl_c) CF_mismatch_elt, 
PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_eltformula(PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_dformula(person_id,assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id ), sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), PSP_PSPRCPGD_XMLP_PKG.cf_amt_sl_cformula(gl_code_combination_id, sum ( decode ( dr_cr_flag , 'C' , summary_amount , 0 ) ))) CF_mismatch_elt, 
--	PSP_PSPRCPGD_XMLP_PKG.cf_charging_instructionsformu(project_id, task_id, award_id, expenditure_organization_id, gl_code_combination_id) CF_Charging _Instructions, 
	PSP_PSPRCPGD_XMLP_PKG.cf_charging_instructionsformu(project_id, task_id, award_id, expenditure_organization_id, gl_code_combination_id, expenditure_type) CF_Charging_Instructions, 
	PSP_PSPRCPGD_XMLP_PKG.cf_credit_amount_pgdl_dspformu(:CF_currency_format) CF_credit_amount_pgdl_dsp,
	PSP_PSPRCPGD_XMLP_PKG.CP_suspense_p CP_suspense,
	PSP_PSPRCPGD_XMLP_PKG.CP_credit_amount_pgdl_p CP_credit_amount_pgdl
FROM 	psp_summary_lines psl,
	psp_payroll_controls ppc
WHERE	psl.status_code	= 'A'
AND 	psl.payroll_control_id = ppc. payroll_control_id
AND	ppc.source_type	= :p_source_type
AND	payroll_source_code	= :p_source_code
AND	ppc.time_period_id	= :p_time_period_id
AND	(batch_name = :p_batch_name OR batch_name IS NULL )
GROUP BY
	currency_code,
	person_id,
	assignment_id,
	gl_code_combination_id,
	project_id,
	task_id,
	award_id,
	expenditure_type,
--	expenditure_organization_id;
	expenditure_organization_id
	--added
	order by currency_code,
	person_id,
	assignment_id,
	gl_code_combination_id,
	project_id,
	task_id,
	award_id,
	expenditure_type,
	expenditure_organization_id