PAY GB Pensions Automatic Enrollment

SELECT
			PAI.ACTION_INFORMATION15 ASSIGNMENT_NUMBER,
			PAI.ACTION_INFORMATION14 FULL_NAME,
			PAI.ACTION_INFORMATION18 AGE,
			fnd_date.canonical_to_date(PAI.ACTION_INFORMATION9) AUTO_ENROLLEMENT_DATE,
			PAI.ACTION_INFORMATION13 TOTAL_EAR_PRP,
			PAI.ACTION_INFORMATION7 EMPLOYEE_CLASSIFICATION,
			fnd_date.canonical_to_date(PAI.ACTION_INFORMATION10) OPT_OUT_END_DATE,
			PAI.ACTION_INFORMATION1 PENSION_SCHEME_NAME,
			PAA.ASSIGNMENT_ACTION_ID ASG_ACT_ID,
			DECODE (pml.MESSAGE_LEVEL,'F','1','W','2') SUCCESS
			FROM PAY_ACTION_INFORMATION PAI,
			PAY_ASSIGNMENT_ACTIONS PAA,
      PAY_MESSAGE_LINES pml
			WHERE PAI.ACTION_CONTEXT_TYPE='AAP'
			AND PAI.ACTION_CONTEXT_ID=PAA.ASSIGNMENT_ACTION_ID
			AND PAA.PAYROLL_ACTION_ID=:P_PAYROLL_ACTION_ID
			AND PAI.ACTION_INFORMATION_CATEGORY='GB_ENROLL_PENSIONS'
			AND PAI.ACTION_INFORMATION1 IS NOT NULL  
      AND pml.PAYROLL_ID IN (123, 111)
      AND pml.MESSAGE_LEVEL IN ('W', 'F')
      AND pml.SOURCE_TYPE = 'A'
			AND pml.SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
			AND fnd_date.canonical_to_date(PAI.ACTION_INFORMATION9) 
			BETWEEN :P_PRP_START_DATE AND :P_PRP_END_DATE
UNION
SELECT 
			PAI.ACTION_INFORMATION15 ASSIGNMENT_NUMBER,
			PAI.ACTION_INFORMATION14 FULL_NAME,
			PAI.ACTION_INFORMATION18 AGE,
			fnd_date.canonical_to_date(PAI.ACTION_INFORMATION9) AUTO_ENROLLEMENT_DATE,
			PAI.ACTION_INFORMATION13 TOTAL_EAR_PRP,
			PAI.ACTION_INFORMATION7 EMPLOYEE_CLASSIFICATION,
			fnd_date.canonical_to_date(PAI.ACTION_INFORMATION10) OPT_OUT_END_DATE,
			PAI.ACTION_INFORMATION1 PENSION_SCHEME_NAME,
			PAA.ASSIGNMENT_ACTION_ID ASG_ACT_ID,
      '3' SUCCESS
			FROM PAY_ACTION_INFORMATION PAI,
			PAY_ASSIGNMENT_ACTIONS PAA
			WHERE PAI.ACTION_CONTEXT_TYPE='AAP'
			AND PAI.ACTION_CONTEXT_ID=PAA.ASSIGNMENT_ACTION_ID
			AND PAA.PAYROLL_ACTION_ID=:P_PAYROLL_ACTION_ID
			AND PAI.ACTION_INFORMATION_CATEGORY='GB_ENROLL_PENSIONS'
			AND PAI.ACTION_INFORMATION1 IS NOT NULL
			AND fnd_date.canonical_to_date(PAI.ACTION_INFORMATION9) 
			BETWEEN :P_PRP_START_DATE AND :P_PRP_END_DATE
       AND NOT EXISTS
						(
						SELECT  1
						FROM    PAY_MESSAGE_LINES
						WHERE   PAYROLL_ID IN (123, 111)
						AND     MESSAGE_LEVEL IN ('W', 'F')
						AND     SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
						AND     SOURCE_TYPE = 'A'
						)
order by SUCCESS