PAY GB Pensions Automatic Enrollment

Description
Categories: BI Publisher, Human Resources
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 Name SQL text Validation
Period Name
 
Payroll Name
 
Mode
 
PRP End Date
 
Date
PRP Start Date
 
Date
Pay End Date
 
Date
Pay Start Date
 
Date
Payroll Action Id
 
Number