PAY GB Pensions Automatic Enrollment

Description
Categories: BI Publisher
Columns: Assignment Number, Full Name, Age, Auto Enrollement Date, Total Ear Prp, Employee Classification, Opt Out End Date, Pension Scheme Name, Asg Act Id, Success ...
Application: Payroll
Source: GB Pensions Automatic Enrollment Report
Short Name: PYGBPAER
DB package:
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
Parameter NameSQL textValidation
Payroll Action Id
 
Number
Pay Start Date
 
Date
Pay End Date
 
Date
PRP Start Date
 
Date
PRP End Date
 
Date
Mode
 
Payroll Name
 
Period Name