Pre-Payments

select pay.payroll_name payroll_name,
1 employee_number,
sum(decode( aa2.action_type, 'R', decode( porg.org_payment_method_name, 'EFT', 1))) EFT_No_R,
sum(decode( aa2.action_type, 'R',decode( porg.org_payment_method_name, 'EFT', pp1.value))) EFT_Amount_R,
sum(decode( aa2.action_type, 'Q', decode( porg.org_payment_method_name, 'EFT', 1))) EFT_No_Q,
sum(decode( aa2.action_type, 'Q',decode( porg.org_payment_method_name, 'EFT', pp1.value))) EFT_Amount_Q,
sum(decode( porg.org_payment_method_name, 'Cheque', 1)) Cheque_No,
sum(decode( porg.org_payment_method_name, 'Cheque', pp1.value)) Cheque_Amount
from per_time_periods tp,
pay_payroll_actions pa,
pay_assignment_actions_v aa,
pay_assignment_actions_v aa2,
pay_pre_payments pp1,
pay_personal_payment_methods_f pm1,
pay_org_payment_methods_f porg,
pay_action_interlocks pil,
per_assignments_f ass,
per_people_x per,
pay_payrolls_x pay
where 1 = 1
and pay.payroll_name = nvl(:p_payroll_name,pay.payroll_name)
and tp.payroll_id = pa.payroll_id
and tp.period_name = :p_period_name
and pa.effective_date between tp.start_date and tp.end_date
and pa.action_type = 'P'
and aa.status_code = 'C'
and aa.payroll_action_id = pa.payroll_action_id
and pp1.assignment_action_id = aa.assignment_action_id
and pm1.personal_payment_method_id (+) = pp1.personal_payment_method_id
and :P_END_DATE_PERIOD between pm1.effective_start_date (+) and pm1.effective_start_date (+)
and pp1.org_payment_method_id = porg.org_payment_method_id
and tp.start_date between porg.effective_start_date and porg.effective_end_date
and ass.assignment_number = aa.assignment_number
and tp.end_date between ass.effective_start_date and ass.effective_end_date
and per.person_id = ass.person_id
and ass.payroll_id = pay.payroll_id
and pil.locking_action_id = aa.assignment_action_id
and aa2.assignment_action_id = pil.locked_action_id
group by payroll_name, per.employee_number