PSP Reconciliation between distribution lines and summary

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_PSPRCDLS_XMLP_PKG.cf_currency_formatformula(currency_code) CF_currency_format,
	PSP_PSPRCDLS_XMLP_PKG.cf_currency_codeformula(currency_code) CF_currency_code,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_d_total_dspformula(:CS_SUM_SL_D_TOTAL, :CF_currency_format) CF_sum_sl_d_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_c_total_dspformula(:CS_SUM_SL_C_TOTAL, :CF_currency_format) CF_sum_sl_c_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_d_total_dspformula(:CS_SUM_DL_D_TOTAL, :CF_currency_format) CF_sum_dl_d_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_c_total_dspformula(:CS_SUM_DL_C_TOTAL, :CF_currency_format) CF_sum_dl_c_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_mismatch_total_dspformula(:CS_sum_dl_d_total, :CS_sum_sl_d_total, :CS_sum_dl_c_total, :CS_sum_sl_c_total) CF_mismatch_total_dsp,*/
	PSP_PSPRCDLS_XMLP_PKG.cf_person_nameformula(person_id) CF_person_name,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_d_person_dspformula(:SUM_SL_D_PERSON, :CF_currency_format) CF_sum_sl_d_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_c_person_dspformula(:SUM_SL_C_PERSON, :CF_currency_format) CF_sum_sl_c_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_d_person_dspformula(:SUM_DL_D_PERSON, :CF_currency_format) CF_sum_dl_d_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_c_person_dspformula(:SUM_DL_C_PERSON, :CF_currency_format) CF_sum_dl_c_person_dsp,
	PSP_PSPRCDLS_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_PSPRCDLS_XMLP_PKG.cf_assignment_numberformula(assignment_id) CF_assignment_number,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_d_assg_dspformula(:SUM_SL_D_ASSG, :CF_currency_format) CF_sum_sl_d_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_c_assg_dspformula(:SUM_SL_C_ASSG, :CF_currency_format) CF_sum_sl_c_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_d_assg_dspformula(:SUM_DL_D_ASSG, :CF_currency_format) CF_sum_dl_d_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_c_assg_dspformula(:SUM_DL_C_ASSG, :CF_currency_format, :sum_sl_c_assg) CF_sum_dl_c_assg_dsp,
	PSP_PSPRCDLS_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_PSPRCDLS_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_PSPRCDLS_XMLP_PKG.cf_amt_sl_cformula(gl_code_combination_id, sum ( decode ( dr_cr_flag , 'C' , summary_amount , 0 ) )) CF_AMT_SL_C,
	--PSP_PSPRCDLS_XMLP_PKG.cf_amt_sl_c_dspformula(:CF_amt_sl_c, :CF_currency_format) CF_amt_sl_c_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_charging_instructionsformul(gl_code_combination_id, project_id, task_id, award_id, expenditure_organization_id, expenditure_type) CF_Charging_Instructions,
	PSP_PSPRCDLS_XMLP_PKG.cf_credit_amount_dl_dspformula(:CF_currency_format) CF_credit_amount_dl_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_1formula(currency_code, person_id, assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id) CF_1,
	--PSP_PSPRCDLS_XMLP_PKG.cf_1_dspformula(:CF_1, :CF_currency_format) CF_1_dsp,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_mismatch_lineformula(:CF_1, sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), :CF_amt_sl_c) CF_Mismatch_Line,*/
	PSP_PSPRCDLS_XMLP_PKG.CP_credit_amount_dl_p CP_credit_amount_dl,
	PSP_PSPRCDLS_XMLP_PKG.CP_suspense_p CP_suspense
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
	ORDER BY 11 ASC,1 ASC,2 ASC