PAY Employee Run Results Summary Report (GB)

select
	 paf.Assignment_Number Assignment_Number
	,paf.Assignment_ID Assignment_ID
	,substr(ppf.first_name,1,1)||' '||substr(ppf.last_name,1,16) Full_Name
	,substr(ppf.last_name,1,18) Last_Name
	,rpad( substr(max(decode (prrv.input_value_id, :CP_Tax_Code_Input_Value_ID, 
	lpad(to_char(paa.action_sequence),16,'0')||prr.source_type||lpad(to_char(prr.run_result_id),16,'0')||prrv.result_value, NULL)),34,7),7)|| '  '|| 
	decode (substr(max(decode(prrv.input_value_id, :CP_Tax_Basis_Input_Value_ID, 
	lpad(to_char(paa.action_sequence),16,'0')||prr.source_type||lpad(to_char(prr.run_result_id),16,'0')||prrv.result_value,NULL)),34,1),'N', '1', NULL) Tax_Code 
	,substr(max(decode (prrv.input_value_id, :CP_NI_Input_Value_ID, lpad(to_char(paa.action_sequence),16,'0')|| prrv.result_value, NULL)),17,1) NI_Category 
	,nvl(sum(decode(pbt.balance_name,'Gross Pay',prrv.result_value * pbf.scale,0)),0) Gross
	,nvl(sum(decode(pbt.balance_name,'PAYE',prrv.result_value * pbf.scale,0)),0) PAYE
	,nvl(sum(decode(pbt.balance_name,'Total Deductions',prrv.result_value * pbf.scale,0)),0) Total_Deductions
	,nvl(sum(decode(pbt.balance_name,'Total Direct Payments',prrv.result_value * pbf.scale,0)),0) Direct_Payments
	,nvl(sum(decode(pbt.balance_name,'Net Pay',prrv.result_value * pbf.scale,0)),0) Net
	,nvl(sum(decode(pbt.balance_name,'NI Employer',prrv.result_value * pbf.scale,0)),0) NI_Employer
	,nvl(sum(decode(pbt.balance_name,'NI Employee',prrv.result_value * pbf.scale,0)),0) NI_Employee
	,max(pbt.currency_code) currency_code
	,max(ppa.date_earned) date_earned
	,max(ppa.payroll_id) payroll_id,
     PAY_PAYGBRRS_XMLP_PKG.cf_other_deductionsformula(nvl(sum(decode(pbt.balance_name,'Total Deductions',prrv.result_value * pbf.scale,0)),0),nvl(sum(decode(pbt.balance_name,'PAYE',prrv.result_value * pbf.scale,0)),0),nvl(sum(decode(pbt.balance_name,'NI Employee',prrv.result_value * pbf.scale,0)),0)) CF_Other_Deductions
	,PAY_PAYGBRRS_XMLP_PKG.cf_total_paymentformula(nvl(sum(decode(pbt.balance_name,'Gross Pay',prrv.result_value * pbf.scale,0)),0),nvl(sum(decode(pbt.balance_name,'Total Deductions',prrv.result_value * pbf.scale,0)),0),nvl(sum(decode(pbt.balance_name,'Total Direct Payments',prrv.result_value * pbf.scale,0)),0)) CF_Total_Payments	,PAY_PAYGBRRS_XMLP_PKG.cf_total_payments_currencyform(max(pbt.currency_code),max(ppa.date_earned),nvl(sum(decode(pbt.balance_name,'Net Pay',prrv.result_value * pbf.scale,0)),0)) CF_NET_CURRENCY 
from	pay_balance_feeds_f	pbf 
	,pay_balance_types		pbt 
	,pay_run_result_values	prrv 
	,pay_run_results		prr 
	,per_people_f		ppf 
	,per_assignments_f		paf 
	,pay_payroll_actions	ppa 
	,pay_assignment_actions	paa 
where ppa.payroll_id          = :P_PAYROLL_ID
and   ppa.time_period_id      = :P_TIME_PERIOD_ID
&P_CONSOLIDATION_SET_LINE
and   paa.payroll_action_id   = ppa.payroll_action_id
and   paa.ASSIGNMENT_ID	= paf.ASSIGNMENT_ID
and   paf.PERSON_ID	   = ppf.PERSON_ID
and   prr.assignment_action_id= paa.assignment_action_id
and   pbf.balance_type_id    = pbt.balance_type_id (+)
and   (
         nvl(pbt.legislation_code,'NULL')  = 'GB' 
         or
         prrv.input_value_id in ( :CP_NI_Input_Value_ID, :CP_Tax_Basis_Input_Value_ID , :CP_Tax_Code_Input_Value_ID)
         )
and   pbf.input_value_id (+) = prrv.input_value_id +0
and   prr.status in ('P', 'PA')
and   prrv.run_result_id    = prr.run_result_id
and   ( (pbf.input_value_id is not null and 
         balance_name in ( 'Gross Pay', 'PAYE','Total Deductions','Total Direct Payments','Net Pay','NI Employer','NI Employee')) or
      ( prrv.input_value_id in ( :CP_NI_Input_Value_ID, :CP_Tax_Basis_Input_Value_ID , :CP_Tax_Code_Input_Value_ID)))
and   ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and   ppa.effective_date between paf.effective_start_date and paf.effective_end_date
group by 
	 paf.Assignment_Number
	,paf.Assignment_ID
	,substr(ppf.first_name,1,1)||' '||substr(ppf.last_name,1,16) 
	,substr(ppf.last_name,1,18)
order by &CP_Sort_Order