ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3313869E053B46B63588994Y 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 PAY PYGBPAER US PAY GB Pensions Automatic Enrollment Application: Payroll Source: GB Pensions Automatic Enrollment Report Short Name: PYGBPAER DB package: BI Publisher :p_payroll_action_id :p_prp_end_date :p_prp_start_date 1 10 :p_payroll_action_id Number US Payroll Action Id 2 20 :p_pay_start_date Date US Pay Start Date 3 30 :p_pay_end_date Date US Pay End Date 4 40 :p_prp_start_date Date US PRP Start Date 5 50 :p_prp_end_date Date US PRP End Date 6 60 :p_mode Char US Mode 7 70 :p_payroll_name Char US Payroll Name 8 80 :p_period_name Char US Period Name