PAY Payments Summary Report (GB)

select 
       'X' Dummy_Break, 
       substr(ppttl.payment_type_name,1,14) Payment_Method_Type, 
       substr(ppttl.payment_type_name,1,14) || ' Totals' Payment_Method_Type_Total, 
       substr(popmftl.org_payment_method_name,1,35) Name, 
       substr(pea.segment3,1,6) Source_Sort_Code, 
       substr(pea.segment4,1,12) Account_Number, 
       substr(pea.segment5,1,20) Account_Name,
       sum(to_number(ppp.value)) Amount,
       count(ppp.value) Total_Assignments_Paid,
	PAY_PAYGBPAY_XMLP_PKG.CP_Running_Total_Number_Paid_p CP_Running_Total_Number_Paid,
	PAY_PAYGBPAY_XMLP_PKG.CP_Running_Total_Amount_Paid_p CP_Running_Total_Amount_Paid, 
	PAY_PAYGBPAY_XMLP_PKG.cf_calculate_running_totalform(:CS_Running_Total_Amount_Paid) CF_Running_Total_Amount_Paid, 
	PAY_PAYGBPAY_XMLP_PKG.cf_running_total_number_paidfo(count ( ppp.value )) CF_Running_Total_Number_Paid
from   pay_payroll_actions    ppa,
       pay_assignment_actions paa,
       pay_pre_payments       ppp,
       pay_org_payment_methods_f    popmf,
       pay_org_payment_methods_f_tl popmftl,
       pay_payment_types      ppt,
       pay_payment_types_tl   ppttl,
       pay_external_accounts  pea
where  ppt.payment_type_id = ppttl.payment_type_id
and    ppttl.language = userenv('LANG')
and    popmf.org_payment_method_id = popmftl.org_payment_method_id
and    popmftl.language = userenv('LANG')
and    ppa.payroll_action_id = paa.payroll_action_id
&P_CONSOLIDATION_SET_LINE
and    ppa.action_type in ('U', 'P')
and    ppa.action_status = 'C'
and    ppa.payroll_id = :P_PAYROLL_ID
and    ppa.EFFECTIVE_DATE between :P_PAYMENT_START_DATE
                              and :P_PAYMENT_END_DATE
and    ppa.effective_date between popmf.effective_start_date and popmf.effective_end_date
and    paa.assignment_action_id = ppp.assignment_action_id
and    ppp.org_payment_method_id = popmf.org_payment_method_id
and    popmf.payment_type_id = ppt.payment_type_id
and    popmf.external_account_id = pea.external_account_id
group by ppttl.payment_type_name, popmftl.org_payment_method_name, pea.segment3,
         pea.segment4, pea.segment5, ppa.consolidation_set_id,
         ppa.effective_date, 
         popmf.effective_start_date, popmf.effective_end_date
order by 1,3,2,4,5,6,7,8,9