PAY Void Payments

select   ppf.full_name Employee_name ,
        paf.assignment_number  Assignment_Number ,
        paa2.serial_number Payment_Number ,
        ppa2.effective_date Payment_Date ,
        ppa1.effective_date Void_Date ,
        ppp.value Amount ,
        ppef.payroll_name Payroll_Name ,
        pcs.consolidation_set_name Consolidation_Set ,
        htu.name GRE_name,       DECODE(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Check') payment_type, 
	--&payment_type C_Payment_Type
	PAY_PAYRPVPR_XMLP_PKG.C_Payment_TypeFormula(DECODE(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Check')) C_Payment_Type
from   
  pay_payroll_actions ppa1,   pay_assignment_actions paa1,   pay_action_interlocks pai1,   pay_assignment_actions paa2,   pay_payroll_actions  ppa2,   per_assignments_f paf,
  per_people_f ppf,
  pay_action_interlocks pai2,   pay_assignment_actions paa3,   pay_payroll_actions ppa3,   hr_organization_units htu,
  pay_payrolls_f   ppef,
  pay_pre_payments ppp,
  pay_consolidation_sets pcs
where ppa1.business_group_id + 0 = :p_business_group_id  
and :p_uk_us = 'US'
and ppa1.action_type = 'D'
and ppa1.effective_date  between :p_start_date and :p_end_date  
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.assignment_action_id = pai1.locking_action_id
and pai1.locked_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and paa2.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppa2.effective_date  between ppf.effective_start_date and
ppf.effective_end_date
and ppa2.effective_date between paf.effective_start_date and
paf.effective_end_date
and pai2.locking_action_id = paa2.assignment_action_id
and paa3.assignment_action_id = pai2.locked_action_id
and paa3.payroll_action_id = ppa3.payroll_action_id
and ppa3.payroll_id = ppef.payroll_id
and paa2.pre_payment_id = ppp.pre_payment_id
and ppa2.effective_date between ppef.effective_start_date and
ppef.effective_end_date
and htu.organization_id = paa2.tax_unit_id
and ppa3.consolidation_set_id = pcs.consolidation_set_id
and htu.business_group_id = :p_business_group_id
&where_optional
union
select   ppf.full_name Employee_name ,
        paf.assignment_number  Assignment_Number ,
        paa2.serial_number Payment_Number ,
        ppa2.effective_date Payment_Date ,
        ppa1.effective_date Void_Date ,
        ppp.value Amount ,
        ppef.payroll_name Payroll_Name ,
        pcs.consolidation_set_name Consolidation_Set ,
        to_char(null) GRE_name,             decode(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Cheque') payment_type, 
	--&payment_type C_Payment_Type
	PAY_PAYRPVPR_XMLP_PKG.C_Payment_TypeFormula(decode(ppa2.action_type,'M',SUBSTRB(hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE','DEPOSIT'),1,8),'Cheque')) C_Payment_Type
from   
  pay_payroll_actions ppa1,   pay_assignment_actions paa1,   pay_action_interlocks pai1,   pay_assignment_actions paa2,   pay_payroll_actions  ppa2,   per_assignments_f paf,
  per_people_f ppf,
  pay_action_interlocks pai2,   pay_assignment_actions paa3,   pay_payroll_actions ppa3,   pay_payrolls_f   ppef,
  pay_pre_payments ppp,
  pay_consolidation_sets pcs
where ppa1.business_group_id + 0 = :p_business_group_id  
and :p_uk_us <> 'US'
and ppa1.action_type = 'D'
and ppa1.effective_date  between :p_start_date and :p_end_date  
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.assignment_action_id = pai1.locking_action_id
and pai1.locked_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and paa2.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppa2.effective_date  between ppf.effective_start_date and
ppf.effective_end_date
and ppa2.effective_date between paf.effective_start_date and
paf.effective_end_date
and pai2.locking_action_id = paa2.assignment_action_id
and paa3.assignment_action_id = pai2.locked_action_id
and paa3.payroll_action_id = ppa3.payroll_action_id
and ppa3.payroll_id = ppef.payroll_id
and paa2.pre_payment_id = ppp.pre_payment_id
and ppa2.effective_date between ppef.effective_start_date and
ppef.effective_end_date
and ppa3.consolidation_set_id = pcs.consolidation_set_id
&where_optional
order by   
Employee_name
&order_optional