PAY GB Pensions Automatic Enrollment

➜ Back to Library
Description
Categories: BI Publisher
Application: Payroll
Source: GB Pensions Automatic Enrollment Report
Short Name: PYGBPAER
DB package:
Run PAY GB Pensions Automatic Enrollment and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Payroll Action Id 
 
 Number
Pay Start Date 
 
 Date
Pay End Date 
 
 Date
PRP Start Date 
 
 Date
PRP End Date 
 
 Date
Mode 
 
 Char
Payroll Name 
 
 Char
Period Name 
 
 Char
Download
Related Blitz Reports
PAY RTI YTD Reconciliation Report Output PAY GB Pensions Automatic Re-enrolment PAY Payroll Tax Report by Legal Employer PAY Payroll Report (Check List of Bonus Payment Notification for Health Ins/Welfare Pension Ins) PAY RTI - Year End P60 Report Process (Multi Threaded) PAY RTI - Year End P60 Report Process PAY Payroll Reconciliation Detail PQP Teachers Pension MCR Reconciliation Report (Excel) PAY RTI ? Employer Alignment Submission Output
Blitz Report™

Blitz Report™ provides multiple benefits: