Magnetic Transfer

select 'Magnetic Transfer' a_type, org.name company, tp.period_name , lk.bank_name_alt, lk.bank_name bank_name, lk.bank_branch_name_alt branch_name, ea.segment1 bsb, ea.segment2 account_nr, pp.value, ea.segment3 account_name, per.last_name || ', ' || per.first_name full_name, per.employee_number
from pay_payroll_actions pa,
pay_assignment_actions_v aa,
pay_pre_payments pp,
pay_personal_payment_methods_f pm,
pay_external_accounts ea,
ap_bank_branches lk,
per_time_periods tp,
per_assignments_f ass,
per_people_x per,
hr_all_organization_units org,
pay_payrolls_x pay
where 1 = 1
and tp.period_name = nvl(:p_period_name, tp.period_name)
and nvl(pa.date_earned,pa.effective_date) between tp.start_date and tp.end_date
and tp.payroll_id = pa.payroll_id
and pay.payroll_id = tp.payroll_id
AND pay.payroll_name = nvl(:p_payroll_name, pay.payroll_name)
and pa.action_type = 'M'
and pa.payroll_action_id = aa.payroll_action_id
and pp.pre_payment_id = aa.pre_payment_id
and pp.personal_payment_method_id = pm.personal_payment_method_id
and :p_period_end_date between pm.effective_start_date and pm.effective_end_date
and pm.external_account_id = ea.external_account_id (+)
and ea.segment1 = lk.bank_number (+)||'-'||lk.bank_num (+)
and ass.assignment_id = aa.assignment_id
and :p_period_end_date between ass.effective_start_date and ass.effective_end_date
and per.person_id = ass.person_id
and org.organization_id (+)= pa.business_group_id
and decode(:p_business_group,'ALL', org.name, :p_business_group) = org.name