PAY IE Cheque Listing

select
   to_number(paa.serial_number) "Cheque_number",
   per.last_name,
   per.first_name,
   per.middle_names,
   ppf.payroll_name "Payroll name",
   ppa.effective_date "Effective date",
   per.employee_number,
   paf.assignment_number,
   ppp.value "Amount",
   ppp.personal_payment_method_id,
   ppa.date_earned,
	PAY_PAYIECHL_XMLP_PKG.report_dateformula() report_date,
	PAY_PAYIECHL_XMLP_PKG.payeeformula(ppp.personal_payment_method_id, ppa.date_earned, per.last_name, per.first_name, per.middle_names) Payee,
	PAY_PAYIECHL_XMLP_PKG.EMPLOYEE_NAMEFORMULA(per.last_name,per.first_name,per.middle_names) Employee_name
from
   pay_payrolls_f ppf, per_people_f per,
   pay_pre_payments ppp, pay_payroll_actions ppa,
   pay_assignment_actions paa, per_assignments_f paf
where not exists
(
   select
      1
   from
      pay_action_interlocks lck,
      pay_assignment_actions void_aa,
      pay_payroll_actions void_pa
   where lck.locked_action_id = paa.assignment_action_id
   and lck.locking_action_id = void_aa.assignment_action_id
   and void_aa.payroll_action_id = void_pa.payroll_action_id
   and void_pa.action_type = 'D'
)
and ppa.payroll_action_id = :PACTID
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status not in ('V', 'E')
and paa.pre_payment_id = ppp.pre_payment_id
and paa.assignment_id = paf.assignment_id
and paf.payroll_id = ppf.payroll_id
and paf.person_id = per.person_id
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppa.effective_date between per.effective_start_date and per.effective_end_date
&P_SORT_ORDER_T