PAY IE P30

SELECT   ppa_p30.payroll_action_id report_id, NULL payroll_id,
         paa_p30.assignment_id assignment_id,
         pact_er.action_information5 employer_paye_number,
         pact_er.action_information4 tax_district_number,
         pap.full_name employee_name,
         MAX (pact_ee.action_information4) prsi_number ,
	PAY_PAYIEP30_XMLP_PKG.cf_payroll_nameformula(NULL) CF_Payroll_Name,
	PAY_PAYIEP30_XMLP_PKG.cf_calculate_total_prsiformula(ppa_p30.payroll_action_id, paa_p30.assignment_id) CF_CALCULATE_TOTAL_PRSI,
	PAY_PAYIEP30_XMLP_PKG.CP_PAYE_YTD_p CP_PAYE_YTD,
	PAY_PAYIEP30_XMLP_PKG.CP_EE_PRSI_YTD_p CP_EE_PRSI_YTD,
	PAY_PAYIEP30_XMLP_PKG.CP_ER_PRSI_YTD_p CP_ER_PRSI_YTD,
	PAY_PAYIEP30_XMLP_PKG.CP_TOTAL_PRSI_REPORT_p CP_TOTAL_PRSI_REPORT,
	PAY_PAYIEP30_XMLP_PKG.CP_TOTAL_PRSI_YTD_p CP_TOTAL_PRSI_YTD,
	PAY_PAYIEP30_XMLP_PKG.CP_PAYE_PTD_p CP_PAYE_PTD,
	PAY_PAYIEP30_XMLP_PKG.CP_EE_PRSI_PTD_p CP_EE_PRSI_PTD,
	PAY_PAYIEP30_XMLP_PKG.CP_ER_PRSI_PTD_p CP_ER_PRSI_PTD
FROM     pay_payroll_actions ppa_p30,
         pay_assignment_actions paa_p30,
         per_all_assignments_f paa,
         per_all_people_f pap,
         pay_action_interlocks pai_p30,
         pay_assignment_actions paa_arc,
         pay_action_interlocks pai_arc,
         pay_assignment_actions paa_prepay,
         pay_action_interlocks pai_prepay,
         pay_action_information pact_er,
         pay_action_information pact_ee,
         per_periods_of_service pps
      WHERE ppa_p30.payroll_action_id = :p_p30_action_id
     AND ppa_p30.payroll_action_id = paa_p30.payroll_action_id
     AND paa_p30.assignment_id = paa.assignment_id
	 AND paa.person_id = pap.person_id
     AND pps.person_id = pap.person_id
	AND NVL (pps.actual_termination_date, nvl ( ppa_p30.date_earned , ppa_p30.effective_date ))
      BETWEEN pap.effective_start_date
      AND pap.effective_end_date
      AND NVL (pps.actual_termination_date, nvl ( ppa_p30.date_earned , ppa_p30.effective_date ))
      BETWEEN paa.effective_start_date
      AND paa.effective_end_date
/*     AND NVL (pps.actual_termination_date, ppa_p30.date_earned)
            BETWEEN pap.effective_start_date
                AND pap.effective_end_date
     AND NVL (pps.actual_termination_date, ppa_p30.date_earned)
            BETWEEN paa.effective_start_date
                AND paa.effective_end_date   */
     AND paa_p30.assignment_action_id = pai_p30.locking_action_id
     AND paa_arc.assignment_action_id = pai_p30.locked_action_id
     AND paa_arc.assignment_action_id = pai_arc.locking_action_id
     AND paa_prepay.assignment_action_id = pai_arc.locked_action_id
     AND paa_prepay.assignment_action_id = pai_prepay.locking_action_id
     AND pact_er.action_context_type = 'PA'
     AND pact_er.action_information_category = 'EMEA PAYROLL INFO'
     AND pact_er.action_context_id = paa_arc.payroll_action_id
     AND pact_er.action_information1 = paa_prepay.payroll_action_id
     AND pact_ee.action_information_category = 'EMPLOYEE DETAILS'
     AND pact_ee.action_context_id = paa_arc.assignment_action_id
	 /* Bug 17918968 Start */
	  AND pps.period_of_service_id = paa.period_of_service_id
	  AND paa.assignment_type = 'E'
	  AND paa.primary_flag = 'Y'
	 /* Bug 17918968 End */
GROUP BY ppa_p30.payroll_action_id,
         NULL,
         paa_p30.assignment_id,
         pact_er.action_information5,
         pact_er.action_information4,
         pap.full_name